当前位置:首页 > Excel2007函数公式收集了688个实例
区!$2:$11)-1,1,1,3)))*1000+ROW(2:11))}
计算第几轮销量最高以及售货员姓名:{=OFFSET(A1,RIGHT(MAX(SUBTOTAL(9,OFFSET(D1,5*(ROW(INDIRECT(\(COUNTA(C:C)/5,1)))))*5-1,)}
提取组名及计算每组平均达标率:{=TEXT(SUBTOTAL(1,OFFSET(B1,((ROW(1:4))*2-1),,,8)),\
判断是否超过一半人达标率在90%以上:{=COUNTIF(OFFSET(B1,((ROW(1:4))*2-1),,,8),\
分别计算每个班第一名的成绩和姓名:名次{=MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*(B$2:B$31=K2))};名{=OFFSET(A$1,MOD(MAX((SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*1000+ROW($2:$31))*(B$2:B$31=K2)),1000)-1,)}
计算哪一个月完成目标:=OFFSET(A1,LOOKUP(,1*(SUBTOTAL(9,OFFSET(B1,1,0,ROW(2:12)-1))>=200),ROW(2:12)),)
有几次连续三个月的平均值低于整体平均值:{=SUM(N((SUBTOTAL(9,OFFSET(B4,ROW(2:11)-2,,3,2))/3 计算10个月中的销售利润并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({\华东区\华南区\华北区\华中区\西南区\-1,)} 将表格转置方向:{=TRANSPOSE(A1:E5)} 对组数进行排名:{=MMULT(N(B2:B11*(IF(LEFT(C2:C11)=\万\万\ 区分大小写提取产品单价:{=MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价表!B2:B5),{1;1;1;1})} 区分大小写查单价且统计三组总金额:{=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)))),\ 按身高对学生排列座次表:{=INDEX($A:$A,MOD(SMALL($C$2:$C$49*1000+ROW($2:$49),(ROW(A1)-1)*6+MOD(COLUMN(A1)-1,6)+1),1000))} 重组教师授课表:{=INDEX(班级!$A:$A,SMALL(IF(班级!$B$2:$D$11=$A3,ROW($2:$11),1048576),COLUMN(C$1)))&\ 提取三个不规则区域的交集:{=INDEX($B:$B,SMALL(IF(COUNTIF(C组!$B$2:$I$2,$B$2:$B$9)*COUNTIF(B组!$C$2:$D$4,$B$2:$B$9),ROW($B$2:$B$9),10),ROW(A4)))&\ 不区分大小写查找单价:=VLOOKUP(B2,单价表!A$2:C$11,3,0)*C2 乱序资料表中查找多个项目:=VLOOKUP($B2,单价表!$A$2:$E$11,MATCH(C$1,单价表!$A$1:$E$1,0),0) 将得分转换成等级:=VLOOKUP(B2,{0,\ 查找美元与人民币报价:=VLOOKUP(B2,INDIRECT(E2&\报价!A2:B9\多条件查找:{=VLOOKUP(A2&B2&C2,IF({1,0},资料表!A2:A11&资料表!B2:B11&资料表!D2:D11,资料表!C2:C11),2,0)} 查找最后更新单价:{=VLOOKUP(10^16,--LEFT(VLOOKUP(B2,单价表!A:Z,COUNTA(INDIRECT(\单价表!A\单价表!A:A,0)&\单价表!A:A,0))),0),ROW($1:$16)),1)} 查找双列信息:{=VLOOKUP(A9,CHOOSE({3,2,1},A1:A6&B1:B6,C1:C6&D1:D6,E1:E6&F1:F6),{2,3},)} 提取姓名拼音的首字母:=VLOOKUP(LEFT(A2),拼音,2)&VLOOKUP(MID(A2,2,1),拼音,2)&VLOOKUP(MID(A2,3,1),拼音,2) 用不确定条件查找:{=VLOOKUP(A2&\资料表!A2:A10,A2)=0,资料表!B2:B10,资料表!A2:A10),资料表!E2:E10),2,0)} 按学历对姓名排序:{=VLOOKUP(MOD(SMALL(MATCH(B$2:B$10,{\大学\高中\初中\小学\} 使用通配符进行查找:{=VLOOKUP(\资料表!B$2:B$9,资料表!A$2:A$9),2,0)} 多工作表查找最大值:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),%um1]\年级!B\,\年级!B\ 对带有合并单元格的区域查找年假天数:=VLOOKUP(F2,OFFSET(B2,MATCH(E2,A2:A13,0)-1,,4,2),2) 查找某业务员在某季度的销量:=HLOOKUP(G2,A1:E9,MATCH(H2,A:A,0),0) 在同一行查找数据:{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)} 计算两个产品不同时期的单价:=HLOOKUP(MONTH(A2),IF(B2=\塑胶机\ 多条件计算加班费:=TEXT(HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2),\>2]6;5\ 根据进厂日期计算有薪假天数:=HLOOKUP(DATEDIF(B2,TODAY(),\ 制作准考证:=HLOOKUP(B2,学生档案库!$1:$11,ROUNDUP(COLUMN()/5,0)+1+INT(ROW()/7)*2,FALSE) 不区分大小写判断两列相同数据个数:{=COUNT(MATCH(A2:A11,B2:B11,0))} 按汉字评语进行排序:{=INDEX(A:B,MOD(SMALL(MATCH($B$2:$B$12,排名标准!$A$2:$A$9,)*100+ROW($B$2:$B$12),ROW(2:12)-1),100),{1,2})} 提取A列最后一个数据:{=INDIRECT(\提取字符串中的汉字:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)>=\ 啊\啊\ 将文件号中的中文大写转小写:{=\第\0)-1)*{100,10,1}),\号文件\ 计算补课科目总数:{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))} 产生混合编号:=TEXT(COUNTIF(C$1:C1,\々\ 提取迟到次数最多者姓名:=INDEX(B2:B11,MODE(MATCH(B$2:B$11,B$2:B$11,0))) 罗列多次迟到者姓名:{=IFERROR(INDEX(B$2:B$11,MODE(IF(COUNTIF(D$1:D1,B$2:B$11)=0,MATCH(B$2:B$11,B$2:B$11,0)))),\ 区分、不区分大小写统计字符个数:{=COUNT(0/(MATCH(MID(A2,ROW($1:$100),1),MID(A2,ROW($1:$100),1),0)=ROW($1:$100)))-1} 按金、银、铜牌排名次:{=MATCH(B2:B11+C2:C11%+D2:D11%%,LARGE(B2:B11+C2:C11%+D2:D11%%,ROW(2:11)-1),0)} 按班级插入分隔行:{=INDEX(A:B,MOD(SMALL(IF({1,0},ROW(2:11)*1001,IF(ROW(2:11)-1=MATCH(A2:A11,A2:A11,0),((MATCH(A2:A11,A2:A11,)+COUNTIF(A2:A11,A2:A11))*1000+100),1048576)),ROW(1:100)),1000),{1,2})&\ 统计一、二班举重参赛人员数:{=COUNT(MATCH(B2:B11&C2:C11,{\一班\二班\举重\ 累计销量并列出排行榜:{=OFFSET($B$1,MATCH(1,N(MAX(IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12)))=IF(COUNTIF($D$1:D1,B$2:B$12)=0,SUMIF(B$2:B$12,B$2:B$12,C$2:C$12))),),)&\ 利用公式对入库表进行数据分析:{=INDEX(B:B,SMALL(IF(MATCH(B$2:B$200,B$2:B$200,0)=ROW($2:$200)-1,ROW($2:$200),65536),ROW(A1)))&\ 罗列每个地区的获奖人员姓名:{=IFERROR(INDEX($A:$A,MATCH(1,(COUNTIF(E$1:E1,$A$2:$A$10)=0)*($B$2:$B$10=E$1),)+1),\ 对合并区域进行数据查询:=OFFSET(B1,MATCH(G2,A2:A13,0)-1+MATCH(H2,{\冰箱\空调\洗衣机\ 将一维人事资料表转二维:{=REPLACE(IFERROR(OFFSET($A$1,MATCH(C$1:F$1&\:*\1,LEN(C$1:F$1)+1,\ 区分大小写查找单价:{=INDEX(B:B,MATCH(0,0/EXACT(E1,A1:A8),0))} 根据姓名查找左边的身份证号:=LOOKUP(E2,B2:B9,A2:A9)
共分享92篇相关文档