Excel常用函数（上）
|
Excel技巧
|
193

4243 字
|
20 分钟

思维导图

Description of image

本文来介绍Excel中常用的函数，博主参考网上的分类，给常用（实际上是博主常用）的Excel函数大致分为6类共36个函数，很基础，也很常用。函数的基础语法都不难，主要的是当熟练掌握基础函数后，如何将它们有机组合起来，博主尽量讲完基础语法后举一些例子，帮助大家灵活理解，当然，在实际应用中肯定会有更高级、更简单的函数实现这些目的，但庞大的Excel函数很少有人可以精通，能实现目的即可，不必追求简洁的语法、高级的函数，不要为了精通Excel而精通Excel（好熟悉的句子，好像在哪里听过）。不喜欢可以喷，没必要关闭。

日期函数

DAY

返回以序列数表示的某日期的天数。 天数是介于 1 到 31 之间的整数。

语法：

=DAY(serial_number)

其中，参数serial_number应为日期格式或数字格式，例如，单元格为日期“2024-12-09”，则返回“9”。请注意，因为Excel会将日期存储为可用于计算的序列号，例如，1900 年 1 月 1 日的序列号是 1，即0为1999年1月0日，每增加一天，序列号增加1。

所以，参数serial_number传入数字类型也不会返回错误，数字本身就是序列号，Excel会自动将时间转化为可计算的数字。

MONTH

返回日期（以序列数表示）中的月份。 月份是介于 1（一月）到 12（十二月）之间的整数。

语法：

=MONTH(serial_number)

说明同DAY。

YEAR

返回对应于某个日期的年份。 Year 作为 1900 – 9999 之间的整数返回。

语法：

=YEAR(serial_number)

说明同DAY。

WEEKDAY

返回对应于某个日期的一周中的第几天。 默认情况下，天数是 1（星期日）到 7（星期六）范围内的整数。

语法（参数中的[],为可选参数，可选参数设有默认值，当不传入时即为默认值，在Excel很多参数都有可选参数）：

=WEEKDAY(serial_number,[return_type])

Serial_number的说明和DAY一样；

可选参数return_type即可选返回的类型，在WEEKDAY提供参数数字和返回类型：

wd

我也不知道为什么返回一个星期需要有这么多的参数，有知道的朋友可以打在评论区。

默认参数星期一返回的是2，因为一个星期第一天是星期日（老外的算法奇奇怪怪）。

EOMONTH

返回某个月份最后一天，第二个参数如果为负数，则返回之前的月份最后一天；正数则为之后。

语法：

=EOMONTH(start_date, months)

参数说明：start_date传入日期，months为相隔月份，当months为0时，为当月最后一天，当months为-1时，为上月最后一天；1则为下月最后一天，以此类推。

EOMONTH还可以用更多的玩法，例如，返回“2024-12-09”月份的第一天：

=EOMONTH("2024-12-09", -1)+1

可以利用EOMONTH先返回上月的最后一天，再加1，则为当月的第一天。

DATEDIF

Excel中的隐藏函数，DATEDIF（在微软官方介绍中，DATEDIF是有bug的，所以在使用DATEDIF时没有补全提示（微软出来挨打！））

函数作用：计算两个日期之间相隔的天数、月数或年数。

=DATEDIF(start_date,end_date,unit)

参数：start_date开始日期；

end_date结束日期；

Unit（必须）返回的信息类型， 传入的类型有：

  • “Y” 一段时期内的整年数；
  • “M” 一段时期内的整月数；
  • “D” 一段时期内的天数；
  • “MD” start_date 与 end_date 之间天数之差。 忽略日期中的月份和年份
    （出bug的参数，也是这个参数的bug导致微软把DATEDIF雪藏）;
  • “YM” start_date 与 end_date 之间月份之差。 忽略日期中的天和年份;
  • “YD” start_date 与 end_date 的日期部分之差。 忽略日期中的年份。

具体效果如下（因为“MD”特殊的bug体质，所以博主就不再演示“MD”参数效果，大家在使用尽量避免使用“MD”）：

datedif

数学函数

涉及数学计算的函数这里只介绍两个，也是最常用的，SUMIFS（SUM和SUMIF就不多过废话了）和ROUND（同理还有ROUNDDOWN和ROUNDUP）。

SUMIFS

SUMIFS 函数是一个数学与三角函数，用于计算其满足多个条件的全部参数的总量。

其语法如下：

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

中括号[]中的条件你可以继续添加，最大支持127个条件，添加的规则是criteria_range（条件范围）、criteria（条件），但是你必须注意，你的条件范围和条件必须是对应且成对出现的，即条件范围1，条件1，条件范围2，条件2，条件范围3，条件3…否则会报错或者计算结果不正确。

很多结尾是ifs的函数遵循这个规则，例如，minifs、maxifs、coundifs等等。

用法简介：

在IFS系的函数中，你可以使用通配符“*”，连接符“&”，比较“<”、“>”、“=”、“<>”等，例如：

SUMIFS演示案例SUMIFS演示案例
=SUMIFS($R$2:$R$19,$T$2:$T$19,"=H*",$U$2:$U$19,">"&"2024-5-1")

解释：求和范围是$R$2:$R$19，条件1为户名T2-T19中开头为H;条件2为存款日期U2-U19大于2024-5-1。

=SUMIFS($R$2:$R$19,$S$2:$S$19,"翡翠城市商业银行",$T$2:$T$19,"<>"&$T$2)

解释：求和范围是$R$2:$R$19，条件1为存款银行为”翡翠城市商业银行”;条件2为户名T2-T19中不为T2，即“Siqi Liu”，“<>”为不等于号。

=SUMIFS($R$2:$R$19,$U$2:$U$19,"<="&"2024-12-31",$U$2:$U$19,">="&"2024-1-1")

解释：求和范围是$R$2:$R$19，条件1为存款日期小于等于2024-12-31；条件2为存款日期大于等于2024-1-1。

ROUND

博主个人宣布这是计算机计算发明以来最伟大的函数ROUND！Excel中采用的是IEEE 754标准进行计算，不可避免的带来精度损失的问题，例如：

=1.2-1.1=0.1

上述公式输入到Excel会返回False，因为1.2-1.1的结果是0.0999999999999999

EXCEL你怎么回事。不过，微软表示这锅不背，二进制下众生平等，都会损失精度，所以你会看到同事的表格中一大推的小数点后999999999。

Excel也给出了解决方法，就是嵌套一个ROUND。

=ROUND(1.2-1.1,2)=0.1

此时的结果返回True。

ROUND的语法为ROUND(number, num_digits)，有两个参数，number, num_digits。

其中number为数字，num_digits为保留的小数位数，四舍五入。

ROUNDDOWN 和 ROUNDUP：

ROUNDDOWN顾名思义就是退1，ROUNDDOWN(2.16,1)=2.1；

ROUNDUP为进1，ROUNDUP(2.11,1)=2.2。

那么问题来了，ROUNDUP(2.10,1)会不会等于2.2？

统计函数

Excel中统计函数出现的概率还是比较高的，博主在此列举几个最常用的统计函数，包括它们相应的IFS函数（if函数的语法和ifs大差不差而且有了ifs，if的使用频次就降低了很多本文就不单独列举if的函数了，日常使用中可以直接用ifs升级if的函数）。

MIN和MAX

MIN：返回一组值中的最小值。

MAX：返回一组值中的最大值。

=MIN(number1, [number2], ...)
=MAX(number1, [number2], ...)

请注意，MIN和MAX的参数接收有以下的特点：

  • 参数可以是数字或者是包含数字的名称、数组或引用；
  • 传入区域中的空白单元格、逻辑值、文本都会被忽略；
  • 传入区域有错误，就会返回错误；
  • 传入区域如果没有任何数字或可转化为数字的数据，则都会返回0。

在MIN和MAX中，你可以传入多个区域、引用或者具体数字，例如：

MINMAX

屏蔽错误值带来的影响最好的方法就是提前清洗数据，用F5定位数据进行修改；

当然也可以使用AGGREGATE函数：

=AGGREGATE(15, 6, A1:A10)
  • 第一个参数 15 指示AGGREGATE执行MIN函数（即找到最小值）。
  • 第二个参数 6 指示忽略错误值以及引用中的空值。
  • A1:A10 是你想要从中找出最小值的数据范围。

我是觉得没必要学习复杂的函数，一个是函数太多，第二个就是适用范围太小（主要是自适应薪酬工作bushi）；
复杂的场景或者纷杂的数据直接上pandas清洗分析了，没必要难为Excel，Excel是有很多解决方法，但我认为专门学习性价比太低，知道有这个功能，函数忘了临时百度就行。

MINIFS和MAXIFS

MIN和MAX的IFS函数语法和之前SUMIFS的语法基本一致，即：

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=MAXIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

criteria_range和criteria的规则和之前介绍SUMIFS中的基本一致，篇幅限制我就不赘述了，这里的规则适用于MINIFS、MAXIFS以及AVERAGEIFS，详见：SUMIFS。

COUNT

COUNT是统计区域内数字个数的函数，但注意，日期类型也会被统计为数字，原理参照上述DAY函数详解。

统计非空单元格COUNTA函数，请注意，COUNTA函数会统计所有非空单元格，即使单元格“看起来是空的”或者有错误单元格。

条件统计COUNTIFS函数条件统计单元格，其中，条件区域的设置参照SUMIFS。

COUNT

AVERAGE

AVERAGE平均值函数，会自动忽略非数字单元格的影响，返回值和Excel右下角数据统计中的平均值一致；

AVERAGEIFS条件平均值函数，返回满足多个条件的所有单元格的平均值。

逻辑函数

终于快讲到IFS了，一会给大伙拉一坨大的，不急，先介绍OR和AND：

OR和AND

OR和AND是判断布尔值的，即判断TRUE和FALSE的，OR是只要有TRUE则返回TRUE，全为FALSE才返回FALSE；

AND是只要有FALSE就返回FALSE，只有全为TRUE才返回TRUE。

AND是“与”，OR是“或”，Excel还有一个逻辑函数是NOT“非”；

NOT传入TRUE返回FALSE,传入FALSE返回TRUE。

与或非都有了，网友们可以尝试在Excel中手搓CUP了。

OR和AND示例：

IF

IF语法：

=IF(logical_test,value_if_true,value_if_False)

logical_test为逻辑测试，当其中逻辑返回TRUE时，则if会返回value_if_true的内容；

当其中逻辑返回FALSE时，则if会返回value_if_False的内容。

你可以方便的用OR和AND与IF嵌套起来：

正如图中所示，IF中可以套IF，这就给了许多天赋选手无限的发挥空间，不过IFS淡然一笑…

IFS

我曾经常常因为自己不会高级函数、复杂的函数而感到格格不入，直到我遇见了IFS…

我直接零帧起手来坨大的：

毕竟话糙理不糙：

说实话，职场中看到别人写的这个，我两眼一黑…

怎么Excel也有屎山的，关键是我没办法继续在上面盖了，你这找谁说理去！

IFS的语法为：

=IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],[logical_test3,value_if_true3]...)

解释一下IFS的参数，logical_test1逻辑条件1，如果为真则执行value_if_true1，logical_test2逻辑条件2，如果为真则执行value_if_true2…

但是请注意，IFS的条件判断是依次的也就是说IFS会直接返回第一个为真的条件；

IFS只可以设置为真的条件，也就是说，你无法直接设置条件为假的情况（可以用NOT非函数，嵌套条件，你就可以设置为假的情况）；

IFS设置的所有条件如果都不为真，则会返回错误#N/A，但是你可以设置在最后一个条件为TRUE，这样当前面所有条件都不为真时，可以执行最后一个条件而不是直接返回错误。

IFERROR

IFERROR是捕获函数错误时的参数，当你不想函数直接返回错误时，可以嵌套IFERROR来屏蔽错误值，例如：

=IFERROR(XLOOKUP(D221,D218:D226,D218:D226),XLOOKUP(D221,J218:J226,J218:J226))

我们可以利用IFERROR来使XLOOKUP检索区域没有相应值时，换个区域检索，理论上，你可以一直嵌套IFERROR直到出现不报错的区域（XLOOKUP是Excel365加入的新函数，XLOOKUP本身就集成了错误收集参数，此处只为演示IFERROR）

虽然IFERROR看似万能，但是我们也应该尽量避免当我们不知道错误类型时盲目使用IFERROR来捕获错误，因为IFERROR会捕获所有的错误，即使是你的函数或者数据出了问题。

在Excel中，常见的错误类型有：

  • **#DIV/0!** – 这个错误出现时意味着公式尝试除以零或空单元格。例如，如果你有一个公式 `=A1/B1` 并且 B1 的值是 0 或者 B1 是一个空单元格，那么就会显示这个错误。
  • **#N/A** – 当 Excel 无法找到公式中引用的数据时会出现此错误。它通常出现在查找函数（如 VLOOKUP, HLOOKUP, MATCH）中，当找不到匹配项时返回 #N/A。
  • **#NAME?** – 如果 Excel 识别不出公式中的文本，则会返回此错误。这通常是由于拼写错误的函数名、未定义的名称或使用了不正确的语法导致的。
  • **#NULL!** – 当你试图用空格而不是逗号来分隔两个区域作为参数时，可能会出现此错误。这个错误在较新的 Excel 版本中已经很少见，因为 Excel 已经能够更好地处理这种情况。
  • **#NUM!** – 此错误表示公式中存在无效的数字。这可能是由于使用了不正确的参数进行数学运算（例如，对负数取平方根），或是迭代计算没有收敛。
  • **#REF!** – 当公式引用了一个无效的单元格位置时，Excel 会显示此错误。这通常发生在删除了公式所引用的行或列之后。
  • **#VALUE!** – 当公式中的操作数类型错误时会返回此错误。例如，当你尝试将文本和数字相加时，或者函数接收到的参数类型不对时。
  • **#SPILL!** – 在 Excel 365 和 Excel 2019 中引入的新错误，当动态数组公式尝试输出数据到已被占用的单元格时发生。

IFERROR会捕获上述所有的错误，所以一般我们会使用IFNA而不是使用IFERROR捕获错误。IFNA只会捕获#N/A错误。

这里的原理很像在python或其他编程语言中尽量避免使用裸的except捕获所有错误，因为直接捕获所有错误会掩盖真正的问题，例如：

=IFERROR(xloookup(D218,D213:D223,D213:D223),"没有返回值")
=IFNA(xloookup(D218,D213:D223,D213:D223),"没有返回值")

上述的两个公式，其中xloookup函数名多另一个字母o，IFERROR会直接捕获所有错误执行value_if_error，返回“没有返回值”；

而IFNA只会捕获#N/A，从而返回了错误的类型，#NAME?。提示函数名错误。

由于篇幅限制，本期先分享这么多，剩余的函数会在Excel常用函数（下）中分享，敬请期待！

文末声明：

您必须遵守关于，您可以随意转发/引用，但要注明原作者Leon或设置本文跳转连接，并且您必须在文中包含或提醒浏览者遵守作者声明。
欢迎关注公众号获取第二手文章！高效工作法

excelExcel函数excel基础函数excel常用函数
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
															
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

															
					
									

			

			
			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
VBA-原地取消公式

							
							

							
Excel汇总数据（获取数据）

							
							

							
Pandas实现Excel中浮点数float转时间datetime