|
根据身份证号码计算年龄(包括年月天):=CONCATENATE(DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"Y"),"年",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"YM"),"月",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"MD"),"天") 计算年资:=10*MIN(DATEDIF(B2,TODAY(),"y"),15)+MAX(DATEDIF(B2,TODAY(),"y")-15,0)*5 计算临时工的工资:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"分",""),"小时",":"))/(8/24)*50,) 计算本日工时工资:=(HOUR(C2-TIMEVALUE("8:00"))-1-ROUNDUP(B2-TIMEVALUE("8:00"),0))*6 计算8：00一16：00的平均电压:{=AVERAGE(IF((DAY(A2:A11)=8)*(HOUR(A2:A11)>=8)*(HOUR(A2:A11)>=16),B2:B11))} 计算工作时间，精确到分钟:=HOUR(C2)+MINUTE(C2)/60-HOUR(B2)-MINUTE(B2)/60-D2+24*(C2<B2) 根据完工时间计算奖金:=IF(HOUR(B2)>=18,-(ROUNDUP((HOUR(B2-"18:00")*60+MINUTE(B2))/30,0))*3,(ROUNDDOWN((HOUR("18:00"-B2)*60+60-MINUTE(B2))/30,0))*3) 计算工程时间:=SUMPRODUCT(MINUTE(B2:B11)+(SECOND(B2:B11)>0)) 计算今天是星期几:=WEEKDAY(NOW(),2) 汇总星期日的支出金额:{=SUM((WEEKDAY(A2:A11,2)=7)*(B2:B11="支出")*C2:C11)} 汇总第一个星期的出库数量:{=SUM(OFFSET(A2,,MIN(IF(WEEKDAY(B1:P1,2)=1,COLUMN(B:P))),,7))} 计算每日工时工资:=8*5*IF(WEEKDAY(A2,2)<6,1,1.5)+(B2-8)*5*1.5 计算指定日期所在月份有几个星期日:{=SUM(N(WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))))=1))} 按周汇总产量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2))+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)*7))*$B2:$AF2)} 按周汇总进仓与出仓数量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$BK1,2))+INT((COLUMN($B1:$BK1))/2)=(1+(INT((COLUMN(A1)+1)/2)-1)*7))*$B3:$BK3*($B2:$BK2=B7))} 罗列本月休息日:{=IFERROR(SMALL(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))),2)=2,DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))))),ROW()),"")} 计算周末奖金补贴:=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B2&":"&C2))-1,2)>5))*10 罗列值班日期:{=MIN(IF(WEEKDAY(DATE(2008,ROW(),ROW($1:$31)),2)=7,DATE(2008,ROW(),ROW($1:$31))))} 计算本月加班时间:{=SUM((MOD(MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))),2),7),2)={1,0})*{3,2})} 今天是本年度第几周:=WEEKNUM(TODAY()) 本月包括多少周:=WEEKNUM(EOMONTH(NOW(),0),2)-WEEKNUM((EOMONTH(NOW(),-1)+1),2)+1 罗列第30周日期:{=TEXT(SMALL(IF(WEEKNUM(DATE(YEAR(NOW()),1,ROW($1:$366)),2)=30,DATE(YEAR(NOW()),1,ROW($1:$366))),ROW(A1)),"YYYY-MM-DD")} 统计某月第四周的支出金额:{=SUM((WEEKNUM(A2:A11*1,1)-WEEKNUM(YEAR(A2:A11)&"-"&MONTH(A2:A11)&"-1")+1=4)*B2:B11)} 判断本月休息日:{=(SUM(N(WEEKNUM(ROW((INDIRECT((EOMONTH(NOW(),-1)+1)&":"&EOMONTH(NOW(),0)))),2)-WEEKNUM(EOMONTH(NOW(),-1)+1,2)+1=5))>3)+4} 计算离职日期:=WORKDAY(A2,5,{"2008-10-1","2008-10-2","2008-1-3"}) 计算工程完工日期:{=WORKDAY(A2,B2,EOMONTH(A2,ROW(INDIRECT("1:"&INT(B2/30*2)))))} 计算2008年第一季度有多少个工作日:=NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{"2008-2-7","2008-2-8","2008-2-9"}) 计算2008年第一季度有多少个非工作日:=EOMONTH(NOW(),3-MONTH(NOW()))-EOMONTH(NOW(),-MONTH(NOW()))-NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{"2008-2-7","2008-2-8","2008-2-9"}) 计算今天离国庆节还有多少个工作日:=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),10,1)),10,1)) 填充12个月的月份名:=CONCATENATE("第",TEXT(ROW(A1),"[DBNum1]"),"月") 产生“坐标”:=CHAR(64+COLUMN(A1)) 检查日仓库报表日期是否正确:{=IF(SUM(N((11-RANK(A2:A11,A2:A11))=(ROW(2:11)-1)=FALSE)),"非递增","递增")} 检查字符串中哪一个字符出现次数最多:{=CHAR(MODE(IFERROR(CODE(MID(A2,ROW(1:16),1)),"")))} 产生每两行累加1的编号:=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,"")) 最后一次不及格是哪次测试:{=INDEX(A:A,MAX((B2:B11<60)*ROW(2:11)))} 计算第11名到第30名学员的平均成绩:{=AVERAGE(IF(RANK(B2:B101,B2:B101)=TRANSPOSE(ROW(11:30)),B2:B101))} 计算成绩排名，不能产生并列名次:=SUMPRODUCT(--((A$2:A$15=A2)*(($C$2:$C$15)+1/ROW($C$2:$C$15))>C2+1/ROW(2:2)))+1 计算第一次收入金额大于30元时的金额是多少:=INDEX(B:B,MIN(IF((A2:A11=A2)*(B2:B11>30),ROW(2:11)))) 计算扣除所有扣款后的最高薪资:{=MAX(B2:B10-MMULT(C2:G10*1,ROW(1:5)^0))} 对班级和成绩升序排列:{=1*MID(SMALL(1*($A2:$A12&TEXT($B2:$B12,"000")),ROW($A$2:$A$12)-1),{1,2},{1,3})} 罗列今日销售的诺基亚手机型号:{=T(INDEX(B:B,SMALL(IF(ISERROR(FIND("诺基亚",B$2:B$11)),10^6,ROW($2:$11)),ROW(1:1))))} 统计图书数量:{=IF(B2="","",MIN(IF(B3:B$13<>"",ROW(3:$13),13))-ROW())} 罗列第一名学生姓名:{=T(INDEX(A:A,SMALL(IF($B$2:$B$11=MAX(B$2:B$11),ROW($2:$11),12),ROW(A1))))} 罗列1到1000之间的质数:{=INDEX(A:A,SMALL(IF(A$2:A$1000<>"",ROW($2:$1000),1001),ROW(A1)))&""} 判断某数是否为质数:{=IF(A2<2,"非质非合",IF(SMALL(IF(MOD(A2,ROW(INDIRECT("1:"&A2)))=0,ROW(INDIRECT("1:"&A2))),2)=A2,"质数","合数"))} 计算某个数的约数个数及罗列所有约数:约数个数{=COUNT(0/(MOD(A2,ROW(INDIRECT("1:"&A2)))=0))};罗列约数{=IFERROR(SMALL(IF(MOD(A$2,ROW(INDIRECT("1:"&A$2)))=0,ROW(INDIRECT("1:"&A$2))),ROW(A1)),"")} 将六个号码组合成一个:{=SUM(B1:B6*10^(2*(ROWS(B1:B6)-ROW(1:6))))} 将每个人的贷款重新分组:{=INDEX($C:$C,SMALL(IF($A$2:$A$11=$E2,ROW($2:$11),ROWS($1:$12)),COLUMN(A1)))} 检测每个志愿是否与之前的重复:=MATCH(B2,$B$2:$B$10,)<>ROWS($2:2) 将列标转换成数字:=COLUMN(INDIRECT(A2&1)) 重组人事资料表:=REPLACE(INDIRECT("B"&1+(ROW(A1)-1)*4+COLUMN(A:A)),1,LEN(D$1)+1,"") 班级成绩查询:{=INDEX($B:$E,SMALL(IF($A$2:$A$12=$H$2,ROW($2:$12),ROWS($1:$12)+1),ROW(A1)),COLUMN(A1))&""} 罗列每日缺席名单:{=INDEX(全体成员!$1:$1,SMALL(IF(COUNTIF($B2:$K2,全体成员!$A$1:$M$1)=0,COLUMN($A:$M),16384),COLUMN(A1)))&""} 计算所有人的一周产量并排名:{=INDEX(1:1,RIGHT(LARGE(SUBTOTAL(9,OFFSET($A2:$A8,,COLUMN($B:$J)-1,,))*10+COLUMN($B:$J)-1,COLUMN(A1)))+1)} 将金额分散填充，空位以“-”占位:=MID(TEXT(INT($A2*100),REPT("-",9-LEN(INT($A2)))&REPT(0,LEN(INT($A2))+1)),COLUMNS($A:A),1) 提取引用区域右下角的数据:=INDIRECT(ADDRESS(ROW(B3:D7)+ROWS(B3:D7)-1,COLUMN(B3:D7)+COLUMNS(B3:D7)-1)) 整理成绩单:=INDIRECT(CHAR(ROWS($1:22)*3)&COLUMN()) 合并三个工作表的数据:=INDIRECT(CHOOSE(MOD(ROW(A2)-1,3)+1,"一年级!A"&INT((ROW(A3))/3)+1,"二年级!A"&INT((ROW(A3))/3)+1,"三年级!A"&INT((ROW(A3))/3)+1)) 多区域计数:=SUM(COUNTIF(INDIRECT({"C2:C11","F2:F11","I2:I11"}),"<60")) 求积、求和两相宜:=SUM(IF(C2="",INDIRECT("E"&LOOKUP(1,0/ISERROR((0/$C$1:C1="")),ROW($C$2:C2))&":E"&(ROW()-1)),C2*D2)) 计算五个工作表最大平均值:{=MAX(SUBTOTAL(1,INDIRECT({"一","二","三","四","五"}&"班!B2:b11")))} 按卡号中的英文及数值排序:{=INDIRECT("A"&MOD(SMALL(CODE(B$2:B$11)*10000+MID(B$2:B$11,2,9)*100+ROW($2:$11),ROW(B1)),100))} 多行多列取唯一值:{=IF(OR((B$2:D$5<>"")*(COUNTIF(F$1:F1,B$2:D$5)=0)),INDIRECT(TEXT(MIN(IF((B$2:D$5<>"")*(COUNTIF(F$1:F1,B$2:D$5)=0),ROW(B$2:D$5)*1000+COLUMN(B:D))),"r0c???"),),"")} 罗列三个表中的最大值:{=SUBTOTAL(4,INDIRECT({"A组";"B组";"C组"}&"!B2:B11"))} |