Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1120to1124
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

summe wenn Sverweis oder wenn

summe wenn Sverweis oder wenn
neuling
Guten Tag
Ich versuche in einer Zeiterfassung die ferien zu berechen mit folgenden Optionen.
1) Ferienguthaben in Abhängigkeit der Arbeitsprozente
+ Wenn Alter grösser 50 dann 1 Woche mehr und wenn Alter grösser 60 dann 2 Wochen mehr
+ Dienstaltersgeschenk 1 oder 2 Wochen mehr in Abhängigkeit der Arbeitsporzente
+ Ferien-Lohn Option 1 oder 2 Wochen Mehr in Abhängigkeit der Arbeitsprozente.
Und das ganze wenn möglich ohne VBA
Schon Mal im Voraus ein grosses Dankeschön für Eure Unterstützung
In der Beilage die Datei
https://www.herber.de/bbs/user/66394.xls
Wenn ein Mitarbeiter

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: summe wenn Sverweis oder wenn
07.12.2009 02:51:10
fcs
Hallo Neuling,
deine Formel in Zelle XX muss dann etwa wie folgt aussehen.
=SUMME(WENN(B69>50;SVERWEIS(B6;B47:E55;WENN(B69>60;4;3);FALSCH);0);
WENN(B12>0;SVERWEIS(B6;B58:D66;WENN(B12=1;2;3);FALSCH);0);
WENN(B14>0;SVERWEIS(B6;B58:D66;WENN(B14=1;2;3);FALSCH);0);SVERWEIS(B6;B47:E55;2;FALSCH))

Allerdings kann ich die Logik für die Anzahl der Basis-Ferientage nicht ganz nachvollziehen. Eigentlich haben Teilzeitbeschäftigte in Deutschland Anspruch auf die gleiche Anzahl an Urlaubstagen wie vergleichbare Vollzeitbeschäftigte oder ist das in deiner Gegend arbeitsvertraglich anders geregelt.
Ansonsten hätte ich noch ein paar Vesserungsvorschläge, die dir langfristig das Leben mit der Datei erleichtern sollten.
  • Bereichsnamen verwenden
    lege für die Zellen/Zellbereiche im Blatt "Steuerdaten", die in den Monatsblättern verwendet werden Bereichsnamen fest und verwende diese in den Formeln auf den Monastblättern. Die Formeln werden so leichter lesbar.

  • zusätzliche Eingabezelle im Blatt "Steuerdaten" für das Jahr.
    Damit kann dann in den Monatsblättern das jeweilige Datum des Monats-Ersten berechnet werden und das umständliche Übernehmen des letzten Datums aus dem Vormonat entfällt.

  • Vereinheitlichung der Zeilenzahl in den Monatsblättern (6 Eingabewochen je Blatt).
    Die für den jeweiligen Monat nicht benötigten Zeilen werden ausgeblendet.
    Per Formel kann man dann den 1. des Monats in der Zeile mit dem passenden Wochentag starten lassen. Am Monatsende werden dann die Zeilen, die nicht für den Monat benötigt werden mit Ergebnis "" angezeigt. Die Formeln für die Gleitzeitstunden müssen dann auch so angepasst werden, dass sie nur einen Wert ungleich 0 anzeigen, wenn in Spalte B ein Datum steht.

  • Funktion TEILERGEBNIS statt SUMME
    für die Berechnung der Wochen- und Monatssummen in den Monatsblättern. Das vereinfacht die Formel für die Monatssumme, da nicht mehr einzelne Zellen in der SUMME-Funktion angegeben werden müssen sondern mit TEILERGEBNIS nur noch der gesamte Monatsbereich.

  • keine verbundenen Zellen für "Bemerkung" in den Monatsblättern
    in deinen Blättern müssen verbundene Zellen hier wirklich nicht sein. Da kann doch die Breite der Spalte entsprechend einstellt werden.

  • Fixieren der Ansicht in den Monatsblättern
    in Zelle C18. Das erleichtert die Dateneingabe

  • Der Vorteil der verschiedenen Anpassungen:
    Die Blätter für die 12 Monate sind bis auf die Übernahme der Vorjahresdaten im Januar und die Berechnung des Resturlaubs in Feb. bis Dez. identisch.
    Für ein neues Kalenderjahr muss dieses "nur" in den Steuerdaten eingegeben werden und in den 12 Monatsblättern sind die auszublendenden Zeilen und ggf. die fixen Feiertage anzupassen. In deiner jetzigen Fassung muss du in großem Umfang die Zeilen und Formeln in den Monatsblättern anpassen, um die Zeilen in das korrekte Wochentagsschema zu bringen.
    Hier mal deine Datei entsprechend zurechtgemacht.
    https://www.herber.de/bbs/user/66399.xls
    Gruß
    Franz
    Anzeige
    AW: summe wenn Sverweis oder wenn
    07.12.2009 08:35:17
    neuling
    Guten Tag Franz
    Zuerst mal ein ganz grosses Dankeschön!
    Zum Verständnis der Ferientage der Teilzeitbeschäftigten. Auch hier in der Schweiz haben diese die gleichen Anzah lFerientage zu gute. Dies ist nur so, weil das Personalbüro diese Darstellung gewünscht hat. Denn sie rechnen Die Ferien Pro Rata in Stunden ab.
    Und jetzt werde ich mal Versuchen das ganze zu nachvollziehen was Du erarbeitet hast. Aber ich bin Dir sehr Dankbar.
    Gruss
    Marcel
    AW: summe wenn Sverweis oder wenn
    07.12.2009 09:28:11
    fcs
    Hallo Marcel,
    in den Formeln in B10 und D10 steckt bei dir aber 2 mal die reduzierte Arbeitszeit drin.
    in B10 ermiitelst du über den SVERWEIS eine equivalent niedrigere Anzahl an Ferientagen und C10 multiplizierst du diese Anzahl Tage mit den niedrigeren Sollstunden pro Tag indem du nochmals mit dem Arbeitspensum multiplizierst.
    In meiner Beispiel-Datei steckt irgendwie auch noch ein VBA-Modul drin. Das kannst du einfach löschen - hat nichts mit der funktionalität der Datei zu tun.
    Gruß
    Franz
    Anzeige
    AW: summe wenn Sverweis oder wenn
    07.12.2009 10:07:33
    neuling
    Hallo Franz
    Darf ich Dich nochmals belästigen.
    Wenn ich jetzt bei den Bereich "Ferien.DA_Option" um Zwei Spalten "4" Wochen und "8" Wochen erweitere, wie muss ich dann die Formel in "MA.Ferien.Jahr.Tage (B10) anpassen?
    Denn die Wenn-Formel hat ja nur zwei Krtiereien.
    Gruss Marcel
    AW: summe wenn Sverweis oder wenn
    07.12.2009 09:30:42
    neuling
    Hallo Franz
    Darf ich Dich noch ein mal belästigen.
    Wenn ich jetzt noch bei den "Ferien.DA_Option" noch zewi weiterSpalten hinzufüge "4" Wochen und "8" Wochen.
    Wie müsste ich dann die Formel in MA.Ferien.Jahr.Tage anpassen?
    Gruss Marcel
    AW: summe wenn Sverweis oder wenn
    07.12.2009 10:44:24
    fcs
    Hallo Marcel,
    vergebe für die Zellen C57:F57 mit den Werten 1, 2, 4 und 8 einen Bereichsnamen, z.B. FL_Optionen
    Jetzt kannst du mit der Funktion VERGLEICH arbeiten, um im SVERWEIS die richtige Spalte zu ermitteln.
    neue Formel in B10:
    =SUMME(WENN(MA.Alter>50;SVERWEIS(MA.Pensum;Ferien.Alter;
    WENN(MA.Alter>60;4;3);FALSCH);0);WENN(MA.DA.Geschenk>0;
    SVERWEIS(MA.Pensum;Ferien.DA_Option;VERGLEICH(MA.DA.Geschenk;
    FL_Optionen;0)+1;FALSCH);0);
    WENN(MA.Ferien.Lohn_Option>0;SVERWEIS(MA.Pensum;Ferien.DA_Option;
    VERGLEICH(MA.Ferien.Lohn_Option;FL_Optionen;0)+1;FALSCH);0);
    SVERWEIS(MA.Pensum;Ferien.Tage;2;FALSCH))
    

    Gruß
    Franz
    Anzeige
    AW: summe wenn Sverweis oder wenn
    07.12.2009 14:08:51
    neuling
    Hallo Franz
    Funktioniert bestens.
    Ich habe mit Deiner Hilfe sehr viel gelernt. Danke.
    Werde mich jetzt noch mit den "Indirekt" befassen.
    Gruss Marcel

    310 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige