Catégorie : Excel

Apprendre Excel : Des premiers pas aux premiers résultats

Apprendre Excel : Des premiers pas aux premiers résultats

Comment apprendre Excel ? Vaste programme ! Par où débuter son apprentissage d’Excel ? Apprendre Excel veut dire bien des choses différentes pour chacun d’entre nous : est-ce apprendre toutes les fonctions d’Excel ? Est-ce concevoir un classeur complet ? Est-ce savoir utiliser et faire évoluer des fichiers Excel existants ? Bref, apprendre Excel est à la fois un objectif et tout un parcours (Personnellement, j’en apprends encore après tant d’années d’utilisation !)

Pour bien apprendre Excel, commençons par comprendre les fondamentaux

Excel est un tableur. Mais qu’est-ce qu’un tableur ? Prenons un extrait de la définition sur Wikipédia :

Une feuille de calcul est une table (ou grille) d’informations de toutes natures (données industrielles, scientifiques, commerciales, comptables, financières, statistiques, données de métiers, etc.). D’une manière générale, toute problématique, pouvant être disposée en lignes et colonnes, éventuellement sur plusieurs plans, impliquant ou n’impliquant pas des calculs, rend le tableur pertinent.

Wikipedia

Une partie de la deuxième phrase est essentielle “toute problématique pouvant être disposée en ligne et colonnes”.

Pour apprendre Excel, il faut penser le monde en lignes et en colonnes. Vous devez apprendre à quadriller, classer, structurer dans des tableaux vos informations / données.

Un petit avertissement à ce stade : si vous pouvez penser votre problème en lignes et en colonnes, Excel est un candidat, mais pas nécessairement LA solution. Access est un autre candidat… D’autres critères sont à explorer pour bien sélectionner le bon outil de travail.

Vous pouvez ainsi consulter les 4,5 apprentissages clés d’un cours Excel et en particulier le premier !

Apprendre Excel c’est apprendre à automatiser

Automatiser, oui, c’est le mot approprié. On ne parle pas encore à ce stade de programmation, mais il s’agit de faire travailler Excel à notre place pour des tâches répétitives.

La première des tâches répétitives est la réalisation d’opérations mathématiques simples pour plusieurs lignes ou plusieurs colonnes.

Afin d’automatiser, il faut pouvoir donner des instructions du type « Prends la valeur de la cellule en 2e ligne et 2e colonne, ajoute la valeur de la cellule en 3e ligne et 2e colonne et donne-moi le résultat ».

Vous comprendrez que c’est un peu long à écrire…

Pour simplifier cette instruction, chaque cellule possède une adresse. On parle également de référence d’une cellule.

Cette référence de cellule s’écrit avec des lettres et un nombre.

Dans Excel 365, les cellules vont de A1 à XFD1048576.

Apprendre Excel : les références

A quoi servent les lettres et les nombres d’une référence ?

La lettre détermine le rang de la colonne.

« A » = 1ère lettre de l’alphabet = 1ère colonne

« Z » = 26e lettre de l’alphabet = 26e colonne

Et après le « Z », on recommence l’alphabet, mais avec deux lettres : « AA », « AB », « AC », …, et ainsi de suite.

La colonne ZZ se trouve donc à la 26*26 + 26 = 702e colonne. (Nous avons 26 fois l’alphabet avec deux lettres + 1 fois l’alphabet avec une seule lettre)

Finalement, on ne cherchera pas vraiment à savoir à quel rang se trouvent les colonnes. Ce qui compte, c’est d’avoir compris le principe.

La colonne XFD se situe à la 16’384e colonne (je vous passe le calcul). Il s’agit d’une puissance de 2.

16’384 = 214

Eh oui, les informaticiens aiment bien les puissances de 2 !

Les nombres déterminent le rang de la ligne

Là, c’est bien plus simple à comprendre.

A12 sera la cellule située à la 12e ligne de la colonne A. B28, sera la cellule située à la 28e ligne de la colonne B.

Depuis quelques versions d’Excel, le nombre de lignes disponibles est de 1’048’576. Devinez quoi ? Encore une puissance de 2 !

1’048’576 = 220

Pour Exceller, vous devez savoir ceci : Excel peut utiliser une autre manière d’écrire les références !

Je dois vous avertir, le monde entier a décidé que les références s’utilisaient comme je vous l’ai décrit ci-dessus. Toutefois, une simple case à cocher dans les options d’Excel et vous entrez dans un autre monde. Celles des références ultra lisibles !

Au lieu d’écrire ZZ28, vous pourriez écrire L28C702.

En quoi est-ce plus simple ? Cette référence indique « L » (comme Lignes) suivi du numéro de la ligne puis « C » (comme Colonnes) suivi du numéro de la colonne.

C’est immédiatement plus clair.

Si vous souhaitez activer (ou désactiver) cette option, procédez comme suit :

Changer l'affichage des références - Étape 1 - Menu fichier - Option
Menu fichier – Options

Ensuite, cliquez sur le menu « Formules » et activez (ou désactivez) la case « Style de référence L1C1 ».

Activer le style de référence L1C1 dans le menu Formules
Menu Formules – Style de référence L1C1

Votre feuille de calcul prendre alors la forme suivante :

Affichage d'une feuille Excel avec le style L1C1
Feuille de calcul – Style L1C1

Rassurez-vous tout de suite, toutes les formules de votre feuille de calcul sont automatiquement converties dans le bon format. C’est un simple remplacement d’une lettre par son rang.

En revanche, pour les plus habitués d’entre vous, cela peut sembler difficile de s’y retrouver.

Pour la suite, je n’utiliserai que la manière la plus répandue d’utiliser les références, à savoir avec les lettres et les nombres.

Apprendre à automatiser les opérations mathématiques simples avec Excel

Maintenant que vous avez bien compris la notion de référence d’une cellule, le véritable usage d’Excel va pouvoir commencer.

Nous disposons de cellules dans lesquelles nous allons inscrire des valeurs, ou des opérations.

Lorsque, dans une cellule, vous souhaitez faire un calcul utilisant la valeur de la cellule A1, vous allez indiquer à Excel, non pas la valeur elle-même, mais le fait qu’Excel doive récupérer la valeur contenue dans A1.

Ainsi, dès que vous modifierez la valeur de la cellule A1, toutes les cellules qui font référence à A1 se mettront automatiquement à jour.

Cependant, Excel ne peut pas savoir si vous écrivez A1 en référence à la cellule A1 ou juste parce que vous souhaitez écrire le texte « A1″…

C’est pourquoi il faut l’indiquer ! Ceci se fait en commençant par écrire le simple symbole « égal » : ‘=’.

Pour dire à Excel que vous souhaitez référencer le contenu de la cellule A1, il faut commencer par écrire le symbole égal ‘=’

Exemple 1 : L’addition et la multiplication

Prenons un exemple avec une facture extrêmement simplifiée.

Vous disposez d’un petit tableau avec les colonnes suivantes :

  • L’intitulé d’un article
  • Le prix unitaire de l’article
  • La quantité
  • Le prix total par article acheté
  • Le prix total de la facture
Exemple opération simple pour apprendre Excel

L’objectif à atteindre est de pouvoir modifier les valeurs du prix unitaire ou de la quantité et d’avoir automatiquement les résultats qui se mettent à jour.

Au lieu de raisonner avec les valeurs (1×299, 6×49, puis la somme des valeurs obtenues, il faut raisonner avec les références des cellules.

Référence celluleRésultat attenduFormule correspondante
D2La multiplication de B2 par C2=B2*C2
D3La multiplication de B3 par C3=B3*C3
D5La somme de D2 et de D3=D2+D3

Vous pouvez également observer que pendant que vous écrivez les formules, le texte de votre formule change de couleur. Les couleurs correspondent aux cellules auxquelles elles font références.

Apprendre Excel - Illustration d'une formule Excel avec la coloration des références d'une formule

Si vous avez bien retranscrit mon exemple, vous devriez avoir le résultat ci-dessous. J’aimerais d’ailleurs en profiter pour attirer votre attention sur 3 éléments :

  1. Premièrement, c’est la position actuelle de mon curseur. Il se trouve en D5, sur le total de la facture. C’est la zone de nom.
  2. Deuxièmement, on peut voir le contenu de cette cellule. Et c’est une formule que l’on voit, et non le résultat ! C’est la barre de formule.
  3. Troisièmement, sur la feuille de calcul, on voit le résultat.

A partir de maintenant, si vous modifiez le prix ou la quantité, vous verrez les résultats se mettre à jour automatiquement.

Exemple 2 : Utiliser votre première fonction – SOMME()

Dans mon exemple, c’était facile, il n’y avait que 2 lignes à ajouter pour obtenir le total de la facture. Si vous en aviez 30, ce serait un peu plus pénible de saisir =D2+D3+D4+…+D31.

C’est pour cela qu’Excel possède de nombreuses fonctions prédéfinies.

Je vous propose de découvrir la fonction SOMME().

Avant d’aller plus loin, je dois maintenant vous présenter la notion de PLAGE (sans le soleil et les serviettes… 🙂 ).

Apprendre Excel : Les PLAGES

Une PLAGE correspond à un ensemble de cellules. Ce sera une manière simplifiée d’indiquer à Excel que nous avons besoin de l’ensemble des cellules contenues dans un rectangle en particulier.

Regardez la sélection ci-dessous :

Apprendre Excel : Les plages

J’ai volontairement vidé tout le contenu des cellules pour mettre l’accent sur la zone sélectionnée. Et c’est également volontairement un rectangle composée de plusieurs colonnes et plusieurs lignes.

Nous aurons besoin des cellules situées en haut à gauche (ici B2) et en bas à droite (ici D5). Ce sont elles qui détermineront notre plage.

L’écriture à utiliser sera : B2:D5.

Excel comprendra qu’il faut prendre en compte toutes les cellules contenues dans ce rectangle.

Ce rectangle peut posséder uniquement une colonne : B2:B5 (en rouge). Ou encore, ne posséder qu’une seule ligne B2:D2 (en bleue).

Utiliser la fonction SOMME()

Vous l’avez deviné, nous allons pouvoir fournir une plage à la fonction SOMME() pour terminer notre exemple.

Avez-vous trouvé comment décrire cette plage ?

Les articles vont de la ligne 2 à la ligne 31, et les cellules à additionner se trouvent dans la colonne D. La plage se décrira ainsi : D2:D31.

Excel pourra alors interpréter cette plage et exécuter la fonction SOMME() sur celle-ci. Comme son nom l’indique, la fonction SOMME additionnera toutes les cellules et vous présentera le résultat.

Petite astuce pour avoir tout lu !

Pour vous remercier d’avoir tout lu, je vous présente une fonction que même les assez bons ne connaissent pas !

Vous avez pu voir sur cette capture qu’à moins d’être sur la cellule, rien n’indique que c’est le résultat d’une opération avec d’autres cellules.

Une fonctionnalité d’Excel permet de montrer immédiatement toutes les formules présentes dans toutes les cellules du classeur.

Ainsi, au lieu de voir le résultat du calcul, vous verrez les formules !

  1. Menu Formules
  2. Afficher les formules (à désactiver ensuite)

Et à ce moment, toutes les formules se révèlent ! En en cliquant sur les cellules (comme en 3), vous verrez immédiatement les cellules concernées par la formule.

N’oubliez pas de cliquer à nouveau sur cette option pour que le résultat revienne.

Pour une raison encore inconnue, les largeurs des cellules augmentent soudainement. Pas d’inquiétude, lorsque l’affichage des formules est désactivé, les largeurs reviennent à leur état originel.

Avez-vous appris à mieux utiliser Excel ?

Faites-le moi savoir dans les commentaires, partagez cet article sur vos réseaux, mettez-vous en contact avec moi sur LinkedIn ou Facebook.

Merci !

Calendrier Excel 2020

Calendrier Excel 2020

Je vous ai préparé LE calendrier Excel pour 2020. Mais pas seulement ! C’est un calendrier Excel pour 2020, 2021 et toutes les années que vous souhaitez ! En plus, j’ai décidé de me démener pour y ajouter les jours fériés automatiquement ! Après la publication de la première version, j’ai mis à jour le fichier pour également disposer des numéros de semaines, selon une simple liste de choix !

J’ai encore amélioré ce calendrier. Il s’appelle version 2021 est se trouve ici.

Et comme je pense au plus grand nombre d’entre vous, mon calendrier Excel contient les jours fériés pour la Belgique, le Canada, la France et deux cantons Suisse : Genève et Vaud.

Calendrier Excel 2020 - Champs configurables
Tous les jours fériés pour toujours et pour les pays suivants : Belgique, Canada, France, Suisse (GE+VD)

Au départ, je ne voulais qu’un calendrier Excel pour 2020…

Et puis, mes souvenirs m’ont rappelé que j’ai toujours souhaité trouver les formules pour calculer automatiquement les dates des jours fériés. J’ai alors commencé ma quête des formules pour calculer les dates automatiquement.

J’ai dû me demander comment chacune des dates était déterminée. Évidemment, les dates avec un numéro fixe sont faciles à rendre variables selon l’année (1er janvier, 1er mai, etc.). Finalement, il n’y a que la date de Pâques qui est particulière à calculer.

J’ai ensuite pensé à VOUS. Le trafic de ce site montre une provenance de nombreux autres pays francophones, en particulier du Canada, de la Belgique et de la Suisse. Je me suis alors mis en quête des formules pour chacun de ces pays.

Certaines dates sont plus difficiles à calculer. Par exemple, en Suisse, dans le canton de Genève, le jeûne genevois se fête le premier jeudi suivant le premier dimanche de septembre. Toujours en Suisse, dans le canton de Vaud, le lundi du jeûne fédéral est fixé au lundi suivant le 3e dimanche de septembre. Enfin, au Canada, la fête du travail est toujours le premier lundi du mois de septembre et l’action de grâce (Thanksgiving) est fixé au 2e lundi du mois d’octobre.

Un calendrier Excel 2020 correctement mis en forme

Il ne suffisait pas de calculer les dates des jours fériés, je souhaitais en plus disposer d’une mise en page qui ressemble à un calendrier. Mais pour cela, il faut pouvoir mettre en forme les bons jours fériés selon le pays. Et cela a passablement compliqué la chose !

Il fallait trouver une formule de mise en forme conditionnelle qui est elle-même conditionnée au choix du pays !

Après quelques tâtonnements, j’ai combiné à différents endroits les fonctions INDIRECT(), ADRESSE(), RECHERCHEV() et une formule matricielle !

À cela s’ajoute la gestion de la mise en forme conditionnelle des samedis, des dimanches et bien sûr des jours fériés ! Sans oublier la gestion du 29 février ! Étant légèrement perfectionniste, je souhaitais de plus que le quadrillage soit correctement mis en forme. Ce qui est le cas.

Un calendrier Excel 2020 imprimable…

Il ne s’agissait pas seulement de faire un joli tableau, il fallait bien le rendre imprimable !

Heureusement qu’avec mon article pour maîtriser la zone d’impression d’Excel, j’ai tout ce qu’il faut pour préparer le fichier à l’impression !

Et j’ai bien prévu de n’avoir que la zone du calendrier qui est imprimée.

Avec ou sans les numéros de semaine, votre calendrier Excel personnalisable

Excel demande souvent pas mal de créativité ! Ajouter les numéros de semaine au calendrier n’est pas très compliqué en soi. La fonction NO.SEMAINE.INT() vous donne tout de suite cette information. En revanche, dans la mesure où dans mon cas tout est dynamique, il fallait rendre conditionnel l’affichage du numéro de semaine.

Et puis, j’utilisais initialement la présence ou non d’un texte (le nom du jour férié) dans la cellule des jours pour adapter la mise en forme. L’ajout d’un numéro de semaine a donc légèrement compliqué la donne ! (voici un article pour découvrir une partie du potentiel des mises en forme conditionnelles d’Excel)

Finalement, c’est fait. Il ne vous reste plus qu’à récupérer la version du calendrier Excel actualisée (le lien se trouve en bas de la page) et me dire ce que vous en pensez en commentaire.

Liste des jours fériés pour 2020 (Belgique, Canada, France et Suisse – GE et VD)

J’ai trouvé les dates suivantes pour la Belgique, le Canada, la France et les deux cantons Suisse de Genève et de Vaud.

PaysDateJour férié
Belgiquemercredi, 1 janvier 2020Jour de l’an
dimanche, 12 avril 2020Pâques
lundi, 13 avril 2020Lundi de Pâques
vendredi, 1 mai 2020Fête du travail
vendredi, 8 mai 2020Armistice 39/45
jeudi, 21 mai 2020Ascension
dimanche, 31 mai 2020Pentecôte
lundi, 1 juin 2020Lundi de Pentecôte
mardi, 21 juillet 2020Fête Nationale
samedi, 15 août 2020Assomption
dimanche, 1 novembre 2020Toussaint
mercredi, 11 novembre 2020Armistice 14/18
dimanche, 15 novembre 2020Fête du Roi
vendredi, 25 décembre 2020Noël
Canadamercredi, 1 janvier 2020Jour de l’an
vendredi, 10 avril 2020Vendredi saint
lundi, 13 avril 2020Lundi de Pâques
lundi, 18 mai 2020Journée nationale des patriotes
mercredi, 1 juillet 2020Fête du Canada
lundi, 7 septembre 2020Fête du travail
lundi, 12 octobre 2020Action de grâce
vendredi, 25 décembre 2020Fête de Noël
Francemercredi, 1 janvier 2020Jour de l’an
dimanche, 12 avril 2020Pâques
lundi, 13 avril 2020Lundi de Pâques
vendredi, 1 mai 2020Fête du travail
vendredi, 8 mai 2020Armistice 39/45
jeudi, 21 mai 2020Ascension
dimanche, 31 mai 2020Pentecôte
lundi, 1 juin 2020Lundi de Pentecôte
mardi, 14 juillet 2020Fête Nationale
samedi, 15 août 2020Assomption
dimanche, 1 novembre 2020Toussaint
mercredi, 11 novembre 2020Armistice 14/18
vendredi, 25 décembre 2020Noël
Suisse GEmercredi, 1 janvier 2020Nouvel an
vendredi, 10 avril 2020Vendredi Saint
dimanche, 12 avril 2020Pâques
lundi, 13 avril 2020Lundi de Pâques
jeudi, 21 mai 2020Ascension
lundi, 1 juin 2020Pentecôte
samedi, 1 août 2020Fête Nationale
jeudi, 10 septembre 2020Jeûne genevois
vendredi, 25 décembre 2020Noël
jeudi, 31 décembre 2020Restauration de la république
Suisse VDmercredi, 1 janvier 2020Nouvel An
jeudi, 2 janvier 2020Nouvel An (et oui, 2 jours !)
vendredi, 10 avril 2020Vendredi Saint
dimanche, 12 avril 2020Pâques
lundi, 13 avril 2020Lundi de Pâques
jeudi, 21 mai 2020Jeudi de l’Ascension
lundi, 1 juin 2020Lundi de Pentecôte
samedi, 1 août 2020Fête Nationale
dimanche, 20 septembre 2020Jeûne fédéral
lundi, 21 septembre 2020Lundi du Jeûne
vendredi, 25 décembre 2020Noël

Le fichier Excel en téléchargement ci-dessous

Une fois le paragraphe débloqué, le lien de téléchargement sera dans le texte ci-dessous. N’oubliez pas de cliquer dessus pour récupérer le fichier.

​Entrez votre email pour ​débloquer l'accès au fichier Excel contenant le ​​calendrier

​​vous recevez en plus mon e-book gratuit

Les 15 fonctionnalités que SEULS les vrais experts de Microsoft Office connaissent !

Si vous n'avez pas reçu l'email d'ici quelques minutes, pensez à vérifier votre dossier "spam" ou "courrier indésirable", les filtres anti-spams sont parfois un peu trop sensibles !

Utilisateurs de GMail : pensez à vérifier votre dossier "Promotion" pour les mêmes raisons. 

Comme promis, voici le lien vers le fichier Excel contenant le calendrier 2020 et toutes les années que vous souhaitez, ainsi que les jours fériés.

Et si vous souhaitez que j’ajoute d’autres pays, que ce fichier vous a plu, dites-moi tout dans les commentaires !

Utiliser un fichier CSV avec Excel

Utiliser un fichier CSV avec Excel

Le fichier CSV et Excel sont des amis mais parfois, ils ont du mal à s’entendre. Je vous propose dans cet article de rétablir la meilleure entente entre les deux. Commençons par la définition : un fichier CSV signifie « Comma Separated Values », des valeurs séparées par des virgules. Or, parfois, le fichier CSV contient des valeurs séparées par … des points-virgules !

Bref histoire du format de fichier CSV

Tout d’abord, c’est un format qui est proposé comme format standard pour Internet par l’IETF (Internet Engineering Task Force) à travers la RFC 4180 puis mise à jour avec la RFC 7111.

Donc, si c’est un standard, tout le monde devrait pouvoir lire et écrire de la même façon, non ? Malheureusement, entre un standard déclaré et son application, il y a souvent des écarts importants… Et vous pouvez le constater à chaque fois que vous ouvrez un fichier CSV avec Excel, c’est toujours une surprise. Enfin, avant d’avoir lu cet article… car après, vous maîtriserez parfaitement la question !

Prérequis pour lire et écrire un fichier CSV

Les fichiers CSV sont des fichiers texte. Premièrement, ce sont des fichiers texte que n’importe quel éditeur de fichiers peut lire et écrire. Ils n’ont pas besoin d’un outil propriétaire (payant) pour être lus. Deuxièmement, l’idée de ces fichiers est de pouvoir échanger des données entre deux organisations (cela peut être deux personnes, deux programmes, un mélange des deux).

L’intérêt d’un standard, c’est que chacun sait à quoi s’attendre et donc comment lire un fichier reçu. Or, plusieurs problèmes viennent troubler l’ordre a été établi. Nous allons les passer en revue.

Le format des nombres

Dans le monde anglo-saxon, le séparateur de décimales est le point, alors qu’en Europe (sauf pour les britanniques), c’est la virgule. Cela va donc commencer à poser un problème pour un fichier CSV qui veut dire « Comma Separated Values »… vos valeurs à virgule risquent de se faire découper en deux !

Il serait donc bienvenu de disposer d’un autre séparateur que la virgule…

De plus, la virgule est utilisée par les anglo-saxons comme un séparateur de milliers. Cela ne facilite pas la lecture des valeurs.

Le format des dates

Pour continuer à simplifier la situation (!), la manière dont les dates sont écrites dans un fichier risque également de subir une interprétation. La même différence entre le monde anglo-saxon et les autres existe pour le format des dates.

Ainsi, une date inscrite 8/12/11 correspond-elle au 8 décembre 2011 ou au 12 août 2011 ? Rien n’empêche d’imaginer le 11 décembre 2008… Il est donc vital de comprendre comment les dates sont inscrites dans un fichier pour pouvoir les interpréter de la bonne manière !

Le format du texte

Pour des raisons techniques, les données de type texte apportent leur lot de complexité également. En dehors de l’encodage dont je vous parle plus loin, il faut traiter le problème du caractère apostrophe ‘ et du guillemet « .

Le premier (apostrophe) pose des problèmes à nos amis les développeurs. En effet, les apostrophes sont utilisées dans les appels aux bases de données et génèrent donc des difficultés. Le moyen de contourner ce problème consiste à placer des guillemets  » autour du texte contenant une apostrophe ‘.

Là aussi, pour des raisons que je ne détaillerai pas, c’est parfois l’inverse qui est utilisé ! Des guillemets à la place d’une apostrophe et vice-versa.

Les séparateurs de valeurs

Normalement, le nom du fichier indique clairement que les valeurs de notre fichier CSV devraient être séparées par des virgules. Faisons un petit récapitulatif de la situation :

  1. Les nombres décimaux utilisent parfois des virgules, parfois des points
  2. Les dates ont des ordres variables d’affichage du jour, du mois et de l’année
  3. Les textes sont parfois encerclés de guillemets, parfois d’apostrophes
  4. Les textes voient parfois leurs apostrophes remplacées par des guillemets

Ces raisons font que différentes méthodes existent pour séparer les valeurs dans un fichier. Il s’agit parfois de point-virgule, parfois le caractère tabulation, parfois ce sera un autre caractère (#, |, …)… l’objectif étant d’éviter qu’il y ait des collusions entre ce caractère et les données.

Et pour terminer, je vous laisse le meilleur pour le prochain paragraphe : l’encodage du fichier !

L’encodage du fichier

Comme la situation était déjà simple, le monde des technologies a ajouté un autre problème… l’encodage.

Je n’aime pas devoir rentrer dans les considérations si techniques. Mais, dans le cas présent, c’est essentiel pour pouvoir comprendre pourquoi, dans certains cas, un import de fichier CSV dans Excel ne fonctionne pas…

Lorsque vous écrivez un fichier texte sur votre ordinateur, le programme d’édition utilise un certain ensemble de caractères (un charset – contraction de « characters » et « set »). Ainsi, lorsque j’utilise le 8e caractère de l’ensemble, cela correspond à une lettre, un chiffre ou un caractère spécial en particulier. Il se trouve que la technologie s’est d’abord développée dans le monde anglo-saxon. Ces derniers n’ont pas prévu dès le départ que le système développé allait autant s’étendre. Ils n’ont donc pas prévu d’intégrer tous les alphabets ou idéogrammes du monde.

L’internationalisation des échanges a vu naître de plus en plus de charsets différents ! Et biensûr, ils ne sont pas compatibles entre eux ! Vous devez donc savoir avec qui vous traitez…

Vos paramètres régionaux

Eh oui, les paramètres système de votre ordinateur vont avoir un impact sur la manière dont un fichier CSV sera lu par Excel. Il est fréquent de voir ce problème lors d’échange de fichiers entre collègues de la même organisation mais ne travaillant pas dans la même langue ou la même région du monde (et ayant donc des configurations d’ordinateur différentes).

Ces paramètres se trouvent dans le panneau de configuration (ancienne version d’affichage – même sur Windows 10 !). Vous trouverez les paramètres qui sont les vôtres pour votre ordinateur.

Vous pouvez également aller explorer les autres onglets (en 4) comme présenté ci-dessous.

Fichier CSV Excel - Paramètres régionaux
Fichier CSV Excel – Paramètres régionaux

Importer un fichier CSV avec Excel

Même si l’explorateur de fichiers associe les fichiers .csv avec Excel, je ne vous recommande pas d’ouvrir directement un fichier csv (en double-cliquant dessus). La probabilité que le fichier ne s’ouvre pas convenablement est très (trop) élevée. Au final, vous risquez plus de frustrations que de gagner réellement du temps.

Mon meilleur ami pour ouvrir les fichiers CSV est Notepad++. C’est un éditeur de texte très puissant et totalement libre d’utilisation. Il permet d’ouvrir les fichiers texte volumineux sans planter ! Et surtout, il sait :

  1. Reconnaître automatiquement l’encodage des fichiers
  2. Convertir un encodage très simplement
  3. Remplacer des caractères par un autre à toute vitesse

Méthode n°1 : Préparer votre fichier avec Notepad++ avant d’ouvrir votre fichier CSV dans Excel

Dans mon tuto sur comment réussir son premier tableau croisé dynamique, j’utilise un exemple de données provenant du site du gouvernement français : https://www.data.gouv.fr/fr/datasets/r/cc16163c-aca0-4977-97da-8ce592f78de1.

Ce fichier CSV n’a pas un encodage qu’Excel comprend immédiatement (vous pouvez tester, vous verrez !).

Alors que lorsque vous l’ouvrez avec Notepad++, cela fonctionnera parfaitement. Vous pourrez alors aller dans le menu « Encodage » (en 1) et visualiser l’encodage actuel (en 2).

Ensuite, vous pourrez convertir ce fichier dans un encodage qui sera immédiatement reconnu par Excel, à savoir ANSI. Cet encodage est celui par défaut pour Windows.

Fichier CSV Excel - Changer encodage Notepad++
Fichier CSV Excel – Changer encodage Notepad++

Vous pourrez ensuite travailler depuis Excel pour terminer la conversion. Mais au moins, à ce stade, les caractères spéciaux du type é ne seront plus présents !

Avant de quitter Notepad++, vous devez:

  1. Vérifier que l’encodage du fichier a bien changé (cf. image ci-dessous)
  2. Enregistrer votre fichier avant de fermer ! (si la disquette en haut à gauche est rouge, elle montre que l’enregistrement n’a pas été fait)
Fichier CSV Excel - ANSI
Fichier CSV Excel – Encodé ANSI

Méthode n°2 : Utiliser l’import de données externes d’un fichier CSV dans Excel

Étape n°1 : Importer le fichier CSV

Il y a un avantage à utiliser cette technique : Excel peut cette fois détecter l’encodage correctement !

Fichier CSV Excel - Importer fichier texte
Fichier CSV Excel – Importer fichier texte

Vous pouvez ensuite sélectionner votre fichier CSV à importer. L’écran suivant apparaît alors.

  1. Type de données d’origine
    • Délimité : Vous disposez de caractères de séparation, par défaut Excel reconnaît les virgules.
    • Largeur fixe : Parfois, les fichiers CSV sont construits avec le même nombre de caractères à chaque ligne. Les lignes qui ont plus ou moins de caractères sont comblées avec des espaces.
  2. Origine du fichier.
    • C’est l’encodage. Ici, comme avec Notepad++, Excel reconnaît que le contenu est en UTF-8 (et non ANSI). Les caractères spéciaux n’apparaissent pas.
    • Toutefois, il est possible qu’Excel se trompe. Vous devez alors choisir par vous-même le bon encodage dans la liste déroulante.
  3. Commencer l’importation à la ligne xx.
    • Il est possible de ne pas importer la ligne d’en-tête par exemple. Ou parfois, les fichiers CSV ont des lignes de description qui ne sont pas utiles à l’importation.
  4. Mes données ont des en-têtes.
    • Cette case permet d’indiquer à Excel si la première ligne représente les noms des colonnes.
  5. Vous avez une prévisualisation de vos données.
    • Cela permet de vérifier si les paramètres sont adaptés.
Assistant importation données CSV - Étape 1
Assistant importation données CSV – Étape 1

Vous pouvez alors cliquer sur le bouton « Suivant > » et continuer la configuration de votre import.

Étape n°2 : Configurer les séparateurs de votre fichier CSV

Assistant importation données CSV - Étape 2
Assistant importation données CSV – Étape 2

À ce stade, par défaut, la seule case cochée est « Tabulations ». Vous aurez certainement besoin de cocher soit « Virgule » soit « Point-virgule ». Les autres options sont explicites. Dès que vous aurez choisi le bon séparateur, l’aperçu se met à jour. Vous pourrez évaluer la pertinence de votre choix.

Vous pouvez alors passer à l’étape 3.

Étape n°3 : Configurer le choix et le format de vos colonnes

Assistant importation données CSV - Étape 3
Assistant importation données CSV – Étape 3

À ce moment, 3 parties très, très, très importantes se présentent à vous (j’ai déjà dit que c’était important ?)

  1. Vous pouvez déterminer le type de chacune des colonnes importées à ce moment précis.
    • Vous pouvez notamment adapter le format des dates. JMA signifie Jour Mois Année. Dans le menu déroulant, vous trouverez les 6 combinaisons possibles. À vous de choisir la bonne !
  2. Vous pouvez déterminer les séparateurs de décimales et de milliers (en cliquant sur « Avancé… »).
  3. Vous pouvez sélectionner chaque colonne les unes après les autres pour déterminer leur type.

N’oubliez surtout pas l’option « Colonne non distribuée ». Cette option permet de ne pas importer les données de la colonne en question. Cela peut s’avérer très utile de n’importer que les données nécessaires.

Enfin, lorsque vous avez terminé, vous pouvez cliquer sur le bouton « Terminer » et utiliser vos données dans Excel.

Considérations (avantages/inconvénients) de cette méthode

Selon les cas, un avantage pour les uns peut être un inconvénient pour lesn autres, je préfère donc lister les considérations à avoir avec cette méthode.

  • Votre fichier CSV reste intact. Vous n’importez que les données.
  • Vous pouvez mettre à jour votre fichier CSV et Excel pourra actualiser l’import des données (en cliquant sur Actualiser). C’est à dire que si le nombre de lignes varie, Excel s’adaptera.
  • Si vous déplacez le fichier CSV ou le fichier Excel, vous aurez un message d’erreur à chaque rafraîchissement des données.
  • Plusieurs personnes peuvent travailler à partir du même fichier CSV.

Méthode n°3 : Récupérer et transformer votre fichier CSV dans Excel

Pour utiliser cette méthode, PowerQuery est nécessaire. Ce composant est présent nativement à partir d’Excel 2016. Pour les Excel 2013 ou Excel 2010 Professionnal Plus (+Software Assurance), Microsoft propose de télécharger le composant depuis son site.

Étape n°1 : Débuter l’import

C’est certainement la méthode la plus complète, et par conséquent, la plus complexe également.

Importer un fichier CSV dans Excel - Récupérer et transformer
Importer un fichier CSV dans Excel – Récupérer et transformer

L’écran suivant apparaît avec les options suivantes :

  1. Origine du fichier
    • Comme nous l’avons déjà vu, c’est l’encodage.
    • Ce qui est surprenant, c’est que l’interface de PowerQuery ne reconnaît pas le bon encodage automatiquement !
    • C’est donc à vous de sélectionner le bon encodage ! La liste est longue, mais je vous recommande de tester parmi les encodages les plus fréquemment utilisés que sont :
      • 1252 – Europe de l’ouest (Windows)
      • 1200 – Unicode
      • 65001 – Unicode UTF-8
      • >>Ne me demandez pas pourquoi, les valeurs ne sont pas triées selon les valeurs numériques, mais selon le nom de l’encodage…
  2. Délimiteur
    • C’est le séparateur qui est utilisé dans votre fichier.

À ce stade, au lieu de tout de suite cliquer sur le bouton « Charger », je vous invite à découvrir un peu plus PowerQuery en cliquant sur « Modifier ».

Fichier CSV Excel - Configuration PowerQuery
Fichier CSV Excel – Configuration PowerQuery

Étape n°2 : Modifier la requête d’importation

Dès que vous avez sélectionné le bon encodage et cliqué sur « Modifier », vous vous retrouvez face à l’affichage suivant.

Fichier CSV Excel - Interface PowerQuery
Fichier CSV Excel – Interface PowerQuery

Je pourrais vous détailler à ce stade toute l’interface, cela serait un peu long. Toutefois, je peux vous informer sur les quelques possibilités que vous pourrez explorer. Quelques exemples :

  • Changer l’ordre des colonnes ou en supprimer
  • Filtrer automatiquement des lignes, autrement dit ne pas les importer
  • Calculer de nouvelles colonnes sur la base des autres colonnes
  • Changer les types des données
  • etc.

Lorsque vous aurez terminé vos paramétrages, vous n’aurez qu’à cliquer sur « Enregistrer et charger » pour que les données se chargent sur votre feuille Excel.

Considérations (avantages/inconvénients) de cette méthode

C’est sans conteste la méthode la plus complète pour importer des données CSV dans Excel. C’est également la méthode la plus complexe. Selon vos besoins, vous pourrez déterminer la solution la plus adaptée.

Comme précédemment, vous aurez les considérations suivantes :

  • Votre fichier CSV reste intact. Vous n’importez que les données.
  • Vous pouvez mettre à jour votre fichier CSV et Excel pourra actualiser l’import des données (en cliquant sur Actualiser). C’est-à-dire que si le nombre de lignes varie, Excel s’adaptera.
  • Si vous déplacez le fichier CSV ou le fichier Excel, vous aurez un message d’erreur à chaque rafraîchissement des données.
  • Plusieurs personnes peuvent travailler à partir du même fichier CSV.

Dernière étape

Avez-vous encore des questions ? Est-ce que cet article vous a plu ? J’attends votre feed-back dans les commentaires !

Merci !

[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.

>