एकाधिक पुस्तकों से भिन्न शीर्षलेखों के साथ तालिकाएँ बनाएँ

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

हमारे पास एक फ़ोल्डर में कई फाइलें हैं (हमारे उदाहरण में - 4 टुकड़े, सामान्य स्थिति में - जितनी आप चाहें) रिपोर्ट:

अंदर, ये फ़ाइलें इस तरह दिखती हैं:

जिसमें:

• हमें जिस डेटा शीट की आवश्यकता होती है उसे हमेशा कहा जाता है तस्वीरें, लेकिन कार्यपुस्तिका में कहीं भी हो सकता है।
  
• चादर से परे तस्वीरें प्रत्येक पुस्तक में अन्य पत्रक हो सकते हैं।
• डेटा वाली तालिकाओं में पंक्तियों की एक अलग संख्या होती है और कार्यपत्रक पर एक अलग पंक्ति से शुरू हो सकती है।
• अलग-अलग तालिकाओं में समान स्तंभों के नाम भिन्न हो सकते हैं (उदाहरण के लिए, मात्रा = मात्रा = मात्रा).
• तालिकाओं में स्तंभों को एक अलग क्रम में व्यवस्थित किया जा सकता है।

कार्य: शीट से सभी फाइलों से बिक्री डेटा एकत्र करें तस्वीरें बाद में एक सारांश या उस पर कोई अन्य विश्लेषण बनाने के लिए एक सामान्य तालिका में।

चरण 1. कॉलम नामों की एक निर्देशिका तैयार करना

पहली बात यह है कि कॉलम नामों और उनकी सही व्याख्या के लिए सभी संभावित विकल्पों के साथ एक संदर्भ पुस्तक तैयार करें:

हम टैब पर फ़ॉर्मैट टेबल बटन का उपयोग करके इस सूची को एक गतिशील "स्मार्ट" तालिका में परिवर्तित करते हैं होम (होम - तालिका के रूप में प्रारूपित करें) या कीबोर्ड शॉर्टकट कंट्रोल+T और इसे कमांड के साथ पावर क्वेरी में लोड करें डेटा - टेबल/रेंज से (डेटा - टेबल/रेंज से). एक्सेल के हाल के संस्करणों में, इसका नाम बदलकर . कर दिया गया है पत्तों के साथ (शीट से).

Power Query क्वेरी संपादक विंडो में, हम परंपरागत रूप से चरण को हटाते हैं परिवर्तित प्रकार और इसके बजाय बटन पर क्लिक करके एक नया चरण जोड़ें f_xफॉर्मूला बार में (यदि यह दिखाई नहीं दे रहा है, तो आप इसे टैब पर सक्षम कर सकते हैं समीक्षा) और वहां अंतर्निहित Power Query भाषा M में सूत्र दर्ज करें:

= टेबल। टोरो (स्रोत)

यह आदेश पिछले चरण में लोड किए गए को परिवर्तित कर देगा स्रोत नेस्टेड सूचियों (सूची) वाली सूची में संदर्भ तालिका, जिनमें से प्रत्येक, बदले में, मूल्यों की एक जोड़ी है यह बन गया एक पंक्ति से:

हमें इस प्रकार के डेटा की थोड़ी देर बाद आवश्यकता होगी, जब सभी लोडेड टेबल से बड़े पैमाने पर हेडर का नाम बदलना होगा।

रूपांतरण पूरा करने के बाद, आदेशों का चयन करें होम - बंद करें और लोड करें - बंद करें और लोड करें ... और आयात का प्रकार बस एक कनेक्शन बनाएं (होम - बंद करें और लोड करें - बंद करें और लोड करें ... - केवल कनेक्शन बनाएं) और एक्सेल पर वापस जाएं।

चरण 2। हम सभी फाइलों से सब कुछ इस प्रकार लोड करते हैं

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

पूर्वावलोकन विंडो में, क्लिक करें में कनवर्ट करना (रूपांतरण) or परिवर्तन (संपादित):

और फिर सभी डाउनलोड की गई फ़ाइलों की सामग्री का विस्तार करें (बाइनरी) कॉलम हेडिंग में डबल एरो वाला बटन सामग्री:

पहली फ़ाइल के उदाहरण पर पावर क्वेरी (वोस्तोक.xlsx) हमसे उस शीट का नाम पूछेगा जिसे हम प्रत्येक कार्यपुस्तिका से लेना चाहते हैं - चुनें तस्वीरें और ओके दबाएं:

उसके बाद (वास्तव में), कई घटनाएं होंगी जो उपयोगकर्ता के लिए स्पष्ट नहीं हैं, जिसके परिणाम बाएं पैनल में स्पष्ट रूप से दिखाई दे रहे हैं:

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

   

4. अतिरिक्त कॉलम हटा दिए जाते हैं।
5. नेस्टेड टेबल की सामग्री का विस्तार किया जाता है (चरण विस्तारित तालिका स्तंभ) - और हम सभी पुस्तकों से डेटा संग्रह के अंतिम परिणाम देखते हैं:

   
   

चरण 3. सैंडिंग

पिछला स्क्रीनशॉट स्पष्ट रूप से दिखाता है कि सीधी असेंबली "जैसी है" खराब गुणवत्ता की निकली:

• कॉलम उलटे हैं।
• कई अतिरिक्त लाइनें (खाली और न केवल)।
• टेबल हेडर को हेडर के रूप में नहीं माना जाता है और डेटा के साथ मिलाया जाता है।
  

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

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

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

= तालिका। नाम बदलें कॉलम (# "एलिवेटेड हेडर", हेडर, मिसिंगफिल्ड। अनदेखा करें)

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

दरअसल, बस इतना ही।

अनुरोध पर लौटना रिपोर्ट हम एक पूरी तरह से अलग तस्वीर देखेंगे - पिछले वाले की तुलना में बहुत अच्छी:

• Power Query, Power Pivot, Power BI क्या है और एक्सेल उपयोगकर्ता को उनकी आवश्यकता क्यों है
  
• किसी दिए गए फ़ोल्डर में सभी फाइलों से डेटा एकत्र करना
• पुस्तक की सभी शीटों से डेटा को एक तालिका में एकत्रित करना