सामग्रीहरू
समस्याको सूत्रीकरण
धेरै जसो एक्सेल प्रयोगकर्ताहरूले ढिलो वा पछि सामना गर्ने एकदमै मानक अवस्थाहरू मध्ये एउटाको लागि एउटा सुन्दर समाधान हेरौं: तपाईंले छिटो र स्वचालित रूपमा फाइलहरूको ठूलो संख्याबाट एक अन्तिम तालिकामा डाटा सङ्कलन गर्न आवश्यक छ।
मानौं हामीसँग निम्न फोल्डर छ, जसमा शाखा शहरहरूबाट डाटा सहित धेरै फाइलहरू छन्:
फाइलहरूको संख्या फरक पर्दैन र भविष्यमा परिवर्तन हुन सक्छ। प्रत्येक फाइल नामको पाना छ बिक्रीजहाँ डाटा तालिका स्थित छ:
तालिकाहरूमा पङ्क्तिहरू (अर्डरहरू) को संख्या, निस्सन्देह, फरक छ, तर स्तम्भहरूको सेट जताततै मानक छ।
कार्य: तालिकाहरूमा शहर फाइलहरू वा पङ्क्तिहरू थप्दा वा मेटाउँदा पछि स्वचालित अद्यावधिकको साथ सबै फाइलहरूबाट डाटा सङ्कलन गर्न। अन्तिम समेकित तालिका अनुसार, त्यसपछि कुनै पनि रिपोर्टहरू, पिभोट तालिकाहरू, फिल्टर-क्रमबद्ध डाटा, आदि निर्माण गर्न सम्भव हुनेछ। मुख्य कुरा सङ्कलन गर्न सक्षम हुनु हो।
हामी हतियार चयन गर्छौं
समाधानको लागि, हामीलाई एक्सेल 2016 को नवीनतम संस्करण चाहिन्छ (आवश्यक कार्यक्षमता पहिले नै पूर्वनिर्धारित रूपमा यसमा निर्मित छ) वा एक्सेल 2010-2013 को अघिल्लो संस्करणहरू नि: शुल्क एड-इन स्थापना भएको छ। पावर क्वेरी माइक्रोसफ्टबाट (यहाँ डाउनलोड गर्नुहोस्)। Power Query बाहिरी संसारबाट Excel मा डाटा लोड गर्न, त्यसपछि स्ट्रिपिङ र प्रशोधन गर्नको लागि एक सुपर लचिलो र सुपर शक्तिशाली उपकरण हो। पावर क्वेरीले लगभग सबै अवस्थित डेटा स्रोतहरूलाई समर्थन गर्दछ - पाठ फाइलहरूदेखि SQL र Facebook 🙂 सम्म
यदि तपाइँसँग एक्सेल 2013 वा 2016 छैन भने, तपाइँ अगाडि पढ्न सक्नुहुन्न (केवल मजाक गर्दै)। एक्सेलको पुरानो संस्करणहरूमा, यस्तो कार्य केवल भिजुअल बेसिकमा म्याक्रो प्रोग्रामिङ गरेर पूरा गर्न सकिन्छ (जुन शुरुवातकर्ताहरूको लागि धेरै गाह्रो छ) वा मोनोटोनस म्यानुअल प्रतिलिपि गरेर (जसले लामो समय लिन्छ र त्रुटिहरू उत्पन्न गर्दछ)।
चरण 1. नमूनाको रूपमा एउटा फाइल आयात गर्नुहोस्
पहिले, उदाहरणको रूपमा एउटा कार्यपुस्तिकाबाट डाटा आयात गरौं, ताकि एक्सेलले "विचार उठाउँछ"। यो गर्नको लागि, नयाँ खाली कार्यपुस्तिका सिर्जना गर्नुहोस् र…
- यदि तपाइँसँग एक्सेल 2016 छ भने, त्यसपछि ट्याब खोल्नुहोस् तथ्याङ्क अनि तेस्पछि क्वेरी सिर्जना गर्नुहोस् - फाइलबाट - पुस्तकबाट (डेटा - नयाँ क्वेरी - फाइलबाट - एक्सेलबाट)
- यदि तपाइँसँग एक्सेल 2010-2013 पावर क्वेरी एड-इन स्थापना भएको छ भने, त्यसपछि ट्याब खोल्नुहोस् पावर क्वेरी र यसमा चयन गर्नुहोस् फाइलबाट - पुस्तकबाट (फाइलबाट - एक्सेलबाट)
त्यसपछि, खुल्ने विन्डोमा, रिपोर्टहरू सहित हाम्रो फोल्डरमा जानुहोस् र कुनै पनि सहर फाइलहरू चयन गर्नुहोस् (यसले फरक पार्दैन, किनभने तिनीहरू सबै सामान्य छन्)। केहि सेकेन्ड पछि, नेभिगेटर सञ्झ्याल देखा पर्नेछ, जहाँ तपाइँ बायाँ छेउमा हामीलाई चाहिने पाना (बिक्री) चयन गर्न आवश्यक छ, र यसको सामग्री दायाँ तिर प्रदर्शित हुनेछ:
यदि तपाइँ यो विन्डोको तल्लो दायाँ कुनामा बटनमा क्लिक गर्नुहुन्छ डाउनलोड (लोड), त्यसपछि तालिका तुरुन्तै यसको मूल रूप मा पानामा आयात गरिनेछ। एकल फाइलको लागि, यो राम्रो छ, तर हामीले त्यस्ता धेरै फाइलहरू लोड गर्न आवश्यक छ, त्यसैले हामी अलि फरक रूपमा जान्छौं र बटन क्लिक गर्नेछौं। सुधार (सम्पादन)। त्यस पछि, पावर क्वेरी क्वेरी सम्पादकलाई पुस्तकबाट हाम्रो डेटाको साथ छुट्टै सञ्झ्यालमा प्रदर्शन गर्नुपर्छ:
यो एक धेरै शक्तिशाली उपकरण हो जसले तपाइँलाई हामीलाई चाहिने दृश्यमा तालिका "समाप्त" गर्न अनुमति दिन्छ। यसको सबै प्रकार्यहरूको सतही विवरणले पनि लगभग सय पृष्ठहरू लिनेछ, तर, यदि धेरै छोटकरीमा, यो सञ्झ्याल प्रयोग गरेर तपाइँ गर्न सक्नुहुन्छ:
- अनावश्यक डाटा, खाली लाइनहरू, त्रुटिहरू भएका लाइनहरू फिल्टर गर्नुहोस्
- एक वा बढी स्तम्भहरू द्वारा डेटा क्रमबद्ध गर्नुहोस्
- पुनरावृत्तिबाट छुटकारा पाउनुहोस्
- टाँसिने पाठलाई स्तम्भहरूद्वारा विभाजन गर्नुहोस् (डिलिमिटरहरू, क्यारेक्टरहरूको सङ्ख्या, आदि द्वारा)
- क्रमबद्ध पाठ राख्नुहोस् (अतिरिक्त खाली ठाउँहरू हटाउनुहोस्, सही केस, आदि)
- प्रत्येक सम्भावित तरिकामा डेटा प्रकारहरू रूपान्तरण गर्नुहोस् (पाठ जस्ता संख्याहरूलाई सामान्य संख्यामा र यसको विपरीत)
- ट्रान्सपोज (रोटेट) तालिकाहरू र दुई-आयामी क्रस-टेबलहरूलाई समतलमा विस्तार गर्नुहोस्
- तालिकामा थप स्तम्भहरू थप्नुहोस् र Power Query मा निर्मित M भाषा प्रयोग गरी तिनीहरूमा सूत्र र कार्यहरू प्रयोग गर्नुहोस्।
- ...
उदाहरणका लागि, हाम्रो तालिकामा महिनाको पाठ नामको साथ एउटा स्तम्भ थपौं, ताकि पछि पिभोट तालिका रिपोर्टहरू निर्माण गर्न सजिलो हुन्छ। यो गर्नको लागि, स्तम्भ शीर्षकमा दायाँ क्लिक गर्नुहोस् मितिर आदेश चयन गर्नुहोस् नक्कल स्तम्भ (नक्कल स्तम्भ), र त्यसपछि देखा पर्ने डुप्लिकेट स्तम्भको हेडरमा दायाँ क्लिक गर्नुहोस् र आदेशहरू चयन गर्नुहोस्। रूपान्तरण – महिना – महिनाको नाम:
प्रत्येक पङ्क्तिको लागि महिनाको पाठ नामहरूसँग नयाँ स्तम्भ बनाइनुपर्छ। स्तम्भ शीर्षकमा डबल-क्लिक गरेर, तपाइँ यसलाई पुन: नामकरण गर्न सक्नुहुन्छ मिति प्रतिलिपि गर्नुहोस् थप सहज गर्न महिनाउदाहरण को लागी
यदि केहि स्तम्भहरूमा कार्यक्रमले डेटा प्रकारलाई सही रूपमा पहिचान गर्न सकेन भने, तपाइँ प्रत्येक स्तम्भको बायाँ छेउमा ढाँचा आइकनमा क्लिक गरेर यसलाई मद्दत गर्न सक्नुहुन्छ:
तपाईं त्रुटिहरू वा खाली लाइनहरू, साथै अनावश्यक प्रबन्धकहरू वा ग्राहकहरू, साधारण फिल्टर प्रयोग गरेर बाहिर निकाल्न सक्नुहुन्छ:
यसबाहेक, सबै प्रदर्शन गरिएका रूपान्तरणहरू दायाँ प्यानलमा निश्चित छन्, जहाँ तिनीहरू सधैं घुमाउन सकिन्छ (क्रस) वा तिनीहरूको प्यारामिटरहरू (गियर) परिवर्तन गर्न सकिन्छ:
हल्का र सुरुचिपूर्ण, हैन?
चरण 2. हाम्रो अनुरोधलाई प्रकार्यमा रूपान्तरण गरौं
प्रत्येक आयातित पुस्तकको लागि बनाइएको सबै डेटा रूपान्तरणहरू पछि दोहोर्याउनको लागि, हामीले हाम्रो सिर्जना गरिएको अनुरोधलाई प्रकार्यमा रूपान्तरण गर्न आवश्यक छ, जुन त्यसपछि हाम्रो सबै फाइलहरूमा लागू हुनेछ। यो गर्न वास्तवमा धेरै सरल छ।
क्वेरी सम्पादकमा, दृश्य ट्याबमा जानुहोस् र बटनमा क्लिक गर्नुहोस् उन्नत सम्पादक (हेर्नुहोस् - उन्नत सम्पादक)। एउटा विन्डो खोल्नु पर्छ जहाँ हाम्रा सबै अघिल्ला कार्यहरू M भाषामा कोडको रूपमा लेखिनेछन्। कृपया ध्यान दिनुहोस् कि हामीले उदाहरणको लागि आयात गरेको फाइलको मार्ग कोडमा हार्डकोड गरिएको छ:
अब केहि समायोजन गरौं:
तिनीहरूको अर्थ सरल छ: पहिलो रेखा (फाइलपाथ) => हाम्रो प्रक्रियालाई तर्कको साथ प्रकार्यमा परिणत गर्दछ फाइलपाथ, र तल हामी यस चरको मानमा निश्चित मार्ग परिवर्तन गर्छौं।
सबै। थिच्नुस समाप्त र यो हेर्नु पर्छ:
डाटा हराएको छ भनेर नडराउनुहोस् - वास्तवमा, सबै ठीक छ, सबै कुरा यस्तो देखिनु पर्छ 🙂 हामीले सफलतापूर्वक हाम्रो अनुकूलन प्रकार्य सिर्जना गरेका छौं, जहाँ डेटा आयात र प्रशोधनका लागि सम्पूर्ण एल्गोरिथ्मलाई कुनै विशेष फाइलमा बाँधिएको बिना सम्झिन्छ। । यसलाई अझ बुझ्ने नाम दिन बाँकी छ (उदाहरणका लागि डाटा प्राप्त गर्नुहोस्) फिल्डको दायाँपट्टिको प्यानलमा नाम र तपाईं कटनी गर्न सक्नुहुन्छ घर - बन्द गर्नुहोस् र डाउनलोड गर्नुहोस् (घर - बन्द र लोड)। कृपया ध्यान दिनुहोस् कि हामीले उदाहरणको लागि आयात गरेको फाइलको मार्ग कोडमा हार्डकोड गरिएको छ। तपाईं मुख्य माइक्रोसफ्ट एक्सेल विन्डोमा फर्कनुहुनेछ, तर हाम्रो प्रकार्यमा सिर्जना गरिएको जडानको साथ प्यानल दायाँमा देखा पर्नेछ:
चरण 3. सबै फाइलहरू सङ्कलन गर्दै
सबै कठिन भाग पछाडि छ, सुखद र सजिलो भाग बाँकी छ। ट्याबमा जानुहोस् डाटा - क्वेरी सिर्जना गर्नुहोस् - फाइलबाट - फोल्डरबाट (डेटा — नयाँ क्वेरी — फाइलबाट — फोल्डरबाट) वा, यदि तपाइँसँग Excel 2010-2013 छ भने, ट्याब जस्तै पावर क्वेरी। देखा पर्ने विन्डोमा, फोल्डर निर्दिष्ट गर्नुहोस् जहाँ हाम्रा सबै स्रोत शहर फाइलहरू अवस्थित छन् र क्लिक गर्नुहोस् OK। अर्को चरणले एउटा सञ्झ्याल खोल्नु पर्छ जहाँ यो फोल्डरमा फेला परेका सबै एक्सेल फाइलहरू (र यसको सबफोल्डरहरू) र ती प्रत्येकका लागि विवरणहरू सूचीबद्ध हुनेछन्:
क्लिक गर्नुहोस् परिवर्तन (सम्पादन) र फेरि हामी परिचित क्वेरी सम्पादक विन्डोमा जान्छौं।
अब हामीले हाम्रो सिर्जना गरिएको प्रकार्यको साथ हाम्रो तालिकामा अर्को स्तम्भ थप्न आवश्यक छ, जसले प्रत्येक फाइलबाट डाटा "पुल" गर्नेछ। यो गर्नको लागि, ट्याबमा जानुहोस् स्तम्भ थप्नुहोस् - अनुकूलन स्तम्भ (स्तम्भ थप्नुहोस् - अनुकूलन स्तम्भ थप्नुहोस्) र देखा पर्ने विन्डोमा, हाम्रो प्रकार्य प्रविष्ट गर्नुहोस् डाटा प्राप्त गर्नुहोस्, प्रत्येक फाइलको पूर्ण मार्ग तर्कको रूपमा यसको लागि निर्दिष्ट गर्दै:
क्लिक गर्नु पछि OK सिर्जना गरिएको स्तम्भ दायाँतिर हाम्रो तालिकामा थपिनुपर्छ।
अब सबै अनावश्यक स्तम्भहरू मेटौं (एक्सेलमा जस्तै, दायाँ माउस बटन प्रयोग गरेर - हटाउन), थपिएको स्तम्भ र फाइल नामको स्तम्भ मात्र छोडेर, किनभने यो नाम (थप स्पष्ट रूपमा, सहर) प्रत्येक पङ्क्तिको कुल डाटामा हुन उपयोगी हुनेछ।
र अब "वाह पल" - हाम्रो प्रकार्यको साथ थपिएको स्तम्भको माथिल्लो दायाँ कुनामा यसको आफ्नै तीरहरूसँग आइकनमा क्लिक गर्नुहोस्:
… अनचेक गर्नुहोस् उपसर्गको रूपमा मूल स्तम्भ नाम प्रयोग गर्नुहोस् (मूल स्तम्भको नाम उपसर्गको रूपमा प्रयोग गर्नुहोस्)र क्लिक गर्नुहोस् OK। र हाम्रो प्रकार्यले रेकर्ड गरिएको एल्गोरिथ्म पछ्याउँदै र साझा तालिकामा सबै कुरा सङ्कलन गर्दै प्रत्येक फाइलबाट डाटा लोड र प्रशोधन गर्नेछ:
पूर्ण सौन्दर्यको लागि, तपाईंले फाइल नामहरू सहितको पहिलो स्तम्भबाट .xlsx विस्तारहरू हटाउन सक्नुहुन्छ - मानक प्रतिस्थापन द्वारा "केही छैन" (स्तम्भ हेडरमा दायाँ क्लिक गर्नुहोस् - उपन्यास) र यो स्तम्भमा पुन: नामाकरण गर्नुहोस् शहर। र मितिको साथ स्तम्भमा डाटा ढाँचा पनि सच्याउनुहोस्।
सबै! थिच्नुस घर - बन्द र लोड (घर - बन्द र लोड)। सबै शहरहरूका लागि क्वेरीद्वारा सङ्कलन गरिएका सबै डाटाहरू "स्मार्ट टेबल" ढाँचामा हालको एक्सेल पानामा अपलोड गरिनेछन्:
सिर्जना गरिएको जडान र हाम्रो असेंबली प्रकार्यलाई कुनै पनि तरिकाले अलग-अलग बचत गर्न आवश्यक छैन - तिनीहरू सामान्य तरिकामा हालको फाइलसँग सँगै बचत हुन्छन्।
भविष्यमा, फोल्डरमा कुनै पनि परिवर्तनहरू (शहरहरू थप्ने वा हटाउने) वा फाइलहरूमा (लाइनहरूको संख्या परिवर्तन गर्दै), यो टेबलमा वा दायाँ प्यानलको क्वेरीमा सिधै दायाँ क्लिक गर्न पर्याप्त हुनेछ। आदेश अद्यावधिक र बचत गर्नुहोस् (रिफ्रेस) - पावर क्वेरीले केही सेकेन्डमा सबै डाटालाई फेरि "पुनर्निर्माण" गर्नेछ।
PS
संशोधन। जनवरी 2017 अद्यावधिकहरू पछि, Power Query ले एक्सेल कार्यपुस्तिकाहरू आफैं सङ्कलन गर्ने तरिका सिके, अर्थात् अब छुट्टै प्रकार्य बनाउन आवश्यक पर्दैन - यो स्वचालित रूपमा हुन्छ। यसैले, यस लेखको दोस्रो चरण अब आवश्यक छैन र सम्पूर्ण प्रक्रिया उल्लेखनीय रूपमा सरल हुन्छ:
- छनौट अनुरोध सिर्जना गर्नुहोस् - फाइलबाट - फोल्डरबाट - फोल्डर चयन गर्नुहोस् - ठीक छ
- फाइलहरूको सूची देखा परेपछि, थिच्नुहोस् परिवर्तन
- क्वेरी सम्पादक विन्डोमा, बाइनरी स्तम्भलाई डबल एरोको साथ विस्तार गर्नुहोस् र प्रत्येक फाइलबाट लिइने पाना नाम चयन गर्नुहोस्।
र त्यो सबै हो! गीत!
- क्रसट्याबलाई पिभोट टेबलहरू बनाउनको लागि उपयुक्त फ्ल्याटमा पुनः डिजाइन गर्नुहोस्
- Power View मा एनिमेटेड बबल चार्ट निर्माण गर्दै
- विभिन्न एक्सेल फाइलहरूबाट एकमा पानाहरू जम्मा गर्न म्याक्रो