根据 Excel 中的多个依赖关系自动填充值

根据 Excel 中的多个依赖关系自动填充值

我已经填好了三列信息。C 列中的值取决于 A 列和 B 列中的两个标识符(例如,George 始终是美国的苹果卖家,而 Sarah 是甜菜卖家)。

我在第 1-8 行中有足够的数据,因此我希望从第 9 行开始的单元格在每次输入新行时自动开始填充所需的信息。

因此,如果我在 A9 中输入“Beets”,在 B9 中输入“Australia”,那么 C9 应该会提取值“Amy”。我将提取此值以自动填充表格的其余部分,以便将来自动添加行。

我使用 INDIRECT(ROW-1) 函数通过公式引用单元格上方的整列来获取正确的数据,如果这有意义的话......

       A         B         C
1   Product  Country    Contact 
2   Apple    Australia  John
3   Apple    America    George
4   Beets    Australia  Amy
5   Beets    America    Sarah
6   Carrot   Australia  Greg
7   Carrot   America    Belinda
8   Apple    Australia  John 
9   Beets    Australia  _Formula:_

=INDEX((INDIRECT("C2:C"&ROW()-1)),MATCH(A10,IF((INDIRECT("B2:B"&ROW()-1))=B10,(INDIRECT("A2:A"&ROW()-1)))),0)

答案1

这是一个似乎有效的答案:

=VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 2, 2), 2, 0)

从内到外进行:

  • VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0) -1, 0, 2, 2), 2, 0)
    MATCH(A9, A$2:A$7, 0)在范围 { , , , , , } 中查找A9( ) ,得出范围为 1-6 的索引值。当然,因为 =  、 = 和 =  ,所以索引值始终为 1、3 或 5;对于 ,索引值为 3。BeetsA2:A7AppleAppleBeetsBeetsCarrotCarrotA2A3A4A5A6A7Beets
  • MATCH(A9, A$2:A$7, 0)-1将上述内容重新映射到 0-5 范围内(具体而言,0、2 或 4;对于 Beets,它是 2)。
  • OFFSET(B$2:C$7, the_above, 0, 2, 2)
    • 占领该B2:C7地区,
    • 从左上角 ( B2) 开始,向下走两行(因为 第一次出现BeetsA2:A7第三行,这意味着我们必须从第 2 行向下走两 (3-1) 行)并向右走零列,到达单元格B4
    • 然后从该点开始取一个 2×2 区域。这样我们就得到了范围B4:C5,即
      澳大利亚艾米
      美国莎拉
      这是全球甜菜卖家的列表。请注意,我们需要一个两列宽的区域,因为我们想将国家与卖家联系起来,但高度应该是国家的数量(在这个例子中恰好是两个)。如果有七个国家,我们会将其更改为 (尽管,如果有七个国家(和三种商品),介绍块将有 21 行高,所以我们将使用从第 2 行到第 22 行的范围。)OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 7, 2)
  • VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 2, 2) , 2, 0)
    VLOOKUP(以及它的兄弟LOOKUPHLOOKUP)本质上是INDEX+的简写MATCH。这将搜索第一列(V代表甜菜卖家区域/数组的垂直)( B4:C5) 用于B9( ),然后返回与查找值 ( )  Australia匹配的行的第 2 列的值。位于第 4 行。由于我们谈论的是列和,列在本语境中是“第 2 列”,因此这将我们带到包含 的单元格。AustraliaAustraliaBCCC4Amy

...这是第 9 行的期望结果,因为 Amy 是澳大利亚甜菜供应商。

相关内容