Tableaux structurés (Partie 3)
- 06. Créer un tableau structuré (nommé le tableau) / Annuler un tableau structuré
- 07. Ajouter une colonne / Insérer une ligne
- 08. Redimensionner un tableau structuré
- 09. Ajouter une formule
- 10. Fonction utilisant un champ du tableau
- 11. Recherche complexe sur plusieurs colonnes (la clé est sur plusieurs colonnes)
- 12. Compter le nombre de données différentes dans une liste
Le fichier reprenant l’ensemble des exemples peut être téléchargé ici: Exemple Tableaux Structurés
Description
Un tableau est utilisé pour gérer et analyser des données. Les tableaux facilitent le classement et l’exploitation de ces données.
Fonctionnalités possibles:
- Filtrer et trier les données des colonnes.
- Utiliser les références du tableau dans vos formules.
- Ajouter une ligne de totaux.
- Différencier facilement les lignes et les colonnes en appliquant un style dans le tableau.
- Servir de base pour un tableau croisé dynamique ou un graphique.
- Ajouter facilement de nouveaux enregistrements et de nouvelles colonnes (possibilité de redimensionnement automatique).
- Mettre à jour automatiquement les objets liés au tableau (formules, TCD, graphiques …).
- Supprimer les doublons.
- Ajouter en une seule fois des formules dans toutes les cellules d’une colonne
Bonne pratiques:
- Chaque cellule de la première ligne contient le nom des champs (en-tête) qui doivent
- être uniques (par défaut, Excel incrémente le nom du champ de façon à ne pas en avoir deux identiques)
- le plus explicite possible.
- Les lignes suivantes contiennent les enregistrements.
- Evitez des types de données différents dans une même colonne (texte, date, numérique).
- La base doit stocker uniquement des données brutes (pas de lignes de sous totaux, pas de sous ensembles …).
6. Créer un tableau structuré (nommé le tableau) / Annuler un tableau structuré
Pour créer un tableau structuré, il faut se positionner sur le tableau, dans n’importe quelle cellule et choisir Tableau dans le Ruban:
Il faut ensuite choisir un style parmi ceux proposés.
Il n’est pas obligatoire de sélectionner l’ensemble du tableau avec le raccourci CTRL + * auparavant.
Pour nommer le tableau, saisir dans la partie Propriété le nom. Par convention, on commencera le nom par « T. » afin de préciser qu’il s’agit d’un tableau.
Il peut être parfois utile d’annuler le format tableau. Pour cela, il suffit de cliquer sur le bouton « Convertir en plage » dans la partie Outils.
Dans la partie Options de style de tableau, vous pouvez choisir différentes option d’affichage. Afin de mieux comprendre, n’hésitez pas à essayer chacune des options.
7. Ajouter une colonne / Insérer une ligne
Il suffit de rajouter le titre à droite de la dernière colonne, ou de remplir n’importe quelle cellule juxtaposée au tableau existant:
Il est bien sûr possible d’ajouter une colonne en insérant entre deux colonnes existantes. Le titre par défaut sera « Colonne 1 ».
Pour ajouter des lignes, le principe est le même, soit en remplissant une cellule juxtaposée au tableau existant (sur la première ligne sous le tableau), soit en insérant une ligne.
8. Redimensionner un tableau structuré
Pour redimensionner le tableau, si par exemple nous avons oublié de prendre une colonne, il suffit de cliquer dans le ruban, sur la partie Propriété « Redimensionner le tableau ».
Pour redimensionner le tableau, si par exemple nous avons oublié de prendre une colonne, il suffit de cliquer dans le ruban, sur la partie Propriété « Redimensionner le tableau ».
9. Ajouter une formule
L’ajout d’une formule est très simple. Il suffit de remplir la cellule avec la formule (comme le schéma ci-dessous.
Le fait de valider (en quittant la cellule) permet, dans un tableau structuré, d’appliquer la formule dans l’ensemble de la colonne. Si vous ne souhaitez l’appliquer que sur la cellule en cours, il suffit de cliquer sur CTRL + Z.
10. Fonction utilisant un champ du tableau
Voici un extrait d’un exemple de tableau:
Dans notre exemple, nous souhaitons chercher la description pour Champ = CURR.
Afin de simplifier l’exemple, j’ai créé un champ nommé « L_Champ » pour la valeur cherchée.
Nous alloons utiliser la fonction RECHERCHEV avec les paramètres présentés ci-dessous:
=RECHERCHEV(L_Champ;T.TablesSAP[[Champ]:[Description]];3;FAUX)
La table matrice choisie ici est T.TablesSAP[[Champ]:[Description]], c’est à dire les colonnes Champ à Description du Tableau « T.TablesSAP ». La lecture est ainsi plus simplifiée et surtout si l’utilisateur ajoute des lignes en fin de tableau, elles seront automatiquement prises.
En revanche, si une colonne est insérée entre Champ et Description, la valeur retournée sera fausse. Je préconise du coup d’utiliser les fonctions INDEX et EQUIV (voir article). D’ailleurs, vous verrez que pour faire des recherches plus complexes sur plusieurs colonnes (lorsque la clé de recherche est sur au moins deux colonnes), nous utiliserons ces deux fonctions.
11. Recherche complexe sur plusieurs colonnes (la clé est sur plusieurs colonnes)
L_Table et L_Champs sont les champs nommés qui seront utilisés pour la recherche.
Voici la formule qu’il faut utiliser pour trouver la description d’après la clé Table+Champs:
{=INDEX(T.TablesSAP;EQUIV(L_Table&L_Champ;T.TablesSAP[Table]&T.TablesSAP[Champ];0);4)}
Vous pouvez constater qu’il s’agit d’une formule matricielle puisque la formules est entre { et }. Afin d’avoir une formule matricielle, au moment de valider la formule, au lieu de cliquer sur ENTREE, il faut faire SHIFT + CTRL + ENTREE. Les formules matricielles permettent de prendre en compte des plages entières.
Le sujet ici n’est pas de savoir comment fonctionnent les fonctions INDEX et EQUIV, ni les formules matricielles mais comment on peut faire pour rechercher d’après deux valeurs dans deux colonnes différentes.
Valeurs cherchées: L_Table&L_Champ
Colonnes de recherche: T.TablesSAP[Table]&T.TablesSAP[Champ]
N’hésitez pas à faire quelques tests et si vous avez des questions, je serai ravi de pouvoir vous répondre.
12. Compter le nombre de données différentes dans une liste
Voici une formule permettant de compter le nombre de cellules différentes dans une liste. Pour vérifier, il suffit de recopier la colonne dans une autre feuille, de supprimer les doublons (Ruban -> Données -> Outils de données -> Supprimer les doublons) pour enfin compter le nombre de cellules présentes !
Nb de Tables différentes:
=SOMMEPROD(1/NB.SI(T.TablesSAP[Table];T.TablesSAP[Table]))
La valeur retournée est 32, alors que le nombre de lignes est de 1834.Nb de Champs différents:
=SOMMEPROD(1/NB.SI(T.TablesSAP[Champ];T.TablesSAP[Champ]))
La valeur retournée est 1426, alors que le nombre de lignes est de 1834.
Nb de Tables différentes:
=SOMMEPROD(1/NB.SI(T.TablesSAP[Table];T.TablesSAP[Table]))
La valeur retournée est 32, alors que le nombre de lignes est de 1834.Nb de Champs différents:
=SOMMEPROD(1/NB.SI(T.TablesSAP[Champ];T.TablesSAP[Champ]))
La valeur retournée est 1426, alors que le nombre de lignes est de 1834.