एकाधिक पुस्तकांमधून भिन्न शीर्षलेखांसह टेबल तयार करा

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

आमच्याकडे एका फोल्डरमध्ये अनेक फाईल्स आहेत (आमच्या उदाहरणात - 4 तुकडे, सर्वसाधारण बाबतीत - तुम्हाला पाहिजे तितक्या) अहवाल:

आत, या फायली यासारख्या दिसतात:

ज्यात:

• आम्हाला आवश्यक असलेली डेटा शीट नेहमी कॉल केली जाते फोटो, परंतु वर्कबुकमध्ये कुठेही असू शकते.
  
• पत्रकाच्या पलीकडे फोटो प्रत्येक पुस्तकात इतर पत्रके असू शकतात.
• डेटा असलेल्या सारण्यांमध्ये वेगवेगळ्या पंक्ती असतात आणि वर्कशीटवर वेगळ्या पंक्तीने सुरू होऊ शकतात.
• वेगवेगळ्या सारण्यांमधील समान स्तंभांची नावे भिन्न असू शकतात (उदाहरणार्थ, मात्रा = मात्रा = प्रमाण).
• टेबलमधील स्तंभ वेगळ्या क्रमाने मांडले जाऊ शकतात.

कार्य: शीटमधील सर्व फायलींमधून विक्री डेटा गोळा करा फोटो त्यानंतर सारांश किंवा इतर कोणतेही विश्लेषण तयार करण्यासाठी एका सामान्य टेबलमध्ये.

पायरी 1. स्तंभ नावांची निर्देशिका तयार करणे

पहिली गोष्ट म्हणजे स्तंभांच्या नावांसाठी आणि त्यांच्या योग्य अर्थासाठी सर्व संभाव्य पर्यायांसह संदर्भ पुस्तक तयार करणे:

टॅबवरील फॉरमॅट अॅज टेबल बटण वापरून आम्ही ही यादी डायनॅमिक "स्मार्ट" टेबलमध्ये रूपांतरित करतो. होम पेज (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित) किंवा कीबोर्ड शॉर्टकट Ctrl+T आणि कमांडसह Power Query मध्ये लोड करा डेटा - सारणी/श्रेणीवरून (डेटा — सारणी/श्रेणीवरून). Excel च्या अलीकडील आवृत्त्यांमध्ये, त्याचे नाव बदलले गेले आहे पाने सह (पत्रकावरून).

पॉवर क्वेरी क्वेरी एडिटर विंडोमध्ये, आम्ही पारंपारिकपणे पायरी हटवतो बदललेला प्रकार आणि बटणावर क्लिक करून त्याऐवजी एक नवीन पायरी जोडा f_xफॉर्म्युला बारमध्ये (जर ते दृश्यमान नसेल, तर तुम्ही ते टॅबवर सक्षम करू शकता पुनरावलोकन) आणि तेथे बिल्ट-इन पॉवर क्वेरी भाषेत सूत्र प्रविष्ट करा:

= तक्ता. Torows(स्रोत)

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

सर्व लोड केलेल्या सारण्यांमधून हेडरचे मोठ्या प्रमाणात पुनर्नामित करताना आम्हाला थोड्या वेळाने या प्रकारच्या डेटाची आवश्यकता असेल.

रूपांतरण पूर्ण केल्यानंतर, आज्ञा निवडा मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि लोड करा… आणि आयात प्रकार फक्त एक कनेक्शन तयार करा (होम — बंद करा आणि लोड करा — बंद करा आणि त्यावर लोड करा... — फक्त कनेक्शन तयार करा) आणि Excel वर परत जा.

पायरी 2. आम्ही सर्व फाईल्समधून सर्वकाही जसे आहे तसे लोड करतो

आता फोल्डरमधून आमच्या सर्व फाईल्सची सामग्री लोड करूया - सध्या, जसे आहे. संघ निवडत आहे डेटा - डेटा मिळवा - फाइलमधून - फोल्डरमधून (डेटा — डेटा मिळवा — फाइलमधून — फोल्डरमधून) आणि नंतर फोल्डर जिथे आमची स्त्रोत पुस्तके आहेत.

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

आणि नंतर सर्व डाउनलोड केलेल्या फाइल्सची सामग्री विस्तृत करा (बायनरी) स्तंभ शीर्षकामध्ये दुहेरी बाण असलेले बटण सामग्री:

पहिल्या फाइलच्या उदाहरणावर पॉवर क्वेरी (Vostok.xlsx) आम्हाला प्रत्येक वर्कबुकमधून घ्यायच्या असलेल्या शीटचे नाव विचारेल – निवडा फोटो आणि ओके दाबा:

त्यानंतर (खरं तर), वापरकर्त्यासाठी स्पष्ट नसलेल्या अनेक घटना घडतील, ज्याचे परिणाम डाव्या पॅनेलमध्ये स्पष्टपणे दृश्यमान आहेत:

1. पॉवर क्वेरी फोल्डरमधून पहिली फाइल घेईल (आमच्याकडे ती असेल Vostok.xlsx — पहा फाइल उदाहरण) उदाहरण म्हणून आणि क्वेरी तयार करून त्याची सामग्री आयात करते नमुना फाइल रूपांतरित करा. या क्वेरीमध्ये काही सोप्या पायऱ्या असतील स्रोत (फाइल प्रवेश) जलवाहतूक (पत्रक निवड) आणि शक्यतो शीर्षके वाढवणे. ही विनंती केवळ एका विशिष्ट फाइलमधून डेटा लोड करू शकते Vostok.xlsx.
   
2. या विनंतीच्या आधारे, त्याच्याशी संबंधित कार्य तयार केले जाईल फाइल रूपांतरित करा (वैशिष्ट्यपूर्ण चिन्हाद्वारे सूचित केले आहे f_x), जेथे स्त्रोत फाइल यापुढे स्थिर राहणार नाही, परंतु व्हेरिएबल मूल्य – एक पॅरामीटर असेल. अशाप्रकारे, हे फंक्शन कोणत्याही पुस्तकातील डेटा काढू शकते ज्यामध्ये आपण तर्क म्हणून सरकतो.
   
3. स्तंभातील प्रत्येक फाईल (बायनरी) वर फंक्शन लागू केले जाईल सामग्री - यासाठी पायरी जबाबदार आहे सानुकूल कार्य कॉल करा आमच्या क्वेरीमध्ये जे फायलींच्या सूचीमध्ये एक स्तंभ जोडते फाइल रूपांतरित करा प्रत्येक वर्कबुकमधून आयात परिणामांसह:

   

4. अतिरिक्त स्तंभ काढले जातात.
5. नेस्टेड टेबलची सामग्री विस्तृत केली आहे (चरण विस्तारित सारणी स्तंभ) – आणि आम्ही सर्व पुस्तकांमधून डेटा संकलनाचे अंतिम परिणाम पाहतो:

   
   

पायरी 3. सँडिंग

मागील स्क्रीनशॉट स्पष्टपणे दर्शवितो की थेट असेंब्ली "जशी आहे तशी" खराब दर्जाची होती:

• स्तंभ उलटे आहेत.
• अनेक अतिरिक्त ओळी (रिक्त आणि फक्त नाही).
• सारणी शीर्षलेख हेडर म्हणून समजले जात नाहीत आणि डेटासह मिसळले जातात.
  

तुम्ही या सर्व समस्यांचे अगदी सहज निराकरण करू शकता - फक्त कन्व्हर्ट सॅम्पल फाइल क्वेरीमध्ये बदल करा. आम्ही त्यात केलेल्या सर्व समायोजने आपोआप संबंधित कन्व्हर्ट फाइल फंक्शनमध्ये येतील, याचा अर्थ प्रत्येक फाइलमधून डेटा आयात करताना ते नंतर वापरले जातील.

एक विनंती उघडून नमुना फाइल रूपांतरित करा, अनावश्यक पंक्ती फिल्टर करण्यासाठी पायऱ्या जोडा (उदाहरणार्थ, स्तंभानुसार Column2) आणि बटणासह शीर्षलेख वाढवणे शीर्षलेख म्हणून पहिली ओळ वापरा (शीर्षलेख म्हणून पहिली पंक्ती वापरा). टेबल अधिक चांगले दिसेल.

वेगवेगळ्या फायलींमधील स्तंभ आपोआप नंतर एकमेकांच्या खाली बसण्यासाठी, त्यांना समान नाव दिले पाहिजे. आपण एम-कोडच्या एका ओळीसह पूर्वी तयार केलेल्या निर्देशिकेनुसार असे वस्तुमान पुनर्नामित करू शकता. चला पुन्हा बटण दाबूया f_x फॉर्म्युला बारमध्ये आणि बदलण्यासाठी फंक्शन जोडा:

= Table.RenameColumns(#"Elevated Headers", Headers, MissingField.Ignore)

हे फंक्शन मागील पायरीवरून सारणी घेते उन्नत शीर्षलेख आणि नेस्टेड लुकअप सूचीनुसार त्यातील सर्व स्तंभांचे नाव बदलते ठळक बातम्या. तिसरा युक्तिवाद मिसिंगफील्ड. दुर्लक्ष करा आवश्यक आहे जेणेकरून निर्देशिकेत असलेल्या, परंतु टेबलमध्ये नसलेल्या शीर्षकांवर, त्रुटी उद्भवणार नाही.

वास्तविक, ते सर्व आहे.

विनंतीकडे परत येत आहे अहवाल आम्ही एक पूर्णपणे भिन्न चित्र पाहू - मागील चित्रापेक्षा खूपच छान:

• Power Query, Power Pivot, Power BI म्हणजे काय आणि एक्सेल वापरकर्त्याला त्यांची गरज का आहे
  
• दिलेल्या फोल्डरमधील सर्व फाइल्समधून डेटा गोळा करणे
• पुस्तकाच्या सर्व शीट्समधील डेटा एका टेबलमध्ये गोळा करणे