नैशविले के कैली एक टिकट स्प्रेडशीट पर काम कर रहे हैं। प्रत्येक घटना के लिए, वह एक टिकट योजना चुनती है। टिकट की योजना घटना के लिए 4 से 16 टिकट प्रकारों में से कहीं भी संकेत कर सकती है। Kaley एक फॉर्मूला चाहता है जो लुकअप टेबल पर जाए और * सभी * मैच लौटाए, नई पंक्तियों को उपयुक्त रूप में सम्मिलित किया।
जबकि मेरे पास एक VLOOKUP नहीं है जो इसे हल कर सकता है, Excel 2016 में बनाए गए नए पावर क्वेरी उपकरण इसे हल कर सकते हैं।
ध्यान दें
यदि आपके पास Excel 2010 या Excel 2013 का Windows संस्करण है, तो आप Microsoft से मुफ्त में पावर क्वेरी डाउनलोड कर सकते हैं। दुर्भाग्य से, पॉवर क्वेरी अभी तक एंड्रॉइड के लिए एक्सेल, आईओएस के लिए एक्सेल या मैक के लिए एक्सेल के लिए उपलब्ध नहीं है।
लक्ष्य को स्पष्ट करने के लिए: माइक मैककैन और मैकेनिक टिकट योजना सी के साथ एलन थियेटर में दिखाई दे रहे हैं। लुकअप टेबल में चार मिलान पंक्तियाँ हैं, केली चार पंक्तियाँ चाहती हैं जो कहती हैं माइक मैककैन और मैकेनिक्स, प्रत्येक एक अलग मैच के साथ लुकअप टेबल।
मूल तालिका में एक सेल का चयन करें। तालिका के रूप में उस डेटा को चिह्नित करने के लिए Ctrl + T दबाएं। तालिका उपकरण टैब पर, तालिका 1 से तालिका का नाम बदलें। लुकअप तालिका के लिए दोहराएं, इसे टिकट कहते हैं।
शो तालिका में एक सेल का चयन करें। डेटा टैब से, टेबल / रेंज से चुनें।
पावर क्वेरी संपादक खुलने के बाद, बंद और लोड ड्रॉप-डाउन खोलें और बंद करें और लोड करें चुनें …।
आयात डेटा संवाद में, केवल एक कनेक्शन बनाएँ चुनें।
टिकट टेबल पर जाएं। केवल टिकट के लिए कनेक्शन बनाएं के चरणों को दोहराएं। आपको क्वेरी फलक में दोनों कनेक्शन देखने चाहिए:
किसी भी रिक्त कक्ष का चयन करें। डेटा चुनें, डेटा प्राप्त करें, क्वेरीज़ मिलाएं, मर्ज करें।
मर्ज संवाद में छह चरण हैं। तीसरा और चौथा मुझे सहज नहीं लगता।
- शीर्ष ड्रॉप-डाउन से शो चुनें
- दूसरी ड्रॉप-डाउन से टिकट चुनें।
- शो तालिका में विदेशी कुंजी के रूप में उस कॉलम का चयन करने के लिए शीर्ष में टिकट योजना के शीर्षक पर क्लिक करें।
- लुकिंग टेबल के प्रमुख क्षेत्र के रूप में उस कॉलम को चुनने के लिए नीचे दिए गए टिकट योजना के शीर्षक पर क्लिक करें।
- ज्वाइन टाइप को खोलें और इनर (केवल मैचिंग रो) चुनें।
- ओके पर क्लिक करें
परिणाम शुरू में निराशाजनक हैं। आप तालिका 1 से सभी फ़ील्ड और तालिका, तालिका, तालिका कहने वाले कॉलम को देखते हैं।
टिकट कॉलम के शीर्ष पर विस्तृत करें आइकन पर क्लिक करें।
आपके पास पहले से ही उस फील्ड का अनसिलेक्ट टिकट प्लान है। जब तक आप मूल नाम को उपसर्ग के रूप में अनचेक नहीं करते हैं, तब तक शेष फ़ील्ड को टिकट। टिकट प्रकार कहा जाएगा।
सफलता! प्रत्येक शो के लिए प्रत्येक पंक्ति कई पंक्तियों में फट जाती है।
मैं डेटा की छँटाई से विशेष रूप से खुश नहीं हूँ। दिनांक के अनुसार क्रमबद्ध करने से टिकट के प्रकार एक विषम तरीके से क्रमबद्ध हो जाते हैं।
वीडियो देखेंा
आज के मामले में, वीडियो लेख लिखे जाने के बाद शूट किया गया था। मेरा सुझाव है कि सॉर्ट क्रम को नियंत्रित करने के लिए टिकट प्रकारों के लिए एक अनुक्रम कॉलम जोड़ना है।
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2204: सभी वीएलबुकअप लौटाएं।
अरे, नेटकास्ट में वापस स्वागत है, मैं बिल जेलन हूं। नैशविले म्यूजिक सिटी से आज का सवाल। मैं नैशविले में वहाँ गया था, कोई एक टिकट प्रणाली में लोडिंग टिकटों को शेड्यूल करने के लिए जिम्मेदार है और इसलिए यहां हमारे पास क्या है: हमारे पास घटनाओं की एक सूची है - आगामी कार्यक्रम-- हमारे पास तारीख, स्थान और एक टिकट योजना है। तो, जैसे, भले ही कुछ पैलेस में आयोजित किया गया हो, अलग-अलग टिकट योजनाएं हो सकती हैं - जैसे, शायद मंजिल को कॉन्फ़िगर किया गया है, आप जानते हैं, सीटों के साथ या शायद यह केवल एक स्थायी कमरा है, है ना?
इसलिए, किस प्रकार की टिकट योजना के आधार पर, आपको यहां लुकअप टेबल पर आना होगा और सभी मेल खाने वाली घटनाओं को खोजना होगा, और अनिवार्य रूप से हम वह करने जा रहे हैं जिसे मैं VLOOKUP विस्फोट कहता हूं। इसलिए अगर हन्नाह सी में कुछ है, तो वे हन्नाह सी के लिए नीचे जाने वाले हैं और अगर हन्नाह सी में 1, 2, 3, 4, 5, 6-- 7 आइटम हैं, तो हम सात पंक्तियों को वापस करने के लिए - जिसका अर्थ है कि आपको छह और पंक्तियाँ सम्मिलित करनी होंगी और उस डेटा को कॉपी करना होगा। ठीक है।
अब, हम एक VLOOKUP के साथ ऐसा नहीं करने जा रहे हैं, लेकिन आपको यह अवधारणा मिल गई है - हम एक VLOOKUP कर रहे हैं और हम सभी उत्तर नई पंक्तियों के रूप में दे रहे हैं। ठीक है, इसलिए, मैं इन दोनों तालिकाओं को लेने जा रहा हूं और उन्हें Ctrl + T के साथ वास्तविक तालिका में बनाता हूं। पहले लोगों ने तालिका 1 को कहा - भयानक नाम, चलो इस ईवेंट या शो को कॉल करते हैं, आइए इसे शो कहते हैं, जैसे- और दूसरा, अब, हे, यहाँ मैंने जो सीखा है, क्योंकि मैंने यह अभ्यास किया है - हमें करना होगा एक अनुक्रम क्षेत्र यहाँ। इसलिए = ROW (A1), डबल-क्लिक करें, और इसे नीचे कॉपी करें और फिर विशेष मान कॉपी और पेस्ट करें। ठीक है। अब हम बनाते हैं कि तालिका में बना देगा - Ctrl + T, और हम उस एक टिकट को कॉल करेंगे।
ठीक है। इसलिए हमारे पास शो हैं, हमारे पास टिकट हैं। मैं डेटा टैब पर जा रहा हूँ, और मैं यहाँ शो चीज़ में हूँ, मैं यह कहना चाहता हूँ कि मैं अपना डेटा किसी टेबल या रेंज से प्राप्त करना चाहता हूँ - यह पावर क्वेरी है, वैसे। यदि आप Excel 2010 या 2013 में वापस आ गए हैं, तो आप इसे Microsoft से मुफ्त में डाउनलोड कर सकते हैं, Power Query टूल डाउनलोड कर सकते हैं। यदि आप Mac या iOS या Android पर हैं, तो क्षमा करें, आपके लिए कोई पावर क्वेरी नहीं है। ठीक है, इसलिए किसी तालिका या श्रेणी से … किसी ऐसे व्यक्ति को खोजें, जिसके पास कोई ऐसा दोस्त हो, जिसके पास एक - विंडोज पीसी हो और उसने इसे स्थापित किया हो। ठीक है। यहाँ एक तालिका है, हम इसके लिए एक चीज़ नहीं करने जा रहे हैं, बस बंद करें और लोड करें, बंद करें और लोड करें, और फिर "केवल कनेक्शन बनाएँ" कहें, एकदम सही। हम अपनी दूसरी तालिका में यहां आने वाले हैं: डेटा प्राप्त करें, किसी तालिका या श्रेणी से, हम इस एक के लिए कुछ भी नहीं कर रहे हैं, बंद करें और लोड करें,बंद करें और लोड करें, "केवल कनेक्शन बनाएँ", ठीक है। तो अब हमारे पास क्या है, क्या हमारा पहला टेबल से कनेक्शन है और दूसरा टेबल से कनेक्शन है। हम इन दोनों को मर्ज नहीं करने जा रहे हैं, जो अनिवार्य रूप से VLOOKUP, या डेटाबेस जॉइंट करने जैसा है, मुझे लगता है कि वास्तव में यह कैसा है। क्वेरीज़ मिलाएं, हम मर्ज करने जा रहे हैं। ठीक है।
अब, सात चीजें जो आपको इस संवाद बॉक्स में करनी हैं - और यह थोड़ा भ्रमित करने वाला है - हम पहली तालिका के रूप में शो का चयन करने जा रहे हैं; दूसरी तालिका के रूप में टिकट चुनें; चुनें कि उनके पास कौन सा क्षेत्र है, और यह कई क्षेत्र हो सकते हैं - आप नियंत्रण-क्लिक कर सकते हैं-- लेकिन इस मामले में केवल एक टिकट योजना है; और फिर टिकट योजना; और फिर हम "केवल मिलान पंक्तियों" के साथ जुड़ें प्रकार को इनर में बदलने जा रहे हैं। ठीक है। अब, आप ठीक पर क्लिक करते हैं और आपको लगता है कि आपकी पूरी समस्या हल होने वाली है, लेकिन आपको सिर्फ इसलिए कुचल दिया जाता है क्योंकि यहां ए - से सभी डेटा हैं, उन्होंने कोई भी नई पंक्तियाँ नहीं डाली हैं - और यहाँ पर, बस टिकट नामक एक उबाऊ बेवकूफ क्षेत्र जिसमें सिर्फ टेबल, टेबल, टेबल, हाह है।
लेकिन, शुक्र है कि इसके शीर्ष पर एक विस्तार आइकन है, और हम इसका विस्तार करने जा रहे हैं- मुझे एक योजना लेने की आवश्यकता नहीं है, मेरे पास पहले से ही है- टिकट प्रकार और अनुक्रम। मैं नहीं चाहता कि इसे टिकट कहा जाए। टिकट टाइप, जो कि पावर क्वेरी करना चाहता है - इसलिए मैं इस बॉक्स को अनचेक करता हूं। ठीक है। अभी हमारे पास डेटा की 17 पंक्तियाँ हैं; जब मैं ठीक क्लिक करता हूँ, BAM! वहां विस्फोट हुआ है। तो, माइकल सीली और स्टारलाइटर के शो अलग-अलग टिकट के सभी प्रकारों के साथ दिखाई देते हैं। ठीक है, और देखें कि ये टिकट प्रकार अनुक्रम में दिखाई देते हैं, यह बहुत अच्छा है। लेकिन माइकल सीली अगला शो नहीं है, अगला शो 5 जून को है। इसलिए जब मैं कोशिश करता हूँ और दिनांक के अनुसार इसे सॉर्ट करता हूँ - तो यह मुझे पागल कर देता है, मैं इसे समझा नहीं सकता। दिनांक, और माइक मैन और मैकेनिक्स के आधार पर क्रमबद्ध 65 तक आते हैं, लेकिन फिर टिकट सभी खराब हो जाते हैं। वे'गलत अनुक्रम पर फिर से, और यही कारण है कि मुझे यह अनुक्रम करना पड़ा- इस तरह महसूस होता है। मैं अनुक्रम द्वारा क्रमबद्ध कर सकता हूं। तो अब, 6, 5, सुंदर, और फिर उसके भीतर, टिकट सही हैं। और वास्तव में, इस बिंदु पर, हमें अब इस कॉलम की आवश्यकता नहीं है। इसलिए मैं राइट-क्लिक और हटा सकता हूं, और फिर बंद करें और लोड कर सकता हूं - इस बार मैं वास्तव में बंद और लोड करने जा रहा हूं, बंद और लोड नहीं कर रहा हूं - और हमारे पास हमारा परिणाम है। ठीक है।
तो हम इस पूरी बड़ी सूची की घटनाओं की एक सूची से चले गए, लेकिन यहां का भयानक हिस्सा है: मैंने इसे खराब कर दिया, माइक मैन और मैकेनिक्स पैलेस बी नहीं है, इसका पैलेस सी। इसलिए मैं शीर्ष दाईं ओर मूल पर वापस आता हूं किताब के बारे में अधिक जानकारी के लिए -हैंड कॉर्नर।
ठीक है। इस कड़ी में विषय: नैशविले में केली को सभी मैचों को वापस करने के लिए एक VLOOKUP करने की आवश्यकता है, आमतौर पर नई पंक्तियों को सम्मिलित करते हुए। और यह एक टिकटिंग डेटाबेस है, ठीक है? इसलिए मैं इसे एक VLOOKUP धमाका कहने जा रहा हूं क्योंकि प्रत्येक शो 16 पंक्तियों में फट जाएगा। हम इसे हल करने के लिए पावर क्वेरी का उपयोग करने जा रहे हैं, और मुझे पता चला है कि तिथि गलत अनुक्रम पर प्रदर्शित होने वाली है जब तक हम टिकट के प्रकार में अनुक्रम फ़ील्ड नहीं जोड़ते हैं। दोनों सेट को Ctrl + T के साथ एक तालिका में बनाएं; शो और टिकट होने के लिए उन्हें नामांकित करें; और फिर प्रत्येक तालिका से, डेटा, टेबल से, बंद करें और लोड करें, केवल एक कनेक्शन बनाने के लिए; दूसरी तालिका के लिए दोहराएं; फिर डेटा, डेटा प्राप्त करें, क्वेरीज़ मिलाएं, मर्ज करें; और फिर वह डायलॉग बॉक्स, यह मेरे लिए बहुत भ्रामक है - ईवेंट चुनें, टिकट चुनें, टिकट पर क्लिक करें दोनों में टाइप करें, संयुक्त को एक आंतरिक जोड़ में बदलें,ठीक पर क्लिक करें, और फिर आपको वह भयानक निराशाजनक परिणाम मिलता है जहां यह सिर्फ एक स्तंभ है जो तालिका, तालिका, तालिका, तालिका कहता है; उस के शीर्ष पर विस्तृत करें आइकन पर क्लिक करें; टिकट अनुक्रम क्षेत्र चुनें; तालिका के नाम के साथ उपसर्ग न करें; और आप तिथि के अनुसार क्रमबद्ध कर सकते हैं, अनुक्रम द्वारा क्रमबद्ध कर सकते हैं; स्प्रेडशीट के लिए बंद करें और लोड करें। खूबसूरत बात यह है कि अगर अंतर्निहित डेटा बदल जाता है - बस ताज़ा करें और आपके पास आपके परिणाम हैं।
अब, हे, आज के वीडियो से उपयोग की गई कार्यपुस्तिका को डाउनलोड करने के लिए, YouTube विवरण में नीचे दिए गए URL पर जाएं। इसके अलावा आगामी सेमिनारों की एक सूची - मैं आपको मेरे एक लाइव पावर एक्सेल सेमिनार में देखना पसंद करूंगा।
मैं नैशविले में दिखाने और मुझे उस महान प्रश्न को देने के लिए कैली को धन्यवाद देना चाहता हूं। मैं आपको रोकना चाहता हूं। मैं आपको अगली बार एक और नेटकास्ट से देखूंगा।
एक्सेल फ़ाइल डाउनलोड करें
एक्सेल फ़ाइल को डाउनलोड करने के लिए: रिटर्न-ऑल-वोडअप्सएक्सएक्सएक्सएक्सएक्सएक्स
पावर क्वेरी मुझे विस्मित करती है। यह तीन-दिवसीय श्रृंखला का दूसरा है जहां जवाब पावर क्वेरी है:
- मंगलवार: दिनांक / समय के एक कॉलम को सिर्फ तारीख में बदलें
- आज: सभी VLOOKUPs लौटाएं
- गुरुवार: प्रत्येक 1100 वस्तुओं के लिए एक सर्वेक्षण बनाएं
मेरे पास उन चीजों की पूरी YouTube प्लेलिस्ट है, जिन्हें मैंने पावर क्वेरी के साथ हल किया था।
एक्सेल थॉट्स ऑफ द डे
मैंने अपने एक्सेल के बारे में सलाह के लिए अपने एक्सेल मास्टर दोस्तों से पूछा है। विचार करने के लिए आज का विचार:
"जब संदेह में, ROUND फ़ंक्शन का उपयोग करें!"
माइक गिरविन
