Обобщените таблици са една от най -мощните функции в 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. Колко поръчки има?
Осевата таблица първоначално е празна, както е показано на изображението по -долу.
Трябва да изберете полета (имена на колони) в подходящи области, за да видите обобщението или подробностите за това поле.
Сега, за да отговоря на горния въпрос, ще избера Вещ (изберете всяка колона, просто се уверете, че няма празна клетка между тях) в полетата за стойност.
Изберете Вещ от списъка с полета и го плъзнете в полето Стойност.
Ние имаме своя отговор. Обобщените таблици ми казват, че има общо 43 поръчки. Това е вярно.
Професионален съвет: Трябва да проверите данните си дали са верни или не. Ако този номер не съвпада с данни, това означава, че сте избрали някакъв неправилен диапазон или това поле има празни клетки.
Информация: Полето за стойност по подразбиране отчита броя на записите в колона, ако съдържа текст, и сумира, ако полето съдържа само стойности. Можете да промените това в настройките на полето за стойност. Как? Ще се видим по -късно в този урок за обобщена таблица.
Сега, след като избрах Дата на поръчка в областта на ценностите, това показва 42. Това означава Дата на поръчка има празна клетка от знаем, че общият брой поръчки е 43.
Идентифицирайте нередовни данни с помощта на обобщени таблици и ги почистете.
Обобщената таблица може да ви помогне да намерите неправилна информация в данните.
През повечето време нашите данни се подготвят от оператори за въвеждане на данни или от потребители, които обикновено са нередовни и се нуждаят от известно почистване, за да изготвят точен отчет и анализ.
Винаги трябва да почиствате и подготвяте данните си по маниерен начин, преди да подготвите каквито и да е отчети. Но понякога само след изготвянето на доклада, ние разбираме, че нашите данни имат известни нередности. Ела, ще ти покажа как…
Q2: Кажете брой поръчки от всеки регион
Сега да отговоря на този въпрос:
Изберете Регион и го плъзнете до Редове Площ и Вещ да се Стойности ■ площ.
Ще получим разделени данни за регион-мъдър. Можем да отговорим от кой регион колко поръчки са дошли.
Съгласно обобщената таблица в нашите данни има общо 4 региона. Но изчакайте, забележете това Централна и Centrle регион. Знаем, че Centrle трябва да бъде Централна. Има нередност. Трябва да отидем до нашите данни и да извършим почистване на данните.
За да изчистим данните в полето регион, филтрираме неправилното име на региона (Centrle) и го коригираме (Central).
Сега се върнете към своите обобщени данни.
Щракнете с десния бутон навсякъде в обобщената таблица и кликнете върху Опресняване.
Отчетът ви вече е актуализиран.
ИНФОРМАЦИЯ: Без значение какви промени правите в изходните си данни, обобщената таблица ще продължи да работи върху стари данни докато не го опресните. Excel създава обобщен кеш и на него се изпълнява обобщена таблица. Когато се опреснява, старият кеш се променя с нови данни.
Сега можете да видите, че всъщност има само 3 региона.
Форматиране на обобщен отчет с категоризирани редове.
Понякога ще ви трябват отчети като горното изображение. Това улеснява разглеждането на вашите данни по структуриран начин. Можете лесно да кажете от кой регион колко артикула са поръчани. Нека да видим как можете да направите това.
Ход Регион и Вещ да се РЕДА ■ площ. Уверете се, че регионът е отгоре, а елементите отдолу, както е показано на изображението.
Плъзнете Вещ за Стойност ■ площ.
В резултат на това ще получите този отчет.
Ще свърши работа. Но понякога вашият шеф иска да докладва в таблична форма без междинни суми. За да направим това, трябва да форматираме нашата обобщена таблица.
Премахнете междинните суми от обобщената таблица
Следвай тези стъпки:
1. Щракнете навсякъде във вашата обобщена таблица.
2. Отидете на Дизайн Раздел.
3. Щракнете върху менюто за междинни суми.
4. Щракнете върху Не показвайте междинни суми.
Можете да видите, че сега няма междинни суми.
Глоба. Но все още не е в таблична форма. Регионите и елементите са показани в една колона. Покажете ги отделно.
Направете таблица с обобщена таблица
Сега, за да покажете региони и елементи в различни колони, изпълнете следните стъпки:
1. Щракнете навсякъде в обобщената таблица
2. Отидете в раздела Дизайн
3. Щракнете върху Оформление на отчета.
4. Щракнете върху Покажи за табличен формуляр опция. Накрая ще имате този изискан поглед върху вашия доклад.
Сега знаем общия брой поръчки, направени за всеки артикул от всеки регион. Той е показан в графа от артикули колони.
Моля, променете името на колоната на Поръчки.
Сега изглежда по -добре.
Q3: Колко единици от всеки артикул са поръчани?
За да отговорим на този въпрос, се нуждаем от сума единици. За да направите това, просто преместете полето Units в Values. Той автоматично ще обобщи броя единици за всеки елемент. Ако колона в обобщената таблица съдържа само стойности, по подразбиране обобщената таблица показва сумата от тези стойности. Но тя може да бъде променена от настройка на поле за стойност. Как? Ще ви покажа последното.
Настройки на полето за стойността на обобщената таблица
Q4: Средната цена на всеки артикул?
В нашите примерни данни цената на един артикул е различна за различните поръчки. Например вижте Свързващи вещества.
Искам да знам средната цена на всеки елемент в обобщена таблица. За да разберете това, плъзнете Единична цена до поле за стойност. Той ще покаже Сумата на единичните разходи.
Ние не искаме Сума на единичните разходи, ние искаме Средна стойност на единичната цена. За да направите това…
- Щракнете с десния бутон навсякъде върху сумата на колоната за единична цена в обобщената таблица
- Щракнете Настройки на поле за стойност
- Въз основа на наличните опции, изберете Средно аритметично и натиснете OK.
Накрая ще имате този обобщен доклад:
Изчислени полета на обобщена таблица
Една от най -полезните функции на обобщената таблица са нейните изчислени полета. Изчислените полета са полета, получени чрез някои операции върху наличните колони.
Нека разберем как да вмъкнете изчислени полета в обобщена таблица с един пример:
Въз основа на нашите данни изготвихме този доклад.
Ето, имаме Сума от единици и Крайна цена. Току -що преместих общата колона в полето Стойности и след това я преименувах на Крайна цена. Сега искам да знам средната цена на единица от всеки артикул за всеки регион. А това би било:
Средна цена = Обща цена / Общи единици
Нека вмъкнем поле в обобщената таблица, което показва средната цена за всеки регион на артикула:
Следвайте тези стъпки, за да вмъкнете изчислено поле в обобщената таблица
1. Щракнете навсякъде в обобщената таблица и отидете на Анализирайте Tab
2. Щракнете върху Полета, елементи и комплекти в изчислителна група.
3. Щракнете върху Изчислени полета.
Ще видите това поле за въвеждане за вашето изчислително поле:
4. В полето за въвеждане на име напишете Average Разходи или каквото искате, Excel няма да има нищо против. В формула поле за въвеждане, запис и натискане Добре.
= Общо / единици
Можете да напишете това ръчно от клавиатурата или можете да щракнете двукратно върху имената на полета, изброени в областта Полета, за да извършите операции.