Catégorie : Excel

[Tuto] Jusqu’où peut-on aller avec les mises en forme conditionnelles sur Excel ?

[Tuto] Jusqu’où peut-on aller avec les mises en forme conditionnelles sur Excel ?

La mise en forme conditionnelle est à Excel ce que les épices sont à la cuisine ! Sans aucune mise en forme, cela peut être bon, mais sans saveur. En trop grande dose, cela peut gâcher tout votre travail. Dans cet article, je souhaite vous présenter quelques fonctionnalités de base :  mise en forme conditionnelle des textes, mise en forme conditionnelle des lignes. Je souhaite surtout vous présenter les fonctionnalités avancées des mises en forme conditionnelles. Enfin, je vous présenterai quelques astuces avancées pour réellement exceller avec les mises en forme conditionnelles !

Introduction

Excel permet de faire des calculs, présenter les résultats dans un graphique et également mettre en forme des données, c’est-à-dire changer le format du nombre, le style de la police, la présence et le style de bordure, et enfin le remplissage (la couleur du fond de la cellule).

La fonctionnalité de mise en forme conditionnelle dans Excel permet d’adapter ces attributs de manière automatique en fonction des critères de vos choix ! Cela peut paraître théorique lorsque c’est écrit ainsi, mais avec quelques cas pratiques, ce sera plus clair.

Cas pratique 0 : Prise en main des mises en forme conditionnelles

Créer une nouvelle règle

La fonction de mise en forme conditionnelle a été placée dans le bandeau “Accueil”, c’est vous dire si elle est fréquemment utilisée !

Comme le menu le présente, plusieurs fonctions pré-configurées existent. Celles-ci seraient d’ailleurs difficiles à mettre en place “sans code”. Elles sont donc utiles, mais ne sont pas extensibles…

La fonction que vous utiliserez le plus souvent est bien la “Nouvelle règle…”. Celle-ci vous correspondra et fera ce que vous lui demanderez.

Excel - Menu mise en forme conditionnelle

Une fois cette action réalisée, la fenêtre suivante apparaît.

Excel - Menu mise en forme conditionnelle - Type reglesDans la liste des options proposées, sur la même idée que précédemment, pour les règles de mise en forme conditionnelle “simples”, les 5 premiers choix pourront être utiles. Pour tous les autres cas, ce sera la dernière option “Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué”.

La formule qui permettra de déclencher la mise en forme sera à saisir dans la barre de formule en 2.

Et en 3, surtout, ne l’oubliez pas ! C’est la mise en forme à adopter si la formule saisie renvoie VRAI.

Combien de fois ai-je oublié de configurer la mise en forme… ? Je ne le compte plus. J’étais pourtant certain de ma formule conditionnelle… et rien ne se passait.

Au bout d’un quart d’heure à vérifier et revérifier, je me rends compte que je n’ai pas configuré de mise en forme spéciale… donc le champ ne se mettait pas en évidence !

Gérer les règles de mise en forme conditionnelleExcel - Gerer les mises en forme conditionnelle

Dans le menu initial des mises en forme conditionnelles, se trouve également une option essentielle : “Gérer les règles…”

Cette option permet non seulement de retrouver l’ensemble des règles qui s’appliquent sur une feuille de calcul, mais elle permet aussit de mettre en pratique une super astuce que je vous dévoile plus loin dans cet article !

L’écran qui apparaît alors est EXTRÊMEMENT important !

Excel - Menu mise en forme conditionnelle - Gerer regles

  1. Après l’oubli de configurer une mise en forme, c’est la deuxième cause de perte de temps ! Les règles affichées ne concernent que la portée affichée. Vous pouvez avoir comme choix : le tableau où se trouve votre souris, la sélection ou la feuille entière. Toutes les règles qui s’appliquent ne sont pas systématiquement affichées !
  2. Le format permet d’identifier d’un coup d’œil la règle recherchée.
  3. La zone “S’applique à” est ESSENTIELLE ! Elle va être la clé pour étendre une règle que vous aurez paramétrée sur une unique cellule.
  4. Last but not least, la case “Interrompre si Vrai”. Eh oui ! Il est possible d’interrompre l’application des règles de mise en forme dès qu’une règle est appliquée. Ainsi, l’ordre des règles va également revêtir une importance.

Avec toutes ces informations en main, vous êtes prêt à mettre en pratique les cas que je vous présente ci-dessous.

Cas pratique 1 : Mise en forme conditionnelle d’un texte

Besoin = Détecter les doublons

Le cas d’utilisation que je vois le plus fréquemment avec Excel, c’est de l’utiliser comme un fichier de commandes ou de factures (voire les deux en même temps !). Je vous invite à lire et à relire mon article décrivant les 4,5 apprentissages qu’un cours Excel devrait vous enseigner, et en particulier le premier point ! Cela étant dit, vous avez un problème à résoudre, c’est celui de ne pas donner deux fois le même numéro de commande ou de facture sur des lignes différentes.

Excel n’étant pas une application de gestion, il n’intègre pas de fonctionnalité d’incrément de numéro ou de création d’un identifiant unique.

Vous devez saisir les numéros de commandes qui contiennent en plus des lettres pour diverses raisons, vous n’avez pas fabriqué une formule de calcul automatique. Vous devez seulement vérifier qu’il n’y a pas eu d’erreur de saisie (et surtout de doublons).

Excel - Mise en forme conditionnelle - Texte

Étape n° 1 : Identifier la formule qui permet de détecter le doublon

Déterminons comment détecter un doublon pour une cellule donnée.

La réponse se trouve dans la question, détecter un doublon, c’est trouver au moins deux fois la même valeur. La formule qui sera utilisée est donc : NB().

Dans la mesure où il s’agira de tester si le nombre d’occurrences une valeur est strictement supérieure à 1, nous nous orienterons vers NB.SI(plage de recherche ; valeur recherchée).

En langage Excel, cela donnera : NB.SI($A$2:$A$19;$A$2)>1.

Cette formule signifie “Compte le nombre de fois que tu trouves la valeur de $A$2 dans la plage de valeur allant de $A$2 à $A$19 et dis-moi si ce nombre est supérieur à 1”.

Étape n° 2 : Tester cette formule sur un seul champ

Pour tester cette formule, il ne reste qu’à ajouter une formule contenant une condition, “Si – Y’a-t-il un doublon” – alors VRAI sinon FAUX.

Cela nous amène à la formule complète : =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX).

Étape n° 3 : Généraliser sa formule sur une plage de champs

Excel - Plage mise en forme conditionnellePour appliquer une règle sur un ensemble de champs, il sera nécessaire de relire et analyser à nouveau la formule qui avait été trouvée.

Il faut analyser les références fixes et réfléchir à celles qui doivent devenir relatives.

Rappel sur les références et l’utilisation du symbole $

Une référence à une cellule avec des symboles $ indique que la référence ne doit pas s’adapter au contexte, l’absence du symbole indique qu’il faut s’adapter au contexte.

Deuxième rappel, $A2 signifie que la colonne ne doit pas s’adapter, mais la ligne oui (ce sera toujours la colonne A). A$2 indique que la colonne doit s’adapter et la ligne non (ce sera toujours la ligne “2”).

Notre formule précédente =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX) possède deux références. L’une correspond à toutes les cellules parmi lesquelles nous recherchons des doublons ($A$2:$A$19), l’autre correspond à la cellule dont nous cherchons les doublons ($A$2).

Comme nous allons étendre la zone sur laquelle nous recherchons des doublons, si la cellule recherchée reste avec les symboles $, la mise en forme changera uniquement si la cellule A2 est en doublon ! Il est donc essentiel de changer manuellement cette référence de $A$2 en A2. Ainsi, la règle adaptera AUTOMATIQUEMENT au contexte où elle s’applique.

Il ne vous reste qu’à étendre la plage d’application de la règle.

Dans le menu “Gérer les règles de mise en forme”, vous devriez avoir cet écran :

Excel - Plage mise en forme conditionnelle - 1

Ainsi, seule la première cellule est vérifiée. Vous allez pouvoir étendre la zone sur laquelle cette règle s’applique avec la petite flèche vers le haut et en sélectionnant toute la plage de vérification.

Excel - Plage mise en forme conditionnelle - 2

Maintenant, c’est tout bon !

Attention : Si vous retiriez les $ de la plage de recherche ($A$2:$A$19), celle-ci changerait également automatiquement. Elle deviendrait A3:A20 pour le test de la 2e ligne, A4:A21 pour la 3e, et ainsi de suite. Au fur et à mesure, les valeurs des premières cellules du tableau ne seraient plus dans la zone de recherche d’un doublon !

Autres cas

A ce stade, vous l’aurez certainement compris, la mise en forme conditionnelle d’une date ou la mise en forme conditionnelle d’une somme n’est pas plus difficile. Il vous faut déterminer la formule qui vous correspond pour détecter quand il faut changer la mise en forme.

Ne pas oublier que la mise en forme conditionnelle peut également adapter le format des nombres. J’ai d’ailleurs récemment découvert la mise à jour des monnaies disponibles dans Excel avec la présence du Bitcoin !

Excel - Menu mise en forme conditionnelle - Format nombre

Cas pratique 2 : Mise en forme conditionnelle d’une autre cellule

Initialement, nous avons tendance à penser que la mise en forme doit s’appliquer sur la cellule contenant les valeurs. Que nenni ! Les règles partent de la cellule à mettre en forme, peu importe où elle se trouve.

Ce sont les règles de mise en forme conditionnelle qui vont déterminer les conditions de changement de la mise en forme.

Ainsi, je peux configurer une cellule que j’appellerais “Garant de la conformité”. Je peux par exemple conditionner sa mise en forme au respect de toutes les règles de gestion de ma feuille Excel.

  • S’il y a un doublon, je passe en rouge.
  • S’il manque une valeur dans une ligne, je passe en bleu.
  • Si le total est négatif, je passe en orange.

D’un coup d’œil, il est alors possible de connaître l’état de cohérence de la feuille / du classeur Excel.

Cas pratique 3 : Mise en forme conditionnelle d’une ligne entière

La mise en forme conditionnelle d’une ligne entière n’est pas plus compliquée que celle d’une cellule unique. Le véritable défi se situe dans la configuration des références ($A$1 ≠ $A1 ≠ A$1 ≠ A1).

La situation que j’utilise régulièrement, c’est de mettre en forme toutes les cellules concernées par un calcul qui ne respecte pas une condition. Par exemple, je vais répartir un travail entre des équipes à l’aide de pourcentage. Bien sûr le total doit faire 100, et je peux le calculer et présenter le résultat.

Excel -MFC - Ligne entiere

L’enjeu reste toujours de gérer les références fixes et relatives. Dans l’exemple présenté, le test est assez simple : =SI($K$2<>1;VRAI;FAUX).

Attention, 100% = 1 et pas 100… Sinon, votre test retournera VRAI tant que le total ne sera pas de 10’000 %.

La plage où appliquer cette règle sera l’ensemble du tableau : $C$2:$K$11.

Hélas, cela ne fonctionnera que pour la première ligne. Dès la deuxième, cela posera un problème ! Avez-vous trouvé pourquoi ?

Il n’y a aucune référence qui est relative dans le test “SI”. Ce sera toujours la cellule K2 qui sera testée. Comme vous souhaitez rendre le test relatif à la ligne où vous vous trouvez, il faudra saisir $K2 au lieu de $K$2.

La bonne réponse est donc : =SI($K2<>1;VRAI;FAUX).

Bonus pour devenir un expert

Dès que possible, j’utilise les tables dans Excel. Cela a un avantage important dans le nommage des cellules dans les formules.

Ainsi, la somme des pourcentages (issue de l’exemple précédent) n’est pas SOMME(C2:J2) mais SOMME(TChargesRepartition[@[Equipe 1]:[Equipe 8]]).Excel - MFC - Table

Cela rend la formule lisible et compréhensible.

Comment faire pour rendre les formules de mise en forme conditionnelle également lisibles ?

Pas de chance ! Les références structurées ne sont pas supportées par cette fonction ! (pas encore… j’espère que cela arrivera un jour !).

Mais, j’ai tout de même une petite astuce pour cela ! Cela s’appelle les “noms”. Oui, oui, les “noms”.

Excel - MFC - Les noms

Cela permet de donner un nom à une plage ou à une cellule. Ainsi, au lieu de désigner les cellules selon leur adresse (A1, K2, …), il est possible de donner un nom à une cellule et d’y faire référence dans les formules.

Reprenons le premier exemple de cet article avec la mise en évidence des doublons.

Excel - Mise en forme conditionnelle - TexteLa formule était : =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX).

Vous pouvez sélectionner toutes les cellules contenant les n° de commandes (de A2 à A19) et leur donner un nom dans la zone nom directement.

Excel - MFC - Donner un nom

La zone nom correspond à cette zone où se trouve habituellement le nom d’une cellule. Ici, j’ai choisi le nom “NoCommandes”.

Ensuite, vous allez pouvoir changer votre formule dans la condition de mise en forme en :

=SI(NB.SI(NoCommandes;A2)>1;VRAI;FAUX)

Cela peut paraître anodin comme changement, mais je vous assure que lorsque vous avez des fichiers Excel complexes avec de très nombreuses mises en forme conditionnelles en place, identifier le périmètre concerné par une formule fait gagner beaucoup de temps !

Dernière action

Avez-vous trouvé la réponse à votre question ? En avez-vous d’autres ? Votre feedback compte pour moi. Un petit commentaire en dessous de l’article me confirmerait que vous avez lu jusqu’ici ! Merci !

Les 4,5 apprentissages qu’un cours sur Excel devrait vous enseigner

Les 4,5 apprentissages qu’un cours sur Excel devrait vous enseigner

Qu’est-ce qu’un cours sur Excel devrait vous enseigner ? Par où commencer pour apprendre à utiliser un outil aussi puissant qu’Excel ? Quel plan de formation suivre ? Faut-il faire le tour des fonctions les unes après les autres et laisser l’apprenant repartir seul avec une boîte à outils en main affronter ses problèmes ? Je vous propose dans cet article de vous présenter les enseignements minimaux qu’une formation sur Excel devrait vous procurer.

Premier apprentissage d’un cours Excel : ce que n’est pas Excel !

Petite anecdote pour commencer

Quand on enseigne Excel, on peut légitimement déclarer que c’est un outil extrêmement puissant. A tel point qu’on pourrait être amené à déclarer qu’on peut tout faire avec. Hors, tous ceux qui assistent les usagers d’Excel ont un jour ou l’autre eu à l’esprit la pensée suivante :

“Mais Excel n’est pas le plus adapté pour cet usage… mais bon, ce serait trop compliqué de tout reprendre à zéro… alors je vais aider pour cette fois, et après, ben… on verra”.

Apprentissage n°1 d’un bon cours Excel : les exemples de mauvais usage !

Bon-outil-cours-Excel

Un bon cours sur Excel devrait d’abord commencer par présenter les autres outils de la suite Office qui sont là et permettent eux aussi de faire plein de belles choses. Les deux mauvais usages les plus fréquemment observés sont :

  1. Considérer Excel comme une application de gestion de base de données. J’insiste sur le mot “Gestion”. Car à proprement parler, une base de données est un système stockant un ensemble de données, et Excel fait cela très bien. Dans le mot “gestion”, il faut comprendre “toutes les manipulations des données de manière structurée”. Ainsi, gérer des commandes, des fournisseurs, des clients nécessitent des écrans de saisie, des règles de gestion (pas deux fois le même fournisseur, un numéro unique par commande, etc.). Pour cet usage, il y a Access ! Il est exactement fait pour cela. Sinon, vous devrez vous mettre à la programmation dans Excel avec des macros. Et si vous avez déjà lu mon avis sur les macros dans Excel, vous savez pourquoi je suis contre.
  2. Utiliser Excel comme un outil de traitement de texte ! Combien de fichiers Excel ai-je vus avec une mise en page de bon de commande, de facture ou toute sorte de fiches d’information. Et à ce moment-là, toutes les galères du monde sur la gestion de la mise en page des impressions des feuilles Excel s’abattent sur vous. Et encore, vous retombez ensuite dans le besoin de consolider des données se trouvant dans des feuilles Excel différentes…

Une fois qu’on a vu ces deux cas, et surtout, qu’on a montré que d’autres outils existent pour cela, on a déjà mieux commencé.

Second apprentissage d’un bon cours Excel : le besoin de concevoir avant de faire

Conception-structure-cours-Excel

Notre envie primaire de faire le plus vite possible nous amène à rentrer dans le vif du sujet trop rapidement. Un classeur, des feuilles de calcul, cela se conçoit ! Les données, cela se structure !

Apprendre aux apprenants que pour analyser des données, il est essentiel de se poser des questions de base :

  1. D’où vont provenir les données ? Saisies manuelles ou importées d’un système tiers ?
  2. Y a-t-il des données dites “de référence” (liste de pays, liste de fournisseurs possibles, etc.) ?
  3. Quelles seront les natures de données (dates, nombres, texte, montant, etc.) ?
  4. Y a-t-il un besoin de cohérence sur les données (date de début doit être inférieure à date de fin) ? Qui, quand et comment fera-t-on la vérification de cette cohérence ?
  5. Et bien d’autres questions…

Lorsqu’on a compris cela, on approchera alors chaque situation avec un regard différent. Le cours sur Excel pourra nous apprendre tout ce qu’Excel peut faire, mais il n’avertira pas d’un problème de conception… et ce serait bien dommage.

Troisième apprentissage d’un bon cours Excel : connaître la pire fonctionnalité d’Excel

Un formateur Excel qui ne vous le dit pas cherche à vous laisser générer des problèmes afin que vous reveniez vers lui… c’est comme vous enseigner à conduire sans vous dire qu’il y a un code de la route…!

Mais quelle est donc cette terrible fonctionnalité ? La pire fonctionnalité d’Excel est bien sûr la fusion de cellules !

Fusionner-cellule-cours-Excel

La fusion de cellules est à la conception ce que la démolition est à la construction. Son opposé ! Comme toujours, toute fonctionnalité a des avantages et des inconvénients. Elle est parfois utile pour certaines situations, mais il est essentiel d’informer du mal qu’elle procure dans de nombreuses situations.

Excel est un tableur ! Il est basé sur des colonnes et des lignes. TOUT est basé sur cette conception de l’outil. La fusion de cellule vient justement détruire toutes les fondations de l’outil. Elle doit être utilisée comme tous les produits dangereux… avec parcimonie et étant certain qu’il n’y a pas d’autres solutions.

Quatrième apprentissage d’un bon cours Excel : documenter le fonctionnement de son fichier Excel

Avant de rentrer dans le vif de ce chapitre, je vous fais une petite confidence… Cet apprentissage est celui qui m’a le plus manqué. Étant un fan inconditionnel d’Excel, je suis tombé dedans quand j’étais petit et j’ai eu l’occasion de créer des fichiers Excel où je me suis fait plaisir… Mises en forme conditionnelles, tableaux croisés dynamiques multi-tables, modèles de données, sources externes, etc. Tant que j’étais là pour l’alimenter, le corriger au fil de l’eau, pas de problème. Et puis est arrivé le moment où j’ai passé la main à un collègue.

Je lui ai pourtant tout expliqué. C’était presque une fierté de montrer toutes les fonctionnalités utilisées, je suis donc certain d’avoir TOUT montré. Mais je ne l’avais pas documenté par écrit.

Je n’avais pas présenté comment l’utiliser simplement (ce qui était le cas). Ou au minimum, informer de ce qu’on pouvait ET ce qu’on ne pouvait pas faire.

Quelques mois plus tard, j’ai constaté toute la dérive et le dysfonctionnement auquel cela a conduit.

Documenter-cours-Excel

Une bonne structure doit pouvoir être décrite simplement. Les fonctions de base (ajouter des lignes de données, alimenter des données de référence, supprimer des données) doivent être décrites comme un manuel qui accompagne tous les objets de votre vie quotidienne.

Ainsi, chaque utilisateur de votre feuille Excel a un manuel qu’il peut consulter, mais à votre niveau, vous avez aussi de quoi démontrer que des consignes n’ont pas été suivies.

Et le demi apprentissage alors ?

Le demi apprentissage supplémentaire qui devrait être enseigné est qu’Excel est un outil pour analyser des données, faire des calculs/des statistiques et présenter des graphiques. Pourquoi est-ce un demi apprentissage ? Parce que si vous êtes à un cours Excel, c’est que vous le savez déjà un peu ! Alors pourquoi le redire ? C’est essentiel de rappeler à la fin ce qu’est Excel ! (on a commencé par indiquer ce que ce n’était pas…) pour que chacun comprenne qu’un outil est performant lorsqu’on l’utilise pour ce à quoi il est destiné. Et ainsi diffuser le message que pour les autres usages, peut-être existe-t-il un autre outil plus approprié ? (Access, Word, Visio, Project pour rester chez Microsoft, mais bien d’autres existent aussi !)

Et vous ? Qu’attendez-vous d’un bon cours Excel ?

Utilisez la zone des commentaires pour me faire part de vos attentes sur un bon cours Excel. N’hésitez pas à partager votre point de vue sur cet article ! Je suis ouvert au débat !

Et dans tous les cas, allez voir comment je vous propose d’apprendre Excel et de faire vos premiers pas avec.

[Tuto] Comment créer un graphique croisé dynamique ?

[Tuto] Comment créer un graphique croisé dynamique ?

Un graphique croisé dynamique (ou GCD de son petit nom) est un graphique lié à un tableau croisé dynamique (ou TCD). Il est donc impératif de maîtriser (si ce n’est exceller…!) les TCD. Vous avez deux manières de débuter votre premier graphique croisé dynamique, soit vous partez de vos données brutes, soit vous partez d’un TCD existant. Je vous présenterai dans cet article deux fonctions additionnelles que seuls les GCD et TCD possèdent, mais que je trouve plus intéressantes visuellement avec un graphique croisé dynamique.

Tout comme les TCD, une fois que vous avez goûté aux graphiques croisés dynamiques, vous ne reviendrez plus jamais en arrière !

Introduction

Présenter ses données sous forme d’un graphique est toujours plus intéressant que de les présenter sous forme d’un tableau de valeurs. Les différences entre les valeurs seront visuellement remarquables alors qu’entre plusieurs nombres juxtaposés, cela peut devenir un calvaire. En bref, un dessin vaut mieux qu’un long discours.

Les graphiques croisés dynamiques apportent en plus la capacité de consolider des données automatiquement selon le ou les critères de votre choix. C’est très utile (si ce n’est essentiel) lorsque vos données sont décrites de manière détaillée.

Ainsi, constituer un GCD basé sur une liste de valeurs avec seulement deux colonnes (nom, valeur) aura moins d’intérêt que si votre GCD s’appuie sur une liste de valeurs détaillées (n° de facture, n° de ligne de commande, nom client, ville client, montant HT, article commandé, lieu initial stock, fournisseur article, ville fournisseur, etc.).

Étape 1 : Préparez vos données

Cette étape reste essentielle pour tous les traitements dans Excel. Je reprends ici la même liste que dans mon article sur les TCD :

Voici la liste des actions minimales et obligatoires de préparation :

  1. Chaque colonne de votre tableau doit posséder un intitulé
  2. Chaque intitulé doit être unique
  3. Votre tableau ne contient pas de lignes ou de colonnes fusionnées
  4. Des données sont présentes à toutes les lignes (pas de saut de ligne pour aérer ou “faire joli”)

La liste des actions supplémentaires suivantes n’est pas obligatoire pour débuter avec la fonction graphique croisé dynamique d’Excel.

Toutefois, pour exploiter les GCD au mieux, je vous les recommande :

  1. Pour les valeurs vides, laissez le champ réellement vide et n’écrivez pas N/A par exemple
  2. Respectez un formatage homogène des dates et reconnu par Excel en tant que date
  3. Donnez des noms de colonnes qui sont parlants pour vous sur la nature des contenus
  4. Soignez la saisie des valeurs textuelles en restant homogène (“Paris”, “paris”, “PARIS” ne sont pas homogènes)

Étape 1bis : Suivez l’exemple par vous-même !

Je vous invite à télécharger ce fichier afin de disposer de votre côté des mêmes données d’illustration. Ainsi, vous devriez être en mesure d’arriver aux mêmes résultats que moi.

L’exemple fourni se base sur les données publiques mises en ligne par le gouvernement français sur le site https://www.data.gouv.fr, en particulier, j’ai utilisé ces données : https://www.data.gouv.fr/fr/datasets/base-de-donnees-de-la-pollution-aerienne-aux-abords-des-ecoles-et-creches-dile-de-france/

Comme il m’a été nécessaire de préparer les données, je vous invite à récupérer le fichier prêt à l’emploi.

Étape 2 : Insérer votre graphique croisé dynamique

Cas 1 : Vous souhaitez insérer directement un graphique croisé dynamique

Dans le ruban, vous cliquez sur “Insertion” puis vous disposerez de l’option “Graphique croisé dynamique” comme indiqué ci-dessous.

Excel - Graphique Croise Dynamique - Insertion

Cas 2 : Vous disposez déjà d’un tableau croisé dynamique

Dans ce cas, vous trouverez, comme présenté sur l’illustration ci-dessous, comment insérer un graphique croisé dynamique lié au tableau croisé dynamique.

Pour cela, vous cliquez préalablement sur votre tableau croisé dynamique afin de faire apparaître le ruban associé, puis vous trouverez le bouton “Graphique croisé dynamique” comme ceci :

Excel - Graphique Croise Dynamique - Insertion - Option 2

Dans les deux cas, vous disposerez d’un tableau croisé dynamique et d’un graphique croisé dynamique.

Votre affichage devrait ressembler à celui-ci :

Excel - Graphique croise dynamique vide

Étape 3 : Posez votre question à Excel !

Habituellement, lorsqu’on insère un graphique dans Excel, un graphique apparaît ! Or, ici, il n’y a rien ! Pourquoi ?

Excel - Graphique Croise Dynamique - Champs - ZonesC’est exactement comme pour les TCD, c’est à vous de poser une question et de sélectionner les colonnes/lignes qui vont permettre d’y répondre.

Vous devrez alors glisser-déposer vos champs disponibles dans la bonne case en bas à droite parmi les quatre zones réservées.

Pour les TCD, nous avions FILTRES, COLONNES, LIGNES et VALEURS. Pour un GCD, vous aurez FILTRES, LÉGENDE, AXE et VALEURS.

Comme je vous l’ai indiqué, un GCD sera toujours lié à un TCD, ainsi, la LÉGENDE correspondra aux COLONNES et l’AXE aux LIGNES.

Il ne reste maintenant qu’à faire une première mise en situation.

Cas pratique : Représentez l’évolution de NO2 par département

Pas de panique, je ne vous demande pas de chercher en dehors des données du fichier que je vous ai préparé !

Il vous faut maintenant sélectionner les champs en les glissant dans la bonne zone réservée.

>>Allez-y, essayez de votre côté !

Petite astuce Excel complémentaire : lorsque vous cochez la case d’un champ de type nombre, Excel vous le place automatiquement dans la zone valeurs et sélectionne également la fonction “Somme”. Si c’est un champ d’un autre type, Excel le placera aussi dans la zone Valeurs, mais utilisera la fonction “Nombre”.

Vous devriez avoir sélectionné les champs suivants :

Excel - GCD - Evolution NO2 par departement

Et vous devriez voir ceci : (j’ai légèrement déplacé le graphique)

Excel - GCD - Evolution NO2 par departement - DATA

Première amélioration à apporter à ce GCD

Les intitulés dans le graphique ne sont pas très lisibles (on ne voit que le texte “Somme de…”. On peut les modifier ! Comme le TCD et le GCD sont totalement liés, tout ce que vous apporterez comme modification à l’un sera automatiquement répercuté sur l’autre.

Je vous propose donc de changer les noms des colonnes qui se présentent dans la table de valeurs. Au lieu de “Somme de NO2_2012”, je saisis simplement “NO2 2012”, et ainsi de suite pour les autres colonnes.

Cela donne maintenant :

Excel - GCD - Evolution NO2 par departement - DATA Amerlioration 1

Tout est à jour automatiquement, sans aucune autre intervention ! Idem pour le graphique et la légende du graphique.

Cas pratique 2 : Représentez l’évolution du NO2 par département et par type d’établissement

Lorsqu’on se met à ajouter des critères de représentation (par xxx et par yyy), il faut bien se poser la question de l’ordre des mots !

Est-ce que vous souhaitez comparer les types d’établissement au sein d’un même département ou bien est-ce que vous souhaitez comparer un même type d’établissement entre les départements ?

C’est cette information qui déterminera l’ordre des champs dans la zone AXE.

La première question donnerait ce résultat :

Excel - GCD - Evolution NO2 par departement et type - DATA

Avec cet ordre des champs :Cas 1 - Ordre type departement

On compare les types d’établissement dans un même département.

La deuxième question donnerait ce résultat :

Excel - GCD - Evolution NO2 par type et departement - DATA

Avec cet ordre des champs :Cas 2 - Ordre type departement

On compare entre les départements le même type d’établissement.

Étape 4 : Triez, filtrez vos graphiques croisés dynamiques

En regardant le graphique ci-dessus, il semble légèrement désordonné. Il faudrait trier par ordre décroissant les résultats selon le taux de NO2 de 2012 et classer les départements selon le même ordre.

Si au passage on retirait les résultats pour les établissements de type “NA”, ce serait mieux (dans le cas présent, cela n’apporte pas d’information significative).

Pour ces actions, selon sa préférence, on pourra faire le tri sur le TCD ou directement sur le graphique croisé dynamique. Les deux étant liés, le résultat sera le même.

Pour le faire depuis le graphique, il faut repérer la petite flèche et explorer ses possibilités.

Découvertes des possibilités offertes

Excel - GCD - Filtrer - Trier depuis graphiquePlusieurs possibilités se présentent à vous :

  1. Filtrer directement les types que vous souhaitez voir ou que vous souhaitez retirer (en cochant ou décochant les choix)
  2. Filtrer sur les étiquettes (comprendre “le type d’établissement”)
  3. Le dernier filtre, celui s’appliquant aux valeurs
    1. Il faudra choisir un des champs valeurs à disposition (NO2_2012 par exemple)
    2. Déterminer un opérateur de comparaison
    3. Déterminer une valeur fixe de comparaison (cela ne peut pas être un champ variable)
  4. Trier simplement par ordre alphabétique (ou alphabétique inverse). Dans le cas présent, ce sera sur l’intitulé du type d’établissement.
  5. Enfin, des “options de tri supplémentaires…”Excel - Graphique Croise Dynamique - Options de tri supplementaires

Allons voir cette dernière option.

On découvre alors qu’il est possible de trier manuellement les types d’établissement ! Si aucun choix automatique ne vous convient, faites celui qui vous arrange !

Tri manuel

Excel - Graphique Croise Dynamique - Tri manuelPour réaliser ce tri manuel, il suffit de sélectionner le champ que l’on souhaite déplacer (ici, le type “NA”). Lorsqu’on le déplace, soudain, Excel montre une barre verte et montre que c’est un ensemble qui va être déplacé et pas uniquement un champ.

On peut alors le positionner après une autre valeur “type” selon votre préférence. Dans l’exemple ci-contre, je le place après “primaire”.

Ascendant par / Descendant par

Dans cette option, vous avez l’ensemble des champs valeurs à disposition. Cela signifie que vous pouvez déterminer l’ordre du tri des types d’établissement selon les valeurs des champs.

Résoudre la question posée

Pour rappel, il s’agit de retirer les “NA” de l’affichage et de trier dans l’ordre descendant selon les valeurs de NO2 de 2012.

Facile !

Il ne faut juste pas oublier de réaliser un filtre sur les types, et le tri sur les départements ! Car oui, ce sont les départements qui doivent être triés à l’intérieur de chaque type…

Excel - Graphique Croise Dynamique - Cas pratique

Étape 5 : Passez aux graphiques croisés dynamiques interactifs !

Ces fonctions supplémentaires qu’apportent les graphiques croisés dynamiques sont de véritables plus qui justifient à eux seuls l’utilisation d’un GCD au lieu d’un graphique standard.

A savoir que ces fonctions sont également disponibles pour les TCD.

Ces deux fonctions sont les segments et la chronologie.

Elles ne sont disponibles que dans les rubans dédiés aux TCD et GCD.

Excel - GCD - Segments et Chronologie

Pour pouvoir insérer une chronologie, il faut disposer d’un champ de type date. Les données sources ne sont pas structurées de manière appropriée pour cela.

Au lieu d’avoir une ligne avec 6 colonnes de données (NO2_2012, NO2_2013, etc.), il aurait fallu disposer de 6 lignes par établissement avec deux colonnes : “Date du relevé” et “Valeur”. Nous aurions pu obtenir tous les graphiques illustrés dans cet article. En prime, nous aurions eu accès à la fonctionnalité “Chronologie”…

La structure de vos données sources est très importante pour pouvoir utiliser Excel à pleine puissance !

Qu’apporte la fonction “Segment” ?

Intrinsèquement, la fonction Segment n’est rien d’autre que l’application d’un filtre sur les données. Toutefois, au lieu d’aller dans les options avancées et de cocher/décocher des cases, vous disposez d’un visuel bien plus explicite.

C’est d’autant plus utile que lorsque vous disposez de plusieurs filtres actifs en même temps, vous pouvez soudain avoir un doute sur les filtres appliqués.

Voyons ce que cela donne de plus près en ajoutant un segment sur les types et les départements :

Excel - GCD - Choix segment

De nouveaux objets font leur apparition à l’écran, les fameux segments.

Excel - GCD - Utilsation segment

Vous pouvez alors filtrer bien plus facilement les données, faire des sélections multiples croisées. Ceci est possible soit en cliquant avec la touche “Ctrl” appuyée, soit en faisant un “Cliqué-glissé-relâché” pour sélectionner directement plusieurs valeurs adjacentes.

Le fichier résultat se trouve ici.

Étape 6 : Donnez-moi votre avis !

Qu’avez-vous pensé de ce tutoriel ? Avez-vous d’autres questions ? Souhaitez-vous en savoir plus ?

Votre avis est important ! Je vous remercie de me laisser un petit mot ci-dessous dans les commentaires.

[SÉCURITÉ] Méfiez-vous des fichiers au format XLS !

[SÉCURITÉ] Méfiez-vous des fichiers au format XLS !

Se méfier des fichiers au format xls ? Comment est-ce possible ? Pourquoi se méfier des fichiers Excel ? Les fichiers dont l’extension se termine par .xls sont en effet des fichiers Excel. Cela fait des années que cette extension est connue de tous. Pour quelle raison vous interpeller avec cette alerte soudaine ?

En connaissant les menaces numériques qui rôdent… les faiblesses de notre propre cerveau face à des hackers… Et je vous assure que les hackers s’intéressent à vous

Prenez votre café en main et échangez quelques minutes de votre temps contre le contenu de ce post. Vous comprendrez l’extension .xls et surtout, le danger que vous courez avec les fichiers .xls.

Quelles différences entre les formats XLS, XLSX, XLSM ?

L’ancien format XLS

Les fichiers .xls sont au format Excel 2003. Ils contiennent toutes les fonctionnalités permises à cette version-là. Il faut savoir qu’à cette époque, les macros Excel n’étaient pas considérées comme une menace, mais uniquement comme une fonctionnalité standard. Je reviendrai un peu plus loin sur cette source de menace que sont les macros.

L’actuel format XLSX

Avec Excel 2007, Microsoft a réalisé une mise à jour majeure de son format interne de fichier. C’est un peu comme si vous changiez le moteur de votre véhicule essence en véhicule électrique. Il faudrait plus qu’un article pour vous décrire le nombre de changements opérés.

Toutefois, certains d’entre eux méritent qu’on s’y attarde.

Premier changement majeur des fichiers .xlsx

Les fichiers .xlsx ne peuvent plus contenir des macros exécutables (dit contenus “actifs”). Ce ne sont que des contenus passifs : des formules, du texte, des mises en forme, des paramètres, mais pas de code exécutable.

Deuxième changement majeur des fichiers .xlsx

Le contenu d’un fichier .xlsx est désormais un standard international (ISO 29500-1:2008-2016). Autrement dit, il n’est pas la propriété de Microsoft (d’autres logiciels peuvent fabriquer des fichiers xlsx et les lire sans payer de royalties à Microsoft). Petite cerise sur le gâteau, il est possible d’extraire très facilement du contenu de ces fichiers (en particulier les images).

Troisième changement majeur des fichiers .xlsx

Le format interne des fichiers a été simplifié et optimisé. Les fichiers sont moins volumineux en .xlsx qu’en .xls. Grâce à cette optimisation, les fichiers peuvent désormais contenir bien plus de cellules en lignes et en colonnes ! Le dernier X de l’extension y fait d’ailleurs référence. Le format interne utilise désormais le format dit XML (eXtensible Markup Language).

Le “nouveau” format XLSM

Si les macros ont été retirées du format de fichier “standard”, il fallait bien les mettre à un endroit. Le M final signifie “avec macros”. Les macros sont des morceaux de programme qui peuvent être embarqués avec vos fichiers Office. Il est ainsi possible d’automatiser certaines activités et de créer des programmes entiers. Personnellement, j’ai mon avis sur la question des macros dans Excel.

Toutefois, en connaissant les menaces numériques qui rôdent… les faiblesses de notre propre cerveau face à des hackers… je ne peux que vous recommander de prévenir vos contacts avant de leur envoyer des fichiers avec des macros !

Et je vous assure que les hackers s’intéressent à vous

À quoi sert une extension de fichier ?

Tous vos fichiers disposent d’une extension de fichier. Ce sont les quelques lettres qui se trouvent après le point du nom du fichier (.xls, .xlsx, .xlsm, .xlst pour les fichiers Excel). Ces extensions ont plusieurs utilités.

L’extension du fichier pour votre système d’exploitation

Votre ordinateur, votre tablette et votre smartphone utilisent tous un système d’exploitation. Cela peut être Windows, MacOS, Android, Linux ou bien d’autres variantes (ChromeOS, RedHat, Ubuntu, etc.). On parlera du SE, Système d’Exploitation ou sa version anglophone OS pour Operating System.

L’OS dispose d’une table de correspondances entre chaque extension qu’il connaît et chaque programme qu’il connaît. Ainsi, les fichiers .xls sont associés à Excel. Windows sait qu’il doit ouvrir Excel et lui transmettre le fichier .xls sur lequel vous avez double-cliqué.

Avec Windows 10, voici un extrait de ce que l’on peut trouver pour Excel :

Liste des extensions de fichiers Excel - XLS

Et pour d’autres extensions :

Liste des extensions de fichiers

L’extension du fichier pour l’application

Une fois que l’OS a ouvert l’application associée et lui a transmis le fichier de contenu (votre fichier .xls par exemple) il a terminé son travail. C’est désormais à l’application de travailler.

L’extension va donner des instructions supplémentaires à une application. Elle va lui indiquer en particulier son format interne. Par exemple, le format .xls indique “Feuille de calcul Microsoft Excel 97-2003”. C’est un format qui a fait son temps !

Alors, pourquoi se méfier des fichiers au format xls ?

Je vous le résume ici : un fichier xls est un fichier utilisant un format qui a plus de 20 ans, qui a été conçu avant 1997 ! Internet n’était pas ce qu’il est aujourd’hui et les attaques informatiques n’étaient pas aussi répandues qu’actuellement ! Les fichiers .xls se présentent comme de simples fichiers Excel et peuvent contenir des macros, c’est-à-dire des programmes qui peuvent compromettre tout votre ordinateur.

Si on vous envoie, ou que vous envoyez des fichiers en .xls, c’est comme envoyer le message suivant :

  • Je n’utilise que des vieux fichiers qui ont entre 15 et 20 ans minimum.
  • Le fichier contient peut-être un virus, à toi de voir si tu veux jouer avec moi.
  • Je n’ai pas compris que c’est dangereux.

Vous l’aurez compris, ne m’envoyez jamais de fichier .xls…

Post-scriptum

Vous vous en douterez peut-être, mais autant être explicite. Ce qui est écrit ici pour les fichiers Excel, l’est tout autant pour les fichiers Word et PowerPoint.

La même histoire a été écrite pour les fichiers Word avec les extensions .doc, .docx et .docm. Idem pour PowerPoint avec les formats .ppt, .pptx et .pptm !

Si vous avez trouvé que le temps de votre café a été bien employé, je vous remercie de me le dire dans les commentaires. D’ailleurs, si cela n’a pas été le cas, faites-le moi savoir pour que je me rattrape !

[Tuto] Comment maîtriser la zone d’impression Excel ?

[Tuto] Comment maîtriser la zone d’impression Excel ?

Définir ou supprimer une zone d’impression sur Excel est parfois vital ! Si vous n’avez pas défini la zone d’impression sur Excel, vous finissez par imprimer un document Excel sur 14 pages ! (Et encore, avec 14 pages, on est parfois chanceux !) Cet article va développer les différents moyens de gérer la zone d’impression avec Excel.

On ne comprend pas pourquoi un caractère “espace” est allé se placer dans la cellule XFD1048576, et là, Excel est sans pitié, il n’y a qu’à regarder cet aperçu avant impression avec un peu d’attention :

Excel - Apercu avec une cellule vide tout au fond du classeur

Vous avez bien lu, 41 606 593 pages d’impression à cause d’un caractère perdu au mauvais endroit d’un classeur. Heureusement que l’imprimante n’a qu’un bac de 500 feuilles et que toutes les pages “imprimées” seront blanches… mais quand même…!

Plusieurs options de remédiation s’offrent à vous, je vous propose d’en faire le tour dans cet article.

Option n°1 : Ne pas définir de zone d’impression !

La première possibilité que vous avez pour empêcher ce drame écologique d’arriver, c’est de sélectionner les cellules que vous souhaitez imprimer et de choisir dans les options d’impression “Imprimer la sélection” :

Zone d'impression Excel - Imprimer la sélection
Imprimer la sélection sans définir de zone d’impression

Selon les plages d’impression que l’on souhaite imprimer, cette solution ne sera pas satisfaisante, mais permettra d’éviter la quête du caractère perdu dans le classeur.

Premier point de vigilance

Vérifiez que tous les contenus que vous souhaitez imprimer sont visibles. Lorsque vous sélectionnez manuellement les cellules à imprimer, si des colonnes ou des lignes ont été masquées, elles ne seront pas imprimées.

Deuxième point de vigilance

Veillez à sélectionner l’ensemble des cellules que vous souhaitez imprimer en évitant les “clics perdus” qui désélectionneraient tout. Cela m’arrive bien trop souvent avec le touch pad de mon ordinateur portable…

Option n°2 : Rendre visible les ruptures de pages (toujours pas de zone d’impression !)

Comme je vous l’ai indiqué au tout début, vous n’êtes ni les premiers, et malheureusement pour notre environnement, ni les derniers à imprimer trop de pages depuis Excel. Et pourtant, les concepteurs d’Excel ont pensé à vous !

Outre la fonction “Aperçu avant impression”, ils en ont prévu une autre pour voir très concrètement l’impact de la mise en plage sur les  impressions.

Prenons un exemple de fichier, et pour travailler avec le même que dans mes articles précédents, voici le lien vers les statistiques de flashs réalisés en France en 2017. Une fois ouvert dans Excel, converti en tableau puis mis en forme, nous obtenons ce résultat :

Zone d'impression Excel - Exemple de données

Quel serait le résultat de l’impression de ce tableau ?

Étape 1 : Activer l’affichage avec sauts de page

Rendez-vous sur le menu “Affichage” puis cliquez sur le bouton “Avec sauts de page”

Excel - Passage en mode avec sauts de page

Votre affichage devient alors :

Exemple de données avec affichage sauts de page

Étape 2 : Comprendre comment interpréter ce qui est présenté

On voit les informations essentielles suivantes :

  1. Une grande zone gris foncé
  2. Une information des numéros de page
  3. Un encadré bleu ainsi que des traits en pointillés bleus

La zone grise

La zone gris foncé correspond à ce qui ne sera pas imprimé. Si vous ne voyez aucune zone gris foncé comme sur l’image ci-dessous, c’est que du texte est présent quelque part dans ces cellules.

Il suffira de sélectionner toutes les cellules qui devraient être vides et d’en supprimer le contenu.

La numérotation des pages

C’est l’ordre dans lequel les pages seront imprimées. Ainsi, on voit ici que le contenu est découpé en colonnes, et que l’imprimante sortira toute la première colonne, puis la deuxième.

Une option existe pour que les impressions sortent par ligne.

  1. Cliquez sur le menu “Mise en page”
  2. Ensuite, cliquez sur l’icône en bas à droite du groupe de boutons “Mise en page”
  3. Rendez-vous sur l’onglet “Feuille” de la fenêtre qui vient d’apparaître
  4. Sélectionnez l’option de votre choix dans le bloc “Ordre des pages”
Zone d'impression Excel - Ordre des pages

Lorsque vous changez d’option, la numérotation des pages s’adapte à votre choix.

L’encadré bleu et les traits en pointillés bleus

Ces encadrements vous montrent :

  • La totalité du contenu qui sera imprimé (encadré bleu plein)
  • Où seront les ruptures de pages (traits en pointillés)

Les traits en pointillés sont extrêmement pratiques pour adapter la largeur des colonnes ou des lignes, le choix d’une impression en mode paysage ou portrait ou encore les largeurs des marges d’impression.

Option n°3 : Passer en mode “Mise en page”

Le mode “Mise en page” est le mode le plus complet et le plus déroutant aussi. Il vous montrera de manière très visuelle les pages avec leur contenu, leur en-tête et pied-de-page, etc.

Excel - Zone d'impression - Passage en mode mise en page

Voici ce qu’il y aura de déroutant :

Excel - Mise en page des impressions - Exemple de données avec affichage mise en page

Comme vous le verrez, les colonnes et les lignes sont désormais espacées ((1) sur l’image). On ne peut pas réduire cet espace blanc. On peut le combler en diminuant la largeur de l’une des colonnes qui précèdent ou celle la colonne suivante. A un moment, l’affichage va s’adapter dès qu’il y aura assez de place.

Une nouvelle zone fait également sont apparition ((2) sur l’image). Il s’agit de l’en-tête (son compagnon “pied-de-page” est également de la partie).

Lorsqu’on clique sur cette zone, un nouveau menu fait son apparition.

Excel - Zone d'impression - Mode mise en page - En-tête et pied de page

On retrouve alors toutes les valeurs qu’il sera possible d’insérer automatiquement sur les impressions : date, numéro de page, nom du fichier, etc.

La première fois que j’ai découvert ce menu, c’est la possibilité de disposer d’une première page différente (comme dans Word) qui m’a le plus surpris !

Option n°4 : Définir la zone d’impression

Last but not least, vous pouvez également définir l’ensemble des cellules à imprimer au lieu de laisser Excel décider pour vous. C’est ce qu’il s’est passé avec les deux précédentes options !

Contrairement à la toute première option, vous ne sélectionnez pas la zone à imprimer. Vous la figez une fois pour toutes.

Pour cela, vous devez suivre les étapes suivantes :

  1. Sélectionnez les cellules que vous souhaitez imprimer
  2. Allez dans le menu “Mise en page”
  3. Cliquez sur le bouton “ZoneImpr”
  4. Puis sur “Définir”
Excel - Zone d'impression - Définir une zone d'impression

A partir de ce moment, seules ces cellules seront “imprimables”. Vous ne pourrez imprimer d’autres cellules qu’à la condition d’annuler la zone qui vient d’être définie.

On pourra voir en passant en mode “Avec sauts de page” que seules ces cellules sont sur fond blanc.

Excel - Zone d'impression - Zone d'impression avec affichage sauts de page

A votre tour de jouer !

Excel vous pose encore des problèmes d’impression ?

Alors non, votre ordinateur n’est pas hanté par le fantôme du caractère perdu ni même nécessairement piraté par un malin qui cherche à vous faire imprimer plus de pages.

Plusieurs situations fréquentes peuvent être à l’origine de ce problème.

Situation 1 : le modèle Excel à l’origine de votre fichier dispose de ce caractère perdu

Ainsi, tous les fichiers qui ont été créés à partir de celui-ci en ont hérité. Ici, pas de chance, aucune solution autre que nettoyer le modèle (pour arrêter la contagion) et corriger tous les fichiers qui ont hérité de ce caractère.

Situation 2 : mes fichiers sont le fruit de l’exportation d’une autre application

C’est l’une des situations les plus fréquentes ! Une de ces applications n’est autre que le système de reporting de mon entreprise qui me permet d’exporter les données au format Excel, l’export vers Excel pouvant prendre deux formes majeures tout à fait différentes : les fichiers “.xlsx” (ou .xls pour les plus anciens) et les fichiers “.csv” (csv = comma separated values = valeurs séparées par des virgules).

C’est dans le premier cas (.xlsx) que trop souvent les moteurs d’exportation des données vont “remplir de vide” les lignes n’ayant pas de données à exporter. Toutes les lignes jusqu’au fond du classeur sont ainsi mises en forme.

Le moteur d’impression d’Excel détecte alors un contenu et l’inclut dans le processus d’impression.

Imprimer en mode paysage et portrait depuis le même classeur

Chacune de vos feuilles de votre classeur peut également avoir sa propre mise en page d’impression !

Pour cela, vous devez définir pour chacune des feuilles si elle doit s’imprimer en paysage ou en portrait. Par défaut, la configuration sera en mode portrait.

Définir l’orientation de la zone d’impression d’Excel

Définir une zone d’impression contre un commentaire !

Cet article vous a plu ? Vous maitrisez désormais comme jamais les zones d’impressions sur Excel. Si vous pensez que cet article y est pour quelque chose, laissez-moi un petit commentaire et n’hésitez pas à partager avec votre entourage !

[Tuto] Réussir son premier tableau croisé dynamique avec Excel !

[Tuto] Réussir son premier tableau croisé dynamique avec Excel !

Tous les débutants ont entendu parler du fameux “Tableau croisé dynamique” dans Excel (TCD pour les intimes, et Pivot Table pour les anglosaxons). Je vous propose dans ce tutoriel de vous initier et vous accompagner dans vos premiers pas. D’autres articles suivront pour aller le plus loin possible.

Menu Insertion - Tableau croisé dynamique

Introduction

Excel est connu pour permettre de stocker des listes sans fin (enfin si, 1’048’576 lignes et 16’384 colonnes exactement). Une fois ces données saisies ou importées, on peut les mettre dans des tableaux, les filtrer, les mettre en forme, faire toutes sortes de calculs. Et c’est déjà pas mal.

Jusque là, Excel fait un travail de tableur tout à fait honorable. Mais il n’aurait sans doute pas eu le même succès sans les fonctions d’analyse comme la fonction “tableau croisé dynamique” (par la suite, j’utiliserai son petit nom : TCD).

Une fois que vous y avez goûté, que vous avez compris la puissance que vous pouviez en tirer, il n’y a rien à faire, dès que vous voyez un tableau de données, vous ne pensez qu’à ça… Alors, allons-y !

Étape 0 : À quoi ça sert un TCD (Tableau Croisé Dynamique) dans Excel ?

J’aurais bien envie de vous dire “A croiser les données !”, mais ce serait un peu léger comme explication…

Cela sert à faire des résumés consolidés de vos données. Qui dit résumé, dit qu’il faut de la matière au départ. Et ici, notre matière, ce sont les lignes de données d’un tableau. Selon votre contexte et votre usage, cela peut être des listes de factures ou de commandes, des listes d’articles, de projets, de résultats sportifs ou toute autre liste.

En général, chacune des lignes de valeur dispose d’une information du type “catégorie”, “lieu”, “date”, etc. Et on se pose quelques questions sur cette masse d’information, par exemple :

  1. Quelle est la somme des factures par pays ?
  2. Combien d’articles ont été vendus par mois ?

Sans le tableau croisé dynamique d’Excel, vous êtes condamnés à faire de nombreuses manipulations !

Et vous devrez faire ceci pour chacun des résultats recherchés, et, selon votre activité et vos besoins, recommencer tous les mois, ou pire, toutes les semaines ou même tous les jours !

Alors qu’un TCD vous donnera l’information en quelques secondes, le temps de quelques clics. En plus, vous pourrez simplement rafraîchir le résultat pour que toutes les valeurs se mettent à jour automatiquement.

Désormais, vous êtes prêt à aller plus loin, passons à la première étape.

Étape 1 : Préparez vos données

Excel est un outil qui demande de la rigueur ! Il y a des règles à respecter afin que l’outil vous donne tout ce qu’il a dans le ventre. Je vous rassure, cette préparation n’a rien de très surprenant, et par la suite, vous transformerez ces bonnes habitudes en réflexes.

Voici la liste des actions minimales et obligatoires de préparation :

  1. Chaque colonne de votre tableau doit posséder un intitulé
  2. Chaque intitulé doit être unique
  3. Votre tableau ne contient pas de lignes ou de colonnes fusionnées
  4. Des données sont présentes à toutes les lignes (pas de saut de ligne pour aérer ou “faire joli”)

La liste des actions supplémentaires suivantes n’est pas obligatoire pour débuter avec la fonction tableau croisé dynamique d’Excel.

Toutefois, pour exploiter les TCD au mieux, je vous les recommande :

  1. Pour les valeurs vides, laissez le champ réellement vide et n’écrivez pas N/A par exemple
  2. Respectez un formatage homogène des dates et reconnu par Excel en tant que date
  3. Donner des noms de colonnes qui sont parlants pour vous sur la nature des contenus
  4. Soignez la saisie des valeurs textuelles en restant homogène (“Paris”, “paris”, “PARIS” ne sont pas homogènes)

Étape 2 : Vous êtes prêt à créer votre Tableau Croisé Dynamique

Je vous propose d’utiliser les données publiques de la liste des localisations des musées de France. Nous chercherons à trouver le nombre de musées par région et par département.

Une fois le fichier téléchargé, enregistré sur votre ordinateur et ouvert dans Excel, vous pouvez poursuivre.

Dans l’onglet Insertion, cliquez sur “Tableau croisé dynamique” :

Menu Insertion - Tableau croisé dynamique

La fenêtre suivante apparaît :

Créer un tableau croisé dynamique

Si vous curseur est positionné sur une des valeurs de votre tableau, Excel le détecte et présélectionne l’ensemble des colonnes et lignes de votre tableau. C’est à ce moment que l’absence de lignes vides est essentielle. Car Excel peut identifier cette ligne vide comme une indication de fin du tableau.

Pour vos premiers pas, je vous invite à vérifier que l’ensemble du tableau est correctement présélectionné en déplaçant les ascenseurs verticaux et horizontaux avec la fenêtre de création du TCD toujours ouverte.

Si tout est correctement sélectionné, vous pouvez valider en cliquant sur OK.

Et si vous avez bien suivi le pas-à-pas, vous vous serez rendu compte de la présence d’une ligne blanche dans le fichier des musées !

Menu Insertion - TCD - Plage de sélection

Ce qui aurait totalement induit en erreur les résultats d’analyse ! Ce qui m’a également interpelé, c’est le nombre de lignes de la sélection par défaut ($A$1:$O624) qui indique une sélection de la ligne 1 à la ligne 624, hors le fichier dispose de 1254 lignes ! Il faut donc supprimer la ligne vide et recommencer.

Cette fois-ci, la sélection est correcte :

Menu Insertion - TCD - Plage de sélection ajustée

Et voilà ! C’est fait ! “Mais, il n’y a rien !” me direz-vous. Eh oui, c’est maintenant que tout commence.

Tableau croisé dynamique - vide

Étape 3 : Petite explication de ce que vous avez devant vous

Vous avez devant vous un tableau qui est prêt à consolider les données de votre tableau et à répondre à toutes vos questions.

Sur chacun des emplacements du tableau qui est devant vous, vous aller pouvoir placer les colonnes du tableau, et selon le lieu de dépôt, les conséquences seront différentes.

Déterminer les LIGNES

Commençons par “LIGNES”, en bas à gauche. Lors du dépôt d’une des colonnes, le TCD va analyser le contenu du tableau initial de valeur et présenter une liste de valeurs textuelles. Celle-ci contiendra très exactement toutes les valeurs du tableau initial de manière unique.

TCD - lignes

L’objectif est de faire des regroupements. Ainsi, pour le champ “NEW REGIONS”, le nom de la région ne sera indiqué qu’une seule fois, même si celui-ci est répété sur 100 lignes différentes dans la source de données.

Déterminer les VALEURS

Continuons en bas à droite : “VALEURS”. Lors du dépôt d’une colonne à cet emplacement, le TCD va chercher à présenter un résultat numérique, et ce, qu’elle que soit la nature des données. Même avec le champ “NEW REGIONS”, un TCD cherchera un nombre.

TCD - valeurs

Pour les champs contenant des nombres, par défaut, il calculera une somme. Pour tous les autres types de champ (texte, date, …), il calculera le nombre de valeurs total.

Ainsi, si nous combinons ces deux premiers axes, nous pourrons disposer du nombre de musées par région. Il faut ici “glisser-déposer” le champ “NOM DU MUSEE” dans le bloc “VALEURS”.

TCD - valeurs complétées

Déterminer les COLONNES

Ensuite, remontons en haut à droite sur “COLONNES”. La finalité est identique à “LIGNES”, mais à partir de colonnes. Soigneusement combinées à “LIGNES”, vous pourrez découper vos résultats selon un autre axe d’analyse (à condition que l’attribut sélectionné soit pertinent). Dans l’exemple présent, il n’y aurait aucun intérêt à ventiler le résultat par département. En effet, chaque département n’appartient qu’à une seule région, et donc aucune consolidation de chiffres possibles.

Si nous avions eu accès au tarif d’entrée des musées, alors, on aurait pu mettre le prix en colonne. Ainsi, nous aurions vu le nombre de musée à un prix donné, et ce, région par région.

Déterminer les FILTRES

Enfin, en haut à gauche, nous avons “FILTRES”. Le dépôt d’une colonne ici permettra, comme le nom le sous-entend, de filtrer selon les valeurs à analyser du tableau initial.

Ainsi, si on ne souhaite pas voir certaines catégories de produit polluer les résultats, nous n’avons pas besoin de les supprimer du tableau initial.

Étape 4 : Construisez le Tableau Croisé Dynamique qui vous correspond

Avant d’aller plus loin, vous devez être au clair avec la question à laquelle vous souhaitez répondre. Vous recherchez le nombre de musées “par région et par département” (1) ou bien, “par département et par région” (2) ?

Si vous ne voyez pas la différence entre ces deux questions, voici une illustration :

TCD - musées par région puis département
Tableau croisé dynamique - musées par département puis région

Vous pouvez voir que c’est légèrement différent. Le total général est identique, c’est normal, ce sont les mêmes données. En revanche, les valeurs n’ont pas été regroupées de la même manière.

Bravo ! Vous avez parfaitement franchi toutes les étapes qui vous mène à la maîtrise des TCD. Dans de futurs articles, je développerai bien d’autres possibilités offertes par les TCD.

Étape 5 : Aller plus loin avec les GCD : Les graphiques croisés dynamiques

Maintenant que vous maîtrisez les TCD, vous pouvez aller explorer encore plus de possibilités avec les GCD ! Les graphiques croisés dynamiques. Ces derniers sont les graphiques liés aux TCD. Pour tout savoir sur les graphiques croisés dynamiques, suivez le guide !

Étape 6 : Avez-vous apprécié cet article ?

Votre avis m’aide à continuer ! Je vous remercie de commenter cet article si cela a été le cas, d’ailleurs, si cela ne l’a pas été aussi. Sinon, je ne le saurai jamais… !