Moyenne avec colonnes non adjacentes et

[gbagne]

Bonjour à tous,
J'ai une tâche que je n'arrive pas résoudre depuis 3 jours et là, je suis dans l'urgence.
Je souhaiterais à partir des données du grand tableau remplir le deuxième tableau plus bas. Dans la colonne "AVGCost" par exemple, je souhaite trouver une formule qui puisse calculer la moyenne par nom (Name) des plages "Cost" c'est à dire (plage E2:N57 plus col S2:S57) (colonne non contigües). Dans la colonne "AVGTotalCost", la moyenne comprenant en plus les "Elements" (plage E2:S57)
Les conditions sont les suivantes: exclure les cellules contenant des valeurs nulles, et n'utiliser que les valeurs uniques (car certains de même noms ont les mêmes valeurs (doublons).
J'ai commencé avec SOMMEPROD, combiné à Fréquence mais je m'y perds.
Quelqu'un pourrait-il me donner un coup de main svp?

Le fichier est joint.
Merci

 

[chris]

Bonjour

=SOMMEPROD(($A$2:$A$57=A61)*(GAUCHE($E$1:$S$1;4)="Cost");$E$2:$S$57)/SOMMEPROD(($A$2:$A$57=A61)*(GAUCHE($E$1:$S$1;4)="Cost"))

 

[JHA]

Bonjour à tous,

Pas certain de tout comprendre

JHA

 

[gbagne]

Bonjour

=SOMMEPROD(($A$2:$A$57=A61)*(GAUCHE($E$1:$S$1;4)="Cost");$E$2:$S$57)/SOMMEPROD(($A$2:$A$57=A61)*(GAUCHE($E$1:$S$1;4)="Cost"))

Bonjour Chris,
Merci pour la formule mais je crois qu'elle ne prend pas en compte les critères de doublons et d'élimination des valeurs nulles (cellules contenant des 0). Peut-on les y inclure?

 

[gbagne]

Bonjour à tous,

Pas certain de tout comprendre

JHA

Bonjour JHA,
Il s'agit de calculer les moyennes par nom avec les critères suivants:
1. Eliminer les 0 en calculant la moyenne
2. Eliminer les doublons (les lignes contenant les mêmes valeurs). Faire le calcul avec les valeurs uniques en quelque sorte.
Merci

 

[JHA]

Bonjour à tous

La formule proposée retire les "0" et les noms en doube, peux-tu nous montrer les doublons sur ton exemple.

JHA

 

[chris]

RE

Quelle version Excel as-tu ?

Tu as des doublons si on considère les costs mais pas les lignes entières ou je ne n'ai pas compris l'aspect doublon

 

[gbagne]

Bonjour à tous

La formule proposée retire les "0" et les noms en doube, peux-tu nous montrer les doublons sur ton exemple.

JHA

Je voulais plutôt dire les valeurs qui sont identiques dans les lignes. Cela change le resultat quand on en tient compte je crois.
fichier joint avec les valeurs repetées.

 

[chris]

RE

Tes lignes en rouge ne sont pas identiques sur les elements comme déjà dit...

Ce qui du coup ne va pas considérer la même chose pour les 2 calculs...

Quid de la version Excel

 

[gbagne]

RE

Tes lignes en rouge ne sont pas identiques sur les elements comme déjà dit...

Ce qui du coup ne va pas considérer la même chose pour les 2 calculs...

Quid de la version Excel

J'ai Excel 2016

 

[chris]

Re

2016 : on peut donc le faire aisément par PowerQuery intégré mais reste le problème de définition des doublons :
Si on dédoublonne sur la base des cost on utilise 50 lignes mais si on dédoublonne les lignes complètes on utilise les 56...

Il faut donc savoir...

 

[gbagne]

En fait c'est sur la base des Cost. Mais PowerQuery, je n'y connais pas grand chose.

 

[chris]

RE

Tu ne réponds pas à la question : dans la mesure où on doit dédoublonner et que les colonnes éléments n'ont pas de doublons, il faut soit tout garder soit définir quels éléments on garde ou pas...

PowerQuery c'est comme les formules : cela s'apprend et il faut bien commencer un jour car on l'utilise de plus en plus...

 

[gbagne]

RE

Tu ne réponds pas à la question : dans la mesure où on doit dédoublonner et que les colonnes éléments n'ont pas de doublons, il faut soit tout garder soit définir quels éléments on garde ou pas...

PowerQuery c'est comme les formules : cela s'apprend et il faut bien commencer un jour car on l'utilise de plus en plus...

Ce sont les colonnes Cost qui ont en effet des doublons qu'il faudrait dédoublonner pour le calcul de AVGCost. Dans la colonne de AVGTotalCost il faudrait juste inclure les colonne Element à partir de la formule de AVGCost.

 

[chris]

Re

Toujours pas clair pour moi : dans un cas on traite 50 lignes mais dans l'autre on a 56 lignes bien distinctes

Donc concrètement on fait la moyenne de quoi ?