- 公開日：
ExcelのVLOOKUP関数とCOUNTIF関数を組み合わせる方法
この記事では、ExcelのVLOOKUP関数とCOUNTIF関数を組み合わせる方法をご紹介します。
COUNTIF関数を組み合わせることで、検索値に該当する値が複数ある場合でも一番上の値しか抽出できないというVLOOKUP関数の弱点を解消することができます。
以下では、検索値に一致する値をすべて抽出する方法と、さらに「&」を組み合わせて、検索値を複数条件を指定する方法をご紹介します。
ExcelのVLOOKUP関数とCOUNTIF関数を組み合わせる方法
以下では、VLOOKUP関数とCOUNTIF関数を組み合わせて条件に当てはまるデータをすべて抽出する方法を3つご紹介します。
- 1つの条件に当てはまる値をすべて抽出する方法
- 複数条件に当てはまる値をすべて抽出する方法
- 特定の数値以上以下の値をすべて抽出する方法
1つの条件に当てはまる値をすべて抽出する方法
上記の画像のような表を用意します。
上の表の「出荷表」から「出荷日」2023/9/2のデータをすべて下の表に抽出していきます。
A列を作業列にするので1列空けてデータを入力してください。
C16セルからF16セルはVLOOKUP関数で指定する列番号を入力します。抽出するのは左から数えて3列目以降なので「3」から連番を振ります。
B18セルからB22セルはVLOOKUP関数の検索値になるので1から連番を振ります。
以下で、VLOOKUP関数とCOUNTIF関数を組み合わせて「2023/9/2」のデータを抽出する手順をご紹介します。
A3セルに「=COUNTIF($B$3:B3,$C$15)」を入力します。
引数に指定する範囲の最初のセルと検索条件は変わらないので、参照がずれないように「$」で固定します。
COUNTIF関数は1つの条件を満たすセルの数を求めます。書式は「=COUNTIF(範囲,検索条件)」です。
使い方や応用方法の詳細は、以下の記事でご紹介しています。
ExcelのCOUNTIF関数の使い方｜条件に一致するデータの個数表示
「検索条件」2023/9/2はB3セルからB3セルの間に無いので、A3セルに0が表示されました。
A3セルのフィルハンドルをドラッグして数式をコピーします。
A3セルからA13セルにCOUNTIF関数の結果が表示されました。
A8セルからA13セルに4が続いているのは「検索条件」2023/9/2がA9セル以降に存在しないためです。
C18セルに「=VLOOKUP($B18,$A$3:$F$13,C$16,FALSE)」を入力して、Enterを押します。
この際、セルを固定する位置を間違えると参照がずれてしまうので、ご注意下さい。
C18セルにVLOOKUP関数の結果が表示されました。
C18セルのフィルハンドルをドラッグして数式をコピーします。
列番号を入力しておくことで手動で入力する手間を減らすことができます。
D18セルからF18セルに数式がコピーされました。
①C18セルからF18セルを選択します。②フィルハンドルをドラッグして数式をコピーします。
「出荷日」2023/9/2に該当するすべての値を抽出することができました。
C22セルからF22セルは、「出荷日」2023/9/2に該当する「製品」が4つしかないため、表の「5」には値が存在しない場合に表示される#N/Aエラーが表示されています。
複数条件に当てはまる値をすべて抽出する方法
上記の画像では、I3セルの検索値を「品種群」紅茶、コーヒー、お茶、ハーブティーと入れ替えることで、2023/9/1の売上表から品種群別の売り上げを抽出することができます。
検索値にしたい条件が2つ以上ある場合、「&」を組み合わせることで複数条件に当てはまる値をすべて抽出することができるので、より柔軟な検索をすることが可能です。
詳細は、以下の記事「&とCOUNTIF関数を組み合わせて検索値をすべて抽出する方法」セクションでご紹介しています。
ExcelのVLOOKUP関数で&でつないで検索値を複数にする
特定の数値以上以下の値をすべて抽出する方法
上記の画像のようなデータを用意します。
A列を作業列にするので1列空けてデータを入力してください。
E3セルからE7セルはVLOOKUP関数の検索値になるので1から連番を振ります。
以下で、左側の「テスト結果」の表から「点数」60以上に当てはまる「氏名」を右側の表にある「合格者」に表示する方法をご紹介します。
A3セルに「=COUNTIF($C$3:C3,">=60")」を入力して、Enterを押します。
参照がずれないように引数に指定する範囲の最初のセルを「$」で固定します。
「以上」「以下」などを指定する比較演算子の記号の種類や、COUNTIF関数での使い方は以下の記事でご紹介しています。
C3セルのセル値「40」は、指定した条件の「60以上」に当てはまらないのでA3セルに0が表示されました。
A3セルのフィルハンドルをドラッグして数式をコピーします。
A3セルからA7セルに数式がコピーされました。
F3セルに「=VLOOKUP(E3,$A$3:$C$7,2,FALSE)」を入力して、Enterを押します。
引数に指定する範囲は、数式をコピーする際にずれないよう「$」で固定してください。
F3セルに結果が表示されました。
F3セルのフィルハンドルをドラッグして数式をコピーします。
F3セルからF5セルに「テスト結果」の表から「点数」60以上に当てはまる「氏名」を表示することができました。
F6セルとF7セルは検索値が存在しないため#N/Aエラーが表示されています。