Extraction multicritère de données en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Extraction de données en VBA Excel

Dans cette formation, nous allons réaliser l'extraction d'informations sur une base de données, selon des critères recoupés en cascade. Et pour cela, nous proposons de récupérer l'application que nous avions développée pour créer des listes déroulantes reliées entre elles par la code VBA Excel.



Dans l'exemple illustré par la capture ci-dessus, des choix ont été réalisés dans les listes déroulantes reliées en cascade. A chaque changement détecté dans une liste, un code Visual Basic est déclenché pour extraire les données correspondant aux choix ainsi recoupés.

Sources et présentation
Dans un premier temps, il s'agit donc de récupérer le classeur Excel offrant ces listes déroulantes reliées.

• Télécharger le classeur extraire-donnees-vba.xlsm en cliquant sur son lien ,
• L'ouvrir dans Excel et cliquer sur le bandeau de sécurité Activer la modification,
• Fermer le classeur en l'enregistrant puis le rouvrir,

Cette manipulation intermédiaire peut s'avérer utile car la sécurité sur les sources externes peut empêcher l'exécution du code prévu pour se déclencher à l'ouverture du classeur, lors de la première exécution.

• Cliquer sur l'onglet listes_cascade en bas de la fenêtre Excel pour activer sa feuille,
• Dans la première liste déroulante en haut de la feuille, sélectionner le département 83-Var par exemple,
• A l'aide de la deuxième liste déroulante, sélectionner l'activité Hôtel/Restaurant,
• Enfin, à l'aide de la troisième liste déroulante, sélectionner la ville de Toulon,

Comme vous le remarquez, les listes déroulantes fonctionnent en cascade. Les propositions des unes dépendent des choix réalisés en amont dans les autres. Ce processus a été bâti lors de la formation précédente pour relier des listes déroulantes entre elles par le code Visual Basic. Leurs données sélectives sont issues de la source inscrite dans la feuille bd_sorties de ce classeur. Cette base recense des idées de sorties par département, activité et ville. Au choix d'un département, nous obtenons dans la deuxième liste, toutes les activités recensées dans ce département. Au choix d'une activité, nous obtenons dans la troisième liste, les villes disponibles pour le département et l'activité sélectionnés en amont.

L'enjeu ici consiste à restituer toutes les idées de sorties correspondant aux choix émis par l'utilisateur, par le biais des listes déroulantes. C'est la raison pour laquelle, un tableau d'extraction est prévu dans la feuille listes_cascade, entre les colonnes B et F, à partir de la ligne 8. Il s'agit de déclencher un code VBA Excel capable de parcourir toutes les lignes de la base de données à la recherche des enregistrements concordants.

Purger les anciennes extractions
A la première utilisation, le tableau d'extraction est vierge. Mais lorsque le code sera fonctionnel, les données isolées, si elles sont enregistrées, seront conservées. A l'ouverture du classeur, nous devons donc commencer par nettoyer les potentielles traces des anciennes extractions. Sur la feuille listes_cascade, il s'agit donc de nettoyer le tableau à partir de la ligne 9, entre les colonnes B et F, tant que des données sont détectées.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code,
• Dans l'explorateur de projet sur la gauche, cliquer sur l'élément ThisWorkbook,

Nous affichons ainsi la feuille de code de l'objet VBA ThisWorkbook désignant le classeur actif. Nous y trouvons toutes les instructions développées dans l'application précédente pour relier les listes déroulantes entre elles. Toutes ces lignes de codes sont placées entre les bornes de la procédure Workbook_Open. En conséquence, elles sont déclenchées à l'ouverture du classeur.

• Après l'instruction Next colonne de la boucle For, ajouter les lignes suivantes (En gras) :

For colonne = 2 To 6 Step 2
ligne = 5
While Sheets('construction').Cells(ligne, colonne).Value <> ''
Sheets('construction').Cells(ligne, colonne).Value = ''
ligne = ligne + 1
Wend
Next colonne

ligne = 9
While (Sheets('listes_cascade').Cells(ligne, 2).Value <> '')
Sheets('listes_cascade').Cells(ligne, 2).Value = ''
Sheets('listes_cascade').Cells(ligne, 3).Value = ''
Sheets('listes_cascade').Cells(ligne, 4).Value = ''
Sheets('listes_cascade').Cells(ligne, 5).Value = ''
Sheets('listes_cascade').Cells(ligne, 6).Value = ''

ligne = ligne + 1
Wend

'Charger les départements
ligne_bd = 2: ligne = 5
While Sheets('bd_sorties').Cells(ligne_bd, 4).Value <> ''
...

Nous initialisons la variable ligne sur le premier indice de ligne du tableau à parcourir dans la feuille listes_cascade. Puis, tant qu'une cellule non vide est détectée en colonne B (While(Sheets('listes_cascade').Cells(ligne, 2).Value <> '')), nous supprimons le contenu de chaque cellule de la ligne, jusqu'à la colonne F, soit la sixième colonne (Sheets('listes_cascade').Cells(ligne, 6).Value = ''). Avant de boucler (Wend), nous n'oublions pas d'incrémenter la variable (ligne = ligne + 1), pour passer sur la ligne suivante à la prochaine itération.

• Enregistrer les modifications(CTRL + S),

Il est encore trop tôt pour tester ce code dans la mesure où aucune extraction n'a eu lieu pour l'instant.

Charger les villes en fonction des départements
Dans le développement précédent, nous avions prévu de charger la liste des villes, une fois que celles des activités et des départements avaient été remplies. Ainsi nous proposions les villes disponibles pour le département et l'activité sélectionnés. Mais nous souhaitons que l'utilisateur puisse extraire les idées de sorties aussi par sélection du département et de la ville, sans nécessairement préciser l'activité.

• Dans l'explorateur de projet, double cliquer sur le Module2 pour afficher son code,
• Changer la signature de la procédure charger_villes comme suit :

Sub charger_villes(sur_dep As Boolean)

Nous ajoutons un paramètre à la procédure de manière à différencier le traitement. Si elle est appelée au changement de département (sur_dep=True), nous devons simplement charger les villes correspondant à ce dernier. Dans le cas contraire, il s'agit de charger les villes correspondant à la fois au département et à l'activité sélectionnés par l'utilisateur, sur les listes déroulantes en amont.

• Dans la procédure, adapter le code de la boucle While comme suit :

Sheets('bd_sorties').Select
ligne = 2: ligne_construct = 5
While Cells(ligne, 1).Value <> ''
If (sur_dep = False And Cells(ligne,4).Value = le_dep And Cells(ligne, 3).Value = lact) Then
If (InStr(1, chaine_villes, '-' & Cells(ligne, 5).Value & '-', 1) = 0) Then
chaine_villes = chaine_villes & '-' & Cells(ligne, 5).Value & '-'
Sheets('construction').Cells(ligne_construct, 6).Value = Cells(ligne, 5).Value
ligne_construct = ligne_construct + 1
End If
ElseIf (sur_dep = True And Cells(ligne, 4).Value = le_dep) Then
If (InStr(1, chaine_villes, '-' & Cells(ligne, 5).Value & '-', 1) = 0) Then
chaine_villes = chaine_villes & '-' & Cells(ligne, 5).Value & '-'
Sheets('construction').Cells(ligne_construct, 6).Value = Cells(ligne,5).Value
ligne_construct = ligne_construct + 1
End If
End If
ligne = ligne + 1
Wend

Nous doublons le critère (If et ElseIf) pour vérifier deux cas. Le premier If est celui du code originel. Il consiste à réceptionner toutes les villes selon les correspondances trouvées dans la base, à la fois sur le département et sur l'activité. C'est la raison pour laquelle, nous ajoutons dans l'énumération des critères, la condition sur la variable booléenne (sur_dep = False And). Cette recherche doit en effet se faire lorsque la demande émane du changement de valeur depuis la liste des activités. Dans le cas contraire (ElseIf), nous devons récupérer l'ensemble des villes correspondant seulement au département choisi, depuis la première liste déroulante. Nous devons donc vérifier ensemble deux critères : la variable booléenne doit être à True et l'égalité sur le champ Département doit être vérifiée.

Ensuite nous ne faisons que répliquer le traitement précédent. Nous exploitons notamment la fonction VBA Excel InStr pour savoir si la cellule de la ville en cours d'analyse (Cells(ligne,5).Value) a déjà été mémorisée. Nous évitons ainsi de cumuler les doublons. Si ce n'est pas le cas, nous l'inscrivons à la suite dans la feuille construction qui sera traitée à l'issue pour proposer une organisation croissante des villes. Nous n'oublions pas d'incrémenter les variables des boucles.

Il s'agit désormais de tester la fonctionnalité de cette procédure capable de charger la liste des activités ou des villes, selon le contexte. Mais pour cela, nous devons adapter son appel dans le code des listes déroulantes, afin de passer le paramètre booléen attendu.

• Dans l'explorateur de projet, double cliquer sur l'élément Feuil1 (listes_cascade) pour afficher son code,
• Dans la procédure liste_act_Change, adapter l'appel de la procédure charger_villes comme suit :

Private Sub liste_act_Change()
On Error Resume Next

liste_villes.Clear

If (liste_act.Value <> 'Sélectionner une activité' And liste_act.Value <> '') Then
Sheets('listes_cascade').Range('I5').Value = liste_act.Value
charger_villes (False)
Else
Sheets('listes_cascade').Range('I5').Value = ''
End If

Sheets('listes_cascade').Range('J5').Value = ''
End Sub

Au changement d'activité, la liste des villes doit considérer le double critère sur le département et l'activité pour effectuer la correspondance en base de données. C'est la raison pour laquelle, telle que nous l'avons conçue, nous passons le paramètre false à la procédure charger_villes. Son instruction conditionnelle adaptera la recherche et la récolte selon ces critères.

• Dans la procédure liste_dep_Change, ajouter l'appel de la procédure charger_villes comme suit :

Private Sub liste_dep_Change()
On Error Resume Next

liste_act.Clear
liste_villes.Clear

If (liste_dep.Value <> 'Sélectionner un département' And liste_dep.Value <> '') Then
Sheets('listes_cascade').Range('H5').Value = liste_dep.Value
charger_activite
charger_villes (True)
Else
Sheets('listes_cascade').Range('H5').Value = ''
End If

Sheets('listes_cascade').Range('I5').Value = ''
Sheets('listes_cascade').Range('J5').Value = ''
End Sub

Après l'appel de la procédure charger_activite, nous passons la main à la procédure charger_villes avec le paramètre True. Au changement de département donc, nous chargeons en même temps la liste des activités ainsi que la liste des villes. Cette fois, les villes récoltées doivent seulement correspondre au département choisi. Il s'agit de tester le bon fonctionnement de ces adaptations.

• Enregistrer les modifications et basculer sur la feuille listes_cascade,
• Sélectionner un département à l'aide de la première liste déroulante,
• Sélectionner une ville à l'aide de la troisième liste déroulante,

Voilà une évolution fort intéressante. Désormais l'utilisateur, dans la foulée du département, peut définir soit l'activité soit la ville, afin d'affiner ses critères de recherche. Dans le cas de l'activité, il peut encore augmenter la précision jusqu'au choix de la ville correspondante.

Extraire les données multicritères en VBA Excel
Les conditions imposées par les choix dans les listes déroulantes, sont instantanément retranscrites dans la zone de critères de la feuille listes_cascade, entre les colonnes H et J. Nous allons donc devoir bâtir un code capable de restituer toutes les informations issues de la base de données, tout en respectant ces critères recoupés.

• Revenir dans l'éditeur de code (ALT + F11),
• Cliquer sur le menu Insertion en haut de la fenêtre de l'éditeur,
• Dans la liste, choisir Module,

Nous créons ainsi un nouveau module (Module 3) pour accueillir les procédures de code d'extraction des données selon les critères recoupés par les listes déroulantes en cascade.

• Dans la feuille de code au centre de l'écran, créer la procédure extraire, comme suit :

Sub extraire()

End Sub

• De la même façon, créer la procédure extraction comme suit :

Sub extraction(ligne_listes As Integer, ligne_bd As Integer)

End Sub

La procédure extraire consistera essentiellement à parcourir la base de données de la feuille bd_sorties, tout en vérifiant les critères recoupés et énoncés dans la zone de critères de la feuille listes_cascade. Elle se chargera alors d'appeler la fonction extraction en lui passant les indices de lignes en cours de lecture dans la feuille bd_sorties (ligne_bd), et en cours d'indexation dans la feuille listes_cascade (ligne_listes). Elle n'aura plus qu'à récupérer les données pour les inscrire comme résultats de la recherche émise par l'utilisateur.

Nous devons commencer par déclarer les variables nécessaires au traitement.

• Dans la procédure extraire, ajouter les déclarations suivantes :

Dim ligne_listes As Integer: Dim ligne_bd As Integer

Nous avons besoin d'une variable permettant de parcourir les enregistrements de la base de données (ligne_bd). Lorsque le critère est concluant, nous devons inscrire les résultats de l'extraction dans la feuille listes_cascade à la suite des autres. La variable ligne_listes doit permettre cette incrémentation.

Avant de poser le code permettant de reconstruire les critères recoupés à vérifier, deux opérations préalables sont nécessaires. Nous devons vérifier que toutes les cellules de la zone de critère ne sont pas vides. Si tel est le cas, aucune opération d'extraction ne doit être enclenchée. Dans le cas contraire, nous devons commencer par vider les cellules de la zone d'extraction pour accueillir les nouveaux résultats.

• A la suite du code, ajouter le test suivant :

If (Range('H5').Value = '' And Range('I5').Value = '' And Range('J5').Value = '') Then
Return
End If

ligne_listes = 9
While (Sheets('listes_cascade').Cells(ligne_listes, 2).Value <> '')
Sheets('listes_cascade').Cells(ligne_listes, 2).Value = ''
Sheets('listes_cascade').Cells(ligne_listes, 3).Value = ''
Sheets('listes_cascade').Cells(ligne_listes, 4).Value = ''
Sheets('listes_cascade').Cells(ligne_listes, 5).Value = ''
Sheets('listes_cascade').Cells(ligne_listes, 6).Value = ''
ligne_listes = ligne_listes + 1
Wend

Dans le cas où (If), la cellule H5 et (And) la cellule I5 et (And) la cellule J5 sont vides ensemble, aucun choix n'a été émis, donc aucun critère n'est à consolider pour l'extraction. De fait, nous abandonnons l'exécution du code (return).

Ensuite, nous initialisons la variable ligne_listes sur la première ligne du tableau d'extraction. A partir de cet indice, nous commençons la lecture des cellules à la recherche de celles qui ne sont pas vides (Sheets('listes_cascade').Cells(ligne_listes, 2).Value <> '')). Tant qu'un résultat est trouvé, tous les contenus des cellules de la ligne en cours sont supprimés et ce, jusqu'à la colonne F (Sheets('listes_cascade').Cells(ligne_listes, 6).Value = ''). Comme toujours dans une boucle While, nous n'oublions pas d'incrémenter la variable de lecture (ligne_listes = ligne_listes + 1).

Il s'agit désormais de passer en revue toutes les lignes de la base de données (feuille bd_sorties), toujours à l'aide d'une boucle While, à la recherche des enregistrements qui correspondent au critère consolidé. Ce critère peut présenter quatre formes différentes :

• Seul le département est défini (H5 <> '', I5 = '', J5 = ''),
• Le département et l'activité sont définis (H5 <> '', I5 <> '', J5 = ''),
• Le département et la ville sont définis (H5 <> '', I5 = '', J5 <> ''),
• Le département, la ville et l'activité sont définis (H5 <> '', I5 <> '', J5 <> ''),

Nous proposons d'exploiter l'instruction conditionnelle If avec des branches ElseIf (Sinon si) pour traiter tous ces cas.

• A la suite du code, ajouter le bloc d'instructions suivant :

ligne_bd = 2: ligne_listes = 9
Sheets('listes_cascade').Select
While (Sheets('bd_sorties').Cells(ligne_bd, 1).Value <> '')
If (Range('H5').Value <> '' And Range('I5').Value = '' And Range('J5').Value = '') Then
If (Sheets('bd_sorties').Cells(ligne_bd, 4).Value = Range('H5').Value) Then
extraction ligne_listes, ligne_bd
ligne_listes = ligne_listes + 1
End If
ElseIf (Range('H5').Value <> '' And Range('I5').Value <> '' And Range('J5').Value = '') Then
If (Sheets('bd_sorties').Cells(ligne_bd, 4).Value = Range('H5').Value And Sheets('bd_sorties').Cells(ligne_bd,3).Value = Range('I5').Value) Then
extraction ligne_listes, ligne_bd
ligne_listes = ligne_listes + 1
End If
ElseIf (Range('H5').Value <> '' And Range('I5').Value = '' And Range('J5').Value <> '') Then
If (Sheets('bd_sorties').Cells(ligne_bd, 4).Value = Range('H5').Value And Sheets('bd_sorties').Cells(ligne_bd, 5).Value = Range('J5').Value) Then
extraction ligne_listes, ligne_bd
ligne_listes = ligne_listes + 1
End If
ElseIf (Range('H5').Value <> '' And Range('I5').Value <> '' And Range('J5').Value <> '') Then
If (Sheets('bd_sorties').Cells(ligne_bd, 4).Value = Range('H5').Value And Sheets('bd_sorties').Cells(ligne_bd, 3).Value = Range('I5').Value And Sheets('bd_sorties').Cells(ligne_bd, 5).Value = Range('J5').Value) Then
extraction ligne_listes, ligne_bd
ligne_listes = ligne_listes + 1
End If
End If
ligne_bd = ligne_bd + 1
Wend

Nous réinitialisons tout d'abord les deux variables de boucle sur les premières lignes des tableaux respectifs (ligne_bd = 2:ligne_listes = 9). Puis, nous entamons la lecture ligne à ligne de la base de données, tant qu'une information est trouvée en première colonne : While (Sheets('bd_sorties').Cells(ligne_bd, 1).Value <> '').

A l'intérieur de cette boucle, nous envisageons chaque critère possible, selon l'état des cellules de la zone de critères dans la feuille listes_cascade. Lorsqu'elles sont toutes définies (ElseIf (Range('H5').Value <> '' And Range('I5').Value <> '' And Range('J5').Value <> '') Then), nous devons nous assurer que le département, l'activité et la ville sont équivalents à la demande. Si ce triple critère est vérifié, nous appelons la procédure extraction en lui passant les indices de lignes en cours pour les deux tableaux. Selon ces indices, c'est elle qui doit se charger de récolter toutes les informations de champs pour l'enregistrement en cours dans la base de données, afin de les inscrire à la suite dans la feuille listes_cascade.

• En conséquence, entre les bornes de la procédure extraction, ajouter les lignes suivantes :

Sheets('listes_cascade').Cells(ligne_listes, 2).Value = Sheets('bd_sorties').Cells(ligne_bd, 1).Value
Sheets('listes_cascade').Cells(ligne_listes, 3).Value = Sheets('bd_sorties').Cells(ligne_bd, 2).Value
Sheets('listes_cascade').Cells(ligne_listes, 4).Value = Sheets('bd_sorties').Cells(ligne_bd, 3).Value
Sheets('listes_cascade').Cells(ligne_listes, 5).Value = Sheets('bd_sorties').Cells(ligne_bd, 4).Value
Sheets('listes_cascade').Cells(ligne_listes, 6).Value = Sheets('bd_sorties').Cells(ligne_bd, 5).Value

Pour la ligne en cours de la base de données, nous prélevons chaque information de colonne (Sheets('bd_sorties').Cells(ligne_bd, 1).Value) pour l'inscrire dans le champ correspondant de la zone d'extraction (Sheets('listes_cascade').Cells(ligne_listes, 2).Value). Tous les résultats concordants sont susceptibles de se cumuler les uns en-dessous des autres. En effet, l'appel de cette procédure est encapsulé dans le traitement itératif de la boucle While.

Mais comme toujours, pour que ces procédures externes puissent s'exécuter, elles doivent être appelées. Nous proposons d'offrir une extraction des données à chaque choix réalisé dans une liste déroulante. L'appel de la procédure extraire doit donc survenir dans le code associé au changement détecté dans chaque liste déroulante.

• Dans l'explorateur de projet, double cliquer sur l'élément Feuil1 (listes_cascade),
• Dans les procédures liste_act_Change et liste_dep_Change, ajouter l'appel de la procédure extraire, juste après l'appel de la procédure charger_villes,
• De même, dans la procédure liste_villes_Change, ajouter l'appel de la procédure extraire comme suit :

Private Sub liste_villes_Change()
On Error Resume Next

If (liste_villes.Value <> 'Sélectionner une ville' And liste_villes.Value <> '') Then
Sheets('listes_cascade').Range('J5').Value = liste_villes.Value
extraire
Else
Sheets('listes_cascade').Range('J5').Value = ''
End If
End Sub

Comme vous le constatez, comme la procédure extraire n'attend aucun paramètre, son appel se fait sans les parenthèses en VBA.

• Enregistrer les modifications et basculer sur la feuille listes_cascades,
• A l'aide de la première liste déroulante, choisir un département, par exemple le Var,

Comme vous le remarquez, dès le choix du département, les données issues de la base et correspondant à ce critère simple, sont extraites et affichées dans la feuille de données.

• A l'aide de la deuxième liste déroulante, choisir une activité, par exemple Hôtel,

Les données extraites s'adaptent instantanément pour n'afficher que les résultats affinés, soit ceux correspondants au double critère émis par les deux premières listes déroulantes. C'est encore une fois l'imbrication des instructions conditionnelles dans la procédure extraire, qui permet de lancer la procédure extraction, afin de récupérer les enregistrements correspondants.

Si nous choisissons une ville, nous restreignons encore un peu plus la liste des résultats pour atteindre la précision demandée. Si nous changeons de département et ne choisissons que la ville sans l'activité, nous obtenons la liste des idées de sorties pour la ville demandée. Notre application d'extraction des données selon des critères recoupés par des listes déroulantes en cascade fonctionne donc parfaitement.

Le code complet de la procédure extraire est le suivant :

Sub extraire()
Dim ligne_listes As Integer: Dim ligne_bd As Integer

If (Range('H5').Value = '' And Range('I5').Value = '' And Range('J5').Value = '') Then
Return
End If

ligne_listes = 9
While (Sheets('listes_cascade').Cells(ligne_listes, 2).Value <> '')
Sheets('listes_cascade').Cells(ligne_listes, 2).Value = ''
Sheets('listes_cascade').Cells(ligne_listes, 3).Value = ''
Sheets('listes_cascade').Cells(ligne_listes, 4).Value = ''
Sheets('listes_cascade').Cells(ligne_listes, 5).Value = ''
Sheets('listes_cascade').Cells(ligne_listes, 6).Value = ''
ligne_listes = ligne_listes + 1
Wend

ligne_bd = 2: ligne_listes = 9
Sheets('listes_cascade').Select
While (Sheets('bd_sorties').Cells(ligne_bd, 1).Value <> '')
If (Range('H5').Value <> '' And Range('I5').Value = '' And Range('J5').Value = '') Then
If (Sheets('bd_sorties').Cells(ligne_bd, 4).Value = Range('H5').Value) Then
extraction ligne_listes, ligne_bd
ligne_listes = ligne_listes + 1
End If
ElseIf (Range('H5').Value <> '' And Range('I5').Value <> '' And Range('J5').Value = '') Then
If (Sheets('bd_sorties').Cells(ligne_bd, 4).Value = Range('H5').Value And Sheets('bd_sorties').Cells(ligne_bd, 3).Value = Range('I5').Value) Then
extraction ligne_listes, ligne_bd
ligne_listes = ligne_listes + 1
End If
ElseIf (Range('H5').Value <> '' And Range('I5').Value = '' And Range('J5').Value <> '') Then
If (Sheets('bd_sorties').Cells(ligne_bd, 4).Value = Range('H5').Value And Sheets('bd_sorties').Cells(ligne_bd, 5).Value = Range('J5').Value) Then
extraction ligne_listes, ligne_bd
ligne_listes = ligne_listes + 1
End If
ElseIf (Range('H5').Value <> '' And Range('I5').Value <> '' And Range('J5').Value <> '') Then
If (Sheets('bd_sorties').Cells(ligne_bd, 4).Value = Range('H5').Value And Sheets('bd_sorties').Cells(ligne_bd, 3).Value = Range('I5').Value And Sheets('bd_sorties').Cells(ligne_bd, 5).Value = Range('J5').Value) Then
extraction ligne_listes, ligne_bd
ligne_listes = ligne_listes + 1
End If
End If
ligne_bd = ligne_bd + 1
Wend
End Sub