Power Query：正解を含めた候補をランダムに出題する

という訳で前回の数式版に続いてクエリ版。問題をランダムに抽出するだけでなく、正解以外の候補を含めてランダムに出題する。

これが完成形。マクロでやるなら制限時間を付けてボタン選択したら履歴を残して新しい問題を表示させて……みたいなこともできるけど、クエリでやるならこの辺が限界かなと思う。

まずは下準備。数式の時と同じく学年別の漢字配当表を用意する。テーブル名は「T_学年別漢字配当表」でこれも前回と同じ。

★後の条件付き書式で使うので、漢字列と読み列を選択して、名前定義しておこう。

名前の定義
    名前：漢字と読み
    参照範囲：=T_学年別漢字配当表[[漢字]:[読み]]

続いて条件を入力するテーブルを作成する。テーブル名は「T_条件」としておく。

２つのテーブルから、それぞれクエリを作成して「接続のみ」で読み込んでおく。

これで準備は完了。

新規クエリ（出題クエリ）に下記のコードを入力。

出題クエリ
let
    学年 = 条件{0}[学年],
    問題数 = 条件{0}[問題数],
    候補数 = 条件{0}[候補数],
    候補見出し = List.Transform({1..候補数}, Text.From),
    ソース = 学年別漢字配当表,
    行の抽出 = Table.SelectRows(ソース, each [学年]=学年),
    列の選択 = Table.SelectColumns(行の抽出, {"番号", "漢字", "読み"}),
    ランダム抽出 = (l as list, n as number)=> [
        乱数リスト = List.Random(List.Count(l)),
        リスト結合 = List.Zip({乱数リスト, l}),
        リストソート = List.Sort(
            リスト結合, (x, y)=> Value.Compare(x{0}, y{0})
        ),
        リスト編集 = List.Transform(リストソート, each _{1}),
        上位保持 = List.FirstN(リスト編集, n)
    ][上位保持],
    テーブル変換 = Table.FromColumns(
        {ランダム抽出(列の選択[番号], 問題数)},
        type table [番号=Int64.Type]
    ),
    レコード列の追加 = Table.AddColumn(
        テーブル変換, "レコード", each 列の選択{[番号=[番号]]}
    ),
    レコード列の展開1 = Table.ExpandRecordColumn(
        レコード列の追加, "レコード", {"漢字", "読み"}
    ),
    リスト列の追加 = Table.AddColumn(レコード列の展開1, "リスト", each [
        番号 = [番号],
        読み = [読み],
        テーブル = 列の選択,
        抽出 = Table.SelectRows(テーブル, each [番号]<>番号),
        重複削除 = Table.Distinct(抽出),
        ランダムリスト1 = ランダム抽出(重複削除[読み], 候補数-1),
        ランダムリスト2 = ランダム抽出({読み} & ランダムリスト1, 候補数),
        レコード変換 = Record.FromList(ランダムリスト2, 候補見出し)
    ][レコード変換]),
    列の削除 = Table.RemoveColumns(リスト列の追加, {"番号", "読み"}),
    レコード列の展開2 = Table.ExpandRecordColumn(
        列の削除, "リスト", 候補見出し
    )
in
    レコード列の展開2

これをテーブルとして読み込む。

条件テーブルの「学年」「問題数」「候補数」を設定し、出題テーブルを右クリック「更新」してやれば、これでランダムに出題されるようになった。コードの出来は置いといて、形としてはほぼ完成の状態。

後はここに肉付けしていくだけ。とりあえず答え合わせ用の条件付き書式を設定してみる。まずはチェックボックスを挿入する。

リボンに「開発」タブを表示させて、フォームコントロールのチェックボックスを挿入。コントロールの書式設定でリンクするセルを適当に設定する。画像では「G4」にしているけど、邪魔ならカット＆ペーストで移動させればいい。結果の「TRUE/FALSE」が見えないよう、リンクセルの文字色は「白」にしておこう。

このチェックボックスを使って切り替え表示される条件付き書式を設定する。

出題テーブルの左上（A4の「漢字」の左上辺り）をクリックすると「T_出題」の範囲が選択できる。

セル範囲を選択したら、条件付き書式の「新しいルール」を指定。

書式ルールの設定で「数式を指定して、書式設定するセルを決定」を選択し、下段の空欄に下記の数式を入力（上の★に書いている「漢字と読み」の名前定義をお忘れなく）。

=IF($G$4,VLOOKUP($A5,漢字と読み,2,FALSE)=A5,FALSE)

後は「書式」で好きな書式を設定すればＯＫ。

これでチェックボックスをオンオフするとことで書式が切り替わるようになる。

予め正解と思われるセルをマウスで選択してから「答え合わせ」をクリックすると、正誤チェックができる。テスト前に自分で問題作って、ざっとチェックするのには役立つかもね。お手軽だし。

あとついでにやるとしたら更新ボタンを作るくらいだけど、「だったら最初からマクロでやれよ」となりそうなので今回は割愛。