अनेक डेटा श्रेणींमध्ये मुख्य सारणी

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

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

आपण प्रारंभ करण्यापूर्वी, आपण काही मुद्दे स्पष्ट करूया. प्रथम, माझा विश्वास आहे की आमच्या डेटामध्ये खालील अटी पूर्ण केल्या आहेत:

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

पद्धत 1: पॉवर क्वेरी वापरून पिव्होटसाठी टेबल तयार करा

एक्सेलच्या 2010 च्या आवृत्तीपासून प्रारंभ करून, एक विनामूल्य पॉवर क्वेरी अॅड-इन आहे जो कोणताही डेटा संकलित आणि रूपांतरित करू शकतो आणि नंतर तो मुख्य सारणी तयार करण्यासाठी स्त्रोत म्हणून देऊ शकतो. या अॅड-इनच्या मदतीने आमची समस्या सोडवणे अजिबात अवघड नाही.

प्रथम, एक्सेलमध्ये एक नवीन रिकामी फाईल तयार करू - त्यात असेंब्ली होईल आणि नंतर त्यात एक पिव्होट टेबल तयार होईल.

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

दिसत असलेल्या विंडोमध्ये, कोणतेही पत्रक निवडा (कोणते फरक पडत नाही) आणि खालील बटण दाबा बदल (सुधारणे):

Power Query Query Editor विंडो Excel च्या वर उघडली पाहिजे. पटलावरील खिडकीच्या उजव्या बाजूला मापदंडांची विनंती करा प्रथम वगळता सर्व स्वयंचलितपणे तयार केलेल्या चरण हटवा - स्रोत (स्त्रोत):

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

स्तंभ वगळता सर्व स्तंभ हटवा डेटास्तंभ शीर्षकावर उजवे-क्लिक करून आणि निवडून इतर स्तंभ हटवा (काढून टाका इतर स्तंभ):

त्यानंतर तुम्ही स्तंभाच्या शीर्षस्थानी असलेल्या दुहेरी बाणावर क्लिक करून गोळा केलेल्या सारण्यांची सामग्री विस्तृत करू शकता (चेकबॉक्स मूळ स्तंभाचे नाव उपसर्ग म्हणून वापरा तुम्ही ते बंद करू शकता):

आपण सर्वकाही योग्यरित्या केले असल्यास, या टप्प्यावर आपण एका खाली एकत्रित केलेल्या सर्व सारण्यांमधील सामग्री पहा:

बटणासह टेबल हेडरवर पहिली पंक्ती वाढवणे बाकी आहे शीर्षलेख म्हणून पहिली ओळ वापरा (शीर्षलेख म्हणून पहिली पंक्ती वापरा) टॅब होम पेज (मुख्यपृष्ठ) आणि फिल्टर वापरून डेटामधून डुप्लिकेट टेबल शीर्षलेख काढा:

कमांडने केलेले सर्व काही जतन करा बंद करा आणि लोड करा - बंद करा आणि लोड करा… (बंद करा आणि लोड करा - बंद करा आणि लोड करा...) टॅब होम पेज (मुख्यपृष्ठ), आणि उघडणाऱ्या विंडोमध्ये, पर्याय निवडा फक्त कनेक्शन (केवळ कनेक्शन):

सर्व काही. हे फक्त सारांश तयार करण्यासाठी राहते. हे करण्यासाठी, टॅबवर जा घाला - PivotTable (घाला — मुख्य सारणी), पर्याय निवडा बाह्य डेटा स्रोत वापरा (बाह्य डेटा स्रोत वापरा)आणि नंतर बटणावर क्लिक करून कनेक्शन निवडा, आमची विनंती. पिव्होटची पुढील निर्मिती आणि कॉन्फिगरेशन पूर्णपणे मानक मार्गाने आम्हाला आवश्यक असलेली फील्ड पंक्ती, स्तंभ आणि मूल्य क्षेत्रामध्ये ड्रॅग करून होते:

भविष्यात स्त्रोत डेटा बदलल्यास किंवा आणखी काही स्टोअर शीट्स जोडल्या गेल्यास, कमांड वापरून क्वेरी आणि आमचा सारांश अद्यतनित करणे पुरेसे असेल. सर्व रिफ्रेश करा टॅब डेटा (डेटा — सर्व रिफ्रेश करा).

पद्धत 2. आम्ही मॅक्रोमध्ये UNION SQL कमांडसह टेबल्स एकत्र करतो

आमच्या समस्येचे दुसरे समाधान या मॅक्रोद्वारे प्रस्तुत केले जाते, जे कमांड वापरून पिव्होट टेबलसाठी डेटा सेट (कॅशे) तयार करते. युनिटी SQL क्वेरी भाषा. हा आदेश अॅरेमध्ये निर्दिष्ट केलेल्या सर्व सारण्या एकत्र करतो शीटनावे पुस्तकाची शीट्स एका डेटा टेबलमध्ये. म्हणजेच, वेगवेगळ्या शीट्सपासून एकापर्यंतच्या श्रेणी भौतिकदृष्ट्या कॉपी आणि पेस्ट करण्याऐवजी, आम्ही संगणकाच्या रॅममध्ये तेच करतो. नंतर मॅक्रो दिलेल्या नावासह एक नवीन शीट जोडते (व्हेरिएबल ResultSheetName) आणि गोळा केलेल्या कॅशेवर आधारित त्यावर पूर्ण (!) सारांश तयार करतो.

मॅक्रो वापरण्यासाठी, टॅबवरील व्हिज्युअल बेसिक बटण वापरा विकसक (विकासक) किंवा कीबोर्ड शॉर्टकट alt+F11. मग आम्ही मेनूद्वारे एक नवीन रिक्त मॉड्यूल समाविष्ट करतो घाला - मॉड्यूल आणि तेथे खालील कोड कॉपी करा:

Sub New_Multi_Table_Pivot() Dim i As long dim arSQL() स्ट्रिंग म्हणून मंद objPivotCache PivotCache म्हणून मंद objRS म्हणून ऑब्जेक्ट मंद ResultSheetName स्ट्रिंग मंद SheetsNames व्हेरिएंट 'शीट नाव म्हणून जेथे परिणामी पिव्होट प्रदर्शित केले जाईल' = "arSheetName" चे परिणाम स्रोत सारण्यांसह नावे SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'आम्ही SheetsNames मधून ActiveWorkbook ReDim arSQL(1 ते (UBound(SheetsNames) + 1) शीट्समधून सारण्यांसाठी कॅशे तयार करतो. ) i = LBound (SheetsNames) to UBound(SheetsNames) arSQL(i + 1) = "सिलेक्ट * FROM [" & SheetsNames(i) & "$]" साठी पुढे मी objRS = CreateObject("ADODB.Recordset") objRS सेट करा .Jin$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName,"; Extended Properties=""Excel 8.0;" उघडा ""), vbNullString ) परिणामी पिव्होट टेबल प्रदर्शित करण्यासाठी पत्रक पुन्हा तयार करा यासह समाप्त करा पुढील ऍप्लिकेशन पुन्हा सुरू करा. DisplayAlerts = खोट्या वर्कशीट्स(ResultSheetName). सेट हटवा wsPivot = Worksheets. wsPivo जोडा ट. Name = ResultSheetName 'या शीटवर व्युत्पन्न केलेला कॅशे सारांश प्रदर्शित करा objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) objPivotCache सेट करा.Recordset = objRS सेट objRS = wsPotetable = "TivCotreables"सह काहीही नाही. objPivotCache = Nothing Range("A3"). End Sub with End निवडा    

तयार झालेला मॅक्रो नंतर कीबोर्ड शॉर्टकटने चालवता येतो alt+F8 किंवा टॅबवरील मॅक्रो बटण विकसक (विकासक — मॅक्रो).

या दृष्टिकोनाचे तोटे:

• डेटा अद्यतनित केलेला नाही कारण कॅशेचे स्त्रोत सारण्यांशी कोणतेही कनेक्शन नाही. तुम्ही स्त्रोत डेटा बदलल्यास, तुम्ही पुन्हा मॅक्रो चालवा आणि सारांश पुन्हा तयार केला पाहिजे.
• पत्रकांची संख्या बदलताना, मॅक्रो कोड संपादित करणे आवश्यक आहे (अॅरे शीटनावे).
  

परंतु शेवटी आम्हाला एक वास्तविक पूर्ण-वाढीव मुख्य सारणी मिळते, जी वेगवेगळ्या शीटमधून अनेक श्रेणींवर तयार केली जाते:

व्होला!

तांत्रिक टीप: मॅक्रो चालवताना तुम्हाला "प्रदाता नोंदणीकृत नाही" सारखी त्रुटी आढळल्यास, बहुधा तुमच्याकडे एक्सेलची 64-बिट आवृत्ती असेल किंवा ऑफिसची अपूर्ण आवृत्ती स्थापित केली असेल (प्रवेश नाही). परिस्थितीचे निराकरण करण्यासाठी, मॅक्रो कोडमधील तुकडा पुनर्स्थित करा:

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

प्रति:

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

आणि मायक्रोसॉफ्ट वेबसाइटवरून ऍक्सेसमधून मोफत डेटा प्रोसेसिंग इंजिन डाउनलोड आणि इंस्टॉल करा - मायक्रोसॉफ्ट ऍक्सेस डेटाबेस इंजिन 2010 पुनर्वितरण करण्यायोग्य

पद्धत 3: Excel च्या जुन्या आवृत्त्यांमधून PivotTable विझार्ड एकत्र करा

ही पद्धत थोडी जुनी आहे, परंतु तरीही उल्लेख करणे योग्य आहे. औपचारिकपणे बोलायचे झाले तर, 2003 पर्यंत आणि त्यासह सर्व आवृत्त्यांमध्ये, PivotTable विझार्डमध्ये "अनेक एकत्रीकरण श्रेणींसाठी एक पिव्होट तयार करण्याचा" पर्याय होता. तथापि, अशा प्रकारे तयार केलेला अहवाल, दुर्दैवाने, वास्तविक पूर्ण सारांशाचे केवळ एक दयनीय स्वरूप असेल आणि पारंपारिक मुख्य सारण्यांच्या अनेक "चिप्स" ला समर्थन देत नाही:

अशा पिव्होटमध्ये, फील्ड सूचीमध्ये कोणतेही स्तंभ शीर्षक नाहीत, कोणतीही लवचिक रचना सेटिंग नाही, वापरलेल्या फंक्शन्सचा संच मर्यादित आहे आणि सर्वसाधारणपणे, हे सर्व मुख्य सारणीसारखे नसते. कदाचित म्हणूनच, 2007 पासून, मायक्रोसॉफ्टने पिव्होट टेबल अहवाल तयार करताना हे कार्य मानक संवादातून काढून टाकले. आता हे वैशिष्ट्य केवळ कस्टम बटणाद्वारे उपलब्ध आहे पिव्होटटेबल विझार्ड(मुख्य सारणी विझार्ड), जे इच्छित असल्यास, द्वारे द्रुत प्रवेश टूलबारमध्ये जोडले जाऊ शकते फाइल - पर्याय - द्रुत प्रवेश टूलबार सानुकूलित करा - सर्व आदेश (फाइल — पर्याय — द्रुत प्रवेश टूलबार सानुकूलित करा — सर्व आदेश):

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

आणि नंतर पुढील विंडोमध्ये, प्रत्येक श्रेणी क्रमाने निवडा आणि सामान्य सूचीमध्ये जोडा:

पण, पुन्हा, हा संपूर्ण सारांश नाही, म्हणून त्याच्याकडून जास्त अपेक्षा करू नका. मी या पर्यायाची शिफारस फक्त अगदी सोप्या प्रकरणांमध्ये करू शकतो.

• PivotTables सह अहवाल तयार करणे
• PivotTables मध्ये गणना सेट करा
• मॅक्रो म्हणजे काय, ते कसे वापरायचे, VBA कोड कुठे कॉपी करायचा इ.
• एकाहून अधिक शीटमधून डेटा संकलन (PLEX अॅड-ऑन)