Агуулга
Microsoft Excel нь хэд хэдэн хүснэгтээс (өөр өөр хуудас эсвэл өөр файлуудаас) мэдээлэл цуглуулах олон хэрэгсэлтэй: шууд холбоос, функц. ҮНЭГҮЙ (ШУУД), Power Query болон Power Pivot нэмэлтүүд гэх мэт. Хаалтны энэ талаас харахад бүх зүйл сайхан харагдаж байна.
Гэхдээ хэрэв та урвуу асуудалтай тулгарвал - нэг хүснэгтээс өгөгдлийг өөр өөр хуудсанд тараах - бүх зүйл илүү гунигтай байх болно. Харамсалтай нь Excel-ийн арсенал дээр ийм өгөгдлийг салгах соёл иргэншсэн хэрэгсэл одоогоор байхгүй байна. Тиймээс та Visual Basic дээр макро ашиглах эсвэл макро бичигч + Power Query хослолыг ашиглан бага зэрэг "файлыг сайжруулах" хэрэгтэй болно.
Үүнийг хэрхэн хэрэгжүүлэх талаар нарийвчлан авч үзье.
Асуудлын томъёолол
Бидэнд борлуулалтын хувьд 5000-аас дээш мөр бүхий хүснэгтийн анхны өгөгдөл байна.
Даалгавар: энэ хүснэгтийн өгөгдлийг энэ номын тусдаа хуудсан дээр хотоор тараах. Тэдгээр. Гаралт дээр та хуудас бүр дээр зөвхөн тухайн хотод борлуулалт хийгдсэн хүснэгтийн мөрүүдийг авах хэрэгтэй.
Бэлтгэх
Макро кодыг төвөгтэй болгохгүйн тулд аль болох хялбар ойлгохын тулд бэлтгэлийн хэд хэдэн алхамыг хийцгээе.
Нэгдүгээрт, тусдаа хайлтын хүснэгт үүсгэх, энд нэг баганад тусад нь хуудас үүсгэхийг хүссэн бүх хотуудыг жагсаах болно. Мэдээжийн хэрэг, энэ лавлах нь эх мэдээлэлд байгаа бүх хотыг агуулаагүй байж магадгүй, гэхдээ зөвхөн бидэнд тайлан хэрэгтэй байгаа хотуудыг л агуулж болно. Ийм хүснэгт үүсгэх хамгийн хялбар арга бол командыг ашиглах явдал юм Өгөгдөл - Давхардсан мэдээллийг устгах (Өгөгдөл - Давхардлыг арилгах) баганын хуулбарын хувьд Хот эсвэл функц UNIK (ОНЦГОЙ) – хэрэв танд Excel 365-ийн хамгийн сүүлийн хувилбар байгаа бол.
Excel-ийн шинэ хуудаснууд нь одоогийн (өмнөх)-ийн өмнө (зүүн талд) өгөгдмөл байдлаар үүсгэгддэг тул энэ лавлах дахь хотуудыг буурах дарааллаар (Z-ээс A хүртэл) эрэмбэлэх нь зүйтэй. хуудсыг цагаан толгойн дарааллаар байрлуулна.
Хоёрдугаарт пХоёр хүснэгтийг динамик болгон хөрвүүлэх (“ухаалаг”) тэдэнтэй ажиллахад хялбар болгох. Бид тушаалыг ашигладаг Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах) эсвэл гарын товчлол Ctrl+T. Харагдах таб дээр байгуулагч (Дизайн) тэднийг дуудъя tablProdaji и TableCityтус тус:
Арга 1. Хуудсаар хуваах макро
Нарийвчилсан таб дээр Developer (Хөгжүүлэгч) товчин дээр дарна уу Visual Basic эсвэл гарын товчлолыг ашиглана уу Alt+F11. Нээгдсэн макро засварлагч цонхонд цэсээр дамжуулан шинэ хоосон модулийг оруулна уу Оруулах - Модуль Дараах кодыг тэнд хуулж авна уу.
Sub Splitter() Муж дахь нүд бүрийн хувьд("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy. Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Энд гогцоотой Тус бүрийн хувьд ... Дараа нь лавлахын нүдээр дамжин өнгөрөх хэсгийг хэрэгжүүлсэн TableCity, хаана нь хот бүрийн хувьд шүүдэг (арга Авто шүүлтүүр) анхны борлуулалтын хүснэгтэд, дараа нь үр дүнг шинээр үүсгэсэн хуудас руу хуулна. Замдаа үүсгэсэн хуудасны нэрийг хотын нэрээр сольж, гоо сайхны зориулалтаар баганын өргөнийг автоматаар тохируулахыг асаав.
Та таб дээр Excel дээр үүсгэсэн макро ажиллуулж болно Developer товч Макроны (Хөгжүүлэгч — Макро) эсвэл гарын товчлол Alt+F8.
Арга 2. Power Query дээр олон асуулга үүсгэх
Өмнөх арга нь бүх авсаархан, энгийн байдлын хувьд мэдэгдэхүйц сул талтай байдаг - борлуулалтын анхны хүснэгтэд өөрчлөлт оруулах үед макро ашиглан үүсгэсэн хуудаснууд шинэчлэгддэггүй. Хэрэв нэн даруй шинэчлэх шаардлагатай бол та VBA + Power Query багцыг ашиглах, эс тэгвээс статик өгөгдөл бүхий хуудас биш, харин шинэчилсэн Power Query асуулга ашиглан макро үүсгэх хэрэгтэй болно.
Энэ тохиолдолд макро нь өмнөхтэй хэсэгчлэн төстэй (энэ нь мөн мөчлөгтэй Тус бүрийн хувьд ... Дараа нь лавлах доторх хотуудыг давтах), гэхдээ давталт дотор шүүж, хуулахаа больж, Power Query хайлт үүсгэж, үр дүнг шинэ хуудсанд байршуулах болно.
Sub Splitter2() Муж дахь нүд бүрийн хувьд("Хотын хүснэгт") ActiveWorkbook.Queries.Нэмэх нэр:=cell.Value, Формула:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Нэр=""Хүснэгт Борлуулалт"]}[Агуулга]," & Chr(13) & "" & Chr(10) & " #""Өөрчлөгдсөн төрөл"" = Хүснэгт.TransformColumnTypes(Эх сурвалж , {{""Ангилал"", текст бичих}, {""Нэр"", бичвэр бичих}, {""Хот"", текст бичих}, {""Менежер"", текст бичих}, {""Хэлэлцээ" огноо "", datetime гэж бичнэ үү}, {""Зардал"", дугаар бичнэ үү}})," & Chr(13) & "" & Chr(10) & " #""Шүүлтүүр ашигласан мөрүүд"" = Хүснэгт.Se " & _ "lectRows(#""Өөрчлөгдсөн төрөл"", тус бүр ([Хот] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Шүүлтүүр ашигласан мөрүүд""" ActiveWorkbook.Worksheets.Add ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Үйлчилгээ үзүүлэгч =Microsoft.Mashup.OleDb.1;Өгөгдлийн эх сурвалж=$Ажлын ном$;Байршил=" & cell.Value & ";Өргөтгөсөн шинж чанарууд=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = Үнэн .RefreshStyle = xlInsertDeleteCellsword.SavePassword. SaveData = Үнэн .AdjustColumnWidth = Үнэн .RefreshPeriod = 0 .PreserveColumnInfo = Үнэн .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name = cell.Endalue Үүнийг эхлүүлсний дараа бид ижил хуудсыг хотоор нь харах болно, гэхдээ аль хэдийн үүсгэсэн Power Query асуулга нь тэдгээрийг үүсгэх болно:
Эх сурвалжийн өгөгдөлд өөрчлөлт орсон тохиолдолд хулганы баруун товчийг - командыг ашиглан харгалзах хүснэгтийг шинэчлэхэд хангалттай Шинэчлэх & Хадгалах (Сэргээх) эсвэл товчлуурыг ашиглан бүх хотыг нэг дор бөөнөөр нь шинэчлэх Бүгдийг шинэчил таб Өгөгдөл (Өгөгдөл - Бүгдийг шинэчлэх).
- Макро гэж юу вэ, тэдгээрийг хэрхэн үүсгэх, ашиглах
- Ажлын номын хуудсыг тусдаа файл болгон хадгалах
- Номын бүх хуудаснаас өгөгдлийг нэг хүснэгтэд цуглуулах