使用单个公式进行高级逆透视[重复]

该TEXTSPLIT(TEXTJOIN())方法适用于较小的表，其中生成的字符串不会超过 32,767 个字符。

对于任何大小的表，另一种方法是使用HSTACK基于TOCOL数组操作技术，该技术将根据表的复杂性而变化。

示例 1：要保留的单行标签字段

=LET(
    tbl, Table3[#All],
    vals, DROP(tbl, 1, 1),
    rowLabels, DROP(TAKE(tbl,, 1), 1),
    colLabels, DROP(TAKE(tbl, 1),, 1),
    HSTACK(
        TOCOL(IF(SEQUENCE(, COLUMNS(vals)), rowLabels)),
        TOCOL(IF(SEQUENCE(ROWS(vals)), colLabels)),
        TOCOL(vals)
    )
)

示例 2：要保留的多个行标签字段

=LET(
    tbl, Table3[#All],
    vals, DROP(tbl, 1, 2),
    rowLabels, DROP(TAKE(tbl,, 2), 1),
    colLabels, DROP(TAKE(tbl, 1),, 2),
    rowId, SEQUENCE(ROWS(vals)),
    HSTACK(
        CHOOSEROWS(rowLabels, TOCOL(IF(SEQUENCE(, COLUMNS(vals)), rowId))),
        TOCOL(IF(rowId, colLabels)),
        TOCOL(vals)
    )
)

示例 3：要逆透视的多个值字段

=LET(
    tbl, Table3[#All],
    vals, DROP(tbl, 1, 1),
    rowLabels, DROP(TAKE(tbl,, 1), 1),
    colLabels, UNIQUE(LEFT(DROP(TAKE(tbl, 1),, 1), 4), TRUE),
    HSTACK(
        TOCOL(IF(SEQUENCE(, COLUMNS(vals)/2), rowLabels)),
        TOCOL(IF(SEQUENCE(ROWS(vals)), colLabels)),
        WRAPROWS(TOCOL(vals), 2)
    )
)

这些只是 3 个基本示例。不过，可以进行修改来处理几乎任何情况。

过去，我曾使用一些相当复杂的INDEX/MATCH解决方案来完成此任务，但令我惊讶的是（近年来我并没有真正经常使用 Excel）Excel 365 使这变得非常容易。

一般解决方案非常简单且可读（特别是在使用命名表和范围时）。例如，对于一个基本表，例如：

该公式实际上非常简单且易于遵循：

 =TEXTSPLIT(
      TEXTJOIN(",",TRUE,
           TOCOL(Table3[Company]&"|"&Table3[[#Headers],
           [2020]:[2023]]&"|"&Table3[[2020]:[2023]])),
           "|",",")

真正让我印象深刻的是这个新LET功能对我来说是多么有用。例如，当对具有稍有不同的输入（带空格）和稍有不同的所需输出的表进行逆透视时，使用LET使公式更容易遵循：

 =LET(
      array,
      TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(Table2[COMPANY]&"|"&Table2[[Service 1]:[Service 5]])),"|",","),
      FILTER(array,CHOOSECOLS(array,2)<>""))

我在网上没有找到很多此类不可旋转公式的示例，因此希望其他人也会发现它有用。

以下是使用动态 Excel 公式进行逆透视的一种方法，适用于MS365

•方法1概述的方法是动态的，它随着行和列扩展的变化而爆炸：

• 单元格中使用的公式A23

=LET(
     _Data, Table3[#All],
     _Companies, TAKE(_Data,-(ROWS(_Data)-1),1),
     _Years, TAKE(_Data,1,-(COLUMNS(_Data)-1)),
     _Values, DROP(_Data,1,1),
     _Answer, HSTACK(
            TOCOL(IFS(_Values>0,_Companies),2),
            TOCOL(IFS(_Values>0,_Years),2),
            TOCOL(_Values,3)),
     VSTACK({"COMPANY","YEAR","VALUE"},_Answer))

_{注意：使用相同的算法可以简单地使上面的公式变得更短，但它不会是动态的，即添加行时它会扩展，但增加列时不会扩展，可以尝试，上面的公式将立即扩展或折叠，如果实际来源发生变化。另一种方法，概述如下}

•方法2：

=HSTACK(TOCOL(IFS(Table3[[2020]:[2023]]>0,Table3[COMPANY]),2),
        TOCOL(IFS(Table3[[2020]:[2023]]>0,Table3[[#Headers],[2020]:[2023]]),2),
        TOCOL(Table3[[2020]:[2023]],3))

变体选项 1：另一种方法是使用自定义来编写第一个方法LAMBDA()来为每个方法运行，其中TOCOL()&IFS()不重复使用，这是动态的：

=LET(
     _Data, Table3[#All],
     _Companies, TAKE(_Data,-(ROWS(_Data)-1),1),
     _Years, TAKE(_Data,1,-(COLUMNS(_Data)-1)),
     _Values, DROP(_Data,1,1),
     _CustomFx, LAMBDA(α,δ, TOCOL(IFS(α>0,δ),3)),
     _Answer, HSTACK(
            _CustomFx(_Values,_Years),
            _CustomFx(_Values,_Companies),
            _CustomFx(_Values,_Values)),
     VSTACK({"COMPANY","YEAR","VALUE"},_Answer))

变体选项 2：如果您想应用与第二种方法相同的算法，也可以尝试这种方式，这不是动态的：

=LET(
     _Data, Table3[#All],
     _Companies, Table3[COMPANY],
     _Years, Table3[[#Headers],[2020]:[2023]],
     _Values, Table3[[2020]:[2023]],
     _CustomFx, LAMBDA(α,δ, TOCOL(IFS(α>0,δ),3)),
     _Answer, HSTACK(
            _CustomFx(_Values,_Years),
            _CustomFx(_Values,_Companies),
            _CustomFx(_Values,_Values)),
     VSTACK({"COMPANY","YEAR","VALUE"},_Answer))

对于第二个示例数据集，您可以尝试以下方式：

=LET(
     _Data, Table2[#All],
     _Companies, TAKE(_Data,-(ROWS(_Data)-1),1),
     _Values, DROP(_Data,1,1),
     _Answer, HSTACK(
            TOCOL(IFS(_Values>0,_Companies),2),
            TOCOL(_Values,3)),
     VSTACK({"COMPANY","SERVICE"},_Answer))

_{NOTE: Using TEXTJOIN() function when unpivoting data, I don't think is a rock-solid method, because the said function itself has some limitations, yes one can say every functions has their limitations, but all depends on the data one has. Still, I won't be using TEXTJOIN() function or ARRAYTOTEXT() or CONCAT() as per MSFT documentations --> If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN() returns the #VALUE! error, clearly, it is bit risky to use. See the cell limit mentioned here, works with the whole array concept as it is taken into one, it will certainly return an error, as the array formula seats in one cell and 溢出到多行/列。}