Садржај
Мицрософт Екцел има много алата за прикупљање података из неколико табела (са различитих листова или из различитих датотека): директне везе, функције ИНДИРЕКТАН (ИНДИРЕКТАН), Повер Куери и Повер Пивот додаци итд. Са ове стране барикаде све изгледа добро.
Али ако наиђете на инверзни проблем – ширење података из једне табеле у различите листове – онда ће све бити много тужније. Тренутно у арсеналу Екцел-а, нажалост, не постоје цивилизовани уграђени алати за такво раздвајање података. Дакле, мораћете да користите макро у Висуал Басиц-у или да користите комбинацију снимача макроа + Повер Куери са малим „пречишћавањем датотеке“.
Хајде да детаљније погледамо како се ово може применити.
Формулисање проблема
Имамо као почетне податке такву табелу величине више од 5000 редова за продају:
Задатак: расподелити податке из ове табеле по градовима на посебним листовима ове књиге. Оне. на излазу треба да добијете на сваком листу само оне редове из табеле у којима је била продаја у одговарајућем граду:
Припремити
Да не бисмо компликовали макро код и учинили га што лакшим за разумевање, хајде да извршимо неколико припремних корака.
Прво, направите засебну табелу за тражење, где ће у једној колони бити наведени сви градови за које желите да направите засебне листове. Наравно, овај директоријум можда не садржи све градове који су присутни у изворним подацима, већ само оне за које су нам потребни извештаји. Најлакши начин за креирање такве табеле је коришћење команде Подаци – Уклоните дупликате (Подаци — Уклони дупликате) за копију колоне град или функцију УНИК (ЈЕДИНСТВЕНА) – ако имате најновију верзију програма Екцел 365.
Пошто се нови листови у Екцел-у подразумевано креирају пре (лево од) тренутног (претходног), такође има смисла сортирати градове у овом директоријуму у опадајућем редоследу (од З до А) – затим након креирања, град листови ће бити распоређени по азбучном реду.
Друго, ппретворити обе табеле у динамичку („паметно“) да би вам олакшао рад са њима. Користимо команду Почетна – Формат као табела (Почетна — Формат као табела) или пречица на тастатури Цтрл+T. На картици која се појави Конструктор (Дизајн) назовимо их таблПродаји и ТаблеЦити, редом:
Метод 1. Макро за поделу по листовима
На картици Напредно развијач (Програмер) кликните на дугме Висуал Басиц или користите пречицу на тастатури други+ФКСНУМКС. У прозору уређивача макроа који се отвори, уметните нови празан модул кроз мени Инсерт – Модул и копирајте тамо следећи код:
Суб Сплиттер() Фор Евери целл Ин Ранге("таблГорода") Ранге("таблПродажи").АутоФилтер Фиелд:=3, Цритериа1:=целл.Валуе Ранге("таблПродажи[#Алл]").СпециалЦеллс(клЦеллТипеВисибле).Цопи Схеетс.Адд АцтивеСхеет.Пасте АцтивеСхеет.Наме = целл.Валуе АцтивеСхеет.УседРанге.Цолумнс.АутоФит Нект целл Ворксхеетс("Данние").СховАллДата Енд Суб Овде са петљом За сваки … Следећи имплементирао пролаз кроз ћелије именика ТаблеЦити, где се за сваки град филтрира (метод АутоФилтер) у оригиналној продајној табели и затим копирање резултата у новокреирани лист. Успут, креирани лист се преименује у исто име града и на њему се укључује аутоматско подешавање ширине колона за лепоту.
На картици можете покренути креирани макро у Екцел-у развијач дугме Макрои (Програмер — Макрои) или пречица на тастатури други+F8.
Метод 2. Креирајте више упита у Повер Куерију
Претходни метод, упркос својој компактности и једноставности, има значајан недостатак - листови креирани макроом се не ажурирају када се промене у оригиналној табели продаје. Ако је потребно ажурирање у ходу, онда ћете морати да користите ВБА + Повер Куери пакет, односно да креирате помоћу макроа не само листове са статичким подацима, већ ажуриране Повер Куери упите.
Макро је у овом случају делимично сличан претходном (такође има циклус За сваки … Следећи да пређете преко градова у директоријуму), али унутар петље више неће бити филтрирања и копирања, већ креирања Повер Куери упита и отпремања његових резултата на нови лист:
Суб Сплиттер2() За сваку ћелију у опсегу("табела градова") АцтивеВоркбоок.Куериес.Адд Наме:=целл.Валуе, Формула:= _ "лет" & Цхр(13) & "" & Цхр(10) & " Соурце = Екцел.ЦуррентВоркбоок(){[Наме=""ТаблеСалес""]}[Цонтент]," & Цхр(13) & "" & Цхр(10) & " #""Цхангед Типе"" = Табле.ТрансформЦолумнТипес(Соурце , {{""Категорија"", унесите текст}, {""Име"", унесите текст}, {""Град"", унесите текст}, {""Менаџер"", унесите текст}, {""Посао дате "", тип датетиме}, {""Цост"", типе нумбер}})," & Цхр(13) & "" & Цхр(10) & " #""Редови са примењеним филтером"" = Табле.Се " & _ "лецтРовс(#""Промењен тип"", сваки ([Град] = """ & целл.Валуе & """))" & Цхр(13) & "" & Цхр(10) & "ин " & Цхр(13) & "" & Цхр(10) & " #""Редови са примењеним филтером""" АцтивеВоркбоок.Ворксхеетс.Адд Витх АцтивеСхеет.ЛистОбјецтс.Адд(СоурцеТипе:=0, Соурце:= _ "ОЛЕДБ; Добављач =Мицрософт.Масхуп.ОлеДб.1; Извор података=$Воркбоок$;Лоцатион=" & целл.Валуе & ";Ектендед Пропертиес=""""" _ , Одредиште:=Распон("$А$1")). КуериТабле .ЦоммандТипе = клЦмд Скл .ЦоммандТект = Арраи("СЕЛЕЦТ *ФРОМ [" & целл.Валуе & "]") .РовНумберс = Фалсе .ФиллАдјацентФормулас = Фалсе .ПресервеФорматтинг = Тачно .РефресхОнФилеОпен = Фалсе .БацкгроундКуери = Труе .РефресхСтиле = клИнсертДелете. СавеДата = Труе .АдјустЦолумнВидтх = Труе .РефресхПериод = 0 .ПресервеЦолумнИнфо = Труе .ЛистОбјецт.ДисплаиНаме = целл.Валуе .Рефресх БацкгроундКуери:=Фалсе Енд Витх АцтивеСхеет.Наме = Нект целл.Валуе Субалуе Након покретања, видећемо исте листове по градовима, али ће их формирати већ креирани Повер Куери упити:
Уз било какве промене у изворним подацима, биће довољно да ажурирате одговарајућу табелу десним тастером миша – командом Ажурирајте и сачувајте (Освјежи) или ажурирајте све градове одједном користећи дугме Ажурирати све табулатор Датум (Подаци — Освежи све).
- Шта су макрои, како их креирати и користити
- Чување листова радне свеске као засебних датотека
- Прикупљање података са свих листова књиге у једну табелу