信息技术基础 课件 项目3 Excel 2016 电子表格软件应用

信息技术基础项目3Excel2016电子表格软件应用目录CONTENTSExcel2016工作界面及基础知识制作员工工资表制作销售趋势分析图分析库存商品明细表01.02.03.04.Excel2016工作界面及基础知识3.1工作完成情况1.Excel2016的工作界面快速访问工具栏编辑栏列标窗口控制按钮组工作表区域垂直滚动条视图按钮水平滚动条活动工作表标签行号控制句柄活动单元格“全部选定”按钮名称框功能区工作完成情况工作完成情况（1）新建和保存工作簿（2）打开与关闭工作簿（3）插入工作表（4）删除工作表（5）重命名工作表（6）移动或复制工作表（7）隐藏或显示工作表（8）冻结工作表

选择“冻结拆分窗格”选项选定工作表区域的任意单元格，然后选择“视图”选项卡,在“窗口”选项组中单击“冻结窗格”按钮，从弹出的下拉列表中选择“冻结拆分窗格”选项，可以冻结此单元格上边和左边的区域，以使其位置固定不变，从而方便数据的浏览。如果要取消冻结，则可以选择“视图”选项卡，在“窗口”选项组中单击“冻结窗格”按钮，从弹出的下拉列表中选择“取消冻结窗格”选项。2.工作表和工作簿的常见操作工作完成情况工作完成情况（1）选定单元格及单元格区域（2）输入数据（3）快速输入工作表数据（4）查找和替换3.在工作表中输入数据4.单元格、行和列的相关操作（1）插入与删除单元格（2）合并与拆分单元格（3）插入与删除行和列（4）隐藏与显示行和列（5）改变行高和列宽（1）修改与删除单元格内容（2）移动和复制表格数据（3）设置表格边框和填充效果①设置表格边框。选定要设置边框的单元格区域,选择“开始”选项卡,在“字体”选项组中单击“边框”的下三角按钮,然后从弹出的下拉列表中选择适当的边框样式。②设置表格填充效果。选定要设置的单元格区域,在“设置单元格格式”对话框的“填充”选项卡中设置单元格区域的背景色、填充效果、图案颜色、图案样式等。（4）套用表格格式（5）设置条件格式（6）格式的复制与清除（7）保护工作表用鼠标右键单击工作表标签,从弹出的快捷菜单中选择“保护工作表”选项,打开“保护工作表”对话框,勾选“保护工作表及锁定的单元格内容”复选框。5.编辑与设置表格数据制作员工工资表3.2工作完成情况工作完成情况（1）新建表格（2）输入数据1.编辑表格内容2.计算加班费（1）计算加班时长选定工作表“加班统计表”的C3:C23单元格区域,选择“数据”选项卡,在“数据工具”选项组中单击“合并计算”按钮,打开“合并计算”对话框。在“函数”下拉列表中选择“求和”选项,然后将光标移至“引用位置”文本框,单击“第1周”工作表,选定该工作表的C3:C23单元格区域,单击“添加”按钮,将“第1周!$C$3:$C$23”添加到“所有引用位置”列表框中。将其他的加班时长按照上述方法也添加到“所有引用位置”列表框中,如图所示,单击“确定”按钮完成数据合并。工作完成情况工作完成情况2.计算加班费（2）计算加班费①插入函数。选定要插入函数的D3单元格,单击编辑栏中的“插入函数”按钮,打开“插入函数”对话框,在“或选择类别”下拉列表中选择“常用函数”选项,从“选择函数”列表框中选择“IF”选项,单击“确定”按钮。②在打开的“函数参数”对话框中设置函数参数。③选定D3单元格,按住鼠标左键向下拖动控制句柄至D23单元格处。选定D2单元格,单击“审阅”选项卡中“批注”选项组的“新建批注”按钮,如图3-42所示,在其中添加加班费的计算方法。工作完成情况工作完成情况3.填充并计算工资（1）引用单元格①单元格地址的引用格式。单元格地址的引用格式是[工作簿名]工作表名!单元格地址。当引用当前工作簿或工作表中的数据时，相应项可省略。②引用的类型。Excel2016提供了三种不同类型的引用：相对引用、绝对引用和混合引用（可以使用F4键切换三种类型）。在实际应用中，要根据数据的关系决定采用哪种类型的引用。（2）计算应发工资金额计算应发工资总额时可以在H4单元格中输入计算公式“=E4+F4+G4”后按Enter键,如图所示；也可以选定H4单元格,然后单击编辑栏中的“插入函数”按钮,在打开的“插入函数”对话框的“或选择类别”下拉列表中选择“常用函数”选项,从“选择函数”列表框中选择“SUM”选项,单击“确定”按钮,接着在打开的“函数参数”对话框中输入单元格的位置,最后单击“确定”按钮。向下填充计算公式或函数即可计算每个人的应发工资总额。工作完成情况工作完成情况3.填充并计算工资（3）计算个人所得税计算个人所得税需要使用函数IF中嵌套函数

IF

来实现多个条件的设定。在I4单元格中应输入“=IF(H4<=5000,0,IF(H4<=8000,(H4-5000)∗0.03,IF(H4<=17000,(H4-5000)∗0.1-210,(H4-5000)∗0.2-1410)))”，然后按Enter键插入函数，最后向下填充公式即可。（4）扣款总额和实发工资。扣款总额的计算公式为：扣款总额=个人所得税+其他；实发工资的计算公式为：实发工资=应发工资总额-扣款总额，这两项的计算方法与计算应发工资总额的计算方法一致，此处不再重复。4.美化表格（1）设置格式（2）设置表格边框（3）冻结窗格5.Excel2016的函数Excel2016中所提到的函数其实是一些预定义的公式,它们使用一些被称为参数的特定数值按特定的顺序或结构进行计算。Excel2016的函数一共有12类,在“公式”选项卡的“函数库”选项组中可以找到,单击“函数库”选项组的“其他函数”的下三角按钮,在弹出的下拉列表中显示的是其他类型的函数。制作销售趋势分析图3.3工作完成情况工作完成情况1.获取数据（1）创建一个新的工作簿,在Sheet1表格中单击“数据”选项卡中“获取外部数据”选项组的“自文本”按钮,打开“导入文本文件”对话框,如图所示。选择要导入的文本文件,然后单击“导入”按钮。（2）打开“文本导入向导-第1步,共3步”对话框,如图所示。在“原始数据类型”选区中,选中“分隔符号”单选按钮,然后单击“下一步”按钮。（3）打开“文中导入向导-第2步,共3步”对话框,如图所示。在“分隔符号”选区中勾选“Tab键”复选框,表示此处分隔符号为制表符Tab键,在“数据预览”选区中可以看到导入数据的效果,然后单击“下一步”按钮。（4）打开“文本导入向导-第3步,共3步”对话框,如图所示。观察“数据预览”选区,发现不需要更改数据格式,单击“完成”按钮。2.创建图表“图表”选项组工作完成情况工作完成情况3.设置图表（1）调整图表的大小和位置（2）更改图表数据源图表创建完成后,可以在后续操作中根据需要向其中添加新数据,或者删除已有的数据。（3）交换图表的行与列“选择数据源”对话框工作完成情况工作完成情况4.格式化图表（1）设置图表标题（2）设置图表坐标轴（3）添加图例和数据标签（4）更改图表类型（5）设置图标样式

（6）添加趋势线“设置图表标题格式”任务窗格“设置坐标轴格式”任务窗格工作完成情况工作完成情况5.打印活动工作表（1）页面设置①设置纸张大小。选择“页面布局”选项卡,在“页面设置”选项组中单击“纸张大小”按钮,从弹出的下拉列表中选择所需的纸张。②设置纸张方向。选择“页面布局”选项卡,然后在“页面设置”选项组中单击“纸张方向”按钮,从弹出的下拉列表中选择一种纸张方向。③设置页边距。选择“页面布局”选项卡,在“页面设置”选项组中单击“页边距”按钮,从弹出的下拉列表中选择一种页边距方案。④设置打印区域。⑤设置打印标题。⑥设置页眉和页脚。（2）打印工作表①打印预览选择“文件”→“打印”选项,可以在右侧预览打印效果。如果看不清楚,那么可以单击预览页面下方的“缩放到页面”按钮。②打印工作表选择“文件”→“打印”选项,显示“打印”选项面板。在“份数”微调框中输入要打印的份数。分析库存商品明细表3.4工作完成情况工作完成情况1.整理原始数据打开工作簿“库存商品明细表”,然后选定待分析的A1:F41单元格区域,选择“数据”选项卡,单击“数据工具”选项组中的“删除重复项”按钮,打开“删除重复项”对话框,如图所示。勾选“数据包含标题”复选框,并勾选列表框中的所有复选框,表示只有这些列都相同时才认为是重复项。单击“确定”按钮,Excel2016将搜索并删除重复值,删除完成后,打开显示处理结果的对话框,单击“确定”按钮,完成操作。“删除重复项”对话框工作完成情况工作完成情况2.数据的排序（1）简单排序选定包含数据的任意单元格,然后选择“数据”选项卡,在“排序和筛选”选项组中单击“升序”或“降序”按钮,默认按列排序。（2）复杂排序复杂排序是指对选定的数据区域,按照两个以上的排序关键字按行或按列进行排序的方法。以下是先按照工作簿“库存商品明细表”的“在库数量”降序排列,当库存数量相同时,再按照“单价”降序排列的操作方法。（3）自定义排序自定义排序是指对选定的数据区域按照用户定义的顺序进行排序。选定数据区域的任意单元格,选择“数据”选项卡,在“排序和筛选”选项组中单击“排序”按钮,打开“排序”对话框。在“主要关键字”下拉列表中选择“名称”选项,在“次序”下拉列表中选择“自定义序列”选项,打开“自定义序列”对话框。在“自定义序列”选项卡的“输入序列”列表框中依次输入指定的序列,每输入一项,按一次Enter键,输入完成后,单击“添加”按钮,序列就被添加到“自定义序列”列表框中。单击“确定”按钮,返回“排序”对话框,然后单击“确定”按钮,数据区域将按照上述指定的序列排序。工作完成情况工作完成情况3.数据的筛选（1）自动筛选自动筛选是指按照单一条件进行数据筛选。选定数据区域的任意单元格,选择“数据”选项卡,在“排序和筛选”选项组中单击“筛选”按钮,表格中的每个表头右侧将显示自动筛选下三角按钮。（2）自定义筛选单击“单价”列的自动筛选下三角按钮,从弹出的下拉列表中选择“数字筛选”→“介于”选项,打开“自定义自动筛选方式”对话框。在“大于或等于”右侧下拉列表框中输入“20”,并选中“与”单选按钮,然后在“小于或等于”右侧的下拉列表框中输入“70”。单击“确定”按钮,即可显示符合条件的记录。（3）高级筛选单击数据区域中的任意单元格,然后选择“数据”选项卡,在“排序和筛选”选项组中单击“高级”按钮,打开“高级筛选”对话框。在“方式”选区中选中“将筛选结果复制到其他位置”单选按钮(若选中“在原有区域显示筛选结果”单选按钮,则不用指定“复制到”区域)。“列表区域”文本框将被自动设定为数据区域$A$1:$F$37。将光标移至“条件区域”文本框中,然后选择包括表头在内的条件区域$I$1:$N$2。工作完成情况工作完成情况4.数据的分类汇总（1）创建分类汇总单击数据区域中“名称”列的任意单元格,选择“数据”选项卡,在“排序和筛选”选项组中单击“升序”按钮,对该字段进行排序。在“分级显示”选项组中单击“分类汇总”按钮,打开“分类汇总”对话框。在“分类字段”下拉列表中选择“名称”选项,在“汇总方式”下拉列表中选择“求和”选项,在“选定汇总项”列表框中勾选“在库数量”复选框。单击“确定”按钮。（2）嵌套分类汇总首先,对数据区域中要实施分类汇总的多个字段进行排序。然后,将光标移至数据区域中,使用上面介绍的方法,按第一关键字对数据区域进行分类汇总。最后,再次打开“分类汇总”对话框,在“分类字段”下拉列表中选择次要关键字,将“汇总方式”下拉列表框和“选定汇总项”列表框保持与第一关键字相同的设置,并取消勾选“替换当前分类汇总”复选框。单击“确定”按钮,操作完成。（3）复制分类汇总的结果通过分级显示按钮仅显示需要复制的结果,按Alt+;组合键选定当前显示的内容,然后按Ctrl+C组合键将其复制到剪贴板中。在目标单元格区域中按Ctrl+V组合键完成粘贴操作。如有需要,可以使用“分类汇总”对话框将目标位置的分类汇总全部删除。工作完成情况工作完成情况5.建立数据透视表（1）创建数据透视表（2）添加、删除、更