Utilizzo della tabella dati what-if di Excel per generare una serie di risultati casuali. Anche se hai una formula complicata che è il risultato di diversi passaggi, la tabella dati ti consentirà di generare centinaia di risposte al modello senza ripetere il modello centinaia di volte.
Guarda un video
- L'obiettivo è creare dati di esempio con prodotto; prodotto; prodotto; prodotto
- L'obiettivo è avere sempre 2 o più prodotti, fino a un massimo di 12
- Memorizza un elenco di prodotti in un elenco personalizzato in modo da poter generare facilmente una colonna di singoli prodotti
- L'uso di RANDBETWEEN () potrebbe restituire elementi duplicati nell'elenco
- Utilizzare la funzione RAND () per decidere se questo prodotto è incluso o meno
- Usa TEXTJOIN () per concatenare gli spazi non vuoti con punti e virgola in mezzo
- Ora che hai un risultato, come puoi ottenere molti risultati
- Sorprendente che una copia e più valori Incolla incolleranno il risultato corrente della formula
- Accelera i valori di incolla utilizzando F4 per ripetere
- Ma - un modo super veloce: usa strumenti what-if e una tabella dati con una cella vuota come cella di input della colonna
- Grazie al professor Simon Benninga per questo metodo
Trascrizione del video
Impara Excel da, episodio 2155: Genera più risultati casuali da una formula.
Hey. Bentornato al netcast. Sono Bill Jelen. Ebbene, oltre al podcast e alla scrittura di libri, scrivo un articolo mensile per la rivista Strategic Finance. Stavo lavorando all'articolo del mese prossimo in cui stavo mostrando loro come utilizzare la query di alimentazione per dividere una colonna di; dati separati in righe e avevo bisogno di generare alcuni dati falsi per questo, e perché non ho aperto il file dall'episodio 2097, non ho idea. Volevo solo creare alcuni dati falsi. Quindi, ovunque da 2 a 12 prodotti in una cella e, nel farlo, ho usato molti trucchi del podcast: join di testo; riempire MELA, BANANA, CILIEGIA; Passeggiata casuale; F4 To Redo - e penso di aver scoperto alcune cose interessanti su come accelerare questo processo.
Ora, prima di tutto, sarebbe stato fantastico se avessi potuto creare un'enorme formula di matrice enorme che avrebbe generato quei dati. Avrei potuto copiare quella formula ma non sono riuscito a raggiungere la mia copia di CTRL + MAIUSC + INVIO e stavo solo cercando qualcosa di più semplice quella mattina. Sono un grande fan di RANDBETWEEN. Uso RANDBETWEEN tutto il tempo. Quindi, se avessimo un elenco di 12 prodotti e quindi, qui, generassimo una serie di risposte usando RANDBETWEEN, quindi l'indice da A1 a A12, chiedendo un numero casuale da 1 a 12, beh ogni volta che premo F9, ottengo un diverso elenco di prodotti, quindi voglio un numero diverso di prodotti in ciascuno, quindi qui, RANDBETWEEN da 2 a 7 o da 2 a 12 o qualunque sia il limite superiore o inferiore, e quindi utilizzare TAXJOIN, quella fantastica nuova funzione in Office 365, separato da;, ignora gli spazi vuoti e poi noi 'andare da E2 proprio lì giù a qualsiasi punto da E2 a E12 - in questo caso in base al sesto valore - genererà quella lista, va bene? Ma il motivo per cui non mi piace è che ho continuato a premere F9, vedi, inizio a ricevere ripetizioni e, sai, presumibilmente si tratta di ordini provenienti da un sito Web e perché qualcuno dovrebbe ordinare ELDEBERRY? ELDERBERRY semplicemente non ha senso, giusto? Quindi, non mi è piaciuta la possibilità di ottenere DATE DATE. Volevo avere una lista unica. Quindi, ecco cosa ho deciso di fare.presumibilmente si tratta di ordini provenienti da un sito Web e perché qualcuno dovrebbe ordinare ELDEBERRY? ELDERBERRY semplicemente non ha senso, giusto? Quindi, non mi è piaciuta la possibilità di ottenere DATE DATE. Volevo avere una lista unica. Quindi, ecco cosa ho deciso di fare.presumibilmente si tratta di ordini provenienti da un sito Web e perché qualcuno dovrebbe ordinare ELDEBERRY? ELDERBERRY semplicemente non ha senso, giusto? Quindi, non mi è piaciuta la possibilità di ottenere DATE DATE. Volevo avere una lista unica. Quindi, ecco cosa ho deciso di fare.
Prima di tutto, stavo per creare un elenco dei 12 prodotti e l'ho memorizzato come un elenco personalizzato, quindi posso semplicemente generare un bel elenco alfabetico di articoli, e poi volevo ovunque da 2, sai, a circa 7 di questi, e quindi quello che ho fatto qui è che dico = SE il RAND. RAND è una funzione fantastica che genera un decimale da 0 a 1 è <.6. Quindi, in altre parole, in circa il 60% dei casi, voglio che tu porti quel prodotto qui nella colonna B, altrimenti non darmi nulla “”. Lo copierò. Quello che farà per me è generare un elenco di prodotti. Non ci saranno mai ripetizioni. Non c'è possibilità di ripetizioni e ogni volta che premo F9, ottengo un elenco diverso di prodotti e, sì, sembra che stiamo ottenendo, sai, il numero giusto di prodotti ogni volta. (= SE (CASUALE () <0,6, A1, ""))
Successivamente, Titolo grafico; ci offrono due posti diversi per un grafico: Sopra il grafico a Va bene. Quindi, ora che ce l'abbiamo, la nuova funzione in Excel in Office 365 è TEXTJOIN. Amo questo. Il delimitatore sarà un; e quindi ignora vuoto. Non importa. In realtà … sì, no, qui non importa. Questa è la cosa importante. Ignoreremo il vuoto. VERO, e quindi ecco la nostra lista di prodotti del genere. Va bene. Quindi, ecco il nostro elenco di prodotti per la prima riga, ma devo generarne un intero gruppo, ed è qui che arriviamo al problema, il problema che stavo cercando di risolvere in questo caso particolare. (= TEXTJOIN (";", TRUE, B1: B12))
Ora, se solo copiassi quella formula, va bene, se prendessi quella formula originale e venissi qui e lo modifichi - scegli:, premi F4 per assicurarti di avere un riferimento assoluto, e copialo - tu vedrò che finirò con oggetti identici fino in fondo. Non sono dati falsi molto interessanti, giusto? Quindi non funzionerà. Quello che devo fare è prendere il risultato di questa formula e crearne un intero gruppo, va bene? (= TEXTJOIN (";", TRUE, $ 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.
Beh ciao. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2155.xlsm
