Съдържание
Pivot таблици е един от най-мощните инструменти в Excel. Те ви позволяват да анализирате и обобщавате различни обобщения на големи количества данни само с няколко кликвания на мишката. В тази статия ще се запознаем с обобщените таблици, ще разберем какво представляват, ще научим как да ги създаваме и персонализираме.
Тази статия е написана с помощта на Excel 2010. Концепцията на обобщените таблици не се е променила много през годините, но начинът, по който ги създавате, е малко по-различен във всяка нова версия на Excel. Ако имате версия на Excel, различна от 2010, бъдете готови, че екранните снимки в тази статия ще се различават от това, което виждате на екрана.
Малко история
В ранните дни на софтуера за електронни таблици, топката на правилото Lotus 1-2-3. Неговото господство беше толкова пълно, че усилията на Microsoft да разработи собствен софтуер (Excel) като алтернатива на Lotus изглеждаха като загуба на време. Сега бързо напред към 2010! Excel доминира над електронните таблици повече, отколкото кодът на Lotus някога е правил в своята история, а броят на хората, които все още използват Lotus, е близо до нула. Как може да стане това? Каква беше причината за толкова драматичен обрат на събитията?
Анализаторите идентифицират два основни фактора:
- Първо, Lotus реши, че тази нова модна GUI платформа, наречена Windows, е просто мимолетна мода, която няма да продължи дълго. Те отказаха да създадат Windows версия на Lotus 1-2-3 (но само за няколко години), като прогнозираха, че DOS версията на техния софтуер ще бъде всичко, което потребителите някога ще имат нужда. Microsoft естествено разработи Excel специално за Windows.
- Второ, Microsoft представи инструмент в Excel, наречен PivotTables, който не беше наличен в Lotus 1-2-3. PivotTables, изключителни за Excel, се оказаха толкова изключително полезни, че хората бяха склонни да се придържат към новия софтуерен пакет Excel, вместо да продължат с Lotus 1-2-3, който ги нямаше.
PivotTables, заедно с подценяването на успеха на Windows като цяло, изиграха марша на смъртта за Lotus 1-2-3 и поставиха началото на успеха на Microsoft Excel.
Какво представляват обобщените таблици?
И така, какъв е най-добрият начин да се характеризира какво представляват обобщените таблици?
С прости думи, обобщените таблици са обобщения на някои данни, създадени, за да улеснят анализа на тези данни. За разлика от ръчно създадените суми, обобщените таблици на Excel са интерактивни. Веднъж създадени, можете лесно да ги промените, ако не дават картината, на която сте се надявали. Само с няколко щраквания на мишката общите суми могат да бъдат обърнати, така че заглавията на колони да станат заглавия на редове и обратно. Можете да правите много неща с осеви таблици. Вместо да се опитвате да опишете с думи всички характеристики на обобщените таблици, по-лесно е да ги демонстрирате на практика...
Данните, които анализирате с PivotTables, не могат да бъдат произволни. Трябва да са необработени необработени данни, като някакъв списък. Например, това може да бъде списък на продажбите, направени от компанията през последните шест месеца.
Вижте данните, показани на фигурата по-долу:
Имайте предвид, че това не са необработени необработени данни, тъй като вече бяха обобщени. В клетка B3 виждаме $30000 30000, което вероятно е общият резултат, който Джеймс Кук направи през януари. Тогава къде са оригиналните данни? Откъде идва цифрата от $XNUMX XNUMX? Къде е оригиналният списък с продажби, от който е получена тази месечна сума? Ясно е, че някой е свършил чудесна работа с организирането и сортирането на всички данни за продажбите за последните шест месеца и превръщането им в таблица с общи суми, която виждаме. Колко време мислиш, че отне? час? Десет часá?
Факт е, че таблицата по-горе не е обобщена таблица. Той беше ръчно изработен от необработени данни, съхранявани другаде, и обработката му отне поне няколко часа. Точно такава обобщена таблица може да бъде създадена с помощта на обобщени таблици само за няколко секунди. Нека да разберем как...
Ако се върнем към първоначалния списък с продажби, ще изглежда така:
Може да се изненадате, че от този списък със сделки с помощта на обобщени таблици и само за няколко секунди можем да създадем месечен отчет за продажбите в Excel, който анализирахме по-горе. Да, можем да направим това и още!
Как да създадете обобщена таблица?
Първо се уверете, че имате някои изходни данни в лист на Excel. Списъкът с финансови транзакции е най-характерният, който се среща. Всъщност това може да бъде списък с каквото и да било: данни за контакт на служителите, колекция от компактдискове или данни за разхода на гориво на вашата компания.
И така, стартираме Excel ... и зареждаме такъв списък ...
След като отворим този списък в Excel, можем да започнем да създаваме обобщена таблица.
Изберете произволна клетка от този списък:
След това в раздела вмъкване (Вмъкване) изберете команда Осева таблица (Осева таблица):
Ще се появи диалогов прозорец Създаване на обобщена таблица (Създаване на обобщена таблица) с два въпроса за вас:
- Какви данни да използвате за създаване на нова обобщена таблица?
- Къде да поставите обобщената таблица?
Тъй като в предишната стъпка вече сме избрали една от клетките на списъка, целият списък ще бъде автоматично избран за създаване на обобщена таблица. Обърнете внимание, че можем да изберем различен диапазон, различна таблица и дори някакъв външен източник на данни, като таблица на Access или MS-SQL база данни. Освен това трябва да изберем къде да поставим новата обобщена таблица: на нов лист или на някой от съществуващите. В този пример ще изберем опцията – Нов работен лист (на нов лист):
Excel ще създаде нов лист и ще постави върху него празна обобщена таблица:
Веднага след като щракнем върху която и да е клетка в обобщената таблица, ще се появи друг диалогов прозорец: Списък с полета на обобщена таблица (Полета на обобщена таблица).
Списъкът с полета в горната част на диалоговия прозорец е списък на всички заглавия от оригиналния списък. Четирите празни области в долната част на екрана ви позволяват да кажете на обобщената таблица как искате да обобщите данните. Докато тези области са празни, в таблицата също няма нищо. Всичко, което трябва да направим, е да плъзнем заглавията от горната област към празните области отдолу. В същото време автоматично се генерира обобщена таблица, в съответствие с нашите инструкции. Ако направим грешка, можем да премахнем заглавията от долната част или да плъзнем други, за да ги заменим.
Район Ценности (Значения) е може би най-важното от четирите. Кое заглавие е поставено в тази област определя какви данни ще бъдат обобщени (сума, средна, максимална, минимална и т.н.) Това почти винаги са числени стойности. Отличен кандидат за място в тази област са данните под заглавието Количество (Цена) на нашата оригинална маса. Плъзнете това заглавие в областта Ценности (Стойности):
Моля, обърнете внимание, че заглавието Количество сега е маркиран с отметка и в областта Ценности (Стойности) се появи запис Сума на сумата (Поле за сума Сума), което показва, че колоната Количество обобщени.
Ако погледнем самата обобщена таблица, ще видим сбора на всички стойности от колоната Количество оригинална маса.
И така, първата ни обобщена таблица е създадена! Удобно, но не особено впечатляващо. Вероятно искаме да получим повече информация за нашите данни, отколкото имаме в момента.
Нека се обърнем към оригиналните данни и се опитаме да идентифицираме една или повече колони, които могат да се използват за разделяне на тази сума. Например, можем да формираме нашата обобщена таблица по такъв начин, че общата сума на продажбите да се изчислява за всеки продавач поотделно. Тези. редове ще бъдат добавени към нашата обобщена таблица с името на всеки продавач в компанията и общата им сума на продажбите. За да постигнете този резултат, просто плъзнете заглавието Продавач (търговски представител) към обл Етикети на редове (струни):
Става по-интересно! Нашата обобщена таблица започва да се оформя...
Вижте предимствата? С няколко щраквания създадохме таблица, чието ръчно създаване би отнело много време.
Какво друго можем да направим? Е, в известен смисъл нашата обобщена таблица е готова. Създадохме полезно обобщение на оригиналните данни. Важна информация вече е получена! В останалата част от тази статия ще разгледаме някои начини за създаване на по-сложни обобщени таблици, както и ще научим как да ги персонализирате.
Настройка на обобщена таблица
Първо, можем да създадем двуизмерна обобщена таблица. Нека направим това, като използваме заглавието на колоната Начин на плащане (Начин на плащане). Просто плъзнете заглавието Начин на плащане към района Етикети на колони (Колони):
Получаваме резултата:
Изглежда много готино!
Сега нека направим триизмерна маса. Как би изглеждала една такава маса? Да видим…
Плъзнете заглавка Пакет (Комплекс) към района филтри за отчети (Филтри):
Забележете къде е той...
Това ни дава възможност да филтрираме отчета на база „За кой ваканционен комплекс е платено“. Например, можем да видим разбивка по продавачи и по методи на плащане за всички комплекси или с няколко щраквания на мишката да променим изгледа на обобщената таблица и да покажем същата разбивка само за тези, които са поръчали комплекса Търсачи на слънце.
Така че, ако разбирате това правилно, тогава нашата обобщена таблица може да се нарече триизмерна. Да продължим с настройването...
Ако изведнъж се окаже, че в обобщената таблица трябва да се показва само плащане с чек и кредитна карта (т.е. безкасово плащане), тогава можем да изключим показването на заглавието Пари в брой (Пари в брой). За това, до Етикети на колони щракнете върху стрелката надолу и премахнете отметката от квадратчето в падащото меню Пари в брой:
Нека да видим как изглежда нашата обобщена таблица сега. Както можете да видите, колоната Пари в брой изчезна от нея.
Форматиране на обобщени таблици в Excel
PivotTables очевидно са много мощен инструмент, но досега резултатите изглеждат малко обикновени и скучни. Например числата, които събираме, не изглеждат като суми в долари – те са просто числа. Нека поправим това.
Изкушаващо е да направите това, с което сте свикнали в такава ситуация и просто да изберете цялата таблица (или целия лист) и да използвате стандартните бутони за форматиране на числа в лентата с инструменти, за да зададете желания формат. Проблемът с този подход е, че ако някога промените структурата на обобщената таблица в бъдеще (което се случва с 99% вероятност), форматирането ще бъде загубено. Това, от което се нуждаем, е начин да го направим (почти) постоянно.
Първо, нека намерим записа Сума на сумата in Ценности (Стойности) и щракнете върху него. В появилото се меню изберете елемента Настройки на полето за стойност (Опции на полето за стойност):
Ще се появи диалогов прозорец Настройки на полето за стойност (Опции на полето за стойност).
преса Формат на числата (Числов формат), ще се отвори диалогов прозорец. Форматиране на клетки (формат на клетката):
От списъка категория (Числови формати) изберете Счетоводство (Финансови) и задайте броя на десетичните знаци на нула. Сега натиснете няколко пъти OKза да се върнем към нашата обобщена таблица.
Както можете да видите, числата са форматирани като суми в долари.
Докато се занимаваме с форматирането, нека настроим формата за цялата обобщена таблица. Има няколко начина да направите това. Ние използваме този, който е по-прост...
Щракнете върху Инструменти за обобщена таблица: Дизайн (Работа с обобщени таблици: конструктор):
След това разгънете менюто, като щракнете върху стрелката в долния десен ъгъл на секцията Стилове на обобщена таблица (Стилове на обобщена таблица), за да видите обширната колекция от вградени стилове:
Изберете всеки подходящ стил и вижте резултата във вашата обобщена таблица:
Други настройки на обобщена таблица в Excel
Понякога трябва да филтрирате данните по дати. Например в нашия списък със сделки има много, много дати. Excel предоставя инструмент за групиране на данни по ден, месец, година и т.н. Да видим как се прави.
Първо премахнете записа. Начин на плащане от региона Етикети на колони (Колони). За да направите това, плъзнете го обратно в списъка със заглавия и на негово място преместете заглавието Дата на резервация (дата на резервация):
Както можете да видите, това временно направи нашата обобщена таблица безполезна. Excel създаде отделна колона за всяка дата, на която е направена сделка. В резултат на това получихме много широка маса!
За да коригирате това, щракнете с десния бутон върху произволна дата и изберете от контекстното меню ГРУПА (Група):
Ще се появи диалоговият прозорец за групиране. Ние избираме Месеци (Месеци) и щракнете OK:
Ето! Тази таблица е много по-полезна:
Между другото, тази таблица е почти идентична с тази, показана в началото на статията, където общите продажби са събрани ръчно.
Има още един много важен момент, който трябва да знаете! Можете да създадете не едно, а няколко нива на заглавия на редове (или колони):
... и ще изглежда така ...
Същото може да се направи със заглавия на колони (или дори филтри).
Нека се върнем към оригиналната форма на таблицата и да видим как да показваме средни стойности вместо суми.
За да започнете, щракнете върху Сума на сумата и от появилото се меню изберете Настройки на полето за стойност (Опции на полето за стойност):
Списъкът Обобщаване на полето за стойност по (Операция) в диалоговия прозорец Настройки на полето за стойност (Опции на полето за стойност) изберете Средно аритметично (Средно аритметично):
В същото време, докато сме тук, нека се променим CustomName (Персонализирано име) с Средна сума (Поле за сума Сума) към нещо по-кратко. Въведете нещо подобно в това поле Ср:
Натискане OK и вижте какво ще стане. Обърнете внимание, че всички стойности са променени от общи на средни, а заглавката на таблицата (в горната лява клетка) се е променила на Ср:
Ако желаете, можете веднага да получите сумата, средната стойност и броя (продажби), поставени в една обобщена таблица.
Ето ръководство стъпка по стъпка как да направите това, като започнете с празна обобщена таблица:
- Плъзнете заглавка Продавач (търговски представител) към обл Етикети на колони (Колони).
- Плъзнете заглавието три пъти Количество (Цена) към площ Ценности (Стойности).
- За първото поле Количество променете заглавието на Обща сума (Сума), а числовият формат в това поле е Счетоводство (Финансови). Броят на десетичните знаци е нула.
- Второ поле Количество име Средноe, задайте операцията за него Средно аритметично (Средно) и числовият формат в това поле също се променя на Счетоводство (Финансови) с нула знака след десетичната запетая.
- За третото поле Количество задайте заглавие Броя и операция за него – Броя (Количество)
- в Етикети на колони Автоматично създадено поле (Колони). Σ Стойности (Σ Стойности) – плъзнете го в областта Етикети на редове (Редове)
Ето какво ще получим в крайна сметка:
Обща сума, средна стойност и брой продажби – всичко в една обобщена таблица!
Заключение
Обобщените таблици в Microsoft Excel съдържат много функции и настройки. В толкова малка статия те дори не са близо, за да ги обхванат всички. Ще е необходима малка книга или голям уебсайт, за да се опишат напълно всички възможности на обобщените таблици. Смелите и любознателни читатели могат да продължат своето изследване на обобщените таблици. За да направите това, просто щракнете с десния бутон върху почти всеки елемент от обобщената таблица и вижте какви функции и настройки се отварят. На лентата ще намерите два раздела: Инструменти за обобщена таблица: Опции (анализ) и Дизайн (Конструктор). Не се страхувайте да направите грешка, винаги можете да изтриете обобщената таблица и да започнете отначало. Имате възможност, която дългогодишните потребители на DOS и Lotus 1-2-3 никога не са имали.