{=MMULT(TRANSPOSE(SUBTOTAL(9,OFFSET(B1,ROW(2:11)-1,1,,5))*MMULT((EXACT(B2:B11,TRANSPOSE(单 价表!A2:A5)))*TRANSPOSE(单价表!B2:B5),{1;1;1;1})),1*(A2:A11={\组\组\组\ 引用销售金额高于200次数最多
者:{=INDEX(A:A,RIGHT(MAX(MMULT((B2:H9>200)*1,TRANSPOSE(COLUMN(B:H)^0))*10+ROW(2:9))))} 根据评委评分和权重分配统计最后得
分:{=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6))} 罗 列选手得分前三名的姓名:
{=OFFSET($A1,RIGHT(LARGE(MMULT($B2:$F8*TRANSPOSE($I2:$I6),TRANSPOSE(COLUMN($B:$F)^0))*10^6+ROW(2:8),COLUMN(A1)),2)-1,,)}
根 据字母评语转换得分:{=MMULT(TRANSPOSE(评语换算得分!A$2:A$11=TRANSPOSE(E2:E11))*1,评语换算得 分!B$2:B$11)+SUBTOTAL(9,OFFSET(B2,ROW(2:11)-2,,,COLUMNS(B:D)))} 多列、隔行数据汇总:{=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)^0))*(A2:A11=\赵还珠\计算犯规低于3次的人 数:{=SUM(N(MMULT(--(B2:B21=TRANSPOSE(B2:B21)),ROW(2:21)^0)={1,2})/{1,2})} 提取姓名:=INDEX(B:B,ROW()*2)&\
从电话簿中选择性引用数据:=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A)) 消除厂牌打印资料照片
行:{=INDEX(A:A,SMALL(IF(MOD(ROW($1:$12),3)>0,ROW($1:$12),1048576),ROW(A1)))&\ 罗列优秀员工:{=INDEX(A:A,MOD(SMALL(B$2:B$11*100+ROW($2:$11),ROW(8:8)),100))} 插入空行分割数据:=IF(MOD(ROW(),3)>0,INDEX(A:A,ROW(A2)*2/3),\
仅仅提取通讯录中四分之三信息:=INDEX(A:B,ROW(A2)*2/3,(MOD(ROW(A3),3)+1)/3+1) 罗 列12月中产量倒数第一名次数最多者名单:
{=INDEX(B:B,SMALL(IF((COUNTIF(B$2:B$13,B$2:B$13)=MAX(COUNTIF($B$2:$B$13,$B$2:$B$13)))*(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)))&\ 按 投诉次数升序排列客服姓名:
{=INDEX(B:B,MOD(SMALL(IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,COUNTIF(B$2:B$12,B$2:B$
12)*10^5+IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,ROW($2:$12),9999999),9999999),ROW(A1)),10^5))&\
计算60分到95分之间的人员个数:=INDEX(FREQUENCY(B2:B11,{60,95}),2) 罗 列导致产品不良的主因: {=IFERROR(T(INDEX($A:$A,SMALL(IF($B$2:$B$11=LARGE(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),ROW(A1)),ROW($2:$11)),COLUMN(A1)