पत्रके मध्ये एक टेबल विभाजित करणे

मायक्रोसॉफ्ट एक्सेलमध्ये अनेक सारण्यांमधून (वेगवेगळ्या शीट्स किंवा वेगवेगळ्या फाइल्समधून) डेटा गोळा करण्यासाठी अनेक साधने आहेत: थेट लिंक्स, फंक्शन अप्रत्यक्ष (अप्रसिद्ध), पॉवर क्वेरी आणि पॉवर पिव्होट अॅड-ऑन इ. बॅरिकेडच्या या बाजूने, सर्वकाही चांगले दिसते.

परंतु जर तुम्हाला उलट समस्या आली - एका टेबलवरून वेगवेगळ्या शीटमध्ये डेटा पसरवणे - तर सर्व काही अधिक दुःखदायक होईल. या क्षणी, दुर्दैवाने, एक्सेलच्या शस्त्रागारात अशा डेटा विभक्त करण्यासाठी कोणतीही सभ्य अंगभूत साधने नाहीत. त्यामुळे तुम्हाला Visual Basic मध्ये मॅक्रो वापरावे लागेल किंवा नंतर थोडे “फाइल रिफाइनमेंट” सह मॅक्रो रेकॉर्डर + पॉवर क्वेरी संयोजन वापरावे लागेल.

याची अंमलबजावणी कशी करता येईल यावर बारकाईने नजर टाकूया.

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

आमच्याकडे विक्रीसाठी 5000 पेक्षा जास्त पंक्तींच्या आकारासह अशा सारणीचा प्रारंभिक डेटा आहे:

कार्य: या टेबलमधील डेटा या पुस्तकाच्या स्वतंत्र शीटवर शहरानुसार वितरित करणे. त्या. आउटपुटवर, आपल्याला प्रत्येक शीटवर फक्त त्या पंक्ती टेबलमधून मिळणे आवश्यक आहे जिथे विक्री संबंधित शहरात होती:

तयार करा

मॅक्रो कोड क्लिष्ट होऊ नये आणि शक्य तितके समजण्यास सोपे बनविण्यासाठी, चला दोन तयारी चरणे करूया.

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

एक्सेलमधील नवीन पत्रके सध्याच्या (मागील) आधी (मागील) डीफॉल्टनुसार तयार केलेली असल्याने, या निर्देशिकेतील शहरांची उतरत्या क्रमाने (Z ते A पर्यंत) क्रमवारी लावणे देखील अर्थपूर्ण आहे - नंतर निर्मितीनंतर, शहर पत्रके वर्णक्रमानुसार लावली जातील.

दुसरा, द пदोन्ही सारण्या डायनॅमिकमध्ये रूपांतरित करा (“स्मार्ट”) त्यांच्यासोबत काम करणे सोपे करण्यासाठी. आम्ही कमांड वापरतो मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित) किंवा कीबोर्ड शॉर्टकट Ctrl+T. दिसणार्‍या टॅबवर रचनाकार (डिझाइन) चला त्यांना कॉल करूया tablProdaji и टेबलसिटीअनुक्रमे:

पद्धत 1. शीट्सद्वारे विभागण्यासाठी मॅक्रो

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

श्रेणीतील प्रत्येक सेलसाठी सब स्प्लिटर()("таблГорода") श्रेणी("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlispyColly). पत्रके.ActiveSheet जोडा.ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit पुढील सेल वर्कशीट्स("Данные").ShowAllData End Sub	  

येथे लूपसह प्रत्येकासाठी … पुढील डिरेक्टरीच्या सेलमधून रस्ता अंमलात आणला टेबलसिटी, जेथे प्रत्येक शहरासाठी ते फिल्टर केले जाते (पद्धत ऑटोफिल्टर) मूळ विक्री सारणीमध्ये आणि नंतर नवीन तयार केलेल्या शीटमध्ये निकाल कॉपी करणे. वाटेत, तयार केलेल्या शीटचे शहराच्या त्याच नावावर पुनर्नामित केले जाते आणि सौंदर्यासाठी स्तंभांच्या रुंदीचे स्वयं-फिटिंग चालू केले जाते.

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

पद्धत 2. पॉवर क्वेरीमध्ये एकाधिक क्वेरी तयार करा

मागील पद्धती, त्याच्या सर्व कॉम्पॅक्टनेस आणि साधेपणासाठी, एक लक्षणीय कमतरता आहे - मूळ विक्री सारणीमध्ये बदल केल्यावर मॅक्रोद्वारे तयार केलेली पत्रके अद्यतनित केली जात नाहीत. फ्लायवर अपडेट करणे आवश्यक असल्यास, तुम्हाला VBA + Power Query बंडल वापरावे लागेल, किंवा त्याऐवजी, मॅक्रो वापरून केवळ स्थिर डेटासह शीट्सच नव्हे तर अपडेट केलेल्या Power Query क्वेरी वापरून तयार करा.

या प्रकरणातील मॅक्रो अंशतः मागील प्रमाणेच आहे (त्यात एक चक्र देखील आहे प्रत्येकासाठी … पुढील निर्देशिकेतील शहरांवर पुनरावृत्ती करण्यासाठी), परंतु लूपमध्ये यापुढे फिल्टरिंग आणि कॉपी केले जाणार नाही, परंतु पॉवर क्वेरी क्वेरी तयार करणे आणि त्याचे परिणाम नवीन शीटवर अपलोड करणे:

श्रेणीतील प्रत्येक सेलसाठी Sub Splitter2() ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "चल" & Chr(13) & "" & Chr(10) & " स्रोत = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & "#""Changed Type"" = Table.TransformColumnTypes(स्रोत , {{""श्रेणी"", मजकूर टाइप करा}, {""नाव"", मजकूर टाइप करा}, {""शहर"", मजकूर टाइप करा}, {""व्यवस्थापक"", मजकूर टाइप करा}, {""डील" तारीख "", datetime टाइप करा}, {""किंमत"", प्रकार क्रमांक}})," आणि Chr(13) आणि "" आणि Chr(10) आणि "#""फिल्टर लागू केलेल्या पंक्ती"" = टेबल पहा " & _ "lectRows(#""बदललेला प्रकार"", प्रत्येक ([शहर] = """ & cell. Value & """))" & 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 = xlPassert .Falseword. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 1 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name.Name = पुढील सेल .  

लाँच केल्यानंतर, आम्ही शहरानुसार समान पत्रके पाहू, परंतु आधीच तयार केलेल्या पॉवर क्वेरी क्वेरी त्या तयार करतील:

स्त्रोत डेटामधील कोणत्याही बदलांसह, योग्य माऊस बटण - कमांडसह संबंधित सारणी अद्यतनित करणे पुरेसे असेल अपडेट आणि सेव्ह करा (रिफ्रेश) किंवा बटण वापरून एकाच वेळी सर्व शहरे मोठ्या प्रमाणात अद्यतनित करा सर्व अद्यतनित करा टॅब डेटा (डेटा — सर्व रिफ्रेश करा).

• मॅक्रो काय आहेत, ते कसे तयार करावे आणि कसे वापरावे
• वर्कबुक शीट्स वेगळ्या फाइल्स म्हणून सेव्ह करत आहे
• पुस्तकाच्या सर्व शीट्समधील डेटा एका टेबलमध्ये गोळा करणे