Excel函数公式实例汇总.docx
《Excel函数公式实例汇总.docx》由会员分享，可在线阅读，更多相关《Excel函数公式实例汇总.docx（56页珍藏版）》请在冰豆网上搜索。
Excel函数公式实例汇总
Excel2007函数公式实例汇总
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,
升级会员 