Excelでのモノづくりは順を追って考えよう～掲示物の作成～

Excelではいろいろなツールが作れます。
今回は、病院などでよくある、バス時刻表の掲示をする仕組みを考えてみます。

表示するもの

表示は次の通りにしたいと思います。現在時刻と時刻表、あとパソコンでせっかくやるので、次のバス時間を表示するというちょっとインタラクティブなものにします。

このようなものを作るには、本来はプログラムをしなければならないのですが、Excelならとても簡単です。
元データとして次のような時刻表を用意します。

現在時刻を求める

現在時刻を求めましょう。現在時刻はNOW関数で求まります。しかし、NOW関数は現在の日要素も含んでいます。

なので、ここから時間要素のみを取り出します。
日付は1日を1をとしたシリアル値なので、1で割ったあまりを求めればいいのです。
あまりを求めるMOD関数を使います。

=MOD(NOW(),1)

これで、現在時刻が求まります。セルD1求めて時刻表示にしましょう。

次のバス時間を求める

では、この時刻にあったバス時間を求めます。まずは雷町方面の時刻表から該当のものを求めましょう。
今回、よく考えるとVLOOKUP関数が使えることがわかります。
あまりこういうケースはないのですが、検索列をそのまま求める、列数指定が1のVLOOKUP関数が使えそうなのです。近似値一致なのでTRUEですね。

=VLOOKUP(D1,A3:A19,1,TRUE)

これをセルD2に入力し、時刻表示にしましょう。

もう行ってしまったバス時間を求めました。これでは役に立ちません。
VLOOKUP関数の近似値一致は、検索値以下で一番近い値を求めます。つまり、現在時刻以前のバス時間を求めてしまったのですね。
VLOOKUP関数は使えそうにありません。
そこで、INDEX関数とMATCH関数の組み合わせが出てきます。
どうするのか。
MATCH関数で求めた値を工夫します。
MATCH関数でも、検索値以下の値を探しますが、ほしいのはその下の値なので、見つかった位置から+1すれば、ほしい位置の時刻になります。
=INDEX(A3:A19,MATCH(D1,A3:A19,1)+1)
この式をセルD2に上書きする形で入力します。

できていそうです。

これで本当にいいか考える

ここで本当にこれでいいのか考えてみましょう。
例えばMATCH関数で現在時刻がぴったり13:00のとき、13:00ちょうどのバス時刻がヒットするかというと、一つ下にしてしまったので出ません。
そこで、検索値ぴったりの時はその値を出して、そうではない時は上記で作成した計算式を使います。
また、MATCH関数の特性も考えましょう。
もし、早朝の時刻の場合はどうなるでしょうか。
時刻表以前の時間だと、MATCH関数は見つけることができません。
時刻表以後の時間だと、MATCH関数では最後の値を出し続けます。
そこで、時刻表以前の場合は一番初めの時刻、時刻表以後の時間であれば「今日は運行していません」とメッセージを出すようにしましょう。

はじめに不具合を想定する

とても大事な話なのですが、この時点であらゆるケースを想定して不具合が出ないか考えます。この時点でそれが出し切れれば理想なのですが、想定しきれない場合もたくさんあります。この時点でどれだけ考え付くかでスキルのレベルがわかると言っても過言ではありません。気づくかどうかは、作成するものの目的や動作を完全に把握していて、それに使うものにも精通している必要があるからです。はじめは難しいかもしれませんので、一旦思いつくところまで組み立ててみると、ああ、こういうケースもあるなと気付くこともあります。また、実働させてみて初めてわかることもありますので、できるだけ思いつくところまで考えてみて、一旦組み上げて、そこで不具合をチェックする、ということが必要です。

不具合に対処する

検索値ぴったりの場合から対処しましょう。
検索値ぴったりの値を出すのはとても簡単で、VLOOKUP関数の完全一致で1列目を求めればいいのです。

=VLOOKUP(D1,A3:A19,1,FALSE)

この計算式で見つからなかった＝エラーになったら、今までの式にします。IFERROR関数で組み立てます。

=IFERROR(VLOOKUP(D1,A3:A19,1,FALSE),INDEX(A3:A19,MATCH(D1,A3:A19,1)+1))

次に時刻表のはじめの値より検索値が少なければの判定をします。時刻表の列の中で最小値を求めれば一番初めのバス時刻がわかりますが、今回、一番初めのバス時刻はセルA3に必ず入力されています。最終バス時刻はセルA19に入力されています。これを使いましょう。

=IF(D1<A3,A3,IF(D1>A19,”今日は運行していません”,

そうではなかったら上記の計算式です。

=IF(D1<A3,A3,IF(D1>A19,”今日は運行していません”,IFERROR(VLOOKUP(D1,A3:A19,1,FALSE),INDEX(A3:A19,MATCH(D1,A3:A19,1)+1))))

かなり長くなりましたが、このように考えていき、この計算式にたどり着きます。
神社方面の計算式も同様に求め、求めた値を見た目よくレイアウトしましょう。

Excelは自動で変わらない

NOW関数やTODAY関数は、自動では値を変えてくれません。再計算の操作が必要です。F9キーで再計算します。
しかし、この表示を1分ごとに変えたいと言って、1分ごとにF9キーを人間が押すのでは全く役に立ちません。
そこで、いくつかの方法が考えられます。
一つはRPAツールを使うことです。
RPAに、このブックをクリックしてF9キーを1分ごとに押して、と教え込んでおけば自動で人間が行うアクションをやってくれます。
規模は小さいですが、こういうことがRPAの本来の使い方かもしれません。
もう一つはVBAを使う方法で、自動的に1分ごとに再計算するアクションをするプログラムをすればできます。しかし、そのためにはVBAのプログラム方法を知る必要があります。
今回は、この規模ですので、RPAツールを使うには多額のライセンス料がかかります。それだけでもコスト的にもったいないのでVBAですべきでしょう。
すでに他の効率化のためにRPAを導入しているのであれば、そのライセンスを使ってRPAで行ってもいいのかもしれません。
Excelの画面で、Alt+F11を押すと、VBAを操作するエディタVBEが表示されます。
挿入メニューの標準モジュールをクリックすると標準モジュールが追加されます。

これをクリックして、以下のものを打ち込みます。

ここで一旦マクロブックとして保存しておきます。

打ち込んだもののどこかをクリックして、F5キーを押すと、1分ごとに自動的に時間が変わっていきます。

いつまでもこの動作はし続けるので、終了する時はこのブックを閉じます。

まとめ

今回は面白いツールをExcelで作る手順と考え方を紹介しました。
実際にこういうものを作るにはプログラムの知識が必要で、プログラムのわかる方はそれで作成すればいいのですが、プログラムは人によってできたりできなかったりするので、もし、プログラムがどうしても苦手でまだExcelの方が使えるという方はこういうアプローチでツールを作ってもいいと思います。
この記事は、ほぼ私のインストラクションの手法で書いています。こんな感じで教えています。
テーマがあって、それを作成するうえで普通に考えたらこうだけど、動かないからこういう考えでいくということを繰り返していくので、問題解決力が身につくのです。Excelを使った現場で、わりとこういうツールを作ることが多めな現場では、問題解決力が試されます。Excelは何度もやり直しができるので、問題を出す、直すを何度もできます。
一番いいのは、まず一通り作ってみて、それをはじめからもう一度作るときれいな状態で出来上がります。組立の順番がわかってから作成しますから。
何かのプログラム製品を作る場合は、多人数で1つのものを作るので、もっと間違いを少なくしなければいけないのですが、Excelツールの開発は一人で行うことが多いので、自分にしか迷惑がかかりませんので、このような開発手法ができます。