二级office函数部分.docx
《二级office函数部分.docx》由会员分享，可在线阅读，更多相关《二级office函数部分.docx（12页珍藏版）》请在冰豆网上搜索。
二级office函数部分
第五套
Excel
从身份证号中提取性别
第一种：
=IF（ISODD（MID（C2,17,1））,"男","女"）
第二种：
=IF（MOD（MID（C2,17,1）,2）=1,"男","女"）
从身份证号中提取出生年月
=MID（C2,7,4）&"年"&MID（C2,11,2）&"月"&MID（C2,13,2）&"日"
从身份证号中提取年龄
第一种：
=INT（（TODAY（）-[@出生日期]）/365）
第二种：
=DATEDIF（E5,TODAY（）,"y"）
第三种：
=ROUND（YEARFRAC（E7,NOW（））,0）
vlookup提取姓名
=VLOOKUP（A2,初三学生档案!
$A$2:
$G$56,2,0）
=VLOOKUP（$A3,INDIRECT（C$2&"!
a:
f"）,6,0）
INDIRECT：
返回文本字符串所指定的引用（间接引用）
INDIRECT（C$2&"!
a:
f"）=英语!
A:
F（英语!
A:
F的意思是：
语文单元格的A到F列）
第六套
说明：
=countifs（条件区域1，条件1，条件区域2，条件2）
将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。
COUNTIFS（criteria_range1,criteria1,[criteria_range2,criteria2]…）
criteria_range1必需。
在其中计算关联条件的第一个区域。
criteria1必需。
条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。
例如，条件可以表示为32、">32"、B4、"苹果"或"32"。
riteria_range2,criteria2,...可选。
附加的区域及其关联条件。
最多允许127个区域/条件对
第八套
=IF（（WEEKDAY（A3,2））>5,"是","否"）
WEEKDAY把日期转换成数字，if判断如果大于5就是6和7就是周末，周末要加班
=LEFT（C3,3）从左边开始提取3位
第9套
应交人所得税：
=IF（K3>80000,K3*45%-13505,IF（K3>55000,K3*35%-5505,IF（K3>35000,K3*30%-2755,IF（K3>9000,K3*25%-1005,IF（K3>4500,K3*20%-555,IF（K3>1500,K3*10%-105,K3*3%））））））
第10套
第一种方法：
=IF（MID（B3,3,2）="01","法律一班",IF（MID（B3,3,2）="02","法律二班",IF（MID（B3,3,2）="03","法律三班","法律四班"）））
第二种方法：
="法律"&NUMBERSTRING（INT（MID（B3,3,2））,1）&"班"
解析：
NUMBERSTRING可将小写数字转换成中文大写数字
=NUMBERSTRING（VALUE,TYPE）
value：
要转化的数字
type：
返回结果的类型，有三种：
1，2，3
=NumberString（1234567890,1）返回结果：
一十二亿三千四百五十六万七千八百九十
=NumberString（1234567890,2）返回结果：
壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
=NumberString（1234567890,3）返回结果：
一二三四五六七八九〇
第11套
方法：
第1种：
插入辅助列：
返回日期中的月份：
=MONTH（[@日期]）
返回日期中的年份：
=YEAR（[@日期]）
=SUMIFS（销售订单!
$G$3:
$G$678,销售订单!
$H$3:
$H$678,2013,销售订单!
$I$3:
$I$678,COLUMN（）-1,销售订单!
$D$3:
$D$678,$A4）
解析:
COLUMN（）函数是指返回其单元格的列号
第2种：
=SUMIFS（销售订单!
G3:
G678,销售订单!
D3:
D678,[@图书名称],销售订单!
B3:
B678,">=2013年1月1日",销售订单!
B3:
B678,"<2013年2月1日"）
第12套：
第一种方法
=TEXT（（MONTH（A3）+1）/3,0）&"季度"
解析：
TEXT可将数值转换成文本
MONTH提取单元格的月份
第二种方法：
=LEN（2^MONTH（A3））&"季度"
解析：
LEN可以返回一个字符的长度
A3单元格日期是2013年11月
MONTH（日期）等于该日期的月份得出的结果是11
2^11=2的11次方结果是2048
=len（2048）表示2048的字符长度是4后面在连接上季度两个字，即：
4季度
第13套
=（H2-F2）*24+（I2-G2）
解析：
（出场日期减进场日期）*24（进场时间减出场时间）
=ROUNDUP（（HOUR（J2）*60+MINUTE（J2））/15,0）*E2
解析：
ROUNDUP：
向上四舍五入
HOUR：
返回小时值
MINUTE：
返回分钟数值
=INT（（HOUR（J2）*60+MINUTE（J2））/15）*E2
解析：
ROUNDUP：
向上四舍五入
HOUR：
返回小时值
MINUTE：
返回分钟数值
第14套
求：
单价单元格
=VLOOKUP（[@图书名称],表2,2,0）
求：
销售额小计单元格
=ROUND（IF（F3>=40,E3*F3*0.93,E3*F3）,2）
求：
所属区域单元格
=VLOOKUP（LEFT（G3,3）,表3,2,0）
求：
销售额B3单元格
=SUMIFS（表1[销售额小计],表1[日期],">=2013年1月1日",表1[日期],"<=2013年12月31日"）
求：
销售额B4单元格
=SUMIFS（表1[销售额小计],表1[图书名称],订单明细!
D7,表1[日期],">=2012年1月1日",表1[日期],"<=2012年12月31日"）
求：
销售额B5单元格
=SUMIFS（表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],">=2013年7月1日",表1[日期],"<=2013年9月30日"）
求：
销售额B6单元格
=SUMIFS（表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],">=2012年1月1日",表1[日期],"<=2012年12月31日"）/12
求：
销售额B7单元格
=SUMIFS（表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],">=2013年1月1日",表1[日期],"<=2013年12月31日"）/B3
升级会员 