EXCEL函数技巧大全Word格式文档下载.docx
《EXCEL函数技巧大全Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《EXCEL函数技巧大全Word格式文档下载.docx（56页珍藏版）》请在冰点文库上搜索。
计算仓库大额进库数量之和:
B8,"
>
1000"
）
对1400到1600之间的工资求和:
{=SUM（SUMIF（B2:
<
="
&
{1400,1600}）*{-1,1}）}
求前三名和后三名的数据之和:
"
LARGE（B2:
B10,4））+SUMIF（B2:
SMALL（B2:
B10,4））
对所有车间人员的工资求和:
=SUMIF（A2:
A10,"
?
车间"
C2）
对多个车间人员的工资求和:
车间*"
汇总姓赵、刘、李的业务员提成金额:
=SUM（SUMIF（A2:
A10,{"
"
刘"
李"
}&
*"
C10））
汇总鼠标所在列中大于600的数据:
=SUMIF（INDIRECT（"
R2C"
CELL（"
col"
）&
:
R8C"
）,FALSE）,"
600"
只汇总60～80分的成绩:
=SUMIFS（B2:
B10,B2:
=60"
B2:
=80"
汇总三年级二班人员迟到次数:
=SUMIFS（D2:
D10,B2:
三年级"
C10,"
二班"
汇总车间女性人数:
=SUMIFS（C2:
C11,A2:
A11,"
*车间"
B11,"
计算车间男性与女性人员的差:
=SUM（SUMIFS（C2:
C11,B2:
B11,{"
},A2:
）*{-1,1}）
计算参保人数:
=SUMPRODUCT（（C2:
C11="
是"
）*1）
求25岁以上男性人数:
=SUMPRODUCT（（B2:
）*1,（C2:
C10>
25）*1）
汇总一班人员获奖次数:
B11="
一班"
）*C2:
C11）
汇总一车间男性参保人数:
=SUMPRODUCT（（A2:
A10&
B10&
C2:
一车间男是"
汇总所有车间人员工资:
=SUMPRODUCT（--NOT（ISERROR（FIND（"
A2:
A10）））,C2:
汇总业务员业绩:
B11={"
江西"
广东"
}）*（C2:
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（产量表:
单价表!
求不同单价下的利润:
{=MMULT（B2:
B10,G2:
H2）*25%}
制作中文九九乘法表:
=COLUMN（）&
ROW（）&
MMULT（ROW（）,COLUMN（））
计算车间盈亏:
=SUM（MMULT（（B3:
E5>
0）*B3:
E5,{1;
1;
1}）,MMULT（（B3:
E5<
1}））
计算各组别第三名产量是多少:
{=MAX（MMULT（COLUMN（A:
E）^0,B2:
G6））}
计算C产品最大入库量:
{=MAX（MMULT（N（A2:
A11="
C"
）,TRANSPOSE（（B2:
B11）*（A2:
））））}
求入库最多的产品数量:
{=MAX（MMULT（TRANSPOSE（（B2:
A11={"
A"
B"
D"
}））,（A2:
}）*1））}
计算累计入库数:
{=MMULT（N（ROW（2:
11）>
=TRANSPOSE（ROW（2:
11）））,B2:
计算每日库存数:
B11-C2:
C11）}
计算A产品每日库存数:
17）>
17）））,（B2:
B17="
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:
10）^0）））}
在不同班级有同名前提下计算学生人数:
{=SUM（1/MMULT（N（A2:
A17&
B17&
C17=TRANSPOSE（A2:
C17））,ROW（2:
17）^0））}
计算前进中学参赛人数:
{=SUM（IFERROR（1/MMULT（N（（A2:
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:
60）*（B2:
80）*B2:
B11）,ROW（2:
11）^0）}，求个数{=MMULT（TRANSPOSE（（B2:
80））,ROW（2:
11）^0）}
汇总A组男职工的工资:
{=MMULT（TRANSPOSE（N（B2:
B11&
男A组"
D11）,ROW（2:
计算象棋比赛对局次数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:
计算余数:
=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:
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））}
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:
合计购物金额、保留一位小数:
=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））
根据重量单价计算金额，结果以万为单位:
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:
=700,1,-1）））/80*50}
将统计金额保留到分位:
=ROUND（SUMPRODUCT（B2:
C10）,2）
将统计金额转换成以万元为单位:
C10）%%,）
对单价计量单位不同的品名汇总金额:
{=SUM（ROUND（B2:
C10*IF（D2:
D10="
G"
1000,1）,（D2:
）*2））}
将金额保留“角”位，忽略“分”位:
{=SUM（ROUNDDOWN（B2:
C10,1））}
计算需要多少零钞:
C10,{0,-1}）*{1,-1}）}
计算值为l万的整数倍数的数据个数:
{=SUM（N（（B2:
C10）=ROUNDDOWN（B2:
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:
仅对筛选出的人员排名次:
{=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:
计算同行相等且长度为3的个数:
{=SUM（（A1:
B10）*（LEN（A1:
A10）=3））}
提取A产品最后单价:
{=INDEX（C:
C,MAX（（B2:
）*ROW（2:
10）））}
判断学生是否符合奖学金发放条件:
=AND（B2>
90,C2<
汉族"
所有裁判都给“通过”就进入决赛:
{=AND（B2:
E2="
通过"
判断身份证长度是否正确:
=OR（LEN（B2）={15,18}）
判断歌手是否被淘汰:
{=OR（B2:
不通过"
根据年龄判断职工是否退休:
=OR（AND（B2="
C2>
60）,AND（B2="
55））
根据年龄与职务判断职工是否退休:
D2>
60+（C2="
干部"
）*3）,AND（B2="
55+（C2="
）*3））
没有任何裁判给“不通过”就进行决赛:
{=NOT（OR（B2:
））}
计彝成绩区域数字个数:
{=SUM（NOT（ISERROR（NOT（B2:
B11）））*1）}
评定学生成绩是否及格:
=IF（AVERAGE（B2:
D2）>
=60,"
及格"
不及格"
根据学生成绩自动产生评语:
D2）<
60,"
IF（AVERAGE（B2:
90,"
良好"
100,"
优秀"
满分"
）））
根据业绩计算需要发放多少奖金:
{=SUM（IF（B2:
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<
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:
A组"
C组"
},C2:
C11））}
混合单价求金额合计:
K"
1000,1）,2））}
计算异常停机时间:
{=SUM（SUBSTITUTE（SUBSTITUTE（IF（C2:
C11<
C11,0）,"
修机"
）,"
换原料"
计算最大数字行与文本行:
{=MAX（IF（B:
B<
ROW（A:
A）））}
找出谁夺冠次数最多:
{=INDEX（B:
B,MIN（IF（MAX（COUNTIF（B2:
B12,B2:
B12））=COUNTIF（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（"
LEN（A2）））,1））<
58））}
计算单元格中大写加小写字母个数:
{=SUM（（CODE（UPPER（MID（A2,ROW（INDIRECT（"
LEN（A2）））,1）））>
64）*（CODE（UPPER（MID（A2,ROW（INDIRECT（"
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（
升级会员 