財務捜査では、預金通帳、仕訳帳等の資料をエクセルに入力するのが基本です。
電子データで取得できるのが理想ですが、ときには紙データをスキャンし、OCRで文字認識するしかないこともあります。
元データによっては、そのままではデータとして使えないことがあります。
今回は日付を例にして、データ変換方法を考えます。
日付がすべて６桁（８桁）の場合
日付を示す文字列は、年が４桁または２桁、月と日は２桁になっているのが基本です。
年を”y”、月を”m”、日を”d”で示すならば、
- yymmdd
- yyyymmdd
となります。
2024年１月３日であれば、
- 240103
- 20240103
です。
６桁、８桁両方の説明をすると長くなるので、通帳でよく見る、６桁ということで、以下話を進めます。
日付を示す数字が６桁で統一されていれば、すべてのデータに同じ関数式を当てはめて日付に変換することができます。
例えば、上の図のように元データがすべて６桁であれば、
- Left関数を使い、「年」にあたる左から２文字を取得（2000を足して2024とする）
- mid関数を使い、「月」にあたる３文字目から２文字を取得
- right関数を使い、「日」にあたる右から２文字を取得
- これらをdate関数でまとめる
ということで、日付に変換することができます。
４桁、５桁があった場合
ただ、上のようにすべてが６桁データに統一されているわけではありません。
例えば、預金通帳をスキャナで読み込んだら、日付がこのようにテキスト化されてしまうことがあります。
通帳などの日付は、「24- 1- 3」と印字されていることがあります。
１桁の月や日がゼロ詰めされていません。
OCRで読み取った場合、「2413」と、忠実に再現してしまいます。
これを関数を使い、日付に変換する方法を考えます。
このときにやってはならないのは、目で見て手で修正という、「力技」でしてしまうこと。
今回はサンプルでデータは10しかありませんが、数千件のデータだととても人間では対応できません。
間違いも多くなりますし、生産的でもありません。
関数を使うことを面倒がって力技で対応していると、いつまで経ってもエクセルを使えないままです。
データの規則性に着目する
さて、先ほどのような扱いにくいデータがあった場合には、まず、データを一とおり確認し規則性を探すことにします。
データをみると、年月日を示すデータは、
- ４桁
- ５桁
- ６桁
の数字になっていることがわかります。
４桁、６桁の場合
そのうち、４桁と、６桁の数字は、簡単に日付に変換できることに気づきます。
- ４桁 → 左から２文字は「年」、３文字目は「月」、右側の１文字は「日」
- ６桁 → 左から２文字は「年」、３文字目から２文字は「月」、右側の２文字は「日」
になります。
例えば、
- 「2413」であれば、2024年１月３日
- 「241111」であれば、2024年11月11日
しか読めません。
このように４桁、６桁の数字と日付は１対１の関係にあります。
if文を使って、文字長が４、６の場合のdate関数を書くことはできそうです。
５桁の数字への対応方法
さて、ここまでは問題ないのですが、５桁の数字は、どのように変換すればいいでしょうか。
例えば、A3セルの「24111」は
- 2024年１月11日
- 2024年11月１日
のどちらにも読むことが可能です。
５桁でも簡単に日付が変換できる場合
しかし、５桁の数字がすべて２通り読めるわけではありません。
２通りに読めるのは、３文字目が「1」の場合だけです。
例えばA5セルはの「24211」は、2024年21月1日という日付はありえないため、
- 2024年２月11日
にしか読めません。
つまり、５桁で日付が２通りに読めるのは、３文字目が「1」の場合だけです。
５桁で３文字目が「1」の場合
先ほど、A3セルの「24111」は
- 2024年１月11日
- 2024年11月１日
のどちらにも読むことが可能と書きましたが、人間はこれを前後の関係から、2024年１月11日と判断できます。
この後、A6セルをみると「2024年４月２日」があります。
通帳の日付は昇順で並んでいるので、11月の可能性はありません。
一方、A10セルの「24111」は、前に10月のデータがあるので
- 2024年11月1日
としか読めないことになります。
整理してみる
以上を整理すると、
- 5桁の数字で３文字目が1の場合、日付は２通りに読める
- 前後の日付から、月は特定できる
ことがわかります。
ここから、エクセルに落とし込んでいきます。
まず、5桁の文字数は、２通りの読み方ができるので、
- C列 → ３文字目を月と読んだ日付
- D列 → ３文字目が”1″の場合、もう一つの読み方である3文字目・4文字目を月と読んだ日付
を表示させます。
次に以上では解決できない「24111」問題を解消します。
この場合、前後のデータから確認することにします。
もし、前のデータが２月以降であれば、１月と読むことはできません。
そこで、文字長5で最初から３文字目が”1″の場合、直前行の日付データの月が1より大きければE列が、そうでなければD列の値が当てはまることになります。
これをF列に判定式として入れます。
前後の日付がわからないと判定できないので、表の全体像を示しました。
- １通りしか読めない４桁、６桁のテキスト
- ２通り読める５桁のテキスト
を場合分けした表になります。
H列では、C列（４桁の場合）、F列（５桁の判定結果）、G列（６桁の場合）の最大値を求めています。
空白セルがあるので、それぞれを「+」でつなぐと”value”エラーとなるためです。
max関数ではなく、min関数でも構いません。
本日のまとめ
一見「読めない」と思うテキストも、規則性に着目するとエクセルで扱える日付等に変換することができます。
ほかにも「データ区切り」機能、「パワークエリ」を使う方法もあります。
「人海戦術」、「力技」によることは避けたいものです。
