Recherche d'informations sur une base de données externe

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


Sujets que vous pourriez aussi aimer :


Listes en cascade et extraction de données externes

Dans ce troisième volet sur la liaison dynamique des données entre Excel et Access, nous proposons de construire les calculs, au demeurant très simples, permettant d'extraire les informations. Cette extraction doit se faire sur la base de critères recoupés, par des listes déroulantes reliées entre elles.



En effet, dans les deux volets précédents, nous avions préparé les données, depuis la base de données source, grâce à des requêtes. Puis nous avions établi la communication dynamique avec ces données, depuis un classeur Excel. Il ne nous reste plus qu'à les manipuler, par ce que fait de mieux Excel, les calculs. L'intérêt ultime est de constater l'évolution dynamique des résultats d'extraction, du fait des liaisons dynamiques, paramétrées dans le deuxième volet.

Sources et présentation de la problématique
Nous proposons de débuter les travaux là où nous les avions arrêtés. Néanmoins, les liaisons dynamiques définies en chemin absolu, ont dû être coupées, pour les besoins du téléchargement. Le classeur Excel et la base de données Access ne sont plus liés, ce qui n'empêche pas la mise en oeuvre de ce troisième volet, mais ne permet pas l'actualisation dynamique. Vous pouvez soit télécharger ces sources et refaire les liaisons, soit récupérer les travaux que nous avions aboutis, lors du deuxième volet.

• Télécharger le fichier extraction-dynamique.rar en cliquant sur son lien,
• Le décompresser dans le dossier de votre choix,
• Double cliquer sur le fichier extraction-dynamique.xlsx pour l'ouvrir dans Excel,
• Cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons notre classeur composé de trois feuilles. La feuille Marques restitue les données des requêtes de la base source, par liaisons dynamiques. Toutes ces listes sont purgées de leurs doublons grâce à l'instruction DISTINCT des requêtes SQL, que nous avons mises en oeuvre lors du premier volet. Ce sont elles qui vont permettre la construction des listes et de leurs dépendances, sans code VBA. La feuille Lien_base restitue les informations de la table Parc, toujours par liaison dynamique. C'est elle qui va servir à l'extraction des données en fonction des choix recoupés. La première feuille, Extraire, propose donc naturellement la structure pour recevoir les listes déroulantes et les calculs.

Listes déroulantes dépendantes
Ces listes doivent permettre de formuler des critères en cascade, pour déclencher les calculs d'extraction. Une marque de véhicule doit être définie avec la première liste déroulante. Un modèle correspondant à cette marque doit être spécifié à l'aide de la seconde liste. Cette deuxième liste déroulante doit donc adapter son contenu en fonction du choix réalisé dans la première.

Pour produire ces relations sans effort, nous devons attribuer des noms aux plages de données. Ces noms calqués sur ceux construits par les connexions, fourniront des données dynamiques. C'est la correspondance entre ces noms qui va établir la relation de dépendance entre les listes déroulantes.

• Cliquer sur l'onglet Marques en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner les cellules de la première extraction, celles des marques de véhicules, soit la plage de cellules B5:B13,
• Dans la zone Nom, à gauche de la barre de formule, remplacer le texte R_Marques par le nom Marques,
• Puis, valider la saisie par la touche Entrée,

R_Marques est le nom attribué à la liste extraite par défaut. Cette liste raisonne sur une plage évolutive, du fait de la connexion dynamique. En la renommant, nous définissons une plage de cellules dynamique calquée et exploitable pour les calculs, avec résultats évolutifs. Nous devons procéder de la même façon pour renommer chaque plage importée dynamiquement par les liaisons.

• Sélectionner la cellule C5 et la renommer Audi,
• Renommer la cellule D5 en Bmw,
• Renommer la cellule E5 en Citroen,
• Renommer la plage de cellules F5:F6 en Fiat,
• Renommer la plage G5:G7 en Mercedes,
• Renommer la plage H5:H6 en Nissan,
• Renommer la plage I5:I14 en Peugeot,
• Renommer la plage J5:J18 en Renault,
• Renommer la plage K5:K6 en Volkswagen,
• Cliquer ensuite sur l'onglet Formules en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

La boîte de dialogue Gestionnaire de noms s'affiche comme l'illustre la capture ci-dessus. Comme vous le Remarquez, la troisième colonne (Fait référence à), corrobore ce que nous annoncions plus haut. Chacune des plages de cellules ainsi renommée est basée sur la plage de cellules, issue de l'extraction dynamique. Ainsi, à chaque mise à jour, par le biais de la liaison externe, si de nouvelles données apparaissent, les bornes des plages évoluent. En les exploitant dans les calculs, nous nous assurons donc de produire des résultats évolutifs, tenant compte des modifications apportées dans la base de données centralisée.

Il est temps d'exploiter ces informations dynamiques pour construire et relier entre elles, les deux listes déroulantes destinées à proposer des informations adaptatives et évolutives.

• Cliquer sur l'onglet Extraire en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule B5 destinée à recevoir la première liste déroulante,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer sa feuille,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation de données,
• Activer l'onglet Options de la boîte de dialogue qui suit,
• Dans la zone Autoriser, choisir Liste,
• Puis, cliquer dans la zone Source située juste en dessous pour l'activer,
• Taper alors la formule suivante : =Marques, et valider par le bouton Ok,

Comme l'illustre la capture ci-dessus, nous venons de construire la liste déroulante parente, celle des marques de véhicules. Elle est basée sur la plage nommée Marques, elle-même calquée sur la source dynamique R_Marques, issue de l'extraction. Elle fait donc indirectement référence à la requête de la base de données source, permettant d'extraire toutes les marques mises à jour, sans doublons.

La seconde liste déroulante doit lui être liée. Les modèles qu'elle doit proposer doivent correspondre à ceux de la marque sélectionnée en amont, par le biais de la première liste déroulante. Le choix dans la première correspond au nom de la plage à charger dans la seconde. C'est pour cela que nous avons attribué des noms concordants. De fait, pour réaliser cette liaison, il suffit d'indiquer à Excel que le contenu de la seconde liste déroulante, est celui de la plage dont le nom correspond au choix dans la première. Et c'est la fonction Excel Indirect qui permet de réaliser ce lien naturel.

• Sélectionner cette fois la cellule D5 pour construire la liste déroulante dépendante,
• Dans le ruban Données, cliquer sur le bouton Validation de données,
• Dans la zone Autoriser de la boîte de dialogue qui suit, choisir Liste,
• Cliquer dans la zone Source située juste en dessous pour l'activer,
• Taper la formule suivante : =Indirect($B$5), puis valider par Ok et confirmer par Oui le message d'erreur,

La fonction Excel Indirect, comme son nom l'indique, force à relier la source de données de la liste déroulante, à la plage de cellules portant le nom correspondant au choix réalisé dans la première liste déroulante. Le message d'erreur peut survenir dans le cas où aucun choix n'a été réalisé dans la première liste déroulante. Dans ce contexte bien entendu, Excel ne parvient pas à établir la relation.

• A l'aide de la première liste déroulante, sélectionner une marque, comme : Peugeot,
• Dérouler alors la seconde liste déroulante,

Comme vous le constatez, son contenu est en relation avec le choix réalisé dans la première liste déroulante. Seuls les modèles de la marque Peugeot sont proposés. Et comme nous l'avons dit plus haut, cette plage étant évolutive par le jeu des liaisons dynamiques aux données, si un modèle de la marque Peugeot est ajouté dans la base de données centralisée, il est mis à jour automatiquement et proposé naturellement dans la liste déroulante.



Grâce aux connexions dynamiques, ces listes déroulantes ont donc pu être construites et reliées sans difficultés. Nous les avons bâties sans code VBA et sans réel calcul. Seule la fonction Indirect a été nécessaire pour établir la liaison.

Nous devons maintenant vérifier que la mise à jour s'opère réellement. Pour cela, vous devez avoir rétabli les connexions ou récupéré le classeur de la formation précédente. L'objectif consiste à ajouter de nouveaux véhicules ainsi que de nouveaux modèles depuis la base de données source. Ainsi, après une nouvelle actualisation, paramétrée toutes les cinq minutes, nous devrions constater l'intégration de ces nouvelles informations, importées automatiquement.

• Ouvrir la base de données Access donnees-externes.accdb,
• Dans l'explorateur d'objets sur la gauche, double cliquer sur le formulaire Parc pour l'exécuter,
• Dans la barre de navigation en bas du formulaire, cliquer sur le bouton Nouvel enregistrement,
• Saisir l'immatriculation suivante : AF138JI,
• Choisir la marque Mercedes à l'aide de la première liste déroulante,
• Saisir ensuite le modèle Classe A qui n'existe pas dans la seconde liste déroulante,

Comme ce modèle n'existe pas encore, il est forcément absent du classeur source, relié à cette base. Mais lors de la prochaine mise à jour automatique, il devrait apparaître. Comme nous avions réglé la propriété Limiter à liste sur non pour ce contrôle, nous avons le droit d'ajouter de nouvelles informations. Et comme cette seconde liste se charge selon une requête au critère dynamique, elle doit proposer cette nouvelle donnée, à la prochaine utilisation.

• Enfin saisir le nombre 90 dans la zone Chevaux,
• Puis, cliquer de nouveau sur le bouton Nouvel enregistrement en bas du formulaire,
• Saisir l'immatriculation : KH875MD,
• Choisir la marque Fiat puis saisir le modèle Panda,
• Taper le nombre 75 dans la zone Chevaux,
• Fermer ensuite le formulaire ainsi que la base de données Access,
• Revenir sur le classeur Excel et activer la feuille Marques,

Moyennant un laps de temps nécessaire de 5 minutes pour la connexion dynamique à la base de données, vous remarquez que les informations sont parfaitement mises à jour automatiquement. Les deux nouveaux modèles apparaissent en effet dans leur colonne respective. Les plages nommées ont adapté leurs bornes pour les intégrer. C'est ce que nous allons prouver.

• Cliquer sur l'onglet Extraire en bas de la fenêtre Excel pour activer sa feuille,
• A l'aide de la première liste déroulante, choisir la marque Fiat,
• Puis, dérouler la seconde liste déroulante,

Le nouveau modèle est en effet proposé, sans que nous soyons intervenu sur la feuille. Les connexions dynamiques se sont chargées d'importer automatiquement les nouvelles informations issues de la base de données Access.

Extraire les données issues des liaisons dynamiques
Si vous affichez la feuille Lien_base, pour les mêmes raisons, vous notez la présence des deux nouveaux véhicules importés automatiquement. Ce tableau est lié à la table Parc qui recense tous les véhicules de la société. C'est donc à partir de la plage nommée qui lui a été attribuée, que nous devons réaliser les calculs d'extraction. Dans la feuille Extraire, ils consistent à importer toutes les informations des véhicules correspondant aux deux critères recoupés par les listes déroulantes. Bien entendu, l'objectif final consistera à vérifier que pour une même requête, les données d'extraction peuvent évoluer, si des modifications ont été apportées dans la base de données source.

Comme nous l'avait appris la formation Excel pour extraire les données recoupées, nous devons bâtir un calcul intermédiaire sur le tableau de la feuille Lien_base. Il doit consister à repérer par des numéros incrémentés, tous les enregistrements concordant avec les deux critères émis par les listes déroulantes. La fonction Excel conditionnelle Si est donc incontournable. Et pour vérifier deux conditions à la fois, nous devons les énumérer dans sa zone de critère, grâce à la fonction Excel ET.

• Sélectionner la cellule F5 de la feuille Lien_base,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit : Si(,
• Saisir la fonction exclusive pour énumérer les critères, suivie d'une parenthèse, soit : Et(,
• Taper la référence de la première cellule pour la marque, soit : B5,

En effet, comme il s'agit de données importées par connexion dynamique, elles sont encapsulées dans des plages nommées par Excel. Pour plus de clarté, nous choisissons donc d'écrire explicitement les références des cellules impliquées dans le calcul.

• Taper le symbole = pour l'égalité du critère à vérifier,
• Cliquer sur l'onglet Extraire en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule de la liste des marques ou taper sa référence, soit : B5,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne Extraire!$B$5,

Pour chaque enregistrement en effet, nous devons vérifier si la marque est identique à celle désignée depuis la liste déroulante. Cette cellule de référence ne bouge pas, elle doit donc être figée. Nous devons désormais vérifier que les modèles correspondent conjointement.

• Taper un point-virgule (;) pour passer dans la condition suivante à énumérer,
• Cliquer sur l'onglet Lien_base pour revenir sur sa feuille,
• Saisir la référence du premier modèle, soit : C5,
• Taper le symbole = pour l'égalité du second critère à vérifier,
• Cliquer sur l'onglet Extraire en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner ou taper la référence de la liste des modèles, soit : D5,
• Enfoncer la touche F4 du clavier, ce qui donne : Extraire!$D$5,
• Fermer la parenthèse de la fonction Et,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction pour extraire la plus grande valeur d'une plage, suivie d'une parenthèse, soit : Max(,
• Revenir sur la feuille Lien_base en cliquant sur son onglet,
• Sélectionner la cellule située juste au-dessus du calcul, soit : F4,
• Taper le symbole deux points (:) pour générer la plage F4:F4,
• Figer seulement la première des deux, ce qui donne : $F$4:F4,
• Fermer la parenthèse de la fonction Max,
• Ajouter une unité à ce résultat, soit : + 1,

La plage de cellules ainsi désignée doit grandir en hauteur en même temps que le calcul est répliqué vers le bas. C'est la raison pour laquelle nous avons figé sa borne supérieure et laissé libre sa borne inférieure. Ainsi, à chaque fois qu'un précédent résultat sera trouvé, il sera incrémenté d'une unité.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets d'affilée ('') pour conserver la cellule vide lorsque les deux critères ne sont pas vérifiés ensemble,
• Fermer la parenthèse de la fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée,

Du fait de la liaison dynamique, le calcul est instantanément reproduit sur la hauteur du tableau lié et importé. Cette nouvelle colonne a automatiquement été intégrée dans le nom donné à la plage dynamique, liée à la source de données Access. En conséquence, elle s'adaptera en hauteur, au gré des mises à jour réalisées dans la base de données centralisée.

• Cliquer sur l'onglet Extraire en bas de la fenêtre Excel, pour activer sa feuille,
• Sélectionner la marque Peugeot à l'aide de la première liste déroulante,
• Choisir le modèle 308 avec la seconde liste qui lui est liée,
• Revenir sur la feuille Lien_base en cliquant sur son onglet,

Comme vous le remarquez, des repères numériques incrémentés se sont implantés en regard de chaque enregistrement concordant. Les critères recoupés par les listes déroulantes liées, imposaient en effet de repérer les modèles 308 pour la marque Peugeot. La formule que nous avons bâtie est la suivante :

=SI(ET(B5=Extraire!$B$5; C5=Extraire!$D$5); MAX($F$4:F4) + 1; '')

Il ne reste plus qu'à exploiter ces résultats pour produire l'extraction. Le principe consiste à rechercher chaque numéro renseigné dans la colonne F de la feuille Lien_base. Cette recherche doit s'effectuer sur la base d'un numéro qui s'incrémente lui aussi, en même temps que le calcul est répliqué. Nous exploiterons donc la fonction Excel Ligne, qui permet de retourner l'indice de ligne de la cellule qui lui est passée en paramètre. Et sur la base de ce numéro, c'est l'imbrication des fonctions Index et Equiv qui va permettre l'extraction des enregistrements correspondants. Leurs syntaxes sont les suivantes :

Index(tableau_de_recherche ; indice_ligne ; indice_colonne)
Equiv(valeur_cherchée ; colonne_de_recherche ; 0)

Le tableau de recherche est celui de la feuille Lien_base. Nous allons le renommer, comme nous l'avons fait pour les plages de requêtes, afin de simplifier le calcul dynamique. L'indice de ligne dépend de la position du numéro incrémenté. Il sera transmis par la fonction Equiv, imbriquée dans ce deuxième argument. L'indice de colonne est connu, selon qu'il s'agit d'extraire la marque, le modèle, l'immatriculation ou les chevaux.

La valeur cherchée est donc le numéro à incrémenter, produit par la fonction Ligne. La colonne de recherche est celle du calcul des numéros incrémentés, soit la colonne F de la feuille Lien_base. Le dernier argument fixé à 0 permet de réaliser une recherche selon une correspondance exacte. Nous imbriquerons ce calcul d'extraction dans une fonction SiErreur. Cette dernière permet de gérer les erreurs lorsque les éléments cherchés ne sont pas trouvés ou pas fournis.

Mais avant cela, il est nécessaire d'attribuer un nom à la plage dynamique de la source de données.

• Sur la feuille Lien_base, sélectionner toutes les données dynamiques, soit la plage de cellules B5:F66,
• Dans la zone nom, changer le nom Parc par bd et valider avec la touche Entrée,

Comme précédemment, bien que le nom Parc s'affiche de nouveau en priorité, la plage bd est désormais calquée sur ce modèle d'importation dynamique et évolutif. Si vous ouvrez le gestionnaire de noms, vous le constatez. Grâce à cette manipulation, nous pouvons désormais désigner cette source par ce nouveau nom, dans les calculs d'extraction. C'est l'enchaînement que nous proposons de résoudre. Il s'agit de commencer par récupérer toutes les marques issues de la base de données et correspondant aux critères recoupés.

• Cliquer sur l'onglet Extraire en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la première cellule de la zone d'extraction, soit : B8,
• Taper le symbole = pour débuter le calcul,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Taper le nom de la fonction d'extraction suivi d'une parenthèse, soit : Index(,
• Saisir le nom du tableau de recherche, soit : bd,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction retournant l'indice de ligne suivie d'une parenthèse, soit : Equiv(,
• Taper le nom de la fonction renseignant sur le numéro de ligne d'une cellule, suivi d'une parenthèse, soit : ligne(,
• Sélectionner la cellule A1 pour débuter la recherche sur le premier numéro, soit le chiffre 1,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Cliquer sur l'onglet Lien_base en bas de la fenêtre Excel pour activer sa feuille,
• Désigner la colonne F complète en cliquant sur son étiquette, ce qui donne : Lien_base!F:F,
• Taper un point-virgule (;) suivi du chiffre 0 pour indiquer une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,
• Retrancher 4 unités à ce résultat, soit -4,

Nous sommes de retour dans les bornes de la fonction Index. A ce stade, la fonction Equiv est censée retourner l'indice de ligne du premier enregistrement correspondant aux critères, selon la position du premier chiffre trouvé. Nous avons soustrait 4 unités à son résultat puisque la recherche dans la base de données débute à la ligne 5 (5-4=1). Il reste à indiquer l'indice de colonne. Il est fixe. Pour la marque, il s'agit de la première.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,
• Saisir le chiffre 1,
• Fermer la parenthèse de la fonction Index,

Nous sommes de retour entre les bornes de la fonction SiErreur. Nous venons de construire le calcul à réaliser lorsqu'aucune embûche n'est rencontrée. En cas de souci, nous devons maintenant lui indiquer comment réagir.

• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas de souci,
• Valider la formule à l'aide du raccourci clavier CTRL + Entrée,

Pour les deux critères précédemment recoupés, nous obtenons en retour, l'extraction de la marque Peugeot, ce qui est parfaitement cohérent. Avant de répliquer ce calcul, nous souhaitons l'adapter pour les autres champs. Pour extraire le modèle, l'immatriculation et les chevaux, il suffit simplement de modifier la valeur de l'indice de colonne. Le chiffre 1 est à modifier par le chiffre 2 puis 3 et enfin 4 pour récupérer les chevaux. Le plus simple consiste à copier et coller le calcul, de barre de formule à barre de formule et à adapter la valeur de l'indice de colonne. Il est important de bien valider cette modification avec la touche Entrée, et non en cliquant dans une cellule avec la souris.

• Adapter le calcul pour les trois autres champs,
• Sélectionner alors la première ligne du calcul d'extraction, soit la plage de cellules B8:E8,
• Tirer la poignée de la sélection sur plusieurs dizaines de lignes vers le bas,

La formule que nous construite est la suivante :

=SIERREUR(INDEX(bd; EQUIV(LIGNE(A1); Lien_base!F:F; 0)-4; 1); '')

Nous produisons bien l'extraction dynamique de tous les enregistrements concordant avec les deux critères recoupés, formulés par les deux listes déroulantes reliées entre elles. En répliquant le calcul sur les lignes du dessous, la fonction Ligne retourne à chaque fois un nombre incrémenté, parti du chiffre 1. La fonction Equiv repère tour à tour l'emplacement des numéros incrémentés en retournant l'indice de ligne correspondant, dans la base de données. La fonction Index n'a plus qu'à extraire l'information correspondante, selon le numéro de colonne qui lui est donné. Nous obtenons bien toutes les 308 de la marque Peugeot.

• Dans la première liste déroulante, choisir la marque Mercedes cette fois,
• Avec la seconde liste, sélectionner le modèle Classe A,

Il s'agit du véhicule que nous avons précédemment ajouté depuis la base de données source. Nous confirmons une fois encore son importation dynamique, puisque les calculs d'extraction l'intègrent, sans aucune intervention dans la structure des feuilles. Pour une dernière vérification, nous proposons de créer une 308 de 170 chevaux qui n'existe pas encore et donc, qui ne peut être extraite.

• Ouvrir la base de données donnees-externes.accdb,
• Depuis le volet des objets Access, double cliquer sur le formulaire Parc pour l'ouvrir,
• Cliquer sur le bouton Nouvel enregistrement en bas du formulaire,
• Saisir l'immatriculation suivante : MO678DV,
• A l'aide de la première liste déroulante, choisir la marque Peugeot,
• Avec la seconde liste déroulante, sélectionner le modèle 308,
• Saisir 170 dans la zone de texte Chevaux,
• Fermer le formulaire Access ainsi que la base de données,
• Revenir sur la feuille Extraire du classeur Excel,

Une fois la mise à jour opérée par l'actualisation de la connexion à la base de données externe, les résultats d'extraction s'ajustent et intégrent les nouvelles informations. Si vous ne souhaitez pas attendre, vous pouvez cliquer sur le bouton Actualiser tout du ruban Données.