De l'Excel au vaisseau spatial en deux modules complémentaires 😎

Excel, utilisé dans 90% des entreprises et critiqué à tort, n'exploite en réalité qu'une infime partie de ses capacités. Les données sont saisies manuellement, les types corrigés à la main, et la question « ça se met à jour tout seul ? » provoque un profond soupir. Parce que deux onglets du menu sont restés intacts.

Le planning de la plupart des gens ressemble à ceci : télécharger le fichier, nettoyer manuellement, ajouter des formules, envoyer au chef. Le lendemain, on recommence. Le surlendemain, pareil. Un ennui mortel. 😔
Quarante minutes par jour pour un travail qui devrait prendre trois clics.

Ajoutez maintenant 2 millions de lignes. Excel plafonne à un million de lignes et plante. Ok, vous divisez le fichier en plusieurs parties. Vous perdez les liens entre les tableaux, le tableau croisé dynamique commence à mentir.

Mais on peut activer 2 modules complémentaires et utiliser Excel à fond.
J'ai découvert ces modules en 2017. Je faisais du freelance. Des sites WordPress et autres tâches routinières. Une mission arrive : extraire certains groupes de produits de 60 gros fichiers Excel et tout fusionner en un seul fichier. Deux jours pour le faire. J'ai essayé de rassembler 2 fichiers manuellement. Horrible, ennuyeux et fastidieux. J'ai commencé à chercher sur le net, au cas où il y aurait une pilule magique. Et j'ai trouvé Power Query. Après avoir un peu bidouillé l'interface, j'ai connecté tous les fichiers d'un dossier et assemblé le fichier cible en 10 minutes. J'en suis resté bouche bée. Bon, je vais vous expliquer ce que sont ces modules 💪

1️⃣ Power Query : des données sans les mains

Il est déjà intégré. Onglet « Données », section « Obtenir et transformer ». Vous connectez la source une fois, configurez la transformation, puis cliquez sur « Actualiser ». Le processus qui prenait une heure prend désormais dix secondes.

Mais il y a un petit problème. Power Query « devine » les types de données en ne regardant que les 200 à 1000 premières lignes, selon la source. Pour les CSV, 200 suffisent, pour d'autres formats un peu plus. Mais le principe est le même : si les données sont hétérogènes, les nombres deviennent silencieusement du texte sans aucun avertissement.

Pour dormir tranquille, définissez toujours les types de données manuellement. Ne faites pas confiance à la détection automatique. Oui, c'est plus long. Mais ensuite, vous n'aurez pas à retravailler les anciens paramètres et à chercher le problème.

Et encore une chose. Ne faites pas une seule énorme requête. Utilisez toujours une approche itérative dans votre travail.
Une chaîne fonctionne mieux : une étape distincte pour le nettoyage, une autre pour la transformation. Le chargement aussi séparément. Quand quelque chose casse (et ça arrivera), vous trouverez le problème en une minute, pas en une heure.

Et oui, PQ permet de se connecter directement aux bases de données ✅

2️⃣ Power Pivot : Excel sans plafond

1 048 576 lignes — la limite d'Excel standard. Power Pivot gère des dizaines de millions grâce à la compression VertiPaq (le même moteur que dans Power BI). Le fichier xlsx lui-même devient plus lourd, une base de données est emballée à l'intérieur. Mais en mémoire, les données prennent beaucoup moins de place : VertiPaq compresse environ 10 fois (bon, un ordre de grandeur — le chiffre exact dépend des données), stocke en colonnes, pas en lignes. Les grandes données volent.

L'essentiel, ce sont les mesures. Ne mettez pas tout dans des colonnes calculées : elles sont calculées en permanence et gonflent le modèle, alors qu'une mesure n'est calculée que lorsque vous la voyez dans le tableau croisé dynamique, dans un contexte de filtre spécifique. La différence est énorme.

Le cœur de Power Pivot est la fonction CALCULATE. Elle modifie le contexte de calcul. « Ventes sans retours », « plan uniquement pour Moscou », « et si le prix augmentait de 10% ». Tout cela, c'est CALCULATE.

Pour les amateurs de pommes (Apple), dont je fais partie 😁
Power Pivot n'est pas disponible sur macOS. C'est triste, mais c'est comme ça. Si des collègues dans l'équipe sont sur Mac, ils ne verront tout simplement pas cette merveille.

Si vos rapports tournent autour d'Excel, essayez ces deux modules 👇

#excel

@data_dzen 🙂