2.6　初级函数综合案例

案例110　使用TEXT函数把秒数转换为分钟数

解法1：用TEXT函数+86400实现

案例及公式如下图所示。这里要将A列中的秒数转换为分钟数。

公式

=TEXT(A1/86400,"mm分ss秒")

公式解释

A1单元格中是秒数，现在要把秒数转换成分钟数。1天的数值是1，1天是24小时，1小时是60分钟，1分钟是60秒，所以=1/（1*24*60*60）计算的是1秒的数值，即1/86400是1秒的数值。

A1/86400得到A1单元格中的时间数值，最后用TEXT函数把数值显示成分和秒。

解法2：用TEXT+TIME函数实现

案例及公式如下图所示。

公式

=TEXT(TIME(,,A1),"mm分ss秒")，完整的公式为=TEXT(TIME(0,0,A1),"mm分ss秒")

公式解释

TIME函数用于返回时间数值，它有3个参数，第1参数：小时数；第2参数：分钟数；第3参数：秒数。

通过TIME(,,A1)会得到A1单元格中的时间数值。最后用TEXT函数显示我们要求的格式。

解法3：用INT+MOD实现

案例及公式如下图所示。

公式

=INT(A1/60)&"分"&MOD(A1,60)&"秒"

公式解释

INT(A1/60)：用秒数除以60，再用INT函数取整得到分钟数。

MOD(A1,60)：这里使用取余函数MOD，被除数是秒数，除数是60，得到秒数。

然后将结果用&符号连接。

本案例视频文件：02/案例110 使用TEXT函数把秒数转换为分钟数

案例111　为什么使用SUMIF函数的求和结果是0

案例如下图所示，其中F1单元格里的“>70%”是条件，C列是条件所在的区域，B列是求和区域，使用公式=SUMIF(C:C,F1,B:B)，为什么得出的结果是0？

原因分析

由于C列中的数值是文本型，故不能被当作数值进行运算，而这里的SUMIF函数的第2参数（F1）被当作判断条件，即判断>70%的数值，而C列中全是文本型数值，所以得出的结果是0。

解决方法1：在条件前加一个“=”。

公式如下图所示。

公式

=SUMIF(C:C,"="&F1,B:B)

公式解释

此公式在第2参数前加了一个“=”，这样条件就是等于“>70%”，这个“>70%”就是文本了，不是比较运算了。

解决方法2：在条件前加一个“*”

公式如下图所示。

公式

=SUMIF(C:C,"*"&F1,B:B)

公式解释

这里在第2参数前加了一个通配符“*”，表示是查找以“>70%”结尾的数值，这样处理之后第2参数就变成了文本。

解决方法3：用SUMPRODUCT函数实现

案例及公式如下图所示。

公式

=SUMPRODUCT(($C$1:$C$10=F1)*($B$1:$B$10))

公式解释

如果单元格区域C1:C10中的数值等于“>70%”，那么就乘以单元格区域B1:B10中对应位置的数值，再相加。

本案例视频文件：02/案例111 为什么使用SUMIF函数的求和结果是0

案例112　对比两张表中的数据

在此案例中，要在2月工作表里用函数实现此功能：如果2月工作表中的产品名称在1月工作表里有，就引用2月工作表里的C列对应的“金额”数据，如果没有就显示“1月没有”，效果如下图所示。

解法1：用TEXT+SUMPRODUCT函数实现

公式如下图所示。

公式

=TEXT(SUMPRODUCT(('1月'!$A$2:$A$5='2月'!A2)*('1月'!$B$2:$B$5='2月'!B2)*('1月'!$C$2:$C$5))," [=0]1月没有")

公式解释

如果1月工作表里的单元格区域A2:A5中的值等于2月工作表中A2单元格中的值，而且1月工作表中的单元格区域B2:B5等于2月工作表中B2单元格中的值，即这两个条件都满足，就乘以1月工作表里对应的单元格区域C2:C5中的值。

再使用SUMPRODUCT函数，如果上面两个条件成立就返回C列对应的值，否则就返回0。

TEXT函数的第2参数为"[=0]1月没有"，如果返回0就显示“1月没有”，其他的全部是“G/通用格式＂。

解法2：用IFERROR+LOOKUP函数实现

公式如下图所示。

公式

=IFERROR(LOOKUP(1,0/(('1月'!$A$2:$A$5='2月'!A2)*('1月'!$B$2:$B$5='2月'!B2)),'1月'!$C$2: $C$5),"1月没有")

公式解释

如果1月工作表的单元格区域A2:A5中的值等于2月工作表的A2单元格中的值，而且1月工作表的单元格区域B2:B5中的值等于2月工作表的B2单元格中的值，即这两个条件都满足就返回1，不满足就返回0，然后用0除以1会返回0，0除以0会报错。

根据LOOKUP函数的这个特点，如果第1参数大于第2参数中的任意一个值，就定位第2参数最后一个数值的位置，然后返回LOOKUP函数的第3参数的值。

解法3：用TEXT+SUMIFS函数实现

公式如下图所示。

公式

=TEXT(SUMIFS('1月'!$C$2:$C$5,'1月'!$A$2:$A$5,'2月'!A2,'1月'!$B$2:$B$5,'2月'!B2),"[=0]1月没有")

公式解释

由于1月工作表的A列和B列中的数据连接起来具有唯一性，不会重复，这里就可以用SUMIFS函数实现查询的作用。

SUMIFS函数的作用是多条件求和，它的参数不确定，当有一个条件时有3个参数；当有两个条件时有5个参数；当有3个条件时有7个参数……而这里用了两个条件，所以有5个参数，第1参数是求和的区域；第2参数为条件1所在的区域；第3参数为条件1；第4参数为条件2所在的区域；第5参数为条件2。

TEXT函数的第2参数为“[=0]1月没有”，如果返回值等于0就显示“1月没有”，其他的全部是“G/通用格式"”。

本案例视频文件：02/案例112 对比两张表中的数据

案例113　判断奇偶行的两种方法

方法1：用MOD函数取余数

案例及公式如下图所示。这里要对B列的奇数行和偶数行进行分类。奇数行显示为1，偶数行显示为0。

公式

=MOD(ROW(A1),2)

公式解释

将公式ROW(A1)向下填充会产生数列1,2,3,4,5,…

1除以2的余数是1。

2除以2的余数是0。

3除以2的余数是1。

4除以2的余数是0。

依此类推，也就是说，如果余数是1，则说明是奇数行，如果余数是0，则说明是偶数行。

方法2：使用判断奇偶性函数

公式如下图所示。

公式

=ISODD(ROW(A1))

=ISEVEN(ROW(A1))

公式解释

ISODD函数用于判断数值，如果是奇数就返回TRUE，否则就返回FALSE。

ISEVEN函数用于判断数值，如果是奇数就返回FALSE，否则就返回TRUE。

本案例视频文件：02/案例113 判断奇偶行的两种方法

案例114　使用SUMIF函数遇到通配符时如何解决

案例及公式如下图所示。这里求规格为“20*2.0Mpa”的产品的数量汇总，结果本应该是5，现在返回是12，为什么？

原因就出在SUMIF函数的第2参数支持通配符。

因为查找条件为“20*2.0MPa”，SUMIF函数会自动把“*”当作通配符，也就是说，只要以“20”开头的值都符合条件，这样就把A列中两个以“200”开头的值也包括了，所以得到的答案是12，正确结果应该是5。

方法1：使用SUBSTITUTE函数

公式如下图所示。

公式

=SUMIF(A:A,SUBSTITUTE(D2,"*","~*"),B:B)

公式解释

SUBSTITUTE(D2,"*","~*")：把“*”替换成“~*”，这样处理之后第2参数里的“*”就不是通配符了，变成普通字符了。

这里的“～”相当于转义符，即把通配符转换为普通的字符。

方法2：使用SUMPRODUCT函数

公式如下图所示。

公式

=SUMPRODUCT((A2:A10=D2)*(B2:B10))

公式解释

如果单元格区域A2:A10中的值等于20*2.0MPa就返回TRUE，否则就返回FALSE；然后再和数量列（B2:B10）中对应的值相乘，最后求总数量。

在运算的过程中，TRUE被当作1，FALSE被当作0。

本案例视频文件：02/案例114 使用SUMIF函数遇到通配符时如何解决

案例115　提取单元格中靠左侧的汉字

案例及公式如下图所示，提取单元格区域A2:A4中靠左侧的汉字，并放到单元格区域B2:B4中。

公式

=LEFT(A2,SEARCHB("?",A2)/2)

公式解释

SEARCHB("?",A2)：找到第1个单字节出现的位置，不过这个SEARCHB函数是区分单/双字节的。其中一个汉字算2个字节，一个字母算1个字节。由于单元格中左侧的值全是汉字，所以要除以2。

这里的“?”代表任意一个单字节。

最后用LEFT函数从单元格左侧提取。

本案例视频文件：02/案例115提取单元格中靠左侧的汉字

案例116　从汉字中提取数字的最简单的方法

案例及公式如下图所示。

公式

=MIDB(A2,SEARCHB("?",A2),2*LEN(A2)-LENB(A2))

公式解释

=SEARCHB("?",A2)：找到第一个数字出现的位置。SEARCHB函数是区分单/双字节的，一个汉字算2个字节，一个数字算1个字节。其中第1参数为“？”，表示任意一个单字节。

由于SEARCHB函数区分单/双字节，所以从文本中间提取字节只能用MIDB函数，不能用MID函数。

2*LEN(A2)-LENB(A2)：得到数字的个数。可以这样理解此公式：把所有字符都当作汉字，也就是将“吃饭129元”全部当作汉字（共6个汉字），即6*2=12个字符。而LENB("吃饭129元")将汉字的字节数放大1倍，而数字的字节数没有被放大，因此，不管有多少汉字，都是被抵消，而数字则不是。2*LEN(A2)将数字和汉字的字节数都放大了2倍，后面的-LENB(A2)没有将数字的字节数放大，而将汉字的字节数放大了2倍，因此2*LEN(A2)-LENB(A)得到数字的个数。

本案例视频文件：02/案例116 从汉字中提取数字的最简单的方法

案例117　简单的数值相加为什么会报错

案例及公式如下图所示。D1单元中的值等于A1、B1、C1这3个单元格中的数值相加，为什么会报错呢？

原因分析：这是由于A1单元格中是“假空”的。有时我们在写公式时，会让单元格显示为空，即""，IFERROR函数的第2参数有时也会让单元格公式的值显示为""，我们称这种为“假空”，如果单元格里什么符号都没有，才称之为真空，如下图所示。

解决方法：补前导0法

公式如下图所示。

公式

=(0&A1)+B1+C1

公式解释

如果我们要进行数值计算，那么在用IFERROR函数的第2参数时不要显示为假空""，要让它显示为0。

如果一定要A1单元格显示为假空，那么可以在D1单元格的公式中加一个前导0进行处理。

如果数据很多，则可以用数组公式{=SUM(--(0&A1:C1))}，如下图所示。

本案例视频文件：02/案例117 简单的数值相加为什么会报错

案例118　使用LOOKUP函数实现反向查找

案例及公式如下图所示。

方法1：用LOOKUP函数可以根据姓名反向查找工号

公式

=LOOKUP(1,0/(B2:B4=D2),A2:A4)

公式解释

判断单元格区域B2:B4中的值有没有等于“曹丽”的，如果有就返回TRUE，否则就返回FALSE。0/TRUE返回0，0/FALSE返回报错信息。

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

方法2：用INDEX+MATCH函数实现

公式如下图所示。

公式

=INDEX(A:A,MATCH(D2,B:B,0))

公式解释

MATCH函数有3个参数，第1参数：查找值；第2参数：数据源（但是一定是一维引用或者是一维数组）；第3参数：0，表示精确查找，也就是查找值和数据源里的值要一样，否则就会报错。

先用MATCH函数查找“曹丽”在B列中的位置，结果返回2。

此INDEX函数中有3个参数，第1参数：数据源；第2参数：返回数据源的哪一行；第3参数：返回数据源的哪一列。

本案例视频文件：02/案例118 使用LOOKUP函数实现反向查找

案例119　比IF函数还经典的判断用法

第1组公式：如果A列中的值是“是”则显示1，是“否”则显示0

IF函数的用法如下图所示。

其他函数的用法如下图所示。

公式解释

这里使用IF函数判断A列中的值。如果A1单元格中的值等于“是”，就返回TRUE，否则返回FALSE。而使用N函数则是把TRUE转换为1，把FALSE转换为0。

第2组公式：如果A列中的值大于100就显示100，否则就显示数值本身

IF函数的用法如下图所示。

其他函数的用法如下图所示。

公式解释

=MIN(A1,100)：如果A1单元格中的值大于100，那么100就是最小值，如果小于100，那么A1单元格中的值就是最小值。

第3组公式：判断A列中的分数，如果小于60则返回“不及格”；如果大于或等于60且小于70则返回“及格”；如果大于或等于70且小于80则返回“良好”；如果大于或等于80则返回“优秀”

IF函数的用法如下图所示。

其他函数的用法如下图所示。

公式解释

LOOKUP函数的第2参数的第1列数据要求升序排序，根据LOOKUP函数的特点，如果在第2参数的第1列数据中没有找到和查找值一样的，就找比查找值小的，然后从这些小的值里面找到最大那个值所在的位置，返回第2参数最后一列对应的值。

第4组公式：如果A列中的值是数字，则显示数字本身，如果是非数字，则显示0

IF函数用法如下图所示。

其他函数用法如下图所示。

公式解释

=TEXT(A1,"0;;;!0")：TEXT函数的第2参数为自定义单元格格式，分为4节，第1节为正数；第2节为负数；第3节为零；第4节为文本，中间用分号分隔。这里的第4节是文本，为“!0”，即强制第4节的文本显示为0。当然，如果A列出现负数和零，则要将公式修改为=Text(A1,”0;-0;0;!0”)。

本案例视频文件：02/案例119比IF函数还经典的判断用法

案例120　引用每个表的C列中的最后一个值

案例及公式如下图所示。在汇总表的B列中引用对应的表的C列中的最后一个值，且是动态的。

此案例包括1张汇总表和3个分表，如下图所示。

公式如下图所示。

公式

=LOOKUP(9E+307,INDIRECT("'"&A2&"'!C:C"))

公式解释

"'"&A2&"'!C:C"：用一串字符表示每一个表的C列。为什么要给工作表名加一对单引号？如果表名中有一些特殊字符，则公式会报错，这里加单引号是为了容错。

"'"&A2&"'!C:C"：这是一串普通的字符串，加上函数INDIRECT之后表示定位A2单元格中的值“曹丽”对应的工作表的C列了。这个就是INDIRECT函数强大的作用，INDIRECT函数可以返回单元格区域。

LOOKUP函数的第1参数为查找值9E+307，它是Excel中的最大数值，如果LOOKUP函数的第1参数大于第2参数的所有值，就会返回第2参数中的最后一个值。

本案例视频文件：02/案例120 引用每个表的C列中的最后一个值

案例121　将单元格中的内容进行分列

这里需要把A列中的数字和汉字分开，分别放到B列和C列中，如下图所示。

公式如下图所示。

公式

=IF(COLUMN(A1)=1,LEFT($A2,2*LEN($A2)-LENB($A2)),RIGHT($A2,LENB($A2)-LEN($A2)))

公式解释

2*LEN($A2)-LENB($A2)：得到A2单元格中数字的个数。

LENB($A2)-LEN($A2)：得到A2单元格中汉字的个数。

如果COLUMN(A1)=1，则返回TRUE。将公式向右填充时，A1会变成B1，COLUMN(B1)返回2，所以COLUMN(B1)=1就返回FALSE。这样IF函数向右分别执行两个公式，第一个公式为LEFT($A2,2*LEN($A2)-LENB($A2)); 第2个公式为RIGHT($A2,LENB($A2)-LEN($A2))。

本案例视频文件：02/案例121 将单元格中的内容进行分列

案例122　求18:00—23:00有几个小时

案例及公式如下图所示。这里求18:00—23:00有几个小时。

公式

=(MID(A1,FIND("-",A1)+1,99)-LEFT(A1,FIND("-",A1)-1))*24

公式解释

FIND("-",A1)+1：这里先用FIND函数找到“-”的位置，然后加1作为MID函数的第2参数提取的位置。

FIND("-",A1)-1：同理，用FIND函数找到“-”的位置，然后减1作为LEFT函数的第2参数提取的位置，得到“-”前面的数据。

将两个FIND函数得到的数据相减再乘以24，就得到我们想要的结果，因为两个数据相减得到的是时间数值，所以还要乘以24。

本案例视频文件：02/案例122 求18:00—23:00有几个小时

案例123　为什么使用SUM函数无法求和

案例及公式如下图所示。这里求A列的数字之和，将结果放在C1单元格中，为什么结果是0？

原因分析：

这是初学者常会遇到的问题。数字一般分为数值型数字和文本型数字。在单元格中，数值型数字为右对齐，文本型数字为左对齐。文本型数字没有大小之分，如果对其求和则结果等于0。

可以将上图中的公式修改一下，如下图所示。此时就会显示正确的结果。

公式

=SUM(--A:A)

公式解释

在文本型数字“100”前面加一个负号变成了-100，在-100前面再加一个负号变成100，而这个100就是数值型数字。--是减负运算符号。

此公式是数组公式，记得要把鼠标光标定位到编辑栏中，然后按Ctrl+Shift+Enter键。

本案例视频文件：02/案例123为什么使用SUM函数无法求和

案例124　提取小括号里的数据

方法1：使用MID+3个FIND函数

案例及公式如下图所示。这里要提取A列单元格中括号里的内容。

公式

=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)

公式解释

FIND("(",A2)+1：找到“（”符号的位置，然后加1作为MID函数的第2参数。

FIND(")",A2)-FIND("(",A2)-1：用“）”符号的位置减去“（”符号的位置再减1，得到MID函数的第3参数，即提取多少字符。这种解法最简单，而且容易理解。

方法2：拉大距离法

公式如下图所示。

公式

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,")","("),"(",REPT(" ",99)),100,99))

公式解释

先把“）”符号替换成“（”符号，然后用替换函数把“（”符号替换成99个空格，目的是让括号里的数据和括号外的数据分开。最后用MID函数从第100个位置开始提取，提取99个字符，再用TRIM去掉前后的空格。

方法3：使用REPLACE+FIND函数

公式如下图所示。

公式

=REPLACE(LEFT(A2,FIND(")",A2)-1),1,FIND("(",A2),)

公式解释

LEFT(A2,FIND(")",A2)-1)：找到“）”符号的位置后减1，得到的结果作为LEFT函数的第2参数，这样就把“）”符号后面的数据（包括“）”符号）去掉了。去掉之后的数据作为REPLACE函数的第1参数，即要处理的数据。

REPLACE函数的第2参数为从哪个位置开始替换，这里要从第1个位置开始，所以第2参数为1。第3参数为替换第几个，用FIND("(",A2)找到“（”符号的位置，包括“（”符号的替换的个数。第4参数为要替换成空值，这里简写了，只输入了一个逗号。

本案例视频文件：02/案例124 提取小括号里的数据

案例125　每隔4行提取数据组成新的一列

案例及公式如下图所示。这里在A列中每隔4行提取数据组成新的一列。

公式

=INDEX(A:A,ROW(A1)*4-3)

公式解释

ROW(A1)：返回1，向下填充公式产生数列2，3，4，…

ROW(A1)*4-3：返回1，向下填充公式产生数列5，9，13，…

INDEX函数有3个参数，第1参数：数据源；第2参数：返回数据源的哪一行；第3参数：返回数据源的哪一列。如果数据只有一行或者只有一列，那么只用两个参数就可以了。

本案例视频文件：02/案例125 每隔4行提取数据组成新的一列

案例126　最简单的分类汇总方法

情况一：有空行的情况

案例及公式如下图所示。这里要对送货单号分别进行汇总。

公式

=IF(B2="",SUMIF(A:A,A1,B:B),"")

公式解释

如果B2单元格中为空值，就计算公式SUMIF(A:A,A1,B:B)，否则就显示为空值。

SUMIF函数是按条件求和，它有3个参数，第1参数：条件所在的区域；第2参数：条件；第3参数求和区域。

情况二：没有空行的情况。

案例及公式如下图所示。

公式

=IF(A2<>A3,SUMIF(A:A,A2,B:B),"")

公式解释

如果A2<>A3，就计算公式SUMIF(A:A,A2,B:B)，否则就显示为空值。

本案例视频文件：02/案例126 最简单的分类汇总方法

案例127　判断汉字和字母

方法1：使用IF和CODE函数

案例及公式如下图所示。

公式

=IF(CODE(A2)>122,"汉字","字母")

公式解释

CODE函数用于返回每个符号的ASCII码。这是因为每个字符都可以用一个数字代替，而小写字母z对应的数字编码是122。

方法2：使用IF和多个函数组合

案例及公式如下图所示。

公式

=IF(COUNT(N(INDIRECT(A2&10000))),"字母","汉字")

公式解释

A2&10000：A2连接10000得到A10000，构建了一个表示单元格的文本字符。经过INDIRECT函数处理就变成一个单元格，而这个A10000单元格中的内容是空值，所以返回0。然后用N函数降维，再用COUNT函数统计数值型数字的个数，此时错误值会被忽略。

方法3：使用TF函数

案例及公式如下图所示。

公式

=IF(A2>="吖","汉字","字母")

公式解释

“吖”是汉字表中第一个汉字。

当然也可以用公式=IF(A2<="z","字母","汉字")。

本案例视频文件：02/案例127 判断汉字和字母

案例128　使用VLOOKUP函数实现多表查找

在表1中，张三对应的数量是400。

在表2中，小老鼠对应的数量是100。

在总表中，我们想要的结果是查找到小老鼠对应的数量100，张三对应的数量400，如下图所示。

这里使用的公式如下图所示。

公式

=IFERROR(VLOOKUP(A2,表1!$A$1:$B$4,2,0),"")&IFERROR(VLOOKUP(A2,表2!$A$1:$B$4,2, 0),"")

公式解释

先在表中查找小老鼠（A2）对应的数量，如果找不到就报错，这里用IFERROR函数将错误值屏蔽为空值，再用连字符“&”连接第2个VLOOKUP函数。

第2个VLOOKUP函数的查找值还是小老鼠（A2），在表2中找，如果找到就返回其对应的数量，如果找不到就报错。

总之，此公式在表1中找不到，就会在表2中找，如果在两个表中都找不到就返回空值。

本案例视频文件：02/案例128 用VLOOKUP函数实现多表查找

案例129　将一列数据快速转换为两列数据

案例及公式如下图所示。这里要将A列中的数据转为两列数据。

公式

=INDEX($A:$A,ROW(A1)*2-1+COLUMN(A1)-1)

公式解释

ROW(A1)*2-1：将公式向下填充产生数列1,3,5,…。

将公式向右填充1列，便在原来的值基础上加1，COLUMN(A1)-1返回0。向右填充公式后A1变成了B1， COLUMN(B1)-1返回1。因为COLUMN(B1)返回2，所以2-1=1。

这里的INDEX函数中有两个参数，如果第1参数是1列，则只用两个参数就可以了，第1参数为数据源，第2参数为引用数据源是哪个位置的。

本案例视频文件：02/案例129 将一列数据快速转换为两列数据

案例130　根据产品名返回最后一次进价

案例及公式如下图所示。这里根据B列的产品名，返回此产品的最后一次进价。

公式

=TEXT((COUNTIF($B$2:B2,B2)=COUNTIF(B:B,B2))*C2,"0.0;;;")

公式解释

COUNTIF($B$2:B2,B2)：这个公式是动态的，依次把不同的产品编号。COUNTIF(B:B,B2)：计算每一个产品的最大编号。如果这两个产品的编号相等，就乘以C列的进价并返回最大的进价，如果不相等就返回0。

TEXT函数：把0屏蔽，显示为空值，把最大进价留下。

本案例视频文件：02/案例130 根据产品名返回最后一次进价

案例131　提取最后一个月的数据

方法1：使用LOOKUP函数

案例及公式如下图所示。这里提取1月至6月中最后一个月（有数据的月）中的数据。

公式

=LOOKUP(9^9,A2:F2)

公式解释

根据LOOKUP函数的特点，如果查找值大于其第2参数的所有值，那么返回第2参数的最后一个数值。

方法2：使用INDEX和MATCH函数

案例及公式如下图所示。

公式

=INDEX(A2:F2,MATCH(9^9,A2:F2))

公式解释

MATCH函数的第3参数如果省略了或者用1，同时第1参数大于第2参数里的所有值，就返回第2参数里的最后一个值的位置，得到的这个位置会作为INDEX函数的第2参数。

INDEX函数有3个参数，第1参数：要引用的数据源；第2参数：引用数据源里的哪一行；第3参数：引用数据源里的哪一列。

如果INDEX函数的第1参数只有一行或者一列，那么INDEX函数只要有两个参数就可以了。

本案例视频文件：02/案例131 提取最后一个月的数据

案例132　为什么使用VLOOKUP函数得不到正确的结果

案例及公式如下图所示。这里使用VLOOKUP函数根据D2单元格中的商品编号返回对应的数量，结果却是错误，为什么？

由于A列中是文本型数字，而D2单元格中是数值型数字，所以使用此公式得不到正确结果。

解决方法1：在查找值后面连接&""（空文本）

公式如下图所示。

公式

=VLOOKUP(D2&"",A:B,2,0)

公式解释

在VLOOKUP函数的第1参数后面连接一对双引号，把数值型数字变成文本型数字，就和数据源里的数字格式相同了。

解法方法2：分列法

步骤1：选中A列，选择“数据”选项卡中的“分列”选项。

步骤2：弹出“分列”对话框，在其中直接单击“完成”按钮就可以了。此方法是通过分列把文本型数字转换为数值型数字，但前提条件是商品编号不能太长，如果太长就会变成科学记数法形式。那么此时又该如何解决呢？只能把D2单元格设置成文本格式了。

本案例视频文件：02/案例132 为什么使用VLOOKUP函数得不到正确的结果

案例133　为什么使用SUMPRODUCT函数得不到正确的结果

下面使用SUMPRODUCT函数求产品名称是A的数量之和，系统返回的结果是0（见左下图），为什么？

正确的公式如右下图所示。

公式

=SUMPRODUCT((A2:A5="A")*B2:B5)

公式解释

第一个公式为什么不对？因为SUMPRODUCT函数不直接支持逻辑值TRUE和FALSE运算，要通过一些运算进行转换才可以，所以把逗号改成乘号就可以了。

如果一定要用逗号呢？可以先把逻辑值转换一下：把TRUE转换为1，把FALSE转换为0，就可以正确计算了，公式为=SUMPRODUCT((A2:A5="A")*1,B2:B5)，如下图所示。

本案例视频文件：02/案例133 为什么使用SUMPRODUCT函数得不到正确结果

案例134　如何实现“六舍七入”

如下图所示，使用ROUND函数可以将1.6变成1，将1.7变成2，即实现“六舍七入”。

方法1：使用ROUND函数

公式如下图所示。

公式

=ROUND(A1-0.2,0)

公式解释

这里可以使用四舍五入函数ROUND来实现“六舍七入”。

因为0.7-0.2=0.5，0.6-0.2=0.4，这样就可以使用四舍五入函数ROUND了。

ROUND函数有两个参数，第1参数：要处理的数据，第2参数：指定哪一个位置上的数字进行四舍五入。

如果第2参数大于0，则将数字四舍五入到指定的小数位。

如果第2参数等于0，则将数字四舍五入到最接近的整数。

如果第2参数小于0，则在小数点左侧（即个位）进行四舍五入。

方法2：使用CEILING函数

公式如下图所示。

公式

=CEILING(A1-0.6,1)

公式解释

在CEILING函数中第1参数是按第2参数的整数倍数向上返回的。举个例子，第1参数是1.6，第2参数是1，1的倍数有1，2，3，…而1.6介于1和2之间，由于此函数是向上返回接近的整数的倍数，所以返回2。

为什么要减0.6？这是为了让1.0～1.6返回1，让1.7～1.9返回2。

方法3：使用FLOOR函数

公式如下图所示。

公式

=FLOOR(A1+0.3,1)

公式解释

FLOOR和CEILING函数可以算得上是一对“姐妹”函数，FLOOR函数将第1参数按第2参数的整数倍数向下返回。

为什么要加0.3？因为这个函数是向下取整，即1.6+0.3=1.9，第2参数为1，1的倍数还是1，所以1.9返回1；而1.7+0.3=2，即向下返回1的倍数2。

本案例视频文件：02/案例134 如何实现“六舍七入”？

案例135　动态获取当前工作表名称

案例及公式如下图所示。这里要动态获取当前工作表的名称。

公式

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)

公式解释

CELL函数用于获取单元格信息。

这个函数有两个参数，第1参数：获取单元格的哪些信息，第2参数：获取哪一个单元格的信息。

如果第2参数省略，则返回最后更改的单元格中的信息，如果有第2参数，并且是单元格区域，则返回该单元格区域左上角的单元格中的信息。

第1参数可以为以下类型。

“address"：引用第一个单元格中的值，文本类型。

"col"：引用单元格的列标。

"color"：如果单元格中的负值以不同颜色显示，则返回1；否则返回0（零）。

"contents"：引用单元格区域左上角的单元格中的值（不是公式）。

"filename"：引用文件名（包括全部路径），文本类型。如果包含目标引用的工作表尚未被保存，则返回空文本("")。

“row”：引用单元格的行号。

"width"：引用取整后的单元格的列宽。列宽以默认字号的一个字符的宽度为单位。

这里的第1参数为“filename”，所以返回文件名包括全部路径，记得一定要保存工作簿，否则返回空值。

得到全部路径："C:\Users\Administrator\Desktop\[工作簿1.xlsx]完美培训"之后，用FIND函数找“]”的位置，然后加1并用MID函数提取字符。

本案例视频文件：02/案例135 动态获取当前工作表名称

案例136　Excel中的两个通配符的用法

通配符：问号？

问号可以代替任意一个字符（注意是字符）。

下面举例说明。

如“张三”，用问号？表示为“张?＂。

如“张三丰”，用问号？表示为“张??”。

通配符：星号*

星号可以代替任意一个以及一个以上的字符。

下面举例说明。

如“张三”，用星号表示为“张*”。

如“张三丰”，用星号表示为还是“张*”。

为什么下图所示的公式= VLOOKUP("45~100",$A$1:$B$3,2,0)会报错？

公式解释：

“~”是一个转义符，也就是要把它当作通配符，如果查找它本身（即“～”），则要在其前面加一个“~”，就像查找“*”一样，要写为“~*”。所以上面的公式正确的写法为=VLOOKUP("45~~100",$A$1: $B$3,2,0)，如下图所示。

本案例视频文件：02/案例136 Excel中的两个通配符的用法

案例137　ROW函数与ROWS函数的区别

ROW函数用于返回单元格或单元格区域的行号，返回的是数组。ROW函数的参数一定是单元格引用，不能是常量数组。

而ROWS函数用于返回总行数，它的参数可以是单元格区域，也可以是常量数组，返回的是一个数值。

ROW函数的第1种用法：括号里不放参数

案例及公式如下图所示。这里要返回单元格B1的行数。

公式

=ROW()

公式解释

如果参数为空，则函数返回当前单元格的行号。

上图中的公式位于B1单元格中，B1单元格的行号是1，所以返回1。

ROW函数的第2种用法：参数为一个单元格

公式如下图所示。

公式

=ROW(A9)

公式解释

如果参数指定一个单元格，就返回此单元格的行号。

在此公式中，因为A9单元格的行号是9，所以公式返回9。

ROW函数的第3种用法：参数为单元格区域

公式如下图所示。

公式

=ROW(A1:A5)

公式解释

此公式的参数为单元格区域，所以返回这个单元格区域的行号。

在此公式中，因为A1的行号是1，A2的行号是2，A3的行号是3，A4的行号是4，A5的行号是5，所以公式返回结果｛1;2;3;4;5｝。但是单元格中只显示1。

在编辑栏里选中公式=ROW(A1:A5)，按快捷键F9，就可以查看返回的结果｛1;2;3;4;5｝。

ROW函数第4种用法：参数为一整行

公式如下图所示。

公式

=ROW(3:3)

公式解释

此公式的参数是第3行整行。因为第3行的行号是3，所以此公式返回3。

ROW函数的第5种用法：参数可以为多行

公式如下图所示。

公式

=ROW(3:5)

公式解释

在此公式中包括3行，即第3~5行。

因为第3行的行号为3，第4行的行号为4，第5行的行号为5，所以公式返回结果｛3;4;5｝。

在编辑栏中选中公式=ROW(3:5)，按快捷键F9就可以查看返回的结果｛3;4;5｝。

ROWS函数的第1种用法：参数为一个单元格（指定一个单元格）

公式如下图所示。

公式

=ROWS(A9)

公式解释

因为参数里只有一个单元格A9，总行数是1，所以返回1。

ROWS函数的第2种用法：参数为单元格区域

公式如下图所示。

公式

=ROWS(A1:A9)

公式解释

因为在此公式中参数为9个单元格，共有9行，所以结果返回9。

ROWS函数的第3种用法：参数为常量数组

公式如下图所示。

公式

=ROWS({1,2,3;4,5,6})

公式解释

在此公式中，参数{1,2,3;4,5,6}是一个常量数组，且是一个2行3列的二维数组，所以返回2。

本案例视频文件：02/案例137 ROW函数与ROWS函数的区别

案例138　如何把“2017-10-20”转换为“20171020”

案例及公式如下图所示。使用TEXT函数可以把日期2017-10-20转换为20171020。

公式

=TEXT(A1,"yyyymmdd")

公式解释

由于A列中的数值是标准的日期格式，所以这里直接用TEXT函数实现。

第2参数：“yyyymmdd"，y（year）表示年；m（month）表示月；d（day）表示日。“yyyy”表示“2017”四位数字年份，也可以用e替代，即=TEXT(A1,"emmdd")；mm表示两位数字的月份，如月份为1，就会显示01，也就是月份不足两位，会自动添加前导0；dd表示两位数字的日，如日期为1，就会显示01。

本案例视频文件：02/案例138如何把“2017-10-20”转换为“20171020”

案例139　为什么公式=IF(2,3,4)返回3

这是IF函数的一种特殊用法，公式=IF(2,3,4)确实是返回3，下面解释一下。

参数讲解

IF函数有3个参数。

第1参数：逻辑判断，可以为大于（>）、小于（<）、等于（=）、不等于（<>）、大于或等于（>=）、小于或等于（<=）。

第2参数：如果第1参数判断成立，那么就显示第2参数。

第3参数：如果第1参数判断不成立，那么就显示第3参数。

要注意的知识点

在运算时，把TRUE当作1，把FALSE当作0。

当第1参数是数值时，如果第1参数为0，则当作FALSE；如果是其他数值，则全当作TRUE，总结一句话就是：非0为TRUE。

下面解释公式=IF(2,3,4)为什么返回3。

=IF(TURE,3,4)返回3，因为第1参数成立，所以返回第2参数3。

=IF(FALSE,3,4)返回4，原理同上一条。

=IF(1,3,4)返回3，把1当作TRUE，所以返回3。

=IF(0,3,4)返回4，把0当作FALSE，所以返回4。

所以=IF(2,3,4)返回3。

本案例视频文件：02/案例139 为什么公式=IF(2,3,4)返回3

案例140　隐藏0值

案例及公式如下图所示。这里要隐藏单元格中的0值。

公式

=IF(A1,A1,"")

公式解释

在此公式中，如果A1单元格中的值不等于0，或者不为空单元格，那么就显示A1单元格中的值，否则就显示空值。

当然这个公式也可以写成=IF(A1<>0,A1,"")。

本案例视频文件：02/案例140 隐藏0值

案例141　计算表达式

这里使用宏表函数加定义名称功能来计算表达式。

步骤1：把鼠标光标定位到B2单元格中。

步骤2：按快捷键Ctrl+F3，打开“编辑名称”对话框，新建一个名称。

步骤3：在“名称”文本框中输入“计算”，在“引用位置”文本框中输入公式“=EVALUATE (Sheet1!A2)”，单击“确定”按钮关闭对话框，如下图所示。

步骤4：在B2单元格中输入公式“=计算”，按Enter键后再向下填充公式，得到如下图所示的结果。

步骤5：在保存时一定要将保存类型选择为“启用宏工作簿”，从Excel 2007版开始，宏表函数属于宏。

本案例视频文件：02/案例141 计算表达式

案例142　如何把“2017.8.30”转换成“2017年8月30日”

案例及公式如下图所示。使用TEXT函数可以把“2017.8.30”的日期形式转换成“2017年8月30日”的日期形式。

公式

=TEXT(SUBSTITUTE(A1,".","-"),"e年m月d日")

公式解释

用SUBSTITUTE函数可以把“.”替换成“-”。

SUBSTITEUE函数有4个参数，第1参数：要处理的文本；第2参数：要被替换的值；第3参数：替换为的值；第4参数：要替换第几个值。

TEXT函数有两个参数，第1参数：要处理的格式，第2参数：要显示的格式。

e相当于“yyyy”，也就是说“e年m月d日”可以写成“yyyy年m月d日”。

本案例视频文件：02/案例142 如何把“2017.8.30”转换成“2017年8月30日”