Qu'est-ce que la fonction LIREDONNEESTABCROISDYNAMIQUE ?
LIREDONNEESTABCROISDYNAMIQUE (GETPIVOTDATA en anglais) te sert dès que tu dois piloter un rapport depuis un TCD sans copier-coller des chiffres qui deviennent faux à la moindre actualisation. Elle garde un lien vivant entre la cellule de ton rapport et la valeur calculée par le tableau croisé, quel que soit le nombre de filtres qui la définissent.
Concrètement, c'est elle qui alimente automatiquement ton tableau de bord exécutif avec le CA de chaque région, extrait les ventes d'un produit précis sur un trimestre donné, ou calcule des ratios en croisant plusieurs filtres simultanément. Chaque actualisation du TCD met à jour toutes les formules en un seul clic.
Syntaxe
=LIREDONNEESTABCROISDYNAMIQUE(champ_données; tableau_croisé; [champ1]; [élément1]; ...)Clique sur un argument pour aller à son explication.
Excel génère automatiquement une formule LIREDONNEESTABCROISDYNAMIQUE quand tu cliques sur une cellule d'un TCD depuis la barre de formule. Utilise ce mécanisme pour récupérer les noms de champs exacts : une espace ou une majuscule différente suffit à provoquer #REF!.
Comprendre chaque paramètre de la fonction LIREDONNEESTABCROISDYNAMIQUE
Les deux premiers arguments sont obligatoires et leur ordre est imposé : d'abord le champ de valeur que tu veux lire, ensuite une cellule du TCD qui sert d'ancrage. Tout ce qui suit, ce sont des paires champ; élément facultatives qui filtrent le résultat.
Ces paires doivent rester groupées deux par deux : un nom de champ, puis tout de suite sa valeur. En oublier la moitié est l'erreur la plus rapide à commettre, et tu peux en empiler autant que ton rapport en réclame.
champ_données
: le nom exact du champ de valeurs que tu veux extraire, tel qu'il apparaît dans la zone « Valeurs » de ton TCDExcel ajoute souvent un préfixe automatique comme "Somme de " ou "Nombre de " : le nom dans la formule doit correspondre mot pour mot à ce qui est affiché dans le TCD.
Par exemple, si ton TCD affiche Somme de Ventes, tu dois écrire "Somme de Ventes" et non "Ventes" seul.
Attention : Un seul caractère de différence dans le nom du champ génère une erreur #REF!. Pour récupérer le nom exact, clique directement sur la cellule du TCD depuis la barre de formule : Excel génère automatiquement la formule avec le bon nom.
tableau_croisé
: une référence à n'importe quelle cellule de ton tableau croisé dynamiqueCette cellule identifie quel TCD utiliser, utile si tu as plusieurs TCD dans le même classeur.
Utilise toujours une référence absolue ($A$3) pour éviter que la formule ne se casse quand tu la copies vers d'autres cellules. Sans les $, la référence se décale et pointe hors du TCD.
[champ1], [élément1]...
: des paires de paramètres qui filtrent la valeur à extraire(facultatif)champ1 est le nom d'un champ du TCD (ligne, colonne ou filtre), élément1 est la valeur précise que tu recherches dans ce champ. Tu peux ajouter autant de paires champ/élément que nécessaire pour affiner le filtre.
Par exemple, pour extraire les ventes de Laptops en Q1 dans la région Ouest, tu passes trois paires : "Produit"; "Laptop"; "Trimestre"; "Q1"; "Région"; "Ouest".
Exemples pratiques pas à pas
Comment extraire le total d'une région avec la fonction LIREDONNEESTABCROISDYNAMIQUE
Ton tableau de bord doit afficher le total de chaque région à côté des objectifs, et il est relu en réunion chaque lundi. Les chiffres vivent dans un tableau croisé dynamique que tu actualises régulièrement, et tu ne veux pas recopier les totaux à la main à chaque fois. La fonction LIREDONNEESTABCROISDYNAMIQUE va chercher la valeur dans le tableau croisé et garde le lien, si bien que ton rapport suit chaque actualisation.
- 1Dans une cellule, écris
=LIREDONNEESTABCROISDYNAMIQUE(. - 2En 1er argument : écris le nom exact du champ de valeur à lire,
"Somme de Ventes". C'est lui qui décide du chiffre rapporté, et il doit reprendre mot pour mot ce qui s'affiche dans la zone Valeurs de ton tableau croisé, préfixe compris. Les guillemets sont obligatoires parce que tu passes ce nom sous forme de texte. - 3En 2ᵉ argument : clique sur une cellule quelconque du tableau croisé,
$A$3. Elle sert seulement à repérer de quel tableau tu parles quand le classeur en compte plusieurs. Fixe-la avec des$pour qu'elle ne se décale pas quand tu recopies la formule ailleurs. - 4En 3ᵉ argument : écris le nom du champ sur lequel tu veux filtrer,
"Région". Il ouvre la première paire champ/élément de la fonction. C'est ce champ qui annonce l'axe à filtrer, l'élément voulu arrivant à l'argument suivant. - 5En 4ᵉ argument : écris l'élément précis à isoler dans ce champ,
"Nord". Il ferme la paire ouverte juste avant, et c'est le couple"Région"puis"Nord"qui pointe la ligne cherchée. Ces arguments vont toujours par deux, et sans la moindre paire la fonction renverrait le total général de 62 500 €. - 6Ferme la parenthèse et appuie sur Entrée.
=LIREDONNEESTABCROISDYNAMIQUE("Somme de Ventes"; $A$3; "Région"; "Nord")"Région"; "Nord" pointe la ligne Nord du tableau croisé et en rapporte le total, soit 30 000 €. La valeur reste branchée sur le tableau croisé, si bien qu'une actualisation la met à jour toute seule là où un copier-coller aurait figé le chiffre de la semaine dernière.
Comment croiser deux critères avec la fonction LIREDONNEESTABCROISDYNAMIQUE
On te demande le détail par produit derrière chaque total régional, parce que le Nord tient ses objectifs sans qu'on sache lequel de ses deux produits porte le résultat. Ton tableau croisé affiche déjà les régions en lignes et les produits en colonnes, et tu veux piocher une case précise à l'intersection des deux. La fonction LIREDONNEESTABCROISDYNAMIQUE accepte autant de paires champ et élément que ton rapport en réclame, et les empile comme un filtre.
- 1Dans une cellule, écris
=LIREDONNEESTABCROISDYNAMIQUE(. - 2En 1er argument : écris le nom du champ de valeur à lire,
"Somme de Ventes". Croiser deux critères ne change rien au chiffre que tu lis, seulement à l'endroit d'où tu le tires, donc ce premier argument reste identique à une lecture à un seul filtre. - 3En 2ᵉ argument : clique sur une cellule du même tableau croisé,
$A$3. Tu interroges toujours le même rapport, l'ancrage ne bouge donc pas d'un exemple à l'autre. Garde ses$pour que la formule résiste à la recopie. - 4En 3ᵉ argument : écris le nom du champ porté par les lignes,
"Région". Il ouvre la première paire et choisit l'axe des régions du tableau croisé. - 5En 4ᵉ argument : écris la région voulue,
"Nord". Elle ferme cette première paire et retient la ligne Nord, exactement comme dans une lecture à un seul critère. - 6En 5ᵉ argument : écris le nom du second champ à filtrer,
"Produit". Il ouvre une deuxième paire, qui va cette fois désigner une colonne du tableau croisé. - 7En 6ᵉ argument : écris le produit voulu,
"Ordinateur". Il ferme la seconde paire, et les deux filtres se cumulent au lieu de s'additionner pour ne garder que la case au croisement de la ligne Nord et de la colonne Ordinateur. Compte tes arguments après les deux premiers, il t'en faut toujours un nombre pair sous peine de casser la formule. - 8Ferme la parenthèse et appuie sur Entrée.
=LIREDONNEESTABCROISDYNAMIQUE("Somme de Ventes"; $A$3; "Région"; "Nord"; "Produit"; "Ordinateur")"Ordinateur" par "Écran", ce qui donnerait 3 000 €.
Comment gérer l'erreur #REF! avec les fonctions LIREDONNEESTABCROISDYNAMIQUE et SIERREUR
Ton modèle de rapport liste les quatre régions commerciales, mais le tableau croisé du mois ne remonte que celles qui ont vendu quelque chose. Dès qu'une région reste muette, sa ligne réclame une valeur qui n'existe pas et le rapport se couvre d'erreurs juste avant d'être diffusé. La fonction SIERREUR entoure la lecture et remplace l'erreur par un message clair, ce qui te laisse un tableau présentable.
- 1Dans une cellule, écris
=SIERREUR(. - 2En 1er argument : écris la lecture normale et referme-la,
LIREDONNEESTABCROISDYNAMIQUE("Somme de Ventes"; $A$3; "Région"; "Ouest"). Telle quelle, elle renvoie l'erreur#REF!puisque le tableau croisé ne contient aucune région Ouest. C'est cette lecture que la fonction SIERREUR va surveiller. - 3En 2ᵉ argument : écris le message à afficher en cas d'erreur,
"Région absente". Préfère un texte explicite à une chaîne vide, sans quoi tu ne distingueras plus une région sans vente d'une région oubliée dans le tableau croisé. - 4Ferme la parenthèse de la fonction SIERREUR et appuie sur Entrée. Celle de la lecture a déjà été refermée à l'étape précédente.
=SIERREUR(LIREDONNEESTABCROISDYNAMIQUE("Somme de Ventes"; $A$3; "Région"; "Ouest"); "Région absente")#REF!, et la fonction SIERREUR l'intercepte pour afficher « Région absente » à la place. Le rapport reste lisible et te dit quoi corriger, au lieu d'étaler une erreur en pleine réunion.
Les erreurs fréquentes avec la fonction LIREDONNEESTABCROISDYNAMIQUE
Neuf fois sur dix, ce qui coince c'est le nom du champ : tu écris "Ventes" alors que le TCD affiche Somme de Ventes, et Excel répond #REF!. Une espace en trop ou une majuscule de travers suffit à casser le lien.
Les autres pièges tournent autour de la même idée de cible qui bouge : une référence relative qui se décale quand tu copies la formule, un champ renommé dans le TCD, ou une fonction pointée vers un tableau normal qui n'a jamais été un TCD.
Noms de champs incorrects ou avec préfixe manquant
Si le TCD affiche Somme de Ventes et que tu écris "Ventes" dans la formule, Excel retourne #REF!. Le nom doit correspondre exactement à ce qui est affiché dans le TCD, préfixe inclus. Une espace en trop ou une majuscule différente suffisent à générer l'erreur.
Solution : Clique sur une cellule du TCD depuis la barre de formule pour générer automatiquement la formule avec les noms exacts. Copie ensuite ces noms dans ta formule. Évite de les retaper à la main.
Référence relative au lieu de référence absolue
Si tu utilises A3 au lieu de $A$3 pour le paramètre tableau_croisé, la référence se décale quand tu copies la formule vers le bas ou vers la droite. La formule pointe alors vers des cellules vides ou hors du TCD et retourne #REF!.
Solution : Fixe toujours la référence au TCD avec des $ : $A$3 reste identique partout où tu copies la formule. Utilise F4 après avoir sélectionné la cellule pour basculer en référence absolue.
Erreur #REF! après une modification de structure du TCD
Si tu renommes un champ, supprimes une ligne de données ou changes la structure du TCD, les formules qui référencent les anciens noms ou positions retournent #REF!. Le TCD a changé, mais les formules pointent encore vers ce qui n'existe plus.
Solution : Après toute modification majeure du TCD, actualise le tableau croisé dynamique, puis vérifie chaque formule LIREDONNEESTABCROISDYNAMIQUE. Si un champ a été renommé, mets à jour le nom dans toutes les formules concernées.
Paires champ/élément incomplètes
Les paramètres champ et élément vont toujours par paires. Si tu passes "Région"; "Nord"; "Produit" sans l'élément correspondant au champ "Produit", Excel retourne une erreur car il attend un argument de plus.
Solution : Vérifie que chaque nom de champ est suivi immédiatement de sa valeur de filtre. Compte les arguments après les deux premiers : tu dois toujours en avoir un nombre pair.
Utilisation sur un tableau normal au lieu d'un TCD
LIREDONNEESTABCROISDYNAMIQUE fonctionne uniquement avec des tableaux croisés dynamiques. Si tu tentes de pointer vers un tableau Excel standard, la formule retourne une erreur car aucun TCD n'est détecté à cette adresse.
Solution : Pour extraire des données d'un tableau Excel normal, utilise RECHERCHEX, la combinaison INDEX/EQUIV ou SOMMEPROD selon le cas. Réserve LIREDONNEESTABCROISDYNAMIQUE exclusivement aux TCD.
LIREDONNEESTABCROISDYNAMIQUE vs RECHERCHEX vs INDEX/EQUIV vs SOMME.SI
Garde LIREDONNEESTABCROISDYNAMIQUE pour un seul cas : alimenter un rapport à partir d'un tableau croisé dynamique, où elle suit automatiquement chaque actualisation du TCD. Dès que ta source est un tableau Excel normal, elle ne fonctionne tout simplement pas.
Pour aller chercher une valeur dans une table de référence classique, passe à RECHERCHEX ou à INDEX/EQUIV ; et si tu veux juste additionner sur un critère unique, SOMME.SI reste la plus directe.
| Critère | LIREDONNEESTABCROISDYNAMIQUE | RECHERCHEX | INDEX/EQUIV | SOMME.SI |
|---|---|---|---|---|
| Source de données | Tableau croisé dynamique uniquement | Tableau Excel standard | Tout tableau | Tout tableau |
| Mise à jour automatique | Avec le TCD | Avec les données source | Avec les données source | Avec les données source |
| Nombre de critères | Illimité (paires) | Flexible (1 à plusieurs) | Flexible | 1 seul |
| Complexité de syntaxe | Moyenne (noms exacts requis) | Facile à moyenne | Élevée | Facile |
| Cas idéal | Rapports alimentés par un TCD | Recherche dans une table de référence | Recherche bidimensionnelle classique | Somme sur un seul critère |
Questions fréquentes
Quand tu cliques sur une cellule d'un tableau croisé dynamique depuis la barre de formule, Excel génère automatiquement une formule LIREDONNEESTABCROISDYNAMIQUE pour référencer cette donnée de façon dynamique. C'est très pratique pour récupérer les noms de champs exacts sans les taper à la main.
Va dans Fichier, puis Options, puis Formules, et décoche l'option « Utiliser la fonction LIREDONNEESTABCROISDYNAMIQUE pour les références de tableau croisé dynamique ». Tu pourras alors cliquer sur les cellules du TCD et obtenir une référence de cellule classique plutôt qu'une formule LIREDONNEESTABCROISDYNAMIQUE.
Si tu changes la structure du TCD (renommes un champ, supprimes des données, ajoutes un niveau), les formules LIREDONNEESTABCROISDYNAMIQUE peuvent retourner #REF!. Après chaque modification structurelle, actualise le TCD puis vérifie et mets à jour les formules concernées.
Non, LIREDONNEESTABCROISDYNAMIQUE fonctionne uniquement avec des tableaux croisés dynamiques. Pour extraire des données de tableaux normaux, utilise RECHERCHEX, INDEX/EQUIV ou SOMMEPROD selon le nombre de critères.
Les erreurs #REF! surviennent généralement quand un champ n'existe plus dans le TCD ou quand le nom est mal orthographié. Vérifie que tous les noms de champs correspondent exactement à ceux du TCD (préfixes inclus), et actualise le tableau croisé dynamique avant de vérifier tes formules. Pour sécuriser un rapport, entoure la formule de SIERREUR : =SIERREUR(LIREDONNEESTABCROISDYNAMIQUE(...); "") affiche une cellule vide en cas d'erreur.
Pour aller plus loin
Continue sur ta lancée après la fonction LIREDONNEESTABCROISDYNAMIQUE : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
