Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
💥 1 livre papier acheté 
= la version en ligne automatiquement offerte. Cliquez ici
  1. Livres et vidéos
  2. Analysez efficacement vos données
  3. Calculs, fonctions et filtres avancés
Extrait - Analysez efficacement vos données à l'aide des tableaux croisés dynamiques (3e édition)
Extraits du livre
Analysez efficacement vos données à l'aide des tableaux croisés dynamiques (3e édition)
2 avis
Revenir à la page d'achat du livre

Calculs, fonctions et filtres avancés

Introduction

Nous avons déjà étudié comment regrouper, filtrer des informations, et comment insérer un champ calculé dans un tableau croisé dynamique. Le présent chapitre va vous permettre d’approfondir ces techniques. Dans ce but nous allons utiliser plusieurs données sources :

  • Une base de données de facturation : Facturation.xlsx.

  • Une liste des ventes journalières d’une importante confiserie : Confiserie.xlsx.

  • Une liste de stages réalisés par un organisme de formation : ListeStages.xlsx

Connaissances nécessaires

Techniques de conception des tableaux croisés dynamiques

Nouveaux acquis

Champs calculés

Éléments calculés

La fonction LIREDONNEESTABCROISDYNAMIQUE

Partager des segments

Filtrer, grouper et calculer des pourcentages

 Ouvrez le fichier Facturation.xlsx.

images/c6-01.PNG

La feuille Factures de ce classeur contient les informations relatives à chaque facture émise par une société sur deux années (2015 et 2016). Nous considérerons ici que nous sommes en 2017 et que notre source est fixe puisque nous n’émettrons plus de factures relatives à 2015 ou 2016.

Notre objectif est d’effectuer des comparatifs annuels.

Chiffre d’affaires annuel par secteur

Pour un gestionnaire, une des premières informations à connaître est le taux de variation en pourcentage du chiffre d’affaires d’un secteur par rapport au chiffre réalisé par ce même secteur l’année précédente.

Pour obtenir une lisibilité optimale, il est souhaitable d’adopter une disposition regroupant les chiffres par secteurs et, à l’intérieur des secteurs, détaillés par année.

 Insérez le tableau croisé dans une nouvelle feuille en vous référant à l’écran qui suit (ne faire glisser que les champs SECTEUR et DATE FACTURE dans la zone Lignes)  :

images/c6-02.PNG

Les premières lignes du tableau croisé sont représentées ci-dessous.

images/c6-03.PNG

 Finalisez le tableau croisé en modifiant les étiquettes et en formatant les nombres.

images/C6-04.PNG

Si vous effectuez cette statistique en cours d’année, paramétrez une plage source dynamique afin de pouvoir intégrer aisément les nouvelles factures dans votre tableau croisé dynamique.

Il faut toujours...

Les éléments calculés

Le classeur Confiserie.xlsx va nous permettre d’insérer des éléments calculés dans notre tableau croisé.

Responsable d’une confiserie, vous devez suivre les chiffres d’affaires de vos commerciaux. La feuille VENTES de ce classeur a été extraite de votre gestion commerciale.

images/c6-15.PNG

Quatre commerciaux vendent vos produits :

  • CROUZET

  • FRAISSE

  • MARTY

  • SICARD

Les produits fabriqués sont :

  • CHOCOLATS

  • PATE D’AMANDE

  • PATE DE FRUITS

  • NOUGATS

Vos clients sont :

  • HYPER (Hypermarchés)

  • SUPER (Supermarchés)

  • BOUTIQUES

Le tableau croisé que nous allons construire est très simple, nous allons calculer le chiffre d’affaires de chaque commercial par type de client.

 Élaborez le tableau croisé dynamique puis renommez les étiquettes et formatez les nombres en vous référant à l’écran ci-dessous :

images/C6-16.PNG

Notre but est de définir, pour l’année prochaine, les objectifs de nos commerciaux pour chaque type de client.

L’objectif se calcule par rapport au chiffre d’affaires réalisé cette année par le commercial pour chaque catégorie de clients :

  • BOUTIQUES +5 %

  • HYPER +10 %

  • SUPER +8 %

Un champ calculé permet d’effectuer des calculs entre différents champs d’un tableau croisé dynamique. Un élément...

La fonction LIREDONNEESTABCROISDYNAMIQUE

Objectif

Lors d’entretiens individuels avec chacun de vos commerciaux, vous faites le point avec eux sur les ventes réalisées.

Vous ne souhaitez pas leur montrer les chiffres réalisés par chacun des trois autres commerciaux mais tenez par ailleurs à leur préciser quelle est la part de leur travail par rapport au chiffre d’affaires total de la société.

Afin d’étudier l’utilisation de la fonction LIREDONNEESTABCROISDYNAMIQUE, nous allons utiliser la même base source que précédemment, le fichier Confiserie.xlsx.

 Concevez ce tableau croisé dans une nouvelle feuille puis renommez les étiquettes et formatez les nombres en vous référant à l’écran suivant.

images/EcranP171.png

 Renommez la feuille créée STAT.

Si vous leur présentez le tableau croisé dynamique tel qu’il est conçu, chacun aura connaissance des chiffres de ses collègues.

Il nous faut donc construire un tableau qui va extraire les données du tableau croisé dynamique. En sélectionnant le nom d’un commercial à l’aide d’une liste déroulante, seules les données relatives à ce commercial doivent apparaître.

Un petit graphique vous aidera dans votre présentation.

Le tableau et le graphique à concevoir sont présentés ci-après.

images/C6-35.PNG

 Saisissez les premiers éléments du tableau dans la feuille STAT.

images/Chap6p174.png

Paramétrez en J3 la liste déroulante permettant de sélectionner un commercial.

 Positionnez votre curseur en J3.

 Dans l’onglet Données, groupe Outils de données, cliquez sur le bouton Validation des données.

 Dans la zone Autoriser de la boîte de dialogue...

Partager un segment entre plusieurs tableaux croisés

Objectif

Lorsque vos analyses nécessitent la conception de plusieurs tableaux croisés dynamiques, vous souhaiterez probablement qu’un filtre appliqué à un tableau s’applique automatiquement à tous les autres.

Partager un segment entre différents tableaux croisés est simple à mettre en œuvre, ceci revient à créer une connexion du segment vers un tableau croisé dynamique. Lorsque vous modifiez le segment partagé, les tableaux croisés connectés sont automatiquement mis à jour.

Il existe donc deux types de segments utilisables :

  • Les segments locaux (connectés à un seul tableau croisé).

  • Les segments partagés (connectés à plusieurs tableaux).

Un tableau croisé dynamique peut contenir des segments locaux et des segments partagés.

Nous avons déjà mis en pratique dans cet ouvrage la méthode de création d’un segment. Pour étudier le partage d’un segment, il nous faut créer plusieurs tableaux croisés.

 Ouvrez le classeur ListeStages.xlsx.

Il recense dans une feuille nommée Stages les différents stages réalisés par un organisme de formation.

images/c6-36.PNG

Conception des tableaux croisés

 Concevez les quatre tableaux croisés dans la feuille TCD en vous référant...