Мундариҷа
Microsoft Excel дорои асбобҳои зиёде барои ҷамъоварии маълумот аз якчанд ҷадвалҳо (аз варақаҳои гуногун ё файлҳои гуногун): истинодҳои мустақим, функсия Бавосита (БАЙРУСУТИ), Power Query ва Power Pivot замимаҳо ва ғайра. Аз ин тарафи баррикада ҳама чиз хуб ба назар мерасад.
Аммо агар шумо ба мушкилоти баръакс дучор шавед - паҳн кардани маълумот аз як ҷадвал ба варақаҳои гуногун - он гоҳ ҳама чиз ғамгинтар хоҳад буд. Дар айни замон, мутаассифона, дар арсенали Excel ягон абзори мутамаддини дарунсохт барои чунин ҷудокунии маълумот мавҷуд нест. Пас шумо бояд макросро дар Visual Basic истифода баред ё пас аз он макросабзкунанда + комбинатсияи Power Query -ро бо каме "тозакунии файл" истифода баред.
Биёед ба таври муфассал дида бароем, ки ин корро чӣ тавр амалӣ кардан мумкин аст.
Тартиб додани мушкилот
Мо ҳамчун маълумоти ибтидоӣ чунин ҷадвал дорем, ки андозаи зиёда аз 5000 сатр барои фурӯш:
Вазифа: дар варақаҳои алоҳидаи ин китоб маълумот аз рӯи ҷадвал тақсим карда шавад. Онхое. Дар натиҷа, шумо бояд дар ҳар як варақ танҳо он сатрҳоро аз ҷадвале, ки фурӯш дар шаҳри мувофиқ буд, гиред:
Омодагӣ кунед
Барои он ки коди макроро душвор насозед ва онро то ҳадди имкон осонтар фаҳмед, биёед якчанд қадамҳои омодагиро иҷро кунем.
Аввалан, ҷадвали ҷустуҷӯи алоҳида эҷод кунед, ки дар он як сутун ҳамаи шаҳрҳоеро номбар мекунад, ки шумо барои онҳо варақаҳои алоҳида эҷод кардан мехоҳед. Албатта, ин феҳрист на ҳама шаҳрҳоеро дар бар гирад, ки дар маълумоти манбаъ мавҷуд аст, балки танҳо онҳое, ки барои онҳо ҳисобот лозим аст. Роҳи осонтарини сохтани чунин ҷадвал ин истифодаи фармон аст Маълумот - Дубликатҳоро нест кунед (Маълумот - Нест кардани такрорӣ) барои нусхаи сутун ҳиссиёт ё функсия УНИК (БЕКОР) – агар шумо версияи охирини Excel 365 дошта бошед.
Азбаски варақаҳои нав дар Excel ба таври нобаёнӣ пеш аз (аз чапи) ҷорӣ (қаблӣ) сохта мешаванд, инчунин маъно дорад, ки шаҳрҳои ин феҳрист бо тартиби камшавӣ (аз Z то A) ҷудо карда шаванд - пас аз эҷод, шаҳр варақаҳо аз рӯи алифбо ҷойгир карда мешаванд.
Дуюм, дар пҳарду ҷадвалро ба динамикӣ табдил диҳед («интеллектуалӣ») барои осон кардани кор бо онҳо. Мо фармонро истифода мебарем Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал) ё миёнабури клавиатура Ctrl+T. Дар ҷадвале, ки пайдо мешавад Конструктор (Тарроҳӣ) биёед онҳоро даъват кунем tablProdaji и TableCityмутаносибан:
Усули 1. Макрос барои тақсимот аз рӯи варақҳо
Дар ҷадвали Advanced таҳиякунанда (Таҳиягар) тугмаро пахш кунед Visual Basic ё миёнабури клавиатураро истифода баред Alt+F11. Дар равзанаи муҳаррири макрос, ки кушода мешавад, ба воситаи меню модули нави холиро ворид кунед Ворид - Модул ва рамзи зеринро дар он ҷо нусхабардорӣ кунед:
Sub Splitter() Барои Ҳар як чашмаки Дар Диапазон("таблГорода") Диапазон("таблПродажи").Майдони AutoFilter:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = ячейка.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, формула:= _ "бигзор" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Ном=""TableSales""]}[Мундариҷа]," & Chr(13) & "" & Chr(10) & " #""Намуди тағирёфта"" = Table.TransformColumnTypes(Сарчашма) , {{""Категория"", матнро нависед}, {""Ном"", матнро нависед}, {""Шаҳр"", матнро нависед}, {""Менеҷер"", матнро нависед}, {""Муомила сана "", санаи вақтро нависед}, {""Арзиш "", рақамро нависед}})," & Chr(13) & "" & Chr(10) & " #""Сатрҳое, ки филтр истифода мешаванд"" = Ҷадвали Ҷд. " & _ "lectRows(#""Намуди тағирёфта"", ҳар як ([Шаҳр] = """ & ячейка.Арзиш & """))" & Chr(13) & "" & Chr(10) & "дар " & Chr(13) & "" & Chr(10) & " #""Саторҳо бо филтр истифода мешаванд""" ActiveWorkbook.Worksheets.Add Бо ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Таъминкунанда =Microsoft.Mashup.OleDb.1;Манбаи маълумот=$Китоби корӣ$;Макон =" & cell.Value & ";Хусусиятҳои васеъ = """"" _ , Таъинот:=Дипазон("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Массив ("SELECT *АЗ [" & cell.Value & "]"). SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Анҷоми бардурӯғ бо ActiveSheet.Name = cell.End. Пас аз ба кор андохтани он, мо ҳамон варақҳоро аз рӯи шаҳр хоҳем дид, аммо дархостҳои аллакай сохташудаи Power Query онҳоро ташкил медиҳанд:
Ҳангоми ҳар гуна тағирот дар маълумоти манбаъ, барои навсозии ҷадвали мувофиқ бо тугмаи рости муш - фармон кифоя хоҳад буд Навсозӣ ва Захира (навсозӣ) ё ҳама шаҳрҳоро якбора бо истифода аз тугма навсозӣ кунед Навсозии ҳама ҷадвали Маълумот (Маълумот - Ҳамаро навсозӣ кунед).
- Макросҳо чист, чӣ гуна сохтан ва истифода бурдани онҳо
- Нигоҳ доштани варақаҳои китоби корӣ ҳамчун файлҳои алоҳида
- Ҷамъоварии маълумот аз ҳама варақаҳои китоб дар як ҷадвал