Clever-Excel-Forum

Hallo liebe Forum-Mitglieder,

ich stehe in meinem Job vor der im Folgenden beschriebenen Herausforderung und habe im hier im Forum noch keine vollständige Lösung gefunden, da wahrscheinlich eine Kombination gleich mehrer Schritte erforderlich ist.

Für jedwede Unterstützung und Tipps bin ich sehr dankbar!
Als absoluter Forum-Neuling bitte ich um Geduld und Verständnis, falls ich etwaige Richtilinien der Forumsetikette übersehen haben sollte und freue mich über Feedback hierzu.   

Die Aufgabe

Während der monatlichen Gehaltsabrechnung vergleichen wir die Gehaltsdaten unserer Mitarbeiter aus dem Vormonat mit den Probe-Gehaltsdaten aus dem aktuellen Monat um ungewollte Abweichungen zu entdecken und zu korrigieren.

Folgendermaßen kann man sich die hier vereinfacht dargestellten Tabellen vorstellen, welche wir von unserem Steuerbüro bekommen:

Akt. Monat (Bild1):
[attachment=28601]



Vormonat (Bild2):

[attachment=28602]

Nun würde ich gerne automatisch diejenigen Zellen in der Liste des aktuellen Monat rot markiert haben, welche sich im Vergleich zum Vormonat geändert haben. Das sähe idealerweise in etwa so aus:

Bild3

[attachment=28603]

Folgende Besonderheiten machen eine Automatisierung ggf. noch komplexer :

• Jeden Monat kommen neue Mitarbeiter hinzu (s. "Michael Schön"), welche gerade dann rot markiert werden müssen, wenn sie im Vormonat gar nicht auftauchen
  
• Auch neue Lohnartposten, welche beim Mitarbeiter im Vormonat nicht vorhanden waren, sollten im aktuellen Monat rot markeirt werden  (s. "Halbj. Bonus")
  
• Darüber hinaus müssten wir ebenfalls erfahren, ob im aktuellen Monat Zahlungen weggefallen sind, welche zuvor getätigt wurden (bspw. Fahrtkosten, welche im Vormonat gezahlt wurden, aber im  akt. Monat vergessen wurden)
  
• Obwohl es theoretisch Personalnummern in der Liste gibt, ändern diese sich manchmal bei den Mitarbeitern und eignen sich daher nicht für Vergleich der Daten. Meine Idee war daher bisher mit einer Verkettung mehrer zu Zellen arbeiten:
  

Bild4

[attachment=28604]


Soweit erstmal die Ausgangslage. Wie gesagt freue ich mich über eure Antworten, auch gerne zu Teillösungen


Viele Grüße,

Klaus

Hallo Klaus,

Zitat: ... und habe im hier im Forum noch keine vollständige Lösung gefunden

in einem Forum vollständige Lösungen für ein einzelnes Projekt zu erwarten halte ich für ziemlich vermessen.
Allerdings sollte es Dir bei Deinen Streifzügen in den Foren nicht entgangen sein, was die Helfer von gezeigen
Bildern halten. Sinnvoller wäre eine Beispieldatei.
Ein Bild führt in den wenigsten Fällen zur Lösung. In meinem über 20jährigen Excelleben war das exakt  2 Mal
der Fall. Wohingegen in einer Datei auch mal was getestet werden kann, was regelmäßig zu einer schnelleren,
wahrscheinlich auch besseren Lösung führen kann.
Deine Datei darfst Du gerne anonymisieren, aber der Aufbau sollte exakt den Realitäten entsprechen.

Moin Klaus,

das schreit nach bedingter Formatierung. Lad doch bitte eine kleine Beispieldatei mit den möglichen Szenarien hoch, dann lässt sich's leichter testen.

Moin Klaus,

es reicht natürlich eine Beispieldatei und vielleicht machst Du Dir vor dem Einstellen mal ein paar Gedanken über den Aufbau - zumindest dass der nicht optimal ist sieht man auf den Bildern ...

Den Aufbau Deiner Tabelle solltest Du also mal überdenken. Eine zeilenweise Führung der Daten ist für eine Auswertung sinnvoller. Jeder Mitarbeiter könnte dann jeden Monat eine Zeile bekommen. Du könntest dann z.B. nach Mitarbeitern und Monaten sortieren und siehst gleich, wenn im Vormonat was drin steht und im aktuellen nicht. Du könntest auch eine bedingte Formatierung darauf aufbauen.
Die Monate sollten übrigens als Datum eingetragen werden und nicht als Texte. Die texte bekommst Du dann über das Zellformat.

Das mit den wechselnden Personalnummern für die gleiche Person solltest Du mal versuchen, auszuschließen. Ansonsten darfst Du keine 2 Personen mit gleichem Namen + Vornamen einstellen.

…

Hi zusammen,

vielen Dank für die schnellen Antworten und auch das Feedback zu den Bildern!
Es tut mir leid, dass ich da nicht schon zuvor an den Upload der Datei gedacht habe. 


Anbei also eine Beispieldatei, welche vom Aufbau und Format dem Original gleicht.

[attachment=28627]

In der Arbeitsmappe des aktuellen Monats sind die Abweichungen zum Vormonat schon von mir gelb markiert.
Die Datumsangaben habe ich umformatiert.

Was den Aufbau der Datei angeht habe ich leider wenig Einfluss, da sie nur so über das Programm unseres Steuerbüros exportiert werden kann.
In der Tat ist es unglücklich, dass in vielen Fällen derselbe Mitarbeiter mehrere Zeilen in einem Monat erhält, wenn er verschiedene Lohnkomponenten erhält.


Viele Grüße

Klaus

Hallöchen,

so könntest Du z.B. feststellen, ob etwas im Vormonat vorhanden war - hier mal für die Vergütung

=ZÄHLENWENNS(Vormonat!C:C;'Aktueller Monat'!C2;Vormonat!D:D;'Aktueller Monat'!D2;Vormonat!Y:Y;'Aktueller Monat'!Y2)

So bekommst Du auch raus, ob es die gleiche Vergütung ist - du erweiterst die Formel einfach um die entsprechende Spalte des Vormonats und die Zelle im aktuellen Monat. Wenn Du prüfen willst, ob im aktuellen Monat etwas nicht vorhanden ist, kannst Du die Formel im Blatt Vormonat einsetzen und die Bezüge entsprechend anpassen.

Nur zur Prüfung des Mitarbeiters würdest Du die Formel entsprechend kürzen. Die Formeln kannst Du dann z.B. für eine bedingte Formatierung nutzen oder nimmst sie hinten neben die Daten …

Hallo,

sieh mal, ob dir meine Excelanwendung Tabellen vergleichen weiter hilft.

Hallo,

du kannst auch mit PowerQuery die beiden Tabellen einlesen

und mit einem "vollständigen äußeren Join"  beide Tabellen vereinen.

Hier brauchst du einen Schlüssel. 
PersonalNr fällt aus , dann würde ich Steuer/SV Nr & Lohnart  vorschlagen.

Das Ergebnis noch mit ZusatzInfo  - alt / neu / gändert