A08传递的函数中级班第五讲文本函数作业
第一题
出生日期
出生日期偷懒解法
身份证有15位18位,开始位置都一样是第7位,但范围不同,15位的提取6位,18位的提取8位
MID(A4,7,6+(LEN(A4)=18)*2) 中直接按6+2法提取,当len值不是18时,返回false,=6+false*2 直接返回16
当数据源是18位时,=6+true*2 相当于 6+2 =8
数据源中存在月份大于12的数据,所以加了判断,数据错误
最终公式:
- =IFERROR(--TEXT(MID(A4,7,6+(LEN(A4)=18)*2),"0-00-00"),"数据错误")
复制代码 性别公式: 偷懒减少判断15、18位
直接根据文本函数特性.
1.位数不足的话,只取到最后
2.其中十五位身份证最后一位数字为性别位;十八位身份证倒数第二位数字为性别位。
那将可以直接取递5位:MID(A4,15,3),2),并且连续取3位,当身份证是15位时,直接取15位后三位没有,依旧取的是第15位。
十八位身份证倒数第二位数字为性别位,相当于第17位! {15;16;17 }组,于是MID(A4,15,3) 取3位后刚好是17位
练手操作
SUBSTITUTE 公式【=IF(A4="","",SUBSTITUTE((SUBSTITUTE(MOD(MID(A4,15,3),2),1,"男")),0,"女"))】中if只是判断有没有空行
公式思路是直接把余数【1】替换成【男】,把【0】 替换成【女】,不用if判断
- =CHOOSE =CHOOSE(MOD(MID(A4,15,3),2)+1,"女","男")
复制代码- =LOOKUP =LOOKUP(MOD(MID(A4,15,3),2),{0,"女";1,"男"})
复制代码- =ndex =INDEX({"女";"男"},MOD(MID(A4,15,3),2)+1)
复制代码- =text =TEXT(MOD(MID(A4,15,3),2),"男;;女")
复制代码 第二题
格式1 公式1 =TEXT(B12,"公元yyyy年mm月dd日")
公式2 =TEXT(B12,"公元yyyy年mm月dd日")
格式二 =TEXT(B12,"dd-mm-yyyy")
格式三 =TEXT(B12,"yyyy年mm月dd日 aaaa")
=TEXT(B12,"e年mm月dd日 aaaa")
第三题
接替思路
1 替换第三条横线和第四条横线
wp-103-24传99递19 =SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3)
2 找到“传”位置(mid,第二参数,开始位置)
10 公式 =FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))
3 找到“递”位置(mid,第三参数,结束位置位置)
13 公式 =FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))
4 2者间范围
2 公式 =FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-1
完成公式 99 =FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-1
转成数数值格式 99 公式 =--MID(A8,FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))+1,FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-1)
简化公式 由于中间数据源是固定2位,区间范围的公式(公式可以中间不定位位数)
直接简化【=FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-1 】成2
提取第三条横线,将数据源只要把数据源第三条横替换其他
wp-103-24&99-19 公式=SUBSTITUTE(A8,"-","&",3)
10 公式=FIND("&",SUBSTITUTE(A8,"-","&",3))
最终简化公式1 99 =MID(A8,FIND("&",SUBSTITUTE(A8,"-","&",3))+1,2)
公式2 99 =--MID(SUBSTITUTE(A8,"-",REPT(" ",99)),300,99)
第四题
公式1 =MID(SUM(RIGHT(LARGE(IF(ISNUMBER(MID(1&A10,ROW($1:$20),1)*1),MID(1&A10,ROW($1:$20),1)+ROW($1:$20)*100,0),ROW($1:$20)),1)*10^(ROW($1:$20)-1)),2,100)
公式2 =LOOKUP(9E+307,--MID(A10,MATCH(,0/MID(A10,ROW($1:$99),1),),ROW($1:$99)))
公式3 =-LOOKUP(,-MID(A10,MATCH(,0/MID(A10,ROW($1:$99),1),),ROW($1:$99)))
公式4 =MAX(--TEXT(MID(A10,ROW($1:$99),COLUMN($A:$P)),"0;0;0;!0"))
公式5 =MAX(--TEXT(MID(A10,ROW($99:$9999)/99,MOD(ROW($99:$9999),99)),"0;0;0;!0"))
公式6 =MAX(IFERROR(MID(A10,ROW($1:$15),COLUMN(A:P))*1,0))
公式7 =MAX((TEXT(MID(A10,ROW($1:$15),COLUMN(A:P)),"0;;0;!0")*1))
公式8 =-LOOKUP(1,-RIGHT(LEFT(A10,MATCH(1,-MID(A10,ROW($1:$99),1))),ROW($1:$99)))
公式9 =-LOOKUP(1,-MID(A10,MATCH(,0/MID(A10,ROW($1:$99),1),),ROW($1:$99)))
公式10 =RIGHT(LEFT(A10,MATCH(1,-MID(A10,ROW($1:$99),1))),MATCH(1,-RIGHT(LEFT(A10,MATCH(1,-MID(A10,ROW($1:$99),1))),ROW($1:$99))))
公式11 =MID(A10,MATCH(,0*(MID(A10,ROW($1:$99),1)&0),),COUNT(0*(MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1)&0)))
第五题
A8为例思路
=LOOKUP(MOD(SUM((MID(A8,ROW($1:$17),1))*F26:F42),11),$F$7:$G$17)
把判断最后一位公式带入后判断真假,相关区域锁定 并注意文本和数字间转换
TRUE =--RIGHT(A8,1)=LOOKUP(MOD(SUM((MID(A8,ROW($1:$17),1))*$F$26:$F$42),11),$F$7:$G$17)
嵌套IF判断真假 最终把上面公式做IF函数条件用
真 ‘=IF(--RIGHT(A8,1)=LOOKUP(MOD(SUM((MID(A8,ROW($1:$17),1))*$F$26:$F$42),11),$F$7:$G$17),"真",假)
发现问题
当最后是X 时 转数字会错, 于是换思路 统一将最后值连上空【&""】强制为文本
最终公式为 =IF(RIGHT(A8,1)&""=(LOOKUP(MOD(SUM((MID(A8,ROW($1:$17),1))*$F$26:$F$42),11),$F$7:$G$17)&""),"真","假")
要是公式需要判断是否有空行,再加【IF(A8="",""】
公式2
=MID("真假",(RIGHT(A8)<>INDEX(G$7:G$17,MOD(SUM(MID(A8,ROW($1:$17),1)*D$7:D$23),11)+1)&"")+1,1)
|