Рад са обртним табелама у програму Мицрософт Екцел

ПивотТаблес су једна од најмоћнијих функција програма Мицрософт Екцел. Омогућавају анализу и сумирање великих количина података у само неколико кликова мишем. У овом чланку истражујемо ПивотТабеле, разумемо шта су и научимо како да их креирамо и прилагођавамо.

Напомена: Овај чланак је написан помоћу програма Екцел 2010 (Бета). Концепт ПивотТабле-а се мало променио током година, али се метод стварања променио у скоро свакој итерацији Екцела. Ако користите верзију програма Екцел која није 2010, очекујте различите екране од оних које видите у овом чланку.

А Литтле Хистори

У раним данима програма за табеларне прорачуне, Лотус 1-2-3 је владао склоништем. Његова доминација је била тако потпуна да су људи мислили да је губитак времена за Мицрософт да се труди да развија сопствени софтвер за табеларне прорачуне (Екцел) да би се такмичио са Лотусом. Фласх-форвард до 2010, и Екцелова доминација на тржишту за табеларне прорачуне је већа него што је Лотус икада био, док се број корисника који још увек воде Лотус 1-2-3 приближава нули. Како се то догодило? Шта је изазвало тако драматичан преокрет судбине?

Индустријски аналитичари су се позабавили два фактора: Прво, Лотус је одлучио да је ова фина нова ГУИ платформа названа “Виндовс” била пролазна мода која никада не би полетјела. Они су одбили да створе Виндовс верзију Лотус 1-2-3 (за неколико година, ионако), предвиђајући да ће њихова ДОС верзија софтвера бити све што је икада потребно. Мицрософт је, наравно, развио Екцел искључиво за Виндовс. Друго, Мицрософт је развио функцију за Екцел коју Лотус није пружио у 1-2-3, наиме ПивотТаблес.  Значајка ПивотТаблес, ексклузивно за Екцел, сматрана је тако запањујуће корисном да су људи били вољни да науче цијели нови софтверски пакет (Екцел) умјесто да се држе програма (1-2-3) који га нису имали. Ова једна карактеристика, заједно са погрешном проценом успеха Виндовса, била је смртна звер за Лотус 1-2-3, и почетак успеха Мицрософт Екцел-а.

Ундерстандинг ПивотТаблес

Дакле, шта је тачно изведена табела?

Једноставно речено, ПивотТабле је сажетак неких података, креираних да омогуће једноставну анализу наведених података. Али за разлику од ручно креираног резимеа, Екцел ПивотТаблес су интерактивне. Када једном креирате, можете га лако променити ако не нуди тачан увид у ваше податке за које сте се надали. У неколико кликова сажетак се може "закретати" - ротирати на такав начин да заглавља ступаца постају заглавља реда, и обрнуто. Има још много тога што се може урадити. Уместо да покушамо да опишемо све карактеристике ПивотТабела, једноставно ћемо их демонстрирати ...

Подаци које анализирате помоћу изведене табеле не могу бити праведни било који подаци - то мора бити рав подаци који су претходно били необрађени (без сумње) - обично нека врста листе. Пример за то може бити листа продајних трансакција у компанији у последњих шест месеци.

Прегледајте доле приказане податке:

Приметите да је ово не сирови подаци. У ствари, то је већ неки сажетак. У ћелији Б3 можемо видјети 30.000 долара, што је очигледно укупна продаја Јамеса Цоока за мјесец јануар. Где су сирови подаци? Како смо дошли до бројке од 30.000 долара? Где је оригинална листа трансакција продаје од које је ова цифра генерисана? Јасно је да је негде неко морао покушати да упореди све трансакције продаје у протеклих шест месеци са резимеом који смо видели горе. Колико дуго мислите да је ово трајало? Сат? Десет?

Вероватно, да. Видите, табела изнад је заправо не а ПивотТабле. Направљен је ручно из необрађених података ускладиштених на неком другом месту, и требало је неколико сати да се компајлира. Међутим, то је управо такав резиме могао креира се помоћу ПивотТаблес, у ком случају би то трајало само неколико секунди. Хајде да сазнамо како…

Ако бисмо пронашли оригиналну листу продајних трансакција, могло би изгледати овако:

Можда ћете се изненадити када сазнате да, користећи функцију ПивотТабле у Екцел-у, можемо да направимо месечни сумарни приказ сличан оном у претходних неколико секунди, са само неколико кликова мишем. Ми то можемо - и још много тога!

Како да креирате ПивотТабле

Прво, осигурајте да имате неке необрађене податке у радном листу у Екцелу. Листа финансијских трансакција је типична, али то може бити листа готово свих: контакт подаци о запосленима, колекција ЦД-а или подаци о потрошњи горива за возни парк ваше компаније.

Зато покрећемо Екцел ... и учитавамо такву листу ...

Када отворимо листу у Екцелу, спремни смо да креирамо ПивотТабле.

Кликните на било коју појединачну ћелију унутар листе:

Онда, из Инсерт кликните на картицу Ротациона табела икона:

Тхе Цреате ПивотТабле појављује се оквир, постављајући вам два питања: На које податке треба да се заснива ваша нова ПивотТабле табела и где треба да буде креирана? Будући да смо већ кликнули на ћелију унутар листе (у горњем кораку), за нас је већ одабрана читава листа која окружује ћелију ($ А $ 1: $ Г $ 88 на Плаћања у овом примеру). Имајте на уму да бисмо могли да изаберемо листу у било којој другој области било ког другог радног листа, или чак и неки спољни извор података, као што је табела Аццесс базе података, или чак и табела базе података МС-СКЛ Сервер. Такође морамо да изаберемо да ли желимо да наша нова ПивотТабле буде креирана на а нев радни лист, или на постојећи један. У овом примеру бирамо а нев један:

Нови радни лист је креиран за нас и креирана је празна табела стожера на том радном листу:

Појављује се и други оквир: Тхе Листа поља заокретне табеле.  Ова листа поља ће бити приказана кад год кликнемо на било коју ћелију унутар изведене табеле (горе):

Листа поља у горњем делу поља је заправо збирка наслова колона из оригиналног радног листа необрађених података. Четири празна поља у доњем делу екрана омогућавају нам да изаберемо начин на који бисмо жељели да наша ПивотТабле сажима необрађене податке. За сада, у тим кутијама нема ништа, па је ПивотТабле празна. Све што треба да урадимо је да повучемо поља са горње листе и спустимо их у доње кутије. ПивотТабле таблица се онда аутоматски креира да би одговарала нашим инструкцијама. Ако то погрешно схватимо, потребно је само повући поља назад одакле су дошли и / или повући нев да бисте их заменили.

Тхе Вредности кутија је вероватно најважнија од четири. Поље које се увлачи у овај оквир представља податке које је потребно сумирати на неки начин (збрајањем, усредњавањем, проналажењем максимума, минимума, итд.). То је скоро увек нумерички података. Савршен кандидат за овај оквир у нашим узорцима података је поље / колона "Количина". Хајде да повучемо то поље у Вредности кутија:

Обратите пажњу да је (а) поље "Износ" у листи поља сада означено, а "сума износа" је додата у Вредности означава да је колона сума збројена.

Ако испитамо саму ПивотТабле табелу, ми заиста налазимо суму свих вредности "Износ" из радног листа необрађених података:

Направили смо нашу прву ПивотТабле! Згодно, али не нарочито импресивно. Вероватно је потребно мало више увида у наше податке.

Позивајући се на наше узорке података, треба да идентификујемо један или више наслова колона које бисмо могли да користимо да бисмо поделили овај укупни број. На пример, можемо одлучити да желимо да видимо резиме наших података где имамо а ред заглавља за сваког од различитих продаваца у нашој компанији, и укупно за сваког. Да бисмо то постигли, све што треба да урадимо је да повучемо поље "Продавач" у Ров Лабелс кутија:

Сада, коначно, ствари постају занимљиве! Наша стожерна табела почиње да се обликује ... .

Са неколико кликова смо направили табелу која би требала дуго да се уради ручно.

Шта још можемо учинити? Па, у једном смислу, наша ПивотТабле је комплетна. Направили смо користан сажетак наших изворних података. Важне ствари су већ научене! За остатак чланка, проучићемо неке од начина на које се могу креирати сложеније ПивотТабле табеле и начине на које се те стожерне табеле могу прилагодити.

Прво, можемо створити а два-димензионална табела. Урадимо то користећи “Начин плаћања” као наслов колоне. Једноставно повуците наслов "Начин плаћања" у Ознаке колона кутија:

Који изгледа овако:

Почињем да добијам врло хладан!

Хајде да направимо три-димензионална табела. Како би такав стол могао изгледати? Па дај да видимо…

Превуците колону / наслов "Пакет" у Филтер за извештаје кутија:

Обратите пажњу на крај ... .

То нам омогућава да филтрирамо наш извештај на основу којег се купује пакет. На пример, можемо видети расподелу продавца вс начин плаћања за све пакете, или, с неколико кликова, промијените га да бисте приказали исту квар за пакет "Сунсеекерс":

И тако, ако мислите о томе на прави начин, наша ПивотТабле је сада тродимензионална. Наставимо са прилагођавањем ...

Ако се испостави, рецимо, то само желимо да видимо чек и кредитну картицу трансакције (тј. без готовинских трансакција), онда можемо да поништимо избор ставке „Готовина“ из наслова колона. Кликните на падајућу стрелицу поред Ознаке колона, и обришите “Цасх”:

Да видимо како то изгледа… Као што видите, “Цасх” је нестао.

Форматирање

Ово је очигледно веома моћан систем, али до сада резултати изгледају веома једноставно и досадно. За почетак, бројеви које сумирамо не изгледају као износи у доларима - само стари бројеви. Хајде да то исправимо.

Искушење би могло бити да урадимо оно што смо навикли да радимо у таквим околностима и једноставно изаберемо целу табелу (или цео радни лист) и користимо стандардне тастере за форматирање бројева на траци са алаткама да завршимо обликовање. Проблем са тим приступом је да ако икада промените структуру ПивотТабле-а (што је вероватно 99%), онда ће се ти формати бројева изгубити. Потребан нам је начин који ће их учинити (полу) сталним.

Прво, пронађемо ставку "Сум оф Амоунт" у Вредности и кликните на њега. Појави се мени. Ми бирамо Подешавања вредности поља ... из менија:

Тхе Подешавања вредности поља појављује се оквир.

Кликните Формат бројева и стандард Оквир Формат ћелија појављује се:

Од Категорија листу, изаберите (рецимо) Рачуноводство, и спустите број децималних места на 0. Кликните У реду неколико пута да се вратите на ПивотТабле…

Као што видите, бројеви су исправно форматирани у доларским износима.

Док смо на тему форматирања, форматирајмо целу ПивотТабле табелу. Постоји неколико начина да то урадите. Хајде да употребимо једноставну ...

Кликните Алати ПивотТабле / дизајн таб:

Затим спустите стрелицу у доњем десном углу Стилови стожера да бисте видели огромну колекцију уграђених стилова:

Изаберите било коју ставку која вам се допада и погледајте резултат у својој изведеној табели:

Друге опције

Можемо радити и са датумима. Сада је обично много, много датума у ​​листи трансакција као што је она са којом смо почели. Али, Екцел пружа могућност групирања података по данима, недељама, месецима, годинама итд. Да видимо како се то ради.

Прво, уклонимо колону "Начин плаћања" из Ознаке колона (једноставно повуците натраг до пописа поља) и замијените је ступцем "Дате Боокед":

Као што можете видети, ово чини нашу стожерну таблу одмах бескорисном, дајући нам један ступац за сваки датум када се трансакција десила - веома широку!

Да бисте то поправили, кликните десним тастером миша на било који датум и изаберите Група… из контекстног менија:

Појављује се оквир за групирање. Ми бирамо Месеци и кликните на дугме У реду:

Воила! А много кориснија табела:

(Узгред, ова табела је практично идентична оној приказаној на почетку овог чланка - оригиналном сажетку продаје који је креиран ручно.)

Још једна добра ствар коју треба имати на уму је да можете имати више од једног наслова реда (или наслова колона):

… Која изгледа овако ... .

Можете да урадите сличну ствар са насловима колона (или чак да пријавите филтере).

Поново држимо ствари једноставним, да видимо како да заплетемо просечно вредности, а не сумиране вредности.

Прво, кликните на “Сум оф Амоунт” и одаберите Подешавања вредности поља ... из контекстног менија који се појављује:

Ин тхе Сумирајте поље вредности по у листи Подешавања вредности поља , изаберите Просек:

Док смо овде, хајде да променимо Прилагођено име, од “просечног износа” до нешто мало концизнијег. Откуцајте нешто попут "Авг":

Кликните У реду, и види како изгледа. Обратите пажњу на то да се све вредности мењају од сумираних укупних вредности до просека, а наслов табеле (горња лева ћелија) је промењен у "Авг":

Ако желимо, можемо чак имати суме, просеке и бројеве (тачке = колико је било продаје) све на истој ПивотТабле!

Ево корака да бисте добили нешто слично на месту (почевши од празне табеле заокретне табеле):

1. Превуците „Продавца“ у Ознаке колона
2. Повуците поље „Износ“ у поље Вредности три пута
3. За прво поље "Износ" промените његово прилагођено име у "Тотал" и то је формат броја Рачуноводство (0 децималних места)
4. За друго поље "Износ" промените његово прилагођено име у "Просек", његову функцију Просек и то је формат броја Рачуноводство (0 децималних места)
5. За треће поље "Износ", промените његово име у "Цоунт" и његову функцију на Цоунт
6. Превуците аутоматски креирано фиелд фром Ознаке колона до Ров Лабелс

Ево шта ћемо завршити:

Укупно, просечно и рачуна на исту ПивотТабле!

Закључак

Постоји много, много више функција и опција за ПивотТабле створене од стране Мицрософт Екцел-а - превише их је за списак у чланку као што је овај. Да би се у потпуности покрио потенцијал Пивот табела, потребна је мала књига (или велика веб страница). Храбри и / или гееки читаоци могу прилично лако да истраже ПивотТабле табеле: Једноставно кликните десним тастером миша на скоро све, и видите које опције су вам доступне. Ту су и два језичка траке: Алати / опције за ПивотТабле и Дизајн.  Није битно ако погријешите - лако је избрисати ПивотТабле и почети поново - могућност да стари ДОС корисници Лотус 1-2-3 никада нису имали.

Ако радите у Оффицеу 2007, можда ћете желети да погледате наш чланак о томе како да креирате ПивотТабле у Екцелу 2007.

Укључили смо Екцелову радну свеску коју можете да преузмете да бисте вежбали своје вештине за ПивотТабле. Требало би да ради са свим верзијама програма Екцел од 97 па надаље.

Преузмите нашу радну књигу о пракси