[Excel]運賃表をデータにして使いやすくする

@tanuki_phoenix さんの記事の問題を私もやってみた。

電車の運賃表とかでよく見る以下のような形式から運賃を出力するツールをExcelで作りたい。
そのまま運賃表から関数を使って作ろうとすると複雑になりそうだったのでPowerQueryでデータ変形して使いやすくしてみた。
ただしPowerQueryの変形が簡単とは言っていない。

ゴール

駅を2つ指定して運賃を表示する。

方針

PowerQueryはピボットの解除ができるので、そこまでもっていけば簡単かなと思った。（この方法が最適なのかは疑問）

↓こんな感じの表を意識すればゴールが近そう。

やり方

駅名を集める

PowerQueryに運賃表を取り込むと見出しが「列1~列N」となることを利用して、インデックスを付けてから駅名を取得する。
その後、インデックス列は要らないので排除。

インデックス列の追加 = Table.AddIndexColumn(ソース, "インデックス", 1, 1),
地点名列を追加 = Table.AddColumn(インデックス列の追加, "カスタム", each Record.Field(_, "列"&Number.ToText([インデックス]))),
インデックス列の削除 = Table.RemoveColumns(地点名列を追加,{"インデックス"}),

ソース

↓
インデックス列の追加
![image.png]
(https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/97601/e8db4d58-d035-e963-b41b-12201b1b8749.png)
↓
地点名列を追加

↓
インデックス列の削除

転置してまた駅名を集める

同様に行列を入れ替えてまた駅名を集める。

転置 = Table.Transpose(インデックス列の削除),
インデックス列の追加2 = Table.AddIndexColumn(転置, "インデックス", 1, 1),
地点名列を追加2 = Table.AddColumn(インデックス列の追加2, "カスタム", each Record.Field(_, "Column"&Number.ToText([インデックス]))),
インデックス列の削除2 = Table.RemoveColumns(地点名列を追加2,{"インデックス"}),

転置

↓
インデックス列の追加2

↓
地点名列を追加2

↓
インデックス列の削除2

Errorを置換

Errorの位置がちょうど見出しになるので「地点1」に置換する。

Errorの見出しを変更 = Table.ReplaceErrorValues(インデックス列の削除2, {{"カスタム", "地点1"}}),

Errorの見出しを変更

ヘッダーを変える

最後の行をヘッダにするため、一度反転させてから1行目をヘッダーに昇格する。

反転された行 = Table.ReverseRows(Errorの見出しを変更),
昇格されたヘッダー数 = Table.PromoteHeaders(反転された行, [PromoteAllScalars=true]),

反転された行

↓
昇格されたヘッダー数

ピボットを解除してゴミの行を削除

「地点1」以外のピボットを解除するといい感じのデータが出来上がる。
あとは運賃が数字の行だけ抽出する。

ピボット解除された列 = Table.UnpivotOtherColumns(昇格されたヘッダー数, {"地点1"}, "地点2", "運賃"),
運賃のレコードのみにするフィルタ = Table.SelectRows(ピボット解除された列, each (Value.Type([運賃]) = type number)),

ピボット解除された列

↓
運賃のレコードのみにするフィルタ

地点1と地点2を入れ替えたテーブルを結合

反対方向の運賃を作るため、地点1と地点2を入れ替えたテーブルを結合する。

地点を逆転させたテーブルを結合 = Table.Combine({運賃のレコードのみにするフィルタ,Table.RenameColumns(運賃のレコードのみにするフィルタ,{{"地点1", "地点2"}, {"地点2", "地点1"}})})

地点を逆転させたテーブルを結合

これでPowerQueryでの変換は完成。
全てをつなげたものがこちら。

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    インデックス列の追加 = Table.AddIndexColumn(ソース, "インデックス", 1, 1),
    地点名列を追加 = Table.AddColumn(インデックス列の追加, "カスタム", each Record.Field(_, "列"&Number.ToText([インデックス]))),
    インデックス列の削除 = Table.RemoveColumns(地点名列を追加,{"インデックス"}),
    転置 = Table.Transpose(インデックス列の削除),
    インデックス列の追加2 = Table.AddIndexColumn(転置, "インデックス", 1, 1),
    地点名列を追加2 = Table.AddColumn(インデックス列の追加2, "カスタム", each Record.Field(_, "Column"&Number.ToText([インデックス]))),
    インデックス列の削除2 = Table.RemoveColumns(地点名列を追加2,{"インデックス"}),
    Errorの見出しを変更 = Table.ReplaceErrorValues(インデックス列の削除2, {{"カスタム", "地点1"}}),
    反転された行 = Table.ReverseRows(Errorの見出しを変更),
    昇格されたヘッダー数 = Table.PromoteHeaders(反転された行, [PromoteAllScalars=true]),
    ピボット解除された列 = Table.UnpivotOtherColumns(昇格されたヘッダー数, {"地点1"}, "地点2", "運賃"),
    運賃のレコードのみにするフィルタ = Table.SelectRows(ピボット解除された列, each (Value.Type([運賃]) = type number)),
    地点を逆転させたテーブルを結合 = Table.Combine({運賃のレコードのみにするフィルタ,Table.RenameColumns(運賃のレコードのみにするフィルタ,{{"地点1", "地点2"}, {"地点2", "地点1"}})})
in
    地点を逆転させたテーブルを結合

スライサーの追加

リストで駅を指定できるようにしてもよいけど、せっかくだからスライサーで選択できるようにしてみた。
PowerQueryから読み込んだ表で地点1と地点2のスライサーを生成するだけでよい。

スライサーで選択している駅の組み合わせから運賃を抽出する

スライサーで選択中のレコードを特定するために式を仕込む。
SUBTOTALを使った式の列を追加する。

選択レコード=SUBTOTAL(103,[@運賃])

こうしておくと、スライサーで絞り込んだ時に該当するレコードの値は1になって該当しないレコードの値は0になる。

レコードが1つに絞り込まれたことを判定するために、どこかに判定用の式を入れておく。

=SUM(運賃表[選択レコード])=1

最後にINDEX+MATCHで運賃を持ってくる。

=IF(運賃テーブル!G1,INDEX(運賃表[運賃],MATCH(1,運賃表[選択レコード],0)),"")

駅が増えて運賃表が変わってもテーブルを更新すれば問題なく使えるはず。

以上。

PowerQueryの部分はもっと最適化できると思うけど、ボタンポチポチからまだ抜け切れていなかったりテーブルで考えてしまったりするので今はこれが精いっぱい。