Работа с обобщени таблици в Microsoft Excel

PivotTables са една от най-мощните функции на Microsoft Excel. Те позволяват да се анализират и обобщават големи количества данни само с няколко кликвания на мишката. В тази статия изследваме PivotTables, разбираме какви са те и научаваме как да ги създаваме и персонализираме.

Забележка: Тази статия е написана с помощта на Excel 2010 (Beta). Концепцията за PivotTable се е променила малко през годините, но методът на създаване е променен в почти всяка итерация на Excel. Ако използвате версия на Excel, която не е 2010 г., очаквайте различни екрани от тези, които виждате в тази статия.

Малка история

В ранните дни на програмите за електронни таблици Lotus 1-2-3 управляваше мястото за почивка. Нейното господство беше толкова пълно, че хората смятаха, че е загуба на време за Microsoft да се занимава с разработването на собствен софтуер за електронни таблици (Excel), за да се конкурира с Lotus. Flash-напред към 2010 г. и доминирането на Excel на пазара на електронни таблици е по-голямо от това, което някога е било в Lotus, докато броят на потребителите, които все още работят с Lotus 1-2-3, се приближава към нула. Как се случи това? Какво е причинило такова драматично обръщане на съдбата?

Индустриалните анализатори го определят като два фактора: Първо, Lotus реши, че тази фантастична нова GUI платформа, наречена Windows, е минаваща прищявка, която никога няма да излезе. Те отказаха да създадат Windows версия на Lotus 1-2-3 (за няколко години, така или иначе), като прогнозираха, че тяхната DOS версия на софтуера е всичко, което някога ще се нуждае. Microsoft, разбира се, разработи Excel изключително за Windows. Второ, Microsoft разработи функция за Excel, която Lotus не е предоставила в 1-2-3, а именно PivotTables. Функцията PivotTables, изключителна за Excel, се смяташе за толкова зашеметяващо полезна, че хората бяха готови да научат цял нов софтуерен пакет (Excel), вместо да се придържат към програма (1-2-3), която нямаше. Тази характеристика, заедно с неправилната преценка за успеха на Windows, беше смъртоносното звънче за Lotus 1-2-3 и началото на успеха на Microsoft Excel.

Разбиране на обобщените таблици

И така, какво точно е PivotTable?

Казано просто, PivotTable е обобщение на някои данни, създадени, за да позволят лесен анализ на тези данни. Но за разлика от ръчно създаденото обобщение, Excel PivotTables са интерактивни. След като веднъж сте създали такава, можете лесно да я промените, ако не ви дават точни данни за вашите данни, за които се надявате. В няколко клика резюмето може да се „завърти” - да се завърти по такъв начин, че заглавията на колоните да станат заглавия на редовете и обратно. Има още много неща, които също могат да бъдат направени. Вместо да опитваме да опишем всички функции на PivotTables, ние просто ще ги демонстрираме ...

Данните, които анализирате с помощта на обобщена таблица, не могат да бъдат само който и да е данни - трябва да бъде суров данни, които преди не са били обработвани (несъобразени) - обикновено списък от някакъв вид. Пример за това може да бъде списъкът на сделките за продажби в компанията за последните шест месеца.

Прегледайте данните, показани по-долу:

Забележете, че това е така не необработени данни. Всъщност това вече е обобщение. В клетка B3 можем да видим 30 000 долара, което очевидно е общата сума на продажбите на Джеймс Кук за месец януари. Къде са суровите данни? Как стигнахме до цифрата от 30 000 долара? Къде е първоначалният списък на сделките за продажби, от които е генерирана тази цифра? Ясно е, че някъде някой трябва да се е опитал да събере всички продажби през последните шест месеца в обобщението, което виждаме по-горе. Колко дълго смятате, че това е взето? Час? десет?

Най-вероятно да. Виждате ли, електронната таблица по-горе е всъщност не обобщена таблица. Тя е създадена ръчно от необработени данни, съхранени на друго място, и наистина са необходими няколко часа за компилиране. Но точно това е резюмето бих могъл да бъдат създадени с помощта на обобщени таблици, в който случай щеше да отнеме само няколко секунди. Нека да разберем как…

Ако проследим първоначалния списък на продажбите, той може да изглежда по следния начин:

Може да се изненадате да научите, че с помощта на функцията PivotTable на Excel можем да създадем месечно обобщение на продажбите, подобно на това по-горе, само за няколко секунди, с няколко кликвания на мишката. Можем да направим това - и много повече!

Как да създадете обобщена таблица

Първо, уверете се, че имате някои сурови данни в работен лист в Excel. Списък на финансовите транзакции е типичен, но може да е списък с почти всичко: данни за контакт на служителите, колекция от компактдискове или данни за разхода на гориво за автомобилния парк на фирмата ви.

Затова стартираме Excel ... и зареждаме такъв списък ...

След като отворим списъка в Excel, сме готови да започнем създаването на PivotTable.

Кликнете върху всяка една клетка в списъка:

След това, от Insert в раздела, кликнете върху обобщена таблица икона:

Най- Създаване на обобщена таблица Появява се полето, което ви задава два въпроса: Какви данни трябва да се базират на новата PivotTable таблица и къде трябва да бъде създадена? Тъй като вече кликнахме върху клетка в списъка (в горната стъпка), целият списък около нея вече е избран за нас ($ A $ 1: $ G $ 88 на Плащания в този пример). Обърнете внимание, че можем да изберем списък във всеки друг регион на друг работен лист или дори някакъв външен източник на данни, като таблица на база данни на Access или дори таблица на база данни на MS-SQL Server. Също така трябва да изберете дали искаме новата ни PivotTable да бъде създадена на нов работен лист или на съществуващ един. В този пример ще изберем a нов едно:

Новият работен лист е създаден за нас и на него е създаден празен PivotTable:

Появява се и друго поле: Списък с полета на обобщената таблица. Този списък с полета ще се показва всеки път, когато кликнем върху клетка в обобщената таблица (по-горе):

Списъкът от полета в горната част на полето всъщност е колекция от заглавия на колони от оригиналния работен лист сурови данни. Четирите празни кутии в долната част на екрана ни позволяват да изберем начина, по който бихме искали нашата PivotTable да обобщи суровите данни. Досега в тези кутии няма нищо, така че обобщената таблица е празна. Всичко, което трябва да направим, е да плъзнете полета надолу от списъка по-горе и да ги пуснете в долните полета. След това автоматично се създава обобщена таблица, която да отговаря на нашите инструкции. Ако го разберем погрешно, трябва само да плъзнем полетата обратно, откъдето са дошли и / или да ги влачим нов полета надолу, за да ги заместят.

Най- Стойности кутията е може би най-важната от четирите. Полето, което се премества в това поле, представлява данните, които трябва да бъдат обобщени по някакъв начин (чрез сумиране, осредняване, намиране на максимум, минимум и т.н.). Това е почти винаги числен данни. Перфектен кандидат за това поле в нашите примерни данни е полето / колоната „Сума“. Нека плъзнем това поле в Стойности кутия:

Обърнете внимание, че (а) Полето „Сума“ в списъка на полетата е отметнато и „Сумата на сумата“ е добавена към Стойности кутия, показваща, че колоната сума е сумирана.

Ако разгледаме самата обобщена таблица, ние наистина намираме сумата от всички стойности "Сума" от работния лист сурови данни:

Създадохме нашата първа обобщена таблица! Удобен, но не особено впечатляващ. Вероятно се нуждаем от малко повече информация за нашите данни.

Позовавайки се на нашите примерни данни, трябва да идентифицираме една или повече заглавия на колони, които бихме могли да използваме, за да разделим това общо. Например, можем да решим, че бихме искали да видим обобщение на нашите данни, където имаме a ред за всеки от различните търговци в нашата компания и общо за всеки. За да постигнем това, трябва само да издърпаме полето "Продажби" в полето Етикети с редове кутия:

Сега, най-накрая, нещата започват да стават интересни! Нашата обобщена таблица започва да се оформя ... .

С няколко кликвания сме създали таблица, която би отнела много време, за да се направи ръчно.

И така, какво друго можем да направим? Е, в един смисъл нашата PivotTable е завършена. Създадохме полезно резюме на изходните ни данни. Важните неща вече са научени! За останалата част от статията ще разгледаме някои начини за създаване на по-сложни обобщени таблици и начини за персонализиране на тези обобщени таблици..

Първо, можем да създадем a две-таблица с размерите. Нека го направим като използваме “Метод на плащане” като заглавие на колона. Просто плъзнете заглавието „Метод на плащане“ към Етикети на колони кутия:

Което изглежда така:

Започваме да получаваме много готино!

Нека го направим три-таблица с размерите. Как може да изглежда такава маса? Е, да видим…

Плъзнете колоната "Опаковка" / заглавието към Филтър за отчети кутия:

Забележете къде свършва ... .

Това ни позволява да филтрираме нашия доклад, въз основа на който се закупува „ваканционен пакет“. Например, можем да видим разбивка на продавач срещу метод на плащане за всичко или с няколко кликвания го променете, за да покажете същата разбивка за пакета „Sunseekers“:

И така, ако мислите за това по правилния начин, нашата PivotTable сега е триизмерна. Нека продължим да персонализираме ...

Ако се окаже, да речем, че искаме само да видим чек и кредитна карта транзакции (т.е. без касови транзакции), тогава можем да премахнем избора на „Кеш“ от заглавията на колоните. Кликнете върху стрелката с падащо меню до Етикети на колони, и деактивирайте „Кеш“:

Да видим как изглежда… Както виждате, “Кеш” е изчезнал.

форматиране

Това очевидно е много мощна система, но досега резултатите изглеждат много ясни и скучни. За начало цифрите, които сумираме, не изглеждат като долари - просто стари числа. Нека го поправим.

Изкушението може да бъде да правим това, което сме свикнали да правим при такива обстоятелства и просто да изберем цялата таблица (или целия работен лист) и да използваме стандартните бутони за форматиране на номера в лентата с инструменти, за да завършите форматирането. Проблемът с този подход е, че ако някога промените структурата на PivotTable в бъдеще (което е вероятно 99%), тогава тези формати на числа ще бъдат загубени. Нуждаем се от начин, който ще ги направи (полу) постоянни.

Първо, ще намерим записа "Сумата на сумата" в Стойности и кликнете върху него. Появява се меню. Ние избираме Настройки на полето за стойност… от менюто:

Най- Настройки на полето за стойност се появява.

Кликнете върху Формат на номера и стандарт Полето Форматиране на клетки появява се:

От категория списък, изберете (да речем) Счетоводство, и пуснете броя на десетичните знаци на 0. Кликнете Добре няколко пъти, за да се върнете към обобщената таблица…

Както можете да видите, числата са правилно форматирани като суми в долари.

Докато сме на тема форматиране, нека да форматираме цялата PivotTable. Има няколко начина да направите това. Нека използваме прост…

Кликнете върху Инструменти / дизайн на PivotTable раздел:

След това спуснете стрелката в долния десен ъгъл на Стил на обобщената таблица списък, за да видите голяма колекция от вградени стилове:

Изберете който и да е, който ви харесва, и погледнете резултата в обобщената таблица:

Други възможности

Можем да работим и с дати. Сега обикновено има много, много дати в списък с транзакции, като този, с който започнахме. Но Excel предоставя опцията за групиране на елементите от данни по ден, седмица, месец, година и т.н. Да видим как се прави това.

Първо, нека премахнем колоната „Метод на плащане“ от Етикети на колони полето (просто плъзнете го обратно в списъка с полета) и го заменете със графата „Дата на записване“:

Както виждате, това прави нашата PivotTable незабавно безполезна, като ни дава една колона за всяка дата, на която е извършена транзакцията - много широка таблица!

За да поправите това, щракнете с десния бутон на мишката върху произволна дата и изберете Група ... от контекстното меню:

Появява се полето за групиране. Ние избираме Месеци и кликнете върху OK:

Voila! А много по-полезна таблица:

(Между другото, тази таблица е почти идентична с тази, показана в началото на тази статия - оригиналното обобщение на продажбите, създадено ръчно.)

Друго страхотно нещо, което трябва да имате предвид е, че можете да имате повече от един набор от заглавия на редове (или заглавия на колони):

… Което изглежда така… .

Можете да направите подобно нещо със заглавията на колоните (или дори да отчетете филтри).

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

Първо кликнете върху „Сума на сумата“ и изберете Настройки на полето за стойност… от контекстното меню, което се показва:

В Обобщете полето за стойност с в списъка Настройки на полето за стойност , изберете Средно аритметично:

Докато сме тук, нека променим Персонализирано име, от “Средна стойност” до нещо по-кратко. Въведете нещо като „Ср.“:

Кликнете Добре, и вижте как изглежда. Забележете, че всички стойности се променят от сумираните стойности към средните стойности, а заглавието на таблицата (горната лява клетка) е променено на „Ср.“:

Ако искаме, можем дори да имаме суми, средни стойности и преброявания (брой = колко продажби имаше) всички на една и съща обобщена таблица!

Ето стъпките, за да получите нещо подобно на място (като се започне от празна обобщена таблица):

Плъзнете „Продавач“ в полето Етикети на колони
Плъзнете полето "Сума" надолу в полето Стойности кутия три пъти
За първото поле "Сума" променете неговото персонализирано име на "Общо" и е числов формат Счетоводство (0 знака след десетичната запетая)
За второто поле "Сума" променете потребителското му име на "Средно", към неговата функция Средно аритметично и е формат на номера Счетоводство (0 знака след десетичната запетая)
За третото поле "Сума" променете името му на "Граф" и неговата функция на Броя
Плъзнете автоматично създаденото поле от Етикети на колони да се Етикети с редове

Ето какво получаваме с:

Общо, средно и разчита на една и съща обобщена таблица!

заключение

Има много, много повече функции и опции за PivotTables, създадени от Microsoft Excel - твърде много, за да бъдат изброени в подобна статия. За пълното покриване на потенциала на обобщените таблици ще се изисква малка книга (или голям уебсайт). Смелите и / или призрачни читатели могат по-лесно да изследват PivotTables: Просто щракнете с десния бутон на мишката върху почти всичко и вижте какви опции стават достъпни за вас. Има и двата раздела за ленти: Инструменти / Опции на PivotTable и Дизайн. Няма значение дали сте направили грешка - лесно е да изтриете PivotTable и да започнете отново - възможността старите DOS потребители на Lotus 1-2-3 никога да не са.

Ако работите в Office 2007, може да искате да разгледате нашата статия за това как да създадете обобщена таблица в Excel 2007.

Включили сме работна книга на Excel, която можете да изтеглите, за да практикувате уменията си в PivotTable. Той трябва да работи с всички версии на Excel от 97 г. нататък.

Изтеглете нашата работна книга за нашата практика