Ви ненавидите функцію Excel GETPIVOTDATA? Чому вона з'являється? Як ви можете запобігти цьому? Чи добре використовується GETPIVOTDATA?
Більшість людей вперше стикаються з GETPIVOTDATA, коли намагаються побудувати формулу поза зведеною таблицею, яка використовує числа у зведеній таблиці. Наприклад, цей відсоток відхилення не буде копіюватися на інші місяці через вставку Excel функцій GETPIVOTDATA.
Excel вставляє GETPIVOTDATA кожного разу, коли ви використовуєте мишу або клавіші зі стрілками для вказівки на клітинку всередині зведеної таблиці під час побудови формули поза зведеною таблицею.
До речі, якщо ви не хочете, щоб відображалася функція GETPIVOTDATA, просто введіть формулу, наприклад = D5 / C5-1, не використовуючи клавіші миші або стрілки для вказівки на клітинки. Ця формула копіюється без проблем.
Ось набір даних, який містить один номер плану на місяць для магазину. Існують також фактичні продажі на місяць за магазин за місяці, які завершились. Ваша мета - скласти звіт, який відображатиме фактичні дані за завершені місяці та план на наступні місяці.
Створіть зведену таблицю за допомогою Store у ROWS. Помістіть місяць і тип у СТОЛБЦИ. Ви отримуєте звіт, показаний нижче, з фактичним січневим планом, січневим планом та абсолютно безглуздим січневим фактичним + планом.
Якщо вибрати клітинку місяця та перейти до Налаштування поля, ви можете змінити проміжні підсумки на Немає.
Це видаляє марний план Фактичний +. Але вам все одно доведеться позбутися колонок плану на січень-квітень. Немає хорошого способу зробити це всередині зведеної таблиці.
Отже, ваш щомісячний робочий процес стає:
- Додайте фактичні дані для нового місяця до набору даних.
- Створіть нову зведену таблицю з нуля.
- Скопіюйте зведену таблицю та вставте як значення, щоб вона більше не була сводною таблицею.
- Видаліть непотрібні стовпці.
Є кращий спосіб піти. На наступному дуже маленькому малюнку показано новий аркуш Excel, доданий до книги. Це все просто Excel, без зведених таблиць. Єдиною магією є функція IF у рядку 4, яка перемикається з фактичного на план на основі дати в комірці P1.
Найперша комірка, яку потрібно заповнити, - це січень, Actuals для Baybrook. Клацніть у цій комірці та введіть знак рівності. За допомогою миші поверніться до зведеної таблиці. Знайдіть камеру для фактичних даних за січень для Baybrook. Клацніть на цю клітинку та натисніть Enter. Як завжди, Excel створює одну з тих надокучливих функцій GETPIVOTDATA, які неможливо скопіювати.
Але сьогодні давайте вивчимо синтаксис GETPIVOTDATA.
Перший аргумент нижче - числове поле "Продажі". Другий аргумент - це комірка, в якій знаходиться зведена таблиця. Решта пар аргументів - це ім’я поля та значення. Чи бачите ви, що зробила автоматично сформована формула? У назві магазину це кодовано "Baybrook". Ось чому ви не можете копіювати ці автоматично згенеровані формули GETPIVOTDATA. Вони насправді твердо кодують імена у формули. Хоча ви не можете скопіювати ці формули, ви можете редагувати їх. У цьому випадку було б краще, якби ви відредагували формулу, щоб вказати на клітинку $ D6.
Ось формула після її редагування. Зникли "Baybrook", "Jan" і "Actual". Натомість ви вказуєте на $ D6, E $ 3, E $ 4.
Скопіюйте цю формулу, а потім виберіть «Спеціальна вставка», «Формули» у всіх інших числових клітинках.
Ось ваш щорічний робочий процес:
- Створіть потворну зведену таблицю, яку ніхто ніколи не побачить.
- Налаштуйте аркуш звіту.
Щомісяця ви повинні:
- Вставте нові фактичні дані під дані.
- Оновіть потворну зведену таблицю.
-
Змініть клітинку P1 на аркуші звіту, щоб відобразити новий місяць. Всі номери оновлюються.
Змінити комірку P1
Ви повинні визнати, що використання простого звіту, який витягує числа зі зведеній таблиці, дає вам найкраще з обох світів. Ви можете форматувати звіт таким чином, що не можете відформатувати зведену таблицю. Порожні ряди чудові. Ви можете мати символи валют у першому та останньому рядках, але не між ними. Ви також отримуєте подвійне підкреслення під загальні підсумки.
Дякуємо @iTrainerMX за пропозицію цієї функції.
Переглянути відео
- GetPivotData відбувається, коли формула вказує всередині зведеної таблиці
- Хоча початкова формула є правильною, ви не можете скопіювати формулу
- Більшість людей ненавидять getpivotdata і хочуть запобігти цьому
- Спосіб 1: Створіть формулу без клавіші миші та стрілок
- Спосіб 2. Вимкніть GetPivotData назавжди, використовуючи спадне меню поруч із параметрами
- Але для GetPivotData є користь
- Ваш менеджер хоче звіт із фактичними даними за минулі місяці та бюджетом на майбутнє
- Звичайний робочий процес дозволить вам створити зведену таблицю, перетворити на значення, видалити стовпці
- Видалення проміжних підсумків для запобігання фактичному та січневому плану за січень за допомогою налаштувань поля
- Натомість створіть зведену таблицю з даними "занадто багато"
- Використовуйте добре відформатований аркуш звіту
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- З першої комірки даних на аркуші побудуйте формулу за допомогою миші
- Дозволити GetPivotData
- Вивчіть синтаксис GetPivotData (поле для повернення, місце зведення, пари)
- Змініть жорстко закодоване значення, щоб вказати на клітинку
- Триразове натискання клавіші F4 блокує лише колону
- Натискання клавіші F4 двічі блокує лише рядок
- Вставте спеціальні формули
- Робочий процес наступного місяця: додайте дані, оновіть зведену таблицю, змініть дату
- Дуже обережно стежте за новими магазинами
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2013 - GetPivotData не може бути цілком злим!
Я буду підкастувати всю цю книгу, натисніть "i" у верхньому правому куті, щоб підписатися.
Добре, ще в епізоді 1998 року я коротко розповів про цю проблему GetPivotData. Якщо ми обчислюємо відхилення у%, і ми знаходимося поза зведеною таблицею, яка вказує всередину, і я використовую мишу або клавішу зі стрілкою, так 2019 / 2018-1. Ця відповідь, яку ми отримаємо тут, правильна для січня, але коли ми двічі клацнемо, щоб скопіювати це, формула не копіює, ми отримуємо січневу відповідь до кінця. І коли ми дивимось на це, ми отримуємо GetPivotData, я не вводив GetPivotData, я просто вказував на ці клітинки, і це почало відбуватися ще в Excel 2002 без будь-якого попередження. І тоді я сказав, що спосіб уникнути цього - набрати формулу C5 / B5-1, і ви отримаєте формулу, яку зможете скопіювати. Або якщо ви просто ненавидите GetPivotData, якщо він "абсолютно злий", перейдіть на вкладку Аналіз, неt між іншим відкрийте кнопку Параметри. Поверніться до зведеної таблиці, перейдіть на вкладку Аналіз, відкрийте спадне меню поруч із Параметри, зніміть цей прапорець, це загальне налаштування. Як тільки ви вимкнете його, він буде вимкнений назавжди, добре.
Більшість випадків, коли я отримую запитання: "Як вимкнути GetPivotData?" але раз у раз я знайду когось, хто любить GetPivotData. І я обідав з Роб Коллі, коли він ще був у Microsoft, і він сказав: "Ну, наші внутрішні клієнти люблять GetPivotData". Я сказав: “Що? Ні, всі ненавидять GetPivotData! " Роб говорить: "Ви маєте рацію, поза межами Microsoft, абсолютно, вони ненавидять GetPivotData". Я говорю про бухгалтерів всередині Microsoft, а пізніше я зустрів того, хто зараз працює в команді Excel, Карлос, і Карлос був одним із бухгалтерів, які використовують цей метод.
Гаразд, ось ось що нам робити. У нас є наш звіт, набір даних, що на кожен місяць ми маємо план для кожного магазину, а потім внизу ми накопичуємо фактичні дані. Гаразд, тому у нас є фактичні дані за січень-грудень, але фактичні лише за кілька місяців, місяців, що минули. І що наш менеджер хоче, щоб ми зробили, це скласти звіт із магазинами ліворуч, лише магазини Техасу, звичайно, щоб ускладнити життя. А потім, переходячи, у нас є місяці, і якщо у нас є фактичний показник за цей місяць, ми показуємо фактичний, тобто фактичний січень, фактичний лютий, фактичний березень, фактичний квітень. Але тоді на місяці, коли у нас немає фактичних даних, ми перемикаємось і показуємо бюджет, так що бюджет виплачується до грудня, а потім загальний підсумок всього, добре. Ну, коли ви намагаєтеся створити цю зведену таблицю, так,це не працює.
Тож вставте зведену таблицю, новий робочий аркуш, ви кладете Store ліворуч, красиву сторону, покладіть Months зверху, поставте Type зверху, поставте Sales тут, добре. Отже, ось що ми отримуємо, з чим нам доводиться починати працювати, тож маємо фактичний січневий план, січневий план, а потім абсолютно марний фактичний плюс-січневий план. Ніхто цим ніколи не скористається, але я можу позбутися цих сірих стовпців, це досить просто, деякі тут у цю клітинку, перейдіть до Налаштування поля та змініть проміжні підсумки на Ні. Але я абсолютно не маю можливості видалити січневий план, а також не видалити квітневий травневий червневий липневий план, добре, немає можливості позбутися цього. Тому на цьому етапі кожного місяця я застряг у виборі всієї зведеної таблиці, перейду до Копіювати, а потім Вставити, Вставити значення. Це вже не зведена таблиця,а потім я починаю вручну видаляти стовпці, які не відображаються у звіті.
Добре, це звичайний метод, але бухгалтери Microsoft додали додатковий крок у січні, це займає 15 хвилин, і цей крок дозволяє цій зведеній таблиці жити вічно, так? Я називаю це найпотворнішою зведеною таблицею у світі, і бухгалтери Microsoft визнають, що це найгірша зведена таблиця у світі, але ніхто ніколи не побачить цей звіт, крім них. Що вони роблять, це приходять сюди на новий аркуш і складають звіт, який хоче їх менеджер. Гаразд, от ось магазини ліворуч, я навіть згрупував їх у Х'юстон, Даллас та Інші, це приємно відформатований звіт. Я виділив підсумки, ви побачите, що коли ми отримуємо деякі цифри, у першому рядку є валюта, але не ці наступні рядки, порожні рядки. Ой, порожні рядки у зведеній таблиці.І одна крихітна логіка тут, де я можу помістити дату проходження в клітинку P1, а потім у мене тут є формула, яка аналізує, ЩО МІСЦЯ датою закінчення> цей стовпець, а потім поставити слово Actual, інакше поставте слово План, добре. Отже, все, що мені потрібно зробити, це змінити це через дату, а потім слово Фактичне перевернути на план, Гаразд.
Зараз, ось що ми робимо, ми дозволимо собі бути GetPivotData'd, так? Я не впевнений, що це дієслово, але ми дозволимо Microsoft отримати GetPivotData. Тож я починаю будувати формулу з знаком =, хапаю мишку, і збираюся піти шукати фактичний Бейбрук за січень! Тож я повертаюся до найпотворнішої у світі зведеної таблиці, знаходжу Бейбрука, знаходжу січень, вважаю справжнім і натискаю Enter, і дозволяю їм робити це зі мною, гаразд, ось ми маємо формулу GetPivotData. Я пам’ятаю той день, коли я це робив, це було, як ви знаєте, після того, як Роб пояснив мені, що вони роблять, і я повернувся і спробував це. Зараз раптово, я все своє життя позбувся GetPivotData, я фактично ніколи не сприймав GetPivotData. Отже, що це таке, це перший предмет - це те, що ми шукаємо, там 'sa поле "Продажі", тут починається зведена таблиця, і це може бути будь-яка комірка зведеної таблиці, вони використовують верхню ліву руку.
Добре, це назва поля "Store", а потім вони закодували "Baybrook", це назва поля "Month", вони жорстко закодували "January", це назва поля "Type", і вони ' я з жорстким кодом "Фактичний". Ось чому ви не можете скопіювати його, оскільки вони жорстко закодували значення. Але бухгалтери Microsoft, Карлос та його співробітники усвідомлюють: "Ой, зачекайте секунду, у нас тут слово" Бейбрук ", тут - січень, тут -" Фактичний ". Нам просто потрібно змінити цю формулу, щоб вказувати на фактичні клітинки у звіті, а не на жорстке кодування " Гаразд, тому вони називають це параметризацією GetPivotData.
Видаліть слово Бейбрук, підійдіть сюди та натисніть клітинку D6. Тепер мені потрібно зафіксувати це до стовпця, добре, тому я натискаю клавішу F4 3 рази, отримую один долар перед D, добре. На січень я видаляю закодований січень, натискаю клітинку E3, двічі натискаю F4, щоб зафіксувати її на рядку, E $ 3. Введіть Actual, видаліть слово Actual, натисніть E4, ще раз F4 двічі, добре, і я отримаю формулу, яка тепер тягне ці дані назад. Я збираюся скопіювати це, а потім Вставити спеціальне, вибрати Формати, alt = "" ESF, побачити, що там підкреслено F, ESF Enter, а потім, що я зробив, я просто повторю з F4, F4 - це повтор, і F4. Добре, отже, тепер у нас є гарний на вигляд звіт, він має порожні місця, він має форматування, він має єдине підкреслення бухгалтерського обліку під кожним розділом,внизу він має подвійне підкреслення бухгалтерського обліку.
Правильно, ви ніколи не отримуєте ці матеріали у зведеній таблиці, це неможливо, але цей звіт створюється із зведеній таблиці. Тоді, що ми робимо, коли отримуємо травневі фактичні дані, повертаємось сюди, вставляємо їх, оновлюємо найпотворнішу зведену таблицю у світі, а потім тут у звіті просто змінюємо дату проходження з 4/30 на 5/31. І що це робить, це спричиняє перехід цієї формули зі слова «План» на «Фактичний», яке переходить і витягує фактичні дані зі звіту замість плану, добре. Ось така річ: це чудово, правда? Я бачу, де б я багато цим займався, якби я все ще, знаєте, працював в бухгалтерії.
Річ, з якою ви повинні бути дуже обережними, - якщо вони створюють новий магазин, ви повинні знати, щоб додати його вручну, вірно, дані будуть відображатися у зведеній таблиці, але ви додавали б їх вручну. Зараз цей є підмножиною всіх магазинів, якби він звітував про всі магазини, я б, мабуть, тут, за межами діапазону друку, мав щось, що витягло загальний підсумок зі зведеної таблиці. І тоді я б знав, якщо цей підсумок не збігається із загальним підсумком із зведеної таблиці, що щось не так, і тут є функція IF, яка каже: „Гей, знаєте, додані нові дані, будьте дуже обережні. " Вони мають якийсь механізм, щоб виявити наявність нових даних. Але я розумію, це круте використання. Отже, хоча більшу частину часу GetPivotData просто зводить нас з розуму, насправді це може бути корисним. Добре,отже, це підказка №21 із 40 у книзі, придбайте книгу прямо зараз, замовте через Інтернет, клацніть на “i” у верхньому правому куті.
Довгий, довгий підсумок сьогодні, добре: GetPivotData трапляється, коли формула вказує всередині зведеної таблиці, а формула поза зведеною таблицею вказує всередині. Хоча початкова формула є правильною, вона не копіюється. Більшість людей ненавидять GetPivotData і хочуть запобігти цьому. Отже, ви можете створити формулу без миші або клавіш зі стрілками, просто введіть формулу або назавжди вимкніть GetPivotData, ах, але тут є користь, добре. Тож ми повинні скласти звіт із фактичними даними за минулий місяць, бюджетом на майбутній. Звичайний робочий процес, створіть зведену таблицю, перетворіть у значення, Видаліть стовпці. Існує спосіб видалити проміжні підсумки, скориставшись Налаштуваннями поля, позбувшись фактичного плюсового плану за січень. Натомість ми просто збираємось створити найпотворнішу у світі зведену таблицю із занадто великою кількістю даних.
Створіть приємно відформатований, звичайний старий аркуш звіту з, можливо, трохи логіки, щоб змінити слово «Фактичний» на «Планувати». А потім із першої комірки звіту, першого місця, де в цьому звіті будуть числа, введіть =, наведіть вказівник на зведену таблицю і дозвольте отримати GetPivotData. Ми вивчаємо синтаксис GetPivotData, тому це поле для повернення, Продажі, де живе зведена таблиця, а потім пари критеріїв, ім’я поля та значення. Ми збираємося вилучити закодоване значення та вказати на клітинку, натискаючи F4 3 рази, блокує лише стовпець, натискання F4 2 рази блокує лише рядок, скопіюйте цю формулу, Вставте спеціальні формули. Я дав там додаткову підказку про те, що F4 є повторним, тому мені довелося лише один раз перейти до діалогового вікна "Спеціальна вставка", а потім для наступної "Спеціальної вставки" просто використав F4. Наступного місяця додайте дані,оновіть зведену таблицю, змініть дату проходження. Переконайтеся, що вони не будували жодного нового магазину, знаєте, мають якийсь механізм, або ручний, або контрольну формулу, перевірте це. Завдяки iTrainerMX у Twitter, який запропонував GetPivotData, а також Карлосу та Робу від Microsoft, Роб тепер від Power Pivot Pro. Карлос за те, що він використовував це, і Роб за те, що він сказав мені, що Карлос цим користувався, я познайомився з Карлосом пізніше, і він підтвердив, що так, він був одним з бухгалтерів, який весь час використовував це в Microsoft, добре, ось.і Роб за те, що він сказав мені, що Карлос цим користується, я познайомився з Карлосом пізніше, і він підтвердив, що так, він був одним з бухгалтерів, який весь час використовував це в Microsoft, добре, ось і ви.і Роб за те, що він сказав мені, що Карлос цим користується, я познайомився з Карлосом пізніше, і він підтвердив, що так, він був одним з бухгалтерів, який весь час використовував це в Microsoft, добре, ось і ви.
Ну, привіт, я хочу подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2013.xlsx
