OpenOffice.info

Hallo,

ich Danke Dir für Deine Lösungen. Das hat mich einige schlaflose Nächte gekostet und jetzt funktioniert innerhalb weniger Stunden alles so wie ich es mir gewünscht hatte. Ein ganz dickes Lob für dieses Engagement. Das Trennen der Bereiche habe ich erstmal aufgeschoben (aber nicht aufgegeben).

Gruß Benni

=SVERWEIS(TEXT(B5;"TT.MM.JJJJ");INDIREKT(SVERWEIS(MONAT(B5);C17:D28;2;0)&".$A$7:$G$37");7;0)

=SVERWEIS(B5;April.$A$7:$G$37;7;0)

&".$A$7:$G$37"

Hallo Benni,

gute Nachricht! Deine Überstundenformel habe ich geknackt :mrgreen:

Ich hatte ja ganz übersehen, das Du auf dem Blatt "Info" die Monate schon aufgelistet hast. Das ersparte ne menge Arbeit.
Mach jetzt folgendes.
Im Blatt "Info" kopierst (oder verschiebst) Du den Bereich B17 bis B28 nach D17 bis D28. Jetzt stehen die Monatsnamen Rechts von der Zahl.
Nun gibst Du folgende Formel in D5 ein.
[code]
=SVERWEIS(TEXT(B5;"TT.MM.JJJJ");INDIREKT(SVERWEIS(MONAT(B5);C17:D28;2;0)&".$A$7:$G$37");7;0)[/code]
Normalerweise würde man ja eine SVERWEIS zu einem anderen Tabellenblatt, wenn man die genaue Adresse weiss, wie folgt schreiben.
[code]
=SVERWEIS(B5;April.$A$7:$G$37;7;0)[/code]
April ist aber nicht fest vorgegeben, also müssen wir uns den Namen von irgendwo her zusammen suchen. Wir stückeln uns jetzt die Adresse mit der erst gennanten Formel zusammen.

Funktionsweise der Formel.
Das Suchkriterium von der ersten SVERWEIS ist das Datum in B5. Dieses muss aber erstmal mit TEXT in einen Text umgewandelt werden. Ohne diese Umwandelung würde z.B "12.04.08 12:55" ausgelesen werden. Und bei einem vergleich mit dem Blatt "April" gibt es keinen Treffer, denn dort steht nur "12. Apr 2008" ohne Uhrzeit. Es spielt keine Rolle ob der Monat in diesem Falle als "04", oder als "Apr" angezeigt wird, da dies nur eine Formatsache ist. Denn intern ist es ja eh nur eine mehrstellige Zahl. Wichtiger ist aber die Uhrzeit, denn die wird nämlich intern mit dazugerechnet. Und durch die umwandelung in Text entfernen wir die Uhrzeit.
Als nächstes brauchen wir für die erste SVERWEIS noch die Matrix wo gesucht werden soll. Und das machen wir mit INDIREKT, und einer zusätzlichen SVERWEIS.
INDIREKT bietet uns die möglichkeit, wie der Name schon sagt, Indirekt auf eine Zelle zuzugreifen und uns den Inhalt daraus zu holen um damit weiter zu arbeiten.
Ja ich weiss! Ist nicht sonderlich gut beschrieben, aber INDIREKT ist auch nicht ganz so einfach zu verstehen.
Das Suchkriterium für die zusätzliche SVERWEIS ist der Monat von B5 als Zahl. Da ja jetzt rechts neben der Zahl, in dem Bereich von C17 bis D28, die Monatsnamen stehen, können wir nun mit dieser SVERWEIS den Monatsnamen uns holen.
Nachdem wir den Namen jetzt haben, hängen wir nun noch bei INDIREKT
[code]
&".$A$7:$G$37"[/code]hinten ran.
Dadurch haben wir jetzt auch die Matrix von der ersten SVERWEIS uns zusammengebastelt. Nun geben wir nur noch mit der Zahl 7 den Index der Matrix an. Und schon sind wir fertig. :lol:

Ich weiss selber das meine Beschreibung nicht gerade die Beste ist, aber hauptsache ist das die Formel funktioniert und man etwas zum nachlesen hat.

Bei mir funktioniert die Formel bestens. :mrgreen:

Gruß
balu

=DATUM(JAHR;12;ZEILE()-6)

=B7+1

Hallo Benni,

dafür das dies dein erstes größeres Projekt ist, hast Du gute Arbeit geleistet. :lol:
Sie ist auch ideenreich. Allein darauf
[code]
=DATUM(JAHR;12;ZEILE()-6)[/code]muss man erst mal kommen.

Ich würde es mir in dieser Hinsicht aber doch "einfacher" machen, und nach alter manier
[code]
=B7+1[/code]eingeben. Aber in B7 selber kannst Du deine Formel drin lassen. Du musst aber dabei bedenken, das beim eventuellen einfügen einer Zeile oberhalb von B7 der Wert bei ZEILE()-6 sich nicht automatisch anpasst.

Thema Konzept:
[quote="gaertnerbursch"]
Oder geht es darum das ich ja in die Felder in denen die Stunden und damit auch Formeln stehen später Stunden schreibe und dabei die Formel quasi überschreibe?
[/quote]
So ist es! Denn ich denke dabei an Fehler, die aus versehen entstehen können. Falls Du bei einer späteren überprüfung deiner eingegebenen Daten feststellst, das Du dich an einer Stelle vertan hast, wird es etwas problematischer das zu ändern.
Man sollte beim erstellen solcher Projekte auch seine eigene "Dummheit" berücksichtigen. 8)
Das ist aber auch leider leichter gesagt, als getan.
Und Du musst nicht unbedingt das Konzept neu überdenken, es war mehr als ein vorschlag zu sehen. Nur leider habe ich das zu so später Stunde nicht richtig rübergebracht. Sorry.


Thema "aktuellen Überstunden":
Die Formel dafür ist schon etwas problematischer, aber das lässt sich bestimmt ändern. Dazu musst Du aber doch einiges ändern. Angefangen mit den Namen für die Kalenderblätter. Wenn Du sie mit Zahlen, anstatt mit Monatsnamnen versiehst, lässt sich anschließend eine Formel erstellen die darauf zugreift. Nur bin ich selber noch nicht so weit, das ich dir da etwas passendes anbieten kann. Das muss ich erst mal selber ausknobeln.
Ich weiss aber das es es hier Spezialisten gibt, die sich damit besser auskennen :lol: . Und die können sich ja auch hier daran beteiligen. 8)
Wenn ich aber was hinbekommen habe, dann melde ich mich wieder.

Gruß
balu

=WENN(MONAT(JETZT())=1;SVERWEIS(JETZT();Januar;7);WENN(MONAT(JETZT())=2;SVERWEIS(JETZT();Februar;7);WENN(MONAT(JETZT())=3;SVERWEIS(JETZT();März;7);WENN(MONAT(JETZT())=4;SVERWEIS(JETZT();April;7);WENN(MONAT(JETZT())=5;SVERWEIS(JETZT();'Mai';7);WENN(MONAT(JETZT())=6;SVERWEIS(JETZT();'Juni';7);WENN(MONAT(JETZT())=7;SVERWEIS(JETZT();'Juli';7);WENN(MONAT(JETZT())=7;SVERWEIS(JETZT();'Juli';7);WENN(MONAT(JETZT())=8;SVERWEIS(JETZT();'August';7);WENN(MONAT(JETZT())=9;SVERWEIS(JETZT();'September';7);0))))))))))

Hallo,

erstmal vielen Dank für eine so ausführliche Antwort zu später (früher) Stunde. Wie sicher auch erkenntlich ist das mein erstes größeres Projekt mit Tabellen. Die vorgeschlagene Formel funktioniert genau so wie ich mir das vorgestellt habe. [quote="balu"]Auch wenn Du dein Konzept, bezüglich Daten-Gültigkeit UND Formel in einer gemeinsamen Zelle noch mal genauestens überlegen solltest, möchte ich dir schon mal ein paar tipps geben.
[/quote] Auch wenn ich mich jetzt doof stelle. Wie ist das gemeint? :oops: Soll eine Zelle immer nur einen Wert enthalten? Oder geht es darum das ich ja in die Felder in denen die Stunden und damit auch Formeln stehen später Stunden schreibe und dabei die Formel quasi überschreibe?
Den 4. Parameter habe ich übrigens auch probiert nur als Anfänger hat mich die daraus resultierende Fehlermeldung an den nicht Feiertagen aus der Bahn geworfen. Die Anführungszeichen waren nur ein weiterer Versuch ein besseres Ergebniss zu erhalten. An Heiligabend und Sylvester arbeite ich nicht Vollzeit sondern 50%. Die Daten zu hinterlegen wäre kein Problem.
Jetzt wirs noch einmal peinlich:[code]=WENN(MONAT(JETZT())=1;SVERWEIS(JETZT();Januar;7);WENN(MONAT(JETZT())=2;SVERWEIS(JETZT();Februar;7);WENN(MONAT(JETZT())=3;SVERWEIS(JETZT();März;7);WENN(MONAT(JETZT())=4;SVERWEIS(JETZT();April;7);WENN(MONAT(JETZT())=5;SVERWEIS(JETZT();'Mai';7);WENN(MONAT(JETZT())=6;SVERWEIS(JETZT();'Juni';7);WENN(MONAT(JETZT())=7;SVERWEIS(JETZT();'Juli';7);WENN(MONAT(JETZT())=7;SVERWEIS(JETZT();'Juli';7);WENN(MONAT(JETZT())=8;SVERWEIS(JETZT();'August';7);WENN(MONAT(JETZT())=9;SVERWEIS(JETZT();'September';7);0))))))))))[/code]Das müsste bis Dezember gehen und soll auf der Info Seite die aktuellen Überstunden an zeigen. Lässt sich das mit dem gleichen Konzept kürzen?

Vielen, vielen Dank nocheinmal.

Gruß Benni

=WENN(C7="Urlaub";D7;WENN(C7="Krank";D7;WENN(C7="KUG";D7;WENN(C7="Schule";D7;WENN(C7="Feiertag: Allerheiligen";D7)))))

=WENN(ZÄHLENWENN(Liste;C7);D7;"")

=SVERWEIS(B7;Feiertabelle;6)='1'

=SVERWEIS(B7;Feiertabelle;6)

=SVERWEIS(B7;Feiertabelle;6;0)

=WENN(ZÄHLENWENN($Feiertage.$A$2:$A$72;B7);WENN(SVERWEIS(B7;$Feiertage.$A$2:$F$36;2;0)=1;D7;0);"")

=WENN(ZÄHLENWENN(FeierDATUM;B7);WENN(SVERWEIS(B7;Feiertabelle;2;0)=1;D7;0);"")

Hallo Benni,

ein Herzliches Willkommen im Forum. :lol: Schön das Du jetzt auch mitmachst.

Auch wenn Du dein Konzept, bezüglich Daten-Gültigkeit [color=#0000FF]UND [/color]Formel in einer gemeinsamen Zelle noch mal genauestens überlegen solltest, möchte ich dir schon mal ein paar tipps geben.

Übrigens, wie unschwer zu erkennen ist, bin ich derjenige der das mit den Feiertagen ursprünglich "verbrochen" hat. :mrgreen: Es freut mich aber sehr zu sehen, das meine Idee dich inspiriert hat. :lol:


[code]
=WENN(C7="Urlaub";D7;WENN(C7="Krank";D7;WENN(C7="KUG";D7;WENN(C7="Schule";D7;WENN(C7="Feiertag: Allerheiligen";D7)))))[/code]
Diese Formel lässt sich stark kürzen (verbessern), aber erst mal ohne den Feiertag.

Du hast doch schon einen Namen (Liste) für den Bereich mit "Urlaub, Schule..." vergeben. Also liegt es doch nahe, den jetzt auch hier zu benutzen. Dazu brauchst Du noch aber eine passende Funktion. Nehmen wir dafür ZÄHLENWENN. Damit kann man auch nach Text suchen, und das nutzen wir gleich schamlos aus.
Wir haben jetzt also eine Funktion, und einen Bereich. Ferner brauchen wir noch eine zusätzliche WENN abfrage. Und das ganze setzen wir nun wie folgt zusammen.
[code]=WENN(ZÄHLENWENN(Liste;C7);D7;"")[/code]
Das war's schon.


[quote="gaertnerbursch"]
Die meisten Feiertage werden korrekt gekennzeichnet aber um Weihnachten erhalte ich 6 zusammenhängende Tage.[/quote]
Das stimmt! Aber erst nachdem ich aus
[code]=SVERWEIS(B7;Feiertabelle;6)='1'[/code]
das hier gemacht habe.
[code]=SVERWEIS(B7;Feiertabelle;6)[/code]
Und dann wurde mir auch schlagartig klar, warum das so ist!
Es fehlt bei SVERWEIS der [color=#0000FF]berühmt-berüchtigte und sehr wichtige 4.Parameter[/color]!!!!!!!!!
Denn du hast da nur 3 Parameter angegeben, und das reicht nicht!!! Richtig ist.
[code]=SVERWEIS(B7;Feiertabelle;6;0)[/code]
Auch wenn in der Hilfe immer wieder gesagt wird, das dies ein "Optionaler" Parameter ist, so kann er einem ganz schöne Kopfschmerzen bereiten wenn er nicht gesetzt wird. Und was ist schon großartig dabei mal eben eine 1, oder 0 zu schreiben? Mehr bedarf es nicht.

1 = Sortierte Liste
0 = Unsortierte Liste

[quote="OpenOffice.org Hilfe"]
In unsortierten Listen werden nur genaue Übereinstimmungen mit dem Suchwert gefunden.[/quote]
Und die Feiertagsliste ist [color=#FF0000]NICHT[/color]sortiert.
Das einzige was jetzt stört, ist, das SVERWEIS jetzt in deiner Tabelle ein #NV (Fehler: Wert nicht verfügbar) ausgibt. Aber das soll jetzt erstmal nicht großartig weiter stören.


[quote="gaertnerbursch"]
Meine Idee war in der WENN-Abfrage SVERWEIS(B7;Feiertabelle;2)=1 zu verwenden. Ich dachte dann erhalte ich an jedem gesetzlichen Feiertag eine 1 und kann daraus meine Abfrage zusammenbauen WENN, DANN.[/quote]
Um es Kurz zu machen, und deine Idee aufzugreifen, schlage ich dir folgende Formel vor.
[code]=WENN(ZÄHLENWENN($Feiertage.$A$2:$A$72;B7);WENN(SVERWEIS(B7;$Feiertage.$A$2:$F$36;2;0)=1;D7;0);"")[/code]
Oder mit Namen, weil wesentlich kürzer.
[code]=WENN(ZÄHLENWENN(FeierDATUM;B7);WENN(SVERWEIS(B7;Feiertabelle;2;0)=1;D7;0);"")[/code]

Ja, ja! Ich weiss! Es gibt auch noch eine dritte möglichkeit OHNE SVERWEIS, und ohne ZÄHLENWENN. Aber die kann noch später besprochen werden, wenn er sein Konzept (wie ich schon eingangs erwähnte) überarbeitet hat.
Btw: Arbeitest Du z.B. an Heiligabend Vollzeit? Wenn nicht, dann sollte in der Stundenauswahlliste eine passendere Zeit zur Auswahl angeboten werden.

[img]http://www.mysmilie.de/smilies/muede/img/029.gif[/img]
balu

Hallo,

ich lese hier schon lange mit und konnte die meisten meiner Problem dank dieses großartigen Forums lösen. Nun komme ich aber gar nicht mehr weiter.
Ich habe eine Tabelle erstellt mit der ich Arbeitsstunden erfassen möchte. Sie besteht aus 14 Seiten, davon 12 Seiten Kalender, 1 Info Seite und 1 Feiertagsseite. In Spalte A und B habe ich das Datum (=DATUM(JAHR;12;ZEILE()-6)) in verschiedenen Formaten (ich weiß geht auch anders). In Spalte C steht die Baustelle auf der ich an diesem Tag war. Hier werden automatisch die Feiertage nach einer Vorlage hier aus dem Forum eingefügt. Spalte D enthält die Sollstunden. E gearbeitete Stunden F die Mehrstunden und schließlich in G die Überstunden. Soweit bin ich auch gekommen. Das meiste Funktioniert so wie ich es will. Zwei Probleme habe ich aber:
In der Spalte E gearbeitete Stunden sollen automatisch die Sollstunden aus D eingetragen werden wenn in C Urlaub, Krank etc. steht. Genauso sollen die Stunden eingetragen werden wenn ein gesetzlicher Feiertag ist. In der Feiertagstabelle habe ich alle gesetzlichen Feiertage mit 1 gekennzeichnet. Meine Idee war in der WENN-Abfrage SVERWEIS(B7;Feiertabelle;2)=1 zu verwenden. Ich dachte dann erhalte ich an jedem gesetzlichen Feiertag eine 1 und kann daraus meine Abfrage zusammenbauen WENN, DANN. Leider stimmt da irgendwas nicht. Fällt besonders im Dezember auf. Die meisten Feiertage werden korrekt gekennzeichnet aber um Weihnachten erhalte ich 6 zusammenhängende Tage. Ich komme nicht weiter und hoffe irgendjemand hat eine Idee. Die Formel habe ich zur Veranschaulichung mal in Spalte I im Dezember eingetragen.
Das 2. Problem betrifft die Info-Seite. Ich möchte gerne die aktuellen Überstunden angezeigt bekommen. Die von mir angefangene Formel funktioniert ist aber sehr lang und aufwändig. Gibts da eine kürzere Variante??
Vielen Dank für alle Tipps und Eure Mühe.

Gruß Benni
[attachment=0]TEst1.ods[/attachment]