GetPivotData в Excel - Поради Excel

Ви щойно створили зведену таблицю в Excel. Клацніть за межами зведеної таблиці. Ви створюєте формулу Excel. Ви копіюєте цю формулу до всіх рядків у зведеній таблиці. Розрахунок повідомляє про неправильну відповідь для всіх, крім першого рядка зведеної таблиці. Вас щойно вжалила функція «Створити GetPivotData». Давайте подивимось, що це таке, і як запобігти цьому.

Просто запобігайте проблемі GetPivotData - швидкий метод

Найшвидший спосіб перешкодити Excel генерувати GetPivotData - це ввести формулу без використання миші або будь-яких клавіш зі стрілками. Якщо ви просто введете текст =E5/D5, то Excel створить "нормальну" відносну формулу, яку можна скопіювати до всіх рядків, що прилягають до зведеної таблиці.

Запобігання проблемі GetPivotData - Постійний метод

Існує прихований параметр, який зупиняє Excel від створення GetPivotData. Це більше приховано в Excel 2003, ніж в Excel 2007.

У програмі Excel 2007 виконайте такі дії:

• Створіть зведену таблицю в Excel 2007
• Переконайтеся, що активна комірка знаходиться всередині зведеної таблиці
• Подивіться на ліву сторону вкладки Параметри зведеної таблиці на стрічці. Є кнопка Параметри. Не натискайте кнопку Параметри! Праворуч від кнопки Параметри є стрілка спадного меню. Клацніть на стрілку спадного меню. Зніміть прапорець біля пункту Створювати GetPivotData.
• Тепер ви можете вводити формули за допомогою миші, клавіш зі стрілками або набору тексту.

У Excel 2003 та новіших версіях виконайте такі дії:

• Виберіть Інструменти, Налаштуйте
• У діалоговому вікні Налаштування є три вкладки. Виберіть вкладку Команди в центрі.
• У лівому полі списку виберіть 7-й пункт, Дані
• У правому полі списку прокрутіть майже до кінця. Восьмою піктограмою в кінці списку є Generate GetPivotData. Перетягніть цей значок із вікна списку та опустіть його посередині будь-якої існуючої панелі інструментів.
• Натисніть кнопку Закрити, щоб закрити діалогове вікно.
• Клацніть на піктограму, яку ви щойно додали на панель інструментів Excel. Це вимкне цю функцію. Тепер ви можете вводити формули за допомогою миші, не створюючи функцій GetPivotData.

Ось бонусна порада: Раніше я зневажав цю функцію і просто хотів, щоб вона весь час вимикалася. Я додав піктограму на свою панель інструментів, вимкнув піктограму, а потім видалив її з панелі інструментів. Зараз … Я трохи полегшив. Я бачу, що ікона іноді має кілька корисних застосувань. Отже, я створив зведену таблицю в Excel 2003 і переконався, що вказівник комірки знаходиться в зведеній таблиці. Потім я використав діалогове вікно налаштування, щоб перетягнути піктограму «Створити GetPivotData» на панель інструментів зведеної таблиці. Тепер - коли я перебуваю у зведеній таблиці, я можу вибрати, чи вмикати чи вимикати цю функцію.

Чому Microsoft зробила це? Чи є корисне використання GetPivotData?

Що має робити GetPivotData? Очевидно, що Microsoft подобається ця функція, оскільки вони нав'язали її мільйонам нічого не підозрюючих людей, які використовують зведені таблиці.

GetPivotData поверне будь-яку видиму комірку зі зведеної таблиці. Однак замість того, щоб вказувати на адресу комірки, ви можете описати значення як перетин різних значень полів.

У міру зміни зведеної таблиці GetPivotData продовжуватиме повертати ті самі логічні дані зі зведеної таблиці за умови, що дані все ще видно в зведеній таблиці. Це може бути важливо, якщо ви змінюєтеся з місяця на місяць у полі сторінки зведеної таблиці, і ви хочете завжди повертати продажі для конкретного клієнта. Оскільки список клієнтів змінюється щомісяця, позиція клієнта змінюватиметься. Використовуючи =GETPIVOTDATA, ви можете переконатися, що повертаєте належне значення зі зведеної таблиці.

=GETPIVOTDATAзавжди починається з двох конкретних аргументів. Потім він додатково має додаткові пари аргументів.

Ось два обов’язкові аргументи:

1. Ім'я поля даних. Це може бути "Сума доходу" або просто "Дохід".
2. Будь-яка комірка, яка знаходиться "всередині" зведеної таблиці. Чи знали ви, що ваша зведена таблиця має назву? Ви, мабуть, цього не знали, оскільки не можете знайти ім’я в інтерфейсі Excel. Вам слід було б звернутися до VBA, щоб дізнатися назву зведеній таблиці. Отже - Microsoft було простіше дозволити вам ідентифікувати зведену таблицю, вказуючи на будь-яку комірку в зведеній таблиці. Хоча ви можете вибрати будь-яку комірку, найбезпечніше вибрати верхню ліву кутову комірку. Існує ймовірність зменшення вашої зведеної таблиці для певної комбінації полів сторінки. У цьому випадку, використовуючи верхню ліву кутову комірку, ви будете впевнені, що ви продовжуєте вказувати всередині зведеної таблиці.

Потім ви продовжуєте функцію додатковими парами аргументів. Кожна пара включає ім'я поля та значення.

GetPivotData може повертати лише значення, видимі в зведеній таблиці

Побачивши, як працюють кілька функцій GetPivotData, ви можете подумати, що вони потужніші, ніж насправді. Насправді функція працюватиме лише в тому випадку, якщо певне значення видно у зведеній таблиці. Розгляньте зображення нижче.

• У комірці A2 GETPIVOTDATA повертає продажі ABC у січні 2004 року в Центральному регіоні. Це захоплення 80003 з комірки G19.
• Однак формула в A6 не вдається. Він просить продати ABC у всіх регіонах. Зведена таблиця показує загальну ABC для Центральної, загальну ABC для Сходу, загальну ABC для Заходу, але вона ніколи не відображає загальну ABC для всіх регіонів, тому результат #REF! помилка.
• Якщо ви повернете поле області до області сторінки, формула в A6 почне працювати, але формула в A2 і A4 почне повертати #REF! Якщо ви вибрали Central з випадаючого списку Region, тоді б працювали всі чотири формули.
• Вимкнення загальних підсумків у діалоговому вікні "Параметри зведеної таблиці" призведе до того, що багато функцій GetPivotData почнуть повертати #REF! помилки.