エクセル雑感
非正規化（カンマ区切り）の結合と集計：最適な手法は？

SELECT A.ID,A.B_ID,B.C_IDs,IFNULL(SUM(C.値), 0) AS 値合計
FROM TBLA AS A
LEFT JOIN TBLB AS B
       ON A.B_ID = B.ID
LEFT JOIN TBLC AS C
       ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
      AND (C.終了日 > STRFTIME('%Y/%m/%d', DATE('now')) OR C.終了日 IS NULL)
GROUP BY A.ID,A.B_ID,B.C_IDs

=LET(
TBLA,DROP(A:.B,2),TBLB,DROP(D:.E,2),TBLC,DROP(G:.I,2),
A_ID,TAKE(TBLA,,-1),
B_ID,VLOOKUP(A_ID,TBLB,1),
C_IDs,VLOOKUP(A_ID,TBLB,2),
C_ID,DROP(REDUCE("",C_IDs,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,",")))),1),
m,MAP(C_ID,
      LAMBDA(x,
      LET(C_END,XLOOKUP(x,TAKE(TBLC,,1),DROP(TBLC,,1)),
          YMD,TAKE(C_END,,-1),
          isYMD,IFERROR(OR(YMD="",YMD>TODAY()),FALSE),
          IF(isYMD,TAKE(C_END,,1),0)))
      ),
VSTACK({"ID","B_ID","C_IDs","値合計"},
       HSTACK(TAKE(TBLA,,1),B_ID,C_IDs,BYROW(m,SUM))
      )
)

let
    // 1. ソーステーブルの参照、型変換、現在日付の取得を一括で行う
    CurrentDate = Date.From(DateTime.LocalNow()),
    Source = Excel.CurrentWorkbook(),
    SourceA = Source{[Name="TBLA"]}[Content],
    SourceB = Source{[Name="TBLB"]}[Content],
    TransformedC = Table.TransformColumnTypes(Source{[Name="TBLC"]}[Content], {{"値", type number}, {"終了日", type date}}),
    // 2. TBLCを「先に」フィルタリング・集計し、ルックアップテーブルを作成
    C_Values_Lookup = Table.Group(
        Table.SelectRows(TransformedC, each [終了日] = null or [終了日] > CurrentDate),
        {"ID"}, {{"C_値_合計", each List.Sum([値]) ?? 0, type number}}
    ),
    // 3. TBLAとTBLBを結合し、C_IDsを展開
    ExpandedCIDs = Table.ExpandTableColumn(
        Table.NestedJoin(SourceA, {"B_ID"}, SourceB, {"ID"}, "B_Data", JoinKind.LeftOuter),
        "B_Data", {"C_IDs"}, {"Original_C_IDs"}
    ),
    // 4. C_IDsを分割し、行に展開 (効率化済み)
    SplitAndExpanded = Table.ExpandListColumn(
        Table.AddColumn(ExpandedCIDs, "C_IDs", 
            each if [Original_C_IDs] = null then {} else Text.Split([Original_C_IDs], ","), 
            type list
        ),"C_IDs"
    ),
    // 5. 展開後のテーブルと「縮小したルックアップテーブル」を結合
    ExpandedC = Table.ExpandTableColumn(
        Table.NestedJoin(SplitAndExpanded, {"C_IDs"}, C_Values_Lookup, {"ID"},"C_Data", JoinKind.LeftOuter
        ), "C_Data", {"C_値_合計"}, {"C_値"}
    ),
    // 6. Original_C_IDsごとに集計（nullは0に）
    GroupedAndCalculated = Table.Group(
        ExpandedC, 
        {"ID", "B_ID", "Original_C_IDs"}, 
        { {"値合計", each List.Sum([C_値]) ?? 0, type number} }
    ),
    // 7. 最終的な列の整理と並び替えをまとめて行う
    FinalOutput = Table.Sort(
        Table.RenameColumns(
            Table.SelectColumns(GroupedAndCalculated, {"ID", "B_ID", "Original_C_IDs", "値合計"}),
            {{"Original_C_IDs", "C_IDs"}}
        ),
        {{"ID", Order.Ascending}}
    )
in
    FinalOutput

Public Sub CSV_JOIN()
    Dim ws As Worksheet:        Set ws = ActiveSheet
    Dim tA As ListObject:       Set tA = ws.ListObjects("TBLA")
    Dim tB As ListObject:       Set tB = ws.ListObjects("TBLB")
    Dim tC As ListObject:       Set tC = ws.ListObjects("TBLC")
    
    Dim tA_ID As Range:         Set tA_ID = tA.ListColumns("ID").DataBodyRange
    Dim tA_B_ID As Range:       Set tA_B_ID = tA.ListColumns("B_ID").DataBodyRange
    Dim tB_ID As Range:         Set tB_ID = tB.ListColumns("ID").DataBodyRange
    Dim tB_C_IDs As Range:      Set tB_C_IDs = tB.ListColumns("C_IDs").DataBodyRange
    Dim tC_ID As Range:         Set tC_ID = tC.ListColumns("ID").DataBodyRange
    Dim tC_Value As Range:      Set tC_Value = tC.ListColumns("値").DataBodyRange
    Dim tC_EndDate As Range:    Set tC_EndDate = tC.ListColumns("終了日").DataBodyRange
    Dim tA_Rows As Long:        tA_Rows = tA_ID.Rows.Count
    Dim oArray() As Variant:    ReDim oArray(1 To tA_Rows, 1 To 4)
    Dim i As Long, j As Long, k As Long
    Dim sB As String, sC As String, v As Variant, tCID As Variant, sumV As Double
    For i = 1 To tA_Rows
        sB = CStr(tA_B_ID.Cells(i).Value): sC = "": sumV = 0
        
        For j = 1 To tB_ID.Rows.Count
            If CStr(tB_ID.Cells(j).Value) = sB Then
                sC = CStr(tB_C_IDs.Cells(j).Value)
                Exit For
            End If
        Next
        If sC <> "" Then
            v = Split(sC, ",")
            For Each tCID In v
                For k = 1 To tC_ID.Rows.Count
                    If CStr(tC_ID.Cells(k).Value) = tCID Then
                        Dim endDate As Variant: endDate = tC_EndDate.Cells(k).Value
                        Dim valueC As Variant: valueC = tC_Value.Cells(k).Value
                        
                        If IsNumeric(valueC) Then
                            If endDate = "" Then
                                sumV = sumV + CDbl(valueC)
                            ElseIf IsDate(endDate) Then
                                If CDate(endDate) > Date Then
                                    sumV = sumV + CDbl(valueC)
                                End If
                            End If
                        End If
                        
                        Exit For
                    End If
                Next k
            Next
        End If
        oArray(i, 1) = tA_ID.Cells(i).Value
        oArray(i, 2) = sB
        oArray(i, 3) = sC
        oArray(i, 4) = CLng(sumV)
    Next i
    ws.Range("K2").Resize(UBound(oArray, 1), 4).Value = oArray
End Sub

import pandas as pd
from datetime import date
# A:B, D:E, G:I のデータ範囲を取得
TBLA = xl("A2:B8", headers=True)
TBLB = xl("D2:E10", headers=True)
TBLC = xl("G2:I12", headers=True) 
# 列名の確認と修正
TBLC.columns = ['ID', '値', '終了日']
TBLC['終了日'] = pd.to_datetime(TBLC['終了日'], errors='coerce')
# TBLAの 'B_ID' と TBLBの 'ID' をキーに結合
TBLB.rename(columns={'ID': 'B_ID_KEY'}, inplace=True)
merged_AB = TBLA.merge(TBLB, left_on='B_ID', right_on='B_ID_KEY', how='left').drop(columns=['B_ID_KEY'])
# C_IDsの展開
df_C_split = merged_AB.assign(C_ID=merged_AB['C_IDs'].str.split(',')).explode('C_ID')
# TBLCの結合と条件判定
merged_ABC = df_C_split.merge(TBLC, left_on='C_ID', right_on='ID', how='left')
TODAY = date.today()
# isYMDの判定: 終了日="" (NaT) または 終了日 > TODAY()
merged_ABC['isYMD'] = merged_ABC['終了日'].isna() | (merged_ABC['終了日'].dt.date > TODAY)
# 値の計算：結合で生じるNaNは0
merged_ABC['計算値'] = merged_ABC.apply(
    lambda row: row['値'] if row['isYMD'] else 0,axis=1
).fillna(0)
# 集計と整形：ID_xはTBLAから来たID列
result_summary = merged_ABC.groupby(['ID_x', 'B_ID', 'C_IDs'], as_index=False)['計算値'].sum()
result_summary.rename(columns={'ID_x': 'ID','計算値': '値合計'}, inplace=True)
# 最終結果の DataFrame を出力
result_summary