大专EXCEL函数重难点整理Word文档格式.docx
- 文档编号:22266936
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:17
- 大小:404.21KB
大专EXCEL函数重难点整理Word文档格式.docx
《大专EXCEL函数重难点整理Word文档格式.docx》由会员分享,可在线阅读,更多相关《大专EXCEL函数重难点整理Word文档格式.docx(17页珍藏版)》请在冰豆网上搜索。
120305
包宏伟
3班
=MID(A3,4,1)&
"
班"
120203
陈万地
2班
(销售情况及产品销售汇总表)-----涉及函数及要点:
RANK,数据透视表
一二季度销售总量
一二季度销售总额
销售额排名
387
640098
=RANK(D2,$D$2:
$D$21,0)
171
134406
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,在“费用类别!
$B$12”范围内,返回第2列的值,精确匹配
WEEKDAY判断星期几的函数,数字2表示1至7代表星期一到星期天
即:
如果A3内提取的星期数>
5,则为加班,返回值“是”,否则返回“否”
2013年第二季度发生在北京市的差
旅费用金额总计为:
2013年钱顺卓报销的火车票总计金
额为:
=SUMPRODUCT(1*(费用报销管理!
D74:
D340="
北京市"
),费用先将日期排序,确认时间范围,如果费用报销管理!
,则
报销管理!
G74:
G340)求和费用报销管理!
G340内的值
B3:
B401="
钱顺卓"
),1*(费两个条件,如果满足条件1名字=钱顺卓、条件2类别=火车票,则求费用报
用报销管理!
F3:
F401="
火车票"
),费用报销管理!
G3:
G401)
销管理!
G401内的和
2013年差旅费用金额中,飞机票占所=SUMPRODUCT(1*(费用报销管理!
飞机票"
),费用
有报销费用的比例为(保留2位小数)报销管理!
G401)/SUM(费用报销管理!
求满足条件=飞机票的费用报销管理!
G401内的值,除以SUM(费用报销管
理!
2013年发生在周末(星期六和星期
=SUMPRODUCT((费用报销管理!
H401="
)*(费用报销管两个条件,如果加班栏内为“是”,且费用类别为“通讯补助”,则求和费用
日)中的通讯补助总金额为:
通讯补助"
G401
(员工工资表)-----涉及函数及要点:
ROUND,IF,分类汇总,SUMPRODUCT
应交个人所得税
=ROUND(IF(K3<
=1500,K3*3/100,IF(K3<
=4500,K3*10/100-105,IF(K3<
=9000,K3ROUND,四舍五入函数,最后面的参数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,销售SUMIFS使用方法
订单!
$C$3:
$C$678,1)SUMFIFS(求和范围,条件1,条件2)
某图书在某月份的销量合
计
此处条件1为销售订单!
$E$678值为A4时(A4为书名)
此处条件2为销售订单!
$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),"
),TODAY(),"
y"
="
第"
&
RANK(F11,$F$2:
$F$45)&
名"
=IF(F11>
=102,"
优秀"
IF(F11>
=84,"
良好"
=72,"
及格
72,"
及格"
不及格"
))))
判断成绩
二级MSoffice考试常用函数整理
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,"
)”,按下回车键,就一切OK。
如图所示。
3.去尾取整函数——INT函数
(注意要区分与TRUNC函数(只取整),而INT取向下整数。
4.取整函数,只取整理。
-TRUNC函数
5/13
例:
=TRUNC(8.9),结果为8;
=TRUNC(-9.6),结果为-9;
5.四舍五入函数——ROUND函数
6.向下舍数字函数——ROUNDDOWN函数
⑴功能按指定的位数对数值进行舍入。
⑵格式ROUNDDOWN(数值或数值单元格,指定的位数)
⑶示例
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(查找目标,查找范围,返回值的列数,精确OR模糊查找)
例1:
如下图所示,要求根据表二中的姓名,查找姓名所对应的年龄
公式:
B13=VLOOKUP(A13,$B$2:
$D$8,3,0)
参数说明:
7/13
1查找目标:
就是你指定的查找的内容或单元格引用。
本例中表二A列的姓名就是查找目标。
我们要根据表二的“姓
名”在表一中A列进行查找。
B13=VLOOKUP(A13,$B$2:
2查找范围(VLOOKUP(A13,$B$2:
$D$8,3,0)):
指定了查找目标,如果没有说从哪里查找,EXCEL肯定会很为难。
所以下一步我们就要指定从哪个范围中进行查找。
VLOOKUP的这第二个参数可以从一个单元格区域中查找,也可
以从一个常量数组或内存数组中查找。
本例中要从表一中进行查找,那么范围我们要怎么指定呢?
这里也是极易出
错的地方。
大家一定要注意,给定的第二个参数查找范围要符合以下条件才不会出错:
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))。
这是VLOOKUP第3个参数。
它是一个整数值。
它怎么得来
的呢。
它是“返回值”在第二个参数给定的区域中的列数。
本例中我们要返回的是“年龄”,它是第二个参数查找范
8/13
围$B$2:
$D$8的第3列。
这里一定要注意,列数不是在工作表中的列数(不是第4列),而是在查找范围区域的第几
列。
如果本例中要是查找姓名所对应的性别,第3个参数的值应该设置为多少呢。
答案是2。
因为性别在$B$2:
$D$8
的第2列中。
4精确OR模糊查找(VLOOKUP(A13,$B$2:
$D$8,3,0)
),最后一个参数是决定函数精确和模糊查找的关键。
精确
即完全一样,模糊即包含的意思。
第4个参数如果指定值是0或FALSE就表示精确查找,而值为1或TRUE时则表
示模糊。
这里蓝色提醒大家切记切记,在使用VLOOKUP时千万不要把这个参数给漏掉了,如果缺少这个参数默为
值为模糊查找,我们就无法精确查找到结果了。
7.IF函数
C列“等级”的评定由_IF函数来求得,C2=IF(B2>
=90,"
IF(B2>
=80,"
=70,"
中等"
=60,"
补考"
9/13
9.名次排位函数——RANK函数
rank函数最常用的是求某一个数值在某一区域内的排名。
rank函数语法形式:
rank(number,ref,[order])
函数名后面的参数中number为需要求排名的那个数值或者单元格名称(单元格内必须为数字),ref为排名的参照
数值区域,order的为0和1,默认不用输入,得到的就是从大到小的排名,若是想求倒数第几,order的值请使
10/13
用1。
下面给出几个rank函数的范例:
示例1:
正排名
此例中,我们在B2单元格求20这个数值在A1:
A5区域内的排名情况,我们并没有输入order参数,不输入order
参数的情况下,默认order值为0,也就是从高到低排序。
此例中20在A1:
A5区域内的正排序是1,所以显示
的结果是1。
示例2:
倒排名
11/13
示例3:
求一列数的排名
10.分解时间函数——YEAR、MOUTH、DAY函数
12/13
13/13
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 大专 EXCEL 函数 难点 整理