Menggunakan Tabel Data Bagaimana-Jika Excel untuk menghasilkan sekumpulan hasil acak. Bahkan jika Anda memiliki rumus rumit yang merupakan hasil dari beberapa langkah, Tabel Data akan memungkinkan Anda menghasilkan ratusan jawaban untuk model tanpa mengulang model 100 kali.
Menonton video
- Tujuannya adalah untuk membuat data sampel dengan produk; produk; produk; produk
- Sasarannya adalah selalu memiliki 2 produk atau lebih, hingga maksimal 12
- Simpan daftar produk dalam daftar kustom sehingga Anda dapat dengan mudah menghasilkan kolom produk tunggal
- Menggunakan RANDBETWEEN () mungkin mengembalikan item duplikat dalam daftar
- Gunakan fungsi RAND () untuk memutuskan apakah produk ini termasuk atau tidak
- Gunakan TEXTJOIN () untuk menggabungkan yang tidak kosong dengan titik koma di antaranya
- Sekarang setelah Anda memiliki satu hasil, bagaimana Anda membuat banyak hasil
- Mengejutkan bahwa satu Salin dan beberapa Nilai Tempel akan menempelkan hasil rumus saat ini
- Percepat Nilai Tempel dengan menggunakan F4 untuk melakukan Ulang
- Tapi - cara super cepat: Gunakan Alat Bagaimana-Jika dan Tabel Data dengan sel kosong sebagai Sel Input Kolom
- Terima kasih kepada Profesor Simon Benninga untuk metode ini
Transkrip Video
Pelajari Excel Dari, Episode 2155: Menghasilkan Beberapa Hasil Acak Dari Satu Formula.
Hei. Selamat datang kembali di netcast. Saya Bill Jelen. Nah, selain podcast dan buku tulis, saya menulis artikel bulanan untuk majalah Strategic Finance. Saya sedang mengerjakan artikel bulan depan di mana saya menunjukkan kepada mereka bagaimana menggunakan power query untuk membagi kolom; memisahkan data ke baris dan saya perlu membuat beberapa data palsu untuk itu, dan mengapa saya tidak membuka file dari episode 2097, saya tidak tahu. Saya hanya ingin membuat beberapa data palsu. Jadi, di mana saja dari 2 hingga 12 produk dalam satu sel, dan, dalam proses melakukan itu, saya menggunakan banyak trik dari podcast - teks bergabung; isi APPLE, BANANA, CHERRY; Jalan Acak; F4 To Redo - dan saya pikir sepanjang jalan saya menemukan beberapa hal menarik tentang bagaimana mempercepat proses ini.
Sekarang, pertama, alangkah baiknya jika saya bisa membuat satu rumus array besar yang akan menghasilkan data itu. Saya bisa saja menyalin rumus itu tetapi saya tidak bisa mendapatkan salinan CTRL + SHIFT + ENTER saya dan saya hanya mencari sesuatu yang lebih sederhana pagi itu. Saya penggemar berat RANDBETWEEN. Saya menggunakan RANDBETWEEN sepanjang waktu. Jadi, jika kami memiliki daftar 12 produk dan kemudian, di sini, dihasilkan serangkaian jawaban menggunakan RANDBETWEEN, jadi indeks A1 hingga A12, meminta nomor acak dari 1 hingga 12, nah setiap kali saya menekan F9, saya mendapatkan daftar produk yang berbeda, dan kemudian saya ingin jumlah produk yang berbeda di masing-masing, jadi di sini, RANDBETWEEN dari 2 hingga 7 atau 2 hingga 12 atau berapapun batas atas atau bawahnya, dan kemudian menggunakan TAXJOIN, fungsi baru yang mengagumkan di Office 365, dipisahkan oleh;, abaikan yang kosong, lalu kami 'kembali dari E2 ke sana ke mana saja di E2 ke E12 - berdasarkan nilai ke-6 dalam hal ini - akan menghasilkan daftar itu, oke? Tapi alasan saya tidak suka ini adalah karena saya terus menekan F9, lihat, saya mulai mendapatkan pengulangan dan, Anda tahu, diduga ini adalah pesanan yang datang dari situs web dan mengapa seseorang memesan ELDEBERRY? ELDERBERRY tidak masuk akal, bukan? Jadi, saya tidak suka kesempatan mendapatkan DATE DATE. Saya ingin memiliki daftar yang unik. Jadi, inilah yang saya putuskan untuk saya lakukan.diduga ini adalah pesanan yang berasal dari situs web dan mengapa seseorang memesan ELDEBERRY? ELDERBERRY tidak masuk akal, bukan? Jadi, saya tidak suka kesempatan mendapatkan DATE DATE. Saya ingin memiliki daftar yang unik. Jadi, inilah yang saya putuskan untuk saya lakukan.diduga ini adalah pesanan yang berasal dari situs web dan mengapa seseorang memesan ELDEBERRY? ELDERBERRY tidak masuk akal, bukan? Jadi, saya tidak suka kesempatan mendapatkan DATE DATE. Saya ingin memiliki daftar yang unik. Jadi, inilah yang saya putuskan untuk saya lakukan.
Pertama, saya akan membuat daftar 12 produk dan saya telah menghafalnya sebagai daftar kustom, jadi saya bisa membuat daftar alfabet item yang bagus, dan kemudian saya ingin di mana saja dari 2, Anda tahu, sekitar 7 ini, dan apa yang saya lakukan di sini adalah saya mengatakan = JIKA RAND. RAND merupakan fungsi mengagumkan yang menghasilkan desimal dari 0 hingga 1 adalah <.6. Jadi, dengan kata lain dalam sekitar 60% kasus, saya ingin Anda membawa produk itu ke kolom B, jika tidak, jangan beri saya "". Saya akan menyalinnya. Apa yang akan dilakukan untuk saya adalah menghasilkan daftar produk. Tidak akan pernah ada pengulangan. Tidak ada kemungkinan pengulangan, dan setiap kali saya menekan F9, saya mendapatkan daftar produk yang berbeda, dan, ya, sepertinya kami mendapatkan, Anda tahu, jumlah produk yang tepat setiap saat. (= IF (RAND () <0.6, A1, ""))
Selanjutnya, Judul Bagan; mereka menawarkan dua tempat berbeda untuk bagan-- Di Atas Bagan a Alright. Jadi, sekarang setelah kita memilikinya, fungsi baru di Excel di Office 365 adalah TEXTJOIN. Aku suka ini. Pembatas akan menjadi; lalu abaikan kosong. Tidak masalah. Saya sebenarnya … ya, tidak, tidak masalah di sini. Itu yang penting. Kami akan mengabaikan kosong. BENAR, lalu inilah daftar produk seperti itu. Baik. Jadi, di sana kami memiliki daftar produk untuk baris pertama, tetapi saya perlu menghasilkan banyak dari ini, dan di sinilah kami benar-benar membahas masalah tersebut, masalah yang saya coba selesaikan dalam kasus khusus ini. (= TEXTJOIN (“;”, TRUE, B1: B12))
Sekarang, jika saya akan menyalin rumus itu ke bawah, baiklah, jika saya mengambil rumus asli itu dan datang ke sini dan mengedit ini - pilih:, tekan F4 untuk memastikan bahwa saya memiliki referensi absolut, dan salin - Anda Saya akan melihat bahwa saya berakhir dengan barang-barang yang identik sepanjang jalan. Itu bukan data palsu yang sangat menarik, bukan? Jadi, itu tidak akan berhasil. Yang perlu saya lakukan adalah saya harus mengambil hasil dari formula ini dan membuat sejumlah besar, oke? (= TEXTJOIN (“;”, TRUE, $ B $ 1: $ B $ 12))
Jadi, awalnya saya melakukan ini. Saya melakukan CONTROL + C, lalu saya datang ke sini dan saya akan menempelkan nilai-nilai khusus - atau saya rasa itu hanya tempel - dan PASTE NILAI seperti itu, oke, dan apa yang menarik bagi saya - dan saya sudah membicarakan hal ini sekali di podcast sebelumnya dan semua orang di komentar YouTube, yah, tentu saja itu akan berhasil; tidak - yang menarik bagi saya adalah saya menyalin sel C14, jadi Anda akan berpikir bahwa, ketika saya menyalin C14, teks dari C14 itu akan disalin ke papan klip, tetapi ternyata tidak. Ini menunjuk ke C14, bukan? Jadi, pertama kali saya tempel, saya mendapat CHERRY, DATE, ELDERBERRY, tetapi Anda sekarang melihat bahwa C14, semut yang bergerak, telah berubah menjadi APPLE, CHERRY, FIG, jadi saya akan turun ke sini dan saya ' Saya akan kembali ke PASTE VALUES, dan saya selalu terkejut karena nilainya berubah ke nilai yang baru.
Baiklah, jadi, jika saya hanya bisa PASTE VALUES, PASTE VALUES, PASTE VALUES, PASTE VALUES, itu akan menghasilkan jawaban baru setiap saat. Kali ini ketika saya PASTE VALUES, APPLE, BANANA, DATE, FIG, ICEBERG, JACKFRUIT, tapi, lihat, merepotkan untuk mengambil mouse dan datang ke sini dan memilih PASTE dan pilih NILAI. Jadi, saya akan menggunakan fungsi REDO yang luar biasa - bukan UNDO, REDO - yaitu F4, jadi F4, tempelkan nilai baru. Saat saya tekan F4, saya hanya mendapatkan BANANA, DATE, ELDERBERRY, LIME. Jadi, sederhana saja. F4, PANAH BAWAH, F4, PANAH BAWAH, F4, PANAH BAWAH, baiklah, dan hidup ini hebat. Di sana, saya punya cukup data palsu untuk artikel itu, oke, tapi bahkan itu merepotkan, oke?
Jadi, metode yang saya pelajari dari teman baik saya yang sekarang sudah meninggal - profesor Simon Benninga mengajari saya ini - jika kita memiliki model - dan ini pada dasarnya adalah model - yang menggunakan RAND atau RANDBETWEEN dan menghasilkan Hasilnya, yang bisa Anda lakukan adalah Anda bisa memiliki beberapa versi dari hasil itu terjadi, baiklah, dan kita harus mulai dari sel di sebelah kiri hasil model kita, pilih sel itu dan sel yang berisi rumus Anda, lalu , berapa pun yang Anda inginkan - katakanlah saya membutuhkan 100 atau 132 ini - cukup salin atau pilih seluruhnya, dan kita akan menuju ke tab DATA, tab DATA, WHAT-IF ANALISIS, TABEL DATA, oke?
Sekarang, saya menggunakan ini sepanjang waktu untuk menunjukkan cara membuat beberapa skenario tetapi, dalam hal ini, kami tidak memiliki apa pun untuk ROW INPUT CELL. Untuk COLUMN INPUT CELL, cukup pilih sel kosong apa pun - tidak peduli apa sel itu - dan ini akan menjalankan model ini 132 kali, setiap kali pada dasarnya menekan F9 untuk menghasilkan nilai acak baru seperti itu, klik OK , dan, bam, dan berhasil. Saya sangat menyukainya.
Sekarang, ini siaran langsung. Setiap kali saya menekan F9, saya mendapatkan 132 set baru ini. Jadi, cukup salin - CONTROL + C - dan keluar ke sini, PASTE NILAI KHUSUS, dan kami memiliki daftar produk palsu kami, dan kami pada dasarnya siap untuk melakukan apa yang ada di episode 2097: membagi kolom x; x; x; ke baris. Sangat disarankan Anda melihat video itu, itu video yang bagus, atau edisi November, edisi November 2017, majalah Keuangan Strategis. Ini akan online pada awal November.
Baik. Jadi, semua metode ini ada di buku Power Excel With, edisi 2017. Klik i di pojok kanan atas untuk memeriksanya.
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. Saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2155.xlsm
