Formel für 'Bedingte Formatierung' gesucht

[N_N_a8952a]

Hallo zusammen,

gleich am Anfang die Information dass meine Frage sich auf Excel 2002 (SP3) bezieht.

Hier der Aufbau meiner Tabelle

Ich habe eine Tabelle mit den ausgefüllten Spalten A , B , C.

In Spalte A werden Namen eingetragen.

In Spalte B wird das Geschlecht eingetragen (ist aber für mein Problem egal)

In Spalte C werden Orte eingetragen

Hier die Schilderung meines Problems

Mit Hilfe der „Bedingten Formatierung“ sollen nun manche Zeilen (A bis C) mit unterschiedlichen Hintergrundfarben versehen werden.

1. In Spalte A sind Namen eingetragen. Bei „nur“ gleichen Namen in Spalte A soll keine Hintergrundfarbe erscheinen.

2. In Spalte C sind Orte eingetragen. Wenn der gleiche Ort mehrmals eingetragen ist, sollen alle Zeilen mit diesem Ort Grün erscheinen

3. Sind jedoch gleiche Kombinationen (Name + Ort) vorhanden, sollen alle Zeilen mit dieser Kombination in Rot erscheinen

Um das ganze etwas deutlicher zu erklären habe ich hier eine Beispielmappe zum ansehen hoch geladen.

Ich würde mich freuen, wenn mir jemand die entsprechend notwendigen Formeln für die bedingte Formatierung nennen würde.

Vielen Dank und Gruß
N.N

[abgemeldet5]

Hallo

Die erste bedingte Formatierung die „WAHR“ ist wird eingestellt.
Da die Grüne bedingte Formatierung auch bei der roten bedingten Formatierung „WAHR“ ist, muss die ROTE zuerst in der Liste stehen!

=SUMMENPRODUKT((($A$9:blush:A$17&$C$9:blush:C$17)=($A9&$C9))*1)>1
Format: ROT

=ZÄHLENWENN($C$9:blush:C$17;$C9)>1
Format: Grün

Ich kann das leider nicht testen, da ich kein Excel2002 mehr habe, sollte aber funktionieren

Gruß Holger

[N_N_a8952a]

Hallo Holger,

tausend Dank für deine schnelle und dann auch noch richtige Antwort.

Ich kann das leider nicht testen, da ich kein Excel2002 mehr habe

Umso größer ist meine Hochachtung für deine Hilfestellung.

sollte aber funktionieren

„sollte“ nicht nur, sondern funktioniert tatsächlich. Also noch Mal vielen Dank und Gruß

N.N

[N_N_a8952a]

Nachfrage, weil zu früh gefreut
Hallo Holger,

leider habe ich mich zu früh gefreut.

Deine Formeln für die Beispieltabelle von mir funktionieren zwar, aber für die Originaltabelle müsste ich sie anpassen. Bei deiner Formel für die Beispieltabelle werden mit der „Bedingten Formatierung“ ja nur die Bereiche A9 bis A17 und C9 bis C17 überwacht.

Bei meiner Originaltabelle müssten allerdings die gesamte Spalte A und die gesamte Spalte C überwacht werden.

Deine zweite Formel (für grün)

=ZÄHLENWENN($C$9:blush:C$17;$C9)>1

konnte ich erfolgreich in diese Formel ändern

=ZÄHLENWENN($C:blush:C;$C1)>1

Aber bei der ersten Formel bin ich leider erfolglos. Ich habe deine Formel (für rot)

=SUMMENPRODUKT((($A$9:blush:A$17&$C$9:blush:C$17)=($A9&$C9))*1)>1

nach ähnlichem Muster geändert

=SUMMENPRODUKT((($A:blush:A&$C:blush:C)=($A1&$C1))*1)>1

Doch dann werden auch die Zeilen die eigentlich rot sein sollten mit grünem Hintergrund angezeigt.

Könntest du also bitte noch mal so nett sein und die Formeln so anpassen dass die überwachten Bereiche nicht begrenzt sind, sondern die gesamte Spalte A und C überwachen?

Vielen Dank und Gruß
N.N

[Reinhard_fb8cb7]

Hallo N.N.,

konnte ich erfolgreich in diese Formel ändern

=ZÄHLENWENN($C:blush:C;$C1)>1

das zeigt daß Zählenwenn() mit ganzen Spalten arbeiten kann.

Aber bei der ersten Formel bin ich leider erfolglos. Ich habe
deine Formel (für rot)

=SUMMENPRODUKT((($A$9:blush:A$17&$C$9:blush:C$17)=($A9&$C9))*1)>1

nach ähnlichem Muster geändert

=SUMMENPRODUKT((($A:blush:A&$C:blush:C)=($A1&$C1))*1)>1

Doch dann werden auch die Zeilen die eigentlich rot sein
sollten mit grünem Hintergrund angezeigt.

Probiere mal diese Formel:
=SUMMENPRODUKT((A1:A100=A2)*(C1:C100=C2))>1
wenn du in A1:C1 Titel hast.

Ganze Spalten in Summenprodukt gehen erst ab XL 2007.

Gruß
Reinhard

[abgemeldet5]

=ZÄHLENWENN($C$9:blush:C$17;$C9)>1
konnte ich erfolgreich in diese Formel ändern
=ZÄHLENWENN($C:blush:C;$C1)>1

Hallo und ok,
dann funktionieren in diesem speziellen Fall ganze Spalten.
meistens geht das bei Excel2002 nicht.

=SUMMENPRODUKT((($A:blush:A&$C:blush:C)=($A1&$C1))*1)>1

Da liegt hier auch „der Hund begraben“ =SUMMENPRODUKT kann sehr wahrscheinlich keine ganzen Spalten.

=SUMMENPRODUKT((($A$1:blush:A$65500&$C$1:blush:C$65500)=($A1&$C1))*1)>1

1. Ich meine Excel2002 hat [glaub] ungefähr 65500 Zeilen sonnst anpassen

2. Das ist ein riesiger Bereich, entsprechend langsam könnte dein Excel werden

3. Die oberste relevante Zeile muss überall in der Formel stehen [hier ist das die 1]

4. Die Zählbereiche $A$1:blush:A$65500&$C$1:blush:C$65500 müssen absolut sein [mit $]

5. Die Prüfzeile muss Spalte absolut : Zeile relativ sein [Spalte A+C mit $ die Zeilen 1 ohne $]

Und natürlich diese Formatierung wieder zuerst in der Liste
Dann funktioniert das auch
Gruß Holger

[N_N_a8952a]

Hallo Holger,

Da liegt hier auch „der Hund begraben“ =SUMMENPRODUKT kann sehr wahrscheinlich keine ganzen Spalten.

=SUMMENPRODUKT((($A$1:blush:A$65500&$C$1:blush:C$65500)=($A1&$C1))*1)>1

Noch mal vielen Dank für deine Mühe. Nachdem ich deine erste Formel für rot nicht auf ganze Spalten anpassen konnte, hatte ich selber schon versucht deine Formel so zu ändern dass wenigstens der Bereich bis Zeile 5000 funktioniert. Das hat im Prinzip auch funktioniert, aber in der Praxis war dadurch Excel so langsam geworden dass damit kein vernünftiges arbeiten möglich war.

Ich meine Excel2002 hat [glaub] ungefähr 65500 Zeilen sonnst anpassen
Das ist ein riesiger Bereich, entsprechend langsam könnte dein Excel werden

Wenn ich jetzt (wie du vorgeschlagen hast) mit deiner neuen Formel den Bereich bis zur Zeile 65500 ausweite, dann kann ich Excel gleich in Rente schicken. Also wird das wohl nichts werden, so dass ich nach einer anderen Lösung suchen muss.

Trotzdem nochmals vielen Dank.

Gruß
N.N

[abgemeldet5]

Hallo
ich denke das Zählenwenn() deutlich schneller ist.
Wie wäre es mit einer Hilfsspalte die A und C zusammenfügen:

Hilfsspalte zB. Z:
in Z1 zum runter kopieren:
=A1&C1

Jetzt verwendest du für „ROT“ die gleiche Formel wie für „GRÜN“ :
=ZÄHLENWENN($Z:blush:Z;$Z1)>1
Die Formel muss immer noch zuerst kommen.

Sollen leere Zellen nicht mit formatiert werden kann man die Formel etwas erweitern:
=(ZÄHLENWENN($Z:blush:Z;$Z1)*($Z1""))>1

Gruß Holger

[N_N_a8952a]

Hallo Reinhard,

auch dir möchte ich für deine Mühe danken.

Probiere mal diese Formel:
=SUMMENPRODUKT((A1:A100=A2)*(C1:C100=C2))>1
wenn du in A1:C1 Titel hast.

das habe ich versucht, allerdings kommen dabei unrauchbare Ergebnisse zustande. Es werden falsche Zeilen und falsche Farben verwendet. Auch sind die falschen Zeilen nicht komplett, sondern nur in einzelnen Bereichen farbig.

Da das nur schwer zu erklären ist, habe ich eine neue Beispieltabelle hoch geladen auf der man im oberen Bereich sehen kann was deine Formel bewirkt. Im unteren Bereich sieht man das Ergebnis von Holgers Formel.

Siehe hier die Beispieltabelle mit deiner Formel

Ganze Spalten in Summenprodukt gehen erst ab XL 2007

Das steht mir leider nicht zur Verfügung. Und wie ich schon „Holger“ geantwortet habe, ist eine Begrenzung bis z.B. Zeile A5000 + C5000 wegen der wahnsinnigen Verlangsamung von Excel in der Praxis nicht zu gebrauchen. Also werde ich mir „wohl oder übel“ etwas anderes einfallen lassen müssen.

Trotzdem noch mal vielen Dank für deine Mühe.

Gruß
N.N

[N_N_a8952a]

Hallo Holger,

ich hätte nicht gedacht dass ich so schnell (um diese Uhrzeit) eine Antwort von dir bekomme. Dafür schon mal meinen besonderen Dank.

Deine Idee mit der Hilfsspalte hört sich recht gut an. Allerdings muss ich mich langsam mal in Richtung Bett bewegen da die Nacht nur noch kurz ist. Und ein wenig Schlaf möchte ich doch noch bekommen.

Aber sobald ich morgen (heute) etwas Zeit übrig habe werde ich mal deinen Vorschlag testen. Das Ergebniss meines Tests teile ich dann schnellstmöglich mit.

Ich verabschiede mich für diese Nacht, sage noch mal vielen Dank und angenehmen Schlaf.

Gruß
N.N

[abgemeldet5]

Gerne,

Übrigens
Die Formeln verhalten sich genau so wie in den Zellen selbst.
Absolute Bezüge bleiben wo sie sind, relative Bezüge passen sich an.

DIE beschriebenen absoluten und relativen Bezüge sind zwingend notwendig!

Unabhängig davon welche Funktionen angewendet werden!
Sonnst funktioniert keine Formel!

Damit du dich nicht „verrennst“ nehme ich hier mal kurz Bezug auf die Formel von Reinhard

Probiere mal diese Formel:
=SUMMENPRODUKT((A1:A100=A2)*(C1:C100=C2))>1

> das kann so nicht funktionieren wenn dort nicht $A$1:blush:A$100 bzw. $C$1:blush:C$100 steht.
> außerdem müssen die Spalten $A2 und $C2 absolut sein damit sie in der Spalte [A und C] bleiben.
Wenn du das nicht berücksichtigst bekommst du nie das richtige angezeigt!

Gruß Holger

[N_N_a8952a]

Rückmeldung - Problem gelöst - Danke
Hallo Holger,

wie versprochen möchte ich die verdiente Rückmeldung liefern.

Ich habe mein Problem nun dank deiner großartigen Hilfe lösen können. Für die bedingten Formatierungen habe ich folgende Formeln (mit Hilfsspalte) benutzt:

Formel 1 für Rot
=(ZÄHLENWENN($Z:blush:Z;$Z1)*($Z1""))>1

Formel 2 für Grün
=ZÄHLENWENN($C:blush:C;$C1)>1

Beide Formeln funktionieren einwandfrei und erledigen die Aufgabe so wie ich es mir gewünscht habe. Auch die Geschwindigkeit ist OK. Man merkt zwar dass Excel da etwas länger braucht, aber bei ca. 5000 Zeilen ist das noch erträglich und ich kann damit sehr gut leben.

Damit du dich nicht „verrennst“ nehme ich hier mal kurz Bezug auf die Formel von Reinhard

Probiere mal diese Formel:
=SUMMENPRODUKT((A1:A100=A2)*(C1:C100=C2))>1

> das kann so nicht funktionieren wenn dort nicht $A$1:blush:A$100 bzw. $C$1:blush:C$100 steht.
> außerdem müssen die Spalten $A2 und $C2 absolut sein damit sie in der Spalte [A und C] bleiben.

Wenn du das nicht berücksichtigst bekommst du nie das richtige angezeigt!

Dass diese Formel nicht funktioniert ist mir auch aufgefallen, und habe das bereits Reinhard mitgeteilt. Obwohl ich normalerweise auch Reinhard als sehr hilfsbereit und fähig kennen gelernt habe (hier im Forum), hat er sich wohl diesmal ausnahmsweise ein wenig vertan. Trotzdem finde ich es sehr nett dass auch er sich bemüht hat um mir zu helfen.

Also zum Schluss bleibt mir nur noch übrig mich noch mal ganz herzlich für die großartige Hilfe zu bedanken.

Gruß
N.N

[Reinhard_fb8cb7]

Dass diese Formel nicht funktioniert ist mir auch aufgefallen,
und habe das bereits Reinhard mitgeteilt. Obwohl ich
normalerweise auch Reinhard als sehr hilfsbereit und fähig
kennen gelernt habe (hier im Forum), hat er sich wohl diesmal
ausnahmsweise ein wenig vertan. Trotzdem finde ich es sehr
nett dass auch er sich bemüht hat um mir zu helfen.

Hallo N.N.,

ich habe einige chronische/permanente Fehler Darunter auch den daß
ich grundsätzlich, warum auch immer, zumindest sehr oft vergesse die
Dollarzeichen in die Formeln einzusetzen

Fiel mir erst auf als ich die Dollarzeichenerwähnung bei Holger las.
Aber da stand schon im Raum daß Summenprodukt() schon bei
schlappen 5.000 Zeilen nervig langsam ist. Und Holger hatte dir schon den
richtigen Hinweis auf Hilfsspalte gegeben.

Deshalb habe ich meinen Lösungsansatz nicht verbessert sondern abgewartet
was du so machst mit der Hilfsspalte. Du hast es gelöst, alles in Butter *find*

PS: ab XL2007 gibt es ja >1.000.000 Zellen. Ich glaube vernünftig kann man
da nur arbeiten wenn man die automatische Berechnung abstellt während der
Eingabezeit. Dann F9 und einen von mir geliebten Cafe au Lait trinken
gehen der in den kleinen Pötten ohne Henkel gereicht wird *gg*

Gruß
Reinhard

[abgemeldet5]

Dass diese Formel nicht funktioniert ist mir auch aufgefallen, und habe das bereits Reinhard mitgeteilt. Obwohl ich normalerweise auch Reinhard als sehr hilfsbereit und fähig kennen gelernt habe (hier im Forum), hat er sich wohl diesmal ausnahmsweise ein wenig vertan. Trotzdem finde ich es sehr nett dass auch er sich bemüht hat um mir zu helfen.

Hallo,
das kann ich voll und ganz unterstreichen. Mit einer Ausnahme:
Reinhard hat sich nicht wirklich vertan,
Die Formel funktioniert einzeln eingefügt einwandfrei,
Sie lässt sich nur nicht auf weitere Zellen kopieren, da sich die Bezüge verändern.

Du hasst in deinem Beispiel zu allem Überfluss ein weiteres Problem erzeugt, was die Fehlersuche natürlich noch weiter erschwert.
Dadurch das der Bereich bis Zeile 100 geht wird der untere Bereich mit geprüft.
Logisch das hier wirklich alles doppelt vorhanden ist.

OK, schön das es funktioniert und danke fürs Feedback
Holger