运营数据分析 课件 Excell 公式和函数入门

本任务内容提要一、认识公式及公式中的运算符二、单元格引用三、使用公式四、认识函数及常用函数类型五、使用函数26二月202426二月2024如何求总分？？？???？？26二月2024这个叫做设置公式26二月2024回车结果显现26二月2024下拉填充柄26二月2024一、公式的组成介绍1、设置公式，必须先输入“=”2、公式由运算符和运算数据组成。、运算符包括4类：

算术运算符、比较运算符、文本运算符、引用运算符；、运算数据包括：常量、单元格引用和函数等。26二月202426二月2024=B2+C2+12+sum(a1:a7)运算数据“单元格引用”运算符26二月2024四类：

1、算术运算符2、比较运算符3、文本运算符4、引用运算符公式运算符号介绍

1、算术运算符有6个，完成基本的数学运算26二月20242、比较运算符有6个，比较两个值，并得出一个逻辑值，即“TRUE（真）”或“FALSE（假）”。26二月202426二月2024比较运算符练习1、在表格中输入4个不等式，比如：“=C2>E2”然后回车，查看显示结果2、与算术运算符显示结果比较差异3、文本运算符：“＆”可将两个或多个单元格的文本值串起来，产生一个连续的文本值。练习：单元格A1输入祝你单元格B1输入快乐！单元格C1输入=A1&B1最后出现“祝你快乐！“

注意：“”英文双引号用在公式中，表示引号中内容为文本格式。26二月20244、引用运算符：有3个，它们的作用是将单元格区域进行合并计算，在单元格数据引用中涉及。26二月2024二、单元格数据的引用也称单元格引用，通过标识单元格地址或单元格区域，指明公式中所使用运算数据的位置来源。

26二月202426二月2024单元格引用范围举例26二月2024单元格引用范围的选取方式1、手动输入：2、用鼠标拖取：26二月2024单元格引用范围的选取练习1、手动输入：比如1）取9日手机的销量数据？2）取A手机的销售数量2、用鼠标拖取：比如1)求9日手机的销售数量2)求A手机的销售数量3求A手机和B手机的销售数量4）所有手机的销售数量以一个简单函数“SUM”进行练习跨表数据的引用

引用不同工作表间的单元格在同一工作簿中，不同工作表中的单元格可以相互引用，它的表示方法为：“工作表名称!单元格或单元格区域地址”。如：Sheet2!F8:F16引用不同工作簿中的单元格在当前工作表中引用不同工作簿中的单元格的表示方法为：[工作簿名称.xlsx]工作表名称！单元格（或单元格区域）地址如：[销售清单.xlsx]Sheet2!F8:F1626二月2024相对引用

如：

B1，或B1：D1。绝对引用

如：

$B$1，或$B$1：$D$1混合引用

如：A$1，

或$A1单元格引用类别26二月20241、相对引用：直接用列标和行号表示单元格—如：B1公式所在单元格的位置改变，行列引用也随之改变。2、绝对引用：在列标和行号的前面都加上“$”符号，如$B$1不论公式复制或移动到什么位置，引用单元格地址的行和列都不会改变。单元格引用分类26二月20243、混合引用：引用中既包含绝对引用又包含相对引用，如：A$1或$A1$A1表示“列变，行不变---只取某列数据”如A$1表示“列不变，行变---只取某行据”。单元格引用分类26二月2024在这里只要输入一个公式，就可以实现一片数据的填充单元格引用(重点练习)在9个单元格中分别输入上面9个数据，然后隔二行：1、在某空白单元格输入“=A1”，然后向右向下拖动，填满9个格子，观察变化2、输入=$A$1，向右向下拖动，填满9个格子，观察变化3、输入=$A1，向右向下拖动，填满9个格子，观察变化4、输入=A$1，向右向下拖动，填满9个格子，观察变化26二月202426二月2024深刻理解4种不同引用数据的变化26二月2024练习后的总结：1、只要设置一个公式，且输入任何一种单元格引用方式，通过拖拉填充，都可以导出一片数据。2、不同的引用方式，导出的数据差异很大：可以是移步换景式的一片数据；也可以是不断重复一个数据；还可以是不断重复一行数据；更可以是连续重复一列数据。26二月2024学以致用逆向思考26二月2024公式公式公式公式每输完一个公式，然后向下向右拉满10个单元格，观察显示结果，在做下一个。1、连续引用上面9个数据2、重复引用C3这个数据3、重复引用第2行数据4、重复引用第三列数据26二月202426二月2024课堂练习126二月2024课堂练习1继续思考，如何求出每个序号与1序号销售数据的差异？26二月2024？？？？26二月202426二月202426二月2024课堂练习226二月2024课堂练习2=C2*$B226二月2024小

结1、变动引用某个（块）数据：相对引用2、固定引用某个（块）数据：绝对引用3、固定引用某（几）列数据：混合引用—列加锁4、固定引用某（几）行数据：混合引用—行加锁26二月2024单元格矩形形区域数据引用再介绍：1、工作表中，由横线分隔出的区域叫行，竖线分隔出的叫列，2、行列相互交叉的格子，叫单元格。3、矩形区域由多个单元格构成的集合。矩形区域的表达方式：通过矩形左上角和右下角单元格地址，以冒号相连进行标识矩形区域的具体样式：26二月2024单元格矩形形区域数据引用介绍：矩形区域的表达方式：通过矩形左上角和右下角单元格地址，以冒号相连进行标识矩形区域的具体样式：a1:a5$a$1:$a$5$a$1:a5$a1:a5a$1:a5取数据观察与练习

1、可直接在设置公式的单元格中输入引用单元格地址，也可在编辑栏中输入，

2、运算数据可直接输入引用的单元格地址，也可点击要引用的单元格。（这二点分别练习，重点掌握）三、几种创建公式的方法26二月202426二月2024如何求总分？？？???？？26二月2024查看地址栏的单元格引用数据26二月2024填充柄下拉后，注意地址栏的变化26二月2024

改变“总分”公式的设置

在F2输入“=$B$2+$C$2+$D$2+$E$2”大家注意填充柄下拉后结果的变化！！！26二月202426二月2024

改变“总分”公式的设置

在F2输入“=B$2+C$2+D$2+E$2”大家注意填充柄下拉后结果的变化！！！26二月202426二月2024

改变“总分”公式的设置

在F2输入“=$B2+$C2+$D2+$E2”大家注意填充柄下拉后结果的变化！！！26二月202426二月2024

移动和复制公式与移动、复制单元格内容的操作方法一样。但移动公式时，公式内的单元格引用不会更改，而复制公式时，单元格引用会根据所用引用类型而变化，即系统会自动改变公式中引用的单元格地址。

利用填充柄复制公式26二月202426二月2024走进函数26二月2024请跟我做在空白单元格输入：154587987@QQ.COM46545887@QQ.COM587987@QQ.COM1587987@QQ.COM187987@QQ.COM136587987@QQ.COM26二月2024要求：假设有一百个号码,把其中的qq号提取出来！26二月2024设置公式计算总分26二月2024改变设置：公式被函数替代

四、认识函数及常用类型

一个函数通常由函数名和参数组成1、函数名表示将执行的操作（如SUM：求和）2、参数表示参与运算的数据，通常用括号进行锁定，内部有常量，变量，表达式等多个种类。SUM(F2:F8)SUMIF(C2:C8,">=90",D2:D8)26二月202426二月2024五、认识函数参数1、参数的个数2、参数的形态3、参数的摆放顺序26二月2024五、认识函数参数一个函数的参数有以下几种。1、不带参数。TODAY（）2、一个参数。SUM(F2:F8)3、固定数量的参数。FIND（“C”，A1）4、不确定数量的参数。OR(A1>3,B1>5...)5、可选参数。VLOOKUP(E1,A1:B10,2,)26二月2024五、函数参数类型三类：常量，变量（单元格引用），表达式1、常量可以是数值，也可以是文字，把文字作为参数，必须加注双引号如：COUNTIF(B1:B22,"男")2、变量通常是单元格引用。SUM(F2:F8)3、表达式作为参数表达式通常是一个公式。EXCELL先计算这个表达式，然后再使用计算结果作为参数值。例如：=SQRT((A1^2)+(A2^2) )26二月2024五、函数参数类型4.把其他函数作为参数这种情况称为“嵌套”函数。Excel先计算最深层的嵌套函数，逐渐向外扩展例如：=SIN(RADIANS(B1))5.把数组作为参数Excel也可以将数组作为参数。一个数组就是一组数值，分别使用逗号和括号进行分隔。例如：=OR(A1={1,2,3})中的A1={1,2,3}即是一个常量数组。

六、函数的输入（全部须英文状态下操作）1、首先在单元格中输入“=”，即进入公式编辑状态。2、接下来输入函数名称。3、紧跟着输一对括号4、括号内输入一个或多个参数，参数之间用逗号分隔。26二月202426二月2024用户可在单元格中手工输入函数，也可以使用函数向导输入函数。（多次练习）

手工输入一般用于参数比较单一、简单的函数，即用户能记住函数的名称、参数等，此时可直接在单元格中输入函数。

手动输入函数26二月2024

如果不能确定函数的拼写或参数，可以使用函数向导输入函数。

在工作表中选择要进行计算的单元格区域26二月202426二月2024也可以使用“公式”选项卡“函数库”组中的按钮来输入函数，方法是单击相应函数类型下方的三角按钮，从弹出的列表中选择需要插入的函数。26二月2024常用函数及其使用范例26二月2024If函数(重点）函数名IF的作用：当某条件成立时，显示什么，否则，又显示什么。函数表现形式：IF（判断条件，条件成立，条件不成立）

三个基本参数IF(a1>b1,“优秀”,“不合格”)26二月2024条件表达式If函数表现形式：1、简单形式IF(a1>b1,“优秀”,“不合格”)2、嵌套形式IF(a>b,“优秀”,IF(a>c,“良好”,“不合格"))26二月2024一、简单形式举例1、营销员业绩达到5000标注为1，否则为0C2处如何设置函数？？？。26二月2024根据题目要求，首先想到使用IF函数?????一、简单形式举例1、营销员业绩达到5000标注为1，否则为0

C2处如何设置函数？？？。26二月2024IF(a1>b1,“优秀”,“不合格")三个参数如何替换如何构造条件表达式一、简单形式举例1、营销员业绩达到5000标注为1，否则为0

C2处如何设置函数？？？。26二月2024IF(a1>b1,“优秀”,“不合格")三个参数如何替换单元格引用一、简单形式举例1、营销员业绩达到5000标注为1，否则为0

C2处如何设置函数？？？。26二月2024IF(a1>b1,“优秀”,“不合格")三个参数如何替换单元格引用B2>=5000简单形式举例例如：计算营销员业绩达到5000的人数。通过图中的IF函数，将大于等于5000的标注。26二月2024=if(B2>=5000,1,0)26二月2024科学成绩比张二高的同学请在备注里写上“我很棒”，否则，写上”加油”备注???？？复习IF函数的简单运用：

如果总分高于320分，标注优秀，否则空白，圈圈内如何设置函数？？？26二月2024？？？2种方法，第一，先计算总分，再设置函数26二月2024？？？=IF(F2>320,"优秀","")2、第二，不可以开新列，那就直接在IF里面，把计算总分的表达式写出来（又有两种写法）？？？26二月2024？？？2、第二，不可以开新列，那就直接在IF里面，把计算总分的表达式写出来（又有两种写法）？？？26二月2024？？？=IF(B2+D2+C2+E2>320,"优秀","")=IF((SUM(B2:E2)>320,"优秀","")26二月2024在下表中，当C5：C12中的数值为正时，D5：D12中用相同的正数反映，当C5：C12区域中的数值为负时，E5：E12用它们的绝对值反映。

D5,E5如何设置函数？？

？？？？？26二月2024

在D5中建立函数如下：=IF（C5＞0，C5，"0"）

在E5中建立函数如下：=IF（C5＜0，C5*（－1），"0"）

不要随意在数字上加双引号，否则误认为文本比较好的处理方式如下：不要犯下面的错误！！！IF(A>=30,“优秀”,“合格”）IF(A>=30,“优秀”，A<30，“合格”）其中，A<30，是潜伏的，不用表达出来26二月2024√？二、嵌套形式举例

IF自身多层嵌套（分类细化）26二月202426二月2024=IF(F2>320,"优秀","")1、前述：如果总分高于320分，标注优秀，否则就空白

2、如果总分高于320为优秀，高于280为良好，其他为合格。（条件细化了）26二月2024=IF(F2>320,"优秀","")=IF(F2>320,“优秀”,IF(F2>280,“良好","合格"))26二月2024注意IF的三个参数的样式保持不变1)=IF(F2>320,"优秀","")2)=IF(F2>320,"优秀",IF(F2>280,"良好","合格"))第一个IF函数的第3个参数被另一个IF函数给代替了观察他们参数的变化!!!!26二月2024注意事项1、有多少个函数，必须加载多少对括号。2、括号千万别乱了，更别少了，仔细点！！！26二月2024

26二月20241、前述：如果总分高于320分，标注优秀，否则就空白

2、如果总分高于320为优秀，高于280为良好，其他为合格。（条件细化了）3、如果总分高于320为优秀，高于280分为良好，高于240为合格，其他为不合格。（条件进一步细化）=IF(F2>320,"优秀","")=IF(F2>320,"优秀",IF(F2>280,"良好","合格"))=IF(F2>320,"优秀",IF(F2>280,"良好",IF(F2>240,"合格","不合格")))比对三个函数的参数变化！！！！26二月2024参数设置注意事项11、可以选择从小到大，或者从大到小的顺序。2、第1个IF的参数一旦设置，则

第2个IF参数的范围是扣除第一个参数范围后，剩下的范围里，再设置相关参数第3个。。。。。26二月202426二月2024IF>320IF>240IF>280优秀良好合格不合格32028024026二月2024IF<240IF<320IF<280优秀良好合格不合格320240280以下任选一种判断方式：1、从大到小2、从小到大错误的参数=IF(F2>320,"优秀",IF(F2>280,"合格","不合格"))=IF(F2>320,"优秀",IF(320>F2>280,"合格","不合格"))26二月2024√设置条件参数适可即止如果有二个选择条件，只设置一个表达式参数即可！例如IF(A>B,“优秀”,“合格”）不能写成

IF(A>B,“优秀”,A<B，“合格”）同理，如果三个条件，，设置二项

如果四个条件，，设置三项即可26二月2024？26二月202426二月202426二月2024练习：不同合格率考核扣分不同,用IF函数嵌套计算结果26二月2024合格率扣分100-90%090-80%180-70%270%以下3设置函数26二月2024IF>320IF>240IF>280优秀良好合格不合格320280240复习这张图，，画出习题的分隔区间，再列出相关函数。设置IF嵌套多少层的关键点1、直接找出条件分界点，有几个分界点，就必须有几个IF函数被设置。2、关注显示结果，显示结果的个数减1，也是IF函数被设置的个数，它与条件分界点可以互相验证！！！26二月202426二月2024IF>320IF>240IF>280优秀良好合格不合格320280240

3个分界点，4个显示结果条件表达形式可以更换如下320优秀320-280良好280-240合格240不和格26二月2024条件表达形式可以更换如下320优秀320-280良好280-240合格240不和格26二月202426二月2024IF>90%IF>70%IF>80%123490%80%70%

3个分界点，4个显示结果练习：不同合格率考核扣分不同,用IF函数嵌套计算结果26二月2024合格率扣分100-90%090-80%180-70%270%以下3设置函数26二月2024小结

前题,无论简单形式，还是嵌套形式，IF函数都是一个条件对应一种显示方式如果多个条件，共同对应一种显示方式，比如之前的“与条件”，“或条件”的出现，该如何设置？？26二月2024小结

前题，IF函数都是一个平台下从小到大，或者从大到小设置条件，且一个条件对应一种结果。如果出现跨平台的多个条件，“比如总分大于320，且数学单科大于70”；或者一个平台下，出现不同方向的条件对应一种结果，该如何设置？？26二月202432032028024070要么跨平台，出现多个条件要么一个平台，多个不同方向的条件例如：语文大于70分的同学，在备注栏标注为优秀,否则为合格26二月2024例如：语文和数学成绩都大于70分的同学，在备注栏标注为优秀,否则为合格26二月2024例如：

（1）数学成绩大于80分，小于90分的同学，在备注栏标注为优秀,否则为合格

26二月2024例如：数学成绩大于80分，小于90分的同学，在备注栏标注为优秀,否则为合格26二月2024注意:在EXCEL的条件表达式中，不可以出现“80〈X〈90”这种连串的表达式。那这种情况该如何展现？？？嵌套形式之2IF与AND函数的嵌套IF与OR函数的嵌套IF与AND\OR函数的嵌套26二月2024例如：语文和数学成绩都大于70分的同学，在备注栏标注为优秀,否则为合格26二月2024

表达“与条件”，“或条件”的函数

AND、OR介绍

1、AND表示“和”、“与”的意思，意为同时满足几个条件。2、OR表示“或”的意思，意为只要满足其中一个条件即可。这两个函数与IF嵌套，可以实现很多组合功能。26二月2024注意嵌套形势的变化

1、AND函数作为IF函数第一个条件参数来设置2、AND函数里面可以嵌套OR函数3、OR函数里面可以嵌套AND函数26二月2024例如：语文和数学成绩都大于70分的同学，在备注栏标注为优秀,否则为合格26二月2024

语文和数学成绩都大于70分的同学标注优秀

AND（B2>70,B3>70)———整体作为条件表达式

26二月2024

语文和数学成绩都大于70分的同学标注优秀

IF（AND(B2>70,B3>70),“优秀”,“合格”)

26二月20241、语文和数学成绩都大于70分的同学

2、语文或数学成绩大于70分的同学

3、语文、数学都大于70分，或者英语大于90分的同学上述条件如何表达？？？

26二月20241、语文和数学成绩都大于70分的同学

2、语文或数学成绩大于70分的同学

3、语文、数学都大于70分，或者英语大于90分的同学上述条件如何表达？？？

26二月20241、AND（B2>70,C2>70)2、OR（B2>70,C2>70)3、OR(AND（B2>70,C2>70),D2>90)例如：

（1）数学成绩大于80分，小于90分的同学，在备注栏标注为优秀,否则为空白。

（2）数学成绩大于90分，为优秀，大于80分，小于90分，为良好，小于80分，为合格。

26二月2024例如：

（1）数学成绩大于80分，小于90分的同学，在备注栏标注为优秀,否则为空白

=if(AND(a7>80,a7<90),“优秀”，“”)

（2）数学成绩大于90分，为优秀，大于80分，小于90分，为良好，小于80分，为合格。

=if（A7>90,”优秀”,IF(A7>80,”良好”,”合格”))

26二月2024要求：试统计6月、7月单件数合计达5件或6月、7月总保费达3万元的营销员奖励人数

26二月2024解题思路1、把相关条件表达出来6月、7月单件数合计达5件:E2+G2>=5总保费达3万元:

F2+H2>=300002、根据关键词“或”用OR函数串连上述条件OR（E2+G2>=5，F2+H2>=30000）3、把条件导入IF函数，完成公式设置IF(OR(E2+G2>=5,F2+H2>=30000),1,0)26二月202426二月2024IF(OR(E2+G2>=5,F2+H2>=30000),1,0)请切换到练习资料“公式的运用”练习10——1526二月2024注意嵌套形势的变化

1、AND函数作为IF函数第一个条件参数来设置2、AND函数里面可以嵌套OR函数3、OR函数里面可以嵌套AND函数26二月202414=IF(0R(AND(E2>70,F2>70),G2>70),”水平高”,”落伍啦”)15=IF(0R(AND(E2>70,F2>70),AND(G2>70,h2>70)),”超级棒”,”革命尚未成功”）26二月20242\=IF(OR(AND(J7>20,K7>20),K7>30),"好","一般")IF（AND(AND(B2>=95,C2>=95),OR(D2=“优”,E2=“优”))，500，0）1\=IF(E2<=1000,E6*0.05,IF(E6<=5000,E6*0.08,E6*0.15))注意事项：文本需加双引号，数值千万不加双引号26二月20241\=IF(E2<=1000,E6*0.05,IF(E6<=5000,E6*0.08,E6*0.15))2\=IF(OR(AND(J7>20,K7>20),(K7>30)),"好","一般")3\=IF(OR(AND(B23="男",C23<30),AND(B23="女",C23<32)),"合格","不合格")4\=IF(J23="男",60,55)+IF(OR(K23="总经理",K23="副部经理"),5,0)5\=IF(AND(OR(B39>45,C39="专科以下"),C39<>"研究生"),"取消","聘用")26二月2024五、查找引用函数一）、基础函数1、ROW2、COLUMN3、MATCH二）、VLOOKUP(核心内容）26二月20241、ROW（）函数返回指定单元格的行号2、COLUMN（）函数返回指定单元格的列号练习：1、返回当前单元格的行号2、返回当前单元格的列号26二月2024查找函数的学习比如我们查高考成绩，，，，，查个人工资，，查户口，查每个药品的价值，，，，查商品的价格，，，26二月2024Vlookup的运用1、从大量数据中直接查找出单条详细数据

例如从工资单中查找某人的工资信息又可分为精确查找和模糊查找2、制作表单一张数据不全的表单需要按照指定要求填充空白内容例如制作销售清单，填定利息或评选指标。26二月2024Vlookup（重点）功能：根据查找线索，到指定区域，指定列，取出相关数据。语法：VLOOKUP(<查找线索>,<在哪里找>,<找到后取第几列>,<是否模糊查找>)4个参数26二月2024

二、

VLOOKUP四个参数介绍

（查找线索，查找区域，返回值的列数，精确OR模糊查找)

任务：根据姓名查找所对应的年龄。

26二月2024设置查找界面表二A列的姓名“南星”就是查找线索，即“A13”。在表二年龄下面设置函数,以通过它在表一中查找相关年龄。

第1个参数说明：查找线索

26二月2024查找线索设置公式处！

实例公式：B13=VLOOKUP(A13,$B$2:$D$8,3,0)

参数2：查找区域

$B$2:$D$8”就是查找区域，

即从B列第二行到D列第8行

注意：查找区域与初始数据（数据源）不一定边界相同？？？？？？

26二月2024注意第2个参数查找区域与数据源的关系！26二月2024查找区域数据源大于或等于查找区域！数据源区域26二月2024重大说明：A：查找线索一定要在查找区域的第1列。例如“南星”，必须是查找区域的第1列。则选定该范围时要从初始数据第2列开始.B：查找区域一定要包含返回值所在的列。本例要返回的值是“年龄”，那么“年龄”列一定要包括在这个范围内。

实例公式：B13=VLOOKUP(A13,$B$2:$D$8,3,0)

参数3：返回值的列数它是“返回值”在第二个参数“查找区域”的列数。本例中“年龄”在查找区域”$B$2:$D$8的第3列，因此该值就是整数“3”。

注意：这里提到的列数不是在初始数据的列数（不是第4列），而是在查找区域区域的第几列。26二月2024

实例公式：B13=VLOOKUP(A13,$B$2:$D$8,3,0)

参数4：精确OR模糊查找

第4个参数如果：值为0或FALSE表示精确查值为1或TRUE时表示模糊查

注意：千万不要把这个参数漏了，如果缺少这个值，参数默认为模糊查找，经常会出错！！！！26二月2024比如我们查高考成绩，，，，，我们在相关网站上输入自己的名字那么这名字就是查找线索！！！！！！26二月2024注意查找线索必须在原数据表外面单元格中新建！！！！！（例如A13）26二月2024

实例公式：B13=VLOOKUP(A13,$B$2:$D$8,3,0)

VLOOKUP参数的变化1、按关键词搜索2、查找区域在多个表格中3、从数据源中查找多个数据4、模糊查找26二月20241、按关键词查找

26二月2024

1、关键词应修正为“*联想*”2、如果关键词为单元格引用，则查找线索输入：“*”&A12&“*”

26二月20242、查找区域在多个表格中

26二月20242、查找区域在多个表格中

26二月2024=VLOOKUP($A16,IF($B$15="实际金额",$A$1:$D$12,$H$1:$K$12),3,0)3、从数据源中查找多个数据

26二月2024练习一下COLUMN函数作用：返回引用单元格的列号1、选择某空白单元格输入=COLUMN（）：即显示当前单元格的列号，然后向右填充2、思考如何在一行中连续显示2、3、4、5这样一串数字：二种方法可用26二月2024练习一下COLUMN函数思考如何在一行中连续出现2、3、4、5这样一串数字：二种方法可用：1、利用当前单元格进行调整比如=COLUMN（）显示为15,则？？？2、直接利用=COLUMN(B1)26二月2024练习一下COLUMN函数思考如何在一行中连续出现2、3、4、5这样一串数字：二种方法可用：1、利用当前单元格进行调整比如=COLUMN（）显示为15,

则=COLUMN（）-13=2

2、直接利用=COLUMN(B1)=226二月202426二月2024注意：1、查找区域$A$1:$d$11要加上绝对引用，因为后续查找都引用这个区域。2、查找线索也是反复用一个，也需要绝对引用。4、模糊查找模糊查找也可称为近似查找。查找的线索在数据源中缺失，但处在一个范围内有对应的结果。26二月2024例题26二月2024某企业设置员工的评价方案如下：起点200200-500为不合格，700-500为及格，700-900为良好，900以上为优秀某员工考核指标为800，如何寻找评价信息。例题26二月2024某企业设置员工的评价方案如下：起点200200-500为不合格，700-500为及格，700-900为良好，900以上为优秀某员工考核指标为800，如何寻找评价信息。26二月2024某企业设置员工的评价方案为：200-500为不合格，700-500为及格，700-900为良好900以上为优秀某员工考核指标为800，如何寻找评价信息。查找线索查找区域26二月2024考核指标考核区域考核评价200200-500不合格500500-700合格700700-900良好900900以上优秀1、把评价方案转化为表格：即查找区域（数据源）系统根据查找线索提供的数据，从第一列中去近似查找。自制表格（查找区域）步骤1、输入3列数据标题：A-分界点，B-对应的数据段，C-赋值项目2、准确找出数据分界点（注意与IF分界点的差异）3、把分界点载入表格第一列，从小到大排列4、第二列载入每一个分界点对应的数据段5、第三列载入每一个数据段对应的赋值项目名称26二月2024自制查找表格的核心，，就是找出分界点，“向小靠拢的规则”决定了分界点必须从最小值开始。26二月2024考核指标考核区域考核评价200200-500不合格500500-700合格700700-900良好900900以上优秀模糊查找取值特点26二月2024考核指标考核区域考核评价200200-500不合格500500-700（630）合格700700-900良好900900以上优秀如果当前的查找线索是“630”，VLOOKUP会自动选择“500”对应的结果去取值。这个原则称为“向小靠拢原则”。26二月2024如果熟练了，数据源只需要二列数据即可。无须载入中间那列分段数据。26二月2024必须自制数据源

=VLOOKUP(B13,$A$2:$B$6,2,1)

请思考考核分界点考核区域1考核区域具体表格考核评价200200-500200≤Y<500不合格500500-700500≤Y<700合格700700-900700≤Y<900良好900900以后900≤Y优秀26二月2024请思考：如果考核区域改变一下，该如何设置分界点26二月202426二月2024500900700优秀良好合格不合格200364690870128026二月2024500900700优秀良好合格不合格比对IF函数分界点的差异在哪里？？深刻提醒精确查找，不需自建数据源，但需要在数据源中合理选择“查找区域”！！模糊查找则需要建立数据源！！！自建数据源！！！26二月2024练习1、在销售表中自动返回产品单价2、根据多条件派发赠品26二月2024=VLOOKUP(A2,A$1:D$18,4,0)*B2=VLOOKUP(C8,IF(D8=“金卡”，$A$3:$B$5,$C$3:$D$5),2,0)26二月2024查询快递费：==VLOOKUP("*"&C2&"*",B6:C12,2,0)查询工资总额=VLOOKUP(A2,工资清单!B:G,6,0)查询应收账款=MAX(TODAY()-B2,0)==VLOOKUP(D2,账龄标准!A:B,2)查询工资1=VLOOKUP($A$2,工资清单!$A:$AW,COLUMN(B1),0)查询工资2=VLOOKUP($B$1,工资清单!A:AW,ROW(A2),0)26二月2024VLOOKUP查找结果出现#N/A：

错误解析1）状况说明：在数据源首列查找不到第一参数的值。2）产生原因：1、数据类型不匹配：文本查找数值，，或数值查找文本2、只有关键字数据：3、查找线索对应的单元格数据缺失或格式不对3）解决方法：1、第一参数“*1”或者“&””“2、第一参数前后加通配符“*”3、补充数据26二月2024使用VLOOKUP的必要条件1、查找区域必须是列结构2、查询条件必须是单条件3、查询方向从左至右4、查询区域不可以出现重复数据查询依据不区别大小写26二月2024函数2——统计函数在各类业绩数据汇总中，经常会用到求和，计数，求平均值，求最大、最小值之类的核算。由此我们导入相关函数的学习。26二月20241）求和函数

1、简单求和SUM（求和区域）2、单一条件求和SUMIF（条件区域，条件，求和区域）3、多条件求和SUMIFS（求和区域，条件区域，条件，条件区域，条件）26二月20241.简单求和函数SUM() 打开表格后，我们将语文成绩一栏的数据求和。在B列的B6单元格输入公式：=SUM(B2:B5)，公式输入完成后按回车键即可。26二月20241.求和函数SUM() 打开表格后，我们将语文成绩一栏的数据求和。在B列的B6单元格输入公式：=SUM(B2:B5)，公式输入完成后按回车键即可。26二月2024SUM(B2:B5)函数的含义把语文成绩一列全部求和，数值是从B2到B5数值结束26二月2024单击公式按钮，会发现在下方就有一个自动求和的按钮。26二月2024这时我们先将鼠标单击B6单元格，然后单击自动求和按钮会出现如图所示，按后按回车键即可。26二月2024再举一例，求数学成绩超过95分的同学的总分之和。？？？？26二月2024sumif（单一条件求和函数）功能对符合某条件的那些记录的相关指定值求和语法SUMIF(条件判断区域,“条件”,

求和区域)3大参数26二月2024在单元格中输入公式=SUMIF(D2:D8,">=95",F2:F8)26二月2024条件区域条件求和区域sumif三个参数解释

1、<条件判断区域>为用于条件判断的单元格区域。2、<条件>为确定哪些单元格将被相加求和的条件。例如，条件可以表示为95、"95"、">95"或"apples"等文本。3、<求和区域>是需要求和的实际单元格。26二月2024重点内容：条件的设置IF函数中的条件设置与SUMIF的不同IF函数中的条件参数是一个单元格值的判断：“B1>5”，”A1<8”，“A1=10”而SUMIF中的条件是一块区域数据具备的共同特征。(如：“>5”,“<8”,10或”10”)。IF(A1=“女”,“优秀”，“合格”）SUMIF(A3:B5,“女”,B3:B5)26二月2024条件的输入格式

重点内容提示1、条件如果是文本，日期，条件表达式，其外面都要加双引号，例如“>30”，“>2002/01/23”2、如果条件表达式涉及单元格引用，必须写成“>”&A1，不可以写成“>A1”

这种形式。单个的单元格引用，就直接导入A1，且不要双引号3、支持通配符的使用，例如“*电脑”这种。26二月2024看图，结果完全无误26二月2024在单元格中输入公式=SUMIF(D2:D8,">=95",F2:F8)26二月2024条件区域条件求和区域注意当条件区域和求和区域一致时，求和区域这参数可以省略。26二月2024Sumifs（多条件求和函数）功能对符合多条件的那些记录的指定值求和语法SUMIFS(求和区域,条件判断区域1,“条件1”，条件判断区域2，”条件2“)对比:SUMIF(条件判断区域,“条件”,求和区域)26二月2024特别注意SUMIFS多个条件同时存在，，一般要求数据分布在不同列，但如果同一列里，，核算不同条件的汇总数，，则要用SUMIF相加！！！设置公式！！！26二月2024求语文和数学得分都大于等于90分的学生总分之和26二月2024在G4单元格输入公式=SUMIFS(F2:F8,C2:C8,">=90",D2:D8,">=90")26二月2024看到图中语文和数学都大于等于90分的学生只有一个同学，他的总分就是247分，与公式求得的结果完全一致。26二月2024求语文得分大于等于80分和小于60分的学生总分之和26二月2024求语文得分大于等于90分和小于80分的学生总分之和26二月2024这个就必须用二个SUMIF相加了易犯错误提醒多条件求和必须区别SUMIFS与SUMIF+SUMIF同一列的多条件求和用SUMIF相加不同列的多条件求和用SUMIFS26二月2024特别提醒参数设置时，尽量使用单元格引用，少输入文字26二月2024仅供参考,有错误!!!电脑总数量=SUMIF(A3:A12,”*电脑”,C3:C12)＆”台”或&B3清华和方正的数量=SUMIF(C3:C12,A3:A12,”清华*”)+SUMIF(C3:C12,A3:A12,”方正*”)(错误)正在使用的电脑有多少台=SUMIFS(C3:C12,A3:A12,”*电脑”,D3:D12,”正在使用”)26二月2024=SUMIF(A18:A31,1,B18:B31)=AVERAGEIF(A18:A31,1,B18:B31)=AVERAGE(C18:C31)=AVERAGE(D18:D31)26二月2024=countif(B37:B46,”男士袜子”)=SUMIF(B37:B46,”男士袜子”,C37:C46)=SUM(H38,H39)=IF(B70>=90%,0,IF(B70>=80%,1,2))=IF(B70>=90%,0,IF(B70>=80%,1,IF(B70>=70%,2,3)))26二月20241、无条件统计个数:count(条件统计区域）COUNTA

COUNT(A1:A10)

计算不为空的记录个数

2、单条件统计个数:

countif（条件统计区域，判断条件）

COUNTIF(A1:A10,"m")

2）.计数函数3、多条件统计个数countifS（条件区域1，条件1,条件区域2，条件2)COUNTIFS(A1:A10,"m",c1:c10,"n")26二月202426二月2024统计出表格中分数大于500分的人数班级姓名分数

1王一499

2周六589

3李四520

2李五510

1周车600

2余二460

3周一500

1周九480

2向三620

3周七570

2向一450

26二月2024COUNTIF(C2:C10,“>500")26二月2024统计出指定1班级中分数大于500分的人数班级姓名分数

1王一499

2周六589

3李四520

2李五510

1周车600

2余二460

3周一500

1周九480

2向三620

3周七570

2向一450

26二月2024COUNTIFS(A2:A10,”1”,C2:C10,“>500")3）求平均值函数1、普通均值——average(平均值区域)2、条件均值——averageif（条件区域，条件，平均值区域）（当条件区域和平均值区域一致时，平均值区域这参数可以省略，）26二月2024三、几个常用的抓取

文本信息的函数1、FIND（查找的文本，包含的文本）2、LEFT（给定的文本字符串，提取数量)3、RIGHT（给定的文本字符串，提取数量）4、MID(指定字符串，指定提取的第一个字符位置，提取的字符数)5、TEXT6、LEN（求字符串长度）26二月2024四、几个常用日期函数1、TODAY:返回当前日期的序列号2、YEAR：返回某日期中的年份3、MONTH：返回某日期中的月份4、WEEKDAY：返回某日期为星期几练习：返回今天的日期，今天的年，月5、D