SVERWEIS?

[Fred11]

Hallo Jens,

die dynamische Ermittlung der richtigen Spalte der Matrix auf Tabellenblatt 1.14, die dem in A1 angegebenen Datum entspricht geht mit der Kombination aus INDEX und VERGLEICH.

Formel in A4 auf dem Tabellenblatt "Tabelle1":
=WENN(ZÄHLENWENN(INDEX('1.14'!$C$4:$AX$39;0;VERGLEICH($A$1;'1.14'!$C$2:$AX$2;0));"")<36;"Unterricht";"Kein Unterricht")

Und dann noch gegebenenfalls die Erweiterung der Formel mit der Funktion INDEX, um den Bezug auf das Tabellenblatt dynamisch zu gestalten, so dass die Formel in Spalte A nach unten kopiert werden kann und sich die Auswertung immer auf das richtige Tabellenblatt (d.h. den richtigen Raum) bezieht:

Formel in A4 auf dem Tabellenblatt "Tabelle1"
=WENN(ZÄHLENWENN(INDEX(INDIREKT("'"&A4&"'!$C$4:$AX$39");0;VERGLEICH($A$1;INDIREKT("'"&A4&"'!$C$2:$AX$2");0));"")<36;"Unterricht";"Kein Unterricht")

Gruß
Fred

[Jens242]

Danke für die Info, nun habe ich schon mal ein paar Ansätze mit denen ich Arbeiten kann.
Nach etwas rumprobieren, bin ich noch auf eine andere Lösung gekommen.
Was haltet ihr davon: =WENN((WVERWEIS(A1;'1.14'!C2:Z39;4;0))="";"kein Unterricht";"Unterricht")

[Statler]

Hallo Jens,

hier ist mal - nach einiger Arbeit  :20: - eine Lösung, die bei mir funktioniert. Die Formel ist "etwas" komplexer geworden, sollte aber funktionieren.
Bei Fragen gerne nochmal melden.
Wichtig! ist natürlich, dass das Format und die Schreibweise der Datumsangaben auf Deinem Übersichtsblatt exakt so aussieht wie wie in den "Unter"-Sheets.
Derzeit sucht die Formel in max. 25 Spalten (also bis max. Spalte Z) und in max 36 Zeilen (also bis Excel-Sheet-Zeile 39).
Wenn Du das ändern möchtest, mußt Du in der Formel nach 25 (für die Änderung der Spalten) und nach 39 (für die Änderung der Zeilenzahl) suchen und diese ändern.
Und du mußt nach der Zahl 36 suchen und diese auch entsprechend erhöhen oder niedriger einstellen.
Wenn Du die Zeilenzahl änderst (oder die Anzahl der Kopfzeilen (derzeit=3 =Tage, Datum, 1. Leerzeile)) dann mußt du auch in der Formel auch beide Zahlen entsprechend erhöhen oder erniedrigen.

Melde Dich doch mal ob es klappt und ob du so etwas damit anfangen kannst.
Gruß
Statler

---

Hallo Jens,

nochmal ich.

Wie soll denn Deine Formel funktionieren? Du prüfst doch, ob in der gesamten Tabelle keine Einträge vorhanden sind.
Was haltet ihr davon: =WENN((WVERWEIS(A1;'1.14'!C2:Z39;4;0))="";"kein Unterricht";"Unterricht")

Das ist doch nicht was du wolltest. Du wolltest doch einen dynamische Abfrage pro Tag haben. Also musst Du irgendwo ja nach der Zeichenkette aus A1 suchen und dann sagen, dass Du nur in dieser Spalte = an diesem Tag schauen möchtest, ob es da einen Eintrag gibt. Oder habe ich da was falsch verstanden?

[Jens242]

Warum diese Formel sucht doch unter angabe von A1 in der Entsprechende Spalte ob ein Eintrag vorhanden ist oder nicht und das ist was ich wollte.

Du hast im Tabellenblatt des Unterrichtsplans die Zeile 2 in das Format Datum umgestellt. Leider habe ich auf diese Datein kein Zugriff, ich kann sie lediglich auslesen.

[Statler]

Das ist kein Problem, dass Du da kein Zugriff hast. Dann musst Du nur das Datumsformat auf deinem "Deckblatt" angleichen. Die beiden Formate müssen gleich sein. Dann funktioniert es.

Gruß
Statler

[Fred11]

Hallo Jens,

zu deiner Frage nach der Meinung zu deiner Formel mit WVERWEIS:

Was haltet ihr davon: =WENN((WVERWEIS(A1;'1.14'!C2:Z39;4;0))="";"kein Unterricht";"Unterricht")

na probiers doch einfach aus, ob es wie gewünscht funktioniert….

Also ich würde sagen:
Im Prinzip schonmal ein bisschen besser wie dein allererster Ansatz mit SVERWEIS.

Aber wie Statler schon geschrieben hat, sie dürfte sie eigentlich nicht das gewünschte Ergebnis liefern:
In der Funktion WVERWEIS hast du als Zeilenindex eine 4 eingetragen, das heißt deine Formel schaut jetzt in der 4. Zeile der Suchmatrix C2:Z39 (also in Zeile 5), ob in der betreffenden Datumsspalte etwas drinsteht oder nicht ("") .
Das heißt du prüfst jetzt nur, ob am jeweiligen Datum dieser Raum zur ersten Unterrichtsstunde belegt ist oder nicht. Der restliche Tag ( restlichen Unterrichtsstunden) wird nicht berücksichtig…

Du hast im Tabellenblatt des Unterrichtsplans die Zeile 2 in das Format Datum umgestellt. Leider habe ich auf diese Datein kein Zugriff, ich kann sie lediglich auslesen

Du möchtest ja auf dem Tabellenblatt "1.14" in der Zeile zwei suchen bzw. ermitteln wo (d.h. in welcher Spalte) sich das auf dem Tabellenblatt "Tabelle1" in Zelle A1 eingetrgaene Datum befindet.
Dafür muss aber gewährleistet sein, dass die Datum-Einträge (d.h. der Wert) in A1 ("Tabelle1") und Zeile 2 ("1.14") auch  einheitlich sind - sonst kann nichts gefunden werden.

Also wenn in A1 steht: 11.03.2020
Dann müssen die Einträge in Zeile 2 ("1.14") auch so sein: 24.02.2020, 25.02.2020, …

Wichtig ist nicht, wie die Zellen formatiert sind, sondern das was wirklich drinstheht,  der Inhalt, nämlich das, was in der Bearbeitungszeile steht, wenn du die Zelle anklickst !


@ Statler
Dein Formel ist ja echt ganz schön komplex.
Braucht es da wirklich so ein Konstrukt mit WECHSELN und ADDRESSE ….?
(siehe Beitrag #11)

Gruß
Fred

[Statler]

Hallo Fred,

keine Ahnung, ob man die Formel jetzt nochmal vereinfachen könnte.
Ich habe ziemlich gebastelt.
Das schwierigste war den Bezug bzw die Range (zb "1.14!A3:A15") so zusammen zu basteln, dass man mit Vergleich, Adresse oder Wechseln auch zugreifen kann.
Ich hatte zuerst immer in zwei getrennten Zellen einen Zellbezug (also zb. in F12 steht der Blattname, in F13 stand der Zellbezug A3 und in F14 der Zellbezug A15) und habe dann versucht beides in der Funktion (zB. mit Vergleich) zusammen zu fassen.
Z.Bsp. VERGLEICH(A1;F12&"!"&F14&":"&F15;0)

Obwohl das reine Ergebnis aus "F12&"!"&F14&":"&F15" das richtige Ergebnis bringt (nämlich z.Bsp. "1.14!A3:A15") nimmt Excel diesen Zusammenschluß in der Funktion so nicht an. Trägt man den gleichen Eintrag manuell in die Vergleichsfunktion ein, gehts aber.
Man muß aber eine ! Zelle haben in der man beides zusammenstellt und kann dann darauf zugreifen. Darauf bin ich nicht gekommen, da ich die Funktionen langsam und Schritt für Schritt erst herleiten wollte.

Als ich das raus hatte, habe ich alle bis dahin benötigten Funktionen zu einer zusammen gefasst.

Aber es funktioniert jetzt und - so wie die ursprüngliche Aufgabenstellung ja war. Wenn man nun weiß, wo man was einstellen muß, kann man den "Arbeitsbereich" auch flexibel und relativ einfach anpassen.
Gruß
Statler

[Jens242]

Guten Morgen,

ich möchte mich bei euch für eure Unterstützung bedanken. Ich bin beeindruckt wie schnell und kompetent die Hilfe hier funktioniert.

@Statler Ja, der WVERWEIS fragt nur die Zeile 4 ab, aber wenn ich die Abfrage auf 3 weitere Zeilen festsetze, hab ich den Tag damit weitgehend abgedeckt.
Mit dem WVERWEIS bin ich jetzt jedenfalls zufrieden. Hier mal die Übersicht dargestellt.

[Jens242]

Moin,

ich habe noch mal einen Nachbrenner zu diesem Thema.
Und zwar möchte ich zusätzlich noch eine Monatsübersicht.
Das heißt nun nicht mehr die Spalte mit dem entsprechenden Datum abgefragt werden, sonder alle Spalten ob Unterriche ja oder nein.
Ich dachte mir ich könnte mit einer einfachen WennUnd Funktion die Sache lösen.
=WENN(UND('[201.1.01 UMSA.xls]MTS Parow (LIVE)'!D4="";[201.1.01 UMSA.xls]MTS Parow (LIVE)'!D8=""[201.1.01 UMSA.xls]MTS Parow (LIVE)'!D16="";"Frei";"Unterricht")
Für jede Spalte frage ich also mehre Zeilen nach Einträgen ab. Das funktioniert soweit auch. Allerdings sind in den Tabellen die Spalten unterschiedlich angeordnet. Deshalb funktiooniert eine Abfrage mit Zellbezug nicht.
Ich müsst also nur Spalten abfragen, die tatsächlich in der ersten Zeile ein Datum haben.
Wie kann ich das realisieren?

[Fred11]

Hallo Jens,

hier mal ein erster Vorschlag.

mit folgender Formel kannst du dynamisch den letzten (Datums-) Eintrag in Zeile 1 ermitteln.
=VERWEIS(2;1/(B1:AX1<>"");SPALTE(B1:AX1))

Und damit dann z.B. mit der Funktion BEREICH.VERSCHIEBEN deinen Suchbereich dynamisch festlegen.
=BEREICH.VERSCHIEBEN(F4;;;36;VERWEIS(2;1/(B1:AX1<>"");SPALTE(B1:AX1))-2)

Und diesen Bereich dann mit der Funktion ZÄHLENWENN (wie schon weiter oben vorgeschlagen) auswerten, ob Einträge vorhanden sind oder nicht
=ZÄHLENWENN(BEREICH.VERSCHIEBEN(C4;;;36;VERWEIS(2;1/(B1:AX1<>"");SPALTE(B1:AX1))-2);"*?")>0

Für detailliertere Hilfestellung bitte deine aktuelle Datei hochladen (die hat sich ja mit Sicherheit seit dem ursprünglichen Problem weiterentwickelt…)

Gruß
Fred