Visualisation de données Excel, partie 2 : démonstrations avancées

1. Intro: : Réglage des attentes: accord, donc maintenant vous êtes probablement maître de presque tous les types de graphiques qu'Excel 2016 a à offrir. Alors allons-y et montons les choses d'un cran. Dans cette prochaine section, nous allons plonger dans une date vraiment unique, vraiment avancée, vraiment avancée,de niveau supérieur de ses exercices. Mais avant de le faire, prenons juste une minute pour fixer quelques attentes. Les graphiques et graphiques avancés numéro un nécessitent souvent l'utilisation de formules et de fonctions avancées . En fin de compte, c'est ce qui les fait avancer. Donc, je sais que ce n'est pas une formule et des fonctions. Bien sûr que vous êtes venu ici pour apprendre la date de est. Mais le fait est que les formules et les fonctions sont des composants essentiels pour permettre des fonctionnalités vraiment, vraiment cool avec des graphiques et des graphiques avancés. Alors pendant les démos, je parlerai des formules. Je vais vous donner un bref résumé des formules, mais en aucun cas ce ne sera un examen complet des formules et des fonctions. Deuxièmement, certaines de ces démos sont assez faciles. Vous pourriez en trouver un très dur. Certains pourraient vous donner envie de vous boucler et de pleurer. Maintenant, c'est bon. Tout ce que je vous demande, c'est de procéder avec un esprit ouvert et de me faire confiance, vous sortirez. Un rendez-vous de son magicien dernier mais pas le moindre, numéro trois. Si vous voulez améliorer votre jeu de formule, apprenez-en plus sur certaines de ces fonctions que nous abordons ici. Quelques si. Index match offset fonction de tableau Count Day, etcetera. Consultez mon Excel Analytics, formules et fonctions avancées Cours et oui, oui, oui, je sais que je suis partiale en tant qu'auteur du cours, mais croyez-moi, c'est vraiment sympa, vraiment examen complet de toutes les formules et fonctions que vous pourriez rencontrer dans Excel. Alors avec ça, allons-y et commençons. 2. Graphiques d'image personnalisée: Très bien, alors commençons les choses avec quelque chose qui est un peu plus facile, mais toujours assez unique et créatif quand même. Et ce sont des graphiques de superposition d'image personnalisés. Donc, dans les exercices de visualisation de données classeur tête sur ce premier onglet vert, l'onglet graphiques de superposition d'image, et nous verrons ce que nous travaillons avec. Et comme vous pouvez le voir ici, nous n'avons que quelques points de données à utiliser. Ce sont les résultats de base de la collecte de fonds. Nous avons un utilisateur entré la valeur pour les dollars collectés et vendre A pour obtenir un objectif de 10 000$ les dollars recueillis et pour cent aller dans les colonnes C et D, qui sont tous les deux calculés sur la base A et B. Et l'idée ici sera de créer quelque chose qui est un peu plus unique et un peu plus créatif. Ensuite, votre colonne standard 100 % empilée. Et pour ce faire, nous allons intégrer nos propres graphismes personnalisés. Donc, j'ai construit deux exemples ici et les ai insérés dans la feuille de calcul, j'ai obtenu un thermomètre et une icône pouce vers le haut, et vous pouvez utiliser tout ce que vous choisissez. Gardez à l'esprit que peu importe le type de graphique que vous utilisez. Assurez-vous qu'il dispose d'un arrière-plan solide et opaque et d'une zone transparente où vous voulez que le graphique brille par derrière. Donc, comme vous pouvez le voir si je fais un clic droit et amène mes pouces vers le haut icône à l'avant. La même affaire ici. Il a unfond blanc opaque et solide, fond blanc opaque et solide, avec la zone transparente juste à l'intérieur du contour des pouces. Alors allons et commençons et insérez réellement une colonne empilée. Je vais sélectionner un contrôle un et deux Sélectionnez C un et C deux qui lui donne à dollars levés et des dollars restants. Comment insérer 100% empilé swap leur propre colonne que j'ai mes deux Siri empilés ensemble sur la même colonne, et maintenant nous faisons un peu de formatage donc je n'ai pas besoin du titre du graphique et n'ai pas réellement besoin l'axe X. Cette boîte orange, ma boîte de dollars restante. En fait, je ne veux pas avec une sorte de remplissage solide donc je peux choisir Non, Phil, Phil, pas de ligne pour mes dollars amassés. Siry's Donnons un remplissage solide avec une belle, rouge foncé sorte de classique, leur maman et sa couleur, et à ce stade, ce qui est important est qu'il traîne dessus et une ligne zéro et 100% marques avec le icône thermomètre. Donc je vais le faire manuellement. Ça ressemble à un bon alignement. Et une fois que j'ai fait ça, n' en ai même pas besoin. Pourquoi plus d'accès ? Vous pouvez choisir de conserver les lignes de grille ou de les perdre si vous le souhaitez. Dans ce cas, je vais les garder. La dernière chose que je veux faire ici. simplement avec le bouton droit sur Format. La zone du graphique. Je ne veux pas de zone Phil ou de bordure de carte. Et maintenant, enfin, si je viens de mes données, Siri peut réduire l'écart avec juste pour s'assurer que les Siri sont aussi larges que toute la zone du graphique. Donc, j'ai bouclé cela vraiment en un seul rectangle qui grandit en fonction de la valeur et vendre huit à, donc je vais le rétrécir à ce dont j'ai besoin, puis il suffit de déplacer et de faire glisser sur le clic droit. Renvoyez-le, et maintenant il est fondamentalement derrière sont obscurcis par mon graphique thermomètre. La zone d'Onley qui brille à travers est la pièce de mon graphique qui est transparente, donc je suis presque là. La dernière chose que je dois faire est juste de remplir cette petite ampoule ici, le fond, qui est en fait inférieur à 0%. Donc, techniquement, ça ne fait pas partie de notre graphique. Je vais juste insérer la forme, attraper l'outil ovale. Si je maintiens Shift pendant que je fais glisser cela, je peux ajuster la taille. Je vais la superposer sur l'ampoule. Donnons-lui ce même Phil rouge et pas de ligne et nous allons faire un clic droit et l'envoyer à l' arrière aussi. Et là, vous l'avez. Maintenant, comme l'utilisateur change sa valeur dans un deux, disons maintenant qu'ils ont augmenté 5800 sont empilés ascensions de zone, et il fait apparaître comme si ce thermomètre augmente en température ou grandit jusqu'à cet objectif de 100%. Alors on y va. Nous avons 8123 50 super simple, mais un bon effet créatif que la plupart des gens ne penseraient pas faire en excellent. Et maintenant ce que je peux faire, c'est difficile d'y accéder. Je vais faire glisser ma superposition hors du chemin juste pour que je puisse sélectionner mon contrôle de presse graphique. Voir pour le copier, juste faire une version dupliquée de celui-ci. Et puis je peux faire glisser mon graphique vers le bas, et je vais utiliser celui-ci comme ma source pour les pouces vers le haut, car il utilise exactement les mêmes données. Pourquoi ne pas changer la couleur du Phil en vert clair ? Et dans ce cas, prenons les lignes du réseau de la rivière et nous allons l'étaler un peu. Et fondamentalement, ce que je veux faire ici est re l'échelle un peu à nouveau afin que 100% frappe juste le haut d'un pouce . Et 0% est juste ici en bas, donc je peux le tester juste en tapant 10 000. Juste que c'est clair où est mon top. On dirait que ça va m'amener juste là. bas, comme vous pouvez le voir, est aligné, sorte que cela semble bon. Maintenant, c'est juste une question d'étirer ça pour remplir toute la forme. Cliquez avec le bouton droit envoyer à l'arrière Là vous l'avez. Alors essayons de brancher quelques valeurs ici dans Sal A. à 2500 ? Voilà, tu y vas. Il est environ 1/4 rempli 5000 obtient évidemment ce à mi-chemin. 66 82. Nous allons aller un peu plus loin, et là vous allez à différents exemples de mettre votre propre spin créatif sur un graphique en colonnes empilées relativement simple pour créer une superposition d'image personnalisée. 3. Ajouter des valeurs de Binary aux séries de dates de surlignées: pour la prochaine démo. Je veux parler d'ajouter des valeurs binaires pour mettre en évidence les plages de dates dans un graphique. Alors dirigez-vous vers le deuxième onglet que l'onglet plages de dates binaires et vous verrez ici. Nous avons juste des colonnes de données. Nous examinons les revenus quotidiens en essayant, et ce que nous voulons présenter ici est une circonstance où vous pourriez avoir des dates ou des plages de dates qui sont significatives d'une manière ou d'une autre, dates que vous voulez attirer l'attention ou mettre en évidence dans un graphique. Dans ce cas, dans la colonne A. Vous verrez des valeurs ou des dates formatées avec un Phil vert clair. Et disons simplement que ces dates représentent des périodes de vente ou de promotion pour cet exemple particulier . façon réaliste, ça pourrait être n'importe quoi. Peut-être que vous voulez juste mettre en valeur les week-ends dans vos données. Ou peut-être que vous voulez mettre en évidence tous les mercredis ou lundis. Quoi qu'il en soit, le fait est que vous essayez de mettre en évidence certaines dates ou plages de dates dans votre graphique, donc nous allons le lancer en sélectionnant A et B et en insérant simplement un graphique linéaire de base, faire glisser vers le haut et l'étaler un peu. Et pourquoi ne pas aller de l'avant et formater cet axe X moche. Donnez-lui une unité majeure de quatre simplement pour le rendre un peu plus lisible. Alors, voilà. Nous avons un chiffre d'affaires tendance affiché sous la forme d'un graphique linéaire. Et maintenant, quand vient le temps de mettre en valeur ces plages de dates dans ce graphique, ce que je vais faire est d'ajouter une nouvelle colonne en C appelée promo, et il aura une valeur par défaut de zéro. Donc, je pourrais juste double-cliquer pour appliquer ce zéro tout le chemin vers le bas. Et maintenant tout ce que j'ai à faire est de remplacer le zéro par un pour toutes les dates qui tombent dans une période promo. Donc 18 à 20 par exemple, je peux copier celui-là. Appliquez-le ici pour augmenter 29 à 34 51 3 62 67 3 69 et enfin 81 à 85. Ce qui me reste avec est un Siri binaire et une colonne voir qui prend soit une valeur de zéro si la date ne tombe pas dans une période promo ou une. Si la date le fait, à ce stade, c'est juste une question d'obtenir ce binaire. Siri est dans ce tableau d'une manière qui rend une sorte de sens si simple. Je peux faire un clic droit sur sélectionner les données. Ajoutez une seconde, Siri Appelons simplement It promo et pour les valeurs sérieuses, sélectionnez Cell C pour contrôler Shift Flèche vers le bas pour me conduire jusqu'au bord de ce tableau, qui est vu 91 Appuyez. Ok deux fois. Faites défiler vers le haut, et comme vous pouvez le voir, nous ne sommes pas tout à fait là. Tout ce qui est fait est d'ajouter une deuxième ligne sur le même axe, qui prend une valeur de zéro ou un, et il est juste complètement noyé par les totaux de revenus. Donc, il est clair que nous devons utiliser un graphique combo. Dans ce cas, je vais aller vers les outils de graphique, changer ma combinaison de sélection de type de graphique ici au bas de la liste. Et comme vous pouvez le voir, Excel a essayé de devenir fantaisie et de changer le type de graphique sur moi. Dans ce cas, je ne veux pas que les revenus soient regroupés. COLONNE Vous voulez revenir au graphique linéaire original que j'avais sélectionné plus tôt et pour la période promo, qui est celui de mon binaire Siri, celui-ci ? Je veux être 100 % colonne de pile sur l'axe secondaire. Je fais ça. Vous pouvez voir qu'on commence à se rapprocher un peu. Donc, ce que je veux faire est de sélectionner ce format de Siri et je veux lui donner un Phil très beau, léger, solide, parce que je veux attirer l'attention sur ces dates. Mais je ne veux pas noyer tout le reste, et je ne veux pas ajouter trop de bruit à cette visualisation. Donc, si je passe aux options de Siri, dernière chose que je vais faire avec ce Siri est juste d'éliminer l'écart avec et avec cela fait essentiellement, il crée ces colonnes de données, dont la largeur représente toutes les dates qui tombent dans un période de promotion particulière. Mais vous remarquerez peut-être que nous avons encore l'accès secondaire ici, qui à ce stade est un peu dénué de sens. Ce n'est vraiment rien qui tombe le long de cette année à l'échelle 100. Mais le problème est, si je le supprime, il supprime les Siri avec, donc vous contrôle pour annuler cela, et nous allons juste utiliser un petit raccourci pour aller au menu d'accueil, et nous pouvons faire le Phil blanc ici. Une autre option, si nous annulons cela, est d'utiliser l'astuce de mise en forme conditionnelle que nous avons utilisée lors de l'un des formats de conférences précédents qui accèdent aux options de nombre, et nous allons utiliser une catégorie personnalisée, et le code sera trois demi-deux-points consécutifs qui rendent essentiellement le texte de l' Axe complètement invisible. Donc, à ce stade, c'est vraiment juste quel que soit le formatage que vous voulez dans ce cas, je vais le faire. Une ligne bleu foncé solide va le lisser juste parce que j'aime les lignes lisses. Et puis, évidemment, vous pouvez ajouter des étiquettes d'axe, titres de graphique, tout ce que votre cœur désire. Mais voilà. Plutôt cool. Manière assez intéressante de mettre en évidence ou de mettre en évidence des plages de dates spécifiques dans un graphique dans Excel . 4. Automation avec OFFSET, COUNTA et les plages nommées: La prochaine démo est l'un des exercices les plus importants de tout le cours, et il s'agit d'automatiser les graphiques en utilisant Offset, Count A et Named Ranges. Maintenant, la raison pour laquelle cette conférence est si importante est parce que des fonctions comme Offset et Count Day sont les éléments de base de la création de graphiques et de graphiques vraiment dynamiques. Fondamentalement, ils vous permettent de créer desgraphiques fluides et dynamiques, graphiques fluides et dynamiques, des données source plutôt que des tableaux fixes traditionnels, ce qui vous permet vraiment de donner vie à vos visualisations et d'intégrer des éléments beaucoup plus avancés fonctionnalité. Alors dirigez-vous vers l'onglet Mises à jour automatiques du graphique. Et comme vous pouvez le voir ici, nous avons un ensemble de données simple. Deux colonnes, date et température. Et disons que mon travail est de mettre à jour un tableau des tendances de la température avec chaque jour où je collecte de nouvelles données. Ce que vous pourriez faire est de sélectionner une cellule B 24 et d'insérer un graphique en courbes comme d'habitude. Et c'est très bien. Les seuls défis que, comme vous ajoutez plus de jours de données et entrez manuellement de nouveaux graphiques de températures ne savent pas à mettre à jour parce que, comme vous pouvez le voir, ses données source sont corrigées via la route 24 de sorte qu'il ne voit pas de nouvelles qui sont ajoutés sous cette option et Excel est de convertir ou de formater cette plage de données sous la forme d'une table , puis de tabuler pour ajouter une nouvelle torréfaction de cette table. Mais je vais vous montrer une approche qui est un peu plus flexible et beaucoup plus puissante, et qui utilise ces offset et compter une fonction sous la forme de Ranges nommées. Donc, ce que nous devons faire ici, c'est diriger vers notre onglet de formules et cliquer sur le bouton de définition du nom . Nous allons le faire deux fois d'abord pour les valeurs X, qui sont des dates et deuxième pour savoir pourquoi les valeurs et les températures de l'air. Commençons par les dates. Donc, en fait, il suffit de nommer les dates affaiblir la portée de ce tableau nommé à un onglet particulier ou au classeur dans son ensemble. C' est bien pour l'instant. Et voici où nous verrions généralement un tableau fixe de cellules servant de données source pour notre graphique. Ce que nous voulons faire est de supprimer cela et d'écrire la fonction de décalage, et ce décalage ne résume trop rapidement, c'est qu'il dit Excel. Je veux que vous commenciez à cette cellule particulière que vous définissez en voulant descendre un certain nombre de lignes de gauche à droite. Un certain nombre de colonnes et retourner un tableau de cellules avec une hauteur particulière et avec défini par le nombre de lignes dans le nombre de colonnes. Et la belle chose est que vous pouvez imbriquer des fonctions dans leur de sorte qu'au lieu de coder en dur les dimensions spécifiques du tableau résultant, vous pouvez le rendre fluide et dynamique. C' est exactement ce qu'on va faire, en utilisant le compte une fonction. Donc avec Offset. C' est un peu difficile à voir, car lorsque vous tapez des fonctions ici dans le nom Manager, Excel ne vous aide pas pas pas à pas. Alors regardez attentivement. Heureusement, ai écrit quelques-uns d'entre eux, donc je connais les étapes, mais fondamentalement, nous allons ouvrir la parenthèse, et la première partie de notre fonction offset est le début. Donc, pour notre tableau de dates, la cellule de départ sera la première date dans le tableau, qui est de vendre un à, puis, comme nous virgule sur va passer à chaque composant de la fonction. Le morceau suivant de la fonction de décalage dit, Voulez-vous descendre ou monter n'importe quelle rose à partir de ce point de départ ? Cette affaire ne veut pas garder cela un zéro. C' est notre point de départ et sera toujours le commentaire suivant dit, Voulez-vous vous déplacer à gauche ou à droite de ce point de départ à nouveau ? La réponse est non. Ce qui nous amène au composant suivant, qui est la hauteur de notre tableau résultant. Et c'est là que nous voulons le comte A. Parce que, eh bien , nous pourrions dire OK, nous voulons un tableau avec une hauteur de 25 lignes et codé en dur 25 dedans. Ensuite, lorsque nous ajoutons de nouvelles données, il va toujours lire les 25 premières lignes et servir le même but qu'un tableau fixe traditionnel , ce qui ne nous mène vraiment nulle part. Donc, au lieu de coder en dur un nombre là-bas, nous allons utiliser le nombre, une fonction qui compte fondamentalement toutes les cellules dans une plage donnée qui contient une certaine valeur. Donc toutes les cellules non vides et la plage dans laquelle nous allons regarder dans ce cas, ouvrez la parenthèse et sélectionnez l'ensemble de la colonne A et fermez la parenthèse. Donc maintenant, le compte d'une fonction dit, Regardez la colonne entière. A. Dites-moi combien de cellules dans la colonne peuvent contenir quelque chose, Donc, dans ce cas, la fonction Count Day examinerait toute la colonne A et compterait 24 cellules qui contiennent des données, qui est presque ce que nous veulent sauf que nous ne voulons pas compter l'en-tête. Donc, vous avez juste besoin de soustraire un ici et une virgule au composant final de la fonction qui est quelle est la largeur du tableau résultant. , En termes de nombre de colonnes, ce cas peut en entrer une parce que nos valeurs X sont toutes contenues dans un tableau unidimensionnel d'une colonne large, donc je peux fermer cette parenthèse qui enveloppe notre jour de décalage et de comptage fonction. Compresser. D' accord. Et maintenant nous allons faire exactement la même chose pour les températures et la colonne B. Donc pour trouver le nom, appelez ça la portée du classeur des températures, c'est bien. Et encore une fois, nous allons type de fonction offset ici. Cette affaire, notre point de départ. C' est Selby aussi. On ne veut pas bouger de rose. Nous ne voulons pas déplacer de colonnes à partir de ce point de départ. La hauteur de notre tableau résultant est Count A ce cas de colonne B moins un pour la virgule d'en-tête Une colonne large fermer la parenthèse Boom. Voilà, tu y vas. Donc maintenant, nous avons nommé une augmentation à définir d'une manière beaucoup plus dynamique. Combien de valeurs nous avons besoin de tracer à partir des colonnes A R X valeurs et colonnes B R Y. Valeurs. La dernière étape consiste donc à ajuster la source de notre graphique en sélectionnant les données et en commençant par la température, ce qui explique pourquoi les valeurs. Nous pouvons éditer celle de Siri, comme vous pouvez le voir ici Serious est actuellement fixé aux cellules B 2 à B 24. Donc cette portée ne changera jamais. Et il ne verra jamais de nouveaux points de données qui seront ajoutés en dessous. Tout ce que je dois faire est de supprimer tout après ce point d'exclamation et les températures de type, qui est le tableau nommé que nous venons de créer pour nous. Ok, la même chose exacte pour l'axe X horizontal. C' est fixé à deux à 24. Je vais tout supprimer après le point d'exclamation et taper les dates. Appuyez sur OK et voulez le faire. Vous ne verrez rien changer au départ, mais à mesure que vous ajoutez plus de données et que vous tapez plus de températures, vous pouvez voir maintenant que notre graphique est automatiquement mis à jour avec ces nouveaux points de données. Si vous sélectionnez la ligne et sélectionnez celle de Siri, vous verrez maintenant que celle de Siri reflète toutes les données de la route 27. Nous avons donc une amorce rapide et en utilisant les fonctions de décalage et de comptage dans les plages nommées pour créer automatiquement des graphiques à jour avec des données source fluides et dynamiques. 5. Ajouter la fonctionnalité de Scroll et de zoom avec le contrôle de forme: Cette prochaine démo est l'une de mes préférées. C' est vraiment cool. C' est quelque chose que beaucoup de gens ne réalisent pas que vous pourriez même faire dans Excel. Il s'agit de créer des graphiques qui peuvent faire défiler et zoomer dynamiquement. Pour ce faire, nous allons utiliser la fonction offset. Mais au lieu de le combiner avec Count Day comme nous l'avons fait dans le passé, cette fois nous allons insérer quelque chose appelé contrôles de formulaire, qui permettra aux utilisateurs de cliquer et de faire glisser et de manipuler des barres de défilement dans le classeur pour dynamiquement modifiez la vue qu'ils voient dans le graphique. C' est donc une combinaison vraiment cool d'outils pour créer un effet vraiment intéressant. Donc, dans cet onglet graphiques de défilement et de zoom tout d'abord, assurez-vous que vous avez l'onglet développeur dans votre ruban. Sinon, c'est super facile à ajouter. Il suffit de diriger vers l'option de fichier allant vers les options au bas ruban personnalisé et ici, sur le côté droit de la douleur, vous devriez voir une case à cocher à côté de la ligne de développeur. Si ce n'est pas coché, allez-y et donnez-lui une vérification et appuyez sur OK et vous devriez le voir apparaître juste là et maintenant l'onglet développeur est où vous pouvez aller pour éditer Visual Basic ou VB un code pour écrire ou éditer macros. Et il inclut aussi les contrôles de formulaire que nous allons utiliser pour cette démo particulière. Donc, dans l'option d'insertion ici, allez-y et sélectionnez le contrôle de formulaire de la barre de défilement. Et c'est vraiment cool. Vous pouvez les faire glisser dans la forme exacte que vous voulez. Donc quelque chose comme ça fonctionnera très bien. Et avec son encore sélectionné, je vais frapper le contrôle C sélectionner un contrôle de cellule vide V pour le coller. Et maintenant je dois faire défiler les barres une pour défiler divin et une pour définir Zoom. Maintenant, pour réellement les personnaliser et s'assurer qu'ils font ce que nous avons besoin d'eux, vous pouvez cliquer avec le bouton droit de la souris pour accéder à vos options de contrôle de format. Donc, dans ce cas, nous regardons la barre de défilement, qui définit la fenêtre de données qu'un utilisateur regarde soit décalée vers le haut ou vers le bas dans ce tableau ici. Donc, la valeur minimale ici sera zéro et la valeur maximale sera 11. Parce que s'ils commencent à Janvier par défaut défilant 11 mois vers le bas, nous vous amènerons au bas de ce tableau à la ligne 14. Je n'ai pas besoin de modifier les options de modification incrémentielle ou de page. Ce qui est important, c'est ce lien cellulaire ici. Donc, si je sélectionne le sélecteur, je vais lier ce contrôle de formulaire pour vendre d 16 quand j'appuie sur OK et d le sélectionner. Maintenant, comme je manipule cela, vous pouvez voir la valeur et vendre D 16 mise à jour en conséquence. Et nous pourrions juste confirmer que nous avons un maximum de 11 et un minimum de zéro, tout comme nous avons réglé le même accord avec le contrôle du format zoom. Dans ce cas, zoom est défini par le nombre de lignes de la visionneuse regarde à un moment donné, et ce nombre de lignes peut croître ou diminuer. Mais l'essentiel est que vous devez regarder au moins un point de données ou au moins une ligne de données, donc nous allons lui donner un minimum d'un et un maximum de 12, ce qui correspond à la hauteur de nos données brutes. Ray ici et dans ce cas, mon lien cellulaire sera donc d 17 Je vais appuyer sur OK et confirmé que mon minimum est un. Mon maximum est 12 alors allons-y et mettons-les quelque part au milieu. Et je veux lancer les choses en insérant un graphique juste pour que je puisse modifier et polir et finaliser le formatage avant de prendre les mesures pour le rendre dynamique. Donc, dans ce cas, je vais contrôler Click Colonnes B et C ainsi que la colonne E. Parce que je veux créer un graphique combo où les impressions sont mes colonnes, cliquez sur. Le taux est ma ligne. N' hésitez pas à utiliser n'importe quelle combinaison de ces trois mesures ou à suivre avec moi tout à fait à vous de sorte que mes options d'insertion vont dans les colonnes. Plus de graphiques en colonnes Combo la route. Cliquez sur le taux comme une ligne sur mon axe secondaire et maintenant à nouveau, n'hésitez pas à suivre avec ces ajustements de mise en forme que je fais ou le formater comme vous le souhaitez. Tu sais, ce n'est vraiment pas la partie importante de la conférence. Je vais juste formater la zone de l'intrigue, débarrasser de mon remplissage et de ma bordure, apporter quelques modifications à ma ligne. Chez Siri. Je pourrais solide, sombre grand avec la largeur de deux. Lisser et ajouter une ombre prédéfinie rapide à 80% de transparence, puis avec mes colonnes, donnez à ces un Phil vert clair et ajustez l'écart avec le bas. Et enfin, je vais juste ajouter un élément de graphique avec quelques lignes de grille horizontales secondaires secondaires. Alors, voilà. Je suis à nouveau satisfait de ce formatage. Personnalisez-le comme bon vous semble. Maintenant, il est temps de rendre cette chose dynamique car en ce moment il se nourrit de ces tableaux de données fixes traditionnels, qui devront mettre à jour le lien orteil vers ses valeurs de défilement et de zoom. Donc, pour ce faire, je vais entrer dans les formules, définir un nom, et commençons par nos valeurs X. Donc nous allons appeler ce Scroll X, et au lieu de cette référence de cellule fixe ici, nous allons taper la fonction offset. Mes valeurs ex sont mes mois. Donc mon point de départ est mon premier mois, qui vit dans la cellule B 3 et le nombre de lignes que je veux déplacer. Ce point de départ sera une fonction de l'index de défilement, qui vit dans la cellule D 16. Parce que rappelez-vous, comme un utilisateur déplace cette barre de défilement à gauche et à droite là essentiellement déplacer le point de départ de cette fenêtre de données qu'il regarde vers le haut ou vers le bas. La pièce suivante est le nombre de colonnes que je veux déplacer ce type de point de départ zéro parce je ne veux pas déplacer le point de départ du tout. Mes mois vivent toujours dans la colonne B. La deuxième à dernière pièce, rappelez-vous, est la hauteur qui, dans ce cas, est définie par l'indice de zoom. Puisque les utilisateurs vont déplacer cette barre de zoom et essentiellement grandir ou se rétrécir, la fenêtre qu'ils regardent enfin avec en termes de nombre de colonnes est encore une fois juste si fermée que la presse parenthèse est correcte et que nos valeurs X défilent sont défini. Maintenant, je vais naviguer à travers ce même processus pour chacune de nos valeurs Y, commençant par scroll, pourquoi ou squirrely Et encore une fois nous allons juste taper offset ici point de départ pour nos premières valeurs Y qui sont des impressions vies en C trois Gonna décalez-le par la valeur dans d 16. Je ne veux pas déplacer de colonnes. La hauteur est l'indice de zoom et D 17. Cette largeur est une fermeture de la presse parenthèse OK, et une fois de plus pour défiler large. Pour compenser ce cas. Mon premier clic, la valeur de taux Celie trois nombre de lignes. Je décale son nombre d 16 de colonnes hauteur zéro est D 17 avec est un même processus essentiellement répété trois fois Maintenant, la dernière étape est d'intégrer ces nouveaux champs nommés que nous avons créés dans notre graphique afin que je puisse cliquer avec le bouton droit de la souris sélectionner les données. Et dans chacune des options de Siri, je vais cliquer sur éditer et plutôt que sur le tableau fixe de C 33 c 14. Ceci est ma colonne Impressions, qui est mon défilement. Pourquoi la colonne Donc je peux juste taper le nom de ce nouveau tableau nommé juste là. L' écureuil. Pourquoi et appuyez sur OK et répétez le processus pour le taux de clic, supprimez tout après le point d'exclamation et tapez simplement scroll. Pourquoi aussi, aussi, appuyez sur OK, et maintenant une note vraiment, vraiment importante est que j'ai besoin d'éditer mon axe X aussi, donc je vais aller en édition, supprimer tout après le point d'exclamation, Cliquez sur le défilement X de celui-ci. OK, mais je n'ai pas encore fini. Même s'il semble qu'il n'y ait qu'une seule valeur qui doit être ajustée pour mon axe X, je dois la changer deux fois une fois avec impression sélectionnée et à nouveau avec Cliquez sur le taux sélectionné. C' est vraiment facile à rater. Mais maintenant, comme vous pouvez le voir, si sélectionné, cliquez sur le taux. Si je retourne dans mes options d'édition, vous voyez cette référence fixe juste là à nouveau. J' ai donc besoin d'entrer Scroll X deux fois et maintenant l'axe X associé à Cliquez sur le taux et l' axe X associé aux impressions, qui, façon réaliste, est le même. L' axe X reflète le nouveau tableau nommé dans toutes les instances, donc je peux appuyer sur. Ok, alors voyons si ça a vraiment fonctionné. Essaie de tester. Notre parchemin semble fonctionner très bien. Tout le chemin à gauche signifie qu'on commence en janvier. ce moment, nous regardons une fenêtre de sept mois, ce qui est logique. Puisque nous avons un indice de zoom de sept. Nous voulons de vous des mois supplémentaires, je peux faire défiler vers la droite. Maintenant, nous regardons la fenêtre complète, ou vous pouvez explorer jusqu'à seulement deux, trois ou quatre mois à la fois. Et voilà. C' est cool ? C' est un graphique vraiment unique, vraiment interactif que vous pouvez construire avec des contrôles de formulaire assez simples et nommé une course en soi. Mais il y a un problème ici que nous devons résoudre, à savoir que si nous zoomons tout le long, nous n'avons en fait aucun garde-corps en place pour garder ce tableau dans les limites de nos données source d'origine. Donc, comme vous pouvez le voir défiler juste après toutes les données dans un tableau qui n'a tout simplement plus de sens et rien n'est incorrect à ce sujet, il trace toujours les bonnes données. Nous avons juste besoin de trouver un moyen de mettre à nouveau des garde-corps en place pour éviter que cela ne se produise. Donc, si vous jouez un peu avec cela, vous remarquerez que toute combinaison de défilement et de zoom dans les valeurs suivantes qui sont égales ou inférieures à 12 produit une vue valide. Donc, en d'autres termes, si je fais un zoom jusqu'à 12 mois et que mon index de défilement est réglé à zéro, qui signifie que je commence en janvier, cette vue est légitime. Mais dès que j'essaie de faire défiler au-delà de décembre, maintenant j'ai des ennuis. Même affaire. Si je fais un zoom vers le bas sur huit mois et que je fais défiler une valeur de quatre, vous pouvez le voir dans cette zone dangereuse une fois de plus. Donc, tout ce que j'ai besoin de faire ici est juste, une formule pour définir la valeur maximale que je veux que l'index de défilement prenne, et donc il pourrait le faire, en utilisant une instruction if conditionnelle ici. Et je dirai, si mon index de zoom plus mon index de défilement est inférieur ou égal à 12, alors juste retourné, quel que soit l'indice d'écureuil aurait été, parce que nous savons que si la combinaison de ces indices est inférieure à 12 alors nous avons un vue valide, donc je peux aller de l'avant et juste mettre en miroir l'index de défilement dans ce cas, sinon la valeur si false, si le total est supérieur à 12 qui peut empêcher la vue de dépasser les limites juste en prenant 12 moins le zoom dans la valeur suivante, j'appuie sur Entrée. Vous pouvez voir que chaque fois que le total dépasse 12. Mon défilement Max fixe sa valeur à tout moment. Le total est inférieur à 12 ou égal à 12 Micmac Scroll reflète l'index de défilement original. Donc, pour mettre en œuvre ce changement et m'assurer que les changements reflétés dans notre graphique, je peux revenir à l'onglet de mes formules, tête dans le gestionnaire de noms et ajuster les trois plages nommées que j'ai créées, commençant par Scroll X et compresser dessus. Tout ce que je dois faire ici est de changer ma référence à D 16 qui est mon index de défilement original à D 18 qui est mon défilement max. Donc, je pourrais juste saisir les six changements à 18 et appuyer sur OK, faire la même chose avec défilement. Pourquoi changer les 16 ? Deux sur 18 et faites défiler Lie à 16 deux sur 18. Et maintenant, si nous faisons défiler vers la droite 2 Octobre, Novembre, Décembre. Maintenant, comme vous pouvez le voir, ça ne me permet pas de faire défiler plus loin. Donc essentiellement, ce que cela signifie est que si je suis zoomé jusqu'à la vue maximale de 12 maintenant, la barre de défilement est essentiellement inutile car elle ne me permet pas de faire défiler hors du royaume ou des limites de mon original. Très bien, donc là, vous l'avez en utilisant des contrôles de formulaire et des fonctions de décalage pour créer des graphiques de défilement dynamique et de zoom dans Excel. 6. Animer des changements au fil du temps: Donc l'autre jour, j'étais en ligne et il est tombé très cool. Exemple de date de est que je veux partager avec vous les gars trouvé sur le site Pew Research Center, qui est une organisation qui capture beaucoup de politiques américaines et de données liées à la politique. Et fondamentalement, ils avaient cette pièce vraiment cool sur la polarisation politique, et ils ont construit ce visuel animé que je pensais être la chose la plus cool jamais. C' est un excellent exemple d'utilisation de graphiques de zones qui se chevauchent et de les animer au fil du temps pour raconter une histoire vraiment claire. Et ce que nous examinons ici, c'est essentiellement des points de vue démocrates et républicains en termes d' être constamment libéraux contre constants conservateurs. Et essentiellement, ce que fait cet organigramme, c'est qu'il agit presque comme un double sifflement d'un gramme, où vous voyez la distribution de deux ensembles de données différents changer au fil du temps ainsi que le chevauchement entre eux. Ils ont donc cinq années différentes dans l'échantillon, et au fur et à mesure que vous parcourez, vous pouvez voir comment, au fil du temps, ces points de vue ont changé et finalement, mesure que vous arrivez à 2014, vous pouvez voir cette division très claire ou polarisation qui a lieu si naturellement j'ai vu cela et dit, je dois juste reproduire cette chose et exceller parce que c'est si propre. C' est tellement cool, ça raconte une histoire si puissante. Donc j'ai pensé que ce serait un excellent exemple à utiliser comme une démo pour vous guider du début à la fin. Alors allons dans Excel et nous allons être sur les changements d'animation au fil du temps Tab. Fondamentalement, ce que j'ai fait était de créer un nouvel ensemble de données brutes, et nous avons les mêmes cinq ans, 94 à 2014. Et pour chaque année, nous avons 21 points de données différents ont émergé, ce qui montre ce score allant de libéral jusqu'à conservateur, où le milieu représente des points de vue mitigés. Et puis nous avons le nombre de démocrates et le nombre de républicains qui s'alignent sur chacun de ces scores pour chacune de ces années. Donc, ce que nous allons faire, c'est commencer par simplement créer un organigramme régional pour une année donnée. Juste que nous pouvons obtenir le bon formatage et commencer à obtenir nos repères. Donc je vais choisir être gagné jusqu'à 22. Je n'ai pas besoin du point de données de l'année réelle ici, donc je vais insérer dans la ligne, options a à D zone et faire un peu de formatage ici. Alors faisons-le grand, parce que c'est vraiment cool. Débarrassez-vous du titre du graphique ici, et je vais me former à la zone du graphique. Je ne veux pas vraiment une frontière autour d'elle. Et puis pour les démocrates, républicains dans la légende, je veux rendre ça beaucoup plus grand, plus audacieux et juste étaler un peu. Donc, ensuite, je veux changer les couleurs réelles et la transparence des deux données. Siri est ici, et j'aimerais les aligner avec les couleurs appropriées. Donc pour mes démocrates, Siri, je vais lui donner un Phil solide bleu foncé. Et appelons-le, appelons-le 30 % de transparence. Et pour mon républicain, Siri va lui donner un Phil rouge foncé avec la même transparence de 30%. On y va. Maintenant, nous avons un Démocrates Bleus. Siri est un républicain rouge, Siri. Il est très clair où se trouve le chevauchement, et c'est un très bel exemple d'une date de est si vous regardiez un instantané dans le temps de 1994 donc déjà nous avons fait un très bon travail en visualisant cet ensemble de données à portée de main et dans ce cas n'ont pas vraiment besoin des étiquettes de l'axe y parce qu'il n'ajoute pas vraiment beaucoup pour voir le Count. Nous sommes vraiment intéressés par les différences relatives entre les deux ensembles de données. C' est donc la première étape en construisant un exemple. Maintenant, la clé est d'animer cette chose au fil du temps. On a cinq ans dans l'ensemble de données. Je veux voir comment les choses ont changé au cours de cette période. Donc je vais faire est juste de taper l'année sélectionnée et donc f 5 faire en gras et italique. Donnez-lui un alignement droit et puis ici et vendez g 5. Je vais utiliser un peu de validation des données. Et ce que je veux faire est de créer une cellule où les utilisateurs peuvent descendre et sélectionner l'une de ces cinq années dans l'échantillon, comme nous l'avons vu sur le site. Donc, pour ce faire, j'ai besoin d'une liste de sources pour cette liste déroulante, et ainsi de suite. Je vais juste entrer dans les années. C' est 94 99 vous savez, je suppose que je vais le faire de la façon légitime de choisir toutes mes années ici. Même avec des doublons, je pourrais les rythmer ici, puis dans les données juste appuyer sur le bouton Supprimer les doublons. Et voilà. Cela me laisse donc avec mes cinq années uniques dans l'ensemble de données et maintenant revenir à G 5. Je vais utiliser une option de validation de données, qui est dans mon onglet de données. Validation des données. Et ce que je veux autoriser dans cette cellule est une liste de valeurs et la liste source de ces valeurs vit ici à partir de s un. Les cinq autres pressent. OK, voilà. J' ai ma liste. Nous allons le rendre audacieux et centré et lui donner un peu de Phil juste pour que les utilisateurs No. Ok, c'est quelque chose que je peux sélectionner. Maintenant, nous avons notre liste déroulante, mais ce n'est lié à rien. Tout ce que vous faites est juste de changer la valeur et G 5 lui-même. Donc, ce que nous devons faire est de créer un deuxième ensemble de données, qui sera la nouvelle source de données pour ce graphique et que cet ensemble de données soit dynamique fonction de l'année sélectionnée par un utilisateur. Donc, je vais commencer par saisir les en-têtes et les coller ici sous le graphique. Ce sera mon nouvel ensemble de données source dynamique, et je peux saisir tous les scores, puisque ceux-ci seront les mêmes et les coller ici et maintenant, l'année où il sera égal. Quoi que l'utilisateur ait entré dans G 5, je vais appuyer sur F 4 pour corriger cette référence. Si vous souhaitez en savoir plus sur les types de référence, vous pouvez absolument consulter My Excel Analytics, formules et les fonctions avancées. Bien sûr, il y a une section entière sur les types de référence, mais donc fondamentalement, cela me permet de faire glisser cela vers le bas et d'avoir la référence reste fixe. Un G 5 dans chaque pièce. Donc maintenant, si je change le 94 à 99 mes 94 changements à un 99 ici est bien dans mes données source. Maintenant, la clé est de remplir les valeurs pour les démocrates et les républicains parce que c'est ce qui va vraiment conduire la carte géographique ci-dessus. Il y a donc plusieurs façons de le faire. Quelques fonctions différentes que vous pouvez utiliser pourraient utiliser index match. Dans ce cas, je vais utiliser un peu sa fonction. Donc, fondamentalement, ce que certains if fait, c'est qu'il additionne les valeurs dans une colonne particulière, compte tenu de certains critères. Donc, dans ce cas, je veux certaines des valeurs dans la colonne Démocrates et la colonne C et fixer que là où les critères vont. Appelez-le un est égal à l'année et vendre G 33 Je peux réparer juste le morceau G de ça. Et le deuxième critère est où le score et la colonne B est égal à. Le score vient de se rétrécir parce qu'il est difficile de voir le score en h 33. Je vais réparer le H là et laisser 33 passer à 34 35 36 lorsque j'applique cette formule vers le bas. Donc, je sais que c'était un passage très rapide de la fonction des sommets à nouveau. Je ne veux pas prendre trop de temps à creuser les formules dans ce cours, mais il y a une section entière sur les fonctions statistiques dans mon cours de formules et de fonctions avancées . Donc, vérifiez définitivement ça. Essayez de répliquer exactement ce que j'ai écrit ici, et quand j'appuie sur OK et que je fais glisser cela vers le bas, il remplit mes valeurs Democrat, et nous pouvons faire un rapide Q A. C'est des données de 1999, qui a 9 2023 38 9 2023 38 a l'air bien. Et maintenant je peux copier et coller cette formule dans la colonne Républicains, et la seule chose que j'ai besoin de changer est la colonne qui additionnait les valeurs de Donc ce n'est plus la colonne C. C'est maintenant l'appeler D, qui est où mes données républicaines vivent. Et la même affaire. Je pourrais simplement faire glisser cela vers le bas et faire un rapide QA Donc 234 16 19 234 16 19 semble bien. Et maintenant encore, je vais changer cette année 2004 changements. Et parce que ces air sont maintenant enrobés comme certains sifs fonctionnent, les valeurs pour les démocrates et les républicains changent aussi. Donc on y est presque. Tout ce que nous devons faire est maintenant lier les données source, que nous avions initialement définies pour 1994 seulement à ce tableau dynamique que nous avons créé. Et voici un très, vraiment super conseil que je n'ai pas appris jusqu'à récemment, et il a été une telle bouée de sauvetage, qui est que je pense que à partir d'Excel 2013 ou peut-être 2010 ils changent les choses de sorte que si vous faites glisser les données source et essayez simplement de les déplacer pour un graphique donné, donc vous savez, il a reformaté les choses. Je ferai la même chose avec les données républicaines ici. Faisons-le glisser vers le bas. Vous voyez, il est juste revenu par défaut au bleu et à l'orange, auquel cas je devrais passer par et dire, D'accord, je veux le remplir de rouge à nouveau et lui donner cette transparence de 30%. Il y a en fait un travail pour empêcher ça de se produire, et donc je vais annuler ces deux étapes. Je viens de prendre la tête à l'option de fichier en allant aux options ici. Si vous allez dans Avancé et faites défiler jusqu'au graphique cette dernière case à cocher indiquant que propriétés suivent le point de données du graphique pour le classeur actif. Il suffit de décocher cela et d'appuyer sur OK, je n'ai aucune idée pourquoi la valeur par défaut est de supprimer totalement votre formatage lorsque vous déplacez un Ray de données source . Cela n'a pas beaucoup de sens pour moi, mais c'est ce que c'est. Et heureusement, une fois que vous connaissez ce petit truc, c'est une solution facile. Donc maintenant, au lieu de faire le reformatage, je peux juste saisir mon tableau source et être déplacé en place pour mes deux Siri. Donc il y a des démocrates et voir comment il reste aussi bleu et voici mon républicain rouge sérieusement peut le traîner juste là. Je peux aussi changer les en-têtes, mais de façon réaliste, c'est le même en-tête dans chaque cas, donc cela n'a pas vraiment d'importance. Mais allons-y et faisons-le. Donc maintenant, nos sources sont liées à notre réseau dynamique et ce que cela signifie, c'est ce boom. Vous pouvez modifier la liste déroulante, sélectionner une nouvelle année et parce que leur statut source change le graphique lui-même . Donc, vous pouvez voir cette distribution vraiment cool qui se passe maintenant. Donc si tu veux t'arrêter là, c'est très bien. Je pense que c'est génial. Je vais vous montrer une autre sorte de pas de bonus juste pour le rendre un peu plus facile à parcourir les animations au lieu d'avoir à cliquer et sélectionner dans une liste à chaque fois. Et pour ce faire, nous allons utiliser un autre type de contrôle de formulaire de notre développeur Tab, et je vais insérer ce qu'on appelle un bouton de rotation, et fondamentalement c'est juste un bouton haut en bas qui change la valeur dans un donné. Alors, suivez ici. Je vais le faire rapidement et me former au contrôle, et je vais lui donner une valeur minimale d'une valeur maximale de cinq, et je vais le lier pour vendre notre et appuyer sur OK ? Et ce que je pouvais faire. Voici revenir dans mon onglet de données avec G 5 sélectionné, et je veux réellement supprimer les paramètres de la liste de validation de données que j'avais fait à l'origine . Donc je vais changer cet orteil n'importe quelle valeur, et ça réinitialise fondamentalement cette cellule. Donc maintenant, comme vous pouvez le voir, ce bouton de rotation change la valeur dans notre un de 1 à 5. Et en fait, ce que je vais faire, c'est de couper ça et de le coller dans Q au lieu de r. Et je vais vous montrer pourquoi en juste une seconde c'est toujours lié au spinner. Et maintenant, et appelez-le, c'est le type 12345 et essentiellement, c'est attribuer un numéro d'index à chacune des années que j'ai disponibles. Donc, un index de un correspond à 94 correspond à 99 ainsi de suite et ainsi de suite. Donc, ce que cela me permet de faire dans la cellule G 5 est de remplacer cette valeur de l'année fonction de recherche, et je vais dire, recherchez la valeur que mon spinner produit actuellement dans ce petit tableau de notre un à travers nous. Cinq. Corrigez ce rayon entier, puis les index de colonne à parce que je veux extraire la valeur à la deuxième colonne de mon index et de ma plage. Recherchez zéro, car je veux trouver la valeur exacte et appuyez sur Entrée. Encore une fois, un peu comme les sommets. C' était un cours de crash très rapide dans les recherches v, mais essentiellement, la fonction de recherche vous permet de rechercher une valeur particulière. Ce cas cherchait un numéro d'index de 1 à 5 et renvoyait les données d'une colonne correspondante. On a trouvé le numéro suivant. Nous avons retourné la valeur de l'année à partir de la colonne suivante. C' est ce que signifie cet index de colonne de deux. Donc, ce qui me permet de faire est de lier la valeur de l'année à mon petit spinner ici et de le rendre juste un peu plus convivial à la page à travers les cinq années de mon échantillon. Donc, nous l'avons là. Il s'agit d'une excellente façon de visualiser les tendances au fil du temps, à l'aide de graphiques en zones superposées animées 7. Créer un tableau de tableau dynamique partie 1: les deux prochaines démonstrations montrent tout sur la création de tableaux de bord dynamiques. Et c'est un sujet vraiment chaud dans Excel car il vous oblige à lier un certain nombre de compétences différentes, vous devez utiliser des formules et des fonctions pour extraire dynamiquement les bons morceaux de vos données . Vous devez utiliser des graphiques et des graphiques pour visualiser vos données de manière significative, et vous devez combiner tout cela pour transformer essentiellement un ensemble de données brutes en une interface utilisateur propre et interactive. Donc, dans l'onglet de tableau de bord dynamique construit un cadre simple pour tableau de bord que nous allons commencer à remplir Et si vous cochez cette petite boîte plus, apparaître au-dessus de la colonne que qui va réellement révéler les données brutes, qui à ce stade vit dans les colonnes a à P. Et juste si vous êtes curieux de savoir comment configurer cela, vous pouvez simplement sélectionner n'importe quel regroupement de colonnes ou de lignes. Ensuite, dans l'onglet données, il y a cette option de groupe ici, et cela les consolide simplement pour que vous puissiez les masquer de la vue. Dans ce cas, j'ai mes données brutes ici, côte à côte avec mon tableau de bord. Mais évidemment, vous pouvez mettre vos données brutes sur un onglet différent. Si vous choisissez, c'est tout à fait à vous de décider. Donc les données que nous examinons ici sont des données de baseball par joueur. Nous avons l'année, l' équipe et la ligue, suivis d'un certain nombre de métriques offensives, matchs à chauves-souris, de courses , de coups , de doubles, triples et ainsi de suite. Et puis ici, dans le petit cadre que j'ai commencé à construire, j'ai une cellule de validation de données où l'utilisateur peut sélectionner n'importe quel joueur. Si vous voulez voir la liste des sources pour cela, allez dans la validation des données de données. Et voici la liste des sources AI à travers un I 100 et, comme vous pouvez le voir ici, ont juste caché la colonne pour le garder hors de vous. Mais voici ma liste de joueurs. J' ai également une liste de mesures uniques, qui seront utilisées plus tard. Alors cachons ça à nouveau et commençons à construire ça. Donc, pour extraire les données, je vais utiliser une de mes fonctions préférées appelées quelques if. Et ce que fait certains if, c'est qu'il extrait ou additionne les données d'une colonne donnée sous un certain nombre de critères spécifiques. Donc, dans cette cellule, voici cinq. Ce que je veux faire, c'est quelques données de ma colonne à chauve-souris, qui est juste ici, et l'appeler F où deux critères sont vrais. Le premier est que le joueur est égal au nom qu'un utilisateur a sélectionné et l'année est égale importe quelle valeur correspond dans la colonne sont donc de faire que je vais taper égal à quelques if et mes quelques plages Mon à la colonne chauve-souris. Donc, sélectionnez toute la colonne F Je vais le réparer en appuyant sur le raccourci F 4. Et maintenant les critères Range numéro un. Utilisons le nom du joueur est notre premier critère. Donc, mes critères varient la colonne de nom et un fixe celui-là aussi. Et le critère avec la valeur dans cette colonne doit être égal est tout ce qui est dans la cellule s à celui-ci sera également fixé. Maintenant, ma deuxième gamme de critères va être ma colonne d'année, qui est B à 4 pour le réparer et les critères qui doivent être vrais ici. Remarquez comment mes formules bloquant la cellule sont de cinq ans. L' accès Soto est cinq. Je pourrais juste flèches vers la gauche, et ça va remplir nos cinq dans ma formule. Maintenant, au lieu de réparer l'art 5 entier. Je veux appuyer sur F quatre pour parcourir mes différents types de référence, et je veux seulement corriger l'Are. Et la raison pour laquelle je fais cela est de sorte que, lorsque je fais glisser cette formule vers le bas, il va lire de nos six sont sept R huit ou neuf et tirer des données pour l'année correcte. Si je réparais l'Are et les cinq, il lirait toujours les données de 2010, peu importe où cette formule vit. Et la raison qui a fixé le morceau de colonne de ce est de sorte que je peux faire glisser cette formule vers la droite et l'avoir toujours lu les années dans la colonne sont au lieu d'avoir cette colonne décalage orteil s ou thé ou vous, le point est référencé. types fixes et relatifs sont incroyablement importants, surtout lorsque vous répliquez des formules à plusieurs endroits. Et avoir une compréhension très ferme des types de référence vous aidera à travailler vraiment, vraiment efficacement. Donc, ce certains sa fonction est bon d'aller dans une presse entrée, puis fait glisser cela vers le bas. Comme vous pouvez le voir, il remplit les données à chauve-souris pour Albert Pools pour chacune de ces six années de l'échantillon. Et maintenant, juste comme un raccourci rapide parce que j'ai défini mes types de référence d'une manière intelligente. Je pourrais simplement copier ces valeurs, les coller ici et maintenant la seule pièce que j'ai besoin de changer est la plage de somme. Donc, au lieu de F qui était ma colonne de chauve-souris Maintenant, je veux un H, qui est ma colonne de hits et les autres critères le nom du joueur et l'année seront vrais pour toutes ces mesures. Donc c'est la seule chose que j'ai besoin de changer ça avec une pression de portée, appliquer ça et je vais répéter ce processus pour toutes mes volumétriques. Donc, les courses, doubles, triples à domicile, bases volées de R B I, pris voler des promenades au cas où vous voyez aussi que j'ai trois indicateurs de taux ici, la moyenne de batte , base volée pourcentage et les retraits des pieds marche. Ceux-ci seront calculés en utilisant différentes fonctions plus simples. Donc maintenant, je vais juste passer par et continuer avec mon fils si pour mettre mes volumétriques en place que je puisse juste coller ça tout le long et ensuite juste un par un, changer ma référence de colonne. Donc les courses sont dans la colonne G. Alors changez les deux g doubles n'appellent pas. Je change donc l'âge à et je triples R et J H 20 J. Et dans ce cas, avez-vous remarqué que j'ai un certain nombre de mesures dans le même ordre que double triples home runs, R B I base volé volé volé voler sont tous dans le même ordre ici. Donc, un petit raccourci supporte réellement la référence fixe de ma colonne et cela permettra à ce j de passer à un k, l m et n. Et comme vous pouvez le voir, regardant la formule telle qu'elle page vers la droite, vous verrez que c'est ce qui se passe. Et maintenant, les marches sont en o et les retraits. Eso Samos K n'est pas p , donc voilà. J' ai fait toutes mes mesures de volume. Faites glisser tout ça vers le bas, faites-le glisser vers le bas, et j'appliquerai mes statistiques de taux. Ces champs d'air calculés, donc les moyennes peuvent juste égaler les coups sur les chauves-souris. Et un conseil qui est vraiment utile ici est parce que je construis un tableau de bord où l'utilisateur pourrait être en mesure de sélectionner un joueur qui n'a pas de valeurs dans chaque cellule. Au lieu de permettre à cette colonne calculée de renvoyer un N laid une erreur de référence. Je vais juste envelopper cette fonction dans une déclaration if error. Donc, si l'erreur ouvre les parenthèses T cinq divisé par s cinq et la virgule vous amène à cette valeur si pièce d'erreur et je veux juste retourner un petit tiret entouré de guillemets. Donc maintenant, si j'arrive à un joueur qui n'a pas de valeurs pour une année donnée, cela montrera juste un petit tiret, qui est beaucoup plus poli que la fonction d'erreur traditionnelle. Donc la même chose avec le taux de base volé, ça va égaler les bases volées divisées par parenthèses ouvertes, bases volées et le vol capturé, ce qui est total intense. Et je pourrais le garder comme ça ou même cas, je peux l'envelopper juste après le signe égal dans si l'erreur s'ouvre, virgule, le tiret et les guillemets de Prentice . Et là, vous l'avez et même affaire exacte Strikeout problème de marcheur d'orteil. Ce sera un e divisé par un D, et j'utiliserai la liaison là-bas aussi. Donc là, vous allez maintenant pour mes totaux pourrait juste utiliser certains problèmes de fonctions. Mes volumes et moi pourrions juste le rythme vers le bas et il apportera mes fils et puis petit pourboire ici. Non. Maintenant, la taille de la police est plus grande et en gras dans cette ligne. Si je veux appliquer cette fonction sans avoir à la réécrire ici, je peux appuyer sur le contrôle C et ensuite tout utiliser. Raccourci de pointe, Ault H v f Et qui colle juste la formule tout en conservant le formatage de la cellule dans laquelle vous collez. Tellement gentil petit tour leur contrôle. Voir Ault, H B f et dernier mais non le moindre, toute la VF. Et il y va. Et j'ai toutes mes données brutes remplies de sorte que vous pouvez sélectionner n'importe quel joueur ici et toutes vos données seront mises à jour en conséquence. Donc remarquez que pour le pourcentage de base volé Billy Butler a plusieurs années où il n'a pas volé ou tenté de bases volées. Ces cas, vous obtenez juste ce joli petit tableau de bord, qui est super propre au lieu de la déclaration d'air. Maintenant que nous avons des données brutes, je veux commencer à utiliser quelques techniques pour attirer l'attention sur des domaines qui nous intéressent. Donc, disons que la moyenne R B I et le ratio de retrait par marche sont en quelque sorte nos indicateurs clés ici. Vous savez, une chose facile à faire serait juste de les rendre audacieux pour attirer davantage l'attention sur ces domaines. Une autre chose que nous pouvons faire est d'ajouter un formatage conditionnel avec une belle échelle de couleurs ici pour attirer l'attention sur les années où ce joueur a fonctionné particulièrement bien ou mal. Et vous pouvez faire quelque chose comme ça avec un orteil vert blanc. Ce qui est bien, c'est que ça peut gérer cette règle. Sélectionnez la règle que je viens de créer et modifiez-la de sorte qu'au lieu de passer du vert au blanc il y a du vert au bleu clair, qui correspond aux cellules qui l'entourent, cela le rend juste se fondre bien. Ici, je peux simplement sélectionner ce tableau dans l'onglet d'accueil. Je peux cliquer sur le bouton peintre de format et il suffit de cliquer sur C cinq, et cela appliquera le même formatage. Sachez qu'il a aussi changé le nombre. Il suffit donc de formater les cellules et de changer le nombre à zéro décimale. Et voilà que vous allez maintenant pour le ratio de retrait à marche. Je veux faire quelque chose de très similaire, mais dans ce cas, nombre plus élevé est pire parce que cela signifie que vous frappez plus souvent que vous marchez . Donc, je veux la même mise en forme conditionnelle juste inversée de sorte que les nombres verts des nombres inférieurs afin que je puisse revenir à mes échelles de couleur et je vais juste faire un blanc au vert, qui est l'opposé de l'orteil vert blanc et puis même affaire. Gérer ça. Modifiez la règle et nous allons changer le maximum à ce bleu clair pour qu'il se fond vraiment bien. Nous allons donc insérer des graphiques combo. Je vais les construire à partir de zéro, commençant par la colonne. Redimensionnons ici. Je vais sélectionner les données où mon premier Siri sera touché. Mes valeurs sérieuses seront vend t 5 à t 10 et mes valeurs ex seront mes années 2010 à 2015. Ajoutons un deuxième Siris pour la moyenne de frappeurs. Mes valeurs seront vous cinq là, vous 10. Mon axe des x sera le même pendant six ans dans la presse d'échantillons. OK, aller aux outils de graphique a changé le type de graphique deux combo. Et ici, je veux que ma moyenne soit une ligne sur l'axe secondaire compresse bien là. Et maintenant, il se résume juste au formatage. Donc, ajoutons quelques éléments vont ajouter le titre de l'axe vertical primaire, qui est hits. Je vais ajouter un titre d'axe vertical secondaire pour la moyenne. Je vais réduire un peu la taille de mes étiquettes d'accès. Rendez mes années audacieuses et réduisez celles. Maintenant, faisons quelques données. formatage sérieux rétrécit un peu l'écart. Qu' est-ce que Phillies avec un bleu clair et nous allons format sont couchés pour être lissés bleu foncé deux points avec puis sur le dernier point de la ligne. Ce qui est ajouté étiquette de données Et nous nous conformons à cette étiquette afin qu'elle soit au-dessus du point de données qui a un remplissage d'arrière-plan de bleu foncé. Maintenant, le texte est blanc et gras. La dernière pièce à ce sujet. Nous pouvons simplement ajouter un marqueur ici, auquel vous pouvez accéder ici sur les options de marqueur. Faisons construire dans le diamant taille sept White Phil et une ligne bleu foncé solide qui fait 1,5 points de large et dernier, mais pas le moindre, va juste pour lui à la zone de carte et se débarrasser de la frontière. Et là, vous l'avez. Donc belle représentation visuelle peu propre des hits et moyenne tracée au fil du temps. Et maintenant, parce que mes données sont dynamiques, cela signifie que mon graphique est aussi dynamique. Maintenant, tout ce que nous devons faire est de copier coller cela et de sélectionner des données différentes. Siri est donc dans ce cas, je veux montrer à des chauves-souris et r B je fais glisser deux coups à des chauves-souris. Je vais faire glisser ma ligne de Teoh R B I. Maintenant, tout ce que nous devons faire est de faire quelques ajustements au formatage. Donc les coups deviennent aux chauves-souris. Moyenne devient R B I. Et modifions le formatage juste un peu. Nous allons faire un vert clair avec une ligne vert foncé, et nous devons éditer le marqueur. Linus. Eh bien, donc les options de marqueur Border sera un vert foncé. Et puis la même chose avec le label Phil. Derrière l'étiquette sera un vert foncé. Donc maintenant, nous avons construit un deuxième graphique qui change à nouveau pour afficher de nouvelles mesures sur les chauves-souris et R B I. Et répétons ce processus une fois de plus pour montrer les bases volées ici dans la colonne A et le taux de base volé. Bonne utilisation du graphique combiné parce que nous avons deux mesures qui sont sur des échelles complètement différentes et que vous connaissez le foret. Changeons le bébé à un SB un r B i Teoh sp pour cent. Nous allons changer la couleur pour dans ce cas, un orange clair et la ligne en orange foncé. Nous devons changer votre marqueur, et notre fond d'étiquette de notre marqueur serait de nouveau orange foncé, et nous allons éditer ce marqueur avec une ligne unie orange foncé. Maintenant, vous pouvez jouer avec l'alignement un peu ici, mais c'est votre idée de base. Donc, nous avons construit un tableau de bord vraiment sympa, vraiment propre qui montre toutes ces données différentes pour n'importe quel joueur que vous choisissez et fait un très bon travail en attirant l'attention sur les mesures clés et la façon dont ils l'ont essayé au fil temps. C' est donc la première partie de la création de tableaux de bord dynamiques et Excel. 8. Créer un tableau de tableau dynamique partie 2: Ok, donc bienvenue à la deuxième partie de notre démo interactive du tableau de bord. L' objectif de la deuxième partie est essentiellement d'ajouter un peu plus d'interactivité au tableau de bord que nous avons construit dans la première partie. Donc, en regardant notre tableau de bord original, vous verrez que nous avons choisi de visualiser trois paires différentes de mesures. On montre une moyenne de coups sur les chauves-souris, un R B I, et une base volée dans le taux de base volé. Mais que se passe-t-il si un utilisateur préfère voir des courses et des prises voler ou tripler et des prises de grève ? Et s'ils veulent voir une combinaison différente des peignes que nous avons inclus ici ? Pour permettre que cela se produise ? Nous devons créer une version dynamique de l'un de ces graphiques afin qu'un utilisateur puisse sélectionner deux mesures et que ces mesures soient visualisées. Alors commençons par réorganiser un peu les choses. Je vais cliquer sur ces deux tableaux, le rétrécir un peu, et je vais juste repositionner l'un au-dessus de l'autre. Et puis ce 3ème 1 est celui que je vais transformer en mon graphique dynamique quelque chose faire glisser ceci et faire ce genre de graphique en vedette dans le tableau de bord Et tout d'abord, nous devons dire à l'utilisateur qu'il peut sélectionner des métriques ici. C' est donc une métrique sélective ici dans X 14. italique gras. Donnez-lui une ligne, et je remplirai juste la cellule juste à côté. Pourquoi 14 avec le jaune clair Phil sorte de correspondre à cette cellule de validation des données apparaissent nous à Et puis même affaire ici dans la colonne A Une métrique de sélection pour peut utiliser le petit format. Raccourci peintre. Là, c'était le même Phil. Et maintenant, nous allons simplement les transformer en cellules de validation de données avec des listes déroulantes, tout comme le nom de notre joueur. Si vous vous souvenez, je peux juste ne pas hygiénique ces deux colonnes et ma liste de métriques vit ici et les colonnes A. H Donc dans, pourquoi 14 Je vais aller dans la validation des données de données. Autorisons une liste et notre liste de sources comme un H deux à H 15. Là, vous allez et puis sélectionnez métrique à la même liste de validation des données de processus et au même tableau source. Maintenant, un utilisateur peut sélectionner l'une des mesures et choisir n'importe quelle paire. Donc, faites un peu de formatage là, juste pour centrer et audacieux de ce peintre et quand nous cachons notre liste de sources. On n'a plus besoin de ça. Ok, donc maintenant vous pouvez sélectionner deux métriques et nous voulons que ces métriques soient visualisées ici dans ce graphique au lieu d'avoir la lecture du graphique à partir d'un tableau fixe de la table ci-dessus. Il y a quelques façons de le faire. Et la première façon est d'adopter une approche similaire à celle que nous avons adoptée avec notre graphique en zone de chevauchement animé , qui est de créer un nouveau tableau de données source dynamique en dessous du graphique. Et si nous voulons le faire, nous pourrions simplement taper nos années et le faire glisser vers le bas et remplir un Siri et créer des en-têtes basés sur les sélections de mesures un et deux. Et maintenant, ce que nous pourrions faire est d'utiliser une combinaison de fonctions comme index et match toe, en fait remplir les données correctes ici en indexant ce tableau et en faisant correspondre à la bonne année dans la colonne sont et métrique dans la rangée quatre. Cela ressemblerait à quelque chose comme ça équivaut à l'index. Voici l'ensemble de notre tableau de données source. Réparez-le avec ces quatre. Le numéro de route vers lequel nous voulons descendre. On va correspondre à l'année ici. Répare le X on va l'égaler dans l'année. tableau source, qui est notre cinq. Il y a 10 quatre zéro signifie juste que je voulais une correspondance exacte, puis une virgule sur quelle colonne je veux tirer de mon tableau. C' est une autre fonction de correspondance, Remini correspondant à l'en-tête de métrique. J' ai réparé la route 32 parce que mes en-têtes vivent toujours sur la route 32. Je vais chercher cela dans le tableau d'en-tête de métrique, qui est de s quatre à un f pour fixé avec F quatre zéro commun, sa correspondance exacte proche des parenthèses. Frappe, entre. Et puis parce que j'ai bien défini mes types de référence, je peux l'appliquer tout le chemin aux deux métriques et essentiellement Qu'est-ce que cela fait, c'est qu'il extrait les données d'ici en fonction des deux métriques sélectionnées donc à des chauves-souris à 42 6 11 que correspond hits 50 à 1 85 hits, 50 à 1 85 Comme vous pouvez le voir, modifiez cette liste déroulante et les données et les données source changent également. Donc c'est l'option 1. Je pourrais simplement faire glisser ma référence source vers le bas vers ce tableau dynamique que j'ai construit . Et pendant que j'aurais fini, je ne veux pas adopter cette approche. Je veux vous montrer une façon un peu plus élégante de le faire sans avoir à construire ce rayon de données source flottante aléatoire sous mon graphique. Non pas que ce soit la pire chose au monde. Tu sais, tu pourrais juste jeter une police blanche dessus et personne ne saura qu'elle est là. Vous savez, le danger de faire cela est que quelqu'un ajoute inévitablement une ligne ou une colonne ou ne la voit pas et supprime quelque chose. Et boum, il y a tout ton dur labeur. C' est donc une option. L' option que je préfère que je vais vous montrer maintenant est d'utiliser la fonction de correspondance, mais de l'utiliser dans le contexte d'une fonction de décalage que nous allons construire dans une plage nommée . Et ce que cela fera est de nous permettre d'extraire dynamiquement des données de la bonne colonne dans son graphique sans avoir à construire un autre tableau de données source quelque part. Alors allons-y. On va aller dans notre onglet de formules, définir un nouveau nom, et on va appeler cette métrique. Nous allons répéter le processus pour métrique à éventuellement et au lieu d'une référence fixe ici. Nous allons commencer par taper une fonction de décalage et dans la fonction de décalage ou point de départ sera la cellule s cinq, puis le nombre de lignes que nous voulons déplacer. Ce point de départ est zéro, car notre premier point de données devrait toujours être le deuxième point de 2010 juste là dans la ligne 5. Mais alors, si je virgule sur le décalage de la colonne, C'est là que nous devons faire preuve de créativité. Parce que si vous y pensez, quand un utilisateur sélectionne un B dans la liste déroulante et vend, pourquoi 14 ? Ensuite, nous ne voulons pas déplacer notre point de départ du tout parce que chez les chauves-souris sont dans la colonne s. Si, cependant, l'utilisateur a sélectionné H ou hits ici dans les EAU 14 alors nous voulons déplacer notre colonne 140.1 de départ vers la droite afin que nous accédez à notre colonne de hits. Nous savons donc que cette partie de la fonction doit être dynamique. Et pour ce faire, nous allons insérer une fonction de correspondance et tout ira. Voici essayer de faire correspondre la valeur entrée par l'utilisateur. Pourquoi 14 dans le tableau d'en-têtes dans la ligne quatre et notez que vous avez besoin d'un tableau unidimensionnel pour que la fonction de correspondance fonctionne. Et le dernier morceau de match est le type de match, qui est zéro pour la correspondance exacte. Fermez les parenthèses et on a presque fini. Sauf qu'il faut faire un dernier ajustement, qui consiste à en soustraire un. Et si vous y pensez, c' est parce que si l'utilisateur a sélectionné un être la fonction de correspondance dit Ok, je vais trouver un B dans ce tableau et cette ligne. Pour là, il est dans la première cellule de ce tableau. Ainsi, la fonction de correspondance renvoie le numéro un et le numéro un dans le contexte d'une fonction de décalage indique excel. Ok, Maintenant, déplacez notre colonne 10.1 de départ vers la droite, ce qui vous amènerait à la colonne des hits, où nous voulions vraiment les valeurs dans ces chauves-souris. Donc, moins un corrige juste pour cette virgule à la seconde, le dernier composant de la fonction offset, qui est l'hyperfix de notre tableau résultant. Dans ce cas, nous voulons six points de données, un pour chaque année. Donc, la hauteur sera toujours six et la largeur sera toujours juste une colonne de large proche. Les parenthèses appuyez sur OK, et nous avons la métrique 1 définie. Faisons la même chose pour trouver un nom pour la métrique à. Et vous devriez être un pro à ce stade, nous allons utiliser à nouveau la fonction offset. Et vraiment, la seule chose qui va changer, c'est à quelle mesure on correspond. Dans ce cas, ce sera un B 14 au lieu de Y 14. Donc, nos points de départ là-bas, nous ne voulons pas déplacer un décalage de colonne Rose est une fonction de correspondance de la valeur et un B 14 dans notre ligne d'en-tête correspondance exacte. Fermez une parenthèse moins une. La hauteur est de six. Avec celui-ci, fermez-le. Ok, Maintenant, cela nous amène à notre dernière étape, qui est juste éditer les Siri dans ce graphique pour lire à partir de nos nouvelles plages nommées au lieu de ces plages fixes ici. Donc c'est juste. Cliquez sur sélectionner les données et ici où il est dit bases volées. Je vais éditer ceci et pour toutes les valeurs. Après le point d'exclamation, je vais remplacer par la métrique 1 juste le nom de notre premier nom Ray. Et pour notre 2ème 1 supprimez tout après le point d'exclamation. Appelez-le en métrique pour appuyer sur. OK, les deux cas, mon axe X restera fixe comme seulement ces six années. C' est bon. Je peux appuyer. D' accord. , Une chose que vous avez peut-être remarqué,c'est que les noms des Siri n'ont pas changé leur pourcentage de base volé. Mais dans ce cas, je suis en train de visualiser à chauves-souris et exécute donc Excel ne sera pas en mesure de changer dynamiquement vos étiquettes d' axe de la manière traditionnelle. Donc on va faire un peu de travail et supprimer les étiquettes ici et dégager un peu de place pour nous-mêmes. Et je vais avoir un peu de fantaisie ici et insérer une boîte de texte. Et je peux définir cette zone de texte pour juste égale à la valeur dans une cellule. Donc je vais mettre cette boîte de texte à égalité avec ce qui est dans la vie 14 et je vais la centrer. On y va. Et nous faisons pivoter ces 90 degrés, faisons un peu de formatage pour se débarrasser du Phil et de la bordure. Je vais faire glisser ça ici comme un titre d'accès improvisé, puis le copier et le coller à nouveau, en faisant glisser ici. Retournez-le et définissez. Celle-ci est égale à B 14 qui est la deuxième mesure sélectionnée par l'utilisateur. J' ai donc créé mes propres versions piratées des étiquettes d'accès. Mais ce que cela fera est de permettre à ceux-ci de changer lorsque l'utilisateur modifie la liste déroulante. Donc, comme vous pouvez le voir ici, je regarde les chauves-souris et R B. Je suis juste pour refléter ce tableau inférieur, et il semble qu'il correspond bien. Essayons de faire la même chose avec les meilleurs résultats du graphique et la moyenne. Là, tu vas à 71 tous les matchs et tu y vas. Nous venons donc d'ajouter une nouvelle couche d'interactivité cool pour faire un tableau de bord vraiment dynamique face à l'utilisateur dans Excel. 9. Formatage dynamique de valeur: D' accord. Nos prochaines démonstrations sur le formatage dynamique basé sur la valeur, qui tente essentiellement de formater des données individuelles, pointent dans un Siri avec des règles de formatage uniques basées sur sa valeur. Donc, pour vous donner un exemple, regardons notre onglet de mise en forme basée sur la valeur, et voici juste deux colonnes de données regardant le volume de clic par jour. Donc, si je devais sélectionner et être et juste insérer votre graphique de base maintenant, comme vous pouvez le voir si je clique avec le bouton droit de la souris sur les Siri et le format, toutes les modifications que je fais ici comme Phil, par exemple, sont appliquées à l'ensemble de Siri. Mais que se passe-t-il si je veux mettre en évidence des points de données individuels ou des colonnes en fonction de leurs valeurs ? Donc, disons que je veux mettre en évidence tout point où le volume de clic est supérieur à 8000. Donc, ce point, celui-ci, celui-ci et ainsi de suite et ainsi de suite, vous pouvez sélectionner individuellement des points de données et modifié manuellement . Ils sont Phil, mais ce n'est évidemment pas une solution évolutive, et cela ne vous permet pas de modifier ou d'ajuster vos seuils, automatiser ce processus. Je vais défaire ça. Changez ce que nous avons essentiellement besoin de faire ici. Il est divisé ou segmenté nos Siri originaux en plusieurs sous-séries qui tombent dans les seaux que nous voulons formater avec des règles uniques. Donc, par exemple, dans ce cas, si je voulais appliquer ce seuil de 8000 dont nous venons de parler, je pourrais créer deux nouvelles colonnes en C et D, où une colonne remplit le volume de clic pour n'importe quel dates où les cliques sont supérieurs 8000 et la deuxième colonne remplit les clics pour toutes les dates ou clics ou moins de 8000, puis mettre à jour mon graphique pour lire à partir de ces deux colonnes au lieu de ma colonne d'origine. De cette façon, il pourrait se former à ma colonne supérieure à 8000, un appelant et ma colonne inférieure à 8000, une autre couleur. Donc nous allons faire quelque chose de similaire mais devenir un peu plus fantaisiste ici. Et ce que je veux faire dans ce cas, c'est créer quatre sous-segments de données basés sur mes volumes de clics . En d'autres termes, je veux calculer les quartiles 123 et 4, puis formater ces tuiles de cour en tant que données individuelles . Chez Siri. Donc, pour le faire dans la cellule C 1, je vais entrer un nouvel en-tête de colonne appelé rang de pourcentage, et ici je vais utiliser la fonction de rang de pourcentage excels où mon tableau appelle B et ma valeur ou mon ex est la cellule droite à gauche. Donc, dans ce cas, battre aussi, et je peux fermer ce format en pourcentage et l'appliquer vers le bas. Et essentiellement, ce que cela fait, c'est me dire où chaque valeur et Colin être tombe le long d'une échelle de 0 à 100% . Ainsi, le plus grand volume de clic dans ce cas 9972 équivaut à un rang pour cent de 100 et le plus petit, qui dans ce cas est 5029 équivaut à zéro. Et je vais utiliser ces valeurs de pourcentage pour diviser ce Siri original en quatre sous-segments basés sur des tuiles de cour. Donc, mon en-tête pour D un sera 10.25 e un sera 10.5, f sera 0.75 et G sera un, et je pourrais juste saisir le peintre de format, donner à ces mêmes types de formats d'en-tête. suffit d'étendre les deux points décimaux là, et maintenant nous devons écrire quelques fonctions qui regroupent essentiellement les nombres de clics dans l'une de ces quatre colonnes en fonction de sa valeur, Nous devrons donc utiliser quelques instructions conditionnelles et logiques les opérateurs pour que cela se produise . Donc, en commençant la cellule D à nous dirons, si le rang de pourcentage dans la colonne C est inférieur ou égal à la valeur dans mon en-tête ici d un, alors je veux retourner le volume de clic. Sinon, je veux laisser ça vide. Et je pourrais le faire juste en tapant deux guillemets consécutifs. Et avant de verrouiller cela, je veux faire quelques ajustements de type de référence. Donc, veillez à ce que je veux juste verrouiller la colonne parce que finalement je veux appliquer ces formules colonnes croisées et lignes vers le bas. J' essaie donc d'être très conscient du type de types de référence que j'ai définis. Et puis pour D un, qui est mon en-tête Headers vivent toujours dans la rangée un donc je peux juste réparer la rangée et laisser les parents D le décalage diacre que je déplace colonne pour l'appeler et ensuite similaire à la mer à ici . Je vais verrouiller juste la colonne pour le B parce que je veux que cette formule augmente quand je me déplace vers le bas. Tellement touché. D' accord ? Et maintenant, si j'applique cela bas, vous remarquerez que les seules valeurs de remplissage des ventes sont les cellules où le rang de pourcentage tombe en dessous de 25. Donc il y a une 2024 15 etcetera. Donc maintenant un cas similaire ici, nous avons juste besoin d'écrire une nouvelle fonction parce que ici nous avons besoin de valeurs qui sont supérieures à 0,25 mais inférieures ou égales à 2,5. C' est un genre de cas similaire ici, mais nous allons utiliser if avec une déclaration conditionnelle de fin. Et notre première logique est si le rang de pourcentage est supérieur à 0,25 et notre deuxième logique est ce pourcentage loin. Le rang est inférieur ou égal à 0,5, puis nous pouvons fermer. La première valeur de parenthèse, si true, sera notre valeur de clic, qui n'est pas battue à la valeur de false sera vide. Et encore une fois, je vais passer en revue et éditer mes types de référence en utilisant le raccourci F 4. Donc pour C 2, je veux juste réparer la colonne. Tu veux ? Je veux juste réparer la ligne à nouveau, voir juste la colonne e un juste la ligne et ensuite être juste à la colonne, puis appuyez sur Entrée et je devrais être capable de l'appliquer encore et vers le bas et avoir tout rempli correctement. Donc maintenant, si tout a fonctionné correctement, chaque point de données de Colin est notre original. Celle de Siri a été dévolue. C' est dans l'une de ces quatre colonnes D E. F ou G. Donc, nous avons tous les points de données que nous avions un Siri original et une colonne B. Nous les avons juste ventilés par valeur, et cela nous permettra de créer n'importe quel règles de formatage que nous choisissons sur chacun de ces Siri individuellement. Alors allons de l'avant et insérez un nouveau graphique en colonnes et ajoutez-y un peu de sérieux. Donc, sélectionnez les données dans la première série d'une Siri sera notre premier quartile dans un décalage de contrôle. Flèche vers le bas pour saisir toutes mes valeurs là-bas. Appuyez sur OK, mon axe X. Il y a sur mes rendez-vous, qui sont de deux à un 61. Ajoutez une seconde Siri pour mon 0,5. Tout ce qui tombe dans mon deuxième quartile. On y va encore. Mon axe X dans les états. Je vais répéter ça deux fois de plus pour mon troisième quartile. On y va, un peu manuel, mais parfois il faut faire ce qu'il faut faire. Et enfin, nous ajoutons notre quatrième et dernier quartile avec nos valeurs de la colonne G, et cela devrait à peu près envelopper les choses. C' est OK, alors maintenant nous allons faire glisser ceci pour que nous nous conformions aux données de Siri réduire l'écart avec et réduire le type de chevauchement de réplication. Style de graphique en colonnes d'origine. Et maintenant, lorsque vous sélectionnez, vous remarquerez que vous pouvez sélectionner chacun de ces Siri individuellement, puis vous êtes supprimé de votre volet de mise en forme. Vous pouvez également sélectionner chacune des forces que cela décompose maintenant en Siri individuels que nous nous conformons à quel que soit notre choix. Alors dans ce cas pour 0.25 pourquoi ne pas lui donner un Phil solide avec une sorte de gris clair ? Ensuite, pour 0,5, faisons un remplissage solide avec un vert clair 4.75 Nous ferons une sorte de vert plus foncé. Et puis enfin, pour une, donnons-lui le rêve le plus sombre tous, et maintenant vous appuyez sur OK. Comme vous pouvez le voir, ce Siri est exactement le même. Que nous choisissions le composite original Siri dans la colonne B ou la combinaison des quatre sous-Siri que nous avons construit, qui, lorsqu'il est qui, agrégé, reflètent également Colin être et vous pouvez voir que ce sont les mêmes valeurs. Sauf que maintenant j'ai des règles de formatage individuelles basées sur ces quatre tuiles de base de valeurs, et la dernière chose que nous pouvons faire juste pour rendre cela un peu amusant est au lieu du codage dur , la colonne de clic. Je veux juste te montrer parce que c'est une belle façon visuelle de tester que ce que nous avons fait est vraiment dynamique. Alors remplacez-les par un générateur de nombres aléatoires. Quelqu' un utilise du rand entre 5000 et 10.000. Vous pouvez faire n'importe quelle valeur ici si vous voulez, et quand je le fais et l'applique maintenant, ces nombres aléatoires d'air qui vont aléatoirement chaque fois que je calcule la feuille de calcul. Donc, je vais dans les formules et je viens de frapper. Calculez maintenant, encore et encore. Et comme vous pouvez le voir, quelle que soit la valeur d'une date ou d'un point de données particulier, il sera toujours formaté correctement. Basé sur ces évasions de quartile. C' est une façon vraiment cool d'ajouter une forme unique de mise en forme dynamique à vos visuels Excel 10. Souligner dynamique une série de données: notre prochaine démo est la main vers le bas l'un de mes favoris. Il s'agit de mettre en évidence dynamiquement une donnée spécifique, celle de Siri. Donc, dans l'onglet d'élection dynamique et sérieuse, vous verrez quelques données familières. Nous avons nos ventes de billets de cinéma par mois pour quatre genres différents. Comédie, thriller, documentaire et romance et ce que je veux faire ici est de construire un graphique de zone empilée qui dynamiquement ou met en évidence le genre que l'utilisateur a sélectionné dans cette cellule de validation de données ici et battre à. Donc pour ce faire, nous allons avoir besoin de créer deux versions de chacune de ces quatre versions. Siri est celui pour définir la mise en forme lorsque ce genre n'est pas sélectionné et Selby 2 et le second pour définir la mise en forme lorsque ce genre est sélectionné. Donc, en d'autres termes, chacun de ces quatre genres aura une version remplie de lumière et une version foncée, et seule la version foncée remplira les données et sera visualisée dans le graphique lorsque ce genre est sélectionné ici dans la liste déroulante . Donc, pour lancer les choses, je vais attraper chacun de nos genres et juste coller deux versions de chacun dans un nouveau tableau de données source ci-dessous et ce que nous devons faire pour remplir ces données source est utilisé quelques instructions conditionnelles qui retourneront données de ce tableau ci-dessus, mais seulement dans les cas où ce genre a été sélectionné dans Selby pour le cas contraire. Je veux retourner des valeurs vides donc je vais commencer par la comédie égale si la liste déroulante Selby trop fixée avec ça pour égale à ma valeur dans un 28 et je vais corriger juste la colonne là, si c'est vrai. Donc, en d'autres termes, si l'utilisateur a sélectionné la comédie, alors je veux tirer la valeur de ma ligne comique dans mes données source Fixer la route ici. Sinon, je veux que ce soit vide afin d'être utilisé pour les guillemets et le fermer. Et puis si je l'ai glissé dessus, dirait qu'il est formaté en monnaie pour une raison quelconque. Alors allons de l'avant et, euh, euh, changez ça pour jouer le numéro. Alors, voilà. Parce que la comédie est sélectionnée, toute cette rangée de données a été remplie. Si l'utilisateur a sélectionné thriller voir que les données disparaissent. Donc maintenant j'ai juste besoin d'une formule de plus pour définir fondamentalement le contraire. Je veux dire, un copier-coller. Celui-ci dit fondamentalement, si b à la liste déroulante n'est pas égal à 8 29 puis tirez dans mes données de vente de billets d'en haut, sinon vide. Donc, tout cela sera vide en ce moment parce que la comédie est sélectionnée ci-dessus. Mais encore une fois, si je suis tombé à Thriller, par exemple, vous voyez les données juste déplacées de la ligne sélectionnée à la ligne non sélectionnée. Maintenant, nous avons juste besoin de répéter le processus pour les trois autres genres. Je vais emprunter les formules qu'il vient d'écrire pour la comédie et les coller dans chacune des trois autres affaires et tout ce que j'ai à faire pour Thriller. Par exemple, dans la ligne 30, il pourrait appuyer sur F 2 pour forer dans la formule. Tout ce que j'ai à faire est de faire glisser cette référence jusqu'à la ligne du thriller dans ces deux cas, et voilà. Je peux attraper le thriller Rose, le traîner jusqu'au mois de décembre. Et comme vous pouvez le voir maintenant, ce thriller est sélectionné et Selby, aussi. La rangée supérieure des données de thriller est renseignée dans la rangée inférieure ne renvoie rien si similaire Case avec documentaire glisser vers le bas aux ventes de documentaires dans les deux cas, puis romance sera la valeur en B 26. Maintenant, je peux sécher ces quatre derniers tout le chemin. Et là, il va, ont créé un nouveau tableau de données source en dupliquant les données pour chaque genre et en créant une règle où les données ne seront remplis que si ce genre particulier est sélectionné par l'utilisateur et Selby, aussi. Donc un autre exemple. Si vous allez à la romance, vous verrez la version sélectionnée de chacun des autres genres est en cours de remplissage. Mais la version sélectionnée, la ligne supérieure pour la romance, est maintenant renseignée. Donc, c'est l'ensemble de données qui va maintenant utiliser pour construire sont la zone empilée, et il sera construit sur la base de ces huit données. Siri est même si seulement quatre seront visibles en même temps. Alors allons de l'avant et insérez nos options de ligne empilées zone, et nous pouvons sélectionner les données et juste se débarrasser de ce qu'Excel a essayé d'ajouter. Et maintenant, il peut ajouter mes premiers Siri. Et dans ce cas, je vais lui donner des noms personnalisés juste pour qu'il soit clair quand nous formons leader. Donc ça va être mon premier Siri, qui est la rangée supérieure de mes données comiques donc je vais l'appeler comédie avec un s pour des valeurs sélectionnées sera B 28 à M 28. Mes étiquettes horizontales seront de janvier à décembre. Un peu difficile à voir, mais ça passe par M 22 et laissez-moi juste faire glisser ça hors du chemin pour rendre ça un peu plus facile. Retournez dans les données sélectionnées. Ok, donc j'ai ma comédie Selected Row. Second Row est la comédie D pour D. Sélectionné ces valeurs vivent ici. Appuyez sur OK, valeurs horizontales. Chaque fois qu'on sera les mêmes mois dans la rangée 22. Je vais répéter ce processus pour chaque thriller de Siri sélectionné est mon top thriller Row Horizontal Labels ou Mes mois et Thriller De Selected. C' est ma deuxième ligne de thriller Quand mes valeurs ex sont mes mois Vous pouvez le voir ajouté qu'en ajoutant 1/3 1 Doc sélectionné top documentaire Row Doc De sélectionné. C' est ma deuxième ligne documentaire et vous pouvez voir que nous construisons lentement chaque tranche de notre carte géographique ici la romance sélectionnée et c'est celle qui a actuellement des données parce que c'est ce que notre cellule de validation des données reflète actuellement et enfin, romance de selected, qui est la dernière rangée de notre rayon juste ici. Ok, donc vous l'avez. Nous avons construit sont empilés zone, comme vous pouvez le voir sur Lee quatre. air de Siri étant montré parce que seulement quatre des huit lignes de ce tableau source brut et dynamique contenaient actuellement des données. Et en fait, seulement quatre le feront à la fois. Alors maintenant, nous allons personnaliser les options de remplissage pour chacun des huit possibles Siri, vous pouvez cliquer avec le bouton droit de la souris sur les données de format Siri, et c'est là que cette liste déroulante est incroyablement utile car elle liste tous les huit ici. Et si je devais essayer de faire un clic droit sur chacun des Siri individuels, il n'y a aucun moyen d'isoler et de cliquer sur l'un des quatre qui sont actuellement vides. Donc je vais utiliser ce panneau de contrôle ici. Alors allons juste descendre la liste de haut en bas, en commençant par la comédie et pourquoi choisir une couleur ? Cette comédie représentera si elle est sélectionnée ou sélectionnée, donc je vais lui donner un Phil solide. Pourquoi ne pas dire que la comédie est des nuances de bleu, alors quand la comédie est sélectionnée, je veux lui donner une couleur bleu vif et ensuite nous allons juste descendre la ligne droite. Lorsque la comédie est sélectionnée, donnons-lui une nuance de bleu plus claire. Faisons la nuance la plus légère possible. Nous irons au thriller quand Thriller sélectionné quand je fais thriller Orange et Nous allons thrillers de sélectionné très, très léger Shade of Orange, passer au documentaire documentaire sélectionné 70 Yellow et quand documentaires de sélectionné plus tard Shade of Yellow et puis nous arrivons à la romance, Faisons un vert romance, et quand il est de sélectionné, ce sera un vert plus léger. Ce n'est pas une presse, OK, et comme vous pouvez le voir, puisque trois des quatre Siri qui sont présentés ici ne sont pas sélectionnés et Selby à nous voir la version pleine de lumière de ces Siris dans les Onley Siri qui montre son version plus sombre est la romance, qui est celle sélectionnée. Donc, si nous avons fait notre travail correctement, cela signifie que lorsque l'utilisateur change la valeur dans cette liste déroulante et l'est aussi, il devrait mettre en évidence dynamiquement juste celle-ci. Siri et renversez tous les trois autres. Siri est de retour à leur version plus légère, et il y va. Il y a du documentaire de thriller comique et de la romance. C' est cool ? Donc, enfin, nous pouvons juste faire un peu de formatage ici. Donnons-lui un titre de graphique au-dessus du graphique l'appellera ventes de billets par mois, puis à ce stade, il est vraiment facile de copier et coller ce qui va sur les outils de graphique peut-être vouloir le changer à 100% colonne empilée. Au lieu de cela, vous savez, format de reconnaissance que Siri réduit l'écart quelque chose comme ça et a ensuite changé le titre en pourcentage des ventes de billets par mois. Depuis maintenant, nous regardons 100% empilés et les deux agiront exactement de la même manière. Donc vraiment juste un choix stylistique. Mais là, vous avez une règle de mise en forme personnalisée vraiment unique que vous pouvez appliquer à vos graphiques et graphiques pour vraiment aider à donner vie aux choses. 11. Créer un graphique de Pacing personnalisé: déjà pour une prochaine démo, je vais vous montrer comment construire un tableau de rythme personnalisé vraiment cool, vraiment unique. Si vous ouvrez l'onglet graphique de rythme personnalisé dans notre classeur Excel, vous verrez ici un tas de colonnes de données qui montrent essentiellement le rythme des revenus vers un objectif. Donc, dans la colonne B, ces totaux de revenus quotidiens aériens qui sont codés en dur en tant que valeurs de la colonne C traduisent essentiellement cela en revenus cumulatifs. Jour après jour, on dirait que nous avons un objectif de 150 000 à atteindre d'ici la fin du mois. écart est juste L'écart entre le pourcentage cumulé et objectif du mois est une utilisation vraiment cool des fonctions de temps Dayton pour calculer chaque jour est un pourcentage du mois, puis Target dit fondamentalement, étant donné la distance que vous êtes à travers le mois et compte tenu de notre objectif, combien de recettes devrions-nous générer chaque jour pour maintenir le rythme afin que nous atteignons les 150 km d'ici la fin du mois ? Donc, ce que nous devons faire maintenant est de créer un visuel qui raconte vraiment cette histoire et qui montre clairement comment nous évoluons actuellement dans ce cas pour le mois d'avril. Alors pourquoi ne pas commencer par insérer un graphique en courbes et je vais ajouter manuellement des données  ? Siri est à ça. Le premier Siri que je voudrais ajouter ici est mon objectif, et mes valeurs seront toutes les valeurs de la ligne 2 à 31. Voilà, tu y vas. Et mes étiquettes d'axe X sont toutes avec mes dates et ma colonne A. Je vais ajouter une seconde. Siri est pour cible. Et tu connais la perceuse. G 23 g 31 x axe, mêmes dates, et tout ce que j'en avais encore une pour l'instant, ce qui va être mon chiffre d'affaires cumulé réel. Je vais seulement choisir jusqu'à Rhode 21. Mais quand je sélectionne mes étiquettes d'axe X, j'apporterai tout le tableau don à la ligne 31 parce que je veux fondamentalement, je veux que cette ligne ou l'état de Siri se termine à la date actuelle. Mais je ne veux pas limiter l'axe des X à cette date. Je veux voir l'axe des X s'étendre tout au long du mois. Voici donc notre point de départ, et nous pouvons faire un peu de formatage en ce moment et formater ces données d'or. Siri, tu veux peut-être une ligne verte solide avec un Phil plus léger. Et peut-être utiliser certains de ces types composés où vous pouvez faire une double ligne ou quelque chose comme ça , évidemment totalement à vous pour la ligne cible, le rythme quotidien que j'essaie de frapper. Faisons ce genre de gris clair deux points avec, et c'est une belle opportunité. Utilisez un type de tiret ici car cela aide à indiquer que c'est une sorte de ligne directrice plus que toute autre chose. Et puis, enfin, enfin, je peux modifier ma ligne de chiffre d'affaires cumulé réel. Je veux que celui-ci se démarque. Puisque c'est une sorte de métrique clé ici, faisons une belle ligne bleue foncée, lisse, lisse, et nous pouvons aller de l'avant et ajouter des marqueurs à cela aussi. Tu sais, je suis fan de ces diamants. Battez-le jusqu'à la taille 6. Donnez-lui un Phil solide avec un blanc et une bordure bleu foncé solide . On y va. Ok, donc nous avons tracé trois de ces Siri et nous avons déjà fait un bon travail pour montrer comment un chiffre d'affaires cumulé évolue au cours des premières semaines du mois et comment cela est lié au rythme cible que nous devons suivre pour atteindre notre objectif de 150 k d'ici la fin du mois. Donc beaucoup de gens s'arrêteraient ici parce que c'est un visuel assez solide. Mais c'est l'exercice de niveau suivant. Donc, évidemment, nous devons aller plus loin, et ce que je veux faire ici, c'est mettre en évidence ces lacunes. Donc, lorsque le chiffre d'affaires cumulé tombe sous la cible, je veux que cet espace entre les deux lignes soit formaté en rouge foncé et si le chiffre d'affaires dépasse la cible, je veux que cet espace soit rempli de vert, cible, je veux que cet espace soit rempli de vert, tout comme un indicateur visuel que les choses rythme de l'air dans une direction négative ou positive. La première chose que nous devons faire est de définir ces zones dans un sens mathématique. Et c'est ce que ces quatre colonnes d'espace réservé sont destinées à faire. Sortons ça du chemin. Donc, pour plus bas essentiellement, ce que je cherche ici est à n'importe quelle date donnée. Quel est le point le plus bas que vous avez atteint ? C' est soit un chiffre d'affaires cumulé, soit la ligne cible. Ça doit être l'un des deux, donc tout ce qu'on a à faire c'est un type de fonction ou un minimum d'hommes, ce sera le minimum de revenus cumulés Colonne C ou Target et Colin G. Upper est juste le revers. Donc, à une date donnée, quelle est la valeur la plus élevée ? S' agit-il de l'objectif ou s'agit-il de revenus cumulatifs ? Il suffit donc de taper une fonction max. même idée exacte ici, en dessous et encore. C' est là que nous allons réellement calculer la différence entre ces deux lignes, en fonction des plus élevées qui sont donc sous Target. La colonne est l'endroit où nous voulons remplir des valeurs qui reflètent la différence entre les deux lignes dans les cas où un revenu cumulé est inférieur à la ligne cible. La cible est l'endroit où nous voulons calculer l'espace entre les lignes où le revenu est supérieur ou surpassant la ligne cible. Donc, et vendre J pour affaiblir type égal. Si sont cumulatifs. chiffre d'affaires est inférieur à la cible dans la valeur que nous voulons retourner est la différence entre les lignes, qui est juste la limite supérieure, moins la valeur de borne inférieure. Il faux est juste un vide, donc aux guillemets et puis cas très similaire pour plus de cible égale si notre chiffre d'affaires est supérieur ou égal à la cible, puis renvoyez à nouveau cette différence, qui est supérieure moins inférieure, sinon vide. Donc maintenant, si je prends ces quatre champs, on a juste calculé un double clic. Vous verrez que les valeurs sont remplies dans la colonne J, où le revenu cumulé tombe sous la cible et les valeurs sont remplies dans la colonne K Dans tous cas où le revenu cumulé dépasse la cible et ce sont les deux Siri que nous allons dans notre graphique qui peut être formaté avec ces remplissages verts et rouges. Alors commençons à ajouter un peu de sérieux ici. Je vais choisir un nouveau Siri. Le 1er 1 que je vais ajouter est plus bas et vous verrez pourquoi en juste une minute valeurs pour plus bas, je vais être augmenté deux à 31 dates de l'axe X. Ok, maintenant je vais ajouter sous cible traînée vers le bas les valeurs Onley jusqu'à la date à laquelle nous avons des données pour la presse OK, encore une fois. Même affaire. Je veux que mes étiquettes d'axe X s'étendent tout au long du mois, même si je n'ai pas de données pour tous ces jours. Dernier mais pas des moindres, Target a traîné vers la route 21, puis nous allons prendre toutes nos dates dans la colonne A Et là, vous allez pour que les choses ne semblent pas tout à fait bien. Et c'est bon, parce qu'on doit faire un peu de travail pour en faire ce dont on a besoin. Donc je vais aller sur un tableau. outils modifient le type de graphique en passant à la combinaison. Donc vous pensez probablement, Oh, Oh, mon Dieu, qu'est-ce qui ne va pas ici ? C' est juste un chaos absolu, mais nous sommes plus intelligents qu'exceller. Donc nous allons dire exactement à Excel ce que nous essayons de faire ici un par un. Allons rejoindre nos Siri et décidons de ce qu'il doit être visualisé. En tant que tel, notre objectif. Vous voyez, il a été changé en colonne Clustered. On veut juste que ça redevienne une ligne. Notre cible. Même chose, juste une ligne et nos revenus cumulés. Faisons une ligne avec des marqueurs. Ce sont les trois que nous avons reculé à l'origine à leur forme originale. Et maintenant ces trois, les nouveaux que nous avons ajoutés plus bas, et plus. Ces zones vont être empilées. Alors faites la même chose pour tous les trois sur le même axe et appuyez sur OK, et maintenant vous voyez que nous sommes un peu plus près ici et en fait, tout ce que nous avons à faire. C' est éditer certaines de ces zones de Siri, et nous devrions être dorés. Donc, d'abord et avant tout, c'est notre plus bas, qui nous donne fondamentalement la superficie jusqu'au point minimum, qu'il s'agisse de revenus ou de cibles. Pas besoin de faire est de lui donner pas Phil et pas de frontière, ce qui nous laisse vraiment juste avec le dessous et au-dessus qui doivent être ajustés. Voici donc le dessous, que nous pouvons remplir avec un rouge et un peu de transparence. Si vous voulez, ça a l'air bien, et puis je peux sélectionner sur Target, et nous donnerons à celui-ci un beau vert foncé avec une transparence similaire. Donc maintenant, lorsque nous étendons cela, nous avons ces zones ombragées personnalisées au-dessus et au-dessous de notre ligne cible. À ce stade, c'est vraiment juste une fonction de faire le formatage que vous voulez faire. Peut-être que nous changeons les unités à tous les deux jours, espacons les choses un peu, probablement utile pour ajouter une légende ici aussi. Alors mettons une légende en bas et espacons ça. Nous n'avons pas besoin d'une légende pour la cause inférieure. C' est vraiment invisible. Quoi qu'il en soit, je ne fais pas ça audacieux, vous savez, et bien sûr, vous pouvez ajouter des étiquettes de données si vous voulez. Peut-être que vous voulez simplement ajouter une étiquette de données que vous connaissez à votre valeur de chiffre d'affaires de la date actuelle. Et vous savez que vous pouvez lui donner un beau Phil bleu foncé avec une police blanche audacieuse. C' est un peu un effet agréable, et vous pouvez aller dans les options mais au-dessus de votre point de données. Et vraiment, à partir d'ici, c'est juste à vous de décider comment vous voulez modifier et personnaliser cela. Mais voilà. Très unique, un moyen efficace de visualiser quelque chose comme se battre contre un objectif. 12. Concevoir un graphique de jauge personnalisé: Cette prochaine démo est amusante, et c'est un bon exemple de la façon de combiner deux types de graphiques très différents ensemble pour créer quelque chose de totalement nouveau et unique. Donc, dans ce cas, nous allons réellement combiner un graphique en beignet avec un nuage de points de toutes choses pour créer un effet de jauge et d'aiguille. Donc, ce type de visualisation de graphique de jauge peut être un moyen vraiment agréable, vraiment simple de montrer où la valeur donnée tombe le long d'une certaine échelle. Donc, dans l'onglet graphique de jauge, vous pouvez voir avoir mis certains objets en place pour commencer à créer un peu d'un modèle. Les formes de la forme vont juste créer presque comme un effet d'arrière-plan pour jauge sur. Ces zones de texte s'alignent simplement avec différents points le long du bord de la jauge. Donc j'ai un peu d'endroit toutes leurs colonnes ici, j'ai la valeur du graphique et j'ai vendu A à ça. Est-ce que la valeur entrée par l'utilisateur que vous allez tracer sur les graphiques ? Vraiment, une seule valeur qui va être montrée, puis une valeur maximale et Selby à Dans ce cas, nous sommes à la recherche d'une échelle de 0 à 10 pourrait être de 0 à 100 an 2000 vraiment n'importe quoi. Donc, la première chose que nous devons faire est de créer un tableau de donut, qui servira de fond à notre jauge. Et dans ce cas, je veux un beignet avec cinq segments le long de la moitié supérieure du cercle. Donc, pour ce faire, je vais taper 10% copier et coller ce total de cinq fois, puis dans la cellule C sept type 50%. La raison pour laquelle je fais cela est parce que j'ai besoin de ceux-ci pour additionner jusqu'à 100% même si je n' utiliserai pas le morceau 50%. Ce qui va fondamentalement faire est juste formater cette tranche de 50% avec non, dit Phil, il devient invisible. Et puis j'ai cinq segments de taille égale le long de la moitié supérieure de mon demi-cercle. Donc, prenons ces valeurs de C deux à C sept et insérez beignet. Je n'ai pas besoin d'un titre de graphique ici ou d'une légende, et tout le fait est de sélectionner ce format de pièce à 50% le point de données et de lui donner aucun Phil et aucune ligne. Et puis pour le graphique lui-même uniforme à la zone du graphique. Non, Phil, pas de ligne. Et c'est bon pour l'instant. Nous allons évidemment faire quelques ajustements supplémentaires de mise en forme avec des couleurs et des alignements momentanément. Mais la prochaine pièce que je veux faire est de commencer à préparer mes données pour les valeurs de nuage qui seront surposées au-dessus de ce graphique en beignet. C' est essentiellement l'idée ici est de créer un graphique combo qui trace les tranches de beignet ainsi que les valeurs X et pourquoi les valeurs pour alimenter mon nuage de points. Donc, la première étape pour générer ces valeurs consiste à calculer la valeur actuelle en termes de degrés . Ça a l'air compliqué, mais ce n'est vraiment pas le cas. Les premières choses. abord, nous pensons à la valeur actuelle comme une partie ou un pourcentage du maximum. Dans ce cas, la valeur 5 renvoie 50,5 ou 50 %. Tout ce que nous devons faire est de multiplier cela par les 180 degrés totaux de notre demi-cercle pour traduire cela en degrés au lieu d'un pourcentage. Alors, voilà. Valeur de cinq sur 10 signifie que nous rechercherions une ligne verticale ou un angle droit de 90 degrés maintenant pour comprendre les valeurs réelles de X et Y que nous avons besoin de tracer le long d'un nuage de points pour définir la position de l'aiguille devra dépoussiérer notre trigonométrie livres et revenir aux bases. J' ai trouvé un site Web qui est vraiment utile pour comprendre cela, alors jetons un coup d'oeil rapide. Donc, nous sommes ici à www dot maths est amusant point com slash géométrie slash unité cercle point html. Et c'est une excellente visualisation interactive qui montre les relations entre les angles, les signes, signes, co sines et les tangentes et la chose importante à regarder les années pendant que je me déplace le long de l'arc de ce cercle. La ligne verte, qui est la hauteur de ce point, ou la valeur Y, est définie par la fonction sinusoïdale ou le sinus de l'angle. La ligne bleue, qui est la valeur X de ce point, est définie par le sinus co de l'angle. Donc, avec cela à l'esprit, je sais que nous pouvons utiliser des fonctions de trigonométrie excels, signe et co signe pour définir le positionnement exact de ce point, qui, quand vous y pensez, servira essentiellement de le point final de mon aiguille. Alors donnons ça un coup dans Excel. Bon, donc l'aiguille de ma jauge a besoin de deux points parce que ces deux points seront reliés par une ligne qui crée essentiellement l'aiguille elle-même. premier point doit être l'origine ou 00 et cela reflète vraiment son point ici. La base de l'aiguille qui ne bougera jamais. Il sera toujours fixé à 00 Le deuxième point est le point que j'ai besoin de définir en utilisant les fonctions de signe et de co que je viens de mentionner. Alors rappelez-vous la raison pour laquelle les valeurs de l'air définies par signe afin que je puisse juste taper signe égal et voici la capture. Il ne peut pas simplement faire le signe des degrés parce qu'Excel est en fait à la recherche de ce nombre en termes d'éclat. Mais heureusement, ils ont une fonction pour le convertir en tapant simplement éclat ici. Alors signez l'éclat D là où vous allez. Cela revient à un, ce qui, dans ce cas, est exactement ce que nous recherchons. Parce qu'avec une valeur de 5 sur 10, nous chercherions une aiguille verticale avec une valeur Y de 1, qui est le maximum pour les valeurs X. Commençons avec juste une fonction de signe co, donc co signe éclat de pour fermer avec deux parenthèses, Et voilà. Donc maintenant, nous avons les points dont nous avons besoin pour un nuage de points, et il s'agit juste d'ajouter que comme un nouveau Siri pour créer notre graphique combo, cliquez avec le bouton droit sur le donut Select données. Ajoutez un nouveau Siri qui appellera Needle et les valeurs de Siri. Pour l'instant, je vais juste sélectionner l'un de ceux-ci soit le X ou le Y. Dans ce cas, je vais sélectionner les valeurs de mensonge dans F deux à F trois Appuyez sur. OK, et comme vous pouvez le voir, Excel a par défaut simplement ajouter un deuxième anneau de mon beignet. Donc, si nous allons dessiner des outils et changer le type de graphique, vous êtes dans un combo et sérieux, qui est que mon beignet restera un beignet, et mon aiguille Siri qui, je viens d'ajouter, sera un disperser avec des lignes droites et compresser. Ok, et maintenant que nous avons défini notre deuxième Siri comme un nuage de points, nous pouvons revenir dans certaines données. Vous pouvez éditer les données de cette aiguille, et maintenant nous pouvons entrer des valeurs X et Y. Donc X valeurs qu'ils vont être e 233 et pourquoi sera après l'arbitre ? On y va. Alors maintenant, nous avons fait le plus dur. Nous avons obtenu les données définies d'une manière qui a du sens. Maintenant, nous avons juste besoin de faire quelques ajustements et ajustements à ces graphiques pour que les choses s'alignent et soient belles. Donc, les premières choses sur mon nuage de points, je vais faire un clic droit et me former sur l'axe X. Je veux un minimum de négatif et un maximum d'un même accord avec le pourquoi minimum de négatif un, un maximum d'un. Et je peux vraiment supprimer l'Axe lui-même parce que je n'ai pas besoin des chiffres. Je peux supprimer les lignes de grille, et je me conforme à cette ligne de toute façon, que je choisis. Donc, je n'ai pas besoin de faire un noir solide avec un peu d'effet d'ombre sur elle aussi . Là, nous allons maintenant sur notre beignet. Formons au beignet. Siri est ce que je veux faire. Seo. Il tourne sur le côté. Je veux le retourner pour que mes segments de taille égale soient sur le dessus afin que je puisse changer l'angle de la première tranche jusqu'à 270 degrés. Voilà, tu y vas. Augmentez un peu la taille du trou de beignet. Ça a l'air sympa. Maintenant, nous allons faire glisser ce graphique combo directement dans notre modèle. Pourrait le rendre un peu plus grand juste pour qu'il s'adapte bien. On y va. Et pourquoi ne pas coder les segments de couleur pour que les couleurs représentent quelque chose de significatif . Par exemple, si cette jauge reflète des valeurs qui progressent de bon à mauvais, alors peut-être que vous voulez vert à gauche et rouge à droite, sorte que vous pourriez juste lui donner un Phil solide, peut-être un vert foncé là-bas, vert clair pour le deuxième segment, jaune pour le troisième orange pour le quatrième et enfin un rouge foncé pour le cinquième. Cela aide vraiment à souligner visuellement cette progression du bon au mauvais ou du mauvais au bon. Alors testons-le et voyons si cette chose fonctionne comme nous l'attendons. Une valeur de graphique. 2.5 Eh bien, ça a l'air faux. Ça devrait être le contraire. Et si nous faisons une valeur de 10 ? Tu vois, c'est aligner zéro, ce qui semble un peu désactivé. Si on fait zéro, ça passe au maximum. Si nous revenons à notre outil en ligne, vous pouvez voir que dans ce cas les angles augmentent lorsque nous nous déplacons dans le sens inverse des aiguilles d'une montre le long l'arc de ce cercle, alors que dans notre cas nous voulons que notre angle augmente lorsque nous nous déplacons dans le sens des aiguilles d'une montre le long de la du cercle. Donc, tout ce que nous devons faire est une solution très facile est juste d'inverser cette fonction de littoral en ajoutant un négatif. Donc on prend le signe négatif maintenant, et voilà. Donc maintenant, valeur cinq droit de haut en bas. Et à mesure que nous augmentons, nous allons dans le sens horaire le long de cette jauge. Donc j'espère apprécié que l'un des tutoriels Excel les plus inhabituels que nous allons probablement jamais voir. Mais je pense que c'est un excellent exemple de pousser vraiment le concept du graphique combo à un tout autre niveau. 13. Visualiser un pourcentage à l'aide des fonctions de rayon: Donc, l'un des outils les plus puissants et excellent qui est généralement mal compris est la théorie. Une fonction, et la raison pour laquelle elle est si mal comprise et si mystérieuse est qu'elle fonctionne d'une manière qui est différente de toute autre formule de cellule ou fonction dans Excel. Donc, à cause de cela, ils pourraient être très difficiles à apprendre et très difficiles à maîtriser. Mais une fois que vous avez une compréhension de la façon dont les fonctions Ray fonctionnent réellement, ils peuvent être un outil incroyablement puissant. Et je vais vous montrer un exemple de la façon dont cela peut également être utilisé dans un contexte de construction de visualisations de données dans Excel. Donc, sous l'onglet Tableau Pourcentage grille, nous avons un utilisateur pour trouver de la valeur et être à l'endroit où vous pouvez simplement taper un pourcentage particulier . Et puis nous avons quatre colonnes avec les fonctions de rayon dans Collins G à J. Maintenant, plutôt que de les écrire à partir de zéro et de ne pas vraiment leur rendre justice, j'ai pensé que je les mettrais ici maintenant pour que vous puissiez creuser autant ou que peu que vous choisissez de décoder ce que les fonctions de l'air font réellement. Mais dans l'intérêt du temps et de garder la visualisation des données concentrée. Je vais juste vous donner un bref résumé de ce que ces quatre tentent d'accomplir ici. Donc, le tableau, fonction et la colonne G utilise essentiellement des fonctions de mod et de ligne pour définir un tableau 100 rose qui ressemble à ceci. Fondamentalement, il compte juste de 0 à 9, puis répète le processus de zéro à nouveau. La deuxième colonne Array Function h utilise des formules d'entier et de ligne pour produire le tableau que nous voyons ici, qui compte essentiellement des groupes de 10. Donc ils sont 10 zéros, 10 10 deux est jusqu'à 10 nines, et on fait défiler vers le haut. Fondamentalement, ce que ces formules et Collins, G et H font, c'est qu'ils créent des paires de points qui peuvent être utilisés pour remplir une grille 10 par 10 en utilisant le type de nuage de points. Et vous finissez avec 100 points individuels. Et donc ces deux colonnes ne font que créer un modèle. C' est pourquoi nous les appelons des boîtes vides que nous pouvons utiliser pour visualiser n'importe quel pourcentage est écrit et B 2, puis la seconde pour les fonctions ray. Fondamentalement, tout ce qu'ils font est qu'ils utilisent ce pourcentage pour répliquer les valeurs et les colonnes G et H vers le bas à une ligne particulière, donc dans ce cas ont écrit 15%. Donc, vous remarquerez que les 15 premières lignes sont juste des doublons de G et H. Et puis il y a mis à zéro. Après ça, tu pourrais voir si je change ça à 12. Maintenant, seules les 12 premières lignes ou doublons ne le changent que pour le 1er 2 Et ce que cela nous permettra de faire est d'ajouter un second Siri à notre nuage de points, en utilisant ces combinaisons de points pour superposer notre grille avec des boîtes remplies pour indiquer le pourcentage. Et Selby aussi. Alors donnons une chance à ce truc. Je vais insérer un nuage de points, faire glisser ici sous mon pourcentage Select data au nom d'un Siri. Le premier, Siri est vide et les valeurs X s'affaiblissent. Il suffit de sélectionner G trois flèche de décalage de contrôle vers le bas pour G un des deux. Et puis les valeurs Y H 3 décalage de contrôle sont descendus à H 102 . Je vais faire un peu de formatage ici. Pas de titre. Aucune ligne de grille n'a même pas besoin des axes eux-mêmes. Et si on se forme à la zone de l'intrigue, je ne veux pas de Phil ou de frontière. Et enfin, je vais éditer le Siri lui-même et dans mes options de remplissage et de ligne et aller dans mes options de marqueur ici. Utilisez des marqueurs intégrés avec des carrés et nous allons les rendre beaux et grands. Que diriez-vous de carrés de 15 points et pourquoi ne pas remplir ces marqueurs avec juste un gris clair ? Puisque c'est une sorte de notre modèle, cela reflète nos boîtes vides. Ne faites pas de frontière sur ceux-ci, et là vous l'avez. Donc, comme je sorte d'espacement de décalage autour, cela devient une grille 10 par 10 vraiment propre. Oups, Débarrassez-vous de cette zone de graphique de format frontière pas Aereo. Donc, vous avez cette grille 10 par 10 propre qui est remplie avec ces deux fonctions de tableau ici , et vous avez peut-être remarqué dans l'onglet des formules. Si vous regardez votre gestionnaire de noms, vous avez peut-être vu ces quatre tableaux nommés, qui correspondent aux rayons que je viens de traverser dans Collins G via J. Donc j'ai déjà construit ça pour vous comme des plages nommées, ce qui signifie que vous avez l'option. Soit créez vos données source comme je viens de le faire, soit si vous préférez vous pouvez entrer dans les données sélectionnées et vous pouvez éditer celles de Siri. Et au lieu de G 33 G un ou deux, vous pouvez simplement taper tableau, souligner X et vous êtes sage serait tableau soulignement pourquoi ? Et cela fait exactement la même chose, juste deux façons d'atteindre le même objectif. Et maintenant tout ce qu'il reste à faire est à un second Siri, et nous allons appeler ça plein et les valeurs X seront le tableau produit dans les cellules. J' y ai trois, je veux. Ou il pourrait taper après le tableau de points d'exclamation X X, et la même affaire ici. Je pourrais sélectionner toute cette gamme de J trois à J 102 ou simplement taper tableau. Pourquoi, pourquoi ? Et entrez et appuyez sur. OK, et je vois ces deux petits points parce que je n'ai sélectionné qu'un pourcentage de deux de ces boîtes vont être remplies. Alors peut-être qu'on en a jusqu'à 25. Donc c'est un peu plus lisible. Et maintenant, nous nous conformons à cette seconde donnée de Siri et c'est à vous de décider. Vous pouvez les garder comme des points, des diamants ou des triangles ou tout ce que vous voulez. Dans ce cas, je vais faire, intégrés, les mêmes marqueurs de taille carrée que mes boîtes vides. Donc taille 15 juste pour qu'ils se superposent vraiment bien sur le dessus. Je vais lui donner un Phil bleu marine, et je ne veux pas de frontière. Et voilà. J' ai donc mes deux plus posés 10 par 10 grilles avec des marqueurs carrés construits de manière à ce que chaque fois que je change ce pourcentage, le bon nombre de boîtes va remplir pour refléter cela ici sur la grille une façon vraiment intéressante de visualiser quelque chose qui est assez simple mais qui peut être visualisé d'une manière beaucoup plus cool que la plupart des gens ont tendance à le montrer. C' est donc votre cours rapide 101 sur l'utilisation des fonctions de tableau pour construire une grille de pourcentage . 14. Conclusion: Eh bien, les gars, c' officiel. Vous avez terminé les graphiques et graphiques Excel Analytics, Advanced Excel. Donc, abord et avant tout, félicitations et savez combien il faut pour réellement s'asseoir et apprendre et pratiquer ce truc et élargir votre ensemble de compétences. Tellement d'énormes accessoires pour toi. La bonne nouvelle, c'est que tu l'as fait ton rencard de sa rock star. Félicitations encore avant que je te laisse partir. Je veux juste frapper une ressource rapide est et les prochaines étapes d'abord, Certains auto-promotion plus éhonté. Si vous êtes intéressé à plonger dans le côté de la formule et de la fonction des choses, consultez ma formule avancée dans le cours de fonction. Je sais que nous avons touché un bon nombre de formules et de fonctions, surtout pendant la section de démonstration de ce cours sur laquelle nous n'avons vraiment pas passé autant de temps nous concentrer que possible. Ce cours comprend donc une tonne de contenu d'environ six heures, et il contient tout ce que vous pourriez souhaiter apprendre sur les formules ou les fonctions. Donc les statistiques, les opérateurs logiques, condition, ALS, les fonctions de texte, les formules de tableau, rechercher la référence et bien plus encore. Une seconde, il y a quelques sites et blogs que j'ai tendance à aller pour obtenir de l'aide. J' ai pensé que je les partagerais avec vous. Tout d'abord et avant tout le support dot Office com est vraiment utile quand il s'agit des graphiques de base ou des formules. Consultez également Office 3 65 Pour toute personne qui n'a pas encore la dernière version d'Excel, 3 65 est un excellent service abordable, abordable, basé sur un abonnement pour vous assurer d'avoir toujours les derniers produits de bureau. Et je jure que je ne suis pas un porte-parole payé. Promettez un deuxième débordement de pile. Vraiment, vraiment excellent site pour les supports de formulaire. Si vous avez des questions spécifiques ou des besoins basés sur un projet, Stack déborde un endroit idéal. Et puis j'ai trouvé ce site Google E 90 e 50 graphiques qui a des choses incroyablement avancées qui plonge vraiment dans des choses comme VB, un revêtement et super avancées mathématiques et statistiques. Donc, si vous voulez vraiment vous pousser et que vous êtes dans ce genre de choses, allez-y et vérifiez que vous êtes bien là. Enfin, les notes et les commentaires sont ce qui maintient des cours comme celui-ci en vie. Je vous encourage donc à partager vos commentaires si vous l'avez pour le meilleur, pour le pire. Et je veux dire que les commentaires positifs, bien sûr, sont toujours très appréciés. Mais les commentaires constructifs sont géniaux pour tous les commentaires que vous pouvez partager pour m'assurer que je produis le meilleur contenu que je puisse faire beaucoup de chemin. Alors s'il vous plaît n'hésitez pas à tendre la main avec ça. Merci beaucoup. Je t' apprécie vraiment. Prendre le temps de s'arrêter au cours signifie vraiment beaucoup pour moi. Et j'espère vous voir dans certains de mes autres cours aussi. Alors merci encore et bonne nuit.