Вы ненавидите функцию GETPIVOTDATA в Excel? Почему появляется? Как это предотвратить? Есть ли хорошее применение GETPIVOTDATA?
Большинство людей впервые сталкивается с GETPIVOTDATA, когда они пытаются построить формулу вне сводной таблицы, которая использует числа в сводной таблице. Например, этот процент отклонения не будет скопирован в другие месяцы из-за того, что Excel вставляет функции GETPIVOTDATA.
Excel вставляет GETPIVOTDATA каждый раз, когда вы используете мышь или клавиши со стрелками, чтобы указать на ячейку внутри сводной таблицы при построении формулы вне сводной таблицы.
Кстати, если вы не хотите, чтобы функция GETPIVOTDATA отображалась, просто введите формулу, например = D5 / C5-1, без использования мыши или клавиш со стрелками для указания на ячейки. Эта формула копируется без проблем.
Вот набор данных, который содержит один номер плана в месяц для каждого магазина. Также есть фактические продажи в месяц для каждого магазина за полные месяцы. Ваша цель - построить отчет, который показывает фактические данные за завершенные месяцы и план на будущие месяцы.
Создайте сводную таблицу с помощью Store в ROWS. Поместите месяц и введите в КОЛОННЫ. Вы получите отчет, показанный ниже, с фактическим январским планом, январским планом и совершенно бессмысленным январским фактическим + планом.
Если вы выберете ячейку месяца и перейдете в Настройки поля, вы можете изменить промежуточные итоги на Нет.
Это удаляет бесполезный план Actual +. Но вам все равно нужно избавиться от столбцов плана с января по апрель. Нет хорошего способа сделать это внутри сводной таблицы.
Итак, ваш ежемесячный рабочий процесс становится:
- Добавьте к набору данных фактические данные за новый месяц.
- Создайте новую сводную таблицу с нуля.
- Скопируйте сводную таблицу и вставьте ее как значения, чтобы она больше не была сводной таблицей.
- Удалите столбцы, которые вам не нужны.
Есть способ лучше. На следующем очень маленьком рисунке показан новый рабочий лист Excel, добавленный в книгу. Это все просто Excel, без сводных таблиц. Единственное волшебство - это функция ЕСЛИ в строке 4, которая переключается с Фактического на План в зависимости от даты в ячейке P1.
Самая первая ячейка, которую необходимо заполнить, - это январь, фактические данные для Бейбрука. Щелкните эту ячейку и введите знак равенства. С помощью мыши вернитесь к сводной таблице. Найдите ячейку с данными за январь для Бейбрука. Щелкните эту ячейку и нажмите Enter. Как обычно, Excel создает одну из тех надоедливых функций GETPIVOTDATA, которые нельзя скопировать.
Но сегодня давайте изучим синтаксис GETPIVOTDATA.
Первый аргумент ниже - это числовое поле «Продажи». Второй аргумент - это ячейка, в которой находится сводная таблица. Остальные пары аргументов - это имя поля и значение. Вы видите, что сделала автоматически созданная формула? В качестве названия магазина было жестко запрограммировано "Бейбрук". Вот почему вы не можете скопировать эти автоматически сгенерированные формулы GETPIVOTDATA. Они фактически жестко кодируют имена в формулы. Даже если вы не можете скопировать эти формулы, вы можете их редактировать. В этом случае было бы лучше, если бы вы отредактировали формулу, чтобы она указывала на ячейку $ D6.
Вот формула после ее редактирования. Ушли в прошлое "Бэйбрук", "Ян" и "Актуальный". Вместо этого вы указываете на $ D6, E $ 3, E $ 4.
Скопируйте эту формулу и выберите «Специальная вставка», «Формулы» во всех остальных числовых ячейках.
Вот ваш годовой рабочий процесс:
- Создайте уродливую сводную таблицу, которую никто никогда не увидит.
- Настройте лист отчета.
Каждый месяц вам необходимо:
- Вставьте новые актуальные данные под данными.
- Обновите уродливую сводную таблицу.
-
Измените ячейку P1 на листе отчета, чтобы отразить новый месяц. Все номера обновляются.
Изменить ячейку P1
Вы должны признать, что использование простого отчета, извлекающего числа из сводной таблицы, дает вам лучшее из обоих миров. Вы можете форматировать отчет так, как не можете форматировать сводную таблицу. Пустые строки вполне подойдут. В первой и последней строках могут быть символы валюты, но не между ними. Вы также получаете двойное подчеркивание под общими итогами.
Спасибо @iTrainerMX за предложение этой функции.
Смотреть видео
- GetPivotData происходит, когда формула указывает внутрь сводной таблицы
- Хотя исходная формула верна, вы не можете скопировать формулу
- Большинство людей ненавидят getpivotdata и хотят предотвратить это
- Метод 1. Создайте формулу без мыши и клавиш со стрелками
- Метод 2. Отключите GetPivotData навсегда с помощью раскрывающегося списка рядом с параметрами.
- Но есть применение GetPivotData
- Ваш менеджер хочет получить отчет с фактическими данными за прошлые месяцы и бюджетом на будущее
- В обычном рабочем процессе вы должны создать сводную таблицу, преобразовать ее в значения, удалить столбцы.
- Удаление промежуточных итогов, чтобы предотвратить январь Actual + Plan с помощью настроек поля
- Вместо этого создайте сводную таблицу со «слишком большим количеством» данных.
- Используйте хорошо отформатированный лист отчета
=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, если он «полностью злой», перейдите на вкладку «Анализ»,Не открывайте, кстати, кнопку "Параметры". Вернитесь к сводной таблице, перейдите на вкладку «Анализ», откройте раскрывающийся список рядом с «Параметры», снимите этот флажок, это глобальный параметр. Как только вы его выключите, он будет отключен навсегда, хорошо.
Чаще всего я получаю следующие вопросы: «Как отключить GetPivotData?» но время от времени я получаю кого-нибудь, кто любит GetPivotData. И я обедал с Робом Колли, когда он еще работал в Microsoft, и он сказал: «Что ж, нашим внутренним клиентам нравится GetPivotData». Я сказал: «Что? Нет, все ненавидят GetPivotData! » Роб говорит: «Вы правы, за пределами Microsoft, безусловно, они ненавидят GetPivotData». Я говорю о бухгалтерах в Microsoft, а позже я встретил человека, который сейчас работает в команде Excel, Карлос, и Карлос был одним из бухгалтеров, использующих этот метод.
Хорошо, вот что нам нужно сделать. У нас есть отчет, набор данных, в котором на каждый месяц у нас есть план для каждого магазина, а внизу мы накапливаем фактические данные. Хорошо, у нас есть фактические данные за период с января по декабрь, но есть фактические данные только за несколько месяцев, за прошедшие месяцы. И наш менеджер хочет, чтобы мы построили отчет с магазинами внизу слева, конечно, только с магазинами Техаса, чтобы усложнить жизнь. Затем у нас есть месяцы, и если у нас есть фактическое значение для этого месяца, мы показываем фактическое значение, то есть фактическое значение января, фактическое значение февраля, фактическое значение марта, фактическое значение апреля. Но затем в те месяцы, когда у нас нет фактических данных, мы переключаемся и показываем бюджет, так что бюджет составляется до декабря, а затем складывается общая сумма, хорошо. Что ж, когда вы пытаетесь создать эту сводную таблицу, да,это не работает.
Итак, вставьте PivotTable, New Worksheet, поместите Store внизу слева, красивую сторону, поместите месяцы вверху, поместите Type вверху, поместите Sales здесь, хорошо. Итак, вот что мы получаем, с чем мы должны начать работать: у нас есть фактический январский план, январский план, а затем совершенно бесполезный январский план фактического плюс. Никто никогда не будет использовать это, но я могу избавиться от этих серых столбцов, это достаточно просто, некоторые здесь, в этой ячейке, перейдите в Настройки поля и измените Промежуточные итоги на Нет. Но у меня нет абсолютно никакого способа удалить план на январь, который не удалит также план на апрель май июнь июль, хорошо, нет никакого способа избавиться от этого. Итак, на этом этапе каждый месяц я застреваю, выбирая всю сводную таблицу, выбирая «Копировать», а затем «Вставить», «Вставить значения». Это больше не сводная таблица,а затем я начинаю вручную удалять столбцы, которых нет в отчете.
Хорошо, это обычный метод, но бухгалтеры Microsoft добавили в январе дополнительный шаг, он занимает 15 минут, и этот шаг позволяет этой сводной таблице жить вечно, верно? Я называю это самой уродливой сводной таблицей в мире, и бухгалтеры Microsoft признают, что это самая уродливая сводная таблица в мире, но никто, кроме них, никогда не увидит этот отчет. Что они делают, так это то, что они переходят к новому листу и создают отчет, который хочет их руководитель. Хорошо, вот магазины внизу слева, я даже сгруппировал их в Хьюстон, Даллас и другие, это красиво оформленный отчет. Я выделил итоги, вы увидите, что когда мы введем некоторые числа, в первой строке будет валюта, но не в этих последующих строках, пустых строках. Ох, пустые строки в сводной таблице.И один крошечный кусочек логики здесь, где я могу поместить конечную дату в ячейку P1, а затем у меня есть формула, которая анализирует, что ЕСЛИ месяц конечной даты> этого столбца, а затем помещаю слово Фактическое, в противном случае поместите слово "план", хорошо. Так что все, что мне нужно сделать, это изменить это до конца даты, а затем слово «Фактический» перевернуть на «план», хорошо.
Итак, вот что мы делаем, мы позволим себе быть GetPivotData'd, верно? Я не уверен, что это глагол, но мы собираемся разрешить Microsoft GetPivotData. Итак, я начинаю строить формулу с помощью знака =, хватаю мышь и собираюсь найти январский Бейбрук! Итак, я возвращаюсь к самой уродливой сводной таблице в мире, нахожу Бейбрука, нахожу январь, нахожу актуальный, нажимаю Enter и позволяю им сделать это со мной, хорошо, вот и мы, теперь у нас есть формула GetPivotData. Я помню тот день, когда я сделал это, это было как после того, как Роб объяснил мне, что они делают, и я вернулся и попробовал. И вот внезапно, всю свою жизнь я избавлялся от GetPivotData, я никогда не использовал GetPivotData. Итак, что это, первый предмет - это то, что мы ищем, вот.sa поле под названием "Продажи", здесь начинается сводная таблица, и это может быть любая ячейка в сводной таблице, они используют верхнюю левую руку.
Хорошо, это имя поля «Магазин», а затем они жестко запрограммировали «Бейбрук», это имя поля «Месяц», они жестко запрограммировали «Январь», это имя поля «Тип», и они ' ve жестко запрограммировал «Фактический». Вот почему вы не можете его скопировать, потому что они жестко запрограммировали значения. Но бухгалтеры Microsoft, Карлос и его коллеги понимают: «Погодите-ка, у нас есть слово Бейбрук, здесь январь, здесь - Actual». Нам просто нужно изменить эту формулу, чтобы она указывала на фактические ячейки в отчете, а не на жестко запрограммированные ». Хорошо, они называют это параметризацией GetPivotData.
Уберите слово Baybrook, подойдите сюда и нажмите на ячейку D6. Теперь мне нужно привязать это к столбцу, хорошо, поэтому я нажимаю клавишу F4 3 раза, получаю один доллар перед D, хорошо. В январе я удаляю жестко запрограммированный январь, нажимаю на ячейку E3, дважды нажимаю F4, чтобы привязать ее к строке E $ 3. Введите «Фактический», удалите слово «Фактический», нажмите E4, снова дважды F4, хорошо, и я получаю формулу, которая теперь возвращает эти данные. Я собираюсь скопировать это, а затем «Специальная вставка», выбрать «Форматы», alt = "" ESF, увидеть, что буква F подчеркнута там, ESF Enter, а теперь, когда я сделал это, я просто повторю с F4, F4 - это повтор, а F4. Хорошо, теперь у нас есть красивый отчет, в нем есть пробелы, есть форматирование, есть единственное подчеркивание под каждым разделом,в самом низу двойное подчеркивание.
Правильно, вы никогда не получите такой материал в сводной таблице, это невозможно, но этот отчет создается на основе сводной таблицы. Итак, что мы делаем, когда получаем фактические данные за май, возвращаемся сюда, вставляем их, обновляем самую уродливую сводную таблицу в мире, а затем здесь, в отчете, просто меняем конечную дату с 30 апреля на 31 мая. И это приводит к тому, что эта формула переключается со слова «План» на «Фактический», которое берет фактические данные из отчета, а не из плана, хорошо. Вот что … это здорово, правда? Я вижу, где бы я этим занимался, если бы все еще работал бухгалтером.
Вы должны быть очень осторожны, если они создают новый магазин, вы должны знать, чтобы добавить его вручную, верно, данные будут отображаться в сводной таблице, но вы должны добавить их вручную. Теперь это подмножество всех магазинов, если бы он сообщал обо всех магазинах, я бы, вероятно, здесь, за пределами диапазона печати, имел что-то, что извлекает общую сумму из сводной таблицы. И тогда я бы знал, что если эта сумма не совпадает с общей суммой из сводной таблицы, что что-то не так, и здесь есть функция IF, говорящая: «Эй, вы знаете, добавлены новые данные, будьте очень осторожны. ” У них есть своего рода механизм обнаружения появления новых данных. Но я понимаю, это классное использование. Итак, хотя в большинстве случаев GetPivotData просто сводит нас с ума, на самом деле он может быть полезен. Хорошо,так что это совет № 21 из 40 в книге, купите книгу прямо сейчас, закажите онлайн, нажмите «i» в правом верхнем углу.
Сегодня длинный, длинный обзор, хорошо: GetPivotData происходит, когда формула указывает внутрь сводной таблицы, а формула за пределами сводной таблицы указывает внутрь. Хотя исходная формула верна, она не копируется. Большинство людей ненавидят GetPivotData и хотят предотвратить это. Таким образом, вы можете создать формулу без мыши или клавиш со стрелками, просто введите формулу или отключите GetPivotData навсегда, ах, но в этом есть применение, хорошо. Таким образом, мы должны построить отчет с фактическими данными за последний месяц и бюджетом на будущее. Обычный рабочий процесс, создание сводной таблицы, преобразование в значения, удаление столбцов. Есть способ удалить промежуточные итоги с помощью настроек поля, избавившись от январского плана фактических плюсов. Вместо этого мы просто собираемся создать самую уродливую сводную таблицу со слишком большим количеством данных.
Создайте красиво отформатированный, простой старый лист отчета, возможно, немного логики, чтобы изменить слово Фактический на План. Затем из первой ячейки отчета, первого места, где будут находиться числа в этом отчете, введите знак =, перейдите к сводной таблице и позвольте GetPivotData произойти. Мы исследуем синтаксис GetPivotData, так что это возвращаемое поле, Sales, где находится сводная таблица, а затем пары критериев, имя поля и значение. Мы собираемся удалить жестко запрограммированное значение и указать на ячейку, нажатие F4 3 раза блокирует только столбец, нажатие F4 2 раза блокирует только строку, копирует эту формулу, вставляет специальные формулы. Я добавил туда дополнительный совет, что F4 - это повтор, поэтому мне нужно было только один раз перейти в диалоговое окно «Специальная вставка», а затем для следующей вставки специальных формул просто использовать F4. В следующем месяце добавим данные,обновите сводную таблицу, измените дату окончания. Убедитесь, что они не построили никаких новых магазинов, знаете, у них есть какой-то механизм, ручной или формула проверки, проверьте это. Спасибо iTrainerMX в Твиттере, который предложил GetPivotData, а также Карлосу и Робу из Microsoft, теперь Робу из Power Pivot Pro. Карлос за то, что использовал это, и Роб за то, что сказал мне, что Карлос использовал это, я встретил Карлоса позже, и он подтвердил, что да, он был одним из бухгалтеров, которые все время использовали это в Microsoft, хорошо, вот и все.и Роб за то, что сказал мне, что Карлос использовал это, я встретил Карлоса позже, и он подтвердил, что да, он был одним из бухгалтеров, которые все время использовали это в Microsoft, хорошо, вот и все.и Роб за то, что сказал мне, что Карлос использовал это, я встретил Карлоса позже, и он подтвердил, что да, он был одним из бухгалтеров, которые все время использовали это в Microsoft, хорошо, вот и все.
Что ж, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2013.xlsx
