OpenOffice.info

Hallo hullen,

[quote]
Ich komme mir vor wie damals im Mathe-Grundkurs ...
[/quote]
Und so wird aus dem Lehrer wieder ein Schüler. ;-)


[quote]
Darf ich fragen, was man beruflich machen muss, um so etwas zu können? Lernt man das in irgendeiner Ausbildung? Oder ist es das Resultat von einem jahrzehntelangem Sich-Reinarbeiten?
[/quote]
Hat nix mit Beruf zu tun.
Learning by doing, das aber über Jahre hinweg. Und natürlich hat das Forum hier mir immer wieder aktiv sehr viel beigebracht, oder ich habe mich mit diversen Themen hier befasst.

Und dabei ist das hier noch alles "Kleine Fische". Es gibt formelkonstrukte, die selbst ich nicht verstehe, oder schon wieder nicht verstehe da zu selten damit befasst.


[quote]
Ich bin dir sehr dankbar, auch für deine umfangreichen Erklärungen!
[/quote]
Freut mich doch sehr zu hören :-)

Wünsche dir noch viel Vernügen mit dem neu gelernten und der Datei.



Gruß
balu

Mein liebster Balu,

ich könnte dich küssen!!!

Ich komme mir vor wie damals im Mathe-Grundkurs, wo der Lehrer was an der Tafel zaubert und ich hochkonzentriert so halbwegs verstehe, was er tut. Zu Hause über den Aufgaben habe ich dann aber nicht mehr den leisesten Schimmer, wie der Lehrer da von A nach B gekommen ist...
Ich bin dir sehr dankbar, auch für deine umfangreichen Erklärungen!

Darf ich fragen, was man beruflich machen muss, um so etwas zu können? Lernt man das in irgendeiner Ausbildung? Oder ist es das Resultat von einem jahrzehntelangem Sich-Reinarbeiten?

Schwer beeindruckt
Hullen

=WENN(ISTFEHLER(SUCHEN("Entfall";B3));SVERWEIS(B2;$E2:$F9;2;0);"schulfrei!")
 

=WENN(SUMMENPRODUKT(AF2>=AD$5:AD$10;AF2<=AE$5:AE$10)=1;"Ferien";WENN(ZÄHLENWENN(AX$2:AX$30;AF2);SVERWEIS(AF2;$AX$2:$AY$30;2;0);WENN(SUMMENPRODUKT(AF2>=AD$33:AD$46;AF2<=AE$33:AE$46)=1;INDEX($AC$33:$AD$46;VERGLEICH($AF2;$AD$33:$AD$46;1);1);WENN(WOCHENTAG(AF2;2)>5;"WE";"j"))))
 

WENN(SUMMENPRODUKT(AF2>=AD$33:AD$46;AF2<=AE$33:AE$46)=1

=WENN(AF2<=$Z$5;"1";WENN(AF2<=$Z$6;"2";WENN(AF2<=$Z$7;"3";WENN(AF2<=$Z$8;"4";"-" ))))
 

=WENN(UND(WERT($AH2)=1;ISTZAHL($AK2));AF2;"-")
 

=WENN(AF2<=$Z$5;1;WENN(AF2<=$Z$6;2;WENN(AF2<=$Z$7;3;WENN(AF2<=$Z$8;4;"-" ))))
 

=WENN(UND($AH2=1;ISTZAHL($AK2));AF2;"-")
 

KKLEINSTE($AD$11:$AD$46;ZEILE()-1)
 

=MIN($C$11:$C$46)
 

=WENN(MAX($C$11:$C$46)=MAX(I$2:I2);"";KGRÖSSTE($C$11:$C$46;ZÄHLENWENN($C$11:$C$46;">"&I2)))
 

=WENN(MAX($C$11:$C$46)=MAX(I$2:I2);"";..
 

KGRÖSSTE($C$11:$C$46;ZÄHLENWENN($C$11:$C$46;">"&I2)))
 

">"&I2

=WENN(A1>I2;....)
 

KGRÖSSTE($C$11:$C$46;14)
 

KGRÖSSTE($C$11:$C$46;9)
 

="AL"&VERGLEICH(1;$AH$1:$AH$374;0)&":"&"AL"&VERGLEICH(2;$AH$1:$AH$374;0)-1

=MIN(INDIREKT(AM1))
 

=WENN(MAX(INDIREKT(AM$1))=MAX(AM$2:AM2);"Gruß balu";KGRÖSSTE(INDIREKT(AM$1);ZÄHLENWENN(INDIREKT(AM$1);">"&AM2)))
 

="AL"&VERGLEICH(2;$AH$1:$AH$374;0)&":"&"AL"&VERGLEICH(3;$AH$1:$AH$374;0)-1

Hallo hullen,

[quote]
Ich habe alles übernommen und angepasst, außer bei der Sortierung der Hilfsspalten habe ich nicht mit dem Formelteil KGRÖSSTE($C$11:$C$46;ZÄHLENWENN($C$11:$C$46;">"&I2) gearbeitet, sondern mit KKLEINSTE($AD$11:$AD$46;ZEILE()-1) - mit anderen Spaltenangaben, weil die Tabelle größer ist. Bringt dasselbe Ergebnis, ist kürzer und - ich verstehe die Formel ;-) Bei deiner Formel ist mit nicht klar, wieso sie den GRÖSSTEN Wert nimmt und trotzdem aufsteigend sortiert.
[/quote]
Is mich jetscht och nich kloar. Abba wenn deine funzt, ist doch jut.

Spaß beiseite, Ernst wird jetzt eingeschult :lol:.
Ich werde gleich noch auf dein und mein Formelsystem genauer eingehen.


[quote]
Auf einer anderen Tabellenseite sollen Zellen mit "schulfrei" markiert werden, wenn in anderen Zellen das Wort "Entfall" auftaucht. Ansonsten erscheint die Zahl der Unterrichtsstunden, die ich mit sverweis ranhole. Warum klappt die Formel nicht?
[/quote]
Nun ja, wenn Du mit SUCHEN oder FINDEN arbeitest, dann musst Du erstmal überprüfen ob z.B. SUCHEN einen Fehler zurück gibt und diesen dementsprechend abarbeiten. Hier MUSS also die Logik umgekehrt werden. Und in deinem Falle bedeutet das, wenn Fehler dann führe die SVERWEIS aus, ansonsten gib die Meldung *Schulfrei* aus.
[code=php]
=WENN(ISTFEHLER(SUCHEN("Entfall";B3));SVERWEIS(B2;$E2:$F9;2;0);"schulfrei!")
 [/code]
das ist aber ein Problem womit sich schon sehr viele herumgeschlagen haben.



[quote]
jetzt kommt das zweite Problem.
[/quote]
Du meinst wohl PROBLEME plural, und nicht singular!

Fangen wir mit AG2 an.
[code=php]
=WENN(SUMMENPRODUKT(AF2>=AD$5:AD$10;AF2<=AE$5:AE$10)=1;"Ferien";WENN(ZÄHLENWENN(AX$2:AX$30;AF2);SVERWEIS(AF2;$AX$2:$AY$30;2;0);WENN(SUMMENPRODUKT(AF2>=AD$33:AD$46;AF2<=AE$33:AE$46)=1;INDEX($AC$33:$AD$46;VERGLEICH($AF2;$AD$33:$AD$46;1);1);WENN(WOCHENTAG(AF2;2)>5;"WE";"j"))))
 [/code]
Und da interessiert uns nur dieser Teil davon.
[code=php]
WENN(SUMMENPRODUKT(AF2>=AD$33:AD$46;AF2<=AE$33:AE$46)=1
[/code]
In dem Zellbereich AD33 bis AE36 hast Du ein Kuddelmuddel-kauderwelsch an Datumen drin. Einmal mit einem VON und BIS Termin, und dann wieder Einzeltermin, und dann wieder VON BIS.
Hast Du das schon wieder vergessen, das beim Einsatz von SUMMENPRODUKT -{so wie zu sehen}- in BEIDEN Spalten ein Datum drin stehen MUSS! Da darf es KEINEN Einzeltermin mit nur einem Datum geben. Also nur 21.11.16 ist in diesem Falle [u]verboten[/u]. Stattdessen in AD35 21.11.16 UND in AE35 21.11.16. Natürlich gilt das für alle Einzeltermine.


Warum in AH2 das hier?
[code=php]
=WENN(AF2<=$Z$5;"1";WENN(AF2<=$Z$6;"2";WENN(AF2<=$Z$7;"3";WENN(AF2<=$Z$8;"4";"-" ))))
 [/code]
Warum setzt Du die Zahlen in doppelte Anführungszeichen und machst aus ihnen Text? Nur damit Du dann in AM2 mit diesem hier
[code=php]
=WENN(UND(WERT($AH2)=1;ISTZAHL($AK2));AF2;"-")
 [/code]
aus Text wieder einen Wert machen musst. Der Sinn ist mir einfach Rätselhaft. Und ich finde ihn blödsinnig.
Mach lieber in AH2 das.
[code=php]
=WENN(AF2<=$Z$5;1;WENN(AF2<=$Z$6;2;WENN(AF2<=$Z$7;3;WENN(AF2<=$Z$8;4;"-" ))))
 [/code]
Und in AM2 das
[code=php]
=WENN(UND($AH2=1;ISTZAHL($AK2));AF2;"-")
 [/code]


So, und nun zu deinem Formelsystem und meinem.
Du setzt diese Formel ein,
[code=php]
KKLEINSTE($AD$11:$AD$46;ZEILE()-1)
 [/code]
und wunderst dich das Du Probleme z.B. in AP hast. Anscheind verstehst Du dein System selber nicht wirklich, oder ich verstehe es jetzt so kurz vorm zu Bette gehen nicht.
Und aus diesem Grunde muss ich mein Formelsystem, welches ich ja nicht erklärt hatte, mal grob beschreiben.

Also mein System besteht aus 2 in Worten ZWEI Formeln. Und zur Erklärung nimm dir meine letzte Beispieldatei *funzt net_und funktioniert doch_v2.ods*.

Die erste ist recht simpel.
[code=php]
=MIN($C$11:$C$46)
 [/code]
Damit wird das kleinste Datum aus dem genannten Bereich herausgefischt.

Auf zur 2. Formel, die Du nicht so richtig verstehst.
[code=php]
=WENN(MAX($C$11:$C$46)=MAX(I$2:I2);"";KGRÖSSTE($C$11:$C$46;ZÄHLENWENN($C$11:$C$46;">"&I2)))
 [/code]

[code=php]
=WENN(MAX($C$11:$C$46)=MAX(I$2:I2);"";..
 [/code]
Dieser Teil ist verständlich. Aber zur Sicherheit, dennoch ein paar Worte dazu.
Wenn das größte [MAX($C$11...)] Datum gleich das größte [MAX(I$2...)] ist, dann mach nix.
Es werden also 2 unabhängige "Spalten" auf ihrem größten Wert hin verglichen.

Weiter zu dem eigentlich nicht verständlichen Teil.
[code=php]
KGRÖSSTE($C$11:$C$46;ZÄHLENWENN($C$11:$C$46;">"&I2)))
 [/code]
Der Datenbereich $C$11:$C$46 ist klar.
- Einmal *Daten* für KGRÖSSTE
- und einmal *Bereich* für ZÄHLENWENN

KGRÖSSTE braucht 2 Parameter damit es funktioniert.
1. Daten
2. Rang_K

Punkt 1 haben wir.
Punkt 2 wird durch ZÄHLENWENN geholt.
Und ZÄHLENWENN macht jetzt nix anderes, als zu zählen wie oft ein Datum im *Bereich* größer ist als das Datum in I2. Und deshalb lautet die *Kriterien* für ZÄHLENWENN.
[code=php]
">"&I2
[/code]
Das ist die Schreibweise für "größer als" in ZÄHLENWENN.
Bei einer WENN-Abfrage würde das einfach so aussehen.
[code=php]
=WENN(A1>I2;....)
 [/code]
Also in meiner besagten Beispieldatei gibt jetzt ZÄHLENWENN in I3 die 14 zurück. Und dadurch würde jetzt der KGRÖSSTE-Teil ausgeschrieben wie folgt aussehen.
[code=php]
KGRÖSSTE($C$11:$C$46;14)
 [/code]
In dem Datenbereich $C$11:$C$46 befinden sich insgesamt 15 Datume. Das "kleinste" Datum wurde ja schon in I2 durch MIN herausgefischt. Es bleiben jetzt also noch 14 Datume übrig. Das größte Datum ist dort der 15.06.17. Und das 14 gößte Datum ist der 01.11.16.

Anderes Beispiel.
In I8 gibt ZÄHLENWENN die 9 zurück, un dementsprechend würde der KGRÖSSTE-Teil ausgeschrieben wie folgt aussehen.
[code=php]
KGRÖSSTE($C$11:$C$46;9)
 [/code]
Und das 9 größte Datum wäre dort dann der 27.02.2017.

Mein Formelsystem arbeitet wohl mit 2 Formeln, aber dafür kann man es immer so ansetzen das das "kleinste" Datum von den großen immer oben steht. Doch das hilft dir jetzt auch nicht sofort weiter, da Du wohl die Unterichtstage pro Quartal LÜCKENLOS aufsteigend sortiert haben möchtest. Stimmt doch, oder!?

Aber auch dafür habe ich einen Lösungsvorschlag.
Was Du brauchst ist einen Dynamischen Zellbereich der sich nach der Quartalseinteilung richtet. Also mit anderen Worten, das 1. Quartal geht von AL2 bis AL49 und das 2. gfeht von AL50 bis AL112 usw.

Aber wie bekommst Du einen solchen Datenbereich automatisch erstellt?
Nun, das geht eigentlich relativ einfach. Dafür werden lediglich VERGLEICH und INDIREKT benötigt.

Für das 1. Quartal nehmen wir in AM1 folgende Formel.
[code=php]
="AL"&VERGLEICH(1;$AH$1:$AH$374;0)&":"&"AL"&VERGLEICH(2;$AH$1:$AH$374;0)-1
[/code]
Die spuckt uns nun folgenden Text aus:

[b]AL2:AL49[/b]

Und jetzt kommt mein Formelsystem wieder zum Zuge. Doch es sieht jetzt ein wenig anders aus, da nun INDIREKT eingesetzt wird.
1. Formel in AM2
[code=php]
=MIN(INDIREKT(AM1))
 [/code]

2. Formel in AM3
[code=php]
=WENN(MAX(INDIREKT(AM$1))=MAX(AM$2:AM2);"Gruß balu";KGRÖSSTE(INDIREKT(AM$1);ZÄHLENWENN(INDIREKT(AM$1);">"&AM2)))
 [/code]
:lol:
Diese Formel runterziehen bis .....

Willst Du wissen wie wir das z.B. mit dem 2. Quartal machen?


Logisch willstDu das. Und das geht sehr einfach.


In AO1
[code=php]
="AL"&VERGLEICH(2;$AH$1:$AH$374;0)&":"&"AL"&VERGLEICH(3;$AH$1:$AH$374;0)-1
[/code]
vergleiche jetzt mal diese mit der Formel in AM1.
Der Unterschied müsste dir eigentlich schon auffalen.

Und was glaubst Du was in AO2 und in AO3 rein kommt?

Ganz einfach!
Formel aus AM2 und AM3 einfacvh da reinkopieren, und fädddisch.

Und die anderen beiden Quartale gehen genau so schnell und einfach über die Bühne. Bis auf das 4. Quartal. Aber den Unterschied siehst Du ja in der angehängten Datei.


Und nun geh ich bubu machen.


Guts nächtle
balu

Hallo Balu,
jetzt kommt das zweite Problem.

Die Tabelle ist quasi der Jahreskalender, mit deren Angaben sich viele andere Tabellen füllen.
Was plötzlich nicht mehr klappt:

Spalte AM nennt alle Unterrichtstermin im 1. Quartal des Schuljahrens, Spalte AN sortiert sie dann, damit ich keine Leerzeilen habe.
In Spalte AO/AP, AQ/AR und AS/AT sollte das gleiche passieren - hat es bislang auch immer, jetzt klappt es aber nicht mehr. Keine Ahnung, warum, ich habe dort an den Formeln nichts geändert. Das einzige, was geändert wurde, ist die Formel in Spalte AG. Findest du einen FEhler=

Liebe Grüße und extrem vielen Dank für deine Ratschläge!!!
Hullen

Hallo Balu,
die Formel klappt wunderbar - vielen Dank!
Ich habe alles übernommen und angepasst, außer bei der Sortierung der Hilfsspalten habe ich nicht mit dem Formelteil KGRÖSSTE($C$11:$C$46;ZÄHLENWENN($C$11:$C$46;">"&I2) gearbeitet, sondern mit KKLEINSTE($AD$11:$AD$46;ZEILE()-1) - mit anderen Spaltenangaben, weil die Tabelle größer ist. Bringt dasselbe Ergebnis, ist kürzer und - ich verstehe die Formel ;-) Bei deiner Formel ist mit nicht klar, wieso sie den GRÖSSTEN Wert nimmt und trotzdem aufsteigend sortiert.

Aber nun ein neues Problem:
Auf einer anderen Tabellenseite sollen Zellen mit "schulfrei" markiert werden, wenn in anderen Zellen das Wort "Entfall" auftaucht. Ansonsten erscheint die Zahl der Unterrichtsstunden, die ich mit sverweis ranhole. Warum klappt die Formel nicht?

Ich habe das Problem angehängt und wäre dir dankbar, wenn du draufschauen könntest.

Und später poste ich noch Problem Nummer 2, wo plötzlich die Sortierung mit KKleinste in der einen Spalte funktioniert und in der anderen nicht. Dazu muss ich aber noch eine Beispieltabelle erstellen, ich poste sie später.

Liebe Grüße
hullen

INDEX($B$1:$C$10;VERGLEICH($E2;$C$1:$C$10;1);1)

=WENN(SUMMENPRODUKT(E2>=C$5:C$10;E2<=D$5:D$10)=1;INDEX($B$1:$C$10;VERGLEICH($E2;$C$1:$C$10;1);1);WENN(ZÄHLENWENN($I$2:$I$16;E2);SVERWEIS(E2;$I$2:$J$16;2;0);WENN(WOCHENTAG(E2;2)>5;"WE";"Schule")))

Hallo hullen,

so, nach dem ich jetzt doch etwas geschlafen habe :lol:, kommt jetzt meine 2. Version.

Als erstes habe ich einen Hilfsbereich angelegt, wo die Daten aus den [i]*gesetzliche Feiertage*[/i] und [i]*Brückentage*[/i] so wie die [i]*sonstige Tage ohne Unterricht*[/i] aufsteigend sortiert aufgelistet werden.
Dieser Bereich geht von I2 bis J46, also reichlich Platz für viele weitere Untrerrichtsfreie Tage oder sonstiges.


Die Spalte H = [i]*Hilfsspalte Ereignisse*[/i] habe ich raus geschmissen. Du hattest wohl mit SVERWEIS gearbeitet und warst deshalb auf diese Spalte angewiesen. Das dumme ist nur, wie dir ja bekannt, dass SVERWEIS nur nach Rechts hin arbeiten kann. Aber es gibt ja da den Trick mit INDEX und VERGLEICH, womit man dann auch nach Links hin arbeiten kann.
Und das sieht dann so aus.
[code=php]
INDEX($B$1:$C$10;VERGLEICH($E2;$C$1:$C$10;1);1)
[/code]
Diese Kombination ist sehr flexibel, weil man mit ihr nicht nur nach Links, oder Rechts suchen kann, sondern auch nach Oben oder Unten. Das aber nur so am Rande.


Die zuletzt gepostete Datei von mir, heute morgen vorm zu Bett gehen, baute ja darauf auf, das erst die Ferien abgearbeitet werden, und dann der Bereich mit den "Feiertagen". Im Prinzip macht das meine neue Version nicht wesentlich anders. Nur mit den Unterschieden, das einerseits die Ferien jetzt wie eben angesprochen abgearbeitet werden, und anderseits die restlichen "Freien Tage" aus dem Hilfsbereich abgearbeitet werden.
Und das sieht jetzt so aus.
[code=php]
=WENN(SUMMENPRODUKT(E2>=C$5:C$10;E2<=D$5:D$10)=1;INDEX($B$1:$C$10;VERGLEICH($E2;$C$1:$C$10;1);1);WENN(ZÄHLENWENN($I$2:$I$16;E2);SVERWEIS(E2;$I$2:$J$16;2;0);WENN(WOCHENTAG(E2;2)>5;"WE";"Schule")))
[/code]
Sie ist nur ein klein wenig länger als die "alte" Formel. Das ist aber nur dem zusätzlichem Datenbereich zu verdanken, da dieser ja eine Sortierfunktion beinhaltet, welches das Arbeiten mit der Formel doch erleichtert.


Da ja jetzt die sonstigen "Freien Tage" anders gehandhabt werden, kann man sie auch wieder mit nur einem Datum eintragen.


[color=#FF0000][b][u]Ach ja, noch etwas sehr wichtiges![/u][/b][/color]

Ferien, auch wenn es nur ein einziger Tag ist, gehört in die Rubrik [i]*übl. Ferien*[/i], und nicht zu [i]*gesetzliche Feiertage* [/i]wie es bei deiner Beispieldatei mit "Pfingstferien 06.06.17-06.06.17" war. Denn so etwas kann ganz schnell das neue System durcheinander bringen, was zu Problemen und Fehlern führt. Und genau deshalb besteht der Bereich [i]*übl. Ferien*[/i] jetzt aus 6, anstatt wie bei dir aus 5 Zeilen.
Im Jahre 2018 gibt es nicht nur einen Pfingstferien-Tag, sondern 4. Und deshalb ist es wichtig das die [i]*übl. Ferien*[/i] um eine Zeile erweitert wurden.

Lange Rede kurzer Sinn.
Schau dir die neue Version in Ruhe an, und bei Fragen meldest Du dich einfach wieder. :wink:



Gruß
balu

=WENN(SUMMENPRODUKT(E3>=C$5:C$9;E3<=D$5:D$9)=1;SVERWEIS(E3;$C$5:$H$9;6;1);WENN(ZÄHLENWENN($C$10:$C$25;E3);SVERWEIS(E3;$C$10:$H$25;6;0);WENN(WOCHENTAG(E3;2)>5;"WE";"Schule")))
 

Hallo Hullen,

im Prinzip stimmt deine Formel so weit. Doch jetzt kommt das große
ABER!

1.
[quote="balu"]
Jetzt musst Du allerdings immer dafür garantieren das die Datumsspalten aufsteigend sortiert sind, denn sonst funktioniet der nächste Schritt nicht.
[/quote]
Der Bereich C5:C25 ist [b][u]NICHT[/b][/u] aufsteigend sortiert!

Da Du aber eine sehr differenzierte Unterteilung der Freien Tage hast, muss das momentan vorhandene System umgebaut werden. Und zwar nach folgendem Schema:
a)
Schaue erst mal nur nach ob Ferien sind.

b)
Wenn keine Ferien, dann schau nach ob es sich um einen gesetzlichen Feiertag handelt.

c)
Wenn weder a noch b zutrifft, dann überprüfe ob Wochenende ist.

d)
Wenn a bis c nicht zutrifft, dann bleibt ja nur noch Schule übrig.

Und die geänderte Formel in F2 sieht dann so aus.
[code=php]
=WENN(SUMMENPRODUKT(E3>=C$5:C$9;E3<=D$5:D$9)=1;SVERWEIS(E3;$C$5:$H$9;6;1);WENN(ZÄHLENWENN($C$10:$C$25;E3);SVERWEIS(E3;$C$10:$H$25;6;0);WENN(WOCHENTAG(E3;2)>5;"WE";"Schule")))
 [/code]
Beachte bitte die geänderten Zellbereiche.
Ferien = C$5:C$9 und D$5:D$9, sowie $C$5:$H$9
Ges. Feiertage = C$10:$C$25, so wie $C$10:$H$25

Jetzt kannst Du dir ja vorstellen was aus der Formel wird, wenn Du nun auch noch die *Brückentage* und *sonstige Tage ohne Unterricht* nach dem gleichen gezeigten Schema zusätzlich einbauen willst.
Es wird eine wahre MONSTERFORMEL!
Es sei denn, Du arbeitest mit "Namen für Bereiche", denn dann könnte man sie etwas übersichtlicher und leserlicher machen.

Oder aber Du setzt ein ganz anderes System ein, doch dazu habe ich jetzt auf die schnelle keine Lust mehr, da das Bett fürterlich laut nach mir schreit.



Gute Nacht
balu

Hallo Balu, hallo paljass,

vielen Dank für eure Schützenhilfe!
Ich habe jetzt Tage damit verbracht, die Formel zu übernehmen und anzupassen - erfolglos. Entweder mache ich einen dummen Übernahme-Fehler oder ... oder ich weiß nicht.

Ich hänge mal meine Datei an. Die entscheidene Spalte ist Spalte F. Dort sollte er anzeigen, was an dem Tag passiert (Schule, WE (Wochenende), oder eben der konkrete Ferienanlass). Dort werden nun aber nur die Wochenenden markiert, alles Ferien missachtet mein Rechner.

Wo liegt der Fehler?

PS: Ich fühle mich geehrt, dass du, Balu, dich an mich erinnerst! Über die Hilfsspalte von dir habe ich in der Tat gestutzt, aber ich hätte nicht gewusst, wie ich sie vermeiden kann. Das Summenprodukt ist mir bis heute nicht wirklich verständlich.

PPS: Balu, selbst mein Formelvorschlag war nicht von mir, sondern höchstwahrscheinlich auch von dir, vor ca. 5-7 Jahren. Die bislang von mir verwendetet Formel funktioniert insoweit, als dass sie zuverlässig alle Schu[attachment=0]funzt net.ods[/attachment]ltage und alle nicht-Schultage markiert, allerdings fehlt mir der sverweis auf den Grund des schulfreien Tages.

Hallo paljass,

weißt Du eigentlich wie schlecht es mir gestern ging?
Bes............ mit Knoten im .....ja wie heißt das gleich noch mal .... ja richtig, Knoten im Hirn, oder waren es doch mehrere?
Und da kommst Du jetzt daher, und sagst "zusammenfassen".

Nö, Du! Is nicht böse gemeint. :wink: :-)

Hast aber recht, das man die Formeln zusammenfassen kann. Und ich denke mir das hullen auch selber darauf gekommen wäre, da er ja nicht ganz unwissend ist, er ist mir hier auch nicht ganz unbekannt.



Gruß
balu





[size=70]p.s
[OffTopic]
So nebenbei habe ich gestern festgestellt, das ich einen bestimmten Kalender wieder etwas umprogrammieren muss. So ein shit abba och.
[/OffTopic][/size]

=WENN(SUMMENPRODUKT(E1>=A$1:A$12;E1<=B$1:B$12)=1;SVERWEIS($E1;$A$1:$C$12;3;1);WENN(WOCHENTAG(E1;2)=7;"S O N N T A G";"Schule"))

Hi,
@balu
wenn du deine Formeln zusammenfasst zu [code]=WENN(SUMMENPRODUKT(E1>=A$1:A$12;E1<=B$1:B$12)=1;SVERWEIS($E1;$A$1:$C$12;3;1);WENN(WOCHENTAG(E1;2)=7;"S O N N T A G";"Schule"))[/code]dann kommst du auch ohne Hilfsspalte aus.

Gruß
paljass

SUMMENPRODUKT(E1>=A$1:B$3;E1<=A$1:B$3)

=SUMMENPRODUKT(E1>=A$1:A$12;E1<=B$1:B$12)
 

=WENN(G1=1;SVERWEIS($E1;$A$1:$C$12;3;1);WENN(WOCHENTAG(E1;2)=7;"S O N N T A G";"Schule"))
 

Hallo hullen,

[code]
SUMMENPRODUKT(E1>=A$1:B$3;E1<=A$1:B$3)
[/code]
Damit warst Du schon fast auf dem richtigen Weg, um dir ausgeben zu lassen ob es sich um einen Ferientag handelt oder nicht.

Jedoch sieht das richtig rum so aus.
[code=php]
=SUMMENPRODUKT(E1>=A$1:A$12;E1<=B$1:B$12)
 [/code]
Wie gesagt, damit wird angezeigt ob Ferien oder nicht. Und das wird in einer seperaten Spalte ausgeführt.

Jetzt musst Du allerdings immer dafür garantieren das die Datumsspalten aufsteigend sortiert sind, denn sonst funktioniet der nächste Schritt nicht. Und wenn es sich um einen einzel Termin handelt, so muss dieser in der A und B Spalte stehen, denn ansonsten wird er nicht angezeigt.

Ja, auch ich wende die SVERWEIS an, aber mit ein wenig etwas drum herum.
[code=php]
=WENN(G1=1;SVERWEIS($E1;$A$1:$C$12;3;1);WENN(WOCHENTAG(E1;2)=7;"S O N N T A G";"Schule"))
 [/code]
Erst wird überprüft ob in der seperaten Spalte (hier die G) eine 1 drin steht. Wenn ja, dann wird die SVERWEIS ausgeführt. Wenn nein, dann wird nachgeschaut ob das Datum ein Sonntag ist, ansonsten wird Schule ausgegeben.

Tabelle1 ist deine Vorgabe, die ich mit meinem System ausgebaut habe.
Tabelle2 ist das was Du dir wünscht, aber nur mit meinem System.



Gruß
balu

Hallo hullen,

dann erstell die Arbeitstabelle für SVERWEIS mit Formeln, die auf die Ferien Daten zugreifen.

[attachment=0]summenprodukt-vs-SVERWEIS_r1.ods[/attachment]

Hallo Clag,
vielen Dank für dein Mitdenken!

[quote]dies Verhalten gibst du doch vor!
mit =WENN(ODER(SUMMENPRODUKT(E1>=A$1:B$3;E1<=A$1:B$3);ZÄHLENWENN(A$1:B$3;E1));"schulfrei";"Schule")
das fordert eine exakte Übereinstimmung um wahr zu werden und du hast in E aber nur Anfang- und End-Datum angegeben.[/quote]

Das verstehe ich nicht. Es ist doch eine ODER-Formel: Entweder liegt der Termin zwischen Anfang und Ende eines Ferienzeitraums ODER er liegt exakt auf einem eintägigen Termin, dann soll "schulfrei" ausgespuckt werden.

[quote]Kannst du deine Datumsangaben nich etwas anders strukturieren, [/quote]

Leider nein. In der Liste werden nur die schulfreien Ereignisse eingetragen, dazwischen kann man keine "Schule" angeben.

Gibt es eine andere Möglichkeit?

lg hullen

Hallo hullen,
[quote="hullen"]a) sie funktioniert nicht richtig, die Formel reagiert nur auf den ersten und letzten Tag von Ferienzeiträumen. [/quote]
dies Verhalten gibst du doch vor!
mit =WENN(ODER(SUMMENPRODUKT(E1>=A$1:B$3;E1<=A$1:B$3);[color=#BF0000]ZÄHLENWENN(A$1:B$3;E1[/color]));"schulfrei";"Schule")
das fordert eine exakte Übereinstimmung um wahr zu werden und du hast in E aber nur Anfang- und End-Datum angegeben.
[quote="hullen"]b) ich habe keine Ahnung, wie ich statt des Platzhalters "schulfrei" dort den sverweis auf das konkrete Ereignis (Allerheiligen, Osterferien) unterbringen kann.[/quote]
Kannst du deine Datumsangaben nich etwas anders strukturieren, dann ist dein Anliegen ganz einfach und einzig mit SVERWEIS() zu lösen.
Guckst du hier in dem Beispiel

[attachment=0]summenprodukt-vs-SVERWEIS.ods[/attachment]
Hilft dir das?

Hallo zusammen,
ich habe zwei Probleme mit einer summenprodukt-Formel.
In der Tabelle stehen die Zeiträume von Ferien und Feiertagen. In einer Auflistung aller Tage eines Monats sollen dann hinter die Termine geschrieben werden, ob an dem Tag Schule ist - ansonsten soll dort "Allerheiligen" oder "Osterferien" stehen.

a) sie funktioniert nicht richtig, die Formel reagiert nur auf den ersten und letzten Tag von Ferienzeiträumen.
b) ich habe keine Ahnung, wie ich statt des Platzhalters "schulfrei" dort den sverweis auf das konkrete Ereignis (Allerheiligen, Osterferien) unterbringen kann.

Hier eine Beispieltabelle:

Dankbar für jede Hilfe
hullen
OO 3.4.1