逻辑、日期与文本函数应用与结果验证

第四章的公式都在回答“多少”，答案是一个可以对账的数字。这一章的三类函数回答的是另外三种问题，这一行属于哪一档、两个日期之间隔了多久、一串字符怎么拆开又怎么合上。答案的形态跟着变了，多数结果不再是数值而是文本，“优”和“良”差一个字，“1992-06-15”和“1992-06-16”差一个字符，状态栏求和这类顺手的核对工具全部失效。所以本章每个任务动手之前都先在纸上留一条对照线索，有的线索是几档人数，有的是翻日历推出来的一个年月，有的是抄下来的一个位段，公式执行完拿结果去和线索碰。提示词与 AI 回复照旧原文照录，其中第一个案例会有意破坏一次全书的惯例，把提示词里固定的版本声明抽掉，看看 AI 的答案会退到什么位置。

一份人事工作簿引出的五项任务

场景从销售部换到人事部。行政人事专员月底接到一项档案数字化的活，材料七零八落，一批培训考核成绩要定等级，一叠员工档案要算工龄、登记出生日期，市场部交来一份姓名和手机号挤在同一列的活动报名名单，最后还要把几列联系方式拼成一列做通讯录。五件事没有一件要算钱，全是判断、日期和字符串的活，正好把逻辑、日期、文本三类函数各练一遍。

1. 十二人的培训考核成绩按五档定等级，用到多级判断。

2. 八名员工从入职日期算工龄，要求精确到“满几年零几个月”。

3. 同一批员工从身份证号里提取出生日期，排成带横线的样式。

4. 十二行“姓名手机号”连写的报名名单拆成两列。

5. 通讯录的姓名、部门、座机、手机四列合成一列，用短横线隔开。

练习文件的四个工作表“考核成绩区”“人事档案区”“混合列拆分区”“通讯录合并区”与五项任务对应，工龄和生日两个任务共用“人事档案区”。文件里的人名、证件号、手机号全部虚构，证件号按第二章的脱敏规则构造，前六位统一是 110101，第 7 至 14 位的出生日期段保真，这一段正是任务三要用的原料。

模板下载：第五章函数练习文件

成绩等级判定与版本声明的关联

第一项任务在“考核成绩区”工作表，A 列是十二名参训员工的姓名，B 列是考核成绩，数据在第 2 行到第 13 行。定级规则五档，90 及以上是优，80 到 89 是良，70 到 79 是中，60 到 69 是及格，60 以下是不及格。动手前先留线索，逐行扫一遍 B 列，90、80、70、60 四个整档分数各有一人踩线，分别是高梦洁、罗嘉树、郭安琪、董雨薇，五档人数从高到低应该是 2、3、3、2、2。这个案例同时做一个对照实验，全书提示词固定以版本声明开头，这一次先故意抽掉它。

隐去版本声明的第一次提问

提示词除了没有版本，其余信息按老规矩写全。

有一张培训考核成绩表，A列姓名（文本），B列考核成绩（数值，
0到100的整数），数据在第2行到第13行。示例行：
沈一舟  95
唐启铭  89
请写一个公式放在C2，按成绩定等级：90及以上是优，80到89是良，
70到79是中，60到69是及格，60以下是不及格，我会下拉到C13。

AI 的回复如下。

在 C2 输入以下公式，下拉填充到 C13：

=IF(B2>=90,"优",IF(B2>=80,"良",IF(B2>=70,"中",IF(B2>=60,"及格","不及格"))))

判断从高到低逐层进行：
* B2>=90 → "优"，不满足才进入下一层
* B2>=80 → "良"，能走到这一层说明成绩已经小于90
* B2>=70 → "中"
* B2>=60 → "及格"
* 以上都不满足 → "不及格"

嵌套 IF 在所有 Excel 版本里都能运行。如果你的版本较新
（2019及以上或Microsoft 365），可以改用 IFS 函数，写法更
平直，需要的话告诉我你的版本。

四层括号一层包一层，公式能用，读起来费劲。值得注意的是回复末尾那句话，AI 不知道版本，把兼容性放在第一位，给了一个在 2003 上也能跑的答案，同时把更好的方案挂在了“告诉我你的版本”后面。第一章模板里的版本项平时感觉不到作用，它的作用在缺席时才显形。

补上版本项后的答案更替

顺着 AI 留的话头，把版本补上去。

我在用Office LTSC专业增强版2024，支持IFS。请给出IFS的写法。

AI 的回复如下。

Office LTSC 2024 支持 IFS。在 C2 输入：

=IFS(B2>=90,"优",B2>=80,"良",B2>=70,"中",B2>=60,"及格",TRUE,"不及格")

IFS 按"条件,结果"成对排列，从左到右逐对判断，命中即返回：
* 前四对与嵌套 IF 的四层判断一一对应
* 最后一对 TRUE,"不及格" 是兜底项。IFS 没有嵌套 IF 那种
  "否则"参数，所有条件都落空时的返回值要自己补一对，
  条件位置直接写 TRUE
* 如果删掉兜底对，成绩59的行会返回 #N/A 错误

两条公式结果完全相同，差别在结构：嵌套 IF 是四层括号层层
包裹，IFS 是一层平铺。以后规则改成六档（比如85到89单算
"中上"），嵌套 IF 要数清楚新条件该插进第几层括号，IFS 只要
在对应位置插入一对"条件,结果"。

两版公式的同表实测

嵌套 IF 进 C 列，IFS 进 D 列，各自下拉到第 13 行，逐行比对，十二行完全一致。四个踩线的人挨个看，高梦洁 90 是优、罗嘉树 80 是良、郭安琪 70 是中、董雨薇 60 是及格，全部落在高档一侧，说明每层的大于等于号都在。再用 =COUNTIF(C2:C13,"优") 挨个数五档，返回 2、3、3、2、2，与线索吻合。

回复里那句“删掉兜底对会返回 #N/A”也顺手验证。在空白列输入去掉 TRUE,"不及格" 的 IFS 下拉，冯天翊 59 分和阮思彤 42 分两行果然显示 #N/A，其余十行正常。这个错误值有它的用处，兜底忘了写，出错的行会自己跳出来喊，嵌套 IF 忘写最后的“否则”参数则会返回 FALSE 混在文本里，反而不容易被看见。

工龄间隔的年月两段计算

第二项任务换到“人事档案区”工作表，A 列姓名，B 列入职日期，C 列身份证号，八名员工的数据在第 2 行到第 9 行。人事要的工龄格式是“满几年零几个月”，年和月两段都要。线索选王砚秋，入职日期 2019 年 8 月 12 日，翻着日历往后推，到 2025 年 8 月 12 日满六个整年，再往后到 2026 年 7 月 12 日又满十一个整月，剩下的十几天不足一月舍去，按 2026 年 7 月底核算应该是“6年11个月”。

需求转写与返回的公式

我在用Office LTSC专业增强版2024。接着处理人事档案区工作表，
A列姓名（文本），B列入职日期（日期），C列身份证号（文本，18位），
数据在第2行到第9行。示例行：
王砚秋  2019/8/12  110101199206154821
林望舒  2020/7/31  110101198811230412
请写一个公式放在D2，从入职日期算到今天，显示成"6年11个月"
这种年月都有的样子，我会下拉到D9。

AI 的回复如下。

在 D2 输入以下公式，下拉填充到 D9：

=DATEDIF(B2,TODAY(),"Y")&"年"&DATEDIF(B2,TODAY(),"YM")&"个月"

DATEDIF 专门计算两个日期的间隔，三个参数依次是起点日期、
终点日期、返回单位：
* "Y" → 相隔的整年数，不满一年的部分舍去
* "YM" → 扣掉整年之后剩余的整月数，结果只会在0到11之间
* 两段结果用 & 与文字拼接成"6年11个月"的样子

两点说明：
1. DATEDIF 是隐藏函数，输入时没有参数提示，插入函数的
   列表里也搜不到它，直接敲完整公式就能正常运行，这是
   从早期版本保留下来的兼容性函数
2. TODAY() 每天自动变化，明天打开文件工龄会自动更新。
   如果要按固定日期核算（比如统一算到月底），把 TODAY()
   换成 DATE(2026,7,31) 这种写死的日期

敲公式时回复第一条说明当场应验，输入 =DATEDIF( 之后编辑栏下方没有弹出任何参数提示，函数名也不在自动补全列表里，看起来像敲错了名字，回车却正常出数。不知道这个特性的人很容易在这里退回去反复检查拼写。

基准日的固定与逐行核对

公式带着 TODAY() 每天变一次结果，书里的数字没法和读者手上的复算对齐。按回复第二条说明，验证版本把终点换成 DATE(2026,7,31)，正文的得数都按这一天冻结。下拉到 D9，八行结果与入职日期的对应关系如下。

王砚秋的“6年11个月”与日历推出来的线索一致。表里另有两行值得停一下。林望舒 2020 年 7 月 31 日入职，到基准日恰好踩在周年当天，返回“6年0个月”，说明 DATEDIF 的整年判定含当天。方启年 2025 年 7 月 30 日入职，比基准日早一年零一天，返回“1年0个月”，把基准日改成 7 月 29 日再看，立刻变成“0年11个月”，一天之差跨过一个整年。工龄涉及转正、年假天数这类待遇，踩线日期算不算满年，公式给的口径要和公司制度的口径核对过才能用。

参数代号的实测区分

"YM" 这个代号最容易被想当然。它看起来像“年和月”，真实含义是“扣除整年后剩余的月数”。空白列做一个对照，F2 输入 =DATEDIF(B2,DATE(2026,7,31),"M")，参数换成单独的 "M"，王砚秋那行返回 83。这是两个日期之间的总月数，六年十一个月折成月正好是 83 个月。如果误把 "M" 当成年月组合里的月，拼出来的工龄就是“6年83个月”，一个看一眼就荒唐的结果。荒唐反而是运气好，83 这种数字自己会暴露，真正危险的是 "YM" 与 "MD" 这类差得不远的代号用混，返回的数字看着都合理，不对线索根本发现不了。

证件号码位段的日期化提取

第三项任务还在“人事档案区”，从 C 列身份证号里取出生日期，登记格式要求“1992-06-15”这种带横线的样式，结果放 E 列。十八位号码里第 7 至 14 位是出生日期，这是固定国标。线索抄两条，王砚秋的号码第 7 至 14 位是 19920615，方启年的是 20010422，后者的号码以 X 结尾，顺便看提取会不会被这个字母干扰。

提取公式的获取与执行

还是人事档案区工作表，C列身份证号是文本格式，18位，第7到14位
是出生日期，比如110101199206154821里的19920615。请写一个公式
放在E2，把出生日期提取成1992-06-15这种带横线的样子，我会下拉
到E9。

AI 的回复如下。

在 E2 输入以下公式，下拉填充到 E9：

=TEXT(MID(C2,7,8),"0000-00-00")

公式分两步：
* MID(C2,7,8) → 从第7个字符开始截取8个字符，得到"19920615"
* TEXT(...,"0000-00-00") → 按四位-两位-两位的掩码重新排版，
  自动在年月之间和月日之间各插一条横线，得到"1992-06-15"

三个注意事项：
1. 结果是文本不是日期值，显示上看不出差别，但不能直接
   参与日期运算
2. 要得到真正的日期值，在公式最前面加两个负号强制转换：
   =--TEXT(MID(C2,7,8),"0000-00-00")
   转换后单元格会先显示成33770这样的数字，把单元格格式
   设为"日期"就恢复正常
3. C列必须是文本格式。如果证件号当初被存成数值，第16位
   起已经被截断成0，好在出生日期段在前14位里，通常还在，
   但整列号码已经废了，先按文本重新粘贴再提取

公式进 E2 下拉到 E9，王砚秋那行返回 1992-06-15，方启年那行返回 2001-04-22，与抄下的两个位段一致。X 在第十八位，MID 从第七位取八个字符根本碰不到它，字母尾号对提取零影响。

提取结果的类型核查

八行结果全部靠左对齐，第三章立过的规矩在这里派上用场，靠左是文本的坐姿。再补一个硬证据，空白格输入 =COUNT(E2:E9)，COUNT 只数数值，返回 0，八个“日期”在 Excel 眼里全是字符串。登记表只要求这个样式，文本就够用，任务可以收工。但档案表的下一步常常是算年龄、按生日排序，文本日期在那些场景里要么报错要么排出怪序，所以顺手把回复第二条的转换版本也验一遍。F2 输入 =--TEXT(MID(C2,7,8),"0000-00-00")，返回 33770，正是 1992 年 6 月 15 日在 Excel 日期系统里的流水号，第三章见过的那套底层记数又露了一次面。把 F 列格式设为日期，显示变成 1992/6/15，靠右对齐，COUNT 数它返回 8，这一列才是能参与运算的真日期。

混合字符列的按字节拆分

第四项任务的材料最糙。市场部交来的活动报名名单在“混合列拆分区”工作表，A 列十二行，姓名和手机号头尾相连，中间没有任何分隔，像“林晚13905712648”这样挤成一串。要求拆成 B 列姓名、C 列手机号。留线索先清点形态，两字名四个，林晚、白露、江河、叶蓁，三字名六个，四字复姓两个，欧阳靖宇、上官临安，手机号全部十一位半角数字。这个案例是本章的错误案例，第一版公式的翻车过程完整保留。

按固定字长拆分的第一版方案

第一次提问犯了一个隐蔽的错，随手抄的两行示例恰好都是三字名。

接着处理混合列拆分区工作表。A列是姓名和手机号连在一起的文本，
数据在第2行到第13行。示例行：
顾承宇13816429305
沈知微13674925810
请写公式拆开，姓名放B列、手机号放C列，我会下拉到第13行。

AI 的回复如下。

从示例行看，姓名是3个字，手机号是11位，按固定长度拆分即可：

B2 输入：=LEFT(A2,3)
C2 输入：=MID(A2,4,11)

* LEFT(A2,3) → 取最左边3个字符，即姓名
* MID(A2,4,11) → 从第4个字符开始取11个字符，即手机号

两条公式分别下拉填充到第13行。

两条公式下拉，六个三字名的行干干净净，另外六行全军覆没。林晚那行拆出的姓名是“林晚1”，手机号只剩十位的“3905712648”，开头的 1 被姓名吞了。欧阳靖宇那行更醒目，姓名截成“欧阳靖”，手机号开头顶着一个“宇”字。十二行错一半，错的全是姓名不足或超出三个字的行。

错误证据的回传与追问

追问把错误行原样抄回去，并补上第一次漏掉的形态规律。

你的公式对一半的行是错的。这张表的姓名不全是3个字：两个字的
有林晚、白露、江河、叶蓁，还有欧阳靖宇、上官临安两个四字的
复姓名字。林晚那行拆出的姓名是"林晚1"，手机号只剩10位；
欧阳靖宇那行的手机号开头带着一个"宇"字。手机号都是11位半角
数字，姓名里没有数字。有没有不依赖姓名字数的拆法？

回头看，AI 第一版并没有乱来，它在回复第一句就交代了推理来源，“从示例行看，姓名是3个字”。两行示例碰巧同一形态，AI 把巧合归纳成了规律。第二章说过示例行是给描述做担保的实物证据，这个案例补上它的另一面，示例行只覆盖常规形态时，担保的就只有常规形态，特例必须自己挑进去。

按字节检索的修正方案

AI 的修正回复如下。

抱歉，我从两行示例归纳出了"姓名固定3个字"，这个前提不成立。
改用按字节定位的拆法，姓名几个字都能拆：

B2 输入：=LEFTB(A2,SEARCHB("?",A2)-1)
C2 输入：=MIDB(A2,SEARCHB("?",A2),11)

原理是汉字和数字的字节宽度不同：
* 带B后缀的函数按字节计数，一个汉字占2字节，一个半角数字
  占1字节
* SEARCHB("?",A2) 里的问号是通配符，按字节搜索时只匹配
  单字节字符，返回的是第一个半角字符（也就是手机号第一位
  数字）所在的字节位置
* 姓名占的字节数等于该位置减1，LEFTB 按这个字节数取出完整
  姓名；MIDB 从该位置起取11个字节，正好是11位手机号

成立的前提：姓名全是汉字（全角字符），手机号是半角数字。
如果有手机号被录成全角数字，SEARCHB 找不到单字节字符会
返回错误。

另外你这张表手机号固定11位，还有一个不数字节的写法：
B2 =LEFT(A2,LEN(A2)-11)
C2 =RIGHT(A2,11)
从总长度里扣掉11位，剩下的全算姓名。两套任选。

字节方案进 B、C 两列重新下拉，逐行检查，林晚回到两个字，欧阳靖宇四个字完整，手机号十二行全部十一位开头是 1。用 =SUMPRODUCT((LEN(C2:C13)=11)*1) 整列复核，返回 12。回复末尾的备选写法也在空白列试过，结果与字节方案完全一致，它的思路更朴素，手机号长度固定，姓名等于总长减十一，这张表上两套等价。字节方案的适用面更宽，将来遇到“姓名加不定长英文编号”这类右段长度不固定的混合列，扣长度的办法失灵，按字节找第一个半角字符依然能用。

多列信息的定界合并

最后一项任务把方向反过来，拆完的东西要拼回去。“通讯录合并区”工作表 A 到 D 列是姓名、部门、座机分机、手机号，八行数据里林望舒、周聿明、苏念真三行的座机是空的，这三行就是本案例的线索。目标是在 F 列合成一段用短横线隔开的通讯串，发给行政做门禁登记。

合并公式与执行结果

最后是通讯录合并区工作表。A列姓名、B列部门、C列座机分机、
D列手机号，都是文本，其中有3行的座机分机是空的，数据在第2行
到第9行。请写一个公式放在F2，把四列合成一段文字，中间用
短横线隔开，比如"王砚秋-行政部-8021-13905712648"，我会下拉到F9。

AI 的回复如下。

在 F2 输入以下公式，下拉填充到 F9：

=TEXTJOIN("-",TRUE,A2:D2)

TEXTJOIN 的三类参数：
* 第一参数 "-" → 分隔符，每两段内容之间插一个
* 第二参数 TRUE → 忽略空单元格。座机为空的行直接跳过这一段，
  不会出现两条连续的短横线
* 第三参数 A2:D2 → 要合并的区域，也可以罗列多个区域或单元格

你的表有3行座机为空，TRUE 正适合：空行合出来是
"林望舒-财务部-13816429305"，三段两条横线。

如果第二参数写 FALSE，空单元格会保留位置，合出来是
"林望舒-财务部--13816429305"，中间两条横线挨在一起。看着难看，
但需要以后按"-"分列还原成四列时，只有 FALSE 能保证每行段数
一致，列的位置不会错乱。

公式进 F2 下拉，王砚秋那行返回“王砚秋-行政部-8021-13905712648”，四段三线，林望舒那行返回“林望舒-财务部-13816429305”，三段两线，空座机被无声跳过，门禁登记要的正是这种紧凑样式，任务通过。用 =LEN(F2)-LEN(SUBSTITUTE(F2,"-","")) 数每行的横线数，座机齐全的五行是 3，空座机的三行是 2，TRUE 的忽略行为整列坐实。

第二参数在空单元格上的分岔

回复主动提到的 FALSE 分岔值得花五分钟做实。G2 输入 =TEXTJOIN("-",FALSE,A2:D2) 下拉，林望舒那行变成“林望舒-财务部--13816429305”，两条横线并排，八行的横线数清一色是 3。两版各有用途，分岔点在这列合并结果的去向。

本章提示词模板

五项任务的提问要点与各自的对照线索汇总如下，本章的公式结果多为文本，线索一律预埋在最容易出错的样本上。

两类场景的空白模板如下，方括号内容替换成实际情况。

【让AI写函数公式】
我在用Office LTSC专业增强版2024。有一张[表名]，结构如下：
[列号] [列名]（[类型]，[取值或格式说明]）
数据在第[起]行到第[止]行。示例行：
[示例行把特殊形态各放一行：踩线的数值、最短和最长的文本、空着的单元格]
请写一个公式放在[单元格]，[要做的判断或转换；每档区间的
边界归属；空值和特殊行怎么处理；结果要长成什么样子]，
我会下拉到第[止]行。

【拿着错误结果追问】
你的公式对一部分行是错的。[哪些行错了，错成了什么样子，
原样抄写错误结果]。这张表的实际情况是[第一版提问里漏掉的
形态规律]。有没有不依赖[错误方案所假设的前提]的写法？