formule recherche la plus rapide Résolu/Fermé

Question

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

Configuration: Linux / Opera 9.80

gbinforme · Answer

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.

eriiic · Answer

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.

gbinforme · Answer

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

PHILOU10120 · Answer

Bonjour

Une idée faire une macro pour copier coller la formule recherche sur 1 colonne à la fois cela va plus vite que de copier la formule sur cinq colonnes sur la hauteur en même temps, j'ai constaté ça sur un fichier de 25000 lignes le gain est important en temps
Et finir par un copier coller valeur pour ne pas garder 800000 formules multiplié par 5 colonnes 4000000 de formules
Pour moi je mets les formules dans des cellules au-dessus du tableau et quand je traite celui-ci je copie la formule par une macro et je fini par un copier coller valeur de la colonne obtenu sur place, comme cela c'est une base et cela pèse moins lourd en calcul de feuille 
Si cela peut vous aider

azertyh · Answer

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.

PHILOU10120 · Answer

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

gbinforme · Answer

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 ?

Formule recherche la plus rapide

7 réponses

Discussions similaires