Microsoft Excel

Herbers Excel/VBA-Archiv

summe wenn Sverweis oder wenn | Herbers Excel-Forum


Betrifft: summe wenn Sverweis oder wenn von: neuling
Geschrieben am: 06.12.2009 16:59:16

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

  

Betrifft: AW: summe wenn Sverweis oder wenn von: fcs
Geschrieben am: 07.12.2009 02:51:10

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


      

    Betrifft: AW: summe wenn Sverweis oder wenn von: neuling
    Geschrieben am: 07.12.2009 08:35:17

    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


      

    Betrifft: AW: summe wenn Sverweis oder wenn von: fcs
    Geschrieben am: 07.12.2009 09:28:11

    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


      

    Betrifft: AW: summe wenn Sverweis oder wenn von: neuling
    Geschrieben am: 07.12.2009 10:07:33

    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


      

    Betrifft: AW: summe wenn Sverweis oder wenn von: neuling
    Geschrieben am: 07.12.2009 09:30:42

    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


      

    Betrifft: AW: summe wenn Sverweis oder wenn von: fcs
    Geschrieben am: 07.12.2009 10:44:24

    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


      

    Betrifft: AW: summe wenn Sverweis oder wenn von: neuling
    Geschrieben am: 07.12.2009 14:08:51

    Hallo Franz

    Funktioniert bestens.

    Ich habe mit Deiner Hilfe sehr viel gelernt. Danke.

    Werde mich jetzt noch mit den "Indirekt" befassen.

    Gruss Marcel


    Beiträge aus den Excel-Beispielen zum Thema "summe wenn Sverweis oder wenn"