მაგიდის ფურცლებად დაყოფა

Microsoft Excel-ს აქვს მრავალი ინსტრუმენტი მონაცემთა შეგროვებისთვის რამდენიმე ცხრილიდან (სხვადასხვა ფურცლებიდან ან სხვადასხვა ფაილებიდან): პირდაპირი ბმულები, ფუნქცია. არაპირდაპირი (არაპირდაპირი), Power Query და Power Pivot დანამატები და ა.შ. ბარიკადის ამ მხრიდან ყველაფერი კარგად გამოიყურება.

მაგრამ თუ შებრუნებულ პრობლემას წააწყდებით - მონაცემების გავრცელება ერთი ცხრილიდან სხვადასხვა ფურცლებზე - მაშინ ყველაფერი გაცილებით სევდიანი იქნება. ამ დროისთვის, Excel-ის არსენალში, სამწუხაროდ, არ არსებობს ცივილიზებული ჩაშენებული ინსტრუმენტები მონაცემთა ასეთი გამიჯვნისთვის. ასე რომ, თქვენ მოგიწევთ გამოიყენოთ მაკრო Visual Basic-ში, ან გამოიყენოთ მაკრო ჩამწერი + Power Query კომბინაცია მცირე „ფაილის დახვეწის“ შემდეგ.

მოდით უფრო დეტალურად განვიხილოთ, თუ როგორ შეიძლება ეს განხორციელდეს.

პრობლემის ფორმულირება

ჩვენ გვაქვს საწყის მონაცემად ასეთი ცხრილი 5000-ზე მეტი მწკრივის ზომით გასაყიდად:

დავალება: ამ ცხრილიდან მონაცემების განაწილება ამ წიგნის ცალკეულ ფურცლებზე ქალაქების მიხედვით. იმათ. გამოსავალზე, თქვენ უნდა მიიღოთ თითოეულ ფურცელზე მხოლოდ ის რიგები ცხრილიდან, სადაც გაყიდვა იყო შესაბამის ქალაქში:

მომზადება

იმისათვის, რომ მაკრო კოდი არ გავართულოთ და რაც შეიძლება მარტივი გაგება არ მოხდეს, მოდით შევასრულოთ რამდენიმე მოსამზადებელი ნაბიჯი.

პირველი, შექმენით ცალკე საძიებო ცხრილი, სადაც ერთი სვეტი ჩამოთვლის ყველა ქალაქს, რომლისთვისაც გსურთ ცალკე ფურცლების შექმნა. რა თქმა უნდა, ეს დირექტორია შეიძლება არ შეიცავდეს წყაროს მონაცემებში არსებულ ყველა ქალაქს, მაგრამ მხოლოდ მათ, რისთვისაც გვჭირდება ანგარიშები. ასეთი ცხრილის შესაქმნელად უმარტივესი გზაა ბრძანების გამოყენება მონაცემები - წაშალეთ დუბლიკატები (მონაცემები - წაშალეთ დუბლიკატები) სვეტის ასლისთვის ქალაქი ან ფუნქცია უნიკალური (უნიკალური) – თუ გაქვთ Excel 365-ის უახლესი ვერსია.

ვინაიდან ახალი ფურცლები Excel-ში ნაგულისხმევად იქმნება მიმდინარე (წინა) ფურცლის წინ (მარცხნივ), ასევე აზრი აქვს ამ დირექტორიაში ქალაქების დახარისხებას კლებადობით (Z-დან A-მდე) – შემდეგ შექმნის შემდეგ ქალაქი. ფურცლები დალაგდება ანბანის მიხედვით.

მეორე, пგადაიყვანეთ ორივე ცხრილი დინამიურად („ჭკვიანი“), რათა გაადვილდეს მათთან მუშაობა. ჩვენ ვიყენებთ ბრძანებას მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი) ან კლავიატურის მალსახმობი Ctrl+T. ჩანართზე, რომელიც გამოჩნდება კონსტრუქტორი (დიზაინი) დავუძახოთ მათ ტაბლპროდაჯი и TableCity, შესაბამისად:

მეთოდი 1. მაკრო ფურცლებზე დაყოფისთვის

Advanced ჩანართზე დეველოპერი (დეველოპერი) დააჭირეთ ღილაკს Visual Basic ან გამოიყენეთ კლავიატურის მალსახმობი Alt+F11. მაკრო რედაქტორის ფანჯარაში, რომელიც იხსნება, მენიუში ჩადეთ ახალი ცარიელი მოდული ჩასმა – მოდული და დააკოპირეთ შემდეგი კოდი იქ:

Sub Splitter() თითოეული უჯრედისთვის Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).ასლი. Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit შემდეგი უჯრედის სამუშაო ფურცლები("Данные").ShowAllData ბოლოს ქვე	  

აქ მარყუჟით თითოეული… შემდეგი განხორციელდა გავლა დირექტორიაში უჯრედების მეშვეობით TableCity, სადაც თითოეული ქალაქისთვის არის გაფილტრული (მეთოდი ავტომატური ფილტრი) გაყიდვების თავდაპირველ ცხრილში და შემდეგ შედეგების კოპირება ახლად შექმნილ ფურცელზე. გზაში შექმნილ ფურცელს ეწოდა ქალაქის ამავე სახელწოდება და მასზე ირთვება სილამაზისთვის სვეტების სიგანის ავტომატური მორგება.

თქვენ შეგიძლიათ გაუშვათ შექმნილი მაკრო Excel-ში ჩანართზე დეველოპერი ღილაკს Macros (დეველოპერი - მაკრო) ან კლავიატურის მალსახმობი Alt+F8.

მეთოდი 2. შექმენით მრავალი მოთხოვნა Power Query-ში

წინა მეთოდს, მთელი თავისი კომპაქტურობითა და სიმარტივით, აქვს მნიშვნელოვანი ნაკლი - მაკროს მიერ შექმნილი ფურცლები არ განახლდება, როდესაც ცვლილებები ხდება ორიგინალური გაყიდვების ცხრილში. თუ ფრენის განახლება აუცილებელია, მაშინ მოგიწევთ გამოიყენოთ VBA + Power Query პაკეტი, უფრო სწორად, შექმნათ მაკრო გამოყენებით არა მხოლოდ სტატიკური მონაცემების ფურცლები, არამედ განახლებული Power Query მოთხოვნები.

მაკრო ამ შემთხვევაში ნაწილობრივ მსგავსია წინა (მას ასევე აქვს ციკლი თითოეული… შემდეგი დირექტორიაში ქალაქების გამეორება), მაგრამ მარყუჟის შიგნით აღარ იქნება გაფილტვრა და კოპირება, არამედ შექმნის Power Query მოთხოვნას და ატვირთავს მის შედეგებს ახალ ფურცელზე:

Sub Splitter2() დიაპაზონის თითოეული უჯრედისთვის ("ქალაქის ცხრილი") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " წყარო = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""შეცვლილი ტიპი"" = Table.TransformColumnTypes(წყარო , {{""კატეგორია"", აკრიფეთ ტექსტი}, {""სახელი"", აკრიფეთ ტექსტი}, {""ქალაქი"", აკრიფეთ ტექსტი}, {""მენეჯერი"", აკრიფეთ ტექსტი}, {""გარიგება" date "", აკრიფეთ datetime}, {""Cost"", აკრიფეთ ნომერი}})," & Chr(13) & "" & Chr(10) & " #""სტრიქონები გამოყენებული ფილტრით"" = Table.Se " & _ "lectRows(#""შეცვლილი ტიპი"", თითოეული ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""მწკრივები გამოყენებული ფილტრით""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; პროვაიდერი =Microsoft.Mashup.OleDb.1;მონაცემთა წყარო=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertwordSeleaveseCells. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.  

მისი გაშვების შემდეგ, ჩვენ ვნახავთ იგივე ფურცლებს ქალაქების მიხედვით, მაგრამ უკვე შექმნილი Power Query შეკითხვები ჩამოაყალიბებს მათ:

წყაროს მონაცემების ნებისმიერი ცვლილების შემთხვევაში, საკმარისი იქნება შესაბამისი ცხრილის განახლება მაუსის მარჯვენა ღილაკით - ბრძანება განახლება და შენახვა (განახლება) ან განაახლეთ ყველა ქალაქი ერთდროულად ღილაკის გამოყენებით Ყველაფრის განახლება tab თარიღი (მონაცემები — ყველას განახლება).

• რა არის მაკროები, როგორ შევქმნათ და გამოიყენოთ ისინი
• სამუშაო წიგნის ფურცლების შენახვა ცალკე ფაილებად
• წიგნის ყველა ფურცლიდან მონაცემების შეგროვება ერთ ცხრილში