意外と不揃いな日付データをExcelで加工する方法

様々なシステムのデータを加工するときに、日付のデータには困らされます。
Excelで日付のデータを加工するパターンを集めてみました。

意外と不揃いな日付データ

ネットバンクや会計ソフトからデータを取り出し、Excelで加工すると非常に効率的です。
データさえあれば、そのデータを見ながら入力する必要はなく、ダウンロードやコピペでExcel上に持ってくることで加工ができます。

ただ、このデータの日付形式が結構不揃いなのです。
各社、いろんな形式のデータがありますので、それに応じてExcel上で整えています。

昨日の記事で取り上げたとおり、Excelが＜日付＞と認める形式でないと正しく加工ができないのです。
Excel上で＜日付＞として扱われるのは、次のような形式です。
2012-10-1
2012/10/1
H24.10.1

 

日付のパターンとその対策

加工にはExcelの置換（Ctrl＋H）を使う方法と関数を使う方法があります。
場合に応じて使い分けていますが、処理を自動化し手間を減らすのであれば関数の方がより便利です。

 

(1)そのまま使える場合

Excelに持ってきてもそのまま日付として使えるケースです。

Paypal　　　　　　　　”2012年10月1日”
住信SBIネット銀行　　　”2012/10/1”

これらの日付形式だと楽ですね。

 

 

(2) .（ピリオド）の加工が必要な場合

○東京三菱UFJ銀行

東京三菱UFJ銀行のネットバンクでは、”2012.10.1″という形式です。
このままだと日付として扱えません。
”2012/10/1”に変換する必要があります。

置換（Ctrl＋H）で、”.”を”／”に置換すれば、

こうなりますので、日付として使えます。

関数でやるならば、SUBSTITUTE関数を使います。
＝SUBSTITUTE（A3［対象のセル］,”.”［検索する文字列］、”／”［置換する文字列］）
という関数です。
.（ピリオド）を/（スラッシュ）に置換します。
あらかじめこの関数を入れたシートを準備し、ネットバンクからデータを貼り付ければ、日付形式を自動的に変換できます。

 

○みずほ銀行
みずほ銀行のネットバンク（個人）は、”2012.10.01”です。
日付にゼロが入っていますが、上記の方法で同様に処理できます。

 

 

○弥生会計

会計ソフトの弥生会計からデータ（仕訳データ）をExcelにエクスポートすると、”H.24/10/1″となります。
（Excelボタンではなく、［ファイル］→［エクスポート］によるデータです。Excelボタンの方は、無駄な部分があったりデータ形式が扱いずらかったりするので使っていません）

Hのあとに”．”が入っているのです。なぜこんな仕様になっているのかはわかりませんが、これもExcelでは＜日付＞とみなしません。
この場合も、SUBSTITUTE関数で処理します。
今回は”.”を空欄に置換しています。

 

○三井住友銀行

似ていますがやっかいなのが三井住友銀行（個人）です。
”H24.10. 1”という形式なのですが、上記の方法でやるとこうなります。

 

日付の1の前に空白が入っているのです。
これも＜日付＞とはみなしません。
詳しい解説は省きますが、左から3文字を「年」、左から5文字目から2文字を「月」、右から2文字を「日」として取り出して、それをつなげています。
その際に「月」と「日」の空白を除去するTRIM関数を入れます。
こうなるともう意地です（笑）。

 

 

(3)年月日がつながっているパターン

 

楽天銀行（法人）は、”20121001”という形式です。
システムによってはこの形式でも＜日付＞とみなしますが、Excelでは＜日付＞とみなさないため加工します。
左側から4文字を「年」、左から5文字目から2文字を「月」、右から2文字を「日」としてくっつけています。
DATE関数は、DATE（年、月、日）で日付を表示する関数です。
もちろん、＆と”/”でくっつけてもかまいません。

（4)年月日がばらばらのパターン

ジャパンネットバンクは年、月、日がそれぞれ別のセルに入っています。
この場合は、これらをDATE関数でつなげます。

 

 

データは加工を前提に

PC上にあるデータならば、ブラウザからとろうが、システムからとろうがそのまま利用しようというのが基本的な考えです。
それを見ながら別のソフトやExcelに入力するのはムダですしめんどくさいですし、入力するときにはミスが起こる可能性もあります。

関数を入力するのは最初だけめんどくさく手間もかかりますが、一度作ってしまえば後は楽です。

ただ、世の中のシステムやデータは、この＜加工＞を前提に作られているわけではありません。
どちらかというと想定していないからこそ、上記のような不揃いなデータがあるのでしょう。
会計ソフトやネットバンクはデータ加工を前提に作って欲しいものです。
（ネットバンクは取り込むソフトもありますが、コストや使い勝手という理由で私は使っていません。）

せめて自分でPC上のデータを作るときには、加工を前提に整理して作るべきです。

 

 

---

---

---

---

【編集後記】

今月のランの目標は200kmです。
今のところ、予定よりも14km足りません(^^;)
この土日にちょっと長めに走って追いつく予定です。