你需要的Excel常用函数都在这里！

2021-06-24 11:23:09 浏览数 (1)

Excel常用函数包括逻辑函数、数学函数、文本函数、统计函数、日期函数，熟练并运用好函数，能够让复杂的问题简单化，可以做到批处理，加快处理各种统计、计算类工作。

下面就来一起学习吧。建议收藏！（避免 "一看就会、一用就忘"）

1、逻辑函数

IF()

代码语言：javascript复制
IF(logical_test,[value_if_true],[value_if_false])

如果第一个参数表达式判断的结果为真时，则返回第二个参数值；为假时，则返回第三个参数值。

Logical_test 逻辑表达式，如判断A2点值是否大于A1的值，本参数可以使用任何比较运算符。也可以使用函数的返回值，如用and函数 的返回值作为第一参数。 value_if_true 通过这个参数的英文说明，可见本参数是第一个参数逻辑表达式返回为真 (True) 时，就返回这个参数。此参数可以是任何文本、字符等。 value_if_false 通过这个参数的英文说明，可见本参数是第一个参数逻辑表达式返回为真 (False) 时，就返回这个参数。此参数可以是任何文本、字符等。

另外多条件判断可以使用 IFS 函数

代码语言：javascript复制
IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

IFS 函数检查是否满足一个或多个条件，且返回符合第一个 TRUE条件的值。IFS 可以取代多个嵌套 IF 语句，并且有多个条件时更方便阅读。

如： =IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F")

也即如果（A2 大于 89，则返回"A"，如果 A2 大于 79，则返回"B"并以此类推，对于所有小于 59 的值，返回"F"）。

例：计算水电气费用

计算水费、电费和气费,考虑不同阶梯价格差异，各类费用计算公式如下。

  • 气费
代码语言：javascript复制
=E3*$B$10
  • 电费
代码语言：javascript复制
=IF(D3<=240,D3*$B$11,IF(D3<=400,D3*$C$11,D3*$D$11))
  • 水费
代码语言：javascript复制
=IF(C3<=120,C3*$B$12,IF(C3<=176,C3*$C$12,C3*$D$12))

AND()

代码语言：javascript复制
AND(logical1, logical2, ...)

所有参数的逻辑值为真时，返回True；只要有一个参数的逻辑值为假，即返回False。

logical1, logical2, ..., logicalN 表示待检测的1到N个条件表达式。

OR()

代码语言：javascript复制
OR(logical1, logical2, ...)

在其参数组中，任何一个参数逻辑值为True，返回True；只要有一个参数的逻辑值为假，即返回False。

logical1, logical2, ..., logicalN 表示待检测的1到N个条件表达式。

例：计算考试是否通过

两种不同通过条件的通过公式如下。

  • 三门均通过，即通过
代码语言：javascript复制
=IF(AND(E2>=60,F2>=60,G2>=60),"通过","不通过")
  • 三门之一通过，即通过
代码语言：javascript复制
=IF(OR(E2>=60,F2>=60,G2>=60),"通过","不通过")

2、统计函数

COUNT()

代码语言：javascript复制
COUNT(value1, [value2], ...)

COUNT函数计算包含数字的单元格以及参数列表中的数字的个数。最多为255个。

有关函数的一些说明：

  • COUNTA 函数计算包含任何类型的信息（包括错误值和空文本 ("")）的单元格。例如，如果区域中包含的公式返回空字符串，COUNTA 函数计算该值。COUNTA 函数不会对空单元格进行计数。
  • 参数可以包含或引用各种类型的数据，但只有数字类型的数据才被计算在内。
  • 如果参数为数字、日期或者代表数字的文本（例如用引号引起的数字，"1"），则将被计算在内。
  • 如果参数为逻辑值、错误值或者不能转换为数字的文本，则不会被计算在内。

COUNTA()

代码语言：javascript复制
COUNTA(value1, [value2], ...)

COUNTA函数计算所选区域中非空单元格的个数。其参数最少1个，最多255个。

注意是非空单元格和空格的区别。

COUNTIF()

代码语言：javascript复制
COUNTIF(range, criteria)

单条件计数。记录所选区域中，满足特定条件的单元格的数值。

range 需要计算个数的区域，如A2:E5 criteria条件的形式为数字、表达式、单元格引用或文本，它定义了要计数的单元格范围。例如，条件可以表示为 32、">32"、B4、"apples"或 "32"。·

有关函数的说明：

  • 保证数据没有前导空格、尾部空格、直引号与弯引号不一致或非打印字符。否则COUNTIF函数 可能返回非预期的值。尝试使用CLEAN函数或者TRIM函数。

COUNTIFS()

代码语言：javascript复制
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

多条件计数。将条件应用于跨多个区域的单元格，然后统计满足所有条件的次数。

每个区域的条件一次应用于一个单元格。如果所有的第一个单元格都满足其关联条件，则计数增加 1。如果所有的第二个单元格都满足其关联条件，则计数再增加 1，依此类推，直到计算完所有单元格。

criteria_range1 必需。在其中计算关联条件的第一个区域。 criteria1 必需。参考COUNTIF 的 criteria criteria_range2, criteria2, ... 可选。附加的区域及其关联条件。 每一个附加的区域都必须与参数 criteria_range1 具有相同的行数和列数。这些区域无需彼此相邻。

如:

COUNTIFS(B2:B6,"=是",C2:C6, "=是")

有关该函数的一些说明:

  • 参数至少为两个，最多为127对。当为2个时，即为单条件计数。
  • 可以使用通配符，问号? 匹配任意单个字符，星号匹配任意字符串。如果要查找实际的问号或星号，请在字符前键入波形符~
  • 不区分大小写。
  • 如果条件参数是对空单元格的引用，COUNTIFS 会将该单元格的值视为0。
例：统计迟到和旷课次数。

计算迟到总次数，旷课总次数，以及前两日都迟到的总次数。

  • 迟到次数
代码语言：javascript复制
=COUNTIFS(B2:K2,"b")
  • 旷课次数
代码语言：javascript复制
=COUNTIF(B2:K2,"c")
  • 1日和2日都迟到的人数
代码语言：javascript复制
=COUNTIFS(B2:B11,"b",C2:C11,"b")

SUM()

代码语言：javascript复制
SUM(number1, [number2], ...)

SUM函数是对数值或者区域进行求和。区域中不能出现错误值。

number1 必须，该参数可以是数值，如1、1.5 等等；或一个区域，如 A1:A10，区域内也是数值。 [number2], ... 第2-255参数可选。

SUMIF()

代码语言：javascript复制
SUMIF(range, criteria, [sum_range])

对范围中符合指定条件的值求和。

range 必需。希望通过标准评估的单元格范围。 每个范围内的单元格必须是数字或名称、数组或包含数字的引用。空白和文本值将被忽略。选定的范围可以包含标准Excel格式的日期。 criteria 必需。参考COUNTIF 的 criteria

该函数的一些说明:

  • 任何文本条件或者含有逻辑或数学符号的条件都必须使用双引号""。如果条件为数字，则无需使用双引号。
  • sum_range 的大小和形状应该与range相同。

SUMIFS()

代码语言：javascript复制
SUMIF(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2])

用于计算其满足多个条件的全部参数的总量。

sum_range 要求和的单元格区域。 criteria_range1 使用criteria1 测试的区域 criteria_range1和 criteria1设置用于搜索某个区域是否符合特定条件的搜索对。一旦在该区域中找到了项，将计算 sum_range 中的相应值的和。 criteria1 定义将计算 criteria_range1中的哪些单元格的和的条件。其表示方式与SUMIF一样。 至少为三个参数，最多可以输入 127 个区域/条件对。当为三个参数时，就和单条件求和一样，后面参数都是成对出现：条件区域2，条件2，条件区域3，条件3...

如果需要，可将条件应用于一个区域并对其他区域中的对应值求和。 如公式 =SUMIF(B2:B5, "John", C2:C5) 只对区域 C2:C5 中在区域 B2:B5 中所对应的单元格等于"John"的值求和。

例：统计借贷金额

根据要求按条件求借贷金额总和。

  • 借款总额
代码语言：javascript复制
=SUMIF(D3:D14,"借",H3:H14)
  • 贷款总额
代码语言：javascript复制
=SUMIF(D3:D14,"贷",H3:H14)
  • 工行借款总额
代码语言：javascript复制
=SUMIFS(H3:H14,D3:D14,"借",E3:E14,"工行")

3、日期函数

有众多日期函数，下面介绍几个常用日期函数及其使用案例。

EOMONTH()

代码语言：javascript复制
EOMONTH(start_date, months)

返回某个月份最后一天的序列号，该月份与 start_date 相隔（之后或之后）指示的月份数。 使用函数 EOMONTH 可以计算正好在特定月份中最后一天到期的到期日。

start_date 必需。开始日期。 应使用DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 months 必需。start_date 之前或之后的月份数。 months 为正值将生成未来日期；为负值将生成过去日期。 如果 months 不是整数，将截尾取整。

如：

=EOMONTH(2011-1-1,1) 此函数表示在 A2 中日期之后一个月的最后一天的日期。 结果：2011-2-28。

例：计算某月最后一天
  • 该月最后一天
代码语言：javascript复制
=EOMONTH(A2,0)
  • 该月有多个天
代码语言：javascript复制
=DAY(A2)

WEEKDAY()

代码语言：javascript复制
WEEKDAY(serial_number,[return_type])

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

serial_number 必需。一个序列号，代表尝试查找的那一天的日期。 应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 如使用函数 DATE(2008,5,23)输入 2008 年 5 月 23 日。 return_type 可选。用于确定返回值类型的数字。

return_type

返回的数字

1 或省略

数字 1（星期日）到 7（星期六）

2

数字 1（星期一）到 7（星期日）

3

数字 0（星期一）到 6（星期日）

11

数字 1（星期一）到 7（星期日）

12

数字 1（星期二）到数字 7（星期一）

13

数字 1（星期三）到数字 7（星期二）

14

数字 1（星期四）到数字 7（星期三）

15

数字 1（星期五）到数字 7（星期四）

16

数字 1（星期六）到数字 7（星期五）

17

数字 1（星期日）到 7（星期六）

WORKDAY()

代码语言：javascript复制
WORKDAY(start_date, days, [holidays])

返回在起始日期之前或之后、与该日期相隔指定工作日的某一日期的日期值。 工作日不包括周末和专门指定的假日。在计算发票到期日、预期交货时间或工作天数时，可以使用函数 WORKDAY 来扣除周末或假日。

start_date 必需。开始日期。 days 必需。start_date之前或之后不含周末及节假日的天数。 days 为正值将生成未来日期；为负值生成过去日期。 holidays 可选。一个可选列表，其中包含需要从工作日历中排除的一个或多个日期。 例如各种省/市/自治区和国家/地区的法定假日及非法定假日。 该列表可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。

NETWORKDAYS()

代码语言：javascript复制
NETWORKDAYS(start_date, end_date, [holidays])

返回参数 start_date 和 end_date 之间完整的工作日数值。 可以使用函数 NETWORKDAYS，根据某一特定时期内雇员的工作天数，计算其应计的报酬。

start_date 必需。开始日期。 end_date 必需。终止日期。 holidays 可选。参考 WORKDAY

EDATE()

代码语言：javascript复制
EDATE(start_date, months)

返回表示某个日期的序列号，该日期与指定日期 (start_date) 相隔（之前或之后）指示的月份数。 使用函数 EDATE 可以计算与发行日处于一月中同一天的到期日的日期。

start_date 必需。开始日期。 months 必需。start_date之前或之后的月份数。 months为正值将生成未来日期；为负值将生成过去日期

例：计算日期
  • 判断是否是周末
代码语言：javascript复制
=IF(WEEKDAY(A2,2)>5,"周末","否")
  • 第n个工作日的日期
代码语言：javascript复制
=WORKDAY(D2,E2,D5:D11)
  • 有多少个工作日
代码语言：javascript复制
=NETWORKDAYS(H2,I2,D5:D11)
  • 转正日期
代码语言：javascript复制
=EDATE(H7,I7)

4、文本函数

REPLACE()

代码语言：javascript复制
REPLACE(old_text, start_num, num_chars, new_text)

使用其他文本字符串并根据所指定的字节数替换某文本字符串中的部分文本。

old_text 必需。要替换其部分字符的文本。 start_num 必需。old_text 中要替换为 new_text 的字符位置。 num_chars 必需。old_text 中希望替换使用 new_text 来进行替换的字符数。 num_bytes 必需。old_text 中希望替换使用 new_text 来进行替换的字节数。 new_text 必需。将替换 old_text 中字符的文本。

REPT()

代码语言：javascript复制
REPT(text, number_times)

将文本重复一定次数。

text 必需。需要重复显示的文本。 number_times 必需。用于指定文本重复次数的正数。

该函数的一些说明：

  • 如果 number_times 为 0，则 REPT 返回 ""（空文本）。
  • 如果 number_times 不是整数，将被截尾取整。
  • REPT 函数结果的长度不能超过 32,767 个字符。
例：隐藏手机号码

把原始文本中的指定字符数的文本字符串替换为新的字符串，比如把18996471864 中间四位替换为****。即 189***1864 。

代码语言：javascript复制
=REPLACE(A2,8,3,REPT("*",3))

‍‍

附录

常用Excel函数

数学函数

INT()

取整

MOD()

求余数

ROUND()

四舍五入

ABS()

取绝对值

SQRT()

算术平方根

RAND()

产生随机数

RANDBETWEEN()

产生随机数

文本函数

MID()

取子串

LEFT()

从左取子串

RIGHT()

从右取子串

LEN()

文本长度

TEXT()

数字转化文本格式

REPT

文本重复

REPLACE

替换特定位置处的文本

SUBSTITUTE

替换文本

日期函数

YEAR()

求年

MONTH()

求月

DAY()

求日

TODAY()

当前日期

DATE()

计算给定的日期

NOW()

当前日期和时间

EDATE()

指定日期前后月份的日期

EOMONTH

某个月份最后一天的序列号

DATEDIF()

计算日期差

统计函数

MAX()

求最大

MIN()

求最大

SUM()

求和

COUNT()

数值计数

COUNTA()

计数

AVERAGE()

求平均

COUNTIF()

条件计数

SUMIF()

条件求和

AVERAGEIF()

条件平均

COUNTIFS()

多条件计数

SUMIFS()

多条件求和

AVERAGEIFS()

多条件平均

FREQUENCY()

求数据分布频率

RANK()

排名次

逻辑函数

IF()

判断

AND()

与

OR()

或

NOT()

非

查找与引用函数

VLOOKUP()

垂直方向查找

OFFSET()

计算偏移量

MATCH()

匹配

INDEX()

索引

INDIRECT()

文本字符串指定的引用

ROW()

引用行的数据

COLUMN()

引用列的数据

HLOOKUP()

水平方向查找

推荐阅读

serverless 编程算法

0 人点赞