Изчисляване на стойности в обобщена таблица

За да изчислите стойностите в обобщена таблица, можете да използвате някои или всички от следните методи за изчисление:

• Сумиращи функции в полета за стойности    Данните в областта на стойностите обобщават базовите изходни данни в обобщената таблица. Например следният източник на данни:

• Създава следните обобщени таблици и обобщени диаграми. Ако създадете обобщена диаграма от данни в обобщена таблица, стойностите в тази обобщена диаграма отразяват изчисленията в асоцииран отчет с обобщена таблица.

• В обобщената таблица полето в колоната Месец предоставя елементите Март и Април. Полето от реда Регион предоставя елементите Север, Юг, Изток и Запад. Стойността в пресечната точка на колоната април и реда Север ред е общите приходи от продажби от записите в източника на данни, които имат стойностите за МесецАприл и стойностите на Регион за Север.

• В една обобщена диаграма полето Регион може да бъде поле на категория, което показва Север, Юг, Изток и Запад като категории. Полето Месец може да бъде поле за серия, което показва елементите март, Април и Май като серия, включена в легендата. А полето Стойности с име Сума на продажбите може да съдържа маркери за данни, които представят общата сума на приходите във всеки регион за всеки месец. Например един маркер за данни ще представлява, чрез позицията си по вертикалната ос (на стойностите), общата сума на продажбите за Април в региона Север.

• За да изчислите полетата със стойности, имате на разположение следващите обобщаващи функции за всички типове източници на данни, с изключение на източника на данни "Онлайн аналитична обработка (OLAP)".

  Функция	Обобщава
  Sum	Сумата на стойностите. Това е функцията по подразбиране за числови данни.
  Count	Броят на стойностите на данните. Сумиращата функция Count работи по същия начин като функцията COUNTA. Count е функцията по подразбиране за данни, които не са числа.
  Average	Средната стойност на стойностите.
  Max	Най-голямата стойност.
  Min	Най-малката стойност.
  Product	Произведението на стойностите.
  Count Nums	Броят на стойностите на данните, които са числа. Сумиращата функция "Брой числа" работи по същия начин като функцията COUNT.
  StDev	Оценка за стандартното отклонение на генерална съвкупност, където извадката е подмножество от цялата генерална съвкупност.
  StDevp	Стандартното отклонение на генералната съвкупност, където генералната съвкупност представлява всички данни, които трябва да бъдат обобщени.
  Var	Оценка за дисперсията на генерална съвкупност, където извадката е подмножество от цялата генерална съвкупност.
  Varp	Дисперсията на генералната съвкупност, където генералната съвкупност представлява всички данни, които трябва да бъдат обобщени.

• Потребителски изчисления    потребителско изчисление показва стойности на базата на други елементи или клетки в областта за данни. Например можете да покажете стойностите в полето за данни Сума на продажбите като процент от продажбите за Март или като общи суми на елементите в полето Месец.

  Следните функции са налични за потребителски изчисления в полета за стойности.

  Функция	Резултат
  Без пресмятане	Показва стойността, въведена в полето.
  % от общата сума	Показва стойности като процент от общата сума на всички стойности или точки за данни в отчета.
  % от сумата на колоната	Показва всички стойности във всяка колона или серия като процент от общата сума за колоната или серията.
  % на сумата на реда	Показва стойността във всеки ред или категория като процент от общата сума за реда или категорията.
  % от	Показва стойности като процент от стойността на избрания Основен елемент в Основното поле.
  % от сумата на родителския ред	Изчислява стойностите по следния начин: (стойност за елемента) / (стойност за родителския елемент в редовете)
  % от сумата на родителската колона	Изчислява стойностите по следния начин: (стойност за елемента) / (стойност за родителския елемент в колоните)
  % от родителската сума	Изчислява стойностите по следния начин: (стойност за елемента) / (стойност за родителския елемент на избраното Основно поле)
  Разлика спрямо	Показва стойности като разликата от стойността на избрания Основен елемент в Основно поле.
  % разлика спрямо	Показва стойности като разликата в проценти от стойността на избрания Основен елемент в Основно поле.
  Подвижна обща стойност в	Стойността за последователни елементи в Основно поле като подвижна обща стойност.
  % подвижна обща стойност в	Изчислява стойността като процент за последователните елементи в Основно поле, които са показани като подвижна обща стойност.
  Подреждане от най-малкото към най-голямото	Показва ранга на избрани стойности в конкретно поле, означавайки най-малкия елемент в полето като 1, а всяка по-голяма стойност с по-висока рангова стойност.
  Подреждане от най-голямото към най-малкото	Показва ранга на избрани стойности в конкретно поле, означавайки най-големия елемент в полето като 1, а всяка по-малка стойност с по-висока рангова стойност.
  Индекс	Изчислява стойностите по следния начин: ((стойност в клетка) x (цялостна обща сума)) / ((обща сума на реда) x (обща сума на колоната))

• Формули    Ако сумиращите функции и потребителските изчисления не дават желаните резултати, можете да създадете свои собствени формули в изчисляеми полета и изчисляеми елементи. Може например да добавите изчисляем елемент с формулата за комисионите от продажбите, които могат да бъдат различни за всеки регион. Отчетът след това би включил автоматично комисионата в междинните суми и крайни общи суми.

Изчисленията и опциите, които са налични в отчет, зависят от това дали източникът на данни е от OLAP база данни или не-OLAP източник на данни.

• Изчисления въз основа на данни от OLAP източник    За обобщени таблици, които са създадени от OLAP кубове, обобщените стойности са подготвени на OLAP сървъра, преди Excel да покаже резултатите. Не можете да промените как се изчисляват тези подготвени стойности в обобщената таблица. Например не можете да промените сумиращата функция, която се използва за изчисляване на полетата с данни или междинните суми, или да добавяте изчисляеми полета или изчисляеми елементи.

  Освен това, ако OLAP сървърът предоставя изчисляеми полета, известни като изчисляеми членове, ще виждате тези полета в списъка с полета на обобщената таблица. Ще виждате също всички изчисляеми полета и изчисляеми елементи, които са създадени с написани на Visual Basic for Applications (VBA) макроси и съхранени във вашата работна книга, но няма да можете да промените тези полета или елементи. Ако имате нужда от допълнителни типове изчисления, обърнете се към администратора на OLAP база данни.

  При OLAP източник на данни можете да включвате или изключвате стойностите на скритите елементи, когато изчислявате междинни суми и крайни общи суми.

• Изчисления въз основа на не-OLAP източник на данни    В обобщени таблици, базирани на други типове външни данни или данни в работен лист, Excel използва сумиращата функция Sum за изчисляване на полета за стойности, съдържащи числови данни, и сумиращата функция Count за изчисляване на полета за данни, които съдържат текст. Можете да избирате и други обобщаващи функции, като например Average, Max или Min за по-нататъшно анализиране и персонализиране на вашите данни. Можете също да създадете свои собствени формули, които използват елементи от отчета или други данни в работен лист, чрез създаване на изчисляемо поле или изчисляем елемент в поле.

Можете да създавате формули само в отчети, базирани на не-OLAP източник на данни. Не можете да използвате формули в отчети, базирани на OLAP база данни. Когато използвате формули в обобщени таблици, трябва да знаете следните правила на синтаксиса и поведението на формули:

• Елементи на формули в "Обобщена таблица"    Във формули, които създавате за изчисляеми полета и изчисляеми елементи, можете да използвате оператори и изрази, както правите в другите формули в работния лист. Можете да използвате константи и препратки към данни от отчета, но не можете да използвате препратки към клетки или дефинирани имена. Не можете да използвате функции за работен лист, които изискват препратки към клетки или дефинирани имена като аргументи, и не можете да използвате функциите за масиви.

• Имена на полета и елементи    Excel използва имената на поле и елемент за идентифициране на тези елементи на отчет във вашите формули. В следващия пример данните в диапазона C3:C9 използват името на полето Млечни продукти. Изчисляем елемент в полето тип, което оценява продажби за нов продукт въз основа на продажбите на млечни продукти, може да използва формула, като например =Млечни продукти * 115%.

  

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

• Формулите работят върху общи суми, а не за отделни записи    Формулите за изчисляеми полета работят със сумата на базовите данни за произволни полета във формулата. Например формулата за изчисляемо поле =Продажби * 1,2 умножава сумата на продажбите за всеки тип и регион по 1,2; тя умножава всяка отделна продажба по 1,2 и след това сумира умножените количества.

  Формулите за изчисляеми елементи работят върху отделните записи. Ако например формулата за изчисляем елемент =Млечни продукти * 115% умножава всяка отделна продажба на млечни продукти 115%, след което умножените количества се сумират в областта на стойностите.

• Интервали, цифри и символи в имената    В име, включващо повече от едно поле, полетата могат да бъдат в произволен ред. В примера по-горе клетките C6:D6 можеха да бъдат "Април Север" или "Север Април". Използвайте единични кавички около имена, които се състоят от повече от една дума или които включват цифри или символи.

• Общи суми    Формулите не може да препращат към общите суми (като Март Общо, Април Общо и Обща сума в примера).

• Имена на полета в препратки към елементи    Можете да включите името на полето в препратка към елемент. Името на елемента трябва да бъде в квадратни скоби – например Регион [Север]. Използвайте този формат, за да избегнете #NAME? грешките, когато два елемента в две различни полета в отчет имат същото име. Ако например отчет има елемент с име "месо" в полето "тип" и друг елемент с име "месо" в полето "категория", можете да избегнете #NAME? грешки чрез препратка към елементите като тип [месо] и категория [месо].

• Препращане към елементи по положение    Можете да се обърнете към елемент по позицията му в отчета, както елементите са подредени и показани. Тип[1] е Млечни продукти, а Тип[2] е Морски храни. Елемент, към който сте се обърнали по този начин, може да се промени, когато се променят позициите на елементите или различни елементи бъдат показани или скрити. Скритите елементи не се броят в този индекс.

  Можете да използвате относителни позиции в препратките към елементи. Позициите се определят спрямо изчисляемия елемент, който съдържа формулата. Ако текущият регион е Юг, Регион-[1] е Север, но ако текущият регион е Север, то Регион+[1] е Юг. Например изчисляем елемент може да използвате формулата =Регион-[1] * 3%. Ако подадената позиция е преди първия елемент или след последния елемент в полето, формулата води до грешка #REF!. грешка.

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

Например следната обобщена диаграма показва продажбите за всеки продавач по региони:

За да видите как биха изглеждали продажбите, ако се увеличат с 10 процента, можете да създадете изчисляемо поле в свързаната обобщена таблица, използващо формула като =Продажби * 110%.

Резултатът се показва веднага в обобщената диаграма, както е изобразено в следващата таблица:

За да видите отделен маркер на данни за продажбите в региона "Север", минус транспортни разходи от 8 процента, можете да създадете изчисляем елемент в поле "Регион" с формула, като = Север – (Север * 8%).

Получената диаграма ще изглежда ето така:

Обаче даден изчисляем елемент, който е създаден в полето "Продавач", ще се покаже като серия, представена в легендата, и ще се покаже в диаграмата като точка от данни във всяка категория.