XL 2013 compter nombre d'éléments différents par rapport à 2 critères

[Asch]

Bonjour à tous,
J'espère arriver à expliquer ma demande...

Je travaille sur un tableau de suivi d'attribution de subventions à des associations (en pièce jointe et édulcoré car énorme). Je compile dans ce tableau toutes les subventions par collectivités, par année, par dispositif. Durant une année, une association (colonne A) peut être soutenue par les deux collectivités (colonne A) au titre de plusieurs dispositifs (colonne D).
J'ai régulièrement besoin de faire des stats à l'aide de ce tableau.

Aujourd'hui, j'ai besoin de savoir (par exemple) le nombre d'associations différentes soutenues par la collectivité V en 2022. Le tableau croisé dynamique en feuille 2 ne fait pas le travail correctement. En effet, une association peut être soutenue plusieurs fois dans l'année. Si elle l'est 3 fois, le TCD compte l'association 3 fois. Mon résultat n'est donc pas juste.

Je pensais à une formule complexe du type =SOMME(SI(FREQUENCE(SI(Tableau1[Collectivité]=V;EQUIV(Tableau1[Association];Tableau1[Association];0));LIGNE(Tableau1[Association])-1);1))
Evidemment, ici elle est incomplète et ne fonctionne pas car ne prend pas en compte la condition de l'année n'arrivant pas à l'intégrer.

Dans l'idéal, j'aimerai, si une formule est possible (à moins qu'il ait une autre solution + simple), que les critères "collectivité" et "années" puissent être modifiés dans des cellules de référence afin d'éviter d'écraser la formule par erreur.

Quelqu'un parmi vous aurait-il la gentillesse de m'aider ?

Bonne fin de journée à tous.

Asch

 

[chris]

Bonjour

Trucs et astuces - Tableaux croisés dynamiques - Calcul d'unicité - Formation - Archétypes synergies

 

[Asch]

Bonjour

Trucs et astuces - Tableaux croisés dynamiques - Calcul d'unicité - Formation - Archétypes synergies

Merci, cette réponse m'a bien aidée

 

[Asch]

Merci, cette réponse m'a bien aidée

bonjour,
je me permets de revenir vers vous toujours sur ma même question qu'en 2022. j'ai perdu les éléments et suis incapable d'obtenir le résultat que je recherche. Dommage, le TCD ne me permet pas de le trouver facilement car mon Excel 2013 n'a pas la fonction dans "Paramètres des champs de valeur" la fonction "Total distinct".

je n'arrive pas non plus à formuler grâce à l'exemple envoyé dans votre réponse du 6/09/2022.

auriez-vous la gentillesse de m'aider ? ou de m'orienter vers quelqu'un ?

je vous en remercie par avance.
Aline

 

[chris]

Bonjour
Est-ce une version 2013 proplus ou sinon laquelle ?

 

[cp4]

Bonsoir @Asch , @chris ,

@Asch : Je n'ai pas parfaitement compris ta problématique. Pourrais-tu nous donner le résultat exact à obtenir?
Peut-être une solution en VBA.

Bonne soirée.

 

[Asch]

Bonsoir @Asch , @chris ,

@Asch : Je n'ai pas parfaitement compris ta problématique. Pourrais-tu nous donner le résultat exact à obtenir?
Peut-être une solution en VBA.

Bonne soirée.

bonjour,

Par exemple, dans le fichier joint, en début de conversation, j'ai besoin de savoir automatiquement qu'en 2022, la collectivité V a soutenu 9 associations différentes.
=SOMME(SI(FREQUENCE(SI(Tableau1[Collectivité]=V;EQUIV(Tableau1[Association];Tableau1[Association];0));LIGNE(Tableau1[Association])-1);1))
je pense qu'il y a une solution avec cette formule en ajoutant le critère "Année". Je n'y arrive pas car je ne comprends pas très bien cette formule.

bonne journée

 

[mapomme]

Bonjour @Asch,

En ajoutant deux colonnes dans le tableau structuré :

• la première concatène les trois critères (collectivité, asso et année)
• la seconde contient une formule qui retourne 1 si le critère de la ligne apparait pour la première fois dans la colonne Critères et qui sinon renvoie 0 si le critère a déjà été rencontré.

Le TCD résultant est sur la feuille "Feuil2".

nota :

• la formule cherche le critère de la ligne EQUIV([@CollAssoAnnee];[CollAssoAnnee];0). Equiv renvoie la position du premier critère rencontré dans la colonne des données des critères.
• On compare cette valeur au numéro de la ligne qu'on traite (en ôtant le n° de ligne du titre bien sûr)
• S'il y a égalité, c'est que le critère n'a jamais été présent avant cette ligne, et donc c'est la première apparition du critère => on renvoie 1
• S'il n'y a pas égalité c'est parce que EQUIV (.....) a trouvé le critère dans les lignes au-dessus de la ligne qu'on traite. Il ne faut pas compter ce critère (c'est un doublon). On renvoie donc 0.

La formule complète est donc :
=1*(EQUIV([@CollAssoAnnee];[CollAssoAnnee];0)=LIGNE([@CollAssoAnnee])-LIGNE(Tableau1[#En-têtes]))

Errata : mis le bon fichier!

 

[Asch]

Bonjour @Asch,

En ajoutant deux colonnes dans le tableau structuré :

• la première concatène les trois critères (collectivité, asso et année)
• la seconde contient une formule qui retourne 1 si le critère de la ligne apparait pour la première fois dans la colonne Critères et qui sinon renvoie 0 si le critère a déjà été rencontré.

Le TCD résultant est sur la feuille "Feuil2".

nota :

• la formule cherche le critère de la ligne EQUIV([@CollAssoAnnee];[CollAssoAnnee];0). Equiv renvoie la position du premier critère rencontré dans la colonne des données des critères.
• On compare cette valeur au numéro de la ligne qu'on traite (en ôtant le n° de ligne du titre bien sûr)
• S'il y a égalité, c'est que le critère n'a jamais été présent avant cette ligne, et donc c'est la première apparition du critère => on renvoie donc 1
• S'il n'y a pas égalité c'est parce que EQUIV (.....) a trouvé le critère dans les lignes au-dessus de la ligne qu'on traite. Il ne faut pas compter ce critère (c'est un doublon). On renvoie donc 0.

La formule complète est donc :
=1*(EQUIV([@CollAssoAnnee];[CollAssoAnnee];0)=LIGNE([@CollAssoAnnee])-LIGNE(Tableau1[#En-têtes]))

Errata : mis le bon fichier!

merci beaucoup pour cette solution après avoir planché pendant des heures en vain.
elle fonctionne très bien avec ma vraie base de donnée.
cette aide m'est très précieuse !!! merci encore !!!