Professionelle Seminare im Bereich MS-Office
Der Name BEREICH.VERSCHIEBEN() ist etwas unglücklich gewählt. Man kann mit der Funktion Bereiche zwar verschieben, man kann Bereiche aber auch verkleinern oder vergrößern. Besser wäre es, die Funktion Bereich.Verändern zu nennen, aber sie heißt nun mal so wie sie heißt.
Die Funktion hat den Aufbau:
BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;Höhe;Breite) Bezug:
Ist die Zelle von der aus wir einen Bereich neu bestimmen. Das ist der Dreh und Angelpunkt.
Zeilen:
Ist die Anzahl der Zeilen, um die ein Bereich verschoben wird. Wird der Bereich nicht verschoben, hat der Parameter „Zeilen“ den Wert 0.
Spalten:
Ist die Anzahl der Spalten, um die ein Bereich verschoben wird. Wird der Bereich nicht verschoben hat der Parameter „Spalten“ den Wert 0.
Höhe:
Das ist die Höhe des Bereichs. Benutzen wir zur Bestimmung des Wertes von Höhe keinen festen Wert sondern z. B: die Funktion ANZAHL() so ist der Bereich per se dynamisch. Wir werden das später bei einem Diagramm und einer Pivottabelle verwenden.
Breite:
Das ist die Breite des Bereichs. Benutzen wir zur Bestimmung des Wertes von Breite keinen festen Wert sondern z. B: die Funktion ANZAHL() so ist der Bereich per se dynamisch.
Nur aus der Theorie heraus ist es kaum möglich, die Funktion zu verstehen, wir sollten also einige Zeit mit praktischen Beispielen verbringen. Vergleichen Sie dazu bitte auch die Beispieltabellen.
Professionelle Seminare im Bereich MS-Office
Beispiele für das Verschieben eines Bereichs
Ein Bereich ist ein zusammenhängender „Block“ von Zellen. Ein Bereich kann aber auch eine einzelne Zelle sein, wie in Beispiel 1.
Beispiel 1:
Der Ausgangspunkt (Bezug) ist A1. Wir verschieben um eine Zeile, also sind wir dann in Zeile 2. Wir verschieben um eine Spalte, also sind wir dann in Spalte B. Der relevante Bereich soll eine Zeile hoch und eine Spalte breit sein, also eine einzige Zelle umfassen.
Somit ist der neue Bereich nicht mehr A1 sondern B2. Der Inhalt von B2 aber ist die Zahl 100.
Beispiel 2:
Wenn wir nicht eine sondern 3 Zeilen verschieben landen wir in B3.
Solche Dinge kann man z. B. verwenden, wenn man in großen Tabellen gezielt auf Werte zugreifen will, ohne zu scrollen.
Professionelle Seminare im Bereich MS-Office
Beispiele für das Erweitern oder Verkürzen eines Bereichs
Beispiel 3:
Wirklich interessant wird BEREICH.VERSCHIEBEN(), wenn wir die Funktion mit anderen Funktionen verbinden. Hier wird eine Summe errechnet, aber nicht fix, sondern der Bereich, der summiert werden soll, wird von BEREICH.VERSCHIEBEN() bestimmt.
Wir verschieben um 0 Spalten und um 0 Zeilen. Die Höhe ist 3. A1 + A2 +A3 gibt 60.
Die Breite des Bereichs ist 1, also werden nur Werte aus der einen Spalte, nämlich Spalte A verwendet.
Am Beispiel der Summe wird eventuell am einfachsten deutlich, was BEREICH.VER-
SCHIEBEN() macht. Wenn Sie schreiben =Summe(A1:A3), dann sagen Sie Excel, dass Sie eine Summe wollen von A1 bis A3. Sie bestimmen also Anfang und Ende der
Summierung. Bei BEREICH.VERSCHIEBEN() haben Sie ebenfalls einen Anfangswert nämlich den Namen der Zelle, der im Parameter „Bezug“ steht. Aber an Stelle eines
Endwertes haben Sie die Höhe des Bereichs, der summiert werden soll. Wenn Sie bei Höhe den Wert 3 setzen, geht der Bereich von A1 bis A3, weil das drei Zeilen sind. Wenn Sie als Bezug C9 setzen und als Höhe 7 geht der Bereich von C9 bis C15, von 9 bis 15 sind es 7 Zeilen.
Professionelle Seminare im Bereich MS-Office
Beispiel 4:
Ändern wir die Formel nur wenig und bestimmen die Breite mit dem Wert 2 ist der Bereich 2 Spalten breit.
Der Beriech beginnt bei A1, es wird nicht verschoben. Der Bereich ist 3 Zeilen hoch, also geht er von A1 bis A3. Der Bereich ist 2 Spalten breit, also geht er von A1 bis B3.
10+20+30+100+200 = 360
Professionelle Seminare im Bereich MS-Office
Beispiel 5:
In H2 ermittelt die Funktion ANZAHL() die Anzahl der Einträge in Spalte F.
Tragen Sie bitte ab F6 weitere Werte ein, Sie sehen, wie sich der Inhalt von H2 verändert.
Löschen Sie H6 ff bitte wieder.
Hier wird wieder eine Summe gebildet, der Bereich, der summiert wird ist aber variabel, weil er von BEREICH.VERSCHIEBEN() bestimmt wird.
Die Höhe für BEREICH.VERSCHIEBEN() ist nicht fix sondern wird von der Funktion ANZAHL() geliefert. Das bedeutet, je mehr Zellen in Spalte F mit Zahlen gefüllt werden, desto größer wird auch der Bereich, der summiert wird.
Professionelle Seminare im Bereich MS-Office
Beispiel 6
Wechseln Sie bitte in Tabelle2.
Sie sehen hier die Umsatzzahlen eines Unternehmens. In G2 geben Sie ein, für welchen Monat Sie die Summen möchten und in G5 soll der Wert ausgegeben werden.
Natürlich wird man bei der geringen Menge an Daten einfach in Zeile 12 die
Summenfunktion nutzen. Aber bei großen Datenmengen lohnt sich der Einsatz der Funktion Bereich.Verschieben durchaus.
Die Formel in G5:
Wir gehen aus von B2 (Bezug). Die Anzahl Zeilen, um die verschoben wird ist 0. Die Anzahle der Spalten, um die verschoben wird ist die Nummer des Monats minus 1. Der Bereich ist 10 Zeilen hoch und eine Spalte breit.
