फ्लोरिडा का डेविड आज का सवाल पूछता है:
मेरे पास दो वर्कबुक हैं। कॉलम A में दोनों का डेटा समान है, लेकिन शेष कॉलम अलग हैं। मैं उन दो वर्कबुक को कैसे मर्ज कर सकता हूं?
मैंने डेविड से पूछा कि क्या यह संभव है कि एक वर्कबुक में दूसरे की तुलना में अधिक रिकॉर्ड हो। और जवाब है हाँ। मैंने डेविड से पूछा कि क्या कुंजी फ़ील्ड प्रत्येक फ़ाइल में केवल एक बार दिखाई देती है। इसका जवाब भी हां है। आज, मैं इसे पावर क्वेरी के साथ हल करूंगा। Power Query टूल डेटा टैब के गेट एंड ट्रांसफॉर्म अनुभाग में Excel 2016+ के विंडोज संस्करणों में पाए जाते हैं। यदि आपके पास Excel 2010 या Excel 2013 के विंडोज संस्करण हैं, तो आप उन संस्करणों के लिए पावर क्वेरी ऐड-इन डाउनलोड कर सकते हैं।
यहां डेविड की कार्यपुस्तिका है। इसमें उत्पाद और फिर डेटा के तीन कॉलम हैं।
यहाँ डेविड की कार्यपुस्तिका है 2. इसमें उत्पाद कोड और फिर अन्य कॉलम हैं। इस उदाहरण में, कार्यपुस्तिका 2 में अतिरिक्त उत्पाद हैं, लेकिन समाधान कार्य करेंगे यदि कार्यपुस्तिका में अतिरिक्त स्तंभ हैं।
यहाँ कदम हैं:
-
कार्यपुस्तिका से डेटा, डेटा प्राप्त करें, फ़ाइल से चुनें:
फ़ाइल से डेटा लोड करें - पहली कार्यपुस्तिका पर ब्राउज़ करें और ठीक पर क्लिक करें
- नेविगेटर संवाद में, बाईं ओर की वर्कशीट चुनें। (भले ही केवल एक कार्यपत्रक हो, आपको इसे चुनना होगा।) आपको दाईं ओर डेटा दिखाई देगा।
- नेविगेटर संवाद में, लोड ड्रॉपडाउन खोलें और लोड टू चुनें…
- केवल एक कनेक्शन बनाएँ चुनें और ओके दबाएं।
-
दूसरी कार्यपुस्तिका के लिए चरण 1-5 दोहराएं।
कार्यपुस्तिका के लिए एक कनेक्शन बनाएँ यदि आपने दोनों कार्यपुस्तिकाएँ कर ली हैं, तो आपको अपनी एक्सेल स्क्रीन के दाईं ओर स्थित क्वेरीज़ एंड कनेक्शंस पैनल पर दो कनेक्शन देखने चाहिए।
दोनों कार्यपुस्तिकाओं के लिए कनेक्शन कार्यपुस्तिकाओं को मर्ज करने के चरणों के साथ जारी रखें:
-
डेटा, डेटा प्राप्त करें, क्वेरीज़ मिलाएं, मर्ज करें।
अलग कॉलम के साथ दो प्रश्नों को मिलाएं - मर्ज डायल में शीर्ष ड्रॉप डाउन से, पहली क्वेरी चुनें।
- मर्ज संवाद में दूसरी ड्रॉप डाउन से, दूसरी क्वेरी चुनें।
- शीर्ष पूर्वावलोकन में उत्पाद शीर्षक पर क्लिक करें (यह कुंजी फ़ील्ड है। ध्यान दें कि आप Ctrl + क्लिक करके दो या अधिक महत्वपूर्ण फ़ील्ड का चयन कर सकते हैं)
- दूसरे पूर्वावलोकन में उत्पाद कोड शीर्षक पर क्लिक करें।
-
ज्वाइन टाइप को खोलें और पूरा आउटर चुनें (दोनों से सभी पंक्तियाँ)
चरण 8 - 12 यहाँ सचित्र है -
ओके पर क्लिक करें। डेटा पूर्वावलोकन अतिरिक्त पंक्तियों को नहीं दिखाता है और केवल अंतिम तालिका में "तालिका" को बार-बार दिखाता है।
यह आशाजनक नहीं लगता है - ध्यान दें कि डेविडट्वो के लिए हेडिंग में एक "विस्तार" आइकन है। उस आइकन पर क्लिक करें।
-
वैकल्पिक, लेकिन मैं हमेशा "उपसर्ग के रूप में मूल कॉलम नाम का उपयोग करें" को अचयनित करता हूं। ओके पर क्लिक करें।
फ़ील्ड को कार्यपुस्तिका 2 से विस्तृत करें परिणाम इस पूर्वावलोकन में दिखाए गए हैं:
कार्यपुस्तिका से सभी रिकॉर्ड - पावर क्वेरी में, होम, क्लोज एंड लोड का उपयोग करें।
यहां सुंदर विशेषता है: यदि कार्यपुस्तिका में अंतर्निहित डेटा बदल जाता है, तो आप परिणाम पुस्तिका में नए डेटा को खींचने के लिए ताज़ा करें आइकन पर क्लिक कर सकते हैं।
ध्यान दें
रिफ्रेश के लिए आइकन आमतौर पर छिपा होता है। आइकन प्रकट करने के लिए क्वेरी और कनेक्शंस फलक के बाएँ किनारे को बाईं ओर खींचें।
वीडियो देखेंा
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2216: कॉमन कॉलम पर आधारित दो वर्कबुक्स को मिलाएं।
अरे, नेटकास्ट में आपका स्वागत है, मैं बिल जेलन हूं। डेविड से आज का सवाल, जो आईआईए के स्पेस कोस्ट चैप्टर के लिए मेलबर्न, फ्लोरिडा में मेरे सेमिनार में थे।
डेविड के पास दो अलग-अलग कार्यपुस्तिकाएं हैं जहां दोनों के बीच कॉलम ए आम है। तो, यहाँ कार्यपुस्तिका 1, यहाँ कार्यपुस्तिका 2-- दोनों में उत्पाद कोड है। यह एक आइटम है कि पहले वाले के पास नहीं है, या इसके विपरीत, और डेविड सभी कॉलम को संयोजित करना चाहता है। तो, हमारे यहाँ तीन कॉलम हैं और यहाँ चार कॉलम हैं। यदि आप कार्यपुस्तिका डाउनलोड कर रहे हैं तो मैं इन दोनों को एक ही कार्यपुस्तिका में रखता हूँ। इनमें से प्रत्येक को लें, इसे अपनी कार्यपुस्तिका में ले जाएं और इसे सहेजें।
ठीक है, इन फ़ाइलों को संयोजित करने के लिए, हम पावर क्वेरी का उपयोग करने जा रहे हैं। Power Query का निर्माण Excel 2016 में किया गया है। यदि आप 10 या 13 के विंडोज संस्करण में हैं, तो आप Microsoft से बाहर जा सकते हैं और पावर क्वेरी डाउनलोड कर सकते हैं। आप एक खाली वर्कशीट से एक खाली वर्कशीट से शुरुआत कर सकते हैं। आप इस फ़ाइल को सहेजने जा रहे हैं- जैसे कि, आप जानते हैं, शायद कार्यपुस्तिका, संयुक्त फ़ाइलों के परिणामों को दिखाने के लिए .xlsx। ठीक है? और हम क्या करने जा रहे हैं, हम दो प्रश्न करने जा रहे हैं। हम कार्यपुस्तिका से डेटा, गेट डेटा, फ़ाइल से, और फिर हम पहली फ़ाइल चुनेंगे। पूर्वावलोकन में, उस शीट का चयन करें जिसमें आपका डेटा है, और हमें इस डेटा के लिए कुछ भी करने की आवश्यकता नहीं है। तो बस लोड बॉक्स खोलें और लोड टू चुनें, केवल कनेक्शन बनाएं, ठीक पर क्लिक करें। उत्तम। अब, हम उस दूसरी वस्तु के लिए दोहराने जा रहे हैं- डेटा, फाइल से,किसी कार्यपुस्तिका से, DavidTwo चुनें, शीट नाम चुनें, और फिर लोड, लोड टू, ओनली ए कनेक्शन बनाएँ। आप यहाँ इस पैनल में देखेंगे, हमारे पास दोनों कनेक्शन मौजूद हैं। ठीक है।
अब वास्तविक कार्य-- डेटा, गेट डेटा, कम्बाइन क्वैरिज, मर्ज, और फिर मर्ज डायल में, डेविडऑन, डेविडट्वो चुनें, और यह अगला चरण पूरी तरह से अनइंस्टिट्यूट है। तुम्हें यह करना ही है। कॉलम - कॉलम को आम में चुनें - ताकि उत्पाद और उत्पाद। ठीक है। और फिर, सम्मिलित प्रकार के साथ यहां बहुत सावधान रहें। मैं दोनों पंक्तियों को सभी से चाहता हूं क्योंकि एक अतिरिक्त पंक्ति हो सकती है और मुझे यह देखने की आवश्यकता है, और फिर हम ठीक पर क्लिक करते हैं। ठीक है। और यहाँ प्रारंभिक परिणाम है। ऐसा नहीं लगता कि यह काम किया है; ऐसा नहीं लगता है कि यह फ़ाइल 2 में थे अतिरिक्त आइटम जोड़े गए हैं। और हमारे पास यह कॉलम 5-- यह अभी शून्य है। मैं कॉलम 5 पर राइट क्लिक करने जा रहा हूं और कहता हूं, उस कॉलम को हटा दें। तो इस विस्तार आइकन को खोलें और उपसर्ग के रूप में मूल कॉलम नाम का उपयोग करें और BAM के लिए इस बॉक्स को अनचेक करें! यह काम करता है। तो अतिरिक्त आइटम जो फ़ाइल 2 में थे, वह फ़ाइल 1 में नहीं हैं,दिखाई देते हैं।
ठीक है। अब आज की फ़ाइल में, यह दिखता है कि यह उत्पाद कोड कॉलम इस उत्पाद कॉलम से बेहतर है, क्योंकि इसमें अतिरिक्त पंक्तियाँ हैं। लेकिन भविष्य में एक दिन हो सकता है जहां वर्कबुक 1 में ऐसी चीजें हैं जो वर्कबुक 2 में नहीं हैं। इसलिए मैं उन दोनों को वहां छोड़ने जा रहा हूं, और मैं किसी भी अशक्त से छुटकारा पाने के लिए नहीं जा रहा हूं, क्योंकि, भले ही तल पर यह पंक्ति पूरी तरह से अशक्त प्रतीत होती है, भविष्य में ऐसी स्थिति हो सकती है जहां हमारे यहाँ कुछ नल हैं क्योंकि कुछ गायब है। ठीक है? तो, अंत में, बंद करें और लोड करें, और हमारी सोलह पंक्तियाँ हैं।
अब, भविष्य में, चलो कहते हैं कि कुछ बदलता है। ठीक है, इसलिए हम उन दो फ़ाइलों में से एक पर वापस जाएंगे और मैं Apple के लिए कक्षा को 99 में बदल दूंगा, और चलो कुछ नया भी डालें और इस कार्यपुस्तिका को सहेजें। ठीक है। और फिर, अगर हम चाहते हैं कि हमारी मर्ज फ़ाइल अपडेट हो जाए, तो यहां पर आएं- अभी, बाहर देखें, जब आप पहली बार ऐसा करते हैं, तो आप रिफ्रेश आइकन नहीं देख सकते हैं - आपको इस बार को पकड़ना होगा और इसे ऊपर खींचना होगा । और हम रिफ्रेश करेंगे, और 17 पंक्तियों को लोड किया जाएगा, तरबूज दिखाई देता है, Apple 99-- में बदल जाता है - यह एक सुंदर चीज है। अब, हे, क्या आप पावर क्वेरी के बारे में सीखना चाहते हैं? केन पल्स और मिगुएल एस्कोबार की यह पुस्तक खरीदें, एम फॉर (डेटा) मोंकी है। मैं तुम्हें गति करने के लिए मिल जाएगा।
आज लपेटें: फ्लोरिडा के डेविड के पास दो कार्यपुस्तिकाएँ हैं जिन्हें वह संयोजित करना चाहता है; उन दोनों के कॉलम A में समान फ़ील्ड हैं, लेकिन अन्य कॉलम सभी अलग हैं; एक कार्यपुस्तिका में अतिरिक्त आइटम हो सकते हैं जो दूसरे में नहीं हैं और डेविड जो चाहते हैं; फ़ाइल में कोई डुप्लिकेट नहीं है; हम इसे हल करने के लिए पावर क्वेरी का उपयोग करने जा रहे हैं, इसलिए रिक्त वर्कशीट पर एक नई रिक्त कार्यपुस्तिका में प्रारंभ करें; आप तीन क्वेरी करने जा रहे हैं, पहले एक-- डेटा, फाइल, वर्कबुक से, और फिर केवल निर्मित कनेक्शन पर लोड करें; दूसरी कार्यपुस्तिका के लिए एक ही बात, और फिर डेटा, डेटा प्राप्त करें, मर्ज करें, दो कनेक्शन का चयन करें, उस कॉलम का चयन करें जो दोनों में सामान्य है - मेरे मामले में, Product-- और फिर Join Type से, आप पूरा जुड़ना चाहते हैं फ़ाइल 1 से सभी, फ़ाइल 2 से सभी। और फिर सुंदर बात यह है कि अंतर्निहित डेटा बदलता है,आप केवल क्वेरी को ताज़ा कर सकते हैं।
आज के वीडियो से कार्यपुस्तिका डाउनलोड करने के लिए, YouTube विवरण में URL पर जाएं।
खैर, हे, मैं अपने सेमिनार के लिए दिखाने के लिए डेविड की तरह चाहता हूं, मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। मैं आपको अगली बार एक और नेटकास्ट से देखूंगा।
एक्सेल फ़ाइल डाउनलोड करें
एक्सेल फाइल को डाउनलोड करने के लिए: कॉम्बिनेशन-बेस्ड-ऑन-कॉमन-कॉलम.xlsx
एक्सेल में पावर क्वेरी एक अद्भुत उपकरण है।
एक्सेल थॉट्स ऑफ द डे
मैंने अपने एक्सेल के बारे में सलाह के लिए अपने एक्सेल मास्टर दोस्तों से पूछा है। विचार करने के लिए आज का विचार:
"जब आप किसी फ़ंक्शन में रेंज या मैट्रिक्स पढ़ते हैं तो हमेशा F4 दबाएं"
तनजा कुहन
