Exportations sélectives 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 :


Importations et exportations sélectives avec Excel

De nombreuses entreprises sont amenées à dépouiller des données denses, issues de serveurs et consolidant de nombreuses informations. Pour des besoins statistiques et stratégiques, il est souvent nécessaire de simplifier et d'isoler les informations afin de mieux les décortiquer et les exploiter. Dans cette formation, nous proposons de travailler sur deux sources externes. La problématique consiste à pouvoir isoler seulement les données pertinentes pour des besoins professionnels. Ainsi, elles pourront être livrées et exportées pour de exploitations optimisées.



Source et présentation de la problématique
Pour réaliser des exportations sélectives, encore faut-il disposer de données à manipuler. C'est pourquoi nous proposons de les réceptionner dans un premier temps.

• Télécharger le classeur donnees-a-filtrer.xlsx en cliquant sur son lien,
• Puis, double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Cliquer ensuite sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur est constitué de deux feuilles nommées respectivement products et communes. Le tableau de la feuille products est le résultat d'une extraction de base de données MySql. Des articles en vente sont détaillés sur plusieurs champs.



Nous souhaitons récupérer ces données pour les manipuler dans une base de données Access. Cependant, elles doivent être nettoyées en amont. Tous les articles dont la quantité en stock est nulle sont considérés comme obsolètes, et ils sont nombreux.

• En bas de la fenêtre Excel, cliquer sur l'onglet Communes pour activer sa feuille,

Ce tableau recense toutes les communes de France avec les codes postaux notamment. Pour les besoins d'une application professionnelle, ces communes doivent être importées dans une base de données Access. Mais l'entreprise intéressée agit sur la région PACA. Donc, seules les communes des départements 04, 05, 06, 13, 83 et 84 doivent être importées. De plus, les informations sur le code INSEE et les coordonnées GPS ne sont pas désirées.

Extraire les données sur critères
Afin de préparer les informations à importer, nous devons créer des tableaux purgés des données non souhaitées. Deux solutions s'offrent à nous. La première consiste à exploiter les fonctions Excel de bases de données. La seconde consiste à utiliser les filtres avancés. Et c'est cette seconde technique que nous choisissons de mettre en oeuvre dans ce cas pratique.

Cette fonctionnalité consiste à appliquer une zone de critères sur le tableau à filtrer pour produire une extraction sélective des données vers un autre emplacement.

• En bas de la fenêtre Excel, cliquer sur l'onglet products pour activer sa feuille,

La zone d'extraction doit débuter par les noms des champs souhaités pour produire le tableau retravaillé. Il est possible de restreindre le niveau de détail en excluant des colonnes dans l'énumération.

Cette zone d'extraction est déjà prévue sur cette feuille. Elle débute en ligne 8 à partir de la colonne I. Et comme vous le remarquez, tous les champs sont énumérés. Nous souhaitons donc récupérer toutes les informations de cette base de données pour lesquelles les quantités en stock ne sont pas nulles. Dans le même temps, nous souhaitons exclure les enregistrements proposant des stocks farfelus. C'est le cas en ligne 138 par exemple avec la quantité 999947. Nous suggérons donc d'extraire les produits pour lesquels la quantité est comprise en 0 et 100 par exemple. Cela signifie que nous devons recouper deux critères. Et comme nous l'avions appris, lorsque des conditions sont à croiser, elles doivent être placées sur la même ligne dans la zone de critères.

Cette zone de critères est prévue en cellule I1.

• Sélectionner la cellule I1 et la copier (CTRL + C),
• Sélectionner la cellule J1 sur sa droite et la coller (CTRL + V),

Les filtres avancés analysent les conditions posées sur des champs. C'est pourquoi nous répliquons précisément leur nom.

• En cellule I2, saisir le critère suivant : >0,
• En cellule J2, taper la condition suivante : <=100,

Certes la mise en forme n'est pas homogène. On note des défauts d'alignement. Peu importe, ces cellules servent d'intermédiaire pour produire les résultats.

Les conditions sont posées sur la même ligne de la zone de critères et pour le même champ. Nous demandons explicitement d'extraire les enregistrements pour lesquels la quantité en stock est à la fois strictement supérieure à zéro et inférieure ou égale à Cent.

• Cliquer n'importe où dans le tableau à nettoyer, par exemple en cellule B3,

En activant une cellule située à l'intérieur du tableau, les filtres avancées détecteront automatiquement les bornes de ce dernier.

• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Trier et filtrer du ruban, cliquer sur le bouton Avancé,
• Dans la boîte de dialogue qui apparaît, cocher la case Copier vers un autre emplacement,

Comme vous le remarquez, Excel a bien défini les bornes du tableau à analyser. Il s'agit de la plage de cellules $A$1:$G$849 mentionnée dans la zone Plages. Cette base de données est donc constituée de 849 lignes, soit de 848 enregistrements en excluant la ligne de titre.

• Dans la zone de critères, sélectionner à la souris la plage de cellules I1:J2,

Nous indiquons ainsi à Excel quelles sont les conditions à recouper et sur quels champs les appliquer.

• Dans la zone Copier dans, sélectionner à la souris la plage de cellules I8:O8,

Excel attribue automatiquement un nom à cette plage du fait de manipulations antérieures qui ont eu lieu sur ce classeur. Quoiqu'il en soit, la plage de cellules pour le début de l'extraction est bien désignée.

• Cliquer sur le bouton Ok pour procéder à l'extraction,

Les enregistrements sont effectivement extraits dans le tableau prévu à cet effet, tout en conservant la ligne d'entête pour les noms des champs. Et comme vous le remarquez, toutes les lignes avec un stock nul ont été exclues. En parcourant le tableau, nous remarquons de même que plus aucune quantité supérieure à 100 ne subsiste. L'extraction est donc un succès. Et pour preuve, le dernier enregistrement est inscrit sur la ligne 250, sachant que le tableau débute à partir de la ligne 8. Il reste donc environ 240 enregistrements sur les 848 de départ.

C'est sur cette base extraite et purgée que nous allons pouvoir réaliser l'exportation sélective. Et pour parfaitement maîtriser les données à importer en base de données Access, nous allons attribuer un nom à ce tableau. La formation Access pour importer des données précises avait démontré l'intérêt de cette manipulation intermédiaire.

• Sélectionner la toute première cellule du tableau d'extraction, soit la cellule I8,
• Tout en maintenant la touche MAJ enfoncée, cliquer sur la toute dernière valeur extraite, soit la cellule O250,

Cette technique, démontrée dans le support des trucs et astuces pour Excel, permet d'inclure toutes les cellules situées entre la première et la dernière.

• Dans la zone Nom, en haut à gauche de la feuille Excel, saisir le nom Produits,
• Puis, valider par la touche Entrée du clavier,

Il est impératif de valider ce nom par la touche Entrée. Le cas échéant, il n'est pas pris en compte.

Le tableau est désormais reconnu par un identifiant le délimitant explicitement des autres données présentes sur la même feuille.

Conditions non exclusives
Comme nous le disions, sur le tableau de la seconde feuille, nous souhaitons isoler les enregistrements de la région PACA. Et seule l'information sur 2 des champs doit être conservée. Ces critères doivent être regroupés et non recoupés. En effet et par exemple, un département ne peut pas être à la fois le 13 et le 83. Il est soit l'un, soit l'autre. Dans la construction des critères, nous ne devons pas exprimer l'opération ET, mais l'opération OU. Dans ce contexte, les conditions ne se placent pas les unes à côté des autres. Elles doivent être positionnées les unes en dessous des autres.

• En bas de la fenêtre Excel, cliquer sur l'onglet communes pour activer sa feuille,

La zone d'extraction est prévue à partir de la ligne 10, entre les colonnes G et H. Comme nous l'évoquions, elle est restrictive sur le détail de l'information à extraire. Seuls les champs pour la commune et le code postal sont prévus. La zone de critères est située sur la ligne 1, entre les colonnes G et H. Elle prévoit de placer des conditions sur la même ligne, malgré nos explications. La raison est simple. Elle consiste à faciliter l'énumération des conditions. Les codes postaux sont des données numériques. Et certains se suivent comme le 04, le 05 et le 06 ou encore comme le 83 et le 84. Plutôt que de les énumérer, nous pouvons exprimer des plages de valeurs. Par exemple, tous les codes postaux supérieurs ou égaux à 4000 et strictement inférieurs à 7000 font bien partie de la région PACA.

• En G2, taper le critère : >=4000,
• En H2, recouper ce critère avec la condition : <7000,
• En G3, taper le critère : >=13000,
• En H3, recouper ce critère avec la condition : <14000,
• En G4, taper le critère : >=83000,
• En H4, recouper ce critère avec la condition : <85000,

Les conditions que nous avons posées se lisent de la façon suivante : Nous souhaitons extraire tous les enregistrements dont les codes postaux sont soit compris entre 4000 et 7000 exclu, soit compris entre 13000 et 14000 exclu, soit compris entre 83000 et 85000 exclu. Bref, il s'agit bien des départements de la région PACA.

• Cliquer dans l'une des cellules du tableau pour le désigner, par exemple C3,
• Dans le ruban Données, cliquer sur le bouton Avancé,
• Dans la boîte de dialogue qui surgit, cocher la case Copier vers un autre emplacement,

Comme précédemment, les bornes du tableau ont été détectées automatiquement. C'est ce qu'indique la zone Plages. Et à ce titre, nous remarquons que la base de données est très volumineuse. Elle s'étend jusqu'à la ligne 39200. En excluant la ligne de titre, nous comptons donc 39199 enregistrements qu'il faut nettoyer.

• Dans la Zone de critères, sélectionner la plage de cellules G1:H4,
• Dans la zone Copier dans, sélectionner la plage de cellules G10:H10,

Comme précédemment, nous avons indiqué les conditions à recouper sur le tableau à filtrer, ainsi que la zone à partir de laquelle doit se produire l'extraction des données.

• Cliquer sur le bouton Ok pour procéder à l'extraction des données,

Seuls les codes postaux et les villes sont effectivement extraits. Cette extraction restrictive, permise par les filtres avancés, est donc particulièrement intéressante. De plus, en faisant défiler le tableau, vous remarquez que seules les villes de la région PACA sont conservées. Ce travail en amont est donc précieux pour permettre de simplifier les importations sélectives et précises qui vont suivre.

L'extraction s'étend jusqu'à la ligne 1166. Nou avons donc considérablement réduit le volume des données. Là encore, ce résultat est intéressant pour améliorer les performances d'une base de données, afin de ne pas avoir à traiter des informations inutiles.

• Sélectionner la première cellule de ce tableau de résultat, soit la cellule G10,
• Tout en maintenant la touche MAJ enfoncée, sélectionner la dernière, soit la cellule H1166,
• Dans la zone Nom, en haut à gauche de la feuille Excel, saisir Communes,
• Puis, valider nécessairement par la touche Entrée su clavier,

Nos extractions sélectives sont désormais produites. Nos tableaux résultants sont bornés et explicitement reconnus par des plages nommées.

• Enregistrer le classeur Excel (CTRL + S),

Cette action est nécessaire pour qu'Access ait connaissance des extractions délimitées par les plages de cellules fraîchement nommées.

Importations sélectives
Il est temps de constater l'intérêt de ces manipulations de données. L'objectif, tel que nous l'avions annoncé, consiste à pouvoir importer des données nettoyées et purgées dans une base de données Access.

• Démarrer Access et créer une base de données vide,
• Fermer la table proposée par défaut en cliquant sur la croix de son onglet,
• En haut de la fenêtre Access, cliquer sur l'onglet Données externes pour activer son ruban,
• Tout à fait à gauche du ruban, cliquer sur le bouton Nouvelle source de données,
• Dans la liste, pointer sur la rubrique : A partir d'un fichier,
• Dans le sous menu qui suit, cliquer sur Excel,
• Dans la boîte de dialogue qui apparaît, cliquer sur le bouton Parcourir,
• Après avoir sélectionné le dossier de téléchargement, cliquer sur le classeur donnees-a-filtrer-dev.xlsx,

Un assistant se déclenche. Il est destiné à identifier les données à importer et à retravailler leur type à la volée.

• Cliquer alors sur le bouton Ok de la boîte de dialogue,

Dans l'étape qui suit, vous constatez que les deux feuilles products et communes sont identifiées. La partie inférieure de la boîte de dialogue propose un aperçu des données interprétées. Si vous les faites défiler vers la droite, vous constatez que tous les tableaux sont mélangés. Dans ce contexte, aucune importation propre n'est possible.

• Cocher la case Afficher les plages nommées,

La communication entre les logiciels de la gamme Office est d'une grande qualité. Les plages nommées sont reconnues et proposées.

• Dans la liste, sélectionner la plage Produits, telle que nous l'avions nommée,

L'aperçu livré est désormais borné au résultat de l'extraction produit par les filtres avancés d'Excel.

• Cliquer sur le bouton Suivant pour progresser dans la phase d'importation,

Access identifie la première ligne de cette plage comme celle des titres de colonnes, soit des champs pour construire la table de réception. Il a vu juste. C'est pourquoi la case est cochée et la première ligne est grisée.

• Cliquer de nouveau sur le bouton Suivant,

Dans cette nouvelle étape, Access propose d'ajuster le type de données des champs identifiés. Ce travail permet de réceptionner une table parfaitement calibrée pour la construction d'une base de données. Nous devrions nous y attarder. Mais nous avons déjà démontré le mécanisme dans d'autres formations. Nous souhaitons ici déboucher sur le résultat de l'importation sélective.

• Cliquer de nouveau sur le bouton Suivant,

Cette nouvelle étape est particulièrement importante. Toute table de base de données doit proposer une clé primaire. C'est elle qui permet d'identifier chaque enregistrement comme unique. Mais elle permet surtout d'établir les relations entre les tables d'une base de données.

• Cocher la case Choisir ma propre clé primaire,
• Puis, sélectionner le champ products_id par son étiquette,

Il est temps de terminer l'importation des données purgées depuis Excel.

• Cliquer sur le bouton Suivant puis sur le bouton Terminer,

Ainsi nous acceptons le nom attribué à la table de réception. Il est calqué sur le nom de la plage de cellules que nous avons créée depuis Excel.

• Valider la boîte de dialogue en cliquant sur le bouton Fermer,

Comme vous le constatez, la table Produits apparaît dans le volet des objets Access sur la gauche de l'écran.

• Double cliquer sur la table Produits pour l'afficher en mode feuille de données,

Comme vous pouvez le voir, les informations sont parfaitement importées dans leur champ. Aucun stock nul n'est à déplorer. La petite barre de navigation indique que cette table recense 242 enregistrements sur les 848 de départ. Notre importation sélective est donc un succès.

Nous proposons de poursuivre afin de restituer les informations sur les communes de la région PACA.

• Fermer la table Produits en cliquant sur la croix de son onglet,
• Dans le ruban Données externes, cliquer sur le bouton Nouvelle source de données,
• Pointer sur la rubrique A partir d'un fichier et cliquer sur la proposition Excel,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Parcourir,
• Double cliquer de nouveau sur le classeur Excel donnees-a-filtrer-dev.xlsx,
• Puis, cliquer sur le bouton Ok pour dérouler les étapes de l'assistant importation,
• Dans la fenêtre qui suit, cocher la case Afficher les plages nommées,
• Dans la liste, sélectionner la plage Communes,
• Puis, cliquer sur le bouton Suivant,

Un message d'alerte Access apparaît. Il informe l'utilisateur que les noms de champs proposés ne lui conviennent pas. Qu'à cela ne tienne, une fois les données correctement importées, nous pourrons les paramétrer à notre guise en mode création de table.

• Dans l'étape qui suit, conserver la case cochée pour les entêtes,
• Cliquer de nouveau sur le bouton Suivant,

Cette nouvelle étape, comme précédemment, propose de typer les champs importés. Pour les mêmes raisons, nous choisissons de l'ignorer.

• Cliquer sur le bouton Suivant,

Comme vous le savez, l'étape qui suit consiste à définir la clé primaire. Elle ne peut pas être posée sur le nom des villes. Il y a en effet des redondances. Pour la même raison, nous ne pouvons pas exploiter la colonne des codes postaux.

• Conserver cochée la case Laisser Access ajouter une clé primaire,
• Puis, cliquer sur le bouton Suivant et sur le bouton Terminer,

Ainsi, pour le nom de la table de réception, nous conservons le nom que nous avions attribué au tableau d'extraction.

• Valider la dernière boîte de dialogue en cliquant sur le bouton Fermer,
• Puis, afficher la table Communes en mode feuille de données,

Comme l'indique la barre de navigation en bas de la table, seuls 1156 enregistrements sur les 39199 de départ résultent. Une fois de plus, notre importation sélective a parfaitement fonctionné pour manipuler au mieux les données utiles.