Метод сводной таблицы Excel для расчета процента роста по годам для каждого продукта, каждого клиента или чего-либо еще.
Смотреть видео
- Программа просмотра загружает данные из системы, где каждый элемент разделяется сочетанием клавиш Alt + Enter.
- Необходимо ВПР для каждого элемента в ячейке
- Используйте Power Query, чтобы разделить элементы, выполните ВПР, а затем снова объедините их
- Это похоже на выполнение ConcatenateX в DAX (которого не существует) или TEXTJOIN (CHAR (10) в Office 365 Excel
- Создал решение с использованием Power Query, включая инструмент «Извлечь как» со структурированными столбцами.
- Эта функция работает только со списком, а не с таблицей, поэтому я использовал функцию Table.Column для преобразования таблицы в список.
Стенограмма видео
Учитесь Excel из эпизода подкаста 2152: Годовой рост в процентах в сводной таблице.
Привет. Добро пожаловать в сетевую трансляцию. Я Билл Джелен. Я был в Далласе для Excelapalooza 6, проводил там 3 дня семинаров, и эти два парня из Швеции были на моем семинаре - Тобиас и Роберт. У Тобиаса есть своя собственная книга по сводным таблицам, которую он написал, и он был на моем семинаре по сводным таблицам, и я показывал один способ добиться годового роста, а у Тобиаса был лучший способ.
Итак, давайте сделаем это. ВСТАВИТЬ, ПОВОРОТНУЮ ТАБЛИЦУ, ОК. Мы собираемся поместить ДАТЫ в левую часть, и я собираюсь нажать CONTROL + Z, чтобы вернуться к таким ежедневным датам, а затем ДОХОД, и мы выберем самое первое поле ДАТА. Так было в Excel 2010, Excel 2013. ГРУППОВЫЕ ПОЛЯ, скажем, вы хотите сгруппировать по МЕСЯЦАМ и ГОДАМ. Хорошо. Прекрасно работает. Возьмите поле ГОДЫ и переместите его в КОЛОННЫ.
Теперь мы не хотим, чтобы здесь ВСЕГО ВСЕГО, поэтому мы щелкаем правой кнопкой мыши и УДАЛЯЕМ ВСЕГО, и это точка, в которой люди хотели бы увидеть рост, процентный рост 2016 года по сравнению с 2015 годом - в этом году по сравнению с прошлым. год - и мне всегда приходится строить это вне сводной таблицы, потому что я использую группировку, поэтому создание ВЫЧИСЛЕННОГО ЭЛЕМЕНТА незаконно. Не могу, хорошо?
Так что я всегда здесь застреваю. У нас есть проблема с GetPivotData и все такое, и Тобиас говорит, о нет, вы можете это сделать. Просто возьми поле ДОХОДОВ еще раз, хорошо? Итак, теперь у нас есть SUM OF REVENUE и SUM OF REVENUE 2. Это будет примерно так. Итак, у нас есть 2015, 2016. СУММА ДОХОДА, мы собираемся перейти в это поле, дважды щелкнуть, ПОКАЗАТЬ ЗНАЧЕНИЯ КАК, и расчет будет% РАЗНИЦОМ ОТ, а БАЗОВОЕ ПОЛЕ будет ГОД, а БАЗОВЫЙ ПУНКТ будет ПРЕДЫДУЩИМ, хорошо? Итак, для 2016 года он рассчитает это 287 ÷ 262-1. Он не будет знать, что делать на 2015 год, потому что у него нет данных за 2014 год. Итак, этот столбец будет пустым. Щелкните ОК. Достаточно просто. Щелкните правой кнопкой мыши и СКРЫТЬ.
Хорошо. Теперь преимущество версии Тобиаса заключается в том, что по мере того, как эта сводная таблица сжимается или растет - допустим, мы добавили срезы, и у нас было всего несколько месяцев или что-то в этом роде - расчет здесь будет уменьшаться или расти вместе со сводной таблицей, в то время как мой, поскольку вычисления находятся за пределами сводной таблицы, это просто не будет работать.
А теперь, эй, если вы умеете читать по-шведски, обязательно ознакомьтесь с этой книгой Тобиаса о сводных таблицах. Отличная книга, но если вы предпочитаете книги на английском языке, посмотрите мою книгу Power Excel With, издание 2017 года.
Хорошо. Итак, цель состоит в том, чтобы построить годовой отчет в виде сводной таблицы и показать рост в%. Я всегда выполняю этот расчет за пределами сводной таблицы, но у меня есть проблемы, такие как проблема GetPivotData, и формула должна обрабатывать, если сводная таблица сжимается или растет. Итак, используя этот метод от Тобиаса, вы можете встроить расчет в сводную таблицу, добавить доход во второй раз, изменить этот расчет на% изменения от, ГОД, ПРЕДЫДУЩИЙ элемент, а затем вам нужно скрыть дополнительный столбец. Отличный, отличный трюк от Тобиаса.
Ну привет. Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2152.xlsm
