エクセル練習問題
指定日付の有効最新マスタ一覧（履歴マスタ）解答

=VSTACK(M社員[#見出し],
LET(開始日,M社員[開始日],ID,M社員[社員ID],
SORT(FILTER(M社員,開始日=MAXIFS(開始日,ID,ID,開始日,"<="&DATE(2024,12,31))))))
)))

=LET(
tbl,VSTACK(TAKE(M社員[#すべて],1),SORT(DROP(M社員[#すべて],1),4,-1)),
GROUPBY(INDEX(tbl,,1),DROP(tbl,,1),LAMBDA(x,INDEX(x,1,1)),3,0,,INDEX(tbl,,4)<=DATE(2024,12,31))
)

=LET(
tbl,VSTACK(TAKE(M社員[#すべて],1),SORT(DROP(M社員[#すべて],1),4,-1)),
GROUPBY(INDEX(tbl,,1),DROP(tbl,,1),SINGLE,3,0,,INDEX(tbl,,4)<=DATE(2024,12,31))
)

let
    ソース = Excel.CurrentWorkbook(){[Name="M社員"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"社員ID", type text}, {"社員名", type text}, {"住所", type text}, {"開始日", type date}}),
    フィルターされた行 = Table.SelectRows(変更された型, each [開始日] <= #date(2024, 12, 31)),
    グループ化された行 = Table.Group(フィルターされた行, {"社員ID"}, {{"最新のデータ", each Table.Max(_,"開始日"), type record}}),
    展開された最新のデータ = Table.ExpandRecordColumn(グループ化された行, "最新のデータ", {"社員名", "住所", "開始日"}, {"社員名", "住所", "開始日"}),
    並び替えられた列 = Table.ReorderColumns(展開された最新のデータ,{"社員ID", "社員名", "住所", "開始日"})
in
    並び替えられた列

SELECT
    m.社員ID,
    m.社員名,
    m.住所,
    m.開始日
FROM
    M社員 AS m
JOIN
    (SELECT
        社員ID,
        MAX(開始日) AS 最新開始日
    FROM
        M社員
    WHERE
        開始日 <= '2024-12-31'
    GROUP BY
        社員ID) AS latest
ON
    m.社員ID = latest.社員ID 
    AND m.開始日 = latest.最新開始日
ORDER BY
    m.社員ID;

SELECT
    社員ID,
    社員名,
    住所,
    開始日
FROM
    (SELECT
        社員ID,
        社員名,
        住所,
        開始日,
        ROW_NUMBER() OVER (PARTITION BY 社員ID ORDER BY 開始日 DESC) as rn
    FROM
        M社員
    WHERE
        開始日 <= '2024-12-31') AS ranked
WHERE
    rn = 1
ORDER BY
    社員ID;

=LET(
終了日,MINIFS([開始日],[社員ID],[@社員ID],[開始日],">"&[@開始日]),
IF(終了日=0,DATE(2099,12,31),終了日-1))

=VSTACK(M社員[#見出し],
SORT(
FILTER(M社員,
(M社員[開始日]<=DATE(2024,12,31))*(M社員[終了日]>DATE(2024,12,31))
)))