formation Excel 2010 base de données, tableaux croisés

ACTION

 onglet "données"

groupe "données externes" (1^er bloc)

<clic g>  sur
sélectionner le fichier
 pour accepter l'importation des données dans Excel
Si la base compte plus d'une table, Excel demande de définir la table à importer

exercice

Importer les données du fichier "access.accdb"

ACTION

 onglet "données"

groupe "données externes" (1^er bloc)

<clic g>  sur  
<clic g>  sur  
sélectionner "nouvelle source de données"
 pour valider

ACTION

1 saisir le nom de la source de données
2 sélectionner le driver odbc associé
3 établir la connexion avec le fichier de base de données
4 indiquer éventuellement la table ou sont les données recherchées

avancé

Un connecteur odbc permet de lier une ou plusieurs tables de base de données à l'application en cours (ici Excel)

ACTION

<clic g> sur
<clic g> sur
désigner le fichier de données Access (accdb)
 pour valider le fichier puis  pour valider la création de la connexion

infos

Elle est maintenant directement accessible dans  du groupe "données externes"

ACTION

<clic g> sur + de la table affiche la liste des champs
sélectionner le champ puis  pour le prendre dans la requête
répéter l'opération pour tous les champs à prendre en compte
les données afférentes peuvent être affichées par
sélectionner le champ dans la partie droite puis  pour en modifier l'ordre
<clic g>  sur
filtrer et trier éventuellement les enregistrements
<clic g>  sur
<clic g> sur
<clic g> sur

ACTION

confirmer ou modifier les coordonnées de la cellule et la feuille d'insertion
 pour valider

infos

Les données peuvent être modifiées dans Excel mais ces modifications ne sont pas répercutées dans la base source ; l'outil  réaffiche les données d'origine

exercice

Créer un nouveau classeur
Y afficher les enregistrements extraits de la base access.accdb pour les champs "société", "contact", "adresse" et "cdpostal"
Enregistrer sous le nom "req"

ACTION

 onglet "données"

groupe "connexions" (2^ème bloc)

une cellule des données importées étant sélectionnée
<clic g>  sur  de  
<clic g>  sur "propriétés de connexion"
onglet , <clic g>  sur
la requête s'affiche de nouveau dans l'assistant

exercice

Modifier la requête et ne prendre que les enregistrements dont le code postal est supérieur ou égal à 10000 et strictement inférieur à 45000
Trier par "cdpostal" et "société" dans l'ordre croissant

ATTENTION

Lorsque qu'un champ est filtré ; il est en gras.
Lors de plusieurs requêtes successives, enlever les filtres utilisés précédemment

ACTION

dans la dernière étape de l'assistant
<clic g>  sur
nommer la requête et indiquer le dossier

avancé

La requête est enregistrée au format .dqy, le dossier requête de l'utilisateur est alors proposé mais un autre peut être choisi

exercice

Enregistrer la requête précédente sous le nom "req1" dans le même dossier que les exercices puis fermer le classeur

exemple

Pour utiliser le pilote odbc de MySql,
Se rendre sur http://www.mysql.fr/downloads/connector/odbc/
Télécharger le pilote Windows (x86, 32-bit), MSI Installer Connector-ODBC
L'installer

ACTION

 MENU WINDOWS

onglet
<clic g>  sur
sélectionner le driver
<clic g>  sur

exemple

Pour créer une source de données à partir du pilote odbc MySql
onglet
<clic g>  sur
sélectionner le driver
<clic g>  sur

avancé

Un connecteur odbc permet de lier une ou plusieurs tables de base de données à l'application en cours (ici Excel). Il peut être nécessaire de rajouter les pilotes ODBC du type de base de données source (Mysql : mysql-connector-odbc-5.1.8-win32.msi ou postérieur)

exemple

Vous avez une version locale de votre site web qui fonctionne en php avec une base de données Mysql (pour cela, vous utilisez Wampserver ou Easyphp qui créent un environnement Apache local). Vous pouvez extraire des données de cette base et les traiter dans Excel : la fenêtre ci-dessus illustre la création d'une source de données à partir de cette base Mysql locale à l'aide du driver odbc précédemment téléchargé

avancé

Access permet de faire la même chose et d'agir directement sur les données source en mode "table", ce qui est très pratique pour modifier globalement les données sans passer par des requêtes ; il suffit alors de rapatrier en ligne les données (après vérification)

ACTION

 onglet "données"

groupe "données externes" (1^er bloc)

<clic g>  sur  
sélectionner la source créée
 pour valider

infos

Pour trouver le driver odbc d'une base de données, interrogez simplement votre moteur de recherche préféré.
Exemple de mots clés : "pilote odbc xxx"

ACTION

 onglet "données"
groupe "données externes" (1^er bloc)

<clic g>  sur  
<clic g>  sur
onglet
<clic g>  sur
sélectionner la requête
 puis  pour valider

ATTENTION

Lors de la modification d'une requête, il peut arriver qu'Excel ait synthétisé les critères au sein d'un seul filtre et n'arrive plus à les lire
Modifier la requête en la décomposant afin qu'Excel la comprenne

ACTION

dans la dernière étape de l'assistant
au lieu de choisir
cocher  (dernière étape)

exercice

Ouvrir la requête "req1" créée précédemment ; modifier les critères de filtre si nécessaire

ACTION

indiquer directement le nouveau critère dans la zone de critères

ou
menu critères
ajouter des critère
indiquer le champ, l'opérateur et la valeur
 lorsque le critère est défini
 permet de combiner des critères
 lorsque tous les critères ont été définis

CONSEIL

Plutôt qu'indiquer un critère complexe pour un même champ comme ceci
il est possible de répéter le champ de critère dans la colonne suivante et d'indiquer le critère sur la même ligne (équivalent à "et")
ou sur une ligne différente (équivalent à "où")

infos

les critères s'inscrivent dans un volet de la fenêtre et les valeurs affichées correspondent aux critères

exercice

Créer une source de données pour les fichiers "accdb" à partir de la base de données "northwind 2011 ios.accdb"
Afficher la table "employes", et extraire les employés habitant une ville dont le code postal est strictement supérieur à 60000 et inférieur ou égal à 74000 exerçant la profession de "représentant commercial", ceux exerçant la profession de "Vice-président des ventes" quelle que soit leur ville ainsi que les employés exerçant la profession de "représentant commercial" habitant Cherbourg

ATTENTION

Penser à cliquer sur  pour actualiser les enregistrements affichés

avancé

Il est possible d'afficher cette requête directement dans Query menu Fichier – Ouvrir. Elle est enregistrée sous le nom  du dossier d'exercices ; si Query affiche un message d'erreur réseau ou disque, il suffit de lui indiquer le chemin d'accès à la base de données en cliquant sur  (ok sur log admin et mot de passe)

ACTION

<clic g>  sur
sélectionner la table
<clic g>  sur  puis

ACTION

<clic g>  sur

exercice

Enregistrer la requête en cours sous le nom "req2" dans le même dossier que les exercices puis renvoyer les données vers Excel

ACTION

 onglet "données"
groupe "données externes" (1^er bloc)

<clic g>  sur  
<clic g>  sur
onglet "cubes olap"
<clic g>  sur
sélectionner le fichier "cube"
 puis  pour valider
la source de données OLAP s'affiche dans la liste
la sélectionner
 pour valider
 pour importer
<faire glisser> les champs du tableau croisé à leurs positions respectives

ACTION

 onglet "données"
groupe "données externes" (1^er bloc)

<clic g>  sur  
saisir l'adresse du site
<clic g>  sur  pour cocher  les tables (au sens internet de "tableau")
les tables cochées ont le signe  à la place de
 pour importer
 pour valider

CONSEIL

Effectuez auparavant une recherche avec votre navigateur préféré puis copier/coller l'adresse internet dans la boîte de dialogue précédente

ATTENTION

Ne pas utiliser le mode "Mettre sous forme de tableau" sous peine de rompre la connexion