☰

多数の列の転置カウント

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 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 の列エントリの下に、次の 2 つの数式を入力してください。

COUNTIF ( A2:A10, 1 )1 を
COUNTIF ( A2:A10, 0 )0 に置き換えます。

MEDIA と IT の下の数式をコピーします。

答え4

VBA 以外のソリューションの場合、Excel 2016 または Microsoft 365 をお持ちであれば、LET を利用できます。これは、Excel for Mac でも機能します (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 列に広がる動的配列です。

使い方

まずフィールド名を取得し、それを配列に格納します。高解像度次に、出力を計算して整形するための行と列のシーケンスを作成します。次に、1と0を含む配列を作成します。体次に、列ごとに1を合計します。ワンカウント行列乗算を使用します。

これは 1 を数えるのではなく、合計できるという事実を利用しています。「x」のような記号の場合は機能しません。同様に、ゼロは数えられず、単に 1 から計算されます。

結果を提供するために、HDRをステッチします（転置）1カウントに（転置）転置された hdr の列をオーバーインデックスして各行の列 2 にエラーを強制し、次に IFERROR を使用して !REF# エラーを転置された onesCount に置き換えます。

これをもう一度繰り返して、0のカウント列を追加します。これは、1のカウントを行数から減算して計算されます。体。

1COUNT と 0COUNT のヘッダーが必要な場合は、これも縫い付けることもできますが、必要な文言をワークシートに書き込む方が簡単でしょう。

これは LET なしでも可能ですが、煩雑でデバッグが困難です。LET を使用すると明確になり、ヘルパー セルが不要になります。

関連情報