|
判断错误类型:=LOOKUP(ERROR.TYPE(A2),ROW(1:7),{"空值错误";"被零除错误";"值错误";"无效的单元格引用";"无效的名称";"数字错误";"值不可用"}) 罗列某运动员九次参赛成绩:{=INDEX($1:$1,MAX(ISTEXT(B2:E2)*COLUMN(B:E)))} 提取每年级第一名名单:=LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1)&":"&LOOKUP(1,0/ISTEXT(B2:E2),B2:E2) 将按日期排列的销售表转换成按品名排列:{=IFERROR(VLOOKUP($A2,IF(MATCH(ROW($1:$15),IF(ISTEXT(日期!$A$1:$A$15),MATCH(日期!$A$1:$A$15,日期!$A$1:$A$15,0)))=MATCH(B$1,日期!$A$1:$A$15,0),日期!$B$1:$C$15),2,0),"")} 按月份统计每个产品的机器返修数量:=SUMPRODUCT(ISNUMBER(FIND(F$2,$A$2:$A$11))*(TEXT($B$2:$B$11,"YM")=TEXT($E3,"YM"))*$C$2:$C$11) 按文字描述求和:{=SUM(ISNUMBER(FIND(A$2:A$8,D2))*B$2:B$8)} 按编码计算库存总数:{=SUM(ISNUMBER(FIND("/"&A$2:A$11*1&"/","/"&D2&"/"))*B$2:B$11)} 从产品规格中提取直径、长、宽:长(直径)=LOOKUP(9.9E+307,--RIGHT(IF(ISNUMBER(FIND("×",A2)),REPLACE(A2,FIND("×",A2),100,""),A2),ROW($1:$100)));宽=IF(ISNUMBER(FIND("×",A2)),--RIGHT(A2,LEN(A2)-FIND("×",A2)),0) 累计每日得分:=(N(C1)=0)*5+N(C1)+IF(B2>0,-B2,0.1) 统计各班所有科目成绩大于60分者人数:{=MMULT(N(TRANSPOSE(A2:A21)=H3:H6),N(COUNTIF(OFFSET(C2:F2,ROW(2:21)-2,),">=60")=4))} 区分大小写统计不重复值个数:{=SUM(N(MMULT(N(EXACT(A2:A11,TRANSPOSE(A2:A11))),ROW(2:11)^0)=TRANSPOSE(ROW(2:11)-1))/TRANSPOSE(ROW(2:11)-1))} 累计每日库存数:=N(G1)+SUM(OFFSET(C$1,ROW(A1)*2-1,,2))-SUM(OFFSET(D$1,ROW(A1)*2-1,,2)) 提取当前工作表名、工作簿名及存放目录:工作表=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"");工作簿=SUBSTITUTE(REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),""),REPLACE(CELL("filename"),1,FIND("]",CELL("filename"))-1,""),"");存放目录=REPLACE(CELL("filename"),FIND("[",CELL("filename")),100,"") 提取第一次参赛取得最佳成绩者姓名与成绩:参赛者{=INDEX(A:A,MOD(MAX((IF(NOT(ISBLANK(C2:C11)),MATCH(A2:A11,A:A,0))=ROW(2:11))*C2:C11*100+ROW(2:11)),100))};成绩{=MAX((IF(NOT(ISBLANK(C2:C11)),MATCH(A2:A11,A:A,0))=ROW(2:11))*C2:C11)} 计算哪一个项目得票最多:{=INDEX({"A","B","C"},RIGHT(MAX(MMULT(TRANSPOSE(ROW(2:11)^0),N(IF(ISBLANK(B2:B11),"A",B2:B11)={"A","B","C"}))*10+{1,2,3})))} 根据利率、存款与时间计算存款加利息数:=FV(B2,D2,-C2,0) 计算七个投资项目相同收益条件下谁投资更少:{=MAX(PV(B2:B8,C2:C8,0,100000))} 根据利息和存款数计算存款达到1万元需要几个月:=NPER(A2,0,-B2,C2)*12 根据投资金额、时间和目标收益计算增长率:=RATE(B2,0,-A2,C2) 根据贷款、利率和时间计算某段时间的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0) 根据贷款、利率和时间计算需偿还的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0) 以固定余额递减法计算资产折旧值:=DB(A$2,B$2,C$2,ROW(A1),12) 以双倍余额递减法计算资产折旧值:=DDB(A$2,B$2,C$2,1,2) 以年限总和折旧法计算折旧值:=SYD(A$2,B$2,C$2,ROW(A1)) 使用双倍余额递减法计算任何期间的资产折旧值:=VDB(A$2,B$2,C$2*12,7,12,2) 获取当前工作簿中工作表数量:=COLUMNS(sheets)&T(NOW()) 建立工作表目录与超级链接:=IFERROR(HYPERLINK(INDEX(sheets,ROW(A1))&"!a1",REPLACE(INDEX(sheets,ROW(A1))&T(NOW()),1,FIND("]",INDEX(sheets,ROW(A1))),"")),"") 选择最后工作表的最后非空单元格:=HYPERLINK(INDEX(sheets,COLUMNS(sheets))&"!A"&LOOKUP(1,0/(INDIRECT(INDEX(sheets,COLUMNS(sheets))&"!A:A")<>""),ROW(1:1048576))) 引用单元格数据同时引用格式:=IF(TODAY()>A2,"",TEXT(A2,格式)) 分别汇总当前表以外的所有工作表数据:AcSht=GET.CELL(62);sheets=GET.WORKBOOK(1);WorkBook=GET.CELL(66);{=IFERROR(REPLACE(INDEX(sheets,SMALL(IF(TRANSPOSE(sheets)<>AcSht,ROW(INDIRECT("1:"&COLUMNS(sheets)))),ROW(A2))),1,LEN(WorkBook)+2,""),"")} 提取单元格的公式:名称=GET.CELL(6,Sheet1!$B1)&T(NOW()) 罗列工作簿中所有名称:{=IFERROR(INDEX(名称,SMALL(IF(名称<>"名称",TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(名称))))),ROW(A1))),"")} 在任意单元格显示当前页数及总页数:无拘无束的页眉="第"&IF(横向当前页=1,纵向当前页,横向当前页+纵向当前页)&"页/共"&总页&"页";纵向当前页=IFERROR(MATCH(ROW(),GET.DOCUMENT(64))+1,1) 提取单元格中的批注:批注=GET.OBJECT(12, "备注 1") 利用列表框筛选数据:筛选=IF(GET.OBJECT(78,"列表框 1"),GET.OBJECT(78,"列表框 1")*TRANSPOSE(ROW(sheet1!$A$2:$A$8))) 判断单元格是否被图形对象覆盖:=ADDRESS(ROW(INDIRECT(左上,0)),COLUMN(INDIRECT(左上,0)))&":"&ADDRESS(ROW(INDIRECT(右下,0)),COLUMN(INDIRECT(右下,0))) 将单元格的公式转换成数值:计算=EVALUATE(Sheet1!A3) 将IP地址补足三位:IP地址=TEXT(EVALUATE("{"&SUBSTITUTE(Sheet1!A4,".",",")&"}"),"000.");QQ=TEXT(EVALUATE("{"&SUBSTITUTE(Sheet1!A4,".",".,")&"}"),"000") 按分隔符取数并求平均:成绩=EVALUATE(SUBSTITUTE("{"&SUBSTITUTE(Sheet1!$B2,"//","/FALSE/")&"}","/",";")) 根据产品规格计算体积:体积=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,"（L）","*"),"（W）","*"),"（H）","")) 计算减肥前后的三国差异:后=EVALUATE("{"&SUBSTITUTE(Sheet1!C3,":",",")&"}"); 前=EVALUATE("{"&SUBSTITUTE(Sheet1!B3,":",",")&"}") 计算各楼层空佘面积:面积=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD1,"[","*istext("""),"]",""")")) 将数据分列，提取省市县:分列=EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(Sheet1!$A5,"省","省"","""),"市","市"",""")&"""}") 按图书编号汇总价格:图书=EVALUATE("{"""&SUBSTITUTE(Sheet1!B2,"/",""",""")&"""}") 标识B列中的重复值:条件格式:=COUNTIF($B:$B,B1)>1 将数据间隔着色:条件格式:=MOD(SUM(N($B$2:$B2<>$B$1:$B1)),2)=0 隐藏错误值:单元格的公式:=VLOOKUP(A2,单价表!$A$2:$B$10,2,0)，条件格式:=ISERROR(B1) 突显前三个最大值:条件格式:=B2>LARGE($B$2:$F$10,4) 将成绩高于平均值的姓名标示“优等”:条件格式:=(B2>AVERAGE($B$2:$F$10))*MOD(COLUMN(),2) 突显奇数行:条件格式:=ISODD(ROW()) 突显非数值:条件格式:=NOT(ISNUMBER(A2))*ISEVEN(COLUMN()) B列中禁止输入重复数据:数据有效性设置-自定义:=COUNTIF(B:B,B8)=1 仅允许录入英文姓名:数据有效性设置-自定义:=SUM(--(ERROR.TYPE(INDIRECT(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1)&1))=3))=LEN(SUBSTITUTE(A2," ","")) 强制录入规范化的日期:数据有效性设置-自定义:=(LEN(A2)=8)*TEXT(A2,"#-00-00") 建立动态下拉选单:定义名称:水果=OFFSET(单价表!$A$1,,,COUNTA(单价表!$A:$A)) 建立二级下拉选单:定义名称:省=OFFSET(参考区!$A$1,,,,COUNTA(参考区!$1:$1));市=OFFSET(参考区!$A$1,1,MATCH(Sheet1!$A$2,参考区!$1:$1,0)-1,COUNTA(OFFSET(参考区!$A$1,1,MATCH(Sheet1!$A$2,参考区!$1:$1,0)-1,1048575))) 建立不重复的下拉选单:{=INDEX(A:A,SMALL(IF(COUNTIF(Sheet1!A$1:A$8,A$1:A$8)=0,ROW($1:$8),1048576),ROW(A2)))&""} (生成不重复单位);定义名称:=OFFSET(名单!$B$1,,,8-COUNTBLANK(名单!$B$1:$B$8)) 让A列只能输入质数:数据有效性设置-自定义:=OR(A2=2,A2=3,PRODUCT(MOD(A2,ROW(INDIRECT("2:"& INT(A2^0.5)))))) 设置D列只能录入男职工的姓名:数据有效性设置-自定义:=VLOOKUP(D2,A:B,2,0)="男" |