你应该知道的 101 个 Excel 函数

以下是您应该了解的大约 100 个重要 Excel 函数的简要概述

日期和时间函数

Excel 提供了许多处理日期和时间的函数。

现在和今天

您可以使用TODAY 函数获取当前日期，使用NOW 函数获取当前日期和时间。从技术上讲，NOW 函数返回当前日期和时间，但您可以仅格式化为时间，如下所示：

TODAY ()  // 返回当前日期
NOW ()  // 返回当前时间

注意：这些是易变函数，每次工作表更改都会重新计算。如果您想要一个静态值，请使用日期和时间快捷方式。

日、月、年和日期

您可以使用DAY、 MONTH和YEAR函数将任何日期分解为其原始组件，并使用DATE 函数将它们重新组合在一起。

= DAY ( "14-Nov-2018" )  // 返回 14 
= MONTH ( "14-Nov-2018" )  // 返回 11 
= YEAR ( "14-Nov-2018" )  // 返回 2018 
= DATE ( 2018 , 11 , 14 )  // 返回 2018 年 11 月 14 日

小时、分钟、秒和时间

Excel 为时间提供了一组并行函数。您可以使用HOUR、MINUTE和SECOND函数来提取时间片段，并且可以使用TIME 函数从各个组件中组装一个 TIME 。

= HOUR ( "10:30" )  // 返回 10 
= MINUTE ( "10:30" )  // 返回 30 
= SECOND ( "10:30" )  // 返回 0 
= TIME ( 10 , 30 , 0 )  //返回 10:30

DATEDIF 和 YEARFRAC

您可以使用DATEDIF 函数以年、月或日为单位获取日期之间的时间。还可以配置 DATEDIF 以获取“标准化”面额的总时间，即“2 年零 6 个月零 27 天”。

使用YEARFRAC获得小数年：

= YEARFRAC ( "14-Nov-2018" , "10-Jun-2021" )  // 返回 2.57

220 多个excel快捷键速查表  适用Windows和Mac  免费下载>>>

	
		下载 “excel快捷键速查表”		Excel快捷键速查表.zip – 已下载54次 – 3 MB
	

EDATE 和 EOMONTH

日期的一项常见任务是将日期向前（或向后）移动给定的月数。您可以为此使用EDATE和EOMONTH 函数。EDATE 按月移动并保留日期。EOMONTH 的工作方式相同，但总是返回该月的最后一天。

EDATE ( date , 6 )  // 提前 6 个月
EOMONTH ( date , 6 )  // 提前 6 个月（月末）

工作日和网络日

要找出未来n 个工作日的日期，可以使用WORKDAY 函数。要计算两个日期之间的工作日数，您可以使用NETWORKDAYS。

WORKDAY（开始， ñ，节假日） //日期ñ工作日未来

视频：如何使用 WORKDAY 计算截止日期

NETWORKDAYS ( start , end , holiday )  // 日期之间的工作日数

注意：这两个功能都会自动跳过周末（周六和周日），如果提供的话，也会跳过节假日。如果您需要更灵活地将哪些日子视为周末，请参阅 WORKDAY.INTL 函数 和NETWORKDAYS.INTL 函数。

WEEKDAY 和 WEEKNUM

为了从日期中找出星期几，Excel 提供了WEEKDAY 函数。WEEKDAY 返回一个介于 1-7 之间的数字，表示星期日、星期一、星期二等。使用 WEEKNUM 函数 获取给定年份的周数。

= WEEKDAY ( date )  // 返回一个数字 1-7 
= WEEKNUM ( date )  // 返回一年中的周数

工程

兑换

大多数工程函数都是非常技术性的……您会在本节中找到很多复数函数。但是，CONVERT 函数对于日常单位转换非常有用。您可以使用 CONVERT 更改距离、重量、温度等的单位。

= CONVERT ( 72 , "F" , "C" )  // 返回 22.2

信息功能

ISBLANK、ISERROR、ISNUMBER 和 ISFORMULA

Excel 提供了许多用于检查单元格中的值的函数，包括 ISNUMBER、   ISTEXT、ISLOGICAL、  ISBLANK、ISERROR和ISFORMULA  这些函数有时称为“IS”函数，它们都根据单元格的内容返回 TRUE 或 FALSE。

Excel 还具有 ISODD和ISEVEN 函数，它们将测试一个数字以查看它是偶数还是奇数。

顺便说一句，上面屏幕截图中的绿色填充是使用条件格式 公式自动应用的 。

逻辑函数

Excel 的逻辑函数是许多高级公式的关键组成部分。逻辑函数返回布尔值 TRUE 或 FALSE。如果您需要逻辑公式的入门知识，他的视频会包含许多示例。

AND、OR 和 NOT

Excel 逻辑函数的核心是AND 函数、OR 函数和NOT 函数。在下面的屏幕中，这些函数中的每一个都用于对 B 列中的值运行一个简单的测试：

= AND ( B5 > 3 , B5 < 9 ) 
= OR ( B5 = 3 , B5 = 9 ) 
= NOT ( B5 = 2 )

• 视频：如何构建逻辑公式
• 指南：50 个公式标准示例

IFERROR 和 IFNA

该IFERROR函数和IFNA功能可以作为一个简单的方法来捕获和处理错误。在下面的屏幕中，VLOOKUP用于从菜单项中检索成本。F 列只包含一个VLOOKUP 函数，没有错误处理。G 列显示了如何使用带有 VLOOKUP 的 IFNA 在输入无法识别的项目时显示自定义消息。

= VLOOKUP ( E5 , menu , 2 , 0 )  // 没有错误捕获
= IFNA ( VLOOKUP ( E5 , menu , 2 , 0 ), "Not found" )  // 捕获错误

IFNA 仅捕获 #N/A 错误，而IFERROR 函数将捕获任何公式错误。

IF 和 IFS 函数

该IF函数是Excel中最常用的功能之一。在下面的屏幕中，IF 检查考试成绩并分配“通过”或“失败”：

多个 IF 函数 可以嵌套在一起以执行更复杂的逻辑测试。

Excel 2019 和Excel 365 中的新增功能，IFS 函数 可以运行多个逻辑测试而无需 嵌套 IF。

= IFS（ C5 < 60 ，“F” ， C5 < 70 ，“D” ， C5 < 80 ，“C” ， C5 < 90 ，“B” ， C5 >= 90 ，“A” ）

查找和引用函数

VLOOKUP 和 HLOOKUP

Excel 提供了许多功能来查找和检索数据。最著名的是VLOOKUP：

= VLOOKUP ( C5 , $F$5:$G$7 , 2 , TRUE )

更多：关于 VLOOKUP 的 23 件事。

HLOOKUP 的工作方式与VLOOKUP类似，但需要水平排列数据：

= HLOOKUP ( C5 , $G$4:$I$5 , 2 , TRUE )

索引和匹配

对于更复杂的查找，  INDEX和MATCH提供了更大的灵活性和功能：

= INDEX（ C5：E12 ，MATCH（ H4 ， B5：B12 ，0 ），MATCH（ H5 ， C4：E4 ，0 ））

I NDEX 函数和MATCH 函数都是出现在各种公式中的强大函数。

更多：如何使用 INDEX 和 MATCH

抬头

该LOOKUP函数 具有默认行为解决某些问题时变得有用。LOOKUP 假定值按升序排序并始终执行近似匹配。当 LOOKUP 找不到匹配项时，它将匹配下一个最小值。在下面的示例中，我们使用 LOOKUP 查找列中的最后一个条目：

行和列

您可以使用ROW 函数和COLUMN 函数在工作表上查找行号和列号。如果没有提供参考，请注意当前单元格的ROW 和 COLUMN 返回值：

行函数还经常出现在处理具有相对行号的数据的高级公式中。

行和列

的ROWS功能和COLUMNS功能在参考提供的行的计数。在下面的屏幕中，我们正在计算名为“Table1”的Excel 表格中的行和列。

注意 ROWS 返回表中数据行的计数，不包括标题行。顺便说一下，这里有 关于 Excel 表格的 23 件事。

超链接

您可以使用HYPERLINK 函数来构造带有公式的链接。注意 HYPERLINK 允许您构建外部链接和内部链接：

= HYPERLINK（ C5 ， B5 ）

获取数据

的GETPIVOTDATA函数是用于从现有数据透视表中检索信息是有用的。

= GETPIVOTDATA（“销售” ， $B$4 ，“地区” ， I6 ，“产品” ， I7 ）

选择

每当您需要根据数字做出选择时，CHOOSE 功能都很方便：

= CHOOSE ( 2 , "red" , "blue" , "green" )  // 返回 "blue"

视频：如何使用 CHOOSE 功能

转置

该移调功能给你一个简单的方法来反之亦然转垂直数据水平，与邪恶。

{ = TRANSPOSE（ B4：C9 ）}

注意：TRANSPOSE 是一个公式，因此是动态的。如果您只需要进行一次性转置操作，请改用选择性粘贴 。

抵消

该OFFSET函数是各种动态范围的有用。从起始位置，它允许您指定行和列偏移量，以及最终行和列大小。其结果是一个范围，可以对变化的条件和输入动态响应。您可以将此范围提供给其他函数，如下面的屏幕所示，其中 OFFSET 构建了一个提供给SUM 函数的范围：

= SUM ( OFFSET ( B4 , 1 , I4 , 4 , 1 ))  // Q3 的总和

间接

在INDIRECT函数 允许你建立为文本引用。这个概念起初有点难以理解，但它在许多情况下都很有用。下面，我们使用 INDIRECT 从 5 个不同工作表中的单元格 A1 中获取值。每个引用都是动态的。如果工作表名称更改，则引用将更新。

=间接( B5 & "!A1" )  // =Sheet1!A1

INDIRECT 函数还用于“锁定”引用，以便在添加或删除行或列时它们不会更改。有关更多详细信息，请参阅INDIRECT 函数页面底部的链接示例。

注意：OFFSET 和 INDIRECT 都是易变函数，可以减慢大型或复杂电子表格的速度。

统计功能

计数和计数

您可以使用COUNT 函数计算数字，使用COUNTA 计算非空单元格。您可以使用COUNTBLANK计算空白单元格，但在下面的屏幕中，我们使用COUNTIF计算空白单元格，这更普遍有用。

= COUNT ( B5:F5 )  // 计算数字
= COUNTA ( B5:F5 )  // 计算数字和文本
= COUNTIF ( B5:F5 , "" )  // 计算空格

COUNTIF 和 COUNTIFS

对于条件计数，COUNTIF 函数可以应用一个条件。该 COUNTIFS功能可以同时应用多个标准：

= COUNTIF ( C5:C12 , "red" )  // 计数 red 
= COUNTIF ( F5:F12 , ">50" )  // 总计数 > 50 
= COUNTIFS ( C5:C12 , "red" , D5:D12 , "TX " )  // red and tx 
= COUNTIFS ( C5:C12 , "blue" , F5:F12 , ">50" )  // blue > 50

视频：如何使用 COUNTIF 函数

总和、总和、总和

总结一切，使用SUM 函数。要有条件地求和，请使用 SUMIF 或 SUMIFS。遵循与计数函数相同的模式，SUMIF 函数只能应用一个条件，而SUMIFS 函数可以应用多个条件。

= SUM ( F5:F12 )  // 一切
= SUMIF ( C5:C12 , "red" , F5:F12 )  // 仅红色
= SUMIF ( F5:F12 , ">50" )  // 超过 50 
= SUMIFS ( F5: F12 , C5:C12 , "red" , D5:D12 , "tx" )  // red & tx 
= SUMIFS ( F5:F12 , C5:C12 , "blue" , F5:F12 , ">50" ) // 蓝色 & >50

视频：如何使用 SUMIF 函数

AVERAGE、AVERAGEIF 和 AVERAGEIFS

按照相同的模式，您可以使用AVERAGE、AVERAGEIF和AVERAGEIFS计算平均值。

= AVERAGE ( F5:F12 )  // all 
= AVERAGEIF ( C5:C12 , "red" , F5:F12 )  // 仅红色
= AVERAGEIFS ( F5:F12 , C5:C12 , "red" , D5:D12 , "tx " )  // 红色和 tx

最小、最大、大、小

您可以使用MAX和MIN找到最大值和最小值，使用LARGE和SMALL可以找到第 n 个最大值和最小值。在下面的屏幕中，“数据”是命名范围C5:C13，用于所有公式。

= MAX ( data )  // 最大
= MIN ( data )  // 最小
= LARGE ( data , 1 )  // 第一大
= LARGE ( data , 2 )  // 第二大
= LARGE ( data , 3 )  // 第三大
= SMALL ( data , 1 )  // 第一个最小的
= SMALL ( data , 2 )  // 第二个最小的
= SMALL ( data , 3 )  // 第三小的

视频：如何找到第 n 个最小值或最大值

MINIFS、MAXIFS

该MINIFS和MAXIFS。这些函数可让您在以下条件下找到最小值和最大值：

= MAXIFS ( D5:D15 , C5:C15 , "female" )  // 最高女性
= MAXIFS ( D5:D15 , C5:C15 , "male" )  // 最高男性
= MINIFS ( D5:D15 , C5:C15 , " female" )  // 最低的女性
= MINIFS ( D5:D15 , C5:C15 , "male" )  // 最低的男性

注意：MINIFS 和 MAXIFS 是通过 Office 365 和 Excel 2019 在 Excel 中新增的。

模式

在MODE函数返回一个范围最常出现的数目：

= MODE ( B5:G5 )  // 返回 1

秩

要将值从最大到最小或从最小到最大排序，请使用RANK 函数：

视频：如何使用 RANK 函数对值进行排名

数学函数

ABS

要将负值更改为正值，请使用ABS 功能。

= ABS ( - 134.50 )  // 返回 134.50

兰德和兰德之间

无论是RAND功能和RANDBETWEEN功能可以动态生成随机数。RAND 创建 0 到 1 之间的长十进制数。RANDBETWEEN 生成两个给定数字之间的随机整数。

= RAND ()  // 介于 0 和 1 之间
= RANDBETWEEN ( 1 , 100 )  // 介于 1 和 100 之间

舍入，舍入，舍入，整数

要向上或向下舍入值，请使用ROUND 函数。要强制四舍五入到给定位数，请使用ROUNDUP。要强制向下舍入，请使用ROUNDDOWN。要完全丢弃数字的小数部分，请使用INT 函数。

= ROUND ( 11.777 , 1 )  // 返回 11.8 
= ROUNDUP ( 11.777 )  // 返回 11.8 
= ROUNDDOWN ( 11.777 , 1 )  // 返回 11.7 
= INT ( 11.777 )  // 返回 11

地面、天花板、地板

要将值四舍五入到最接近的 倍数，请使用MROUND 函数。该FLOOR功能和吊顶功能也圆给定的倍数。FLOOR 力向下舍入，CEILING 力向上舍入。

= MROUND ( 13.85 , .25 )  // 返回 13.75 
= CEILING ( 13.85 , .25 )  // 返回 14 
= FLOOR ( 13.85 , .25 )  // 返回 13.75

模组

该MOD函数返回余数。这听起来很无聊和令人讨厌，但是 MOD 出现在各种公式中，尤其是需要“每 n 次”做某事的公式。在下面的屏幕中，您可以看到当除数为 3 时，MOD 如何每隔三个数字返回零：

总产品

该SUMPRODUCT函数有各类数据的时候是一个强大和灵活的工具。您可以使用 SUMPRODUCT 轻松地根据标准进行计数和求和，并且可以以优雅的方式使用它，而 COUNTIFS 和 SUMIFS 则无法使用。在下面的屏幕中，我们使用 SUMPRODUCT 对 3 月份的订单进行计数和汇总。有关详细信息和指向许多示例的链接，请参阅SUMPRODUCT 页面。

= SUMPRODUCT ( -- ( MONTH ( B5:B12 ) = 3 ))  // 对 March 进行计数
= SUMPRODUCT ( -- ( MONTH ( B5:B12 ) = 3 ), C5:C12 )  // 对 March 求和

小计

该SUBTOTAL函数是一个“集合函数”，它可以在一组数据执行多个操作的。总而言之，SUBTOTAL 可以执行 11 种操作，包括 SUM、AVERAGE、COUNT、MAX、MIN 等（完整列表请参见此页面）。SUBTOTAL 的关键特性是它会忽略已从Excel 表中“过滤掉”的行，以及手动隐藏的行（可选）。在下面的屏幕中，SUBTOTAL 仅用于对表中的 7 个可见行进行计数和求和：

= SUBTOTAL ( 3 , B5:B14 )  // 返回 7 
= SUBTOTAL ( 9 , F5:F14 )  // 返回 9.54

总计的

与 SUBTOTAL 一样，  AGGREGATE 函数 也可以对一组数据运行许多聚合操作，并且可以选择忽略隐藏行。主要区别在于 AGGREGATE 可以运行更多操作（总共 19 个）并且还可以忽略错误。

在下面的屏幕中，AGGREGATE 用于执行 MIN、MAX、LARGE 和 SMALL 操作，同时忽略错误。通常，单元格 B9 中的错误会阻止这些函数返回结果。 有关AGGREGATE 可以执行的操作的完整列表，请参阅此页面。

= AGGREGATE ( 4 , 6 , values )  // MAX 忽略错误，返回 100 
= AGGREGATE ( 5 , 6 , values )  // MIN 忽略错误，返回 75

文本函数

左、右、中

要从文本的左侧、右侧或中间提取字符，请使用LEFT、RIGHT和MID 函数：

= LEFT ( "ABC-1234-RED" , 3 )  // 返回 "ABC"
 = MID ( "ABC-1234-RED" , 5 , 4 )  // 返回 "1234" 
= RIGHT ( "ABC-1234-RED" , 3 )  // 返回“红色”

伦

该LEN函数会返回一个字符串的长度。LEN 出现在许多计算单词或字符的公式中。

查找、搜索

要在单元格中查找特定文本，请使用FIND 函数 或SEARCH 函数。这些函数返回匹配文本的数字位置，但 SEARCH 允许使用通配符并且 FIND 区分大小写。当未找到文本时，这两个函数都会抛出错误，因此请在ISNUMBER函数中换行以返回 TRUE 或 FALSE（此处为示例）。

= FIND ( "Better the devil you know" , "devil" )  // 返回 12 
= SEARCH ( "这不是我美丽的妻子" , "bea*" )  // 返回 12

替换，替换

要按位置替换文本，请使用REPLACE 函数。要通过匹配替换文本，请使用SUBSTITUTE 函数。在第一个示例中，REPLACE 通过将前两个字符替换为空字符串 (“”) 来删除两个星号 (**)。在第二个示例中，SUBSTITUTE 通过将“#”替换为“”来删除所有井号字符 (#)。

= REPLACE（“**红” ，1 ，2 ， "" )  // 返回 "Red" 
= SUBSTITUTE ( "##Red##" , "#" , "" )  // 返回 "Red"

代码，字符

要找出字符的数字代码，请使用CODE 函数。要将数字代码转换回字符，请使用CHAR 函数。在下面的示例中，CODE 将 B 列中的每个字符转换为其对应的代码。在 F 列中，CHAR 将代码转换回字符。

= CODE ( "a" )  // 返回 97 
= CHAR ( 97 )  // 返回 "a"

视频：如何使用 CODE 和 CHAR 函数

修剪，干净

要消除文本中的多余空间，请使用TRIM 功能。要删除换行符和其他非打印字符，请使用CLEAN。

=修剪 ( A1 )  // 删除多余的空格
= CLEAN ( A1 )  // 删除换行符

视频：如何使用 TRIM 和 CLEAN 清理文本

连接、文本连接、连接

通过 Office 365 在 Excel 中新增的是 CONCAT 和 TEXTJOIN。该 CONCAT功能可让您连接（连接）多个值，包括无定界符值的范围。该TEXTJOIN功能做同样的事情，但允许你指定一个分隔符，也可以忽略空值。

= TEXTJOIN ( "," , TRUE , B4:H4 )  // 返回 "red,blue,green,pink,black" 
= CONCAT ( B7:H7 )  // 返回 "8675309"

Excel 还提供了CONCATENATE 函数，但它不提供特殊功能。我不会打扰它，而是直接与公式中的 & 字符连接。

精确的

的确切功能允许您在一个区分大小写的方式比较两个文本字符串。

上、下、正

要更改文本的大小写，请使用UPPER、LOWER和PROPER 函数

= UPPER ( "Sue BROWN" )  // 返回 "SUE BROWN" 
= LOWER ( "Sue BROWN" )  // 返回 "sue brown" 
= PROPER ( "Sue BROWN" )  // 返回 "Sue Brown"

视频：如何使用公式更改大小写

文本

最后但绝对不是最不重要的是 TEXT 功能。文本功能让您可以将数字格式作为文本应用到数字（包括日期、时间等）。当您需要在消息中嵌入格式化的数字时，这尤其有用，例如“销售结束于 [日期]”。

= TEXT ( B5 , "$#,##0.00" )  
= TEXT ( B6 , "000000" ) 
= "保存 " & TEXT ( B7 , "0%" ) 
= "销售结束 " & TEXT ( B8 , "mmm d " )

更多：自定义数字格式的详细示例。

动态数组函数

动态数组 是Excel 365中的新增功能 ，是对 Excel 公式引擎的 重大升级。作为动态数组更新的一部分，Excel 包括直接利用动态数组解决传统公式难以解决的问题的新函数。如果您使用的是 Excel 365，请确保您了解这些新功能：

220 多个excel快捷键速查表  适用Windows和Mac  免费下载>>>

下载 “excel快捷键速查表” Excel快捷键速查表.zip – 已下载54次 – 3 MB