|
提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))} 计算卫冕失败最多的次数:{=MAX(FREQUENCY(ROW(2:11),((B2:B10="第一名")<>(B3:B11="第一名"))*ROW(2:10)))} 低于平均成绩中的最优成绩:{=MAX(IF(B2:B11<AVERAGE(B2:B11),B2:B11,))} 计算语文成绩大于90分者的最高总成绩:=DMAX(A1:E11,5,G1:G2) 计算数学成绩等于100分的男生最高总成绩:=DMAX(A1:E11,"总分",B1:B2) 根据下拉列表计算不同项目的最大值:=DMAX(A1:E11,G4,G1:G2) 计算中间成绩:=MEDIAN(B2:B11) 显示动态日期，但不能超过9月30日:=MIN("2008-9-30",TODAY()) 根据工作时间计算可休假天数:=MIN(SUM((B2={"A","B","C"})*{5,4,3})+(C2-1),10) 确定最佳成绩:=MATCH(MIN(B2:B11),B2:B11,) 计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("（?具类",A2:A11)),B2:B11)),"0.00%")} 计算得票最少者有几票:{=MIN(COUNTIF(B2:C11,B2:C11))} 根据工程的难度系数计算奖金:=MIN(A2,1+(A2>1.3)*0.3)*500 将科目与成绩分开:{=MID(A2,MIN(IF(ISNUMBER(FIND(ROW($1:$9),A2)),FIND(ROW($1:$9),A2))),100)} 计算五个班的第一名人员的最低成绩:=MIN(SUBTOTAL(4,INDIRECT({"一","二","三","四","五"}&"班!B2:b11"))) 根据员工生产产品的废品率记分:=MAX(MIN(6-(B2*100-5),10),0) 统计售价850元以上的产品最低利率是多少:=DMIN(A1:D11,F4,F1:F2) 统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2) 第三个最小的成绩:=SMALL(B2:B11,3) 计算最后三名成绩的平均值:=AVERAGE(SMALL(B2:B11,{1,2,3})) 将成绩按升序排列:{=SMALL(B$2:B$11,ROW(A1))} 罗列三个班第一名成绩:{=SMALL(IF(C$2:C$11="第一名",D$2:D$11),ROW(A1))} 将英文月份名称升序排列:{=INDEX(A$2:A$13,SMALL(IF(CODE($A$2:$A$13)=SMALL(CODE(A$2:A$13),ROW(A1)),ROW($1:$12)),COUNTIF(C$1:C1,CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))&"*")+1))} 查看产品曾经销售的所有价位:{=IF(ROW(A1)>SUM(1/COUNTIF(B$2:C$11,B$2:C$11)),"",SMALL(B$2:C$11,1+COUNTIF(B$2:C$11,"<="&E1)))} 罗列三个工作表B列最后三名成绩:=SMALL(一班:三班!B:B,ROW(A1)) 第3个最小成绩到第6个最小成绩之间的人数:{=SUM((((SMALL(B2:D11,ROW(INDIRECT("1:"&COUNT(B2:D11))))>SMALL(B2:D11,{3,6}))*{1,-1})))} 计算与第3个最大值并列的个数:{=SUM(--(B2:B11=LARGE(B2:B11,3)))} 计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11) 按成绩列出学生排行榜:{=INDEX(A$2:A$11,MATCH(LARGE(10-ROW($2:$11)+B$2:B$11*1000,ROW(A1)),10-ROW($2:$11)+B$2:B$11*1000,0))} 最后一次获得第一名是第几届:{=INDEX(A:A,LARGE((B2:B11="第一名")*ROW(2:11),1))} 提取销量的前三名的外销产品名称:{=LOOKUP(0,0/($B$2:$B$10*100+ROW($2:$10)=(LARGE(IF(RIGHT(A$2:A$10,3)="外销）",B$2:B$10*100+ROW($2:$10)),ROW(A1)))),A$2:A$10)} 哪种产品生产次数最多:{=TEXT(MODE(B2:B9*1),"00")} 罗列出被投诉多次的工作人员编号:{=IFERROR(TEXT(MODE(IF(COUNTIF($D$1:D1,$B$2:$B$11)=0,$B$2:$B$11*1)),"00"),"")} 对学生成绩排名:=RANK(B2,B$2:B$11,0) 计算两列数值相同个数:=COUNT(RANK(B2:B11,C2:C11)) 查询某人成绩在三个班中的排名:成绩{=LOOKUP(0,0/(E2:E11=H2),F2:F11)};名次=RANK(I2,(B2:B11,D2:D11,F2:F11),0) 分别统计每个分数段的人员个数:{=FREQUENCY(B2:B11,D2:D5)} 蝉联冠军最多的次数:{=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10<>B$3:B$11)*ROW(B$2:B$10)))} 计算最多经过几次测试才成功:{=MAX(FREQUENCY(ROW(2:11),(B2:B11="成功")*ROW(2:11)))} 计算三个不连续区间的频率分布:{=SUM(LOOKUP({1,3,5},ROW(1:5),FREQUENCY(B2:B11,{500,550,600,650})))} 计算因密码错误被锁定几次:{=COUNT(0/((FREQUENCY(ROW(2:12),(B2:B12<>"错误")*ROW(B2:B12))-1)>=3))} 计算小学加初中人数及中专加大学人数:{=FREQUENCY((B2:B11<>"小学")*(B2:B11<>"初中"),0)} 计算文本的频率分布:{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))} 夺冠排行榜:{=IF(ROW(A1)>SUM(1/COUNTIF($B$2:$B$11,$B$2:$B$11)),"",INDEX($B$2:$B$11,MATCH(LARGE(FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,),ROW($1:$9))-ROW($1:$10)%,ROW(A1)),FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,),ROW($1:$9))-ROW($1:$10)%,)))} 谁蝉联冠军次数最多:=INDEX(B2:B11,MATCH(MAX(FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10))),FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10)),)) 中国式排名:{=SUM(--(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11>B2)))+1} 谁获得第二名:{=INDEX(A:A,SMALL(IF(B$2:B$11=SMALL(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),2),ROW($2:$11),1048576),ROW(A1)))&""} 记录当前日期与时间:=TEXT(NOW(),"m月d日 h:m:s") 确定是否已到加油时间:=TEXT(NOW()-B2,"H:m")>"5:30" 国庆倒计时:=TEXT("10-1"-TEXT(NOW(),"mm-dd"),"00") 统计发货到收款天数:=ROUNDUP(IF(B2<>"",B2-A2,NOW()-A2),0) 统计已到达收款时间的货品数量:=COUNTIF(B2:B10,"<"&(TODAY()-30)) 本月需要完成几批货物生产:{=SUM(N(B2:B11=TEXT(TODAY(),"MMMM")))} 计算本季度收款的合计:{=SUM(IF(ROUNDUP(B2:B11/3,0)=ROUNDUP(TEXT(TODAY(),"M")/3,0),C2:C11))} 判断今年是否闰年:=OR((MOD(TEXT(TODAY(),"yyyy"),4)=0)*(MOD(TEXT(TODAY(),"yyyy"),100)<>0),AND(MOD(TEXT(TODAY(),"yyyy"),{100,400})=0)) 计算2008年有多少个星期日:{=SUM(N(TEXT(DATE(2008,1,ROW(INDIRECT("1:"&("2008-12-31"-"2008-1-1")))),"AAA")="日"))} 计算本月有多少天:=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),"D") 确定今年母亲节的日期:=DATE(YEAR(TODAY()),5,14-WEEKDAY(DATE(YEAR(TODAY()),4,30),2)) 今年包含多少个星期:{=SUM(N(WEEKDAY(DATE(YEAR(TODAY()),1,ROW(INDIRECT("1:"&(365+(DAY(DATE(YEAR(TODAY()),2,29))=29))))),2)=7))+(WEEKDAY(DATE(YEAR(TODAY()),1,365+(DAY(DATE(YEAR(TODAY()),2,29))=29)))<7)} 将身份证号码转换成出生日期序列:=DATE(MID(B2,7,2+(LEN(B2)=18)*2),MID(B2,9+(LEN(B2)=18)*2,2),MID(B2,11+(LEN(B2)=18)*2,2)) 计算建国多少周年:=YEAR(TODAY())-1949 计算2000年前电脑培训平均收费:{=AVERAGE(IF(YEAR(A2:A11)<2000,B2:B11))} 计算今天离本年度最后一天的天数:=(YEAR(TODAY())&"-12-31")-TODAY() 计算本月需要交货的数量:{=SUM((MONTH(B2:B11)=MONTH(TODAY()))*C2:C11)} 计算8月份笔筒和毛笔的进货数量:{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1="笔筒")+(B1:H1="毛笔"),B2:H11)))} 计算交货起止月:{=MIN(MONTH(B2:B11))&"月-"&MAX(MONTH(B2:B11))&"月"} 有几个月要交货:{=COUNT(0/FREQUENCY(MONTH(B2:B11),MONTH(B2:B11)))} 哪几个月要交货:{=IFERROR(SMALL(IF(FREQUENCY(MONTH(B$2:B$11),MONTH(B$2:B$11)),MONTH(B$2:B$11)),ROW(A1))&"月","")} 统计家具类和文具类产品在1月份的出库次数:{=SUM((B2:B11={"文具类","家具类"})*(IF(C2:C11>0,MONTH(C2:C11)=1)))} 计算今年平均每月天数:{=AVERAGE(DAY(DATE(YEAR(TODAY()),ROW(2:13),0)))} 计算员工转正时间:=DATE(YEAR(B2),MONTH(B2)+3+(DAY(B2)>15),16) 统计本月下旬出库数量:{=SUM(C2:C11*(DAY(B2:B11)>20))} 计算生产速度是否达标:=YEARFRAC(C2,D2)<=(E2/B2) 计算截至今天的利息:=B2*D2*YEARFRAC(C2,NOW()) 计算还款日期:=TEXT(EDATE(B2,C2),"yy-mm-dd") 计算2008年到2010年共有多少天:{=SUM(DAY(EDATE("2008-1-31",ROW(1:36)-1)))} 提示合同续约:=TEXT(EDATE(B2,C2*12)-TODAY(),"[<0]合同过期;[<=10]即将到期;;") 计算借款日期到本月底的天数:=EOMONTH(B2,MONTH(TODAY())-MONTH(B2))-B2 计算本季度天数:=SUM(DAY(EOMONTH(NOW(),{0,1,2}-MOD(MONTH(NOW())-1,3)))) 生成工资结算日期:=TEXT(EOMONTH(B2,0)+1,"e年M月D日") 统计两倍工资的加班小时数:=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&EOMONTH(B2,0))),"AAA")="六"))*8 计算员工工作天数和月数:=DATEDIF(B2,C2,"M") 根据进厂日期计算员工可假休天数:=MIN(IF(DATEDIF(B2,TODAY(),"M")<6,0,IF(DATEDIF(B2,TODAY(),"M")<=12,3,3+2*(DATEDIF(B2,TODAY(),"Y")))),15) |