arrays - 如何使用 Excel 溢出范围将多列数组转换为两列数组

Question

我正在尝试创建一个溢出范围解决方案，以将日期列表和多列名称转换为结构化的两列数据。我可以使用 VBA 来做到这一点，但是因为这将是自动化的和基于 Web 的，所以我需要一个溢出范围解决方案。

我的情况的示例文件可以在这个文件中找到。

正如您将在下面看到的，我有一组在 12 月请求休假/PTO的员工（“懒鬼”）的列表。我创建了两个溢出范围公式的列表，它们是：

1. 列出十二月所有日子的列
2. 请求休息日的懒惰者列表（横向排列）。

第 2 部分提出了问题，因为懒鬼的数量不一致，我不确定如何为每一行创建额外的日期。因此，我的问题是如何构造一个过滤器/数组公式来按天列出每个员工。

在以下选项卡中，您可以看到我想要的结果。我使用了一个宏来生成它，但是因为它是基于网络的，所以我不能使用它。

我尝试了各种混合和匹配数组公式和过滤器，但找不到用每个名称填充日期的方法。

虽然我的问题仅限于解决从多列到两列方法的转换，但我很高兴听到针对整体概念的评论。

标签： arraysexcelspill-range

Answer 1

这是您的数据表：

=LET(
    end,       PtoRequestsTable[End],
    strt,      PtoRequestsTable[Start],
    us,        PtoRequestsTable[Slacker],
    usCnt,     COUNTA(us),
    lst,       DATE(YEAR(TODAY()),12,1),
    led,       DATE(YEAR(TODAY()),12,31),
    dtSq,      INT(SEQUENCE((led-lst+1)*usCnt,,lst,1/usCnt)),
    md,        MOD(SEQUENCE((led-lst+1)*usCnt,,0),usCnt)+1,
    ussl,      IF((INDEX(end,md)>=dtSq)*(INDEX(strt,md)<=dtSq),INDEX(us,md),""),
               SORT(FILTER(CHOOSE({1,2},dtSq,ussl),ussl<>""),1,-1)
)

它会随着表格自动增长和收缩。它还具有开始和结束日期作为输入。