Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1832to1836
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Mögliche Gründe für langsame Excel-Datei

Mögliche Gründe für langsame Excel-Datei
10.06.2021 11:11:26
Marc
Hallo liebe Excel-Cracks,
über den Lauf der Zeit habe ich mir immer mehr Knowhow zusammengeklaubt und durch bessere Nutzung von Möglichkeiten wie Dictionary/Arrays usw. und Codeoptimierungen Funktionen von 10min Laufzeit auf 5 Sekunden runtergekriegt, ich glaube deshalb, dass ich inzwischen doch zumindest einigermassen gut VBA-Code programmiere. Nun habe ich für mein Geschäft eine neue Planungsdatei gemacht und habe das Problem, dass das Ganze extrem langsam wurde, und zwar nicht unbedingt die VBA-Code-Teile (aufgrund der teilweisen Komplexität können die effektiv ein wenig Zeit brauchen), sondern mehr das einfache arbeiten in der Datei. Konkret: Eine Zelle ändern und danach in die nächste Zeile springen kann also durchaus mal 5-6 Sekunden in Anspruch nehmen, dazwischen dreht "die Sanduhr".
Zugegeben, die Datei ist relativ gross und komplex:
  • Dateigrösse ist ca. 6.2MB

  • 13 Tabellen

  • Die vor allem relevante Haupttabelle hat 468 Zeilen, dafür aber 220 Spalten mit vielen Index-Formeln (die anderen Tabellen enthalten vor allem Daten und müssen nicht angepasst werden)

  • Es bestehen Verknüpfungen zu 4 externen Tabellen

  • die Datei beinhaltet diversen VBA-Code, ein Teil für den monatlichen Import von Umsätzen, usw. (sollte für den Betrieb eigentlich keine Rolle spielen), ein Teil Suche usw. (und das ist mir klar, dass dies ein wenig bremsen kann)

  • in der relevanten Haupttabelle hat es diverse Checkboxen für die Filtrierung der Daten nach vorgegebenen Kriterien sowie eine "BeforeDoubleClick"-Abfrage, die aber nur auf die ersten 4 Spalten limitiert ist

  • die Datei liegt auf einem Server und mit Zugriff aus Homeoffice wird das sicher auch ein wenig langsamer, in anderen Excel-Dateien vom Server tritt das Problem aber nicht auf

  • Wenn ich die automatische Formelauswertung ausschalte, dann ist das Problem weg, das scheint auf die Formeln in der Tabelle als Problem hinzudeuten. Der Grossteil besteht aber nur aus Daten, die Formeln machen meiner Ansicht nach weniger als 5% aus.
    Trotz allem kann ich nicht verstehen, wieso nur schon nach einer Änderung in eine andere Zelle zu springen so lange dauert? Ich weiss, das sind nicht viele Infos (sorry, die Datei kann ich aus Datenschutzgründen schlicht nicht teilen), ich erwarte von Euch keine Wunder. Aber vielleicht hat jemand von Euch eine Idee, an was das liegen könnte resp. wie ich mich allenfalls an den Fehler rantasten kann?
    Danke im Voraus für Eure Rückmeldungen!
    Lg Marc

    22
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: Vermutung:Index
    10.06.2021 11:20:34
    Fennek
    Hallo,
    schön, dass Du VBA so beschleunigen konntest.
    Als Vermutung:
    Viele Formeln machen Excel schnell langsam. Die Index-Funktion ist zwar "genial" und für kleine Tabellen hilfreich, aber bei der genannten Größe könnte das eine Bremse sein.
    Versuche die Calculation auf "manual" umzustellen.
    mfg
    AW: Vermutung:Index
    10.06.2021 11:30:30
    Marc
    Hallo Fennek,
    ich wollte mit meiner "Beschleunigungs-Protzerei" ja nur aufzeigen, dass ich zwar ein Amateur bin und mir der entsprechenden Limitierungen durchaus bewusst bin, dass ich aber auch kein Anfänger mehr bin, der einfach mal Euch Experten die Arbeit machen lassen will weil mein Anfängercode einfach besch...eiden ist. Aber naja, nach meiner Beschreibung muss ich mir von Dir wahrscheinlich ein wenig gutmütigen Spott über meine Beschleunigungsversuche durchaus gefallen lassen;-).
    Aber danke für die Antwort, das deckt sich ein wenig mit meiner Vermutung. Die Umstellung auf "manual" habe ich probiert, und dann ist das Problem effektiv weg. Und Deine Aussage, dass die Index-Funktion eher langsam ist bestätigt das auch, ich habe vor allem mit Index gearbeitet.
    Ich lasse die Frage absichtlich noch offen, vielleicht hat ja noch jemand sonst einen guten Input, aber Du hast mir auf jeden Fall schon extrem weitergeholfen, danke dafür!
    Anzeige
    AW: Kein Spott, sondern ein Kompliment
    10.06.2021 11:40:17
    Fennek
    Hallo,
    der Vorschlag wäre alle Formeln mit Index durch VBA zu ersetzen.
    mfg
    AW: Kein Spott, sondern ein Kompliment
    10.06.2021 11:46:10
    Marc
    Hey Fennek,
    danke, dann nehme ich es als Kompliment:-)
    Danke für den Vorschlag, das wird leider ein wenig schwierig, aber ich schau mal was sich machen lässt.
    Lg
    Marc
    AW: es nur INDEX() zuweisen, ist unbegründet ...
    10.06.2021 13:23:46
    neopa
    Hallo Fennek, hallo Marc,
    ... denn es ist u.a. nicht bekannt, wie Marc die Argumente für seine INDEX()-Formeln definiert hat. Ohne diese zu kennen, sollte man mE eine derartige Aussage nicht so bestimmend treffen.
    Die Ursachen für eine derartig langsame Auswertung kann mE nur bei Kenntnis des vorhandenen in Gänze gesehen werden. Und da ist außer der Dateigröße und das INDEX()-Formel in dieser vorkommen, so gut wie nichts wirklich bekannt.
    Gruß Werner
    .. , - ...
    Anzeige
    AW: es nur INDEX() zuweisen, ist unbegründet ...
    10.06.2021 13:45:04
    Marc
    Hallo Werner,
    stimmt natürlich. Da meine Kollegen gerne mal die Struktur der Datei verändern, habe ich in der (ausgeblendeten) Zeile 1 für jede Spalte eine Variable (Beschreibung der Spalte) definiert, so dass die Formeln nicht verworfen werden, wenn eine zusätzliche Spalte irgendwo reingeworfen wird. Ich frage deshalb in den notwendigen Formeln für z.B. Monatsumsatz in der Index-Funktion mit Vergleich einerseits die Zeile ab (das ersetze ich jetzt mit "Zeile()" in der Hoffnung, dass dies besser ist), aber vor allem frage ich die Spalte in Zeile 1 gemäss Variable ab. Auch hier versuche ich jetzt, den Vergleich zu eliminieren und hoffe, dass es dann besser flutscht.
    Somit sieht so ne Formel etwa so aus:
    
    =INDEX($1:$1048576;VERGLEICH($B24;$B:$B;0);VERGLEICH("UMSATZ";$1:$1;0)) * INDEX($1:$1048576; VERGLEICH($B24;$B:$B;0);VERGLEICH("MARGE";$1:$1;0) 
    
    Gibt Dir das einen besseren Einblick?
    Lg
    Marc
    Anzeige
    AW: hierzu noch ...
    10.06.2021 14:33:27
    neopa
    Hallo Marc,
    ... ergänzend zu den Aussagen, die Daniel Dir bereits vermittelt hat: Aus Deinen bisherigen Aussagen entnahm ich, dass Deine INDEX()-Formel sich in der "relevanten Haupttabelle" mit nur ca 470 Zeilen stehen, Wozu wertest Du dann den gesamten Zeilenbereich aus? Außerdem empfiehlt es sich, die Datentabelle dorrt mit der Funktion als "Als Tabelle formatieren" in eine "intelligente" zu wandeln und die Auswertung auf deren eindeutige Spaltenbezeichnungen zu beziehen. Eingefügte neue Datensätze werden dann auch so ohne Formelanpassung automatisch in diese integriert. Auch ist in einer solchen Tabelle eine Sortierung einfacher realisierbar.
    Gruß Werner
    .. , - ...
    Anzeige
    AW: hierzu noch ...
    10.06.2021 14:42:46
    Marc
    Hallo Werner,
    auch das ist ein guter Input (ich werde förmlich zugeschüttet, ich komme mir aktuell ziemlich doof vor;-). Werde ich umgehend ausprobieren.
    Danke und Gruss
    Marc
    AW: und ...
    10.06.2021 14:51:04
    neopa
    Hallo Peter,
    ... wenn Du schon am "probieren" bist, hast Du auch schon mal drüber nachgedacht, Deine gesamte Auswertung und auch Datenzusammenstellung evtl. mit PowerQuery Fuinktionalität vorzunehmen?
    Gruß Werner
    .. , - ...
    AW: und ...
    10.06.2021 15:02:04
    Marc
    Ich denke, genau solche Überlegungen trennen mich noch von der Einstufung "sehr gut" bei Excel;-). Nein, habe ich nicht, aber auch das wird zu meiner Freizeitlektüre dazukommen, "again what learned" frei nach Lothar Matthäus.
    Danke für den Input.
    Lg Marc
    Anzeige
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 11:43:05
    Daniel
    Hi
    Index ist nicht das Problem.
    Funktionen, die bei großen Datenmengen viel Rechenzeit erfordern, sind beispielsweise
    - SVerweis/Vergleich mit genauer Übereinstimmung
    - SummeWenn(s) sowie alle weiteren -wenn(s) funktionen
    - Summenprodukt
    - Alle Matrixberechnungen
    Kritisch zu betrachten sind in diesem Zusammenhang auch die Wenn(s)-Funktionen, wenn sue auf ganze Spalten verweisen.
    Excel rechnet dann zwar nur den genutzten Bereich durch, dieser kann aber manchmal erheblich größer sein als erforderlich, z.b. wenn Formatierungen zu weit gezogen ist.
    Man erkennt das, wenn man die Scrollbar ganz nach unten zieht und dann nicht beim Ende der Tabelle landet, sondern deutlich weiter unten.
    Weiterhin problematisch können Volatile Funktionen sein (Indirekt, Bereich.Verschieben, Heute(), jetzt(), Zufallszahl und -Bereich).
    Formeln, die solche Funktionen enthalten oder auf solche referenzieren, werden bei jeder Änderung in Excel neu berechnet, egal was du änderst.
    Das mal für dich zur Info.
    Gruß Daniel
    Anzeige
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 12:59:32
    Marc
    Hallo Daniel,
    wahrscheinlich liegt genau da das Problem, ich nutze für die Index-Funktion sowohl für Zeile wie auch Spalte die Vergleichsfunktion, dazu verwende ich in der Tabelle Summewenns und Summenprodukte:-(. Aber immerhin weiss ich jetzt, was es ist und kann z.B. in den Index-Formeln zumindest für die Spalten mal die Vergleich-Funktion eliminieren.
    Den genutzten Bereich kann ich zum Glück ausschliessen, da habe ich extrem drauf geschaut, alle Formeln werden automatisch nur für den notwendigen Bereich erzeugt.
    Dein Hinweis für die volatilen Funktionen betrifft mich auch nicht, finde ich aber rein Informativ sehr interessant für die Zukunft (habe das in anderen Tabellen im Einsatz).
    Danke Dir auf jeden Fall für die hilfreiche Rückmeldung!
    Lg
    Marc
    Anzeige
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 13:43:25
    Daniel
    Hi
    Für Vergleich und SVerweis gibts mehrere Möglichkeiten, der Optimierung:
    wenn du mehrfach gleichen Suchwert und gleichen Suchbereich verwendest (beim SVerweis variiert nur die Spalte), dann ersetze SVerweis durch das funktionsgleiche Index(...;Vergleich()) und lagerer den Vergleich in eine Hilfszelle aus, die dann von mehreren Indexfunktionen verwendet wird.
    eine weitere Optimierungsmöglichkeit bei großen Datenmengen ist folgende:
    wenn die Suche auf genaue Übereinstimmung geht, dann mache folgendes wenn möglich:
    - sortiere die Quellliste nach der Suchspalte aufsteigend und verwende die Option für sortierte Listen (SVerweis 4. Parameter = wahr/1)
    - wenn du trotzdem die Prüfung auf Übereinstimmung brauchst, verwende:
    =Wenn(SVerweis(Suchbegriff;SuchSpalte;1;Wahr)=Suchbegriff;SVerweis(Suchbegriff;SuchMatrix;Spalte;Wahr);"")
    das ist bei bei großen Datenmengen um welten schneller als die Suche in unsortierten Daten.
    Gruß Daniel
    Anzeige
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 13:56:26
    Marc
    Hey Daniel,
    Deinen ersten Vorschlag "Auslagerung in Hilfszelle" habe ich bereits angefangen umzusetzen. Ich nutze nun in der Art
    
    =Index($1:$1048576;Zeile();Hilfszelle)
    
    Betreffend Optimierungen: Sortieren kann ich leider nicht, das würde zu grosse Probleme mit den Kollegen geben.
    Den Vorschlag
    
    =Wenn(SVerweis(Suchbegriff;SuchSpalte;1;Wahr)=Suchbegriff;SVerweis(Suchbegriff;SuchMatrix;Spalte; Wahr);"") 
    
    habe ich noch nicht ganz kapiert, schau das aber am konkreten Beispiel an (sollte ich so nachbauen können) .
    Danke Dir
    Marc
    Anzeige
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 14:10:49
    Daniel
    das was du nicht kapierst, hängt mir der Sortierung der Listen zusammen.
    wenn du die schnelle Variante des SVerweises für sortierte Daten verwendest, hast du das Problem, dass du keine Fehlermeldung (#NV) bekommst wenn der Suchbegriff nicht vorhanden ist, stattdessen wird der nächstkleinere Wert als Ergebnis verwendet.
    Das kann auch mal unerwünscht sein und mal will diesen Hinweis haben.
    mit dieser Formel bekommst du dann auch mit dem sortierten SVerweis eine Hinweismeldung, wenn der Suchbegriff nicht vorhanden ist (wobei du diese selber bestimmen kannst).
    somit kombinierst du dann beide Vorteile: Schnelle Suche + Hinweis bei nicht vorhandenem Suchbegriff.
    Gruß Daniel
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 14:27:12
    Marc
    Aha, jetzt habe ich verstanden, logisch: SVerweis mit Parameter Wahr oder 1 nimmt die nächste Annäherung wenn der exakte Wert nicht vorhanden (deshalb ist auch die aufsteigende Sortierung notwendig). Mit dem Vergleich "=Suchbegriff" stellst Du gleichzeitig sicher, dass auch wirklich ein Übereinstimmendes Ergebnis vorliegt. Soweit logisch, merk ich mir.
    In diesem Fall kann ich es aber leider effektiv nicht brauchen, da die freie Sortierung der Daten wie gesagt eine ganz wichtige Voraussetzung ist. Ich werde gelyncht, wenn ich den Leuten das wegnehmen würde...
    Lg
    Marc
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 16:06:37
    Daniel
    man kann nur das machen was geht.
    der Effekt ist halt schon bemerkenswert.
    Wenn die Datenmenge X = 2^N ist, dann ist im statistischen Mittel die Suchzeit für die unsortierte Suche proprtional X, für die sortierte Suche hingegen proprortional N.
    was das bedeutet, kann man sich ja leicht ausrechnen.
    Wem jetzt noch die Legende vom Erfinder des Schachspiels in den Sinn kommt, der weiß jetzt, warum man diese Geschichte immer noch erzählt.
    Gruß Daniel
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 13:16:34
    Rudi
    Hallo,
    noch ne mögliche Bremse: Bedingte Formatierungen
    Gruß
    Rudi
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 13:33:46
    Marc
    Hallo Rudi,
    et voila, auch das kann ich anbieten;-). So wies aussieht, habe ich effektiv alles mögliche, was bremst, auch reingenommen:-(.
    Danke für die Info, auch das gibt mir einen Ansatzpunkt wie ich beschleunigen kann.
    Lg
    Marc
    AW: Mögliche Gründe für langsame Excel-Datei
    10.06.2021 13:57:43
    Marc
    Schau ich gleich an, danke wiederum.
    Lg
    Marc
    AW: und dazu auch noch ...
    10.06.2021 15:07:32
    neopa
    Hallo Peter,
    ... es kann sich durch nachträgliches Einfügen/Einkopieren von Zeilen bzw. auch nur Zellen in Bereiche mit bedingten Formatierungsdefinitionen sehr schnell ein "aufschaukeln" an entsprechenden (ungewollt) zusätzlichen bed. Formatierungsregeln ergeben. Excel ist nämlich nicht in der Lage zu erkennen, ob es sich bei eingefügten/einkopierten Zellen mit gleichen Regeln wirklich um eine gleiche Regel handelt und erzeugt deshalb automatisch zusätzliche neue Regeln mit entsprechend ab-/eingrenzenden Bereichsdefinitionen und auch entsprechend darauf angepassten Formeldefinitionen.
    Gruß Werner
    .. , - ...

    Links zu Excel-Dialogen

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige