在 Excel 中返​​回没有行引用的值

在 Excel 中返​​回没有行引用的值

是否存在一个公式可以让您返回没有行引用的单元格中的值?

我有一份报告,其中一行在 A 列中有一个行引用(产品名称),在旁边的列中有一个值(余额),而下面紧邻的一行在 A 列中显示相应的值(费率),但没有行引用。

我想做一个 SUMIF 或类似函数,它可以返回余额值,但不适用于汇率值。我试图避免使用以规定的单元格编号为目标的公式,以防将来报告在添加或删除产品时增加或减少列,从而返回错误的值。

谢谢。

答案1

如果我理解正确的话,您想要对 B 列中 A 列不为空的所有单元格求和,如下图所示:

在此处输入图片描述

单元格 C11 中的公式是

=SUMIF(A:A,"<>",B:B)

如果这不是您想要的,请编辑您的问题并给出一个例子。

记录:单元格引用由列引用和行引用组成。在单元格引用“A10”中,列引用为“A”,行引用为“10”。您似乎将术语“行引用”用于完全不同的东西,这令人困惑。

答案2

您拥有什么和需要什么似乎相当清楚。

您所说的“行引用”通常被称为“查找值”。

有多种方法可以查找列(或行,如果需要)中的值。今天(2022 年)我们可能会使用,XLOOKUP()但这在 2016 年不可用。当时,两种主要方法是VLOOKUP()INDEX/MATCH

VLOOKUP()通常情况下,它很擅长查找您称为行引用的值,但显然您可能会看到列的插入和删除。在这种情况下,它表现不佳,尽管它可以通过适当的规划做到这一点。但如果不需要,为什么要进行规划呢?(我是它的忠实粉丝,所以可以说这句话很难付诸实践。)

所以,INDEX/MATCH。鉴于您使用“行引用”,我认为您对 Excel 不太熟悉。所以我将给出一个基本的解释。

假设您的“行引用”位于 C 列,从第 1 行开始,到第 1000 行。此外,您目前有 23 列产品。因此,您使用 C:Z 列(1 + 23 = 24)和 1000 行。该范围将按C1:Z1000Excel 中的格式书写。

接下来,我会说您将要查找的值放在单元格 A1 中,而查找所需总和的公式则放在单元格 B1 和 B2 中(因此它们在物理上与它们在数据范围 (C1:Z1000) 中的显示方式相匹配)。

因此,您在 A1 中放置一个值,所需的结果就会出现在 B1 和 B2 中。

其公式如下:

=INDEX(D1:Z1000,  MATCH(A1,  C1:C1000,  0),  0)

它的作用是,内部函数MATCH()会在出现这些值的 C 列中查找您在 A1 中输入的值。它会向公式返回一个数字,即它在“多少行”上找到该值。因此,如果查找值(“行引用”)在第 397 行,它会向公式返回“397”。现在可能很清楚了,看看其中的前两个参数“A1”和“C1:C1000”,但还有第三个参数:“0”,它指示MATCH()查找完全匹配,而不是接近匹配。

因此,找到它之后(在此示例中为第 397 行),公式实际上变成:

=INDEX(D1:Z1000,  397,  0)

INDEX()是一个函数,它被赋予一个数据范围,在本例中为 D1:Z1000,然后是行号和列号。在这个例子中,行号是 397,列号是... 特殊。使用似乎毫无意义的“0”实际上告诉它返回行中的所有列。

此时,让我提醒大家注意,INDEX()给出的范围是 D1 到 Z1000,而不是 C1 到 Z1000。这是因为您不想在公式的输出中获得带有“行引用”的列,因为它可能包含数字和文本,然后这些数字可能会成为总和的一部分,从而使其错误。

因此,INDEX()将获取找到的行号MATCH()并返回该行中的所有产品值,但不返回其中的“行引用”值。

到目前为止一切顺利。在公式返回行信息后,还有一件事要做。但在添加该元素之前,让我先解决您遇到的关键问题,即获取第二行的值以将它们相加。

您可能已经知道该怎么做了,但如果没有,请按照上述操作,只是在MATCH()返回的结果中添加“1”,如下所示:

=INDEX(D1:Z1000, MATCH(A1, C1:C1000, 0) + 1, 0)

现在它将返回“398”INDEX()而不是“397”,这样就会收集查找到的包含“行引用”的行之后的行中的信息。

单元格 B1 获取不添加 1 的版本,而单元格 B2 获取添加 1 的版本。

最后一步是SUM()该部分的结果INDEX/MATCH

=SUM( INDEX(D1:Z1000, MATCH(A1, C1:C1000, 0), 0) )

=SUM( INDEX(D1:Z1000, MATCH(A1, C1:C1000, 0)+1, 0) )

所以一切都非常简单:使用它MATCH()来找到“行引用”所在的行,然后将其用于余额,或为其加 1 作为费率,然后使用它INDEX()来收集所有行的数据,最后用它SUM()来将它们相加。

还有一件事需要解决。那就是如何处理可能随时间发生的列的插入或删除。

有几种行之有效的方法可以做到这一点。一种方法是简单地使范围INDEX()使用比需要的更宽,足够宽,以至于您不需要经常检查它是否仍然正常,因此在我们的示例中,也许不是以 Z 列结束,而是以 BZ 列结束,添加 52 列,这样您就可以在删除至少 52 列之前保持良好。这种方法实际上针对的是这样的情形:您实际上并不插入新产品的列,而是简单地越过最后一个产品并在下一列的顶部添加新产品名称。

如果您可以确保在添加产品时始终插入列,那么您可以使用上述想法的变体,只需在范围中添加一列,即 C1:AA1000,并且永远不要,只需转到下一列并添加产品名称。您始终会插入一列。然后 Excel 将更改您添加的额外列的范围大小,您无需做任何工作。

无论您如何处理插入,Excel 都会以相同的方式处理列删除。

还有其他方法可以做到这一点,但是在开始时,简单的方法很容易理解和使用。

相关内容