La fonction LIREDONNEESTABCROISDYNAMIQUE () permet d'extraire des données de synthèse figurant dans un tableau croisé dynamique.

Excel offre plusieurs solutions permettant de synthétiser des données selon un ou plusieurs critères, comme les fonctions base de données (BDSOMME(), BDNB() …), ou des fonctions comme SOMMEPROD(), SOMME.SI(), etc...

S'appuyer sur un TCD et la fonction LIREDONNEESTABCROISDYNAMIQUE() est une solution très intéressante pour les grosses bases de données, tant pour sa facilité de mise en œuvre que pour sa puissance.

Syntaxe:

LIREDONNEESTABCROISDYNAMIQUE("champ_données", tableau_croisé, champ1, élément1, champ2, élément2, ...)

• champ_données écrit entre guillemets, est le nom  du champ de données contenant les données à extraire.

• tableau_croisé est la référence à une cellule, ou plage de cellules d'un tableau croisé dynamique.  Il s'agit généralement de l'adresse de la première cellule en haut à gauche du TCD.

• champ1, élément1, champ2, élément2... représentent de 1 à 14 paires de noms de champ et d'éléments décrivant les données à extraire. Les paires peuvent apparaître dans n'importe quel ordre. Les noms de champ et les noms d'éléments non numériques sont entre guillemets. 

Rapide à mettre en place:

Cette fonction est très rapide à mettre en place car elle peut se générer automatiquement sans aucune saisie, il suffit de sélectionner une cellule en dehors du TCD, inscrire le signe = et sélectionner une cellule du TCD:

Excel génère automatiquement cette formule:

=LIREDONNEESTABCROISDYNAMIQUE("Qt Solde";$L$3;"Délégué";"CARPENTIER";"LibArticle";"ANGLAIS ILLUSTRE")
 

Dans cet exemple elle renvoie 10, soit la somme du champ "Qt Solde" pour monsieur CARPENTIER du champ "Délégué", sur le produit "ANGLAIS ILLUSTRE" du champ "LibArticle", le tout issu du TCD situé cellule L3.

Pour bénéficier de cet automatisme, vérifiez que l'option Excel "Formules" "Utiliser les fonctions LIREDONNEESTABCROISDYNAMIQUE etc. " est bien cochée, comme expliqué sur ce lien.

Les données doivent être affichées dans le TCD:

Cette fonction remonte des données synthétisées figurant dans le TCD, il faut donc concevoir le TCD en fonction de ce que l'on souhaite obtenir comme information.

Dans l'exemple ci-dessus nous remontions la quantité réalisée par monsieur Carpentier pour le produit Anglais Illustré, imaginons que je veuille extraire cette quantité pour le produit "Anglais illustré" quelle que soit la personne, j'aurais tendance à écrire:

=LIREDONNEESTABCROISDYNAMIQUE("Qt Solde";$L$3;"LibArticle";"ANGLAIS ILLUSTRE")
 

Or, cette formule renvoie #REF!.

En effet, le total par libellé article n'est pas présent dans le TCD, pour obtenir cette donnée il faut que je la fasse apparaître dans le TCD par exemple comme ceci:

L'élément étant affiché dans le TCD, la formule ci dessous renvoie bien 51

=LIREDONNEESTABCROISDYNAMIQUE("Qt Solde";$L$3;"LibArticle";"ANGLAIS ILLUSTRE") 
 

La fonction peut faire référence à des cellules:

Comme toutes les fonctions, LIREDONNEESTABCROISDYNAMIQUE() peut faire référence à des valeurs situées dans des cellules afin de pouvoir effectuer des copier/coller, par exemple à partir du TCD ci-dessous, je veux remplir l'information Qt Solde du tableau de droite:

La formule en Z5 peut être écrite ainsi pour faire référence aux libellés inscrits dans la colonne Y:

=LIREDONNEESTABCROISDYNAMIQUE("Qt solde";$T$3;"Délégué";$Y4)
 
 

Si un délégué n'est pas présent dans le TCD, la formule renverra #REF!.

Pour tester cette situation et afficher par exemple 0 dans ces cas là, on utilise souvent la fonction SIERREUR(), ce qui donnerait:

=SIERREUR(LIREDONNEESTABCROISDYNAMIQUE("Qt solde";$T$3;"Délégué";$Y4);0)
 

Remarque: pour les versions antérieures à 2007, tester à l'aide des fonction SI() et ESTERREUR():

=SI(ESTERREUR(LIREDONNEESTABCROISDYNAMIQUE("Qt solde";$T$3;"Délégué";$Y4));0;LIREDONNEESTABCROISDYNAMIQUE("Qt solde";$T$3;"Délégué";$Y4))
 

 La fonction LIREDONNEESTABCROISDYNAMIQUE n'est pas sensible à la casse mais comme toutes les fonctions, sensible à l'orthographe y compris aux accents, les libellés utilisés doivent donc être strictement identiques à ceux figurant dans le TCD.

La fonction T() souvent en appui pour faire référence au libellé d'un champ de valeurs:

A partir du même exemple ci-dessus, je souhaite remplir ce tableau avec une seule et même formule:

J'inscris cette formule en Z4:

=LIREDONNEESTABCROISDYNAMIQUE(Z$3;$T$3;"Délégué";$Y4)
 

 
Cette formule me renvoie #REF! Excel a du mal à interpréter le libellé en cellule Z3, qui pourtant est juste et correspond exactement au libellé figurant dans le TCD.

La petite fonction T() renvoie le texte auquel son argument fait référence, au cas où Excel aurait du mal à interpréter automatiquement ce texte. La formule devient:

=LIREDONNEESTABCROISDYNAMIQUE(T(Z$3);$T$3;"Délégué";$Y4)
 

 
Je peux maintenant la copier coller dans tout le tableau pour obtenir mon résultat:

Le cas des champs Date

Lorsqu'un élément fait référence à une date, il est préférable de présenter la valeur sous forme de numéro de série ou d'utiliser une fonction DATE(), c'est d'ailleurs ce que propose spontanément la fonction LIREDONNEESTABCROISDYNAMIQUE().

Dans l'exemple ci-dessous la recherche de la valeur correspondant au 3 mars 2012 peut s'écrire de différentes manières:

 Première solution proposée automatiquement par la fonction:

=LIREDONNEESTABCROISDYNAMIQUE("Relevé";$B$14;"Date";DATE(2012;3;3))

Il est possible d'opter pour un numéro de série à la place de la fonction DATE():

=LIREDONNEESTABCROISDYNAMIQUE("Relevé";$B$14;"Date";40971)

Rien n'empêche d'écrire la formule ci-dessous:

=LIREDONNEESTABCROISDYNAMIQUE("Relevé";$B$14;"Date";"03/03/2012")

... mais attention … dans ce dernier cas la formule ne fonctionnera pas si le classeur est ouvert sur une machine dont les paramètres régionaux sont différents, raison pour laquelle il est recommandé de ne pas utiliser cette méthode.

Même principe avec des dates et des heures, par exemple ci-dessous pour extraire la valeur correspondant au 22/04/2012 à 18h48m14s:

on peut utiliser le numéro de série avec décimales pour les heures, ou les fonctions DATE() et HEURE() ou TEMPS(), la fonction génère automatiquement ceci:

=LIREDONNEESTABCROISDYNAMIQUE("Relevé";$B$2;"Heure";DATE(2012;4;22)+TEMPS(18;48;14))

En cas de référence à un tableau externe

La fonction LIREDONNEESTABCROISDYNAMIQUE() est conçue pour fonctionner avec un TCD du même classeur ou d'un autre classeur du moment qu'il est ouvert, elle affiche #REF! lorsqu'elle fait référence à un tableau croisé dynamique situé dans un autre fichier qui est fermé.

Une façon de contourner le problème si l'on ne peut pas garder le fichier source ouvert, consiste à  placer la fonction LIREDONNEESTABCROISDYNAMIQUE() quelque part dans le même fichier que le TCD et faire référence à la cellule qui contient le résultat dans le fichier de destination.

Pour voir d’autres exemples et vous entraîner à manipuler la fonction LIREDONNEESTABCROISDYNAMIQUE(), le fichier Excel accompagnant cet article vous présente cinq  exercices corrigés de difficulté croissante abordant les cas les plus courants d'utilisation de cette fonction.

… et s'il vous reste des questions, rejoignez-nous dans les Forums.