Discussion :

[Socks Wielder]

Bonjour à vous tous,

Je suis assez débutant dans excel et plus j'apprends plus je vois que je n'en sais pas beaucoup.

J'ai regardé dans les forums mais les questions semblent être plus avancées que mes connaissances... enfin.

J'ai un tableau de données avec comme première colonne les années (C15:C19) et les autres des gens Marie(D15: D19), Steve (E15:E19), Pierre (F15:F19) etc. Sous chacun j'ai des données (disons leur salaire) et les années 2010 à 2014 par exemple. Les noms se retrouvent donc sur la ligne 14.

J'aimerai calculer la moyenne de leur salaire dépendamment de l'année de début et de fin que je choisis.

Donc, j'ai une liste déroulante avec le nom de chaque personne (I13), une autre avec l'année de début (K13) et une autre avec l'année de fin (M13).
Je suis capable de calculer la moyenne pour une personne par exemple Marie avec:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(SI(C15:C19>=K13;SI(C15:C19<=M13;1;0))*D15: D19)

Y a t'il un moyen de le faire pour chaque sans devoir utiliser 25 Si pour mes 25 noms?

En espérant être assez précis.
Merci de votre aide!

[(WOLF)]

Bonjour, si vos données sont filtrées, essayez de voir du coté de la fonction sous.total qui permet de faire aussi la moyenne. Sinon un tableau croisé dynamique peu faire l'affaire et enfin la combinaison de plusieurs formules est possible mais dans ce cas je préfère avoir un fichier modèle pour tester en direct les formules.

[Promethee25]

Bonjour,

Si j'ai bien compris ton besoin (à savoir la moyenne pour une seule personne à la fois, celle précisée en case I13), cette formule matricielle devrait faire l'affaire:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOYENNE(DECALER($C$14; EQUIV(K13;$C$15:$C$19;0); EQUIV(I13;$D$14:$F$14;0); M13 - K13 + 1; 1))

Tu sembles le savoir mais je précise quand-même que c'est à valider par Ctrl+Maj+Entrée.

[Socks Wielder]

Un gros merci Promethee25, tout fonctionne. Reste juste a comprendre pourquoi et comment fonctionne la fonction Decaler!

Petite précision, si on nomme C15:C19 est-ce que ça pose problème? Par exemple: année.

Également si mes dates sont en fait sous le format mensuelles: 2001-01-31, 2001-02-28, etc.

[(WOLF)]

Je n'ai qu'un seul mot à dire...Le retour de politesse que je viens de me prendre en pleine gueule il ne m'arrivera pas deux fois. Bonne continuation.

[Promethee25]

DECALER permet de se décaler en ligne et/ou en colonne et de sélectionner le nombre de lignes et de colonnes voulues à partir de la case sur laquelle on s'est décalé.
Ici:
- on part de C14 puisque c'est la case en haut à gauche de ta table.
- Pour savoir de combien de lignes on se décale, on cherche la position de l'année inférieure (K13) dans la plage des années (C15:C19) à l'aide d'EQUIV.
- Pour savoir de combien de colonnes on se décale, on cherche la position de la personne (I13) dans la plage des années (D14:F14) à l'aide d'EQUIV.
- Le nombre de lignes à sélectionner correspond à la différence entre l'année inférieure et l'année supérieure + 1 (ex: si min = 2010 et max=2013, on a bien 4 années soit 2013-2010+1).
- Le nombre de colonnes à sélectionner est 1 puisque tu souhaites avoir le résultat pour une personne.

Il ne reste plus qu'à faire la moyenne de cette plage avec la fonction MOYENNE.

Les $ n'ont ici pas d'importance puisque tu n'étires pas la formule donc C15:C19 ou $C$15:$C$19 donneront le même résultat; tu peux également nommer cette plage si tu veux.


Si tu utilises des dates et non juste des années, ça va changer la formule, notamment le nombre de lignes qui ne sera plus égal à M13 - K13 + 1.
Si tu n'arrives pas à adapter, je peux t'aider mais il me faut plus d'infos sur le format exact de ta table:
- Est-ce que tu as une ligne par mois?
- Est-ce que par contre K13 & M13 sont bien juste les années?
- Veux-tu le salaire mensuel moyen?

[Socks Wielder]

En fait, j'avais simplifier le problème pour voir si c'était possible de le faire.

En vrai, j'ai une feuille 'Données Brutes' avec une table de 2001-01-31 à 2014-12-31 comme colonne A et 25 nom sur la ligne 2. J'utilise une autre feuille 'interface' pour la présentation seulement et une dernière pour les calculs. Mon idée première était de simplement calculer le nombre de données en mois dépendamment des dates choisis puis, par la suite, calculer la somme des données dépendamment de la sélection du nom de la première ligne. Grâce a un vecteur de 1 et de 0 multiplier par la colonne approprier pour la sélection. Enfin, je pensais diviser le total par le nombre de mois calculer plus haut.

D'où le =SOMME(SI(C15:C19>=K13;SI(C15:C19<=M13;1;0))*D15:D19) que je divisais par I15: =SOMME(SI(C15:C19>=K13;SI(C15:C19<=M13;1;0);0)).

J'ai tenté d'adapter la formule mais sans succès. j'ai 67,9 au lieu de 45,06 :p
Donc oui j'ai une ligne 2000-01-31 où chaque nom a une valeur
Ensuite ligne 2: 2000-02-28 où chaque nom à encore une autre valeur. Ainsi de suite.

Désolé (WOLF) je n'avais en fait même pas vu qu'il y avait plus d'une réponse :o. J'ai vu la formule et je me suis emballé. Je vais regarder la fonction sous.total, le tableau croisé dynamique ne m'intéresse pas vraiment ici. En fait mes données ne sont pas filtrées malheureusement elle varie et peuvent augmenter ou diminuer à chaque mois. (Plus ou moins d'heures travaillées, vacances etc)

[Promethee25]

A supposer que tu aies une ligne par mois (lignes 15 à 68 dans mon exemple) et que tu veuilles la moyenne mensuelle pour les années comprises entre l'année stockée en K13 et celle stockée en M23 (il ne s'agit donc pas de date mais d'année dans ces 2 cases; ex: 2013 & 2014), la formule matricielle suivante devrait te convenir:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOYENNE(DECALER($C$14; EQUIV(K13;ANNEE($C$15:$C$68);0); EQUIV(I13;$D$14:$F$14;0); SOMME(1*(ANNEE($C$15:$C$68)>=K13)*(ANNEE($C$15:$C$68)<=M13)); 1))

Toujours à valider avec Ctrl+Maj+Entrée

Note que si une année n'est que partiellement remplie, ça n'affectera pas la moyenne car ça ne tient pas compte du nombre de mois saisis pour une année.

Est-ce ce que tu veux?

NB1: Tu devrais utiliser un tableau structuré pour avoir des formules plus robustes et qui s'ajustent automatiquement lorsque tu saisis de nouvelles entrées/de nouveaux mois.
NB2: Si tu veux mettre des dates en K13 & M23, enlève simplement les "ANNEE" de la formule et saisis bien des dates qui sont dans la liste.

[Socks Wielder]

Je veux "simplement" la moyenne pour le nombre de mois sélectionner.

Si je sélectionne Mr X qui a comme valeur 10-20-30-40-50 pour les 5 mois de 2000-01-31 a 2000-05-31 je veux qu'il me retourne 30 comme moyenne si je sélectionne ses 5 mois. Si je sélectionne 2000-03-31 comme début et 2000-05-31 comme fin alors je ne veux que la moyenne des mois 3 à 5, donc 40 etc. Je ne veux pas tenir compte des années mais seulement des mois.

En modifiant ma formule je trouve le bon résultat mais seulement si je sélectionne comme date de fin la dernière date. Il ne se modifie que si je change la date de début.

[Promethee25]

Ben ça correspond à mon "NB2":

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOYENNE(DECALER($C$14; EQUIV(K13;$C$15:$C$68;0); EQUIV(I13;$D$14:$F$14;0); SOMME(1*($C$15:$C$68>=K13)*($C$15:$C$68<=M13)); 1))

[Socks Wielder]

Merci beaucoup pour ta patience

Je comprends un peu mieux comment ça fonctionne!
J'ai réussis à le transferer dans mon plus gros fichier sans problème. Je ne peux pas dire que je comprends entièrement mais j'y vois une certaine logique. Je vais sans doute essayer de le refaire avec d'autres données.




Merci encore