Verwenden der Was-wäre-wenn-Datentabelle von Excel, um eine Reihe zufälliger Ergebnisse zu generieren. Selbst wenn Sie eine knifflige Formel haben, die das Ergebnis mehrerer Schritte ist, können Sie mit der Datentabelle Hunderte von Antworten auf das Modell generieren, ohne das Modell hundertmal zu wiederholen.
Schau Video
- Ziel ist es, Beispieldaten mit Produkt; Produkt; Produkt; Produkt zu erstellen
- Ziel ist es, immer 2 oder mehr Produkte zu haben, bis zu maximal 12
- Speichern Sie eine Liste von Produkten in einer benutzerdefinierten Liste, damit Sie problemlos eine Spalte mit einzelnen Produkten erstellen können
- Bei Verwendung von RANDBETWEEN () werden möglicherweise doppelte Elemente in der Liste zurückgegeben
- Verwenden Sie die Funktion RAND (), um zu entscheiden, ob dieses Produkt enthalten ist oder nicht
- Verwenden Sie TEXTJOIN (), um die Nicht-Leerzeichen mit dazwischen liegenden Semikolons zu verketten
- Wie können Sie nun, da Sie ein Ergebnis haben, viele Ergebnisse erzielen?
- Es ist überraschend, dass eine Kopie und mehrere Einfügewerte das aktuelle Ergebnis der Formel einfügen
- Beschleunigen Sie die Einfügewerte, indem Sie F4 verwenden, um erneut auszuführen
- Aber - ein superschneller Weg: Verwenden Sie Was-wäre-wenn-Tools und eine Datentabelle mit einer leeren Zelle als Spalteneingabezelle
- Vielen Dank an Professor Simon Benninga für diese Methode
Video-Transkript
Lernen Sie Excel aus, Episode 2155: Generieren Sie mehrere zufällige Ergebnisse aus einer Formel.
Hallo. Willkommen zurück im Netcast. Ich bin Bill Jelen. Zusätzlich zum Podcast und zum Schreiben von Büchern schreibe ich einen monatlichen Artikel für das Strategic Finance Magazine. Ich arbeitete an dem Artikel des nächsten Monats, in dem ich ihnen zeigte, wie man die Leistungsabfrage verwendet, um eine Spalte von zu teilen. Daten in Zeilen getrennt und ich musste dafür einige gefälschte Daten generieren, und warum ich die Datei aus Episode 2097 nicht geöffnet habe, weiß ich nicht. Ich wollte nur einige gefälschte Daten erstellen. Also, irgendwo zwischen 2 und 12 Produkte in einer Zelle, und dabei habe ich viele Tricks aus dem Podcast verwendet - Text Join; füllen Sie APPLE, BANANA, CHERRY; Zielloser Spaziergang; F4 To Redo - und ich denke, ich habe auf dem Weg einige interessante Dinge entdeckt, wie ich diesen Prozess beschleunigen kann.
Zunächst einmal wäre es großartig gewesen, wenn ich nur eine riesige massive Array-Formel hätte erstellen können, die diese Daten generiert hätte. Ich hätte diese Formel nach unten kopieren können, aber ich konnte meine Kopie von STRG + UMSCHALT + EINGABETASTE nicht erreichen und suchte an diesem Morgen nur nach etwas Einfacherem. Ich bin ein großer Fan von RANDBETWEEN. Ich benutze RANDBETWEEN die ganze Zeit. Wenn wir also eine Liste mit 12 Produkten hätten und dann hier eine Reihe von Antworten mit RANDBETWEEN generiert hätten, würde der Index von A1 bis A12, der nach einer Zufallszahl von 1 bis 12 fragt, jedes Mal, wenn ich F9 drücke, angezeigt eine andere Liste von Produkten, und dann möchte ich eine andere Anzahl von Produkten in jedem, also hier RANDBETWEEN von 2 bis 7 oder 2 bis 12 oder was auch immer die obere oder untere Grenze ist, und dann mit TAXJOIN, diese fantastische neue Funktion in Office 365, getrennt durch;, ignoriert die Leerzeichen, und dann 'Wenn Sie von E2 nach E2 zu E12 wechseln - basierend auf dem 6. Wert in diesem Fall - wird diese Liste generiert, okay? Aber der Grund, warum ich das nicht mag, ist, dass ich immer wieder F9 gedrückt habe. Ich bekomme Wiederholungen und, wissen Sie, angeblich sind dies Bestellungen von einer Website und warum sollte jemand ELDEBERRY bestellen? ELDERBERRY macht einfach keinen Sinn, oder? Also mochte ich die Chance dort nicht, DATE DATE zu bekommen. Ich wollte eine einzigartige Liste haben. Also, hier ist, was ich beschlossen habe, dass ich tun würde.angeblich handelt es sich um Bestellungen von einer Website und warum sollte jemand ELDEBERRY bestellen? ELDERBERRY macht einfach keinen Sinn, oder? Also mochte ich die Chance dort nicht, DATE DATE zu bekommen. Ich wollte eine einzigartige Liste haben. Also, hier ist, was ich beschlossen habe, dass ich tun würde.angeblich handelt es sich um Bestellungen von einer Website und warum sollte jemand ELDEBERRY bestellen? ELDERBERRY macht einfach keinen Sinn, oder? Also mochte ich die Chance dort nicht, DATE DATE zu bekommen. Ich wollte eine einzigartige Liste haben. Also, hier ist, was ich beschlossen habe, dass ich tun würde.
Zuerst wollte ich eine Liste der 12 Produkte erstellen und habe diese als benutzerdefinierte Liste gespeichert, damit ich einfach eine schöne alphabetische Liste von Elementen erstellen kann, und dann wollte ich irgendwo zwischen 2 und ungefähr 7 von diesen, und so, was ich hier getan habe, sage ich = WENN der RAND. RAND ist eine großartige Funktion, die eine Dezimalstelle von 0 bis 1 generiert. <0,6. Mit anderen Worten, in etwa 60% der Fälle möchte ich, dass Sie dieses Produkt hierher in Spalte B bringen, andernfalls geben Sie mir nichts "". Ich werde das aufschreiben. Was das für mich tun wird, ist eine Liste von Produkten zu erstellen. Es wird niemals Wiederholungen geben. Es gibt keine Chance auf Wiederholungen, und jedes Mal, wenn ich F9 drücke, erhalte ich eine andere Liste von Produkten, und es sieht so aus, als würden wir jedes Mal die richtige Anzahl von Produkten erhalten. (= IF (RAND () <0,6, A1, ""))
Als nächstes Diagrammtitel; Sie bieten uns zwei verschiedene Plätze für ein Diagramm an - über dem Diagramm ein Okay. Nun, da wir das haben, ist die neue Funktion in Excel in Office 365 TEXTJOIN. Ich liebe es. Das Trennzeichen wird ein sein; und dann leer ignorieren. Es spielt keine Rolle. Ich eigentlich … ja, nein, das ist hier wirklich egal. Das ist das Wichtige. Wir werden leer ignorieren. TRUE, und dann ist hier unsere Liste solcher Produkte. Okay. Dort haben wir also unsere Produktliste für die erste Zeile, aber ich muss eine ganze Reihe davon generieren, und hier kommen wir tatsächlich zu dem Problem, das ich in diesem speziellen Fall zu lösen versucht habe. (= TEXTJOIN (";", TRUE, B1: B12))
Wenn ich diese Formel nur nach unten kopieren würde, in Ordnung, wenn ich diese Originalformel genommen und hierher gekommen wäre und diese bearbeitet hätte - wählen Sie :, drücken Sie F4, um sicherzustellen, dass ich eine absolute Referenz habe, und kopieren Sie sie nach unten - Sie Ich werde sehen, dass ich ganz unten identische Gegenstände habe. Es sind keine sehr interessanten gefälschten Daten, oder? Das wird also nicht funktionieren. Was ich tun muss, ist, dass ich das Ergebnis dieser Formel nehmen und eine ganze Reihe davon erstellen muss, okay? (= TEXTJOIN (";", TRUE, $ B $ 1: $ B $ 12))
Also habe ich das anfangs gemacht. Ich habe CONTROL + C gemacht, und dann komme ich hierher und werde spezielle Werte einfügen - oder ich denke, das ist nur Einfügen - und solche Werte einfügen, okay, und was mich fasziniert - und ich habe Ich habe bereits einmal im Podcast darüber gesprochen, und alle in den YouTube-Kommentaren waren sich sicher, dass das funktionieren wird. nein - was mich fasziniert ist, dass ich Zelle C14 kopiert habe, und Sie würden denken, dass beim Kopieren von C14 der Text von C14 in die Zwischenablage kopiert worden wäre, aber das ist es nicht. Es zeigt auf C14, richtig? Das erste Mal, dass ich einfüge, habe ich KIRSCHE, DATUM, ELDERBERRIE, aber Sie sehen jetzt, dass C14, die marschierenden Ameisen, zu APFEL, KIRSCHE, ABB. Geändert wurde, und so werde ich hier runterkommen und ich ' Ich gehe wieder zu PASTE VALUES und bin immer schockiert, dass es auf den neuen Wert geändert wurde.
Okay, wenn ich nur PASTE VALUES, PASTE VALUES, PASTE VALUES, PASTE VALUES einfügen könnte, würde dies jedes Mal eine neue Antwort generieren. Dieses Mal, wenn ich WERTE, APFEL, BANANE, DATUM, FIG, ICEBERG, JACKFRUIT EINFÜGTE, aber es ist mühsam, die Maus zu greifen und hierher zu kommen und EINFÜGEN zu wählen und WERTE zu wählen. Also werde ich die großartige REDO-Funktion verwenden - nicht UNDO, REDO - die F4 ist, also F4, füge den neuen Wert ein. Wenn ich F4 drücke, bekomme ich nur BANANA, DATE, ELDERBERRY, LIME. Es ist also einfach. F4, PFEIL UNTEN, F4, PFEIL UNTEN, F4, PFEIL UNTEN, in Ordnung, und das Leben ist großartig. Dort habe ich genug gefälschte Daten für den Artikel, okay, aber selbst das ist ein Ärger, okay?
Die Methode, die ich von einem guten Freund von mir gelernt habe, der jetzt verstorben ist - Professor Simon Benninga - hat mir dies beigebracht - wenn wir ein Modell haben - und dies ist im Wesentlichen ein Modell - das RAND oder RANDBETWEEN verwendet und ein generiert Das Ergebnis ist, dass Sie mehrere Versionen dieses Ergebnisses haben können, okay, und wir müssen von der Zelle links neben dem Ergebnis unseres Modells beginnen, diese Zelle und die Zelle auswählen, die Ihre Formel enthält, und dann Wie viele Sie auch wollen - sagen wir, ich brauchte 100 davon oder 132 davon - kopieren Sie einfach oder wählen Sie sie ganz nach unten aus, und wir kommen hier zur Registerkarte DATEN, zur Registerkarte DATEN, WAS-WENN ANALYSE, DATENTABELLE, in Ordnung?
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.
Nun, hey. Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2155.xlsm
