Datenbank Funktionen
Autor: Beate Schmitz - Erstellt: 2007-02 - Letzte Revision: ?Datenbankfunktionen
Datenbankfunktionen sind ein mächtiges und dabei schnelles Instrument, um große Datenbanken nach Kriterien auszuwerten. Schnell sind sie, weil sie keine Arrayformeln sind. Und sie lassen sich auf ganze Spalten anwenden. Sie haben den Nachteil, dass ein Kriterienbereich definiert werden muss. Es ist nicht möglich, die Kriterien in die Formel zu integrieren. Wenn Sie das wollen, können Sie auf folgende Alternativen zurückgreifen: Auswerten von Bereichen nach ODER-Kriterien
In diesem Beitrag möchte ich Ihnen insbesondere Hinweise zum geschickten Einsatz der Datenbankfunktionen geben, die über die Online-Hilfe hinausgehen. Unten gebe ich Ihnen zunächst ein einfaches Beispiel, damit sie die Funktionsweise verstehen. Vor Einsatz der Datenbankfunktionen sollten Sie deren Besonderheiten kennen.
Zum Kriterienbereich ist zu sagen, dass UND-Bedingungen in einer Zeile stehen, ODER-Bedingungen untereinander. Die Überschriften des Kriterienbereichs müssen den Spaltenüberschriften der auszuwertenden Spalten entsprechen. Die Formel muss bezüglich des Kriterienbereichs genau die passende Größe haben, es dürfen keine Leerzeilen enthalten sein. Soll sich der Kriterienbereich bei Ihnen in der Größe den aktuellen Bedingungen anpassen, müssen Sie den Kriterienbereich in der Formel dynamisieren. Die Anzahl der UND und ODER-Kriterien ist frei programmierbar; der Übersichtlichkeit halber bin ich in meinen Beispielen von nur zwei UND-Kriterien ausgegangen.
Hinweisen möchte ich auch auf eine mögliche Fehlerquelle, wenn Text als Bedingung verglichen wird: DB-Funktionen werten nach "enthält" aus. Sucht man z.B. nach "Wolf" wird auch "Wolff" mitgezählt. Überlegen Sie also, ob derartige Fehlerquellen in Ihrer Datei auftreten könnten. Das Ergebnis wird falsch sein. Hier ein Beispiel mit Auswegen.
Wenn es sich bei den Bedingungen um Werte handelt, können Sie auch die Operatoren >, <, <>, <= und >= im Kriterienbereich einsetzen. Ansonsten wird nach der genauen Übereinstimmung ausgewertet. Hier ein Beispiel. Auch Daten (da Excel sie als Werte versteht) können Sie mit Operatoren kombinieren.
Interessant kann der Einsatz von Datenbankfunktionen sein, wenn man eine sehr große Datenbank in einem separaten Tabellenblatt nach allen möglichen Kombinationen auswerten möchte, welches die gleichen Überschriften enhält wie die Datenbank selbst. Dann braucht man, wenn man die Formel leicht erweitert, nicht pro Kriterium einen separaten Kriterienbereich anzulegen, hier ein Beispiel.
Wie man eine Formel als Kriterium in Datenbankfunktionen benutzt, erfahren Sie unter excelformeln.de.
Sämtliche Namen der Datenbankfunktionen beginnen mit DB, woran man sie dann auch sofort erkennt.
Die Syntax der Datenbankfunktionen ist: DBFUNKTION(Datenbank;Feld;Suchkriterien), siehe Beispiel
| Diese Datenbankfunktionen stellt Excel uns zur Verfügung und in der Online-Hilfe (F1) erhalten Sie Erklärung dazu: | |
| DBANZAHL | Gibt die Anzahl von Zellen zurück, die in einer Datenbank Zahlen enthalten |
| DBANZAHL2 | Gibt die Anzahl nicht leerer Zellen in einer Datenbank zurück |
| DBAUSZUG | Extrahiert einen einzelnen Datensatz aus einer Datenbank, der dem angegebenen Kriterium entspricht |
| DBMAX | Gibt den maximalen Wert einer Reihe von ausgewählten Datenbankeinträgen zurück |
| DBMIN | Gibt den minimalen Wert einer Reihe von ausgewählten Datenbankeinträgen zurück |
| DBMITTELWERT | Gibt den Mittelwert ausgewählter Datenbankeinträge zurück |
| DBPRODUKT | Multipliziert die Werte in einem bestimmten Datensatzfeld, die mit dem Kriterium in einer Datenbank übereinstimmen |
| DBSTABW | Schätzt die Standardabweichung auf Basis einer Stichprobe ausgewählter Datenbankeinträge |
| DBSTABWN | Berechnet die Standardabweichung auf Basis einer Grundgesamtheit von ausgewählten Datenbankeinträgen |
| DBSUMME | Addiert die Zahlen aus der betreffenden Feldspalte derjenigen Datensätze in der Datenbank, die den angegebenen Kriterien entsprechen |
| DBVARIANZ | Schätzt die Varianz, ausgehend von einer Stichprobe aus bestimmten Datenbankeinträgen |
| DBVARIANZEN | Berechnet die Varianz, ausgehend von der Grundgesamtheit aus bestimmten Datenbankeinträgen |
Beipiele:
- Grundlagenbeispiele mit UND und ODER-Varianten
- Die Syntax an einem Beispiel
- Auswertung mit Operatoren bei Werten
- Kriterienbereich in der Formel dynamisieren
- Auswertung verschiedener Kriterien pro Zeile mit nur einem Kriterienbereich
- Probleme bei Textbedingungen - Auswertung über Funktion
- Ausflug zu excelformeln.de: Eine Formel als Kriterium in Datenbankfunktionen benutzen
Grundlagenbeispiel mit DBSUMME mit UND und ODER-Varianten
Wieviele Tonnen Bananen lagern in Köln?
| A | B | C | D | E | F | G | H | I | |
| 1 | |||||||||
| 2 | Obstsorte | Lager | Menge/to | Obstsorte | Lager | Menge/to | |||
| 3 | Bananen | Hamburg | 5 | Bananen | Köln | 12 | |||
| 4 | Birnen | Berlin | 7 | ||||||
| 5 | Äpfel | Köln | 4 | ||||||
| 6 | Bananen | Köln | 3 | ||||||
| 7 | Bananen | München | 3 | ||||||
| 8 | Äpfel | Berlin | 5 | ||||||
| 9 | Birnen | Köln | 6 | ||||||
| 10 | Äpfel | München | 8 | ||||||
| 11 | Bananen | Köln | 9 | ||||||
| 12 |
| Formeln der Tabelle | ||||
|
Wenn wir die Syntax DBFUNKTION(Datenbank;Feld;Suchkriterien) an obigem Beispiel aufsplitten ist:
- DBFUNKTION: DBSUMME
- Datenbank: B2:D11
- Feld: D2 (genau so gut könnte dort als Text stehen: "Menge/to" - also die Spaltenüberschrift der Spalte, die ausgewertet werden soll)
- Suchkriterien: F2:G3
Wieviele Tonnen Bananen und Birnen lagern in Köln und Berlin?
Geben Sie alle möglichen Kombiniationen in den Kriterienbereich ein und erweitern den Bereich in der Formel:
| A | B | C | D | E | F | G | H | I | |
| 1 | |||||||||
| 2 | Obstsorte | Lager | Menge/to | Obstsorte | Lager | Menge/to | |||
| 3 | Bananen | Hamburg | 5 | Bananen | Köln | 25 | |||
| 4 | Bananen | Berlin | 7 | Bananen | Berlin | ||||
| 5 | Äpfel | Köln | 4 | Birnen | Köln | ||||
| 6 | Bananen | Köln | 3 | Birnen | Berlin | ||||
| 7 | Bananen | München | 3 | ||||||
| 8 | Äpfel | Berlin | 5 | ||||||
| 9 | Birnen | Köln | 6 | ||||||
| 10 | Äpfel | München | 8 | ||||||
| 11 | Bananen | Köln | 9 | ||||||
| 12 |
| Formeln der Tabelle | ||||
|
Auswertung mit Operatoren bei Werten
Wie schon oben beschrieben, können Sie auch die Operatoren >, <, <>, <= und >= im Kriterienbereich einsetzen, wenn es sich um Werte handelt. Ansonsten wird nach der genauen Übereinstimmung ausgewertet. Auch Daten (da Excel sie als Werte versteht) können Sie mit Operatoren kombinieren. Natürlich können Sie auch mehrere verschiedene Operatoren für die UND- und ODER-Kriterien festlegen.
An wievielen Orten lagern mehr als 4 Tonnen Bananen?| A | B | C | D | E | F | G | H | I | |
| 1 | |||||||||
| 2 | Obstsorte | Lager | Menge/to | Obstsorte | Menge/to | Anzahl | |||
| 3 | Bananen | Hamburg | 5 | Bananen | >4 | 3 | |||
| 4 | Bananen | Berlin | 7 | ||||||
| 5 | Äpfel | Köln | 4 | ||||||
| 6 | Bananen | Köln | 3 | ||||||
| 7 | Bananen | München | 3 | ||||||
| 8 | Äpfel | Berlin | 5 | ||||||
| 9 | Birnen | Köln | 6 | ||||||
| 10 | Äpfel | München | 8 | ||||||
| 11 | Bananen | Köln | 9 | ||||||
| 12 |
| Formeln der Tabelle | ||||
|
Kriterienbereich in der Formel dynamisieren
Wenn der Kriterienbereich in der Formel zu weit gefasst ist und Leerzeilen enthält, ergeben sich falsche Resultate. Wenn Ihre ODER-Kriterien in der Anzahl variieren können, sollten Sie vorbeugen, indem Sie den Kriterienbereich in der Formel mit der Funktion Bereich.Verschieben dynamisieren:
| A | B | C | D | E | F | G | H | I | |
| 1 | |||||||||
| 2 | Obstsorte | Lager | Menge/to | Obstsorte | Lager | Menge/to | |||
| 3 | Bananen | Hamburg | 5 | Bananen | Köln | 25 | |||
| 4 | Bananen | Berlin | 7 | Bananen | Berlin | ||||
| 5 | Äpfel | Köln | 4 | Birnen | Köln | ||||
| 6 | Bananen | Köln | 3 | Birnen | Berlin | ||||
| 7 | Bananen | München | 3 | ||||||
| 8 | Äpfel | Berlin | 5 | ||||||
| 9 | Birnen | Köln | 6 | ||||||
| 10 | Äpfel | München | 8 | ||||||
| 11 | Bananen | Köln | 9 | ||||||
| 12 |
| Formeln der Tabelle | ||||
|
Hier werden also so viele ODER-Kriterien berücksichtigt, wie Zeilen in Spalte F ausgefüllt sind. Weiterhin dürfen zwischen den verschiedenen ODER-Kriterien keine Leerzeilen vorkommen, sonst greift der Bereich zu kurz!
Auswertung verschiedener Kriterien pro Zeile mit nur einem Kriterienbereich
Sie haben eine umfangreiche Tabelle und möchten jedwede mögliche Kombination auswerten. In diesem Fall die Frage: Welche Tonnage welcher Obstsorte lagert wo? Zunächst sehen Sie hier die Ausgangstabelle:
Tabelle8| A | B | C | D | E | |
| 1 | |||||
| 2 | Obstsorte | Lager | Menge/to | ||
| 3 | Bananen | Hamburg | 6 | ||
| 4 | Bananen | Berlin | 8 | ||
| 5 | Äpfel | Köln | 5 | ||
| 6 | Bananen | Köln | 8 | ||
| 7 | Bananen | München | 1 | ||
| 8 | Äpfel | Berlin | 7 | ||
| 9 | Birnen | Köln | 1 | ||
| 10 | Äpfel | München | 9 | ||
| 11 | Bananen | Köln | 3 | ||
| 12 | Bananen | Hamburg | 2 | ||
| 13 | Bananen | Berlin | 5 | ||
| 14 | Äpfel | Köln | 2 | ||
| 15 | Bananen | Köln | 1 | ||
| 16 | Bananen | München | 4 | ||
| 17 | Äpfel | Berlin | 9 | ||
| 18 | Bananen | Hamburg | 3 | ||
| 19 | Bananen | Berlin | 4 | ||
| 20 | Äpfel | Köln | 2 | ||
| 21 | Bananen | Köln | 5 | ||
| 22 | Bananen | München | 8 | ||
| 23 | Äpfel | Berlin | 9 | ||
| 24 | Birnen | Köln | 9 | ||
| 25 | Äpfel | München | 5 | ||
| 26 | Bananen | Köln | 5 | ||
| 27 |
Nach dem, was Sie bisher kennen, müssten Sie immer einen Kriterienbereich mit den jeweiligen Kriterien eingeben, um das passende Ergebnis zu erhalten. Das wäre ziemlich unübersichtlich und arbeitsaufwändig - dabei dann auch noch fehlerträchtig. Gehen Sie besser wie folgt vor:
Um jede Kombination in den Kriterienbereich als Unikat der Auswertungstabelle zu holen, setzen Sie den Spezialfilter - Filterergebnis in ein anderes Tabellenblatt für den Listenbereich B2:C26 ein, indem Sie Duplikate wegfiltern. Automatisch entstehen dadurch identische Spaltenüberschriften. Der Übersichtlichkeit halber habe ich diese dann nach Obstsorte und Lager alphabetisch aufsteigend sortiert. In die Spalte D des Auswertungsblatts fügen Sie die gewünschte Datenbankfunktion ein, durch den gezielten Einsatz relativer und absoluter Bezüge wird in jeder Formel der Kriterienbereich bis zur jeweiligen Zeile ausgewertet. Am Schluss der Formel wird die Summe der darüber befindlichen Ergebnisse subtrahiert, so dass in jeder Zeile das Ergebnis für genau diese Kombination ausgegeben wird:
| A | B | C | D | E | |
| 1 | |||||
| 2 | Obstsorte | Lager | Menge/to | ||
| 3 | Äpfel | Berlin | 25 | ||
| 4 | Äpfel | Köln | 9 | ||
| 5 | Äpfel | München | 14 | ||
| 6 | Bananen | Berlin | 17 | ||
| 7 | Bananen | Hamburg | 11 | ||
| 8 | Bananen | Köln | 22 | ||
| 9 | Bananen | München | 13 | ||
| 10 | Birnen | Köln | 10 | ||
| 11 |
| Formeln der Tabelle | ||||
|
Die Formel aus D3 kann nach unten kopiert werden.
Als Alternativen hierzu wäre der Einsatz einer Pivottabelle oder Teilergebnis Menü zu nennen. Oder der Einsatz von Summenprodukt, was bei einer großen Datenbank langsam ist.
Probleme bei Textbedingungen - Auswertung über eine Funktion
Wenn Sie Textbedingungen haben, müssen Sie sehr achtsam sein, denn Datenbankfunktionen werten diese nach "enthält" aus und nicht danach, ob sie völlig identisch sind. Es gibt aber einen Ausweg, auf dem Sie Ihr Ziel erreichen können: Auswertung über eine Funktion!
Zunächst das Beispiel, es soll in F4 die Summe aus Spalte B ausgegeben werden, die in Spalte A den Namen "Wolf" hat. Ohne diese Vorgehensweise würden auch alle Werte von Spalte B addiert, die in Spalte A "Wolff" haben:
| A | B | C | D | E | F | |
| 1 | ||||||
| 2 | FALSCH | |||||
| 3 | ||||||
| 4 | Name | Wert | Summe | Wolf | 15 | |
| 5 | Wolff | 9 | ||||
| 6 | Wolf | 5 | ||||
| 7 | Wolff | 1 | ||||
| 8 | Wolf | 3 | ||||
| 9 | Wolf | 1 | ||||
| 10 | Wolff | 7 | ||||
| 11 | Wolf | 6 |
| Formeln der Tabelle | ||||||
|
Und hier die Regeln dafür:
- Man muss eine Formel erstellen, deren Rückgabewert eine Bool'scher Wert ist, also WAHR oder FALSCH
- Das Kriterium muss in der Formel enthalten sein
- Diese Formel muss sich unbedingt auf die Zelle beziehen, die direkt unter der Überschriftszeile der Liste befindet. Da wir in unserem Beispiel Spalte A untersuchen ist die erste Zelle in der Liste entsprechend A5. Ob dann im Ergebnis der Zelle A2 WAHR oder FALSCh steht, ist unerheblich
- Es darf im Suchkriterienbereich auf keinen Fall eine Überschriftenzeile stehen
- Der Suchkriterienbereich muss die "leere" Überschriftenzeile miteinbeziehen!!!
Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben