特此强调,几乎所有公式都或为B站、或为EH、或为知乎的公式,我只是将其用let函数整合起来
真的特别鸣谢EH网的各位,学到很多,故今天来分享一下办公常用的各类数据提取的函数
好废话不多说,直接上函数
__________________________________________________________________________
身份证提取性别、虚岁、周岁、出生年月、生肖、星座、校验码
- =LET(
- _data,A2:A46,
- _性别,IF(MOD(MID(_data,17,1),2)=0,"女","男"),
- _出生年月,TEXT(MID(_data,7,8),"0-00-00"),
- _周岁,DATEDIF(_出生年月,TODAY(),"Y"),
- _虚岁,_周岁-1,
- _星座,LOOKUP(--TEXT(MID(_data,11,4),"mdd"),{101,"摩羯座";120,"水瓶座";219,"双鱼座";321,"白羊座";420,"金牛座";521,"双子座";621,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1023,"天蝎座";1122,"射手座";1222,"摩羯座"}),
- _生肖,MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(_data,7,4)-4,12)+1,1),
- _校验码,BYROW(_data,LAMBDA(_data,IF(IF(RIGHT(_data,1)="X","X",--RIGHT(_data,1))=VLOOKUP(MOD(SUMPRODUCT(--MID(_data,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1),{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0),"校验通过","校验未通过"))),
- _堆叠公式,HSTACK(_性别,_虚岁,_周岁,_出生年月,_生肖,_星座,_校验码),
- _堆叠名称,VSTACK({"性别","虚岁","周岁","出生年月","生肖","星座","校验码"},_堆叠公式),
- _结果容错,IFERROR(_堆叠名称,""),
- _结果容错
- )
复制代码 数据提取固定长度、提取汉字、提取字母、颠倒字符顺序、统计某字出现的次数等
- =LET(
- _data,A2:A4,
- _提取固定长度的数字,BYROW(_data,LAMBDA(_data,VLOOKUP(0,{0,1}*MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(_data,":",""),",",""),";",""),"、",""),"。",""),"",""),ROW($1:$400),11),2,0))),
- _提取数字及运算符,BYROW(_data,LAMBDA(_data,MID(_data,MIN(FIND({"","-"}&ROW($1:400)-1,_data&"-"&1-ROW($1:400))),1+COUNT(-MID(_data,COLUMN(1:1),2))))),
- _提取0以外数字,BYROW(_data,LAMBDA(_data,CONCAT(IFERROR(IF(--MID(_data,ROW($1:$400),1),MID(_data,ROW($1:$400),1),""),"")))),
- _提取汉字,BYROW(_data,LAMBDA(_data,CONCAT(IF(LENB(MID(_data,ROW($1:$400),1))=2,MID(_data,ROW($1:$400),1),"")))),
- _提取字母,BYROW(_data,LAMBDA(_data,CONCAT(IF(ISNUMBER(--IF(LENB(MID(_data,ROW($1:$400),1))=1,MID(_data,ROW($1:$400),1))),"",IF(LENB(MID(_data,ROW($1:$400),1))=1,MID(_data,ROW($1:$400),1),""))))),
- _颠倒字符顺序,BYROW(_data,LAMBDA(_data,TEXTJOIN("",TRUE,INDEX(SORT(IF({1,0},-ROW($1:$400),MID(_data,ROW($1:$400),1))),,2)))),
- _统计某字出现的次数,BYROW(_data,LAMBDA(_data,"出现"&SUMPRODUCT(1*(MID(_data,COLUMN(1:1),1)="数据"&""))&"次")),
- _提取首字母,BYROW(_data,LAMBDA(_data,Initial(_data))),
- _堆叠公式,HSTACK(_提取固定长度的数字,_提取数字及运算符,_提取0以外数字,_提取汉字,_提取字母,_颠倒字符顺序,_统计某字出现的次数,_提取首字母),
- _堆叠名称,VSTACK({"提取固定长度的数字","提取数字及运算符","提取0以外数字","提取汉字","提取字母","颠倒字符顺序","统计某字出现的次数","提取首字母"},_堆叠公式),
- _结果容错,IFERROR(_堆叠名称,""),
- _结果容错
- )
复制代码
|