Office: Eine bedingte Formatierung, viele variablen erkennen!

Guten Tag zusammen,

gibt es möglichkeiten, mit nur einer bedingte Formatierung, viele variablen einzubauen, die allesamt zu den gewünschten Ergebnis führen?

Als beispiel:

Das Unternehmen verwendet User ID, statt echten Namen.

Nun haben wir, sagen wir, 50 Mitarbeitern die alle eine eigene User-ID haben. Diese könnte folgend aussehen:

Mitarbeiter 1 hat die "U-ID20"
Mitarbeiter 2 hat "U-ID22"
Mitarbeiter 3 = "U-ID45"
Mitarbeiter 4 = "U-ID82"
Mitarbeiter 5 = "U-ID23"

...und so weiter.

Dann haben wir noch Gruppen.
Je Gruppe hat eine festgelegte Farbe und eine Gruppenbezeichnung.
Bsp.: Die Gruppe 1 mit der farbe "Blau" nennt sich "Strolche".
Gruppe 2 ist Grün und nennt sich "Poseidon"

Jeder Mitarbeiter ist in eine bestimmte Gruppe zugeordnet.

Nun möchte ich, mit einer bedingte Formatierung und(vermutlich?) der =wenn und =Oder Formel, alle zutreffende MA der Gruppe in eine Bedingte formatierung packen.

Das Endergebnis ist: Ich gebe auf der entsprechede Zelle mit der bedingte Formatierung, einen beliebigen MA "U-IDXX" ein, und die Zelle zeigt mir die entsprechende Gruppe dieses MA´s mit Farbe.

Allerdngs gibt es kleine unterschiede in der Schreibweise der U-ID.
Ein bsp.: jemand würde mir seinen User ID mit "U-ID22", aus lauter fauelheit,
als "U ID22" angeben. Oder auch mal so,
"U-ID 22".

Also mal fehlt das "-" Minuszeichen oder ein Leerzeichen ist dazwischen. Das führt dazu, dass die obige gewünschte Formel, nichts erkennen wird...

Deshalb die Frage mit zusätzliche Formel =Oder, um möglichst viele variablen auszuhebeln.
Ich denke, da wird eine Menge arbeit stecken, jeden MA die unterschiedliche variablen einzutippen, aber das ist eine einmalige Sache, die sitzen muss. Hoffe ich.

Oder kennt jemand eine noch besssere Lösung dafür?

Gruß
Semri

 

Hola,

ist denn wenigstens immer die Zahl beim Mitarbeiter eindeutig und einzigartig?
Wenn es unterschiedliche Gruppen und somit unterschiedliche Farben gibt brauchst du für jede Farbe eine eigene Regel.
Grundsätzlich sollte das gehen, auch mit wenig Aufwand. Da aber keine Excel-Beispieldatei zu sehen ist, kann man nicht mehr dazu sagen.

Gruß,
steve1da

 

Hallo Semri,

in der beiliegenden Excel-Datei habe ich im Arbeitsblatt "Tabelle1"

1) eine strukturierte Tabelle "TabMA" eingefügt mit 2 Spalten: Spalte "MA_ID" enthält die exakte MA-ID (ohne Abweichungen) und die Spalte "Gruppe" die dazugehörige Gruppenbezeichnung.
Gleichzeitig enthält diese Spalte auch eine bedingte Formatierung: Gruppe "Strolche" färbt sich dunkelblau, Gruppe "Poseidon" dunkelgrün und Gruppe "Nereus" gelb, jeweils mit dazu passender Schriftfarbe (weiß bzw. schwarz, jeweils fett).
Fügt man einen weiteren Mitarbeiter an (in diesem Fall in A13:B13) und gibt in B13 eine dieser 3 Gruppen ein, färbt sich die Zelle B13 entsprechend ein.

2) In Zelle E6 habe ich eine Eingabezelle für MA-IDs vorgesehen. Diese dürfen mit einer gewissen Eingabetoleranz eingegeben werden: Sie müssen mit "U" beginnen, sie müssen "ID" enthalten und sie müssen mit 2 Ziffern enden. "U" und "ID" kann auch "u" bzw. "id" geschrieben werden.
Die Formel in Zelle E7 wandelt diese MA-ID in eine exakte MA-ID um, so wie sie in Spalte MA_ID von Tabelle "TabMA" enthalten ist. Gelingt diese Umwandlung nicht, erscheint als Formelergebnis "U-IDxx" bzw. "#WERT".

3) In Zelle F7 ist eine Formel enthalten, die die Gruppe zur MA-ID aus Zelle E7 ermittelt. Sie enthält die gleiche bedingte Formatierung wie die Spalte "Gruppe" der Tabelle "TabMA".
Wird in Zelle E6 keine erkennbare MA-ID eingegeben, erscheint in F7 der Fehlerwert "#WERT".

 

Genial!
Deine Idee könnte ich bei der Arbeit tatsächlich einsetzen.
Ich lese mich gerade ein, was die Einzelnen Befehlen für Funktionen haben.

Ich verstehen schonmal, dass die Eingabe für =RECHTS_Text_Anzahl, die letzten Zeichen im Text entsprechend abgeschnitten wiedergibt. Mit Wert(Rechts(_Text__Anzahl_, wäre das also für Zahlen, die dann rechtsbündig stehen werden.

Ich raffe noch nicht, warum das "größer als" > Zeichen ab der [Wahrheitswert2] vorkommt:
=WENN(UND(SUCHEN("U";E6)=1;SUCHEN("ID";E6)>1;ISTZAHL(WERT(RECHTS(E6;2))));"U-ID"&RECHTS(E6;2);"U-IDxx")

Erst einmal, besten Dank.

Gruß Paul

 

Hallo,

du möchtest MA-IDs vorsehen, die mit einer gewissen Eingabetoleranz (in Zelle E6) eingegeben werden dürfen:
1) Sie müssen mit "U" beginnen,
2) sie müssen "ID" enthalten und
3) sie müssen mit 2 Ziffern enden.
4) "U" und/oder "ID" dürfen auch klein geschrieben werden.

Die ersten 3 Bedingungen habe ich durch die 3 Bedingungen in der UND(Bedingung1;Bedingung2;Bedingung3)-Funktion behandelt, wobei Bedingung2 heißt, dass die Zeichenkette "ID" in der Zeichenkette der Zelle E6 enthalten sein muss. Da du aber mit Bedingung1 verlangst, dass diese mit "U" beginnen muss (Zeichenposition =1), kann die Zeichenkette "ID" nicht an Zeichenposition 1 stehen, sondern muss entweder an Zeichenposition 2 oder 3 oder 4 oder ... stehen.

Die SUCHEN("ID";E6)-Funktion gibt die Zeichenposition zurück, an der "ID" in der Zeichenkette von Zelle E6 gefunden wird. Ist "ID" in E6 nicht enthalten, wird von SUCHEN(..) der Fehlerwert #WERT! zurückgegeben.
Da in der UND(..)-Funktion für die Bedingung2 ein Wahrheitswert benötigt wird, habe ich dafür den Ausdruck SUCHEN("ID";E6)>1 vorgesehen, der nur dann ein WAHR als Resultat liefert, wenn "ID" in E6 enthalten ist, jedoch nicht am Zeichenkettenanfang. "ID" kann indirekt auch nicht am Zeichenkettenende von E6 stehen, denn dann wäre Bedingung3 nicht erfüllt.

Du kannst natürlich auch einen Ausdruck ohne ">1" verwenden, also SUCHEN("ID";E6); Auch dann funktioniert die Formel.
Liefert nämlich die SUCHEN-Funktion eine Zeichenposition zurück (d.h. eine Zahl >0), so wird diese Zahl von UND(..) als Wahrheitswert WAHR interpretiert, wohingegen die Zahl 0 als Wahrheitswert FALSCH gewertet würde. Da wegen Bedingung1 diese Zahl nicht 1 sein kann, muss die Auswertung des Formelausdruckes trotzdem korrekt funktionieren.

 

Super, Danke.
So komme ich schonmal weiter.

Ich habe die von dir erstellte Mappe Erweitert und weitere Suchkriterien eingebaut, so wie ich bei der Arbeit tatsächlich arbeite.
Deine obige Formel habe ich angepasst auf Anzahl 4 statt wie bisher 2 unter ;ISTZAHL(WERT(RECHTS(K3;4))));"U-ID"&RECHTS(K3;4);"U-IDxx").
Die MA ID habe ich erweiter auf 4 Zahlen erweitert und und die Gruppe separat voneinander getrennt.
Für die Gruppenzuordnung der einzelnen MA funktioniert mit der SVERWEIS gut. Jedoch nicht, wenn die Gruppen-Tabelle nicht einheitlich in einer Spalte stehen. Sondern getrennt in unterschiedlichen Spalten verteilt sind.
Dazu habe ich mit Formel: =SVERWEIS(M2;Tabelle2&Tabelle3&Tabelle4&Tabelle5;2;0) versucht, also diese &-Zeichen dazwischen, um weitere Matrixfelder zu vergnüpfen. Funkioniert schonmal nicht.

Dazu kommt noch eine Tabelle mit PaketNr.hinzu, die jeder MA zugeteilt bekommt. Diese PaketNr. mit User-ID´s erhalte ich Extern und kopiere sie z.B. hier in diese Muste01 rein.
So kam mir diese Idee mit Gruppen einteilung nach Namen und Farben. Ich möchte die kopierte Ablage in das ZIEL Tabelle einfügen, und so verfärben sich die Zeilen automatisch entsprechend der Gruppenfarbe, welches die Formel die MA U-ID erkennt. ... wenn sie es erkennt.

Dazu mehr in der Muster01 Datei im Anhang.

Zu guter Letzt,
versuche ich noch eine möglichkeit zu finden, aus der Zusammengeführte Zelle mit Infomationen, auf 3 verschiedene Zelle zu separieren.
Wie im Beispiel Muster01 Datei, unter "Auslieferung zur Post", habe ich einen Auslieferungsdatum(K27), in der ein Paket bei der Post abgegeben wurde. auf der zweiten Zeile(K28), den aktuellen Datum und ein Länderkürzel. der rest ist nicht wichitg.

Ich versuche mittels Datedif, den Differenz zwischen Auslieferungsdatum und der Datum der "letzten Status" mit TT MM JJ Format, auf der Zelle M5 einblenden zu lassen. Allerdings hackt es, wegen dem zusätzlichen nebenstehende Text auf der K28. Ebenso möchste ich das aktuelle Status Datum auf der P28 in Englische Format anzeigen lassen. Umformatieren kann ich schon, aber wie bringe ich die zelle dazu, dass sie nur das Datum auf der K28 abliest und den Rest ignoriert?

Das gleiche für Länderkürzel. Hier kann ich normaleweise sverweis einsetzen, aber auch hier müssen Zwei Ländercode Zeichen erkennbar sein.

Ja, ich könnte hier in der Tat, die Zusammengeführte Zellen, wieder separieren und es mir einfacher(?) machen. Stimmt.

Aber trotzdem Danke bis hierher.

Gruß Semri

 

Hallo,
da ich nicht wusste, welche Excel-Version du benutzt, du aber eine *.xlsm-Datei hochgeladen hast, habe ich dir 2 kleine VBA-Funktionen im bereits enthaltenen allgemeinen Codemodul eingefügt:

Code:

Public Function SplittArray(S As String, Tr As String) As String()
  SplittArray = Split(S, Tr)
End Function
'
Public Function SplittIdx(S As String, Tr As String, Idx As Integer) As String
  SplittIdx = Trim$(SplittArray(S, Tr)(Idx - 1))
End Function

Die Funktion "SplittArray" zerlegt den String "S" mit Hilfe des Trennzeichenstrings "Tr" in einen String-Array (Achtung: MATRIX-Formel), wobei das erste Array-Element die Nr.0, das zweite Array-Element die Nr. 1, usw., bekommt:
zB.: Steht in Zelle K28 folgende Zeichenkette: "12.04.2021 / FR / Zugestellt"
so kannst du
1) den Zellbereich K29:N29 markieren,
2) in die Formeleditierzeile folgende Formel eingeben: =SplittArray(K28;"/")
3) diese Formeleingabe nicht mit der EINGABE-Taste beenden, sondern mit der Tastenkombination Strg+Umschalt+EINGABE
so erscheint die Formel in allen 4 Zellen als {=SplittArray(K28;"/")} mit den geschweiften Klammern
mit den Werten: in Zelle K29=12.04.2021 ; Zelle L29=FR ; Zelle M29=Zugestellt ; Zelle N29=#NV
'
Die Funktion "SplittIdx" gibt ein einzelnes Array-Element des Strings "S" zurück, der mit dem Trennzeichenstring "Tr" in ein String-Array aufgesplittet wurde. Dabei enthält "Idx" die Nummer 1 für das erste Element, die Nummer 2 für das zweite Element, usw.
zB. Steht in Zelle K28 wiederum folgende Zeichenkette: "12.04.2021 / FR / Zugestellt"
so gibt die folgende Funktioneingabe: =SplittIdx(K28;"/";1) den Wert "12.04.2021" zurück,
so gibt die folgende Funktioneingabe: =SplittIdx(K28;"/";3) den Wert "Zugestellt" zurück.

Ich habe die 4 enthaltenen Tabellen "Tabelle1".."Tabelle4" umbenannt in "TabNereus", "TabStrolche", "TabPoseidon", "TabOrient". Ferner habe ich im Arbeitsblatt "Länder" die Ländertabelle ebenfalls umgewandelt in eine strukturierte Tabelle
namens "TabLänder".

In die Datumsdifferenz-Zelle M5 habe ich die Formel "=SplittIdx(K28;"/";1)-K27" eingefügt und sie mit dem benutzerdef. Zahlenformat 0 "Tage" versehen.

In die "Abfrage"-Zelle P2 habe ich folgende Formel eingefügt:
=WENNNV(SVERWEIS(M2;TabNereus;P1;0); WENNNV(SVERWEIS(M2;TabStrolche;P1;0); WENNNV(SVERWEIS(M2;TabPoseidon;P1;0); SVERWEIS(M2;TabOrient;P1;0))))
In der Zelle P1 darüber kann jetzt die Spalten-Nr. eingegeben werden für die vorgenannte Formel. Sie enthält ebenfalls ein benutzerdef. Zahlenformat: "Abfrage: Spalte="0 Auf diese Weise kann so entweder die "User-ID"-, die "Vorname"- oder die "Name"-Spalte angezeigt werden.

In der Land-Abfrage-Zelle P27 habe ich folgende Formel eingefügt: =SVERWEIS(SplittIdx(K28;"/";2);TabLänder;2;FALSCH)

In der Date-Abfrage-Zelle P28 habe ich folgende Formel eingefügt:
=DATWERT(SplittIdx(K28;"/";1)) zusammen mit dem benutzerdef. Zahlenformat: MM/TT/JJJJ fürs englische Datumsformat.

 

Hallo,

ergänzend zu meinem Posting von heute 11:14 Uhr, habe ich 2 weitere benutzerdef. VBA-Funktionen eingefügt und sie in Zelle M2 und zur bedingten Formatierung der Zellen in J9:K20 eingesetzt.
Dabei beherrscht die Funktion "=GetMA_ID(S)" auch die Umwandlung der nachträglich dazu gekommenen Abweichungen bei der MA_ID.

 

Verblüffend ...

Zuerst, musste ich mir 20min lang diese Funktionen und VBA Code´s, über mich ergehen lassen ...
Ich benutze bereits einen Teil, dank deiner Unterstützung, die Funktionen bei der Arbeit. Mein Workflow ist in der tat merklich etwas zügiger geworden. Es ist einfach Toll, wenn man mit Hilfe der Funktionen, Informaionen schneller parat hat zum arbeiten.

Ja, ich hatte dass nicht so auf dem Schirm, dass wir hier im Forum tatsächlich mit unterschiedlichen Excel-Versionen arbeiten. Meiner ist Excel-2013, genau so wie bei der Arbeit auch. Ob wir im Unternehmen jemals einen Excel Upgrade zulegen werden, ist unwahrscheinlich.

Ich bin sehr gespannt, wie sich der Rest der Funktionen, zeitlich als auch qualitativ ausmacht.
Die von dir erstellte VBA Codes würde ich gerne in meine "Richtige" Excel Datei einpflegen. Leider dürfen wir keine Externe Dateien, jegliche Art, in unsere PC per USB oder sonst wie übertragen. Heißt, ich muss mir die restlichen Code´s evtl. abfotografieren und sie manuell eintippen, danach die Formel entsprechend bauen..

Eine Frage kommt mir noch in den Sinn.
Bei Word Programm gibt es die möglichkeit, die Zelle auf einer vorgefertigte Tabelle, einen Text Transparent(Hilfstext?) zu machen. Beim anklicken dieser Zelle, schwindet dieser Text, und ich gebe meine meine information ein.

Gibt es diese Möglichkeit auch in Excel? Ich vermute, wieder mit VBA Code.

Besten Dank.

Gruß
Semri