Aperçu des sections

  • Généralités

  • B. Extraction d'enregistrements

    Excel permet de masquer les enregistrements ne répondant pas aux critères spécifiés.  Si ces critères sont simples, le filtre automatique peut être utilisé ; s'ils sont complexes ou calculés, c'est le filtre élaboré qui doit être utilisé.

    1.         LE FILTRE AUTOMATIQUE

    Le filtre automatique va permettre d'utiliser les valeurs des champs dans des listes déroutantes.


    groupe "trier et filtrer"
    (3ème bloc)

    une cellule de données étant sélectionnée
    <clic g> 
    sur
      
    des triangles pointés vers le bas  correspondant à des listes déroutantes s'affichent près des titres de colonne – cliquer de nouveau sur le filtre pour les enlever


    pointer sur  le triangle bas à droite du titre de colonne
    <clic g> pour dérouler la zone de liste
    <clic g> sur la valeur à prendre en compte
    le triangle du champ sélectionné affiche  - seuls les enregistrements répondant aux critères restent alors affichés ; les autres sont masqués. Les numéros de ligne des enregistrements sont alors en bleu

     efface le filtre en cours
     annule tous les filtres et réaffiche tous les enregistrements

     

    Ouvrir base.xlsx
    Afficher la feuille nommée "base de données"
    Afficher (en masquant les enregistrements ne répondant pas aux critères) les enregistrements concernant la région "Ouest"
    puis ceux de la région "Ouest" et ceux de la région "Est"

    Des critères s'appliquant à deux champs distincts peuvent être combinés

    Il est possible d'être plus précis dans la définition des filtres :

    filtres textuels affiche les opérateurs de comparaison de texte

    filtres numériques affiche les opérateurs de comparaison numériques

    L'un ou l'autre s'affichent selon la nature des données de la colonne

    Dans base.xlsx,
    Pour afficher les enregistrements dont les ventes sont supérieures à 100 k€
    je clique sur de
    je pointe sur
    je clique sur
    je saisis 100

    Dans base.xlsx, feuille "base de données"
    Afficher les enregistrements concernant la région "Ouest"
    puis affiner en ne prenant que les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 k€ (voir base terminé.xlsx, feuille "solution1")
    Annuler le filtre "ventes" puis annuler le filtre automatique

    Ce filtre peut aussi utiliser plusieurs valeurs combinées pour un même champ avec les opérateurs booléens "et", "ou" ainsi que les jokers "?", "*".

    OPERATEURS

    et     intersection - les deux conditions doivent être simultanées

    ou    union - l'une ou l'autre des conditions est nécessaire et suffisante

    ?       remplace un caractère (n'importe lequel)

    *        remplace plusieurs caractères

    ~       précède le ? ou le * considérés comme texte

     

    Il est aussi possible de filtrer les données à partir la cellule active ; dans ce cas, la valeur servant au filtre sera la valeur même de cette cellule.


    sélectionner
    la cellule
    <clic d> 

    <clic g>  sur

    seuls les enregistrements possédant pour le champ donné la même valeur que la cellule active restent affichés

    Dans base.xlsx, feuille "base de données"
    Masquer les enregistrements ne concernant pas la ville de Caen puis tout réafficher

    Enfin, il est encore possible de filtrer les enregistrements en fonction de la couleur de leur police ou de leur remplissage (seules les couleurs utilisées dans le tableau sont proposées) ; c'est là un outil qui vient en complément du format conditionnel.

    Si les couleurs de police ou de cellule sont uniformes, la commande est en grisé

     

    Dans base.xlsx, feuille "base de données"
    Affecter une couleur de police différente aux 10 premières lignes
    Affecter une couleur de remplissage différente aux 10 lignes suivante
    Affecter une couleur de police différente aux 10 lignes suivante
    Affecter une couleur de remplissage différente aux 10 lignes suivante
    Trier par couleur de police puis de cellule puis annuler le tri

    2.         LE FILTRE ÉLABORE

    Le filtre élaboré offre plus de possibilités que le filtre automatique. Il nécessite la détermination de la plage de données, d'une zone de critères (conditions de sélection) et accessoirement d'une zone d'extraction (zone d'affichage).

    a)   LA PLAGE DE DONNÉES

    La plage de données correspond à l'ensemble des cellules contenant les informations à traiter. Elle peut comprendre la totalité ou une partie d'une feuille de calcul. On peut utiliser successivement des plages de données différentes de la feuille de travail.

    b)  LA ZONE DE CRITÈRES

    La zone de critères est une zone de la feuille de travail dans laquelle vont être indiquées les différentes conditions de sélection des enregistrements.

    insérer plusieurs lignes vierges au-dessus des données
    recopier la ligne des noms des champs de données comme 1ère ligne
    indiquer les critères de recherche sur la deuxième ligne et suivantes

    Ne pas mettre sur les mêmes lignes zone de critères et données car si l'une de ces lignes de données se trouve cachée, les critères de la même ligne le seront aussi

    c)   L'EXTRACTION

    L'extraction va permettre de n'afficher que les enregistrements correspondant aux critères définis (les lignes des autres enregistrements sont masquées).


    groupe "trier et filtrer"
    (3ème bloc)

    une cellule de données étant sélectionnée
    <clic g> 
    sur
      
    vérifier
    la plage de données
    indiquer
    la zone de critères
    si une cellule de données est déjà sélectionnée, Excel détermine seul la plage de données

    Dans base.xlsx, feuille "base de données"
    Extraire (en masquant les enregistrements)
    les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 K€ et de la région "Est" dont les ventes sont supérieures à 35 K€ puis annuler l'opération
    (voir base terminé.xlsx, feuille "solution2")

     

     évite d'afficher des enregistrements identiques pour les champs concernés

    d)  LA ZONE D'EXTRACTION

    C'est la zone de la feuille de travail où vont s'inscrire les enregistrements répondant aux critères spécifiés.

     


    groupe "trier et filtrer"
    (3ème bloc)

    une cellule de données étant sélectionnée
    <clic g> 
    sur
      
    activer  

    vérifier
    la plage de données
    indiquer
    la zone de critères
    <clic g> dans

    <clic g> sur la 1ère cellule de destination
     pour valider
    si une cellule de données est déjà sélectionnée, Excel détermine seul la plage de données

    Si les données ont reçu une mise en forme de type "tableau", Excel remplace les références "ligne" et "colonne" par le n° du tableau

     

    La zone d'extraction ne peut pas être sur une autre feuille de travail

     

    Vous pouvez spécifier les colonnes à inclure dans l’opération de copie
    Copiez les noms des colonnes souhaitées vers la première ligne de la zone d'extraction puis lors du filtrage, incluez les références aux noms des colonnes copiées de la zone d'extraction. (Les lignes copiées incluront alors uniquement les colonnes dont vous avez copié les étiquettes)

     

    Dans base.xlsx, feuille "base de données"
    Extraire (en extrayant les enregistrements en O1 par exemple) les enregistrements de la région ouest dont les ventes sont inférieures à 100 K€ et de la région est dont les ventes sont supérieures à 35 K€ puis annuler l'opération (voir base terminé.xlsx, feuille "solution3")

    e)  LES CRITÈRES D'EXTRACTION

    Ils peuvent être beaucoup plus élaborés que dans le cas du filtre automatique.

    insérer plusieurs lignes vierges au-dessus des données
    recopier la ligne des noms des champs de données comme 1ère ligne
    indiquer les critères de recherche sur la deuxième ligne et suivantes
    Indiquer la valeur de comparaison et l'opérateur logique à partir de la 2ème ligne du champ de critères (format indifférent)

    TEXTE

    ?       remplace un caractère
    *        remplace plusieurs caractères
    ~       précède le ? ou le * considérés comme texte

    CHIFFRE

    =, <, <=,>,>=…
    les opérateurs standard sont disponibles

    Le lien entre les critères présents sur la première ligne de sélection est automatiquement de type "et" : les enregistrements satisfaisant à la fois à tous les critères seront sélectionnés.
    Le lien entre les critères présents sur des lignes successives est automatiquement de type "ou" : les enregistrements satisfaisant l'un ou l'autre des critères sont sélectionnés

     

    Pour cerner la valeur d'un champ, ajouter ou remplacer le nom d'un champ non utilisé dans la zone de critères par celui du champ à cerner et indiquer des formules complémentaires dans les deux cellules de la même ligne
    ex :

     

    Dans base.xlsx, feuille "base de données"
    Extraire (en masquant les enregistrements ne répondant pas aux critères) les enregistrements de la région "Sud" entre 20 et 80 k€ et ceux de la région "Nord" inférieurs à 30 et supérieurs à 75 k€ puis annuler l'opération
    (voir base terminé.xlsx, feuille "solution3", 2éme champ de critères)

     

    En mode "tableau" (utilisation de la mise en forme prédéfinie des tableaux), des problèmes peuvent se poser avec les critères multiples – éviter ce mode sur les lignes de critères

    Les critères utilisés peuvent aussi faire référence à une formule.

    modifier le nom du champ de critère utilisé
    (
    ex : ventes ètotaux)
    indiquer la formule logique en faisant référence à la première cellule de valeurs du champ
    (ex
     

    Dans base.xlsx, feuille "base de données"
    Extraire (en masquant les enregistrements ne répondant pas aux critères) les enregistrements dont les ventes sont supérieures à la moyenne
    (voir base terminé.xlsx, feuille "solution4")

  • B. Extraction de données

    Access et Excel communiquent et échangent des données très naturellement.


    groupe "données externes"
    (1er 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

     

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

    2.         L'EXTRACTION AVANCÉE DE DONNÉES D'UNE BASE ACCESS

    Excel permet d'extraire d'une base les seules données utiles par l'intermédiaire d'une requête effectuée avec un assistant. La requête peut être enregistrée.


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

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

    a)   LA CRÉATION DE LA SOURCE DE DONNÉES

    Elle consiste à enregistrer dans un fichier "source de données" les paramètres de connexion au fichier de base de données par le canal du pilote (driver) "odbc"

      fenêtre query

    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

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

    b)  LA CONNEXION À LA BASE DE DONNÉES

    Elle crée un canal (grâce au pilote odbc) entre un fichier de base de données et Excel.

      fenêtre query

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

    La source de données est créée.

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

    c)   LA CREATION DE LA REQUÊTE

    L'assistant "requête" prend alors la main et permet alors de sélectionner les champs à prendre en compte ainsi que les critères d'extraction.

      assistant requête

    <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 avec
    sélectionner le champ dans la partie droite puis
     ou pour en modifier l'ordre
    <clic g>
     sur
    filtrer et trier éventuellement les enregistrements
    <clic g>
     sur
    <clic g>
    sur
    <clic g>
    sur
    confirmer ou modifier les coordonnées de la cellule et la feuille d'insertion
     pour valider

    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

     

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

    d)  LA MODIFICATION DE LA REQUÊTE

    La requête peut être modifiée. L'assistant permet d'utiliser des critères de sélection pour les enregistrements et de trier ces enregistrements.


    groupe "connexions"
    (2ème bloc)

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

    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

     

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

    e)  'ENREGISTREMENT DE LA REQUÊTE

    À la fin de l'assistant requête, Excel permet l'enregistrement de la requête.

      assistant requête

    dans la dernière étape de l'assistant
    <clic g>  sur

    nommer la requête et indiquer le dossier

    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

     

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

    3.         L'EXTRACTION DE DONNÉES D'UNE BASE EXTERNE

    Excel permet d'extraire des données issues de la plupart des bases avec les drivers odbc. Sont accessibles la plupart des formats du marché si les pilotes adéquats ont été installés. Seule la connexion à la source diffère du cas précédent.

    a)   L'INSTALLATION DU PILOTE ODBC

    Le pilote doit être le plus souvent téléchargé à partir du site web de l'éditeur.

    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

    b)  LA CRÉATION DE LA SOURCE DE DONNÉES

    Elle va consister à enregistrer dans un fichier "source de données" les paramètres du pilote (driver)  "odbc"

     panneau de configuration


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

     

    Pour créer une source de données à partir du pilote odbc MySql
    onglet
    <clic g>  sur
    sélectionner le driver
    paramétrer la connexion
    <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.2 win32.msi ou postérieur)

    c)   LA CONNEXION A LA BASE DE DONNÉES

    Elle crée un canal entre un fichier de base de données et Excel par l'intermédiaire du pilote odbc.

    Vous avez une version locale de votre site web qui fonctionne en php avec une base de données Mysql (pour cela, vous utilisez webmatrix, Wampserver ou Easyphp qui créent un environnement Web 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é

    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 (restauration Sql et après vérification)

    d)  L'IMPORTATION DANS EXCEL

    Il faut désigner à Excel la source de données créée.

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

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

     

    Pour trouver le driver odbc d'un type de base de données, interrogez simplement votre moteur de recherche préféré.

    4.         L'EXTRACTION AVANCÉE AVEC MICROSOFT QUERY

    Microsoft Query permet d'ouvrir les requêtes enregistrées ou de traiter les données avant de les renvoyer vers Microsoft Excel.

    a)   OUVERTURE DE LA REQUÊTE

    Il faut d'abord désigner la requête, préalablement enregistrée, pour l'ouvrir.


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

    <clic g>  sur  
    <clic g> 
    sur
    onglet

    <clic g> 
    sur
    sélectionner
    la requête
     puis
     pour valider
    dans la dernière étape de l'assistant, choisir  

    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 (l'ouvrir alors directement en sql et modifier)
    Modifier la requête en la décomposant afin qu'Excel la comprenne

     

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

    Si Excel ne trouve plus la base de données (déplacée, pas le même micro…), cliquez sur  pour désigner son chemin d'accès

    b)  DÉTERMINATION DES CRITÈRES

    La requête ouverte, les critères peuvent être ajoutés ou modifiés.

     query
    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

     

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

     

      

    Plutôt qu'indiquer un critère complexe pour un même champ comme ici :
    il vaut mieux 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ù")

     

    Créer une source de données pour les fichiers "accdb" à partir de la base de données "northwind 2013 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

    Penser à cliquer sur  pour actualiser les enregistrements affichés

     

    Il est possible d'afficher cette requête directement dans Query (menu Fichier). 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 vide)

    c)   AJOUTER UNE TABLE

    L'opération est simple.

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

    d)  RENVOYER LES DONNÉES VERS EXCEL

    Une fois toutes les manipulations effectuées, il faut renvoyer les données dans Excel.

     query
    <clic g>
     sur

     

    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

    5.         LE CUBE OLAP

    Le cube Olap est une structure de base de données intelligente et pluridimensionnelle permettant de travailler sur une partie des données comme dans un sous-ensemble de la base. Excel permet d'utiliser un cube existant.


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

    <clic g>  sur  
    <clic g> 
    sur
    onglet
    ,  <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 importer
    <faire glisser> les champs du tableau croisé à leurs positions respectives

    6.         LES AUTRES SOURCES D'EXTRACTION

    Il est aussi possible d'extraire des données à partir du Web ; les données affichées sous forme de "table" sur la page peuvent être récupérées mais les données de la base liée (mysql ou autre) ne peuvent évidemment pas être directement importées.


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

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

    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

    Le résultat peut être tout à fait satisfaisant pour les tableaux de type "liste" qu'il suffit alors de mettre en forme.

     

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