Anzahl gleicher Werte in sehr großer Tabelle

Bild

Betrifft: Anzahl gleicher Werte in sehr großer Tabelle
von: V A T
Geschrieben am: 08.05.2015 10:19:30

Hallo,
ich habe eine Frage und hoffe auf eure bewährte Hilfe. Vorab vielen dank an alle, die versuchen mich zu unterstützen!
Ich haben eine Tabelle mit 20 Spalten und 500000 Zeile. In der ersten Spalte (A) steht eine Kennnummer. Ich möchte nun eine Spalte hinzufügen, in der steht wie oft die Kennnummer aus Spalte A in allen Kennnummern der Spalte A vorkommt.
Im Normalfall mache ich das mit einer Formel =zählenwenn(A:A;A2)und ziehe die einfach nach unten. Funktioniert prima! :-)
Beispiel:
Spalte A - neue Spalte mit Formel
23 3
34 2
56 1
34 2
78 1
23 3
23 3
Aber...das Problem sind nun die 500000 Zeilen (ja, ich weiß da gibt's andere Programme, aber ich kenn mich halt mal nur mit Excel einigermaßen aus). Wenn ich die Formel bis zur letzten Zeile runterkopiere, dann rechnet mein Rechner 10 Stunden.
Gibt es irgendeinen Weg das schneller hin zu bekommen?
Grüße und nochmals Danke!
V A T

Bild

Betrifft: AW: Anzahl gleicher Werte in sehr großer Tabelle
von: Daniel
Geschrieben am: 08.05.2015 10:31:41
Hi
1. sortiere nach der Spalte A
2. erste Hilfsspalte (X) diese Formel, Formel für X2, bis ans Datenende runterziehen
=Summe(1;Wenn(A1=A2;X1;0))
3. Ergebnisspalte (y) diese Formel, wieder für Y2 und bis zum Datenende
=Wenn(A2=A3;Y3;X2)
4. Spalte Y kopieren und an gleicher Stelle als Wert einfügen
5. Spalte X löschen
6. falls erforderlich Tabelle in die Ausgangsreihenfolge zurücksortieren
ist zwar Statisch und muss wiederholt werden, wenn sich die Tabelle ändert, hat aber bei der Datenmenge die beste Peformance
Gruß Daniel

Bild

Betrifft: AW: Anzahl gleicher Werte in sehr großer Tabelle
von: Rudi Maintaire
Geschrieben am: 08.05.2015 10:36:41
Hallo,
ca.2,5 Sek.

Function ZaehlMich(arr)
  Dim i As Long, oDic As Object
  Set oDic = CreateObject("scripting.dictionary")
  For i = 1 To UBound(arr)
    oDic(arr(i, 1)) = oDic(arr(i, 1)) + 1
  Next
  For i = 1 To UBound(arr)
    arr(i, 1) = oDic(arr(i, 1))
  Next
  ZaehlMich = arr
End Function

Sub test()
  Dim arr
  With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
    arr = .Value
    .Offset(, 20) = ZaehlMich(arr)
  End With
End Sub
Gruß
Rudi

Bild

Betrifft: AW: Anzahl gleicher Werte in sehr großer Tabelle
von: V A T
Geschrieben am: 08.05.2015 11:06:08
Hallo Rudi,
danke für Deine Antwort, die ja auch höchstens 2,5 Sekunden gebraucht hat... ;-)
Könntest Du deine Lösung etwas erläutern/kommentieren, mir fehlt ein wenig das VBA-Knowhow, um sie sofort zu verstehen und somit auch zu wissen, wie ich sie praktisch einsetze.
Gruß
V A T

Bild

Betrifft: AW: Anzahl gleicher Werte in sehr großer Tabelle
von: V A T
Geschrieben am: 08.05.2015 11:21:43
Hi,
ok, Entwarnung bzgl. der erbetenen Zusatzunterstützung! :-)
Ich hab es hinbekommen, ich kann es anwenden, es funktioniert (bei mir sind es zwar 2,5 Minuten, aber das liegt bestimmt an meinem saulangsamen System und ist immer noch 1.000.000 mal schneller als die Methode zuvor).
Inhaltlich verstehe ich es zwar nur teilweise (bin in VBA nicht fit genug), aber es reicht, um es auf meine Bedürfnisse anzupassen und weiter damit zu arbeiten.
Vielen, vielen Dank für Deine schnelle und super funktionierende Lösung!!!
Immer wieder ein geiles Forum!!!
Gruß
V A T

Bild

Betrifft: AW: Anzahl gleicher Werte in sehr großer Tabelle
von: Daniel
Geschrieben am: 08.05.2015 12:03:47
Hi
du hast ja noch eine manuelle Lösung bekommen, mit der du die Aufgabe auch ohne Makros in 2 Minuten erledigen kannst!
Gruß Daniel

Bild

Betrifft: Interessehalber nachgefragt ...
von: der neopa C
Geschrieben am: 08.05.2015 16:56:13
Hallo V A T,
... dank Rudis Makro hast Du exakt das von Dir gewünschte Ergebnis.
Mich interessiert nun, wie lange für Deine Datenmenge eine PIVOTauswertung dauern würde.
Dieselbe wäre mit ca. Mausklicks in wenigen Sekunden erstellt (notfalls einfach nachfragen) Sie ergibt zwar nicht die von Dir gewünschte Zuordnung des Ergebnisses in jeder Zeile, dafür aber eine schnelle komprimierte Ergebnisauswertung (die Du zusätzlich nach beliebigen Gesichtspunkten sortieren filtern etc. kannst).
Wie lange dauert die PIVOT-Auswertung und eine Aktualisierung bei zwei Datenänderungen in Deiner Datei mit Deinem PC?
Würde mich über Deine Antwort freuen.
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Anzahl gleicher Werte in sehr großer Tabelle"