Utilisation de la table de données What-If d'Excel pour générer un tas de résultats aléatoires. Même si vous avez une formule délicate qui est le résultat de plusieurs étapes, la table de données vous permettra de générer des centaines de réponses au modèle sans répéter le modèle des centaines de fois.
Regarder la vidéo
- L'objectif est de créer des exemples de données avec le produit; produit; produit; produit
- L'objectif est de toujours avoir 2 produits ou plus, jusqu'à un maximum de 12
- Stockez une liste de produits dans une liste personnalisée afin de pouvoir facilement générer une colonne de produits uniques
- L'utilisation de RANDBETWEEN () peut renvoyer des éléments en double dans la liste
- Utilisez la fonction RAND () pour décider si ce produit est inclus ou non
- Utilisez TEXTJOIN () pour concaténer les non-blancs avec des points-virgules entre
- Maintenant que vous avez un résultat, comment faire plusieurs résultats
- Étonnant qu'une copie et plusieurs valeurs de collage collent le résultat actuel de la formule
- Accélérez les valeurs de collage en utilisant F4 pour refaire
- Mais - un moyen ultra rapide: utilisez les outils de simulation et un tableau de données avec une cellule vide comme cellule d'entrée de colonne
- Merci au professeur Simon Benninga pour cette méthode
Transcription vidéo
Learn Excel From, Episode 2155: Générer plusieurs résultats aléatoires à partir d'une formule.
Hey. Bienvenue à nouveau sur le netcast. Je suis Bill Jelen. Eh bien, en plus du podcast et des livres d'écriture, j'écris un article mensuel pour le magazine Strategic Finance. Je travaillais sur l'article du mois prochain où je leur montrais comment utiliser Power Query pour diviser une colonne de; séparer les données en lignes et j'avais besoin de générer de fausses données pour cela, et pourquoi je n'ai pas ouvert le fichier de l'épisode 2097, je n'en ai aucune idée. Je voulais juste créer de fausses données. Donc, de 2 à 12 produits dans une cellule, et, pour ce faire, j'ai utilisé beaucoup d'astuces du podcast - jointure de texte; remplir la pomme, la banane et la cerise; Random Walk; F4 To Redo - et je pense qu'en cours de route, j'ai découvert des choses intéressantes sur la façon d'accélérer ce processus.
Maintenant, tout d'abord, cela aurait été génial si je pouvais simplement créer une énorme formule de tableau massif qui aurait généré ces données. J'aurais pu copier cette formule vers le bas mais je ne pouvais pas atteindre ma copie de CTRL + SHIFT + ENTRÉE et je cherchais juste quelque chose de plus simple ce matin-là. Je suis un grand fan de RANDBETWEEN. J'utilise RANDBETWEEN tout le temps. Donc, si nous avions une liste de 12 produits et ensuite, ici, générions une série de réponses en utilisant RANDBETWEEN, donc l'indice de A1 à A12, demandant un nombre aléatoire de 1 à 12, eh bien à chaque fois que j'appuie sur F9, j'obtiens une liste différente de produits, et puis je veux un nombre différent de produits dans chacun, alors ici, RANDBETWEEN de 2 à 7 ou 2 à 12 ou quelle que soit la limite supérieure ou inférieure, puis en utilisant TAXJOIN, cette nouvelle fonction impressionnante dans Office 365, séparé par;, ignorez les espaces vides, puis nous 'Le fait de passer de E2 à n'importe quel endroit de E2 à E12 - basé sur la 6ème valeur dans ce cas - va générer cette liste, d'accord? Mais la raison pour laquelle je n'aime pas cela est que j'ai continué d'appuyer sur F9, voyez, je commence à avoir des répétitions et, vous savez, il s'agit prétendument de commandes provenant d'un site Web et pourquoi quelqu'un commanderait ELDEBERRY? ELDERBERRY n'a tout simplement pas de sens, non? Donc, je n'ai pas aimé la chance d'obtenir le DATE DATE. Je voulais avoir une liste unique. Alors, voici ce que j'ai décidé que j'allais faire.Il s'agit prétendument de commandes provenant d'un site Web et pourquoi quelqu'un commanderait ELDEBERRY? ELDERBERRY n'a tout simplement pas de sens, non? Donc, je n'ai pas aimé la chance d'obtenir le DATE DATE. Je voulais avoir une liste unique. Alors, voici ce que j'ai décidé que j'allais faire.Il s'agit prétendument de commandes provenant d'un site Web et pourquoi quelqu'un commanderait ELDEBERRY? ELDERBERRY n'a tout simplement pas de sens, non? Donc, je n'ai pas aimé la chance d'obtenir le DATE DATE. Je voulais avoir une liste unique. Alors, voici ce que j'ai décidé que j'allais faire.
Tout d'abord, j'allais créer une liste des 12 produits et je l'ai mémorisée sous forme de liste personnalisée, afin que je puisse simplement générer une belle liste alphabétique d'articles, puis je voulais n'importe où de 2, vous savez, à environ 7 de ceux-ci, et donc ce que j'ai fait ici, c'est que je dis = SI le RAND. RAND est une fonction géniale qui génère une décimale de 0 à 1 est <0,6. Donc, en d'autres termes, dans environ 60% des cas, je veux que vous apportiez ce produit ici dans la colonne B, sinon ne me donnez rien «». Je vais copier cela. Ce que cela va faire pour moi, c'est générer une liste de produits. Il n'y aura jamais de répétition. Il n'y a aucune chance de se répéter, et chaque fois que j'appuie sur F9, j'obtiens une liste de produits différente, et, oui, il semble que nous obtenons, vous savez, le bon nombre de produits à chaque fois. (= SI (RAND () <0,6, A1, «»))
Ensuite, le titre du graphique; ils nous offrent deux emplacements différents pour un graphique - Au-dessus du graphique un Très bien. Donc, maintenant que nous l'avons, la nouvelle fonction d'Excel dans Office 365 est TEXTJOIN. J'aime cela. Le délimiteur va être un; puis ignorez vide. Cela n'a pas d'importance. En fait, je… ouais, non, ça n'a vraiment pas d'importance ici. C'est la chose importante. Nous allons ignorer le vide. VRAI, et voici notre liste de produits comme ça. Bien. Nous avons donc notre liste de produits pour la première ligne, mais je dois en générer tout un ensemble, et c'est là que nous arrivons réellement au problème, le problème que j'essayais de résoudre dans ce cas particulier. (= TEXTJOIN (";", TRUE, B1: B12))
Maintenant, si je voulais simplement copier cette formule, d'accord, si je prenais cette formule originale et venais ici et modifiais ceci - choisissez le:, appuyez sur F4 pour m'assurer que j'ai une référence absolue, et copiez-la - vous Je vais voir que je me retrouve avec des éléments identiques tout en bas. Ce ne sont pas de fausses données très intéressantes, non? Donc, cela ne fonctionnera pas. Ce que je dois faire, c'est que je dois prendre le résultat de cette formule et en créer tout un tas, d'accord? (= TEXTJOIN («;», TRUE, $ B $ 1: $ B $ 12))
Donc, au départ, je l'ai fait. J'ai fait CONTROL + C, puis je viens ici et je vais coller des valeurs spéciales - ou je suppose que c'est juste coller - et COLLER DES VALEURS comme ça, d'accord, et ce qui me fascine - et j'ai en a parlé une fois sur le podcast avant et tout le monde dans les commentaires YouTube était, bien sûr, cela va fonctionner; non - ce qui me fascine, c'est que j'ai copié la cellule C14, et on pourrait donc penser que, lorsque j'ai copié C14, ce texte de C14 aurait été copié dans le presse-papiers, mais ce n'est pas le cas. Il pointe vers C14, non? Donc, la première fois que je colle, j'ai CHERRY, DATE, ELDERBERRY, mais vous voyez maintenant que C14, les fourmis en marche, a changé en APPLE, CHERRY, FIG, et donc je vais descendre ici et je ' Je vais à nouveau COLLER VALEURS, et je suis toujours choqué que cela soit passé à la nouvelle valeur.
Très bien, donc, si je pouvais simplement COLLER DES VALEURS, COLLER DES VALEURS, COLLER DES VALEURS, COLLER DES VALEURS, cela générerait une nouvelle réponse à chaque fois. Cette fois, quand JE COLLE DES VALEURS, APPLE, BANANE, DATE, FIG, ICEBERG, JACKFRUIT, mais, regardez, c'est un problème de saisir la souris et de venir ici et de choisir COLLER et de choisir les VALEURS. Donc, je vais utiliser l'impressionnante fonction REDO - pas UNDO, REDO - qui est F4, donc F4, collez la nouvelle valeur. Quand j'appuie sur F4, j'obtiens mais BANANE, DATE, ELDERBERRY, LIME. Donc, c'est tout simplement simple. F4, FLÈCHE BAS, F4, FLÈCHE BAS, F4, FLÈCHE BAS, d'accord, et la vie est belle. Là, j'ai assez de fausses données pour l'article, d'accord, mais même cela est un problème, d'accord?
Donc, la méthode que j'ai apprise d'un bon ami à moi qui est maintenant décédé - le professeur Simon Benninga m'a appris cela - si nous avons un modèle - et c'est essentiellement un modèle - qui utilise RAND ou RANDBETWEEN et génère un résultat, ce que vous pouvez faire, c'est que plusieurs versions de ce résultat se produisent, d'accord, et nous devons commencer par la cellule à gauche du résultat de notre modèle, choisir cette cellule et la cellule qui contient votre formule, puis , quel que soit le nombre que vous voulez - disons que j'en avais besoin de 100 ou 132 d'entre eux - copiez ou sélectionnez tout en bas, et nous allons arriver ici à l'onglet DATA, l'onglet DATA, WHAT-IF ANALYSE, TABLEAU DE DONNÉES, d'accord?
Maintenant, je l'utilise tout le temps pour montrer comment créer plusieurs scénarios mais, dans ce cas, nous n'avons vraiment rien pour ROW INPUT CELL. Pour la CELLULE D'ENTREE DE COLONNE, choisissez simplement n'importe quelle cellule vide - peu importe de quelle cellule il s'agit - et cela va exécuter ce modèle 132 fois, chaque fois en appuyant essentiellement sur F9 pour générer de nouvelles valeurs aléatoires comme ça, cliquez sur OK , et, bam, et ça marche. J'adore ça.
Maintenant, c'est en direct. Chaque fois que j'appuie sur F9, j'obtiens un nouvel ensemble de 132 d'entre eux. Alors, copiez simplement - CONTROL + C - et sortez ici, COLLEZ DES VALEURS SPÉCIALES, et nous avons notre fausse liste de produits, et nous sommes prêts à faire essentiellement ce qui était de retour dans l'épisode 2097: diviser une colonne de x; x; x; aux lignes. Je vous recommande vivement de regarder cette vidéo, c'est une excellente vidéo, ou le numéro de novembre, numéro de novembre 2017, du magazine Strategic Finance. Il sera disponible en ligne début novembre.
Bien. Donc, toutes ces méthodes sont dans le livre Power Excel With, édition 2017. Cliquez sur ce i dans le coin supérieur droit pour le vérifier.
Recap of what we talked about today: the goal is to create a sample data set with ; separated products; always two or more products up to a max of 12; so I stored a list of products in a custom list so you can easily generate a single column of products; using RANDBETWEEN, that would have worked but we have duplicates in the list so I'm using the RAND function to say is this product in or not; then the TEXTJOIN function to concatenate the non-blanks with ;s in between; but then the big question, now that you have one result, how do you make multiple results using the RAND function; well, that's surprising that one copy and then multiple paste values will give you different answers because it's always recalculating after each paste; and you could speed that using F4 to redo the paste values. Not a bad way to go, but the superfast way, thanks to Professor Benninga, use the WHAT-IF tools and a data table with an empty cell as the COLUMN INPUT CELLS and you'll be able to generate hundreds of random results very, very quickly. Alright, there you go.
Et bien Salut. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2155.xlsm
