Izmantojot Excel's What-If Data Table, lai izveidotu virkni nejaušu rezultātu. Pat ja jums ir sarežģīta formula, kas ir vairāku darbību rezultāts, datu tabula ļaus ģenerēt simtiem atbilžu uz modeli, neatkārtojot modeli 100 reizes.
Skatīties video
- Mērķis ir izveidot datu paraugus ar produktu; produkts; produkts; produkts
- Mērķis vienmēr ir 2 vai vairāk produkti, bet ne vairāk kā 12
- Saglabājiet produktu sarakstu pielāgotā sarakstā, lai jūs varētu viegli izveidot atsevišķu produktu kolonnu
- Izmantojot RANDBETWEEN (), sarakstā var tikt parādīti vienumu dublikāti
- Izmantojiet funkciju RAND (), lai izlemtu, vai šis produkts ir iekļauts
- Izmantojiet TEXTJOIN (), lai starp tukšajām daļām sasietu tukšās kolas
- Tagad, kad jums ir viens rezultāts, kā panākt daudz rezultātu
- Pārsteidzoši, ka viena kopija un vairākas ielīmēšanas vērtības ielīmē pašreizējo formulas rezultātu
- Paātriniet ielīmēšanas vērtības, izmantojot Re, lai veiktu F4
- Bet - ļoti ātrs veids: kā kolonnas ievades šūnu izmantojiet rīku “Kas būtu, ja būtu” un datu tabulu ar tukšu šūnu
- Paldies profesoram Saimonam Benningam par šo metodi
Video atšifrējums
Uzziniet programmu Excel, sērija 2155: ģenerējiet vairākus nejaušus rezultātus no vienas formulas.
Hei. Laipni lūdzam atpakaļ netcast. Es esmu Bils Jelens. Nu, papildus podcast un grāmatu rakstīšanai es rakstu ikmēneša rakstu žurnālam Strategic Finance. Es strādāju pie nākamā mēneša raksta, kur es viņiem parādīju, kā izmantot enerģijas vaicājumu, lai sadalītu kolonnu; atdalīja datus rindās, un man tam vajadzēja ģenerēt dažus viltotus datus, un kāpēc es neatvēru failu no 2097. epizodes, man nav ne jausmas. Es tikai gribēju izveidot dažus viltotus datus. Tātad, kaut kur no 2 līdz 12 produktiem vienā šūnā, un, to darot, es izmantoju daudz triku no aplādes - teksta pievienošana; pildiet ĀBOLU, BANĀNU, ĶIRŠU; Nejauša pastaiga; F4 To Redo - un es domāju, ka pa ceļam es atklāju dažas interesantas lietas, kā paātrināt šo procesu.
Tagad, pirmkārt, būtu bijis lieliski, ja es tikko būtu varējis izveidot vienu milzīgu masīvu formulu, kas būtu ģenerējis šos datus. Es būtu varējis nokopēt šo formulu uz leju, bet es nevarēju sasniegt savu CTRL + SHIFT + ENTER kopiju, un es tajā rītā vienkārši meklēju kaut ko vienkāršāku. Es esmu liels RANDBETWEEN fans. Es visu laiku izmantoju RANDBETWEEN. Tātad, ja mums būtu 12 produktu saraksts un pēc tam šeit izveidotu atbilžu sēriju, izmantojot RANDBETWEEN, tāpēc indekss A1 līdz A12, pieprasot nejaušu skaitli no 1 līdz 12, labi katru reizi, kad es nospiežu F9, es saņemu atšķirīgs produktu saraksts, un tad es katrā no tiem vēlos atšķirīgu produktu skaitu, tāpēc šeit RANDBETWEEN no 2 līdz 7 vai 2 līdz 12 vai kāda ir augšējā vai apakšējā robeža, un pēc tam, izmantojot TAXJOIN, šī lieliskā jaunā funkcija Office 365, atdalot ar;, ignorē tukšās vietas, un tad mēsJa jūs ejat no E2 turpat uz leju līdz jebkurai vietai E2 uz E12 - pamatojoties uz 6. vērtību šajā gadījumā, tas ģenerēs šo sarakstu, labi? Bet iemesls, kāpēc man tas nepatīk, ir tas, ka es turpināju nospiest F9, redziet, man sāk parādīties atkārtojumi, un, jūs zināt, iespējams, ka tie ir pasūtījumi, kas nāk no vietnes, un kāpēc kāds pasūta ELDEBERRY? ELDERBERRY vienkārši nav jēgas, vai ne? Tātad, man nepatika iespēja saņemt DATE DATE. Es gribēju, lai man būtu unikāls saraksts. Tātad, lūk, ko es nolēmu darīt.domājams, ka tie ir pasūtījumi, kas nāk no vietnes, un kāpēc kāds pasūta ELDEBERRY? ELDERBERRY vienkārši nav jēgas, vai ne? Tāpēc man nepatika iespēja saņemt DATE DATE. Es gribēju, lai man būtu unikāls saraksts. Tātad, lūk, ko es nolēmu darīt.domājams, ka tie ir pasūtījumi, kas nāk no vietnes, un kāpēc kāds pasūta ELDEBERRY? ELDERBERRY vienkārši nav jēgas, vai ne? Tātad, man nepatika iespēja saņemt DATE DATE. Es gribēju, lai man būtu unikāls saraksts. Tātad, lūk, ko es nolēmu darīt.
Pirmkārt, es gatavojos izveidot 12 produktu sarakstu, un tas ir iegaumēts kā pielāgots saraksts, tāpēc es varu vienkārši izveidot jauku alfabētisku priekšmetu sarakstu, un tad es gribēju jebkur no 2, ziniet, līdz apmēram 7 no tiem, un tāpēc es šeit darīju, ka es saku = JA RAND. RAND ir lieliska funkcija, kas ģenerē decimālu no 0 līdz 1 ir <, 6. Citiem vārdiem sakot, apmēram 60% gadījumu es vēlos, lai jūs šo produktu nogādātu šeit uz B sleju, pretējā gadījumā nedodiet man neko “”. Es to nokopēšu uz leju. Tas, kas man darīs, ir ģenerēt produktu sarakstu. Nekad nebūs atkārtojumu. Nav iespēju atkārtoties, un katru reizi, kad nospiežu F9, es saņemu atšķirīgu produktu sarakstu, un, jā, izskatās, ka mēs katru reizi saņemam pareizo produktu skaitu. (= JA (RAND () <0,6, A1, “”))
Nākamais, diagrammas nosaukums; viņi mums piedāvā divas dažādas diagrammas vietas - virs diagrammas labi. Tātad, tagad, kad tas mums ir pieejams, jaunā funkcija Excel programmā Office 365 ir TEXTJOIN. ES mīlu šo. Atdalītājs būs a; un pēc tam ignorēt tukšu. Tas nav svarīgi. Es patiesībā … jā, nē, šeit tas tiešām nav svarīgi. Tā ir svarīga lieta. Mēs ignorēsim tukšu. PATIESA, un tad šeit ir mūsu līdzīgo produktu saraksts. Labi. Tātad, mums ir mūsu produktu saraksts pirmajai rindai, bet man tas ir jāveido vesels kaudze, un šeit mēs faktiski nonākam pie jautājuma, jautājuma, kuru es mēģināju atrisināt šajā konkrētajā gadījumā. (= TEXTJOIN (“;”, TRUE, B1: B12))
Tagad, ja es tikai kopētu šo formulu uz leju, labi, ja es paņēmu šo sākotnējo formulu un atnācu šeit un rediģēju to - izvēlieties:, nospiediet F4, lai pārliecinātos, ka man ir absolūta atsauce, un nokopēju to uz leju - jūs Es redzēšu, ka man līdz galam ir vienādi priekšmeti. Tas nav ļoti interesanti viltoti dati, vai ne? Tātad, tas nedarbosies. Kas man jādara, man ir jāņem šīs formulas rezultāts un jāizveido vesels ķekars, labi? (= TEXTJOIN (“;”, TRUE, $ B $ 1: $ B $ 12))
Tātad, sākotnēji es to izdarīju. Es izdarīju CONTROL + C, un pēc tam es šeit ierados un gatavojos ielīmēt īpašas vērtības - vai arī es domāju, ka tas ir tikai ielīmēt - un tādas PASTAS VĒRTĪBAS, labi, un tas, kas man aizrauj - un es esmu vienreiz par to vienreiz runājām podkāstā, un visi YouTube komentāros bija, protams, protams, ka tas darbosies; nē - tas, kas man ir aizraujoši, ir tas, ka es nokopēju šūnu C14, un tāpēc jūs domājat, ka, kopējot C14, šis teksts no C14 būtu nokopēts starpliktuvē, bet tas tā nav. Tas norāda uz C14, vai ne? Tātad, pirmo reizi ielīmējot, es saņēmu ĶIRŠI, DATUMU, ELDERBERRY, bet jūs tagad redzat, ka C14, soļojošās skudras, ir nomainījušās uz APPLE, CHERRY, FIG, un tāpēc es nokāpšu šeit un es Es atkal eju uz PASTES VĒRTĪBĀM, un es vienmēr esmu šokēts, ka tas mainījās uz jauno vērtību.
Labi, tāpēc, ja es vienkārši spētu vienkārši PASTAS VĒRTĪBAS, PASTAS VĒRTĪBAS, PASTAS VĒRTĪBAS, PASTAS VĒRTĪBAS, tas katru reizi radītu jaunu atbildi. Šoreiz, kad es PASTE VALUES, APPLE, BANANA, DATE, FIG, ICEBERG, JACKFRUIT, bet, skatieties, ir grūti paķert peli un nākt šeit, izvēlēties PASTE un izvēlēties VĒRTĪBAS. Tātad, es izmantošu lielisko REDO funkciju - nevis UNDO, REDO - kas ir F4, tāpēc F4, ielīmējiet jauno vērtību. Nospiežot F4, es saņemšu tikai BANANA, DATE, ELDERBERRY, LIME. Tātad, tas ir vienkārši. F4, bultiņa uz leju, F4, bultiņa uz leju, F4, bultiņa uz leju, labi, un dzīve ir lieliska. Tur man ir pietiekami daudz viltotu datu rakstam, labi, bet pat tas ir apgrūtinājums, labi?
Tātad metode, ko iemācījos no sava laba drauga, kurš tagad ir miris - profesors Saimons Benninga man to iemācīja - ja mums ir modelis - un tas būtībā ir modelis -, izmantojot RAND vai RANDBETWEEN un ģenerējot Rezultāts, ko jūs varat darīt, ir tas, ka jums var notikt vairākas šī rezultāta versijas, labi, un mums jāsāk no šūnas pa kreisi no mūsu modeļa rezultāta, jāizvēlas tā šūna un šūna, kurā ir jūsu formula, un pēc tam , lai cik daudz jūs vēlētos - pieņemsim, ka man vajadzēja 100 no šiem vai 132 no tiem - vienkārši nokopējiet vai atlasiet līdz galam, un mēs ejam šeit uz cilni DATA, cilni DATA, WHAT-IF ANALĪZE, DATU TABULA, labi?
Tagad es to visu laiku izmantoju, lai parādītu, kā izveidot vairākus scenārijus, taču šajā gadījumā mums RK INPUT CELL nav nekā. Slejā COLUMN INPUT CELL vienkārši izvēlieties jebkuru tukšu šūnu - nav svarīgi, kāda tā ir šūna - un tas šo modeli darbinās 132 reizes, katru reizi būtībā nospiežot F9, lai ģenerētu tādas jaunas nejaušas vērtības, noklikšķiniet uz Labi , un, bam, un tas darbojas. Es to absolūti mīlu.
Tagad tas ir tiešraidē. Katru reizi, kad nospiežu F9, es saņemu jaunu 132 komplektu no šiem. Tātad, vienkārši nokopējiet - CONTROL + C - un iznāciet šeit, IELIETOJIET ĪPAŠĀS VĒRTĪBAS, un mums ir mūsu viltus produktu saraksts, un mēs esam gatavi būtībā darīt to, kas bija atpakaļ 2097. epizodē: sadalīt x kolonnu; x; x; uz rindām. Ļoti iesakām apskatīt šo videoklipu, tas ir lielisks video vai žurnāla Strategic Finance novembra numurs, 2017. gada novembra numurs. Tas būs pieejams tiešsaistē novembra sākumā.
Labi. Tātad visas šīs metodes ir grāmatā Power Excel With, 2017. gada izdevums. Noklikšķiniet uz i augšējā labajā stūrī, lai to pārbaudītu.
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.
Nu, hei. Es gribu pateikties, ka apstājāties. Tiksimies nākamreiz, lai skatītu citu netcast raidījumu no.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2155.xlsm
