カード利用明細データから科目を推測する
はじめに
こんにちは。
JIM ACCOUNTINGの児島泰洋です。
今回はクレジットカードの利用明細データを使って仕訳の科目を推測する方法について書かせていただきます。
会社が従業員に経費支払のためにクレジットカードを支給していて、経理部ではその利用明細データをExcelに取り込んで仕訳伝票を作成し、会計システムにアップロードしているケースを想定しています。
したがって、お使いの会計システムが利用明細データを直接取り込んで、しかも科目推測までしてくれているのであればこの記事を読む必要はありません。（freeeなどの会計ソフトは設定さえきちんとしておけば自動でやってくれます。）
うちはそんな便利な会計ソフトは使ってない、会計ソフトにそういう機能はあるけどなかなかうまく使えない、私はそれでもExcelを使いたいという方はぜひ読んでみてください。
経理でのワンシーン
かつては（今でも？）カード会社から毎月紙で送られてくるカードの利用明細を見ながらひとつひとつ仕訳を会計システムに手入力していたはずです。
さすがにそれでは数が多くてとても対処しきれないので、カード会社のホームページから利用明細データを取得して仕訳をExcelで作成している会社もあることでしょう。
とはいえ、利用明細データから得られるのは支払日・支払先・金額・利用内容くらいなので、仕訳の科目は自分でひとつひとつ選択して入力しなければなりません。
この作業もなかなかたいへんですね。
科目も自動で推測して入力できるようになったら便利だと思いませんか？
科目を推測する方法
支払先や利用内容によって使用する科目はだいたい決まってくるので（Amazonなら新聞図書費など）、ルールさえ決めてしまえば実現できそうな気がしますよね。
では具体的にどうすればできるでしょう？
ここでは利用内容に「特定の文字列」が含まれていれば、「特定の科目」を表示させることを考えます。
（支払先でも同じことができます。支払先と科目がほぼ１対１で対応するのであれば支払先を使ってもかまいません。）
そうすると、以下の工程が必要になってくると考えられます。
工程➀ 「特定の文字列」に「特定の科目」を対応させるパターンの一覧表の作成
工程② 利用明細データの貼り付け
工程③ 利用内容にどの「特定の文字列」が含まれているかの判定
工程④ 利用内容に含まれている「特定の文字列」に対応した「特定の科目」の表示
順番に見ていきましょう。
工程➀ 推測パターンの一覧表を作る
利用明細データの「利用内容」に含まれる「特定の文字列」に「特定の科目」を対応させるパターンをいくつか作成して一覧表にまとめましょう。
さすがに何もないところから推測結果を得ることはできませんので、パターンの一覧表を事前に作っておかないといけません。
パターンがとても少なければ関数だけで対処できなくもないですが、パターンが増えてくると関数が複雑になりすぎますし、メンテナンスが難しくなるので、めんどくさがらずに一覧表を作りましょう。
一覧表のイメージは以下のとおりです。
１行目に「特定の文字列」、２行目に対応する科目を横に並べます。（ちなみに、行は横のライン、列は縦のラインを意味します。）
あとで利用明細データを貼り付けるので、このような配置にしています。
| E | F | G | H | I | |
| 1 | ＥＴＣ | 昭和シェル | 中部電力 | ドコモ | アマゾン |
| 2 | 旅費交通費 | 車両費 | 水道光熱費 | 通信費 | 新聞図書費 |
工程② 利用明細データを貼り付ける
上記の一覧表を含むExcelシートに利用明細データを貼り付けます。
利用明細データはあらかじめカード会社のホームページからダウンロードしておいてください。
以下の表ではＡ列に日付、Ｃ列に金額、Ｄ列に利用内容を貼り付けています。
Ｂ列は後で関数を使って推測した科目を表示させるための列です。
Ｅ列からＩ列は工程➀で作った一覧表です。
Ｊ列の合計は後でまた説明します。
| A | B | C | D | E | F | G | H | I | J | |
| 1 | 日付 | 科目推測 | 金額 | 利用内容 | ＥＴＣ | 昭和シェル | 中部電力 | ドコモ | アマゾン | 合計 |
| 2 | － | － | － | － | 旅費交通費 | 車両費 | 水道光熱費 | 通信費 | 新聞図書費 | － |
| 3 | 5/6 | 750 | EＴＣ XX地区 | |||||||
| 4 | 5/13 | 4,177 | 昭和シェル XXX | |||||||
| 5 | 5/15 | 1,700 | 中部電力 XXX | |||||||
| 6 | 5/20 | 320 | XXXドコモ | |||||||
| 7 | 5/25 | 750 | アマゾン XXX |
工程③ 利用内容にどの「特定の文字列」が含まれているかを判定する
利用明細データにある「利用内容」（カード会社によって呼び方は異なりますが、カードを何に使ったのかがわかる簡単な説明書きです）にどの「特定の文字列」が含まれているかを判定します。（もちろんどれも含まれないこともあります。）
工程➀では「特定の文字列」を５種類用意しています。利用内容にそのうちどれが含まれているかは分かりませんので、ひとつひとつ判定しなければなりません。
それぞれの「特定の文字列」が含まれていることは数字の「１」、含まれていないことは数字の「０」で表現することにします。
そのためにCOUNTIFS関数を使用します。（COUNTIF関数でも同じことができますが、COUNTIFS関数のほうが汎用性が高いのでおススメです。）
COUNTIFS関数は検索条件範囲（ここでは利用内容）に検索条件（ここでは特定の文字列）に一致するものがいくつあるかを数えてくれる関数です。
COUNTIFS関数は上記の検索条件範囲と検索条件を引数として以下のように記述します。
「=COUNTIFS(検索条件範囲, 検索条件)」
ただし、検索条件範囲にある文字列が検索条件と完全に一致するとは限りません。
一文字でも違えば一致しないとみなされ数えられません。
例えば、「ＥＴＣ」という文字列を検索条件とするとき、利用内容も「ＥＴＣ」であれば数えてくれますが、「ＥＴＣ ＸＸ」だったときは数えてくれません。
だからといって、両者を一致させるのはとても手間がかかってしまいますので、科目推測なんてせずに初めから自分でやったほうがよくなってしまいます。
そこで、ワイルドカード「*」を使います。検索条件を「”*”&”ＥＴＣ”&”*”」のようにして、探したい文字列をワイルドカードで挟み込みます。こうすると検索条件範囲に探したい文字列が含まれていれば数えてくれるようになります。
まず、セルＥ３に以下のように関数を記述してください。
=COUNTIFS($D3,”*”&E$1&”*”)
１番目の引数の検索条件範囲はセルＤ３ですが、Ｄの前に＄マーク（絶対参照を表します）をつけています。この数式を右側のセルにコピーするときに参照する列がずれないようにするためのものです。
２番目の引数の検索条件はセルＥ１を参照していますが、この数式を下側のセルにコピーしたときに参照する行がずれないように１の前に＄マークをつけています。
Ｅ３の関数をＥ３からＩ７までの範囲のセルにコピーすると以下のようになります。
セルＥ３は「１」になっていますね。これはセルＤ３に「ＥＴＣ」という文字列が１個含まれているからです。
| A | B | C | D | E | F | G | H | I | J | |
| 1 | 日付 | 科目推測 | 金額 | 利用内容 | ＥＴＣ | 昭和シェル | 中部電力 | ドコモ | アマゾン | 合計 |
| 2 | － | － | － | － | 旅費交通費 | 車両費 | 水道光熱費 | 通信費 | 新聞図書費 | － |
| 3 | 5/6 | 750 | EＴＣ XX地区 | 1 | 0 | 0 | 0 | 0 | 1 | |
| 4 | 5/13 | 4,177 | 昭和シェル XXX | 0 | 1 | 0 | 0 | 0 | 1 | |
| 5 | 5/15 | 1,700 | 中部電力 XXX | 0 | 0 | 1 | 0 | 0 | 1 | |
| 6 | 5/20 | 320 | XXXドコモ | 0 | 0 | 0 | 1 | 0 | 1 | |
| 7 | 5/25 | 750 | アマゾン XXX | 0 | 0 | 0 | 0 | 1 | 1 |
なお、利用内容には「特定の文字列」が１回だけ含まれることを想定しています。同じ文字列が複数回含まれることはまれなのでたいていは問題ないでしょう。ただし、利用内容に２種類以上の「特定の文字列」が含まれることはありえます。上記の例では５種類しかありませんが、パターンを増やすと２種類以上の検索条件に当てはまるものがでてきます。そのようなことがおきていないか確かめるためにＩ列の合計欄を設けています。
Ｉ列の合計が２以上であるときは複数の種類の「特定の文字列」を含んでしまっているので、「特定の文字列」を見直した方が良いです。
複数の種類の検索条件に当てはまったとしても、先に設定した（より左側の列の）検索条件が優先されるだけなので大した問題ではないですが、１種類だけ当てはまるようにしておいたほうが望ましいです。そのほうが意図した結果が得られやすいからです。
工程④ 利用内容に含まれている「特定の文字列」に対応した「特定の科目」の表示
工程③で利用内容にどの「特定の文字列」が含まれているかが判定されましたので、この「特定の文字列」に対応した科目をＢ列の科目欄に表示させます。
例えば、セルＤ３には「ＥＴＣ」が含まれているので、セルＥ３が「１」となっています。なので同じ列にあるセルＥ２を参照して、セルＢ３に「旅費交通費」と表示させればよいわけです。
この処理を関数で実現するのは少々難しいですが、やってみましょう。
３行目以下のＢ列に表示させたい科目はセルＥ２からセルＩ２（すべて２行目）までの範囲にあります。
あとは「１」がある列の位置が分かれば参照するセルを１個にしぼることができます。
そしてこのセルの値をＢ列に表示させる関数を作ればよいことになります。
そのためにINDEX関数とMATCH関数のふたつを使います。
INDEX関数は参照範囲の左上のセルを原点として、〇行目の〇列目にある値を返します。
したがって、INDEX関数は以下のように３つの引数を必要とします。
１番目の引数に参照範囲（ここではセルＥ２からセルＩ２の絶対参照）、
２番目の引数に参照範囲における行番号、
３番目の引数に参照範囲における列番号をそれぞれ指定します。
今回は参照範囲が１行だけなので２番目の引数は省略され、３番目の引数が２番目に繰り上がります。
したがって、INDEX関数は以下のように記述されます。
=INDEX($E$2:$I$2,列番号)
１番目の引数である参照範囲（Ｅ２からＩ２）は決まっていますし、行番号も省略されますので、あとは参照する列番号だけが分かればよいことになります。
参照する列番号、すなわち数字の「１」がある列を探すためにはMATCH関数を使います。
MATCH関数は探したい値が参照範囲のいちばん左のセルを始点として何番目にあるかを返します。
したがってMATCH関数は以下の三つの引数を指定します。
１番目の引数に探したい値（ここでは１）、
２番目の引数に参照範囲（ここではMATCH関数を使うセルと同じ行のＥ列からＩ列まで）、
３番目の引数に照合の種類（ここでは何も考えず０にしましょう、省略しないこと！）
したがって、MATCH関数（３行目の場合）は以下のように記述されます。
=MATCH(1,E3:I3,0)
MATCH関数の戻り値は参照範囲内で１がある列の相対位置（左から何番目か）となります。
この場合は１になります。
MATCH関数をINDEX関数の２番目の引数にすることで、列番号も決めることができます。
上記をまとめて関数を記述すると以下のようになります。
=INDEX($E$2:$I$2,MATCH(1,E3:I3,0))
この関数をセルＢ３からセルＢ７にコピーすると以下のようになります。
| A | B | C | D | E | F | G | H | I | J | |
| 1 | 日付 | 科目推測 | 金額 | 利用内容 | ＥＴＣ | 昭和シェル | 中部電力 | ドコモ | アマゾン | 合計 |
| 2 | － | － | － | － | 旅費交通費 | 車両費 | 水道光熱費 | 通信費 | 新聞図書費 | － |
| 3 | 5/6 | 旅費交通費 | 750 | EＴＣ XX地区 | 1 | 0 | 0 | 0 | 0 | 1 |
| 4 | 5/13 | 車両費 | 4,177 | 昭和シェル XXX | 0 | 1 | 0 | 0 | 0 | 1 |
| 5 | 5/15 | 水道光熱費 | 1,700 | 中部電力 XXX | 0 | 0 | 1 | 0 | 0 | 1 |
| 6 | 5/20 | 通信費 | 320 | XXXドコモ | 0 | 0 | 0 | 1 | 0 | 1 |
| 7 | 5/25 | 新聞図書費 | 750 | アマゾン XXX | 0 | 0 | 0 | 0 | 1 | 1 |
ここまでで仕訳に必要なデータはほぼそろえられたはずです。
あとはお使いの会計システムにあった様式に整えて、仕訳データをアップロードすれば完了です。
注意
科目はあくまで推測なので、推測結果をきちんと見直して、適宜修正しなければならないことに注意してください。
また、利用内容にどの「特定の文字列」も含まれない場合は、当然科目は推測されません。
あくまで仕訳作成をサポートするためのものであることに留意してください。
ここまで読んでいただきありがとうございます。お疲れ様でした。
Excelデータもご用意しましたので、（ご自分の責任で）ご使用ください。
↓ ↓ ↓
