預金通帳のOCR読取後のExcelデータ加工

こちらの記事を始め、その後も数回にわたって解説している、預金通帳のOCR読み取り。
最終的にはその読み取ったデータを会計ソフトにインポートするのであるが。
そのままインポートできるわけではなく、ある程度Excel上で加工が必要だ。

OCRソフトで読み取ったデータをそのままExcel上に取り込むと、上記図の通り、ひとつの列に数値（金額）と文字列（摘要）が混在することになる。
預金通帳は、その限られたスペースを有効に使うため、引出欄または預入欄にその金額が印字されるだけでなく、空いたスペースに取引の内容を示す摘要が印字されるからだ。
このままでは扱いが難しいので、これを数値と文字列に分ける必要がある。

また、OCRで読み取ったデータは100％合ってるとは限らない。
基本的には数値（金額）重視でOCRをかけているのだが、それでも誤認識は避けられない。
数値が違ったまま会計ソフトにインポートするというのは致命的であり、Excel上でしっかりと合わせておかなければならない。
そのための検証・修正作業も必要になってくる。

もちろん数値以外の日付や摘要も確認・修正が必要なのだが、そちらについては今回はとりあえず説明を省略する。

◆数値と文字列を分ける

これを分けるためには、Excelの「T関数」というものを使う。

詳しい解説は上記リンク先にあるが、要は「参照セルが、文字列だったらそのまま、文字列以外（数値等）なら空白」となる関数のようだ。
文字列か否かを判定してくれるので、後は「IF関数」を組み合わせて、これらのデータをしっかりと分ける。

先ほどのT関数とIF関数を使い「参照セル（C2）が文字列以外ならその数値、そうでないならゼロ」という算式を入れ、後はその式を下にドラッグコピーすれば良い。

もちろん預入欄も同様である。

そして、摘要欄も同じような感じで抽出する。

ちなみに、「残高2」の欄は次の検証のために設けた列である。

◆読み取った数値の検証

検証作業といっても大したことをする訳ではない。
「通帳の残高欄をOCR読み取りした数値」と「引出欄・預入欄の数値から計算される残高」、これが合っているかどうかを確認するだけだ。

元の通帳がこちら。

こちらがOCR結果をExcelに貼付け加工・検証したもの。

①は、通帳の「,（カンマ）」を「9」と認識してしまった結果、「1,000円」「1,100円」がそれぞれ「19,000円」「19,100円」となってしまったケース

②は、通帳の正しい残高が「29,328円」のところ、「293,281円」と末尾に余計な「1」がついてしまったケース

③は、①と同様に「,」を「9」と認識してしまったため、「4,702円」が「49,702円」となったケース

④は、「0（ゼロ）」を「9」と認識してしまい、「20,000円」が「29,000円」となったケース

⑤は、通帳残高が「89,502円」のところ、頭の「8」が認識されず、「9,502円」となったケース

このように、残高欄の数値がOCRで誤認識されていたり、引出欄・預入欄の数値が誤認識されていれば、検証欄がゼロにならない。
これを元に、該当箇所を原資料と突合して必要な修正をし、検証欄が全てゼロとなるようにする。

上記の例では、かなり古い通帳（20年以上前の三井銀行）であり、OCRソフトの辞書もまっさらなものを使っているため認識精度がイマイチだが、ちゃんと設定すれば修正が必要な箇所はそれほど多くはならないはず。

---

慣れてきたら、いちいち算式を入力してコピーしなくても、マクロを使って少しは楽に出来るかも。

ただ、金融機関によって通帳のレイアウトは異なる。
残高欄が毎行記載されず、同日の取引の最終行のみ記載されるものや、通帳に「摘要欄」などがさらに別途設けられているものなど。
そのような場合には上記で紹介した算式も少し工夫が必要である。

ま、とにかく、これでようやく扱いやすいデータとなる。

http://katocpta.blogspot.com/2012/08/ocrexcel.html預金通帳のOCR読取後のExcelデータ加工