Как да направя Пивот таблица, така че да превърна левовете в евро?

Днес ще превърнем левовете в евро!

Получихме интересен въпрос във форума на Аула „Как мога да направя Пивот таблица в Ексел така, че да добавя колона в нея и числата, които са ми били в лева да се превърнат в евро, без да се налага допълнителна колона в таблицата с база данни?“.

Големият интерес изразен с дълги дискусии и различни предложения за начини, по които може да се реши този казус, ни провокираха да създадем тема в блога на Aula, с която да споделим решението на проблема.

Въпросът изглежда твърде специфично зададен, НО проблемът е доста глобален.

Принципният въпрос тук е: Как да правим допълнителни колони в Пивот таблиците, които представялват полета, които се изчисляват?

Отговор на принципният въпрос за създаване на изчислителни полета в Пивот таблица на Ексел ще видите в това видео:

Ако видеото не ви е било достатъчно, ето кратко описание как да създаваме изчислителни полета в Пивот таблица:

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

Как процедираме, ако имаме обикновена таблица в Ексел?

В таблицата добавя колона, на която записваме име Сума Евро.

На база на вече съществуващите стойности в левове, в колона със стойности в Евро записваме формулата (=B3/1.95583). Съвсем бързо и лесно с основни познания по Ексел, може да превърнем стойностите от левове в евро. Използваме клетката B3 в която е стойността в левове, след което делим на 1.95583 (валутния курс на еврото спрямо лева).

Готово! Вече имаме стойността от клетка B3 в евро.

Нека да разкопираме формулата в колона C и да форматираме стойностите.

1. Разтегляме формулата в колона C така, че за всяка стойност в левове да поучим еквивалента ѝ в Евро;
2. Кликваме на стрелката на панела Number от Рибън раздела Home, следва да ни се отвори прозореца Format Cells;
3. Там в раздела Number в прозореца Category: избираме  Accounting;
4. В падащото меню за Symbol (символ) избираме Euro €;
5. С ОК потвърждаваме форматирането.

Страхотно направихме това в обикновена таблица.

Не това обаче е задачката!

Задачата ни е без да добавяме допълнителна колона в основната таблица, в която да се пресмятат стойностите, да създадем колона в Пивот таблица в която да става това пресмятане на стойностите от Левове в Евро.

Как може да добавим колона с формула в Пивот таблица?

Имаме обикновена таблица в Ексел, в която имаме база данни и от нея искаме да създадем Пивот таблица, в която последна колона да става превръщане на стойностите записани от левове, в евро.

Вариант 1:

Директно записване на формулата в съседна на пивот таблицата 

Можем да приложим метода както при обикновената таблица.

Може в колоната до Пивот таблицата да напишем формула от вида =B3/1.95583.

Да, определено решаваме проблема! Стойносите ще се пресметнат.

Само че така пресметнати директно в колоната, стойностите не са част от Пивот таблицата.

Някой ще каже: „Голяма работа, нали се смята“

Да, ама НЕ!

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

Когато обаче правим нещо извън таблицата, то определено се разваля – при промяна начина на показване анализа на информацията в Пивот таблицата.

Ако погледнем в палетата на Пивот таблицата имаме обаче само две полета – Име и Сума. Тези две полета ни създават две колони.

Въпроса е: дали не можем да създадем още едно поле в което да става изчислението?!?

Вариант 2:

Създаване на изчислителни полета в Пивот таблицата.

1. Селектираме Пивот таблицата, за да се появят контекстуалните раздели с инструментите за Пивот таблицата (PivotTable Tools);
2. Избираме контекстуалния раздел Analyze;
3. В панела Calculation, избираме бутона Fields, Items, & Sets;
4. От падащото меню избираме Calculated Field… (Изчислителни полета).

Отваря се прозореца Insert Calculated Field (Вмъкни изчислително поле):

1. В полето Name: може да запишем името на изчислителното поле;
2. Кликваме в полето Formula в което ще запишем формулата, с която в това поле ще превръщаме стойностите от левовете в евро. Изписваме равно (=), след което в раздела Fields избираме полето „Сума“, т.к. това е полето което ще участва във формулата ни;
3. Можем да кликнем два пъти бързо върху полето „Сума“ или при вече избрано поле да кликнем на Insert Field, следва полето да се добави във формулата, след което трябва да разделим (/) на 1,95583.
   Така за това поле получихме следната формула = Сума/1,95583. Това означава, че стойностите в полето ще са на база стойностите в полето „Сума“, разделени на валутния курс;
4. Кликваме на Add, за да добавим полето сред другите полета за тази Пивот таблица;
5. С ОК, потвърждаваме направени промени и запазваме новосъздаденото поле „Suma-evro„.

Слагаме отметка на Полето „Suma-Evro“ и го поставяме като сума в раздела Values на пивот таблицата.

Супер! Вече пресмятането става изцяло в Пивот таблицата.

Как да форматираме стойностите в Пивот таблицата?

Сега въпросът е: как да форматираме така получените стойности в Пивот таблицата?

НЕправилният подход:

Селектираме колоната от Пивот таблицата и по познания ни начаин то падащото меню, в панела Number от раздела Home, избираме дадения формат.

Това е неправилният подход.

Защо?

Така не задаваме формат на Пивот таблицата, а формат на клетките намиращи се в този диапазон. Ако в същия диапазон след време направим тази Пивот таблица да показва информация на тези стойности в долари, крони или каквото и да било, то този диапазон ще помни първоначално зададения му формат … левове.

КОЙ тогава е правилният подход?

Отиваме в палетата с полетата за дадената Пивот таблица.

1. Селектираме полето за което искаме да направим настройка на формата му – в нашия случай – „Sum of Suma-evro“;
2. В появилото се меню избираме Value Field Settings… (Настройки на полето).

 

 

Появява ни се прозореца Value Field Settings:

1. В Custom Name може да променим наименованието на полето, така че да изглежда добре когато се появява като заглавие на колоната в Пивот таблицата;
2. Избираме Number Format бутона. Появява ни се прозореца Format Cells (Форматиране на клетките);
3. В полето Category: избираме Accounting;
4. От падащото меню Symbol (Символ)  съответно избираме €;
5. С ОК потвърждаваме настройките направени в прозореца Format Cells;
6. С ОК потвърдаваме настройките, от прозореца Value Field Settings, за полето.

Същите стъпки повтаряме и за другото поле – „Sum of Сума“.

Готово!

Получихме форматиране което вече със сигурност е зададено за самата Пивот таблица.

ВНИМАНИЕ!

Има една дребна особеност при форматирането което направихме по-горе, вътре в самата Пивот таблица.

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

Форматирането се губи!

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

Ако досега Пивот таблиците са били нещо страшно и неразбираемо, то тогава разгледайте нашият курс по Ексел – Тайните на Excel, там ще намерите основните познания на Пивот таблиците.

Ще се убедите, че са един от основните и най-ценните инструменти в Ексел.

Ако не сте използвали Пивот таблиците досега, само с основни познания по тях ще видите колко много работа, грешки и време пестят те.

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

 

Ако тази статия Ви е била полезна и интересна, може да я споделите и със своите приятели във Facebook. 🙂