змест
Microsoft Excel мае мноства інструментаў для збору дадзеных з некалькіх табліц (з розных лістоў або з розных файлаў): прамыя спасылкі, функцыя ускосна (УСКАСНЫ), надбудовы Power Query і Power Pivot і г. д. З гэтага боку барыкады ўсё выглядае добра.
Але калі вы сутыкнецеся з адваротнай задачай - разнесці дадзеныя з адной табліцы па розных лістах - то ўсё будзе нашмат сумней. На дадзены момант цывілізаваных убудаваных сродкаў для такога падзелу дадзеных у арсенале Excel, на жаль, няма. Такім чынам, вам давядзецца выкарыстоўваць макрас у Visual Basic або выкарыстоўваць камбінацыю запісу макрасаў + Power Query з невялікай «дапрацоўкай файла».
Давайце больш падрабязна разгледзім, як гэта можна рэалізаваць.
Пастаноўка задачы
У якасці зыходных дадзеных у нас ёсць такая табліца памерам больш за 5000 радкоў для продажаў:
Заданне: размеркаваць даныя гэтай табліцы па гарадах на асобных лістах дадзенай кнігі. Тыя. на выхадзе трэба атрымаць на кожным аркушы толькі тыя радкі з табліцы, дзе была распродаж у адпаведным горадзе:
Рыхтаваць
Каб не ўскладняць код макраса і зрабіць яго максімальна простым для разумення, выканаем пару падрыхтоўчых крокаў.
Па-першае, стварыць асобную табліцу пошуку, дзе ў адным слупку будуць пералічаныя ўсе гарады, для якіх вы хочаце стварыць асобныя аркушы. Вядома, гэты каталог можа ўтрымліваць не ўсе гарады, прысутныя ў зыходных дадзеных, а толькі тыя, па якіх нам патрэбныя справаздачы. Прасцей за ўсё стварыць такую табліцу з дапамогай каманды Дадзеныя - выдаленне дублікатаў (Дадзеныя — Выдаліць дублікаты) для копіі слупка Горад або функцыі УНІК (УНІКАЛЬНЫ) – калі ў вас апошняя версія Excel 365.
Паколькі новыя аркушы ў Excel ствараюцца па змаўчанні раней (злева ад) бягучага (папярэдняга), таксама мае сэнс сартаваць гарады ў гэтым каталогу па змяншэнні (ад Я да А) – тады пасля стварэння горад аркушы будуць размешчаны ў алфавітным парадку.
Па-другое, ппераўтварыць абедзве табліцы ў дынамічныя («разумныя»), каб з імі было лягчэй працаваць. Выкарыстоўваем каманду Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу) або спалучэнне клавіш Ctrl+T. На ўкладцы, якая з'явіцца Канструктар (Дызайн) давайце іх назавем tablProdaji и TableCity, адпаведна:
Спосаб 1. Макрас для падзелу па лістах
На ўкладцы Дадаткова распрацоўшчык (Распрацоўшчык) націсніце на кнопку Візуальны Бейсик або скарыстайцеся спалучэннем клавіш Alt+F11. У якое адкрылася акне рэдактара макрасаў устаўце праз меню новы пусты модуль Устаўка – модуль і скапіруйце туды наступны код:
Sub Splitter() Для кожнай ячэйкі ў Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Тут з пятлёй Для кожнага ... Далей рэалізаваны праход па ячэйках каталога TableCity, дзе для кожнага горада ён фільтруецца (метад Аўтафільтр) у зыходнай табліцы продажаў, а затым скапіяваць вынікі ў толькі што створаны ліст. Па шляху створаны ліст перайменоўваецца ў аднайменны горад і на ім для прыгажосці ўключаецца аўтападбор шырыні слупкоў.
Вы можаце запусціць створаны макрас ў Excel на ўкладцы распрацоўшчык кнопка Макрас (Распрацоўшчык — Макрасы) або спалучэнне клавіш 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) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Зменены тып"" = Table.TransformColumnTypes(Source , {{""Катэгорыя"", увядзіце тэкст}, {""Назва"", увядзіце тэкст}, {""Горад"", увядзіце тэкст}, {""Менеджэр"", увядзіце тэкст}, {""Здзелка" дата "", увядзіце datetime}, {""Кошт"", увядзіце нумар}})," & Chr(13) & "" & Chr(10) & " #""Радкі з ужытым фільтрам"" = Table.Se " & _ "lectRows(#""Зменены тып"", кожны ([Горад] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "у " & 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 = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub Пасля яго запуску мы ўбачым тыя ж лісты па гарадах, але фармаваць іх будуць ужо створаныя запыты Power Query:
Пры любых зменах у зыходных дадзеных дастаткова абнавіць адпаведную табліцу правай кнопкай мышы – каманда Абнавіць і захаваць (Абнавіць) або масава абнавіць усе гарады з дапамогай кнопкі абнавіць усе таб Дата (Дадзеныя — Абнавіць усе).
- Што такое макрасы, як іх ствараць і выкарыстоўваць
- Захаванне лістоў працоўнай кніжкі ў асобныя файлы
- Збор дадзеных з усіх аркушаў кнігі ў адну табліцу