|
串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2)) 给公式添加运算说明:=CONCATENATE("你好",B2,"2008")&T(N("公式含义:连接“你好”和单元格B2、“2008”")) 根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),"[=1]男;[=0]女") 将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))} 将货款显示为“万元”为单位:=TEXT(B2,"￥#"&""""&"."&""""&"#,万元") 根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日") 显示今天的英文日期及星期:="资料日期："&TEXT(TODAY(),"dddd, mmmm dd, yyyy") 显示今天每项工程的预计完成时间:=TEXT(SUM("08:00",B$2:B2),"h:mm:ss 上午/下午") 统计A列有多少个星期日:{=SUM(N(TEXT(A1:A11,"aaa")="日"))} 将数据显示为小数点对齐:=TEXT(B2,"#.0????") 计算A列的日期有几个属于第二季度:{=SUM((--(TEXT(A1:A11,"m"))>{3,6})*{1,-1})} 在A列产生1到12月的英文月份名:=TEXT((ROW())&"-1","mmmm") 将日期显示为中文大写:=TEXT("2008-8-10","[DBNum2]yyyy年m月d日") 将数字金额显示为人民币大写:=IF(MOD(B2,1)=0,TEXT(INT(B2),"[dbnum2]G/通用格式元整;负[dbnum2]G/通用格式元整;零元整;"),IF(B2>0,,"负")&TEXT(INT(ABS(B2)),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),"[dbnum2]0角0分;;"),"零角",IF(ABS(B2)<>0,,"零")),"零分","")) 判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本") 计算达成率，以不同格式显示:=TEXT(B2/800,"[>=1]0.0倍;[>0]0.00%;") 计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH("a",A2&"a"),"[>"&LEN(A2)&"]没找到;第"&SEARCH("a",A2&"a")&"个") 从身份证号码中提取表示性别的数字:=MID(B2,TEXT(LEN(B2),"[=15]15;17"),1) 将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&";"&"!"&B1:B5&";"&A1:A5)} 计算年终奖:=TEXT(B2,"[>3]15!0!0;[>1]1!0!0!0;5!0!0;") 计算星期日完工的工程个数:{=COUNT((TEXT(B2:B10+C2:C10-1,"AAA")="日")^0)} 计算本月星期日的个数:{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),"d")+ROW(INDIRECT("1:"&DAY(DATE(,TEXT(TODAY(),"m")+1,)))),"AAA")="日"))} 检验日期是否升序排列:=TEXT(N(A3>=A2),";;日期有误;") 判断单元格中首字符的类型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),"[="&CODE(A3)&"]字母;;数字;汉字") 计算每个季度的天数:{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),"d"))} 将数据重复显示5次:=SUBSTITUTE(TEXT(A2&"？","@@@@@"),"？","") 将表示起止时间的数字格式化为时间格式:=TEXT(B2,"#!:00-00!:00") 根据起止时间计算经过时间:=TEXT(INT(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60)/60)+MOD(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60),60.1)%,"0小时.00分钟") 将数字转化成电话格式:=TEXT(A2,"(0000)0000-0000") 在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,"DDDD")} 将汇总金额保留一位小数并显示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)} 计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000 将数据对齐显示，将空白以“．”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2) 利用公式制作简易图表:=IF(B2>0,REPT(" ",5)&"｜"&REPT("■",ABS(B2))&B2&REPT(" ",5-ABS(B2)),REPT(" ",5-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&"｜"&REPT(" ",5)) 利用公式制作带轴的图表且标示升降:{=IF(A2<>"",A2&"┫","")&IF(A2="",REPT("〓",(MAX(ABS(B$2:B$8))+6)*2),IF(B2>0,REPT(" ",4+MAX(ABS(B$2:B$8)))&IF(ROW()=2," ",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓")))&REPT("■",ABS(B2))&B2&REPT(" ",4+MAX(ABS(B$2:B$8))-ABS(B2)),REPT(" ",4+MAX(ABS(B$2:B$8))-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&IF(ROW()=1," ",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓"))&REPT(" ",4+MAX(ABS(B$2:B$8))))))} 计算单元格中数字个数:=LEN(A2)*2-LENB(A2) 将数字倒序排列:{=TEXT(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*10^(ROW(INDIRECT("1:"&LEN(A2)))-1)),REPT(0,LEN(A2)))} 计算购物金额中小数位数最多是几:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1} 计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,"'"," ")," ",""))+1 将英文句子规范化:=PROPER(LEFT(A2))&TRIM(RIGHT(A2,LEN(A2)-1)) 分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",100)),COLUMN(A2)*100-99,100)) 提取英文名字:=LEFT(A2,FIND(" ",A2)-1) 将分数转换成小数:=(LEFT(A2,FIND("/",A2)-1)+RIGHT(A2,LEN(A2)-FIND("/",A2)))/2 从英文短句中提取每一个单词:=IFERROR(MID($A2,FIND("~",SUBSTITUTE(" "&$A2&" "," ","~",COLUMN(A2))),FIND("~",SUBSTITUTE(" "&$A2&" "," ","~",COLUMN(B2)))-FIND("~",SUBSTITUTE(" "&$A2&" "," ","~",COLUMN(A2)))),"") 将单位为“双”与“片”混合的数量汇总:{=SUM(IF(ISNUMBER(FIND("/",C2:C9)),(LEFT(C2:C9,FIND("/",C2:C9)-1)+RIGHT(C2:C9,LEN(C2:C9)-FIND("/",C2:C9)))/2,C2:C9*IF(B2:B9="片",0.5,1)))} 提取工作表名:=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) 根据产品规格计算产品体积:=PRODUCT(LEFT(B2,FIND("*",B2)-1),MID(B2,FIND("*",B2)+1,FIND("*",B2,FIND("*",B2)+1)-1-FIND("*",B2)),RIGHT(B2,LEN(B2)-FIND("*",B2,FIND("*",B2)+1))) 提取括号中的字符串:=IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),"") 分别提取长、宽、高:=MID($B2,FIND("@",SUBSTITUTE($B2,"（","@",COLUMN(A1)))+1,FIND("@",SUBSTITUTE($B2,"）","@",COLUMN(A1)))-FIND("@",SUBSTITUTE($B2,"（","@",COLUMN(A1)))-1) 提取学校与医院地址:{=IF(OR(IFERROR(FIND({"学校","医院"},A2),FALSE)),A2,"")} 计算密码字符串中字符个数:{=COUNT(FIND(CHAR(ROW(65:90)),A2),FIND(CHAR(ROW(97:122)),A2),FIND(ROW(1:10)-1,A2))} 通讯录单列转三列:{=MID(INDEX($A:$A,SMALL(IF(IFERROR(FIND(C$1,$A$1:$A$15),FALSE),ROW($1:$15),100000),ROW(A1))),LEN(C$1)+1,100)} 将15位身份证号码升级为18位:{=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7,,19),17)&MID("10X98765432",MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1))} 将产品型号规范化:=IF(MID(A2,5,2)="00",A2,REPLACE(A2,5,,"00")) 求最大时间:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),"00!:00 00-00")),"hmm/dd/mm")} 分别提取小时、分钟、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,) 将年级或者专业与班级名称分开:{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),0)),10,)} |