Conseils de pro pour Excel, partie 6 : Analyse

1. Intro à l'analyse: D' accord, si tu regardes cette vidéo, tu veux un régal. Cette prochaine section du cours vous fera découvrir certains des excels. outils d'analyse les plus puissants comme les tableaux de données, les feuilles de prévision, recherche d'or et le gestionnaire de scénarios. Nous allons travailler sur la résolution de l'optimisation complexe est avec solveur, explorer les tables de données avec des fonctions de cube et même construire des simulations Monte Carlo à l'aide d'un simulateur de roulette pleine échelle. J' espère que tu es excité. Maintenant, commençons. 2. Outils d'analyse rapide: tout droit pour cette astuce pro. Je veux parler de la façon dont nous pouvons explorer instantanément nos données à l'aide de quelque chose qu'on appelle des outils d'analyse rapide . Ceci est un conseil d'analyse d'une étoile très simple à utiliser, mais un outil vraiment intéressant pour jouer avec. Donc, les versions les plus récentes d'Excel croyaient 2013 et ultérieures, incluent une option appelée Outils d'analyse rapide, et tout ce que vous devez faire est de sélectionner une plage de cellules, peu comme celle-ci. Tu verras un pop-up apparaître en bas, non ? On dirait un éclair avec un graphique à barres en dessous. Et lorsque vous cliquez sur cette option, ou appuyez sur contrôle que, vous accédez à un menu de l'outil d'analyse rapide. Et dans ce menu, vous trouverez toutes sortes d'outils d'analyse, y compris les formats conditionnels, types de graphiques, les lignes calculées et les colonnes. Vous pouvez convertir cette plage en une table ne peut pas déclencher des lignes. Toutes sortes d'outils d'analyse très, très intéressants et puissants regroupés dans ce menu unique ici pour un accès rapide. Et ce qui nous permet de faire est de prendre une gamme de cellules de base comme vous voyez en haut de la diapositive et de la convertir en quelque chose comme ça. Vous connaissez l'échelle de couleurs ou l'ajout d'une colonne calculée avec les valeurs moyennes ou insertion d'un graphique en colonnes en cluster en un clic. Tu sais, quiconque me connaît et connaît mes cours le sait. manière générale, j'essaie d'éviter ce genre d'outils de raccourci comme celui-ci parce que je suppose que je suis vieille école. J' aime passer par les menus traditionnels, mais je suis en fait très impressionné par la douceur et la commodité de ce menu d'outil d'analyse rapide , et par conséquent, j'ai commencé à l'utiliser un peu plus moi-même. Maintenant, une remarque ici, vous ne verrez pas toujours toutes ces options car ces options et ces outils peuvent nécessiter les données soient dans un certain format. Donc, si vous êtes sélectionné, les données ne sont pas compatibles avec un certain type d'outil, comme une ligne d'allumage ou un pivot. Par exemple, vous pouvez ne pas voir cette option dans votre barre d'outils d'analyse rapide. Donc, les cas d'utilisation les plus courants ici, évidemment juste explorer rapidement une variété d'outils d'analyse de données populaires sans avoir à naviguer manuellement à travers différentes options de ruban. Et aussi, j'ai trouvé que c'est un excellent moyen d'ajouter rapidement des lignes ou des colonnes calculées pour des choses telles que les totaux moyens des comptes sans avoir à taper dans une seule formule. Alors nous allons sauter dans Excel. avons une bonne gamme de données que nous pouvons utiliser pour mettre en pratique certains de ces outils d'analyse rapide. Bon, donc si vous voulez suivre, allez-y et ouvrez votre classeur de protestation avait votre table des matières, et nous allons faire défiler jusqu'à droite jusqu'à nos conseils d'analyse. Et dans ce cas, nous voulons les outils d'analyse rapide Démo. Allez-y et des liens vers cette feuille. Et ce que vous verrez est une simple gamme de cellules ici. Nous regardons les scores du film I am DB calculés en moyenne par année 2011 à 2015 et par genre ici dans la colonne A. Maintenant, c'est un excellent échantillon de données à utiliser pour pratiquer ces outils d'analyse rapide parce que c'est bidimensionnel. Nous avons ici une belle plage ou table de valeurs que nous pouvons utiliser pour la mise en forme conditionnelle basée sur la valeur , des choses comme les échelles de couleur et les concepts élevés. Nous avons du temps, Siri ou tendance ici avec cinq années de données pour que nous puissions montrer des choses comme des totaux en cours ou des graphiques en ligne ou en colonnes. Nous avons la possibilité d'ajouter des lignes calculées et la ligne 13 ou des lignes de colonne ou d'étincelles calculées ici dans la colonne G. C'est donc un bon ensemble de données polyvalent pour voir vraiment un large éventail d'options pour ces outils. Allons de l'avant et sélectionnez un à tout le chemin vers le bas jusqu'à F 12. Notez que j'inclut également les en-têtes. Et comme vous pouvez le voir, si vous passez la souris sur la plage que vous avez sélectionnée, vous obtiendrez cette analyse rapide Pop up ici en bas à droite. Allez-y et cliquez ou appuyez sur Ctrl Qué. Et nous voici dans le menu d'analyse rapide et vous avez des options de mise en forme. totaux des options de graphique, qui sont des lignes et des colonnes calculées, peuvent comporter des tableaux ou des pivots. Donc, tous les outils d'analyse les plus populaires et les plus puissants emballés ici dans un seul menu pratique. Maintenant, peut-être ma fonctionnalité préférée est le fait que vous n'avez même pas à vous engager à n'importe quelle option pour voir la sortie. Vous obtenez donc ce type d'aperçu rapide dans votre sélection en survolant simplement ces différentes options. Et si vous voulez faire des informations personnalisées, ce qui est quelque chose que j'aime souvent faire Lorsque vous cliquez à travers ces outils, vous allez réellement accéder à la boîte de dialogue de mise en forme où vous pouvez personnaliser vous savez exactement ce que vous voulez ici et puis appuyez sur OK et comme n'importe quel autre changement, appuyez sur Control Z Undo, et vous pouvez revenir directement dans ces outils pour que les options de formatage parlent vraiment elles-mêmes. Très simple pour les graphiques. Fondamentalement, ce juste des insertions et un nouveau graphique en tant qu'objet classeur a obtenu différentes options communes ici au début de notre liste. Dans la section totale, nous pouvons ajouter la rose calculée nous a montré ce genre de ligne ombrée bleu ici dans l'icône, ou si nous continuons à droite, nous pouvons ajouter des colonnes calculées. Et cette utilisation est vos modes d'agrégation ou d'urbanisation d'été les plus populaires, certains un pourcentage moyen du total et un total en cours d'exécution à nouveau, très facile à ajouter sans taper une seule formule. Là, nous pouvons convertir, organiser à une table, insérer une table de pivot vide directement à partir d'ici, ou nous pouvons ajouter des lignes d'étincelles et exceller le nez juste de la chauve-souris Que l'endroit le plus approprié pour laisser tomber les lignes d'étincelles dans ce cas serait juste là dans la colonne G Donc encore une fois, j'ai été très, très impressionné par ces outils et par la façon dont ils sont lisses et pratiques. Je vous encourage donc à travailler avec les données présentées ici ou à déposer votre propre échantillon et explorer comment ces outils d'analyse rapide peuvent vous aider à analyser et à comprendre vos données. 3. Le gestionnaire de scénarios: Très bien, prenons quelques minutes et parlons d'excels, de scénario, de gestionnaire, d' outil et, plus précisément, plus précisément, comment cet outil peut être utilisé pour comparer rapidement les différentes sorties du modèle. Ainsi, le gestionnaire de scénarios, que vous trouverez dans votre onglet de données sous les options d'analyse « Que faire si » vous permet essentiellement d'enregistrer et d'accéder rapidement à des combinaisons spécifiques de valeurs de cellules prédéfinies. Laissez-moi vous montrer un exemple ici. Si nous explorons cette option de gestionnaire de scénarios, nous verrons une liste de tous les scénarios prédéfinis que nous avons créés. Dans ce cas. J' en ai quatre. Et que se passe-t-il lorsque vous ajoutez ou créez un nouveau scénario à partir de zéro ? Tu le dis à Excel. Je voulais trouver un scénario basé sur cet ensemble spécifique de cellules. Dans ce cas, j'ai trois d'entre eux ont nommé un pour cent de cellule. J' ai nommé un taux d'intérêt de deuxième cellule et une durée de 1/3 cellule. Maintenant, un conseil rapide. Ici. Vous n'avez pas à vous donner de noms. Cela pourrait juste dire H sept h huit h neuf. Je trouve que nommer ces cellules aide à rendre ce processus un peu plus intuitif et convivial . Donc, essentiellement, nous disons à Excel que nous définissons un scénario basé sur des valeurs particulières pour chacune de ces trois cellules. Ce cas, j'ai nommé le scénario 20% 30 ans, et pour ce scénario, je veux vendre un pour changer à une valeur de point à 20%. Je ne vendrai pas au taux d'intérêt pour passer à une valeur de 0,475 ou 4,75 % et je veux changer la cellule trois la durée à 30 maintenant. Une fois que vous avez défini et nommé ce scénario, vous pouvez rapidement l'afficher et modifier automatiquement ces cellules en fonction des valeurs que vous avez configurées. Maintenant. Ceci est très souvent utilisé pour les exercices de modélisation, qui vous permettent de fixer différentes combinaisons d'entrées comme ces trois cellules ici, l'acompte, le taux d'intérêt et la durée afin de calculer l'effet sur un certain la production pilotée par la formule dans ce cas, l'argent à fermer sur la propriété et les dépenses mensuelles estimées. Et si nous devions poursuivre ce processus et créer trois scénarios supplémentaires, alors avec un clic d'un bouton, nous pouvons rapidement comparer quatre sorties différentes en fonction de ces quatre combinaisons de nos entrées. Ainsi, comme vous pouvez le voir un excellent outil pour évaluer rapidement l'impact de différentes combinaisons de valeurs d'entrée. Maintenant, les cas d'utilisation courants. Ceci est souvent utilisé pour établir des prévisions basées sur plusieurs variables qui peuvent changer comme la saisonnalité, les taux d'intérêt, etc., etc., ou pour modéliser plusieurs résultats potentiels dans les cas où l'incertitude ou le risque est un facteur. Par exemple, créer un scénario de risque faible, moyen ou élevé pour prédire les rendements de votre portefeuille d'actions. Donc, avec cela, allons sauter dans Excel et créer certains de ces scénarios pour nous-mêmes. D' accord, alors allez-y et ouvrez votre classeur de pourboire professionnel. Si vous suivez le long, a dû gestionnaire de scénarios dans la section Conseils Purple Analytics et nous allons de l'avant et lien directement ici. Nous avons maintenant un calculateur de coût immobilier, car ceux d'entre vous ont suivi tout au long du cours. Cela peut sembler familier de notre conseil d'audit de formule, mais fondamentalement, nous évaluons deux chiffres clés ici. Encaisse nécessaire pour fermer sur la propriété et les dépenses mensuelles estimées, qui sont tous deux déterminés par un certain nombre de facteurs ici dans Collins, G et H prix d'achat du taux d'impôt foncier, différentes conditions de prêt ici, sur lequel nous allons nous concentrer dans cette démo ainsi que d'autres facteurs comme les services publics, d'assurance, les frais et ainsi de suite. Maintenant, vous remarquerez que j'ai nommé chacune de ces cellules en utilisant la zone de nom juste ici à gauche de la barre de formule. Donc pour cent de baisse, taux d'intérêt et durée et encore une fois cela rendra un peu plus lisible, mais plus intuitif une fois que nous commençons à créer des scénarios maintenant, l'idée ici est de prédéfinir un certain nombre de combinaisons pour ces trois dans H sept à H 9, afin de déterminer rapidement et facilement l'impact sur nos principaux résultats. Et il y a plusieurs façons de le faire. Nous pourrions les transformer en validation de données, cellules déroulantes, vous savez contenant une liste limitée d'options et demander aux utilisateurs de sélectionner manuellement ces combinaisons. Mais dans ce cas, nous avons vraiment trois ou quatre combinaisons qui sont les plus probables et les plus réalistes et à une sorte de présélectionner ces combinaisons. Le gestionnaire de scénarios sera un excellent outil à utiliser dans ce cas particulier, alors allons de l'avant et sélectionnez les trois cellules qui feront partie de notre scénario h sept h huit h neuf. Nous allons aller dans notre data town explorer les outils d'analyse et si dans notre section de prévisions et cliquer sur Scénario Manager. Donc à partir d'ici, nous allons définir quatre scénarios différents à des fins de démonstration. Le premier sera de 20 % en baisse et un taux d'intérêt de 5 % pendant 30 ans. Alors allons de l'avant et ajoutons celui-ci. Maintenant tu peux lui donner le nom que tu veux. Dans ce cas, je ne serai pas très malin. Je vais juste taper ce que nous utilisons pour les entrées. Donc 20% 5%. 30. Et puisque nous avons sélectionné H 73 chaque neuf Excel sait déjà que ces air les trois cellules qui vont changer pour définir ce scénario. Et puis vous pouvez ajouter des commentaires ici personnaliser la protection que ce cas empêchaient les utilisateurs d'apporter des modifications et appuyez sur. OK, alors on y va. Il est rempli avec les valeurs actuelles dans ces cellules, et nous pouvons simplement les modifier pour répondre à nos critères. Donc pour cent vers le bas de son point à ce qui est plus facile pour vous pouvez taper 0.2 ou vous pouvez réellement taper 20% ici. Je suis un peu mieux comme cette approche. C' est plus lisible. d' Le tauxd' intérêt dans ce cas est de 5% dans le terme. La longueur est de 30. Alors appuyez bien là, et ça a créé notre scénario. Et maintenant, regardez ce qui se passe lorsque je clique sur ce bouton d'affichage boom. Il a changé ce taux d'intérêt à 5 %. Donc maintenant ces trois cellules prennent les valeurs exactes à trouver. Par ce premier scénario, nous venons de créer. Donc le même processus. Naviguons à travers et ajoutons trois autres. Le second sera de 20% en baisse à 4% sur 15 ans. 20% 4% 15 Appuyez sur OK, 20%. 4 % 15 ans. D' accord, continuons là-dessus. La prochaine sera une mise de fonds plus faible, seulement 10% en baisse à 5% sur 30 ans. Et je sais que je bouge rapidement, alors n'hésitez pas à ralentir les choses pour suivre. Mais ici, on a 10 à 5%. C' était exact. Plus de 30 ans. Ça a l'air bien. Et enfin, nous allons ajouter notre quatrième ici, qui est 10% avait un taux d'intérêt de 4% sur 15 et appuyez sur OK, 10% 4% 15. On y va. Nous avons donc maintenant nos quatre scénarios définis ici, et pour déterminer rapidement l'impact de chacun de ces scénarios sur nos cellules de sortie. Tout ce que nous avons à faire est de sélectionner le scénario et de cliquer sur Afficher. Alors maintenant, vérifiez ça. Il est passé à 20%. 4% 15 clic. Le troisième élément montre 10% 5% 30, puis la dernière option. 10 % 4 % 15. Et chaque fois que nous changeons ces éléments, vous pouvez voir que nos liquidités à clore ou nos calculs mensuels de dépenses changent en conséquence. Vous y allez donc une démonstration rapide de l'utilisation du gestionnaire de scénarios pour définir des combinaisons prédéfinies de valeurs d'entrée de cellule. 4. Optimiser avec la recherche objectif: Très bien, ce prochain conseil professionnel est amusant. Nous allons parler de la façon de résoudre pour des sorties individuelles ou des modèles d'optimisation simples en utilisant excels Gold Seek outil maintenant recherche or, que vous trouverez dans votre onglet de données sous le quoi si les options d'analyse vous permet fondamentalement trouver le résultat que vous voulez En autorisant Excel toe changer automatiquement la valeur d'une seule cellule d'entrée donnée. La recherche d'or nécessite des entrées et des sorties très simples. Vous devez déterminer une seule cellule d'entrée codée en dur et une seule cellule de sortie basée sur la formule . Nous ne pouvons pas ajouter d'autres conditions ici. Nous ne pouvons pas tester des combinaisons de plusieurs entrées pour ces optimisations plus complexes. Les problèmes seront l'utilisation de l'outil Excels Solver dans une démo plus avancée dans le cours. Considérez une affaire comme celle-ci. Nous avons un intrant basé sur la valeur, qui est la quantité d'unités vendues juste là et vendre siège à. Nous avons un ensemble de facteurs de calcul comme le prix unitaire ou le prix de détail, le coût de production de chaque unité et un certain coût fixe fixe. Et avec ces facteurs ainsi que la valeur de la quantité, nous pouvons déterminer le profit, qui est notre production basée sur la formule dans ce cas, notre profit sera égal à la quantité multipliée par la différence entre le prix et le coût unitaire moins le coût fixe fixe fixe. Donc, ce scénario particulier, ce modèle simple vérifie toutes les cases pour l'outil de recherche d'objectif. Nous avons une seule quantité d'entrée codée en dur et une sortie basée sur une formule unique. Le prophète. Alors voyons à quoi cela ressemble en fait. Lorsque nous explorons cet outil de recherche d'objectif, vous verrez une boîte de dialogue qui ressemble à ceci, et ce que nous faisons ici, c'est que nous définissons notre cellule de sortie C huit, qui est notre profit à un objectif spécifique ou une valeur cible dans ce cas, 1000 $ en changeant cette entrée basée sur la valeur, voir la quantité. Donc, en anglais clair ont dit Excel, je veux que vous changiez la cellule C deux comme vous avez besoin pour atterrir à 1000$ de profit. Combien de quantité ou combien d'unités faire à vendre pour gagner 1000 dollars ? Et lorsque vous configurez gold, recherchez cette façon et appuyez sur OK, il produit automatiquement ces valeurs. Vous avez fixé votre profit 2000$ sorte de travail en arrière à la quantité qui équivaut à 833 unités vendues maintenant cas d'utilisation courante ici, tout comme nous le montrons ici, déterminer l'entrée idéale nécessaire pour produire un objectif ou une cible spécifique résultat. Vous savez, par exemple, nous pourrions faire un profit de 1000$ ici. Nous pourrions réaliser un bénéfice de zéro afin de déterminer combien de quantité nous devons vendre pour atteindre le seuil de rentabilité. Vous connaissez une combinaison comme ça, ou vraiment simplement résoudre des problèmes d'optimisation simples qui sont basés sur une seule variable d'entrée . Alors amenons cet exemple dans Excel et pratiquons la configuration de l'outil de recherche d'or nous-mêmes. Bon, donc si vous voulez suivre avec moi, ouvrir votre cahier d'exercices de pourboire professionnel et à partir de notre table des matières, nous allons explorer notre objectif, chercher une démonstration dans notre section de conseils d'analyse et aller de l'avant et établir un lien. Et ici, vous verrez, sont familiers. Modèle très simple basé sur cette quantité d'entrée et celui-ci sortie le prophète. Et comme vous pouvez le voir, si je sélectionne la cellule C deux, c'est simplement une valeur codée en dur. Nous avons des facteurs de calcul prédéfinis ici à partir du prix unitaire, du coût unitaire et du coût fixe dans notre cellule prophète. C huit. s'agit de notre production basée sur la formule, qui prend la quantité, Ils'agit de notre production basée sur la formule, qui prend la quantité,la multiplie par le prix unitaire moins le coût unitaire, puis soustrait le coût fixe à la fin pour déterminer le bénéfice réel. Ainsi, comme vous pouvez le voir, vous pouvez ajuster différentes valeurs ici. Si je vendais 325 unités, mon profit serait de 38. Si je vendais 325 unités, Si je vendais 23 000 unités, mon profit serait de 34 250 et ainsi de suite et ainsi de suite. Donc, la façon dont beaucoup d'utilisateurs qui ne sont pas familiers avec la recherche d'or va utiliser Excel et utiliser spécifiquement un modèle comme celui-ci est, vous savez, disons que nous voulons 10 000$ de profit. Tu sais, tu pourrais dire, OK, essayons de vendre 8000. Eh bien, c'est trop. Essayons 7500 sont encore trop élevés. Peut-être 6000 à faible 65. Vous êtes en quelque sorte d'essayer de rétrécir sur ce nombre en utilisant les invités et vérifier l'approche, et c'est exactement ce que recherche d'or est conçu orteil automatiser pour vous et sera. Vous pouvez également une sorte de retour dans la formule pour créer cette valeur de quantité optimale en pré-envoyant votre profit recherche d'or est conçu pour le faire pour vous passer par ce processus et ne pas vous faire une sorte d'inverser les calculs pour comprendre ce nombre et voir à. Alors passons dans notre onglet de données. Nous allons regarder nos outils d'analyse et cliquer sur la recherche d'or ou la deuxième option ici et tout ce que nous avons sont trois conditions ici. Nous allons mettre une cellule spécifique, ce qui est notre profit pour mettre ce prophète à une valeur spécifique. Et ça pourrait être n'importe quoi. Commençons par 0$. Je veux trouver le point d'équilibre. Et nous voulons le faire en changeant une cellule donnée, qui est la quantité ici et voir, et tout ce que nous avons à faire est d'appuyer sur OK et de vérifier. Il a juste automatisé le processus de résolution de ce problème pour nous. Donc la valeur cible était zéro. Il a optimisé trouvé la valeur actuelle de zéro. Ok, ça nous dit que Ok, on doit vendre 167 unités avant de casser un profit. Et pour confirmer cela si nous choisissons bien 1 66, nous avons perdu un dollar. Choisissez 1 68 nous avons fait 2$ et vous pouvez tester très rapidement différents scénarios ici. Comme dans ce cas, nous voulons mettre le prophète à 100 000 par exemple. Même processus exact. Mêmes entrées exactes. Appuyez sur OK, et voilà. J' ai trouvé une solution et je nous l'ai dit. Pour 100 000$ de profit, tu dois vendre 66 833 unités. Il y a donc un cours de recherche d'or. C' est un outil assez étonnant lorsque vous avez besoin d'optimiser les modèles d'entrée simple et de sortie unique simples dans Excel. 5. Forecasting les données de base: Très bien, il est temps de parler d'une de mes techniques d'analyse préférées. Et plus précisément, je veux vous montrer comment nous pouvons créer des feuilles de prévision en utilisant des données historiques et Excel. Maintenant. Les versions récentes d'Excel croient à partir de 2016 et Office 3 65 incluent un outil de triche prévisionnelle intégré qui vous permet de calculer des prévisions en fonction d'un ensemble donné de valeurs historiques. Ainsi, par exemple, nous disposons de données de base telles que les températures moyennes mensuelles, qui, dans ce cas, continuent de s'étendre sur environ six ans de données. Ce que nous pouvons faire est de sélectionner ces deux colonnes, y compris le mois ou l'indicateur de date, ainsi que les valeurs Exercer dans l'onglet de données et cliquer sur l'outil de triche de prévision juste à côté des options d'analyse de ce que si. Et lorsque nous faisons cela, nous verrons un aperçu de base de la prévision par défaut produite par Excel, ainsi que des options pour personnaliser des choses telles que la longueur de la prévision, l'intervalle de confiance, toute saisonnalité et aussi personnalisé comment gérer des choses comme les valeurs manquantes ou en double. Et une fois que vous avez configuré ces paramètres comme vous le souhaitez, Excel produira une feuille qui ressemble à ceci. Il s'agit de la feuille de prévision réelle. Il s'agit d'un nouvel onglet de votre classeur, qui contient le graphique de saisonnalité ainsi qu'une nouvelle table de données contenant vos valeurs historiques existantes, ainsi qu'un ensemble de valeurs prévisionnelles calculées. Je vais vous montrer quelques exemples de exactement à quoi cela ressemble dans Excel maintenant une note rapide . Parfois, vous pouvez utiliser des outils tels que les lignes de tendance pour des exercices de prévision très simples. Mais gardez à l'esprit que ceux-ci ne rendront pas compte de choses comme la confiance ou la saisonnalité. Donc, pour les cas plus complexes ou personnalisés et recommande certainement d'utiliser cette option de triche de prévision à la place. Donc cas d'utilisation courante ici pour un, prédire toutes les valeurs futures telles que les taux d'intérêt ou les rendements boursiers. Et deux, si vous voulez calculer une fourchette attendue de résultats futurs en fonction d'un niveau de confiance donné , cette feuille de prévision et les formules qu'elle produit sont très, très utiles pour ce faire. Donc, avec ce discours assez, allons dans la pratique exceller, en construisant nos propres prévisions. Très bien, donc pour ceux d'entre vous qui suivaient avec moi avaient votre cahier d'exercices de pourboires professionnels, nous recherchons la démo de prévision de base dans notre section pourpre de conseils Analytics. Allez-y et lien tout droit, et ici vous verrez deux ensembles différents de données obtenu des données boursières ici dans les colonnes, un à E, un à E, regardant les cours des actions d'Apple, que le prix de clôture et le volume par jour. Nous avons également quelques températures moyennes mensuelles de Barcelone, en Espagne, qui s'étendent sur environ six ans de données. Donc deux types très, très différents d'ensembles de données que nous allons utiliser pour pratiquer la prévision. Commençons par nos données boursières ici. Et une chose que je veux vous montrer est que si nous devions sélectionner la colonne B et contrôler, cliquez sur les valeurs que nous voulons prévoir, qui dans ce cas sera le cours de clôture de l'action. Maintenant, si nous allons à notre fiche technique et que nous choisissons la feuille de prévision à côté de ce que si l'analyse, vous verrez ce message d'erreur, il est dit, Hey, nous ne pouvons pas créer de prévision parce que votre chronologie n'est pas espacée uniformément. En d'autres termes, vous avez des écarts incohérents entre certains de ces points et la raison pour laquelle c'est le cas, parce que le marché est fermé les jours fériés et les week-ends. Donc nous avons ces lacunes, vous savez, comme les 2 et 3 mars, où nous ne fournissons aucune donnée ici. n'y a pas de prix de clôture ou de volume, donc ce que j'ai fait est dans une nouvelle colonne ici. Colonne C Il s'agit juste d'une série de valeurs séquentielles ou d'un index qui ne fait qu'étiqueter le Jour des jours un jour, deux jours trois, peu importe qu'il s'agisse d'un week-end, d'un jour de semaine ou d'un jour férié, peu importe qu'il s'agisse d'un week-end ou d'un jour férié . Et maintenant, si nous sélectionnons les colonnes C et D et revenons dans notre feuille de prévision maintenant, nous sommes en mesure de produire une prévision réelle, donc la vente prendra sa meilleure idée à vos paramètres. Vous connaissez vos prévisions. Commencez votre prévision et votre intervalle de confiance saisonnalité, etc. Mais si vous voulez personnaliser exactement la façon dont cela est configuré, allez de l'avant et explorez les options dans le coin inférieur gauche et ici nous avons un tas d' options supplémentaires à choisir, et une option est de changer à quel point vous voulez un prévisions. Alors peut-être qu'au lieu de 1574 jours, peut-être que nous avons seulement besoin de prévoir à 1400 jours, ce qui réduit la durée prévue de celui-ci. Une autre option consiste à en changer un. La prévision commence. Par défaut, cette prévision va commencer. Le dernier jour où nous avons des valeurs réelles et la raison pour laquelle excellé par défaut à adopter cette approche est que lui permet d'utiliser tous ces points de données. Tous ces chiffres réels historiques aident à générer la prévision. Mais ce que nous pouvons faire, c'est changer cela. Vous ne à quelque chose comme 1000 jours et forcez Excel pour commencer la prévision plus tôt. Et fondamentalement, cela nous donne une période de chevauchement où nous pouvons réellement comparer les valeurs prévues aux chiffres réels. Maintenant, l'inconvénient dont nous constatons les résultats ici est qu'en accélérant le début des prévisions, nous fournissons le début des prévisions, moins d'informations historiques pour exceller et donc potentiellement avoir un impact sur la précision de la prévision elle-même. Alors peut-être qu'on divise la différence ici en disant : Commencez à 1100 jours. Ça a l'air plutôt bien. Nous avons encore une bonne partie de chevauchement ici, mais cela semble être un peu plus réaliste. Maintenant, les autres éléments qui affaiblissent personnalisé ici l'intervalle de confiance et qui est fondamentalement créer ces deux limites les limites supérieure et inférieure et nous dire que nous pouvons être sûrs à 95% que les valeurs réelles observées dans le l'avenir sera dans ces limites, alors regardez ce qui se passe lorsque nous augmenterons la confiance à 99. Vous remarquerez que les limites deviennent plus importantes, créant une fenêtre plus large qui pourrait potentiellement contenir nos valeurs futures réelles. Et si nous réduisons cela à quelque chose comme 50% de confiance maintenant, nous avons une limite beaucoup plus étroite et une cible beaucoup plus difficile à atteindre. Par conséquent, étaient moins confiants que les valeurs réelles seront toujours dans cette plage. Donc, en général, tu vas vouloir rester avec quelque chose comme 90 ou 95 ce genre de norme d'air. Et à partir de là, nous pouvons également faire face à la saisonnalité. Par défaut, Excel détecte automatiquement la saisonnalité. Dans ce cas, s'il y avait un certain nombre de jours ou une certaine période où nous voyons un motif répéter encore et encore, nous pourrions définir ce modèle manuellement, et je vais vous montrer exactement comment nous pourrions le faire pour les données météorologiques. Et puis, lorsque nous créons la feuille, nous pouvons également inclure des statistiques de prévision comme il suffit de cocher cette case. Et enfin, nous avons notre plage chronologique et la colonne C Day valeurs d'index Living colonne D, et ensuite nous pouvons choisir si nous avions des points manquants que nous pourrions interpréter tard tôt de ceux à zéro. Dans ce cas, nous n'avons pas de points manquants. Et si nous avons des doublons, pourrait choisir comment résumer ou agréger. Dans la plupart des cas, ces doublons donnent un choix approprié. Donc avec ça, je suis confiant. Je suis content de nos choix. De cette façon, nous pouvons comprendre ça. Allez-y et cliquez. Créer, et ce qu'Excel fait est d'insérer une nouvelle feuille ici, nous donne un peu pop up toe. Bienvenue sur notre nouvelle feuille, et il est écrit : «  Super ». Nous avons créé un tableau avec une copie de nos données Colonnes A et B, ainsi que les valeurs prévues et notre confiance. Bounds dans les colonnes C, D et e Donc je l'ai eu. Ça a l'air bien. Allons de l'avant et faites glisser cette feuille à droite de notre feuille de prévision et nommez-la quelque chose comme la prévision boursière. Vérifie ça. Il a inclus le tableau pour nous, tout comme nous l'avions prévu. Il a inclus ces statistiques de prévision ici dans ce petit mini tableau dans les colonnes G et H. Je ne vais pas entrer dans celles en ce moment et j'ai ensuite obtenu nos copies réelles de nos points de données historiques ici. Dans vient A et B et si nous faisons défiler vers le bas ou encore plus efficace, sélectionné vente vide utilisé. Les commandes sont en approche descendante. Nous y allons, le dernier jour de nos valeurs observées ou dans ce cas, le premier jour où nous avons déterminé que nous voulons que nos prévisions commencent maintenant. Ces nouvelles colonnes sont également remplies ici, et elles sont en fait remplies de formules de prévision qui alimentent les entrées en fonction de la façon dont nous avons configuré nos paramètres dans la boîte de dialogue et de la même chose avec notre confiance supérieure. Les limites utilisaient des formules d'intervalle de confiance confiance ici. Donc, si nous voulions modifier l'un de ces paramètres, nous pourrions soit revenir en arrière et créer une toute nouvelle prévision à partir de zéro en utilisant la boîte de dialogue. Ou nous pourrions aller de l'avant et mettre à jour les entrées individuelles ou les arguments de ces fonctions et faire la table à jour dynamiquement. donc vraiment utile là-bas. Faites défiler le haut. Et une chose que je veux vous montrer ici est que dans ce cas nous pouvons éditer ce tableau comme n'importe quel autre. Et disons que nous voulions suivre une approche plus simple pour prévoir ces valeurs boursières et simplement aller à ses outils graphiques. Ajoutez un élément de graphique et lancez une ligne de tendance linéaire en place qui a créé cette ligne de tendance en pointillés bleus qui, comme vous pouvez le voir, suit presque la prévision exacte qu'Excel est créé. Donc, dans ce cas particulier, utiliser une prévision linéaire comme un premier passage très rapide et simple pourrait être une approche valide, et en fait, cela va produire presque exactement les mêmes valeurs que notre prévision ici. Mais ça ne marchera pas toujours. Et un cas où il tombe souvent à court est un cas où vous avez la saisonnalité. Revenons donc à notre feuille de prévision de base, Sélectionner les colonnes G et H Cette fois-ci, qui est nos données mensuelles de température. Retournez dans les données et voyons ce qui se passe ici et nous choisissons feuille de prévision. Alors jetez un oeil à ce modèle très, très différent ici, et il y a clairement une saisonnalité qui se produit au cours de ces six années de valeurs réelles. Donc, la différence entre cet exemple et l'exemple de stock est qu'un nous ne pouvons pas simplement gifler une option de ligne de tendance de base ici pour suivre ce chemin de prévision car il y a une tendance saisonnière qui se produit parce que, littéralement, nous examinons la valeur des données de la saison. Eso Excel dit bien que nous avons détecté automatiquement la saisonnalité et si vous l'avez dit manuellement, disaient essentiellement tous les 12 points, qui sont des mois, nous voyons la même répétition de modèle, ce qui a du sens. Donc, dans ce cas, je ne vais pas créer de nouvelles feuilles. Ça aura l'air pareil, a l'autre. Mais vous l'avez aussi. Bons exemples d'utilisation de la feuille de prévision excels pour fournir des valeurs prédites ou prévues l'aide de deux types de données très différents. 6. Détection Outlier: tout droit pour ce conseil pro, Nous sommes un peu plus avancés. Ceci est un conseil d'analyse quatre étoiles. Nous allons parler de la façon de trouver ou de détecter des valeurs aberrantes, en utilisant une combinaison de statistiques, fonctions et de formatage conditionnel. Maintenant, ce que nous allons faire ici, c'est utiliser des fonctions comme médiane et quartile toe calculent réellement valeurs aberrantes statistiques, et ensuite nous allons appliquer des règles de formatage conditionnel pour attirer l'attention sur eux ou les mettre en évidence dans nos colonnes. Donc, dans ce cas, nous examinons les données des athlètes avec les noms et la colonne A. Nous avons les hauteurs et la colonne B, les poids et les colonnes. Voir, Et ce qui va faire est de créer les calculs aberrants. En utilisant ces fonctions de stats, nous allons calculer la valeur médiane. Le premier quartile, troisième quartile, la plage inter quartile ou comme vous êtes et quelque chose appelé multiplicateur de défense. Et cette clôture est essentiellement ce qui détermine à quel point hors de la norme de valeur doit tomber pour être étiqueté comme aberrant. Et ce qui finira avec est une plage de valeurs à trouver par la clôture interne ou inférieure et la clôture supérieure ou extérieure, ce qui signifie que toutes les valeurs qui sortent bien de cette plage, techniquement étiquetées comme une valeur aberrante. Donc, à partir de là, tout ce que nous avons à faire est de nous diriger vers notre onglet d'accueil, règles de formatage conditionnel. Et nous allons utiliser une règle de surbrillance personnalisée pour mettre en évidence les cellules qui ne sont pas entre ces valeurs de clôture et vend F 8 F 11. Et lorsque nous appliquons cette règle de formatage à dans ce cas, la colonne B reçoit immédiatement les valeurs aberrantes surlignées en jaune Maintenant. Une chose à noter. Si vous avez affaire à des calculs beaucoup plus simples ou à des règles de mise en forme comme les valeurs de fin supérieure ou un pourcentage supérieur spécifique, utilisez simplement des formats conditionnels de base. Vous n'avez pas à passer par tout ce processus, mais ce que nous faisons est d'utiliser des fonctions statistiques réelles pour définir ces valeurs aberrantes d'une manière un peu plus scientifique. Et comme je vais vous le montrer à partir d'ici, nous pouvons ajuster notre clôture et voir comment cela a un impact, qui vend de l'air en surbrillance et étiqueté comme des valeurs aberrantes. Cas d'utilisation donc courants ici d'abord et avant tout identifier les anomalies statistiques dans l' ensemble de données en fonction de ces critères personnalisés que vous définissez et, deuxièmement, trouver et supprimer des valeurs qui peuvent avoir été mal saisies. Donc si on a vu une hauteur de 800 pouces. Vous savez, qui sera très rapidement marqué comme une valeur aberrante et un mentor quels paramètres nous choisissons. Et ce serait une assez bonne indication de l'erreur d'entrée de l'utilisateur et pas ah, pas seulement un très, très grand être humain. Alors avec cela, nous allons sauter dans un cahier de conseils pro. Nous allons jeter un oeil à cet ensemble de données de démonstration exacte et exécuter ce processus étape par étape. Bon, donc si vous suivez, vous devriez savoir que l'exercice avait votre table des matières. Et recherchez la démo de détection des valeurs aberrantes dans notre section Conseils Purple Analytics. Allez-y et liez directement à cela. Elle et ce que vous trouverez ici sont les noms des joueurs ou des athlètes et la colonne A. Et tout comme nous avons décrit les hauteurs, les poids et les colonnes B et C. Notre objectif est de remplir ces calculs statistiques ici dans la colonne F pour déterminer exactement comment nous traitons et définissons les valeurs aberrantes. Donc on va commencer par la médiane et vendre F à. Cela ne sera pas réellement utilisé dans notre calcul aberrant, mais c'est un excellent résumé statistique à inclure je vais juste utiliser la fonction médiane 0.2 colonne B appuyez sur OK. me dit que la hauteur médiane dans cet échantillon de 74 pouces maintenant qui nous amène aux fonctions de tuile de base. Commencez par taper une formule quartile, et vous verrez trois options différentes. Ici vous avez la version E X, c ou exclusive I NC inclus version et la version quartile originale, qui est une fonction de compatibilité compatible avec les versions antérieures d'Excel. Et ce que cela vous dit est qu'à l'origine, il n'y avait que la fonction quartile, puis à partir et je crois que 2010 Microsoft a commencé à introduire de nouvelles variantes de cette formule. Maintenant, dans ce cas particulier, nous verrons la même réponse quelle que soit la version que nous utilisons. Mais je veux vous montrer le i N C. La version inclusive en ce moment, certains onglets qui dans les trois versions utilisent les mêmes arguments, commençant par le tableau cominby, puis la tuile de cour, que vous pouvez étiqueter avec un 0123 ou quatre. Et dans ce cas, nous voulons le premier quartile ou le 25e centile dans une presse 81 là et le fermer et appuyer sur Entrée. Maintenant, ce que cela nous dit, c'est que les joueurs qui ont 72 pouces de haut sont au moins aussi grands que 25% des autres roses ou joueurs de l'échantillon. Maintenant un peu de nuance ici. Mais la différence entre les versions inclusives et exclusives est que la version exclusive déterminerait essentiellement la valeur supérieure à 25% des valeurs dans le tableau . La version inclusive détermine la valeur supérieure ou égale à 25 % des valeurs du tableau. C' est donc la différence. C' est la nuance. Des actes exclusifs comme des actes plus importants que des actes inclusifs comme supérieurs ou égaux à. Et une autre note ici. Si vous vouliez utiliser cette fonction pour renvoyer la valeur minimale ou maximale avec une étiquette de zéro ou quatre, vous devrez utiliser la version inclusive pour le faire. Donc, il va et lâcher. Sont un retour dans leur presse entrer, nous pouvons simplement copier cette formule. Coller ici pour 1/3 quartile simplement changé le 123 et nous obtenons un 75. Maintenant, la gamme Inter quartile d'air de I Q R. C' est un simple que le troisième trimestre je vais dans un quatre moins le premier quartile en F 3. C' est une gamme de trois pouces, et ensuite, notre multiplicateur de clôture. Maintenant, ce que fait la clôture est essentiellement de déterminer à quelle distance en dehors de notre plage interquartile une valeur doit tomber pour être étiquetée comme une valeur aberrante statistique. Ainsi, plus la clôture est élevée ou plus le multiplicateur de clôture est élevé, plus loin de notre gamme attendue. Cette valeur doit tomber pour être considérée comme une valeur aberrante, et il s'agit d'une valeur codée en dur. Dans de nombreux cas, 1.5 est un bon point de départ standard. Allons de l'avant et commençons là et enfin, nous devons déterminer notre clôture intérieure ou la clôture inférieure ainsi que notre clôture extérieure ou la clôture supérieure . Ainsi, la clôture inférieure est définie comme le premier quartile, moins parenthèse ouverte. Inter quartile gamme fois notre clôture donc F trois moins cinq fois à six qui renvoie un 67,5 . Cela signifie que la valeur la plus faible prise par Selcan avant d'être traitée comme une valeur aberrante est de 67,5 pouces. Logique très similaire ici pour la clôture extérieure ou la clôture supérieure. Prenez le troisième quartile et nous allons ajouter le I Q. R fois le multiplicateur de clôture Fermez-le. Appuyez sur Entrée. Même processus de pensée ici. La valeur la plus élevée qu'un Selcan prend avant qu'il ne soit traité comme une valeur aberrante est de 79,5 pouces. Maintenant, à partir d'ici, nous avons fait le plus dur. La dernière étape consiste à signaler ou à attirer l'attention sur les valeurs aberrantes réelles que nous avons définies ici dans la colonne B. Donc, c'est aussi attrayant . Ancienne flèche de changement de contrôle vers le bas pour saisir la colonne entière. Je vais aller dans notre menu d'accueil formatage conditionnel. Et nous allons utiliser, hum, hum, mettre en évidence les règles cellulaires ici. Et ce que nous voulons vraiment, ce sont des cellules qui ont une valeur qui ne se situe pas dans la plage de nos clôtures supérieures et inférieures. Donc, nous voulons une option pas entre deux, ce que nous n'avons pas est l'une de nos normes. Mais si nous allons dans plus de règles ici, nous avons cette option où nous pouvons dire des valeurs de cellule de format qui ne sont pas entre. Et puis nous pouvons simplement pointer vers les cellules qui nous préoccupent, qui sont tout le chemin en haut. Nous voulons donc mettre en évidence les cellules qui ne tombent pas dans cette plage. Ils ne sont pas entre huit ou 11 et nous nous conformons. Formater celui-ci est à vous d'utiliser une sorte de Phil jaune ici et appuyez sur OK et vérifier . On appuie sur OK maintenant. Toute valeur supérieure à la clôture supérieure supérieure à 79,5 est désormais mise en surbrillance. Et si nous passons à l'erreur de contrôle du bas vers le bas, nous avons des valeurs ici qui étaient en dessous de la clôture inférieure de 67,5. Nous avons donc essentiellement signalé ces valeurs aberrantes en fonction de nos conditions statistiques ou d'une configuration ici. Et je regarde ce qui se passe si on change ce multiplicateur de clôture d'un 1,5 à un. Maintenant, nous incluons plus de valeurs qui étaient étiquetées comme des valeurs aberrantes ici parce que nous avons essentiellement créé une gamme plus étroite de valeurs normales. Nous changeons ce multiplicateur de clôture pour ou agrandissons cette clôture. Maintenant, nous rendons moins probable qu'une valeur soit étiquetée comme aberrant. Donc nous ne voyons que quelques points de données ici dans la colonne B qui sont signalés dans ce cas, trois des plus grands joueurs de baseball du match. Donc, là, vous avez un excellent petit cours de crash sur la façon d'utiliser certaines de ces fonctions de statistiques relativement simples combinées avec le formatage conditionnel toe effectivement détecter et signaler les valeurs aberrantes dans vos données 7. Tableaux de données automatisés: très bien pour cette astuce pro, nous allons parler d'un conseil analytique quatre étoiles relativement avancé et nous allons discuter de la façon d'évaluer les entrées variables en utilisant des tables de données excels. Maintenant, les tables de données dont nous parlons ici sont différentes de vos plages de cellules standard , formatées sous forme de tableaux. En fait, je souhaite qu'Excel leur donne un nom différent parce qu'ils fonctionnent réellement. fait différemment, nous parlons sont les tables de données que vous trouverez dans votre onglet de données à partir des outils d'analyse Et ce que ces tables de données font est qu'elles vous permettent calculer sur tableau de résultats en cliquant sur un bouton basé sur une plage entière une variété de valeurs d'entrée potentielles. Donc pour vous montrer ce que je veux dire par là, nous allons regarder notre outil de calcul des coûts immobiliers, que nous avons vu tout au long du cours. Et l'idée ici est de comprendre exactement ce que sont nos coûts hypothécaires. Les coûts hypothécaires mensuels ressembleraient à des taux d'intérêt différents allant de 3 % à 10 % par tranches de moitié de pourcentage. Donc, l'approche manuelle fastidieuse de la vieille école serait de brancher ces différents taux d'intérêt dans la cellule H huit et de voir ce que les coûts hypothécaires crachent et vendent h 14. Mais au lieu de faire cela, nous allons devenir un peu plus sophistiqués, et nous allons sélectionner cette table cette plage vide en colonnes, Jane K. Nous allons utiliser l'outil de table de données excels pour produire un tableau de résultats sur la base de cette fourchette de taux d'intérêt. Donc, nous allons aller à notre ville de données pour explorer l'option de la table de données à partir des outils d'analyse et si. Et puis ici, nous avons seulement deux entrées potentielles une cellule d'entrée de ligne et une cellule d'entrée de colonne. Et dans cette démo particulière, tout ce que nous avons est une colonne contenant cette plage de valeurs et la cellule dans laquelle nous voulons tester cette plage de valeurs vit en h huit. Donc, en traitant l'âge de huit ans comme l'entrée de la colonne, Excel est capable de cracher tout ce tableau de résultats dans la colonne K en fonction de la fourchette des taux d'intérêt dans la colonne J. Donc, plutôt que de tester manuellement 15 ou environ différents intérêts et en enregistrant les résultats ou en tapant une formule 15 fois, nous avons configuré l'outil de table de données une fois et produit tous les résultats un clic. Maintenant, les tables de données peuvent être utilisées pour évaluer les résultats comme nous le voyons ici en fonction des changements apportés à une seule variable d'entrée comme nous l'avons montré avec le taux d'intérêt ou avec plusieurs variables, auquel cas nous branchons également une cellule d'entrée de ligne. Et je vais vous montrer un exemple des deux dès qu'on sautera dans Excel. Mais de vrais cas d'utilisation courante rapide calculant une matrice de résultats basée sur des combinaisons de valeurs d'entrée comme celle-ci. Paiements mensuels basés sur des facteurs tels que les taux d'intérêt et les montants des acomptes. Ou aller encore un peu plus loin. Vous pouvez utiliser cet outil pour identifier le résultat optimal compte tenu de multiples combinaisons d' entrées variables. Alors allons-y. Ouvrez notre classeur de conseils professionnels, retroussez nos manches et entraînez-vous à construire certaines de ces tables de données. Très bien, alors à partir de votre table des matières, allez de l'avant et faites défiler jusqu'aux conseils d'analyse. On va regarder la démo de la table de données dans ce cas, aller de l'avant et lier directement à cet onglet violet. Et ici, vous verrez notre calculateur de coût immobilier, qui, comme nous l'avons vu précédemment, prend un tas d'intrants comme le prix d'achat, ce taux d'imposition et certaines conditions de prêt et crache des sorties comme le montant du prêt hypothécaire coûts, impôts fonciers etc. Maintenant, ce que nous nous soucions dans ce cas, c'est le coût hypothécaire mensuel ici et vendre H 14. Et ce coût hypothécaire est fonction de quelques facteurs différents pour un, l'acompte et pour le taux d'intérêt, entre autres. Donc, si nous modifions votre acompte de 20% à 30%, vous remarquerez que nos coûts hypothécaires mensuels sont réduits. En revanche, si nous changeons notre taux d'intérêt de 4 % à 5 %, nos coûts hypothécaires augmentent. Ces deux facteurs ont donc une incidence sur le coût hypothécaire. Donc, ce que nous allons faire ici est de créer deux tables de données différentes pour tester ou évaluer différentes combinaisons de ces valeurs d'entrée potentielles. Donc, dans le premier cas ici, tout ce que nous testons, ce sont les variations du taux d'intérêt. première étape consiste donc à créer une colonne contenant chacune des variations du taux d'intérêt que nous voulons tester afin de voir l'impact sur notre formule de production ou sur les coûts hypothécaires. Et je dois juste vous montrer cette astuce ici, vous pouvez soit taper ces valeurs manuellement ou ce que vous pouvez faire et les supprimer Vous pouvez taper votre première valeur ici, survolez le coin inférieur droit et écoutez cela. Maintenez le bouton droit de la souris, faites glisser vers le bas et sauvegardez, puis relâchez pour accéder à ce type de menu caché ici. Et ce qu'on va faire, c'est tomber dans Siri en bas. On va remplir un Siris dans une colonne. On va monter ou augmenter de 0,5 %. On va s'arrêter à 10%. C' est notre presse de valeur maximale. OK, Booms a rempli cette parfaite, incrémentielle sérieuse pour nous d'un clic sur le bouton. Maintenant, le but ici est de remplir ces cellules vides ici de K 4 à K 18. Et pour le faire avec les tables de données, nous devons configurer les choses d'une manière très spécifique. La première étape, que nous venons de réaliser, consiste à définir la plage des valeurs d'entrée. La deuxième étape est en fait un lien avec la formule que nous cherchons à évaluer. Donc nous prenons juste la formule de H 14 et nous la référençons ici dans South K 3. Ce que cela va nous permettre de faire est de sélectionner toute cette gamme de cellules, qui inclut à la fois la formule elle-même et la variété des valeurs d'entrée. Et à partir de là, nous pouvons aller dans notre onglet de données. Et si l'analyse daté table et encore, tout comme notre démo. Nous n'avons pas de variables d'entrée dans une rangée, mais nous avons des entrées variables dans une colonne, et la cellule d'entrée elle-même n'est pas cette plage que je viens de créer. C' est la cellule qui est en fait référencée dans la formule qui contient le taux d'intérêt, qui dans ce cas est vendre H 8. Et c'est tout ce dont nous avons besoin pour configurer cette table de données appuyez sur OK, et voilà. Il est rempli tous les extrants de coûts mensuels en fonction de cette gamme différente de taux d'intérêt . Et comme vous pouvez le voir, il a généré un seul tableau ou tableau plutôt que 15 formules individuelles ici. Donc, en passant à notre prochaine démo, nous allons suivre la même approche, sauf que nous ajoutons un ensemble supplémentaire de critères ici pour différents acomptes de sorte qu'il fera ici est de créer une matrice réelle de valeurs qui ne tiennent pas compte que les changements au taux d'intérêt, mais aussi les changements à l'acompte 5 10 ou 20% donc très similaire processus ici et sélectionnez l'ensemble de la gamme de cellules, y compris la formule et les cellules d'entrée de ligne variable et de colonne. On va entrer dans les données. Que faire si l'analyse de la table datée ? Et maintenant, nous avons une cellule d'entrée de ligne. Et c'est la cellule de la formule contenant nos variations d'entrée de ligne, qui sont les acomptes de paiement en direct et vendent H sept. On y va. Et encore une fois, notre Colin est la cellule contenant le taux d'intérêt. Juste notre index de colonne et appuyez sur. OK, et vous l'avez là. Cette fois, nous avons produit un seul tableau bidimensionnel qui produit des valeurs de sortie basées sur plusieurs critères. Alors voilà. Ces tables de données dans Excel pourraient être un peu difficiles, un peu maladroit à travailler avec au début. Mais une fois que vous avez vraiment une poignée sur eux, ils pourraient être un excellent outil pour évaluer un tableau entier de résultats basés sur des entréesvariables entrées 8. Outils de recherche Power Query: d' accord. Je veux prendre quelques minutes et vous guider à travers une introduction rapide et brève à l'un des outils les plus puissants dans Excel appelé Power Query. Maintenant, c'est une astuce d'analyse avancée quatre étoiles. Nous allons parler de la façon de connecter, façonner, façonner, transformer et charger des données provenant de sources externes en Excel à l'aide de ces outils de requête Power. Donc, dans la mode Microsoft classique, vous pouvez voir ces outils nommés toutes sortes de choses différentes. Selon la version d'Excel que vous utilisez, vous pouvez voir la requête d'alimentation que vous pouvez voir. Obtenez des données que vous pouvez voir obtenir en transformation et tous signifie la même chose. J' utilise office 3 65 pro plus. Et c'est ce que je vois dans mon onglet de données. J' ai un groupe d'outils appelé Obtenir et transformer les données et ils obtiennent la commande de données sur la gauche. Et ce que nous allons faire dans cette démo est de se connecter à une note de fichier CSP plate que vous pouvez également vous connecter à des sources de base de données ou web ap. Je n'en suis pas ainsi et ainsi de suite. Cette affaire, on va rester simple. J' ai rendu ce fichier disponible, fait partie du cours. Fondamentalement, nous allons faire une analyse rapide de certaines données de campagne Kickstarter. Donc, une fois que nous pointons vers le fichier verra cet aperçu de la requête, puis cliquez sur Modifier le remorquage. Lancez notre éditeur de requêtes, s'ouvrira en tant que classeur séparé en dehors de notre environnement de feuille de calcul Excel, et vous verrez ici un aperçu de vos données ainsi que toutes sortes d'outils que nous avons pour façonner filtrer, personnaliser et transformer ces données avant de les charger dans Excel. C' est donc comme notre centre de commande pour configurer cette requête ou cette connexion. Maintenant, gardez à l'esprit, il n'y a aucun moyen que je puisse même gratter la surface de tous ces outils dans le cadre de cette démo particulière. Mais j'ai tout un cours qui va en profondeur dans ces outils le cours Power Query, Power pivot et Dax. Maintenant, aux fins de cette introduction, gardez à l'esprit qu'il y a quelques sections importantes de cette fenêtre d'édition de requête. Vous avez votre barre d'outils d'édition de requête en haut avec des options pour transformer ou ajouter de nouvelles colonnes et une fenêtre en bas à droite appelée Étapes appliquées. Maintenant, ces étapes appliquées sont des changements absolus de jeu parce que essentiellement, que se passe-t-il ici ? est que chaque fois que vous effectuez un ajustement ou une transformation, toute opération que vous appliquez à votre table est enregistrée en tant que nouvelles étapes appliquées exactement comme les étapes sont enregistrées dans une macro V B a. Maintenant, une fois que vous avez transformé et façonné vos données et que vous êtes prêt à les charger dans Excel à des endroits où vous pouvez les charger soit dans une feuille de calcul où elles vivront en lignes, colonnes et cellules, soit dans les données où vous pouvez compresser ces données et stocker beaucoup, beaucoup plus d'informations centaines de millions de rose. C' est également là que vous pouvez créer des modèles de relations pour créer des relations de table et connecter ou fusionner des informations provenant de plusieurs sources. Donc, pour résumer rapidement les cas d'utilisation courants numéro un se connecter à des fichiers plats ou à des sources de base de données, puis transformer ou filtrer ces données avant de les charger dans Excel. Pour une analyse plus approfondie, numéro deux Création d'un processus E T L entièrement automatisé, qui signifie extraire, transformer et charger qui pourrait être actualisé en un seul clic à mesure que de nouvelles données redeviennent disponibles . Détails. Air tout couvert dans ma requête de puissance, le pivot de puissance et le cours Dax. Donc, avec ça, allons sauter dans Excel. Vous allez vous guider à travers une démo rapide pour comment nous pouvons utiliser la requête de puissance pour se connecter et transformer sur un fichier CSP externe. Très bien, donc si vous voulez suivre, dirigez-vous votre table des matières et recherchez cette démo puissante Corey dans nos conseils d'analyse pourpre , et lorsque vous ferez un lien, tout ce que vous verrez ici est une feuille vierge. Pour l'instant. Maintenant, pour introduire ce sujet de la requête de puissance, j'ai mis à votre disposition un fichier de projet appelé projets Kickstarter dot CSP. N' hésitez pas à télécharger cela pour suivre ou simplement vous asseoir et regarder ce spectacle. Je vais aller à mon onglet de données ici quelques façons que je peux attraper un fichier CSP soit à partir de ce bouton Slash CSP ou dans la liste déroulante Obtenir des données, ce qui me montre quelques-unes des autres sources de données ainsi. Cette affaire, je vais aller dans le fichier de texte slash CSP. Je vais double-cliquer sur le fichier Kickstarter Project que j'ai chargé sur mon bureau. Donc, la première chose que nous allons voir est un aperçu rapide basé sur les 200 premières lignes de ce fichier a obtenu les noms du projet I DS, catégories, catégories principales, puis toutes sortes d'informations sur le moment où le projet a été lancé, quand la date limite est le nombre de bailleurs de fonds, combien d'argent a été promis et ainsi de suite. Maintenant, nous allons vouloir filtrer ces données, les réduire à ce dont nous avons besoin. Donc, nous allons cliquer sur l'orteil du bouton d'édition, en fait lancer l'éditeur de requête et vous verrez qu'il se lance dans une nouvelle fenêtre en dehors de l'environnement de feuille de calcul Excel. Et c'est là que nous allons faire toute cette mise en forme et le filtrage et la transformation des données avant qu'il ne soit chargé dans Excel et vous remarquerez qu'il a déjà appliqué quelques étapes pour identifier la source de mon arrêt juste ici promu les en-têtes, et il a détecté certains types de données ici et appliqué ces types de données automatiquement. Donc, pour des raisons de démonstration, ajoutons quelques filtres supplémentaires ici. Hum, peut-être que nous ne voulons pas que toutes les données de date limite ici aient des projets remontant à 2009 alors appliquons une date, filtrons et disons que les projets Onley sortent d'ici. Adobe lecteur qui sont après, Pourquoi ne pas dire 2014 afin que nous puissions aller à Décembre 2014 Sélectionner 31e Appuyez sur OK, nous avons ajouté une nouvelle étape appliquée pour filtrer ces rose, et nous pouvons trier cette colonne croissant comme nous le ferions avec n'importe quelle table Excel pour voir ça. Maintenant, nous avons affaire à des projets dont les dates limites remontent au 1er janvier. 2015. Maintenant, la même histoire ici, nous pouvons filtrer par pays. Pour l'instant, il suffit de regarder les projets U. S. S. On y va. Et maintenant, l'état si la campagne a réussi ou si elle a échoué ou a été annulé filtre d'approvisionnement là aussi. Et sur Lee, regardez les projets réussis. Appuyez sur. OK, maintenant on pourrait continuer à continuer et encore. On pourrait changer les types de données ici. Nous pourrions supprimer des colonnes supplémentaires, avoir des colonnes redondantes dans ce cas. Mais pour des raisons de démonstration, allons de l'avant et passons au chargement des données. Je vais choisir des vêtements et charger à partir du haut à gauche, et cela va me montrer mes options de chargement. Maintenant, si je ne voulais pas charger ces données dans une feuille de calcul et réellement stocker ces rose ces observations dans des lignes et des colonnes mais je pourrais faire est Onley créer la connexion et la déposer dans le modèle de données. Non, il se peut que vous n'ayez pas accès au modèle de données, selon la version d'Excel que vous utilisez. Et dans ce cas, je veux réserver la conversation du modèle de données pour un autre conseil professionnel. Donc, pour l'instant, nous allons utiliser l'option de table et réellement charger ces données de filtre que nous avons créées ici dans notre feuille de calcul existante, la feuille de calcul de requête d'alimentation et en vendre une et nous appuyons sur. Ok, nous verrons la douleur de la requête apparaître et il est dit, accord, nous chargeons des données de ce CSP du projet Kickstarter. Il a chargé 41 097 lignes et il a laissé tomber ces données sous forme de table ici en mon pouvoir. Corrie 10. Allons de l'avant et fermons cette douleur d'ici. Nous pouvons travailler avec cette table comme n'importe quel autre, nous pouvons insérer un tableau croisé dynamique, par exemple. Laissons tomber ceci dans une nouvelle feuille et onglet sur, et nous pouvons appeler ce pivot Kickstarter, par exemple. Et ici, nous avons nos champs de cette table Kickstarter et nous pouvons travailler comme un tableau croisé dynamique normal ici. Peut-être que vous voulez ventiler les données par catégorie principale ici sur Rose. Et dans ce cas, regardons le montant promis en dollars américains. américains Et comme n'importe quel autre pivot, nous pouvons changer les formats numériques. La monnaie aurait pu le faire dans l'éditeur de requête aussi. Et prenons ces projets d'état état comme filtres. Et rappelez-vous, nous avons trié il sont filtrés vers le bas pieds seulement montrer des projets réussis. C' est la seule option que nous avons ici. Maintenant, c'est plutôt cool. Nous pouvons maintenant voir combien d'argent a été promis par catégorie. Voir beaucoup d'argent et la technologie des jeux de conception, ce qui a du sens. Mais ce n'est pas vraiment ce qui rend la requête de puissance si puissante que nous aurions pu juste charger que CS étant copié et collé dans un onglet. Ce qui rend la requête Power si puissante, c'est que si nous voulons maintenant modifier certains paramètres ou fonctionnalités de notre connexion, peut-être que nous ne voulons pas filtrer les projets réussis Onley. Peut-être que nous voulons regarder les projets échoués maintenant. Au lieu de cela, nous n'avons pas à réinventer la roue. Nous n'avons pas à filtrer manuellement la table qui a été laissée en son pouvoir. Corey Tam. Au lieu de cela, nous pouvons aller dans les requêtes de données et les connexions, et nous pouvons faire un clic droit pour éditer cette requête de projet Kickstarter et le vérifier. Cette dernière étape, cette étape de Rose filtrée est l'endroit où nous avions filtré vers le pays égal U S et l' état du projet égal à réussi. Tout ce qu'on a à faire, c'est de cliquer sur ce petit équipement. Je viens éditer cette étape, et la deuxième ligne est le filtre d'état. Cliquez sur cette ellipse, supprimez ce filtre et appuyez sur OK et elle s'actualisera pour ramener ces lignes à partir du fichier CSP d'origine. Maintenant, lorsque nous fermons et chargeons, vous verrez dans notre requête la douleur que Kickstarter Project Curry se rafraîchit lui-même. Et ce que nous devrions voir, c'est que plus de données arrivent ici au lieu de seulement 41 000. Cela devrait montrer un total plus élevé parce que nous incluons maintenant des projets avec différents états de projets. Et c'est exactement ce que nous voyons. Maintenant, nous voyons 127 000 875 lignes chargées sur cette table et comme vous vous attendiez, nous pouvons éliminer notre douleur, diriger vers notre pivot, sauter dans les outils de tableau croisé dynamique et rafraîchir, et maintenant vérifier. Quand on regarde notre filtre d'état. Maintenant, nous n'avons pas seulement des options réussies. Nous avons échoué, annulé en direct et suspendu aussi. Et là, vous l'avez. Donc très, très rapide, très démo de niveau de surface de certains de ces outils de requête de puissance de base. Encore une fois, si vous voulez que les connaissances fondamentales profondes, consultez la requête de puissance, le pivot de puissance et le cours Dax et allez vraiment en profiter. Mais j'espère, pour l'instant qu'au moins vous a inspiré jouer avec certains de ces outils plus avancés et voir comment ils pourraient être en mesure de révolutionner votre flux de travail dans Excel. 9. Les données de modélisation modéliser les données: tout droit pour cette astuce pro. J' aimerais vous présenter un concept très puissant et exceller. Nous allons parler de la façon de construire une analyse des modèles de données relationnelles dans Excel excelle maintenant. Le modèle de données est utilisé à plusieurs fins différentes. Pour un, vous pouvez compresser extrêmement grandes quantités de données des centaines de millions de rose, sinon plus. Deuxièmement, vous pouvez créer des relations de table, ce qui est beaucoup plus efficace, une façon élégante de fusionner des données entre des tables ou des sources sans les assembler manuellement avec des formules auto. Troisièmement, vous pouvez ajouter de nouvelles colonnes et mesures calculées à l'aide d'un langage de formule appelé DAX ou d'expressions d'analyse de données. Il existe donc plusieurs façons d'introduire des données dans le modèle de données. Les méthodes les plus courantes pour créer une nouvelle connexion à une source de données à l'aide d'un outil comme la requête d'alimentation , également appelé obtenir des données ou obtenir la transformation et les charges directement à partir de cette connexion dans le modèle. Une façon plus simple d'ajouter des datées au modèle, surtout pour des raisons de démonstration, consiste à prendre une table à partir d'une feuille de calcul, qui est généralement une table beaucoup plus petite et à cliquer sur le bouton Ajouter au modèle de données dans l' alimentation onglet pivot. Maintenant, c'est un bon point de pause pour vous faire savoir que certaines versions d'Excel n' auront pas accès à ces outils de modélisation de données. Donc, si vous ne voyez pas votre onglet Power Pivot, le premier endroit à vérifier est les options de fichier. Adan et cherchez votre calme Adan spécifiquement, si vous ne voyez toujours pas le pouvoir, pivotez là. Je recommande Googling Où est le power pivot ? Et vous serez redirigé vers le site Web du support de bureau qui ressemble à ceci. Et ici, vous verrez les produits de bureau exacts qui ont accès à ces outils de modélisation de données et de pivot de puissance. Maintenant, en supposant que vous ayez accès au modèle de données, une fois que vous avez ajouté vos tables au modèle, vous serez en mesure de gérer ou de modifier la fenêtre de votre modèle de données et de voir quelques vues différentes comme vous voyez ici à gauche, nous avons la vue de diagramme, auquel cas nous voyons nos tableaux apparaître comme des objets individuels, et c'est là que nous pouvons créer les relations de table réelles entre eux, fonction de choses appelées clés primaires et étrangères. Sur la droite, nous avons notre vue de données, qui est un type plus traditionnel de mise en page tabulaire. Mais la beauté du modèle de données est qu'une fois que ces relations sont définies, une fois que vous avez créé ce que nous appelons un modèle de données relationnelles, qui, fins de cette conférence, je définit comme un groupe de tables liées, nous pouvons ensuite insérer un tableau croisé dynamique pour explorer et analyser les données de toutes ces sources connexes dans une seule vue. Et c'est pourquoi nous appelons cela un pivot de puissance. Il ressemble et se sent exactement comme un tableau croisé dynamique régulier, mais il est assis au-dessus d'un modèle de données. Cas d'utilisation donc courants ici, combinant des informations provenant de plusieurs sources sans les écraser en fusionnant ou en utilisant des fonctions comme Look Up ou Index et deux. C' est la base pour construire des solutions d'intelligence décisionnelle robustes qui peuvent intégrer et fusionner des sources de données de toutes sortes d'endroits, comme les données de vente. Finances RH, marketing, etc. Donc, avec ça, allons sauter dans Excel. Je vais passer par une démo extrêmement rapide et très brève pour montrer certains de ce que ces modèles de données et outils de pivot de puissance conduisent. Très bien, donc si vous vous sentez courageux et que vous souhaitez suivre, rendez-vous à votre table des matières dans votre cahier de protestation à la recherche des conseils de Purple Analytics ici et passons dans la démo quatre étoiles de modélisation de données. Nous allons aller de l'avant et nous allons ouvrir un lien vers cette feuille, et ce que nous examinons ici est un tableau appelé Transactions. Nous avons la colonne de date de transaction A a obtenu le produit qui a été vendu et Colin être quelques informations sur le client basé sur un client i d. Ici, dans la colonne C et puis les valeurs réelles ici, la quantité vendue dans la colonne D Maintenant sur son propre, cette table ne nous est pas très utile. On pourrait le brancher dans un tableau croisé dynamique et regrouper la quantité ou les ventes. Vous savez, pour le produit I s ou les clients ou jours. Mais à quoi ça nous dit vraiment que nous avons vendu le produit numéro 76 six fois, non ? Nous n'avons pas de contexte supplémentaire à ce sujet. Donc ce que nous aimerions vraiment Voici quelques tableaux de recherche qui pourraient potentiellement cartographier ce produit I d ou ce client i d deux dimensions supplémentaires ou des informations supplémentaires que nous pouvons utiliser pour vraiment en savoir plus sur ces ventes. Et en fait, c'est exactement ce que nous avons. Si nous développons les colonnes de groupe. Vous verrez qu'il y a trois tables de plus. J' ai une table verte appelée produits. Nous avons une table orange appelée clients et une table jaune appelée Calendrier. Maintenant, comme vous pouvez vous y attendre, chacune de ces tables comprend une colonne I D ou, dans le cas du calendrier, une colonne de date, qui peut mapper ces informations à nos transactions. Données dans les colonnes Athor profonde. En d'autres termes, si nous connaissons le produit I D, ce que nous faisons, alors en créant une connexion ou une relation avec la table de produits, alors nous connaissons également la marque du produit et le nom du produit, ainsi que le prix de détail et le coût. Il en va de même pour le client i D. Cette clé peut être utilisée pour extraire des informations sur ces clients les noms, villes, pays, état matrimonial, sexe quel type d'adhésion ils ont. Toutes ces informations peuvent être liées à cette table de transactions car nous avons les colonnes clés qui peuvent les connecter. Vous pourriez donc être tenté d'aller de l'avant et commencer à écrire des fonctions comme la recherche vers le haut ou la correspondance d'index pour les lier ensemble dans une seule table maître. Parce qu'à la vieille école, Excel était habitué à avoir besoin d'une seule table ou d'une seule source pour les tableaux croisés dynamiques. C' était toujours la seule grande limitation des pivots. Donc peut-être que vous tapez, vous savez, si vous regardez ici, nous regardons le produit que j'ai trouvé dans notre table de produits et saisissons la deuxième colonne avec une correspondance exacte. Et boum, on y va. Nous avons maintenant notre marque de produit dans la colonne E. Nous avons donc lié cette marque de produit à notre tableau de transactions. Maintenant, écoutez. Nous pourrions poursuivre ce processus pour obtenir le nom du produit, prix de détail, ainsi que toutes les informations sur le client et toutes les informations de notre calendrier. Mais il y a deux problèmes avec ce numéro un. Et si ces tables incluaient des centaines de colonnes au lieu d'une poignée comme nous avons ici tout d'un coup ? Cette approche n'est pas tout à fait évolutive, et ce que nous avons créé est une table vraiment inefficace avec une tonne de valeurs en double qui ne sont tout simplement pas nécessaires. Alors allons de l'avant et supprimons cette colonne ici, et je vais vous montrer plus élégante l'approche plus sophistiquée pour accomplir la même chose en utilisant le modèle de données. Donc, j'ai déjà ajouté ces tableaux au modèle de données, mais si vous souhaitez vous entraîner, vous devez sélectionner une table individuelle, explorer votre puissance, le temps de pivot et cliquer sur cette annonce pour le bouton de modèle de données ici Maintenant, parce que je les ai déjà ajoutés, nous pouvons aller à notre bouton de gestion qui ouvrira la fenêtre du modèle de données ici. Nous sommes dans notre vue de données. Par défaut, nous pouvons voir ces quatre tableaux au format tabulaire sous forme d'onglets séparés et de vue de diagramme, qui montre réellement chacune de nos tables comme un objet sur la toile. Maintenant, vous remarquerez que ces lignes reliant les tables représentent les relations. Donc, dans ce cas, le produit I D. Qui est mis en évidence, est ce qui relie les produits aux transactions. Nous avons un client, i d, qui connecte les clients aux transactions. Et enfin, nous avons notre date de transaction qui se connecte au champ de date dans notre tableau de calendrier. Maintenant, pour vous montrer comment créer ces relations à partir de zéro, aller de l'avant à l'onglet de conception ici, gérer les relations, et tout ce que nous avons à faire est de sélectionner ces trois relations, appuyez sur supprimer, d' accord. Et quand nous fermerons cette boîte de dialogue maintenant, nous verrons ces tables déconnectées à nouveau. Et tout ce que nous devons faire pour simple est de saisir le champ que nous voulons et de le connecter à une clé primaire dans la table de recherche. Donc la date de la transaction à ce jour produit i D au produit i d. Client i d au client 18. Et c'est simple, c'est qu'en moins de 10 secondes, nous avons répliqué tous les efforts de création de ces fonctions de recherche ou d'index pour assembler manuellement ces données ensemble. Et nous avons obtenu exactement le même résultat. Et maintenant, voici la meilleure partie. Je retourne dans l'onglet d'accueil ici, insérez un tableau croisé dynamique dans une nouvelle presse de feuille de calcul. OK, nous allons double-cliquer pour nommer cette fois quelque chose comme le pivot du modèle de données. Voilà la beauté de ça. Maintenant, nous avons affaire à un pivot de puissance, qui est techniquement la même ancienne disposition de tableau croisé dynamique juste assis au-dessus d'un modèle de données. Et nous avons accès à toutes nos tables de modèles de données, calendriers, clients, produits et transactions, ainsi qu'à d'autres tables du classeur, qui dans ce cas ne sera pas utile car il n'y a aucune relation avec trouver entre eux. Donc, si nous explorons notre table de transactions, vous verrez quelques mesures différentes que j'ai définies les transactions, quantité totale et le chiffre d'affaires. Elles ont été conçues avec un langage appelé Dax, qui est hors du cadre de ce cours, mais c'est quelque chose que je couvre dans mon parapet de requête de puissance et cours Dax. Alors saisissons la quantité totale. Tirez ça dans nos valeurs. Ici et maintenant, nous pouvons accéder à n'importe lequel de nos autres tables et répartir ces quantités de toutes les manières différentes, comme la marque de sous-produit sur Rose, par exemple, ici nous pouvons voir que les produits américains vendus 1200 atomiques produits vendus 695. Nous pouvons également saisir un champ de notre table de clients, comme la ville de clients. Faites glisser cela dans et les étiquettes de rôle secondaires. Marque de produit Pope. Donc, comme vous pouvez le voir, je manipule ce pivot exactement comme je manipulerais n'importe quel autre tableau croisé dynamique dans Excel . La seule différence est que maintenant je suis capable d'accéder aux champs à travers quatre tables différentes qui sont connectées uniquement avec les relations que nous avons définies dans le modèle de données. Des trucs incroyablement puissants 10. Fonctions CUBE: d' accord. Il est temps de plonger dans un conseil d'analyse de niveau cinq étoiles, vraiment expert. Nous allons parler de la façon d'explorer des modèles de données en dehors des pivots en utilisant quelque chose appelé fonctions de cube. Maintenant, deux mises en garde avant de plonger. Numéro un. Si vous n'avez pas consulté le conseil professionnel de la modélisation des données ou que vous n'avez pas appris sur les modèles de données dans le passé et que vous vous encouragez fortement à le faire numéro deux, vous aurez besoin d'une version d'Excel. Il est compatible avec le modèle de données et avec power pivot. Si vous n'êtes pas sûr, allez sur la recherche Google Where is power pivot et vous allez accéder au site Web de support de bureau qui ressemble à ceci. Et ici, vous pourrez voir les versions exactes d'Excel qui incluent l'accès à ces outils de modèle de données et power pivot. Maintenant, revenons-y. Donc les fonctions de cube. Le moyen le plus simple d'explorer les données d'un modèle de données consiste à utiliser un tableau croisé dynamique ou un pivot de puissance. Mais ce que les fonctions de cube font est de vous permettre d'extraire des valeurs filtrées spécifiques de votre modèle de données et de les extraire directement dans les cellules de feuille de calcul. Ainsi, par exemple, si vous voulez créer une vue qui ressemble à ceci. Vous pouvez utiliser des fonctions de cube pour ce faire, et dans mon expérience, il y a quatre types de fonctions de cube que j'utilise presque exclusivement. Le premier est appelé un jeu de cubes, et essentiellement un ensemble de cubes est une collection d'éléments ou de membres de cube de votre modèle. Il est essentiellement équivalent à une colonne entière d'une table dans votre modèle. Dans ce cas, nous avons défini un jeu de cubes contenant des informations sur les marques de produits. Maintenant, dans les jeux de cubes, vous avez quelque chose appelé membres de cube et ces air ombrés en bleu ici dans le membre Visual Cube est un élément unique provenant d'un ensemble de cubes. Donc, en d'autres termes, c'est un élément d'une colonne de table ici. Nous examinons différentes valeurs dans la colonne d'adhésion de notre table client : or, or, argent, bronze et normal. Et notez que nous utilisons également des membres Q pour définir des mesures quantitatives ou des mesures calculées comme les transactions ou la quantité. Ensuite, nous avons un type spécial de membre appelé membre classé cube. Ceux-ci sont ombrés en vert ici dans le visuel et ces air, tout comme vous les membres, leurs éléments individuels dans un ensemble de cubes. La seule différence est qu'ils sont basés sur un rang d'ordre. Et c'est ce qui nous permet de faire des choses comme montrer les cinq meilleurs produits par quantité, exemple, comme vous le voyez ici. Et enfin, nous avons notre quatrième type de cube primaire, qui est la valeur du cube, et ce sont toutes les cellules ombrées en jaune. Là, les valeurs numériques réelles qui étaient agrégées en fonction d'un ensemble d'expressions membres. Et ces expressions de membre nous aident à filtrer ces valeurs pour récupérer les nombres appropriés . Maintenant, ces condamnent finement être délicat et un peu peu familier à travailler avec. Au début, je vais vous guider à travers exactement comment construire une vue juste comme ça. Mais gardez à l'esprit qu'il existe de nombreux autres types de fonctions de cube, et j'ai inclus un lien pour plus d'informations ici. Ce lien court vous mènera à la page de documentation de support de bureau quatre fonctions Cube , qui ressemble à ceci, et ici vous pouvez faire défiler et en apprendre un peu plus sur les fondamentaux de Cuba et certaines de ces autres options que je ne couvre pas. Donc, les cas d'utilisation courants ici pour un tableur de construction de rapports ou de tableaux de bord basés sur une feuille de calcul qui se trouvent au-dessus des données de votre modèle de données sans avoir à compter sur des tableaux croisés dynamiques et des fonctions de cube pourraient être un excellent moyen de documenter tous les ensembles et membres dans votre modèle de données. Donc, avec cela, allons entrer dans notre classeur de conseils professionnels et nous entrainons à construire une vue de rapport comme celle que vous voyez ici. Bon, donc si vous suivez le cours, vous savez, l' vous savez, exercice a dû faire défiler votre table des matières jusqu'à vos conseils d'analyse violets ici, nous allons explorer cette démo de fonction de cinq étoiles Cube. Vas-y et relie-toi à cette feuille. Maintenant, ce que vous regardez ici est un modèle vierge de base que j'ai créé à partir d'un exemple de rapport . n'y a pas de formules ou quoi que ce soit ici. C' est juste du format. Il se vend. Et notre objectif est de convertir cela et de le relier en un rapport entièrement fonctionnel qui extrait les données du modèle de données sous-jacent. Maintenant, pour commencer, nous devons faire un examen rapide du modèle de données avec lequel nous travaillons. Donc, je vais sauter dans l'onglet Power Pivot, cliquer sur gérer le modèle de données, et allons passer dans la vue de diagramme ici Donc c'est le modèle avec lequel nous travaillons dans ce classeur. Nous avons des données sur les transactions avec trois mesures calculées, quantité totale et le chiffre d'affaires. Les mesures sont des métriques que nous allons intégrer dans notre rapport. Et nous avons aussi un certain nombre de champs dont nous pouvons tirer. Ces trois tables de recherche ont des champs de calendrier comme le jour, le mois, le trimestre et l'année des informations sur les clients comme les pays et les villes, sexe et le statut de la carte de membre et les produits qui incluent des choses comme la marque, le nom, le prix de détail et le coût. Donc, continuons et insérons réellement un tableau croisé dynamique de notre modèle de données, et je vais le déposer dans un classeur existant. Je vais le mettre ici, euh, dans notre feuille de calcul des fonctions cube. Et laissons tomber ici et vendons H 2 et appuyez sur OK. Et ce que nous allons faire est d'utiliser ce tableau croisé dynamique pour aider à repérer la file d'attente et nous assurer que les nombres qui remplissaient avec les fonctions de cube sont exacts. Et puis une fois que nous l'avons fait, nous pouvons aller de l'avant et supprimer le pivot lui-même et être laissé avec juste le rapport personnalisé que nous avons construit de sorte que, comme vous pouvez le voir ici, nous avons quelques ventilations différentes des données. Nous examinons trois paramètres différents. Transactions, quantité et chiffre d'affaires. abord, nous le ventirons par mois pour les trois derniers mois. Avril, mai et juin. Ensuite, nous faisons une ventilation par type de membre doré, argent et bronze. Et ce troisième tableau montre ici les ventes par produit pour les cinq principaux produits en fonction des transactions, quantité ou du chiffre d'affaires. Commençons donc par les ventes par mois. Et je vais explorer ce pivot, et je vais tirer dans certains de ces champs transactions, quantité et chiffre d'affaires. Et dans ce cas, nous voulons le décomposer par mois à partir de ma table calendrier Grab nom mois tirant dans rose . Et dans ce cas, tout ce qui nous intéresse vraiment, c'est la presse d'avril, de mai et de juin. Ok, trions ça un vertige. C' est donc un petit aperçu de la vue que nous essayons d'atteindre ici en tirant ces mêmes valeurs dans les cellules de la feuille de calcul elles-mêmes. Donc, si vous vous souvenez que la fonction de valeur de cube était le type de cube qui peut extraire ou agréger ces valeurs numériques. Le problème est, si nous commençons par simplement taper la valeur du cube. On ouvre la parenthèse. Notre connexion va commencer par un devis ouvert et ce sera ce modèle de données de classeur . Vous pouvez appuyer sur le verrouillage de tabulation que dans fermé La citation Maintenant, le deuxième argument et le troisième et le quatrième et le cinquième pour une fonction de valeur de cube vous demandent essentiellement de pointer vers une expression de membre. Et cette expression de membre aide à indiquer à Excel comment filtrer les valeurs à renvoyer. Donc, dans ce cas, nous filtrons vers le bas en fonction de deux conditions ou critères différents. Nom du mois et valeur cinq et virgule du nom de la mesure dans la cellule C quatre. C' est donc vraiment tout ce dont j'ai besoin pour cette fonction de valeur de cube. Je ferme la presse entre parenthèses, je reçois N A. Et la raison pour laquelle j'obtiens cette erreur est que les valeurs Q ne savent pas comment interpréter les cellules qui contiennent juste du texte. Donc, pour l'instant, cette cellule ne signifie rien. C' est juste une chaîne de texte qui dit des transactions. Même chose avec mes noms de mois. Avril, Mai Juin argent doré, bronze. Donc, ce que nous allons devoir faire est de convertir ces chaînes de texte en fonctions de membre Cube réelles . Donc, commençons avec nos mesures ici, nous pouvons taper égaux membre Cube ouvert Citation ce modèle de données de classeur Commencez comme ceci à chaque fois que vous passez à l'expression membre et c'est le champ ou l'élément de notre modèle de données que nous veulent capturer dans cette cellule. Alors ouvrez un autre devis. Cela vous permettra d'accéder aux tables et aux champs de votre modèle. Dans ce cas, nous voulons un champ de nos mesures. Et si nous entrons dans une période, cela nous amène au niveau suivant de la hiérarchie, qui est la liste réelle des options de mesure ici. Et nous voulons des transactions. Donc je vais l'insérer, fermer la citation, fermer la parenthèse, appuyer sur Entrée. Donc maintenant, il dit toujours les transactions. Mais maintenant c'est un membre réel Qué, et nous allons passer par le même processus ici pour avril égale membre Cube de ce modèle de données de classeur et l'expression de membre, rappelez-vous, rappelez-vous, a ouvert la citation à nouveau. Cette fois, il vient de la période de la table de calendrier à la liste des colonnes que nous voulons le nom de mois Colin. Et maintenant, parce que nous voulons un mois spécifique dans le nom de Colin, nous allons ajouter une période de plus et taper le nom du mois entre parenthèses. Donc, nous voulons spécifiquement le mois d'avril, puis fermer le devis. Fermez la parenthèse. N' hésitez pas à faire une pause sur l'écran pour vous assurer que vous avez la bonne fonction et je vais appuyer, entrer et vérifier. Deux choses arrivent. Numéro un. Ma cellule membre de cue prend désormais la valeur April, ce qui n'est pas seulement du texte. Il est un membre réel Qué, et notre valeur de cube retourne maintenant un nombre approprié, qui est 6588 que nous pouvons mapper à notre pivot en tant que Q. Et cela ne correspond. Nous sommes donc sur la bonne voie. Et maintenant que nous avons défini certaines d'entre elles, il est très facile de les appliquer au reste de nous-mêmes afin que nous puissions saisir cette colonne de transactions , copier, coller deux fois de plus, puis simplement ajuster ce dernier argument des transactions à la quantité totale et des transactions à la presse de revenus. Entrez la même chose avec Avril, Mai et Juin. Je vais accélérer ça deux fois de plus et changer le mois d'avril dans la dernière dispute. Deux principaux et enfin à Juin. Et tout comme les fonctions Excel régulières. Nous pouvons traiter nos types de référence avec soin ici parce que nos mois vivent toujours dans la colonne B. Nous pouvons corriger cette référence de colonne. Et parce que nos en-têtes sont des mesures vivent toujours dans la rangée quatre. Nous pouvons corriger cette référence de ligne et appuyer sur Entrée et vérifier ceci. traîné vers le bas, glisser sur et boom ! Nous avons toutes nos valeurs Q remplies. On peut vérifier contre le pivot. Tout a l'air bien. Et maintenant, nous commençons à Cruz. On peut prendre un de ces membres de cube du mois. Nous pouvons le coller dans le modèle de type de membre ici et maintenant, au lieu de la table de calendrier, nous allons ouvrir ceci et nous voulons qu'il se sent de notre table de clients. Et la colonne que nous voulons est la colonne de la carte de membre. Et dans ce cas, pour la première valeur, les membres spécifiquement que nous voulons sont dorés, sorte que vous pouvez le taper en guillemets fermés, fermer les parenthèses, appuyer sur Entrée, puis copier que deux fois de plus pour peupler l'argent et le bronze. Donc l'argent juste là et le bronze juste ici et maintenant vérifier. Copiez cette valeur de cube, collez-la sur lee. Les références que nous voulons déplacer sont en mouvement, les autres sont fixes. Ils pointent toujours vers les en-têtes corrects ici ainsi que les membres appropriés pour le type de membre ici dans la colonne B. Alors allons de l'avant et donnons. C' était pauvre mois. Nom out, carte de membre complet, et nous pouvons juste re trier un peu d'or au sommet et argent que le bronze. Utilisez les valeurs que nous devrions voir. Et boum ! Ce sont les valeurs que nous voyons ici à partir de nos valeurs Q. Donc on est deux pour deux. La dernière chose que nous devons faire ici est de remplir ces ventes par matrice de produits, qui va tirer dans les cinq meilleurs produits. Alors voilà le piège. Nous ne savons pas ce que sont les cinq meilleurs produits, donc nous ne pouvons pas simplement commencer à taper les membres réguliers Cube à partir de zéro comme nous l'avons fait avec le mois ou type de membre. Maintenant, nous pouvons trier les différentes marques de produits dans le pivot pour déterminer quels seront les cinq premiers . Mais si nous voulons que cette vue change dynamiquement et toujours trier en fonction des données actuelles, nous avons une meilleure approche pour le faire et ce que nous allons faire ici est introduit un ensemble de cubes ici dans la colonne B 16. Alors allons de l'avant et tapez Cube set fonction cette fois comme toujours, va commencer par la connexion à ce modèle de données de classeur. Et maintenant, l'expression set va être la liste ou la collection de marques de produits, que vous allez venir de notre table de produits. Alors ouvrez le devis produits marque de produit point. Et maintenant, voici la clé. La dernière petite option après le point je ne veux pas sélectionner tout, et je ne veux pas sélectionner un élément spécifique. Donc, je vais taper les membres, qui va forcer cet ensemble de cube à stocker tous les membres possibles de cette marque de produit Colin a fermé la citation, et maintenant nous devons mettre à jour ou remplir les arguments supplémentaires ici. Donc, pour la légende, ce que nous voulons que la cellule affiche, faisons des marques. Et maintenant l'ordre de tri. C' est ainsi que nous déterminons comment les membres classés par cube sont triés dans cet ensemble. Donc, dans ce cas, on va trier les volumes élevés descendant sur le dessus. C' est le numéro deux. Et qu'est-ce qu'on veut trier ? Eh bien, nous pouvons choisir n'importe laquelle de ces mesures. Allons de l'avant et choisissez les transactions. Et parce que cela vient de notre table de mesures et ouvrir le devis mesure point transaction est fermé Le devis. Fermez les parenthèses, mettez l'écran en pause un instant. Assure-toi que tu l'as descendu. Si longue fonction et nous allons entrer ça dans. Donc toujours voir est le mot marques ici. Mais cette cellule contient tout un ensemble de membres, et cela va nous permettre d'utiliser maintenant quelque chose appelé un membre classé cube pour extraire éléments individuels de cet ensemble. Donc, vérifiez qu'il est égal à membre classé Cube à partir de ce modèle de données de classeur. Maintenant, l'expression set est une référence à un jeu de cubes, que nous venons de créer ici dans Selby 16 Press. Permettez-moi de le verrouiller parce que cet ensemble de cubes vivra toujours là. Et maintenant pour le rang, vous remarquerez que j'ai créé un petit index de 1 à 5. Dans la colonne A. Ça va être notre indice de classement. Donc, pointons juste sur ce numéro de rang, et nous pouvons aller de l'avant et fermer la fonction appuyez sur Entrée, et le premier élément que nous voyons est tout. Si nous cliquons et faisons glisser vers le bas, nous voyons quelques marques de produits différentes apparaissant Karmanos, Telltale Ebony et High Top. Maintenant, les questions que tout n'est pas une marque individuelle. C' est en fait le total de toutes les marques et qui apparaîtra toujours en haut, avoir un ensemble de cubes. Donc, la façon la plus simple de juste pour cela est de simplement commencer par le deuxième rang, puis trois et quatre à cinq, puis six. Et parce que nous avons défini une référence est, eh bien, copier cette valeur. Collez-le, faites-le glisser vers le bas, faites-le glisser dessus, et ça a l'air bien. Mais nous allons faire un dernier Que A et R pivoter une carte membre à part entière forage vers les produits marque de produit dans, et nous allons trier ces marques de produits descendant par transactions et presse. OK, regarde ça. Karmanos Telltale Ébène Hi Top Tri État. Même exactement cinq que nous avons rempli en utilisant les membres de rang Cube et les ensembles Cube, et il semble que les valeurs des transactions, de la quantité et du chiffre d'affaires correspondent parfaitement. Le pivot a donc fait son travail et recommande d'en garder un juste pour une exploration rapide et facile des données . Mais dans ce cas, nous pouvons aller de l'avant et sélectionner ces colonnes, supprimer le pivot, et là nous allons. Nous sommes restés avec un format personnalisé parfait et un rapport que nous avons construit entièrement en utilisant des formulesde cube des formules 11. Simulation Monte Carlo: très bien, je veux partager l'une de mes techniques Advanced Analytics préférées dans Excel. Il s'agit d'un conseil de niveau expert cinq étoiles . Ce que nous allons faire, c'est construire notre propre modèle de simulation Monte Carlo entièrement dans Excel. Non V B A. Pas de macros, Juste vente directe des formules et des tables de données. Maintenant, la simulation Monte Carlo. Il est généralement utilisé pour prédire la probabilité de certains résultats futurs en fonction d' échantillonnage aléatoire répété. En d'autres termes, nous simulons au hasard le même résultat encore et encore et encore et encore, puis analysons l'ensemble des résultats obtenus. Alors, quelle meilleure façon de démontrer la simulation de Monte Carlo que de construire notre propre simulateur de roulette dans Excel ? Donc, la façon dont cela fonctionne est que vous pouvez placer un certain type de pari rouge ou noir, pair ou impair, numéros spécifiques, et chaque pari est associé à un paiement donné. Donc, après avoir placé votre pari, déterminer un pari étaient en utilisant couru entre deux essentiellement randomiser ou simuler un spin de la roue de la roulette. Dans ce cas particulier, nous avons parié 10$ sur le rouge. Le résultat est le numéro 23 rouge. Donc on a gagné 10 dollars. Mais le but n'est pas seulement de s'asseoir ici et de tourner au hasard encore et encore. Ce que nous voulons créer est quelque chose comme celui-ci où nous enregistrons les résultats d' un certain nombre de simulations dans ce cas, 10 tours, puis évaluons ces résultats et les résumons avec des fonctions statistiques de base . Donc, nous calculons ici le gain total ou la perte, le nombre de tours, le nombre de victoires par rapport aux pertes, la moyenne du pourcentage de victoires, rendement par tour et le maximum de base et les hommes. Et l'outil que nous utilisons toe réellement conduire ces simulations est excelle table de données. Donc, nous allons aller à notre onglet données, déposer dans nos outils d'analyse et utiliser la table de données pour créer et enregistrer autant simulations que nous le déterminons. Maintenant. Évidemment, nous utilisons cela pour créer quelque chose d'amusant et intéressant. Mais il y a un certain nombre de cas d'utilisation très sérieux et très réalistes où cela pourrait entrer en jeu pour simuler au hasard un modèle économique des milliers de fois afin de comprendre la probabilité de quelque chose comme un profit par rapport à un ou de tirer parti de ce qui crée des modèles prédictifs qui peuvent effectivement tenir compte d'un certain degré d'incertitude pour un ou plusieurs intrants du modèle, comme ce que les taux d'intérêt feront à l'avenir ou comment les coûts de l'offre changeront. C' est donc un outil d'analyse et de science des données incroyablement puissant que nous pouvons jouer avec et apprendre ici dans l'environnement Excel familier. Alors allons de l'avant et sautons dans son cahier d'exercices de conseil professionnel et pratiquons la construction de certaines de ces simulations. Bon, donc si vous voulez suivre, allez à votre table des matières, allez à votre table des matières, faites défiler les conseils d'analyse ici en violet et nous allons sauter dans notre démo de simulation de Monte Carlo. Allez-y et liez directement à cet onglet, et voici notre simulateur de roulette. Maintenant, pour ceux d'entre vous qui ne sont pas familiers avec le jeu de Relent, voilà à quoi ressemble la table. Vous avez 36 numéros, moitié noir, moitié lu, ainsi que des nombres verts zéro et double zéro. Ainsi, le concessionnaire fera tourner une roue qui contient les 38 fentes associées à chacun de ces numéros. En ce qui concerne les paris faras, vous pouvez placer des paris de différentes façons, que vous pouvez voir ici dans cette cellule déroulante. Vous pouvez parier sur des numéros individuels, qui sont les résultats les plus rares et donc le paiement, le plus élevé dans ce cas, $35 à 1 sur votre pari. Vous pouvez également parier sur le 1er 3ème des numéros. 1ère 12 2e 12 ou 3ème 12 la 1ère 2ème ou troisième colonne de nombres. La première moitié de la seconde, la moitié des nombres pairs. Numéros impairs, rouge ou noir. Donc, à titre d'exemple simple, disons que nous parions sur le noir. Nous pouvons entrer un certain montant de mise, disons 10$. Ce paiement nous dit qu'il parie sur le noir paie 1 à 1. Donc si on gagne, on aura 10$. Nous perdons, nous perdons les 10$ que nous avons misé. Donc, à partir de là, nous compresser le bouton F neuf calculer ou la tête à son onglet de formule et calculer rapidement maintenant bouton pour simuler différents tours de la roue. Donc, comme vous pouvez le voir, nous avons parié sur le noir. Donc, tout résultat rouge signifie que nous perdons notre pari vers le bas 10$. Tout résultat noir signifie que nous gagnons 10$ à cause de l'oneto. Un paiement. Maintenant, ce qui nous intéresse vraiment, c'est l'information ici. Nos données de simulation et nos résultats de simulation ici. C' est là que nous allons utiliser un orteil de table de données, faire tourner la roue 10 fois virtuellement et enregistrer les résultats de chaque tour ici dans la colonne C et j'ai déjà rempli certaines fonctions dans Colin F. Pour résumer. Ces résultats ont créé une plage nommée appelée Spins, et nous utilisons une partie de base pour calculer la perte totale de gain. Nous prenons un maximum de colonne B pour le nombre de tours en utilisant le comptage si pour calculer les vents et ainsi de suite et ainsi de suite. Alors allons de l'avant et commençons avec un petit nombre de simulations, seulement 10 tours. Donc, ce que nous allons faire ici, c'est sélectionner toute cette plage de B 15 à C 25 et rappelez-vous que lorsque nous avons utilisé des tables de données, une des cellules de la sélection doit contenir la formule que nous simulons, qui dans ce cas, est le résultat de spin logé et vendre je neuf. J' ai donc simplement référencé ce résultat ici en C 15 afin que nous puissions sélectionner la référence entière lorsque nous insérons notre table de données. Alors dirigez-vous vers votre onglet de données ici. Nous allons aller au et si les options d'analyse mettent la table de données en bas, et ici nous avons nos deux critères par ligne. Entrée dans notre entrée de colonne. Maintenant, nous n'avons pas de valeurs variables qui testaient dans les lignes, donc cela va évidemment être vide. Mais c'est là que les choses deviennent un peu bizarres. Nous avons des valeurs différentes ici dans notre colonne, donc nous avons besoin d'une sorte d'entrée de colonne. Mais ces valeurs ne se nourrissent en fait d'aucune entrée qui influe sur notre résultat. En d'autres termes, nous ne branchons pas le numéro un, deux ou trois dans une entrée de formule. Nous voulons juste randomiser cette formule un certain nombre de fois et faire en sorte qu'Excel se rende compte que tout ce que nous avons à faire est de référencer une cellule vide. Selby, 14 est complètement vide. Nous pouvons l'utiliser pour une colonne, entrer et appuyer sur OK, et voilà. Une fois que nous avons fait cela, Excel est maintenant itérer à travers cette table de données, vous pouvez voir qu'il a créé un seul tableau contenant tous les résultats, et il a enregistré un Siris de résultats, de victoires et de pertes dans ce cas basé sur 10 tours différents de la roue. Donc, dans ce premier exemple, nous parions un pari de 10$ sur le noir. On l'a filé 10 fois. On a fini par gagner 40$ parce qu'on a frappé le noir sept fois. On a frappé rouge ou vert, ce qui veut dire qu'on a perdu trois fois. Donc notre probabilité de victoire était de 70 % et notre rendement moyen par tour était de 4$. Alors allons de l'avant et essayons une autre simulation en calculant simplement maintenant. Ok, cette fois, on a perdu 20 alors que le pourcentage était de 40 % à nouveau. Maintenant, on a perdu 60 ans. On n'a gagné que deux fois sur 10 tours. Puis encore une fois perdu 60. Perdu, 40 cassés. Même ce temps a perdu 20. Donc, comme vous pouvez le voir, il y a un peu de variabilité en termes de probabilité de victoire. Maintenant, nous connaissons les statistiques réelles. Ici, nous connaissons les probabilités riales. Les chances de gagner un placement rouge ou noir à la roulette sont égales à 18 résultats gagnants sur 38 résultats possibles, ce qui équivaut à 47,4%. Et la raison pour laquelle cette probabilité de victoire que nous voyons ici et vendons F 19 est si sauvage et même pas vraiment cibler cette vraie probabilité est que nous avons affaire à une très, très petite taille d'échantillon. Tout peut arriver en 10 tours. Vous pourriez gagner 10 sur 10, vous pourriez perdre 10 sur 10. Donc, ce que j'aimerais faire ici, c'est répéter le processus non pas pour 10 tours, mais pour 10 000 tours et voir ce qui se passe maintenant. Gardez à l'esprit que vous ne pouvez pas simplement supprimer une cellule individuelle de votre table de données, qui est un tableau. Vous devez sélectionner le tout, commençant par C 16 va contrôler la flèche de décalage vers le bas, puis supprimer. Et je vais aussi supprimer les numéros d'index de spin ici et petit conseil pro ici. Je vais sélectionner le numéro un, passez le curseur sur le coin droit de la cellule, maintenez le clic droit sur ma souris pendant que j'ai glissé vers le bas et reculer. Et quand je sortirai, j'aurai ce menu secret ici qui a une option de Siri en bas. Et ce que je veux faire, c'est remplir un Siris dans cette colonne. Je veux entrer dans des entiers d'un et je veux m'arrêter à 10 000. Appuyez sur OK, et on y va. Si je contrôle notre bas, vous verrez qu'il a créé un Siri jusqu'à 10 000 tours maintenant le même processus qu' avant. Lorsque vous sélectionnez ce contrôle de plage, déplacez la flèche vers le bas pour saisir toute la plage de cellules Données . Que faire si l'analyse Table de données, Aucune colonne d'entrée de ligne. Entrée égale Selby 14 noir et appuyez sur. OK, et maintenant ça va réfléchir un peu parce que ça itère 10 000 fois et enregistre les résultats et voyons ce qui se passe. On y va. Donc on fait le même pari qu'avant, pariant 10$ sur Black. La seule différence, c'est qu'au lieu de faire ce pari 10 fois, on a fait ce pari 10 000 fois. C' était une très longue nuit au casino. Et le résultat que nous voyons ici est une perte totale de 4980$. Vous pouvez voir le répondant 10 000 fois. Nous avons gagné 4751 fois et jetez un oeil à cette probabilité de victoire. Rappelle-toi ce qu'on a dit. La probabilité réelle réelle pour ce pari est de 47,4%. Ici, nous voyons un résultat de 47,5, et si nous allons de l'avant et passer une fois de plus en frappant, calculez maintenant , rappelez-vous, cela va prendre quelques secondes, généralement 10 secondes environ pour réellement courir à travers la simulation et là vous l'avez. Donc 47,6 cette fois, nous avons perdu 4900$. Et faisons juste un calcul de plus. Donnez-lui quelques secondes pour courir. On y va. Notre troisième résultat, nous n'avons perdu que 3400 cette fois. Nous avions une probabilité de vent plus élevée que la normale, mais toujours très, très proche du vrai pourcentage ou des vraies chances de gagner. Donc, ce que je vais faire ici, c'est sélectionner toute la plage ici et supprimer cette table de données juste parce que je ne veux pas ralentir les choses ici et allons de l'avant et juste supprimer ces valeurs d'index jusqu'à 10 000 et nous sommes en quelque sorte de retour où nous avons commencé. Donc, à partir d'ici, n'hésitez pas à jouer autour. Essayez différents types de paris, essayez différents montants de pari, simulez différents nombres de tours. C' est tout à vous de choisir d'ici. Mais c'est votre cours de base. Lors de la création d'un modèle de simulation Monte Carlo dans Excel 12. Optimiser avec Solver: D' accord. Je suis tellement excité de partager ce prochain conseil professionnel avec vous. Il s'agit d'un conseil d'analyse de niveau cinq étoiles, vraiment expert. Nous allons parler d'optimisation des modèles complexes à l'aide d'un solveur. Maintenant, ce sont des solveurs conçus pour faire est de travailler avec des problèmes d'optimisation complexes du monde réel qui nécessitent plusieurs entrées ou variables de décision soumises à un ensemble donné de contraintes. Considérons maintenant une affaire comme celle-ci. Vous regardiez une matrice de transport qui nous indique le coût d'expédition des marchandises entre quatre usines différentes de Boston, New York, Chicago et Oakland, et quatre centres de distribution différents Miami, Dallas, Seattle et Baltimore. Et nous allons marcher à travers ça en profondeur quand nous sauterons dans Excel. Mais la ligne du fond ? L' objectif ici est de déterminer le nombre d'unités de produit à expédier de chaque usine à chaque centre de distribution afin de minimiser nos frais d'expédition totaux. Maintenant, ce n'est pas si simple, parce que dans le monde réel, nous avons aussi des contraintes et des limites. Premièrement, nous devons exécuter toutes les commandes demandées dans ce cas, 6000 commandes totales, et deux, et deux, nous ne pouvons pas dépasser le stock disponible dans chaque usine spécifique. Donc, pour un problème comme celui-ci. Nous ne pouvons vraiment pas utiliser des outils plus simples comme or Seek, qui nécessitait une seule variable d'entrée ou de décision et un seul résultat concret. Dans ce cas, nous devons utiliser le solveur car le solveur nous permet de minimiser, maximiser ou de cibler une valeur objective. Il nous permet de changer plusieurs cellules d'entrée ou variables de décision, et il nous permet également de déterminer des contraintes spécifiques. Donc, nous allons faire ici la tête de notre onglet de données et cliquer sur le bouton Solveur pour lancer la boîte de paramètres du Solveur. Maintenant, si vous ne voyez pas le solveur dans votre ville de données, gardez à l'esprit que la plupart des versions d'Excel semi-solveur en tant que plug-in intégré, mais par défaut il n'est généralement pas activé. Donc, vous devrez peut-être vous diriger vers les options de fichier Adan's, puis explorer votre Excel. Ajouter des ins pour brancher la fraise. Maintenant, regardant cette boîte de dialogue ici, il y a trois paramètres qui devront déterminer le numéro un est notre objectif. Dans ce cas, notre objectif vit dans la cellule E 23. Il s'agit de nos frais d'expédition, et notre objectif n'est pas de fixer ces frais d'expédition à une valeur spécifique. C' est pour minimiser le numéro deux. Nous déterminons exactement quelles cellules variables ou variables d'entrée modifier afin d'atteindre nos objectifs. Voici donc des variables de décision vivent dans les cellules D 16 30 19 Et enfin, mais non des moindres, nous devons optimiser sous réserve d'un ensemble spécifique de contraintes. Donc dans ce cas, nous ne pouvons pas expédier des fractions d'unités, donc nous sommes des unités. Besoin d'être des nombres entiers sont des entiers. Nous ne pouvons pas dépasser le stock disponible dans chaque usine, et nous avons besoin de chaque centre de distribution pour répondre à toute sa demande. Maintenant, une note très rapide sur les méthodes de résolution, que vous verrez juste sous cette fenêtre de contrainte. Il y a trois options différentes que vous pouvez choisir ici. Je ne vais pas entrer dans les détails. C' est bien en dehors du cadre de ce cours, mais la règle empirique que vous allez utiliser simplex LP pour toute optimisation linéaire simple est comme problème que nous traitons ici et g r g ou évolutif pour une optimisation plus complexe, non linéaire. La grande différence entre ces deux est que G R. G est beaucoup plus rapide, mais peut ne pas fournir la solution optimale globale. L' évolution est plus susceptible de fournir cette solution globale, mais prend beaucoup plus de temps à calculer. Maintenant que nous avons fixé notre objectif, nos variables de décision et nos contraintes peuvent aller de l'avant et cliquer sur le sel et le vérifier. Excel produira ou remplira toutes les valeurs de ces variables de décision afin de minimiser nos résultats. Frais d'expédition. Donc, nous voyons que nous expédions 225 unités de Boston à Miami, adorant 975 unités de New York à Dallas, 500 d'Oakland à Seattle. Et à la fin de la journée, nous répondons à la demande en disant que les 6000 unités ne dépassaient pas notre stock dans chaque usine , et notre coût total d'expédition arrive à un peu moins de 97 000$. Donc, comme vous pouvez le voir, cas d'utilisation commune incroyablement puissants comme nous le voyons ici déterminer des résultats optimaux soumis à des contraintes du monde réel, choses comme des stocks limités comme des planchers de prix comme des valeurs entières, etcetera afin que nous allons sauter dans un classeur de conseil professionnel et résoudre ce problème d'optimisation. Pas à pas. Très bien, donc si vous suivez, dirigez votre contenu de table écureuil jusqu'à vos conseils d'analyse, et nous allons vous connecter à notre démo de solveur ici et ici, vous verrez cette matrice de transport nous en avons parlé dans la diapositive. Mais avant de plonger, insérez dans votre onglet de données et cherchez ce bouton de solveur. C' est généralement tout le chemin sur la droite. Si vous ne le voyez pas, dû déposer les options Adan's Et puis à partir de cette liste déroulante, vous allez vouloir gérer votre Excel, ajouter des éléments et appuyer sur Go, et vous devriez voir le solveur Adan dans cette fenêtre ici. Fais juste une coche dans cette case. Appuyez sur OK, et vous devriez voir cela apparaître dans votre tentative de date. Maintenant, revenons à notre problème ici. Ce que nous avons nos frais de transport, le coût réel pour expédier une seule unité de nos usines à nos centres de distribution. C' est basé sur le kilométrage réel, n'est-ce pas ? Donc, l'expédition de Boston à Baltimore est assez bon marché, seulement environ 4$ par unité. Mais l'expédition de Boston à Seattle coûte beaucoup, beaucoup plus cher, plus de 30$ par unité. Maintenant, nous sommes également confrontés à deux contraintes différentes. Ici, nous avons des contraintes d'approvisionnement, qui est la limite au nombre d'unités que chaque usine peut fournir. Donc Boston n'a actuellement que 1000 unités en stock. New York City a 2000 Oakland de Chicago en a seulement 500, donc nous ne pouvons pas expédier plus d'unités qu' une usine donnée en stock. Donc, c'est la contrainte numéro un ou la contrainte d'approvisionnement. Nous avons également des contraintes de demande. Vous êtes répertorié ici dans Rhode 10 qui dit que OK, Miami Onley doit remplir 1250 commandes donc il n'est pas logique d'expédier plus d'unités à Miami. Et en fait, nous ne pouvons pas expédier moins d'unités à Miami parce que nous devons remplir ces commandes. Nous devons mettre ces produits entre les mains des clients qui les ont commandés. Donc, on doit ramener le 975 à Dallas. Il faut obtenir 3200 et 52 clients dans la région de Seattle et ainsi de suite et ainsi de suite. Voilà donc nos contraintes en matière de demande. Et en fin de compte, il y a 6000 unités que nous cherchons à expédier ici dans le cadre de ce modèle. Maintenant, si nous sautons sous la matrice des transports, nous avons notre objectif ou nos résultats. Donc, le but ici encore est de remplir ces valeurs ici dans D 16 à G 19. Il s'agit des variables d'entrée ou de décision qui seront modifiées afin d'évaluer ou calculer le coût total d'expédition. Alors regarde ce qui se passe si on branche quelques numéros. Disons que nous expédions 100 unités de Boston à Miami, 50 de New York à Dallas, 350 de Chicago à Seattle et 25 d'Oakland à Baltimore. Comme vous pouvez le voir, nous calculons le nombre d'unités expédiées par usine. On va s'assurer que ces chiffres ne dépassent pas ces contraintes en I 5 à 8. Nous calculons également le nombre d'unités reçues par chaque centre de distribution. Je veux m'assurer que ces chiffres ne dépassent pas ou n'entrent pas en dessous de ceux ici dans D 10 à G 10. Ceux-ci doivent être exactement égaux à la fin de la journée parce que nous devons exécuter ces commandes et enfin, mais non le moindre, notre cellule de frais d'expédition totaux. C' est l'objectif de la ligne de fond que nous nous soucions est une simple fonction de produit qui prend fondamentalement toutes les unités vendues ou expédiées de chaque centre de distribution d'usine, les multiplie par leurs coûts respectifs et résume ensuite le total à cracher sur ce coût total d'expédition. Dans ce cas, pour les 525 unités que nous venons de brancher est une démo qui coûterait un peu plus de 10 000$ à expédier. Donc, nous connaissons notre objectif. Nous connaissons nos variables de décision, et nous connaissons nos contraintes. Il est temps de résoudre ce problème. Allons de l'avant aux données, cliquez sur Solveur et ici vous verrez vos paramètres de solveur. Donc, les premières choses d'abord. Quel est notre objectif ? Eh bien, l'objectif est que les frais d'expédition sont des vies ici et vendent E 23. Et nous ne voulons pas définir ce coût d'expédition à une valeur particulière, bien que nous puissions le faire si nous le voulions. Et nous ne voulons certainement pas maximiser les coûts d'expédition. Ce serait tout simplement fou. Nous voulons minimiser nos frais d'expédition et nous allons le faire en changeant un certain ensemble de cellules variables ou variables de décision qui vivent ici dans Green de 16 30 19. On y va. Et la seule chose que nous devons faire maintenant est de définir nos contraintes et elles vont être des contraintes différentes. Allez-y et ajoutez le premier. Notre première contrainte, qui est simple, mais il est important d'ajouter ici, c'est que nous ne pouvons pas expédier des fractions de produits, est-ce pas. Nous devons expédier des unités entières ou des quantités. Donc, nous savons que nos variables de décision ici de 16 30 19 doivent être I anti, ce qui indique qu'il doit être un entier donc appuyez sur. OK, on y va. C' est la contrainte numéro un. Nous en ajouterons deux de plus. La première sera notre contrainte d'approvisionnement, ce qui signifie que nos unités expédiées, qui vivent ici dans I 16 19 unités par usine doivent être inférieures ou égales au nombre d' unités que chaque usine a disponibles. Et notez que je pourrais le faire en une seule étape en sélectionnant le type du tableau entier de quatre cellules. L' autre solution consisterait à transformer cette situation en quatre contraintes différentes. Vous savez où je 16 est inférieur ou égal à I cinq i 17 ce moins ou égal à I six. Mais nous pouvons le faire en une seule étape en sélectionnant les quatre cellules à la fois. Encore une fois, ça dit qu'on ne peut pas expédier plus d'unités qu'une usine n'a Chris OK ? Et puis notre troisième contrainte est notre contrainte de demande. Nous devons donc répondre à l'ensemble de la demande pour chaque centre de distribution. Donc les unités reçues par ces centres de distribution vivent ici dans D 21 3ème G 21. Et dans ce cas, on ne peut pas être fini ou en dessous. Nous devons être exactement égal au nombre de commandes demandées ou d'unités demandées pour chacun de ces endroits qui vivent ici et vend D 10 à G 10 Appuyez sur OK, et nous sommes prêts à partir. La méthode de résolution va être l'option simplex LP. Comme il s'agit d'un cas d'optimisation linéaire et vérifiez-le. Tout ce que nous devons faire maintenant est de cliquer résolu. Et presque instantanément, nous obtenons cette boîte de résultats qui dit solveur a trouvé une solution. Toutes les contraintes et les conditions optimales d'alité sont satisfaites et notez qu'il a branché les valeurs ici dans nos variables de décision et il crache que les frais d'expédition totaux minimums , qui dans ce cas est de 96 687$ et 75 cents. Alors allons de l'avant et gardons cette solution de solveur. Notez que nous pouvons enregistrer ces valeurs comme un scénario dans notre scénario, gestionnaire ou nous pouvons simplement appuyer sur OK et là vous l'avez. Nous avons optimisé ce défi logistique très complexe en déterminant exactement le nombre unités à expédier de quatre usines différentes à nos quatre centres de distribution différents. Et là, vous avez une démonstration rapide de la façon d'utiliser Excel Solver outil pour résoudre ces types de problèmes d'optimisation du monde réel plus complexes. 13. Analyse ToolPak (aperçus): Très bien, Cette prochaine astuce pro est un peu d'un teaser, une sorte d'aperçu de quelques façons différentes que vous pouvez explorer vos données en utilisant quelque chose appelé l'analyse Tool Pack. Donc, tout comme le solveur, le pack d'outils d'analyse est un plug-in Excel intégré qui est conçu pour prendre en charge une gamme d'outils et de méthodes plus avancés et complexes de science des données et de statistiques, choses comme l'analyse de variance et sur la régression de la variance de la co. Tests T sifflé un grammes et ainsi de suite. Maintenant, évidemment, je n'ai pas le temps de plonger dans chacune de ces méthodes spécifiques. À leur tour, ils méritent chacun un examen beaucoup plus approfondi, beaucoup plus complet. Mais ce que je vais vous montrer, c'est la portée des options disponibles dans ce pack d'outils, ainsi que quelques-unes que nous pouvons utiliser dès le départ et qui sont plus simples mais pas moins puissantes. Donc, dans cette démo, nous allons regarder un échantillon d'environ 5000 athlètes olympiques avec leur âge, leur taille et leur poids. Et ce que nous pouvons faire est d'explorer notre onglet de données et de rechercher le bouton d'analyse des données. Si vous ne voyez pas cela signifie probablement que vous avez juste besoin d'activer cela. Ajouter. Alors passez aux options de fichier. Adan et gérez l'orteil de votre Excel Adan. Activez cet outil sur votre ruban. Et une fois que vous cliquez sur l'analyse des données, vous verrez une liste défilante de toutes vos différentes options ici, dont certaines sont simples. Certains sont assez avancés. Maintenant, pour la démonstration, on va parler de deux options ici. D' abord, nous allons examiner une matrice de corrélation entre nos trois champs, âge, taille et poids. Et à partir de là, nous allons générer des statistiques descriptives pour calculer des choses comme le mode moyen. Variantes variées comptage biaisé hommes et valeurs Max et ainsi de suite sans avoir à taper une seule formule. Ce sera donc une excellente façon de vraiment décrire et comprendre nos données en un clic . Des cas d'utilisation très courants ici, tout comme je vous l'ai montré, générant ces statistiques descriptives sans avoir à utiliser de formules et à explorer ou analyser des données en utilisant ces données scientifiques ou des méthodes statistiques plus avancées comme l'analyse variantes, construction de modèles prédictifs, etcetera. Donc, nous allons sauter dans Excel et prendre ce pack d'outils d'analyse pour un essai routier. Très bien, alors allez-y et ouvrez votre classeur professionnel défiler dans la table des matières vers vos conseils d'analyse Purple et nous allons sauter dans la démo d'aperçu de l'outil d'analyse . Et quand nous nous joindrons, nous verrons à nouveau notre liste d'athlètes olympiques. On a un échantillon d'environ 5000 athlètes ici. Nous examinons les âges, les hauteurs et les centimètres et les poids en kilogrammes. Donc, ce serait un jeu de données agréable pour pratiquer certaines de ces options de pack d'outils d'analyse. Tout d'abord, dirigez-vous vers votre onglet de données sur la droite. C' est là que vous devriez voir vos branchements activés. Si vous ne voyez pas l'analyse de données, passez à la section Options de fichier. Vous allez gérer la presse de votre Excel Adan Go. Vous devriez voir Analysis Tool Pack dans cette liste. Allez-y et cochez la case et appuyez sur OK. Ça devrait apparaître ici et allons voir ce que nous avons à notre disposition. Alors que nous faisons défiler, nous verrons un certain nombre d'options différentes ici. Analyse de la variance, corrélation, variance co, lissage exponentiel, nombres aléatoires moyens mobiles , des tests T, des tests T et quelques autres. Donc évidemment une sorte de gamme de niveau de difficulté et de complexité ici. Quelques outils simples, certains qui sont assez avancés. Dans ce cas. Nous allons nous en tenir à quelques options de base que vous pouvez utiliser immédiatement, commençant par des statistiques descriptives. Alors allons de l'avant et appuyez sur OK. Ici, nous verrons une boîte de dialogue assez intuitive dit. Très bien, quelle est votre plage de valeurs d'entrée ? Eh bien, j'aimerais explorer ou analyser ces trois colonnes, alors sélectionnons B à D. Elles sont organisées en colonnes, donc c'est bon. Nous avons des en-têtes ou des étiquettes dans notre première ligne. Et où voulons-nous abandonner cette sortie ? le Mettonslecoin supérieur gauche ici et vendons le F 1. Et incluons aussi le cas le plus grand et le plus petit, une valeur, fondamentalement une façon fantaisie de dire le max et les hommes. Et je pense que ça devrait le faire. Nous n'avons pas besoin de nous soucier du niveau de confiance pour la moyenne. A ce 0.95% par défaut est très bien. Allons de l'avant et appuyez sur OK et regardez ça. Ça va tomber dans toutes ces statistiques sommaires ici dans les colonnes, F à K, littéralement en quelques secondes. Je pense à combien de temps cela aurait fallu pour produire toutes ces valeurs avec des formules auto . Cela signifie que les médianes, les variantes d' écart-type auraient pris un certain temps et ont pu simplement produire toutes ces valeurs, plus quelques autres en quelques secondes. Donc, en la regardant, Max et les hommes, on dirait que nous avons un athlète qui avait 65 ans, est le plus âgé. Notre plus jeune athlète n'avait que 12 ans en termes de taille. Le plus grand athlète, 216 centimètres. Et pour ceux d'entre vous qui ne sont pas sur le système métrique, ça fait environ 7 pieds un pouce. Et nous pouvons le confirmer en filtrant simplement nos données sources ici. Trier en descendant par hauteur. Et ici, nous voyons et habiller Glina da Kiss, qui est un joueur de basket-ball national grec, ce qui ne devrait pas venir comme une surprise étant donné sa taille et ensuite regarder poids ressemble à notre plus grand. En fait, c'était 160 kilogrammes. Notre plus petit n'était que 35 kilogrammes et la même histoire ici. Trions ces poids en montant, et vous pouvez voir que c'est comme Oh Soo Ihara, qui est un gymnaste japonais. Et encore une fois, pour ces gens non métriques, 35 kilogrammes, c'est un cheveu de plus de 77£. Donc ces air sont des statistiques descriptives vraiment, vraiment utiles, mais aussi assez simples à produire. Allons de l'avant et faisons un autre exemple ici, en revenant à nos options d'analyse de données. Il est temps que je veuille examiner les corrélations ou les relations entre ces trois colonnes. Donc même ranch d'entrée ici. Être 30. Ok, encore les colonnes d'air. Nous avons des en-têtes d'étiquettes dans la première rangée, et cette fois, laissons tomber notre sortie de corrélation sous leurs statistiques ici dans F 20. Et c'est simple, c'est bon ? Et on y va. Allons de l'avant et formons ces pour qu'il soit un peu plus lisible sous forme de pourcentages. Et regarde ça. Nous avons cette matrice de corrélation qui est produite ici. Vous verrez 100% type de sur la diagonale parce que chaque champ est 100% corrélé avec lui-même. Mais celles qui nous préoccupent vraiment ici sont ces deux valeurs ainsi que celle-ci ici. Je vais juste les rendre audacieux. Et fondamentalement, ce que cela nous dit, c'est qu'il n'y a pas beaucoup de corrélation ou de relation entre les âges et les hauteurs. seulement environ 11 p. 100 de corrélation, ce qui peut sembler un peu surprenant au début jusqu'à ce que vous réalisiez que nous ne regardons pas l'ensemble de la population. On ne regarde pas les hauteurs jusqu'à deux nouveau-nés ou nourrissons. Ce sont tous bien, pour la plupart, athlètes adultes. Donc, une fois que vous avez atteint l'âge adulte, vous ne grandissez plus vraiment. Il n'est donc pas surprenant que nous y voyons une corrélation relativement faible, et cette même histoire vaut pour l'âge et attendre pour les mêmes raisons. Maintenant, il est intéressant, c'est que nous voyons une corrélation très claire, très forte de 78% entre la taille et le poids, ce qui est également logique parce que athlètes plus grands et plus grands ont tendance à peser plus. Alors, voilà. C' est une façon très rapide d'utiliser des outils tels que la corrélation et les statistiques descriptives pour explorer et en apprendre davantage sur votre ensemble de données sans avoir à utiliser d'outils ou de formules de fantaisie. Et encore une fois, je ne vais pas plonger dans l'autre type d'outils plus complexes de ce pack d'outils d'analyse . Mais je vous encourage à explorer ces options et à essayer de jouer avec ces outils pour vous-même. Donc, nous l'avons là. Apprêt rapide, aperçu rapide du pack d'outils d'analyse