☰

对许多列进行转置计数

tag-icon • tag-icon microsoft-excel vba macros countif transpose
对许多列进行转置计数

输入：

输入

我必须像这样获取每列中“1”和“0”的数量：

预期输出

Excel/宏中有什么方法可以做到这一点吗？

答案1

如果我们说列从 开始"D1"并打印到"A3"，它看起来可能像这样：

Sub countAndTranspose()
Dim section As Range, count As Range, entry As Range, sectionPrint As Range

Set section = Range("D1", Cells(1, Columns.count).End(xlToLeft)) 'All headers
For Each entry In section
    Set sectionPrint = Cells(Rows.count, 1).End(xlUp) 'Last row in "A"
    Set count = Range(Cells(entry.Row + 1, entry.Column), Cells(Rows.count, entry.Column).End(xlUp))
    sectionPrint.Offset(1).Value = entry.Value 'Print header below last row in A
    sectionPrint.Offset(1, 1).Value = WorksheetFunction.CountIf(count, 1) 'Print sum of 1
    sectionPrint.Offset(1, 2).Value = WorksheetFunction.CountIf(count, 0) 'Print sum of 0
Next entry

End Sub

我们首先定义列的范围，从"D1"行的最远端开始。
然后我们循环遍历这个范围，选择在哪里打印列名（A3 及以下应为 EMPTY）

在此处输入图片描述

答案2

Excel Power Query 解决方案

结果：
在此处输入图片描述

对于专业人士- 完整流程：

let
    Source = Excel.CurrentWorkbook(),
    #"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"OPERATION", "MEDIA", "IT"}, {"OPERATION", "MEDIA", "IT"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Content",{"Name"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Value", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Value", type text}}, "en-US")[Value]), "Value", "Count", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Attribute", "1", "0"})
in
    #"Replaced Value"

为他人- 详细步骤。

选择输入数据表（包括标题）。
插入 -> 表格

在此处输入图片描述

好的

在此处输入图片描述

点击“内容”旁边的双箭头
取消选中使用原始列名作为前缀

在此处输入图片描述

好的

右键单击“名称”列 -> 删除

选择整个表格
变换 -> 取消透视列

在此处输入图片描述

首页 -> 分组

在此处输入图片描述

好的

选择列“值”
转换 -> 将列透视
为值列选择“计数”

在此处输入图片描述

好的

在此处输入图片描述

选择整个表格
变换->替换值
填写如下：

在此处输入图片描述

好的

主页 -> 关闭并加载

答案3

在OPERATION、MEDIA和IT的列条目下方请输入两个公式：

COUNTIF ( A2:A10, 1 )以一
COUNTIF ( A2:A10, 0 )代表零。

复制 MEDIA 和 IT 下面的公式。

答案4

对于非 VBA 解决方案，如果您拥有 Excel 2016 或 Microsoft 365，则可以利用 LET。这也适用于 Mac 版 Excel（与 Power BI 不同）。

=LET( array, A1:E4,
       hdr, INDEX( array, 1, ),
       rSeqBody, SEQUENCE( ROWS( array ) - 1 ),
       cSeq, SEQUENCE( 1, COLUMNS( array ) ),
       body, INDEX( array, rSeqBody + 1, cSeq ),
       onesCount, MMULT( TRANSPOSE( SIGN( rSeqBody ) ), body ),
       t1s, IFERROR( INDEX( TRANSPOSE( hdr ), TRANSPOSE( cSeq ), {1,2} ), TRANSPOSE( onesCount ) ),
       t10s, IFERROR( INDEX( t1s, TRANSPOSE( cSeq ), {1,2,3} ), ROWS( body ) - TRANSPOSE( onesCount ) ),
       t10s )

这是一个示例应用程序： 截屏

其中输入是数组，包括带有字段名称的标题行，输出是溢出到下方单元格和右侧一列的动态数组。

怎么运行的

它首先获取字段名称并将它们放入名为人类发展报告。然后，它创建行和列序列，用于计算和塑造输出。然后，它创建一个包含 1 和 0 的数组，称为身体。然后按列对 1 求和个位计数使用矩阵乘法。

这不是在计算 1。它利用了它们可以相加的事实。如果它是一个符号，比如“x”，它就行不通了。同样，零也不被计算，而只是从 1 中计算出来。

为了达到效果，它拼接了 hdr（转置）到一数（转置）通过对转置 hdr 的列进行过度索引，以强制在每行的第 2 列中出现错误，然后使用 IFERROR 将 !REF# 错误替换为转置的 thoseCount。

重复此操作一次，以添加 0 的计数列，该列通过从行数中减去 1 的计数计算得出身体。

如果您想要 1COUNT 和 0COUNT 标题，也可以将其缝合上，但可能更容易将其以您想要的任何措辞写入工作表中。

不使用 LET 也能实现，但会很麻烦，调试起来很困难。LET 使操作更加清晰，并且不需要辅助单元。

相关内容