マクロでオートフィルターを使うと、自動で特定の年月のデータを抽出することができます。
Excelシートに複数年のデータがある場合の解決策をまとめてみました。
オートフィルターでできること
オートフィルターは、特定のデータだけを抽出することができる機能。
次のようなデータがあったときに、ショートカットキー（Ctrl+Shift+L）から各項目に表示された「▼」をもとに特定のデータを選べます。
たとえば、6月のデータだけを表示させたいときは、日付の「▼」をクリックし、6月だけにチェックを入れれば、
6月のデータだけを表示させることができます。
このオートフィルターをExcelマクロで自動化するという記事を以前に書いています。
Excelマクロでオートフィルター。効率化は小さく体験することから。 – GO for IT 〜 税理士 植村 豪 Official Blog
複数年分のデータがExcelシートにある場合、オートフィルターで年月で範囲指定してデータを抽出することができます。
この流れをマクロで自動化してみようというのが今回のはなしです。
会計データを1つのファイルにまとめているケース
このようなデータをExcelなら年ごとにファイルを変えずとも数年分のデータをまとめて1枚のシートにまとめることもできます。
また、そのデータを関数をつかって会計データに置き換えて、
マクロでオートフィルターを使うことで、特定月の会計データをだけを自動的に抽出することができます。
ただ、複数年のデータが1枚のシートにある場合に「月」だけでオートフィルターをかけると、2022年だけでなく2021年以前の特定月のデータが抽出されることになります。
そこで、オートフィルターで特定の「月」に加えて「年」でも抽出できるようにしたいものです。
流れとしては、
- 特定の年を指定
- 特定の月を指定
- オートフィルターで特定の年月で抽出
という流れです。
これをマクロで書くと、こんな感じです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub Filter_year_month() '■変数の宣言 Dim Filter_year Dim Filter_month '■年月の指定 Filter_year = InputBox("抽出したい年を指定") Filter_month = InputBox("抽出したい月を指定") '■年月でフィルタリング Dim S As Date, E As Date S = DateSerial(Filter_year, Filter_month, 1) E = WorksheetFunction.EoMonth(S, 0) Range("o3").AutoFilter 4, ">=" & S, xlAnd, "<=" & E End Sub |
このマクロを解説してみます。
期間指定してオートフィルター
では上から順番に見ていきます。
抽出したい年月を変数で指定する
まずはマクロで抽出したい年と月を指定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub Filter_year_month() '■変数の宣言 Dim Filter_year Dim Filter_month '■年月の指定 Filter_year = InputBox("抽出したい年を指定") Filter_month = InputBox("抽出したい月を指定") |
何年の何月という条件は毎回変わるので、変数として「Filter_year（年）」「Filter_month（月）」としています。
変数というのは、ざっくりいえばそのときによって変えることのできる数字。
Filter_year = InputBox(“抽出したい年を指定”)で「2022」を入力すれば、変数「Filter_year」は2022となり、
続いて表示される月で「6」と入力すれば、変数Filter_monthは「6」となります。
これでお気づきになったかもしれませんが、「2022年6月のデータを抽出する」ということになります。
年と月はじぶんが任意に選べるわけです。
期間を指定する
次に先程指定した変数をもとにオートフィルターを自動でかけたいわけですが、次のように書いています。
|
1 2 3 4 5 6 7 8 9 |
'■年月でフィルタリング Dim S As Date, E As Date S = DateSerial(Filter_year, Filter_month, 1) E = WorksheetFunction.EoMonth(S, 0) Range("o3").AutoFilter 4, ">=" & S, xlAnd, "<=" & E |
S As Date, E As Dateで「S」と「E」という2つの変数を使うことにし、これを日付データとしています。
また年月を指定するのに、期間で設定しています。
たとえば、2022年6月のデータを抽出したい場合には、「2022/6/1〜2022/6/30」までというようにです。
それを2つの変数「S（1日）」と「E（末日）」で指定しています。
まずは変数「S（初日）」のほうから見てみます。
|
1 |
S = DateSerial(Filter_year, Filter_month, 1) |
DateSerialという関数をつかって、DateSerial(year,month,day)として日付のシリアル値を出すことができます。
これを踏まえると、最初にボックスで入力した「年：2022（Filter_year）」、「月：6（Filter_month）」に、最後に「1」を指定することで「2022/6/1」とできたわけです。
次に変数「E（末日）」です。
|
1 |
E = WorksheetFunction.EoMonth(S, 0) |
Excelの関数の1つに「=EOMONTH（指定日,数字」という関数があります。End Of Month（月末）の略です。
たとえば、A1セルに2022/7/1と入力されていて、B1セルに=EOMONTH（A1,1)とした場合、1は1ヶ月後の末日。
結果、「2022/8/31」となります。
これを踏まえて。
マクロでExcelの関数を使うときには「WorksheetFunction.」をつけてExcelの関数を使います。
|
1 |
E = WorksheetFunction.EoMonth(S, 0) |
とすることで、すぐ上の変数「S（初日）」の0ヶ月先ということになり、6月30日が変数「E」になるわけです。
でその変数「S」「E」を使って、「6月1日以上6月30日以下のデータでオートフィルターをかけてちょ」とお願いをします。
上記のデータでは「O3」のセルが日付の項目。これをもとにオートフィルターをかけています。
|
1 |
Range("o3").AutoFilter 4, ">=" & S, xlAnd, "<=" & E |
4というのは、範囲データの左から数えて4列目（Lから数えて）ということを意味しています。
「xlAnd」というのは、マクロ版の「SかつE」ということです。
これを動かすと、6月分のデータだけを自動で抽出してくれるようになります。
あとは、このデータをマクロを使ってCSVファイルに保存して、会計ソフトにインポートするといったこともできます。
Excel1枚にデータをまとめておけば、データの加工もしやすいです。ただ、せっかくならそのデータを経理にも利用したいもの。オートフィルターを使うと便利です。
ということで。参考にしていただければうれしいです。
【編集後記】
昨日は株価評価をしてからマクロの手入れなどを。夜は子どもたちと遊んだりという1日でした。
【昨日の1日1新】
※「1日1新」→詳細はコチラ
フロプレステージュ タルト
