Да приемем, че имаме изградена обобщена таблица с резултатите от анализа на продажбите по месеци за различни градове (ако е необходимо, прочетете тази статия, за да разберете как да ги създадете като цяло или да опресните паметта си):
Искаме леко да променим външния му вид, така че да показва данните, от които се нуждаете, по-ясно, а не просто да изхвърля куп числа на екрана. Какво може да се направи за това?
Други изчислителни функции вместо баналната сума
Ако щракнете с десния бутон върху изчисленото поле в областта с данни и изберете командата от контекстното меню Опции на полето (Настройки на полето) или във версия на Excel 2007 – Опции на полето за стойност (Настройки на полето за стойност), тогава ще се отвори много полезен прозорец, чрез който можете да зададете куп интересни настройки:
По-специално, можете лесно да промените функцията за изчисляване на полето на средно, минимум, максимум и т.н. Например, ако променим сумата на количеството в нашата обобщена таблица, тогава ще видим не общия приход, а броя на транзакциите за всеки продукт:
По подразбиране Excel винаги автоматично избира сумиране за числови данни. (сума), а за нечисловите (дори от хиляда клетки с числа да има поне една празна или с текст или с число в текстов формат) – функция за преброяване на стойностите (Броя).
Ако искате да видите в една обобщена таблица наведнъж средната стойност, сумата и количеството, т.е. няколко изчислителни функции за едно и също поле, тогава не се колебайте да хвърлите мишката в областта с данни на полето, от което се нуждаете няколко пъти подред, за да получите нещо подобно:
…и след това задайте различни функции за всяко от полетата, като щракнете върху тях на свой ред с мишката и изберете командата Опции на полето (Настройки на полето)за да получите това, което искате:
Ако в същия прозорец Опции на полето щракнете върху бутона Освен това (Настроики) или отидете на раздела Допълнителни изчисления (в Excel 2007-2010), тогава падащият списък ще стане достъпен Допълнителни изчисления (Показване на данните като):
В този списък, например, можете да изберете опции Процент от сумата на реда (% от реда), Процент от общо по колона (% от колоната) or Дял от общата сума (% от общото)за автоматично изчисляване на проценти за всеки продукт или град. Ето как например ще изглежда нашата обобщена таблица с активирана функция Процент от общо по колона:
Динамика на продажбите
Ако в падащия списък Допълнителни изчисления (Показване на данните като) изберете опция Разграничение (разлика), и в долните прозорци Област (Основно поле) и Element (Основен елемент) изберете месец и обратно (в родната английска версия вместо тази странна дума имаше по-разбираема предишен, тези. предишен):
…тогава получаваме обобщена таблица, която показва разликите в продажбите на всеки следващ месец спрямо предходния, т.е. – динамика на продажбите:
И ако замените Разграничение (разлика) on Дадена разлика (% от разликата) и добавете условно форматиране за да подчертаем отрицателните стойности в червено, получаваме същото, но не в рубли, а като процент:
PS
В Microsoft Excel 2010 всички горепосочени настройки за изчисление могат да бъдат направени още по-лесно – чрез щракване с десния бутон върху произволно поле и избиране на команди от контекстното меню Общо за (Обобщаване на стойностите по):
… и Допълнителни изчисления (Показване на данните като):
Също така във версията на Excel 2010 към този набор бяха добавени няколко нови функции:
- % от общото по родителски ред (колона) – позволява ви да изчислите дела спрямо междинната сума за ред или колона:
В предишните версии можете да изчислите пропорцията само спрямо общата сума.
- % от сумата с натрупване – работи подобно на функцията за кумулативна сума, но показва резултата като дроб, т.е. в проценти. Удобно е да се изчисли например процентът на изпълнение на плана или бюджета:
- Сортиране от най-малкото към най-голямото и обратно – малко странно име на функцията за класиране (RANK), която изчислява поредния номер (позиция) на елемент в общия списък със стойности. Например, с негова помощ е удобно да се класират мениджърите по общите им приходи, определяйки кой на какво място е в общото класиране:
- Какво представляват обобщените таблици и как да ги изградите
- Групиране на числа и дати с желаната стъпка в осеви таблици
- Изграждане на отчет с обобщена таблица върху множество диапазони от изходни данни