Discussion :

[Mathsdeb]

Bonsoir,

Je sollicite votre aide concernant un sujet un peu délicat :

Une formule de calcul ou une macro qui me permettrait de compter le nombre de fournisseurs sans les doublons en fonction de la société, de la banque, du type et de la devise.

Mon but après est que le résultat apparaisse ensuite dans mon tcd

J'ai utilisé la formule sommeprod mais je n'arrive pas à l'adapter.

Ci-joint mon fichierQUESTION DEVELOPPEUR.xlsx

Merci beaucoup pour votre aide

[mat955]

Bonjour,

Justement, un TCD te permet en quelques cliques de te donner le nombre de fournisseurs selon tous tes critères.

QUESTION DEVELOPPEUR.xlsx

[Pierre Fauconnier]

Salut.

Les pièces jointes sont rarement utiles dans un premier message...

Cela étant, je ne vois pas trop comment on pourrait fonctionner sans construction à côté des données.

Imaginons le cas suivant:



On va créer deux colonnes supplémentaires, l'une pour concaténer les valeurs des quatre premières, la seconde pour calculer le "poids" de chaque ligne.



Dans ces deux colonnes, on utilisera respectivement les formules suivantes:
=A2&B2&C2&D2 et =1/NB.SI($E$2:$E$5;E2)Dans le TCD, l'addition des poids relatifs donnera le nombre de lignes différentes.



Cela dit, sans TCD, il suffirait de modifier un peu la dernière formule pour la rendre matricielle:
=SOMMEPROD(1/NB.SI(E2:E5;E2:E5))

[papouclo]

Bonjour,
Une proposition utilisant le tableau de mathdeb sans rajout.
J'ai utilisé les noms respectifs sociétés, banques, types, fournisseurs, devises pour les plages A2:A472 , B2:B472 , ... ce qui rend , je l'espère, les formules plus compréhensibles.
En fait, dans le classeur joint, les noms ont été définis de façon dynamique au moyen de la fonction DECALER :
le nom sociétés par :

Code :

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

=DECALER(Feuil1!$A$2;;;NBVAL(Feuil1!$A:$A)-1;1)

le nom banques par :

Code :

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

=DECALER(sociétés;;1)

...

On peut obtenir le résultat cherché en écrivant en F2 la formule :

Code :

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

1
2
=SI(NB.SI.ENS($A$2:$A2;$A2;$B$2:$B2;$B2;$C$2:$C2;$C2;$E$2:$E2;$E2)=1;
SOMMEPROD(1/NB.SI(DECALER($D2;;;NB.SI.ENS(sociétés;$A2;banques;$B2;types;$C2;devises;$E2);1);DECALER($D2;;;NB.SI.ENS(sociétés;$A2;banques;$B2;types;$C2;devises;$E2);1)));"")

à recopier vers le bas.
A chaque première occurrence d'une configuration donnée - même société, même banque, même type, même devise - on obtient le nombre de fournisseurs sans doublon.
Cordialement
Claude

[78chris]

Bonjour

Pourquoi ne précisez-vous pas la version Excel ?

C'est important car cela évolue au fil des versions et la réponse n'est pas la même.

Sur 2013, on peut l'avoir directement dans le TCD...

[Pierre Fauconnier]

Salut Chris,

Sauf erreur, il faut tout de même passer par un powerpivot, créer une colonne concaténée au sein du jeu de données, utiliser la fonction DistinctCount, ce qui ramènera une colonne avec la même valeur pour toutes les lignes. Pas trop convaincu que ce soit plus simple.


Mais en TCD "natif" d'Excel, je ne vois pas trop comment faire. Tu as une idée?

[78chris]

Bonjour

Salut Chris,

Sauf erreur, il faut tout de même passer par un powerpivot, créer une colonne concaténée au sein du jeu de données, utiliser la fonction DistinctCount, ce qui ramènera une colonne avec la même valeur pour toutes les lignes. Pas trop convaincu que ce soit plus simple.
Mais en TCD "natif" d'Excel, je ne vois pas trop comment faire. Tu as une idée?

Dans 2013 cela utilise effectivement PowerPivot mais éventuellement de façon transparente :
lors de l'insertion d'un TCD, il y a en bas de la fenêtre la commande "Ajouter au modèle de données" : cela place la table dans PowerPivot
Sur les versions qui n'ont pas l'onglet PowerPivot, il y aura dans les calculs statistiques proposés dans le TCD, Distinctcount.
Dans les autres il faut demander le TCD à partir de l'interface Powerpivot mais on a ensuite les mêmes calculs proposés.

Pas besoin d'ajouter de colonne ou autre mesure explicite.

[Pierre Fauconnier]

Avec PowerPivot, il faut quand même ajouter une colonne calculée pour pour utiliser DistinctCount sur plusieurs colonnes (en fait, sur la colonne résultant de la concaténation). Je ne vois pas comment on pourrait faire sans cela.

Sans PowerPivot, j'ai beau regarder dans mon tcd, je ne vois nulle part de fonction DistinctCount.

Donc, si je résume, on doit passer par un powerpivot et ajouter une colonne calculée soit au sein du jeu de données sur lequel s'appuie le pivot soit dans la source. Actuellement, le seul gain que je vois se situe dans l'utilisation de distinctrow au sein des fonctions statistiques.

Tu peux détailler ta manoeuvre? Parce que je suis assez perplexe quand tu dis qu'on peut le faire sans ajout de colonne ou de formule explicite.

[78chris]

Re

Non, non c'est tout simple : ci-joint le fichier avec les étapes pour une version non pro plus donc sans onglet Powerpivot.

Je vérifie et joins l'autre + tard ;-)

Edit : a noter que le total fait un décompte distinct des fournisseurs en ne comptant qu'un fois ceux qui fournissent dans 2 catégories. d'où l'écart avec la somme d'un ratio calculé en amont.

Edit 2: on peut suivre le même chemin avec la version pro plus...

[Pierre Fauconnier]

Chris,

Dans ton tcd, tu comptes juste le nombre de fournisseurs distincts, pas le nombre de lignes distinctes... Tu arrives à 187. Cela signifie qu'il y a 187 valeurs différentes uniquement dans la colonne Fournisseur. Et pour arriver à ce résultat-là sans TCD, je n'ai pas besoin non plus d'une colonne supplémentaire, la formule, qui porterait alors uniquement sur la colonne fournisseur, donnerait aussi 187.

Et dans ce cas, devoir monter un TCD juste pour calculer le nombre de valeurs distinctes, je trouve que c'est un peu lourd par rapport à ma formule, et qui complique en plus l'utilisation du résultat dans d'autres formules, mais c'est une appréciation tout à fait personnelle.


Avec ma concaténation pour prendre en compte l'ensemble des colonnes (comme j'avais compris la question initiale) et la formule, on arrive à 189 lignes distinctes. Ce résultat est confirmé par l'outil de suppression des doublons de l'onglet données qui, lorsqu'il porte sur l'ensemble des colonnes, donne bien comme résultat 189 lignes restantes et pas 187.

Pour le faire sur un cas plus simple, je ne vois comment, avec ou sans tcd et sans construction de colonnes, on pourrait distinguer 3 lignes différentes dans la table suivante, là où la concaténation et le formule le permettent:



J'ai déjà retourné cette question dans tous les sens de multiples fois, sans trouver aujourd'hui une solution plus satisfaisante que celle que je préconise (en tout cas dans un classeur ouvert et sans vba)

[78chris]

Bonjour Pierre

Je ne critique aucune des solutions proposées dans ce fil.

Je suis juste repartie de la demande initiale qui était d'avoir cette information dans un TCD.

Dans les versions antérieures à 2013, j'applique la même solution que toi.

Je voulais juste montrer qu'avec 2013, il existe une solution simple intégrée dans le TCD.
Elle présente l'avantage, de s'adapter à chaque maille du TCD car le calcul dépend de la hiérarchie qu'on crée dans le TCD : ici on a le nombre de fournisseurs distincts par devise, type, banque et société mais avec les sous-totaux (que je n'ai pas affichés) et les totaux la synthèse s'adapte à chaque niveau.

Cet avantage peut aussi être un inconvénient si on ne veut pas cette adaptation : comme dans tous les calculs, il est donc important, de bien définir ce que l'on souhaite mesurer pour choisir la solution adéquate.

Pour moi le but de ces échanges est juste d'élargir les possibilités offertes... et je n'affirme en aucun cas que ma solution est la meilleure...

[Pierre Fauconnier]

Chris,

Je ne fais pas un concours non plus. Mon incompréhension de ta solution (et son rejet, ceci dit) vient du fait que nous n'avons pas la même interprétation de la question initiale, puisque tu donnes une solution qui compte les fournisseurs différents (une seule colonne) et que je donne une solution qui compte les lignes différentes (plusieurs colonnes).

Donc, nous ne donnons pas une solution différent au même problème (la preuve, nos résultats qui ne sont pas identiques), nous donnons chacun une solution à des problèmes différents. Et ta solution ne répond pas, selon moi, à la demande initiale (ce n'est pas un jugement ni une attaque personnelle, c'est juste ma compréhension de la question par rapport à ta compréhension de la question).

Et je reste donc sur ma position que je ne vois pas d'autre solution que celle que je propose (ce n'est pas une question qu'elle soit meilleure, c'est qu'elle est à mon avis la seule) pour dénombrer les lignes complètes différentes dans une table de données. Et je retiens ta solution pour ce qui est d'un dénombrement monocolonne, en maintenant qu'utiliser un tcd pour ça, c'est utiliser un bazooka pour tuer une mouche (appréciation purement personnelle et non une attaque ad hominem) .

Il restera à voir ce qu'en dira le posteur initial, notamment sur ce qu'il fallait comprendre de sa question.

[78chris]

Re

...Et je retiens ta solution pour ce qui est d'un dénombrement monocolonne, en maintenant qu'utiliser un tcd pour ça, c'est utiliser un bazooka pour tuer une mouche (appréciation purement personnelle et non une attaque ad hominem)

Mais il y avait de toute façon un TCD prévu...

Il restera à voir ce qu'en dira le posteur initial, notamment sur ce qu'il fallait comprendre de sa question.

C'est effectivement à lui de le préciser...

Bonne journée

[Pierre Fauconnier]

Re


Mais il y avait de toute façon un TCD prévu...

Bonne journée

Tout à fait, sauf qu'entre la demande formulée et le besoin réel, j'ai appris qu'il y avait parfois, voire souvent sur les forums, un monde de différences, et qu'utiliser un tcd pour récupérer une valeur scalaire ne me semble pas efficace... Sauf à répéter une valeur forcément toujours la même sur les différentes lignes, ce qui ne peut que nuire à l'analyse...

Je serais curieux de voir les cas réels dans lesquels on a besoin de cette info dans un tcd, et c'est l'intérêt des forums d'aller au delà de la question formulée pour comprendre le vrai besoin et y répondre adéquatement. Ce que je veux dire par là, c'est que le demandeur parle de TCD parce qu'il n'entrevoit peut-être que cette voie pour arriver à ses fins.

[Mathsdeb]

Bonsoir à tous les deux,

Juste Excellent !!! J'ai réussi à trouver la solution à mon problème grâce à vous.

Merci infiniment et encore un GRAND BRAVO pour ce forum.

Merci encore