一、判断公式

1、把公式返回的错误值显示为空

公式:C2=IFERROR(A2/B2，'')

说明:如果是错误值，则为空，否则正常显示。

将公式返回的错误值显示为空。

2、IF的多条件判断

公式: C2=if (and (a2500，B2='未过期''补货''')

说明:两个条件都用AND建立，任一条件都用OR函数建立。

多条件判断IF

二、统计公式

3、统计两表重复

公式:B2=COUNTIF(Sheet15！答：答，答2)

说明:如果返回值大于0，则存在于另一个表中，0不存在。

两个表的重复统计。

4、统计年龄在30~40之间的员工个数

公式:=频率(D2:D8，{40，29})

统计年龄在30岁到40岁之间的员工人数。

5、统计不重复的总人数

公式:C2=sum product(1/COUNTIF(A2:A8，A2:A8))

说明:用COUNTIF统计每个人的出现次数，用出现次数除以1，然后相加。

统计非重复的总数。

6、按多条件统计平均值

F2公式:=averageifs (d: d，b: b，'金融'C:C，'大专')

根据多个条件的统计平均值

7、中国式排名公式

公式:=SUMPRODUCT(($ D $ 4:$ D $ 9=D4)*(1/COUNTIF(D$4:D$9，D $ 4 😀 $ 9)))

二、数字处理

8、取绝对值：=ABS(数字)

9、数字取整：=INT(数字)

10、数字四舍五入：=四舍五入(数字，小数位)

四、求和公式

11、隔列求和

公式:H3=苏米夫($A$2:$G$2，H$2，A3:G3)

or=sumproduct((mod(column(B3:G3)，2)=0) * B3: G3)。

说明:如果标题行没有规则，请使用第二个公式。

每隔一列求和

12、单条件求和

公式:F2=SUMIF(A:A，E2，C:C)

说明:sumif功能的基本用法

13、单条件模糊求和

公式:详情见下图。

说明:如果需要模糊求和，需要掌握通配符的使用，其中星号表示任意数量的字符，如' *A* '表示A前后任意数量的字符，即包括A。

单条件模糊求和

14、多条求模糊求和

公式:C11=sumifs(C2:C7，A2:A7，A11'* 'B2:B7，B11)

说明:在sumifs中可以使用通配符*。

公式多条目模糊求和

15、多表相同位置求和

公式:B2=SUM(sheet 1:sheet 19！B2)

说明:在表格中间删除或增加表格后，公式结果会自动更新。

查找相同位置的多个表格的总和

16、按日期和产品求和

公式:F2=SUMPRODUCT((月$ A $ 2:$ A $ 25)=F $ 1)*($ B $ 2:B $ 25=$ E2)* $ C $ 2:C $ 25)

说明:SUMPRODUCT可以完成多条件求和。

按日期和产品汇总说明

五、查找与引用公式

17、单条件查找

公式:C11=VLOOKUP(B11，B3:F7，4，FALSE)

说明:搜索是VLOOKUP擅长的，也是它的基本用法。

单一条件搜索

18、双向查找

公式:=INDEX(C3:H7，MATCH(B10，B3:B7，0)，MATCH(C10，C2:H2，0))

说明:使用MATCH函数查找位置，使用INDEX函数获取值。

双向搜索

19、查找最后一个符合条件记录

公式:详情见下图。

说明: 0/(条件)可以把不合格的变成错误值，而lookup可以忽略错误值。

查找最后一条符合条件的记录。

20、多条件查找

公式:详情见下图。

说明:公式的原理和前面的公式一样。

多条件搜索

21、指定非空区域最后一个值查找

公式:详情见下图。

说明:省略

指定非空区域的最后一个值查找。

22、区间取值

公式:详情见下图。

说明:vLOOKUP和LOOKUP函数都可以按间隔取值。请注意，销售一栏中的数字必须按升序排列。

间隔值

六、字符串处理公式

23、多单元格字符合并

公式:C2=语音(A2:A7)

说明:语音功能只能合并字符内容，不能合并数字。

多单元格字符合并

24、截取除后3位之外的部分

公式:=左(D1，伦(D1)-3)

说明:LEN计算总长度，LEFT从左边切下总长度-3块。

截取除最后3位以外的部分。

25、截取 – 之前的部分

公式:B2=Left(A1，FIND('-'A1)-1)

说明:用FIND功能找到位置，用左拦截。

截取-前一部分

26、截取字符串中任一段

公式:B1=TRIM(MID(SUBSTITUTE $ A1，” REPT('20))，20，20))

说明:公式被强行插入n个空字符截取。

截取字符串中的任意段落。

27、字符串查找

公式: B2=if (count('河南'A2))=0，'否''是')

说明：FIND查找成功，返回字符的位置；否则返回一个错误值，COUNT可以统计出位数，用来判断搜索是否成功。

28、字符串查找一对多

公式: B2=if (count (find ({ '辽宁''黑龙江''吉林' }，A2))=0，'其他''东北')

说明:将FIND的第一个参数设置为常量数组，使用COUNT函数对FIND结果进行计数。

七、日期计算公式

29、两日期间隔的年、月、日计算

A1是开始日期(2011年12月1日)，B1是结束日期(2013年6月10日)。

计算：

相隔几天？=datedif(A1，B1，' d ')结果：557之间有几个月？=datedif(A1，B1，' m ')结果：18之间有多少年？=datedif(A1，B1，' Y ')结果：1不管年份相隔多少个月？=datedif(A1，B1，' Ym ')结果：6不管年份相隔多少天？=datedif(A1，B1，' YD ')结果：192，不管年和月之间相隔多少天？=datedif(A1，B1，' MD ')结果：9datedif函数第3个参数说明：。

“y”时间周期中的整年数。“m”周期中的整月数。“d”时间段中的天数。“MD”日的区别。忽略日期中的月份和年份。YM的月差。忽略日期中的日和年。YD '天的差异。忽略日期中的年份。30.扣除周末的工作日天数

公式：

C2=网络天数。INTL(IF(B2DATE(2015，1，1)，DATE(2015，1，1)，B2)，DATE(2015，1，31)，11)

说明:返回两个日期之间的所有工作日的数量，并使用参数来指示哪一天是周末以及有多少天是周末。周末和任何指定为假日的日期不被视为工作日。

扣除周末工作日数。

八、其他常用公式

31、创建工作表目录的公式

列出所有工作表名称，然后自动添加超链接来管理工作表，非常方便。

使用方法：

第1步:在定义名称中输入公式：

=MID(GET。工作簿(1)，查找(']'获取。工作簿(1)) 1，99)T(现在())

创建工作表目录的公式步骤1

第2步:在工作表中输入一个公式并拖动它。工作表列表和超链接会自动添加。

=if error(HYPERLINK(' # ' ' INDEX(sh name，ROW(A1))' 'A1 'INDEX(Shname，ROW(A1))，'')

创建工作表目录的公式步骤2

32、中英文互译公式

公式:=filter XML(web service('

http://fanyi.youdao.com/translate?i='A2'doctype=xmlversion ')，'//translation ')

汉英翻译公式

33.VLOOKUP典型应用

vlookup的经典应用是一对一的正向查找，比如下图的表1和表2。要将表2中的销售量填入表1，您可以使用C3的公式：

=IFNA(VLOOKUP(A3，E:F，2，)，'未找到')下拉，任务完成。

34.查找最后一次出现的数据

找“最后”，LOOKUP比VLOOKUP好。比如下面的表4，三个人的名字不止一次出现，而表3只需要实现统计表4中最新的销售数据。

这时，C3单元格中的公式是：

=ifna (lookup (1，0/(e $3: e $8=a3)，g $3: g $8)，'未找到')。

35.一对多查找

一对多搜索一直是个难题。如果在Excel中有最新的动态数组函数过滤器，可以省不少力气(Office 365专属功能):

=过滤器(B3:C17，A3:A17=E22)

36.模糊查找

模糊搜索也是常见的需求。比如找出图表6中的所有字母A，公式如何穷尽？

{=index (a: a，small (if (is number (find (c $3，a $3: a $14))，row ($3: $14)，15)，row (a1))'}或者使用365版本中的公式：

=FILTER(A3:A14，ISNUMBER(FIND(C3，A3:A14)))

37.一维表转二维表

还有一些问题，好像不是搜索，但也可以通过搜索来解决。例如，要将下图中的一维表转换为二维表，表中的公式为：

{=INDEX($C:$C，MATCH($E3F$2，$A:$A$B:$B，)}

38.条件计数

如下图所示，工资8000以上的人群比例要统计一下。

=COUNTIF(E:E，' 8000')/COUNT(E:E)

COUNTIF(E:E，' 8000 ')用于计算8000以上的数，然后COUNT(E:E)用于计算E列中所有值的个数，将它们相除得到比例。发生了什么事？你在拖延时间吗？

39.条件求和

如下图所示，总金额要按部门计算。

=SUMIF(B:B，F2，D:D)

如果B列中的部门等于单元格F2中的部门，则将对应于D列的金额相加.

40.有道翻译

如下图，A2输入要翻译的文本，B2输入以下公式，即可实现英汉翻译。当然，只需要保持计算机连接：

=filter XML(web service(' http://fanyi . youdong . com/translate？i='A2'doctype=xmlversion ')，'//translation ')

41.合并单元格计数

如下图，要统计每个店的人数。

同时选择D2:D13单元格区域，在编辑字段中输入公式，按Ctrl键输入：

=COUNTA(B2:B13)-SUM(D3:D13)

42.合并单元格添加序号

如下图，要根据部门添加序号。

同时选择A2:A15单元格区域，在编辑栏中输入公式，按Ctrl键输入：

=COUNTA(2美元：B2)

43.合并单元格求和

如下图，要统计每个店铺的总销售金额。

同时选择D2:D10单元格区域，在编辑字段中输入公式，按Ctrl键输入：

=总和(C2:C20)-总和(D3:D20)

244?_iz=31825&from=article.detail&x-expires=1705728701&x-signature=OP7zwfFVDTr81xZ7RclbFgiMZH8%3D&index=35″ alt=”ee1143d4da624eb4977adbc109f3f244?_iz=31825&from=article.detail&x-expires=1705728701&x-signature=OP7zwfFVDTr81xZ7RclbFgiMZH8%3D&index=35″ />

SUM(C2:C20)即当前行及以下所有C列数据的和，SUM(D3:D20)是本类别之后所有类别之和，二者相减，得到是本类别的和。