Office: (Office Version unbekannt) "INDIREKT" vermeiden

Moin Forum :)

für die Arbeit habe ich eine sehr umfangreiche Exceldatei mit mehreren Blättern, vielen Formeln und noch mehr Daten erstellt (und sie wird noch wachsen).
Damit Excel nicht im Schneckentempo arbeitet habe ich volatile Funktionen soweit es ging vermieden und die Bedingten Formatierungen auf ein Minimum reduziert.
Trotzdem arbeitet Excel bereits spürbar langsam.
Ein paarmal komme ich um "HEUTE()" nicht drumherum, das wird auch so bleiben müssen, "BEREICH.VERSCHIEBEN" "JETZT()" usw. benutze ich garnicht, aber "INDIREKT" wird auf einigen Blättern in langen Tabellen mehrfach verwendet. Das würde ich gerne ändern, finde aber die Lösung nicht.

In der Firma arbeiten wir mit dem aktuellen Win10 und Office365 (das Forum "zwingt" mich zur Auswahl eines Präfixes, aber Office365 steht leider nicht zur Auswahl).
Makros sind keine Option, und es wäre schön wenn Matrixformeln vermieden werden könnten (vermutlich hat dort ausser mir noch nie jemand davon gehört). Falls nötig wären Hilfsspalten garkein Problem.

Alle erstellten Tabellen sind diese vorformatierten "Datentabellen" (nennt man die so?) und ich arbeite viel mit dem Namensmanager (habe viele Bereiche definiert um Formelbezüge übersichtlicher zu halten). Ist sonst noch was wichtig?

Hier nun eine kleine Beispieldatei:
Den Anhang IndirektVermeiden.xlsx betrachten

Konkret geht es nun um die Spalten L und N.
Dort muss ich berechnen ob in einen bestimmten Zeitraum Feiertage fallen, und wenn ja wieviele.
Der Zeitraum wird anhand der Spalten C, F-K und M errechnet.
Die Feiertage werden durch ein "ja" in Spalte E definiert.

Die Anzahl der Feiertage wird mit "ZÄHLENWENNS" errechnet.
Für den Zeitraum muss mittels Formeln ein Zellbereich errechnet werden. Genau dies mache ich noch mit "INDIREKT", und genau dies würde ich gerne ändern und hoffe auf Eure Hilfe.

Code:

=ZÄHLENWENNS(INDIREKT(ADRESSE(ZEILE()-SUMME([@[Transport dauer LG]];[@[WE Ein]]);SPALTE([@Feiertag]))&":"&ADRESSE(ZEILE();SPALTE([@Feiertag])));"ja";INDIREKT(ADRESSE(ZEILE()-SUMME([@[Transport dauer LG]];[@[WE Ein]]);SPALTE([@Tag]))&":"&ADRESSE(ZEILE();SPALTE([@Tag])));"<=5")

Code:

=ZÄHLENWENNS(INDIREKT(ADRESSE(ZEILE();SPALTE([@Feiertag]))&":"&ADRESSE(ZEILE()+SUMME([@[Transport dauer VG]];[@[WE Aus]];1);SPALTE([@Feiertag])));"ja";INDIREKT(ADRESSE(ZEILE();SPALTE([@Tag]))&":"&ADRESSE(ZEILE()+SUMME([@[Transport dauer VG]];[@[WE Aus]];1);SPALTE([@Tag])));"<=5")

Wenn noch Fragen sind: immer her damit.

 

Hallo Achim,

gib in Spalte L folgende Formel ein (also in Zelle L6, L7, ..., L67):

Code:

=ZÄHLENWENNS(INDEX([Feiertag]; ZEILE()-ZEILE(XYZ)-[@[Transport dauer LG]]-[@[WE Ein]];1):[@Feiertag]; "ja"; INDEX([Tag];  ZEILE()-ZEILE(XYZ)-[@[Transport dauer LG]]-[@[WE Ein]];1):[@Tag];"<=5")

gib in Spalte N folgende Formel ein (also in Zelle N6, N7, ..., N67):

Code:

=ZÄHLENWENNS([@Feiertag]:INDEX([Feiertag];  MIN(ZEILE()-ZEILE(XYZ)+[@[Transport dauer VG]]+[@[WE Aus]]+1;ZEILEN(XYZ)-1); 1);"ja";[@Tag]:INDEX([Tag]; MIN(ZEILE()-ZEILE(XYZ)+[@[Transport dauer VG]]+[@[WE Aus]]+1;ZEILEN(XYZ)-1);1); "<=5")

In der beiliegenden Excel-Datei habe ich obige Formeln bereits eingefügt.

 

a) Kalkulation manuell
b) alle Formeln rauswerfen und nur auf Anforderung über VBA rechnen lassen
c) Modell umstellen von Formeln auf Pivot-Tabellen, denn die rechnen genau einmal y Werte, während x Formeln x*y Berechnungen/Abfragen für y Werte veranstalten.

 

Ganz lieben Dank Anton, damit hast Du mir sehr geholfen.
Vor allem habe ich endlich die Indexfunktion verstanden. Damit hatte ich es vorher auch schon versucht, aber nicht hinbekommen.
Die Funktion "Zeilen", kannte ich bisher garnicht. Auch sehr interessant.

Mit "Zeile(Bereich)" liest Du die erste Zeile des angegebenen Bereiches aus. Wäre ich nie darauf gekommen das sowas geht.
Bei mir privat mit Win7 und Office 2010 funkioniert das auch. Auf dem Firmenrechner mit Win10 und Office 365 gibt das allerdings einen Überlauf, da eben nicht die erste Zeile sondern alle Zeilen genommen werden.
Da musste ich "MIN(ZEILE(BEREICH))" nehmen oder direkt einen Zellbezug auf die erste Zeile der Tabelle setzen.
Bist Du sicher das man "Zeile" so verwenden kann? Habe ich vielleicht etwas falsch gemacht?

Jedenfalls konnte ich gestern alle Formeln meiner originalen Datei anpassen und bin "Indirekt" komplett los.
Danke dafür.


@ lupo1
Danke für Deine Antwort, allerdings hatte ich ja extra geschrieben das Makros keine Option sind, und weil die Kollegen nicht wirklich Excel-affin sind ist auch die manuelle Neuberechnung keine Lösung, auch wenn ich sie gerne verwendet hätte wenn man sie blattweise und nicht Mappenweit benutzen könnte.
Ob Pivottabellen eine Lösung wären kann ich nicht beurteilen, denn damit habe ich mich immer noch nicht beschäftigt und kenne mich garnicht aus.

 

Hallo Achim,

die Funktion INDEX(Bezugsbereich; Zeile; [Spalte]) kann auf zweierlei Arten genutzt werden:
1) indem sie den Zellwert im Schnittpunkt Zeile+Spalte innerhalb von Bezugsbereich liefert.
2) indem sie den Zellbezug im Schnittpunkt Zeile+Spalte innerhalb von Bezugsbereich liefert.
Beides Mal sind sowohl Zeile als auch Spalte als die Zeilen/Spalten-Nummer relativ zum Bezugsbereich zu nehmen.

Ich habe in der Spalte L und auch in der Spalte N die INDEX-Funktion in der 2. Variante genutzt.
Dadurch konnte ich zB. für Spalte L folgenden Formelausdruck bilden:
INDEX([Feiertag]; AnfangszeilenNr; 1) : [@Feiertag]
wobei AnfangszeilenNr durch den Ausdruck
ZEILE()-ZEILE(XYZ)-[@[Transport dauer LG]]-[@[WE Ein]]
zu ersetzen ist.
Wegen der ":"-Schreibweise liefert die INDEX([Feiertag]; AnfangszeilenNr; 1)-Funktion den Zellbereichsanfang (linke obere Zelle) und [@Feiertag] das Zellbereichsende (rechte untere Zelle) vom ohnehin einspaltigen Bezugsbereich [Feiertag].


Die ZEILE()-Funktion liefert die Zeilennummer derjenigen Zelle, in der sie sich befindet. Diese Zeilennummer ist jedoch die Zeilennummer des aktiven Arbeitsblattes.
Die ZEILE(XYZ)-Funktion liefert die Zeilennummer der ersten Zeile des Zellbereiches XYZ, wobei XYZ bei dir der Datenbereich deiner strukturierten Tabelle (namens "Tabelle1014") inklusive Überschriftenzeile im Arbeitsblatt ist. Damit aber liefert dieser Ausdruck die Zeilennummer der Tabellenüberschriftzeile (=5). Dieser Wert muss deshalb von ZEILE() abgezogen werden, weil innerhalb der INDEX()-Funktion die relative Zeilennummer bezüglich des Bereiches [Feiertag] benötigt wird.
Die weiteren Subtraktionen der Tabellenfeldwerte [@[Transport dauer LG]] und [@[WE Ein]] der aktuellen Tabellenzeile sind durch deine Vorgaben bedingt.

Damit ist der erste Kriterienbereich Krit1 der ZÄHLENWENNS(Krit1; Wert1; Krit2; Wert2)-Funktion erklärt. Der zweite Kriterienbereich Krit2 erklärt sich analog (angewendet auf den [Tag]-Spaltenbereich).


Der erste Kriterienbereich Krit1 der ZÄHLENWENNS(Krit1; Wert1; Krit2; Wert2)-Funktion in der N-Spalte lautet wie folgt:
[@Feiertag] : INDEX([Feiertag]; MinEndezeilenNr; 1)
wobei MinEndezeilenNr durch den etwas komplizierteren Ausdruck
MIN(ZEILE()-ZEILE(XYZ)+[@[Transport dauer VG]+[@[WE Aus]]+1; ZEILEN(XYZ)-1)
zu ersetzen ist. Dabei ist der Formelteil MIN(EndeZeilenNr; ZEILEN(XYZ)-1) dadurch bedingt, eine Fehlermeldung in der INDEX()-Funktion durch Überschreiten des in dieser Funktion verwendeten Bezugsbereiches [Feiertag] zu vermeiden.
Dabei liefert die Funktion ZEILEN(XYZ) die Zeilenanzahl der strukturierten Tabelle (inklusive Überschriftenzeile). Weil nur die Datenzeilen interessieren, muss 1 subtrahiert werden.
Die MIN(EndeZeilenNr; DatenzeilenAnzahl)-Funktion liefert damit den kleineren der beiden angeführten Werte und es bleibt die Zeilennummer innerhalb des Bezugsbereichs [Feiertag]. Dies ist wichtig für die Formeln der letzten Zeilen der strukturierten Tabelle in der N-Spalte.

Diese Erläuterungen gelten für die Excel-Version 2016. Falls sich für Excel-365 ein abweichendes Verhalten ergeben sollte, so kann ich dazu nichts sagen, denn ich besitze diese Version nicht.