Live-Forum - Die aktuellen Beiträge
Datum
Titel
13.10.2025 19:31:31
VBA Formel mit Bezug auf Überschrift anstatt Spalte
13.10.2025 15:10:17
Überlappende Zeiträume über mehrere Tabellen mit leeren Zell
13.10.2025 13:34:56
PQ Abfrage mit verschiedenen Werten ausführen + Protokoll
13.10.2025 12:24:02
Zählen in Abhängigkeit einer Jahreszahl
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1616to1620
1616to1620
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zählen von Kombinationen aus einer Tabelle

Forumthread: Zählen von Kombinationen aus einer Tabelle

Zählen von Kombinationen aus einer Tabelle
26.03.2018 14:03:38
Kombinationen
Hallo,
Ich habe eine Tabelle mit 50 Spalten und 500 Zeilen. Jede Spalte entspricht einem Kriterium. Ich möchte die Anzahl der Kombinationen von 3 Kriterien zählen. Z.B.: Wie oft kommt die Kombination aus Spalte A=1, Spalte B=3 und Spalte C=5 in den Zeilen 3 bis 500 vor. Die Kriterien werden später auch Texte oder Namen enthalten.
Ich habe eine Beispiel erstellt, der die Fragestellung hoffentlich besser erläutert.
https://www.herber.de/bbs/user/120678.xlsx
Bei der Masse komme ich mit ZÄHLENWENN() nicht weit.
Anzeige

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zählen von Kombinationen aus einer Tabelle
26.03.2018 14:16:53
Kombinationen
Hallo
so...

Eingabeseite
 ABCDEFGHIJKLMNOP
14z.B.:               
15Kriterium 4Kriterium 3Kriterium 2Anzahl
168120
172981

 verbundene Zellen 
A15:B15
C15:E15
F15:I15
J15: P15
A16:B16
C16:E16
F16:I16
J16: P16
A17:B17
C17:E17
F17:I17
J17: P17

verwendete Formeln
Zelle Formel Bereich N/A
J16:J17=ZÄHLENWENNS(Datenbank!D: D;A16;Datenbank!C:C;C16;Datenbank!B:B;F16)  
http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://Hajo-Excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 25.14 einschl. 64 Bit


LG UweD
Anzeige
AW: Zählen von Kombinationen aus einer Tabelle
26.03.2018 14:51:08
Kombinationen
Danke für deine Antwort.
Leider habe ich vergessen zu erwähnen, dass ich ca. 100 unterschiedliche Werte je Kriterien habe. Sprich eine Kombination aus 3 Kriterien mit jeweils 100 Werten. Das mit ZÄHLENWENN zu lösen wäre extrem arbeitsreich.
AW: dyn. mit SUMME(), MMULT() und MTRANS() ...
26.03.2018 15:02:57
...
Hallo Jan oder Christian ;-)
... und wegen letzterer Funktion bedarf es einer klassischen Matrixformel und diese kannst Du nicht in einer verbundenen Zelle aufstellen. Deshalb hebe zumindest diese in der Ergebnisspalte J auf.
Im Beispiel für drei Kriterien, die in Zeile 15 frei wählbar sein können aus Datenbank!3:3
in J16:
{=WENN(ANZAHL(A16:I16)=0;"";SUMME(INDEX(--(MMULT((Datenbank!A$3:CZ$3=A$15)*
(Datenbank!A$4:CZ$500=A16)+(Datenbank!A$3:CZ$3=C$15)*(Datenbank!A$4:CZ$500=C16)+
(Datenbank!A$3:CZ$3=F$15)*(Datenbank!A$4:CZ$500=F16);MTRANS(SPALTE(A1:CZ1)^0))=3);)))}
und nach unten kopieren.
Bei mehr als drei Kriterien die Formel analog erweitern.
Gruß Werner
.. , - ...
Anzeige
AW: dyn. mit SUMME(), MMULT() und MTRANS() ...
26.03.2018 15:30:41
Jan
Vielen Dank das funktioniert richtig gut. Auch wenn ich die Formel noch nicht nachvollziehen kann.
Gibt es eine einfache Möglichkeit bei z.B.: J16=0 die Zeile J16 auszublenden? Ich habe ein Makro das diese Aufgabe übernimmt. In dieser Excel sind nur soviele Makros, dass alles langsam wird.
Vielen Dank
Anzeige
AW: ausblenden nur über VBA; evtl. bed. Form.? owT
26.03.2018 15:45:51
...
Gruß Werner
.. , - ...
AW: ausblenden nur über VBA; evtl. bed. Form.? owT
26.03.2018 16:25:45
Jan
Hallo
Ich habe noch ein Problem bei der Implementierung in die richtige Excel. Obwohl er zumindestens 1 Treffer haben müsste, schreibt er eine leere Zelle, wegen WENN(ANZAHL(H7:J7)=0; "" ;
Schreibfehler kann ich ausschließen.
Was muss ich in diesem Teil
MTRANS(SPALTE(A1:CZ1)^0))=3)
anpassen? Der Rest scheint richtig angepasst zu sein. Wenn ich später 6 Kriterien hätte müsste ich dann
MTRANS(SPALTE(A1:CZ1)^0))=6)
schreiben?
Anzeige
AW: zu 1.) H7;J7 sind Zahlen? zu 2.) ja owT
26.03.2018 16:39:20
...
Gruß Werner
.. , - ...
AW: zu 1.) H7;J7 sind Zahlen? zu 2.) ja owT
26.03.2018 17:11:03
Jan
Nein, H7 und J7 ist ein String (H7=.AKH J7=DN17). In der Beispiel Excel mit deinem Code funktioniert dies allerdings einwandfrei.
AW: dann nutze ANZAHL2() anstelle ANZAHL() owT
26.03.2018 17:16:17
...
Gruß Werner
.. , - ...
Anzeige
AW: dann nutze ANZAHL2() anstelle ANZAHL() owT
27.03.2018 09:23:53
Jan
Mit ANZAHL2() kam leider nur #NV
Wärst du so freundlich mal meine File zu checken? Ich bin gerade etwas ratlos. Ist sicher irgendein Bezeichnungsfehler.
https://www.herber.de/bbs/user/120691.xlsm
AW: ANZAHL2() ergibt kein #NV ...
27.03.2018 10:20:33
...
Hallo Jan,
... und da ich mich nicht mit VBA beschäftige, lade ich mir auch keine XLSM-Datei aus dem Forum.
Gruß Werner
.. , - ...
Anzeige
AW: und noch ein paar Erläuterungen hierzu? owT
27.03.2018 11:36:49
...
Gruß Werner
.. , - ...
AW: und noch ein paar Erläuterungen hierzu? owT
27.03.2018 12:17:45
Jan
Gerne
R&I Device list! entspricht der Datenbank! (Kriterien=A4:CC4 / Daten=A5:CC5)
Kriterium I= Freies_Suchkriterium_1 [R&I...!BG4]
Kriterium II= Dimension [R&I...!D4]
Kriterium III= Medium [R&I...!P4]
Eingabe und Ausgabe auf Folie: Controlling Bauteile
Eingabe: [H7:Jxxx]
Ausgabe: Q8 bzw Qxxx
Brauchst du noch weitere Informationen?
Anzeige
AW: die Formel ist nicht korrekt angepasst ...
27.03.2018 17:43:51
...
Hallo Jan,
... ersten hast Du nicht ANZAHL2() eingesetzt sondern =WENN(ANZAHL(H8:J8)=0;... zu stehen. Da würdest Du bei Dir stets als Ergebnis "nope" erhalten. Und da Du die Auswertung Deiner Kriterien nicht in Spalte A sondern in B beginnst, muss es in der Formel auch ... MTRANS(SPALTE(B1:CZ1)^0) ... lauten.
Außerdem beinhaltet Deine Datentabelle jede Menge #BEZUG!-Fehler.
Gruß Werner
.. , - ...
Anzeige
AW: die Formel ist nicht korrekt angepasst ...
28.03.2018 16:38:40
Jan
Die Bezüge fehlen, weil die Datei 8MB groß ist und ich die hier nicht hochladen konnte. Daher habe ich nahe zu alles rausgelöscht.
Ich habe immer noch einen Bezugfehler drinne, ich raff es nicht sorry. Mit Anzahl2 hatte ich es vorher schon versucht, habe aber nur ein #NV bekommen.
=WENN(ANZAHL2(H8:J8)=0;"Fehler";SUMME(INDEX(--(MMULT(('R&I Device list'!B$5:CZ$5=H$7) *('R&I Device list'!B$6:CZ$500=H8)+( 'R&I Device list'!B$5:CZ$5=I$7) *( 'R&I Device list'!B$6:CZ$500=I8)+( 'R&I Device list'!B$5:CZ$5=J$7) *( 'R&I Device list'!B$6:CZ$500=J8);MTRANS(SPALTE(B1:CZ1)^0))=3);)))
Anzeige
AW: dies kann ich so nicht nachvollziehen ....
28.03.2018 19:49:31
...
Hallo Jan,
... in der von Dir eingestellten Datei.
Nachdem ich die BEZUGS-Fehler-Formeln gelöscht habe erhalte ich mit folgender Formel:
{=WENN(ANZAHL2(H8:J8)=0;"nope";SUMME(INDEX(--(MMULT(('R&I Device list'!B$5:CZ$5=H$7)*
('R&I Device list'!B$6:CZ$500=H8)+( 'R&I Device list'!B$5:CZ$5=I$7)*
( 'R&I Device list'!B$6:CZ$500=I8)+( 'R&I Device list'!B$5:CZ$5=J$7)*
( 'R&I Device list'!B$6:CZ$500=J8);MTRANS(SPALTE(B1:CZ1)^0))=3);)))}
keine Fehlermeldung. Demzufolge wäre zu vermuten, dass in Deiner Datentabelle zumindest ein #NV-Fehler in der Formel verursacht.
Gruß Werner
.. , - ...
Anzeige
AW: dies kann ich so nicht nachvollziehen ....
29.03.2018 09:57:15
Jan
Vielen Dank
Ja ohne Bezugsfehler sagt er jetzt bei mir 0, obwohl er was finden müsste. Aber dann funktioniert die Formel zumindestens schon mal. Nach Ostern schau ich mir das nochmal genauer an.
Ich wünsche dir Frohe Ostern und ein paar schöne freie Tage.
AW: in Deiner bisher eingestellten Datei ...
29.03.2018 14:42:30
...
Hallo Jan,
... kann als Ergebnis nur eine 0 ermittelt werden.
Wenn Du weiter Fragen hierzu hast und diese erst nach Ostern stellst, könnte dieser thread evtl. bereits deaktiviert im Archiv sein.
Du müsstest dann einen neuen thread aufmachen und dort im thread im Betreff z.B. folgendes schreiben: "@ neopa; Fortsetzungsthread ..." und in Deinem Text zusätzlich den Link zu diesem Archivthread: https://www.herber.de/forum/archiv/1616to1620/t1616411.htm mit angeben, damit man sich schneller orientieren kann.
Gruß Werner
.. , - ...
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Zählen von Kombinationen in Excel-Tabellen

Schritt-für-Schritt-Anleitung

Um die Anzahl von Kombinationen aus mehreren Kriterien in einer großen Excel-Tabelle zu zählen, kannst du die Funktion ZÄHLENWENNS() oder Matrixformeln verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

  1. Tabellenstruktur vorbereiten: Stelle sicher, dass deine Tabelle die relevanten Daten in den Spalten enthält. In diesem Beispiel arbeiten wir mit einer Tabelle, die 50 Spalten und 500 Zeilen hat.

  2. Formel für die Kombinationen verwenden:

    • Wenn du die Kombination aus Spalte A, B und C zählen möchtest, kannst du eine Formel wie folgt verwenden: 
      =ZÄHLENWENNS(Datenbank!A:A;A1;Datenbank!B:B;B1;Datenbank!C:C;C1)
    • Diese Formel zählt die Anzahl der Zeilen, in denen die Werte in den Spalten A, B und C den Werten in der ersten Zeile entsprechen.

  3. Matrixformel anwenden (bei mehr Kriterien):

    • Wenn du mehr als drei Kriterien hast, kannst du eine Matrixformel verwenden: 
      {=WENN(ANZAHL(A1:C1)=0;"";SUMME(INDEX(--(MMULT((Datenbank!A$3:CZ$3=A$15)*(Datenbank!A$4:CZ$500=A1)+(Datenbank!A$3:CZ$3=B$15)*(Datenbank!A$4:CZ$500=B1)+(Datenbank!A$3:CZ$3=C$15)*(Datenbank!A$4:CZ$500=C1); MTRANS(SPALTE(A1:CZ1)^0))=3);)))}
    • Diese Formel muss als Matrix eingegeben werden (Strg + Shift + Enter).

  4. Nach unten kopieren: Ziehe die Formel nach unten, um die Anzahl der Kombinationen für alle Zeilen zu zählen.

Häufige Fehler und Lösungen

  • Problem: Formel ergibt #NV oder #BEZUG! Fehler.

    • Lösung: Überprüfe die Zellbezüge in deiner Formel. Achte darauf, dass sie korrekt sind und keine Daten fehlen.

  • Problem: Ergebnis ist 0, obwohl es Treffer geben sollte.

    • Lösung: Stelle sicher, dass die Werte in den Zellen genau übereinstimmen (keine zusätzlichen Leerzeichen oder unterschiedliche Formate).

Alternative Methoden

  • Pivot-Tabellen: Eine Pivot-Tabelle kann ebenfalls verwendet werden, um Kombinationen zu zählen. Dies ist besonders nützlich bei großen Datenmengen und ermöglicht eine schnelle Analyse.

  • VBA-Makros: Wenn du mit VBA vertraut bist, kannst du ein Makro schreiben, das die Zählung automatisiert.

Praktische Beispiele

  1. Beispiel 1: Angenommen, du möchtest wissen, wie oft die Kombination (A=1, B=3, C=5) vorkommt:

    =ZÄHLENWENNS(Datenbank!A:A;1;Datenbank!B:B;3;Datenbank!C:C;5)

  2. Beispiel 2: Bei mehr als 100 Werten pro Kriterium könnte eine Matrixformel wie folgt aussehen:

    {=WENN(ANZAHL(A1:C1)=0;"";SUMME(INDEX(--(MMULT((Datenbank!A$3:CZ$3=A$15)*(Datenbank!A$4:CZ$500=A1)+(Datenbank!A$3:CZ$3=B$15)*(Datenbank!A$4:CZ$500=B1)+(Datenbank!A$3:CZ$3=C$15)*(Datenbank!A$4:CZ$500=C1); MTRANS(SPALTE(A1:CZ1)^0))=3);)))}

Tipps für Profis

  • Verwende ANZAHL2() anstelle von ANZAHL(), wenn du sicherstellen möchtest, dass auch nicht-leere Zellen gezählt werden. Dies kann hilfreich sein, um leere Zellen in deinen Berechnungen zu vermeiden.

  • Vermeide verbundene Zellen, wenn du mit Matrixformeln arbeitest, da dies zu Problemen führen kann.

FAQ: Häufige Fragen

1. Wie kann ich die Formel für mehr als drei Kriterien anpassen?
Du kannst die Formel erweitern, indem du weitere Bedingungen hinzufügst, und die Anzahl in der MTRANS-Funktion entsprechend änderst.

2. Was tun, wenn die Formel nicht funktioniert?
Überprüfe alle Zellbezüge und stelle sicher, dass die Daten in den Zellen korrekt formatiert sind. Achte auch auf eventuelle Schreibfehler in den Kriterien.

3. Gibt es eine einfachere Methode, um Kombinationen zu zählen?
Ja, die Verwendung von Pivot-Tabellen kann oft eine schnellere und benutzerfreundlichere Methode zur Analyse von Daten in Excel sein.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige