Office: Performance bei vielen SVERWEIS-Funktionen & Datei-Design für große Datenmengen

Hallo miteinander,

ich plane aktuell, eine größere Excel-Datei zu erstellen und habe vorab ein paar grundlegende Fragen zur Performance und zum Aufbau.

Aufbau der Datei (geplant):

• 3 bis 5 Master-Tabellen, jeweils auf einem eigenen Tabellenblatt.
  • Jede Master-Tabelle enthält:
    • Nummern oder Suchbegriffe
    • Längere Texte oder Zahlen
    • Die Daten sind in „intelligenten Tabellen“ (also als strukturierte Tabellen mit Überschriften) organisiert.
• Ein Blatt „Hilfe“, auf dem die Datei erklärt wird sowie wichtige Ansprechpartner und Hinweise genannt sind.
• Ein „Grunddaten“-Blatt, auf dem einmalig allgemeine Kundendaten hinterlegt werden.
• Zusätzlich ein Angebotsblatt, das pro Artikel kopiert wird.
  • Eine Excel-Datei steht immer für genau einen Kunden.
  • Je nachdem, wie viele Angebote ein Kunde hat, wird dieses Blatt mehrfach kopiert.
  • Auf diesem Blatt sollen:
    • Angebotstexte automatisch generiert werden
    • Preise auf Basis der Masterdaten berechnet werden

Technischer Hintergrund & meine Frage:
Auf den Angebotsblättern verwende ich viele SVERWEIS-Funktionen, um Texte und Preise aus den Master-Tabellen zu holen.
Beispiel:

• In Spalte A wird ein Begriff eingegeben (z. B. Artikelnummer)
• In Spalte B wird per SVERWEIS der passende Text oder Preis gesucht und angezeig

Das wiederholt sich z. B. von A1 bis A100 (bzw. B1 bis B100).

Problem:
Wenn ich nur eine Zelle in diesem Bereich ändere, verarbeitet Excel alle SVERWEIS-Funktionen neu – auch die, die eigentlich leer sind oder keinen Wert enthalten.
Das kostet Zeit und Ressourcen, vor allem bei vielen Blättern.

Idee:
Ich überlege daher, die SVERWEIS-Funktion mit einer WENN-Abfrage zu kombinieren:

Code:

=WENN(A1<>"";SVERWEIS(A1;...);"")

Meine Fragen dazu:

1. Bringt diese WENN-Abfrage wirklich eine spürbare Entlastung für Excel?
   Oder wird der SVERWEIS trotzdem im Hintergrund berechnet, weil Excel auf alles vorbereitet sein will?
2. Wie kann man in einem solchen Fall generell Ressourcen sparen?
   Ich möchte verhindern, dass die Datei beim Arbeiten ständig hängt oder langsam wird.
3. Gibt es mit Microsoft 365 vielleicht eine Alternative zu SVERWEIS, die performanter ist?
   (z. B. XVERWEIS)
4. Sonstige Tipps für große Excel-Dateien mit vielen dynamischen Blättern und Verweisen?

Rahmenbedingungen:

• Ich schätze, dass pro Angebotsblatt ca. 200-300 SVERWEISE verwendet werden.
• Bei einem mittelgroßen Kunden wären das ca. 60–80 Angebotsblätter.
• Das macht insgesamt über 16.000 bis 24.000 SVERWEIS-Formeln allein auf diesen Blättern.

Makros/VBA sind bei uns in der Firma nur bedingt erlaubt – die IT-Abteilung sieht das eher kritisch. Wenn es sich nicht vermeiden lässt, kann ich zwar ein kleines Makro schreiben, aber das sollte die Ausnahme bleiben.

Ich bin noch ganz am Anfang des Projekts – daher freue ich mich über grundsätzliche Empfehlungen, bevor ich mit dem Aufbau beginne.

Viele Grüße
Mücke

 

Hi,

3-5 Mastertabellen? Das sind wohl 2-4 zu viel.
Auswertung macht man z.B. mit Filter oder PQ.

Sollte eigentlich kein Problem sein, zudem bei intelligenten Tabellen keine Formel auf Vorrat angelegt werden.
Aber, ohne ein Muster wird das nur ein Stochern im Nebel.
Als Hilfe:
https://praxistipps.chip.de/excel-als-datenbank-nutzen-so-gehts_35414
Erstellen eines Datenmodells in Excel - Microsoft-Support

 

Hey zusammen,

ich wollte kurz den Hintergrund zu meiner Idee mit den Master-Tabellen und Angebotsblättern erläutern.

Die Master-Tabellen enthalten nur Daten – es wird dort nichts berechnet. Beispiele:

MasterTabelle Einkauf:

• Interne Artikelnummer
• Lieferanten-Artikelnummer
• Lieferantenname
• Preisdatum
• Preis netto
• Preis pro Einheit
• Versandkosten netto
  
  
• Versandkosten pro Einheit
• Sonstige Kosten netto
• Sonstige Kosten pro Einheit

MasterTabelle Arbeitsplätze:

• Arbeitsplatznummer
• Bezeichnung
• Maschinenbezeichnung
• Kosten pro Stunde (netto)
• Wartungskosten pro Jahr (netto)

Diese Tabellen dienen also als zentrale Datenquelle, in denen Werte eingetragen und ggf. angepasst oder erweitert werden.

Im Angebotsblatt können dann in nicht gesperrten Zellen manuell Daten eingetragen werden. In den gesperrten Zellen wird per SVERWEIS auf die Master-Tabellen zugegriffen, um automatisch Werte wie Preise, Kosten etc. zu übernehmen.

Da wir die Angebote inklusive Kalkulation, Arbeitsablaufbeschreibung etc. vollständig archivieren möchten (nicht nur als Ausdruck oder PDF), hatte ich vorgesehen, für jeden Artikel eines Kunden ein eigenes Angebotsblatt zu kopieren und zu speichern.

Zusätzlich plane ich ein Übersichtsblatt, das anzeigt, bei welchen Artikeln sich aufgrund von Preisänderungen in den Master-Tabellen etwas geändert hat – damit man schnell erkennt, für welche Kunden/Artikel ein neues Angebot erstellt werden muss.

Aktuell existiert noch keine Musterdatei – das Ganze ist bisher nur ein Konzept, das ich gerade sammle und strukturiere. Ich überlege, welche Infos genau benötigt werden, welche Abfragen sinnvoll sind etc.
Der Grundgedanke ist: Bisher arbeitet jeder sein eigenes System – in Zukunft soll das vereinheitlicht werden.

Deinen Link schaue ich mir noch an !!

Gruß Mücke

 

Hi
ja, die Wenn-Abfrage kann Rechenzeit sparen. Wenn A1 nicht befüllt ist, wird der SVerweis nicht ausgeführt.

wenn man viele SVerweise hat, die auf große Datentabellen verweisen, dann ist die beste Methode zur verbeserung der Performance diese Methode.

- sortiere die Liste des SVerweises (2. Parameter) nach der ersten Spalte aufsteigend.
- verwende dann den SVerweis für sortierte Listen, also 4. Parameter = WAHR
- wenn es möglich ist, dass ein Suchwert nicht vorhanden ist und man dann eine entsprechende Fehlermeldung haben möchte, muss man eine zusätzliche Abfrage einfügen:
=Wenn(SVerweis(A1;Liste;1;wahr)=A1;SVerweis(A1;Liste;Spaltennummer;wahr);"# nicht gefunden")


sollte man mehrere SVerweise mit dem selben Suchbegriff und der selben Liste haben (also unterschiedliche Spalten), dann kann es sinnvoll sein, in einer Hilfsspalte zuerst mit VERGLEICH die Zeilennummer der Fundstelle zu ermitteln und dann die Daten mit INDEX auszulesen. Dann muss die Zeitaufwendige Suche nicht mehrfach ausgeführt werden, sondern für jede Gruppe nur einmal.

Gruß Daniel

 

Oh Mann, stimmt – den INDEX- in Kombination mit dem VERGLEICH-Befehl gibt es ja auch noch!
Wenn ich mich recht erinnere, ist das sogar deutlich schneller und verbraucht weniger Ressourcen.
Wenn ich das dann noch mit einer WENN-Abfrage kombiniere, sodass geprüft wird, ob im Suchfeld überhaupt etwas steht, könnte das wirklich eine saubere Lösung werden.

Und Fehler kann ich auch elegant mit WENNFEHLER abfangen.

Ja, das ist richtig!
Und in diesen „intelligenten Tabellen“ – wie heißen die eigentlich richtig? – ist das tatsächlich nicht der Fall.
Aber im Formular kann ich ja nicht im Voraus sagen, ob in der Stückliste nur ein Teil oder zehn Teile stehen.
Das empfinde ich als ein Performance-Problem.

Ich überlege auch gerade, ob man mit bedingter Formatierung Zeilen ausblenden könnte.
So könnte an der Stelle der Stückliste immer nur eine leere Zeile angezeigt werden – und sobald man dort etwas einträgt, würde automatisch die nächste Zeile eingeblendet.

Das habe ich aber noch nicht getestet.
Allerdings meine ich mich zu erinnern, dass bedingte Formatierungen nur greifen, wenn die Zellen auch sichtbar sind – andernfalls ignoriert Excel das, glaube ich. Bin mir da aber nicht mehr sicher.