धेरै पुस्तकहरूबाट विभिन्न हेडरहरूसँग तालिकाहरू बनाउनुहोस्

समस्याको सूत्रीकरण

हामीसँग एउटै फोल्डरमा धेरै फाइलहरू छन् (हाम्रो उदाहरणमा - 4 टुक्रा, सामान्य अवस्थामा - तपाईलाई मनपर्ने धेरै) रिपोर्ट:

भित्र, यी फाइलहरू यस्तो देखिन्छ:

जसमा:

• हामीलाई चाहिने डाटा पाना सधैं बोलाइन्छ फोटो, तर कार्यपुस्तिकामा कहीं पनि हुन सक्छ।
  
• पाना परे फोटो प्रत्येक पुस्तकमा अन्य पानाहरू हुन सक्छन्।
• डेटा भएका तालिकाहरूमा पङ्क्तिहरूको फरक संख्या हुन्छ र कार्यपत्रमा फरक पङ्क्तिबाट सुरु हुन सक्छ।
• विभिन्न तालिकाहरूमा एउटै स्तम्भहरूको नाम फरक हुन सक्छ (उदाहरणका लागि, मात्रा = मात्रा = मात्रा).
• तालिकाहरूमा स्तम्भहरू फरक क्रममा व्यवस्थित गर्न सकिन्छ।

कार्य: पानाबाट सबै फाइलहरूबाट बिक्री डाटा सङ्कलन फोटो एक साधारण तालिकामा पछि सारांश वा कुनै अन्य विश्लेषणहरू निर्माण गर्न।

चरण 1. स्तम्भ नामहरूको डाइरेक्टरी तयार गर्दै

गर्न को लागी पहिलो कुरा स्तम्भ नाम र तिनीहरूको सही व्याख्या को लागी सबै सम्भावित विकल्पहरु संग एक सन्दर्भ पुस्तक तयार छ:

हामीले ट्याबमा रहेको ढाँचाको रूपमा तालिका बटन प्रयोग गरेर यो सूचीलाई गतिशील "स्मार्ट" तालिकामा रूपान्तरण गर्छौं। गृहपृष्ठ (घर - तालिकाको रूपमा ढाँचा) वा किबोर्ड सर्टकट Ctrl+T र आदेशको साथ Power Query मा लोड गर्नुहोस् डाटा - तालिका/दायराबाट (डेटा - तालिका/दायराबाट)। Excel को हालैका संस्करणहरूमा, यसलाई पुन: नामाकरण गरिएको छ पातहरु संग (पानाबाट).

पावर क्वेरी क्वेरी सम्पादक विन्डोमा, हामी परम्परागत रूपमा चरण मेटाउँछौं बदलिएको प्रकार र बटनमा क्लिक गरेर यसको सट्टा नयाँ चरण थप्नुहोस् f_xसूत्र पट्टीमा (यदि यो देखिएन भने, त्यसपछि तपाइँ यसलाई ट्याबमा सक्षम गर्न सक्नुहुन्छ समीक्षा) र त्यहाँ निर्मित पावर क्वेरी भाषा M मा सूत्र प्रविष्ट गर्नुहोस्:

=तालिका (स्रोत)

यो आदेशले अघिल्लो चरणमा लोड गरिएको एकलाई रूपान्तरण गर्नेछ मुहान नेस्टेड सूचीहरू (सूची) सम्मिलित सूचीमा सन्दर्भ तालिका, जसमध्ये प्रत्येक, बारीमा, मानहरूको जोडी हो। यो बन्यो एक लाइनबाट:

हामीलाई यस प्रकारको डाटा अलि पछि आवश्यक पर्नेछ, जब सबै लोड गरिएका तालिकाहरूबाट हेडरहरू पुन: नामाकरण गर्नुहोस्।

रूपान्तरण पूरा गरेपछि, आदेशहरू चयन गर्नुहोस् घर - बन्द गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ... र आयात को प्रकार बस एक जडान सिर्जना गर्नुहोस् (घर - बन्द गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ... - जडान मात्र सिर्जना गर्नुहोस्) र Excel मा फर्कनुहोस्।

चरण 2. हामी सबै फाइलहरूबाट सबै चीजहरू जस्तै लोड गर्छौं

अब फोल्डरबाट हाम्रा सबै फाइलहरूको सामग्री लोड गरौं - अहिलेको लागि, जस्तै। टोलीहरू छनौट गर्दै डाटा - डाटा प्राप्त गर्नुहोस् - फाइलबाट - फोल्डरबाट (डेटा - डाटा प्राप्त गर्नुहोस् - फाइलबाट - फोल्डरबाट) र त्यसपछि फोल्डर जहाँ हाम्रो स्रोत पुस्तकहरू छन्।

पूर्वावलोकन विन्डोमा, क्लिक गर्नुहोस् रूपान्तरण गर्नुहोस् (रूपान्तरण) or परिवर्तन (सम्पादन):

र त्यसपछि सबै डाउनलोड गरिएका फाइलहरूको सामग्री विस्तार गर्नुहोस् (बाइनरी) स्तम्भ शीर्षकमा डबल तीरहरू भएको बटन सामग्री:

पहिलो फाइलको उदाहरणमा पावर क्वेरी (Vostok.xlsx) ले हामीलाई प्रत्येक कार्यपुस्तिकाबाट लिन चाहेको पानाको नाम सोध्नेछ - छान्नुहोस् फोटो र OK थिच्नुहोस्:

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

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

   

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

   
   

चरण 3. स्यान्डिङ

अघिल्लो स्क्रिनसटले स्पष्ट रूपमा देखाउँछ कि प्रत्यक्ष असेंबली "जस्तो छ" खराब गुणस्तरको भयो:

• स्तम्भहरू उल्टो छन्।
• धेरै अतिरिक्त लाइनहरू (खाली र मात्र होइन)।
• तालिका हेडरहरूलाई हेडरको रूपमा बुझाइँदैन र डेटासँग मिलाइन्छ।
  

तपाइँ यी सबै समस्याहरू सजिलैसँग समाधान गर्न सक्नुहुन्छ - केवल कन्भर्ट नमूना फाइल क्वेरीलाई ट्वीक गर्नुहोस्। हामीले यसमा गर्ने सबै समायोजनहरू स्वचालित रूपमा सम्बन्धित कन्भर्ट फाइल प्रकार्यमा पर्नेछ, जसको मतलब प्रत्येक फाइलबाट डाटा आयात गर्दा तिनीहरू पछि प्रयोग हुनेछन्।

अनुरोध खोलेर नमूना फाइल रूपान्तरण, अनावश्यक पङ्क्तिहरू फिल्टर गर्न चरणहरू थप्नुहोस् (उदाहरणका लागि, स्तम्भ द्वारा Column2) र बटनको साथ हेडिङहरू उठाउँदै हेडरको रूपमा पहिलो लाइन प्रयोग गर्नुहोस् (हेडरको रूपमा पहिलो पङ्क्ति प्रयोग गर्नुहोस्)। तालिका धेरै राम्रो देखिनेछ।

बिभिन्न फाईलहरूका स्तम्भहरू पछि स्वचालित रूपमा एक अर्कामा फिट हुनको लागि, तिनीहरूको नाम एउटै हुनुपर्छ। तपाईंले M-कोडको एक लाइनको साथ पहिले सिर्जना गरिएको डाइरेक्टरी अनुसार यस्तो ठूलो नामकरण गर्न सक्नुहुन्छ। फेरि बटन थिचौं f_x सूत्र पट्टीमा र परिवर्तन गर्न प्रकार्य थप्नुहोस्:

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

यो प्रकार्यले अघिल्लो चरणबाट तालिका लिन्छ उन्नत हेडरहरू र नेस्टेड लुकअप सूची अनुसार यसमा सबै स्तम्भहरू पुन: नामाकरण गर्दछ Headlines। तेस्रो तर्क मिसिङफिल्ड।बेवास्ता गर्नुहोस् आवश्यक छ ताकि ती शीर्षकहरूमा जुन निर्देशिकामा छन्, तर तालिकामा छैनन्, त्रुटि देखा पर्दैन।

वास्तवमा, यो सबै हो।

अनुरोधमा फर्कदै रिपोर्ट हामी पुरा तरिकाले फरक तस्वीर देख्नेछौं - अघिल्लो भन्दा धेरै राम्रो:

• Power Query, Power Pivot, Power BI के हो र किन एक्सेल प्रयोगकर्तालाई तिनीहरूको आवश्यकता छ
  
• दिइएको फोल्डरमा सबै फाइलहरूबाट डाटा सङ्कलन
• पुस्तकका सबै पानाहरूबाट डाटा सङ्कलन गर्दै एउटा तालिकामा