用于从溢出范围内的两个数据集创建笛卡尔积的公式

Question

我正在寻找一个公式，它可以从两组数据中创建笛卡尔积，其中任何一组数据都可以增加或减少。

@ScottCraner 在这篇文章中给出的答案几乎给了我答案，只是它没有像预期的那样溢出。
Excel - 将 a 列和 b 列的每个组合合并到 d 列和 e 列

我不是在寻找 PowerQuery 或 VBA 解决方案 - PQ 涉及信任员工按下Refresh，VBA 涉及信任员工注意到显示“启用宏”的黄色栏并理解其含义。

测试数据：

在上图中，我尝试M:N使用列中的数据获取列中的值A:F。
目前，我正在获取中的值J:K。

• 我对原始数据的精简版本位于D一个表格的列中，这个表格被想象为表格1。
• F1包含公式=SEQUENCE(EndDate-StartDate+1,,StartDate)，为我提供开始和结束之间的所有日期的列表。
• G1包含公式=UNIQUE(Table1[Process])
• I1包含溢出公式=SEQUENCE(COUNTA($F$1#)*COUNTA(G1#))，该公式将给出最终表中的行数（即我需要溢出多少）。
• J1包含公式=IF(I1#<>"",INDEX($F$1#,INT(ROW()-1)/COUNTA($G$1#)+1))。
  该Index部分本身会向下拖动并向我提供列中的值M，或者我可以选择范围并使用数组公式也向我提供值。
  如果我在单元格中评估公式，J4它应该引用单元格F2....并且确实如此，但随后会返回单元格中的值F1。
  

我感觉我遗漏了有关溢出范围的一些基本知识。有什么想法吗？

Answer 1

使用 Let 和 CHOOSE，我们可以将所有步骤合并为一个公式来输出两列：

=LET(rng,Table1[Process],
    strt,A2,
    end,B2,
    unq,UNIQUE(rng),
    dt,SEQUENCE(end-strt+1,,strt),
    cntP,COUNTA(unq),
    cntD,COUNT(dt),
    lstP,INDEX(unq,INT(SEQUENCE(cntP*cntD,,1,1/cntD))),
    lstD,INDEX(dt,MOD(SEQUENCE(cntP*cntD,,0),cntD)+1),
    CHOOSE({1,2},lstP,lstD))

它基本上按照你的步骤进行。

1. 我们从表中获得了唯一值。
2. 我们使用序列获取日期范围。
3. 我们计算两个列表并存储它们。
4. 使用 INT() 我们得到一个数字数组，该数组是日期重复的计数，这些数字是唯一的，因此数字是排序的。这将传递给索引
5. 然后日期也是一样，但是数字是连续的。
6. 然后通过 CHOOSE，我们将它们并排放置，这样它们就会溢出。