Archiv des LibreOffice- und OpenOffice.org-Wiki

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

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.

1. 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:

2. Welche Funktion für welche Anwendung?

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

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

3.2. Die Praxis anhand von Beispielen

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

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

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

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

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

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

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

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

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

5. Siehe auch


KategorieCalc


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