Bi for Excel : comment analyser et visualiser vos données dans Excel

Un fichier Excel peut commencer sa vie comme un simple tableau de ventes et finir en créature tentaculaire, avec douze onglets, des formules copiées à la main et une personne qui sait seule comment tout fonctionne. C’est souvent à ce moment-là que l’on découvre les limites d’une analyse improvisée. La bonne nouvelle, c’est qu’Excel dispose déjà de plusieurs outils de veille stratégique pour transformer des données brutes en informations utiles.

Power Query, le modèle de données, les tableaux croisés dynamiques et Power Pivot permettent de préparer, relier, analyser et visualiser des données sans quitter complètement l’univers Excel. Power BI peut ensuite prendre le relais lorsque le partage et la mise à jour des rapports deviennent plus ambitieux. Voici comment assembler ces briques sans faire de votre classeur un plat de spaghettis numériques.

La veille stratégique dans Excel, qu’est-ce que cela signifie ?

La veille stratégique, souvent désignée par le sigle anglais BI, consiste à exploiter des données pour mieux comprendre une activité et éclairer les décisions. Dans Excel, cela peut prendre une forme très accessible : suivre les ventes par région, comparer les dépenses d’un service ou repérer les produits dont les stocks baissent trop vite.

Un tableau brut répond surtout à la question « qu’est-ce qui s’est passé ? ». Une analyse bien construite aide aussi à comprendre « où ? », « quand ? » et « par rapport à quoi ? ». Un graphique ne devient donc pas utile simplement parce qu’il est coloré. Il doit permettre de repérer un écart, une tendance ou une anomalie plus vite qu’en parcourant des centaines de lignes.

Excel est particulièrement pratique lorsque les données sont de taille raisonnable, que les utilisateurs connaissent déjà le logiciel et que l’analyse doit rester souple. Mais le tableur n’est pas une baguette magique : si les informations sont mal organisées ou les définitions floues, les résultats le seront aussi. Une jolie courbe ne répare pas une colonne de dates remplie à moitié de dates et à moitié de texte.

Commencer par des données propres et bien structurées

Avant de fabriquer un tableau de bord, il faut préparer le terrain. Une table exploitable comporte généralement une ligne par observation et une colonne par type d’information : date, produit, région, quantité, montant, par exemple. Les noms de colonnes doivent être explicites, les cellules fusionnées évitées et les lignes de total placées hors de la plage de données.

Cette structure peut sembler moins jolie qu’un tableau mis en page comme un rapport. Elle est pourtant bien plus facile à filtrer et à actualiser. Pensez à une bibliothèque : les livres rangés par catégorie et dotés d’une étiquette se retrouvent vite. Un tas de livres décoratif, beaucoup moins.

Il faut aussi vérifier quelques éléments simples avant l’analyse :

• Les dates sont reconnues comme des dates, et les montants comme des nombres.

• Les valeurs comparables utilisent les mêmes conventions : « Île-de-France » ne doit pas cohabiter avec « IDF » sans règle de rapprochement.

• Les doublons et les valeurs manquantes sont repérés, puis traités selon leur signification métier.

• Les unités sont explicites : euros, pièces, heures ou pourcentages ne se mélangent pas dans une même colonne.

Ces vérifications évitent de produire des analyses trompeuses. Un chiffre peut être calculé correctement par Excel et rester faux dans la réalité, parce que la donnée d’entrée ne représente pas ce que l’on croit.

Power Query : préparer les données sans recommencer chaque semaine

Power Query est l’outil de connexion et de transformation intégré à Excel. Il peut importer des informations depuis des fichiers, des dossiers, des bases de données ou d’autres sources compatibles. Son intérêt majeur : enregistrer les étapes de préparation pour pouvoir les rejouer à l’actualisation.

Imaginons une équipe commerciale qui reçoit chaque lundi un fichier contenant les ventes de la semaine précédente. Sans automatisation, quelqu’un ouvre le fichier, supprime les colonnes inutiles, corrige les formats, ajoute une région et recopie le résultat dans un classeur de suivi. C’est faisable, jusqu’au jour où une étape est oubliée ou où le fichier change légèrement de structure.

Avec Power Query, on définit une suite d’opérations : conserver les colonnes nécessaires, corriger les types, filtrer les lignes vides, fusionner les fichiers d’un dossier et associer les ventes à une table de régions. La semaine suivante, il suffit souvent d’ajouter le nouveau fichier puis d’actualiser la requête. Les étapes restent visibles et modifiables, ce qui facilite la maintenance.

Power Query ne rend pas toute transformation automatiquement correcte. Il faut contrôler le résultat, surtout après une modification de la source. Si un fournisseur renomme une colonne ou change le format d’une date, une requête peut échouer ou produire un résultat inattendu. L’automatisation supprime les gestes répétitifs, pas le besoin de vérifier.

Le modèle de données : relier les informations au lieu de les empiler

Quand plusieurs tables décrivent la même activité, on peut être tenté de tout fusionner en une immense feuille. Cette méthode fonctionne parfois, mais elle répète les mêmes informations à l’infini. Le nom d’un produit, par exemple, peut apparaître sur chaque ligne de vente. Une correction devient alors une chasse au trésor peu amusante.

Le modèle de données d’Excel permet de conserver des tables séparées et de les relier à l’aide de champs communs. Une table de ventes peut ainsi être associée à une table de produits, une table de clients et une table de dates. On parle souvent de schéma en étoile : la table des faits, qui contient les événements mesurables, est reliée à des tables descriptives.

Dans notre exemple, la table des ventes contient le montant, la quantité et une référence de produit. La table des produits précise le nom, la catégorie et la marque. Cette séparation évite les répétitions et permet d’analyser les ventes par catégorie sans recopier la catégorie sur chaque ligne.

Les relations doivent être définies avec soin. Une référence doit désigner le même élément dans les tables liées, et la table descriptive doit généralement contenir une seule ligne par élément. Si les identifiants sont incohérents, les totaux peuvent être incomplets ou surprenants. Excel n’a pas le sens du contexte : il suit les relations qu’on lui donne, même si elles sont mauvaises.

Tableaux croisés dynamiques et mesures utiles

Le tableau croisé dynamique est souvent le moyen le plus rapide d’explorer un jeu de données. On déplace des champs dans les zones de lignes, de colonnes, de filtres et de valeurs pour examiner les résultats sous différents angles. Quelques glissements de souris suffisent à comparer les ventes par région, puis par mois, puis par catégorie.

Lorsqu’il s’appuie sur le modèle de données, le tableau croisé dynamique peut exploiter plusieurs tables reliées. Power Pivot, composant de modélisation associé à Excel, permet de gérer ce modèle et de créer des mesures plus élaborées avec le langage DAX. Une mesure est un calcul évalué selon le contexte de l’analyse : par exemple, le chiffre d’affaires total pour la région actuellement sélectionnée.

Cette distinction est importante. Une colonne calculée ajoute une valeur à chaque ligne de la table. Une mesure calcule un résultat à la demande, en fonction des filtres actifs. Pour obtenir une marge totale, une comparaison avec l’année précédente ou un taux de réalisation, une mesure bien définie est souvent plus adaptée qu’une forêt de formules dispersées dans les feuilles.

Avant de créer des calculs complexes, clarifiez les règles métier. Le chiffre d’affaires inclut-il les taxes ? Une commande annulée est-elle exclue ? Le taux de croissance compare-t-il des périodes complètes ? Ces questions semblent moins techniques que DAX, mais elles déterminent si le résultat sera vraiment utile.

Visualiser pour faire ressortir l’information

Une visualisation efficace met en avant une question précise. Pour comparer des catégories, un graphique en barres est généralement plus facile à lire qu’un diagramme circulaire composé de nombreuses parts. Pour suivre une évolution dans le temps, une courbe aide à repérer une tendance. Pour montrer une répartition géographique, une carte peut être pertinente, à condition que les données de localisation soient suffisamment fiables.

Un tableau de bord n’a pas besoin de tout montrer. Il doit aider son lecteur à comprendre rapidement la situation et à décider quoi examiner ensuite. Quelques indicateurs clés, un graphique d’évolution et des filtres clairs peuvent être plus efficaces qu’une mosaïque de visualisations qui se disputent l’attention.

Dans Excel, les segments permettent de filtrer facilement un tableau croisé dynamique, par exemple par région ou par catégorie. Une chronologie peut servir à sélectionner une période. Ces contrôles rendent le rapport interactif, mais il faut garder les filtres visibles : un chiffre isolé, sans indication de la période ou du périmètre sélectionné, peut être interprété de travers.

Pour chaque graphique, posez-vous une question simple : « Quelle décision ou quelle découverte cette représentation facilite-t-elle ? » Si la réponse est « elle fait joli dans la présentation », il est peut-être temps de retirer le graphique. L’esthétique compte, mais elle n’a pas à conduire la réunion.

Excel ou Power BI : choisir l’outil adapté

Excel convient bien à l’exploration, aux analyses ponctuelles et aux rapports utilisés par une équipe restreinte. Il offre de la souplesse et s’intègre naturellement aux habitudes de nombreux utilisateurs. Pour un suivi personnel ou un rapport mensuel maîtrisé, il peut largement suffire.

Power BI devient intéressant lorsque plusieurs personnes doivent consulter les mêmes rapports, que les actualisations doivent être organisées ou que les volumes et les besoins de partage dépassent le cadre d’un classeur. Il permet de publier des rapports interactifs et de centraliser certaines définitions. Les deux outils peuvent se compléter : Excel pour analyser ou préparer certains éléments, Power BI pour diffuser des tableaux de bord plus largement.

Le choix dépend moins d’une compétition entre logiciels que de questions pratiques :

• Combien de personnes consulteront le rapport, et avec quels droits ?

• À quelle fréquence les données doivent-elles être actualisées ?

• Les utilisateurs ont-ils besoin de modifier librement l’analyse ou seulement de consulter des résultats ?

• Le volume de données et la complexité des relations restent-ils confortables dans Excel ?

Un changement d’outil ne résout pas à lui seul un problème de gouvernance. Il faut toujours savoir qui possède les données, qui peut les modifier et quelle version du rapport fait foi. Sinon, le tableau de bord officiel finira à côté de trois copies presque identiques, nommées « final », « final-v2 » et « final-vraiment-final ».

Construire une analyse fiable, étape par étape

Pour démarrer sans transformer le projet en chantier interminable, choisissez une question métier concrète. Par exemple : « Quelles catégories de produits expliquent la baisse des ventes ce trimestre ? » Identifiez ensuite les sources nécessaires, préparez-les avec Power Query et reliez les tables utiles dans le modèle de données.

Créez quelques mesures correspondant aux indicateurs définis avec les personnes concernées, puis construisez un tableau croisé dynamique pour vérifier les résultats. Enfin, choisissez des graphiques adaptés et testez le rapport avec un utilisateur qui n’a pas participé à sa création. S’il ne comprend pas les filtres ou les chiffres sans explication orale, le rapport doit probablement être simplifié.

Documentez les sources, les règles de calcul et la fréquence d’actualisation. Vérifiez aussi les résultats avec un calcul indépendant ou un échantillon connu. Cette étape n’est pas spectaculaire, mais elle protège la confiance dans l’analyse. Et dans un outil d’aide à la décision, la confiance vaut davantage qu’un effet de transition bien choisi.

La veille stratégique dans Excel n’est donc pas une collection de graphiques, mais une chaîne de travail : des données structurées, une préparation reproductible, un modèle cohérent, des calculs explicites et des visualisations lisibles. En maîtrisant ces étapes, on passe du classeur que l’on subit au rapport que l’on peut réellement interroger. Excel reste alors ce qu’il sait faire de mieux : rendre l’analyse accessible, à condition de lui donner des données et des règles qui tiennent debout.