Садржај
Пивот табеле је један од најмоћнијих алата у Екцел-у. Они вам омогућавају да анализирате и сумирате различите сажетке великих количина података са само неколико кликова мишем. У овом чланку ћемо се упознати са пивот табелама, разумети шта су, научити како да их креирамо и прилагодимо.
Овај чланак је написан помоћу програма Екцел 2010. Концепт изведених табела се није много променио током година, али начин на који их креирате је мало другачији у свакој новој верзији Екцел-а. Ако имате верзију Екцел-а не 2010, будите спремни да ће се снимци екрана у овом чланку разликовати од онога што видите на екрану.
Мало историје
У раним данима софтвера за табеларне прорачуне, лопта правила Лотус 1-2-3. Његова доминација била је толико потпуна да су напори Мицрософта да развије сопствени софтвер (Екцел) као алтернативу Лотусу изгледали као губљење времена. Сада брзо напред у 2010! Екцел доминира у табелама више него што је Лотус код икада учинио у својој историји, а број људи који још увек користе Лотус је близу нуле. Како се ово могло догодити? Шта је био разлог за тако драматичан преокрет?
Аналитичари идентификују два главна фактора:
- Прво, Лотус је одлучио да је ова нова ГУИ платформа под називом Виндовс само пролазна мода која неће дуго трајати. Одбили су да направе Виндовс верзију Лотуса 1-2-3 (али само на неколико година), предвиђајући да ће ДОС верзија њиховог софтвера бити све што ће потрошачима икада требати. Мицрософт је природно развио Екцел посебно за Виндовс.
- Друго, Мицрософт је представио алатку у Екцел-у под називом ПивотТаблес која није била доступна у Лотусу 1-2-3. Заокретне табеле, ексклузивне за Екцел, показале су се толико корисним да су људи склони да се држе новог пакета Екцел софтвера радије него да наставе са Лотусом 1-2-3, који их није имао.
Заокретне табеле, заједно са потцењивањем успеха Виндовс-а уопште, одиграле су марш смрти за Лотус 1-2-3 и довеле до успеха Мицрософт Екцел-а.
Шта су заокретне табеле?
Дакле, који је најбољи начин да окарактеришете шта су изведене табеле?
Једноставно речено, заокретне табеле су резимеи неких података, креирани да би се олакшала анализа ових података. За разлику од ручно креираних укупних вредности, Екцел изведене табеле су интерактивне. Једном креиране, можете их лако изменити ако не дају слику којој сте се надали. Са само неколико кликова мишем, збројеви се могу преокренути тако да наслови колона постану наслови редова и обрнуто. Можете да урадите многе ствари са заокретним табелама. Уместо да покушавате речима да опишете све карактеристике стожерних табела, лакше је то показати у пракси…
Подаци које анализирате помоћу изведених табела не могу бити насумични. То би требало да буду необрађени подаци, као нека врста листе. На пример, то може бити листа продаје коју је компанија направила у последњих шест месеци.
Погледајте податке приказане на слици испод:
Имајте на уму да ово нису необрађени подаци, као што је већ сажето. У ћелији Б3 видимо 30000 долара, што је вероватно укупан резултат који је Џејмс Кук урадио у јануару. Где су онда оригинални подаци? Одакле цифра од 30000 долара? Где је оригинална листа продаје из које је изведен овај месечни укупни износ? Јасно је да је неко урадио одличан посао организовања и сортирања свих података о продаји за последњих шест месеци и претварања у табелу укупних вредности коју видимо. Шта мислите колико је трајало? Сат? Десет сати?
Чињеница је да табела изнад није стожерна табела. Направљен је ручно од необрађених података ускладиштених на другом месту и за обраду је требало најмање неколико сати. Управо таква збирна табела се може креирати помоћу обртних табела за само неколико секунди. Хајде да схватимо како…
Ако се вратимо на оригиналну продајну листу, то би изгледало отприлике овако:
Можда ћете бити изненађени да са ове листе трговина уз помоћ пивот табела и за само неколико секунди можемо да креирамо месечни извештај о продаји у Екцел-у, који смо анализирали изнад. Да, можемо то и више!
Како направити стожерну табелу?
Прво, уверите се да имате неке изворне податке у Екцел листу. Листа финансијских трансакција је најтипичнија која се јавља. У ствари, то може бити листа било чега: контакт подаци запослених, колекција ЦД-а или подаци о потрошњи горива ваше компаније.
Дакле, покрећемо Екцел ... и учитавамо такву листу ...
Након што отворимо ову листу у Екцелу, можемо да почнемо да креирамо стожерну табелу.
Изаберите било коју ћелију са ове листе:
Затим на картици Инсертион (Инсерт) изаберите команду Ротациона табела (Ротациона табела):
Појавиће се оквир за дијалог Креирајте изведену табелу (Креирање пивот табеле) са два питања за вас:
- Које податке користити за креирање нове изведене табеле?
- Где ставити стожерни сто?
Пошто смо у претходном кораку већ изабрали једну од ћелија листе, цела листа ће бити аутоматски изабрана за креирање пивот табеле. Имајте на уму да можемо да изаберемо другачији опсег, другу табелу, па чак и неки екстерни извор података као што је Аццесс или МС-СКЛ табела базе података. Поред тога, треба да изаберемо где ћемо поставити нову стожерну табелу: на нови лист или на један од постојећих. У овом примеру ћемо изабрати опцију – Нови радни лист (на нови лист):
Екцел ће креирати нови лист и на њега поставити празну стожерну табелу:
Чим кликнемо на било коју ћелију у стожерној табели, појавиће се још један дијалог: Листа поља изведене табеле (Поља изведене табеле).
Листа поља на врху дијалога је листа свих наслова са оригиналне листе. Четири празна подручја на дну екрана омогућавају вам да заокретној табели кажете како желите да сумирате податке. Све док су ове области празне, ни у табели нема ништа. Све што треба да урадимо је да превучемо наслове из горњег дела у празна подручја испод. Истовремено, стожерна табела се аутоматски генерише, у складу са нашим упутствима. Ако погрешимо, можемо да уклонимо наслове из доњег дела или превучемо друге да их заменимо.
Област Вредности (Значења) је вероватно најважније од четири. Који наслов се налази у овој области одређује који ће подаци бити сумирани (збир, просек, максимум, минимум, итд.) То су скоро увек нумеричке вредности. Одличан кандидат за место у овој области су подаци под насловом Износ (Цена) нашег оригиналног стола. Превуците овај наслов у област Вредности (Вредности):
Имајте на уму да је наслов Износ је сада означен квачицом, а у области Вредности (Вредности) појавио се унос Збир износа (поље Износ Износ), што означава да је колона Износ сажео.
Ако погледамо саму стожерну табелу, видећемо збир свих вредности из колоне Износ оригинални сто.
Дакле, наша прва стожерна табела је направљена! Погодно, али не посебно импресивно. Вероватно желимо да добијемо више информација о нашим подацима него што тренутно имамо.
Хајде да се окренемо оригиналним подацима и покушамо да идентификујемо једну или више колона које се могу користити за поделу ове суме. На пример, можемо да формирамо нашу стожерну табелу на начин да се укупан износ продаје израчунава за сваког продавца појединачно. Оне. редови ће бити додати нашој пивот табели са именом сваког продавца у компанији и њиховим укупним износом продаје. Да бисте постигли овај резултат, само превуците наслов продавац (продајни представник) у регион Редне ознаке (Стрингс):
Постаје занимљивије! Наша изведена табела почиње да се обликује…
Видите предности? У неколико кликова направили смо табелу за коју би било потребно много времена да се направи ручно.
Шта друго можемо да урадимо? Па, у извесном смислу, наша стожерна табела је спремна. Направили смо користан резиме оригиналних података. Важне информације су већ примљене! У остатку овог чланка, погледаћемо неке начине за креирање сложенијих заокретних табела, као и научити како да их прилагодите.
Подешавање изведене табеле
Прво, можемо креирати дводимензионалну стожерну табелу. Урадимо то користећи наслов колоне Начин плаћања (Начин плаћања). Само превуците наслов Начин плаћања на област Ознаке колона (колоне):
Добијамо резултат:
Изгледа веома кул!
Сада направимо тродимензионалну табелу. Како би изгледао такав сто? Хајде да видимо…
Превуците заглавље пакет (комплекс) до области филтери извештаја (Филтери):
Обратите пажњу где се налази…
Ово нам даје прилику да филтрирамо извештај на основу „Који је комплекс за одмор плаћен“. На пример, можемо да видимо рашчламбу по продавцима и начинима плаћања за све комплексе, или у пар кликова мишем променимо приказ стожерне табеле и прикажемо исту рашчламбу само за оне који су наручили комплекс Сунсеекерс.
Дакле, ако ово добро разумете, онда се наша стожерна табела може назвати тродимензионалном. Наставимо са постављањем…
Ако се одједном испостави да само плаћање чеком и кредитном картицом (тј. безготовинско плаћање) треба да буде приказано у пивот табели, онда можемо да искључимо приказ наслова готовина (Готовина). За ово, поред Ознаке колона кликните на стрелицу надоле и опозовите избор у пољу за потврду у падајућем менију готовина:
Хајде да видимо како сада изгледа наша стожерна табела. Као што видите, колона готовина нестао од ње.
Форматирање изведених табела у Екцел-у
Очигледно је да су заокретне табеле веома моћан алат, али за сада резултати изгледају помало једноставно и досадно. На пример, бројеви које сабирамо не изгледају као износи у доларима – они су само бројеви. Хајде да поправимо ово.
Примамљиво је да урадите оно на шта сте навикли у таквој ситуацији и једноставно изаберете целу табелу (или цео лист) и користите стандардна дугмад за форматирање бројева на траци са алаткама да бисте подесили жељени формат. Проблем са овим приступом је у томе што ако икада промените структуру изведене табеле у будућности (што се дешава са шансом од 99%), форматирање ће бити изгубљено. Оно што нам треба је начин да то учинимо (скоро) трајним.
Прво, хајде да пронађемо унос Збир износа in Вредности (Вредности) и кликните на њега. У менију који се појави изаберите ставку Поставке поља вредности (Опције поља вредности):
Појавиће се оквир за дијалог Поставке поља вредности (Опције поља вредности).
Притисните дугме Формат броја (Формат броја), отвориће се оквир за дијалог. Формат Целлс (формат ћелије):
Са листе Категорија (Формати бројева) изаберите рачуноводство (Финансијски) и подесите број децималних места на нулу. Сада притисните неколико пута OKда се вратимо на нашу стожерну табелу.
Као што видите, бројеви су форматирани као износи у доларима.
Док смо већ код тога са форматирањем, хајде да поставимо формат за целу изведену табелу. Постоји неколико начина да се то уради. Користимо онај који је једноставнији…
Кликните Алати за заокретну табелу: Дизајн (Рад са изведеним табелама: конструктор):
Затим проширите мени кликом на стрелицу у доњем десном углу одељка Стилови заокретне табеле (Стилови заокретне табеле) да бисте видели обимну колекцију уграђених стилова:
Изаберите било који одговарајући стил и погледајте резултат у вашој стожерној табели:
Друге поставке изведене табеле у Екцел-у
Понекад је потребно да филтрирате податке по датумима. На пример, на нашој листи заната има много, много датума. Екцел пружа алат за груписање података по дану, месецу, години итд. Да видимо како се то ради.
Прво уклоните унос. Начин плаћања из региона Ознаке колона (Колоне). Да бисте то урадили, превуците га назад на листу наслова и на његово место померите наслов Дате Боокед (датум резервације):
Као што видите, ово је привремено учинило нашу стожерну табелу бескорисном. Екцел је направио засебну колону за сваки датум на који је извршена трговина. Као резултат тога, добили смо веома широк сто!
Да бисте ово поправили, кликните десним тастером миша на било који датум и изаберите из контекстног менија ГРУПА (Група):
Појавиће се дијалог за груписање. Ми бирамо Месеци (месеци) и кликните OK:
Воила! Ова табела је много кориснија:
Иначе, ова табела је скоро идентична оној приказаној на почетку чланка, где су укупни подаци о продаји састављени ручно.
Постоји још једна веома важна тачка коју треба да знате! Можете креирати не један, већ неколико нивоа наслова редова (или колона):
… и изгледаће овако…
Исто се може урадити са насловима колона (или чак филтерима).
Вратимо се на првобитни облик табеле и видимо како да прикажемо просеке уместо збира.
Да бисте започели, кликните на Збир износа и из менија који се појави изаберите Поставке поља вредности (Опције поља вредности):
Листа Сумирај поље вредности по (Операција) у дијалогу Поставке поља вредности (Опције поља вредности) изаберите Просек (Просек):
У исто време, док смо овде, мењајмо се ЦустомНаме (Прилагођено име) са Просек износа (поље Износ Износ) на нешто краће. Унесите у ово поље нешто попут Авг:
Press OK и види шта се дешава. Имајте на уму да су се све вредности промениле са укупних на просечне, а заглавље табеле (у горњој левој ћелији) се променило у Авг:
Ако желите, можете одмах добити износ, просек и број (продаја) смештених у једну стожерну табелу.
Ево водича корак по корак о томе како то да урадите, почевши од празног пивот табеле:
- Превуците заглавље продавац (продајни представник) у регион Ознаке колона (Колоне).
- Превуците наслов три пута Износ (Цена) до области Вредности (Вредности).
- За прво поље Износ промени наслов у укупан (Износ), а формат броја у овом пољу је рачуноводство (Финансијски). Број децималних места је нула.
- Друго поље Износ име Просечане, подесите операцију за то Просек (Просек) и формат броја у овом пољу се такође мењају у рачуноводство (Финансијски) са нула децимала.
- За треће поље Износ поставите наслов Рачунати и операција за њега - Рачунати (Количина)
- У Ознаке колона (Колоне) поље аутоматски креирано Σ Вредности (Σ Вредности) – превуците га у област Редне ознаке (Линије)
Ево шта ћемо завршити:
Укупан износ, просечна вредност и број продаја – све у једној пивот табели!
Zakljucak
Пивот табеле у Мицрософт Екцел-у садрже много функција и подешавања. У тако малом чланку, нису ни близу да их све покрију. Била би потребна мала књига или велики веб сајт да би се у потпуности описали све могућности заокретних табела. Храбри и радознали читаоци могу да наставе са истраживањем заокретних табела. Да бисте то урадили, само кликните десним тастером миша на скоро било који елемент заокретне табеле и погледајте које функције и подешавања се отварају. На траци ћете пронаћи две картице: Алати за заокретну табелу: Опције (анализа) и Дизајн (Конструктор). Немојте се плашити да направите грешку, увек можете да избришете изведену табелу и да почнете изнова. Имате прилику коју дугогодишњи корисници ДОС-а и Лотуса 1-2-3 никада нису имали.