C'est quoi un modèle de données dans Excel ?
Le modèle de données est un espace interne au classeur où plusieurs tables coexistent, reliées entre elles par des relations. On l'alimente et on l'interroge depuis un tableau croisé dynamique, sans qu'il apparaisse dans les feuilles. Il s'appuie sur le moteur en mémoire de Power Pivot et dépasse largement le million de lignes d'une feuille.
À quoi sert le modèle de données dans Excel
Le modèle de données te sert à analyser plusieurs tables ensemble sans les recopier les unes dans les autres à coups de RECHERCHEV. Tu gardes tes tables séparées, une pour les clients, une pour les commandes, une pour les produits, et tu les relies par une colonne commune. Un seul tableau croisé dynamique puise alors dans les trois à la fois, comme le ferait une base de données.
L'intérêt tient d'abord à la place que tu ne perds plus. Sans modèle, ramener la région du client ou la catégorie du produit dans chaque ligne de vente gonfle le fichier de colonnes dupliquées et le ralentit. Avec le modèle, la relation fait ce rapprochement à la volée, et rien n'est recopié dans les feuilles.
Tu y viens le jour où une seule feuille ne suffit plus. C'est le cas quand tes données dépassent le million de lignes, quand elles arrivent de plusieurs sources, ou quand une analyse doit croiser des informations éparpillées dans des tables différentes. Le moteur en mémoire encaisse ces volumes qu'un classeur ordinaire refuse.
Comment ajouter des tables au modèle de données dans Excel
Le modèle se remplit table par table, puis se relie par une colonne commune. Mets tes plages sous forme de tableaux, ajoute-les au modèle en créant un tableau croisé dynamique, puis trace les relations depuis l'onglet Données.
- 1Mets chaque plage sous forme de tableau avec Ctrl+L, pour qu'Excel la reconnaisse comme une table nommée.
- 2Sélectionne une table, ouvre l'onglet Insertion, puis clique sur Tableau croisé dynamique.
- 3Dans la fenêtre qui s'ouvre, coche « Ajouter ces données au modèle de données » avant de valider.
- 4Répète l'opération pour chaque table à intégrer, elles rejoignent toutes le même modèle.
- 5Ouvre l'onglet Données puis Relations, et relie deux tables en choisissant leur colonne commune.
Quelle différence entre le modèle de données et Power Pivot
Le modèle de données est le contenu, Power Pivot est l'outil qui l'édite. Le modèle, ce sont tes tables chargées en mémoire et les relations qui les lient, et il existe dans le fichier dès que tu coches « Ajouter au modèle de données ». Power Pivot, lui, est un complément d'Excel, une fenêtre à part qui vient travailler ce contenu.
Tu peux donc alimenter un modèle et créer des relations sans jamais activer Power Pivot. Le tableau croisé dynamique et Power Query suffisent à charger des tables dans le modèle et à les relier depuis le menu Données puis Relations. Le modèle tourne en arrière-plan, et l'analyse marche déjà.
Power Pivot apporte ce que ces menus ne donnent pas. Il ouvre la vue diagramme où tu vois tes tables et tires les relations à la main, il gère finement les colonnes, et surtout il te laisse écrire des mesures en DAX. Le modèle reste le même dessous, Power Pivot ne fait que te donner de meilleurs outils pour le travailler.
Pourquoi Excel refuse de créer une relation dans le modèle de données
Excel refuse la relation quand la colonne commune contient des doublons du côté de la table de référence. Une relation relie une table de détail, tes ventes, à une table de référence, tes clients ou tes produits, et cette clé doit être unique côté référence. Deux clients qui portent le même identifiant, et Excel bloque, parce qu'il ne saurait pas vers quelle ligne pointer.
La parade tient en une vérification avant de relier. Assure-toi que la clé n'apparaît qu'une seule fois dans la table de référence, quitte à construire une table dédiée qui liste chaque valeur une fois. Le côté détail, lui, a le droit de répéter la clé autant qu'il veut, c'est même son rôle.
Un second motif de refus tient au format des colonnes. Excel relie une colonne de texte à une colonne de texte, pas un identifiant stocké en nombre d'un côté et en texte de l'autre. Uniformise le type des deux colonnes avant de tracer la relation, sinon le lien ne se crée pas.
Les termes liés au modèle de données
Questions fréquentes sur le modèle de données
Souvent, le classeur est en mode de compatibilité, hérité d'un ancien format qui ne gère pas le modèle. Enregistre-le au format Excel actuel, ferme puis rouvre le fichier, et la case réapparaît dans la fenêtre de création du tableau croisé dynamique. Vérifie aussi que ta source est bien une table et que ton édition d'Excel gère le modèle, ce qui n'est pas le cas d'Excel pour le web.
Oui, pour croiser plusieurs tables il rend les RECHERCHEV inutiles. Tu relies tes tables par une clé commune une seule fois, et le tableau croisé dynamique exploite ensuite les champs de toutes les tables sans formule de rapprochement. Le fichier reste léger, car tu ne dupliques plus les colonnes dans chaque ligne comme le ferait un RECHERCHEV.
Le modèle de données dépasse largement la limite de 1 048 576 lignes d'une feuille, car il stocke les données compressées en mémoire, pas dans les cellules. Tu peux ainsi analyser plusieurs millions de lignes issues de bases externes ou de plusieurs fichiers. La vraie limite devient la mémoire vive de ton ordinateur, pas la taille d'une feuille.
Pour aller plus loin
Tu sais maintenant ce que le mot veut dire. Reste à s’en servir, avec un fichier qui l’utilise, le cours qui remet tout dans l’ordre et de quoi t’entraîner dessus.
Tout le lexique
