神カレンダーとは
悲しいかな、日付に相当する部分がこうなってるExcelに遭遇することが、稀によくあります...
| 年月 | 日 |
|---|---|
| 11 | |
| 12 | |
| ... | |
| 19 | |
| 2020年4月 | 20 |
| 21 | |
| ... | |
| 29 | |
| 30 | |
| 1 | |
| 2 | |
| ... | |
| 14 | |
| 2020年5月 | 15 |
| 16 | |
| ... | |
| 30 | |
| 31 | |
| 1 | |
| 2 | |
| ... | |
| 9 | |
| 2020年6月 | 10 |
| 11 | |
| ... | |
| 20 |
年月や日は、書式設定でそうなっているのではなく、それぞれ文字列と数値で格納されています。
人間が読む分には問題ないExcelですが、データとして処理する際の苦しみポイントは、
- 年月の文字列は、その月内の特定の日の隣にあるわけでなく、「真ん中らへんのどこか」に入力してある。
- カレンダー開始日、終了日ともに月の途中の場合がある。
といったところでしょうか。
年月が必ずその月内の先頭の日の隣に入力してあれば、「下方向にフィル」でなんとかできるので、セルが結合されているほうが良いという珍しい(?)ケースでもあります。
神カレンダーから日付を導出する
サンプル
let
GodCalendar = #table(
type table [年月 = text, 日 = Int64.Type],
{
{null, 11}, {null, 12}, {null, 13}, {null, 14},
{null, 15}, {null, 16}, {null, 17}, {null, 18}, {null, 19}, {"2020年4月", 20}, {null, 21},
{null, 22}, {null, 23}, {null, 24}, {null, 25}, {null, 26}, {null, 27}, {null, 28},
{null, 29}, {null, 30},
{null, 1}, {null, 2}, {null, 3}, {null, 4}, {null, 5}, {null, 6}, {null, 7},
{null, 8}, {null, 9}, {null, 10}, {null, 11}, {null, 12}, {null, 13}, {null, 14},
{"2020年5月", 15}, {null, 16}, {null, 17}, {null, 18}, {null, 19}, {null, 20}, {null, 21},
{null, 22}, {null, 23}, {null, 24}, {null, 25}, {null, 26}, {null, 27}, {null, 28},
{null, 29}, {null, 30}, {null, 31},
{null, 1}, {null, 2}, {null, 3}, {null, 4}, {null, 5}, {null, 6}, {null, 7},
{null, 8}, {null, 9}, {"2020年6月", 10}, {null, 11}, {null, 12}, {null, 13}, {null, 14},
{null, 15}, {null, 16}, {null, 17}, {null, 18}, {null, 19}, {null, 20}
}
),
年月日のnullではない最初と最後の要素取り出す = List.Transform(
{GodCalendar[年月], GodCalendar[日]},
each
let
rn = List.RemoveNulls(_),
fl = {List.First(rn), List.Last(rn)}
in
fl
),
それらをガッチャンコして日付に変換する = List.Transform(
List.Zip(年月日のnullではない最初と最後の要素取り出す),
each Date.FromText(_{0} & Number.ToText(_{1}, "0日"))
),
開始日と終了日がゲットできたのでリストにする =
let
start = それらをガッチャンコして日付に変換する{0},
end = それらをガッチャンコして日付に変換する{1}
in
List.Dates(
start,
Duration.Days(end - start) + 1,
#duration(1, 0, 0, 0)
),
元テーブルに追加 = Table.AddColumn(
GodCalendar,
"日付",
each Function.ScalarVector(
type function(dummy as any) as any,
(inputTable) => 開始日と終了日がゲットできたのでリストにする
)([0]),
type date
)
in
元テーブルに追加
結果
注意点
今回のサンプルの場合、神カレンダー側に余分な日(同じ日がダブってるなど)がある場合、エラーが出るので気づくことができますが、抜けている場合はエラーが出ません。
最初と最後だけ見てるので、途中の月の表記が間違っていれも問題ないというメリットはありますが、基本的には、神カレンダー側もカレンダーとして矛盾がない必要があり、想定される矛盾の内容によって、対応方法を変える必要がありそうです。
そもそも
「日」の部分の「値」がちゃんと日付になってさえいれば問題ないわけで、再利用しやすいExcelの作り方を地道に啓蒙していくのも大切ですね(職場環境によってはそっちのほうが難しいかもしれませんが...)