Обобщените таблици са невероятен вграден инструмент за отчитане в Excel. Въпреки че обикновено се използва за обобщаване на данни с общи стойности, можете също да ги използвате за изчисляване на процента на промяна между стойностите. Още по-добре: Лесно е да се направи.
Можете да използвате тази техника, за да правите всякакви неща – почти навсякъде, където искате да видите как една стойност се сравнява с друга. В тази статия ще използваме простия пример за изчисляване и показване на процента, с който общата стойност на продажбите се променя месец по месец.
Ето листа, който ще използваме.
Това е доста типичен пример за лист за продажби, който показва датата на поръчката, името на клиента, търговския представител, общата стойност на продажбите и няколко други неща.
За да направим всичко това, първо ще форматираме нашия диапазон от стойности като таблица в Excel и след това ще създадем обобщена таблица, за да направим и покажем нашите изчисления за процентна промяна.
Съдържание
Форматиране на диапазона като таблица
Ако вашият диапазон от данни все още не е форматиран като таблица, бихме ви насърчили да го направите. Данните, съхранявани в таблици, имат множество предимства пред данните в диапазони от клетки на работен лист, особено при използване на обобщени таблици (прочетете повече за ползите от използването на таблици).
За да форматирате диапазон като таблица, изберете диапазона от клетки и щракнете върху Вмъкване > Таблица.
Проверете дали диапазонът е правилен, дали имате заглавки в първия ред на този диапазон и след това щракнете върху „OK“.
Диапазонът вече е форматиран като таблица. Именуването на таблицата ще улесни позоваването й в бъдеще при създаване на обобщени таблици, диаграми и формули.
Щракнете върху раздела „Дизайн“ под Инструменти за таблица и въведете име в полето, предоставено в началото на лентата. Тази таблица е наречена „Продажби“.
Можете също да промените стила на таблицата тук, ако желаете.
Създайте обобщена таблица за показване на процентна промяна
Сега нека да продължим със създаването на обобщената таблица. От новата таблица щракнете върху Вмъкване > обобщена таблица.
Появява се прозорецът Създаване на обобщена таблица. Той автоматично ще открие вашата маса. Но можете да изберете таблицата или диапазона, които искате да използвате за обобщената таблица в този момент.
Групирайте датите в месеци
След това ще плъзнем полето за дата, по което искаме да групираме, в областта на редовете на обобщената таблица. В този пример полето се казва Дата на поръчка.
От Excel 2016 нататък стойностите на датите се групират автоматично в години, тримесечия и месеци.
Ако вашата версия на Excel не прави това или просто искате да промените групирането, щракнете с десния бутон върху клетка, съдържаща стойност за дата, и след това изберете командата „Група“.
Изберете групите, които искате да използвате. В този пример са избрани само години и месеци.
Годината и месецът вече са полета, които можем да използваме за анализ. Месеците все още се именуват като Дата на поръчка.
Добавете полетата със стойности към обобщената таблица
Преместете полето Година от Редове и в областта Филтър. Това позволява на потребителя да филтрира обобщената таблица за една година, вместо да претрупва обобщената таблица с твърде много информация.
Плъзнете полето, съдържащо стойностите (Обща стойност на продажбите в този пример), които искате да изчислите, и представите промяната в областта Стойности два пъти.
Може още да не изглежда много. Но това ще се промени много скоро.
И двете полета със стойности ще са по подразбиране като сума и в момента нямат форматиране.
Стойностите в първата колона, които бихме искали да запазим като общи. Те обаче изискват форматиране.
Щракнете с десния бутон върху число в първата колона и изберете „Форматиране на числа“ от контекстното меню.
Изберете формата „Счетоводство“ с 0 знака след десетичната запетая от диалоговия прозорец Форматиране на клетки.
Обобщената таблица сега изглежда така:
Създайте колона за процентна промяна
Щракнете с десния бутон върху стойност във втората колона, посочете „Показване на стойности“ и след това щракнете върху опцията „% разлика от“.
Изберете „(Назад)“ като основен елемент. Това означава, че стойността на текущия месец винаги се сравнява със стойността от предходните месеци (поле Дата на поръчката).
Обобщената таблица вече показва както стойностите, така и процентната промяна.
Щракнете върху клетката, съдържаща етикети на редове, и въведете „Месец“ като заглавка на тази колона. След това щракнете върху заглавната клетка за втората колона със стойности и въведете „Разлика“.
Добавете няколко стрелки за отклонение
За да изчистим наистина тази обобщена таблица, бихме искали да визуализираме по-добре процентната промяна, като добавим няколко зелени и червени стрелки.
Те ще ни предоставят прекрасен начин да видим дали промяната е била положителна или отрицателна.
Щракнете върху която и да е от стойностите във втората колона и след това щракнете върху Начало > Условно форматиране > Ново правило. В прозореца Редактиране на правилото за форматиране, който се отваря, направете следните стъпки:
Изберете опцията „Всички клетки, показващи стойности „Разлика“ за Дата на поръчка“.
Изберете „Набори на икони“ от списъка със стил на форматиране.
Изберете червените, кехлибарите и зелените триъгълници от списъка със стил на икони.
В колоната Тип променете опцията за списък, за да кажете „Число“ вместо Процент. Това ще промени колоната Стойност на 0. Точно това, което искаме.
Щракнете върху „OK“ и условното форматиране се прилага към обобщената таблица.
Обобщените таблици са невероятен инструмент и един от най-простите начини за показване на процентната промяна във времето за стойностите.