धेरै डेटा दायराहरूमा पिभोट तालिका

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

पिभोट तालिकाहरू Excel मा सबैभन्दा अद्भुत उपकरणहरू मध्ये एक हो। तर अहिलेसम्म, दुर्भाग्यवश, एक्सेलको कुनै पनि संस्करणले उडानमा धेरै प्रारम्भिक डाटा दायराहरूको लागि सारांश निर्माण गर्ने जस्तो सरल र आवश्यक काम गर्न सक्दैन, उदाहरणका लागि, विभिन्न पानाहरूमा वा विभिन्न तालिकाहरूमा:

हामीले सुरु गर्नु अघि, केही बिन्दुहरू स्पष्ट गरौं। एक प्राथमिकता, म विश्वास गर्छु कि निम्न सर्तहरू हाम्रो डेटामा पूरा हुन्छन्:

• तालिकाहरूमा कुनै पनि डेटा भएको पङ्क्तिहरूको संख्या हुन सक्छ, तर तिनीहरूसँग एउटै हेडर हुनुपर्छ।
• स्रोत तालिकाहरू भएका पानाहरूमा कुनै अतिरिक्त डेटा हुनु हुँदैन। एउटा पाना - एउटा टेबल। नियन्त्रण गर्न, म तपाईंलाई किबोर्ड सर्टकट प्रयोग गर्न सल्लाह दिन्छु Ctrl+अन्त, जसले तपाईंलाई कार्यपत्रमा अन्तिम प्रयोग गरिएको कक्षमा लैजान्छ। आदर्श रूपमा, यो डेटा तालिकामा अन्तिम सेल हुनुपर्छ। यदि तपाइँ क्लिक गर्दा Ctrl+अन्त तालिकाको दायाँ वा तल कुनै पनि खाली कक्ष हाइलाइट गरिएको छ - यी खाली स्तम्भहरू दायाँ वा तालिकाको तल पङ्क्तिहरू मेटाउनुहोस् र फाइल बचत गर्नुहोस्।

विधि १: पावर क्वेरी प्रयोग गरेर पिभोटका लागि तालिकाहरू बनाउनुहोस्

एक्सेलको लागि 2010 संस्करणबाट सुरु गर्दै, त्यहाँ एक नि: शुल्क पावर क्वेरी एड-इन छ जसले कुनै पनि डेटा सङ्कलन र रूपान्तरण गर्न सक्छ र त्यसपछि यसलाई पिभोट तालिका बनाउनको लागि स्रोतको रूपमा दिन सक्छ। यस एड-इनको मद्दतले हाम्रो समस्या समाधान गर्न गाह्रो छैन।

पहिले, एक्सेलमा नयाँ खाली फाइल सिर्जना गरौं - यसमा एसेम्बली हुनेछ र त्यसपछि यसमा पिभोट टेबल बनाइनेछ।

त्यसपछि ट्याबमा तथ्याङ्क (यदि तपाईंसँग Excel 2016 वा पछिको छ) वा ट्याबमा पावर क्वेरी (यदि तपाइँसँग एक्सेल 2010-2013 छ) आदेश चयन गर्नुहोस् क्वेरी सिर्जना गर्नुहोस् - फाइलबाट - एक्सेल (डेटा प्राप्त गर्नुहोस् - फाइलबाट - एक्सेल) र सङ्कलन गरिने तालिकाहरूसँग स्रोत फाइल निर्दिष्ट गर्नुहोस्:

देखा पर्ने विन्डोमा, कुनै पनि पाना चयन गर्नुहोस् (यसले कुनै फरक पर्दैन) र तलको बटन थिच्नुहोस् परिवर्तन (सम्पादन):

Power Query Query Editor सञ्झ्याल एक्सेलको शीर्षमा खोल्नु पर्छ। प्यानलमा विन्डोको दायाँ छेउमा प्यारामिटर अनुरोध गर्नुहोस् पहिलो बाहेक सबै स्वचालित रूपमा सिर्जना गरिएका चरणहरू मेटाउनुहोस् - मुहान (मुहान):

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

स्तम्भ बाहेक सबै स्तम्भहरू मेटाउनुहोस् तथ्याङ्कस्तम्भ शीर्षकमा दायाँ क्लिक गरेर र चयन गरेर अन्य स्तम्भहरू मेटाउनुहोस् (हटाउनुहोस् अन्य स्तम्भहरू):

त्यसपछि तपाईले स्तम्भको शीर्षमा रहेको डबल एरोमा क्लिक गरेर सङ्कलन गरिएको तालिकाको सामग्री विस्तार गर्न सक्नुहुन्छ (चेकबक्स उपसर्गको रूपमा मूल स्तम्भ नाम प्रयोग गर्नुहोस् तपाईं यसलाई बन्द गर्न सक्नुहुन्छ):

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

यो बटनको साथ तालिका हेडरमा पहिलो पङ्क्ति उठाउन बाँकी छ हेडरको रूपमा पहिलो लाइन प्रयोग गर्नुहोस् (हेडरको रूपमा पहिलो पङ्क्ति प्रयोग गर्नुहोस्) ट्याब गृहपृष्ठ (घर) र फिल्टर प्रयोग गरेर डाटाबाट डुप्लिकेट तालिका हेडरहरू हटाउनुहोस्:

आदेशको साथ गरिएको सबै बचत गर्नुहोस् बन्द गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ... (बंद गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ...) ट्याब गृहपृष्ठ (घर), र खुल्ने विन्डोमा, विकल्प चयन गर्नुहोस् जडान मात्र (कनेक्सन मात्र):

सबै कुरा। यो केवल एक सारांश निर्माण गर्न बाँकी छ। यो गर्नको लागि, ट्याबमा जानुहोस् घुसाउनुहोस् - PivotTable (घुसाउनुहोस् - पिभोट तालिका), विकल्प छान्नुहोस् बाह्य डेटा स्रोत प्रयोग गर्नुहोस् (बाह्य डाटा स्रोत प्रयोग गर्नुहोस्)र त्यसपछि बटन क्लिक गरेर जडान चयन गर्नुहोस्, हाम्रो अनुरोध। पिभोटको थप सिर्जना र कन्फिगरेसन हामीलाई पङ्क्तिहरू, स्तम्भहरू र मानहरू क्षेत्रमा आवश्यक क्षेत्रहरू ड्र्याग गरेर पूर्ण रूपमा मानक तरिकामा हुन्छ:

यदि भविष्यमा स्रोत डेटा परिवर्तन भयो वा केहि थप स्टोर पानाहरू थपियो भने, यो आदेश प्रयोग गरेर क्वेरी र हाम्रो सारांश अद्यावधिक गर्न पर्याप्त हुनेछ। सबै ताजा गर्नुहोस् ट्याब तथ्याङ्क (डेटा - सबै ताजा गर्नुहोस्).

विधि 2. हामी म्याक्रोमा UNION SQL आदेशको साथ तालिकाहरू एकजुट गर्छौं

हाम्रो समस्याको अर्को समाधान यो म्याक्रोद्वारा प्रतिनिधित्व गरिएको छ, जसले आदेश प्रयोग गरेर पिभोट तालिकाको लागि डाटा सेट (क्यास) सिर्जना गर्दछ। UNITY SQL क्वेरी भाषा। यो आदेशले एरेमा निर्दिष्ट गरिएका सबै तालिकाहरू संयोजन गर्दछ पानानामहरू एकल डेटा तालिकामा पुस्तकको पानाहरू। अर्थात्, भौतिक रूपमा विभिन्न पानाहरूबाट एकमा दायराहरू प्रतिलिपि गर्न र टाँस्नको सट्टा, हामी कम्प्युटरको RAM मा पनि त्यस्तै गर्छौं। त्यसपछि म्याक्रोले दिइएको नामको साथ नयाँ पाना थप्छ (चर ResultSheetName) र संकलित क्यासको आधारमा यसमा पूर्ण (!) सारांश सिर्जना गर्दछ।

म्याक्रो प्रयोग गर्न, ट्याबमा रहेको भिजुअल बेसिक बटन प्रयोग गर्नुहोस् विकासकर्ता (विकासकर्ता) वा किबोर्ड सर्टकट alt+F11। त्यसपछि हामी मेनु मार्फत नयाँ खाली मोड्युल घुसाउँछौं घुसाउनुहोस् - मोड्युल र त्यहाँ निम्न कोड प्रतिलिपि गर्नुहोस्:

Sub New_Multi_Table_Pivot() Dim i As long dim arSQL() स्ट्रिङको रूपमा Dim objPivotCache PivotCache को रूपमा Dim objRS वस्तुको रूपमा Dim ResultSheetName स्ट्रिङ Dim SheetsNames को रूपमा भेरिएन्ट 'पाना नाम जहाँ नतिजा पिभोट प्रदर्शित हुनेछ 'arSheetN' = "arSheet" को परिणाम स्रोत तालिकाहरू भएका नामहरू SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'हामी ActiveWorkbook ReDim arSQL (1 To (UBound(SheetsNames) + 1) सँग SheetsNames बाट पानाहरूबाट तालिकाहरूको लागि क्यास बनाउँछौं। ) i = LBound (SheetsNames) to UBound(SheetsNames) arSQL(i + 1) = "SELECT* FROM [" & SheetsNames(i) & "$]" को लागी अर्को i Set objRS = CreateObject("ADODB.Recordset") objRS खोल्नुहोस् Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName,"; Extended Properties=""Excel 8.0;" ""), vbNullString ) त्रुटिमा नतिजा पिभोट तालिका प्रदर्शन गर्न पाना पुन: सिर्जना गर्नुहोस् अर्को अनुप्रयोग पुन: सुरु गर्नुहोस्। DisplayAlerts = False Worksheets(ResultSheetName)। सेट wsPivot = Worksheets मेटाउनुहोस्। wsPivot थप्नुहोस्। Name = ResultSheetName 'यस पानामा उत्पन्न क्यास सारांश प्रदर्शन गर्नुहोस् objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) सेट गर्नुहोस् objPivotCache.Recordset = objRS सेट objRS = wsPivTivsCatable सँग केहि पनि छैन। ivot.Range("A3") सेट objPivotCache = कुनै पनि दायरा ("A3")। अन्त्य उपको साथ अन्त्य चयन गर्नुहोस्    

समाप्त म्याक्रो त्यसपछि किबोर्ड सर्टकट संग चलाउन सकिन्छ alt+F8 वा ट्याबमा रहेको म्याक्रो बटन विकासकर्ता (विकासकर्ता - म्याक्रो).

यस दृष्टिकोणको हानि:

• डाटा अपडेट गरिएको छैन किनभने क्यास स्रोत तालिकाहरूमा कुनै जडान छैन। यदि तपाईंले स्रोत डेटा परिवर्तन गर्नुभयो भने, तपाईंले म्याक्रो फेरि चलाउनु पर्छ र सारांश पुन: निर्माण गर्नुपर्छ।
• पानाहरूको संख्या परिवर्तन गर्दा, म्याक्रो कोड सम्पादन गर्न आवश्यक छ (एरे पानानामहरू).
  

तर अन्तमा हामीले वास्तविक पूर्ण पिभोट तालिका पाउँछौं, विभिन्न पानाहरूबाट धेरै दायराहरूमा निर्मित:

Voilà!

प्राविधिक नोट: यदि तपाईंले म्याक्रो चलाउँदा "प्रदायक दर्ता छैन" जस्तो त्रुटि पाउनुभयो भने, सम्भवतः तपाइँसँग एक्सेलको 64-बिट संस्करण छ वा Office को अपूर्ण संस्करण स्थापना भएको छ (पहुँच छैन)। स्थिति ठीक गर्न, म्याक्रो कोडमा टुक्रा बदल्नुहोस्:

	 प्रदायक=Microsoft.Jet.OLEDB.4.0;  

लाई:

	प्रदायक=Microsoft.ACE.OLEDB.12.0;  

र माइक्रोसफ्ट वेबसाइटबाट पहुँचबाट नि:शुल्क डाटा प्रोसेसिङ इन्जिन डाउनलोड गरी स्थापना गर्नुहोस् - Microsoft Access Database Engine 2010 Redistributable

विधि 3: एक्सेलको पुरानो संस्करणहरूबाट PivotTable विजार्डलाई समेकित गर्नुहोस्

यो विधि अलि पुरानो छ, तर अझै पनि उल्लेख लायक छ। औपचारिक रूपमा भन्नुपर्दा, 2003 सम्म र सहित सबै संस्करणहरूमा, PivotTable Wizard मा "धेरै समेकन दायराहरूको लागि पिभोट निर्माण" गर्ने विकल्प थियो। यद्यपि, यस तरिकाले निर्माण गरिएको रिपोर्ट, दुर्भाग्यवश, वास्तविक पूर्ण सारांशको केवल एक दयनीय झल्को मात्र हुनेछ र परम्परागत पिभोट तालिकाहरूको धेरै "चिप्स" लाई समर्थन गर्दैन:

यस्तो पिभोटमा, फिल्ड सूचीमा कुनै स्तम्भ शीर्षकहरू छैनन्, त्यहाँ कुनै लचिलो संरचना सेटिङ छैन, प्रयोग गरिएका प्रकार्यहरूको सेट सीमित छ, र, सामान्यतया, यो सबै पिभोट तालिकासँग धेरै मिल्दोजुल्दो छैन। सायद यसैले गर्दा, 2007 मा सुरु गर्दै, माइक्रोसफ्टले पिभोट तालिका रिपोर्टहरू सिर्जना गर्दा मानक संवादबाट यो प्रकार्य हटायो। अब यो सुविधा कस्टम बटन मार्फत मात्र उपलब्ध छ PivotTable विजार्ड(पिभोट टेबल विजार्ड), जुन, यदि चाहियो भने, द्रुत पहुँच उपकरणपट्टी मार्फत थप्न सकिन्छ फाइल - विकल्पहरू - द्रुत पहुँच उपकरणपट्टी अनुकूलित गर्नुहोस् - सबै आदेशहरू (फाइल - विकल्पहरू - द्रुत पहुँच उपकरणपट्टी अनुकूलित गर्नुहोस् - सबै आदेशहरू):

थपिएको बटनमा क्लिक गरेपछि, तपाईंले विजार्डको पहिलो चरणमा उपयुक्त विकल्प चयन गर्न आवश्यक छ:

र त्यसपछि अर्को विन्डोमा, पालैपालो प्रत्येक दायरा चयन गर्नुहोस् र यसलाई सामान्य सूचीमा थप्नुहोस्:

तर, फेरि, यो पूर्ण-विस्तारित सारांश होइन, त्यसैले यसबाट धेरै आशा नगर्नुहोस्। म यो विकल्प धेरै साधारण अवस्थामा मात्र सिफारिस गर्न सक्छु।

• PivotTables को साथ रिपोर्टहरू सिर्जना गर्दै
• PivotTables मा गणना सेट अप गर्नुहोस्
• म्याक्रोहरू के हुन्, तिनीहरूलाई कसरी प्रयोग गर्ने, VBA कोड कहाँ प्रतिलिपि गर्ने, आदि।
• धेरै पानाहरूबाट एकमा डाटा सङ्कलन (PLEX एड-अन)