Editions ENI Livres | Vidéos | e-Formations
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.
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.
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é.
Modifiez le deuxième champ en vous basant sur la capture ci-dessous.
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 :
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.
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.
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 :
Les premières lignes de la feuille RELEVEHEURES :
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.
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))
Validez par OK puis Fermer.
Le deuxième argument NBVAL(Liste!$A$1:$L$1) calcule le nombre...
