Thema: Daten- und Analysetools

Fantastische Verknüpfungen und wo man sie in Excel findet 25

 in Daten- und Analysetools  Stichworte: Verknüpfung Von:
Des einen Freud, des anderen Leid: Externe Verknüpfungen in Excel können hilfreich sein - oder einen in den Wahnsinn treiben
 

Vor längerer Zeit hatte ich einen Artikel veröffentlicht, der sich mit externen Verknüpfungen in Excel-Dateien befasst. Wie du ja sicherlich weißt, lassen sich in einer Formel Bezüge zu einer anderen Arbeitsmappe erstellen. Was in manchen Fällen ganz praktisch erscheinen mag, treibt einen jedoch manchmal in den Wahnsinn. Nämlich dann, wenn man beim Öffnen einer Datei mit der Meldung beglückt wird „Diese Arbeitsmappe enthält Verknüpfungen zu mindestens einer externen Quelle…“, obwohl man zumindest wissentlich niemals eine solche Verknüpfung gesetzt hat.

Solche externen Links können nämlich nicht nur über normale Formeln entstehen, sondern über unzählige andere Wege, die leider nicht immer so einfach zu finden sind. Wenn du also schon einmal einen Kollegen mit einem irren Kichern und einem Hauch von Wahnsinn in den Augen gesehen hast, könnte es sein, dass er bei der Suche nach einer solchen Verknüpfung einfach übergeschnappt ist…

Damit es dir nicht auch so ergeht, zeige ich dir heute ein paar weitere Verstecke.

Weiterlesen »

Bedingte Formatierung in Pivot-Tabellen 28

 in Daten- und Analysetools / Formatierung und Layout  Stichworte: Bedingte Formatierung / Pivot-Tabelle Von:
Bedingte Formatierung und Pivot-Tabellen passen hervorragend zusammen - wenn man ein paar Dinge beachtet!
 

Wer diesen Blog schon länger liest, der weiß, dass ich ein großer Fan von bedingter Formatierung und von Pivot-Tabellen bin.
Beides sind für sich genommen schon mächtige und extrem nützliche Funktionen, um mehr Licht in seine Zahlenfriedhöfe zu bringen.

Noch besser wird es, wenn man bedingte Formatierung und Pivot-Tabellen miteinander kombiniert. Hierbei gibt es ein paar Besonderheiten zu beachten.

Und so geht’s:

Weiterlesen »

Kreuztabellen entpivotieren 8

 in Daten- und Analysetools  Stichworte: Pivot-Tabelle Von:
Mit einem cleveren Trick lässt sich eine Kreuztabelle in eine flache Datentabelle umwandeln, um sie dadurch Pivot-tauglich zu machen.
 

Pivot-Tabellen sind eine der großartigsten Funktionen überhaupt in Excel. Mit ihrer Hilfe lassen sich umfangreiche Datentabellen mit nur wenigen Klicks aus allen Richtungen durchleuchten.

Was aber tun, wenn das Datenmaterial in einer für Pivot-Tabellen ungeeigneten Form vorliegt? Ein typisches Beispiel dafür sind kreuztabellenartige Monatsauswertungen, die schon für jeden Monat eine eigene Spalte enthalten.

Wie man solche Kreuztabellen entpivotiert, um sie für den Einsatz von echten Pivot-Tabellen tauglich zu machen, das zeige ich im heutigen Artikel.

Und so geht’s:

Weiterlesen »

Listenabgleich: Finde die fehlenden Einträge! 25

 in Daten- und Analysetools  Stichworte: Power Query Von:
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:

Weiterlesen »

Ein Spezialfall: Datumsreihen automatisch fortschreiben 35

 in Daten- und Analysetools  Stichworte: Datenreihe Von:
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:

Weiterlesen »

Von Excel zu XML (und zurück) 89

 in Daten- und Analysetools  Stichworte: XML Von:
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:

Weiterlesen »

Massen-Import mit PowerQuery 60

 in Daten- und Analysetools  Stichworte: Power Query Von:
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.

Weiterlesen »

Ordnerstrukturen mit PowerQuery in Excel einlesen 32

 in Daten- und Analysetools  Stichworte: Power Query Von:
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:

Weiterlesen »

Einführung in Power-Query – Teil 2 36

 in Daten- und Analysetools  Stichworte: Power Query Von:
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:

Weiterlesen »

Einführung in Power-Query – Teil 1 31

 in Daten- und Analysetools  Stichworte: Power Query Von:
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.

Weiterlesen »

Dropdown-Liste Spezial 47

 in Daten- und Analysetools / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / Datenüberprüfung / Formatierte Tabelle / Pivot-Tabelle Von:
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:

Weiterlesen »

Planspiele mit verschiedenen Szenarien

 in Daten- und Analysetools  Stichworte: Szenario-Manager / Was-wäre-wenn-Analyse Von:
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:

Weiterlesen »

Eingabeprüfung: Nur Großschreibung erlauben 17

 in Allgemeine Excel-Tipps / Daten- und Analysetools / Formeln und Funktionen / Logisch / Text  Stichworte: GROSS / IDENTISCH / KLEIN Von:
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:

Weiterlesen »

Eingabefehler vermeiden durch eine Datenüberprüfung 42

 in Daten- und Analysetools  Stichworte: Datenüberprüfung / Gültigkeit Von:
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:

Weiterlesen »

Verdichten und Gruppieren in Pivot-Tabellen 32

 in Daten- und Analysetools  Stichworte: Pivot-Tabelle Von:
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:

Weiterlesen »

Wenn der Blitz in Excel einschlägt 5

 in Daten- und Analysetools  Stichworte: Blitzvorschau Von:
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:

Weiterlesen »