हाल ही में, मेरे एक मित्र ने मुझे दिए गए शब्दों के एक सेट से मिलकर सभी संभावित वाक्यांशों की पीढ़ी में मदद करने के अनुरोध के साथ मुझसे संपर्क किया। ऑनलाइन विज्ञापन और एसईओ प्रचार के लिए कीवर्ड और वाक्यांशों की सूची संकलित करते समय इस तरह की समस्याएं उत्पन्न हो सकती हैं, जब आपको किसी खोज क्वेरी में शब्दों के सभी संभावित क्रमपरिवर्तन से गुजरना पड़ता है:
गणित में, इस ऑपरेशन को कहा जाता है कार्तीय गुणन. आधिकारिक परिभाषा इस प्रकार है: सेट ए और बी का कार्टेशियन उत्पाद सभी जोड़ों का सेट है, जिनमें से पहला घटक सेट ए से संबंधित है, और दूसरा घटक सेट बी से संबंधित है। इसके अलावा, सेट के तत्व दोनों हो सकते हैं संख्या और पाठ।
मानव भाषा में अनुवादित, इसका अर्थ है कि यदि सेट ए में हमारे पास, उदाहरण के लिए, "सफेद" और "लाल" शब्द हैं, और सेट बी "बीएमडब्ल्यू" और "मर्सिडीज" में हैं, तो इन दो सेटों के कार्टेशियन उत्पाद के बाद हम आउटपुट पर प्राप्त करें दोनों सूचियों के शब्दों से बने वाक्यांशों के सभी संभावित रूपों का सेट है:
- सफेद बीएमडब्ल्यू
- लाल बीएमडब्ल्यू
- सफेद मर्सिडीज
- लाल मर्सिडीज
... यानी बस हमें क्या चाहिए। आइए एक्सेल में इस कार्य को हल करने के कुछ तरीकों को देखें।
विधि 1. सूत्र
आइए सूत्रों से शुरू करते हैं। आइए मान लें कि प्रारंभिक डेटा के रूप में हमारे पास क्रमशः कॉलम ए, बी और सी में मूल शब्दों की तीन सूचियां हैं, और प्रत्येक सूची में तत्वों की संख्या भिन्न हो सकती है:
सबसे पहले, आइए इंडेक्स के साथ तीन कॉलम बनाएं, यानी सभी संभावित संयोजनों में प्रत्येक सूची से शब्दों की क्रमिक संख्या। इकाइयों की पहली पंक्ति (E2:G2) मैन्युअल रूप से दर्ज की जाएगी, और बाकी के लिए हम निम्नलिखित सूत्र का उपयोग करेंगे:
यहां तर्क सरल है: यदि पिछले बेहतर सेल में सूचकांक पहले ही सूची के अंत तक पहुंच गया है, यानी फ़ंक्शन द्वारा गणना की गई सूची में तत्वों की संख्या के बराबर है COUNT (काउंटा), फिर हम नंबरिंग को पुनः आरंभ करते हैं। अन्यथा, हम सूचकांक को 1 से बढ़ा देते हैं। डॉलर चिह्नों ($) के साथ श्रेणियों के चतुर निर्धारण पर विशेष ध्यान दें ताकि आप सूत्र को नीचे और दाईं ओर कॉपी कर सकें।
अब जब हमारे पास प्रत्येक सूची से आवश्यक शब्दों की क्रम संख्या है, तो हम फ़ंक्शन का उपयोग करके स्वयं शब्दों को निकाल सकते हैं सूचकांक (अनुक्रमणिका) तीन अलग-अलग कॉलम में:
यदि आप पहले अपने काम में इस समारोह में नहीं आए हैं, तो मैं आपको कम से कम तिरछे तरीके से इसका अध्ययन करने की दृढ़ता से सलाह देता हूं - यह कई स्थितियों में मदद करता है और उपयोगी नहीं है (और भी अधिक!) VPR (VLOOKUP).
खैर, उसके बाद, यह केवल परिणामी अंशों को पंक्तिबद्ध करने के लिए संघनन प्रतीक (&) का उपयोग करके गोंद करने के लिए रहता है:
... या (यदि आपके पास एक्सेल का नवीनतम संस्करण है) आसान फ़ंक्शन के साथ जोड़ना (टेक्स्टजॉइन), जो किसी दिए गए विभाजक वर्ण (स्थान) के माध्यम से निर्दिष्ट कक्षों की संपूर्ण सामग्री को गोंद कर सकता है:
विधि 2. पावर क्वेरी के माध्यम से
Power Query Microsoft Excel के लिए एक शक्तिशाली ऐड-इन है जो दो मुख्य कार्य करता है: 1. लगभग किसी भी बाहरी स्रोत से Excel में डेटा लोड करना, और 2. लोड की गई तालिकाओं के सभी प्रकार के परिवर्तन। Power Query पहले से ही Excel 2016-2019 में निर्मित है, और Excel 2010-2013 के लिए इसे एक अलग ऐड-इन के रूप में स्थापित किया गया है (आप इसे Microsoft की आधिकारिक वेबसाइट से मुफ्त में डाउनलोड कर सकते हैं)। यदि आपने अभी तक अपने काम में Power Query का उपयोग करना शुरू नहीं किया है, तो इसके बारे में सोचने का समय आ गया है, क्योंकि ऊपर वर्णित जैसे परिवर्तन वहां आसानी से और स्वाभाविक रूप से, केवल एक-दो आंदोलनों में किए जाते हैं।
सबसे पहले, पावर क्वेरी में स्रोत सूचियों को अलग-अलग क्वेरी के रूप में लोड करते हैं। ऐसा करने के लिए, प्रत्येक तालिका के लिए, निम्न चरणों का पालन करें:
- आइए एक बटन के साथ तालिकाओं को "स्मार्ट" में बदल दें तालिका के रूप में प्रारूपित करें टैब होम (होम - तालिका के रूप में प्रारूपित करें) या कीबोर्ड शॉर्टकट कंट्रोल+T. प्रत्येक तालिका को स्वचालित रूप से एक नाम दिया जाएगा तालिका1,2,3…, जो, हालांकि, टैब पर वांछित होने पर बदला जा सकता है निर्माता (डिज़ाइन).
- तालिका में सक्रिय सेल सेट करने के बाद, बटन दबाएं मेज से (तालिका से) टैब जानकारी (तारीख) या टैब पर पावर क्वेरी (यदि आपने इसे Excel 2010-2013 के लिए एक अलग ऐड-इन के रूप में स्थापित किया है)।
- खुलने वाली क्वेरी संपादक विंडो में, कमांड का चयन करें होम - बंद करें और लोड करें - बंद करें और लोड करें ... (होम - बंद करें और लोड करें - बंद करें और लोड करें ..) और फिर विकल्प बस एक कनेक्शन बनाएं (केवल कनेक्शन बनाएं). यह लोड की गई तालिका को स्मृति में छोड़ देगा और इसे भविष्य में एक्सेस करने की अनुमति देगा।
यदि आप सब कुछ सही ढंग से करते हैं, तो दाहिने पैनल में आउटपुट मोड में तीन अनुरोध होना चाहिए केवल कनेक्शन हमारे टेबल नामों के साथ:
अब पहली क्वेरी पर राइट क्लिक करें और कमांड चुनें संपर्क (संदर्भ)इसकी एक अद्यतन योग्य प्रतिलिपि बनाने के लिए, और फिर कमांड के माध्यम से डेटा में एक अतिरिक्त कॉलम जोड़ें एक कॉलम जोड़ना ž – कस्टम कॉलम (कॉलम जोड़ें -ž कस्टम कॉलम). सूत्र इनपुट विंडो में, नए कॉलम का नाम दर्ज करें (उदाहरण के लिए, Fragment2) और सूत्र के रूप में एक अत्यंत सरल व्यंजक:
=तालिका2
... यानी, दूसरे शब्दों में, दूसरी क्वेरी का नाम:
क्लिक करने के बाद OK हम एक नया कॉलम देखेंगे, जिसमें प्रत्येक सेल में दूसरी तालिका के वाक्यांशों के साथ एक नेस्टेड तालिका होगी (यदि आप शब्द के बगल में सेल की पृष्ठभूमि में क्लिक करते हैं तो आप इन तालिकाओं की सामग्री देख सकते हैं) तालिका):
यह परिणामी कॉलम के हेडर में डबल एरो वाले बटन का उपयोग करके इन नेस्टेड टेबल की सभी सामग्री का विस्तार करने और अनचेक करने के लिए बनी हुई है मूल स्तंभ नाम को उपसर्ग के रूप में प्रयोग करें (उपसर्ग के रूप में मूल स्तंभ नाम का प्रयोग करें):
... और हमें पहले दो सेटों से तत्वों के सभी संभावित संयोजन मिलते हैं:
इसके अलावा, सब कुछ समान है। सूत्र के साथ एक और परिकलित कॉलम जोड़ें:
=तालिका3
..., और फिर नेस्टेड तालिकाओं का फिर से विस्तार करें - और अब हमारे पास पहले से ही तीन सेटों से शब्दों के क्रमपरिवर्तन के लिए सभी संभावित विकल्प हैं:
यह बाएं से दाएं सभी तीन स्तंभों का चयन करने के लिए बनी हुई है कंट्रोल, और कमांड का उपयोग करके रिक्त स्थान से अलग उनकी सामग्री को संयोजित करें कॉलम मर्ज करें (कॉलम मर्ज करें) टैब से परिवर्तन (रूपांतरण):
परिणामी परिणाम पहले से ही परिचित कमांड के साथ शीट पर वापस उतारे जा सकते हैं होम - बंद करें और लोड करें - बंद करें और लोड करें ... (होम - बंद करें और लोड करें - बंद करें और लोड करें ..):
यदि भविष्य में हमारे स्रोत तालिकाओं में टुकड़ों के साथ कुछ बदलता है, तो परिणामी तालिका पर राइट-क्लिक करके और कमांड का चयन करके उत्पन्न क्वेरी को अपडेट करना पर्याप्त होगा अपडेट करें और सहेजें (ताज़ा करें) या कीबोर्ड शॉर्टकट दबाकर कंट्रोल+ऑल्ट+F5.
- Power Query, Power Pivot, Power Map और Power BI क्या है और उन्हें Excel उपयोगकर्ता की आवश्यकता क्यों है?
- पावर क्वेरी में गैंट चार्ट बनाना
- INDEX फ़ंक्शन का उपयोग करने के 5 तरीके