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

公開日: : 最終更新日:2013/09/13 ■Excelで効率アップ!

このエントリーをはてなブックマークに追加
はてなブックマーク - 意外と不揃いな日付データをExcelで加工する方法
Pocket

様々なシステムのデータを加工するときに、日付のデータには困らされます。
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足りません(^^;)
この土日にちょっと長めに走って追いつく予定です。


■スポンサードリンク

■スポンサードリンク




お知らせ
4/26 経理&会計のためのExcel入門セミナー  


■ブログ「EX-IT」(管理人 井ノ上陽一)は、次の方法で購読できます。

・RSS(feedly。更新情報を受け取れます→関連記事
follow us in feedly

・Facebook(Facebook上で更新情報を見ることができます)
・Twitter(→ツイートまとめ


■本気で税理士独立を目指す方限定のメルマガ「税理士進化論」平日毎日配信!
税理士進化論 

■井ノ上陽一 サービスメニュー
 ※現在、ご紹介以外の税理士顧問契約はうけておりません
返金保証付き!・Excel・IT・効率化コンサルティング
返金保証付き!初心者向けWordPressブログコンサル
■井ノ上陽一の著書
『社長!「経理」がわからないと、あなたの会社潰れますよ』 
『使える経理帳票―これ一冊でOK! Excel』 
『そのまま使える経理&会計のためのExcel入門』 
このエントリーをはてなブックマークに追加
はてなブックマーク - 意外と不揃いな日付データをExcelで加工する方法
Pocket

  • 井ノ上陽一
    (株)タイムコンサルティング代表取締役・税理士
    井ノ上陽一
    Microsoft MVP For Excel


    会計ソフトfreee認定アドバイザー
    クラウド会計ソフトfreeeフリー

    RUNチーム「Admiral」
    運営サイト:SUB4 http://sub4.jp/
    トライアスロンチーム「ポセイドン」主宰

    Excel、ITを駆使し、雇われない・雇わないフリーランスとしての働き方・生き方を楽しんでいる。
    日課は、早起き(4時〜6時)、ブログ、トライアスロンのトレーニング、料理、少々ゲーム。
    ブログは2007年7月9日より2,400日以上毎日更新中。
    さらに詳細なプロフィールはこちら

  • 2008年11月以降に読んだ本の全リスト→こちら
PAGE TOP ↑