Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différentes

[Rio]

Bonjour à tous,

J'utilise Office Excel 2003.
Je recherche à obtenir le nombre de valeurs différentes d'une colonne, tout en ayant une condition dans une autre colonne.
Mon fichier :
Colonne 1 / Colonne 2
OT 44441 / OT 44441 D120
OT 44441 / OT 44441 D121
OT 44442 / OT 44442 D122
OT 44442 / OT 44442 D123
OT 44442 / OT 44442 D124

Je recherche donc la formule qui me permettra de trouver que pour l'OT 44441 il y a 2 valeurs différentes qui lui correspondent dans la colonne 2. Et de même pour l'OT 44442 il y a 3 valeurs différentes qui lui correspondent dans la colonne 2.
En gros SI(Ax="OT 44441") --> NBVAL Differentes(Colonne 2)

J'ai regardé beauoup de posts avec des formules matricielles mais je me perds entre les formules nb.si / frequence / somme conditionnelle...

Merci d'avance

Rio

 

[hoerwind]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Bonjour et bienvenu sur le forum,

Une question similaire a pourtant été posée dernièrement.

Essaie cette formule, à copier vers le bas :

=SOMMEPROD((A$1:A$5=A1)*(1/NB.SI(B$1:B$5;B$1:B$5)))

 

[CISCO]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Bonjour

A quoi sert la colonne 1 dans cette histoire ?

@ plus

P.S : Bonjour Hoerwind. Ta proposition répond à ma question. Vu comme ça, c'est trop "facile" .

 

[Rio]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Merci déjà de répondre, je me doute que d'autres posts parlent déjà de ce que je recherche ou s'en approche mais vu que j'arrive déjà pas trop à expliquer ce que je veux faire, j'ai du mal a rechercher

Bon donc la formule proposée ne marche pas car ce qui se trouve dans la colonne 1, n'est pas seule dans les cellules de la colonne 2 :
OT 44441 / OT 44441 D120

Sinon pour l'autre question, la colonne 1 sert a poser la condition.

En gros combien y a-t-il de valeurs différentes pour la cellule de la colonne 1 ?
Exemple :
OT 44441 / OT 44441 D120
OT 44441 / OT 44441 D121

Ici on a D120 et D121 donc je voudrais obtenir dans une cellule le chiffre 2

Cela pour chaque cellule de la premier colonne, j'ai 3000 lignes...

Merci

 

[hoerwind]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Re, salut Cisco,

Bizarre, pourtant chez moi cela fonctionne, vois la pièce jointe

Si tu as encore des difficultés, mets un extrait de ton fichier en pièce jointe, ce sera plus facile pour te venir en aide

 

[natorp]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Bjr Rio, Hoerwind et cisco

la formule d'hoerwind en C1 fonctionne très bien, tu peux l'adapter en C2 et la tirer vers le bas :

=SI(A2=A1;"";SOMMEPROD((A$1:A$5=A2)*(1/NB.SI(B$1:B$5;B$1:B$5))))

Cordialement, Gérard

 

[Rio]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Ok merci beaucoup pour toute votre aide, je viens de comprendre ppourquoi cela ne fonctionnait pas. J'avais mis le calcul juska la cellule 5000 mais le probleme c'est qu'il n'y avait que 3000 lignes donc des blancs qui induisait une valeur nulle pour le NB.SI.

Bon donc maintenant comment je peux faire pour mettre 65536 lignes :

=SOMMEPROD((A$1:A$65536=A1)*(1/NB.SI(B$1:B$65536;B$1:B$65536)))

En fait suivant les somaines, le nombre delignes traitées dans ce fichier sera différent... et comme j'ai entièrement automatise le fichier en VBA je vais pas demande a l'opérateur de changer le nombre dans la formule

Merci les gens

 

[hoerwind]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Re,

SOMMEPROD est une fonction matricielle, la seule que je connaisse qui permet une validation par la seule touche Entrée
Les formules matricielles n'autorisent pas de référence à des colonnes entières et sont gourmandes en temps de calcul
Deux raisons pour ne pas faire référence à des plages plus grandes que celles dont on a besoin
De plus, dans certaines circonstances, les formules matricielles ne font pas bon ménage avec des cellules vides

Je te conseillerai donc de nommer les plages de recherche de façon dynamique, par la fonction DECALER
Si les cellules vides posent problème, il faudra ajouter une condition préliminaire pour éviter les messages d'erreurs

La formule pourrait ressembler à ceci :
=SOMMEPROD((Col1=A1)*(Col1<>"")*(1/NB.SI(Col2;Col2)))

Si tu ne t'en sors pas, mets en pièce jointe un extrait du fichier qui pose problème.

 

[Rio]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Oui donc je mets en PJ le fichier avec seulement quelques données.
Donc le problèmes c'est que déjà on ne peut chsoir le nombre de cellules à l'avance.
De plus c'est beaucoup trop lourd à gérer, mon PC pro ne peut même pas le faire...
DOnc je ne sais pas si je peux intégrer quelque chose dans ma macro de calcul ??
Je sais que je peux calculer le nombre de lignes par :
Dim dm As Long
dm = Range("G65536").End(xlUp).Row

Apres comment peut on faire pour la formule ?, seule cette matricielle existe ?

Merci

 

[Rio]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Cette formule un peu longue marche mieux en terme de performance :

=SI(A2="";"";SOMME(1/NB.SI(DECALER(INDEX(A$2:A$1807;EQUIV(A2;A$2:A$1807;0));0;1;NB.SI(A$2:A$1807;A2);1);DECALER(INDEX(A$2:A$1807;EQUIV(A2;A$2:A$1807;0));0;1;NB.SI(A$2:A$1807;A2);1))))

Mon PC calcule les 1800 lignes en 3 sec. Par contre meme probleme cette matricielle ne peut me permettre de ne pas connaitre le nombre de lignes au depart !!...

 

[JBOBO]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Bonjour,
la formule d'hoerwind adapté pour un nombre de lignes aleatoires grace à la fonction indirect

=SOMMEPROD((INDIRECT("A1:A"&NBVAL(A:A))=A1)*(1/NB.SI(INDIRECT("B1:B"&NBVAL(B:B));INDIRECT("B1:B"&NBVAL(B:B)))))

 

[Rio]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Merci beaucoup, c'est parfait !

Encore un grand merci pour votre temps et je vais aller demander un PC au service info pour faire tourner ma macro !

 

[Rio]

Re : Nombre de valeurs différentes en fonction d'une condition dans 2 colonnes différ

Bonjour,

J'ai une autre question sur le même sujet :
J'utilise la formule : =SI(B985="";"";SOMMEPROD((B985=INDIRECT("B4:B"&NBVAL(O:O)))*(INDIRECT("O4:O"&NBVAL(O:O)))))
pour calculer pour chaque circuit se trouvant dans la colonne B, le volume globale lui correspondant. En effet dans la colonne B il y a des doublons de circuit.

La formule fonctionne très bien mais pour les 2 dernières lignes, le résultats est de 0 ?? Pourquoi je ne sais pas ?? C'est vraiment du a la position car j'ai essayé d'enlever la dernière ligne et la valeur de l'avant avant dernière est pasée à 0...

Si quelqu'un à une idée, ca doit pas être bien compliqué