Hallo zusammen,
ich bräuchte nochmal euren Support. Da es letztens super geklappt hat dachte ich, ich probiere es nochmal.
Ich habe ein ziemliches Performance-Problem in Excel und suche nach Lösungsansätzen, wie man mein vorhandenes Setup schneller bekommt.
Meine Datei ist derzeit so aufgebaut, dass ich vordefinierte Daten aus verschiedenen Quellen ungefiltert (keine Formatierungsänderung etc) in einer Tabelle meines Arbeitsblatt "RawData" einfüge.
Diese Informationen kommen in einer Tabelle "Absätze" eines anderen Arbeitsblatts "Absätze_Insgesamt" und werden dort entsprechend umgewandelt. Diese Tabelle dient als meine Quelltabelle und anhand dieser baue ich mir meine Anzeigen über Pivots (mit und ohne Dax Formeln), über Diagramme und sonstige Veranschaulichungen auf.
Ich habe bereits um die 40 Spalten in der Tabelle "Absätze" und es werden vermutlich auch noch mehr werden. Mein Problem hierdran ist, dass jede Spalte und in jeder Zeile ihre entsprechende Formel hat (alle mit KI erstellt), vor allem existieren viele und lange LET-Spaltenformeln.
Angefangen von
Bei 20+k Zeilen a 40 Spalten kommt da einiges zusammen und mir geht jeder Rechner ein. Derzeit hat die Excel-Datei nur durch diese Formeln in der Tabelle "Absätze" über 100mb.
Ich finde einfach keinen Ansatz wie ich das ganze Performancetechnisch optimieren kann.
Eine Idee war per VBA die Tabellenformeln nach der Berechnung zu ich nenns mal „materialisieren“, also in Werte umwandeln (dass nicht mehr die Formel sondern der Wert einfach steht). Das würde auch die Größe der Excel Liste von >100mb auf ~ 5mb runterdrücken und damit wird alleine das Öffnen nicht mehr über 5 Minuten Brauchen (bei nem RYZEN 7 5825U).
Kurz gesagt:
1. Erste Datenzeile in der Tabelle Absätze behält die Formeln (Vorlage).
2. Alle weiteren Zeilen werden in Werte konvertiert.
3. Beim nächsten Sync kopiert Excel die Formeln aus Zeile 1 automatisch in neu hinzugekommene Zeilen; danach werden diese wieder materialisiert.
Ich denke das funktioniert zwar für alles statischen Formeln gut, ich habe damit aber dann zwei Probleme in einem und zwar: wenn jemand eine Information ändert, auf die sich eine Formel bezogen hat, passt sich nichts dynamisch an und zudem; die Auto-Aktualisierung der Währungsumrechnung wird damit sowieso zerschossen - und > das alles wird dann irgendwann keiner mehr auf den Schirm haben.
Eine andere Idee war die Excel Formeln in Power Query zu übertragen, weil ich gehört habe, dass Power Query Berechnungen nur durchführt, wenn sich die Daten laden oder aktualisieren und auch schneller ist (Wie auch immer das verarbeitet wird?). Kriege aber keine der Beispielformeln im M-code für Power Query umgewandelt und sehe zudem auch keine Möglichkeit für meine vorhandene Tabelle in den jeweiligen Spalten Formeln einzubauen. Das geht scheinbar nur wenn ich neue Spalten erstelle in Power Query.
Oder sind die Formeln einfach Murks, dass ich mir einen Deadlock damit geschaffen habe und müsste da eher drangehen?
Würde mich sehr freuen, wenn mir jemand eine idee geben könnte wie man DAS -.- Lösen könnte.
ich bräuchte nochmal euren Support. Da es letztens super geklappt hat dachte ich, ich probiere es nochmal.
Ich habe ein ziemliches Performance-Problem in Excel und suche nach Lösungsansätzen, wie man mein vorhandenes Setup schneller bekommt.
Meine Datei ist derzeit so aufgebaut, dass ich vordefinierte Daten aus verschiedenen Quellen ungefiltert (keine Formatierungsänderung etc) in einer Tabelle meines Arbeitsblatt "RawData" einfüge.
Diese Informationen kommen in einer Tabelle "Absätze" eines anderen Arbeitsblatts "Absätze_Insgesamt" und werden dort entsprechend umgewandelt. Diese Tabelle dient als meine Quelltabelle und anhand dieser baue ich mir meine Anzeigen über Pivots (mit und ohne Dax Formeln), über Diagramme und sonstige Veranschaulichungen auf.
Ich habe bereits um die 40 Spalten in der Tabelle "Absätze" und es werden vermutlich auch noch mehr werden. Mein Problem hierdran ist, dass jede Spalte und in jeder Zeile ihre entsprechende Formel hat (alle mit KI erstellt), vor allem existieren viele und lange LET-Spaltenformeln.
Angefangen von
XML:
=RawData[@Bestelldatum]
Code:
=LET(
SKU_Text; GLÄTTEN(""&[@SKU]);
Anzahl_Wert; WENNFEHLER(1*[@AnzahlArtikel]; 0);
PT_Text; ""&[@Rabatt];
PT_Geputzt; GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(PT_Text;"€";"");ZEICHEN(160);"");ZEICHEN(9);""));
PT_Komma; WENNFEHLER(FINDEN(",";PT_Geputzt);0);
PT_Punkt; WENNFEHLER(FINDEN(".";PT_Geputzt);0);
PT_Norm; WENN(PT_Komma>0; WECHSELN(PT_Geputzt;".";""); WENN(UND(PT_Punkt>0; PT_Komma=0); WECHSELN(PT_Geputzt;".";","); PT_Geputzt));
PT_Raw; WENNFEHLER(WERT(PT_Norm);0);
IstSummenzeile; UND( ODER(SKU_Text=""; KLEIN(SKU_Text)="0"); ODER(Anzahl_Wert=0; ""&[@AnzahlArtikel]=""); PT_Raw>0 );
KostenEinheit; WENNFEHLER( 1*XVERWEIS([@[Artikel_kurz]]; tbl_Shop[Artikel]; tbl_Shop[Versandkosten]); 0 );
WENN( IstSummenzeile; ""; Anzahl_Wert * KostenEinheit )
)
Code:
=LET(
Bestell;[@BestellNr];
ZeilenMaske;[BestellNr]=Bestell;
ArtAlle;FILTER([Artikel_kurz];ZeilenMaske);
MengeAlle;FILTER([AnzahlArtikel];ZeilenMaske);
SKUAlle;FILTER([SKU];ZeilenMaske);
PTAlle;FILTER([Rabatt];ZeilenMaske);
LandAlle;FILTER([Land];ZeilenMaske);
MwStAlle;FILTER([MwSt];ZeilenMaske);
RowIdxAlle;FILTER(ZEILE([BestellNr]);ZeilenMaske);
AnzZeilen;ZEILEN(RowIdxAlle);
KeyAlle;FILTER([Schlüssel_BRV];ZeilenMaske);
MengeZ;WENNFEHLER(1*MengeAlle;0);
PT_Text;""&PTAlle;
PT_Putz;GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(PT_Text;"€";"");ZEICHEN(160);"");ZEICHEN(9);""));
PT_k;WENNFEHLER(FINDEN(",";PT_Putz);0);
PT_p;WENNFEHLER(FINDEN(".";PT_Putz);0);
PT_norm;WENN(PT_k>0;WECHSELN(PT_Putz;".";"");WENN(UND(PT_p>0;PT_k=0);WECHSELN(PT_Putz;".";",");PT_Putz));
PT_Wert;WENNFEHLER(WERT(PT_norm);0);
SKU_T;GLÄTTEN(""&SKUAlle);
SumSKUleer;WENN(SKU_T="";WAHR;WENN(KLEIN(SKU_T)="0";WAHR;FALSCH));
SumAnzLeer;WENN(MengeZ=0;WAHR;WENN(""&MengeAlle="";WAHR;FALSCH));
SumPreisPos;WENN(PT_Wert>0;WAHR;FALSCH);
SummenFlag_Key;WENNFEHLER(ISTZAHL(SUCHEN("||";KeyAlle));FALSCH);
IstSummenzeile_Arr;WENN(SummenFlag_Key;WAHR;WENN(SumSKUleer;WENN(SumAnzLeer;WENN(SumPreisPos;WAHR;FALSCH);FALSCH);FALSCH));
PosMaske;NICHT(IstSummenzeile_Arr);
art;FILTER(ArtAlle;PosMaske);
menge;WENNFEHLER(1*FILTER(MengeAlle;PosMaske);0);
anzpos;ZEILEN(art);
landfil;FILTER(LandAlle;PosMaske);
landtxt;WENNFEHLER(GROSS(GLÄTTEN(INDEX(FILTER(landfil;landfil<>"");1)));"");
land_ISO2;WENN(LÄNGE(landtxt)>=2;LINKS(landtxt;2);"");
gewicht;WENNFEHLER(1*XVERWEIS(art;tbl_Shop[Artikel];tbl_Shop[Gewicht_kg]);0);
rolle;XVERWEIS(art;tbl_Shop[Artikel];tbl_Shop[Kategorie];"");
FlagHA;WENN(IDENTISCH(rolle;"HA");1;0);
FlagNA;WENN(IDENTISCH(rolle;"NA");1;0);
FlagMB;WENN(IDENTISCH(rolle;"MB");1;0);
gesamtMengeHA;SUMMENPRODUKT(menge;FlagHA);
idxErsteHA;WENN(gesamtMengeHA>0;VERGLEICH(1;FlagHA;0);0);
FlagErsteHA;WENN(anzpos>0;WENN(SEQUENZ(anzpos)=idxErsteHA;1;0);0);
gewichtErsteHA;WENN(gesamtMengeHA>0;SUMMENPRODUKT(gewicht;FlagErsteHA);0);
gewichtNA;SUMMENPRODUKT(gewicht;menge;FlagNA);
gewicht_Paket1;WENN(gesamtMengeHA>0;gewichtErsteHA+gewichtNA;gewichtNA);
LC_Text;WENNFEHLER(""&tbl_Versandtarife[Landcode];"");
LC_Clean;GROSS(GLÄTTEN(WECHSELN(LC_Text;ZEICHEN(160);"")));
LC_ISO2_raw;WENN(LÄNGE(LC_Clean)>=2;LINKS(LC_Clean;2);"");
LC_ISO2;WENN(LC_ISO2_raw="UK";"GB";LC_ISO2_raw);
grp_all;WENNFEHLER(1*tbl_Versandtarife[Tarifgruppe];"");
preis_all;WENNFEHLER(1*tbl_Versandtarife[R_Versandpreis];0);
grp_land;WENNFEHLER(FILTER(grp_all;LC_ISO2=land_ISO2);SEQUENZ(0));
preis_land;WENNFEHLER(FILTER(preis_all;LC_ISO2=land_ISO2);SEQUENZ(0));
anz_tarife;ZEILEN(grp_land);
preis_paket1;WENN(ODER(land_ISO2="";anzpos=0;anz_tarife=0);0;WENNFEHLER(XVERWEIS(gewicht_Paket1;grp_land;preis_land;0;1);0));
mengeErsteHA;WENN(anzpos>0;SUMMENPRODUKT(menge;FlagErsteHA);0);
restErsteHA;MAX(mengeErsteHA-1;0);
preis_ersteHA;WENN(gesamtMengeHA>0;WENNFEHLER(XVERWEIS(gewichtErsteHA;grp_land;preis_land;0;1);0);0);
kosten_ersteHA_rest;restErsteHA*preis_ersteHA;
FlagHA_Other;WENN(FlagHA=1;WENN(FlagErsteHA=1;0;1);0);
gew_HA_other;WENNFEHLER(FILTER(gewicht;FlagHA_Other=1);0);
menge_HA_other;WENNFEHLER(1*FILTER(menge;FlagHA_Other=1);0);
preis_HA_other;WENNFEHLER(XVERWEIS(gew_HA_other;grp_land;preis_land;0;1);0);
kosten_HA_other;SUMMENPRODUKT(menge_HA_other;preis_HA_other);
gew_MB;WENNFEHLER(FILTER(gewicht;FlagMB=1);0);
menge_MB;WENNFEHLER(1*FILTER(menge;FlagMB=1);0);
preis_MB;WENNFEHLER(XVERWEIS(gew_MB;grp_land;preis_land;0;1);0);
kosten_MB;SUMMENPRODUKT(menge_MB;preis_MB);
nettoGesamt; preis_paket1 + kosten_ersteHA_rest + kosten_HA_other + kosten_MB;
HatSummenzeile;NICHT(ISTFEHLER(VERGLEICH(WAHR;IstSummenzeile_Arr;0)));
Mw_S_Text;""&WENNFEHLER(INDEX(FILTER(MwStAlle;IstSummenzeile_Arr);1);"");
Mw_P_Text;""&WENNFEHLER(INDEX(FILTER(MwStAlle;NICHT(IstSummenzeile_Arr));1);"");
Mw_S_Clean;GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(Mw_S_Text;"%";"");ZEICHEN(160);"");ZEICHEN(9);""));
Mw_P_Clean;GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(Mw_P_Text;"%";"");ZEICHEN(160);"");ZEICHEN(9);""));
Mw_S_k;WENNFEHLER(FINDEN(",";Mw_S_Clean);0);
Mw_S_p;WENNFEHLER(FINDEN(".";Mw_S_Clean);0);
Mw_S_norm;WENN(Mw_S_k>0;WECHSELN(Mw_S_Clean;".";"");WENN(UND(Mw_S_p>0;Mw_S_k=0);WECHSELN(Mw_S_Clean;".";",");Mw_S_Clean));
Mw_S_Val;WENNFEHLER(WERT(Mw_S_norm);"");
Mw_P_k;WENNFEHLER(FINDEN(",";Mw_P_Clean);0);
Mw_P_p;WENNFEHLER(FINDEN(".";Mw_P_Clean);0);
Mw_P_norm;WENN(Mw_P_k>0;WECHSELN(Mw_P_Clean;".";"");WENN(UND(Mw_P_p>0;Mw_P_k=0);WECHSELN(Mw_P_Clean;".";",");Mw_P_Clean));
Mw_P_Val;WENNFEHLER(WERT(Mw_P_norm);"");
Mw_S_Faktor;WENN(Mw_S_Val="";"";WENNS(Mw_S_Val>5;1+Mw_S_Val/100;Mw_S_Val>=1;Mw_S_Val;WAHR;1+Mw_S_Val));
Mw_P_Faktor;WENN(Mw_P_Val="";"";WENNS(Mw_P_Val>5;1+Mw_P_Val/100;Mw_P_Val>=1;Mw_P_Val;WAHR;1+Mw_P_Val));
MwStFaktor;WENN(HatSummenzeile;Mw_S_Faktor;Mw_P_Faktor);
SKU_Akt;GLÄTTEN(""&[@SKU]);
Anzahl_Akt;WENNFEHLER(1*[@AnzahlArtikel];0);
PT_Text_Akt;""&[@Rabatt];
PT_Putz_Akt;GLÄTTEN(WECHSELN(WECHSELN(WECHSELN(PT_Text_Akt;"€";"");ZEICHEN(160);"");ZEICHEN(9);""));
PT_K_Akt;WENNFEHLER(FINDEN(",";PT_Putz_Akt);0);
PT_P_Akt;WENNFEHLER(FINDEN(".";PT_Putz_Akt);0);
PT_Norm_Akt;WENN(PT_K_Akt>0;WECHSELN(PT_Putz_Akt;".";"");WENN(UND(PT_P_Akt>0;PT_K_Akt=0);WECHSELN(PT_Putz_Akt;".";",");PT_Putz_Akt));
PT_Raw_Akt;WENNFEHLER(WERT(PT_Norm_Akt);0);
IstSummenzeile_Akt;ODER(WENNFEHLER(FINDEN("||";""&[@[Schlüssel_BRV]])>0;FALSCH);UND( ODER(SKU_Akt="";KLEIN(SKU_Akt)="0"); ODER(Anzahl_Akt=0; ""&[@AnzahlArtikel]=""); PT_Raw_Akt>0));
SollSchreiben;ODER(IstSummenzeile_Akt;AnzZeilen=1);
WENN(SollSchreiben; WENN(MwStFaktor="";""; RUNDEN(nettoGesamt*MwStFaktor;2)); "")
)
Code:
=LET(
Land_Text; ""&[@Land];
Land_Bereinigt; GROSS(GLÄTTEN(Land_Text));
Land_ISO2_raw; WENN(LÄNGE(Land_Bereinigt)>=2; LINKS(Land_Bereinigt;2); "");
Land_ISO2; WENN(Land_ISO2_raw="UK"; "GB"; Land_ISO2_raw);
Vertrieb_Text; GROSS(GLÄTTEN(""&[@[Vertrieb_auto]]));
Laender_ISO2_Liste; {"DE";"AT";"CH";"NL";"BE";"LU";"FR";"IT";"ES";"PT";"IE";"GB";"US";"CA";"AU";"NZ";"JP";"SE";"NO";"DK";"PL";"CZ";"HU";"RO";"BG";"SK";"SI";"HR";"GR";"TR"};
Waehrungen_Liste; {"EUR";"EUR";"CHF";"EUR";"EUR";"EUR";"EUR";"EUR";"EUR";"EUR";"EUR";"GBP";"USD";"CAD";"AUD";"NZD";"JPY";"SEK";"NOK";"DKK";"PLN";"CZK";"HUF";"RON";"BGN";"EUR";"EUR";"EUR";"EUR";"TRY"};
Waehrung_Code;
WENNS(
Vertrieb_Text="SHOP EU"; "EUR";
ODER(Vertrieb_Text="SHOP US"; Vertrieb_Text="SHOP USA"); "USD";
LINKS(Vertrieb_Text;3)="AMZ"; WENNFEHLER( XVERWEIS(Land_ISO2; Laender_ISO2_Liste; Waehrungen_Liste; "EUR"; 0 ); "EUR");
WAHR; WENNFEHLER( XVERWEIS(Land_ISO2; Laender_ISO2_Liste; Waehrungen_Liste; "EUR"; 0 ); "EUR")
);
Betrag_Nativ; WENNFEHLER(1*[@[PreisTotal_Brutto_Num]]; "");
Datentyp_Spalte; tbl_Währungstabelle[Datentyp];
Von_Spalte; tbl_Währungstabelle[Von];
Zu_Spalte; tbl_Währungstabelle[Zu];
Kurs_Spalte; tbl_Währungstabelle[Aktueller Umrechnungskurs];
Direkter_Kurs; WENNFEHLER(
XVERWEIS(Waehrung_Code&":EUR"; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS(Waehrung_Code&"/EUR"; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS(Waehrung_Code; FILTER(Von_Spalte; Zu_Spalte="EUR"); FILTER(Kurs_Spalte; Zu_Spalte="EUR")))); "");
Umgekehrter_Roh; WENNFEHLER(
XVERWEIS("EUR:"&Waehrung_Code; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS("EUR/"&Waehrung_Code; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS("EUR"; FILTER(Von_Spalte; Zu_Spalte=Waehrung_Code); FILTER(Kurs_Spalte; Zu_Spalte=Waehrung_Code)))); "");
Umgekehrter_Kurs; WENN(ISTZAHL(Umgekehrter_Roh); 1/Umgekehrter_Roh; "");
Kurs_Cur_USD; WENNFEHLER(
XVERWEIS(Waehrung_Code&":USD"; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS(Waehrung_Code&"/USD"; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS(Waehrung_Code; FILTER(Von_Spalte; Zu_Spalte="USD"); FILTER(Kurs_Spalte; Zu_Spalte="USD")))); "");
Kurs_USD_EUR; WENNFEHLER(
XVERWEIS("USD:EUR"; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS("USD/EUR"; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS("USD"; FILTER(Von_Spalte; Zu_Spalte="EUR"); FILTER(Kurs_Spalte; Zu_Spalte="EUR")))); "");
Kurs_EUR_USD; WENNFEHLER(
XVERWEIS("EUR:USD"; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS("EUR/USD"; Datentyp_Spalte; Kurs_Spalte;
XVERWEIS("EUR"; FILTER(Von_Spalte; Zu_Spalte="USD"); FILTER(Kurs_Spalte; Zu_Spalte="USD")))); "");
Kurs_via_USD; WENNS(
UND(ISTZAHL(Kurs_Cur_USD); ISTZAHL(Kurs_USD_EUR)); Kurs_Cur_USD*Kurs_USD_EUR;
UND(ISTZAHL(Kurs_Cur_USD); ISTZAHL(Kurs_EUR_USD)); Kurs_Cur_USD / Kurs_EUR_USD;
WAHR; ""
);
Kurs_Gesucht; WENNS(
ISTZAHL(Direkter_Kurs); Direkter_Kurs;
ISTZAHL(Umgekehrter_Kurs); Umgekehrter_Kurs;
ISTZAHL(Kurs_via_USD); Kurs_via_USD;
Waehrung_Code="EUR"; 1;
WAHR; ""
);
Ergebnis_EUR; WENN(ODER(Betrag_Nativ=""; Kurs_Gesucht=""); ""; Betrag_Nativ*Kurs_Gesucht);
Ergebnis_EUR
)Ich finde einfach keinen Ansatz wie ich das ganze Performancetechnisch optimieren kann.
Eine Idee war per VBA die Tabellenformeln nach der Berechnung zu ich nenns mal „materialisieren“, also in Werte umwandeln (dass nicht mehr die Formel sondern der Wert einfach steht). Das würde auch die Größe der Excel Liste von >100mb auf ~ 5mb runterdrücken und damit wird alleine das Öffnen nicht mehr über 5 Minuten Brauchen (bei nem RYZEN 7 5825U).
Kurz gesagt:
1. Erste Datenzeile in der Tabelle Absätze behält die Formeln (Vorlage).
2. Alle weiteren Zeilen werden in Werte konvertiert.
3. Beim nächsten Sync kopiert Excel die Formeln aus Zeile 1 automatisch in neu hinzugekommene Zeilen; danach werden diese wieder materialisiert.
Ich denke das funktioniert zwar für alles statischen Formeln gut, ich habe damit aber dann zwei Probleme in einem und zwar: wenn jemand eine Information ändert, auf die sich eine Formel bezogen hat, passt sich nichts dynamisch an und zudem; die Auto-Aktualisierung der Währungsumrechnung wird damit sowieso zerschossen - und > das alles wird dann irgendwann keiner mehr auf den Schirm haben.
Eine andere Idee war die Excel Formeln in Power Query zu übertragen, weil ich gehört habe, dass Power Query Berechnungen nur durchführt, wenn sich die Daten laden oder aktualisieren und auch schneller ist (Wie auch immer das verarbeitet wird?). Kriege aber keine der Beispielformeln im M-code für Power Query umgewandelt und sehe zudem auch keine Möglichkeit für meine vorhandene Tabelle in den jeweiligen Spalten Formeln einzubauen. Das geht scheinbar nur wenn ich neue Spalten erstelle in Power Query.
Oder sind die Formeln einfach Murks, dass ich mir einen Deadlock damit geschaffen habe und müsste da eher drangehen?
Würde mich sehr freuen, wenn mir jemand eine idee geben könnte wie man DAS -.- Lösen könnte.
Zuletzt bearbeitet:
![[PfE] Bugfixer](https://pics.computerbase.de/forum/avatars/m/558/558392.jpg?1361954165){kind=avatar}
