Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1768to1772
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1768to1772
1768to1772
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Berechnung identischer Formelfunktion

Berechnung identischer Formelfunktion
30.06.2020 09:05:33
Martin
Hallo,
kann mir vielleicht jemand sagen, ob identische Funktionen innerhalb einer Formel doppelt ausgeführt werden oder ob Excel doppelte Formelfunktionen automatisch erkennt und nur einmal ausführt?
Beispiel: 

=WENN(SVERWEIS(J7;Datenquelle;1)=J7;WENN(SVERWEIS(J7;Datenquelle;2)=0;"";SVERWEIS(J7;   Datenquelle;2));"")

Die Formelfunktion SVERWEIS(J7;Datenquelle;2) ist in der Formel doppelt enthalten. Wird diese auch doppelt ausgeführt oder "erinnert" sich Excel an das Ergebnis bei der ersten Berechnung und verzichtet auf eine zweite Ausführung der Funktion?
Ich bin gerade dabei meine Exceltabellen mit umfangreichen Formeln zu optimieren, daher meine Frage.
Gruß,
Martin

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Berechnung identischer Formelfunktion
30.06.2020 09:22:38
Beverly
Hi Martin,
ich bin zwar kein Formelfachmann, aber vielleicht ginge as so:
=WENN(UND(SVERWEIS(J7;Datenquelle;1)=J7;SVERWEIS(J7;Datenquelle;2)=0);"";SVERWEIS(J7;Datenquelle;2))

AW: Berechnung identischer Formelfunktion
30.06.2020 09:35:19
Martin
Hallo Beverly,
ich danke dir für deine Antwort. Die von mir gepostete Formel funktioniert (ebenso wie deine). Aber auch in deiner Formel ist die Formelfunktion SVERWEIS(J7;Datenquelle;2) doppelt enthalten.
Mir geht es jetzt wirklich ausschließlich um die Frage, ob Excel doppelte Formelfunktionen automatisch erkennt und nur einmal ausführt.
Wenn ich kurz Ausholen darf: Inzwischen habe ich mich von SVERWEIS komplett getrennt und bin auf die VERGLEICH-Funktion in einer Hilfsspalte mit der ermittelten Zeile umgestiegen. Da ich je Zeile über 50 Spaltenabfragen auf die jeweils gleiche Zeile der Quelldaten vornehme, greife ich jetzt mit der Index-Funktion auf die Daten zu und beziehe die Zeilennummer aus der Hilfsspalte. Es würde mich jetzt nur interessieren, ob ich ISTNV innerhalb der Hilfsspalte verwende oder besser in jeder einzelnen Spalte der über 50 Index-Formeln, damit VERGLEICH nicht doppelt ausgeführt wird.
Gruß,
Martin
Anzeige
AW: Berechnung identischer Formelfunktion
30.06.2020 09:40:43
Beverly
Hi Martin,
weshalb sollte die Funktion in meiner Formel doppelt ausgeführt werden? Der zweite Teil wird doch nur berechnet wenn die erste Bedingung nicht erfüllt ist.

Deine Formel liefert doch falsche Ergebnisse
30.06.2020 10:25:27
Martin
Hallo Beverly,
leider muss ich mich korrigieren, dass deine Formel doch falsche Ergebnisse liefert, wenn das Suchkriterium nicht vorhanden ist. Bei meiner Formel wird dann ein "" als Ergebnis ausgegeben, bei deiner Formel wird ein falscher Wert ausgegeben.
Gruß,
Martin
Formelauswertung
30.06.2020 09:44:50
MCO
Hallo Martin!
Ausgehend von der Formelauswertung (unter Formeln/Formelüberwachung) gehe ich davon aus, dass Excel alles immer wieder brav durchrechnet.
Bei so umfangreichen Formel ist zu überlegen, ob man nicht die Formel entweder per VBA setzt und dann durch die Werte ersetzt oder die Werte direkt per VBA errechnet und so die dauernde erneute Berechnung spart. Möglich wäre auch, die Werte in der jeweils geänderten Zeile oder zur Zelle neu zu berechnen.
Gruß, MCO
Anzeige
AW: Formelauswertung
30.06.2020 10:13:56
Martin
Hallo MCO,
da die umfangreichen Quelldaten immer wieder neu aus einer externen CSV-Datei importiert werden, ist eine VBA Lösung eher schwierig umsetzbar bzw. ebenfalls sehr zeitintensiv.
Mit der Berechnung von Excel muss ich mich noch intensiv auseinandersetzen. Kannst du mir zufällig auch sagen, ob Excel bei der Neuberechnung nur das aktuelle Tabellenblatt neu berechnet oder auch alle anderen Tabellenblätter der Arbeitsmappe, auch wenn deren Formeln unabhängig vom aktuellen Arbeitsblatt sind? Ich habe erst jetzt die Tabelleneigenschaft EnableCalculation entdeckt und frage mich, ob ich diese bei den nicht aktiven Tabellenblättern über das Workbook_Sheet(De)Activate auf FALSE bzw. TRUE setzen sollte.
Viele Grüße
Martin
Anzeige
AW: Berechnung identischer Formelfunktion
30.06.2020 09:52:17
Daniel
Hi
Meines Wissen nach wird in einer Formel jede Funktion einzeln berechnet, weshalb es sinnvoll sein kann, eine mehrfach verwendete Funktion oder Teilformel in eine eigene Zelle auszulagern.
Es gibt noch mehr Gründe, warum das sinnvoll ist:
Die eigentliche Formel wird kürzer und übersichtlicher, auch musst du bei nachträglichen Änderungen an dieser Teilformel diese nicht mehrfach durchführen (was fehleranfällig ist) sondern nur einmal.
Was die Performance angeht, da brauchst du dir bei deiner Formel keine Sorgen machen, der SVerweis mit sortierten Daten ist so schnell, dass man den problemlos mehrfach in der Formel haben kann.
Anders sähe es aus, wenn du den SVerweis für unsortierte Daten hättest, der ist bei großen Datenmengen sehr langsam, so dass es dann besser ist ihn auszulagern.
Wenn deine Rückgabewerte Texte sind, kannst du die 0 bei Verweis auf eine Leerzelle durch einfaches Anhängen des Leerstrings verhindern und damit einen SVerweis sparen:
SVerweis(...)&""
Nur bei Zahlen sollte man das nicht machen, weil die Zahlen dadurch Texte werden, was eine weitere Auswertung erschwert.
Gruß Daniel
Anzeige
AW: Berechnung identischer Formelfunktion
30.06.2020 10:02:52
Martin
Hallo Daniel,
du hast mir schon gestern sehr weitergeholfen und ich konnte meine Tabelle unheimlich beschleunigen.
Ich hätte in meiner Aufgabenbeschreibung gleich schreiben sollen, dass ich eigentlich mit VERWEIS in einer Hilfsspalte arbeite. Mir geht es um die Klärung der Frage, ob es besser ist ISTNV in der Hilfsspalte zu verwenden und damit die VERGLEICH-Funktion doppelt auszuführen oder ISTNV(Hilfsspalte) in jeder der Index-Abfragen einzusetzen, wodurch mehr Formelcode erforderlich ist. Verstehst du was ich meine?
Viele Grüße
Martin
AW: Berechnung identischer Formelfunktion
30.06.2020 10:24:34
Daniel
Wenn du das, was ich dir geschrieben habe auch verstanden hast, solltest du eigentlich selber wissen, was warum zu tun ist.
Den NV-Fehler gibts in der Regel nur bei Suche mit genauer Übereinstimmung in unsortierten Daten und die ist Laaaaaaaaaaaaaaaaagsam.
Einfache Abfragen und Vergleiche mit Einzelwerten sind sehr schnell.
Eine Formel in einer Zelle wird dann neu berechnet, wenn sich eine Zelle, die von der Formel verwendet wird, ändert.
Excel merkt sich dieses Formelergebnis der Zelle, so dass diese Formel nicht neu berechnet werden muss, wenn die Formel einer anderen Zelle sich auf diese Zelle bezieht, dann kann das vorhandene Ergebnis verwendet werden ohne Neuberechnung.
Gruß Daniel
Anzeige
AW: Berechnung identischer Formelfunktion
30.06.2020 10:28:18
Martin
Hallo Daniel,
super, ich danke dir für deine Erklärung (auch wenn ich scheinbar etwas auf dem Schlauch stand).
Gruß,
Martin
AW: Berechnung identischer Formelfunktion
30.06.2020 13:46:06
Alex 

Eine Formel in einer Zelle wird dann neu berechnet, wenn sich eine Zelle, die von der Formel verwendet wird, ändert.
Ist zwar grundsätzlich richtig - aber (wie immer!): Vorsicht bei Verallgemeinerungen!
Volatile Funktionen und Formeln, die in bedingten Formatierungen als Bedingung fungieren, werden bei jeder Eingabe bzw. Änderung von Zellwerten neu berechnet.
WENNFEHLER
30.06.2020 10:58:40
lupo1
Der innere Teil
...WENN(SVERWEIS(J7;Datenquelle;2)=0;"";SVERWEIS(J7;Datenquelle;2))...
wird zu
...WENNFEHLER(1(1/SVERWEIS(J7;Datenquelle;2));"")...
_______________________________________
Unter bestimmten Umständen kann man mit SVERWEIS(J7;Datenquelle;{1.2}) noch einen Term einsparen.
Anzeige
akademisch z. 2. Frage "Erinnert sie sich an Erg?"
30.06.2020 11:19:48
lupo1
Daniel meinte, dass gleiche Suchbereiche in mehreren vorhandenene SVERWEISen (gilt auch für VERGLEICH) tatsächlich beschleunigend wirken. Das könne dann aber nur über internes Sortieren und Vorhalten des Sorts möglich sein.
Szenario:
A1:A500000: Ganze Zufallszahlen zwischen 1 und 200.000
B1[:B10000]: =SVERWEIS(ZEILE();A$1:A$500000;1) benötigt 2 Sekunden
B1[:B10000]: =SVERWEIS(ZEILE();A1:A500000;1) benötigt 30-60 Sekunden, da sich der Suchbereich durch das Herunterkopieren verändert.
Fazit: Daniel könnte recht haben. Allerdings stellt sich die Frage, warum das Verrutschen nur so relativ wenig mehr Zeit kostet.
Händisch ermittelt. Sicherer wäre es mit Timer in VBA, weil dann wirklich genau erst nach Rechenausführung die Zeit genommen wird.
Anzeige
Korrektur: Richtig jeweils der ;FALSCH)-SVERWEIS
30.06.2020 11:27:20
lupo1
entschuldige mein schlechtes Gedächtnis
30.06.2020 12:42:47
Daniel
aber könntest du bitte mal auf den Beitrag verlinken, in dem ich das geschrieben haben soll?
wenn ich mich recht erinnere, war das eher Lucs Meinung.
bei mir werden auch beide Varianten (in der Korrigierten Fassung) etwa gleich schnell berechnet.
eine Steigerung von 2 auf 30-60 Sekunden ist aber schon erheblich.
Da du von Zufallszahlen sprichst, gehe auch mal davon aus, dass diese unsortiert in der Liste vorliegen.
Gruß Daniel
Sicher?
30.06.2020 13:26:59
Daniel
Dann zeig mal bitte wann und wo ich das geschrieben habe.
Ich habe es noch mal nachgelesen und ...
30.06.2020 19:18:12
Luc:?
…es könnte sein, Daniel,
dass ich deine SortierBemerkung missverstanden und auch auf das Kriterium 0 (unsortiert) bezogen habe, was mich zu dieser AW veranlasst hatte:
Und was das evtl Vorsortieren betrifft, das könnte natürlich sein, aber es wäre ggf mehrfacher Aufwand. Es ist auch fraglich, ob die FmlOptimierung durch den FmlText-Interpreter soweit geht, einen 1× vorsortierten Bereich in allen gleichartigen Fmln zu verwenden, aber eben auch möglich. Dazu lässt sich µS in der Xl-Hilfe nicht aus.
Aus der kann man aber nun nicht entnehmen, dass ich eine 1malige interne Vorsortierung für alle gleichartigen Fmln/Fktt vermute. Das könnte möglich sein, aber wenn, dann doch wohl eher bei Verwendung von (gleich-)benannten Bereichen bzw TeilFmln.
Dieses evtl Missverständnis mag dadurch zustande gekommen sein, dass du eine wesentlich schnellere SortierGeschwindigkeit als Voraussetzung angeführt hattest, was ja bei externer Sortierung unwesentlich wäre.
Im Übrigen bin ich nach Lektüre eines Großteils der µS-Hinweise unter Hansuelis Link eher davon überzeugt, dass nicht intern einmalig für alle gleichartigen Fmln/Fktt „vorsortiert“ wird.
Dazu schreibe ich später noch etwas im dortigen Thread.
Luc :-?
Anzeige
Bitte demnächst vorher nochmal lesen bevor
30.06.2020 19:30:12
Daniel
Du solche Anmerkungen schreibt, vorallem dann, wenn du sie für alle sichtbar in die Betreffzeile schreibst.
Da sollte nichts falsches stehen
Und ja, es könnte nicht nur sein, dass du mich hier missverstanden hast, du hast es tatsächlich.
Also sei bitte in Zukunft sorgfältiger und teil das bitte auch deinem Kumpel Lupo mit.
Hallo Luc, wir können uns ja mal auf ein Bierchen
30.06.2020 20:18:40
lupo1
... treffen, da wir gerade zu Kumpels geweiht wurden. Es darf auch ein Corona-Pils sein.
Falls Du Antialkoholiker bist, trinke ich auch gern einen Tee mit.
____________________
Wer auch immer das Szenario mit dem wiederholt verwendeten LOOKUP-Bereich getestet hat: Er hat gut daran getan! Finde ich. Das Messen von Zeiten in Excel ist allerdings nicht immer sofort eindeutig.
Bierchen ist recht, aber ob Dir der Weg ...
02.07.2020 23:53:02
Luc:?
…zu einer mir naheliegenden Destille nicht gar zu weit ist, Lupo…‽ ;-]
Aber ich habe ja noch mehr „Kumpel“, da das Daniels „Ehrentitel“ für Leute ist, die mitunter meine Meinung teilen, vor allem, wenn sie seiner nicht entspricht…
Darauf einen „Kumpeltod“! (Single Malt is better!)
Gruß, Luc :-?

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige