Excel: Absolute Bezüge verhalten sich unterschiedlich beim Erweitern einer Tabelle

Computer: Software & Programmierung Office Software
#1

Hallo alle zusammen,

ich versuche gerade in Excel 2010 einen Bereich in einer Prüfung zu verwenden. Der Bereich soll von der aktuellen Zeile bis zur letzten Zeile der Tabelle (als Tabelle formatiert) gehen.
Angenommen die Tabelle ist im Bereich A1:B300. A1 sind die Überschriften, in A2 und den folgenden Zeilen ist in der Formel B2:B$300 enthalten, also habe ich als Bereiche:
A2: B2:B$300
A3: B3:B$300
A4: B4:B$300

Auf den ersten Blick funktioniert das einwandfrei. Wenn ich jetzt aber die Tabelle um eine Zeile erweitere, habe ich diese Bereiche:
A2: B2:B$301
A3: B3:B$300
A4: B4:B$300

Der Bereich in A2 wird automatisch um die zusätzliche Zeile erweitert, in den anderen Zeilen verändert sich allerdings nichts.

Warum reagieren die Bezüge in A2 anders als in den anderen Zeilen und wie bekomme ich es hin dass alle Zeilen gleich reagieren?

Gruß
Tobias

#2

Hallo Tobias

Dafür ist eine formatierte Tabelle absolut nicht geeignet! Innerhalb der Tabelle verhalten sich Formeln Deiner Art in einer Zelle nur dann „vernünftig“, wenn sie sich auf die gleiche Zeile (bzw. auf den gleichen Datensatz) beziehen. Versuche mal folgendes: Lösche in A2 Deine Formel. Jetzt gib in A2 ein „=summe(“ und versuche mit der Maus den Bereich festzulegen. In einer „normalen“ Tabelle kannst Du den Bereich mit der Maus nach Belieben senkrecht und/oder waagrecht festlegen. In einer formatierten Tabelle wirst Du das nie so hinkriegen, wie Du es haben möchtest. Das klappt nur, wenn Du den Bereich waagrecht in der gleichen Zeile mit der Maus festlegst.

Das hängt damit zusammen, dass die formatierten Tabellen Datenbanken im engeren Sinn sind. Die Zellen sind als Datenbankfelder strukturiert. Was Du in Deiner Tabelle vorhast, kannst Du z. B. auch in einer Access Tabelle nicht machen.

Eine Excel-Zelle in einer „normalen“ Tabelle hingegen ist ein freies, unabhängiges Kalkulationsfeld, in dem Du Werte oder Formeln nach Lust und Laune eingeben kannst.

Grüsse Niclaus

#3

Hallo Tobias

Da ich selber auch mit dem gleichen Problem kämpfe wie Du, habe ich „gehirnt“ und bin zu folgender Lösung mit INDIREKT() gekommen.

Dazu musst Du den Namen Deiner Tabelle kennen: In die formatierte Tabelle hineinklicken. Register Tabellentools/Entwurf wählen. Dort siehst Du ganz links unter Eigenschaften den Tabellennamen (Excel 2013). – Bei mir heisst die Tabelle „Tabelle1“.

Alle Formeln in Spalte A löschen. Dann in A2 folgende Formel eingeben:

=MAX(INDIREKT("B2:B"&ZEILEN(Tabelle1[#Alle])))

Dann wird Dir mit der Autokorrektur-Option vorgeschlagen: „Alle Zellen in dieser Spalte mit dieser Formel überschreiben.“ Das akzeptierst Du.

Statt MAX setzt Du natürlich diejenige Formel ein, die Du brauchst.

Ich hoffe, das hilft Dir. Grüsse Niclaus

#4

Nachtrag: Die obige Formel gilt nur, wenn in der strukturierten Tabelle keine Ergebniszeile aktiviert ist. Wenn eine aktiviert ist, lautet die Formel:

=MAX(INDIREKT("B2:B"&ZEILEN(Tabelle14[#Alle])-1))

Grüsse Niclaus

#5

Hallo,

optimal ist es nicht, aber da ich in mehreren anderen Formeln Bezüge habe die sich auf die komplette Spalte beziehen sollen, ist das mit der Tabellenformatierung viel einfacher da ich als Verweis z.B. einfach Tabelle1[Spalte1] verwenden kann. Außerdem spare ich mir dadurch das Kopieren sämtlicher Formeln der Tabelle nach unten da das die Tabelle bei jeder neuen Zeile automatisch macht.
Ich sehe aber kein Problem darin die Tabellenformatierung zu nutzen, solange ich keine Sortierfunktionen verwende.

Funktionieren würde es mit INDIREKT() , allerdings ist die Funktion sehr langsam. Bei ein paar Formeln merkt man davon zwar nichts aber bei mehreren hundert Zellen die diese Funktion benutzen kann es leicht passieren dass das gesamte Dokument deutlich langsamer reagiert.

Bei meiner Frage ging es mir weniger darum eine Lösung für das ursprüngliche Problem zu finden, auch wenn ich nichts dagegen hätte. Falls es dir hilft hier ein paar Ideen:

  1. Eine Zelle unterhalb der Tabelle nehmen von der sicher dass sie nicht erreicht wird, also z.B. B2:B$2000 . Falls man etwas unterhalb der Tabelle einträgt, muss man allerdings aufpassen dass das keine Probleme verursacht.

  2. Wenn nicht sowieso eine Ergebniszeile vorhanden ist eine leere Ergebniszeile verwenden, die so formatiert wird dass sie nicht sichtbar ist und B2:Tabelle1[[#Ergebnisse];[Spalte2]] verwenden. Der Nachteil wäre allerdings dass man die Tabelle manuell erweitern muss und das nicht automatisch bei Eingaben in der nächsten Zeile passiert.

  3. In meinem Fall brauche ich den Bereich für ZÄHLENWENNs() . Demnach wäre es auch möglich die ZÄHLENWENNS-Funktion zweimal zu verwenden, einmal für die komplette Spalte mit [Spalte2] und davon die zweite Formel für den Bereich B$2:B2 abziehen.

Mir ging es mit meiner Frage darum warum die Bezüge unterschiedlich behandelt werden und wie ich es schaffe dass alle Zeilen das gleiche machen, unabhängig davon wie sie sich dann verhalten. In diesem Fall muss ich es halt anders lösen, aber unter Umständen könnte das irgendwann mal zu größeren Problemen führen wenn gleiche Formeln unterschiedlich reagieren.

Gruß
Tobias

#6

Hallo Tobias
1 und 2 finde ich gute Lösungen.
Zu 1:

Eine Zelle unterhalb der Tabelle nehmen, von der sicher, dass sie nicht erreicht wird,

Es kann auch die unmittelbar nächste Zeile unterhalb der Tabelle sein. Diese Zeile wird automatisch bei jeder neuen Eingabe in der Tabelle nach unten geschoben. Bei einer neuen Eingabe in die Tabelle muss allerdings die Tabulator-Taste anstelle der Return-Taste verwendet werden.

Zu 2:

Der Nachteil wäre allerdings dass man die Tabelle manuell erweitern muss und das nicht automatisch bei Eingaben in der nächsten Zeile passiert.

Auch hier geschieht die Erweiterung der Tabelle automatisch, wenn man für eine neue Eingabe die Tabulator-Taste benutzt.

Grüsse Niclaus