Більшість людей вперше стикаються з GETPIVOTDATA, коли намагаються побудувати формулу поза зведеною таблицею, яка використовує числа у зведеній таблиці. Наприклад, цей відсоток відхилення не буде копіюватися на інші місяці через вставку Excel функцій GETPIVOTDATA.
Excel вставляє GETPIVOTDATA кожного разу, коли ви використовуєте мишу або клавіші зі стрілками для вказівки на клітинку всередині зведеної таблиці під час побудови формули поза зведеною таблицею.
До речі, якщо ви не хочете, щоб відображалася функція GETPIVOTDATA, просто введіть формулу, наприклад, =D5/C5-1не використовуючи клавіші миші або стрілки для вказівки на клітинки. Ця формула копіюється без проблем.
Ось набір даних, який містить один номер плану на місяць для магазину. Існують також фактичні продажі на місяць за магазин за місяці, які завершились. Ваша мета - скласти звіт, який відображатиме фактичні дані за завершені місяці та план на наступні місяці.
Створіть зведену таблицю за допомогою пункту Зберігати у рядки. Помістіть місяць і тип у стовпці. Ви отримуєте звіт, показаний нижче, з фактичним січневим планом, січневим планом та абсолютно безглуздим січневим фактичним + планом.
Якщо вибрати клітинку місяця та перейти до Налаштування поля, ви можете змінити проміжні підсумки на Ні.
Це видаляє марний план Фактичний +. Але вам все одно доведеться позбутися колонок плану на січень-квітень. Немає хорошого способу зробити це всередині зведеної таблиці.
Отже, ваш щомісячний робочий процес стає:
- Додайте фактичні дані для нового місяця до набору даних.
- Створіть нову зведену таблицю з нуля.
- Скопіюйте зведену таблицю та вставте як значення, щоб вона більше не була сводною таблицею.
- Видаліть непотрібні стовпці.
Є кращий спосіб піти. На наступному дуже стислому малюнку показано новий аркуш Excel, доданий до книги. Це все просто Excel, без зведених таблиць. Єдиною магією є функція IF у рядку 4, яка перемикається з фактичного на план, виходячи з дати в комірці P1.
Найперша комірка, яку потрібно заповнити, - Січень фактичний для Бейбрука. Клацніть у цій комірці та введіть знак рівності.
За допомогою миші поверніться до зведеної таблиці. Знайдіть клітинку на січень фактично для Бейбрука. Клацніть на цю клітинку та натисніть Enter. Як завжди, Excel створює одну з тих надокучливих функцій GETPIVOTDATA, які неможливо скопіювати.
Але сьогодні давайте вивчимо синтаксис GETPIVOTDATA.
Перший аргумент нижче - числове поле "Продажі". Другий аргумент - це комірка, в якій знаходиться зведена таблиця. Решта пар аргументів - це ім’я поля та значення. Чи бачите ви, що зробила автоматично сформована формула? У назві магазину це кодовано "Baybrook". Ось чому ви не можете копіювати ці автоматично згенеровані формули GETPIVOTDATA. Вони насправді твердо кодують імена у формули. Хоча ви не можете скопіювати ці формули, ви можете редагувати їх. У цьому випадку було б краще, якби ви відредагували формулу, щоб вказати на клітинку $ D6.
На малюнку нижче показано формулу після її редагування. Зникли "Baybrook", "Jan" і "Actual". Натомість ви вказуєте на $ D6, E $ 3 та E $ 4.
Скопіюйте цю формулу, а потім виберіть «Спеціальна вставка», «Формули» у всіх інших числових клітинках.
Ось ваш щомісячний робочий процес:
- Створіть потворну зведену таблицю, яку ніхто ніколи не побачить.
- Налаштуйте аркуш звіту.
Щомісяця ви повинні:
- Вставте нові фактичні дані під дані.
- Оновіть потворну зведену таблицю.
-
Змініть клітинку P1 на аркуші звіту, щоб відобразити новий місяць. Всі номери оновлюються.
Ви повинні визнати, що використання звіту, який витягує числа зі зведені таблиці, дає вам найкраще з обох світів. Ви можете форматувати звіт таким чином, що не можете відформатувати зведену таблицю. Порожні ряди чудові. Ви можете мати символи валют у першому та останньому рядках, але не між ними. Ви також отримуєте подвійне підкреслення під загальні підсумки.
Дякуємо @iTrainerMX за пропозицію цієї функції.
