如何汇总行和列数据？

Question

考虑以下数据：

第一列有很多重复的数字（类别代码）。数据以这种通用格式呈现给我，但行数和列数更多。行数各不相同，但通常有 40 列左右。我只需要列1、2和。只有列或只有列6中才会有条目，绝不会同时有，绝不会两者都有。我需要的只是列标题。45

因此，我想重新配置数据并显示它，Sheet 2使其看起来像下面这样：

我不想使用宏或 VBA，因为公司安全设置可能会很麻烦。是否有一个单行命令可以在单元格 A1 中输入，从而产生我需要的输出？

Answer 1

如果您的数据表名为Table7：（
使用表格结构意味着当你在数据表中添加/删除行时，引用将自动调整）

您可以在单个单元格中输入以下公式，结果将SPILL给出您想要的输出：

=HSTACK(
    CHOOSECOLS(
        Table7,
        1,
        2,
        6
    ),
    BYROW(
        Table7[Fruit],
        LAMBDA(arr,
            IF(
                arr = "",
                "Vegetable",
                "Fruit"
            )
        )
    )
)

• CHOOSECOLS返回输出所需的列
• 该BYROW函数返回另一列，其中包含项目类型，具体取决于单元格是否Fruit为空
• HSTACK将两者合并为一个数据表输出

编辑：
对于可以自动调整多个中间列的公式，请尝试以下公式
*该公式将保留列1,2 and the last column并返回列标题4-penultimate column

=LET(
    firstCol, 4,
    numCols, COLUMNS(Table7) - 4,
    cols, SEQUENCE(, numCols, firstCol),
    hdrs, INDEX(Table7[#Headers], , cols),
    HSTACK(
        CHOOSECOLS(Table7, 1, 2, COLUMNS(Table7)),
        BYROW(
            LEN(CHOOSECOLS(Table7, cols)) > 0,
            LAMBDA(arr, XLOOKUP(TRUE, arr, hdrs))
        )
    )
)

再次，您可能需要将结构化引用更改为常规单元格引用。

• LEN(CHOOSECOLS(Table7, cols)) > 0返回一个二维数组，TRUE/FALSE取决于单元格中是否有内容。
• BYROW将匹配TRUE相应列标题/名称的条目

Answer 2

调整以下内容：

1. CHOOSECOLS使用或函数过滤表并仅选择某些行FILTER。

=CHOOSECOLS(A2:F6,{1,2,6})

或者

=FILTER(FILTER(A2:F6,A2:A6<>""),{1,1,0,0,0,1})

2. 找到与前两列匹配的列的索引。然后检查水果值是否不为空。如果是，则写入“Fruit”。如果不是，则写入“Vegetable”。

=IF(INDEX($D$2:$D$6,MATCH(A10&B10,($A$2:$A$6)&($B$2:$B$6),0))<>"",$D$1,$E$1)

将公式向下拖动以填充其他行。