一句话学函数公式.docx
《一句话学函数公式.docx》由会员分享,可在线阅读,更多相关《一句话学函数公式.docx(34页珍藏版)》请在冰豆网上搜索。
一句话学函数公式
一句话学函数公式
资料来源于互联网:
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之间带小