☰

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。 柔軟なオプションが 1 つあり、それは を使用することです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()上記の 3 つすべてを結合し、2 番目と 3 番目の変数で減算を行って日数を返します。#(注記：日数には過去の日付がすべて含まれます。同じにしたくない場合は、 を除外します+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
  • Data次に、タブ --> Get & Transform Data--> Get Data--> From Other Sources-->から空のクエリを開きます。Blank Query
  • 上記のようにPower Queryウィンドウが開きます。TabからHome--> 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必要な出力を含む が作成され、後者をクリックすると、結果を配置する場所を尋ねるウィンドウが表示されます。

関連情報