Archiv des LibreOffice- und OpenOffice.org-Wiki

[ÜberSicht] [TitelIndex] [WortIndex] [SeiteFinden

1. Daten aus Datentabellen auslesen

Editieren

Dieser Bereich der Tabellen-Funktionen befasst sich mit Datentabellen, aus denen auf verschiedene Weise Informationen abgerufen werden.

Diese Datentabellen – die auch nur aus einzelnen Zeilen oder Spalten bestehen können – werden im folgenden mehrheitlich Matrizen genannt, um den Unterschied zu Tabellen zu betonen. Eine Calc-Tabelle kann mehrere Matrizen enthalten.

2. Stolperfallen vermeiden

Ausnahmsweise fängt dieser Beitrag mit der Nennung von drei Stolperfallen an. Dies darum, weil diese Funktionen einen so vielseitigen Einsatzbereich haben, dass es sich lohnt, sich mit ihnen vertraut zu machen. Die erwähnten Fallen sind nämlich ganz einfach in den Griff zu bekommen:

  • Zellbereich
    In den VERWEIS-Funktionen müssen Zellbereiche genannt werden. Diese Bereiche sollten unbedingt absolut adressiert sein, da sich sonst beim Kopieren der Formeln die Bereichsadresse verändern würde. Die beste Variante ist, ihnen einen Namen zuzuweisen und in der Formel diesen Namen zu verwenden, damit ist die absolute Adressierung garantiert. Abgesehen davon, dass die Formeln dadurch auch leichter lesbar werden, ergeben sich noch weitere Vorteile:

    • Wenn der Matrix-Bereich später erweitert werden muss, wird einfach die Zellbereichsdefinition im Namen geändert (Einfügen -> Namen -> ändern) und damit sind bereits alle Formeln angepasst, welche diesen Namen verwenden.

    • Der Name steht im Navigator zur Verfügung und so kann der Zellbereich bei Bedarf direkt angesteuert und ausgewählt werden.

  • Funktions-Parameter
    Die Funktionen SVERWEIS und WVERWEIS umfassen 4 Parameter, wobei der vierte davon kein Pflichtparameter ist. Aber genau dieser Parameter verursacht die meisten Probleme. Als Faustregel sollte man sich merken: diesen Parameter standardmässig auf 0 setzen. Der FunktionsAutopilot hilft bei der Eingabe der Funktion. Steht der vierte Parameter nicht auf 0, so geht das Programm davon aus, dass die Daten aufsteigend sortiert sind. Das wäre ja an und für sich noch nicht unbedingt ein Problem. Aber als Folge dieser Annahme verhält sich die Funktion anders, wenn keine genaue Übereinstimmung gefunden wird. Bei einer "sortierten" Liste liefert die Funktion in dem Fall den nächstgelegenen Wert und keinen Fehlerwert. Dieses Verhalten ist bei der Arbeit mit Datensätzen meist nicht gewollt. Daher ist es meist sinnvoller, man definiert die Verweismatrix als unsortiert, auch wenn das nicht den Tatsachen entspricht.

  • Der Fehlerfall
    Wenn eine Verweis-Funktion den gesuchten Wert in der genannten Matrix nicht findet, liefert sie den Fehlerwert #NV zurück. Dies kann man in der Formel berücksichtigen, z.B. so:
    WENN(ISTNV(SVERWEIS(Suchkriterium;Matrix;Index;0)); "Wert in Matrix nicht vorhanden"; SVERWEIS(Suchkriterium;Matrix;Index;0))

3. Welche Funktion für welche Anwendung?

  • Wenn man einen Übergabewert hat, wie z.B. eine Kundennummer, mit dem eine Matrix durchsucht werden soll, um dazu passende Informationen (Name, Wohnort...) zurückzuerhalten, sind dafür die Funktionen VERWEIS SVERWEIS WVERWEIS geeignet

  • Wenn man die Position eines in einer (einzeiligen oder einspaltigen) Matrix gefundenen Wertes wissen will, ist dafür die Funktion VERGLEICH zuständig. Dabei bezieht Position sich auf die Matrix, nicht auf die Zeilen- oder Spaltennummer der Tabelle.

  • Wenn man auf eine Zelle zugreifen will, deren Position innerhalb der Matrix bekannt ist, ist das ein Fall für die Funktion INDEX. Diese tut innerhalb einer Matrix das Gleiche, wie die Formel =A2 innerhalb einer Tabelle:
    =A2 liefert den Inhalt der Zelle in Spalte A, Zeile 2
    =INDEX(A3:B10;4;2) liefert den Inhalt der zweiten Spalte der vierten Zeile wobei die Zählung für Spalte und Zeile am Anfangspunkt der Matrix beginnt, also in der Zelle A3.

4. Strukturierte Datentabellen: SVERWEIS, WVERWEIS

SVERWEIS und WVERWEIS funktionieren gleich, bei SVERWEIS geschieht die Suche in einer senkrecht angeordneten Tabelle, während WVERWEIS von einer waagerecht angeordneten Tabelle ausgeht.

4.1. Die Theorie

SVERWEIS-Tabellen sind angeordnet wie DatenBank-Tabellen. Die erste Spalte übernimmt dabei die Funktion des Schlüsselfeldes, über das man auf die rechts stehenden Informationen zugreift. Wenn man eindeutige Ergebnisse benötigt, muss die Tabelle so aufgebaut sein, dass jeder Eintrag in Spalte 1 nur genau einmal vorkommt.

Die Funktion durchsucht also jeweils die erste Spalte einer Matrix nach dem Suchbegriff, welcher ihr übergeben wird. Wird der Suchbegriff gefunden, so liefert die Funktion als Ergebnis den Inhalt einer bestimmten Spalte aus der gefundenen Zeile. Welche Spalte das sein soll, muss der Funktion ebenfalls mitgegeben werden, wobei 1 die erste Spalte der Matrix ist, also die "Schlüsselfeld-Spalte". 1 kann man benutzen, wenn man nur überprüfen will, ob der Eintrag überhaupt in der Matrix existiert.

Standardmäßig ist die Funktion nicht darauf ausgerichtet, eindeutige Werte zu finden. Sie geht davon aus, dass die Matrix, welche durchsucht werden soll, aufsteigend nach der ersten Spalte sortiert ist. Stößt sie beim Durchsuchen dieser ersten Spalte auf einen höheren Wert als den gesuchten, ohne den genauen Wert gefunden zu haben, so sucht sie nicht mehr weiter und liefert den nächstkleineren Wert zurück, da sie ja annimmt, die folgenden Werte können nur noch höher sein. Teilt man der Funktion jedoch im vierten Parameter mit, dass die Matrix nicht aufsteigend sortiert ist, so sucht sie entweder bis zum Auffinden des gesuchten Wertes oder sonst bis zum Ende der Matrix und liefert einen Fehlerwert zurück, wenn der exakte Wert nicht gefunden wird.

Wenn man also vermeiden will, dass man bei nicht vorhandenem exakten Suchergebnis einen Annäherungswert bekommt, muss man der Funktion jeweils den vierten Parameter 0 für unsortiert mitgeben, auch wenn eine aufsteigende Sortierung vorliegt.

Bei WVERWEIS ist es umgekehrt: Der Suchbegriff wird in der ersten Zeile der Matrix gesucht und als Ergebnis wird der Inhalt einer bestimmten Zeile der gefundenen Spalte zurückgegeben. Zeile 1 ist dabei die erste Zeile der Matrix. Für die Eindeutigkeit gilt das gleiche wie bei SVERWEIS: der Suchbegriff darf nur einmal in der Schlüsselfeld-Spalte vorkommen.

Damit werden auch die Grenzen beider Funktionen klar:
Eine SVERWEIS-Matrix kann maximal 256 Felder (Spalten) haben, dafür soviele Datensätze (Zeilen) wie Calc als Maximum für eine Tabelle zulässt.
WVERWEIS kann nur maximal 256 Datensätze (Spalten) verwalten, dafür aber pro Datensatz soviele Felder (Zeilen), wie die Tabelle zulässt.
Sehr grosse Tabellen sollten aber nicht auf diese Weise bearbeitet werden, dafür ist eine "richtige" DatenBank geeigneter.

4.2. Die Praxis anhand von Beispielen

4.2.1. Adressdaten zu einer Kundennummer finden

Eine Adresstabelle ist eine lebende Matrix. D.h., es können sowohl Datensätze wie auch Felder hinzukommen. Da wir für die Matrix-Lokalisierung eine AbsoluteAdresse benötigen, definieren wir den Matrix-Bereich von Anfang an über die gesamte Ausdehnung der Tabelle.

Da die Spalte mit den Kundennummern unsere Schlüsselfeld-Spalte sein soll, steht sie daher in Spalte A. Der Matrix-Bereich wäre also: $A$1:$IV$32000 (bzw. $A$1:$IV$64000 ab Version 2.0). Dafür legen wir einen Namen fest:

Die ganze Tabelle markieren, dann: Einfügen → Namen → Festlegen: Adressen. von nun an kann man den Namen Adressen benutzen statt den Zellbereich nennen zu müssen. Im nachstehenden Beispiel fängt die eigentliche Matrix erst in Zeile 3 mit dem ersten Datensatz an. Bei der Namensvergabe des Bereichs kann man also – wenn man ganz genau sein will – den Adressbereich korrigieren, so dass der Matrix-Bereich erst in Zelle $A$3 beginnt. Allerdings wird man kaum je nach einer Kundennummer Spalte 1 oder Kunden-Nr. suchen, insofern stören die zwei oberen Zeilen auch nicht.

Diese beiden oberen Zeilen sind für die SVERWEIS-Funktion vollkommen irrelevant. Sie wurden hier nur als Arbeitsmittel eingefügt. So kann es ja sein, dass die Spaltentitel auch für Filterungen o.ä benutzt werden und die Nennung der Matrix-Spalten-Nummer ist hilfreich für die Verwendung von SVERWEIS: Will man auf die Spalte mit den Postleitzahlen zugreifen, so muss man wissen, dass das die Spalte Nr. 6 der Adress-Matrix ist.

Die Spalte Kurzbezeichnung füllen wir mit Hilfe von Text-Funktionen aus: =VERKETTEN(TEIL(C3;1;1);". ";D3;", ";G3)

A

B

C

D

E

F

G

H

1

Spalte 1

Spalte 2

Spalte 3

Spalte 4

Spalte 5

Spalte 6

Spalte 7

Spalte 8

2

Kunden-Nr.

Anrede

Vorname

Nachname

Strasse

PLZ

Ort

Kurzbezeichnung

3

1002

Herr

Daniel

Huber

Seeweg 12

17032

München

D. Huber, München

4

1003

Herr

Heinz

Schmidt

Bachstr. 11

86473

Stuttgart

H. Schmidt, Stuttgart

5

1004

Herr

Viktor

Keller

Brühlstr. 3

60572

Bremen

V. Keller, Bremen

6

1005

Frau

Barbara

Müller

Erikastr. 39

32568

Hanover

B. Müller, Hannover

Um nun zur Kundennummer 1004 den passenden Nachnamen zu finden, muss die Funktion SVERWEIS mit diesen Parametern aufgerufen werden:
=SVERWEIS(1004;Adressen;4)
Handelt es sich beim Feld Kunden-Nr. um ein alfanumerisches Feld, müsste die Funktion so erfasst werden:
=SVERWEIS("1004";Adressen;4)

4.2.2. Das Beispiel weiter ausgebaut zur Rechnungsbearbeitung

Das folgende Beispiel Rechnung ist vielleicht nicht besonders bearbeitungsfreundlich, wenn wir an eine wirkliche Rechnungsdaten-Erfassung denken, aber es soll ja auch nur die Funktionsweise von SVERWEIS verdeutlichen.

4.2.2.1. Artikelstamm

Die zweite "Stammdatentabelle," die wir benötigen ist ein Artikelverzeichnis, in unserem Fall eine Bücherliste. Auch diese Matrix bekommt einen Namen: Artikel.

A

B

C

1

Spalte 1

Spalte 2

Spalte 3

2

Artikel-Nr.

Artikelbezeichnung

Preis

3

001

Heinrich Böll, Die verlorene Ehre der Katarina Blum

12,80

4

002

Heinrich Böll, Ansichten eines Clowns

9,20

5

003

Fontane, Kleist und Hölderlin

34,20

6

004

Gedichte von Gottfired Benn

18,20

4.2.2.2. Rechnungs-Kopfdaten

Die Rechnungsdaten teilen wir auf zwei Tabellen auf, wobei eine die Matrix Rechnungen (Namen zuweisen!) enthält. Hier gibt es pro Rechnung nur eine Zeile. Unsere Schlüsselfeld-Spalte ist die Rechnungsnummer. In der zweiten Spalte soll das Rechnungsdatum eingetragen werden, in der dritten Spalte die Kundenummer.

In die Spalte 4 holen wir uns mit SVERWEIS die Kurzbezeichnung für den Kunden, da diese Information für die Bearbeitung der Rechnungs-Kopfdaten-Tabelle genügt. Wie die Spalte Rechnungstotal zustande kommt, wird beim Erstellen der Positionsdaten-Matrix erklärt, da das Rechnungstotal ja aus den Positionen berechnet werden soll.

A

B

C

D

E

F

G

1

Spalte 1

Spalte 2

Spalte 3

Spalte 4

Spalte 5

Spalte 6

Spalte 7

2

RE-Nr.

RE-Datum

Kunden-Nr.

Kurz-Bezeichnung

RE-Betrag

Zahlungs-Ziel

Zahlungs-Eingang

3

000001

13.07.05

0004

V. Keller, Bremen

22,00

Formel in D3:

=SVERWEIS(C3;Adressen;8;0)

4.2.2.3. Rechnungs-Positionsdaten

Die Rechnungs-Positionsdaten-Matrix beginnt ebenfalls mit der Rechnungsnummer. In den Spalten 2 und 3 werden mittels SVERWEIS die Kundennummer und die Kurzbezeichnung aus der Rechnungs-Kopfdaten-Datei geholt. Die Anzahl und Artikelnummer werden in den Spalten 4 und 5 erfasst. Aufgrund der Artikelnummer in Spalte 5 werden aus der Artikeldatei die Artikel-Bezeichnung und der Artikelpreis geholt. Aus Anzahl und Stückpreis wird in Spalte 8 das Positionstotal ermittelt.

A

B

C

D

E

F

G

H

1

Spalte 1

Spalte 2

Spalte 3

Spalte 4

Spalte 5

Spalte 6

Spalte 7

Spalte 8

2

RE-Nr.

Kd.-Nr.

Kunde

Anz.

Art.-Nr.

Bezeichnung

Preis

Pos.-Total

3

000001

1004

V. Keller, Bremen

1

002

Heinrich Böll, Ansichten eines Clowns

9,20

9,20

Formel in B3:

=SVERWEIS(A3;Rechnungen;3;0)

Formel in C3:

=SVERWEIS(A3;Rechnungen;8;0)

Formel in F3:

=SVERWEIS($E3;Artikel;2;0)

Formel in G3:

=SVERWEIS($E3;Artikel;3;0)

Formel in H3:

=G3*D3

Nun fehlt noch das versprochene Rechnungstotal in der Rechnungs-Kopfdaten-Matrix. Dazu setzen wir eine "echte" Matrix-Funktion ein: SUMMENPRODUKT(). Zuvor gibt es aber wieder Zellbereiche zu benennen. Die Spalte Rechnungs-Nr. in der Rechnungspositionstabelle erhält den Namen ReNr. Der Adressbereich für diesen Matrix-Namen fängt in Zeile 3 an und kann bis zum Ende der Tabelle reichen ($A$3:$A$32000). Ebenfalls bekommt die Spalte Positions-Total einen Namen: PositionsTotal. Nun können wir in die Spalte Rechnungstotal der Rechnungs-Kopfdatendatei folgende Formel eintragen:
{=SUMMENPRODUKT(ReNR=A3;PositionsTotal)}

Die Klammern werden eingefügt, sobald man die Formel mit Shift+Ctrl+Return abschliesst.

4.2.2.4. Wenn daraus eine Rechnung gedruckt werden soll

... würde das den Rahmen dieses Beitrags sprengen. Wen es aber interessiert der kann sich hier: Rechnung.zip die Musterdateien herunterladen.

4.3. Mit SVERWEIS auf Zellbereiche anderer Dateien zugreifen

Zumindest seit der Version 2 von OpenOffice.org lässt sich die Funktion SVERWEIS() zur Matrix-Funktion erweitern. Der Parameter Matrix von SVERWEIS wird dazu mit Hilfe von DDE gebildet:

{=SVERWEIS(A1;DDE("soffice";"/home/ich/Beispiel.ods";"Tabelle1.A1:B10";0);2;0)}

sv1.png

Wenn man die Formel über die Eingabezeile eingibt, muß man sie mit SHIFT+CTRL+RETURN abschließen. Im Funktionsautopiloten kreuzt man stattdessen das Matrix-Optionsfeld an.

4.4. Von einander unabhängige Zellbereiche: VERWEIS

Für die Funktionen SVERWEIS/WVERWEIS ist eine eindeutige Tabellenstruktur Voraussetzung. Der Suchbegriff wird in der ersten Matrix-Spalte bzw. -Zeile gesucht und der Rückgabewert wird nur in einer Richtung gesucht: nach rechts auf der gleichen Zeile, bzw. nach unten in der gleichen Spalte. Umgekehrt (nach links bzw. nach oben) geht nicht.

Anders ist es bei der Funktion VERWEIS. Hier wird mit örtlich voneinander unabhängigen Matrizen für Suchbegriff und Ergebniswert gearbeitet. Da diese Matrizen aber nur jeweils aus einer Zeile oder einer Spalte bestehen, werden sie hier Vektoren genannt.

  A  

  B  

  C  

  D  

Im ersten Beispiel wird im Vektor $A$1:$A$5 nach der PLZ gesucht und anschliessend aus dem Vektor $D$1:$D$5 der zugehörige Ort zurückgegeben.

Im zweiten Beispiel ist die Suche genau umgekehrt: im rechten Vektor wird nach dem Ort gesucht und anschliessend aus dem linken Vektor die PLZ geliefert.

1

10115     

Berlin

2

80331     

München

3

70376     

Stuttgart

4

21029     

Hamburg

5

28205     

Bremen

6

7

Stuttgart

=VERWEIS(70376;$A$1:$A$5;$D$1:$D$5)

8

70376

=VERWEIS("Stuttgart";$D$1:$D$5;$A$1:$A$5)

Die beiden Vektoren können dabei vollkommen ortsunabhängig voneinander stehen: wenn der Suchbegriff in der dritten Zeile des Such-Vektors gefunden wird, gibt das Programm den Wert aus der dritten Zeile des Ergebnisvektors zurück:

  A  

  B  

  C  

  D  

  E  

  F  

1

10115

Stuttgart

=VERWEIS(70376;$A$1:$A$5;$C$3:$C$7)

2

80331

70376

=VERWEIS("Stuttgart";$C$3:$C$7;$A$1:$A$5)

3

70376

Berlin

4

21029

München

5

28205

Stuttgart

6

Hamburg

7

Bremen

Bei der Verwendung von VERWEIS sollte jedoch sichergestellt sein, dass der Suchbegriff auch tatsächlich vorhanden ist. Während man nämlich bei den SVERWEIS/WVERWEIS-Funktionen mit dem vierten Parameter eine Steuerungsmöglichkeit für den Fall nicht gefunden hat, fehlt diese Möglichkeit bei VERWEIS. Fehlt der Suchbegriff im Suchvektor, so liefert die Funktion einfach den Inhalt der ersten Zeile des Ergebnisvektors.

Wenn nicht sichergestellt werden kann, dass der Suchbegriff im Suchvektor existiert, muss man andere Funktionen zu Hilfe nehmen, wie z.B.SVERWEIS.

  A  

  B  

  C  

  D  

  E  

  F  

1

10115

Suchbegriff nicht gefunden

2

80331

Formel in E1:
=WENN(ISTNV(SVERWEIS(70000;A1:A5;1;0)); "Suchbegriff nicht gefunden"; VERWEIS(70000;A1:A5;C3:C7))

3

70376

Berlin

4

21029

München

5

28205

Stuttgart

Berlin

6

Hamburg

Formel in E5:
=VERWEIS(70000;A1:A5;C3:C7)

7

Bremen

5. INDEX, VERGLEICH – Zellen am Schnittpunkt von Koordinaten

Eine häufige Aufgabenstellung besteht darin, in einer Datentabelle einen Wert zu finden, dessen Position nicht als Zelladresse mitgegeben werden kann. Man benötigt in einem ersten Schritt eine Funktion, welche die Position einer Zelle innerhalb einer Matrix ermittelt und in einem zweiten Schritt den Inhalt dieser Zelle zurückgibt. Das ist ein Fall für das Geschwisterpaar INDEX und VERGLEICH.

A

B

C

D

E

F

1

Bevölkerung nach Staatsangehörigkeit

2

Land

Länder-KZ

Einheit

2002

2003

2004

3

Deutschland

D

1,000

75'188,7

75'189,9

75'212,9

4

Ausland

Ausland

1,000

7'348,0

7'341,8

7'288,0

5

Türkei

TR

1,000

1'912,2

1'877,7

1'764,3

6

Italien

I

1,000

609,8

601,3

548,2

7

Griechenland

GR

1,000

359,4

354,6

316,0

8

Polen

PL

1,000

317,7

326,9

292,1

9

Österreich

A

1,000

189,3

189,5

174,0

10

11

Gesucht sind

12

Herkunft:

I

13

Jahr:

2002

14

Das Jahr steht in Spalte:

4

=VERGLEICH(B13;A2:F2;0)

15

Das Land steht in Zeile:

5

=VERGLEICH(B12;B2:B9;0)

16

Am Kreuzpunkt demnach:

609,8

=INDEX(A2:F9;VERGLEICH(B12;B2:B9;0);VERGLEICH(B13;A2:F2;0))

6. Siehe auch


KategorieCalc

7. Flexible Zelladressen

Editieren

Um eine Zelle oder deren Inhalt anzusprechen, benutzt man normalerweise die konkrete Zelladresse, also etwa E13 bzw. $E$13. Nicht immer ist diese Adresse bekannt, bzw. sie hängt von bestimmten Faktoren ab.

Calc bietet zwei Funktionen an, mit denen man flexibel Zelladressen ansprechen kann: VERSCHIEBEN und INDIREKT. Meist benötigt man zusätzlich weitere Funktionen, um Zeilen-, Spalten-, Tabellen-Informationen abzurufen und Verkettungs-Funktionen, um Zelladressen zusammenzusetzen.

7.1. Das "Zellfenster" VERSCHIEBEN()

Mit der Funktion VERSCHIEBEN bezieht man sich auf eine Zelle, welche ausgehend von einer Bezugszelle um eine bestimmte Anzahl Zeilen und/oder Spalten vesetzt ist. Sowohl positive als auch negative Verschiebungswerte sind möglich.

7.1.1. Auf den Inhalt einer Zelle zugreifen, die sich in einem bestimmten Abstandsverhältnis zu einer Bezugszelle befindet

A

B

C

D

E

F

1

1

9

17

25

33

41

2

2

10

18

26

34

42

3

3

11

19

27

35

43

4

4

12

20

28

36

44

5

5

13

21

29

37

45

6

6

14

22

30

38

46

7

8

28

=VERSCHIEBUNG(A1;3;3)

9

17

=VERSCHIEBUNG(E6;-5;-2)

7.1.2. Ganze Zell-Bereiche bearbeiten, die in einem bestimmten Abstandsverhältnis zu finden sind

VERSCHIEBUNG hat noch zwei weitere Parameter, die im Zusammenenspiel mit anderen Funktionen interessant sind: Höhe und Breite. Damit bezieht sich die Verschiebung nicht nur auf eine einzelne Zelle, sondern auf einen Zellbereich, der am Verschiebepunkt beginnt und eine bestimmte Anzahl Zellen hoch und breit ist.

A

B

C

D

E

F

1

1

9

17

25

33

41

2

2

10

18

26

34

42

3

3

11

19

27

35

43

4

4

12

20

28

36

44

5

5

13

21

29

37

45

6

6

14

22

30

38

46

7

8

252

=SUMME(VERSCHIEBUNG(A1;2;2;3;3))

9

252

=SUMME(C3:E5)

10

1

=ZÄHLENWENN(VERSCHIEBUNG(A1;2;2;3;3);20))

11

1

=ZÄHLENWENN (C3:E5)

7.1.3. Dito, aber ausgehend von der aktuellen Zelle

Im vorigen Beispiel haben wir als Bezugspunkt eine konkrete Zelladresse (A1) mitgegeben. Stattdessen kann man natürlich auch diese Bezugs-Zelladresse mit weiteren Funktionen zusammensetzen.

Zum Beispiel liefert: =SUMME(VERSCHIEBUNG(INDIREKT(ADRESSE(ZEILE();SPALTE()));-7;3;3;3)) die Summe eines 3x3 Zellen grossen Bereichs, der 7 Zeilen oberhalb und 3 Spalten rechts der aktuellen Zelle beginnt.

7.2. INDIREKT() auf eine Zelle zugreifen

7.2.1. Auf den Inhalt einer Zelle zugreifen, deren Adresse als Text in einer bekannten Zelle steht

INDIREKT erwartet die Nennung einer Zelladresse, um deren Inhalt zurückzugeben. Allerdings ergibt die Schreibweise =INDIREKT("D1") wenig Sinn, da man ja genausogut =D1 schreiben kann. Der Sinn von INDIREKT erschließt sich erst, wenn die Zelladresse – im Beispiel D1 – nicht direkt übergeben wird, sondern zum Beispiel aus einer anderen Zelle gelesen oder mittels weiterer Funktionen zusammengesetzt wird.

Wenn also die Zelladresse – immer noch D1 aus unserem Beispiel – als Textstring z.B. in A1 steht, so würde man INDIREKT so formulieren:
=INDIREKT(A1)
Das Ergebnis ist dann der Wert aus Zelle D1.

A

B

C

D

1

D1

17

25

33

2

3

33

=INDIREKT(A1)

7.2.2. Eine Zelle in einem definierten Abstand von der aktuellen Zelle

Mit INDIREKT ist es auch möglich, eine Verschiebung zu definieren, die von der aktuellen Zelladresse ausgeht. Dazu muss zunächst die aktuelle Adresse ermittelt werden:

=ADRESSE(ZEILE();SPALTE())

Die Verschiebung wird nun definiert, indem die aktuelle ZEILE und die aktuelle SPALTE um einen Korrekturwert verändert wird:
SPALTE()+/-Korrekturwert
ZEILE()+/-Korrekturwert.

Die ganze Formel heißt dann z.B.:

=INDIREKT(ADRESSE(ZEILE()-3;SPALTE()+5))

und liefert den Inhalt derjenigen Zelle, die 3 Zeilen weiter oben und 5 Spalten weiter rechts als die aktuelle Zelle steht.

7.2.3. Eine zu verwendende Zelladresse mittels VERKETTEN() zusammensetzen

Auch mit Text-Funktionen kann eine Bezugsadresse für INDIREKT zusammengestellt werden.

7.2.3.1. Innerhalb einer Datei

=INDIREKT(VERKETTEN("A";"1"))

liefert den Inhalt von Zelle A1 der aktuellen Tabelle.

=INDIREKT(VERKETTEN("Tabelle3.";"A";"1"))

liefert den Inhalt von Zelle A1 der Tabelle3.

7.2.3.2. Datei-übergreifend

Auf den Inhalt einer Zelle aus einer anderen Datei kann man ebenfalls mit INDIREKT zugreifen:

=INDIREKT("'file:///c:/Verzeichnis/Datei.sxw'#$Tabelle1.A1")

bzw. sie zusammensetzen:

Datei: Versand.sxc

 

Datei: Adressen.sxc

A

B

 

A

B

C

1

Verzeichnis: 

C:/Verzeichnis/

 

1

101

Heinz

Karstr

2

Datei: 

Adressen.sxc

 

2

102

Fritz

Seeweg

3

Tabelle: 

Tab2

 

3

103

Anna

Brühlstr

4

Datei: 

Adressen.sxc

 

4

5

Zelle: 

C3

 

5

6

 

6

7

Brühlstr.

=INDIREKT(VERKETTEN("'file:///";B1;B2;"'#$";B3;".";B4))

 

7

7.2.3.3. Bereichs-Definition per Formel festlegen

Seit Version 2.0 von OpenOffice.org kann man auch einen Bereich per Formel zusammensetzen:

A

B

C

D

1

45

1

4

7

2

B1

2

5

8

3

D3

3

6

9

Die Formel in A1 summiert einen Bereich, dessen Koordinaten in den Zellen A2 und A3 zu finden sind:

=SUMME(INDIREKT(A2&":"&A3))

7.2.4. "Ganz absolute" Adressen

Wenn man AbsoluteAdressen in der Art $E$1 definiert, so bedeutet absolut die Bindung an den Zellinhalt, nicht an die Adresse. Wird nachträglich vor Spalte E eine Spalte eingefügt und der Inhalt von E1 rutscht dadurch nach F1, so werden alle Zellbezüge, die bisher auf $E$1 verwiesen, automatisch in $F$1 geändert. Im Normalfall ist das auch so gewünscht.

Es gibt jedoch Fälle, bei denen man das "Zellfenster" absolut fix haben möchte. Verschiebt sich eine Spalte, so soll der Inhalt derjenigen Zelle angezeigt werden, welche an die Stelle von – in unserem Beispiel E1 – rückt.

Die Formel

=INDIREKT("Tabelle1.E1")

bzw.

=INDIREKT(ADRESSE(1;5;4;"Tabelle1"))

liefert immer den jeweiligen Inhalt von Zelle E1 aus Tabelle1, egal wie viele Zeilen/Spalten vor E1 eingefügt oder gelöscht werden.

7.2.5. Excel®-Kompatibilität

Wenn mit INDIREKT() Zellen einer anderen Tabelle adressiert werden sollen, benötigt OpenOffice.org diese Schreibweise:

=INDIREKT("Tabellenname.A1")

...während in Excel® diese Schreibweise benutzt wird:

=INDIREKT("Tabellenname!A1")

Dies führt zu Kompatibilitätsproblemen, wenn Dateien zwischen diesen Programmen ausgetauscht werden. Wie das gelöst werden kann, erfahren Sie auf der Seite ExcelKompatibilität im Abschnitt Adressierungen.

7.3. Zell-Positions-Infos

=ZEILE()
Ermittelt die aktuelle Zeile
=SPALTE()
Ermittelt die aktuelle Spalte
=ADRESSE(ZEILE();SPALTE())
Ermittelt die aktuelle Adresse
=TABELLE()
Ermittelt die aktuelle Tabelle

7.3.1. Flexible Tabellen-Namen

Während man aus ZEILE() plus SPALTE() eine gültige Adresse generieren (z.B. =ADRESSE(ZEILE()+3;SPALTE())+2) und darüber mit INDIREKT auf den Zellinhalt zugreifen kann, ohne eine konkrete Zelladresse zu benutzen, ist es mit der Tabelle nicht so einfach. Die Funktion TABELLE() liefert lediglich die Position der Tabelle innerhalb der Datei, nicht aber den Namen. Den benötigt man aber, um z.B. auf den Inhalt einer Zelle zuzugreifen.

Den Namen der aktuellen Tabelle erhält man über die Funktion ZELLE(). Diese liefert je nach übergebenem Parameter verschiedene Informationen. Der Parameter FILENAME liefert den vollständigen Datei- und Tabellennamen:

=ZELLE("FILENAME")

liefert eine Ausgabe wie:

'file:///home/ich/verweis.ods'#$Tabelle1

Daraus kann man nun mit Textfunktionen den Tabellennamen herausfiltern:

=TEIL(ZELLE("FILENAME");FINDEN("$";ZELLE("FILENAME"))+1;LÄNGE(ZELLE("FILENAME")))

...und für die Verwendung mit INDIREKT() wieder zusammensetzen:

=INDIREKT(VERKETTEN(TEIL(ZELLE("FILENAME");FINDEN("$";ZELLE("FILENAME"))+1;LÄNGE(ZELLE("FILENAME")));".";"A1"))

7.4. Wie viele Zeilen, Spalten, Tabellen, Bereiche ?

Mit =ZEILEN(C4:H12) oder ZEILEN(Adressen) (wenn ein Name für einen Zellbereich vergeben wurde) wird die Anzahl Zeilen des Bereichs ermittelt.

Mit =SPALTEN(C4:H12) oder SPALTEN(Adressen) (wenn ein Name für einen Zellbereich vergeben wurde) wird die Anzahl Spalten des Bereichs ermittelt.

Mit TABELLEN() wird die Anzahl Tabellen des aktuellen Dokumentes ermittelt.

Auch eine Anzahl Bereiche kann ermittelt werden. Wobei ermittelt nicht der richtige Ausdruck ist. Die Bereiche müssen in der Funktion aufgelistet werden, entweder als Zellbezüge:
=BEREICHE(A1:A3;B1:B4;C1:E2;D3:F4), Ergebnis:4
oder als benannte Zellbereiche oder auch Datenbereiche:
=BEREICHE(Name1;Name2;Name3;Name4;Name5;Name6), Ergebnis: 6.

7.5. Siehe auch


KategorieCalc

8. siehe auch

CalcFunktionen


KategorieCalc


LizenzBedingungen | AnbieterKennzeichnung | DatenschutzErklärung | Stand: 2013-04-28