Excel Wenn(UND( Funktion über ganze Spalten?
=WENN(UND(C6:C9999="123_A";D6:D9999=HEUTE();E6:E9999=7);1;0)
Ich möchte gerne das in der Spalte C6 bis C9999 nach "123_A" gesucht wird, in der Spalte D6 bis D9999 nach dem Datum von Heute und in der Spalte E6 bis E9999 nach der Zahl 7. Stehen diese in einer Reihe, so soll der Wert 1 ausgeben werden, sonst 0. Leider funktioniert diese Formel immer nur, wenn sie auch in der entsprechenden Reihe ausgeführt ist. Gibt es dafür eine Lösung?
So ich habe mal ein Bild dazu eingefügt. Links ist eine Liste die mit Daten befüllt wird. Rechts in der Liste soll dann in der Zelle eine 1 erscheinen, wo alle 3 genannten kreterien in einer Reihe C,D,E zutreffen. Ich möchte damit tracken, ab wann eine Anlage quasi belegt ist. Später wäre halt noch ergänzend, das die Tabelle auch weiß, wann die Anlage wieder Frei ist. Aber ein Schritt nach dem nächsten.
7 Antworten
Ist es das, was Du suchst ? Wenn ja, kannst Du die Formel entsprechend kopieren.
=WENN(UND(SVERWEIS($G6;$C:$E;1;FALSCH)=$G6;SVERWEIS($G6;$C:$E;2;FALSCH)=$G$5;SVERWEIS($G6;$C:$E;3;FALSCH)=7/24);1;0)
Wenn Du die Stundenzahl flexibel haben willst, nehme Bezug auf die Spalte der Zeile 5
=WENN(UND(SVERWEIS($G6;$C:$E;1;FALSCH)=$G6;SVERWEIS($G6;$C:$E;2;FALSCH)=$G$5;SVERWEIS($G6;$C:$E;3;FALSCH)=N$5/24);1;0)
Mit 2 Hilfsspalten könntest Du trotz mehrfachen Auftritt eine Einmaligkeit herstellen.
Schau mal hier
https://www.file-upload.net/download-14759409/Einsatzplan.xlsx.html
Mit der Erweiterung für einen weiteren Tag (Morgen)
https://www.file-upload.net/download-14759420/Einsatzplan.xlsx.html
O.K. dann stimmte es was ich zuerst gesagt habe. Du fragst immer wieder die Eintragungen der 3 Spalten links ab. Dann musst du diese 3 Spalten auch "absolut" setzen, wenn du die Formel in der Reihe nach rechts kopieren willst. Dann bezieht sich die Formel immer wieder auf die Spalten C,D,E.
Wenn du das nicht machst, und die Formel nach rechts kopierst, wandert auch die Suche automatisch um eine Spalte nach rechts.
Ich möchte Quasi, das wie auf dem Bild, J6 die kompletten reihen C,D,E abfragt. Aber das geht mit der WENN Formel so formuliert nicht. Wenn irgendwo von 6 bis 99999 in der Reihe C,D,E das entsprechende vorkommt. Dann soll in J6 eine 1 stehen. Ich habe auch schon versucht es mit seinem Seitenverweis, aber das wollte irgendwie auch nicht.
Also, ich habe mir tatsächlich die Mühe gemacht, die Daten in einer Excel Tabelle zu erfassen und deine Formeln auszuprobieren und da bin ich auf etwas gestoßen, das hätte mir eigentlich auch sofort auffallen können. Du kannst eine Funktion nicht als Bedingung verwenden. "HEUTE" ist aber eine Funktion und sie gibt das Systemdatum des PC's wieder. Das wirst du festellen, wenn du nur diese eine Bedingung mal versuchst. =WENN (C6;HEUTE();1;0) funktioniert nicht.
Also, das wird der erste Fehler sein. Du musst als Suchkriterium das exakte Datum (als Text formatiert) in der Zelle hinterlegen also 25.11.2021, dann Zelle formatieren und TEXT auswählen, nur dann kann die Formel exakt suchen. Um die einzelnen Wenn Funktionen in der Formel anzlegen, würde ich es mal mit dem Formelassistenten probieren.
Ich würde dir aber grundsätzlich empfehlen, ein Office Forum zu suchen, da gibts die echten Excel Cracks, für die das hier nur Spielkram ist.
Heute funktioniert, nach c6 muss bei dir ein = und kein ;. Natürlich muss dann in c6 bei dir dann auch ein Datum stehen.
mein Problem war nur das ich mit der wenn(und Funktion gerne eine Matrix durchsuche wollte. Der SVERWEIS hat mir dabei geholfen. Jedoch sucht er natürlich immer nur nach dem ersten Ergebnis und dann nicht weiter.
Soll die Formel und das Ergebnis jeweils in Spalte F stehen?
Dann schreib die Formel mal so in Zelle F6:
=WENN(UND(C6="123_A";D6=HEUTE();E6=7);1;0)
Und dann ein Doppelklick auf die rechte untere Ecke von Zelle F6, damit die Formel automatisch runterkopiert wird.
So prüft die Formel zeilenweise, ob die 3 Bedingungen stimmen und gibt in jeder Zeile das Ergebnis aus.
nein, das stimmt nicht. Es soll ja soweit ich verstanden haben, in jeder Reihe die Bedingung von C6 bis C9999 geprüft werden. Dann müssen die absolut gesetzt werden.
Nein, dein Kommentar ist falsch. Meine Antwort ist richtig!
Folgender Vorschlag, der für jede beliebige Reihenfolge alle Ergebnisse ausgibt.
Die Hilfsspalte kann man ja ggf mit weißer Schrift machen und sperren, damit da nichts kaputt geht.
Für den Plan am nächsten Tag halt dann oben das Datum anpassen, alles andere dürfte automatisch laufen.
=WENN(UND($C$6:$C$9999="123_A";$D$6:$D$9999=HEUTE();E6:E9999=7);1;0)
So müsste es gehen, wenn ich mich recht entsinne. Die Zellen, in denen der Bedingungswert gefunden werden soll, müssen mit den $ absolut gesetzt werden.
Dann kannst du die Formel in jede beliebige Reihe kopieren, sie bezieht sich immer wieder auf die C6:C9999
Mit der Spalte D muss du dann natürlich genauso verfahren
Die Ausgabe, wo ja nun auch die Formel drin steht, soll einfach nur in einer Zelle vorhanden sein. Nicht in jeder Reihe dazu.
aber du hast da oben doch geschrieben, dass du die Formel runter kopierst
Nein, das muss ein Missverständnis sein. Ich versuche herauszufinden, wie ich hier ein Bild einfüge.
Am einfachsten ist es mit dem Snippingtool einen Screenshot zu ziehen und den dann noch in die Frage rein zu kopieren. In die Kommentare kann man glauche ich keine Bilder rein kopieren
Alles klar, ich hatte erst versucht dies in die Antworten zu kopieren :D
Es geht also doch mit einem SVERWEIS, danke! Habe gerade damit experimentiert und es geht, eingeschränkt. Wenn in Spalte C der Text mehrfach vorkommt, nimmt er natürlich immer nur den ersten Treffer. Ich denke das ich mir für meine Aufgabe grundlegend etwas anderes überlegen muss. Da ich mit dieser Strategie ja auch nur einen "Start" tracken kann.