
У меня есть таблица счетов-фактур и дат, измененных. Это дает несколько записей счетов-фактур с несколькими датами по человеку. Я пытаюсь найти даты между первой и последней записью, чтобы получить временные рамки того, сколько времени прошло от начала до конца.
Смотрите таблицу ниже:
Счет | Введено/Изменено | Последнее изменение | Состояние |
---|---|---|---|
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с требуемым выводом, а последний выведет окно с вопросом, куда поместить результат.