Blog ENI : Toute la veille numérique !
-25€ dès 75€ sur les livres en ligne, vidéos... avec le code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. Tableaux de synthèse et tableaux de bord
  3. Mise en forme des résultats
Extrait - Tableaux de synthèse et tableaux de bord Traitez et analysez de gros volumes de données avec Excel 2016
Extraits du livre
Tableaux de synthèse et tableaux de bord Traitez et analysez de gros volumes de données avec Excel 2016
1 avis
Revenir à la page d'achat du livre

Mise en forme des résultats

Introduction

Nous venons de voir comment aboutir à des résultats statistiques fiables à l’aide de sous-totaux ou de formules variées. La présentation de ces informations est essentielle afin que les points importants soient mis en valeur. Nous verrons dans le chapitre Tableaux de bord et indicateurs comment mettre en œuvre des indicateurs graphiques dans vos tableaux de bord, le présent chapitre va vous permettre d’appliquer des techniques de mise en forme variées à vos résultats.

 Ouvrez le classeur BaseChap5.xlsx.

Ce classeur contient déjà un ensemble de feuilles contenant des données et les tableaux de statistiques associés. Les calculs étant déjà réalisés, vous allez pouvoir vous consacrer uniquement à la mise en forme des résultats.

Vont être abordées les fonctionnalités suivantes :

  • Mises en forme conditionnelles

  • Sparklines

  • Graphiques

  • Formule faisant apparaître un indicateur

Statistiques de dépenses de sections de club

Mise en forme des valeurs

La feuille StatSports du classeur comprend deux tableaux de synthèse relatifs aux dépenses des différentes sections d’un club d’arts martiaux.

Le tableau des colonnes A et B calcule les dépenses cumulées et celui des colonnes D à O les dépenses mensuelles à partir des saisies effectuées dans la feuille DEPENSES1.

images/Chap5-001.PNG

La formule placée en B5 est : =SOMME.SI(DEPENSES1!$B$2:$B$1000;StatSports!A5;DEPENSES1!$C$2:$C$1000)

Cette formule a été recopiée vers le bas et vers la droite jusqu’à la cellule O10.

La formule placée en D5 est une formule matricielle : {=SOMME((DEPENSES1!$B$2:$B$1000=StatSports!$A5)*(MOIS(DEPENSES1!$A$2:$A$1000)=StatSports!D$4)*(DEPENSES1!$C$2:$C$1000))}

Notre premier travail va consister à placer en police de couleur rouge les dépenses mensuelles dont le montant est supérieur à 150 euros.

Excel nous offre la possibilité d’appliquer une mise en forme conditionnelle aux données de nos tableaux. Cette option permet de paramétrer un format de mise en forme qui s’appliquera aux valeurs affichées en fonction d’un ou plusieurs critères.

 Avant de débuter, sélectionnez la plage D5 à O10.

 Onglet Accueil - groupe Styles, cliquez sur le bouton Mise en forme...

Dépenses personnelles

Mise en forme des valeurs

La feuille STATDEP du classeur comprend un tableau de synthèse relatif aux dépenses par postes d’un foyer.

La formule placée en C4 est : =SOMME.SI.ENS(DEPENSES2!$D$2:$D$500;DEPENSES2!$B$2:$B$500;STATDEP!$A4;DEPENSES2!$C$2:$C$500;STATDEP!C$3)

Cette formule a été recopiée vers le bas puis vers la droite.

Un budget de dépenses prévisionnel a été établi en début d’année et notre travail va consister à placer sur fond rouge les noms des postes dont les dépenses totales sont supérieures au budget prévu.

images/Chap5-014.PNG

La manipulation à effectuer maintenant est légèrement différente de la technique mise en œuvre précédemment. En effet, dans l’exercice précédent, nous avions appliqué des mises en forme conditionnelles directement aux cellules concernées en fonction de leurs valeurs. Ici la couleur rouge doit être appliquée à une cellule de la colonne A en fonction de la comparaison des données contenues en colonnes B et E. La mise en forme ne dépend donc pas de la cellule elle-même mais d’autres cellules.

L’utilisation d’une forme de calcul va donc être nécessaire.

 Sélectionnez la plage des postes A4 à A14.

 Onglet Accueil - groupe...

Suivi de commerciaux

Mise en forme des valeurs

La feuille STATASSUR du classeur comprend une synthèse des contrats vendus par des commerciaux en assurances. Ces statistiques sont obtenues à partir des données de la feuille ASSURANCES.

images/Chap5-022.PNG

La formule placée en B14 est : =SOMME.SI.ENS(ASSURANCES!$E$2:$E$2000;ASSURANCES!$C$2:$C$2000;STATASSUR!$A14;ASSURANCES!$B$2:$B$2000;STATASSUR!B$13)

Cette formule a été recopiée vers le bas puis vers la droite pour chaque commercial et chaque produit.

Afin de suivre nos commerciaux nous allons placer sur fond rouge dans le deuxième tableau les chiffres d’affaires inférieurs à l’objectif correspondant.

 Sélectionnez la plage de chiffres d’affaires réalisés : B14 à D19.

 Onglet Accueil - groupe Styles, cliquez sur le bouton Mise en forme conditionnelle.

 Cliquez sur Nouvelle règle.

 Dans la boîte de dialogue Nouvelle règle de mise en forme, sélectionnez la dernière option Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.

 Saisissez la formule =B14<B4.

images/Chap5-023.PNG

 Cliquez sur le bouton Format.

 Onglet Remplissage, sélectionnez la couleur Rouge puis terminez deux fois par OK.

Les chiffres d’affaires inférieurs à l’objectif sont maintenant affichés sur fond rouge.

images/Chap5-024.PNG

Cette mise en forme conditionnelle a été appliquée sur le détail des chiffres d’affaires. Nous allons maintenant appliquer quelques indicateurs visuels sur les pourcentages de notre troisième tableau.

En fonction du pourcentage du chiffre d’affaires réalisé par rapport à l’objectif, trois indicateurs vont être définis :

  • Pourcentage supérieur ou égal à 90 % : icône verte

  • Pourcentage supérieur à 80 % et inférieur à 90 % : icône orange

  • Pourcentage inférieur ou égal à 80 % : icône...

Gestion d’heures

Présentation

La feuille GESTTEMPS intègre une liste de durées horaires. Chaque jour, chacun de vos collaborateurs saisit la répartition de son temps de travail entre la réalisation de devis, la facturation, la participation à des réunions et les visites chez des clients.

Dans la feuille STATGESTTEMPS a été conçu un tableau de synthèse totalisant les temps de travail par collaborateur et par type d’activité.

Les premières lignes de la feuille GESTTEMPS :

images/Chap5-039.PNG

Le tableau de la feuille STATGESTTEMPS :

images/Chap5-040.PNG

La formule placée en B4 est : =SOMME.SI.ENS(GESTTEMPS!$D$2:$D$1000;GESTTEMPS!$C$2:$C$1000;STATGESTTEMPS!$A4;GESTTEMPS!$B$2:$B$1000;STATGESTTEMPS!B$3)

Cette formule a été recopiée vers le bas puis vers la droite pour chaque collaborateur et chaque type d’activité.

Aucune mise en forme n’a été appliquée aux résultats. La première action à effectuer est de formater les résultats au format horaire. Les nombres tels qu’affichés dans le tableau correspondent à un nombre de jours. En cours d’année, nos collaborateurs vont bien sûr dépasser les 24 heures par activité. Il faudra donc veiller à paramétrer un format qui permet de dépasser les 24 heures.

Paramétrage du format horaire...

Suivi de chantier de construction

Présentation

Vous dirigez une entreprise de construction dans le secteur du bâtiment. La feuille SuiviChantier intègre une liste de dépenses relatives à un chantier de construction. Les dépenses se répartissent en trois catégories : MO (main d’œuvre), matériaux et honoraires. Lorsque vous avez établi le devis, vous avez établi un prévisionnel des dépenses par postes.

Les premières lignes de la feuille SuiviChantier :

images/Chap5-045.PNG

Les tableaux de la feuille StatChantier :

images/Chap5-046.PNG

La formule placée en D5 est : =SOMME.SI(SuiviChantier!$B$2:$B$1000;StatChantier!A5;SuiviChantier!$C$2:$C$1000)

Cette formule a été recopiée vers le bas.

Afin de visualiser comment se situent les totaux des dépenses de chaque catégorie par rapport au budget prévisionnel, nous allons intégrer dans les cellules de la colonne D des barres de progression.

En outre, si pour un poste le coût prévisionnel a été dépassé, nous ferons apparaître dans la colonne E des indicateurs graphiques rouges. Le nombre de points rouges est relatif à la valeur du dépassement : si les coûts cumulés dépassent le budget de 50 % à 59 %, cinq points doivent être affichés, si les coûts cumulés dépassent le budget de 20 % à 29 %, deux points doivent apparaître.

images/Chap5-055.PNG

Insérer une barre de données dans les cellules

La mise en forme conditionnelle que nous allons appliquer doit prendre en compte pour chacune des lignes le prévisionnel correspondant....