Power Query के साथ विभिन्न Excel फ़ाइलों से तालिकाओं को असेंबल करना

समस्या का निरूपण

आइए एक बहुत ही मानक स्थितियों में से एक के लिए एक सुंदर समाधान देखें, जिसका सामना अधिकांश एक्सेल उपयोगकर्ता जल्दी या बाद में करते हैं: आपको बड़ी संख्या में फ़ाइलों से डेटा को एक अंतिम तालिका में जल्दी और स्वचालित रूप से एकत्र करने की आवश्यकता होती है। 

मान लीजिए कि हमारे पास निम्न फ़ोल्डर है, जिसमें शाखा शहरों के डेटा के साथ कई फाइलें हैं:

फाइलों की संख्या कोई मायने नहीं रखती और भविष्य में बदल सकती है। प्रत्येक फ़ाइल में एक शीट होती है जिसका नाम है बिक्रीजहां डेटा तालिका स्थित है:

तालिकाओं में पंक्तियों (आदेशों) की संख्या, निश्चित रूप से भिन्न होती है, लेकिन स्तंभों का सेट हर जगह मानक होता है।

कार्य: तालिकाओं में शहर की फ़ाइलों या पंक्तियों को जोड़ते या हटाते समय बाद में स्वचालित अद्यतन के साथ सभी फ़ाइलों से एक पुस्तक में डेटा एकत्र करना। अंतिम समेकित तालिका के अनुसार, फिर कोई भी रिपोर्ट, पिवट टेबल, फ़िल्टर-सॉर्ट डेटा इत्यादि बनाना संभव होगा। मुख्य बात यह है कि एकत्र करने में सक्षम होना।

हम हथियारों का चयन करते हैं

समाधान के लिए, हमें एक्सेल 2016 के नवीनतम संस्करण की आवश्यकता है (आवश्यक कार्यक्षमता पहले से ही डिफ़ॉल्ट रूप से इसमें निर्मित है) या एक्सेल 2010-2013 के पिछले संस्करणों में मुफ्त ऐड-इन स्थापित है पावर क्वेरी माइक्रोसॉफ्ट से (इसे यहां डाउनलोड करें)। Power Query बाहरी दुनिया से एक्सेल में डेटा लोड करने, फिर उसे अलग करने और संसाधित करने के लिए एक सुपर लचीला और सुपर शक्तिशाली उपकरण है। Power Query लगभग सभी मौजूदा डेटा स्रोतों का समर्थन करता है - टेक्स्ट फ़ाइलों से लेकर SQL और यहां तक ​​कि Facebook तक

यदि आपके पास एक्सेल 2013 या 2016 नहीं है, तो आप आगे नहीं पढ़ सकते (सिर्फ मजाक कर रहे हैं)। एक्सेल के पुराने संस्करणों में, ऐसा कार्य केवल विजुअल बेसिक (जो शुरुआती लोगों के लिए बहुत मुश्किल है) में मैक्रो प्रोग्रामिंग करके या नीरस मैन्युअल प्रतिलिपि (जिसमें लंबा समय लगता है और त्रुटियां उत्पन्न करता है) द्वारा पूरा किया जा सकता है।

चरण 1. एक फ़ाइल को एक नमूने के रूप में आयात करें

सबसे पहले, आइए एक उदाहरण के रूप में एक कार्यपुस्तिका से डेटा आयात करें, ताकि एक्सेल "विचार को उठाए"। ऐसा करने के लिए, एक नई रिक्त कार्यपुस्तिका बनाएँ और…

• यदि आपके पास एक्सेल 2016 है, तो टैब खोलें जानकारी और फिर क्वेरी बनाएँ - फ़ाइल से - पुस्तक से (डेटा - नई क्वेरी- फ़ाइल से - एक्सेल से)
• यदि आपके पास Excel 2010-2013 Power Query ऐड-इन स्थापित है, तो टैब खोलें पावर क्वेरी और उस पर चयन करें फ़ाइल से - पुस्तक से (फाइल से - एक्सेल से)

फिर, खुलने वाली विंडो में, रिपोर्ट के साथ हमारे फ़ोल्डर में जाएं और शहर की किसी भी फाइल का चयन करें (इससे कोई फर्क नहीं पड़ता, क्योंकि वे सभी विशिष्ट हैं)। कुछ सेकंड के बाद, नेविगेटर विंडो दिखाई देनी चाहिए, जहां आपको बाईं ओर उस शीट का चयन करना होगा जिसकी हमें आवश्यकता है (बिक्री), और इसकी सामग्री दाईं ओर प्रदर्शित होगी:

यदि आप इस विंडो के निचले दाएं कोने में स्थित बटन पर क्लिक करते हैं डाउनलोड (भार), तो तालिका को तुरंत अपने मूल रूप में शीट में आयात किया जाएगा। एकल फ़ाइल के लिए, यह अच्छा है, लेकिन हमें ऐसी कई फ़ाइलों को लोड करने की आवश्यकता है, इसलिए हम थोड़ा अलग तरीके से जाएंगे और बटन पर क्लिक करेंगे। सुधार (संपादित). उसके बाद, Power Query क्वेरी संपादक को पुस्तक से हमारे डेटा के साथ एक अलग विंडो में प्रदर्शित किया जाना चाहिए:

यह एक बहुत शक्तिशाली उपकरण है जो आपको उस तालिका को "समाप्त" करने की अनुमति देता है जिसकी हमें आवश्यकता है। यहां तक ​​कि इसके सभी कार्यों के एक सतही विवरण में लगभग सौ पृष्ठ लगेंगे, लेकिन, यदि बहुत संक्षेप में, इस विंडो का उपयोग करके आप यह कर सकते हैं:

• अनावश्यक डेटा, खाली लाइनों, त्रुटियों वाली पंक्तियों को फ़िल्टर करें
• डेटा को एक या अधिक कॉलम के आधार पर सॉर्ट करें
• दोहराव से छुटकारा
• स्टिकी टेक्स्ट को कॉलम से विभाजित करें (सीमांकक द्वारा, वर्णों की संख्या, आदि)
• पाठ को क्रम में रखें (अतिरिक्त रिक्त स्थान हटाएं, सही मामला, आदि)
• डेटा प्रकारों को हर संभव तरीके से परिवर्तित करें (संख्याओं जैसे टेक्स्ट को सामान्य संख्याओं में बदलें और इसके विपरीत)
• तालिकाओं को स्थानांतरित (घुमाएं) और दो-आयामी क्रॉस-टेबल को फ्लैट वाले में विस्तारित करें
• तालिका में अतिरिक्त कॉलम जोड़ें और Power Query में निर्मित M भाषा का उपयोग करके उनमें सूत्रों और कार्यों का उपयोग करें।
• ...

उदाहरण के लिए, आइए हमारी तालिका में महीने के टेक्स्ट नाम के साथ एक कॉलम जोड़ें, ताकि बाद में पिवट टेबल रिपोर्ट बनाना आसान हो जाए। ऐसा करने के लिए, कॉलम हेडिंग पर राइट-क्लिक करें डेटाऔर कमांड का चयन करें डुप्लीकेट कॉलम (डुप्लिकेट कॉलम), और फिर प्रकट होने वाले डुप्लिकेट कॉलम के शीर्षलेख पर राइट-क्लिक करें और कमांड चुनें रूपांतरण - माह - माह का नाम:

प्रत्येक पंक्ति के लिए महीने के टेक्स्ट नामों के साथ एक नया कॉलम बनाया जाना चाहिए। कॉलम शीर्षक पर डबल-क्लिक करके, आप इसका नाम बदल सकते हैं कॉपी तिथि अधिक आरामदायक महीना, उदा।

यदि कुछ कॉलम में प्रोग्राम डेटा प्रकार को सही ढंग से नहीं पहचानता है, तो आप प्रत्येक कॉलम के बाईं ओर प्रारूप आइकन पर क्लिक करके इसकी सहायता कर सकते हैं:

आप एक साधारण फ़िल्टर का उपयोग करके त्रुटियों वाली पंक्तियों या खाली लाइनों के साथ-साथ अनावश्यक प्रबंधकों या ग्राहकों को बाहर कर सकते हैं:

इसके अलावा, सभी प्रदर्शन किए गए परिवर्तन दाहिने पैनल में तय किए गए हैं, जहां उन्हें हमेशा वापस (क्रॉस) किया जा सकता है या उनके पैरामीटर (गियर) को बदल सकते हैं:

हल्का और सुरुचिपूर्ण, है ना?

चरण 2. आइए हमारे अनुरोध को एक फ़ंक्शन में बदलें

प्रत्येक आयातित पुस्तक के लिए किए गए सभी डेटा परिवर्तनों को बाद में दोहराने के लिए, हमें अपने बनाए गए अनुरोध को एक फ़ंक्शन में बदलने की आवश्यकता है, जिसे बाद में, हमारी सभी फाइलों पर लागू किया जाएगा। ऐसा करना वास्तव में बहुत सरल है।

क्वेरी एडिटर में, व्यू टैब पर जाएं और बटन पर क्लिक करें उन्नत संपादक (देखें - उन्नत संपादक). एक विंडो खुलनी चाहिए जहां हमारे सभी पिछले कार्यों को एम भाषा में कोड के रूप में लिखा जाएगा। कृपया ध्यान दें कि उदाहरण के लिए हमने जो फ़ाइल आयात की है उसका पथ कोड में हार्डकोड किया गया है:

आइए अब कुछ समायोजन करें:

उनका अर्थ सरल है: पहली पंक्ति (फ़ाइलपथ)=> हमारी प्रक्रिया को एक तर्क के साथ एक फ़ंक्शन में बदल देता है दस्तावेज पथ, और नीचे हम इस चर के मान के लिए निश्चित पथ बदलते हैं। 

सभी। पर क्लिक करें अंत और इसे देखना चाहिए:

डरो मत कि डेटा गायब हो गया है - वास्तव में, सब कुछ ठीक है, सब कुछ इस तरह दिखना चाहिए 🙂 हमने अपना कस्टम फ़ंक्शन सफलतापूर्वक बनाया है, जहां डेटा आयात करने और संसाधित करने के लिए संपूर्ण एल्गोरिदम को किसी विशिष्ट फ़ाइल से बंधे बिना याद किया जाता है . यह इसे और अधिक समझने योग्य नाम देना बाकी है (उदाहरण के लिए डेटा प्राप्त करें) क्षेत्र में दाईं ओर के पैनल में पहला नाम और आप काट सकते हैं होम - बंद करें और डाउनलोड करें (होम - बंद करें और लोड करें). कृपया ध्यान दें कि उदाहरण के लिए हमने जो फ़ाइल आयात की है उसका पथ कोड में हार्डकोड किया गया है। आप मुख्य Microsoft Excel विंडो पर वापस आ जाएंगे, लेकिन हमारे फ़ंक्शन के लिए बनाए गए कनेक्शन वाला एक पैनल दाईं ओर दिखाई देना चाहिए:

चरण 3. सभी फाइलों को एकत्रित करना

सबसे कठिन हिस्सा पीछे है, सुखद और आसान हिस्सा रहता है। टैब पर जाएं डेटा - क्वेरी बनाएं - फ़ाइल से - फ़ोल्डर से (डेटा — नई क्वेरी — फ़ाइल से — फ़ोल्डर से) या, यदि आपके पास एक्सेल 2010-2013 है, तो टैब के समान पावर क्वेरी. दिखाई देने वाली विंडो में, उस फ़ोल्डर को निर्दिष्ट करें जहां हमारी सभी स्रोत शहर फ़ाइलें स्थित हैं और क्लिक करें OK. अगले चरण में एक विंडो खुलनी चाहिए जहां इस फ़ोल्डर (और इसके सबफ़ोल्डर्स) में मिली सभी एक्सेल फाइलें और उनमें से प्रत्येक के लिए विवरण सूचीबद्ध होंगे:

क्लिक करें परिवर्तन (संपादित) और फिर से हम परिचित क्वेरी संपादक विंडो में आते हैं।

अब हमें अपने बनाए गए फ़ंक्शन के साथ अपनी तालिका में एक और कॉलम जोड़ने की आवश्यकता है, जो प्रत्येक फ़ाइल से डेटा को "खींच" देगा। ऐसा करने के लिए, टैब पर जाएं कॉलम जोड़ें - कस्टम कॉलम (कॉलम जोड़ें - कस्टम कॉलम जोड़ें) और दिखाई देने वाली विंडो में, हमारे फ़ंक्शन को दर्ज करें डेटा प्राप्त करें, इसके लिए तर्क के रूप में प्रत्येक फ़ाइल के लिए पूर्ण पथ निर्दिष्ट करना:

क्लिक करने के बाद OK बनाया गया कॉलम दाईं ओर हमारी तालिका में जोड़ा जाना चाहिए।

अब सभी अनावश्यक कॉलम हटा दें (जैसे एक्सेल में, राइट माउस बटन का उपयोग करके - हटाना), फ़ाइल नाम के साथ केवल जोड़े गए कॉलम और कॉलम को छोड़कर, क्योंकि यह नाम (अधिक सटीक रूप से, शहर) प्रत्येक पंक्ति के लिए कुल डेटा में उपयोगी होगा।

और अब "वाह पल" - हमारे फ़ंक्शन के साथ जोड़े गए कॉलम के ऊपरी दाएं कोने में अपने स्वयं के तीरों के साथ आइकन पर क्लिक करें:

... अनचेक करें मूल स्तंभ नाम को उपसर्ग के रूप में प्रयोग करें (उपसर्ग के रूप में मूल स्तंभ नाम का प्रयोग करें)और क्लिक करें OK. और हमारा फ़ंक्शन रिकॉर्ड किए गए एल्गोरिथम का पालन करते हुए और एक सामान्य तालिका में सब कुछ एकत्र करते हुए, प्रत्येक फ़ाइल से डेटा को लोड और संसाधित करेगा:

पूर्ण सुंदरता के लिए, आप फ़ाइल नामों के साथ पहले कॉलम से .xlsx एक्सटेंशन भी हटा सकते हैं - मानक प्रतिस्थापन द्वारा "कुछ भी नहीं" (कॉलम हेडर पर राइट-क्लिक करें - विकल्प) और इस कॉलम का नाम बदलें City. और तारीख के साथ कॉलम में डेटा फॉर्मेट को भी सही करें।

सभी! पर क्लिक करें होम - बंद करें और लोड करें (होम - बंद करें और लोड करें). सभी शहरों के लिए क्वेरी द्वारा एकत्र किए गए सभी डेटा को वर्तमान एक्सेल शीट पर "स्मार्ट टेबल" प्रारूप में अपलोड किया जाएगा:

बनाए गए कनेक्शन और हमारे असेंबली फ़ंक्शन को किसी भी तरह से अलग से सहेजने की आवश्यकता नहीं है - वे सामान्य तरीके से वर्तमान फ़ाइल के साथ सहेजे जाते हैं।

भविष्य में, फ़ोल्डर में किसी भी परिवर्तन (शहरों को जोड़ने या हटाने) या फाइलों में (लाइनों की संख्या में परिवर्तन) के साथ, यह सीधे टेबल पर या दाएं पैनल में क्वेरी पर राइट-क्लिक करने के लिए पर्याप्त होगा और चुनें आज्ञा अपडेट करें और सहेजें (ताज़ा करें) - पावर क्वेरी कुछ सेकंड में सभी डेटा को फिर से "पुनर्निर्माण" करेगी।

PS

संशोधन। जनवरी 2017 के अपडेट के बाद, Power Query ने सीखा कि Excel कार्यपुस्तिकाओं को स्वयं कैसे एकत्रित किया जाता है, अर्थात अब कोई अलग फ़ंक्शन बनाने की आवश्यकता नहीं है - यह स्वचालित रूप से होता है। इस प्रकार, इस लेख के दूसरे चरण की अब आवश्यकता नहीं है और पूरी प्रक्रिया काफ़ी सरल हो जाती है:

1. चुनें अनुरोध बनाएँ - फ़ाइल से - फ़ोल्डर से - फ़ोल्डर का चयन करें - ठीक
2. फाइलों की सूची दिखाई देने के बाद, दबाएं परिवर्तन
3. क्वेरी संपादक विंडो में, डबल तीर के साथ बाइनरी कॉलम का विस्तार करें और प्रत्येक फ़ाइल से लिए जाने वाले शीट नाम का चयन करें

और यह सबकुछ है! गाना!

• पिवट टेबल बनाने के लिए उपयुक्त एक फ्लैट में क्रॉसस्टैब का नया स्वरूप
• Power View में एनिमेटेड बबल चार्ट बनाना
• मैक्रो विभिन्न एक्सेल फाइलों से शीट्स को एक में इकट्ठा करने के लिए