Partage de compétences pratiques sur Excel : Réaliser une feuille de présence intelligente

Dans l'article précédent « Partager des compétences pratiques sur Excel : Comment rendre les graphiques à barres plus vivants ! 》Dans cet article, nous avons découvert les moyens de rendre les graphiques à barres Excel plus vivants et plus distinctifs. Aujourd'hui, nous allons voir comment réaliser une feuille de présence intelligente, venez apprendre !

Si la machine de présence n'est pas si intelligente et nécessite des statistiques auxiliaires manuelles, et si l'entreprise n'a pas encore utilisé la machine de présence, la création de la feuille de présence pour le mois en cours est un processus compliqué et long. Par conséquent, comment améliorer l'efficacité du travail et compléter les statistiques mensuelles de présence dans un temps limité est un gros problème pour le MM du service du personnel.

Pour gagner en efficacité, vous devez avoir une meilleure feuille de présence. Cette feuille de présence doit comporter au moins les points suivants :

• Simplifier la saisie des éléments de présence

• Affichage accrocheur des données du week-end

• Capable de résumer automatiquement les données

Aujourd'hui, je vais vous apprendre comment faire une feuille de présence intelligente, qui comporte les trois points ci-dessus en même temps, venez jeter un oeil !

Avant de commencer à le réaliser, jetons un coup d'œil à l'affichage final de l'effet :

Dans la démonstration, vous pouvez retrouver les points suivants :

• La saisie du titre est très simple, il vous suffit de saisir un numéro du mois ;

• Le nombre de jours dans le mois changera automatiquement, la date s'ajustera et s'affichera automatiquement en fonction du mois, et les samedis et dimanches changeront automatiquement de couleur

• Les données de présence sont saisies à l'aide du drop ; -menu bas, et il y aura des informations rapides ;

• Il y a une présence sur le côté droit du formulaire La zone de statistiques de résultats génère automatiquement des données statistiques basées sur le contenu rempli.

Laissez-moi vous apprendre à créer un tel formulaire.

1. Production du corps principal de la feuille de présence

Tout d'abord, réalisez un formulaire le plus basique, comme le montre l'image :

La préparation de ce formulaire est très simple, je crois que tout le monde peut le compléter, ensuite c'est juste un petit travail de détail, vous devez donc le regarder attentivement.

Fusionnez la première ligne et les cinq dernières cellules de la deuxième ligne, fusionnez respectivement trois et deux. L'effet final est le suivant :

Cliquez sur la cellule fusionnée dans la première ligne. Cliquez avec le bouton droit et sélectionnez. "Formater les cellules". Dans la boîte de dialogue contextuelle, le format personnalisé est : Tableau des statistiques de fréquentation de la société Xinsheng pour octobre.

Remarque, voici le chiffre 0, pas la lettre O.

Après la configuration, cliquez sur OK, puis entrez un numéro dans la première ligne pour voir l'effet.

N'est-ce pas étonnant ? En fait, cela profite des caractéristiques des formats personnalisés.

Entrez simplement le "nombre de jours dans le mois" directement sur la deuxième ligne, puis remplissez la formule : =DAY(EDATE(DATE(2018,A1,1),1)-1)

Une brève introduction à cette formule La signification de , trois fonctions de date sont utilisées :

• Le format de la fonction DATE est : DATE (année, mois, jour), c'est-à-dire qu'une date est obtenue en fonction de l'année spécifiée, mois et jour. Dans cet exemple, l'année est 2018 (si c'est l'année suivante, remplacez-le simplement par 2019). Le mois est le nombre saisi dans la première ligne et le jour est 1. La date obtenue est la. 1er du mois dans la feuille de présence.

• Le format de la fonction EDATE est : EDATE (date de début, nombre de mois d'intervalle), c'est-à-dire qu'une nouvelle date est obtenue en fonction de la date spécifiée et du nombre de mois d'intervalle. Dans cet exemple, la date de début. est le 1er du mois en cours, et le nombre de mois d'intervalle est de 1 représente la date du 1er du mois suivant.

• Le format de la fonction DAY est : DAY (date), qui obtient le jour du mois pour la date spécifiée. Dans cet exemple, la date spécifiée est un jour avant le 1er du mois suivant (il y a un. moins 1 après la fonction EDATE), autrement dit c'est le dernier jour du mois. Utilisez ensuite la fonction JOUR pour obtenir le nombre total de jours dans le mois.

Remarque : les fonctions de date sont souvent utilisées conjointement avec plusieurs fonctions, et il existe plusieurs idées pour le même problème. Certaines formules ont des idées très intelligentes, ce qui est également l'un des plaisirs de l'apprentissage des fonctions.

La prochaine chose à définir est le jour de la semaine. Remplissez la formule dans la cellule C4 : =TEXT(DATE(2018,$A1,C3),"aaa") et faites-la glisser vers la droite.

La formule =TEXT(DATE(2018,$A$1,C$3),"aaa") utilise deux fonctions, TEXT et DATE Expliquons respectivement les fonctions de ces deux fonctions : =TEXT(DATE(2018,$A,C),"aaa")中用到了两个函数TEXT和DATE，分别解释一下这两个函数的作用：

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

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

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

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

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

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

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

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

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

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

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

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

进一步选择【使用公式确定要设置格式的单元格】，输入公式：=C>$AF Le. La fonction DATE vient d'être mentionnée. Dans cette formule, l'année est toujours 2018 et le mois est A1. Parce que la formule doit être tirée vers la droite, afin d'éviter que A1 ne change en tirant vers la droite, $ lock est. ajouté devant le numéro de colonne, et le jour est Utilisez simplement les chiffres correspondants dans la troisième ligne pour l'exprimer, afin d'obtenir toutes les dates du mois.

Le format de la fonction TEXTE est : TEXTE (données pour préciser le format, code de format). Cette fonction est considérée comme une fonction relativement avancée. Bien que la structure soit relativement simple, elle comporte de nombreux codes de format, c'est donc également une fonction multifonctionnelle. Le code de format dans cet exemple est « aaa », qui utilise un mot pour afficher le jour de la semaine. Les lecteurs intéressés peuvent essayer les effets des codes « aaaa », « ddd » et « ddd » respectivement.

Remarque : Lors de l'utilisation de la fonction TEXTE, le code de format doit être entouré de guillemets (les guillemets sont saisis en mode anglais).

À ce stade, le tableau est pratiquement terminé. Certaines unités peuvent ne pas faire la distinction entre le matin et l'après-midi, mais certaines unités le font. Comment changer une ligne pour chaque personne en deux lignes ? ? Certainement pas. Une astuce très simple est utilisée ici. Jetons un coup d'œil à la démonstration d'animation :

Dans cette opération, plusieurs techniques interviennent : la technique d'insertion de lignes vierges par lots, la technique d'utilisation du peintre de format, et la technique de rapide en remplissant des techniques de contenu en double, j'espère que vous pourrez pratiquer davantage et maîtriser ces techniques. À ce stade, le thème de cette feuille de présence est terminé, et l'effet est le suivant :

2. Utiliser le formatage conditionnel et la validité des données pour traiter les tableaux

Les deux tâches suivantes doivent être complétées :

• Laissez les dates dans le tableau être affichées en fonction de la situation réelle, ainsi que les samedis et dimanches changera automatiquement de couleur ; li>

• Utilisez le menu déroulant pour terminer la saisie des données de présence, et des informations rapides s'afficheront en même temps.

Voyons comment obtenir ces effets. Pour le premier élément, vous devez utiliser la fonction de formatage conditionnel. Bien qu'il n'y ait pas beaucoup d'étapes, il y a de nombreux détails. Veuillez les suivre lentement :

(1) Sélectionnez la zone de données et cliquez sur [Formatage conditionnel] - [Nouvelle règle. ] :

Allez plus loin Sélectionnez [Utiliser des formules pour déterminer les cellules à formater], saisissez la formule :

=C$3>$AF$2, puis cliquez sur le bouton [Formater] :

🎜🎜Analyse de formule : très simple, il s'agit de comparer si chaque date de la troisième ligne est supérieure au nombre de jours du mois. S'il est supérieur, exécutez-le selon le format défini. Dans cette étape, faites attention. la position de $. 🎜🎜Sélectionnez [Personnalisé], entrez trois points-virgules dans le type : ;;;, notez qu'il s'agit d'un point-virgule en anglais et cliquez sur OK une fois terminé. 🎜🎜🎜🎜🎜Explication du code de format : La signification spécifique est plus compliquée, rappelez-vous 🎜 Entrer trois points-virgules signifie ne pas afficher le contenu de la cellule. 🎜🎜🎜Cliquez à nouveau sur OK. À ce moment-là, vous constaterez que les données qui n'appartiennent pas à la date du mois en cours ne sont plus visibles : 🎜🎜🎜🎜🎜Pour définir davantage le changement de couleur automatique les samedis et dimanches. , vous pouvez toujours sélectionner la zone de données, créer une nouvelle règle et saisir la formule =OR( C$4="six", C$4="day") puis définir le format : 🎜🎜🎜🎜🎜Cette formule est également facile à comprendre. OR sera exécuté selon le format défini si l'une des deux conditions est remplie, qui est la quatrième. Si le comportement est "six" ou "jour", la couleur spécifiée sera affichée. 🎜🎜Après avoir cliqué sur le bouton Formater les cellules, sélectionnez [Remplir], sélectionnez une couleur et cliquez sur OK. 🎜🎜🎜🎜🎜Cliquez à nouveau sur OK pour voir l'effet : 🎜🎜🎜🎜🎜Hé, pourquoi les espaces vides à l'arrière sont-ils également colorés ? Y a-t-il un problème ? 🎜

En regardant attentivement, j'ai découvert que cet endroit se trouvait être samedi, mais il était masqué par le format défini précédemment, mais il prenait toujours effet lors de la coloration. Comment résoudre ce problème ? C'est très simple. Après avoir sélectionné la zone de données, ouvrez les règles de gestion de la mise en forme conditionnelle :

Cochez "Stop si vrai" après la règle suivante, puis cliquez sur le bouton Monter et cliquez sur OK.

Voir l'effet.

L'étape suivante consiste à définir la validité des données de fréquentation. Avant de procéder à cette étape, terminons d'abord la création de la zone statistique.

3. Production de zone statistique

La production de cette zone est très simple Selon vos besoins réels, ajoutez une colonne de remarques, remplissez les rubriques de fréquentation et la légende, et utilisez la fonction COUNTIF sous chaque légende. pour compléter les statistiques, la formule est =COUNTIF($C5:$AG6,AI)/2 :

Le format de la fonction COUNTIF est : COUNTIF (zone statistique, contenu à compter), en termes simples, c'est chaque élément de fréquentation (à compter) dans la deux lignes (zone statistique) correspondant au contenu de chaque personne) apparaissent. Les données de chaque jour dans l'exemple étant constituées de deux lignes, le résultat statistique doit être divisé par 2.

Quant au traitement de ces cellules fusionnées, c'est très simple. Il suffit d'en fusionner une puis d'utiliser le pinceau de format pour la brosser.

Maintenant, revenez en arrière et configurez le menu déroulant des données de fréquentation. Avant de définir la validité, effectuez d'abord un travail de préparation, copiez la légende et la description pour une utilisation ultérieure, puis définissez la validité. Regardons la démonstration d'animation pour cette partie de l'opération :

A ce stade, la feuille de présence est complétée. Je pense que vous gagnerez beaucoup du contenu d’aujourd’hui !

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!