- 公開日：
ExcelのVLOOKUP関数とCOUNTIFS関数を組み合わせる方法
この記事では、ExcelのVLOOKUP関数とCOUNTIFS関数を組み合わせる方法をご紹介します。
COUNTIFS関数を組み合わせることで、複数条件を指定することができます。また、検索値が複数ある場合にすべての値を抽出することが可能です。
以下では、COUNTIFS関数を組み合わせて様々な条件を指定する方法をご紹介しています。
ExcelのVLOOKUP関数とCOUNTIFS関数を組み合わせる方法
COUNTIFS関数を使って重複している検索値にナンバリングすることで検索値が異なる値となるので、検索値が重複している場合に一番上の値しか返せないというVLOOKUP関数の弱点を克服することができます。
上記の画像のようなデータ用意します。
A列は作業列に使用します。VLOOKUP関数で検索値を探す列になるので必ずデータの左端を1列空けてください。
H2セルとI2セルは、COUNTIFS関数の引数に指定する条件を入力します。
H5セルからH9セルは、検索値として指定するので連番を振ります。
以下では、VLOOKUP関数とCOUNTIFS関数を組み合わせて複数条件を指定する方法をご紹介します。
指定した文字列に当てはまる値をすべて抽出する方法
以下では、VLOOKUP関数とCOUNTIFS関数を組み合わせて、指定した2つ以上の「文字列」に当てはまる値をすべて抽出する方法をご紹介します。
COUNTIFS関数の引数の検索条件を指定します。
H2セルに条件1（例：アパレルB）、I2セルに（例：渋谷1号店）を入力します。
A2セルに「=COUNTIFS($B$2:B2,$H$2,$C$2:C2,$I$2)」を入力して、Enterを押します。
「ブランド」がアパレルBであり、「店舗」が渋谷1号店に当てはまるセルの数を数えます。
引数に指定する範囲の最初のセルと検索条件は変わらないので、参照がずれないように「$」で固定します。
COUNTIFS関数は、選択した範囲内に指定した条件に一致するセルがいくつあるのかを求めます。検索条件は最大で127個指定することが可能です。書式は「=COUNTIFS(条件範囲1,検索条件1,[条件範囲2,検索条件2],…)」です。
COUNTIFS関数の書式の詳細は、以下の記事「COUNTIFS関数の書式」セクションでご紹介しています。
ExcelのCOUNTIFS関数の使い方｜複数条件に一致するデータを数える
指定した範囲のセルが条件を満たしていないので、A2セルに0が表示されました。
A2セルのフィルハンドルをドラッグして数式をコピーします。
A3セルからA13セルに数式がコピーされました。
I5セルに「=VLOOKUP(H5,A:F,4,FALSE)」を入力して、Enterを押します。
I5セルに結果が表示されました。
I5セルのフィルハンドルをドラッグして数式をコピーします。
I5セルからI9セルに「ブランド」がアパレルBであり「店舗」が渋谷1号店に当てはまる値に紐づく「名前」をすべて抽出することができました。
条件に一致する値が3つしかないため、I8セルとI9セルには検索値が存在しない場合に表示される#N/Aエラーが表示されています。
ワイルドカードで部分一致した文字列に当てはまる値をすべて抽出する方法
以下では、部分一致した特定の文字列を条件に指定して、任意の文字列に当てはまる値をすべて抽出する方法をご紹介します。
COUNTIFS関数の引数の検索条件を指定します。
H2セルに条件1（例：????A）、I2セルに（例：渋谷*）を入力します。
「?」や「*」はあいまいな値を検索する際に使用するワイルドカードです。
「?」は文字数を表しています。例では「アパレル」が4字なので「?」を4つ付けています。
「*」は文字列の前後に1つ付けることで文字数に関係なく文字列に置き換えます。例えば「渋谷*」と入力することで、「渋谷」以降の文字列が異なっていても「渋谷1号店」「渋谷2号店」の両方を条件にすることができます。
ワイルドカードの詳しい説明は、以下の記事でご紹介しています。
A2セルに「=COUNTIFS($B$2:B2,$H$2,$C$2:C2,$I$2)」を入力して、Enterを押します。
「ブランド」が任意の4字（????）+Aであり、最初の文字列が「渋谷」から始まる「店舗」に当てはまるセルの数を数えます。
引数に指定する範囲の最初のセルと検索条件は変わらないので、参照がずれないように「$」で固定します。
指定した範囲の値が条件を満たしているので、A2セルに1が表示されました。
A2セルのフィルハンドルをドラッグして数式をコピーします。
A3セルからA13セルに数式がコピーされました。
I5セルに「=VLOOKUP(H5,A:F,4,FALSE)」を入力して、Enterを押します。
I5セルに結果が表示されました。
I5セルのフィルハンドルをドラッグして数式をコピーします。
I5セルからI9セルに、「ブランド」が任意の4字（????）+Aであり、最初の文字列が「渋谷」から始まる「店舗」に当てはまる値に紐づく「名前」をすべて抽出することができました。
条件に一致する値が4つしかないので、I9セルには検索値が存在しない場合に表示される#N/Aエラーが表示されています。
指定した数字の範囲に当てはまる値をすべて抽出する方法
COUNTIFS関数の引数の検索条件を指定します。
H2セルに条件1（例：>=1000000）、I2セルに（例：>1000000）を入力します。
比較演算子の使い方は、以下の記事「COUNTIFS関数で使える比較演算子」セクションでご紹介しています。
ExcelのCOUNTIFS関数の検索条件で「以上」「以下」を指定する方法
A2セルに「=COUNTIFS($E$2:E2,$H$2,$F$2:F2,$I$2)」を入力して、Enterを押します。
「先月売り上げ」が1000000以上であり、「今月売り上げ」が1000000より大きいセルの数を数えます。
引数に指定する範囲の最初のセルと検索条件は変わらないので、参照がずれないように「$」で固定します。
指定した範囲の値が条件を満たしているので、A2セルに1が表示されました。
A2セルのフィルハンドルをドラッグして数式をコピーします。
A3セルからA13セルに数式がコピーされました。
I5セルに「=VLOOKUP(H5,A:F,4,FALSE)」を入力して、Enterを押します。
I5セルに結果が表示されました。
I5セルのフィルハンドルをドラッグして数式をコピーします。
I5セルからI9セルに、「先月売り上げ」が￥1,000,000以上であり、「今月売り上げ」が￥1,000,000より大きい値に紐づく「名前」をすべて抽出することができました。
条件に一致する値が3つしかないので、I7セルからI9セルには検索値が存在しない場合に表示される#N/Aエラーが表示されています。