Thema: Formeln und Funktionen

Schaltjahre in Excel berechnen 7

 in Datum und Uhrzeit / Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: DATUM / JAHR / REST / Schaltjahr / TAG / WAHL Von:
2 mal 3 Varianten, um in Excel ein Schaltjahr zu ermitteln.
 

Heute ist wieder der Tag, den es nur alle vier Jahre gibt: Der 29. Februar. Wenn auf Deinem Handy (Kalender, Armbanduhr…) also schon der erste März angezeigt, dann hat hier offensichtlich irgendwer ignoriert, dass 2016 ein Schaltjahr ist.

Wie landläufig bekannt ist, finden Schaltjahre nur alle vier Jahre statt. Aber das ist nicht die ganze Wahrheit. Es sind genau genommen 3 Regeln, die bei der Beurteilung eines Schaltjahres zu berücksichtigen sind.

Welche genau das sind und vor allem wie man das in eine schicke Excel-Formel verpackt, erklärt der heutige Artikel.

Und so geht’s:

Weiterlesen »

Eingabeprüfung: Nur Großschreibung erlauben 17

 in Allgemeine Excel-Tipps / Daten- und Analysetools / Formeln und Funktionen / Logisch / Text  Stichworte: GROSS / IDENTISCH / KLEIN Von:
Mit Hilfe der Datenüberprüfung zulässige Eingaben gezielt steuern
 

In Excel könnte alles so schön einfach sein, wenn es da nicht immer den bösen Anwender gäbe.

Der macht leider nicht immer das, was er soll (oder zumindest was wir glauben, dass er machen soll) und bringt durch unerwünschte oder fehlerhafte Eingaben unsere schönen Kalkulationen aus dem Gleichgewicht.

Im heutigen Artikel zeige ich, wie man sicherstellt, dass bei einer Dateneingabe nur Großbuchstaben (oder nur Kleinbuchstaben) akzeptiert werden.

Und so geht’s:

Weiterlesen »

Fehlende Zahlen in einer Liste finden 36

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: KKLEINSTE / WENN / ZÄHLENWENN / ZEILE Von:
Lücken in einer Werte finden: Eine einfache und eine nicht ganz so einfache Variante!
 

Neulich erhielt ich eine Excel-Anfrage zu einem Thema, über das ich mir bisher noch nie Gedanken gemacht hatte. Da ich mir aber vorstellen kann, dass der eine oder die andere auch schon an einer ähnlichen Fragestellung verzweifelt ist, habe ich diesen Artikel geschrieben.

Worum es geht?

In einer langen Liste fortlaufender Nummern (sagen wir z.B. Rechnungsnummern) sind immer wieder Lücken enthalten. Das heißt, zwischendurch fehlen einfach Nummern.

Da die Liste jedoch wirklich seeehr lang ist und wir seeehr bequem sind, soll Excel uns bei der Suche nach den fehlenden Zahlen helfen.

Und so geht’s:

Weiterlesen »

Den letzten Wert einer Liste finden 14

 in Formeln und Funktionen / Informationen / Nachschlagen und Verweisen / Statistik  Stichworte: INDEX / MAX / SUMMENPRODUKT / ZEILE Von:
Wenn der SVERWEIS mal versagt, kann SUMMENPRODUKT vielleicht weiterhelfen
 

Vielen Excel-Anwendern dürfte bekannt sein, wie man mit Hilfe der SVERWEIS-Funktion einen bestimmten Wert in einer Liste findet.

Wenn der gesuchte Eintrag mehrmals in der Liste vorhanden ist, dann gibt die SVERWEIS-Funktion üblicherweise den ersten Wert wieder.

Was macht man aber, wenn man eigentlich den letzten Wert bräuchte? Wann also z.B. ein Produkt zum letzten Mal verkauft wurde? Hier hilft der SVERWEIS leider nicht weiter.

Ein etwas kniffeliges Problem, das wir in diesem Artikel ganz elegant lösen werden. Und so geht’s:

Weiterlesen »

Trendanalysen mit Excel 3

 in Formeln und Funktionen / Statistik  Stichworte: SCHÄTZER Von:
Mit linearen Trends (vorsichtig) in die Zukunft schauen
 

Wer möchte nicht manchmal gerne ein wenig in die Zukunft sehen können? Ein Blick in die Glaskugel oder den Kaffeesatz wären sicherlich mögliche Ansätze.

Aber Excel wäre nicht Excel, wenn es für die Hobby-Wahrsager unter uns nicht auch ein paar passende Funktionen dafür bieten könnte. Einen sehr einfachen Vertreter aus dem Bereich der statistischen Trendanalyse sehen wir uns heute an:

Die SCHÄTZER-Funktion.

Und so geht’s:

Weiterlesen »

Wie zählt man eindeutige Werte in Excel? 50

 in Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: Matrixformel / SUMME / ZÄHLENWENN Von:
Nur eindeutige Werte zählen klappt in Excel mit einem kleinen Trick.
 

Excel bietet allerhand Funktionen, um Werte in Tabellen zu zählen: ANZAHL, ANZAHL2, ZÄHLENWENN, ZÄHLENWENNS. Damit werden schon sehr viele Szenarien abgedeckt.

Was machst du aber, wenn in deiner Tabelle Werte mehrfach vorkommen, du aber jeden Wert nur einmal zählen möchtest? Eine vermeintlich triviale Aufgabe, für die es jedoch in Excel leider keine Standardfunktion gibt.

Im heutigen Artikel zeige ich dir, wie man es mit einem kleinen Trick doch hinbekommt.

Und so geht’s:

Weiterlesen »

Traue keiner (Excel-)Statistik…

 in Formeln und Funktionen / Statistik  Stichworte: CHIQU.INV.RE / CHIQU.TEST / MEDIAN / MITTELWERT / MODUS.EINF / STABW.N / T.INV / TTEST Von:
Statistik mit Excel: Möglichkeiten und Grenzen in der Tabellenkalkulation
 

… die du nicht selbst gefälscht hast. Bei dem Begriff „Statistik“ verspüren sicherlich einige Excel-Anwender eine leichte Gänsehaut. Offen gesagt gehört dieses Fachgebiet auch nicht gerade zu meinen Steckenpferden.

Daher kommt im heutigen Artikel wieder ein Gastautor zu Wort. Robert Grünwald wird uns alle ein wenig in die Untiefen der Statistik einführen und zeigen, wie Excel bei statistischen Fragestellungen behilflich sein kann – und wo die Grenzen des Programms liegen.

Vielen Dank an Robert für den nun folgenden Gastartikel.

Weiterlesen »

Ein kleiner Filter-Trick 5

 in Formeln und Funktionen / Logisch  Stichworte: ISTGERADE / ISTUNGERADE Von:
Mit einem kleinen Trick lassen sich gerade (oder ungerade) Zahlen filtern
 

Der Umgang mit Wertelisten erfordert in manchen Situationen den Einsatz von Filtern. Excel bietet da schon im Standard sehr viele Möglichkeiten.

Manchmal reicht das jedoch nicht aus.

Im heutigen Beispiel wollen wir eine Liste nur nach geraden (oder ungeraden) Zahlen filtern.

Hört sich einfach an – ist es auch. Allerdings erst auf den zweiten Blick…

Und so geht’s:

Weiterlesen »

Finde einen Schnittpunkt in einer Excel-Tabelle 15

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: INDIREKT Von:
Eine verblüffende Methode, um den Wert an einem beliebigen Schnittpunkt einer Tabelle zu ermitteln!
 

Nehmen wir folgendes Szenario an:
Du bist Analyst, Controller oder sonst jemand, der häufig mit Excel zu tun hat und arbeitest an einer umfangreichen Umsatzübersicht, in der Umsätze nach Verkaufsregion und Produkt aufgeschlüsselt sind.

Nun willst deinem Chef die Bedienung besonders leicht machen (Chefs wollen ja immer alles schön einfach) und überlegst dir dazu, dass er über zwei einfache Auswahlfelder für die Region und das Produkt ganz bequem den dazugehörigen Wert aus der riesigen Tabelle angezeigt bekommen soll.

Vereinfacht ausgedrückt also den entsprechenden Schnittpunkt einer bestimmten Zeile und einer bestimmten Spalte.

Nur: Mit welcher Excel-Formel ermittelt man diesen Schnittpunkt?

Die Lösung, die ich dir heute vorstelle, wird dich mit Sicherheit verblüffen!

Weiterlesen »

Runden für Fortgeschrittene 1

 in Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: ABRUNDEN / AUFRUNDEN / OBERGRENZE / RUNDEN / UNTERGRENZE Von:
Zwei kaum bekannte Rundungsfunktionen können sehr praktisch sein!
 

Im heutigen Artikel stelle ich zwei wenig bekannte, aber nichtsdestotrotz sehr praktische Excel-Funktionen vor.

Es geht um spezielle Rundungsfunktionen, die hervorragende Dienste leisten, wenn es darum geht, auf ein Vielfaches einer Zahl auf- oder abzurunden.

Die leider etwas irreführenden Namen dieser Funktionen:

  • UNTERGRENZE
  • OBERGRENZE

Wie sie funktionieren und wofür man sie in der Praxis brauchen kann, zeige ich dir hier:

Weiterlesen »

Der erste Tag einer Kalenderwoche 57

 in Datum und Uhrzeit / Formeln und Funktionen  Stichworte: DATUM / KALENDERWOCHE / WOCHENTAG Von:
Eine trivial erscheinende Frage erweist sich als harte Nuss!
 

Die Kalenderwoche ist gerade im Geschäftsbereich eine gängige Zeitangabe. Vor allem bei Projekten arbeitet man häufig mit Kalenderwochen, um den Beginn oder die Dauer von Projektschritten zu definieren.

Excel bietet hier mit der KALENDERWOCHE-Funktion ein praktisches Mittel, um aus einem Datum die zugehörige Kalenderwoche zu ermitteln. Was mache ich aber für den umgekehrten Fall: Wenn ich nur eine Kalenderwoche gegeben habe und dazu z.B. das Datum des ersten Tages wissen möchte?

Kann doch gar nicht so schwer sein, habe ich mir gedacht. Bis mir nach einiger Zeit ziemlich der Kopf geraucht hat, denn die Tücke steckt hier im Detail.

Schließlich habe ich doch noch eine Lösung gefunden, welche sogar die in unseren Breiten übliche ISO-Kalenderwochenregelung berücksichtigt.

Und so geht’s:

Weiterlesen »

„Magische“ Summenfunktion 11

 in Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: SUMME Von:
Ein Trick in der SUMME-Funktion kann viel Arbeit ersparen!
 

Beim Tauchen durch die Tiefen des Internets kann man auch auf wahre Excel-Perlen stoßen. Eine dieser Perlen fand ich bei einem Excel-Blogger aus Hong Kong.

Ming Fung Wong, so der Name des Bloggers, hat eine fast „magische“ Anwendung der bekannten SUMME-Funktion beschrieben, die mir völlig neu war und die ich auch dir nicht vorenthalten möchte.

Wer Arbeitsmappen nutzt, die viele einzelne Arbeitsblätter enthalten, wird diese Funktion zukünftig nicht mehr missen wollen.

Und so geht’s:

Weiterlesen »

Finanzmathematik Teil 3: Wie lange dauert’s noch?

 in Finanzmathematik / Formeln und Funktionen  Stichworte: ZZR Von:
Mit der ZZR-Funktion berechnet man die Laufzeit bei Zins-, Renten- oder Tilgungsrechnungen
 

Nachdem wir in den beiden letzten Teilen gesehen haben, wie man die Höhe von Ratenzahlungen oder das benötigte Start- oder Zielkapital berechnen kann, geht es diesmal um die Laufzeit.

Mit der heutigen Excel-Funktion können wir endlich berechnen, wie lange etwas dauert. Zum Beispiel

  • bis ein Kredit zurückbezahlt ist
  • bis ein bestimmtes Sparziel erreicht ist
  • bis ein vorhandenes Vermögen verbraucht ist

Die Antworten auf diese und ähnliche Fragen liefert die ZZR-Funktion (ZZR = Zahlungszeiträume). Und so geht’s:

Weiterlesen »

Finanzmathematik Teil 2: Barwertberechnung 5

 in Finanzmathematik / Formeln und Funktionen  Stichworte: BW Von:
Mit der BW-Funktion den heutigen Wert einer Investition berechnen
 

Letzte Woche hatte ich mit der RMZ-Funktion gezeigt, wie hoch die regelmäßigen Zahlungen sein müssen, um ein bestimmtes Ziel zu erreichen (z.B. Darlehen abbezahlen, Kapital ansparen etc).

Heute stelle ich eine verwandte Excel-Funktion aus der Finanzmathematik vor, die etwas anders gelagerte Fragestellungen beantworten soll:

  • Wie hoch muss mein Vermögen sein, um für eine bestimmte Zeit eine fixe Rente bezahlt zu bekommen
  • Wie hoch könnte ein Darlehen sein, wenn ich einen bestimmten monatlichen Betrag zur Rückzahlung aufbringen kann
  • Welchem heutigen Betrag entspricht eine zukünftige Investition bei einer gegebenen Verzinsung

usw.

Die Antworten auf diese und ähnliche Fragen liefert die Barwert-Funktion. Und so geht’s:

Weiterlesen »

RMZ: Finanzmathematik leicht gemacht 3

 in Finanzmathematik / Formeln und Funktionen  Stichworte: RMZ Von:
Mit der RMZ-Funktion lassen sich ganz leicht regelmäßige Zahlungen unter Berücksichtigung von Zinsen berechnen.
 

Zinsen, Laufzeiten, Annuitäten, Barwert: Diese Begriffe sind den meisten Nicht-Bankern ein Graus.

Aber wäre es nicht schön, wenn man seinem Kreditberater oder Versicherungsvertreter ein wenig auf die Finger klopfen könnte?

Excel wäre nicht Excel, wenn es nicht ein paar schöne Funktionen hätte, die (nicht nur) dem Finanzmathematik-Laien hierbei ein wenig unter die Arme greifen.

Heute stelle ich die RMZ-Funktion vor. Wie sie dir im Zusammenhang mit Krediten oder beim Sparen helfen kann, zeige ich in diesem Artikel.

Und so geht’s:

Weiterlesen »

Praxisfall: Bedingte Formatierung im Entfernungsrechner 6

 in Formatierung und Layout / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Bedingte Formatierung / BEREICH.VERSCHIEBEN / INDEX / SPALTEN / VERGLEICH / ZEILEN Von:
Die bedingte Formatierung erleichtert das Ablesen von Zahlen in langweiligen, großen Tabellen
 

Die „Bedingte Formatierung“ in Excel bietet unzählige Möglichkeiten, Tabelleninhalte dynamisch und abhängig von bestimmten Kriterien oder Ereignissen ansprechend aufzubereiten.

Heute zeige ich Dir am Beispiel eines Entfernungsrechners eine praktische Anwendung: Hier hilft die bedingte Formatierung beim Ablesen der Distanz zwischen zwei Städten aus einer ziemlich großen Matrix.

Dabei setzen wir eine ganze Reihe von Excel-Funktionen ein:

  • INDEX
  • BEREICH.VERSCHIEBEN
  • VERGLEICH
  • SPALTEN
  • ZEILEN

Und so geht’s:

Weiterlesen »