Help us understand the problem. What is going on with this article?

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

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

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

ゴール

駅を2つ指定して運賃を表示する。
運賃計算2.gif

方針

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

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

やり方

駅名を集める

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

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

ソース
image.png

インデックス列の追加
image.png

地点名列を追加
image.png

インデックス列の削除
image.png

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

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

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

転置
image.png

インデックス列の追加2
image.png

地点名列を追加2
image.png

インデックス列の削除2
image.png

Errorを置換

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

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

Errorの見出しを変更
image.png

ヘッダーを変える

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

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

反転された行
image.png

昇格されたヘッダー数
image.png

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

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

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

ピボット解除された列
image.png

運賃のレコードのみにするフィルタ
image.png

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

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

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

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

これで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のスライサーを生成するだけでよい。

image.png

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

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

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

image.png

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

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

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

image.png

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

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

image.png

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

以上。

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

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away