Excel公式和函数 典型案例-制作入库单

Excel 公式和函数 典型案例－制作入库单

入库单是商家与商家之间互相调货的凭证，它可以表明商品的名称、入库数量、入库时间以及产品单价等内容。本例将根据创建好的“库存商品入库单”，利用数组公式完成“入库单计算部分”的制作。在计算过程中，主要是通过SUM 函数与其他函数的嵌套使用而完成的。

1．练习要点

● 排序

● 使用数组公式 ● 使用SUM 函数 2．操作步骤：

（

1）合并

A1至E1单元格区域，输入“库存商品入库单”文字，设置字体格式，并调整其【行高】为30。然后，在A2至E2单元格区域中，分别输入各字段名称，如图11-47所示。

图11-47 输入数据并调整行高

提 示 设置标题文字和各字段名的【字

体】均为“方正姚体”；【字号】分别为18和12。

（2）在A3至E19单元格区域，分

别输入相应的数据内容，并为其添加“所有边框”和“粗匣边框”效果，如图11-48所示。

设置

输入数据

输入数据

图11-48 输入相关数据

提 示

选择A2至E2单元格区域，设置

其【填充颜色】为“黄色”。

（3）选择C3至C19单元格区域，单击【排序和筛选】下拉按钮，执行【升序】命令。然后，在弹出的对话框中，选择【扩展选定区域】单选按钮，单击【排序】按钮，即可对“入库单”进行排序操作，如图11-49所示。

技 巧 另外，也可以选择【数据】选项

卡，单击【排序和筛选】组中的【排序】按钮，进行排序操作。

图11-49 排序 （4）在G1至J16单元格区域中，创建“入库单的计算部分”数据表，并

设置要求计算的字段名称的【填充颜色】为“橙色”，如图11-50所示。

图11-50 入库单的计算部分

（5）选择J2单元格，在【编辑栏】中输入“=SUM(($A$3:$A$19="洗手液")*(MONTH($E$3:$E$19)=7)*$C$3:$C$19)”公式，并按Ctrl+Shift+Enter 组合键，即可计算出“7月份洗手液的入库数量”如图11-51所示。

图11-51 7月份洗手液的入库数量 提 示 在【编辑栏】中输入公式后，按

Ctrl+Shift+Enter 组合键即表示该

公式为数组公式。

（6）选择J3单元格，在【编辑栏】中输入“=SUM(($A$3:$A$19="洗衣粉

")*(MONTH($E$3:$E$19)=2)*$C$3:$C$19

)”公式，并按Ctrl+Shift+Enter 组合键，如图11-52所示。

选择

效果显示

创建数据表

输入

计算结果

图11-52 2月份洗衣粉的入库数量

（7）选择J4单元格，在【编辑栏】中输入“=SUM(IF(($A$3:$A$19="洗手液")+($A$3:$A$19="洗涤剂"),($C$3:$C$19))*($D$3:$D$19))”公式，并按Ctrl+Shift+E nter 组合键，如图11-53所示。

图11-53 洗手液和洗涤剂的总价值

（8）选择J5单元格，在【编辑栏】中输入“=SUM(IF((MONTH($E$3:$E$19)>=3)<>($A$3:$A$19="空气清新剂"),$C$3:$C$19))”公式，并按Ctrl+Shift+Enter 组合键，如图11-54所示。

图11-54 计算结果

注 意

由于J5单元格是合并单元格，因此数组公式将无法输入，用户可

以先拆分该单元格，输入数组公式之后再进行合并。

（9）选择J8单元格，在【编辑栏】中输入“=SUM((A3:A19="空气清新剂")*(C3:C19>500))”公式，并按Ctrl+Shift+E nter 组合键，即可计算出空气清新剂入库数量有几次超过500，如图11-55所示。

图11-55 空气清新剂大于500的次数

（10）选择J9单元格，在【编辑栏】中输入“=SUM(1/COUNTIF($A$3:$A$19,$A$3:$A$19))”公式，并按Ctrl+Shift+En ter 组合键，如图11-56所示。

图11-56 该入库单中的产品种类

（11）选择J10单元格，在【编辑栏】中输入“=SUM(IF(MATCH(MONTH($E $3:$E$19),MONTH($E$3:$E$19),0)=ROW ($E$3:$E$19)-2,1))”公式，并按Ctrl+Shi ft+Enter 组合键，如图11-57所示。

输入

计算结果

输入

计算结果

输入

计算结果

输入

计算结果

输入

计算结果

图11-57 入库单统计的月份数

（12）选择I13单元格，在【编辑栏】中输入“=SUM(IF(($A$3:$A$19="洗衣粉"),($C$3:$C$19),0))”公式，并按Ctrl+Shift+Enter 组合键，即可计算出洗衣粉的总数量，如图11-58所示。

图11-58 洗衣粉的总数量

（13）选择I14单元格，在【编辑栏】中输入“=SUM(IF(($A$3:$A$19="洗涤剂"),($C$3:$C$19),0))”公式，并按Ctrl+Shift+Enter 组合键，即可计算出洗涤剂的总数量，如图11-59所示。

图11-59 洗涤剂的总数量

（14）选择I15单元格，在【编辑栏】中输入“=SUM(IF(($A$3:$A$19="空气清新剂"),($C$3:$C$19),0))”公式，并

按Ctrl+Shift+Enter 组合键，即可计算出空气清新剂的总数量，如图11-60所示。

图11-60 空气清新剂的总数量

（15）选择I16单元格，在【编辑栏】中输入“=SUM(IF(($A$3:$A$19="洗手液"),($C$3:$C$19),0))”公式，并按Ctrl+Shift+Enter 组合键，即可计算出洗手液的总数量，如图11-61所示。

11-61 洗手液的总数量

输入

计算结果

输入

计算结果

输入

计算结果

输入

计算结果

输入

计算结果