Excel beißt nicht!

Thema: Formeln und Funktionen

Etwas angestaubt, aber sehr vielseitig: DBSUMME 21

27.09.2022 in Datenbank / Formeln und Funktionen Stichworte: DBSUMME Von: Martin Weiß

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:

Summe mit mehreren Bedingungen innerhalb eines Felds 20

09.08.2022 in Formeln und Funktionen Stichworte: SUMMENPRODUKT / SUMMEWENNS Von: Martin Weiß

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:

Ein kleines Geheimnis in der INDEX-Funktion 22

12.07.2022 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: INDEX / VERGLEICH Von: Martin Weiß

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:

Suche über mehrere Spalten und Zeilen 6

22.03.2022 in Formeln und Funktionen Stichworte: AGGREGAT / INDEX / KKLEINSTE / SPALTE / ZEILE Von: Martin Weiß

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:

Excel für Glücksritter 7

22.02.2022 in Formeln und Funktionen / Statistik Stichworte: Kamera / ZUFALLSBEREICH Von: Martin Weiß

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.

Den XVERWEIS ausreizen 35

08.02.2022 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: XVERWEIS Von: Martin Weiß

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:

Viele Funktionen in einer: AGGREGAT 33

11.01.2022 in Formeln und Funktionen / Mathematik und Trigonometrie Stichworte: AGGREGAT / KKLEINSTE Von: Martin Weiß

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..

Liste nur mit eindeutigen Werten erstellen 125

26.10.2021 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: Dubletten / Duplikate / EINDEUTIG / FILTER / INDEX / VERGLEICH / ZÄHLENWENN Von: Martin Weiß

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:

Adressenproblem: Straßennamen von der Hausnummer trennen 14

22.06.2021 in Formeln und Funktionen Stichworte: FINDEN / LÄNGE / LINKS / Matrixformel / MIN / RECHTS Von: Martin Weiß

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!

2 Wochen mehr Urlaub mit Excel? 10

08.06.2021 in Datum und Uhrzeit / Formeln und Funktionen Stichworte: KALENDERWOCHE Von: Martin Weiß

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…

EINDEUTIG mal etwas anderes 16

11.05.2021 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: ANZAHL2 / EINDEUTIG / SORTIEREN Von: Martin Weiß

Eine dynamische Array-Funktion in Excel 365 bietet interessante Möglichkeiten

Die Excel-Funktion, die ich heute vorstelle, gehört zur Kategorie der dynamischen Array-Funktionen, welche mit Microsoft 365 / Office 365 neu eingeführt wurden: EINDEUTIG.

Wie der Name schon vermuten lässt, kann man damit eindeutige Werte ermitteln. Hört sich erst einmal unspektakulär an, aber die Funktion hat es in sich und bietet praktische Möglichkeiten, die bisher nur umständlich mit komplizierten Formeln umsetzbar waren – oder eben überhaupt nicht.

Wofür man die EINDEUTIG-Funktion in der Praxis nutzen kann, zeigt der heutige Artikel.

Und so geht’s:

Filter-Ausgabe für nicht zusammenhängende Spalten 25

13.04.2021 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: FILTER / INDEX / SEQUENZ Von: Martin Weiß

Mit einem Trick können bei der FILTER-Funktion beliebige Spalten ausgegeben werden.

Wenn du Excel aus Microsoft 365/Office 365 im Einsatz hast, dann kennst du vielleicht die dort verfügbare FILTER-Funktion. Falls nicht, empfehle ich dir, mal einen Blick in diesen Artikel zu werfen: https://www.tabellenexperte.de/eine-funktion-liefert-alle-treffer/ .

Denn damit kann man eine Tabelle sehr bequem nach einem beliebigen Kriterium filtern und die gefilterten Ergebnisse an anderer Stelle ausgeben lassen. Allerdings wirft die FILTER-Funktion immer sämtliche Spalten der Quelltabelle aus oder zumindest nur einen zusammenhängenden Spaltenbereich.

Was aber ist, wenn ich vielleicht nur ganz bestimmte Spalten in meiner Ausgabeliste benötige, die eben keinem zusammenhängenden Bereich entstammen?

Wie das geht, zeigt der heutige Artikel:

Bildauswahl per Dropdown-Liste

09.03.2021 in Formeln und Funktionen / Informationen / Nachschlagen und Verweisen Stichworte: Datenüberprüfung / INDEX / VERGLEICH Von: Martin Weiß

Sehr cool: Angezeigte Bilder dynamisch per Dropdown-Liste verändern!

Dropdown-Listen sind in Excel sehr beliebt, zumal sie das Leben des Anwenders sehr vereinfachen.

Heute zeige ich dir in diesem Zusammenhang einen Trick, mit dem Du ziemlich sicher deine Kollegen (oder deinen Chef) verblüffen wirst.

Wie lassen sich Bilder dynamisch verändern, abhängig von dem Wert, der aus einer Dropdown-Liste ausgewählt wird?

So geht unser kleiner Bildertrick:

Die neue FILTER-Funktion mit Jokersuche 17

12.01.2021 in Formeln und Funktionen Stichworte: FILTER / ISTZAHL / SUCHEN Von: Martin Weiß

Trio mit vier Fäusten: Die FILTER-Funktion wird verstärkt durch zwei alte Bekannte

Excel aus Microsoft 365 (ehemals Office 365) bietet mit der FILTER-Funktion ein ungemein praktisches und einfaches Werkzeug, um aus einem Datenbestand einen bestimmten Extrakt herauszufiltern – und das Ganze auch noch komplett dynamisch.

So genial und einfach sie auch ist, die FILTER-Funktion hat dennoch eine kleine Schwäche: Sie kann nur exakte Treffer liefern. Wenn man nur nach einem Teil eines Textes sucht, scheitert sie leider.

Wie man die Funktion trotzdem dazu bringen kann, auch Textfragmente zu akzeptieren, zeigt der heutige Artikel.

Wie SVERWEIS, nur richtig! 14

22.12.2020 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: IDENTISCH / INDEX / VERGLEICH Von: Martin Weiß

SVERWEIS berücksichtigt keine Groß- und Kleinschreibung. Die hier vorgestellte Alternative schon!

Vor ein paar Wochen erreichte mich eine Leseranfrage zu einem interessanten Excel-Problem, dessen Existenz mir bis dahin gar nicht bewusst war (danke an dieser Stelle an Wilfried B.!)

Es ging darum, zu einer Abkürzung den dazu passenden Langtext aus einer Referenztabelle auszugeben. Das hört sich erst einmal nach einer klassischen SVERWEIS-Aufgabe an. Und jetzt kommt das große Aber….

Bei den Abkürzungen kommt es auf die exakte Schreibweise an. Das heißt, es muss zwischen Groß- und Kleinschreibung unterschieden werden. Und wie ich dann feststellen musste, kann der SVERWEIS damit leider nicht dienen.

Welche Formellösung dann doch zum gewünschten Ergebnis führt, das zeige ich dir in diesem Artikel.

Und so geht’s:

Alles nur Zufall, oder was? 4

27.07.2020 in Formeln und Funktionen / Statistik Stichworte: ZUFALLSBEREICH / ZUFALLSMATRIX / ZUFALLSZAHL Von: Martin Weiß

Eine neue Zufallsfunktion in Microsoft-365 bietet interessante Möglichkeiten

Es hängt ja bekanntlich alles Mögliche vom Zufall ab:

Der lang ersehnte Lottogewinn
Das Geschlecht seines neugeborenen Kindes
Die Rendite einer Investition auf dem Aktienmarkt
Ob bei Bauarbeiten mal wieder eine Weltkriegsbombe gefunden wird oder das millionste Keltengrab

Die Liste lässt sich endlos fortsetzen, aber eines ist klar: Der Zufall bestimmt unser Leben in nicht unerheblichem Maß. Wie gut, dass uns auch Excel in dieser Hinsicht nicht allein lässt 🙂

Im heutigen Artikel stelle ich eine ganz neue und durchaus praktische Excel-Funktion vor, mit der sich die bisher schon möglichen Zufallsberechnungen noch optimieren lassen.

Und so geht’s: