Сводная таблица не может найти разницу между январем текущего года и декабрем прошлого года. Excel 2010

Через несколько недель я собираю свои первые сводные таблицы, не могу поверить в силу их, но попал в ловушку, которую я не могу преодолеть.

У меня есть рабочий лист, в котором есть столбец с необработанными данными (вручную вменяется)

Затем у меня есть еще 2 столбца, которые вычисляют разницу с предыдущим месяцем и разницу%. Это прекрасно работает.

Как вы можете видеть, январь 2015 года пуст, так как поле разметки ищет предыдущий месяц, а январь - первый месяц.

как я могу понять это, чтобы посмотреть на декабрь 2014 года.

2 ответа

Если есть два разных уровня группировки, то есть год и месяц, невозможно получить Excel для сравнения последнего элемента одной группы с первым элементом предыдущей группы.

Excel рассматривает дополнительный уровень группировки как другое поле, которое видно при просмотре полей сводной таблицы. Годы даты показывают как отдельное поле:

При выборе для вычисления разницы Excel может сравниться только с значениями в одном поле. Обратите внимание, что вы можете сравнить с определенным значением месяца, но не конкретным месяцем определенного года:

Аналогично, Excel не может сравниться со значениями из предыдущей группировки, хотя человеческая логика знает это предыдущее значение в общей последовательности.

Чтобы увидеть разницу между каждым месяцем года, необходимо, чтобы весь год-месяц в одном поле. Это невозможно с помощью группировки PivotTable в обычные даты.

Обходное решение требует добавления нового поля в данные, которые содержат одно значение за год-месяц. Затем сводная таблица будет группировать данные по этим значениям Year-Month.

Это можно сделать двумя способами:

  • Рассчитать первый день каждого года-месяца

Вставьте другой столбец с этой формулой (со ссылкой на дату в ячейке A2):

=Date(Year(A2),Month(A2),1)

Выполняет группировку до того, как данные перейдут в сводную таблицу, обработав каждую дату как первую из месяца. В сводной таблице по умолчанию будет отображаться полная дата. Вы можете применить форматирование пользовательского номера к полю, чтобы отображать только месяц и год, например: "mmm yyyy"

Это лучшее решение при группировке по месяцам, поскольку оно позволяет больше параметров форматирования.

  1. Вычислить текстовое значение для каждого года-месяца

Вставьте столбец с этой формулой:

=TEXT(A2,"yyyy mm")

Например, следующие данные:

Может генерировать эту сводную таблицу (с суммами, а затем с разностями):

Текстовое решение имеет недостаток, который вы должны указать Год, а затем Месяц численно (и дополняется 4 и 2 разрядами соответственно), если вы хотите, чтобы сводная таблица автоматически упорядочивала значения в хронологическом порядке.

Преимущество текстового решения - большая гибкость. Например, вы можете группировать два месяца с более продвинутыми формулами или группами двух полей фактических данных вместе путем конкатенации.


Вы пробовали комбинировать месяц и год в одной ячейке? Поэтому в исходных данных в столбце месяца есть:

Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15

Вместо того, чтобы иметь один столбец для названия месяца и один год. Убедитесь, что как только вы вводите месяцы на вкладке исходных данных, они переходят в правую часть ячейки, что означает, что Excel видит их как даты, а не текст.

licensed under cc by-sa 3.0 with attribution.