3 хитрини за анализ на данни с пивот таблици

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

Да започнем от таблицата със статистиката за музеите в България по области, с която ви запознах в статията за Ексел таблиците. Самите данни са публикувани на сайта на НСИ.

Тази купчина от числа нищо не ни говори на пръв поглед. Необходимо е да я подредим и обобщим по някакъв начин, за да можем да направим анализ. Много са възможностите, но тук ще си говорим за пивот таблици. Затова:  Insert > Pivot Table

И след това просто даваме ОК

Ако досега не сте правили пивот таблица – честито. Току-що сътворихте първата си такава и тя изглежда така:

Повече за това как се създават пивот таблици и за какво служат – на сайта на Майкрософт.

Или в тази много хубава статия: https://expert-bg.org/article-pivot-table-ms-excel/

Но аз ви обещах три хитрини

1. Калкулационно поле

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

Например – имаме брой посещения, имаме и брой музеи. Искаме да изчислим среден брой посещения на един музей. Формулата е проста – делим посещенията на музеите. Но няма нужда да го правим в таблицата с данните. Вместо това отиваме на PivotTable Tools > Options > Formulas > Calculated Field

Появява се менюто Insert Calculated Field.

Тук в полето Name записваме името на полето, което ще създадем. Аз съм си го нарекъл „Средно посещения на музей”

Полето под него се нарича Formula, което подсказва, че там трябва да запишем формулата. Обърнете внимание на полето Fields. Там са колоните в пивот таблицата. Избираме ги като кликнем два пъти върху тях. А за писането на формулите използваме стандартните математически оператори + (събиране), – (изваждане), / (делене) и * (умножение), както и скоби.

Ето как изглежда нашата формула:

А когато дадем ОК, нашето калкулационно поле се появява автоматично в пивот таблицата. От там нататък можем да го използваме съвсем пълноценно като всяко друго поле.

Това беше първият трик – създаване на калкулационно поле. С негова помощ можем да превръщаме суми от лева в евро, да изчисляваме % отстъпка от някаква сума, да разделим числата на 100 или 1000 и т.н. БЕЗ да създаваме допълнителни колони в нашата основна таблица. По този начин не увеличаваме и обема на файла си. Калкулационното поле се съхранява само в кеша на пивот таблицата. А това заема далеч по-малко място от формула, разнесена на няколко (десетки) хиляди реда.

2. Сортиране за по-лесен анализ

Нека добавим в нашата таблица данни за броя на музеите.

Както забелязвате, по подразбиране Ексел сортира данните по азбучен ред. Но това не е много удобно за анализ, защото ние се интересуваме от тенденциите в числата. Нека да ги сортираме по по-удобен начин. За целта кликаме с десния бутон някъде в колоната с областните градове и избираме Sort > More Sort Options

В следващото меню ще избера Descending, след това ще кликна върху стрелката на полето отдолу и ще избера Sum of Средно посещения на музей.

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

Резултатът е леко изненадващ – Видин е на първо място. Вижте колко лесно и елегантно установихме този факт. Буквално с няколко кликания на мишката.

Има един проблем обаче. Тези данни са общо за двете години, за които имаме данни. Затова нека да добавим в колоните и полето „Година”. Сега вече имаме база за сравнение във времето.

Което ни води до третия трик

3. Процентни изменения

Чрез пивот таблиците можем лесно и бързо да обогатим нашия анализ като изчислим процентното изменение на даден показател. Например на средния брой посещения. За целта първо добавяме „Среден брой посещения на музей” към полето Values на пивот таблицата. Точно така – едно поле можем да го добавим повече от един път. Ексел услужливо добавя една цифра 2 зад името на полето.

Сега кликаме с десния бутон върху това второ поле и избираме Value Field Settings. След това избираме Show values as

 

Кликаме върху стрелката в дясно на полето Show values as (обикновено там пише Normal). От падащото меню  избираме %Of

След това в Base field избираме „Година” и „2011”.

На български казано искаме от Ексел да ни показва това поле не като бройки, а като процент от бройките през 2011 г. А когато цъкнем ОК, получаваме една голяма таблица, в която най-отдясно е изменението в % на средния брой посещения през 2016 спрямо 2011.

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

Ето как само с няколко кликания по пивот таблицата можем бързо и лесно да спретнем един аналитичен отчет от нашите данни. Спестеното време за обработка можем да вложим в по-важното – същинския анализ.

Веднага се очертават въпросите. Защо Видин „изпъква” толкова много над другите? Как е възможно почти двумилионната ни столица да е едва на шесто място по среден брой посещения на музеите? Защо броят на музеите в Хасково е с 40% повече, а средната посещаемост спада с 18% спрямо 2011? Какво се случва в Кърджали – там посещаемостта е едва една трета от тази преди 5 години.

А сега си представете, че става дума не за музеите в областните градове, а за продажбите по региони на вашата фирма. Досетихте ли се вече как тези три хитринки ще ви помогнат да блеснете пред шефа?

Публикувано в Хитрини с етикети , , . Постоянна връзка.