我最喜欢在Excel表中使用数据的方法

Excel 数据提取和汇总的三个实用函数

假设您拥有一个大型工作簿，其中包含格式精美、已筛选和排序的表格。您可能认为工作已经完成，但实际上，Excel 正在等待您对这些表格进行更多操作，渴望帮助您充分利用您到目前为止所做的辛勤工作。

本文将介绍三个我经常使用的函数或函数组合，它们可以用来提取或汇总 Excel 表格中的信息。

VLOOKUP 和 HLOOKUP

VLOOKUP 和 HLOOKUP 都用于查找和检索表格中特定位置的值。

• VLOOKUP 依赖于垂直数据表，并查找表中的第一列（垂直）。
• HLOOKUP 依赖于水平数据表，并查找表中的第一行（水平）。

VLOOKUP

这里，我有一个考试成绩列表和每个等级所需的成绩（我们称之为表 1）。我还有一张包含学生成绩的表（从这一点起称为表 2）。我希望 Excel 使用表 1 中的信息来完成表 2 中缺少的列。

我将使用 VLOOKUP，因为我希望 Excel 查找表 1 的第一列中的值以返回表 2 中每个学生的成绩。VLOOKUP 函数的语法如下：

<code>=VLOOKUP(a,b,c,d)</code>

其中：

• a 是要查找的值（在上例中，那是 E 列中的值），
• b 是包含参考值的表格（在本例中，它是单元格 A1 到 B9，或表 1），
• c 是该表中的列号（我希望它返回等级，所以它是表 1 的第二列），以及
• d 是一个可选条件，它告诉 Excel 近似查找值（“TRUE”）还是精确查找值（“FALSE”）。如果留空，则默认值为 TRUE。

因此，在我的例子中，我将在单元格 F2 中输入此公式来计算 Tom 的成绩，然后使用自动填充来查找表中其他成绩：

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

我使用了 $ 符号来为上面的值 b 创建一个绝对引用，因为我希望 Excel 持续使用单元格 A1 到 B9 来查找值。我还为值 d 使用了“TRUE”，因为分数边界表包含范围，而不是分配给单个分数的等级。

HLOOKUP

这里，我们有相同的等级边界信息，但这次是水平显示的。这意味着我们想要获取的数据位于边界表的第二行。

HLOOKUP 函数的语法与 VLOOKUP 类似：

<code>=HLOOKUP(a,b,c,d)</code>

其中：

• a 是要查找的值（在此示例中，是 C 列中的值），
• b 是包含查找值的单元格的绝对引用（在本例中，它是 A1 到 I2），
• c 是该表中的行号（我希望它返回等级，所以它是第二行），以及
• d（可选）是“TRUE”（近似值）或“FALSE”（精确值）。

因此，我将在单元格 C5 中输入此公式来计算 Tom 的成绩，然后使用自动填充来查找表中其他成绩：

<code>=VLOOKUP(a,b,c,d)</code>

INDEX 与 MATCH

另一种有效查找和检索值的方法是通过 INDEX 和 MATCH，尤其是在一起使用时。INDEX 查找并返回已定义位置的值，而 MATCH 查找并返回值的位。它们一起可以实现动态数据检索。

单独的语法

在我们一起查看这些函数之前，让我们简要地单独查看它们。

INDEX 的语法是

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

其中 a 是包含数据的单元格范围，b 是要评估的行号，c 是要评估的列号。

以此为基础，

<code>=HLOOKUP(a,b,c,d)</code>

将评估单元格 B2 到 D8，并返回该范围内的第四行和第二列中的值。

对于 MATCH，我们遵循

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

其中 x 是我们要查找的值，y 是要查找值的范围，z（可选）是匹配类型。

以此为基础，

<code>INDEX(a,b,c)</code>

将告诉我数字 5 在范围 B2 到 B8 中的位置，而 0 告诉 Excel 执行精确匹配。

结合使用

在此示例中，我希望 Excel 告诉我指定球员在一个给定月份进球的数量。更具体地说，我想知道球员 C 在第三个月进了多少个球，但我将创建此公式，以便我可以随时更改这些条件。

为此，我需要 Excel 确定球员 C 在表格中的位置，然后告诉我数据第三列中的值。

在单元格 G4 中，我将从 INDEX 函数开始，因为我希望 Excel 从我的原始数据中查找并返回一个值。然后，我将告诉 Excel 在哪里查找该数据。

<code>INDEX(B2:D8,4,2)</code>

INDEX 语法的下一部分是行号，这将根据我在单元格 G2 中声明的球员而有所不同。例如，如果我想查找球员 A，它将是第一行。为此，我将启动 MATCH 函数，因为我希望 Excel 将我键入单元格 G2 中的球员与球员列 (A2:A8) 中的相应单元格匹配，并计算出它位于哪一行。我还添加了最后的 0，因为我希望 Excel 返回精确检索。

<code>=VLOOKUP(a,b,c,d)</code>

现在我已经告诉 Excel INDEX 函数的行号，我需要用列号来完成。在我的例子中，列号代表我键入单元格 G3 中的月份号。

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

当我按下 Enter 键时，Excel 正确地告诉我球员 C 在第三个月进了五个球。

现在，我可以更改我的查找表中的任何值以查找任何球员在任何月份的总计。

COUNTIF 和 SUMIF

从它们的名称中可以看出，这两个函数根据您设置的条件来计数和求和值。任何未包含在您条件中的内容都不会被添加或计数，即使它在您指定的范围内也是如此。

COUNTIF

COUNTIF 计数包含特定条件的单元格。语法是

<code>=HLOOKUP(a,b,c,d)</code>

其中 a 是您要计数的范围，b 是计数的条件。

同样，如果我想包含多个条件，我将使用 COUNTIFS：

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

其中 a 和 b 是第一个范围条件配对，c 和 d 是第二个范围条件配对（您可以最多有 127 个配对）。

如果任何条件是文本或逻辑或数学符号，则必须将其括在双引号中。

在我的下面的工资表中，我想计算收入超过 40,000 英镑的人数，以及单独计算获得超过 1,000 英镑奖金的服务人员人数。

要计算工资超过 40,000 英镑的员工人数，我需要在单元格 D8 中输入此公式：

<code>INDEX(a,b,c)</code>

其中 C2:C6 是工资所在的范围，">40000" 是条件。

要计算获得超过 1,000 英镑奖金的服务人员人数，我将使用 COUNTIFS，因为我有两个条件。

<code>INDEX(B2:D8,4,2)</code>

B2:B6,"Services" 部分是第一个范围条件配对，D2:D6,">1000" 是第二个。

即使我的上表中用逗号分隔千位数，我也没有在公式中包含这些逗号，因为逗号在此处具有不同的功能。

SUMIF

SUMIF 根据您设置的条件对单元格求和。它的工作原理与 COUNTIF 类似，但在括号中包含更多参数。语法是

<code>MATCH(x,y,z)</code>

其中 a 是您要在求和之前评估的单元格范围，b 是该评估的条件（这可以是值或单元格引用），c（可选）是如果与 a 不同则要添加的单元格。

这次，我们要计算三件事：超过 40,000 英镑的工资总额、服务部门的总工资以及工资超过 35,000 英镑的员工的奖金总额。

首先，要计算超过 40,000 英镑的工资总额，我需要在单元格 D8 中输入以下公式：

<code>=VLOOKUP(a,b,c,d)</code>

其中 C2:C6 引用表中的工资，">40000" 告诉 Excel 只对超过此金额的值求和。

接下来，我想找出服务部门的总工资。因此，在单元格 C9 中，我将输入

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

其中 B2:B6 引用部门列，"Services" 告诉 Excel 我正在特别寻找服务部门的员工，C2:C6 告诉 Excel 对这些员工的工资求和。

我的最后一个任务是找出收入超过 35,000 英镑的员工获得了多少奖金。在单元格 C10 中，我将输入

<code>=HLOOKUP(a,b,c,d)</code>

其中 C2:C6 告诉 Excel 评估工资，">35000" 是这些工资的条件，D2:D6 告诉 Excel 对满足条件的个人的奖金求和。

Excel 还具有 SUMIFS 函数，它执行相同的过程，但针对多个条件。它与 SUMIF 的语法大相径庭：

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

其中 a 是要相加的单元格范围，b 是要评估的第一个范围，c 是 b 的条件，d 和 e 是下一个范围条件配对（您可以最多有 127 对）。

使用上表，假设我想对收入超过 45,000 英镑的员工的奖金求和。这是我将输入的公式：

<code>INDEX(a,b,c)</code>

---

掌握了上述函数后，请尝试使用 XLOOKUP 函数，该函数旨在通过查找查找值列左侧和右侧的值来解决 VLOOKUP 的一些缺点，而无需重新排列数据。

以上是我最喜欢在Excel表中使用数据的方法的详细内容。更多信息请关注PHP中文网其他相关文章！