Die Excel-Funktion BEREICHE

In der Excel-Funktionsbibliothek gibt es unter der Kategorie „Nachschlagen und Verweisen“ eine Funktion namens BEREICHE.

Ich bin ehrlich, mit dieser Funktion konnte ich bisher nicht viel anfangen. Dennoch will ich sie Dir in diesem Beitrag ein wenig näherbringen.

1. Beschreibung und Syntax

Mit der Funktion BEREICHE wird die Anzahl der Bereiche in einem Bezug / in einer bestimmten Referenz zurückgegeben.

Mehrere Verweise müssen mit einem zusätzlichen Klammerpaar eingeschlossen werden.

Die Syntax ist ganz einfach und lautet:

BEREICHE(Bezug)

Dabei kann ein im Bezug definierter Bereich aus einer einzelnen Zelle oder aus mehreren zusammenhängenden Zellen bestehen.

2. BEREICHE bei einzelnen Zellen

Der einfachste Fall ist das Zählen einzelner Zellen in einem Bezug.

So kann der Bezug aus einer Zelle, aus zwei, drei, vier oder mehreren auseinanderliegenden einzelnen Zellen bestehen.

Handelt es sich nur um eine Zelle, genügen einfache Klammern. Handelt es sich um mehrere Zellen, sind doppelte Klammern erforderlich (siehe Abbildungen).

3. BEREICHE bei überlappenden Zellen

Die definierten Bereiche können sich auch überlappen. Die folgenden Abbildungen zeigen ein paar Varianten.

In allen gezeigten Fällen sind doppelte Klammern notwendig.

Im Blog Contextures von Debra Dalgleish [1] habe ich folgendes Beispiel gefunden:

Zunächst wurden die Bereiche Bereich1 und Bereich2 im Menü Formeln/Namen definieren wie in der Abbildung zu sehen definiert. Bereich1 ist blau, Bereich2 ocker gefärbt.

Das Beispiel beschreibt das Argument Bezug nun dadurch, dass die namentlichen Bereiche aufgezählt, nicht aber durch ein Semikolon, sondern durch ein Leerzeichen getrennt werden.

Debra Dalgleish schreibt dazu:

„Wenn Sie das Leerzeichen verwenden, um aus den Referenzen einen Schnittpunkt zu erstellen, werden die Schnittpunktebereiche gezählt.“

Die markierten Bereiche schneiden sich an drei Punkten, die mit fetten gestrichelten Rändern umrandet sind. Das Formelergebnis ist 3.

4. BEREICHE in der INDEX-Funktion

Die INDEX-Funktion gibt es in der Matrixversion und in der Bezugsversion.

In diesem Beispiel, das ich auch auf Contextures [1] gefunden habe, soll die Bezugsversion verwendet werden. In nachfolgender Tabelle sind vier Gruppen mit jeweils den Zelladressen dargestellt.

Markiere die Gruppen in der Reihenfolge B4:B10;D4:D10;F4:F10;H4:H10. Vergib hierfür im Menü Formeln/Namen definieren z.B. den Namen „Test1“.

Angenommen, Du suchst nun den Wert in der dritten Zeile und in der ersten Spalte.

Schreibe dazu die INDEX-Formel in der Bezugsversion

Die Argumente sind Bezug, Zeile, Spalte und Bereich.

=INDEX(Test1;3;1;BEREICHE(Test1))

Als Bezug trägst Du den Namen des gerade definierten Bereiches „Test1“ ein.

Du suchst den Wert in Zeile 3 und Spalte 1.

Für das Argument „Bereich“ schreibst Du

BEREICHE(Test1)

Die Formel =BEREICHE(Test1) gibt 4 zurück.

Die INDEX-Formel gibt H6 zurück, also den Wert in der dritten Zeile des Bereiches „Test1“. Der namentliche Bereich wird als eine Spalte betrachtet, denn Du hast in der INDEX-Formel als Argument Spalte1 bestimmt.

Ein Widerspruch?

Nein. Die Gruppe 4 wurde bei der Namensdefinition als letzte Referenz ausgewählt. Der definierte Bereich wird als eine Spalte betrachtet. Deshalb wird der Wert in Zeile 3 der letzten Referenz ausgegeben.

Eine Probe dazu:

Markiere die Gruppen in der Reihenfolge H4:H10;F4:F10;D4:D10;B4:B10. Vergib hierfür im Menü Formeln/Namen definieren z.B. den Namen „Test2“.

Angenommen, Du suchst wieder den Wert in der dritten Zeile und in der ersten Spalte.

Schreibe dazu die INDEX-Formel in der Bezugsversion

Die Argumente sind Bezug, Zeile, Spalte und Bereich.

=INDEX(Test2;3;1;BEREICHE(Test2))

Als Bezug trägst Du den Namen des gerade definierten Bereiches „Test2“ ein.

Die INDEX-Formel gibt nun den Wert B6 zurück, also wieder den Wert in der dritten Zeile der zuletzt geschriebenen Referenz, der Gruppe 1.

5. BEREICHE in der bedingten Formatierung

Ein letztes Beispiel zur Funktion BEREICHE will ich hier zeigen.

Ich habe es im Buch „Microsoft Excel – Formeln & Funktionen“ [2] gefunden.

Angenommen, Du willst auf Deinem Arbeitsblatt, Tabelle3, eine Liste mit beliebigen Werten anlegen. In Zelle B1 befindet sich die Überschrift „Liste“. Die Werte sollen nun gleich nach der Überschrift von oben nach unten eingetragen werden.

Die ersten 10 Werte liegen bereits vor.

Die Liste soll abschließend nicht mehr als 30 Einträge enthalten. Zelle B31 wird daher den letzten Wert enthalten. Ist diese Zelle erreicht, soll sich Zelle B1 mit der Überschrift einfärben.

Die Idee dazu ist, die Bereiche in der Schnittmenge zwischen Deiner Liste und der Zelle B31 mit der Formel

=BEREICHE(Liste Tabelle3!$B$31)

zu zählen.

Die Umsetzung dieses Gedankens schlägt allerdings fehl, da das Dialogfeld zur bedingten Formatierung keine Schnittmengenoperationen erlaubt.

Es ist also ein Trick notwendig, um die Aufgabe zu lösen.

Gehe zuerst ins Menü Formeln / Namen definieren. Vergib den Namen „Liste“ und schreibe unter „Bezieht sich auf“ die Formel

=BEREICH.VERSCHIEBEN(Tabelle3!$B$1;0;0;ANZAHL(Tabelle3!$B$1:$B$31)+1)

(über diesen Link findest Du einen früheren Beitrag zur Funktion BEREICH.VERSCHIEBEN)

Vergib einen weiteren Namen: „Formel“. Schreibe unter „Bezieht sich auf“ nun die obige Formel

=BEREICHE(Liste Tabelle3!$B$31)

Aktiviere nun Zelle B1. Gehe im Menü Start / Formatvorlagen auf die bedingte Formatierung. Wähle „Neue Regel“ und dort „Formel zur Ermittlung der zu formatierenden Zellen“.

Schreibe in das Formelfeld des Dialogfeldes die Formel

=(Formel=1)

und formatiere z.B. fett und rote Schrift.

Trage jetzt, beginnend ab Zelle B12 weitere Werte in Deine Liste ein.

Wenn Du Zelle B31 erreichst, wird die Überschrift in B1, wie vorgegeben, bedingt formatiert.

6. Fazit

Die Funktion BEREICHE allein gibt nichts Nennenswertes her.

In Verbindung mit anderen Funktionen, gerade durch die Schnittstellenoperation ist sie dann doch noch zu gebrauchen.

Quellen:

[1]   https://contexturesblog.com/archives/2011/01/03/30-excel-functions-in-30-days-02-areas/

[2] Egbert Jeschke u.a.: Microsoft Excel – Formel & Funktionen – Das Maxibuch, 3. Auflage, O’Reilly Verlag, Köln 2013, Seite 284 f.

4 Kommentare zu „Die Excel-Funktion BEREICHE“

  1. Hallo,

    diese Formel hat wohl noch einen falschen Bezug unter Punkt 4. (Test1 statt Test2):
    =INDEX(Test1;3;1;BEREICHE(Test2))
    Richtig müßte es heißen:
    =INDEX(Test2;3;1;BEREICHE(Test2))

    Einen sinnvollen Einsatz habe ich noch nicht dafür gefunden.

    Danke.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s