Partager des compétences pratiques sur Excel : organisez rapidement les données de présence !

Dans l'article précédent "Apprentissage des graphiques Excel : Créer un histogramme multi-séries multi-conditions avec des valeurs cibles", nous avons découvert un didacticiel de graphique Excel, et aujourd'hui nous partageons un didacticiel sur l'organisation des données de présence, juste deux simples Une fois vous apprenez la formule, vous n'aurez pas peur, peu importe le nombre de personnes dans votre entreprise.

Les machines de présence sont utilisées par presque toutes les entreprises. Au début et à la fin de chaque mois, il nous faut beaucoup de temps pour trier les données de présence exportées par la machine. Aujourd'hui, je vais vous apprendre deux formules qui peuvent. être triés en une minute. Exportez les données de fréquentation.

Les données de présence que nous exportons habituellement depuis la machine de présence sont essentiellement dans ce format lorsqu'elles sont ouvertes via Excel :

Afin de collecter davantage les données de présence, nous espérons organiser le format ci-dessus dans cet effet :

Le pointage quotidien de chaque personne est enregistré sur la même ligne, et les anomalies sont notées après comparaison en fonction du temps de trajet.

Le temps de trajet est le suivant :

Le temps de travail le matin est de 8h00, le temps de travail le matin est de 12h00, le temps de travail de l'après-midi est de 13h30 et le temps de travail le l'après-midi est 17h30

Pour réaliser ce genre de transformation, cela semble très... Une chose gênante, en fait, tant que vous maîtrisez deux formules et quelques compétences opérationnelles de base, vous pouvez la réaliser en une minute. comment y parvenir.

1. Organisation des données de base

Ajoutez quelques colonnes à droite des données de base, divisez le temps en quatre colonnes : travail du matin, travail du matin, travail de l'après-midi et congé de l'après-midi, et marquez le correspondant heure :

Copiez les trois colonnes du numéro de carte, de la personne et de la date aux positions correspondantes à droite, puis utilisez la fonction "Supprimer les doublons" :

Après avoir cliqué sur OK, le contenu en double sera supprimé, ne laissant qu'une seule ligne par personne et par jour :

La tâche suivante consiste à remplir l'heure de pointage correspondante dans la position correspondante, et à ne pas afficher l'heure spécifique pour les données anormales, afficher uniquement le mot anormal . Pour atteindre cet objectif, deux formules doivent être utilisées. Examinons d’abord la première formule.

2. Utilisez la formule pour noter l'heure d'arrivée

Afin de faciliter les statistiques de l'heure d'arrivée, vous devez d'abord prendre des notes en fonction du temps de trajet pour obtenir l'effet de l'image ci-dessous :

Répartir en fonction du temps de trajet Il existe quatre situations :

1 L'enregistrement avant 8 heures est considéré comme un travail du matin

2. comme travailler le matin. En tenant compte du facteur de travail l'après-midi, la limite artificielle est entre 12h00 et 12h30. Cela signifie que vous quitterez le travail le matin

3. artificiellement stipulé que pointer entre 13h00 et 13h30 signifie que vous quitterez le travail dans l'après-midi

4. Pointer après 17h30 signifie que vous quitterez le travail dans l'après-midi

5. les enregistrements ne sont pas valides et l’écran est vide.

La formule de la cellule E2 est :

=IF(D2=$L$1,D2=$P$1,D2=$N$1,$N$2,"") code >, double-cliquez sur le remplissage pour obtenir l'effet de l'image. <code>=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

Le premier IF est  : =IF(D2

🎜Lorsque d2 (heure de l'horloge) est inférieur ou égal à k1 (heure du matin temps de travail) Quand, le résultat de la fonction if est k2 (les quatre mots "aller travailler le matin"), sinon elle renvoie une valeur nulle 🎜🎜🎜Le deuxième if est 🎜 : IF(AND( D2>=$L$1,D2🎜🎜<img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/image/470/364/212/1658832673103984.jpg" class="lazy" title="1658832579533626 .jpg" alt="Partager des compétences pratiques sur Excel : organisez rapidement les données de présence !">🎜🎜Lorsque d2 (pointage à l'heure) est supérieur ou égal à L2 (heure de pointage le matin) et inférieur supérieur ou égal à o1 (pointage artificiellement spécifié à l'heure du travail), le résultat de la fonction if est L2 (pointage le matin) Ces quatre mots sont "hors travail"), sinon une valeur nulle est renvoyée. 🎜🎜🎜Le troisième si est 🎜 : <code>IF(AND(D2>=$P$1,D2🎜🎜🎜🎜<p>Lorsque d2 (heure de pointage) est supérieur ou égal à p2 (heure de début de pointage artificiellement spécifiée) et inférieur ou égal à M1 (heure de travail de l'après-midi), le résultat de la fonction if est M2 (les quatre mots "off travail l'après-midi"), sinon une valeur nulle est renvoyée. </p> <p><strong>Le quatrième if est </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)

Lorsque d2 (heure d'enregistrement) est supérieur ou égal à N1 ( après-midi, quittez le travail), le résultat de la fonction if est N2 (les quatre mots « quittez le travail l'après-midi »), sinon elle renvoie une valeur nulle.

Après avoir complété les informations de remarque, il est temps de remplir l'heure correspondante dans la zone correspondante. À ce moment, vous pouvez utiliser une formule pour cliquer avec le bouton droit et dérouler vers le bas pour terminer le remplissage de l'heure. formule magique.

3. Temps de remplissage

Entrez la formule dans la cellule K2 :

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

Tirez vers la droite pour terminer le remplissage du temps.

Ceci La formule utilise deux fonctions, text et sumifs. Voyons le principe de la formule. La structure de la fonction

sumifs est sumifs (zone de données à additionner, zone de condition 1, condition 1, zone de condition 2, condition 2...) Dans l'exemple d'aujourd'hui, nous avons utilisé trois conditions , la somme réelle est la colonne D. Les trois conditions sont la date, le numéro de carte et les informations de remarque. Les nombres qui remplissent les trois conditions sont tous uniques, de sorte que le résultat de la sommation et le résultat de référence sont cohérents. Donc la formule est : SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2)

Utilisez la formule pour obtenir le résultat sous la forme de nombres comme celui-ci, car dans Excel, l'essence de la date et de l'heure sont des nombres, les entiers représentent les dates et les décimales représentent l'heure. Changez le format de cellule de la zone ci-dessus. passer du temps et revoir En regardant l'effet, les nombres se sont transformés en heures spécifiques, comme indiqué ci-dessous :

🎜En fait, après avoir défini le format de cellule, l'objectif est fondamentalement atteint afin d'améliorer l'effet d'affichage et. forcer l'affichage à être au format horaire, nous Une fonction de texte est ajoutée en dehors des sumifs Même au format normal, elle est affichée par heure, et la position 0 est affichée comme exception. 🎜🎜🎜🎜🎜 Expliquez brièvement l'utilisation du texte, texte (données, format spécifié). Dans cet exemple, le deuxième format de paramètre est défini comme le mode d'affichage des heures, des minutes et des secondes. Les lettres h, m et s représentent les heures. , minutes et secondes respectivement. Il s'agit d'un affichage à deux chiffres. 🎜🎜Le point-virgule dans le code de format peut être affiché séparément selon le type de données. Le texte spécifie que les données sont divisées en quatre types : nombre positif ; nombre négatif ; Dans cet exemple, les nombres positifs sont affichés selon le format de l'heure, les nombres négatifs et le texte ne sont pas affichés sans un format spécifié et zéro est affiché sous la forme du mot « anormal ». 🎜🎜À propos de la fonction texte, il vous suffit aujourd'hui de comprendre son utilisation dans cet exemple. 🎜🎜🎜🎜Résumé : 🎜🎜🎜🎜1. Utilisation appropriée des colonnes auxiliaires : L'organisation des données de fréquentation a toujours été un problème gênant. Il est souvent très difficile de réussir en une seule étape. peut réduire la difficulté du problème. 🎜🎜2. La formule n'est pas omnipotente : la donnée de base de la fréquentation est le temps, et les réglementations associées forment ensemble la relation logique entre les données. Cet exemple convient à une situation relativement standardisée, si plusieurs équipes coexistent, il ne peut pas y parvenir. grâce à cette méthode, elle doit être prise en compte de manière globale en fonction du temps de trajet correspondant au quart de travail de chaque personne, et le logiciel de support de la machine de présence est nécessaire pour compléter le résumé statistique des données. 🎜🎜Recommandations d'apprentissage associées : 🎜Tutoriel Excel🎜🎜

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!