Schneller SVERWEIS bzw. INDEX / VERGLEICH

[friedensbringer]

Hallo zusammen,

ich hoffe ihr könnt mir bei einem Problem weiterhelfen und zwar geht es um eine Performanceverbesserung in einer Datei mit sehr vielen Zeilen.
Wenn ich das endlich hinbekommen würde wäre ich sehr froh, weil es diese Fälle immer wieder einmal gibt und ich regelmäßig an der Performance dieser Dateien verzweifle.

Ich habe beiliegend eine entsprechende Beispieldatei hochgeladen, damit man genau sieht worum es mir hier geht.
Aufgrund der zulässigen Größe für Anhänge musste ich diese ein wenig einschränken. Die verschiedenen Varianten habe ich im oberen Bereich für jeweils vier Zeilen eingefügt,
man kann das zu Testzwecken natürlich in den Spalten C-K bis ganz nach unten kopieren.

Die Datei mit 200.000 Zeilen habe ich hier auf Google Drive hochgeladen.

In der aktuellen Datei werden SVERWEISE gemacht, diese auf verschiedene Tabellenblätter, (in der tatsächlichen Anwendung teilweise sogar auf andere Dateien - was nochmals Performance frisst).
Der Schlüssel ist in diesem Fall immer die Kundennummer. Der SVERWEIS schaut zB so aus (grün markiert):

=SVERWEIS($A2;Umsatz!$A:$E;3;FALSCH)

Da es sich bei der Originaldatei um > 200.000 Zeilen handelt dauert dies mehrere Stunden und es kann auch sein dass die Abfrage überhaupt abgebrochen wird.
Bei der Beispieldatei mit ca. 10.000 Zeilen wird der Performanceunterschied aber wie weitem nicht so krass ausfallen.

Jetzt habe ich einige Ansätze gefunden, dass man das mit einem SVERWEIS macht aber die Daten in allen Datenquellen vorher nach
dem Schlüsselfeld (also in meinem Fall der Kundennummer) sortiert und dann den SVERWEIS "modifiziert" und mit WAHR sucht, da dies deutlich performanter ist.
Damit man hier tatsächlich wieder den korrekten Datensatz erhält wird das so gemacht (blau markiert):

=WENN(SVERWEIS($A8;Umsatz!$A:$E;1;WAHR)=$A8;SVERWEIS($A8;Umsatz!$A:$E;3;WAHR);"")

Soweit ist das für mich noch ganz verständlich und das funktioniert auch extrem viel schneller.

Jetzt haben sich (zum Glück) schlauere Leute als ich ganz intensiv mit diesem Thema auseinandergesetzt
und auch getestet wie lange unterschiedliche Varianten dauern und die empfehlen aus Performancegründen
eigentlich durchgängig INDEX / VERGLEICH mit sortierten Daten.

http://www.exceluser.com/blog/727/excels...sults.html
http://analystcave.com/excel-vlookup-vs-...rformance/

Ich bekomme aber leider einfach die entsprechenden INDEX / VERGLEICH Abfragen nicht hin.
Irgendwie stehe ich (leider) mit dieser sehr praktischen Funktion ein wenig auf "Kriegsfuß" weil ich sie einfach nicht so wirklich durchschaue.

So habe ich es jetzt einmal gelöst (orange markiert):

=INDEX(Umsatz!$C:$C;VERGLEICH($A14;Umsatz!$A:$A;1))

Auch das geht deutlich schneller. In der angegebenen Website steht aber noch folgendes (für die bestmögliche getestete Performance):

INDEX-MATCH in Two Formulas, Sorted Data

Finally, this trial uses separate formulas for INDEX and MATCH:

B3:    =INDEX(Data,$G3,B$1)
G3:    =MATCH($A3,Code,1)

Here, we can modify cell G3 to give us an exact match:

G3:    =IF(INDEX(Code,MATCH($A3,Code,1))=$A3, MATCH($A3,Code,1), NA())

In other words, using the two-formula INDEX-MATCH approach against sorted data can be significantly faster than using either VLOOKUP or the one-formula INDEX-MATCH technique, and is best practice. 

Net Calculation Time for Approximate Match: 0.391
Net Calculation Time for Exact-Match Version: 0.438

Ich hoffe ihr könnt mir da weiterhelfen das so wie dort in rot angegeben hinzubekommen.

---- Nur noch am Rande erwähnt:

Es gibt für diese großen Abfragen auch ein Excel-Addin welches SQLite verwendet. Funktioniert ebenfalls ausgezeichnet,
da ich das aber nicht auf jedem Client ein AddIn installieren möchte würde ich das lieber anders lösen. Aber vielleicht hilft es ja jemandem.

http://archive.is/vG8CG

Es scheint hier generell sehr viele Informationen zu diesem Thema zu geben, aber leider oft nur englisch btw. etwas dürftig oder ohne Beispieldatei erklärt:

http://www.goodly.co.in/perform-faster-v...arge-data/
https://fastexcel.wordpress.com/2012/03/...1-vlookup/

Generell wäre für mich auch noch interessant zu wissen wie ihr solche Sachen löst?
SQL? Power-Query? Power-Pivot? Power-BI?

Vielen Dank für eure Hilfe und lg

Olli

[Fennek]

Hallo,

wenn man alle Tabellen in Arrays überführt, alle Operationen in Arrays durchführt und am Ende alle Daten enbloc zurückschreibt, sollte es recht schnell (Schätzung: unter 1 Minute) machbar sein.

Aber zuerst sollten die Power-Query-Spezialisten prüfen, ob es damit nicht einfacher möglich ist.

mfg

[friedensbringer]

Hallo Fennek,

danke für deine Antwort. Ich bin natürlich auch Möglichkeiten wie Power-Query usw. gegenüber aufgeschlossen.
Wichtig wäre mir zum Schluss eigentlich nur:

a) Ich muss es noch verstehen können
b) Es sollte in Excel möglichst ohne zusätzliche AddIns laufen
c) Es sollte automatisiert möglich sein, also zB über VBA in der Nacht (das macht es jetzt schon, es dauert nur sehr lange).

Danke und lg

Olli

[Fennek]

Hallo,

ein kleiner Test mit Pivot-Tabelle sah sehr gut aus:

Zuerst die Sheets Umsatz, Artikel und Besuche als "intelligente" Tabellen definieren (strg-t).

Dann eine PivotTabelle anlegen, aber recht weit oben auf "alle Tabellen" klicken. Wenn man die Spalten richtig sortiert, entspricht es deinem Beispiel.

XL schlägt vor, die Verbindungen der Tabellen automatisch vorzunehmen, in diesem Fall klappt das gut.

mfg

[Fennek]

Hallo,

eigentlich dachte ich, mit Arrays sollte es recht einfach gehen, aber ...

Folgender Code brauchte auf meinem Rechner mit 10.000 Datensätzen 20  Sekunden:

Code:

Sub Fen()
Anf = Timer
Dim Res()
Um = Sheets("Umsatz").Cells(1).CurrentRegion
Ar = Sheets("Artikel").Cells(1).CurrentRegion
Be = Sheets("Besuche").Cells(1).CurrentRegion
With CreateObject("system.collections.sortedlist")
   For i = 2 To UBound(Um)
       .Add Um(i, 1), i
   Next i
   For i = 2 To UBound(Ar)
       If Not .contains(Ar(i, 1)) Then .Add Ar(i, 1), i
   Next i
   For i = 2 To UBound(Be)
       If Not .contains(Be(i, 1)) Then .Add Be(i, 1), i
   Next i
   Set F = .getkeylist()
ReDim Res(.Count - 1, 10)
U = Application.Index(Um, 0, 1)
A = Application.Index(Ar, 0, 1)
B = Application.Index(Be, 0, 1)
For i = 0 To .Count - 1
       Res(i, 0) = F(i)
       Z = WorksheetFunction.Match(F(i), U, 1)
       If IsNumeric(Z) Then
           Res(i, 1) = Um(Z, 2)
           Res(i, 2) = Um(Z, 3)
           Res(i, 5) = Um(Z, 4)
           Res(i, 8) = Um(Z, 5)
       End If
       
       Z = WorksheetFunction.Match(F(i), A, 1)
       If IsNumeric(Z) Then
           Res(i, 3) = Ar(Z, 3)
           Res(i, 6) = Ar(Z, 4)
           Res(i, 9) = Ar(Z, 5)
       End If
       Z = WorksheetFunction.Match(F(i), B, 1)
       If IsNumeric(Z) Then
           Res(i, 4) = Be(Z, 3)
           Res(i, 7) = Be(Z, 4)
           Res(i, 10) = Be(Z, 5)
       End If
Application.StatusBar = i
   Next i
Sheets("TT").Cells(2, 1).Resize(.Count, 10) = Res
End With
Debug.Print Timer - Anf
End Sub

mfg

(Die Ausgabe erfolgt in ein neues Sheets("TT")

[GMG-CC]

Moin,
ich denke, dass Power Query auch eine "erträgliche" Geschwindigkeit bietet. 
ich habe dir hier einmal den genierten Quellcode eingestellt:

Code:

let
   Quelle = Excel.CurrentWorkbook(){[Name="Kunden"]}[Content],
   #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Kundennummer", Int64.Type}, {"Kundenname", type text}}),
   #"Zusammenführte Abfragen" = Table.NestedJoin(#"Geänderter Typ",{"Kundennummer"},Umsatz,{"Kundennummer"},"NewColumn",JoinKind.LeftOuter),
   #"Erweiterte NewColumn" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "NewColumn", {"Umsatz 2014", "Umsatz 2015", "Umsatz 2016"}, {"Umsatz 2014", "Umsatz 2015", "Umsatz 2016"}),
   #"Zusammenführte Abfragen1" = Table.NestedJoin(#"Erweiterte NewColumn",{"Kundennummer"},Artikel,{"Kundennummer"},"NewColumn",JoinKind.LeftOuter),
   #"Erweiterte NewColumn1" = Table.ExpandTableColumn(#"Zusammenführte Abfragen1", "NewColumn", {"Artikel 2014", "Artikel 2015", "Artikel 2016"}, {"Artikel 2014", "Artikel 2015", "Artikel 2016"}),
   #"Zusammenführte Abfragen2" = Table.NestedJoin(#"Erweiterte NewColumn1",{"Kundennummer"},Besuche,{"Kundennummer"},"NewColumn",JoinKind.LeftOuter),
   #"Erweiterte NewColumn2" = Table.ExpandTableColumn(#"Zusammenführte Abfragen2", "NewColumn", {"Besuche 2014", "Besuche 2015", "Besuche 2016"}, {"Besuche 2014", "Besuche 2015", "Besuche 2016"}),
   #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Erweiterte NewColumn2",{"Kundennummer", "Kundenname", "Umsatz 2014", "Artikel 2014", "Besuche 2014", "Umsatz 2015", "Artikel 2015", "Besuche 2015", "Umsatz 2016", "Artikel 2016", "Besuche 2016"})
in
   #"Neu angeordnete Spalten"

Da das große File auch als *.xlsb noch 46Mb belegt, habe ich hier auf ein Hochladen verzichtet (ginge das überhaupt, ohne es zu splitten?)

[Fennek]

Hallo,

nur so zur Übung mit VBA:

Der erste Code enthielt vermutlich ein paar Fehler, dieser Code ist mit 7 Sekunden sogar noch schneller:

Code:

Sub Fen2()
Anf = Timer
Dim Res()
Um = Sheets("Umsatz").Cells(1).CurrentRegion
Ar = Sheets("Artikel").Cells(1).CurrentRegion
Be = Sheets("Besuche").Cells(1).CurrentRegion
With CreateObject("system.collections.sortedlist")
   For i = 2 To UBound(Um)
       .Add Um(i, 1), Um(i, 2) & "|" & i & "|"
   Next i
   For i = 2 To UBound(Ar)
       If Not .contains(Ar(i, 1)) Then
           .Add Ar(i, 1), Ar(i, 2) & "||" & i & "|"
       Else
           .Item(Ar(i, 1)) = .Item(Ar(i, 1)) & i & "|"
       End If
   Next i
   For i = 2 To UBound(Be)
       If Not .contains(Be(i, 1)) Then
           .Add Be(i, 1), Be(i, 2) & "|||" & i
       Else
           .Item(Be(i, 1)) = .Item(Be(i, 1)) & i & "|"
       End If
   Next i
   Set F = .getkeylist()
   Set it = .getvaluelist()
ReDim Res(.Count - 1, 10)
For i = 0 To .Count - 1
       Res(i, 0) = F(i)
       ZZ = Split(it(i), "|")
       Res(i, 1) = ZZ(0)
       Z = Val(ZZ(1))
       If Z Then
           Res(i, 2) = Um(Z, 3)
           Res(i, 5) = Um(Z, 4)
           Res(i, 8) = Um(Z, 5)
       End If
       
       Z = Val(ZZ(2))
       If Z Then
           Res(i, 3) = Ar(Z, 3)
           Res(i, 6) = Ar(Z, 4)
           Res(i, 9) = Ar(Z, 5)
       End If
       
       If UBound(ZZ) = 3 Then
       Z = Val(ZZ(3))
           If Z Then
               Res(i, 4) = Be(Z, 3)
               Res(i, 7) = Be(Z, 4)
               Res(i, 10) = Be(Z, 5)
           End If
       End If
Application.StatusBar = i
   Next i
Sheets("TT").Cells(2, 1).Resize(.Count, 10) = Res
End With
Debug.Print Timer - Anf
End Sub

Dabei habe ich eine neue Funktion von "sortedList" gelernt.

mfg

(falls der Code genutzt wird, bitte gründlich prüfen)

[friedensbringer]

Hallo Günther,

vielen Dank für diesen Ansatz. Kannst du mir hier vielleicht kurz erklären wie ich hier mit Power Query vorgehen muss um das zu erreichen?
Habe mich bisher leider noch nicht wirklich viel damit beschäftigen können.

Was bedeutet denn "erträglich", wie lange hat es denn ca. gedauert alle Daten zu befüllen?

Ich glaube splitten der Datei wäre hier etwas schwierig, ich wollte die Datei eigentlich hier hochladen, aber es war leider nur möglich maximal 2 MB hochzuladen (was ich eh verstehen kann).
Vielen Dank und lg

Olli

[shift-del]

Generell wäre für mich auch noch interessant zu wissen wie ihr solche Sachen löst?
SQL? Power-Query? Power-Pivot? Power-BI?

Das Problem mit Formeln ist dass sie jedes Mal neu berechnet werden.
Bei Power Query wird nur auf Anforderung aktualisiert.

Power Query: mit Zusammenführen von zwei Tabellen.
Datenmodell (ohne Power Pivot): mit Beziehungen und dann direkter Auswertung in Pivot-Tabellen.
Datenmodell (mit Power Pivot): zusätzlich grafische Darstellung der Beziehungen.

[snb]

Oder

Code:

Sub M_snb()
  Dim sp(0, 10)
    
  With CreateObject("scripting.dictionary")
    For j = 1 To 3
      sn = Sheets(Choose(j, "Umsatz", "Artikel", "Besuche")).Cells(1).CurrentRegion
      For jj = 2 To UBound(sn)
        st = sp
        If .exists(sn(jj, 1)) Then st = .Item(sn(jj, 1))
        For jjj = 1 To 5
          st(0, Choose(jjj, 0, 1, 1 + j, 4 + j, 7 + j)) = sn(jj, jjj)
        Next
        .Item(sn(jj, 1)) = st
      Next
    Next
        
     Sheets("Kunden").Cells(1, 20).Resize(.Count, 10) = Application.Index(.items, 0, 0)
  End With
End Sub