Microsoft Excel: Formeln & Funktionen - Das Maxibuch, 2., aktualisierte und erweiterte Auflage

Kapitel 1 Probleme mit Funktionen lösen

92

Hinweis Die Funktion MITTELWERTWENN() ist erst ab der Excel-Version 2007 verfügbar!

MITTELWERTWENN() berechnet den Durchschnittswert für alle Zellen in einem Bereich,

die einem angegebenen Kriterium entsprechen – in diesem Fall Männer oder Frauen.

Abbildung 1.96: Berechnung der durchschnittlichen Bewertung von Männern bzw. Frauen

Die Formel

=MITTELWERTWENN(C2:C185;"m";D2:D185)

liefert eine Durchschnittsnote bei Männern

von 2,50. Frauen bewerten die Seminare mit einer Durchschnittsnote von 2,06 wesentlich

besser.

Wie bewerten

Frauen das

Excel-Seminar?

Man kann die Umfrageergebnisse auch noch feiner auswerten. Das Trainingscenter möchte

wissen, wie Frauen durchschnittlich die Excel-Seminare bewerten. Das Ergebnis liefert die

Funktion MITTELWERTWENNS().

Hinweis Die Funktion MITTELWERTWENNS() ist erst ab der Excel-Version 2007 verfügbar!

MITTELWERTWENNS() berechnet den Durchschnittswert für alle Zellen in einem Bereich,

die mehreren angegebenen Kriterien entsprechen – in diesem Fall Frauen und Excel.

Abbildung 1.97: Berechnung der durchschnittlichen Bewertung der Excel-Seminare von Frauen

Die Formel

=MITTELWERTWENNS(D2:D185;C2:C185;"w";B2:B185;"Excel")

liefert eine Durchschnitts-

note für die Excel-Seminare, gegeben von Frauen, von 1,38.

Datenbankfunktionen anwenden

Die Datenbankfunktionen finden immer dann Anwendung, wenn es darum geht, Auswer-

tungen aus einer bereits bestehen Excel-Datenbank zu generieren. Eine Datenbank in Excel

bezeichnet eine Tabelle mit Überschriften (die sogenannten »Feldnamen«) und einer Anzahl

von Datenzeilen (die sogenannten »Datensätze«) darunter. Die Informationen innerhalb der

einzelnen Spalten werden »Feldinhalte« genannt.

Datenbankfunktionen anwenden

93

Den Datenbankspezialisten unter Ihnen ist sofort klar, was wir mit einer »Excel-Datenbank«

meinen. Nach unseren Erfahrungen ist aber die weitaus größere Gruppe der Excel-Benutzer

in Teilen noch nicht für dieses Thema sensibilisiert und macht sich das Leben mit den Daten-

banklisten in Excel unnötig schwer, weil einige einfache Grundregeln für Listen verletzt wer-

den. Beachten Sie bitte, dass für Datenbanklisten Folgendes gelten muss:

씰

Listen haben Spaltenüberschriften

씰

Listen enthalten keine komplett leeren Zeilen oder Spalten

씰

Listen enthalten keine verbundenen Zellen

Vorteil ab 2007Die Verwendung von Excel als Datenbank stieß in den Versionen bis 2003 relativ schnell an

ihre Grenzen – eine Tabelle konnte maximal 65.536 Zeilen umfassen. Diese Grenze hat sich

seit der Version 2007 nach oben verändert: Sie können hier bis zu 1.048.576 Zeilen je Tabel-

lenblatt für Ihre Datenlisten nutzen. Bei der Spaltenzahl ist es ähnlich: Während bis Excel

2003 256 Spalten im Tabellenblatt verfügbar waren, sind es ab Excel 2007 16.384 Spalten.

Dynamische Namen für Datenbanken

Das Vergeben eines dynamischen Namens für eine Datenbank ist eine sehr effektive Möglich-

keit, sich die Arbeit mit den Daten innerhalb der Excel-Datenbank zu erleichtern. Ist dieser

Name festgelegt, können Sie eine vordefinierte Datenmaske für die Anzeige und Erfassung

von Daten anzeigen lassen und anschließend durch die Datensätze blättern, Daten suchen

oder auch Daten eingeben.

Wichtig bei der Vergabe von dynamischen Namen ist, dass mindestens eine Leerzeile unterhalb

und eine Leerspalte neben dem letzten Eintrag mit einbezogen werden. Hiermit umgehen Sie

Probleme bei der Erweiterung Ihrer Tabelle, d.h. beim Einfügen neuer Spalten oder Zeilen.

CD-ROMDie Excel-Datenbank für dieses Beispiel finden Sie auf der Buch-CD im Ordner \Buch\Kap12 in den

Arbeitsmappen DBFUNKTION leer.xls (Excel 97-2003) bzw. DBFUNKTION leer.xlsx (Excel 2007/2010)

auf dem Blatt Urdaten.

Um uns die Arbeit für spätere Auswertungen zu erleichtern, geben wir dieser Datenbank

einen dynamischen Namen, der, unabhängig davon, wie groß die Liste ist oder ob Einträge

hinzugefügt werden, immer den gleichen Namen hat. Dieser Name kann dann in den DB-

Funktionen verwendet werden.

HinweisDie hierzu eingesetzte Funktion BEREICH.VERSCHIEBEN() ist in Kapitel 9 näher beschrieben.

Dynamische

Namen in Excel

2007/2010

vergeben

Um der Excel-Liste einen dynamischen Namen zu geben, gehen Sie in Excel 2007/2010 wie

folgt vor:

1. Klicken Sie auf die Zelle A1 bzw. auf die Startzelle Ihrer Datenbank und klicken Sie auf

der Registerkarte Formeln in der Gruppe Definierte Namen auf die Schaltfläche Namen

definieren.

Abbildung 1.98: Der Weg zur Definition von Namen in 2007/2010

Kapitel 1 Probleme mit Funktionen lösen

94

2. Geben Sie in dem sich öffnenden Dialogfeld Namen definieren, wie in Abbildung 1.99

dargestellt, neben Name das Wort Start ein und klicken Sie auf OK.

Mit diesem Schritt haben Sie den Startpunkt für Ihre Datenbank festgelegt. Als Nächstes

müssen Sie mit der Funktion BEREICH.VERSCHIEBEN() den dynamischen Namen für

die Datenbank erzeugen. Hierzu gilt es zunächst zu ermitteln, wie viele Einträge die

Spalte A und die Zeile 1 beinhalten.

3. Markieren Sie die gesamte Spalte A per Klick auf den Spaltenkopf, und weisen Sie der

Spalte über das Namensfeld, wie in Abbildung 1.100 dargestellt, den Namen Zeile zu.

Bestätigen Sie Ihre Eingabe mit der

(¢)-Taste.

Hinweis Der Name Zeile für die Spalte A ist deshalb sinnvoll, weil die Anzahl der Einträge in Spalte A, in

diesem Fall also die Zeilen, errechnet werden sollen.

4. Zum Test errechnen Sie nun über die Funktion ANZAHL2() die Anzahl der Zeilen in

Spalte A. Klicken Sie dazu in eine beliebige, leere Zelle des Tabellenblattes und geben Sie

die Formel wie folgt ein:

=ANZAHL2(Zeile)

Abbildung 1.99: Definieren Sie den Startpunkt

Ihrer Datenbank

Abbildung 1.100: Der Spalte A wird ein Name zugewiesen