Tri sans doublon d'une plage multicolonne via une formule appliquée dans un champ

[david84]

Bonjour,

je fais suite aux discussions que j'avais initiées ici ou là ou encore là sur la possibilité de trier des plages multicolonne par le biais de formules.

Si les propositions exposées dans ces discussions remplissent leur office, la question du temps de traitement revient invariablement sur le tapis dès lors que la plage à traiter est importante.

L'un des moyens d'y remédier est de valider la formule non pas dans une cellule qui sera par la suite étirée mais directement dans un champ préalablement sélectionné.

Nous avons vu dans certaines discussions récentes (dont celle-ci) que cette façon de faire permettait un gain de temps appréciable.
Cependant les solutions proposées ne sont opérationnelles que sur une plage d'une seule colonne (ou ligne).

Concernant le traitement des plages multicolonne les solutions que j'avais proposées ne sont pas applicables en l'état vue que la structure des formules, basée sur l'utilisation de la fonction MIN couplée à NB.SI(Plage;Critère)=0 oblige de fait une comparaison préalable entre deux plages : celles des arguments "plage" et "critère" de NB.SI.
C'est cette comparaison qui permet l'exclusion des valeurs en doublon.

Si l'on veut pouvoir appliquer une formule capable de faire le travail directement dans un champ il faut donc trouver une autre méthode.

Lorsque l'on travaille sur une plage d'une seule colonne l'utilisation de la fonction EQUIV permet de faire ce travail.
Le problème c'est que cette fonction ne supporte pas le "multicolonne".

Il a donc fallu trouver une autre façon de faire : c'est cette idée que je vous livre.
Je ne vous saoulerai pas avec mes explications : si vous avez des questions sur la méthode utilisée j'y répondrai bien volontiers.

Je joins deux classeurs à ce message.
Dans le 1er classeur vous trouverez d'une part trois formules déjà proposées dans les discussions déjà initiées qui traitent des tris sur une plage multicolonne :
- un tri sans doublon par ordre de colonne
- un tri sans doublon par ordre de ligne
- un tri sans doublon par ordre alphanumérique
Vous trouverez également trois nouvelles formules qui font le même travail mais qui, elles, peuvent être validées directement dans un champ.

Le 2ème classeur comporte une plage de 100 lignes sur 10 colonnes et sert à tester les différentes formules afin de se rendre compte de visu de la différence du temps de traitement entre une formule validée dans une cellule et tirée vers le bas et une formule validée une seule fois dans un champ.

Afin de vous rendre compte de la différence de temps de traitement entre les deux types de validation il est nécessaire de respecter le protocole suivant :
- ne tester qu'une formule à la fois
- la tester sur une plage de longueur équivalente : dans le classeur test la plage est de 100 cellules
- amusez-vous à ajouter puis supprimer des noms qui ne sont pas déjà présents dans la plage pour visualiser la vitesse de traitement de la formule.

NB : sur 100 cellules la différence est déjà visible (surtout concernant la comparaison entre les deux formules traitant du tri sans doublon par ordre alphanumérique) mais amusez-vous à tester sur des plages plus importantes...

Bien entendu il va sans dire qu'au-delà d'un certain seuil le recours au tri VBA est vivement recommandé mais la possibilité de valider une formule matricielle directement dans un champ m'apparaît être une solution à creuser.

Ces deux classeurs sont au format .xlsx car les formules utilisées ne peuvent être lues (et donc étudiées pour ceux et celles que cela intéresse) en l'état au format xls.
Pour ceux qui veulent les utiliser dans un classeur au format .xls il est bien entendu possible de nommer certaines parties des formules (si certains sont intéressés qu'ils le disent et je fournirai un classeur au format .xls).

Si vous avez des questions n'hésitez pas !

A+

 

[Calvus]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonsoir,

David ? Impressionnant comme d'habitude !

Bravo. Je me souviens très bien de solutions que tu m'as apportées, alors que tout le monde se demandait seulement si c'était possible.

Bon, je ne comprends toujours rien à tes formules, mais je réussis à en saisir la qualité et essaierai un jour d'en tirer la substantifique moelle.

Chapeau bas, sincèrement et merci pour ce travail.
enfin merci...pas vraiment, car je vais encore devoir reculer le moment où je devrai me mettre au vba pour compenser la lourdeur de mes fichiers....

Cordialement.
Calvus.

PS : Tibo que je regrette sincèrement de ne plus voir parmi nous, mais que je salue néanmoins, serait vraiment fier de toi.

 

[MJ13]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonjour David, Calvus


Merci David pour ces fichiers qui te font arriver au niveau des grands Maître formuliste. Le résultat est époustouflant .

C'est là, que je me dis que j'ai bien fait d'investir dans le VBA .

 

[13GIBE59]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

bonjour tout le monde,

10^5 bravos David pour tes fichiers.
Bien sûr, je ne comprends rien à tes formules, mais je les ai observées (pas étudiées !) et une question me turlupine : à quoi servent donc 10^5 et 10^2 ?

Cordialement.

JB

 

[david84]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonjour,

merci pour vos retour.

J'ai épuré certaines formules par-ci par-là (notamment quelques MOD qui ne s'imposaient pas) : le fichier MultiColMatChamp.xlsx du message 1 a été actualisé.

@JB : voilà une question qu'elle est bonne !

Le principe général est le suivant : la difficulté dans le tri d'une plage multicolonne est de pouvoir différencier une même valeur qui se retrouve à différents endroits. Si tu travailles sur une seule colonne la ligne suffit mais dans une plage multicolonne une même valeur peut se trouver non seulement dans des lignes différentes mais bien évidemment dans des colonnes différentes et donc cela ne suffit plus.
L'emploi des puissances me permet de prendre en compte cela en donnant un coefficient supérieur soit à la ligne soit à la colonne en fonction de mon besoin de manière à pouvoir ensuite trier toutes les valeurs.
Je récupère donc une matrice de lignes ou colonnes que je peux trier dans la suite de ma fonction.
Ensuite grâce à MOD je récupère le n° de ligne/colonne initial.
Et enfin je peux éliminer les valeurs en doublon.

Mais plutôt que de me perdre dans des explications peu claires je te propose de m'indiquer toi-même une formule (plutôt l'une des formules placées en colonne M, N ou O car elles sont plus simples) et je te fais un exemple qui te permettra (je l'espère) de comprendre la façon dont je procède.

A+

 

[R@chid]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonjour @ tous,
Bravo David84

 

[13GIBE59]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonjour David,

Dans un de mes fichiers, j'avais utilisé 9^9, en fait c'est pareil, non ?

JB

 

[david84]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Cela dépend, peut-être que dans ton cas on voulait simplement indiquer un nombre très grand (on l'utilise par exemple pour rechercher la dernière ligne d'une plage de valeurs numériques) tandis que là je m'en sers comme un coefficient.
A+

 

[Victor21]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonsoir, David

Superbe travail, qu'il me faudra un peu de temps pour décortiquer ...

Une question de béotien cependant (pas seulement par pure curiosité, mais aussi pour tenter de comprendre) : des nombres dans la "zone", mélangés aux prénoms, renvoient des résultats que je ne m'explique pas dans les tris alpha, tant dans la matrice que dans les cellules, alors que les tris par colonne ou par ligne fonctionnent correctement (MultiColMatChamp.xlsx).
Une explication ?

 

[Modeste]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonjour David ... et tout le monde,

En ces temps où on ne fait plus que se croiser (parfois), je ne voulais pas rester silencieux: je suis muet d'admiration!
Bon d'accord, il y a aussi cette migraine lancinante qui revient quand je scrute tes formules qui explique mon mutisme (comment le chantait Julien Clerc? "Et je veux te dédier ma migraine, mon ennui, le début de ma haine et le fond de mon orgie")

Bravo, David et merci de revenir me tourmenter Ça aussi, ça empêche de se prendre trop au sérieux ! Et merci surtout pour ceux et celles qui s'inspirent et s'inspireront longtemps de ces petits chefs-d'œuvre

---

PS: j'espère que Patrick n'a pas imaginé un instant que j'allais apporter un élément de réponse à sa douloureuse question

 

[david84]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonjour,
@Patrick :

...alors que les tris par colonne ou par ligne fonctionnent correctement (MultiColMatChamp.xlsx).
Une explication ?

Telles qu'elles sont conçues, les formules à valider dans un champ ne supportent pas les nombres, même celles classant par ligne ou par colonne contrairement à ce que tu penses (teste plus attentivement et tu constateras que même si des nombres apparaissent dans le résultat ils ne font que remplacer des valeurs textuelles et non s'ajouter à elles).

Ceci est dû au fait que la fonction NB.SI, utilisée pour classer les valeurs

NB.SI(zone;"<"&zone)

ne fait pas la distinction entre les valeurs textuelles et les nombres.

Pour le vérifier fais le test suivant :
- sélectionne une plage égale en lignes et en colonnes à la plage nommée "zone"
- rentre

=SI(zone<>"";NB.SI(zone;"<"&zone))

et valide en matriciel : tu obtiens une plage de valeurs triées dans le sens ascendant allant de 0 au nombre de données.
- entre dans zone 2 ou 3 nombres : tu t'apercevras alors que ces derniers sont également triés mais qu'il n'y a pas de différence entre le tri de ces 3 nombres et les 3 plus petites valeurs textuelles.

Autrement dit elles ne sont pas départagées, d'où le conflit.

Sur le principe on peut aisément régler cela si l'on ne veut trier que les valeurs textuelles en remplaçant zone<>"" par ESTTEXTE(zone) :

=SI(ESTTEXTE(zone);NB.SI(zone;"<"&zone))

Le problème c'est que ESTTEXTE est incroyablement lent ! Donc ça rame...et comme l'idée première est le gain de temps, les formules bien qu'opérationnelles ne remplissent pas la condition d'un temps de calcul rapide.

Il est même possible de trier valeurs textuelles et nombres avec NB.SI en prévoyant dans la condition de tester si l'on a affaire à une valeur textuelle ou à un nombre, puis en choisissant de donner aux valeurs textuelles un coefficient supérieur aux nombres :

SI(zone<>"";SI(ESTTEXTE(zone);(NB.SI(zone;"<"&zone)+1)*10^3;NB.SI(zone;"<"&zone)))

Je livre un fichier à titre d'information pour exemple tout en reprécisant bien que ces formules sont très lentes sur de grandes plages, donc hors cadre à mon avis pour l'instant (fichier nommé "MultiColChamp_Txt_Nb.xlsx" placé dans le message 1).

Si cela peut néanmoins donner des idées à certains sur la manière d'accélérer cette condition je suis preneur (j'ai testé ESTTEXTE mais également la combinaison NON et ESTNUM, T(zone)>0 mais c'est vraiment lent contrairement à zone<>"").

Donc pour résumer : en utilisant zone<>"" c'est vraiment rapide donc si l'on a dans la zone à traiter que des valeurs textuelles pas de problème. Dans le cas contraire, c'est possible mais bien trop lent donc inefficace en l'état.

@Modeste : merci l'ami ! C'est toujours un plaisir de te lire .

A+

 

[Victor21]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonjour, David

Merci d'avoir pris de ton temps pour m'expliquer en détail le fonctionnement, qui me permettra d'avancer dans la décortication de ta formule
Je m'en vais d'un coup de souris voir ton nouveau fichier

 

[david84]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Je t'en pris et n'hésite pas à poser des questions si certains points ne sont pas clairs.

Concernant le fait de ne gérer que les valeurs textuelles et étant donné que le fait de passer par des fonctions comme ESTTEXTE ralentit fortement le traitement je livre une idée : si l'on part du principe que toute valeur textuelle est supérieure d'un point de vue alphanumérique à un nombre (fut-il important), le fait de remplacer zone<>"" par zone>9^9 devrait faire sortir les nombres du traitement.

Je verrai cela plus avant demain mais cela me paraît a priori logique...à voir !

A+

 

[Victor21]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Re, David

Effectivement : >9^9 trie les cellules contenant du texte, et <9^9 ne trie que les cellules vides et celles contenant des nombres

 

[david84]

Re : Tri sans doublon d'une plage multicolonne via une formule appliquée dans un cham

Bonjour à tous, dont Rachid que je n'avais pas salué !

Effectivement : >9^9 trie les cellules contenant du texte, et <9^9 ne trie que les cellules vides et celles contenant des nombres

Exacte mais si un nombre est supérieur à 9^9 (c'est peu prévisible je te l'accorde) cela fiche le bordel !

Donc j'ai opté pour zone>=" "

Maintenant les formule proposées dans le fichier MultiColChamp_txt_Nb.xlsx me semblent suffisamment rapides pour être jugées opérationnelles (le classeur Test comporte quand même 1000 cellules réparties sur 100 lignes X 10 colonnes, donc c'est lourd pour un calcul en matriciel).

J'ai actualisé le fichier MultiColChamp_txt_Nb.xlsx et supprimé l'autre, devenu inutile puisque tout est regroupé dans ce fichier.
Vous pouvez donc tester ces formules dans le classeur test.
Si vous relevez des dysfonctionnements merci de me les communiquer.

A+