balgor
Goto Top

Excel zwingt beim berechnen einer SVERWEIS-Formel den Rechner mit Warntönen in die Knie - Was kann ich verbessern?

Hallo,

ich habe eine Preisliste mit aktuellen Preisen und einen Export unseres Artikelstamms. Die Preisliste umfasst ca. 44mb und 250k Zeilen, der Artikelstamm ist ca. 4mb und 6000 Zeilen groß.
Ich wollte das mir die Formel für jeden Artikel aus dem Artikelstamm, die er in der Preisliste findet, den zur Zeit noch aktuellen Preis aus dem Artikelstamm schreibt.

Beispiel:
Artikel | Neuer Preis ab jetzt | Alter Preis bis jetzt
Ball | 10,00 | 9,00
Rad | 80,00 | 75,00

Den "alter Preis bis jetzt" wollte ich eben aus dem Artikelstamm holen.

Mein Lösungsansatz war folgender:

=WENN(WENNFEHLER(SVERWEIS(A2;artikelstamm.csv!$A:$AI;35;FALSCH);"")="";"";SVERWEIS(A2;artikelstamm.csv!$A:$AI;35;FALSCH))

Diese kopierte ich einfach auf alle Zeilen der Preisliste, in der Spalte wo ich den Wert brauche. Soweit ich sagen kann funktionierte das auch ohne bisher ohne weiteres, allerdings waren die Preislisten um einiges kleiner, vllt. 10-20k Zeilen und entsprechende MB. Dabei dauerte das berechnen der Formel (siehe Excel-Bild) 10-30 Sekunden.
Das es bei 250k Zeilen entsprechend länger dauert, darauf war ich eingestellt. Dass es meinen Rechner aber so dermaßen quält das dieser mit Warntönen daher kommt überraschte mich. Nach 2-3 Minuten der Berechnung fing der Rechner an zu Piepen (so Richtung Bios-Warntöne) und nach kurzer Zeit ging es in einen Dauerpiepton über, so dass ich Excel abwürgen musste, danach war wieder Ruhe. Normal beenden ließ es sich nicht mehr.

Ich such jetzt möglichst einen Lösungsansatz der sich weiterhin in Excel auf Formelbasis bewegt, der Entweder die Berechnung schneller vonstatten gehen lässt oder die Auslastung des Rechners soweit reduziert das dieser an der Aufgabe nicht stirbt.

Der Parameter "Wahr" in dem SVERWEIS ist leider keine Option.

Vielen dank im Voraus für die Mühen!
excel

Content-Key: 491947

Url: https://administrator.de/contentid/491947

Ausgedruckt am: 28.03.2024 um 12:03 Uhr

Mitglied: erikro
erikro 05.09.2019 um 15:43:32 Uhr
Goto Top
Moin,

was soll der ganze WENN-Konstrukt mit dem doppelten SVERWEIS? Warum nicht nur den letzten Teil der Formel (zweiter SVERWEIS)? Die Ausgabe #NV kannst Du in den Optionen unterdrücken. So, wie Du das geschrieben hast, muss Excel in jeder Zeile erst den ersten SVERWEIS auswerten, dann WENNFEHLER, dann WENN und dann noch einmal den zweiten SVERWEIS. Lässt Du den ganzen WENN-Teil weg, muss es nur einmal den SVERWEIS auswerten. Das dürfte schonmal schneller gehen.

hth

Erik
Mitglied: Balgor
Balgor 05.09.2019 um 15:56:24 Uhr
Goto Top
Hallo,

danke für den Beitrag. Klingt erst mal logisch dein Gedanke. Ich habe es entsprechend ausprobiert und der Geschwindigkeitsgewinn ist leider marginal, wenn überhaupt messbar.
Mitglied: erikro
erikro 05.09.2019 um 16:00:44 Uhr
Goto Top
Mit Abbruch wegen Überlastung?
Mitglied: Balgor
Balgor 05.09.2019 um 16:09:49 Uhr
Goto Top
Ja, leider.

Ich finde es generell schon ein starkes Stück, dass sich der Rechner akustisch meldet weil es ihm so zu viel ist. Was genau meckert dort eigentlich?
Mitglied: Pjordorf
Pjordorf 05.09.2019 um 16:11:09 Uhr
Goto Top
Hallo,

Zitat von @Balgor:
Ich such jetzt einen Lösungsansatz
Ist deine CPU ausreichend gekühlt wenn das Berechnungschaos losrennt?
Sind die Gehäuse Lüfter frei und können die Abwärme abtansportieren?
Ist der Grafik Lüfter OK usw.?

Gruß,
Peter
Mitglied: erikro
erikro 05.09.2019 um 16:41:13 Uhr
Goto Top
Moin,

1. Schmeiße die 33 überflüssigen Spalten aus dem alten Stamm raus. Du brauchst ja nur Bezeichnung und Preis.
2. Suche nicht über die gesamten Spalten, sondern nur bis zu der Zeile, in der auch Daten stehen. Du suchst nämlich nicht über 6.000 Datensätze, sondern über 1.048.576 und das jedes Mal.
3. Sortiere beide Tabellen nach dem Suchkriterium.

Das sollte ein wenig Performance bringen, auch wenn ich das seltsam finde. Ich habe versucht, das nachzustellen. Bei 1.000.000 Zieldatensätzen und 7.000, in denen gesucht wird, hat das bei mir ein paar Sekunden gedauert.

Liebe Grüße

Erik
Mitglied: Balgor
Balgor 05.09.2019 um 16:52:17 Uhr
Goto Top
Ist jetzt nicht Brandneu der Rechner, 4 Jahre alt, aber in der Zeit mehrfach von innen gereinigt worden. Würde ihm die Schulnote 2 geben für Sauberkeit.
Steht auch ziemlich frei, alleine, mitten im Raum.

Das ist auch kein Hochleistungsrechner,Win7, i3-4160 3.60GHz, 4gb Ram, Onboard-Grafik, für alles andere womit ich zutun habe reicht der dicke, nur diese Formel im Zusammenhang mit dieser Datei bricht ihm das Genick.

Ich würde den Fokus doch eher auf alternativen zur Formel legen.

Ursprünglich habe ich das mit Query gemacht, ist allerdings viel zu umständlich wenn ich das am laufenden Band machen muss. Frisst viel mehr Zeit als die Formel, mehr Schritte die ich nicht automatisieren kann, welche mehr Fehler bedeuten können.
Mitglied: Balgor
Balgor 05.09.2019 um 16:58:30 Uhr
Goto Top
Das werde ich morgen mal versuchen.

Ich hätte aber gedacht, da ich ja nur Spalte 35 haben will, dass er die anderen gar nicht beachtet. Insgesamt haben die Listen 80 Spalten, welche aber unterschiedlich gefüllt sind.
Sortiert sind beide bereits nach dem Suchkriterium.

Sekunden klingt toll face-smile
Mitglied: erikro
erikro 05.09.2019 um 17:02:06 Uhr
Goto Top
Moin,

Zitat von @Balgor:

Das werde ich morgen mal versuchen.

Ich hätte aber gedacht, da ich ja nur Spalte 35 haben will, dass er die anderen gar nicht beachtet. Insgesamt haben die Listen 80 Spalten, welche aber unterschiedlich gefüllt sind.

Wenn die Suchmatrix 35 Spalten hat, dann muss er auch 35 Spalten laden. face-wink

Wenn das nicht besser wird, würde ich mal über eine Lösung mit der Powershell nachdenken.

Liebe Grüße

Erik
Mitglied: Guenther45
Guenther45 05.09.2019 um 17:07:25 Uhr
Goto Top
Moin,
wenn du mindestens Excel 2010 hast, dann sollte das mit Power Query sehr gut und erheblich besserer Performance lösbar sein. Beispiele, wie das gehen kann: http://www.excel-ist-sexy.de/pqq-pq-verweis-statt-sverweis/ oder etwas ausführlicher hier: http://www.excel-ist-sexy.de/pqq-sverweis-nach-beiden-seiten/

Gruß
Günther
Mitglied: Pjordorf
Pjordorf 05.09.2019 um 19:03:59 Uhr
Goto Top
Hallo,

Zitat von @Balgor:
Ich hätte aber gedacht, da ich ja nur Spalte 35 haben will, dass er die anderen gar nicht beachtet. Insgesamt haben die Listen 80 Spalten, welche aber unterschiedlich gefüllt sind.
Sortiert sind beide bereits nach dem Suchkriterium.
Warum dauert meine Adressuche so lange? Ich will doch nur die mit den Namchname Holperdinger haben, und der Ort Kleinkleckersdorf ist auch der Weltliste enthalten. Warum sucht er dann in Moskau und Paris und NewYork und Passadena und Mexiko und und und.
Du musst einen Rechner schon genau sagen was du erwartest, KI ist noch lange nicht soweit es zu erkennen das du die anderen Spalten nur als Platzhalter nimmst und er nur in einer Spalte suchen soll. Nachdenken statt denken hätte dir hier geholfen. Und ein 4 Jahre alter I3-4160 ist wirklich noch nicht so alt das er dein Problem ist face-smile

Gruß,
Peter
Mitglied: erikro
erikro 06.09.2019 um 08:57:20 Uhr
Goto Top
Zitat von @Guenther45:

Moin,
wenn du mindestens Excel 2010 hast, dann sollte das mit Power Query sehr gut und erheblich besserer Performance lösbar sein. Beispiele, wie das gehen kann: http://www.excel-ist-sexy.de/pqq-pq-verweis-statt-sverweis/ oder etwas ausführlicher hier: http://www.excel-ist-sexy.de/pqq-sverweis-nach-beiden-seiten/

Gruß
Günther

Das wäre auch mein nächste Vorschlag gewesen.
Mitglied: muftypeter
muftypeter 06.09.2019 um 09:26:02 Uhr
Goto Top
Hallo,
das eine ist EXCEL, das andere die Hardware.

Der Warnton sagt nur, daß eine im Bios eingestellte Warnschwelle erreicht ist. Hier mal prüfen, ab wann gewarnt wird. Nach 4 Jahren ist die Hardware auch nicht mehr taufrisch, immerhin scheint er auch von innen gepflegt zu werden.

Evtl. läßt sich das XLS ja optimieren. Ich würde auch den Arbeitsspeicher kontrollieren, evtl. reicht der nicht aus, XLS hat da gerne bei größeren Sachen etwas mehr.

Grüße Peter
Mitglied: Balgor
Balgor 06.09.2019 um 09:40:34 Uhr
Goto Top
Moin,

ich habe den Artikelstamm jetzt mal auf nur 2 Spalten reduziert, die Bezeichnung und dem Preis. Gefühlt ist es etwas schneller, allerdings immer noch Lichtjahre von Sekunden entfernt. Grob hochgerechnet würde es bei gleichbleibender Geschwindigkeit immer noch ca. 45 Minuten dauern.

Allerdings tritt nach ca. 3-4 Minuten wieder das Piepen auf, daher Abbruch meinerseits.

So langsam geht leider der Ursprungsgedanke der Formel wieder den Bach runter, wenn ich so viel Vorarbeit leisten muss. Bin ja froh, dass es bei kleineren Preislisten in wenigen Sekunden erledigt ist. Datei öffnen, Formel reinkopieren, runterziehen, paar Sekunden warten und fertig. Schon kann ich weiter arbeiten.

Verwenden tue ich übrigens Office 2013.
Den Vorschlag von Guenther45 werde ich mir nächste Woche mal anschauen, heute steht erst mal etwas anderes an.

Vielen Dank soweit!
Mitglied: TheJoker2305
TheJoker2305 06.09.2019 um 13:01:12 Uhr
Goto Top
Hallo zusammen,

ich würde bei diese Konstellation ein MS SQL Server Express verewenden.
Mit Talend open Studio die neuen Preislisten immer automatisch in eine Preishistorietabelle laden.

Dann mit einer View die Daten per Excel Datenbankverbindung als fertig berechnete Werte ausgeben oder auch über Talend Open Studio die Exceldatei direkt mit den neuen Daten befüttern.

Das kostet zar alles etwas Einrichtungszeit, aber es ist nachhaltiger und im täglichen Einsatz um Welten schneller.

Es ist immerhin Excel - auch wenn man bis zu 1.048.576 Zeilen und 65535 Spalten zur Verfügung hat, ist es noch lange keine Datenbank.
Die Datenbanktabellen kann man dann mit einerm Index zu Beschleunigung der Suche ausstatten.
mit dem TOS (Talend Open Studio) ist auch ein automatischer Versand / Updload der fertigen Liste möglich.

Greetz,
thejoker2305
Mitglied: Balgor
Balgor 10.09.2019 um 15:14:22 Uhr
Goto Top
Moin,

so, ich habe das mit jetzt Power Query hinbekommen, hätte ich mit dem normalen Query aber auch machen können. Wobei ich zugeben muss, dass die Handhabung von Power Query etwas angenehmer ist und nach kurzer Einfindungsphase auch schneller von der Hand ging.

Bei kleineren Listen werde ich weiterhin meine Formel verwenden, da sie insgesamt viel schneller geht und keine Vorbereitung benötigt.
Wenn die Listen aber wieder so groß werden, was Gott sei Dank nicht oft vor kommt, dann werde ich es wieder mit Power Query machen.

Trotzdem Schade, dass mir meine Kombination aus Hardware und Software einen strich durch die Rechnung macht.
Mitglied: Guenther45
Guenther45 10.09.2019 aktualisiert um 19:00:36 Uhr
Goto Top
Zitat von @Balgor:
Trotzdem Schade, dass mir meine Kombination aus Hardware und Software einen strich durch die Rechnung macht.

Ebenfalls schade, dass du kein Wort des Dankes für die kostenlose und zielführende Hilfe gefunden hast! Hier antworten keine Angestellten von MS sondern freiwillige Helfer aus der "freien Wildbahn". face-sad
Mitglied: Pjordorf
Pjordorf 10.09.2019 um 22:21:11 Uhr
Goto Top
Hallo,

Zitat von @Guenther45:
Ebenfalls schade, dass du kein Wort des Dankes für die kostenlose und zielführende Hilfe gefunden hast! Hier antworten keine Angestellten von MS sondern freiwillige Helfer aus der "freien Wildbahn". face-sad
Und ebenfalls schade das es vom TO noch nicht mal hierfür reicht... Wie kann ich einen Beitrag als gelöst markieren?

Gruß,
Peter
Mitglied: Balgor
Balgor 11.09.2019 aktualisiert um 09:33:47 Uhr
Goto Top
Hallo,

Zitat von @Pjordorf:

Hallo,

Und ebenfalls schade das es vom TO noch nicht mal hierfür reicht... Wie kann ich einen Beitrag als gelöst markieren?

Gruß,
Peter

Der Grund dafür ist ganz einfach. Ich bin noch nicht dazu gekommen. Ich haderte mit mir das Thema als "gelöst" zu markieren, da es nicht vollends zufriedenstellend gelöst ist. "erledigt" trifft es ganz gut, war mir aber entgangen das diese Möglichkeit besteht. Dies werde ich noch nachholen

Zitat von @Guenther45:
Ebenfalls schade, dass du kein Wort des Dankes für die kostenlose und zielführende Hilfe gefunden hast! Hier antworten keine Angestellten von MS sondern freiwillige Helfer aus der "freien Wildbahn". face-sad

In dem ersten Absatz ist durchaus Anerkennung für den Tipp enthalten, lediglich "das Wort des Dankes" nicht, dem stimme ich zu. Dafür entschuldige ich mich und bedanke mich hiermit nochmals in ausführlicherer Form.

Danke, an alle die zur Erledigung meines Problems beigetragen haben!

Edit:
Eine Möglichkeit das Thema auf "erledigt" zu setzen sehe ich trotzdem noch nicht face-sad