Helmut Vonhoegen
www.helmut-vonhoegen.de
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.
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.
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.
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.
Syntax: EINDEUTIG(Matrix; Nach_Spalte; Genau_einmal)
Die Funktion ermittelt die eindeutigen oder die einmaligen Werte in einem Zellbereich oder einer Matrix und gibt sie in Form einer dynamischen Matrix aus. Mit Matrix wird der Datenbereich angegeben, den die Funktion auswerten soll. Nach_Spalte ist ein Wahrheitswert. FALSCH oder ausgelassen vergleicht die Elemente zeilenweise, WAHR dagegen spaltenweise. Genau_einmal ist ebenfalls ein Wahrheitswert. WAHR gibt genau die Elemente zurück, die nur einmal vorkommen, FALSCH gibt alle eindeutigen Elemente zurück.
Syntax: FELDWERT(Wert; Feldname)
Die Funktion gibt den Wert des mit Feldname bezeichneten Feldes für den ausgewählten Wert eines verknüpften Datentyps aus. Existiert der angegebene Wert oder der Feldname nicht, liefert die Funktion den Fehler #FELD!.
Syntax: FILTER(Matrix; Einschließen; Wenn_leer)
Die Funktion gibt den Wert des mit Feldname bezeichneten Feldes für den ausgewählten Wert eines verknüpften Datentyps aus. Existiert der angegebene Wert oder der Feldname nicht, liefert die Funktion den Fehler #FELD!.
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.
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.
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] ).
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).
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).
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.
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.
Syntax: SORTIEREN(Matrix; Sortierindex; Sortierreihenfolge; Nach_Spalte)
Die Funktion sortiert die Daten aus dem mit Matrix angegebenen Bereich oder der Matrix. Sortierindex gibt die Nummer der Zeile oder Spalte an, nach der sortiert werden soll. Das Argument Nach_Spalte ist ein Wahrheitswert. WAHR sorgt für eine spaltenweise Sortierung, FALSCH für eine zeilenweise. Das Sortierergebnis wird in einer dynamischen Matrix ausgegeben, die ab der Zelle beginnt, die die Formel enthält. Der Bereich muss leer sein, sonst erscheint der Fehlerwert #ÜBERLAUF!.
Syntax: SORTIERENNACH(Matrix; Nach_Matrix1; Sortierreihenfolge1; Nach_Matrix2; Sortierreihenfolge2;…)
Die Funktion erlaubt die Sortierung von Daten aus dem mit Matrix angegebenen Bereich anhand von Schlüsseln, die mit den Argumenten Nach_Matrix1, Nach_Matrix2 … angegeben werden, wobei für jeden Schlüssel mit Sortierreihenfolge(n) eigene Reihenfolgen angegeben werden können.
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.
Syntax: SPALTEN(Matrix)
Liefert die Anzahl der Spalten eines Bereichs oder einer Matrix.
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).
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
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.
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.
Syntax: WVERWEIS(Suchkriterium; Matrix; Zeilenindex;
Bereich_Verweis)
Die Funktion entspricht exakt der Funktion SVERWEIS(); lediglich Zeilen und Spalten sind vertauscht.
Syntax: XVERGLEICH(Suchkriterium; Suchmatrix; Vergleichsmodus; Suchmodus)
Die Funktion durchsucht eine Suchmatrix bzw. einen Bereich nach einem Suchkriterium und gibt die relative Position aus. Bei Zeichenfolgen wird die Groß- und Kleinschreibung ignoriert. Das optionale Argument Vergleichsmodus gibt an, auf welche Art nach dem Suchkriterium gesucht werden soll. Das optionale Argument Suchmodus gibt an, auf welche Art gesucht werden soll.
Syntax: XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; Wenn_nicht_gefunden; Vergleichsmodus; Suchmodus)
Die Funktion durchsucht in dem mit Suchmatrix angegebenen Bereich nach dem mit Suchkriterium angegebenen Wert. Dabei wird die Groß- und Kleinschreibung ignoriert, wenn es sich um Zeichenfolgen handelt. Von der gefundenen Position in der Suchmatrix aus wird innerhalb derselben Zeile die mit Rückgabematrix angegebene Spalte aufgesucht; der dort gefundene Wert liefert das Ergebnis der Funktion.
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.
Syntax: ZEILEN(Matrix)
Liefert die Anzahl der Zeilen eines Bereichs oder einer Matrix.