Excel-Formeln mit Bereichsschreibweise (A1:A99999)

[Stefan_B_25]

Hallo Welt

ich bin gerade dabei einige bereits vorhandene Excel-Arbeitsmappen, die mir die Arbeit sehr erleichtern, auf die Bereichsschreibweise (A1:A99999) umzustellen.

Diese erspart immerhin die Verwendung von Makros, da die Formeln ja nur noch in einer Zelle stehen müssen und nicht jedes mal runter zu kopieren sind, wenn sich die Zeilenanzahl in der Quelltabelle ändern.

Leider stoße ich dabei immer wieder an Grenzen. Oder zumindest auf Probleme, weil die Formeln anders reagieren, als sie es bei der herkömmlichen Schreibweise tun.

Ich hoffe sehr, das ich hier die passenden Infos bekomme.

Im Einzelnen sind dies folgende Formeln:

1. UND
Hier habe ich den Effekt, dass die Ergebnisse der zwei Bedingungen beide WAHR sind, was sich zeigt, wenn man sie separat in Spalten darstellt. Sobald ich sie aber in die UND-Formel schreibe, kommt als Ergebnis für alle Zeilen FALSCH heraus. Anscheinend wird bei der Bereichsschreibweise jede Zeile mit jeder anderen Zeile verglichen.
Beispiel: =UND(A1:A99999)<>"";B1:B99999)<>""), soll WAHR bringen, wenn sowohl in Spalte A, als auch in Spalte B ein Wert steht.

2. HYPERLINK
Hier habe ich das Problem, dass ich mich als Quelle für den Pfad (Hyperlink_Adresse, als auch für den angezeigten Text (Freundlicher_Name) auf eine Spalte beziehe, in der ich Devices (komplette Pfade) einer unterhalb der Excel-Arbeitsmappe vorhandene Dateistruktur eingetragen habe.
So lange ich den Parameter "Freundlicher_Name" leer lasse, gibt es keine Probleme mit der Bereichsschreibweise. Sobald ich aber auch diesen mit der neuen Schreibweise befülle, geht nichts mehr und es wird für alle Zeilen nur der Text "0" angezeigt.

3. INDIREKT
Bei dieser Formel habe ich häufig das Problem, dass sie mit der Bereichsschreibweise nicht funktioniert, also eine Fehlermeldung generiert. Besonders dann, wenn ich sie in Kombination mit der Formel ADRESSE verwende. Da wo es ging bin ich auf die Formel INDEX umgestiegen.
Beispiel: (für Spalte 3 in der Tabelle "TABELLENBLATT")
Variante1:   =INDIREKT(ADRESSE(ZEILE(A2:A99999);3;4;1;"TABELLENBLATT")))
Variante2:  =INDEX(TABELLENBLATT!$1:$1048576;ZEILE(A2:A99999);3)

Ich habe schon eine zusätzliche Formel herum gebaut, damit nur die gefüllten Zeilen berücksichtigt werden und nicht alle bis Zeile 99999. Leider hat das auch nicht geholfen.
Beispiel (in TABELLENBLATT2): =WENN(ZEILE(A2:A99999)>ANZAHL2(TABELLENBLATT1!A:A);"";Tabelle 1!A2:A99999)

Ich bin dankbar für Lösungen, aber natürlich auch für Infos, die mich weiterbringen können.

Ich helfe natürlich auch selbst jedem gerne, der Fragen zu Excel hat.

Danke im Voraus.

Gruß Stefan

[Whiz-zarD]

vor 17 Stunden schrieb Stefan_B_25:

1. UND

Hier habe ich den Effekt, dass die Ergebnisse der zwei Bedingungen beide WAHR sind, was sich zeigt, wenn man sie separat in Spalten darstellt. Sobald ich sie aber in die UND-Formel schreibe, kommt als Ergebnis für alle Zeilen FALSCH heraus. Anscheinend wird bei der Bereichsschreibweise jede Zeile mit jeder anderen Zeile verglichen.
Beispiel: =UND(A1:A99999)<>"";B1:B99999)<>""), soll WAHR bringen, wenn sowohl in Spalte A, als auch in Spalte B ein Wert steht.

Wie soll die Formel

=(A1:A99999)<>""

funktionieren? A1:A99999 ist ein Vektor und kein einzelner Wert. Die Formel wird immer FALSCH zurückliefern.

Darüber hinaus ist das extrem inperformant. Formeln werden bei jeder Zellenänderung neu berechnet und jedes Mal erzeugt er diesen enorm großen Vektor. Je mehr du von diesen Formeln verwendest, desto mehr Zeit braucht er für die Berechnung, was irgendwann dazu führt, dass Excel aus sehr langsam wird. Mir schließt sich auch nicht, was das soll. Benutze doch einfach intelligente Tabellen.

https://www.hands-on-excel.com/intelligente-tabellen/

Die haben den Vorteil, dass sie noch eine spezielle Zellenangabe besitzen, die sich dynamisch verändert, sodass man solche Spielereien wie A1:A99999 nicht benötigt.

https://support.microsoft.com/de-de/office/verwenden-von-strukturierten-verweisen-für-excel-tabellen-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

vor 18 Stunden schrieb Stefan_B_25:

2. HYPERLINK
Hier habe ich das Problem, dass ich mich als Quelle für den Pfad (Hyperlink_Adresse, als auch für den angezeigten Text (Freundlicher_Name) auf eine Spalte beziehe, in der ich Devices (komplette Pfade) einer unterhalb der Excel-Arbeitsmappe vorhandene Dateistruktur eingetragen habe.
So lange ich den Parameter "Freundlicher_Name" leer lasse, gibt es keine Probleme mit der Bereichsschreibweise. Sobald ich aber auch diesen mit der neuen Schreibweise befülle, geht nichts mehr und es wird für alle Zeilen nur der Text "0" angezeigt.

Verstehe ich nicht.

vor 18 Stunden schrieb Stefan_B_25:

3. INDIREKT
Bei dieser Formel habe ich häufig das Problem, dass sie mit der Bereichsschreibweise nicht funktioniert, also eine Fehlermeldung generiert. Besonders dann, wenn ich sie in Kombination mit der Formel ADRESSE verwende. Da wo es ging bin ich auf die Formel INDEX umgestiegen.
Beispiel: (für Spalte 3 in der Tabelle "TABELLENBLATT")
Variante1:   =INDIREKT(ADRESSE(ZEILE(A2:A99999);3;4;1;"TABELLENBLATT")))
Variante2:  =INDEX(TABELLENBLATT!$1:$1048576;ZEILE(A2:A99999);3)

Was soll denn bei

=ZEILE(A2:A99999)

Auch rauskommen? Lies dir doch mal die Dokumentation durch: "Gibt die Zeilennummer des Bezuges zurück."
Was soll er denn bei der Formel zurückgeben, wenn du ihm 99998 Zeilen gibst? Da kannst du auch gleich 2 schreiben.

Ich glaube einfach, dass du nicht verstanden hast, was du da eigentlich tust, da du in allen Punkten die Funktionen völlig falsch verwendest.

[Stefan_B_25]

Am 1.3.2022 um 06:40 schrieb Whiz-zarD:

Wie soll die Formel

=(A1:A99999)<>""

funktionieren? A1:A99999 ist ein Vektor und kein einzelner Wert. Die Formel wird immer FALSCH zurückliefern.

Darüber hinaus ist das extrem inperformant. Formeln werden bei jeder Zellenänderung neu berechnet und jedes Mal erzeugt er diesen enorm großen Vektor. Je mehr du von diesen Formeln verwendest, desto mehr Zeit braucht er für die Berechnung, was irgendwann dazu führt, dass Excel aus sehr langsam wird. Mir schließt sich auch nicht, was das soll. Benutze doch einfach intelligente Tabellen.

https://www.hands-on-excel.com/intelligente-tabellen/

Die haben den Vorteil, dass sie noch eine spezielle Zellenangabe besitzen, die sich dynamisch verändert, sodass man solche Spielereien wie A1:A99999 nicht benötigt.

https://support.microsoft.com/de-de/office/verwenden-von-strukturierten-verweisen-für-excel-tabellen-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

Verstehe ich nicht.

Was soll denn bei

=ZEILE(A2:A99999)

Auch rauskommen? Lies dir doch mal die Dokumentation durch: "Gibt die Zeilennummer des Bezuges zurück."
Was soll er denn bei der Formel zurückgeben, wenn du ihm 99998 Zeilen gibst? Da kannst du auch gleich 2 schreiben.

Ich glaube einfach, dass du nicht verstanden hast, was du da eigentlich tust, da du in allen Punkten die Funktionen völlig falsch verwendest.

Hallo Whiz-zarD,

danke für Deine Antwort.
Habe endlich Zeit gefunden, mich mit Deinen Vorschlägen zu befassen.

Du hast Recht. Die "Intelligente Tabelle" ist sehr interessant. Insbesondere die dynamische Erweiterung der Zeilen.
Allerdings habe ich bis jetzt keine Lösung dafür gesehen, was die Übertragung dieser Dynamik auf andere Tabellenblätter betrifft.
Also die Funktionalität, dass sich Tabelle2 automatisch erweitert, wenn ich in Tabelle1 eine Zeile ergänze.
Eben diese erreiche ich durch meine Schreibweise.
Für die Formel "=ZEILE(A2:A99999)" sieht das prinzipiell so aus:
Formel in A2: 

Formel  in A3ff (wird automatisch ausgegraut)

Ergebnis:


Dies basiert auf der Schreibweise, wie sie auch bei den recht neuen Formeln, wie SORTIEREN, FILTER, oder auch EINDEUTIG zur Anwendung kommt.
Kombiniert mit einem Bezug zu einer anderen Tabelle und mit Bedingungen (Formel WENN, ISTZAHL, etc.) lässt sich so eine komplette Arbeitsmappe dynamisieren.
Es gibt halt einige Formeln, die sich halt nicht so leicht auf diese Schreibweise umstellen lassen. Und eben die sind Inhalt meiner obigen Fragen.

Vorschläge?

Danke im Voraus.

Gruß