Попытка найти разницу дат в Excel между несколькими записями

Попытка найти разницу дат в Excel между несколькими записями

У меня есть таблица счетов-фактур и дат, измененных. Это дает несколько записей счетов-фактур с несколькими датами по человеку. Я пытаюсь найти даты между первой и последней записью, чтобы получить временные рамки того, сколько времени прошло от начала до конца.

Смотрите таблицу ниже:

Счет Введено/Изменено Последнее изменение Состояние
N805844 05.02.2024 Джо Последний
N805844 05.02.2024 Стив Прежний
N805844 28.12.2024 Майк Прежний
N808961 04.01.2024 Джо Последний
N805961 03.01.2024 Джо Прежний
N805961 02.01.2024 Майк Прежний
N805961 02.01.2024 Стив Прежний

Я пытался работать над вспомогательным столбцом, чтобы определить номер счета-фактуры, а затем вычесть все значения дат, что должно дать одно число — дней до завершения. Я учитываю «Последний» и «предыдущий», потому что это может быть полезно.

Я думал о формуле Макс/Мин на другом листе, но не уверен, как это сделать.

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

решение1

Есть довольно много способов сделать это, я постараюсь опубликовать некоторые из них, насколько я знаю, используя Excel Formulas. Есть также один гибкий вариант, и это использование POWER QUERY.

Использование формул Excel:

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


• Формула, используемая в ячейке F2, возвращает уникальные счета-фактуры

=UNIQUE(A2:A8)

• Формула, используемая в ячейке G2, возвращает начальную и конечную дату.

=AGGREGATE({15,14},6,B$2:B$8/($F2=A$2:A$8),1)

• В #течение дней поместите в ячейку следующееI2

=H2-G2+1

Все приведенные выше формулы необходимо заполнить, за исключением формулы для счетов-фактур.


Альтернативный путь 2

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


Следующая формула является MS365эксклюзивной.

=LET(
     _UniqInv, UNIQUE(A2:A8),
     _LastestDate, MAXIFS(B2:B8,A2:A8,_UniqInv),
     _PriorDate, MINIFS(B2:B8,A2:A8,_UniqInv),
     _DataBody, HSTACK(_UniqInv, _PriorDate, _LastestDate, _LastestDate-_PriorDate+1),
     VSTACK({"Unique Invoice","Prior Date","Latest Date","# Of Days"},_DataBody))

  • Использование UNIQUE()функции для возврата уникальных счетов-фактур.
  • Используется MAXIFS()для возврата самой поздней даты.
  • Используется MINIFS()для возврата даты начала.
  • Используется HSTACK()для объединения всех трех приведенных выше функций, а также для вычитания второй и третьей переменных для возврата #дней.Примечание:Количество дней включает все предыдущие даты, если вы не хотите то же самое, исключите +1).
  • Наконец, используйте VSTACK()для добавления всего вместе с заголовком.

Другой способ — использовать новую функцию бета-версии в MS365-GROUPBY()

=LET(
     _Data, DROP(GROUPBY(A2:A8,B2:B8,HSTACK(MIN,MAX),0,0),1),
     _NoOfDays, INDEX(_Data,,3)-INDEX(_Data,,2)+1,
     VSTACK({"Unique Invoice","Prior Date","Latest Date","# Of Days"},
     HSTACK(_Data,_NoOfDays)))

И это также можно сделать с помощью Power Query, доступного в Windows Excel 2010+иExcel 365 (Windows or Mac)

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


Чтобы использовать Power Query, выполните следующие действия:

  • Сначала преобразуйте исходные диапазоны в таблицу и назовите ее соответствующим образом. Для этого примера я назвал ееTable1

  • Далее откройте пустой запрос из DataTab --> Get & Transform Data--> Get Data--> From Other Sources-->Blank Query

  • Вышеуказанное позволяет Power Queryоткрыть окно, теперь из HomeTab --> Advanced Editor--> и вставьте следующее, M-Codeудалив все, что вы видите, и нажмитеDone

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Invoice"}, {{"Start_Date", each List.Min([#"Entered/Modified"]), type datetime}, {"End_Date", each List.Max([#"Entered/Modified"]), type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "No_Of_Days", each Duration.Days([End_Date]-[Start_Date])+1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Invoice", type text}, {"Start_Date", type date}, {"End_Date", type date}, {"No_Of_Days", Int64.Type}})
in
    #"Changed Type"

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

  • Наконец, чтобы импортировать его обратно в Excel--> Нажмите Close & Loadили Close & Load To--> Первый нажатый элемент создаст файл New Sheetс требуемым выводом, а последний выведет окно с вопросом, куда поместить результат.

Связанный контент