Techniques d'extraction de données avec Excel

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


Sujets que vous pourriez aussi aimer :


Techniques d'extraction de données

Les méthodes pour extraire de l'information de base de données sont nombreuses. Elles varient selon le contexte. Leur dessein peut différer. On peut par exemple exploiter l'information ainsi isolée pour nourrir des listes déroulantes reliées en cascade ou fournir des résultats de synthèse particulièrement affinés. Ces différents modules proposent des techniques et des situations.

Module 1 sur 10 : Intersections de plages



Ici, il est question d'extraire l'information textuelle ou de consolider les données numériques au croisement des plages de cellules pour offrir des bilans dynamiques instantanés. Vous découvrez une technique simple capable de remplacer les fonctions Excel d'extraction telles que Index, Equiv et RechercheV. Elle consiste à énumérer des plages de cellules croisées. L'espace fait office d'opérateur d'intersection. Ainsi, toutes les données numériques situées au croisement peuvent être consolidées tandis que les données textuelles peuvent être extraites.

Lien de la formation détaillée | Liende la vidéo

Module 2 sur 10 : Critères recoupés



Dans ce module, il s'agit d'extraire de l'information de bases de données Excel par formules croisant des critères spécifiés par le biais de listes déroulantes reliées entre elles. Ce volet est la conclusion de deux développements précédents. Nous avions réussi à reconstituer des sources de données purgées de leurs doublons. Puis, nous avions réussi à les relier entre elles pour faire interagir les listes déroulantes ainsi générées. L'utilisateur, par le biais de trois listes déroulantes dynamiques, désigne un département puis une activité et une ville recensées pour le choix réalisé en amont. Instantanément, les enregistrements concordant avec les choix recoupés, sont extraits dans la feuille Excel avec les détails des champs. Un calcul intermédiaire dans la base de données permet de repérer par incréments numériques, les enregistrements vérifiant les trois conditions à la fois. Pour ce faire, vous énumérez ces contraintes dans une fonction Excel ET. Elle doit elle-même être imbriquée dans une fonction conditionnelle Si. Ensuite, avec l'imbrication des fonctions Excel Index et Equiv, vous réalisez l'extraction de chaque champ des enregistrements concordants, grâce à un numéro de ligne et un numéro de colonne dans la source de données. Le numéro de colonne est fixe car il est connu. Le numéro de ligne dépend des choix en cascade effectués par le biais des listes déroulantes. Il est repéré par un nombre incrémenté. Vous trouvez ce numéro grâce à la fonction Excel Ligne imbriquée dans la fonction Equiv.

Lien de la formation détaillée | Lien de la vidéo

Module 3 sur 10 : Extraction par fragments de textes



Votre mission est de bâtir un moteur de recherche capable d'extraire les enregistrements d'une base de données Excel en fonction de mots clés tapés et ce, quel que soit le champ du critère à recouper. Cette application Excel permet de mettre en pratique les fonctions de traitement des chaînes de caractères afin d'offrir de la souplesse et de l'efficacité dans l'extraction des données. L'utilisateur saisit un bout d'information textuelle dans une cellule. Des formules Excel doivent importer toutes les lignes concordantes, quelle que soit la nature du mot clé et quel que soit le champ de la base de données concerné par la requête. Pour cela, vous construisez dynamiquement un code de référence. Il doit être le fruit de l'assemblage des bouts de textes issus de chaque champ. Ainsi, vous créez par concaténation une chaîne unique, rassemblant toutes les informations remarquables pour chaque enregistrement. Vous exploitez les fonctions Excel Gauche et Cherche pour former cette chaîne. C'est un calcul intermédiaire qui doit réaliser la recherche du mot clé tapé dans le code reconstruit et assemblé. S'il est trouvé, l'enregistrement correspondant doit être marqué d'un numéro incrémenté. C'est ainsi que vous pourrez les importer tous indépendamment. Ensuite, vous devez enclencher les fonctions Index et Equiv pour extraire les données correspondant à la recherche et marquées par ce numéro incrémenté. Grâce à la fonction Ligne appliquée sur une cellule dont la référence varie avec le calcul, vous les repérez tous.

Lien de la formation détaillée | Lien de la vidéo

Module 4 sur 10 : Recherches sur une base externe



Dans ce module, votre mission est d'extraire les données d'une source externe reliée au classeur Excel par des liaisons dynamiques pour des mises à jour automatiques des calculs d'extraction. Ce cas pratique est la suite logique de la formation sur la connexion automatique à une base de données externe. Nous avions déjà constaté l'actualisation régulière des données sans l'intervention ni du code VBA, ni des calculs Excel. Ici vous prouvez que vous pouvez extraire de l'information depuis Excel, grâce aux calculs, comme si vous agissiez directement depuis la base de données centralisée. Vous attribuez de nouveaux noms aux différentes plages de cellules importées et liées dynamiquement à la source externe. Ces plages doivent être calquées sur les noms attribués par Excel lors des connexions. De fait, elles héritent des mêmes propriétés et deviennent dynamiques et évolutives, au gré des modifications engendrées depuis la base de données d'origine. De plus, ces nouveaux noms peuvent directement être exploités dans les formules. D'ailleurs, ils permettent de relier naturellement deux listes déroulantes entre elles. C'est ainsi que le contenu de la seconde liste déroulante doit s'adapter au choix réalisé dans la première liste, qui récupère indirectement le nom de la plage de cellules pour ajuster la source de données. C'est la fonction Excel Indirect qui permet d'établir cette relation en cascade très simplement. Finalement, sur la base de ces critères recoupés, vous produisez l'extraction de tous les enregistrements concordants. Et si de nouveaux enregistrements sont créés dans la base de données centralisée, ils sont automatiquement importés et extraits dans la feuille de calcul Excel. Vous parvenez donc à manipuler dynamiquement les données d'une source externe, depuis un classeur Excel, sans aucun code Visual Basic.

Lien de la formation détaillée | Lien de la vidéo

Module 5 sur 10 : Extraire les images d'une recherche



La vocation de cette solution est d'importer les photos et les textes des enregistrements de la base de données correspondant aux termes cherchés par les formules Excel et sans code VBA. Vous récupérez tout d'abord les travaux de la formation Visual Basic précédente. Elle avait permis d'importer les données multimédias d'une base externe. Les photos étant liées aux cellules par leurs propriétés, vous créez un premier calcul capable de retourner les références des cellules contenant les images. Vous repérez ensuite les enregistrements coïncidant avec la recherche grâce à un calcul intermédiaire. Il doit consister à identifier par un numéro incrémenté, chaque ligne correspondante dans la base de données. Vous produisez alors l'extraction des informations de texte, grâce à l'imbrication des fonctions Excel Index et Equiv. Vous obtenez en retour les enregistrements correspondant aux mots clés de recherche. Vous créez pratiquement la même formule pour extraire les photos de la recherche. Mais vous devez l'encapsuler dans la fonction Indirect pour que les références calculées soient interprétées et pointent bien sur l'objet Image. Il est nécessaire de figer toutes les références dans le calcul et de l'attacher à un nom de plage. C'est ensuite ce nom, associé à l'image, qui permet l'extraction dynamique des données multimédias, en fonction des expressions cherchées.

Lien de la formation détaillée | Lien de la vidéo

Module 6 sur 10 : Zone de recherche indéfinie



Vous créez une application Excel permettant de comparer les ventes annuelles réalisées par une entreprise. Cette confrontation dynamique doit s'opérer au choix des années à examiner. Un tableau d'une feuille annexe archive tous les chiffres réalisés pour chaque article vendu. Ces chiffres sont répertoriés par année. Dans la feuille principale de l'application, vous disposez de deux listes déroulantes pour choisir deux dates. Vous bâtissez un calcul d'extraction pour importer tous les chiffres des articles, selon l'information variable de colonne dépendant de l'année choisie. C'est la fonction Excel Adresse qui permet de pointer sur une ligne et une colonne dynamiques. La fonction ligne référence une cellule qui progresse en même temps que le calcul, afin de retourner toutes les valeurs d'une colonne. La fonction Equiv retourne dynamiquement l'indice de colonne correspondant à la date, pour produire l'extraction des résultats. Le tout doit être encapsulé dans la fonction Excel Indirect afin d'interpréter les références calculées en tant que telles. Enfin, vous montez une mise en forme conditionnelle avec des jeux d'icônes pour renforcer l'interprétation des résultats. La comparaison des progressions ou régressions entre deux années devient évidente.

Lien de la formation détaillée | Lien de la vidéo

Module 7 sur 10 : Moteur de recherche



Obtenir des résultats de recherche affinés pour extraire les enregistrements d'une base de données recoupant tous les mots clés saisis par l'utilisateur est l'objectif de la solution à livrer. Vous récupérez les travaux réalisés au cours de deux formations précédentes. La première avait permis d'importer les enregistrements d'une source externe, avec leurs images attachées dans les cellules correspondantes. La seconde fut l'occasion de créer une fonction VBA pour supprimer les accents des mots. Son but est de permettre des comparaisons insensibles aux caractères latins. Ici, en découpant et en rangeant tous les mots clés de recherche dans un tableau de variables, vous parvenez à déceler leur présence dans les enregistrements de la source de données. C'est l'imbrication de deux boucles VBA qui permet de parcourir toutes les lignes et pour chacune d'entre elles, de comparer tous les mots clés. Si seul l'un d'entre eux n'est pas trouvé, la recherche est considérée comme infructueuse. De fait, l'enregistrement n'est pas extrait et le traitement se poursuit sur les suivants. C'est la fonction VBA InStr qui permet de repérer la présence des mots clés dans les champs de la base de données. Elle doit être associée à votre fonction VBA pour supprimer les accents dans la comparaison. Avec un code Visual Basic finalement simple, vous produisez un moteur de recherche Excel puissant, permettant de saisir autant de mots clés que souhaité pour des résultats cohérents et ciblés.

Lien de la formation détaillée | Lien de la vidéo

Module 8 sur 10 : Recherche multi-source



Dans ce volet, il s'agit de trouver une information située dans l'une des bases de données pour extraire tous les résultats correspondants. L'enjeu consiste donc à adapter la plage de cellules de recherche. Vous débutez les travaux à partir d'un classeur source. Celui-ci est constitué de quatre feuilles. Trois d'entre elles sont des bases de données de véhicules, identifiés par leur immatriculation. Depuis la première feuille, vous devez permettre la recherche d'une automobile par cette immatriculation. Mais nous ne savez pas dans quel tableau elle se situe. Vous réalisez une recherche intermédiaire qui identifie la source de données. Puis, grâce aux fonctions RechercheV et RechercheH, vous produisez l'extraction des informations liées, sur la source de données dynamique ainsi retournée.

Lien de la formation détaillée | Lien de la vidéo

Module 9 sur 10 : Critères croisés ou isolés



Ici, vous devez monter une solution Excel pour extraire les véhicules d'un parc automobile sur des contraintes précises et recoupées ou isoler les automobiles selon des conditions plus larges. Vous construisez donc un outil souple et ergonomique permettant à un garage ou concessionnaire de visualiser rapidement toutes les voitures d'occasion correspondant aux critères du client. La recherche peut être affinée sur la marque, le modèle, le prix et le kilométrage. Mais un client peut très bien rechercher un véhicule seulement dans sa fourchette de prix avec un plafond kilométrique. L'enjeu de ce développement consiste donc à livrer des résultats sur une ou plusieurs conditions pour plus de souplesse et pour plus de puissance.

Lien de la formation détaillée | Lien de la vidéo

Module 10 sur 10 : Les expressions régulières en VBA Excel



Vous allez apprendre à purger les extractions de chaînes de caractères avec les expressions régulières en Visual Basic Excel. L'objectif est de restituer des données nettoyées et explicites. Vous terminez ainsi l'application du moteur de recherche Excel avec saisie semi-automatique sur la base de fichiers de cache, construits par un code serveur d'un site Web, en fonction des termes de recherches effectuées par les internautes. Cette application avait récolté et consolidé les données pour offrir les mots clés de recherche au code VBA. Celui-ci a permis de les extraire dans un tableau de variables pour les comparer au fil de la saisie avec les expressions tapées par l'utilisateur. C'est ainsi que des suggestions intuitives lui sont offertes en temps réel. A validation, le fichier de cache devant être restitué, il s'agissait de le nettoyer des balises Html qui sont prévues pour une restitution instantanée sur une page Internet. Ce sont les expressions régulières qui permettent de réaliser cette prouesse. Pour cela, vous instanciez la classe vbscript.regexp grâce à la fonction CreateObject. Dès lors vous pouvez construire des motifs grâce à la propriété Pattern, afin de déceler la présence de chaînes de texte remarquables mais non définies à l'avance. Les expressions régulières permettent ainsi d'éliminer toutes les balises Html et fragments de texte indésirables, tout en insérant un caractère remarquable de séparation. Celui-ci sert à la découpe des résultats indépendants par la fonction VBA Split. De fait, vous engagez une boucle parcourant toutes les rangées du tableau ainsi généré à la volée pour restituer les réponses en adéquation avec la demande utilisateur, par le biais de la saisie semi-automatique dans le moteur de recherche VBA Excel.

Lien de la formation détaillée | Lien de la vidéo