《Excel数据处理与可视化》（第二版） 课件 韩春玲 第1部分 数据采集整；第2部分 函数与公式

第1部分 数据采集整理----1.1数据录入与基本设置1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式1．“0”数字占位符数字“0”代表占位符，如果单元格的内容大于指定占位符，则显示实际数字；如果小于占位符的数量，则用0补足。1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式2．“#”数字占位符“#”数字占位符，只显有意义的零，不显示无意义的零。小数点后数字如大于“#”的数量,则按“#”的位数四舍五入。1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式3．“?”数字占位符“?”数字占位符，在小数点两边为无意义的零添加空格，以实现按小数点对齐。1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式4．“,”千分位分隔符“,”千分位分隔符。在数字中，每隔三位数加进一个逗号，也就是千位分隔符，以免数字太多不好读取，1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式5.“@”文本占位符使用单个@，作用是引用固定文本。使用多个@，则可以重复文本，1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式6．“!”原样显示后面的符号在自定义格式中，“”、#、？等都是有特殊意义的字符，如果想在单元格中显示这些字符，需要在前符号前加“!”，，1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式7．“*”重复后面的字符重复显示*后面的字符，直到充满整个列宽。1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式8．颜色显示符代码：[红色];[蓝色];[黑色];[绿色]，显示结果为正数为红色，负数显示蓝色，零显示黑色1.1 数据录入与基本设置1.1.2设置数字以小数点对齐，使数值大小一目了然第1步：设置数据右对齐第2步：设置单元格格式，自定义单元格格式为：#.0?????，1.1 数据录入与基本设置1.1.3单元格数据换行第1种方法：自动换行。第2种方法：Alt+Enter。1.1 数据录入与基本设置1.1.4设置不能隔行隔列填写数据1．打开“数据验证”2．设置验证条件3、设置出错警告1.1 数据录入与基本设置1.1.5设置倾斜列标签单击“开始”菜单的“对齐方式”功能区中的“方向”按钮，可以沿对角或垂直方向旋转文字，这是标记窄列的好方式，1.1 数据录入与基本设置1.1.6单元格数据太多，加滚动条显示第1步：插入文本控件第3步：关闭设计模式，输入文字第2步：设置文本框属性1.1 数据录入与基本设置1.1.7冻结拆分窗格，轻松查看行列数据1．冻结行2．冻结列3．冻结行和列1.1 数据录入与基本设置1.1.8轻松绘制单斜线、双斜线表头1．单斜线表头。2．双斜线表头。1.1 数据录入与基本设置1.1.9如何让数字以“万”为计数单位来显示第1种方法：设置单元格格式第2种方法：选择性粘贴除以10000。1.1 数据录入与基本设置1.1.10设置仅能修改部分单元格数据第1步：解除“锁定”。第2步：设置保护1.1 数据录入与基本设置1.1.11隐藏工作表第1步：打开VBA对话框。第2步：设置要隐藏的工作表属性。第1部分 数据采集整理----1.2合并单元格1.2 合并单元格1.2.1批量合并单元格第1步：“数据”→“分类汇总”第2步：“定位”→“定位条件”第3步：“开始”→“合并后居中”1.2 合并单元格1.2.2批量拆分合并单元格第1步：取消“合并后居中”第2步：“定位”→“空值”第3步：输入“=A2”,按Ctrl+Enter组合键执行计算1.2 合并单元格1.2.3合并单元格填充序号选中整个合并单元格区域，输入公式“=MAX($A$1:A1)+1”，按“Ctrl+Enter”组合键执行计算。1.2 合并单元格1.2.4合并单元格计算1.合并单元格求和3.合并单元格平均值2.合并单元格计数1.2 合并单元格1.2.5合并单元格筛选1. 选择合并单元格，复制到另一列，备用。3.“定位”→“定位条件”→“空值”2.“开始”→“合并单元格”→“取消合并单元格”4.输入公式=A2，按“Ctrl+Enter”键结束5.备用的合并单元格区域，单击“格式刷”1.2 合并单元格1.2.6合并单元格数据查询公式：=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,0),,3),2,)第1部分 数据采集整理----1.3数据规范1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入1．规范性别输入1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入2．限定输入内容1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入3．限定数字范围1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入4．限定文本长度1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入5．限制输入重复信息1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入6．限定身份证号码1.3 数据规范1.3.2设置只能输入规范的日期第1步：设置日期格式。第2步：数据验证规范日期区间。1.3 数据规范1.3.3巧用数据有效性规范报到时间1.公式“=NOW()”2.“数据”→“数据验证”→“序列”，“来源”设为“=$E$2”，3.“报到时间”一列单元格格式4.隐藏E列。1.3 数据规范1.3.4数量单位，怎么计算平均值{=ROUND(AVERAGE(--SUBSTITUTE(B2:B10,"分",)),2)}，1.3 数据规范1.3.5一键添加“能计算”的数量单位“设置单元格格式”对话框，在“分类”中选择“自定义”，直接在“类型”文本框的“G/通用格式”后输入数量单位，1.3 数据规范1.3.6多级联动菜单，规范数据输入第1步：分级数据整理。第2步：自定义名称。第3步：建立各级菜单。1.3 数据规范1.3.7”数值”不能计算，怎么办第1种方法：选择性粘贴。第2种方法：数据分列。第3种方法：VALUE函数。1.3 数据规范1.3.8规范全角半角数据公式“=ASC(D3)”公式“=WIDECHAR(D3)”1.3 数据规范1.3.9数字与文本分离的三种方法第1种方法：函数法。第2种方法：分列。第3种方法：快速填充。1.3 数据规范1.3.10用LOOKUP和FIND函数规范标准名称第1步：建立关键字与标准名称对应表。第2步：函数实现公式“=LOOKUP(1,0/FIND($D$2:$D$7,A2),$E$2:$E$7)”1.3 数据规范1.3.11给同一单元格中姓名和电话号码中间加分隔符号公式“=REPLACEB(A2,SEARCHB("?",A2),0,"：")第1部分 数据采集整理----1.4行列设置1.4 行列设置1.4.1快速删除空白行1．纯空白行，无其它空白单元格2．既有空白行，又有空白单元格1.4 行列设置1.4.2不管插入行还是删除行，序号都可自动填写方法1：ROW函数。方法2：ROW函数+表格。方法3：SUBTOTAL函数。1.4 行列设置1.4.3数据的转置与跳过单元格复制１．选择性粘贴，数据转置的使用。2．跳过单元格复制。1.4 行列设置1.4.4最快捷的一列变多列方式1．将数据复制到C列。2．在D1单元格中输入公式“=C6”，按Enter键执行计算。因为从C6开始名字另起一列显示。3．公式向下填充，再向右填充，在C1：G5区域会囊括原来的一整列分布的数据。4．选中D1：G5区域，复制，再进行选择性粘贴，用数值覆盖原有的公式。5．删除C1：G5区域以外的数据。1.4 行列设置1.4.5聚光灯效果（阅读模式）改变当前行和列的颜色1．条件格式设计颜色。2．颜色随单元格改变而移动1.4 行列设置1.4.6同一部门员工合并到同一单元格1.4 行列设置1.4.7同一部门员工，由同一单元格变分行显示1．打开查询编辑器。2．姓名的分列显示3．逆透视列。4．关闭并上载第1部分 数据采集整理----1.5数据维度转换1.5 数据维度转换1.5.1为工作表建目录1．“公式”菜单中“定义名称”公式“=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())”公式“=IFERROR(HYPERLINK(目录&"!A1",MID(目录,FIND("]",目录)+1,99)),"")”2．公式1.5 数据维度转换1.5.2旁门左道创建目录第1步：选定所有工作表。第2步：输入公式。第3步：自动生成“兼容性报表”。第4步：复制目录到“目录”工作表。1.5 数据维度转换1.5.3链接到另一张表的四种姿势第1种方法：文字。1.5 数据维度转换1.5.3链接到另一张表的四种姿势第2种方法：形状。1.5 数据维度转换1.5.3链接到另一张表的四种姿势第3种方法：图标。1.5 数据维度转换1.5.3链接到另一张表的四种姿势第4种方法：ActiveX控件。1.5 数据维度转换1.5.4单击订单名称即可跳到订单详情工作表第1部分 数据采集整理----1.6数据格式转换1.6 数据格式转换1.6.1数值取整的9种方式1．INT取整。1.6 数据格式转换1.6.1数值取整的9种方式2．TRUNC取整。1.6 数据格式转换1.6.1数值取整的9种方式3．ROUND小数取整。1.6 数据格式转换1.6.1数值取整的9种方式4．ROUND整数取整。1.6 数据格式转换1.6.1数值取整的9种方式5．ROUNDUP向上舍入函数。1.6 数据格式转换1.6.1数值取整的9种方式6．ROUNDDOWN向下舍入函数。1.6 数据格式转换1.6.1数值取整的9种方式7．MROUND函数。1.6 数据格式转换1.6.1数值取整的9种方式8．CEILING函数1.6 数据格式转换1.6.1数值取整的9种方式9．FLOOR函数1.6 数据格式转换1.6.2数值的特殊舍入方式：舍入到偶数或奇数1．舍入到偶数MROUND四舍五入到偶数CEILING函数FLOOR函数EVEN函数1.6 数据格式转换1.6.2数值的特殊舍入方式：舍入到偶数或奇数2．舍入到奇数1.6 数据格式转换1.6.3NUMBERSTRING和TEXT函数：阿拉伯数字和中文数字转换1．阿拉伯数字转中文数字2．中文数字转阿拉伯数字1.6 数据格式转换1.6.4一串串长短不一的文本算式，怎么算结果第1步：选项设置。第2步：数据分列。第3步：选项设置。1.6 数据格式转换1.6.5人民币阿拉伯数字转为中文大写格式公式“=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零元整"),"零角",IF(A2^2<1,,"零")),"零分","整")”第1部分 数据采集整理----1.7数据筛选1.7 数据筛选1.7.1数值筛选基础1．筛选指定某值。1.7 数据筛选1.7.1数值筛选基础2．排除指定值筛选。1.7 数据筛选1.7.1数值筛选基础3．高于指定值筛选。1.7 数据筛选1.7.1数值筛选基础4．筛选指定范围数值。1.7 数据筛选1.7.1数值筛选基础5．筛选前几位数值。1.7 数据筛选1.7.1数值筛选基础6．高于平均值筛选。1.7 数据筛选1.7.2高级筛选高级筛选的关键点：必须按照筛选要求自己写一个条件区域。高级筛选的原数据区域第一行（列标签）一定要包含条件区域的第一行（列标签）。高级筛选条件区域，一定要注意各条件之间的关系。1.7 数据筛选1.7.3筛选符合条件人员的两种方法1.IF+COUNTIF、2.高级筛选1.7 数据筛选1.7.4不用公式的跨表查询：查询指定顾客的购买记录第1步：写条件。第2步：高级筛选。1.7 数据筛选1.7.5SUMIF+CELL使隐藏列不参与汇总第1步：建立辅助行第2步：函数实现。1.7 数据筛选1.7.6序号经过筛选后仍然不乱第1部分 数据采集整理----1.8排序和排名1.8 排序和排名1.8.1多关键字排序，多少个排序条件都可以一起来“数据”→“排序”“添加条件”1.8 排序和排名1.8.2数据也可按行排序第1步：选定除了行标题（首列）以外的所有数据，单击“数据”→“排序”第2步：选择“主要关键字”为消费记录所在“行2”，“次序”为“降序”；单击“添加条件”按钮，添加“次要关键字”，次要关键字选择消费方式所在的“行4”，根据要求选择升序或者降序，1.8 排序和排名1.8.3按自定义序列排序，谁在前谁在后由你定1.8 排序和排名1.8.4剔除0值排名次，升序降序由你来定1．剔除0值降序排。2．剔除0值升序排名。1.8 排序和排名1.8.5只给有销量的产品添加序号公式“=IF(C2=0,"",(C2<>0)*(COUNT($C$2:C2)-COUNTIF($C$2:C2,"0")))”1.8 排序和排名1.8.6RANK.EQ引用合并区域，实现多列数据排名公式“=RANK.EQ(B3,($B$3:$B$11,$E$3:$E$11,$H$3:$H$11,$K$3:$K$11))”1.8 排序和排名1.8.7SUMPRODUCT实现中式排名公式“=SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))”1.8 排序和排名1.8.8SUMPRODUCT分组排名公式公式“=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14))”1.8 排序和排名1.8.9利用公式自动生成字母序列公式“=CHAR(COLUMN(A1)+64)”公式“=CHAR(ROW(A1)+64)”公式“=CHAR(COLUMN()+96)”公式“=CHAR(ROW(B1)+96)”1.8 排序和排名1.8.10数据透视表实现排名（1）建立数据透视表。（2）添加字段到“行”（3）更改“值显示方式”第1部分 数据采集整理----1.9数据去重复1.9数据去重复1.9.1删除重复项，也可以随你所欲1．只有一列数据。2．数据有多列。1.9数据去重复1.9.2每人报名项目多少不一，计算有多少人报名公式“=SUMPRODUCT(1/COUNTIF($A$2:$A$16,$A$2:$A$16))”1.9数据去重复1.9.3COUNT+MATCH，统计两列有多少重复值公式“=COUNT(MATCH(A2:A11,B2:B11,0))”，按Ctrl+Shift+Enter组合键1.9数据去重复1.9.4利用EXACT函数设置条件格式，标记两组数据的不同在“新增格式规则”对话框中，“选择规则类型”为“使用公式确定要设置格式的单元格”，并在“为符合此公式的值设计格式”中输入公式“=OR(EXACT(A2,$B$2:$B$21))=FALSE”，第1部分 数据采集整理----1.10多工作簿、工作表合并、汇总与拆分1.10 多工作簿、工作表合并、汇总与拆分1.10.1数据查询功能实现多工作表合并第1步：打开查询编辑器。第2步：追加查询。第3步：关闭并上载1.10 多工作簿、工作表合并、汇总与拆分1.10.2PowerQuery编辑器实现多工作簿合并与刷新第1步：将需要合并的工作簿存入一个文件夹中。第2步：新建“合并”工作簿。新建的工作簿不要和需要合并的工作簿放在同一文件夹。第3步：新建查询。第4步：删除多余数据列。第5步：添加列选项。第6步：数据加工。第7步：数据加载到表格。第8步：数据刷新。1.10 多工作簿、工作表合并、汇总与拆分1.10.3多工作表数据汇总第1种情况：所有表“姓名”排序一致。公式“=SUM('*'!C2)”1.10 多工作簿、工作表合并、汇总与拆分1.10.3多工作表数据汇总第2种情况：所有表“姓名”排序不一致。公式“=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$12)&"月!$B$2:$B$37"),汇总!B2,INDIRECT(ROW($1:$12)&"月!$c$2:$c$37")))”1.10 多工作簿、工作表合并、汇总与拆分1.10.4结构一致的多个工作表，合并计算是最好的汇总方法1.10 多工作簿、工作表合并、汇总与拆分1.10.5多个工作表，不用合并，直接查询新建“查询”第1部分 数据采集整理----1.11图片处理1.11 图片处理1.11.1批量导入文件名第1步：生成包含图片名称的文本文档。第2步：生成Excel文件。1.11 图片处理1.11.2工作表中批量插入照片第1步：公式输入第2步：粘贴到文本文档。公式“="<table><imgsrc=""E:\图片\"&B2&".jpg""width=""100""height=""100"">"”第3步：粘贴Unicode文本。1.11 图片处理1.11.3批注也可插入图片第1步：插入批注第2步：设置批注格式第3步：设置填充效果第4步：填充图片效果1.11 图片处理1.11.3批注也可插入图片第1步：将图片整理到文件夹第2步：复制文件夹路径第3步：输入公式1.11 图片处理1.11.5一次为成百上千幅图片重命名第1步：显示已知文件类型的扩展名第2步：新建Excel文件，输入公式第3步：建立记事本文档第4步：记事本文档“重命名”第5步：双击运行批处理文档1.11 图片处理1.11.6利用Excel照相机自动匹配图片第1步：打开“照相机”功能第2步：给图片拍照第3步：定义名称第4步：输入名称第2部分 函数与公式----2.1公式综述2.1 公式综述2.1.1必须知道的公式基础知识1、运算符。2.1 公式综述2.1.1必须知道的公式基础知识2、运算符优先级（1）如果公式中包含了相同优先级的运算符，Excel将从左到右进行计算。（2）如果公式中有不同级别的混合运算，运算符的顺序从高到低依次为：引用运算符（:）、负号（如-l）、%（百分比）、^（乘方）、*和/（乘和除）、+和-（加和减）、&（连接符）、比较运算符。2.1 公式综述2.1.1必须知道的公式基础知识3、相对引用与绝对引用。（1）概念：A1：相对引用。$A$1：绝对引用。$A1：列绝对引用，行相对引用。A$1：行绝对引用，列相对引用。（2）用途:行前添加$，复制公式时，行数不发生变化;列前添加$，复制公式时，列数不发生变化。（3）F4键→引用方式之间切换的快捷键：按一次：绝对引用。按两次：对行绝对引用、对列相对引用。按三次：对行相对引用、对列绝对引用。按四次：相对引用。2.1 公式综述2.1.1必须知道的公式基础知识4、公式排查错误。2.1 公式综述2.1.1必须知道的公式基础知识5、公式填充。2.1 公式综述2.1.1必须知道的公式基础知识6、公式批量填充。2.1 公式综述2.1.1必须知道的公式基础知识7、公式转换为数值。2.1 公式综述2.1.1必须知道的公式基础知识8、公式显示。2.1 公式综述2.1.1必须知道的公式基础知识9、显示另一单元格公式。2.1 公式综述2.1.1必须知道的公式基础知识10、隐藏公式。2.1 公式综述2.1.1必须知道的公式基础知识11、保护公式。2.1 公式综述2.1.1必须知道的公式基础知识12、显示公式部分计算结果。2.1 公式综述2.1.1必须知道的公式基础知识13、中断公式编辑。2.1 公式综述2.1.2公式中常出现的错误代码及修正方法错误值含义解决办法＃＃＃＃1、单元格数据太长。2、单元格公式所产生的结果太大，单元格中无法完整显示。3、日期和时间格式的单元格相减，出现了负值。1、增加列宽。2、如果是由日期或时间相减产生了负值引起的，可以改变单元格的格式为文本，但结果只能是负时间量。＃DIV/0!1、除数为0。2、公式中除数使用了空单元格或是包含零值单元格的单元格引用。1、修改单元格引用。2、在用作除数的单元格中输入不为零的值。＃VALUE!1、数值运算时引用文本型的数据。2、使用了不正确的参数或运算符。3、当执行自动更正公式功能时不能更正公式。1、更正相关的数据类型或参数类型。2、提供正确的参数；＃REF!删除了被公式引用的单元格范围。恢复被引用的单元格范围，或是重新设定引用范围。＃N/A查找或引用函数中找不到匹配的值检查被查找的值，使其存在于查找的数据区域＃NAME?在公式中使用了Excel所不能识别的文本，比如：1、输错了函数名称。2、使用了已删除的区域或名称。3、引用的文本没有加英文双引号。1、改正函数名称。2、修改引用的区域或名称。3、引用的文本加英文双引号。＃NUM!1、函数参数无效。2、公式的结果太大或太小，无法在工作表中表示。1、确认函数中使用的参数类型正确。2、修改公式，找到错诶原因并更正。＃NULL！1、使用了不正确的区域运算符。2、引用的单元格区域的交集为空。1、改正区域运算符使之正确。2、更改引用使之相交。2.1 公式综述2.1.3使用“追踪错误”对公式进行检查2.1 公式综述2.1.4将公式保护起来第1步：所有单元格去除“锁定”。第2步：保护和隐藏“公式”单元格。第3步：设置保护工作表2.1 公式综述2.1.5数组公式——基础知识1．数组概念。2．数组的维度。3．数组公式。2.1 公式综述2.1.6数组公式——应用初步1、行列数相同的数组运算。2．数组与单一数据运算。3．单列数组与单行数组运算。4．单行或单列数组与多行列二维数组运算。5．行列相等的二维数组运算。2.1 公式综述2.1.7数组公式——典型应用1、“绕过”乘积直接求和。2.1 公式综述2.1.7数组公式——典型应用2．计算连续数值和、平均值。2.1 公式综述2.1.7数组公式——典型应用3．计算不同产品种类数。2.1 公式综述2.1.7数组公式——典型应用4．多条件运算。2.1 公式综述2.1.7数组公式——典型应用5．构建新数组运算。第2部分 函数与公式----2.2统计函数2.2 统计函数2.2.1MODE.MULT函数统计出现最多的数字（一）公式“=MODE.MULT($A$2:$G$12)”,2.2 统计函数2.2.2MODE.MULT函数统计出现最多的数字（二）公式“=IFERROR(INDEX(MODE.MULT($A$1:$E$10),ROW(A1)),"")”2.2 统计函数2.2.3COUNTIF函数给众多班级中相同班级学生编号公式“=COUNTIF($B$2:B2,B2)”2.2 统计函数2.2.4五个常用的“IFS”结尾的多条件统计函数2.2 统计函数2.2.5SUM+COUNTIF函数统计不重复值的个数公式“=SUM(1/COUNTIF(B2:B16,B2:B16))”，按Ctrl+Shift+Enter组合键执行计算2.2 统计函数2.2.6FREQUENCY函数分段计数公式“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”，按Ctrl+Shift+Enter组合键执行计算2.2 统计函数2.2.7COUNTIFS函数统计满足多个条件的单元格数量COUNTIFS函数统计满足多个条件的单元格数量2.2 统计函数2.2.8TRIMMEAN函数去掉最高分和最低分求平均值=TRIMMEAN（B3:B8,2/9）=TRIMMEAN(F3:F8,4/9)第2部分 函数与公式----2.3文本函数2.3 文本函数2.3.1MID与FIND函数结合提取括号内数据公式“=MID(A2,FIND("（",A2)+1,FIND("）",A2)-1-FIND("（",A2))”2.3 文本函数2.3.2TEXT——超级好用的文本函数功能：TEXT函数可通过格式代码对数字应用格式，从而更改数字的显示方式。语法：TEXT(Value,Format_text)。参数：Value：数值，或是计算结果为数字值的公式，也或对包含数字值的单元格的引用。Format_text：文本形式的数字格式。注意：text返回的一律都是文本形式的数据。如果需要计算，可以先将文本转换为数值，然后再计算。文本型数值遇到四则运算会自动转为数值。但文本会不参与sum之类的函数运算。2.3 文本函数2.3.2TEXT——超级好用的文本函数1．格式日期。代码含义m将月显示为不带前导零的数字。mm根据需要将月显示为带前导零的数字。mmm将月显示为缩写形式（Jan到Dec）。mmmm将月显示为完整名称（January到December）。d将日显示为不带前导零的数字。dd根据需要将日显示为带前导零的数字。ddd将日显示为缩写形式（Sun到Sat）。dddd将日显示为完整名称（Sunday到Saturday）。yy将年显示为两位数字。yyyy将年显示为四位数字。2.3 文本函数2.3.2TEXT——超级好用的文本函数2．格式时间。代码含义h将小时显示为不带前导零的数字。[h]以小时为单位显示经过时间。如果使用了公式，该公式返回小时数超过24时间，请使用类似于[h]:mm:ss的数字格式。hh根据需要将小时显示为带前导零的数字。如果格式含有AM或PM，则基于12小时制显示小时；否则，基于24小时制显示小时。m将分钟显示为不带前导零的数字。注释m或mm代码必须紧跟在h或hh代码之后或紧跟在ss代码之前；否则，Excel会显示月份而不是分钟。[m]以分钟为单位显示经过时间。如果所用的公式返回的分钟数超过60，请使用类似于[mm]:ss的数字格式。mm根据需要将分钟显示为带前导零的数字。注释m或mm代码必须紧跟在h或hh代码之后或紧跟在ss代码之前；否则，Excel会显示月份而不是分钟。s将秒显示为不带前导零的数字。[s]以秒为单位显示经过时间。如果所用的公式返回的秒数超过60，请使用类似于[ss]的数字格式。ss根据需要将秒显示为带前导零的数字。如果要显示秒的小数部分，请使用类似于h:mm:ss.00的数字格式。AM/PM、am/pm、A/P、a/p基于12小时制显示小时。时间介于午夜和中午之间时，Excel会使用AM、am、A或a表示时间；时间介于中午和午夜之间时，Excel会使用PM、pm、P或p表示时间。2.3 文本函数2.3.2TEXT——超级好用的文本函数3．千分位分隔符。代码含义"#,###"只保留整数"#,###.00"保留两位小数"#,"显示为1,000的整倍数"#,###.0,"显示为1,000的整倍数，且保留一位小数"0.0,,"显示为1,000,000的整倍数，且保留一位小数2.3 文本函数2.3.2TEXT——超级好用的文本函数4．格式数字、货币。代码含义"0.00"只保留整数"#,##0"千分位分隔符，只保留整数"#,##0.00"千分位分隔符，保留整数两位小数"$#,##0"只保留整数"$#,##0.00"保留两位小数"$#,##0.00_);($#,##0.00)"两位小数，负数"$*#,##0"只保留整数，$与数字间一个空字符"$*#,##0.00"两位小数，$与数字间一个空字符2.3 文本函数2.3.2TEXT——超级好用的文本函数5．加0前导符补充位数。6．百分比7．特殊格式。2.3 文本函数2.3.2TEXT——超级好用的文本函数8．条件区段判断。（1）四个条件区段。（2）三个条件区段。（3）两个条件区段。2.3 文本函数2.3.2TEXT——超级好用的文本函数9、自定义条件区段。（1）四个自定义条件区段。（2）三个自定义条件区段。（3）两个自定义条件区段。2.3 文本函数2.3.3&——文本连接符的使用1．基本用法。2．合并后换行。2.3 文本函数2.3.3&——文本连接符的使用3．合并带格式的内容。4．合并列实现多条件查找。2.3 文本函数2.3.4CONCATENATE文本连接函数（1）基本用法。（2）合并后换行。（3）合并带格式的内容。2.3 文本函数2.3.5CONCAT文本连接函数（1）合并区域（2）加分隔符合并区域。（3）条件筛选区域合并。2.3 文本函数2.3.6TEXTJOIN文本连接函数公式“=TEXTJOIN("、",1,IF($A$2:$A$15=D2,$B$2:$B$15,""))”,按Ctrl+Shift+Enter组合键执行计算2.3 文本函数2.3.6TEXTJOIN文本连接函数同行合并公式“=TEXTJOIN("、",1,A1:F1)”行列区域合并公式：“=TEXTJOIN("",1,A1:L4)”2.3 文本函数2.3.7CLEAN函数清除非打印字符CLEAN函数可以快速去除各种非打印字符2.3 文本函数2.3.8SUBSTITUTE文本替换函数的使用公式“=SUBSTITUTE(C2,MID(C2,11,4),"****")”SUBSTITUTE函数四个特点举例2.3 文本函数2.3.8SUBSTITUTE文本替换函数的使用例1：统一替换部分字符。例2：计算字符串内特定字符的个数。例3：带单位的数值计算。例4：同一单元格中最大值。2.3 文本函数2.3.9REPLACE函数——隐藏身份证号码部分数字公式“=REPLACE(C3,7,8,"********")第2部分 函数与公式----2.4时间与日期函数2.4 时间与日期函数2.4.1根据身份证计算退休时间公式“=EDATE(DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),55*12+MOD(MID(A2,17,1),2)*5*12)”2.4 时间与日期函数2.4.2根据续费月数，计算到期日公式“=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))”2.4 时间与日期函数2.4.3DATEDIF函数计算精确到年月天的账龄公式“=DATEDIF(B2,C2,"Y")&"年"&DATEDIF(B2,C2,"YM")&"个月"&DATEDIF(B2,C2,"MD")&"天"”2.4 时间与日期函数2.4.3DATEDIF函数计算精确到年月天的账龄DATEDIF函数应用：2.4 时间与日期函数2.4.4WORKDAY函数计算几个工作日之后的日期第1步：将每个金额范围的最低值与需交款工作日写入E3:F7区域。第2步：在C2单元格中输入公式“=WORKDAY(B2,VLOOKUP(A2,$E$3:$F$7,2))”，按Enter键执行计算，再将公式向下填充，即可得所有金额交款日期。2.4 时间与日期函数2.4.5EOMONTH函数取某月最后一天公式“=B2/DAY(EOMONTH(A2,0))”EOMONTH函数功能：返回某个月份最后一天语法：EOMONTH(start_date,months)中文语法：EOMONTH(日期,月份数)2.4 时间与日期函数2.4.6NETWORKDAYS.INTL函数，工作日的花样算法怎么算两个日期之间的工作日？2.4 时间与日期函数2.4.6NETWORKDAYS.INTL函数，工作日的花样算法Weekend四种应用第2部分 函数与公式----2.5数学函数2.5 数学函数2.5.1SUMIF函数应用——单条件、多条件、模糊条件求和2.5 数学函数2.5.2SUMIF函数应用——非空条件、排除错误值、日期区间求和2.5 数学函数2.5.3SUMIF函数应用——隔列求和、查找引用公式“=SUMIF($B$2:$G$2,H$2,$B3:$G3)”2.5 数学函数2.5.3SUMIF函数应用——隔列求和、查找引用1．SUNIF还可以实现查找与引用。2．多列区域查找引用。2.5 数学函数2.5.4SUMIFS多字段多条件求和2.5 数学函数2.5.5SUMPRODUCT用法解析1．基本用法。公式“=SUMPRODUCT(B2:B9,C2:C9)”2.5 数学函数2.5.5SUMPRODUCT用法解析2．单条件求和。公式“=SUMPRODUCT((B2:B11="女")*C2:C11)”2.5 数学函数2.5.5SUMPRODUCT用法解析3．多条件求和。公式“=SUMPRODUCT((B2:B11="女")*(C2:C11>15),C2:C11)”2.5 数学函数2.5.5SUMPRODUCT用法解析4．模糊条件求和。公式“=SUMPRODUCT(ISNUMBER(FIND("销售",A2:A11))*(C2:C11="女"),D2:D11)”2.5 数学函数2.5.5SUMPRODUCT用法解析5．单条件计数。公式“=SUMPRODUCT(N(B2:B11="女"))”2.5 数学函数2.5.5SUMPRODUCT用法解析6．多条件计数。公式“=SUMPRODUCT((B2:B11="女")*(C2:C11>15))”2.5 数学函数2.5.5SUMPRODUCT用法解析7．模糊条件计数。公式“=SUMPRODUCT(ISNUMBER(FIND("销售",A2:A11))*(C2:C11="女"))”2.5 数学函数2.5.5SUMPRODUCT用法解析8．按月份统计数据。公式“=SUMPRODUCT((MONTH($A$2:$A$13)=D2)*($B$2:$B$13))”,2.5 数学函数2.5.5SUMPRODUCT用法解析9．跨列统计。公式“=SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3)”2.5 数学函数2.5.5SUMPRODUCT用法解析10．多权重统计。公式“=SUMPRODUCT(B$2:D$2,B3:D3)”2.5 数学函数2.5.5SUMPRODUCT用法解析11．二维区域统计。公式“=SUMPRODUCT(($B$2:$B$13=$E2)*($A$2:$A$13=F$1)*$C$2:$C$13)”2.5 数学函数2.5.5SUMPRODUCT用法解析12．不间断排名。公式“=SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))”2.5 数学函数2.5.6SUMPRODUCT函数注意事项：乘号与逗号有区别2.5 数学函数2.5.7SUBTOTAL函数实现忽略隐藏行统计公式“=SUBTOTAL(109,B3:B14)”Function_numFunction_num函数函数解释（包含隐藏值）（忽略隐藏值）1101AVERAGE平均值2102COUNT计数（对数值计数）3103COUNTA计数（对文本计数）4104MAX最大值5105MIN最小值6106PRODUCT计算乘积7107STDEV给定样本标准偏差8108STDEVP样本总体标准偏差9109SUM和10110VAR基于给定样本的方差2.5 数学函数2.5.8AGGREGATE——忽略错误值计算的万能函数2.5 数学函数2.5.9ROUND()函数对数据四舍五入公式“=ROUND(A2,-2)”2.5 数学函数2.5.10QUOTIENT与TRUNC函数——截去小数，保留整数公式“=QUOTIENT(C2,B2)”公式“=TRUNC(C2/B2,0)”第2部分 函数与公式----2.6查找与引用函数2.6 查找与引用函数2.6.1VLOOKUP函数应用之基础——基本查找公式“=VLOOKUP(F3,$B$2:$D$15,3,0)”2.6 查找与引用函数2.6.2VLOOKUP函数应用之小成——多行多列查找公式“=VLOOKUP($B18,$C$2:$G$15,COLUMN(B1),0)”2.6 查找与引用函数2.6.3VLOOKUP函数应用之提升——区间查找、等级评定、模糊查找1、区间查找公式“=VLOOKUP(B2,$E$3:$F$6,2)”2.6 查找与引用函数2.6.3VLOOKUP函数应用之提升——区间查找、等级评定、模糊查找2、等级评定公式“=VLOOKUP(B2,{0,”不合格“;60,”合格“;70,”良好“;85,”优秀“},2)”2.6 查找与引用函数2.6.3VLOOKUP函数应用之提升——区间查找、等级评定、模糊查找3、模糊查找公式“=VLOOKUP("G"&"*",A1:B8,2,0)”2.6 查找与引用函数2.6.4VLOOKUP函数应用之进阶——多条件查找、逆向查找1、多条件查找公式“=VLOOKUP(E2&F2,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)”2.6 查找与引用函数2.6.4VLOOKUP函数应用之进阶——多条件查找、逆向查找2、逆向查找公式“=VLOOKUP(E2&F2,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)”2.6 查找与引用函数2.6.4VLOOKUP函数应用之进阶——多条件查找、逆向查找2、逆向查找公式“=VLOOKUP(D2,CHOOSE({1,2},B1:B10,A1:A10),2,0)”2.6 查找与引用函数2.6.5VLOOKUP函数应用之高级篇——一对多查找公式“=VLOOKUP($B$12&ROW(B1),IF({1,0},$B$2:$B$9&COUNTIF(INDIRECT("b2:b"&ROW($2:$9)),$B$12),$C$2:$C$9),2,0)”，按Ctrl+Shift+Enter组合键执行计算2.6 查找与引用函数2.6.6HLOOKUP行查找函数公式“=HLOOKUP(B2,$F$2:$I$3,2)”2.6 查找与引用函数2.6.7LOOKUP查询函数十种用法公式“=LOOKUP(1,0/(C2:C12=G2),E2:E12)”第1种用法：逆向查找。2.6 查找与引用函数2.6.7LOOKUP查询函数十种用法公式“=LOOKUP(1,0/(C2:C12=G2),E2:E12)”第2种用法：