Недавно сам добио питање од читаоца о комбиновању више радних листова у истој радној свесци у један радни лист.
Замолио сам га да користи Повер Куери за комбиновање различитих листова, али сам онда схватио да за некога ко је нов у Повер Куерију ово може бити тешко.
Зато сам одлучио да напишем овај водич и покажем тачне кораке за комбиновање више листова у једну табелу помоћу програма Повер Куери.
Испод видеа где показујем како да комбинујем податке са више листова/табела помоћу програма Повер Куери:
Испод су писана упутства о томе како комбиновати више листова (у случају да више волите писани текст од видео записа).
Напомена: Повер Куери се може користити као додатак у програмима Екцел 2010 и 2013, и уграђена је функција од програма Екцел 2016 па надаље. На основу ваше верзије, неке слике могу изгледати другачије (снимци слика коришћени у овом водичу потичу из програма Екцел 2016).
Комбинујте податке са више радних листова користећи Повер Куери
Када комбинујете податке са различитих листова помоћу програма Повер Куери, потребно је да подаци буду у Екцел табели (или барем у именованим опсезима). Ако подаци нису у Екцел табели, метода приказана овде не би функционисала.
Претпоставимо да имате четири различита листа - исток, запад, север и југ.
Сваки од ових радних листова има податке у Екцел табели, а структура табеле је конзистентна (тј. Заглавља су иста).
Кликните овде да преузмете податке и пратите их.
Ова врста података је изузетно једноставна за комбиновање помоћу програма Повер Куери (који заиста добро функционише са подацима у Екцел табели).
Да би ова техника најбоље функционисала, боље је да имате називе за своје Екцел табеле (радите и без ње, али је лакше користити када су табеле именоване).
Табелама сам дао следећа имена: Еаст_Дата, Вест_Дата, Нортх_Дата и Соутх_Дата.
Ево корака за комбиновање више радних листова са Екцел табелама помоћу програма Повер Куери:
- Идите на картицу Подаци.
- У групи Гет & Трансформ Дата кликните на опцију „Гет Дата“.
- Идите на опцију „Из других извора“.
- Кликните на опцију „Празан упит“. Ово ће отворити уређивач Повер Куери.
- У уређивачу упита упишите следећу формулу у траку са формулама: = Екцел.ЦуррентВоркбоок(). Имајте на уму да формуле Повер Куери разликују велика и мала слова, па морате да користите тачну формулу као што је поменуто (иначе ћете добити грешку).
- Притисните тастер Ентер. Ово ће вам показати све називе табела у целој радној свесци (такође ће вам приказати именоване опсеге и/или везе у случају да постоје у радној свесци).
- [Опционални корак] У овом примеру желим да комбинујем све табеле. Ако желите да комбинујете само одређене Екцел табеле, можете да кликнете на падајућу икону у заглављу назива и изаберете оне које желите да комбинујете. Слично, ако сте именовали опсеге или везе, а желите само да комбинујете табеле, можете уклонити и те именоване опсеге.
- У ћелији заглавља садржаја кликните на двострану стрелицу.
- Изаберите колоне које желите да комбинујете. Ако желите да комбинујете све колоне, уверите се да је (Изабери све колоне) означено.
- Поништите опцију „Користи оригинално име колоне као префикс“.
- Притисните ОК.
Горе наведени кораци би комбиновали податке са свих радних листова у једну табелу.
Ако пажљиво погледате, видећете да последња колона (крајња десна) има назив Екцел табела (Еаст_Дата, Вест_Дата, Нортх_Дата и Соутх_Дата). Ово је идентификатор који нам говори који запис је дошао из које Екцел табеле. Ово је и разлог зашто сам рекао да је боље имати описне називе за Екцел табеле.
Ево неколико измена које можете да урадите на комбинованим подацима у самом Повер Куери -у:
- Превуците и поставите колону Име на почетак.
- Уклоните „_Дата“ из колоне са именом (тако да у колони са именом остајете Исток, Запад, Север и Југ). Да бисте то урадили, кликните десним тастером миша на заглавље Наме и кликните на Реплаце Валуес. У дијалогу Замени вредности замените _Дата празним местом.
- Промените колону Подаци да приказује само датуме (а не и време). Да бисте то урадили, кликните на заглавље колоне Датум, идите на картицу „Претвори“ и промените тип података у Датум.
- Преименујте упит у ЦонсолидатедДата.
Сада када имате комбиноване податке са свих радних листова у програму Повер Куери, можете их учитати у Екцел - као нову табелу на новом радном листу.
Да уради ово. следите доле наведене кораке:
- Кликните на картицу „Датотека“.
- Кликните на Затвори и Учитај у.
- У дијалогу Увоз података изаберите опције Табела и Нови радни лист.
- Притисните У реду.
Горе наведени кораци би комбиновали податке са свих радних листова и дали вам те комбиноване податке на новом радном листу.
Један проблем који морате решити када користите ову методу
У случају да сте користили горњи метод за комбиновање свих табела у радној свесци, вероватно ћете се суочити са проблемом.
Погледајте број редова комбинованих података - 1304 (што је тачно).
Сада, ако освежим упит, број редова се мења на 2607. Поново освежите и промениће се на 3910.
Ево проблема.
Сваки пут када освежите упит, он додаје све записе у оригиналним подацима комбинованим подацима.
Напомена: Са овим проблемом ћете се суочити само ако сте за комбиновање користили Повер Куери СВЕ ИЗВРСНЕ ТАБЕЛЕ у радној свесци. У случају да сте одабрали одређене табеле за комбиновање, нећете се суочити са овим проблемом.Хајде да схватимо узрок овог проблема и како то исправити.
Када освежите упит, он се враћа уназад и прати све кораке које смо предузели за комбиновање података.
У кораку где смо користили формулу = Екцел.ЦуррентВоркбоок (), дао нам је списак свих табела. Ово је добро функционисало први пут јер су постојала само четири стола.
Али када освежите, у радној свесци има пет табела - укључујући нову табелу коју је Повер Куери уметнуо тамо где имамо комбиноване податке.
Дакле, сваки пут када освежите упит, осим четири Екцел табеле које желимо да комбинујемо, у добијене податке додаје и постојећу табелу упита.
То се назива рекурзија.
Ево како да решите ово питање.
Када унесете = Екцел.ЦуррентВоркбоок () у формулар Повер Куери и притиснете ентер, добићете листу Екцел табела. Да бисте били сигурни да ћете комбиновати само табеле са радног листа, морате некако филтрирати само ове табеле које желите да комбинујете и уклонити све остало.
Ево корака како бисте били сигурни да имате само потребне табеле:
- Кликните на падајући мени и задржите показивач миша на текстуалним филтерима.
- Кликните на опцију Садржи.
- У дијалогу Филтерски редови унесите _Дата у поље поред опције „садржи“.
- Притисните ОК.
Можда нећете видети никакве промене у подацима, али ако то учините спречићете да се добијена табела поново додаје када се упит освежи.
Имајте на уму да смо у горњим корацима користили „_Дата”Да филтрира како смо на тај начин именовали табеле. Али шта ако се ваше табеле не именују доследно. Шта ако су сви називи табела насумични и немају ништа заједничко.
Ево начина да то решите - користите филтер „није једнако“ и унесите назив упита (што би у нашем примеру било ЦонсолидатедДата). Ово ће осигурати да све остане исто и да се добијена табела упита која се креира филтрира.
Осим што Повер Куери чини читав процес комбиновања података са различитих листова (или чак истих листова) прилично лаким, још једна предност његове употребе је што га чини динамичним. Ако било којој табели додате још записа и освежите Повер Куери, аутоматски ће вам се дати комбиновани подаци.Важна напомена: У примеру који смо користили у овом водичу заглавља су била иста. У случају да су заглавља различита, Повер Куери ће комбиновати и креирати све колоне у новој табели. Ако су подаци доступни за ту колону, они ће бити приказани, иначе ће показати нулу.
Можда ће вам се допасти и следећи водичи за Повер Куери:
- Комбинујте податке из више радних свезака у програму Екцел (помоћу програма Повер Куери).
- Како откинути податке у Екцелу помоћу програма Повер Куери (познат и као Гет & Трансформ)
- Набавите листу имена датотека из фасцикли и потфасцикли (помоћу програма Повер Куери)
- Спојите табеле у програму Екцел помоћу програма Повер Куери.
- Како упоредити два Екцел листа/датотеке
