以下为一些基本函数,掌握这些函数后,基本可以自制部门各种常用的工作表格。如果有问题请在阅读完后在页面下方留言。

VLOOKUP

VLOOKUP 是 Excel 中使用最广泛的函数之一。
使用 VLOOKUP,你可查找左侧列中的值,如果找到匹配项,则会在右侧的另一列中返回信息。
VLOOKUP 表示:
vlookup图解

需要在表格或区域中按行查找内容时,请使用 VLOOKUP。

使用方法:

  1. 选择单元格。

  2. 键入 = VLOOKUP (然后选择要查找的值。

  3. 键入逗号 (,) 并选择要在其中查找值的区域或表。

  4. 键入逗号 (,) 及要查找的值所在行的行号。

  5. 键入 ,FALSE) 查找精确匹配项。

  6. 按 Enter。

视频教学

视频示例中使用的公式为:

=VLOOKUP(A7, A2:B5, 2, FALSE)。

在这一最简单的形式中,VLOOKUP 函数表示:

=VLOOKUP(查阅值、包含查阅值的区域、区域中包含返回值的列号以及(可选)为近似匹配指定 TRUE 或者为精确匹配指定 FALSE)。


IF

IF 函数是 Excel 中最常用的函数之一,也是构建很多公式的必要条件之一,它可以对值和期待值进行逻辑比较。
通过 IF 语句,可以在条件之间进行逻辑比较。IF 语句通常指示某条件为 true 时执行某项操作,否则执行其他操作。公式可以返回文本、值或者更多计算。

因此 IF 语句可能有两个结果。 第一个结果是比较结果为 True,第二个结果是比较结果为 False。

例如,=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。

视频教学

常见问题

问题 详细说明
单元格中的 0(零) value_if_true 或 value_if_False 参数无参数值。若要查看返回的正确值,应为两个参数添加参数文本,或者为参数添加 TRUE 或 FALSE。
#NAME? (显示在单元格中) 这通常意味着公式存在拼写错误。

注意: 如果要在公式中使用文本,需要将文字用引号括起来(例如“Text”)。 唯一的例外是使用 TRUE 和 FALSE 时,Excel 能自动理解它们。

可选

另外单独说一点,if函数虽然简单却是需完成很多公式计算的必要函数之一。
而且if函数本身也可以通过自身的嵌套,完成很多基本的判断。
比如在PAX所使用的表格中,判断一套衣柜到底是属于哪个颜色,就使用了if套嵌。更多的使用方法在如下两个视频当中。

IF嵌套

高级 IF 函数


IFS 函数

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

语法

IFS 函数的语法如下:

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

参数 描述
logical_test1(必需) 计算结果为 TRUE 或 FALSE 的条件。
value_if_true1(必需) 当 logical_test1 的计算结果为 TRUE 时要返回结果。 可以为空。
logical_test2…logical_test127(可选) 计算结果为 TRUE 或 FALSE 的条件。
value_if_true2…value_if_true127(可选) 当 logical_testN 的计算结果为 TRUE 时要返回结果。 每个 value_if_trueN 对应于一个条件 logical_testN。 可以为空。

注意

  • IFS 函数允许测试最多 127 个不同的条件。 但不建议在 IF 或 IFS 语句中嵌套过多条件。 这是因为多个条件需要按正确顺序输入,并且可能非常难构建、测试和更新。

  • 若要指定默认结果,请对最后一个 logical_test 参数输入 TRUE。 如果不满足其他任何条件,则将返回相应值。 在示例 1 中,行 6 和行 7(成绩为 58)展示了这一结果。

  • 如果提供了 logical_test 参数,但未提供相应的 _value_if_true_,则此函数显示“你为此函数输入的参数过少”错误消息。

  • 如果 logical_test 参数经计算解析为 TRUE 或 FALSE 以外的值,则此函数返回 #VALUE! 错误。

  • 如果找不到 TRUE 条件,则此函数返回 #N/A! 错误。


SUM

SUM函数将为值求和。 你可以将单个值、单元格引用或是区域相加,或者将三者的组合相加。

视频教学

语法

SUM(number1,[number2],[number3]..)

参数名称 说明
number1 必需 要相加的第一个数字。 该数字可以是 4 之类的数字,B6 之类的单元格引用或 B2:B8 之类的单元格范围。
number2-255 可选 这是要相加的第二个数字。 可以按照这种方式最多指定 255 个数字。

SUMIF

SUMIF作为条件函数之一,你可以根据给定条件或指定的条件对某区域内进行求和、求平均值、计数或获取最小值或最大值。

使用 SUMIF,你可以根据在另一区域寻找的特定条件对某一区域求和。

结构

SUMIF 的结构如下所示:
sumif结构图解

使用方法

  1. 选择单元格。

  2. 键入 =SUMIF(。

  3. 单击并拖动要分析的单元格。

  4. 输入要查找的类别。

  5. 单击并拖动单元格区域。

  6. 按 Enter。

视频教学

提示:

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

  • 若要根据多个条件对若干单元格求和,请参阅 [SUMIFS 函数]

语法

SUMIF(range, criteria, [sum_range])

SUMIF 函数语法具有以下参数:

  • range   必需。 要按条件计算的单元格区域。 每个区域中的单元格都必须是数字, 或者是包含数字的名称、数组或引用。 空白和文本值将被忽略。 所选区域可能包含标准 Excel 格式的日期 (下面的示例)。

  • criteria   必需。 定义哪些单元格将被添加的数字、表达式、单元格引用、文本或函数形式的条件。 例如, 条件可以表示为32、”>32”、B5、”32”、”苹果” 或 TODAY ()。

重要

任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (“) 括起来。 如果条件为数字,则无需使用双引号。

  • sum_range   可选。 要添加的实际单元格 (如果要添加的单元格不在range参数中指定的单元格)。 如果省略了sum_range参数, 则 Excel 将添加在range参数中指定的单元格 (与应用条件的单元格相同的单元格)。

  • 可以在 criteria 参数中使用通配符 (包括问号 (?) 和星号 ( * ))。 问号匹配任意单个字符;星号匹配任意一串字符。 如果要查找实际的问号或星号,请在该字符前键入波形符 ( ~ )。

  • 使用 SUMIF 函数匹配超过 255 个字符的字符串或字符串 #VALUE! 时,将返回不正确的结果。

  • sum_range 参数与** range** 参数的大小和形状可以不同。求和的实际单元格通过以下方法确定:使用 sum_range 参数中左上角的单元格作为起始单元格,然后包括与 range 参数大小和形状相对应的单元格。


SUMIFS

SUMIFS 函数是一个数学与三角函数,用于计算其满足多个条件的全部参数的总量。此函数可以运用在PAX系列中来得到详细的数据,但如果所需数据量较大或需要知道详细信息,不妨尝试使用数据透视表。
SUMIFS 与 SUMIF 相同,但它允许你使用多个条件对若干单元格求和。
SUMIFS结构如下所示:
SUMIFS公式图示

语法

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

参数名称 详细说明
Sum_range (必需) 要求和的单元格区域。
Criteria_range1 (必需) 使用 Criteria1 测试的区域。Criteria_range1 和 Criteria1设置用于搜索某个区域是否符合特定条件的搜索对。 一旦在该区域中找到了项,将计算 Sum_range 中的相应值的和。
Criteria1 (必需) 定义将计算 Criteria_range1 中的哪些单元格的和的条件。 例如,可以将条件输入为 32、”>32”、B4、”苹果” 或 “32”。
Criteria_range2, criteria2, … (optional) 附加的区域及其关联条件。 最多可以输入 127 个区域/条件对。

常见问题

问题 详细说明
显示 0 (零), 而不是预期结果。 如果要测试文本值,如姓名,请确保将 Criteria1,2 用引号引起来。
Sum_range 有值 TRUE 或 FALSE 时,结果不正确。 Sum_range 的值 TRUE 和 FALSE 的求值方式不同,可能会在将其相加时导致意外结果。Sum_range 中包含 TRUE 的单元格的求值结果为 1。 包含 FALSE 的单元格的求值结果为 0(零)。

最佳做法

要执行的操作 说明
使用通配符。 在 criteria1,2 中使用问号 (?) 和星号 (*) 之类的通配符可以帮助找到相似但不精确的匹配项。问号匹配任何单个字符。星号匹配任意字符序列。如果要查找实际的问号或星号,请在问号前键入波形符 (~)。例如, = SUMIFS (A2: A9, B2: B9, “= A *”, C2: C9, “To?”) 将添加名称以 “To”开头并以可能发生变化的最后一个字母结尾的所有实例。
了解 SUMIF 和 SUMIFS 之间的区别。 SUMIFS 和 SUMIF 的参数顺序有所不同。 具体而言,sum_range 参数在 SUMIFS 中是第一个参数,而在 SUMIF 中,却是第三个参数。 这是使用这些函数时出现问题的一个常见原因。如果要复制和编辑这些相似函数,请确保按正确的顺序放置参数。
对区域参数使用相同行数和列数。 Criteria_range 参数与 Sum_range 参数必须包含相同的行数和列数。

FIND

函数 FIND 和 FINDB 用于在第二个文本串中定位第一个文本串,并返回第一个文本串的起始位置的值,该值从第二个文本串的第一个字符算起。

语法

FIND(find_text, within_text, [start_num])

FINDB(find_text, within_text, [start_num])

FIND 和 FINDB 函数语法具有下列参数:

  • find_text     必需。 要查找的文本。

  • within_text    必需。 包含要查找文本的文本。

  • start_num    可选。 指定开始进行查找的字符。 within_text 中的首字符是编号为 1 的字符。 如果省略 start_num,则假定其值为 1。

注意

  • FIND 和 FINDB 区分大小写,并且不允许使用通配符。 如果您不希望执行区分大小写的搜索或使用通配符,则可以使用 SEARCH 和 SEARCHB 函数。

  • 如果 find_text 为空文本 (“”),则 FIND 会匹配搜索字符串中的首字符(即编号为 start_num 或 1 的字符)。

  • Find_text 不能包含任何通配符。

  • 如果 find_text 未显示在 within_text 中, 则 FIND 和 FINDB 返回 #VALUE! 。

  • 如果 start_num 不大于零, 则 FIND 和 FINDB 返回 #VALUE! 。

  • 如果 start_num 大于 within_text 的长度, 则 FIND 和 FINDB 返回 #VALUE! 。

  • 可以使用 start_num 来跳过指定数目的字符。 以 FIND 为例,假设要处理文本字符串“AYF0093.YoungMensApparel”。 若要在文本字符串的说明部分中查找第一个“Y”的编号,请将 start_num 设置为 8,这样就不会搜索文本的序列号部分。 FIND 从第 8 个字符开始查找,在下一个字符处找到 find_text,然后返回其编号 9。 FIND 始终返回从 within_text 的起始位置计算的字符编号,如果 start_num 大于 1,则会对跳过的字符计数。


本博客所有文章除特别声明外,均采用 CC BY-SA 3.0协议 。转载请注明出处!

ubuntu 16.04安装jupyter notebook 下一篇