तालिकालाई पानाहरूमा विभाजन गर्दै

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

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

यसलाई कसरी लागू गर्न सकिन्छ भनेर नजिकबाट हेरौं।

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

हामीसँग प्रारम्भिक डेटाको रूपमा बिक्रीको लागि 5000 पङ्क्तिहरू भन्दा बढीको आकारको तालिका छ:

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

तयारी गर्नुहोस्

म्याक्रो कोडलाई जटिल नबनाउन र यसलाई सम्भव भएसम्म बुझ्नको लागि सजिलो बनाउनको लागि, केही तयारीका चरणहरू गरौं।

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

एक्सेलमा नयाँ पानाहरू पूर्वनिर्धारित रूपमा हालको (अघिल्लो) पहिले (बायाँ तर्फ) सिर्जना गरिएको हुनाले, यो डाइरेक्टरीमा रहेका शहरहरूलाई घट्दो क्रममा (Z देखि A) मा क्रमबद्ध गर्नु पनि अर्थपूर्ण हुन्छ - त्यसपछि सिर्जना पछि, शहर पानाहरू वर्णमाला अनुसार व्यवस्थित गरिनेछ।

दोस्रो, यो пदुबै तालिकाहरूलाई गतिशीलमा रूपान्तरण गर्नुहोस् ("स्मार्ट") तिनीहरूसँग काम गर्न सजिलो बनाउन। हामी आदेश प्रयोग गर्छौं गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा) वा किबोर्ड सर्टकट Ctrl+T। देखिने ट्याबमा कन्स्ट्रक्टर (डिजाईन) तिनीहरूलाई कल गरौं tablProdaji и टेबलसिटीक्रमशः

विधि 1. पानाहरू द्वारा विभाजनको लागि म्याक्रो

उन्नत ट्याबमा विकासकर्ता (विकासकर्ता) बटन मा क्लिक गर्नुहोस् दृश्य मूल वा किबोर्ड सर्टकट प्रयोग गर्नुहोस् alt+F11। खुल्ने म्याक्रो सम्पादक विन्डोमा, मेनु मार्फत नयाँ खाली मोड्युल घुसाउनुहोस् घुसाउनुहोस् - मोड्युल र त्यहाँ निम्न कोड प्रतिलिपि गर्नुहोस्:

Sub Splitter() दायरा ("таблГорода") दायरा ("таблПродажи") मा प्रत्येक कक्षको लागि।AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]")।SpecialCells(xlCellCellTy)। पानाहरू।Add ActiveSheet।Taste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit अर्को सेल कार्यपत्रहरू("Данные")।ShowAllData End Sub	  

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

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

विधि २. पावर क्वेरीमा धेरै प्रश्नहरू सिर्जना गर्नुहोस्

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

यस अवस्थामा म्याक्रो आंशिक रूपमा अघिल्लोसँग समान छ (यसको चक्र पनि छ प्रत्येकको लागि ... अर्को डाइरेक्टरीमा शहरहरूमा पुनरावृत्ति गर्न), तर लुप भित्र अब फिल्टरिङ र प्रतिलिपि हुनेछैन, तर पावर क्वेरी क्वेरी सिर्जना गरी नयाँ पानामा यसको नतिजाहरू अपलोड गर्नुहोस्:

Sub Splitter2() दायरा ("शहर तालिका") मा प्रत्येक कक्षको लागि ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & "source = Excel.CurrentWorkbook(){[Name=""TableSales""][सामग्री]," & Chr(13) & "" & Chr(10) & "#""परिवर्तित प्रकार"" = Table.TransformColumnTypes(स्रोत , {{""श्रेणी"", टाइप टेक्स्ट}, {""नाम"", टाइप टेक्स्ट}, {""शहर"", टाइप टेक्स्ट}, {""प्रबन्धक"", टाइप टेक्स्ट}, {""डिल मिति "", मिति समय टाइप गर्नुहोस्}, {""लागत"", प्रकार नम्बर}})," र Chr(13) र "" र Chr(10) र "#""फिल्टर लागू भएका पङ्क्तिहरू"" = तालिका हेर्नुहोस् " & _ "lectRows(#""परिवर्तन गरिएको प्रकार"", प्रत्येक ([शहर] = """ र कक्ष। मान र """))" र Chr(13) र "" र Chr(10) र "in " & Chr(13) & "" & Chr(10) & " #""फिल्टर लागू भएको पङ्क्तिहरू प्रदायक =Microsoft.Mashup.OleDb.0;डेटा स्रोत=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , गन्तव्य:=Range("$A$1"))। QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlPassertSword. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 1 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name=Name=cell।  

यसको सुरुवात पछि, हामी शहर द्वारा समान पानाहरू देख्नेछौं, तर पहिले नै सिर्जना गरिएको पावर क्वेरी प्रश्नहरूले तिनीहरूलाई गठन गर्नेछ:

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

• म्याक्रोहरू के हुन्, तिनीहरूलाई कसरी सिर्जना गर्ने र प्रयोग गर्ने
• कार्यपुस्तिका पानाहरू अलग फाइलहरूको रूपमा बचत गर्दै
• पुस्तकका सबै पानाहरूबाट डाटा सङ्कलन गर्दै एउटा तालिकामा