Warum ein teures Anwesenheitsmanagement-Tool für Ihr Startup kaufen, wenn Sie die Anwesenheit des Teams in Excel verfolgen können? Jawohl! Sie können ganz einfach einen Anwesenheitstracker in Excel erstellen. In diesem Artikel erfahren Sie, wie das geht.
Schritt 1: Erstellen Sie 12 Blätter für jeden Monat in einer Arbeitsmappe
Wenn Sie die Anwesenheit für ein Jahr verfolgen möchten, müssen Sie jedes Monatsblatt in Excel erstellen.
Schritt 2: Fügen Sie Spalten für jedes Datum in jedem Monatsblatt hinzu.
Erstellen Sie nun eine Tabelle, die die Namen Ihrer Teamkollegen enthält, eine Spalte für Summen und 30 (oder Anzahl Tage pro Monat) Spalten mit Datum und Wochentag als Spaltenüberschriften.
Um den Namen des Wochentags zu erhalten, können Sie den Kalender nachschlagen oder die Formel verwenden, um ihn in die restlichen Zellen zu kopieren.
| =TEXT(Datum,"TT") |
Sie können hier darüber lesen.
Formatieren Sie die Wochenenden und Feiertage dunkel und füllen Sie sie mit festen Werten wie Wochenende/Feiertage, wie in der Abbildung unten gezeigt.
Machen Sie dasselbe für jedes Blatt.
Schritt 3. Korrigieren Sie die möglichen Eingaben mithilfe der Datenvalidierung für jede offene Zelle.
Jetzt kann jeder seine Anwesenheit in das Blatt eintragen, aber er kann zufälligen Text eingeben. Einige schreiben möglicherweise P für Präsens oder Präsens oder per usw. Dateneinheitlichkeit ist in jedem Anwesenheitsverwaltungssystem obligatorisch.
Damit Benutzer nur P oder A für anwesend bzw. abwesend schreiben können, können wir die Datenvalidierung verwenden.
Wählen Sie eine beliebige Zelle aus, gehen Sie zu Daten im Menüband und klicken Sie auf Datenvalidierung. Wählen Sie eine Liste aus den Optionen aus und schreiben Sie A,P in das Textfeld.
Klicken Sie auf OK.
Kopieren Sie diese Validierung für den gesamten offenen Datenbereich (offener Bereich bedeutet eine Zelle, in die der Benutzer Werte einfügen kann).
Schritt 3: Sperren Sie alle Zellen außer dort, wo Anwesenheit eingegeben werden muss.
Wählen Sie ein Datum und eine Datumsspalte aus. Wählen Sie beispielsweise 1. Januar. Klicken Sie jetzt auf den ausgewählten Bereich und gehen Sie zur Zellenformatierung. Geh zum Schutz. Deaktivieren Sie das gesperrte Kontrollkästchen. Klicken Sie auf OK. Kopieren Sie nun diesen Bereich in alle offenen Datumsbereiche.
Dies ermöglicht den Zugang zu diesen Zellen nur, wenn wir die Arbeitsblätter mit dem Arbeitsblattschutzmenü schützen. So bleiben Ihre Formeln und Formatierungen erhalten und Benutzer können nur ihre Anwesenheit ändern.
Schritt 4: Berechnen Sie die aktuellen Tage der Teamkollegen
Wie berechnet man die heutigen Tage? Nun, jeder hat seine eigenen Formeln zur Berechnung der Anwesenheit. Ich werde meine hier besprechen. Sie können Änderungen gemäß Ihrer Anwesenheitsliste vornehmen.
Ich zähle die Gesamtzahl der gegenwärtigen Tage als Gesamtzahl der Tage in einem Monat, abzüglich der Anzahl der Abwesenheitstage. Dadurch werden Feiertage und Wochenenden in Schach gehalten. Sie werden automatisch als Arbeitstage gezählt.
Die Excel-Formel zum Zählen der heutigen Tage lautet also:
| =COUNT(dates)-COUNTIF(attendance_range, "A") |
Dadurch bleiben alle Personen standardmäßig den ganzen Monat lang anwesend, bis Sie sie auf dem Blatt als abwesend markiert haben.
Im Beispiel lautet die Formel:
| =ANZAHL($C$2:$AG$2)-ZÄHLENWENN(C3:AG3,"A") |
Ich habe diese Formel in Zelle B3 geschrieben und dann nach unten kopiert. Sie können sehen, dass 27 Tage als Geschenk angezeigt werden. Auch wenn ich nicht alle Präsenzzellen gefüllt habe. Sie können dies beibehalten, wenn sie standardmäßig vorhanden sein sollen. Oder wenn sie standardmäßig abwesend sein sollen, markieren Sie alle Zellen als abwesend. Dadurch werden nur die aktuellen Tage in der aktuellen Berechnung berücksichtigt.
Schritt 5: Schützen Sie das Blatt
Jetzt haben wir alles auf diesem Blatt gemacht. Schützen wir es, damit niemand die Formel oder die Formatierung auf dem Blatt ändern kann.
Gehen Sie in der Multifunktionsleiste auf die Registerkarte Überprüfung. Suchen Sie das Menü "Blatt schützen". Klick es an. Es öffnet sich ein Dialogfeld, in dem Sie nach den Berechtigungen gefragt werden, die Sie den Benutzern erteilen möchten. Aktivieren Sie alle Berechtigungen, die Sie zulassen möchten. Ich möchte nur, dass der Benutzer die Anwesenheit mit nichts anderem füllen kann. Also werde ich es so lassen wie es ist.
Sie sollten ein Passwort verwenden, das Sie sich leicht merken können. Andernfalls kann jeder sie entsperren und die Anwesenheitsarbeitsmappe ändern.
Wenn Sie nun versuchen, Nicht-Anwesenheitszellen zu ändern, lässt Excel dies nicht zu. Sie können die Anwesenheitszellen jedoch ändern, da wir sie ungeschützt haben.
Schritt 6: Führen Sie das obige Verfahren für alle Monatsblätter durch
Machen Sie dasselbe für jedes Monatsblatt. Am besten kopieren Sie dasselbe Blatt und machen 12 Blätter daraus. Heben Sie den Schutz auf, nehmen Sie die erforderlichen Änderungen vor und schützen Sie sie dann erneut.
Bereiten Sie das Master-Anwesenheitsblatt vor
Obwohl wir alle Blätter bereit haben, die für das Ausfüllen der Anwesenheitsliste verwendet werden können, haben wir keinen einzigen Ort, um sie alle zu überwachen.
Die Verwaltung möchte alle Anwesenheiten an einem Ort statt auf verschiedenen Blättern sehen. Wir müssen ein Master-Anwesenheitsblatt erstellen.
Schritt 7: Vorbereiten der Master-Tabelle, um die Anwesenheit an einem Ort in Excel zu überwachen
Bereiten Sie dafür eine Tabelle vor, die die Namen der Teamkollegen als Zeilenüberschriften und den Monatsnamen als Spaltenüberschriften enthält. Siehe das Bild unten.
Schritt 7: Suchen Sie die Anwesenheit des Teams aus jedem Monatsblatt
Um die Anwesenheit aus dem Blatt nachzuschlagen, können wir eine einfache SVERWEIS-Formel verwenden, aber dann müssen wir dies 12 Mal für jedes Blatt tun. Aber Sie wissen, dass wir eine Formel haben können, um aus mehreren Blättern nachzuschlagen.
Verwenden Sie diese Formel in Zelle C3 und kopieren Sie sie in den Rest der Blätter.
| =SVERWEIS($A3,INDIREKT(C$2&"!$A$3:$B$12"),2,0) |
Da wir wissen, dass alle Blätter insgesamt im Bereich B3:B12 anwesend sind, verwenden wir die Funktion INDIREKT, um Werte von mehreren Blättern abzurufen. Wenn Sie diese Formel nach rechts kopieren, sucht sie nach Werten in Feb-Blättern.
Achtung: Stellen Sie sicher, dass die Blattnamen und die Spaltenüberschriften im Master gleich sind, sonst funktioniert diese Formel nicht.
Schritt 8: Verwenden Sie die Sum-Funktion, um alle aktuellen Tage des Jahres eines Teamkollegen zu erhalten.
Dies ist optional. Wenn Sie möchten, können Sie die Gesamtzahl der heutigen Tage Ihrer Mitarbeiter über das Jahr hinweg einfach mit der Summenformel berechnen.
Und das ist alles. Wir haben unser Excel-Anwesenheitsverwaltungssystem bereit. Sie können dies nach Ihren Anforderungen ändern. Verwenden Sie es für Gehaltsberechnungen, Anreizberechnungen oder alles andere. Dieses Tool wird Sie nicht im Stich lassen.
Sie können in jedem Blatt Änderungen vornehmen, um Feiertage und Wochenenden separat zu berechnen. Ziehen Sie sie dann von der Gesamtzahl der gegenwärtigen Tage ab, um die Gesamtzahl der Arbeitstage zu berechnen. Sie können auch L für Urlaub in das Dropdown-Menü aufnehmen, um den Urlaub von Mitarbeitern zu markieren.
Also ja, Leute, so können Sie ein Excel-Anwesenheitsmanagementsystem für Ihr Startup erstellen. Es ist günstig und sehr flexibel. Ich hoffe, dieses Tutorial hilft Ihnen beim Erstellen Ihrer eigenen Excel-Anwesenheitsarbeitsmappe. Wenn Sie Fragen haben, lassen Sie es mich im Kommentarbereich unten wissen.
Nachschlagen aus Variablentabellen mit INDIRECT: Um eine Tabellenvariable in Excel zu suchen, können wir die INDIRECT-Funktion verwenden. Die Funktion INDIREKT nimmt den Textbereich und wandelt ihn in den tatsächlichen Anwesenheitsbereich um.
Verwenden Sie INDEX und MATCH, um den Wert zu suchen: Die INDEX-MATCH-Formel wird verwendet, um dynamisch und präzise einen Wert in einer gegebenen Tabelle nachzuschlagen. Dies ist eine Alternative zur SVERWEIS-Funktion und überwindet die Unzulänglichkeiten der SVERWEIS-Funktion.
Verwenden Sie SVERWEIS aus zwei oder mehr Nachschlagetabellen | Um aus mehreren Tabellen zu suchen, können wir einen IFERROR-Ansatz verwenden. Das Nachschlagen aus mehreren Tabellen nimmt den Fehler als Wechsel zur nächsten Tabelle. Eine andere Methode kann ein If-Ansatz sein.
So führen Sie die Groß-/Kleinschreibung in Excel durch | Die SVERWEIS-Funktion von Excel unterscheidet nicht zwischen Groß- und Kleinschreibung und gibt den ersten übereinstimmenden Wert aus der Liste zurück. INDEX-MATCH ist keine Ausnahme, kann jedoch geändert werden, um die Groß-/Kleinschreibung zu berücksichtigen. Mal sehen wie…
Nachschlagen von häufig erscheinendem Text mit Kriterien in Excel | Die Suche erscheint am häufigsten im Text in einem Bereich, den wir mit der Funktion INDEX-MATCH mit MODE verwenden. Hier ist die Methode.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
So verwenden Sie die Excel SVERWEIS-Funktion| Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie Excel ZÄHLENWENN-Funktion| Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.