Re : Valeurs distinctes avec valeurs vides
Bonsoir
Effectivement, la formule ne donnait pas le bon résultat dans tous les cas. Dans la foulée, j'ai même vu que la première partie, celle qui calcule le nombre de termes différents dans la colonne E, n'était pas parfaite (cf la pièce jointe lagire2bis). Je l'ai donc aussi modifiée.
Cf. donc en pièce jointe. A vérifier bien sûr.
Le principe :
EQUIV(x cellules;x cellules;0)=LIGNE(1:x) renvoie FAUX pour les doublons (parce que, pour un doublon y, EQUIV(y;x cellules;0) renvoie la position du 1er y dans les x cellules, et pas celle du 2ème doublon dans ces x cellules. Ex : EQUIV({"AA";"BB";"AA"};{"AA";"BB";"AA"};0) donne {1;2;1} et pas {1;2;3})
SOMMEPROD(1*EQUIV(x cellules;x cellules;0)=LIGNE(1:x)) donne le nombre de valeurs différentes, puisque les doublons comptent pour FAUX, et que 1*FAUX= 0.
Le problème ici, c'est de lister les x cellules à prendre en compte, une première fois sur la plage E1:E9, une seconde fois sur F1:F9.
En I11, pour la première partie, pour la colonne E
ESTNUM(R1:R9)*(E1:E9<>"");E1:E9;"") garde les termes différents de "" de E1:E9 sur les lignes contenant des nombres dans la colonne R, et met "" dans les autres cellules.
SI(ESTNUM(R1:R9);E1:E9);0) garde les termes de E1:E9 sur les lignes contenant des nombres dans la colonne R, et met des 0 dans les autres cellules (et pas des "" dans les autres cellules).
EQUIV(SI(ESTNUM(R1:R9)*(E1:E9<>"");E1:E9;"");SI(ESTNUM(R1:R9);E1:E9);0);0) renvoie la position des termes du premier groupe dans le second groupe.
SOMMEPROD(1*(SIERREUR(EQUIV(SI(ESTNUM(R1:R9)*(E1:E9<>"");E1:E9;"");SI(ESTNUM(R1:R9);E1:E9);0);0)=LIGNE(1:9))) compte le nombre de termes différents dans la plage E1:E9 sur les lignes contenant des nombres dans la plage R1:R9
Toujours en I11, mais pour la seconde partie, pour la colonne F, c'est la même méthode, mais il faut faire un test en plus pour comparer F1:F9 à E1:E9, sous la forme SI(ESTNA qui choisit les termes intéressants dans la plage F1:F9 non présents dans la plage E1:E9, termes différents de "", et placés sur les bonnes lignes.
SI(ESTNA(EQUIV(SI(ESTNUM(R1:R9)*(F1:F9<>"");F1:F9;"");SI(ESTNUM(R1:R9);E1:E9;"");0));F1:F9;"") garde les termes de la plage F1:F9 sur les lignes contenant des nombres dans la colonne R, différents (à cause du ESTNA) des termes de la plage E1:E9 sur les mêmes lignes, et met des "" dans les autres cellules.
SI(ESTNA(EQUIV(SI(ESTNUM(R1:R9)*(F1:F9<>"");F1:F9;"");SI(ESTNUM(R1:R9);E1:E9;"");0));F1:F9;0), idem, mais met des 0 dans les autres cellules.
EQUIV(SI(ESTNA(EQUIV(SI(ESTNUM(R1:R9)*(F1:F9<>"");F1:F9;"");SI(ESTNUM(R1:R9);E1:E9;"");0));F1:F9;"");SI(ESTNA(EQUIV(SI(ESTNUM(R1:R9)*(F1:F9<>"");F1:F9;"");SI(ESTNUM(R1:R9);E1:E9;"");0));F1:F9;0);0) renvoie la position des termes du premier groupe dans le second.
Ensuite, on procède de même, avec un test =LIGNE(1:9) pour éliminer les doublons et on les somme avec un SOMMEPROD(....) matriciel à cause des ESTNUM(...), des (...<>"") qui font intervenir des plages de plusieurs cellules, et non d'une seule cellule.
Si quelqu'un trouve plus court...
@ plus