Menggunakan Jadual Data What-If Excel untuk menghasilkan banyak hasil rawak. Walaupun anda mempunyai formula rumit yang merupakan hasil dari beberapa langkah, Jadual Data akan membolehkan anda menghasilkan beratus-ratus jawapan untuk model tanpa mengulangi model 100 kali.
Tonton Video
- Tujuannya adalah untuk membuat data sampel dengan produk; produk; produk; produk
- Matlamatnya adalah untuk selalu mempunyai 2 atau lebih produk, sehingga maksimum 12 produk
- Simpan senarai produk dalam senarai khusus sehingga anda dapat menghasilkan lajur produk tunggal dengan mudah
- Menggunakan RANDBETWEEN () mungkin mengembalikan item pendua dalam senarai
- Gunakan fungsi RAND () untuk memutuskan apakah produk ini disertakan atau tidak
- Gunakan TEXTJOIN () untuk menggabungkan bukan kosong dengan titik koma di antara
- Sekarang anda mempunyai satu hasil, bagaimana anda memperoleh banyak hasil
- Mengejutkan bahawa satu Salin dan beberapa Nilai Tampal akan menampal hasil formula sekarang
- Percepat Nilai Tampal dengan menggunakan F4 to Re do
- Tetapi - cara yang sangat pantas: Gunakan What-If Tools dan Data Table dengan sel kosong sebagai Column Input Cell
- Terima kasih kepada Profesor Simon Benninga untuk kaedah ini
Transkrip Video
Belajar Excel Dari, Episod 2155: Menjana Pelbagai Hasil Rawak Dari Satu Formula.
Hey. Selamat kembali ke netcast. Saya Bill Jelen. Selain itu, untuk podcast dan buku penulisan, saya menulis artikel bulanan untuk majalah Strategic Finance. Saya sedang mengerjakan artikel bulan depan di mana saya menunjukkan kepada mereka bagaimana menggunakan kueri kuasa untuk membahagi lajur; data dipisahkan ke baris dan saya perlu menghasilkan beberapa data palsu untuk itu, dan mengapa saya tidak membuka fail dari episod 2097, saya tidak tahu. Saya hanya mahu membuat beberapa data palsu. Jadi, di mana sahaja dari 2 hingga 12 produk dalam satu sel, dan, dalam proses melakukannya, saya menggunakan banyak helah dari podcast - teks bergabung; isi APPLE, BANANA, CHERRY; Jalan Rawak; F4 To Redo - dan saya berfikir sepanjang perjalanan saya menemui beberapa perkara menarik tentang bagaimana mempercepat proses ini.
Sekarang, pertama, akan lebih baik sekiranya saya dapat membuat satu formula array besar yang akan menghasilkan data tersebut. Saya boleh menyalin formula itu tetapi saya tidak dapat mencapai salinan CTRL + SHIFT + ENTER saya dan saya hanya mencari sesuatu yang lebih sederhana pada pagi itu. Saya peminat RANDBETWEEN. Saya menggunakan RANDBETWEEN sepanjang masa. Jadi, jika kita mempunyai senarai 12 produk dan kemudian, di sini, menghasilkan rangkaian jawapan menggunakan RANDBETWEEN, jadi indeks A1 hingga A12, meminta nombor rawak dari 1 hingga 12, baik setiap kali saya menekan F9, saya mendapat senarai produk yang berbeza, dan kemudian saya mahukan jumlah produk yang berbeza dalam setiap produk, jadi di sini, RANDBETWEEN dari 2 hingga 7 atau 2 hingga 12 atau apa sahaja had atas atau bawah, dan kemudian menggunakan TAXJOIN, fungsi baru yang hebat ini Office 365, dipisahkan oleh;, abaikan kekosongan, dan kemudian kita 'kembali dari E2 di sana ke mana sahaja di E2 hingga E12 - berdasarkan nilai ke-6 dalam kes ini - akan menghasilkan senarai itu, baiklah? Tetapi alasan saya tidak suka ini adalah kerana saya terus menekan F9, lihat, saya mula mendapat ulangan dan, anda tahu, diduga ini adalah pesanan yang datang dari laman web dan mengapa seseorang memesan ELDEBERRY? ELDERBERRY tidak masuk akal, bukan? Jadi, saya tidak suka peluang untuk mendapatkan TARIKH TARIKH. Saya mahu mempunyai senarai yang unik. Jadi, inilah yang saya memutuskan bahawa saya akan lakukan.diduga ini adalah pesanan yang datang dari laman web dan mengapa seseorang memesan ELDEBERRY? ELDERBERRY tidak masuk akal, bukan? Oleh itu, saya tidak menyukai peluang untuk mendapatkan DATE DATE. Saya mahu mempunyai senarai yang unik. Jadi, inilah yang saya memutuskan bahawa saya akan lakukan.diduga ini adalah pesanan yang datang dari laman web dan mengapa seseorang memesan ELDEBERRY? ELDERBERRY tidak masuk akal, bukan? Oleh itu, saya tidak menyukai peluang untuk mendapatkan DATE DATE. Saya mahu mempunyai senarai yang unik. Jadi, inilah yang saya memutuskan bahawa saya akan lakukan.
Pertama, saya akan membuat senarai 12 produk dan saya menghafalnya sebagai senarai tersuai, jadi saya hanya dapat menghasilkan senarai item abjad yang bagus, dan kemudian saya mahu di mana saja dari 2, anda tahu, hingga sekitar 7 ini, dan jadi apa yang saya buat di sini adalah saya katakan = JIKA TANDA. RAND adalah fungsi hebat yang menghasilkan perpuluhan dari 0 hingga 1 adalah <.6. Oleh itu, dengan kata lain dalam kira-kira 60% kes, saya ingin anda membawa produk itu ke sini ke ruangan B, jika tidak, berikan saya apa-apa "". Saya akan menyalinnya. Apa yang akan saya lakukan ialah menjana senarai produk. Tidak akan ada ulangan. Tidak ada kemungkinan pengulangan, dan setiap kali saya menekan F9, saya mendapat senarai produk yang berbeza, dan, ya, nampaknya kami mendapat, anda tahu, jumlah produk yang betul setiap kali. (= JIKA (RAND () <0.6, A1, “”))
Seterusnya, Tajuk Carta; mereka menawarkan kami dua tempat berbeza untuk carta - Di Atas Carta Baiklah. Oleh itu, setelah kita memilikinya, fungsi baru di Excel di Office 365 adalah TEXTJOIN. Saya suka ini. Pembatas akan menjadi; dan kemudian abaikan kosong. Tidak menjadi masalah. Saya sebenarnya … yeah, tidak, ia tidak menjadi masalah di sini. Itulah perkara penting. Kita akan abaikan kosong. BENAR, dan inilah senarai produk kami seperti itu. Baik. Jadi, ada senarai produk kami untuk barisan pertama, tetapi saya perlu menghasilkan banyak perkara, dan di sinilah kita benar-benar sampai ke masalah, masalah yang saya cuba selesaikan dalam kes ini. (= TEXTJOIN (“;”, BENAR, B1: B12))
Sekarang, jika saya hanya menyalin formula itu, baiklah, jika saya mengambil formula asal itu dan datang ke sini dan mengeditnya - pilih:, tekan F4 untuk memastikan bahawa saya mempunyai rujukan mutlak, dan salin - anda Saya akan melihat bahawa saya berakhir dengan barang-barang yang sama sepanjang jalan. Ini bukan data palsu yang sangat menarik, bukan? Jadi, itu tidak akan berjaya. Apa yang perlu saya lakukan ialah saya perlu mengambil keputusan formula ini dan membuat banyak dari mereka, baiklah? (= TEXTJOIN (“;”, BENAR, $ B $ 1: $ B $ 12))
So, initially I did this. I did CONTROL+C, and then I come here and I'm going to do paste special values -- or I guess that's just paste -- and PASTE VALUES like that, alright, and what's fascinating to me -- and I've talked about this once on the podcast before and everyone in the YouTube comments was, well, of course that's going to work; no -- what's fascinating to me is I copied cell C14, and so you would think that, when I copied C14, that text from C14 would have been copied to the clipboard, but it's not. It's pointing to C14, right? So, the first time that I paste, I got CHERRY, DATE, ELDERBERRY, but you now see that C14, the marching ants, has changed to APPLE, CHERRY, FIG, and so I’m going to come down here and I'm going to PASTE VALUES again, and I'm always shocked that it changed to the new value.
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.
Baiklah, hei. Saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun fail
Muat turun fail sampel di sini: Podcast2155.xlsm
