11
EXCEL 2016 xlsx-Datei öffnet sich nicht da sehr groß und viele SVERWEIS enthalten
Hallo,
ich habe eine xlsx-Datei (ca. 20MB) in EXCEL 2016 erstellt, welche sehr viele Arbeitsblätter (ca. 120 Stück) enthält.
Das erste Arbeitsblatt ist eine Tabelle mit ca. 150 Spalten und 130 Zeilen.
Alle folgenden Arbeitsblätter sind Formulare welche sich per SVERWEIS aus dem ersten Datenblatt die Werte holen.
Für jeden neuen Auftrag wir ein neues Arbeitsblatt angelegt welches wieder per SVERWEIS auf das erste Arbeitsblatt zugreift und Werte ausließt.
Das Ganze hat auch bis zum 119. Arbeitsblatt funktioniert, ab dem 120. Arbeitsblatt ließ sich die Datei noch speichern aber nicht mehr öffnen.
Eine WWW-Suche nach dem Problem deutet darauf hin das die Funktion SVERWEIS sehr viel Rechenleistung und Speicher benötigt und ab einer bestimmten Menge von SVERWEIS mit Berechnungen nicht mehr hinterherkommt.
Dies ist die Formel
=SVERWEIS($AF$10;Blatt1!$D$5:$BT$109;SPALTEN(Blatt1!$D:$H);FALSCH)
die in jedem Tabellenblatt ca. 20-mal aufgerufen wird.
Kann ich diese Formel mit anderen Funktionen ersetzten, welche nicht das oben beschriebene Problem verursachen?
Oder kann ich einen anderen Weg gehen um das Problem zu lösen?
Ich habe versucht die Datei unter Excel 2016 32 und 64 Bit zu öffnen, beides funktioniert nicht.
Um weiterarbeiten zu können habe ich mit einem BackUp gearbeitet, in welchem ich jetzt diese Formel austauschen wöllte.
Auch ein Versuch in EXCEL unter Formeln > Berechnungsoptionen > von Automatisch auf Manuell umzustellen hat keinen Erfolg gebracht.
Kann eventuell ein Update von EXCEL 2016 oder kann EXCEL 2013 mit der SVERWEIS Funktion besser umgehen?
Vielen Dank Hobi84.
ich habe eine xlsx-Datei (ca. 20MB) in EXCEL 2016 erstellt, welche sehr viele Arbeitsblätter (ca. 120 Stück) enthält.
Das erste Arbeitsblatt ist eine Tabelle mit ca. 150 Spalten und 130 Zeilen.
Alle folgenden Arbeitsblätter sind Formulare welche sich per SVERWEIS aus dem ersten Datenblatt die Werte holen.
Für jeden neuen Auftrag wir ein neues Arbeitsblatt angelegt welches wieder per SVERWEIS auf das erste Arbeitsblatt zugreift und Werte ausließt.
Das Ganze hat auch bis zum 119. Arbeitsblatt funktioniert, ab dem 120. Arbeitsblatt ließ sich die Datei noch speichern aber nicht mehr öffnen.
Eine WWW-Suche nach dem Problem deutet darauf hin das die Funktion SVERWEIS sehr viel Rechenleistung und Speicher benötigt und ab einer bestimmten Menge von SVERWEIS mit Berechnungen nicht mehr hinterherkommt.
Dies ist die Formel
=SVERWEIS($AF$10;Blatt1!$D$5:$BT$109;SPALTEN(Blatt1!$D:$H);FALSCH)
die in jedem Tabellenblatt ca. 20-mal aufgerufen wird.
Kann ich diese Formel mit anderen Funktionen ersetzten, welche nicht das oben beschriebene Problem verursachen?
Oder kann ich einen anderen Weg gehen um das Problem zu lösen?
Ich habe versucht die Datei unter Excel 2016 32 und 64 Bit zu öffnen, beides funktioniert nicht.
Um weiterarbeiten zu können habe ich mit einem BackUp gearbeitet, in welchem ich jetzt diese Formel austauschen wöllte.
Auch ein Versuch in EXCEL unter Formeln > Berechnungsoptionen > von Automatisch auf Manuell umzustellen hat keinen Erfolg gebracht.
Kann eventuell ein Update von EXCEL 2016 oder kann EXCEL 2013 mit der SVERWEIS Funktion besser umgehen?
Vielen Dank Hobi84.
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 394267
Url: https://administrator.de/contentid/394267
Printed on: April 26, 2024 at 14:04 o'clock
11 Comments
Latest comment
Hallo,
https://support.office.com/en-us/article/excel-specifications-and-limits ...
VLookup = SVerweis
https://exceljet.net/things-you-should-know-about-vlookup
https://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match- ...
https://www.quora.com/What-are-the-limitations-of-VLOOKUP
http://eimagine.com/say-goodbye-to-vlookup-and-hello-to-index-match/
https://www.smartsheet.com/vlookup-2010-2013-2016
http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/
https://www.trifacta.com/blog/the-trouble-with-vlookup/
Vielleicht mal von SVerweis weggehen...
Gruß,
Peter
Zitat von @Hobi84:
Kann eventuell ein Update von EXCEL 2016 oder kann EXCEL 2013 mit der SVERWEIS Funktion besser umgehen?
Wie wenig RAM hat dein Rechner?Kann eventuell ein Update von EXCEL 2016 oder kann EXCEL 2013 mit der SVERWEIS Funktion besser umgehen?
https://support.office.com/en-us/article/excel-specifications-and-limits ...
VLookup = SVerweis
https://exceljet.net/things-you-should-know-about-vlookup
https://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match- ...
https://www.quora.com/What-are-the-limitations-of-VLOOKUP
http://eimagine.com/say-goodbye-to-vlookup-and-hello-to-index-match/
https://www.smartsheet.com/vlookup-2010-2013-2016
http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/
https://www.trifacta.com/blog/the-trouble-with-vlookup/
Vielleicht mal von SVerweis weggehen...
Gruß,
Peter
Moin,
ich denke, dass deine Mappe ausgesprochen suboptimal aufgebaut ist.
Ich frage mich (dich), ob da nicht ein AutoFilter wesentlich bessere Dienste leistet.
Und wenn es denn (aus unerfindlichen Gründen) unbedingt >120 Blätter sein müssen, dann würde ich diese automatisch mit Power Query, -> http://www.excel-ist-sexy.de/power-query-das-add-in/ generieren lassen. Da wirst du aber gewiss um die Kaffeepause gebracht, die du bislang wegen der mangelnden Geschwindigkeit einlegen durftest
.
Gruß
Günther
ich denke, dass deine Mappe ausgesprochen suboptimal aufgebaut ist.
Ich frage mich (dich), ob da nicht ein AutoFilter wesentlich bessere Dienste leistet.
Und wenn es denn (aus unerfindlichen Gründen) unbedingt >120 Blätter sein müssen, dann würde ich diese automatisch mit Power Query, -> http://www.excel-ist-sexy.de/power-query-das-add-in/ generieren lassen. Da wirst du aber gewiss um die Kaffeepause gebracht, die du bislang wegen der mangelnden Geschwindigkeit einlegen durftest
.Gruß
Günther
Ganz blöde Frage: Wenn alle SVerweise auf das erste Datenblatt gehen wiso machst du dann nicht das erste Datenblatt als eigene Datei und alle anderen Datenblätter entweder auch als eigene Datei oder in Gruppen in eigene Dateien und machst den SVerweis immer nur auf die Referenzdatei (ehm. erstes Datenblatt). Dann müsstest du immer nur einen Teil aller Datenblätter (und somit auch SVerweise) laden.
Hallo Peter,
könnte ich das Prolem lösen wenn ich alle SVerweise durch ein Konstuckt aus INDEX und VERGLEICH wie hier beschrieben https://www.traens.com/tipps/microsoft/excel-index-und-vergleich.html austausche?
Viele Grüße
Hobi 84
könnte ich das Prolem lösen wenn ich alle SVerweise durch ein Konstuckt aus INDEX und VERGLEICH wie hier beschrieben https://www.traens.com/tipps/microsoft/excel-index-und-vergleich.html austausche?
Viele Grüße
Hobi 84
Hallo,
Gruß,
Peter
Zitat von @Hobi84:
könnte ich das Prolem lösen wenn ich alle SVerweise durch ein Konstuckt aus INDEX und VERGLEICH wie hier beschrieben https://www.traens.com/tipps/microsoft/excel-index-und-vergleich.html austausche?
Warum probierst du es nicht aus? Wir haben (und wir wollen auch nich) deine 20 MB Excel Dateien/Blätter haben. Da musst du schon selbst durch. könnte ich das Prolem lösen wenn ich alle SVerweise durch ein Konstuckt aus INDEX und VERGLEICH wie hier beschrieben https://www.traens.com/tipps/microsoft/excel-index-und-vergleich.html austausche?
Gruß,
Peter
Hi Ukulele,
das habe ich am Anfang so gehabt und hat sich als ungünstig rausgestellt, da eine Änderung z.B. Einfügen von einer Spalte im ersten Datenblatt nicht automatisch sich die Formeln in den anderen Blättern übernimmt.
Viele Grüße
Hobi84.
das habe ich am Anfang so gehabt und hat sich als ungünstig rausgestellt, da eine Änderung z.B. Einfügen von einer Spalte im ersten Datenblatt nicht automatisch sich die Formeln in den anderen Blättern übernimmt.
Viele Grüße
Hobi84.
Hi Peter,
es wäre schon schön zu wissen ob INDEX und VERGLEICH dieselben Probleme verursachen wie SVerweis oder ob es hier funktioniert, da ich ungern in 120 Blättern alle Formeln händisch ändern möchte und dann lohnt sich die Mühe nicht.
Viele Grüße
Hobi84.
es wäre schon schön zu wissen ob INDEX und VERGLEICH dieselben Probleme verursachen wie SVerweis oder ob es hier funktioniert, da ich ungern in 120 Blättern alle Formeln händisch ändern möchte und dann lohnt sich die Mühe nicht.
Viele Grüße
Hobi84.
Hallo Günther,
ich habe jetzt versucht mit Abrufen und Transformieren meine Formeln zu ersetzten, bin aber an der Umsetzung gescheitert.
Hast du mal ein kleines Beispiel für mich wo erklärt wird wie ich den Inhalt aus einer Zelle in Blatt 1 hole wenn im Blatt 2 in der Zelle A1 einen ID angeben wird, welche dann in Blatt 1 anhand der ID in die entsprechende Zeile geht und dort dann in die gewünschte Spalte und dann den Inhalt in Blatt 2 kopiert. So wie es meine Formel mit SVerweis macht =SVERWEIS($AF$10;Blatt1!$D$5:$BT$109;SPALTEN(Blatt1!$D:$H);FALSCH).
Viele Grüße
Hubi84.
ich habe jetzt versucht mit Abrufen und Transformieren meine Formeln zu ersetzten, bin aber an der Umsetzung gescheitert.
Hast du mal ein kleines Beispiel für mich wo erklärt wird wie ich den Inhalt aus einer Zelle in Blatt 1 hole wenn im Blatt 2 in der Zelle A1 einen ID angeben wird, welche dann in Blatt 1 anhand der ID in die entsprechende Zeile geht und dort dann in die gewünschte Spalte und dann den Inhalt in Blatt 2 kopiert. So wie es meine Formel mit SVerweis macht =SVERWEIS($AF$10;Blatt1!$D$5:$BT$109;SPALTEN(Blatt1!$D:$H);FALSCH).
Viele Grüße
Hubi84.
Moin Hubi,
prinzipiell zum Thema SVERWEIS in Power Query: -> http://www.excel-ist-sexy.de/pqq-sverweis-nach-beiden-seiten/
Ansonsten einfach einmal eine kleine Muster-xlsx - Sammlung zusammenstellen und DEUTLICHES Wunschergebnis angeben oder per Formel berechnen lassen (ich quäle mich aber ungern durch lange Formeln, wobei dein SVERWEIS ja noch halbwegs übersichtlich zu sein scheint).
Gruß
Günther
prinzipiell zum Thema SVERWEIS in Power Query: -> http://www.excel-ist-sexy.de/pqq-sverweis-nach-beiden-seiten/
Ansonsten einfach einmal eine kleine Muster-xlsx - Sammlung zusammenstellen und DEUTLICHES Wunschergebnis angeben oder per Formel berechnen lassen (ich quäle mich aber ungern durch lange Formeln, wobei dein SVERWEIS ja noch halbwegs übersichtlich zu sein scheint).
Gruß
Günther
Halllo Günther,
hier mal eine kleine Muster-Datei als Bild.
Kannst du damit was anfagen?
Vielen Dank
Hubi84.
hier mal eine kleine Muster-Datei als Bild.
Kannst du damit was anfagen?
Hubi84.
Moin,
ich kann zumindest etwas damit anfangen, Excel aber nicht. Bildchen machen sich in einer Galerie besser, hier sollten es *.xlsx sein.
Der Aufbau von "Liste" ist so, wie es sinnvoll ist. Das Blatt "Formular" ist nicht gerade so, dass Excel vor Entzücken in Ohnmacht fällt (das hat dann einen anderen Grund).
Ich tendiere stark dazu, dir a) eine andere Anordnung der Auswertung zu empfehlen (prinzipiell eine Liste wie im Blatt Liste) und die komplette Auswertung in 1 Blatt zu legen und dann per Filter oder PivotTable die einzelnen Aufträge zu betrachten.
Gruß
Günther
ich kann zumindest etwas damit anfangen, Excel aber nicht. Bildchen machen sich in einer Galerie besser, hier sollten es *.xlsx sein.
Der Aufbau von "Liste" ist so, wie es sinnvoll ist. Das Blatt "Formular" ist nicht gerade so, dass Excel vor Entzücken in Ohnmacht fällt (das hat dann einen anderen Grund).
Ich tendiere stark dazu, dir a) eine andere Anordnung der Auswertung zu empfehlen (prinzipiell eine Liste wie im Blatt Liste) und die komplette Auswertung in 1 Blatt zu legen und dann per Filter oder PivotTable die einzelnen Aufträge zu betrachten.
Gruß
Günther