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

项目六 超市商品销售管理项目六 超市商品销售管理项目描述现代超市都会利用计算机来帮助完成商品的销售、清点、统计分析以及员工的工资发放。本项目通过制作超市商品清单及收银单、制作超市销售日报表、制作超市月销售额统计表、制作超市员工业绩工资表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行的AD字段分别录入“商品编码”、“商品名称”、“商品单价”、“单位”。 根据职院超市的商品信息，从第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行的AE字段分别录入“商品编码”、“商品名称”、“单价”、“数量”、“金额”。 在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中的数据。2IF函数的使用（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函数嵌套3VLOOKUP函数的使用（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返回错误值#VALUE!；如果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）完成分类汇总单击“分类汇总”对话框中的“确定”按钮，完成分类汇总操作。分类汇总后的“日商品销售明细”表，如图6-39所示。图 6-39 分类汇总效果图（5）分级查看汇总结果 单击数据表左上方的“2”，查看每种商品的销售总量，如图6-40所示。图 6-40 每种商品的销售汇总 单击数据表左上方的“1”，查看商品销售总量，如图6-41所示。图 6-41 商品销售总量汇总4. 筛选查看“日商品销售明细”表（1）设置筛选方式选择“数据”“筛选”“自动筛选”菜单命令，设置筛选方式为“自动筛选”，如图6-42所示。图 6-42 设置“自动筛选”（2）查看自动筛选结果单击字段名称后的下拉按钮，例如“商品名称”后的下拉按钮；在下拉选项中选择要查看的选项，例如“康师傅老坛酸菜面”；查看“康师傅老坛酸菜面”的销售情况，如图6-43所示。图 6-43 查看“康师傅老坛酸菜面”的销售情况（3）查看自定义筛选结果 单击字段名称后的下拉按钮，例如“销售时间”的下拉按钮，打开下拉选项，如图6-44所示。图 6-44 打开“销售时间”下拉选项 在下拉选项中，选择“自定义”选项，打开“自定义自动筛选方式”对话框，如图6-45所示。图 6-45 “自定义自动筛选方式”对话框 在“销售时间”选项中，单击第一行左边的下拉按钮，在下拉选项中选择“大于或等于”；再单击第一行右边的组合框，输入时间，例如“9:30”。 选中“与”单选按钮；单击第二行左边的下拉按钮，在下拉选项中选择“小于或等于”；再单击第二行右边的组合框，输入时间，例如“9:50”。 单击“确定”按钮，查看到“9:309:50”时间段的销售情况如图6-46所示。图 6-46 自定义筛选结果5. 制作商品销售日报图表将“日商品销售明细”表分类汇总后，就已经完成了“超市销售日报表”的制作。为了更直观地显示“日商品销售”情况，还可以利用Excel的图表功能制作商品销售日报图表。具体操作步骤如下。（1）打开图表向导 单击“超市销售日报表”左上方的数字“2”，选中“A2:F47”区域，单击菜单栏上的“插入”菜单，如图6-47所示。图 6-47 打开“图表”菜单命令 单击“图表”菜单命令，打开“图表向导”对话框，如图2-48所示。图 6-48“图表向导”对话框（2）创建图表 单击“标准类型”选项卡，在“图表类型”列表中单击“柱形图”，单击“下一步”按钮，如图6-49所示。图 6-49“图表向导”之二 单击“系列”选项卡，如图6-50所示；选中“系列”列表中“销售时间”，单击“删除”按钮；再选中“单价”，单击“删除”按钮。图 6-50 “系列”选项卡 单击“下一步”按钮，再单击“下一步”按钮，如图6-51所示。图 6-51 “图表向导”之4 单击“完成”按钮，完成“商品销售日报图表”制作，如图6-52所示。图 6-52 商品销售日报图表6.2.3 知识学习1. Excel数据表建立准则在Excel中，可以把数据表（或称数据清单）作为数据库来处理，实现排序、分类汇总、筛选等数据库功能。为了使Excel能容易地对数据表进行处理，建立数据表时应注意以下准则。 一张工作表只建立一张数据表。 工作表的第一行建立各列标题，列标题使用的字体、格式应和下面的数据相区别。 同一列数据的类型应一致。 工作中的数据区不出现空白行和列。2. 数据排序对某些数据表，有时需要按字段的大小进行排序。排序的依据字段称为“关键字”，例如，对“日商品销售明细”表排序时的“商品编码”、“销售时间”等。Excel默认依据列字段排序，支持单个关键字排序（即简单排序），也支持多个关键字排序（即复杂排序），但最多支持三个关键字排序。排序时，英文字符按字母顺序排序，而汉字默认按拼音顺序排序。（1）简单排序实现简单排序的方法是：单击排序所依据列中的任一单元格，根据需要单击“常用”工具栏中的“升序”或“降序”按钮。简单排序也可以用“排序”对话框完成。（2）复杂排序复杂排序是根据多字段的内容对数据清单进行排序，即排序所依据的第一关键字（主要关键字）内容相同时，再按第二关键字（次要关键字）进行排序，第二关键字也相同时，再按第三关键字进行排序。复杂排序要使用“排序”对话框完成。如果数据表需按行排序或汉字笔画排序，要修改Excel的默认设置。修改方法是：单击“排序”对话框中的“选项”按钮，打开“排序选项”对话框，如图6-53所示；在“排序选项”对话框中，修改排序方向和方法，然后单击“确定”按钮即可。图 6-53 “排序选项”对话框2. 分类汇总分类汇总就是按数据清单的某列字段对记录进行分类，将列字段值相同的连续记录分为一组，并可以对各组数据进行求和、计数、求平均值、求最大值等汇总计算。（1）插入分类汇总在分类汇总前，应按分类所依据的列进行排序。这样才能确保列字段值相同的记录是连续的，汇总才能成功。插入分类汇总的方法如下。首先，单击“数据”菜单，在下拉选项中单击“分类汇总”菜单命令，打开“分类汇总”对话框。然后，在对话框中，选择“分类字段”，例如“日商品销售明细”表中的“商品名称”；选择“汇总方式”，例如“日商品销售明细”表中的“求和”；设置“选定汇总项”，例如“日商品销售明细”表中的“销售数量”、“金额”。最后，单击“确定”按钮，分类汇总的结果随之显示出来，完成分类汇总。（2）删除分类汇总插入分类汇总后，再次单击“数据”菜单中“分类汇总”菜单命令，打开“分类汇总”对话框中；在“分类汇总”对话框中单击“全部删除”命令，即可删除分类汇总，使数据清单恢复原状。3. 数据筛选在实际应用中，经常需要在一个数据清单中查找满足特定条件的记录，而筛选就是查找的一种快捷有效的方法。筛选可以显示满足条件的行，而不满足条件的会被暂时隐藏。Excel提供两类筛选数据清单的方式，分别是自动筛选和高级筛选。（1） 自动筛选单击数据清单中任意单元格，选择“数据”“筛选”“自动筛选”菜单命令，进入自动筛选状态。此时，每列的标题名右端都会有“全部”、“前10个”、“自定义”和当前列中所出现的值等选项。在这些选项中，单击所需的选项，即可进行自动筛选查看。（2） 高级筛选使用高级筛选，可以应用较复杂的条件来筛选数据清单。与自动筛选不同的是，使用高级筛选需要在数据清单之外另建一个条件区域。条件区域可以建在数据清单的上方、下方、左侧或右侧，但与数据清单间必须至少要保留一个空行或空列。例如，筛选英语、数学和计算机成绩都及格的学生，操作步骤如下。 建立条件区域。在与数据清单空一行或一列的空白区域输入英语、数学和计算机字段名，在对应的字段名下一单元格输入条件“=60”，如图6-54所示。图 6-54 创建条件区域 单击数据清单中任意单元格，然后选择“数据”“筛选”“高级筛选”菜单命令，打开“高级筛选”对话框，如图6-55所示。图 6-55 “高级筛选”对话框 在对话框中，根据要求单击“方式”下的单选按钮，选中所需的方式。在“条件区域”中，输入条件区域的引用；或单击右端的拾取器按钮，隐藏对话框，再在工作表中使用鼠标拖动选中条件区域。最后，单击“确定”按钮，完成高级筛选。筛选结果，如图6-56所示。图 6-56 高级筛选效果图说明：在“列表区域”中，Excel会自动识别出数据区域。如果单击了“方式”下的“将筛选结果复制到其他位置”单选按钮，则“复制到”激活变黑，用“条件区域”的类似操作确定复制到的位置，然后单击“确定”按钮完成筛选。高级筛选后，选择“数据”“筛选”“全部显示”命令，可显示高级筛选隐藏的数据。4. 图表Excel中，可以用图表的方式显示工作表中数据。图表具有较好的视觉效果，可方便用户查看数据之间的差异和趋势。Excel为用户提供了丰富的图表类型，如柱形图、折线图、饼图等。（1）创建图表创建图表最常用的方法是使用图表向导。例如，在“学生成绩表”中创建显示“姓名”、“计算机成绩”图表的操作步骤如下。 在“学生成绩表”中，拖动鼠标选中“B2:B8”；按下“Ctrl”键不要松开，再拖动鼠标选中“E2:E8”。选中的“B2:B8”和“E2:E8”不连续区域作为要创建图表的数据源。 单击“常用”工具栏中的“图标向导”按钮或选择“插入”“图表”菜单命令，打开图表向导对话框，如图6-57所示。图 6-57 “图表向导”对话框 在“标准类型”选项卡中，选择图表的类型，例如“柱形图”“簇状柱形图”；然后，单击“下一步”按钮，打开“图表向导”步骤2对话框，如图6-58所示。 图 6-58 “图表向导”步骤2对话框在“标准类型”选项卡中，有多种图表类型可供选择，如“柱形图”、“饼图”等。每一种图表类型还细分出若干子类型，可根据需要进行选择它们。如果对提供的图表类型不满意，还可以在“自定义类型”选项卡中自行选择所需的图表类型。 在图表向导第2步中，定义图表的数据源；在“系列”选项卡中，设置图表中显示的分类轴标志；设置完成后，单击“下一步”按钮，打开“图表向导”步骤3对话框，如图6-59所示。 图 6-59 “图表向导”步骤3对话框一般情况下，Excel会自动识别出图表的数据源。如果有误，可以单击“数据区域”框右端的拾取器按钮，隐藏对话框；然后，在工作表中选择数据源区域，并拖到隐藏的对话框。 在图表向导第3步中，设置图表的“标题”、“数值轴”、“网格线”、“图例”、“数据表”等选项，例如在“标题”选项卡的“图表标题”中输入“学生计算机成绩图表”、“数值轴”中输入“成绩”，在“图例”选项卡中取消“显示图例”的复选；设置完成后，单击“下一步”按钮，打开“图表向导”步骤4对话框，如图6-60所示。图 6-60 “图表向导”步骤4对话框 在图表向导第4步中，选择图表插入位置，例如选中“作为其中的对象插入”；然后，单击“完成”按钮，完成图表创建，如图6-61所示。图 6-61 图表效果图（2）更改图表设置图表创建后，还可以根据需要更改图表的类型、数据源、图表选项和位置设置。更改的方法是：在图表上单击鼠标右键，在快捷菜单中选择“图表类型”、“图表选项”等选项进行更改；或者单击图表选中后，选择“插入”“图表”菜单命令，在打开的对话框中进行更改。例如，将“学生成绩表”图表类型更改为“折线图“、图例靠右显示的操作步骤如下。 在如图6-61所示的图表上单击鼠标右键，打开快捷菜单，如图6-62所示；单击“图表类型”，在打开的对话框中选择“折线图”。 图 6-62 图表向导步骤4对话框 再在图表上单击鼠标右键，单击“图表选项”，在打开的对话框中选择“图例”选项卡，选中“显示图例”，并选中“靠右”；单击“完成”按钮，完成图表更改。（3）格式化图表Excel图表是由图表区、图表标题、图例、绘图区、系列和数据标志等对象组成。可以通过鼠标右击图表区，在打开的快捷菜单中单击“图表区格式”菜单命令，打开“图表区格式”对话框，如图6-63所示；在对话框中，设置字体、边框、填充色等格式化图表。图 6-63 “图表区格式”对话框6.3 制作超市月销售额统计表6.3.1 任务分析超市以多分店方便顾客赢得了市场。为及时核算成本，高效地调度资金，需要对各个分店的月销售额进行统计、分析。统计时，通常每个分店制作一张统计表，然后将各分店的销售数据合并，完成整个超市系统当月商品销售情况的统计、分析，并根据各分店的销售业绩核算出奖金额。为避免月底集中统计造成工作量的不平衡，或每天统计造成工作效率低下，可分上、中、下三旬对每个月各分店的销售情况进行统计。制作思路：首先，创建各类工作表，如“一分店”月销售额统计表、“合并计算表”、“各店销售业绩统计表”等；然后，对各个分店的月销售额进行统计、分析，制成图表；最后，将各分店的月销售情况进行合并计算，并核算出各分店的奖金额。学习的知识点： 格式排版 合并计算 跨表引用6.3.2 制作步骤1. 创建各类工作表（1）创建各分店月销售额统计表 新建工作簿，命名为“职院超市月销售额统计表”；分别将Sheet1、Sheet2、Sheet3重命名为：“一分店”、“二分店”、“三分店”。 单击“一分店”，参照图6-64所示，完成一分店月销售额统计表的创建。图 6-64 一分店月销售额统计表 选中“A1:L16”区域，将“职院一分店月销售额统计表”的内容复制到“二分店”、“三分店”，并分别将标题中的“一分店”修改为“二分店”、“三分店”。 单击“保存”按钮，完成各分店月销售额统计表的创建。（2）创建合并计算表 鼠标右键单击“一分店”工作表，在快捷菜单中单击“移到或复制工作表”，打开“移到或复制工作表”对话框，如图6-65所示。图 6-65 “移到或复制工作表”对话框 单击“（移至最后）”；选中 “建立副本”复选框，再单击“确定”按钮。 重命名“一分店2”为“合并计算表”，将标题修改为“职院超市各分店月销售额统计总表”，如图6-66所示。图 6-66 合并计算表 单击“保存”按钮，完成合并计算表的创建。（3）创建各店销售业绩统计表 单击“合并计算表”，选择“插入”“工作表”菜单命令，在“合并计算表”插入一张新工作表；将新工作表重命名为“各店销售业绩统计表”，鼠标左键按住“各店销售业绩统计表”拖动到最后，松开鼠标左键。 参照图6-67所示，完成“各店销售业绩统计表”创建后，单击“保存”按钮，保存创建的工作表。图 6-67 各分店销售业绩统计表2. 统计各分店月销售额（1）统计一分店月销售额 根据一分店提交的商品销售日报表，分上、中、下三旬完成当月各商品销售数量的录入，如图6-68所示。图 6-68 录入好销售数量的工作表 利用Excel中的公式、函数，计算“娃哈哈矿泉水”上旬销售金额，如图6-69所示；利用填充柄复制公式到其他单元格，计算出上旬所有商品的销售金额。中、下旬销售金额用同样的方法计算出。图 6-69 计算“娃哈哈矿泉水”上旬销售金额 计算“娃哈哈矿泉水”的月销售总数，如图6-70所示；利用填充柄复制公式到其他单元格，计算出所有商品的月销售总数。图 6-70 计算“娃哈哈矿泉水”月销售总数 计算“娃哈哈矿泉水”的月销售总额，如图6-71所示；利用填充柄复制公式到其他单元格，计算出所有商品的月销售总额。图 6-71 计算“娃哈哈矿泉水”月销售总额 计算上旬销售金额合计值，如图6-72所示；用同样的方法，计算出中、下旬销售金额合计值及销售总额合计值，如图6-73所示。图 6-72 计算上旬销售金额合计值图 6-73 计算销售总额合计值 选中“商品名称”、“销售总数”两列，打开“图表向导”对话框，如图6-74所示；在向导指引下，制作“商品月销售总数”图表，如图6-75所示。图 6-74 创建销售总数图表图 6-75 一分店商品月销售总数图表 选中“商品名称”、“销售总额”两列，打开“图表向导”对话框，如图6-76所示；在向导指引下，制作“商品月销售总额”图表，如图6-77所示。图 6-76 创建销售总额图表图 6-77 一分店商品月销售总额图表（2）统计二、三分店月销售额按照一分店月销售额统计及分析的方法，完成二、三分店月销售额的统计及分析。3. 统计超市月销售额（1）合并计算 单击“合并计算表”，选中“E4:L16”区域，如图6-78所示。图 6-78 打开“合并计算表” 选择“数据”“合并计算”菜单命令，打开“合并计算”对话框，如图6-79所示。图 6-79 “合并计算”对话框 单击“一分店”工作表，选中一分店月销售额统计表中的“E4:L16”区域，如图6-80所示。图 6-80 选择“引用位置” 单击“合并计算”对话框中的“添加”按钮，将一分店的“E4:L16”区域添加到引用位置；用同样的方法，添加二、三分店月销售额统计表中的“E4:L16”区域；选中 “标签位置”的“创建连至源数据的链接”复选框，如图6-81所示。图 6-81 添加所有引用位置 单击“确定”按钮，完成三个分店月销售的合并计算，如图6-82所示。图 6-82 合并计算后结果 单击左侧的“+”号，查看每个分店的销售数据，如图6-83所示。图 6-83 查看各分店数据（2）创建超市月销售图表 在合并计算表中，选中“商品名称”、“销售总数”两列，打开“图表向导”对话框，在向导指引下，制作“超市商品月销售总数”图表，如图6-84所示。 选中“商品名称”、“销售总额”两列，打开“图表向导”对话框，在向导指引下，制作“超市商品月销售总额”图表，如图6-84所示。图 6-84 超市系统月销售图表4. 核算奖金基数（1）引用月销售额 单击“各店销售业绩统计表”，选中D3单元格，在D3单元格内输入“=一分店!L16”，如图6-85（a）所示；按下“Enter”键，将“一分店”表中L16单元格的月销售总额引用到D3单元格，如图6-85（b）所示。 （a） 数据跨表引用 （b） 引用结果图 6-85 引用一分店月销售额 同理，选中D4单元格，在D4单元格内输入“=二分店!L16”，将“二分店”表中L16单元格的月销售总额引用到D4单元格；选中D5单元格，在D5单元格内输入“=三分店!L16”，将“三分店”表中L16单元格的月销售总额引用到D5单元格。完成引用后如图6-86所示。图 6-86 引用各分店月销售额（2）计算奖金基数 选中E3单元格，在E3单元格内输入“D3*2%”， 按下“Enter”键，计算出一分店奖金基数。 利用填充柄复制公式到E4、E5单元格，计算出二、三分店奖金基数，如图6-87所示。图 6-87 奖金核算结果6.3.3 知识学习1. 合并计算一个公司内可能有很多的销售地区或者分公司，各个分公司具有各自的销售报表和会计报表，为了对整个公司的所有情况进行全面的了解，就要将这些分散的数据进行合并，从而得到一份完整的销售统计报表或者会计报表。利用Excel提供的合并计算功能，可快捷地完成这些数据汇总工作。（1）基本概念合并计算是指，将多个源区中的数据汇总为一个数据的计算方法。Excel 提供了两种合并计算的方法。一是通过位置合并计算，适用于源区中的数据源具有相同的对应位置数据的汇总。二是通过分类合并计算，适用于源区中的数据没有相同的布局时数据的汇总。 要想合并计算，必须先为汇总信息定义一个目的区，用来显示摘录的信息。此目标区域可位于与源相同的工作表上，也可以在另一个工作表上或工作簿内。然后，选择要合并计算的源。此源可以来自单个工作表、多个工作表或多重工作簿中。 在Excel中，可以最多指定255个源区域来进行合并计算。在合并计算时，不需要打开包含源区域的工作簿。（2） 通过位置合并计算 通过位置来合并计算非常适用于处理相同表格的数据汇总。例如，将超市的多家分店数据合并形成一个完整的超市报表，将不同地区的税务报表合并形成一个市的总税务报表等。用这种方式合并计算时，要求：同一类数据在所有源区域的数据表中处于相同的位置。（3）通过分类合并计算设分类合并计算前的原始数据，位于Sheet2工作表中，如图6-88所示。汇总各个产品总销售量的操作如下。图 6-88某公司销售表 在Sheet2工作表中，单击“A16”单元格，将光标定位于A16，存放汇总的第一个数据；然后，选择“数据”“合并计算”菜单命令，打开“合并计算”对话框。 单击“引用位置”，选中原始数据表中的“B1:C1