XL 2013 VBA: Dico & Recherche multicritère // Some Si

sarah33 · 21 Novembre 2015

Bonjour à tous,

J'ai découvert il y a peu de temps grâce au forum les variables dictionnary !
En effet, je dois travailler une base comportant beaucoup de lignes..

Actuellement, j'utilise Dictionnary pour retrouver une valeur, en fonction de un critère.
Aujourd'hui, je souhaite utiliser Dictionnary pour 2 choses:

Réaliser des calculs, pour les lignes contenant le critère de recherche:
Exemple: Additionner les Qtité pour toutes les références "A"

Aussi, je souhaite faire la même chose pour les lignes contenant 2 critères:
Exemple Additionner les Qtité pour toutes les référence "B" dont la référence2 est "AK"

Je souhaite utiliser dictionnary, pour réaliser ceci (si biensur c'est possible...) car le temps de traitement est trop long avec les autres fonctions habituelles..
Egalement, mon appli étant 100% VBA, ça m'embêterai d'y glisser des formules ou fonctions formules, pour cette partie là..

Pourriez-vous m'indiquer la marche à suivre?

J'ai tenté des tuto, mais rien de suffisamment clair pour mon faible niveau...
Faut-il créer plusieurs dicos? comment additionner les valeurs du dico?

Merci d'avance pour vos réponses !

PS: j'ai mis un fichier 2013 et un en 2003 , au cas ou..

gosselien · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Bonjour,

je te comprends , je rame depuis des semaines sur ça aussi mais il m'arrive d'y voir clair (un moment de lucidité sans doute)

essaye ceci:

Sub SommeItemsByDico()
Set Mondico = CreateObject("Scripting.Dictionary")
For Each C In Range("a2", [a65000].End(xlUp))
' addition de la valeur de la colonne 2 si colonne C contient ak ou AK
Mondico(C.Value) = Mondico(C.Value) + IIf(C.Offset(, 2) = UCase("AK"), C.Offset(, 1), 0)
Next C
[r2].Resize(Mondico.Count, 1) = Application.Transpose(Mondico.Keys)
[s2].Resize(Mondico.Count, 1) = Application.Transpose(Mondico.Items)
End Sub

et va voir chez le Maitre : http://boisgontierjacques.free.fr/fichiers/Cellules/SousTotal2Colonnes.xls

sarah33 · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Bonjour gosselien,

Moi aussi lol, j'ai même créer une usine à gaz, en combinant TCD & Dico pour arriver à mes fins, mais c'était vraiment du bricolage !!
Je te remercie pour ta réponse !! je vais feuilleter tout ça, et tester grandeur nature !!

Merci pour ton retour si rapide !
A bientot !

Dranreb · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Bonjour.
Pour ce genre de choses je préfère un UseForm avec des ComboBox liées. Je n'utilise plus de Dictionary parce que les ComboBox ont déjà une faculté de recherche d'un critère dans leurs listes. Et aussi parce qu'ils ont le gros inconvénient de fabriquer tout un tableau de l'ensemble des items quand on demande à n'en récupérer qu'un seul selon sa position dedans au lieu de sa clé.

Bonjour Paf.

Paf · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Bonjour sarah33, gosselien,

l'utilisation de dictionary serait utile pour additionner les quantité référence par référence et afficher toutes les références et leur quantité.

Dans la mesure on ne veut traiter qu'une référence ça ne se justifie pas vraiment.

L' utilisation ou non d'un dico passera par le 'balayage' d'un tableau contenant les données

Par exemple:

par fonction personnalisée:

Code:

Function Som2(plage, Crit1, Crit2)
 Dim MonTab, i
 MonTab = plage
 For i = LBound(MonTab) To UBound(MonTab)
    If MonTab(i, 1) = Crit1 And MonTab(i, 3) = Crit2 Then Som2 = Som2 + MonTab(i, 2)
 Next
End Function

ou macro

Code:

sub Som2()
 Dim MonTab, i,Crit1,Crit2,Som
 Crit1=cells(6,10)
 Crit2=cells(6,11)
 MonTab = range("A2:C14") ' à adapter
 For i = LBound(MonTab) To UBound(MonTab)
    If MonTab(i, 1) = Crit1 And MonTab(i, 3) = Crit2 Then Som = Som + MonTab(i, 2)
 Next
 Cells(6,12)=Som
End Sub

à adapter pour la recherche 1 critère

A+

Edit: bonjour Dranreb

re edit correction de end function pour la sub

sarah33 · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Bonjour Dranreb, Paf,

l'utilisation de dictionary serait utile pour additionner les quantité référence par référence et afficher toutes les références et leur quantité.

C'est exactement ce que je veux faire en faite

En effet mon exemple n'est pas vraiment représentatif.
JE dois bien balayer grossomodo 5000 lignes, pour chercher les valeurs présentes dans 75 000lignes, avec donc des boucles.
Mais pour l'exemple, je voulais juste comprendre le principe de "multicritère" et de "calcul" au sein du dictionary.

Ceci dit, comme dit Dranreb, à refaire, je pense que je partirai sur des userform et combobox !

Edit: merci pour vos exemple avec variable tableau, ça m'aide vraiment à mieux comprendre comment réaliser des calculs avec recherche !!!!! je commençais à me dire que ce n'était pas possible... xD comment j'ai pu penser ça !
C'est à dire que les tutos pour ce thème (dico ou variable tableau) sont plutot costaud pour les débutants..

Dranreb · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Un item d'un Dictionary peut contenir tout ce que peut contenir un Variant, y compris … un Dictionary !
J'ai un modules de service qui sait fabriquer sur ce principe ce que j'appelle un dictionnaire arborescent.
C'est une voie pour accéder à une info selon des critères multiples.
Mais il y a longtemps que je ne le propose plus car il servait surtout à un module de classe ComboBoxLiés.
Or pour ça je préfère proposer ComboBoxLiées (remarquer le féminin) qui utilise à la place ce que j'appelle des Sujets, constitués de tableaux de tableaux de tableaux de Variant. Accès direct aux listes de numéros de lignes selon les ListIndex des ComboBox.

gosselien · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Paf à dit:

par fonction personnalisée:

Code:

Function Som2(plage, Crit1, Crit2)
 Dim MonTab, i
 MonTab = plage
 For i = LBound(MonTab) To UBound(MonTab)
    If MonTab(i, 1) = Crit1 And MonTab(i, 3) = Crit2 Then Som2 = Som2 + MonTab(i, 2)
 Next
End Function

[/QUOTE]

Bonjour ou re à tous et merci pour l'explication qui est bien juste pour des tableaux courts, mais encore faut il les comprendre (ici j'ai compris je pense) :) 

à priori un petite erreur, la fonction ne fonctionnait pas ....

Function Somm2(plage As Range, Crit1 As String, Crit2 As String)
 Dim MonTab, i, Tot
 MonTab = plage
 For i = LBound(MonTab) To UBound(MonTab)
    If MonTab(i, 1) = Crit1 And MonTab(i, 3) = Crit2 Then Tot = Tot + MonTab(i, 2)
 Next
 Somm2 = Tot
End Function

Dranreb · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Voulez vous une démo à partir de votre fichier d'un UserForm qui affiche le nombre d'éléments et la somme des quantités ?

sarah33 · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Merci pour vos explications,

Je veux bien un fichier démo perso !

Dranreb · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Le voici alors.

Paf · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Re,

@ gosselien

à priori un petite erreur, la fonction ne fonctionnait pas ....

peut-être sur XL 2007 ? mais telle qu'elle est donnée au post #5, elle fonctionne sur XL2003 et le classeur fourni par sarah33

merci pour l'explication qui est bien juste pour des tableaux courts

Si cette remarque est pour moi (?), que le tableau soit court ou long, il faudra, de toutes façons dico ou pas, balayer tous le tableau .

A+

gosselien · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Paf à dit:
Re,

@ gosselien

peut-être sur XL 2007 ? mais telle qu'elle est donnée au post #5, elle fonctionne sur XL2003 et le classeur fourni par sarah33

Si cette remarque est pour moi (?), que le tableau soit court ou long, il faudra, de toutes façons dico ou pas, balayer tous le tableau .

A+

Ici en XL2007, ça n'a pas fonctionné au premier coup

La remarque n'est pas péjorative loin de là , et instructive (pour moi ) , je me suis mal exprimé , sorry

Paf · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Re,

@ gosselien

La remarque n'est pas péjorative loin de là , ...

je n'avais pas pris comme telle la remarque :"merci pour l'explication qui est bien juste pour des tableaux courts"
qui pourrait laisser penser que l'explication n'est pas juste pour des tableaux "longs"; je tenais juste à préciser.

cordialement

BOISGONTIER · 21 Novembre 2015

Re : VBA: Dico & Recherche multicritère // Some Si

Bonjour,

EXemple de fonction matricielle utilisant un dictionnaire

Elle remplace 1000 Somme.si
Au lieu de plusieurs secondes de recalcul, il faut 0,2 sec

Code:

Function TotalClé(clé As Range, champ As Range, colResult, messageErreur)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ.Value
  b = clé.Value
  For i = LBound(a) To UBound(a)
    d(a(i, 1)) = 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)
    If d(b(i, 1)) <> "" Then temp(i) = d(b(i, 1)) Else temp(i) = messageErreur
  Next i
  TotalClé = Application.Transpose(temp)
End Function

http://boisgontierjacques.free.fr/fichiers/fonctionsperso/Totalcle2critJB.zip
http://boisgontierjacques.free.fr/fichiers/Cellules/CombienPerso.xls
http://boisgontierjacques.free.fr/fichiers/Cellules/MatricielPerso.xls

JB

XL 2013 VBA: Dico & Recherche multicritère // Some Si

sarah33

XLDnaute Junior

Pièces jointes

gosselien

XLDnaute Barbatruc

sarah33

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

Paf

XLDnaute Barbatruc

sarah33

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

gosselien

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

sarah33

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

Pièces jointes

Paf

XLDnaute Barbatruc

gosselien

XLDnaute Barbatruc

Paf

XLDnaute Barbatruc

BOISGONTIER

XLDnaute Barbatruc

Pièces jointes

Discussions similaires