全国计算机二级Office-Excel函数的31条案例，按着表格数据与步骤来学习就更容易掌握哦！

1、根据学期中三种成绩的比重分配，获取学期成绩。

=C8*$C$5+D8*$D$5+E8*$E$5

引用方式有绝对引用、混合引用、相对引用，可以借助F4键快速切换。

如果学了SUMPRODUCT函数，也可以换种方式。

=SUMPRODUCT(C8:E8,$C$5:$E$5)

2、根据成绩的区间判断，获取等级。

=IF(B5>=90,"优秀",IF(B5>=80,"良","及格"))

IF函数语法：

=IF(条件,条件为真返回值,条件为假返回值)

IF函数图解

3、重量±5以内为合格，否则不合格。

=IF(AND(A4>=-5,A4<=5),"合格","不合格")< p="">

=IF(ABS(A4)<=5,"合格","不合格")< p="">

AND函数当所有条件都满足的时候返回TRUE，否则返回FALSE。

ABS是返回数字的绝对值。

4、根据对应表，查询2月销量。

=VLOOKUP(A4,F:G,2,0)

VLOOKUP函数语法：

=VLOOKUP(查找值,在哪个区域查找,返回区域第几列,精确或模糊匹配)

第4参数为0时为精确匹配，1时为模糊匹配。VLOOKUP函数图解如下

5、根据番号查询品名和型号。

=VLOOKUP($A4,$E:$G,2,0)

=VLOOKUP($A4,$E:$G,3,0)

正常情况下可以用VLOOKUP函数，然后将参数3分别设置为2和3，不过考虑到列数可能比较多，也就是通用的情况下，所以用COLUMN函数作为第3参数。这个函数是获取列号，B1的列号就是2，C1的列号就是3，依次类推。

=VLOOKUP($A4,$E:$G,COLUMN(B1),0)

6、正确显示文本+日期的组合。

=A4&TEXT(B4,"!_yyyy-m-d")

=A4&TEXT(B4,"!_e-m-d")

&的作用就是将两个内容合并起来，不过遇到日期，合并后日期就变成数字。有日期存在的情况下要借助TEXT函数，显示年月日的形式用yyyy-m-d，4位数的年份也可以用e代替。这里添加_是为了防止以后有需要处理，可以借助这个分隔符号分开，因为是特殊字符前面加!强制显示。

7、计算收入大于3万的人的累计收入总和。

=SUMIF(C:C,">30000",C:C)

SUMIF函数语法：=SUMIF(条件区域,条件,求和区域),对区域进行条件求和。

8、序列号为102开头的累计收入总和。

=SUMIF(A:A,"102*",C:C)

通配符号有2个，一个是*代表全部，102开头就是102*，如果是包含102用*102*。另一个通配符是?代表一个字符，比如现在有3个字符，就用???。

说明：通配符只能针对文本格式进行处理，数字格式的序列号不可以用。

9、统计每一种水果的购买次数。

10、统计每一种水果运费大于20元的次数。

=COUNTIF(B:B,G5)

=COUNTIFS(B:B,G14,E:E,">20")

COUNTIF函数语法：

=COUNTIF(条件区域,条件)

COUNTIFS函数语法：

=COUNTIFS(条件区域1,条件1,条件区域2,条件2……)

COUNTIF(COUNTIFS)对区域进行条件计数，有S可以多条件计数。

11、宝贝标题包括耳钉，就返回首饰，否则为其他。

=IF(COUNTIF(A4,"*耳钉*"),"首饰","其他")

=IF(ISERROR(FIND("耳钉",A4)),"其他","首饰")

根据SUMIF函数支持通配符的特点，COUNTIF函数也支持，包含就用*耳钉*。

当然也能借助FIND函数判断，如果有出现就返回数字，否则返回错误值，而ISERROR函数就是判断是否为错误值。

12、根据身份证号码，获取性别、生日、周岁。

性别：从15位提取3位，如果奇数就是男，偶数就是女。

=IF(MOD(MID(A4,15,3),2),"男","女")

MOD函数就是取余数的意思，奇数除以2的余数就是1，偶数除以2的余数就是0。1在这里相当于TRUE也就是返回男，0就是FALSE返回女。

高版本中用ISODD函数判断是不是奇数，用ISEVEN函数判断是不是偶数，所有也可以将公式改成高版本的。

=IF(ISODD(MID(A2,15,3)),"男","女")

生日：从第7位提取8位，设置公式后将单元格设置为日期格式。

=--TEXT(MID(A4,7,8),"0-00-00")

周岁：

=DATEDIF(D4,TODAY(),"y")

TODAY也可以换成NOW。

13、把歌曲和作者合并到一个单元格。

=A4&"-"&B4

&就是将字符连接起来，叫连字符。

14、将字符串合并成一个单元格。

=PHONETIC(A4:K4)

PHONETIC这是一个很神奇的文本合并函数，可以轻松将内容合并起来，不过只针对文本，切记！

如果PHONETIC解决不定，下面这篇文章就可以解决。

前14个运用属于基础运用有详细解释，后6个属于进阶需要靠自己动脑思考。

15、根据产品名称和城市查询销售额

=VLOOKUP(G4,$A$3:$E$9,MATCH(H4,$A$3:$E$3,0),0)

=SUMPRODUCT(($A$4:$A$9=G4)*($B$3:$E$3=H4)*$B$4:$E$9)

16、品牌月度销售额查询

=SUMIFS(C:C,A:A,E4,B:B,F4)

17、分别提取产品和编码

=LEFT(A4,LENB(A4)-LEN(A4))

=RIGHT(A4,2*LEN(A4)-LENB(A4))

18、从起始时间提取日期和时间

=--LEFT(A4,FIND("",A4)-1)

=--RIGHT(A4,LEN(A4)-FIND("",A4))

19、将省份（区）和城市分离出来

=LEFT(A4,FIND(IF(ISNUMBER(FIND("区",A4)),"区","省"),A4))

=RIGHT(A4,LEN(A4)-LEN(B4))

20、知道某日期，获取下个月第一天

21、轻松转换成星期格式

TEXT函数：=TEXT(C2,"aaaa")

TEXT函数中的代码：“aaaa”，可以将日期格式，轻松转换成星期格式；

22、得到所有员工的姓氏

LEFT函数：=LEFT(B2,1)

LEFT函数，用于截取文本中左侧的字符串，通过LEFT函数，可以返回：所有员工的姓氏；

23、得到所有员工的名字

RIGHT函数：=RIGHT(B2,1)；

RIGHT函数，用于截取文本中右侧的字符串，通过RIGHT函数，可以返回：所有员工的名字；

24、求出员工【出生日期】的年份

YEAR函数：=YEAR（C2）

YEAR函数，用于返回时间格式的年份，使用YEAR函数，可以批量返回：员工【出生日期】的年份；

25、求出员工【出生日期】的月份

MONTH函数：=MONTH（C2）

MONTH函数，用于返回时间格式的月份，使用MONTH函数，可以批量返回：员工【出生日期】的月份；

26、求出员工【出生日期】的日期

DAY函数：=DAY（C2）

DAY函数，用于返回时间格式的日期，使用DAY函数，可以批量返回：员工【出生日期】的日期；

27、算出当前单元格的行号

ROW函数：=ROW（B2）

ROW函数，用于返回单元格的行号；选中G2单元格，在编辑栏输入函数公式：=ROW（）；然后输入函数参数：用鼠标选中B2单元格，并按回车键结束确认，即可返回B2单元格的行号：2；

28、算出单元格的列号

COLUMN函数：=COLUMN（B2）

COLUMN函数，用于返回单元格的列号；选中G2单元格，在编辑栏输入函数公式：=COLUMN（）；然后输入函数参数：用鼠标选中B2单元格，并按回车键结束确认，即可返回B2单元格的列号：2；

29、根据姓名查找这人的学历

VLOOKUP函数：=VLOOKUP（H2，B：F，5，0）；

VLOOKUP函数，可以在表格区域内，查找指定内容；通过【姓名】列的：貂蝉，可以定位到相关【学历】列的：高中；

30、把公司员工的月薪排名

RANK函数：=RANK（E2，$E$2，$E$9）;

RANK函数；用于返回指定单元格值的排名，我们选中F2：F9单元格区域，并输入RANK函数公式，然后按Excel快捷键【Ctrl+Enter】，即可返回月薪排名；

31、求几个区域的乘积总和

SUMPRODUCT函数：=SUMPRODUCT（A2：B4，C2：D4）;

利用SUMPRODUCT函数，可以计算出：A2：B4单元格区域，与C2：D4单元格区域的乘积总和；