必看！最好的28个Excel公式

让我们来看看这28个公式。你还记得几个？

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(真返回值假返回值的条件)。

中频功能图

3.重量在5以内为合格，否则为不合格。

=IF(AND(A4=-5，A4=5)，“通过”，“失败”)。

=IF(ABS(A4)=5，“通过”、“失败”)。

当满足所有条件时，“与”函数返回“真”，否则返回“假”。

ABS函数返回一个数字的绝对值。

4.确定三个单元格是否满足条件。

5.了解六个常用函数的用法：SUM、AVERAGE、COUNT、MAX、MIN和rank。

6.根据对应的表格查询2月份的销量。

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

VLOOKUP函数的语法：

=VLOOKUP(查找值，在哪个区域查找，返回区域的列，精确或模糊匹配)。

第四个参数是0表示精确匹配，1表示模糊匹配。

VLOOKUP函数的图形

7.根据序列号查询名称和型号。

=VLOOKUP(A4 $ e : $ G，2，0)

=VLOOKUP(A4 $ e : $ G，3，0)

正常情况下可以使用VLOOKUP功能，然后将参数3分别设置为2和3。但是，考虑到COLUMNs的数量可能相对较大，即在一般情况下，column函数用作第三个参数。这个函数就是得到列号，B1的列号是2，C1的列号是3，以此类推。

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

8.根据员工姓名查询员工编号。

9.根据名称在数据源中查找排名。

10.根据业务员介绍，反向搜索产品名称。

11.提取字符：截取A2单元格第九位后的字符。

12.今天是几号？今天是星期几？如何快速获取这些信息？

13.在获得销量之前，n大，之后n小。

14.知道d前缀函数。

15.得到总金额，即数量*单价再求和。

16.正确显示文本和日期的组合。

=A4TEXT(B4，“！_yyyy-m-d”)

=A4TEXT(B4，“！_e-m-d”)

的功能是合并两个内容，但是当遇到日期时，合并的日期会变成一个数字。如果有日期，使用TEXT函数，显示年、月、日的格式为yyyy-m-d，4位年份可以用e代替，此处添加了_防止以后处理，可以用这个分隔符隔开，因为是在特殊字符前添加的！强制显示。

17.计算收入在3万以上的人的累计总收入。

=SUMIF(C:C，“30000”c : c)

SUMIF函数语法：

=SUMIF(条件区域、条件区域、求和区域)

区域的条件求和。

18.从序列号102开始的累计总收入。

=SUMIF(A:A，“102*”，C:C)

有两个通配符，一个是*代表全部，102的开头是102*。如果它包含102，请使用*102*。另一个通配符是？比如一个字符，如果现在有3个字符，就用？

说明：通配符只能处理文本格式，不能使用数字格式的序列号。

19.统计每种水果的购买次数，每种水果运费高于20元的次数。

=COUNTIF(B:B，G5)

=COUNTIFS(B:B、G14、E:E、“20”)

COUNTIF函数语法：

=COUNTIF(条件区域，条件)

COUNTIFS函数语法：

=COUNTIFS(条件区域1、条件区域1、条件区域2、条件2.)

COUNTIF(COUNTIFS)有条件地计数区域，s可以有条件地计数。

20.如果宝宝称号包括耳环，那就回归首饰，否则就是其他。

=if (countif (a4)、“*螺柱*”、“珠宝”、“其他”)。

=IF(ISERROR(FIND(“耳环”，A4))，“其他”，“珠宝”)。

根据SUMIF函数支持通配符的特点，COUNTIF函数也支持通配符，使用*耳环*进行包含。

当然也可以借助FIND功能来判断。如果出现，会返回一个数字，否则会返回一个错误值，而ISERROR函数会判断是否是错误值。

21.根据身份证号，获取性别、生日、年龄。

性别：15位中选3位，奇数为男，偶数为女。

=IF(MOD(MID(A4，15，3)，2)，“男”，“女”)

MOD函数意味着取余数。奇数除以2的余数是1，偶数除以2的余数是0。这里的1相当于TRUE，即返回男性，0为FALSE，返回女性。

在更高的版本中，用ISODD函数来判断是否为奇数，用isoven函数来判断是否为ISEVEN。所以公式也可以改成更高的版本。

=IF(ISODD(MID(A2，15，3))，“男”，“女”)。

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

=文本(MID(A4，7，8)，" 0-00-00 ")

年龄：

=DATEDIF(D4，TODAY()，“y”)

也可以用NOW替换TODAY。

22.将歌曲和作者合并到一个单元格中。

=A4“-”B4

是连接字符，称为连字符。

23.将字符串组合成一个单元格。

=PHYPERIAL(a 4: k4)

语音这是一个神奇的文字合并功能，可以轻松合并内容，但只针对文字，切记！

24.根据产品名称和城市查询销售情况。

=VLOOKUP(G4，3:澳元$E$9，MATCH(H4，3:澳元$E$3，0)，0)

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

25.查询品牌月销量。

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

26.分别提取产品和代码。

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

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

27.从开始时间选择日期和时间。

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

=右(A4，LEN(A4)-FIND(“”，A4))

28.将省(区)与城市分开。

=LEFT(A4，FIND(IF(ISNUMBER(FIND(“区”，A4))，“区”，“省”)，A4))。

=右(A4，LEN(A4)-LEN(B4))