содржина
Пивот маси е една од најмоќните алатки во Excel. Тие ви дозволуваат да анализирате и сумирате различни резимеа на големи количини на податоци со само неколку кликнувања на глувчето. Во оваа статија, ќе се запознаеме со стожерните табели, ќе разбереме што се тие, ќе научиме како да ги креираме и прилагодиме.
Оваа статија е напишана со користење на Excel 2010. Концептот на PivotTables не се промени многу со текот на годините, но начинот на кој ги креирате е малку различен во секоја нова верзија на Excel. Ако имате верзија на Excel не 2010, тогаш подгответе се дека сликите од екранот во оваа статија ќе се разликуваат од она што го гледате на вашиот екран.
Малку историја
Во раните денови на софтверот за табеларни пресметки, топката за правило Lotus 1-2-3. Неговата доминација беше толку целосна што напорите на Мајкрософт да развие сопствен софтвер (Excel) како алтернатива на Lotus изгледаа како губење време. Сега брзо напред до 2010 година! Excel доминира во табеларни пресметки повеќе отколку што Lotus кодот некогаш го направил во својата историја, а бројот на луѓе кои сè уште користат Lotus е блиску до нула. Како може да се случи ова? Која беше причината за ваквиот драматичен пресврт на настаните?
Аналитичарите идентификуваат два главни фактори:
- Прво, Lotus одлучи дека оваа новоформирана GUI платформа наречена Windows е само минлива мода што нема да трае долго. Тие одбија да изградат Windows верзија на Lotus 1-2-3 (но само за неколку години), предвидувајќи дека DOS верзијата на нивниот софтвер ќе биде се што ќе им треба на потрошувачите. Мајкрософт природно го разви Excel специјално за Windows.
- Второ, Microsoft воведе алатка во Excel наречена PivotTables која не беше достапна во Lotus 1-2-3. PivotTables, ексклузивни за Excel, се покажаа како толку многу корисни што луѓето имаат тенденција да се придржуваат до новиот софтверски пакет на Excel наместо да продолжат со Lotus 1-2-3, кој ги немаше.
PivotTables, заедно со потценувањето на успехот на Windows воопшто, го одигра маршот на смртта за Lotus 1-2-3 и го воведе успехот на Microsoft Excel.
Што се стожерните табели?
Значи, кој е најдобриот начин да се карактеризира што се PivotTables?
Во едноставни термини, стожерните табели се резиме на некои податоци, создадени за да се олесни анализата на овие податоци. За разлика од рачно креираните збирки, Excel PivotTables се интерактивни. Откако ќе се создадат, можете лесно да ги менувате ако не ја дадат сликата на која се надевавте. Со само неколку кликнувања на глувчето, збирките може да се превртат така што насловите на колоните ќе станат наслови на редови и обратно. Можете да правите многу работи со стожерните табели. Наместо да се обидувате со зборови да ги опишете сите карактеристики на стожерните табели, полесно е тоа да се демонстрира во пракса…
Податоците што ги анализирате со PivotTables не можат да бидат случајни. Треба да бидат необработени податоци, како список од некој вид. На пример, тоа може да биде список на продажби направени од компанијата во последните шест месеци.
Погледнете ги податоците прикажани на сликата подолу:
Забележете дека ова не се необработени податоци, бидејќи тие се веќе сумирани. Во ќелијата Б3 гледаме 30000 долари, што е веројатно вкупниот резултат што Џејмс Кук го направи во јануари. Каде се тогаш оригиналните податоци? Од каде цифрата од 30000 долари? Каде е оригиналната листа на продажби од која е изведен овој месечен вкупен износ? Јасно е дека некој направил одлична работа да ги организира и подреди сите податоци за продажбата за последните шест месеци и да ги претвори во табела со вкупни суми што ја гледаме. Што мислите, колку време траеше? Час? Десет часот?
Факт е дека табелата погоре не е стожерна табела. Беше рачно изработен од необработени податоци складирани на друго место и беа потребни најмалку неколку часа за обработка. Токму таква сумарна табела може да се креира со помош на стожерните табели за само неколку секунди. Ајде да дознаеме како…
Ако се вратиме на оригиналната продажна листа, тоа би изгледало вака:
Можеби ќе се изненадите дека од оваа листа на занаети со помош на стожерни табели и за само неколку секунди, можеме да креираме месечен извештај за продажба во Excel, кој го анализиравме погоре. Да, можеме да го направиме тоа и повеќе!
Како да креирате стожерна табела?
Прво, проверете дали имате некои изворни податоци во лист на Excel. Списокот на финансиски трансакции е најтипичен што се случува. Всушност, тоа би можело да биде список на што било: детали за контакт на вработениот, колекција на ЦД или податоци за потрошувачката на гориво на вашата компанија.
Значи, започнуваме Excel ... и вчитуваме таков список ...
Откако ќе ја отвориме оваа листа во Excel, можеме да започнеме да создаваме стожерна табела.
Изберете која било ќелија од оваа листа:
Потоа на јазичето вметнување (Вметни) изберете команда Стожерна табела (Табела на вртење):
Ќе се појави дијалог-кутија Креирај PivotTable (Креирање стожерна табела) со две прашања за вас:
- Кои податоци да се користат за да се создаде нова стожерна табела?
- Каде да се стави стожерната табела?
Бидејќи во претходниот чекор веќе избравме една од ќелиите на списокот, целата листа ќе биде автоматски избрана за да се создаде стожерна табела. Забележете дека можеме да избереме различен опсег, различна табела, па дури и некој надворешен извор на податоци, како што е табела со база на податоци Access или MS-SQL. Дополнително, треба да избереме каде да ја поставиме новата стожерна табела: на нов лист или на некој од постоечките. Во овој пример, ќе ја избереме опцијата - Нов работен лист (на нов лист):
Excel ќе создаде нов лист и ќе постави празна стожерна табела на неа:
Штом ќе кликнете на која било ќелија во стожерната табела, ќе се појави уште едно поле за дијалог: Список на полиња на PivotTable (Полиња на свртена табела).
Списокот на полиња на врвот на полето за дијалог е листа на сите наслови од оригиналната листа. Четирите празни области на дното на екранот ви дозволуваат да ѝ кажете на PivotTable како сакате да ги сумирате податоците. Се додека овие површини се празни, нема ништо ниту во табелата. Сè што треба да направиме е да ги повлечеме насловите од горната област до празните области подолу. Во исто време, автоматски се генерира стожерна табела, во согласност со нашите упатства. Ако направиме грешка, можеме да ги отстраниме насловите од долната област или да влечеме други за да ги замениме.
Површина Вредности (Значења) е веројатно најважното од четирите. Кој наслов е поставен во оваа област одредува кои податоци ќе се сумираат (збир, просек, максимум, минимум итн.) Тоа се скоро секогаш нумерички вредности. Одличен кандидат за место во оваа област се податоците под насловот износ (Трошоци) на нашата оригинална табела. Повлечете го овој наслов во областа Вредности (Вредности):
Ве молиме имајте предвид дека насловот износ сега е означено со штиклирање, а во областа Вредности (Вредности) се појави запис Збир на износ (Поле за износ Износ), што покажува дека колоната износ сумираше.
Ако ја погледнеме самата стожерна табела, ќе го видиме збирот на сите вредности од колоната износ оригинална табела.
Значи, нашата прва стожерна табела е создадена! Практично, но не особено впечатливо. Веројатно сакаме да добиеме повеќе информации за нашите податоци отколку што имаме моментално.
Ајде да се свртиме кон оригиналните податоци и да се обидеме да идентификуваме една или повеќе колони што може да се користат за да се подели оваа сума. На пример, можеме да ја формираме нашата стожерна табела на таков начин што вкупниот износ на продажбата се пресметува за секој продавач поединечно. Оние. во нашата стожерна табела ќе се додадат редови со името на секој продавач во компанијата и нивната вкупна продажна сума. За да го постигнете овој резултат, само повлечете го насловот продавач (продажен претставник) во регионот Етикети на редови (жици):
Станува поинтересно! Нашата стожерна табела почнува да се обликува…
Видете ги придобивките? Со неколку кликања, создадовме табела на која би требало многу долго да се создаде рачно.
Што друго можеме да направиме? Па, во извесна смисла, нашата стожерна табела е подготвена. Создадовме корисно резиме на оригиналните податоци. Важни информации веќе се добиени! Во остатокот од оваа статија, ќе разгледаме неколку начини за создавање посложени PivotTables, како и да научиме како да ги приспособите.
Поставување PivotTable
Прво, можеме да создадеме дводимензионална стожерна табела. Ајде да го направиме ова користејќи го насловот на колоната Метод за плаќање (Метод за плаќање). Само повлечете го насловот Метод за плаќање до областа Етикети на колони (Колони):
Го добиваме резултатот:
Изгледа многу кул!
Сега да направиме тродимензионална табела. Како би изгледала таква маса? Ајде да видиме…
Повлечете го заглавието пакет (Комплекс) до областа филтри за известување (Филтри):
Забележете каде се наоѓа…
Ова ни дава можност да го филтрираме извештајот врз основа на „За кој комплекс за одмор е платен“. На пример, можеме да видиме дефект по продавачи и по начини на плаќање за сите комплекси, или со неколку кликнувања на глувчето, да го смениме приказот на стожерната табела и да го прикажеме истиот дефект само за оние што го нарачале комплексот Трагачи на сонцето.
Значи, ако го разбирате ова правилно, тогаш нашата стожерна табела може да се нарече тридимензионална. Ајде да продолжиме со поставување…
Ако одеднаш се покаже дека само плаќањето со чек и кредитна картичка (т.е. безготовинско плаќање) треба да се прикаже во стожерната табела, тогаш можеме да го исклучиме приказот на насловот Пари (Пари). За ова, веднаш до Етикети на колони кликнете на стрелката надолу и отштиклирајте го полето во опаѓачкото мени Пари:
Ајде да видиме како изгледа нашата стожерна табела сега. Како што можете да видите, колоната Пари исчезна од неа.
Форматирање на PivotTables во Excel
PivotTables се очигледно многу моќна алатка, но досега резултатите изгледаат малку обични и досадни. На пример, бројките што ги собираме не изгледаат како износи во долари - тие се само бројки. Ајде да го поправиме ова.
Примамливо е да го направите она на што сте навикнати во таква ситуација и едноставно да ја изберете целата табела (или целиот лист) и да ги користите стандардните копчиња за форматирање броеви на лентата со алатки за да го поставите саканиот формат. Проблемот со овој пристап е што ако некогаш ја промените структурата на стожерната табела во иднина (што се случува со 99% шанса), форматирањето ќе се изгуби. Она што ни треба е начин да го направиме (скоро) трајно.
Прво, да го најдеме записот Збир на износ in Вредности (Вредности) и кликнете на неа. Во менито што се појавува, изберете ја ставката Поставки на полето за вредност (Опции на полето за вредност):
Ќе се појави дијалог-кутија Поставки на полето за вредност (Опции на полето за вредност).
Кликнете на копчето Формат на броеви (Формат на број), ќе се отвори дијалог-кутија. Форматирај ќелии (формат на ќелија):
Од листата категорија (Формати на броеви) изберете Сметководство (Финансиски) и поставете го бројот на децимални места на нула. Сега притиснете неколку пати OKда се вратиме на нашата стожерна табела.
Како што можете да видите, бројките се форматирани како износи во долари.
Додека сме во фаза на форматирање, ајде да го поставиме форматот за целата PivotTable. Постојат неколку начини да го направите ова. Го користиме оној што е поедноставен…
Кликнете на Алатки на PivotTable: Дизајн (Работа со PivotTables: Constructor):
Следно, проширете го менито со кликнување на стрелката во долниот десен агол на делот Стилови на стожерна табела (Стилови на PivotTable) за да ја видите обемната колекција на вградени стилови:
Изберете кој било соодветен стил и погледнете го резултатот во вашата стожерна табела:
Други поставки на PivotTable во Excel
Понекогаш треба да ги филтрирате податоците по датуми. На пример, во нашата листа на занаети има многу, многу датуми. Excel обезбедува алатка за групирање на податоци по ден, месец, година и така натаму. Ајде да видиме како е направено.
Прво отстранете го записот. Метод за плаќање од регионот Етикети на колони (Колони). За да го направите ова, повлечете го назад во списокот со наслови и на негово место, преместете го насловот Датум на резервација (датум на резервација):
Како што можете да видите, ова привремено ја направи нашата стожерна табела бескорисна. Excel создаде посебна колона за секој датум на кој беше направена трговија. Како резултат на тоа, добивме многу широка маса!
За да го поправите ова, кликнете со десното копче на кој било датум и изберете од контекстното мени ГРУПА (група):
Ќе се појави дијалог-кутија за групирање. Ние избираме Месеци (месеци) и кликнете OK:
Voila! Оваа табела е многу покорисна:
Патем, оваа табела е речиси идентична со онаа прикажана на почетокот на статијата, каде што вкупните продажби беа составени рачно.
Постои уште една многу важна точка што треба да ја знаете! Можете да креирате не едно, туку неколку нивоа на наслови на редови (или колони):
…и ќе изгледа вака…
Истото може да се направи со наслови на колони (или дури и филтри).
Ајде да се вратиме на оригиналната форма на табелата и да видиме како да се прикажат просеците наместо сумите.
За да започнете, кликнете на Збир на износ и од менито што се појавува изберете Поставки на полето за вредност (Опции на полето за вредност):
Листата Сумирајте го полето за вредност според (Операција) во полето за дијалог Поставки на полето за вредност (Опции на полето за вредност) изберете Просек (Просечно):
Во исто време, додека сме тука, да се промениме Прилагодено име (Прилагодено име) со Просечна сума (Поле за износ Износ) до нешто пократко. Внесете во ова поле нешто слично Ср:
прес OK и види што ќе се случи. Забележете дека сите вредности се сменија од збирки во просеци, а заглавието на табелата (во горната лева ќелија) се промени во Ср:
Доколку сакате, можете веднаш да ги добиете износот, просекот и бројот (продажбите) сместени во една стожерна табела.
Еве чекор по чекор водич за тоа како да го направите ова, почнувајќи со празна стожерна табела:
- Повлечете го заглавието продавач (продажен претставник) во регионот Етикети на колони (Колони).
- Повлечете го насловот три пати износ (Трошоци) до област Вредности (Вредности).
- За првото поле износ сменете го насловот во Вкупно (Износ), а форматот на броеви во ова поле е Сметководство (Финансиски). Бројот на децимални места е нула.
- Второ поле износ името Просечнод, поставете ја операцијата за тоа Просек (Просечно) и форматот на броеви во ова поле исто така се менуваат во Сметководство (Финансиски) со нула децимални места.
- За третото поле износ постави наслов Брои и операција за него - Брои (Квантитет)
- Во Етикети на колони Полето (Колони) се креира автоматски Σ Вредности (Σ Вредности) – повлечете го до областа Етикети на редови (линии)
Еве со што ќе завршиме:
Вкупен износ, просечна вредност и број на продажби – се во една стожерна табела!
Заклучок
Стожерните табели во Microsoft Excel содржат многу функции и поставки. Во толку мала статија, тие не се ни блиску да ги покријат сите. Потребна е мала книга или голема веб-локација за целосно да се опишат сите можности на стожерните табели. Смелите и испитувачки читатели можат да продолжат со истражување на стожерните табели. За да го направите ова, само кликнете со десното копче на речиси секој елемент од стожерната табела и видете кои функции и поставки се отвораат. На лентата ќе најдете две јазичиња: Алатки на PivotTable: Опции (анализа) и дизајн (Конструктор). Не плашете се да направите грешка, секогаш можете да ја избришете PivotTable и да започнете одново. Имате можност која долгогодишните корисници на DOS и Lotus 1-2-3 никогаш ја немале.