版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、 第 4 章 数算 数 算不论在任何行业都应用较频繁,而其使用又偏于简单,所以本书基于从易到难的原则从数学函数开始教学。 本章针对数学函数中的一些常用函数展示其功能、语法、应用案例和多函数嵌套的应用技巧,也会涉及一些简单的数组应用。 本章要点: 求和运算 求积运算 商与余数 随机数 乘方与开方 数据之舍入问题 分类汇总 4.1 求和运算 数 算中,没有比求和运算更常用的了。然而求和也有诸多技巧,掌握好这些求和函数可使工作事半功倍。 4.1.1 SUM 求和 SUM 函数用于计算其所有参数之和。 SUM 函数有 1255 个参数,除第一个参数外,每个参数都是可选参数。其语法如下: SUM(num
2、ber1,number2,.)其中 number1、number2 等表示 SUM 的参数。“number2,.”中的省略号表示它有多 个参数,多到 Excel 所允许的上限值;而方括号表示这些参数都是可选参数,在使用时可以根据需 第 4 章 数算要省略参数。从第 2 参数开始,SUM 函数既可以省略参数的值也可以省略参数。 SUM 函数的参数支持所有数据类型。以下多种写法都是允许的: =SUM(A1,B3,A4:A6) 参数包括单元格与区域,支持 1255 个区域 =SUM(12.5,-5,A10:A50,10+56,) 参数包括小数、整数、区域引用、表达式等 =SUM(A:A,Sheet2
3、!B:B,1,2,3) 参数包括整列引用,且支持跨表引用和常量数组 案例 1对偶数列执行产量汇总 图 4-1 中产量皆在偶数列,汇总所有产量的公式如下: =SUM(B2:B7,D2:D7,F2:F7)公式使用了 3 个参数,分别引用 3 个产量区域, 57 只要不超过 255 个区域就可采用相同方式追加其他区域的地址。 图 4-1 汇总 3 组产量 然而,如果 Excel 只能按如此方式处理区域汇总的话,则显得过于笨拙。事实上有两种思路可以简化公式。 思路一是由 SUM 函数本身的性质所决定,即 SUM 的参数是区域引用时,会自动忽略区域中的文本与逻辑值。由此您想到了什么? 对的,既然它可以忽
4、略文本,这意味着本例中 3 个区域可以改写为一个区域: =SUM(A2:F7)思路二是采用数组公式,并套用 IF 和 MOD 函数来实现。公式如下: =SUM(IF(MOD(COLUMN(A:F),2)=0,A2:F7)在单元格中录入以上公式,并按下【Ctrl+Shift+Enter】组合键,将会返回相同的结果。 可能读者会反问:“这种解决办法似乎一样 吧?公式反而更长了!”是的,在当前环境下,公式的确显得过于臃肿,但当您学习了数组公式后,就会发现它是这 3 种公式中通用性最好的公式,可以适用于所有情况。 例如,产量表有 2 000 列,其中待汇总的数据分布在 1 000 个偶数列中,公式 1
5、 显然行不通, 事倍功半;再如 A、C、E 显示的不是人名,而是工号,那么公式 2 又行不通,它会将工号一并计算进去;而公式 3 可以适应所有情况,而且不管对 10 列还是 1 000 列汇总,公式的长度都一样。 这也说明解题思路有多种,既要计算结果准确又要提高效率必须通晓多种思路,将公式运用在合适的环境中。不同条件采用不同公式,适当地使用才可以发挥公式的潜力。 对于以上数组公式的含义、计算过程、编写思路将在本书第 14 章进行详细解说,此处只需要了解 SUM 函数有此用法即可。 本例文件参见光盘:第 4 章SUM 函数应用.xlsxSUM 函数在处理参数时还有一个特性:忽略引用中的文本、逻辑
6、值和文本型数字,但是直接写在参数中的文本、逻辑值和文本型数字不会忽略。也就是说区域引用中的文本、逻辑值和文本型数字对SUM 函数计算结果没有任何影响,而直接出现在参数中时,将分 4 种处理办法: =SUM(12,中国)文本参数将造成公式出错,直接返回错误值 =SUM(12,TRUE,23100) TRUE 当做 1 参与汇总,FALSE 当做 0 参与汇总,结果为 13=SUM(12,24)对于文本型数字将转换成数值参与汇总,公式结果为 36=SUM(1,TRUE,10,TRUE)忽略数组参数中的逻辑值 TRUE 和 FALSE,结果为 3 以上 4 个特性必须牢记,它们直接影响公式的结果。特
7、别是第 4 点,它在数组公式中使用相当频繁。 案例 2汇总A 列大于 5 的数字个数SUM 本是求和函数,但有时也用它计数。例如,计算 A 列中大于 5 的数字个数,可以采用图 4-2 中的数组公式实现。 =SUM(N(A1:A105)=SUM(-(A1:A105)=SUM(A1:A105)*1)在图 4-3 所示的公式中,表达式“A1:A105”产生的结果是逻辑值,为了让 SUM 函数可对逻辑值汇总,通常采用 3 种方法: 以上 3 个公式是数组公式,需要输入公式后按【Ctrl+Shift+Enter】组合键才可以得到正确结果。 图 4-2 使用 N 转换逻辑值图 4-3 “A1:A105”
8、的计算过程 数组参数“A1:A105”的计算过程可以通过“公式求值”对话框观察,它返回的是由 TRUE 和 FALSE 组成的数组,包括 10 个元素。由于 SUM 函数会忽略数组参数中的逻辑值,所以使用 N 函数将逻辑值转换成数值,将 TRUE 转换成 1,将 FALSE 转换成 0。所以最后参与 SUM 汇总的值是“1;0;0;1;0;1;0;0;0;0”,最终结果为 3。 使用 N 函数、“*1”或者“-”可以将逻辑值转换成 1 或者 0,对于如此用法的原理请参考本书第 5 章真假值运算的内容。 4.1.2 SUMIF 条件求和 SUMIF 函数用于按条件计算区域中数据之和。 SUMIF
9、 函数有 3 个参数,前两个是必选参数,第 3 个是可选参数。其语法如下: SUMIF(range, criteria, sum_range)其中,range 表示条件区域,只能是引用而不能使用数组;criteria 是条件,如大于 5、等于100 之类,不能同时使用多个条件,但可以配合数组的应用突破此限制;sum_range 表示实际求和的区域,如果忽略此参数,则用第 1 参数参与求和。 函数的第 3 参数可以直接忽略参数,但是不能忽略参数的值。 例如,公式“=SUMIF(A1:A10,=60,B1:B10)”的含义是条件区域 A1:A10 符合“=60” 这个条件的话,那么将 B 列中对应
10、的数值求和;公式“=SUMIF(A1:A10,100)公式中第 1 参数是条件区域也是求和区域,条件为大于 100。 如果改为汇总男性捐款之和,那么条件区域与求和区域不同(如图 4-5 所示),公式如下: =SUMIF(B2:B9,男,C2:C9)图 4-4 汇总大于 100 的数据图 4-5 汇总男性捐款之和 本例文件参见光盘:第 4 章SUMIF 函数应用.xlsxSUMIF 函数的条件参数支持通配符,这大大扩展了 SUMIF 的功能,使只有部分匹配的对象也可以参与计算。 通配符包括星号“*”和问号“?”,前者代表任意长度的字符,后者代表单个任意字符。例如, “中国”、“ 人民 ”、“中国
11、人民”都符合条件“*国*”,而唯有“中国”符合条件“?国”。如果 条件改为“?国?”,则全都不符合条件,因为“?国?”表示长度为 3,中间一位字符是“国”。 案例 2汇总已采购的不同品牌打印机数量 图 4-6 中 A 列包括多种采购物品,而且打印机还存在“打印机”与“列印机”两种写法。不过 SUMIF 支持通配符这个特性, 足以应付这种场面,使用条件“*印机”即可: =SUMIF(A2:A8,*印机,B2:B8)如果需要计算打印机与显示器两种设备之和,那么可以采用 两个 SUMIF 函数: 图 4-6 统计打印机台数 =SUMIF(A2:A8,*印机,B2:B8)+SUMIF(A2:A8,*显
12、示器,B2:B8)当学习数组后,以上公式可以简化,具体请参阅本书第 14 章和第 15 章。 SUMIF 的第 3 参数有一个奇特之处,笔者称之为“取景框”功能。即以第 1 参数为框架, 第 3 参数指定取景的起点,从而形成一个与第 1 参数对应的区域。换一种说法即为第 3 参数所代表的实际区域是以第 3 参数指定的点作为最终确定区域的左上角参照点,而高度和宽度则取决于第 1 参数。 例如,公式“=SUMIF(B2:B9,男,C2)”中第 3 参数为 C2,那么实际求和区域是 C2:C9, C2:C9 的宽度与高度来源于第 1 参数 B2:B9;公式“=SUMIF(B2:B9,男,D10)”中
13、的实际求和区域是 D10:D17。 案例 3将引用作为条件进行数据汇总 前面两个案例中 SUMIF 的第 2 参数是直接在参数中应用字符串,而对于单元格引用作为条件时则需要使用连接符&。 1图 4-7 所示为产量统计表,需要计算前 N 名之产量合计。数字 N 由 H1 单元格指定。操作步骤如下: 将 H1 单元格的格式设置为自定义“前 0 名之和”,如图 4-8 所示。 图 4-7 计算前 N 名之和图 4-8 设置单元格格式 2在 H2 单元格录入以下公式: =SUMIF(B2:F7,=&LARGE(B2:F7,H1)此时由于 H1 单元格空白,那么以上公式的结果是前 0 名之和,结果为 0
14、。 3在 H1 单元格录入数值 3,单元格将显示为“前 3 名之和”,而公式则返回 132,0, 表示 B2:F7区域中前 3 个最大值之和。 此公式需要说明两点:其一是 LARGE 函数用于计算前 N 大值,本例中使用 H1 单元格的数值3 作为 LARGE 的取值参数,表示从 B2:F7 区域取第 3 大值;其二是 SUMIF 函数的第 2 参数使用比较运算符与单元格引用时,必须使用文本连接符连接运算符与条件。例如,大于等于 A3 单元格的值需要使用表达式“=&A3”,而非“=A3”。表达式“=A3”后者的“A3”表示字符串,而非单元格引用。 案例 4统计最高销量图 4-9 是产品的销量统
15、计表,现需统计销量最高的产品的销量。 SUMIF 函数的第 2 参数支持数组,所以本例可以采用常量数组作为SUMIF 的第 2 参数,然后使用 MAX 函数计算所有产品销量之和的最大值。公式如下: =MAX(SUMIF(B2:B10,MP3;耳机;无线鼠标,C2:C10) 以上公式由于 SUMIF 函数的条件参数采用数组,所以计算结果也是一个数组“50;47;20”。而 MAX 函数则从该数组中取最大值,该值即为最高销量。 关于数组的详细阐述请参阅本书第 14 章和第 15 章。 图 4-9 产品销量表 案例 5汇总多种产品的销量图 4-10 中包括多种产品的销量,要求计算 D1 单元格的字符
16、串中指定的产品的销量合计。 根据案例 4 的分析,SUMIF 函数直接使用常量数组时可以计算多个产品的销量,那么在 SUMIF 外层套用一个 SUM 函数即可将所有销量执行合计。所以本例中的难点在于将 D1 的字符串转换成数组。 将带有分隔符号的字符串转换成数组有一个标准模板供调用: 图 4-10 统计字符串中指定的产品销量合计 TRIM(MID(SUBSTITUTE(字符对象,分隔符,REPT( ,100),ROW(1:N+1)*100-99,100)其中,“字符对象”表示待转换的字符串,也可以是单元格引用;“ROW(1:N+1)”中的 N 表示字符串中分隔符的个数。例如,将“15 元 34
17、6 元 5.5 元”转换成数组,由于在此字符串中出现的分隔符“元”有两次,那么此处 N+1 是 3,公式如下: =TRIM(MID(SUBSTITUTE(15 元346 元5.5 元,元,REPT( ,100),ROW(1:3)*100-99,100)将公式录入单元格后,从编辑栏选择整个公式,然后按【F9】键计算公式,可以得到以下数组: =15;346;5.5在本书中第 6 章讲解 TRIM 和 MID 函数时将会详细解说此模板的含义,分别记录在 6.3.5 节和 6.5.4 节。 有了以上模板,实现本例需求轻松自如,公式如下: =SUM(SUMIF(A2:A10,TRIM(MID(SUBST
18、ITUTE(D1,+,REPT(,100),ROW(1:4)*100-99,10 0),B2:B10)以上公式中 SUMIF 函数的第 2 参数计算结果是“无线鼠标;手机;无线鼠标;台灯”,所以公式中 SUMIF 函数的功能是分别统计这 4 种产品的销量,最后通过外层的 SUM 函数将 SUMIF 函数的计算结果汇总。 4.1.3 SUMIFS 多条件求和 SUMIFS 函数用于按多条件计算区域中的数据之和。 SUMIFS 函数有 3255 个参数,前 3 个是必选参数,第 4 个开始是可选参数,但是从第 2 个开始参数必须成对出现。其语法如下: SUMIFS(sum_range, crite
19、ria_range1, criteria1, criteria_range2, criteria2, .)SUMIFS 和 SUMIF 的参数排列和限制方式皆不相同。SUMIFS 函数的第 1 参数 sum_range 是求和区域,不可以省略;后面的所有参数中位于偶数位数的参数是求和的条件区域,奇数位数的参数是条件,条件区域和条件可以有 1127 对。 案例 汇总男性大于100的捐款合计 对符合两个条件的数据求和,SUM、SUMIF 和 SUMPRODUCT 等函数都可以实现,但 SUMIFS 最简单快捷,且最容易理解,如图 4-11 所示。 =SUMIFS(C2:C9,C2:C9,100,B
20、2:B9,男)此公式中,第 5 个参数原本是“=男”,此处简写成“男”。由此引申出另一个问题,假设条件就是“=男”两个字符时,公式该如何处理?答案是在条件前面追加一个等号,当有两个等号时,Excel 将第一个等号看做运算符,将第二个看做普通字符,如图 4-12 所示。 =SUMIFS(C2:C9,C2:C9,100,B2:B9,=男)图 4-11 多条件求和图 4-12 多条件求和之引申问题 本例文件参见光盘:第 4 章SUMIFS 函数应用.xlsx特别需要指出的是,SIMIFS 的第 1 参数和后面的所有偶数参数都只能是区域引用,且引用区域的高度与宽度必须完全一致。而 SUMIF 函数则不
21、强制条件区域和引用区域的高度和宽度一致。 SIMIFS 函数的条件参数也支持通配符。 4.1.4 SUMPRODUCT 乘积之和 SUMPRODUCT 函数用于求区域或数组乘积之和。 SUMPRODUCT 函数有 1255 个参数,从第 2255 个参数都是可选参数。其语法如下: 第 4 章 数算SUMPRODUCT(array1, array2, array3, .)SUMPRODUCT 函数的每个参数都是区域引用或者数组。如果是区域引用,多个引用必须保持高度与宽度一致;如果是数组,多个数组必须确保维数一致。可以同时使用区域引用和数组。 SUMPRODUCT采用数组运算,对初学者来说理解上有
22、些困难,下面通过图示展示其计算过程。图 4- 13 中有 3 个产品的单价和数量,SUMPRODUCT 函数由于采用数组运算,所以可以单 个公式实现多步运算的功能。图中公式如下: =SUMPRODUCT(B2:B4,C2:C4)SUMPRODUCT函数的计算方式是每个参数中对应的值相乘,然后将所有乘积相加得到最后结果。本公式中参数 B1:B2 和 C1:C2 的运算过程如图 4-14 所示,先计算 B2 与 C2 的乘积,然后计算B3 与 C3 的乘积,再计算 B4 与 C4 的乘积,最后将 3 个乘积累加。 图 4-13 数组运算图 4-14 使用辅助区分步运算 对于参数需要补充几点: u
23、参数中如果出现文本、文本型数字和逻辑值,都当做 0 处理。u 如果只有一个参数,那么直接合计参数中的数字。u 如果多个数组参数的维数不同,或者多个区域引用的高度不同,函数将返回错误值。案例根据瓜类产品的单价和数量计算总金额 图 4-15 是采购表,如果需要计算所有产品的金额总价,根据前面的分析 可以很快写出公式: 59 =SUMPRODUCT(B2:B8,C2:C8) 本例中要求只计算瓜类产品的总金额,意味着需要添加一个条件,然而 SUMPRODUCT 不支持 SUMIF 函数那种带通配符的条件,所以公式会复杂 一些。完整的公式如下: 图 4-15 采购表 =SUMPRODUCT(B2:B8,
24、C2:C8,-(ISNUMBER(FIND(瓜,A2:A8)本公式重点在于第 3 参数, 由于不能使用通配符来确定区域是否符合条件, 所以采用ISNUMBER+ FIND 组合实现通配符的同等功能。本例中 FIND 函数表示在 A2:A8 区域中每一个单元格查找“瓜”字,如果找到则返回代表出现位置的序号,否则返回错误值。然后使用 ISNUMBER 函数判断前一个步骤所生成的内存数组中哪些元素是数字,如果是数字将返回逻辑值 TRUE,否则返回FALSE。又由于 SUMPRODUT 函数会将逻辑值当成 0 计算,所以为了避免此问题,使用“-” 将逻辑值转换成 1 和 0 组成的数组。 或许前面的文
25、字解释在视觉上不够清晰,来看看下面的计算过程吧。 =SUMPRODUCT(B2:B8,C2:C8,-(ISNUMBER(FIND(瓜,A2:A8)=SUMPRODUCT(B2:B8,C2:C8,-(ISNUMBER(2;2;#VALUE!;#VALUE!;3;2;#VALUE!)=SUMPRODUCT(B2:B8,C2:C8,-(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE)=SUMPRODUCT(B2:B8,C2:C8,1;1;0;0;1;1;0)=171.7根据以上 4 个步骤可以很容易理解 ISNUMBER+FIND 组合及符号“-”的运作过程。当然,本例也
26、可以采用其他方式书写公式,使其简化: =SUMPRODUCT(B2:B8*C2:C8*ISNUMBER(FIND(瓜,A2:A8)这种所有参数相乘的写法奥妙在于可以直接将逻辑值转换成数字,不再需要“-”或者使用 N 函数。“-”的含义是将文本型数字或者逻辑值转换成数字。例如,“-TRUE”,其实第一个“-”已将文 本型数字或者逻辑值转换成数字,但是它得到的值是负数,所以再添加一个“-”将负数转换成正数。“-TRUE”等于 1,“-7”等于 7。 本例文件参见光盘:第 4 章SUMPRODUCT 函数应用.xlsx4.1.5 SUMSQ 求平方和 SUMSQ 函数用于计算所有参数的平方和。 SU
27、MSQ 函数有 1255 个参数,除第一个参数外,每个参数都是可选参数。其语法如下: SUMSQ(number1,number2,.) SUMSQ 和SUM 函数的使用方法和参数规则完全相同。 案例 根据三角形边长判断三角形是否直角三角形 图 4-16 中包括一个三角形的 3 条边长,要求判断它是否直角三角形。由于直角三角形的特点是两直角边的平方之和等于斜边的平方,所以 利用 SUMSQ 函数可以比较简单地完成判断。公式如下: =SUMSQ(A2:B2)=C22图 4-16 根据三角形边长判断是否直角三角形 但是,以上公式显然通用性不够,如果斜边不确定存放在哪个单元格中,那么需要修改公式,找出
28、两个直角边和斜边。公式如下: =SUMSQ(SMALL(A2:C2,1),SMALL(A2:C2,2)=MAX(A2:C2)2以上公式中 SMALL 函数用于获取第 N 个最小值,本例中利用它从 A2:C2 中提取第 1 和第 2 小值,即三角形的直角边,再用 MAX 函数提取最大值,最后分别计算平方后再进行比较。 本例文件参见光盘:第 4 章SUMSQ 函数应用.xlsx4.2 求积运算 求积运算也称乘法运算,Excel 提供了 5 个与求积相关的函数,但由于只有 PRODUCT 和 MMULT 函数实用性较强,本节仅针对这两个函数进行详解。 第 4 章 数算4.2.1 PRODUCT 求积
29、 PRODUCT 函数有 1255 个参数,除第一个参数外,每个参数都是可选参数。其语法如下: PRODUCT(number1, number2, .) PRODUCT 和 SUM 函数的使用方法和参数规则完全相同。 案例 1根据规则计算体积79 根据长宽高计算体积,这是 PRODUCT 函数的专长。例如, 图 4-17 中的数据,直接将存放长宽高的区域地址作为函数的参数即可。 =PRODUCT(B3:D3)PRODUCT 函数对于空单元格会自动略过,而不是将它当做 0 处理。如上图中 D 列空白时,体积不会返回 0,而是长与宽的乘积, 即面积。 本例文件参见光盘:第 4 章PRODUCT 函
30、数应用.xlsx图 4-17 根据规则计算体积 如果参数中指定的数据包括空格或者参数是文本型数字,也不会影响乘法运算。例如以下公式: = PRODUCT (123,20, 21)公式等于 123*20*21 的乘积,函数会自动去除参数中的所有空格,且将文本型数字转换成数值再参与运算。然而对于数组参数却例外,需要通过“-”或者 N 函数将它们转换成数字后才可以求积, 这也引申出下一个案例。 案例 2从字符串中提取规格并计算体积 图 4-18 的 B 列中包括了产品的长宽高,然而 3 个数字保存在同一单元格中,且有汉字干扰, 无法用任何函数直接计算体积。不过如果将汉字替换成空格,然后将字符串分成
31、3 段再作为PRODUCT 函数的参数就可以实现计算。公式如下: ),高,),ROW($1:$3)*10,10),宽,=PRODUCT(-MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,长,公式中使用了 3 次替换函数 SUBSTITUTE 分别将“长宽高”3 字替换成 10 个空格,然后用=PRODUCT(-MID(25012020,ROW($1:$3)*10,10)=PRODUCT(- 250;120;20) ),ROW($1:$3)*10,10) ),高,),ROW($1:$3)*10,10)=PRODUCT(-MID(SUBSTITUTE(250120
32、高 20,高,250 宽 120 高 20,宽,=PRODUCT(-MID(SUBSTITUTE(SUBSTITUTE(MID 函数将替换后的新字符串分成 3 段,每段包括 3 个数字和若干个空格,接着使用“-”将 MID 取出的 3 段文本转换成数值再参与求积运算。可以通过以下步骤查看计算过程: =PRODUCT(250;120;20)= 600000关于 MID 和 SUBSTITUTE 函数的功能与语法可以参阅第 6 章的相关内容。 本公式中 MID 和 ROW 函数均采用了数组运算,输入公式后需要按【Ctrl+Shift+Enter】组合键结束,否则公式只能返回每个产品的长,而不是体积
33、。 =PRODUCT(-MID(SUBSTITUTE(B2,*,),ROW($1:$3)*10-9,10)如果本例中不包括“长宽高”3 字,而是 1*2*3 形式的规格,公式则更简单许多(如图 4-19 所示): 本公式中只执行了一次替换,将星号*替换成 10 个空格符。另外,由于替换后的字符串无前缀空格,所以需要在 ROW 后面减 9,表示从第一个字符开始取字符串。 图 4-18 根据字符串计算体积 1图 4-19 根据字符串计算体积 2本例使用了数组,如果无法理解运算过程,可以阅读本书第 14 章和第 15 章后再返回此公式, 将会明白其计算原理。 4.2.2 MMULT 两数组的矩阵积
34、MMULT 函数用于计算两个数组的矩阵乘积,这是 Excel 函数中最难理解的一个函数。 MMULT 函数有两个必选参数,分别代表两个数组或者两个区域引用。第 1 参数的列数必须等于第 2 参数的行数,其返回值是一个矩阵,矩阵的行数等于第 1 参数的行数,列数等于第 2 参数的行数。其语法如下: MMULT(array1, array2)MMULT 函数的两个参数中有任何一个文本或者空单元格都会造成公式返回错误值,而且第 1 参数的列数不等于第 2 参数的行数时也会返回错误值。 由于 MMULT 函数是执行数组运算,产生的结果也是一个数组,运算过程比较复杂,文字解说比较难理解,下面通过图片展示
35、其计算过程。 图 4-20 中的公式需选中 C3:E5 区域后再输入,且必须按【Ctrl+Shift+Enter】组合键才可以获取正确结果。 图 4-20 一维数组参数对应关系 从图 4-20 可以看出 MMULT 函数的两个数组参数的运算过程,特别是错综复杂的参数对应方 式。如果读者已明白图 4-20 所传达的信息,那么可以进入步一下,再看看二维数组参数的对应过程图示,如图 4-21 所示。 图 4-21 二维数组参数对应关系 图 4-21 显然计算过程比较复杂,如果没有借助单元格辅助,仅在大脑中推算二维参数的对应过程, 不管对新用户还是老用户都较费力,所幸工作中实际需要应用时两个参数都是多
36、行多列的情况极其罕见。 案例 1分别统计各部门盈亏总计图 4-22 中 B3:E5 区域是 3 个部门在一年中的盈亏统计数据,企业要求 60 万为目标,对于超过 60 万部分算盈利, 少于 60 万部分算亏损。现要求分开计算盈与亏的总金额。根据 MMULT 函数的特性设置公式,第 1 参数首先采用B3:E5 区域的值减去 60 计算超出 60 的金额,再追加一个条件“B3:E560”从而排除小于 60 的部分,得到真正的盈利金额;鉴于 MMULT 函数的第 2 参数行数必须等于第 1 参数 图 4-22 盈亏表 =SUM(MMULT(B3:E560)*(B3:E5-60),1;1;1;1)=S
37、UM(MMULT(B3:E560”,它可以返回一个由 TRUE 和 FALSE 组成的 3 行 4 列的数组,满足条件时返回 TRUE,否则返回 FALSE。当这个数组与另一个表达式“B3:E5-60”相乘时,由于 TRUE 乘以任何数值结果不变,而 FALSE 乘以任何数值结果为 0,从 而实现排除小于 60 部分的金额。其二是第 2 参数“1;1;1;1”,因为 MMULT 是用第 1 参数与第2 参数对应的值相乘,所以第 2 参数全都使用 1。又由于第 2 参数的行数必须等于第 1 参数的列数, 所以采用分号形式创建一个纵向的一维数组。 对于数组的详细解说请参阅本书第 14 章和第 15
38、 章。 案例 2统计区域中数据重复最多次的次数 本例文件参见光盘:第 4 章MMULT 函数应用.xlsx图 4-23 是古代美女投票调查表,要求计算其中得票最多的次数。 乍一看会感觉这与 MMULT 函数之间没有任何关系,实则 MMULT 最擅长处理这类问题。本例利用图 4-20 中的思路设计公式。首先需要将被投票的美女姓名转换成数字,即每出现一次投票,就显示数字 1,然后将这些代表不同对象的 1 分别汇总,最后利用 MAX 函数取最大值。但是这个思路引出两个难点:其一是如何将姓名转换成 1,且每出现一次姓名就产生一个对应的 1;其二是将代表不同对象的数字 1 分别汇总,而不是像 SUM 函
39、数一样不管多少参数都一次性汇总,得到单个结果。 解决第一个难点采用等号即可。例如,“A=A”结果为 TRUE,再利用“-”将 TRUE 转换成 1 即可。另外,为了让代表不同对象的 1 实现分类汇总,采用图 4-20 中的思路,将 B2:B10 转置后再与 B2:B10 进行比较,比较结果将会得到一个多行多列的数组,将该数组按行汇总其实就是此处需要实现的目的统计每一个汉字出现的次数,再接下来的工作就变得异常简单取其中的最大值。 根据以上分析,本例可用以下数组公式解决,需要按【Ctrl+Shift+Enter】组合键录入公式。 =MAX(MMULT(-(B2:B10=TRANSPOSE(B2:B
40、10),ROW(2:10)0)公式中 TRANSPOSE 函数用于转置数组,相当于将图 4-24 中的 B3:B11 转换成 D1:L1 的效果。转置后执行区域比较才会产生图 4-24 中的多行多列的内存数组。然后将这个 9 行 9 列的数组与另一个 1 行 9 列的数组“1,1,1,1,1,1,1,1,1”进行矩阵乘积,就实现了分类汇总的目的。其中MMULT 函数的第 2 参数“ROW(2:10)0”用于产生数组“1,1,1,1,1,1,1,1,1”,这基于一个数 算规则任何正数的 0 次方都等于 1。 图 4-23 投票结果表图 4-24 将姓名转换成数字 1 的运算原理图示 同样,如果读
41、者需要了解公式的每一个计算步骤,可以单击功能区“公式”“公式求值”逐步查看公式的计算过程。 案例 3计算不重复数据个数图 4-25 是 9 个月中诞生的优秀员工统计表,现需统计共有多少个优秀员工,一人出现多次只算一次。 本例和案例 2 有相似之处,前面的几个步骤完全一致,需要将每个姓名转换成数字 1,然后分类汇总,最后将这些汇总数字转换成不重复数据个数。公式如下: =SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10) 0)图 4-25 各届冠军统计表此公式是数组公式,需要在输入公式后按【Ctrl+Shift+Enter】组合键才可以获得正确结果。
42、 如果能理解案例 2 ,那么表达式“ (MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)”的运算过程就可以理解,它和案例 2 的设计思路完全一致,产生的结果是一个一维数组“3;2;2;1;1;3;2;2;3”。需要详解的是为什么 1 除以这个数组后再汇总就能得到不重复的数据个数。仔细观察数组“3;2;2;1;1;3;2;2;3”的规律,会发现某个单元格的值重复出现几次,数组中 就会出现几,而且该值会重复出现,重复次数与该值本身相等。例如,张文出现了 3 次,那么数组中会有 3 个 3;陈星出现了两次,那么数组中会对应产生两个 2;同时由于胡丽广也出现两次
43、,所以数组中有 4 个 2其余的依此类推。 接下来就是将这些重复出现的数值转换成 1,最后把所有 1 汇总即为不重复的数据个数。此处采用的“1/数组”的形式,它可以完美地解决这个问题。例如,数组中有 3 个 3,用 1 分别去除以 3 个 3,将得到 3 个 1/3,而 3 个 1/3 之和正好是 1。所以通过这种思路可以将数组中的所有数据转换成 1,而最后对 1 汇总就可实现案例的要求。 当然,计算不重复数据的个数还有很多其他方法,不局限于“SUM(1/MMULT)”这种形式,在 后面讲 COUNTIF 和 FREQUENCY 函数时将有相关的介绍。 案例 4按奖牌数量及含金量排名次图 4-
44、26 是某比赛中各省队的奖牌数量统计,要求对省队排名,排名规则有两个:按奖牌数量降序排列;数量相同时按含金量排列,金牌大于银牌,银牌大于铜牌。 排名次的一个基本思路是统计所有数据中大于当前数据的个数,将该值加 1 即为当前数据的名次。例如,所有数据中大于当前数据的个数是 0, 那么当前数据排列为 0+1 名。其他的依此类推。 而对于含金量问题,可以采用将奖牌数据按金牌、银牌、铜牌的顺序逐渐缩小的办法来处理。例如,金牌的含金量是 1,银牌的含金量 1%, 图 4-26 奖牌榜 铜牌的含金量 1/10000,如果某队得金银铜版牌各一块,那么含金量是 1.0101。最后用 1.0101 参与排序,而
45、不是用数量 3 参与排序。此方式可以顺利解决奖牌含金量问题。 根据以上分析,在 E2 单元格设置如下公式,再将公式向下填充至 E10 即可。 =1+SUM(N(MMULT(B$2:D$10,1;0.01;0.0001)B2+C2%+D2%)公式中的“MMULT(B$2:D$10,1;0.01;0.0001)”部分用于将所有省队的奖牌数量转换成含金量,其结果是“1.0205;1.0807;2.0702;8.0206;2.0306;1.0808;7.0704;5.0402;1.0106”。 然后将当前队的奖牌数转换成含金量,并用所有省队的含金量与当前队的含金量进行比较,将大于当前队含金量的个数加
46、1 即为当前队的排名。 MMULT 函数的功能相当强大,还可以实现很多神奇的矩阵运算,由于篇幅限制,不再罗列更多的案例。需要进一步学习 MMULT 函数的其他功能,读者可以进入本书售后服务 QQ 群与笔者当面交流。 4.3 商与余数 在 Excel 中,直接求商或者余数的情况极其少见,但套用在其他函数中使用的频率相当高,尤其是 MOD 函数,在工作中配合其他函数应用能发挥无限潜力。 4.3.1 QUOTIENT 求商 QUOTIENT 函数用于返回除法的整数部分,即整除状态下的商。 QUOTIENT 函数有 1255 个参数,除第一个参数外,每个参数都是可选参数。其语法如下: QUOTIENT
47、(numerator, denominator)两个参数皆是必选参数,其中第 1 参数是被除数,第 2 参数是除数。由于函数是执行除法运算, 所以参数必须是数值,否则会返回错误值。对于逻辑值不会自动转换成数值参与运算。如果第 2 参数是空单元格或者 0,将返回“#DIV/0!”。 案例 对合并单元格编号图 4-27 是工资表,每个表中包括每个员工的工资条,一个工资条包括间隔行共 4 行,现要求在 A 列对工资条编号。操作步骤如下: 1在 A1 单元格录入以下公式,A1 单元格将产生编号 1。 =QUOTIENT(ROW()+3,4) 2选择 A1:A4 区域,按下组合键【Ctrl+C】复制数据
48、。 3选择 A1:A44 区域,按下组合键【Ctrl+V】粘贴数据,A 列将产生图 4-28 所示的效果。 图 4-27 工资表图 4-28 编号 公式中 ROW 函数可以返回公式所在行的行号,所以同一个公式放在不同行中会产生不同的序号。本例中第一个公式处于 A1 单元格,那么表达式“ROW()+3”的结果为 4,将它与第 2 参数 4 相除的商为 1。 当向下复制公式后,由于公式所在单元格存在递增 4 行的关系,所以表达式“ROW()+3”能产生 4 的倍数,QUOTIENT 则将它除以 4 得到 1 的整数倍数,且以递增 1 的状态向下填充。 本例文件参见光盘:第 4 章QUOTIENT
49、函数应用.xlsx4.3.2 MOD 求余数 MOD 函数用于返回除法运算的余数。 MOD 函数有两个必选参数。其语法如下: MOD(numerator, denominator)两个参数皆是必选参数,其中第 1 参数是被除数,第 2 参数是除数,两者都必须是数值,否则会返回错误值。对于逻辑值会自动转换成数值参与运算。如果第 2 参数是空单元格或者 0,将返回“#DIV/0!”。 MOD 虽然是求余数的函数,但实际工作中通常用它实现其他功能。 案例 1隔行累加数据图 4-29 中 B2:B28 区域包括学生在初一、初二、初三 3 个年级的迟到次数统计,现要求分别统计每个年级的学生迟到合计。 首
50、先计算一年级的迟到次数合计。观察图 4-29 可以发现每个学生的一年级迟到数据的存放区域有一个特点,即 B2、B5、B8、B11B26,即单元格的行号除以 3 余数为 2。根据这个特点可以使用 MOD 搭配 ROW 函数 来实现需求,公式如下: =SUMPRODUCT(B2:B28*(MOD(ROW(2:28),3)=2)以上公式中“ROW(2:28)”可以产生 228 的自然数序列,而表达式 图 4-29 学生在 3 个年级中的迟到次数统计 “MOD(ROW(2:28),3)=2”则产生一个由 TRUE 和 FALSE 组成的数组,行号除 3 等于 2 时返回TRUE,否则返回 FALSE。
51、然后用这个数组与 B2:B28 区域的值相乘,基于 TRUE 乘以任意数等于数值本身而 FALSE 乘以任意数返回 0 这个前提,两者相乘后初二和初三对应的数据都被转换成 0, 所以 SUMPRODUCT 函数最后计算的合计数据刚好是初一学生的迟到次数合计。 =SUMPRODUCT(B2:B28*(MOD(ROW(2:28),3)=0)=SUMPRODUCT(B2:B28*(MOD(ROW(2:28),3)=1)理解以上分析后,初二和初三学生的迟到次数合计就顺理成章了,公式如下: 本例是每 3 行取 1 行数据求和的案例,如果要每 4 列取 1 列值求和,那么将 ROW 函数替换为 COLUM
52、N 函数,将 MOD 函数的第 4 参数替换为 4 即可。 本例文件参见光盘:第 4 章MOD 函数应用.xlsx案例 2根据货款计算需要备用多少零钞 图 4-30 中B 列为公司需要支付给多个厂商的货款,为了避免支付时因找零问题浪费时间,现要求计算每个厂商所需 5 元、2 元和 1 元的零钞各有多少,方便提前备好零钞。 =INT(MOD(INT(B2),10)/5)=INT(MOD(MOD(INT(B2),10),5)/2)=MOD(MOD(MOD(INT(B2),10),5),2)由于只计算零钞,所以可使用 MOD 函数将金额除以 10 后的余数进行计算。5 元、2 元和 1 元零钞数量计算公式如下: 将 3 个公式录入到 D2:F2 区域后向下填充,可以得到图 4-31 所示的效果。 图 4-30 货款列表图 4-31 根据货款计算需要备用多少零钞 在 3 个公式中都使用了 INT 函数
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2024年资产评估师之资产评估基础过关检测试卷A卷附答案
- 【2026年】2025年新能源汽车电池测试卷试卷+答案含4份
- (新版)金属冶炼(黑色金属铸造)安全管理人员考试题库(含答案)
- (完整版)骨科考试试题(含答案)
- (2025)中储粮考试题库与答案
- 2025殡葬服务笔试题及答案
- (2025)“安全生产月”安全生产知识竞赛试题库(答案)
- (新版)矿山救护队考试竞赛题库及答案
- 2025年校园新教师入职培训规章制度:教师职业素养培训大纲
- 2019-2020学年上学期高一期末考试备考金卷 数学(乙卷)试题 教师版含答案解析可编辑可打印 高一数学备考刷题
- 高甘油三酯血症管理专家共识(2025版)
- 2026年行测类题库及答案详解
- 三年级语文上册人教版《大自然的声音》
- 2026语文新教材 1第24课《我将无我不负人民》
- 2026安徽省考评员培训考试题库及答案
- 2026年四川省拟任乡科级领导干部任职资格试题及参考答案
- 统编五年级上册《航天员写给孩子的信》课件
- 2026年陕西水务发展建设集团第三工程有限公司招聘(28人)考试备考试题及答案详解
- 2026年高校审计处岗招聘笔试真题附答案
- 安徽省黄山市屯溪四中2025年七年级上学期地理期中考试(含答案)
- 凝心聚力共逐梦家校携手启新程
评论
0/150
提交评论