Använda Excel: s What-If-datatabell för att generera en massa slumpmässiga resultat. Även om du har en knepig formel som är resultatet av flera steg kommer datatabellen att du kan generera hundratals svar på modellen utan att upprepa modellen 100 gånger.
Kolla på video
- Målet är att skapa provdata med produkt; produkt; produkt; produkt
- Målet är att alltid ha två eller fler produkter, upp till max 12
- Lagra en produktlista i en anpassad lista så att du enkelt kan skapa en kolumn med enskilda produkter
- Användning av RANDBETWEEN () kan returnera dubbletter i listan
- Använd RAND () -funktionen för att avgöra om den här produkten ingår eller inte
- Använd TEXTJOIN () för att sammanfoga de icke tomma ämnena med halvkolon däremellan
- Nu när du har ett resultat, hur gör du många resultat
- Överraskande att en kopia och flera klistra in värden klistrar det aktuella resultatet av formeln
- Snabba klistra in värdena genom att använda F4 till Re do
- Men - ett supersnabbt sätt: Använd What-If-verktyg och en datatabell med en tom cell som kolumninmatningscell
- Tack till professor Simon Benninga för denna metod
Videoutskrift
Lär dig Excel från, avsnitt 2155: Skapa flera slumpmässiga resultat från en formel.
Hallå. Välkommen tillbaka till netcast. Jag heter Bill Jelen. Tja, förutom podcasten och skriva böcker skriver jag en månadsartikel för tidningen Strategic Finance. Jag arbetade med nästa månads artikel där jag visade dem hur man använder kraftfråga för att dela upp en kolumn med; separerade data till rader och jag behövde generera falska data för det, och varför jag inte öppnade filen från avsnitt 2097, har jag ingen aning om. Jag ville bara skapa falska data. Så var som helst från 2 till 12 produkter i en cell, och när jag gjorde det använde jag många knep från podcasten - text join; fyll APPLE, BANANA, CHERRY; En spontan promenad; F4 Att göra om - och jag tror att jag på vägen upptäckte några intressanta saker om hur man kan påskynda denna process.
För det första skulle det ha varit fantastiskt om jag bara kunde ha skapat en enorm massiv matrisformel som skulle ha genererat den informationen. Jag kunde ha kopierat den formeln men jag kunde inte nå min kopia av CTRL + SKIFT + ENTER och jag letade bara efter något enklare den morgonen. Jag är ett stort fan av RANDBETWEEN. Jag använder RANDBETWEEN hela tiden. Så, om vi hade en lista med 12 produkter och sedan genererade en serie svar med RANDBETWEEN, så indexet A1 till A12, som ber om ett slumpmässigt tal från 1 till 12, ja varje gång jag trycker på F9 får jag en annan produktlista, och då vill jag ha ett annat antal produkter i var och en, så här, RANDBETWEEN av 2 till 7 eller 2 till 12 eller vad som helst den övre eller nedre gränsen är, och sedan använda TAXJOIN, den fantastiska nya funktionen i Office 365, åtskilda av;, ignorera tomrummen, och sedan 'kommer du från E2 dit ner till var som helst i E2 till E12 - baserat på det sjätte värdet i det här fallet - kommer att generera listan, okej? Men anledningen till att jag inte gillar det här är att jag fortsatte att trycka på F9, se, jag börjar upprepa och, du vet, det är enligt uppgift beställningar som kommer från en webbplats och varför skulle någon beställa ELDEBERRY? ÄLDREBERRY är bara inte meningsfullt, eller hur? Så jag tyckte inte om chansen att få DATE DATE. Jag ville ha en unik lista. Så här är vad jag bestämde mig för att jag skulle göra.påstås att detta är beställningar som kommer från en webbplats och varför skulle någon beställa ELDEBERRY? ÄLDREBERRY är bara inte meningsfullt, eller hur? Så jag tyckte inte om chansen att få DATE DATE. Jag ville ha en unik lista. Så här är vad jag bestämde mig för att jag skulle göra.påstås att detta är beställningar som kommer från en webbplats och varför skulle någon beställa ELDEBERRY? ELDBERRY är bara inte meningsfullt, eller hur? Så jag tyckte inte om chansen att få DATE DATE. Jag ville ha en unik lista. Så här är vad jag bestämde mig för att jag skulle göra.
Först och främst skulle jag skapa en lista över de 12 produkterna och jag har lagrat det här som en anpassad lista, så jag kan bara skapa en fin alfabetisk lista med artiklar, och sedan ville jag ha allt från 2, du vet, till cirka 7 av dessa, och så vad jag gjorde här är jag säger = OM RAND. RAND är en fantastisk funktion som genererar ett decimal från 0 till 1 är <.6. Så, med andra ord, i ungefär 60% av fallen vill jag att du tar den produkten hit till kolumn B, annars ger mig ingenting ””. Jag kommer att kopiera ner det. Det som kommer att göra för mig är att skapa en lista med produkter. Det kommer aldrig att upprepas. Det finns ingen chans för några upprepningar, och varje gång jag trycker på F9 får jag en annan produktlista, och ja, det ser ut som om vi får rätt antal produkter varje gång. (= OM (RAND () <0,6, A1, “”))
Därefter, Chart Title; de erbjuder oss två olika platser för ett diagram - Ovanför diagram en okej. Så nu när vi har det är den nya funktionen i Excel i Office 365 TEXTJOIN. Jag älskar det här. Avgränsaren kommer att bli en; och ignorera sedan tom. Det spelar ingen roll. Jag faktiskt … ja, nej, det spelar ingen roll här. Det är det viktiga. Vi kommer att ignorera tomma. SANT, och här är vår lista över sådana produkter. Okej. Så där har vi vår produktlista för första raden, men jag måste generera en hel massa av dessa, och det är här vi faktiskt kommer till frågan, den fråga som jag försökte lösa i just detta fall. (= TEXTJOIN (“;”, SANT, B1: B12))
Om jag bara skulle kopiera den formeln ner, okej, om jag tog den ursprungliga formeln och kom hit och redigerade den här - välj:, tryck på F4 för att se till att jag har en absolut referens och kopiera den ner - du Jag ser att jag slutar med identiska föremål hela vägen ner. Det är inte särskilt intressanta falska data, eller hur? Så det fungerar inte. Vad jag behöver göra är att jag måste ta resultatet av denna formel och skapa en hel massa av dem, okej? (= TEXTJOIN (“;”, SANT, $ B $ 1: $ B $ 12))
Så ursprungligen gjorde jag det här. Jag gjorde CONTROL + C, och sedan kommer jag hit och ska klistra in speciella värden - eller jag antar att det bara klistrar in - och klistra in värden så, okej, och vad som är fascinerande för mig - och jag har pratade om detta en gång på podcasten tidigare och alla i YouTube-kommentarerna var, naturligtvis, det kommer att fungera; nej - det som är fascinerande för mig är att jag kopierade cell C14, och så skulle man tro att när jag kopierade C14 skulle den texten från C14 ha kopierats till Urklipp, men det är det inte. Det pekar på C14, eller hur? Så första gången jag klistrar in fick jag KÖRSEL, DATUM, ÄLDERBÄR, men du ser nu att C14, de marscherande myrorna, har ändrats till ÄPPEL, KÖRS, FIG, och så ska jag komma ner hit och jag ' Jag går till PASTE VALUES igen, och jag är alltid chockad över att det ändrats till det nya värdet.
Okej, så om jag bara kunde klistra in värden, klistra in värden, klistra in värden, klistra in värden, skulle det generera ett nytt svar varje gång. Den här gången när jag klistrar in värden, äpple, banan, datum, fig, isberg, jackfrukt, men det är svårt att ta tag i musen och komma upp hit och välja klistra in och välj värden. Så jag ska använda den fantastiska REDO-funktionen - inte UNDO, REDO - som är F4, så F4, klistra in det nya värdet. När jag trycker på F4 får jag bara BANANA, DATE, ELDERBERRY, LIME. Så det är bara enkelt. F4, NER PIL, F4, NER PIL, F4, NER PIL, okej, och livet är fantastiskt. Där har jag tillräckligt med falska data för artikeln, okej, men även det är ett besvär, okej?
Så, metoden som jag lärde mig av en god vän till mig som nu är avliden - professor Simon Benninga lärde mig detta - om vi har en modell - och detta är i huvudsak en modell - som använder RAND eller RANDBETWEEN och genererar en resultat, vad du kan göra är att du kan få flera versioner av det resultatet att hända, okej, och vi måste börja från cellen till vänster om resultatet av vår modell, välja den cellen och cellen som innehåller din formel och sedan hur många du än vill - låt oss säga att jag behövde 100 av dessa eller 132 av dessa - bara kopiera eller välj hela vägen ner så kommer vi hit till DATA-fliken, DATA-fliken, VAD-OM ANALYS, DATATABELL, okej?
Nu använder jag detta hela tiden för att visa hur man skapar flera scenarier, men i det här fallet har vi inte riktigt något för ROW INPUT CELL. För COLUMN INPUT CELL, välj bara vilken tom cell som helst - det spelar ingen roll vilken cell det är - och detta kommer att köra den här modellen 132 gånger, varje gång du trycker på F9 för att generera nya slumpmässiga värden som det, klicka OK , och, bam, och det fungerar. Jag älskar absolut det.
Nu är detta live. Varje gång jag trycker på F9 får jag en ny uppsättning av 132 av dessa. Så, kopiera bara - CONTROL + C - och kom ut hit, KListra in SPECIALVÄRDEN, så har vi vår falska lista med produkter, och vi är redo att i huvudsak göra det som var tillbaka i avsnitt 2097: dela upp en kolumn med x; x; x; till rader. Rekommenderar starkt att du tittar på den videon, det är en fantastisk video, eller novembernumret, november 2017, av tidningen Strategic Finance. Det kommer ut online i början av november.
OK. Så, alla dessa metoder finns i boken Power Excel With, 2017-upplagan. Klicka på det i det övre högra hörnet för att kolla in det.
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.
Men Hej. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2155.xlsm
