Mündəricat
Microsoft Excel-də bir neçə cədvəldən (müxtəlif vərəqlərdən və ya müxtəlif fayllardan) məlumat toplamaq üçün bir çox alət var: birbaşa bağlantılar, funksiya DOLAYI (Birbaşa), Power Query və Power Pivot əlavələri və s. Barrikadanın bu tərəfindən hər şey yaxşı görünür.
Ancaq tərs bir problemlə qarşılaşsanız - məlumatları bir cədvəldən müxtəlif vərəqlərə yaymaq - onda hər şey daha kədərli olacaq. Hal-hazırda, təəssüf ki, Excel arsenalında bu cür məlumatların ayrılması üçün sivil daxili alətlər yoxdur. Beləliklə, siz Visual Basic-də makrodan istifadə etməli və ya sonra bir az "fayl zərifliyi" ilə makro yazıcı + Power Query birləşməsindən istifadə etməli olacaqsınız.
Bunun necə həyata keçirilə biləcəyinə daha yaxından nəzər salaq.
Problemin formalaşdırılması
İlkin məlumat olaraq satış üçün 5000-dən çox cərgə ölçüsü olan belə bir cədvəlimiz var:
Tapşırıq: bu cədvəldəki məlumatları bu kitabın ayrı-ayrı vərəqlərində şəhərlər üzrə yaymaq. Bunlar. çıxışda, hər vərəqdə yalnız müvafiq şəhərdə satışın olduğu cədvəldən həmin sətirləri əldə etməlisiniz:
Hazırlamaq
Makro kodu çətinləşdirməmək və onu mümkün qədər asan başa düşmək üçün bir neçə hazırlıq addımını yerinə yetirək.
Birincisi, ayrıca axtarış cədvəli yaradın, burada bir sütun ayrıca vərəqlər yaratmaq istədiyiniz bütün şəhərləri sadalayacaq. Əlbəttə ki, bu kataloq mənbə məlumatlarında olan bütün şəhərləri deyil, yalnız hesabata ehtiyacımız olan şəhərləri ehtiva edə bilər. Belə bir cədvəl yaratmağın ən asan yolu əmrdən istifadə etməkdir Məlumat - Dublikatları silin (Məlumatlar - dublikatları silin) sütun surəti üçün şəhər və ya funksiya UNİK (UNİKAL) – Excel 365-in ən son versiyası varsa.
Excel-də yeni vərəqlər defolt olaraq cari (əvvəlki) cədvəldən əvvəl (solunda) yaradıldığından, bu kataloqdakı şəhərləri azalan ardıcıllıqla (Z-dən A-ya) çeşidləmək də məntiqlidir – sonra yaradıldıqdan sonra şəhər vərəqlər əlifba sırası ilə düzülür.
İkincisi, пhər iki cədvəli dinamikə çevirin onlarla işləməyi asanlaşdırmaq üçün (“ağıllı”). əmrindən istifadə edirik Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format) və ya klaviatura qısa yolu Ctrl+T. Görünən tabda konstruktor (Dizayn) onları çağıraq tablProdaji и TableCitymüvafiq olaraq:
Metod 1. Vərəqlərə bölmək üçün makro
Ətraflı sekmesinde geliştirici (İnkişaf etdirici) düyməsini sıxın Visual Basic və ya klaviatura qısa yolundan istifadə edin Dayandırmaq+F11. Açılan makro redaktoru pəncərəsində menyu vasitəsilə yeni boş modul daxil edin Daxil et - Modul və aşağıdakı kodu oraya kopyalayın:
Sub Splitter() Aralıqdakı Hər Bir Xanaya("таблГорода") Range("таблПродажи").AutoFilter Sahəsi:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = xana.Value ActiveSheet.UsedRange.Columns.AutoFit Next xana İş vərəqləri("Данные").ShowAllData End Sub Burada bir döngə ilə Hər biri üçün... Sonrakı kataloqun xanalarından keçidi həyata keçirdi TableCity, burada hər bir şəhər üçün süzülür (metod Avtomatik Filtr) orijinal satış cədvəlində və sonra nəticələri yeni yaradılmış vərəqə kopyalayın. Yol boyu yaradılan vərəq şəhərin eyni adı ilə dəyişdirilir və gözəllik üçün sütunların eninin avtomatik uyğunlaşdırılması işə salınır.
Yaradılmış makronu Excel-də tabda işlədə bilərsiniz geliştirici düyməsini Makro (Tərtibatçı — Makroslar) və ya klaviatura qısa yolu Dayandırmaq+F8.
Metod 2. Power Query-də çoxsaylı sorğular yaradın
Əvvəlki metod, bütün kompaktlığı və sadəliyi üçün əhəmiyyətli bir çatışmazlığa malikdir - orijinal satış cədvəlində dəyişikliklər edildikdə makro tərəfindən yaradılmış vərəqlər yenilənmir. Tez yeniləmə lazımdırsa, onda siz VBA + Power Query paketindən istifadə etməli olacaqsınız, daha doğrusu, makrodan istifadə edərək təkcə statik məlumatlı vərəqlər deyil, yenilənmiş Power Query sorğuları yaratmalısınız.
Bu vəziyyətdə makro qismən əvvəlkinə bənzəyir (onun da dövrü var Hər biri üçün... Sonrakı kataloqdakı şəhərlər üzərində təkrarlamaq üçün), lakin dövrə daxilində artıq filtrləmə və surət çıxarmaq olmayacaq, lakin Power Query sorğusu yaratmaq və onun nəticələrini yeni vərəqə yükləmək olacaq:
Sub Splitter2() Aralıqdakı Hər Bir Xanaya("Şəhər cədvəli") ActiveWorkbook.Queries.Ad Add:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Ad=""TableSales""]}[Məzmun]," & Chr(13) & "" & Chr(10) & " #""Dəyişdirilmiş Növ"" = Cədvəl.TransformColumnTypes(Mənbə) , {{""Kateqoriya"", mətni yazın}, {""Ad"", mətni yazın}, {""Şəhər"", mətni yazın}, {""Menecer"", mətni yazın}, {""Sövdələşmə" tarix "", datetime yazın}, {""Xərc"", nömrə yazın}})," & Chr(13) & "" & Chr(10) & " #""Filtr tətbiq olunan sıralar"" = Cədvəl.Se " & _ "lectRows(#""Dəyişdirilmiş növ"", hər biri ([Şəhər] = """ & xana.Dəyər & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Filtr tətbiq olunan sıralar""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provayder =Microsoft.Mashup.OleDb.1;Məlumat Mənbəsi=$İş dəftəri$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Massiv("SEÇ *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells.SaveCellsword SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name = cell.End Altue Başladıqdan sonra biz şəhərlər üzrə eyni vərəqləri görəcəyik, lakin artıq yaradılmış Power Query sorğuları onları formalaşdıracaq:
Mənbə məlumatlarında hər hansı bir dəyişiklik olduqda, müvafiq cədvəli sağ siçan düyməsini - əmrlə yeniləmək kifayətdir Yeniləyin və Saxlayın (Təzələmək) və ya düyməni istifadə edərək bütün şəhərləri bir anda yeniləyin Hamısını yeniləyin nişanı Tarix (Məlumat - Hamısını Yeniləyin).
- Makrolar nədir, onları necə yaratmaq və istifadə etmək
- İş kitabı vərəqlərinin ayrıca fayllar kimi saxlanması
- Kitabın bütün vərəqlərindən məlumatların bir cədvəldə toplanması