Սեղանը թերթիկների բաժանելը

Microsoft Excel-ն ունի բազմաթիվ գործիքներ մի քանի աղյուսակներից (տարբեր թերթերից կամ տարբեր ֆայլերից) տվյալներ հավաքելու համար՝ ուղիղ հղումներ, գործառույթ: Անկախ (Անուղղակի), Power Query և Power Pivot հավելումներ և այլն: Բարիկադի այս կողմից ամեն ինչ լավ է թվում:

Բայց եթե հանդիպեք հակադարձ խնդրի՝ տվյալների տարածում մեկ աղյուսակից տարբեր թերթերի վրա, ապա ամեն ինչ շատ ավելի տխուր կլինի: Այս պահին Excel-ի զինանոցում նման տվյալների տարանջատման քաղաքակիրթ ներկառուցված գործիքներ չկան, ցավոք: Այսպիսով, դուք պետք է օգտագործեք մակրո Visual Basic-ում, կամ օգտագործեք մակրո ձայնագրիչ + Power Query համադրությունը մի փոքր «ֆայլի ճշգրտմամբ»:

Եկեք ավելի սերտ նայենք, թե ինչպես դա կարող է իրականացվել:

Խնդրի ձևակերպում

Մենք որպես նախնական տվյալ ունենք վաճառքի համար նախատեսված 5000-ից ավելի տողերի նման աղյուսակ.

Առաջադրանք. այս աղյուսակի տվյալները բաժանել ըստ քաղաքների այս գրքի առանձին թերթիկների վրա: Նրանք. Ելքի ժամանակ դուք պետք է յուրաքանչյուր թերթիկի վրա ստանաք միայն այն տողերը աղյուսակից, որտեղ վաճառքը եղել է համապատասխան քաղաքում.

Պատրաստել

Որպեսզի մակրո կոդը չբարդացնենք և հնարավորինս հեշտ ընկալվի, կատարենք մի քանի նախապատրաստական ​​քայլ։

Նախ, ստեղծել առանձին որոնման աղյուսակ, որտեղ մեկ սյունակում կցուցադրվեն բոլոր քաղաքները, որոնց համար ցանկանում եք ստեղծել առանձին թերթեր: Իհարկե, այս գրացուցակը կարող է պարունակել ոչ բոլոր քաղաքները, որոնք առկա են սկզբնաղբյուրի տվյալների մեջ, այլ միայն այն քաղաքները, որոնց համար մեզ անհրաժեշտ են հաշվետվություններ: Նման աղյուսակ ստեղծելու ամենահեշտ ձևը հրամանի օգտագործումն է Տվյալներ – Հեռացրեք կրկնօրինակները (Տվյալներ — Հեռացնել կրկնօրինակները) սյունակի պատճենի համար Քաղաք կամ գործառույթ ՅՈՒՆԻԿ (ԵՔՆԱԿԱՆ) – եթե ունեք Excel 365-ի վերջին տարբերակը:

Քանի որ Excel-ում նոր թերթերը լռելյայնորեն ստեղծվում են ընթացիկ (նախորդից) առաջ (նախորդից) առաջ, իմաստ ունի նաև այս գրացուցակի քաղաքները դասավորել նվազման կարգով (Z-ից A), այնուհետև ստեղծելուց հետո՝ քաղաքը: թերթերը կդասավորվեն այբբենական կարգով:

Երկրորդ, пփոխարկել երկու աղյուսակները դինամիկ («խելացի»)՝ նրանց հետ աշխատելն ավելի հեշտ դարձնելու համար: Մենք օգտագործում ենք հրամանը Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ) կամ ստեղնաշարի դյուրանցում Ctrl+T. Հայտնվող ներդիրում շինարար (Դիզայն) եկեք նրանց կանչենք tablProdaji и TableCityհամապատասխանաբար.

Մեթոդ 1. Մակրո՝ թերթերով բաժանելու համար

Ընդլայնված ներդիրում երեվակիչ (մշակող) սեղմեք կոճակի վրա Visual Basic կամ օգտագործեք ստեղնաշարի դյուրանցում ալտ+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 Հաջորդ բջիջի Worksheets(«Данные»).ShowAllData End Sub	  

Այստեղ մի օղակով Յուրաքանչյուրի համար… Հաջորդը իրականացրեց անցումը գրացուցակի բջիջների միջով TableCity, որտեղ յուրաքանչյուր քաղաքի համար այն զտված է (մեթոդ Ավտոֆիլտր) սկզբնական վաճառքի աղյուսակում և այնուհետև պատճենելով արդյունքները նորաստեղծ թերթում: Ճանապարհին ստեղծված թերթիկը վերանվանվում է քաղաքի նույն անունով և դրա վրա միացվում է գեղեցկության համար սյուների լայնությունը ավտոմատ կերպով հարմարեցնելը։

Դուք կարող եք գործարկել ստեղծված մակրո Excel-ում ներդիրում երեվակիչ կոճակ Մակրո (Մշակող — մակրո) կամ ստեղնաշարի դյուրանցում ալտ+F8.

Մեթոդ 2. Ստեղծեք մի քանի հարցումներ Power Query-ում

Նախորդ մեթոդը, չնայած իր ամբողջ կոմպակտությանը և պարզությանը, ունի էական թերություն՝ մակրոյի կողմից ստեղծված թերթիկները չեն թարմացվում, երբ փոփոխություններ են կատարվում սկզբնական վաճառքի աղյուսակում: Եթե ​​արագ թարմացումն անհրաժեշտ է, ապա դուք ստիպված կլինեք օգտագործել VBA + Power Query փաթեթը, ավելի ճիշտ, ստեղծել մակրո օգտագործելով ոչ միայն ստատիկ տվյալներով թերթեր, այլև թարմացված Power Query հարցումներ:

Մակրոն այս դեպքում մասամբ նման է նախորդին (այն ունի նաև ցիկլ Յուրաքանչյուրի համար… Հաջորդը գրացուցակի քաղաքների վրա կրկնելու համար), բայց օղակի ներսում այլևս չի լինի զտում և պատճենում, այլ Power Query հարցումի ստեղծում և դրա արդյունքները նոր թերթիկում վերբեռնելու համար.

Sub Splitter2() Range-ի յուրաքանչյուր բջիջի համար ("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Փոխված տեսակը"" = Table.TransformColumnTypes(Աղբյուր , {{""Կատեգորիա"", տպեք տեքստ}, {""Անուն"", տպեք տեքստ}, {""Քաղաք"", տպեք տեքստ}, {""Մենեջեր"", տպեք տեքստ}, {""Գործարք" date "", type datetime}, {""Cost"", type number}})," & 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 = xlInsertwordSelevesells. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Թարմացնել BackgroundQuery:=False End With ActiveSheet.Name = բջիջը:  

Գործարկումից հետո մենք կտեսնենք նույն թերթիկները ըստ քաղաքների, բայց արդեն ստեղծված Power Query հարցումները կձևավորեն դրանք.

Աղբյուրի տվյալների ցանկացած փոփոխության դեպքում բավական կլինի թարմացնել համապատասխան աղյուսակը մկնիկի աջ կոճակով՝ հրամանով. Թարմացնել և պահպանել (Թարմացնել) կամ կոճակի միջոցով զանգվածաբար թարմացրեք բոլոր քաղաքները Թարմացնել բոլորը ականջակալ Ամսաթիվ (Տվյալներ — Թարմացնել բոլորը).

• Ինչ են մակրոները, ինչպես ստեղծել և օգտագործել դրանք
• Աշխատանքային գրքույկի թերթերի պահպանում որպես առանձին ֆայլեր
• Գրքի բոլոր թերթերից տվյալների հավաքում մեկ աղյուսակի մեջ