案例背景
每学期开学前学校负责管理办公用品的总务部门,要清楚办公用品的库存情况,就要有一个“办公用品库存统计表”,以统计办公用品的出库、入库和库存情况。而办公用品种类繁多,各部门领取办公用品也并非开学时一次性领取,而是根据实际需要不断领取,造成原始数据表单的数据记录(行数)较多,不便于统计库存量。所以需要建立数据透视表,使管理人员一目了然的对办公用品的库存情况进行动态地统计,以便及时补充库存,避免影响工作。
通过本节的学习,学员应主要掌握创建数据透视表的方法。
关键技术点
要实现本案例中的功能,学员应该掌握以下EXCEL技术点。
●基础知识:数据透视表
最终效果展示
Step 1创建工作簿,重命名工作表
新建一个Excel工作簿,保存为文件名“办公用品统计表”,然后将工作表重名为“原始数据”,删除多余的工作表。
Step 2输入表格各字段标题
在A1:G1单元格区域分别输入各个字段的标题名称,
Step 3输入原始数据
在A2:G43单元格区域输入表格原始数据。
Step 4填充背景色
选中A1:G1单元格区域,并填充浅绿色背景色。
Step5 设置文本居中显示
选中A1:G43单元格区域,设置文本居中显示。
Step6设置表格边框
选中A1:G43单元格区域,为表格添加边框。
已经建立了原始数据表单,下面要在此基础上创建数据透视表。数据透视表是是从数据库中产生的一个动态汇总表格,它的透视和筛选能力使其具有极强的数据分析能力,通过转换行或列可以查看源数据不同的汇总结果,并且可以显示不同的页面来筛选数据,还可以根据需要显示区域中的明细数据。
Step1选择数据类型和报表类型
①在“原始数据”中选择任意一个非空单元格如A1 单元格,然后单击菜单“数据”→“数据透视表和数据透视图”,打开“数据透视表和数据透视图的向导—3步骤之
②在“请指定待分析的数据源类型”组合框中选择默认的的“Microsoft Office Excel数据列表或数据库”、在“所需创建的报表类型”中选择默认的“数据透视表”。
③单击“下一步”按钮打开“数据透视表和数据透视图的向导—3步骤之
Step2选择数据源区域
①在该对话框中,“选择区域”文本框中默认的工作表的数据区域为$A$1:$G$43。
②单击“下一步”按钮打开“数据透视表和数据透视图的向导—3步骤之
Step3选择数据透视表显示位置
①选中“新建工作表”单选按钮。 ②单击完成按钮即可创建Excel默认的数据透视表版式图,同时会打开“数据透视表“工具栏和”数据透视表字段列表“任务窗格。
③将该数据透视表自动创建的工作表Sheet4重命名为“统计表”。Step4设置行、列字段布局
①将”数据透视表字段列表“任务窗格中的列字段“事由”拖曳至工作表B3:G3单元格区域的“将列字段拖至此处”。
②将“用品名称”拖至A4单元格的“将行字段拖至此处”,再将“部门”拖至B4单元格。
Step5设置“数据项”字段布局
在”数据透视表字段列表“任务窗格中单击”数量“,再单击”添加到“右侧的下箭头按钮,在下拉列表中选中”数据区域“,最后单击”添加到“按钮即可将”数量“添加到C15:E49单元格区域的”请将数据项拖至此处“。
Step6隐藏“总计“
①单击”数据透视表“工具栏中”数据透视表“右侧的下箭头按钮,在弹出的下拉菜单中选择“表选项”,弹出“数据透视表选项 “对话框。
②在“格式选项”组合框中取消勾选“列总计”和“行总计”,而勾选“合并标志”,然后单击确定按钮。
此时数据透视表中就隐藏了列“总计”和行“总计”。
Step7调整数据项的顺序
选中C4单元格,右键单击在弹出的下拉菜单中选择“顺序”→“向右移”。
此时原来的C4:C49 单元格区域内容“出库”及数值就会向右移动,效果如图所示。
Step8计算字段
①单击数据透视表“工具栏中”数据透视表“右侧的下箭头按钮,在弹出的下拉菜单中选择“公式”→“计算项”,弹出“在‘事由‘中插入计算字段“对话框。
②在名称文本框中输入“库存”,在“项”列表框中选中“入库”,再拖动鼠标选中“公式”文本框中的“
③此时“公式”文本框中的内容为“=入库”,在此后面输入减号“-”,并在“项”列表框中选中“出库”,然后单击“插入项”按钮。
④此时“公式”文本框中的内容为“=入库-出库”,然后单击“确定”按钮。
至此计算字段“库存”已经添加成功了。
Step9输入表格标题
选中A1:F1单元格区域,设置格式为“合并及居中”,输入标题“办公用品库存统计表”,然后设置字形为“加粗”,字号为“
Step10删除行
右键单击第2行的行标,在弹出的快捷菜单中选择“删除”,此时第2行即被删除,下一行的单元格会自动上移。
Step11隐藏行
右键单击新的第2行的行标,在弹出的快捷菜单中选择“隐藏”,此时第2行即被隐藏起来。
Step12为表格设置边框
选中A2:E38单元格区域,单击常用工具栏“边框”右侧的下拉箭头,从弹出的下拉列表框中选择“所有边框”。
至此至此数据透视表创建完成。
关键知识点讲解
设置页面字段
在Step3至Step5中分别介绍了设置行列字段布局和数据项字段布局的方法,其实按照下面介绍的方法进行操作更加直观便捷。
①在Step3中单击左下角的“布局”按钮弹出“数据透视表和数据透视图的向导——布局”对话框。
②在该对话框中分别将右边的“用品名称”字段和“部门”字段按钮拖到左边的图中的“行”框中,将“是由”字段按钮拖到“列”框中,将“数量”拖到“数据”框中。
③单击“确定”按钮返回“数据透视表和数据透视图的向导—3步骤之
单元2操作练习
1.安下图样式设计“期末考试成绩统计表”,将你任教的一个班上学期的期末考试成绩输入并用SUM函数计算总分,用AVERAGE、MAX和MIN函数分别求出平均分、最高分和最低分,平均分要求保留一位小数。
2根据您的实际收入,参照下图,按照2.2节案例“个人所得税报告书”的计算公式核算2009年1—8月你的个税缴纳情况。
3.按照下表样式输入5—10个本年级教师个人信息。要求:
⑴假定个人代码为4位数,其中前两位数字是“01“,请将A列的数字格式设成自定义格式⑵输入”性别“时使用”数据有效性“功能,设置成下拉箭头进行选择。
⑶输入“身份证号码“时参照2.3节案例的方法进行具体操作。
4.参照2.4节案例使用“数据“的“分类汇总“功能,按照下图样式设计成绩汇总表,首先以您任教班级(或组别)为”分类字段“,汇总方式先后为”平均值“、”最大值“和”标准偏差“,”汇总项“为全部5科成绩及总分,然后再以”性别“为”分类字段“在此进行分类汇总。
5.在练习4的“成绩汇总表”基础上,使用RANK函数进行班级和年级排名
6.按下表样式静本年级教师的个人信息输入,使用“数据透视表“功能,按以下要求对数据表进行统计分析。
⑴按学科——职称进行透视汇总
⑵按学科、性别——职称进行透视汇总⑶
⑶按学科——文化程度进行透视汇总
联系客服