İçerik
Microsoft Excel, birkaç tablodan (farklı sayfalardan veya farklı dosyalardan) veri toplamak için birçok araca sahiptir: doğrudan bağlantılar, işlev DOLAYLI (DOLAYLI), Power Query ve Power Pivot eklentileri vb. Barikatın bu tarafından bakıldığında her şey yolunda görünüyor.
Ancak ters bir problemle karşılaşırsanız – verileri bir tablodan farklı sayfalara yaymak – o zaman her şey çok daha üzücü olacaktır. Şu anda, ne yazık ki, Excel'in cephaneliğinde bu tür veri ayrımı için medeni yerleşik araçlar yoktur. Bu yüzden Visual Basic'te bir makro kullanmanız veya sonrasında küçük bir "dosya iyileştirme" ile makro kaydedici + Power Query kombinasyonunu kullanmanız gerekecektir.
Bunun nasıl uygulanabileceğine daha yakından bakalım.
Sorunun formülasyonu
İlk veri olarak, satışlar için 5000 satırdan daha büyük bir tabloya sahibiz:
Görev: Bu tablodaki verileri şehirlere göre bu kitabın ayrı sayfalarına dağıtmak. Şunlar. çıktıda, her sayfada yalnızca satışın ilgili şehirde olduğu tablodaki satırları almanız gerekir:
Hazırlamak
Makro kodunu karmaşıklaştırmamak ve mümkün olduğunca kolay anlaşılır hale getirmek için birkaç hazırlık adımı gerçekleştirelim.
İlk olarak, ayrı bir arama tablosu oluşturun, burada tek bir sütun, ayrı sayfalar oluşturmak istediğiniz tüm şehirleri listeleyecektir. Tabii ki, bu dizin kaynak verilerde bulunan tüm şehirleri değil, yalnızca rapora ihtiyaç duyduğumuz şehirleri içerebilir. Böyle bir tablo oluşturmanın en kolay yolu şu komutu kullanmaktır: Veri – Yinelenenleri Kaldır (Veri — Yinelenenleri kaldırın) sütun kopyası için Şehir veya işlev UNIK (BENZERSİZ) – Excel 365'in en son sürümüne sahipseniz.
Excel'deki yeni sayfalar varsayılan olarak mevcut (önceki) olandan önce (solda) oluşturulduğundan, bu dizindeki şehirleri azalan düzende (Z'den A'ya) sıralamak da mantıklıdır - ardından oluşturulduktan sonra şehir sayfalar alfabetik olarak düzenlenecektir.
İkinci olarak, пher iki tabloyu da dinamik hale getir (“akıllı”) onlarla çalışmayı kolaylaştırmak için. komutunu kullanıyoruz. Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir) veya klavye kısayolu Ctrl+T. Görünen sekmede Inşaatçı (Tasarım) onları arayalım tablProdaji и TabloŞehir, sırasıyla:
Yöntem 1. Yapraklara göre bölme makrosu
Gelişmiş sekmesinde geliştirici (Geliştirici) düğmeye tıklayın Visual Basic veya klavye kısayolunu kullanın Ara Toplam+F11. Açılan makro düzenleyici penceresinde menüden yeni bir boş modül ekleyin Ekle – Modül ve aşağıdaki kodu oraya kopyalayın:
Sub Splitter() Aralıktaki Her hücre için("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy. Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Sonraki hücre Çalışma Sayfaları("Данные").ShowAllData End Sub Burada bir döngü ile Her biri için… Sonraki dizinin hücrelerinden geçişi uyguladı TabloŞehir, her şehir için filtrelendiği yer (yöntem Otomatik filtre) orijinal satış tablosunda ve ardından sonuçları yeni oluşturulan sayfaya kopyalama. Yol boyunca, oluşturulan sayfa şehrin aynı adıyla yeniden adlandırılır ve üzerinde güzellik için sütunların genişliğini otomatik olarak sığdırır.
Oluşturulan makroyu Excel'de sekmesinden çalıştırabilirsiniz. geliştirici düğmesine tıklayın Makrolar (Geliştirici — Makrolar) veya klavye kısayolu Ara Toplam+F8.
Yöntem 2. Power Query'de birden çok sorgu oluşturun
Tüm kompaktlığı ve basitliği için önceki yöntemin önemli bir dezavantajı vardır - makro tarafından oluşturulan sayfalar, orijinal satış tablosunda değişiklik yapıldığında güncellenmez. Anında güncelleme gerekliyse, VBA + Power Query paketini kullanmanız veya bunun yerine yalnızca statik veriler içeren sayfalar değil, güncellenmiş Power Query sorguları kullanarak bir makro oluşturmanız gerekir.
Bu durumda makro kısmen öncekine benzer (ayrıca bir döngüye sahiptir). Her biri için… Sonraki dizindeki şehirleri yinelemek için), ancak döngü içinde artık filtreleme ve kopyalama olmayacak, ancak bir Power Query sorgusu oluşturup sonuçlarını yeni bir sayfaya yükleyecek:
Sub Splitter2() Aralıktaki Her Hücre İçin("Şehir tablosu") ActiveWorkbook.Queries.Add Ad:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[İçerik]," & Chr(13) & "" & Chr(10) & " #""Değişen Tür""" = Table.TransformColumnTypes(Source , {{""Kategori"", metin yazın}, {""Ad"", metin yazın}, {""Şehir"", metin yazın}, {""Yönetici"", metin yazın}, {""Anlaşma date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Filtre uygulanmış satırlar"" = Table.Se " & _ "lectRows(#""Tür değişti"", her biri ([Şehir] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Filtre uygulanmış satırlar""" ActiveWorkbook.Worksheets.Add with ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Sağlayıcı =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 = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=ActiveSheet ile Yanlış Son.Name = cell.Value Sonraki hücre End Sub Lansmanından sonra, aynı sayfaları şehre göre göreceğiz, ancak önceden oluşturulmuş Power Query sorguları bunları oluşturacak:
Kaynak verilerde herhangi bir değişiklik olduğunda, ilgili tablonun sağ fare tuşu ile güncellenmesi yeterli olacaktır - komut Güncelle ve Kaydet (Yenile) veya butonu kullanarak tüm şehirleri toplu olarak güncelleyin Güncelleme Tüm çıkıntı Veri (Veri — Tümünü Yenile).
- Makrolar nedir, nasıl oluşturulur ve kullanılır
- Çalışma kitabı sayfalarını ayrı dosyalar olarak kaydetme
- Kitabın tüm sayfalarından verileri tek bir tabloda toplama