ExcelVBAで家計簿アプリを作ろう　その9-4

家計簿アプリを、より使いやすく自分仕様に改造していきましょう！

以前の投稿で、家計簿アプリとして最低限の機能を持たせることができました。
（その６までをご確認いただいていない方は、まずは下記まとめをご確認ください。）

ExcelVBAで家計簿アプリを作ろう　その1~6まとめ

本記事はExcelマクロで作る家計簿アプリの作り方実践・説明の記事のまとめです。その１～その６についての概要説明と説明内容の詳細記載、各記事へのリンクを記載しています。

yorozuya-tanuki.com

ただ、これは本当に最低限の機能しかなく、勘定科目も『固定費』と『変動費』しかないので、
各家庭に合った勘定科目を自由に追加できるように、
このマクロのデコレーションをしていく必要があります。

前回からダウンロードしてきた明細書を基に、おおよそでリストに記入できるようにしていくマクロの作成に取り掛かっています。

前回は貼り付けたデータを確認し、勘定科目の照合する関数を追加し、それぞれの項目に対応する勘定科目を記入することができました。
今回はその内容をの中から、所定の月のデータにフィルターをかけ、必要な月のデータだけ『入力』シートに貼り付ける作業ををやっていきましょう。

フィルター操作ができると、マクロでデータ整理できる幅がまた一段と広がります。
頑張って慣れていきましょう！

簡単なイメージは下記記事に記載していますので、イメージづくりや今回以降の流れの参考に。
また、前回までの記事については下記をご確認ください。

ExcelVBAで家計簿アプリを作ろう　その7以降の予定

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では、これまでに作った家計簿アプリに今後追加実装していきたい内容・今後説明していこうと考えている内容について説明。

yorozuya-tanuki.com

ExcelVBAで家計簿アプリを作ろう　その7-1

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では、今後に向けたシートの修正と、テーブルの設定、ドロップダウンリストの設定について説明。

yorozuya-tanuki.com

ExcelVBAで家計簿アプリを作ろう　その8-1

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では、似た操作・動きを１つのFunctionにし、引数の違いで動きを変えるマクロの作り方について説明。

yorozuya-tanuki.com

ExcelVBAで家計簿アプリを作ろう　その9-1

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では、ダウンロード明細書から家計簿に自動入力していくためのマクロを作るために、操作の流れのイメージについて説明。

yorozuya-tanuki.com

フィルター機能について

ご存じの方も多いと思いますが、Excelには選択した領域の中で指定した条件に合致するデータだけを表示してくれる「オートフィルター」という機能があります。

Excel上での機能を使う場合は、
　１．フィルターをかけたいデータ領域を選択
　２．『データ』タブの『フィルター』を選択
　３．選択したデータ領域の1行目に▼マークが表示される
　４．フィルターをかけたいデータがある列の▼マークをクリックし、
　　　かけたいフィルターの内容を選択する
という手順で、データにフィルターをかけて表示することができます。

このフィルターをかけた後、改めてデータ領域を選択してコピー&ペーストすると、フィルターをかけた状態のデータ（表示されているデータ）を貼り付けることができます。

この手順をそのままマクロで作ってしまえば、所定の月のデータだけを取り出して、簡単に『入力』のシートに貼り付けることができてしまうのです。

マクロでフィルターを使う

マクロ上で『オートフィルター』を使う場合のコマンドも同じように『AutoFilter』というコマンドを使います。

実際に使用する場合は、下記のような書き方で使用します。

　Range(「フィルターかけたい領域」).AutoFilter _
　　Field:= ~, _
　　Criteria1:= ~, _
　　Operator:= ~, _
　　Criteria2:= ~, _
　　VisibleDropDown:= ~

Excelマクロ上でオートフィルターを使う場合は、最大で6個のデータを指定してあげる必要があります。1つずつ説明していきましょう。

Range(「フィルターをかけたい領域」)

最初に指定するのはフィルターをかけたい領域です。
これは単純にデータがある領域ですね。

例えば、アクティブシート上の”A1:F16″の領域にフィルターをかけたい場合は、
　ActiveSheet.Range(“A1:F16”).AutoFilter
になりますし、1行目1列目のセルから100行目6列目のセルまでを選択した領域にフィルターをかけたい場合は、
　With ActiveSheet　
　　.Range(.Cells(1,1),.Cells(100,6)).AutoFilter
　End with
と書くことで好きな領域を選択することができます。

Field:=~

Fieldにはフィルターをかけたいデータの列番号を入力します。
番号は一番左側が１になります。

たとえば3列目のデータでフィルターをかけたい場合は、
　ActiveSheet.Range(“A1:F16”).AutoFilter Field:=3
と書けば3列目のデータでフィルターをかけることができます。

Criteria1:= ~, _

Criteria1ではフィルターをかけたいフィルター条件を入力します。
ここでどういった条件を入力すべきか、については、次のOperatorで指定する条件によって変わってきます。

たとえばOperatorでセルの値がどうなっているか、を調べる条件になっていた場合、
文字列でフィルターをかけることになるので、Criteria1では文字列を入力します。
　ActiveSheet.Range(“A1:F16”).AutoFilter Field:=3 Criteria1:=”文字列”
一方「上位○個のデータを表示する」といった形式の条件になっていた場合、
何個のデータを表示するのか、といった数値データが必要になるので、Criteria1には数値データを入力することになります。
　ActiveSheet.Range(“A1:F16”).AutoFilter Field:=3 Criteria1:=10

このようにOperatorで設定する条件によって、フィルターのかけ方が変わってくるので、
気を付けておきましょう。

Operator:= ~, _

Operatorは上記でも説明した通り、検索する方法・手法を指定します。
イメージとしては、オートフィルターの▼マークをクリックしたときに出てくるディスプレイで、
フィルターのかけ方を選択する作業と同じです。

使用できる設定値は下記の11種類です。

1. xlFilterValues ： フィルターの値
2. xlAnd ： フィルター条件 1 で、かつフィルター条件２でもある
3. xlOr ： フィルター条件 1 、もしくはフィルター条件２
4. xlTop10Items ： 表示される最高値項目 (フィルター条件 1 で指定される項目数)
5. xlTop10Percent ： 表示される最高値項目 (フィルター条件 1 で指定される割合)
6. xlBottom10Items ： 表示される最低値項目 (フィルター条件 1 で指定される項目数)
7. xlBottom10Percent ： 表示される最低値項目 (フィルター条件 1 で指定される割合)
8. xlFilterCellColor ： セルの色
9. xlFilterDynamic ： 動的フィルター
10. xlFilterFontColor ： フォントの色
11. xlFilterIcon ： フィルターアイコン

ただ、この中で最も使用頻度が高いとしたら1つ目の『xlFilterValues』かと思います。
今回もこれを使用します。

Criteria2:= ~, _

Criteria2は、フィルターをかけたい2つ目のフィルター条件です。

Operetorで指定できる項目に　xlAnd　xlOr　があったと思いますが、これらを使用する場合に指定します。
使用方法はCiteria1と同じです。

VisibleDropDown:= ~

VisibleDropDownでは、オートフィルターを起動したときに出てくる▼マークを表示させるかどうか、を設定します。

Trueを入力した場合は▼マークが表示されます。
Falseを入力した場合は▼マークが表示されません。

すべて省略した場合

これまでに説明した項目は「フィルターをかけたい領域」以外は全て省略が可能です。
ただし、その場合は個々の項目を省略した場合と異なり、
　シートのオートフィルターを適応させるか、解除するか
という切り替えを行います。
すでにシート上にオートフィルターが適応されている場合は解除しますし、
オートフィルターが設定されていないのであれば、オートフィルターが適応されます。

今回のマクロでオートフィルターを使っていく

では実際に「家計簿アプリ」の方でこのオートフィルターを使っていこうとした場合、
どう入力していけばいいのか。

必要なのは、
　１．どの領域にフィルターをかけたいか
　２．どんな文字列でフィルターをかけたいか
この2点です。

フィルターをかけたい領域を設定する。

今回フィルターをかけたい領域は、「カード明細」のシートの場合は下記図の中の青線の領域です。

ただしこの領域は、読み込んでくるファイルによって何行目までデータがあるかわかりませんよね。
なので、最終行がどこにあるのかを確認する必要があります。

最終行の確認方法は下記記事にも記載していますので、詳細は省きますが、
下記コマンドで最終行の取得が可能です。

　Dim EndRow1 as Integer
　EndRow1 = ThisWorkbook.Sheets(“カード明細用”).Cells(ThisWorkbook.Sheets(“カード明細用”).Rows.Count, 1).End(xlUp)

ExcelVBAで家計簿アプリを作ろう　その1-3

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では最終行の探索、Range・Cellの違い、図形クリックによるマクロ起動について説明しています。

yorozuya-tanuki.com

最終行が分かってしまえば、
　最初の行：１行目
　最初の列：１列目
　最後の列：６列目
と全ての行と列の数が判明しましたので、下記の領域が「フィルターをかけたい領域」になります。

　With ThisWorkbook.Sheets(“カード明細用”)
　　.Range(.Cells(1,1), .Cells(EndRow1,6))
　End With

フィルターをかけたい文字列を取得する

今回フィルターをかけたい文字列は、『○年○月』　というデータです。
そもそもこの文字列は『入力』シートの”M4″セルと”O4″セルに記載されていますので、この値をとってくればOKです。

　Dim SearchYear1 as Integer
　Dim SearchMonth1 as Integer
　With ThisWorkbook.Sheets(“入力”)
　　SearchYear1 = .Range(“M4”).Value
　　SearchMonth1 = .Range(“O4”).Value
　End With

この○年○月のデータを使って、『カード明細用』シートや『銀行明細用』シートに記載されている「日付」列にあるデータを抽出していきますが、「日付」列にある日付はシートごと（読み込んだファイルごと）に異なっている可能性があります。
　（2023.4.0、2023年4月0日、2023/4/0　等）
ですので、各シートに合わせたフィルター条件を設定する必要があります。

ややこしく書いていますが、要は、

　読み込んだファイルが「2023年4月0日」の表記だった場合のフィルター条件
　　　⇒　SearchYear1 & “年” & SearchMonth1 & “月” & “*”

という形で、読み込むファイルの日付の入力形式を確認し、
それに合わせた表示方法にし、かつ“日”以降は未定（何日でもOK）なので、ワイルドカードも使用して入力してあげればOKなのです、

上記を踏まえてフィルターを設定する

以上を踏まえて、『カード明細用』シートにフィルターを設定するマクロは下記のようになります。
フィルターをかける動作はファイルを読み込むと同時に実行した方が動作が減るので、『CardMeisaiCopy』のプロシージャ内に追記しています。

Option Explicit

Sub CardMeisaiCopy()
'カード明細サンプルを貼り付けるマクロ

Dim FileName1 As String

'ファイルを開く
FileName1 = Application.GetOpenFilename
Workbooks.Open Filename:=FileName1

'ファイルのデータを所定のシートに貼り付ける
ActiveWorkbook.Sheets(1).Range("A:C").Copy
ThisWorkbook.Sheets("カード明細用").Range("A:C").PasteSpecial
Application.CutCopyMode = False

'ファイルを閉じる
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

'===========オートフィルター部分==============

Dim SearchYear1 As Integer
Dim SearchMonth1 As Integer
Dim EndRow1 As Integer
Dim FilterString1 As String     'フィルター条件の文字列

  
With ThisWorkbook.Sheets("入力")
    SearchYear1 = .Range("M4").Value
    SearchMonth1 = .Range("O4").Value
    FilterString1 = SearchYear1 & "." & SearchMonth1 & "*"
End With
  
With ThisWorkbook.Sheets("カード明細用")
    EndRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range(.Cells(1, 1), .Cells(EndRow1, 6)).AutoFilter Field:=1, Criteria1:=FilterString1, Operator:=xlFilterValues
    
    '以降でフィルターをかけた領域をコピーし、「入力」シートに貼り付けていく
    
    
    
    
    .Range(.Cells(1, 1), .Cells(EndRow1, 6)).AutoFilter オートフィルターの解除
    
End With

End Sub

フィルター領域のコピーに向けて

これであとはフィルターをかけた領域をコピーして「入力」のシートに貼り付けていけばいいのですが、例えばフィルターをかけた『勘定科目』の欄を「入力」のシートの『勘定科目』の欄に貼り付けようと思うと、下記のような書き方になってしまいます。

With ThisWorkbook.Sheets("カード明細用")
.Range(.Cells(1, 6), .Cells(EndRow1, 6)).Copy
    
ThisWorkbook.Sheets("入力").Range(ThisWorkbook.Sheets("入力").Cells(8, 4), ThisWorkbook.Sheets("入力").Cells(8 + EndRow1 - 1, 4)).PasteSpecial Paste:=xlValue
end with

これでも問題はないのですが、非常に煩雑ですよね。
With関数を使って「カード明細用」のシートは省略して書くことができますが、
「入力」のシートまでは省略できません。
（With関数の中にWith関数を入れることはできますが、どちらにせよ2種類同時に省略はできません）

なので別の方法で、
具体的には、変数でシート自体を定義してしまってより分かりやすい書き方に変えていこうと思います。

また、現状だと「入力」シートは下記のような項目になっていますが、
明細書からせっかくコピーするのであれば、「日付」や「内容」等の情報も入力しておきたいですよね。

この辺りの細々した修正に関しては、次回やっていきたいと思います。

次の記事へ

ExcelVBAで家計簿アプリを作ろう　その9-5

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では、オブジェクトの変数定義の方法と、ダウンロードファイルからの自動入力マクロの最終仕上げについて説明。

yorozuya-tanuki.com