So rechnen Sie mit Excel-Funktionen

Komplexe Funktionen: ANZAHL, RUNDEN, MITTELWERT, Verschachtelungen

SUMME ist natürlich kein sehr spektakulärer Vertreter der Funktionen. Die vielfältigen Möglichkeiten von Funktionen zeigen sich erst an einem etwas komplexeren Beispiel: Nehmen wir an, der Spielverein Obersee veranstaltete einen Spielnachmittag, bei dem alle Teilnehmenden Punkte sammelten. Zur Auswertung trägt Organisator Egon Müller zuerst Jahrgang, Name, Vorname und Geschlecht in eine Tabelle ein und in der letzten Spalte die erreichte Punktzahl.

An den folgenden Beispielen sehen Sie, wie einfach die Auswertung solcher Datensätze mit den von Excel bereitgestellten Funktionen ist.

Funktion ANZAHL

Leider hat Egon Müller vergessen, wie viele Leute am Spielnachmittag teilnahmen. Zugegeben: Wenige Teilnehmer könnte Müller einfach von Hand zählen, aber er benutzt lieber die Funktion ANZAHL. Müller möchte, dass die Anzahl der Teilnehmer in Zelle I2 erscheint. Als Bezug gilt für die Formel die Position des ersten und des letzten Wertes, der für ihn wichtig ist, im Beispiel A2 und A12. Die korrekte Formel muss also bei aktivierter Zelle 12 so aussehen: =ANZAHL(A2:A12). Das Ergebnis ist 11.

Als Nächstes will Müller in Zelle I3 wissen, wie alt die Teilnehmenden im Durchschnitt sind. Dafür eignet sich die Funktion MITTELWERT. Sie ermittelt den Durchschnitt der eingetragenen Daten. In Spalte A steht der Jahrgang der Mitspieler, folglich muss die Formel =MITTELWERT(A2:A12) lauten. Das Resultat ist leider unschön: 1969.81818.

Funktion RUNDEN

Dem begegnet Müller mit der Funktion RUNDEN. Im Gegensatz zum MITTELWERT verlangt RUNDEN einen so genannten Parameter, also eine weitere Anweisung. In diesem Fall ist es die Anzahl Stellen hinter dem Komma, die angezeigt werden sollen. Hat eine Funktion wie in diesem Fall mehr als einen Parameter, werden sie durch Strichpunkte (;) voneinander getrennt.

Manchmal ist es ratsam, den gerundeten Wert in einem separaten Feld (im Beispiel in Zelle J4) darzustellen, die Formel bezieht sich dann auf die Zelle, in der das ungerundete Resultat steht. Beim Runden auf zwei Stellen nach dem Komma ist der zusätzliche Parameter 2, die Formel in J4 lautet =RUNDEN(I3;2), das Ergebnis 1969,82.

Diese Form ist in unserem Beispiel allerdings nicht sehr schön, weil jetzt für dasselbe Resultat zwei Zellen benötigt werden (I3 und J4). Abhilfe schafft ein geschachtelter Befehl, in dem die Funktionen MITTELWERT und RUNDEN kombiniert werden. Jetzt wird es etwas komplizierter: Die erste Funktion RUNDEN bezieht sich auf die zweite Funktion MITTELWERT, die sich wiederum auf bestimmte Zellen bezieht.

Dementsprechend müssen Sie in der Formel Klammern setzen. Im Beispiel lautet die Formel =RUNDEN(MITTELWERT(A2:A12);0). Wenn mit dem Parameter 0 gerundet wird, also keine Nachkommastellen zugelassen sind, steht in Zelle I3 die Zahl 1970, weil 1969,81818 aufgerundet wird.

FORMELTEXT: Formeln als Text wiedergeben

Funktionen MAX und MIN

Entsprechend verfahren Sie, um den Jahrgang des jüngsten und des ältesten Teilnehmers herauszufinden. Dabei helfen Ihnen die Funktionen MAX und MIN, die Sie nicht mit RUNDEN kombinieren müssen, da Sie ja nur eine Zahl suchen. Die jüngste Teilnehmerin hat Jahrgang 1991,=MAX(A2:A12), gesucht wird also die höchste Zahl. Der älteste Teilnehmer hat Jahrgang 1942, =MIN(A2:A12), hier wird die niedrigste Zahl gesucht.

Funktion ZÄHLENWENN

Nun möchte Müller gern wissen, wie viele Frauen und Männer teilgenommen haben. Diese Formel bezieht sich nicht mehr auf die gesamte Datenreihe, sondern nur noch auf jene Einträge, die eine bestimmte Bedingung erfüllen (w oder m in der Spalte "Geschlecht"). Die Funktion ZÄHLENWENN erfüllt einen ähnlichen Zweck wie ANZAHL - aber mit einem ganz entscheidenden Unterschied:

Diese Funktion braucht einen Parameter mit der Bedingung, die erfüllt sein muss, damit die Zelle mitgezählt wird. Wenn Müller also die Frauen zählen möchte, braucht er folgende Formel: =ZÄHLENWENN(D2:D12;"w"). Die Anführungszeichen vor und nach w braucht es, damit Excel erkennt, dass es sich hier um Buchstaben handelt und nicht um Zahlen, es hier also nichts zu rechnen gibt. Schreibt Meier die Formel in Zelle I7, erscheint dort das Resultat 5. Genauso verfahren Sie, wenn Sie die Anzahl der männlichen Teilnehmer berechnen lassen wollen.

Funktion RANG

Selbstverständlich interessiert nach einem Spielnachmittag auch die Rangliste der Spieler, interessant ist die Spalte E mit den Punkten.

Dazu benützt Müller die Funktion RANG. Sie ist insofern speziell, als dass sich sowohl Bezug als auch Parameter auf Zellbereiche beziehen. Zuerst bezeichnet man die Zelle, für welche die Rangzahl gesucht wird (in nebenstehenden Screenshot ist das z.B. E2), und als Parameter nach dem Strichpunkt den Bereich der Zellen, die alle zu berücksichtigenden Werte enthalten (E2 bis E12). Müller schreibt=RANG(E2;E2:E12) und erhält als Ergebnis 10. Das heißt, Vera Gertner wurde am Spielnachmittag Zehnte.

Funktion SVERWEIS

Jetzt könnte Müller endlich den Sieger ermitteln. Bei längeren Listen ist es aber schwierig, auf einen Blick Rang 1 zu finden. Hier greifen Sie wiederum auf die Funktion MAX zurück. Die Punkte sind in der Spalte E, die Formel lautet folglich =MAX(E2:E12), das Resultat ist 245.

Später könnte man auch das Minimum in der Spalte F suchen: =MIN(E2:E12). .´

Müller will aber noch eine andere Person erwähnen als nur den Erstplatzierten: Irgendein Teilnehmer hat ihm gesagt, dass der nächste Spielabend genau auf seinen 60.Geburtstag fällt, aber Müller kann sich nicht mehr erinnern, wer es war. Die passende Funktion zu seinem Problem heißt SVERWEIS.

SVERWEIS braucht zwingend vier Parameter:

* Ein Suchkriterium
* Eine Matrix (ein Bereich der Tabelle, in dem das Suchkriterium gefunden werden soll).
* Eine Spaltenzahl
* Einen logischen Wert namens Bereich-Verweis, der WAHR oder FALSCH sein kann. Ist er FALSCH, liefert die Formel das Resultat aus der Zeile, die genau dem Wert entspricht. Gibt man WAHR ein, liefert die Formel die größtmögliche Übereinstimmung und funktioniert nur, wenn die Werte in aufsteigender Reihenfolge sortiert sind.

Die Matrix wird begrenzt durch eine Zelle links oben und eine rechts unten, im Beispiel die Spalten A bis C, sie heißt also folgerichtig A2:C12. Innerhalb der Matrix werden die Spalten neu nummeriert, SVERWEIS sucht immer in der am weitesten links gelegenen Spalte nach dem Suchkriterium. Die erste Spalte in der Matrix ist A (wo die gesuchten Jahrgänge drinstehen, der gesuchte Teilnehmer muss Jahrgang 1955 haben) und trägt damit die Nummer 1. Spalte C, aus welcher der Wert (der Vorname) ausgegeben werden soll, trägt die Nummer 3. Die Formel lautet demnach =SVERWEIS(1955;A3:C13;3;FALSCH). Das Resultat ist der Vorname des mit Jahrgang 1955 in diesem Jahr 60-jährigen Urs (Bosch).

Funktion INDEX

Die Funktion SVERWEIS hat einen großen Nachteil: Sie kann als Resultat nur Werte anzeigen, die rechts von der ausgewerteten Spalte liegen. Braucht man einen Wert unabhängig von der Spalte, muss man auf die Funktion INDEX zurückgreifen. Genau so eine Formel muss Müller anwenden, will er auf einen Blick den Namen des Siegers sehen. Die Namen der Teilnehmenden stehen nämlich links von den Punkten.

INDEX benötigt drei Parameter:

* Den Bereich mit den Werten für das Resultat (Namen in Spalte B)
* Die Formel, deren Resultat bestimmen wird, aus welcher Zeile der Wert kommt.
* Die Spalte, auf die sich die Formel bezieht.

Das Ganze muss man sich also wie eine Art Fadenkreuz vorstellen. Der gesuchte Wert (der Name des Siegers) steht in Spalte B, der Bereich für die Formel ist deshalb B2:B12. Um die richtige Zeile zu ermitteln, benötigen wir wieder den höchsten Wert aus Spalte E. Dazu setzen wir die Funktion MAX ein:MAX(E2:E12). Die Funktion VERGLEICH liefert der Funktion INDEX die Angaben über die gesuchte Zeile. Die richtige Spalte, in der INDEX den höchsten Wert findet, ist E, folglich ist der dritte Parameter E2:E12. Fertig ausgeschrieben lautet die Formel =INDEX(B2:B12;VERGLEICH(MAX(E2:E12);E2:E12)).

Matrixformeln

Müller fehlt noch die komplette Rangliste. Das will er jetzt noch nachholen. Am einfachsten geht das mit einer Matrixformel. Matrixformeln berechnen mehrere Werte auf einmal und geben die Resultate in mehreren Zeilen aus:

Markieren Sie zuerst den Bereich, in dem die Resultate erscheinen sollen, also F2 bis F12. Schreiben Sie wieder die RANG-Formel in die Bearbeitungsleiste, aber definieren Sie im ersten Teil der Formel nicht wie vorher nur eine Zelle (E2), sondern den ganzen Zellbereich (E2:E12). Die Formel lautet neu =RANG(E2:E12;E2:E12). Schließen Sie die Eingabe mit der Tastenkombination Ctrl + Umschalt/Shift + Enter ab. Die Rangliste ist nun komplett erstellt und die Formel von geschweiften Klammern eingefasst. Daran erkennen Sie eine Matrixformel. Mehr Wissenswertes zu Matrixformeln bietet übrigens die Excel-Hilfe.

Hinweis: Wenn Sie statt der Tastenkombination CTRL+Shift+Enter die geschweiften Klammern von Hand eingeben, erhalten Sie keine Matrixformel!