Excel beißt nicht!

Thema: Formeln und Funktionen

Flexibler Zugriff auf Pivot-Tabellen 8

13.07.2020 in Formeln und Funktionen Stichworte: Pivot-Tabelle / PIVOTDATENZUORDNEN Von: Martin Weiß

Eine spezielle Funktion erlaubt den zielgerichteten Zugriff auf einzelne Werte in einer Pivot-Tabelle

Wenn du diesen Blog schon länger liest, weißt du, dass ich ein großer Fan von Pivot-Tabellen bin. Sie sind perfekt, um mit minimalen Aufwand größte Datenmengen auszuwerten und zu verdichten. In manchen Fällen braucht man jedoch nur einen einzelnen Wert aus einer Pivot-Tabelle, um diesen beispielsweise in einem Management-Dashboard darzustellen.

Wie man ganz gezielt, flexibel und zuverlässig auf einen bestimmten Datenpunkt zugreift, zeige ich dir in diesem Artikel.

Und so geht’s:

Liste der Dateiverknüpfungen 9

25.05.2020 in Formeln und Funktionen / Informationen Stichworte: VERKNÜPFTE.DATEIEN / Verknüpfung Von: Martin Weiß

Eine verblüffend einfache Methode, um eine Liste aller extern verknüpften Dateien zu erhalten

Du kennst vielleicht das Problem: Beim Öffnen einer Excel-Datei kommt der Hinweis, dass die Datei einige Verknüpfungen zu externen Quellen enthält. Und nun sollst du dich entscheiden, ob die Verknüpfungen aktualisiert werden sollen oder nicht. Weil du ein vorsichtiger Anwender bist („gebranntes Kind scheut das Feuer“), möchtest du natürlich erst einmal wissen, zu welchen Dateien hier eigentlich verknüpft wird.

An dieser Stelle macht es Excel einem nicht gerade einfach. Wäre es nicht schön, wenn man übersichtlich in einem Tabellenblatt eine Auflistung aller verknüpften Dateien einschließlich der kompletten Pfade bekommen könnte? Gut, dass du fragst. Denn genau darum dreht es sich im heutigen Artikel!

Und so geht’s:

Eine Funktion liefert alle Treffer 45

27.04.2020 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: FILTER / SORTIEREN Von: Martin Weiß

Eine neue Tabellenfunktion in Excel liefert ALLE gesuchten Werte

Vor etwa 4 Jahren hatte ich einen Artikel unter dem Titel „Besser als SVERWEIS: Alle Werte finden“ veröffentlicht. Damit habe ich offensichtlich einen Schmerzpunkt bei vielen Excel-Anwendern angesprochen, denn dieser Beitrag ist einer der beliebtesten und am meisten kommentierten überhaupt.

Die dort gezeigte Formel, um das Problem zu lösen, ist jedoch relativ kompliziert und für Otto-Normal-Excelanwender nur schwer durchschaubar. Mittlerweile sind 4 Jahre vergangen und Excel hat sich weiterentwickelt. Und bekanntlich führen viele Wege nach Rom. Grund genug also, in diesem und den folgenden Artikeln ein paar Alternativen aufzuzeigen.

Der Original-Artikel „Besser als SVERWEIS: Alle Werte finden“
Alternative 1: Die FILTER-Funktion – davon handelt der heutige Artikel
Alternative 2: Der eingebaute Spezialfilter
Alternative 3: Eine Pivot-Tabelle
Alternative 4: Power Query

Den Anfang macht eine geradezu unfassbar einfache Lösung. Worin genau das genannte Problem besteht und wie die heutige Lösung aussieht, erfährst du in diesem Beitrag.

Und so geht’s:

Goodbye SVERWEIS, willkommen XVERWEIS! 48

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

Diese neue Funktion ist wie der SVERWEIS, nur um Klassen besser!

Gefürchtet, geliebt und irgendwie unausweichlich. Die allseits bekannte SVERWEIS-Funktion könnte auch aus dem Buch „Der Herr der Ringe“ stammen:

„Eine Funktion, sie zu knechten, sie alle zu finden, ins Dunkel zu treiben und ewig zu binden“

So fühlt sich zumindest mancher Excel-Einsteiger, wenn er Bekanntschaft mit dem SVERWEIS macht. Wenn man ihn jedoch einmal gemeistert hat, kommt man nur schwer wieder davon los.

Nun, du kannst den SVERWEIS getrost in den Schicksalsberg werfen. Denn jetzt gibt es den

XVERWEIS

Was diese neue Excel-Mehrzweckwaffe kann, erfährst du in diesem Artikel.

Excel für Kreative: Die UNIZEICHEN-Funktion 27

27.01.2020 in Formeln und Funktionen / Text Stichworte: DEZINHEX / HEXINDEX / Spiele / UNICODE / UNIZEICHEN Von: Martin Weiß

Eine ziemlich unbekannte Funktion bietet viel Spielraum für Kreative

Aktuelle Excel-Versionen bieten einen Katalog von mehr als 450 Tabellenfunktionen. Viele davon sind so speziell, dass man oft keine Ahnung hat, wofür man sie brauchen kann. In diese Kategorie dürfte auch die Funktion UNIZEICHEN fallen. Oder hast du je von ihr gehört oder kannst gar sagen, wozu sie gut ist?

Nun, ich hätte da ein paar interessante Ideen. Vielleicht hast du ja gerade auf so etwas gewartet, und weißt es nur noch nicht!

Und so geht’s:

Datenanalyse mit Excel: Deskriptive Statistik automatisieren 5

11.11.2019 in Formeln und Funktionen / Statistik Von: Gastautor

Ein kleines Excel-Tool erleichtert statistische Auswertungen.

Excel und Statistik. Zwei Begriffe, die bei vielen Menschen vermutlich vor lauter Begeisterung die Fußnägel kräuseln lassen. Andere wiederum können sich nichts Schöneres vorstellen… Da meine Kenntnisse auf dem Gebiet der Statistik vorsichtig formuliert sehr überschaubar sind, kommt ein Gastautor zu Wort, der wesentlich mehr Erfahrung mitbringt. Robert Grünwald hat schon ein paar mal hier im Blog Statistik-Artikel veröffentlicht und ist daher vielleicht dem einen oder anderen Leser schon bekannt. Er stellt heute ein kleines Tool vor, mit dem sich bestimmte statistische Analysen in Excel vereinfachen lassen.

Und so geht’s:

Die INDIREKT-Funktion: Ich kenne einen, der jemanden kennt… 27

12.08.2019 in Formeln und Funktionen Stichworte: INDIREKT / Schnittmengenoperator Von: Martin Weiß

Die INDIREKT-Funktion kann sehr hilfreich sein, wenn man ein wenig um die Ecke denkt...

Excel ist über die Jahre hinweg immer komfortabler und benutzerfreundlicher geworden. Allerdings gibt es viele Tabellenfunktionen, deren Sinn sich einem nicht immer sofort erschließt. Dazu gehört beispielsweise auch die INDIREKT-Funktion.

Dabei passt diese Funktion ganz hervorragend in unsere heutige vernetzte Zeit, in der Beziehungen so wichtig sind. Es heißt ja immer, man muss nicht alles selber können, es reicht, wenn man jemanden kennt, der weiß wie etwas geht.

Und so ähnlich ist das auch mit der INDIREKT-Funktion…

Formatierte Tabellen, relative Bezüge und deren Probleme 17

08.07.2019 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: BEREICH.VERSCHIEBEN / Formatierte Tabelle / Strukturierte Verweise Von: Martin Weiß

Wie man Probleme mit relativen Bezügen in formatierten Tabellen vermeiden kann

Ein extrem praktisches und leistungsfähiges Konzept in Excel sind die formatierten Tabellen („intelligente Tabellen“). Nicht nur im Zusammenhang mit Pivot-Tabellen und Power Query-Abfragen machen sie das Leben viel einfacher. Auch sonst sind die damit verbundenen Automatiken sehr nützlich.

Allerdings haben sie auch ihre Grenzen und Nachteile. Einer davon betrifft relative Zellbezüge. Wo genau das Problem liegt und wie man es umgehen kann, zeigt der heutige Artikel.

Und so geht’s:

Dynamische Arrayfunktionen (Teil 2) 4

29.04.2019 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: SEQUENZ / SORTIERENNACH Von: Martin Weiß

Zwei weitere Beispiele aus der zukünftigen Kategorie der dynamischen Array-Funktionen

Kürzlich hatte ich dir ja schon ein paar neue Excel-Funktionen vorgestellt, die noch nicht der breiten Öffentlichkeit zur Verfügung stehen: SORTIERE, FILTER und EINDEUTIG. Falls du diesen spannenden Einblick in die nahe Zukunft verpasst hast, empfehle ich dir unbedingt, diesen Artikel nachzulesen: Ein Blick in die Zukunft: Dynamische Arrayfunktionen

Heute zeige ich zwei weitere interessante Array-Funktionen, die bald Einzug in die Excel-Welt finden dürften: SORTIERENNACH und SEQUENZ.

Und so geht’s:

Ein Blick in die Zukunft: Dynamische Arrayfunktionen 19

01.04.2019 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: EINDEUTIG / FILTER / SORTIEREN Von: Martin Weiß

Der Umgang mit Arrays wird sich in Excel grundlegend ändern. Hier gibt es einen kleinen Ausblick.

Der heutige Artikel fällt etwas aus der Reihe, denn ich werde dir Dinge in Excel zeigen, die noch nicht sehr viele Menschen gesehen haben! Ich möchte mit dir einen Blick in die Excel-Zukunft werfen, wo vieles von dem, was wir bisher von Tabellenfunktionen gekannt haben, auf den Kopf gestellt wird.

Bist du neugierig geworden? Gut so! Dann lehn‘ dich entspannt zurück und freue dich auf die Lektüre.

Ach ja, noch ein kleiner Hinweis für die ganz Misstrauischen unter uns: Nein, auch wenn es das Datum vielleicht vermuten lässt, ist in diesem Artikel KEIN Aprilscherz versteckt.

Und los geht’s:

Eine Dateiliste in Excel erzeugen 38

18.03.2019 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: DATEIEN / INDEX / ZEILE Von: Martin Weiß

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:

Wie ein SVERWEIS von hinten: Den letzten Wert finden 24

04.03.2019 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik Stichworte: INDEX / VERGLEICH / ZÄHLENWENN Von: Martin Weiß

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:

Eine spezielle Verkettung von Zellen 36

18.02.2019 in Formeln und Funktionen / Nachschlagen und Verweisen / Text Stichworte: Matrixformel / TEXTVERKETTEN Von: Martin Weiß

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:

Forecast-Diagramm mit Pfiff 8

09.01.2019 in Diagramme / Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: INDEX / ISTZAHL / MAX / MIN / VERGLEICH Von: Martin Weiß

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:

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

10.12.2018 in Formatierung und Layout / Formeln und Funktionen / Mathematik und Trigonometrie Stichworte: Formatierte Tabelle / SUMMEWENN Von: Gastautor

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!

Sortiert oder nicht sortiert? 5

29.10.2018 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: Matrixformel Von: Martin Weiß

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: