excel从入门到精通（函数）

steven_lv
682 阅读9分钟

excelcon个入门到精通（函数）

注：部分资料来自excel官方文档

1.SUM

1.选中填入求和值的单元格
2.输入"=SUM("
3.框选要求和的数据

image.png
类似用法函数有：MAX,MIN,AVERAGE,

2.LARGE,SMALL函数

  1. 选中待填入结果的单元格， 2.输入“=LARGE 3.框选待处理的数据， 4.输入","再输入第二个参数（例如2，获取第二高值）

image.png

3. IF

函数说明：例如，=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。
1.选中待填入结果的单元格
2.输入"=",
3.点击函数选择按钮
4.选择IF函数

image.png 5.在公式生成器第一栏中输入条件，第二栏输入条件为真填入的值，第三栏输入条件为假填入的值

image.png

4.IFS

1.函数说明：

语法：=IFS(Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

2.举例：

image.png 单元格 A2:A6 的公式如下为：

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

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

5.VLOOKUP

1.函数说明：

=VLOOKUP (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE) 。

2.举例

1.精准匹配：

image.png
2.模糊匹配：
VLOOKUP函数最后一个参数为TRUE,开启模糊匹配，第二个参数开启绝对引用，方便将该函数复用在其他单元格 image.png

6.IFERROR

可以使用 IFERROR 函数捕获和处理公式中的错误。 IFERROR 返回公式计算结果为错误时指定的值;否则，它将返回公式的结果。

语法

IFERROR(value, value_if_error)

IFERROR 函数语法具有下列参数：

  • value    必需。 检查是否存在错误的参数。
  • value_if_error    必需。 公式计算结果为错误时要返回的值。 评估以下错误类型：#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？或 #NULL！。

示例

复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果，请选中它们，按 F2，然后按 Enter。

配额销售数量
21035
550
23
公式说明结果
=IFERROR(A2/B2,"计算中有错误")检查第一个参数中公式的错误（210 除以 35），未找到错误，返回公式结果6
=IFERROR(A3/B3,"计算中有错误")检查第一个参数中公式的错误（55 除以 0），找到被 0 除错误，返回 value_if_error计算中有错误
=IFERROR(A4/B4,"计算中有错误")检查第一个参数中公式的错误（"" 除以 23），未找到错误，返回公式结果。0

7.COUNT

用途

COUNT 函数计算包含数字的单元格个数以及参数列表中数字的个数。 使用 COUNT 函数获取区域中或一组数字中的数字字段中条目的个数。 例如，可以输入以下公式计算区域 A1:A6 中数字的个数：=COUNT(A1:A6) 。 在这个示例中，如果此区域中有 5 个单元格包含数字，则答案就为 5

image.png

语法

COUNT(value1, [value2], ...)

COUNT 函数语法具有下列参数：

  • value1    必需。 要计算其中数字的个数的第一项、单元格引用或区域。
  • value2, ...    可选。 要计算其中数字的个数的其他项、单元格引用或区域，最多可包含 255 个。

注意: 这些参数可以包含或引用各种类型的数据，但只有数字类型的数据才被计算在内。

示例

复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果，请选中它们，按 F2，然后按 Enter。 如果需要，可调整列宽以查看所有数据。

数据
2008/12/8
19
22.24
TRUE
#DIV/0!
公式说明结果
=COUNT(A2:A7)计算单元格区域 A2 到 A7 中包含数字的单元格的个数。3
=COUNT(A5:A7)计算单元格区域 A5 到 A7 中包含数字的单元格的个数。2
=COUNT(A2:A7,2)计算单元格区域 A2 到 A7 中包含数字和值 2 的单元格的个数4

8.COUNTIF

用途

COUNTIF 是一个统计函数，用于统计满足某个条件的单元格的数量；例如，统计特定城市在客户列表中出现的次数。

COUNTIF 的最简形式为：

  • =COUNTIF(要检查哪些区域? 要查找哪些内容?)

例如：

  • =COUNTIF(C2:C14,F2)

image.png

语法

COUNTIF(range, criteria)

参数名称说明
range   （必需）要进行计数的单元格组。 区域可以包括数字、数组、命名区域或包含数字的引用。 空白和文本值将被忽略。
criteria   （必需）用于决定要统计哪些单元格的数量的数字、表达式、单元格引用或文本字符串。例如，可以使用 32 之类数字，“>32”之类比较，B4 之类单元格，或“苹果”之类单词。COUNTIF 仅使用一个条件。 如果要使用多个条件，请使用 COUNTIFS。

示例

要在 Excel 中使用这些示例，请复制下表中的数据，然后将其粘贴进新工作表的 A1 单元格中。

数据数据
苹果32
橙子54
桃子75
苹果86
公式说明
=COUNTIF(A2:A5,"苹果")统计单元格 A2 到 A5 中包含“苹果”的单元格的数量。 结果为“2”。
=COUNTIF(A2:A5,A4)统计单元格 A2 到 A5 中包含“桃子”（A4 中的值）的单元格的数量。 结果为 1。
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)计算单元格 A2 到 A5 中苹果（A2 中的值）和橙子（A3 中的值）的数量。 结果为 3。 此公式两次使用 COUNTIF 表达式来指定多个条件，每个表达式一个条件。 也可以使用 COUNTIFS 函数。
=COUNTIF(B2:B5,">55")统计单元格 B2 到 B5 中值大于 55 的单元格的数量。 结果为“2”。
=COUNTIF(B2:B5,"<>"&B4)统计单元格 B2 到 B5 中值不等于 75 的单元格的数量。 与号 (&) 合并比较运算符不等于 (<>) 和 B4 中的值，因此为 =COUNTIF(B2:B5,"<>75")。 结果为“3”。
=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")统计单元格 B2 到 B5 中值大于 (>) 或等于 (=) 32 且小于 (<) 或等于 (=) 85 的单元格的数量。 结果为“3”。
=COUNTIF(A2:A5,"*")统计单元格 A2 到 A5 中包含任何文本的单元格的数量。 通配符星号 (*) 用于匹配任意字符。 结果为“4”。
=COUNTIF(A2:A5,"?????es")统计单元格 A2 到 A5 中正好为 7 个字符且以字母“es”结尾的单元格的数量。 通配符问号 (?) 用于匹配单个字符。 结果为“2”。

8.COUNTIFS

用途

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

语法

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

COUNTIFS 函数语法具有以下参数：

  • criteria_range1    必需。 在其中计算关联条件的第一个区域。
  • criteria1    必需。 条件的形式为数字、表达式、单元格引用或文本，它定义了要计数的单元格范围。 例如，条件可以表示为 32、">32"、B4、"apples"或 "32"。
  • criteria_range2, criteria2, ...    可选。 附加的区域及其关联条件。 最多允许 127 个区域/条件对。

重要: 每一个附加的区域都必须与参数 criteria_range1 具有相同的行数和列数。 这些区域无需彼此相邻。

示例 1

复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果，请选中它们，按 F2，然后按 Enter。 如果需要，可调整列宽以查看所有数据。

销售人员超过 Q1 配额超过 Q2 配额超过 Q3 配额
王伟是否否
孙力是是否
张颖是支持是
李芳否支持是
公式说明结果
=COUNTIFS(B2:D2,"=是")计数王伟超出 Q1、Q2 和 Q3 阶段销售配额的次数（仅 Q1）。1
=COUNTIFS(B2:B5,"=是",C2:C5,"=是")计算有多少销售人员同时超出其 Q1 和 Q2 配额（孙力和张颖）。2
=COUNTIFS(B5:D5,"=是",B3:D3,"=是")计数李芳和孙力超出 Q1、Q2 和 Q3 阶段销售配额的次数（仅 Q2）。1

示例 2

数据****
12011-5-1
22011-5-2
32011-5-3
42011-5-4
52011-5-5
62011-5-6
公式说明结果
=COUNTIFS(A2:A7,"<6",A2:A7,">1")计算 1 和 6 之间（不包括 1 和 6）有几个数包含在单元格 A2 到 A7 中。4
=COUNTIFS(A2:A7, "<5",B2:B7,"<5/3/2011")计算单元格 A2 到 A7 中包含小于 5 的数，同时在单元格 B2 到 B7 中包含早于 2011-5-3 的日期的行数。2
=COUNTIFS(A2:A7, "<" & A6,B2:B7,"<" & B4)说明与前例相同，但在条件中使用单元格引用而非常量。2

9.SUMIFS

用法参照COUNTIFS，用于计算其满足多个条件的全部参数的总量

10.INDIRECT

用途

返回由文本字符串指定的引用。 此函数立即对引用进行计算，并显示其内容。 如果需要更改公式中对单元格的引用，而不更改公式本身，请使用函数 INDIRECT。

语法

INDIRECT(ref_text, [a1])

INDIRECT 函数语法具有以下参数：

  • Ref_text    必需。 对包含 A1 样式引用、R1C1 样式引用、定义为引用的名称或作为文本字符串对单元格的引用的单元格的引用。 如果ref_text不是有效的单元格引用，则 INDIRECT 返回#REF！ 错误值。

    • 如果ref_text引用外部引用 (工作簿) ，则必须打开另一个工作簿。 如果源工作簿未打开，INDIRECT 返回#REF！ 错误值。

      注意   Web 应用中不支持Excel引用。\

    • 如果ref_text单元格区域超出行限制 1，048，576 或列限制 16，384 (XFD) ，INDIRECT 返回 #REF！ 错误。

      注意   此行为与早于 Excel 的版本不同， Microsoft Office Excel 2007 忽略超出的限制并返回值。\

  • A1    可选。 一个逻辑值，用于指定包含在单元格 ref_text 中的引用的类型。

    • 如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。
    • 如果 a1 为 FALSE，则将 ref_text 解释为 R1C1 样式的引用。

示例

复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果，请选中它们，按 F2，然后按 Enter。 如果需要，可调整列宽以查看所有数据。

数据
B21.333
B345
赵强10
562
公式说明结果
'=INDIRECT(A2)单元格 A2 中的引用值。 引用的是单元格 B2，其中包含值 1.333。1.333
'=INDIRECT(A3)单元格 A3 中的引用值。 引用的是单元格 B3，其中包含值 45。45
'=INDIRECT(A4)因为单元格 B4 有定义名“国明”，对定义名的引用即是对单元格 B4 的引用，其中包含值 10。10
'=INDIRECT("B"&A5)将 B 和 A5 中的值 (5) 合并在一起。 这将反过来引用单元格 B5，其中包含值 62。62
avatar
文章
阅读
粉丝