Microsoft 365 Compter les cellules non vides dans un tableau selon plusieurs critères

LAETI-TOINOU · 19 Novembre 2021

Bonjour à vous les experts d'EXCEL,

Je viens vers vous, car malgré tous les sujets et posts que j'ai lus, je n'arrive pas à trouver la solution à mon problème.

En effet, je dois préparer un tableau de bord pour les demandes et accords d'absences du personnel ; tableau qui m'a déjà demandé beaucoup d'heures de travail.
Sur chaque ligne, je dois saisir le nom du salarié, son poste, si c'est une "DEMANDE" ou un "ACCORD" de la hiérarchie, son motif d'absence dans la cellule correspondante. (Par la suite, j'aimerais pouvoir automatiser la saisie des dates en lien avec un autre onglet SAISIE DATES (mais chaque chose en son temps).

Dans l'onglet "SUIVI", j'aimerais pouvoir afficher le nombre de jour d'absences ACCORDES
- pour un salarié en particulier
- la période de référence (du 01/01 au 31/01 par exemple)
- le motif d'absence : CP, RTT, maladie, formation...
dans les cellules B13 à G13

Et faire un même récap, pour plusieurs salariés.

Je ne sais faire que les totaux d'une personne sur une année.

J'ai mis quelques noms, mon tableau étant prévu pour 250 lignes.

Je compte vraiment sur votre aide.

Merci d'avance.

Bonne fin de journée.

AtTheOne · 4 Janvier 2022

Bonjour Laëtitia @LAETI-TOINOU
Bonjour le forum
et bonne année à tous

Voilà Laëtitia j'ai élaboré une nouvelle version avec ,dans l'onglet Suivi Absences, 2 lignes par salarié.
Cela m'a fait reprendre pas mal de chose, mais le résultat ne doit pas être loin de tes attentes.
Le tableau d'information sur le salarié ne contient plus de formule et est rechargé à chaque activation de la feuille, comme le planning.
J'ai essayé de déboguer mais je ne suis toujours pas à l’abri d'une coquille : teste au maximum.

Encore une fois meilleurs voeux
A bientôt
Alain

LAETI-TOINOU · 4 Janvier 2022

AtTheOne à dit:
Bonjour Laëtitia @LAETI-TOINOU
Bonjour le forum
et bonne année à tous

Voilà Laëtitia j'ai élaboré une nouvelle version avec ,dans l'onglet Suivi Absences, 2 lignes par salarié.
Cela m'a fait reprendre pas mal de chose, mais le résultat ne doit pas être loin de tes attentes.
Le tableau d'information sur le salarié ne contient plus de formule et est rechargé à chaque activation de la feuille, comme le planning.
J'ai essayé de déboguer mais je ne suis toujours pas à l’abri d'une coquille : teste au maximum.

Encore une fois meilleurs voeux
A bientôt
Alain

Bonjour Alain,

Merci pour l'énorme travail que tu réalises pour moi.
Effectivement, nous ne sommes pas loin du but.

Après quelques manipulations, je rencontre les coquilles suivantes :
- Lorsque je veux utiliser les boutons "Remise à zéro du tableau", un message me dit que les macros sont peut-être désactivées.
- J'arrive à poser des demandes, par contre, je n'arrive pas à saisir l'accord correspondant sur la ligne du dessous. Les accords ne sont possibles que si les cases de la ligne du dessus sont vides.
- Lorsqu'il y a un commentaire sur une demande ou un accord, y a-t'il moyen d'avoir un visuel ?

Peut-être que j'en demande un peu trop à EXCEL.

Merci beaucoup.

Nota : J'ai posé une autre question, hier sur le forum, en me servant la base de ce planning.

AtTheOne · 4 Janvier 2022

Bonsoir Laëtitia (@LAETI-TOINOU) bonsoir le forum

LAETI-TOINOU à dit:
- Lorsque je veux utiliser les boutons "Remise à zéro du tableau", un message me dit que les macros sont peut-être désactivées.

Oui j'avais accidentellement supprimé le code, c'est corrigé.

LAETI-TOINOU à dit:
- J'arrive à poser des demandes, par contre, je n'arrive pas à saisir l'accord correspondant sur la ligne du dessous. Les accords ne sont possibles que si les cases de la ligne du dessus sont vides.

Il y avait un BUG : le nom de la colonne Type du tableau d'absence avait été écrasée par un commentaire ("Naissance"), mais de toutes façons pour accorder une ou des absences après une demande, il faut se placer sur la ligne qui contient les demandes (celles du haut) et choisir "donner l'accord" . Les demandes accordées basculent alors sur la ligne du bas.

Sinon tu peux poser et accorder directement des absences en te plaçant sur la ligne du bas (s'il n'y a pas déjà des demandes en cours pour la période sélectionnée), j'ai fait cela pour les régularisations a posteriori et éviter de saisir la demande puis de donner l'accord en 2 étapes.

LAETI-TOINOU à dit:
- Lorsqu'il y a un commentaire sur une demande ou un accord, y a-t'il moyen d'avoir un visuel ?

J'ai ajouté un contrôle du double-clic gauche qui permet d'afficher l'information :

(bon, l'alignement ce n'est pas vraiment ça mais on comprend quand même)

Voilà. Il restera la gestion des reports en avril et peut-être un bilan en mars pour alerter les salariés qui ont trop de CP encore à prendre, deux outils que l'on peut rajouter. Peut-être dans un autre fil.

J'ai vu que tu avais eu des réponses à ton autre post, je jette un oeil après.

Amicalement
Alain

LAETI-TOINOU · 4 Janvier 2022

AtTheOne à dit:
Bonsoir Laëtitia (@LAETI-TOINOU) bonsoir le forum

Oui j'avais accidentellement supprimé le code, c'est corrigé.

Il y avait un BUG : le nom de la colonne Type du tableau d'absence avait été écrasée par un commentaire ("Naissance"), mais de toutes façons pour accorder une ou des absences après une demande, il faut se placer sur la ligne qui contient les demandes (celles du haut) et choisir "donner l'accord" . Les demandes accordées basculent alors sur la ligne du bas.

Sinon tu peux poser et accorder directement des absences en te plaçant sur la ligne du bas (s'il n'y a pas déjà des demandes en cours pour la période sélectionnée), j'ai fait cela pour les régularisations a posteriori et éviter de saisir la demande puis de donner l'accord en 2 étapes.

J'ai ajouté un contrôle du double-clic gauche qui permet d'afficher l'information :

Regarde la pièce jointe 1126518
(bon, l'alignement ce n'est pas vraiment ça mais on comprend quand même)

Voilà. Il restera la gestion des reports en avril et peut-être un bilan en mars pour alerter les salariés qui ont trop de CP encore à prendre, deux outils que l'on peut rajouter. Peut-être dans un autre fil.

J'ai vu que tu avais eu des réponses à ton autre post, je jette un oeil après.

Amicalement
Alain

Merci Alain pour ta réactivité.

Sinon tu peux poser et accorder directement des absences en te plaçant sur la ligne du bas (s'il n'y a pas déjà des demandes en cours pour la période sélectionnée), j'ai fait cela pour les régularisations a posteriori et éviter de saisir la demande puis de donner l'accord en 2 étapes.
-> Sur ce point, je me mettais sur la ligne "ACCORD", et non pas sur la ligne "DEMANDE".

J'ai ajouté un contrôle du double-clic gauche qui permet d'afficher l'information :
-> Au top.

Je vais naviguer sur le doc demain, mais là je crois que nous sommes arrivés à ce que je souhaitais.

Je te confirme ça rapidement.

Merci et bonne soirée.

Laëtitia

AtTheOne · 6 Janvier 2022

Bonsoir Laëtitia (@LAETI-TOINOU ), Bonsoir à tous.

Comme promis il y a un certain temps j'ai ajouté un mémo sur les fonctions utilisées, n'hésite pas à consulter l'aide sur les nouvelles fonctions matricielles dynamiques d'EXCEL 365 et 2021.
Dans notre cas elles sont très pratiques car elles s'ajustent dynamiquement en nb lignes et nb colonnes.
Tu les trouves dans les onglets "Suivi Absences" pour le planning et "Tables" pour les listes triées et filtrées.
J'espère que cela t'aideras à t'approprier cet outil de gestion des absences.
Bien sûr il reste le VBA, là j'ai mis pas mal de commentaires mais il faut s'approprier le langage et le modèle objet d'Excel.

N'hésite pas à poser des questions pour obtenir des éclaircissements sur tel ou tel autre point.

Je ne sais pas si ton deuxième fil avance, je vais y jeter un oeil

A bientôt
Alain

LAETI-TOINOU · 7 Janvier 2022

AtTheOne à dit:
Bonsoir Laëtitia (@LAETI-TOINOU ), Bonsoir à tous.

Comme promis il y a un certain temps j'ai ajouté un mémo sur les fonctions utilisées, n'hésite pas à consulter l'aide sur les nouvelles fonctions matricielles dynamiques d'EXCEL 365 et 2021.
Dans notre cas elles sont très pratiques car elles s'ajustent dynamiquement en nb lignes et nb colonnes.
Tu les trouves dans les onglets "Suivi Absences" pour le planning et "Tables" pour les listes triées et filtrées.
J'espère que cela t'aideras à t'approprier cet outil de gestion des absences.
Bien sûr il reste le VBA, là j'ai mis pas mal de commentaires mais il faut s'approprier le langage et le modèle objet d'Excel.

N'hésite pas à poser des questions pour obtenir des éclaircissements sur tel ou tel autre point.

Je ne sais pas si ton deuxième fil avance, je vais y jeter un oeil

A bientôt
Alain

LAETI-TOINOU · 7 Janvier 2022

Bonjour Alain, bonjour tout le monde,

1/ Concernant ce projet :
Merci pour le mémo sur toutes les formules. Je vais les décortiquer pour essayer de les reproduire.

Alain, j'ai présenté à ma hiérarchie ton fichier, qui le trouve très bien.
Bien évidemment, on me demande une info supplémentaire qui est la suivante :

Notre agence a
XX chauffeurs
XX chefs de chantier
XX Secrétaires ....

Mon Chef d'Agence souhaiterait qu'apparaisse chaque jour le nombre de personnes présentes par type de poste, par exemple :

XX chauffeurs dans nos effectifs, dont 5 absents, soit XX-5 présents
XX chefs de chantier dans nos effectifs, dont 3 absents, soit XX-3 présents....

2/ Concernant mon autre projet :
Je sèche, car je n'arrive pas à trouver les bonnes formules.

3/ Un autre projet à venir sur une gestion de paquetages :
Encore un projet qui me demande beaucoup de temps, car je ne maitrise pas EXCEL.
Mais avec de la persévérance et l'aide de la communauté, je prends goût à travailler avec ce logiciel.

Un grand merci à la communauté, et surtout à Alain qui donne beaucoup de son temps.

AtTheOne · 7 Janvier 2022

Bonsoir Laëtitia (@LAETI-TOINOU) , bonsoir à tous.

Pour la nouvelle demande, regarde ton Fil sur le prêt de matériel : on affiche dans le planning le reste du matériel disponible en fonction des Qtés totales et des Qtés réservées.
Ce n'est pas loin de "Afficher le personnel disponible en fonction de l'effectif et des absences posées".

Tu peux imaginer une deuxième feuille de suivi avec uns colonne "métier" et une colonne "effectif", qui voisinent un planning où on affiche l'effectif disponible pour chaque métier et chaque jour. La formule matricielle dynamique sera très proche de celle de ton projet de suivi de prêt de matériel.

Je te laisse commencer, tu vas progresser, mais tu peux me faire signe si tu coinces ...

Bon courage

Amicalement
Alain

LAETI-TOINOU · 7 Janvier 2022

AtTheOne à dit:
Bonsoir Laëtitia (@LAETI-TOINOU) , bonsoir à tous.

Pour la nouvelle demande, regarde ton Fil sur le prêt de matériel : on affiche dans le planning le reste du matériel disponible en fonction des Qtés totales et des Qtés réservées.
Ce n'est pas loin de "Afficher le personnel disponible en fonction de l'effectif et des absences posées".

Tu peux imaginer une deuxième feuille de suivi avec uns colonne "métier" et une colonne "effectif", qui voisinent un planning où on affiche l'effectif disponible pour chaque métier et chaque jour. La formule matricielle dynamique sera très proche de celle de ton projet de suivi de prêt de matériel.

Je te laisse commencer, tu vas progresser, mais tu peux me faire signe si tu coinces ...

Bon courage

Amicalement
Alain

Avec ce fils conducteur, je vais essayer ça ce week-end.

Je te tiens au courant, car c'est effectivement une bonne façon pour moi de progresser.

Bonne soirée.

Laëtitia

LAETI-TOINOU · 8 Janvier 2022

Rebonjour Alain,

Là, je crois que je vais m'arracher les cheveux,

1/ En tant que bonne élève, j'ai tenté la formule dont tu me parles je n'y arrive vraiment pas.
En la décortiquant, j'ai cru comprendre qu'il fallait remplacer Lignes par Colonnes, mais le résultat n'était vraiment pas au rendez-vous.

2/ Ensuite, j'ai opté pour une formule NB.SI (qui me paraissait plus abordable), mais elle ne fonctionne pas. Pour connaitre le nombre de personnes par poste, je pensais aller dans l'onglet Tables, et utiliser la fonction NB.SI, et sur l'onglet Suivi je pensais utiliser la fonction NB.SI.ENS, mais là encore, ça ne fonctionnait pas.

Je voudrais arriver à ce type de résultat :

Qu'apparaisse chaque poste, avec le nombre de personnes par poste, et qu'ensuite sous chaque date, apparaisse les effectives restant pour chaque type de poste.

L'avantage, est de pouvoir voir instantanément le nombre de personnes présentes sur un seul et même onglet.

Mais je me rends compte, que lorsque le nombre de salariés augmente, mon tableau se rapproche dangereusement des totaux que je souhaite voir apparaitre.

Je ne sais vraiment pas comment m'y prendre.

Laëtitia

AtTheOne · 8 Janvier 2022

Bonsoir Laëtitia,
Cet après-midi c'était plomberie ! Remplacement du mitigeur de la cuisine.
Le démontage n'a pas été de la tarte, pas d'accès pour le outils ni même une lame de scie. Bref ça m'a occupé, mais c'est fait.
Revenons à nos moutons...
Je pense qu'il faut créer une autre feuille car si l'effectif complet est de 250, pour voir les disponibilités il faudra descendre en bas du planning. Et il faudra gérer les variations de l'effectif complet...
Je regarde et je te donne des pistes pour avancer.
Amicalement
Alain

LAETI-TOINOU · 9 Janvier 2022

Bonjour Alain, bonjour tout le monde,

Il est 20h00, j'ai essayé d'adapter les formules, comme tu m'as conseillé de faire (malgré les explications que tu m'as laissées sur chaque fichier), mais je n'y arrive vraiment pas.

AtTheOne à dit:
Je pense qu'il faut créer une autre feuille car si l'effectif complet est de 250, pour voir les disponibilités il faudra descendre en bas du planning. Et il faudra gérer les variations de l'effectif complet...
Je regarde et je te donne des pistes pour avancer.

EXCEL a eu raison de moi.

J'ai encore besoin d'un petit coup de pouce sur ce coup là.

Merci d'avance Alain.

Bonne soirée

Laëtitia

AtTheOne · 9 Janvier 2022

Bonsoir Laëtitia (@LAETI-TOINOU) bonsoir le forum.

J'ai eu raison des fuites (occupation plomberie) !

J'ai eu raison d'Excel (occupation Laëtitia) !

J'ai ajouté l'effectif dans le tableau "_tb_Emploi" (à compléter manuellement onglet "Tables")

J'ai rajouté, à regret, dans l'onglet BdD le service et le poste.

Puis je t'ai créé dans l'onglet "Suivi Absences" une zone de surveillance des effectifs présents pour chaque date du planning.

D'abord comme tu l'avais commencé en reprenant le contenu du tableau "_tb_Emploi" mais par une formule matricielle dynamique (un filtre avec toutes les lignes !)
Puis en ajoutant, alignée avec le planning, une formule matricielle dynamique qui ressemble beaucoup à celle du projet de prêt de matériel : ça fonctionne (avec un nb.si.ens à la place du somme.si.ens)

Pour respecter ton souhait de voir le suivi de l'effectif présent sur l'onglet "Suivi Absences", j'ai modifier le VBA pour décaler automatiquement cette zone de surveillance en fonction des variations de l'effectif global.

Pour revenir sur les formules matricielles dynamiques, si tu affectes un nom à la cellule qui contient la formule ce nom suivi de # fait référence à la plage qu'occupe la formule (_X0#, _Planning# etc).

Je t'ai fait un mode opératoire pour les flèches de l'onglet Mémo.

Voilà, tu dormiras peut-être mieux si tu vois mon message ce soir.

Amicalement
Alain

LAETI-TOINOU · 10 Janvier 2022

AtTheOne à dit:
Bonsoir Laëtitia (@LAETI-TOINOU) bonsoir le forum.

J'ai eu raison des fuites (occupation plomberie) !

J'ai eu raison d'Excel (occupation Laëtitia) !

J'ai ajouté l'effectif dans le tableau "_tb_Emploi" (à compléter manuellement onglet "Tables")

J'ai rajouté, à regret, dans l'onglet BdD le service et le poste.

Puis je t'ai créé dans l'onglet "Suivi Absences" une zone de surveillance des effectifs présents pour chaque date du planning.

D'abord comme tu l'avais commencé en reprenant le contenu du tableau "_tb_Emploi" mais par une formule matricielle dynamique (un filtre avec toutes les lignes !)
Puis en ajoutant, alignée avec le planning, une formule matricielle dynamique qui ressemble beaucoup à celle du projet de prêt de matériel : ça fonctionne (avec un nb.si.ens à la place du somme.si.ens)

Pour respecter ton souhait de voir le suivi de l'effectif présent sur l'onglet "Suivi Absences", j'ai modifier le VBA pour décaler automatiquement cette zone de surveillance en fonction des variations de l'effectif global.

Pour revenir sur les formules matricielles dynamiques, si tu affectes un nom à la cellule qui contient la formule ce nom suivi de # fait référence à la plage qu'occupe la formule (_X0#, _Planning# etc).

Je t'ai fait un mode opératoire pour les flèches de l'onglet Mémo.

Voilà, tu dormiras peut-être mieux si tu vois mon message ce soir.

Amicalement
Alain

Bonjour Alain, Bonjour tout le monde,

Encore un grand merci pour ce beau travail.

Effectivement, ça fait beaucoup de lignes à consulter en fin de planning.

Peut-on (là je pense que tu vas me dire que j'exagère, mais j'ai tendance à être perfectionniste, et avant que ma hiéarchie ne me le fasse remarquer) ne filtrer que certains services et/ou postes.

Il est vrai que dans ma société, la personne qui valide les demandes de CP du personnel de chantier, n'est pas la même personne que celle qui valide le personnel administratif.

Quand on valide les absences des chauffeurs, des maçons, des chefs de chantiers.... on n'a pas besoin de connaitre le nombre de secrétaires et/ou de comptables....

Tu crois pouvoir encore m'aider sur ce dernier (je l'espère) point ?

Un grand merci d'avance puissance 1000.

Bonne journée.

Laëtitia

J'espère que vous avez passé un bon week-end.

AtTheOne · 10 Janvier 2022

Bonjour Laëtitia (@LAETI-TOINOU ) bonjour le forum,

LAETI-TOINOU à dit:
Peut-on (là je pense que tu vas me dire que j'exagère, mais j'ai tendance à être perfectionniste, et avant que ma hiéarchie ne me le fasse remarquer) ne filtrer que certains services et/ou postes.

Il est vrai que dans ma société, la personne qui valide les demandes de CP du personnel de chantier, n'est pas la même personne que celle qui valide le personnel administratif.

Quand on valide les absences des chauffeurs, des maçons, des chefs de chantiers.... on n'a pas besoin de connaitre le nombre de secrétaires et/ou de comptables....

Voilà
la formule est :

Enrichi (BBcode):

=FILTRE(_tb_Emploi;SI(_Filtre_Service="";_tb_Emploi[Service]<>"";_tb_Emploi[Service]=_Filtre_Service)*SI(_Filtre_Poste="";_tb_Emploi[Poste]<>"";_tb_Emploi[Poste]=_Filtre_Poste))

Explications :

_Filtre_Service et _Filtre_Poste étant les valeurs sur lesquelles filtrer, _tb_Emploi étant le tableau contenant la liste des emplois par service avec les effectifs, la formule en cellule _ET devient :

=FILTRE( 'la Fonction Matricielle Dynamique FILTRE
_tb_Emploi ; 'le tableau structuré filtré
SI( _Filtre_Service = "" ; _tb_Emploi[Service] <> "" ; _tb_Emploi[Service] = _Filtre_Service)
'Critère 1 : si le filtre service est vide, tous les services non vides, sinon le service filtré

* 'La multiplication des 2 critères correspond à un ET logique

SI( _Filtre_Poste = "" ; _tb_Emploi[Poste] <> "" ; _tb_Emploi[Poste] = _Filtre_Poste )
' Critère 2 : si le filtre poste est vide, tous les postes non vides, sinon le poste filtré

)

Amicalement

Modif : Oups ! j'avais oublié de déprotéger les 2 cellules modifiables.
Modif 2 : Affichage de la formule telle quelle, reprise de l'explication de la formule

Alain

Microsoft 365 Compter les cellules non vides dans un tableau selon plusieurs critères

LAETI-TOINOU

XLDnaute Occasionnel

Pièces jointes

AtTheOne

XLDnaute Accro

Pièces jointes

LAETI-TOINOU

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

Pièces jointes

LAETI-TOINOU

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

Pièces jointes

LAETI-TOINOU

XLDnaute Occasionnel

LAETI-TOINOU

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

LAETI-TOINOU

XLDnaute Occasionnel

LAETI-TOINOU

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

LAETI-TOINOU

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

Pièces jointes

LAETI-TOINOU

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

Pièces jointes

Discussions similaires