Vermittlung praktischer Excel-Kenntnisse: Anwesenheitsdaten schnell organisieren!

Im vorherigen Artikel „Erlernen von Excel-Diagrammen: Erstellen Sie ein Histogramm mit mehreren Serien und mehreren Bedingungen mit Zielwerten“ haben wir etwas über ein Excel-Diagramm-Tutorial erfahren, und heute teilen wir ein Tutorial zum Organisieren von Anwesenheitsdaten, nur zwei einfache Einmal Wenn Sie die Formel lernen, werden Sie keine Angst mehr haben, egal wie viele Leute in Ihrem Unternehmen sind.

Anwesenheitsmaschinen werden von fast jedem Unternehmen verwendet. Zu Beginn und am Ende eines jeden Monats benötigen wir viel Zeit, um die von der Maschine exportierten Anwesenheitsdaten zu sortieren Anwesenheitsdaten exportieren.

Die Anwesenheitsdaten, die wir normalerweise aus dem Anwesenheitsautomaten exportieren, haben grundsätzlich dieses Format, wenn sie über Excel geöffnet werden:

Um die Anwesenheitsdaten weiter zu erfassen, hoffen wir, das obige Format so zu organisieren:

Der tägliche Punch-In jeder Person wird in derselben Zeile aufgezeichnet und Auffälligkeiten werden nach dem Vergleich basierend auf der Pendelzeit markiert.

Die Pendelzeit ist wie folgt:

Die Arbeitszeit am Morgen ist 8:00 Uhr, die Arbeitszeit am Morgen ist 12:00 Uhr, die Arbeitszeit am Nachmittag ist 13:30 Uhr und die Arbeitszeit am Nachmittag ist 17:30

Um diese Art der Transformation zu erreichen, fühlt es sich sehr ... an. Eine mühsame Sache, solange Sie zwei Formeln und einige grundlegende Bedienfähigkeiten beherrschen, können Sie es in einer Minute schaffen wie man es erreicht.

1. Organisation der Grunddaten

Fügen Sie rechts neben den Grunddaten einige Spalten hinzu, teilen Sie die Zeit in vier Spalten auf: Morgenarbeit, Morgenarbeit, Nachmittagsarbeit und Nachmittagsfeier, und markieren Sie die entsprechenden Uhrzeit:

Kopieren Sie die drei Spalten Kartennummer, Person und Datum an die entsprechenden Stellen rechts und nutzen Sie dann die Funktion „Duplikate löschen“:

Nach dem Klicken auf OK wird der doppelte Inhalt angezeigt gelöscht, sodass nur eine Zeile pro Person und Tag übrig bleibt:

Die nächste Aufgabe besteht darin, die entsprechende Punch-In-Zeit an der entsprechenden Position einzugeben und nicht die spezifische Zeit für abnormale Daten anzuzeigen, sondern nur das Wort abnormal . Um diesen Zweck zu erreichen, müssen zwei Formeln verwendet werden. Schauen wir uns zunächst die erste Formel an.

2. Verwenden Sie die Formel, um die Einstempelzeit zu notieren

Um die Statistik der Einstempelzeit zu erleichtern, müssen Sie zunächst Notizen entsprechend der Pendelzeit machen, um den Effekt im Bild unten zu erzielen :

Aufteilen nach der Pendelzeit:

1. Einchecken vor 8 Uhr gilt als morgens Wenn man den Faktor Arbeit am Nachmittag berücksichtigt, liegt die künstliche Grenze zwischen 12:00 und 12:30 Uhr Wenn Sie zwischen 13:00 und 13:30 Uhr einchecken, bedeutet dies, dass Sie nachmittags Feierabend haben.

5 Check-Ins sind ungültig und die Anzeige ist leer.

E2-Zellenformel lautet:

=IF(D2=$L$1,D2=$P$1,D2=$N$1,$N$2,"") Code >, doppelklicken Sie auf die Füllung, um den Effekt im Bild zu erzielen.

Das erste IF ist

: =IF(D2

Wenn d2 (Uhrzeit) kleiner oder gleich k1 ist ( Morgenarbeitszeit) Wenn das Ergebnis der if-Funktion k2 ist (die vier Wörter „morgens zur Arbeit gehen“), andernfalls wird ein Nullwert zurückgegeben =IF(D2=$L,D2=$P,D2=$N,$N,"")，双击填充可实现图中的效果。

第一个IF为：=IF(D2

当d2（打卡时间）小于等于k1（上午上班时间）时，if函数的结果为k2（上午上班这四个字），否则返回空值；

第二个if为：IF(AND(D2>=$L,D2

当d2（打卡时间）大于等于L2（上午下班时间）同时小于等于o1（人为规定下班打卡截止时间）时，if函数的结果为L2（上午下班这四个字），否则返回空值。

第三个if为：IF(AND(D2>=$P,D2

Das zweite if ist : IF(AND (D2>=$L$1,D2

🎜🎜🎜Wenn d2 (Einstempeln) größer oder gleich L2 (Zeit zum Ausstempeln am Morgen) ist und kleiner oder gleich o1 (künstlich festgelegte Frist für das Einstempeln von der Arbeit), das Ergebnis der if-Funktion ist L2 (Einstempeln am Morgen). Diese vier Wörter sind „frei von der Arbeit“), andernfalls wird ein Nullwert zurückgegeben. 🎜🎜🎜Das dritte Wenn ist 🎜: IF(AND(D2>=$P$1,D2🎜🎜🎜🎜<p>Wenn d2 (Stanzzeit) größer oder gleich p2 (künstlich vorgegebene Einstempel-Startzeit) und kleiner oder gleich M1 (Nachmittagsarbeitszeit) ist, ist das Ergebnis der if-Funktion M2 (die vier Wörter „aus“) Arbeit am Nachmittag"), andernfalls wird ein Nullwert zurückgegeben. </p> <p><strong>Das vierte if ist </strong>: <code>IF(D2>=$N$1,$N$2,"")IF(D2>=$N,$N,"")

当d2（打卡时间）大于等于N1（下午下班时间）时，if函数的结果为N2（下午下班这四个字），否则返回空值。

完成了备注信息之后，就该把对应的时间填入对应的区域内，这时候可以用一个公式右拉下拉就能完成时间的填充，一起来看看是哪个神奇的公式吧。

三、填充时间

在K2单元格输入公式：

=TEXT(SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K),"hh:mm:ss;;异常;")

右拉下拉即可完成时间的填充。

这个公式用到了两个函数，text和sumifs，来看看公式的原理吧。

sumifs函数的结构为sumifs（要求和的数据区域，条件区域1，条件1，条件区域2，条件2……），在今天的例子里我们用了三个条件，实际求和的是D列，三个条件分别是日期、卡号和备注信息，符合三个条件的数字都是唯一的，所以求和结果和引用结果是一致的。

因此公式为：

SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K)

Wenn d2 (Check-in-Zeit) größer oder gleich N1 ist ( Nachmittags Feierabend) ist das Ergebnis der if-Funktion N2 (die vier Wörter „Nachmittags Feierabend“), andernfalls wird ein Nullwert zurückgegeben.

Nachdem Sie die Bemerkungsinformationen vervollständigt haben, ist es an der Zeit, die entsprechende Zeit in den entsprechenden Bereich einzutragen. Zu diesem Zeitpunkt können Sie mit der rechten Maustaste klicken und nach unten ziehen, um die Zeitausfüllung abzuschließen magische Formel.

3. Füllzeit

Geben Sie die Formel in Zelle K2 ein:

=TEXT(SUMIFS($D:$D,$C:$C,$J3,$A:$A, $H3,$E:$E,K$2),"hh:mm:ss;;Exception;")

Ziehen Sie nach rechts, um die Zeitfüllung abzuschließen.

Das Die Formel verwendet zwei Funktionen, Text und Sumifs. Schauen wir uns das Prinzip der Formel an. Die Struktur der

sumifs-Funktion ist sumifs (zu summierender Datenbereich, Bedingungsbereich 1, Bedingung 1, Bedingungsbereich 2, Bedingung 2...). Im heutigen Beispiel haben wir drei Bedingungen verwendet. Die tatsächliche Summe ist Spalte D. Die drei Bedingungen sind Datum, Kartennummer und Bemerkungsinformationen. Die Zahlen, die die drei Bedingungen erfüllen, sind alle eindeutig, sodass das Summierungsergebnis und das Referenzergebnis konsistent sind. Die Formel lautet also: SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2)

Verwenden Sie die Formel, um als Ergebnis einige Zahlen wie diese zu erhalten, da in Excel das Wesentliche von Datum und Uhrzeit Zahlen sind, ganze Zahlen Datumsangaben darstellen und Dezimalzahlen die Zeit darstellen. Ändern Sie das Zellenformat des obigen Bereichs Wenn Sie den Effekt betrachten, werden die Zahlen zu bestimmten Zeiten, wie unten gezeigt:

🎜Tatsächlich wird nach dem Festlegen des Zellenformats der Zweck im Wesentlichen erreicht, nämlich den Anzeigeeffekt zu verbessern Erzwingen Sie die Anzeige im Zeitformat. Wir fügen eine Textfunktion außerhalb von Sumifs hinzu. Auch im regulären Format wird sie nach Zeit angezeigt, und die Position 0 wird als Ausnahme angezeigt. 🎜🎜🎜🎜🎜 Erklären Sie kurz die Verwendung von Text, Text (Daten, angegebenes Format). In diesem Beispiel ist das zweite Parameterformat als Anzeigemodus für Stunden, Minuten und Sekunden definiert , Minuten und Sekunden. Es handelt sich um eine zweistellige Anzeige. 🎜🎜Das Semikolon im Formatcode kann je nach Datentyp separat angezeigt werden: positive Zahl, negative Zahl. In diesem Beispiel werden positive Zahlen entsprechend dem Zeitformat angezeigt, negative Zahlen und Text werden ohne angegebenes Format nicht angezeigt und Null wird als Wort „abnormal“ angezeigt. 🎜🎜Was die Textfunktion betrifft, müssen Sie heute nur noch deren Verwendung in diesem Beispiel verstehen. 🎜🎜🎜🎜Zusammenfassung: 🎜🎜🎜🎜1. Die ordnungsgemäße Verwendung von Hilfsspalten war schon immer ein mühsames Problem. Derzeit ist es oft sehr schwierig, die Hilfsspalten richtig zu nutzen kann die Schwierigkeit des Problems erheblich verringern. 🎜🎜2. Die Formel ist nicht allmächtig: Die Grunddaten der Anwesenheit sind die Zeit, und die zugehörigen Vorschriften bilden zusammen die logische Beziehung zwischen den Daten. Wenn mehrere Schichten nebeneinander existieren, kann dies nicht erreicht werden Bei dieser Methode muss die Pendelzeit, die jeder Schicht entspricht, umfassend berücksichtigt werden, und die unterstützende Software des Anwesenheitsautomaten ist erforderlich, um die statistische Zusammenfassung der Daten zu vervollständigen. 🎜🎜Verwandte Lernempfehlungen: 🎜Excel-Tutorial🎜🎜

Das obige ist der detaillierte Inhalt vonVermittlung praktischer Excel-Kenntnisse: Anwesenheitsdaten schnell organisieren!. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!