Начните с данных за несколько лет.
Допустим, у вас есть подробные записи за два года. У каждой записи есть ежедневная дата. Когда вы создаете сводную таблицу из этого отчета, в сводной таблице будут сотни строк с ежедневными датами. Это не краткое изложение.
Выберите одну из этих ячеек даты в сводной таблице. На вкладке «Анализ» на ленте выберите «Групповое поле».
Поскольку вы находитесь в поле даты, вы получаете эту версию диалога группирования. В нем снимите флажок «Месяцы» и выберите «Годы».
Ежедневные даты сводятся к годам. Переместите поле Date из ROWS в COLUMNS.
Результат практически идеальный. Но вместо общего итога в столбце D вам, вероятно, понадобится процентное отклонение.
Чтобы избавиться от столбца «Общий итог», на вкладке «Дизайн» выберите «Общие итоги», «Вкл» только для столбцов. (Я согласен, это одна из самых неудобных формулировок в Excel.)
Чтобы построить столбец дисперсии, вам необходимо написать формулу вне сводной таблицы, которая указывает внутри сводной таблицы. Не касайтесь мыши или клавиш со стрелками при построении формулы, иначе появится неприятная функция GETPIVOTDATA. Вместо этого просто введите =C5/B5-1и нажмите Enter.
Спасибо Александру Воробьеву за этот совет.
Смотреть видео
- Начните с данных за несколько лет
- Вставка, сводная таблица
- Перетащите поле даты в область строки
- Excel 2016: нажмите Ctrl + Z, чтобы разгруппировать даты
- Перетащите доход в область значений
- Выберите любую дату в области строки
- Использовать поле группы
- Выберите месяцы и годы
- Как добавить промежуточные итоги в поле лет после группировки сводной таблицы
- Используйте табличную форму в сводной таблице, чтобы дать каждому полю строки отдельный столбец
- Повторите все метки строк, чтобы заполнить пробелы в сводной таблице
- Перетащите годы в область столбца
- Щелкните правой кнопкой мыши заголовок столбца Общая сумма и удалите
- Как избежать использования функции GetPivotData, когда формула указывает на сводную таблицу
- Чтобы построить дисперсию, введите формулу без мыши и стрелок.
- Спасибо Александру Воробьеву за этот совет
Стенограмма видео
Изучите Excel из подкаста MrExcel, выпуск 1998 г. Сводная таблица по годам.
Я буду подкастировать всю эту книгу, иди, нажмите «i» в правом верхнем углу, чтобы открыть плейлист MrExcel Excel.
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Сегодня первая тема книги о сводных таблицах и о том, как создать годовой отчет. Итак, мы начинаем с набора данных, который охватывает два года, понимаете, мы переходим с 2018 по 2019 год. Я собираюсь создать сводную таблицу, вставить, сводную таблицу, хорошо, хорошо, давайте перейдем к новый рабочий лист.
На новом листе мы возьмем поле Date и перетащим его в область Rows. А потом, как видите, в Excel 2016 он автоматически группирует вещи. Теперь я знаю, что большинство людей все еще используют Excel 2013, может быть, Excel 2010, поэтому я собираюсь нажать Ctrl + Z, чтобы отменить. Такой опыт вы получите в 2010 и 2013 годах. Мы собираемся взять поле «Доход» и перетащить его в область «Значения». Итак, мы начинаем с уродливого отчета, в нем будут сотни и сотни строк, потому что продажи у нас происходят каждый божий день. Но я собираюсь перейти к самому первому полю даты, самому первому полю даты, перейти к инструментам сводной таблицы, проанализировать, в 2013 или 2016 и 2010 годах оно называлось Параметры, и выбрать поле группы. Убедитесь, что вы выбрали дату, прежде чем выбирать поле группы и сводить значения к месяцам и годам. Или кварталы и годы,или «Месяцы, кварталы и годы» нажмите «ОК». И мы получаем новое виртуальное поле под названием «Годы внизу», здесь, в области «Строки».
Теперь немного досад. Раздражает то, что они не предоставляют нам итоговые данные за год, это достаточно легко исправить, хотя в моем случае это исправлять не нужно. Нажимаем «Параметры поля» и «Автоматически», потому что я не планирую оставлять здесь годы. Конечно, каждый раз, когда я создаю сводную таблицу, я перехожу в раздел «Дизайн» и говорю «Показать в табличной форме», чтобы поместить все в отдельный столбец. А затем «Повторите все метки элементов», чтобы заполнить пустые поля слева. Опять же, в этом нет необходимости, потому что я не планирую оставлять Годы там, где они есть, я собираюсь взять Годы и перетащить их в Столбцы, и это отчет, для которого я стремился.
Хорошо, у нас есть месяцы, идущие вниз в левой части, с 2018 по 2019 год. В столбце D, что я действительно хочу, мне не нужен общий итог, я собираюсь щелкнуть правой кнопкой мыши и сказать "Удалить большую сумму" Итого, и теперь мы за пределами сводной таблицы. Итак,% Change, очень заманчиво построить эту формулу с помощью мыши или клавиш со стрелками, но вы не можете, потому что вы получите функцию… получить сводные данные, которую нелегко скопировать. Итак, = C5 / B5-1, мы отформатируем это в процентах и дважды щелкните, чтобы скопировать его, справа. Красивый отчет, показывающий, где мы были в прошлом году, где мы были в этом году, а затем процентное изменение от года к году.
Что ж, этот совет и 40 других - все в этой книге. Идите вперед, нажмите на букву «i» в правом верхнем углу рядом с книгой. Хорошо, поэтому вы начинаете с набора данных с несколькими годами, выполняете Insert, Pivot table, перетаскиваете поле даты в область строки. А в Excel 2016 нажмите Ctrl + Z, чтобы отменить их автоматические обновления группировки. Почему бы нам просто не оставить это здесь? Ну, это действительно зависит от обстоятельств. Если у вас будут данные, скажем, со 2 января по 30 декабря, они не будут правильно сгруппировать эти данные. Чтобы получить поле года, требуется целый год. Возможно, это сработает для вас, я немного помешан на контроле, я предпочитаю просто контролировать, поэтому я отменяю. Есть: перетащите доход в область значений, выберите любую ячейку, любую дату в области строки, а затем поле группы, выберите «Месяцы и годы»,перетащите годы над областью столбца, щелкните правой кнопкой мыши общий итог, чтобы удалить, а затем постройте отклонение, введите формулу, не используйте мышь или клавиши со стрелками.
Я понятия не имею, как это произносится, но спасибо этому читателю (Александр Воробьев) за этот совет и спасибо, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast1998.xlsx
