excel-项目六 超市商品销售管理

项目六超市商品销售管理

项目描述

现代超市都会利用计算机来帮助完成商品的销售、清点、统计分析以及员工的工资发放。本项目通过制作超市商品清单及收银单、制作超市销售日报表、制作超市月销售额统计表、制作超市员工业绩工资表4个任务，完成简易的超市商品销售管理系统的构建，实现超市商品销售的计算机管理。

1．制作超市商品清单及收银单

制作超市商品清单、收银单的示例如图6-1、6-2所示。完成该任务除了需要在项目五中已学会的Excel工作表创建、数据编辑等知识和技能，还要进一步学会Excel工作表格式排版、公式与函数使用的知识和技能。

图6-1 超市商品清单

图6-2 超市收银单

2．制作超市销售日报表

制作超市销售日报表的示例如图6-3、6-4、6-5所示。完成该任务需要学会排序、筛选以及分类汇总的知识与技能。

图6-3 商品日销售明细表图6-4 商品日销售明细（分类汇总）

图6-5 商品日销售明细（图表分析）

3．制作超市月销售额统计表

制作超市月销售额统计表的示例如图6-6、6-7、6-8所示。完成该任务需要学会合并计算、跨表引用的知识与技能。

图6-6 商品月销售统计表（图表分析）

图6-7商品月销售统计表（合并计算）

图6-8商品月销售业绩统计表

4．制作超市员工工资表

制作超市员工工资表的示例如图6-9所示。完成该任务需要学会绝对引用、公式与函数综合应用的知识与技能。

图6-9 超市员工工资表

6.1 制作超市商品清单及收银单

6.1.1 任务分析

商品清单就是一个Excel表，表中通常包含商品的编码、名称、单价等信息，创建一个新Excel表后，录入商品的编码、名称、单价等信息，即完成商品清单的制作。在超市购物时，可发现收银员要做的工作是输入商品编码、输入购买数量、输入实付款，商品的名称、单价、应收款、找零等信息是自动生成的。因此，在收银单制作时，除了包含商品的编码、名称、单价等信息外，还要包含交易时间、交易数量、应收款、实付款、找零等信息，并且根据收银员输入的数据能自动生成所需的数据。

制作思路：首先，新建一个Excel工作簿，在工作表1中录入商品信息，进行相应的格式排版，制作出商品清单；然后，在工作表2中，制作出收银单的雏形；最后，根据输入的商品编码自动生成商品名称及单价，根据输入的购买数量自动计算出金额及应收款，根据输入的实付款自动计算出找零。

学习的知识点：

●数据录入

●格式排版

●公式函数

6.1.2 制作步骤

1. 制作商品清单

（1）新建一个Excel工作簿

①选择“开始”→“所有程序”→“Microsoft Office”→“Microsoft Office Excel 2003”菜单命令，新建一个Excel工作簿。

②将Excel工作簿命名为“职院超市收银系统”，将sheet1重命名为“商品清单”、sheet2重命名为“收银单”，删除sheet3，如图6-10所示。

图6-10 新建工作簿

（2）录入商品清单数据

①单击“商品清单”，在第1行录入标题“职院超市货架商品清单”，在第2行的A～D 字段分别录入“商品编码”、“商品名称”、“商品单价”、“单位”。

②根据职院超市的商品信息，从第3行起依次完成各商品信息的录入，如图6-11所示。

③单击常用工具栏上的保存按钮，保存录入的文字信息。

图6-11 职院超市货架商品清单

（3）设置标题格式

①选中标题行“职院超市货架商品清单”要合并的单元格区域“A1:D1”，单击工具栏上

的“合并及居中”按钮，将标题行合并及居中显示，如图6-12所示。

图6-12 设置“商品清单”标题格式

②选中标题文字“职院超市货架商品清单”，将标题文字设置为“黑体”、“14号”、“加粗”。

（4）设置字段名称格式

①选中字段名称行区域“A2:D2”，单击格式工具栏上的“加粗”按钮，将字段名称设置为加粗。

②接着选择“格式”→“单元格”菜单命令，打开“单元格格式”对话框；单击“单元格格式”对话框中“图案”选项卡，单击“灰色-40%”，如图6-13所示。

③单击“确定”按钮，将字段名称填充为灰色底纹。

图6-13 “单元格格式”对话框的“图案”选项卡

（5）设置数据区域格式

①选中数据区域“A2:D14”，选择“格式”→“单元格”菜单命令，打开“单元格格式”对话框。

②单击“单元格格式”对话框中“边框”选项卡，将外边框设置为蓝色双线，内边框设置为红色单线，如图6-14（a）所示。

③单击“确定”按钮，完成数据区域的边框设置，如图6-14（b）所示。

（a）“单元格格式”对话框的“边框”选项卡（b）设置效果

图6-14 设置数据清单区域格式

（6）商品清单设置

①鼠标左键单击左上角行标号和列标号的交叉处，选中整个工作表；然后，选择“格式”→“单元格”菜单命令，打开“单元格格式”对话框；单击“单元格格式”对话框中的“对齐”选项卡，如图6-15所示；将“水平对齐”、“垂直对齐”都设置为“居中”后，单击“确定”按钮。

图6-15 “单元格格式对话框”的“对齐”选项卡

②将鼠标移动到行标号或列标号之间的交叉处，在鼠标变成双向箭头时按住左键上下拖动，调整行高；将鼠标移动到列标号之间的交叉处，在鼠标变成双向箭头时按住左键左右拖动，调整列宽。

③选中“C3:C14”区域，选择“格式”→“单元格”菜单命令，打开“单元格格式”对话框；单击“单元格格式”对话框中的“数字”选项卡，在“分类”选项中单击“货币”，在“小数位数”文本框中输入“2”，在“货币符号”组合框的下拉选项中单击人民币符号“￥”，如图6-16所示；最后，单击“确定”按钮，完成设置。

图6 -16 “单元格格式”对话框的“数字”选项卡

2. 制作收银单雏形

（1）录入收银单中的文字信息

①单击“收银单”，在第1行录入标题“职院超市收银单”，在第2行录入“交易时间”，在第3行的A～E字段分别录入“商品编码”、“商品名称”、“单价”、“数量”、“金额”。

②在A16录入“应收款：”，在D16录入“实付款：”，在A17录入“大写：”，在A18录入“找零：”，在A19录入“货款请当面点清职院超市欢迎您再次光临！”，如图6-17所示。

③单击常用工具栏上的保存按钮，保存录入的文字信息。

图6-17 制作收银单

（2）设置格式

①选中标题行“职院超市收银单”要合并的单元格区域“A1:E1”，单击工具栏上的“合并及居中”按钮，将标题行合并及居中显示，如图6-18所示；选中“职院超市收银单”，将标题行文字设置为“黑体”、“14号”、“加粗”；选中“A19:E19”，单击工具栏上的“合并及居中”按钮，将“货款请当面点清职院超市欢迎您再次光临！”设置为两行显示。

图6-18 设置“收银单”标题格式

②选中“A2:E3”区域，打开“单元格格式”对话框中的“边框”选项卡；在“预置”中单击“内部”选项，在“样式”中单击“虚线”选项，在“边框”中分别单击“上横线”、“中横线”、“下横线”选项，如图6-19所示；单击“确定”按钮。

图6-19 设置横虚线

③选中“B2:E2”区域，单击工具栏上的“合并及居中”按钮，将交易发生的时间值居中显示。

③选中字段名称行区域“A3:E3”，单击格式工具栏上的“加粗”按钮；接着单击工具栏上的“填充颜色”按钮，将底纹填充为“灰色-40%”。

④选中“A3:E15”区域，打开“单元格格式”对话框中的“边框”选项卡；在“边框”中单击“中竖线”选项，单击“确定”按钮。

⑤选中“A15:E15”区域，打开“单元格格式”对话框中的“边框”选项卡；在“边框”中单击“下横线”选项，单击“确定”按钮。

⑥选中“A16:C17”区域，打开“单元格格式”对话框中的“边框”选项卡；在“边框”中单击“中横线”选项，单击“确定”按钮。

⑦选中“A17:E18”区域，打开“单元格格式”对话框中的“边框”选项卡；在“边框”中分别单击“中横线”、“下横线”选项，单击“确定”按钮。

⑧选中“B16:C16”，单击工具栏上的“合并及居中”按钮；选中“B17:C17”，单击工具栏上的“合并及居中”按钮；选中“D16:D17”，单击工具栏上的“合并及居中”按钮；选中“E16:E17”，单击工具栏上的“合并及居中”按钮；选中“B18:E18”，单击工具栏上的“合并及居中”按钮。

⑨选中B16，打开“单元格格式”对话框中的“数字”选项卡，在“分类”选项中单击“货币”，在“小数位数”文本框中输入“2”，在“货币符号”组合框的下拉选项中单击人民币符号“￥”，单击“确定”按钮；选中E16，用同样的方法设置其货币格式。

⑩选中B17，在单元格应用公式“= B16”，并在“分类”选项中单击“特殊”，在“类型”中单击“中文大写数字”，如图6-20所示；最后，单击“确定”按钮。

图6-20 设置“中文大写数字”

3. 在收银单中使用公式与函数

（1）自动生成商品名称、单价

在A4:A15区域内任一单元格输入商品编码，相对应商品清单表中的商品名称、单价自动显示在收银单中的操作步骤如下。

①选中B4单元格，单击“插入函数”按钮，打开“插入函数”对话框，如图6-21所示；在“选择函数”中找到“VLOOKUP”函数。

图6-21 打开“插入函数”对话框

②单击“确定”按钮，打开“选定参数”对话框；继续单击“确定”按钮，打开“函数

参数”对话框，如图6-22所示；在第一个参数“Lookup_value”的文本框中输入“A4”。

图6-22 “函数参数”对话框

说明：第一个参数为在数据表首列需要搜索的值，即在“商品编码”列要输入的商品编码，当前的参数应确定为A4。

③在第二个参数“Table_array”的文本框中输入“商品清单!A3:D14”。

说明：第二个参数为需要在其中搜索数据的信息表，即为“商品清单”表中的商品信息区域“A3:D14”。

④在第三个参数“Col_index_num”的文本框中输入“2”。

说明：第三个参数为满足条件的单元格在第二个参数区域的列序号。此处，需要返回的是商品名称，为信息表的第二列。所以，参数值应为“2”。同理，若是返回商品单价，则参数值应应确定为“3”。

⑤单击“确定”按钮；将鼠标移至当前的B4单元格的右下角，鼠标呈现十字架实心的形状，按住鼠标左键并拖动至B15单元格，松开鼠标，如图6-23所示。

图6-23 函数使用报错

说明：此时，由于没有在A列输入商品编码，所以会出现图6-23中所示的提示符号，表示此时函数不可用。可以在“A4:A15”的任意单元格内输入一商品编码，商品名称即可显示。同理，在单价列应用此函数，即可返回商品编码对应的单价，如图6-24所示。

6-24 函数使用正确情况

（4）自动生成购买时间

选中B2单元格，在单元格内输入“=NOW()”，按下回车键“Enter”，即可返回当前的系统时间。

（3）自动计算应收款

根据输入商品的编码、购买的数量，自动计算应收款的操作步骤如下。

①选中E4单元格，在单元格内输入“=C4*D4”，完成后按下回车键“Enter”。

②将鼠标移至当前的E4单元格的右下角，鼠标呈现十字架实心的形状，按住鼠标左键并拖动至E15单元格，松开鼠标。

③输入顾客购买的一种商品编码和数量，例如“A004”、“3”，计算出相应的金额，如图6-25所示。

图6-25 自动计算购买一种商品的金额

④选中B16单元格，插入使用“SUM”函数，“函数参数”设置为“E4:E15”，即可自动计算出应付款。例如，输入某顾客购买的商品编码、购买数量后计算出的商品应付款，如图6-26所示。应付款的大写金额显示在B17单元格中。

图6-26 自动计算应付款

（3）自动计算找零

根据输入的实付款，自动计算找零的操作步骤为：选中B18单元格，在单元格内输入“=E16-B16”，按下回车键“Enter”，即可。至此，收银单制作完毕。

为了提醒收银员，可使用条件格式设置找零。找零为负数时，以红色底纹显示，提示收银员实付款金额不足；找零为正数时，以绿色底纹显示，提示收银员付款成功，按数找零。具体操作步骤如下。

①选中B18单元格后，单击“格式”菜单的“条件格式”菜单项，打开“条件格式”对话框。

②在“条件格式”对话框中，设置条件“单元格数值”、“小于”、“0”；单击“格式”按钮，在“单元格格式”对话框中单击“图案”选项卡，设置红色底纹，如图6-27（a）所示。

③单击“添加”按钮，设置条件2“单元格数值”、“大于等于”、“0”；单击“格式”按钮，在“单元格格式”对话框中单击“图案”选项卡，设置绿色底纹，如图6-27（b）所示。

（a）设置条件1 （b）设置条件2

图6-27 设置“条件格式”

④单击“确定”按钮，完成设置。此时，输入某顾客实付款“40”元后，收银单如图6-28所示。

图6-28 收银单效果图

6.1.3 知识学习

1. Excel的功能

Excel具有制作表格、数据计算处理、数据库、图表4种功能，其中制作表格、数据计算处理为常用的基本功能。在利用Excel进行数据计算处理时，有几百种函数可供使用。当将Excel中某个数据区域作为数据清单（字段+记录）时，可以实现记录的增删改、排序、筛选、分类汇总等数据库的功能。使用Excel中提供的各类图表，可以直观地呈现Excel中的数据。

2．IF函数的使用

（1）语法规则

IF函数执行真假值判断，根据逻辑测试的真假值返回不同的结果，可以使用函数IF对数值和公式进行条件检测。其语法规则为：

IF(Logical_test,Value_if_true,Value_if_false)

（2）参数说明

①Logical_test。表示计算结果为“true”或“false”的任意值或表达式。例如“A10=100”就是一个逻辑表达式，如果单元格A10中的值等于100，表达式即为“true”，否则为“false”，本参数可使用任何比较运算符。

②Value_if_true。“logical_test”为“true”时返回的值，可以是其他公式。

③Value_if_false。logical_test为“false”时返回的值，当然也可以是其他公式。

（3）使用举例

根据学生的成绩打分，判断其评定结果，小于60分评定为“不及格”，大于等于60评定

为“及格”。具体操作步骤如下。

①选中“C2”，打开“函数参数”对话框；设置参数1为条件表达式“B2>=60”，设置条件2的参数为“及格”，设置条件3的参数为“不及格”，如图6-29所示。

②单击“确定”按钮；使用填充柄，将“C2”复制至“C9”单元格，即可完成8位同学的成绩评定。

图6-29 IF函数参数对话框

说明：图6.1.21所示“函数参数”设置的3个条件中，条件1成立时，返回参数2“及格”（返回值为字符时需加上双引号）；若条件1不成立，则返回参数3“不及格”。

需要指出，IF函数不仅可以对一个条件的判断作出两种选择，也可以利用嵌套应用于更复杂的情况。IF函数可以嵌套七层，用Value_if_false 及Value_if_true 参数可以构造复杂的检测条件。例如，给应用举例再增加一个条件，即成绩达到85及以上，评定为优秀。这样就等于有两个条件，会出现三个结果。“函数参数”的设置，如图6-30所示。

图6-30 IF函数嵌套

3．VLOOKUP函数的使用

（1）语法规则

VLOOKUP是一个纵向查找函数。其语法规则为：

VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)

（2）参数说明

①Lookup_value。Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。

②Table_array。Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。

③Col_index_num。Col_index_num为Table_array中待返回的匹配值的列序号。Col_index_num为1时，返回Table_array第一列的数值，Col_index_num为2时，返回table_array 第二列的数值，以此类推。如果Col_index_num小于1，函数VLOOKUP返回错误值#V ALUE!；如果Col_index_num大于Table_array的列数，函数VLOOKUP返回错误值#REF!。

④Range_lookup。Range_lookup为一逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。如果为true或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于Lookup_value 的最大数值。如果Lookup_value 为false，函数VLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A。

（3）使用举例

如图6-31所示，在“A2:F12”区域中提取100003、100004、100005、100007、1000105人的全年总计销量，并对应输入到“I4:I8”中。

图6-31 VLOOKUP函数示例

数据查找时，可以采用一个一个的手动查找操作。但在数据量大的时候将十分繁琐。因此，使用VLOOKUP函数查找将十分便捷。具体操作步骤如下。

选中“I4”单元格，在单元格内输入“=Vlookup(”，Excel提示4个参数。

②第一个参数设置为“H4,”，对应100003；第二个参数设置为“$A$2:$F$12,”，为要查找的区域(绝对引用)；第三个参数设置“6”，为“全年总计”，是区域的第六列。第4个参数不设置，因为要精确的查找工号。

③补全最后的右括号“)”，得到公式“=VLOOKUP(H4,$A$2:$F$12,6)”；使用填充柄，

将“I4”单元格复制“I8”即可完成查找操作。查找结果如图6-32所示。

图6-32 VLOOKUP函数查找结果

6.2 制作超市销售日报表

6.2.1 任务分析

超市销售日报表是对超市一天销售的商品做一个统计分析。报表通常包含商品编码、商品名称、销售时间、销售数量、单价、（销售）金额等信息，数据来自收银单。为便于掌握顾客的购买习惯、商品的受欢迎程度，需要对销售数据按商品编码、销售时间进行排序，并分类汇总、制成图表。超市的管理者根据这些信息提示，可以合理地调配人员，补充适销商品，撤换滞销商品。

制作思路：首先，新建一个Excel工作簿，在工作表中录入当日的商品销售数据，制作“日商品销售明细”表；然后，对表中数据按商品编码、销售时间进行排序；最后，对表中数据进行分类汇总、筛选查看，并制作成图表。

学习的知识点：

●数据排序

●分类汇总

●筛选查看

●制作图表

6.2.2 制作步骤

1. 制作日商品销售明细表

（1）新建一个Excel工作簿

打开Excel，将新工作簿命名为“职院超市销售日报表”，重命名sheet1为“日商品销售明细”，删除sheet2、sheet3。

（2）录入报表数据

根据任务一中的收银单，完成一天超市商品销售信息的录入，如图6-33所示。录入完毕后，单击常用工具栏上的保存按钮，保存录入的数据。

图6-33 职院超市销售日报表

（3）编排格式

将标题行合并居中、文字加粗，给数据清单区域添加边框；根据数据类型，将数据设置成相应的时间型或货币型；调整对齐方式、字体、字号，调整单元格的宽度、高度。编排完成后，单击常用工具栏上的保存按钮，保存编排的格式。编排后的效果，如图6-34所示。

图6-34 编排后的职院超市销售日报表

2. 排序“日商品销售明细”表

（1）选中排序操作的区域

鼠标指向A2单元格，按下鼠标左键拖动到F40单元格，松开鼠标左键，选中排序操作的区域“A2:F40”。

说明：排序是对数据表中数据清单的操作，数据清单是由字段名和每一条记录组成的。在日商品销售明细表中，排序的选定区域应该是“A2:F40”。

（2）打开排序对话框

选择“数据”→“排序”菜单命令，打开“排序”对话框，如图6-35所示。

图6-35 “排序”对话框

（3）设置排序关键字

①单击“主要关键字”组合框，在下拉列表中选择“商品编码”选项，将主要关键字设置为“商品编码”；再单击“升序”前的单选按钮，将排序方式设置为“升序”。

②单击“次要关键字”组合框，在下拉列表中选择“销售时间”选项，在主要关键字相同的情况下将“次要关键字”设置为“销售时间”；再单击“升序”前的单选按钮，将排序方式设置为“升序”，如图6-36所示。

图6-36 确定排序关键字

说明：为了方便查看数据，掌握销售情况，根据任务分析，对表中的数据应该按照商品编码、销售时间进行排序操作。这样，同一种商品的销售信息就在一起了。

（4）完成排序

设置好排序关键字后，单击“排序”对话框中的“确定”按钮，完成排序操作。排序后的日商品销售明细表，如图6-37所示。

图6-37排序后的日商品销售明细表

3. 分类汇总“日商品销售明细”表

（1）选中分类汇总操作的区域

鼠标指向A2单元格，按下鼠标左键拖动到F40单元格，松开鼠标左键，选中分类汇总操作的区域“A2:F40”。

说明：分类汇总也是对整个数据清单的操作。

（2）打开分类汇总对话框

选择“数据”→“分类汇总”菜单命令，打开“分类汇总”对话框，如图6-38所示。

图6-38 “分类汇总”对话框

（3）设置分类汇总选项

①单击“分类字段”组合框的下拉按钮，在下拉选项中选择“商品名称”，将分类字段设为商品名称，方便管理人员查看。

②单击“汇总方式”组合框的下拉按钮，在下拉选项中选择“求和”，将汇总方式设置为“求和”。

③在“选定汇总项”列表中，选中“销售数量”和“金额”前的复选框；接着分别选中“替换当前分类汇总”、“汇总结果显示在数据下方”前的复选框。

（4）完成分类汇总

单击“分类汇总”对话框中的“确定”按钮，完成分类汇总操作。分类汇总后的“日商