Archiv des LibreOffice- und OpenOffice.org-Wiki

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

Da Calc-Tabellen sehr oft auch als Datentabellen verwendet werden, mit datenbankähnlicher Struktur, entsteht häufig der Wunsch, diese Tabellen auf verschiedene Arten auszuwerten. Die Funktions-Kategorie Datenbank bietet dazu einige Anwendungen. Weitere Möglichkeiten sind StandardFilter und SpezialFilter oder auch Matrix- und Tabellenfunktionen

1. Struktur der Datenbank-Funktionen

Editieren

(./) OOo2

Datenbank-Funktionen dienen dazu, Werte aus einer Tabelle mit Datenbank-Struktur abzurufen und/oder spaltenweise Berechnungen mit ausgewählten Datensätzen anzustellen. Der Aufbau der Filterkriterien ist dabei der gleiche wie bei SpezialFiltern

1.1. Bezugsgrössen von DB-Funktionen

Alle DB-Funktionen bestehen aus drei Bereichen:

1.
Einer Ausgangstabelle, die wie eine Datenbank aufgebaut ist, d.h., jede Spalte hat einen eindeutigen Spaltentitel

A

B

C

D

E

F

G

H

1

Kunden-Nr.

Anrede

Name

Strasse

PLZ

Ort

Geb.Datum

Geb.Jahr

2

1002

Herr

Daniel Huber

Seeweg 12

17032

München

01.02.61

1961

3

1003

Herr

Heinz Schmidt

Bachstr. 11

86473

Stuttgart

14.03.78

1978

4

1004

Herr

Viktor Keller

Brühlstr. 3

60572

Bremen

17.09.81

1981

5

1005

Frau

Barbara Müller

Erikastr. 39

32568

Hanover

12.06.76

1976

2.
Einem Kriterienbereich, in dem die Auswahlkriterien hinterlegt werden

A

B

C

D

E

F

G

H

1

Kunden-Nr.

Anrede

Name

Strasse

PLZ

Ort

Geb.Datum

Geb.Jahr

2

München

3.
Der (den) Ausgabezelle(n) für das Ergebnis.

A

B

C

D

E

F

G

H

1

Anzahl gefundener Datensätze

2

1

=DBANZAHL2(A1:H5;0;A10:H11)

1.2. Tabellenorganisation, Bereiche benennen

Sowohl die Ausgangstabelle wie auch der Kriterienbereich müssen als Zellbereich in den Funktionen genannt werden, also z.B. so: $Tabelle1.$A$1:$H$3000.

Leichter tut man sich, wenn man diesen Bereichen einen einen Namen zuordnet. Erstens fällt das Schreiben und Lesen der Funktionen leichter, zweitens handelt es sich bei einem Zellbereichsnamen automatisch um eine AbsoluteAdresse und man erlebt keine Überraschungen beim Kopieren und drittens kann man beim Ändern der Ausdehnung eines Bereichs einfach den Adressbereich im Namen ändern und schon verwenden alle Formeln, die sich auf diesen Bereich beziehen, wieder die richtige Adresse.

Vorschlag für die Tabellenorganisation:

  • Eine Tabelle für die Datenbank. Diese ganz markieren und einen Namen zuweisen. Es spielt keine Rolle, wenn mehr Zeilen und Spalten markiert sind, als tatsächlich benutzt werden, das ist sogar von Vorteil bei Tabellen, deren Anzahl Zeilen und/oder Spalten ändern kann.

  • Eine Tabelle, in der zuoberst einige Zeilen für die Kriterien reserviert werden. Auch diesem Bereich einen Namen geben und vorsichtshalber die ganzen Zeilen in den Bereich aufnehmen. Darunter dann die Zellen für die DB-Funktionen.
    So sieht man auf einen Blick, auf welchen Kriterien die Ergebnisse beruhen.
    Wenn für die Filterkriterien ODER-Wertbereiche genannt werden sollen, so benötigt man pro ODER-Bedingung eine Zeile im Kriterienbereich. Mit 5, 6 Zeilen kommt man aber normalerweise aus.

1.3. Der Kriterienbereich

Der Kriterienbereich besteht aus mindestens zwei Feldern. Im oberen Feld der Spaltenname aus der Datentabelle, die geprüft werden soll, darunter das Kriterium (man kann aber auch alle Spaltentitel im Kriterienbereich auflisten).

1.3.1. Gesucht oder ausgeschlossen: ein exakter Wert

Datensätze mit dem Ort München

A

  

Datensätze ohne den Ort München

A

1

Ort

1

Ort

2

München

2

<> München

1.3.2. Gesucht: grösser als/kleiner als

Datensätze mit Postleitzahlen über 80000

A

  

Datensätze mit Postleitzahlen unter 80000

A

1

PLZ

1

PLZ

2

80000

2

80000

1.3.3. Ungenaue Suche: regulärer Ausdruck

Datensätze mit "Huber" im Namen, wobei sowohl Huber gefunden werden soll, als auch Daniel Huber, als auch C. Huber-Schmitt

A

Eine Liste der regulären Ausdrücke 1 findet sich im Hilfetext.

1

Name

2

.*Huber.*

1.3.4. UND-Bedingungen

Weniger Datensätze (alle Bedingungen müssen erfüllt sein, damit ein Datensatz ausgewählt wird)

UND-Bedingungen müssen in einer Zeile stehen. Es kann sich um Bedingungen in unterschiedlichen Feldern handeln, aber es kann auch ein Feld z.B. auf mehrere Werte geprüft werden

A

B

   

A

B

C

1

Ort

Geb.Jahr

PLZ

PLZ

PLZ

2

München

1970

10000

20000

<> 15000

1.3.5. ODER-Bedingungen

Mehr Datensätze (nur eine Bedingung muss erfüllt sein, damit ein Datensatz ausgewählt wird)

ODER-Bedingungen werden untereinander geschrieben.

A

1

Ort

2

München

3

Stuttgart


2. Datensätze zählen, Spaltenberechnungen

Editieren

(./) OOo2.0

Mit den Funktionen DBANZAHL(), DBANZAHL2(), DBSUMME() ermittelt man die Anzahl ausgewählter Datensätze bzw. die Summe einer bestimmten Spalte dieser Datensätze. Laut Hilfetext kann DBANZAHL() nur verwendet werden, wenn sich alle Auswahlkriterien auf Spalten mit numerischem Inhalt beziehen. Andernfalls ist DBANZAHL2() anzuwenden. Dies scheint aber nicht kategorisch zu gelten.


2.1. Ausgewählte Datensätze: Zeilen zählen, Spalten summieren

A

B

C

D

E

F

1

Marke

Modell

Bildschirm

Anzahl

Preis

Umsatz

2

Toshiba

Toshiba Tecra A5-148, Pentium-M 750 (1.86GHz), 14" =WXGA

14

4

1603

6412

3

Lenovo

Lenovo Z60t 2511-FFG Pentium M 760, 14" WXGA Olympic Edition

14

1

2254

2254

4

Fujitsu Siemens

Fujitsu Siemens S7020 Supreme, Pentium M 750, 14" SXGA+

14

1

2099

2099

5

Fujitsu Siemens

Fujitsu Siemens S7020 Supreme, Pentium M 750, 14" SXGA+

14

1

2111

2111

6

Toshiba

Toshiba Satellite M60-163, Pentium M 740 (1.73GHz), 17 WXGA+

17

2

2162

4324

7

Asus

Asus W3V-H004P, Pentium-M 750 (1.86GHz), 14" WXGA

14

1

2287

2287

8

Asus

Asus A7VB-R002H, Pentium M 750, 17" WXGA+ Glare Type

17

2

2436

4872

9

Samsung

Samsung X1, Pentium-M 753 (1.2GHz), 14" WXGA, Glossy

14

1

2681

2681

10

Asus

Asus W2VC-U003P, Pentium-M 760 2.0GHz, 17" WSXGA+ Glare Type

17

2

3312

6624

11

Samsung

Samsung M50, Pentium-M 770 (2.13GHz), 17" WXGA+

17

3

3386

10158

12

Toshiba

Toshiba Qosmio G20-155, Pentium-M 760 2.0GHz 17" TFT WXGA+

17

4

3534

14236

13

Fujitsu Siemens

Fujitsu Siemens AMILO M3438G, Pentium M 750, 17" WXGA+

17

8

2050

16400

14

Lenovo

Lenovo Z60t 2511-EJG, Pentium M 750, 14" WXGA, Deutsch

14

1

2137

2137

15

Sony

Sony VAIO VGN-A517B, Pentium-M 740 (1.73GHz), 17" WXGA+

17

6

2264

13584

16

17

Modelle pro Marke

18

Marke

Spaltentitel des Kriterienbereichs

19

Toshiba

Prüfwert für die Bedingung

20

3

=DBANZAHL($A$1:$F$15;0;A18:A19)

21

Marke

Spaltentitel des Kriterienbereichs

22

Samsung

Prüfwert für die Bedingung

23

2

=DBANZAHL2($A$1:$F$15;0;A21:A22)

24

Umsatz pro Bildschirmtyp

25

Bildschirm

Spaltentitel des Kriterienbereichs

26

14

Prüfwert für die Bedingung

27

19981

=DBSUMME($A$1:$F$15;6;A25:A26)

28

Bildschirm

Spaltentitel des Kriterienbereichs

29

17

Prüfwert für die Bedingung

30

70098

=DBSUMME($A$1:$F$15;6;A28:A29)

2.2. Siehe auch

Weitere Möglichkeiten zum Zählen und Summieren von ausgewählten Tabellenzeilen bieten Matrixformeln

3. Minimum, Maximum, Mittelwert, DBAUSZUG

Editieren

(./) OOo2.0

Mit den Funktionen DBMIN(), DBMAX(), DBMITTELWERT(), DBAUSZUG() greift man gezielt auf einzelne Spaltenwerte zu.


3.1. Grenzwerte finden, Bezüge herstellen

DBMIN(), DBMAX() und DBMITTELWERT() tun das, was man vom Namen her erwarten darf: sie nennen den kleinsten, grössten oder durchschnittlichen Wert einer Spalte.

Der Funktionsname DBAUSZUG() lässt vielleicht vermuten, dass man damit einen Filter setzen kann, der dann evtl. mehrere Datensätze als Ergebnis liefert. Das ist so nicht richtig. Eine DB-Funktion ist keine Matrix-Funktion, sie liefert immer nur genau einen Wert in genau eine Ergebniszelle.
Der Zweck von DBAUSZUG() ist der: es wird ein Datensatz gesucht, der den Bedingungen des Filterkriterienbereichs entspricht. Zurückgegeben wird der Inhalt einer wählbaren Spalte des gefundenen Datensatzes. Und genau hier liegt das Dilemma dieser Funktion: wenn mehrere Datensätze den Suchkriterien entsprechen, kann kein eindeutiger Bezug hergestellt werden. Angenommen, man sucht in einer Adresstabelle nach einem Geburtsdatum und möchte den zugehörigen Personennamen anzeigen. Wenn nun mehrere Personen am gleichen Tag Geburtstag haben, läuft das auf eine Fehlermeldung hinaus.

DBAUSZUG() kennt zwei Fehlersituationen und liefert dafür unterschiedliche Rückgabewerte: Wenn der Suchwert nicht gefunden wird, wird als Ergebnis #WERT angezeigt, das entspricht dem Fehlercode 519. Gibt es zu den Suchkriterien mehrere Ergebnisse, so lautet der Rückgabewert Err.502. Bei Bedarf kann man den genauen Fehlercode mit der Funktion FEHLERTYP() abfragen und in eine WENN()-Formel einbauen.

Warum hier die Funktionen DBMIN(), DBMAX(), DBAUSZUG() im gleichen Kapitel zusammengefasst sind, hat einen einfachen Grund: es geht darum, zu zeigen, wie man zuerst das Minimum oder Maximum einer Spalte ermittelt (im Beispiel ist das die Preisspalte) und dann zu dem gefundenen Minimum/Maximum den Inhalt einer anderen Zelle (im Beispiel den Artikeltext) liefert. Das macht so nur Sinn, wenn jeder Preis nur einmal vorkommt (siehe vorigen Absatz). Wenn diese Voraussetzung nicht gegeben ist, muss man wohl eher auf Filter zurückgreifen. Für den speziellen Fall, dass es genügt, bei mehreren Fund-Datensätzen den Artikeltext des ersten gefundenen Artikels zu nennen, wird weiter unten ein Lösungsansatz genannt.

3.1.1. Den grössten, kleinsten und mittleren Wert einer Spalte ermitteln

A

B

C

D

1

Marke

Modell

Zollmass

Preis

2

Sony

Sony SDM-S95AR, Monitor 19", Schwarz

19

479.00

3

Sony

Sony SDM-S95AR, Monitor 19", Silber

19

528.00

4

Samtron

Samtron 94V, 19 Zoll, silber

19

380.00

5

BenQ

BenQ FP93GX, 19 Zoll Monitor, 2ms

19

556.00

6

BenQ

BenQ FP91G+, 19 Zoll, 8ms

19

397.00

7

Eizo

Eizo S1910-AS, 19 Zoll LCD, Grau

19

790.00

8

Viewsonic

Viewsonic VX924, 19" LCD, 3ms, Testsieger!

19

539.00

9

HP

HP L1940 19 Zoll LCD, Carbon Silber, Pivot, DVI

19

630.00

10

HP

HP L1955 19 Zoll LCD, Silber, Pivot, DVI

19

575.00

11

12

Was kostet das teuerste Gerät von Sony?

13

Marke

Modell

Zollmass

Preis

14

Sony

15

528.00

=DBMAX($A$1:$D$10;"Preis";A13:D14)

16

17

Was kostet das billigste 19"-Gerät?

18

Marke

Modell

Zollmass

Preis

19

19

20

380.00

=DBMIN($A$1:$D$10;"Preis";A18:D19)

21

Welches sind die Durchschnittspreise der verschiedenen Marken?

22

Marke

Marke

Marke

23

Sony

BenQ

HP

24

503.50

476.50

602.50

25

=DBMITTELWERT($A$1:$D$10; "Preis";A23:A24)

=DBMITTELWERT($A$1:$D$10; "Preis";B23:B24)

=DBMITTELWERT($A$1:$D$10; "Preis";C23:C24)

3.1.2. Zum kleinsten Wert einer Spalte den zugehörigen Namen ausgeben

Im folgenden Beispiel wird mit zwei Kriterienbereichen gearbeitet. Der Bereich A13:A14 dient der Funktion DBMIN() in Zelle B14 dazu, den günstigsten Preis zu finden. B14 ist aber gleichzeitig das Kriterium für die Funktion DBAUSZUG() in Zelle A15.

A

B

C

D

1

Marke

Modell

Zollmass

Preis

2

Sony

Sony SDM-S95AR, Monitor 19", Schwarz

19

479.00

3

Sony

Sony SDM-S95AR, Monitor 19", Silber

19

528.00

4

Samtron

Samtron 94V, 19 Zoll, silber

19

380.00

5

BenQ

BenQ FP93GX, 19 Zoll Monitor, 2ms

19

556.00

6

BenQ

BenQ FP91G+, 19 Zoll, 8ms

19

397.00

7

Eizo

Eizo S1910-AS, 19 Zoll LCD, Grau

19

790.00

8

Viewsonic

Viewsonic VX924, 19" LCD, 3ms, Testsieger!

19

539.00

9

HP

HP L1940 19 Zoll LCD, Carbon Silber, Pivot, DVI

19

630.00

10

HP

HP L1955 19 Zoll LCD, Silber, Pivot, DVI

19

575.00

11

12

Welches ist das günstigste Modell?

13

Zollmass

Preis

14

19

380.00

=DBMIN($A$1:$D$10;"Preis";A13:A14)

15

Samtron 94V, 19 Zoll, silber

=DBAUSZUG($A$1:$D$10;"Modell";B13:B14)

3.1.2.1. DBAUSZUG bei nicht eindeutigen Kriterien

Wie weiter oben beschrieben, liefert DBAUSZUG() nur dann ein Ergebnis, wenn die Suchkriterien genau einen Datensatz identifizieren. Wenn mehrere Datensätze zu den Suchkriterien passen, es aber genügt, den ersten oder letzten dieser Datensätze zu bestimmen, kann man sich mit einem kleinen Trick behelfen.

Im folgenden Beispiel gibt es zwei Artikel mit gleichem Preis. In die Datentabelle wurde eine Hilfsspalte eingefügt mit folgender Formel:
=D2+ZEILE()/1000
Damit wird der Preis um eine Nachkommastelle erweitert, die ihn wieder eindeutig macht. DBMAX() bezieht sich nun nicht auf die Spalte Preis, sondern auf Preis2 und liefert der Funktion DBAUSZUG() damit wieder einen eindeutigen Wert.

A

B

C

D

E

1

Marke

Modell

Zollmass

Preis

Preis2

2

Sony

Sony SDM-S95AR, Monitor 19", Schwarz

19

479.00

479.002

3

Sony

Sony SDM-S95AR, Monitor 19", Silber

19

528.00

528,003

4

Samtron

Samtron 94V, 19 Zoll, silber

19

380.00

380.004

5

BenQ

BenQ FP93GX, 19 Zoll Monitor, 2ms

19

556.00

556.005

6

BenQ

BenQ FP91G+, 19 Zoll, 8ms

19

397.00

397.006

7

Eizo

Eizo S1910-AS, 19 Zoll LCD, Grau

19

790.00

790.007

8

Viewsonic

Viewsonic VX924, 19" LCD, 3ms, Testsieger!

19

539.00

539.008

9

HP

HP L1940 19 Zoll LCD, Carbon Silber, Pivot, DVI

19

630.00

630.009

10

HP

HP L1955 19 Zoll LCD, Silber, Pivot, DVI

19

790.00

790.010

11

12

Welches ist das ungünstigste Modell?

13

Zollmass

Preis

14

19

790,010

=DBMAX($A$1:$E$10;"Preis2";A13:A14)

15

HP L1955 19 Zoll LCD, Silber, Pivot, DVI

=DBAUSZUG($A$1:$E$10;"Modell";B13:B14)

4. Siehe auch

CalcFunktionen


KategorieCalc

  1. Die Verwendung regulärer Ausdrücke muß unter Extras → Optionen → OpenOffice.org Calc → Berechnen erlaubt sein (1)


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