Вычисляемое поле сводной таблицы Excel с датой всегда возвращает 0

У меня есть пилотная таблица, в которой обобщено множество операций.

Каждая операция датируется:

Data       | Operation | Result
-----------|-----------|-------
01/09/2020 | A         | 3
05/10/2020 | B         | 4
07/11/2020 | C         | 5
13/11/2020 | B         | 3
20/11/2020 | B         | 8
01/12/2020 | C         | 3
01/12/2020 | A         | 9

Итак, у меня есть столбец с датой начала [MIN (Date)] и конечной датой [Max (Date)]:

Operation | Start Date | End Date 
----------|------------|------------
 A        | 01/09/2020 | 01/12/2020
 B        | 05/10/2020 | 20/11/2020
 C        | 07/11/2020 | 01/12/2020

Теперь мне нужен столбец, который возвращает период (дни) от даты начала до даты окончания.

Использование =DATEDIF( MIN(DATE), MAX(DATE), "D" ) в формуле вычисляемого поля не работает. Я предполагаю, что это потому, что даты Max и Min не находятся в одной строке.

Я не могу использовать дату начала и дату окончания, как они рассчитаны в поле Сумма значений.

Я, вероятно, упускаю здесь какую-то важную концепцию. Спасибо


excel pivot-table calculated-field datediff
person rsd    schedule 22.01.2021    source источник
comment
Не можете помочь с вашей конкретной проблемой, поскольку вы предоставили недостаточно информации для ее воспроизведения, но вы можете упростить формулу. Вам не нужно DATEDIF для расчета продолжительности дней. Даты хранятся в виде серийных номеров, поэтому разница в днях будет просто EndDate - StartDate. Может помочь отредактировать ваш вопрос после просмотра тем HELP для Как мне задать хороший вопрос и также Как создать минимальный, полный и проверяемый пример.   -  person Ron Rosenfeld    schedule 23.01.2021
comment
@RonRosenfeld, это все еще невозможно (добавлены данные образца), поскольку вычисленное поле не позволяет мне ссылаться на дату начала и дату окончания поля, только дату.   -  person rsd    schedule 24.01.2021
comment
что ты имеешь в виду @AnilGoyal?   -  person rsd    schedule 24.01.2021
comment
Можете ли вы использовать powerpivot / powerquery в Excel?   -  person AnilGoyal    schedule 24.01.2021
comment
@AnilGoyal, да. Данные в сводную таблицу поступают из Power Query, созданного из Table.Combine other tables (PQ).   -  person rsd    schedule 24.01.2021

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете легко сделать это в Power Query (Get & Transform), доступном в Excel 2010+.

Наведя курсор на таблицу, перейдите к:

  • Data=> Get & Transform => from Table/Range
  • Откроется пользовательский интерфейс Power Query с загруженной таблицей данных.
  • Измените тип столбца даты с datetime на date
  • Then select the Operation column and
    • Group by
      • Advanced
      • Добавьте агрегированные минимальные и максимальные значения для начальной и конечной дат.

введите описание изображения здесь

  • Добавьте настраиваемый столбец с формулой =Duration.Days([End Date]-[Start Date])

введите описание изображения здесь

  • Закройте и загрузите

MCode

et
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {" Operation ", type text}, {" Result", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {" Operation "}, {{"Start Date", each List.Min([Date]), type nullable date}, {"End Date", each List.Max([Date]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Duration", each Duration.Days([End Date]-[Start Date]))
in
    #"Added Custom"

введите описание изображения здесь

person Ron Rosenfeld    schedule 24.01.2021
comment
Я собирался опубликовать решение в аналогичных строках. Проголосовали. - person AnilGoyal; 24.01.2021
comment
Вау! Table.Group () - это то, что я искал. Он действует как SELECT DISTINCT, и теперь я могу забыть о сводных таблицах! Спасибо! - person rsd; 24.01.2021
comment
@rsd И я только что прочитал ваш комментарий о таблице, полученной из PQ, так что вы, вероятно, могли бы включить этот код в этот запрос. - person Ron Rosenfeld; 24.01.2021