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
IF(AND( D2>=$L$1,D2🎜🎜<img src="https://img.php.cn/upload/image/114/658%20/359/1658832579533626.jpg" 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)
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)
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!