michi1
Goto Top

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

Content-Key: 368777

Url: https://administrator.de/contentid/368777

Ausgedruckt am: 19.03.2024 um 03:03 Uhr

Mitglied: Kraemer
Kraemer 21.03.2018 um 17:33:31 Uhr
Goto Top
Moin,

deine Formel passt, soweit ich die verstanden habe, nicht zu deiner Beschreibung.
Ich denke, du kommst mit =UND und =ODER weiter.
Mitglied: Michi1
Michi1 21.03.2018 um 19:50:58 Uhr
Goto Top
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
Mitglied: Kraemer
Kraemer 22.03.2018 um 09:18:44 Uhr
Goto Top
Naja, wie gesagt, ich verstehe anhand deiner Beschreibung nicht ganz, wie das genau aussehen soll.

Mir schwebt da was in der Art vor:

=und(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4)=2  
Mitglied: Michi1
Michi1 22.03.2018 um 11:40:23 Uhr
Goto Top
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. face-smile
Michi
Mitglied: Kraemer
Kraemer 22.03.2018 aktualisiert um 12:21:19 Uhr
Goto Top
Zitat von @Michi1:
UND ist die falsche Funktion dafür.
warum?

=UND(FALSCH;WAHR)
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...
Mitglied: Michi1
Michi1 22.03.2018 um 13:48:20 Uhr
Goto Top
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.
Mitglied: Kraemer
Kraemer 22.03.2018 um 13:54:51 Uhr
Goto Top
Zitat von @Michi1:
UND macht einen logischen Vergleich zwischen zwei Bedingungen.
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.
Mitglied: Michi1
Michi1 22.03.2018 um 15:29:24 Uhr
Goto Top
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.

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.
Mitglied: Kraemer
Kraemer 22.03.2018 um 17:00:28 Uhr
Goto Top
Zitat von @Kraemer:
Noch einmal, deine Beschreibung passt nicht zur Formel - erkläre einfach mal mit eigenen Worten, was wann wie passieren soll...
Mitglied: Michi1
Michi1 22.03.2018 um 20:06:36 Uhr
Goto Top
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
Mitglied: Kraemer
Kraemer 23.03.2018 um 00:30:33 Uhr
Goto Top
=and(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4)=2)  
Mitglied: Michi1
Michi1 23.03.2018 um 14:03:44 Uhr
Goto Top
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?
Mitglied: Kraemer
Kraemer 23.03.2018 um 14:42:15 Uhr
Goto Top
1. Hast du meine Formel schon einmal getestet?

2.

=IF(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4);"falsch")  
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

=2
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
INDIRECT(ADDRESS(2;COLUMN();1))="AU"  
und
MAX(Sheet1!$H$4:$Z$4)=2

und zusammen gefasst ergibt das

=and(INDIRECT(ADDRESS(2;COLUMN();1))="AU";MAX(Sheet1!$H$4:$Z$4)=2)  
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.
Mitglied: Michi1
Michi1 23.03.2018 um 21:25:57 Uhr
Goto Top
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... face-wink)

=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
Mitglied: Kraemer
Kraemer 26.03.2018 aktualisiert um 07:28:30 Uhr
Goto Top
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 funktioniert

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 hat

Hier 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.
Mitglied: Michi1
Michi1 26.03.2018 um 23:21:57 Uhr
Goto Top
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
Mitglied: Michi1
Michi1 03.04.2018 um 12:15:51 Uhr
Goto Top
Hallo zusammen,

hat jemand anderes vielleicht eine hilfreiche Idee dazu?

Danke
Michi
Mitglied: Kraemer
Lösung Kraemer 03.04.2018 um 13:33:50 Uhr
Goto Top
Zitat von @Michi1:
hat jemand anderes vielleicht eine hilfreiche Idee dazu?
jupp

Namensmanager

Lege dort einen neuen Wert an, nenne ihn sagen wir mal "Start" und dieser bezieht sich auf
=INDIREKT(ADRESSE(2;SPALTE();1))
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
Mitglied: Michi1
Michi1 03.04.2018 um 23:26:53 Uhr
Goto Top
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! face-wink

Machs gut
Michi
Mitglied: Kraemer
Kraemer 04.04.2018 um 07:40:16 Uhr
Goto Top
Zitat von @Michi1:
Vielen lieben Dank und jetzt lass ich dich auch in Ruhe! face-wink
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 face-smile