Thema: Formeln und Funktionen

Eine Dateiliste in Excel erzeugen 40

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: DATEIEN / INDEX / ZEILE Von:
Mit einer alten Excel-4-Makrofunktion eine Dateiliste erstellen
 

Manchmal benötigt man in Excel eine Liste aller Dateien eines bestimmten Verzeichnisses. Leider bietet Excel dafür keine eingebaute Funktion. Trotzdem lässt sich diese Aufgabe auf unterschiedlichen Wegen lösen, zum Beispiel per Makroprogrammierung oder mit Hilfe von Power Query („Daten abrufen und Transformieren“).

Im heutigen Artikel zeige ich dir jedoch einen anderen Weg, bei dem wir doch nur ein paar Formeln verwenden werden.

Und so geht’s:

Weiterlesen »

Wie ein SVERWEIS von hinten: Den letzten Wert finden 24

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: INDEX / VERGLEICH / ZÄHLENWENN Von:
SVERWEIS findet immer den ersten Wert. Aber wie findet man den letzten?
 

Jeder Excel-Anwender, der mit der SVERWEIS-Funktion vertraut ist, weiß, dass man damit immer das erste Ergebnis zu einem gesuchten Wert finden kann. Wenn jedoch das letzte Vorkommen gesucht wird, hilft der SVERWEIS nicht weiter. Hier muss man zu ein paar Tricks greifen.

Aber auch das ist kein großes Hexenwerk. In meiner hier vorgestellten Lösung werden wir ein paar andere Funktionen miteinander kombinieren und so das gewünschte Ergebnis erzielen.

Und so geht’s:

Weiterlesen »

Eine spezielle Verkettung von Zellen 36

 in Formeln und Funktionen / Nachschlagen und Verweisen / Text  Stichworte: Matrixformel / TEXTVERKETTEN Von:
Ein praktischer Anwendungsfall für das Zusammenfassen von mehreren Zellen
 

Der heutige Artikel ist einem Leser zu verdanken, der folgendes Problem hatte:

In einer Exceltabelle wird für jeden Mitarbeiter ein Kreuzchen gesetzt, wenn dieser Mitarbeiter am betreffenden Tag Urlaub hat. Du kennst vermutlich diese Art von Urlaubsübersichten.
Nun soll aber zusätzlich in einer Zusammenfassung eine Liste nur dieser genommenen Urlaubstage angezeigt werden.

Ich fand diese Aufgabenstellung sehr interessant und möchte dir meine Lösung im folgenden Beitrag zeigen.

Und so geht’s:

Weiterlesen »

Forecast-Diagramm mit Pfiff 8

 in Diagramme / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: INDEX / ISTZAHL / MAX / MIN / VERGLEICH Von:
Mit ein paar Tricks lässt sich ein schlichtes Liniendiagramm mit einer dynamischen Box aufpeppen.
 

Die Idee zum heutigen Artikel habe ich mir bei meinem geschätzten Excel-Kollegen Andreas Thehos „ausgeliehen“. Der hat auf den Excellent Days 2018 ein Umsatzdiagramm gezeigt, das er mit ein paar Tricks um einen dynamischen Kasten erweitert hat. Durch diesen coolen Effekt lassen sich die Istwerte von den Planwerten sehr eindrucksvoll abgrenzen.

Wie das aussieht und wie man so ein Diagramm erstellt, zeigt der folgende Beitrag.

Und so geht’s:

Weiterlesen »

„Liste“ und „Tabelle“. Was ist der Unterschied? 15

 in Formatierung und Layout / Formeln und Funktionen / Mathematik und Trigonometrie  Stichworte: Formatierte Tabelle / SUMMEWENN Von:
Im Sprachgebrauch oft synonym verwendet, gibt es zwischen Listen und Tabellen wesentliche Unterschiede, wie dieser Gastartikel zeigen wird.
 

Heute gibt es nach längerer Zeit wieder mal einen Gastbeitrag. Dirk Schneider stellt immer wieder fest, dass vielen Excel-Anwendern die Unterschiede zwischen Listen und Tabellen nicht klar sind. Daher möchte er in seinem Beitrag Licht ins Dunkel bringen.

Vielleicht gehörst du ja auch zu denjenigen, die nicht so genau wissen, was die Nachteile des einen und die Vorteile des anderen sind. Oder du hast entsprechende Kollegen, die damit Probleme haben. Dann ist dieser Artikel genau richtig.

Bühne frei für Dirk Schneider!

Weiterlesen »

Sortiert oder nicht sortiert? 5

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Matrixformel Von:
Zwei Möglichkeiten, wie man per Formel eine Liste auf ihre Sortierung prüft.
 

Der Umgang mit Listen gehört zum täglichen Brot des geplagten Excel-Anwenders. Das Programm bietet dafür auch viele Hilfsmittel: Filter, diverse Sortier- und Gruppierungsfunktionen etc.

Wie man eine Liste nur mit Hilfe von Formeln automatisch sortieren kann, habe ich schon vor längerer Zeit in einer dreiteiligen Artikelserie beschrieben (Teil 1, Teil 2 und Teil 3).

Was ist aber, wenn du vielleicht nur herausfinden möchtest, ob eine Liste überhaupt sortiert ist oder nicht? Auch das lässt sich mit Hilfe von ein paar kleinen Formeln einfach feststellen.

Und so geht’s:

Weiterlesen »

Eine Frage der HÄUFIGKEIT 6

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: HÄUFIGKEIT Von:
Eine weniger bekannte Funktion hilft bei der Berechnung von Intervallen und Klassen
 

Auch wenn du mit Statistik eher auf Kriegsfuß stehst (so wie ich), könnte die heute vorgestellte Funktion für dich interessant sein: Es geht um die HÄUFIGKEIT.

Du bist Lehrer und möchtest die Ergebnisse der letzten Schulaufgabe zusammengefasst haben? Wieviele Einser, Zweier oder Dreier wurden geschrieben?
Du arbeitest in der Personalabteilung und möchtest die Verteilung der Mitarbeiter nach Altersklassen bestimmen?
Du bist Vertriebscontroller und möchtest kalkulieren, in welchem Preissegment ein Produkt am häufigsten verkauft wurde?

Dann solltest du diese Funktion kennen!

Und so geht’s:

Weiterlesen »

VERGLEICH-Funktion mit Jokersuche 6

 in Formeln und Funktionen  Stichworte: VERGLEICH Von:
Ein weniger bekanntes Feature macht die VERGLEICH-Funktion noch flexibler in der Suche
 

Über die VERGLEICH-Funktion habe ich ja schon allerhand geschrieben. Mit ihr lässt sich die Position eines gesuchten Wertes in einer Liste ermitteln, und in Kombination mit der INDEX-Funktion steckt sie jeden SVERWEIS locker in die Tasche.

Ein etwas weniger bekanntes Feature sorgt sogar für noch mehr Flexibilität: Man kann unter bestimmten Voraussetzungen nämlich auch Joker-Zeichen bei der Suche verwenden.

Und so geht’s:

Weiterlesen »

SVERWEIS mit Raketenantrieb 38

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: SVERWEIS Von:
Dem lahmen SVERWEIS werden mit einem Trick Beine gemacht!
 

Der SVERWEIS gehört sicherlich zu den beliebtesten Excel-Funktionen. Vermutlich kannst du dich noch daran erinnern, wie stolz du warst, als du zum ersten mal durchstiegen hast, wie diese Funktion anzuwenden ist und welche tollen Dinge man damit anstellen kann – wie zum Beispiel das Abgleichen von zwei Listen gegeneinander.

Allerdings hat der SVERWEIS auch verschiedene Nachteile. Wenn er bei großen Listen mit mehreren 1000 Datensätzen zum Einsatz kommt, dann kann sich das Ganze zu einem ziemlichen Geduldsspiel entwickeln. Mit anderen Worten: Der SVERWEIS kann eine ziemlich lahme Krücke sein.

Allerdings gibt es einen Trick, wie man diese ansonsten sehr praktische Funktion schneller macht. Und damit meine ich RICHTIG schneller!

Und so geht der SVERWEIS mit Raketenantrieb:

Weiterlesen »

Das Dreamteam INDEX+VERGLEICH aufgebohrt (Teil 2) 35

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: INDEX / VERGLEICH Von:
INDEX + VERGLEICH nochmal aufgebohrt: Ein kleiner Trick ermöglicht mehrere Suchkriterien.
 

Der erste Beitrag dieses kleinen Zweiteilers endete mit einem Cliffhanger. Ich habe dir zuerst den Mund wässrig gemacht und dich dann auf den zweiten Teil vertröstet. Nicht séhr schön…
Auf der anderen Seite, so funktionieren eben echte Thriller. Was Dan Brown und Co können, klappt auch mit Excel 😉

Genug der Vorrede. Hier kommt die Auflösung zu der Frage, wie sich mehrere Suchkriterien in einer INDEX-VERGLEICH-Formel verarbeiten lassen.

Weiterlesen »

Das Dreamteam aufgebohrt: INDEX + VERGLEICH mit mehreren Kriterien 60

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: INDEX / VERGLEICH Von:
SVERWEIS war gestern: INDEX + VERGLEICH bieten maximale Flexibilität
 

Dass die Kombination der beiden Funktionen INDEX und VERGLEICH einem SVERWEIS um Längen voraus ist, habe ich vor langer Zeit schon in diesem Artikel beschrieben.

Wenn du bisher um diese zwei Funktionen einen großen Bogen gemacht oder sie vielleicht noch gar nicht gekannt hast, empfehle ich dir, dich ein wenig mit ihnen zu beschäftigen. Es lohnt sich wirklich, denn zusammen sind sie fast unschlagbar!

Einen kniffeligen Anwendungsfall dafür zeige ich dir in den nächsten beiden Artikel. Es geht um die folgende Aufgabe:

Für einen kleinen Report sollen die benötigten Daten aus einer großen Rohdatentabelle per Formel ausgelesen werden. Die Schwierigkeit dabei ist, dass die jeder auszulesende Wert vier verschiedene Kriterien erfüllen muss.

Das war ist dir noch zu abstrakt? Die folgenden Bilder machen es deutlicher.

Weiterlesen »

Einen dynamischen Datenbereich ausgeben 32

 in Formeln und Funktionen  Stichworte: INDEX / VERGLEICH Von:
Durch geschickten Formeleinsatz lassen sich Datenbereiche dynamisch ausgeben
 

Beim Umgang mit größeren Datenmengen stellt sich oft das Problem, dass man nur einen bestimmten Teil der Daten für eine Auswertung benötigt. Excel bietet dazu verschiedene praktische Hilfsmittel, wie z.B. die Filterfunktionen.

Wenn die Quelltabelle Datumswerte enthält und ein bestimmter Datumsbereich dynamisch an einer anderen Stelle ausgegeben werden soll, muss man jedoch auf andere Lösungen zurückgreifen. Eine davon stelle ich heute vor.

Und so geht’s:

Weiterlesen »

Alle Wege führen nach Rom… 14

 in Formeln und Funktionen / Logisch / Nachschlagen und Verweisen  Stichworte: ERSTERWERT / INDEX / SVERWEIS / WAHL / WENN / WENNS Von:
Sechs unterschiedliche Lösungen für ein Problem: In Excel führen viele Wege zum Ziel.
 

Wie so oft im Leben trifft der Spruch „Alle Wege führen nach Rom“ in ganz besonderer Weise auch auf Excel zu. Bei den allermeisten Problemen führt nämlich nicht nur ein Weg zur Lösung. Der Fantasie und Kreativität des Anwenders sind dabei nur selten Grenzen gesetzt.

Heute will ich anhand eines einfachen Beispiels demonstrieren, wie eine Lösung auf sechs unterschiedlichen Wegen erreicht werden kann. Dabei gibt es nicht „Richtig“ oder „Falsch“. Dieses Beispiel soll dich dazu inspirieren, auch einmal etwas um die Ecke zu denken, gewohntes Terrain zu verlassen und die für dich geeignete Variante zu finden.

Und los geht’s:

Weiterlesen »

Den Rang innerhalb einer Gruppe berechnen 22

 in Formeln und Funktionen / Statistik  Stichworte: RANG.GLEICH / SUMMENPRODUKT Von:
Wenn die normale RANG-Funktion nicht weiterhilft: Rang-Berechnung innerhalb von Gruppen oder Kategorien.
 

Vor geraumer Zeit habe ich gezeigt, wie man in einer Werteliste einfache Rangfolgen berechnet.

Einige Leser wollten nun wissen, wie man so etwas macht, wenn die Liste nicht „flach“ ist, sondern Gruppierungsmerkmale enthält. In Umsatzlisten könnten das Regionen sein, im Sport Altersklassen, Geschlecht oder ähnliches.

Der heutige Artikel zeigt eine Möglichkeit, Rangfolgen innerhalb solcher Kategorien zu berechnen.

Und so geht’s:

Weiterlesen »

Excel-Funktionen von A bis Z

 in Formeln und Funktionen  Stichworte: Funktionsübersicht Von:
Eine Übersicht aller Excel-Funktionen in Deutsch und Englisch inkl. Verfügbarkeit in den verschiedenen Excel-Versionen
 

In vielen Unternehmen sind aus unterschiedlichsten Gründen nicht überall die gleichen Excel-Versionen im Einsatz. Da kann es sehr schnell passieren, dass eine in der eigenen Arbeitsmappe verwendete Funktion bei einem Kollegen in der Nachbararbteilung leider nur zu einer kryptischen Fehlermeldung führt, da er mit einer älteren Excel-Version arbeitet.

Damit dies zukünftig nicht mehr passiert, habe ich mir etwas Besonderes überlegt und eine vollständige Übersicht aller eingebauten Excel-Funktionen und deren Verfügbarkeit in den verschiedenen Excel-Versionen erstellt. Und nicht nur das: Die Übersicht liefert neben den deutschen Funktionsnamen auch gleich noch die englische Variante mit, für den Fall, dass auf deinem Computer Excel in der englischsprachigen Version installiert ist.

Und das bietet meine Übersicht:

  • Mit deutschen und englischen Funktionsnamen
  • Angabe der Verfügbarkeit in den verschiedenen Excel-Versionen
  • Verlinkung über den Funktionsnamen zu bereits veröffentlichten Artikeln hier auf dem Blog
  • Freie Sortierung der Tabelle über die kleinen Pfeilsymbole neben dem Spaltennamen
  • Freitextsuche und Filter

Und hier geht’s zur Funktionsübersicht…

Weiterlesen »

Die Matrix Reloaded. Oder in Excel: {STRG+Umschalt+Eingabe} 14

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Matrixformel / MODALWERT / ZÄHLENWENN Von:
Noch mehr Tipps zu Array-Formeln in Excel
 

Ich geb’s ja gerne zu: Mir waren diese gruseligen Array-Formeln mit den geschweiften Klammern selbst lange Zeit unheimlich. Wenn man eine Formel schon mit dieser komischen Tastenkombination abschließen muss, dann kann doch etws nicht stimmen.

Sie sind anfangs schwer zu durchschauen und es bedarf einiger geistiger Kopfstände, bis man den Zugang findet. Aber ich kann dir versprechen, es lohnt sich. Heute geht’s weiter mit ein paar praktischen Anwendungsbeispielen:

  • Quersumme berechnen
  • Listen mit Fehlerwerten summieren
  • Liste auf Dubletten überprüfen
  • Liste per Formel alphabetisch sortieren

Und los geht’s:

Weiterlesen »