XL 2019 Moyenne et comptage distinct selon conditions

[Micha972]

Bonsoir à tous et merci d'avance pour votre aide.

Je travaille pour une association qui a un fichier excel généré et actualisé automatiquement depuis leur backoffice.
Dans ce fichier les données sont affichées dans des tables (format tableau).

J'ai 4 colonnes : Ville / Projet / Entreprise / Montant du don

Il y a des entreprises en doublon (sur plusieurs projets et plusieurs fois par projet)
Le besoin : Obtenir le nombre moyen d'entreprises impliquées par projet et par ville en même temps

Je vois comment je pourrais faire avec un TCD intermédiaire et une moyenne.si mais ça rajoute des étapes et donc du risque. J'aimerais trouver une super formule (ou power query, ou pivot, DAX) qui me fait cette moyenne de valeurs distinctes avec ces 2 étages (ville et projet) de manière automatique.

Vous trouverez le format de sortie du fichier en PJ et un exemple de ce que je voudrais obtenir en passant par l'étape intermédiaire TCD.

Si possible j'aimerais ne pas utiliser de macro ni de formule matricielle.

Merci beaucoup à la communauté !!!

 

[chris]

Bonsoir

Ci-joint solution PowerQuery

 

[R@chid]

Bonjour et Bienvenue sur XLD,
deux propositions dans le fichier joint.
Par formules et des colonnes intermédiaires.
et une autre via Power Query.


Cordialement


Edit : Salut Chris

 

[Micha972]

Ah yes trop bien merci à vous 2 @chris et @R@chid vos propositions via power query sont top et fonctionnent parfaitement !
Merci d'avoir été aussi réactif.

J'ai 2 dernières questions du coup comme vous avez trouvé la solution rapidement :
1/ J'aurai besoin aussi d'afficher le montant moyen annuel des dons.
Les montants des dons sont affichés par année (donnée que je n'ai pas affichée dans mon exemple)

Est-ce que le plus simple est de faire 2 requêtes Power Query séparées étant donné qu'il s'agit de regroupements différents et de les joindre après sur la ville ?

2/ Est-ce que cette méthode via Power Query fonctionnera toujours une fois le fichier réimporté dans leur backoffice et extrait à nouveau avec un nouveau nom (nomfichier_dateexport) ?
En gros est-ce que cette requête currentworkbook est portable en quelquesorte ou est-ce qu'elle est directement liée au nom du fichier qui doit rester le même ?

Merci pour vos réponses !

Michaël

 

[chris]

RE

C'est par Ville et par Année ?

Peut-être plus simple de garder l'année dans la requête et faire un TCD qui montrera les 2 niveaux

 

[Micha972]

RE

C'est par Ville et par Année ?

Peut-être plus simple de garder l'année dans la requête et faire un TCD qui montrera les 2 niveaux

Oui en fait on a les montants versés par ville , par année, par projet et par entreprise.
Chaque entreprise va verser sur 1, 2 ou 3 ans.
Du coup il faudrait calculer le montant moyen annuel versé sachant que toutes les entreprises ne vont pas donner sur le même nombre d'années.
J'ai ajouté la colonne année en question dans le fichier que je joins à nouveau.

Merci pour tes éclairages !!

 

[chris]

RE

C'est quoi 1 ou 2 ou 3 ? La nième année du projet ?

C'est juste la moyenne par ville et Année indépendamment des projets et entreprises ?

CurrentWorkbook prend le tableau du classeur donc le nom importe peu

Sinon on peut faire la requête sur un fichier externe dont on saisie le chemin et le nom dans une cellule

 

[Micha972]

RE

C'est quoi 1 ou 2 ou 3 ? La nème année du projet ?

Oui c'est ça.
Et du coup ça s'affiche si l'entreprise a versé de l'argent sur cette nième année.
Du coup on cherche à savoir le montant moyen annuel versé par entreprise par ville. Indépendamment des projets. Il faudrait donc juste 2 chiffres.
D'un côté nombre d'entreprises moyen qui par projet et par ville, ça c'est bon via ta solution précédente.
Et un 2e valeur qui correspond au montant moyen constaté de don annuel par entreprise.

 

[chris]

RE

Une proposition

Si R@chid peut contrôler, ce serait bien

 

[Micha972]

Wow trop bien !

Merci beaucoup @chris , même moi en me relisant j'étais plus sûr de comprendre ce que je voulais dire.

C'est exactement ça donc du coup j'ai plus qu'à fusionner cette requête avec ta précédente sur le nombre d'entreprise moyen pour avoir un tableau complet !

Franchement c'est canon, tu m'enlèves une grosse épine du pied merci !

Bonne soirée

 

[chris]

RE

C'est déja fusionné.

Mais avec les moyennes je me méfie toujours des pondérations d'où mon souhait que R@chid bien meilleur en maths que moi, valide

 

[Micha972]

Ah je comprends. Oui en effet les moyennes d'entreprises ne me paraissent pas bonnes, elles sont différentes de ce que j'avais avec ta 1ère requête.

 

[chris]

RE
non les moyennes du nombre d’entreprise est bon : ta source a changé

Je parlais de la moyenne de Donation

Edit arf non tu as raison, c'est l'ajout des années qui démultiplie

Je reposte le fichier modifié

 

[R@chid]

Bonsoir @ tous et @ toutes,

RE

Une proposition

Si R@chid peut contrôler, ce serait bien

Qu'est-ce que Rachid pourrait dire devant sa prof en la matière
Je vais lire la question d'abord, et télécharger ton fichier pour apprendre.

 

[chris]

RE

En maths c'est toi le prof, lol

Prend le tout dernier fichier et tu me diras pour les Donations

J'ai trouvé ta formule récente à base de LET sur le forum épatante et il n'est pas certain que j'ai tout compris...
Je n'ai pas encore bien réussi à exploiter ce truc...