एकाधिक डेटा श्रेणियों में पिवट तालिका

समस्या का निरूपण

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

शुरू करने से पहले, आइए कुछ बिंदुओं को स्पष्ट करें। एक प्राथमिकता, मेरा मानना ​​है कि हमारे डेटा में निम्नलिखित शर्तें पूरी होती हैं:

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

विधि 1: Power Query का उपयोग करके पिवट के लिए तालिकाएँ बनाएँ

एक्सेल के लिए 2010 संस्करण से शुरू होकर, एक मुफ्त पावर क्वेरी ऐड-इन है जो किसी भी डेटा को एकत्र और परिवर्तित कर सकता है और फिर इसे पिवट टेबल बनाने के लिए एक स्रोत के रूप में दे सकता है। इस ऐड की मदद से हमारी समस्या का समाधान करना बिल्कुल भी मुश्किल नहीं है।

सबसे पहले, एक्सेल में एक नई खाली फाइल बनाते हैं - उसमें असेंबली होगी और फिर उसमें एक पिवट टेबल बनाई जाएगी।

फिर टैब पर जानकारी (यदि आपके पास एक्सेल 2016 या बाद का संस्करण है) या टैब पर पावर क्वेरी (यदि आपके पास एक्सेल 2010-2013 है) कमांड का चयन करें क्वेरी बनाएँ - फ़ाइल से - एक्सेल (डेटा प्राप्त करें - फ़ाइल से - एक्सेल) और एकत्रित की जाने वाली तालिकाओं के साथ स्रोत फ़ाइल निर्दिष्ट करें:

दिखाई देने वाली विंडो में, कोई भी शीट चुनें (इससे कोई फ़र्क नहीं पड़ता) और नीचे दिए गए बटन को दबाएं परिवर्तन (संपादित):

Power Query Query Editor विंडो एक्सेल के ऊपर खुलनी चाहिए। पैनल पर विंडो के दाईं ओर अनुरोध पैरामीटर पहले को छोड़कर सभी स्वचालित रूप से बनाए गए चरणों को हटा दें - स्रोत (स्रोत):

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

कॉलम को छोड़कर सभी कॉलम हटाएं जानकारीकॉलम शीर्षक पर राइट-क्लिक करके और चयन करके अन्य कॉलम हटाएं (हटाना अन्य कॉलम):

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

यदि आपने सब कुछ सही ढंग से किया है, तो इस बिंदु पर आपको एक के नीचे एक एकत्रित सभी तालिकाओं की सामग्री देखनी चाहिए:

यह बटन के साथ तालिका शीर्षलेख में पहली पंक्ति को ऊपर उठाने के लिए बनी हुई है हेडर के रूप में पहली पंक्ति का प्रयोग करें (शीर्षक के रूप में पहली पंक्ति का प्रयोग करें) टैब होम (घर) और फ़िल्टर का उपयोग करके डेटा से डुप्लिकेट तालिका शीर्षलेख निकालें:

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

हर चीज़। यह केवल एक सारांश बनाने के लिए बनी हुई है। ऐसा करने के लिए, टैब पर जाएं सम्मिलित करें - पिवोटटेबल (सम्मिलित करें - पिवट टेबल), विकल्प चुनें बाहरी डेटा स्रोत का उपयोग करें (बाहरी डेटा स्रोत का उपयोग करें)और फिर बटन पर क्लिक करके चयन कनेक्शन, हमारे आग्रह। पिवट का आगे निर्माण और विन्यास पूरी तरह से मानक तरीके से होता है, जो हमें आवश्यक फ़ील्ड को पंक्तियों, स्तंभों और मान क्षेत्र में खींचकर होता है:

यदि भविष्य में स्रोत डेटा बदलता है या कुछ और स्टोर शीट जोड़े जाते हैं, तो यह कमांड का उपयोग करके क्वेरी और हमारे सारांश को अपडेट करने के लिए पर्याप्त होगा सभी को रीफ्रेश करें टैब जानकारी (डेटा - सभी को ताज़ा करें).

विधि 2. हम मैक्रो में UNION SQL कमांड के साथ तालिकाओं को जोड़ते हैं

हमारी समस्या का एक अन्य समाधान इस मैक्रो द्वारा दर्शाया गया है, जो कमांड का उपयोग करके पिवट टेबल के लिए एक डेटा सेट (कैश) बनाता है एकता एसक्यूएल क्वेरी भाषा। यह आदेश सरणी में निर्दिष्ट सभी से तालिकाओं को जोड़ता है शीटनाम एक डेटा तालिका में पुस्तक की शीट। यानी अलग-अलग शीट से लेकर एक तक की रेंज को फिजिकली कॉपी और पेस्ट करने के बजाय हम कंप्यूटर की रैम में भी ऐसा ही करते हैं। फिर मैक्रो दिए गए नाम के साथ एक नई शीट जोड़ता है (चर) परिणाम पत्रकनाम) और एकत्रित कैश के आधार पर उस पर एक पूर्ण (!) सारांश बनाता है।

मैक्रो का उपयोग करने के लिए, टैब पर विजुअल बेसिक बटन का उपयोग करें विकासक (डेवलपर) या कीबोर्ड शॉर्टकट ऑल्ट+F11. फिर हम मेनू के माध्यम से एक नया खाली मॉड्यूल डालें सम्मिलित करें - मॉड्यूल और निम्नलिखित कोड को वहां कॉपी करें:

Sub New_Multi_Table_Pivot() Dim i as Long Dim arSQL() स्ट्रिंग डिम objPivotCache के रूप में PivotCache Dim objRS के रूप में ऑब्जेक्ट डिम रिजल्टशीट नाम स्ट्रिंग डिम शीट्स के रूप में वैरिएंट 'शीट नाम के रूप में नाम जहां परिणामी पिवट प्रदर्शित किया जाएगा परिणामशीटनाम = "पिवट" 'शीट की एक सरणी स्रोत तालिका के साथ नाम पत्रक नाम = ऐरे ("अल्फा", "बीटा", "गामा", "डेल्टा") 'हम शीट्स से तालिकाओं के लिए एक कैश बनाते हैं ActiveWorkbook ReDim arSQL के साथ नाम (1 To (UBound(SheetsNames) + 1) ) i = LBound (शीटनाम) के लिए UBound(SheetsNames) arSQL(i + 1) = "चुनें * से [" और शीट्सनाम (i) और "$]" अगला मैं objRS = CreateObject ("ADODB.Recordset") objRS सेट करता हूं .Open Join$(arSQL, "UNION ALL"), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) परिणामी पिवट तालिका प्रदर्शित करने के लिए शीट को फिर से बनाएं के साथ समाप्त करें त्रुटि फिर से शुरू करें अगला एप्लिकेशन। डिस्प्ले अलर्ट = झूठी वर्कशीट्स (परिणामशीटनाम)। हटाएं सेट wsPivot = वर्कशीट्स। wsPivo जोड़ें टी। नाम = रिजल्टशीटनाम 'इस शीट पर उत्पन्न कैश सारांश प्रदर्शित करें सेट objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) सेट objPivotCache.Recordset = objRS सेट objRS = wsPivot objPivotCache के साथ कुछ भी नहीं। बनाएंPivotTable TableDestination:=wsPivot.Range सेट करें objPivotCache = कुछ भी नहीं रेंज ("A3")। एंड सब के साथ एंड का चयन करें    

फिर समाप्त मैक्रो को कीबोर्ड शॉर्टकट के साथ चलाया जा सकता है ऑल्ट+F8 या टैब पर मैक्रोज़ बटन विकासक (डेवलपर - मैक्रोज़).

इस दृष्टिकोण के विपक्ष:

• डेटा अपडेट नहीं किया गया है क्योंकि कैश का स्रोत तालिकाओं से कोई संबंध नहीं है। यदि आप स्रोत डेटा बदलते हैं, तो आपको मैक्रो को फिर से चलाना होगा और फिर से सारांश बनाना होगा।
• चादरों की संख्या बदलते समय, मैक्रो कोड (सरणी .) को संपादित करना आवश्यक है शीटनाम).
  

लेकिन अंत में हमें एक वास्तविक पूर्ण धुरी तालिका मिलती है, जिसे विभिन्न शीटों से कई श्रेणियों पर बनाया जाता है:

देखा!

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

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

करने के लिए:

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

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

विधि 3: Excel के पुराने संस्करणों से PivotTable विज़ार्ड को समेकित करें

यह विधि थोड़ी पुरानी है, लेकिन फिर भी ध्यान देने योग्य है। औपचारिक रूप से कहें तो, 2003 तक और सहित सभी संस्करणों में, पिवोटटेबल विजार्ड में "कई समेकन श्रेणियों के लिए एक धुरी का निर्माण" करने का विकल्प था। हालांकि, इस तरह से बनाई गई एक रिपोर्ट, दुर्भाग्य से, वास्तविक पूर्ण सारांश का केवल एक दयनीय समानता होगी और पारंपरिक पिवट टेबल के कई "चिप्स" का समर्थन नहीं करती है:

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

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

और फिर अगली विंडो में, बारी-बारी से प्रत्येक श्रेणी का चयन करें और इसे सामान्य सूची में जोड़ें:

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

• PivotTables के साथ रिपोर्ट बनाना
• PivotTables में परिकलन सेट करें
• मैक्रोज़ क्या हैं, उनका उपयोग कैसे करें, वीबीए कोड कहां कॉपी करें, आदि।
• एकाधिक शीट से एक में डेटा संग्रह (PLEX ऐड-ऑन)