Course: Cours en ligne Excel 2013 niveau 2 tableaux croisés traitement de données, macros, vba

General

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" (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

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" (1^er 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" (1^er 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" (1^er 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" (1^er 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" (1^er 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