Zusammenfügen von zwei Tabellen für SOLL-IST Vergleich

[KathaDD]

Hallo zusammen, ich versuche nun eine kleine Ewigkeit einen SOLL-IST-Vergleich zu erstellen. Im Prinzip sollen die Daten der ersten beiden Spalten übereinandergelegt werden und eine neue Spalte (Differenz) mit den Unterschieden als auch mit den Gemeinsamkeiten angezeigt werden. Weiß jemand wie ich auf dieses Ergebnis komme?
Vielen Dank schonmal

[Lutz Fricke]

Hallo KathaDD,

wenn's händisch geht, ganz einfach:

• die Werte aus Spalten A und B in das neue Blatt kopieren.
  
• die Werte aus Spalte E und F ins neue Blatt unter die ersten Werte kopieren.
  
• dann im neuen Blatt die beiden Spalten markieren und mit Daten -> Datentools -> Duplikate entfernen alle doppelten Sätze entfernen.
  
• jetzt mit SUMMEWENNS die Soll-Werte hinter den Datensätzen "addieren" (da jeder Satz nur einmal vorkommt ist das wie SVerweis etc.).
  
• Genause mit den Ist-Werten und dann die Differenz bilden.
  

Gruß,
Lutz

[Ralf A]

Moin,

da ja Lisa z. Bsp. Wasser und Spielzeug im IST verkauft aber das im SOLL nicht vermerkt ist, wirst Du, wenn es so wie in Deinem Beispielergebnis werden soll, das auch händisch machen müssen. Über einen Join geht es jedenfalls nicht.
Dazu müsste im SOLL für das Bsp. Lisa vermerkt werden, dass sie je 0 Möbel und Spielzeuge Einkaufen muss. Bei allen anderen fehlenden Einträgen das Gleiche.
Die Crux bei Deiner Aufstellung ist, dass Du etwas vergleichen willst, dass nicht vorhanden und somit nicht vergleichbar ist.
Ansonsten ginge das mit PQ zu lösen.

[Klaus-Dieter]

Hallo Kata,

mit einem Makro ließe sich das ohne Probleme lösen, darf es so etwas sein? Dazu müsste man aber den genauen Aufbau der Datei kennen.

[ws-53]

Mit Power Query sind es nur ein paar Musklicks. Für die Darstellung des Ergebnisses habe ich jedoch Pivot Tabelle gewählt, da sich so die Gruppierung per Mausklick nach belieben anpassen lässt.

---

Die Crux bei Deiner Aufstellung ist, dass Du etwas vergleichen willst, dass nicht vorhanden und somit nicht vergleichbar ist.

@Ralf - Das ist keine Crux sondern Standard bei Soll-/Ist-Vergleichen. Denn je detaillierte du planst, umso größer ist die Wahrscheinlichkeit dass es nur Soll oder nur Ist gibt. Z.B. Kartoffeln und Apfel werden nach Sorten geplant, auf der Aggreationsebenbe Kartoffeln und Äpfel kann dann das Soll durchaus sehr genau erreicht werden, aber auf der darunterliegenden Ebene der Sorten gibt es i.d.R. große Abweichungen.

Somit ist dein "Ansonsten" keine Einschränkung.

[ws-53]

wenn's händisch geht, ganz einfach: ...

@Lutz - Wenn wir es wirklich schaffen sollten die Bürokratie abzubauen und tatsächlich am Ende Arbeitszeit eingespart werden kann, dann wird diese durch uneffektive Vorschläge wieder vollständig verplempert!

[maninweb]

Hallo,

anbei eine wohl mögliche Formellösung, wenn ich mich nicht verrechnet habe ...

PHP-Code:

=LET(vnSoll; $A$3:$C$16; 
     vnIst; $E$3:$G$14; 
     vnMatrix; VSTAPELN(HSTAPELN(SEQUENZ(ZEILEN(vnSoll); 1; 1; 0); ERWEITERN(vnSoll; ZEILEN(vnSoll); 4; 0)); 
               HSTAPELN(SEQUENZ(ZEILEN(vnIst); 1; 2; 0); SPALTENWAHL(ERWEITERN(vnIst; ZEILEN(vnIst); 4; 0); {1.2.4.3}))); 
     vnRows; SEQUENZ(ZEILEN(vnMatrix); 1; 1; 0) * SEQUENZ(1; SPALTEN(vnMatrix)); 
     vnColumns; SEQUENZ(ZEILEN(vnMatrix)) * SEQUENZ(1; SPALTEN(vnMatrix); 1; 0); 
     vnKeys; INDEX(vnMatrix; 0; 2) & INDEX(vnMatrix; 0; 3); 
     vnSearch; INDEX(vnMatrix; 0; 1) & vnKeys; 
     vnMap; WEGLASSEN(MAP(vnMatrix; vnRows; vnColumns; LAMBDA(A;X;Y; 
            LET(U; INDEX(vnMatrix; Y; 2) & INDEX(vnMatrix; Y; 3); 
                V; INDEX(vnMatrix; Y; 1); 
                   WAHL(X; A; A; A; 
                   WENN(V <> 1; WENNFEHLER(INDEX(vnMatrix; VERGLEICH("1" & U; vnSearch; 0); 5); 0); A); 
                   WENN(V <> 2; WENNFEHLER(INDEX(vnMatrix; VERGLEICH("2" & U; vnSearch; 0); 5); 0); A))))); 0; 1); 
     vnUniques; ZEILENWAHL(vnMap; VERGLEICH(EINDEUTIG(vnKeys); INDEX(vnMap; 0; 1) & INDEX(vnMap; 0; 2); 0)); 
     vnSort; SORTIERENNACH(vnUniques; INDEX(vnUniques; 0; 1); 1; INDEX(vnUniques; 0; 2); 1); 
     HSTAPELN(vnSort; INDEX(vnSort; 0; 3) - INDEX(vnSort; 0; 4))) 


Die Formel erstellt die Matrix vnMatrix aus beiden Tabellen SOLL und IST. Dabei werden beide Tabellen um Spalten erweitert.
Einmal vorne mit der Tabellennummer und hinten 2 Spalten für die Soll- und Ist-Werte. vnRows und vnColumns erstellen je
eine Matrix mit Positionswerten (Zeilen- und Spaltennummern) zur Übergabe an MAP. vnKey und vnSearch sind Schlüssel.
vnMap geht dann alle Werte in vnMatrix durch und setzt die Werte. Danach braucht man nur noch die Doppler entfernen,
was die Reihenfole Soll-Ist beachten. Abschließend wird sortiert und die Differenz drangehängt.

Gruß

[EA1950]

Hallo KathaDD,

da du Excel365 hast, habe ich dir 2 Formeln erstellt:

in Zelle O2 (mit momentanen Resultatbereich O2:S25):

PHP-Code:

=LET(tbSoll; $A$3:$C$16; tbIst; $E$3:$G$15; tbErg; VSTAPELN(ERWEITERN(tbSoll; ; 4; 0); HSTAPELN(ERWEITERN(SPALTENWAHL(tbIst; 1; 2); ; 3; 0); INDEX(tbIst; ; 3))); tbErgD; HSTAPELN(tbErg; NACHZEILE(tbErg; LAMBDA(zl; INDEX(zl; 3) - INDEX(zl; 4)))); gr; GRUPPIERENNACH(SPALTENWAHL(tbErgD; 1; 2); SPALTENWAHL(tbErgD; 3; 4; 5); SUMME; 0; 2; ; ); VSTAPELN({"Name"."Einkauf"."Soll"."Ist"."Differenz"}; gr)) 


Hier erfolgt eine Gruppierung nach den Spalten Name/Einkauf (mit Zwischensummen).

in Zelle U2 (mit momentanen Resultatbereich U2:Y31):

PHP-Code:

=LET(tbSoll; $A$3:$C$16; tbIst; $E$3:$G$15; tbErg; VSTAPELN(ERWEITERN(tbSoll; ; 4; 0); HSTAPELN(ERWEITERN(SPALTENWAHL(tbIst; 1; 2); ; 3; 0); INDEX(tbIst; ; 3))); tbErgE; SPALTENWAHL(tbErg; 2; 1; 3; 4); tbErgD; HSTAPELN(tbErgE; NACHZEILE(tbErgE; LAMBDA(zl; INDEX(zl; 3) - INDEX(zl; 4)))); gr; GRUPPIERENNACH(SPALTENWAHL(tbErgD; 1; 2); SPALTENWAHL(tbErgD; 3; 4; 5); SUMME; 0; 2; 1); VSTAPELN({"Einkauf"."Name"."Soll"."Ist"."Differenz"}; gr)) 


Hier erfolgt eine Gruppierung nach Einkauf/Name (ebenfalls mit Zwischensummen).

Beide Resultatbereiche habe ich mit 2 bedingten Formatierungen unterlegt (für die Hauptgruppierung und die Summenzeilen).

Bei beiden Formeln werden die Tabellenbereiche (A3:C16 bzw. E3:G15) an die Variablen tbSoll bzw. tbIst übergeben.
Falls du bereits über die Funktion ABSCHNBEREICH(...) verfügst, kannst du die Größe dieser Tabellenbereiche auch dynamisch gestalten durch die Eingabe eines zusätzlichen Punktes nach dem Doppelpunkt: A3:.C99 bzw. E3:.G99
Dieser zusätzliche Punkt bewirkt, dass jeder der Bereiche von seinem Ende her auf Dateninhalt getestet wird und nur der Bereich mit Daten verwendet wird (im vorliegendem Fall also A3:C16 bzw. E3:G15).

[ws-53]

anbei eine wohl mögliche Formellösung,

Die Werte stimmen. Aber das ist vom Aufwand in etwa so, als würde ich Samstags den Gehweg mit einer Zahnbürste kehren wollen.

Aber vllt. wir ja durch die neue Regierung so viel Bürokratie abgebaut, dass so der zeitliche Leerlauf ausgeglichen werden kann.

[maninweb]

Moin,

@ws-53, Danke. Was den Aufwand betrifft, sehe ich das etwas anders, denn man könnte ja bspw. eine LAMBDA draus machen,
weiter verallgemeinern und somit auch wiederverwenden.

Gruß