CLS: Tipps & Tricks: Excel-Formeln



Tipps & Tricks: Excel-Formeln

Auf dieser Seite finden Sie 10 Tipps & Tricks für Excel-Formeln. Soweit keine Einschränkungen dazu genannt werden, gelten diese Tipps & Tricks für alle Versionen von Excel.

Wenn in den folgenden Erläuterungen Texte wie MsgBox formatiert sind, handelt es sich um konkret einzugebende Inhalte wie VBA-Code oder die Eingabe von Werten. Menüs wie Datei Speichern sind wie hier zu sehen formatiert. Schaltflächen oder Registerkarten auf Dialogen werden wie Menüs behandelt.

Alle Tipps sind nach bestem Wissen geprüft, aber selbstverständlich ohne Gewähr. Sollten Sie doch einen Fehler darin entdecken, würden wir uns freuen, wenn Sie uns per eMail Bescheid sagen.

Sie möchten die Anzahl der im Tipp "Anzahl bestimmter Werte ermitteln (1): Sortieren" gesuchten Einträge nicht nur wissen, sondern auch in einer Excel-Zelle automatisch anzeigen können? Dann nutzen Sie die ZählenWenn-Funktion, damit Excel das ausrechnen kann. Wenn der zu untersuchende Bereich A1:A18 und gesuchte Inhalt Aachen ist, lautet die Formel:

=ZÄHLENWENN(A1:A18;"Aachen")

Immer dann, wenn der Vergleich für je eine Zelle des Bereichs richtig ist, wird die Anzahl um 1 erhöht. Damit erhalten Sie die Anzahl aller Zellen, welche Aachen enthalten.

Die im Tipp "Anzahl bestimmter Werte ermitteln (3): ZählenWenn-Formel" genannte Formel ist zwar einfach einzugeben, aber eventuell zu unflexibel für Sonderfälle. Dann können Sie auf die im Tipp "Matrix-Formeln erweitern Excels Fähigkeiten enorm!" beschriebenen Matrix-Formeln ausweichen. Die entsprechende Formel für die Anzahl der Aachen-Einträge lautet:

{=SUMME(WENN(A1:A18="Aachen";1;0))}

Denken Sie bitte daran, dass die geschweiften Klammern ({}) nicht mit eingegeben werden dürfen und die Bestätigung mit Strg+Umschalt+Eingabe (Ctrl+Shift+Return) erfolgen muss.

Wenn Sie in Excel zu einer bestimmten Zelle deren Nachbarzelle finden müssen, scheint das einfach zu sein: zu =$C$17 ist das natürlich =$D$17. Was aber, wenn Sie die Zelladresse nicht wissen, weil diese das Ergebnis einer Berechnung ist? In der folgenden Tabelle soll der Wert aus Spalte B neben dem Maximum der Spalte A ermittelt werden, also zur 18,4 der Nachbarwert 24,4:

  A B
1 12,3 45,6
2 7,3 18,9
3 18,4 24,4
4 17,1 27,7

Zur besseren Lesbarkeit sind dabei die Zellen $A$1:$A$4 als Bereichsname BereichA und $B$1:$B$4 als BereichB benannt. Das Problem ist, dass sie zu dem Maximalwert aus der Spalte A so etwas ähnliches wie seine Zelladresse finden müssen:

  A B
...
9 Maximum: 18,4 =MAX(BereichA)
10 dessen Index: 3 =VERGLEICH($B$9;BereichA;0)
11 dessen Nachbar: 24,4 =INDEX(BereichB; $B$10)
12 alles in einem: 24,4 =INDEX(BereichB; VERGLEICH(MAX(BereichA); BereichA; 0))

Die Max-Funktion ermittelt dabei zuerst das gewünschte Maximum aus Spalte A, also 18,4. Die Vergleich-Funktion findet innerhalb der Matrix BereichA den angegebenen Maximalwert und zeigt dessen Position innerhalb des Zellbereichs an, hier also eine 3 für die dritte Zelle. Der letzte Parameter 0 bedeutet, dass eine exakte Übereinstimmung notwendig ist.

Mit der Index-Funktion kann nun wiederum aus dem BereichB der Zellinhalt an gleicher Position ermittelt werden. In der letzten Zeile sind alle diese einzelnen Formeln dann in einer einzigen zusammengefaßt.

Wenn Sie viele gleichartige Tabellen (etwa wöchentliche oder monatliche Übersichten) in einer Datei haben, lassen sich deren Werte sehr leicht zusammenfassen. Voraussetzung dafür ist, dass die Tabellen in der Struktur übereinstimmen. Das ist meistens schon deshalb gegeben, weil sie oftmals durch Kopieren entstanden sind.

Für die Summe der jeweiligen Werte gehen Sie in einem neuen Blatt beispielsweise in C5, wo in den anderen (Monats-)Tabellen jeweils der gewünschte Wert steht. Beginnen Sie die Formel mit:

=Summe(

Dann wechseln Sie in die Tabelle Januar und markieren dort C5, so dass nun in der Formel

=Summe(Januar!C5

zu sehen ist. Halten Sie anschließend die Umschalt-(Shift-)Taste gedrückt und klicken auf die Registerlasche des Blattes Dezember. Die Formel erweitert sich zu

=Summe('Januar:Dezember'!C5

und Sie können mit der Eingabe-(Return-)Taste die Eingabe beenden, wobei auch die schließende Klammer ergänzt wird. Sie sehen als Jahresergebnis die Summe aller Werte für die Zellen C5. Für kompliziertere Daten lesen Sie den Tipp "Sehr unterschiedliche Datenquellen konsolidieren".

Excel besitzt umfangreiche finanzmathematische Methoden, die Ihnen auch helfen, Kredite oder Sparpläne zu berechnen. Inhaltlich unterscheiden sich diese beiden ja nur in der Richtung, die das Geld nimmt: grundsätzlich gilt bei allen Berechnungen, dass positive Beträge Ihnen zufließen und negative Sie Geld kosten. Um die Gesamtkosten für einen Kredit zu ermitteln, tragen Sie die relevanten Werte wie folgt ein:

  A B C
1   monatlich jährlich
2 Kreditsumme: 100.00,00 €  
3 Regelmäßige Zahlung: -500,00 €  
4 Monatszins: 0,54% 6,50%   B4:=C4/12
5 Dauer in Monaten: 180 15   B5:=C5*12
6 Zukünftiger Wert: -112.647,70 €     B6:=ZW(B4;B5;B3;B2)
7          
8 Gesamtrückzahlung: -202.647,70 €     B8:=B6+B3*B5

Dabei sind in einigen Feldern selbstverständlich Formeln enthalten, die in der letzten Spalte zu sehen sind. Die Funktion ZW (Zukünftiger Wert) errechnet den Endwert einer Investition unter Berücksichtigung regelmäßiger Zahlungen und eines konstanten Zinssatzes.

Sie haben vielleicht bemerkt, dass die Formel selber gar keine Angaben über die Einheit der Dauer (Monate, Quartale, Jahre, etc.) verlangt. Diese stecken implizit im Zinssatz drin: wenn Ihre Rückzahlungen wie oben monatlich verzinst werden, teilen Sie den Jahreszins durch 12 und geben die Dauer auch in Monaten an.

Für den oben beispielhaft gerechneten Kredit von 100.000,- € müssen Sie bei jährlich 6,5% Zins übrigens nicht nur den zukünftigen Wert betrachten. Tatsächlich haben Sie ja in diesem Zeitraum auch eine monatlichen Rückzahlung von 500,- € gehabt. Die Bank hat also nach 15 Jahren mehr als das Doppelte des ursprünglichen Kreditbetrags von Ihnen erhalten...

Die im Tipp "Kreditberechnungen (1): einfach" geschilderte Technik wird lästig, wenn Sie damit verschiedene Varianten durchrechnen wollen. Mit 10 verschiedenen Laufzeiten und 10 unterschiedlichen Zins-Angeboten wären das schon 100 identische Berechnungen. Dafür stellt Excel nicht nur eine Matrixformel (siehe Tipp "Matrix-Formeln erweitern Excels Fähigkeiten enorm!") zur Verfügung, sondern auch einen Assistenten zur Verfügung.

Beginnen Sie mit der ersten Berechnung aus dem Tipp "Kreditberechnungen (1): einfach" wie in der folgenden Tabelle gezeigt. Schreiben Sie dann in die Zeile neben dem Ergebnis die verschiedenen Prozentsätze und in die Spalte unterhalb die möglichen Zeiten (hier jeweils auf 4 Varianten gekürzt):

  A B C D E F
1   jährlich
2 Kreditsumme: 100.00,00 €
3 Regelmäßige Zahlung: 0,00 €
4 Jahreszins: 6,50%
5 Dauer in Jahren: 15
6 Zukünftiger Wert: -257.184,10 € 5,5% 6,0% 6,5% 7,0%
7   13
8   14
9   15
10   16

Markieren Sie den Bereich inklusive der neuen Zeilen/Spalten, wie oben bereits hellblau hinterlegt. Im Menü Extras wählen Sie Tabelle (oder Menü Extras Mehrfachoperation bis Excel 2002) und sehen dann einen kleinen Dialog, in dem Sie für Zeilen in die Zelle B4 (weil dort die Prozente wie auch in der Zeile stehen) und für Spalten in die Zelle B5 (weil dort die Dauer wie auch in der Spalte steht) klicken.

Nach Bestätigung mit OK erstellt Excel für diesen Bereich eine Matrixformel {=MEHRFACHOPERATION(B4;B5)}, die für jede Zeilen-/Spalten-Kreuzung das entsprechende Ergebnis anzeigt:

  A B C D E F
...  
6 Zukünftiger Wert: -257.184,10 € 5,5% 6,0% 6,5% 7,0%
7   13 -200.577 € -213.293 € -226.749 € -240.985 €
8   14 -211.609 € -226.090 € -241.487 € -257.853 €
9   15 -223.248 € -239.656 € -257.184 € -275.903 €
10   16 -235.526 € -254.035 € -273.091 € -295.216 €

Am einfachsten ist dies im Schnittpunkt für 15 Jahre und 6,5% zu sehen, wo trotz der hier aus Platzgründen gerundeten Ergebnisse wieder der Wert -257.184 € wie in der ursprünglichen Berechnung zu finden ist.

Oft genug gibt es verzwickte Probleme in Excel, die nicht mit "normalen" Formeln oder Datenlisten- bzw. Datenbank-Funktionalität zu lösen sind. Bevor Sie nun versuchen, das mit VBA-Programmierung zu bearbeiten, sollten Sie unbedingt einen Blick auf Matrix-Formeln werfen. Nehmen wir an, Sie haben folgende Daten:

  A B C D
1   Sonne Mond Sterne
2 Jan 11 56 46
3 Feb 5 2 5
4 Feb 22 48 55
5 Feb 8 7 7
6 Mrz 44 56 8
7 Jan 79 2 45

Um nun beispielsweise die Summe der Werte für Sonne im Januar zu ermitteln, gibt es eine eigene Funktion, noch ganz ohne Matrix-Fähigkeiten: =SummeWenn($A$2:$A$7;"Jan";$B$2:$B$7).

Damit können Sie im ersten Argument $A$2:$A$7 als Suchbereich und im zweiten Argument "Jan" als Suchkriterium vorgeben. Die tatsächliche Summe wird gebildet aus dem dritten Argument mit dem Bereich $B$2:$B$7 und zwar nur für die Zellen, bei denen die Bedingung zutrifft. Das Ergebnis wäre hier 90.

Jetzt wollen Sie aber statt der Summe deren Mittelwert ausrechnen. Und? Genau, diese Funktion (z.B. =MITTELWERTWENN() gibt es nicht. Schade eigentlich, aber genau hier helfen Ihnen nun die Matrixfunktionen. Sie können die zwei Schritte, nämlich eine Wenn-Bedingung für jede(!) Zeile und anschließend deren Mittelwert, in einer einzigen Zelle zusammenfassen. Das Besondere dabei ist, dass Sie für sechs Zeilen eigentlich sechsmal eine Wenn-Funktion in je einer Zeile benötigen, deren Mittelwert Sie dann in einer siebten Zelle errechnen könnten. Das bräuchte sieben Zellen mit Formeln.

Die Matrix-Formel macht diese Arbeit in einer einzigen Zelle, daher dürfen Sie darin (entgegen den üblichen Regeln) ganze Bereiche statt einzelner Zellen angeben. Nur das Ergebnis muss am Ende auf eine einzige Zahl hinauslaufen, was durch die sogenannten Aggregatfunktionen wie SUMME(), MITTELWERT() oder ANZAHL() um die innere Berechnung herum erledigt wird. Schreiben Sie also etwas weiter unten in der Tabelle:

  A B C D
... 
12   Sonne Mond Sterne
13 Jan =MITTELWERT(WENN($A$2:$A$7=$A13;B$2:B$7))
14 Feb
15 Mrz

In der Formel wird tatsächlich ein ganzer Bereich ($A$2:$A$7) mit dem Inhalt einer einzigen Zelle ($A13) verglichen und als Ergebnis ebenfalls ein ganzer Bereich (B$2:B$7) zurückgegeben. Das klappt aber, solange es eine Matrixformel ist und die vielen Einzelwerte mit der Aggregatformel MITTELWERT() zusammengefaßt wird.

Damit das klappt, dürfen Sie diese Formel nicht einfach mit Return bestätigen, sondern müssen mit Strg+Umschalt+Return (Ctrl+Shift+Return) abschließen. Danach erscheinen um die Formel herum geschweifte Klammern, die Sie aber nicht als Zeichen eingeben dürfen. Das korrekte Ergebnis ist nun 45. Die Formel ist bereits so vorbereitet, dass Sie diese in die Nachbarzellen kopieren können und dort alle passenden Mittelwerte finden.

Leider bietet Excel mit seiner Text-Funktion keine Möglichkeit, das Quartal zu einem Datum zu errechnen. Anstatt nun eine komplizierte Formel zu bemühen,können Sie dies mit der Monat- und der Wahl-Funktion lösen, wenn beispielsweise in A1 das Datum steht:

=WAHL(MONAT(A1);1;1;1;2;2;2;3;3;3;4;4;4)

Die Monat-Funktion findet für das Tagesdatum die zugehörige Zahl von 1 bis 12 und die Wahl-Funktion nimmt den passenden Eintrag der nächsten zwölf Parameter.

Manchmal wäre es praktisch, Teile einer Formel schon mal ausrechnen zu können, um das Ergebnis schrittweise zu prüfen. Das geht tatsächlich, wenn Sie im Editiermodus sind, also beispielsweise für eine Zelle mit Formel die F2-Taste gedrückt haben.

Markieren Sie dann den gewünschten Formelteil und drücken F9, so verwandelt sich dieser in sein Ergebnis. Achtung: Sie müssen diesen Editiermodus mit Esc verlassen, sonst steht dort dauerhaft das Ergebnis statt der Teilformel!

Natürlich ist Excel keine relationale Datenbank wie Access. Trotzdem lassen sich damit anhand einer eindeutigen Kennung die Werte aus einer anderen Tabelle finden.

Angenommen, Sie haben in einer Tabelle eine Liste aller Niederlassungen mit ihren Adressen, also Ort, Straße und Hausnummer als Spaltenüberschriften. Dann müssen Sie diese nach der ersten Spalte (also dem Ort) alphabetisch sortieren lassen. Außerdem sollten Sie sie wegen der bequemeren Nutzung mit einem Bereichsnamen versehen und deshalb nur die Inhalte (ohne die Spaltenüberschriften!!) als ListeNiederlassungen benennen.

Auf einem beliebigen anderen Tabellenblatt können Sie nun beispielsweise in Zelle C3 den Ort der Niederlassung eintragen und ermitteln dazu die passende Straße in einer Nachbarzelle mit folgender Formel:

=SVERWEIS(C3;ListeNiederlassungen;2;FALSCH)

Der Parameter 2 gibt die gewünschte Spalte der Liste an, deren Wert zurückgegeben werden soll. Wenn der in C3 gefundene Wert in der Liste gar nicht vorkommt, würde Excel den nächstkleineren Wert auswählen, was natürlich bei Adressen fatal wäre. Daher sollten Sie als letzten Parameter FALSCH angeben, was in einem solchen Fall die #NV-Meldung ergibt.

Um Straße und Hausnummer gemeinsam anzuzeigen, erweitern Sie die Formel so:

=SVERWEIS(C3;ListeNiederlassungen;2;FALSCH) & " " & SVERWEIS(C3;ListeNiederlassungen;3;FALSCH)

Für eine Auswahl des Ortes aus einer DropDown-Liste lesen Sie am besten den Tipp "Werte in einer Zelle aus einer Liste auswählen"