3.2　数组综合案例

案例143　使用数组求1～100的和

案例及公式如下图所示。这里要求1～100的和。

公式

=SUM(ROW(1:100))

公式解释

ROW(1:100)是区域数组，表示1~100行的行号， ROW(1:100)返回1;2;3;4;…;100。

最后用SUM函数求和。

本案例视频文件：03/案例143使用数组求1～100的和

案例144　使用数组求文本中的数字之和

案例及公式如下图所示。这里要求A列文本中的数字之和。

公式

=SUM(--SUBSTITUTE(A1:A3,"kg",""))

公式解释

SUBSTITUE函数的第1参数使用了数组，另外，使用SUBSTITUE函数得到的结果是文本型字符，所以还要通过减负运算变为数值型字符。

例如：在100前面加一个负号变成-100，在-100前面再加一个负号，变成了100，这样100就是数值型字符了。

最后再使用SUM函数求和。

本案例视频文件：03/案例144使用数组求文本中的数字之和

案例145　使用MID函数求单元格中的数字之和

案例及公式如下图所示。这里要求单元格中的数字之和。

公式

=SUM(--(0&MID(A1,ROW($1:$10),1)))

公式解释

其中MID函数的第2参数使用了数组，把A1单元格中的每一个数字都进行分隔。

由于A1单元格中只有3个数字，而这里分隔了10个位置，经过减负运算后结果为空值，系统会报错，所以这里在MID函数前面连接一个0。最后再用SUM函数求和。

本案例视频文件：03/案例145 使用MID函数求单元格中的数字之和

案例146　使用LEN函数统计单元格区域中有多少个字母A

案例及公式如下图所示。这里要统计A1:A3单元格区域中有多少个字母A。

公式

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"A","")))

公式解释

其中LEN函数的参数和SUBSTITUTE函数的第1参数用了数组用法。

然后用没有替换之前的字符个数减去替换成空格的个数，即得到A的个数。

本案例视频文件：03/案例146 使用LEN函数统计单元格区域中有多少个字母A

案例147　使用RIGHT函数提取单元格中右边的数字

案例及公式如下图所示。这里要把单元格右边的数字提取出来。

公式

=RIGHT(A1,COUNT(-RIGHT(A1,ROW($1:10))))

公式解释

第2个RIGHT函数的第2参数用了数组，即从A1单元格的右边提取1个字符，再提取2个字符……一直到提取10个字符。

为什么要在第2个RIGHT函数前面加一个负号？因为使用RIGHT函数得到的结果是文本型字符，通过加一个负号可以转换为数值型字符，便于COUNT函数统计数值型字符的个数。

本案例视频文件：03/案例147 使用RIGHT函数提取单元格中右边的数字

案例148　使用SUMIF函数求张三和李四的销量之和

案例及公式如下图所示。这里要求张三和李四的销量之和。

公式

=SUM(SUMIF(A:A,{"曹丽";"张三"},B:B))

公式解释

SUMIF函数的第2参数用了数组，为什么最后还要使用SUM函数求和？因为SUMIF函数的第2参数有两个条件，返回两个结果，所以要把它们加起来。

本案例视频文件：03/案例148 使用SUMIF函数求张三和李四的销量之和

案例149　使用VLOOKUP函数求每个员工上半年和下半年的销量之和

案例及公式如下图所示。这里求员工上半年和下半年的销量之和。

公式

=SUM(VLOOKUP(A2,$A$1:$C$4,{2;3},0))

公式解释

VLOOKUP函数的第3参数用了数组，返回第2列和第3列中的数值100和400。然后使用SUM函数求和。

本案例视频文件：03/案例149 使用VLOOKUP函数求每个员工上半年和下半年的销量之和

案例150　使用COUNTIF函数统计字符共出现多少次

案例及公式如下图所示。这里要统计“曹丽”和“张三”共出现多少次？

公式

=SUM(COUNTIF(A:A,{"曹丽";"张三"}))

公式解释

其中COUNTIF函数的第2参数用了数组，两个条件返回两个结果，所以外面还要嵌套一个SUM函数用来求和。

本案例视频文件：03/案例150 使用COUNTIF函数统计字符共出现多少次

案例151　使用MATCH函数统计不重复值的个数

案例及公式如下图所示。这里要统计不重复值的个数。

公式

=SUM(N(MATCH(A1:A6,A1:A6,0)=ROW(A1:A6)))

公式解释

MATCH函数的第1参数用了数组，因为查找值有6个，所以返回6个位置，分别是{1;2;3;1;2;2}。然后与ROW(A1:A6)返回的行号{1;2;3;4;5;6}对比，看是否相等，如果相等就返回TRUE，否则就返回FALSE。接着通过N函数把TRUE转换为1，把FALSE转换为0。另外要注意，此题利用了MATCH函数的一个特点来查找值：如果查找值重复出现，则只显示它第1次出现的位置，例如“曹丽”第一次出现是在第1个位置，在A4单元格中又查找到“曹丽”，但也是只返回第一次出现的位置。

本案例视频文件：03/案例151 使用MATCH函数统计不重复值的个数

案例152　使用FIND函数查找最后一个“/”的位置

案例及公式如下图所示。这里要查找最后一个“/”的位置。

公式

=COUNT(FIND("/",A1,ROW($1:99)))

公式解释

FIND函数的第3参数用了数组，即从第1个位置开始查找，只要查找到“/”就会返回一个数值，一直到最后一个“/”之后才会报错。之后用COUNT函数计算数值的个数就是最后一个“/”的位置。

本案例视频文件：03/案例152 使用FIND函数查找最后一个“/”的位置

案例153　使用FIND函数查找单元格中第一个数字出现的位置

案例及公式如下图所示。这里要查找单元格中第一个数字出现的位置。

公式

=MIN(FIND(ROW($1:$10)-1,A1&5/19))

公式解释

FIND函数的第1参数用了数组，即查找值ROW($1:$10)，返回结果{1;2;3;4;5;6;7;8;9;10}。之后为什么还要减1？因为我们需要的10个数字是从0开始的，不包括10，所以要将查找值变为{0;1;2;3;4;5;6;7;8;9}，这10个值返回10个结果。为什么还要连接“5/19?“5/19得到0.263157894736842，得到的这个值包含10个阿拉伯数字，这相当于A1&“0123456789”。因为FIND函数找不到查找值会报错，这里的目是容错：第一个数字出现在最前面的位置，所以它的值最小，而后面通过添加5/19产生的10个阿拉伯数字并不影响第一个数字出现的位置。

本案例视频文件：03/案例153 使用FIND函数查找单元格中第一个数字出现的位置

案例154　使用数组根据日期返回对应的季度

案例及公式如下图所示。这里要根据日期返回对应的季度。

公式

="第"&MATCH(MONTH(A1),{1,4,7,10})&"季度"

公式解释

这是一个普通的公式，不需要按快捷键Ctrl+Shift+Enter，但是MATCH函数的第2参数要用常量数组表示。

MATCH函数的第3参数省略了，应该是1。然后进行模糊匹配，如果找不到相等的值就找比查找值小的值，小的值如果有许多，就找其中最大的那个值。

本案例视频文件：03/案例154 使用数组根据日期返回对应的季度

案例155　使用数组隔行求和

案例及公式如下图所示。这里要求偶数行中的数值之和。

公式

=SUM((MOD(ROW(A1:A7),2)=0)*(A1:A7))

公式解释

MOD函数的第1参数（即被除数）用了数组（A1:A7），返回的结果为{1;2;3;4;5;6;7}。将结果除以2分别取它们的余数，得到{1;0;1;0;1;0;1}。然后进行判断，如果结果等于0，则说明是偶数行，如果结果等于1，则说明是奇数行。1乘以数字得到还是原来的数字，0乘以任意数字都得到0，然后再求和。

本案例视频文件：03/案例155 使用数组隔行求和

案例156　使用数组引用每一行单元格中的最后一个数据

案例及公式如下图所示。

公式

=LOOKUP(1,0/(C2:F2<>""),C2:F2)

公式解释

LOOKUP函数的第2参数用了数组。因为这个函数特殊，所以不需要按快捷键Ctrl+Shift+Enter转为数组公式。C2:F2<>""用于判断及返回逻辑值{TRUE,TRUE,FALSE, FALSE}，然后用0除以这组逻辑值得到0和报错{0,0,#DIV/0!,#DIV/0!}。

本案例视频文件：03/案例156 使用数组引用每一行单元格中的最后一个数据

案例157　使用数组引用每一行单元格中的第一个数据

案例及公式如下图所示。这里要引用每一行单元格中的第一个数据。

公式

=INDEX(C2:E2,MATCH(,0/(C2:E2<>""),))

公式解释

MATCH函数的第2参数用了数组(C2:E2<>"")，返回{TRUE,TRUE,TRUE}，然后用0除以它。因为0除以FALSE会报错，0除以TRUE会返回0，所以可以查找第一个0的位置。MATCH函数的第3参数为0，用于精确查找。这里只输入了一个逗号，是省略形式，得到了INDEX函数第3参数的值。当然这里也可以用MATCH(1=1,C2:E2<>"",)查找第一个TRUE的位置，最后整个公式实际上为：=INDEX(C2:E2,MATCH(1=1,(C2:E2<>""),))，这里的“1=1”返回TRUE，作为MATCH函数的查找值。

本案例视频文件：03/案例157 使用数组引用每一行单元格中的第一个数据

案例158　使用数组统计超过15位数字的个数

案例及公式如下图所示。这里要统计超过15位数字的个数。

公式

=SUM(N($A$1:$A$5=C1))

公式解释

此公式用于判断单元格区域A1:A5中的值是否等于单元格C1中的值，如果等于就返回TRUE，如果不等于就返回FALSE。再通过N函数把TRUE转为1，把FALSE转为0，最后求和。

如果单元格区域A1:A5中的数字个数与单元格C1中的数字个数一样，则可以在COUNTIF函数中加“*”，即=COUNTIF(A:A,C1&"*")，轻松得到结果（这是一个普通公式，第1个公式是数组公式）。

本案例视频文件：03/案例158 使用数组统计超过15位数字的个数

案例159　使用MID函数提取单元格中最后一个逗号后面的数据

案例及公式如下图所示。这里要提取单元格中最后一个逗号后面的数据。

公式

=MID(A1,MATCH(1,0/(MID(A1,ROW($1:$30),1)=","))+1,99)

公式解释

此公式先用MID函数把每一个字符进行分隔，然后判断它是否等于逗号。如果等于就返回TRUE，如果不等于就返回FALSE。

再用0除以结果，得到一串0和报错：0除以FALSE会报错，0除以1等于0。0/(MID (A1,ROW($1:$30),1)=",")返回{#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!; #DIV/ 0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!; #DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}，作为MATCH函数的第2参数。

MATCH函数的第3参数为1，这里省略了，只写了两个参数。另外，在此案例中，逗号必须是在英文半角状态输入的。

本案例视频文件：03/案例159 使用MID函数提取单元格中最后一个逗号后面的数据

案例160　使用MID函数提取数字

案例及公式如下图所示。这里要从中英文中提取数字。

公式

=MID(A2,MIN(FIND(ROW($1:$10)-1,A2&5/19)),COUNT(-MID(A2,ROW($1:$99),1)))

公式解释

这里使用COUNT(-MID(A2,ROW($1:$99),1))返回单元格中（A2）数字的个数。为什么在MID函数前面加一个负号？因为MID函数得到的是文本型数字。COUNT函数用于统计数值型数字的个数。

本案例视频文件：03/案例160 使用MID函数提取数字

案例161　使用IF+VLOOKUP函数实现反向查找

案例及公式如下图所示。这里要通过工号查找对应的姓名，即实现反向查找。

公式

=VLOOKUP(D2,IF({1,0},B:B,A:A),2,0)

公式解释

IF({1,0},B:B,A:A)的作用是把B列中的内容和A列中的内容调换，让B列中的内容在首列。

{1,0}是一个常量数组，同时显示B列和A列中的内容。

本案例视频文件：03/案例161 使用IF+VLOOKUP函数实现反向查找

案例162　使用CHOOSE函数实现反向查找

案例及公式如下图所示。这里要通过工号查找对应的姓名和数量。

公式

=VLOOKUP($E2,CHOOSE({1,2,3},$B:$B,$A:$A,$C:$C),COLUMN(B1),0)

公式解释

在CHOOSE({1,2,3},$B:$B,$A:$A,$C:$C)中，其中CHOOSE函数的第1参数为数组，同时显示3列，且B列内容在首列。

使用CHOOSE函数优于使用多层IF函数实现反向查找。如果要使用IF函数，则公式为：

=VLOOKUP($E$2,IF({1,1,0},IF({1,0},$B:$B,$A:$A),$C:$C),COLUMN(B2),0)

本案例视频文件：03/案例162 使用CHOOSE函数实现反向查找

案例163　使用COUNTIF函数统计大于100且小于200的数字个数

案例及公式如下图所示。这里要统计A列中大于100且小于或等于200的数字个数。

公式

=SUM(COUNTIF(A:A,{">100",">=200"})*{1,-1})

公式解释

COUNTIF函数的第2参数为数组，此函数还包括了两个条件，最后返回两个结果{4,2}。

之后再将这两个结果和常量数组{1,-1}相乘，目的就是过滤大于或等于200的数值，得到A列中大于100且小于200的数字个数。当然也可以用COUNTIFS函数简单实现相同的目的：=COUNTIFS(A:A,">100",A:A,"<200")。

本案例视频文件：03/案例163 使用COUNTIF函数统计大于100且小于200的数字个数

案例164　双条件查找的7种方法

这里要根据姓名和科目查找分数。

第1种方法：使用SUMIFS函数

案例及公式如下图所示。这里要通过姓名和科目查找对应的分数。

公式

=SUMIFS($C$2:$C$10,$A$2:$A$10,F2,$B$2:$B$10,G2)

公式解释

由于在数据源中，根据姓名和科目这两个条件同时查找到的值不会重复，所以，这里可以用SUMIFS函数实现多条件求和，也可以用来查询数值。

第2种方法：使用VLOOKUP函数

案例及公式如下图所示。

公式

=VLOOKUP(F2&G2,IF({1,0},$A$2:$A$10&$B$2:$B$10,$C$2:$C$10),2,)

公式解释

这里通过IF函数把A列和B列变成了一列，且作为VLOOKUP函数的第2参数的首列，C列作为VLOOKUP函数的第2参数的第2列，这样就把两个条件变成了一个条件进行查找了。

第3种方法：使用DSUM函数

案例及公式如下图所示。

公式

=DSUM($A$1:$C$10,3,F1:G2)

公式解释

这个函数的缺点是不能向下填充公式。其中第1参数为数据源A1:C10；第2参数为返回第几列（3）；第3参数为包含指定条件的单元格区域（F1:G2）。

第4种方法：使用INDEX函数

案例及公式如下图所示。

公式

=INDEX(C:C,MAX(IF(($A$2:$A$10=F2)*($B$2:$B$10=G2),ROW($C$2:$C$10),0)))

公式解释

在此公式中，当两个条件都满足时，就返回它们的行号，否则就返回0。因为这里只需要满足其中一个条件，所以取最大值得到满足条件的行号，再用INDEX函数引用对应的数值。

第5种方法：使用LOOKUP函数

案例及公式如下图所示。

公式

=LOOKUP(1,0/(($A$2:$A$10=F2)*($B$2:$B$10=G2)),$C$2:$C$10)

公式解释

当两个条件$A$2:$A$10=F2和$B$2:$B$10=G2都满足时就返回TRUE，否则就返回FALSE。然后用0除以这个一维数组，0/FALSE会报错，0/TRUE等于0。

LOOKUP函数有一个特性：如果查找值大于第2参数的最大值，就定位最后一个数值的位置，这里就是定位最后一个0的位置，然后返回第3参数对应的位置。

第6种方法：使用SUMPROUCT函数

案例及公式如下图所示。

公式

=SUMPRODUCT(($A$2:$A$10=F2)*($B$2:$B$10=G2)*($C$2:$C$10))

公式解释

在此公式中，如果两个条件都满足，则返回TRUE，再乘以C列的成绩，然后再求和。

第7种方法：使用OFFSET函数

案例及公式如下图所示。

公式

=OFFSET($C$1,MATCH(F2&G2,$A$1:$A$10&$B$1:$B$10,)-1,)

公式解释

MATCH函数把两个条件通过连接符&变成一个条件(F2&G2,$A$1:$A$10&$B$1:$B$10,)，找到两个条件满足的位置后，用OFFSET函数来返回成绩。

可以用INDIRECT和INDEX函数得到同样的结果：

=INDIRECT("R"&MATCH(F2&G2,$A$1:$A$10&$B$1:$B$10,)&"C3",)

=INDEX(C:C,MATCH(F2&G2,$A$1:$A$10&$B$1:$B$10,))

本案例视频文件：03/案例164 双条件查找的7种方法

案例165　使用INDEX函数实现一对多查询并且纵向显示结果

案例及公式如下图所示。这里要根据姓名查找对应的爱好，并纵向显示结果。

公式

=INDEX(B:B,SMALL(IF($A$1:A6=$D$1,ROW($A$1:A6),2^20),ROW(A1)))&""

公式解释

如果在单元格区域A1:A6中有姓名等于“曹丽”的，就显示它们的行号，否则就显示2^20，也就是Excel 2010版本最大的行号。

使用SMALL函数在向下填充公式时会产生由倒第1小的行号，倒数第2小的行号……组成的序列，最后用INDEX函数引用对应的爱好。

为什么后面还要连接一对空引号（“”）？这样做的目的是屏蔽引用空单元格产生的0。

本案例视频文件：03/案例165 使用INDEX函数实现一对多查询并且纵向显示结果

案例166　使用INDEX函数实现一对多查询并且横向显示结果

案例及公式如下图所示。这里要根据姓名查找对应的爱好，并横向显示结果。

公式

=INDEX($B:$B,SMALL(IF($A$1:$A6=$D$1,ROW($A$1:$A6),2^20),COLUMN(A1)))&""

公式解释

公式原理同案例165，只是这里使用了COLUMN函数。

本案例视频文件：03/案例166 使用INDEX函数实现一对多查询并且横向显示结果

案例167　实现一对多查询并且将结果用顿号分隔

案例及公式如下图所示。这里要根据姓名查找对应的爱好，且将结果用顿号分隔。

公式

=MID(SUBSTITUTE(PHONETIC(INDIRECT("R"&MATCH(D2,A:A,0)&"C1:R"&MATCH (1,0/(A:A=D2))&"C2",0)),D2,"、"),2,99)

公式解释

要得到我们需要的结果，前提条件是把A列中的姓名进行排序，让相同的姓名连在一起。

这里用两个MATCH函数找到查找值，即第1个姓名和最后一个姓名的位置作为INDIRECT函数的参数。INDIRECT函数返回的是一个动态单元格区域，然后在其外面嵌套一个连接函数PHONETIC，把姓名和爱好连接起来。

接着用替换函数SUBSTITUTE把姓名替换成顿号，然后通过MID函数过滤第1个顿号，从第2个位置开始提取，提取99个字符。

本案例视频文件：03/案例167 实现一对多查询并且将结果用顿号分隔

案例168　LOOKUP+FIND函数的经典组合应用

案例及公式如下图所示。这里根据A列中水果的品名，在E1:E7单元格中找到对应的种类。

公式

=LOOKUP(1,0/FIND($D$1:$D$7,A2),$E$1:$E$7)

公式解释

FIND函数的第1参数为数组，返回的是一个数值。接着再用0除以FIND函数得到0和报错。

根据LOOKUP函数的特性，如果查找值大于第2参数的最大值，就定位最后一个数字的位置，然后返回第3参数对应的值。

本案例视频文件：03/案例168 LOOKUP+FIND函数的经典组合应用

案例169　单列去重

案例及公式如下图所示。

公式

=INDEX(A:A,SMALL(IF(MATCH($A$2:A6&"",$A$2:A6&"",0)=ROW($A$2:A6)-1, ROW($A$2:A6),2^20),ROW(A1)))&""

公式解释

在上面的函数中，MATCH($A$2:A6&"",$A$2:A6&"",0)=ROW($A$2:A6)-1利用MATCH函数的特点：如果查找值重复出现，则只显示查找值第1次出现的位置，然后和ROW($A$2:A6)结果对比。为什么要减1？因为ROW($A$2:A6)是从2开始计算的，而MATCH函数是从1开始查找的。

为什么MATCH函数的参数还要连接“”？这里是为了容错，当向下填充公式时，$A$2:A6中的A6单元格就会变成A7单元格，A7单元格是空单元格，公式就会报错，在其后加了""就不会报错了。

为什么公式最后还要连接“”？这是为了屏蔽引用空单元格时显示的0。

本案例视频文件：03/案例169 单列去重

案例170　多列去重

案例及公式如下图所示。这里要将A、C、F列中的值去重。

公式

=INDIRECT(TEXT(MIN(IF((COUNTIF($H$1:H1,$A$2:$F$6)=0)*($A$2:$F$6<>""),ROW($A$2:$A$6)*10^5+COLUMN($A$2:$F$2),104857616384)),"R0C00000"),0)&""

公式解释

在(COUNTIF($H$1:H1,$A$2:$F$6)=0)*($A$2:$F$6<>"")中，如果H列中出现一个姓名，则COUNTIF函数统计的姓名就不是0，同时要满足单元格区域不等于空。如果这两个条件都成立，就显示行号加权后再加上列号的结果，否则就显示104857616384，即最大的行号连接最大的列号。

为什么要对行号加权呢？因为数据源是多行多列的二维区域。

这个公式中的MIN函数也用得很巧妙，当“曹丽”在单元格H2中出现时，最小值不是200001，而是200003，也就是引用的第2行中第3列的“天津丫头”了。

本案例视频文件：03/案例170 多列去重

案例171　中国式排名

案例及公式如下图所示。这里要根据A列中的分数进行中国式排名。

公式

="第"&SUM((MATCH($A$2:$A$6,$A$2:$A$6,0)=ROW($A$2:$A$6)-1)*($A$2:$A$6>=A2)) &"名"

公式解释

如上图所示，如果表中有两个90分，则其排名为并列第1名，这就是中国式排名。而美国式排名就不一样了，如果有两个90分，则一个是第1名，另一个只能是第2名。

根据MATCH函数的特性，在MATCH($A$2:$A$6,$A$2:$A$6,0)=ROW($A$2:$A$6)-1中，如果字符重复出现，就将其第1次出现的位置与其行号ROW($A$2:$A$6)-1进行对比，如果相等而且$A$2:$A$6>=A2（A2:A6单元格区域中的值大于或等于A2单元格中的值）条件也成立，就返回1，否则就返回0，然后再用SUM函数求和。

本案例视频文件：03/案例171 中国式排名

案例172　美国式排名

案例及公式如下图所示。这里要根据A列中的分数进行美国式排名。

公式

="第"&SUM(N(($A$2:$A$6+ROW($A$2:$A$6)*0.01)>=(ROW(A2)*0.01+A2)))&"名"

公式解释

($A$2:$A$6+ROW($A$2:$A$6)*0.01)用于对行号进行加权。由于在本案例中，当分数相同时不能并列取名次，所以这里让相同的分数变成不相同的分数，即给它们加上自己的行号乘以0.01的积。

同时，(ROW(A2)*0.01+A2)用于给每一个单元格中的值加同样的权重，然后进行判断。

为什么还要使用N函数？使用N函数用于把TRUE转为1，把FASLE转为0，然后再用SUM函数求和。

本案例视频文件：03/案例172 美国式排名

案例173　多工作表汇总

案例及公式如下图所示。这里要根据A列中的值，汇总“1月”至“3月”表中对应的数量。

公式

=SUM(SUMIF(INDIRECT("'"&{"1月","2月","3月"}&"'!A:A"),A2,INDIRECT("'"&{"1月","2月","3月"}&"'!B:B")))

公式解释

SUMIF函数在此处起两个作用：一是把每一个工作表中的姓名汇总，二是降维。

在此公式中，第1个INDIRECT函数返回每一个工作表的A列，第2个INDIRECT函数返回每一个工作表的B列。

为什么最后还要用SUM函数求和？因为要把每一个工作表中的姓名对应的数量汇总。

本案例视频文件：03/案例173 多工作表汇总

案例174　目录制作

案例及公式如下图所示。这里要在A列中显示月份目录。

公式

=HYPERLINK("#'"&IFERROR(MID(INDEX(AllSheet,ROW(A1)),FIND("]",INDEX(AllSheet,ROW(A1)))+1,99),"")&"'!A1",IFERROR(MID(INDEX(AllSheet,ROW(A1)),FIND("]", INDEX(AllSheet,ROW(A1)))+1,99),""))

公式解释

AllSheet是定义的名称。按快捷键Ctrl+F3可以打开“名称”对话框，在其中可以将单元格名称定义为AllSheet；在“引用位置”文本框中输入宏表函数=GET.WORKBOOK(1)。这个宏表函数的作用就是返回当前工作簿路径、工作簿名称的扩展名及所有工作表名。

通过INDEX函数引用每一个工表名。

通过FIND函数找到“]”的位置，再加上1，作为MID函数的第2参数，也就是把前面的路径和工作簿名称的扩展名去掉，包括“]”。

这里的IFERROR函数是为了屏蔽错误值，在向下填充公式时，没有工作表名就会报错（举个例子，一个工作簿中只有6个工作表，把公式向下填充到第7个单元格时就会报错，因为没有第7个工作表）。

HYPERLINK函数有两个参数，第1参数为#+单引号+工作表名+单引号+!+A1；第2参数是表名；另外，每一个参数都是文本，记得要加双引号。

另外，一定要将工作簿另存为启用宏工作簿，否则会报错，因为GET.WORKBOOK是宏表函数。

本案例视频文件：03/案例174 目录制作

案例175　VLOOKUP函数的第1参数数组用法

案例及公式如下图所示。计算“曹丽”和“小老鼠”对应的数量之和。

公式

=SUM(VLOOKUP(T(IF({1},A2:A3)),A:B,2,0))

公式解释

VLOOKUP函数的第1参数不能直接引用两个及两个以上单元格中的值，要通过T+IF函数转换一下。

本案例视频文件：03/案例175 VLOOKUP函数的第1参数数组用法

案例176　计算体积

案例及公式如下图所示。这里要根据A列中的数值，计算出对应的体积。

公式

=PRODUCT(--TRIM(MID(SUBSTITUTE(A2,"*",REPT(" ",99)),ROW($1:$3)*99-98,99)))

公式解释

REPT(" ",99)：产生99个空格。

SUBSTITUTE(A2,"*",REPT(" ",99))：把*替换成99个空格。

MID(SUBSTITUTE(A2,"*",REPT(" ",99)),ROW($1:$3)*99-98,99)：把每个数据提取出来。

TRIM函数用于去掉数据前后的空格。

为什么还要加“- -”，因为使用MID函数得到的是文本型数据。

最后用PRODUCT函数把MID函数提取出来的3个数值相乘。

本案例视频文件：03/案例176 计算体积

案例177　把9*20*30中的数字分别提取到3个单元格中

案例及公式如下图所示。这里要将A列中的值分别提取到3个单元格中。

公式

=TRIM(MID(SUBSTITUTE($A2,"*",REPT(" ",99)),COLUMN(A1)*99-98,99))

公式解释

本案例和案例176差不多，区别在于MID函数的第2参数。在176案例中用了数组，这里没有。将=COLUMN (A1)*99-98公式向右填充会产生1,100,199,…等差数列。

本案例视频文件：03/案例177 把9*20*30中的数字分别提取到3个单元格中

案例178　将多列转为一列

案例及公式如下图所示。这里要将单元格区域A1:D3中的值转为一列显示。

公式

=INDEX($A$1:$D$3,MOD(ROW(A3),3)+1,INT(ROW(A3)/3))

公式解释

此案例不是数组公式，为什么放在这里介绍？这是为了和下面的案例179放在一起介绍。

将MOD(ROW(A3),3)+1公式向下填充会产生循环的{1;2;3}数组。

将INT(ROW(A3)/3)公式向下填充会产生循环的{1;1;1;2;2;2;3;3;3}数组。

本案例视频文件：03/案例178 将多列转为一列

案例179　将一列转为多列

案例及公式如下图所示。这里要将A列中的值显示在C2:F4单元格区域中。

公式

=LOOKUP(COLUMN(A1:D1)+ROW(A1:A3)*4-4,IF({1,0},ROW(A1:A12),A1:A12))

公式解释

此公式是数组公式。

COLUMN(A1:D1)+ROW(A1:A3)*4-4：作为LOOKUP函数的第1参数，向右填充或者向下填充公式时会产生数组{1,2,3,4;5,6,7,8;9;10;11;12}。

IF({1,0},ROW(A1:A12),A1:A12)：构建了两列多行的数据作为LOOKUP函数的第2参数。

当然也可以用公式=INDEX($A$1:$A$12,COLUMN(A1)+ROW(A1)*4-4)得到相同的结果，而不使用数组公式。

本案例视频文件：03/案例179 将一列转为多列

案例180　用全称匹配简称

案例及公式如下图所示。这里要根据A列中的全称，在F列中找到对应的简称，并显示在B列中。

公式

=LOOKUP(1,0/FIND($F$1:$F$6,A1),$F$1:$F$6)

公式解释

0/FIND($F$1:$F$6,A1)：这里的FIND函数的第1参数为数组，如果在单元格区域F1:F6中找到对应的数值就返回一个数值，如果没有找到就会报错（0除以错误值还是报错，0除以数值返回0）。根据LOOKUP函数的特点，如果查找值大于第2参数的最大值，就定位最后一个数值的位置，返回第3参数对应的值。

此公式不按快捷键Ctrl+Shift+Enter也有效，这也是LOOKUP函数的一个特点。

本案例视频文件：03/案例180 用全称匹配简称

案例181　用简称匹配全称

案例及公式如下图所示。这里要根据A列中的简称，在E列中找到对应的全称，并显示在B列中。

公式

=LOOKUP(1,0/FIND(A1,$E$1:$E$6),$E$1:$E$6)

公式解释

0/FIND(A1,$E$1:$E$6)：FIND函数的第2参数为数组，如果在单元格区域E1:E6中找到对应的数值就返回数值，如果没有找到就报错（0除以错误值还是报错，0除以数值返回0）。根据LOOKUP函数的特点，如果查找值大于第2参数的最大值，就定位最后一个数值的位置，返回第3参数对应的值。

此公式不按快捷键Ctrl+Shift+Enter也有效。

本案例视频文件：03/案例181 用简称匹配全称

案例182　使用LOOKUP函数实现多条件查找

案例及公式如下图所示。这里要根据产品名称和型号计算对应的数量之和。

公式

=LOOKUP(1,0/((A$2:A$5=E2)*(B2:B5=F2)),C2:C5)

公式解释

((A$2:A$5=E2)*(B2:B5=F2))：同时满足这两个条件（A$2:A$5=E2和B2:B5=F2）就返回TRUE，反之则返回FALSE。然后用0除以结果：0除以FALSE会报错，0除以TURE会返回0。根据LOOKUP函数的这个特点，如果查找值大于第2参数的最大值，就定位最后一个数值的位置，返回第3参数对应的值。

本案例视频文件：03/案例182使用LOOKUP函数实现多条件查找

案例183　对合并单元格按条件求和

案例及公式如下图所示。这里要对每个人的数量进行求和，并放在一个单元格中。

公式

=SUM(IF(LOOKUP(ROW($A$1:$A$9),IF($A$1:$A$9<>"",ROW($A$1:$A$9)),$A$1:$A$9)=D1,$B$1:$B$9,0))

公式解释

此公式的LOOKUP函数的第1参数用得巧妙，如果单元格区域A1:A9不等于空，就显示它们的行号，否则就显示FALSE。由于FALSE在LOOKUP函数中不参加计算，且第2参数是升序排序，查找值1和查找值2全部定位到行号1的位置，查找值3至查找值6全都定位到行号3的位置，查找值7至查找值9全部定位到行号7的位置，最后相当于在A列中把合并单元格填充了，也就是构建了没有合并单元格的效果。

LOOKUP(ROW($A$1:$A$9),IF($A$1:$A$9<>"",ROW($A$1:$A$9)),$A$1:$A$9)=D1：如果等于“小老鼠”就显示B列的数据，否则就返回0，最后用SUM函数求和。

本案例视频文件：03/案例183 对合并单元格按条件求和

案例184　查找姓名最后一次出现的位置对应的数量

案例及公式如下图所示。这里要根据姓名，查找其最后一次出现的位置对应的数量。

公式

=LOOKUP(1,0/(A2:A8=D2),B2:B8)

公式解释

0/(A2:A8=D2)：如果单元格区域A2:A8中有等于“曹丽”的值，就返回TRUE。0除以TRUE等于0，0除以FALSE会报错，这样就构建一个由错误值和0组成的一维数组。根据LOOKUP函数的特点，如果查找值大于第2参数的最大值，就定位最后一个数字的位置，然后返回第3参数对应的值。

本案例视频文件：03/案例184查找姓名最后一次出现的位置对应的数量

案例185　双条件计数

案例及公式如下图所示。这里要查找大号的产品A共有几条记录。

公式

=SUM((A1:A8="A")*(B1:B8="大号"))

公式解释

在此公式中，如果公式中的两个条件都满足就返回1，否则就返回0，最后用SUM函数求和。

当然，如果你的Excel版本是2007版以上，那么也可以用COUNTIF函数实现：=COUNTIFS (A:A,"A",B:B,"大号")。

本案例视频文件：03/案例185 双条件计数

案例186　如何生成序列

案例及公式如下图所示。这里要生成1~9的数列，并放在A1:C3单元格区域中。

公式

=COLUMN(A1:C1)+ROW(A1:A3)*3-3

公式解释

COLUMN(A1:C1)：横向产生一维数组{1,2,3}，然后加上ROW(A1:A3)*3-3。

ROW(A1:A3)*3-3：返回{0;3;6}。

当然，此效果也可以用普通公式=COLUMN(A1)+ROW(A1)*3-3实现。

本案例视频文件：03/案例186 如何生成序列

案例187　引用合并单元格中的值

案例及公式如下图所示。这里要引用A列单元格中的值，并将值放在单独的单元格中。

公式

=LOOKUP(1,0/($A$1:A1<>""),$A$1:A1)

公式解释

这个公式的巧妙之处在于$A$1:A1，即锁住单元格区域的起始部分，这样单元格区域变为动态的，可以不断扩展。

0/($A$1:A1<>"")：如果单元格区域A1:A1中的值不等于空就返回TRUE。0除以TRUE返回0，0除以FALSE会报错。根据LOOKUP函数的这个特点，如果查找值大于第2参数的最大值，就定位最后这个值的位置，返回第3参数对应的值。

本案例视频文件：03/案例187 引用合并单元格中的值

案例188　从汉字中提取数字

案例及公式如下图所示。这里要从A列中提取数字。

公式

=LEFT(A2,COUNT(-LEFT(A2,ROW($1:$30))))

公式解释

-LEFT(A2,ROW($1:$30))：从A2单元格左边提取数字。LEFT函数的第2参数为数组，从左边提取1个数字是“3”，提取2个数字是“32”，提取3个数字是“32克”，这里包含“克”了，前面的“32”是文本型数字，加一个负号，就把文本型数字转为数值型数字。然后用COUNT函数统计数值型数字的个数，将2作为LEFT函数的第2参数。

本案例视频文件：03/案例188 从汉字中提取数字

案例189　将通话记录里的分和秒相加

案例及公式如下图所示。这里要将C列中的值相加，并显示为h:m:s格式。

公式

=TEXT(SUM(--TEXT(({"0时","0时0分"}&C2:C9),"h:m:s;-0;0;!0")),"[m]:s")

公式解释

这里的TEXT函数的第1参数为数组。

由于在C列的通话时长数据中有的有分和秒数据，有的只有秒数据，所以，这里统一在前面加上{"0时","0时0分"}。这样就得到我们需要的格式：显示时、分、秒，而不是文本格式。TEXT函数的第2参数"h:m:s;-0;0;!0"的第4节就是用“！”强制将文本显示为0，再用SUM函数求和。

（提示：TEXT函数的第2参数的自定义单元格格式分为4节，第1节为正数；第2节为负数；第3节为零；第4节为真正的文本，中间用分号分隔。）

另外，"[m]:s"用于把小时格式强制显示为分和秒格式。

本案例视频文件：03/案例189 将通话记录里的分和秒相加

案例190　使用COUNTIF函数统计不连续列中的字符个数

案例及公式如下图所示。这里要统计A、C、E列中“小老鼠”的个数。

公式

=SUM(COUNTIF(OFFSET(A:A,0,{0,2,4}),A1))

公式解释

COUNTIF函数的第1参数不支持多区域联合，也不支持常量数组，所以，这里通过OFFSET函数的第1参数实现了支持多区域联合和常量数组效果。

OFFSET函数的第1参数为整个A列，第2参数为偏移行数（不偏移），第3参数为偏移列数（用了数组）。由于第3参数偏移了3列，所以返回3个结果，最后用SUM函数求和。

本案例视频文件：03/案例190 使用COUNTIF函数统计不连续列中的字符个数

案例191　使用COUNTIF函数统计不重复值的个数

案例及公式如下图所示。这里要统计A列中不重复值的个数。

公式

=SUM(1/COUNTIF(A2:A6,A2:A6))

公式解释

COUNTIF函数的第2参数为区域数组，COUNTIF(A2:A6,A2:A6)返回5个结果：{2;2;1;2;2}，然后用1除以它们。例如，假如有两个“曹丽”就返回两个2，用1除以两个2，得到两个1/2；假如有30个“曹丽”，就返回30个1/30，30个1/30相加还是1。

本案例视频文件：03/案例191 使用COUNTIF函数统计不重复值的个数

案例192　使用COUNT函数统计不重复值的个数且排除空单元格

案例及公式如下图所示。这里要统计A列中不重复值个数且排除空单元格。

公式

=COUNT(0/(MATCH(A2:A8,A2:A8,0)=ROW(A2:A8)-1))

公式解释

MATCH(A2:A8,A2:A8,0)=ROW(A2:A8)-1：在使用MATCH函数查找值时，如果查找值第1次出现，则显示其本身的位置，如果重复出现，则只显示第1次出现的位置，不会显示其本身的位置。然后与ROW(A2:A8)-1对比，由于ROW(A2:A8)返回{2;3;4;5;6;7;8}，而MATCH函数查找的位置是从1开始的，所以要减1。MATCH函数在查找值时，遇到空单元格会报错，不影响COUNT函数统计数值型数字。

之后用0除以结果，0/TRUE返回TRUE，0/FALSE报错，0/错误值还是返回错误值。

本案例视频文件：03/案例192 使用COUNT函数统计不重复值的个数且排除空单元格