20
Excel 2013: Bedingte Formatierung mit IF und Indirect() wird nicht ausgewertet
Hallo zusammen,
ich möchte eine bedingte Formatierung verwenden, zu der ich den Vergleichswert auf Grund folgender Bedingungen ermittle:
- Prüfe die aktuelle Spalte und lese aus Zeile 2 den Inhalt aus.
- vergleiche diesen mit einem Vergleichswert XY
- Ist der Vergleich erfolgreich, dann lese im Sheet1 den MAX()-Wert aus einer ZeileX von Spalte H-Z aus
- Ist der Vergleich FALSCH, dann springe in die nächste IF-Iteration im FALSE-Zweig von IF
- Vergleiche diese Rückgabe mit 2 (in meinem Beispiel)
- Wenn TRUE, dann formatierte...
Achtung, ich verwende eine englische Office-Version! ADDRESS ist korrekt geschrieben.
Funktionsnamenübersetzung:
INDIRECT=INDIREKT
ADDRESS=ADRESSE
COLUMN=SPALTE
Zur Vereinfachung habe ich das Ganze mal runtergebrochen und getestet:
=INDIRECT(ADDRESS(2;COLUMN();1))="AU" => funktioniert in bedingeter Formatierung: Rückgabe=TRUE
=IF(1=1;MAX(Sheet1!$H$4:$Z$4);"")=2 => funktioniert in bedingeter Formatierung: Rückgabe=TRUE (MAX liefert 2 zurück)
Sobald ich "Indirect()" im IF() auswerten will, erfolgt scheinbar keine Rückgabe von IF. Weder TRUE noch FALSE scheinen durchlaufen zu werden. Ich habe auch schon erfolglos mit "" experimentiert.
=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4);"falsch")=2 => geht nicht
Wer habe eine Idee woran das liegt?
Wer hat die Lösung?
Danke
Michi
ich möchte eine bedingte Formatierung verwenden, zu der ich den Vergleichswert auf Grund folgender Bedingungen ermittle:
- Prüfe die aktuelle Spalte und lese aus Zeile 2 den Inhalt aus.
- vergleiche diesen mit einem Vergleichswert XY
- Ist der Vergleich erfolgreich, dann lese im Sheet1 den MAX()-Wert aus einer ZeileX von Spalte H-Z aus
- Ist der Vergleich FALSCH, dann springe in die nächste IF-Iteration im FALSE-Zweig von IF
- Vergleiche diese Rückgabe mit 2 (in meinem Beispiel)
- Wenn TRUE, dann formatierte...
Achtung, ich verwende eine englische Office-Version! ADDRESS ist korrekt geschrieben.
Funktionsnamenübersetzung:
INDIRECT=INDIREKT
ADDRESS=ADRESSE
COLUMN=SPALTE
Zur Vereinfachung habe ich das Ganze mal runtergebrochen und getestet:
=INDIRECT(ADDRESS(2;COLUMN();1))="AU" => funktioniert in bedingeter Formatierung: Rückgabe=TRUE
=IF(1=1;MAX(Sheet1!$H$4:$Z$4);"")=2 => funktioniert in bedingeter Formatierung: Rückgabe=TRUE (MAX liefert 2 zurück)
Sobald ich "Indirect()" im IF() auswerten will, erfolgt scheinbar keine Rückgabe von IF. Weder TRUE noch FALSE scheinen durchlaufen zu werden. Ich habe auch schon erfolglos mit "" experimentiert.
=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4);"falsch")=2 => geht nicht
Wer habe eine Idee woran das liegt?
Wer hat die Lösung?
Danke
Michi
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 368777
Url: https://administrator.de/contentid/368777
Ausgedruckt am: 26.11.2024 um 12:11 Uhr
20 Kommentare
Neuester Kommentar
Moin,
deine Formel passt, soweit ich die verstanden habe, nicht zu deiner Beschreibung.
Ich denke, du kommst mit =UND und =ODER weiter.
deine Formel passt, soweit ich die verstanden habe, nicht zu deiner Beschreibung.
Ich denke, du kommst mit =UND und =ODER weiter.
Hi,
danke für deine Antwort.
Ne, ich denke, dass mir UND oder ODER an dieser Stelle nicht helfen, denn es funktioniert dies nicht:
=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4);"falsch")=2 => geht nicht
Ich prüfe in der aktullen Spalte auf den Inhalt einer Zelle und gebe auch dem Sheet1 einen Max-Wert aus einer Zeile zurück. Was mir UND oder ODER da helfen soll verstehe ich grad nicht.
Wie ist dein Gedanke?
Grüße
Michi
danke für deine Antwort.
Ne, ich denke, dass mir UND oder ODER an dieser Stelle nicht helfen, denn es funktioniert dies nicht:
=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4);"falsch")=2 => geht nicht
Ich prüfe in der aktullen Spalte auf den Inhalt einer Zelle und gebe auch dem Sheet1 einen Max-Wert aus einer Zeile zurück. Was mir UND oder ODER da helfen soll verstehe ich grad nicht.
Wie ist dein Gedanke?
Grüße
Michi
Naja, wie gesagt, ich verstehe anhand deiner Beschreibung nicht ganz, wie das genau aussehen soll.
Mir schwebt da was in der Art vor:
Mir schwebt da was in der Art vor:
=und(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4)=2
Hi,
Achtung, das Erste im If ist die Bedinung. Wenn die TRUE ist, wird aus Sheet1 der Max-Wert ausgelesen.
UND ist die falsche Funktion dafür.
Danke trotzdem fürs drüber nachdenken.
Michi
Achtung, das Erste im If ist die Bedinung. Wenn die TRUE ist, wird aus Sheet1 der Max-Wert ausgelesen.
UND ist die falsche Funktion dafür.
Danke trotzdem fürs drüber nachdenken.
Michi
warum?
ergibt? richtig: FALSCH. Perfekt - wenn ich deine Formel richtig verstanden habe
Noch einmal, deine Beschreibung passt nicht zur Formel - erkläre einfach mal mit eigenen Worten, was wann wie passieren soll...
=UND(FALSCH;WAHR)Noch einmal, deine Beschreibung passt nicht zur Formel - erkläre einfach mal mit eigenen Worten, was wann wie passieren soll...
Weil ich keine zwei Bedingungen habe, sondern eine.
UND macht einen logischen Vergleich zwischen zwei Bedingungen.
Ich prüfe eine und führe dann etwas aus.
UND macht einen logischen Vergleich zwischen zwei Bedingungen.
Ich prüfe eine und führe dann etwas aus.
Quark!
Zitat von https://support.office.com/de-de/article/und-funktion-5f19b2e8-e1df-4408 ...
Verwenden Sie die Funktion UND (eine der logischen Funktionen), um zu bestimmen, ob alle Bedingungen in einer Prüfung WAHR sind.
Verwenden Sie die Funktion UND (eine der logischen Funktionen), um zu bestimmen, ob alle Bedingungen in einer Prüfung WAHR sind.
Zitat von https://support.office.com/de-de/article/und-funktion-5f19b2e8-e1df-4408 ...
Verwenden Sie die Funktion UND (eine der logischen Funktionen), um zu bestimmen, ob alle Bedingungen in einer Prüfung WAHR sind.
Verwenden Sie die Funktion UND (eine der logischen Funktionen), um zu bestimmen, ob alle Bedingungen in einer Prüfung WAHR sind.
Wie da auch steht: "bestimmen, ob alle Bedingungen..."
Ich habe in der ersten Iteration nur eine Bedingung. Wenn diese TRUE ist, dann lese ich Werte aus. Wenn sie FALSE ist, gehe ich in die nächste Iterationsebene und habe erst dort eine weitere Bedingung...
Ich verstehe deinen Ansatz nicht. Sorry.
Zitat von @Kraemer:
Noch einmal, deine Beschreibung passt nicht zur Formel - erkläre einfach mal mit eigenen Worten, was wann wie passieren soll...
Noch einmal, deine Beschreibung passt nicht zur Formel - erkläre einfach mal mit eigenen Worten, was wann wie passieren soll...
Ich weiß nicht, wie ich das präziser beschreiben sollte. Ich sehe auch nicht, wo ich mir widerspreche.
Wo siehst du den Widerspruch? Vielleicht kann ich es dann in genau diesem Detail versuchen zu erläutern.
Der Punkt ist einfach: Warum funktioniert INDIRECT in dieser Konstellation nicht?
Welche Alternative habe ich?
=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4);"falsch")=2 => geht nicht
Wo siehst du den Widerspruch? Vielleicht kann ich es dann in genau diesem Detail versuchen zu erläutern.
Der Punkt ist einfach: Warum funktioniert INDIRECT in dieser Konstellation nicht?
Welche Alternative habe ich?
=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4);"falsch")=2 => geht nicht
=and(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4)=2)
Bedenke, dass ich, wenn die Prüfung "INDIRECT(ADDRESS(2;COLUMN();1))="AU";" fehlschlägt weitere Spalten prüfen muss (FALSE-Zweig). Ich hatte die Formel oben vereinfacht und es beschrieben.
Das kann ich mit UND nicht lösen.
Oder etwa doch?
Das kann ich mit UND nicht lösen.
Oder etwa doch?
1. Hast du meine Formel schon einmal getestet?
2.
gibt falsch zurück, wenn nicht in der indirect-Adresse "AU" steht
gibt das Ergebnis aus MAX(Sheet1!$H$4:$Z$4) zurück, wenn in der indirect-Adresse "AU" steht
aus diesem Strang kommen also exakt zwei mögliche Werte:
1. falsch
2. n
hier prüfst du, ob das Ergebnis aus dem if 2 entspricht.
Ergibt drei mögliche Werte:
A) im Fall 1. false
B) im Fall 2. false, wenn 2. <> 2
C) im Fall 2. true, wenn 2. = 2
Vereinfacht prüfst du
und
und zusammen gefasst ergibt das
kommt dir bekannt vor?
Die Formel sagt nichts anderes wie: Mache meine Zellen bunt, wenn INDIRECT(ADDRESS(2;COLUMN();1))="AU" ist und dann auch noch MAX(Sheet1!$H$4:$Z$4)=2 ist.
2.
=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4);"falsch") gibt das Ergebnis aus MAX(Sheet1!$H$4:$Z$4) zurück, wenn in der indirect-Adresse "AU" steht
aus diesem Strang kommen also exakt zwei mögliche Werte:
1. falsch
2. n
=2Ergibt drei mögliche Werte:
A) im Fall 1. false
B) im Fall 2. false, wenn 2. <> 2
C) im Fall 2. true, wenn 2. = 2
Vereinfacht prüfst du
INDIRECT(ADDRESS(2;COLUMN();1))="AU" MAX(Sheet1!$H$4:$Z$4)=2und zusammen gefasst ergibt das
=and(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4)=2) Die Formel sagt nichts anderes wie: Mache meine Zellen bunt, wenn INDIRECT(ADDRESS(2;COLUMN();1))="AU" ist und dann auch noch MAX(Sheet1!$H$4:$Z$4)=2 ist.
Hi,
nein, ich habe deine Formel noch nicht geprüft, weil dir offensichtlich noch immer nicht klar ist, dass diese Prüfung
MAX(Sheet1!$H$4:$Z$4)=2
nur dann durchgeführt werden soll, wenn die erste Prüfung
INDIRECT(ADDRESS(2;COLUMN();1))="AU"
TRUE ergibt.
Wenn die erste Prüfung FALSE ergibt, erfolgt für drei weitere Spalten diesselbe Prüfung in weiteren Iterationen (Verschachtelung) im FALSE-Zweig der IF-Funktion (und zwar wird aus anderen Zeilen der MAX-Wert ausgelesen).
Hier mal die ganze Formel um das zu verdeutlichen:
(Um es auch gleich zu sagen: Nein, ich war nicht derjenige, der den Sheetnamen vergeben hat...
)
=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX('1-CrossRef Prim As. Support As.'!$H$4:$Z$4);IF(INDIRECT(ADDRESS(2;COLUMN();1))="A";MAX('1-CrossRef Prim As. Support As.'!$H$5:$Z$5);IF(INDIRECT(ADDRESS(2;COLUMN();1))="I";MAX('1-CrossRef Prim As. Support As.'!$H$6:$Z$6);IF(INDIRECT(ADDRESS(2;COLUMN();1))="C";MAX('1-CrossRef Prim As. Support As.'!$H$7:$Z$7);"error"))))=2
Grüße
Michi
nein, ich habe deine Formel noch nicht geprüft, weil dir offensichtlich noch immer nicht klar ist, dass diese Prüfung
MAX(Sheet1!$H$4:$Z$4)=2
nur dann durchgeführt werden soll, wenn die erste Prüfung
INDIRECT(ADDRESS(2;COLUMN();1))="AU"
TRUE ergibt.
Wenn die erste Prüfung FALSE ergibt, erfolgt für drei weitere Spalten diesselbe Prüfung in weiteren Iterationen (Verschachtelung) im FALSE-Zweig der IF-Funktion (und zwar wird aus anderen Zeilen der MAX-Wert ausgelesen).
Hier mal die ganze Formel um das zu verdeutlichen:
(Um es auch gleich zu sagen: Nein, ich war nicht derjenige, der den Sheetnamen vergeben hat...
)=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX('1-CrossRef Prim As. Support As.'!$H$4:$Z$4);IF(INDIRECT(ADDRESS(2;COLUMN();1))="A";MAX('1-CrossRef Prim As. Support As.'!$H$5:$Z$5);IF(INDIRECT(ADDRESS(2;COLUMN();1))="I";MAX('1-CrossRef Prim As. Support As.'!$H$6:$Z$6);IF(INDIRECT(ADDRESS(2;COLUMN();1))="C";MAX('1-CrossRef Prim As. Support As.'!$H$7:$Z$7);"error"))))=2
Grüße
Michi
Zitat von @Michi1:
nein, ich habe deine Formel noch nicht geprüft, weil dir offensichtlich noch immer nicht klar ist, dass diese Prüfung
und dir ist noch immer nicht klar, wie die =und funktioniertnein, ich habe deine Formel noch nicht geprüft, weil dir offensichtlich noch immer nicht klar ist, dass diese Prüfung
nur dann durchgeführt werden soll, wenn die erste Prüfung
darauf zu bestehen ist überhaupt nicht nötig, weil es auf das Ergebnis keinen Einfluss hatHier mal die ganze Formel um das zu verdeutlichen:
ja, ist deutlich. Du quälst dich mit einem Konstrukt rum, welches kaum wartbar ist.Nimm 3 von meinen Formeln (separat), mit den passenden Bedingungen und alles ist gut. Und vor allem kapiert man auch in Jahren noch, was da eigentlich geprüft wird.
Deine anfängliche Aussage
Sobald ich "Indirect()" im IF() auswerten will, erfolgt scheinbar keine Rückgabe von IF.
ist nämlich schlicht falsch. Das Problem ist, dass du die falsch auswertest. Und das ist bei so einem Monster auch nicht ungewöhnlich.PS: Das du meinen Vorschlag, der in Sekunden umgesetzt ist, noch nicht einmal getestet hast, ist eigentlich schon frech! Wenn mein Vorschlag nämlich nicht so funktionieren würde, wie du dir das vorstellst, könnten wir klären warum das so ist.
Hi,
so, jetzt habe ich deine Formel probiert:
In der conditional Formatting funktioniert sie nicht. Ich bekomme weder, wenn ich auf =2, noch bei der Prüfung auf <>2 eine Formatierung der Zelle, auf welche die Formel angewendet wird.
Wenn ich die Formel normal im Sheet teste, erhatle ich TRUE, was korrekt ist (bei Prüfung auf =2).
Mir ist jedoch weiterhin schleierhaft, wie die Iteration im FAlse-Zweig mit der UND-Funktion umgesetzt werden könnte.
Idee?
Grüße
Michi
so, jetzt habe ich deine Formel probiert:
In der conditional Formatting funktioniert sie nicht. Ich bekomme weder, wenn ich auf =2, noch bei der Prüfung auf <>2 eine Formatierung der Zelle, auf welche die Formel angewendet wird.
Wenn ich die Formel normal im Sheet teste, erhatle ich TRUE, was korrekt ist (bei Prüfung auf =2).
Mir ist jedoch weiterhin schleierhaft, wie die Iteration im FAlse-Zweig mit der UND-Funktion umgesetzt werden könnte.
Idee?
Grüße
Michi
Hallo zusammen,
hat jemand anderes vielleicht eine hilfreiche Idee dazu?
Danke
Michi
hat jemand anderes vielleicht eine hilfreiche Idee dazu?
Danke
Michi
jupp
Namensmanager
Lege dort einen neuen Wert an, nenne ihn sagen wir mal "Start" und dieser bezieht sich auf
Dann benennst du noch die einzelnen Zeilen auf dem Tabellenblatt 1-Cross... mit ROW_AU, ROW_A etc: http://www.tab-tipps.de/excel/formeln/namen.shtml
Und dann tauscht du in deiner Formel die Bezüge gegen die Namen aus. Frage mich nicht, wo der Unterschied ist - bei meiner UND-Formel gab es auch Probleme - mit den Namen nicht
Namensmanager
Lege dort einen neuen Wert an, nenne ihn sagen wir mal "Start" und dieser bezieht sich auf
=INDIREKT(ADRESSE(2;SPALTE();1))Und dann tauscht du in deiner Formel die Bezüge gegen die Namen aus. Frage mich nicht, wo der Unterschied ist - bei meiner UND-Formel gab es auch Probleme - mit den Namen nicht
Hi,
Juhuu, so hat das jetzt geklappt.
Habe die Formel auch noch erweitert, damit die TRUE-Rückgabe exakt ist.
=AND(IF(INDIRECT_="AU";MAX(AU_ROW);IF(INDIRECT_="A";MAX(A_ROW);IF(INDIRECT_="I";MAX(I_ROW);IF(INDIRECT_="C";MAX(C_ROW);"error"))))=2;INDIRECT_CELL=4)
Vielen lieben Dank und jetzt lass ich dich auch in Ruhe!
Machs gut
Michi
Juhuu, so hat das jetzt geklappt.
Habe die Formel auch noch erweitert, damit die TRUE-Rückgabe exakt ist.
=AND(IF(INDIRECT_="AU";MAX(AU_ROW);IF(INDIRECT_="A";MAX(A_ROW);IF(INDIRECT_="I";MAX(I_ROW);IF(INDIRECT_="C";MAX(C_ROW);"error"))))=2;INDIRECT_CELL=4)
Vielen lieben Dank und jetzt lass ich dich auch in Ruhe!
Machs gut
Michi
jederzeit gerne. Ich knoble mit Vorliebe an solchen Problemen. Du siehst jetzt hoffentlich aber auch ein, wie wichtig es ist, Vorschläge zu testen und dazu eine Rückmeldung zu geben. Nur dadurch war es mir jetzt Möglich, dein Problem nachzuvollziehen
