Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
972to976
972to976
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Bedingte Formatierung mit Formel

Forumthread: Bedingte Formatierung mit Formel

Bedingte Formatierung mit Formel
28.04.2008 16:39:00
Markus
Hallo liebe Excel Freunde,
Seit einiger Zeit versuche ich mich ohne jeglichen Erfolg am nachfolgend beschriebenen Problem:
Bedingte Formatierung mit 3 Bedingungen:
Vergleich mit Datensätzen aus Tabellenblatt Download
1) Zellwert entspricht Wert in Spalte A UND ($B$1) in Spalte C
2) Zellwert entspricht Wert in Spalte D UND ($B$1) in Spalte C
3) Zellwert entspricht Wert in Spalte A UND ($B$1) in Spalte C UND ("nicht leer") in Spalte D
Zur Veranschaulichung nachstehend eine vereinfachte Beispieldatei:
https://www.herber.de/bbs/user/51933.xls
Ich wäre wirklich extrem dankbar, wenn mir jemand helfen könnte.
Beste Grüsse
Markus

Anzeige

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bedingte Formatierung mit Formel
29.04.2008 00:24:22
Daniel
Hi
folgendes mal grundsätzlich:
- wenn du mit bedingten Formatierungen arbeitest, und Zellbezüge zu anderen Tabellenblättern bestehen, müssen diese als NAMEN definiert sein, direkte Bezugsangaben funktionieren nicht.
- du musst dir über die Reihenfolge der bedingungen genau im klaren sein, da nur eine von den drei Bedingten Formatierungen zur Anwendung kommt:
Bedingung 2 wird nur dann geprüft, wenn Bedingung 1 nicht erfüllt werden konnte.
in deinem Fall sollte die Reihenfolge wahrscheinlich so sein:
- Bedingung 1: Fall 2 (dunkelgrün und fett)
- Bedingung 2: Fall 3 (hellgrün und fett)
- Bedingung 3: Fall 1 (nur Fett)
Hier für Fall 1 und Fall 2 die Formeln der Bedingten Formatierung (geschrieben für Zelle B4)
Fall 2 (dunkelgrün):

=ZÄHLENWENN(BEREICH.VERSCHIEBEN(Werk;VERGLEICH($B$1;Werk_S;0)-1;1;ZÄHLENWENN(Werk_S;$B$1);1);B4) >0


Fall 1 (Fett)


=ZÄHLENWENN(BEREICH.VERSCHIEBEN(Werk;VERGLEICH($B$1;Werk_S;0)-1;-2;ZÄHLENWENN(Werk_S;$B$1);1);B4) >0


für fall 3 ist es mir jetzt zu komplex, die Formel zu entwickeln, da du hier zusätzlich zu Fall 2 noch zusätzlich mit einer Vergleichsfunktion prüfen musst, in welcher Zeile sich der Wert in spalte A befindet und ob die Zelle in der Spalte D leer ist.
die verwendeten Namen WERK und WERK_S beziehen sich auf das Tabellenblatt Download und müssen entsprechend angelegt werden:
WERK: =Download!$C$1
WERK_S: =Download!$C:$C
die Tabelle Download muss nach der Spalte C (Werk) sortiert sein.
Gruß, Daniel

Anzeige
AW: Bedingte Formatierung mit Formel
29.04.2008 07:51:00
Markus
Hallo Daniel,
Ein Licht am Horizont :-) Ich hatte mich in einer Sackgasse verrannt und kam mit meinen Überlegungen nicht mehr weiter.
Erstmal vielen herzlichen Dank für Deine Hinweise. Ich muss mir die Reihenfolge der Bedingungen nochmals gründlich überlegen und werde versuchen, Deine vorgeschlagenen Formeln entsprechend einzusetzen.
Ich melde mich wieder. Nochmals vielen Dank und beste Grüsse.
Markus

Anzeige
AW: Bedingte Formatierung mit Formel
29.04.2008 14:57:00
Markus
Hallo,
Ich habe die Vorschläge von Daniel umgesetzt. Funktioniert soweit perfekt.
Bedingung 1 und 3 habe ich eingebaut, es fehlt nun noch die (komplizierteste) Formel für die Bedingung 2.
Im untenstehenden Upload habe ich den bisherigen Stand wie auch die Beschreibung der noch fehlenden Bedingung dokumentiert.
https://www.herber.de/bbs/user/51962.xls
Falls mir jemand auch noch bei der dritten Formel helfen könnte wäre dies genial.
Ich habe noch eine zusätzliche Frage in die Runde:
Wenn ich eine längere Formel in die bedingte Formatierung hineinkopiere sehe ich nur einen Teil davon, da das Fenster zu klein ist. Wenn ich nun aber in das Fenster hineinklicke um etwas zu ändern kann ich mich mit dem Cursor nicht bewegen, die Formel verändert sich. Gibt es da einen Trick?
Beste Grüsse
Markus

Anzeige
AW: Bedingte Formatierung mit Formel
29.04.2008 19:27:00
Daniel
Hi
mir ist da auch nichts bekannt, da die Cursortasten eine Verschiebung der aktuellen Zellreferenz bewirken
da hilft nur, eben nicht mit den Cursortasten, sondern nur mit der Maus zu arbeiten.
Microsoft hat da leider was sehr unkomfortables programmiert.
bei langen Formeln empfiehlt es sich daher, die Formel erst in einer normalen Zelle zusammenzustellen und dann den kompletten Formeltext in die Bedingte Formatierung zu kopieren.
(hierzu den Formeltext in der Bearbeitungszeile markieren und kopieren, nicht nur die Zelle)
die Formel muss immer so aufgebaut sein, daß sie einen Wahrheitswert (WAHR/FALSCH) als ergebnis hat.
die Formel für deine 2. Bedingung sieht so aus (wieder für B4):

=INDEX(BEREICH.VERSCHIEBEN(Werk;VERGLEICH($B$1;Werk_S;0)-1;1;ZÄHLENWENN(Werk_S;$B$1);1); _
VERGLEICH(B4;BEREICH.VERSCHIEBEN(Werk;VERGLEICH($B$1;Werk_S;0)-1;-2;ZÄHLENWENN(Werk_S;$B$1);1);0);1)""


Gruß, Daniel

Anzeige
AW: Bedingte Formatierung mit Formel
30.04.2008 11:38:00
Markus
Hallo Daniel,
Ich habe Deinen Formelvorschlag für die zweite Bedingung eingebaut. Funktioniert genauso, wie ich mir das vorgestellt habe. Vielen herzlichen Dank für Deine Unterstützung, war mir eine grosse Hilfe. Alleine hätte ich das mit meinen (noch) bescheidenen Excel Kenntnissen nicht hingekriegt.
Hast Du den Hinweis von Erich G. zu meiner Zusatzfrage (editieren von Formeln) gesehn? Mit F2 geht es also.
Ich werde nun noch die Formelvarianten von Erich G. testen. Durch seinen Hinweis bin ich noch auf einen möglichen Fehler in den Daten gestossen, der zusätzlich noch überprüft werden müsste. Ich werde dazu auf seinen Beitrag noch separat antworten.
Mein Hauptproblem ist aber gelöst.
Beste Grüsse und danke nochmals.
Markus

Anzeige
AW: Bedingte Formatierung mit Formel
30.04.2008 23:27:24
Daniel
HI
die Funktion der F2-Taste kannte ich in diesem Zusammenhang noch nicht, vielen dank für den Hinweis (natürlich auch an Erich)
Gruß, Daniel

AW: Bedingte Formatierung mit Formel
29.04.2008 20:19:06
Erich
Hallo Markus,
vielleicht verstehe ich deine Bedingungen ja falsch - vielleicht aber auch nicht.
Ich würde es so machen:
 D
4117
5120

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
D41. / Formel ist =SUMMENPRODUKT(($B$1&""=Werk_S)*(D4&""=Nachf_S)) >0Abc
D42. / Formel ist =SUMMENPRODUKT(($B$1&""=Werk_S)*(D4&""=Mat_S)*(Werk_S<>"")) >0Abc
D43. / Formel ist =SUMMENPRODUKT(($B$1&""=Werk_S)*(D4&""=Mat_S)) >0Abc
D51. / Formel ist =SUMMENPRODUKT(($B$1&""=Werk_S)*(D5&""=Nachf_S)) >0Abc
D52. / Formel ist =SUMMENPRODUKT(($B$1&""=Werk_S)*(D5&""=Mat_S)*(Werk_S<>"")) >0Abc
D53. / Formel ist =SUMMENPRODUKT(($B$1&""=Werk_S)*(D5&""=Mat_S)) >0Abc
Namen in Formeln
ZelleNameBezieht sich auf
D4Mat_S=Download!$A$2:$A$1000
D4Nachf_S=Download!$D$2:$D$1000
D4Werk_S=Download!$C$2:$C$1000
D5Mat_S=Download!$A$2:$A$1000
D5Nachf_S=Download!$D$2:$D$1000
D5Werk_S=Download!$C$2:$C$1000

Wenn ich in der Beispielmappe Download!D2 von 117 auf 116 setze, bleibt mit der alten Formel
Übersicht Werk 200!D4 dunkelgrün. Mit der neuen Formel ist die Bed. 1 nicht mehr erfüllt.
Hier die Mappe (bed. Formatierung kannst du von D4 auf andere Zellen mit dem Pinsel übertragen.)
https://www.herber.de/bbs/user/51967.xls
Noch ein Tipp: Bei den Formeln für die bed. Formatierung kannst du mit Taste F2 in den Bearbeitungsmodus
wechseln - so wie bei einer Zelle.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Bedingte Formatierung mit Formel
30.04.2008 11:44:05
Markus
Hallo Erich,
Besten Dank für Deine Hinweise. Ich werde Deinen Vorschlag in meiner Originaldatei implementieren und testen. Benötige dazu aber noch etwas Zeit. Durch Deinen Hinweis:
Wenn ich in der Beispielmappe Download!D2 von 117 auf 116 setze, bleibt mit der alten Formel
Übersicht Werk 200!D4 dunkelgrün. Mit der neuen Formel ist die Bed. 1 nicht mehr erfüllt.
bin ich auf einen anderen möglichen Fehler gestossen.
Ich werde mich auf jeden Fall nochmals melden.
Beste Grüsse
Markus

Anzeige
AW: Bedingte Formatierung mit Formel
30.04.2008 15:12:09
Markus
Hallo Erich,
Ich habe Deine Vorschläge integriert:
https://www.herber.de/bbs/user/51984.xls
Hier mein Feedback:
1) Du hast meine Bedingungen völlig richtig verstanden.
2) Ich musste eine kleine Korrektur bei der zweiten Bedingung machen (rot markiert in Zeile 30)
3) Es gibt noch einen Schönheitsfehler: Leere Zellen werden ebenfalls grün markiert (siehe Upload)
Neues Problem:
Durch Deinen Hinweis (Änderung von 117 zu 116 auf D2) bin ich auf eine Schwachstelle meines Konzeptes gestossen. Die 116 als Nachfolger ist nicht zulässig, da kein Datensatz mit Materialnummer 116 existiert. Dies kann jedoch (durch eine Fehleingabe im SAP) vorkommen. Ausserdem muss ich die Vollständigkeit meiner Übersichtstabelle überprüfen. Ich möchte also meinen Download auf mögliche Fehler überprüfen. Die Datensätze im Download müssten also nach 3 Kriterien überprüft werden (Siehe Beschreibung in Spalten F..G..H von Download (siehe Beispiel). Könntest Du mir evtl. auch dabei noch helfen?
Fazit (schon jetzt):
Dank Deiner Hilfe steht mir nun ein Werkzeug für die Datenüberprüfung zur Verfügung mit dem ich Fehler in den SAP Stammdaten sehr schnell visuell erkennen kann. Ich bin begeistert. Vielen herzlichen Dank.
Markus

Anzeige
Die Frage ist noch offen owT
30.04.2008 15:19:54
Markus

AW: Bedingte Formatierung mit Formel
30.04.2008 16:53:00
Erich
Hi Markus,
die Prüfungen könnten so funzen:
 ACDEFGH
1MaterialWerkNachfolgemat. Überprüfung 1Überprüfung 2Überprüfung 3
2100200116 Fehler Fehler

Formeln der Tabelle
ZelleFormel
F2=WENN((D2>"")*ISTNV(VERGLEICH(D2;Mat_S;0)); "Fehler";"")
G2=WENN((A2="")+ZÄHLENWENN('Übersicht Werk 200 neu'!$B$4:$F$9;Download!A2); "";"Fehler")
H2=WENN((D2="")+ZÄHLENWENN('Übersicht Werk 200 neu'!$B$4:$F$9;Download!D2); "";"Fehler")

und die bed. Formatierung kannst du wg. der leeren Zellen so erweitern:
 B
3deaktiviert 2
4 

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B41. / Formel ist =(B4>0)*SUMMENPRODUKT(($B$1&""=Werk_S)*(B4&""=Nachf_S)) >0Abc
B42. / Formel ist =(B4>0)*SUMMENPRODUKT(($B$1&""=Werk_S)*(B4&""=Mat_S)*(Nachf_S<>"")) >0Abc
B43. / Formel ist =(B4>0)*SUMMENPRODUKT(($B$1&""=Werk_S)*(B4&""=Mat_S)) >0Abc
Namen in Formeln
ZelleNameBezieht sich auf
B4Mat_S=Download!$A$2:$A$1000
B4Nachf_S=Download!$D$2:$D$1000
B4Werk_S=Download!$C$2:$C$1000

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Bedingte Formatierung mit Formel
30.04.2008 18:00:00
Markus
Hallo Erich,
Ich bin restlos begeistert! Vielen herzlichen Dank. Alles funktioniert perfekt. Eine kleine Verständnisfrage hätte ich noch.
Wozu dient / was bewirkt der Formelteil &"" hinter der jeweiligen Zelladresse bei den Formeln für die bedingte Formatierung?
Herzliche Grüsse
Markus

Anzeige
AW: Bedingte Formatierung mit Formel
30.04.2008 19:08:00
Erich
Hi Markus,
freut mich, dass es funzt!
Das &"" war nötig, weil du die Materialnr. in der Übersicht als Zahlen stehen hast, in Download aber als Texte.
(Siehst du daran, dass ein Text (normalerweise) linksbündig in der Zelle steht.)
B4 ist eine Zahl, B4&"" ein Text - und der lässt sich dann mit den Werten in Download vergleichen.
Umgekehrt kannst du aus einem Text "123" per Formel eine Zahl machen:
="123"*1 oder ="123"+0 oder =--"123" ergeben alle die Zahl 123
Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Bedingte Formatierung mit Formel
01.05.2008 09:04:00
Erich
Hi Markus,
für den Fall, dass du das hier noch liest:
Die "offizielle" Methode für die Erzeugung eines Textes aus einer Zahl ist natürlich =TEXT(123;"000")
=123&"" ist nur eine Abkürzung.
Es gibt aber einen Unterschied: =12&"" ergibt "12", =TEXT(12;"000") aber "012".
Das kann wichtig sein, wenn deine Artikelnummern zweistellig sein können und die Texte führende Nullen haben.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Bedingte Formatierung mit Formel
01.05.2008 10:03:00
Markus
Hallo Erich,
Zum Glück habe ich Dein letztes Post noch gelesen. Jetzt wird mir einiges klar. Ich hatte immer ein Durcheinander mit Text- und Zahlenformat in meiner Originaldatei. Ich kontte sie nicht uploaden, da sie zu umfangreich ist und vertrauliche Daten enthält. Tatsache ist, dass der Download im Textformat anfällt. So ist z.B. das Feld für das Werk 0200 im Textformat. Früher hatte ich immer den Download "von Hand" in Zahlen umgewandelt. Mit Deiner Formel ist dies nun nicht mehr erforderlich.
In meiner Vorgabetabelle "Übersicht Werk 200" waren die Felder im Zahlenformat gepflegt. Ich bin nun am überlegen, ob ich dies so belassen soll. Der Hauptzweck meiner Tabelle ist die Überwachung der korrekten Datenpflege der Nachfolger in den verschiedenen Werken. Es sind mehrere Werke zu beobachten (in meinem Beispiel habe ich nur die Werke 0200 und 1800 aufgeführt). Vorgabe bezüglich Reihenfolge Vorgänger_Nachfolger ist jedoch das Werk 200. Ich habe mir deshalb vorgestellt (wie im Beipiel angedeutet), mehrere Tabellenblätter anzulegen (durch Kopieren), die Werksnummer in Feld B1 entsprechend einzusetzen und die Vorgabewerte mit Formeln zu übertragen.
Ich meine dies müsste so funkionnieren.
Nochmals vielen herzlichen Dank für Deine Hilfe und schönen Feiertag. Ich habe viel gelernt.
Grüsse aus der Schweiz
Markus
Anzeige
;

Forumthreads zu verwandten Themen

Zellen kopieren (mit Formatierung)
Yves

Hallo zusammen,
ich möchte Daten von einem Tabellenblatt (Tabelle2) in ein anderes Tabellenblatt (Tabelle1) kopieren.
Habe dies bis anhin so gelöst:
sheets("Tabelle1").cells(1,1).value = sheets("Tabelle2").cells(5,1).value
Somit wurden einfach nur die Werte 'kopiert'. Nun würde...
08.08.2008 14:09:18
Thread-ID: 1000289
8 Beiträge
Bedingte formatierung mit Wenn
kdl

Hallo, ich freue mich dieses Forum gefunden zu haben. An Hand eurer Beiträge konnte ich schon einiges dazulernen. In der letzten Zeit habe ich mich damit beschäftigt mit Excel zu zeichnen, über bedingte Formatierung (Rahmen zu weisen) erstelle ich die Draufsicht einer Arbeitsplatte.
Meine e...
12.09.2008 14:06:00
Thread-ID: 1009364
2 Beiträge
Bedingte Formatierung mit Bereich
Burghard

Hallo,
ich habe einen Fotokalender gebastelt (für jeden Monat ein Blatt).
Das Jahr läßt sich im 1. Tabellenblatt in Zelle A1 einstellen. Alle anderen Tabellenblätter ändern sich entsprechend.
In den Monaten habe ich verschiedene Bereiche mit einer bedingten Formatierung versehen, um...
05.10.2008 16:09:12
Thread-ID: 1014259
6 Beiträge
Anzeige
SVERWEIS funktioniert nicht
sascha76er

Hallo,
hoffentlich kann mir jemand hier helfen, bei mir funktionieren die SVERWEISE für die Spalten
AI, AQ, AR, AS
nicht.
Leider ist meine Liste größer wie 300 kb, deshalb musste ich die Liste bei einem 1Klickhoster bereitstellen.
Liste: http://www.mediafire.com/?oxyk0vmb21x...
08.10.2008 11:23:24
Thread-ID: 1014967
7 Beiträge
Anzeige
SVERWEIS mit Text funktioniert nicht
sascha76er

Hallo,
komme einfach nicht weiter bei meinem Sverweis, vielleicht kann sich jemand mal meine Beispieldatei angucken?
Beispiel: https://www.herber.de/bbs/user/56594.xls
Vorab vielen Dank
Sascha
07.11.2008 13:38:00
Thread-ID: 1022656
5 Beiträge
Bedingte Formatierung mit Formeln beschränkt?
Vera

Hallo. Stoße gerade auf ein seltsames Phänomen:
wenn ich eine bedingte Formatierung auf ein Formelergebnis lege, klappt das gut. Das Formelergebnis habe ich dann runterkopiert - auch dann ist die bedingte Formatierung noch ok. Aber ab einer gewissen Anzahl Zeilen klappts dann nicht mehr.
16.01.2009 10:39:21
Thread-ID: 1040464
9 Beiträge
Anzeige
Bedingte Formatierung mit VBA-Modul
Fuhrer

Hallo zusammen
Ich will mit einem VBA-Modul einen bestimmten Bereich nach definierten Kriterien einfärben.
Das Modul wird später einem Comnand-Button Zugeordent (dies ist für mich kein Problem).
Nun konnte ich im Internet ein entprechendes Makro finden. Jedoch ist dieses Makro nur f...
19.01.2009 18:03:57
Thread-ID: 1041611
6 Beiträge
bedingte Formatierung mit Datum
Andy

Guten Tag,
ich habe eine Frage.
Ich würde gerne die folgende Bedienung in einer bedingten Formatierung umsetzen.
wenn ein Datum in einer Zelle (A1) nach dem Datum in einer anderen Zelle (A2) ist und eine dritte Zelle (A3) leer ist, dann mache die Zelle (A1) rot.
geht das?
vi...
21.01.2009 16:33:00
Thread-ID: 1042306
3 Beiträge
Anzeige
Excel2007: bedingte Formatierung mit Forme
Thorsten

Hallo,
ich habe ein Problem mit bedingten Formatierungen, die auf Formeln basieren, im Excel 2007.
Ich habe in Exel XP bzw. 2002 Dateien mit bedingten Formatierungen auf Formelbasis erstellt und diese werden im Excel 2007 nicht mehr korrekt abgebildet.
Ich habe dazu eine Datei als...
27.01.2009 15:15:00
Thread-ID: 1044267
8 Beiträge
Bedingte Formatierung mit VBA
Steffen

Hallo,
habe schon viele Einträge gelesen, aber noch keiner war für mein Problem hilfreich oder ich stelle mich nur zu dämlich an. Folgendes Problem:
1. In einer Tabelle, die vom SQl-Server gefüllt wird, ist außerhalb des benannten Bereiches eine Spalte M, deren Zellen eine bestimmte Far...
13.02.2009 09:44:00
Thread-ID: 1050348
4 Beiträge
VBA - Bedingte Formatierung mit For-Next-Schlaufe
Martin

Hallo zusammen
Ich habe ein Makro geschrieben und komme mit der bedingten Formatierung nicht weiter:
Ich habe eine For-Next-Schlaufe, die von unten die Zeilen rauffährt (For i = lezeile To 7 Step -1), unter anderem sollte am Ende der Schlaufe die Spalte A bis J auf der Zeile i jeweils...
12.03.2009 17:38:23
Thread-ID: 1059597
2 Beiträge
VBA - Bedingte Formatierung mit For-Next-Schlaufe
Martin

Hallo zusammen
Ich habe ein Makro geschrieben und komme mit der bedingten Formatierung nicht weiter:
Ich habe eine For-Next-Schlaufe, die von unten die Zeilen rauffährt (For i = lezeile To 7 Step -1), unter anderem sollte am Ende der Schlaufe die Spalte A bis J auf der Zeile i jeweils...
16.03.2009 10:15:21
Thread-ID: 1060621
4 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige