 |
Exercice : Tableau croise dynamique
|
 |
I. Utiliser une source de données externe
Une source externe peut être une table Access, un autre
classeur Excel fermé (une feuille de calcul ou une plage nommée)...
 |
Base Access voyage.accdb |
 |
| onglet Insertion dans le groupe Tableaux
 bouton Tableau croisé dynamique
option Utiliser une source de données externe
bouton Choisir la connexion
bouton Rechercher
Sélectionnez
le type de fichier dans le menu déroulant puis recherchez votre fichier
source sur le PC bouton Ouvrir |
|
| |
|
Choisissez la table |
|
|
| |
Zone nommée bdd dans base.xlsx |
Vous
pouvez accéder aux paramètres de connexion.
| |
| onglet Données dans le groupe Connexions
Le bouton Propriétés est intéressant, et tout particulièrement l'onglet Définition car vous y trouverez
la syntaxe à utiliser si vous désirez réaliser le même travail en VBA
L'onglet Utilisation contient les options pour
l'actualisation automatique de la source |
|
|
La mise à jour à chaque ouverture peut aussi être paramétrée
directement depuis le TCD:
| |
onglet Options dans le groupe Options du
tableau croisé dynamique Options
onglet Données
Cochez l'option Actualiser les données
lors de l'ouverture du classeur |
|
|
II. La fonction LIREDONNEESTABCROISDYNAMIQUE
La fonction LIREDONNEESTABCROISDYNAMIQUE permet
d'extraire le contenu d'un TCD.
La formule peut être mise en oeuvre très facilement:
Pour désactiver l'option de création automatique de la
formule LIREDONNEESTABCROISDYNAMIQUE lorsque vous faites référence à une
cellule du rapport:
| |
| Sélectionnez le TCD
onglet Options dans le groupe Options du
tableau croisé dynamique
Ouvrez le menu déroulant Options
Décochez l'option Générer l'extraction de données
croisées dynamiques
|
|
|
III. Trucs et astuces
A. Remplacer les cellules vides par des zéros
| |
onglet Options dans le groupe Options du
tableau croisé dynamique Options
onglet Disposition et mise en forme
Dans le champ Pour les cellules vides, afficher : assurez vous que l'option est cochée
puis saisissez la valeur 0 |
|
|
B. Comment utiliser l'ancienne interface pour créer un TCD
Cette question revient souvent suite au passage à Excel2007,
et peut être intéressante, notamment pour gérer les sources de données multi
feuilles.
En fait il suffit d'ajouter le bouton "Assistant tableau croisé
dynamique" dans la barre d'outils accès rapide.
| |
  Sélectionnez
l'option Personnaliser
Dans le menu déroulant Choisir les
commandes dans les catégories..
sélectionnez l'option Commandes non présentes dans le ruban
Sélectionnez la commande Assistant
tableau croisé dynamique
bouton Ajouter
pour
qu'il apparaisse dans la barre d'outils d'accès rapide |
 |
|
C. Créer un rapport à partir de sources multi classeurs
Les données de chaque classeur sont dans une plage nommée
"bdd".
| |
Base.toulon.xlsx et amiens.xlsx |
| |
| Plages de feuilles de calcul avec étiquettes
bouton Suivant (2 fois, jusqu'à l'étape 2b) |
|
| |
|
| Vous pouvez sélectionner vos classeurs source en cliquant sur
le bouton Parcourir
Complétez le nom de la plage nommée avant de cliquer sur le
bouton Ajouter, pour obtenir une syntaxe dans
la style:
'C:\Documents and
Settings\yvon\dossier\toulon.xlsx'!bdd
Sélectionnez les classeurs, complétez la plage nommée
bouton Ajouter autant de fois que vous avez
de classeurs sources
bouton Suivant |
|
| |
|
Précisez la cellule de destination pour le tableau croisé
bouton Terminer |
|
|
Le TCD qui s'affiche contient notamment un champ de page
permettant de filtrer les classeurs sources.
D. Définir la source dynamiquement
Tout comme dans les versions précédentes d'Excel, la
fonction DECALER est pratique pour redéfinir automatiquement la plage de
données source, si vous utilisez un nombre de lignes ou de colonnes variables.
La plage nommée est modifiée en fonction du nombre de cellules non vides.
| |
Activez la feuille contenant la source
onglet Formules dans le ruban
bouton Définir un nom dans le groupe Noms définis
Dans le champ Nom, attribuez un
nom à la plage de cellules (par exemple NomPlage)
Assurez-vous que Classeur est
sélectionné dans le menu déroulant Zone
Dans le champ Fait référence à:,
indiquez la formule suivante: =DECALER($A$1;;;NBVAL($A$1:$A$2000);NBVAL($A$1:$H$1))
Cet exemple limite la base de données de la colonne A jusqu'à H,
et jusqu'à 2000 lignes
OK
Vous pouvez ensuite utiliser le nom pour définir l'emplacement
des données (2ème étape lorsque vous créez le tableau croisé dynamique) =NomPlage |
IMPORTANT: Il doit impérativement y avoir des étiquettes
dans toutes les colonnes spécifiées.
Pour utiliser la même formule sans limite de lignes et de
colonnes: =DECALER($A$1;;;NBVAL($A:$A);NBVAL($1:$1))
E. Privilégiez l'utilisation des références absolues
La base de données ne doit pas contenir de cellules vides
dans la première ligne d'en-tête. Un enregistrement peut être vide, sauf dans
la première colonne de gauche.
Il est préférable de démarrer la base de données dans la
cellule A1.Ne créez pas deux tableaux sur la même ligne.
Vous pouvez utiliser la même feuille pour plusieurs tables
"mono-champs" (sur une colonne), en passant 1 comme cinquième
paramètre de la fonction DECALER. Vous déterminez de cette manière une
plage d'une seule colonne.
| |
Vous devez gérer des catégories de produits et des catégories de
clients
Indiquez les catégories de produits en colonne A, les catégories
de clients en colonne B, puis utilisez:
Catégories de produits =DECALER($A$1;1;0;NBVAL($A:$A)-1;1)
Catégories de clients =DECALER($B$1;1;0;NBVAL($B:$B)-1;1) |
Il aussi possible de remplacer la fonction DECALER par des
Tableaux. Utilisez les caractéristiques de l'outil 'Tableau' pour définir
aisément les données source. La plage de cellules sera ainsi redéfinie
automatiquement en fonction de l'ajout ou de la suppression d'enregistrements.
F. Faire des glisser/déposer vers le rapport
Si vous désirez effectuer des glisser/déposer de la liste de
champs directement vers le rapport dans la feuille de calcul, comme dans les
anciennes versions d'Excel:
| |
Faites un clic droit dans le TCD
Choisissez Options du tableau croisé
dynamique dans le menu contextuel
onglet Affichage
Cochez l'option Disposition classique du
tableau croisé dynamique glisser de champs dans la grille OK |
G. Appliquer une mise en forme conditionnelle dans le rapport
| |
Sélectionnez les cellules correspondant aux étiquettes de
valeur dans le rapport
onglet Accueil dans le ruban
bouton Mise en forme conditionnelle dans le
groupe Style
Bien que les nombreux critères prédéfinis soient disponibles,
privilégiez Nouvelle règle dans le menu
déroulant
En effet, comme vous le constatez dans la boîte de dialogue, Cellules sélectionnées est l'option par défaut et
peut provoquer des mises en formes fantaisistes si vous choisissez des critères
prédéfinis
Sélectionnez donc la troisième option qui prend en compte les
étiquettes de lignes et de colonnes
|
|
| |
|
Ensuite, choisissez la mise en forme à appliquer, puis
validez |
|
|
