3.3　中级函数综合案例

案例193　根据身份证号提取户籍所在地

案例及公式如下图所示。这里要根据A列中的身份证号提取户籍所在地。

公式

=VLOOKUP(--LEFT(A2,6),身份证代码!A:B,2,0)

公式解释

此公式从A2单元格左边提取6位数字。这6位数字表示省市代码，并作为VLOOKUP函数的第1参数，然后在数据源中查找。由于数据源中的第1列是数值型数字，所以要在LEFT函数前加--，返回文本型数值。

本案例视频文件：03/案例193 根据身份证号提取户籍所在地

案例194　根据身份证号提取出生日期

案例及公式如下图所示。这里要根据A列中的身份证号提取出生日期。

公式

=TEXT(TEXT(RIGHT(19&MID(A2,7,6+(LEN(A2)=18)*2),8),"0000-00-00"),"YYYY-MM-DD")

公式解释

早期的身份证号有15位，此公式是为了兼顾15位的身份证号：如果身份证号是18位就提取8位数字，如果是15位就提取6位数字，再在前面补上“19”，然后从单元格右边提取8位数字。

为什么还要用TEXT函数转换一下？因为第一个TEXT函数得到的是文本，不是日期型数据，在其外面再嵌套一个TEXT函数才会得到真正的日期型数据。

本案例视频文件：03/案例194 根据身份证号提取出生日期

案例195　根据身份证号提取性别

案例及公式如下图所示。这里要根据A列中的身份证号提取性别。

公式

=TEXT(-1^MID(A2,15,3),"女;男")

公式解释

在15位的身份证号码中，第15位数字表示性别，如果是偶数就是女性，如果是奇数就是男性。在18位的身份证号码中，第17位数字表示性别，如果是偶数就是女性，如果是奇数就是男性。另外，-1的偶次方是1，-1的奇次方是-1。而TEXT函数的第2参数的自定义格式分4节：第1节为正数；第2节为负数；第3节为0；第4节为文本。这里只用了第4参数的前两节：正数和负数，中间要用分号分开。

本案例视频文件：03/案例195 根据身份证号提取性别

案例196　根据身份证号计算年龄

案例及公式如下图所示。这里要根据A列中的身份证号计算年龄。

公式

=DATEDIF(B2,TODAY(),"Y")

公式解释

DATEDIF函数中有3个参数：第1参数为起始日期；第2参数为结束日期；第3参数为计算方式，即计算两日期相差的年数。

本案例视频文件：03/案例196 根据身份证号计算年龄

案例197　根据名称显示照片

案例及公式如下图所示。这里要根据名称显示工作表2中的照片。

公式

=INDEX(照片!$B:$B,MATCH(Sheet2!$A$1,照片!$A:$A,0))

公式解释

按快捷键Ctrl+F3，打开“编辑名称”对话框。

复制公式=INDEX(照片!$B:$B,MATCH(Sheet2!$A$1,照片!$A:$A,0))，粘贴到“引用位置”文本框中，在“名称”文本框中输入“相片”。

复制第2张工作表里的任意一张图片并粘贴到第1张工作表里。

将鼠标光标定位到编辑栏里，输入“=相片”，再按Enter键。

然后修改第1张工作表中A1单元格的姓名。

本案例视频文件：03/案例197 根据名称显示照片

案例198　使用VLOOKUP函数制作工资条

案例及公式如下图所示。这里根据编号，在引用数据源表中找到对应的值。

公式

=VLOOKUP($A2,数据源!$A$1:$P$16,COLUMN(B1),0)

公式解释

在B2单元格中输入公式。接着把公式向右填充到P2单元格中，然后选中单元格区域A1:P3，将公式向下填充到第45行，中间不能停顿。

本案例视频文件：03/案例198 使用VLOOKUP函数制作工资条

案例199　将周末高亮显示

案例及公式如下图所示。这里要将日期为周六和周日的单元格高亮显示。

公式

=IF(MONTH(DATE(2018,$A$2,COLUMN(A1)))=$A$2,DATE(2018,$A$2,COLUMN(A1)),"")

=WEEKDAY(C$2,2)>5

公式解释

在C2单元格中输入第1个公式并向右填充产生日期，这个公式的巧妙之处在于使用了IF判断函数，如果根据日期提取出来的月份还是等于A2单元格中的月份，就显示公式DATE(2018,$A$2, COLUMN(A2))，否则显示空。

在C3单元格中输入第2个公式=WEEKDAY(C$2,2)>5，WEEKDAY函数用于返回一个日期是一周的第几天，如果第2参数为2，那么星期一就是一周的第1天，星期天就是一周的第7天。然后将公式向右填充。

另外，选择“开始”→“条件格式”→“新建规则”命令，在弹出的对话框中选择“使用公式确定要设置格式的单元格”，在下面的文本框中输入公式=WEEKDAY(C$2,2)>5，单击“格式”按钮，如下图所示。

弹出“设置单元格格式”对话框，设置相应格式，如下图所示。

本案例视频文件：03/案例199将周末高亮显示

案例200　使用定义名称功能+INDIRECT函数实现二级下拉菜单

案例及公式如下图所示。这里要为B7单元格设置数据有效性，并设置二级下拉菜单。

公式

=INDIRECT($A$7)

公式解释

选中要设置数据有效性的单元格区域，在Excel功能区中选择“数据”选项卡中的“数据有效性”命令，在弹出的对话框中可以设置数据有效性。选择“设置”选项卡，在“允许”列表框中选择“序列”选项，在“来源”列表框中输入公式“=INDIRECT($A$7)”，如下图所示。另外，在A7单元格中一定要先输入数据，否则系统会报错。

如果数据来源是多个单元格，则要用相对引用。

本案例视频文件：03/案例200 使用定义名称功能+INDIRECT函数实现二级下拉菜单

案例201　使用数组公式实现二级下拉菜单

案例及公式如下图所示。这里要根据A2:B6单元格区域中的值，为G1:G6单元格区域设置二级下拉菜单。

公式

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

公式解释

D2单元格需要设置数据有效性：选择单元格D2，然后选择“数据”选项卡中的“数据工具”选项，在打开的下拉列表中选择“数据验证”命令。在打开的对话框中选择“设置”选项卡，在“允许”列表框中选择“序列”，在“来源”文本框中选择单元格区域B2:B5。E2单元格也需要设置数据有效性，在“来源”文本框中选择单元格区域G1:G6。

G列中的公式其实就是1对多查询公式。

本案例视频文件：03/案例201 使用数组公式实现二级下拉菜单

案例202　将TEXT函数当IF函数用

案例及公式如下图所示。

公式

=TEXT(TEXT(A2,"[>=80]优秀;[>=70]良好;0"),"[>=60]及格;不及格")

公式解释

首先要理解Excel里的自定义单元格格式分为4节，第1节为正数；第2节为负数；第3节为零；第4节为文本，记得中间是用分号分开。

另外，如果出现3节，前两节有条件，则把不符合前两节条件的全部放在第3节上。

如果出现两节，第1节有条件，把不符合第1节条件的全部放在第2节上。

这里用了两个TEXT函数嵌套实现IF判断的作用，里层的TEXT函数有两个条件，将大于或等于80和大于或等于70的都过滤了，剩下小于70的都放在第3节上，交给外层的TEXT函数处理。

本案例视频文件：03/案例202 将TEXT函数当IF函数用

案例203　为银行卡号每隔4位加空格

案例及公式如下图所示。这里要将A列中的银行卡号每隔4位加空格。

公式

=LEFT(A2,4)&" "&TEXT(MID(A2,5,14),"0000 0000 0000 000")

公式解释

当数字超过15位时，函数无法实现此功能，只能把19位的银行卡号拆分成4位和15位进行处理。

本案例视频文件：03/案例203 为银行卡号每隔4位加空格

案例204　动态求每周的销量

案例及公式如下图所示。这里要根据A列中的日期动态求销量。

公式

=SUM(OFFSET(A1,COUNTA(A:A)-1,2,-WEEKDAY(MAX(A:A),2),1))

公式解释

WEEKDAY函数用于返回一个日期是一周的第几天。如果它的第2参数为2，则规定了星期一是一周的第1天，星期天是一周的第7天。由于A列中的日期是按升序排序的，所以其最后一天的日期也是最大值。

为什么要在WEEKDAY函数前面加一个负号？因为OFFSET函数是先找到最后这个单元格，然后向上扩展行数的。

COUNTA(A:A)-1：得到的结果为OFFSET函数的第2参数，即向下偏移的行数，最后用SUM函数求和。

本案例视频文件：03/案例204 动态求每周的销量

案例205　设置7天内生日提醒

案例及公式如下图所示。这里要对A列中的日期设置7天内生日提醒。

公式

=IF(AND((DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY())>=1,(DATE(YEAR (TODAY ()),MONTH(A2),DAY(A2))-TODAY())<=7),(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()),"")

公式解释

(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY())：从出生日期中提取年、月和日，这里用TODAY函数提取年。要提取7天内过生日的，这里要满足两个条件：结果大于或等于1且小于或等于7就显示结果，否则就显示空。

本案例视频文件：03/案例205 设置7天内生日提醒

案例206　使序号随着筛选而自动编号

案例及公式如下图所示。这里要对工作表中的数据随着筛选而自动编号。

公式

=SUBTOTAL(103,$B$1:B2)-1

公式解释

SUBTOTAL函数有两个参数：第1参数为计算方式，例如用103表示统计非空单元格的个数，但是不包括隐藏的行。为什么第2参数不为$B$1:B1，而为$B$1:B2？因为SUBTOTAL函数会把最后一行当作汇总行。

本案例视频文件：03/案例206 使序号随着筛选而自动编号

案例207　给合并单元格编号

案例及公式如下图所示。这里要实现将合并单元格编号。

公式

=COUNTA($A$1:A1)

公式解释

对于这个公式要注意两点。

第1点：单元格区域$A$1:A1“锁住头不锁住尾”，也就是冒号前面的A1为绝对引用，冒号后面的A1为相对引用。

第2点：在A2单元格中输入公式之后，选中单元格区域A2:A10，再按快捷键Ctrl+Enter。

本案例视频文件：03/案例207 给合并单元格编号

案例208　不显示错误值的3种方法

案例及公式如下图所示。这里要根据金额和数量求单价，且不显示错误值。

公式

=IFERROR(A2/B2,"")

公式解释

由于数量的值有时是空的，也就是当0作为除数时会产生错误值，这里使用IFERROR函数来处理：如果公式有错误值就显示空，没有错误值就显示原公式的结果。

如果你的Excel版本是2003版本，就要用这个公式：=IF(ISERROR(A2/B2),"",A2/B2)。

就此案例来说，还可以用公式=IF(B2,A2/B2,"")得到相同的结果。

本案例视频文件：03/案例208 不显示错误值的3种方法

案例209　TEXT函数+!的用法

案例及公式如下图所示。这里要对A列中每个单元格中的数字求和。

公式

=SUM(--TEXT(MID(A1,ROW($1:$19),1),"0;;0;!0"))

公式解释

此公式就是把A列单元格中的每一个数字相加。当然这个公式在实际工作中用不到，但是我们要学会TEXT函数的第2参数的用法。由于在用MID函数分隔每一个数字时会产生一些空文本""，如果直接在其前面加“--”符号会报错，所以要用TEXT函数处理一下。TEXT函数的第2参数"0;;;!0" 共有4节，第1节为正数，显示0；第2节为负数，显示空；第3节为零，显示0；第4节为文本，用!0强制显示0。

本案例视频文件：03/案例209 TEXT函数+!的用法

案例210　计算经过多少个工作日完成任务

案例及公式如下图所示。这里要计算经过多少个工作日完成任务。

公式

=WORKDAY.INTL(A2-1,B2,11)

公式解释

WORKDAY.INTL这个函数只有在Excel 2010及以上的版本中才有，其有4个参数。第1参数：开始日期；第2参数：经过多少天；第3参数：指定哪个日期为非工作日期；第4参数：特殊的节假日，这里省略了第4参数。

为什么将A2单元格中的值减1？因为这里的日期计算包括开始日期这一天，多算了一天。

如果Excel是2010以下版本，那么可以用以下公式，如下图所示。

=SMALL(IF(WEEKDAY(A2+ROW($1:$99)-1,2)<>7,A2+ROW($1:$99)-1),B2)

WEEKDAY(A2+ROW($1:$99)-1,2)<>7：从开始日期开始计算，包含开始日期，依次加0，然后加1、加2、加3……一直加到98，如果得到的日期不等于7，则说明不是星期天。

WEEKDAY函数的第2参数如果为2，则表示星期一是一周的第1天。

最后用SMALL函数把符合条件的日期提取出来。

本案例视频文件：03/案例210　计算经过多少个工作日完成任务

案例211　向下和向右填充公式生成26个字母

案例及公式如下图所示。这里要在单元格中向下和向右填充公式生成26个字母。

公式

=SUBSTITUTE(ADDRESS(1,COLUMN(A1)+ROW(A1)-1,4),1,"")

公式解释

这里利用了ADDRESS函数，它共有4个参数。第1参数：引用的行；第2参数：引用的列；第3参数：引用方式，这里为4，表示为相对引用；第4参数：引用的工作表。

这里的ADDRESS函数的第3参数用得很巧妙，不管是向下还是向右填充公式，都会产生数列1,2,3,…，最后用SUBSTITUTE函数把多余的1去掉。

本案例视频文件：03/案例211 向下和向右填充公式生成26个字母

案例212　提取括号里的数据

案例及公式如下图所示。

公式

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

公式解释

这里用SUBSTITUTE函数把“（”和“）”统计成“（”，然后用拉大距离法，通过SUBSTITUTE函数把“（”替换成99个空格。接着用MID函数提取数据，从第99个位置开始，提取99个字符，也就是提取小括号里的数据，最后用TRIM函数把数据前后的空格去掉。

当然我们可以利用FIND函数查找“（”和“）”的位置，再用MID函数提取其中的数据，如下图所示。

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

本案例视频文件：03/案例212 提取括号里的数据

案例213　计算一个日期为当月的第几周

案例及公式如下图所示。这里要根据A列中的日期，计算此日期为当月的第几周。

公式

="第"&(WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1,2)+1)&"周"

公式解释

WEEKNUM函数用于返回一个日期是一年中的第几周，而我们要先找到这个月的第一天是一年中的第几周，然后将两数相减，得到的是日期是当月的第几周。

EOMONTH(A2,-1)得到的是上一个月的最后一天日期，然后加1得到当月的第一天日期。

本案例视频文件：03/案例213 计算一个日期为当月的第几周

案例214　隔列求和的3种方法

案例及公式如下图所示。这里要计算A2:F2单元格区域中偶数列的数量之和。

方法1：使用SUM和IF函数

公式

=SUM(IF(A1:F1="生产",A2:F2,0))

公式解释

此种方法是使用数组公式来求解的，如果单元格区域A1:F1中有字符等于“生产”，就显示其下方单元格中对应的数据，否则就显示0，然后再用SUM函数求和。

方法2：使用SUMIF函数（见下图）

=SUMIF(A1:F1,"生产",A2:F2)。

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

方法3：使用SUM+IF+COLUMN函数（见下图）

公式

=SUM(IF(MOD(COLUMN(A1:F1),2),0,A2:F2))

此公式是求偶数列中的数的和。这里使用了取余函数MOD。它的第2参数为2，表示除数，如果结果等于1就显示0，等于0就显示下一行对应的数值，然后用SUM函数求和。这个公式也是数组公式。

本案例视频文件：03/案例214 隔列求和的3种方法

案例215　取得单元格的列号

案例及公式如下图所示。这里要计算当前单元格中的列号。

公式

=COLUMN()

公式解释

如果COLUMN函数的参数为空，则返回这个公式所在的单元格的列号。

当然，也可以用单元格信息函数CELL，即公式=CELL("col")，如下图所示。但是这个公式只能应用于一个单元格中，不能应用于多个单元格中，如果要应用于多个单元格中，就要把它的第2参数补上，即=CELL("col",C2)。

本案例视频文件：03/案例215 取得单元格的列号

案例216　筛选在19:00—23:00范围内的时间

案例及公式如下图所示。这里要根据A列的时间，筛选出在19:00—23:00范围内的时间。

公式

=TEXT(INDEX(A:A,SMALL(IF((HOUR($A$2:$A$10)>=19)*(HOUR($A$2:$A$10)<=23),ROW($A$2:$A$10),2^20),ROW(A1))),"e-m-d hh:mm:ss;;")

公式解释

用HOUR函数可以把单元格区域A2:A10中的小时提取出来，如果大于或等于19，且小于或等于23，就显示它们所在的行号，否则就显示2^20，也就是Excel最大的行号。

在此公式中，当引用最后一个单元格中的值为0时，也会显示日期格式，所以用TEXT函数处理一下，如果值为0则显示为空。

本案例视频文件：03/案例216 筛选在19:00—23:00范围内的时间

案例217　判断某月有多少天

案例及公式如下图所示。这里根据A列中的日期，判断日期所在的当月有多少天。

公式

=DAY(EOMONTH(A1,0))

公式解释

EOMONTH函数用于返回一个日期的前几个月或者后几个月的最后一天。其中共有两个参数，第1参数：起始日期；第2参数：如果为0，则表示起始日期所在月份的最后一天，如果为-1，则表示起始日期前一个月的最后一天，如果为1，则表示起始日期后一个月的最后一天，依此类推。

本案例视频文件：03/案例217 判断某月有多少天

案例218　获取当前工作表的名称

案例及公式如下图所示。

公式

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

公式解释

CELL("filename")：使用了单元格信息函数CELL，用于返回“工作簿路径+[工作簿名.扩展名]+工作表名”。然后用FIND函数找到右半边中括号的位置并加1，作为MID函数的第2参数。

本案例视频文件：03/案例218 获取当前工作表的名称

案例219　输出4位数，不足4位在左边加0

案例及公式如下图所示。这里要将A列单元格中的数据输出为4位数，如果不足4位数则在左边加0。

方法1：使用RIGHT函数

公式

=RIGHT(A1+10000,4)

公式解释

先将单元格中的数字加上10000（前提是单元格中的数字不超过4位），目的是补充0，原来的数字位置保持不变，然后从单元格的右边开始提取4位数字，这样就在不足4位的数字的前面补0了。

方法2：使用REPT和LEN函数（见下图）

公式

=REPT(0,4-LEN(A1))&A1

公式解释

REPT函数用于重复第1参数，重复多少次由它的第2参数决定。4-LEN(A1)得到要重复的次数。

方法3：使用TEXT函数（见下图）

公式

=TEXT(A1,"0000")

本案例视频文件：03/案例219 输出4位数，不足4位在左边加0

案例220　限制单元格中只能输入15位或者18位字符

案例及公式如下图所示。这里要限制单元格A1中只能输入15位或者18位字符。

公式

=OR(LEN(A1)=15,LEN(A1)=18)

公式解释

在“数据有效性”对话框里设置数据有效性公式。具体操作方法：选中A列（要设置的单元格），选择“数据”选项卡中的“数据有效性”命令。在打开的“数据有效性”对话框中选择“设置”选项卡，在“允许”列表框中选择“自定义”选项，在“公式”文本框中输入“=OR(LEN(A1)=15,LEN(A1)=18)”，单击“确定”按钮。

单元格字符的长度等于15或者单元格字符的长度等于18，这两个条件有一个成立就可以了。

本案例视频文件：03/案例220 限制单元格中只能输入15位或者18位字符