Ключът към Pivot Table в MS Excel

Много хора изпитват респект, когато се заговори за PIVOT TABLE в MS Excel.

Често описван като труден, неразбираем, объркан, този инструмент се избягва от голям брой потребители.

Pivot Table е инструмент за динамично реорганизиране на данни. Съчетава в себе си множество функционалности, като сортиране и филтриране на данни, групиране и извеждане на междинни обобщения по посочен критерий, поддържа връзки към оригиналния източник на данни, като позволява актуализация на промените във всеки един момент.

Възпроизвежда данните като процент от ред, колона или тотал, поддържа възможност за извеждане на разлики между полетата при заявен критерий.

Изучаването на Pivot Table изисква предварителни знания за няколко важни действия и инструменти в MS Excel , които ще внесат яснота и разбиране в цялата функционалност на т.н. Динамични или Осеви таблици.

Какво трябва да знаем преди да посегнем към обработка на данните с Pivot Table?

01. Свързване на данни в Excel (Links)

Ежедневно използваме или свързване на данни при работа с формули, функции. В Excel съществуват три категории връзки: връзки към клетки от същия работен лист (Worksheet), връзки към клетки от съседен работен лист, връзки към клетки от друг файл.

  • Връзки към клетки от същия работен лист
    • Записът от такъв тип е стандартен, въвежда се знак за равенство и се посочва адресът на клетката (=А1)
  • Връзки към клетки от съседен работен лист.
    • Адресът има две измерения и съдържа име на работния лист и адреса на посочената клетка.Този тип е известен още , като 2D Address. (=WorksheetName!А1)
  • Връзки към клетки от друг файл.
    • Наречен 3D, адресът съдържа названието на файла, името на работния лист и абсолютния адрес на посочената клетка. (=[FileName.xlsx]WorkSheetName!$A$1)

02. Консолидиране на данни

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

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

В MS Excel съществува инструмент, наречен Consolidate ( Data Tab / Data Tools Group / Consolidate), с който се ускорява и улеснява избора на данни, а обобщаването на информация се автoматизира. Чрез избор на функция и посочване на цели масиви с данни, които да бъдат обобщени едновременно, инструментът осигурява удобен, бърз и сигурен метод за консолидация на данни.

03. Сортиране на данни

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

Инструментът SORT (DATA Tab / SORT & FILTER Group / SORT Button) e популярен и много използван в Excel. Позволява подреждане на данни както по един , така и по няколко критерия едновременно.

04. Филтриране на данни

Достигането до точно определени данни, текст, числа или дати , често се извършва с вградения автоматичен филтър в програмата. Data Tab / Sort & Filter Group / Filter Button. Филтрирането по един или множество критерии позволява да ограничим до минимум излишната информация и да извлечем търсеното, за кратко време.

05. Групиране на данни

При големи обеми от данни, удобство е организирането им в групи (Data Tab/Outline Group/ Group Button). Този метод позволява да комбинираме информацията в по-големи структури и да работим предимно с основните или главните теми в таблицата. Всяка група може да съдържа детайлна информация, представена чрез подтеми, които могат да се визуализират или скриват при необходимост. Групите създават компактен изглед на таблицата.

06. Извеждане на междинни суми или обобщения със Subtotal

Междинните суми са едно голямо удобство при обобщаване на данни и по-точно при изготвяне на представителна извадка за определен тип данни в таблицата. Активирането му става от Data Tab / Outline Group / Subtotal

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

07. Функции SUM, AVERAGE, MAX, MIN, COUNT, PRODUCT

В Pivot Table има 11 вградени функции, с които могат да се обработват многобройните редове и колони с данни. Най-често използвани са широко популярните SUM (функция за събиране), Average (извежда средно-аритметична стойност), Count (преброява клетките с числови данни) , MAX (извежда най-голяма стойност), MIN( извежда най-малка стойност) , Product (функция за умножение).

Всички представени действия – свързване на данни, консолидиране на данни, сортиране и филтриране на данни, групиране на редове и колони, междинни суми, основни функции са включени в работата на PIVOT TABLE.

Предварителното им усвояване, разбиране, осмисляне и практическо приложение е ключът към успешна и ефективна работа с този изключителен инструмент.

Автор: Събина Василева