精华内容
下载资源
问答
  • EXCEL函数公式大全使用VLOOKUP函数,IF函数和数据验证来通过下拉框自动获得产品价格. Excel函数和公式在工作中经常使用. 是否直接使用公式决定了我们的工作效率. 今天,我们将学习VLOOKUP功能,IF功能和数据验证功能...

    ad476b917ff1499ca15bb337dc43e732.png

    EXCEL函数公式大全使用VLOOKUP函数,IF函数和数据验证来通过下拉框自动获得产品价格. Excel函数和公式在工作中经常使用. 是否直接使用公式决定了我们的工作效率. 今天,我们将学习VLOOKUP功能,IF功能和数据验证功能,这些功能可提高我们的工作效率. 选择产品代码并自动获取产品价格. 在日常工作中,产品的数量和价格是确定的和唯一的. 为了减少我们的数据输入,通过选择产品代码自动获得产品价格和名称. 今天的示例是选择产品代码并自动获取产品名称和单价.

    443c7698e2506589965455dcc1efa786.png

    我们的第一步是使用数据验证为产品编号创建一个下拉框. 选择需要创建下拉框的区域-选择数据-选择数据验证-设置验证条件-允许选择序列-源选择数据源区域.

    1ad5ad6edd51dad07e19a4ae941d63d1163266.jpeg?token=fdd625dba8b26ca9266e514390e8581f&s=5400F61A1F1D44CAD4E9F403009033

    1e282f9774e8d748de6a87b384c3b365.jpg

    使用数据验证使产品编号下拉框的最终结果如下图所示. 您可以根据需要选择相应的产品编号.

    9b2f4780465d3c98ad5dc29e0b754d91.png

    第二步使用VLOOKUP函数获取产品名称. 使用VLOOKUP函数的方法是: 公式----插入函数----输入函数名称VLOOKUP ----单击以转到----单击OK.

    b63a680585c7b8124bbc0db1b15ab03f.png

    3123b02abc3a4db09bd4628be4ff3852.gif

    我们将发现VLOOKUP函数由四个参数组成. 第一个参数指示要在数组第一列中搜索的值. 此参数可以是一个值或该值所在的单元格. 第二个参数指示要搜索的范围. 第三个参数表示该函数返回搜索范围区域中匹配值的序列号. 第四个参数用TRUE或FALSE表示搜索方法,或者用1或0指定搜索方法. 如果是TRUE或1excel数据验证公式,或者省略,则返回近似匹配值;如果是FALSE或0,则精确匹配返回值,如果找不到该值,则返回错误值#N / A.

    b63a680585c7b8124bbc0db1b15ab03f.png

    使用VLOOKUP函数获取产品名称的公式为: VLOOKUP(B3,$ A $ 13: $ C $ 17,2)

    2ea67d97df43892ed2ac3882a41c8fbb.png

    53cb8948d813cf4929341422a1134730.png

    使用VLOOKUP函数获得产品价格的公式为: VLOOKUP(B3,$ A $ 13: $ C $ 17,3)

    3db82c69b6d7c6f8be32ad7459590795.png

    第三步使用IF函数确定产品编号是否为空,产品名称和单价也为空. IF函数的用法是: 公式----插入函数----输入函数名称IF函数----单击以转到----单击OK.

    d56b0c53b1e6317ef6eb040842d99866.png

    2869dd4e78e691267f693b4ff0c3f090.png

    我们将发现IF函数由三个参数组成. 第一个参数表示具有比较运算符的逻辑值指定条件判断公式. 第二个参数表示满足逻辑条件时返回的数据. 第三个条件表示逻辑值不成立时返回的数据.

    a35f851fdc5c0ad4c0e9a9b33c27433b.png

    使用IF函数确定产品编号是否为空的公式为: = IF(B3 =“”,“”,VLOOKUP(B3,$ A $ 13: $ C $ 17,2))

    e6b466d6f5ff949060477431b89f8ebe.png

    好的,这节课就在这里. 谢谢收看我们将在下一期见. 如果您有任何疑问,请给我们留下评论或私信. 如果您想知道一个函数公式excel数据验证公式,可以告诉我们,我们会及时答复您.

    本文来自电脑杂谈,转载请注明本文网址:

    http://www.pc-fly.com/a/tongxinshuyu/article-223811-1.html

    展开全文
  • 出库入库表格函数公式出库入库表格函数公式篇一:二级excel高级操作-停车- 解题步骤《计算机应用基础》课程教学任务参考步骤设计者: 计算机基础教研室例3-2 打开素材文件,按下列要求操作。操作要求(1)在Sheet4的A1...

    出库入库表格函数公式

    出库入库表格函数公式

    篇一:二级excel高级操作-停车- 解题步骤

    《计算机应用基础》课程教学任务参考步骤

    设计者: 计算机基础教研室

    例3-2 打开素材文件,按下列要求操作。

    操作要求

    (1)在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时,提示错误原因,样式为“警告”,错误信息为“只能录入5位数字或文本”。

    (2)在Sheet4的B1单元格中输入公式,判断当前年份是否为闰年,结果为TRUE或FALSE。

    闰年定义:年数能被4整除而不能被100整除,或者能被400整除的年份。

    (3)使用HLOOKUP函数,对Sheet1“停车情况记录表”中的“单价”列进行填充。 要求:根据Sheet1中的“停车价目表”价格,使用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行填充。

    注意:函数中如果需要用到绝对地址的请使用绝对地址进行计算,其他方式无效。

    (4)在Sheet1中,使用数组公式计算汽车在停车库中的停放时间。要求: ?

    ? 计算方法为:“停放时间=出库时间-入库时间” 格式为:“小时:分钟:秒”

    例如:一小时十五分十二秒在停放时间中的表示为:“1:15:12” 将结果保存在“停车情况记录表”中的“停放时间”列中。 ?

    (5)使用函数公式,对“停车情况记录表”的停车费用进行计算。

    要求:根据Sheet1停放时间的长短计算停车费用,将计算结果填入到“停车情况记录表”的“应付金额”列中。

    注意:

    ?

    ? 停车按小时收费,对于不满一个小时的按照一个小时计费; 对于超过整点小时数十五分钟(包含十五分钟)的多累积一个小时。 例如1小时23分,将以2小时计费

    (6)使用统计函数,对Sheet1中的“停车情况记录表”根据下列条件进行统计。 ? 统计停车费用大于等于40元的停车记录条数,并将结果保存在J8单元格中;

    ? 统计最高的停车费用,并将结果保存在J9单元格中。

    (7)将Sheet1中的“停车情况记录表”复制到Sheet2中,对Sheet2进行高级筛选。 要求:

    ? 筛选条件为:“车型”-小汽车,“应付金额”=30;

    ? 将结果保存在Sheet2中。

    注意:

    ? 无需考虑是否删除筛选条件;

    ? 复制过程中,将标题项“停车情况记录表”连同数据一同复制;

    ? 复制数据表后,粘贴时,数据表必须顶格放置。

    (8)根据Sheet1中的“停车情况记录表”,创建一个数据透视图,保存在Sheet3中。 ? 显示各种车型所收费用的汇总;

    ? x坐标设置为“车型”;

    ? 求和项为“应付金额”;

    ? 将对应的数据透视表保存在Sheet3中。

    操作说明

    1. 设置单元格限定录入字数-数据有效性

    根据操作要求(1),打开Excel例3-02文件,选择Sheet4工作表,选择A1单元格,在“数据”选项卡的“数据工具”功能组,点击“”(数据有效性)打开数据有效性对话框对话窗。如图3- 1所示。

    图3- 1选择数据有效性

    在“数据有效性”对话窗口选择“设置”选项卡,设置“有效性条件”允许“文本长度”数据“等于”长度“5”,如图3- 2所示。点击选择“出错警告”项目选项卡,设置“出错样式”为“警告”,在“错误信息”栏下输入“只能录入5位数字或文本”。

    图3- 2设置数据有效性

    2.

    判断闰年

    根据操作要求(2),对于闰年需表达两个可能分别成立的条件,所以使用OR函数。而条件之一“年数能被4整除而不能被100整除”为两个并列条件,需要使用AND函数,这里表达整除需要用到取余函数MOD,而其中的当年年份需要用到年函数YEAR和当日日期函数TODAY。

    选择Sheet4工作表的B1单元格,在“公式”选项卡下“函数库”功能组中点击“”(逻辑),在弹出的逻辑函数列表中选择“OR”函数,在OR“函数参数”对话窗中,条件Logcal1的文本框中输入闰年的第一种判断方式,即“年数能被4整除而不能被100整除”,由于是一组并列条件,嵌入AND函数,将转到AND“函数参数”对话窗,分别在Logical1条件文本框中输入条件式“MOD(YEAR(TODAY()),4)=0”(年份能被4整除),在Logical2条件文本框中输入条件式“MOD(YEAR(TODAY()),100)0”(年份不能能被100整除)。如图3- 3所示。

    图3- 3闰年的并列条件“被4整除而不能被100整除”

    在编辑栏点击AND函数的结尾处(最后一个“)”符号前),回到OR“函数参数”对话窗,在Logical2条件文本框中输入第二个判断条件“能被400整除的年份”的条件式,“MOD(YEAR(TODAY()),400)=0”如图3- 4所示。

    图3- 4通过OR函数表达闰年的两组判断方式

    展开全文
  • Excel2007函数公式实例集

    千次阅读 2011-03-26 13:34:00
    Excel2007函数公式收集了688个实例,涉及到137个函数、7个行业、41类用途,为大家提供一个参考,拓展思路的机会。

    Excel2007函数公式收集了688个实例,涉及到137个函数、7个行业、41类用途,为大家提供一个参考,拓展思路的机会。公式由{}包括的为数组公式,在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键,自动生成数组公式。

    对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)

    对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}

    对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}

    对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)}

    对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10)}

    求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))

    求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)

    求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}

    求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)

    用SUM函数计数:{=SUM((B2:B9="男")*1)}

    求1累加到100之和:{=SUM(ROW(1:100))}

    多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3)))}

    计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)

    计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")

    对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}

    求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4))

    对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2)

    对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)

    汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{"赵","刘","李"}&"*",C2:C10))

    汇总鼠标所在列中大于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")

    只汇总60~80分的成绩:=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,"<=80")

    汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,"三年级",C2:C10,"二班")

    汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,"*车间",B2:B11,"女")

    计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*车间")*{-1,1})

    计算参保人数:=SUMPRODUCT((C2:C11="是")*1)

    求25岁以上男性人数:=SUMPRODUCT((B2:B10="男")*1,(C2:C10>25)*1)

    汇总一班人员获奖次数:=SUMPRODUCT((B2:B11="一班")*C2:C11)

    汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10="一车间男是")*1)

    汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND("车间",A2:A10))),C2:C10)

    汇总业务员业绩:=SUMPRODUCT((B2:B11={"江西","广东"})*(C2:C11="男")*D2:D11)

    根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)

    计算A1:A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}

    根据二边长判断三角形是否为直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1,"非直角","直角")

    计算1到10的自然数的积:=FACT(10)

    计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)

    计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)

    计算每小时生产产值:=PRODUCT(C2:E2)

    根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0.5

    根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,{2,3}))/2

    跨表求积:=PRODUCT(产量表:单价表!B2)

    求不同单价下的利润:{=MMULT(B2:B10,G2:H2)*25%}

    制作中文九九乘法表:=COLUMN()&"*"&ROW()&"="&MMULT(ROW(),COLUMN())

    计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))

    计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}

    计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}

    求入库最多的产品数量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A11={"A","B","C","D"})*1))}

    计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}

    计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}

    计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2:C17-D2:D17))}

    求第一名人员最多有几次:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))}

    求几号选手选票最多:{=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)*100+B2:B10))}

    总共有几个选手参选:{=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)))}

    在不同班级有同名前提下计算学生人数:{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))}

    计算前进中学参赛人数:{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))*(A2:A17="前进中学")),ROW(2:17)^0),0))}

    串联单元格中的数字:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}或=SUMPRODUCT(B2:K2,10^(COLUMNS(B:K)-COLUMN(B:K)-1))

    计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}

    计算成绩在60-80分之间合计数与个数:求和{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)},求个数{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)}

    汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}

    计算象棋比赛对局次数l:=COMBIN(B1,B2)

    计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))}

    预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60

    计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)

    计算中奖率:=TEXT(1/PERMUT(B1,B2),"0.00%")

    计算最大公约数:=GCD(B1:B5)

    计算最小公倍数:=LCM(B1:B5)

    计算余数:=MOD(A2,B2)

    汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)

    根据单价数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)

    设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),""))

    根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),"男","女")

    每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)

    工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)

    汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}

    将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)

    计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1

    秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))),60^(3-COLUMN(A:A)))

    生成隔行累加的序列:=QUOTIENT(ROW()+1,2)

    根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2

    计算预报温度与实际温度的最大误差值:{=MAX(ABS(C2:C8-B2:B8))}

    计算个人所得税:=ROUND(0.05*SUM(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,100000}+ABS(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,100000}))/2,0)

    产生100到200之间带小数的随机数:=RAND()*(200-100)+100

    产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10}

    将20个学生的考位随机排列:{=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))}

    将三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&":"&OFFSET(B$1,RANK(G2,G$2:G$11),)&":"&OFFSET(C$1,RANK(G2,G$2:G$11),)

    产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)

    产生1到100之问的奇数随机数:{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100))}

    产生1到10之间随机不重复数:{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBETWEEN(1,12-ROW()))}

    根据三角形三边长求证三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,{2,3}),2)),"是","不是")

    计算Al:A10区域开三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))}

    计算Al:A10区域倒数之积:{=PRODUCT(POWER(A1:A10,-1))}

    根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3))

    抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)))

    统计A1:B10区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)))

    统计参考人数:=SUMPRODUCT((EVEN(COLUMN(A1:J12))=COLUMN(A1:J12))*(MOD(ROW(A1:J12),3)=1)*(A1:J12<>""))

    计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)))

    合计购物金额、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)

    将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1))

    将金额进行四舍六入五单双:=IF((A2-TRUNC(A2,1))<=0.04,TRUNC(A2,1),IF((A2-TRUNC(A2,1))>=0.06,TRUNC(A2,1)+0.1,TRUNC((TRUNC(A2,1)+0.1)/2,1)*2))

    根据重量单价计算金额,结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000

    计算年假天数:=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5)

    根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)

    将金额见角进元与见分进元:见分进元=CEILING(TRUNC(A2,2),1);见角进元=CEILING(TRUNC(A2,1),1)

    分别统计收支金额并忽略小数:收入合计=SUMPRODUCT(INT(B2:B8));支出合计=SUMPRODUCT(TRUNC(C2:C8))

    成绩表的格式转换:姓名=INDEX(A:A,INT((ROW(A6))/3));科目=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1);成绩=INDEX($B$2:$D$7,INT((ROW(A1)-1)/3)+1,MOD((ROW(A1)-1),3)+1)

    隔两行进行编号:=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),"")

    INT函数在序列中的复杂运用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9);=INT((ROW(A2))*2/3)

    统计交易损失金额:=SUMPRODUCT(B2:B11-CEILING(B2:B11,0.1))

    根据员工工龄计算年资:=C2+CEILING(B2*30,30)*(INT(B2)>0)

    成绩表转换:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1)

    计算机上网费用:=CEILING(B2,30)/30*2

    统计可组建的球队总数:=SUMPRODUCT(FLOOR(B2:B10,5)/5)

    统计业务员提成金额,不足20000元忽略:=FLOOR(B2,20000)/20000*500

    FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A1>0,1,-10)))

    将数据转换成接近6的倍数:=MROUND(A1,6)

    以超产80为单位计算超产奖:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50}

    将统计金额保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)

    将统计金额转换成以万元为单位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%%,)

    对单价计量单位不同的品名汇总金额:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="G",1000,1),(D2:D10="G")*2))}

    将金额保留“角”位,忽略“分”位:{=SUM(ROUNDDOWN(B2:B10*C2:C10,1))}

    计算需要多少零钞:{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})}

    计算值为l万的整数倍数的数据个数:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))}

    计算完成工程需求人数:{=SUM(ROUNDUP(B2:B11/C2:C11,))}

    按需求对成绩进行分类汇总:=SUBTOTAL(HLOOKUP(G$1,{"平均成绩","科目数量","最高成绩","最低成绩","成绩合计";1,2,4,5,9},2,0),B2:D2)

    不间断的序号:=SUBTOTAL(103,$B$2:B2)

    仅对筛选出的人员排名次:{=CONCATENATE("第",SUM(N(IF((SUBTOTAL(103,OFFSET(优等生!A$1,ROW($2:$31)-2,)))=1,$C$2:$C$31,)>C2))+1,"名")}

    判断两列数据是否相等:

    计算两列数据同行相等的个数:{=SUM(N(A1:A10=B1:B10))}

    计算同行相等且长度为3的个数:{=SUM((A1:A10=B1:B10)*(LEN(A1:A10)=3))}

    提取A产品最后单价:{=INDEX(C:C,MAX((B2:B10="A")*ROW(2:10)))}

    判断学生是否符合奖学金发放条件:=AND(B2>90,C2<>"汉族")

    所有裁判都给“通过”就进入决赛:{=AND(B2:E2="通过")}

    判断身份证长度是否正确:=OR(LEN(B2)={15,18})

    判断歌手是否被淘汰:{=OR(B2:E2="不通过")}

    根据年龄判断职工是否退休:=OR(AND(B2="男",C2>60),AND(B2="女",C2>55))

    根据年龄与职务判断职工是否退休:=OR(AND(B2="男",D2>60+(C2="干部")*3),AND(B2="女",D2>55+(C2="干部")*3))

    没有任何裁判给“不通过”就进行决赛:{=NOT(OR(B2:E2="不通过"))}

    计彝成绩区域数字个数:{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)}

    评定学生成绩是否及格:=IF(AVERAGE(B2:D2)>=60,"及格","不及格")

    根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)<60,"不及格",IF(AVERAGE(B2:D2)<90,"良好",IF(AVERAGE(B2:D2)<100,"优秀","满分")))

    根据业绩计算需要发放多少奖金:{=SUM(IF(B2:B11>80000,1000,500))}

    根据工作时间计算12月工资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))

    合计区域的值并忽略错误值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))}

    既求积也求和:=IF(D2<>"",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3)))

    分别统计收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"",B2:B13,0),"负","-")*1<0,SUBSTITUTE(B2:B13,"负","-")*1))}

    将成绩从大到小排列:{=IF(ROW(A1)>COUNT(B$2:B$11),"",LARGE(B$2:B$11,ROW(A1)))}

    排除空值:{=INDEX($A:$B,SMALL(IF($B$1:$B$11<>"",ROW($1:$11),ROWS($1:$11)+1),ROW()),COLUMN(B2))&""}

    有选择地汇总数据:{=SUM(IF(A2:A11={"A组","C组"},C2:C11))}

    混合单价求金额合计:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="K",1000,1),2))}

    计算异常停机时间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"",C2:C11,0),"修机",""),"换原料","")*1)}

    计算最大数字行与文本行:{=MAX(IF(B:B<>"",ROW(A:A)))}

    找出谁夺冠次数最多:{=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12))=COUNTIF(B2:B12,B2:B12),ROW(2:12))))}

    将全角字符转换为半角:=ASC(A2)

    计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2))*2-LENB(ASC(A2))

    将半角字符转换成全角显示:=WIDECHAR(A2)

    计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2)))

    判断单元格首字符是否为字母:=OR(AND(CODE(A2)>64,CODE(A2)<91),AND(CODE(A2)>96,CODE(A2)<123))

    计算单元格中数字个数:{=SUM((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>47)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<58))}

    计算单元格中大写加小写字母个数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))>64)*(CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))<91))}

    产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65)),小写字母=CHAR(ROW(A65)+32)

    产生大写字母A到ZZ的字母序列:=IF(ROW()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+(ROW()-1)/26-1))&IF(ROW()>26,CHAR(MOD(ROW()-1,26)+65),"")

    产生三个字母组成的随机字符串:=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))

    用公式产生换行符:=A2&CHAR(10)&B2

    将数字转换成英文字符:字符码=RANDBETWEEN(1,100),升序位置=CHAR(MOD(A1-1,26)+65)

    将字母升序排序:{=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))}

    返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))

    根据身份证号码提取出生年月日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15)),"年",MID(B2,11-2*(LEN(B2)=15),2),"月",MID(B2,13-2*(LEN(B2)=15),2),"日 ")

    计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),": ",IF(AVERAGE(B2:D2)>=60,"","不"),"及格")

    提取前三名人员姓名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1)),A2:A11),"|",LOOKUP(0,0/(B2:B11=LARGE(B2:B11,2)),A2:A11),"|",(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,3)),A2:A11)))

    将单词转换成首字母大写:=PROPER(A2)

    将所有单词转换成小写形式:=LOWER(A2)

    将所有句子转换成首字母大写其余小写:=CONCATENATE(PROPER(LEFT(A2)),LOWER(RIGHT(A2,LEN(A2)-1)))

    将所有字母转换成大写形式:=UPPER(A2)

    计算字符串中英文字母个数:{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))))}

    计算字符串中单词个数:{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1)),MID(PROPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))))}

    将文本型数字转换成数值:{=SUM(VALUE(B2:B10))}

    计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1))))

    提取混合字符串中的数字:{=MAX(IFERROR(VALUE(MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2))))),0))}

    串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2))

    给公式添加运算说明:=CONCATENATE("你好",B2,"2008")&T(N("公式含义:连接“你好”和单元格B2、“2008”"))

    根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),"[=1]男;[=0]女")

    将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}

    将货款显示为“万元”为单位:=TEXT(B2,"¥#"&""""&"."&""""&"#,万元")

    根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日")

    显示今天的英文日期及星期:="资料日期:"&TEXT(TODAY(),"dddd, mmmm dd, yyyy")

    显示今天每项工程的预计完成时间:=TEXT(SUM("08:00",B$2:B2),"h:mm:ss 上午/下午")

    统计A列有多少个星期日:{=SUM(N(TEXT(A1:A11,"aaa")="日"))}

    将数据显示为小数点对齐:=TEXT(B2,"#.0????")

    计算A列的日期有几个属于第二季度:{=SUM((--(TEXT(A1:A11,"m"))>{3,6})*{1,-1})}

    在A列产生1到12月的英文月份名:=TEXT((ROW())&"-1","mmmm")

    将日期显示为中文大写:=TEXT("2008-8-10","[DBNum2]yyyy年m月d日")

    将数字金额显示为人民币大写:=IF(MOD(B2,1)=0,TEXT(INT(B2),"[dbnum2]G/通用格式元整;负[dbnum2]G/通用格式元整;零元整;"),IF(B2>0,,"负")&TEXT(INT(ABS(B2)),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),"[dbnum2]0角0分;;"),"零角",IF(ABS(B2)<>0,,"零")),"零分",""))

    判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本")

    计算达成率,以不同格式显示:=TEXT(B2/800,"[>=1]0.0倍;[>0]0.00%;")

    计算字母“A”的首次出现位置,忽略大小写:=TEXT(SEARCH("a",A2&"a"),"[>"&LEN(A2)&"]没找到;第"&SEARCH("a",A2&"a")&"个")

    从身份证号码中提取表示性别的数字:=MID(B2,TEXT(LEN(B2),"[=15]15;17"),1)

    将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&";"&"!"&B1:B5&";"&A1:A5)}

    计算年终奖:=TEXT(B2,"[>3]15!0!0;[>1]1!0!0!0;5!0!0;")

    计算星期日完工的工程个数:{=COUNT((TEXT(B2:B10+C2:C10-1,"AAA")="日")^0)}

    计算本月星期日的个数:{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),"d")+ROW(INDIRECT("1:"&DAY(DATE(,TEXT(TODAY(),"m")+1,)))),"AAA")="日"))}

    检验日期是否升序排列:=TEXT(N(A3>=A2),";;日期有误;")

    判断单元格中首字符的类型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),"[="&CODE(A3)&"]字母;;数字;汉字")

    计算每个季度的天数:{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),"d"))}

    将数据重复显示5次:=SUBSTITUTE(TEXT(A2&"?","@@@@@"),"?","")

    将表示起止时间的数字格式化为时间格式:=TEXT(B2,"#!:00-00!:00")

    根据起止时间计算经过时间:=TEXT(INT(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60)/60)+MOD(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60),60.1)%,"0小时.00分钟")

    将数字转化成电话格式:=TEXT(A2,"(0000)0000-0000")

    在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,"DDDD")}

    将汇总金额保留一位小数并显示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)}

    计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000

    将数据对齐显示,将空白以“.”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2)

    利用公式制作简易图表:=IF(B2>0,REPT(" ",5)&"|"&REPT("■",ABS(B2))&B2&REPT(" ",5-ABS(B2)),REPT(" ",5-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&"|"&REPT(" ",5))

    利用公式制作带轴的图表且标示升降:{=IF(A2<>"",A2&"┫","")&IF(A2="",REPT("〓",(MAX(ABS(B$2:B$8))+6)*2),IF(B2>0,REPT(" ",4+MAX(ABS(B$2:B$8)))&IF(ROW()=2," ",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓")))&REPT("■",ABS(B2))&B2&REPT(" ",4+MAX(ABS(B$2:B$8))-ABS(B2)),REPT(" ",4+MAX(ABS(B$2:B$8))-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&IF(ROW()=1," ",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓"))&REPT(" ",4+MAX(ABS(B$2:B$8))))))}

    计算单元格中数字个数:=LEN(A2)*2-LENB(A2)

    将数字倒序排列:{=TEXT(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*10^(ROW(INDIRECT("1:"&LEN(A2)))-1)),REPT(0,LEN(A2)))}

    计算购物金额中小数位数最多是几:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}

    计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,"'"," ")," ",""))+1

    将英文句子规范化:=PROPER(LEFT(A2))&TRIM(RIGHT(A2,LEN(A2)-1))

    分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",100)),COLUMN(A2)*100-99,100))

    提取英文名字:=LEFT(A2,FIND(" ",A2)-1)

    将分数转换成小数:=(LEFT(A2,FIND("/",A2)-1)+RIGHT(A2,LEN(A2)-FIND("/",A2)))/2

    从英文短句中提取每一个单词:=IFERROR(MID($A2,FIND("~",SUBSTITUTE(" "&$A2&" "," ","~",COLUMN(A2))),FIND("~",SUBSTITUTE(" "&$A2&" "," ","~",COLUMN(B2)))-FIND("~",SUBSTITUTE(" "&$A2&" "," ","~",COLUMN(A2)))),"")

    将单位为“双”与“片”混合的数量汇总:{=SUM(IF(ISNUMBER(FIND("/",C2:C9)),(LEFT(C2:C9,FIND("/",C2:C9)-1)+RIGHT(C2:C9,LEN(C2:C9)-FIND("/",C2:C9)))/2,C2:C9*IF(B2:B9="片",0.5,1)))}

    提取工作表名:=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

    根据产品规格计算产品体积:=PRODUCT(LEFT(B2,FIND("*",B2)-1),MID(B2,FIND("*",B2)+1,FIND("*",B2,FIND("*",B2)+1)-1-FIND("*",B2)),RIGHT(B2,LEN(B2)-FIND("*",B2,FIND("*",B2)+1)))

    提取括号中的字符串:=IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),"")

    分别提取长、宽、高:=MID($B2,FIND("@",SUBSTITUTE($B2,"(","@",COLUMN(A1)))+1,FIND("@",SUBSTITUTE($B2,")","@",COLUMN(A1)))-FIND("@",SUBSTITUTE($B2,"(","@",COLUMN(A1)))-1)

    提取学校与医院地址:{=IF(OR(IFERROR(FIND({"学校","医院"},A2),FALSE)),A2,"")}

    计算密码字符串中字符个数:{=COUNT(FIND(CHAR(ROW(65:90)),A2),FIND(CHAR(ROW(97:122)),A2),FIND(ROW(1:10)-1,A2))}

    通讯录单列转三列:{=MID(INDEX($A:$A,SMALL(IF(IFERROR(FIND(C$1,$A$1:$A$15),FALSE),ROW($1:$15),100000),ROW(A1))),LEN(C$1)+1,100)}

    将15位身份证号码升级为18位:{=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7,,19),17)&MID("10X98765432",MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1))}

    将产品型号规范化:=IF(MID(A2,5,2)="00",A2,REPLACE(A2,5,,"00"))

    求最大时间:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),"00!:00 00-00")),"hmm/dd/mm")}

    分别提取小时、分钟、秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)

    将年级或者专业与班级名称分开:{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),0)),10,)}

    提取各软件的版本号:=REPLACE(REPLACE(A2,1,SEARCH("(",A2),),LEN(REPLACE(A2,1,SEARCH("(",A2),)),1,)

    店名分类:=IF(COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2))=1,"餐饮娱乐",IF(COUNT(SEARCH({"干洗","医院","药","茶","蛋糕","面包","物流","驾校","开锁","家政","装饰","搬家","维修","中介","卫生","旅馆"},A2))=1,"便民服务",IF(COUNT(SEARCH({"游乐场","旅行社","旅游"},A2))=1,"旅游")))

    查找编号中重复出现的数字:重复数字个数{=COUNT(SEARCH((ROW($1:$10)-1)&"*"&(ROW($1:$10)-1),A2))};重复字符=IF(COUNT(SEARCH("0*0",A2)),0,"")&SUBSTITUTE(SUMPRODUCT(ISNUMBER(SEARCH(ROW($1:$9)&"*"&ROW($1:$9),A2))*ROW($1:$9)*10^(9-ROW($1:$9))),0,)

    统计名为“刘星”者人数:{=COUNT(SEARCH("?刘星",A2:A9))}

    剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH("重庆市",A2)),"","四川省"),"")

    将日期规范化再求差:=SUBSTITUTE(C2,".","-")-SUBSTITUTE(B2,".","-")

    提取两个符号之间的字符串:=TRIM(MID(SUBSTITUTE(B2,"*",REPT(" ",50)),FIND("*",B2),100))

    产品规格格式转换:=SUBSTITUTE(SUBSTITUTE(A2,":","("),"*",")*")&")"

    判断调色配方中是否包含色粉“B”:=LEN(SUBSTITUTE(B2,"B",""))<>LEN(B2)

    提取姓名与省份:=TRIM(MID(A2,1,FIND("|",A2)-1)&MID(SUBSTITUTE(A2,"|",REPT(" ",100)),500,100))

    将IP地址规范化:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("."&A2,".0","."),".0","."),".","",1)

    提取最后一次短跑成绩:=REPLACE(A2,1,FIND("々",SUBSTITUTE(A2,"|","々",LEN(A2)-LEN(SUBSTITUTE(A2,"|",)))),)

    从地址中提取省名:=LEFT(A2,FIND("省",A2))

    计算小学参赛者人数:{=COUNT(0/(LEFT(B2:B11)="小"))}

    计算四川方向飞机票总价:=SUMPRODUCT(N(LEFT(A2:A11,2)="四川"),N(B2:B11="飞机"),C2:C11)

    通过身份证号码计算年龄:=TEXT(TODAY(),"YYYY")-(IF(LEN(B2)=18,"",19)&LEFT(REPLACE(B2,1,6,""),2+(LEN(B2)=18)*2))

    从混合字符串中取重量:=LOOKUP(9E+307,--LEFT(B2,ROW($1:$10)))*C2

    将金额分散填充:=LEFT(RIGHT(" ¥"&$A2*100,13-COLUMN()))

    提取成绩并计算平均:{=AVERAGE(MID(A2:A7,4,LEN(A2:A7)-3)*1)}

    提取参赛选手姓名:=MID(A2,FIND(":",A2)+1,LEN(A2))

    从混合字符串中提取金额:=LOOKUP(307,--MID(B2,MIN(FIND({1;2;3;4;5;6;7;8;9},B2&123456789)),ROW($1:$99)))

    从卡机数据提取打卡时间:=730>--MID(A2,14,4)

    根据卡机数据判断员工部门:=CHOOSE(MATCH(--RIGHT(A2,3),{1,38,14,11,8,21,43,9,28},0),"生产部","业务部","总务部","人事部","食堂","保卫部","采购部","送货部","财务部")

    根据身份证号码统计男性人数:{=SUM(MOD(LEFT(RIGHT(B2:B11,1+(LEN(B2:B11)=18))),2))}

    从汉字与数字混合字串中提取温度数据:{=MAX(IFERROR(--RIGHT(LEFT(B2,LEN(B2)-1),ROW($1:$10)),0))}

    将字符串位数统一:{=TEXT(RIGHT(A2,LEN(A2)-1),"!"&LEFT(A2)&REPT(0,MAX(LEN(A$2:A$10))-1))}

    对所有人员按平均分排序:{=INDEX(A:A,RIGHT(LARGE(B$2:B$11*1000+ROW($2:$11),ROW()-1),3))}

    取金额的角位与分位叫:=--RIGHT(ROUND(A2*100,),2)

    从格式不规范的日期中取出日:=TRIM(RIGHT(SUBSTITUTE(A2,".","   ",2),3))

    计算平均成绩(忽略缺考人员):=ROUND(AVERAGE(B2:B10),2)

    计算90分以上的平均成绩:{=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B10>90),B2:B10)),2)}

    计算当前表以外的所有工作表平均值2:=AVERAGE(一班:五班!B:B)

    计算二车间女职工的平均工资:{=AVERAGE(IF((B2:B10="二车间")*(C2:C10="女"),D2:D10))}

    计算一车间和三车间女职工的平均工资:{=AVERAGE(IF((B2:B10="一车间")+(B2:B10="三车间")*(C2:C10="女"),D2:D10))}

    计算各业务员的平均奖金:{=AVERAGE(1500+300*(INT((C2:C11-80000)/10000)))}

    计算平均工资(不忽略无薪人员):=ROUND(AVERAGEA(B2:B10),2)

    计算每人平均出口量:{=AVERAGEA((C2:C11="A")*D2:D11)}

    计算平均成绩,成绩空白也计算:{=AVERAGEA(B2:B11*1)}

    计算二年级所有人员的平均获奖率:{=TEXT(AVERAGEA(IF(LEFT(A2:A10,3)="二年级",B2:B10/C2:C10)),"0.00%")}

    统计前三名人员的平均成绩:=AVERAGEA(LARGE(B2:B11,{1,2,3}))

    求每季度平均支出金额:=AVERAGEIF(B2:B9,"支出",C2)

    计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250")

    去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,">"&MIN(B2:B11),B2:B11,"<"&MAX(B2:B11))

    生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"")

    计算生产车间异常机台个数:=COUNT(C2:C11)

    计算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),"0.00%")}

    统计属于餐饮娱乐业的店名个数:{=COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2:A11))}

    统计各分数段人数:{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))}

    统计有多少个选手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))}

    统计出勤异常人数:=COUNTA(B2:B11)

    判断是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),"没有","有")

    统计未检验完成的产品数:=COUNTBLANK(B2:B11)

    统计产量达标率:=TEXT(COUNTIF(B2:B11,">=800")/COUNT(B2:B11),"0.00")

    根据毕业学校统计中学学历人数:=COUNTIF(B2:B11,"*中学")

    计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))}

    统计连续三次进入前十名的人数:{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))}

    统计淘汰者人数:{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))}

    统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))}

    统计诺基亚、摩托罗拉和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,"*"&{"诺基亚","摩托罗拉","联想"}&"*"))

    统计联想比摩托罗拉手机的销量高多少:{=SUM(COUNTIF(B2:B11,{"诺基亚*","*联想*"})*{1,-1})}

    统计冠军榜前三名:{=INDEX(B:B,SMALL(IF(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1))>=LARGE(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1)),3),ROW($2:$12)),ROW(A1)))}

    统计真空、假空单元格个数:=COUNTIF(成绩!C2:C11,"=")

    对名册表进行混合编号:=IF(RIGHT(B1)<>"班",ROW()-COUNTIF($B$1:B1,"??班"),TEXT(COUNTIF($B$1:B1,"??班"),"[DBNum2]0"))

    提取不重复数据5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)}

    中国式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1}

    统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,"三好学生",C2:C11,">80")}

    统计业绩在6万到8万之间的女业务员个数:=COUNTIFS(B2:B11,"女",C2:C11,">60000",C2:C11,"<=800000")

    统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{"二班","三班"},C2:C11,"数学*"))

    根据身高计算各班淘汰人数:=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{"<160",">180"}))

    计算A列最后一个非空单元格行号:{=MAX((A:A<>"")*ROW(A:A))}

    计算女职工的最大年龄:{=MAX((B2:B11="女")*C2:C11)}

    消除单位提取数据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)="-",-1,1)}

    计算单日最高销售金额:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))}

    查找第一名学生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,))

    统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}

    根据达标率计算员工奖金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550})

    提取产品最后报价和最高报价:{=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)

    根据身份证号码计算年龄(包括年月天):=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"))}

    将三列课程转换成单列且忽略空值:{=INDIRECT(TEXT(SMALL(IF($B$2:$D$7<>"",ROW($2:$7)*1000+1,1048576001),ROW(A1)),"r#c000"),)&""}

    罗列两个正整数的所有公约数:{=IFERROR(SMALL(IF((MOD(A$2,ROW(INDIRECT("1:"&GCD(A$2:B$2))))=0)*(MOD(B$2,ROW(INDIRECT("1:"&GCD(A$2:B$2))))=0),ROW(INDIRECT("1:"&GCD(A$2:B$2)))),ROW()-1),"")}

    B列最大值的地址:{=ADDRESS(MAX(IF(B2:B11=MAX(B2:B11),ROW(2:11))),2)}

    记录最后一次销量大于3000的地址:{=ADDRESS(MOD(MAX((IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*COLUMN(B2:D7)*1000),1000),INT(MAX((IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*ROW(B2:D7)+(IF(ISNUMBER(B2:D7),B2:D7,0)>3000)*COLUMN(B2:D7)*1000)/1000))}

    根据下拉列表引用不同工作表的产量:=INDIRECT(ADDRESS(11,2,1,1,D1))

    根据下拉列表罗列班级成绩第一名姓名:{=IFERROR(INDIRECT(ADDRESS(LARGE(((INDIRECT(D$1&"!B2:B10")=MAX(INDIRECT(D$1&"!B2:B10")))*ROW($2:$10)),ROW(A1)),1,1,1,D$1)),"")}

    查询成绩:=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0))

    在具有合并单元格的A列产生自然数编号:=1+COUNT(OFFSET($A$2,,,ROW()-2,))

    引用合并区域时防止产生0值:=IF(A1<>"",A1,OFFSET(B1,-1,))

    计算10届运动会中有几次破纪录:=SUMPRODUCT(N(SUBTOTAL(5,OFFSET(B2,,,ROW(2:10)))<SUBTOTAL(5,OFFSET($B$2,,,ROW(1:9)))))

    计第奎续三天之总产量大于等于25万元的次数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))>=25))

    进、出库合计查询:=SUM(OFFSET(A1,E2,MATCH(G2&"总计",B1:C1,0),F2-E2+1))

    根据人数自动调整表格大小:{=IFERROR(OFFSET($E$1,SMALL(IF(F$2:F$5>=TRANSPOSE(ROW(INDIRECT("1:"&MAX(F$2:F$5)))),ROW($2:$5)-1),ROW(1:1)),),"")}

    累计数据:{=SUM(OFFSET(B$2,,,ROW()-1))}

    计算至少两科不及格的学生人数:{=SUM(--(COUNTIF(OFFSET($B$1,ROW(2:11)-1,,,4),"<60")>=2))}

    列出成绩最好的科目:{=OFFSET(A2,,SUM((MAX(SUBTOTAL(9,OFFSET(A2,1,ROW(1:4),4)))=SUBTOTAL(9,OFFSET(A2,1,COLUMN(A:D),4)))*COLUMN(B:E))-1)}

    计算及格率不超过50%的科目数:{=SUM(N(COUNTIF(OFFSET(A1,1,COLUMN(A:D),10,1),"<60")>=ROWS(2:11)/2))}

    罗列两次未打卡人员:{=IFERROR(OFFSET(A$1,LARGE((COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,,4),"×")>=2)*ROW($2:$11),ROW(A1))-1,),"")}

    计算语文、英语、化学、政治哪科总分最高:=CHOOSE(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,1,MATCH({"语文","英语","化学","政治"},$B$1:$G$1,0),10,))),SUBTOTAL(9,OFFSET(A1,1,MATCH({"语文","英语","化学","政治"},$B$1:$G$1,0),10,)),0),"语文","英语","化学","政治")

    连续三届达到100的次数:=SUMPRODUCT(N(COUNTIF(OFFSET(B1,ROW(2:9)-1,,3,1),">=100")=3))

    罗列及格率最高的学生姓名:{=INDEX(A:A,SMALL(IF(MAX(COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),">=60"))=COUNTIF(OFFSET(A1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),">=60"),ROW($2:$11),12),ROW(A1)))&""}

    计算Excel类图书最多进货量及书名:{=MAX(SUMIF(OFFSET(B1,ROW(2:11)-1,1,1,6),">=100")*(B2:B11="excel"))}

    计算Excel类图书进货最多的是哪一个月:{=INDEX(C1:H1,MATCH(MAX(SUMIF(B2:B11,"excel",OFFSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1))),SUMIF(B2:B11,"excel",OFFSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1)),0))}

    根据下拉列表中的时间和产品名计算销量冠军:{=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2)),OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0))}

    根据下拉列表中的产品提取姓名与销量:{=IFERROR(1/MOD(SMALL(IF(B2:B11=K1,1/SUBTOTAL(9,OFFSET(C2,ROW(2:11)-2,0,1,COLUMNS(C:H)))+ROW(2:11)),ROW(1:10)),1),"")}

    计算产量最高的季度:=TEXT(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,{0,3,6,9},1,3))),SUBTOTAL(9,OFFSET(A1,{0,3,6,9},1,3)),0),"[DBNum1]0季度")

    分栏打印:=IF(ROW()=1,CHOOSE(MOD(COLUMN()-1,3)+1,资料!$A$1,资料!$B$1,""),IF(MOD(COLUMN(),3)=0,"",OFFSET(资料!$A$1,INT(COLUMN()/3)*9+ROW()-1,MOD(COLUMN(),3)-1,)))

    分类汇总:=IF(SUMIF(B$2:B$11,E2,C$2:C$11)=0,"",SUMIF(B$2:B$11,E2,C$2:C$11))

    分类汇总并排序:{=OFFSET(B$1,RIGHT(LARGE(IF(MATCH(B$2:B$11,B$2:B$11,)=ROW($2:$11)-1,SUMIF(B$2:B$11,B$2:B$11,C$2:C$11)*1000+ROW($2:$11),ROWS($1:$11)+1),ROW(1:1)),3)-1,)&""}

    工资查询:{=IFERROR(OFFSET(D1,MATCH(F2&G2&H2,A2:A11&B2:B11&C2:C11,0),),G2&"无此人")}

    多表成绩查询:{=SUBTOTAL(9,OFFSET(INDIRECT(ADDRESS(1,MATCH(H1,1:1,0),1,1,{"一班";"二班";"三班"})),1,,ROWS(2:11),))}

    计算每个学生总分是否高于本班平均成绩:{=SUM(C2:E2)>AVERAGE(IF((A2=A$2:A$11),SUBTOTAL(9,OFFSET(B$1,ROW($2:$11)-1,1,,COLUMNS(C:E)))))}

    计算每个学生进入前三名的科目总数:{=SUM(N((RANK(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1)),OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1)))<=3))}

    计算高于单科平均值的科目总数:{=SUM(N(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1))>SUBTOTAL(1,OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1))))}

    罗列平均成绩倒数三名的班级:{=OFFSET(A1,MATCH(SMALL(SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),ROW(1:3)),SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),),)}

    将姓名重复三次:{=T(OFFSET(A$1,ROUNDUP(ROW(INDIRECT("1:"&ROWS(A$2:A$5)*3))/3,0),))}

    多表汇总金额:{=SUM(SUBTOTAL(6,OFFSET(INDIRECT({"华南区","华东区","华北区"}&"!B1:C1"),ROW(2:10)-1,)))}

    从单价表引用单价并汇总金额:{=SUM((N(OFFSET(G1,MATCH(A2:A7,F2:F13,),)))*B2:B7)}

    从单价表引用最新单价并汇总金额:{=SUM((N(OFFSET(F1,MATCH(A2:A7,D2:D13,)+(COUNTIF(D2:D13,A2:A7)-1),)))*B2:B7)}

    根据完工状况汇总工程款:{=SUM(SUBTOTAL(9,OFFSET(C1,ROW(2:11)-1,,1,2))*(E2:E11=G2))}

    统计最后三天的平均销量:{=SUBTOTAL(1,OFFSET(INDIRECT("B"&MAX((A:A<>"")*ROW(1:1048576))),,,-3,1))}

    重组培训科目表:姓名=LOOKUP(ROW()-1,COUNTIF(OFFSET(B$1:G$1,,,ROW($1:$7)),"<>"),A$2:A$8)&"";科目=IFERROR(OFFSET(B$2,MATCH(H2,$A$2:$A$7,)-1,COUNTIF($H$2:H2,H2)-1),"")

    从多个产品相同单价的单价表中引用单价:=SUMPRODUCT(COUNTIF(OFFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H2

    统计所有业务员销售利润并罗列排列榜:{=OFFSET(A1,MOD(LARGE(INT(SUBTOTAL(6,OFFSET(C2,ROW(C2:C11)-2,,,3)))*1000+ROW(2:11),ROW(2:11)-1),1000)-1,)}

    按季度引用不同价格并统计金额与累计:{=IF(A2<>"累计",LOOKUP(COUNTIF(OFFSET(A$1,1,0,ROWS($2:2),),"合计")+1,ROW($2:$5)-1,F$2:F$5)*B2,SUM(C1:C$2*(A1:A$2<>"累计")))}

    计算10个月中的销售利润并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({"华东区","华南区","华北区","华中区","西南区"}&"!A1"),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)}

    计算五个地区销售利润:{=TRANSPOSE(MMULT({1,1,1,1,1,1,1,1,1,1},SUBTOTAL(6,OFFSET(INDIRECT({"华东区","华南区","华北区","华中区","西南区"}&"!A1"),ROW(西南区!$2:$11)-1,1,1,3)))*1000+ROW(2:11))}

    计算第几轮销量最高以及售货员姓名:{=OFFSET(A1,RIGHT(MAX(SUBTOTAL(9,OFFSET(D1,5*(ROW(INDIRECT("1:"&CEILING(COUNTA(C:C)/5,1)))-1),,5))*10+ROW(INDIRECT("1:"&CEILING(COUNTA(C:C)/5,1)))))*5-1,)}

    提取组名及计算每组平均达标率:{=TEXT(SUBTOTAL(1,OFFSET(B1,((ROW(1:4))*2-1),,,8)),"0.00%")}

    判断是否超过一半人达标率在90%以上:{=COUNTIF(OFFSET(B1,((ROW(1:4))*2-1),,,8),">=0.9")>COLUMNS(B:I)/2}

    分别计算每个班第一名的成绩和姓名:名次{=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<AVERAGE(SUBTOTAL(9,OFFSET(B4,ROW(2:11)-2,,,2))))))}

    计算10个月中的销售利润并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({"华东区","华南区","华北区","华中区","西南区"}&"!A1"),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)}

    将表格转置方向:{=TRANSPOSE(A1:E5)}

    对组数进行排名:{=MMULT(N(B2:B11*(IF(LEFT(C2:C11)="万",10000,1))<TRANSPOSE(B2:B11*(IF(LEFT(C2:C11)="万",10000,1)))),ROW(2:11)^0)+1}

    区分大小写提取产品单价:{=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={"A组","B组","C组"}))}

    引用销售金额高于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,"D";60,"C";80,"B";90,"A"},2)

    查找美元与人民币报价:=VLOOKUP(B2,INDIRECT(E2&"报价!A2:B9"),2,0)

    多条件查找:{=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"&MATCH(B2,单价表!A:A,0)&":Z"&MATCH(B2,单价表!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&"",IF({1,0},IF(COUNTIF(资料表!A2:A10,A2)=0,资料表!B2:B10,资料表!A2:A10),资料表!E2:E10),2,0)}

    按学历对姓名排序:{=VLOOKUP(MOD(SMALL(MATCH(B$2:B$10,{"大学";"高中";"初中";"小学"},0)*1000+ROW($2:$10),ROW(A1)),1000),IF({1,0},ROW($2:$10),A$2:A$10),2,0)}

    使用通配符进行查找:{=VLOOKUP("*"&A2&"*",IF({1,0},资料表!B$2:B$9,资料表!A$2:A$9),2,0)}

    多工作表查找最大值:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),"[DBNum1]")&"年级!B"&MATCH(D2,A:A,0)))),IF({1,0},SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),"[DBNum1]")&"年级!B"&MATCH(D2,A:A,0))),ROW(1:6)),2,0),"[DBNum1]")}

    对带有合并单元格的区域查找年假天数:=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="塑胶机",{0,3,8;25,19,18},{0,5,10;12.5,10,11}),2)

    多条件计算加班费:=TEXT(HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2),"[>2]6;5")*HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.0001,50.0001;0,0.5,1},2)

    根据进厂日期计算有薪假天数:=HLOOKUP(DATEDIF(B2,TODAY(),"y"),{0,1,3,5,7,10;0,2,3,5,7,10},2)

    制作准考证:=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("A"&(MATCH(1,0/(A:A<>""))))}

    提取字符串中的汉字:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)>="啊"),),SUM(MATCH({1,2},1/(MID(A2,ROW($1:$99),1)>="啊"),{0,1})*{-1,1})+1)}

    将文件号中的中文大写转小写:{="第"&TEXT(SUM((MATCH(MID(A2,{2,3,4},1),TEXT(ROW($1:$10)-1,"[DBNum2]"),0)-1)*{100,10,1}),"000")&"号文件"}

    计算补课科目总数:{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))}

    产生混合编号:=TEXT(COUNTIF(C$1:C1,"*"),"[DBNum2]")&TEXT(ROW()-MATCH("々",C$1:C1),"(000);;")

    提取迟到次数最多者姓名:=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,{"冰箱","空调","洗衣机"},0),MATCH(I2,C1:E1,0))

    将一维人事资料表转二维:{=REPLACE(IFERROR(OFFSET($A$1,MATCH(C$1:F$1&":*",IF(COUNTIF(OFFSET(A$1,,,ROW($1:$42)),"")=ROW()-2,A$1:A$42),0)-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)

    将中文大写编号转换成阿位伯数字小写:=TEXT(LOOKUP(1,0/(B2=TEXT(ROW($1:$1000),"[DBNum2]000")),ROW($1:$1000)),"000")

    将姓名按拼音升序排列:{=LOOKUP(0,0/(ROW(A1)=MMULT(N($A$2:$A$11>=TRANSPOSE($A$2:$A$11)),ROW($2:$11)^0)),A$2:A$11)}

    将酒店按星级降序排列:{=LOOKUP(ROUND(1/MOD(LARGE(LEN(B$2:B$10)+1/ROW($2:$10),ROW(A1)),1),0),ROW($2:$10),A$2:A$10)}

    计算某班六年中谁获第一名次数最多:{=MAX(COUNTIF(B2:B7,B2:B7))}

    罗列每个名次的所有姓名:{=IFERROR(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(A2)))),"")}

    提取新书的印刷批次:=LOOKUP(9E+307,--RIGHT(LEFT(A2,FIND("[",A2)-1),ROW($1:$99)))

    罗列2008年每月第一个及最后一个星期日:{=MIN(IF(WEEKDAY(DATE(2008,ROW(A1),ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0))))),2)=7,DATE(2008,ROW(A1),ROW(INDIRECT("1:"&DAY(DATE(2008,ROW(A1)+1,0)))))))}

    填补空白区:=LOOKUP(1,0/($A$2:A2<>""),A$2:A2)

    将字母转换成评分:{=AVERAGE(LOOKUP(B2:I2,{"A","B","C","D","E"},{10,9.5,8,7,5}))}

    将字母转换成评分并对选手排名:{=LOOKUP(MOD(LARGE(MMULT(LOOKUP($B$2:$I$7,{"A","B","C","D","E"},{10,9.5,8,7,5}),ROW($1:$8)^0)*10000+ROW($2:$7),ROW(A1)),10),ROW($2:$7),A$2:A$7)}

    标识各选手应得的奖牌:{=LOOKUP(SUM(N(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11,0)>B2))+1,ROW($1:$4),{"冠军","亚军","季军",""})}

    计算各厂商参赛人数:{=IFERROR(LOOKUP(SMALL(IF(A$2:A$21<>"",ROW($2:$21)),ROW(A1)),ROW($2:$21),A$2:A$21)&":  "&MMULT(SMALL(IF(A$2:A$21<>"",ROW($1:$20),21),ROW(A1)+{0,1}),{-1;1}),"")}

    从品名信息中分别提取多段数值:=IFERROR(-LOOKUP(0,-MID($A2,FIND(B$1,$A2)+LEN(B$1),ROW($1:$100))),"--")

    反向查找数据:=LEN(A2)-LOOKUP(100,SEARCH(B2,A2,ROW($1:$99)))-LEN(B2)+2

    一级、二级分组编号:=TEXT(COUNTIF(B$1:B2,"第*"),"00")&TEXT(ROW()-LOOKUP(1,0/(LEFT(B$1:B2)="第"),ROW($1:2)),"[=0] ;000")

    计算购货金额:{=LOOKUP(9E+307,--MID($A2,MATCH(0,0*MID($A2,ROW($1:$1000),1),0),ROW($1:$15)))*(LOOKUP(9E+307,--LEFT(REPLACE(A2,1,FIND("*",A2),""),ROW($1:$1000))))}

    谁是百米冠军:=LOOKUP(0,0/(B2:B11=MIN(B2:B11)),A2:A11)

    从销售记录中提取销量与单价并计算金额:{=LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("公斤",A2),100,""),ROW($1:$100)))*LOOKUP(10^16,--RIGHT(REPLACE(A2,FIND("元",A2),100,""),ROW($1:$100)))}

    根据比赛结果降序排列选手且标识名次:{=LOOKUP(SUM(N(COUNTIF(B$2:B$21,E2)<--IF(FREQUENCY(COUNTIF($B$2:$B$21,B$2:B$21),COUNTIF($B$2:$B$21,B$2:B$21)),COUNTIF($B$2:$B$21,B$2:B$21))))+1,ROW($1:$4),{"冠军","亚军","季军",""})}

    计算每个职工的得分:=LOOKUP(,-FIND(B2,{"A**","A*","A","B**","B*","B","C**","C*","C","D"}),11-ROW($1:$10))

    查询业务员的负责地区:{=T(INDEX(B:B,SMALL(IF(LOOKUP(ROW(A$2:A$11),IF(A$2:A$11<>"",ROW(A$2:A$11)),A$2:A$11)=$D$2,ROW(A$2:A$11),1048576),ROW(1:1))))}

    根据产量计算员工产量得分:{=LOOKUP(B2,{3,0.5}*(ROW($1:$11)-1))}

    根据员工得分转换为相应的等级:=LOOKUP(B2,--REPLACE(等级与分值!B$2:B$6,FIND("-",等级与分值!B$2:B$6),10,""),等级与分值!A$2:A$6)

    提取产量冠军的组别:=IF(COUNTA(B2:E2),LOOKUP(1,0/ISTEXT(B2:E2),B$1:E$1),"")

    区分工种和达标率计算奖金:=LOOKUP(C2*100,1*LEFT(达标与奖金标准!B$1:K$1,FIND("%",达标与奖金标准!B$1:K$1)-1),OFFSET(达标与奖金标准!B$1,MATCH(B2,达标与奖金标准!A$2:A$4,0),,,10))

    使用通配符查找所有符合条件的数据:{=IFERROR(LOOKUP(1,0/SEARCH("*医院*",IF(COUNTIF($C$1:C3,A$2:A$12)=0,A$2:A$12,)),A$2:A$12),"")}

    分别提取身份证号码中的年月日:=TEXT(TEXT(MID($A2,7,8),"0000-00-00"),"[DBNum1]"&CHOOSE(MATCH(B$1,{"年","月","日"},0),"YYYY年","M月","D日"))

    根据不良率判断送货品处理办法:=CHOOSE((SUM(N(C2/B2>={0,0.005,0.01}))),"合格","允收","退货")

    让VLOOKUP函数在多区域查找:=VLOOKUP(A11,CHOOSE(MATCH(B11,{"一年级","二年级","三年级"},0),A1:B9,D1:E9,G2:H9),2,0)

    将区域互换位置:=VLOOKUP(E2&"",CHOOSE({2,1},A2:A9,C2:C9),2,0)

    跨表统计最大值:{=CHOOSE(MOD(MAX(SUBTOTAL(9,INDIRECT({"A组";"B组";"C组"}&"!B2:B10"))*100+{1;2;3}),100),"A组","B组","C组")}

    罗列所有参加田径的人员:{=IFERROR(VLOOKUP(1,CHOOSE({1,2},--(COUNTIF(OFFSET(C$2,,,ROW($2:$11)-1),"田径")=ROW(1:1)),A$2:A$11),2,),"")}

    计算今天是本月的上旬、中旬还是下旬:=CHOOSE(MIN(CEILING(DAY(TODAY())/10,1),3), "上旬","中旬","下旬")

    建立文件目录:=HYPERLINK("[E:/产量表/"&TEXT(ROW(1:1),"[DBNum1]")&"月产量表.xlsx]sheet1!A1",TEXT(ROW(1:1),"[DBNum1]")&"月产量表")

    链接“总表”中B列最大值单元格:{=HYPERLINK("#总表!B"&MAX((MAX(总表!B:B)=总表!B:B)*ROW(B:B)),"至总表B列最大值")}

    链接至B列最末的非空单元格:{=HYPERLINK("#B"&MAX(((B:B)<>"")*ROW(B:B)),"B列最后非空值")}

    选择冠军姓名:{=HYPERLINK("#"&TEXT(SUM(SMALL(IF(B2:B13=MAX(B2:B13),ROW(2:13)),ROW(INDIRECT("1:"&COUNTIF(B2:B13,MAX(B2:B13)))))*10^((ROW(INDIRECT("1:"&COUNTIF(B2:B13,MAX(B2:B13))))-1)*2)),REPT("A00!,",COUNTIF(B2:B13,MAX(B2:B13))-1)&"A00"),"得票冠军")}

    选择二年级旷课人员名单:{=HYPERLINK("#A"&MIN(IF(A2:A12="二年级",ROW(2:12)))&":B"&MAX(IF(A2:A12="二年级",ROW(2:12))),"二年级名单")}

    选择产量最高工作表:{=HYPERLINK("#"&CHAR(64+MOD(MAX(SUBTOTAL(9,INDIRECT(CHAR(64+ROW(1:8))&"组!B2:B11"))*100+ROW(1:8)),100))&"组!A1","跳至最大产量组")}

    选择打印区域:=HYPERLINK("#Print_Area",IF(ISERR(INDEX(Print_Area,1,1)),"未设置打印区","跳至打印区域"))

    计算期末平均成绩:{=AVERAGE(IF(ISEVEN(COLUMN(B:I)-1),B3:I3))}

    提取期末成绩明细:{=INDEX(成绩表!1:1,SMALL(IF(ISEVEN(COLUMN($B:$I)-(ROW()<>1)),COLUMN($B:$I)),COLUMN(A1)))}

    提取每日累计出库数和每日库存数:日期=INDEX(A:A,ROW(A1)*2);累计出库数{=SUM(ISODD(ROW(INDIRECT("2:"&(ROW(A1)*2)+1)))*OFFSET(C$1,1,,ROWS($1:1)*2))};每日库存数{=SUM(SUMIF(OFFSET(B$1,1,,ROW(A1)*2),{"进库","出库"},C$2)*{1,-1})}

    根据身份证号码汇总男、女职工总数:男{=SUM(--ISODD(MID(B2:B10,15,3)))};女{=SUM(--ISEVEN(MID(B2:B10,15,3)))}

    提取当前表打印区域地址:=IF(ISNA(VLOOKUP("苹果",A2:B5,2,0)),10,LOOKUP(10^16,--LEFT(VLOOKUP("苹果",A2:B5,2,0),ROW(1:100))))

    计算生产部人数和非生产部人数:生产部人数{=SUM((NOT(ISERR(FIND("车间",A2:A11)))*B2:C11))};非生产部人数{=SUM((ISERR(FIND("车间",A2:A11)))*B2:C11)}

    提取A、B列相同项与不同项:{=T(INDEX(A:A,SMALL(IF(NOT(ISERROR(MATCH(A$2:A$11,B$2:B$11,0))),ROW($2:$11),1048576),ROW(A1))))}

    计算产品体积:=IF(ISERROR(FIND("/",B2)),B2^3,PRODUCT(1*TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",100)),{1,100,200},100))))

    引用单价并去除干扰符:=IF(ISNA(MATCH(B2,单价表!B$1:E$1,0)),"请更新单价",C2*LOOKUP(10^16,--LEFT(HLOOKUP(B2,单价表!B$1:E$2,2,0),ROW($1:$100))))

    查询书籍在七年中的最高单价:{=IF(ISNA(MATCH(A10,A2:A8,0)),"书名错误",MAX(VLOOKUP(A10,A1:H8,COLUMN(B:H),0)))}

    根据计价单位查询单价:=IF(ISNA(MATCH(B2,F$1:H$1,0)),"未设定汇率",C2*HLOOKUP(B2,F$1:H$2,2,0))

    数字、字母与汉字个数计算:数字个数{=SUM(--(ERROR.TYPE(INDIRECT("XFD"&MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)&1))=3))}

    判断错误类型:=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)="男"

    禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH("长?*宽?*高?*",B2))

    自动记录进库时间:=IF(ISBLANK(B2),"",IF(C2="",NOW(),C2))

    记录历史最高值:=MAX(B:B,D2)

    解一元二次方程:X+100=X^2+10

    解二元一次方程:X=10X=(100-5Y)/25,Y=Y/5=(200 +4X)/4

    展开全文
  • Excel培训 如何用Excel函数COUNTIFS和SUMIFS来多条件筛选统计数据 求学若渴 一、CountIFs函数,作用:多条件计数 语法规则:countif(range,criteria,range,criteria,...) ...具体公式写法:=CO...

    推荐下载Excel视频教程

    点击这里直接下载

    Excel培训 如何用Excel函数COUNTIFS和SUMIFS来多条件筛选统计数据 求学若渴

    一、CountIFs函数,作用:多条件计数

    语法规则:countif(range,criteria,range,criteria,...)

    示例1、如何来统计“苹果iPhone7”手机的销量,其中(A列是产品名称其中包含“苹果”,B列是产品型号其中包含“iPhone7”)

    具体公式写法:=COUNTIFS(A2:A9,"苹果",B2:B9,"iPhone7")

    备注:我的头条号和我的微信公众号名字是一样的,大家可以关注后查看完整课件

    示例2、如何来统计售价在3000~4000之间的手机有多少个?其中(C列是产品价格)

    具体公式写法:=COUNTIFS(C2:C9,">3000",C2:C9,"<4000")

    二、SumIFs函数,作用:多条件求和

    语法规则:SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

    示例1、如何来统计“苹果iPhone7”手机的销售额,其中(A列是产品名称其中包含“苹果”,B列是产品型号其中包含“iPhone7”,C列是销售额)

    具体公式写法:=SUMIFS(C2:C9,A2:A9,"苹果",B2:B9,"iphone7")

    示例2、如何来计算“苹果iphone”系列的销售额,其中(A列是产品名称其中包含“苹果”,B列是产品型号其中包含“iPhone”系列的所以型号,C列是销售额)

    具体公式写法:=Sumifs(C2:C9,A2:A9,"苹果",b2:b9,"*iphone*")

    展开全文
  • Excel函数公式计算阶梯式电价

    千次阅读 2017-08-16 09:51:35
    在所有的用电量中,执行第一档价格的是601,即A2-0千瓦时。 执行第二档与第一档差价的是总电量减第一档用电量止码,即A2-260千瓦时,结果为341千瓦时。 执行第三档与第二档差价的是总电量减第二档用电量止码,即A2...
  • Excel进阶课程一函数公式

    千次阅读 2019-09-08 17:33:39
    公式函数之常见错误 数据处理 1.合并计算 2.排序 3.筛选 4.分类汇总 5.分级显示 数据处理之制作图表 1.迷你图 2.创建图表 3.认识图表中的元素 4.编辑,修饰图表 SUM函数 1.参数设置:sum(n...
  • 使用 Black Scholes 公式计算欧洲价格期权的 R 函数。 输入作为(当前股票价格、现货价格、时间(以年为单位)、利率、方差/波动率)提供,对于欧式看涨期权和欧式看跌期权,函数的输出分别为 2 个值。 实验数据 ...
  • 截取字符的公式 有一组数据101~103*,11~20*…我想截取~至*之間的數字。 =MID($A3,FIND("~",$A3)+1,FIND("*",$A3)-(FIND("~",$A3)+1)) 如何确定*号后的数字 =RIGHT(A1,LEN(A1)-....
  • 通达信公式函数

    千次阅读 2013-03-24 17:28:40
    整理如下: ---------------------------------------------------------...算术函数 -------------------------------------------------------------------------------- NOT 取反 求逻辑非。 用法: NOT(X) 返...
  • 今天江西仁和会计小编把工作中最常用对新手又有些难度的16个公式整理出来,分享给同学们。  个个都很实用,一定要注意收藏哦。  1、完成率公式(可能有负值)  【例15】如下图所示,要求根据B的实际和C...
  •  4、在【明细表】中新增辅助列“是否为首次保养”,=IF(A2<>A1,"yes",""),由于数据量比较大,将公式---计算选择---手动,输入公式后填充完公式,按F9手动计算  5、给“是否为首次保养”添加筛选(开始--排序和...
  • 在Excel中,乘法运算可以是乘积函数或星号*,但它们是不同的。乘积函数不仅使用单个值作为参数,而且还使用数组和对单元格的引用。例如,要计算A1到A6中所有值的乘积,您不需要一个个地编写...双击单元格E2,将公式...
  • 简单的进出库表带总表,excele格式,代价格只要输入品名的代码就能得到你要出入库的名称,输入数量就可以自动生成金额。没有宏代码,用的函数公式
  • 本书从函数功能、函数格式、参数说明、注意事项、Excel 版本提醒、案例应用、交叉参考7 个方面,全面、细致地介绍了Excel 2016/2013/2010/2007/2003 中公式函数的使用方法、实际应用和操作技巧。最后3 章还将公式...
  • 考虑我们最初在“线性回归”中提出的问题,特征是房子的面积x,这里的x是实数,结果y是房子的价格。 假设我们从样本点的分布中看到x和y符合3次曲线,那么我们希望使用x的三次多项式来逼近这些样本点。 那么首先...
  • 选股公式函数汇总解释

    千次阅读 2008-04-05 13:16:17
    公式可以这样算: (-65.2)²+ (-26.2)²+ (-17.2)²+ (54.8)²+ (53.8)²/5=2226.16 6、样本方差VAR(X,N):是总体方差的N/(N-1)倍。 2226.16*5/(5-1)=2782.70 估算样本方差,总比总体样本方差大一点,当N够大时,...
  • 欢迎大家到我的小站下载本书的前言、目录和样章:点击进入下载地址 由人民邮电出版社出版的《Excel公式函数大辞典》这本书本周已向全国开始发货,预计4月初就会在全国各地的书店上架销售。本书全面、细致地介绍了...
  • 目标函数:既可代指损失函数,又可代指代价函数/成本函数。有的地方将损失函数和代价函数没有细分也就是两者等同的。 损失函数(Loss function): 损失函数越小,就代表模型拟合的越好。 例如有平方误差损失函数、...
  • 本题要求自定义函数,计算水果的价格。水果单价的设置规则为: 水果类型包括苹果、香蕉、梨,分别用整数1、2、3表示水果类型。 水果单价分为普通价格和会员价格。其中:苹果单价6.99元,会员价5.99元;香蕉单价4.99...
  • 机器学习的过程就是将训练集中里的房屋面积喂给学习算法,学习算法就开始工作了,然后输出一个函数,通常被称为h(hypothesis假设函数),有了这个h函数,输入测试数据中想要出售的房屋价格,该函数会给出相应售卖价格...
  • 手机评站网今天精心准备的是《index函数》,下面是详解!函数INDEX的功能是什么?函数INDEX的功能是帮助返回表或区域中的值或值的引用。INDEX可以返回一个值或者一组值,可以返回对某个单元格的引用或者单元格区域的...
  • 公司销售的产品一般都会有一份详细的价格表,方便销售人员对外报价。但客户咨询或购买的并非全部产品,如果把...今天阿钟老师就教大家利用VLOOKUP函数制作一份自动报价单,省得你再出报价单时查询价格表,再录入的麻...
  • 函数根据 Heston 和 Nandi(2000) 的 GARCH 期权定价公式计算看涨期权的价格。 该函数的输入是:标的资产的当前价格、执行价格、标的资产的无条件方差、到期时间(以天为单位)和每日无风险利率。
  • VLOOKUP函数4个参数,参数1:选择值,比如产品1。参数2:数据查找范围列,比如A:C。参数3:填充值在数据范围列中的第几列,如2(第1列是查找列,品名列,第二列是价格,就写2)。参数4:0代表等于,1代表模糊查询。 ...
  • 函数

    2015-01-08 10:40:45
    考虑我们最初在“线性回归”中提出的问题,特征是房子的面积x,这里的x是实数,结果y是房子的价格。假设我们从样本点的分布中看到x和y符合3次曲线,那么我们希望使用x的三次多项式来逼近这些样本点。那么首先需要将...
  • 一篇文章看透母函数、矩、矩母函数联系与区别

    千次阅读 多人点赞 2019-10-31 11:41:02
    矩母函数的暴力拆解一篇文章看透母函数、距、矩母函数联系与区别...全文基于自己的理解,仅供社区朋友共同学习,文章不会罗列公式,对小白绝对友善。温馨提示:数学大佬可以直接跳过不看哦~ 导读 矩母函数,英文...
  • TB函数

    千次阅读 2016-01-12 19:37:05
    数学函数Abs: 返回参数的绝对值。 Acos: 返回参数的反余弦值。 Acosh: 返回参数的反双曲余弦值。 Asin: 返回参数的反正弦值。 Asinh: 返回参数的反双曲正弦值。 Atan: 返回参数的反正切值。 Atan2: 返回给定的...
  • 函数的嵌套调用与函数的高级应用

    千次阅读 2019-03-31 19:28:29
    函数的嵌套调用指的是在调用一个函数的过程中,调用另外一个函数,这种在函数内调用其他函数的方式称为嵌套调用。 function example($price,$tax){ function examples($yuan,$taxs=7.5){ return $yuan*$taxs; ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 17,015
精华内容 6,806
关键字:

价格函数公式