☰

Excel에서 여러 항목 간의 날짜 차이를 찾으려고 합니다.

tag-icon • tag-icon microsoft-excel worksheet-function
Excel에서 여러 항목 간의 날짜 차이를 찾으려고 합니다.

송장 및 날짜 수정 테이블이 있습니다. 이렇게 하면 개인별로 여러 날짜가 포함된 여러 송장 항목이 제공됩니다. 내가 하려는 것은 첫 번째 항목과 마지막 항목 사이의 날짜를 찾아 처음부터 끝까지 걸린 시간을 알려주는 것입니다.

아래 표를 참조하세요.

송장 입력/수정됨 최종 수정됨 상태
N805844 2024년 2월 5일 조 최신
N805844 2024년 2월 5일 스티브 이전의
N805844 2024년 12월 28일 마이크 이전의
N808961 2024년 1월 4일 조 최신
N805961 2024년 1월 3일 조 이전의
N805961 2024년 1월 2일 마이크 이전의
N805961 2024년 1월 2일 스티브 이전의

나는 송장 번호를 식별하기 위해 도우미 열 작업을 한 다음 단일 숫자(완료까지의 일수)를 산출해야 하는 모든 날짜 값을 빼려고 했습니다. 도움이 될 수 있기 때문에 "최신"과 "이전"을 고려하고 있습니다.

다른 시트의 최대/최소 공식에 대해 생각해 보았지만 어떻게 그렇게 할 수 있는지 잘 모르겠습니다.

그런 다음 이 테이블을 회전하여 완료까지 남은 날짜를 표시하겠습니다. 어떤 도움이라도 주시면 감사하겠습니다.

답변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)

여기에 이미지 설명을 입력하세요

파워 쿼리를 사용하려면 다음 단계를 따르세요.

  • 먼저 소스 범위를 테이블로 변환하고 그에 따라 이름을 지정합니다. 이 예에서는 이름을 다음과 같이 지정했습니다.Table1
  • Data다음으로 Tab --> Get & Transform Data--> Get Data--> From Other Sources--> 에서 빈 쿼리를 엽니다.Blank Query
  • 위의 내용을 Power Query사용하면 이제 Home탭 --> --> 보이는 내용을 제거하여 Advanced Editor다음을 붙여넣은 후 창을 열 수 있습니다.M-CodeDone
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필요한 출력을 생성하고 후자는 결과를 어디에 배치할지 묻는 창을 표시합니다.

관련 정보