Ако четете този урок, има голям шанс да сте чували (или дори да използвате) обобщената таблица на Excel. Това е една от най -мощните функции в Excel (без майтап).
Най -хубавото при използването на обобщена таблица е, че дори и да не знаете нищо в Excel, все пак можете да правите доста страхотни неща с нея с много основно разбиране за нея.
Да започваме.
Натисни тук за да изтеглите примерните данни и да ги следвате.
Какво е обобщена таблица и защо трябва да ви е грижа?
Обобщена таблица е инструмент в Microsoft Excel, който ви позволява бързо да обобщите огромни набори от данни (с няколко кликвания).
Дори ако сте абсолютно нов в света на Excel, можете лесно да използвате обобщена таблица. Това е толкова лесно, колкото плъзгане и пускане на заглавки на редове/колони за създаване на отчети.
Да предположим, че имате набор от данни, както е показано по -долу:
Това са данни за продажбите, които се състоят от ~ 1000 реда.
Той има данни за продажбите по региони, тип търговец и клиент.
Сега вашият шеф може да иска да знае няколко неща от тези данни:
- Какви бяха общите продажби в Южния регион през 2016 г.?
- Кои са първите пет търговци на дребно по продажби?
- Как се сравнява представянето на The Home Depot с други търговци на дребно на юг?
Можете да продължите и да използвате функциите на Excel, за да ви даде отговорите на тези въпроси, но какво ще стане, ако изведнъж шефът ви излезе със списък от още пет въпроса.
Ще трябва да се връщате към данните и да създавате нови формули всеки път, когато има промяна.
Това е мястото, където обобщените таблици на Excel са наистина полезни.
В рамките на секунди обобщена таблица ще отговори на всички тези въпроси (както ще научите по -долу).
Но истинската полза е, че тя може да побере вашия изискан шеф, управляван от данни, като отговаря незабавно на въпросите му.
Толкова е просто, че можете да отделите няколко минути и да покажете на шефа си как да го направи сам.
Надяваме се, че сега имате представа защо обобщените таблици са толкова страхотни. Нека да продължим и да създадем обобщена таблица, използвайки набора от данни (показан по -горе).
Вмъкване на обобщена таблица в Excel
Ето стъпките за създаване на обобщена таблица, използвайки данните, показани по -горе:
- Щракнете навсякъде в набора от данни.
- Отидете на Вмъкване -> Таблици -> Осева таблица.
- В диалоговия прозорец Създаване на обобщена таблица опциите по подразбиране работят добре в повечето случаи. Ето няколко неща, които трябва да проверите:
- Таблица/диапазон: Попълва се по подразбиране въз основа на вашия набор от данни. Ако вашите данни нямат празни редове/колони, Excel автоматично ще идентифицира правилния диапазон. Можете да промените това ръчно, ако е необходимо.
- Ако искате да създадете обобщена таблица на определено място, под опцията „Изберете къде искате да бъде поставен отчетът на обобщената таблица“, посочете Местоположението. В противен случай се създава нов работен лист с обобщена таблица.
- Щракнете върху OK.
Веднага щом щракнете върху OK, се създава нов работен лист с обобщена таблица в него.
Докато обобщената таблица е създадена, няма да видите данни в нея. Всичко, което ще видите, е името на обобщената таблица и инструкция от един ред вляво и полетата на обобщена таблица вдясно.
Сега, преди да преминем към анализиране на данни с помощта на тази обобщена таблица, нека разберем кои са гайките и болтовете, които образуват обобщена таблица на Excel.
Ядките и болтовете на обобщена таблица на Excel
За да използвате ефективно обобщена таблица, важно е да знаете компонентите, които създават обобщена таблица.
В този раздел ще научите за:
- Pivot Cache
- Зона на стойностите
- Зона на редовете
- Площ на колоните
- Област на филтрите
Pivot Cache
Веднага щом създадете обобщена таблица с помощта на данните, нещо се случва в задната част. Excel прави моментна снимка на данните и ги съхранява в паметта си. Тази моментна снимка се нарича Pivot Cache.
Когато създавате различни изгледи с помощта на обобщена таблица, Excel не се връща към източника на данни, а използва Pivot Cache за бърз анализ на данните и да ви даде обобщението/резултатите.
Причината, поради която се генерира кеш кеш, е да се оптимизира функционирането на обобщената таблица. Дори когато имате хиляди редове с данни, обобщената таблица е супер бърза при обобщаването на данните. Можете да плъзгате и пускате елементи в редовете/колоните/стойностите/филтрите и той незабавно ще актуализира резултатите.
Забележка: Един недостатък на оборотния кеш е, че увеличава размера на вашата работна книга. Тъй като това е реплика на изходните данни, когато създавате обобщена таблица, копие на тези данни се съхранява в Pivot Cache.
Прочетете още: Какво е Pivot Cache и как да го използвате най -добре.
Зона на стойностите
Областта на стойностите е това, което съдържа изчисленията/стойностите.
Въз основа на набора от данни, показан в началото на урока, ако бързо искате да изчислите общите продажби по региони през всеки месец, можете да получите обобщена таблица, както е показано по -долу (ще видим как да създадете това по -късно в урока) .
Оцветената в оранжево област е зоната на стойностите.
В този пример той има общите продажби за всеки месец за четирите региона.
Зона на редовете
Заглавията вляво от областта Стойности правят областта Редове.
В примера по -долу областта на редовете съдържа регионите (маркирани в червено):
Площ на колоните
Заглавията в горната част на областта Стойности правят областта Колони.
В примера по -долу областта на колоните съдържа месеците (маркирани в червено):
Област на филтрите
Областта на филтрите е незадължителен филтър, който можете да използвате за по -нататъшно пробиване в набора от данни.
Например, ако искате да видите само продажбите за многоредови търговци на дребно, можете да изберете тази опция от падащото меню (подчертано на изображението по -долу) и обобщената таблица ще се актуализира само с данните за многоредови търговци на дребно.
Анализ на данни с помощта на обобщена таблица
Сега, нека се опитаме да отговорим на въпросите, като използваме обобщената таблица, която създадохме.
Натисни тук за да изтеглите примерните данни и да ги следвате.
За да анализирате данни с помощта на обобщена таблица, трябва да решите как искате резюмето на данните да изглежда в крайния резултат. Например може да искате всички региони вляво и общите продажби точно до него. След като имате предвид тази яснота, можете просто да плъзнете и пуснете съответните полета в обобщената таблица.
В секцията Полета с обобщен раздел имате полетата и областите (както е подчертано по -долу):
Полетата се създават въз основа на бекенд данните, използвани за обобщената таблица. Разделът Области е мястото, където поставяте полетата и според това къде отива полето, вашите данни се актуализират в обобщената таблица.
Това е прост механизъм за плъзгане и пускане, където можете просто да плъзнете поле и да го поставите в една от четирите области. Веднага щом направите това, той ще се появи в обобщената таблица в работния лист.
Сега нека се опитаме да отговорим на въпросите, които вашият мениджър е използвал с помощта на тази обобщена таблица.
Q1: Какви бяха общите продажби в Южния регион?
Плъзнете полето Регион в областта Редове и полето Приходи в областта Стойности. Той автоматично ще актуализира обобщената таблица в работния лист.
Обърнете внимание, че веднага щом изпуснете полето Приходи в областта Стойности, то става Сума от приходите. По подразбиране Excel сумира всички стойности за даден регион и показва общата сума. Ако искате, можете да промените това на Брой, Средни или други статистически показатели. В този случай сумата е това, от което се нуждаем.
Отговорът на този въпрос ще бъде 21225800.
Q2 Кои са първите пет търговци на дребно по продажби?
Плъзнете полето Клиент в областта Ред и Приходите в областта със стойности. В случай, че има други полета в раздела за област и искате да го премахнете, просто го изберете и го плъзнете от него.
Ще получите обобщена таблица, както е показано по -долу:
Имайте предвид, че по подразбиране артикулите (в този случай клиентите) са сортирани по азбучен ред.
За да получите първите пет търговци на дребно, можете просто да сортирате този списък и да използвате първите пет имена на клиенти. Да го направя:
- Щракнете с десния бутон върху всяка клетка в областта Стойности.
- Отидете на Сортиране -> Сортиране от най -голямо до най -малко.
Това ще ви даде сортиран списък въз основа на общите продажби.
Въпрос 3: Как се сравнява представянето на Home Depot с други търговци на дребно на юг?
Можете да направите много анализ на този въпрос, но тук нека просто се опитаме да сравним продажбите.
Плъзнете полето за регион в областта Редове. Сега плъзнете полето Клиент в областта Редове под полето Регион. Когато направите това, Excel ще разбере, че искате първо да категоризирате данните си по региони, а след това по клиенти в регионите. Ще имате нещо, както е показано по -долу:
Сега плъзнете полето Приходи в областта Стойности и ще имате продажбите за всеки клиент (както и за целия регион).
Можете да сортирате търговците на дребно въз основа на данните за продажбите, като следвате стъпките по -долу:
- Щракнете с десния бутон върху клетка, която има продажната стойност за всеки търговец на дребно.
- Отидете на Сортиране -> Сортиране от най -голямо до най -малко.
Това незабавно би сортирало всички търговци на дребно по стойността на продажбите.
Сега можете бързо да прегледате Южния регион и да установите, че продажбите на Home Depot са били 3004600 и се справят по -добре от четирима търговци на дребно в Южния регион.
Сега има повече от един начин да одра котката. Можете също така да поставите региона в областта на филтъра и след това да изберете само южния регион.
Натисни тук за изтегляне на примерните данни.
Надявам се този урок да ви даде основен преглед на обобщените таблици на Excel и да ви помогне да започнете с него.
Ето още някои уроци за обобщена таблица, които може да ви харесат:
- Подготовка на изходни данни за обобщена таблица.
- Как да приложите условно форматиране в обобщена таблица в Excel.
- Как да групирате дати в обобщени таблици в Excel.
- Как да групирате числа в обобщена таблица в Excel.
- Как да филтрирате данни в обобщена таблица в Excel.
- Използване на срезове в обобщена таблица на Excel.
- Как да замените празните клетки с нули в обобщените таблици на Excel.
- Как да добавяте и използвате изчислени полета на обобщена таблица на Excel.
- Как да опресните обобщена таблица в Excel.
