Moyenne à partir d'une matrice ayant sa clé filtrée sur critère d'unicité

Bonjour,

Je suis bloqué sur la résolution de ce problème depuis quelques jours et je commence à me demander si c'est finalement possible à réaliser sous excel !

j'ai un tableau de données de ce type:

je souhaite récupérer la moyenne des valeurs de la seconde colonne : 1,2,2,5 en me basant sur l'unicité de la colonne ref. -->soit un résultat de 2.5

et non la moyenne basique de cette colonne: 1,1,2,2,2,5 qui serait alors pondérée avec les occurrences de ref: 2.166

J'ai exploré différentes formules matricielles avec index et unique pour me rapprocher de ce que je cherche mais je n'arrive pas à arriver au bout !

J'espère que ces explications seront suffisamment claires pour vous permettre de partager vos solutions !

Merci d'avance pour votre aide

Val

Bonjour,

Si vous permettez l'ajout d'une 4e colonne pour vous permettre de bien comprendre.

Donc en supposant votre tableau en A1:C7

En D2, entrez

=1*(NB.SI($A$2:A2;A2)=1)

Attention aux $ ! Puis étirez vers le bas.

Cela vous permet de vérifier si la référence est unique (renvoie 1 dans ce cas).

Ensuite on va simplement faire une somme des valeurs uniques, qu'on va diviser par le nombre de valeurs uniques.

Donc pour retrouver votre résultat, entrez

=SOMMEPROD(B2:B7;D2:D7)/SOMME(D2:D7)

Et voilà !

Bon si vous voulez, vous pouvez aussi le faire sans la colonne D mais ca va vite faire une formule assez énorme, c'est plus simple avec la colonne d'aide.

Bonjour,

Essayez :

=MOYENNE(SI(LIGNE(A2:A7)=EQUIV(A2:A7;A:A;0);B2:B7;""))

À valider en matriciel pour Excel 2016

(Nota si vous avez essayez Unique c'est que vous n'êtes pas sous Excel 2016)

Bonjour,

Si tu as essayé avec la fonction UNIQUE, c'est que tu ne disposes pas d'Excel 2016.

Je propose donc :

=MOYENNE(RECHERCHEX(UNIQUE(Tableau1[ref]);Tableau1[ref];Tableau1[valeurs];;0))

Bonjour à tous !

Une autre approche ?

=MOYENNE(RECHERCHE(UNIQUE(A2:A7);A2:B7))

Merci pour vos message, j'ai tout testé et je me rends compte qu'il manque du détail dans mon énoncé initial, trop simplifié pour pouvoir appliquer les solutions partagées.

Je détaille donc un peu plus mon tableau :

Je conserve la colonne "autre" car mon tableau d'origine comporte d'autres données qui ne permettent pas de supprimer des doublons tels que pour les 2 dernières lignes.

Je rajoute la colonne site, l'objectif est toujours d'obtenir la moyenne des valeurs de colonne C (qui sont en fait des délais).

Avec ces nouvelles données, le but est toujours de retrouver le délai moyen pour la France: 15.31 (moyenne des valeurs surlignées en jaune) ou pour UK: 2.8

Les valeurs vides et les doublons (par site et par ref) sont exclues du calcul de la moyenne

désolé pour ce changement et merci d'avance !

Rebonjour,

Oui c'est mieux d'exposer le problème complètement…

En reprenant la colonne d'aide définie dans mon précédent message, vous utilisez MOYENNE.SI (MOYENNE.SI, fonction) - Support Microsoft pour vérifier le pays, puis la référence.

Votre exemple avec les valeurs surlignées est vraiment mal choisi car on ne sait pas pourquoi vous ignorez le couple {France, ref=3}, d'autant plus que si elle est à 0 ou à "vide" cela change complètement le résultat. Et vu que vous donnez la somme (15.31) au lieu de la moyenne attendue, on ne peut pas vérifier.

Bon dans tous les cas, avec la colonne d'aide et en respectant stricto sensus : Ref unique (qu'importe le pays) et pays filtré, on obtient la formule suivante :

Avec la colonne d'aide en F.

=MOYENNE.SI.ENS(C2:C7; A2:A7;"France"; F2:F7;1)

Re,

Alors :

=MOYENNE(UNIQUE(FILTRE(C2:C8;(A2:A8="France")*(C2:C8<>""))))

RE,

Selon ma première démarche :

=LET(
f;UNIQUE(FILTRE(Tableau2[ref];Tableau2[site]="France"));
r;RECHERCHEX(f;Tableau2[ref];Tableau2[délai];;0);
MOYENNE(r))

Saboh12617,

merci pour le retour rapide ! et désolé pour les manques de détails.

la valeur pour le couple france - 3 est volontairement vide (et non égal à 0)

la valeur target pour la france est bien la moyenne de 5.103, et pour UK, elle est de 2.8

en utilisant la colonne d'aide, la première ligne UK ne serait pas comptabilisée puisque le 1 est comptablisé pour la première ligne de donnée uniquement: la moyenne UK serait basée sur une seule ligne au lieu des deux. cela fonctionnerait effectivement pour le cas de la france pour ce jeu de données mais pas pour mes données totales

Donc c'est bien les références uniques par pays, et non globalement. Ok pour la cellule vide, il faut donc bien utiliser la fonction moyenne pour l'ignorer.

Par ailleurs si vous etes sur une version plus récente d'XL, les autres propositions sont probablement plus adaptées. C'est plus "lisible" comme formule.

Dans tous les cas, pour corriger ma proposition, changez la formule dans la colonne d'aide par

=(NB.SI.ENS($B$2:B2;B2;$A$2:A2;A2)=1)*(A2=$B$10)

(vous pouvez mettre "France" comme référence à une cellule bien entendu)

Puis on peut resimplifier la moyenne par

=MOYENNE.SI(E2:E8;1;C2:C8)

Afin de mieux visualiser les références, je vous met ci-après un screen (formule en anglais mais peu importe).

Bonjour à tous de nouveau !

Compte tenu des explications fournies précédemment, l'élément "ref" me semble important pour déterminer la matrice unique.

Si pour un pays donné, deux délais sont identiques mais avec des valeurs "ref" différentes, le vecteur sera constitué par les deux valeurs et non pas une seule.

Dans cette optique, une proposition à tester :

encore merci pour vos retour fructueux !

la solution de JFL fonctionne à condition que les données ref et delai soient collées : le unique se base sur les données de la lignes complète.

Dans mon cas, j'ai des données entre ces deux colonnes, ce qui fait que le unique ne fonctionne pas bien. il faudrait que le unique prenne en compte uniquement la colonne ref pour que ça fonctionne je pense :(

La solution de saboh12617 fonctionne bien mais oblige à avoir une colonne d'aide dédiée à chaque site, ce qui n'est pas pratique !

Je pense que les données partagées n'étaient pas suffisament détaillées. voici donc un extract plus conséquent.

j'ai besoin de calculer les délai1 et delai2 moyens pour chaque site, en ne comptant que les ref uniques.

des données (data1 et data2) existent entre chaque colonne de donnée nécessaires à ces moyennes

encore désolé pour ce nouveau changement :s

merci pour votre aide

Bonsoir à tous !

C'est préférable d'exposer l'intégralité des contraintes dès le départ.
Cela évite aux contributeurs d’œuvrer dans de mauvaises directions....

Une version 2 :

Bonjour,

Merci à JFL pour ta solution, ça fonctionne !

merci à tous les autres contributeurs, malgré le manque de détails de mes premiers message !

Bonne journée à vous

Bonjour à tous !

Histoire un tantinet laborieuse mais qui se termine bien....

Tant mieux !

Je vous remercie de ce retour.