Frage Riesige Excel-Tabelle dauert zu lange, um Links zu aktualisieren oder Formeln zu berechnen


Ich habe eine Excel-Tabelle mit 5000 Zeilen und Spalten bis AY (Größe 12 MB). Außer den ersten sechs Spalten enthält der Rest entweder vlookups oder andere Formeln. Alle vlookups befinden sich in einem separaten Excel-Arbeitsblatt. Ich habe die Excel-Einstellung geändert, um die Links manuell zu aktualisieren und Formeln zu berechnen. Jedes Mal, wenn ich versuche, die Links zu aktualisieren, hängt entweder Excel oder es dauert etwa 15 Minuten.

Im Moment arbeite ich daran, das Arbeitsblatt in vier Instanzen aufzuteilen. wo ich vier verschiedene Excel-Blätter nacheinander aktualisieren werde. Dazu müssen Indexspalten jedoch manuell in die nächste Stufe kopiert werden. Es dauert 3-4 Stunden, um die Links zu aktualisieren. Gestern habe ich Fehlermeldungen bekommen, dass:

Excel verfügt nicht über genügend Ressourcen, um den Vorgang abzuschließen.

Mir blieb keine andere Wahl, als die gesamte Operation in vier kleine Schritte aufzuteilen, die einen manuellen Eingriff erforderten.

Ich schaute INDEX an. Nicht wirklich auf meinen Fall anwendbar. Irgendwelche Ideen, wie ich es schnell schaffen kann?


4
2017-08-20 19:33


Ursprung


Das klingt nach einem guten Kandidaten für eine Datenbankanwendung (ms-access oder andere) anstatt einer Tabellenkalkulation. Excel wurde nicht wirklich für die Verarbeitung großer Datenmengen entwickelt. - Stewbob


Antworten:


5000 Zeilen mal 50 Spalten ist kein großes Arbeitsblatt. 12MB ist eine vollkommen vernünftige kleine bis mittlere Dateigröße und Sie sollten nichts so drastisches wie das Nacharbeiten zu einer Datenbank oder etwas ähnliches tun müssen.

Nach Ihrer Beschreibung scheint das Problem die Links zu sein. Wie groß sind die referenzierten Arbeitsmappen? Rechnen sie neu, wenn du dich mit ihnen verbindest? Ich mag die Pre-Opening-Idee: Man kann sehen, was dann passiert.

Ist Ihr Speicherverbrauch (siehe Task-Manager) größer als Ihr verfügbarer physischer Arbeitsspeicher? Sobald Speicher auf die Festplatte ausgelagert werden muss, werden die Dinge langsam langsamer.

Abgesehen von der Hilfe vor dem Öffnen und Sortieren, schlage ich vor, die Nicht-Lookup-Formeln zu betrachten: Kann man sie in Array-Formeln umwandeln und einmal pro Berechnung statt einmal pro Zeile aufrufen?

Auf der SVERWEIS-Sache: hast du versucht zu verwenden INDEX(value_range, MATCH(lookup_key, key_range))? Es gibt Situationen, in denen es schneller ist.

Sie sagen nicht, wenn Sie irgendwelche VBA haben. Wenn Sie dies tun, sehen Sie, wie oft VBA-Funktionen aufgerufen werden und wie viel Zeit sie benötigen. VBA-Code, der viel auf Excel-Objekte verweist, kann teuer sein.


3
2017-09-12 13:50





Wenn sich in Ihrer Datei Daten befinden, die nicht konditional sein müssen und gleich bleiben (zB das Geburtsdatum einer Person), dann nach dem ersten vlookup, um die Daten zu erhalten, 'Kopieren' und 'Einfügen Spezial - Werte' die gesamte Spalte / row, um zu verhindern, dass die Datei diese Zellen jedes Mal neu berechnet, wenn Sie die Berechnung ausführen. Das Ausführen eines vlookup für Daten aus einem anderen vlookup, das ich bemerkt habe, dauert ziemlich lange.


3
2017-07-27 19:04





Leider ist das bei Excel-Tabellen so ... VBA ist nicht gerade die schnellste Option.

Möglicherweise möchten Sie die Daten in eine Microsoft Access-Datenbank migrieren.


2
2017-08-20 19:49





Öffnen Sie die Arbeitsmappe (n), mit denen Sie verknüpfen, bevor Sie die Arbeitsmappe öffnen, die die Verknüpfungen enthält.

Sortieren Sie die Daten und verwenden Sie die sortierte Option in SVERWEIS (verwenden Sie für den letzten Parameter True).


2
2017-09-12 10:55





Ich benutze Office 10, um dies zu umgehen.

  1. Ich habe meine riesige Datei geöffnet und sie als Excel-Arbeitsmappe 1997-2003 gespeichert.
    • Es veranlasste eine Nachricht, die mich etwas fragte (ich kann mich nicht erinnern).
  2. Ich klickte OK und ich hatte eine neue Excel 1997-2003 Datei.
    • Die Datei ging von 25 MB auf unter 1 MB.
  3. Dann habe ich es diesmal wieder von der alten 1997-2003 Datei zu einem neuen Arbeitsbuch gespeichert und es wurde noch kleiner.

Ich hatte dieses Problem mit mehreren Arbeitsmappen und dies hat es immer für mich gelöst.


2
2017-11-04 18:06





Vielleicht hol dir einen schnelleren Computer oder rüste dein RAM auf


1
2017-08-20 19:44



Das geht leider nur so weit: Excel wird nicht mehr als einen CPU- oder CPU-Kern nutzen :( - warren


Ich stimme Mike Woodhouse zu. Das sind nicht viele Daten.

Als Test würde ich eine neue Tabelle mit den Daten aus beiden Tabellen erstellen, die in einer neuen Tabelle zusammengefasst werden. Dies wird Ihnen sagen, wenn das Problem auftritt, da die Daten in zwei separaten Tabellenkalkulation sind. Dies sollte kein Problem sein, aber es wird ein guter Test sein.


0
2017-09-12 14:51





Ich weiß nicht, ob dies für Ihre Situation hilfreich ist, da es sich um Datenbankverknüpfungen handelt, aber ich hatte das gleiche Problem, außer dass es zu lange dauerte, um eine Tabelle zu aktualisieren, die eine Datenbankabfrage ausführt.

Die Art, wie ich die Dinge um den Faktor 15 beschleunigte, bestand darin, dass die Abfrage zuerst die erforderlichen Daten auswählte, anstatt sie von Excel filtern zu lassen. Zweitens habe ich im Fenster Verbindungseigenschaften meiner DB-Verbindung die Option "Daten aus dem externen Datenbereich entfernen vor dem Speichern der Arbeitsmappe" markiert. Dies half enorm, so dass meine Datei nicht kontinuierlich mit den größeren Datenmengen wuchs, die ich abfragen könnte.

Jetzt dauert meine gesamte Aktualisierung etwa 2-3 Sekunden statt 30-45.


0
2018-05-11 21:48





Meiner Erfahrung nach ist die Verknüpfung mit externen Dateien der langsamste Prozess überhaupt. Ich habe gelesen, dass dies daran liegt, dass Excel das Berechnungsmodell jedes Mal durch eine Anzahl von verschiedenen verknüpften Dateien aktualisieren muss, anstatt nur eines.

Ich vermute von Ihrem ersten Beitrag, dass das war, was das Rechnen verlangsamte. Haben Sie versucht, alle Daten in die gleiche Arbeitsdatei zu verschieben und alle Links so zu aktualisieren, dass sie intern zu dieser Datei gehören?


0
2017-08-06 13:30