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


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

スクリーンショット 2012 10 11 10 02 15




■スポンサードリンク

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

ネットバンクや会計ソフトからデータを取り出し、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)で、”.”を”/”に置換すれば、
スクリーンショット 2012 10 11 10 16 18

こうなりますので、日付として使えます。
スクリーンショット 2012 10 11 10 17 29

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

スクリーンショット 2012 10 11 10 45 36

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

スクリーンショット 2012 10 11 10 45 46

○弥生会計

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




■スポンサードリンク



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

スクリーンショット 2012 10 11 11 16 30

○三井住友銀行

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

スクリーンショット 2012 10 11 10 50 49

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

スクリーンショット 2012 10 11 10 50 15

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

楽天銀行(法人)は、”20121001”という形式です。
システムによってはこの形式でも<日付>とみなしますが、Excelでは<日付>とみなさないため加工します。
左側から4文字を「年」、左から5文字目から2文字を「月」、右から2文字を「日」としてくっつけています。
DATE関数は、DATE(年、月、日)で日付を表示する関数です。
もちろん、&と”/”でくっつけてもかまいません。
スクリーンショット 2012 10 11 10 51 28

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

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

スクリーンショット 2012 10 11 10 53 53

データは加工を前提に

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

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

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

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

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

【編集後記】

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

■スポンサードリンク
|||||||||||||||||||||||||||||||||||||||||||||||
■主催セミナー
10/14 WordPressブログ入門セミナー 
9/29 Excel入門セミナー
■個別コンサルティング
■メールコンサルティング
■セミナー開催要望フォーム


■スポンサードリンク







■サービスメニュー
『雇われない・雇わない生き方』無料送付
■執筆・講演
■ブログリクエストフォーム 
■メルマガ「税理士進化論」 
直近100記事一覧


■著書
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』




■ブログ「EX-IT」(管理人 井ノ上陽一)は、次の方法で購読できます。
・Facebook(Facebookにブログの更新情報が流れます。

・RSS(feedly。更新情報を受け取れます→詳細follow us in feedly
・Twitter(Twitterで更新情報を受け取れます) https://twitter.com/yoichiinoue 
・メールで更新情報を受け取れます。 詳細
The following two tabs change content below.
井ノ上 陽一
1972年生まれ。大阪生まれ宮崎育ち東京在住。 ひとりビジネスを、時間・お金の両面でサポート。 ・株式会社タイムコンサルティング代表取締役 ・MicrosoftMVP for Excel ・アイアンマン(スイム3.8km、バイク180.2km、ラン42.2km) ・著書に『ひとり社長の経理の基本』、『社長!「経理」がわからないと、あなたの会社潰れますよ』、『ひとり社長の経理の基本』,『そのまま使える経理&会計のためのExcel入門』ほか。 さらに詳細なプロフィールはこちら