Autor: Peter Haserodt  --- Aus Excel Standard - Gruppe: Funktionen

Bereich.Verschieben

Autor: Peter Haserodt - Erstellt: ?      - Letzte Revision: 2010-11-11
Bereich.Verschieben wäre nahezu genial ...
... wenn Sie nicht zu den volatilen Funktionen gehören würde!
(Mehr über volatile Funktionen finden Sie unter: !Langsames Excel (1) - Volatile Funktionen )

Eine weitgehend unbekannte Funktion, die aber ungeahnte Möglichkeit hat.
Ich möchte ihnen hier die grundlegene Struktur vermitteln, Anwendungen werden Sie dann selber finden.
Trotzdem möchte und muss ich hier erwähnen, dass man in vielen Fällen mit ein wenig Mathematik diese Funktion durch die Bezugsvariante der Indexfunktion ersetzen kann.


Als erstes das Beispiel:

 
 ABCDEFG
1       
2 Nur Optik1234 
3Nur Optik JanuarFebruarMärzApril 
41Äpfel101001.00010.000 
52Birnen202002.00020.000 
63Pflaumen303003.00010.000 
74Austern404004.00020.000 
8       
9       
10       
11 Suchzeile3 Suchspalte:3 
12 Summe:13.330  10.000 
13       
14 Anzahl Zeilen2 Anzahl Spalten3 
15       
16 Summe:33.330  11.100 
17       
Formeln der Tabelle                                                                                                    
C12 : =SUMME(BEREICH.VERSCHIEBEN(C4:F4;C11-1;0))
F12 : =SUMME(BEREICH.VERSCHIEBEN(C4:C7;0;F11-1))
C16 : =SUMME(BEREICH.VERSCHIEBEN(C4:F4;0;0;C14))
F16 : =SUMME(BEREICH.VERSCHIEBEN(C4:C7;0;0;;F14))
 

Peter Haserodt
  • Formelhilfe
  • Makroentwicklung
  • VBA-Programmierung
  • + + + + + + + + +
Die Funktionsweise und der Hintergrund:

Man arbeitet in Excel oft mit Bereichen. Manchmal wäre es aber schön, wenn ich einen Bereich abhängig von einer Position angeben kann. Genau dies macht Bereich.Verschieben.

Schauen wir uns die Formel in Zelle C12 an und reduzieren wir diese erstmal ein wenig:

Wenn ich dort: Summe(C4:F4) schreibe, ist klar, was dabei herauskommt, nämlich die Summe der ersten Zeile des Ockerfarbenen Bereiches. Also dies was neben Äpfel steht.

Jetzt ist die Aufgabe, diesen Bereich nicht fest zu verdrahten, sondern der Anwender soll aussuchen können, welche Zeile er summieren willl. Diese gibt er in der Zelle C11 ein.

Es wird jetzt Zeit, sich die Syntax von Bereich.Verschieben anzuschauen:
BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe];[Breite])

Am Anfang interessieren uns nur die ersten 3 Argumente.
1. Bereich: Dies ist klar, der ursprüngliche Bereich, in unserem Beispiel C4:F4

2. Zeilen: Dies ist eine Ganzzahl zum Verschieben der Zeilenposition.
In unserem Beispiel ist die Zeilenposition jeweils 4 (C4:F4)
Je nachdem was ich für Zeilen angebe berechnet Excel nun einen neuen Bereich für die Zeilen,
Gebe ich 0 an, so bleibt alles beim Alten, gebe ich z.B. 2 an wird jeder Zeilenbezug im Bereich um 2 erhöht,
also aus C4:F4 würde C6:F6, bei - 3 daraus C1:F1 (Solange ich natürlich nicht die Spalten verändere).

Und bei Zeilen 2 und einem Bereich von D11:C13 würde daraus natürlich D13:C15


3. Spalten: Dies ist eine Ganzzahl zum Verschieben der Spaltenposition.
Spaltenzahl 2 bezogen auf unser Beispiel C4:F4 würde dann zu E4:H4, dies war jetzt aber einfach, oder?
Natürlich gibt der Spaltenversatz erst in unseren Beispiel in F12 einen Sinn.

Angeben müssen Sie aber Zeilen und Spalten immer und es ist klar, wenn ich nur die Zeilen verschieben will dann in Spalten eine 0 und umgekehrt.

Schauen wir uns jetzt die Formel in C12 komplett an:
=SUMME(BEREICH.VERSCHIEBEN(C4:F4;C11-1;0))

Wir nehmen als Ursprungsbereich C4:F4 und wollen diesen anhand der in C11 stehenden Zahl Zeilenweise verschieben.
Ist klar, das Spalte 0 sein muss.

Aber warum C11-1?
Ganz einfach, wenn ich die erste Zeile haben will, muss ich ja einen Zeilenverschub von 0 haben usw...

Ich könnte dies auch anders lösen mit:
=SUMME(BEREICH.VERSCHIEBEN(C3:F3;C11;0)), indem ich einfach den Ursprungsbereich um eines nach oben setze.
Die Formel in F12 können Sie jetzt sicherlich selbst nachvollziehen.

Bevor ich nun auf die Formeln in C16 und F16 komme, verdauen Sie erst mal das bisherige.

Höhe und Breite eines Bereiches Verändern:

Auch dies ist öfters mal ganz sinnvol, wenn z.B. nur die Monate Januar und Februar gefüllt sind, möchte ich natürlich nur die 2 Spalten.

In den Beispielen C16 und F16 habe ich darauf verzichtet, auch noch einen Zeilen, bzw. Spaltenversatz einzubauen.
Dies wäre dann doch etwas verwirrend.

Was wir hier einsetzen sind die optionalen Argumente Höhe, respektive Breite der Funktion.
Machen wir es kurz und schmerzlos:
Die Formel in C16:

=SUMME(BEREICH.VERSCHIEBEN(C4:F4;0;0;C14))

Hier ist das optionale Argument Höhe angegeben und das letze Argument Breite weggelassen.
Die Höhe die ich erhalten will steht in C14 in unserem Beispiel und hat dort den Wert 2.
Das bedeutet für Excel, das es den Bereich auf 2 Zeilen erweitern soll.

Aus C4:F4 wird dementsprechend -> C4:F5.

Denken Sie immer rechteckig bei soetwas. Linke obere Zelle, rechte untere Zelle.


Die Formel in F16:

=SUMME(BEREICH.VERSCHIEBEN(C4:C7;0;0;;F14))
Hier erhöhe ich die Spaltenzahl um den Wert in F14 (3).

Aus C4:C7 wird also C4:E7

Beachten Sie unbedingt hier die beiden Semikolons vor F14!

(Breite ist das letzte Argument der Funktion, um es angeben zu können, muss ich aber alle anderen Argumente der Funktion angeben. Da ich aber keine Höhe angeben will, lasse ich den Teil einfach leer aber das Semikolon benötige ich.

Zum Abschluss:

Selbstverständlich können die Werte für den Versatz auch mit anderen Funktionen ermittelt werden.
(Zum Beispiel mit Vergleich um ein Obst zu finden oder mit Anzahl um zu sehen wieviel Monate schon voll sind und und und...)
Und Sie sollten auch immer prüfen, ob sie nicht eine andere Berechnungsmöglichkeit finden.


Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben
ToDo
Google Werbung