Excel-Formel: Verbessern?

Moderator: ModerationP

Excel-Formel: Verbessern?

Beitragvon SchweizerDesTages » 06. Okt 2017, 10:26

Hallo zusammen

Ich habe eine dynamische (Intelligente) Tabelle mit 76000 Zeilen. Das Öffnen der Datei dauert ewigs, weil immer alles berechnet wird und x mal das ganze berechnet wird (je nach PC zwischen 2 und 6 mal).
Ich vermute, es liegt an gewissen Formeln innerhalb dieser intelligenten Tabelle und ich frage mich, ob man die eine oder andere Formel verbessern kann:

in einer Spalte steht das Datum (sowohl aus 2016er Daten als auch 2017er Daten): da ich Jahresvergleiche auf Tagesebene durchführen will, soll er mir die Werte mit einer 1 schreiben (damit ich nachher im Pivot das Filtern kann), bei dem folgendes gilt:
Befinden wir uns im laufenden Jahr (ausser heute), dann mach eine 1
Befinden wir uns im vorherigen Jahr, dann mach eine 1, falls der Monat < aktueller Monat ist
Befinden wir uns im vorherigen Jahr aber im selben Monat, dann mach eine 1, falls der Tag kleiner ist als der heutige Tag
Code: Alles auswählen
=WENN([@Tag]=HEUTE();0;WENN(MONAT([@Tag])>MONAT(HEUTE());0;WENN(MONAT([@Tag])<MONAT(HEUTE());1;WENN(TAG([@Tag])<TAG(HEUTE());1;0))))

Beispiel:
12.02.2017 = 1
20.10.2016 = 0
05.10.2016 = 1

kann man den Code irgendwie verbessern?
Danke für eure Unterstützung.
SchweizerDesTages
 

Re: Excel-Formel: Verbessern?

Beitragvon Fen » 06. Okt 2017, 10:38

Hallo und Grüße in die Schweiz,

eine Variante wäre, die Formeln (teilweise) durch VBA zu ersetzen. Ob das möglich sein könnte, müßtest du beurteilen.

mfg
Fen
 

Re: Excel-Formel: Verbessern?

Beitragvon snb » 06. Okt 2017, 10:41

=text([@Tag]';"MM.JJJJ")<>text(HEUTE();"MM.JJJJ")
snb
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 4993
Registriert: 25. Sep 2014, 16:37

Re: Excel-Formel: Verbessern?

Beitragvon Gast » 06. Okt 2017, 10:55

... und, ganz banale Ergänzung zu den Profi-Optimierungen,

in irgendeine freie Zelle (z.B. BY138) die Formel "=Heute()" schreiben und in allen anderen Formeln das "Heute()" ersetzen durch "$BY$138" ersetzen.

Denn HEUTE() ist volatil und für Excel entsprechend oft neu zu berechnen.

Grüße
Biber
Gast
 

Re: Excel-Formel: Verbessern?

Beitragvon CaBe » 06. Okt 2017, 10:56

Hallo SchweizerDesTages,
SchweizerDesTages hat geschrieben:Das Öffnen der Datei dauert ewigs, weil immer alles berechnet wird und x mal das ganze berechnet wird (je nach PC zwischen 2 und 6 mal).

Ich könnte mir vorstellen, dass allein der Dateiumfang ausreicht, um das Laden derartig zu verzögern. Es könnte schon viel helfen, falls die Dateigröße (in kBytes) deutlich sinkt.
SchweizerDesTages hat geschrieben:Ich vermute, es liegt an gewissen Formeln innerhalb dieser intelligenten Tabelle und ich frage mich, ob man die eine oder andere Formel verbessern kann:

Was spricht dagegen, die Formeln durch ein Makro abzulösen? Prinzipiell würde ein "kleiner" Code durch deine Tabelle durchgejagt werden und die Ergebnisse (1 oder 0) eintragen. Das wäre schnell und kompakt.
SchweizerDesTages hat geschrieben:Befinden wir uns im laufenden Jahr (ausser heute), dann mach eine 1
Befinden wir uns im vorherigen Jahr, dann mach eine 1, falls der Monat < aktueller Monat ist
Befinden wir uns im vorherigen Jahr aber im selben Monat, dann mach eine 1, falls der Tag kleiner ist als der heutige Tag

Es scheint immer nur 1 rauszukommen!?
SchweizerDesTages hat geschrieben:kann man den Code irgendwie verbessern?

Mit einem Makro.
Freundlichst
Carsten

Keine Antwort wäre wirklich schade! Windows 10 Pro, Office 2013 Pro Deutsch, manchmal Office 2016 Pro Englisch
Jede Änderung meiner Beiträge durch andere Benutzer möge bitte mit Grund und Namen gekennzeichnet werden.
Benutzeravatar
CaBe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5286
Registriert: 06. Apr 2005, 09:20
Wohnort: Bremen

Re: Excel-Formel: Verbessern?

Beitragvon HKindler » 06. Okt 2017, 13:01

Hi,

noch eine Anmerkung zu Biber: Ob ich HEUTE() jedes mal hinschreibe oder einmal in eine Zelle und dann die Zelle verwende, das schenkt sich nicht viel, denn auch dann wird jedes mal jede Formel, die sich auf die Zelle bezieht neu berechnet. Besser wäre es, beim Öffnen der Datei das heutige Datum einmalig in die Zelle zu schreiben:
Code: Alles auswählen
Private Sub Workbook_Open()
Tabelle1.Range("A1") = Date
End Sub
Jetzt werden die Formeln nur noch beim Öffnen neu berechnet und eben nicht mehr bei jeder Neuberechnung.

Interessant wäre eine (gekürzte) Datei, damit man sich mal die Formeln insgesamt anschauen könnte. Sehr oft lässt sich noch einiges an Rechenzeit heraus holen.
Gruß,
Helmut

----------------------------
Windows 8.1 (64 Bit) / Office 2013 (32 Bit)
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1484
Registriert: 04. Jul 2013, 09:02

Re: Excel-Formel: Verbessern?

Beitragvon SchweizerDesTages » 06. Okt 2017, 15:24

Hallo
Danke für eure Unterstützung.

Wie würde ein VBA-Code aussehen? Es müsste ja jeden Tag wieder diese 76'000 Zeilen neu berechnet werden und nicht nur diejenigen Einträge, die neu dazukommen (respektive: wenn einmal eine "1" drin steht, dann bleibts auch am nächsten Tag eine 1). und sobald er im alten Jahr die erste 0 gefunden hat, dann kann er eigentlich abbrechen weil auch die Folgetage 0 sind....


ich probiers aber mal mit dem Makro beim Öffnen der Datei und dann dem Zuweisen auf eine Zelle. Damit wäre schon geholfen.
SchweizerDesTages
 

Re: Excel-Formel: Verbessern?

Beitragvon Gast » 06. Okt 2017, 15:28

Moin HKindler,

.... denn auch dann wird jedes mal jede Formel, die sich auf die Zelle bezieht neu berechnet.

Ist dem so?
Dann wäre doch jegliches Konstrukt mit Hilfsspalten (die ich auch oft verwende) eine reine Augenwischerei.
Ich gehe schon davon davon aus, dass Excel natürlich die Formel-Abhängigkeit von anderen Zellen verwaltet (sprich: wenn 5000 Formeln von Zelle BY138 abhängen, diesen Wert EINMAL neu berechnet und alle 5000 abhängigen Formeln nur dann, wenn sich BY138 mal ändert)

Unbestritten ist die Variante mit "Einmalig-beim-Öffnen-Setzen" sauberer, keine Frage.
Wenn es dem SchweizerDesTages egal ist, ob er nun eine xlsM oder xlsX-Datei hat, dann soll er das tun...

Aber enttäuschend fände ich es schon, wenn die Redmonder so etwas nicht auf dem Schirm gehabt haben sollten.
Ich dachte, das sind die sympathischen Weltmarktführer? ;-)

Grüße
Biber
Gast
 

Re: Excel-Formel: Verbessern?

Beitragvon SchweizerDesTages » 06. Okt 2017, 15:54

snb hat geschrieben:=text([@Tag]';"MM.JJJJ")<>text(HEUTE();"MM.JJJJ")

Diese Formel funktioniert leider nicht wie gewünscht:
Gestern war der 05.10.2017
Demzufolge soll es mir eine 1 angeben wenn:
wir im aktuellen Jahr befinden BIS zum 05.10.2017 oder
wir im vergangenen Jahr befinden BIS zum 05.10.2016 (dh Oktober (ab 6.), November, Dezember-Datum müssten dort 0 sein)

Die obige Formel macht aber immer "WAHR", ausser der aktuelle Monat im 2017 (so wars be mir auf jedenfall)
SchweizerDesTages
 

Re: Excel-Formel: Verbessern?

Beitragvon Gast » 06. Okt 2017, 16:47

im Grossen und Ganzen will ich ja wissen:
Gestern war der 254. Tag im Jahr (beispielsweise).
Gib mir alle Daten aus den vergangenen Jahren bis zum 254. Tag

Das Problem ist aber, dass 2016 ein Schaltjahr war...
Ich will nämlich der 1. bis 5. Oktober vergleichen mit dem 1. bis 5. Oktober vom letzten Jahr (und dann würde die obige Überlegung) nicht funktionieren meiner Meinung nach... da dort der 5. Oktober dann der 255. Tag gewesen wäre (ich müsste es also irgendwie abfangen).

Deswegen kontrollier ich das ganze via Jahr (aktuelles Jahr = 1); sonst: wenn der Monat kleinre ist als der aktuelle Monat, dann 1; sonst wenn wir also im selben Monat befinden wie das aktuelle Datum, dann schau, dass der TAG kleiner ist als das heutige Datum :)
Gast
 

Re: Excel-Formel: Verbessern?

Beitragvon SchweizerDesJahres » 06. Okt 2017, 17:50

hi

Eine Beispielmappe mit ein paar Zeilen (100 bis 200) würde helfen.

Mfg
SchweizerDesJahres
 

Re: Excel-Formel: Verbessern?

Beitragvon SchweizerDesAbends » 06. Okt 2017, 20:10

Ich werde übers Weekend eine Datei basteln.

Aber, ich glaube ich hab noch einen Riesenresourcenfresser gefunden bei folgender Formel:

in Zeile X8 steht:
Code: Alles auswählen
WENN(UND(ZÄHLENWENN(F$1:F7;[@Nummer])=0;[@Nummer]<Tab!$O$1;[@Nummer]>Tab!$N$2);1;0)

Was macht der Code?
Er zählt, ob es die Nummer bereits vorher gibt:
- falls ja, dann setz 0
- falls nein, dann überprüfe, ob die Nummer kleiner ist als die hinterlegte Nummer in O1 und grösser als die hinterlegte Nummer in N2 (jeweils im Reiter "Tab") liegt: falls nein, setz 0, falls ja, setz 1

Irgendwie dauerts ewigs, wenn ich dort auch nur ein Mü abändere.
Falls es ein VBA Code gibt ohne Schleife wärs glaub auch ok...

Hoffe, ihr könnt mir nochmals helfen.
Gruss aus der Schweiz
SchweizerDesAbends
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 06. Okt 2017, 19:38

Re: Excel-Formel: Verbessern?

Beitragvon CaBe » 07. Okt 2017, 14:45

Hallo SchweizerDesAbends,
SchweizerDesAbends hat geschrieben:Ich werde übers Weekend eine Datei basteln.
Darauf will ich mal warten. Allerdings verstehe ich nicht richtig
SchweizerDesAbends hat geschrieben:Falls es ein VBA Code gibt ohne Schleife wärs glaub auch ok...
Warum ohne Schleife? Meine Ideen bzgl. deines Problems basieren letztlich alle auf einer Schleife, nämlich einer Schleife durch alle Zeilen. Die 75000 Zeilen sollten in der Schleife überraschend schnell abgearbeitet sein. Letztlich wird deine Beispieldatei helfen, dies zu beweisen.
Freundlichst
Carsten

Keine Antwort wäre wirklich schade! Windows 10 Pro, Office 2013 Pro Deutsch, manchmal Office 2016 Pro Englisch
Jede Änderung meiner Beiträge durch andere Benutzer möge bitte mit Grund und Namen gekennzeichnet werden.
Benutzeravatar
CaBe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5286
Registriert: 06. Apr 2005, 09:20
Wohnort: Bremen

HEUTE()

Beitragvon ehem. lupo1 » 07. Okt 2017, 15:05

Gast hat geschrieben: in irgendeine freie Zelle (z.B. BY138) die Formel "=Heute()" schreiben und in allen anderen Formeln das "Heute()" ersetzen durch "$BY$138" ersetzen. Denn HEUTE() ist volatil und für Excel entsprechend oft neu zu berechnen.

Dass HEUTE() volatil ist, stimmt. Dass der vielfache Bezug auf ein HEUTE() ggü vielen HEUTE() etwas daran ändert, wage ich zumindest zu bezweifeln. Denn jede abhängige Formel wird ebenso volatil.

Besser: In einem Workbook.Open [BY138] = Today. Dann ist Ruhe.

Wer keine Makros will oder darf, schreibt einfach das Datum als Wert in BY138.
ehem. lupo1
 

Re: Excel-Formel: Verbessern?

Beitragvon SchweizerDesAbends » 07. Okt 2017, 19:39

danke, die performance mit dem makro ist deutlich verbessert (Das Datum wird nun als Wert irgendwo hin geschrieben), aber es rechnet immer noch extrem lange bei dieser Formel da:

(in X8 steht)
Code: Alles auswählen
WENN(UND(ZÄHLENWENN(F$1:F7;[@Nummer])=0;[@Nummer]<Tab!$O$1;[@Nummer]>Tab!$N$2);1;0)


Ich will schauen, ob quasi die Nummer des Kunden neu ist; falls ja, mach eine 1... da leider aus früheren Zeiten Kundennummern 7stellig waren, überprüfe ich nun, ob die Nummer kleiner ist als in O1... und da ich nur an Neukunden im laufenden Jahr interessiert bin, sollte die Nummer grösser als N2 sein (erst dann könnte es ein Neukunde sein; falls er nicht schon in einem vorherigen Tag (Zeile) bestellt hat).


hoffe, jemand hat hier auch eine tolle Lösung.
Danke.
SchweizerDesAbends
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 06. Okt 2017, 19:38

Nächste

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: Flotter Feger, GMG-CC, hiob und 14 Gäste