主页 / computer / 问题 / 1671787
Accepted
Darren Bartrup-Cook
Asked: 2021-08-28 04:24:00 +0800 CST

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

  • 772

我正在寻找一个公式，该公式将从两组数据中创建一个笛卡尔积，其中任何一组数据都可以增长或缩小。

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

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

测试数据： 在此处输入图像描述

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

  • 我的原始数据的缩减版本D位于一个名为Table1的表中的列中。
  • 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部分本身将向下拖动并给我 column 中的值M，或者我可以选择范围并使用数组公式也给我值。
    如果我评估单元格中的公式，J4它应该引用单元格F2.... 并且确实如此，但随后返回来自单元格的值F1。
    在此处输入图像描述

我有一种感觉，我在这里遗漏了一些关于溢出范围的基本内容。有任何想法吗？

microsoft-excel worksheet-function

1 个回答

  1. Best Answer

    使用 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，我们将它们并排放置，以免它们溢出。

    在此处输入图像描述

    • 5

相关问题