Office: (Office 2016) Beschleunigung von Arrayformeln

Helfe beim Thema Beschleunigung von Arrayformeln in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo Community, ich habe eine Datei in Excel 2016 (dienstlich) erstellt, in der die Berechnungen vermutlich durch Nutzung von Arrayformeln sehr... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Bigenno, 18. Mai 2021.

  1. Bigenno Neuer User

    Beschleunigung von Arrayformeln


    Hallo Community,

    ich habe eine Datei in Excel 2016 (dienstlich) erstellt, in der die Berechnungen vermutlich durch Nutzung von Arrayformeln sehr langsam durchgeführt werden. Privat nutze ich Excel 365. Die dort neu eingeführte Filterformel kann ich jedoch in der dienstlichen Datei nicht verwenden.

    Die Datei enthält auf einem Tabellenblatt etwa 45.000 Datensätze (Monate, Datumsangaben und mehrere Ereignisse zu unterschiedlichen Zeiten eines Tages). Diese zeitlich forlaufenden Daten trenne ich auf einem weiteren Blatt für etwaige Berechnungen nach Datum, Uhrzeit sowie dem Ort eines Ereignisses (insgesamt 10 Orte mit Ereignissen zu unterschiedlichen Zeiten eines Tages = 10x 45.000 Datenreihen).
    Zur getrennten Darstellung nutze ich folgende Arrayformel von Martin Weiß:

    {=WENN($I$31=0;" ";INDEX('Grunddaten ELDIS'!$B$2:$B$44996;KGRÖSSTE(('Grunddaten ELDIS'!$A$2:$A$44996=$I$30)*(ZEILE('Grunddaten ELDIS'!$A$2:$A$44996)-1);ZÄHLENWENN('Grunddaten ELDIS'!$A$2:$A$44996;$I$30)+1-ZEILE('Grunddaten ELDIS'!A1))))}

    Insgesamt ist die Datei auf etwa 50 MB angewachsen, die Berechnungen verlaufen extrem langsam. Wie kann ich die Berechnungen beschleunigen? Ich habe gelesen, etwa Hilfsspalten zu nutzen und die Arrayformel zu trennen. Diese tut aber genau das, was sie soll und so wie ich es brauche, allerdings sehr langsam.

    Gibt es in Excel 2016 eine Alternative zur neuen Filterformel aus Excel 365? Eine Beispieldatei kann ich nicht anhängen, da zu groß.

    Vielen dank im Voraus!

    Enrico
     
    Bigenno, 18. Mai 2021
    #1
  2. DL_
    DL_ User
    Moin

    Ohne weitere Kenntnisse der Datei:
    Pivot
    Power Query
    Evtl. reicht sogar filtern.
     
  3. Bigenno Neuer User
     
    Bigenno, 19. Mai 2021
    #3
  4. Bigenno Neuer User

    Beschleunigung von Arrayformeln

    Hallo, danke für die Info.

    ich specke die Datei heute mal ab und lade ein Beispiel hoch.
     
    Bigenno, 19. Mai 2021
    #4
  5. Exl121150 Erfahrener User
    Hallo Enrico,
    Eine 1. Optimierungsmöglichkeit:
    betrachtet man deine Formel, so sieht man, dass sie folgenden Formelbestandteil enthält:
    ZÄHLENWENN('Grunddaten ELDIS'!$A$2:$A$44996;$I$30)+1
    wobei 'Grunddaten ELDIS'!$A$2:$A$44996 den Zählbereich enthalten und $I$30 im aktuellen Arbeitsblatt das Zählkriterium.
    Wenn du zB. in Zelle $J$30 folgende Formel einfügst: =ZÄHLENWENN('Grunddaten ELDIS'!$A$2:$A$44996;$I$30)+1, so vereinfacht sich die im Zitierbereich enthaltene Formel:
    {=WENN($I$31=0;" ";INDEX('Grunddaten ELDIS'!$B$2:$B$44996;KGRÖSSTE(('Grunddaten ELDIS'!$A$2:$A$44996=$I$30)*(ZEILE('Grunddaten ELDIS'!$A$2:$A$44996)-1);$J$30-ZEILE('Grunddaten ELDIS'!A1))))}
    der Länge nach und sicher auch der Zeit nach, weil die ZÄHLENWENN-Funktion nur 1x ausgeführt werden muss.

    Eine 2. Optimierungsmöglichkeit:
    Angesichts der Zeilenanzahl in 'Grunddaten ELDIS' wird sicherlich auch folgende Vereinfachung etwas bringen:
    aus dem Formelteil ZEILE('Grunddaten ELDIS'!A1) einfach nur ZEILE(A1) zu machen, womit wir bei folgender Formel angelangt wären:
    {=WENN($I$31=0;" ";INDEX('Grunddaten ELDIS'!$B$2:$B$44996;KGRÖSSTE(('Grunddaten ELDIS'!$A$2:$A$44996=$I$30)*(ZEILE('Grunddaten ELDIS'!$A$2:$A$44996)-1);$J$30-ZEILE(A1))))}
    Nach dem gleichen Prinzip kann man schließlich vereinfachen zu:
    {=WENN($I$31=0;" ";INDEX('Grunddaten ELDIS'!$B$2:$B$44996;KGRÖSSTE(('Grunddaten ELDIS'!$A$2:$A$44996 = $I$30)*ZEILE($A$1:$A$44995);$J$30-ZEILE(A1))))}

    Eine 3. Optimierungsmöglichkeit:
    Dazu müsstest du dir im Arbeitsblatt 'Grunddaten ELDIS' eine freie Spalte suchen neben den Spalten A und B. Ich nehme im Folgenden an, dass Spalte C frei wäre und das Arbeitsblatt mit der obigen Formel 'Abfrage' heißt:
    Dann könntest du in 'Grunddaten ELDIS'!C2 folgende Formel speichern:
    =(A2-Abfrage!$I$30)*ZEILE(A1)
    und diese Formel hinunterkopieren bis 'Grunddaten ELDIS'!C44996
    Dann vereinfacht sich obige Formel zu:
    {=WENN($I$31=0;" ";INDEX('Grunddaten ELDIS'!$B$2:$B$44996;KGRÖSSTE('Grunddaten ELDIS'!$C$2:$C$44996;$J$30-ZEILE(A1))))}
    Diese 3. Möglichkeit ist natürlich nicht besonders elegant, weil nämlich Spalte 'Grunddaten ELDIS'!C mit Formeln ergänzt werden muss, falls noch weitere Zeilen in diesem Arbeitsblatt dazukommen, würde aber wahrscheinlich die meiste Zeitersparnis bringen.
     
    Exl121150, 20. Mai 2021
    #5
  6. Exl121150 Erfahrener User
    Hallo,

    es tut mir leid, bei der 3. Optimierungsmöglichkeit hat sich ein Tippfehler eingeschlichen:
    Die Formel in Zelle 'Grunddaten ELDIS'!C2 muss lauten:
    =(A2=Abfrage!$I$30)*ZEILE(A1) statt =(A2-Abfrage!$I$30)*ZEILE(A1)
    Diese Formel ist dann wie gehabt in die Zellen hinunterzukopieren bis Zelle 'Grunddaten ELDIS'!C44996
     
    Exl121150, 20. Mai 2021
    #6
  7. Bigenno Neuer User

    Hallo, vielen Dank für die Möglichkeiten. Ich probiere das heute Abend sehr gern aus und gebe dazu eine Rückmeldung. Die Datei berechnet die Auftragsbelastung von Außendiensten in ihren Bereichen. Sie gibt dann im Endergebnis an, ob mehr Fahrzeuge in einem Bereich benötigt werden oder nicht. Die Formel filtert mir die Daten zu einem Auftrag zu einer bestimmten Uhrzeit eines Tages. Ich filtere die Daten durch wenn-Funktionen dann weiter nach der Auftragszeit und ordne sie so den Schichten im Drei-Schicht-System den Tagen Montag bis Sonntag zu. So kann ich im Endeffekt berechnen, an welchen Tagen die meisten Aufträge entstehen, wie hoch also welche Schicht an welchem Tag belastet ist. Dementsprechend kann ich dann entscheiden, ob ich weitere Fahrzeuge benötige, um alle Aufträge abdecken zu können.
    Diese Array-Formel hat die ganze Berechnung, so vermute ich, derart verlangsamt, so dass ich schon am Verzweifeln war.

    Grüße, Enrico
     
    Bigenno, 20. Mai 2021
    #7
  8. Bigenno Neuer User

    Beschleunigung von Arrayformeln

    Hier ist eine Beispieldatei.
     
    Bigenno, 21. Mai 2021
    #8
Thema:

Beschleunigung von Arrayformeln

Die Seite wird geladen...
  1. Beschleunigung von Arrayformeln - Similar Threads - Beschleunigung Arrayformeln

  2. Beschleunigen des ROI mit Business Intelligence

    in Microsoft Excel Tutorials
    Beschleunigen des ROI mit Business Intelligence: Beschleunigen des ROI mit Business Intelligence Excel 2019 Excel 2016 Mehr... Weniger Die Analyse von...
  3. Beschleunigen von VBA Excel Schleife

    in Microsoft Excel Hilfe
    Beschleunigen von VBA Excel Schleife: Hallo zusammen, ich überprüfe mit folgender Schleife eine Matrix von ca. 49000 Zellen. Dies nimmt entsprechend Zeit in Anspruch und ich bin auf der Suche ob es hier einen schnelleren Weg gibt....
  4. Ladezeiten Webbrowsersteuerelement beschleunigen.

    in Microsoft Access Hilfe
    Ladezeiten Webbrowsersteuerelement beschleunigen.: Hallo , Und danke für die Aufnahme in dieses Forum. Nun zu meiner Frage. In einem Formular verwende ich ein Webbrowsersteuerelement um ein Pdf Dokument anzuzeigen. Der Pfad zu der Datei wird aus...
  5. Beschleunigung einer Abfrage

    in Microsoft Access Hilfe
    Beschleunigung einer Abfrage: Hallo, ich habe eine Abfrage mit 10 Feldern - darunter 2, in denen mehrere Tabellenfelder zusammengefasst werden - und 3 Feldern mit je einer Bedingung. Zwei der Bedingungen sind einfach, z. B....
  6. Ativitätensuche in Kontakt beschleunigen

    in Microsoft Outlook Hilfe
    Ativitätensuche in Kontakt beschleunigen: Ich verwende Outlook 2010 auf einem älteren Notebook. Habe ca. 6.000 Kontakte drinnen. Ich öffne einen Kontakt. Klicke im Ribbon auf "Aktivitäten" Wähle bei Anzeigen "E-Mail" Im Fenster wird...
  7. Verweis Formel entschlacken und beschleunigen

    in Microsoft Excel Hilfe
    Verweis Formel entschlacken und beschleunigen: Hey Leute, da ich mal wieder mit Excel-Tabellen um mich schmeiße, bin ich leider wieder an ein Problem gestoßen wo die Hardware nicht so richtig hinterher kommt! Ich habe zwei Listen wobei ich...
  8. langsame Dropdowns beschleunigen

    in Microsoft Excel Hilfe
    langsame Dropdowns beschleunigen: Hallo liebe User, ich habe in meinem Arbeitsblatt viele Tabellenblätter mit sehr vielen zusammengehörigen Zellen, welche ich der Übersicht wegen gruppiert habe. Nun ist es aber leider so, dass...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden