Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.10.2025 19:34:56
Eine Zelle in 3 Spalten teilen
20.10.2025 19:24:08
Excel Form befüllen
20.10.2025 19:03:51
wenn istleer funktioniert nicht wegen formel
20.10.2025 13:56:55
Wenn Funktion mit Xverweis oder ähnlich
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
1684to1688
1684to1688
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zählenwenn bei 250.000 Zeilen zu langsam

Forumthread: Zählenwenn bei 250.000 Zeilen zu langsam

Zählenwenn bei 250.000 Zeilen zu langsam
04.04.2019 23:18:01
Max
Abend ihr Excel-Profis.
Ich habe folgendes Problem:
Ich will über die Zählenwenn-Formel herausfinden, ob die Werte in Arbeitsblatt1 in Arbeitsblatt2 vorkommen. Es kann aich sein, dass die Werte mehr als 1x vorkommen.
In Arbeitsblatt1 Spalte A sind ca. 235.000 Zeilen/Daten und in Arbeitsblatt2 Spalte A ca. 250.000 Zeilen/Daten. Mit der Zählenwenn-Formel dauert die die Berechnung mehr als 4 Stunden.
Ich habe auch versucht das ganze über VBA mit Schleifen oder der Application.WorksheetFunction.CountIf schneller werden zu lassen, leider vergeblich.
Mit Funktionen kenne ich mich zu wenig aus.
Hat jemand eine zündende Idee für mich?
Besten Dank.
Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zählenwenn bei 250.000 Zeilen zu langsam
05.04.2019 00:38:55
Daniel
Hi
wenns nur darauf ankommt, ob ein Wert in einer anderen Liste vorkommt, dann gehts recht zügig per Formel so:
1. die zu durchsuchende Liste nach der Suchspalte aufsteigend sortieren
2. das vorhanden sein mit dieser Formel feststellen: =SVerweis(Suchbegriff;Suchspalte;1;1)=Suchbegriff
bei Wahr kommt der Suchbegriff in der durchsuchten Spalte vor, bei Falsch nicht.
wenn die Anzahlen verglichen werden sollen, bei dieser Datenmenge am besten mit der Pivottabelle, hierzu folgende Schritte zur vorbereitung:
1. in beiden Tabellen eine Hilfsspalte einfügen, welche die Herkunft der Daten kennzeichnet (Tabelle1, Tabelle2)
2. beide Tabellen in eine Tabelle untereinander kopieren.
3. Pivottabellenauswertung anlegen. Die Suchspalte nach Zeilen, die Quelle (Tabelle1/Tabelle2) nach Spalten und dann nach Suchspalte mit "Anzahl" auswerten.
Ergbnis ist eine Liste, in der jeder Suchbegriff 1x vorkommt und anzeigt, wie oft dieser Suchbegriff in jeder der beiden Tabellen vorkommt.
Gruß Daniel
Anzeige
AW: Zählenwenn bei 250.000 Zeilen zu langsam
05.04.2019 11:19:32
Günther
Moin,
mit Power Query sollte das ERHEBLICH schneller gehen. Wobei mir nicht klar ist, wie sich das Ergebnis darstellen soll. - Wie so oft im Foren-Leben würde eine kleine Muster-xlsx mit Wunschergebnis und Helfern helfen.
Gruß
Günther
AW: Zählenwenn bei 250.000 Zeilen zu langsam
05.04.2019 17:59:23
onur
Pste mal die (Beispiels-) Datei.
AW: Zählenwenn bei 250.000 Zeilen zu langsam
06.04.2019 14:59:19
Max
Wenn ich am Montag auf der Arbeit bin poste ich mal eine Beispiel Datei.
Anzeige
AW: Zählenwenn bei 250.000 Zeilen zu langsam
06.04.2019 15:27:07
Günther
Möglichst eine *.xlsx, *.xlsm/*.xlsb werden von verschiedenen Helfern aus gutem Grund nicht heruntergeladen. Und mit Wunschergebnis, bitte...
Gruß
Günther
AW: Zählenwenn bei 250.000 Zeilen zu langsam
08.04.2019 09:10:34
Max
Anbei die Datei. Da ich nur max. 300 KB hochladen kann, habe ich Datei massiv gekürzt.
Wie gesagt: Tabelle N hat ca. 235.000 Zeilen, die mit Tabelle Q (ca. 245.000 Zeilen) abgeglichen werden müssen. Ich muss wissen wie oft die Werte in Tabelle N in Tabelle Q stehen, um später mit den Zahlen weiter arbeiten zu können.
https://www.herber.de/bbs/user/129005.xlsx
Anzeige
AW: Zählenwenn bei 250.000 Zeilen zu langsam
08.04.2019 18:10:31
onur
Sind 15,84 sec schnell genug ? (Wird mit richtigen Daten etwas länger brauchen) - Wer sagt denn, dass VBA langsamer ist als EXCEL-Formeln ?
Deine Beispielsdatei ist für den A.... - es sind ja fast keine Übereinstimmungen drin.
https://www.herber.de/bbs/user/129021.xlsm
Anzeige
AW: Zählenwenn bei 250.000 Zeilen zu langsam
10.04.2019 11:55:29
Max
Sorry für die verspätete Antwort, musste den Code erst angleichen und testen.
270 Sekunden = 4,5 Minuten anstatt 5,5 Stunden für ca. 250.000 Datensätze ... WAHNSINN.
Und wieder was sehr nützliches dazu gelernt.
Danke dir vielmals, Onur.
AW: Zählenwenn bei 250.000 Zeilen zu langsam
10.04.2019 12:32:56
onur
Gerne!
Die Zeile 
w = w

kann natürlich weg - Testmüll.
Anzeige
AW: Zählenwenn bei 250.000 Zeilen zu langsam
10.04.2019 12:36:48
Günther
Moin Max,
mit Power Query könntest du wahrscheinlich noch mehr Zeit rausholen. Ich habe deine Daten mal erweitert und probiert, wie das zeitliche Verhalten ist...
Bei Bedarf lege ich die "große" Datei für einige Tage in meine Cloud und gebe dir/euch den Link.
Gruß
Günther
;
Anzeige
Anzeige

Infobox / Tutorial

Zählenwenn bei großen Datenmengen effizient nutzen

Schritt-für-Schritt-Anleitung

Um die Zählenwenn-Funktion bei großen Datenmengen effizient zu nutzen, kannst Du folgende Schritte befolgen:

  1. Daten vorbereiten: Stelle sicher, dass Deine Daten in den Arbeitsblättern gut strukturiert sind. In unserem Fall haben wir zwei Arbeitsblätter: Arbeitsblatt1 (ca. 235.000 Zeilen) und Arbeitsblatt2 (ca. 250.000 Zeilen).

  2. Zählenwenn-Formel anwenden: Du kannst die Zählenwenn-Formel wie folgt einsetzen:

    =ZÄHLENWENN(Arbeitsblatt2!A:A; Arbeitsblatt1!A1)

    Diese Formel wird die Anzahl der Vorkommen des Wertes in Zelle A1 von Arbeitsblatt1 in Arbeitsblatt2 zählen.

  3. Verwendung von SVerweis: Alternativ kannst Du die SVerweis-Funktion verwenden, um zu überprüfen, ob ein Wert vorhanden ist:

    =SVERWEIS(A1; Arbeitsblatt2!A:A; 1; FALSCH)

    Diese Methode ist in großen Datenmengen oft schneller.

  4. Pivot-Tabellen nutzen: Wenn Du die Häufigkeit von Werten zwischen den beiden Tabellen vergleichen möchtest, ist die Verwendung einer Pivot-Tabelle empfehlenswert. Füge Hilfsspalten hinzu, um die Herkunft der Daten zu kennzeichnen, und führe dann die Analyse mit einer Pivot-Tabelle durch.

Häufige Fehler und Lösungen

  • Fehler 1: Die Zählenwenn-Formel benötigt zu lange.

    • Lösung: Sortiere die Daten in der Suchspalte aufsteigend und verwende die SVerweis-Funktion. Dies kann die Berechnungszeit erheblich verkürzen.

  • Fehler 2: Keine Übereinstimmungen trotz vorhandener Daten.

    • Lösung: Überprüfe, ob es Unterschiede in der Datenformatierung (z.B. Zahl vs. Text) gibt. Verwende die Funktion TEXT() oder WERT(), um sicherzustellen, dass die Formate übereinstimmen.

Alternative Methoden

  • Power Query: Eine effektive Methode für große Datenmengen ist die Verwendung von Power Query. Hier kannst Du die Daten aus beiden Arbeitsblättern importieren und mit einer Abfrage die Übereinstimmungen ermitteln. Dies kann die Berechnungszeit drastisch reduzieren.

  • VBA: Wenn Du mit VBA vertraut bist, kannst Du ein Makro schreiben, das die Zählenwenn-Funktion effizienter umsetzt. Dies kann die Verarbeitungsgeschwindigkeit im Vergleich zu Excel-Formeln deutlich steigern.

Praktische Beispiele

  1. Beispiel mit Zählenwenn: Angenommen, Du möchtest wissen, wie oft der Wert "12345" in Arbeitsblatt2 vorkommt:

    =ZÄHLENWENN(Arbeitsblatt2!A:A; "12345")

  2. Beispiel mit SVerweis: Überprüfe, ob der Wert aus A1 auf Arbeitsblatt1 in Arbeitsblatt2 vorhanden ist:

    =SVERWEIS(A1; Arbeitsblatt2!A:A; 1; FALSCH)

  3. Beispiel für Pivot-Tabelle:

    • Füge in beiden Arbeitsblättern eine Hilfsspalte hinzu, um die Quelle zu kennzeichnen (z.B. "Tabelle1" oder "Tabelle2").
    • Erstelle eine Pivot-Tabelle, um die Häufigkeit der Werte darzustellen.

Tipps für Profis

  • Vermeide volatile Funktionen: Funktionen wie INDIREKT() oder BEREICH.VERSCHIEBEN() können die Berechnungszeit erhöhen. Halte Deine Formeln so einfach wie möglich.

  • Nutze Array-Formeln: Bei komplexen Berechnungen können Array-Formeln helfen, mehrere Berechnungen gleichzeitig durchzuführen.

  • Datenmodell verwenden: Wenn Du Excel 2013 oder neuer verwendest, kannst Du das Datenmodell nutzen, um Daten effizient zu analysieren, ohne die Leistung zu beeinträchtigen.

FAQ: Häufige Fragen

1. Warum ist die Zählenwenn-Formel langsam bei großen Datenmengen?
Die Zählenwenn-Formel muss jede Zeile einzeln überprüfen, was bei großen Datenmengen zu langen Berechnungszeiten führen kann.

2. Wie kann ich die Berechnungszeit verbessern?
Versuche, die SVerweis-Funktion oder Power Query zu nutzen, um die Abfragen effizienter zu gestalten.

3. Was ist Power Query?
Power Query ist ein Tool in Excel, das es ermöglicht, Daten einfach zu importieren, zu transformieren und zu kombinieren, was besonders bei großen Datenmengen vorteilhaft ist.

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