一个收入日报表及传递单的设计实例

收入日报表及传递单的设计实例

在商业企业特别是有门市部的企业中，收款工作一般由基层出纳人员担任，这样门市或基层就需要和财务部门进行单据传递，有的还有交款工作，因此需要传递单比较正规。有的单位没有物流软件，这样就需要用EXCEL来设置收入日记账。如果传递单能够从日记账中自动生成，那么会减少录入人员的很大的工作量。

例：某事业单位，下属两个收款部门，一个总台，一个门市部。总台从事事业性收费和经营性收费，门市部从事经营性收费。两个部门都要每天将收到的款项和开具的单据交到财务部。

工作目的：设置一张规范的传递单。

工作要求：减少重复劳动，反映日报表中各要素统计情况。

了解了目的和要求后，先设计工作思路，然后按步骤逐步完成具体工作。

工作思路：设计两个单元表，主表用于序时反映每天的收入，相当于收入日报表；副表用于设计传递单，以极少的手工输入为判定条件，用公式导入其他的要素结果。

工作步骤：

一、建立两个单元表：如下图

以下的讲述中，有时会将收入日报表称为主表，将传递单称为副表。

二、设计收入日报表：格式如下

说明：1、交款单位只有两个：总台和门市部；收入项目有三种：应税收入、非税收入、定金；发票类型有四种：增值税发票、财政非税票据、定额发票、收据。所以这三项可以用数据有效性定为选项，输入时可以直接选择，不易出错：

2、金额栏等于后面的五种收款方式之和。

3、收入日报表可以在期末以数据透视表来的形式来进行分析和统计工作（这里不作介绍）。

二、设计传递单：

1、固定部份：传递单上，有一部份不需要输入，我们可以将其固定下来：

2、用SUM函数求合计金额。

3、合计金额与收款方式的各项之和核对。

公式如下：

结果如下：

4、手动输入部分：手动输入越少，越不容易出错。

从上表可见，我们只需要输入日期和单位两个要素。

5、设计公式从主表上提取数值，用VLOOKUP函数的多条件查找。从传递单上可见，我们需要时间、单位、发票型号这三个条件来取得发票号码、票据张数、金额三个数值。公式如下：

{=IFERROR(VLOOKUP($B$2&$B4&$D$2,IF({1,0},收入日报

表!$A$3:$A$10000&收入日报表!$E$3:$E$10000&收入日报

表!$B$3:$B$10000,收入日报表!$F$3:$F$10000),2,0),"")}

这个公式是完整的连续的，请忽略WORD的分行显示。

公式释义：

（1）用IFERROR是为了显示美观，即在由于公式找不到项目而显示错误的时候，用空值显示。否则会出现下图的错误提示，影响打印出来的美观：

（2）VLOOKUP的多条件，用&进行连接，并且条件对应的查找区域也应该一一对应。公式中的B2是副表的时间，所以要在收入日报表中A列去查找；同样，B4是票据类型，对应主表中E列；D2是单位，对应主表的B列。

（3）三个条件相符后，公式会去F列寻找发票号码。同样，G列是票据张数，H列是金额。公式大同小异。

（4）公式中的IF函数，IF({1,0},第一参数,第二参数)，表示固定第一参数为条件参数列，固定第二参数为求值参数列。

（5）由于本公式是一个数组公式，所以需要在公式录入后，用CTRL+SHIFT+ENTER完成输入工作。

6、用SUMPRODUCT函数对收款方式进行求值。公式如下：

=SUMPRODUCT((收入日报表!$A$3:$A$10000=$B$2)*(收入日报表!$B$3:$B$10000=$D$2)*(收入日报表!$I$3:$I$10000))

这也是一个连续的公式，也请忽略WORD的分行。

公式释义：

（1）当收入日报表上的时间与传递单上一致，公式会提取部门与传递单上一致的现金收款的当日合计数，公式中显示现金列在I列。同理，微信、支付宝等其他收款方式的数据在对应的列中，公式大同小异，只是数值列不同而已。

（2）因为我们不知道一年中这个表到底要录入多少行，所以我们暂定为1万行。当然也可以设置为2万行、3万行，只要是表格允许的最大值以内，都行。（题外话，我用的这个WPS表格有上百万行）

至此，所有工作完成。总结一下要点：

一、运用了两个主要函数：VLOOKUP和SUMPRODUCT；运用了两个辅助函数，IF用于简化公式，IFERROR用于美化表格。

二、运用了数组公式，普通公式用ENTER直接完成输入，数组公式需要用CTRL+SHIFT+ENTER完成输入，之后会在公式外部出现大括号{}。

三、传递单尽量少用手动输入，减少出错概率。