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. Fonctionnalités avancées
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

Fonctionnalités avancées

Introduction

Maintenant que vous êtes familiarisé avec les techniques de conception des tableaux croisés dynamiques, nous allons approfondir ces connaissances. Le présent chapitre va vous permettre de mettre en pratique les techniques avancées ; afin de disposer de cas concrets et variés, nous allons utiliser quatre fichiers sources :

GestionStocks.xlsx avec lequel vous allez construire des statistiques sur les stocks de matières premières par numéro de lots.

SuiviProduction.xlsx qui recense les données de production d’un site de fabrication de salades salées.

HorairesEntrees-Sorties.xlsx qui inventorie les heures d’entrée-sortie d’agents travaillant dans une zone confinée.

Concurrents.xlsx : qui répertorie des concurrents sportifs participant régulièrement à des courses nationales.

Ces quatre tableaux vont vous permettre de concevoir des tableaux croisés dynamiques variés.

Connaissances nécessaires

Techniques de conception des tableaux croisés dynamiques

Nouveaux acquis

Champs calculés

Utilisation de plages dynamiques

Groupements chronologiques

Gestion de sources multiples

Gestion des relations

Calculs sur les heures

Classements par tranches

Suivi de stocks matières premières

Synthèses et calculs

 Ouvrez le fichier GestionStocks.xlsx.

images/CaptureP106.PNG

La feuille SuiviStock de ce classeur enregistre tous les mouvements de stocks (en kg) relatifs aux matières premières utilisées dans une usine de fabrication de gâteaux incorporant des fruits secs.

La gestion des stocks est à effectuer par matière première avec un sous-détail par numéro de lots.

Total des entrées-sorties par matière et numéro de lot

 À partir de la feuille SuiviStock, concevez le tableau croisé en vous basant sur la boîte de dialogue Champs de tableau croisé dynamique présentée ci-après.

images/C5-02.PNG

Le tableau présente les mouvements de stock par produit, puis détaillés par lot.

En faisant glisser les champs QTE ENTREE et QTE SORTIE dans la zone ∑ VALEURS, Excel utilise automatiquement la fonction Nombre car ces champs comportent des cellules vides. Si les cellules vides avaient été remplacées par des zéros, la fonction Somme aurait été appliquée.

 Modifiez pour ces deux champs les Paramètres des champs de valeurs, ouvrez la liste du premier champ et cliquez sur l’option Paramètres des champs de valeurs.

 Sélectionnez la fonction Somme et saisissez QTE ENTREE TOTALE dans la zone Nom personnalisé.

images/C5-03.PNG

 Modifiez le deuxième champ en vous basant sur la capture ci-dessous.

images/C5-04.PNG

 Modifiez enfin le libellé de l’étiquette des lignes : Produits - N° Lot.

Les premières lignes du tableau inséré sont représentées ci-après :

images/CaptureP109.PNG

Le total général indique les poids totaux de matières premières entrées et sorties. Nous allons considérer ici que ceci n’est pas forcément significatif car nous additionnons des poids de produits différents.

 Réalisez un clic droit dans le tableau croisé puis cliquez sur l’option Options du tableau croisé dynamique ; dans l’onglet Totaux et filtres, décochez l’option Afficher les totaux des colonnes.

Une première analyse rapide nous montre que certains lots ont été entièrement consommés. L’intérêt est donc maintenant...

Suivi de production

Objectif

Vous êtes responsable production sur un site de fabrication de salades. Votre objectif est de :

  • Calculer les productions réelles annuelles de chacune des salades.

  • Connaître la production horaire moyenne par produits finis, calculée sur les données des deux années, afin d’affiner votre prix de revient.

  • Connaître le taux de perte moyen par produit.

  • Comparer la productivité par produit de l’année 2016 par rapport à 2015.

 Ouvrez le classeur SuiviProduction.xlsx.

Le classeur contient une feuille (PROD) incluant les relevés de production des deux années. Chaque ligne du tableau source détaille la fabrication d’une salade.

Ci-dessous sont présentées les premières lignes de la feuille PROD.

images/c5-26.PNG

Conception des tableaux d’analyse

Insertion des nouvelles formules

Avant de débuter nos calculs statistiques, des calculs préliminaires sont nécessaires. Nous devons calculer :

  • La durée de fabrication du produit.

  • La production réelle (sans les pertes).

  • La production horaire.

 Ajoutez dans la feuille les colonnes et formules détaillées ci-dessous.

images/c5-27.PNG

DUREE : =C2-B2

PROD REELLE : =E2-F2

PROD HORAIRE : =J2/I2/24 (à formater au format Nombre - Séparateur de milliers)

 Recopiez les formules vers le bas jusqu’à la dernière ligne du tableau.

Productions réelles annuelles

Construisons le premier tableau croisé : productions réelles annuelles...

Horaires d’entrées-sorties

Définition des plages dynamiques

 Ouvrez le fichier HorairesEntrees-Sorties.xlsx.

Ce classeur contient deux feuilles. La feuille AGENTS présente la liste des personnes autorisées à travailler en zone confinée. La feuille RELEVEHEURES est extraite d’un fichier de pointage qui retrace les heures d’entrées et de sorties de chaque agent dans la zone confinée.

La zone confinée dans laquelle interviennent les personnes a été mise en service le 1er janvier 2017. Vous devez suivre en temps réel le temps cumulé passé en zone confinée par chaque agent, afin de ne plus faire intervenir les agents qui auraient dépassé leur quota d’heures autorisées.

Les premières lignes de la feuille AGENTS :

images/C5-40.PNG

Les premières lignes de la feuille RELEVEHEURES :

images/c5-41.PNG

Le nombre de lignes de la feuille RELEVEHEURES va augmenter chaque jour et de nouveaux agents peuvent être amenés à intervenir. Notre intérêt est donc de paramétrer deux plages dynamiques.

AGENTS :

 Dans l’onglet Formules, groupe Noms définis, cliquez sur le bouton Gestionnaire de noms, ou bien utilisez le raccourci CtrlF3.

 Dans la boîte de dialogue Gestionnaire de noms, cliquez sur le bouton Nouveau.

Entrez le nom de la plage (ListeAgents) puis saisissez la formule dans la zone Fait référence à :

=DECALER(AGENTS!$A$1;0;0;NBVAL(AGENTS!$A$1:$A$200);2)

 Validez par OK.

HORAIRES :

 Dans la boîte de dialogue Gestionnaire de noms, cliquez sur le bouton Nouveau.

Entrez le nom de la plage (Horaires) puis saisissez la formule dans la zone Fait référence à :

=DECALER(RELEVEHEURES!$A$1;0;0;NBVAL(RELEVEHEURES!$A$1:$A$10000);5)

 Validez par OK.

 Cliquez sur Fermer.

Préparation du modèle de données

Le tableau croisé dynamique doit être conçu à partir de deux feuilles de calcul. Les données de ces deux feuilles sont en relation par l’intermédiaire du code de l’agent. Ce code est unique dans la feuille AGENTS. Dans la feuille RELEVEHEURES ce code est présent autant de fois que l’agent est entré/sorti. Cet ensemble de données constitue un modèle de données.

Un modèle de données...

Concurrents sportifs

Les données sources

 Ouvrez le fichier Concurrents.xlsx.

Chaque ligne de notre tableau source est relative à un concurrent et au nombre de points qu’il a acquis lors de différentes compétitions.

images/C5-66.PNG

Plage dynamique en lignes et en colonnes

De nouveaux concurrents pouvant être ajoutés, nous allons définir une plage dynamique pour ces données. Par sécurité, lorsque vous définissez une plage dynamique, nous vous conseillons de considérer que le nombre de lignes peut varier mais également le nombre de colonnes. Ceci vous permettra de ne pas avoir à redéfinir vos plages sources dès qu’une modification intervient.

La fonction DÉCALER va donc devoir prendre en compte une plage dont la hauteur et la largeur sont variables. Tenons compte ici d’un maximum de 5000 lignes et de 12 colonnes.

 Dans l’onglet Formules, groupe Noms définis, cliquez sur le bouton Gestionnaire de noms, ou bien utilisez le raccourci-clavier CtrlF3.

 Dans la boîte de dialogue Gestionnaire de noms, cliquez sur le bouton Nouveau.

 Entrez le nom de la plage (ListeConcurrents) puis saisissez la formule dans la zone Fait référence à :

=DECALER(Liste!$A$1;0;0;NBVAL(Liste!$A$1:$A$5000);NBVAL(Liste!$A$1:$L$1))

images/C5-67.PNG

 Validez par OK puis Fermer.

Le deuxième argument NBVAL(Liste!$A$1:$L$1) calcule le nombre...