So rechnen Sie mit Excel-Funktionen

Die Tabellenkalkulation Microsoft Excel 2016 bot über 400 Funktionen – von einfachen Rechenaufgaben wie z.B. eine Addition (Funktion SUMME) bis zu aufwändigen Auswertungen der Daten, bei der Werte verglichen werden (Funktion INDEX). Auf dieser Webseite stellt Microsoft alle vorhandenen Funktionen vor. Damit fällt auch der Umgang mit komplexen Datensätzen leicht – allerdings nur, wenn Sie wissen, wie Formeln und Funktionen in Excel grundsätzlich funktionieren.

Die hier vorgestellten Funktionen lassen sich nicht nur mit Excel 2013 oder Excel 2016 verwenden, sondern funktionieren auch mit älteren Excel-Versionen. Die hier vorgestellten Funktionen entsprechenden zudem den Funktionen in Excel 2011 für Macintosh .  

Ob die hier vorgestellten Formeln auch in der aktuellen Excelversion 2019 funktionieren, müssen Sie ausprobieren. Die Wahrscheinlichkeit dafür ist aber groß.

Die wesentliche Neuerung von Excel 2016 gegenüber der Vorgängerversion 2013 waren vor allem die erweiterten Visualisierungsfunktionen. Wie sich Office 2016 speziell auf dem Mac macht, erklärt dieser Ratgeber. Fast alle Funktionen der Windows-Vorgängerversion Excel 2013 stehen damit auch auf dem Macintosh zur Verfügung.

Sie können sich alle in Excel vorhandenen Funktionen anzeigen lassen, wenn Sie die Registerkarte „Formeln“ wählen und dann die Schaltfläche „Funktion einfügen“ anklicken. In dem sich dann öffnenden Fenster wählen Sie bei „Kategorie auswählen“ „Alle“ aus. Dann zeigt Excel im Auswahlfenster darunter alle vorhandenen Funktionen an. Mit einem Mausklick bekommen Sie zu jeder Funktion eine kurze Beschreibung.

Formeln: Die Grundlage für Funktionen in Excel

Bevor wir zu den Excel-Funktionen wie SVERWEIS oder Matrixformeln kommen, erklären wir erst einmal deren Basis: Die Formeln.

Die Formel schreiben Sie immer in die Bearbeitungsleiste und sie beginnt immer mit einem Gleichzeichen (=). Ein einfaches Beispiel: Lassen Sie Excel das Resultat der Summe 113+253 berechnen. Sie klicken dazu auf eine beliebige Zelle, schreiben =113+253 in die Bearbeitungsleiste und drücken die Enter-Taste. Sofort wird das Resultat der Summe – 366 – in der zuvor angeklickten (“aktivierten”) Zelle angezeigt.

©2014

Meistens ist es aber so, dass Sie mit Werten rechnen, die bereits in der Tabelle stehen. Sie geben keine Zahlen ein, sondern beschreiben lediglich, wo sie eingetragen sind. Dazu nutzen Sie das Koordinatensystem von Excel: Zeilen werden nach dem Schema 1,2,3 etc. nummeriert, Spalten mit A,B,C etc. alphabetisch angezeigt. Die Zelle ganz oben links ist also A1, die rechts davon B1 und die darunter A2 etc.

In obigen Screenshot steht in der Zelle A1 die Zahl 113, in A2 253. Für die Berechnung bezieht sich Excel auf Zellen, die Koordinaten A1 und A2 der Zellen heißen deshalb “Bezug”. Die Formel, die wir für die Zelle A3 eintragen, lautet =A1+A2. Das angezeigte Resultat in A3 ist wieder 366.

Wenn Sie später Werte in der Tabelle verändern, werden Sie sehen, dass sich auch die Resultate Ihrer Formeln sofort anpassen. Bei komplexen Tabellen mit vielen Werten behalten Sie so die Übersicht. Ebenso bei Tabellen, die ständig aktualisiert werden. Sie nutzen Excel nicht mehr nur als Datenspeicher, sondern als komplexes Auswertungsprogramm für Ihre Daten.

Funktionen in Excel

Mit dieser Art der Formelnutzung wäre Excel aber nicht fähig, wirklich komplexe Berechnungen zu ermöglichen. Darum geht die Software noch einen Schritt weiter und bietet die so genannten Funktionen an. Ein Beispiel: Anstatt im obigen Beispiel =A1+A2 zu schreiben, sagen wir Excel einfach, was wir tun wollen: Wir brauchen die Summe. Fürs Addieren stellt Excel die gleichnamige Funktion SUMME zur Verfügung.

Video-Kurs “Excel Formeln & Funktionen & Pivot-Tabellen Masterclass!” bei Udemy ansehen

Eine Funktion besteht aus dem groß geschriebenen Namen, danach in Klammern () einzelne Zellen bzw. ein ganzer Zellbereich sowie andere Parameter – jeweils mit ; getrennt. Für einen Zellbereich geben Sie die Koordinaten der ersten und der letzten Zelle an, die berücksichtigt werden sollen, getrennt durch einen Doppelpunkt. Die Formel für das Beispiel ist =SUMME(A1:A2).Sie könnte aber auch =SUMME(A1:A23) heißen oder =SUMME(A:A), wenn die ganze Spalte A addiert werden soll ist. Ohne Funktion wäre dann die Summe schon schwieriger auszurechnen. Am einfachsten erfassen Sie Zellbereiche übrigens, in dem Sie die Zellen beim Erfassen der Formel mit der Maus markieren, statt die Zellbezüge einzutippen.

Hinweis: Alle Funktionen finden Sie unter der Registerkarte Formeln. Die Funktionsbibliothek dort sind nach Gruppen sortiert.

Auf der nächsten Seite erklären wir ” Komplexe Funktionen”.

Weitere Tipps, Tricks und Tools rund um Excel:

Die 5 wichtigsten Excel-Makros für den Alltag

Drei pfiffige Excel-Tools und eine Excel-Alternative

Zehn Excel-Tipps für Office-Profis

Pivot-Tabellen in Excel erstellen – so geht’s

Die zehn besten Excel-Funktionen

In nur 8 Schritten zur perfekten Excel-Tabelle

Die 5 wichtigsten Excel-Makros für den Alltag

Rechnen mit Excel – Formeln und Funktionen

©2014

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.

©2014

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.

Funktion MITTELWERT

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.

Lese-Tipp: Die besten Tipps für Excel

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.

Drei pfiffige Excel-Tools und eine Excel-Alternative

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.

©2014

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.

10 Tricks für schnelleres Arbeiten mit Microsoft Excel

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:

©2014

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.

©2014

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.

Excel-Tabellen mit professionellen Steuerelementen erstellen

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

Funktion SVERWEIS

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.

©2014

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

Tipp: Die zehn besten Excel-Funktionen

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.

©2014

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

Tipp: Geniale Organigramme mit Excel

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:

©2014

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!

Funktionen in Excel bestehen aus einem Funktionsnamen, dem in Klammern gesetzt Funktionswerte folgen. “Bereich” steht für Zellen, für welche die Koordinaten der ersten und der letzten Zelle getrennt durch einen Doppelpunkt angegeben werden (z.B. A1:A15). Andere Funktionen arbeiten mit Zahlen, die direkt eingetragen werden (3;5;…) oder auf die mit Koordinaten verwiesen wird (A1;B17).

Hier eine Auswahl der für Microsoft Excel verfügbaren Funktionen.

ANZAHL(Bereich)

Zählt die Anzahl der Einträge in einem Bereich.

ARBEITSTAG(Anfangsdatum;Tage;[Freie Tage])

Ermittelt das Datum vor oder nach einer bestimmten Anzahl von Arbeitstagen.

HEUTE()

Ermittelt das aktuelle Datum.

ZÄHLENWENN(Bereich;Kriterium)

Zählt die Einträge nur, wenn Sie ein Kriterium erfüllen.

SUMME(Zahl 1;Zahl 2;… )

Addiert Zahlen und Bereiche.

SUMMEWENN(Bereich;Kriterium;Summe-Bereich)

Addiert Zahlen und Bereiche nur, wenn sie ein Kriterium erfüllen Zuerst wird der Bereich angegeben, in dem das Kriterium steckt, dann das Kriterium und schließlich der Bereich, in dem die Zahlen addiert werden.

PRODUKT(Zahl 1;Zahl 2;…)

Multipliziert Zahlen oder Bereiche.

MAX(Bereich)

Ermittelt den maximalen Wert aus einem Bereich.

MIN(Bereich)

Ermittelt das Minimum aus einem Bereich.

RUNDEN(Zahl; Anzahl Stellen)

Rundet eine Zahl oder einen Wert auf eine beliebige Anzahl Stellen vor oder nach dem Komma. Ist die Anzahl Stellen größer als 0, wird auf Anzahl hinter dem Komma gerundet. Ist der Wert 0, wird auf die ganze Zahl gerundet, und ist der Wert kleiner als 0, wird auf die entsprechende Anzahl Stellen vor dem Komma gerundet.

WENN(Suchkriterium;Aktion)

Führt eine Aktion nur durch, wenn das Suchkriterium erfüllt ist. Oft in Kombination mit anderen Formeln.

VERGLEICH(Suchkriterium;Bereich;Typ)

Vergleicht Werte nach einem Suchkriterium (zum Beispiel eine Zahl). Für den Typ gibt es drei mögliche Werte: 0: Liefert den ersten Wert, der genau dem Suchkriterium entspricht. 1: Liefert den größten Wert, der kleiner oder gleich wie das Suchkriterium ist. –1: Liefert den kleinsten Wert, der größer oder gleich wie das Suchkriterium ist.

SVERWEIS(Suchkriterium;Matrix;Spaltenmatrix)

Sucht nach einem Kriterium in der am weitesten links gelegenen Spalte einer Matrix. Ausgegeben wird der Wert in der Spalte, die in der Spaltenzahl definiert ist, also zwingend rechts des Suchkriteriums liegt.

Viele nützliche Excel-Tipps

Bis jetzt drehte sich alles um Formeln und Funktionen. Doch wie druckt man eine Excel-Tabelle korrekt aus? Wozu dient die bedingte Formatierung? Was hat es mit Pivot-Tabellen auf sich? Wie erzwinge ich einen Zeilenumbruch? Diese und noch viele weitere Fragen zu Excel beantworten unsere Tipps und Tricks auf der nächsten Seite.

Bedingte Formatierung

Mit bedingten Formatierungen können Sie in Excel die Formatierung einer Zelle abhängig von deren Inhalt festlegen. Damit lassen sich wichtige Bereiche hervorheben. Markieren Sie dazu den Bereich, die Sie formatieren wollen. Gehen Sie dann auf “Start, Bedingte Formatierung” und wählen Sie dann die gewünschte Formatierung. In unserem Beispiel oben ist das „Datenbalken“. Fahren Sie mit dem Mauszeiger über die Vorschaubilder, der markierte Bereich in der Tabelle ändert sich fortlaufend. Haben Sie die gewünschte Formatierung gefunden, klicken Sie sie an.

Tipp: Bedingte Formatierung in Excel und Libre Office nutzen

Pivot-Tabellen

Pivot-Tabellen in Excel helfen Ihnen bei der Analyse von Daten, ohne das ursprüngliche Datenmaterial zu verändern. Eine Pivot-Tabelle ist ein Excel-Tool, mit dem Sie Daten zusammenfassen und analysieren können. Excel bündelt hierzu verschiedene Daten aus einer Tabelle, sortiert diese und generiert daraus die Pivot-Tabelle. Zusätzlich besitzen diese speziellen Tabellen verschiedene Filtermöglichkeiten, die das Analysieren erleichtern.

Excel Druckbereich festlegen

Unter „Datei, Drucken“ sollten Sie einmal grundlegend alle Einstellungen für den Drucker vornehmen. Wenn Sie dann konkret ein Tabellenblatt ausdrucken wollen, gehen Sie so vor: Markieren Sie mit der Maus den auszudruckenden Bereich auf der Tabelle. Dann öffnen Sie die Registerkarte „Seitenlayout, Seite einrichten, Druckbereich“ und wählen dann „Druckbereich festlegen“.

Tipp: Excel-Tabellen perfekt ausdrucken

In Excel Zeilenumbruch erzwingen

Wenn der Inhalt einer Zelle sehr lang ist, wird er nur abgeschnitten dargestellt. Sie sehen dann einen Teil des Textes nicht, weil er durch den Inhalt der Nachbarzelle verdeckt wird. Natürlich können Sie die Spalte einfach breiter aufziehen. Aber damit wird oft die Gesamtansicht des Tabellenblattes beeinträchtigt.

Sie können dieses Problem aber grundsätzlich lösen, indem sie einen Zeilenumbruch erzwingen. Markieren Sie die Zelle, für deren Text Sie den Zeilenumbruch erzwingen wollen und klicken Sie dann auf „Start, Ausrichtung, Zeilenumbruch“. Und schon sehen Sie den gesamten Text in der Zelle umgebrochen, so dass er über mehrere Zeilen geht.

Tipp: Wenn Sie bei bestimmten Wörtern einen zusätzlichen Zeilenumbruch erzwingen wollen, dann geben Sie dort einfach einen Bindestrich ein (auf unserem obigen Screenshot ist das bei „Cloud-Speicher“ und „Online-Speicher“ der Fall).

Wenn Sie zusätzlich einen Zeilenumbruch erzwingen wollen, obwohl die Spalte für die Darstellung des Textes breit genug ist, dann setzen Sie den Mauszeiger an die gewünschte Stelle und drücken die Tasten ALT+Enter. In unserem Beispiel auf dem Screenshot ist das hinter dem Doppelpunkt der Fall.

Lese-Tipp: Noch mehr Ratgeber zum Thema Excel:

Drei pfiffige Excel-Tools und eine Excel-Alternative

Tolle Diagramme mit Excel, PowerPoint und Word gestalten

Profi-Tipps für Excel und Calc

So kontrollieren Sie Ihre Ausgaben mit Excel

Die zehn besten Excel-Funktionen

Mit Excel clever rechnen

Excel-Funktionen für Profis

Excel-Tabellen perfekt ausdrucken

Die besten Tipps für Excel

Drei pfiffige Excel-Tools