Sub 部門集計()
    Dim mCode As Variant
    Dim formulaText As String
    Dim csvPath As String
    Dim xlsxPath As String
    Dim ws As Worksheet
    Dim lo As ListObject

    csvPath = ThisWorkbook.Path & "\7-8-会計データ.csv"
    xlsxPath = ThisWorkbook.Path & "\7-8-部門マスター.xlsx"

    mCode = Array( _
        "let", _
        "    会計データ = Csv.Document(File.Contents(""" & Replace(csvPath, "\", "\\") & """), [Delimiter="","", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),", _
        "    ヘッダーの昇格 = Table.PromoteHeaders(会計データ, [PromoteAllScalars=true]),", _
        "    型変更 = Table.TransformColumnTypes(ヘッダーの昇格,{{""伝票日付"", type date}, {""伝票番号"", type text}, {""部門コード"", type text}, {""勘定科目コード"", type text}, {""勘定科目"", type text}, {""金額"", Int64.Type}}),", _
        "    部門マスター = Excel.Workbook(File.Contents(""" & Replace(xlsxPath, "\", "\\") & """), null, true),", _
        "    部門マスター_Sheet = 部門マスター{[Item=""部門マスター"",Kind=""Sheet""]}[Data],", _
        "    部門マスター_ヘッダー = Table.PromoteHeaders(部門マスター_Sheet, [PromoteAllScalars=true]),", _
        "    部門マスター_型変更 = Table.TransformColumnTypes(部門マスター_ヘッダー,{{""部門コード"", type text}, {""部門名"", type text}}),", _
        "    マージ = Table.NestedJoin(型変更, {""部門コード""}, 部門マスター_型変更, {""部門コード""}, ""7-8-部門マスター"", JoinKind.LeftOuter),", _
        "    展開 = Table.ExpandTableColumn(マージ, ""7-8-部門マスター"", {""部門名""}, {""部門名""}),", _
        "    グループ化 = Table.Group(展開, {""部門コード"", ""部門名""}, {{""金額合計"", each List.Sum([金額]), Int64.Type}}),", _
        "    並べ替え = Table.Sort(グループ化,{{""部門コード"", Order.Ascending}})", _
        "in", _
        "    並べ替え" _
    )

    formulaText = Join(mCode, vbCrLf)

    ThisWorkbook.Queries.Add _
        Name:="部門集計", _
        Formula:=formulaText

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Set lo = ws.ListObjects.Add( _
        SourceType:=0, _
        Source:=Array( _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;" & _
            "Data Source=$Workbook$;" & _
            "Location=部門集計;" & _
            "Extended Properties=""""" _
        ), _
        Destination:=ws.Range("A1") _
    )

    lo.Name = "T_部門集計"

    With lo.QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [部門集計]")
        .Refresh BackgroundQuery:=False
    End With

    lo.ListColumns("金額合計").DataBodyRange.NumberFormatLocal = "#,##0"

    ThisWorkbook.Queries("部門集計").Delete
End Sub