Excel中常用函数应用

一、函数基础知识

1.1.公式 –插入函数 –搜索想要的函数

在Excel中，要插入函数并搜索想要的函数，可以按照以下步骤进行：

⑴.选择要插入函数的单元格：
首先，单击你想要插入函数的单元格，通常是你希望显示函数结果的单元格。

⑵.点击函数按钮：
在Excel的公式选项卡中，你会找到一个名为“函数库”的区域。在这个区域，有一个按钮标有“插入函数”或“函数插入”等字样。点击这个按钮。

⑶.选择函数：
点击“插入函数”按钮后，会弹出一个函数插入对话框。在这个对话框中，你可以浏览不同的函数类别，或者在搜索框中输入你想要查找的函数名称。

⑷.搜索想要的函数：
在函数插入对话框的搜索框中输入你想要查找的函数名称，然后按下回车键或点击“确定”按钮。Excel会列出所有与你搜索词相关的函数。

⑸.选择并插入函数：
从搜索结果中选择你想要使用的函数，然后点击“确定”按钮。Excel会在你选择的单元格中插入所选函数的名称，并在公式栏中显示函数的参数输入框。

1.2.引用多个区域

在Excel中，你可以引用多个不相邻的单元格或区域，并将它们作为一个函数的参数输入。

使用逗号分隔区域：

  • 若要引用多个相邻的区域，可以使用逗号分隔区域的范围，例如“A1:D10,F1:G10”。
  • 这种方法适用于要引用多个单元格或区域，并且这些单元格或区域在同一行或列上的情况。

在大多数函数中，可以使用多个逗号（或其他分隔符）将多个参数分隔开，因此你可以在函数的参数输入框中输入引用剪贴板，然后使用逗号和其他分隔符将其分开。例如：假设你想要使用SUM函数来计算A1:D10和F1:G10的总和，可以这样输入：=SUM(A1:D10,F1:G10)。

1.3.相对引用、绝对引用

相对引用是指在公式中使用的单元格引用，它会随着公式的复制和填充而相对调整。例如，如果你在单元格A1中输入公式=B1+C1，那么当你将这个公式拖动到A2单元格时，公式会自动调整为=B2+C2，这是因为相对引用会根据公式的位置相对调整单元格引用。

绝对引用是指在公式中使用的单元格引用，它不会随着公式的复制和填充而改变。在Excel中，使用美元符号$来指定绝对引用。例如，如果你在单元格A1中输入公式=$B$1+$C$1，那么无论你将这个公式拖动到其他单元格，引用的单元格始终会保持为B1和C1。

下面的案例中相对引用是G4单元格的数据，后续数据都是依次行和列自动调整，而绝对引用的时候使用$G$4，则拖动的时候始终是固定的G4，不会变动。

1.4.混合引用：固定列/固定行

在Excel中，混合引用是一种引用单元格或区域时，使行或列参照相对变化，而使另一行或列参照保持固定的引用方式。这种引用方式通常用于公式中，以便在填充公式时，可以更方便地引用数据表格中的单元格或区域。在混合引用中，有两种常见的方法，即固定列和固定行。

1. 固定列的混合引用

  • 固定列的混合引用是指在单元格或区域的引用中，将列的部分作为绝对参照，并保持不变。
  • 在使用混合引用时，将美元符号“$”放置在要固定的列字母和数字之间。
  • 例如，假如需要在单元格A1中输入一个混合引用公式来计算B列中数据的平均值，可以这样输入：=AVERAGE($B1:$B10)，此时，当填充此公式至同一行的其它单元格时，B列仍然是绝对参照。

2. 固定行的混合引用

  • 固定行的混合引用是指在单元格或区域的引用中，将行的部分作为绝对参照，并保持不变。
  • 在使用混合引用时，将美元符号“$”放置在要固定的行数字和字母之间。
  • 例如，假如需要在单元格A1中输入一个混合引用公式来计算第1行中A列到H列的数据总和，可以这样输入：=SUM(A$1:H$1)，此时，当填充此公式至同一列的其它单元格时，第1行仍然是绝对参照。

混合引用可用于动态修改单元格中公式的一部分，以便在拖动或复制公式时，可以跨不同行或列引用单元格或区域。如下所示

二、常用运算函数

2.1.排名：rank

在Excel中，RANK函数用于确定数值在指定单元格范围内的排名。通常用于对数值进行排名、分类或评分。RANK函数有两个参数：要求排名的数值以及数值所在的区域。以下是使用RANK函数的基本语法：

=RANK(number,ref,[order])

其中：

  • number：要求排名的数值。
  • ref：包含要排名的单元格区域。
  • order：定义排名方式的可选参数。如果省略此参数，默认情况下数值越大，排名越高。如果需要按数值从小到大排序，可以将order参数设置为0或FALSE。

举个例子，如果想在范围A1:A10中查询数值80的排名，应该使用以下公式：=RANK(80,A1:A10,1)。这将返回数值80在所选区域中的排名（排在所有数值中的第几位），其中order参数指定为1，表示结果从高到低排序。需要注意的是，Excel的RANK函数可以自定义排名方式。由于默认排序方式为从大到小，对于数值相同的数据，后面出现的数据排名会更高。若需要值相同的数据具有相同的排名，则可以使用RANK函数的改进版本——RANK.EQ或RANK.AVG函数。

使用RANK函数可以轻松确定单元格区域中给定数值的排名，在数据分析和报表制作中非常实用。

2.2.极值：max和min

Excel 中的 MAX 和 MIN 函数分别用于找到一组数中的最大值和最小值。语法如下：

  • MAX 函数的语法是：MAX(数值1, 数值2, ...)，它会返回参数中的最大值。
  • MIN 函数的语法是：MIN(数值1, 数值2, ...)，它会返回参数中的最小值。

例如，如果你有一列数据在 A1 到 A10 单元格中，你可以使用 MAX 函数来找到这些数中的最大值：=MAX(A1:A10)。类似可以使用 MIN 函数来找到最小值。

2.3.计数：count，counta，countif和countifs

⑴.COUNT

COUNT（计数）：用于计算指定范围内的单元格中包含数字的个数。

示例：如果在 A2:E2 单元格范围内有 155.99、10.89、缺考、65.99 和 3.45，则 COUNT(A1:A5) 返回值为 4。如下：

⑵.COUNTA

COUNTA（计数非空）：用于计算指定范围内的单元格中非空单元格的个数，可以是数字、文本或错误值。

示例：如果在 A2:E2 单元格范围内有 6.48、3.85、空格、7.38 和5.81，则 COUNTA(A1:A5) 返回值为 4。如下：

⑶.COUNTIF

COUNTIF（条件计数）：用于计算符合指定条件的单元格的个数。

示例：如果在 A8:E8 单元格范围内有 1、2、3、4 和 5，则 COUNTIF(A1:A5,">3") 返回值为 2，因为只有 4 和 5 大于 3。

⑷.COUNTIFS

COUNTIFS 函数是 Excel 中用于统计满足多个条件的单元格个数的函数。它的基本语法如下：

COUNTIFS(条件区域1, 条件1, [条件区域2, 条件2], ...)

其中，条件区域1 是希望进行判断的数据范围，条件1 是与之相对应的条件。根据实际需求，你可以通过添加 条件区域2、条件2 和更多的条件范围和条件来实现更精确的计数。

以下是一些 COUNTIFS 函数的示例用法：

  1. 统计成绩大于90分的人数： 在单元格中输入公式： =COUNTIFS(C2:C12,">90")。 对于单个条件来说，COUNTIFS 函数与 COUNTIF 函数的用法是一样的。

  2. 统计1班成绩大于90分的人数： 输入公式： =COUNTIFS(A2:A13,"1班",C2:C13,">90")

  3. 统计成绩在80~90分之间的人数： 在单元格中输入公式： =COUNTIFS(C2:C13,">=80", C2:C13,"<90")

案例如下：

示例：如果在 A8:E8 单元格范围内有 1、2、3、4 和 5，A9:E9 单元格范围内有 "A"、"B"、"A"、"A" 和 "B"，则 COUNTIFS(A8:E8,">3",A9:A9,"A") 返回值为 1，因为只有 4 大于 3 并且对应的 E 列单元格为 "A"的只有一个。如下图：

2.4.平均：average，averagea，averageif和averageifs

⑴.AVERAGE 函数：

语法：=AVERAGE(数据范围)

作用：计算指定数据范围内数值的平均值。

示例：假设我们有一列销售额数据（D4:D15），使用 =AVERAGE(D4:D15) 可以得到平均销售额为 $271.58。请注意，如果某个单元格为空，它不会计入平均值的计算。

⑵.AVERAGEA 函数

当需要计算的数据包含数字、文本和逻辑值的单元格范围的平均值时，可以使用 AVERAGEA 函数。AVERAGEA 函数将会把文本和逻辑值转换为相应的数值，"文本" 会被视为0，TRUE 会被视为1，FALSE 会被视为0，然后计算它们的平均值。

案例：有一个单元格范围 A10:E10，其中包含数字、文本和逻辑值，要计算这些值的平均值，可以使用 AVERAGEA 函数，计算结果为 (10 + 0 + 1 + 20 + 0) / 5 = 31 / 5 = 6.2。所以 AVERAGEA 函数的返回值为6.2。

⑶.AVERAGEIF 函数：

语法：=AVERAGEIF(条件范围, 条件, 平均值范围)

作用：根据条件筛选数据范围，并计算符合条件的数值的平均值。

假设你有一个销售数据的 Excel 表格，其中包含产品名称和销售额两列。你想要计算某个特定产品的平均销售额。你可以使用 AVERAGEIF 函数来实现这个目标。

假设你的表格如下：

产品名称销售额
产品A 100
产品B 150
产品A 120
产品C 80
产品B 200
产品A 90

现在想要计算产品 A 的平均销售额。可以使用 AVERAGEIF 函数来查找产品名称列中符合条件的单元格，并计算相应的销售额的平均值。具体的公式如下：

=AVERAGEIF(A2:A7, "产品A", B2:B7)

这里：

  • A2:A7 是产品名称的单元格范围。
  • "产品A" 是要匹配的条件。
  • B2:B7 是对应的销售额的单元格范围。

这个函数会找到产品名称列中所有与 "产品A" 匹配的单元格，并计算这些单元格对应的销售额的平均值。在这个例子中，结果将是 (100 + 120 + 90) / 3 = 103.33。

⑷.AVERAGEIFS 函数：

语法：=AVERAGEIFS(平均值范围, 条件范围1, 条件1, 条件范围2, 条件2, ...)

作用：根据多个条件筛选数据范围，并计算符合所有条件的数值的平均值。

AVERAGEIFS 函数与 AVERAGEIF 函数类似，但它允许你根据多个条件计算数据的平均值。下面是一个例子，说明了 AVERAGEIFS 函数的作用：假设你有一个销售数据的 Excel 表格，包含产品名称、销售额和销售区域三列。你想要计算特定产品在特定区域的平均销售额。你可以使用 AVERAGEIFS 函数来实现这个目标。

产品名称销售额销售区域
产品A 100 区域1
产品B 150 区域2
产品A 120 区域1
产品C 80 区域2
产品B 200 区域1
产品A 90 区域2

现在，假设你想要计算在区域1销售的产品A的平均销售额。你可以使用 AVERAGEIFS 函数来查找符合多个条件的单元格，并计算相应的销售额的平均值。具体的公式如下：

=AVERAGEIFS(B2:B7, A2:A7, "产品A", C2:C7, "区域1")
这里：
  • B2:B7 是销售额的单元格范围。
  • A2:A7 是产品名称的单元格范围。
  • "产品A" 是要匹配的产品名称条件。
  • C2:C7 是销售区域的单元格范围。
  • "区域1" 是要匹配的销售区域条件。

这个函数会找到产品名称列中为 "产品A"，并且销售区域列中为 "区域1" 的单元格，并计算这些单元格对应的销售额的平均值。在这个例子中，结果将是 (100 + 120) / 2 = 110。

2.5.时间日期

Excel 中有许多日期和时间相关的函数，用于处理日期、时间和日期时间数据。以下是一些常用的日期时间函数及其作用的示例说明：

⑴.TODAY()

作用: 返回当前日期（不包括时间）。

=TODAY()

返回当前日期，例如：2024-04-18。

⑵.NOW()

作用: 返回当前日期和时间。

=NOW()

返回当前日期和时间，例如：2024-04-18 10:30:00。

⑶.DATE(year, month, day)

作用: 根据提供的年、月、日构建日期。

=DATE(2024, 4, 18)

返回日期 2024 年 4 月 18 日。

⑷.TIME(hour, minute, second)

作用: 根据提供的小时、分钟、秒构建时间。

=TIME(10, 30, 0)

返回时间 10:30:00。

⑸. DATEDIF(start_date, end_date, unit)

作用: 计算两个日期之间的间隔。

=DATEDIF(A1, A2, "Y")

假设 A1 包含起始日期，A2 包含结束日期，上述公式将返回两个日期之间的完整年数。

⑹.YEAR(date), MONTH(date), DAY(date)

作用: 分别从日期中提取年、月、日。

=YEAR(A1)
=MONTH(A1)
=DAY(A1)

假设 A1 包含日期，上述公式将分别返回该日期的年、月、日。

⑺. HOUR(time), MINUTE(time), SECOND(time)

作用: 分别从时间中提取小时、分钟、秒。

=HOUR(A1)
=MINUTE(A1)
=SECOND(A1)

假设 A1 包含时间，上述公式将分别返回该时间的小时、分钟、秒。

⑻. WEEKDAY(date, [return_type])

作用: 返回日期对应的星期几。

=WEEKDAY(A1)
=WEEKDAY(A1, 2)

上述公式将分别返回日期 A1 是星期几，可以通过 return_type 参数调整返回的方式（1 表示星期日为第一天，2 表示星期一为第一天）。

⑼.EOMONTH 

EOMONTH 函数是Excel中一个常用的日期时间函数，其英文全称为 "End of Month"，意为取得指定日期所在月份的最后一天的日期。它通常用于计算滚动日期，例如计算一定时间跨度后的最后一天。下面是 EOMONTH 函数的语法：

EOMONTH(start_date, months)

其中，start_date 是开始日期，months 是月数，表示时间跨度，可以是正数、负数或零。如果 months 为正数，则 EOMONTH 函数将返回 start_date 所在月份加上指定的月份后的最后一天的日期。如果 months 是负数，则将返回 start_date 所在月份减去指定的月份数后的最后一天的日期。如果 months 是零，则将返回 start_date 所在的月份的最后一天的日期。

下面是 EOMONTH 函数的示例：

=EOMONTH("2022/3/14",0)     // 返回 "2022/3/31"
=EOMONTH("2022/3/14",2)     // 返回 "2022/5/31"
=EOMONTH("2022/3/14",-2)    // 返回 "2022/1/31"

在这些示例中，第一个参数是开始日期，第二个参数表示计算的时间跨度，根据时间跨度的不同，EOMONTH 函数返回的结果也不同。

三、函数进阶使用

3.1.Vlookup函数的模糊查找

VLOOKUP 是 Excel 中一种常用的查找函数，用于在数据表中按照指定的条件查找某个值，并返回相应的结果。它的语法如下：

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

各参数的说明如下：

  • lookup_value: 要在表格中查找的值，通常是一个单元格引用或者直接输入的值。

  • table_array: 要进行查找的表格范围，通常是一个区域引用。这个范围至少应该包含要查找的值以及要返回的结果。

  • col_index_num: 返回结果所在的列号。例如，如果您想要返回表格范围中的第二列作为结果，则此参数值为 2。

  • range_lookup (可选): 这是一个逻辑值，表示是否要进行近似匹配。如果为 TRUE 或省略，则 VLOOKUP模糊匹配，如果为 FALSE，则 VLOOKUP 将查找精确匹配的值。通常建议使用 FALSE 以确保准确性。

VLOOKUP 函数的工作原理是：在指定的表格范围中按照 lookup_value 查找，并在找到匹配值时，返回该行中指定列的值。它的作用是根据指定的查找值，在一个表格或区域中查找匹配项，并返回相应的结果。

案例一：假设你有一个包含员工姓名和对应工资的表格，如下所示：

员工姓名工资
张三 3000
李四 3500
王五 3200

现在，假设你想要查找某个员工的工资。你可以使用 VLOOKUP 函数来实现这个目标。例如，你想要查找李四的工资，你可以使用以下的 VLOOKUP 函数：

=VLOOKUP("李四", A2:B4, 2, FALSE)

这里：

  • "李四" 是要查找的员工姓名。
  • A2:B4 是你的表格区域，第一列是员工姓名，第二列是对应的工资。
  • 2 表示你要返回的结果在查找区域中的第几列，这里表示工资所在的列。
  • FALSE 表示要进行精确匹配，即只返回与查找值完全相等的结果。

注意：

  • 如果要使用 VLOOKUP 函数，要确保查找区域的数据是按照顺序排列的。
  • 查找的内容必须是在第一列，例如查找的李四相关的，李四必须在第一列才可以。也就是指定查找范围的第一列才可以。

这个函数会在表格中查找员工姓名列中为 "李四" 的单元格，并返回其对应的工资。在这个例子中，结果将是 3500。

案例二：求员工姓名查询对应的业绩点

案例三：根据指定的范围求员工销售业绩的提成点

3.2.Vlookup多条件查找

VLOOKUP 函数通常只能实现单条件查找，这里所说的只是一个特殊情况，通过辅助列实现绑定多条件查询

案例：获取指定日期和品牌的销售额度，实现的时候，需要绑定日期和品牌。创建一个辅助列，然后绑定，如下：

绑定后效果如下：

指定查询公式如下：

=VLOOKUP(I2&J2,$A$2:$E$33,5,)
这里的I2&J2指的是绑定日期和品牌，然后通过绑定，在去根据上面创建的辅助列进行查询。如下：

3.3.Hlookup函数查找

HLOOKUP 函数是 Excel 中的一种水平查找函数，用于在指定的水平区域中查找某个值，并返回相应的结果。其语法如下：

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

参数说明：

  • lookup_value: 要查找的值。
  • table_array: 要进行查找的区域范围。
  • row_index_num: 查询后返回结果所在的行号。
  • range_lookup (可选): 匹配模式。如果为 TRUE 则 HLOOKUP模糊匹配，如果为 FALSE，则 HLOOKUP 将查找精确匹配的值。

举例说明：假设您有一个包含产品名称和对应价格的数据表格，如下所示：

 

如果您想要根据姓名查找对应的提成点和业绩，可以使用 HLOOKUP 函数：

# 提成点
=HLOOKUP(I8,$1:$3,3,FALSE)
# 业绩
=HLOOKUP(I8,$1:$3,2,FALSE)

这个公式意味着在 1:3 区域（指的是行）中查找值为指定员工姓名的 "提成点" 和业绩，并返回其对应的数据。由于价格信息在表格范围的第二、三行，因此 row_index_num 参数为 3表示提成点，2表示业绩。由于我们希望进行精确匹配，因此 range_lookup 参数为 FALSE。执行这个公式后，会返回商品B的价格。

3.4.Match函数与Vlookup函数结合

MATCH 函数是 Excel 中用于在指定范围内查找某个值并返回其相对位置的函数。它的语法如下：

MATCH(lookup_value, lookup_array, [match_type])

参数说明：

  • lookup_value: 要查找的值。
  • lookup_array: 要进行查找的范围。
  • match_type (可选): 匹配类型，用于指定匹配方式。它可以取三个值之一：
    • 1 或省略：表示查找比 lookup_value 大的最小值。要求 lookup_array 必须按升序排列。
    • -1：表示查找比 lookup_value 小的最大值。要求 lookup_array 必须按降序排列。
    • 0：表示精确匹配。如果找不到完全匹配的值，则返回错误。

举例说明：
假设有一个包含学生姓名的列表，如下所示：

学生姓名
小明
小红
小刚
小美

如果要查找 "小红" 在列表中的位置，可以使用以下公式：

=MATCH("小红", A1:A4, 0)

这个公式会在 A1:A4 范围内查找 "小红"，并返回其相对位置。由于我们希望进行精确匹配，因此 match_type 参数设置为 0。如果 "小红" 存在于列表中，此公式将返回她在列表中的位置，否则将返回错误。

 

举例说明：
假设您有一个包含员工姓名和对应部门的数据表格，如下所示：

员工姓名部门
小明 销售部
小红 财务部
小刚 技术部
小美 市场部

如果您想要查找员工姓名为 "小红" 的部门，可以结合使用 MATCH 和 VLOOKUP 函数：

=VLOOKUP("小红", A2:B5, MATCH("部门", A1:B1, 0), FALSE)

这个公式的意思是：在 A2:B5 区域中查找值为 "小红" 的员工姓名，并返回其所在的部门。MATCH("部门", A1:B1, 0) 部分用于确定部门信息在表格中的哪一列。由于我们希望进行精确匹配，因此 range_lookup 参数为 FALSE。

案例：根据对应的学生姓名和科目，返回该学生科目的成绩，实现公式如下：

=VLOOKUP(L3,$A:$G,MATCH(M3,$A$1:$H$1,0),FALSE)

效果如下图：

3.5.IF函数使用

IF 函数是 Excel 中的逻辑函数，用于根据指定条件返回不同的值。其语法如下：

IF(logical_test, value_if_true, value_if_false)

参数：

  • logical_test: 要进行判断的条件。
  • value_if_true: 如果条件为真，要返回的值。
  • value_if_false: 如果条件为假，要返回的值。

举例说明：假设您有一个包含学生成绩的数据表格，如下所示：

学生姓名分数
小明 85
小红 45
小刚 90
小美 60

如果您想要根据学生成绩判断是否及格，可以使用 IF 函数：

=IF(B2>=60, "及格", "不及格")

这个公式意味着如果 B2 单元格中的分数大于等于 60，则返回 "及格"，否则返回 "不及格"。如下图：

但是需要注意的时候，这里的IF函数的灵活性是比不了编程语言中的，如果分支太多，则需要不断地嵌套，如下面根据业绩范围求提成点，如下：

=IF(D2<50000,"2%",IF(D2<200000,"3%","5%"))

效果如下：

posted @ 2024-04-17 11:52  酒剑仙*  阅读(300)  评论(0)    收藏  举报