мазмуну
Microsoft Excel бир нече таблицалардан (ар кандай барактардан же ар кандай файлдардан) маалыматтарды чогултуу үчүн көптөгөн куралдарга ээ: түз шилтемелер, функция КЫЙЫР (ТУЗДУК), Power Query жана Power Pivot кошумчалары ж.б. Баррикаданын бул жагынан баары жакшы көрүнөт.
Бирок, эгерде сиз тескери маселеге туш болсоңуз - маалыматтарды бир таблицадан ар кандай баракчаларга жайылтуу - анда баары бир топ кайгылуу болот. Азыркы учурда, тилекке каршы, Excel арсеналында мындай маалыматтарды бөлүү үчүн цивилизациялуу орнотулган куралдар жок. Ошентип, сиз Visual Basic'те макросту колдонушуңуз керек, же макро жазгыч + Power Query айкалышын бир аз "файлды тактоо" менен колдонушуңуз керек болот.
Келгиле, муну кантип ишке ашырууга болорун кененирээк карап көрөлү.
Проблеманы түзүү
Бизде баштапкы маалыматтар катары сатуу үчүн 5000 саптан ашкан өлчөмү бар таблица бар:
Милдет: бул таблицадагы маалыматтарды шаарлар боюнча бул китептин өзүнчө барактарында бөлүштүрүү. Ошол. чыгарууда, ар бир баракта сатуу тиешелүү шаарда болгон таблицадагы саптарды гана алышыңыз керек:
даярдоо
Макрокодду татаалдаштырбоо жана аны мүмкүн болушунча оңой түшүнүү үчүн, бир нече даярдоо кадамдарын жасайлы.
Биринчиден, өзүнчө издөө таблицасын түзүү, мында бир тилке сиз өзүнчө барактарды түзгүңүз келген бардык шаарларды тизмелейт. Албетте, бул каталогдо булак маалыматтарында болгон бардык шаарлар камтылбашы мүмкүн, бирок бизге отчет керек болгон шаарлар гана болушу мүмкүн. Мындай таблицаны түзүүнүн эң оңой жолу – бул команданы колдонуу Маалыматтар - Дубликаттарды алып салуу (Маалымат - кайталанмаларды алып салуу) тилке көчүрмөсү үчүн сезимдер же функция UNIQUE (Уникалдуу) – эгер сизде Excel 365тин эң акыркы версиясы болсо.
Excelдеги жаңы барактар демейки боюнча учурдагы (мурунку) барактан мурун (сол жагында) түзүлгөндүктөн, бул каталогдогу шаарларды кемүү тартибинде (Zден Ага чейин) иреттөө мааниси бар - андан кийин түзүлгөндөн кийин, шаар баракчалар алфавиттик тартипте жайгаштырылат.
Экинчиден пэки таблицаны динамикага айландырыңыз («акылдуу») алар менен иштөөнү жеңилдетүү үчүн. Биз буйрукту колдонобуз Башкы бет – Таблица катары форматтоо (Башкы бет — Таблица катары формат) же баскычтоптун жарлыгы Ctrl+T. Пайда болгон өтмөктө Конструктор (Дизайн) аларды чакыралы tablProdaji и TableCityтиешелүүлүгүнө жараша:
Метод 1. Барактар боюнча бөлүү үчүн макро
Advanced өтмөгүндө иштеп (Иштеп чыгуучу) баскычын чыкылдатыңыз Visual Basic же баскычтопту колдонуңуз Alt+F11. Ачылган макро редактор терезесинде меню аркылуу жаңы бош модулду киргизиңиз Кыстаруу – модуль жана ал жерде төмөнкү кодду көчүрүү:
Sub Splitter() Ар бир уяча үчүн диапазон("таблГорода") Диапазон("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Кийинки уячанын Worksheets("Данные").ShowAllData End Sub Sub Бул жерде цикл менен Ар бири үчүн… Кийинки каталогдун клеткалары аркылуу өтүүнү ишке ашырган TableCity, кайда ар бир шаар үчүн чыпкаланат (ыкма Автофильтр) баштапкы сатуу таблицасында жана андан кийин натыйжаларды жаңы түзүлгөн баракка көчүрүү. Жолдо, түзүлгөн барак шаардын аталышы менен аталып, сулуулук үчүн тилкелердин туурасын автоматтык түрдө орнотуу күйгүзүлөт.
Түзүлгөн макросту Excelде өтмөктө иштете аласыз иштеп баскычы Macros (Иштеп чыгуучу — Макрос) же баскычтоптун жарлыгы Alt+F8.
Метод 2. Power Queryде бир нече суроо түзүңүз
Мурунку ыкма, анын бардык компакттуулугу жана жөнөкөйлүгү үчүн, олуттуу кемчилиги бар - макро менен түзүлгөн баракчалар баштапкы сатуу таблицасына өзгөртүүлөр киргизилгенде жаңыланбайт. Эгер тез арада жаңыртуу керек болсо, анда VBA + Power Query пакетин колдонушуңуз керек болот, тагыраак айтканда, макросту колдонуу менен статикалык маалыматтары бар барактарды гана эмес, жаңыланган Power Query сурамдарын түзүшүңүз керек болот.
Бул учурда макрос мурункуга жарым-жартылай окшош (анын цикли да бар Ар бири үчүн… Кийинки каталогдогу шаарларды кайталоо үчүн), бирок циклдин ичинде мындан ары чыпкалоо жана көчүрүү болбойт, бирок Power Query сурамын түзүү жана анын натыйжаларын жаңы баракка жүктөө:
Sub Splitter2() Ар бир уяча үчүн Диапазон("Шаар таблицасы") ActiveWorkbook.Queries.Add Name:=cell.Value, Формула:= _ "келсин" & Chr(13) & "" & Chr(10) & " Булак = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Өзгөрүлгөн түрү"" = Table.TransformColumnTypes(Булак) , {{""Категория"", текстти териңиз}, {""Аты-жөнү"", текстти териңиз}, {""Шаар"", текстти териңиз}, {""Менеджер"", текстти териңиз}, {""Келишим" дата "", datetime териңиз}, {""Кост"", номерди териңиз}})," & Chr(13) & "" & Chr(10) & " #""Чыпка колдонулган саптар"" = Таблица.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;Data Source=$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 = Чын .RefreshStyle = xlInsertDeleteCells.alse. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name = cell.Value Аны ишке киргизгенден кийин, биз шаар боюнча бирдей барактарды көрөбүз, бирок буга чейин түзүлгөн Power Query сурамдары аларды түзөт:
Булак маалыматтарындагы кандайдыр бир өзгөрүүлөр менен, чычкандын оң баскычы - буйрук менен тиешелүү таблицаны жаңыртуу жетиштүү болот Жаңыртуу & Сактоо (Жаңылоо) же баскычын колдонуп, бир эле учурда бардык шаарларды жапырт жаңыртыңыз өзгөртүү Бардык табулатура маалыматтар (Маалымат — баарын жаңылоо).
- Макрос деген эмне, аларды кантип түзүү жана колдонуу керек
- Жумушчу китептин барактары өзүнчө файлдар катары сакталууда
- Китептин бардык барактарынан маалыматтарды бир таблицага чогултуу