Soll/Ist-Vergleich zweier Tabellen

22.08.2025 03:31:45

StefanA89

AW: Soll/Ist-Vergleich zweier Tabellen

22.08.2025 18:31:38

Yal

Hallo Stefan,

das ist eine Fragestellung, die leicht ausserhalb des klassischen Excel-Umfangs liegt. Die Lösung besteht daran, deine Daten so zu transformieren, dass Ist und Soll miteinander kombinierbar werden. Es geht am einfachste mit Power Query.

Davor: in Tabelle "Soll" steht die Spaltenüberschriften "Aug" und "Sep". Ändere diese in "Aug 2025" und "Sep 2025". Es wird daraus "Aug 25" und "Sep 25". Passt aber trotzdem.

- klicke auf die Tabelle "Abweichung", Menü "Daten", "aus Tabelle/Bereich" (in Excel 2016 vielleicht leicht abweichende Titel. Wir haben immerhin 2025)
- Du bist in Power Query Editor
- links in der Liste, rechtsklicke auf die Abfrage "Abweichung" (per Default haben Queries dieselbe Name wie ihre Quelle)
- wähle "Duplizieren"
- rechtkslicke diese neue Query und "umbenennen" in "Soll". Es geht auch über "Abfrage-Eigenscahften" rechts oben.
- klicke auf der erste Schritt "Quelle" der Query "Soll", ändere in der Bearbeitungsleiste (entspricht Formel-Bearbeitungsleiste in Excel) die Quellenname von "Abweichung" in "Soll". Achte, dass PQ streng case sensitiv ist.
- Du hast jetzt deine zwei Datenquelle in Power Query angebunden. Jetzt kann man spielen.
- klicke die Query "Abweichungen"
- markiere die Spalte "Datum", indem Du im Spaltenkopf klicke. Es geht in PQ alles Spaltenweise und Spalten werden immer über den Spaltenkopf angesprochen.
- Menü "Transformieren", "Datum", "Monat", "Monatsbegin"
- jede neue Transformationsschritt wird in der Liste der "Angewendete Schritte" gesammelt. Es wird Schritt für Schritt programmiert.
- die Ergebniszeile, die Du in jede Tabelle hast, führt zu eine Störung. In Spalte "Datum" die Einträge mit "(NULL)" wegfiltern,
- Spalten "Datum" und "Stadt" markieren,
- Menü "Transformieren", "Gruppieren nach", in untere mittleres "Vorgang", "Summe" auswählen, rechts davon Spalte "geöffnet" einstellen, ganz links Spaltename "geöffnet" eingeben.
- Wir haben jetzt für jede Stadt | Monat eine Summe der Öffnungszeiten

Jetzt müssen wir die "Soll" vorbereiten
- Abfrage "Soll", links in der Abfrageliste wählen/aktivieren
- der zweite Schritt (rechts in "angewendete Schritte") "Geänderter Typ" löschen. Es ist auf die jetzige Stand fixiert, macht eventuell Problem, wenn "Okt 25" dazukommt.
- hier auch Ergebniszeile durch Filter der "(NULL)" in Spalte "Stadt" weg
- Spalte "Stadt" markieren,
- Menü "Transformieren", "Spalte entpivotieren", "andere Spalten entpivotieren" (wäre es nur für diese Entpivotierungsfunktion, muss man PQ kennen!)
- in der Bearbeitungsleiste den "Attribut" in "Datum" ändern
- Spalte "Datum" markieren (und nur diese),
- Rechtsklick, "Typ ändern", "Datum". Hätten wir nicht den " 25" nach den Monate, würde kein Datum richtig erkannt!

- Abfrage "Abweichung" aktivieren
- Menü "Start", "Abfragen zusammenführen", "Abfragen zusammenführen"
- in mittleren Auswahlfeld die Abfrage "Soll" einstellen,
- in beide Abfragen die Feld "Stadt" und "Datum", nacheinander mit "Strg" markieren. Achten auf die angezeigte "1" und "2", weil 1 mit 1 und 2 mit 2 abgeglichen werden.
- Join-Art soll bereits richtig sein: "Linker äußerer Join (alle aus erste, übereinstimmende aus zweiter)"
- im Spaltenkopf der neue Spalte "Soll" auf dem Doppelpfeil-Symbol klicken,
- ganz unten "Präfix", Haken weg
- nur "Wert" anhaken lassen
- in der Bearbeitungsleiste, den zweite {"Wert"} in {"Soll"} ändern
- man sieht jetzt, dass wir vielleicht die Spalte "geöffnet" in "ist umbenennen hätte sollen. Kann man immer noch: Doppelklick auf dem Namen "geöffnet" und in "Ist" ändern

- Menü "Spalten hinzufügen", "benutzerdefinierte Spalte",
- Spaltenname "Delta" eintragen,
- Formel =[Soll] - [Ist] eintragen/zusammenklicken
- nochmal Benutzerdefinierte Spalte
- Spaltenname "Abw." eintragen
- Formel = [Ist] / [Soll] eintragen
- Spalten "Ist", "Soll" und "Delta" markieren,
- Rechtsklick, "Typ ändern", "Dauer"
- Spalte "Abw.", "Typ ändern", "Prozentsatz"

Wir sind mit der Transformation fertig.
- Menü "Datei", "Schliessen & laden in ..."
- "nur Verbindung erstellen" wählen
- wir sind wieder in Excel klassik.
- rechts in der Liste der Abfragen, Abfrage "Abweichung" rechtsklicken, "Laden in ..." wählen, "Tabelle" wählen.
Fertig.
Die Prozentzahl-Typisierung von PQ kommt nicht durch. Spalte "Abw." markieren, "Zelle formatieren", "Prozent" wählen.

Ändern sich Daten in den Quellen, einfach Ergebnis-Ausgabetabelle rechtsklicken und "aktualisieren" klicken.
Abfragen werden in der Datei gespeichert, sind aber keine Makro. Die Datei bleibt ".xlsx". Beim Öffnen der Datei werden die Abfrage nicht sichtbar. Entweder die Ergebnistabelle anklicken, um den Menü "Abfrage" sichtbar zu machen, oder durch Menü "Daten", "Abfragen und Verbindungen" die Abfrageliste sichtbar machen.

Weitere Info über Power Query im Tutorial https://excelhero.de/power-query/power-query-ganz-einfach-erklaert

VG
Yal

AW: in meiner XL2016 so nicht nachstellbar ...

23.08.2025 21:20:18

neopa C

Hallo Yal,

... jedenfalls so wie Du Deinen Lösungsansatz beschrieben hast, funktioniert er zumindest in meiner XL2016er nicht.
Ich hatte zunächst auch eine (reine) PQ-Lösung aufstellen wollen. Habe mich dabei aber "vergaloppiert" und zudem ziemlich schnell festgestellt, dass es zumindest für mich mit der Kombination aus Pivot und Formel(n) einfacher, schneller und flexibler zu realisieren geht.

Nicht desto trotz habe ich eben versucht, Deinen Lösungsvorschlag 1:1 nachzubauen. Jedoch nach:

...

- Menü "Transformieren", "Spalte entpivotieren", "andere Spalten entpivotieren" ...

- in der Bearbeitungsleiste den "Attribut" in "Datum" ändern

und dann

- Spalte "Datum" markieren (und nur diese), ...

ist mein XL2016 mit Error-Meldung ausgestiegen :-(
Nun ist mir unklar geblieben, ob ich Deine Angaben - falls Deinerseits kein Schreibfehler vorliegen sollte - nicht richtig interpretiert habe oder in Deiner aktuelleren XL-Version etwas anders realisiert wird?

Kannst Du mal bitte Deine Beispiel-Lösungsdatei einstellen? Dann kann ich es mir es morgen noch einmal anschauen.

Gruß Werner
.. , - ...
aus C the unseen

AW: in meiner XL2016 so nicht nachstellbar ...

25.08.2025 12:33:34

Yal

Hallo Werner,

eigentlich ist der von Dir beschriebene Punkt relativ einfach und hat seit 2016 keine Änderung bekommen.
Ein Entpivotieren à la "andere Spalten entpivotieren" erzeugt eine Spalte für die bisherigen Spaltenname und vergibt standardmässig den Namen "Attribut". Es gibt keine andere Lösung als in der M-Code den Name per Hand anzupassen. Dieses Entpivotieren hat auch die Besonderheit, dass anschliessend die Spalten "Attribut" und "Wert" markiert sind, daher muss man auf dem Hut sein, die nächste Schritt nicht auf "Wert" wirken zu lassen, daher das Anmerken "nur die Spalte "Datum"" markieren.

Anbei meinen Entwurf:
https://www.herber.de/bbs/user/178739.xlsx
Es wird bei dir eine Meldung geben, dass der M-Code mit einer neueren Version von PQ erzeugt wurde, sollte aber zu keinen Nebeneffekt führen.

Deine Lösungsformel sieht tatsächlich sehr schlank aus. Nur die Kombination Pivottable und Formel direkt rechts davon ist mir -aus eigener Erfahrung- eher unstabil. Die AGGREGAT-Formel finde ich zwar sehr stark, aber schwer zu durchschauen.
Die Frage ist immer dabei, ob es eine einmalige Auswertung ist oder etwas, was regelmässig gemacht werden soll. Formeln müssten dann immer neu angepasst werden. Wenn die Quellen dann als Datei (auch csv) oder Datenbank kommen, dann spricht es für eine PQ-Lösung.

Am Ende wählt der Fragenden die für sich passende Lösung . Gut, dass er/sie die beide hat.

VG
Yal

AW: Deine nun eingestellte Datei zeigt nun, ...

26.08.2025 10:56:33

neopa C

Hallo Yal,

... wie ich auch nicht anders erwartet hatte, das der von Dir am 22.08 beschriebene PQ-Lösungsweg mit Deiner nun vorliegenden PQ-Lösung zumindest teilweise nicht übereinstimmt. Obwohl Du Dir mit Deiner Beschreibung wieder einmal viel Arbeit gemacht hattest, kann so etwas dann leider zum Gegenteil zu dem von Dir beabsichtigten führen. Trotzdem fand und finde ich grundsätzlich Deine wiederkehrenden Bestrebungen Unerfahrene durch Beschreibungen Deiner vorgeschlagenen Lösungswege gut und lobenswert.

Deiner persönlichen Erfahrungen, wonach eine Kombination Pivottable und Formel "instabil" wäre, teile ich jedoch nicht. Wie bei fast allen gilt wohl auch hierfür, dass man lediglich gut wissen sollte, wie man es anwenden kann und muß. Somit spielt hierfür wie bei jeder Art von möglichen Lösungswegen viel subjektives eine Rolle. Dies sowohl seitens uns Helfenden wie natürlich auch und möglicherweise noch mehr für die welche Hilfe suchen.

Gruß Werner
.. , - ...
aus C the unseen

AW: es wäre zwar eine reine Formellösung möglich, ...

23.08.2025 20:54:06

neopa C

Hallo Stefan,

... doch einfacher ist mE eine Kombination aus einer Pivotlösung mit zwei relativ einfachen Formeln. Vor allem ist eine solche Lösungskombination dann auch viel flexibler in der Auswertungshandhabung als eine reine Formellösung und mE auch einfacher in der Erstellung als eine PQ-Lösung (zumindest in XL2016).

Für Deine Beispieldatei nachfolgend mein Lösungsansatz. In O2: R## ist die Pivotauswertung (im Berichtslayout; ohne Teilergebnisse und Spalte [Monate] aus Abweichung[Datum] im Format: "MMM") definiert. Die angestrebten Ergebnisspalten [SOLL] und [Proz. Diff] werden mit den ebenfalls nachfolgend aufgezeigten Formeln in S5:T5 (die weit genug - über die Zeile "Gesamt" hinaus -nach unten ziehend zu kopieren sind) ermittelt (vorausgesetzt, die Monatsbezeichnung in der Kopfzeile der Soll-Tab wird in einem entsprechenden Excel-Datumsformat geschrieben (also im Beispiel nicht "Sept" sondern "Sep"). Deine Spalte Abweichung[Monat] hab ich ausgeblendet ich. Sie kann auch gelöscht werden, weil sie unnötig ist.

Die Tab. "Soll" sollte in Deiner Originaldatei natürlich nicht direkt unterhalb von der Tab. "Abweichung" stehen. Sie kann z.B. auch problemlos in einem anderen Tabellenblatt stehen. (übrigens wäre diese so wie Du sie ermittelt hast auch gar nicht notwendig. Ausreichend wären nur die Datenwerte, die Du in K9:K10 zu stehen hast. Es müßte dann dafür lediglich die Formel in S5 etwas erweitert werden).

Arbeitsblatt mit dem Namen 'Tabelle1'

A

B

D

G

H

N

O

P

Q

R

S

T

U

2

KW

(Alle)

3

Tab. "Abweichung":

4

KW

Datum

Stadt

geschlossen

geöffnet

Stadt

Monate

Zu

IST

SOLL

proz.Diff

5

31

01.08.2025

Stadt A

08:00

Stadt A

Aug

16:00

744:00

-100,00%

6

32

04.08.2025

Stadt B

05:00

Stadt B

Aug

11:00

496:00

-100,00%

7

32

04.08.2025

Stadt D

04:00

Stadt B

Sep

2:00

480:00

-100,00%

8

32

08.08.2025

Stadt C

03:00

Stadt C

Aug

9:00

10:00

99:00

-89,90%

9

32

09.08.2025

Stadt B

06:00

Stadt D

Aug

8:00

744:00

-98,92%

10

33

11.08.2025

Stadt C

07:00

Gesamt

38:00

18:00

2563:00

-99,30%

11

33

11.08.2025

Stadt D