(大专)EXCEL函数重难点整理2018

（二级MS Office真题汇编与专用题库）EXCEL试题函数难点整理(图书销售订单明细及统计报告)-----涉及函数及要点:VLOOKUP,SUM,SUMPRODUCT
统计项目销售额
658638
15210
40727
9845.25 答案内的函数公式明细
所有订单的总销售金额=SUM(订单明细!H3:H636)
《MS Office高级应用》图书在2012年的总销售额
隆华书店在2011年第3季度（7月1日~9月30日）的总销售额隆华书店在2011年的每月平均销售额（保留2位小数）=SUMPRODUCT(1*(订单明细!E377:E636="《MS Office高级应用》"),订单明细!H377:H636) =SUMPRODUCT(1*(订单明细!C178:C289="隆华书店"),订单明细!H178:H289)
=SUMPRODUCT(1*(订单明细!C3:C376="隆华书店"),订单明细!H3:H376)/12
(学生成绩单)-----涉及函数及要点:MID,SUM,AVERAGE,柱状图,分类汇总
学号姓名班级答案内的函数公式明细
120305 包宏伟3班=MID(A3,4,1)&"班"
120203 陈万地2班=MID(A3,4,1)&"班"
(销售情况及产品销售汇总表)-----涉及函数及要点:RANK,数据透视表
一二季度销售总量一二季度销售总额销售额排名答案内的函数公式明细387 640098 3班=RANK(D2,$D$2:$D$21,0)
=RANK(D2,$D$2:$D$21,0) 171 134406 2班
1 / 13
(差旅报销及分析)-----涉及函数及要点: LEFT,VLOOKUP,IF,WEEKDAY ，SUMPRODUCT 题目
答案内的函数公式明细 答案解析
提取活动地点内的地区 提取费用类别 判断是否加班
=LEFT(C3,3)
从左侧提取C3内的前3个字符
=VLOOKUP(E3,费用类别!$A$3:$B$12,2,FALSE) =IF(WEEKDAY(A3,2)>5,"是","否")
查找E3,在“费用类别!$A$3:$B$12”范围内,返回第2列的值,精确匹配 WEEKDAY 判断星期几的函数,数字2表示1至7代表星期一到星期天 即:如果A3内提取的星期数>5,则为加班,返回值“是”,否则返回“否”
2013年第二季度发生在北京市的差 旅费用金额总计为：
2013年钱顺卓报销的火车票总计金 额为：
=SUMPRODUCT(1*(费用报销管理!D74:D340="北京市"),费用先将日期排序，确认时间范围，如果费用报销管理!D74:D340="北京市"，则 报销管理!G74:G340) 求和费用报销管理!G74:G340内的值
=SUMPRODUCT(1*(费用报销管理!B3:B401="钱顺卓"),1*(费两个条件，如果满足条件1名字=钱顺卓、条件2类别=火车票，则求费用报 用报销管理!F3:F401="火车票"),费用报销管理!G3:G401)
销管理!G3:G401内的和 2013年差旅费用金额中，飞机票占所
=S UMPRODUCT(1*(费用报销管理!F3:F401="飞机票"),费用 有报销费用的比例为（保留2位小数）报销管理!G3:G401)/SUM(费用报销管理!G3:G401) 求满足条件=飞机票的费用报销管理!G3:G401内的值，除以SUM(费用报销管
理!G3:G401)
2013年发生在周末（星期六和星期 =SUMPRODUCT((费用报销管理!H3:H401="是")*(费用报销管两个条件，如果加班栏内为“是”,且费用类别为“通讯补助”,则求和费用 日）中的通讯补助总金额为：
理!F3:F401="通讯补助"),费用报销管理!G3:G401)
报销管理!G3:G401
(员工工资表)-----涉及函数及要点: ROUND,IF,分类汇总,SUMPRODUCT 题目
答案内的函数公式明细
答案解析
应交个人所得税
=ROUND(IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3 ROUND,四舍五入函数,最后面的参数2为保留最后2位小数点,中间 *20/100-555,IF(K3<=35000,K3*25%-1005,IF(K3<=5500,K3*30%-2755,IF(K3<= IF 为判断值,如果怎样便怎样,最后在末尾一次性补齐右括号 80000,K3*35%-5505,IF(K3>80000,K3*45%-13505))))))),2)
管理人员应付工资合计 =SUMPRODUCT(1*(D3:D17="管理"),I3:I17)
在J20:L26内手工录入标题及行列名称,在数据内容内利用SUMPRODUCT 求出各项值.
2 / 13
(第一学期期末成绩)-----涉及函数及要点:IF,MID,SUM,AVERAGE,分类汇总,簇状条形图 题目
答案内的函数公式明细
答案解析
根据学号提取班级名称
=IF(MID(A4,4,2)="01","1班",IF(MID(A4,4,2)="02","2班","3班"))
如果提取的=01则为1班,如为02则为2班,否则为3班
题目 答案内的函数公式明细 答案解析
=SUMIFS(销售订单 !$H$3:$H$678,销售订单 !$E$3:$E$678,A4,销售 S U MIFS
使用方法
订单!$C$3:$C$678,1) SUMFIFS(求和范围,条件 1,条件 2) 某图书在某月份的销量合 计 此处条件 1为销售订单!$E$3:$E$678值为
A 4时(A4为书名)
此处条件 2为销售订单!$C$3:$C$678值为 1时(1
为 1月份)
3 / 13
(学生成绩)-----涉及函数及要点: IF,MOD,MID,--TEXT,DATEDIF,TODAY 题目
答案内的函数公式明细
答案解析
=IF(MOD(MID(C3,17,1),2)=1,"男","女")
MOD 取余函数,第 17位身份证号码奇数为男,偶数为女 C3内第 17位开始取 1位,除以 2,余为 1时则是奇数,判断为”男”,否 则为”女”
根据身份证号码自动判断性别
=--TEXT(MID(C3,7,8),"0-00-00")
根据身份证号码提取出生年月 日为XXXX-XX-XX 格式 根据身份证号码计算年龄 计算班级名次,格式为“第XX 名” =DATEDIF(--TEXT(MID(C3,7,8),"0-00-00"),TODAY(),"y") ="第"&RANK(F11,$F$2:$F$45)&"名"
=IF(F11>=102,"优秀",IF(F11>=84,"良好",IF(F11>=72,"及格 ",IF(F11>72,"及格","不及格"))))
判断成绩
二级 MS office 考试常用函数整理
1.单条件求和——SUMIF 函数
如要统计 C 列中的数据,要求统计条件是
B 列中数据为"条件一"。

并将结果放在
C6单元格中，我们只要在
C6单元格中输入公式“=SUMIF （B2：B5，"条件一"，C2：C5）”即完成这一统计。

2.符合多个条件的数据求和——SUMIFS 函数
统计 5班中语文名次、数学名次均位于前
20名同学的总分之和。

在相应单元格内
输入公式
4 / 13
“=SUMIFS(M2:M80,A2:A80,"5",D2:D80,"<=20",F2:F80,"<=20")”，按下回车键，就一切O K。

如图所示。

3.去尾取整函数——INT函数
（注意要区分与T R UNC函数（只取整），而I N T取向下整
数。

）
4.取整函数，只取整理。

-TRUNC函数
5 / 13
例：=TRUNC（8.9），结果为8；=TRUNC（-9.6），结果为-9；
5.四舍五入函数——ROUND函数
6.向下舍数字函数——ROUNDDOWN函数
⑴功能按指定的位数对数值进行舍入。

⑵格式R OUNDDOWN(数值或数值单元格，指定的位数)
⑶示例
12.351
325.525
1.保留2位小数——舍去千分位以后的小数位保留到百分位。

=ROUNDDOWN(A1,2) =ROUNDDOWN(A2,2)
12.35 325.52
2.舍去小数位保留整数——舍去十分位及以后的小数位保留整数
=ROUNDDOWN(A1,0) =ROUNDDOWN(A2,0)
12 325
3.整数保留到十位——整数部分舍去个位上大于0的数字
=ROUNDDOWN(A1,-1) =ROUNDDOWN(A2,-1) =ROUNDDOWN(A2,-2)
10 320
7.VLOOKUP函数的使用方法
6 / 13
VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。

VLOOKUP（查找目标，查找范围，返回值的列数，精确O R模糊查找)
例1：如下图所示，要求根据表二中的姓名，查找姓名所对应的年龄
公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)
参数说明：
7 / 13
1查找目标：就是你指定的查找的内容或单元格引用。

本例中表二A列的姓名就是查找目标。

我们要根据表二的名”在表一中A列进行查找。

公式：B13 =VLOOKUP(A13 ,$B$2:$D$8,3,0)
2查找范围（VLOOKUP(A13,$B$2:$D$8,3,0)）：指定了查找目标，如果没有说从哪里查找，
EXCEL肯定会很为难。

所以下一步我们就要指定从哪个范围中进行查找。

V L OOKUP的这第二个参数可以从一个单元格区域中查找，也可
以从一个常量数组或内存数组中查找。

本例中要从表一中进行查找，那么范围我们要怎么指定呢？这里也是极易出
错的地方。

大家一定要注意，给定的第二个参数查找范围要符合以下条件才不会出错：
A查找目标一定要在该区域的第一列。

本例中查找表二的姓名，那么姓名所对应的表一的姓名列，那么表一的姓名
列（列）一定要是查找区域的第一列。

像本例中，给定的区域要从第二列开始，即$B$2:$D$8，而不能是$A $2:$D$8。

因为查找的“姓名”不在$A$2:$D$8区域的第一列。

B该区域中一定要包含要返回值所在的列，本例中要返回的值是年龄。

年龄列（表一的D列）一定要包括在这个围内，即：$B$2:$D$8，如果写成$B$2:$C $8就是错的。

3返回值的列数（B13 =VLOOKUP(A13,$B$2:$D$8,3 ,0)）。

这是V LOOKUP第3个参数。

它是一个整数值。

它怎么得的呢。

它是“返回值”在第二个参数给定的区域中的列数。

本例中我们要返回的是“年龄”，它是第二个参数查找范
8 / 13
围$B$2:$D$8的第3列。

这里一定要注意，列数不是在工作表中的列数（不是第4列），而是在查找范域的第几
列。

如果本例中要是查找姓名所对应的性别，第3个参数的值应该设置为多少呢。

答案是2。

因为性别在$B$2:$D 的第2列中。

），最后一个参数是决定函数精确和模糊查找的关键。

精确
4精确O R模糊查找
（VLOOKUP(A13,$B$2:$D$8,3,0 )
即完全一样，模糊即包含的意思。

第4个参数如果指定值是0或F A LSE就表示精确查找，而值1或TRUE时则表
示模糊。

这里蓝色提醒大家切记切记，在使用VLOOKUP时千万不要把这个参数给漏掉了，如果缺少这个参数默为
值为模糊查找，我们就无法精确查找到结果了。

7. IF函数
C列“等级”的评定由IF函数来求得，C2=IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=70,"中等",IF(B2>=60,"及格","
补考"))))
9 / 13
9.名次排位函数——RANK函数
rank函数最常用的是求某一个数值在某一区域内的排名。

rank函数语法形式：rank(number,ref,[order])
函数名后面的参数中n u mber为需要求排名的那个数值或者单元格名称（单元格内必须为数字），ref为排名的参照
数值区域，order的为0和1，默认不用输入，得到的就是从大到小的排名，若是倒数第几，order的值请使
10 / 13
用
1。

下面给出几个r a nk函数的范
例：
示例1：正排名
此例中，我们在B2单元格求20这个数值在A1:A5区域内的排名情况，并没有输入o r d er参数，不输入o r der
参数的情况下，默认o r der值为0，也就是从高到低排序。

此例中20在
A1:A5区域内的正排序是1，所以显示
的结果是
1。

示例2：
倒排名
11 / 13
示例3：求一列数
的排名
10.分解时间函数——YEAR、MOUTH、DAY函数
12 / 13
13 / 13。