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

编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,编辑母版文本样式,第二级,第三级,第四级,第五级,2021/10/16,#,Word 2016,Office,办公软件应用高级教程,表格基本编辑,Excel,高手速成,Excel,电子表格,Office,办公软件应用高级教程,学习目标,由制作培训登记表案例入手，学习,EXCEL,工作表及单元格的编辑方法，掌握,EXCEL,编制基本表格技能。,Excel,电子表格,Office,办公软件应用高级教程,高手抢鲜看,高手,加油站,高手,大闯关,高手,勇拓展,初入职场，设计一份员工培训计划表是办公人员所必须具备的基本功，下面小编就与大家一起来完成员工培训表的制作,。,图,2-1-1,学员培训登记表制作,效果图,高手抢鲜看,进入到办公高手实习段位的各位大侠们一定已经掌握了基本的,Excel,电子表格操作技能（如需要学习基础技能可以查看与本教材配套的,Office,办公软件案例教程一书），本章将就一些,Excel,电子表格的操作技巧进行重点介绍。,一,.,单元格内文字换行,在,Excel,电子表格中，有时需要对单元格内文字进行换行，换行前后的文字效果如图,2-1-2,所示，以下三种方法可以轻松实现。,图,2-1-2,换行前后的单元格文字效果,高手,加油站,（,1,）,功能选项卡换行法,在,Excel,单元格中输入文字后，单击【开始,|,自动换行】功能选项卡，如图,2-1-3,所示，单元格内的文字即可实现自动换行。,图,2-1-3,使用“自动换行”功能选项卡实现文字换行,高手,加油站,（,2,）,右键菜单换行法,选中要自动换行的单元格，右击选择【设置单元格格式】，在【设置单元格格式】对话框中选择【对齐】选项卡，在【文本控制】组中选中【自动换行】选项，如图,2-1-4,所示，所选中的全部单元格中的文字即可实现自动换行。,图,2-1-4,使用右键菜单实现文字换行,（,3,）,快捷键强制换行法,有时,Excel,单元格内的文字不需要自动换行，而需要在特定的文字位置处换行，这时可以使用快捷键的方法进行强制换行，具体方法为将光标定位在需要换行的文字位置，按【,Alt+Enter,】组合键即可以实现在任意文字位置上的换行。换行效果如图,2-1-5,所示。,图,2-1-5,使用快捷键实现文字强制换行效果,高手,加油站,2.,合并单元格,在,Excel,电子表格中，选中需要合并的单元格，单击【开始,|,合并后居中】功能选项卡,，,可以将选中的单元格合并，此按钮为开关键，对合并后的单元格再次单击该按钮，也可以单击【合并后并居中】右侧下三角按钮，在展开的下拉列表中单击【取消单元格合并】选项（如图,2-1-6,所示），单元格将拆分成合并前默认的单元格。,图,2-1-6,合并后居中选项卡实现单元格合并,图,1-6-8,公文主体设置,高手,加油站,3.,调整行高和列宽,当单元格宽度不够时，所录入的数字等内容经常出现“,#,”显示效果，此时可以采用拖动、双击和精确设置三种方法来调整单元格的行高与列宽。,（,1,）,拖动：将光标置于要设置行高或列宽的单元格内，按住鼠标左键拖动行边线或列边线，可以增加或减少行高或列宽。,（,2,）,双击法：直接双击列边线或行边线，可以设置该列或该行最适合的列宽。,（,3,）,精确设置法：选中行标签或列标签右击，在图,2-1-7,中选择【行高】或在图,2-1-8,中选择【列宽】，即可精确设置行高或列宽。,图,2-1-7,设置行高,图,2-1-8,设置列宽,高手,大闯关,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,设置页眉,高手,大闯关,Step 2:,录入标题,点击页面返回至文档编辑区，移动光标至,A2,单元格，输入标题文字学员培训登记表”，如图,2-1-12,所示。,图,2-1-12,录入标题,Step 3:,录入文字,分别在,A3,、,C3,单元格输入“培训类别”、“培训项目”文字，对比样表，在,3-15,行单元格的,1,、,3,、,5,列中分别录入正文文字，如图,2-1-13,所示。,图,2-1-13,录入正文文字,高手,大闯关,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,号、水平与垂直居中。完成效果如图,2-1-14,所示。,图,2-1-14,表格排版效果图一,高手,大闯关,Step 5:,表格排版二,（,1,）,设置第十行（个人简历行）行高为,5cm,合并,B10:G10,单元格，将光标置于,A10,单元格，右击选择【设置单元格格式】，选中【文本控制】下的【自动换行】选项，设置,A10,文字为自动换行。,（,2,）,将光标移至第十一行，右击插入一行，设置第十一行、第十二行、第十三行行高为,1.3,，,合并,A11,、,A12,、,A13,单元格，设置文字内容为自动换行，合并,B11:C11,、,D11:E11,、,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,表格排版效果图二,高手,大闯关,Step 6:,页面调整,移动光标至,G4,单元格，输入文字“（照片）”，选中表格，单击【开始,|,边框】按钮，先设置所有框线，再设置精外侧框线，使用【开始,|,打印】或【页面设置】对话框中的【打印预览】按钮查看文档排版效果，根据页面进行行高与列宽的微调，以“培训表,.xlsx,”保存文件，案例制作完成。,高手,勇拓展,练习案例外边框线为双实线，页面为横向，注意列宽与行高的设置，整体页面布局合理。,小贴士,!,完成如图,2-1-16,所示的公司聘任员工计划及薪酬预算表，源文件见资源文件“,/2-1,资源,/,公司聘任员工预算表,.xlsx,”。,图,2-1-16,预算表制作效果图,Excel,高手速成,数据验证设置,Excel,电子表格,Office,办公软件应用高级教程,由制作学生成绩表案例入手，学习在,Excel,工作表中设置数据验证，掌握对数据有效性进行验证设置的技能。,学习目标,Excel,电子表格,Office,办公软件应用高级教程,高手抢鲜看,高手加油站,高手,大闯关,高手,勇拓展,在制作学生成绩表的过程中，对数据的类型和格式会有严格要求，因此需要在输入数据时对数据的有效性进行验证。下面小编就与大家一起来完成学生成绩表的制作,。,图,2-2-1,学生成绩表制作,效果图,高手抢鲜看,高手,加油站,数据有效性是对单元格或单元格区域输入的数据从内容到数量上的限制。对于符合条件的数据，允许输入；对于不符合条件的数据，则禁止输入。这样就可以依靠系统检查数据的正确有效性，避免错误的数据录入。,设置数据长度,在学生成绩表中，学生的身份证号的长度是固定的，因此需要对输入的数据的长度进行限制，以避免输入错误数据，操作方法如下。,（,1,）选中,D3:D10,单元格区域。如下图,2-2-2,所示。,图,2-2-2,学生成绩表,D3:D10,单元格区域选中效果图,高手,加油站,（,2,）单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，如图,2-2-3,所示。,图,2-2-3,数据工具组中的数据验证按钮,（,3,）弹出【数据验证】对话框，选择【设置】选项卡，单击【验证条件】选项组内的【允许】文本框右侧的下拉按钮，在弹出的选项列表中选择【文本长度】选项。如图,2-2-4,所示。,图,2-2-4,数据验证对话框设置选项卡文本长度选项效果图,高手,加油站,（,4,）数据文本框变为编辑状态，在【数据】文本框的下拉列表中选择【等于】选项，在【长度】文本框内输入“,18”,，选中【忽略空值】复选框，单击【确定】按钮。如图,2-2-5,所示。,图,2-2-5,数据验证对话框设置文本长度效果图,（,5,）即可完成设置输入数据长度的操作，当输入的文本长度不是,18,时，即会弹出提示窗口，如图,2-2-6,所示。,图,2-2-6,提示窗口,高手,加油站,2.,设置输入信息时的提示,完成对单元格输入数据的长度限制设置后，可以设置输入信息时的提示信息，操作方法如下。,（,1,）选中,D3:D10,单元格区域,单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，如上图,2-2-3,所示。,图,2-2-3,数据工具组中的数据验证按钮,（,2,）弹出【数据验证】对话框，选择【输入信息】选项卡，选中【选定单元格时显示输入信息】复选框，在【标题】文本框内输入“提示信息”，在【输入信息】文本框内输入“身份证号为,18,位，请正确输入！”，单击【确定】按钮，如图,2-2-7,所示。,图,2-2-7,输入信息选项卡,高手,加油站,（,3,）返回,Excel,工作表中，选中设置了提示信息的单元格时，即可显示提示信息，效果如图,2-2-8,所示。,图,2-2-8,显示提示信息,3.,设置输错时的警告信息,在输入错误的数据时，可以设置警告信息提示，操作方法如下。,（,1,）选中,D3:D10,单元格区域，单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，如上图,2-2-3,所示。,图,2-2-3,数据工具组中的数据验证按钮,（,2,）弹出【数据验证】对话框，选择【出错警告】选项卡，选中【输入无效数据时显示出错警告】选择框，在【样式】下拉列表中选择【停止】选项，在【标题】文本框内输入文字“错误警告”，在【错误信息】文本框内输入文字“请输入正确的身份证号格式”，单击【确定】按钮，如图,2-2-9,所示。,图,2-2-9,出错警告选项卡,高手,加油站,（,3,）如果输入错误数据时，即会弹出设置的警示信息，如图,2-2-10,所示。,图,2-2-10,错误警告信息,4.,设置单元格的下拉按钮,在,Excel,工作表中，对数据设置有效性时除了以上几种情况以外，还可以将某些输入字段，设置下拉选项以方便输入，操作方法如下。,（,1,）选中,C3:C10,单元格区域，单击【数据】选项卡下【数据工具】选项组中的【数据验证】按钮，如上图,2-2-3,所示。,图,2-2-3,数据工具组中的数据验证按钮,高手,加油站,（,2,）弹出【数据验证】对话框，选择【设置】选项卡，单击【验证条件】选项组【允许】文本框内的下拉按钮，在弹出的下拉列表中选择【序列】选项，如下图,2-2-11,所示。,图,2-2-11,数据验证对话框设置选项卡,（,3,）即可在【来源】文本框，输入“男，女”，同时选中【忽略空值】和【提供下拉箭头】复选框，单击确定按钮，如下图,2-2-12,所示。注：输入的逗号为半角逗号。,图,2-2-12,在来源文本框中输入信息,高手,加油站,（,3,）在性别列的单元格后显示下拉选项，单击下拉按钮，即可在下拉列表中选择性别，效果如图,2-2-13,所示。,图,2-2-13,设置完后的下拉按钮,（,4,）使用同样的方法在,C4:C10,单元格区域内完成输入，效果如图,2-2-1,所示。,图,2-2-1,学生成绩表制作效果图,高手,大闯关,Step 1:,设置数据长度,（,1,）运行“,Excel 2016,”程序，单击【文件,|,打开】，打开“资源文件,2/2-2,资源,/,鑫海集团销售表”，如图,2-2-14,所示的界面。,图,2-2-14,打开销售表,（,2,）设置“订单编号”字段，,D3:D8,单元格文本长度为“,6,”,，如下图,2-2-15,所示。,图,2-2-15,设置数据长度效果图,高手,大闯关,Step 2:,设置提示信息,在,G3:G8,单元格显示输入信息“输入提示，数据为带两位小数的正数”，效果如图,2-2-16,所示。,图,2-2-16,设置销售额字段输入提示信息,Step 3:,设置出错时警告信息,（,1,）在,C3:C8,单元格中设置日期限制，单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，在弹出【数据验证】对话框，选择【设置】选项卡，单击【验证条件】选项组内的【允许】文本框右侧的下拉按钮，在弹出的选项列表中选择【日期】选项，在【数据】的下拉列表中选择【介于】，分别在【开始日期】中输入“,2019-1-1,”,，在结束日期中输入“,2019-12-31,”,，如图,2-2-17,所示。,图,2-2-17,分别输入开始日期和结束日期,高手,大闯关,（,2,）在【出错警告】选项卡中输入“标题：错误提示；错误信息：请输入正确的日期格式”，如下图,2-2-18,所示。,图,2-2-18,出错警告,Step 4:,设置下拉选项,（,1,）选中,B3:B8,单元格区域，单击【数据】选项卡下【数据工具】组中的【数据验证】按钮，在弹出【数据验证】对话框，选择【设置】选项卡，单击【验证条件】选项组内的允许】文本框右侧的下拉按钮，在弹出的选项列表中选择【序列】选项，如下图,2-2-19,所示。,图,2-2-19,数据验证对话框设置选项卡,高手,大闯关,Step 4:,设置下拉选项,（,2,）在【来源】文本框中输入“北京，上海，广州，深圳，杭州，南京”，如下图,2-2-20,所示。,图,2-2-20,在【来源】文本框中输入信息,（,3,）最终形成如图,2-2-21,所示的效果图,图,2-2-21,设置下拉选项效果图,完成如图,2-2-22,所示的佳乐佳超市库存明细表结果文件，源文件见资源文件“,/2-2,资源,/,佳乐佳库存明细表,.xlsx,”。,高手,勇拓展,设置数据有效性时，与一些函数一起使用，会起到意想不到的效果。,小贴士,!,图,2-2-22,预算表制作效果图,Excel,高手速成,文本函数应用,Excel,电子表格,Office,办公软件应用高级教程,由制作学生明细表案例入手，学习在,Excel,工作表中,TEXT,函数、,LEFT,函数、,RIGHT,函数和,MID,函数的使用方法，掌握在,Excel,工作表中使用文本函数的技能。,学习目标,Excel,电子表格,Office,办公软件应用高级教程,高手抢鲜看,高手,加油站,高手,大闯关,高手,勇拓展,文本函数不仅仅只用于改变数值成文本，用的妥当，可以当作逻辑函数，还可以附加变量、,数组,，使其更加灵活多变。文本函数也是,Excel,函数与公式基础入门必学之一。在我们实际生活、学习工作中，都有广泛的使用，下面小编就与大家一起来利用文本函数快速完成学生明细表的制作,图,2-3-1,学生明细表制作,效果图,高手抢鲜看,高手,加油站,文本函数是指可以在公式中处理字符串的函数，也具有改写大小写、连接文字串或者确定文字串的长度等功能，本章将就一些,Excel,电子表格中常用的文本函数操作进行重点介绍。,TEXT,函数,TEXT,函数的功能是,将数值转换为按指定数字格式表示的文本。,其语法格式如下：,TEXT,(,value,format_text,),参数,Value,为数值、计算结果为数字值的公式，或对包含数字值的,单元,格,的引用。,参数,Format_text,为,“,单元格格式,”,对话框中,“,数字,”,选项卡上,“,分类,”,框中的,文本形式,的数字格式。,其,Format_text,（单元格格式）部分参数代码（常用）如下图,2-3-2,所示。,图,2-3-2,Format_text,（单元格格式）部分参数代码（常用）,高手,加油站,2.LEFT,函数,LEFT,函数的功能是基于所指定的字符数返回文本字符串中的第一个或前几个字符。函数的语法格式如下：,LEFT,（,text,num_chars,）,参数,text,表示包含要提取字符的文本字符串；参数,num_chars,指定函数,LEFT,所要提取的字符数。参数,num_chars,必须大于或等于,0,，当等于,0,时函数的返回值为空；如果参数,num_chars,小于,0,，则返回错误值“,#VALUE!”,；如果其取值大于文本长度，则返回所有的字符；如果省略，则认为是,1,。函数的相关应用如图,2-3-3,所示。,图,2-3-3 LEFT,函数参数的相关应用,高手,加油站,3.RIGHT,函数,RIGHT,函数的作用是根据所指定的字符数返回文本字符串中最后一个或多个字符。函数的语法格式如下,:,RIGHT(text,num_chars),从表达式中可以看出这两个函数都只有两个参数。,Text,表示包含要提取字符的文本字符串；num_chars,指定函数,RIGHT,所要提取的字符数，参数,num_chars,必须大于或等于,0,，当等于,0,时函数的返回值为空；如果参数,num_chars,小于,0,时，由返回错误值“,#VALUE!”,；如果取值大于文本长度，则返回所有的字符；如果省略，则认为是,1,。函数的相关应用如图,2-3-4,所示。,图,2-3-4 RIGHT,函数参数的相关应用,高手,加油站,4.MID,函数,MID,函数的功能是返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定，字符包括空格。函数的语法格式如下：,MID,（,text,start_num,num_chars,）,参数text,表示包含要提取字符的文本字符串；参数,start_num,表示文本中要提取的第一个字符的位置，文本中第一个字符的位置为,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,函数参数的相关应用,高手,大闯关,Step 1:,打开文件,运行“,Excel 2016,”程序，单击【文件,|,打开】，“资源文件,3/2-3,资源,/,旅游三班学生明细表”，如图,2-3-6,所示的界面。,图,2-3-6,旅游三班学生明细表,高手,大闯关,Step 2:,使用,TEXT,函数和,MID,函数，分别提取身份证号码的出生日期和性别,（,1,）如何从身份证号码中提取出生日期，这是很多人都在问的一个问题，使用,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,函数提取身份证号码的出生日期,高手,大闯关,Step 2:,使用,TEXT,函数和,MID,函数，分别提取身份证号码的出生日期和性别,(2),在身份证号码中，除了含有出生日期之外，还能判断性别，倒数第二位表示性别，男性为奇数，女性为偶数。因此，根据这个规则，可以在,E3,单元格中设置公式：,=TEXT(MOD(MID(c3,17,1),2),”,男,;,女,”,),。,首先用,MID,函数提取,18,位身份证号码中的第,17,位，即,MID(C3,17,1),；再用,MOD,函数（被除数，除数），而结果是余数，本例中被除数是身份证号码的第,17,位数字，除数是,2,，当被除数是偶数时，余数为零，反之余数为,1,，利用,TEXT,的四段分类显示规则“正,;,负,;,零,;,文本”，将正数定义为“男”，零定义为“女”，就实现了提取性别的目的，如下图,2-3-8,所示。,图,2-3-8,使用,TEXT,函数判断性别,高手,大闯关,Step 3:,使用,LEFT,函数提取户籍所在省,Step 4:,使用,RIGHT,函数提取详细地址,在编辑学生明细表时，会常常遇到将录取学生的家庭住址，分别设置为“户籍所在省”、“户籍所在市”和“详细地址”，以往在填写时会非常麻烦，现在就可以使用,LEFT,函数提取字符，在,H3,单元格中输入公式：,=LEFT(G3,3),完成相关操作，如下图,2-3-9,所示。,图,2-3-9,使用,LEFT,函数提取户籍在省,选中,J3,单元格，使用,RIGHT,函数输入公式：,=RIGHT,（,G3,6,）完成相关操作，如下图,2-3-10,所示。,图,2-3-10,使用,RIGHT,函数提取详细地址,高手,大闯关,Step 5:,使用,MID,函数提取户籍所在市,选中,I3,单元格，使用,MID,函数输入公式：,=MID,（,G3,4,，,3,）完成相关操作，如下图,2-3-11,所示。,图,2-3-11,使用,MID,函数提取户籍所在市,最终完成学生明细表的制作，如图,2-3-1,所示。,图,2-3-1,学生明细表制作效果图,参照如图,2-3-12,所示的大高公司员工明细表原文件效果图，源文件见资源文件“,/2-3,资源,/,大高公司员工明细表,.xlsx,”,，,最终完成如图,2-3-13,所示的结果文件效果图。,高手,勇拓展,文本函数,TEXT,有着十分强大的功能，与其它函数一起使用，往往会起到意想不到的效果。,。,小贴士,!,图,2-3-12,大高公司员工明细表效果图,图,2-3-13,大高公司员工明细表结果文件效果图,Excel,高手速成,数据条件求和,Excel,电子表格,Office,办公软件应用高级教程,由制作梁山泊员工工资表案例入手，学习在,Excel,工作表单元格地址引用的方法及,SUMIF,函数和,SUMIFS,函数的使用，掌握使用以上函数完成多条件下的计算技能。,学习目标,Excel,电子表格,Office,办公软件应用高级教程,高手抢鲜看,高手,加油站,高手,大闯关,高手,勇拓展,在实际使用,Excel,工作时，有时会遇到对一些含有一定条件的数据进行求和运算，熟练使用,SUMIF,函数和,SUMIFS,函数会起到事半功倍的效果，下面小编就与大家一起来学习两个函数，完成员工工资表的制作,。,图,2-4-1,员工工资表制作,效果图,高手抢鲜看,高手,加油站,在公式运算时，单元格的引用是非常重要的。,Excel,将单元格的引用分为三类：即相对引用、绝对引用和混合引用。下面在学习函数之前，先介绍一下三种单元格的引用。,1.,单元格引用,（,1,）相对引用,相对引用是指，将含有公式的单元格复制到其他地方时，公式内的从属单元格会根据目的单元格发生一定变化的引用方式。例如，在单元格,C7,内输入公式,“,=C5+C6”,再将单元格复制至,D7,中。这时，选择就是相对引用方式，则,D7,中输入的公式是,“,=D5+D6”,。如下图,2-4-2,，,2-4-3,所示。,图,2-4-2,相对引用之前公式,图,2-4-3,相对引用之后公式,高手,加油站,（,2,）绝对引用,绝对引用是指，不受目的单元格的影响，公式内的从属单元格恒定不变的一种引用方式。”绝对引用时，需要在行号列标前添加“,$,”。例如，设单元格,H5,为绝对引用时输入,”$H$5”,，,H6,单元格引用时输入“,$H$6”,，单元格,H7,输入公式为：”,=$H$5+$H$6”,，其结果为下图,2-4-4,所示。,图,2-4-4,绝对地址引用公式,（,3,）混合引用,混合引用是指，仅固定行和列中一方的引用方式。混合引用是一种生成矩阵表时非常实用的引用方式，行和列上分别罗列计算用值，在交叉位置显示计算结果，如下图,2-4-5,所示的“九九乘法表”。,图,2-4-5,混合引用公式范例“九九乘法表”,高手,加油站,2.SUMIF,函数,SUMIF,函数是重要的数学和三角函数，在,Excel2016,工作表的实际操作中应用广泛，其功能是根据指定条件对指定的若干单元格求和。使用该函数可以在选中的范围内求与检索条件一致的单元格对应的合计范围的数值。,SUMIF,函数的语法格式如下：,SUMIF,（,range,criteria,sum_range,）,Range,：指的是选定的用于条件判断的单元格区域。Criteria,：指的是在指定单元格区域内检索符合条件的单元格，其形式可以是数字，表达式或文本。直接在单元格或编辑栏中输入检索条件时，需要加双引号。,Sum_range,：选定的需要求和的单元格区域。该参数忽略求和的单元格区域内包含的空白单元格、逻辑值或文本。,接下来，使用,SUMIF,函数对科目总分进行求和，如下图,2-4-6,所示。,图,2-4-6,使用,SUMFI,函数对科目总分求和,在上面的例子中，对,F3,（求和条件）和单元格区域,B3:B8,的值依次进行比较，仅在满足条件时，对单元格区域,C3:C8,中对应的值进行求和并显示。,另外，求和条件中还可指定“,=95”,或“,95”这类使用了不等号的表达式，此时，就分别表示“是否大于等于,95,”或“是否小于,95”,这样的判定式。,高手,加油站,3.SUMIFS,函数,SUMIFS,函数是,Office2007,新增函数，使用该函数可快速对多条件单元格求和，,SUMIFS,函数,功能十分强大，可以通过不同范围的条件，求规定范围的和。,SUMIFS,函数的语法格式如下：,SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,.),Criteria,_range1,：为计算关联条件的第一个区域。,Criteria,1,：为条件,1,，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对,criteria_range1,参数中的哪些单元格求和。,Criteria,_range2,：为计算关联条件的第二个区域。,Criteria,2,：为条件,2,均成对出现。最多允许,127,个区域、条件对，即参数总数不超,255,个。,Sum_range,：是需要求和的实际单元格。包括数字或包含数字的名称、区域或单元格引用，忽略空白值和文本值。,接下来，使用,SUMIFS,函数对带有一定条件的科目总分进行求和，如下图,2-4-7,所示。,图,2-4-7,使用,SUMIFS,函数求和,在上面的例子中，对单元格区域,D3:D11,中对应的值进行求和并显示，第一个求和条件区域是,B3:B11,，对应的求和条件是,F3,；第二个求和条件区域是,C3:C11,，对应的求和条件是,G3,，根据以上条件，对符合条件的区域的值依次进行比较，然后求和。,高手,大闯关,Step 1:,打开文件,（,1,）,运行“,Excel 2016,”程序，单击【文件,|,打开】，打开资源文件“,/4/2-4,资源,/,梁水泊工资明细表”，出现如图,2-4-8,所示的界面。,图,2-4-8,员工工资明细表,（,2,）,使用,SUM,函数求出,I3,所在单元格“实发工资”，如下图,2-4-9,所示。,图,2-4-9,使用,SUM,函数求出实发工资,实发工资由（基础工资,+,岗位工资,+,津贴,-,五险一金）求出,小贴士,!,高手,大闯关,Step 2:,SUMIF,函数,使用,SUMIF,函数，求符合条件是男教师基础工资总和，如图,2-4-10,所示。,图,2-4-10,使用,SUMIF,函数求男教师的基础工资总计,最终完成如图,2-4-11,所示的结果效果图。,图,2-4-11,使用,SUMIF,函数求男教师的基础工资总计结果效果图,高手,大闯关,Step 3:SUMIFS,函数,使用,SUMIFS,函数，求符合条件，职称是副教授的男教师基础工资总和，如图,2-4-12,所示。,图,2-4-12,使用,SUMIFS,函数求男教师职称是副教授的基础工资总计,最终完成如图,2-4-1,所示的结果效果图。,图,2-4-1,使用,SUMIFS,函数求男教师职称是副教授的基础工资总计结果效果图,完成如图,2-4-13,，图,2-4-14,所示的新天地超市二季度销售统计表和科目总分统计表，源文件,/,资源文件“,/2-4,资源,/,新天地超市二季度销售统计表,xlsx,”和“科目总分统计表,.xlsx,”。,高手,勇拓展,练习案例时，当判定对象为字符串时，可以使用“,*”,（星号）或者“？”,(,问号,),等通配符来指定“模糊条件”。,小贴士,!,图,2-4-13,销售统计表制作效果图,图,2-4-14,科目总分统计表制作效果图,Excel,高手速成,查找数据使用,Excel,电子表格,Office,办公软件应用高级教程,由制作员工信息表案例入手，学习在,Excel,工作表中查找函数,-LOOKUP,函数、,VLOOKUP,函数以及,COLUMN,函数的使用方法，掌握在,Excel,表格中查找数据的技能。,学习目标,Excel,电子表格,Office,办公软件应用高级教程,高手抢鲜看,高手,加油站,高手,大闯关,高手,勇拓展,在实际使用,Excel,工作时，有时需要对数据表中的数据进行查找，,Excel2016,提供了强大的查找函数,LOOKUP,函数、,VLOOKUP,函数和,COLUMN,函数，下面小编就与大家一起来用以上函数完成员工信息表的制作,。,图,2-5-1,员工信息表制作,效果图,高手抢鲜看,高手,加油站,查找与引用函数用于在数据表中查找特定数值，或者查找某一单元格的引用时使用的函数。常用的查找与引用函数包括,LOOKUP,函数、,VLOOKUP,函数和,COLUMN,函数等。,1.LOOKUP,函数,LOOKUP,函数的功能是从向量或数组中查找符合条件的数值。,该函数有两种语法形式，即向量和数组。向量形式是指从一行或一列的区域内查找符合条件的数值。向量形式的,LOOKUP,函数按照在单行区域或单列区域查找的数值，返回第二个单行区域或单列区域中相同位置的数值。数组形式是指在数组的首行或首列中查找符合条件的数值然后返回数组的尾行或尾列中相同位置的数值。本节介绍向量形式的,LOOKUP,函数语法格式。,其语法格式如下：,LOOKUP,（,lookup_value,lookup_vector,result_vector,）,Lookup_value:,在单行或单列区域内要查找的值，可以是数字、文本、逻辑值或者包含名称的数值或引用。,Lookup_vector,：指定的单行或单列的查找区域。其数值必须升序排列，文本不区分大小写。,Result_vector,：指定的函数返回值的单元格区域。其大小必须与,lookup_vector,相同，如果,lookup_value,小于,lookup_vector,的最小值，则函数,LOOKUP,返回错误值“,#N/A”,。,高手,加油站,例如在,“,员工销售表”中，使用,lookup,函数查找每位员工对应的销售量，如下图,2-5-2,所示。,图,2-5-2,员工销售表,（,1,）首先，选中,H3,单元格，使用数据验证中的序列方式输入姓名，如下图,2-5-3,所示。,图,2-5-3,使用数据验证输入员工姓名,（,2,）使用,LOOKUP,函数的向量形式，进行查找。选中,I3,单元格输入公式如下：“,=LOOKUP(H3,B3:B8,E3:E8),效果如图,2-5-4,所示。,图,2-5-4,使,LOOKUP,函数使用姓名查找销量效果图,高手,加油站,2.,VLOOKUP,函数,VLOOKUP,函数的功能是进行列查找，并返回当前行中指定的列的数值。其函数语法格式如下：,VLOOKUP,（,lookup_value,table_array,col_index_num,range_lookup,）,Lookup_value,：需要在表格数组第一列中查找的数值。,Lookup_value,可以为数值或引用数值。若,lookup_value,小于,table_array,第一列中的最小值，,VLOOKUP,函数返回错误值“,#N/A”,。,Table_array,：指定的查找范围。使用对区域或区域名称的引用。,Table_array,第一列中的值是由,lookup_value,搜索到的值。这些值可以是文本、数字和逻辑值。,Col_index_num,：指,table_array,中返回的匹配值的列序号。,Col_index_num,为,2,时，返回,table_array,第二列中的数值，以此类推。如果,col_index_num,小于,1,，,VLOOKUP,函数返回错误值,“#VALUE”,；大于,table_array,的列数，,VLOOKUP,返回错误值“,#REF!”,。,Range_lookup;,指逻辑值，指定希望,VLOOKUP,函数查找精确的匹配值还是近似匹配值。如果参数值为,TRUE(,或为,1,或省略,),，则只寻找精确