Мазмұны
Microsoft Excel бағдарламасында бірнеше кестелерден (әртүрлі парақтардан немесе әртүрлі файлдардан) деректерді жинауға арналған көптеген құралдар бар: тікелей сілтемелер, функция ТӘУЕЛСІЗДІК (ТІКЕСІЗ), Power Query және Power Pivot қондырмалары, т.б. Баррикаданың осы жағынан бәрі жақсы көрінеді.
Бірақ егер сіз кері мәселеге тап болсаңыз - деректерді бір кестеден әртүрлі парақтарға тарату - онда бәрі әлдеқайда қайғылы болады. Өкінішке орай, қазіргі уақытта Excel арсеналында деректерді мұндай бөлуге арналған өркениетті кіріктірілген құралдар жоқ. Сондықтан Visual Basic бағдарламасында макросты пайдалану керек болады немесе кейін сәл «файлды нақтылау» арқылы макро рекордері + Power Query тіркесімін пайдалану керек.
Мұны қалай жүзеге асыруға болатынын егжей-тегжейлі қарастырайық.
Мәселені тұжырымдау
Бізде бастапқы деректер ретінде сатуға арналған көлемі 5000 жолдан асатын кесте бар:
Тапсырма: осы кестедегі деректерді осы кітаптың жеке парақтарында қалалар бойынша тарату. Анау. шығу кезінде сіз әр парақта тек тиісті қалада сатылым болған кестедегі жолдарды алуыңыз керек:
Дайындық
Макрокодты қиындатпау және оны мүмкіндігінше түсінуді жеңілдету үшін бірнеше дайындық қадамдарын орындайық.
Біріншіден, бөлек іздеу кестесін жасаңыз, мұнда бір баған бөлек парақтар жасағыңыз келетін барлық қалаларды тізімдейді. Әрине, бұл каталог бастапқы деректерде бар барлық қалаларды емес, тек есептерді қажет ететін қалаларды қамтуы мүмкін. Мұндай кестені құрудың ең оңай жолы - команданы пайдалану Деректер – Көшірмелерді жою (Деректер — көшірмелерді жою) баған көшірмесі үшін сезім немесе функция UNIK (ЕРЕКШЕ) – Excel 365 бағдарламасының соңғы нұсқасы болса.
Excel бағдарламасындағы жаңа парақтар әдепкі бойынша ағымдағы (алдыңғы) парақтың алдында (сол жағында) жасалғандықтан, осы каталогтағы қалаларды кему ретімен (Z-ден A-ға дейін) сұрыптау мағынасы бар, содан кейін жасалғаннан кейін қала парақтар алфавит бойынша орналасады.
Екіншіден пекі кестені динамикалық түрлендіру («ақылды») олармен жұмыс істеуді жеңілдету үшін. Біз пәрменді қолданамыз Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу) немесе пернелер тіркесімі Ctrl+T. Пайда болған қойындыда Конструктор (Дизайн) оларды шақырайық tablProdaji и TableCityсәйкесінше:
1-әдіс. Парақтар бойынша бөлуге арналған макрос
Қосымша қойындысында әзірлеуші (Әзірлеуші) түймешігін басыңыз 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 Келесі ұяшық Жұмыс парақтары("Данные").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, формула:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Атауы=""TableSales""]}[Мазмұн]," & Chr(13) & "" & Chr(10) & " #""Өзгертілген түрі"" = Table.TransformColumnTypes(Source) , {{""Санат"", мәтінді теріңіз}, {""Аты"", мәтінді теріңіз}, {""Қала"", мәтінді теріңіз}, {""Менеджер"", мәтінді теріңіз}, {""Мәміле" күн "", datetime деп теріңіз}, {""Шығын"", нөмірін теріңіз}})," & Chr(13) & "" & Chr(10) & " #""Сүзгі қолданылған жолдар"" = Кесте.Se " & _ "lectRows(#""Өзгертілген түрі"", әрқайсысы ([Қала] = """ және ұяшық. Мән & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Сүзгі қолданылған жолдар""" ActiveWorkbook.Worksheets.Add ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Провайдер =Microsoft.Mashup.OleDb.1;Дерек көзі=$Жұмыс кітабы$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Массив("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells.alse. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name = cell.End Subalue Оны іске қосқаннан кейін біз қала бойынша бірдей парақтарды көреміз, бірақ бұрыннан жасалған Power Query сұраулары оларды қалыптастырады:
Бастапқы деректердегі кез келген өзгерістер кезінде тінтуірдің оң жақ түймешігі - пәрмен арқылы сәйкес кестені жаңарту жеткілікті болады Жаңарту және сақтау (Жаңарту) немесе түймені пайдаланып барлық қалаларды бірден жаңартыңыз Барлығын жаңарту қойындысы мәліметтер (Деректер — Барлығын жаңарту).
- Макростар дегеніміз не, оларды құру және пайдалану жолы
- Жұмыс кітабының парақтарын бөлек файлдар ретінде сақтау
- Кітаптың барлық парақтарынан мәліметтерді бір кестеге жинау