Excel工作表可以理解为一个由行、列组成的二维数组。Excel公式支持数组和数组公式。数组可以是常量数组,也可以是单元格区域的引用。使用数组和数组公式时,可以逐数组元素或逐单元格处理后返回结果。
下面直接从一个实例入手,再去讨论相关知识点。
1 实例(从文本里提取数字)
相关数据及需要达到的效果:
序号 | 记录 | 金额 |
1 | 吃饭15元 | 15 |
2 | 朋友过生日送礼金300元 | 300 |
3 | 买米95 | 95 |
4 | 火车票200块回家 | 200 |
5 | 房租900元 | 900 |
6 | 水费200块钱 | 200 |
7 | 电费250元 | 250 |
关键是要获得第一个数字出现的位置。
试着使用下面的公式
=FIND(1,B2,1)
可以返回3,表示字符1在字符串“吃饭15元”中的位置。
如果要查找0,1,2,3,4,5,6,7,8,9十个字符呢?这时,数组及数组公式就可以派上用场了。使用数组{0,1,2,3,4,5,6,7,8,9}作为参数,可以逐步处理每一个数字在字符串中出现的位置,返回的也是一个数组(10个值)。由此,我们选择一个有10个单元格的连续区域D2:M2,输入=FIND({0,1,2,3,4,5,6,7,8,9},B2,1),同时按下CTRL+SHIFT+ENTER,然后复制数组公式,得到如下效果:
错误值#VALUE!表示某数字在字符串中不存在,所以也就不能返回位置值。
为了避免出现错误值,可以在字符串后串一个包含10个数字字符的数字字符串:
上表中每行最小的数字就是字符串中每一个数字出现的位置,为此我们可以使用MIN()函数。=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&”0123456789″,1))
下一步要获取数字长度。
函数LEN()可以返回字符数,函数LENB()可以返回字符串的字节数,一个数字一个字节,一个汉字两个字节,组合使用可以获取数字的长度:=LEN(B2)-(LENB(B2)-LEN(B2))
使用mid函数即可以截取数字,在公式前使用两个负号,可将文本转换为数字:
=–MId(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&”0123456789″,1)),LEN(B2)-(LENB(B2)-LEN(B2)))
以下公式可以做一个改良:
1 {0,1,2,3,4,5,6,7,8,9}可以用ROW($A$1:$A$10)-1代替;
2 “0123456789”可以用5/19代替。
5/19 = 0.26315789474
得到的下面公式具有同样效果,更通用和简洁:
=–MId(B5,MIN(FIND(ROW($A$1:$A$10)-1,B5&5/19,1)),LEN(B5)-(LENB(B5)-LEN(B5)))
2 数组及数组公式相关知识
2.1 数组公式
数组就是一组数据,数组公式可以进行多重运算(对每一个数组元素或区域的每一个单元格都应用操作),减少了多次写于单元格的过程,可以实现常用公式较烦锁的操作,一步到位。
2.2 删除数组公式
当我们对一个区域应用数组公式时,如D2:D6,使用此区域,输入{=B2:B6*C2:C6},按CTRL+SHIFT+ENTER,会在D2:D6的每个单元格输入{=B2:B6*C2:C6},都是一样,此时数组公式是一个整体,不能更改数组的一部分,也就是不能单独删除其中的一个单元格的数组公式,我们要删除数组公式要全部选中,当我们有时修改一个单元格的数组公式,不能退出时,大家记得按ESC键。
2.3 一个单元格显示数组的情况
由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值。
2.4 数组分类
可分为横向数组、纵向数组、区域数组(多行多列),也可以按维来分,横向数组和纵向数组都属于一维数组,区域数组属于二维数,像这种{1,2,3,1}就是常量数组
2.5 怎样查看数组的结果(也就是选择公式的一部分求结果),当然是独孤九剑F9或使用“公式求值”来按顺序求值复合公式的每一部分。
2.6 数组成员中间有时有分号,有时用逗号是怎么回事?横向数组用逗号分开,纵向数组用分号分开。
2.7 常量数据在函数里的应用
a) 23 =INDEX({23,24,25,22},1,1)
b) 24=INDEX({23,24,25,22},1,2)
c) 25=INDEX({23,24,25,22},1,3)
d) 22=INDEX({23,24,25,22},1,4)
2.8 要么用某些函数来取其共性,如SUM Max/Min,Small/Large等
a) 94=Sum({23,24,25,22})
b) 25=Max({23,24,25,22})
c) 22=Min({23,24,25,23})
d) 25=Large({23,24,25,22},ROW(A1))
e) 22 =SMALL({23,24,25,22},ROW(A1))
f) 23=SMALL({23,24,25,22},ROW(A2))
g) 24=SMALL({23,24,25,22},ROW(A3))
h) 25=SMALL({23,24,25,22},ROW(A4))
2.9 参数
a) 数组公式最大的特征就是所引用的参数是数组参数,包括区域数组和常量数组。
b) 区域数组,是一个矩形的单元格区域,如 $A$1:$D$5
c) 常量数组,是一组给定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3}
d) 数组公式中的参数必须为”矩形”,如{1,2,3;1,2}就无法引用了
2.10 输入
同时按下CTRL+SHIFT+ENTER,数组公式的外面会自动加上大括号{}予以区分。
3 更多实例
3.1 求表一区域F42:G44大于10数据和
公式:{=SUM((F42:G44>10)*(F42:G44))}
公式解释:F42:G44>10会返回{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE},在运算时True=1 False=0。
然后数组{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}和区域数组(F42:G44))相对应的数据进行相乘,相乘之后得到一个新的数组{20,0;0,50;30,0},然后用求和函数Sum进行求和,最后记得三键一齐下CTRL+SHIFT+ENTER
3.2 求表二产品AA的总价
公式:=SUM((C50:C54=”AA”)*(D50:D54)*(E50:E54))
c) 公式解释:=SUM((C50:C54=”AA”)*(D50:D54)*(E50:E54))
先用判断区域C50:C54是否有等于AA的,结果返回 {TRUE;FALSE;FALSE;TRUE;FALSE},因为在运算过程中True=1,False=0这样就得到{=SUM({TRUE;FALSE;FALSE;TRUE;FALSE}*(D50:D54)*(E50:E54))},然后因为任何数字和0相乘都等于0,也就是符合为AA的就不为0,不符合的就为0上面三个数组相乘最后得到一个新的数组{100;0;0;250;0},最后用Sum求和,三键一齐下得到结果为350
4 Row()函数在数组公式中的运用
a) 谈到数组公式,我们不得不说一下ROW()这个函数,它在数组公式中起到了很大的作用,许多公式中都需要用到它来作为参数。
b) 我们先来做一个题目:求正整数列1,2,3,4……100这100个数字之和(首先假设你不知道等差数列求和公式,呵呵),=SUM(ROW($A$1:$A$100))。
c) 我们利用Row(A1:A100)来产生1到100自然数,然后用Sum求和。我们都知道,ROW()是用于返回单元格行号的函数,通常它只能引用一个参数。但是在数组公式中,该函数就能引用多个单元格作为参数,对于整个引用区域进行分别运算,从而就能返回一组数据。
ROW(A1)=1
ROW(A2)=2
……
ROW(A100)=100
ROW($A$1:$A$100)={1;2;3……100}
知道了这一点以后,我们就能在数组公式中利用这一个功能来得到一组连续的正整数。
当然COLUMN()的作用和ROW()是相同的,上面的计算也能用以下公式:
{=SUM(COLUMN($A$1:$CV$1))}
但是相对于ROW()的引用方式来说,A1:A100要比A1:CV1更直观地体现出所引用的是100行还是100列,所以ROW()一般来说使用得更普遍些,当然也不排除有时候需要用到COLUMN(),这就要看具体情况了。