Excel+Power BI数据分析电子课件第3章

第3章经典函数让你战无不胜教师：Office助手Classicalfunctionsmakeyouinvincible3.1统计函数3.3查找与引用函数3.2逻辑函数3.1统计函数Statisticalfunction实例31自动计算命令自动计算命令是指“开始”选项卡“编辑”组中的“自动求和”按钮，能快速的进行一些简单的统计操作，如求和、求平均值、最大/小值等，既简化了运算了又大大提高了工作效率。下面介绍自动计算命令的使用方法。如图所示，某个商店第2季度销售记录，使用自动计算命令求出每个月销售的最大值，方法如下：实例31自动计算命令选定B3:D11单元格区域，打开“开始”选项卡，单击“编辑”组中的“自动求和”按钮，打开的下拉列表中选择“最大值”，如左图所示，即可求出每个月的销售的最大值，如右图所示。此方法简单快捷，因此，在进行基础计算时，优先推荐使用自动计算命令进行操作实例32统计个数COUNT函数COUNT函数用于计算某个区域中包含数字的单元格的个数。例如，在左图所示的工作表中使用COUNT函数计算A2:A8区域中数字个数。单击B2单元格，在B2单元格或编辑栏中输入公式：=COUNT(A2:A8)，如中间图所示，按Enter键确认后，即可计算出包含数字的单元格个数，对文本(A2、A8)、逻辑值(A6)，错误值(A7)不进行计算，如右图所示。实例32统计个数COUNT函数综上，可以看出COUNT函数语法结构如下：COUNT(Value1,[Value2],...)Value1,Value2…是1-255个参数，可以包含或引用各种不同类型的参数，最多可包含255个参数，各参数之间用逗号分割。需要注意的是，函数或公式中的符号都要使用英文半角形式，此处的逗号也不例外。注意:1.如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如"1"），则将被计算在内。2.逻辑值和直接输入到参数列表中代表数字的文本被计算在内，如果参数为错误值或不能转换为数字的文本，则不会被计算在内。3.如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将不计算在内。4.若要计算逻辑值、文本值或错误值的个数，使用COUNTA函数。5.若要只计算符合某一条件的数字的个数，使用COUNTIF函数或COUNTIFS函数。实例33单条件计数COUNTIF函数COUNTIF函数用于计算某个区域中满足给定条件的单元格数目，其语法结构如下：COUNTIF(range，criteria)例如，使用COUNTIF函数在“请假统计”工作表中对每位员工的请假情况进行统计并以此填入“个人请假情况”工作表的相应单元格，操作步骤如下：实例33单条件计数COUNTIF函数步骤1：单击“个人请假情况”工作表中的B2单元格，打开“公式”选项卡，单击“插入函数”按钮，弹出“插入函数”对话框。在“搜索函数”文本框中输入“countif”，单击“转到”按钮进行查找，查找结果显示在“选择函数”列表框中，单击“确定”按钮。弹出“函数参数”对话框。步骤2：将鼠标光标定位在“函数参数”对话框的“Range”文本框中，单击“请假统计”工作表标签，鼠标拖动选中D3:D25单元格区域，将鼠标光标定位在“Criteria”文本框，再单击A2单元格，单击“确定”按钮，求出第一位员工的请假次数。步骤3：将鼠标指针指向B2单元格填充柄，双击填充柄或者按住鼠标左键进行拖动，自动求出其他员工的请假次数，如图所示。实例33单条件计数COUNTIF函数如果公式使用熟练，可以直接输入公式和参数求出每位员工的请假情况，操作步骤如下：步骤1：单击“个人请假情况”工作表中的B2单元格，在编辑栏中输入公式：=COUNTIF(请假统计!$D$3:$D$25,A2)，如图所示，按Enter键，求出第一位员工的请假次数。步骤2：将鼠标指针指向B2单元格填充柄，双击填充柄或者按住鼠标左键进行拖动，求出其他员工的请假次数。实例34多条件计数COUNTIFS函数COUNTIFS函数用于计算多个区域中满足给定条件的单元格的个数，给定的条件为2个及以上，其语法结构如下：COUNTIFS(criteria_range1,criteria1,…)下面通过实例介绍该函数的使用方法。在左图所示的工作表中，要求根据“顾客资料”工作表中的数据，在“性别和年龄”工作表的B列中计算各年龄段男顾客人数，如右图所示。实例34多条件计数COUNTIFS函数本例给定2个条件：条件1是各年龄段；条件2是男顾客，所以使用COUNTIFS函数进行求解，操作步骤如下：步骤1：在“性别和年龄”工作表中，单击B2单元格，然后单击功能区中的“插入函数”按钮，弹出的对话框中，进行如图所示的设置。实例34多条件计数COUNTIFS函数步骤2：弹出“函数参数”对话框，进行如左图所示的设置。步骤3：将鼠标指针指向B2单元格填充柄，双击填充柄或者按住鼠标左键进行拖动，自动求出其他年龄段男顾客人数，如右图所示。实例34多条件计数COUNTIFS函数如果公式使用熟练，可以直接输入公式和参数求出各年龄段男顾客人数，方法如下：在"性别和年龄"工作表中，单击B2单元格，输入公式：=COUNTIFS(顾客资料!C2:C21,A2,顾客资料!B2:B21,"男")，按Enter键确认操作，然后利用自动填充功能对其他单元格进行填充。因为第1参数C2:C21单元格区域在C列，所以C2:C21可以简写C:C，代表整个C列。同理，第3参数B2:B21单元格区域在B列，所以B2:B21可以简写为B:B，代表整个B列。因此公式：=COUNTIFS(顾客资料!C2:C21,A2,顾客资料!B2:B21,"男")，可以简化为：=COUNTIFS(顾客资料!C:C,A2,顾客资料!B:B,"男")。实例35单条件求和SUMIF函数SUMIF函数用于对满足条件的单元格求和，其语法结构如下：SUMIF(range,criteria,[sum_range])某商店的销售商品明细如图3-20所示，要求按照B列中的类别，统计“日用品”销售总额，将统计结果保存在H5单元格中。实例35单条件求和SUMIF函数步骤1：单击H2单元格，然后单击编辑栏中的“插入函数”按钮，在“插入函数”对话框中搜索SUMIF函数，单击“确定”按钮，弹出“函数参数”对话框。步骤2：在“Range”文本框中输入条件所在区域，“日用品”在B2:B20区域，因此输入B2:B20，或者通过选中区域的方式输入条件所在区域，方法：将鼠标光标定位在该文本框中，鼠标拖动选中工作表中的B2:B20区域，如图所示。实例35单条件求和SUMIF函数步骤3：在“Criteria”文本框中输入查找值，查找值位于G5单元格，所以单击G5单元格或者直接输入G5。步骤4：在“Sum_Range”文本框中输入求和数据所在的区域。本例要对金额进行统计，金额位置在E列，因此输入E2:E20，或者通过选中区域的方式输入求和数据所在区域，如图所示。实例35单条件求和SUMIF函数步骤5：单击“确定”按钮，“日用品”销售总额显示在H2单元格，如图所示。如果公式使用熟练，可以直接输入公式和参数求出“日用品”销售总额，方法如下：单击H5单元格，输入公式：=SUMIF(B:B,G5,E:E)或者=SUMIF(B2:B20,G5,E2:E20)，按Enter键确认输入，即可统计出“日用品”销售总额。实例36多条件求和SUMIFS函数SUMIFS函数用于对区域中满足多个条件的单元格求和，其语法结构如下：SUMIFS(sum_range,criteria_range1,criteria1,…)。如图所示，要求统计“日用品”中“酸奶酪”销售总额，将统计结果保存在I5单元格中。实例36多条件求和SUMIFS函数步骤1：单击I5单元格，然后单击编辑栏中的“插入函数”按钮，在“插入函数”对话框中搜索SUMIFS函数，单击“确定”按钮，弹出“函数参数”对话框。步骤2：在“Sum_Range”文本框中输入求和数据所在的区域。此例要对金额进行统计，金额位置在E列，因此输入E2:E20，或者通过选中区域的方式输入条件所在区域，方法：将鼠标光标定位在该文本框中，鼠标拖动选中工作表中的E2:E20区域，如图所示。实例36多条件求和SUMIFS函数步骤3：在“Criteria_Range1”文本框中输入条件1所在区域。此例首先按“日用品”进行统计，条件1所在位置是B列，因此输入B2:B20，或者通过选中区域的方式输入条件1所在区域，方法：将鼠标光标定位在该文本框中，鼠标拖动选中工作表中的B2:B20区域。步骤4：在“Criteria1”文本框中输入查找值1，查找值1位于G5单元格，所以单击G5单元格或者直接输入G5，如图所示。实例36多条件求和SUMIFS函数步骤5：在“Criteria_Range2”文本框中输入条件2所在区域。条件2“酸奶酪”所在位置是A列，因此输入A2:A20，或者通过选中区域的方式输入条件2所在区域，如图所示。步骤6：在“Criteria2”文本框中输入查找值2，查找值2位于H5单元格，所以单击H5单元格或者直接输入H5实例36多条件求和SUMIFS函数步骤7：单击“确定”按钮，“日用品”中“酸奶酪”销售总额显示在I5单元格，如图所示。如果公式使用熟练，可以直接输入公式和参数求出“日用品”销售总额，方法如下：单击I5单元格，输入公式：=SUMIFS(E:E,B:B,G5,A:A,H5)或者=SUMIFS(E2:E20,B2:B20,G5,A2:A20,H5)，按Enter键确认输入，即可统计出“日用品”中“酸奶酪”销售总额。实例37获取排名:RANK函数RANK函数用于对指定区域内的数据进行排名，其语法结构为：RANK(number,ref,[order])。在图中，按照由高到低的顺序统计每种产品的“销量”排名，以第1名、第2名、第3名……的形式标识名次并填入“销量排名”列中。操作步骤如下：实例37获取排名:RANK函数步骤1：单击D2单元格，然后单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框，搜索RANK函数，如图3-35所示，单击“确定”按钮，弹出“函数参数”对话框。步骤2：在“Number”文本框中输入要排名的单元格。因要对“销量”排名，所以单击工作表中第一个“销量”单元格C2。步骤3：在“Ref”文本框中指定排名所在的区域。本例要对C2:C14区域的数据排名，因此将光标定位在该文本框中，鼠标拖动选定工作表中的C2:C14区域。并按F4键在行号和列标前面加上绝对引用符号$，固定排名区域，如图所示。实例37获取排名:RANK函数步骤4：在“Order”文本框中设置排位方式。从大到小排序为降序，用0表示或省略不写，默认降序；从小到大排序为升序，用非零值(如1)表示。本例省略不写，降序排名。步骤5：设置完成后，单击“确定”按钮，第一个“销量”排名显示在D2单元格中，如图所示实例37获取排名:RANK函数步骤6：将光标定位在编辑栏“=”后面，输入”第”&，在公式末尾“)”后输入&”名”,如左图所示，&是连接符号，将“第”、“RANK(E2,$E$2:$E$26)”、“名”三者联系起来。按Enter键，效果如右图所示。实例37获取排名:RANK函数步骤7：将鼠标指针指向D2单元格的填充柄，双击填充柄或按住鼠标左键向下拖动，按照“销量”自动排名，如图所示。如果公式使用熟练，可以直接输入公式和参数求出各年龄段男顾客人数，方法如下：单击D2单元格，输入公式：=”第”&RANK(C2,$C$2:$C$14)&”名”，按Enter键确认操作，拖动填充柄或双击填充柄对其他单元格进行填充。3.2逻辑函数Logicalfunction实例38单条件逻辑判断IF函数If函数是逻辑判断常用函数，它可以根据给定的条件进行判断，判断条件是否成立，然后返回对应的逻辑值，某商店对销售量进行考核评定，要求将销售量>=260评定为“销售之星”，如图所示。实例38单条件逻辑判断IF函数步骤1：单击E2单元格，然后单击编辑栏中的“插入函数”按钮，在“插入函数”对话框中搜索IF函数，弹出“函数参数”对话框。步骤2：在Logical_test”文本框中输入判断条件。首先对第一个销售量进行判断，判断的条件是>=260，所以输入D2>=260步骤3：在“Value_if_true”文本框中输入条件成立时返回的结果，即“销售之星”。步骤4：在“Value_if_false”文本框中输入条件不成立时返回的结果，””，即空文本，如图所示。实例38单条件逻辑判断IF函数步骤5：单击“确定”按钮，判断结果显示在E2单元格，拖动填充柄或双击填充柄对其他单元格进行填充，效果如图所示实例38单条件逻辑判断IF函数步骤5：单击“确定”按钮，判断结果显示在E2单元格，拖动填充柄或双击填充柄对其他单元格进行填充，效果如图所示。如果公式使用熟练，可以直接输入公式和参数求出“日用品”销售总额，方法如下：单击E2单元格，输入公式：=IF(E2>260,"销售之星","")，按Enter键确认输入，拖动填充柄或双击填充柄对其他单元格进行填充，效果如上图所示。实例39多条件逻辑判断:IF＋AND函数IF＋AND函数是对多个条件同时判断，当所有条件全部满足时则条件成立，返回逻辑真值TRUE；当有一个或多个条件不满足时则条件不成立，返回逻辑假值FALSE。某商店仅对第4季度进行销售考核评定，要求将季度为“4季度”且销售量>=260评定为“销售之星”，如图所示。实例39多条件逻辑判断:IF＋AND函数单击E2单元格，输入公式：=IF(AND(B2="4季度",D2>=26),"销售之星","")，按Enter键确认输入，拖动填充柄对其他单元格进行填充，评定结果如图所示。此例中的AND(B2="4季度",D2>=26)也可以表示为AND(D2>=26,B2="4季度"),即参数的顺序不影响判断结果。这样使用IF＋AND组合函数实现了同时满足双条件的判断，同理，使用该组合函数也可以同时进行多条件判断，最多可以有255个条件。实例40多条件逻辑判断:IF＋OR函数IF＋OR函数是对多个条件进行逻辑判断，只要有一个条件满足则条件成立，返回逻辑真值TRUE；当所有条件都不满足时则条件不成立，返回逻辑假值FALSE.。某商店对销售量和销售额进行考核评定，要求将销售量>=260或者销售额>1200000评定为“销售之星”，如图所示。实例40多条件逻辑判断:IF＋OR函数在E2单元格中输入公式：=IF(OR(D2>=260,E2>1200000),"销售之星","")，按Enter键确认输入，拖动填充柄对其他单元格进行填充，评定结果如图所示。此例中的OR(D2>=260,E2>1200000)也可以表示为OR(E2>1200000,D2>=260)，即参数的顺序不影响判断结果。这样使用IF＋OR组合函数解决了“或”关系多条件逻辑判断。在处理多条件复杂逻辑判断问题时，首先要区分多个条件之间是“或”关系还是“且”关系，“或”关系使用OR函数，“且”关系使用AND函数，确定使用哪个函数后，再输入公式计算结果。实例41多层级条件逻辑判断:IF嵌套函数IF函数嵌套是对数值区间多层级关系的条件判断。例如，在图中，对各店铺的销量进行评定，根据销售量评定等级，评定规则如下：

销售量>=260，评定等级为“优秀”。

销售量>=200，评定等级为“良好”。

销售量<200，评定等级为“一般”。实例41多层级条件逻辑判断:IF嵌套函数在E2单元格中输入公式：=IF(D2>=260,"优秀",IF(D2>=200,"良好","一般"))，按Enter键确认输入，拖动填充柄对其他单元格进行填充，评定结果如图所示。按照上述方法可以对数值区间更多层级条件进行判断。层级条件增加，IF函数嵌套层级也随之增加。在使用IF函数嵌套时要注意按照数值区间的大小顺序依次对每个层级进行判断，可以按照从大到小的顺序，或者从小到大顺序，但不能从中间某个区间开始判断，否则容易出错。3.3查找与引用函数Findandreferencefunctions实例42垂直查找:VLOOKUP如图所示是学生的部分成绩表，包含学生的学号以及对应的各科成绩和总分信息，要求按照给定的学号查找学生的总分。要查找的学号位于在L列，查找结果放置在M列。操作步骤如下：实例42垂直查找:VLOOKUP步骤1：单击M2单元格，单击编辑栏中的“插入函数”按钮，在“插入函数”对话框中搜索VLOOKUP函数，单击“确定”按钮，弹出“函数参数”对话框。步骤2：在“Lookup_value”文本框中输入查找值，查找值位于L2单元格，所以单击L2单元格或者直接输入L2。步骤3：在“Table_array”文本框中输入查找区域。本例要在A2:J19区域查找，因此输入$A$2:$J$19，如图所示，或者通过选中区域的方式输入查找区域，方法：将鼠标光标定位在该文本框中，鼠标拖动选中工作表中的A2:J19区域。按F4键在行号和列标前面加上绝对引用符号$将选区固定。实例42垂直查找:VLOOKUP函数步骤4：在“Col_index_num”文本框中输入查找列数。这里的列数从查找范围的第1列作为1，而要查找的总分在第10列，所以在该文本框中输入10，如图所示，表示查找列“总分”是查找范围A2:J19区域的第10列。步骤5：在“Range_lookup”文本框中输入精确匹配的值。此例按照学号精确查找总分，因此参数输入为0或FALSE，如图所示。实例42垂直查找:VLOOKUP函数步骤6：单击“确定”按钮，学号120304的总分显示在M2单元格，双击M2单元格填充柄，自动填充其他学号的“总分”，效果如图所示。如果公式使用熟练，在M2单元格中直接输入公式：=VLOOKUP(L2,$A$2:$J$19,10,0)或者=VLOOKUP(L:L,$A$2:$J$19,10,0)，按Enter键确认输入，双击M2单元格填充柄即可按照给定的学号查询到对应的总分。实例43提取单元格数据:INDEX函数INDEX函数既可以从单列中提取数据，也可以从单行中提取数据，还可以从行列交叉区域中提取数据，下面结合实例介绍INDEX函数从单列、单行及其交叉区域中提取数据方法。1.从单列中提取单元格数据如图所示是某单位部分工资表，要求按照工号查找奖金，查找条件位于H2单元格，查找结果放置在I2单元格。实例43提取单元格数据:INDEX函数2.从单行中提取单元格数据在图中，要求按照名称查找对应的金额，查找条件位于F2单元格，查找结果放置在G2单元格。此例是按照F2单元格中的查找条件在第2行中提取对应的数值，将提取的数值放置在G2单元格。所以在G2单元格中输入公式：=INDEX(B2:D2,3)，按Enter键确认输入，即可提取查找条件对应的数据，如图所示。实例43提取单元格数据:INDEX函数3.从行列交叉区域中提取单元格数据在图3-63所示的工资表中，要求按照工号和名称双条件查找对应的金额，查找条件分别位于H2和I2单元格，查找结果放置在J2单元格。实例43提取单元格数据:INDEX函数此例是按照H2和I2单元格中的查找条件在第4行