Ved hjelp av Excels What-If Data Table for å generere en haug med tilfeldige resultater. Selv om du har en vanskelig formel som er resultatet av flere trinn, vil datatabellen tillate deg å generere hundrevis av svar på modellen uten å gjenta modellen 100 ganger.
Se på video
- Målet er å lage eksempeldata med produkt; produkt; produkt; produkt
- Målet er å alltid ha 2 eller flere produkter, opp til maks 12
- Lagre en liste over produkter i en tilpasset liste, slik at du enkelt kan generere en kolonne med enkeltprodukter
- Bruk av RANDBETWEEN () kan returnere dupliserte elementer i listen
- Bruk RAND () -funksjonen til å bestemme om dette produktet er inkludert eller ikke
- Bruk TEXTJOIN () for å sammenkoble ikke-blanke med halvkolon i mellom
- Nå som du har ett resultat, hvordan oppnår du mange resultater
- Overraskende at en kopi og flere limverdier limer inn det nåværende resultatet av formelen
- Fremskynde limverdiene ved å bruke F4 til Re do
- Men - en superrask måte: Bruk What-If-verktøy og en datatabell med en tom celle som kolonneinntakscelle
- Takk til professor Simon Benninga for denne metoden
Videoutskrift
Lær Excel fra, episode 2155: Generer flere tilfeldige resultater fra en formel.
Hei. Velkommen tilbake til netcast. Jeg er Bill Jelen. I tillegg til podcasten og skrivebøkene skriver jeg en månedlig artikkel for Strategic Finance magazine. Jeg jobbet med neste måneds artikkel der jeg viste dem hvordan man bruker strømforespørsel for å dele en kolonne med; separerte data til rader og jeg trengte å generere falske data for det, og hvorfor jeg ikke åpnet filen fra episode 2097, aner jeg ikke. Jeg ville bare lage falske data. Så, hvor som helst fra 2 til 12 produkter i en celle, og i ferd med å gjøre det, brukte jeg mange triks fra podcasten - text join; fyll APPLE, BANANA, CHERRY; Tilfeldig tur; F4 To Redo - og jeg tror underveis jeg oppdaget noen interessante ting om hvordan jeg kan øke hastigheten på denne prosessen.
For det første hadde det vært flott om jeg bare kunne ha opprettet en enorm massiv matriseformel som ville ha generert disse dataene. Jeg kunne ha kopiert den formelen ned, men jeg kunne ikke nå kopien av CTRL + SKIFT + ENTER, og jeg lette bare etter noe enklere den morgenen. Jeg er en stor fan av RANDBETWEEN. Jeg bruker RANDBETWEEN hele tiden. Så hvis vi hadde en liste over 12 produkter og deretter genererte en serie svar ved hjelp av RANDBETWEEN, så indeksen til A1 til A12, og ba om et tilfeldig tall fra 1 til 12, vel hver gang jeg trykker på F9, får jeg en annen liste over produkter, og så vil jeg ha et annet antall produkter i hver enkelt, så her, RANDBETWEEN på 2 til 7 eller 2 til 12 eller hva den øvre eller nedre grensen er, og deretter bruke TAXJOIN, den fantastiske nye funksjonen i Office 365, atskilt med; ignorere blanke, og så 'Går du fra E2 helt ned til hvor som helst i E2 til E12 - basert på den sjette verdien i dette tilfellet - vil generere den listen, ok? Men grunnen til at jeg ikke liker dette er at jeg fortsatte å trykke på F9, skjønner, jeg begynner å få gjentakelser, og du vet, angivelig er dette ordrer fra et nettsted og hvorfor skulle noen bestille ELDEBERRY? ELDERBERRY gir bare ikke mening, ikke sant? Så jeg likte ikke sjansen for å få DATE DATE. Jeg ønsket å ha en unik liste. Så her bestemte jeg meg for at jeg skulle gjøre.angivelig er dette bestillinger som kommer fra et nettsted, og hvorfor skulle noen bestille ELDEBERRY? ELDERBERRY gir bare ikke mening, ikke sant? Så jeg likte ikke sjansen for å få DATE DATE. Jeg ønsket å ha en unik liste. Så her bestemte jeg meg for at jeg skulle gjøre.angivelig er dette bestillinger som kommer fra et nettsted, og hvorfor skulle noen bestille ELDEBERRY? ELDERBERRY gir bare ikke mening, ikke sant? Så jeg likte ikke sjansen for å få DATE DATE. Jeg ønsket å ha en unik liste. Så her bestemte jeg meg for at jeg skulle gjøre.
Først hadde jeg tenkt å lage en liste over de 12 produktene, og jeg har lagret denne som en egendefinert liste, så jeg kan bare lage en fin alfabetisk liste over varer, og så ville jeg ha alt fra 2, vet du, til omtrent 7 av disse, og så hva jeg gjorde her er jeg sier = HVIS RAND. RAND er en fantastisk funksjon som genererer en desimal fra 0 til 1 er <.6. Så, med andre ord, i omtrent 60% av tilfellene, vil jeg at du skal bringe det produktet hit til kolonne B, ellers gi meg ingenting "". Jeg vil kopiere det ned. Det som kommer til å gjøre for meg er å lage en liste over produkter. Det blir aldri noen gjentakelser. Det er ingen sjanse for gjentakelser, og hver gang jeg trykker på F9, får jeg en annen liste over produkter, og ja, det ser ut til at vi får riktig antall produkter hver gang. (= HVIS (RAND () <0,6, A1, “”))
Deretter, Chart Title; de tilbyr oss to forskjellige steder for et diagram - Over Chart a Alright. Så nå som vi har det, er den nye funksjonen i Excel i Office 365 TEXTJOIN. Jeg elsker dette. Avgrenseren kommer til å være en; og så ignorere tom. Det spiller ingen rolle. Jeg faktisk … ja, nei, det betyr ikke noe her. Det er det viktige. Vi kommer til å ignorere tomt. SANT, og så er listen vår over slike produkter. Greit. Så der har vi produktlisten vår for første rad, men jeg må generere en hel haug med disse, og det er her vi faktisk kommer til problemet, problemet jeg prøvde å løse i dette tilfellet. (= TEXTJOIN (“;”, SANT, B1: B12))
Nå, hvis jeg bare ville kopiere den formelen ned, ok, hvis jeg tok den originale formelen og kom hit og redigerte denne - velg:, trykk F4 for å sikre at jeg har en absolutt referanse, og kopier den ned - du Jeg ser at jeg ender opp med identiske gjenstander helt ned. Det er ikke veldig interessante falske data, ikke sant? Så, det kommer ikke til å fungere. Det jeg trenger å gjøre er at jeg må ta resultatet av denne formelen og lage en hel haug med dem, ok? (= TEXTJOIN (“;”, SANT, $ B $ 1: $ B $ 12))
Så først gjorde jeg dette. Jeg gjorde CONTROL + C, og så kommer jeg hit, og jeg skal lime inn spesielle verdier - eller jeg antar at det bare er lim inn - og SOMMER VERDIER slik, ok, og det som er fascinerende for meg - og jeg har snakket om dette en gang på podcasten før, og alle i YouTube-kommentarene var, selvfølgelig, det kommer til å fungere; nei - det som er fascinerende for meg er at jeg kopierte celle C14, og så skulle du tro at når jeg kopierte C14, ville teksten fra C14 ha blitt kopiert til utklippstavlen, men det er det ikke. Det peker på C14, ikke sant? Så første gang jeg limer inn, fikk jeg KERSE, DATO, ELDERBERRY, men du ser nå at C14, de marsjerende maurene, har endret seg til Eple, Kirsebær, fig, og så kommer jeg til å komme ned hit og jeg ' Jeg går til PASTE VALUES igjen, og jeg er alltid sjokkert over at den endret seg til den nye verdien.
Alright, so, if I just could just PASTE VALUES, PASTE VALUES, PASTE VALUES, PASTE VALUES, it would generate a new answer every single time. This time when I PASTE VALUES, APPLE, BANANA, DATE, FIG, ICEBERG, JACKFRUIT, but, look, it's a hassle to grab the mouse and come up here and choose PASTE and choose VALUES. So, I'm going to use the awesome REDO function -- not UNDO, REDO -- which is F4, so F4, paste the new value. When I press F4, I’ll get but BANANA, DATE, ELDERBERRY, LIME. So, it's just simple. F4, DOWN ARROW, F4, DOWN ARROW, F4, DOWN ARROW, alright, and life is great. There, I have enough fake data for the article, alright, but even that is a hassle, alright?
So, the method that I learned from a good friend of mine who's now deceased -- professor Simon Benninga taught me this -- if we have a model -- and this is essentially a model -- that is using RAND or RANDBETWEEN and generating a result, what you can do is you can have multiple versions of that result happen, alright, and we have to start from the cell to the left of the result of our model, choose that cell and the cell that contains your formula, and then, however many you want -- let's say I needed a 100 of these or 132 of these -- just copy or select all the way down, and we're going to come here to the DATA tab, the DATA tab, WHAT-IF ANALYSIS, DATA TABLE, alright?
Now, I use this all the time to show how to create multiple scenarios but, in this case, we don't really have anything for ROW INPUT CELL. For the COLUMN INPUT CELL, just choose any empty cell -- it doesn't matter what cell it is -- and this is going to run this model 132 times, each time essentially pressing F9 to generate new random values like that, click OK, and, bam, and it works. I absolutely love that.
Now, this is live. Every time I press F9, I get a new set of 132 of these. So, just copy -- CONTROL+C -- and come out here, PASTE SPECIAL VALUES, and we have our fake list of products, and we're ready to essentially do what was back in episode 2097: split a column of x;x;x; to rows. Highly recommend you check out that video, it's a great video, or the November issue, November 2017 issue, of Strategic Finance magazine. It'll be out online in early November.
Alright. So, all of these methods are in the book Power Excel With, 2017 edition. Click that i on the top right-hand corner to check it out.
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.
Vel hei. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2155.xlsm
