エクセル雑感
顧客ごとの時系列データから直前の履歴を取得する

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    // テーブルのデータ型を適切に設定
    変更された型 = Table.TransformColumnTypes(ソース,{{"顧客", type text}, {"日付", type date}, {"商品", type text}, {"金額", type number}}),

    // 顧客ごとにグループ化し、各グループのテーブルに「前回購入」の情報を追加する関数を定義
    追加されたカスタム = Table.AddColumn(変更された型, "カスタム", each
        let
            現在の行 = _,
            // フィルタリング: 現在の行と同じ顧客で、日付が現在の行よりも古い購入履歴を抽出
            フィルタリング = Table.SelectRows(変更された型, each [顧客] = 現在の行[顧客] and [日付] < 現在の行[日付]),
            
            // ソート: フィルタリングされた履歴を日付で降順に並べ替え（最新の購入が一番上に来るように）
            ソート = Table.Sort(フィルタリング,{{"日付", Order.Descending}}),
            
            // 前回購入の情報を取得
            前回購入 = if Table.IsEmpty(ソート) 
                       then [
                           前回購入日 = "初回購入", 
                           前回商品 = null, 
                           前回金額 = null
                       ]
                       else [
                           前回購入日 = ソート[日付]{0}, // ソート後の一番上の日付
                           前回商品 = ソート[商品]{0},  // ソート後の一番上の商品
                           前回金額 = ソート[金額]{0}   // ソート後の一番上の金額
                       ]
        in
            前回購入
    ),
    
    // カスタム列のレコードを展開し、必要な列名に変更
    展開されたカスタム = Table.ExpandRecordColumn(追加されたカスタム, "カスタム", {"前回購入日", "前回商品", "前回金額"}, {"前回購入日", "前回商品", "前回金額"}),
    
    // 前回購入日のデータ型を調整（日付またはテキストを許可）
    最終的な変更された型 = Table.TransformColumnTypes(展開されたカスタム,{{"前回購入日", type text}, {"前回商品", type text}, {"前回金額", type number}}),

    // 必要に応じて、元の列と前回購入列のみを選択
    選択された列 = Table.SelectColumns(最終的な変更された型,{"顧客", "日付", "商品", "金額", "前回購入日", "前回商品", "前回金額"})
in
    選択された列

WITH RankedSales AS (
    SELECT 顧客,日付,商品,金額,
           LEAD(日付, 1, '初回購入') OVER (PARTITION BY 顧客 ORDER BY 日付 DESC) AS 前回購入日候補,
           LEAD(商品, 1)            OVER (PARTITION BY 顧客 ORDER BY 日付 DESC) AS 前回商品,
           LEAD(金額, 1)            OVER (PARTITION BY 顧客 ORDER BY 日付 DESC) AS 前回金額
    FROM t_sales
)
SELECT 顧客,日付,商品,金額,
       CASE 
           WHEN 前回購入日候補 = '初回購入' THEN '初回購入'
           ELSE CAST(前回購入日候補 AS VARCHAR)
       END AS 前回購入日,
       前回商品,前回金額
FROM RankedSales
ORDER BY 日付 ASC, 顧客 ASC

Sub 前回購入情報を算出()
    
    '--- 1. 変数の宣言 ---
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    '前回購入情報を保持するためのDictionaryオブジェクト
    Dim prevPurchaseData As Object
    Set prevPurchaseData = CreateObject("Scripting.Dictionary")
    
    'Dictionaryから取り出すデータ（配列）を受け止めるための変数
    Dim prevData As Variant
    
    '--- 2. シートと範囲の特定 ---
    Set ws = ThisWorkbook.ActiveSheet
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    
    '--- 3. E1:G1にヘッダーを設定 ---
    ws.Range("E1").Value = "前回購入日"
    ws.Range("F1").Value = "前回商品"
    ws.Range("G1").Value = "前回金額"
    
    '--- 4. データ行を上から順に処理 ---
    For i = 2 To lastRow
        Dim customer As String
        Dim purchaseDate As Date
        customer = ws.Cells(i, "A").Value
        purchaseDate = ws.Cells(i, "B").Value
        
        '--- 5. 前回購入情報があるかチェック ---
        If prevPurchaseData.Exists(customer) Then
            '--- 前回購入情報がある場合 ---
            'Dictionaryから前回購入情報を取得 (Variant型として配列全体を受け取る)
            prevData = prevPurchaseData.Item(customer)
            'E列（前回購入日）に出力
            ws.Cells(i, "E").Value = prevData(1)
            'F列（前回商品）に出力
            ws.Cells(i, "F").Value = prevData(2)
            'G列（前回金額）に出力
            ws.Cells(i, "G").Value = prevData(3)  
        Else
            '--- 前回購入情報がない場合（初回購入） ---  
            ws.Cells(i, "E").Value = "初回購入"
        End If
        
        '--- 6. 現在の購入情報をDictionaryに登録/更新 ---
        '新しい配列に現在の行のデータを格納
        Dim currentData(1 To 3) As Variant
        currentData(1) = purchaseDate
        currentData(2) = ws.Cells(i, "C").Value
        currentData(3) = ws.Cells(i, "D").Value
        
        'Dictionaryを更新
        prevPurchaseData.Item(customer) = currentData
        
    Next i
    
    '--- 7. 整形 ---
    ws.Range("E2:E" & lastRow).NumberFormatLocal = "yyyy/mm/dd"
    ws.Range("G2:G" & lastRow).NumberFormatLocal = "#,##0"
    
    MsgBox "前回購入情報の算出が完了しました。", vbInformation
    
End Sub