Kurzreferenz der Tabellenfunktionen in Excel bis zur Version 2016

Helmut Vonhoegen 

www.helmut-vonhoegen.de

Zurück zur Funktionsübersicht



Nachschlage- und Verweisfunktionen

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.

Zu den Funktionen, die für ganz unterschiedliche Situationen von großem Nutzen sind, gehören die Verweisfunktionen VERWEIS(), 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.

ADRESSE()

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

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)

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

Bei positiven Werten werden Zeilen und Spalten nach unten bzw. nach rechts versetzt, bei negativen Werten erfolgt die Verschiebung in die umgekehrte Richtung.

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)

Die Funktion ermittelt die Anzahl der Bereiche, die in dem mit dem Argument Bezug angegebenen Zellbereich enthalten sind. Soll Bezug in Form mehrerer Bereiche angegeben werden, müssen doppelte Klammern verwendet werden.

FORMELTEXT()

Syntax:   FORMELTEXT(Bezug)

Die in Excel 2013 neu eingeführte Funktion liefert die in der mit Bezug angegebenen Zelle enthaltene Formel als Zeichenfolge zurück, so wie sie sonst in der Bearbeitungsleiste angezeigt wird, wenn die Zelle ausgewählt ist.

HYPERLINK()

Syntax:   HYPERLINK(Hyperlink_Adresse; Freundlicher_Name)

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

INDEX()

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

Syntax2:         INDEX(Matrix; Zeile; Spalte)

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 ersten Form der Funktion lässt sich ein Bezug aus Bereichen durch Angabe der entsprechenden Zeile, Spalte und (bei Mehrfachauswahl) dem Bereich erfahren. 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.

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 [Strg]+[Umschalt]+[Eingabe] ).

INDIREKT()

Syntax:   INDIREKT(Bezug; A1)

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()

Syntax:   MTRANS(Matrix)

Die Funktion tauscht – transponiert – Zeilen und Spalten in einer Matrix. 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 [Strg]+[Umschalt]+[Eingabe] abschließen).

 

PIVOTDATENZUORDNEN()

Syntax:   PIVOTDATENZUORDNEN(Datenfeld; PivotTable;

                Feld1; Element1; Feld2; Element2; ...)

Die Funktion liefert Daten aus einer Pivot-Tabelle. Datenfeld gibt das Datenfeld an, dessen Daten abgerufen werden sollen, PivotTable ist ein Bezug auf eine Zelle, z. B. die linke obere Eckzelle, oder einen Bereich in der Pivot-Tabelle. Darüber wird ermittelt, welche Pivot-Tabelle die gewünschten Daten enthält. Zusätzlich lassen sich noch Paare von Feld- und Elementnamen angeben, um die gewünschten Daten näher zu spezifizieren. Bis zu 126 Feld-/Element-Paare sind möglich.

RTD()

Syntax:   RTD(ProgID; Server; Topic1; Topic2; ...)

Die Funktion empfängt Echtzeitdaten eines registrierten Add-Ins mit der angegebenen ProgID, das die COM-Automatisierung unterstützt. Server gibt an, auf welchem Server das Programm ausgeführt wird. Bei lokaler Ausführung kann dieser Name entfallen. Thema1 muss angegeben werden, um die gewünschten Daten zu identifizieren; weitere Themen - bis 253 - können angegeben werden.

SPALTE()

Syntax:   SPALTE(Bezug)

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)

Liefert die Anzahl der Spalten eines Bereichs oder einer Matrix.

SVERWEIS()

Syntax:   SVERWEIS(Suchkriterium; Matrix; Spaltenindex;

                Bereich_Verweis)

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 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).

VERGLEICH()

Syntax:   VERGLEICH(Suchkriterium; Suchmatrix; Vergleichstyp)

Die Funktion durchsucht einen mit Suchmatrix angegebenen 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)

Liefert auf der Grundlage eines Suchkriteriums den Inhalt einer korrespondierenden Zelle, vergleiche 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:

In der ersten Form benötigt die Funktion neben dem Suchkriterium einen Such- und einen Ergebnisvektor. Suchvektor ist eine Spalte oder eine Zeile, die nach dem mit Suchkriterium angegebenen Wert durchsucht werden soll. Von der Fundstelle aus wird der Wert, der die gleiche Position in Ergebnisvektor einnimmt, als Ergebnis von der Funktion ausgegeben. Such- und Ergebnisvektor sollten deshalb die gleiche Größe haben. Die Einträge in Suchvektor müssen in aufsteigender Folge sortiert sein. Wenn kein dem Suchkriterium genau entsprechender Wert gefunden werden kann, wird der nächstkleinere Wert übernommen.

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, 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; ...)

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 254 verschiedene Werte eingetragen werden können, ist auch der Eintrag für Index auf 254 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)

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)

Liefert die Anzahl der Zeilen eines Bereichs oder einer Matrix.