Korzystanie z tabeli danych warunkowych programu Excel do generowania wielu losowych wyników. Nawet jeśli masz skomplikowaną formułę, która jest wynikiem kilku kroków, Tabela danych pozwoli Ci wygenerować setki odpowiedzi do modelu bez powtarzania modelu 100 razy.
Obejrzyj wideo
- Celem jest utworzenie przykładowych danych z produktem; produktem; produktem; produktem
- Celem jest zawsze mieć 2 lub więcej produktów, maksymalnie 12
- Przechowuj listę produktów na liście niestandardowej, aby łatwo wygenerować kolumnę pojedynczych produktów
- Użycie RANDBETWEEN () może zwrócić zduplikowane elementy na liście
- Użyj funkcji RAND (), aby zdecydować, czy ten produkt jest uwzględniony, czy nie
- Użyj TEXTJOIN (), aby połączyć elementy niebędące spacjami z średnikami między nimi
- Teraz, gdy masz już jeden wynik, jak uzyskać wiele wyników
- Zaskakujące jest to, że jedna kopia i wiele wklejanych wartości spowoduje wklejenie bieżącego wyniku formuły
- Przyspiesz Wklej wartości, używając F4 do Re do
- Ale - super szybki sposób: użyj narzędzi warunkowych i tabeli danych z pustą komórką jako komórką wejściową kolumny
- Podziękowania dla profesora Simona Benninga za tę metodę
Transkrypcja wideo
Learn Excel From, odcinek 2155: Generowanie wielu losowych wyników na podstawie jednej formuły.
Hej. Witamy ponownie w netcast. Jestem Bill Jelen. Cóż, oprócz podcastu i pisania książek, co miesiąc piszę artykuł dla magazynu Strategic Finance. Pracowałem nad artykułem w przyszłym miesiącu, w którym pokazywałem im, jak używać funkcji Power Query do dzielenia kolumny; Oddzieliłem dane do wierszy i musiałem wygenerować fałszywe dane do tego, i dlaczego nie otworzyłem pliku z odcinka 2097, nie mam pojęcia. Chciałem tylko stworzyć fałszywe dane. Tak więc, od 2 do 12 produktów w jednej komórce, a robiąc to, wykorzystałem wiele sztuczek z podcastu - łączenie tekstu; wypełnij JABŁKO, BANAN, WIŚNIA; Losowy spacer; F4 To Redo - i myślę, że po drodze odkryłem kilka interesujących rzeczy, jak przyspieszyć ten proces.
Po pierwsze, byłoby wspaniale, gdybym mógł po prostu stworzyć jedną ogromną formułę tablicową, która wygenerowałaby te dane. Mogłem skopiować tę formułę, ale nie mogłem dotrzeć do mojej kopii CTRL + SHIFT + ENTER i szukałem czegoś prostszego tego ranka. Jestem wielkim fanem RANDBETWEEN. Cały czas używam RANDBETWEEN. Tak więc, gdybyśmy mieli listę 12 produktów, a następnie tutaj wygenerowaliśmy serię odpowiedzi za pomocą RANDBETWEEN, więc indeks od A1 do A12, prosząc o losową liczbę od 1 do 12, cóż, za każdym razem, gdy naciskam F9, otrzymuję inną listę produktów, a następnie chcę inną liczbę produktów w każdym z nich, więc tutaj, RANDBETWEEN od 2 do 7 lub od 2 do 12 lub jakikolwiek jest górny lub dolny limit, a następnie używając TAXJOIN, tej niesamowitej nowej funkcji w Office 365, oddzielone;, zignoruj puste miejsca, a następnieprzejście z E2 do dowolnego miejsca w E2 do E12 - w tym przypadku na podstawie szóstej wartości - wygeneruje tę listę, dobrze? Ale powodem, dla którego mi się to nie podoba, jest to, że naciskałem F9, widzisz, zaczynam się powtarzać i, wiesz, podobno to zamówienia ze strony internetowej i po co ktoś miałby zamawiać ELDEBERRY? ELDERBERRY po prostu nie ma sensu, prawda? Więc nie podobała mi się szansa uzyskania DATE DATE. Chciałem mieć wyjątkową listę. Oto, co zdecydowałem, że zamierzam zrobić.podobno to zamówienia ze strony internetowej i dlaczego ktoś miałby zamawiać ELDEBERRY? ELDERBERRY po prostu nie ma sensu, prawda? Więc nie podobała mi się szansa uzyskania DATE DATE. Chciałem mieć wyjątkową listę. Oto, co zdecydowałem, że zamierzam zrobić.podobno to zamówienia ze strony internetowej i dlaczego ktoś miałby zamawiać ELDEBERRY? ELDERBERRY po prostu nie ma sensu, prawda? Więc nie podobała mi się szansa uzyskania DATE DATE. Chciałem mieć wyjątkową listę. Oto, co zdecydowałem, że zamierzam zrobić.
Po pierwsze, miałem zamiar stworzyć listę 12 produktów i zapamiętałem ją jako listę niestandardową, więc mogę po prostu wygenerować ładną alfabetyczną listę pozycji, a potem chciałem gdziekolwiek od 2 do około 7 z tych, więc to, co tutaj zrobiłem, to mówię = JEŻELI RAND. RAND to niesamowita funkcja, która generuje ułamek dziesiętny od 0 do 1 to <.6. Innymi słowy, w około 60% przypadków chcę, abyś przyniósł ten produkt do kolumny B, w przeciwnym razie nie dawaj mi nic „”. Spiszę to. To, co dla mnie zrobi, to wygenerowanie listy produktów. Nigdy się nie powtórzy. Nie ma szans na jakiekolwiek powtórzenie i za każdym razem, gdy naciskam F9, otrzymuję inną listę produktów i tak, wygląda na to, że za każdym razem otrzymujemy odpowiednią liczbę produktów. (= JEŻELI (RAND () <0,6; A1; „”))
Następnie tytuł wykresu; oferują nam dwa różne miejsca na wykres - Powyżej wykresu w porządku. Tak więc, skoro już to mamy, nową funkcją w programie Excel w Office 365 jest TEXTJOIN. Uwielbiam to. Separatorem będzie; a następnie zignoruj puste. To nie ma znaczenia. Właściwie… tak, nie, to naprawdę nie ma tutaj znaczenia. To jest ważna rzecz. Zignorujemy puste. PRAWDA, a oto nasza lista takich produktów. W porządku. Mamy więc naszą listę produktów w pierwszym wierszu, ale muszę wygenerować ich całą masę i tutaj właśnie dochodzimy do problemu, problemu, który próbowałem rozwiązać w tym konkretnym przypadku. (= TEXTJOIN („;”; PRAWDA; B1: B12))
Teraz, gdybym po prostu skopiował tę formułę, dobrze, gdybym wziął tę oryginalną formułę i przyszedł tutaj i edytował to - wybierz:, naciśnij F4, aby upewnić się, że mam bezwzględne odniesienie, i skopiuj to w dół - ty Zobaczysz, że skończę z identycznymi przedmiotami na samym dole. To niezbyt interesujące fałszywe dane, prawda? Więc to nie zadziała. To, co muszę zrobić, to wziąć wynik tej formuły i stworzyć ich całą masę, dobrze? (= TEXTJOIN („;”; TRUE; 1 $ B $: 12 $ B $))
Więc na początku to zrobiłem. Zrobiłem CONTROL + C, a potem przychodzę tutaj i zamierzam wkleić specjalne wartości - lub myślę, że to po prostu wklej - i WKLEJ WARTOŚCI w ten sposób, dobrze, i co mnie fascynuje - i mam rozmawialiśmy o tym kiedyś w podcastie i wszyscy w komentarzach na YouTube byli, cóż, oczywiście, że to zadziała; nie - fascynuje mnie to, że skopiowałem komórkę C14, więc można by pomyśleć, że gdy skopiowałem C14, ten tekst z C14 zostałby skopiowany do schowka, ale tak nie jest. Wskazuje na C14, prawda? Tak więc, kiedy po raz pierwszy wkleiłem, dostałem WIŚNĘ, DATĘ, ELDERBERRY, ale teraz widzisz, że maszerujące mrówki C14 zmieniły się w JABŁKO, WIŚNIA, FIGURA, więc zejdę tutaj i ja ” mam zamiar ponownie WKLEĆ WARTOŚCI i zawsze jestem zszokowany, że zmieniło się na nową wartość.
W porządku, więc gdybym mógł po prostu WKLEJ WARTOŚCI, WKLEJ WARTOŚCI, WKLEJ WARTOŚCI, WKLEJ WARTOŚCI, wygenerowałoby to nową odpowiedź za każdym razem. Tym razem, kiedy WKLEJĘ WARTOŚCI, JABŁKO, BANAN, DATĘ, FIGI, LODÓWKĘ, JACKFRUIT, ale spójrz, trudno jest złapać mysz i podejść tutaj i wybrać WKLEJ i wybrać WARTOŚCI. Więc użyję niesamowitej funkcji REDO - nie UNDO, REDO - czyli F4, więc F4, wklej nową wartość. Kiedy naciskam F4, dostaję tylko BANAN, DATA, ELDERBERRY, LIME. Więc to jest po prostu proste. F4, STRZAŁKA W DÓŁ, F4, STRZAŁKA W DÓŁ, F4, STRZAŁKA W DÓŁ, w porządku, a życie jest świetne. Mam wystarczająco dużo fałszywych danych do artykułu, dobrze, ale nawet to jest kłopotliwe, dobrze?
Tak więc metoda, której nauczyłem się od mojego dobrego przyjaciela, który już nie żyje - profesor Simon Benninga nauczył mnie tego - jeśli mamy model - i jest to w istocie model - polega na używaniu RAND lub RANDBETWEEN i generowaniu wynik, możesz zrobić wiele wersji tego wyniku, w porządku, i musimy zacząć od komórki na lewo od wyniku naszego modelu, wybrać tę komórkę i komórkę zawierającą twoją formułę, a następnie ile tylko zechcesz - powiedzmy, że potrzebowałem 100 lub 132 z nich - po prostu skopiuj lub zaznacz całą drogę w dół, a przejdziemy tutaj do karty DANE, DANE, CO-JEŚLI ANALIZA, TABELA DANYCH, dobrze?
Teraz używam tego cały czas, aby pokazać, jak tworzyć wiele scenariuszy, ale w tym przypadku tak naprawdę nie mamy nic dla ROW INPUT CELL. W przypadku COLUMN INPUT CELL po prostu wybierz dowolną pustą komórkę - nie ma znaczenia, która to komórka - i uruchomi ten model 132 razy, za każdym razem naciskając klawisz F9, aby wygenerować nowe losowe wartości, kliknij OK i bam, i to działa. Bardzo mi się to podoba.
Teraz to jest na żywo. Za każdym razem, gdy naciskam F9, otrzymuję nowy zestaw 132 takich. Więc po prostu skopiuj - CONTROL + C - i wyjdź tutaj, WKLEJ SPECJALNE WARTOŚCI, a mamy naszą fałszywą listę produktów i jesteśmy gotowi zasadniczo zrobić to, co było w odcinku 2097: podzielić kolumnę x; x; x; do rzędów. Gorąco polecam obejrzenie tego filmu, to świetne wideo, lub listopadowego wydania magazynu Strategic Finance z listopada 2017 roku. Ukaże się online na początku listopada.
W porządku. Tak więc wszystkie te metody są opisane w książce Power Excel With, wydanie 2017. Kliknij i w prawym górnym rogu, aby to sprawdzić.
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.
Więc hej. Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2155.xlsm
