Microsoft Excel

Herbers Excel/VBA-Archiv

Vergleich großer Datenmengen | Herbers Excel-Forum


Betrifft: Vergleich großer Datenmengen von: Roland
Geschrieben am: 17.11.2009 09:03:42

Hallo Herbianer,

ich habe eine Datei mit Stammdaten, die ziemlich umfangreich ist (ca. 500.000 Zeilen). In dieser Datei ist für jedes Teil ein bestimmtes Lagerfach hinterlegt.

Nun muss ich diese Datei immer wieder mit Lagerdateien abgleichen, die mir von Kunden zugeschickt werden, d.h. ich bekomme von Kunden eine Aufstellung mit 10.000 Artikeln, die diese vorrätig haben und muss den Kunden mitteilen, in welchem Lagerfach dieses Teil normalerweise untergebracht ist.

Dies habe ich momentan über einen sverweis gelöst, was aber ziemlich lange dauert und auch sehr viel Speicher kostet. Gibt es hierfür eine schneller Lösung?

Leider kann die ganze Sache nur in Excel ausgeführt werden, Access steht leider nicht zur Auswahl.

Danke und Gruß
Roland

  

Betrifft: AW: Vergleich großer Datenmengen von: Roland Hochhäuser
Geschrieben am: 17.11.2009 09:56:22

Hallo Roland,

bei der Datenmenge geht das schon einen Zacken schneller mit VBA (eine Formelalternative habe ich nicht in petto). Das setzt aber voraus, dass ihr überhaupt mit Makros arbeiten dürft, wenn nicht, wars das. Wenn ja, wäre es günstig, die Struktur der Stammdatendatei und der Lagerdateien zu kennen sowie zu wissen, ob die Lagerdateien der Kunden immer gleich aufgebaut sind. Zudem solltest Du sagen, wohin genau der Output hin soll. Daher der Vorschlag (nur wenn mit Makros gearbeitet werden darf): lade mal zwei Dateien hoch mit jeweils ca. 5 Spieldatensätzen, und ich schau mir die Sache an.

Gruß aus Berlin von
Roland Hochhäuser


  

Betrifft: AW: Vergleich großer Datenmengen von: Roland
Geschrieben am: 17.11.2009 11:02:17

Hallo Roland,

also meine Idee war folgende:
Ich öffne für jede neue Zuordnung die gleiche Datei. Dort sind verschiedene Command Boxen eingebaut.

Mit Command Box1 wähle ich die Stammdatendatei aus und öffne diese.
Mit Command Box2 wähle ich die Vergleichsliste aus und öffne auch diese (diese beiden Command Boxen hab ich bereits erstellt).

Code:
Dim DateiName As String
DateiName = Application.GetOpenFilename("Excelfiles (*.xls*), *.xls*", Title:="Datei auswählen", _
MultiSelect:=False)
Select Case DateiName
Case "Falsch"
Case Else
Workbooks.Open DateiName
End Select

Durch die nächste Command Box soll nun ein neues Datei geöffnet werden, für die ich gerne folgende Benennung hätte: „Kundendatei“+_zugeordnet.xlsx oder auch .xlsm

In diese neue Datei würde ich dann gerne die benötigten Daten aus der Kundendatei einfügen, d.h. die Kundendateien sind meistens verschieden aufgebaut. Ich hätte mir deshalb eine Abfrage gedacht nach dem Schema: In welcher Spalte ist die Bestellnummer hinterlegt? In welcher Spalte ist die Benennung hinterlegt? In welcher Spalte ist der Lagerbestand hinterlegt? Nach Eingabe dieser drei Spalten sollten die Spalten dann in die neue Datei übertragen werden und die alte Kundendatei wieder geschlossen werden.

Dann soll in der neuen Datei der Vergleich der Bestellnummern durchgeführt werden und das Lagerfach dort in Spalte D eingefügt werden.

Ich hab jetzt extra mal ausführlich ausgeführt wie ich mir das vorstelle. Wenn du eine bessere Idee hast bin ich dafür natürlich offen.

Bisher hab ich das Ganze immer per Sverweis ausgeführt und die Spalten und Formeln händisch eingefügt, aber mit VBA denke ich sollte es etwas schneller gehen.

Ich hoffe du kannst mir da helfen. Teilweise bekomm ich die Sachen per VBA Rekorder und probieren hin, aber ich häng gerade etwas.

Danke und Grüße aus München
Roland


https://www.herber.de/bbs/user/65972.xlsx
https://www.herber.de/bbs/user/65973.xlsx


  

Betrifft: AW: Vergleich großer Datenmengen von: Roland Hochhäuser
Geschrieben am: 17.11.2009 11:42:18

Hallo Roland,

Excel 2007 habe ich nicht im Büro. Da müsstest du bis heute abend warten.

Bis dahin
Roland Hochhäuser


  

Betrifft: AW: Vergleich großer Datenmengen von: Roland
Geschrieben am: 17.11.2009 11:47:46

Hallo Roland,

passt schon. Ist nicht so dringend.

Versuch in der Zwischenzeit schon mal ein paar Teillösungen zu erarbeiten.

Trotzdem schon mal Danke.

Gruß
Roland


  

Betrifft: AW: Vergleich großer Datenmengen von: Jutta_N.
Geschrieben am: 17.11.2009 14:51:06

Hallo Roland,
ich habe solche Dateien in Access verknüpft, mache dann meinen Abgleich.
Da ich anschließend das Ergebnsi für die anderen in Excel brauche, hole ich mir die Daten in Excel mit Externe Daten > importieren wieder rein.
Geht fixer als mit irgendwelchen Formeln. Und einmal angelegt muss man in Access dann auch nichts mehr machen. Nur noch die die verknüpfte Exceltabelle öffnen..
Gruß
Jutta


  

Betrifft: AW: Vergleich großer Datenmengen von: Roland Hochhäuser
Geschrieben am: 17.11.2009 22:41:39

Hallo Roland,

Einige Verständnisfragen:
1. Es soll eine neue Datei erstellt und nach einem Muster abgespeichert werden: „Kundendatei“+_zugeordnet.xlsx. Ich verstehe das so, dass dieser Dateiname vorgeschlagen wird, du aber die Möglichkeit haben willst, ihn zu ändern oder zu ergänzen, richtig?

2. Wenn ich dich richtig verstehe, willst du exakt 3 Spalten aus der jeweiligen Kundendatei auswählen und in eine neue Datei einfügen. Die Spaltenauswahl ist kein Problem, aber das Einfügen: Soll dann in der Datei „Kundendatei“+_zugeordnet.xlsx in Spalte A immer die Bestellnummer gem. Kundendatei, in Spalte B immer die Benennung gem. Kundendatei, in Spalte C immer der Lagerbestand gem. Kundendatei und dann zu guter Letzt in Spalte D das passende Lagerfach aus der Stammdatei oder willst du auch die Spalten wechseln können?

3. Ich komme mit deinen hochgeladenen Dateien nicht klar:
In welcher Spalte der Kundendatei sind die Bestellnummer, die Benennung und der Lagerbestand?
In welcher Spalte der Stammdatei sind die Bestellnummer und das Lagerfach? Dort finde ich nur TEILE_SACHNUMMER, "Zuordnung 400", "Auslastung 400", "Zuordnung 600" und "Auslastung 600". Das kann m.E. nicht die vollständige Struktur der Stammdatei sein, da fehlen noch Spalten!? Für dich sicher eine Kleinigkeit, das zu erklären, aber ich stecke nicht in deinem Job.

4. Die ganze Arbeit man in der Tat durch eine Datei erledigen lassen, ich würde aber perspektivisch eher für ein Addin plädieren. Das versteckt etwas besser den VBA-Code und lässt sich bei Bedarf im Ladeverhalten auch noch "personalisieren"; muss ja nicht jeder in der Fa. mitbekommen, warum und wer den Job besser und schneller erledigt.

5. Beim nächsten Mal Dateien bitte im xls-Format hochladen, das ist einfach noch verbreiteter und steht mir auch tagsüber zur Verfügung.

Roland Hochhäuser


  

Betrifft: AW: Vergleich großer Datenmengen von: Roland
Geschrieben am: 18.11.2009 13:07:16

Hallo Roland,

sorry für die längere Reaktionszeit musste aber erst noch ein ppar Dinge durcharbeiten. Hier also erst mal die Antworten zu deinen Fragen:

1. Ich möchte wenn möglich immer die gleiche Datei öffnen und aus dieser heraus sämtliche Makros starten
(mein bisheriger Entwurf sieht wie folgt aus: https://www.herber.de/bbs/user/66011.xls)

2. Richtig, ich will immer exakt drei Spalten (zumindest jetzt) aus der jeweiligen Kundendatei auswählen und diese sollen dann immer an der gleichen Stelle eingefügt werden. Danach folgen ein paar Berechnungen und das passende Fach steht als Endergebnis da. (die Berechnungen bekomm ich eigentlich schon hin, aber die dauern hat dann ziemlich lang: https://www.herber.de/bbs/user/66012.xls)

3. Zu meinen Dateien:
Genau das ist ja das Problem: Die Kunden sitzen in verschiedene Ländern und schicken die Aufstellungen vogelwild. In der Kundensdatei stehen die Bestellnummer, die Benennung und die Lagermenge in Spalte D, E und F.
Spalte D muss dann noch so formatiert werden, dass nur die letzten sieben Ziffern angezeigt werden usw. aber das krieg ich selber hin.
In der Stammdatei steht in Spalte A die Bestellnummer = Sachnummer. Mehr benötige ich für diese Berechnung nicht. Mit allen Daten hätte die Datei über 30MB und wäre für mich nicht zu benutzen.
Vielleicht auch noch mal eine Erklärung: Wir bekommen von Kunden eine Aufstellung ihres Bestandes und planen für diese dann das Lager neu, d.h. über die Berechnung finden wir heraus, wie oft das Fach mit der Größe 1, 2, 3 usw. benötigt wird und planen dem Kunden dann welches Regal (Fächer sind festgelegt) er wie oft benötigt und welches Teil in welches Fach passt.

4. Was meinst du mir Addin und was ist hier der Unterschied zum Makro?

5. Hab die Dateien noch mal in xls-Format hochgeladen (sorry)

Danke und Grüße
Roland


https://www.herber.de/bbs/user/66014.xls = Kundendatei

https://www.herber.de/bbs/user/66015.xls = Stammdaten


  

Betrifft: AW: Vergleich großer Datenmengen von: Roland Hochhäuser
Geschrieben am: 18.11.2009 15:27:52

Hallo Roland,

es wird immer unklarer, daher mal weitere Fragen:

1. du willst das Instrument als Datei, dann willst du es nicht als Addin, soweit begriffen und OK.

2. "Danach folgen ein paar Berechnungen" Von Berechnungen war bisher nicht die Rede. Wie auch immer: die Datei 66011.xls enthält einige Makros, die SVerweise produzieren, jedoch gerade keine Berechnungen vornehmen. Andererseits ist dort wie in der Kundendatei eine Spalte QTY enthalten, die in der Kundendatei die Lagermenge bedeutet. Ist die Spalte QTY in der Datei 66011.xls jetzt das Lagerfach? oder doch die Lagermenge? Wenn letzteres, dann fällt auf, das die Zahlen andere sind als in der Kundendatei. Meinst du das mit Berechnungen und wie wurden die vorgenommen.

3. Ist den unterschiedlichen Kundendateien denn wenigstens gemeinsam, das immer das letzte Arbeitsblatt die hier interessierenden Informationen enthält?

Bis dann
Roland Hochhäuser


  

Betrifft: AW: Vergleich großer Datenmengen von: Roland
Geschrieben am: 20.11.2009 14:27:15

Hallo Roland,

entschuldige die späte Rückmeldung, aber ich konnte von zuhause nicht antworten.

Ich versuch das Ganze noch mal genauer auszudrücken:

Meine erste Teilfrage ist, wie kann ich aus einem bestehenden Excel-Sheet (Ursprungsdatei) über Buttons wie im Bsp. (https://www.herber.de/bbs/user/66011.xls) andere Dateien öffnen und Inhalte aus diesen in der Ursprungsdatei anzeigen lassen.

Nimm z.B. meine hochgeladene Datei. Wie kann ich dort eine beliebige Datei öffnen und mir ein bestimmtes Tabellenblatt aus dieser Datei im Tabellenblatt2 anzeigen lassen.

Danke für die Anregungen
Roland


  

Betrifft: Im Archiv gibts genug dazu o.w.T von: Roland Hochhäuser
Geschrieben am: 21.11.2009 11:59:44




  

Betrifft: AW: Vergleich großer Datenmengen von: fcs
Geschrieben am: 18.11.2009 08:42:35

Hallo Roland,


bei der Datenmenge wird jeder Abgleich seine Zeit brauchen.
Ich hab jetzt verschiedenes probiert.
1. Nutzung von SVERWEIS-Formel sehr langsam, obwohl Formelunterstützte Lösungen eigetlich normalerweise sehr effektiv sind.

2. Suche der Teilenummern aus dem Kundenblatt im Stammdatenblatt.
Das geht einigermassen. Auf meinem Notebook (2 Ghz Pentium Dual Processor) ca. 30 bis 50 Sekunden um 10000 Datensätze abzugleichen.

Bei den eingestellten Spalten für die Werte, die aus den Stammdaten eingelsen werden sollen muss du nochmals anpassen.

Gruß
Franz
https://www.herber.de/bbs/user/65998.xlsm


  

Betrifft: Liste Sortieren. von: Tino
Geschrieben am: 17.11.2009 18:57:39

Hallo,
wenn Du die Liste mit den 500000 Zeilen aufsteigend sortierst,
kannst Du die Variante im SVERWEIS ungefähre Übereinstimmung verwenden,
die ist um ein vielfaches schneller.

Beispiel:
Tabelle1

 ABCDE
2LagernummerFach 50001060
350000050 50001161
450000151 50001262
550000252   
650000353   
750000454   
850000555   
950000656   
1050000757   
1150000858   
1250000959   
1350001060   
1450001161   
1550001262   
1650001363   
1750001464   
1850001565   
1950001666   
2050001767   
2150001868   
2250001969   
2350002070   
2450002171   
2550002272   
2650002373   
2750002474   
2850002575   
2950002676   
3050002777   
3150002878   
3250002979   
3350003080   
3450003181   
3550003282   
3650003383   
3750003484   
3850003585   
3950003686   

Formeln der Tabelle
ZelleFormel
E2=WENN(SVERWEIS(D2;A:B;1;WAHR)=D2;SVERWEIS(D2;A:B;2;WAHR); "")
E3=WENN(SVERWEIS(D3;A:B;1;WAHR)=D3;SVERWEIS(D3;A:B;2;WAHR); "")
E4=WENN(SVERWEIS(D4;A:B;1;WAHR)=D4;SVERWEIS(D4;A:B;2;WAHR); "")



Die Liste ist nach der Spalte Lagernummer aufsteigend sortiert.

Gruß Tino


Beiträge aus den Excel-Beispielen zum Thema "Vergleich großer Datenmengen"