” Excelでつくった現金出納帳を、会計ソフトにインポート… って、日付データの形式が合わないしーっ！”
みたいなこともありますよね。そんなときに役立つ、日付データを年月日に分解するマクロについて。Excel VBAのお話です。
本記事でつくりたいマクロとはこんなもの
きょうは、「インポートで役立つ！日付データを年月日に分解するマクロ」というお話です。
「マクロ」とは、端的に言うと、処理を自動化するプログラムのことなのですが。これからお話するマクロは、次のようなケースを想定しています ↓
上図の流れを言葉であらわすと、
- Excelでつくっている現金出納帳の日付データの形式と、それを会計ソフトにインポートするためのフォームの日付形式が違っている
- そこで、Excel現金出納帳ファイル（上図オレンジ色）のデータを、いったん、別のExcelファイル（上図青色）の貼付用Sheetにコピペする（上図オレンジ色矢印）
- 貼付用シートにコピペしたデータを、マクロを使って自動的に、インポートフォームの形式に書き換える（上図青矢印）
- インポートフォームのデータを、会計ソフトに取り込む（上図緑矢印）
このうち③のマクロのつくりかたについて、このあと見ていきましょう。というのが本記事の内容です。
このマクロをつくっておけば、Excel現金出納帳のデータをコピペしなおすことで、なんどでもすぐにインポートフォームの形式に書き換えることが可能になります。これがマクロの威力。
では、はじめていきましょうか。
マクロ作成前の「前提」を確認です
はじめに、マクロを作成する前の「前提」を確認します。
前述した図のとおり、青色部分の「本記事のExcel Book（.xlsm）」をこれから仕上げていくわけですが。このExcel Bookは、２つのSheet（シート）で構成されています。
２つのシートがどのような状態にあるかをカンタンに見ていきましょう。
Sheet名「元データ貼付」
独自につくっているExcel現金出納帳のデータをコピペした状態です（便宜的に、コピペ部分を「薄い青」で色付けしてあります）↓
Sheet名「インポートフォーム」
さきほどの「元データ貼付」Sheetから、マクロで書き換えて転記をするためのSheetです。というわけで、いまはタイトル行のみ入力済み ↓
「元データ貼付」Sheetのタイトルとの違いは、「日付」のところだけです。
「元データ貼付」のほうが「日付」欄は１つなのに対して、「インポートフォーム」のほうは「年」「月」「日」と３つの欄に分かれていることを確認しておきましょう。
完成形はこちら
先に、完成形も確認をしておきましょう。これから作成をするマクロによって、「インポートフォーム」には次のようにデータが転記されます ↓
「元データ貼付」Sheetでは「2018/6/1」というように表示されていた日付が、「インポートフォーム」Sheetでは「2018」「6」「1」と分かれています。
日付以外のデータについては、ただただ、「元データ貼付」Sheetから「インポートフォーム」Sheetに転記をしただけです。
それではいよいよ、マクロづくりをはじめます。
本記事を見ながら、いっしょにマクロづくりを体験してみたい！ ということであれば。マクロ作成前のExcelファイルがダウンロードできます↓
日付データを年月日に分解するマクロをつくる
それではまず、「VBE」を起動します。ちなみに、VBEというのは「Visual Basic Editor」のこと。
マクロをつくる際に必要なプログラミング言語である「VBA（Visual Basic for Applications）」を、編集するための機能が「VBE」です。
まぁ、そんなもんかな。ということで、とにかく起動です。
VBE、始動。
前述した、マクロ作成前のExcel Bookを開いた状態で、「Alt + F11」のキーを押すとVBEが起動します。
はじめての方だと、「なんじゃこりゃ？」というような画面ではありますが。それはそれとして、先に進みましょう。
VBEが起動したらまず、マクロをつくる（コード（文字）を書く）ための場所をつくります。
画面上部のメニューから「挿入」をクリック。続いて開くリストの中から「標準モジュール」をクリックします。すると、次のような画面になるはずです ↓
上記の「赤枠」で囲った範囲に、コード（文字）を記述していきます。
VBEのプログラム記述場所（上図の赤枠内）には、覚えもないのに「Option Explicit」と書いてあるかもしれないし、書いていないかもしれない。
「なんだこれ？」ということであれば、本記事に関してはひとまず無視をしていただくことでかまいません。
呪文「Sub」を唱えることからはじめてみる
それでは、コード（文字）を記述していきましょう。
まずは、「Sub tenki」と入力をしてみましょう。入力したら「Enter」キーを押してみます。こうなるはずです ↓
クリックで拡大できます
勝手にコードが追加記述されましたよね。そもそも、「Sub」ってなんじゃいな？ ということですが。
まぁ、コードを書き始めるうえでの「呪文」みたいなものです。決めゼリフと言うか、決まり文句と言うか。
とにかく、「Sub」と唱えるところからはじまります。そして、「Sub」の次に入力した「tenki」は任意の「マクロ名」を表します。
ここでは「転記」をするマクロという意味で「tenki」としました。イヤならじぶんの好みで名付ければよし。
そして、「Enter」キーを押すことで自動記述された「End Sub」は、マクロの終点をあらわしています。
つまり。マクロ「tenki」のコードはここまでですよぉ、ということです。
したがって、マクロ「tenki」のコードは、「Sub」と「End Sub」のあいだに記述する。そういうルールになっております。
手順を「日本語」で書いてみる
ここで、いきなりコードを記述し始める前に。マクロ「tenki」で行うことの手順を明らかにしておきましょう。手順をVBEに箇条書きしてみます ↓
クリックで拡大できます
上記の箇条書きをするときのポイントは、文章のアタマに「 ’ 」を付けること。これを付けたあとに文字を入力して「Enter」キーを押すと、入力した文字が緑色になっているはずです。
これを「コメント」と呼びます。コメント状態の文字については、VBEではただの「メモ書き」として認識されます。
よって、マクロの挙動にはなんら影響を与えません。あとでコードを見たときの覚え書きとしても使えますから、コメントは積極的に使うのがいいらしい。
で、手順としては。上記画像のとおりですが、こんなカンジです ↓
- 「元データ貼付」SheetのA列にある「日付」データを、「インポートフォーム」SheetのA〜C列に、年・月・日に分解して転記する
- 「元データ貼付」SheetのB列以降のデータを、「インポートフォーム」SheetのD〜H列に転記する
それでは、手順に沿って進めていきましょう。
日付データを「年・月・日」に分解して転記する
１つめの手順のコメント下に、次のようにコードを入力してみましょう ↓
クリックで拡大できます
いきなりで面食らうかもしれませんが。上記３行のコードについて、説明を加えます。まず、１行目から ↓
クリックで拡大できます
１行目のコードは「＝」を挟んで、大きく左右に分かれています。そして、ここでの「＝」の意味は、上図の赤色矢印のとおり、「右側を左側に代入しますよ」です。
その「右側」は、まず「Year（ ）」というコードがあります。Yearのあとのカッコ内の日付データから「年（Year）」を抜き出す、というコードです。
Yearのカッコ内には、「Worksheets(1).cells(2,1)」の記述があり、これは、「ワークシート１枚目のセルA2」をあらわしています。
したがって、「ワークシート１枚目のセルA2」、つまり、「元データ貼付」SheetのA2セルの日付データから、年の数字を抜き出す。というのが、コードの右側です。
なお、セルA2は「２行め,１列め」のセルです。これを、コードでは「Cells（2,1）」と表現しています。セルは「Cells（行,列）」で指定できる、ということです。
続いて、「＝」の左側を見てみましょう。「Worksheets(2).cells(2,1)」は右側と同様の読み方をすれば、「ワークシート２枚目のセルA2」だとわかります。
「Value」は「値」をあらわしています。よって、「インポートフォーム（ワークシート２枚目）SheetのセルA2の値」と読めます。
これらをまとめると。上図のとおり、「ワークシート１枚目のセルA2から「年」の数字を抜き出して、ワークシート２枚目のセルA2の値として入力する」となります。
２行目、３行目も似たようなもの
２行目と３行目のコードも、同じ要領で読んでみましょう。「Year」が「Month」「Day」に置き換わっている点には注意です。
２行目がこちら ↓
クリックで拡大できます
つまり。元データ貼付（ワークシート１枚目）の日付データ（セルA2）から「月」を抜き出して、インポートフォーム（ワークシート２枚目）のセルB2に入力しますよ。ということです。
次、３行目 ↓
クリックで拡大できます
もう大丈夫ですね。元データ貼付（ワークシート１枚目）の日付データ（セルA2）から「日」を抜き出して、インポートフォーム（ワークシート２枚目）のセルC2に入力しますよ。ということです。
途中経過を確認してみよう
ここまでのコードが正しいものであるかどうか、確認をしてみましょう。
「F5」キーを押すと、コードが実行されます。「F５」キーを押したあと、「Alt＋F11」キーを押してみましょう。通常のExcelの画面が開きます。
「インポートフォーム」Sheetは、次のようになっているはずです ↓
クリックで拡大できます
「元データ貼付」SheetのA2セルの日付データ「2018/6/1」が、「インポートフォーム」ではきちんと年・月・日に分解して転記されています。
上記の転記されたデータはいったん削除して。ふたたび、「Alt＋F11」キーでVBEに戻ります。
この作業を元データの行数分だけ繰り返せというのか？
と、ここまで終えたところで。イヤなことに気が付きます。
いままでのコードでできたことは、「元データ貼付」Sheetのうちの「たったの１行分」です。
もしかして、これと同じことを「元データ貼付」Sheetの行数分だけ繰り返すのか…？ そんなのイヤだ、絶対に。ということで「技」を繰り出すことにします。
結論として、下記のようにコードを追加、一部修正をします ↓
クリックで拡大できます
わかりやすいように、追加箇所を「青色文字」で、修正箇所を「赤色文字」で示します ↓
クリックで拡大できます
上記を順番に解説します。まずは、追加部分「For i = 2 to 11」と「Next」。これも、「Sub 〜 End Sub」のように、はじめと終わりでセットになっています。
「For」ではじまり、「Next」で終わる。では、なにがはじまり、なにが終わるのか？
それは、「For i = 2 to 11」の記述から察することができますが、「 i 」を「２から11」まで「１づつ」変化させる。これが、「For」ではじまり、「Next」で終わるのです。
これについて、修正部分（赤色文字）を確認です。もともと記述をしていた３行のなかで、「セルの行数（２行目）」をあらわしていた箇所（２と記述していた）を「 i 」に変更しました。
この修正によって、「元データ貼付」Sheetにある「日付データ」の転記を、２行目から11行目まで繰り返す。というコードに変わりました。
以上のように「For 〜 Next」のコードを使うことで、イヤだった手作業での繰り返しをせずとも、自動化をすることができます。
ところで「 i 」とは何者なのか？
もうひとつ、追加部分があります。「Dim i As long」です。
ハナシが長くなりますので、詳しいことは省かせていただきますが。「Dim i As long」は、「 i は、整数の変数ですよ！」ということを宣言しているコードです。
これもまた、「Sub」や「For」と同じく、呪文のようなものです。
ちなみに「変数」というのは、「なにかしらのデータ」を保管しておくための箱だ、としばしば説明されます。ふ〜ん、という感じで聞き流してOKですが。
なお、その箱の名称は任意であり、ここでは「 i 」と名付けました。そして、変数「 i 」は「整数」です、の部分が「As Long」。
整数の場合には「Long」、文字列なら「String」などと決まっていますので、変数の内容に応じてそこは変わります。
というわけで。「For 〜 Next」のなかで変数「 i 」を扱いたいために、「Dim i As long（i は、整数の変数ですよ！）」と宣言をしたのです。
途中経過を確認してみよう Part２
ここで、ふたたび途中経過を確認してみましょう。「F5」キーで、実行でしたよね。そのあと、「Alt＋F11」キーを押してExcelの画面を開きます。
「インポートフォーム」Sheetは、次のようになっているはずです ↓
「元データ貼付」Sheetのデータ分の行数だけ、きちんと転記をされていることがわかります。
上記の転記されたデータはいったん削除して。ふたたび、「Alt＋F11」キーでVBEに戻ります。
元データの最終行数を取得せよ
手作業での繰り返しを回避することができて、めでたしめだし。とはまだいきません。
さきほどの「For 〜 Next」から、一部コードを再掲します ↓
For i = 2 To 11
上記コードの「11」は、なんの数字かと言うと。「元データ貼付」Sheetのデータ行数でしたよね。
ところが。元データはいつもいつも11行とは限りません。もしも５行だったら？ 30行だったらどうでしょう？
そのたびに、「11」の部分を「５」や「30」に書き換えるのもおっくうです。
じゃあ、とりあえず多めの数字にしておくのはどうだろう？ ということで、ひとまず「20」でチャレンジしてみましょう。
「For i = 2 To 11」を「For i = 2 To 20」と書き換えて。「F5」キーで実行、「Alt ＋ F11」キーでExcelを表示してみましょう。「インポートフォーム」Sheetはこうなるはずです ↓
「元データ貼付」Sheetにはない12行目以降の転記がおかしなことになってしまいました… そこで、またまた「技」を繰り出します。
さきほど確認をした「For i = 2 To 11」を次のように書き換えましょう ↓
クリックで拡大できます
少々難解なことになっているようにも見えますが。「最終行の数字」を取得するための公式として覚えてしまうのがよいでしょう。
これで、「元データ貼付」Sheetのデータが何行であろうと、自動的に変数「 i 」で対応できることになります。
あとは、いままでの復習で
これで、日付データの転記については終了です。
日付データ以外の転記が残っていますが、そこはこれまでのハナシを当てはめていけばできる部分です。説明抜きで解答を示します ↓
クリックで拡大できます
「F5」キーで実行してみましょう。「元データ貼付」Sheetのすべてのデータが、「インポートフォーム」Sheetに転記されます ↓
会計ソフトなどにインポートするだけならば、これでおしまいなのですが。列幅が狭くて、確認するにも見づらいよね… と言うのであれば。
列幅を調整するコードを付け加えておきましょう。コードの末尾である「End Sub」のひとつ前に、次のように一行だけ追加します ↓
クリックで拡大できます
「Worksheets(2).Columns(“A:H”)」は、ワークシートの２枚目（「インポートフォーム」Sheet）のAからH列、つまり、データがある列の範囲を表しています。「Autofit」が自動調整の指示です。
これで、すべてのデータがきれいに表示されるようになります ↓
マクロの記録をしたExcelファイルを保存する際には、「拡張子」に注意が必要です。通常は「.xlsx」ですが、マクロを含めて保存する場合には「.xlsm」になります。
誤って「.xlsx」で保存をしてしまうと、せっかくのマクロは保存されません。気をつけましょう。
まとめ
日付データを年月日に分解するマクロについて見てきました。
通常のExcel関数（ワークシート関数）でも対応できないわけではありませんが。より自動化・効率化を考えてマクロを利用する場面はあるでしょう。
今回ご紹介した「For〜Next」や「変数」、「最終行取得」は、他のケースでも利用ができますから、覚えておくと役立ちます。
＊＊＊＊＊＊＊＊＊＊＊＊
きょうの執筆後記
＊＊＊＊＊＊＊＊＊＊＊＊
ブログには書けない・書きにくいことその他。きょうの「執筆後記」は毎日メルマガでお届け中です。
よろしければメルマガ（無料）をご登録ください！ → 登録はこちらから
