अलीकडेच, माझ्या एका मित्राने दिलेल्या शब्दांचा संच असलेल्या सर्व संभाव्य वाक्यांशांच्या निर्मितीसाठी मदत करण्याची विनंती करून माझ्याशी संपर्क साधला. ऑनलाइन जाहिराती आणि एसइओ प्रमोशनसाठी कीवर्ड आणि वाक्प्रचारांची सूची संकलित करताना या प्रकारच्या समस्या उद्भवू शकतात, जेव्हा आपल्याला शोध क्वेरीमधील शब्दांच्या सर्व संभाव्य क्रमपरिवर्तनांमधून जाण्याची आवश्यकता असते:
गणितात या ऑपरेशनला म्हणतात कार्टेशियन उत्पादन. अधिकृत व्याख्या खालीलप्रमाणे आहे: संच A आणि B चे कार्टेशियन उत्पादन सर्व जोड्यांचा संच आहे, त्यातील पहिला घटक A संचाचा आहे आणि दुसरा घटक B संचाचा आहे. शिवाय, संचांचे घटक दोन्ही असू शकतात. संख्या आणि मजकूर.
मानवी भाषेत अनुवादित, याचा अर्थ असा की जर सेट A मध्ये आपल्याकडे, उदाहरणार्थ, “पांढरा” आणि “लाल” आणि B “BMW” आणि “Mercedes” असे शब्द असतील, तर या दोन संचांच्या कार्टेशियन उत्पादनानंतर आपण get on the output हा वाक्यांशांच्या सर्व संभाव्य रूपांचा संच आहे, दोन्ही सूचीच्या शब्दांनी बनलेला आहे:
- पांढरा bmw
- लाल बीएमडब्ल्यू
- पांढरी मर्सिडीज
- लाल मर्सिडीज
… म्हणजे आपल्याला जे हवे आहे. एक्सेलमध्ये हे कार्य सोडवण्याचे दोन मार्ग पाहू.
पद्धत 1. सूत्रे
चला सूत्रांसह प्रारंभ करूया. समजू की प्रारंभिक डेटा म्हणून आमच्याकडे अनुक्रमे A, B आणि C स्तंभांमध्ये मूळ शब्दांच्या तीन सूची आहेत आणि प्रत्येक यादीतील घटकांची संख्या बदलू शकते:
प्रथम, निर्देशांकांसह तीन स्तंभ बनवू, म्हणजे प्रत्येक यादीतील शब्दांची क्रमिक संख्या सर्व संभाव्य संयोजनांमध्ये. युनिट्सची पहिली पंक्ती (E2:G2) व्यक्तिचलितपणे एंटर केली जाईल आणि उर्वरितसाठी आम्ही खालील सूत्र वापरू:
येथे तर्क सोपे आहे: जर वरच्या मागील सेलमधील निर्देशांक आधीच सूचीच्या शेवटी पोहोचला असेल, म्हणजे फंक्शनद्वारे गणना केलेल्या सूचीमधील घटकांच्या संख्येइतके असेल. COUNT (COUNTA), नंतर आम्ही क्रमांकन पुन्हा सुरू करतो. अन्यथा, आम्ही निर्देशांक 1 ने वाढवतो. डॉलर चिन्हे ($) सह श्रेणीच्या चपखल फिक्सिंगकडे विशेष लक्ष द्या जेणेकरून तुम्ही सूत्र खाली आणि उजवीकडे कॉपी करू शकता.
आता आमच्याकडे प्रत्येक सूचीमधून आवश्यक असलेल्या शब्दांची क्रमिक संख्या आहे, आम्ही फंक्शन वापरून स्वतः शब्द काढू शकतो. INDEX (INDEX) तीन स्वतंत्र स्तंभांमध्ये:
जर तुम्ही तुमच्या कामात हे कार्य पाहिले नसेल, तर मी तुम्हाला त्याचा किमान तिरपे अभ्यास करण्याचा सल्ला देतो - हे बर्याच परिस्थितींमध्ये मदत करते आणि कमी (आणि आणखीही!) व्हीपीआर (VLOOKUP).
बरं, त्यानंतर, हे फक्त एकत्रीकरण चिन्ह (&) वापरून परिणामी तुकड्यांना रेषेनुसार चिकटविणे बाकी आहे:
… किंवा (तुमच्याकडे Excel ची नवीनतम आवृत्ती असल्यास) सुलभ कार्यासह एकत्र (TEXTJOIN), जे दिलेल्या सेपरेटर कॅरेक्टर (स्पेस) द्वारे निर्दिष्ट सेलची संपूर्ण सामग्री चिकटवू शकते:
पद्धत 2. पॉवर क्वेरीद्वारे
पॉवर क्वेरी हे मायक्रोसॉफ्ट एक्सेलसाठी एक शक्तिशाली अॅड-इन आहे जे दोन मुख्य कार्ये करते: 1. जवळजवळ कोणत्याही बाह्य स्रोतावरून डेटा एक्सेलमध्ये लोड करणे आणि 2. लोड केलेल्या टेबलचे सर्व प्रकारचे परिवर्तन. पॉवर क्वेरी आधीच एक्सेल 2016-2019 मध्ये तयार केली गेली आहे आणि एक्सेल 2010-2013 साठी ते स्वतंत्र अॅड-इन म्हणून स्थापित केले आहे (तुम्ही Microsoft च्या अधिकृत वेबसाइटवरून विनामूल्य डाउनलोड करू शकता). जर तुम्ही अजून तुमच्या कामात Power Query वापरणे सुरू केले नसेल, तर त्याबद्दल विचार करण्याची वेळ आली आहे, कारण वर वर्णन केल्याप्रमाणे परिवर्तन तेथे सहज आणि नैसर्गिकरित्या, फक्त दोन हालचालींमध्ये केले जाते.
प्रथम, पॉवर क्वेरीमध्ये स्वतंत्र क्वेरी म्हणून स्त्रोत सूची लोड करूया. हे करण्यासाठी, प्रत्येक टेबलसाठी, खालील चरणे करा:
- चला एका बटणासह सारण्यांना “स्मार्ट” मध्ये बदलू या सारणी म्हणून स्वरूपित करा टॅब होम पेज (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित) किंवा कीबोर्ड शॉर्टकट Ctrl+T. प्रत्येक टेबलला आपोआप एक नाव दिले जाईल तक्ता 1,2,3…, जे, तथापि, टॅबवर इच्छित असल्यास बदलले जाऊ शकते रचनाकार (डिझाइन).
- टेबलमध्ये सक्रिय सेल सेट केल्यावर, बटण दाबा टेबलवरून (टेबलवरून) टॅब डेटा (तारीख) किंवा टॅबवर उर्जा प्रश्न (जर तुम्ही ते एक्सेल 2010-2013 साठी वेगळे अॅड-इन म्हणून स्थापित केले असेल).
- उघडणाऱ्या क्वेरी एडिटर विंडोमध्ये, कमांड निवडा मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि लोड करा… (होम — बंद करा आणि लोड करा — बंद करा आणि लोड करा..) आणि नंतर पर्याय फक्त एक कनेक्शन तयार करा (केवळ कनेक्शन तयार करा). हे लोड केलेले टेबल मेमरीमध्ये ठेवेल आणि भविष्यात त्यात प्रवेश करण्यास अनुमती देईल.
आपण सर्वकाही योग्यरित्या केल्यास, नंतर उजव्या पॅनेलमधील आउटपुट मोडमध्ये तीन विनंत्या असाव्यात फक्त कनेक्शन आमच्या टेबल नावांसह:
आता पहिल्या क्वेरीवर उजवे-क्लिक करा आणि कमांड निवडा दुवा (संदर्भ)त्याची अपडेट करण्यायोग्य प्रत बनवण्यासाठी आणि नंतर कमांडद्वारे डेटामध्ये अतिरिक्त कॉलम जोडा स्तंभ जोडणे ž – सानुकूल स्तंभ (स्तंभ जोडा -ž सानुकूल स्तंभ). फॉर्म्युला इनपुट विंडोमध्ये, नवीन स्तंभाचे नाव (उदाहरणार्थ, Fragment2) आणि सूत्र म्हणून एक अत्यंत साधी अभिव्यक्ती प्रविष्ट करा:
= तक्ता 2
… म्हणजे, दुसऱ्या शब्दांत, दुसऱ्या क्वेरीचे नाव:
वर क्लिक केल्यानंतर OK आम्हाला एक नवीन कॉलम दिसेल, ज्याच्या प्रत्येक सेलमध्ये दुसर्‍या टेबलमधील वाक्ये असलेली एक नेस्टेड टेबल असेल (तुम्ही शब्दाच्या पुढील सेलच्या पार्श्वभूमीत क्लिक केल्यास या टेबलमधील मजकूर पाहू शकता. टेबल):
परिणामी स्तंभाच्या शीर्षलेखातील दुहेरी बाणांसह बटण वापरून या नेस्टेड सारण्यांमधील सर्व सामग्री विस्तृत करणे आणि अनचेक करणे बाकी आहे. मूळ स्तंभाचे नाव उपसर्ग म्हणून वापरा (मूळ स्तंभाचे नाव उपसर्ग म्हणून वापरा):
... आणि आम्हाला पहिल्या दोन संचांमधून घटकांचे सर्व संभाव्य संयोजन मिळतात:
पुढे, सर्वकाही समान आहे. सूत्रासह दुसरा गणना केलेला स्तंभ जोडा:
= तक्ता 3
…, आणि नंतर नेस्टेड टेबल्स पुन्हा विस्तृत करा - आणि आता आमच्याकडे अनुक्रमे तीन संचांमधून शब्दांना परवानगी देण्यासाठी सर्व संभाव्य पर्याय आहेत:
धरून डावीकडून उजवीकडे सर्व तीन स्तंभ निवडणे बाकी आहे Ctrl, आणि कमांड वापरून रिक्त स्थानांद्वारे विभक्त केलेली त्यांची सामग्री एकत्र करा स्तंभ विलीन करा (स्तंभ विलीन करा) टॅब वरून परिवर्तन (परिवर्तन):
परिणामी परिणाम आधीपासून परिचित कमांडसह शीटवर परत अनलोड केले जाऊ शकतात मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि लोड करा… (होम — बंद करा आणि लोड करा — बंद करा आणि लोड करा..):
जर भविष्यात आमच्या स्त्रोत सारण्यांमध्ये तुकड्यांसह काहीतरी बदलले तर, परिणामी टेबलवर उजवे-क्लिक करून आणि कमांड निवडून व्युत्पन्न केलेली क्वेरी अद्यतनित करणे पुरेसे आहे. अपडेट आणि सेव्ह करा (रिफ्रेश) किंवा कीबोर्ड शॉर्टकट दाबून Ctrl+alt+F5.
- पॉवर क्वेरी, पॉवर पिव्होट, पॉवर मॅप आणि पॉवर बीआय काय आहे आणि त्यांना एक्सेल वापरकर्त्याची आवश्यकता का आहे
- पॉवर क्वेरीमध्ये गॅंट चार्ट तयार करणे
- INDEX फंक्शन वापरण्याचे 5 मार्ग