Praktischer Austausch von Excel-Kenntnissen: Erstellen Sie eine intelligente Anwesenheitsliste

Im vorherigen Artikel „Praktische Excel-Kenntnisse weitergeben: So gestalten Sie Balkendiagramme anschaulicher!“ 》In diesem Artikel haben wir erfahren, wie man Excel-Balkendiagramme lebendiger und markanter gestalten kann. Heute werfen wir einen Blick darauf, wie man eine intelligente Anwesenheitsliste erstellt. Kommen Sie und lernen Sie!

Wenn die Anwesenheitsliste nicht so intelligent ist und manuelle Hilfsstatistiken erfordert und das Unternehmen die Anwesenheitsliste noch nicht verwendet hat, ist die Erstellung der Anwesenheitsliste für den aktuellen Monat ein komplizierter und langwieriger Prozess. Daher ist es für den MM in der Personalabteilung eine große Sache, die Arbeitseffizienz zu verbessern und monatliche Anwesenheitsstatistiken innerhalb einer begrenzten Zeit zu erstellen.

Um die Effizienz zu verbessern, müssen Sie über eine bessere Anwesenheitsliste verfügen. Diese Anwesenheitsliste muss mindestens die folgenden Punkte enthalten:

• Vereinfachen Sie die Eingabe von Anwesenheitspunkten

• Auffällige Anzeige von Wochenenddaten

• Kann Daten automatisch zusammenfassen

Heute werde ich es Ihnen beibringen Wie man eine intelligente Anwesenheitsliste erstellt, die die oben genannten drei Punkte gleichzeitig enthält, schauen Sie sich das an!

Bevor Sie mit der Erstellung beginnen, werfen wir einen Blick auf die endgültige Effektanzeige:

In der Demonstration finden Sie folgende Punkte:

• Die Titeleingabe ist sehr einfach, Sie müssen nur einen eingeben Monatsnummer;

• Die Anzahl der Tage im Monat ändert sich automatisch, das Datum wird automatisch entsprechend dem Monat angepasst und angezeigt, und Samstage und Sonntage ändern automatisch die Farbe

• Anwesenheitsdaten werden über den Drop eingegeben -Down-Menü, und es werden sofortige Informationen angezeigt;

• Auf der rechten Seite des Formulars befindet sich die Anwesenheitsliste. Der Ergebnisstatistikbereich generiert automatisch statistische Daten basierend auf dem ausgefüllten Inhalt.

Lassen Sie mich Ihnen beibringen, wie man ein solches Formular erstellt.

1. Erstellung des Hauptteils der Anwesenheitsliste

Erstellen Sie zunächst ein möglichst einfaches Formular, wie im Bild gezeigt:

Die Vorbereitung dieses Formulars ist meiner Meinung nach sehr einfach Kann es fertigstellen, als nächstes Es ist nur eine kleine Detailarbeit, also schauen Sie es sich unbedingt genau an.

Führen Sie die erste Zeile und die letzten fünf Zellen in der zweiten Zeile zusammen, jeweils drei und zwei. Der fertige Effekt ist wie unten gezeigt:

Klicken Sie mit der rechten Maustaste auf die verbundene Zelle in der ersten Zeile und wählen Sie „. Zellen formatieren“. Das angepasste Format im Popup-Dialogfeld lautet: Anwesenheitsstatistiktabelle des Unternehmens Xinsheng für Oktober.

Hinweis: Hier ist die Zahl 0, nicht der Buchstabe O.

Klicken Sie nach dem Einrichten auf „OK“ und geben Sie dann eine Zahl in die erste Zeile ein, um den Effekt zu sehen.

Ist es nicht erstaunlich? Tatsächlich werden hier die Eigenschaften benutzerdefinierter Formate ausgenutzt.

Geben Sie einfach die „Tage des Monats“ direkt in die zweite Zeile ein und füllen Sie dann die Formel aus: =DAY(EDATE(DATE(2018,A1,1),1)-1)

Eine kurze Zusammenfassung Einführung in diese Formel Die Bedeutung von , drei Datumsfunktionen werden verwendet:

• Das Format der DATE-Funktion ist: DATUM (Jahr, Monat, Tag), das heißt, ein Datum wird basierend auf dem angegebenen Jahr, Monat, und Tag. In diesem Beispiel ist das Jahr 2018 (Wenn es das nächste Jahr ist, ändern Sie es einfach in 2019. Der Monat ist die in der ersten Zeile eingegebene Zahl und der Tag ist 1. Das erhaltene Datum ist der 1.). Monat in der Anwesenheitsliste eingetragen.

• Das Format der EDATE-Funktion ist: EDATE (Startdatum, Anzahl der Monate Abstand), d. h. ein neues Datum wird basierend auf dem angegebenen Datum und der Anzahl der Monate Abstand ermittelt. In diesem Beispiel das Startdatum ist der 1. des aktuellen Monats, und die Anzahl der Monate dazwischen beträgt 1, was das Datum des 1. des nächsten Monats darstellt.

• Das Format der DAY-Funktion ist: DAY (Datum), das den Tag des Monats für das angegebene Datum abruft. In diesem Beispiel liegt das angegebene Datum einen Tag vor dem 1. des nächsten Monats (es gibt einen minus 1 nach der EDATE-Funktion), mit anderen Worten: Es ist der letzte Tag des Monats. Verwenden Sie dann die Funktion DAY, um die Gesamtzahl der Tage im Monat zu ermitteln.

Hinweis: Datumsfunktionen werden häufig in Verbindung mit mehreren Funktionen verwendet, und es gibt mehrere Ideen für dasselbe Problem. Einige Formeln haben sehr clevere Ideen, was auch den Spaß am Erlernen von Funktionen ausmacht.

Als nächstes müssen Sie den Wochentag festlegen: Geben Sie die Formel in Zelle C4 ein: =TEXT(DATUM(2018,$A1,C3),"aaa") und ziehen Sie sie nach rechts.

Die Formel =TEXT(DATE(2018,$A$1,C$3),"aaa") verwendet zwei Funktionen, TEXT und DATE. Lassen Sie uns die Funktionen dieser beiden Funktionen erklären: =TEXT(DATE(2018,$A,C),"aaa")中用到了两个函数TEXT和DATE，分别解释一下这两个函数的作用：

DATE函数刚才已经讲过了，在这个公式中，年还是2018，月用的A1，因为公式要向右拉，为了防止右拉的时候A1发生变化，所以在列号前面加了$锁定，日就用第三行对应的数字表示，这样就得到了当月所有的日期。

TEXT函数的格式为：TEXT（要指定格式的数据，格式代码）。这个函数算是一个比较高级的函数了，虽然结构比较简单，但是格式代码非常多，所以也是一个多功能函数。本例中的格式代码为“aaa”，就是用一个字来显示星期，有兴趣的读者可以自己试试代码“aaaa”“ddd”和“dddd”分别是什么效果吧。

注：在使用TEXT函数的时候，格式代码必须加引号（引号在英文状态输入）。

至此，表格大体上已经完成了，有些单位可能不区分上午和下午，但有些单位是区分的，如何将每个人的一行变成两行，难道一行一行插入么？肯定不是，这里用到了一个非常简单的小技巧，一起通过动画演示来看看吧：

在这个操作中，涉及到几个技巧：批量插入空行的技巧、使用格式刷的技巧、快速填充重复内容的技巧，希望大家能够多练习，熟练掌握这些技巧。

至此，这个考勤表的主题已经做好了，效果是这样的：

二、使用条件格式和数据有效性加工表格

接下来需要完成两项工作：

• 让表格中的日期根据实际情况显示，同时周六周日会自动变色；

• 考勤数据使用下拉菜单完成输入，同时有提示信息。

来看看如何实现这些效果。对于第一项内容，要用到条件格式这个功能，步骤虽然不多，但是很多细节地方，大家慢慢跟着来：

（1）选择数据区域，点击【条件格式】-【新建规则】：

进一步选择【使用公式确定要设置格式的单元格】，输入公式：=C>$AF Die In dieser Formel ist das Jahr immer noch 2018 und der Monat ist A1, da die Formel nach rechts gezogen werden muss, um zu verhindern, dass sich A1 beim Ziehen nach rechts ändert vor der Spaltennummer hinzugefügt, und der Tag ist. Verwenden Sie einfach die entsprechenden Zahlen in der dritten Zeile, um ihn auszudrücken, sodass Sie alle Daten des Monats erhalten.

Das Format der TEXT-Funktion ist: TEXT (Daten zur Angabe des Formats, Formatcode). Diese Funktion gilt als relativ fortgeschrittene Funktion. Obwohl die Struktur relativ einfach ist, verfügt sie über viele Formatcodes und ist daher auch eine multifunktionale Funktion. Der Formatcode in diesem Beispiel ist „aaa“, der ein Wort zur Anzeige des Wochentags verwendet. Interessierte Leser können die Auswirkungen der Codes „aaaa“, „ddd“ und „dddd“ ausprobieren.

Hinweis: Bei Verwendung der TEXT-Funktion muss der Formatcode in Anführungszeichen eingeschlossen werden (die Anführungszeichen werden im englischen Modus eingegeben).

An diesem Punkt ist die Tabelle im Grunde fertig. Einige Einheiten unterscheiden möglicherweise nicht zwischen Vormittag und Nachmittag, einige Einheiten jedoch schon. Wie kann man eine Zeile für jede Person in zwei Zeilen umwandeln? ? Definitiv nicht. Hier kommt ein ganz einfacher Trick zum Einsatz. Schauen wir uns die Animationsdemonstration an:

Bei diesem Vorgang sind mehrere Techniken beteiligt: ​​die Technik des stapelweisen Einfügens von Leerzeilen, die Technik der Verwendung des Format Painters und die Technik des schnellen Ich hoffe, dass Sie mit den Techniken zum Ausfüllen doppelter Inhalte mehr üben und sich diese Techniken aneignen können. Zu diesem Zeitpunkt ist das Thema dieser Anwesenheitsliste abgeschlossen und der Effekt ist wie folgt:

2. Verwenden Sie bedingte Formatierung und Datengültigkeit, um Tabellen zu verarbeiten

Als nächstes müssen zwei Aufgaben erledigt werden:

• Lassen Sie die Daten in der Tabelle entsprechend der tatsächlichen Situation sowie Samstage und Sonntage anzeigen ändert automatisch die Farbe; li>

• Verwenden Sie das Dropdown-Menü, um die Eingabe der Anwesenheitsdaten abzuschließen, und es werden gleichzeitig sofortige Informationen angezeigt.

Sehen wir uns an, wie wir diese Effekte erzielen können. Für das erste Element müssen Sie die Funktion zur bedingten Formatierung verwenden. Obwohl es nicht viele Schritte gibt, folgen Sie ihnen bitte langsam:

(1) Wählen Sie den Datenbereich aus und klicken Sie auf [Bedingte Formatierung] – [Neue Regel]. ]:

Gehen Sie weiter: Wählen Sie [Formeln verwenden, um die zu formatierenden Zellen zu bestimmen], geben Sie die Formel ein:

=C$3>$AF$2 und klicken Sie dann auf die Schaltfläche [Formatieren]:

🎜🎜Formelanalyse: Ganz einfach: Vergleichen Sie, ob jedes Datum in der dritten Zeile größer ist als die Anzahl der Tage im Monat. Achten Sie in diesem Schritt darauf, ob es größer ist die Position von $. 🎜🎜Wählen Sie [Benutzerdefiniert], geben Sie drei Semikolons als Typ ein: ;;;. Beachten Sie, dass es sich dabei um Semikolons im Englischen handelt, und klicken Sie auf „OK“, wenn Sie fertig sind. 🎜🎜🎜🎜🎜Erklärung des Formatcodes: Die spezifische Bedeutung ist komplizierter. Denken Sie daran: 🎜Die Eingabe von drei Semikolons bedeutet, dass der Inhalt der Zelle nicht angezeigt wird. 🎜🎜🎜Klicken Sie erneut auf OK. Zu diesem Zeitpunkt werden Sie feststellen, dass die Daten, die nicht zum Datum des aktuellen Monats gehören, nicht mehr sichtbar sind: 🎜🎜🎜🎜🎜Um den automatischen Farbwechsel an Samstagen und Sonntagen weiter einzustellen, Sie können weiterhin den Datenbereich auswählen, eine neue Regel erstellen und die Formel =OR( C$4="six", C$4="day") eingeben und dann das Format festlegen: 🎜🎜🎜🎜🎜Diese Formel ist auch einfach Zum Verständnis wird OR gemäß dem festgelegten Format ausgeführt, wenn eine der beiden Bedingungen erfüllt ist. Dies ist die vierte. Wenn das Verhalten „sechs“ oder „Tag“ ist, wird die angegebene Farbe angezeigt. 🎜🎜Nachdem Sie auf die Schaltfläche „Zellen formatieren“ geklickt haben, wählen Sie [Füllen], wählen Sie eine Farbe aus und klicken Sie auf OK. 🎜🎜🎜🎜🎜Klicken Sie erneut auf OK, um den Effekt zu sehen: 🎜🎜🎜🎜🎜Hey, warum sind die Leerstellen hinten auch farbig? Stimmt etwas nicht? 🎜

Bei genauerem Hinsehen stellte ich fest, dass dieser Ort zufällig Samstag war, aber durch das zuvor festgelegte Format ausgeblendet wurde, aber beim Einfärben trotzdem wirksam wurde. Es ist ganz einfach. Nachdem Sie den Datenbereich ausgewählt haben, öffnen Sie die Verwaltungsregeln der bedingten Formatierung:

Klicken Sie nach der folgenden Regel auf „Stoppen, wenn wahr“, klicken Sie dann auf die Schaltfläche „Nach oben“ und dann auf „OK“.

Sehen Sie die Wirkung.

Der nächste Schritt besteht darin, die Gültigkeit der Anwesenheitsdaten festzulegen. Bevor wir mit diesem Schritt fortfahren, schließen wir zunächst die Erstellung des Statistikbereichs ab.

3. Erstellung des statistischen Bereichs

Die Erstellung dieses Bereichs ist sehr einfach. Fügen Sie entsprechend Ihren tatsächlichen Anforderungen eine Bemerkungsspalte hinzu, füllen Sie die Anwesenheitselemente und die Legende aus und verwenden Sie die ZÄHLENWENN-Funktion unter jeder Legende Um die Statistiken zu vervollständigen, lautet die Formel =COUNTIF($C5:$AG6,AI)/2:

Das Format der ZÄHLENWENN-Funktion ist: ZÄHLENWENN (statistischer Bereich, zu zählender Inhalt), für Laien ausgedrückt ist es jedes (zu zählende) Anwesenheitselement in der Da die Daten für jeden Tag im Beispiel aus zwei Zeilen bestehen, muss das statistische Ergebnis durch 2 geteilt werden.

Die Verarbeitung dieser zusammengeführten Zellen ist sehr einfach. Führen Sie einfach eine zusammen und verwenden Sie dann den Formatpinsel, um sie zu bürsten.

Gehen Sie nun zurück und richten Sie das Dropdown-Menü der Anwesenheitsdaten ein. Führen Sie vor dem Festlegen der Gültigkeit zunächst einige Vorbereitungsarbeiten durch, kopieren Sie die Legende und die Beschreibung zur späteren Verwendung und legen Sie dann die Gültigkeit fest. Sehen wir uns die Animationsdemonstration für diesen Teil des Vorgangs an:

An diesem Punkt ist die Anwesenheitsliste ausgefüllt. Ich glaube, dass Sie von den heutigen Inhalten viel profitieren werden!

Verwandte Lernempfehlungen: Excel-Tutorial

Das obige ist der detaillierte Inhalt vonPraktischer Austausch von Excel-Kenntnissen: Erstellen Sie eine intelligente Anwesenheitsliste. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!