Transponierte Zählungen für viele Spalten

Question

Die Eingabe:

Ich muss die Anzahl der „1“ und „0“ in jeder Spalte folgendermaßen ermitteln:

Gibt es eine Möglichkeit, dies in Excel/Makro zu tun?

Answer 1

Wenn wir sagen, dass die Spalten bei beginnen "D1"und wir bis drucken "A3", könnte es ungefähr so ​​aussehen:

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

Wir beginnen mit der Definition unseres Spaltenbereichs, beginnend bei "D1"und bis zum äußersten Ende der Zeile.
Dann durchlaufen wir diesen Bereich und wählen aus, wo der Spaltenname gedruckt werden soll (A3 und unten sollten LEER sein)

Answer 2

Excel Power Query-Lösung

Ergebnis:

---

Für Profis- vollständiges Verfahren:

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"

---

Für andere- detaillierte Schritte.

Wählen Sie Ihre Eingabedatentabelle (einschließlich Überschriften) aus.
Einfügen -> Tabelle

OK

Klicken Sie auf die Doppelpfeile neben "Inhalt"
Deaktivieren SieUrsprünglichen Spaltennamen als Präfix verwenden

OK

Rechtsklick auf Spalte "Name" -> Entfernen

Ganze Tabelle auswählen
Transformieren -> Spalten entpivotieren

Home -> Gruppieren nach

OK

Wählen Sie die Spalte „Wert“ aus.
Transformieren Sie -> Pivot-Spalte.
Wählen Sie als Wertespalte „Anzahl“ aus.

OK

Ganze Tabelle auswählen.
Transformieren -> Werte ersetzen.
So ausfüllen:

OK

Home -> Schließen & Laden

Answer 3

Unterhalb der Spalteneinträge BETRIEB, MEDIEN und IT tragen Sie bitte zwei Formeln ein:

COUNTIF ( A2:A10, 1 )für Einsen
COUNTIF ( A2:A10, 0 )für Nullen.

Kopieren Sie die Formeln unter MEDIA und IT.

Answer 4

Für eine Nicht-VBA-Lösung können Sie LET nutzen, wenn Sie Excel 2016 oder Microsoft 365 haben. Dies funktioniert auch mit Excel für Mac (im Gegensatz zu 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 )

Dies ist eine Beispielanwendung:

Dabei ist die Eingabe das Array, einschließlich der Kopfzeile mit den Feldnamen, und die Ausgabe ist ein dynamisches Array, das in die Zellen darunter und eine Spalte rechts darüber übergeht.

Wie es funktioniert

Zuerst werden die Feldnamen abgerufen und in ein Array mit dem Namen eingefügt:hdr. Anschließend werden Zeilen- und Spaltensequenzen zum Berechnen und Gestalten der Ausgaben erstellt. Anschließend wird ein Array mit den Einsen und Nullen erstellt, dasKörper. Dann werden die 1en pro Spalte summiert indiejenigenAnzahlunter Verwendung einer Matrizenmultiplikation.

Dabei werden die Einsen nicht gezählt. Es wird die Tatsache ausgenutzt, dass sie summiert werden können. Wenn es ein Symbol wie „x“ wäre, würde es nicht funktionieren. Ebenso werden die Nullen nicht gezählt, sondern einfach aus den Einsen berechnet.

Um das Ergebnis zu liefern, wird das HDR zusammengefügt.(transponiert)auf die EinenCount(transponiert)durch Überindizierung der Spalten des transponierten HDR, um Fehler in Spalte 2 jeder Zeile zu erzwingen, dann wird IFERROR verwendet, um die !REF#-Fehler durch die transponierten Fehler zu ersetzen.

Dies wird noch einmal wiederholt, um eine Spalte mit der Anzahl der Nullen hinzuzufügen. Diese wird berechnet, indem die Anzahl der Einsen von der Anzahl der Zeilen auf derKörper.

Wenn Sie die Überschriften „1COUNT“ und „0COUNT“ möchten, können Sie diese auch annähen, aber es ist wahrscheinlich einfacher, sie einfach mit dem gewünschten Wortlaut in das Arbeitsblatt zu schreiben.

Dies ist auch ohne LET möglich, aber chaotisch und schwer zu debuggen. LET schafft Klarheit und macht Hilfszellen überflüssig.