燃費計算表を作ってみよう

2018.06.18関数

今回は所有車などの運行記録を取り、燃費を計算するシートを作成します。
車の台数はナンバーが「1001」「1251」「8525」「9632」の4台とします。
燃費の計算は、満タン給油して、その時の給油量と現在の走行距離メーターの値を記録しておき、前回の走行距離から今回の走行距離まで何kmか計算し、それを給油量で割ることによって、ガソリン1ℓあたり、何km走るかを求めます。
実際に操作するファイルはこちらからダウンロードできます。ぜひ操作してみてください。

記録する項目

記録は、給油した年月日と、その時の給油量と走行距離メーターの値を記録します。
また、どの車のデータがわからなくならないように、車のナンバーも控えます。
車のナンバー、年月日、走行距離、給油量の一覧表とします。
車ごとに別シートに管理する方法もありますが、車の台数が20台、30台と増えていくと、その分、シートの数も多くなります。
データを見たり操作したりするときにシートが多いと扱いづらかったり、誤って記録してしまったりする可能性も増えてしまいます。
ひとつの種類の一覧表は一つの表で管理した方が、メリットが多いです。
その代わり、必要なデータを見たい場合はそのままでは見ることはできないので、記録されているデータを一目で見れるように自動集計する仕組みが必要になります。

記録シートの設計

ダウンロードしたファイルをご覧ください。
シートはA列から、車のナンバー、年月日、走行距離、給油量の4項目を記録します。
ダウンロードしたファイルには、この4項目が既に作成され、データが入力されています。

ダウンロードファイル

 

1月1日から1月13日までのデータは、それぞれの車で記録し始めた時点の走行距離を記録したものです。
1月17日以降のデータに、燃費を自動で計算する計算式を設定します。
燃費を求めるために、同じ車で、今記録した直前の給油日を求め、その時点の走行距離を計算する必要があります。
以前の給油日は、記録した行の上の行までの中で、同じ車の給油日の最大値を見れば、最新の給油日を求めることができます。
求めた最新の給油日で、同じ車の走行距離が、直前の走行距離となります。
今記録した走行距離から直前の走行距離を引いた値を、今記録した給油量で割ることで燃費が計算されます。

計算する箇所

 

ここで使用する計算式は次の通りです。

MAXIFS関数:同じ車の給油日の最大値を求める

書式は次の通りです。
=MAXIFS(最大値を求める範囲,検索する範囲,検索するデータ)
今回の場合は次のような計算式になります。
最大値を求める範囲は、一番上から一つ上の行までの日付が入力されている範囲です。
検索する範囲は、一番上からひとつ上までの車のナンバーが入力されている範囲です。
検索するデータは、今回入力された車のナンバーです。
=MAXIFS(一番上から一つ前の行までの日付の範囲,一番上から一つ前の行までの車のナンバーの範囲,今回入力した車のナンバー)
となります。
1月17日のデータの6行目に対応するMAXIFS関数は次の通りです。
=MAXIFS($A$1:A5,$B$1:B5,B6)
セルA1とセルB1は、コピーしても移動させないために絶対参照にしています。

SUMIFS関数:直前の走行距離を求める

今回はひとつの値を求めるだけなのですが、他に条件を指定して一つの値を求める関数はありませんので、一番近いSUMIFS関数を使います。検索されるものは1行だけなので、合計される値は1つなので、条件を指定して平均値を求めるAVERAGEIFS関数でも構わないです。
書式は次の通りです。
=SUMIFS(合計を求める範囲,検索する範囲,検索するデータ,検索する範囲,検索するデータ)
今回の場合は次のような計算式になります。
合計を求める範囲は、走行距離の列全てです。
検索する範囲の1つめは、給油日の列全てです。
検索するデータの1つめは、MAXIFS関数で求めた直前の給油日です。
検索する範囲の2つめは、車のナンバーの列全てです。
検索するデータの2つめは、今回入力した車のナンバーです。
直前の給油日をMAXIFS関数で求めた時のように、一番上からひとつ前の行までの範囲で指定しても構わないのですが、直前の給油日を求めた時点でどの行か特定されるので、列を全て選択してもよいでしょう。
1月17日のデータの6行目に対応するSUMIFS関数は次の通りです。
=SUMIFS(D:D,B:B,B6,A:A,MAXIFS($A$1:A5,$B$1:B5,B6))

走行距離を求める計算式

今回記録した走行距離から、SUMIFS関数で求めた直前の走行距離から引けば、前回から今回までの走行距離が求まります。
1月17日のデータの6行目に対応する走行距離を求める計算式は次の通りです。
=D6-SUMIFS(D:D,B:B,B6,A:A,MAXIFS($A$1:A5,$B$1:B5,B6))
では、この計算式をセルE6に入力しましょう。

走行距離を求める

燃費を求める計算式

今回の給油量を、前回から今回までの走行距離で割れば、前回から今回までの燃費が求まります。
1月17日のデータの6行目に対応する燃費を求める計算式は次の通りシンプルな割り算です。
=E6/C6
では、この計算式をセルF6に入力しましょう。

燃費を求める

数式のコピー

作成した計算式は、下にコピーした場合、移動してほしいセルは相対参照のまま、移動してほしくないセルは絶対参照を設定しておきました。
セルE6とF6はそのまま下にオートフィルすれば、全て計算されます。

オートフィル

まとめ

今回は燃費計算書の作成を通して、計算式の組み立て方と、Excelで表を作成する時にどんな項目が必要かの考えかたを解説しました。
計算式内の範囲指定で、片方だけ絶対参照にする方法もポイントです。
上で書いた通り、このままでは1つの車に注目して見るには、少し見づらいので、フィルターなどの機能を使う必要があります。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top