Повечето хора се сблъскват за първи път с GETPIVOTDATA, когато се опитват да изградят формула извън обобщена таблица, която използва числа в обобщената таблица. Например този процент на отклонение няма да се копира до останалите месеци поради вмъкване на функции на GETPIVOTDATA в Excel.
Excel вмъква GETPIVOTDATA по всяко време, когато използвате мишката или клавишите със стрелки, за да насочите към клетка в обобщената таблица, докато изграждате формула извън обобщената таблица.
Между другото, ако не искате да се появи функцията GETPIVOTDATA, просто напишете формула, като например =D5/C5-1без да използвате мишката или клавишите със стрелки, за да сочите към клетки. Тази формула се копира без никакви проблеми.
Ето набор от данни, който съдържа един номер на план на месец за магазин. Има и действителни продажби на месец на магазин за месеците, които са завършени. Вашата цел е да създадете отчет, който да показва факти за завършените месеци и план за бъдещите месеци.
Изградете обобщена таблица с Store in Rows. Поставете месец и тип в колони. Получавате отчета, показан по-долу, с действителен януари, план за януари и напълно безсмисления план за януари Actual +.
Ако изберете месечна клетка и отидете на Настройки на полето, можете да промените междинните суми на Няма.
Това премахва безполезния план Actual +. Но все пак трябва да се отървете от колоните на плана за януари до април. Няма добър начин да направите това в обобщената таблица.
И така, вашият месечен работен процес става:
- Добавете действителните данни за новия месец към набора от данни.
- Изградете нова обобщена таблица от нулата.
- Копирайте обобщената таблица и поставете като стойности, така че тя вече не е обобщена таблица.
- Изтрийте колоните, които не са ви необходими.
Има по-добър начин. Следващата много компресирана фигура показва нов работен лист на Excel, добавен към работната книга. Всичко това е само прав Excel, без обобщени таблици. Единствената магия е функцията IF в ред 4, която превключва от Actual към Plan въз основа на датата в клетка P1.
Първата клетка, която трябва да бъде попълнена, е актуална за януари за Baybrook. Кликнете в тази клетка и въведете знак за равенство.
С помощта на мишката се придвижете обратно до осевата таблица. Намерете клетката за януари Действително за Baybrook. Кликнете върху тази клетка и натиснете Enter. Както обикновено, Excel създава една от онези досадни GETPIVOTDATA функции, които не могат да бъдат копирани.
Но днес, нека изучим синтаксиса на GETPIVOTDATA.
Първият аргумент по-долу е числовото поле "Продажби". Вторият аргумент е клетката, където се намира обобщената таблица. Останалите двойки аргументи са име на поле и стойност. Виждате ли какво направи автоматично генерираната формула? Тя е кодирана с твърдо „Baybrook“ като името на магазина. Ето защо не можете да копирате тези автоматично генерирани формули GETPIVOTDATA. Те всъщност твърдо кодират имена във формули. Въпреки че не можете да копирате тези формули, можете да ги редактирате. В този случай би било по-добре, ако редактирате формулата, за да сочи към клетка $ D6.
Фигурата по-долу показва формулата, след като я редактирате. Изчезнаха "Baybrook", "Jan" и "Actual". Вместо това посочвате $ D6, E $ 3 и E $ 4.
Копирайте тази формула и след това изберете Специално поставяне, Формули във всички останали цифрови клетки.
Сега ето вашият месечен работен процес:
- Изградете грозна въртяща таблица, която никой никога няма да види.
- Настройте работния лист на отчета.
Всеки месец трябва:
- Поставете нови фактически данни под данните.
- Опреснете грозната ос.
-
Променете клетка P1 на отчетен лист, за да отрази новия месец. Всички номера се актуализират.
Трябва да признаете, че използването на отчет, който извлича числа от обобщена таблица, ви дава най-доброто от двата свята. Можете да форматирате отчета по начини, по които не можете да форматирате обобщена таблица. Празни редове са добре. Можете да имате символи за валута на първия и последния ред, но не и между тях. Получавате двойно подчертаване и под общите суми.
Благодаря на @iTrainerMX, че предложи тази функция.
