Thema: Formeln und Funktionen


Wie transponiert man mehrzeilige Datensätze in ein vernünftiges Tabellenformat? 36

Mehrzeilige Datensätze in einzeilige umwandeln: Drei mögliche Lösungen
 

Die Verarbeitung von aus externen Quellen stammenden Daten ist ein häufiger Anwendungsfall von Excel. Manchmal liegen diese Daten aber in einem unbrauchbaren oder zumindest unhandlichen Format vor.

Wer schon einmal mit Adresslisten (oder Artikellisten) zu tun hatte, bei denen jedes Datenfeld in einer neuen Zeile stand, weiß wovon ich spreche.

Wie man solche mehrzeiligen Datensätze in ein einzeiliges Tabellenformat verwandeln kann, zeigt der heutige Artikel.

Und so geht’s:

Weiterlesen »

XVERWEIS für Groß- und Kleinschreibung 6

"A" ist nicht gleich "a". Oder: Wie man Schreibweisen beim Einsatz von XVERWEIS berücksichtigen kann
 

Manche vermeintlichen Gewissheiten stellen sich irgendwann als Irrtum heraus. Dazu gehört beispielsweise auch die Annahme, dass Verweisfunktionen wie XVERWEIS schon immer die richtigen Ergebnisse liefern werden.

In den allermeisten Fällen trifft das ja auch zu – korrekte Anwendung mal vorausgesetzt. Wenn aber bei der Suche nach Groß- und Kleinschreibung unterschieden werden muss, dann sieht es ziemlich düster aus. Die Schreibweise wird vom XVERWEIS nämlich einfach ignoriert.

Wie man doch noch zu einer richtigen Lösung kommen kann, wenn es auf korrekte Schreibweisen ankommt, zeigt dieser Artikel.

Weiterlesen »

Irren ist (nicht nur) menschlich. Oder: Fehlerbehandlung in Excel 19

So werden Fehlerwerte in Excel-Funktionen gezielt abgefangen und sinnvoll behandelt.
 

Welcher Excel-Anwender ist nicht schon mal auf eine Formel gestoßen, die nur ein unschönes „#NV“, „DIV/0!“ oder „#WERT!“ anzeigt.

Es hat natürlich grundsätzlich schon seine Richtigkeit, wenn eine Formel aufgrund von falschen oder fehlenden Eingaben kein vernünftiges Ergebnis liefern kann und stattdessen eine dieser Fehlermeldungen ausgibt. Aber es sieht eben nicht besonders schön aus und verwirrt weniger versierte Anwender unnötig.

Noch problematischer ist es, wenn man beispielsweise mit einer SUMME-Funktion auf einen Datenbereich zugreift, der solche Fehlerwerte enthält. Denn dann liefert auch die SUMME (oder andere Formeln) wieder nur einen Fehler.

Excel bietet verschiedene Funktionen zur Fehlerbehandlung an, von denen ich an dieser Stelle die folgenden vorstellen möchte:

  • ISTFEHLER()
  • WENNFEHLER
  • FEHLER.TYP()

Und so geht’s:

Weiterlesen »

Texte zerlegen leichtgemacht 32

Eine bisher umständliche Aufgabe wird dank neuer Tabellenfunktionen endlich einfach!
 

Kürzlich hatte ich ein paar der 14 neuen Tabellenfunktionen vorgestellt, die seit August 2022 in Excel aus Microsoft 365 verfügbar sind. Heute gehts mit drei weiteren sehr hilfreichen Textfunktionen aus dieser Serie weiter:

  • TEXTVOR
  • TEXTNACH
  • TEXTTEILEN

Wie sie funktionieren und was man damit anstellen kann, erfährst du in diesem Beitrag.

Weiterlesen »

Excel, der Verwandlungskünstler: Einheiten umwandeln 14

Mit der UMWANDELN-Funktion kannst du eine Vielzahl von Maßeinheiten in eine andere Maßeinheit umrechnen.
 

Excel kommt ja für die unterschiedlichsten Anwendungsfälle zum Einsatz. Sehr oft geht es um irgendwelche Verkaufsergebnisse und Umsätze, Ausgaben, Einnahmen, Kosten etc.
Geld eben.

Aber vielleicht benutzt du Excel ja dazu, um Temperaturen, Entfernungen, Geschwindigkeiten oder Gewichte aufzuzeichnen oder damit zu rechnen.

Dann könnte dieser Artikel höchstwahrscheinlich interessant für dich werden. Denn es gibt eine sehr praktische Excel-Funktion, mit der man eine unglaubliche Vielzahl an Maßeinheiten in andere Maßeinheiten umrechnen kann: Kilometer in Meilen, Celsius in Fahrenheit, Gallonen in Liter und vieles mehr.

Darf ich vorstellen: Die UMWANDELN-Funktion!

Und so geht’s:

Weiterlesen »

Excel für (Hoch)stapler 10

Brandneue Textfunktionen bringen große Vereinfachungen mit sich
 

Nachdem ich im letzten Artikel mit DBSUMME eine schon lange verfügbare, aber eher unbekannte Tabellenfunktion vorgestellt habe, gibt es heute wieder mal etwas ziemlich Aktuelles. Etwa seit Mitte August 2022 gibt es in Excel aus Microsoft 365 eine ganze Reihe neuer und wirklich nützlicher Funktionen (14 Stück, um genau zu sein), von denen ich dir heute zwei vorstellen möchte:

  • HSTAPELN
  • VSTAPELN

Es geht also um das Stapeln 🙂

Was man damit genau anfangen kann, das zeige dir im folgenden Beitrag.

Weiterlesen »

Etwas angestaubt, aber sehr vielseitig: DBSUMME 21

Eine eher unbekannte Funktion erweist sich als sehr flexibel bei der Bildung von bedingten Summen
 

Vor ein paar Wochen hatte ich beschrieben, wie man in einer Tabelle Summen bilden kann, bei denen unterschiedliche Kriterien innerhalb einer Spalte erfüllt sein müssen. In meiner Lösung habe ich dafür die SUMMENPRODUKT-Funktion verwendet (falls du den Artikel verpasst hast, kannst du ihn gerne hier nachlesen).

Danach hat mich der Leser Matthias Rack in den Kommentaren auf eine alternative Lösung hingewiesen, vielen Dank nochmal für die Anregung! Diesen Hinweis nehme ich heute zum Anlass, um dir eine etwas in die Jahre gekommene, aber trotzdem nützliche und sehr flexible Funktion vorzustellen: DBSUMME

Und so geht’s:

Weiterlesen »

Summe mit mehreren Bedingungen innerhalb eines Felds 20

Eine scheinbar gewöhnliche Aufgabe erfordert doch eine ungewöhnliche Lösung.
 

In Excel ist es manchmal so, wie im richtigen Leben. Etwas sieht ganz einfach aus, stellt sich bei näherer Betrachtung aber also doch nicht so trivial heraus. Der vermeintlich naheliegende Weg kann der falsche sein.

Die gute Nachricht: Meistens gibt es auch für nicht triviale Fragen in Excel ein Lösung.

So auch im heutigen Beispiel, wenn bei der Summierung mehrere Bedingungen innerhalb einer Spalte erfüllt sein müssen.

Und so geht’s:

Weiterlesen »

Ein kleines Geheimnis in der INDEX-Funktion 22

In der INDEX-Funktion schlummert mehr, als vielen Anwendern bekannt ist
 

Kürzlich hatte ich die Ehre und das Vergnügen, auf den Excel-Kompetenztagen in Fulda als Trainer aufzutreten (eine Veranstaltung, die ich jedem ambitionierten Excel-Anwender nur empfehlen kann). Im Kurs über die Verweisfunktionen war unter anderem die INDEX-Funktion ein Thema. Versierte Excel-Anwender nutzen diese Funktion in Kombination mit VERGLEICH gerne als den besseren SVERWEIS.

Deutlich weniger bekannt ist, dass INDEX in zwei Versionen auftritt. Als Matrixversion (das ist die „übliche“ Variante) und als Bezugsversion (das ist die eher unbekannte Variante). Letztere ermöglicht es, mit nur einer Formel gleich mehrere Bereiche auswerten zu können.

Wozu man das brauchen kann und wie das funktioniert, kannst du in diesem Artikel lesen.

Und so geht’s:

Weiterlesen »

Suche über mehrere Spalten und Zeilen 6

Ein weiteres Einsatzgebiet für die Mauerblümchen-Funktion AGGREGAT
 

Manchmal steht man in Excel vor einer Aufgabe und denkt sich: Das ist doch ganze einfach! Aber auf den zweiten Blick stellt man fest: Mist, doch nicht so einfach…

Hast du zum Beispiel schon einmal versucht, nur mit Hilfe von Formeln einen bestimmten Wert in einer Tabelle zu finden? Also eine Suche, die sich sowohl über mehrere Spalten als auch über mehrere Zeilen erstreckt? Klingt eigentlich ganz einfach, oder?

Dann lass dich mal überraschen. Denn die Lösung führt über eine Funktion, die völlig zu Unrecht ein ziemliches Schattendasein fristet: AGGREGAT

Und so geht’s:

Weiterlesen »

Excel für Glücksritter 7

Mit ein paar kleinen Zufallsfunktionen und etwas Kreativität lässt sich Excel in einen Spielautomaten verwandeln
 

Du bist Excel-Anwender und bist einem gelegentlich Spielchen am Computer nicht abgeneigt? Dann habe ich heute eine sehr gute Nachricht für dich:

Ein Glückspielautomat (a.k.a. einarmiger Bandit) in Excel!

Dass Excel nicht nur für Liebhaber von trockenen Zahlenfriedhöfen geeignet ist, habe ich schon an verschiedenen Stellen gezeigt (guckst du hier, hier oder hier).

Der Blogleser Berhhard73 hat im vergangenen Dezember unter einem Artikel über Zufallsfunktionen die Frage gestellt:
Kann man diese Funktionen in Excel auch für eine Art einarmigen Banditen nutzen?

Ja, kann man!

Was dabei herausgekommen ist, kannst du in diesem Artikel nachlesen.

Weiterlesen »

Den XVERWEIS ausreizen 39

Der XVERWEIS an sich ist schon eine Bereicherung. Aber da geht noch deutlich mehr!
 

Wer Excel aus Microsoft 365/Office 365 oder das brandneue Excel 2021 einsetzt, hat vermutlich schon Bekanntschaft mit der XVERWEIS-Funktion gemacht oder zumindest davon gehört. Es lohnt sich unbedingt, sich mit dem Nachfolger des berühmt-berüchtigten SVERWEIS zu beschäftigen, bringt er doch wesentliche Verbesserungen mit sich.

Darüber hatte ich auch vor längerer Zeit schon im Artikel Goodbye SVERWEIS, willkommen XVERWEIS! geschrieben.

Wenn du also schon mit XVERWEIS gearbeitet hast, dann zeige ich dir heute mit ein paar Spezialfällen, wie man noch viel mehr herausholen kann. Oder du kommst auf den Geschmack, dich endlich mit dieser tollen Funktion zu beschäftigen und den SVERWEIS endgültig zu begraben.

Und so geht’s:

Weiterlesen »

Viele Funktionen in einer: AGGREGAT 33

Mit dem Multitalent AGGREGAT lassen sich in Excel viele andere Funktionen in einer verbesserten Variante ersetzen.
 

Heute stelle ich eine unglaublich mächtige, aber mindestens im gleichen Maße unbekannte/ignorierte Excel-Funktion vor.

Es geht dabei um eine Funktion, die viele einzelne (genau genommen 19!) eigenständige Excel-Funktionen in sich vereinigt. Und die dazu noch besser ist, als jede dieser einzelnen Funktionen für sich. Hört sich ziemlich gut an, oder?

Die Rede ist von der Funktion AGGREGAT.

Sehen wir uns diese „Über-Funktion“ einmal etwas näher an..

Weiterlesen »

Liste nur mit eindeutigen Werten erstellen 125

Mit Hilfe einer Matrixformel können aus einer Liste nur die eindeutigen Werte extrahiert werden (ohne Duplikate)
 

Vor einiger Zeit hatte ich in einem Artikel beschrieben, wie man in einer Liste mit Duplikaten die Anzahl der eindeutigen Werte bestimmt.

Der heutige Artikel geht einen Schritt weiter:
Ich möchte nicht nur wissen, wie viele eindeutigen Werte es gibt, sondern ich möchte diese gleich in einer eigenen Liste ausgeben. Also frei von Dubletten. Und zwar mit Hilfe von Excel-Formeln.

Und so geht’s:

Weiterlesen »

Adressenproblem: Straßennamen von der Hausnummer trennen 15

Ein kniffeliges Problem und eine mögliche Lösung dafür
 

Excel kommt oft zum Einsatz, wenn Adressenbestände für den Import in ein CRM- oder ERP-System aufbereitet werden müssen. Nun sehen manche Zielsysteme für den Straßennamen und die Hausnummer zwei getrennte Felder vor, in vielen Adressenlisten ist diese Aufteilung aber nicht gegeben. Was läge also näher, als die Straße in Excel in zwei Felder aufzuteilen.

Was sich zunächst einfach anhört, stellt sich jedoch schnell als ziemlich kniffelig heraus. Denn es gibt ja gefühlt unendlich viele Variationen, wie eine Hausnummer in einer Adressenangabe aussehen kann.

In diesem Artikel werde ich ein paar Formeln vorstellen, um zumindest einen Großteil der vielen Varianten in den Griff zu bekommen und vom Straßennamen zu trennen.

Mach‘ dich auf einiges gefasst!

Weiterlesen »

2 Wochen mehr Urlaub mit Excel? 10

Die KALENDERWOCHE-Funktion in Excel kann Dir zu bis zu zwei Wochen mehr Urlaub im Jahr verhelfen...
 

Excel bietet mit der Funktion KALENDERWOCHE die Möglichkeit, sich zu einem gegebenen Datum die dazugehörige Kalenderwoche ausgeben zu lassen.

Was sich auf den ersten Blick ganz praktisch anhört, könnte in der Praxis allerdings zu Verwirrung führen: Wer hat sich denn schon mal Gedanken gemacht, wann in einem Jahr eigentlich genau die KW 1 beginnt?

Ich jedenfalls lange Zeit nicht. Zumindest nicht, bis ich mich näher mit der KALENDERWOCHE-Funktion beschäftigt habe. Und dabei musste ich feststellen, dass KW 1 nicht immer KW 1 ist und dass der schlaue Excel-Anwender vielleicht plötzlich 2 Wochen mehr Urlaub im Jahr haben könnte…

Weiterlesen »