पॉवर क्वेरीमधील एका शीटमधून मल्टीफॉर्मेट टेबल तयार करणे

समस्येचे सूत्रीकरण

इनपुट डेटा म्हणून, आमच्याकडे एक एक्सेल फाइल आहे, जिथे एका शीटमध्ये खालील फॉर्मच्या विक्री डेटासह अनेक सारण्या आहेत:

लक्षात ठेवा की:

• कोणत्याही क्रमवारी न करता पंक्ती आणि स्तंभांमध्ये विविध आकारांच्या आणि उत्पादनांच्या विविध संचांसह आणि प्रदेशांसह सारण्या.
• टेबल्समध्ये रिकाम्या ओळी घातल्या जाऊ शकतात.
• टेबलची संख्या कोणतीही असू शकते.
  

दोन महत्त्वाची गृहीतके. असे मानले जाते की:

• प्रत्येक सारणीच्या वर, पहिल्या स्तंभात, त्या व्यवस्थापकाचे नाव आहे ज्यांच्या विक्रीचे सारणी स्पष्ट करते (इव्हानोव्ह, पेट्रोव्ह, सिदोरोव्ह इ.)
  
• सर्व सारण्यांमध्ये वस्तू आणि प्रदेशांची नावे सारखीच लिहिली आहेत – केस अचूकतेसह.
  

सर्व सारण्यांमधून डेटा एका सपाट सामान्यीकृत सारणीमध्ये गोळा करणे हे अंतिम ध्येय आहे, त्यानंतरच्या विश्लेषणासाठी आणि सारांश तयार करण्यासाठी सोयीस्कर आहे, म्हणजे यामध्ये:

पायरी 1. फाइलशी कनेक्ट करा

चला नवीन रिकामी एक्सेल फाईल तयार करू आणि ती टॅबवर निवडा डेटा आदेश डेटा मिळवा - फाइलमधून - पुस्तकातून (डेटा — फाइलवरून — वर्कबुकमधून). विक्री डेटासह स्त्रोत फाइलचे स्थान निर्दिष्ट करा आणि नंतर नेव्हिगेटर विंडोमध्ये आम्हाला आवश्यक असलेली शीट निवडा आणि बटणावर क्लिक करा. डेटा रूपांतरित करा (डेटा ट्रान्सफॉर्म):

परिणामी, त्यातील सर्व डेटा पॉवर क्वेरी एडिटरमध्ये लोड केला जावा:

पायरी 2. कचरा साफ करा

स्वयंचलितपणे व्युत्पन्न केलेल्या पायऱ्या हटवा सुधारित प्रकार (बदललेला प्रकार) и उन्नत शीर्षलेख (प्रचारित शीर्षलेख) आणि फिल्टर वापरून बेरीजसह रिक्त रेषा आणि रेषा काढून टाका निरर्थक и एकूण पहिल्या स्तंभाद्वारे. परिणामी, आम्हाला खालील चित्र मिळते:

पायरी 3. व्यवस्थापक जोडणे

कोणाची विक्री कुठे आहे हे नंतर समजून घेण्यासाठी, आमच्या टेबलमध्ये एक स्तंभ जोडणे आवश्यक आहे, जिथे प्रत्येक पंक्तीमध्ये एक संबंधित आडनाव असेल. यासाठी:

1. कमांड वापरून लाइन क्रमांकांसह एक सहायक स्तंभ जोडू स्तंभ जोडा - अनुक्रमणिका स्तंभ - 0 पासून (स्तंभ जोडा — अनुक्रमणिका स्तंभ — ० पासून).

2. कमांडसह सूत्रासह एक स्तंभ जोडा एक स्तंभ जोडणे - सानुकूल स्तंभ (स्तंभ जोडा — सानुकूल स्तंभ) आणि तेथे खालील बांधकाम सादर करा:

या सूत्राचे तर्क सोपे आहे - जर पहिल्या स्तंभातील पुढील सेलचे मूल्य "उत्पादन" असेल, तर याचा अर्थ असा होतो की आम्ही नवीन सारणीच्या सुरूवातीस अडखळलो आहोत, म्हणून आम्ही मागील सेलचे मूल्य यासह प्रदर्शित करतो. व्यवस्थापकाचे नाव. अन्यथा, आम्ही काहीही प्रदर्शित करत नाही, म्हणजे शून्य.

आडनावासह मूळ सेल मिळविण्यासाठी, आम्ही प्रथम मागील चरणातील सारणीचा संदर्भ घेतो #"इंडेक्स जोडले", आणि नंतर आम्हाला आवश्यक असलेल्या स्तंभाचे नाव निर्दिष्ट करा [स्तंभ1] चौरस कंसात आणि त्या स्तंभातील सेल क्रमांक कुरळे कंसात. सेल नंबर सध्याच्या पेक्षा एक कमी असेल, जो आपण कॉलममधून घेतो निर्देशांक, अनुक्रमे.

3. हे रिक्त पेशी भरण्यासाठी राहते निरर्थक कमांडसह उच्च सेलमधील नावे रूपांतर - भरा - खाली (परिवर्तन — भरा — खाली) आणि पहिल्या स्तंभातील आडनावांसह निर्देशांक आणि पंक्ती असलेले यापुढे आवश्यक नसलेले स्तंभ हटवा. परिणामी, आम्हाला मिळते:

पायरी 4. व्यवस्थापकांद्वारे स्वतंत्र सारण्यांमध्ये गटबद्ध करणे

पुढील पायरी म्हणजे प्रत्येक व्यवस्थापकाच्या पंक्तींचे स्वतंत्र सारण्यांमध्ये गट करणे. हे करण्यासाठी, ट्रान्सफॉर्मेशन टॅबवर, ग्रुप बाय कमांड (ट्रान्सफॉर्म – ग्रुप बाई) वापरा आणि उघडणाऱ्या विंडोमध्ये, मॅनेजर कॉलम निवडा आणि ऑपरेशन ऑल रो (सर्व पंक्ती) निवडा जेणेकरून कोणतेही एकत्रित फंक्शन लागू न करता डेटा गोळा करा. ते (बेरीज, सरासरी इ.). P.):

परिणामी, आम्हाला प्रत्येक व्यवस्थापकासाठी स्वतंत्र टेबल मिळतात:

पायरी 5: नेस्टेड टेबल्सचे रुपांतर करा

आता आम्ही परिणामी स्तंभाच्या प्रत्येक सेलमध्ये असलेल्या टेबल्स देतो सर्व डेटा सभ्य आकारात.

प्रथम, प्रत्येक टेबलमध्ये यापुढे आवश्यक नसलेला स्तंभ हटवा व्यवस्थापक. आम्ही पुन्हा वापरतो सानुकूल स्तंभ टॅब परिवर्तन (परिवर्तन — सानुकूल स्तंभ) आणि खालील सूत्र:

नंतर, दुसर्‍या गणना केलेल्या स्तंभासह, आम्ही प्रत्येक सारणीतील पहिली पंक्ती शीर्षलेखांवर वाढवतो:

आणि शेवटी, आम्ही मुख्य परिवर्तन करतो – प्रत्येक टेबलला M-फंक्शन वापरून उलगडणे सारणी.अनपिव्होट इतर स्तंभ:

हेडरमधील प्रदेशांची नावे नवीन कॉलममध्ये जातील आणि आम्हाला एक अरुंद मिळेल, परंतु त्याच वेळी, एक लांब सामान्य सारणी मिळेल. सह रिक्त पेशी निरर्थक दुर्लक्ष केले जाते

अनावश्यक इंटरमीडिएट कॉलम्सपासून मुक्त होणे, आमच्याकडे आहे:

चरण 6 नेस्टेड टेबल्स विस्तृत करा

स्तंभ शीर्षलेखातील दुहेरी बाणांसह बटण वापरून सर्व सामान्यीकृत नेस्टेड सारण्या एकाच सूचीमध्ये विस्तृत करणे बाकी आहे:

... आणि शेवटी आम्हाला जे हवे होते ते मिळते:

कमांड वापरून तुम्ही परिणामी टेबल परत एक्सेलमध्ये एक्सपोर्ट करू शकता मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि लोड करा… (मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि त्यावर लोड करा...).

• एकाधिक पुस्तकांमधून भिन्न शीर्षलेखांसह टेबल तयार करा
• दिलेल्या फोल्डरमधील सर्व फाइल्समधून डेटा गोळा करणे
• पुस्तकाच्या सर्व शीट्समधील डेटा एका टेबलमध्ये गोळा करणे