क्वेंटिन से आज का सवाल जो मेरे अटलांटा पावर एक्सेल सेमिनार में था। क्वेंटिन को एक्सेल में 1000+ ग्राहकों में से प्रत्येक के लिए एक ही 7 सर्वेक्षण प्रश्न उत्पन्न करने हैं।
जैसा कि आप इस आंकड़े में देख सकते हैं, ग्राहक ए में हैं। दोहराए जाने वाले प्रश्न कॉलम डी में हैं।
आप इसे वीबीए या फ़ार्मुलों के साथ हल कर सकते हैं, लेकिन यह पावर क्वेरी सप्ताह है, इसलिए मैं पावर क्वेरी में एक शांत चाल का उपयोग करने जा रहा हूं।
यदि आप प्रत्येक सर्वेक्षण के बीच एक रिक्त पंक्ति चाहते हैं, तो अनुक्रम संख्या जोड़ें और अंतिम प्रश्न के बाद संख्या 7 जोड़ें।
दोनों डेटा सेट से Ctrl + T दबाएं। दूसरे डेटा सेट को नाम से याद रखें जिसे आप याद रख सकते हैं, जैसे प्रश्न या सर्वेक्षण।
दूसरे डेटा सेट से, डेटा का उपयोग करें, टेबल से।
पावर क्वेरी संपादक खुलता है। होम टैब से, क्लोज एंड लोड ड्रॉप-डाउन चुनें और क्लोज एंड लोड टू चुनें…। अगले संवाद में, केवल एक कनेक्शन बनाएँ चुनें।
अब आप Excel में वापस आ गए हैं। कॉलम ए डेटा, टेबल से ग्राहक तालिका में किसी भी सेल को चुनें। क्वेरी संपादक खुलने के बाद, रिबन में जोड़ें कॉलम टैब पर क्लिक करें और फिर कस्टम कॉलम चुनें। सूत्र =#"Questions"(# और उद्धरण चिह्नों सहित) है।
प्रत्येक पंक्ति में दोहराए गए मूल्य तालिका के साथ संपादक में एक नया कॉलम दिखाई देता है। कॉलम हेडर में विस्तार आइकन पर क्लिक करें।
तालिका में दोनों फ़ील्ड चुनें। होम टैब से, बंद करें और लोड करें चुनें।
1000+ ग्राहकों में से प्रत्येक के लिए दोहराए गए 7 प्रश्नों के साथ एक नई वर्कशीट दिखाई देगी।
वीडियो देखेंा
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट एपिसोड 2205 से एक्सेल सीखें: ऋण सर्वेक्षण विस्फोट।
अरे, नेटकास्ट में आपका स्वागत है, मैं बिल जेलन हूं। अब, कल के एपिसोड 2204 में, यह नैशविले से कायली था, जिसे कॉलम डी में यहां प्रत्येक आइटम के लिए एक VLOOKUP विस्फोट करना था - हमारे पास कॉलम जी में आइटमों का एक मिलान गुच्छा था और उन्हें विस्फोट करने की आवश्यकता थी। तो, यदि पैलेस सी में 8 आइटम थे, तो हमें 8 पंक्तियाँ मिलेंगी।
अब, आज, हमारे पास क्वेंटिन है। अब, क्वेंटिन अटलांटा में मेरे सेमिनार में था, लेकिन वह वास्तव में फ्लोरिडा से है, और क्वेंटिन के यहां लगभग 1000 ग्राहक हैं - अच्छी तरह से, कॉलम ए में 1000 से अधिक ग्राहक-- और प्रत्येक ग्राहक के लिए, उसे यह सर्वेक्षण बनाने की आवश्यकता है- - 1, 2, 3, 4, 5, 6 प्रश्नों का यह सर्वेक्षण। और मैं यहाँ क्या करने जा रहा हूँ, मैं एक अनुक्रम संख्या को 7 के माध्यम से संख्या 1 के साथ जोड़ने जा रहा हूं, इस तरह, मैं बीच में एक अच्छी रिक्त पंक्ति बना सकता हूं। मैं इन दोनों डेटा सेट को एक तालिका में बनाने जा रहा हूं; इसलिए, हम इन 7 पंक्तियों को इन 1000 ग्राहकों में से प्रत्येक के लिए विस्फोट करने की कोशिश कर रहे हैं। यही लक्ष्य है।
अब, मैं वीपीए के साथ ऐसा कर सकता हूं; मैं सूत्रों के साथ ऐसा कर सकता हूं; लेकिन यह "पॉवर क्वेरी वीक" की तरह है, हम इस पर दौड़ रहे हैं, यह एक पंक्ति में हमारा तीसरा पॉवर क्वेरी उदाहरण है, इसलिए मैं पॉवर क्वेरी का उपयोग करने जा रहा हूं। मैं इसे एक टेबल में छोड़ देता हूं। मैं इस तालिका 1 को नाम देने के लिए बहुत सावधान रहने वाला हूं। मैं इसे एक नाम देने जा रहा हूं। हम बाद में उस नाम का पुन: उपयोग करने जा रहे हैं, इसलिए मैं इसे प्रश्न - जैसे कहूंगा। और फिर यह तालिका 2 होगी, लेकिन मैं इसका नाम बदलकर ग्राहक बनने जा रहा हूं- इतना महत्वपूर्ण नहीं कि मैं इसका नाम बदल दूं क्योंकि यह दूसरा नाम है जिसके पास नाम होना है। इसलिए, हम इसे चुनने जा रहे हैं; डेटा; और हम टेबल / रेंज से कहने जा रहे हैं। Get और Transform Data-- इसे Power Query के रूप में जाना जाता है। यह Excel 2016 में बनाया गया है। यदि आपके पास Windows पर 2010 या 2013 है,मैक नहीं, आईओएस नहीं, एंड्रॉइड नहीं, आप Microsoft से मुफ्त में पावर क्वेरी डाउनलोड कर सकते हैं।
इसलिए, हम टेबल / रेंज से डेटा प्राप्त करने जा रहे हैं; यहाँ हमारी तालिका है - हम इसके लिए कुछ भी नहीं करने जा रहे हैं, बस बंद करें और लोड करें; बंद करें और लोड करें; केवल एक कनेक्शन बनाएँ; ठीक है, और देखें, उस क्वेरी का नाम प्रश्न है। यह यहाँ पर उसी नाम का उपयोग करता है। और फिर हम इस पर वापस आते हैं, और, डेटा; टेबल / रेंज से; इसलिए, हमारे 1000 या अधिक ग्राहकों की सूची है।
अरे अब, यहाँ मिगुएल एस्कोबार को चिल्लाया गया है, मेरे दोस्त, जो एम इज़ फॉर (दाता) मोंकी के सह-अधिकारी हैं। मैं वीडियो का एक लिंक डालूँगा - पावर क्वेरी के बारे में शानदार किताब - इससे मुझे मदद मिली। हम एक नए कस्टम कॉलम में डालने जा रहे हैं, और कस्टम कॉलम फॉर्मूला यहीं है: = # "क्वेरी का नाम"। मैंने कभी भी मिगुएल के बिना नहीं सोचा होगा, इसलिए मिगुएल के लिए धन्यवाद।
और जब मैं ठीक पर क्लिक करता हूं, तो हाँ, यह ऐसा नहीं लगता है कि यह काम कर रहा है - हमें बस टेबल, टेबल, टेबल मिलता है, लेकिन ठीक यही है जो हमने कल केली और टिकटिंग के साथ किया था। और मुझे बस इतना ही करना है, और मैं वास्तव में यह कहने जा रहा हूं कि मुझे शायद अनुक्रम की आवश्यकता नहीं है … ठीक है, चलो इसे केवल मामले में डालें। हम इसे देखने के बाद इसे बाहर निकाल सकते हैं। अभी, हमारे पास 1000 पंक्तियाँ हैं, और अब हमारे पास 7000 पंक्तियाँ हैं- सुंदर। अब मैं देख सकता हूं कि यह अनुक्रम में दिखाई दे रहा है, इसलिए मुझे इसकी आवश्यकता नहीं है। मैं ठीक क्लिक करूंगा और केवल एक कॉलम निकालूंगा। और फिर मैं घर कर सकता हूं; बंद करें और लोड करें; और BAM! - अब हमारे पास 6 प्रश्नों के साथ 7000 से अधिक पंक्तियाँ होनी चाहिए और हर ग्राहक के लिए एक रिक्त स्थान होना चाहिए। संगोष्ठी में क्वेंटिन उस एक से रोमांचित था। कूल, कूल ट्रिक-- VBA से बचा जाता है, इंडेक्स का उपयोग करके सूत्रों का एक पूरा गुच्छा बचता है,और चीजों की तरह - महान जाने के लिए रास्ता।
लेकिन, हे, आज, मैं तुम्हें एम (फॉर) डेटा के साथ भेज देता हूं। केन पल्स और मिगुएल एस्कोबार ने पावर क्वेरी के बारे में सबसे बड़ी पुस्तक लिखी। मुझे वह किताब बहुत पसंद है; 2 घंटे में आप उस पुस्तक के साथ एक समर्थक बन जाएंगे।
ठीक है, इसलिए, आज रैप-अप - क्वेंटिन को 1000 अलग-अलग ग्राहकों के लिए एक समान सर्वेक्षण उत्पन्न करने की आवश्यकता है। प्रत्येक ग्राहक के लिए 6 या 7 या 8 प्रश्न हैं। अब, हम VBA या मैक्रो के साथ ऐसा कर सकते हैं, लेकिन, जब से हम यहां पॉवर क्वेरी पर चलते हैं, पॉवर क्वेरी करते हैं। मैंने प्रश्नों में एक अतिरिक्त रिक्त प्रश्न जोड़ा; मैंने एक अनुक्रम संख्या जोड़ी, यह सुनिश्चित करने के लिए कि खाली वहां रहता है; ग्राहकों को एक तालिका में बनाएं; एक तालिका में प्रश्न बनाएं; यह वास्तव में महत्वपूर्ण है कि आप उन प्रश्नों को नाम दें जिन्हें आप याद रख सकते हैं - मैंने "प्रश्न" कहा है। Power Query, केवल एक कनेक्शन के रूप में प्रश्नों को जोड़ें; और फिर, जैसा कि आप ग्राहकों को पॉवर क्वेरी में जोड़ रहे हैं, एक नया कस्टम कॉलम बनाएँ जहाँ सूत्र है: # "पहले क्वेरी का नाम" और फिर पावर क्वेरी संपादक में उस कॉलम का विस्तार करें; बंद करे &स्प्रेडशीट पर वापस लोड करें, और आप कर रहे हैं। एक अद्भुत ट्रिक- मुझे पॉवर क्वेरी से प्यार है - एक्सेल में 20 साल में सबसे बड़ी बात।
मैं अपने सेमिनार में दिखाने के लिए क्वेंटिन को धन्यवाद देना चाहता हूं। वह मेरे सेमिनार से पहले कई बार गया है - महान व्यक्ति। मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।
एक्सेल फ़ाइल डाउनलोड करें
एक्सेल फ़ाइल डाउनलोड करने के लिए: ऋण-सर्वेक्षण-विस्फोट.एक्सएलएक्स
पावर क्वेरी मुझे विस्मित करती है। अधिक पावर क्वेरी जानने के लिए डेटा बंदर के लिए पुस्तक एम देखें।
एक्सेल थॉट्स ऑफ द डे
मैंने अपने एक्सेल के बारे में सलाह के लिए अपने एक्सेल मास्टर दोस्तों से पूछा है। विचार करने के लिए आज का विचार:
"आप इसे समझने के अलावा AGGREGATE के साथ कुछ भी कर सकते हैं।"
लियाम बाटिक
