Neu in Office 365 | So einfach können Sie mittlerweile Ihre Bilder mit Transparenz versehen
Bilder mit Transparenz sind chic und liegen voll im Trend. Man sieht diese Technik in letzter Zeit sehr häufig. Das Bild drängt sich auf diese Art und Weise nicht zu sehr in den Vordergrund und Texte, Zahlen wie auch Icons können so gut mit dem Bild kombiniert werden. Im Beispiel hier oben habe ich eine Bildcollage aus zwei übereinanderliegenden transparenten Bildern erstellt und mit Text kombiniert. Das geht mittlerweile mit Office 365 und dem neuen Befehl Transparenz ganz einfach. weiterlesen
Excel-Tutorial: Mit Power Query und Pivot die Altersstruktur einer Firma lückenlos darstellen
Die Kenntnis der Altersstruktur der Mitarbeiter/innen ist für viele Unternehmen essentiell. Die Analyse wird oft auf folgende Weise erstellt:
1) Zunächst wird der aktuelle Bestand der Beschäftigten beschafft, der Zu- und Abgänge berücksichtigt.
2) Dann wird das Alter der Beschäftigten berechnet.
3) Liegen die Informationen zu 1) und 2) vor, wird ermittelt, wie viele Personen zu jeder Altersgruppe gehören. Dafür werden die Altersangaben gruppiert.
Schritt 3 lässt sich über eine Pivot-Auswertung realisieren. Allerdings stellen sich dabei folgende Fragen:
- Wie lässt sich AUTOMATISIERT die Liste der aktuell Beschäftigten mit ihrem gegenwärtigem Alter bereitstellen?
- Wie lässt sich das Alter ermitteln, ohne die Ausgangsdaten mit Zusatzspalten erweitern zu müssen?
- Wie lässt sich sicherstellen, dass bei fehlenden Daten für eine Altersstufe die Achse im Pivot-Diagramm trotzdem lückenlos ist?
Die Lösung bringen zwei Abfragen, die über den Power Query-Editor generiert und anschließend beim Aufbau der Pivot-Tabelle genutzt werden. Das Pivot-Diagramm ist dann zum Schluss im Nu erstellt.
Der Einsatz von Power Query bringt noch einen weiteren Vorteil: Das Mindest- und Höchstalter der Beschäftigten lässt sich je nach Bedarf flexibel für die Pivot-Tabelle anpassen.
Ein TUTORIAL zeigt anhand zahlreicher Bildschirmfotos, wie die Lösung Schritt für Schritt aufgebaut wird. Wer mitmachen will, kann sich auch gleich die Übungsdatei herunterladen. Hier geht’s zum Tutorial und der Übungsdatei.
Power Query-Formeln in Excel einfacher erstellen dank IntelliSense
Seit dem Power BI Desktop-Update vom September 2018 werde ich beim Erstellen und Bearbeiten von Power Query-Formeln durch IntelliSense unterstützt: Dank Autovervollständigen, QuickInfos zu Funktionen und Parametern sowie Zeilennummerierung kann ich Formeln in der Power Query-Sprache »M« schneller und bequemer erfassen, anpassen und dokumentieren.
Leider steht IntelliSense noch nicht direkt in Excel zur Verfügung. Mit dem folgenden kleinen Trick aber schon. Es sind nur 3 Schritte.
Die Nachteile der Datumsgruppierung in Pivot umgehen
Seit Excel 2016 lassen sich Datumsangaben auch in Pivot-Tabellen, die auf dem Datenmodell aufbauen, per Rechtsklick nach Jahren, Quartalen und Monaten gruppieren. »Endlich!« werden viele von Ihnen sagen. »Bloß nicht!« sage ich, denn die Gruppierung hat zwei große Nachteile. Welche das sind und wie Sie sie umgehen, erfahren Sie in diesem Beitrag.
Bei der Verwendung des Datenmodells ist die automatische Datumsgruppierung hinderlich
Mehrere Excel-Listen kombinieren und berechnen: Der Cross Join macht’s möglich
Im Beitrag vom 3. Juli 2018 habe ich beschrieben, wie Listen mit gleichen Spalten über Joins miteinander abgeglichen werden. Heute geht es um Listen, die keine Gemeinsamkeit aufweisen.
Hier im Beispiel sind es Artikel, die in unterschiedlichen Farben erhältlich sind. Je nach Farbe gibt es für jeden Artikel auf den Basispreis einen prozentualen Aufschlag. Gebraucht wird also eine Artikelliste, die für jeden Artikel in jeder Farbkombination den Preis bereithält.
Per Cross Join zwei Listen kombinieren
Das lässt sich lösen mit einem speziellen Join – dem sog. Cross Join (Kreuzprodukt). Er kombiniert alle Daten einer Tabelle mit allen Daten einer anderen Tabelle.
Allerdings ist der Cross-Join in Power Query nicht über Start > Kombinieren > Abfragen als neue Abfrage zusammenführen zu finden. Er muss über eine benutzerdefinierte Spalte herbeigeführt werden.
Excel-Listen abgleichen und verknüpfen, das geht ganz leicht: Die 6 magischen Joins von Power Query
Was Datenbanken als Standard können, was in Excel per SVERWEIS und mit umständlichem Zusammenkopieren funktioniert, lässt sich in Power Query mit ein paar Mausklicks und ohne jede Formel erledigen.
- Welche Produkte für Kunde A werden auch an Kunde B verkauft?
- Welche Artikel aus Lager A kann auch Lager B liefern?
- Welche Artikel wurden im aktuellen Jahr nicht verkauft?
- Welche Kunden haben im letzten Jahr keine Bestellung aufgeben?
Um solche Fragen zu beantworten, müssen Listen abgeglichen werden. Das erledigen in Power Query sogenannte Joins. Sie stellen die notwendigen Beziehungen zwischen den zu vergleichenden Tabellen her. So werden beispielsweise die Artikelnummern aus Lager A mit den Artikelnummern aus Lager B abgeglichen. Für diesen Abgleich stellt Power Query sechs verschiedene Join-Möglichkeiten zur Verfügung.
Doppeltes Power Query? So lassen sich Abfragen im Nachhinein analysieren
Power Query ist DAS Werkzeug zur Abfrage von Daten aus beliebigen Quellen. Selbst komplexe Abfragen mit vielen einzelnen Schritten lassen sich einfach zusammenstellen.
Was aber, wenn ich in einer bestehenden Abfrage noch mal nachschauen will, welche Schritte nacheinander durchgeführt wurden, weil ich gerade eine neue Abfrage ähnlich gestalten möchte?
Da wäre es doch praktisch, wenn ich alte und neue Abfrage nebeneinander anordnen und dann Schritt für Schritt vergleichen könnte. Doch wie bekomme ich den Abfrageeditor zweimal geöffnet? Mit der folgenden kurzen Anleitung klappt’s!
Jetzt wird es eng: Schnell einen der letzten Plätze ergattern
Wer wissen will, wie das im Video gezeigte interaktive Dashboard funktioniert und wie es aufgebaut wird, sollte sich schnell den letzten Platz für diesen Workshop sichern.
Auch für den Workshop Excel-Formulare ist noch genau ein Platz verfügbar.
Ganz 3 Plätze gibt es noch für die Excel-Kompetenztage. Hier geht es in 5 Kursreihen um Formeln und Funktionen, Diagramme, Pivot sowie die neuen BI-Tools von Excel.
Schon lange ausgebucht ist der Workshop zu Listen und Power Query.
Wer sich für weitere Events und Workshops interessiert, schreibt einfach eine E-Mai an info@office-kompetenz.de.
Daten in Power Query für Pivot-Auswertungen mit Jahr, ISO-Jahr und -Kalenderwoche vorbereiten
Pivot-Auswertungen nach ISO-Kalenderwoche werden in unterschiedlichsten Bereichen benötigt, ob für Umsätze, Bestellungen oder Produktionszahlen.
In Pivot kann für Datumsspalten eine Gruppierung nach Jahr, Quartal, Monat und/oder Tag gewählt werden, aber Kalenderwochen-Gruppierungen gibt es nicht.
Bild 1: Produktionszahlen in Pivot nach Jahr und ISO-Kalenderjahr und -woche auswerten
Wer die Daten für die Pivot-Auswertung mit Power Query vorbereitet, freut sich über die vielfältigen Möglichkeiten und die große Zeitersparnis gegenüber aufwendigen Copy&Paste- und Berechnungsaktionen in Excel.
Allerdings steht Power Query in Bezug auf Datumsfunktionen Excel noch ein wenig nach. In Excel steht für die Berechnung der ISO-Kalenderwoche seit Version 2010 die Funktion KALENDERWOCHE mit dem Parameter 21 und seit Version 2013 die Funktion ISOKALENDERWOCHE zur Verfügung.
Power Query verfügt bisher über keine solche Funktion. Aber auch dafür gibt eine Lösung.
In Diagrammen automatisch die drei niedrigsten Werte farblich hervorheben
Wer kennt sie nicht: Diagramme mit einer Vielzahl mehr oder weniger hoher Säulen. Doch was sagen sie eigentlich aus? Oder: Was sollen sie aussagen?
Sind es Umsätze, Gewinne oder Bestellungen, interessieren sich alle zuerst für die höchsten Säulen.
Bei Fehlerquoten, Reklamationen oder Kosten hingegen gilt der Blick den kleinsten Säulen: sie haben am besten abgeschnitten.
Links die Standardlösung in Grau, rechts das Diagramm, in dem die drei niedrigsten Werte dank grüner Farbe sofort erkennbar sind
Woher aber sollen die Betrachter wissen, welche Säulen wichtig sind?
Farben helfen dabei, die Aussage von Diagrammen leichter zu erfassen. Der Blick auf das Wesentliche oder auf Probleme lässt sich gezielt lenken: einfach die betreffenden Säulen farblich hervorheben. Natürlich soll das automatisch erfolgen.
Wie das geht, zeigt die folgende Schritt-für-Schritt-Anleitung.
