Formule recherche la plus rapide

Résolu

Excel

azertyh Messages postés 121 Date d'inscription Statut Membre Dernière intervention -
gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention - 18 nov. 2012 à 23:25

Bonjour,
j'ai des données de plus de 800000 lignes dans feuil1. pour chaque ligne, je dois chercher une valeur dans feuil2 qui a plus de 800000 lignes aussi.
quelle est la formule de recherche la plus rapide : recherchev ou la combinaison index et equiv?
et est-ce que j'aurais un gain de temps si la feuil2 est triée?
merci

A voir également:

Recherche rapide sur excel
Liste déroulante excel - Guide
Acces rapide - Guide
Ajout rapide snap ✓ - Forum Snapchat
Word et excel gratuit - Guide
Recherche automatique des chaînes ne fonctionne pas - Guide

7 réponses

Réponse 1 / 7

gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention 4 724

bonjour,

Je ne pense pas qu'il y ai une différence notable entre les 2 formules:
- equiv recherche sur la colonne puis index ne fait que fournir le résultat indexé
- rechechev recherche sur la colonne puis décale sur la ligne pour donner le résultat.

Que ton tableau soit trié ou non, cela ne va pas changer non plus car si tu as autant de lignes dans chaque tableau, tu vas parcourir de toute façon à peu près le même nombre de lignes puisque la recherche s'arrête au premier trouvé.

Mais sur un grand volume, il serait sans doute souhaitable de passer en calcul manuel afin de ne pas tout recalculer à chaque modification.

azertyh Messages postés 121 Date d'inscription Statut Membre Dernière intervention 21

merci pour ta réponse rapide.
pas d'autres avis?

et c'est sûr que je vais passer en calcul manuel.

Réponse 2 / 7

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonsoir,

Comme c'est une seule fonction on pourrait supposer que recherchev() est optimisée (?)

Maintenant il faudrait que tu en dises plus sur ce que tu veux faire.
Peut-être qu'une macro te collant le résultat plutôt qu'une formule serait plus intéressant.
Tu peux ainsi alléger le fichier, gagner sur des lenteurs (quoique tes fonctions ne seront pas ré-évaluées en permanence).
En contrepartie tu n'auras plus de mise à jour au fil de l'eau, mais sur demande.
Tout dépend comment tu utiliseras ton fichier en fait. Mises à jour en masse ou unitaires, si c'est ponctuel ou très régulier, etc

Teste déjà avec formules et à l'usage tu verras...

eric

Jamais tu ne répondras à un mp non sollicité...
Bon, ça c'est fait.

azertyh Messages postés 121 Date d'inscription Statut Membre Dernière intervention 21

c'est vraiment une recherche entre les 2 tableaux que je veux faire.
par contre, une fois que la recherche est finie, je fige le résultat par une copie/coller valeur pour qu'il n'y a pas de re-calcul.
pour plus d'infos : dans le tableau2, j'ai 6 colonnes que je dois ré-écrire dans le tableau1. j'ai déjà fait la première colonne, ça a mis 2 heures 30. il reste 5 colonnes. en supposant que je ne me trompe pas dans ma formule car pour chaque recherche, il y a des nombreux SI imbriqués : si au lieu de >5000, j'ai mis par exemple <5000, je ne verrai le résultat qu'après 2 heures, et hop pour 2 heures de plus après modification de la formule.

Réponse 3 / 7

gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention 4 724

Bonjour,

Pour ta question initiale, j'ai fait un test sur 30000 et une colonne :
equiv et rechechev mettent exactement le même temps de calcul 52"

Maintenant tu nous dis que tu as de nombreux SI imbriqués : cela n'as rien à voir avec une recherche.

Si tu as 800000 lignes et 6 colonnes, c'est totalement dément et il serait plus judicieux de le faire par macro comme te le suggère Éric : en utilisant des zones mémoire tu passes tes heures en minutes.

Ton classeur va être très lourd à manipuler et tu as intérêt à faire des sauvegardes car il y a de fortes chances que tu ais des plantages du logiciel.

Toujours zen
La perfection est atteinte, non pas lorsqu'il n'y a plus rien à ajouter, mais lorsqu'il n'y a plus rien à retirer. Antoine de Saint-Exupéry

Réponse 5 / 7

azertyh Messages postés 121 Date d'inscription Statut Membre Dernière intervention 21

par exemple, sur une des colonnes, j'ai la formule suivante :

=SI(OU(G2="FR";G2="GR");"0-0";RECHERCHEV(H2;'[4081 in post.xlsx]4081 in post'!$G$3:$H$766727;2;FAUX))

sur une autre colonne, une formule de recherche simple :

=RECHERCHEV(E2;'[4081 in post.xlsx]4081 in post'!$A$3:$B$766745;2;FAUX)

comment une macro pourrait accélérer le calcul dans ce cas?

une fois qu'une formule achève son calcul, je vérifie son résultat par tri et filtre. et pour éviter le calcul, je copie et colle la valeur.

c'est ainsi que j'ai dû modifier la première formule comme ceci :

=SI(OU(G3="FR";G3="GR");"0-0";SI(B3<=2007;RECHERCHEV(E3;'[4081 in post.xlsx]4081 in post'!$A$3:$H$766727;8;FAUX);RECHERCHEV(H3;'[4081 in post.xlsx]4081 in post'!$G$3:$H$766727;2;FAUX)))

c'est toujours une formule de recherche, les conditions s'appliquent uniquement dans le tableau1.

fait est meilleur que parfait.

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

tu devrais déposer un exemple de qcq dizaines de lignes.
Déposer le fichier xls (réduit au nécessaire et anonymisé) sur cjoint.com et coller ici le lien fourni.

eric

gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention 4 724

Bonjour,

c'est toujours une formule de recherche

C'est d'accord mais ton dernier poste faisait croire à autre chose mais ta recherche a l'air de s'effectuer sur un autre classeur ?

comment une macro pourrait accélérer le calcul dans ce cas?

En utilisant des zones mémoire, le temps est divisé par 10, 100 parfois plus.

Réponse 6 / 7

PHILOU10120 Messages postés 6445 Date d'inscription Statut Contributeur Dernière intervention 824

Bonjour

Si au-dessus de votre tableau, la ligne au-dessus des entêtes des colonnes ou vous mettez votre formule et que par une macro commande vous faite un copier de la formule et vous sélectionnez la première cellule de la colonne vous ancrer et fin bas et coller la formule puis calcule copier la sélection et collage spécial valeur

le code pour une formule sur une colonne avec la formule en K17 copier en K20 et jusqu'à la fin de colonne

Sub Copier_formule()
'
' Copier_formule Macro
'

'
Application.Goto Reference:="k17"
Selection.Copy
Application.Goto Reference:="k20"
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="l17" ' la cellule de la formule suivante
End Sub

Si cela peut vous aider je ne suis pas un spécialiste des macros voir Eriiic

azertyh Messages postés 121 Date d'inscription Statut Membre Dernière intervention 21

si ceci réduit le temps par 100 ou par 10, ou même par 2, je suis intéressé.
une fois que le calcul en cours est fait, je vais tester cette macro et vous fera part si c'est bon ou non.

gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention 4 724

Cette macro ne va pas te gagner du temps de calcul mais te faciliter la mise en place du résultat des formules.
Pour obtenir des gains de calcul, il faut calculer par macro en mémoire au lieu de calculer sur la feuille.

azertyh Messages postés 121 Date d'inscription Statut Membre Dernière intervention 21

bonjour,
les copies et collages sont faites en quelques secondes manuellement. c'est le temps de calcul qu'il faut réduire, s'il existe des façons de le réduire.
je vais mettre mon topic en résolu puisque j'ai la réponse à mes 2 premières questions.
néanmoins, je suis intéressé par ce calcul par macro en mémoire, par curiosité.

gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention 4 724

bonjour,

Comme te l'a dit Éric, s tu pouvais nous mettre un exemple de quelques lignes sans éléments personnels mais avec la structure des données sur https://www.cjoint.com/ puis nous mettre le lien généré ici ce serait plus facile. Si tu ne veux pas tu peux toujours lire ceci qui donne un rapport de 1000 !

Réponse 7 / 7

gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention 4 724

Bonjour azertyh,

Je reviens sur ton problème que tu as résolu avec le temps. Tu disais :

si ceci réduit le temps par 100 ou par 10, ou même par 2, je suis intéressé.

Je viens d'avoir l'occasion de traiter un classeur avec des recherches un peu complexes (1000 environ) qui était géré par formules de recherche classiques.
Le calcul mettait (contrôlé par macro) 50/60 secondes, mais à chaque modification, c'est rapidement lassant ! :)

J'ai mis les recherches avec un tableau mémoire et pour les mêmes opérations, le calcul s'effectue en moins d'une seconde et là, c'est agréable. ;-)

Dans ton cas, je pense que tes 2h30, réduits de la même manière, se rapprochent de mon temps prohibitif ?

Discussions similaires

Figer plusieurs lignes et plusieurs colonnes

VBA: remplir combobox à partir d'une colonne

Problème excel pour faire x en fonction de y

mise en forme conditionnelle d'une colonne en fonction d'autres

Créer un cercle autour d'un point connu dans graphique

Transformer un fichier xml en xls

Votre réponse

Discussions similaires