Excel UDF 到表中的数据块的逆透视（融化、反向透视、展平、标准化）

这个问题将寻求多种方法LET/LAMBDA VBA UDF and Power Query Function，因此不会有单一的正确答案，而是征集可供参考的方法。

Scott提出了一个问题here关于反转包含数据块而不是单个数据点的复杂表。基本思想如下表所示：

每个城市的数据分为四列，包含压力、温度、湿度和 CO2（或 PTHC）。我们希望根据州和市的月份对 PTHC 值块进行逆透视。这是所需的输出：

行的顺序并不重要，只要它们完整即可 - 即输出可以按月份、城市、州排序……这并不重要。输出不需要是溢出的动态数组 - 即，在 Power Query 函数的情况下，显然不会。

可以假设 PTHC 块总是一致的，即

• 它永远不会跳过字段值，例如PTHC PTC PTHC...
• 它永远不会改变顺序，例如甲状腺激素治疗

月份始终以与块大小相同的组形式呈现（在本例中为 4，因此将有四个一月列、二月列等）。例如如果有7个月，则有7个PTHC块或28列数据。

However，月份的模式也可以交错，这样 月份将增加，并且 PTHC 块将被分组（即 PPP TTT HHH CCC) 像这样：

UDF 还必须在块内容纳多于或少于 4 个字段。 Months 和 PTHC 的使用只是说明，在本例中代表月份的属性将始终是单行（尽管多行方法将是一个有趣的问题 - 但这是一个新的单独的问题）。表示字段值 PTHC 的属性也将是单行。

我将根据 Scott 的问题提出一个 LET 函数，但是有 当然可以是更好的方法，VBA 和 Power Query 都有 他们自己的优势。目标是创建一个集合 工作方法。

LET/LAMBDA 方法

这需要Excel 365。公式为：

=LET( upValues, C3:N7,  upHdr, C2:N2,  upAttr, C1:N1,
      byBody, A3:B7,  byHdr, A2:B2,
      attrTitle, "month",

         upFields, UNIQUE( upHdr,1 ), blockSize, COUNTA( upFields ),
         byC, COLUMNS( byBody ), upC, COLUMNS( upValues ),
         dmxR, MIN( ROWS( upValues ), ROWS( byBody ) ),
         upCells, dmxR * upC/blockSize,
         tCSeq, SEQUENCE( 1, byC + 1 + blockSize ),  tRSeq, SEQUENCE( upCells + 1,, 0 ),  upSeq, SEQUENCE( upCells,, 0 ),
         hdr, IF( tCSeq <= byC,  INDEX( byHdr, , tCSeq ),
                 IF( tCSeq = byC + 1, attrTitle,
                     INDEX( upFields, 1, tCSeq - byC - 1 ) ) ),
         muxBody, INDEX( byBody, SEQUENCE( upCells, byC, 0 )/byC/upC*blockSize + 1, SEQUENCE( 1, byC ) ),
         muxAttr, INDEX( upAttr, MOD( SEQUENCE( upCells,, 0, blockSize ), upC ) + 1 ),
         muxValues, INDEX( upValues, SEQUENCE( upCells, blockSize, 0 )/upC+1, MOD(SEQUENCE( upCells, blockSize, 0 ),upC)+1),
         table, IF( tCSeq <= byC, muxBody,
                   IF( tCSeq = byC + 1, muxAttr,
                       INDEX( muxValues, upSeq + 1, tCSeq - byC - 1 ) ) ),
         IF( tRSeq = 0, hdr, INDEX( table, tRSeq, tCSeq) )  )

这需要 6 个变量：

• upValues - 将在块中逆透视的数据
• upHdr - 包含 PTHC 值的标题行
• upAttr - 将取消透视的属性，即月份行
• byBody - 将反转值的值主体，即 State 和 City 值
• byHdr - byBody 的标头（标题“State”和“City”）
• attrTitle - 将取消透视的属性的可选标题

在此图中可以更好地理解这些内容：

为了更容易理解，这里给出了测试数据和显示的结果：

上面的输出也可以说明：

红色文本是用于构造结果的内部变量。

该公式由 5 个部分组成，如下所示：

测量尺寸很明显 - 它只是参数化稍后将重复使用的变量。dmxR使用 upValues 或 byBody 行的 MIN 以防用户意外输入格式错误的值和 byBody，否则会导致无意义的输出。

构建序列创建三个用于索引输入和输出的序列：

• tCSeq（表列序列）是按列调整大小的序列，最终输出表将具有 byBody + 属性（月份）+ 值（块大小）列。
• tRSeq（表行序列）是一个按行序列，其大小适合最终输出表，该表将具有 dmxR*upC/blocksize + 1 (hdr) 行。
• upSeq(unpivot 序列) 是一个按行序列，其大小适合最终输出表，该表将具有 dmxR*upC/blocksize 行（无标题）。

创建数组组件使用上面的维度和序列来构造输出表的各个部分。

• hdr(header) 是带有标签（州和城市）、属性标题（月份）和字段名称 (PTHC) 的新标题。
• muxBody（多路复用 byBody）是跨 dmxR 行多路复用的 byBody 的重复。
• muxAttr(多路复用的 upAttr) 是跨 dmxR 行多路复用的 upAttr 的重复。
• mux值(多路复用的 upValues) 是按块重复，将具有 dmxR*upC/blocksize 行。

最后两行stitch零件在一起。第一的，table缝针muxBody, muxAttr and mux值在逐列积分中使用tCSeq以及使用行式多路复用upSeq.

只是因为它在心理上更容易（并且更容易测试），我将行式集成分开（使用tRSeq） 的hdr到table在最后一行。

使用 IF 语句拼接的另一种方法是使用 IFERROR(INDEX ，它会强制出现错误，然后用表的下一部分替换错误，但这很难测试和调试，即使它只是按行或按列. 把row-wise和column-wise组合起来，就是一个cauchemar。