Comment créer un tableau qui s’actualise et identifie les meilleures performances sur Excel !

comment créer un tableau qui s'actualise et identifier automatiquement les meilleures performances sur Excel bonjour à tous c'est Alex d'excelformation.f et dans ce tutoriel je vais vous montrer comment créer un tableau qui va automatiquement s'actualiser dès que nous allons ajouter des données à la suite d'une base donc ici nous allons par exemple ajouter les ventes du mois d'octobre qui s'élèv à 1000 € afin que celle-ci s'ajoute automatiquement sur le graphique nous verrons également comment faire apparaître de manière dynamique les extrêmes c'est-à-dire la valeur max maximale et minimale pour cela nous allons voir que si nous modifions la valeur du mois d'octobre que nous venons d'insérer pour que les ventes correspondent maintenant à 2500 alors automatiquement cette barre sera mise en vert étant donné qu'il s'agit de la valeur la plus importante sur ce graphique en revanche si nous avions réalisé 300 € de vente alors ça aurait été la valeur la plus faible et celle-ci aurait été mise en évidence en rouge alors vous trouverez très régulièrement de nouvelles vidéos sur la chaîne alors pensez à vous abonner et surtout à activer la clochette de notification afin d'être sûr de ne manquer aucune astuce et de devenir rapidement un [Musique] prodexcel pour rappel en ce moment sur excelformation.fr c'est le blackfiday et donc si vous voulez obtenir la quasi totalité de mes formations et de mes applications à impricacé rendez-vous directement sur excelformation.fr vous trouverez le lien dans la description de la vidéo et comme d'habitude si vous voulez être sûr de bien comprendre tout ce que nous allons voir dans cette vidéo vous pouvez télécharger le support de ce tutoriel en suivant le lien également disponible dans la description de la vidéo qui vous permettra de télécharger le support de cette vidéo ainsi que son corrigé afin de pouvoir réaliser toutes les opérations en même temps que moi voilà donc comme nous l'avons vu dans l'introduction de la vidéo nous allons maintenant voir comment créer un tableau de bord qui a pour particularité de s'actualiser automatiquement en fonction des lignes que nous insérons dans un tableau et en plus d'identifier automatiquement la meilleure et la moins bonne performance de l'ensemble des données du tableau et donc pour illustrer ce tutoriel nous avons actuellement à l'écran un exemple avec le tableau des ventes d'une entreprise mois par mois et donc pour commencer nous allons créer des plages nommées dynamiques à partir de ces données qui vont nous permettre d'automatiser l'extension de nos plages de données en garantissant également que notre tableau reste dynamique et adaptable nous avons déjà eu l'occasion de traiter en détail cette notion de plage nommé dynamique dans un tutoriel précédent sorti il y a quelques semaines et si vous souhaitez approfondir cette notion je vous invite à consulter cette vidéo pour obtenir une explication plus poussée de la fonction décalée et de ses applications pratiques et donc pour faire simple une plage nommée est tout simplement une section de la feuille de calcul à laquelle nous attribuons un nom spécifique afin de pouvoir y faire référence plus facilement dans les formules ou les graphiques cette méthode nous évite donc d'avoir à sélectionner manuellement les plages de cellules ce qui sera surtout pratique lorsque les données vont évoluer de manière très fréquente donc en créant des plages dynamique nous allons ajouter une dimension d'automatisation supplémentaire à nos données et dès qu'une nouvelle donnée est saisie dans une table alors la plage va s'étendre automatiquement pour inclure ces nouvelles lignes ce qui va donc amener un gain de temps important et surtout lorsque nous serons amenés à manipuler de grandes quantités de données voilà donc pour créer une plage nomée dynamique nous allons nous appuyer donc comme je vous l'expliqué juste avant sur la fonction décalé qui permet de définir une plage de cellule qui va s'ajuster automatiquement en fonction des entrées que nous allons spécifié donc pour créer notre plage dynamique nous pouvons utiliser le raccourci clavier contrô F3 et ensuite nous allons cliquer sur le bouton Nouveau une fois que le gestionnaire des noms a bien été lancé ici nous allons donner un nom à notre plage nous allons par exemple ici l'appeler n vente étant donné que celle-ci va reprendre l'ensemble des ventes mensuelles de notre tableau ensuite donc dans le champ fait référence a nous tapons le symbole égal pour informmer Excel qu'il s'agit effectivement d'une plage dynamique qui va reposer sur l'utilisation d'une formule ex cette formule donc qui utilise la fonction décalée pour rappel la fonction décalée attend cinq arguments le premier de ces arguments et référence il s'agit de la cellule de départ à laquelle Excel va commencer à générer la plage de cellule ici nous allons sélectionner la cellule B9 qui est la première cellule dans laquelle nous avons des données ensuite nous allons définir que cette plage de départ ne doit jamais bougé donc les deux paramètres suivants ligne et colonne seront égales à ZrO et donc là nous nous en arrivons à l'argument qui va véritablement nous intéresser l'argument hauteur qui définit la hauteur de la plage de cellule à généré ici donc plage de cellule sera retourné par la fonction nbval qui va analyser combien de cellules contiennent une valeur à l'intérieur d'une plage de cellule ici cette plage de cellule va commencer à partir de la cellule B9 et nous allons donc l'étendre bien en dessous afin de pouvoir prendre en compte des futurs enregistrements que nous pourrions saisir dans notre tableau cela étant fait nous pouvons fermer l'apparence de la fonction NBV et ensuite donc nous allons spécifier que la largeur c'est-à-dire le nombre de colonnes de la page de cellule doit être égal à une étant donné que nous voulons extraire uniquement une colonne de notre tableau cela étant fait nous fermons la parenthèse finale et lorsque nous cliquons sur OK nous avons maintenant notre plage nommée dynamique qui va s'ajuster automatiquement en fonction du contenu du tableau VO donc maintenant que nos plages nommées sont en place nous allons créer le graphique qui va afficher les données de manière visuelle et dynamique pour cela nous sélectionnons toutes les cellules de notre tableau puis nous allons nous rendre dans le menu Insertion afin de nous rendre maintenant dans le groupe graphique dans lequel sont répartis tous les types de graphiques ici nous allons insérer par exemple un graphique en colonne donc nous déroulons histogramme et nous choisissons d'insérer un histogramme 2 D de cette manière nous trouvons effectivement le graphique dans lequel chacune des barres représente les ventes réalisé par l'entreprise pour un mois donné si nous le souhaitons nous pouvons bien entendu personnaliser l'apparence de ce graph graphique en choisissant par exemple l'un des styles proposés directement dans le menu création de graphique ici nous allons par exemple opter pour le style numéro 9 avant d'aller plus loin nous allons vouloir créer une deuxième plage dynamique qui va nous permettre d'obtenir la liste des mois qui sajustera elle aussi en fonction du contenu de notre tableau donc nous appuyons sur contrôle F3 nous sélectionnons la formule que nous avons créée juste avant et nous la copions en appuyant sur contrôle C de cette manière si nous appuyons sur nouveau nous pouvons créer la plage n moi puis nous allons modifier la formule sur laquelle il va nous suffire de modifier uniquement le début en remplaçant la formule de départ B9 par la cellule A9 dans laquelle se trouve le mois de janvier ensuite nous cliquons sur OK et nous validons et maintenant nous allons pouvoir utiliser ces deux plages dynamiques directement comme source de notre graphique pour cela nous le sélectionnons puis nous nous rendons dans création de graphique et nous cliquons ensuite sur Sélectionner les données pour commencer nous allons modifier les étiquettes en cliquant donc sur le bouton Modifier nous supprimons la plage des cellules A9 jusqu'à A14 et à la place nous saisissons n moi qui est le nom que nous venons de créer à l'instant alors attention comme vous l'avez vu il faut bien préciser également le nom de la feuille de calcul sinon cela ne marchera pas nous validons une seconde fois et maintenant nous allons modifier la plage des cellules qui correspond au ventes comme vous vous en doutez ici il s'agit de la plage nvente que nous avons également créé juste avant donc nvente si jamais vous avez oublié le nom exact de la plage dynamique que vous avez créé pas de souci il suffit d'appuyer sur F3 pour retrouver les noms puis de sélectionner le nom à insérer et de cliquer sur Ok ici étant donné que j'ai saisi juste avant le nom je peux cliquer sur Annuler et valider en appuyant sur OK et voilà notre graphique est désormais prêt à s'adapter automatiquement au changement que nous allons mettre en place sur notre tableau pour nous en rendre compte nous allons valider en appuyant sur OK ici notre graphique est toujours pareil mais si nous ajoutons les ventes du mois de juillet qui sont par exemple ici égales à 1000 300 nous allons pouvoir constater que celles-ci seront ajoutées automatiquement dans le graphique et donc maintenant pour cette deième partie de la vidéo nous allons vouloir faire ressortir la meilleure performance de manière visuelle directement au niveau de ce graphique pour cela nous allons mettre en place une simulie mise en forme conditionnel qui va colorer en ver la barre qui représente à la vente la plus élevée cela nous permettra de voir d'un simple coup d'œil le moi avec la meilleure vente et donc pour cela nous allons commencer par insérer une colonne juste à droite du tableau et nous l'utiliserons pour faire apparaître une seule barre de couleur différente sur le graphique qui représentera la meilleur des ventes donc pour cela nous allons sélectionner la plage des cellules dans lesquelles nous voulons mettre en place ce calcul ici nous allons donc sélectionner une grande plage par exemple les cellules C9 jusqu'à C2 donc pour sélectionner directement une plage de cellule nous pouvons saisir les coordonnées de départ et de fin ici dans la zone des noms lorsque nous appuyons sur Entrée Excel va sélectionner donc toutes ces cellules ensuite nous allons pouvoir mettre en place une for formule à l'intérieur de cette plage de cellule qui va permettre de ne récupérer que la valeur maximale de notre plage et sinon nous renvoyer une erreur donc pour cela nous utilisons la fonction SI qui permet d'effectuer un test logique afin de renvoyer un résultat donné lorsque ce test sera égal à vrai ou un autre résultat lorsqu'il sera égal à faux ici donc le test va consister à regarder si la valeur de la cellule saisie juste à côté correspond à la valeur maximale que nous allons donc obtenir grâce à la fonction max de l'ensemble des ventes de notre tableau que nous allons obtenir en utilisant le la plage dynamique n vente que nous avons créé juste avant donc ici nous l'insérons et donc lorsque effectivement ce résultat correspondra à la valeur maximale nous pourrons récupérer la valeur et dans tous les autres cas pour être sûr que aucune information n'apparaisse sur le graphique nous afficherons donc une erreur na en saisissant tout simplement ce résultat ici en tant qu'argument valeur s faut ensuite nous fermons la parenthèse et nous validons la formule sur l'ensemble des cellules sélectionnées en utilisant les touches contrôle et entrée et maintenant comme vous pouvez le constater nous avons une colonne remplie de Na sauf pour la valeur maximale qui correspond au vent du mois de mars c'est-à-dire ici au 20200 maintenant que notre colonne Max est configuré pour identifier la valeur maximale nous allons vouloir l'intégrer dans notre graphique via une plage dynamique exactement comme nous l'avons fait juste avant donc nous rappuyons sur contrô et F3 alors pour information si jamais le RAC cour clavier contrôle F3 ne fonctionne pas sur votre poste c'est probablement que vous êtes sur un clavier réduit comme les claviers que nous rrouvons sur les PC portables et donc il faudra appuyer en même temps sur la touche fonction qui se trouve la plupart du temps en bas à gauche de votre clavier voilà donc maintenant que nous sommes ici nous allons faire exactement comme tout à l'heure à savoir que nous sélectionnons la formule de N vente que nous copions en appuyant sur contrôle C et ensuite donc nous cliquons sur nouveau ici nous allons appeler cette plage n V max et insérer donc la formule que nous venons juste seement de coller à l'instant sauf que nous allons modifier la cellule de référence donc la cellule de départ il ne s'agit plus de la cellule B9 mais de la cellule C9 c'est-à-dire la première cellule de notre colonne max cela étant fait nous pouvons cliquer sur OK pour valider et fermer le gestionnaire des noms maintenant nous resélectionnons le graphique afin d'insérer cette nouvelle valeur en nous rendant dans création de graphique et en cliquant sur Sélectionner les données ici nous allons pouvoir ajouter une nouvelle série que nous allons appeler tout simplement max et pour laquelle la valeur de la série va correspondre au nom que nous venons de créer à savoir n vent max nous sélectionnons le nom après avoir appuyé sur la touche F3 comme je vous l'aiexpliqué juste avant pour que effectivement Excel puisse récupérer la plage dynamique que nous avons créé nous allons devoir insérer avant le nom de cette plage le nom de la feuille de calcul je recommence vente max ok ok et maintenant lorsque je valide en appuyant sur OK nous pouvons voir que la barre qui correspond au au mois max c'est-à-dire ici au mois de mars a été doublé l'objectif va maintenant être de configurer cette barre pour qu'elle apparaisse au-dessus de la barre du mois de mars avec une mise en forme qui permettra donc de faire comprendre au lecteur du graphique que effectivement il s'agit de la valeur maximale et donc pour cela nous cliquons sur cette valeur max puis nous effectuons un clic droit afin de cliquer sur mettre en forme une série de données sur le panneau latéral qui s'affiche sur la droite nous allons mettre en place une superposition des séries à 100 % et si nous souhaitons rendre le graphique un petit peu plus lisible nous pouvons également réduire la largeur d'intervalle pour que les barres soient un petit peu plus larges étant donné que notre barre Max est toujours sélectionné nous allons maintenant nous rendre dans remplissage nous allons souhaiter qu'il n'y ait aucun remplissage afin de pouvoir voir la barre située juste en dessous en revanche nous allons appliquer une bordure verte autour de cette barre pour bien faire comprendre qu'il s'agit de la barre maximale donc nous choisissons ici la couleur verte avec une largeur de point par exemple de 1,5 et pour mettre un petit effet nous allons également appliquer un éclat en choisissant ici l'éclat vert parmi la liste des éclats proposés donc maintenant lorsque nous regardons notre graphique nous voyons que la barre du mois de mars a bien été mise en avant et qu'il s'agit donc ici de la valeur maximale de chiffre d'affaire réalisé sur la période par l'entreprise et donc maintenant comme vous vous en doutez nous allons vouloir recommencer cette opération sauf que cette fois nous allons vouloir faire apparaître uniquement la valeur minimale donc comme tout à l'heure nous créons une colonne vente minimale puis nous allons saisir la formule si B9 est égal maintenant à min c'est-à-dire à la valeur minimale de notre liste dynamique n vente alors nous voudrons récupérer cette valeur B9 en revanche dans le cas contraire nous voulons générer une erreur na donc di N- a nous fermons la parenthèse de la fonction SI et nous validons en appuyant sur contrôle entrée et maintenant la seule valeur qui apparaît dans cette colonne et qui n'est pas une erreur et 800 qui correspond au vent du mois de janvier nous allons maintenant créer le nom qui correspond en sélectionnant ici nent max nous sélectionnons la formule que nous copion nous créons un nouveau nom que nous allons appeler n vente min et donc nous allons coller la formule que nous venons d'insérer dans le Press papier en faisant bien attention de modifier la référence de la cellule de départ il s'agira maintenant de la cellule des9 ensuite nous cliquons sur OK pour créer le nom puis nous pouvons fermer le gestionnaire des noms et donc maintenant nous allons pouvoir ajouter encore une fois une nouvelle série dans notre graphique en cliquant sur celui-ci et en cliquant sur Sélectionner les données nous cliquons sur Ajouter pour créer le nom de la série mean ensuite nous sélectionnons une cellule pour insérer automatiquement le nom de la feuille de calcul et ici nous allons insérer la plage n vant minine cela étant fait nous pouvons cliquer sur OK et sur OK pour qu'Excel nous ajoute effectivement cette nouvelle barre directement dans le graphique comme tout à l'heure nous allons retirer le remplissage nous allons mettre en place pass une bordure de couleur rouge de 1,5 points et ensuite donc nous allons mettre en place un éclat qui va permettre d'ajouter un petit effet directement sur le graphique de cette manière comme vous pouvez le constater les valeurs apparaissent bel et bien sur le graphique et il nous suffira d'ajouter une nouvelle entrée sur celui-ci pour que les valeurs se mettent à jour automatiquementici nous avons fait 500 ventes au cours du mois d'août c'est maintenant le mois d'août qui est mis en évidence et si en septembre nous avons réalisé 2200 ventes alors ce sera celui-ci qui passera en vert et donc maintenant avant de terminer il ne reste plus qu'à ajouter un titre à notre graphique afin d'être sûr que le lecteur puisse comprendre de quoi il s'agit nous allons appeler ce graphique évolution des ventes donc comme d'habitude vous pouvez télécharger le support de secours en suivant le lien que vous trouverez dans la description de la vidéo si la vidéo vous a plu n'oubliez pas de la liker et de la partager à vos collègues et amis sur l'ensemble de vos réseaux sociaux et vous pouvez également me laisser vos impression dans l'espace des commentaires je lirai chacun d'entre eux et y répondrai avec plaisir évidemment si ce n'est pas encore fait abonnez-vous la chaîne pour ne manquer aucune astuce c'est également très important pour m'aider à gagner en visibilité afin de pouvoir développer la chaîne et continuer de vous proposer des tas de nouvelles vidéo merci d'avoir suivi cette vidéo et à très bientôt pour un prochain tuto