谷歌表格-来自两个单独列的交叉连接/笛卡尔积

Question

希望有人能帮我在Google Sheets中使用笛卡尔积公式。我有两列分离的数据，并希望在单独的标签页中创建这两列的所有可能组合。第一列是ID（文本），第二列是日期格式。输出应该是两个单独的列。公式应是动态的，也就是说，当新的ID或日期添加到输入列表时，列表应更新。

我在网上寻找解决方案，但没有找到可行的解决方案。我熟练掌握Excel，但不太熟悉Google Sheet :)

以下是一个示例表格：https://docs.google.com/spreadsheets/d/150uIg3XH1hxZa8vSxDhcVZVOcSEOp175OPYL4Rc-wWI/edit?usp=sharing

- William Pontoppidan

Answer 1

使用此代码：

=ARRAYFORMULA(
  SPLIT(
    FLATTEN(
        FILTER('input 1'!A2:A, 'input 1'!A2:A <> "")
      & ","
      & TRANSPOSE(FILTER('input 2'!A2:A, 'input 2'!A2:A <> ""))
    ),
    ","
  )
)

在单元格A3中，但请确保重新格式化B列为日期（从E列复制格式）：

- Marios

---

谢谢，这正是我所需要的。目前有10个ID（'input'！A2：A11）和12个月份（'input 2'！A2：A13）。您知道是否可以使公式“动态”，以便如果我添加新的ID，则公式将自动检测并更改为“'input'！A2：A12”吗？谢谢！ - William Pontoppidan

@WilliamPontoppidan 通过使用FILTER修正了公式，以支持开放范围，使其更具动态性。 - kishkin

这太完美了。非常感谢！@WilliamPontoppidan 顺便说一下，您还没有标记任何答案。 - IMTheNachoMan

Answer 2

我认为Marios的解决方案更适合你的情况，但是这里还有另一种解决方案，只是为了展示不同的方法。通过一些工作，这种方法可以推广到任意列数。

=ARRAYFORMULA(
  {
    VLOOKUP(
      INT(SEQUENCE(COUNTA('input 1'!A2:A) * COUNTA('input 2'!A2:A), 1,) / COUNTA('input 2'!A2:A)) + 1,
      {SEQUENCE(COUNTA('input 1'!A2:A)), FILTER('input 1'!A2:A, 'input 1'!A2:A <> "")},
      2,
    ),
    VLOOKUP(
      MOD(SEQUENCE(COUNTA('input 1'!A2:A) * COUNTA('input 2'!A2:A), 1,), COUNTA('input 2'!A2:A)) + 1,
      {SEQUENCE(COUNTA('input 2'!A2:A)), FILTER('input 2'!A2:A, 'input 2'!A2:A <> "")},
      2,
    )
  }
)

- kishkin

Answer 3

尝试：

=INDEX(SPLIT(FLATTEN(
 OFFSET('input 1'!A2,,,COUNTA('input 1'!A2:A))&"♠"&TRANSPOSE(
 OFFSET('input 2'!A2,,,COUNTA('input 2'!A2:A)))), "♠"))

并将B列格式化为日期

- player0

Answer 4

使用LAMBDA，您也可以在不依赖于字符串操作的情况下使用它。

=LAMBDA(
  range_1, range_2, count_1, count_2,
  {
    BYROW(
      INDEX(ROUNDUP(SEQUENCE(count_1 * count_2) / count_2)),
      LAMBDA(r, INDEX(range_1, r))
    ),
    BYROW
      (INDEX(MOD(SEQUENCE(count_1 * count_2) - 1, count_2) + 1),
      LAMBDA(r, INDEX(range_2, r))
    )
  }
)('input 1'!A2:A, 'input 2'!A2:A, COUNTA('input 1'!A2:A), COUNTA('input 2'!A2:A))

- Le Hibou