交通費精算で、往復・定期区間の入れまちがいを防ぐ3つの工夫｜Excelで「二重？」「定期内？」に印をつける

交通費の精算で、月末に「この往復、帰りの分も別の行に入っていない？」「ここは定期の区間では？」と確かめ直していませんか。1件ずつは小さな金額でも、人数が増えると確認に時間がかかり、見落としも出やすくなります。

この記事では、Excel の交通費の明細に、入れまちがいが起きやすい3か所の「印」をつける工夫を紹介します。印は「まちがい」と決めつけるものではなく、「ここを見てください」という合図です。例の駅名・運賃・氏名はすべて架空です。

明細の形をそろえる

まず、1回の移動を1行にし、次の列をそろえます。

日付／氏名／出発／到着／片道・往復／片道の金額

ポイントは、金額の欄には「片道の金額」だけを入れることです。往復かどうかは別の列で選びます。出発と到着の駅は、駅名の一覧からプルダウンで選べるようにしておくと、「中央」と「中央駅」のような書き方のちがいが出にくくなり、このあとの印も正しく働きます。

工夫1：往復は選ぶだけにして、二重の入力に印をつける

精算額は、往復なら片道の金額の2倍を式で出します（E列が片道・往復、F列が片道の金額）。

精算額：=IF(E2="往復",F2*2,F2)

これで「往復の金額を自分で計算して入れる」手間と計算ちがいがなくなります。ただ、よくあるのが、往復を選んだうえに、帰りの分も別の行に入れてしまう二重の入力です。

そこで、行き先の向きに関係なく同じ区間とわかるように、区間の名前を作ります（C列が出発、D列が到着）。

区間：=IF(C2<D2,C2&"-"&D2,D2&"-"&C2)

「中央→西が丘」も「西が丘→中央」も、同じ「中央-西が丘」になります。そのうえで、「同じ日・同じ人・同じ区間が2行以上あり、そのどれかが往復」なら印をつけます。

二重？：=IF(AND(COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$H$2:$H$20,H2)>1,COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$H$2:$H$20,H2,$E$2:$E$20,"往復")>0),"二重？","")

行きと帰りを片道で2行に分けて入れた場合は、どちらも片道なので印はつきません。例では、11月2日に「中央→西が丘 往復」と「西が丘→中央 片道」の両方が入っていた2行に「二重？」がつきました。同じ日に同じ区間を本当に2往復した場合も印がつくので、そのときはメモ欄に理由を書いてもらう、と決めておくと確認が早くなります。

工夫2：定期の区間を一覧にして、「定期内？」の印をつける

通勤定期を支給している会社では、定期の区間の中の移動は精算しない、と決めていることが多いと思います（扱いは会社のきまりによります）。けれど、明細を見るだけでは、だれの定期がどこからどこまでかを覚えていないと気づけません。

そこで、別のシート「定期」に、人ごとに定期の区間の中にある駅を1行ずつ並べます。

佐藤／桜台、佐藤／北町、佐藤／中央、田中／港南、田中／中央

明細の側では、出発と到着の駅が、両方ともその人の定期の駅の一覧にあれば印をつけます。

定期内？：=IF(AND(COUNTIFS(定期!$A:$A,B2,定期!$B:$B,C2)>0,COUNTIFS(定期!$A:$A,B2,定期!$B:$B,D2)>0),"定期内？","")

例では「佐藤 北町→中央」と「田中 中央→港南」に印がつき、「田中 港南→桜台」のように片方だけが定期の駅の移動にはつきませんでした。片方だけが定期の区間のときは、定期の端の駅から先の運賃だけを精算する、といった会社のきまりに合わせて確かめます。

工夫3：よく使う区間の運賃表と比べて、金額ちがいに印をつける

3つ目は、運賃の打ちまちがいです。とくに多いのが、往復を選んだのに、金額の欄にも往復の金額を入れてしまう形です。この場合、精算額は4倍分になってしまいます。

別のシート「運賃」に、よく使う区間と片道の運賃を並べておきます（A列とB列に駅、C列に片道の運賃）。明細では、向きに関係なくその区間の運賃を引いてきます。

運賃表の金額：=SUMIFS(運賃!$C:$C,運賃!$A:$A,C2,運賃!$B:$B,D2)+SUMIFS(運賃!$C:$C,運賃!$A:$A,D2,運賃!$B:$B,C2)

運賃表にない区間は0になるので、次の式で印を分けます（K列が運賃表の金額）。

金額確認：=IF(K2=0,"運賃表になし",IF(F2<>K2,"金額確認",""))

例では、片道380円の区間に760円と入っていた行に「金額確認」がつき、運賃表にない区間の行には「運賃表になし」と出ました。「運賃表になし」は、まちがいではなく、初めての行き先の合図です。確かめたあとで運賃表に足していくと、表がだんだん育っていきます。運賃が改定されたときは、運賃表を直すのを忘れないようにします。

印のついた行だけを見る

3つの印の列に、オートフィルターをかけておくと、月末の確認は「印のついた行だけ」を見れば済みます。例の12行では、印がついたのは8行でした。そのうち直すべきだった2か所（往復の二重と、往復の金額の入れまちがい）を直すと、精算額の合計は佐藤さんの分で4,110円から3,040円にかわりました。残りの印は、定期の区間の扱いや、同じ日に2往復したかなど、本人に確かめて決めるものです。

まとめ

金額の欄は片道だけにして往復は選ぶ。同じ日・同じ区間の二重に印をつける。定期の駅の一覧と比べて「定期内？」の印をつける。よく使う区間の運賃表と比べて金額ちがいに印をつける。印は決めつけではなく確認の合図として使うと、月末の見直しが短くなり、見落としも減らしやすくなります。

交通費・経費精算表を作ることもできます

日付・氏名・科目・金額を1行ずつ入れると、往復は自動で2倍になり、月ごとに人・科目ごとの合計と「領収書なし」の印が出る Excel のテンプレートを、ココナラのコンテンツマーケットで出しています。この記事の「二重？」「定期内？」「金額確認」の印を足したい、会社の科目やきまりに合わせたい、といった場合は、下のサービスからご相談ください。今お使いの精算表を見ながら、どこを自動にできるかのご相談から受け付けています。