Moyenne de présence sur les 12 derniers mois

Excel
1

Bonjour,

Je tente d’exploiter, l’exemple que vous fournissiez sur votre site avec une formule qui imbrique MOYENNE ET DECALER.

Mon objectif est d’avoir en fin de ligne, pour chaque membre la moyenne de présence de celui-ci sur les 12 derniers mois. Ceci implique d’une part que les noms des membres sont en début de ligne et, d’autre part que leurs présences mensuelles suivent sur les colonnes (voir mon fichier – onglet « Exemple »).

Le tableau comportera forcément 2 années, puisque par exemple, en janvier 2018 je devrai aller rechercher les 11 dernières données de 2017 (pour avoir mes 12 mois).

Tout va pour le mieux lorsque toutes les données sont remplies mais dès que des colonnes sont vides, le calcul n’est plus bon (voir la ligne 14 : il devrait me renvoyer la moyenne d’octobre 2017 à septembre 2018 soit 8 présences sur 12 et donc 66.7% et non 60%).Il décale en fait en comptant aussi les colonnes vides.

Pouvez-vous m’aider ?

Merci pour votre réponse.

Cordialement

Jean-Marie.

DECALER.xlsx (15.1 KB)

3

Bonjour,

A tester avec cette formule matricielle (Validez par les touches CTRL + MAJ + ENTREE)

=MOYENNE(DECALER(Q14;0;-12-COLONNE(INDEX(B14:P14;1;EQUIV(1;(1-ESTVIDE(B14:P14));0)));1;12))

le résultat de ESTVIDE(B14:P14) est {FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.VRAI.VRAI}
ou VRAI est 1 et FAUX est 0.
On décale de 2 VRAI par la fonction EQUIV, sur une longueur de 12 mois.

DECALER.xlsx (16,6 Ko)

2 « J'aime »
4

Bonsoir,

Un super grand merci !!

Cordialement

JM

M. Jean-Marie Ydens
snedy007@gmail.com
This e-mail is intended for the exclusive use of the recipient(s). This e-mail and its attachments, if any, may contain confidential information and/or information protected by intellectual property rights or other rights. This e-mail does not constitute any commitment except when expressly otherwise agreed in a written agreement between the intended recipient and JM Ydens.

If you receive this message by mistake, please, notify the sender with the « reply » option and delete immediately this e-mail from your system, and destroy all copies of it. You may not, directly or indirectly, use this e-mail or any part of it if you are not the intended recipient.

1 « J'aime »
5

Bonsoir,

Désolé de vous déranger encore.

Comme vous me l’aviez conseillé, j’ai testé la formule que vous m’avez fournie et je pense que j’ai crié trop tôt victoire.

Je dois sans doute m’y prendre mal mais je connais mal ces formules matricielles. Je ne demande néanmoins qu’à apprendre.

Dans le test effectué, j’ai des résultats mitigés.

Premier constat

Ligne 12 : OK il recule bien de 12 colonnes (sans compter la vide), il fait la somme des colonnes à droite (E12 :Q12) et trouve 8 donc ok 8/12 = 66,7%

Ligne 13 : Idem = OK

Ligne14 : ? Reculer de 12 nous conduit à la cellule C14 et la sommation de C14 à Q14 nous donne 9 soit 9/12 = 75%. Il me retourne 72.7% ?

Ligne 15 aussi en erreur.

Puis-je vous demander de me dire où je fais une erreur.

Deuxième constat

Si j’encode une donnée dans une colonne vide (par ex : Q12=1), le résultat en R12 ne se modifie pas.

Merci déjà pour votre commentaire.

Jean-Marie

M. Jean-Marie Ydens
snedy007@gmail.com
This e-mail is intended for the exclusive use of the recipient(s). This e-mail and its attachments, if any, may contain confidential information and/or information protected by intellectual property rights or other rights. This e-mail does not constitute any commitment except when expressly otherwise agreed in a written agreement between the intended recipient and JM Ydens.

If you receive this message by mistake, please, notify the sender with the « reply » option and delete immediately this e-mail from your system, and destroy all copies of it. You may not, directly or indirectly, use this e-mail or any part of it if you are not the intended recipient.

DECALER_TESTS.xlsx (16.2 KB)

6

Bonsoir @JMY,
Re @Mimimathy,

Une proposition:
Dans le dernier fichier joint feuille “Exemple”

En “R12”

=SOMME(SI(COLONNE(B12:Q12)>=GRANDE.VALEUR(SI(ESTNUM(B12:Q12);COLONNE(B12:Q12));12);SI(ESTNUM(B12:Q12);B12:Q12)))/12

Formule matricielle à valider avec ctrl + alt + entrée

Puis tirer vers le bas autant que nécessaire.

Cordialement.

7

Bonjour,

Ou alors, toujours en matricielle

=MOYENNE(DECALER(Q12;0;-13-COLONNE(INDEX(B12:Q12;1;EQUIV(1;(1-ESTVIDE(B12:Q12));0)));1;12))
8

Re @Mimimathy,

Sans vouloir te contredire, car j’avoue bien volontiers avoir du mal avec la fonction “DECALER” mais il y a encore un problème avec ta formule sur la ligne 13.

75% au lieu de 83,33 %

Cdlt.

9

Salut MDO
Exact,
Alors une autre approche, toujours en matricielle

=MOYENNE(INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:Q12))*COLONNE(B12:Q12))-11;1)):INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:Q12))*COLONNE(B12:Q12));1)))
1 « J'aime »
10

Bonsoir,

Merci pour cette nouvelle formule, j’avance. Elle semble bien fonctionner (je reste prudent cette fois-ci).

Le cas pratique est le suivant : un service club comprend « N » nombre de membres. Ils se réunissent 1 fois par mois sauf en juillet et en août. Le droit de vote est accordé aux membres qui ont un taux de présence de « Y% » sur les 12 derniers mois. Il faut que le secrétaire connaisse automatiquement le taux de présence adéquat lors des votes.

En annexe, le tableau où j’ai fait quelques tests qui semblent corrects (voir onglet « Exemple ».

J’avoue humblement que je ne maîtrise pas la formule.

Pouvez-vous me dire ce qui explique le « -11 » alors que l’on parle de 12 mois ? Le recul se fait-il à partir du moment où le logiciel ne trouve pas de cellule vide ? Il se trouve donc déjà sur une cellule qui contient une valeur ?

Comment devrais-je modifier ma formule si les membres se réunissent 2 fois par mois au lieu de une ?

Merci pour votre concours.

M. Jean-Marie Ydens
snedy007@gmail.com
This e-mail is intended for the exclusive use of the recipient(s). This e-mail and its attachments, if any, may contain confidential information and/or information protected by intellectual property rights or other rights. This e-mail does not constitute any commitment except when expressly otherwise agreed in a written agreement between the intended recipient and JM Ydens.

If you receive this message by mistake, please, notify the sender with the « reply » option and delete immediately this e-mail from your system, and destroy all copies of it. You may not, directly or indirectly, use this e-mail or any part of it if you are not the intended recipient.

DECALER_TEST3.xlsx (21 KB)

11

Bonjour @JMY,
Salut @Mimimathy,

Je t’avais proposé une formule différente, je te la re-propose en l’adaptant aux nouvelles données.

En “W12” toujours en Formule matricielle à valider avec ctrl + alt + entrée

=SIERREUR(SOMME(SI(COLONNE(B12:V12)>=GRANDE.VALEUR(SI(ESTNUM(B12:V12);COLONNE(B12:V12));12);SI(ESTNUM(B12:V12);B12:V12)))/12;SOMME(SI(COLONNE(B12:V12)>=GRANDE.VALEUR(SI(ESTNUM(B12:V12);COLONNE(B12:V12));NBVAL(B12:V12));SI(ESTNUM(B12:V12);B12:V12)))/12)

Tirer vers le bas autant que nécessaire.

Concernant le “-11” de la formule de @Mimimathy, oui il s’agit bien du décalage de 11 lignes vers le bas de la ligne 1, mais @Mimimathy, t’expliquera mieux que moi s’agissant de la fonction " DECALER ".

Cordialement.

1 « J'aime »
12

Bonjour à tous

Comme le dis MDO et toi-même, c’est -11 cellules depuis la première déjà concerné
Sauf que cela n’est pas du DECALER, c’est la recherche de l’adresse des cellules concernées

ex:

=ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12))-11;1)

Validé en matricielle donne $K$12

=ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12));1)

Validé en matricielle donne $V$12

et c’est ensuite la moyenne de la plage à l’aide de la fonction INDIRECT du résultat des deux formules
Assez complexe :thinking:

Je pense que ton autre demande, consiste à récupérer le même résultat même si il y a un chiffre 2 (pour 2 réunions) dans les mois

1 « J'aime »
13

Re,

si c’est le cas, voilà la petite formule :star_struck:
toujours en matricielle

=SI(NB.SI(INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12))-11;1)):INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12));1));">0")=12;NB.SI(INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12))-11;1)):INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12));1));">0")/12;NB.SI(INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12))-11;1)):INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12));1));">0")/NB.SI(INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12))-11;1)):INDIRECT(ADRESSE(LIGNE();MAX(NON(ESTVIDE(B12:V12))*COLONNE(B12:V12));1));">=0"))

DECALER_TEST3.xlsx (22,4 Ko)