Utilisation dictionary pour eviter formule EQUIV

**ericdev67** · 28/09/2016, 11h37

Bonjour,

Dans ma "feuil1", j'effectue différents calculs, et notamment en colonne Y où je calcule les mois d'une date (présente dans la colonne W).

Donc à présent, je mets en Y2 =INDEX(Feuil2!I:I;EQUIV(MOIS(W2);Feuil2!H:H;0)))

En Feuil2, colonne H, j'ai les numéros des mois (de 1 à 12) et en I, j'ai les mois correspondants (=de janvier à Décembre)

Et je copie/colle ma formule jusqu'à la dernière ligne.

N'y aurait-il pas un moyen plus rapide ? notamment en utilisant Dictionary ? (j'ai copié/collé des codes qui fonctionnent très très vite grâce à dictionary, mais je suis trop débutant pour pouvoir créer ce code....)

Une petite aide ?
En vous remerciant par avance,

**Jean-Philippe André** · 28/09/2016, 12h09

Salut,

je pense que la fonction format devrait repondre a ton besoin
http://jeannot45.developpez.com/arti...es/#LIII-B-4-b

**ericdev67** · 28/09/2016, 13h31

Merci Jean-Philippe André,

je ne connaissais pas cette fonction. Merci pour le tuyau.

En fait, je prenais l'exemple de mois, mais j'ai aussi des formules type :

=SI(ESTERREUR(EQUIV(K2;Feuil2!A:A;0));"Inconnu";INDEX(Feuil2!B:B;EQUIV(K2;Feuil2!A:A;0)))

Où Feuil2 en A, j'ai un numéro matricule et en B le nom du collègue.

D'où ma demande d'aide avec l'utilisation du dictionary....que j'adapterai aussi pour ce cas, pour le cas des mois, années, et d'autres recherches....

En vous remerciant pour votre aide,

**ericdev67** · 30/09/2016, 19h39

Bonjour,

Je mets en exemple un fichier test.....

Ce que je recherche c'est de ne plus avoir les formules Index/equiv qui se trouvent en D,E,F et de calculer automatiquement les valeurs (numériques ou alpha....)

Je pensais à Dictionary, mais il y a peut etre une autre solution....?

EN vous remerciant pour votre aide,

**Marc-L** · 30/09/2016, 20h27

Bonjour,

si la table est aussi petite, peu importe la formule de calculs via INDEX / EQUIV ou RECHERCHEV !
Et même si elle est grande via une astuce utilisant un double RECHERCHEV …

_________________________________________________________________________________________________________

Je suis Paris, Nice, Bruxelles, Charlie, …

**ericdev67** · 30/09/2016, 21h05

Merci Marc-L,

Oui, la table est petite (c'est un fichier test), mais avec les formules, lorsque je fais des calculs (par exemple, dans l'exemple, si je trie sur les mois (ordre croissant par exemple)), cela met un certain temps de recalcul....d'où l'utilisation du dictionary, qui me calculerait automatiquement tous les liens, et ensuite si je fais des tris, d'autres calculs (car c'est dans une petite partie de mon code), je ne "perds" pas de temps.....

Merci pour votre aide,

**patricktoulon** · 30/09/2016, 21h19

je pense avoir compris ce que tu cherche
mais il faut que tu sache qu'avec un dictionnaire il faut l'avoir duement remplie avant
sinon oui c'est possible et de le rendre dynamique dans l'evenemnt change du sheets
ca te conviendrait ????
re voir meme sans !!!!! avec un simple find dans l'evenement change tout simplement

met ceci: dans le sheets"feuil1")

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Column = 2 And Target.Value <> "" Then
        With Sheets("Table")
            Set c = .Range(.Cells(2, 5), .Cells(Rows.Count, 5).End(xlUp)).Find(Target.Value, Lookat:=xlWhole)
            If Not c Is Nothing Then
                Target.Offset(0, 2) = c.Offset(0, 1)
                Target.Offset(0, 3) = c.Offset(0, -3)
                Target.Offset(0, 4) = c.Offset(0, -2)
            End If
        End With
    End If
If Target.Value = "" Then Range(Target, Target.Offset(0, 3)) = ""
End Sub

et voila j'ai pas fait pour les secteur vu que c'est pareil que les mois dans le sheets ("Table")
voil c'est dynamique et pas de formule dans la cellule

**ericdev67** · 30/09/2016, 21h45

Bonjour patricktoulon,

Une fois de plus, merci pour ton aide,

Ce que je cherche, c'est la rapidité, sans devoir ouvrir la feuille (=feuil1 ou "table" ), et à force de chercher, j'ai vu vu les tutos de JB qui utilisent dictionary...d'où mon orientation vers dictionary...mais si une autre méthode existe, suis preneur....

Suis pas très fortiche dans les tableaux, mais j'ai commencé à utilisé les ubunt etc...et j'ai vu que c'était vraiment plus rapide que les formules et/ou les "for i......" donc, j'essaye d'aller dans cette direction

merci de ton aide,
Et vive le pilou-pilou....

Edit : nos messages se sont croisés, j'ai mis le code dans la Feuil1, modifié B2 (Private Sub Worksheet_Change(ByVal Target As Range)) , mais rien ne s'est passé...?

...ok pour les autres colonnes, il faut juste que je comprenne comment tu gères les éléments de "Table" (dans ton exemple, tu prends la colonne 2), et après je ferai idem pour les autres, no problèmo)....Merci

J'ai peut-être (mais ça m'arrive souvent...

) mal formulé la demande : il faut que je traite les 8115 lignes....

Merci pour ton aide

**patricktoulon** · 30/09/2016, 23h05

d'accord sue plusieurs ligne en memetemps ca n'est plus du dynamique alors c'est du "sur commande "

du genre je clique sur un bouton puis pouff toute mes cellules sont remplies c'est bien cela ????

**joe.levrai** · 30/09/2016, 23h56

Bonjour,

une proposition qu'il m'arrive d'utiliser sur de très gros fichiers excel, quand l'utilisateur veut éventuellement garder la main sur le fonctionnement des formules

je t'ai intégralement décomposé le processus via de nombreuses variables, cela devrait faciliter la compréhension et la possibilité de convertir ceci en une procédure "appelable" et munie de paramètres plutôt que de variables

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Option Base 1
 
Sub Toto()
Dim MesFormules(), i As Long
Dim ColonneDepart As Long, Nb_Colonnes As Long, Ligne_Debut_Donnees As Long
Dim EffacerFormule As Boolean, AvecEnTetes As Boolean
Dim LaFeuille As Worksheet
 
AvecEnTetes = True
EffacerFormule = False
Ligne_Debut_Donnees = 2
ColonneDepart = 4
Nb_Colonnes = 3
Set LaFeuille = Feuil1
 
MesFormules = Array("=INDEX(Table!C[2],MATCH(RC[-2],Table!C[1],0))", _
                    "=INDEX(Table!C[-3],MATCH(RC[-2],Table!C[-4],0))", _
                    "=INDEX(Table!C[-3],MATCH(RC[-3],Table!C[-5],0))")
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    With LaFeuille.Cells(Ligne_Debut_Donnees - 1 - AvecEnTetes, ColonneDepart)
        With .Resize(.CurrentRegion.Rows.Count + AvecEnTetes, Nb_Colonnes)
            For i = LBound(MesFormules) To UBound(MesFormules)
                .Columns(i).Formula = MesFormules(i)
            Next i
 
            .Calculate
 
            If EffacerFormule Then .Value = .Value
        End With
    End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 
End Sub

à noter que ta première formule consiste à écrire le numéro du mois en toutes lettres, en partant uniquement du numéro du mois.
ça me semble plus simple d'utiliser la formule =DATE(2016,B2,1) avec un format de cellule en "mmmm" ... plutôt qu'une table de correspondance sur laquelle tu fais un INDEX/EQUIV

**boisgontierjacques** · 01/10/2016, 03h07

Bonjour,

Voir PJ

Fonction perso matricielle plus rapide que Recherchev() si la formule Recherchev() est recopiée plusieurs fois.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Function RechvM(clé As Range, champ As Range, colResult)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ.Value
  b = clé.Value
  For i = LBound(a) To UBound(a)
    d(a(i, 1)) = a(i, colResult)
  Next i
  Dim temp()
  ReDim temp(LBound(b) To UBound(b))
  For i = LBound(b) To UBound(b)
    temp(i) = d(b(i, 1))
  Next i
  RechvM = Application.Transpose(temp)
End Function

Boisgontier

**ericdev67** · 01/10/2016, 08h41

Bonjour,

Merci patricktoulon,
Oui, c'est ca, je lance la macro et pouff tout est rempli. C'est exactement ce que je recherche. sans passer par des formules.

Merci joe.levrai,
Oui, votre code est très intéressant, et merci pour les explications. Mais comme je l'ai indiqué, je ne souhaiterai pas passer via des formules. Mais je garde votre code en réserve, et je sais que je vais le ré-utiliser pour un autre cas....Merci.

Merci JB,
J'avais trouvé votre code sur votre site. D'ailleurs merci pour tous vos tutos qui me font avancer. En fait, dans mon projet, je souhaite que la feuille "Base" et la feuil1 soient masquées (dans une autre feuille, j'ai des TCD qui sont en lien avec la feuil1). Donc mon projet importe un fichier (via la méthode ADO), le met en Feuil1 (feuille masquée), le code recherché fait les liens avec la feuille "table" (aussi masquée), et le TCD se mettra à jour automatiquement avec les nouvelles données. Toute la partie TCD et mise à jour est OK et fonctionne, la partie import via ADO aussi, c'est le lien entre Feuil1 et table (via un code type dictionary) qui me pose souci.....

En vous remerciant pour votre aide,

**joe.levrai** · 01/10/2016, 11h23

Envoyé par ericdev67

Merci joe.levrai,
Oui, votre code est très intéressant, et merci pour les explications. Mais comme je l'ai indiqué, je ne souhaiterai pas passer via des formules. Mais je garde votre code en réserve, et je sais que je vais le ré-utiliser pour un autre cas....Merci.

Bonjour,

je ne comprend pas en quoi la demande n'est pas remplie ?

Tu souhaites remplacer tes formules par les valeurs, je t'ai proposer une méthode qui fait ça quasi instantanément et sur un nombre conséquent de lignes. tu as la possibilité de conserver ou non les formules sur la feuille

Tu peux paramétrer intégralement en fonction de la configuration de ta feuille, et insérer dans chaque colonne une formule parfaitement différente

Que manque-t-il ?

Pourquoi vouloir s'écarter à tout prix des fonctions natives prévues par excel ?

A moins que tu veuilles manipuler des objets moins simples à appréhender, ou que les calculs soient fait en interne totalement, "pour le fun" ? Dans ce cas là, tu peux calculer tes fonctions en interne en utilisant Application.WorkSheetFunction."LaFonction"

Malgré toute l'admiration que j'ai sur les proposition de JB quand il manipule les dictionnaire (entre autres) et son site sur lequel je pioche très très souvent des idées et pistes, crois-tu qu'il te sera plus simple de faire évoluer/modifier ton code avec ce type de procédure ?

A toi de voir, c'est fonction de tes capacités.... il n'y a jamais de chemin unique en VBA

**ericdev67** · 01/10/2016, 11h39

Merci joe.levrai,

J'ai du mal m'exprimer et ne voulait en aucun cas minimiser ta solution et ton code....ce n'était vraiment pas le but...

Effectivement ton code fonctionne très bien,

Mais comme je le disais en réponse à JB, j'aimerai faire le calcul en "interne" sans passer par les formules...Le but pour moi étant double : bien laisser la feuil1 en masquer, mais surtout d'apprendre et me perfectionner (=étant autodidacte, je prend les codes, essaye de les modifier, voir la réaction du code, etc....afin de le ré-utiliser et/ou l'adapter..par exemle, dans ton code, je sais que je vais "jouer" avec la logique du Array que tu proposes...

..) et comme j'ai vu que le dictionary ou confrère calcule vraiment très vite, j'essaye de passer par cette voie.....

En te remerciant pour ton aide,

**Marc-L** · 01/10/2016, 14h09

Si vraiment c'est de performance dont il est question, l'objet Dictionary n'est pas toujours le plus rapide !

Comme déjà évoqué, l'astuce de la formule de calculs peut s'avérer la plus rapide et la plus simple à mettre en œuvre !

En joignant un classeur xlsx exact reflet du contexte de travail, on pourrait effectuer enfin des tests sérieux …

Maintenant si ce n'est pas de pure rapidité dont il question mais juste d'acquérir l'utilisation de l'objet Dictionary,
il y a tellement d'exemples sur ce forum comme sur l'excellent site du grand Jacques …

_________________________________________________________________________________________________________
Pour apprendre quelque chose aux gens, il faut mélanger ce qu’ils connaissent avec ce qu’ils ignorent. (Pablo Picasso)

**ericdev67** · 01/10/2016, 19h03

Merci Marc-L,

Je suis allé sur l'excellent site de JB, et c'est pour cette raison que je cherche via dictionary à résoudre le problème, mais j'ai besoin d'une petite aide car c'est la logique de dictionary que j'ai du mal à appliquer....

En vous remerciant pour votre aide,

**Marc-L** · 01/10/2016, 19h22

JB t'a honoré de sa présence dans cette discussion dans le post #11 en te livrant même un exemple sur mesure, What Else ?‼

**ericdev67** · 01/10/2016, 19h58

Re,

Oui, c'est vrai

Le soucis est qu'il faut mettre les formules dans les cellules pour appeler la fonction, alors que je cherche à inscrire directement les valeurs....

Un peu comme la proposition de patricktoulon,

Merci pour votre aide,

**joe.levrai** · 01/10/2016, 20h20

Bonjour,

pourquoi aurai-je mal pris le fait que ma proposition n'est pas ce que tu cherches ?

Si les contributeurs étaient si susceptibles, il y a longtemps que le forum serait déserté

Quoi qu'il en soit, tu sembles cherches le beurre et l'argent du beurre sur cette discussion.

Tu veux utiliser des objets que tu n'arrives pas à manipuler... et les propositions qui t'ont été faites, utilisant ces objets, ne te conviennent pas car tu voudrais avoir les valeurs et pas des formules

Soit,

revenons sur un morceau de ma proposition : regarde bien la ligne 30 qui permet de convertir une formule en sa valeur textuelle

ajoutes cette méthode à la proposition de JB ... et on est bon ?

Non, je ne pense pas en fait, j'espère me tromper

**Marc-L** · 01/10/2016, 20h55

En attendant ton autoformation avec Dictionary (rien qu'avec le code de JB et la documentation),
nommer par exemple SECTEURS la plage A2:C13 des secteurs de la feuille Table
puis exécuter cette démonstration tellement évidente pour un débutant et au résultat quasi instantané :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Demo1()
         Const F = "=SI(RECHERCHEV($C2;SECTEURS;1;VRAI)=$C2;RECHERCHEV($C2;SECTEURS;2;VRAI);"""")"
         Application.ScreenUpdating = False
         T! = Timer
    With Feuil1.Cells(1).CurrentRegion.Rows
        With .Item("2:" & .Count).Columns
             .Item(5).FormulaLocal = F
             .Item(6).FormulaLocal = Replace(F, ";2;", ";3;")
             .Item("E:F").Formula = .Item("E:F").Value
        End With
    End With
         Application.ScreenUpdating = True
         Debug.Print "Exécuté en "; Format(Timer - T, "0.000s")
End Sub

Dans un environnement international, utiliser la formule en version native anglaise avec la propriété Formula …

Utilisation dictionary pour eviter formule EQUIV

Macros et VBA Excel

Discussions similaires

Partager

Partager