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 ?
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.
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 :
Et pour d’autres extensions :
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 ?
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 afin de bien préparer sa mise en page avant l’impression.
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 :
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” auraient été 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” :
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 :
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”
Votre affichage devient alors :
Étape 2 : Comprendre comment interpréter ce qui est présenté
On voit les informations essentielles suivantes :
Une grande zone gris foncé (ou zone grisée)
Une information des numéros de page
Un encadré bleu ainsi que des traits en pointillés bleus
La zone grise (ou zone grisée)
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.
Cliquez sur le menu “Mise en page”
Ensuite, cliquez sur l’icône en bas à droite du groupe de boutons “Mise en page”
Rendez-vous sur l’onglet “Feuille” de la fenêtre qui vient d’apparaître
Sélectionnez l’option de votre choix dans le bloc “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.
C’est le meilleur moyen de parfaitement gérer la mise en page avant l’impression de votre feuille Excel. La configuration de votre impression peut d’ailleurs être faite feuille par feuille : une au format portrait et une autre au format paysage.
Voici ce qu’il y aura de déroutant :
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.
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 définir la zone d’impression, vous devez suivre les étapes suivantes :
Sélectionnez les cellules que vous souhaitez imprimer
Allez dans le menu “Mise en page”
Cliquez sur le bouton “ZoneImpr”
Puis sur “Définir”
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.
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
Changer l’orientation d’impression de plusieurs feuilles en même temps
Merci à Laurent (en commentaire) de l’avoir signalé, il est également possible de changer la mise en place de plusieurs feuilles avec une manipulation complémentaire.
En maintenant la touche Ctrl pressée, vous pouvez alors sélectionner plusieurs feuilles de votre classeur et modifier comme précédemment l’orientation de l’impression.
Avec une situation comme celle-ci :
Zone impression Excel – Plusieurs feuilles
Il paraît évident que l’orientation paysage sera préférable. Sauf que le fichier contient un onglet par mois pour résumer les résultats de vente…
Zone impression Excel – Plusieurs feuilles sélectionnées
Enfin, il ne reste qu’à changer l’orientation de toutes les pages en vue de l’impression. Pour cela, direction menu « Mise en page » > « Orientation » > « Paysage ».
Zone impression Excel – Plusieurs feuilles changement orientation
A la fin, pensez à simplement cliquer sur le nom d’une autre feuille que la feuille active histoire de désactiver la sélection multiple de feuilles…
Zone impression Excel – Désactiver sélection
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 !
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.
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 :
Quelle est la somme des factures par pays ?
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.
Si vous pensez que vous avez besoin de plus d’explications, j’ai rédigé un second article qui détaille encore plus les objectifs d’un tableau croisé dynamique. Je l’ai humblement nommé le tableau croisé dynamique pour les nuls.
Vous pourrez ensuite reprendre le fil de cet article.
É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 :
Chaque colonne de votre tableau doit posséder un intitulé
Chaque intitulé doit être unique
Votre tableau ne contient pas de lignes ou de colonnes fusionnées
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 :
Pour les valeurs vides, laissez le champ réellement vide et n’écrivez pas N/A par exemple
Respectez un formatage homogène des dates et reconnu par Excel en tant que date
Donner des noms de colonnes qui sont parlants pour vous sur la nature des contenus
Soignez la saisie des valeurs textuelles en restant homogène (“Paris”, “paris”, “PARIS” ne sont pas homogènes)
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” :
La fenêtre suivante apparaît :
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 !
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 :
Et voilà ! C’est fait ! “Mais, il n’y a rien !” me direz-vous. Eh oui, c’est maintenant que tout commence.
É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.
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.
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 ».
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 :
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.
Je vous propose également cette vidéo pour illustrer l’utilisation des tableaux croisés dynamiques avec l’analyse des données publiquement disponibles sur le Covid via le site https://data.gouv.fr :
https://www.youtube.com/watch?v=H0iao5mAtcs
É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… !
Comparer deux fichiers Excel est un des besoins les plus fréquents lorsque l’on doit consolider, vérifier, contrôler des données qui ont été saisies par plusieurs personnes différentes. Contrairement à Word, il n’y a pas de fonction de suivi des modifications. Il y a d’autres moyens de comparer deux fichiers Excel.
Plusieurs solutions s’offrent à vous selon votre version d’Excel :
Vous possédez une version assez récente d’Excel (>= Excel 2013) en version professionnelle. Vous avez de la chance, Microsoft vous offre un add-on inclus dans Excel pour comparer 2 fichiers Excel : Le composant Excel Inquire. Cela est valable pour les abonnements Microsoft 365 (Excel 365), il faut disposer d’un abonnement E3 au minimum.
Vous possédez une version non professionnelle, vous devrez installer un composant open source.
Préambule
Les plus curieux d’entre vous auront peut-être trouvé le petit bouton caché qui semble indiquer l’existence de cette fonction, mais pour un usage dans un contexte très particulier !
Cette fonctionnalité dispose de nombreux inconvénients qui sont décrits de manière détaillée sur le site de Microsoft. En premier lieu, c’est une fonctionnalité considérée comme ancienne (sous-entendu : bientôt obsolète). En second lieu, elle est complètement inutile si on veut comparer un fichier en notre possession et un autre transmis par email par exemple.
Choisir le moyen de comparer deux fichiers Excel selon votre version d’Office
Pour tout ceux qui possèdent une version Excel Professionnel Plus 2013 au minimum, ce sera très facile. Sinon, il faudra se rabattre sur une solution libre alternative, qui vous servira tout de même !
De toute manière, sachez qu’Excel regorge de fonctionnalités à découvrir, en particulier, il y a des compléments disponibles qui n’attendent que vous.
Excel fournit non seulement toutes les fonctionnalités que l’on peut retrouver dans les menus habituels, mais également quelques composants complémentaires désactivés par défaut.
C’est justement un de ces composants, nommé Spreadsheet Compare (Inquire), qui va nous permettre de comparer pleinement deux fichiers Excel !
Pour la suite de l’article, il faut savoir qu’il est nécessaire de disposer d’une version Excel Professionnel Plus 2013 au minimum.
Ouvrez le menu Fichier > Options, puis sélectionnez la section Compléments. Ensuite, sélectionner le choix Compléments COM, puis cliquez sur Atteindre… :
Le menu suivant s’affiche avec plus ou moins de choix selon les produits installés sur votre ordinateur. Si votre version d’Excel le permet (au minimum Professionnel Plus 2013), vous aurez l’option “Inquire” disponible. Cochez et validez avec OK.
Si à ce moment, vous ne voyez pas le composant Inquire dans la liste des composants COM, c’est qu’il est fort probable que votre licence actuelle ne le permette pas.
Dans la mesure où vous seriez avec un abonnement Microsoft 365 (ou Excel 365), il vous faut un niveau de licence E3 au minimum.
Par ailleurs, vous pouvez également consulter l’aide de Microsoft détaillant ce point.
Un nouvel onglet apparaît alors dans le ruban avec le titre Inquire.
Étape 2 : Préparer vos 2 fichiers Excel
Cela pourrait paraître désormais simple d’utiliser cette fonction. Or, il faut s’en méfier ! En effet, la comparaison de fichiers Excel nécessite de prendre bien des précautions.
Lorsque Word compare deux fichiers, il peut se baser sur le contenu qui a de fortes chances de disposer de certaines similarités. Ainsi, les titres à différents endroits dans le document seront similaires. Word pourra se baser sur ces informations pour voir qu’entre ces deux titres, le contenu a changé et ainsi déterminer les écarts.
Dans Excel, comment savoir si deux contenus situés à la case A2 sont différents parce que :
ils sont le résultat d’une formule et que c’est la formule qui a été changée ?
une ligne a été insérée et qu’il faut comparer A2 du 1er fichier à A3 du 2e fichier ?
l’ordre de tri a changé et qu’il n’est plus en A2 ?
le contenu a été modifié ?
C’est donc à vous de prendre les précautions nécessaires pour présenter les deux fichiers d’une manière appropriée.
Le meilleur moyen est de disposer d’une colonne “maîtresse” (en base de données on parle d’une clé primaire) qui permet de trier toutes les autres colonnes selon un ordre croissant. Cela peut s’agir d’un numéro de facture, d’un numéro de commande, d’un numéro de projet, etc.
L’objectif est de présenter au composantInquire d’Excel au moins les premières lignes et colonnes avec des contenus censés être similaires.
Étape 3 : Sauvegarder vos fichiers avec des noms indiquant clairement leur origine
Inquire vous demandera de sauvegarder vos 2 fichiers Excel avant de lancer la comparaison. Si un de vos fichiers se trouve en pièce jointe d’un email et l’autre sur votre ordinateur, vous devrez enregistrer la pièce jointe sur votre ordinateur (ou n’importe quel autre espace de stockage).
Afin de déterminer quel contenu provient de quelle source, je vous recommande de clairement noter dans le nom du fichier une indication de l’original et de celui pouvant comporter des différences.
Étape 4 : Comparer vos 2 fichiers Excel
Pour lancer la fonction Comparer les fichiers, vous devez au préalable ouvrir les deux fichiers en même temps. Comme ils ont désormais des noms clairs et différents, Excel vous permet de les ouvrir en même temps.
Ouvrez la fonction Comparer les fichiers présente dans le ruban Inquire.
Vous relèverez que l’interface pour sélectionner les fichiers est un peu spartiate. Il n’y aucun moyen de naviguer pour sélectionner ses fichiers et les noms sont essentiels pour savoir quel est le fichier de référence et quel est le fichier contenant des modifications.
Voici donc l’interface de comparaison qui se dévoile :
Contenu du fichier de référence (premier des deux fichiers fournis) avec une certaine coloration des cellules (expliqué au point 3). La mise en forme initiale du fichier est totalement ignorée dans la présentation, mais prise en compte pour la comparaison.
Contenu du fichier avec du contenu modifié (deuxième des deux fichiers fournis) avec les mêmes éléments de coloration (expliqué au point 3). Idem pour la mise en forme qui est également ignorée pour l’affichage ici présent.
Liste des critères à prendre en compte ou à ignorer dans la comparaison. Chaque critère de comparaison dispose d’une certaine couleur. Chaque bloc de présentation reprend ces mêmes couleurs.
Liste exhaustive des différences trouvées entre les deux fichiers, avec les mêmes codes couleurs, pour conserver la cohérence de lecture des différences.
Récapitulatif sous forme d’histogramme du nombre de différences trouvées selon les critères de comparaison. On peut par exemple voir dans cet exemple qu’il y a plus de valeurs réellement mises à jour que de résultats de formules.
Étape 6 : Comparer deux fichiers Excel, oui, mais que cherchez vous à comparer ?
Souhaitez-vous comparer une feuille de chacun de vos classeurs ? Souhaitez-vous comparer une colonne en particulier d’une feuille de votre classeur ? Pensez bien qu’Excel ne peut se fier qu’aux références des cellules et comparer A1 d’un fichier avec A1 de l’autre fichier. Si une colonne a été insérée, toutes les cellules seront marquées comme différentes.
Je vous recommande donc d’avoir une approche méthodique selon le type de différences découvertes, et de faire l’analyse type par type.
Surtout, vérifiez que vous avez bien présenté les fichiers selon le bon critère de tri initial.
Il n’y qu’à regarder le résultat des deux mêmes fichiers comparés avec une mauvaise préparation :
On pourrait croire que toutes les valeurs ont été touchées car elles apparaissent colorées et mettant ainsi en valeur les différences. Or, en regardant de plus près, on peut voir que le tri n’est pas fait de la même façon.
Étape de secours : Pour tous ceux qui ne possède pas la bonne version d’Office
Je viens de découvrir avec stupeur que même en possédant la toute dernière version d’Office (Office 365), mais pas la version dite « ProPlus », je ne pouvais plus activer le composant de comparaison de fichiers Excel !
J’ai donc chercher une solution, et en ai trouvé une !
Malheureusement Tiphaine (cf. commentaires), il sera nécessaire d’installer quelque chose en plus… Selon vos besoins, il y a une option gratuite ou une option payante (mais avec bien plus d’options que juste la comparaison…).
Option 1 : Un add-on gratuit pour uniquement comparer deux feuilles Excel
J’ai trouvé un utilitaire sous licence GPL (General Public Licence). Cela signifie que vous pouvez l’utiliser librement à titre privé ou à titre professionnel (je ne suis pas juriste, je vous invite donc à le vérifier par vos soins ici : https://www.gnu.org/licenses/old-licenses/gpl-2.0-faq.html).
Voici un mode d’emploi de cet add-on gratuit de comparaison de feuilles Excel
La première étape consiste à l’installer. Personnellement, même en ayant coché la case « Installer et activer l’add-in », je ne l’ai pas trouvé sur Excel (ni sur Excel 2013, ni sur Excel 365).
J’ai donc dû l’installer manuellement.
Étape 1 : Activer le ruban « Développeur »
Un clic droit sur la bande supérieure d’Excel fait apparaître le menu suivant :
Sélectionnez « Personnaliser le ruban : » puis sur l’écran suivant :
Sélectionnez « Développeur ».
Étape 2 : Ouvrir le menu des compléments
Un nouveau menu apparaît dans la bande du ruban. Sur Office 2013 / Office 2016, cela donnera ceci :
Sur Office 365, vous aurez ceci :
Dans le premier cas, il faudra cliquer sur « Compléments » alors que dans le 2e cas, ce sera sur « Compléments Excel ». In fine, ce sera le même menu qui s’affichera dans les deux cas.
Étape 3 : Activer le complément de comparaison de fichiers Excel
Avant dernière étape avant de pouvoir utiliser le composant, aller chercher le composant et l’activer. Il vous faudra suivre les étapes indiquées ici :
Sélectionner le fichier « Spreadsheet Compare.xla »
Étape 4 : Utiliser le composant
Pour l’aide à l’utilisation du composant, un fichier d’aide est installé avec le composant au chemin suivant : « C:\Program Files (x86)\Spreadsheet Compare\Spreadsheet Compare Help.chm »
Option 2 : Un add-on payant pour comparer deux ou plusieurs feuilles Excel et également apporter de nombreuses fonctionnalités !
Plusieurs raisons peuvent vous faire choisir l’option de l’add-on payant :
Vous souhaitez avoir une garantie d’assistance
Vous ne souhaitez pas installer un composant pour une unique fonction
Alors, cet add-on est fait pour vous !
Comparer deux feuilles Excel avec un Add-on
Il possède un gros défaut, c’est que l’interface n’est qu’en anglais à ce jour. Je pourrai tenter de convaincre l’éditeur de traduire cet add-on si vous êtes plusieurs à le réclamer…
Par ailleurs, je tiens à une entière transparence, si vous utilisez ces liens pour acquérir cet add-on, je toucherai une petite commission. Toutefois, je n’ai pas été rémunéré pour faire de la pub. J’ai sélectionné ce produit pour son efficacité et son rapport qualité / prix totalement librement.
Bonne chance dans vos premiers pas avec cet outil de comparaison dans Excel. Il peut se révéler très puissant et salvateur, comme il peut vous rendre totalement chèvre !
Je vous remercie de m’indiquer si cet article vous a permis de comparer vos deux fichiers Excel. Si ce n’est pas le cas, faites-le moi savoir, ainsi je pourrais actualiser cet article pour l’améliorer.
Sachez également qu’il est possible de comparer deux fichiers Word alors que la fonction « Suivi des modifications » n’était pas active !
Vous pouvez vous faire pirater à cause d’un fichier .xls !
Dois-je utiliser les macros Excel ? Qu’est-ce que le VBA ? Ces deux termes (VBA et Macro) sont ont en réalité une forte relation entre elles. Les macros sont des morceaux de programme qui peuvent être ajoutés par vos soins. Ces bouts de programmes sont écrits dans un langage : le VBA – Visual Basic for Applications.
A quoi sert le VBA ? Est-ce une macro Excel ?
Qu’est-ce que le VBA ?
VBA signifie “Visual Basic for Application”. C’est un langage de programmation allégé. La version complète étant le VB.Net.
Le VBA permet d’automatiser toutes les actions que vous faites manuellement au sein des applications Office.
Prenons un exemple. Tous les mois, vous recevez des données dans plusieurs fichiers Excel différents. Puis, vous les fusionnez en suivant toujours les mêmes étapes de nettoyage des données.
Dans cette situation, il serait intéressant d’enregistrer la suite des actions que vous réalisez afin de pouvoir les reproduire automatiquement. C’est exactement à cela que sert le VBA. Pour être plus précis encore, le terme Macro désigne la fonction qui a été enregistrée et qui pourra être reproduite. Le VBA désigne le langage de programmation qui permet d’écrire les fonctions.
Commence-t-on par une macro Excel ou le VBA ?
Pour les plus rôdés d’entre vous, la réponse est « peu importe ». Pour les autres, je vous recommande de débuter avec la fonction d’enregistrement des actions (“enregistrer une macro” dans Word et Excel).
Pour accéder à cette fonction, il faut préalablement activer le ruban “Développeur”. Un clic droit sur le menu Accueil puis “Personnaliser le ruban” (idem dans Word, Excel, PowerPoint, Outlook – Fonction inexistante dans OneNote).
Puis cochez la case du menu “Développeur” pour l’afficher :
Vous aurez ensuite la possibilité d’accéder à la fonction “Enregistrer une macro” (uniquement Word et Excel). Pour les autres logiciels, pas d’enregistreur des actions manuelles ; il vous faudra écrire le code vous-même.
Et si cela ne fonctionne pas comme prévu ?
C’est à ce moment qu’il faut souvent passer par la case “Visual Basic” ou encore “Macros” >> “Editer” pour arriver dans le code qui a été produit automatiquement et le corriger manuellement.
Donc le VBA n’est là que pour automatiser des actions manuelles ?
Il serait totalement injuste de s’arrêter à cela ! En effet, à partir du moment où il est possible d’accéder à un langage de programmation, on peut faire bien plus que cela.
Il devient possible d’ajouter des composants tels que des boutons, des menus déroulants ou encore des nouvelles fenêtres pour interagir avec l’utilisateur… On rentre totalement dans le développement logiciel. On peut presque tout faire ! A condition d’apprendre à faire du développement.
Bon nombre de sites se sont spécialisés pour développer des applications complètes avec Excel en particulier.
Les feuilles de calcul sont utilisées pour sauvegarder les données et le reste n’est qu’une série d’interface avec des menus déroulants, des boutons, des listes qui ont été ajoutés afin de mettre à jour les données. Comme n’importe quelle application qui utiliserait une base de données.
Alors pour ou contre le VBA ?
Je ne tournerai pas autour du pot. Je suis personnellement un véritable puriste d’Excel, Word, etc. Devoir utiliser une macro Excel, c’est comme flirter avec l’ennemi…
Le purisme – Une macro Excel tu n’utiliseras point
Pourquoi ? Pourquoi être un puriste lorsque tant de possibilités s’ouvrent à nous ? De nombreuses raisons se bousculent à la porte pour justifier cela, faisons le tour de chacune d’elles.
Comme les macros sont surtout répandues dans l’utilisation d’Excel, je vais me focaliser sur l’utilisation de la macro Excel uniquement.
La première raison : la richesse d’Excel
Tout au long de ma carrière, j’ai pu constater qu’Excel est certainement le plus beau produit que je connaisse à ce jour. Je n’ai pas trouvé un problème qui ne puisse être résolu avec l’ensemble des fonctionnalités (hors macros et VBA) déjà disponibles dans Excel.
Même s’il m’a parfois été nécessaire de réfléchirlonguement et tourner le problème dans tous les sens, avant de finir par trouver un moyen de passer mon chemin en ayant évité le pire (= les macros).
La deuxième raison : la tranquillité de transmettre les fichiers
Eh oui, qui dit Macro, dit programmation, dit “Je peux tout faire” mais aussi “Faire n’importe quoi”. Comprenez, je peux programmer des actions malveillantes ! Et ce, de bonne foi, ou de mauvaise foi.
Par exemple, avec les macros, on peut modifier des fichiers qui se trouvent sur notre ordinateur, mais aussi sur les dossiers partagés de mon entreprise. Je peux interagir avec toutes les fonctions de mon ordinateur, mais aussi avec les équipements connectés à mon ordinateur.
C’est pour cette raison que dans les entreprises, les macros sont naturellement bloquées par défaut. Et elles peuvent être bloquées de différentes manières : interdiction de les exécuter ou interdiction de les transmettre par mail (quand je vous parlais de la peste…! ).
Depuis la version 2007, Microsoft a adopté des formats de fichiers distinctifs pour savoir si un fichier est porteur ou non d’une macro. On retrouvera la lettre “m” à la fin de l’extension “.xlsm », “.docm”, “.pptm”.
La troisième raison : si je ne peux pas le faire avec les fonctions standards, c’est que ce n’est pas le bon produit !
Pourquoi vouloir développer une application avec un langage qui est limité (VBA et non VB.Net !) ? Si j’ai besoin d’une application, alors autant en développer une ! J’aurai de toute façon besoin des mêmes compétences, mais au moins je ne serai pas limité par les fonctionnalités du langage à disposition.
Mais mes données sont dans Excel/Word/…, alors c’est plus simple d’interagir avec, non ?
Eh bien non ! Depuis 2008, le format OpenXML (format qui compose les fichiers Office) est une norme internationale. C’est à dire qu’on peut accéder au contenu sans utiliser les produits de Microsoft. Et pas seulement y accéder… On peut aussi créer, modifier des documents Office sans obligation de passer par les produits de Microsoft.
N’importe quel langage de programmation me permet alors d’accéder, comme mes macros, au contenu des fichiers Office, de les modifier, etc. Et surtout : les fichiers restent “propres”, sans macros, sans risque et sont donc transmissibles partout.
La quatrième raison : la recrudescence des virus dans les macros
Les pirates informatiques l’ont bien compris : les macros sont des applications qui passent plus facilement les barrières des entreprises ! Alors ils programment leur virus avec et les transmettent aux entreprises et aux particuliers. Le mot macro devient synonyme de risque.
Il n’y a pas de règles sans exception… Une petite macro Excel de temps en temps…
Pour une utilisation limitée, personnelle, très spécifique ou si vous savez qu’il n’y a pas d’autres solutions, alors on peut utiliser une petite macro Excel, évidemment. L’un de mes articles en fera même la démonstration pour un cas non résolu : le changement de la langue dans une présentation PowerPoint. Je n’ai pas trouvé d’autre solution que de vous proposer d’utiliser une macro !
[Tuto] Comment mettre en évidence uniquement la valeur maximale de chaque ligne dans un tableau Excel ?
La mise en forme conditionnelle de la valeur maximale est un besoin très classique des utilisateurs d’Excel. C’est pourquoi je vous propose à travers cet article de vous montrer comment le faire. Exceptionnellement, je vous propose un article sur un ton un peu léger et moins formelle que d’habitude.
Mise en forme conditionnelle de la valeur maximale dans Excel : Première étape
Mais oui… Je suis sûr qu’il y a un truc dans Excel pour faire ça automatiquement. Voyons voir… Regardons dans la mise en forme conditionnelle, je crois que c’est là…
Il y a plein de truc sympas, pleins de couleurs, mais pas de quoi mettre en avant uniquement la valeur maximale… C’est peut-être dans les autres règles ? Continuons notre exploration…
Zut alors… Il va falloir trouver la solution tout seul…
Étape 1 : A nous de définir nos règles !
Sélectionnez votre tableau de valeurs pour créer une nouvelle règle de mise en forme comme suit :
Mise en forme conditionnelle de la valeur maximale – Nouvelle règle
Étape 2 : Écrivons notre formule magique
=SI(B2=MAX($B2:$L2);VRAI;FAUX)
Mise en forme conditionnelle de la valeur maximale – Contenu de la règle
La formule mérite une petite traduction. En français, cela donnerait ceci :
Si ma cellule courante (B2 sans les $) est égale au maximum de ma ligne (MAX($B2:$L2) alors j’ai trouvé le vainqueur.
La subtilité réside dans l’utilisation ou non du fameux symbole $. Il faut lire ce symbole comme un cadenas. Il se place toujours avant l’information qu’il veut bloquer (la lettre de la colonne ou le n° de la ligne).
$B$2:$L$2 signifie « Bloque la colonne à B, bloque la ligne à 2 » jusqu’à « bloque la colonne à L et bloque la ligne à 2 » ou encore « Fais toujours référence aux cellules allant de B2 à L2 ».
$B2:$L2 signifie « Fais toujours référence aux cellules allant de la colonne B à la colonne L, mais si les lignes changent, alors change les références en $B3:$L3, $B4:$L4, $B5:$L5 et ainsi de suite.
B$2:L$2 signifie « Fais toujours référence à la ligne 2, avec le même nombre de colonne qu’il y a entre B et L ». Si mon point départ change de colonne, alors les références deviennent C$2;M$2, D$2;N$2, E$2;O$2 et ainsi de suite ».
Comme nous voulons la valeur maximale par ligne, nous choisissons la deuxième option !
Etape 3 : Choisissez votre mise en forme en cas de succès
En cliquant sur le bouton “Format…”, vous devez sélectionner une mise en forme différente afin de mettre en évidence la cellule qui sera le maximum de la ligne. Ici, j’ai choisi de mettre le fond en rouge. Jugez du résultat :
Faisons un test en mettant une valeur élevée dans la cellule B2 (ici 100) :
Parfait !
Etape 4 : Et si je souhaitais la valeur maximale par colonne ?
Je serais bien tenté de vous demander de faire l’exercice par vous-même… mais bon… voici la réponse :
=SI(B2=MAX(B$2:B$18);VRAI;FAUX)
Il suffit de changer les références dans le choix de la valeur maximale. Cette fois, c’est B$2:B$18, car je souhaite fixer le bloc selon les lignes et la rendre mobile selon les colonnes.
Cela donne immédiatement ceci :
Etape 5 : Dernière étape
Si vous avez aimé cette astuce, faites-le moi savoir dans les commentaire la page !
Merci !
Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site web. Si vous continuez à utiliser ce site, nous supposerons que vous en êtes satisfait.OkPolitique de confidentialité
).
