東北自動車道利用者必見! 『東北自動車道走行シミュレーション』


匠への技

 「匠への技」第7弾は、東北自動車のインターチェンジ、ジャンクション、パーキングをキーにポイントからポイントの距離を求め走行速度から所要時間を求めるシミュレーションの作表です。 
表題には、東北自動車道利用者の方のためのように記述していますが、このシナリオに至るまでの経緯があります。
複数の方々からOFFSET関数の使い方を問われており、この「匠への技」でおつなぎしたいと思っていました。やっと実現です。今回の目玉「OFFSET関数」をじっくりご鑑賞してください。
尚、東北自動車道のポイント間の距離については間違っていないと思いますが実際ご使用になる場合はご検証下さい。
もし、間違っている箇所を散見しましたらご連絡下さい。


 画面デザイン       ブック名:takumivol-007.xls   シート名:走行SIM

 セル説明へJUMP 川口JCT方面から   1:C5  2:C6  3:D5  4:B8  5:B9  6:B10
             青森方面から       ※川口JCT方面と同様になり説明は省略しています。
  

■設計の足かせ
@距離テーブルのセット
 川口JCT〜青森IC間とその逆方向となる青森IC〜川口JCTまでの2つのテーブルを用意しています。行きと帰りと考えてください。ポイント毎に距離を登録していますがその距離は前のポイントからの距離となります。よって川口JCT〜青森ICの開始地となる川口JCTの前のポイントはありませんので無記入となります。(単位:qを登録している)青森IC〜川口JCTの開始値青森ICも同様に無記入となっています。
例えば、浦和IC〜岩槻ICまでの距離は岩槻ICの行の5.7qとなります。


A出発地・到着地の指定
 コンボボックスを用意しました。(定義方法は、 ここ  を参照)
但し、102ポイントにもなるとリスト内から選択するのは容易ではなくC列に相対番号を入力する欄を設けました。
その指標番号はテーブルのG列となります。コンボボックスからの選択もしくは番号入力としました。


 使用する関数  
  1. IF(論理式,真の条件,偽の条件)   
  2. TEXT(値,表示形式)
  3. SUM(数値1,数値2,...)
  4. AND(論理式1, 論理式2, ...)
  5. OFFSET(基準,行数,列数,高さ,幅)
・・・・・6回目登場
・・・・・2回目登場
・・・・・3回目登場
・・・・・2回目登場
・・・・・初回


 解説
川口JCT方面から の定義説明
  1. セルC5 出発点                                            <画面デザインへ>
    川口JCT〜青森ICのテーブルから 出発地の入力。  (コンボボックスOR相対値)

  2. セルC6 到着点                                            <画面デザインへ>
    川口JCT〜青森ICのテーブルから 到着地の入力。  (コンボボックスOR相対値) 

  3. セルD5 入力チェック                                         <画面デザインへ>
    入力拠点のチェック。    出発地<到着地  出発点より到着点の方が前のポイントの時入力エラーを返す。
    関数式  =IF(AND(C5<>"",C6<>""),IF(C5>=C6,"入力エラー",""),"")
    • AND関数 AND(C5<>"",C6<>"")  は出発点セルC5および到着点セルC6に入力されているか否かの判断です。両方入力されているときTRUEとなり次の式が適用されます。FALSEの時は "" を代入します。
      TRUEの時、セルC5がC6より先の地点もしくは同地点かを判断しTRUEの時は "入力エラー" を代入し、FALSEの時は "" を代入します。

  4. セルB8 走行距離                                          <画面デザインへ>
    入力セルC5からC6までの走行距離を計算します。  (ここの関数式がVOL-7の目玉です。)
    関数式  =IF(AND(C5<>"",C6<>""),IF(C5<C6,SUM(OFFSET(I2,C5,0,C6-C5)),""),"")
          
    • SUM()は、「OFFSET関数で指定するセル範囲を参照しその値を集計しなさい」といった数式です。 
      OFFSET(基準,行数,列数,高さ,幅) この関数は、基準から行、列、高さ、幅によりセルをシフトさせ可変的にセル範囲を求めることができます。ここでは、入力した出発点から到着点までのセル範囲を求めています。その求めたセル範囲の区間距離の合計を計算させ延べキロ数を求めます。
       OFFSET(I2,C5,0,C6-C5)を日本語にすると「川口JCTの距離のセルI2を基準として出発点の入力セルC5の相対値で行をシフト(下へ移動)させ開始セルを指定、列は0なのでシフトなし、求めた開始セルから C6-C5 (到着点−出発点) でセルの高さを割り出し、出発点から到着点までのセル範囲を求めなさい」となります。
      SUM(OFFSET(I2,C5,0,C6-C5)) OFFSET関数でセル範囲を求めSUM関数で出発点から到着点までの区間距離を計算させています。

  5. セルB9 走行速度                                          <画面デザインへ>
    走行速度を時速qを単位として入力します。

    セルB10 所要時間                                          <画面デザインへ>
  6. 関数式  =IF(AND(B8<>"",B9<>""),TEXT((B8/B9)/24,"hh:mm:ss"),"")
    小学校の時に学習した式を覚えていますか?道のり÷早さ=時間 この式が基本です。
    式の通り B8/B9 走行距離÷走行速度 で1時間を100とした時間を求めます。次の /24 はExcel上での時間を表すシリアル値を求め、そのシリアル値をTEXT関数で時分秒で表示させています。

青森IC方面から の定義説明
 川口方面からと同様になりますので省略します。


 もひとつ おまけ
シートの保護について
今回のダウンロードブックには、シートの保護を施しています。入力できる箇所は、黄色のセルのみです。
効果は、誤って関数式の組み込まれているセルに入力を行ってもエラーで返される点です。この点も便利ですので機能として覚えて損はしません。
  @入力可のセルをアクティブにします。
  A右クリックを行いショートカットメニューから「セルの書式設定」を選択します。
  B保護タブを選択します。
  C保護タブの「ロック」のチェックを外します。

   

  Dメニューバー「ツール」→「保護」→[シートの保護] を選択しシートを保護します。
    ※シート保護解除もD同様です。パスワードも付すことができますので付した場合は忘れないようにしましょう。
    

以上の操作でセルに対する入力保護を施すことができます。



○「匠への技」VOL-7で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-007.xls  Download file: takumivol-007.lhz