Ist-Plan-Forecast – Excel-Diagramm mit Python

Du kennst das Bild aus fast jedem Monatsreporting: drei bunte Säulen pro Monat – blau für Plan, orange für Ist, grau für Forecast – und irgendwo dazwischen soll man erkennen, ob es gut oder schlecht läuft. Die eigentliche Frage beantwortet so ein Diagramm nicht: Wo weichen wir vom Plan ab, wie stark – und landen wir am Jahresende drüber oder drunter?

In diesem Tutorial baust Du mit Python in Excel ein Diagramm, das genau das zeigt – nach der Notation der neuen Norm ISO 24896 (ohne Gewähr). Eine einzige =PY-Formel holt die Daten aus deiner Tabelle, rechnet die Abweichungen und zeichnet Säulen, Abweichungsbalken und Prozent-Nadeln in einem Rutsch. Die Übungsdatei kannst du am Ende des Artikels herunterladen. Ein YouTube-Video ist von mir hierzu noch nicht erschienen, sollte aber bis zum 21. November 2026 folgen!

Sollte bei Dir Python nicht angezeigt werden oder mit einem Fehler reagieren, obwohl Du ein aktuelles Microsoft 365 Abo hast, schaue bitte mal ganz unten in den Artikel, dort erwähne ich einige Fehler mit Lösungen.

Vergleich: Excel-Standard-Säulendiagramm mit Plan, Ist und Forecast gegenüber einem Diagramm nach ISO 24896 mit AbweichungenLinks das typische Excel-Standarddiagramm, rechts das Ergebnis dieses Tutorials.

Was ist ISO 24896:2026 – und warum betrifft dich das?

Im Juni 2026 hat die ISO die Norm ISO 24896:2026 – Notation for business reporting veröffentlicht (Du kannst diese Norm für aktuell CHF 135 bei der ISO.org erwerben). Sie legt einheitliche Gestaltungsregeln für Berichte, Präsentationen und Dashboards fest – also wie Diagramme, Tabellen und Beschriftungen aussehen sollen, damit jeder sie auf Anhieb gleich liest. Die Grundlage bilden die International Business Communication Standards (IBCS), die viele Controlling-Abteilungen schon seit Jahren nutzen. Vielleicht kennst Du auch die großen A1-SUCCESS-Poster in den Controlling-Abteilungen. Mir sind diese Plakate schon vor über 15 Jahren begegnet und viele Unternehmen berichten ihre Zahlen IBCS- bzw. nun ISO 24896-konform.

Für dein Excel-Reporting heißt das vor allem: Szenarien erkennt man an der Füllung, nicht an der Farbe. Ist-Werte sind dunkel gefüllt, Planwerte nur umrandet, Forecasts schraffiert. Rot und Grün sind ausschließlich für Abweichungen reserviert – grün, wenn die Abweichung gut ist, rot, wenn sie schlecht ist. Und nicht IST-Zahlen in grün, weil grün nun mal Deine Firmenfarbe ist.

Notation nach ISO 24896: IST dunkel gefüllt, PL umrandet, FC schraffiert, VJ hellgrau; Abweichungen als grüne und rote Balken und NadelnDie wichtigsten Notationsregeln für dieses Beispiel.

Excel kann schraffierte Säulen, Abweichungs-Nadeln und eine automatisch berechnete Kernaussage im Titel nur mit viel Bastelei. Mit Python in Excel schreibst du das einmal als Code – und das Diagramm aktualisiert sich bei jeder Datenänderung. Die vollständigen Detailregeln stehen im Normtext; dieses Tutorial setzt die Grundprinzipien für einen Plan-Ist-Forecast-Vergleich um.

Was du brauchst

  • Microsoft 365 mit Python in Excel. Für Business- und Enterprise-Abos ist es in Excel für Windows (Current Channel) allgemein verfügbar, für Family und Personal je nach Plattform als Vorschau.
    Ich selbst nutze Microsoft 365 Apps for Enterprise E5 Version 2610 im aktuellen Kanal, habe aber auch alles mit Microsoft 365 Family Version 2612 Insider getestet (Stand: 2026-11-17).
  • Eine Internetverbindung – der Python-Code läuft nicht auf deinem Rechner, sondern in der Microsoft-Cloud. Hier also auch noch einmal ganz speziell prüfen, ob die Daten im Firmennetz verbleiben müssen oder in der Cloud-Umgebung verarbeitet werden dürfen.
  • Keine gesonerte Python-Installation – pandas, NumPy und matplotlib sind in Excel bereits geladen. Das Python Anaconda-Paket kann aber leider nicht um andere Pakete erweitert werden.
  • Die Übungsdatei (Download unten) oder deine eigenen Monatsdaten.

Schritt 1: Die Daten als Excel-Tabelle anlegen

Python liest am zuverlässigsten aus einer formatierten „intelligenten“ Excel-Tabelle (Strg+T). Der Vorteil: Kommen Zeilen dazu, wächst der Bezug automatisch mit. In der Übungsdatei heißt die Tabelle tbl_Umsatz und hat zunächst diese Spalten:

MonatPlanIstForecast
Jan820845
Feb780752
…………
Sep1.0201.074
Okt1.0601.085
Nov1.1001.062
Dez1.2501.290

Die Logik ist einfach: Für abgeschlossene Monate gibt es einen Ist-Wert, für die restlichen Monate einen Forecast. Eine Zeile hat also immer entweder Ist oder Forecast. Die Werte sind in T€ (Tausend Euro). In der Übungsdatei rechnen drei graue Formelspalten die Abweichungen zusätzlich in Excel – als Kontrolle für das, was Python später zeigt.

Komplexer würde die Darstellung noch durch den Vorjahresvergleich mit Previous Year (PY) oder einer Restaufwandsschätzung im Projektmanagement. Zudem könnte der Zeitraum natürlich noch um mehrere Jahre vergrößert werden. Szenarien, die später besprochen werden können.

Eine Vorschau auf das Tabellenblatt „Daten“ in der Übungsdatei. Der Datenbereich wurde als Tabelle formatiert und in tbl_Umsatz benannt.

Schritt 2: Den Python-Code kopieren

Kopiere den folgenden Code komplett in die Python-Zelle. Keine Sorge, Du musst ihn nicht auswendig verstehen – darunter erkläre ich die neun Abschnitte. In der Übungsdatei steht derselbe Code im Blatt Python-Code.

# =======================================================================
# Plan-Ist-Forecast-Diagramm nach ISO 24896 (IBCS-Notation) - ohne Gewähr
# Andreas Thehos · Python in Excel
# =======================================================================
# 1) Daten aus der Excel-Tabelle holen
df = xl("tbl_Umsatz[#Alle]", headers=True)
for spalte in ["Plan", "Ist", "Forecast"]:
df[spalte] = pd.to_numeric(df[spalte], errors="coerce")
# 2) Kennzahlen berechnen
df["IstFC"] = df["Ist"].fillna(df["Forecast"]) # Ist, sonst Forecast
df["dPL"] = df["IstFC"] - df["Plan"] # Abweichung absolut (T€)
df["dPLp"] = df["dPL"] / df["Plan"] * 100 # Abweichung relativ (%)
ist_monat = df["Ist"].notna() # True = Ist-Monat
# 3) Notation: Farben und Zahlenformat
DUNKEL, GRUEN, ROT = "#333333", "#5B9B2E", "#D7191C"
farbe = np.where(df["dPL"] >= 0, GRUEN, ROT)
def de(wert, nachkomma=0, vorzeichen=False):
s = f"{wert:{'+' if vorzeichen else ''},.{nachkomma}f}"
return s.replace(",", "X").replace(".", ",").replace("X", ".")
# 4) Gleicher Maßstab für alle T€-Diagramme
oben = max(df["IstFC"].max(), df["Plan"].max()) * 1.18
d_oben = max(df["dPL"].max(), 0) * 1.6 + oben * 0.02
d_unten = min(df["dPL"].min(), 0) * 1.6 - oben * 0.02
hoehen = [oben * 0.30, d_oben - d_unten, oben]
fig, (ax_p, ax_d, ax_h) = plt.subplots(
3, 1, sharex=True, figsize=(11, 7.6),
gridspec_kw={"height_ratios": hoehen, "hspace": 0.12})
x = np.arange(len(df))
# 5) Relative Abweichung ΔPL% als Nadeln (Pins)
ax_p.vlines(x, 0, df["dPLp"], colors=farbe, linewidth=2.5)
ax_p.scatter(x, df["dPLp"], color=farbe, marker="s", s=38, zorder=3)
for xi, v in zip(x, df["dPLp"]):
ax_p.annotate(de(v, 1, True), (xi, v), xytext=(0, 7 if v >= 0 else -7),
textcoords="offset points", ha="center",
va="bottom" if v >= 0 else "top", fontsize=9)
grenze = df["dPLp"].abs().max() * 1.7
ax_p.set_ylim(-grenze, grenze)
# 6) Absolute Abweichung ΔPL als Balken
ax_d.bar(x, df["dPL"], width=0.62, color=farbe)
for xi, v in zip(x, df["dPL"]):
ax_d.annotate(de(v, 0, True), (xi, v), xytext=(0, 3 if v >= 0 else -3),
textcoords="offset points", ha="center",
va="bottom" if v >= 0 else "top", fontsize=9)
ax_d.set_ylim(d_unten, d_oben)
# 7) Hauptdiagramm: PL umrandet, IST gefüllt, FC schraffiert
ax_h.bar(x - 0.14, df["Plan"], width=0.62, facecolor="white",
edgecolor=DUNKEL, linewidth=1.0, label="PL")
ax_h.bar(x[ist_monat] + 0.06, df["IstFC"][ist_monat], width=0.62,
color=DUNKEL, label="IST")
ax_h.bar(x[~ist_monat] + 0.06, df["IstFC"][~ist_monat], width=0.62,
facecolor="white", edgecolor=DUNKEL, hatch="////",
linewidth=1.0, label="FC")
for xi, v, pl in zip(x, df["IstFC"], df["Plan"]):
ax_h.annotate(de(v), (xi + 0.06, max(v, pl)), xytext=(0, 3),
textcoords="offset points", ha="center", fontsize=9)
ax_h.set_ylim(0, oben)
ax_h.set_xticks(x, df["Monat"])
ax_h.set_xlim(-0.6, len(df) - 0.4)
# 8) Achsen entrümpeln, Nulllinien, Beschriftungen
for ax, name in [(ax_p, "ΔPL %"), (ax_d, "ΔPL"), (ax_h, "")]:
for s in ["top", "right", "left"]:
ax.spines[s].set_visible(False)
ax.spines["bottom"].set_visible(ax is ax_h)
ax.tick_params(left=False, labelleft=False, bottom=False)
if ax is not ax_h:
ax.axhline(0, color=DUNKEL, linewidth=1.0)
ax.text(-0.75, 0, name, ha="right", va="center", fontsize=10,
fontweight="bold")
ax_h.legend(loc="upper left", bbox_to_anchor=(1.0, 0.95), frameon=False)
# Trennlinie zwischen IST- und FC-Monaten
grenze_x = x[ist_monat].max() + 0.5
for ax in (ax_p, ax_d, ax_h):
ax.axvline(grenze_x, color="#999999", linewidth=0.8, linestyle=(0, (3, 3)))
# 9) Titelbereich mit Kernaussage (wird automatisch berechnet)
summe_pl, summe_fc = df["Plan"].sum(), df["IstFC"].sum()
abw = (summe_fc / summe_pl - 1) * 100
fig.text(0.125, 0.985, xl("Firma"), fontsize=10, va="top")
fig.text(0.125, 0.955, "Umsatz in T€", fontsize=11, fontweight="bold", va="top")
fig.text(0.125, 0.925, "Jan–Dez 2026 · IST, FC, PL", fontsize=10, va="top")
fig.text(0.9, 0.985,
f"Jahr laut IST+FC: {de(summe_fc)} T€ – "
f"{de(abw, 1, True)} % gegenüber PL",
fontsize=11, fontweight="bold", ha="right", va="top")
fig.subplots_adjust(top=0.88, left=0.125, right=0.9, bottom=0.06)
fig
# Ende - bis fig markieren und kopieren

Was der Code macht

  • Abschnitt 1 – Daten holen: xl("tbl_Umsatz[#Alle]", headers=True) lädt die ganze Tabelle samt Überschriften als pandas-DataFrame. pd.to_numeric sorgt dafür, dass leere Zellen sauber als »kein Wert« ankommen.
  • Abschnitt 2 – Kennzahlen: IstFC nimmt den Ist-Wert und füllt Lücken mit dem Forecast. Daraus entstehen die absolute Abweichung dPL in T€ und die relative Abweichung dPLp in Prozent.
  • Abschnitt 3 – Notation: Ein dunkles Grau für die Szenarien, Grün und Rot nur für Abweichungen. Die kleine Funktion de() formatiert Zahlen deutsch – mit Tausenderpunkt, Dezimalkomma und Vorzeichen.
  • Abschnitt 4 – gleicher Maßstab: Die Höhen der Teildiagramme werden aus den Datenbereichen berechnet. Dadurch ist 1 T€ im Abweichungsbalken genauso hoch wie im Hauptdiagramm – eine Kernregel normgerechter Diagramme.
  • Abschnitt 5 – ΔPL %: Die relative Abweichung als Nadeln mit quadratischem Kopf und Beschriftung.
  • Abschnitt 6 – ΔPL: Die absolute Abweichung als grüne oder rote Balken.
  • Abschnitt 7 – Hauptdiagramm: Die Plan-Säule liegt umrandet leicht versetzt dahinter, davor die gefüllte Ist- oder schraffierte Forecast-Säule.
  • Abschnitt 8 – Aufräumen: Keine y-Achsen, keine Gitternetzlinien – die Werte stehen direkt an den Säulen. Eine gestrichelte Linie trennt Ist- und Forecast-Monate.
  • Abschnitt 9 – Titelbereich: Oben links steht, wer, was und wann; oben rechts die Kernaussage – automatisch berechnet aus den Summen.

Der Zugriff von Python in Excel ist gegenüber einer regulären Python-Installation und der Ausführung von unabhängigen .py-Dateien stark limitiert. Microsoft behandelt Python-Code in Arbeitsmappen als potenziell nicht vertrauenswürdig und führt ihn deshalb abgeschottet aus. Mehr dazu in einem anderen Artikel oder in meinen Beratungen und Seminaren.

Nutzt Du ein Englisches Excel?
In einer englischen Excel-Oberfläche heißt der Tabellenbezug tbl_Umsatz[#All] statt [#Alle]. Am sichersten: Lösch die Zeile mit xl(...) und klick im Bearbeitungsmodus einfach auf die Tabelle – Excel setzt den passenden Bezug selbst ein. Ich muss meinen Code aber auch ständig kontrollieren, wenn ich etwas mit der KI erstelle. Häufig steht dann dort im Code [#All] und muss angepasst werden.

Schritt 3: Eine Python-Zelle anlegen

Klick in eine leere Zelle neben der Tabelle – im Bild oben und in der Übungsdatei ist das J4 (grün umrandet). Dann hast Du drei Möglichkeiten:

  1. =PY eintippen und mit Tab bestätigen,
  2. Register Formeln → Python einfügen wählen oder
  3. das Tastenkürzel Strg+Alt+Umschalt+P drücken.

Vor der Bearbeitungsleiste erscheint jetzt ein grünes PY-Symbol. Ab hier ist die Zelle ein kleiner Python-Editor: Die Eingabetaste erzeugt einen Zeilenumbruch, statt die Zelle zu verlassen.

Füge nun den Code in die Zelle mit STRG + V oder WIN + V ein.

Die Zelle J4 wurde angewählt und =py geschrieben, dann Tab gedrückt und der Code eingefügt.

Schritt 4: Bestätigen und Diagramm anzeigen lassen

Bestätige die Eingabe mit STRG + ENTER.

Bestätige die Python-Zelle mit Strg+Eingabe. Nach ein, zwei Sekunden (der Code läuft in der Cloud) zeigt die Zelle ein Bildobjekt Image an. Ein Klick auf das kleine Kartensymbol öffnet eine Vorschau

Anschließend sollte in der Zelle [PY]Image stehen (1). In der Bearbeitungsleiste siehst Du den Code (2). Den kompletten Code kannst Du aber auch im Register Formeln über den Python Editor (3) auf der rechten Seite anzeigen lassen (4).

Zunächst wird noch keine Grafik angezeigt.

Du kannst Dir auch eine Vorschau auf das Objekt anzeigen lassen.

Dies Grafik hättest Du gerne.

Mit STRG + SHIFT + ALT + C (nimm beide Hände) kannst Du nun ein frei platzierbares Bild erzeugen. Bzw. auch mehrere, wenn Du die Kombination öfter drückst. Die Bilder sind mit der Python-Zelle in J4 verbunden und werden bei Änderungen in der Tabelle tbl_Umsatz aktualisiert.

Die Grafik kann verschoben und mit gedrückter Taste Alt auch an den Ecken am Raster eingerastet werden.

Damit das Diagramm dauerhaft im Blatt sichtbar ist, hast du zwei Wege:

  • Über den Zellen: Rechtsklick auf die Zelle und die Option zur Anzeige über den Zellen wählen (englisch Display Plot over Cells). Das Bild lässt sich dann frei verschieben und skalieren – ideal für Berichte.
  • In der Zelle: In der Bearbeitungsleiste das Menü Python-Ausgabe öffnen und Excel-Wert wählen. Das Bild sitzt dann in der Zelle. Zieh die Zeile und Spalte groß, damit es lesbar ist.

Du findest diese Optionen aber auch mit einem rechten Mausklick im Kontextmenü des Bilds.

Das Ergebnis im Detail

Das fertige Diagramm: Musterfirma GmbH, Umsatz in T€, Januar bis Dezember 2026.

Lies das Diagramm von oben nach unten: Die Kernaussage im Titel sagt Dir sofort, wo das Jahr voraussichtlich landet – hier bei 11.882 T€, also 1,6 % über Plan. Die Nadeln zeigen, in welchen Monaten es prozentual gehakt hat (Februar, Mai, Juli, November), die Balken darunter, wie viel das in Euro ausmacht.

Besonders wichtig ist Baustein 3: Weil die Abweichungsbalken im gleichen Maßstab wie die Umsatzsäulen gezeichnet sind, wirken sie bewusst klein. Das ist kein Fehler, sondern realistische Ehrlichkeit – 58 T€ Abweichung bei rund 1.000 T€ Umsatz sind klein. Die Prozent-Nadeln darüber machen die Muster trotzdem gut sichtbar.

Schritt 5: Testen – ändere einen Forecast

Jetzt kommt der eigentliche Vorteil. Ändere in der Tabelle den Forecast für November von 1.062 auf 1.150 und drück die Eingabetaste. Python rechnet neu: Der rote Balken im November wird grün, die Nadel springt nach oben, und die Kernaussage im Titel passt sich an.

Noch spannender: Trag für Oktober einen Ist-Wert ein und lösch dort den Forecast. Die Oktobersäule wird dunkel, und die Trennlinie zwischen Ist und Forecast wandert automatisch einen Monat weiter.

In weiteren Schritten könnte man den Code natürlich noch so anpassen, dass der Forecast nicht angezeigt wird, falls es einen Ist-Wert geben sollte.

Übungsdatei herunterladen

In der Mappe findest du vier Blätter: Start mit der Schritt-für-Schritt-Anleitung und Zusatzaufgaben, Daten mit der Tabelle und der markierten Python-Zelle, Python-Code zum Kopieren und Lösung mit dem fertigen Diagramm als Bild

Download der Datei von Nextcloud unter:
https://cloud.at-excel.de/index.php/s/f4QtAZiRGrJwf5N?dir=/YouTube/2026-11-17

Typische Fehler und schnelle Lösungen

  • Die Eingabetaste macht nur eine neue Zeile: Richtig so – Python-Zellen bestätigst du mit Strg+Eingabe.
  • #PYTHON! in der Zelle: Der Code hat einen Fehler. Klick auf die Fehlermeldung – Excel zeigt die Python-Fehlermeldung im Diagnose-Bereich. Häufigster Grund: Ein Spaltenname stimmt nicht exakt (z. B. »Forecast « mit Leerzeichen).
  • KeyError: ‚Plan‘: Die Überschrift fehlt im Bezug. Prüfe, ob headers=True gesetzt ist und der Bezug [#Alle] enthält.
  • #BESCHÄFTIGT! oder #VERBINDUNG!: Excel wartet auf die Cloud oder hat keine Verbindung. Kurz warten bzw. Internetverbindung prüfen.
  • Werte sind Text: Steht in einer Zelle »1.085 T€« als Text statt der Zahl 1085, wird sie zu »kein Wert«. Gib nur Zahlen ein und nutze das Zahlenformat für die Darstellung.
  • Falsche Excel-Version. Da wende Dich bitte an Deine IT, Du benötigst schon eine monatlich aktuelle Version von Microsoft Excel 365.

Wenn diese Meldung kommt, vielleicht einfach kurz speichern, schließen und neu starten.

Auch bei aktueller Excel 365-Version kann es passieren, dass Dir der Bereich Python lokal fehlt. Schau doch mal online bei Excel oder speichere die Datei, schließe sie und öffne sie erneut.

Python oder klassisches Excel-Diagramm?

Python in Excel ist kein Ersatz für jedes Diagramm. Für diesen Anwendungsfall spielt es aber seine Stärken aus:

Python in ExcelKlassisches Excel-Diagramm
Schraffur, Nadeln, versetzte Plan-Säulenper Code, wiederverwendbarnur mit Hilfsreihen und Tricks
Kernaussage im Titelautomatisch berechnetnur über Zellbezug im Titel
Gleicher Maßstab über Teildiagrammewird berechnetmanuell abgleichen oder mit Hilfskonstrukten
Interaktivität (Mouseover, Filter)nein, statisches Bildja
VoraussetzungMicrosoft 365 und Internetjede Excel-Version

Häufige Fragen

Brauche ich Python-Kenntnisse?

Nein. Du kannst den Code kopieren und nur die Tabellen- und Spaltennamen anpassen. Wer mehr will, ändert Farben, Texte oder ergänzt ein Vorjahr – die Kommentare im Code zeigen, wo.

Läuft das auch auf dem Mac oder im Browser?

Die Verfügbarkeit hängt von deinem Microsoft-365-Abo und der Plattform ab und wird laufend erweitert. Auf nicht unterstützten Plattformen kannst du die Mappe öffnen, die Python-Zellen rechnen dort aber nicht neu.

Verlassen meine Daten den Rechner?

Ja – Python in Excel rechnet in der Microsoft-Cloud. Für vertrauliche Unternehmenszahlen solltest du vorher klären, ob das in deinem Unternehmen freigegeben ist.

Zum Schluss….

Hey! Schön, dass Du diesen Artikel nach so langer Inaktivität in diesem Blog gefunden hast. Ich hoffe, er ist Dir oder Deinen Kolleg*innen nützlich.

In den letzten Jahren habe ich mich beruflich sehr viel u. a. in den vielen Microsoft 365 Apps, Python, IT-Sicherheit & KI unterwegs. War dabei aber in den sozialen Medien sehr zurückhaltend. Neben der Beschäftigung mit Microsoft-Produkten bin ich immer häufiger auch mit Software MADE IN EU unterwegs. Nextcloud, LibreOffice, Collabora Office und die damit verbundenen Dienste haben mir beiden letzten beiden Microsoft Cloud-Ausfällen meine mit dem Kunden vereinbarten Beratungstermine gerettet – ich bin gerne redundant unterwegs. Mehr Arbeit, mehr Ausfallsicherheit… und die Wahl, wo die Daten liegen sollen.

Da seit Corona fast alle Beratungen nur noch online stattfinden, bin ich in der Zeit, die ich früher für diesen Blog und YouTube genutzt hatte nun in meinen sozialen Aktivitäten und im Sport aktiv. Irgendwo muss man ja mal Menschen live sehen und sich bewegen. Meine Beschäftigung mit den Themen Gewaltfreie Kommunikation nach M. Rosenberg und Sozialpsychologie haben mir neben privaten Erkenntnissen auch viele wertvolle Tools für die Arbeit geben können. Sportlich bin ich in Dortmund und Bochum beim Takemusu Aikido angekommen, als Übungsleitung führe ich zusammen mit einem tollen Team im institutionellen Rahmen Selbstbehauptungs- und Selbstverteidungskurse für Frauen in Bochum durch.
2022 war aber auch die Bereitschaft der fast 400.000 monatlichen YouTube-Zuschauer sehr gering, den Kanal zu abonnieren oder ein Video zu liken bzw. zu kommentieren. Ich würde mich freuen, wenn dieser neue Anlauf gelingt.

Ich möchte aber nun wieder mehr zu aktuellen Themen in meinem Blog und bei YouTube platzieren, mir selbst hilft das natürlich auch bei meinen Vor- und Nachbereitungen für meine beruflichen Aktivitäten. Es sollte sich also lohnen, mal in den nächsten Tagen erneut auf diesen Blog zu schauen.

Wenn Du beruflich mit mir in Kontakt treten willst und an Leistungen u. a. zu Microsoft 365, Power BI, Copilot und speziell natürlich auch Excel interessiert bist, kontaktiere mich bitte unverbindlich über die E-Mail thehos@at-training.de. Per Chat bin ich unter andreas@at-excel.de nur nach vorheriger Freigabe Deines Tenants erreichbar. Du erreichst mich auch unter LinkedIn unter meinem Namen.

Link führt zu LinkedIn-Artikel: https://www.linkedin.com/feed/update/urn:li:activity:7528414329461760000/
Unter LinkedIn kann auch diskutiert werden.

So… das war es für heute!
Viele Grüße / Glück Auf! / Ad Astra!
Andreas

Power Query und Pivot-Tabelle mit Datenmodell

Ein weiterer Workshop! Doch dieses mal werden wir mit Hilfe von Power Query Daten ins Excel importieren und von dort aus mit dem Datenmodell weiterarbeiten. Mit Hilfe von DAX-Funktionen werden Measures gebildet, die dann in der Pivot-Auswertung zur Verfügung stehen.

Übungsdateien des Videos:

Zunächst wieder die reinen Verkaufsdaten in einer Liste auf einem Tabellenblatt:

Anschließend eine Datei mit einer Kalendertabelle auf einem Tabellenblatt und zwei Tabellen auf dem zweiten Tabellenblatt:

Die folgenden Steuerdaten werden zusätzlich benötigt. Hier greife ich per Webanfrage auf die Tabelle zu. Dafür muss der Link zu diesem Blog-Beitrag kopiert werden:
https://thehosblog.com/2022/03/04/power-query-und-pivot-tabelle-mit-datenmodell/

LandMehrwertsteuer
Deutschland19%
Niederlande21%
Österreich20%
Schweiz7,7%
Umsatz- bzw. Mehrwertsteuersätze. Ohne Gewähr. Stand: 4. März 2022

Die über Power Query abgefragten Tabellen werden im Datenmodell oder schon vorab in Power Query miteinander verknüpft. Informationen, die später in Pivot nicht verfügbar sein sollen, werden vorab im Datenmodell ausgeblendet. So benötigt man nicht den Umsatz der ursprünglichen Tabelle, sondern das mit Hilfe der DAX-Funktion berechnete Measure. Auch die Filialen tauchen tausendfach in den Sales-Daten auf. Hier benötige ich lediglich das in der Filialtabelle enthaltene Feld für spätere Filterzwecke.

Sternschema im Excel-Datenmodell: Umsatztabelle mit Kalender, Filial- und Steuertabelle verknüpft
Datenmodell zeigt mir die Filter- und Auswertungsbeziehungen in der Diagrammsicht

Mit den ins Datenmodell importierten Daten können nun nach der Erstellung von DAX-Funktionen Auswertungen gefahren werden.

Pivot-Tabelle mit monatlichen Netto-, Steuer- und Bruttowerten aus dem Datenmodell
Monatliche Auswertung mit Übersicht über Netto, Steuer und Brutto

Das Tabellenblatt kann nun kopiert und in Kopie weiter modifiziert werden.

Pivot-Tabelle mit Quartalswerten brutto je Filialstandort
Quartalszahlen über Bruttowerte pro Filialstandort

Danke an den Hinweis zum Fehler im Video an Olaf!

Ukraine-Nothilfe – Spende des YouTube-Erlöses

Liebe Excel-/Office-/Microsoft 365-Freund:innen,

wir sind vermutlich alle sind von der aktuellen politischen Lage und der Not und dem Leid der Menschen in der Ukraine geschockt. Doch was kann man selbst tun? Wie kann man helfen? Wie wird sich die Situation entwickeln? Pures Kopfkino… ich selbst habe nicht im Ansatz eine Vorstellung davon, was die Menschen dort gerade durchmachen müssen.

Doch was sind unsere Talente, die wir zur Linderung der Not beisteuern können?

Wer schon länger meinem YouTube-Kanal www.youtube.com/athehos folgt, der kennt meine regelmäßigen Spendenaktionen für die Kindertafel in Bochum und Wattenscheid oder hat vielleicht noch die Förderung einer politischen Studienreise von zwanzig Schülerinnen und Schülern nach Polen in die KZ-Gedenkstätte Ausschwitz-Birkenau in Erinnerung.

Zunächst werde ich meine YouTube-Werbeeinnahmen spenden.

Wir – ein Familienbeschluss – haben uns für die Aktion „ARD / Nothilfe Ukraine“ entschieden und die ersten 1.517 Euro überwiesen, die Dank eurer Klicks auf die Videos durch YouTube vergütet wurden.

https://www.spendenkonto-nothilfe.de/

Bestehend aus:

  • Bündnis Entwicklung Hilft – Gemeinsam für Menschen in Not e. V.
  • Aktion Deutschland Hilft e.V.

Ich werde offene Online-Seminare anbieten, deren Teilnahmegebühren zu 100% gespendet werden.

In Kürze werde ich hier einige Online-Seminare über Microsoft Teams in der Länge von 2 x 2 Stunden anbieten, welche rund um meine regulären Arbeitszeiten und Kundenverpflichtungen platziert werden.

Thematisch versuche ich Themen zu identifizieren, die möglichst interessant für euch sein könnten. Bei aktiver Teilnahme gibt es dann auch ein personalisiertes Teilnahmezertifikat per PDF.

Hier sammle ich schon einmal anonym eure Themenwünsche und Feedback :
https://forms.office.com/Pages/ResponsePage.aspx?id=esdKmr6lOkiliY-fR9wTKwfkrcoXzFRJiOWiUOmmDZxUQVowTlY1SERFME5ISUxCMUNCQ1dRTTRQUy4u

Bei generellem Interesse werdet ihr hier bald die Themen, Termine und Konditionen finden. Ansonsten hoffe ich natürlich, dass es euch gut geht und wir die Situation gemeinsam meistern!

Bleibt gesund
Euer Andreas

Pivot-Tabellen und Power Query – Neue Reihe

Du möchtest aus Deinen Daten eine für Deine Zwecke aussagekräftige Auswertung erzeugen? Du musst Daten aus diversen Datenquellen zusammenfügen und die Informationen verdichten?

Dann sind Power Query und Pivot-Tabellen genau die richtigen Werkzeuge für Dich!
Einen kurzen Blick in die neue Reihe findest Du im folgenden Intro-Video:

Videolink: https://youtu.be/sE94pP1KODQ

Doch bevor ich auf die modernen Pivot-Tabellen mit Datenmodell eingehe, möchte ich mit Dir eine klassische Pivot-Tabelle aufbauen. Im folgenden Bild erkennst Du das Endergebnis des Videos.

Aufbau eines Berichts mit Hilfe von Datenschnitten zur Filterung, einer zeitlichen Zusammenfassung in Tabelle, Diagramm und Textfeld.
Endergebnis des Videos!

Und so sahen die Ausgangsdaten aus Datei 1 aus.

Ausgangssituation in den Daten – eine typische Liste

Die Technik wird auch noch für Online- und Mac-Nutzer benötigt. Für einige genutzte Funktionen benötigst Du Excel 2019 oder neuer (z. B. die Funktion TEXTVERKETTEN). Die anderen Techniken funktionieren aber schon bei älteren Excel-Versionen.

Die Übungs- und Ergebnisdateien für das folgende YouTube-Video kannst Du hier herunterladen:

Ausgangsdatei für das erste Trainingsvideo:

Ergebnisdatei:

An diesen Pivot-Tabellen – die tolle und mächtige Ergebnisse liefern – gibt es aber einige Schwachstellen, welche ich dann in späteren Videos mit Hilfe der Pivot-Tabellen mit Datenmodell und den DAX-Funktionen (data analysis expressions) beheben werde.

Wenn Du mich auf dieser Reise durch Power Query und Pivot begleiten magst, würde ich Dich bitten, die Videos zu kommentieren, zu bewerten und den Kanal zu abonnieren. Für mich der einzige Ansporn, weiter kostenlosen Content bei YouTube hochzuladen.

Videolink: https://youtu.be/MTcCcrQ3rM0

Ich freue mich auf eine spannende Reise
Andreas

P.S.: Die Reihe steht Dir kostenlos zur Verfügung, Du kannst die Videos auch gerne ins Unternehmen verlinken. Die Übungsdateien dürfen in unveränderter Form unter Nennung der Quelle und des Autors kommerziell genutzt werden.

Excel – Drucken und Seitenlayout

Drum prüfe, wer was bindet!
Beim Ausdruck kann man so viel verkehrt machen, dabei kann man einen ordentlichen Ausdruck so gut vorbereiten und normale Fehler schnell vermeiden.

Soll das Blatt vielleicht ins Querformat gelegt werden und neben einem Logo eine vernünftige Überschrift tragen? Sollen Überschriften bei mehrseitigen Ausdrucken immer oben auf der Seite wiederholt werden? Möchte man Fehldrucke vermeiden?

Allein die Laufzeit des Videos von gut 30 Minuten zeigt, dass das Thema Seitenlayout und Drucken recht komplex sein kann.

Videolink: https://youtu.be/zosJoTw0JGA

Dateien zum Download:

Ich freue mich wie immer auf euer Feedback unterhalb des Videos.

Andreas

Bisherige Beiträge zu dieser Reihe:
Folgen 1-3 https://thehosblog.com/2020/11/17/neuer-excel-grundkurs/
Folge 4 https://thehosblog.com/2020/11/18/excel-grundkurs-autoausfullen/
Folge 5 https://thehosblog.com/2020/11/19/excel-grundkurs-bezuge/
Folge 6 https://thehosblog.com/2020/11/20/excel-grundkurs-aufbau-von-formeln/
Folge 7 https://thehosblog.com/2020/11/23/excel-grundkurs-einstieg-in-funktionen/
Folgen 8 und 9: https://thehosblog.com/2020/11/26/excel-grundkurs-zahlenformat-wahrung/
Folge 10: https://thehosblog.com/2020/11/28/excel-grundkurs-die-funktion-runden/
Folge 11: https://thehosblog.com/2020/11/30/excel-grundkurs-tabellen/
Folge 12: https://thehosblog.com/2020/12/14/excel-grundkurs-drucken-und-seitenlayout/

Unterschiedliche Maßeinheiten addieren – Rezepte in Excel

Du pflegst Deine Rezepte in Excel-Listen und willst jetzt kurz vor Weihnachten mal eben überblicken, was Du so benötigst? Du hast aber die Mengen in unterschiedlichen Maßeinheiten wie Gramm, Kilogramm, Unze etc. angegeben?

Einige dieser Maßeinheiten kann Excel umrechnen. Leider kennt es nicht unser deutsches Pfund mit 500 Gramm, dafür aber andere Maßeinheiten. Schaue am besten ins Video, um Dir einen Überblick zu verschaffen.

Werte unterschiedlicher Maßeinheiten addieren? Vielleicht für die nächsten Rezepte?
UMWANDELN in einfacher Verwendung und als Matrixfunktion

Die Funktion UMWANDELN ermöglicht es mir, Zahlen von einer Maßeinheit in eine andere Maßeinheit umzurechnen. Doch was, wenn Werte unterschiedlicher Maßeinheiten addiert werden müssen?

Mit LET und SEQUENZ stehen mir im neuen Excel 365 mächtige Funktionen zur Verfügung.

Mit LET und SEQUENZ weden die Möglichkeiten von Excel 365 genutzt

Hier könnt ihr die Datei online anschauen und herunterladen:
https://atexcel-my.sharepoint.com/:x:/g/personal/andreas_at-excel_de/EQozC_Nftd5Dk7nxaeWa9l4B7ARw2obu_PIlBQ8wV3s4CA?e=oAtV8H

Datei zum direkten Download:

Videolink: https://youtu.be/HVbRF3Lu90Y

Ich wünsche euch eine Frohe Weihnachtszeit!
Bleibt gesund und hoffentlich bis bald mal wieder auf einem der Excel-Stammtische!
Andreas

P.S.: Merci an meine Excel-Freunde, die den Wunschzettel http://www.wunschzettel.de/thehos gefunden haben 🙂

BINGO! mit Excel 365

Hey! Alle lieben BINGO, oder?
Und Excel ist auch noch so gut und liest uns alle ca. 20 Sekunden die neuen Zahlen vor!

Zu Nikolaus 2020 habe ich die BINGO-Datei mit Excel 365 erstellt, da mich hier einige Formeln gereitzt haben. Dann noch „ein wenig“ VBA programmiert, damit die zufälligen Zahlen von 1 bis 75 vorgelesen werden, bis jemand den STOPP-Button drückt.

Da ich VBA nutze, funktioniert der Code nicht im Browser.

Generiere auf Knopfdruck bis zu 21 Spielfelder

Die Felder werden zufällig erstellt. Unter dem B nur die Zahlen 1 bis 15, unter dem I entsprechend 16 bis 30, unter dem N 31 bis 45, unter dem G die Zahlen 46 bis 60 und unter dem O die Zahlen 61 bis 75. Jede Zahl natürlich nur einmal. Das mittlere Feld dient als Joker.

Die Seiten sind so vorbereitet, dass man entsprechend die Spielscheine drucken oder als PDF erstellen und an seine online-BINGO-Mitspieler/-innen schicken kann.

7 Blätter werden gedruckt

Wenn das Spiel nun losgeht, sieht der Spielführer zur Kontrolle auf diesem Blatt auch, welche Zahlen passen und entsprechend fünf Treffer in einer horizontalen, diagonalen oder vertikalen Reihe ergeben.

Die Steuerzentrale der Spielleitung

Hier natürlich nicht Schummeln!

Auf dem anderen Blatt lässt sich dann prüfen, ob ein BINGO oder ein weiteres erzielt wurde.

In der Regel sieht die Spielleitung nur die folgenden drei Schalter.

Die Bildchen sind aus den neuen Microsoft 365-Piktogrammen

Mit Spiel neu Starten wird ein Spiel komplett neu begonnen. Wenn jemand BINGO ruft, muss man auf die mittlere Schaltfläche klicken, der Spielverlauf wird dann unterbrochen und kann sofort weitergeführt werden oder erst so lange Warten, bis jemand rechts auf WEITER drückt.

Für das Spiel selbst muss natürlich der Ton eingeschaltet sein, sonst kann Excel ja nichts vorlesen.

Datei zum Download als XLSM-Datei wegen der Programmierung. Bitte nur Herunterladen, wenn ihr fremden VBA-Code ausführen dürft. Vielleicht einfach vorher mit ALT + F11 in den Code hineinschauen.

Videolink zu YouTube: https://youtu.be/gQvdEb24xpM

Hier nur der Code zur Demonstration. Geht bestimmt viel feiner 😉

Solltet ihr noch Fehler finden, korrigiere ich natürlich gerne meine Version.

'### BINGO! von Andreas Thehos, 2020-12-06             ###
 '### Läuft wegen einige Funktionen nur unter Excel 365 ###
 Global Zahlen(1 To 75, 1 To 2) As String 'In dieses Array werden die Ziehungen gespeichert und die Ausgabezeit vermerkt.
 Global Zeit As Date                      
 Global i As Integer                      
 Global Dauer As Integer      
            
 Public Sub BINGO_initialize()
 Dim z As Integer
 Dim NewGame As Integer
 Dim WerteGefuellt As Boolean
 Dim Bereich As Range
 Dim Zelle As Range
 Set Bereich = Range("BINGOWerte")       
 Dim ws As Worksheet                      
 Set ws = ThisWorkbook.Sheets("Ziehungen")
     Dauer = Range("Dauer").Value
 NewGame = MsgBox("Möchtest Du ein neues Spiel starten?", vbYesNo, "Neues Spiel") If NewGame = 7 Then Exit Sub  
ws.Columns("Z:AB").Clear          
WerteGefüllt = False Do Until WerteGefuellt = True 
Application.Calculate     
If Range("WerteEinmalig").Value = 1 Then WerteGefuellt = True 
Loop z = 1 For Each Zelle In Bereich.Cells     
Zahlen(z, 1) = Zelle.Value     
z = z + 1 
Next 
Range("SpielAn") = 1 
Zeit = Now + TimeSerial(0, 0, Dauer) 
i = 1 
BINGO_START (Zeit)
 End Sub
 Public Sub BINGO_run()
 Dim NewGame As Integer
 Dim WerteGefuellt As Boolean
 Dim Zeit As Date
 Dim Bereich As Range
 Dim Zelle As Range
 Dim Bingo As Integer
 Dim ws As Worksheet
 Dim Dauer As Integer
 Set Bereich = Range("BINGOWerte")
 Set ws = ThisWorkbook.Sheets("Ziehungen")
 Dauer = Range("Dauer").Value
 If Range("SpielAn") = 1 Then
     Zeit = Time
     Application.Speech.Speak Zahlen(i, 1)
     Zahlen(i, 2) = Zeit
 ws.Cells(9 + i, 26).Value = Zahlen(i, 1) ws.Cells(9 + i, 27).Value = Zahlen(i, 2) Zeit = Zeit + TimeSerial(0, 0, Dauer) i = i + 1
 End If
 If i = 76 Then Exit Sub
     If Range("SpielAn") = 1 Then
        Zeit = Now + TimeSerial(0, 0, Dauer)
        BINGO_START (Zeit)
     Else
        Bingo = MsgBox("BINGO?", vbYesNo, "Unterbrechung")
        If Bingo = 6 Then
            MsgBox "Das Spiel wird unterbrochen. Herzlichen Glückwunsch! Weiter mit WEITER!", vbOKOnly, "Unterbrechung"
            Exit Sub
        Else
            Range("SpielAn") = 1
            Zeit = Now + TimeSerial(0, 0, Int(Dauer / 2))
            BINGO_START (Zeit)
        End If
 End If
 End Sub
 Sub BINGO_START(Zeit)
     Debug.Print Zeit
     Application.OnTime Zeit, "Bingo_run"
 End Sub
 Sub WEITER()
     Range("SpielAn") = 1
     BINGO_run
 End Sub
 Sub BingoUnterbrechung()
  Range("SpielAn").Value = 0
 End Sub
 Sub Scheine_generieren()
 Dim Scheine As Range
 Dim Zielschein As Range
 Set Scheine = Range("Scheine")
 For Each Zielschein In Scheine.Cells     Range("Beispielschein").Copy     Range(Zielschein).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Next
 End Sub

Viel Spaß und Glück beim BINGO!
Es gibt sicher auch noch einigen Verbesserungsbedarf.

Eine frohe Weihnachtszeit wünscht
Andreas

P.S.: Hey, Jürgen und René! Danke euch von Herzen. Freut mich doch jedes mal, wenn man an einander denkt.
P.P.S.: Meine Amazon-Wunschliste habe ich gelöscht und bin auf https://www.wunschzettel.de/thehos umgestiegen.

Lineare Funktion mit Excel – freier Download

Meine lineare Funktionsgleichung f(x) = mx + b beschreibt eine Gerade mit Steigung m und y-Achsenabschnitt b. Das bedeutet, dass für jeden ganzen Wert von x der Wert von y um den Wert m ansteigt, fällt oder bei m=0 konstant bleibt.

Ich stelle euch hier eine Excel-Datei zur freien Verfügung, in der ihr die Werte für m und b in die oberen orangefarbenen Felder eingeben könnt. Die Wertetabelle wird sofort ermittelt und der Graph in das feste Koordinatensystem eingetragen (insofern er x- und y-Werte im angegebenen Ausschnitt hat).

Vielleicht hilft euch der Graph, für den Mathematik-Unterricht zu üben. Ändert die Werte der Steigung und vom y-Achsenabschnitt, um die Auswirkung auf den Graphen direkt zu sehen.

Darstellung eines Graphen mit der Funktionsgleichung f(x) = 0,25x
Lineare Funktion mit Steigung m = 0,25 und Achsenabschnitt b = 0

Das Koordinatensystem wurde auf den Wertebereich -5 bis +5 eingeschränkt. Es können leider keine Brüche wie ein Drittel 1/3 dargestellt werden. Bei einer Steigung von m = 1/3 bitte folgendes eingeben: =1/3. Die Darstellung erfolgt dann als Dezimalzahl 0,33.

Die Wertetabelle hat von mir einen mittleren x-Wert bekommen, den ihr frei ändern könnt. Die beiden Werte davor und dahinter können ebenfalls mit einem frei wählbaren Abstand eingetragen werden.

Hier noch eine Funktionsgleichung mit negativer Steigung m und negativem y-Achsenabschnitt.

Lineare Funktion mit Steigung m = -2 und Achsenabschnit b = -2

Download der XLSX-Datei:

Die Datei kann im Rahmen der Creative-Common-Lizenz CC BY-ND 4.0 verwendet werden. Mehr Infos dazu: https://creativecommons.org/licenses/by-nd/4.0/deed.de

Das besondere an diesem Diagramm ist, dass die Skalierung auf den beiden Achsen für x und y identisch ist und nicht von Excel verzerrt wird.

Wie diese Datei erzeugt wird und das Diagramm erstellt wird, möchte ich in späteren Videos bei YouTube https://www.youtube.com/athehos erläutern.

Erst einmal viel Spaß beim Ausprobieren
Andreas