☰

許多列的轉置計數

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及以下應為空）

在此輸入影像描述

答案2

Excel 電力查詢解決方案

結果：
在此輸入影像描述

對於專業人士- 完整程序：

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"

為他人- 詳細步驟。

選擇您的輸入資料表（包括標題）。
插入->表格

在此輸入影像描述

好的

在此輸入影像描述

點擊“內容”旁邊的雙箭頭
取消選中使用原始列名作為前綴

在此輸入影像描述

好的

右鍵單擊列“名稱”-> 刪除

選擇整個表
“轉換”->“逆透視列”

在此輸入影像描述

首頁 -> 分組依據

在此輸入影像描述

好的

選擇列“值”
轉換 -> 將列透視
為值列選擇“計數”

在此輸入影像描述

好的

在此輸入影像描述

選擇整個表
Transform -> Replace Values
填寫如下：

在此輸入影像描述

好的

首頁 -> 關閉並加載

答案3

在「操作」、「媒體」和「IT」列條目下方，請輸入兩個公式：

COUNTIF ( A2:A10, 1 )為 1
COUNTIF ( A2:A10, 0 )為 0。

複製 MEDIA 和 IT 下面的公式。

答案4

對於非 VBA 解決方案，如果您有 Excel 2016 或 Microsoft 365，則可以利用 LET。

=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# 錯誤替換為轉置的 onesCount。

再重複一次以新增 0 的計數列，該列是透過從行數中減去 1 的計數來計算的。身體。

如果您想要 1COUNT 和 0COUNT 標題，您也可以將其縫合，但用您想要的任何措辭將其寫入工作表可能會更容易。

不用 LET 也是可以的，但是比較混亂且難以調試。 LET 提供了清晰度並消除了對輔助細胞的需求。

相關內容