Jadvalni varaqlarga bo'lish

Microsoft Excel-da bir nechta jadvallardan (turli varaqlardan yoki turli fayllardan) ma'lumotlarni yig'ish uchun ko'plab vositalar mavjud: to'g'ridan-to'g'ri havolalar, funksiya BILVOSIT (BOSHQA), Power Query va Power Pivot qo'shimchalari va boshqalar. Barrikadaning bu tomonida hamma narsa yaxshi ko'rinadi.

Ammo agar siz teskari muammoga duch kelsangiz - ma'lumotlarni bitta jadvaldan turli varaqlarga tarqatish - keyin hamma narsa juda achinarli bo'ladi. Afsuski, hozirda Excel arsenalida bunday ma'lumotlarni ajratish uchun tsivilizatsiyalashgan o'rnatilgan vositalar mavjud emas. Shunday qilib, siz Visual Basic-da makrosdan foydalanishingiz yoki so'l yozuvchisi + Power Query kombinatsiyasidan keyin biroz "faylni tozalash" bilan foydalanishingiz kerak bo'ladi.

Keling, buni qanday amalga oshirish mumkinligini batafsil ko'rib chiqaylik.

Muammoni shakllantirish

Bizda dastlabki ma'lumotlar sifatida savdo uchun 5000 dan ortiq satrli jadval mavjud:

Vazifa: ushbu jadvaldagi ma'lumotlarni ushbu kitobning alohida varaqlarida shaharlar bo'yicha taqsimlash. Bular. Chiqishda siz har bir varaqda faqat tegishli shaharda savdo bo'lgan jadvaldagi qatorlarni olishingiz kerak:

Tayyor bo'ling

Ibratli kodni murakkablashtirmaslik va uni iloji boricha tushunishni osonlashtirish uchun keling, bir nechta tayyorgarlik bosqichlarini bajaramiz.

Birinchidan, alohida qidirish jadvalini yarating, bu erda bitta ustunda siz alohida varaqlar yaratmoqchi bo'lgan barcha shaharlar ro'yxati bo'ladi. Albatta, ushbu katalog manba ma'lumotlarida mavjud bo'lgan barcha shaharlarni o'z ichiga olmaydi, lekin faqat bizga hisobot kerak bo'lgan shaharlarni o'z ichiga olishi mumkin. Bunday jadvalni yaratishning eng oson yo'li buyruqni ishlatishdir Ma'lumotlar - dublikatlarni olib tashlang (Ma'lumotlar - dublikatlarni olib tashlash) ustun nusxasi uchun tuyg'ular yoki funksiya UNIK (BEKISTON) – agar sizda Excel 365 ning so‘nggi versiyasi bo‘lsa.

Excelda yangi varaqlar sukut bo'yicha joriy (oldingi)dan oldin (chapda) yaratilganligi sababli, ushbu katalogdagi shaharlarni kamayish tartibida (Z dan A gacha) saralash ham mantiqan to'g'ri keladi - keyin yaratilgandan keyin shahar varaqlar alifbo tartibida joylashtiriladi.

Ikkinchidan пikkala jadvalni dinamikaga aylantiring (“aqlli”) ular bilan ishlashni osonlashtiradi. Biz buyruqdan foydalanamiz Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash) yoki klaviatura yorlig'i Ctrl+T. Ko'rsatilgan yorliqda konstruktor (Dizayn) ularni chaqiraylik tablProdaji и TableCitymos ravishda:

1-usul. Varaqlar bo'yicha bo'lish uchun makro

Kengaytirilgan yorlig'ida dasturchi (ishlab chiquvchi) tugmasini bosing Visual Basic yoki klaviatura yorliqlaridan foydalaning Alt+F11. Ochilgan so'l muharriri oynasida menyu orqali yangi bo'sh modulni joylashtiring Qo'shish - modul va u erda quyidagi kodni nusxalash:

Sub Splitter() Diapazondagi har bir katak uchun("tablGoroda") Range("tablProdaji").AutoFilter maydoni:=3, Kriteria1:=hujayra.Value diapazoni("tablProdaji[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Keyingi katak Ish varaqlari("Dannye").ShowAllData End Sub	  

Bu erda halqa bilan Har biri uchun… Keyingi katalog kataklari orqali o'tishni amalga oshirdi TableCity, bu erda har bir shahar uchun filtrlanadi (usul Avtomatik filtr) asl savdo jadvalida va keyin natijalarni yangi yaratilgan varaqqa nusxalash. Yo'l davomida yaratilgan varaq shaharning bir xil nomiga o'zgartiriladi va go'zallik uchun ustunlar kengligini avtomatik ravishda o'rnatish yoqiladi.

Yaratilgan makrosni yorliqda Excelda ishga tushirishingiz mumkin dasturchi tugma macros (Dasturchi — Makroslar) yoki klaviatura yorlig'i Alt+F8.

2-usul. Power Query-da bir nechta so'rovlar yarating

Oldingi usul, uning barcha ixchamligi va soddaligi uchun muhim kamchilikka ega - asl savdo jadvaliga o'zgartirishlar kiritilganda makro tomonidan yaratilgan varaqlar yangilanmaydi. Agar tez yangilash zarur bo'lsa, siz VBA + Power Query to'plamidan foydalanishingiz kerak, aniqrog'i, makros yordamida nafaqat statik ma'lumotlarga ega varaqlar, balki yangilangan Power Query so'rovlarini yaratishingiz kerak bo'ladi.

Bu holda makro qisman oldingisiga o'xshaydi (uning ham tsikli bor Har biri uchun… Keyingi katalogdagi shaharlar bo'ylab takrorlash uchun), lekin tsikl ichida endi filtrlash va nusxalash emas, balki Power Query so'rovini yaratish va uning natijalarini yangi varaqqa yuklash:

Sub Splitter2() Diapazondagi har bir katak uchun("Shahar jadvali") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Manba = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""O'zgartirilgan tur"" = Table.TransformColumnTypes(Manba) , {{""Kategoriya"", matnni kiriting}, {""Ism"", matnni kiriting}, {""Shahar"", matn kiriting}, {""Menejer"", matn kiriting}, {""Belgi" sana "", datetime yozing}, {""Xarajat"", raqam turi}})," & Chr(13) & "" & Chr(10) & " #""Filtr qo'llanilgan qatorlar"" = Jadval. " & _ "lectRows(#""O'zgartirilgan tur"", har biri ([Shahar] = """ & hujayra.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Filtr qo'llanilgan qatorlar""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Manba:= _ "OLEDB; Provayder =Microsoft.Mashup.OleDb.1;Ma'lumotlar manbai=$Workbook$;Location=" & cell.Value & ";Kengaytirilgan xususiyatlar=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Massiv("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCellse.alpword. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name = cell.End Subalue  

Uni ishga tushirgandan so'ng, biz shaharlar bo'yicha bir xil varaqlarni ko'ramiz, lekin allaqachon yaratilgan Power Query so'rovlari ularni shakllantiradi:

Manba ma'lumotlaridagi har qanday o'zgarishlar bilan sichqonchaning o'ng tugmasi - buyruq bilan mos keladigan jadvalni yangilash kifoya qiladi Yangilash va saqlash (yangilash) yoki tugma yordamida barcha shaharlarni birdaniga yangilang Hammasini yangilash tab ma'lumotlar (Ma'lumotlar - Hammasini yangilash).

• Makroslar nima, ularni qanday yaratish va ishlatish
• Ishchi kitob varaqlarini alohida fayllar sifatida saqlash
• Kitobning barcha varaqlaridan ma'lumotlarni bitta jadvalga to'plash