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

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

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

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

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

前回は各種明細書のデータを開いて、データ部分を家計簿アプリの所定のシートに貼り付けるマクロを説明していきました。
今回は貼り付けたデータを確認し、勘定科目の照合をする関数の追加をやっていきましょう。

マクロを駆使して作っていくのも良いですが、関数を使って実現できるところは活用していった方が、マクロが作りやすかったり、動きも早くなる場合もあります。
マクロだけでなくExcel関数の使いこなしも、しっかり練習していきましょう。

【本記事の目標】

データ照合する関数を使いこなそう

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

事前準備リストとデータ照合する
それぞれの読み出しシートに照合関数を記入する

事前準備リストとデータ照合する

前回のマクロを起動することで、『カード明細用』のシートや『銀行明細用』のシートそれぞれに、ダウンロードしてきた明細書が貼り付けられている状態になっているかと思います。

ここに記載されている「支払先」もしくは「内容」に記載されている内容と、
その9-1で作った『事前準備リスト』の「内容」を比較していけば、『事前準備リスト』に記載できている内容については自動的に勘定科目を入力できるようになるはずです。

ではデータの照合は具体的にどうやったらいいのか？

これもExcel関数を使う方法とマクロを使う方法と両方ありますが、
今回はExcel関数を使う方法を試していこうと思います。

今回使う関数は

MATCH
OFFSET
IFERROR

この３つです。
それぞれの関数について説明していきます。

MATCH関数について

MATCH関数というのは

　あるセルにあるデータが、指定された1列もしくは1行の
　データリストの中のどこにあるかを探す

という機能を持った関数です。
この関数を使う場合は、下記のように3つの情報を指定する必要があります。

　MATCH（検索値、検索範囲、照合の種類）

それぞれ説明していきましょう。

検索値

検索値はそのまま、検索したいデータのことです。
ここには数値や文字列を直接入力することも可能ですし、１つのセルを指定することもできます。
数値や文字列を直接入力した場合は、その数値や文字列を検索しますし、
セルを指定した場合は、そのセルに記載されている内容を検索していきます。

使い方の例
　=MATCH(100,　・・・　　数値を直接入力
　=MATCH(“本日”,　・・・　文字列を直接入力
　=MATCH(A1,　・・・　　　セルを指定

検索範囲

検索範囲は、検索値に入力した内容を検索する範囲です。そのままですね。
ここには検索したい縦一列の範囲、もしくは横一行の範囲を指定します。
一列・一行であればいいので、「A列全て」や「10行目全て」といった指定も可能です。
また、必ずしもその指定した範囲全てにデータが入っている必要はなく、空欄があったとしても気にせず指定することができます。

使い方の例
　①　=MATCH(“検索値”,A:A,　・・・　　　A列全てを検索
　②　=MATCH(“検索値”,C2:C8,　・・・　　縦1列の6セルだけを検索
　③　=MATCH(“検索値”,10:10,　・・・　　10行目全てを検索
　④　=MATCH(“検索値”,E7:G7,　・・・　　横1行の3セルだけを検索

照合の種類

照合の種類は、検索の方法について指定をします。
検索の方法としては以下の3種類があり、それぞれ対応する数値を記入することで検索方法を指定することができます。

　①　対応数値：1　検索方法：以下検索
　　　（検索値以下の値で、検索値に最も近い値を探します。
　　　　検索値および検索範囲のデータが数値で、
　　　　かつ昇順で並んでいる必要があります。）

　②　対応数値：0　検索方法：完全一致
　　　（検索値と全く同じ値を探します。
　　　　数値・文字列どちらでも検索が可能です。）

　③　対応数値：-1　検索方法：以下検索
　　　（検索値以上の値で、検索値に最も近い値を探します。
　　　　検索値および検索範囲のデータが数値で、
　　　　かつ降順で並んでいる必要があります。）

使い方の例
　=MATCH(5,A:A,1)
　=MATCH(20,B:B,-1)
　=MATCH(“検索値”,C:C,0)

この関数で得られる値

この関数は検索値が検索範囲の中のどこにあるかを探す関数なので、
この関数で得られる値は、検索範囲の中で上から何番目、もしくは左から何番目の場所にあったか、という値が返ってきます。

例えば、下記のようにA列に数値が並んだデータがあり、その中から「８」を探したいとき
　・並んでいる数値の中で、8という数値がどこにあるか探したい
　　　　⇒　=MATCH(8,A4:A20,0)　⇒　上から6番目
　・A列中で、8という数値がどこにあるか探したい
　　　　⇒　=MATCH(8,A:A,0)　⇒　上から9番目
というように、検索範囲の中で何番目の場所にあるかの数値が返ってきます。

『検索範囲の中で』の場所なので、検索範囲の指定の仕方によって上記のように結果が変わるので、どの範囲で検索しているのかというのをしっかりと把握しておくのは非常に重要になります。

また、数値データの場合は【以上】【以下】の検索も可能です。
例えば、上記のようにA列に数値が並んだデータがあり、その中から「10」に一番近い値を探したいとき
　・並んでいる数値の中で、10という数値に一番近い値がどこにあるか探したい
　　　　⇒　=MATCH(10,A4:A20,1)　⇒　上から6番目
　・A列中で、10という数値に一番近い値がどこにあるか探したい
　　　　⇒　=MATCH(10,A:A,1)　⇒　上から9番目
という使い方になり、検索した値に一番近い値が、
検索範囲の中で何番目の場所にあるかの数値が返ってきます。

今回の場面での使い方

今回の場合、『カード明細用』シートの列にある「支払先」の内容が、『事前準備リスト』の「内容」の中のどれに合致するかが知りたいので、下記のような使い方になります。

　=MATCH(B2,事前準備リスト!A:A,0)

この関数を実行すると、下記のようにそれぞれの「支払先」の内容が『事前準備リスト』のどこにあるかが返ってきます。
ただし、『事前準備リスト』のどこにもない場合、エラーとして#N/Aが返ってきます。

MATCH関数は、検索値が検索範囲内に無かった場合、エラーとして返ってきます。
この特徴を使えば、検索値が検索範囲内にあるかどうか、ということも検索できるようになります。
これもMATCH関数を使ったテクニックの１つですので、知っておいて損はないかと思います。

OFFSET関数について

OFFSET関数というのは

　指定のセルから指定した数だけ移動した先にあるセル情報を表示する

という機能を持った関数です。
上記説明だけだとイメージしにくいかもしれませんが、図で書くと下記のようなイメージになります。

上記のように　=OFFSET(B2,5,2,3,2)　という関数を使うと、D7：E9を指定したのと同じ結果になります。
じゃあこれを何に使うの？というのがイメージが湧かないかもしれませんが、MATCH関数等と組み合わせると、その有用性が見えてきます。

その前にOFFSET関数の使い方を具体的に説明していきます。

この関数を使う場合は5つの情報を指定する必要があります。

　OFFSET（参照、行数、列数、高さ、幅）

それぞれ説明していきましょう。

参照

参照では、移動する際のスタート地点のセルを指定します。
ここで指定したセルを開始点として、
「行数」「列数」で指定した数値分だけ上下左右に移動していきます。

行数、列数

行数・列数は、参照で指定したセルから上下左右にいくつ分のセルを移動させるかを指定します。
「いくつ分のセルを移動させるか」を指定するので、ここには数値を入力する必要があります。
任意のセルを入力することも可能ですが、そのセルには数値データが入っている必要があります。

また行数は、プラスの数値を入力すると参照セルから下に移動していきます。
逆にマイナスの数値を入力すると参照セルから上に移動していきます。
一方列数は、プラスの数値を入力すると参照セルから右に移動していきます。
逆にマイナスの数値を入力すると参照セルから左に移動していきます。

使い方の例
　＝OFFSET(B2,5,2・・・)　　数値を直接入力
　＝OFFSET(B2,A1,A2・・・)　数値が入力されているセルを指定

高さ、幅

高さ・幅は、移動した先のセルからどのぐらいの範囲を選択するかを指定します。
「どのぐらいの範囲」を指定するので、ここも数値を入力する必要があります。
任意のセルを入力することも可能ですが、そのセルには数値データが入っている必要があります。
また、この高さと幅は省略することも可能です。
ただし、省略した場合は高さ＝１、幅＝１が入力されることになるので、１つのセルだけを選択することになります。

また、高さと幅は１以上の数値しか入力することができません。
また範囲が広がる方向も行数・列数と同じように、高さは下方向に、幅は右方向に範囲が広がります。
なので必然的に、行数・列数で指定して移動したセルは、高さ・幅で広げた範囲の左上に来ます。

使い方の例
　＝OFFSET(B2,5,2)　　　高さ・幅の入力を省略
　=OFFSET(B2,5,2,2)　　幅の入力を省略、高さの数値を直接入力
　＝OFFSET(B2,5,2,A1,A2)　　数値が入力されているセルを指定

今回の関数で得られる値

OFFSET関数は単純にセルを指定しているだけなので、
移動した後のセルに入力されている値がただ表示されるだけです。

ならそのセルを見に行けばいいじゃないか。と思われるかもしれませんが、
例えば非常にたくさんのデータ（100行×100列のデータとか）がある中の、上から57セル目、左から38セル目のデータが欲しい、となったとき、Excelシートをスクロールしながらセルを数えていくのは非常に手間ですよね。
そういった時に、OFFSET関数を使ってたくさんのデータの左上のセルを参照セルとして、行数を57、列数を38と入力すれば、一瞬で指定のセルのデータを取り出すことができるのです。

またOFFSET関数は範囲を指定できる、という点も非常に強みで、
この特徴はほかの関数と組み合わせたときにその効果を発揮します。

例えば　100行×100列のデータ　があったとき、上から57セル目、左から38セル目のセルと、そこから5セル下までのデータの中で合計値や最大値を知りたい、となったとき、
SUM関数やMAX関数を使ってそのセルの場所を指定するのも非常に手間ですよね。
そういった時に、OFFSET関数を使って、例えば下記のように
　=SUM(OFFSET(A1,57,38,5))
　=MAX(OFFSET(A1,57,38,5))
SUM関数やMAX関数とOFFSET関数を組み合わせることで、計算したいセルの場所を探しに行かなくても、計算式を作ることができます。

今回の場面での使い方

今回の場合、『カード明細用』のシートに記載の「支払先」の欄に記載されている内容が、『事前準備リスト』のシートに記載のリストの上から何番目に出てくるかが、MATCH関数を使って分かっているので、
その隣にある勘定科目を取り出すことも簡単ですよね。
実際に関数で書くと、下記のような使い方になります。

　=OFFSET(事前準備リスト!$A$1,
　　　　　　　　　MATCH(B2,事前準備リスト!A:A,0)-1,1)

この関数を実行すると、照合エリアに照合された勘定科目が表示されるようになります。
ただし、もともとMATCH関数でエラーが出ていたセルについては、そのままエラーが残った状態になっています。

IFERROR関数について

IFERROR関数というのは

　その数式・セルがエラー出なかった場合はその数式・セルの値を表示し、エラーだった場合は別の内容を表示する

という機能を持った関数です。
イメージとしてはIF関数と似ていますよね。
違う部分としては、IF関数は色々な条件式を指定することが可能ですが、IFERROR関数はその中でもエラーの有無だけの条件式に特化させたものになっています。

じゃあ、IFERROR関数はIF関数の下位互換か？というとそうでもありません。
IFERROR関数は『エラーの有無だけの条件式に特化』させているという性質のため、IF関数のような条件式が不要なのです。
なので、IFERROR関数を使った方がIF関数を使った場合よりも関数がシンプルになります。

具体的にIFERROR関数の使い方を説明していきましょう。

IFERROR関数では2つの値を指定する必要があります。

　IFERROR（値、エラーの場合の値）

それぞれ説明していきましょう。

値

この「値」には２つの意味があります。
　・この「値」に入力した値もしくは関数がエラーかどうかを判定する
　　　　⇒　IF文での条件式の機能
　・エラーでない場合に、入力した値もしくは関数の結果を表示する
　　　　⇒　IF文での『真』の場合の値
「エラーが出る可能性がある関数を入力したいけども、エラーじゃない場合は関数の計算結果を表示してほしいな～」
という値を入力します。
ですので、この「値」には基本的に関数を入力することが多いです。

使い方の例
　＝IFERROR(A1,・・・)　　A1セルがエラーじゃないときはA1セルの値
　＝IFERROR(SUM(A1:A10),・・・)　　SUM関数でエラーが出ないときはSUM関数の結果

エラーの場合の値

「エラーの場合の値」はそのまま、「値」に入力した値もしくは関数がエラーだった時に表示する値です。（IF文での『偽』の場合の値に相当）
エラー表示の、#N/A、#DIV/0!、#REF!、等を表示したくない場合に使うことが多いかと思いますので、””（空欄）や０を記入することが多いと思います。
もちろんそれ以外を入力してもらっても問題ありません。

使い方の例
　＝IFERROR(SUM(A1:A10),””)
　＝IFERROR(MATCH(A1,B:B,0),”エラーです”)

今回の場面での使い方

今回の場面だと、OFFSET関数とMATCH関数を組み合わせることで、
　=OFFSET(事前準備リスト!$A$1,MATCH(B2,事前準備リスト!A:A,0),1)
という関数を入力すれば照合ができるようになりましたが、
事前準備リストに記載がない場合は、この関数はエラーになってしまいます。

このままだと見た目も悪いですし、エラーをエラーのままで残しておくと、後々マクロやほかの関数を使う場合に不具合が出る場合があるので、消せる場合は消しておいた方が良いです。

このような場合にIFERROR関数を使うことで、エラー表示を消すことができます。
具体的には下記のように書くと、エラーだったところが空欄として表示されるようになります。

　=IFERROR(
　　　　OFFSET(事前準備リスト!$A$1,
　　　　　　　　MATCH(B2,事前準備リスト!A:A,0),1),””)

計算後の結果↓

それぞれの読み出しシートに照合関数を記入する

MATCH関数、OFFSET関数、IFERROR関数を使うことで、事前準備リストに記載されている内容と、読み出したデータの内容を照合し、内容に応じた勘定科目を表示することができました。
この関数を使えばどのシートでも使えるので、今回作った『カード明細用』シートと『銀行明細用』シートの両方にこの関数を記載しておきましょう。

記載する際の注意点として、マクロでファイルを貼り付ける可能性がある場所は避けて関数を記入しましょう。
でないと、次にファイルを読み出した際に、せっかく入力した関数が全て消えてしまいます。