地下鉄の料金表

下の表は、某県某市の地下鉄の料金表です。

送信者 OpenOffice
このサンプルファイルは、calc123.odsをSKYDRIVEからダウンロードしてください。

ここの地下鉄は、3駅以上乗ると200円。2駅までなら100円という設定です。単純明快。
この料金表の数式は長く見えますが、料金と同様に構造は単純です。
=IF(出発駅のセルの位置-到着駅のセルの位置=0;"";IF(ABS(出発駅のセルの位置-到着駅のセルの位置)>=3;200;100))


出発駅が、B3:B10の範囲内で上から何行目に入力されているか。そして、到着駅が同じ範囲内で上から何行目に入力されているか。これを調べて引き算をします。ここでゼロが返されたら当駅同士なので空白にします。
ゼロでない場合は、引き算の結果の絶対値を、出発駅から到着駅までの駅の数として、料金計算の根拠にします。
駅の数が2駅までなら100円。3駅以上なら200円にします。

この数式の中で重要な役割を果たす関数は、MATCH関数とABS関数です。

関数名：MATCH関数
書式：=MATCH(検査値;検査範囲;検査タイプ)
何ができる：検査値を、検査範囲のなかで何番目のセルにあるかを返します。
平たく書くと：=MATCH($B3;$B$3:$B$10;0)　で、B3セルの値が、B3：B10の範囲で何番目のセルにあるかを返します。この場合、1が返されます。
　＊MATCH関数の検査タイプで0で指定すると、検査値と一致するものを検索します。

関数名：ABS関数
書式：=ABS(検査値)
何ができる：検査値をの絶対値を返します。
平たく書くと：=ABS(-3)　で3が返されます。　=ABS(3)　でも3が返されます。

例えば、出発駅A駅から到着駅C駅の場合
A駅は=MATCH($B3;$B$3:$B$10;0)で1が返されます。
C駅は=MATCH(E$2;$B$3:$B$10;0)で3が返されます。
1-3は、ゼロではないので、この値の絶対値を求めます。
=ABS(MATCH($B3;$B$3:$B$10;0)-MATCH(E$2;$B$3:$B$10;0))　で2が返されます。

絶対値を求めるのは、
=MATCH($B3;$B$3:$B$10;0)-MATCH(E$2;$B$3:$B$10;0)の結果がマイナス2になるためです。これでは料金を徴収できません。

で、これまでの数式で得られた結果をIF関数に当てはめて、3以上なら200を返し、そうでなければ100を返すことにします。
=IF(MATCH($B3;$B$3:$B$10;0)-MATCH(E$2;$B$3:$B$10;0)=0;"";IF(ABS(MATCH($B3;$B$3:$B$10;0)-MATCH(E$2;$B$3:$B$10;0))>=3;200;100))


さらに表を見やすくするために、100円区間と200円区間を色で区分します。
ツールバーの書式＞条件付きの書式設定でセルの値が200なら薄い黄色で塗りつぶします。
ツールバーの書式＞条件付きの書式設定でセルの値が空白なら薄い青色で塗りつぶします。

塗りつぶしを効率的に行なうには、

  1. 1つのセルに塗りつぶしの設定を行なった後、設定を行なったセルをコピーします。
  2. 同様の書式を適用したいセルをマウスで選択します。
  3. ツールバーの編集＞形式を選択して貼り付けをたどり、形式を選択する画面で書式にチェックを入れてほかのチェックを外します。
  4. OKをクリックします。

この記事をクリップ！