Calcul d'une ancienneté moyenne

gonz19 · 13 Mai 2017

Bonjour,

Je souhaiterais reproduire le résultat du tableau et de l'histogramme se trouvant dans le document joint, sans avoir à faire les deux colonnes intermédiaires (formule 1 et 2).

La base de données fait pour l'instant 2000 lignes et 100 colonnes. Cette base de donnée étant extraite d'un autre logiciel et servant à plusieurs fichiers récapitulatif, je voudrais ne pas être obliger d’insérer de nouvelle colonnes (en rouge). Chaque mois 200 nouvelles lignes sont ajoutées.

formule 1 : =RECHERCHEV(F2;Qualification;2;FAUX) renvoie un nom de groupe en colonne K2:K11
formule 2 : =DATEDIF(I2;AUJOURDHUI();"Y") calcul l’ancienneté
mois : en D15

résultat: =MOYENNE.SI.ENS($L$2:$L$11;$K$2:$K$11;C18;$A$2:$A$11;$D$15) renvoie l'ancienneté moyen en fonction du groupe; et du mois sélectionné.

Je sollicite votre aide pour tenter de trouver une solution.
Je vous remercie d'avance du temps que vous passerez à m'aider.

Bonne soirée

CISCO · 13 Mai 2017

Bonsoir

Tu peux faire avec

Code:

SOMMEPROD((A$2:A$11=D$15)*ESTNUM(CHERCHE(CAR(DROITE(C18;1)+96);F$2:F$11))*DATEDIF(I$2:I$11;AUJOURDHUI();"Y"))/SOMMEPROD((A$2:A$11=D$15)*(ESTNUM(CHERCHE(CAR(DROITE(C18;1)+96);F$2:F$11))))

La partie CAR(DROITE(C18;1)+96) sert à ramener un "a" à partir du terme "GROUPE 1" (C18), un "b" à partir de "GROUPE 2" (C19) et un "c" à partir du terme "GROUPE 3" (C20). Autrement dit, cela ne fonctionne que si les noms des qualifications correspondant au groupe 1 contiennent un "a" dedans (N20 et N21), ceux correspondant au groupe 2, un "b" (N22, N23 et N24), et ceux correspondant au groupe 3, un "c" (N25 et N26).

Si tu as plus de 9 groupes, cela ne fonctionnera pas tel que, puisque pour GROUPE 10, il faudrait faire avec DROITE(C18;2).

Si tu as un tableau N19:O29 plus compliqué il faudra certainement faire autrement, en mettant autre chose à la place de cette partie CAR(DROITE(C18;1)+9).

@ plus

gonz19 · 14 Mai 2017

bonjour,

Je viens de prendre connaissance de cette superbe formule.
Je devrais être capable de l'adapter dans mon tableau.
Merci beaucoup pour ton aide.

Comme tu le sous-entends dans ton post la partie de formule CAR(Droite(C18;1)+96) ne va pas le faire.
En fait, j'ai simplifié les noms de groupe et les qualifications pour l’exemple.
Les qualifications sont plutôt du type :
Aide soignante; Aide Soignante hqa -->pour le groupe "aide soignante"
Infirmière; Infirmière Anesthésiste; Infirmière b --> pour le groupe "infirmière"
Infirmière Référente SSR, Cadre de bloc, cadre kiné --> pour le groupe "cadre"
Attention quelque piège sur les groupes.

C'est pour ça que je pensais faire un tableau récap sur une autre feuille avec des cellules nommées.

Qu'en penses-tu ?

merci d'avance
@+

CISCO · 15 Mai 2017

Bonjour

Cf. une possibilité en pièce jointe, avec une formule matricielle, donc à valider avec les trois touches Ctrl+maj+entrer.
Il faut que les noms dans la colonnes F soient écrits exactement de la même manière que dans N20:N28. Le mieux serait d'y mettre une liste déroulante. De même, il faut que les termes dans la plage C18:C20 soient écrits comme dans O20:O28. La casse n'a pas d'importance.

@ plus

gonz19 · 15 Mai 2017

bonjour,

La formule à l'aire parfaite. Je suis bien conscience que les qualifications doivent être écrite exactement pareil. Ca va surement être compliquer de travailler avec des listes déroulantes mais je vais essayer de faire un tableau le plus évolutif possible sans possibilités d'erreur.

merci de ton aide précieuse.

CISCO · 15 Mai 2017

Bonjour

gonz19 à dit:
bonjour,
...Ca va surement être compliquer de travailler avec des listes déroulantes mais je vais essayer de faire un tableau le plus évolutif possible sans possibilités d'erreur.

merci de ton aide précieuse.

On peut aussi mettre une liste déroulante ne te proposant qu'une partie de la liste totale, en fonction des premières lettres que tu as écrites.

@ plus

Calcul d'une ancienneté moyenne

gonz19

XLDnaute Occasionnel

Pièces jointes

CISCO

XLDnaute Barbatruc

gonz19

XLDnaute Occasionnel

CISCO

XLDnaute Barbatruc

Pièces jointes

gonz19

XLDnaute Occasionnel

CISCO

XLDnaute Barbatruc

Discussions similaires