条件に合致する複数のレコードを請求データ一覧から取得し、請求書にリストアップする方法(マクロなし、MATCH関数を使用)


タイトル通りです。レコードを並び替える必要もありません。結構自信作かも。

まず、「請求書」シートと「請求データ」シートを用意します。

「請求データ」のA1:G21に「請求リスト」という名前を定義してください。

皆さんが作りたいものはそれぞれ異なるので、細かな作り込みの解説は割愛し、重要な部分だけを解説します。

肝は、Match関数の使い方です。請求書シートの作業用セル(灰色部分)に注目してください。

C列に下記のMATCH関数を入力し、G3の検索値検索範囲(「請求データ」のE列)から探します。

要点を分かりやすくするため、簡単に書くとこうなります。

=MATCH(検索値, 検索範囲, 0)

B列に入っている文字列(例えば、7行目の「請求データ!E1:E21」)は検索範囲を意味しています。この文字列が指す検索範囲を参照するためにINDIRECT関数を使っています。

(ちなみに、最後の引数0は完全一致を意味します。)

=MATCH(検索値, INDIRECT(検索範囲を指す文字列), 0)

次にA列とB列に注目してください。

7行目、8行目、9行目、と検索開始行がずれていっていますね。

便宜上、それぞれ、1回目の検索、2回目の検索、3回目の検索と呼び、もう少し詳しく解説します。

まず、1回目の検索で、請求リストの1行目から探し、 6行目で検索値を見つけました。

次に、2回目の検索で、請求リストの7行目から探し、 12行目で検索値を見つけました。

最後に、3回目の検索で、請求リストの13行目から探し、 18行目で検索値を見つけました。

要するに、検索値が見つかる→検索値が見つかった行の次の行から検索を開始→検索値が見つかる→次の行から検索、というを繰り返しているだけです。当然ですが、繰り返しの回数は請求書の行数が上限です。

作業用セルの式は下記のようになっています。

B列の「検索範囲」は、「検索列」を示すF4、「最終行」を示すE4 、それぞれの値によって動的に変化します。

また、今回は顧客名と請求月度の2つを検索キーにし、合計4通りの検索条件を設けることができるようにしました。

1. 両方が入力されている場合(AND検索)  → E列を検索

2. いずれか一方だけが入力されている場合 → C列またはD列を検索

3. いずれも入力がない場合        → E列を検索

E列は顧客名と請求月度を連結しただけです。日付がシリアル値になっていますが、問題はありません。

ちなみに、検索値を入力する際に「Google」の代わりに「G*」や「*oo*」とワイルドカードを使っても同じ検索結果を得られます。MATCH関数って面白いですね。

あとは、検索値が見つかった行を基に、顧客名や摘要など各項目の値を参照するだけです。参照にはINDEX関数を使います。下は摘要を参照するための式です。検索値が見つからない場合は空白にします。

=IF(ISERROR(INDEX(請求データ, $D7, 6)), "", INDEX(請求データ, $D7, 6))

$D7には、検索値が見つかった行の行番号が入っています。6は請求データの6列目という意味です。

あとがき

前からこういうのをマクロ無しで作れないものかと、いろいろ試しては挫折していました。そんなに複雑な仕組みではないのですが、いろんな場面で応用できそうな気がします。

#Excel #MATCH関数