Excel Berechnungen + Formeln: Formeln erstellen und automatisch füllen!

Mit unseren Excel-Tipps vom Einsteiger zum Profi: Mit einfachen Formeln wird aus Excel ein praktisches Tool: Weit über eine einfache Tabellenverwaltung hinaus! Mit den Excel Formeln lassen sich ganz einfach die Werte von Zeilen, Spalten oder Tabellenzellen addieren, subtrahieren und auch komplexe Berechnungen durchführen. Wir zeigen hier die wichtigsten Excel Formeln und wie man sie nutzt, um sich die Arbeit mit Microsofts Excel Tool stark u erleichtern.

Bild oben: Mehrwertsteuer in Excel per Formel berechnen – mehr dazu im Video!

Excel Formeln: So nutzt man die Rechen-Tools!

Das Erstellen von Formeln ist eine wichtige Funktion in Microsoft Excel (und auch OpenOffice / LibreOffice). Eine Formel – nicht mehr als eine einfache Gleichung in einer Zelle – ist der Beginn jeder Berechnung oder Analyse. Zum Beispiel: Um die Summe von den beiden Zellen „B1“ und „C1“ zu berechnen, muss man nur die Formel „=B1+C1“ in eine Zelle seiner Wahl eingeben. Diese Syntax, so schlicht sie auch sein mag, legt das Fundament für sämtliche Operationen, die in Excel möglich sind.

 
Relativer versus absoluter Zellbezug

Der Unterschied zwischen relativem und absolutem Bezug in Formeln ist wichtig für das effektive Arbeiten mit Excel. Ein relativer Bezug, wie in „=B1+C1“, wandelt sich, wenn die Formel dupliziert wird – sie passt sich an die jeweilige Position an. Das ist wichtig, wenn Sie die gleiche Berechnung auf eine Datenreihe oder andere Zellen anwenden.

Wollen Sie jedoch, dass eine Zelle in der Formel konstant bleibt, unabhängig von ihrer neuen Position? Dann ist der absolute Bezug hilfreich. Fügen Sie das Dollarzeichen $ vor Spalte und Zeile ein, wie in „$B$1+C1“, und Sie haben eine feste Zuordnung in der Formel geschaffen.

Excel-Power: Automatisch Bruttopreise oder Nettopreise berechnen

Sobald die erste Summen-Berechnung funktioniert, steht der Weg zu den fortgeschrittenen Funktionen offen. Mit der Formel-Funktion können natürlich, neben dem Addieren von Werten, auch weitere Berechnungen durchgeführt werden. Möchte man automatisch aus Nettopreisen ohne Steuer Bruttopreise mit Mehrwertsteuer berechnen, gibt man neben der Spalte mit den Nettopreisen diese Formel ein: „=A2 * 1,19“. Alternativ könnte Excel auch den Wert zunächst durch 100 Teilen und dann mit 19 multiplizieren: In dem Formeleditor sind auch komplexere Rechnungen möglich.

Zieht man jetzt den Rahmen mit dem grünen Viereck unten rechts nach unten, wird die Berechnung auf alle markierten Zeilen ausgedehnt: Die Formel in der nächsten Zeile „=A3 * 1,19“, in der Zeile darauf „=A4 * 1,19“: So lassen sich ganz schnell und automatisiert die Bruttopreise für eine Spalte mit gegebenen Nettopreisen berechnen.

Profi-Funktionen: Berechnungen mit MAX(), MITTELWERT() oder ZINS()

Excel wartet mit einer Bibliothek vordefinierter Funktionen für spezialisierte Berechnungen auf: So kann die Software unter anderem die mathematischen Funktionen SUMME(), MITTELWERT(), MAX() oder die finanzmathematischen Funktionen ZINS() und ZZR() auswerten. Mit diesen praktischen Tools lassen sich Daten schnell und einfach auswerten und Schlüsse aus den Excel-Berechnungen ziehen.

So lassen sich zum Beispiel die größten Werte einer Zahlenreihe mit der Funktion MAX() finden, oder die häufigsten Werte einer Spalte mit der Funktion MODUS(). Möchte man den Mittelwert einer langen Datenreihe ermitteln, helfen MITTELWERT() oder MEDIAN().

 
SUMME() – Diese Funktion addiert alle Zahlen in einem bestimmten Bereich. Zum Beispiel, =SUMME(A1:A10) summiert die Zahlen von Zelle A1 bis A10. Wie in dem Beispiel oben schon zu sehen war, lässt sich das Wort „Summe“ weglassen, außerdem werden neben der Addition auch alle anderen mathematischen Berechnungen („+“, „-„, „*“ oder „/“) unterstützt, auch in Kombination:

• Beispiel: Gegeben seien folgende Werte in den Zellen A1 bis A4: 10, 20, 30, 40.
• Formel: =SUMME(A1:A4)
• Ergebnis: 100

 
MITTELWERT() – Diese Funktion berechnet den Durchschnitt einer Gruppe von Zahlen. =MITTELWERT(B1:B10) liefert den Durchschnitt der Werte in den Zellen B1 bis B10.

• Beispiel: Werte in B1 bis B4: 2, 4, 6, 8.
• Formel: =MITTELWERT(B1:B4)
• Ergebnis: 5

 
MAX() – Diese Funktion findet den höchsten Wert in einem Bereich. Angenommen, die Excel Tabelle enthält Verkaufszahlen in C1 bis C10, dann gibt =MAX(C1:C10) den höchsten Wert der Spalte C an.

• Werte in C1 bis C4: 5, 10, 15, 20.
• Formel: =MAX(C1:C4)
• Ergebnis: 20

 
ZINS() – Dies ist eine finanzmathematische Funktion, die den Zinssatz pro Periode einer Annuität berechnet. Sie benötigt Parameter wie die Gesamtzahl der Zahlungsperioden, die Höhe der einzelnen Zahlungen und den gegenwärtigen Wert des Darlehens. Zum Beispiel, wenn ein Darlehen über 10.000 € über 5 Jahre mit monatlichen Zahlungen von 200 € zurückgezahlt wird, könnten Sie =ZINS(60,-200,10000) verwenden, um den monatlichen Zinssatz zu berechnen.

• Beispiel: Ein Darlehen von 10.000 € soll in 5 Jahren (60 Monate) mit monatlichen Zahlungen von 200 € zurückgezahlt werden.
• Formel: =ZINS(60,-200,10000)
• Hypothetisches Ergebnis: 0,004167 oder 0,4167% (monatlicher Zinssatz)

 
ZZR() – Mit dieser Funktion können Sie die Anzahl der Zahlungsperioden für eine Investition berechnen, basierend auf regelmäßigen, konstanten Zahlungen und einem konstanten Zinssatz. Nehmen wir an, Sie möchten wissen, wie viele Monate Sie benötigen, um einen Kredit in Höhe von 5.000 € abzuzahlen, bei monatlichen Zahlungen von 150 € und einem Jahreszinssatz von 4%.

• Beispiel: Darlehen von 5.000 €, Jahreszinssatz von 4% und monatlichen Zahlungen von 150 €.
  Formel: =ZZR(4%/12,-150,5000)
  Hypothetisches Ergebnis: 36 Monate

 
MODUS() – Diese statistische Funktion gibt den am häufigsten vorkommenden Wert in einem Datensatz an. Wenn E1 bis E10 verschiedene Zahlen enthalten und man den am häufigsten darin vorkommenden Wert herausfinden muss, hilft die Formel =MODUS(E1:E10).

• Beispiel: Werte in E1 bis E10: 1, 2, 2, 3, 4, 4, 4, 5, 6, 7.
• Formel: =MODUS(E1:E10)
• Ergebnis: 4 (da der Wert 4 am häufigsten vorkommt)

 
MEDIAN() – Diese Funktion findet den mittleren Wert einer Liste von Zahlen, d.h. die Zahl in der Mitte, wenn die Zahlen der Größe nach sortiert sind. Für eine Liste von Werten in D1 bis D10, würde =MEDIAN(D1:D10) den Median zurückgeben.

• Beispiel: Werte in D1 bis D5: 1, 3, 5, 7, 9.
• Formel: =MEDIAN(D1:D5)
• Ergebnis: 5

Unterschied zwischen MITTELWERT und MEDIAN

Der Unterschied zwischen MITTELWERT() und MEDIAN() in Excel liegt darin, wie die Software den zentralen Wert in der Mitte einer Gruppe von Zahlen berechnet. Bei Zahlenreihen mit großen Ausreißern und Sonderfällen wird von vielen Nutzern die Funktion MEDIAN() bevorzugt, weil sie ein oft genaueres Bild der Werte wiedergibt.

MITTELWERT():

Die Funktion MITTELWERT() berechnet das arithmetische Mittel einer Zahlengruppe. Dazu werden alle Zahlen addiert und die Summe anschließend durch die Anzahl der Zahlen:

Formel: (a1 + a2 + … + an) / n

Der Mittelwert ist anfällig für Ausreißer, d.h., sehr hohe oder sehr niedrige Werte können den Mittelwert stark beeinflussen.
Beispiel: Für die Zahlen 1, 2, 2, 3, 4, 4, 4, 5, 6, 7 ist der Mittelwert (1+2+2+3+4+4+4+5+6+7) / 10 = 3,8.

MEDIAN():

Die MEDIAN() Funktion berechnet den mittleren Wert einer nach Größe sortierten Zahlengruppe. Bei einer ungeraden Anzahl von Werten ist der Median der mittlere Wert. Bei einer geraden Anzahl ist der Median das arithmetische Mittel der zwei mittleren Werte. Das Ergebnis von MEDIAN() ist dabei weniger anfällig für Ausreißer als das Ergebnis von MITTELWERT().

Beispiel: Für die Zahlen 1, 2, 2, 3, 4, 4, 4, 5, 6, 7 ist der Median (4+4) / 2 = 4, da 4 und 4 die mittleren zwei Zahlen nach der Sortierung sind.

In der statistischen Analyse wird in der Regel der Median-Wert bevorzugt, besonders wenn die Datensatzverteilung stark asymmetrisch ist. In der Regel gibt er ein genaueres Bild der zentralen Tendenz an. Der Mittelwert hingegen wird genutzt, wenn die Daten symmetrisch verteilt sind und keine Ausreißer vorkommen.