15.6 Matrix- und Bereichsfunktionen 

Die unter dieser Rubrik aufgeführten Funktionen dienen in erster Linie der Behandlung von Bezügen: dem Ermitteln der Adressen von Zellen, der Größe von Bereichen, dem Durchsuchen von Bereichen etc.

Obwohl diese Funktionen alle in Tabellen verwendet werden können, finden sie zum größten Teil eher in der Makroprogrammierung Anwendung.

Verweistabellen abfragen 

Zu den Funktionen, die für ganz unterschiedliche Situationen von großem Nutzen sind, gehören die beiden Verweisfunktionen WVERWEIS und SVERWEIS. Sie können überall da benutzt werden, wo es darum geht, aus einer vorhandenen Tabelle, deren erste Zeile oder Spalte in aufsteigender Reihenfolge sortiert ist, gezielt Informationen herauszuziehen. Typische Beispiele für solche Tabellen sind Steuertabellen, Inventarlisten, Verzeichnisse und Kataloge. Der Unterschied zwischen den beiden Funktionen besteht darin, dass im ersten Fall die Tabelle waagerecht aufgebaut ist, im zweiten Fall senkrecht.

In der Tabelle sind durchschnittliche Werte für Wohnungen aufgelistet. In der ersten Zeile ist die Zimmeranzahl in aufsteigender Reihenfolge eingetragen. Für jede Zimmeranzahl sind nun in den Spalten bestimmte Daten aufgeführt. Das heißt, die Daten von Zelle B5 bis B7 gehören zu den Einzimmerwohnungen, die Daten in den Zellen C5 bis C7 gehören zu den Zweizimmerwohnungen usw.

Waagerechte Verweistabelle

Der gesamte Datenbereich der Tabelle ist im Beispiel mit dem Namen »Wohnung« bezeichnet. Nun wollen Sie die Miete für die Dreizimmerwohnungen abgreifen. Die Formel lautet:

=WVERWEIS (3 ;Wohnung; 2)

Die Funktion hat drei Argumente:

Zeilenindex meint, die wievielte Zeile in der Matrix den Ergebniswert liefern soll. Das hängt davon ab, welche Information für den gewählten Fall gewünscht wird. Interessieren Sie sich für die Nebenkosten, wäre der Index = 3.

Die Funktion geht also mit dem angegebenen Suchkriterium in die erste Zeile, sucht von links nach rechts nach einem Wert, der zum Suchkriterium passt. Ist die entsprechende Spalte gefunden, wandert der Zellzeiger zu der Zeile abwärts, deren Index angegeben wurde. Die Funktion liefert schließlich den dort gefundenen Zellinhalt als ihr Ergebnis. Dieser Inhalt kann ein numerischer Wert, aber auch ein Text sein. Gibt es keinen mit dem Suchkriterium identischen Wert in der Zeile, wird der Wert genommen, der vor dem nächsthöheren liegt. Wäre das Suchkriterium z. B. 2,5 Zimmer, liefert die Funktion den Wert für 2 Zimmer.

Die Funktion SVERWEIS wird bei Tabellen benutzt, die senkrecht aufgebaut sind. Typisches Beispiel ist eine Steuertabelle. Dort sind in der ersten Spalte in aufsteigender Reihenfolge die verschiedenen Einkommensstufen aufgeführt. Zu jeder Einkommensstufe sind in den Spalten rechts neben der ersten Spalte die Steuerbeträge für die verschiedenen Steuerklassen aufgeführt.

Wer nachschauen will, was er zu zahlen hat, liest zunächst die erste Spalte von oben nach unten, bis er einen Betrag gefunden hat, der seinem Einkommen entspricht. Dann geht er in der entsprechenden Tabellenzeile so viele Spalten nach rechts, bis er in der Spalte angekommen ist, die seiner Steuerklasse entspricht. Nichts anderes tut die SVERWEIS-Funktion.

Ein einfacheres Beispiel, das in der Abbildung verwendet wird, ist eine Devisentabelle. Links stehen in kleinen Sprüngen die €-Beträge. In den Spalten daneben die entsprechenden Beträge für Lira und FRF.

Senkrechte Verweistabelle

Der gesamte Datenbereich der Tabelle ist im Beispiel mit dem Namen »DEVISEN‘ bezeichnet. Die Funktion kann nun etwa die Frage beantworten: Wie viele Lire entsprechen 150 €?

INDEX-Funktionen 

Excel bietet zwei unterschiedliche INDEX-Funktionen an:

=INDEX (Bezug; Zeile;Spalte;Bereich)

für die Abfrage von Datentabellen

=INDEX (Matrix; Zeile; Spalte)

für die Abfrage einer Matrix.

Die INDEX-Funktion für Datentabellen beantwortet die Frage, was in der Zelle eingetragen ist, die im Schnittpunkt der Zeile n und der Spalte m des mit Bezug angegebenen Bereichs liegt. Das letzte Argument Bereich kann angegeben werden, wenn der Bezug eine Mehrfachauswahl enthält. Sind also z. B. drei Zellblöcke in der Mehrfachauswahl enthalten, bedeutet eine 2 für Bereich, dass sich die Zeilen- und Spaltennummern auf den zweiten Zellblock beziehen. Wird Bereich nicht angegeben, wird immer der erste Block genommen.

Im Beispiel, das durch die Abbildung verdeutlicht wird, ist ein Bereich für die Produktionsergebnisse mehrerer Werke der letzten fünf Jahre mit dem Namen »PRODUKTION« definiert. Die Zelle, die in der dritten Spalte und der dritten Zeile dieses Bereichs liegt, enthält den Betrag 2.100.000.

Ein Beispiel für die Funktion INDEX

Diese INDEX-Funktion arbeitet also nicht mit der Zelladresse, sondern mit der relativen Position einer Zelle in einem definierten Bereich. Die Spalten- oder Zeilennummer kann natürlich auch das Ergebnis einer Formel sein.

Die INDEX-Funktion kann auch benutzt werden, um Werte aus einer Matrix herauszulesen. Dies kann ein einzelner Wert sein, aber auch eine Matrix. Das Matrix-Argument kann als Bereichsbezug oder als Matrix-Konstante eingegeben werden.

Um nicht nur einen einzelnen Wert auszulesen, sondern eine Matrix, kann der Wert für Zeile und/oder Spalte auf Null gesetzt oder weggelassen werden.

=INDEX({3.5.9.7;34.54.23.98} ;0;0) 
=INDEX({3.5.9.7;34.54.23.98};;)

liefern als Ergebnis die gesamte Matrix.

=INDEX({3.5.9.7;34.54.23.98};2)

ergibt eine Matrix mit den Werten der zweiten Zeile.

=INDEX({3.5.9.7;34,54.23.98};;2)

ergibt eine Matrix mit den Werten der zweiten Spalte.

Soll das Ergebnis der Formel wiederum eine Matrix sein, muss wie bei der Eingabe von Matrix-Formeln verfahren werden. Sie markieren also zunächst einen entsprechend großen Bereich für die Ergebnis-Matrix und geben in einer beliebigen Zelle die INDEX-Funktion ein. Die Formel muss mit + + abgeschlossen werden.

Die Funktion WAHL 

Die Funktion WAHL liefert einen beliebigen Wert aus einer gegebenen Liste von maximal 29 Werten. Die Syntax lautet:

=WAHL(Index;Wertl;Wert2; . . . ;Wert29)

Das erste Argument der Funktion gibt an, das wievielte Element der Liste gesucht wird. Index kann als Zahl, Bezug oder Formel eingegeben werden. Als Werte können Zahlen, Zellbezüge, Namen, Texte und Formeln verwendet werden. Bereichsbezüge sind nicht erlaubt. (Um Werte aus Bereichen zu ziehen, ist ja die Funktion INDEX vorhanden).

Hier ein praktisches Beispiel für die Nutzung der WAHL-Funktion: Kunden sind in verschiedene Rabattstufen eingeteilt. Die verschiedenen Rabatte werden in der Zeile 7 aufgelistet. Der Kunde mit der Rabattstufe 1 soll den ersten Rabatt aus dieser Liste erhalten, der Kunde mit Stufe 2 den zweiten etc. Die Rabattstufen sind in der Spalte C ab der Zelle C9 eingetragen.

Die Formel wird zunächst in der Zelle D9 entwickelt. Sie kann lauten:

=WAHL(C9;E7;F7;G7)

Nun sollen die Rabattsätze für die anderen Kunden bestimmt werden. Es liegt nahe, die Kopierfunktion zu benutzen. Mit dem Ergebnis werden Sie aber nicht zufrieden sein. Der Fehler folgt daraus, dass die Adressen der Zellen mit den Rabattsätzen relativ eingetragen worden sind. Es soll aber für jeden Kunden dieselbe Rabattliste zugrunde gelegt werden. Also müssen die Zellen für die Rabatte absolut adressiert werden. Wenn Sie die Formel entsprechend korrigieren

=WAHL(C9;E$7;F$7;G$7)

kann das Ergebnis ohne weitere Probleme kopiert werden. Anstelle der Zellverweise in der Argumentliste könnten die Prozentsätze auch direkt als Konstanten in die Formel eingetragen werden, also

=WAHL(C9;5%;7%;8%)

Dadurch ginge aber einiges an Flexibilität verloren. Änderte sich der Rabattsatz, müssten auch die Formeln geändert werden. Besonders interessant ist die WAHL-Funktion auch für Berechnungsalternativen. In diesem Fall können verschiedene Formeln als Argumente benutzt werden. Hier ein Beispiel:

A (Dl; SUMME( POSTEN) *0,70; SUMME ( POSTEN) *0,75; SUMME(POSTEN) *0, 80+PORTO)

Abhängig von dem in Dl abgestellten Wert wird die Summe der Posten mit einem anderen Faktor multipliziert. Im dritten Fall werden noch Portokosten addiert. Während die WENN-Funktion nur eine einfache Verzweigung zwischen zwei Möglichkeiten zulässt, es sei denn, die Formel arbeitet mit verschachtelten WENN-Funktionen, erlaubt die WAHL-Funktion die Auswahl aus bis zu 29 Möglichkeiten.

Beispiel für die WAHL-Funktion

Referenz der Matrix und Bereichsfunktionen 

ADRESSE()

Syntax: ADRESSE(Zeile;Spalte;Abs;A1;Tabellenname)

Beispiel: ADRESSE(2;5;4;FALSCH)

Ergebnis: Z(2)S(5)

Liefert die Adresse der mit Zeile und Spalte angegebenen Zelle.

Abs bestimmt den Bezugstyp: 1 oder keine Angabe (absoluter Bezug); 2 (absolute Zeile, relative Spalte); 3 (relative Zeile, absolute Spalte); 4 (relativer Bezug).

Das Argument A1 ist ein Wahrheitswert und bestimmt die Schreibweise des gewünschten Bezugs. Wenn A1 WAHR ist oder ausgelassen wird, werden die Bezüge in der A1-Schreibweise zurückgegeben. Wenn A1 FALSCH ist, werden die Bezüge in der Z1S1-Schreibweise zurückgegeben.

Tabellenname bestimmt den Namen der Tabelle oder der Makrovorlage, die als externer Bezug verwendet wird. Fehlt Tabellenname, wird keiner benutzt.

BEREICH.VERSCHIEBEN()

Syntax: BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;Höhe;Breite)

Beispiel: BEREICH.VERSCHIEBEN(A10:B15;0;1)

Ergebnis: B10:C15

Die Funktion liefert einen Bereichsbezug, der um eine mit Zeilen und Spalten festgelegte Zahl von Zeilen und Spalten gegenüber dem mit Bezug festgelegten Bereich verschoben ist. Als Ausgangspunkt dient die linke obere Eckzelle des Bereichs.

Bei positiven Werten werden Zeilen und Spalten nach unten bzw. nach rechts versetzt, bei negativen umgekehrt.

Höhe und Breite sind optionale Argumente und bezeichnen die Größe des neuen Bezugs als Anzahl der Zeilen und Spalten. Wenn Zeilen- und Spaltenanzahl mit dem ursprünglichen Bezug übereinstimmen, müssen für diese Argumente keine Werte eingetragen werden.

BEREICHE()

Syntax: BEREICHE(Bezug)

Beispiel: BEREICHE(C18:D18;E10:F12)

Ergebnis: 2

Die Funktion ermittelt die Anzahl der Bereiche (Zellen oder Matrizen), die in Bezug enthalten sind.

HYPERLINK()

Syntax: HYPERLINK(Hyperlink_Adresse;Freundlicher_Name)

Beispiel: HYPERLINK(http://www.databecker.de;"Data Becker")

Erstellt eine Verknüpfung zu der angegebenen Adresse. »Freundlicher Name« ist die Bezeichnung, die in der Zelle angezeigt wird.

INDEX()

Syntax1: INDEX(Bezug;Zeile;Spalte;Bereich)

Beispiel: INDEX(D18:F21;3;3)

Ergebnis: »Gesamt«, wenn Zelle F20 als Eintrag »Gesamt« enthält

Syntax2: INDEX(Matrix;Zeile;Spalte)

Beispiel: INDEX(B25:D28;2;2)

Ergebnis: C26 mit dem Inhalt der Zelle C26

Die Funktion INDEX liegt in zwei unterschiedlichen Ausprägungen vor. In der ersten Form dient sie der Ermittlung eines Bezugs aus Bereichen, in der zweiten der Abfrage einer Matrix.

Mit der Nummer von Zeile und Spalte wird der Ort bezeichnet, dessen Bezug ermittelt werden soll.

Bereich als optionales Argument wird dann verwendet, wenn Bezug eine Mehrfachauswahl enthält. Die einzelnen Bereiche werden in Bezug in Klammern gesetzt. Mit Bereich wird durch die Eingabe der entsprechenden Nummer auf diesen Bereich für die Abfrage verwiesen. Wird Bereich nicht angegeben, wird immer der erste Teilbereich genommen.

Das Ergebnis der Abfrage wird von Funktionen, die einen Bezug verlangen, als Bezug interpretiert. Funktionen, die einen Wert verlangen, interpretieren das Ergebnis als Wert.

2.  Mit der zweiten Form der Funktion lassen sich Werte aus einer Matrix abfragen. Das Ergebnis kann ein einzelner Wert sein oder wiederum eine Matrix.

Mit der Angabe von Zeile und Spalte wird der Ort der Matrix festgelegt, dessen Wert ermittelt werden soll. Wollen Sie nicht nur einen einzelnen Wert ermitteln, so lässt sich auch eine Matrix in Form einer Spalte oder einer Zeile auslesen. Wollen Sie eine Spalte auslesen, muss die Angabe für Zeile weggelassen werden, wollen Sie eine Zeile auslesen, gilt das analoge Verfahren. Allerdings muss in diesen Fällen die Funktion selber wie eine Array-Formel eingegeben werden. (Ausgabebereich markieren, Funktion eingeben, beenden mit + + ).

INDIREKT()

Syntax: INDIREKT(Bezug;A1)

Beispiel: INDIREKT(A2)

Ergebnis: 1994, wenn in A2 z. B. A128 eingetragen ist und in der Zelle A128 1994 steht.

Die Funktion ermittelt indirekt den Inhalt einer Zelle, auf die in einer anderen Zelle verwiesen wird. Mit A1 wird angegeben, wie der Eintrag in Bezug steht: in der A1-Schreibweise (WAHR oder weggelassen) oder in der Z1S1-Schreibweise (FALSCH)MTRANS().

MTRANS()

Syntax: MTRANS(Matrix)

Beispiel: siehe Abbildung

Die Funktion tauscht Zeilen und Spalten in einer Matrix (transponieren), die erste Zeile der alten Matrix wird die erste Spalte der neuen usw. Die Funktion muss als Matrixfunktion eingegeben werden (Ausgabebereich markieren, Funktion eingeben, mit + + abschließen).

Transponieren einer Matrix

Zwei Hinweise sind angebracht: Die transponierte Matrix ist natürlich abhängig von der Ursprungsmatrix, d.  h., sie macht alle Veränderungen der Ursprungsmatrix mit. In der transponierten Matrix sind keine Veränderungen einzelner Elemente möglich.

Geeignet ist diese Funktion besonders zur Ausgabe von Daten, die in Excel in einer Zeilen-Matrix geliefert werden.

PIVOTDATENZUORDNEN()

Syntax: PIVOTDATENZUORDNEN(Pivot-Tabelle;Name)

Beispiel: PIVOTDATENZUORDNEN(B10;"Umsatzauswertung")

Ergebnis: 10000, wenn das der Wert der Zelle B10 in der Pivot-Tabelle »Umsatzauswertung« ist.

Gibt einzelne Werte oder die Werte eines angebenen Bereichs aus einer Pivot-Tabelle zurück.

RTD()

Syntax: RTD(ProgID;Server;Thema1;Thema2;…)

Beispiel: RTD("MeinProgramm.ProgID";"MeinServer";"Preis")

Ergebnis: Daten aus dem Programm

Die Funktion empfängt Echtzeitdaten eines Add-Ins, das die COM-Automatisierung unterstützt.

SPALTE()

Syntax: SPALTE(Bezug)

Beispiel: {SPALTE(A38:C38)}

Ergebnis: {1  2  3}

Die Funktion liefert die Spaltennummer des mit Bezug angegebenen Bereichs. Wird Bezug nicht angegeben, ist das Ergebnis die Spaltennummer der Zelle, in der die Funktion steht.

Wird die Funktion als horizontale Matrix eingegeben und ist Bezug ein Bereich, dann werden die entsprechenden Spaltennummern ausgegeben.

SPALTEN()

Syntax: SPALTEN(Matrix)

Beispiel: SPALTEN(A47:C48)

Ergebnis: 3

Liefert die Anzahl der Spalten eines Bereichs oder einer Matrix.

SVERWEIS()

Syntax: SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

Beispiel: siehe Abbildung

Die Funktion ermittelt ausgehend von einer Zelle in einer Matrix den Inhalt der Zelle in derselben Zeile einer anderen Spalte.

Hierbei durchsucht die Funktion die erste Spalte (linksaußen) der mit Matrix angegebenen Matrix oder eines Bereichs nach Suchkriterium. Falls der angegebene Wert nicht gefunden werden kann, benutzt die Funktion den nächstkleineren Wert in der Spalte.

Von dieser Position aus wird die mit Spaltenindex angegebene Spalte aufgesucht (1 für die erste Spalte, also die Spalte in der der gesuchte Wert steht, 2 für die zweite). Wird für Spaltenindex eine Zahl größer als die Spaltenbreite der Matrix angegeben, liefert die Funktion den Fehlerwert #BEZUG.

Da die Funktion die erste Spalte nacheinander durchsucht, bis sie den Wert findet, der dem Suchkriterium entspricht, sollten zuvor die Werte in aufsteigender Ordnung sortiert werden.

Verweisfunktionen

VERGLEICH()

Syntax: VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp)

Beispiel: siehe Abbildung zu SVERWEIS()

Die Funktion durchsucht einen Bereich bzw. eine Matrix nach einem Suchkriterium und gibt die relative Position aus.

Das optionale Argument Vergleichstyp gibt an, auf welche Art nach dem Suchkriterium gesucht werden soll:

VERWEIS()

Syntax1: VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor)

Syntax2: VERWEIS(Suchkriterium;Matrix)

Beispiel: siehe Abbildung zu SVERWEIS()

Liefert auf der Grundlage eines Suchkriteriums den Inhalt einer korrespondierenden Zelle, vgl. SVERWEIS() und WVERWEIS().

Die Funktion liegt in zwei unterschiedlichen Formen vor. Bei der ersten werden zwei getrennte Bereiche (Spalten oder Zeilen) benutzt, bei der zweiten ein zusammenhängender Bereich.

Die Einträge in Suchvektor müssen in steigender Folge sortiert sein. Kann kein dem Suchkriterium genau entsprechender Wert gefunden werden, dann wird der nächstkleinere Wert übernommen.

2.  Für die zweite Form der Funktion wird die erste Zeile oder Spalte einer Matrix nach dem Suchkriterium durchsucht. Ob eine Zeile oder eine Spalte durchsucht wird, ist von der Dimensionierung der Matrix abhängig. Hat eine Matrix mehr Spalten als Zeilen oder ist ihre Anzahl gleich, so wird die erste Zeile durchsucht. Besitzt eine Matrix mehr Zeilen, so wird die erste Spalte durchsucht.

Ist die Funktion auf der Suche nach dem Kriterium beispielsweise in der ersten Spalte fündig geworden, dann geht sie in dieser Zeile nach rechts bis zur letzten Spalte und gibt den Wert der dortigen Zelle zurück. Analog wird bei der Suche in der ersten Zeile verfahren.

WAHL()

Syntax: WAHL(Index;Wert1;Wert2;...)

Beispiel: WAHL(3;orange;rot;gelb;grün;blau)

Ergebnis: gelb

Die Funktion liefert einen Wert aus einer Liste von Werten.

Mit Index wird festgelegt, der wievielte Wert als Ergebnis zurückgegeben werden soll. Da bis zu 29 verschiedene Werte eingetragen werden können, ist auch der Eintrag für Index auf 29 begrenzt.

WVERWEIS()

Syntax: WVERWEIS(Suchkriterium;Matrix;Zeilenindex;Bereich_Verweis)

Die Funktion entspricht exakt der Funktion SVERWEIS(); lediglich Zeilen und Spalten sind vertauscht.

ZEILE()

Syntax: ZEILE(Bezug)

Beispiel: ZEILE(Daten)

Ergebnis: 105, wenn als Bezug A105:D105 angegeben wurde

Die Funktion liefert die Zeilennummer des unter Bezug angegebenen Bezugs. Wird für Bezug keine Angabe gemacht, wird als Ergebnis die Zeilennummer der Zelle ausgegeben, in der die Funktion steht.

Wird die Funktion als vertikale Matrix eingegeben und ist Bezug ein Bereich, dann werden die entsprechenden Zeilennummern ausgegeben.

ZEILEN()

Syntax: ZEILEN(Matrix)

Beispiel: ZEILEN(Inhalt)

Ergebnis: 4, wenn der Bereich Inhalt 4-zeilig ist

Liefert die Anzahl der Zeilen eines Bereichs oder einer Matrix.

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.