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

Основната таблица на Excel е един от най-мощните инструменти, които могат да ви помогнат да обобщите и анализирате големи масиви от данни по ефективен начин.

Основната таблица е един от най-мощните и полезни инструменти за обобщаване на данни в Excel, който ви позволява бързо да обобщавате, сортирате, реорганизирате, анализирате, групирате, преброявате големи набори от данни.

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

Този урок ще ви даде инструкции стъпка по стъпка как да създавате и използвате централни таблици в Excel.

Организирайте вашите данни

За да създадете централна таблица, вашите данни трябва да имат таблица или структура на база данни. Така че трябва да организирате данните си в редове и колони. За да конвертирате диапазона си от данни в таблица, изберете всички данни, отидете в раздела „Вмъкване“ и щракнете върху „Таблица“. В диалоговия прозорец Създаване на таблица щракнете върху „OK“, за да преобразувате набора от данни в таблица.

Използването на таблица на Excel като изходен набор от данни за създаване на обобщена таблица прави вашата въртяща таблица динамична. Когато добавяте или премахвате записи в таблицата на Excel, данните в опорната точка ще се актуализират с нея.

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

Вмъкване на въртяща се таблица

Първо изберете всички клетки, които съдържат данни, и отидете в раздела „Вмъкване“ и щракнете върху „Опорна диаграма“. След това изберете опцията „Обобщена диаграма и обобщена таблица“ от падащото меню.

Ще се отвори диалогов прозорец Създаване на обобщена таблица. Excel автоматично ще идентифицира и попълни правилния диапазон в „полето Таблица/Обхват“, в противен случай ще избере правилната таблица или диапазон от клетки. След това посочете целевото местоположение за вашата централна таблица на Excel, може да бъде „Нов работен лист“ или „Съществуващ работен лист“ и щракнете върху „OK“.

Ако изберете „Нов работен лист“, в отделен работен лист ще бъде създаден нов лист с празна централна таблица и въртяща се диаграма.

Изградете своята обобщена таблица

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

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

  • В Полева секция изброява всички полета (колони), които сте добавили към вашата таблица. Тези имена на полета са всички имена на колони от вашата изходна таблица.
  • В Раздел за оформление има 4 области, т.е. филтри, колони, редове и стойности, с които можете да подредите и пренаредите полетата.

Добавяне на полета към обобщената таблица

За да създадете обобщена таблица, плъзнете и пуснете полета от секцията Поле в области на секцията Оформление. Можете също да плъзгате полетата между областите.

Добавяне на редове

Ще започнем с добавяне на полето „Компания“ към секцията „Редове“. Обикновено нечисловите полета се добавят към областта на реда на оформлението. Просто плъзнете и пуснете полето „Comapny“ в областта „Row“.

Всички имена на компании от колоната „Компания“ в изходната таблица ще бъдат добавени като редове в основната таблица и ще бъдат сортирани във възходящ ред, но можете да щракнете върху бутона за падащо меню в клетката Етикети на редове, за да промените реда.

Добавяне на стойности

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

В горната примерна екранна снимка имаме редица компании, но искаме да разберем общите приходи на всяка компания. За да получите това, просто плъзнете и пуснете полето „Приходи“ в полето „Стойност“.

Ако искате да премахнете полета от секцията Области, просто премахнете отметката от квадратчето до полето в секцията Полета.

Сега имаме едномерна таблица на компаниите (етикети на редове) заедно със сумата от приходите.

Добавяне на колона

Двумерна таблица

Редовете и колоните заедно ще създадат двуизмерна таблица и ще запълнят клетките с третото измерение на стойностите. Да предположим, че искате да създадете централна таблица, като изброите имената на компании като редове и използвате колони, за да покажете дати и попълните клетките с общия приход.

Когато добавите полето „Дата“ към областта „Колона“, Excel автоматично добавя „Тремесечно“ и „Години“ към полетата на колоните, за да изчисли и по-добре обобщи данните.

Сега имаме двуизмерна таблица с триизмерни стойности.

Добавете филтри

В случай, че искате да филтрирате данните по „Регион“, можете да плъзнете и пуснете полето „Регион“ в областта за филтриране.

Това добавя падащо меню над вашата обобщена таблица с избраното „Поле за филтриране“. С това можете да филтрирате приходите на компаниите за всяка година по регион.

По подразбиране всички региони са избрани, премахнете отметката от тях и изберете само региона, по който искате да филтрирате данните. Ако искате да филтрирате таблицата по множество записи, поставете отметка в квадратчето до „Избор на няколко елемента“ в долната част на падащото меню. И изберете няколко региона.

Резултатът:

Сортиране

Ако искате да сортирате стойността на таблицата във възходящ или низходящ ред, щракнете с десния бутон върху която и да е клетка в колоната Sum of Revenue, след това разгънете „Сортиране“ и изберете реда.

Резултатът:

Групиране

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

Първо изберете колоните и щракнете с десния бутон върху тях. След това изберете опцията „Група“ от падащото меню.

В прозореца за групиране изберете „Тремесечия“ и „Години“, защото искаме да бъдат организирани във финансови тримесечия на всяка година. След това щракнете върху „OK“.

Сега вашите данни са организирани във финансови тримесечия на всяка година.

Настройки на полето за стойност

По подразбиране обобщената таблица обобщава числовите стойности от функцията Sum. Но можете да промените типа на изчислението, което се използва в областта Стойности.

За да промените функцията за обобщение, щракнете с десния бутон върху произволни данни в таблицата, щракнете върху „Обобщи стойности по“ и изберете вашата опция.

Като алтернатива можете да щракнете върху стрелката надолу до „Сума от ..“ в областта на стойността на секцията с полета и да изберете „Настройки на полето за стойност“.

В „Настройки на полето за стойност“ изберете функцията си, за да обобщите данните. След това щракнете върху „OK“. За нашия пример ние избираме „Брой“, за да преброим броя на печалбите.

Резултатът:

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

За да покажете стойностите като проценти, щракнете с десния бутон навсякъде в таблицата, след това щракнете върху „Показване на стойностите като“ и изберете вашата опция.

Когато изберем „% от общата сума на колоната“, резултатът ще бъде такъв,

Обновете основната таблица

Въпреки че отчетът за обобщена таблица е динамичен, когато прави промени в изходната таблица, Excel не опреснява автоматично данните в обобщената таблица. Той трябва да бъде ръчно „опреснен“, за да се актуализират данните.

Щракнете някъде в централната таблица и отидете на раздела „Анализ“, щракнете върху бутона „Обновяване“ в групата „Данни“. За да обновите текущата централна таблица в работния лист, щракнете върху опцията „Обновяване“. Ако искате да опресните всички основни таблици в работната книга, щракнете върху „Опресняване на всички“.

Като алтернатива можете да щракнете с десния бутон върху таблицата и да изберете опцията „Обновяване“.

Това е. Надяваме се тази статия да ви даде подробен преглед на обобщените таблици на Excel и да ви помогне да създадете такава.