यादृच्छिक परिणामों का एक समूह उत्पन्न करने के लिए एक्सेल के व्हाट-इफ़ डेटा टेबल का उपयोग करना। यहां तक कि अगर आपके पास एक मुश्किल सूत्र है जो कई चरणों का परिणाम है, तो डेटा तालिका आपको मॉडल के 100 बार दोहराए बिना मॉडल के सैकड़ों उत्तर उत्पन्न करने की अनुमति देगा।
वीडियो देखेंा
- लक्ष्य उत्पाद के साथ नमूना डेटा बनाना है; उत्पाद; उत्पाद; उत्पाद
- लक्ष्य के पास हमेशा 2 या अधिक उत्पाद होते हैं, अधिकतम 12 तक
- कस्टम सूची में उत्पादों की सूची संग्रहीत करें ताकि आप आसानी से एकल उत्पादों का एक स्तंभ उत्पन्न कर सकें
- RANDBETWEEN () का उपयोग करके सूची में डुप्लिकेट आइटम वापस कर सकते हैं
- यह तय करने के लिए RAND () फ़ंक्शन का उपयोग करें कि यह उत्पाद शामिल है या नहीं
- बीच-बीच में सेमी-कॉलन के साथ गैर-रिक्त स्थान को छिपाने के लिए TEXTJOIN () का उपयोग करें
- अब जब आपके पास एक परिणाम है, तो आप कई परिणाम कैसे बनाते हैं
- हैरानी की बात है कि एक कॉपी और कई पेस्ट वैल्यू सूत्र के वर्तमान परिणाम को चिपकाएंगे
- F4 का उपयोग करके चिपकाएँ मानों को पुन: करें
- लेकिन - एक सुपर फास्ट तरीका: कॉलम इनपुट सेल के रूप में खाली सेल के साथ व्हाट-इफ टूल्स और एक डेटा टेबल का उपयोग करें
- इस विधि के लिए प्रोफेसर साइमन बेनिंगा का धन्यवाद
वीडियो ट्रांसक्रिप्ट
एक्सेल से सीखें, एपिसोड 2155: एक सूत्र से कई यादृच्छिक परिणाम उत्पन्न करें।
अरे। नेटकास्ट में आपका स्वागत है। मैं बिल जेलन हूं। ठीक है, इसके अलावा, पॉडकास्ट और किताबें लिखने के लिए, मैं स्ट्रैटेजिक फाइनेंस पत्रिका के लिए एक मासिक लेख लिखता हूं। मैं अगले महीने के लेख पर काम कर रहा था, जहाँ मैं उन्हें दिखा रहा था कि कैसे एक कॉलम को विभाजित करने के लिए पावर क्वेरी का उपयोग किया जाए; डेटा को पंक्तियों में अलग कर दिया और मुझे उसके लिए कुछ नकली डेटा उत्पन्न करने की आवश्यकता थी, और मैंने प्रकरण 2097 से फ़ाइल क्यों नहीं खोली, मुझे कोई पता नहीं है। मैं सिर्फ कुछ नकली डेटा बनाना चाहता था। तो, एक सेल में 2 से 12 उत्पादों से कहीं भी, और, ऐसा करने की प्रक्रिया में, मैंने पॉडकास्ट से बहुत सारे ट्रिक का उपयोग किया - टेक्स्ट जॉइन; भराव, BANANA, चेरी भरें; यादृच्छिक चाल; F4 को फिर से करें - और मुझे लगता है कि जिस तरह से मैंने इस प्रक्रिया को गति देने के कुछ दिलचस्प चीजों की खोज की।
अब, सबसे पहले, यह बहुत अच्छा होता अगर मैं सिर्फ एक विशाल भारी सरणी सूत्र बना पाता जो उस डेटा को उत्पन्न करता। मैं उस फॉर्मूले को कॉपी कर सकता था, लेकिन मैं CTRL + SHIFT + ENTER की अपनी कॉपी तक नहीं पहुँच सका और मैं उस सुबह कुछ सरल खोज रहा था। मैं RANDBETWEEN का बहुत बड़ा प्रशंसक हूं। मैं हर समय RANDBETWEEN का उपयोग करता हूं। इसलिए, अगर हमारे पास 12 उत्पादों की सूची थी और फिर, यहां, रैंडबीटेन का उपयोग करके उत्तरों की एक श्रृंखला तैयार की गई, तो A1 से A12 तक का सूचकांक, 1 से 12 तक यादृच्छिक संख्या के लिए पूछ रहा है, हर बार जब मैं F9 दबाता हूं, मुझे मिलता है उत्पादों की एक अलग सूची, और फिर मैं हर एक में उत्पादों की एक अलग संख्या चाहता हूं, इसलिए यहां 7 या 2 में से 12 के माध्यम से या जो भी ऊपरी या निचली सीमा है, 2 का RANDBETWEEN है, और फिर TAXJOIN का उपयोग करके, उस भयानक कार्य को पूरा करें कार्यालय 365, द्वारा अलग किया गया, खाली को अनदेखा करता है और फिर हमE2 से वहीं जा रहा है E2 से E12 में कहीं भी नीचे - इस मामले में 6 मूल्य के आधार पर - उस सूची को उत्पन्न करेगा, ठीक है? लेकिन इसका कारण यह नहीं है कि मैं F9 दबाता रहा, देखिए, मुझे दोहराव मिलना शुरू हो गया है और आप जानते हैं, कथित तौर पर यह एक वेबसाइट से आने वाले आदेश हैं और कोई व्यक्ति ELDEBERRY को आदेश क्यों देगा? ELDERBERRY सिर्फ मतलब नहीं है, है ना? इसलिए, मुझे DATE DATE मिलने का मौका पसंद नहीं आया। मैं एक अनूठी सूची रखना चाहता था। तो, यहाँ है कि मैं क्या करने जा रहा था कि फैसला किया।कथित तौर पर यह एक वेबसाइट से आने वाले आदेश हैं और कोई व्यक्ति ELDEBERRY को आदेश क्यों देगा? ELDERBERRY सिर्फ समझ में नहीं आता है, है ना? इसलिए, मुझे DATE DATE मिलने का मौका पसंद नहीं आया। मैं एक अनूठी सूची रखना चाहता था। तो, यहाँ है कि मैंने क्या फैसला किया कि मैं क्या करने जा रहा था।कथित तौर पर यह एक वेबसाइट से आने वाले आदेश हैं और कोई व्यक्ति ELDEBERRY को आदेश क्यों देगा? ELDERBERRY सिर्फ मतलब नहीं है, है ना? इसलिए, मुझे DATE DATE मिलने का मौका पसंद नहीं आया। मैं एक अनूठी सूची रखना चाहता था। तो, यहाँ है कि मैं क्या करने जा रहा था कि फैसला किया।
सबसे पहले, मैं 12 उत्पादों की एक सूची बनाने जा रहा था और मुझे यह एक कस्टम सूची के रूप में याद है, इसलिए मैं सिर्फ वस्तुओं की एक अच्छी वर्णमाला सूची तैयार कर सकता हूं, और फिर मैं 2 से कहीं भी चाहता था, आप जानते हैं, लगभग 7 इनमें से, और इसलिए मैंने यहां जो किया वह मैं कहता हूं = आईएफ रैंड। रैंड एक भयानक कार्य है जो 0 से 1 तक एक दशमलव उत्पन्न करता है <.6। इसलिए, दूसरे शब्दों में, लगभग 60% मामलों में, मैं चाहता हूं कि आप उस उत्पाद को यहां कॉलम बी में लाएं, अन्यथा मुझे कुछ भी नहीं देना ""। मैं उसे नीचे कॉपी करूंगा। मेरे लिए जो करने जा रहा है वह उत्पादों की एक सूची है। कोई दोहराव कभी नहीं होगा। किसी भी दोहराव का कोई मौका नहीं है, और हर बार जब मैं एफ 9 दबाता हूं, तो मुझे उत्पादों की एक अलग सूची मिलती है, और, हाँ, ऐसा लगता है कि हम मिल रहे हैं, आप जानते हैं, हर बार उत्पादों की सही संख्या। (= IF (RAND) (<0.6, A1, ""))
अगला, चार्ट शीर्षक; वे हमें एक चार्ट के लिए दो अलग-अलग स्थानों की पेशकश करते हैं - चार्ट के ऊपर एक ठीक। तो, अब जब हमारे पास है, तो Office 365 में Excel में नया फ़ंक्शन TEXTJOIN है। मुझे यह पसंद है। परिसीमन एक होने जा रहा है; और फिर खाली को अनदेखा करें। इससे कोई फर्क नहीं पड़ता। मैं वास्तव में … हाँ, नहीं, यह वास्तव में यहाँ कोई फर्क नहीं पड़ता। यही महत्वपूर्ण बात है। हम खाली की अनदेखी कर रहे हैं। सच, और फिर यहाँ हमारे जैसे उत्पादों की सूची है। अच्छा जी। इसलिए, हमारे पास पहली पंक्ति के लिए हमारी उत्पाद सूची है, लेकिन मुझे इनमें से एक संपूर्ण समूह बनाने की आवश्यकता है, और यह वह जगह है जहां हम वास्तव में इस मुद्दे को प्राप्त करते हैं, जिस मुद्दे को मैं इस विशेष मामले में हल करने की कोशिश कर रहा था। (= TEXTJOIN (";", सही, बी 1: बी 12))
अब, अगर मैं सिर्फ उस सूत्र को कॉपी करूंगा, तो ठीक है, अगर मैं वह मूल सूत्र ले लूं और यहां आकर उसे संपादित कर दूं - तो चुनें: F4 दबाएं, यह सुनिश्चित करने के लिए कि मेरे पास एक पूर्ण संदर्भ है, और इसे कॉपी करें - आप 'मैं देखता हूँ कि मैं सभी तरह के समान आइटमों को समाप्त करता हूँ। यह बहुत दिलचस्प नकली डेटा नहीं है, है ना? तो, यह काम नहीं कर रहा है। मुझे क्या करने की आवश्यकता है मुझे इस सूत्र का परिणाम लेने और उनमें से एक पूरी गुच्छा बनाने की आवश्यकता है, ठीक है? (= TEXTJOIN (";", सही, $ B $ 1: $ B $ 12):
इसलिए, शुरू में मैंने ऐसा किया। मैंने कंट्रोल + सी किया था, और फिर मैं यहां आया और मैं विशेष मूल्यों को पेस्ट करने जा रहा हूं - या मुझे लगता है कि यह सिर्फ पेस्ट है - और इस तरह से ठीक है, ठीक है, और मेरे लिए क्या आकर्षक है - और मैं पहले पॉडकास्ट पर एक बार इस बारे में बात की गई और YouTube टिप्पणियों में सभी ने कहा, ठीक है, निश्चित रूप से यह काम करने जा रहा है; नहीं - मेरे लिए क्या आकर्षक है, मैंने सेल C14 की प्रतिलिपि बनाई है, और इसलिए आप सोचते होंगे कि, जब मैंने C14 की प्रतिलिपि बनाई, तो C14 से उस पाठ को क्लिपबोर्ड पर कॉपी किया गया होगा, लेकिन यह नहीं है। यह C14 की ओर इशारा कर रहा है, है ना? इसलिए, पहली बार जब मैंने पेस्ट किया, तो मुझे CHERRY, DATE, ELDERBERRY मिला, लेकिन अब आप देखते हैं कि C14, मार्चिंग चींटियाँ, बदलकर APPLE, CHERRY, FIG हो गई हैं, और इसलिए मैं यहाँ आने वाला हूँ और मैं ' मैं फिर से PASTE VALUES जा रहा हूं, और मैं हमेशा हैरान हूं कि यह नए मूल्य में बदल गया।
ठीक है, इसलिए, यदि मैं सिर्फ PASTE VALUES, PASTE VALUES, PASTE VALUES, PASTE VALUES कर सकता हूं, तो यह हर बार एक नया उत्तर उत्पन्न करेगा। इस बार जब मैं PASTE VALUES, APPLE, BANANA, DATE, FIG, ICEBERG, JACKFRUIT, लेकिन, देखो, यह माउस को हड़पने और यहाँ आकर PASTE को चुनने और VALUES को चुनने का झंझट है। इसलिए, मैं भयानक REDO फ़ंक्शन का उपयोग करने जा रहा हूं - UNDO, REDO - जो कि F4 है, इसलिए F4, नया मान चिपकाएँ। जब मैं F4 दबाता हूं, तो मुझे BANANA, DATE, ELDERBERRY, LIME मिल जाएगा। तो, यह सिर्फ सरल है। F4, DOWN ARROW, F4, DOWN ARROW, F4, DOWN ARROW, ठीक है, और जीवन महान है। वहाँ, मेरे पास लेख के लिए पर्याप्त नकली डेटा है, ठीक है, लेकिन यह भी एक परेशानी है, ठीक है?
तो, विधि जो मैंने अपने एक अच्छे दोस्त से सीखी जो अब मृत हो गई है - प्रोफेसर साइमन बेनिंगा ने मुझे यह सिखाया - अगर हमारे पास एक मॉडल है - और यह अनिवार्य रूप से एक मॉडल है - जो रैंड या रैंडबेटविन का उपयोग कर रहा है और एक जनरेट कर रहा है परिणाम, आप क्या कर सकते हैं आप उस परिणाम के कई संस्करण हो सकते हैं, ठीक है, और हमें अपने मॉडल के परिणाम के बाईं ओर सेल से शुरू करना होगा, उस सेल और सेल का चयन करें जिसमें आपका सूत्र है, और फिर हालाँकि, बहुत से आप चाहते हैं - मान लें कि मुझे इनमें से 100 या 132 की आवश्यकता है - बस कॉपी करें या सभी तरह से नीचे का चयन करें, और हम यहां डेटा टैब, डेटा टैब, व्हाट्स-आईएफ में आने वाले हैं विश्लेषण, डेटा टेबल, ठीक है?
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.
अच्छा हे। मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2155.xlsm
