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

项目六超市商品销售管理项目描述现代超市都会利用计算机来帮助完成商品的销售、清点、统计分析以及员工的工资发放。本项目通过制作超市商品清单及收银单、制作超市销售日报表、制作超市月销售额统计表、制作超市员工业绩工资表4个任务，完成简易的超市商品销售管理系统的构建，实现超市商品销售的计算机管理。1制作超市商品清单及收银单制作超市商品清单、收银单的示例如图61、62所示。完成该任务除了需要在项目五中已学会的EXCEL工作表创建、数据编辑等知识和技能，还要进一步学会EXCEL工作表格式排版、公式与函数使用的知识和技能。图61超市商品清单图62超市收银单2制作超市销售日报表制作超市销售日报表的示例如图63、64、65所示。完成该任务需要学会排序、筛选以及分类汇总的知识与技能。图63商品日销售明细表图64商品日销售明细（分类汇总）图65商品日销售明细（图表分析）3制作超市月销售额统计表制作超市月销售额统计表的示例如图66、67、68所示。完成该任务需要学会合并计算、跨表引用的知识与技能。图66商品月销售统计表（图表分析）图67商品月销售统计表（合并计算）图68商品月销售业绩统计表4制作超市员工工资表制作超市员工工资表的示例如图69所示。完成该任务需要学会绝对引用、公式与函数综合应用的知识与技能。图69超市员工工资表61制作超市商品清单及收银单611任务分析商品清单就是一个EXCEL表，表中通常包含商品的编码、名称、单价等信息，创建一个新EXCEL表后，录入商品的编码、名称、单价等信息，即完成商品清单的制作。在超市购物时，可发现收银员要做的工作是输入商品编码、输入购买数量、输入实付款，商品的名称、单价、应收款、找零等信息是自动生成的。因此，在收银单制作时，除了包含商品的编码、名称、单价等信息外，还要包含交易时间、交易数量、应收款、实付款、找零等信息，并且根据收银员输入的数据能自动生成所需的数据。制作思路首先，新建一个EXCEL工作簿，在工作表1中录入商品信息，进行相应的格式排版，制作出商品清单；然后，在工作表2中，制作出收银单的雏形；最后，根据输入的商品编码自动生成商品名称及单价，根据输入的购买数量自动计算出金额及应收款，根据输入的实付款自动计算出找零。学习的知识点数据录入格式排版公式函数612制作步骤1制作商品清单（1）新建一个EXCEL工作簿选择“开始”“所有程序”“MICROSOFTOFFICE”“MICROSOFTOFFICEEXCEL2003”菜单命令，新建一个EXCEL工作簿。将EXCEL工作簿命名为“职院超市收银系统”，将SHEET1重命名为“商品清单”、SHEET2重命名为“收银单”，删除SHEET3，如图610所示。图610新建工作簿（2）录入商品清单数据单击“商品清单”，在第1行录入标题“职院超市货架商品清单”，在第2行的AD字段分别录入“商品编码”、“商品名称”、“商品单价”、“单位”。根据职院超市的商品信息，从第3行起依次完成各商品信息的录入，如图611所示。单击常用工具栏上的保存按钮，保存录入的文字信息。图611职院超市货架商品清单（3）设置标题格式选中标题行“职院超市货架商品清单”要合并的单元格区域“A1D1”，单击工具栏上的“合并及居中”按钮，将标题行合并及居中显示，如图612所示。图612设置“商品清单”标题格式选中标题文字“职院超市货架商品清单”，将标题文字设置为“黑体”、“14号”、“加粗”。（4）设置字段名称格式选中字段名称行区域“A2D2”，单击格式工具栏上的“加粗”按钮，将字段名称设置为加粗。接着选择“格式”“单元格”菜单命令，打开“单元格格式”对话框；单击“单元格格式”对话框中“图案”选项卡，单击“灰色40”，如图613所示。单击“确定”按钮，将字段名称填充为灰色底纹。图613“单元格格式”对话框的“图案”选项卡（5）设置数据区域格式选中数据区域“A2D14”，选择“格式”“单元格”菜单命令，打开“单元格格式”对话框。单击“单元格格式”对话框中“边框”选项卡，将外边框设置为蓝色双线，内边框设置为红色单线，如图614（A）所示。单击“确定”按钮，完成数据区域的边框设置，如图614（B）所示。（A）“单元格格式”对话框的“边框”选项卡（B）设置效果图614设置数据清单区域格式（6）商品清单设置鼠标左键单击左上角行标号和列标号的交叉处，选中整个工作表；然后，选择“格式”“单元格”菜单命令，打开“单元格格式”对话框；单击“单元格格式”对话框中的“对齐”选项卡，如图615所示；将“水平对齐”、“垂直对齐”都设置为“居中”后，单击“确定”按钮。图615“单元格格式对话框”的“对齐”选项卡将鼠标移动到行标号或列标号之间的交叉处，在鼠标变成双向箭头时按住左键上下拖动，调整行高；将鼠标移动到列标号之间的交叉处，在鼠标变成双向箭头时按住左键左右拖动，调整列宽。选中“C3C14”区域，选择“格式”“单元格”菜单命令，打开“单元格格式”对话框；单击“单元格格式”对话框中的“数字”选项卡，在“分类”选项中单击“货币”，在“小数位数”文本框中输入“2”，在“货币符号”组合框的下拉选项中单击人民币符号“”，如图616所示；最后，单击“确定”按钮，完成设置。图616“单元格格式”对话框的“数字”选项卡2制作收银单雏形（1）录入收银单中的文字信息单击“收银单”，在第1行录入标题“职院超市收银单”，在第2行录入“交易时间”，在第3行的AE字段分别录入“商品编码”、“商品名称”、“单价”、“数量”、“金额”。在A16录入“应收款”，在D16录入“实付款”，在A17录入“大写”，在A18录入“找零”，在A19录入“货款请当面点清职院超市欢迎您再次光临”，如图617所示。单击常用工具栏上的保存按钮，保存录入的文字信息。图617制作收银单（2）设置格式选中标题行“职院超市收银单”要合并的单元格区域“A1E1”，单击工具栏上的“合并及居中”按钮，将标题行合并及居中显示，如图618所示；选中“职院超市收银单”，将标题行文字设置为“黑体”、“14号”、“加粗”；选中“A19E19”，单击工具栏上的“合并及居中”按钮，将“货款请当面点清职院超市欢迎您再次光临”设置为两行显示。图618设置“收银单”标题格式选中“A2E3”区域，打开“单元格格式”对话框中的“边框”选项卡；在“预置”中单击“内部”选项，在“样式”中单击“虚线”选项，在“边框”中分别单击“上横线”、“中横线”、“下横线”选项，如图619所示；单击“确定”按钮。图619设置横虚线选中“B2E2”区域，单击工具栏上的“合并及居中”按钮，将交易发生的时间值居中显示。选中字段名称行区域“A3E3”，单击格式工具栏上的“加粗”按钮；接着单击工具栏上的“填充颜色”按钮，将底纹填充为“灰色40”。选中“A3E15”区域，打开“单元格格式”对话框中的“边框”选项卡；在“边框”中单击“中竖线”选项，单击“确定”按钮。选中“A15E15”区域，打开“单元格格式”对话框中的“边框”选项卡；在“边框”中单击“下横线”选项，单击“确定”按钮。选中“A16C17”区域，打开“单元格格式”对话框中的“边框”选项卡；在“边框”中单击“中横线”选项，单击“确定”按钮。选中“A17E18”区域，打开“单元格格式”对话框中的“边框”选项卡；在“边框”中分别单击“中横线”、“下横线”选项，单击“确定”按钮。选中“B16C16”，单击工具栏上的“合并及居中”按钮；选中“B17C17”，单击工具栏上的“合并及居中”按钮；选中“D16D17”，单击工具栏上的“合并及居中”按钮；选中“E16E17”，单击工具栏上的“合并及居中”按钮；选中“B18E18”，单击工具栏上的“合并及居中”按钮。选中B16，打开“单元格格式”对话框中的“数字”选项卡，在“分类”选项中单击“货币”，在“小数位数”文本框中输入“2”，在“货币符号”组合框的下拉选项中单击人民币符号“”，单击“确定”按钮；选中E16，用同样的方法设置其货币格式。选中B17，在单元格应用公式“B16”，并在“分类”选项中单击“特殊”，在“类型”中单击“中文大写数字”，如图620所示；最后，单击“确定”按钮。图620设置“中文大写数字”3在收银单中使用公式与函数（1）自动生成商品名称、单价在A4A15区域内任一单元格输入商品编码，相对应商品清单表中的商品名称、单价自动显示在收银单中的操作步骤如下。选中B4单元格，单击“插入函数”按钮，打开“插入函数”对话框，如图621所示；在“选择函数”中找到“VLOOKUP”函数。图621打开“插入函数”对话框单击“确定”按钮，打开“选定参数”对话框；继续单击“确定”按钮，打开“函数参数”对话框，如图622所示；在第一个参数“LOOKUP_VALUE”的文本框中输入“A4”。图622“函数参数”对话框说明第一个参数为在数据表首列需要搜索的值，即在“商品编码”列要输入的商品编码，当前的参数应确定为A4。在第二个参数“TABLE_ARRAY”的文本框中输入“商品清单A3D14”。说明第二个参数为需要在其中搜索数据的信息表，即为“商品清单”表中的商品信息区域“A3D14”。在第三个参数“COL_INDEX_NUM”的文本框中输入“2”。说明第三个参数为满足条件的单元格在第二个参数区域的列序号。此处，需要返回的是商品名称，为信息表的第二列。所以，参数值应为“2”。同理，若是返回商品单价，则参数值应应确定为“3”。单击“确定”按钮；将鼠标移至当前的B4单元格的右下角，鼠标呈现十字架实心的形状，按住鼠标左键并拖动至B15单元格，松开鼠标，如图623所示。图623函数使用报错说明此时，由于没有在A列输入商品编码，所以会出现图623中所示的提示符号，表示此时函数不可用。可以在“A4A15”的任意单元格内输入一商品编码，商品名称即可显示。同理，在单价列应用此函数，即可返回商品编码对应的单价，如图624所示。624函数使用正确情况（4）自动生成购买时间选中B2单元格，在单元格内输入“NOW”，按下回车键“ENTER”，即可返回当前的系统时间。（3）自动计算应收款根据输入商品的编码、购买的数量，自动计算应收款的操作步骤如下。选中E4单元格，在单元格内输入“C4D4”，完成后按下回车键“ENTER”。将鼠标移至当前的E4单元格的右下角，鼠标呈现十字架实心的形状，按住鼠标左键并拖动至E15单元格，松开鼠标。输入顾客购买的一种商品编码和数量，例如“A004”、“3”，计算出相应的金额，如图625所示。图625自动计算购买一种商品的金额选中B16单元格，插入使用“SUM”函数，“函数参数”设置为“E4E15”，即可自动计算出应付款。例如，输入某顾客购买的商品编码、购买数量后计算出的商品应付款，如图626所示。应付款的大写金额显示在B17单元格中。图626自动计算应付款（3）自动计算找零根据输入的实付款，自动计算找零的操作步骤为选中B18单元格，在单元格内输入“E16B16”，按下回车键“ENTER”，即可。至此，收银单制作完毕。为了提醒收银员，可使用条件格式设置找零。找零为负数时，以红色底纹显示，提示收银员实付款金额不足；找零为正数时，以绿色底纹显示，提示收银员付款成功，按数找零。具体操作步骤如下。选中B18单元格后，单击“格式”菜单的“条件格式”菜单项，打开“条件格式”对话框。在“条件格式”对话框中，设置条件“单元格数值”、“小于”、“0”；单击“格式”按钮，在“单元格格式”对话框中单击“图案”选项卡，设置红色底纹，如图627（A）所示。单击“添加”按钮，设置条件2“单元格数值”、“大于等于”、“0”；单击“格式”按钮，在“单元格格式”对话框中单击“图案”选项卡，设置绿色底纹，如图627（B）所示。（A）设置条件1（B）设置条件2图627设置“条件格式”单击“确定”按钮，完成设置。此时，输入某顾客实付款“40”元后，收银单如图628所示。图628收银单效果图613知识学习1EXCEL的功能EXCEL具有制作表格、数据计算处理、数据库、图表4种功能，其中制作表格、数据计算处理为常用的基本功能。在利用EXCEL进行数据计算处理时，有几百种函数可供使用。当将EXCEL中某个数据区域作为数据清单（字段记录）时，可以实现记录的增删改、排序、筛选、分类汇总等数据库的功能。使用EXCEL中提供的各类图表，可以直观地呈现EXCEL中的数据。2IF函数的使用（1）语法规则IF函数执行真假值判断，根据逻辑测试的真假值返回不同的结果，可以使用函数IF对数值和公式进行条件检测。其语法规则为IFLOGICAL_TEST,VALUE_IF_TRUE,VALUE_IF_FALSE（2）参数说明LOGICAL_TEST。表示计算结果为“TRUE”或“FALSE”的任意值或表达式。例如“A10100”就是一个逻辑表达式，如果单元格A10中的值等于100，表达式即为“TRUE”，否则为“FALSE”，本参数可使用任何比较运算符。VALUE_IF_TRUE。“LOGICAL_TEST”为“TRUE”时返回的值，可以是其他公式。VALUE_IF_FALSE。LOGICAL_TEST为“FALSE”时返回的值，当然也可以是其他公式。（3）使用举例根据学生的成绩打分，判断其评定结果，小于60分评定为“不及格”，大于等于60评定为“及格”。具体操作步骤如下。选中“C2”，打开“函数参数”对话框；设置参数1为条件表达式“B260”，设置条件2的参数为“及格”，设置条件3的参数为“不及格”，如图629所示。单击“确定”按钮；使用填充柄，将“C2”复制至“C9”单元格，即可完成8位同学的成绩评定。图629IF函数参数对话框说明图6121所示“函数参数”设置的3个条件中，条件1成立时，返回参数2“及格”（返回值为字符时需加上双引号）；若条件1不成立，则返回参数3“不及格”。需要指出，IF函数不仅可以对一个条件的判断作出两种选择，也可以利用嵌套应用于更复杂的情况。IF函数可以嵌套七层，用VALUE_IF_FALSE及VALUE_IF_TRUE参数可以构造复杂的检测条件。例如，给应用举例再增加一个条件，即成绩达到85及以上，评定为优秀。这样就等于有两个条件，会出现三个结果。“函数参数”的设置，如图630所示。图630IF函数嵌套3VLOOKUP函数的使用（1）语法规则VLOOKUP是一个纵向查找函数。其语法规则为VLOOKUPLOOKUP_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）使用举例如图631所示，在“A2F12”区域中提取100003、100004、100005、100007、1000105人的全年总计销量，并对应输入到“I4I8”中。图631VLOOKUP函数示例数据查找时，可以采用一个一个的手动查找操作。但在数据量大的时候将十分繁琐。因此，使用VLOOKUP函数查找将十分便捷。具体操作步骤如下。选中“I4”单元格，在单元格内输入“VLOOKUP”，EXCEL提示4个参数。第一个参数设置为“H4,”，对应100003；第二个参数设置为“A2F12,”，为要查找的区域绝对引用；第三个参数设置“6”，为“全年总计”，是区域的第六列。第4个参数不设置，因为要精确的查找工号。补全最后的右括号“”，得到公式“VLOOKUPH4,A2F12,6”；使用填充柄，将“I4”单元格复制“I8”即可完成查找操作。查找结果如图632所示。图632VLOOKUP函数查找结果62制作超市销售日报表621任务分析超市销售日报表是对超市一天销售的商品做一个统计分析。报表通常包含商品编码、商品名称、销售时间、销售数量、单价、（销售）金额等信息，数据来自收银单。为便于掌握顾客的购买习惯、商品的受欢迎程度，需要对销售数据按商品编码、销售时间进行排序，并分类汇总、制成图表。超市的管理者根据这些信息提示，可以合理地调配人员，补充适销商品，撤换滞销商品。制作思路首先，新建一个EXCEL工作簿，在工作表中录入当日的商品销售数据，制作“日商品销售明细”表；然后，对表中数据按商品编码、销售时间进行排序；最后，对表中数据进行分类汇总、筛选查看，并制作成图表。学习的知识点数据排序分类汇总筛选查看制作图表622制作步骤1制作日商品销售明细表（1）新建一个EXCEL工作簿打开EXCEL，将新工作簿命名为“职院超市销售日报表”，重命名SHEET1为“日商品销售明细”，删除SHEET2、SHEET3。（2）录入报表数据根据任务一中的收银单，完成一天超市商品销售信息的录入，如图633所示。录入完毕后，单击常用工具栏上的保存按钮，保存录入的数据。图633职院超市销售日报表（3）编排格式将标题行合并居中、文字加粗，给数据清单区域添加边框；根据数据类型，将数据设置成相应的时间型或货币型；调整对齐方式、字体、字号，调整单元格的宽度、高度。编排完成后，单击常用工具栏上的保存按钮，保存编排的格式。编排后的效果，如图634所示。图634编排后的职院超市销售日报表2排序“日商品销售明细”表（1）选中排序操作的区域鼠标指向A2单元格，按下鼠标左键拖动到F40单元格，松开鼠标左键，选中排序操作的区域“A2F40”。说明排序是对数据表中数据清单的操作，数据清单是由字段名和每一条记录组成的。在日商品销售明细表中，排序的选定区域应该是“A2F40”。（2）打开排序对话框选择“数据”“排序”菜单命令，打开“排序”对话框，如图635所示。图635“排序”对话框（3）设置排序关键字单击“主要关键字”组合框，在下拉列表中选择“商品编码”选项，将主要关键字设置为“商品编码”；再单击“升序”前的单选按钮，将排序方式设置为“升序”。单击“次要关键字”组合框，在下拉列表中选择“销售时间”选项，在主要关键字相同的情况下将“次要关键字”设置为“销售时间”；再单击“升序”前的单选按钮，将排序方式设置为“升序”，如图636所示。图636确定排序关键字说明为了方便查看数据，掌握销售情况，根据任务分析，对表中的数据应该按照商品编码、销售时间进行排序操作。这样，同一种商品的销售信息就在一起了。（4）完成排序设置好排序关键字后，单击“排序”对话框中的“确定”按钮，完成排序操作。排序后的日商品销售明细表，如图637所示。图637排序后的日商品销售明细表3分类汇总“日商品销售明细”表（1）选中分类汇总操作的区域鼠标指向A2单元格，按下鼠标左键拖动到F40单元格，松开鼠标左键，选中分类汇总操作的区域“A2F40”。说明分类汇总也是对整个数据清单的操作。（2）打开分类汇总对话框选择“数据”“分类汇总”菜单命令，打开“分类汇总”对话框，如图638所示。图638“分类汇总”对话框（3）设置分类汇总选项单击“分类字段”组合框的下拉按钮，在下拉选项中选择“商品名称”，将分类字段设为商品名称，方便管理人员查看。单击“汇总方式”组合框的下拉按钮，在下拉选项中选择“求和”，将汇总方式设置为“求和”。在“选定汇总项”列表中，选中“销售数量”和“金额”前的复选框；接着分别选中“替换当前分类汇总”、“汇总结果显示在数据下方”前的复选框。（4）完成分类汇总单击“分类汇总”对话框中的“确定”按钮，完成分类汇总操作。分类汇总后的“日商品销售明细”表，如图639所示。图639分类汇总效果图（5）分级查看汇总结果单击数据表左上方的“2”，查看每种商品的销售总量，如图640所示。图640每种商品的销售汇总单击数据表左上方的“1”，查看商品销售总量，如图641所示。图641商品销售总量汇总4筛选查看“日商品销售明细”表（1）设置筛选方式选择“数据”“筛选”“自动筛选”菜单命令，设置筛选方式为“自动筛选”，如图642所示。图642设置“自动筛选”（2）查看自动筛选结果单击字段名称后的下拉按钮，例如“商品名称”后的下拉按钮；在下拉选项中选择要查看的选项，例如“康师傅老坛酸菜面”；查看“康师傅老坛酸菜面”的销售情况，如图643所示。图643查看“康师傅老坛酸菜面”的销售情况（3）查看自定义筛选结果单击字段名称后的下拉按钮，例如“销售时间”的下拉按钮，打开下拉选项，如图644所示。图644打开“销售时间”下拉选项在下拉选项中，选择“自定义”选项，打开“自定义自动筛选方式”对话框，如图645所示。图645“自定义自动筛选方式”对话框在“销售时间”选项中，单击第一行左边的下拉按钮，在下拉选项中选择“大于或等于”；再单击第一行右边的组合框，输入时间，例如“930”。选中“与”单选按钮；单击第二行左边的下拉按钮，在下拉选项中选择“小于或等于”；再单击第二行右边的组合框，输入时间，例如“950”。单击“确定”按钮，查看到“930950”时间段的销售情况如图646所示。图646自定义筛选结果5制作商品销售日报图表将“日商品销售明细”表分类汇总后，就已经完成了“超市销售日报表”的制作。为了更直观地显示“日商品销售”情况，还可以利用EXCEL的图表功能制作商品销售日报图表。具体操作步骤如下。（1）打开图表向导单击“超市销售日报表”左上方的数字“2”，选中“A2F47”区域，单击菜单栏上的“插入”菜单，如图647所示。图647打开“图表”菜单命令单击“图表”菜单命令，打开“图表向导”对话框，如图248所示。图648“图表向导”对话框（2）创建图表单击“标准类型”选项卡，在“图表类型”列表中单击“柱形图”，单击“下一步”按钮，如图649所示。图649“图表向导”之二单击“系列”选项卡，如图650所示；选中“系列”列表中“销售时间”，单击“删除”按钮；再选中“单价”，单击“删除”按钮。图650“系列”选项卡单击“下一步”按钮，再单击“下一步”按钮，如图651所示。图651“图表向导”之4单击“完成”按钮，完成“商品销售日报图表”制作，如图652所示。图652商品销售日报图表623知识学习1EXCEL数据表建立准则在EXCEL中，可以把数据表（或称数据清单）作为数据库来处理，实现排序、分类汇总、筛选等数据库功能。为了使EXCEL能容易地对数据表进行处理，建立数据表时应注意以下准则。一张工作表只建立一张数据表。工作表的第一行建立各列标题，列标题使用的字体、格式应和下面的数据相区别。同一列数据的类型应一致。工作中的数据区不出现空白行和列。2数据排序对某些数据表，有时需要按字段的大小进行排序。排序的依据字段称为“关键字”，例如，对“日商品销售明细”表排序时的“商品编码”、“销售时间”等。EXCEL默认依据列字段排序，支持单个关键字排序（即简单排序），也支持多个关键字排序（即复杂排序），但最多支持三个关键字排序。排序时，英文字符按字母顺序排序，而汉字默认按拼音顺序排序。（1）简单排序实现简单排序的方法是单击排序所依据列中的任一单元格，根据需要单击“常用”工具栏中的“升序”或“降序”按钮。简单排序也可以用“排序”对话框完成。（2）复杂排序复杂排序是根据多字段的内容对数据清单进行排序，即排序所依据的第一关键字（主要关键字）内容相同时，再按第二关键字（次要关键字）进行排序，第二关键字也相同时，再按第三关键字进行排序。复杂排序要使用“排序”对话框完成。如果数据表需按行排序或汉字笔画排序，要修改EXCEL的默认设置。修改方法是单击“排序”对话框中的“选项”按钮，打开“排序选项”对话框，如图653所示；在“排序选项”对话框中，修改排序方向和方法，然后单击“确定”按钮即可。图653“排序选项”对话框2分类汇总分类汇总就是按数据清单的某列字段对记录进行分类，将列字段值相同的连续记录分为一组，并可以对各组数据进行求和、计数、求平均值、求最大值等汇总计算。（1）插入分类汇总在分类汇总前，应按分类所依据的列进行排序。这样才能确保列字段值相同的记录是连续的，汇总才能成功。插入分类汇总的方法如下。首先，单击“数据”菜单，在下拉选项中单击“分类汇总”菜单命令，打开“分类汇总”对话框。然后，在对话框中，选择“分类字段”，例如“日商品销售明细”表中的“商品名称”；选择“汇总方式”，例如“日商品销售明细”表中的“求和”；设置“选定汇总项”，例如“日商品销售明细”表中的“销售数量”、“金额”。最后，单击“确定”按钮，分类汇总的结果随之显示出来，完成分类汇总。（2）删除分类汇总插入分类汇总后，再次单击“数据”菜单中“分类汇总”菜单命令，打开“分类汇总”对话框中；在“分类汇总”对话框中单击“全部删除”命令，即可删除分类汇总，使数据清单恢复原状。3数据筛选在实际应用中，经常需要在一个数据清单中查找满足特定条件的记录，而筛选就是查找的一种快捷有效的方法。筛选可以显示满足条件的行，而不满足条件的会被暂时隐藏。EXCEL提供两类筛选数据清单的方式，分别是自动筛选和高级筛选。（1）自动筛选单击数据清单中任意单元格，选择“数据”“筛选”“自动筛选”菜单命令，进入自动筛选状态。此时，每列的标题名右端都会有“全部”、“前10个”、“自定义”和当前列中所出现的值等选项。在这些选项中，单击所需的选项，即可进行自动筛选查看。（2）高级筛选使用高级筛选，可以应用较复杂的条件来筛选数据清单。与自动筛选不同的是，使用高级筛选需要在数据清单之外另建一个条件区域。条件区域可以建在数据清单的上方、下方、左侧或右侧，但与数据清单间必须至少要保留一个空行或空列。例如，筛选英语、数学和计算机成绩都及格的学生，操作步骤如下。建立条件区域。在与数据清单空一行或一列的空白区域输入英语、数学和计算机字段名，在对应的字段名下一单元格输入条件“60”，如图654所示。图654创建条件区域单击数据清单中任意单元格，然后选择“数据”“筛选”“高级筛选”菜单命令，打开“高级筛选”对话框，如图655所示。图655“高级筛选”对话框在对话框中，根据要求单击“方式”下的单选按钮，选中所需的方式。在“条件区域”中，输入条件区域的引用；或单击右端的拾取器按钮，隐藏对话框，再在工作表中使用鼠标拖动选中条件区域。最后，单击“确定”按钮，完成高级筛选。筛选结果，如图656所示。图656高级筛选效果图说明在“列表区域”中，EXCEL会自动识别出数据区域。如果单击了“方式”下的“将筛选结果复制到其他位置”单选按钮，则“复制到”激活变黑，用“条件区域”的类似操作确定复制到的位置，然后单击“确定”按钮完成筛选。高级筛选后，选择“数据”“筛选”“全部显示”命令，可显示高级筛选隐藏的数据。4图表EXCEL中，可以用图表的方式显示工作表中数据。图表具有较好的视觉效果，可方便用户查看数据之间的差异和趋势。EXCEL为用户提供了丰富的图表类型，如柱形图、折线图、饼图等。（1）创建图表创建图表最常用的方法是使用图表向导。例如，在“学生成绩表”中创建显示“姓名”、“计算机成绩”图表的操作步骤如下。在“学生成绩表”中，拖动鼠标选中“B2B8”；按下“CTRL”键不要松开，再拖动鼠标选中“E2E8”。选中的“B2B8”和“E2E8”不连续区域作为要创建图表的数据源。单击“常用”工具栏中的“图标向导”按钮或选择“插入”“图表”菜单命令，打开图表向导对话框，如图657所示。图657“图表向导”对话框在“标准类型”选项卡中，选择图表的类型，例如“柱形图”“簇状柱形图”；然后，单击“下一步”按钮，打开“图表向导”步骤2对话框，如图658所示。图658“图表向导”步骤2对话框在“标准类型”选项卡中，有多种图表类型可供选择，如“柱形图”、“饼图”等。每一种图表类型还细分出若干子类型，可根据需要进行选择它们。如果对提供的图表类型不满意，还可以在“自定义类型”选项卡中自行选择所需的图表类型。在图表向导第2步中，定义图表的数据源；在“系列”选项卡中，设置图表中显示的分类轴标志；设置完成后，单击“下一步”按钮，打开“图表向导”步骤3对话框，如图659所示。图659“图表向导”步骤3对话框一般情况下，EXCEL会自动识别出图表的数据源。如果有误，可以单击“数据区域”框右端的拾取器按钮，隐藏对话框；然后，在工作表中选择数据源区域，并拖到隐藏的对话框。在图表向导第3步中，设置图表的“标题”、“数值轴”、“网格线”、“图例”、“数据表”等选项，例如在“标题”选项卡的“图表标题”中输入“学生计算机成绩图表”、“数值轴”中输入“成绩”，在“图例”选项卡中取消“显示图例”的复选；设置完成后，单击“下一步”按钮，打开“图表向导”步骤4对话框，如图660所示。图660“图表向导”步骤4对话框在图表向导第4步中，选择图表插入位置，例如选中“作为其中的对象插入”；然后，单击“完成”按钮，完成图表创建，如图661所示。图661图表效果图（2）更改图表设置图表创建后，还可以根据需要更改图表的类型、数据源、图表选项和位置设置。更改的方法是在图表上单击鼠标右键，在快捷菜单中选择“图表类型”、“图表选项”等选项进行更改；或者单击图表选中后，选择“插入”“图表”菜单命令，在打开的对话框中进行更改。例如，将“学生成绩表”图表类型更改为“折线图“、图例靠右显示的操作步骤如下。在如图661所示的图表上单击鼠标右键，打开快捷菜单，如图662所示；单击“图表类型”，在打开的对话框中选择“折线图”。图662图表向导步骤4对话框再在图表上单击鼠标右键，单击“图表选项”，在打开的对话框中选择“图例”选项卡，选中“显示图例”，并选中“靠右”；单击“完成”按钮，完成图表更改。（3）格式化图表EXCEL图表是由图表区、图表标题、图例、绘图区、系列和数据标志等对象组成。可以通过鼠标右击图表区，在打开的快捷菜单中单击“图表区格式”菜单命令，打开“图表区格式”对话框，如图663所示；在对话框中，设置字体、边框、填充色等格式化图表。图663“图表区格式”对话框63制作超市月销售额统计表631任务分析超市以多分店方便顾客赢得了市场。为及时核算成本，高效地调度资金，需要对各个分店的月销售额进行统计、分析。统计时，通常每个分店制作一张统计表，然后将各分店的销售数据合并，完成整个超市系统当月商品销售情况的统计、分析，并根据各分店的销售业绩核算出奖金额。为避免月底集中统计造成工作量的不平衡，或每天统计造成工作效率低下，可分上、中、下三旬对每个月各分店的销售情况进行统计。制作思路首先，创建各类工作表，如“一分店”月销售额统计表、“合并计算表”、“各店销售业绩统计表”等；然后，对各个分店的月销售额进行统计、分析，制成图表；最后，将各分店的月销售情况进行合并计算，并核算出各分店的奖金额。学习的知识点格式排版合并计算跨表引用632制作步骤1创建各类工作表（1）创建各分店月销售额统计表新建工作簿，命名为“职院超市月销售额统计表”；分别将SHEET1、SHEET2、SHEET3重命名为“一分店”、“二分店”、“三分店”。单击“一分店”，参照图664所示，完成一分店月销售额统计表的创建。图664一分店月销售额统计表选中“A1L16”区域，将“职院一分店月销售额统计表”的内容复制到“二分店”、“三分店”，并分别将标题中的“一分店”修改为“二分店”、“三分店”。单击“保存”按钮，完成各分店月销售额统计表的创建。（2）创建合并计算表鼠标右键单击“一分店”工作表，在快捷菜单中单击“移到或复制工作表”，打开“移到或复制工作表”对话框，如图665所示。图665“移到或复制工作表”对话框单击“（移至最后）”；选中“建立副本”复选框，再单击“确定”按钮。重命名“一分店2”为“合并计算表”，将标题修改为“职院超市各分店月销售额统计总表”，如图666所示。图666合并计算表单击“保存”按钮，完成合并计算表的创建。（3）创建各店销售业绩统计表单击“合并计算表”，选择“插入”“工作表”菜单命令，在“合并计算表”插入一张新工作表；将新工作表重命名为“各店销售业绩统计表”，鼠标左键按住“各店销售业绩统计表”拖动到最后，松开鼠标左键。参照图667所示，完成“各店销售业绩统计表”创建后，单击“保存”按钮，保存创建的工作表。图667各分店销售业绩统计表2统计各分店月销售额（1）统计一分店月销售额根据一分店提交的商品销售日报表，分上、中、下三旬完成当月各商品销售数量的录入，如图668所示。图668录入好销售数量的工作表利用EXCEL中的公式、函数，计算“娃哈哈矿泉水”上旬销售金额，如图669所示；利用填充柄复制公式到其他单元格，计算出上旬所有商品的销售金额。中、下旬销售金额用同样的方法计算出。图669计算“娃哈哈矿泉水”上旬销售金额计算“娃哈哈矿泉水”的月销售总数，如图670所示；利用填充柄复制公式到其他单元格，计算出所有商品的月销售总数。图670计算“娃哈哈矿泉水”月销售总数计算“娃哈哈矿泉水”的月销售总额，如图671所示；利用填充柄复制公式到其他单元格，计算出所有商品的月销售总额。图671计算“娃哈哈矿泉水”月销售总额计算上旬销售金额合计值，如图672所示；用同样的方法，计算出中、下旬销售金额合计值及销售总额合计值，如图673所示。图672计算上旬销售金额合计值图673计算销售总额合计值选中“商品名称”、“销售总数”两列，打开“图表向导”对话框，如图674所示；在向导指引下，制作“商品月销售总数”图表，如图675所示。图674创建销售总数图表图675一分店商品月销售总数图表选中“商品名称”、“销售总额”两列，打开“图表向导”对话框，如图676所示；在向导指引下，制作“商品月销售总额”图表，如图677所示。图676创建销售总额图表图677一分店商品月销售总额图表（2）统计二、三分店月销售额按照一分店月销售额统计及分析的方法，完成二、三分店月销售额的统计及分析。3统计超市月销售额（1）合并计算单击“合并计算表”，选中“E4L16”区域，如图678所示。图678打开“合并计算表”选择“数据”“合并计算”菜单命令，打开“合并计算”对话框，如图679所示。图679“合并计算”对话框单击“一分店”工作表，选中一分店月销售额统计表中的“E4L16”区域，如图680所示。图680选择“引用位置”单击“合并计算”对话框中的“添加”按钮，将一分店的“E4L16”区域添加到引用位置；用同样的方法，添加二、三分店月销售额统计表中的“E4L16”区域；选中“标签位置”的“创建连至源数据的链接”复选框，如图681所示。图681添加所有引用位置单击“确定”按钮，完成三个分店月销售的合并计算，如图682所示。图682合并计算后结果单击左侧的“”号，查看每个分店的销售数据，如图683所示。图683查看各分店数据（2）创建超市月销售图表在合并计算表中，选中“商品名称”、“销售总数”两列，打开“图表向导”对话框，在向导指引下，制作“超市商品月销售总数”图表，如图684所示。选中“商品名称”、“销售总额”两列，打开“图表向导”对话框，在向导指引下，制作“超市商品月销售总额”图表，如图684所示。图684超市系统月销售图表4核算奖金基数（1）引用月销售额单击“各店销售业绩统计表”，选中D3单元格，在D3单元格内输入“一分店L16”，如图685（A）所示；按下“ENTER”键，将“一分店”表中L16单元格的月销售总额引用到D3单元格，如图685（B）所示。（A）数据跨表引用（B）引用结果图685引用一分店月销售额同理，选中D4单元格，在D4单元格内输入“二分店L16”，将“二分店”表中L16单元格的月销售总额引用到D4单元格；选中D5单元格，在D5单元格内输入“三分店L16”，将“三分店”表中L16单元格的月销售总额引用到D5单元格。完成引用后如图686所示。图686引用各分店月销售额（2）计算奖金基数选中E3单元格，在E3单元格内输入“D32”，按下“ENTER”键，计算出一分店奖金基数。利用填充柄复制公式到E4、E5单元格，计算出二、三分店奖金基数，如图687所示。图687奖金核算结果633知识学习1合并计算一个公司内可能有很多的销售地区或者分公司，各个分公司具有各自的销售报表和会计报表，为了对整个公司的所有情况进行全面的了解，就要将这些分散的数据进行合并，从而得到一份完整的销售统计报表或者会计报表。利用EXCEL提供的合并计算功能，可快捷地完成这些数据汇总工作。（1）基本概念合并计算是指，将多个源区中的数据汇总为一个数据的计算方法。EXCEL提供了两种合并计算的方法。一是通过位置合并计算，适用于源区中的数据源具有相同的对应位置数据的汇总。二是通过分类合并计算，适用于源区中的数据没有相同的布局时数据的汇总。要想合并计算，必须先为汇总信息定义一个目的区，用来显示摘录的信息。此目标区域可位于与源相同的工作表上，也可以在另一个工作表上或工作簿内。然后，选择要合并计算的源。此源可以来自单个工作表、多个工作表或多重工作簿中。在EXCEL中，可以最多指定255个源区域来进行合并计算。在合并计算时，不需要打开包含源区域的工作簿。（2）通过位置合并计算通过位置来合并计算非常适用于处理相同表格的数据汇总。例如，将超市的多家分店数据合并形成一个完整的超市报表，将不同地区的税务报表合并形成一个市的总税务报表等。用这种方式合并计算时，要求同一类数据在所有源区域的数据表中处于相同的位置。（3）通过分类合并计算设分类合并计算前的原始数据，位于SHEET2工作表中，如图688所示。汇总各个产品总销售量的操作如下。图688某公司销售表在SHEET2工作表中，单击“A16”单元格，将光标定位于A16，存放汇总的第一个数据；然后，选择“数据”“合并计算”菜单命令，打开“合并计算”对话框。单击“引用位置”，选中原始数据表中的“B1C13”，再单击“合并计算”对话框中的“添加”按钮；选中“标签位置”选项组中的“首行”、“最左列”，如图689所示。图689设置引用、标签位置单击“确定”按钮，完成各个产品总销售量汇总，如图690所示。图690合并计算效果2跨表引用跨表引用是指将其他工作表中数据引用到本工作表中。跨表引用的标识符为“”，引用规则是工作表名称单元格”表达式。例如有名称为SHEET1、SHEET2和SHEET3的3张工作表，现要用SHEET1的D8单元格的内容乘以40，再加上SHEET2的B8单元格内容乘以60，作为SHEET3的A8单元格的内容。则在SHEET3的A8单元格内输入表达式“SHEET1D840SHEET2B860”，即可将SHEET1、SHEET2的数据，跨表引用到SHEET3。64制作超市员工工资表641任务分析超市员工的工资一般由基本工资、业绩奖金、考勤奖等组成。其中基本工资的多少由岗位决定，每个月基本上是固定的；业绩奖金则与销售额挂钩，考勤奖与出勤天数挂钩，每个月都会有浮动。工资表中除了包含工资收入信息外，还要包含各种保险费、个人所得税等支出信息。因此，制作超市员工工资表时，不仅要对收入和支出进行核算，计算出应发工资、实发工资，而且还要熟悉业绩奖金、考勤奖的核算方法、各种保险费及个人所得税扣缴的规则。掌握了这些知识和技能，才能最终完成超市员工工资表的制作任务。制作思路首先，新建一个EXCEL工作簿，根据工资构成制作一张工资表的空表；然后，在空表中录入员工编号、员工姓名、岗位、基本工资等基本信息，根据各分店月销售额统计表核算业绩奖金，根据出勤天数核算考勤奖；最后，根据各种保险费及个人所得税扣缴比例计算扣缴金额，核算出实发工资，完成工资表的制作。学习的知识点公式函数绝对引用各种保险费及个人所得税扣缴比例642制作步骤1制作工资表的空表新建工作簿，命名为“职院超市员工工资表”。参照图691所示，制作工资表的空表。图691工资表空表2录入员工基本信息参照图692所示，录入员工的编号、姓名、岗位信息和基本工资。图692录入员工基本信息的工资表3核算业绩奖金业绩奖金的核算方法为分店店长的业绩奖金为各分店的奖金基数，各分店工作人员的业绩奖金为店长业绩奖金的80，总经理的业绩奖金为三位店长业绩奖金的平均值，会计业绩奖金为总经理业绩奖金的80。（1）核算分店店长的业绩奖金核算一分店店长的业绩奖金。选中工资表中G6单元格，在单元格内输入“各店销售业绩统计表E3”，计算出分店店长1的业绩奖金。核算二分店店长的业绩奖金。选中工资表中G10单元格，在单元格内输入“各店销售业绩统计表E4”，计算出分店店长2的业绩奖金。核算三分店店长的业绩奖金。选中工资表中G14单元格，在单元格内输入“各店销售业绩统计表E5”，计算出分店店长3的业绩奖金。各分店店长的业绩奖金核算结果，如图693所示。图693核算分店店长业绩奖金（2）核算各分店工作人员的业绩奖金核算一分店员工的业绩奖金。选中工资表中G7单元格，在单元格内输入“G680”，计算出管理员1的业绩奖金；然后，利用填充柄复制公式到G8、G9单元格，计算出收银员1、供货员1的业绩奖金。核算二分店员工的业绩奖金。选中工资表中G11单元格，在单元格内输入“G1080”，计算出管理员2的业绩奖金；然后，利用填充柄复制公式到G12、G13单元格，计算出收银员2、供货员2的业绩奖金核算三分店员工的业绩奖金。选中工资表中G15单元格，在单元格内输入“G1480”，计算出管理员3的业绩奖金；然后，利用填充柄复制公式到G16、G17单元格，计算出收银员3、供货员3的业绩奖金。各分店员工业绩奖金核算结果，如图694所示。图694核算分店员工业绩奖金（3）核算总经理、会计的业绩奖金选中G4单元格，在单元格内输入“AVERAGEG6,G10,G14”，核算总经理的业绩奖金，如图695所示。选中G5单元格后，在单元格内输入“G480”，核算会计的业绩奖金,如图695所示。图695核算总经理、会计业绩奖金4核算考勤奖考勤奖的核算方法是出勤天数达到22天，发考勤奖基数100元，多加一天班，加班费为50元每天；出勤天数未到22天，无考勤奖基数100元，缺勤一天，扣20元。考勤奖核算步骤如下。录入