Comparer deux plages excel rapidement [Résolu/Fermé]

Signaler
-
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
-
Bonjour,

Je suis à la recherche d'un code VBA pour une fonction qui permette de comparer 2 plages excel, comme suit :

PlageA =A1:B20000

PlageB =C1:D20000

RANGVAL = le rang de l'ordre d'apparition de la valeur en différence

fonction = DIFFTAB(PlageA;PlageB;RANGVAL)

En cellule E1, je pose ma fonction "DIFFTAB(PlageA;PlageB;LIGNE($A1))" et je tire vers le bas.
Dans E1, E2 et le suite, le résultat se présente sous la forme =>

* si la valeur en différence appartient à la plage A = "(A) -valeur-"
* si la valeur en différence appartient à la plage B = "(B) -valeur-"
* si aucune ou plus aucune la valeur en différence = "???".

J'ai fait deux fonctions :

1- par comparaison de tableau avec en prime la possibilité de choisir soit à la valeur égale soit valeur recherchée contenue dans la plage de recherche,

2- comparaison avec la commande MATCH de la plage A puis B.

Elles fonctionnent toutes les 2. Mon problème est que lorsque je le fais sur un gros volume - 20000 lignes - cela n'en finit pas de calculer au point qu'il me faut couper l'exécution d'Excel.

Je me suis demandé si la solution n'était pas de calculer les différences et les restituer dans une plage d'accueil prédéfinies car mes fonctions présentes l'inconvénient qu'elles recalculent tout à chaque ligne.

C'est un besoin courant dans mon travail. Il y a bien la solution de la commande EQUIV + INDEX mais il faut les mettre au point. De plus, EQUIV ne permet pas de le faire sur plusieurs colonnes (Ex: EQUIV("TOTO";A1:D2000;0)), certains formats de cellules posent problème (la valeur recherchée est bien dans la plage de recherche mais EQUIV ne la trouve pas - vérification faite par CTRL+F cela marche-, gérer si EQUIV échoue par ESTERREUR.

Dans mon cas, c'est surtout la rapidité d'une fonction qui est mon vrai problème car du volume.

Cordialement.

2 réponses

Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
Bonjour,

tu ne veux pas déposer un classeur avec 2-3 exemples , résultat attendu et le comment ? Que doit faire cette fonction personnalisée ?

Parce que pour comprendre si la valeur en différence appartient à la plage A = "(A) -valeur-" ... Ca ne parle qu'à toi...

Et pourquoi pas un sub qui traite toute la plage ?
eric

En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
28s c'est encore beaucoup je trouve.
Quel processeur et combien de ram ?

quand dois-je décocher les dates JMA précisément
Si, dans l'extraction, il n'y a pas de dates au format français jour/mois/année tu peux décocher.
Sinon certaines se retrouvent au format anglais avec jour et mois inversés et nécessitent un traitement supplémentaire.

J'avais détecté que c'était le contrôle RefEdit qui est bogué.
J'essaie de faire autrement pour m'en passer mais il va me falloir du temps disponible pour le faire.
En attendant voici une version avec raz des refEdit, mis également sur sélection présent/absent.
+ surlignage opérationnel : https://www.cjoint.com/?DDtxNOiLzvP
eric
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
Suite...
Nouvelle version sans les refedit, et donc un fonctionnement plus correct : https://www.cjoint.com/c/DDunlyvr5m4
Plus traitement modifié pour un gain de temps de 30% environ.
Modif assez profondes, donc à tester en long, en large et en travers.

eric

PS: à-priori les dates réagissent mieux, je laisse quand même l'option 'Corriger les dates JMA' au cas ou, mais désactivée par défaut.
Bonsoir Eric,

Excellent travail. Tu tiens LA SOLUTION : nickel.

J'ai testé en long et large la nouvelle version.

Comparaison "Absents" ou "Présents" = 7,8 à 8,3 secondes = excellent.
Comparaison "Absents" avec couleur = 7,4 secondes = excellent.
Comparaison "Présents" avec couleur = 2,5 à 2,6 secondes = excellent.

J'ai fait plusieurs tests avec permutation de choix.

Retours tests :

1 - à la première utilisation avec couleur, la couleur est en fond noir,
2 - lorsque l'on procède à la comparaison avec couleur, à la comparaison suivante avec couleur + permutation - passer de "Absents" à "Présents" (ou inverse), les cellules non sélectionnées prennent la couleur de la comparaison précédente avec couleur,
3 - les comparaisons de "Absents" ou "Présents", en cochant "corriger les dates JMA", le temps prend entre 25 et 26 secondes,
4 - le test avec "Supprimer" est très long (j'ai scratché).

Configuration :

Performances Windows 4,6
Processeur : AMD Athlon (tm) 64 X2 Dual Core Processor 3600+ 1,99 GHz
RAM : 2 Go
Type de système : 32 bits

Me concernant, la solution est très bonne. Le choix avec "supprimer" est pour moi moins important.

Espérant avoir été un bon testeur,

Cordialement.
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
Bon, on arrive à des temps raisonnables :-)

Retours tests :
1) ok, je crois voir l'oubli.
2) effectivement j'ai fait le choix de ne pas modifier l'environnement des cellules non sélectionnées. J'ajouterai éventuellement le choix à faire par l'utilisateur.
3) là on aura du mal à faire mieux. Obligé de ré-écrire les données une par une en cellule et c'est très long. Mais je ne suis pas sûr que cette option soit encore utile (?)
C'est un peu pareil pour la couleur présents/absents : ça dépend fortement du nombre de cellules à traiter.
4) ouais. J'ai mis l'option vite fait qu'elle soit présente dès le départ. Vue la technique utilisée ça ne m'étonne pas. Ca sera nettement mieux à la prochaine version.

Prochaine version qu'on pourra appeler Deluxe si j'arrive au bout d'une nouvelle idée ;-)
Lundi ou mardi, je travaille par petits bouts.

eric
Bonjour Eric,

Je te laisse juge des améliorations à apporter - sans que tu es aucune obligation.

Pour moi, dans l'état, le but est d'ores et déjà atteint. Bravo pour les résultats obtenus.

Dès la semaine prochaine, je compte en faire usage dans un contexte professionnel.

Re-mille mercis.

Cordialement.
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
Bonjour,

Je te laisse juge des améliorations à apporter - sans que tu es aucune obligation.
C'est bien comme ça que je l'entendais ;-)
Correction des anomalies que tu as signalées et amélioration du temps de traitement des suppressions.
Je te laisse découvrir l'oeuf de pâques surprise ;-) Pas besoin d'explication sur cette nouvelle fonctionnalité qui, je pense, pourra t'intéresser.

Là ça touche à sa fin. J'espère que je n'ai pas ajouté de régressions. Maintenant plus d'évol, juste des corrections des anomalies que tu pourras détecter.
https://www.cjoint.com/c/DDvvIDwWTU0

eric
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
Bonjour,

Oui, tu peux mettre en résolu, en haut vers ton titre.
Il reste des passages qui font un peu bricolage suite aux modif pour dysfonctionnements sur 2010 que je dois reprendre car ils ne me plaisent pas, ça sera un peu plus tard.
Et il reste sans doute des bugs...

eric
Bonsoir Eric,

Je vais mentionner la question comme résolue.

Je te remercie pour toutes démarches.

A titre purement informel, deux cas de figures (j'ai du occulter quelques tests)
- si l'on choisit deux colonnes dans "de" - A:B par exemple et deux colonnes dans "dans" - C:D par exemple en résultat colonne E, quand B est en différence dans C:D, il donne la référence de A systématiquement,
- autre cas : si nous avons deux colonnes - A:B du Feuil1 par exemple = A 80000 lignes B=15000 lignes et deux colonnes - C:D Feuil2 par exemple = C 15000 lignes D=80000 lignes soit l'inverse du Feuil1, quand nous comparons "de" Feuil2!B:B, "dans" Feuil1!A:A avec en résultat colonne Feuil2!D:D, - (nous comparons donc la même chose, on s'attend à un résultat vide) - il trouve en différence les valeurs portant égales à partir de la 150001 lignes sur les 80000 lignes.

Je te souhaite bonne continuation.

Cordialement.
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
Bonsoir,

1) quand B est en différence dans C:D, il donne la référence de A systématiquement,

C'est normal, c'est ce que j'ai prévu.
On compare 2 champs, mais on n'est pas obligé d'avoir les 2 champs en résultat.
Si on en veut qu'un seul c'est le 1er qui est retourné.
Si c'est le 2nd qui t'intéresse le plus simple c'est de mettre 2 colonnes et de supprimer celle qui ne t'intéresse pas. Le demander à chaque fois deviendrait lourd.

2) ça doit être un bug, il va falloir que je me mette en situation pour constater.
J'essaie de voir ça demain, mais j'aurais d'autres choses avant.

eric
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
Bonjour,

J'ai un peu de mal à dégager suffisamment de temps mais je ne t'oublie pas.
eric
Messages postés
23795
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 janvier 2021
6 557
Bonjour,

Ca y est, j'ai pu reprendre la partie qui ne me plaisait qu'à moitié.
Je n'ai pas pu reproduire ton 2nd pb signalé, On est bien d'accord que toutes les plages sont dans le même classeur ?
Car si tu repars d'un profil, je n'y stocke pas le nom du classeur, juste les références des plages.
Regarde si tout est ok pour toi.
https://www.cjoint.com/c/DDDlscdw1b2

eric

PS: j'ai ajouté la possibilité d'insérer une colonne résultat pour pouvoir filtrer dessus.