Office办公软件应用高级教程--Excel课件全套ppt课件最全电子教案完整版教学教程整套.pptx

《Office办公软件应用高级教程--Excel课件全套ppt课件最全电子教案完整版教学教程整套.pptx》由会员分享，可在线阅读，更多相关《Office办公软件应用高级教程--Excel课件全套ppt课件最全电子教案完整版教学教程整套.pptx（161页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、Word 2016Office 办公软件应用高级教程表格基本编辑表格基本编辑 Excel 高手速成Excel 电子表格Office 办公软件应用高级教程Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程学习目标由制作培训登记表案例入手，学习EXCEL工作表及单元格的编辑方法，掌握EXCEL编制基本表格技能。Word 2016Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手抢鲜看高手加油站高手大闯关高手勇拓展Word 2016Office 办公软件应用高级教程Of

2、fice 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程初入职场，设计一份员工培训计划表是办公人员所必须具备的基本功，下面小编就与大家一起来完成员工培训表的制作。图2-1-1学员培训登记表制作效果图高手抢鲜看Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程 进入到办公高手实习段位的各位大侠们一定已经掌握了基本的Excel电子表格操作技能（如需要学习基础技能可以查看与本教材配套的Office 办公软件案例教程一书），本章将就一些Excel电子表格的操作技巧进行重点介绍。一.单元

3、格内文字换行在Excel电子表格中，有时需要对单元格内文字进行换行，换行前后的文字效果如图2-1-2所示，以下三种方法可以轻松实现。图 2-1-2换行前后的单元格文字效果高手加油站（1）功能选项卡换行法 在Excel单元格中输入文字后，单击【开始|自动换行】功能选项卡，如图2-1-3所示，单元格内的文字即可实现自动换行。图 2-1-3使用“自动换行”功能选项卡实现文字换行Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站（2）右键菜单换行法选中要自动换行的单元格，右击选择【设置单元格格式】，在【

4、设置单元格格式】对话框中选择【对齐】选项卡，在【文本控制】组中选中【自动换行】选项，如图2-1-4所示，所选中的全部单元格中的文字即可实现自动换行。图 2-1-4使用右键菜单实现文字换行（3）快捷键强制换行法有时Excel单元格内的文字不需要自动换行，而需要在特定的文字位置处换行，这时可以使用快捷键的方法进行强制换行，具体方法为将光标定位在需要换行的文字位置，按【Alt+Enter】组合键即可以实现在任意文字位置上的换行。换行效果如图2-1-5所示。图 2-1-5使用快捷键实现文字强制换行效果Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子

5、表格Office 办公软件应用高级教程高手加油站2.合并单元格合并单元格在Excel电子表格中，选中需要合并的单元格，单击【开始|合并后居中】功能选项卡，可以将选中的单元格合并，此按钮为开关键，对合并后的单元格再次单击该按钮，也可以单击【合并后并居中】右侧下三角按钮，在展开的下拉列表中单击【取消单元格合并】选项（如图2-1-6所示），单元格将拆分成合并前默认的单元格。图 2-1-6合并后居中选项卡实现单元格合并Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程图1-6-8 公文主体设置高手加油站3.调整行

6、高和列宽调整行高和列宽当单元格宽度不够时，所录入的数字等内容经常出现“#”显示效果，此时可以采用拖动、双击和精确设置三种方法来调整单元格的行高与列宽。（1）拖动：将光标置于要设置行高或列宽的单元格内，按住鼠标左键拖动行边线或列边线，可以增加或减少行高或列宽。（2）双击法：直接双击列边线或行边线，可以设置该列或该行最适合的列宽。（3）精确设置法：选中行标签或列标签右击，在图2-1-7中选择【行高】或在图2-1-8中选择【列宽】，即可精确设置行高或列宽。图2-1-7 设置行高图2-1-8 设置列宽Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子

7、表格Office 办公软件应用高级教程高手大闯关Step 1:设置页眉（1）运行“Excel 2016”程序，单击【文件|新建】，出现如图2-1-9所示的界面，选择新建【空白工作薄】，建立一个新的Excel文档。图 2-1-9 新建Excel文档（2）双击文档左下角工作表标签“Sheet1”,修改工作表名称为“培训登记表”，效果如图2-1-10所示。图 2-1-10 重命名工作表（3）单击【插入|页眉和页脚】菜单，移动光标至左侧单元格，输入页眉文字为“办公软件综合实训之EXCEL篇”，并设置字号为宋体、12号字，效果如图2-1-11所示。图 2-1-11 设置页眉Word 2016Office

8、 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 2:Step 2:录入标题点击页面返回至文档编辑区，移动光标至A2单元格，输入标题文字“学员培训登记表”，如图2-1-12所示。图 2-1-12 录入标题Step 3:Step 3:录入文字录入文字分别在A3、C3单元格输入“培训类别”、“培训项目”文字，对比样表，在3-15行单元格的1、3、5列中分别录入正文文字，如图2-1-13所示。图 2-1-13录入正文文字Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子

9、表格Office 办公软件应用高级教程高手大闯关Step 4:表格排版一（1）选中标题行1-7列，单击【开始|合并并居中】按钮，合并A2:G2列，设置字体为宋体、20号字加粗。（2）合并3-9行单元格：根据样表，分别单击合并【开始|合并并居中】按钮，合并D3:G3、A9:B9、E9:F9、G4:G8单元格。（3）设置3-9行行高：按住Shift键选中3-9行，右击选择【行高】，设置行高为0.85cm。（4）设置列宽：根据表格内容，分别调整A列至G列的列宽，参考设置为2.3、3、1.76、1.76、2、2.8、3.5厘米。（5）设置字体：选中正文所有文字，设置字体为宋体、12号、水平与垂直居中。

10、完成效果如图2-1-14所示。图 2-1-14表格排版效果图一Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 5:表格排版二 （1）设置第十行（个人简历行）行高为5cm,合并B10:G10单元格，将光标置于A10单元格，右击选择【设置单元格格式】，选中【文本控制】下的【自动换行】选项，设置A10文字为自动换行。（2）将光标移至第十一行，右击插入一行，设置第十一行、第十二行、第十三行行高为1.3，,合并A11、A12、A13单元格，设置文字内容为自动换行，合并B11:C11、D11:E

11、11、F11:G11单元格，分别输入“论文题目”、“发表刊物及时间”、“获奖情况”文字内容，同理合并B12:C12、D12:E12、F12:G12、B13:C13、D13:E13、F13:G13单元格。（3）设置第十四行行高为3，第十五行行高为5，第十六行行高为1.5，A14、A15、A16文字内容为自动换行，合并B14:G14、B15:G15、A16:G16单元格，在B15单元格中输入单位负责人、单位盖章及日期等文字内容。制作效果如图2-1-15所示。图 2-1-15 表格排版效果图二Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格O

12、ffice 办公软件应用高级教程高手大闯关Step 6:页面调整移动光标至G4单元格，输入文字“（照片）”，选中表格，单击【开始|边框】按钮，先设置所有框线，再设置精外侧框线，使用【开始|打印】或【页面设置】对话框中的【打印预览】按钮查看文档排版效果，根据页面进行行高与列宽的微调，以“培训表.xlsx”保存文件，案例制作完成。Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手勇拓展 练习案例外边框线为双实线，页面为横向，注意列宽与行高的设置，整体页面布局合理。小贴士!完成如图2-1-16所示的公司聘任

13、员工计划及薪酬预算表，源文件见资源文件“/2-1资源/公司聘任员工预算表.xlsx”。图 2-1-16 预算表制作效果图Word 2016Office 办公软件应用高级教程 Excel 高手速成数据数据验证设验证设置置Excel 电子表格Office 办公软件应用高级教程Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程 由制作学生成绩表案例入手，学习在Excel工作表中设置数据验证，掌握对数据有效性进行验证设置的技能。学习目标Word 2016Office 办公软件应用高级教程Excel 电子表格Off

14、ice 办公软件应用高级教程高手抢鲜看高手加油站高手大闯关高手勇拓展Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程在制作学生成绩表的过程中，对数据的类型和格式会有严格要求，因此需要在输入数据时对数据的有效性进行验证。下面小编就与大家一起来完成学生成绩表的制作。图2-2-1学生成绩表制作效果图高手抢鲜看Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站 数据有效性是对单元格或单元格区域输入的数据从内容到数

15、量上的限制。对于符合条件的数据，允许输入；对于不符合条件的数据，则禁止输入。这样就可以依靠系统检查数据的正确有效性，避免错误的数据录入。1.设置数据长度设置数据长度在学生成绩表中，学生的身份证号的长度是固定的，因此需要对输入的数据的长度进行限制，以避免输入错误数据，操作方法如下。（1）选中D3:D10单元格区域。如下图2-2-2所示。图 2-2-2 学生成绩表D3:D10单元格区域选中效果图Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站（2）单击【数据】选项卡下【数据工具】组中的【数据验证】

16、按钮，如图2-2-3所示。图2-2-3 数据工具组中的数据验证按钮（3）弹出【数据验证】对话框，选择【设置】选项卡，单击【验证条件】选项组内的【允许】文本框右侧的下拉按钮，在弹出的选项列表中选择【文本长度】选项。如图2-2-4所示。图2-2-4 数据验证对话框设置选项卡文本长度选项效果图Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站（4）数据文本框变为编辑状态，在【数据】文本框的下拉列表中选择【等于】选项，在【长度】文本框内输入“18”，选中【忽略空值】复选框，单击【确定】按钮。如图2-2-

17、5所示。图2-2-5 数据验证对话框设置文本长度效果图（5）即可完成设置输入数据长度的操作，当输入的文本长度不是18时，即会弹出提示窗口，如图2-2-6所示。图2-2-6 提示窗口Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站2.2.设置输入信息时的提示设置输入信息时的提示完成对单元格输入数据的长度限制设置后，可以设置输入信息时的提示信息，操作方法如下。（1）选中D3:D10单元格区域,单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，如上图2-2-3所示。图2-2-3 数据工具组中的

18、数据验证按钮（2）弹出【数据验证】对话框，选择【输入信息】选项卡，选中【选定单元格时显示输入信息】复选框，在【标题】文本框内输入“提示信息”，在【输入信息】文本框内输入“身份证号为18位，请正确输入！”，单击【确定】按钮，如图2-2-7所示。图2-2-7 输入信息选项卡Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站（3）返回Excel工作表中，选中设置了提示信息的单元格时，即可显示提示信息，效果如图2-2-8所示。图2-2-8 显示提示信息3.3.设置输错时的警告信息设置输错时的警告信息在输

19、入错误的数据时，可以设置警告信息提示，操作方法如下。（1）选中D3:D10单元格区域，单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，如上图2-2-3所示。图2-2-3 数据工具组中的数据验证按钮（2）弹出【数据验证】对话框，选择【出错警告】选项卡，选中【输入无效数据时显示出错警告】选择框，在【样式】下拉列表中选择【停止】选项，在【标题】文本框内输入文字“错误警告”，在【错误信息】文本框内输入文字“请输入正确的身份证号格式”，单击【确定】按钮，如图2-2-9所示。图2-2-9 出错警告选项卡Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel

20、 电子表格Office 办公软件应用高级教程高手加油站（3）如果输入错误数据时，即会弹出设置的警示信息，如图2-2-10所示。图2-2-10 错误警告信息4.4.设置单元格的下拉按钮设置单元格的下拉按钮在Excel工作表中，对数据设置有效性时除了以上几种情况以外，还可以将某些输入字段，设置下拉选项以方便输入，操作方法如下。（1）选中C3:C10单元格区域，单击【数据】选项卡下【数据工具】选项组中的【数据验证】按钮，如上图2-2-3所示。图2-2-3 数据工具组中的数据验证按钮Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office

21、办公软件应用高级教程高手加油站（2）弹出【数据验证】对话框，选择【设置】选项卡，单击【验证条件】选项组【允许】文本框内的下拉按钮，在弹出的下拉列表中选择【序列】选项，如下图2-2-11所示。图2-2-11 数据验证对话框设置选项卡（3）即可在【来源】文本框，输入“男，女”，同时选中【忽略空值】和【提供下拉箭头】复选框，单击确定按钮，如下图2-2-12所示。注：输入的逗号为半角逗号。图2-2-12 在来源文本框中输入信息Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站（3）在性别列的单元格后显示

22、下拉选项，单击下拉按钮，即可在下拉列表中选择性别，效果如图2-2-13所示。图2-2-13 设置完后的下拉按钮（4）使用同样的方法在C4:C10单元格区域内完成输入，效果如图2-2-1所示。图 2-2-1 学生成绩表制作效果图Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 1:设置数据长度（1）运行“Excel 2016”程序，单击【文件|打开】，打开“资源文件2/2-2资源/鑫海集团销售表”，如图2-2-14所示的界面。图 2-2-14 打开销售表（2）设置“订单编号”字段，D3:

23、D8单元格文本长度为“6”，如下图2-2-15所示。图 2-2-15 设置数据长度效果图Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 2:Step 2:设置提示信息在G3:G8单元格显示输入信息“输入提示，数据为带两位小数的正数”，效果如图2-2-16所示。图 2-2-16 设置销售额字段输入提示信息Step 3:Step 3:设置出错时警告信息设置出错时警告信息（1）在C3:C8单元格中设置日期限制，单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，在弹出【数据验证】对话框

24、，选择【设置】选项卡，单击【验证条件】选项组内的【允许】文本框右侧的下拉按钮，在弹出的选项列表中选择【日期】选项，在【数据】的下拉列表中选择【介于】，分别在【开始日期】中输入“2019-1-1”，在结束日期中输入“2019-12-31”，如图2-2-17所示。图 2-2-17 分别输入开始日期和结束日期Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关（2）在【出错警告】选项卡中输入“标题：错误提示；错误信息：请输入正确的日期格式”，如下图2-2-18所示。图2-2-18 出错警告Step 4:

25、设置下拉选项设置下拉选项（1）选中B3:B8单元格区域，单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，在弹出【数据验证】对话框，选择【设置】选项卡，单击【验证条件】选项组内的【允许】文本框右侧的下拉按钮，在弹出的选项列表中选择【序列】选项，如下图2-2-19所示。图 2-2-19 数据验证对话框设置选项卡Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 4:设置下拉选项设置下拉选项（2）在【来源】文本框中输入“北京，上海，广州，深圳，杭州，南京”，如下图2-2-20所示。图2

26、-2-20 在【来源】文本框中输入信息（3）最终形成如图2-2-21所示的效果图图 2-2-21 设置下拉选项效果图Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程 完成如图2-2-22所示的佳乐佳超市库存明细表结果文件，源文件见资源文件“/2-2资源/佳乐佳库存明细表.xlsx”。高手勇拓展设置数据有效性时，与一些函数一起使用，会起到意想不到的效果。小贴士!图 2-2-22 预算表制作效果图Word 2016Office 办公软件应用高级教程 Excel 高手速成文本函数文本函数应应用用Excel 电

27、子表格Office 办公软件应用高级教程Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程由制作学生明细表案例入手，学习在Excel工作表中TEXT函数、LEFT函数、RIGHT函数和MID函数的使用方法，掌握在Excel工作表中使用文本函数的技能。学习目标Word 2016Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手抢鲜看高手加油站高手大闯关高手勇拓展Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格

28、Office 办公软件应用高级教程文本函数不仅仅只用于改变数值成文本，用的妥当，可以当作逻辑函数，还可以附加变量、数组，使其更加灵活多变。文本函数也是Excel函数与公式基础入门必学之一。在我们实际生活、学习工作中，都有广泛的使用，下面小编就与大家一起来利用文本函数快速完成学生明细表的制作图2-3-1学生明细表制作效果图高手抢鲜看Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站文本函数是指可以在公式中处理字符串的函数，也具有改写大小写、连接文字串或者确定文字串的长度等功能，本章将就一些Exce

29、l电子表格中常用的文本函数操作进行重点介绍。1.TEXT函数函数TEXT函数的功能是将数值转换为按指定数字格式表示的文本。其语法格式如下：TEXT TEXT(valuevalue,format_textformat_text)参数Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。参数Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。其Format_text（单元格格式）部分参数代码（常用）如下图2-3-2所示。图 2-3-2 Format_text（单元格格式）部分参数代码（常用）Word 2016Office 办公软件应

30、用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站2.LEFT函数函数LEFT函数的功能是基于所指定的字符数返回文本字符串中的第一个或前几个字符。函数的语法格式如下：LEFT（text,num_chars）参数text表示包含要提取字符的文本字符串；参数num_chars指定函数LEFT所要提取的字符数。参数num_chars必须大于或等于0，当等于0时函数的返回值为空；如果参数num_chars小于0，则返回错误值“#VALUE!”；如果其取值大于文本长度，则返回所有的字符；如果省略，则认为是1。函数的相关应用如图2-3-3所示。图

31、2-3-3 LEFT函数参数的相关应用Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站3.RIGHT3.RIGHT函数函数RIGHT函数的作用是根据所指定的字符数返回文本字符串中最后一个或多个字符。函数的语法格式如下:RIGHT(text,num_chars)从表达式中可以看出这两个函数都只有两个参数。Text表示包含要提取字符的文本字符串；num_chars指定函数RIGHT所要提取的字符数，参数num_chars必须大于或等于0，当等于0时函数的返回值为空；如果参数num_chars小于0

32、时，由返回错误值“#VALUE!”；如果取值大于文本长度，则返回所有的字符；如果省略，则认为是1。函数的相关应用如图2-3-4所示。图2-3-4 RIGHT函数参数的相关应用Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站4.MID4.MID函数函数MID函数的功能是返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定，字符包括空格。函数的语法格式如下：MID（text,start_num,num_chars）参数text表示包含要提取字符的文本字符串；参数start_num表示文

33、本中要提取的第一个字符的位置，文本中第一个字符的位置为1，第二个字符的位置为2，依次类推。参数num_chars指定函数从文本中返回字符的个数。在使用时需要注意以下几点：（1）如果参数start_num大于文本长度，则函数返回空值。（2）如果参数start_num小于文本长度，但参数start_num加上num_chars超过了文本的长度，则函数返回要提取的第一个字符直到最后的一个字符。（3）如果函数start_num小于1，参数num_chars是负数，函数则返回错误值“#VALUE!”。函数的相关应用如图2-3-5所示。图2-3-5 MID函数参数的相关应用Word 2016Office

34、办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 1:打开文件运行“Excel 2016”程序，单击【文件|打开】，“资源文件3/2-3资源/旅游三班学生明细表”，如图2-3-6所示的界面。图 2-3-6 旅游三班学生明细表Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step Step 2:2:使用TEXT函数和MID函数，分别提取身份证号码的出生日期和性别（1）如何从身份证号码中提取出生日期，这是很多人都在问的

35、一个问题，使用TEXT函数可以很容易的实现，选中学生明细表D3单元格，在该单元格设置公式为：=TEXT（MID(C3,7,8),”0000-00-00”）。首先使用MID函数从身份证号码中的第7位开始提取8个数字出来，这部分就是出生日期，再用TEXT函数将这个8位数字以“0000-00-00”的格式显示，此时得到出生日期格式，如下图2-3-7所示。图 2-3-7 使用TEXT函数提取身份证号码的出生日期Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step Step 2:2:使用TEXT函数

36、和MID函数，分别提取身份证号码的出生日期和性别(2)在身份证号码中，除了含有出生日期之外，还能判断性别，倒数第二位表示性别，男性为奇数，女性为偶数。因此，根据这个规则，可以在E3单元格中设置公式：=TEXT(MOD(MID(c3,17,1),2),”男;女”)。首先用MID函数提取18位身份证号码中的第17位，即MID(C3,17,1)；再用MOD函数（被除数，除数），而结果是余数，本例中被除数是身份证号码的第17位数字，除数是2，当被除数是偶数时，余数为零，反之余数为1，利用TEXT的四段分类显示规则“正;负;零;文本”，将正数定义为“男”，零定义为“女”，就实现了提取性别的目的，如下图2

37、-3-8所示。图2-3-8 使用TEXT函数判断性别Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 3:使用LEFT函数提取户籍所在省Step 4:使用RIGHT函数提取详细地址在编辑学生明细表时，会常常遇到将录取学生的家庭住址，分别设置为“户籍所在省”、“户籍所在市”和“详细地址”，以往在填写时会非常麻烦，现在就可以使用LEFT函数提取字符，在H3单元格中输入公式：=LEFT(G3,3)完成相关操作，如下图2-3-9所示。图2-3-9 使用LEFT函数提取户籍在省 选中J3单元格，

38、使用RIGHT函数输入公式：=RIGHT（G3,6）完成相关操作，如下图2-3-10所示。图 2-3-10 使用RIGHT函数提取详细地址Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 5:使用MID函数提取户籍所在市选中I3单元格，使用MID函数输入公式：=MID（G3,4，3）完成相关操作，如下图2-3-11所示。图 2-3-11 使用MID函数提取户籍所在市最终完成学生明细表的制作，如图2-3-1所示。图 2-3-1 学生明细表制作效果图Word 2016Office 办公软件

39、应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程参照如图2-3-12所示的大高公司员工明细表原文件效果图，源文件见资源文件“/2-3资源/大高公司员工明细表.xlsx”，最终完成如图2-3-13所示的结果文件效果图。高手勇拓展 文本函数TEXT有着十分强大的功能，与其它函数一起使用，往往会起到意想不到的效果。小贴士!图 2-3-12 大高公司员工明细表效果图图2-3-13 大高公司员工明细表结果文件效果图Word 2016Office 办公软件应用高级教程 Excel 高手速成数据条件求和数据条件求和Excel 电子表格Office 办公软件

40、应用高级教程Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程 由制作梁山泊员工工资表案例入手，学习在Excel工作表单元格地址引用的方法及SUMIF函数和SUMIFS函数的使用，掌握使用以上函数完成多条件下的计算技能。学习目标Word 2016Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手抢鲜看高手加油站高手大闯关高手勇拓展Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级

41、教程在实际使用Excel工作时，有时会遇到对一些含有一定条件的数据进行求和运算，熟练使用SUMIF函数和SUMIFS函数会起到事半功倍的效果，下面小编就与大家一起来学习两个函数，完成员工工资表的制作。图2-4-1员工工资表制作效果图高手抢鲜看Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站在公式运算时，单元格的引用是非常重要的。Excel将单元格的引用分为三类：即相对引用、绝对引用和混合引用。下面在学习函数之前，先介绍一下三种单元格的引用。1.1.单元格引用单元格引用（1）相对引用）相对引用相

42、对引用是指，将含有公式的单元格复制到其他地方时，公式内的从属单元格会根据目的单元格发生一定变化的引用方式。例 如，在 单 元 格 C7内 输 入 公 式“=C5+C6”,再将单元格复制至D7中。这时，选择就是相对引用方式，则D7中输入的公式是“=D5+D6”。如下图2-4-2，2-4-3所示。图 2-4-2 相对引用之前公式 图 2-4-3 相对引用之后公式Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加油站（2）绝对引用）绝对引用绝对引用是指，不受目的单元格的影响，公式内的从属单元格恒定不变的一

43、种引用方式。”绝对引用时，需要在行号列标前添加“$”。例如，设单元格H5为绝对引用时输入”$H$5”，H6单元格引用时输入“$H$6”，单元格H7输入公式为：”=$H$5+$H$6”，其结果为下图2-4-4所示。图 2-4-4 绝对地址引用公式（3）混合引用）混合引用混合引用是指，仅固定行和列中一方的引用方式。混合引用是一种生成矩阵表时非常实用的引用方式，行和列上分别罗列计算用值，在交叉位置显示计算结果，如下图2-4-5所示的“九九乘法表”。图2-4-5 混合引用公式范例“九九乘法表”Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Of

44、fice 办公软件应用高级教程高手加油站2.SUMIF函数函数SUMIF函数是重要的数学和三角函数，在Excel2016工作表的实际操作中应用广泛，其功能是根据指定条件对指定的若干单元格求和。使用该函数可以在选中的范围内求与检索条件一致的单元格对应的合计范围的数值。SUMIF函数的语法格式如下：SUMIF（range,criteria,sum_range）Range：指的是选定的用于条件判断的单元格区域。Criteria：指的是在指定单元格区域内检索符合条件的单元格，其形式可以是数字，表达式或文本。直接在单元格或编辑栏中输入检索条件时，需要加双引号。Sum_range：选定的需要求和的单元格区

45、域。该参数忽略求和的单元格区域内包含的空白单元格、逻辑值或文本。接下来，使用SUMIF函数对科目总分进行求和，如下图2-4-6所示。图 2-4-6使用SUMFI函数对科目总分求和在上面的例子中，对F3（求和条件）和单元格区域B3:B8的值依次进行比较，仅在满足条件时，对单元格区域C3:C8中对应的值进行求和并显示。另外，求和条件中还可指定“=95”或“95”这类使用了不等号的表达式，此时，就分别表示“是否大于等于95”或“是否小于95”这样的判定式。Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手加

46、油站3.SUMIFS3.SUMIFS函数函数SUMIFS函数是Office2007新增函数，使用该函数可快速对多条件单元格求和，SUMIFS函数功能十分强大，可以通过不同范围的条件，求规定范围的和。SUMIFS函数的语法格式如下：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,.)Criteria _range1：为计算关联条件的第一个区域。Criteria 1：为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range1参数中的哪些单元格求和。Criteria _

47、range2：为计算关联条件的第二个区域。Criteria 2：为条件2 均成对出现。最多允许127个区域、条件对，即参数总数不超255个。Sum_range：是需要求和的实际单元格。包括数字或包含数字的名称、区域或单元格引用，忽略空白值和文本值。接下来，使用SUMIFS函数对带有一定条件的科目总分进行求和，如下图2-4-7所示。图2-4-7 使用SUMIFS函数求和在上面的例子中，对单元格区域D3:D11中对应的值进行求和并显示，第一个求和条件区域是B3:B11，对应的求和条件是F3；第二个求和条件区域是C3:C11，对应的求和条件是G3，根据以上条件，对符合条件的区域的值依次进行比较，然后

48、求和。Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 1:打开文件 （1）运行“Excel 2016”程序，单击【文件|打开】，打开资源文件“/4/2-4资源/梁水泊工资明细表”，出现如图2-4-8所示的界面。图 2-4-8 员工工资明细表 （2）使用SUM函数求出I3所在单元格“实发工资”，如下图2-4-9所示。图 2-4-9 使用SUM函数求出实发工资实发工资由（基础工资+岗位工资+津贴-五险一金）求出小贴士!Word 2016Office 办公软件应用高级教程Office 办公

49、软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 2:Step 2:SUMIF函数 使用SUMIF函数，求符合条件是男教师基础工资总和，如图2-4-10所示。图2-4-10 使用SUMIF函数求男教师的基础工资总计最终完成如图2-4-11所示的结果效果图。图 2-4-11 使用SUMIF函数求男教师的基础工资总计结果效果图Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程高手大闯关Step 3:SUMIFS函数使用SUMIFS函数，求符合条件，职称是副教授的男教师基

50、础工资总和，如图2-4-12所示。图2-4-12 使用SUMIFS函数求男教师职称是副教授的基础工资总计最终完成如图2-4-1所示的结果效果图。图 2-4-1使用SUMIFS函数求男教师职称是副教授的基础工资总计结果效果图Word 2016Office 办公软件应用高级教程Office 办公软件应用高级教程Excel 电子表格Office 办公软件应用高级教程 完成如图2-4-13，图2-4-14所示的新天地超市二季度销售统计表和科目总分统计表，源文件/资源文件“/2-4资源/新天地超市二季度销售统计表xlsx”和“科目总分统计表.xlsx”。高手勇拓展练习案例时，当判定对象为字符串时，可以使