Brug af Excels What-If-datatabel til at generere en masse tilfældige resultater. Selvom du har en vanskelig formel, der er resultatet af flere trin, giver datatabellen dig mulighed for at generere hundredvis af svar på modellen uden at gentage modellen 100 gange.
Se video
- Målet er at oprette eksempeldata med produkt; produkt; produkt; produkt
- Målet er altid at have 2 eller flere produkter, op til maksimalt 12
- Gem en liste over produkter på en tilpasset liste, så du nemt kan generere en kolonne med enkeltprodukter
- Brug af RANDBETWEEN () returnerer muligvis duplikatelementer på listen
- Brug RAND () -funktionen til at afgøre, om dette produkt er inkluderet eller ikke
- Brug TEXTJOIN () til at sammenkæde de ikke-tomme emner med semikolon imellem
- Nu hvor du har et resultat, hvordan opnår du mange resultater
- Overraskende, at en kopi og flere indsæt værdier indsætter det aktuelle resultat af formlen
- Fremskynde indsætningsværdierne ved at bruge F4 til Re do
- Men - en super hurtig måde: Brug What-If-værktøjer og en datatabel med en tom celle som kolonneinputcelle
- Tak til professor Simon Benninga for denne metode
Videoudskrift
Lær Excel fra, afsnit 2155: Generer flere tilfældige resultater fra en formel.
Hej. Velkommen tilbage til netcast. Jeg er Bill Jelen. Derudover skriver jeg en månedlig artikel til magasinet Strategic Finance til podcast og bøger. Jeg arbejdede på næste måneds artikel, hvor jeg viste dem, hvordan man bruger strømforespørgsel til at opdele en kolonne med; adskilt data til rækker, og jeg havde brug for at generere falske data til det, og hvorfor jeg ikke åbnede filen fra episode 2097, aner jeg ikke. Jeg ville bare oprette falske data. Så hvor som helst fra 2 til 12 produkter i en celle, og i færd med at gøre det brugte jeg mange tricks fra podcasten - text join; fyld APPLE, BANANA, CHERRY; Tilfældig gåtur; F4 To Redo - og jeg tror, at jeg undervejs opdagede nogle interessante ting om, hvordan man fremskynder denne proces.
Først og fremmest ville det have været dejligt, hvis jeg bare kunne have oprettet en enorm massiv matrixformel, der ville have genereret disse data. Jeg kunne have kopieret den formel ned, men jeg kunne ikke nå min kopi af CTRL + SKIFT + ENTER, og jeg ledte bare efter noget enklere den morgen. Jeg er en stor fan af RANDBETWEEN. Jeg bruger RANDBETWEEN hele tiden. Så hvis vi havde en liste over 12 produkter og derefter genererede en række svar ved hjælp af RANDBETWEEN, så indekset fra A1 til A12, der beder om et tilfældigt tal fra 1 til 12, ja hver gang jeg trykker på F9, får jeg en anden liste over produkter, og så vil jeg have et andet antal produkter i hver enkelt, så her, RANDBETWEEN på 2 til 7 eller 2 til 12 eller hvad end den øvre eller nedre grænse er, og derefter bruge TAXJOIN, den fantastiske nye funktion i Office 365, adskilt af; ignorer de tomme felter, og så 'går vi fra E2 lige derned og hvor som helst i E2 til E12 - baseret på den 6. værdi i dette tilfælde - vil generere denne liste, okay? Men grunden til at jeg ikke kan lide dette er, at jeg fortsatte med at trykke på F9, se, jeg begynder at få gentagelser, og du ved, angiveligt er dette ordrer fra et websted, og hvorfor skulle nogen bestille ELDEBERRY? ELDERBERRY giver bare ikke mening, ikke? Så jeg kunne ikke lide chancen der for at få DATE DATE. Jeg ville have en unik liste. Så her er hvad jeg besluttede at jeg skulle gøre.angiveligt er dette ordrer fra et websted, og hvorfor skulle nogen bestille ELDEBERRY? ELDERBERRY giver bare ikke mening, ikke? Så jeg kunne ikke lide chancen der for at få DATE DATE. Jeg ville have en unik liste. Så her er hvad jeg besluttede at jeg skulle gøre.angiveligt er dette ordrer fra et websted, og hvorfor skulle nogen bestille ELDEBERRY? ELDERBERRY giver bare ikke mening, ikke? Så jeg kunne ikke lide chancen der for at få DATE DATE. Jeg ville have en unik liste. Så her er hvad jeg besluttede at jeg skulle gøre.
Først og fremmest skulle jeg oprette en liste over de 12 produkter, og jeg har dette husket som en brugerdefineret liste, så jeg kan bare generere en flot alfabetisk liste over varer, og så ville jeg have alt fra 2, du ved, til omkring 7 af disse, og så hvad jeg gjorde her er, siger jeg = HVIS RANDEN. RAND er en fantastisk funktion, der genererer en decimal fra 0 til 1 er <.6. Så med andre ord, i ca. 60% af tilfældene, vil jeg have dig til at bringe det produkt herover til kolonne B, ellers giver mig intet “”. Jeg vil kopiere det ned. Hvad det vil gøre for mig er at generere en liste over produkter. Der bliver aldrig gentaget. Der er ingen chance for gentagelser, og hver gang jeg trykker på F9, får jeg en anden liste over produkter, og ja, det ser ud til, at vi får det rigtige antal produkter hver gang. (= HVIS (RAND () <0,6, A1, “”))
Dernæst diagramtitel; de tilbyder os to forskellige steder til et diagram - over diagrammet en okay. Så nu hvor vi har det, er den nye funktion i Excel i Office 365 TEXTJOIN. Jeg elsker det her. Afgrænseren bliver en; og derefter ignorere tom. Det betyder ikke noget. Jeg faktisk … ja, nej, det betyder virkelig ikke noget her. Det er den vigtige ting. Vi ignorerer tomt. SAND, og så er her vores liste over sådanne produkter. Okay. Så der har vi vores produktliste til første række, men jeg skal generere en hel masse af disse, og det er her, vi faktisk kommer til problemet, det problem, jeg prøvede at løse i denne særlige sag. (= TEXTJOIN (“;”, SAND, B1: B12))
Hvis jeg bare ville kopiere formlen ned, okay, hvis jeg tog den originale formel og kom her og redigerede denne - vælg:, tryk på F4 for at sikre, at jeg har en absolut reference, og kopier den ned - du Jeg ser, at jeg ender med identiske genstande helt ned. Det er ikke meget interessante falske data, ikke? Så det fungerer ikke. Hvad jeg skal gøre er, at jeg skal tage resultatet af denne formel og oprette en hel flok af dem, okay? (= TEXTJOIN (“;”, SAND, $ B $ 1: $ B $ 12))
Så oprindeligt gjorde jeg dette. Jeg gjorde CONTROL + C, og så kommer jeg her, og jeg vil indsætte specielle værdier - eller jeg antager, at det bare er lim - og indsætte værdier sådan, okay, og hvad der er fascinerende for mig - og jeg har talte om dette en gang på podcasten før, og alle i YouTube-kommentarerne var, selvfølgelig, det kommer til at fungere; nej - hvad der er fascinerende for mig er, at jeg kopierede celle C14, og så skulle man tro, at når jeg kopierede C14, ville teksten fra C14 være kopieret til udklipsholderen, men det er det ikke. Det peger på C14, ikke? Så første gang jeg indsætter, fik jeg kirsebær, dato, ældre, men du ser nu, at C14, de marcherende myrer, er blevet ændret til æble, kirsebær, fig, og så kommer jeg ned her og jeg ' Jeg går til PASTE VALUES igen, og jeg er altid chokeret over, at det ændrede sig til den nye værdi.
Okay, så hvis jeg bare kunne indsætte værdier, indsætte værdier, indsætte værdier, indsætte værdier, ville det generere et nyt svar hver eneste gang. Denne gang, når jeg indsætter værdier, æble, banan, dato, fig, isberg, jackfruct, men se, det er besværet at få fat i musen og komme herop og vælge pasta og vælge værdier. Så jeg vil bruge den fantastiske REDO-funktion - ikke UNDO, REDO - som er F4, så F4, indsæt den nye værdi. Når jeg trykker på F4, får jeg kun BANANA, DATE, ELDERBERRY, LIME. Så det er bare simpelt. F4, PIL NED, F4, PIL NED, F4, PIL NED, okay, og livet er fantastisk. Der har jeg nok falske data til artiklen, okay, men selv det er besvær, okay?
Så metoden, som jeg lærte af en god ven af mig, der nu er død - professor Simon Benninga lærte mig dette - hvis vi har en model - og dette er i det væsentlige en model - der bruger RAND eller RANDBETWEEN og genererer en resultat, hvad du kan gøre er, at du kan få flere versioner af dette resultat til at ske, okay, og vi er nødt til at starte fra cellen til venstre for resultatet af vores model, vælge den celle og den celle, der indeholder din formel, og derefter uanset hvor mange du vil - lad os sige, at jeg havde brug for 100 af disse eller 132 af disse - bare kopier eller vælg helt ned, og vi kommer her til fanen DATA, DATA-fanen, HVAD-HVIS ANALYSE, DATATABEL, okay?
Nu bruger jeg dette hele tiden til at vise, hvordan jeg opretter flere scenarier, men i dette tilfælde har vi ikke rigtig noget til ROW INPUT CELL. For COLUMN INPUT CELL skal du bare vælge en hvilken som helst tom celle - det betyder ikke noget, hvilken celle det er - og dette vil køre denne model 132 gange, hver gang du i det væsentlige trykker på F9 for at generere nye tilfældige værdier som det, skal du klikke på OK , og, bam, og det fungerer. Det elsker jeg absolut.
Nu er dette live. Hver gang jeg trykker på F9, får jeg et nyt sæt på 132 af disse. Så bare kopier - CONTROL + C - og kom ud her, SÆT SPECIALE VÆRDIER, så har vi vores falske liste over produkter, og vi er klar til i det væsentlige at gøre det, der var tilbage i episode 2097: opdele en kolonne med x; x; x; til rækker. Stærkt anbefale, at du tjekker den video, det er en fantastisk video eller novemberudgaven, november 2017-udgaven, af magasinet Strategic Finance. Det kommer ud online i begyndelsen af november.
I orden. Så alle disse metoder findes i bogen Power Excel With, 2017-udgaven. Klik på det i øverste højre hjørne for at tjekke det ud.
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.
Nå, hej. Jeg vil gerne takke dig, fordi du kom forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2155.xlsm
