एक टेबल को शीट में विभाजित करना

Microsoft Excel में कई तालिकाओं (विभिन्न शीटों से या विभिन्न फ़ाइलों से) से डेटा एकत्र करने के लिए कई उपकरण हैं: सीधे लिंक, कार्य अप्रत्यक्ष (अप्रत्यक्ष), Power Query और Power Pivot ऐड-ऑन इत्यादि। बैरिकेड के इस तरफ से, सब कुछ अच्छा दिखता है।

लेकिन अगर आप एक उलटी समस्या में भाग लेते हैं - एक टेबल से अलग-अलग शीट में डेटा फैलाना - तो सब कुछ बहुत दुखद होगा। फिलहाल, एक्सेल के शस्त्रागार में इस तरह के डेटा पृथक्करण के लिए कोई सभ्य अंतर्निहित उपकरण नहीं हैं, दुर्भाग्य से। तो आपको विजुअल बेसिक में मैक्रो का उपयोग करना होगा, या मैक्रो रिकॉर्डर + पावर क्वेरी संयोजन का उपयोग थोड़ा "फ़ाइल शोधन" के साथ करना होगा।

आइए इस पर करीब से नज़र डालें कि इसे कैसे लागू किया जा सकता है।

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

हमारे पास प्रारंभिक डेटा के रूप में ऐसी तालिका है जिसमें बिक्री के लिए 5000 से अधिक पंक्तियों का आकार है:

कार्य: इस तालिका से डेटा को इस पुस्तक के अलग-अलग शीट पर शहर के अनुसार वितरित करना। वे। आउटपुट पर, आपको प्रत्येक शीट पर तालिका से केवल उन पंक्तियों को प्राप्त करने की आवश्यकता होती है जहां बिक्री संबंधित शहर में हुई थी:

तैयार करना

मैक्रो कोड को जटिल न बनाने और इसे जितना संभव हो सके समझने में आसान बनाने के लिए, आइए कुछ प्रारंभिक चरण करें।

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

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

दूसरा, пदोनों तालिकाओं को गतिशील में बदलें ("स्मार्ट") उनके साथ काम करना आसान बनाने के लिए। हम कमांड का उपयोग करते हैं होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें) या कीबोर्ड शॉर्टकट कंट्रोल+T. दिखाई देने वाले टैब पर निर्माता (डिज़ाइन) चलो उन्हें बुलाते हैं टैब्लप्रोदाजी и टेबलसिटी, क्रमशः:

विधि 1. शीट द्वारा विभाजन के लिए मैक्रो

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

सब स्प्लिटर () रेंज में प्रत्येक सेल के लिए ("таблГорода") रेंज ("таблПродажи")। ऑटोफिल्टर फील्ड: = 3, मानदंड 1: = सेल। वैल्यू रेंज ("таблПродажи [# ऑल]")। स्पेशल सेल (xlCellTypeVisible)। कॉपी करें पत्रक। सक्रियशीट जोड़ें।	  

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

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

विधि 2. Power Query में अनेक क्वेरी बनाएँ

पिछली विधि, इसकी सभी कॉम्पैक्टनेस और सादगी के लिए, एक महत्वपूर्ण कमी है - मैक्रो द्वारा बनाई गई शीट्स को अपडेट नहीं किया जाता है जब मूल बिक्री तालिका में परिवर्तन किए जाते हैं। यदि फ़्लाई पर अपडेट करना आवश्यक है, तो आपको VBA + Power Query बंडल का उपयोग करना होगा, या इसके बजाय, मैक्रो का उपयोग करके न केवल स्थिर डेटा के साथ शीट, बल्कि अद्यतन Power Query क्वेरीज़ का उपयोग करना होगा।

इस मामले में मैक्रो आंशिक रूप से पिछले एक के समान है (इसमें एक चक्र भी है प्रत्येक के लिए … अगला निर्देशिका में शहरों पर पुनरावृति करने के लिए), लेकिन लूप के अंदर अब फ़िल्टरिंग और कॉपी नहीं होगी, बल्कि एक Power Query क्वेरी बनाना और उसके परिणामों को एक नई शीट पर अपलोड करना होगा:

सब स्प्लिटर 2 () रेंज में प्रत्येक सेल के लिए ("सिटी टेबल") ActiveWorkbook.Queries। नाम जोड़ें: = सेल। वैल्यू, फॉर्मूला: = _ "चलो" और Chr (13) और "" और Chr (10) और "स्रोत = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & "#""Changed Type"" = Table.TransformColumnTypes(Source , {{""श्रेणी"", टेक्स्ट टाइप करें}, {""नाम"", टेक्स्ट टाइप करें}, {""सिटी"", टेक्स्ट टाइप करें}, {""मैनेजर"", टेक्स्ट टाइप करें}, {""डील दिनांक "", प्रकार datetime}, {""लागत"", प्रकार संख्या}})," और Chr(13) और "" और Chr(10) और "#""फ़िल्टर के साथ पंक्तियाँ लागू"" = Table.Se "& _"lectRows(#""बदला हुआ प्रकार"", प्रत्येक ([शहर] = """ और सेल। मान और """))" और Chr(13) और "" और Chr(10) और "में " & Chr(13) & "" & Chr(10) & "#""पंक्तियों के साथ फ़िल्टर लागू""" ActiveWorkbook.Worksheets.Add with ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; प्रदाता =Microsoft.Mashup.OleDb.1;डेटा स्रोत=$कार्यपुस्तिका$;स्थान=" और सेल.वैल्यू और ";विस्तारित गुण=""""" _ , गंतव्य:=रेंज("$A$1"))। QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False। सेवडाटा = ट्रू  

इसके लॉन्च के बाद, हम शहर के अनुसार समान पत्रक देखेंगे, लेकिन पहले से बनाई गई Power Query क्वेरी उन्हें बनाएगी:

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

• मैक्रोज़ क्या हैं, कैसे बनाएं और उनका उपयोग कैसे करें
• कार्यपुस्तिका शीट को अलग फाइलों के रूप में सहेजना
• पुस्तक की सभी शीटों से डेटा को एक तालिका में एकत्रित करना