Обобщените таблици са една от най -мощните функции в Excel. Дори и да сте начинаещ, можете да смачкате големи количества данни в полезна информация. Обобщената таблица може да ви помогне да правите отчети за минути. Анализирайте лесно чистите си данни и ако данните не са чисти, това може да ви помогне да изчистите данните си. Не искам да ви отегчавам, така че нека да влезем в него и да проучим.
Как да създадете обобщена таблица
Просто е. Просто изберете вашите данни. Отидете на Вмъкване. Кликнете върху Pivot Table и готово.
Но почакай. Преди да създадете обобщена таблица, уверете се, че всички колони имат заглавие.
Ако някое заглавие на колона е оставено празно, обобщената таблица няма да бъде създадена и ще премине през съобщение за грешка.
Изискване 1: Всички колони трябва да имат заглавие, за да започнете с обобщени таблици в Excel
Трябва да организирате данните си с подходящо заглавие. След като го имате, можете да вмъкнете обобщената таблица.
Поставете обобщената таблица от лентата
За да вмъкнете обобщена таблица от менюто, изпълнете следните стъпки:
1. Изберете диапазона от данни
2. Отидете на раздела за вмъкване
3. Щракнете върху иконата на обобщена таблица.
4. Ще се появи поле за създаване на обобщена таблица.
5. Тук можете да видите диапазона от данни, който сте избрали. Ако смятате, че това не е диапазонът, който искате да изберете, директно преоразмерете диапазона си от тук, вместо да се връщате и да избирате данни отново.
6. След това можете да изберете къде искате вашата обобщена таблица. Препоръчвам да използвате новия работен лист, но можете да използвате и текущия работен лист. Просто дефинирайте местоположението в полето Местоположение.
7. Сега, ако сте приключили с настройките, натиснете бутона OK. Ще имате своята обобщена таблица в нов лист. Просто изберете полетата си за обобщения. Ще видим как създаваме обобщение на данните с помощта на обобщената таблица, но първо нека изясним основите. В този урок за въртяща се таблица на Excel ще научите повече, отколкото очаквате.
Вмъкване на пряк път за обобщена таблица (Alt> N> V)
Това е последователна клавишна комбинация, за да отворите Create Обобщена таблица кутия за опции.
Натиснете бутона Alt и го освободете. Удари н и го пуснете. Удари V и го пуснете. Ще се отвори поле за създаване на обобщена таблица.
Сега просто следвайте горната процедура, за да създадете обобщена таблица в Excel.
Вмъкване на пряк път за обобщена таблица с помощта на Стария пряк път на Excel (Alt> D> P)
Едно нещо, което най -много ми харесва в Microsoft Excel, е, че във всяка нова версия на Excel те въвеждат нови функции, но те не изхвърляйте старите функции (както направи MS с победа 8. Беше жалко). Това позволява на старите потребители да работят нормално върху нови версии, както са работили по -стари версии.
Ако натискате последователно ALT, D и P на клавиатурата Excel ще се отвори за създаване на съветник за обобщена таблица.
Изберете подходящата опция. Избраната опция в горната екранна снимка ще ни накара да създадем обобщена таблица, както създадохме преди.
Натиснете Enter или щракнете върху Next, ако искате да проверите избрания от вас диапазон.
Натиснете Enter отново.
Изберете нов работен лист или където искате вашата осева таблица да натисне Enter. И това е направено.
Създавайте отчети с помощта на обобщени таблици
Сега знаете как да вмъкнете обобщена таблица. Нека се подготвим да правим отчети с помощта на обобщени таблици за минути.
Имаме данни за стационарния ред.
Полетата на колоните са:
Дата на поръчка: Дата на поръчката (очевидно)
Регион: Регионът на реда в страната
Клиент: Име на клиента (какво друго може да бъде)
Вещ: Поръчан артикул
Мерна единица: Брой единици на поръчан артикул
Единична цена: На единица цена
Обща сума: Обща стойност на поръчката (единица*единична цена).
Ще използваме за създаване на отчети с помощта на обобщени таблици
Полета на обобщена таблица: Съдържа списък с имена на колони във вашите данни.
Осеви области: Тези 4 области се използват за показване на вашите данни по маниерен начин.
ФИЛТРИ: Поставете тук полета, от които искате да приложите филтри в отчета си.
КОЛОНИ: Поставете полетата тук, които искате в колоните си в отчета: (По -добре да се покаже, отколкото да се обясни)
РЕДОВЕ: Плъзнете полета, които искате да се показват необработени, както в горното изображение, показах регион в ROWS.
СТОЙНОСТИ: Изберете поле, за да получите брой, сума, средно, процент (и много други) и т.н., които ще искате да видите.
Използвайки горната информация, ние подготвихме този бърз обобщен доклад, който показва от кой регион колко поръчки са направени за всеки артикул.
Сега, когато разбирате вашите данни и обобщени полета (в края на краищата сте умни), нека бързо да отговорим на някои въпроси, свързани с тези данни, като използваме обобщената таблица.
Q1. Колко поръчки има?
Осевата таблица първоначално е празна, както е показано на изображението по -долу.
Трябва да изберете полета (имена на колони) в подходящи области, за да видите обобщението или подробностите за това поле.
Сега, за да отговоря на горния въпрос, ще избера Item (изберете всяка колона, просто се уверете, че няма празна клетка между тях) в полетата за стойност.
Изберете елемент от списъка с полета и го плъзнете в полето Стойност.
Ние имаме своя отговор. Обобщените таблици ми казват, че има общо 43 поръчки. Това е вярно.
Сега, след като избрах Дата на поръчка в областта Стойности, показва 42. Това означава Дата на поръчка има празна клетка от знаем, че общият брой поръчки е 43.
Идентифицирайте нередовни данни с помощта на обобщени таблици и ги почистете.
Обобщената таблица може да ви помогне да намерите неправилна информация в данните.
През повечето време нашите данни се подготвят от оператори за въвеждане на данни или от потребители, които обикновено са нередовни и се нуждаят от известно почистване, за да изготвят точен отчет и анализ.
Винаги трябва да почиствате и подготвяте данните си по маниерен начин, преди да подготвите каквито и да е отчети. Но понякога само след изготвянето на доклада, ние разбираме, че нашите данни имат известни нередности. Ела, ще ти покажа как…
Q2: Кажете брой поръчки от всеки регион
Сега да отговоря на този въпрос:
Изберете Регион и го плъзнете до Редове Площ и Вещ да се Стойности ■ площ.
Ще получим разделени данни за регион-мъдър. Можем да отговорим от кой регион колко поръчки са дошли.
Съгласно обобщената таблица в нашите данни има общо 4 региона. Но изчакайте, забележете това Централна и Централна регион. Знаем, че Centrle трябва да бъде Централна. Има нередност. Трябва да отидем до нашите данни и да извършим почистване на данните.
За да изчистим данните в полето регион, филтрираме неправилното име на региона (Centrle) и го коригираме (Central).
Сега се върнете към своите обобщени данни.
Щракнете с десния бутон навсякъде в обобщената таблица и кликнете върху Опресняване.
Отчетът ви вече е актуализиран.
Сега можете да видите, че всъщност има само 3 региона.
Форматиране на обобщен отчет с категоризирани редове.
Понякога ще ви трябват отчети като горното изображение. Това улеснява разглеждането на вашите данни по структуриран начин. Можете лесно да кажете от кой регион колко артикула са поръчани. Нека да видим как можете да направите това.
Ход Регион и Вещ да се РЕДА ■ площ. Уверете се, че регионът е отгоре, а елементите отдолу, както е показано на изображението.
Плъзнете Вещ за Стойност ■ площ.
В резултат на това ще получите този отчет.
Ще свърши работа. Но понякога вашият шеф иска да докладва в таблична форма без междинни суми. За да направим това, трябва да форматираме нашата обобщена таблица.
Премахнете междинните суми от обобщената таблица
Следвай тези стъпки:
1. Щракнете навсякъде във вашата обобщена таблица.
2. Отидете на Дизайн Раздел.
3. Щракнете върху междинните суми меню.
4. Щракнете върху Не показвайте междинни суми.
Можете да видите, че сега няма междинни суми.
Глоба. Но все още не е в таблична форма. Регионите и елементите са показани в една колона. Покажете ги отделно.
Направете таблица с обобщена таблица
Сега, за да покажете региони и елементи в различни колони, изпълнете следните стъпки:
1. Щракнете навсякъде в обобщената таблица
2. Отидете в раздела Дизайн
3. Щракнете върху Оформление на отчета.
4. Щракнете върху Покажи за опцията Таблична форма. Накрая ще имате този изискан поглед върху вашия доклад.
Сега знаем общия брой поръчки, направени за всеки артикул от всеки регион. Той е показан в графа от колони Елементи.
Моля, променете името на колоната на Поръчки.
Сега изглежда по -добре.
Q3: Колко единици от всеки артикул са поръчани?
За да отговорим на този въпрос, се нуждаем от сума единици. За да направите това, просто преместете полето Units в Values. Той автоматично ще обобщи броя единици за всеки елемент. Ако колона в обобщената таблица съдържа само стойности, по подразбиране обобщената таблица показва сумата от тези стойности. Но тя може да бъде променена от настройка на поле за стойност. Как? Ще ви покажа последното.
Настройки на полето за стойността на обобщената таблица
Q4: Средната цена на всеки артикул?
В нашите примерни данни цената на един артикул е различна за различните поръчки. Например вижте Свързващи вещества.
Искам да знам средната цена на всеки елемент в обобщена таблица. За да разберете това, плъзнете Единична цена до поле за стойност. Той ще покаже Сумата на единичните разходи.
Ние не искаме Сума на единичните разходи, ние искаме Средна стойност на единичната цена. За да направите това…
1. Щракнете с десния бутон навсякъде върху сумата на колоната за единична цена в обобщената таблица
2. Щракнете Настройки на поле за стойност
3. Въз основа на наличните опции, изберете Средно аритметично и натиснете OK.
Накрая ще имате този обобщен доклад:
Изчислени полета на обобщена таблица
Една от най -полезните функции на обобщената таблица са нейните изчислени полета. Изчислените полета са полета, получени чрез някои операции върху наличните колони.
Нека разберем как да вмъкнете изчислени полета в обобщена таблица с един пример:
Въз основа на нашите данни изготвихме този доклад.
Ето, имаме Сума от единици и Крайна цена. Току -що преместих общата колона в полето Стойности и след това я преименувах на Крайна цена. Сега искам да знам средната цена на единица от всеки артикул за всеки регион. А това би било:
Средна цена = Обща цена / Общи единици
Нека вмъкнем поле в обобщената таблица, което показва средната цена за всеки регион на артикула:
Следвайте тези стъпки, за да вмъкнете изчислено поле в обобщената таблица
1. Щракнете навсякъде в обобщената таблица и отидете на раздела Анализ
2. Щракнете върху полета, елементи и набори в изчислителната група.
3. Щракнете върху Изчислени полета.
Ще видите това поле за въвеждане за вашето изчислително поле:
4. В полето за въвеждане на име напишете Средната цена или каквото искате, Excel няма да има нищо против. В полето за въвеждане на формула напишете и натиснете OK.
= Общо / единици
Можете да напишете това ръчно от клавиатурата или можете да щракнете двукратно върху имената на полета, изброени в областта Полета, за да извършите операции.
5. Изчисленото поле е добавено сега към вашата обобщена таблица. Нарича се като сума на средната цена, но не е сума. Excel просто изпълнява функцията по подразбиране за именуване на колоната (като ритуал). Преименувайте тази колона и ограничете показаните десетични цифри.
И там имате изчислено поле. Можете да го направите толкова сложен, колкото искате. За пример направих тази проста средна операция.
Групиране в обобщената таблица
Имате подготвен този обобщен доклад.
Сега искам този доклад да се разделя годишно. Вижте моментната снимка по -долу.
Имаме колона за Дата на поръчката. Преместете полето OrderDate в Редове отгоре.
Не прилича на необходимия доклад. Трябва да имаме групови дати по година.
Сега, за да групирате поле в обобщена таблица на Excel, изпълнете следните стъпки:
1. Щракнете с десния бутон върху полето, което искате да групирате.
2. Щракнете върху Групиране. Ще имате тази кутия с опции за персонализиране. Тъй като това е колона с данни, Excel ни показва съответното групиране. Можете да изберете началната и крайната си дата.
3. Изберете за години и натиснете OK. Направили сте ежегодно групиране в обобщената таблица на Excel.
Фрагменти на обобщена таблица
Слайсерите бяха въведени в Excel 2010 като добавка. В Excel 2013 и 2016 тя е достъпна по подразбиране точно като филтри.
Слайсерите не са нищо друго освен филтри. За разлика от филтрите, Slicers показва всички налични опции точно пред вас. Това прави таблото ви за управление по -интерактивно.
Как да добавите филийки в обобщена таблица, Excel 2016 и 2013
Резачките за обобщена таблица се добавят лесно. Следвай тези стъпки:
1. Щракнете навсякъде в обобщената таблица и отидете на раздела Анализ.
2. Щракнете върху Insert Slicer. Ще имате списък с полета за вашите данни. Изберете колкото искате.
3. За този пример изберете Регион и натиснете OK.
Добавихте Slicer към отчета си. Сега приложете филтъра само с едно щракване.
Вмъкване на времева линия в Excel 2016 и 2013
Това е една от любимите ми функции за обобщени таблици в Excel. Тази нова функционалност работи само с дати. Използвайки това, можете визуално да изберете период от време, за да филтрирате данните си.
За да вмъкнете курсора на хронологията, изпълнете следните стъпки:
Как да добавите времева линия в обобщена таблица, Excel 2016 и 2013
1. Щракнете навсякъде в обобщената таблица и отидете на раздела Анализ.
2. Щракнете върху Вмъкване на времева линия от група филтри. Всички колони, съдържащи времеви стойности в източници на данни, ще бъдат изброени в поле за избор, от което да избирате. Тук имаме само един. Така че, да…
2. Изберете вашите опции и натиснете Enter или щракнете върху OK. Готово е и имате своята времева линия на сводната таблица точно пред вас.
Можете да изберете да го показвате ежедневно, месечно, тримесечно или годишно. Избрах Месечен тук.
В тази статия съм обхванал най -важните и полезни функции на обобщена таблица. Разгледахме новите функции на обобщена таблица в Excel 2016 и 2013 г. Научихме за класическото използване на обобщена таблица, която се изпълняваше в Excel 2007, 2010 и по -стари. Те все още са полезни. Ако не намерихте отговора си, свързан с вашата обобщена таблица тук, попитайте в секцията за коментари.
Има много други функции, които тепърва ще бъдат обяснени. Научаваме разширената функция на обобщена таблица в следващата статия. Дотогава Excel във всичко.
