De What-If-gegevenstabel van Excel gebruiken om een aantal willekeurige resultaten te genereren. Zelfs als je een lastige formule hebt die het resultaat is van verschillende stappen, kun je met de gegevenstabel honderden antwoorden op het model genereren zonder het model honderden keren te herhalen.
Bekijk video
- Het doel is om voorbeeldgegevens te creëren met product; product; product; product
- Het doel is om altijd 2 of meer producten te hebben, tot een maximum van 12
- Sla een lijst met producten op in een aangepaste lijst, zodat u gemakkelijk een kolom met afzonderlijke producten kunt genereren
- Het gebruik van RANDBETWEEN () kan dubbele items in de lijst opleveren
- Gebruik de functie RAND () om te beslissen of dit product al dan niet is inbegrepen
- Gebruik TEXTJOIN () om de niet-spaties samen te voegen met puntkomma's ertussen
- Nu je één resultaat hebt, hoe kun je dan veel resultaten behalen
- Verrassend dat één Kopieer en meerdere Plakwaarden het huidige resultaat van de formule zullen plakken
- Versnel de plakwaarden door F4 te gebruiken om opnieuw te doen
- Maar - een supersnelle manier: gebruik What-If-tools en een gegevenstabel met een lege cel als kolominvoercel
- Met dank aan professor Simon Benninga voor deze methode
Videotranscriptie
Leer Excel van, aflevering 2155: genereer meerdere willekeurige resultaten van één formule.
Hallo. Welkom terug bij de netcast. Ik ben Bill Jelen. Nou, naast de podcast en het schrijven van boeken schrijf ik maandelijks een artikel voor het tijdschrift Strategic Finance. Ik werkte aan het artikel van volgende maand waarin ik hen liet zien hoe ze powerquery kunnen gebruiken om een kolom te splitsen van; gescheiden gegevens in rijen en ik moest daarvoor wat nepgegevens genereren, en waarom ik het bestand uit aflevering 2097 niet heb geopend, heb ik geen idee. Ik wilde gewoon nepgegevens maken. Dus ergens tussen de 2 en 12 producten in één cel, en terwijl ik dat deed, gebruikte ik veel trucs van de podcast - text join; vul APPEL, BANAAN, KERS; Willekeurige wandeling; F4 To Redo - en ik denk dat ik gaandeweg enkele interessante dingen ontdekte om dit proces te versnellen.
Ten eerste zou het geweldig zijn geweest als ik zojuist een enorme matrixformule had kunnen maken die die gegevens zou hebben gegenereerd. Ik had die formule kunnen kopiëren, maar ik kon mijn exemplaar van CTRL + SHIFT + ENTER niet bereiken en ik was die ochtend gewoon op zoek naar iets eenvoudiger. Ik ben een grote fan van RANDBETWEEN. Ik gebruik de hele tijd RANDBETWEEN. Dus, als we een lijst van 12 producten hadden en dan, hier, een reeks antwoorden genereerden met RANDBETWEEN, dus de index van A1 tot A12, die om een willekeurig getal van 1 tot 12 vraagt, nou elke keer dat ik op F9 druk, krijg ik een andere lijst met producten, en dan wil ik in elk een ander aantal producten, dus hier, RANDBETWEEN van 2 tot 7 of 2 tot 12 of wat de boven- of ondergrens ook is, en dan met TAXJOIN, die geweldige nieuwe functie in Office 365, gescheiden door;, negeren de lege plekken, en dan 'als je van E2 naar beneden gaat naar ergens in E2 naar E12 - in dit geval gebaseerd op de 6e waarde - wordt die lijst gegenereerd, oké? Maar de reden dat ik dit niet leuk vind, is dat ik op F9 bleef drukken. Zie je, ik krijg herhalingen en, weet je, dit zijn naar verluidt bestellingen die van een website komen en waarom zou iemand ELDEBERRY bestellen? ELDERBERRY klopt gewoon niet, toch? Dus ik hield niet van de kans om DATE DATE te krijgen. Ik wilde een unieke lijst hebben. Dus dit is wat ik besloot dat ik ging doen.naar verluidt zijn dit bestellingen die van een website komen en waarom zou iemand ELDEBERRY bestellen? ELDERBERRY klopt gewoon niet, toch? Dus ik hield niet van de kans om DATE DATE te krijgen. Ik wilde een unieke lijst hebben. Dus dit is wat ik besloot dat ik ging doen.naar verluidt zijn dit bestellingen die van een website komen en waarom zou iemand ELDEBERRY bestellen? ELDERBERRY klopt gewoon niet, toch? Dus ik hield niet van de kans om DATE DATE te krijgen. Ik wilde een unieke lijst hebben. Dus dit is wat ik besloot dat ik ging doen.
Ten eerste ging ik een lijst maken van de 12 producten en ik heb dit onthouden als een aangepaste lijst, dus ik kan gewoon een mooie alfabetische lijst met items genereren, en toen wilde ik ergens tussen 2, weet je, tot ongeveer 7 hiervan, en dus wat ik hier deed, is dat ik zeg = ALS de RAND. RAND is een geweldige functie die een decimaal genereert van 0 tot 1 is <.6. Dus, met andere woorden, in ongeveer 60% van de gevallen wil ik dat u dat product hier naar kolom B brengt, anders geeft u mij niets "". Ik zal dat kopiëren. Wat dat voor mij gaat doen, is een lijst met producten genereren. Er zullen nooit herhalingen zijn. Er is geen kans op herhalingen, en elke keer dat ik op F9 druk, krijg ik een andere lijst met producten, en ja, het lijkt erop dat we elke keer het juiste aantal producten krijgen. (= IF (RAND () <0,6, A1, ""))
Vervolgens Grafiektitel; ze bieden ons twee verschillende plaatsen voor een kaart. Boven kaart a in orde. Dus nu we dat hebben, is de nieuwe functie in Excel in Office 365 TEXTJOIN. Ik hou hiervan. Het scheidingsteken wordt een; en negeer dan leeg. Het maakt niet uit. Ik eigenlijk … ja, nee, het maakt hier echt niet uit. Dat is het belangrijkste. We gaan leeg negeren. WAAR, en dan is hier onze lijst met dat soort producten. Oke. Dus daar hebben we onze productlijst voor de eerste rij, maar ik moet er een hele reeks van genereren, en hier komen we eigenlijk bij het probleem, het probleem dat ik in dit specifieke geval probeerde op te lossen. (= TEXTJOIN (";", TRUE, B1: B12))
Nu, als ik die formule gewoon zou kopiëren, oké, als ik die originele formule zou nemen en hierheen kwam en dit had bewerkt - kies de:, druk op F4 om er zeker van te zijn dat ik een absolute referentie heb, en kopieer het naar beneden - jij Ik zal zien dat ik helemaal naar beneden met identieke items kom. Het zijn niet erg interessante nepgegevens, toch? Dus dat gaat niet werken. Wat ik moet doen, is dat ik het resultaat van deze formule moet nemen en er een hele reeks van moet maken, oké? (= TEXTJOIN (";", TRUE, $ B $ 1: $ B $ 12))
Dus in eerste instantie deed ik dit. Ik deed CONTROL + C, en dan kom ik hier en ga ik speciale waarden plakken - of ik denk dat dat gewoon plakken is - en PLAK WAARDEN zoals dat, oké, en wat is fascinerend voor mij - en ik heb ik heb hier al eens eerder over gesproken op de podcast en iedereen in de YouTube-reacties was, nou ja, dat gaat natuurlijk werken; nee - wat mij fascineert is dat ik cel C14 heb gekopieerd, en dus zou je denken dat, toen ik C14 kopieerde, die tekst van C14 naar het klembord gekopieerd zou zijn, maar dat is het niet. Het wijst naar C14, toch? Dus de eerste keer dat ik plak, kreeg ik CHERRY, DATE, ELDERBERRY, maar je ziet nu dat C14, de marcherende mieren, is veranderd in APPLE, CHERRY, FIG, en dus ga ik hierheen en ik ' Ik ga weer WAARDEN PLAKKEN, en ik ben altijd geschokt dat het is veranderd in de nieuwe waarde.
Oké, dus als ik gewoon WAARDEN, WAARDEN, WAARDEN PASTE, WAARDEN PASTE, zou het elke keer een nieuw antwoord genereren. Dit keer wanneer ik WAARDEN, APPEL, BANAAN, DATUM, VIJG, IJSBERG, JACKFRUIT PLAK, maar kijk, het is een gedoe om de muis te pakken en hier te komen en PASTE te kiezen en WAARDEN te kiezen. Dus ik ga de geweldige REDO-functie gebruiken - niet UNDO, REDO - dat is F4, dus F4, plak de nieuwe waarde. Als ik op F4 druk, krijg ik maar BANAAN, DATUM, Vlierbes, KALK. Het is dus heel simpel. F4, PIJL OMLAAG, F4, PIJL OMLAAG, F4, PIJL OMLAAG, oké, en het leven is geweldig. Daar heb ik genoeg nepgegevens voor het artikel, oké, maar zelfs dat is een gedoe, oké?
Dus de methode die ik heb geleerd van een goede vriend van mij die inmiddels overleden is - professor Simon Benninga heeft me dit geleerd - als we een model hebben - en dit is in wezen een model - dat is het gebruik van RAND of RANDBETWEEN en het genereren van een resultaat, wat je kunt doen is dat je meerdere versies van dat resultaat kunt laten gebeuren, oké, en we moeten beginnen bij de cel links van het resultaat van ons model, die cel kiezen en de cel die je formule bevat, en dan , hoeveel je er ook wilt - laten we zeggen dat ik er 100 of 132 nodig had - kopieer of selecteer helemaal naar beneden, en we komen hier naar het tabblad DATA, het tabblad DATA, WAT-ALS ANALYSE, GEGEVENSTABEL, oké?
Nu gebruik ik dit de hele tijd om te laten zien hoe je meerdere scenario's kunt maken, maar in dit geval hebben we niet echt iets voor ROW INPUT CELL. Kies voor de KOLOMINVOERCEL een willekeurige lege cel - het maakt niet uit welke cel het is - en dit zal dit model 132 keer uitvoeren, elke keer dat je in wezen op F9 drukt om op die manier nieuwe willekeurige waarden te genereren, klik op OK , en, bam, en het werkt. Ik hou daar absoluut van.
Dit is nu live. Elke keer dat ik op F9 druk, krijg ik een nieuwe set van 132 hiervan. Dus kopieer gewoon - CONTROL + C - en kom hier, PLAK SPECIALE WAARDEN, en we hebben onze nep-lijst met producten, en we zijn klaar om in wezen te doen wat er terug was in aflevering 2097: splits een kolom met x; x; x; naar rijen. Beveel je ten zeerste aan om die video te bekijken, het is een geweldige video, of het novembernummer, november 2017 nummer, van het tijdschrift Strategic Finance. Het zal begin november online verschijnen.
Oké. Al deze methoden staan dus in het boek Power Excel With, editie 2017. Klik op die i in de rechterbovenhoek om het te bekijken.
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.
Hallo daar. Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2155.xlsm
