Excel beißt nicht!

Thema: Daten- und Analysetools

Listenabgleich: Finde die fehlenden Einträge! 25

11.06.2018 in Daten- und Analysetools Stichworte: Power Query Von: Martin Weiß

Mit Hilfe von Power Query werden fehlende Datensätze blitzschnell identifiziert

Der Abgleich von zwei Listen ist eine häufige Anwendung in Excel. Oft geht es dabei darum, die Daten der einen Tabelle mit weiteren Informationen aus der anderen Tabelle anzureichern:
Artikellisten mit den Preisen, Verkäufer mit den Regionen, Mitarbeiter mit Abteilungen und so weiter. SVERWEIS lässt grüßen!

Heute geht es jedoch darum, aus zwei annähernd gleichen Listen diejenigen Einträge zu identifizieren, die nur in einer der beiden Listen enthalten sind und in der anderen Liste fehlen. Eine Paradeanwendung für „Daten abrufen und transformieren“, besser bekannt als Power Query.

Und so wird’s gemacht:

Ein Spezialfall: Datumsreihen automatisch fortschreiben 30

14.05.2018 in Daten- und Analysetools Stichworte: Datenreihe Von: Martin Weiß

Mit einem kleinen Trick klappt es auch mit ausgefallenen Datumsreihen

Jeder einigermaßen geübte Excel-Anwender kennt vermutlich die Autoausfüllen-Funktion:
Man gibt zwei Werte ein, markiert die beiden Zellen und erweitert die Markierung über das kleine Ausfüllkästchen in der rechten unteren Zellenecke. Excel erkennt das Muster und schreibt die Datenreihe automatisch fort. Das funktioniert meistens auch ganz gut, kann aber speziell bei Datumsreihen ziemlich kniffelig sein.

Aber hast schon einmal versucht, eine Datumsreihe nur mit dem 1. und dem 15. eines Monats fortzuschreiben?
Oder immer nur mit den Werktagen von Montag bis Freitag?

Und so geht’s:

Von Excel zu XML (und zurück) 87

22.01.2018 in Daten- und Analysetools Stichworte: XML Von: Martin Weiß

Wie man aus einer Excel-Tabelle eine XML-Datei erstellt

Auch wenn wir Excel-Fetischisten das nicht so gerne hören:
Es gibt noch andere Systeme da draußen in dieser bösen Welt, mit denen man hin und wieder Daten austauschen muss. Insbesondere in Zeiten des allgegenwärtigen Internets erfreut sich hier ein Format besonderer Beliebtheit:

Das XML-Format.

Wie man seine Excel-Tabelle als XML-Datei speichert oder eine vorliegende XML-Datei in eine Excel-Tabelle importiert, zeige ich im heutigen Artikel.

Und so geht’s:

Massen-Import mit PowerQuery 58

06.11.2017 in Daten- und Analysetools Stichworte: Power Query Von: Martin Weiß

Die Inhalte vieler Dateien mit wenigen Klicks in eine einzige Tabelle importieren.

Im letzten Artikel habe ich gezeigt, wie man mit Hilfe von PowerQuery einen kompletten Verzeichnisbaum in Excel einliest und somit mit minimalem Aufwand eine Übersicht über alle enthaltenen Dateien erhält, nebst Erstellungsdatum und Dateigröße.

Jetzt gehen wir einen Schritt weiter und lesen die Inhalte aller Dateien eines Verzeichnisses in einem Rutsch in eine einzige Excel-Tabelle ein.

Wie das geht und welche Voraussetzungen dabei zu beachten sind, erfährst du im folgenden Beitrag.

Ordnerstrukturen mit PowerQuery in Excel einlesen 32

16.10.2017 in Daten- und Analysetools Stichworte: Power Query Von: Martin Weiß

Ein komplettes Verzeichnis mit PowerQuery in Excel einlesen und auswerten

PowerQuery entwickelt sich immer mehr zu einem persönlichen Favoriten von mir in Excel. Mit wenig Aufwand lassen sich hier Dinge erledigen, die vorher nicht oder nur sehr umständlich möglich waren.

Heute zeige ich dir, wie man damit den Inhalt eines Verzeichnisses auf deinem Computer ausliest, alle enthaltenen Dateinamen in sämtlichen Unterverzeichnissen in einer Excel-Tabelle auflisten und sich mit wenig Aufwand einen schnellen Überblick über die Speicherbelegung schaffen kann.

Und so geht’s:

Einführung in Power-Query – Teil 2 36

09.05.2016 in Daten- und Analysetools Stichworte: Power Query Von: Martin Weiß

Teil 2 meiner kleinen Einführung in das mächtige Tool namens Power Query

Nach dem ersten Teil der Einführung in Power Query von letzter Woche geht es diese Woche gleich mit Teil 2 weiter.

Heute zeige ich dir unter anderem, wie man mehrere Quelltabellen ruckizucki miteinander verknüpft. Wenn dir in der Vergangenheit für diesen Zweck mit SVERWEIS bei wirklich großen Tabellen die Füße eingeschlafen sind, dann wirst du von den Möglichkeiten begeistert sein, die Power Query an dieser Stelle bietet.

Und so geht’s:

Einführung in Power-Query – Teil 1 31

02.05.2016 in Daten- und Analysetools Stichworte: Power Query Von: Martin Weiß

Aller Anfang ist leicht: Erste Schritte mit dem mächtigen Instrument "Power Query"

Heute stelle ich dir ein mächtiges Instrument vor, mit dem die Möglichkeiten von Excel stark erweitert werden. Mit der Excel-Version 2013 wurden von Microsoft zusätzliche Werkzeuge bereitgestellt, um unter dem Schlagwort „Business Intelligence“ den Umgang mit großen Datenmengen zu erleichtern.

Eines dieser Instrumente heißt Power Query für Excel.

Es handelt sich dabei um ein sogenanntes Add-In, welches für Excel 2010 und 2013 zur Verfügung steht und in die neue Excel-Version 2016 bereits integriert ist.

Die schlechte Nachricht: Für Anwender der Excel-Versionen 2007 und älter ist hier leider Schluss. Dort wird dieses mächtige Add-In leider nicht unterstützt. Vielleicht ein Grund mehr, auf eine aktuelle Excel-Version umzusteigen.

Dropdown-Liste Spezial 47

11.04.2016 in Daten- und Analysetools / Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: BEREICH.VERSCHIEBEN / Datenüberprüfung / Formatierte Tabelle / Pivot-Tabelle Von: Martin Weiß

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:

Planspiele mit verschiedenen Szenarien

04.04.2016 in Daten- und Analysetools Stichworte: Szenario-Manager / Was-wäre-wenn-Analyse Von: Martin Weiß

Flexibel und mit wenig Aufwand unterschiedliche Szenarien durchspielen.

Das Schöne an Excel-Tabellen ist ja, dass man mit Hilfe von Funktionen bestimmte Berechnungen automatisch anstellen kann. Man gibt ein paar Werte ein und die SUMME-Funktion präsentiert sofort das Ergebnis.

Wer jetzt eine Kalkulation mit verschiedenen Werten durchspielen möchte, kann die Werte für die unterschiedlichen Szenarien eintippen und sich die alten Ergebnisse jedes Mal merken.

Oder er verwendet ein cleveres, wenn auch ein relativ unbekanntes Hilfsmittel aus der Excel-Trickkiste: den Szenario-Manager!

Und so geht’s:

Eingabeprüfung: Nur Großschreibung erlauben 17

22.02.2016 in Allgemeine Excel-Tipps / Daten- und Analysetools / Formeln und Funktionen / Logisch / Text Stichworte: GROSS / IDENTISCH / KLEIN Von: Martin Weiß

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:

Eingabefehler vermeiden durch eine Datenüberprüfung 42

02.11.2015 in Daten- und Analysetools Stichworte: Datenüberprüfung / Gültigkeit Von: Martin Weiß

Zulässige Eingaben in Excel mit Hilfe der Datenüberprüfung festlegen

Zu einer anwenderfreundlichen Tabelle gehört unter anderem, dass man den Benutzer auf etwaige Fehleingaben bereits während der Datenerfassung hinweist.

Excel bietet dafür verschiedene Möglichkeiten. Eine davon ist die sogenannte Datenüberprüfung. Mit ihrer Hilfe lassen sich für die entsprechenden Tabellenbereiche Regeln definieren, die nur bestimmte Eingaben zulassen.

Dies ist ein wirksames Mittel, um Fehleingaben und damit unnötigen Datenmüll in seinen Arbeitsblättern zu verhindern.

Und so geht’s:

Verdichten und Gruppieren in Pivot-Tabellen 32

12.10.2015 in Daten- und Analysetools Stichworte: Pivot-Tabelle Von: Martin Weiß

Mit wenigen Klicks lassen sich Datumswerte in einer Pivot-Tabelle verdichten

Pivot-Tabellen sind in Excel eine wirklich feine Angelegenheit. Leider haben viele Leute völlig zu Unrecht einen Heidenrespekt davor (falls auch du zu ihnen gehören solltest, empfehle ich dir diesen Einsteiger-Artikel).

Ein besonders praktisches Feature möchte ich dir heute vorstellen. Wenn die Ausgangstabelle nämlich viele verschiedene Datumswerte enthält und man seine Pivot-Tabelle z.B. nach Monaten oder Quartalen verdichten möchte, geht das mit mit wenigen Klicks.

Und zwar so:

Wenn der Blitz in Excel einschlägt 5

05.10.2015 in Daten- und Analysetools Stichworte: Blitzvorschau Von: Martin Weiß

Die Blitzvorschau ist ein intelligentes und Zeit sparendes Hilfsmittel während der Dateneingabe in Excel

Excel bietet viele Optionen an, die die Eingabe von Daten erleichtern. Dazu gehören z.B. formatierte Tabellen oder das Auto-Auffüllen.

Ein besonderes „Schmankerl“ gibt es in diesem Zusammenhang in der Excel Version 2013 (und natürlich in der brandneuen Version 2016): Die sogenannte Blitzvorschau, in Excel-Englisch auch „Flash-Fill“ genannt.

Diese fast schon intelligent anmutende Funktion kommt recht unscheinbar daher, hat es aber wirklich in sich. Was es damit auf sich hat und wie du dir damit eine Menge manuelle Eingabearbeit ersparen kannst, zeige ich dir im heutigen Artikel.

Und so geht’s:

Excel: Dubletten verhindern 35

02.03.2015 in Daten- und Analysetools / Formatierung und Layout / Nachschlagen und Verweisen / Statistik Stichworte: Bedingte Formatierung / Datenüberprüfung / Dubletten / Duplikate / SUMMENPRODUKT / ZÄHLENWENN Von: Martin Weiß

3 Methoden, um doppelte Werte bereits bei der Dateneingabe zu vermeiden

Wer mit großen Datenmengen und langen Listen in Excel arbeiten muss, kennt vermutlich das Thema der Dubletten:

Einträge wurden doppelt in der Tabelle erfasst, obwohl eigentlich jeder Wert nur einmal vorkommen dürfte.

Wie man bereits vorhandene Dubletten erkennt, habe ich unter anderem in diesem Artikel beschrieben.

Aber wie lässt sich schon bei der Datenerfassung in Excel verhindern, dass neue Dubletten angelegt werden?

So geht’s:

Die NSA in Excel? Formeln überwachen (Teil 2) 3

01.12.2014 in Daten- und Analysetools Stichworte: Überwachungsfenster Von: Martin Weiß

Mit Hilfe des Überwachungsfensters lassen sich beliebige Zellen stets im Blick halten.

Nachdem ich dir im ersten Teil dieser „Überwachungsserie“ die Formelauswertung gezeigt habe, sehen wir uns heute ein weiteres Instrument an, um in Excel den Überblick zu behalten.

Gerade in umfangreichen Arbeitsmappen, bei denen sich Daten in unterschiedlichen Arbeitsblättern befinden, kannst du mit diesem Werkzeug auch weit entfernt liegende Zellen beobachten:

Das Überwachungsfenster

Und so geht’s:

Die NSA in Excel? Formeln überwachen (Teil 1) 1

24.11.2014 in Daten- und Analysetools Stichworte: Formelauswertung Von: Martin Weiß

Die Formelauswertung hilft bei der Fehlersuche in komplexeren Formeln und Funktionen

Wer in Excel mit komplexeren Formeln arbeitet, kann schnell den Überblick verlieren.

Insbesondere, wenn man mehrere Funktionen ineinander verschachtelt hat, stellt man sich oft die Frage: Warum liefert das Biest anstelle des gewünschten Ergebnisses nur einen Fehler?

Aber Excel wäre nicht Excel, wenn es den geplagten Anwender nicht bei der Problemsuche unterstützen würde. Im heutigen ersten Teil zeige ich dir ein Hilfsmittel, das für mehr Durchblick in komplexen Formeln sorgt:

Die Formelauswertung

Und so geht’s: