विषय-सूची
समस्या का निरूपण
इनपुट डेटा के रूप में, हमारे पास एक एक्सेल फाइल है, जहां शीट्स में से एक में निम्नलिखित फॉर्म के बिक्री डेटा के साथ कई टेबल हैं:
ध्यान दें कि:
- विभिन्न आकारों की तालिकाएँ और उत्पादों और क्षेत्रों के विभिन्न सेटों के साथ पंक्तियों और स्तंभों में बिना किसी छँटाई के।
- तालिकाओं के बीच रिक्त रेखाएँ डाली जा सकती हैं।
- तालिकाओं की संख्या कोई भी हो सकती है।
दो महत्वपूर्ण धारणाएँ। यह मान लिया है कि:
- प्रत्येक तालिका के ऊपर, पहले कॉलम में, उस प्रबंधक का नाम होता है जिसकी बिक्री तालिका दर्शाती है (इवानोव, पेट्रोव, सिदोरोव, आदि)
- सभी तालिकाओं में वस्तुओं और क्षेत्रों के नाम एक ही तरह से लिखे गए हैं - एक मामले की सटीकता के साथ।
अंतिम लक्ष्य सभी तालिकाओं से एक फ्लैट सामान्यीकृत तालिका में डेटा एकत्र करना है, जो बाद के विश्लेषण के लिए सुविधाजनक है और सारांश तैयार करना है, यानी इसमें:
चरण 1. फ़ाइल से कनेक्ट करें
आइए एक नई खाली एक्सेल फाइल बनाएं और इसे टैब पर चुनें जानकारी आदेश डेटा प्राप्त करें - फ़ाइल से - पुस्तक से (डेटा - फ़ाइल से - कार्यपुस्तिका से). बिक्री डेटा के साथ स्रोत फ़ाइल का स्थान निर्दिष्ट करें और फिर नेविगेटर विंडो में उस शीट का चयन करें जिसकी हमें आवश्यकता है और बटन पर क्लिक करें डेटा कनवर्ट करें (डेटा रूपांतरण):
परिणामस्वरूप, इसका सारा डेटा Power Query संपादक में लोड किया जाना चाहिए:
चरण 2. कचरा साफ करें
स्वचालित रूप से उत्पन्न चरणों को हटाएं संशोधित प्रकार (परिवर्तित प्रकार) и एलिवेटेड हेडर (प्रचारित शीर्षलेख) और फ़िल्टर का उपयोग करके रिक्त रेखाओं और कुल योग वाली रेखाओं से छुटकारा पाएं रिक्त и कुल पहले कॉलम द्वारा। नतीजतन, हमें निम्नलिखित चित्र मिलता है:
चरण 3. प्रबंधकों को जोड़ना
बाद में यह समझने के लिए कि किसकी बिक्री होती है, हमारी तालिका में एक कॉलम जोड़ना आवश्यक है, जहां प्रत्येक पंक्ति में एक समान उपनाम होगा। इसके लिए:
1. आइए कमांड का उपयोग करके लाइन नंबरों के साथ एक सहायक कॉलम जोड़ें कॉलम जोड़ें - इंडेक्स कॉलम - 0 . से (कॉलम जोड़ें - इंडेक्स कॉलम - 0 से).
2. कमांड के साथ सूत्र के साथ एक कॉलम जोड़ें एक कॉलम जोड़ना - कस्टम कॉलम (कॉलम जोड़ें — कस्टम कॉलम) और वहां निम्नलिखित निर्माण का परिचय दें:
इस सूत्र का तर्क सरल है - यदि पहले कॉलम में अगले सेल का मान "उत्पाद" है, तो इसका मतलब है कि हम एक नई तालिका की शुरुआत पर ठोकर खा चुके हैं, इसलिए हम पिछले सेल के मूल्य को प्रदर्शित करते हैं प्रबंधक का नाम। अन्यथा, हम कुछ भी प्रदर्शित नहीं करते हैं, अर्थात शून्य।
मूल नाम के साथ मूल सेल प्राप्त करने के लिए, हम पहले पिछले चरण से तालिका देखें #"सूचकांक जोड़ा गया", और फिर उस कॉलम का नाम निर्दिष्ट करें जिसकी हमें आवश्यकता है [स्तंभ1] वर्गाकार कोष्ठकों में और उस स्तंभ में घुंघराले कोष्ठक में कक्ष संख्या। सेल नंबर वर्तमान संख्या से एक कम होगा, जिसे हम कॉलम से लेते हैं सूची, क्रमशः।
3. यह खाली कोशिकाओं को भरने के लिए बनी हुई है रिक्त आदेश के साथ उच्च कोशिकाओं के नाम ट्रांसफॉर्म - फिल - डाउन (रूपांतरण - भरण - नीचे) और पहले कॉलम में अंतिम नामों के साथ सूचकांकों और पंक्तियों के साथ अब आवश्यक कॉलम को हटा दें। परिणामस्वरूप, हमें मिलता है:
चरण 4. प्रबंधकों द्वारा अलग-अलग तालिकाओं में समूहीकृत करना
अगला चरण प्रत्येक प्रबंधक के लिए पंक्तियों को अलग-अलग तालिकाओं में समूहित करना है। ऐसा करने के लिए, ट्रांसफॉर्मेशन टैब पर, ग्रुप बाय कमांड (ट्रांसफॉर्म - ग्रुप बाय) का उपयोग करें और खुलने वाली विंडो में, मैनेजर कॉलम और ऑपरेशन ऑल रो (सभी पंक्तियों) का चयन करें ताकि बिना किसी एग्रीगेटिंग फ़ंक्शन को लागू किए डेटा एकत्र किया जा सके। उन्हें (योग, औसत, आदि)। पी।):
नतीजतन, हमें प्रत्येक प्रबंधक के लिए अलग-अलग टेबल मिलते हैं:
चरण 5: नेस्टेड टेबल्स को रूपांतरित करें
अब हम टेबल देते हैं जो परिणामी कॉलम के प्रत्येक सेल में स्थित हैं सभी डेटा सभ्य आकार में।
सबसे पहले, उस कॉलम को हटा दें जिसकी अब प्रत्येक तालिका में आवश्यकता नहीं है प्रबंधक. हम फिर से उपयोग करते हैं कस्टम कॉलम टैब परिवर्तन (रूपांतरण - कस्टम कॉलम) और निम्न सूत्र:
फिर, एक और परिकलित कॉलम के साथ, हम प्रत्येक तालिका में पहली पंक्ति को शीर्षकों तक बढ़ाते हैं:
और अंत में, हम मुख्य परिवर्तन करते हैं - एम-फ़ंक्शन का उपयोग करके प्रत्येक तालिका को खोलना टेबल.अनपिवोटअन्य कॉलम:
हेडर से क्षेत्रों के नाम एक नए कॉलम में जाएंगे और हमें एक संकरा, लेकिन साथ ही, एक लंबी सामान्यीकृत तालिका मिलेगी। के साथ खाली सेल रिक्त नजरअंदाज कर दिया जाता है।
अनावश्यक मध्यवर्ती स्तंभों से छुटकारा पाने के लिए, हमारे पास है:
चरण 6 नेस्टेड तालिकाओं का विस्तार करें
यह कॉलम हेडर में डबल एरो वाले बटन का उपयोग करके सभी सामान्यीकृत नेस्टेड तालिकाओं को एक सूची में विस्तारित करने के लिए बनी हुई है:
... और अंत में हमें वही मिलता है जो हम चाहते थे:
आप परिणामी तालिका को कमांड का उपयोग करके एक्सेल में वापस निर्यात कर सकते हैं होम - बंद करें और लोड करें - बंद करें और लोड करें ... (होम - बंद करें और लोड करें - बंद करें और लोड करें ...).
- एकाधिक पुस्तकों से भिन्न शीर्षलेखों के साथ तालिकाएँ बनाएँ
- किसी दिए गए फ़ोल्डर में सभी फाइलों से डेटा एकत्र करना
- पुस्तक की सभी शीटों से डेटा को एक तालिका में एकत्रित करना