エクセルで同じキーワードのセルをすべて抽出したいときは、フィルター機能が便利です。しかし、抽出したデータを加工したいときに、フィルター機能で抽出してから該当範囲をコピーして別のシートに貼り付ける、といった作業を繰り返すのは非効率的です。本記事では、関数を使用して特定のキーワードのセルをすべて抽出する方法を紹介します。
はじめに
技術的に大切なポイントは赤色のアンダーラインで、アイデアとして大切なポイントは黄色のアンダーラインで強調しています。また、関数の記述は黒いボックス内にあります。ポイントだけ読みたい方は活用してください。
完成形の概要
一例として、A列からC列のリストの中から、セルE2に入力した品目と一致するデータをすべて抽出して表示する場合を紹介します。今回の例では完全一致かつ検索条件はひとつにしていますが、数式の作り方を変えれば部分一致や複数条件での抽出も可能です。
今回の例におけるポイントは、G列の数式です。数式自体は比較的容易なので、エクセルの関数に詳しくない方でも少し頑張れば使えるようになると思います。
H列からJ列はひとつの簡単な数式をオートフィルするだけです。
G列の意味が理解できれば、さまざまな環境に合わせられるでしょう。
G列の数式・その1（セルG2）
セルG2の数式は次のとおりです。
=IFERROR(MATCH($E$2,$B$2:$B$8,0)+1,"")この数式では、単純にMATCH関数で検索しているだけです。
MATCH関数では、検査値にセルE2の文字列を指定し、検査範囲をセルB2からセルB8と指定して検索しています。セルE2は検索したい文字列を入力するセルのため、数式は入っていません。
抽出したいデータの行番号さえわかれば表示することは簡単なので、MATCH関数で行番号を取得する計算をしています。
なお、 絶対参照 にする必要はありません。次のセルG3以降の数式と見た目が同じになるように、説明上付けているだけです。
また、完全一致で検索したいので照合の型は0を指定します。ほかの照合の型は癖が強いので、MATCH関数では基本的に0を使用することをおすすめします。
MATCH関数の後ろに付いている+1は、計算結果を調整するための足し算です。
今回の例でいうと、1行目は項目名の行にしているため、元のリストでは2行目からデータが入力されています。しかし、上記のMATCH関数のみだと計算結果は「1」となり、実際の行番号の「2」と合いません。そのため、MATCH関数の最後に調整用の数字を足して実際の行番号と一致するようにしています。
難しい話を抜きにするのであれば、「データが始まっている行番号から1を引いた数字を足す」ものとして理解すれば充分です。
データが3行目から始まっているのであれば「2」、データが10行目から始まっているのであれば「9」を足しましょう。
IFERROR関数では、MATCH関数の計算結果がエラーになったときの処理を指示しています。検索して見つからないとエラーが返ってくるので、エラー時に空白になるように””を指定しています。
エラー時に「#N/A!」や「#VALUE!」が表示されても構わないという方は使う必要はありません。
G列の数式・その2（セルG3以降）
セルG3の数式は次のとおりです。セルG3以降のセルはオートフィル可能です。
=IFERROR(MATCH($E$2,OFFSET($B$2:$B$8,G2-1,0),0)+G2,"")セルG2の数式と比較すると若干の変更があります。この変更点が本記事のアイデアの重要な点です。
1つ目の変更点は、MATCH関数の検査範囲にOFFSET関数を組み込んだ点です。
ここでのOFFSET関数の役割は、セルG2で見つけた行番号（2）の次の行番号から検索できるように、検査範囲を調整するというものです。
MATCH関数は最初に見つけたセル番地の相対位置を返すという機能なので、同じ範囲で検索する限り同じ計算結果しか返ってきません。そこで、MATCH関数の検査範囲を意図的にずらすことで、次の検索をさせています。（内容が難しい方は、この部分は読み流して問題ありません）
OFFSET関数が基準とする位置は、セルG2のときと同じです。OFFSET関数は基準とするセル番地やセル範囲を動かせる関数なので、セルG2の計算結果を利用して下方向に検査範囲をずらします。
なお、セルG3以降では基準とする位置を固定しないと正しく機能しないため、基準とするセル範囲は 絶対参照 が必須です。
G2の計算結果は「2」ですが、1行目はデータが入力されていないセルなので、その分は引き算する必要があります。このため、行数の調整は「G2-1」という指定になります。この行数の調整は各セルのひとつ上のセルを参照する必要があるので、 絶対参照 にしてはいけません。
最終的に、OFFSET関数によってセルG3の検査範囲は下方向に1ずれた範囲に変わります。
OFFSET関数で検査範囲を操作したことで、2行目のりんごが検査範囲に入っていません。この結果、MATCH関数で3行目以降を検索できるようになります。
2つ目の変更点は、MATCH関数の後ろに付ける行番号の調整の足し算を変えた点です。
セルG3以降では、ひとつ上のセルの計算結果を足してMATCH関数の計算結果を調整します。セルG2と異なり、ひとつ上の計算結果によって調整幅が変わるためです。
H列からJ列の数式
セルH2の数式は次のとおりです。H列からJ列は、すべてオートフィルで反映できます。
=IFERROR(INDEX(A:A,$G2),"")この数式では、単純にINDEX関数で計算しているだけです。
INDEX関数を使って、A列全体を検索します。INDEX関数で表示するセルの行番号は、G列の計算結果に従います。
G列で抽出に必要な行番号がすでにわかっているため、その計算結果を利用するだけでデータを表示できます。
IFERROR関数については「G列の数式・その1」で説明した内容とまったく同じため割愛します。
おわりに
フィルター機能のように同じ文字列（キーワード）のセルをすべて抽出できる方法の紹介でした。
この方法を使えば、フィルター機能での絞り込みとコピーアンドペーストをする作業から解放されます。また、データベースを直接操作するという危険な作業をする必要もありません。別のシートや別のブックで参照することも可能なので、よろしければ活用してみてください。
コメント