【PowerQuery】更新するたびにランダムな出社パターンの勤務表を作成
最初に言っておくと、今回の記事は例によって本来PowerQueryでやるようなものではありません。元々Excelワークシート関数でやったことをPowerQueryでもできるかなと思い試しました。ただ、試行錯誤しながらやった中でM関数の使い方等勉強になったところが色々あり、他で活かせるかなと思っています。
（１） やりたいこと
ある月(又は期間)の勤務日程を決める際、一日毎の出社率が決まっており、日ごとに出社する人と在宅勤務する人とに分けたい場合に、決められた条件でランダムに振り分けたいというものです。最終的な形は下図の通りです。
（２） 事前準備
上図右側のような設定入力用のテーブル３つ（メンバー、出社率、基準日）をExcelワークシート上に作成し、それぞれをデータ→テーブル又は範囲から にてPowerQueryエディターに取り込みます。読み込みはすべて「接続専用」とします。
下図は３テーブルを取り込み後のPowerQueryエディターです。
出社率クエリを選択し、値の上で右クリック→ドリルダウンをクリックします。
単一の数値となります。0.5＝Excelワークシート上の50%です。
基準日クエリを選択し、同様にドリルダウンします。
単一の日付となります。
※参考
（３） コード
ここで先に全体のコードを書いておきます。前項までの準備をした後に詳細エディターにそのまま貼付けて出力可能です。詳しくは次の（４）で説明していきます。
let
人数 = List.Count(メンバー[メンバー]),
出社可能人数 = Number.RoundDown( 人数 * 出社率 ),
// ○ = 出社 □ = 在宅
ソース = List.Generate(
()=> [ 日付 = 基準日,
mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数)
],
each [日付] <= Date.EndOfMonth(基準日),
each [ 日付 = Date.AddDays([日付],1) ,
mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数)
]
),
テーブル変換 = Table.FromRecords(ソース),
// 1文字ずつ列を分割、列名はメンバー
mark列の分割 = Table.SplitColumn(テーブル変換, "mark", Splitter.SplitTextByRepeatedLengths(1) , メンバー[メンバー]),
ピボット解除された他の列 = Table.UnpivotOtherColumns(mark列の分割, {"日付"}, "属性", "値"),
// 前ステップの列からテーブルへ 乱数列はList.Random関数にて前ステップのリストカウント分を作成し追加
乱数列を追加_メンバー除く = Table.FromColumns(
{ピボット解除された他の列[日付],
ピボット解除された他の列[値],
List.Random (List.Count(ピボット解除された他の列[日付]))
}
,{"日付","mark","乱数"}
),
// 日付列、乱数列の順で昇順にソート
並べ替えられた行 = Table.Sort(乱数列を追加_メンバー除く,{{"日付", Order.Ascending}, {"乱数", Order.Ascending}}),
削除された列 = Table.RemoveColumns(並べ替えられた行,{"乱数"}),
メンバー追加 = Table.FromColumns(
{ 削除された列[日付],
ピボット解除された他の列[属性],
削除された列[mark]
}
,{"日付","メンバー","mark"}
),
ピボットされた列 = Table.Pivot(メンバー追加, List.Distinct(メンバー追加[メンバー]), "メンバー", "mark")
in
ピボットされた列
（４） 実践・経過説明
PowerQueryエディターでホーム→新しいソース（右端）→ その他のソース → 空のクエリをクリックします。
空のクエリが作成されます。
ホームタブ → 詳細エディター をクリックします。
下図のコードを入力し完了をクリックします。（ステップの追加からひとつずつかいてもＯＫです）
最初の「人数」「出社可能人数」のステップはそれ以下で使用するために定義したものです。出社可能人数の計算は人数に出社率を乗じた結果をラウンドダウンしていますがここは必要に応じて変えてください。
その後の「ソース」のステップが今回ひとつのポイントとなります。List.Generate関数を使ってレコードのリストを作成します。以下説明
List.Generate( ()=> [ 日付 = 基準日, mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数) ], each [日付] <= Date.EndOfMonth(基準日), each [ 日付 = Date.AddDays([日付],1) ,
mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数)
]
)
mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数)
]
)
初期値(initial)
日付 = 基準日（ワークシート上に入力した年月日となります） mark = 出社可能人数分の”○” と 人数から出社可能人数を差し引いた数分の”□”を くっつけたテキスト
をレコードとして設定します。
条件(condition)
Date.EndOfMonth関数を用い、日付を初期値(基準日)の月の月末日以下に設定。
次の値(next)
日付 = 日付+1日 を加算した日付 mark = 出社可能人数分の”○” と 人数から出社可能人数を差し引いた数分の”□”を くっつけたテキスト
をレコードとして設定します。
初期値と次の値で記した、mark の内容はまったく同じです。つまり、日付は基準日からその月の月末日まで、mark はすべての日付で同じ値 のレコードのリストが作成されます。以降の画像を見た方がわかり易いと思います。
下図が先ほどのList.Generateによって作成されたリストです。カーソルを1行目におき下部に中身が表示された状態にしています。日付とmarkが1セットのフィールドとなったレコードのリストで日付が2021/9/1 から2021/9/30 までの30セットできています。
次に、数式バーの左のfx(ステップの追加）をクリックし次の数式を入力します。
Table.FromRecords(ソース)
前ステップのレコードをテーブルに変換する記述です。
次にmark列の値を一文字毎に列を分割し、列名をメンバー名にします。
fxステップの追加をクリックし次の数式を入力します。
Table.SplitColumn(テーブル変換, "mark", Splitter.SplitTextByRepeatedLengths(1) , メンバー[メンバー])
Splitter.SplitTextByRepeatedLengths に1を設定することでmark列の値を1文字ずつ列分割することができました。この結果、下図の通りとなりますがこの時点ではまだランダムな並びにはなっていません。
次に、日付列を選択した状態で、右クリック → その他の列のピボット解除 をクリックします。
下図の通りピボット解除されます。
次に、再びfx(ステップの追加)をクリックし、次の数式を入力します。
Table.FromColumns( {ピボット解除された他の列[日付],
ピボット解除された他の列[値],
List.Random (List.Count(ピボット解除された他の列[日付]))
}
,{"日付","mark","乱数"}
)
Table.FromColumns関数で3つの列からテーブルを作成します。最初の2つは前ステップの日付と値(mark）です。3つ目の列は、List.Randomで作成した乱数のリストです。List.Ramdomの引数で乱数をいくつ作るか指定する必要がありますので前ステップの日付の数分となるよう設定しています。Table.FromColumnsの最後の部分でテーブルの列名をそれぞれ"日付","mark","乱数"と指定しています。結果、下図の通りとなります。メンバー名については後のステップで追加します。
次に日付毎にランダムなmarkの並びにするために日付、乱数列の順番で昇順にソートします。各列毎に並び替えボタンをポチポチするか、もしくはステップの追加で次の数式を入力します。
Table.Sort(乱数列を追加_メンバー除く,{{"日付", Order.Ascending}, {"乱数", Order.Ascending}})
結果的に下図の通り、日付順にmarkがランダムに並んだ状態となります。
乱数列は不要となりますので削除します。
ステップの追加(fx)をクリックして次の数式を入力します。
Table.FromColumns( { 削除された列[日付],
ピボット解除された他の列[属性],
削除された列[mark]
}
,{"日付","メンバー","mark"}
)
少し前のステップと同じTable.FromColumns関数で3つの列からテーブルを作ります。前ステップの日付、mark列にピボット解除した際の属性（メンバー名）を追加する形です。下図の通りとなります。
最後にメンバー列を選択した状態で、変換タブ → 列のピボット をクリックします。
下図の通り値列をmark、値の集計関数は修正しないとしOKをクリックします。
これで完成です。
（５）動作確認
下図はワークシートにテーブルとして読込んだ状態です。テーブル上で右クリックし、更新してみます。
○と□の並びがランダムな並び置き換わりました。
次にメンバーをひとり追加（マイケル）、出社率を30％に変更、基準日を2021/10/1に変更した状態にしてみます。出社可能人数は2名（7名×30%）です。
テーブルを更新すると下図の通り、各項目が追加・変更され、日ごとに〇が２つ、□が5つのマークがランダムに振られた状態に変わりました。
（６）別解 ※２０２１/１０/２９追記
もう少しシンプルな書き方を考えたので追記しておきます。
List.generateは日付のみのリストを作成、その後必要な日数×人数分の日付・メンバー・markのリストを作成してから結合してピボット解除
let
人数 = List.Count(メンバー[メンバー]),
出社可能人数 = Number.RoundDown( List.Count(メンバー[メンバー])*出社率 ),
// 日付のリストを作成
ソース = List.Generate(
()=> 基準日,
each _ <= Date.EndOfMonth(基準日),
each Date.AddDays(_,1)
人数 = List.Count(メンバー[メンバー]),
出社可能人数 = Number.RoundDown( List.Count(メンバー[メンバー])*出社率 ),
// 日付のリストを作成
ソース = List.Generate(
()=> 基準日,
each _ <= Date.EndOfMonth(基準日),
each Date.AddDays(_,1)
),
// 日数×人数分の日付リスト
日付リスト = List.Sort( List.Repeat( ソース,人数)),
日付リスト = List.Sort( List.Repeat( ソース,人数)),
メンバーリスト = List.Repeat(メンバー[メンバー],List.Count(ソース)),
// 1日分のmarkリスト
markリスト_日 = Text.ToList(
Text.Repeat("○",出社可能人数)
&Text.Repeat("□",人数-出社可能人数)
),
// 日数×人数分のランダムmarkリスト
markリスト = List.Repeat(
Table.Sort(
Table.FromColumns(
{ markリスト_日,
List.Random( List.Count(markリスト_日))
} ,{"mark","乱数"})
,{"乱数"}
)[mark] // 乱数列で並び替え後、mark列のみ抜き出し
,List.Count(ソース) // 日付分繰り返し
),
リスト結合 = Table.FromColumns(
{日付リスト,
メンバーリスト,
markリスト
},{"日付","メンバー","mark"}),
ピボットされた列 = Table.Pivot(リスト結合, List.Distinct(リスト結合[メンバー])
, "メンバー", "mark"
)
in
ピボットされた列
