Office: (Office 2016) Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren

Helfe beim Thema Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Moin moin zusammen, ich habe mal wieder eine etwas komplexere Thematik und komme leider nicht weiter. Mein Ziel ist es, eine als Kalender aufgebaute... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Der_Bo, 20. Februar 2020.

  1. Der_Bo Erfahrener User

    Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren


    Moin moin zusammen,

    ich habe mal wieder eine etwas komplexere Thematik und komme leider nicht weiter.

    Mein Ziel ist es, eine als Kalender aufgebaute Tabelle in eine Übersichtlichere Darstellung umzuwandeln. Leider bin ich des VBA nicht mächtig, weshalb ich mich auf die "klassischen" Formeln konzentrieren muss.

    Meine aktuelle Datei sieht aus wie im Anhang. Den Anhang Fehlzeitenuebersicht_2019.xlsm betrachten

    Um es nicht zu komplex zu machen, möchte ich nur die Eintragungen für "Urlaub" sprich die "1" auswerten.

    Meine Formel soll von der Logik her wie folgt aussehen (s. Zelle AO9 im Reiter "Mustermann, Max): =WENN(L9=1;DATUM(JAHR(HEUTE());MONAT(A9);TAG(L6));"")

    Nun soll das ganze aber nicht nur für einzelne Zellen, sondern den jew. gesamten Monat funktionieren. Idealerweise also:

    =WENN(B9:AF9=1;DATUM(JAHR(HEUTE());MONAT(A9);TAG(B6:AF6));""). Leider funktioniert das mit dieser Formel jedoch nicht.

    Damit nicht genug, sollen auch noch zusammenhängende Zeiträume als Datumsbereich ausgegeben werden, also z.B.: 11.03. - 15.03.2020

    Ich stehe momentan einfach auf dem Schlauch, wie ich das am besten hinbekomme. Kann mir hier jemand helfen?

    edit: ach ja, zu allem Überfluss muss das ganze dann noch auf den Reiter "Übersicht" übertragen werden.
     
    Zuletzt bearbeitet: 20. Februar 2020
  2. Exl121150 Erfahrener User
    Hallo,

    ich habe deine Excel-Datei modifiziert/ergänzt und zwar im Arbeitsblatt "Mustermann, Max":
    1. Im Zellbereich AN7:AN18 habe ich 12 DropDowns eingefügt, sodass dort die Fehlzeitenarten (1/K/F/…/A) ausgewählt werden können.
    2. Im Zellbereich AO7:BK18 habe ich 23x12 Matrixformeln eingefügt. Diese verwenden die DropDown-Einstellung aus Punkt 1) und listen die Tagesdaten auf, in denen diese Fehlzeitart im Bereich B7:AF18 eingetragen ist.
    3. Im Zellbereich BL7:BL18 habe ich 12 benutzerdefinierte VBA-Funktionen eingefügt. Z.B. ist in Zelle BL12 die Funktion
      "=DatumsListe(AN12;A12:AF12)" enthalten. Der erste Parameter AN12 greift wieder auf die Fehlzeiten-DropDown aus Punkt 1) zu, während im 2. Parameter A12:AF12 die 32 Monats-Zellen der Jahresübersicht enthalten sind. Die Funktion liefert für die Fehlzeitart "1" den Ausdruck "11.-14., 17.-19., 21.6.2019". Bei Auswahl der Fehlzeitart "k" in AN12 erscheint in BL12 der Ausdruck "26.-27.6.2019".
    4. In Zelle A25 habe ich noch eine weitere benutzerdefinierte VBA-Funktion eingefügt "=Ostern(A24)". Sie errechnet zur Jahreseingabe in A24 das Osterdatum (für den Zeitraum 1900-9999). In VBA würde sie sogar für den Zeitraum 100-9999 funktionieren.
    Das stimmt nicht. Sie funktioniert. Du musst sie nur als Matrixfunktion eingeben, d.h. die Formel ist in die Formeleditierzeile von Excel einzugeben und die Formeleingabe ist dann nicht bloß mit der EINGABE-Taste abzuschließen, sondern mit der Tastenkombination Strg+Umschalt+EINGABE. Daraufhin erscheint die Formel in geschweiften Klammern eingeschlossen:
    {=WENN(B9:AF9=1;DATUM(JAHR(HEUTE());MONAT(A9); TAG(B6:AF6));"")}
     
    Exl121150, 21. Februar 2020
    #2
  3. Der_Bo Erfahrener User
    Hi Anton,

    vielen, vielen Dank. Das hat mir extrem weitergeholfen.

    Viele Grüße
     
  4. Der_Bo Erfahrener User

    Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren

    Ich habe noch eine ergänzende Frage zu meinem obigen Thema:

    Ich habe nun noch eine Auswahl der Feiertage pro Bundesland in meine Tabelle eingebaut und möchte, dass sich die angezeigte Feiertagsliste entsprechend dem ausgewählten Bundesland aktualisiert. Das ist besonders für die Berechnung der Nettoarbeitstage je nach Bundesland wichtig. Ergo habe ich -am Beispiel des Feiertags Hlg. 3 Könige in Bayern und Ba-Wü- folgende Formel verwendet:

    {=WENN(UND(ODER(wertAuswahlBundesland=$F$3:$U$3);ODER(F5:U5="X"));DATUM(JAHR(HEUTE());1;6);"")}

    - "wertAuswahlBundesland" verweist auf mein Dropdownfeld
    - der Bereich F3:U3 umfasst die Bundesländer in der Übersichtstabelle
    - der Bereich F5:U5 ist die Zeile in welcher ein X steht, falls das Bundesland an diesem Tag einen Feiertag hat.

    Leider komme ich da nicht mehr weiter. Entweder Excel zeigt mir tatsächlich das Formelergebnis (06.01.2020) an oder es macht das Feld leer. Durch die Änderung der Auswahl im Dropdown ändert sich aber nichts.

    Habt ihr eine Idee?

    Vielen Dank vorab und Grüße

    der_Bo
     
    Zuletzt bearbeitet: 7. April 2020
    Der_Bo, 7. April 2020
    #4
  5. Exl121150 Erfahrener User
    Hallo,

    leider kann man mit den UND(..) bzw. ODER(…)-Funktionen keine Matrixfunktionen bauen.

    Aus diesem Grund habe ich dir in der beiliegenden Excel-Datei ein Arbeitsblatt "Feiertage" eingefügt, in dem sich
    • 2 ListTabellen "TabFeiertage" und "TabBdLdFeiertage" befinden.
    • Über die Zelle B4 (die das Jahr enthält) werden die Kalenderdaten der möglichen Feiertage der Spalte "TabFeiertage[Datum]" ermittelt.
    • In Zelle B8 befindet sich ein Bundesländer-DropDown, das seine Daten aus der Kopfzeile "TabBdLdFeiertage[#Kopfzeilen]" bezieht. Damit kann in Zelle C8 die Tabellenspalte des ausgewählten Bundeslandes ermittelt werden.
    • Weiters ist in Zelle B12 ein Feiertage-DropDown, das seine Daten aus der Spalte "TabFeiertage[Feiertag]" erhält, womit in Zelle C12 die Tabellenzeile des ausgewählten Feiertages bezogen werden kann.
    • Damit kann in Zelle B13 der Feiertagsstatus aus der ListTabelle "TabBdLdFeiertage" ermittelt werden:
      "x" der Tag ist gesetzlicher Feiertag, "?" der Tag ist nur in einem Teilgebiet gesetzlicher Feiertag, "0" der Tag ist kein (gesetzlicher) Feiertag in diesem Bundesland.
    • In Zelle B14 wird das Datum des Feiertags angezeigt (falls in B13 ein "x" oder "?" enthalten ist) oder es wird "---" angezeigt (falls in B13 ein "0" enthalten ist).

    Ob die "x" bzw. "?" in den einzelnen Bundesländern wirklich exakt so zutreffen, das müsstest du im Einzelfall noch überprüfen. Ich habe die Daten aus dem Internet bezogen, da ich als Ösi die deutschen Details nicht genau kenne. Die Berechnung der Feiertagsdatumsangaben dürfte zutreffen.

    Im Namensmanager habe ich die beiden Namen "ListeFeiertage" und "ListeBundesländer" eingefügt.
     
    Exl121150, 7. April 2020
    #5
  6. Der_Bo Erfahrener User
    Hi Anton,

    vielen Dank für die Lösung. Mal wieder hochgradig. Nur leider ein kleines Problem hierbei: Mittlerweile soll die Datei nicht mehr mit VBA angelegt sein. Daher habe ich mir eine Office-Vorlage geschnappt und diese ergänzt bzw. überarbeitet. Hintergrund ist auch, dass die Liste bundesweit eingesetzt werden soll und daher unterschiedliche Feiertage bei der Berechnung der verbrauchten Urlaubstage angesetzt werden müssen. Da dies verformelt ist, benötige ich also eine entsprechende Vorbereitungsformel, die beim Anlegen des Mitarbeiters und der damit verbundenen Auswahl des Bundeslandes die Feiertage, die nicht zutreffen ausblendet (daher meine obige Formel).

    Zum besseren Verständnis habe ich jetzt mal meine Datei angehängt. Den Anhang Kopie von Fehlzeitenplaner neu_2020.xlsx betrachten


    Vielleicht kannst du dir das mal anschauen?

    Für Rückfragen einfach melden :-)
    VG
    der_Bo
     
    Der_Bo, 8. April 2020
    #6
  7. Exl121150 Erfahrener User
    Hallo,

    ich habe deine Excel-Datei modifiziert und unten angefügt - wie du siehst, diesmal ohne VBA-Einsatz.

    1. Dafür habe ich im Arbeitsblatt "gesetzl. Feiertage" die Bundesländerfeiertagsauflistung im Bereich F3:U23 umgewandelt in eine ListTabelle namens "BundesländerFeiertage". Die ListTabelle "Betriebsferien" in B3:C23 habe ich unverändert belassen.
    2. Im Namensmanager habe ich den Namen "ListeBundesländer" mit der Formel =BundesländerFeiertage[#Kopfzeilen] eingefügt.
      Mit diesem Namen habe ich auch im Arbeitsblatt "Kalenderansicht" das Bundesländer-DropDown in Zelle T2 (wertAuswahlBundesland) formelmäßig angepasst.
    3. Im Namensmanager habe ich ferner den Namen "NrAuswahlBundesland" mit der Formel =VERGLEICH(wertAuswahlBundesland;ListeBundesländer;0) eingefügt.
      Dieser Name ermöglicht es, mittels des vorhin erwähnten Bundesländer-DropDown (=wertAuswahlBundesland) die zutreffende Spalte in der Tabelle "BundesländerFeiertage" zu ermitteln.
    4. Zu guter Letzt habe ich dann den entscheidenden Namen "BetriebsferienAuswahlBundesland" in den Namensmanager mit der Formel:
      =(Betriebsferien[Feiertage]*(INDEX(BundesländerFeiertage;0;NrAuswahlBundesland)="X""))
      eingefügt, die sich wie eine Matrixformel verhält. Wie sie sich verhält, habe ich nur zu Demonstrationszwecken im schräg durchstrichenen Bereich B30:B49 dargestellt. Durch diese Formel werden die für ein Bundesland (=wertAuswahlBundesland) nicht zutreffenden Feiertagsdaten als 0-Werte dargestellt.
      Den Effekt habe ich ferner im Bereich F27:G30 mit den beiden Funktionen NETTOARBEITSTAGE(…) bzw. NETTOARBEITSTAGE.INTL(…) gezeigt, wo beidesmal der Name "BetriebsferienAuswahlBundesland" zum Einsatz kommt.
    Meine Ergänzungen im Arbeitsblatt "gesetzl. Feiertage" im Bereich B27:L50 können alle gelöscht werden.
     
    Exl121150, 8. April 2020
    #7
    1 Person gefällt das.
  8. Der_Bo Erfahrener User

    Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren

    Hello again,

    nachdem ich die Datei nun in Verwendung habe (was ich ohne eure Hilfe niemals vollständig hinbekommen hätte), habe ich nun nochmal eine Luxus-Frage:

    Ich habe im Reiter "Abwesenheitsliste" die automatische Berechnung der Fehltage eingebaut:
    =WENN(E4="";NETTOARBEITSTAGE([@Anfangsdatum](C4);[@Enddatum](D4);lstFreieTage);(WERT(F4)*24-WERT(E4)*24)/8)


    Da die Datei über mehrere Jahre hinweg genutzt werden soll, ist diese Abwesenheitsliste natürlich sehr lang. Leider ist es nun so, dass meine NETTOARBEITSTAGE-Formel den Bereich "IstFreieTage" immer nur vom aktuell ausgewählten Kalenderjahr berechnen kann (da sich die Formeln in genanntem Bereich natürlich immer nur auf das aktuelle/ausgewählte Jahr beziehen). Soweit so gut und so korrekt. Allerdings verfälscht es die Gesamtzahl der Netto-Tage in der Abwesenheitsliste, wenn ich mir die Fehltage eines Mitarbeiters über mehrere Jahre hinweg ansehen möchte.

    Besteht die Möglichkeit der Formel beizubringen, dass sich der Bereich "IstFreieTage" immer auf das Jahr der Zelle C4 bzw. D4 beziehen kann? Ich habe es bereits mit Verketten und Indirekt versucht, jedoch komme ich auf keinen grünen Zweig.

    Meine Idee war, dass die Formel grundsätzlich so aussehen könnte:
    =WENN(E4="";NETTOARBEITSTAGE([@Anfangsdatum](C4);[@Enddatum](D4);KALENDERJAHR=JAHR(D4)lstFreieTage);(WERT(F4)*24-WERT(E4)*24)/8)

    Macht das Sinn?

    Danke schonmal vorab und viele Grüße,

    der_Bo
     
  9. steve1da Super-Moderator
    Hola,

    eine Beispieldatei wäre schön.

    Gruß,
    steve1da
     
    steve1da, 2. Dezember 2020
    #9
  10. Der_Bo Erfahrener User
    Hi steve1da,

    aber sicher...
     
  11. steve1da Super-Moderator
    Hola,

    da IstFreieTage ja ein definierter Bereich ist wo nur die Feiertage eines bestimmten Jahres stehen sehe ich aktuell keine Möglichkeit das variabel zu gestalten. Excel müsste ja für jedes Jahr intern irgendwo die Feiertage ermitteln und die sind ja immer anders (außer den fixen natürlich).

    Gruß,
    steve1da
     
    steve1da, 2. Dezember 2020
    #11
  12. Der_Bo Erfahrener User
    Hi steve1da,

    genau das ist der Knackpunkt. Ich hatte gehofft, dass ich irgendwie in der Formel NETTOARBEITSTAGE den dritten Syntaxwert mit einer verschachtelten Formel so hinkriege, dass Excel sich die freien Tage für das Kalenderjahr aus Zelle D4 zieht. Wäre halt eine Luxuslösung... :-)
     
  13. steve1da Super-Moderator

    Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren

    Hola,

    das einzige was mir grad einfällt ist, die freien Tage für jedes Jahr nebeneinander zu erstellen und diesen Bereich dann für die Formel heranzieht.

    Gruß,
    steve1da
     
    steve1da, 2. Dezember 2020
    #13
  14. Exl121150 Erfahrener User
    Hallo Christian,

    du hast im Arbeitsblatt "gesetzl. Feiertage" die strukt.Tabelle "Betriebsferien" mit der Spalte/Feld "Feiertage", wobei diese Spalte identisch ist mit dem benannten Zellbereich "IstFreieTage". Darüber hinaus hast du unterhalb davon eine 2. Tabelle "Betriebsferien7" mit den Feiertagen des Vorjahres.
    Ich weiß natürlich nicht, für was du diese separate Tabelle fürs Vorjahr benötigst - aber mein Gedanke ist: Wenn du die 2. Tabelle zu einem Bestandteil der ersten Tabelle machst, so erstreckt sich der benannte Zellbereich "IstFreieTage" automatisch über die Feiertage zweier Jahre. Ich habe dir zusätzlich noch die Feiertage des Vorvorjahres unten dran gehängt, sodass jetzt in diesen Demodaten 3 Jahre mit ihren Feiertagen im Zellbereich "IstFeiertage" enthalten sind. Das ließe sich so ganz leicht auf noch weitere Vorjahre erweitern. -- Das alles unter der Voraussetzung, dass du die separate Vorjahrestabelle "Betriebsferien7" nicht benötigst.
     
    Exl121150, 2. Dezember 2020
    #14
    2 Person(en) gefällt das.
  15. Der_Bo Erfahrener User
    Hi Anton,

    erste Sahne. Auf die Idee bin ich noch gar nicht gekommen.

    Das löst mein Problem. Vielen Dank.

    Gruß
    der_Bo
     
Thema:

Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren

Die Seite wird geladen...
  1. Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren - Similar Threads - Kalenderblatt Bereich auslesen

  2. Bereiche eines Arbeitsblattes "nachhaltig" verknüpfen

    in Microsoft Excel Hilfe
    Bereiche eines Arbeitsblattes "nachhaltig" verknüpfen: Hallo liebes Forum, ich habe bisher immer mit Verknüpfungen gearbeit, um einen bestimmten Teil eines Arbeitsblattes auf einem anderen abzubilden. Inzwischen frisst es mir am meisten Zeit,...
  3. Bereichs-Namen auf anderen Tabellen-Blättern

    in Microsoft Excel Hilfe
    Bereichs-Namen auf anderen Tabellen-Blättern: Hallo, unten stehender Code funktioniert einwandfrei, solange die Bereichs-Namen auf einem Tabellen-Blatt sind. Wenn ich das Makro auf einem anderen Blatt zB. (Ausgabe) ausführe, kommt eine...
  4. Bereich per in anderes Tabellenblatt spiegeln

    in Microsoft Excel Hilfe
    Bereich per in anderes Tabellenblatt spiegeln: Hallo Leute, ich möchte einen Bereich eines TB(Tabellenblatt) in einem anderen TB der gleichen Mappe per "=" einfügen. Also wenn ich ein TB Quelle habe und ich möchte die Zelle B14 im TB Ziel...
  5. Bereich aus anderer Tabelle einfügen je nach Dropdown Wert

    in Microsoft Excel Hilfe
    Bereich aus anderer Tabelle einfügen je nach Dropdown Wert: Hallo zusammen, ich würde gerne ein einer Tabelle, in der sich ein Dropdown befindet einen Bereich aus einer anderen Tabelle einfügen, sagen wir A3 bis E8 wenn in dem Dropdown ein gewisser Wert...
  6. Saisonale Mietpreise einem Zeitraum zuordnen

    in Microsoft Excel Hilfe
    Saisonale Mietpreise einem Zeitraum zuordnen: Hallo zusammen. Ich möchte Mietpreise einer Ferienwohnung berechnen. Wenn ich den Zeitraum eingebe, soll der Mietpreis pro Tag aus einer zweiten Tabelle geholt werden und mit der Anzahl der...
  7. Urlaubswünsche auflisten

    in Microsoft Excel Hilfe
    Urlaubswünsche auflisten: Hallo Mitstreiter, erstmal einen schönen Nachmittag. Ich habe ein kleines Problem und ich hoffe ihr könnt mir helfen. Ich habe einen Jahreskalender gebastelt bei dem man in den Spalten D,...
  8. Feiertage automatisch in Kalenderblatt einfügen

    in Microsoft Excel Hilfe
    Feiertage automatisch in Kalenderblatt einfügen: Liebe Leute! Die beigefügte Datei ist eine Arbeitszeiterfassung und vergleichbar mit einem Kalenderblatt. Jedoch ist es mir nicht gelungen, die Feiertage (auf dem zweiten Blatt) so einzubauen,...
Schlagworte: