わえなび ワード&エクセル問題集

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【神Excel】Excelで鉄道の時刻表を再現する方法を本気で考えてみた

先日、名古屋鉄道(名鉄)の駅に掲示されている時刻表がExcelで作られているのでは?という内容のツイートが投稿され、ネット上で話題となりました(詳しくは後述)。実際には違っていたようですが、本当に鉄道やバスの時刻表をExcelで作るのであればどのような作業が必要なのでしょうか?

f:id:waenavi:20190404081014j:plain

 

「ネ申エクセル」などと揶揄するのは簡単ですが、よく考えてみれば、Excelの使い方を改めて考えさせられる良問だと思います。そこで、今回は、Twitterで話題となった時刻表をExcelで再現する問題を出題します。

問題

次のツイートの画像は、名鉄一宮駅の名鉄名古屋方面平日ダイヤの時刻表である。これをExcelで作成するのに必要な作業は何か、述べなさい。また、実際にExcelで時刻表を作成し、フッターの左側にファイル名を挿入しなさい。

togetter.com

 

これが完成イメージです。ただし、下部の標準停車駅案内を除きます。

f:id:waenavi:20190404160051j:plain

 

解説

1.事件の概要

名古屋鉄道の名鉄一宮駅で撮影された時刻表の下(フッター)に、Excelのファイルであることを表す拡張子「XLS」が記載されていたことから、この時刻表がExcelで作られているのではないか?神Excelか?などと話題になりました。しかし、デイリースポーツ(神戸新聞)の記者が名鉄広報部に確認したところによると、この時刻表は、名鉄独自のシステムで作った時刻表をExcelに貼り付けて印刷しているだけであるとのことでした。残念ながら「神Excel」ではありませんでした。

www.daily.co.jp

2.名鉄について

次の図は、名鉄の名古屋本線のうち準急急行が停車する駅を示しています(この図をExcel方眼紙で作ることはそんなに難しいことではありません)。電車の種類としては、ミュースカイ(名鉄空港特急)、快特、特急、快急、急行、準急と、普通電車のあわせて7種類の種別があります。問題となっている名鉄一宮駅は左から4番目、名鉄名古屋方面は右方向です。

f:id:waenavi:20190404082006j:plain

 

公式の時刻表はこちらをご覧ください。HTMLです。

名鉄一宮駅発時刻表(平日:月~金)
https://www.meitetsu.co.jp/ekibetsu/timetable/timetable/tt122821.html

 

3.帳票を作る前にリストとマスタを作れ!

以前、当サイトにおいて、帳票とリストは分けて考えることについて解説しました。時刻表のように列幅や行の高さを微調整しなければならない帳票を作る場合、帳票を先に作ってはいけません。いったん、Excelでリストを作って、それをもとに帳票を作るべきです。

 

ところで、実際に運行されている電車にはそれぞれ固有の番号が付けられていて(これを専門用語では列車番号と言うらしい)、その電車がそれぞれの駅で何時何分何秒に発車するかという発車時刻が決められています。当然、このようなデータは鉄道会社のデータベースシステムに保存されているはずです。時刻表を作るには、電車の運行システムから次のような項目を抽出する必要があります。

  • ダイヤ(路線・上り下り・平日土日)
  • 列車番号
  • 停車駅(当駅止まりを除く)と発車時刻
  • 種別(特急・急行・快速など)
  • 終点の駅

リスト形式にします。なお、列車番号は仮の番号にしています。

f:id:waenavi:20190404083040j:plain

 

駅や種別はコード化して、マスタを別に作ります。これを正規化といいます。

f:id:waenavi:20190404083113j:plain

 

さらに、名鉄では標準的な停車駅とは異なる、変則的な停車、または途中で種別が変わるなどの例外を「記事表示」として表します。

f:id:waenavi:20190404083319j:plain


これもコード化して、マスタを作っておきます。

f:id:waenavi:20190404083355j:plain

 

4.時刻表に実際に表示する文字列を作っておく

運行システムに保存されている大量のデータのうち、名古屋方面平日ダイヤで名鉄一宮駅(NH50)を発車する電車218本のデータを抽出したと仮定します。ちなみに、このExcelは、上の公式の時刻表(HTML)から抽出したものです。

f:id:waenavi:20190404083514j:plain

f:id:waenavi:20190404083548j:plain

 

マスタのうち、駅の一覧に「行先」という名前を付けておきます(マスタ表を選択して名前を入力する)。

f:id:waenavi:20190404083801j:plain

 

K列~O列にそれぞれ次のような数式を入れます。なお、このシートのシート名を「data」としています。

  • K:=D2*100+COUNTIF($D$2:D2,D2)
  • L:=TEXT(E2,"00")
  • M:=H2&I2
  • N:=VLOOKUP(G2,行先,3,0)
  • O:=F2

f:id:waenavi:20190404083915j:plain

 

それぞれ次のような意味です。

  • K:リストのデータを縦横2次元の表にするために行列番号を求めておく
  • L:1桁の「分」を2桁表示にする
  • M:記事表示
  • N:行先の駅名
  • O:種別コードは色を変えるときに使う

また、タイトルの部分と時刻表の右下に小さく表示する最終ダイヤ改正日を入力しておきます。

f:id:waenavi:20190404084135j:plain

 

5.帳票を作る

(1)2重の枠

まず、写真の時刻表は2重の枠で囲まれています。この枠のため、左右の端に2列、上下の端に2行が必要です。

f:id:waenavi:20190404084309j:plain

 

(2)何列必要か

「分」の右に記事表示、下に行先表示をします。さらに、間隔のため上に1行・左に1列必要です。したがって、1本の電車につき3行x3列となります。

f:id:waenavi:20190404084343j:plain

 

1時間に20本の時刻を入力できるよう60列(20x3)用意し、列幅を調整します。

f:id:waenavi:20190404084502j:plain

 

(3)タイトル

先ほど入力したデータを参照しながら、タイトル部分を作ります。

f:id:waenavi:20190404084555j:plain

 

(4)複合参照

C列には縦向きに、5、6、7、・・・、22、23、0と入力しています。また、10行目には1~20の数値を入力しています。「=$C12*100+E$10」とすると501になります。これは5時台の1本目の電車という意味です。

f:id:waenavi:20190404084825j:plain

 

この「501」を検索値とするVLOOKUPで、分、記事表示、行先表示を求めます。

  • 分: =IFERROR(VLOOKUP(E11,data!$K:$O,2,0),"")
  • 記事表示: =IFERROR(VLOOKUP(E11,data!$K:$O,3,0),"")
  • 行先表示: =IFERROR(VLOOKUP(E11,data!$K:$O,4,0),"")

f:id:waenavi:20190404084957j:plain

 

時刻の数字(時と分)は念のため、「縮小して全体を表示する」にしておきます。

f:id:waenavi:20190404085034j:plain

 

また、記事表示は縦書きにします。

f:id:waenavi:20190404085137j:plain

 

同様に右上に種別コードを求めます。

  • 種別コード: =IFERROR(VLOOKUP(E11,data!$K:$O,5,0),"")

f:id:waenavi:20190404085213j:plain

 

横向きにオートフィルをして正しく表示されることを確認します。

f:id:waenavi:20190404085257j:plain

 

(5)条件付き書式

条件付き書式を設定します。右上の種別コードがAであれば、赤い四角に赤い文字、白の塗りつぶしです。なお、この種別コードは相対参照です。

f:id:waenavi:20190404085409j:plain

 

同様にB~Fを設定します。

f:id:waenavi:20190404085457j:plain

 

縦横2方向にオートフィルをします。検索値を複合参照にしておくことによって、縦横にオートフィルをするだけですべての時刻が表示されます。また、条件付き書式を相対参照にすることによって、正しく色が変わります。

f:id:waenavi:20190404085537j:plain

 

行の高さを狭くします(文字の色を白色にしておく)。

f:id:waenavi:20190404085617j:plain

 

セルを結合して最終改正日(2019.03.16)を入力します。

f:id:waenavi:20190404085729j:plain

 

1行おきに塗りつぶしの色をグレーにします。

f:id:waenavi:20190404160231j:plain

 

下の部分は入力するだけです。

f:id:waenavi:20190404160319j:plain

f:id:waenavi:20190404085854j:plain

 

6.フッターの設定

名前を付けて保存します。

f:id:waenavi:20190404090309j:plain

 

最後に、フッターの左側にファイル名を挿入して完成です。

f:id:waenavi:20190404090459j:plain

 

f:id:waenavi:20190404090503j:plain

 


 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]