Formel beschleunigen oder durch eine bessere ersetzen

03.07.2025 14:36:06

Raimund

Hi Experten,

im Blatt "Tickets" berechne ich in Spalte Q den gleichen oder nachfolgenden Servicetag aus dem Blatt "WIS", im Vergleich zum Kundennamen in Spalte C und dem Datum in Spalte I. In Spalte R berechne ich die Tage, die dazwischen liegen.

Meine Formel in Spalte Q scheint alles korrekt zu berechnen. Leider benötigt die Aggregat-Formel mehrere Minuten für die Neuberechnung. In meinem Blatt "WIS" habe ich mehr als 100.000 Zeilen, und es werden täglich weitere hinzukommen, was die Berechnung zusätzlich verlangsamt.

https://www.herber.de/bbs/user/177990.xlsb

Meine Frage lautet: Ist es möglich, meine Formel zu beschleunigen oder durch eine bessere zu ersetzen?

=IFERROR(

    IF(OR(ISBLANK(P2); ISBLANK(C2));

        "";

        AGGREGATE(15; 6; WIS!A:A/(WIS!C:C=C2)/(WIS!A:A>=P2); 1)

    );

    ""

)

Vielen Dank im Voraus für eure Hilfe.

Gruß,
Raimund

AW: Formel beschleunigen oder durch eine bessere ersetzen

03.07.2025 14:51:21

daniel

Hi
könntet du bitte die gewünschte Excelversion korrekt angeben, damit man weiß, welche Funktionen verwendet werden dürfen und welche nicht?
du scheibst, "Excel 2003", aber das verwendet kein AGGREGAT, das kam erst mit Excel 2010.
Also in welcher Excelversion muss das ganze laufen?

prinzipiell solltest du bei solchen Matrixberechnungen keine ganzen Spalten angeben, weil die dann vollständig durchgerechnet werden, sondern den Zellbereich immer so exakt wir möglich anpassen.
Wenn die Tabelle ständig wächst, dann kann man die ständige Anpassung der Formel dadurch vermeiden, dass man die Liste als "intelligente Tabelle" formatiert (Einfügen - Tabellen - Tabelle) und in der Formel dann die Namen dieser Tabelle verwendet (Excel setzt hier automatisch die Namen ein, wenn du einen Zellbereich vollständig ausgewählt hast).
die intelligente Tabelle erweitert sich automatisch wenn Daten hinzugefügt werden und mit den Namen als Zellbezug in der Formel hat man dann immer den extakt passenden Bezug.

Gruß Daniel

AW: Formel beschleunigen oder durch eine bessere ersetzen

03.07.2025 15:00:57

Raimund

Hi Daniel,

Ich benutze Microsoft Office LTSC Professional Plus 2024.

Beide Tabellen kommen via MySQL Connection von dem Server.
Die Berechnung mache ich in dem Blatt Tickets und die Auswertung in einer Pivot Tabelle.

Vielen Dank im Voraus für eure Zeit und Hilfe.

Gruss
Raimund

AW: Formel beschleunigen oder durch eine bessere ersetzen

03.07.2025 15:28:14

daniel

in deiner Excelversion könntest du das AGGREGAT durch MINWENNS ersetzen

=MinWenns(WIS!A:A;WIS!C:C;C2;WIS!A:A;">="&P2)

das rechnet normalerweise etwas schneller als das Aggregat und rechnet bei ganzen Spalten als Referenz auch nur die tatsächlich genutzten Zeilen durch und nicht alle.

Gruß Daniel

AW: Formel beschleunigen oder durch eine bessere ersetzen

03.07.2025 16:09:33

Raimund

Hi Daniel,

Ich habe es noch mit einer anderen Formel versucht, siehe unten, aber Deine ist wesentlich schneller.

=IF(OR(ISBLANK(P2); ISBLANK(C2));
"";
IFERROR(
INDEX(WIS!A$2:A$500292;
MATCH(1;
(WIS!C$2:C$500292=C2) * (WIS!A$2:A$500292>=P2);
0)
);
"")
)

Vielen Dank für Deine Hilfe

Gruss
Raimund

AW: Formel beschleunigen oder durch eine bessere ersetzen

03.07.2025 17:49:45

Yal

Hallo Raimund,

da Du auf einer Datenbank zugreifst, könntest Du anstatt die Daten zu runterladen, dann mit Excel berechnen, die Daten mittels Power Query (PQ) abrufen und die Berechnung direkt in PQ machen zu lassen.
Eine der Besonderheit von PQ ist, dass eine interne Optimierung vorgenommen wird: Bearbeitungen, die vom Datenbank-Engine erledigt werden können, werden den DB-Engine übergeben, u.a. Filterung, Join, Gruppierung usw. Du kannst dann die Zwischentabelle zur Prüfung anzeigen, aber auch direkt nur den Pivot herausgeben.

Zugang über Menü "Daten", "Daten abrufen", "aus Datenbank", "aus MySQL-Datenbank".
Für weitere Verarbeitung in PQ, siehe folgende Tutorial: https://excelhero.de/power-query/power-query-ganz-einfach-erklaert

VG
Yal

AW: Formel beschleunigen oder durch eine bessere ersetzen

03.07.2025 18:00:14

Raimund

Hi Yal,

Darüber habe ich auch schon nachgedacht aber Power Query ist irgendwie nicht mein Freund.
Ich bin wahrscheinlich schon zu alt dafür.
Ganz einfache Sachen bekomme ich schon hin, aber ich wüsste es nicht, wie und wo ich die 2 Formeln, die ich brauche, einbauen soll.

Dennoch vielen Dank für Deinen Vorschlag

Gruss
Raimund

AW: Formel beschleunigen oder durch eine bessere ersetzen

03.07.2025 21:59:34

Yal

Hallo Raimund,

zugegeben, es ist in dem Fall gar nicht so einfach, weil es ist nicht ein "genauer" Join, sondern ein "gleich oder grösser".

Ich habe spasseshalber die Aufgabe zugeknopft:
- beide Tabelle über Menü "Daten", "aus Tabelle/Bereich" ins PQ rübergebracht
- wir haben dann zwei Abfragen "Daten_Ticket" und "Daten_WIS" (es sind die Namen der Arbeitsblätter. Genau gesehen kommt alles aus WIS)
- eine Funktion gebaut, um die gezielte Wert aus Daten_WIS zu extrahieren

let Erg = (Kunde as text, Zeit as date) =>  

let

    Quelle = Table.SelectRows(Daten_WIS, each ([customer_name] = Kunde) and ([collection_timestamp] >= DateTime.From(Zeit))),

    Min = List.Min(Quelle[collection_timestamp])

in

    Min

in Erg

- diese Funktion wird als neue Spalte in Daten_Ticket aufgerufen

= Table.AddColumn(#"Geänderter Typ", "Min", each fxJoin([Name], [Date booked in]), type datetime)

- Dann müsste nur noch den Zeitabstand zwischen diese Spalte "Min" und "Date booked in"

Man könnte den Umweg über die Funktion sparen, wenn die Spalte "Min" so implementiert wäre (Schöner ist es nicht):

= Table.AddColumn(#"Geänderter Typ", "Min", each let Erg = (N, Z) => List.Min(Table.SelectRows(Daten_WIS, each ([customer_name] = N) and ([collection_timestamp] >= DateTime.From(Z)))[collection_timestamp]) in Erg([Name],[Date booked in]), type datetime)

VG
Yal

AW: Formel beschleunigen oder durch eine bessere ersetzen

04.07.2025 14:20:14

Raimund

Hi Yal,

Vielen Dank für Deine PQ Lösung.
Ich werde es versuchen diese umzusetzen. Hoffentlich finde ich heute Zeit dafür.

Auf jeden Fall werde ich mich diesbezüglich noch melden.

Vien Dank für Deine Zeit und Hilfe

Gruss

Raimund

AW: Formel beschleunigen oder durch eine bessere ersetzen

04.07.2025 18:52:04

Raimund

Hi Val,

Habe es gerade versucht mit PQ und ich mag es noch weniger als zuvor.

Fehler:
Expression.Error: The name 'fxJoin' wasn't recognized. Make sure it's spelled correctly.

Expression.Error: The name 'Geänderter Typ' wasn't recognized. Make sure it's spelled correctly.

Habe das:

= Table.AddColumn(#"Geänderter Typ", "Min", each fxJoin([Name], [Date booked in]), type datetime)

so abgeändert:

= Table.AddColumn(#"Changed Type", "Min", each fxJoin([Name], [Date booked in]), type datetime)

Bei Daten_WIS:

Enter Parameters?

Kunde und Zeit. Dann Invoke

Ich lasse es lieber mit PQ. Es raube mir den letzten Nerv.

Trotzdem vielen Dank für Deine Hilfe.

Gruss

Raimund

AW: Formel beschleunigen oder durch eine bessere ersetzen

05.07.2025 23:30:06

Yal

Hallo Raimund,

sorry, ich habe übersehen zu erklären, dass die Abfrage, die als Funktion gilt, "fxJoin" umbenannt werden soll, um richtig gerufen zu werden.

Es ist immer ein Problem, wenn zu viel auf einmal erklärt werden muss.

Lass den Kopf nicht hängen und beschäftige dich weiterhin mit Power Query, aber nach deinem Rhythmus.
Es ist ein starkes Werkzeug und viele Vorteile. Der größte davon ist, dass es zwingt die Lösung gut strukturiert zu bauen.

VG
Yal

Formel beschleunigen oder durch eine bessere ersetzen

Forumthread: Formel beschleunigen oder durch eine bessere ersetzen

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Formel beschleunigen oder durch eine bessere ersetzen

Forumthread: Formel beschleunigen oder durch eine bessere ersetzen

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread