タクシーの仕事で、Excelを使って売り上げを見える化しているツールを紹介しました。このツールでは、お客さんを乗せるたびに売り上げが積み上がっていく様子をグラフ化しているのですが、データを日々ずっと同じシートに連続して記録しているため、いろいろ工夫している点があります。この辺りを中心に、掘り下げて紹介しようと思います。
基本はお客さんを乗せるたびに時刻と料金を記録
データの入力は、お客さんを乗せるたびに行います。乗せた時刻と降ろした時刻、それと料金を記録するのが基本となります。具体的には、以下のようなシートを使っています。
C1列は、単純に1から順に振った番号が入ります。こうすることで、現在まで何回やっているかが一目瞭然となります。計算式を使い、後述するC2列の日付が直前レコードと異なったら1にリセットしますが、基本は直前レコードの値に1加えることにより、自動で値を表示しています。具体的な計算式は、以下のとおりです。
=IF(RC2=R[-1]C2,R[-1]C+1,1)
C2列には日付が入ります。お客さんを乗せるたびに日付を入力するのは面倒ですから、あらかじめ入力された状態にしておきます。先頭の行は当日の日付の値（上記の例だと「2021/9/11」）で、以降の行は、以下の計算式により、1つ上のセルの値としています。
=R[-1]C
日付には、曜日も併記したいので、「セルの表示設定」で「表示形式」を以下のようにしています。「ddd」が曜日に相当しますが、英語表記のため、「[$-ja-JP]」を付加して日本語表記に変換しています。
[$-ja-JP]yyyy/mm/dd ddd
赤い見出しの「出発」と「到着」（C3列とC4列）は、空車時の開始時刻と終了時刻がそれぞれ入ります。青い見出しの「出発」と「到着」（C10列とC11列）は、実車時（お客さんが乗っている状態）の開始時刻と終了時刻です。実車時の「到着」と空車時の「出発」、空車時の「到着」と実車時の「出発」は、それぞれ同時刻であることがほとんどですが、少し異なる場合もあるので別物として扱うようにしています。金額（C12列）には料金が入ります。
24時を過ぎたときの日付と時刻の扱い
通常は、朝8時半過ぎに出庫して、翌朝4時半過ぎに帰庫します。そのため、厳密には記録すべき日付が24時を過ぎると変わります。しかし、これを同じ乗務日のレコードとみなすほうが都合がよいので、日付は変えずに扱っています。さらに、時刻も0時にリセットせず、24時、25時...として扱っています。具体的には、以下のような感じです。
このような時刻表示にするには、「セルの表示設定」で「表示形式」を以下のようにします。特殊な形式なので「ユーザー定義」で追加します。
[hh]:mm
ちなみに、「h」や「m」だけで「時」と「分」を表記できます。これを、「hh」や「mm」とすることで、それぞれ2桁表記にできます。1桁の数値の場合は、10の位に0が補完されます。「hh」を「[」と「]」で囲むと、0時以降を24時、25時...と表記してくれます。
このような時刻を含め、入力はiPhone上で行いますが、運転中ということもありますので、基本的に音声入力で行います。例えば、「23:04」を入力する場合、「にじゅうさんじよんぷん」と発話すると、「23時4分」と解釈されます。これでセルの入力を確定すると、時刻として正しく入力できます。ですが、24時以降の時刻の場合、同じ方法では音声入力がうまくいきません。
これを回避するため、24時以降の時刻の場合は次のようなやり方で音声入力しています。例えば、「25:07」を入力する場合、「にじゅうごころんなな」と発話します。こうすると、「25: 7」と解釈されます。なぜか「:」の後ろに空白が入りますが、そのまま確定すれば、時刻として正しく入力できます。
次の乗務日に記録を開始するときの日付入力方法
C2列の「日付」は、計算式により同じ日付が延々と入力されています。ですが、次の乗務日になれば、日付を変更しなければなりません。一言で言うと、空白レコードのうち先頭の「日付」セルを次の乗務日の日付で上書きするだけですが、具体的にiPhoneで次のような手順で行っています。
まず、空白レコードのうち先頭の「日付」セルをタップしてメニューを表示させ、「消去」をタップします（画像左）。すると、計算式が消去され、空白セルとなります。同時に、左の番号は1にリセットされ、以降の日付は一時的にシリアル値0が示す「1900/01/00」となります（画像右）。
そのまま日付を直打ちしてもよいのですが、書式が日付形式のセルなので入力ヘルパーが使えます。編集状態にしてキーボードを表示させ、日付の入力ヘルパーのアイコンをタップします（画像左）。画面下部中央に当日の日付が示されていることを確認して、「完了」をタップします（画像右）。
これで当日の日付が入力され、以降のセルは計算式により当日の日付に書き換わります。ただ、このままだと前回の日付と当日の日付のレコードが混在している状態です。改めて当日の日付で絞り込むため、オートフィルターのアイコンをタップします（画像左）。「並べ替えとフィルター」メニューの「日付フィルター」を改めてタップします（画像右）。
「日付フィルター」のメニューで、すでに「今日」が設定されている状態ですが、改めてタップします（画像左）。こうすることで、書き換わった当日の日付のレコードだけの絞り込みが完了します（画像右）。
累計や平均などの数値は計算式で自動算出
金額を入力しているだけでは、その日の売り上げの状況は把握できませんから、あらかじめ計算式を組み込んでおき、自動で算出されるようにしておきます。
まずは「累計」です。「金額」が空欄だった場合は計算しませんが、そうでない場合は「金額」の合計を求めます。具体的には以下の計算式のとおりで、SUMIF関数で同じ「日付」の「金額」の合計を求めています。検索範囲は、入力対象の先頭行であるR3C12から現在の行までのRC12としています。
=IF(ISBLANK(RC12),"",SUMIF(R3C2:RC2,RC2,R3C12:RC12))
「平均」は「累計」を回数で割れば簡単に出る指標ですが、せっかくなので正確な数値を見える化しておきます。計算の要領は累計と同じで、SUMIF関数の代わりにAVERAGEIF関数を使うだけです。
=IF(ISBLANK(RC12),"",AVERAGEIF(R3C2:RC2,RC2,R3C12:RC12))
「税抜」は、消費税を除いた売り上げです。給与に影響するのは、もちろんこの税抜きの売り上げですから、実は最も重要な指標です。以下の計算式のように「累計」から求めますが、10円単位で四捨五入しています。
=IF(ISBLANK(RC12),"",ROUND(RC13*100/110,-1))
「効率」は、1時間あたりにどれくらいの売り上げを上げているかを示す指標です。空車で出発してから、途中でお客さんを乗せ、お客さんを降ろすまでの時間を基準にして算出しています。具体的な計算式は、以下のとおりです。
=IF(OR(ISBLANK(RC3),ISBLANK(RC11)),"",RC12/(RC11-RC3)/24)
「予測」は、最大で直近3回分の売り上げと、それを売り上げるのに要した時間を考慮して、28時（翌4時）時点の売上額を予測した数値です。単純に、それまでの売り上げに対して、その後直近3回分の傾きで売り上げたらいくらになるかといった指標です。いわゆる着地予想ってやつですね。あまり正確ではありませんが、いまの勢いがどれくらいなのかを知る程度には役立ってます。
=IF(OR(ISBLANK(RC2),ISBLANK(RC10),ISBLANK(RC11)),"",RC13+(28/24-RC11)*IF(RC1<3,IF(RC1<2,RC12/(RC11-RC3),SUM(R[-1]C12:RC12)/(RC11-R[-1]C3)),SUM(R[-2]C12:RC12)/(RC11-R[-2]C3)))
以上、基本となる時刻や料金の入力と、そこから簡単に計算できる指標に絞って紹介しました。次回は、入力したデータをグラフ化する部分の作り込みについて紹介しようと思います。
コメント