Tutoriels > Tutoriels & Astuces Excel > Tableaux Croisés Dyn. > TCD - Extraire les données avec LIREDONNEESTABCROISDYNAMIQUE()

TCD - Extraire les données avec LIREDONNEESTABCROISDYNAMIQUE()

Publié par Mth le 02-07-2012 (119262 lectures)

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:

 

Extraire des données d'un Tableau Croisé Dynamique



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:

 

Extraire des données d'un Tableau Croisé Dynamique avec la fonction LIREDONNEESTABCROISDYNAMIQUE()

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:

 

Extraire des données d'un Tableau croisé dynamique avec la fonction LIREDONNEESTABCROISDYNAMIQUE()

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))
 

 

Alerte 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:

 

Extraire des données d'un tableau Croisé Dynamique avec la fonction LIREDONNEESTABCROISDYNAMIQUE()

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:

 

Extraire des donées dun Tableau Croisé Dynamique avec la fonction LIREDONNEESTABCROISDYNAMIQUE()

 

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:

 

Champs date et fonction LIREDONNEESTABCROISDYNAMIQUE()

 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:

 

Champs date et heure et fonction LIREDONNEESTABCROISDYNAMIQUE()

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.

 

 

 Trait bleu

 

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.

 

Exercices corrigés sur la fonction LIREDONNEESTABCROISDYNAMIQUE()

 

Téléchargement du fichier d'exercices corrigés

 

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

Pour toutes vos questions ou si vous rencontrez des difficultés, n'hésitez pas à rejoindre nos Forums de Discussions !

 

Autres articles dans cette catégorie Publié le Vues
TCD - Les Options d'un Tableau Croisé Dynamique 30-12-2012 99105
TCD - Trier un Tableau Croisé Dynamique 30-12-2012 82448
TCD - Les Filtres: Textes, Dates ou Valeurs 03-09-2012 62341
TCD - Les Filtres : Généralités 03-09-2012 46925
TCD - Les Filtres : Le filtre du Rapport 03-09-2012 30623
TCD - Extraire les données avec LIREDONNEESTABCROISDYNAMIQUE() 02-07-2012 119263
TCD - Grouper des champs Date dans un Tableau Croisé Dynamique 18-03-2012 131147
TCD - Grouper des champs numériques dans un Tableau Croisé Dynamique 18-03-2012 60636
TCD - Grouper ou Dégrouper les éléments d'un Tableau Croisé dynamique 18-03-2012 44485
TCD - Effectuer deux calculs différents sur un même champ 03-01-2012 38234
TCD - Actualiser un TCD 16-04-2011 67044
TCD - Source de données dynamique 17-10-2010 133008
TCD - Faire glisser / déplacer les champs 20-06-2010 54802
TCD - Créer un élément calculé 25-05-2010 64282
TCD - Intégrer un champ calculé 14-05-2010 221652
Mon Premier Tableau Croisé Dynamique avec Excel 2007 28-03-2010 131672
Comment reconstituer une base à partir d'un TCD ? 12-12-2009 23067
Passeport Pourcentages 03-09-2007 11818