是否存在一个公式可以让您返回没有行引用的单元格中的值?
我有一份报告,其中一行在 A 列中有一个行引用(产品名称),在旁边的列中有一个值(余额),而下面紧邻的一行在 A 列中显示相应的值(费率),但没有行引用。
我想做一个 SUMIF 或类似函数,它可以返回余额值,但不适用于汇率值。我试图避免使用以规定的单元格编号为目标的公式,以防将来报告在添加或删除产品时增加或减少列,从而返回错误的值。
谢谢。
答案1
答案2
您拥有什么和需要什么似乎相当清楚。
您所说的“行引用”通常被称为“查找值”。
有多种方法可以查找列(或行,如果需要)中的值。今天(2022 年)我们可能会使用,XLOOKUP()
但这在 2016 年不可用。当时,两种主要方法是VLOOKUP()
和INDEX/MATCH
。
VLOOKUP()
通常情况下,它很擅长查找您称为行引用的值,但显然您可能会看到列的插入和删除。在这种情况下,它表现不佳,尽管它可以通过适当的规划做到这一点。但如果不需要,为什么要进行规划呢?(我是它的忠实粉丝,所以可以说这句话很难付诸实践。)
所以,INDEX/MATCH
。鉴于您使用“行引用”,我认为您对 Excel 不太熟悉。所以我将给出一个基本的解释。
假设您的“行引用”位于 C 列,从第 1 行开始,到第 1000 行。此外,您目前有 23 列产品。因此,您使用 C:Z 列(1 + 23 = 24)和 1000 行。该范围将按C1:Z1000
Excel 中的格式书写。
接下来,我会说您将要查找的值放在单元格 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 都会以相同的方式处理列删除。
还有其他方法可以做到这一点,但是在开始时,简单的方法很容易理解和使用。