Comptage valeurs/couple de valeurs uniques

Bonjour,

Je souhaiterai faire un comptage de valeurs/couple de valeurs uniques sur mon tableau à plusieurs entrées.

J'ai mis un fichier en pièce jointe, avec un exemple, et ce que je souhaiterai faire ressortir.

Idéalement sans macro, et avec des formules (matricielles ?)

Merci à celles et ceux qui prendront le temps de lire !

89exemple.xlsx (10.12 Ko)

Bonjour,

Pourquoi la Ref B n'a que 3 couleurs ?

Alors, en fait ce qui m’intéresse c'est le nombre de couleurs différentes.

Donc, pour la colonne B : Platine, Champagne, et "-" qui correspond à vierge.

Bonjour,

On commence par l'extraction des références :

=SIERREUR(INDEX($A$1:$A$10;PETITE.VALEUR(SI(NB.SI($A$14:A14;$A$2:$A$10)=0;LIGNE($A$2:$A$10);9^9);1));"")

Formule matricielle (à valider par Ctrl+Maj+Entrée)

Formule en A15, on tire vers le bas. Lorsque toutes les références sont affichées, plus d'affichage.

Jusque là, c'est simple !

Ensuite, il faut compter les matières différentes, les couleurs différentes et les couples matière-couleur différents, mais par référence. Le couple aurait pu se ramener au nombre de chaque référence, à moins qu'il puisse y avoir des couples matière-couleur identiques sous la même référence, dans le doute on l'a traité avec les deux autres.

On a donc créé 3 noms (à voir dans le gestionnaire de nom) :

  • Matière, qui est la concaténation des plages Référence et Matière
  • Couleur, qui est la concaténation des plages Référence et Couleur
  • MatCoul, qui est la concaténation des plages Référence, Matière et Couleur.
Les formules en B15, C15 et D15, respectivement.
=SI($A15<>"";SOMME(--(FREQUENCE(SI($A$2:$A$10=$A15;EQUIV(Matière;Matière;0)+1;0);LIGNE($1:$10))>0))-1;"")

=SI($A15<>"";SOMME(--(FREQUENCE(SI($A$2:$A$10=$A15;EQUIV(Couleur;Couleur;0)+1;0);LIGNE($1:$10))>0))-1;"")

=SI($A15<>"";SOMME(--(FREQUENCE(SI($A$2:$A$10=$A15;EQUIV(MatCoul;MatCoul;0)+1;0);LIGNE($1:$10))>0))-1;"")

Ces formules sont évidemment aussi matricielles...

Comme tu le constates, c'est la même formule qui diffère uniquement par la matrice d'éléments concaténés (nommée).

J'espère que ton modèle est parfaitement conforme à tes données (réelles), sinon il va te falloir l'adapter et donc assimiler rapidement les éléments de cette formule pour la comprendre.

(Petite parenthèse : généralement on compte les valeurs distinctes d'une plage en utilisant une expression 1/NB.SI(plage;plage) qui renvoie une série de valeurs dont la somme réalisée avec SOMMEPROD correspond au nombre de valeurs distinctes. Mais si NB.SI accepte n'importe quelle plage, si on lui glisse une matrice sous une autre forme, refus de la traiter ! Il faut donc procéder autrement. Toutefois, en créant autant de plages d'éléments concaténés que nécessaires, on rendrait possible l'utilisation de SOMMEPROD avec NB.SI telle qu'indiquée ci-dessus.)

Donc pour éviter de créer des colonnes supplémentaires, on a créé des matrices nommées que l'on va pouvoir tester avec EQUIV : EQUIV cherche le rang de chaque élément de la matrice dans la matrice, et évidemment si un élément se répète elle ne trouvera que le premier d'entre eux, ce qui filtre les doublons. Les plages commençant ligne2, le +1 ajouté à la matrice de résultats renvoyés par EQUIV rétablit en quelque sorte le numéro de ligne, mais en fait il est indispensable que le résultat soit supérieur à 1 pour l'utilisation qui en est faite avec FREQUENCE, donc comme en tout état de cause l'un des résultats renvoyés par EQUIV sera 1, avec +1 on assure que tous les résultats seront supérieurs. L'utilisation d'EQUIV est mise sous condition que la référence soit celle cherchée : la matrice renvoyée sera donc la valeur EQUIV +1 si la référence correspond ou 0 dans le cas contraire.

On traite cette matrice avec FREQUENCE qui va répartir les valeurs renvoyées dans les intervalles qu'on lui soumet : ici 1 à 10 (les intervalles sont délimités par la suite de nombre 1 à 10). Ce qu'on indique ce sont les délimitations, le nombre d'intervalles est supérieurs de 1 aux bornes... La borne supérieure importe peu, si elle est égale à la valeur maximale de la matrice répartie, le dernier intervalle aura toujours la valeur 0 (aucun problème, il faut surtout veiller à ce que des valeurs distinctes ne puissent être cumulées dans le dernier intervalle, ce qui fausserait les résultats). Les doublons par contre vont se cumuler dans le même intervalle. Si on fait la somme des intervalles supérieurs à 0, on aura donc le résultat cherché, qu'il faut diminuer de 1 correspondant au premier intervalles qui cumulent toutes les valeurs 0 de la matrice (précisément inférieures ou égales à 1, ce pourquoi on a éliminé la valeur 1).

Cela devrait permettre d'adapter s'il y a lieu la formule (après relecture le cas échant de l'aide sur FREQUENCE...)

Cordialement.

53madjer-exemple.xlsx (11.00 Ko)
MFerrand a écrit :

Bonjour,

On commence par l'extraction des références :

=SIERREUR(INDEX($A$1:$A$10;PETITE.VALEUR(SI(NB.SI($A$14:A14;$A$2:$A$10)=0;LIGNE($A$2:$A$10);9^9);1));"")

Formule matricielle (à valider par Ctrl+Maj+Entrée)

Formule en A15, on tire vers le bas. Lorsque toutes les références sont affichées, plus d'affichage.

Jusque là, c'est simple !

Ensuite, il faut compter les matières différentes, les couleurs différentes et les couples matière-couleur différents, mais par référence. Le couple aurait pu se ramener au nombre de chaque référence, à moins qu'il puisse y avoir des couples matière-couleur identiques sous la même référence, dans le doute on l'a traité avec les deux autres.

On a donc créé 3 noms (à voir dans le gestionnaire de nom) :

  • Matière, qui est la concaténation des plages Référence et Matière
  • Couleur, qui est la concaténation des plages Référence et Couleur
  • MatCoul, qui est la concaténation des plages Référence, Matière et Couleur.
Les formules en B15, C15 et D15, respectivement.
=SI($A15<>"";SOMME(--(FREQUENCE(SI($A$2:$A$10=$A15;EQUIV(Matière;Matière;0)+1;0);LIGNE($1:$10))>0))-1;"")

=SI($A15<>"";SOMME(--(FREQUENCE(SI($A$2:$A$10=$A15;EQUIV(Couleur;Couleur;0)+1;0);LIGNE($1:$10))>0))-1;"")

=SI($A15<>"";SOMME(--(FREQUENCE(SI($A$2:$A$10=$A15;EQUIV(MatCoul;MatCoul;0)+1;0);LIGNE($1:$10))>0))-1;"")

Ces formules sont évidemment aussi matricielles...

Comme tu le constates, c'est la même formule qui diffère uniquement par la matrice d'éléments concaténés (nommée).

J'espère que ton modèle est parfaitement conforme à tes données (réelles), sinon il va te falloir l'adapter et donc assimiler rapidement les éléments de cette formule pour la comprendre.

(Petite parenthèse : généralement on compte les valeurs distinctes d'une plage en utilisant une expression 1/NB.SI(plage;plage) qui renvoie une série de valeurs dont la somme réalisée avec SOMMEPROD correspond au nombre de valeurs distinctes. Mais si NB.SI accepte n'importe quelle plage, si on lui glisse une matrice sous une autre forme, refus de la traiter ! Il faut donc procéder autrement. Toutefois, en créant autant de plages d'éléments concaténés que nécessaires, on rendrait possible l'utilisation de SOMMEPROD avec NB.SI telle qu'indiquée ci-dessus.)

Donc pour éviter de créer des colonnes supplémentaires, on a créé des matrices nommées que l'on va pouvoir tester avec EQUIV : EQUIV cherche le rang de chaque élément de la matrice dans la matrice, et évidemment si un élément se répète elle ne trouvera que le premier d'entre eux, ce qui filtre les doublons. Les plages commençant ligne2, le +1 ajouté à la matrice de résultats renvoyés par EQUIV rétablit en quelque sorte le numéro de ligne, mais en fait il est indispensable que le résultat soit supérieur à 1 pour l'utilisation qui en est faite avec FREQUENCE, donc comme en tout état de cause l'un des résultats renvoyés par EQUIV sera 1, avec +1 on assure que tous les résultats seront supérieurs. L'utilisation d'EQUIV est mise sous condition que la référence soit celle cherchée : la matrice renvoyée sera donc la valeur EQUIV +1 si la référence correspond ou 0 dans le cas contraire.

On traite cette matrice avec FREQUENCE qui va répartir les valeurs renvoyées dans les intervalles qu'on lui soumet : ici 1 à 10 (les intervalles sont délimités par la suite de nombre 1 à 10). Ce qu'on indique ce sont les délimitations, le nombre d'intervalles est supérieurs de 1 aux bornes... La borne supérieure importe peu, si elle est égale à la valeur maximale de la matrice répartie, le dernier intervalle aura toujours la valeur 0 (aucun problème, il faut surtout veiller à ce que des valeurs distinctes ne puissent être cumulées dans le dernier intervalle, ce qui fausserait les résultats). Les doublons par contre vont se cumuler dans le même intervalle. Si on fait la somme des intervalles supérieurs à 0, on aura donc le résultat cherché, qu'il faut diminuer de 1 correspondant au premier intervalles qui cumulent toutes les valeurs 0 de la matrice (précisément inférieures ou égales à 1, ce pourquoi on a éliminé la valeur 1).

Cela devrait permettre d'adapter s'il y a lieu la formule (après relecture le cas échant de l'aide sur FREQUENCE...)

Cordialement.

Whoowo, déjà, merci vraiment d'avoir de votre temps pour développer autant votre réponse !

Je vais lire tout ça, et je vous ferai un retour rapidement.

Merci encore !

Merci, c'est exactement ce que je voulais !

Bonne journée !

Bonjour,

Bonjour Madjer, bonjour Ferrand

Comme j'ai passé mon AM dessus, je mets ma solution, et du coup, pour faire comme Ferrand, je dois y aller de mes explications (qui seront plus succinctes).

J'ai extrait de la colonne A la liste des matières, puis par la formule sommeprod(1/nb.si(), j'ai compté les éléments distinct de chaque groupe, ceux ci étant délimité par un décalage (nb de ligne = équivalent à A dans la colonne a, puis B, etc.., ce qui m'a permis d’éviter l’écueil du nb.si(), puis pour les couples, un nb.si.ens() .

57madjer2.xlsx (18.44 Ko)

Bien joué DjiDji !

Sauf erreur de ma part, pour les Matière et Couleur, il faut que ce soit trié sur la colonne A.

Bonne soirée.

Rechercher des sujets similaires à "comptage valeurs couple uniques"