Excel - Bedingte Formatierung in Pivot bei Abgleich

Taschmahal

Lt. Commander
Registriert
Juni 2008
Beiträge
1.223
Hello,

ich habe eine Pivottabelle bei der für jeden Tag je Mitarbeiter eine Anzahl steht (Tabelle "PIVOT"). Dann habe ich noch ein Arbeitsblatt ("Abwesenheit"). In Abweisenheit steht zu jedem Tag ob jemand abwesend ist (in diesem Beispiel ="/" = anwesend (kann auch ein anderes Symbol sein), "TW" = Teleworking zB, "U" = Urlaub (wäre aber mal egal)).

Ich würde mir jetzt gerne in der Pivottabelle für die entsprechende Zelle (also Datum + Mitarbeiter) orange färben lassen, wenn jemand teleworken war, oder grün wenn er anwesend war. Dass an Tagen an denen jemand abwesend ist nichts steht, reicht leider nicht, weil das auch in Ordnung sein kann, weil derjenige zB etwas anderes gearbeitet hat.

Geht sowas überhaupt? Dadurch dass die beiden Tabellen auch nicht gleich sortiert sind, bzw. die Pivot je nach Filterung sich verändert, kann ich ja auch nicht damit arbeiten, dass Spalte B = Person B, Spalte C = Person C ist.

Es müsste also ein Abgleich sein bei der zB A10 und C7 (Spalte A = Datum, Spalte 7 = Person) in Tabelle PIVOT verglichen wird mit Tabelle Abwesenheit was dort steht, und je nachdem was dort steht muss es C10 orange oder grün färben.

Hoffe ihr wisst weiter.

Anm: Mit Makros kenn ich mich nicht wirklich aus, nur ganz ganz ganz rudimentär.
 
Ob es geht, haengt von deinen Tabellen ab.
Und ohne die kann man dir weder eine Antwort auf deine Frage geben, noch dir bei der Umsetzung einer Loesung helfen.
 
Tabelle (=Pivottabelle, bei der die Werte eine Anzahl darstellen): Pivot
AnzahlPerson APerson B
01.10.2018456234
02.10.2018245
03.10.2018

Tabelle: Abwesenheit
Person APerson B
01.10.2018xx
02.10.2018TWx
03.10.2018UK

In Tabelle Pivot
Zelle B2 soll grün sein, weil Person A am 01.10.2018 anwesend ist, also x
Zelle B3 soll gelb sein, weil Person A am 02.10.2018 teleworken ist, also tw
Zelle B4 soll blau sein, weil Person A am 03.10.2018 im Urlaub ist, also u
Zelle C2 soll grün sein, weil Person B am 01.10.2018 anwesend ist, also x
Zelle C3 soll grün sein, weil Person B am 02.10.2018 anwesend ist, also x
Zelle C4 soll blau sein, weil Person B am 03.10.2018 im Krankenstand ist, also k

Versteh nicht ganz wofür du hier eine Tabelle brauchst, brauch das ganze ja nicht direkt in Excel gelöst, sondern einen Weg. Die tatsächliche Arbeitsmappe kann ich nicht hochladen.
 
Taschmahal schrieb:
Versteh nicht ganz wofür du hier eine Tabelle brauchst, brauch das ganze ja nicht direkt in Excel gelöst, sondern einen Weg. Die tatsächliche Arbeitsmappe kann ich nicht hochladen.

Ein Bild mehr sagt als tausend Worte und eine funktionale Dummy Datei hilft auch deutlich weiter und reduziert Missverstaendnisse ...
Zu mal es auch die Huerde senkt, jemanden zu helfen (der Helfer muss erst mal was machen, bevor er das Problem angehen kann).

INDEX(Abwesenheit!$A$1:$C$4;VERGLEICH(Pivot!$A2;Abwesenheit!$A$1:$C$4;0);VERGLEICH(Pivot!B$2;Abwesenheit!$A$1:$A$3;0))="x"

Der Bereich fuer die bedingte Formatierung sollte im Beispiel mit B2 beginnen.

Mit Index() basteln wir uns den notwendigen Verweis zum Abgleichen des Inhalts.
Index(Matrix in Abwendheit; Zeileindex in der Matrix; Spalteindex in der Matrix)

Mit Vergleich() suchen wir nun einmal nach dem Datum in der Matrix Abwesenheit, um den Zeilenindex innerhalb dieser Matrix zu erhalten und noch ein weiteres Mal, um den Spaltenindex' des Mitarbeites zu erhalten.
 
  • Gefällt mir
Reaktionen: Taschmahal und Bob.Dig
Super danke, werde das dann ausprobieren, wie gesagt an sich hätte schon der Hinweis auf Index und Vergleich gereicht, wenn ich mal die Funktion weiß die überhaupt verwendet werden muss, fitzel ich mir das schon zu Recht.
 
@Scientist
Hello,

so bin gerade dabei deine Formel zu testen, nur dass mir hier kein Formalfehler passiert, die Formel gehört schon in der bedingte Formatierung unter "Werte formatieren, für die diese Formel wahr ist:"?

Und dann habe ich da noch eine Logikfrage, wieso wird in der Zeilenmatrix die gesamte Matrix von Abwesenheit also A1:C4 angegeben, in der Spaltenmatrix aber nur A1:A3?

Müsste das eine nicht A1:A8 zB und das andere A1:G1 lauten? Bzw. müsste "Pivot!B$2" nicht B1 sein?

Bekomme es noch nicht 100% hin, auch wenn ich so zumindest weiß, worauf die Formel abzielt (was schon mal ein enormer Schritt vorwärts ist zu meinen vorherigen Überlegungen mit Wenn und Sverweis.

Hab jetzt noch eine Dummy-Datei gebaut und die Werte verändert.
 

Anhänge

  • test.xlsx
    303,2 KB · Aufrufe: 415
Taschmahal schrieb:
so bin gerade dabei deine Formel zu testen, nur dass mir hier kein Formalfehler passiert, die Formel gehört schon in der bedingte Formatierung unter "Werte formatieren, für die diese Formel wahr ist:"?
Genau, dass sollte der letzte Punkt in der Liste sein.

Taschmahal schrieb:
Und dann habe ich da noch eine Logikfrage, wieso wird in der Zeilenmatrix die gesamte Matrix von Abwesenheit also A1:C4 angegeben, in der Spaltenmatrix aber nur A1:A3?

Müsste das eine nicht A1:A8 zB und das andere A1:G1 lauten? Bzw. müsste "Pivot!B$2" nicht B1 sein?

Ja, da hab ich mich verkopiert ...
Ich hab die Bezuege an die in deinem Beispiel nachtraeglich angepasst.
 
  • Gefällt mir
Reaktionen: Taschmahal
Nachtrag:
Ich hab mir jetzt deine Datei mal angeschaut ...

1) Bei der Formel haben sich Anfuehrungszeichen eingeschlichen. Damit wird die Formel als Text behandelt.
2) Dein Datum in der Pivot-Tabelle ist ein Text und kein Datum. Dadurch wirft dieser Vergleich ein Fehler aus.

Und dazu kommen halt noch die Kopierfehler von mir, die du angesprochen hast.

Allgemein:
Es ist hilfreich erst mal die Formel in einer Zelle im Blatt zu erstellen und die funktionierende anschließend in die bedingte Formatierung.
 
  • Gefällt mir
Reaktionen: Taschmahal
1) Keine Sorge die habe ich eh beim testen immer rausgelöscht, es übernimmt sie trotzdem irgendwann
2) Das Datum ist in der ursprünglichen Datei ausgeschrieben, mir ist gar nicht aufgefallen, dass es hier so komisch gekürzt wird, zumal alle Einstellungen und Zellenformat auf dem folständigem Datum stehen...

Edit: Okay, das mitm Datum war ein eigenartiger Fehler, ist jetzt mal geändert (auch wenn alles richtig war)


@Scientist
Nochmals ein großes Danke, jetzt hab ich es geschafft!
 
Zuletzt bearbeitet:
Hello,

was für einen Grund könnte es haben, dass es die selbe Formel nur in einer zweiten Datei, die gleich zu der ersten ist, nicht ausführt. Was ich ausschließen kann, bedingte Formatierungen gehen, hab in einem anderen Blatt dieser Datei ganz einfach laufen.

Okay mittlerweile zeigt es mir zwar wieder die Werte farblich da je nach Abwesenheit/Anwesenheit, allerdings nicht entsprechend derjenigen Mitarbeitern, es ist irgendwie verrückt (also verschoben).
 
Zuletzt bearbeitet:
Hola,

dann ist die Datei nicht gleich - wahrscheinlich liegen die Werte nicht in den selben Zellen und in der bedingten Formatierung wurde das nicht angepasst.

Gruß,
steve1da
 
Also angepasst habe ich die Formel natürlich.

Die Zeile ordnet es ja auch mittlerweile korrekt zu, die Spalte jedoch nicht. Wirkt für mich ein bischen wie wenn man in einem SVERWEIS auf "WAHR" statt auf "FALSCH" stellt. Was aber bei der Formel nicht vorkommen sollte...

=INDEX(Abwesenheit!$A$2:$Q$262;VERGLEICH($A8;Abwesenheit!$A$2:$A$262;0);VERGLEICH(B$7;Abwesenheit!$A$2:$Q$2;0))="K"

In der Pivot-Tabelle gehen die Werte von B8: P253
 
Hola,

deine "Spalten"Werte fangen in A2 an, deine "Zeilen"-Werte auch?
Dann geht dein Vergleich bis Spalte Q, es sollen aber auch Werte bis Spalte R ausgelesen werden?
Geraten:
=INDEX(Abwesenheit!$A$2:$R$262;VERGLEICH($A8;Abwesenheit!$A$2:$A$262;0);VERGLEICH(B$7;Abwesenheit!$A$1:$Q$1;0))="K"
Wenn das nicht passt steht im Schnittpunkt kein K.
Gruß,
steve1da
 
  • Gefällt mir
Reaktionen: Taschmahal
Hello ich glaub das mit Q und R hast du gerade während meiner Edits abgefragt, hatte beide Lösungen getestet. Die Werte selbst gehen bis Q.

A2 ist quasi der Schnittpunkt, sprich in A3 beginnt der erste Zeileneintrag, in B2 der erste Spalteneintrag. Hat in der anderen Datei ohne Probleme so funktioniert.

Danke werds jetzt einfach nochmal testen, anscheinend ist irgendwo ein Verhaspler. Was ich auch schon getestet habe, nachdem die Zeilen ja richtig erkannt werden, nur die Spalten nicht, ob die ID's in B2:Q2 das richtige Format haben jeweils.

Anmerkung zu deiner Formel,
$A$1:$Q$1 kann nicht passen, die Werte die gesucht werden stehen erst in Zeile 2


EDIT: Okay danke, hab jetzt bei deiner Formal nur meine Anmerkung eben korrigert und jetzt scheints zu passen. hmpf. Es hat sich damit aber nichts verändert, muss man das kapieren?
 
Hola,

dann:
=INDEX(Abwesenheit!$A$3:$Q$262;VERGLEICH($A8;Abwesenheit!$A$3:$A$262;0);VERGLEICH(B$7;Abwesenheit!$A$2:$Q$2;0))="k"

Gruß,
steve1da
 
  • Gefällt mir
Reaktionen: Taschmahal
Zurück
Oben