Thema: Formeln und Funktionen

Den Wert an einem Schnittpunkt finden (Variante 2) 3

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / VERGLEICH Von:
Eine weitere Möglichkeit, den Wert eines Schnittpunkts in einer Tabelle auszugeben.
 

Vor ziemlich genau einem Jahr habe ich eine Methode vorgestellt, um mit Hilfe der INDIREKT-Funktion und dem sogenannten Schnittmengenoperator den Wert auszugeben, der sich am Schnittpunkt einer bestimmten Spalte und einer bestimmten Zeile befindet.

Die damals gezeigte Lösung ist zwar sehr elegant, hat aber ein paar kleine Nachteile, so dass sie nicht für alle Szenarien immer auch praktikabel ist.

Heute zeige ich eine weitere Möglichkeit, das Problem zu lösen.

Und so geht’s:

Weiterlesen »

Der Zufall als Assistent 2

 in Formeln und Funktionen / Mathematik und Trigonometrie / Statistik  Stichworte: ANZAHL / AUFRUNDEN / MITTELWERT / SUMME / WURZEL / ZÄHLENWENN / ZUFALLSZAHL Von:
Ein kreativer Einsatz der Excel-Funktion ZUFALLSZAHL
 

Heute kommt wieder einmal ein Gastautor zu Wort. Martin Fuchs, seines Zeichens Analyst und Controller und somit ein Parade-Excel-Anwender, hat sich einige Gedanken zur Funktion ZUFALLSZAHL() gemacht.

Er zeigt, wie man sich mit dieser und ein paar anderer Excel-Funktionen der Kreiszahl Pi nähern kann. Das dabei zur Veranschaulichung erstellte Diagramm wird auch die mathematischen Abstinenzler unter uns auf jeden Fall ansprechen.

Vielen Dank an Martin Fuchs für diesen schönen Beitrag!

Und los geht’s:

Weiterlesen »

Wer hat das letzte Wort in Excel? 17

 in Formeln und Funktionen / Text  Stichworte: FINDEN / LÄNGE / RECHTS / WECHSELN Von:
In Excel das letzte Wort in einem Text finden: Eine nur auf den ersten Blick einfache Aufgabe...
 

Für die Arbeit mit Texten und Textteilen bietet Excel einige sehr praktische Funktionen. So lassen sich mit den Funktionen LINKS() oder RECHTS() gezielt bestimmte Teile aus einem Text herauslösen und anschließend getrennt weiterverarbeiten.

Wie kann man nun z.B. aus einem Satz variabler Länge gezielt das letzte Wort herauslösen?

In Kenntnis der RECHTS-Funktion eine auf den ersten Blick trivial erscheinende Aufgabe, die es bei näherer Betrachtung aber in sich hat. Wozu man das brauchen sollte? Um z.B. in einem kombinierten Namensfeld nur den Nachnamen zu isolieren.

Der folgende Artikel stellt zwei mögliche Lösungen vor.

Weiterlesen »

Excel-Tricks mit Steuerelementen (Teil 2) 23

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / Formulare / INDEX / Steuerelemente / VERGLEICH Von:
Im Teil 2 der Artikelserie geht es um das Kombinationsfeld aus den ActiveX-Steuerelementen.
 

In der letzten Woche habe ich hier eine Möglichkeit vorgestellt, mit nur einem einzigen Dropdown-Feld eine ganze Liste in Excel zu befüllen.

Für den Einstieg ganz nett, aber noch nicht so richtig komfortabel.

Heute greife ich das Thema nochmal auf und zeige Dir, wie sich mit wenig Aufwand noch deutlich mehr aus den Steuerfeldern herausholen lässt.

Und so geht’s:

Weiterlesen »

Excel-Tricks mit Steuerelementen (Teil 1) 34

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: ANZAHL2 / BEREICH.VERSCHIEBEN / Formulare / Steuerelemente Von:
Mit einem kleinen Trick lässt sich das Ergebnis eines Steuerelements flexibler ausgeben
 

Excel eignet sich hervorragend dafür, eigene Formulare zu gestalten. Über eine Vielzahl von vordefinierten Steuerelementen lassen sich Auswahllisten, Checkboxen, Optionsfelder und einiges mehr realisieren.

Üblicherweise wird dabei mit diesen Steuerelemente eine fixe Zelle verknüpft, in der das ausgewählte Ergebnis eingetragen wird.

Im heutigen Artikel zeige ich dir am Beispiel des Kombinationsfeldes (= Dropdown-Feld), wie man den Ausgabebereich für solche Steuerelemente ein wenig flexibler gestalten kann.

Und so geht’s:

Weiterlesen »

Dropdown-Liste Spezial 47

 in Daten- und Analysetools / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / Datenüberprüfung / Formatierte Tabelle / Pivot-Tabelle Von:
Mit ein paar kleinen Tricks ist die Pflege von Dropdown-Listen ein Kinderspiel.
 

Auswahllisten oder Dropdown-Listen sind in Excel extrem beliebt. Sie erleichtern dem Anwender die Dateneingabe, verhindern Fehler und lassen deine Tabelle einfach professionell erscheinen.

Es gibt nur ein kleines Ärgernis: Wenn neue Einträge in der Auswahlliste dazukommen sollen, ist meistens Handarbeit angesagt.

Heute zeige ich dir, wie man eine Dropdown-Liste erstellt, die

  • automatisch neue Einträge aufnimmt
  • alphabetisch sortiert ist
  • keine doppelten Einträge enthält

Und das alles (fast) vollautomatisch!

Und so geht’s:

Weiterlesen »

Besser als SVERWEIS: Alle Werte finden

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: INDEX / KGRÖSSTE / Matrixformel / WENNFEHLER / ZÄHLENWENN / ZEILE Von:
Was der SVERWEIS nicht kann: Alle Treffer zu einem Suchkriterium liefern. So geht's trotzdem.
 

Der SVERWEIS ist mit Sicherheit eine der wichtigsten Funktionen, die dem versierten Excel-Anwender zur Verfügung stehen und sie gehört natürlich auch zu meinen Favoriten.

Die SVERWEIS-Funktion liefert bekanntermaßen zu einem Suchkriterium in der einen Tabelle den ersten gefundenen Treffer aus einer anderen Tabelle zurück. Was macht man aber, wenn der gesuchte Wert mehrmals vorkommt und man gerne alle Ergebnisse angezeigt bekommen möchte?

Hier ist der SVERWEIS leider hoffnungslos überfordert. Wie man so etwas trotzdem hinbekommt, zeigt der heutige Artikel.

Weiterlesen »

Datumsberechnung Spezial (Teil 2) 11

 in Datum und Uhrzeit / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: INDEX / SVERWEIS / WENN / WOCHENTAG Von:
Wie berechnet man den zweiten Mittwoch, den dritten Freitag oder den vierten Montag eines Monats?
 

Nachdem ich vor zwei Wochen im ersten Teil dieser Kurzserie gezeigt habe, wie man den letzten bestimmten Wochentag (z.B. den letzten Montag) in einem Monat findet, werde ich heute die Flexibilität noch etwas erhöhen.

In diesem Artikel zeige ich, wie man bestimmt, auf welches Datum z.B. der erste Montag, der zweite Mittwoch oder der dritte Donnerstag eines Monats fällt.

Wenn du dich also in dem kommenden Jahr konsequent von jeder am zweiten Dienstag im Monat stattfindenden Abteilungsbesprechung abseilen willst, dann hilft dieser Beitrag vielleicht bei deiner Urlaubsplanung…

Weiterlesen »

Datumsberechnung Spezial (Teil 1) 12

 in Datum und Uhrzeit / Formeln und Funktionen  Stichworte: MONATSENDE / SVERWEIS / WENN / WOCHENTAG Von:
Wie man in Excel den letzten Montag (Dienstag, Mittwoch...) eines Monats berechnet.
 

Excel bietet standardmäßig schon eine ganze Menge an Datumsfunktionen. Trotzdem reichen die manchmal nicht aus. Denn was machst du, wenn du z.B. immer den letzten Montag eines Monats berechnen möchtest?

Eine etwas kniffelige Angelegenheit, für die es bestimmt unterschiedliche Lösungen gibt. Eine davon zeige ich dir im heutigen Artikel.

Und so geht’s:

Weiterlesen »

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 43

 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? 52

 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 »