EXCEL.docx

EXCEL.docx

《EXCEL.docx》由会员分享，可在线阅读，更多相关《EXCEL.docx（56页珍藏版）》请在冰豆网上搜索。

EXCEL

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（）-