XL 2019 Tableau de bord

lcouaci · 15 Janvier 2021

Salut le Forum et BONNE ANNEE à tous !

Je souhaiterais faire un tableau de bord afin d'afficher les accident du travail par année, comme sur l'image
en dessous !

Je souhaiterais récupérer mes informations : effectif et Nombre Accidents sur des feuilles "2019"; "2020"; "2021"...; "20..n.

Comment procéder pour avoir mes informations sur chaque colonne pour année "An" créer, dans mon tableau "Bilan Annuel" sur ma feuille Tableau de bord?

Effectif= l'effectif total des agents de la boite

Nombre accident de travail=maximum de "N° d'ordre", mais comment générer une numérotation automatiser pour chaque entrée? car ma formule "=rang() -1)" ne fonctionne pas

Cordialement, et Merci au forum !!!

Exl-Noob · 15 Janvier 2021

Bonjour @lcouaci et @Rouge,

"récupérer les informations effectif et nombre d'accident sur les feuilles "2019", etc."
C'est problématique, car il n'y a aucune mention du nombre de salarié sur la feuille 2019 (ni les autres).
En revanche le nombre d'accident est bien récupéré aujourd'hui avec la formule que j'ai modifiée un peu en C7:H7 :

Code:

=SIERREUR(MAX(INDIRECT("'"&Tableau1[[#En-têtes];[2019]]&"'!A:A"));"")

Idem pour le pourcentage pour éviter un message d'erreur :

Code:

=SIERREUR((C7*100)/C6;"")

Il me semble inutile de préciser l'année (colonne M des onglets 2019:2021), d'autant qu'il y a déjà des erreurs aujourd'hui en 2021

Et pour compléter la suggestion de Rouge je te propose de mettre ce code en colonne A de...

Rouge · 15 Janvier 2021

Bonjour,

Pas tout compris, le nombre de personnes dans l'entreprise est-il constant? sinon où trouver l'information?
Pour les numéros d'ordre, je suppose que cela concerne les feuilles "2019, 2020, 2021", si c'est bien cela,
dans la cellule A2 de chaque feuille, écrivez ceci
=LIGNE()-1
et tirer la formule vers le bas

Cdlt

Exl-Noob · 15 Janvier 2021

Bonjour @lcouaci et @Rouge,

"récupérer les informations effectif et nombre d'accident sur les feuilles "2019", etc."
C'est problématique, car il n'y a aucune mention du nombre de salarié sur la feuille 2019 (ni les autres).
En revanche le nombre d'accident est bien récupéré aujourd'hui avec la formule que j'ai modifiée un peu en C7:H7 :

Code:

=SIERREUR(MAX(INDIRECT("'"&Tableau1[[#En-têtes];[2019]]&"'!A:A"));"")

Idem pour le pourcentage pour éviter un message d'erreur :

Code:

=SIERREUR((C7*100)/C6;"")

Il me semble inutile de préciser l'année (colonne M des onglets 2019:2021), d'autant qu'il y a déjà des erreurs aujourd'hui en 2021

Et pour compléter la suggestion de Rouge je te propose de mettre ce code en colonne A de chaque feuille 2019:2021 :

Code:

=SI(ESTTEXTE([Service]);LIGNE()-1;"")

A suivre.

lcouaci · 15 Janvier 2021

Rouge à dit:
Bonjour,

Pas tout compris, le nombre de personnes dans l'entreprise est-il constant? sinon où trouver l'information?
Pour les numéros d'ordre, je suppose que cela concerne les feuilles "2019, 2020, 2021", si c'est bien cela,
dans la cellule A2 de chaque feuille, écrivez ceci
=LIGNE()-1
et tirer la formule vers le bas

Cdlt

Merci, avec mes recherche sur la fonction ligne j'ai pu trouver cette information; et cette partie de mon projet est réglée.
Ma préoccupation, c'est comment rapatrier les informations des autres feuilles, et éventuellement des nouvelles feuilles à venir, sur ma seule feuille "tableau de bord" ?

Grand merci à vous, pour votre contribution !

Exl-Noob · 15 Janvier 2021

C'est déjà fait en fait.
Tu ne vois pas les formules après avoir enlevé le verrouillage de la feuille ?
Si tu créé la feuille 2022 sur la base de la feuille 2021 tu verras que le tableau se met à jour.

lcouaci · 15 Janvier 2021

Exl-Noob à dit:
Bonjour @lcouaci et @Rouge,

"récupérer les informations effectif et nombre d'accident sur les feuilles "2019", etc."
C'est problématique, car il n'y a aucune mention du nombre de salarié sur la feuille 2019 (ni les autres).
En revanche le nombre d'accident est bien récupéré aujourd'hui avec la formule que j'ai modifiée un peu en C7:H7 :

Code:

=SIERREUR(MAX(INDIRECT("'"&Tableau1[[#En-têtes];[2019]]&"'!A:A"));"")

Idem pour le pourcentage pour éviter un message d'erreur :

Code:

=SIERREUR((C7*100)/C6;"")

Il me semble inutile de préciser l'année (colonne M des onglets 2019:2021), d'autant qu'il y a déjà des erreurs aujourd'hui en 2021

Et pour compléter la suggestion de Rouge je te propose de mettre ce code en colonne A de chaque feuille 2019:2021 :

Code:

=SI(ESTTEXTE([Service]);LIGNE()-1;"")

A suivre.

Merci,
oui en effet, les effectifs trop changeant c'est pourquoi j'ai préféré qu'on inscrivent chaque année, dans "Tableau de bord".
Mais là n'est pas mon souci.
Aussi (colonne M des onglets 2019:2021) est inutile comme suggérer.

Juste vous dire un grand merci, pour votre contribution !

Du coup, mon projet est résolu !!!

lcouaci · 15 Janvier 2021

Exl-Noob à dit:
C'est déjà fait en fait.
Tu ne vois pas les formules après avoir enlevé le verrouillage de la feuille ?
Si tu créé la feuille 2022 sur la base de la feuille 2021 tu verras que le tableau se met à jour.

Si, si ! je les vois. Et de plus je viens de tester, ça marche !

Merci, mon projet résolu grâce à toi et les autres(#Rouge) qui ont bien voulu m'aider !!

lcouaci · 16 Janvier 2021

Exl-Noob à dit:
Bonjour @lcouaci et @Rouge,

"récupérer les informations effectif et nombre d'accident sur les feuilles "2019", etc."
C'est problématique, car il n'y a aucune mention du nombre de salarié sur la feuille 2019 (ni les autres).
En revanche le nombre d'accident est bien récupéré aujourd'hui avec la formule que j'ai modifiée un peu en C7:H7 :

Code:

=SIERREUR(MAX(INDIRECT("'"&Tableau1[[#En-têtes];[2019]]&"'!A:A"));"")

Idem pour le pourcentage pour éviter un message d'erreur :

Code:

=SIERREUR((C7*100)/C6;"")

Il me semble inutile de préciser l'année (colonne M des onglets 2019:2021), d'autant qu'il y a déjà des erreurs aujourd'hui en 2021

Et pour compléter la suggestion de Rouge je te propose de mettre ce code en colonne A de chaque feuille 2019:2021 :

Code:

=SI(ESTTEXTE([Service]);LIGNE()-1;"")

A suivre.

salut,

Juste profiter de toi à nouveau
alors dans ton code =SIERREUR(MAX(INDIRECT("'"&Tableau1[[#En-têtes];[2019]]&"'!A:A"));"") veux tu m'expliquer pas à pas la référence dans la fonction INDIRECT =("'"&Tableau1[[#En-têtes];[2019]]&"'!A:A"));"") car je connais pas cette fonction. A dire vrai, je la découvre avec toi.

Est ce qu'il n y a pas plus simple de faire cette fonction dans mon cas ?

De même pour ton code =SI(ESTTEXTE([Service]);LIGNE()-1;"")

je trouve =ligne()-1 est plus simple

Exl-Noob · 18 Janvier 2021

Bonjour @lcouaci,

Pour faire référence à une valeur inscrite dans un autre onglet il faut faire un lien de la forme :

Code:

='2020'!B5

On met le nom de l'onglet entre guillemet simple ('2020') + point d'exclamation (!) + référence de la cellule (B5).
Donc =INDIRECT('2020'!B5) fonctionne exactement comme ='2020'!B5
Mais pour qu'Excel interprète des données si on les génère dynamiquement, on doit l'intégrer dans une formule INDIRECT() qui va "lire" le contenu qui se trouve entre parenthèse et s'y rendre pour en extraire les données.
Dans la formule, au lieu d'indiquer en dur la valeur de l'année, je vais la chercher dans le tableau (Tableau1[[#En-têtes];[2019]]). Ceci est créé dynamiquement par Excel, même si on l'impression que c'est statique.
Le signe & sert uniquement à concaténer des éléments entre eux sans utiliser la formule CONCATENER(), trop lourde :

Code:

"bon"&"jour" = "bonjour"

Donc avec cette formule en C7 je vais chercher les valeurs dans la colonne A:A de l'onglet en référence :

Code:

INDIRECT("'"&Tableau1[[#En-têtes];[2019]]&"'!A:A") = '2019'!A:A

Ensuite, tu proposes de simplifier

Code:

=SI(ESTTEXTE([Service]);LIGNE()-1;"")

Tu as raison, le résultat est le même s'il s'agit de numéroter les lignes. En apparence. Car si tu recopies ta formule dans les cellules A2 à A40, tu vas avoir une valeur maximum à 39 (en A40), et elle va apparaître dans ton tableau de synthèse puisqu'on prend la valeur maximale présente dans la colonne A.
Pour contourner cela, je n'affiche le numéro de la ligne que lorsqu'une valeur est remplie dans la cellule adjacente (colonne service), et rien sinon. Du coup, si je recopie ma formule dans les cellules A2 jusqu'à A40, le tableau de synthèse ne va compter que les lignes ayant été complétées à minima sur le service concerné.

Mais tu pourrais aussi souhaiter ne comptabiliser que les lignes d'accident de travail suffisamment renseignées. Et définir que "suffisamment" signifie que tu as pu compléter au moins 15 cellules sur les 17 colonnes que compte ton tableau. Tu pourrais alors transformer la formule ainsi :

Code:

=SI(NBVAL(B2:R2)>=15;LIGNE()-1;"")

A suivre.

lcouaci · 18 Janvier 2021

Exl-Noob à dit:
Bonjour @lcouaci,

Pour faire référence à une valeur inscrite dans un autre onglet il faut faire un lien de la forme :

Code:

='2020'!B5

On met le nom de l'onglet entre guillemet simple ('2020') + point d'exclamation (!) + référence de la cellule (B5).
Donc =INDIRECT('2020'!B5) fonctionne exactement comme ='2020'!B5
Mais pour qu'Excel interprète des données si on les génère dynamiquement, on doit l'intégrer dans une formule INDIRECT() qui va "lire" le contenu qui se trouve entre parenthèse et s'y rendre pour en extraire les données.
Dans la formule, au lieu d'indiquer en dur la valeur de l'année, je vais la chercher dans le tableau (Tableau1[[#En-têtes];[2019]]). Ceci est créé dynamiquement par Excel, même si on l'impression que c'est statique.
Le signe & sert uniquement à concaténer des éléments entre eux sans utiliser la formule CONCATENER(), trop lourde :

Code:

"bon"&"jour" = "bonjour"

Donc avec cette formule en C7 je vais chercher les valeurs dans la colonne A:A de l'onglet en référence :

Code:

INDIRECT("'"&Tableau1[[#En-têtes];[2019]]&"'!A:A") = '2019'!A:A

Ensuite, tu proposes de simplifier

Code:

=SI(ESTTEXTE([Service]);LIGNE()-1;"")

Tu as raison, le résultat est le même s'il s'agit de numéroter les lignes. En apparence. Car si tu recopies ta formule dans les cellules A2 à A40, tu vas avoir une valeur maximum à 39 (en A40), et elle va apparaître dans ton tableau de synthèse puisqu'on prend la valeur maximale présente dans la colonne A.
Pour contourner cela, je n'affiche le numéro de la ligne que lorsqu'une valeur est remplie dans la cellule adjacente (colonne service), et rien sinon. Du coup, si je recopie ma formule dans les cellules A2 jusqu'à A40, le tableau de synthèse ne va compter que les lignes ayant été complétées à minima sur le service concerné.

Mais tu pourrais aussi souhaiter ne comptabiliser que les lignes d'accident de travail suffisamment renseignées. Et définir que "suffisamment" signifie que tu as pu compléter au moins 15 cellules sur les 17 colonnes que compte ton tableau. Tu pourrais alors transformer la formule ainsi :

Code:

=SI(NBVAL(B2:R2)>=15;LIGNE()-1;"")

A suivre.

Salut,
J'ai bien pris note de ton explication et grand merci à toi . Ça va maintenant !

Cordialement..

Exl-Noob · 19 Janvier 2021

Du coup, tu peux sans doute marquer le sujet comme clos.

lcouaci · 23 Janvier 2021

Exl-Noob à dit:
Du coup, tu peux sans doute marquer le sujet comme clos.

Salut,
justement je voulais le faire; mais ici je ne sais pas comment m'y prendre. tu veux m'aider, s'il te plait ?

soan · 23 Janvier 2021

Bonjour lcouaci, le fil,

pour marquer le sujet comme clos, tu dois cliquer sur la coche du post
qui a résolu ton exo Excel ; ici, ça a l'air d'être le post #3 d'Exl-Noob (salut) ;
avant, on pouvait mettre en résolu plus d'un post mais plus maintenant ;
c'est bien dommage !

le dont j'ai parlé est en fait dans un rond gris, sur le bord droit du post (sous un chiffre) ;
le rond deviendra vert quand tu auras cliqué dessus ; bord droit : sera en vert également.

soan

lcouaci · 23 Janvier 2021

KUKU,

Merci du coup de main.

Et surtout bon weekend!

XL 2019 Tableau de bord

lcouaci

XLDnaute Junior

Pièces jointes

Exl-Noob

Rouge

XLDnaute Impliqué

Exl-Noob

Guest

Pièces jointes

lcouaci

XLDnaute Junior

Exl-Noob

Guest

lcouaci

XLDnaute Junior

lcouaci

XLDnaute Junior

lcouaci

XLDnaute Junior

Exl-Noob

Guest

lcouaci

XLDnaute Junior

Exl-Noob

Guest

lcouaci

XLDnaute Junior

soan

XLDnaute Barbatruc

lcouaci

XLDnaute Junior

Discussions similaires