在 Microsoft Excel 中使用结构化引用的四种方法

在 Excel 中工作通常围绕着寻找不同数据点之间的联系。但是，在插入复杂的公式时，重复使用相对和绝对显式单元格引用（例如“B7”或其变体）只能让您在公式栏变得难以阅读的混乱之前达到此目的。

Excel 中的结构化引用允许您通过为表及其标题分配名称来简化工作。然后，这些名称可以用作隐式单元格引用，以便 Excel 可以自动获取结构化数据并进行计算。

以下是在 Excel 中使用结构化引用的一些最常见方法。

1.计算内表

由于结构化引用仅适用于表，因此利用它们的最佳方法是在这些相同的表中。

例如，我们将创建一个从 B2 到 F8 的简单表，其中包含商店的销售数据。请注意，我们将表命名为“Sales”（请参阅​​左上角的“表名称”）。

让我们计算一下每笔销售的总金额：

第 1 步： 单击 F2（但不要单击下拉图标）。转到“主页”，然后转到“插入”，然后选择“在右侧插入表格列”。这将自动向表中添加一个新列。

第 2 步： 将 G 列标题命名为“Total”。

第 3 步： 在 G3 中，插入 =[@PricePerUnit]*[@Quantity] 并按 Enter 键。根据需要格式化单元格输出。

“[@PricePerUnit]”和“[@Quantity]”是对这些列中相应字段的引用。列名称之前的“@”参数意味着每个结果单元格将使用同一表行中的引用。

翻译一下，G3 中的公式 =[@PricePerUnit]*[@Quantity] 本质上与写作 =$C3*$D3 相同。

2.获取表之外的范围

当您想在表格外的单元格中使用结构化引用时，您需要在引用前面加上 TableName。在我们前面的示例中，使用“Sales[Total]”将获取表“Sales”的标题“Total”下的整个范围。这意味着您将在一个数组中获得多个可以操作的值。

以下是 Excel 中单元格 I3 中的外观，前提是您为范围向下溢出留出足够的空间。

3.对列求和和部分求和

要快速对整列求和，您可以使用“表格设计”选项（在“表格样式选项”下）中的“总计行”复选标记。以下是获取“数量”和“总计”列总计的示例。

虽然“总计”行本身无法移动，并将被放置在表格的末尾（允许插入），但您可以在其他地方复制其结果：

• 要获取“总计”列中所有行的总和，请使用 =SUM(Sales[Total]).
• 如果您只想获取可见列的总和，例如过滤表格后，请使用 =SUBTOTAL(109,Sales[Total])。这个公式是表格格式中的“总行”选项在其行中实际执行的操作。

您还可以根据表中找到的特定变量获取部分总和，而无需对其进行格式化。例如：

• 要获取 Mike 的所有销售额总和，您可以使用 =SUMIF(Sales[Seller],”Mike”,Sales[Total])。公式中，“Mike”是手动输入的字符串。
• 要获取 ID 为“41230”的所有产品的总和，请使用以下 =SUMIF(Sales[ProductID],41230,Sales[Total])。请注意，由于 ProductID 列具有“常规”格式，因此您可以直接输入数字。

4.通过间接方式从表中进行数据验证

假设您有以前使用过的 Sales 表。您可以创建自定义数据验证选项，以便更轻松地搜索表。让我们创建一个较小的表格，允许您在 ProductID、日期或卖家之间进行选择，然后从这些子集中选择任何单个项目来显示小计。

第 1 步： 在单元格 B13 中，创建数据验证（数据选项卡 > 数据工具 > 数据验证）。

第 2 步： 在弹出窗口中，从“允许”选项中选择“列表”，然后在“源”框中手动插入列的值，并以逗号分隔。在本例中，我们插入了“ProductID、Seller、Date。”

第 3 步： 在单元格 C13 中，创建另一个数据验证。再次选择“列表”。对于“来源”，插入以下公式：=INDIRECT("Sales["&B13&"]").

第 4 步： 在单元格 D13 中，使用以下公式：=SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total]).

您现在可以从两个数据验证列表中选择选项，小计将显示在D13中。

以上是在 Microsoft Excel 中使用结构化引用的四种方法的详细内容。更多信息请关注PHP中文网其他相关文章！