Excelで条件に基づいてリストを生成する方法(4つの方法)

Excelを使っていると、特定の条件に合致するデータだけを抽出して、新しいリストを作成したい場面がよくあります。本記事では、条件に基づいてリストを生成する4つの方法を詳しく解説します。今回はExcel 365を使用していますが、お使いのバージョンでも同様の手順で作業できます(FILTER関数など一部の機能はExcel 365限定です)。

まずは、例題のベースとなるデータセットを確認しましょう。

このデータセットには、さまざまな地域に住む人々の名前と、それぞれが所有する車の情報が含まれています。このデータをもとに、条件に応じたリストを作成していきます。

なお、ここでは説明をシンプルにするため、基本的なダミーデータを使用した小さな表を用意しています。実際の業務では、もっと大規模で複雑なデータセットを扱うことになるでしょうが、考え方は同じです。

練習用ワークブックのダウンロード

以下のリンクから、練習用ワークブックを自由にダウンロードしてご利用ください。

条件に基づいてリストを生成する4つの方法

ここでは例として、「地域」を条件として、その地域に該当する人名のリストを作成します。

データセットが小さいため、地域は4つあることがわかっています。あらかじめ地域名を入力しておき、各地域ごとに該当する人名のリストを抽出します。

方法1:INDEX関数とSMALL関数の組み合わせを使う

リストを作成するには、表から複数の値を順番に取り出す数式が必要です。そのために活躍するのが、INDEX関数とSMALL関数の組み合わせです。

これらの関数の詳細については、INDEXおよびSMALLの解説記事をご参照ください。さらに、補助的にIF関数、ROW関数、IFERROR関数も使用します。詳細は各関数の解説記事(IF、ROW、IFERROR)を確認してください。

それでは、実際の数式を見てみましょう。

=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

この数式では、それぞれの関数が次のような役割を担っています。

• INDEX関数:配列B2:B12(名前列)から値を返します。
• SMALL関数:取り出すべき行番号を算出します。
• IF関数:SMALL関数の中で条件との一致を判定します。
• ROW関数:列内のセルを順に走査し、外側のROW関数はSMALL関数のk番目の値を指定します。
• IFERROR関数:数式から発生しうるエラーを処理し、空白を表示させます。

これらの関数が連携することで行番号が算出され、INDEX関数がその結果を返します。数式を下方向にコピー(ドラッグ)すれば、指定した地域の人名がすべて表示されます。

同じ要領で、他の地域についても数式を入力しましょう(数式自体は同じで、参照セルをずらすだけです)。

別の書き方:INDEX-SMALLの組み合わせ(代替パターン)

同じ数式は、別の書き方で表現することもできます。使用する関数は前述のものと同一で、記述の仕方が異なるだけです。

数式は以下の通りです。

=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")

この場合も、数式を実行する際にはCtrl + Shift + Enterキーを押して配列数式として確定する必要があります。

2つの数式にはわずかな違いがあります。お気づきでしょうか?

そうです。最初の数式では「-1」をSMALL部分の最後で引いていましたが、こちらの数式ではIF部分の中で「-1」を引いています。「-1」を引く目的は、適切な行番号に変換することです。前者は最後に調整する方式、後者は早い段階で調整してから以降の処理に進む方式という違いがあります。

残りの条件についても同様に数式を入力すれば、リストが完成します。

関連記事:Excelでセル内にリストを作成する方法(3つの簡単な方法)

方法2:AGGREGATE関数を使う

Excelには、さまざまな集計処理を行えるAGGREGATE関数が用意されています。この関数を使っても、条件に基づいたリストを生成できます。

AGGREGATE関数は、AVERAGE(平均)、COUNT(カウント)、MAX(最大値)などの集計計算の結果を返す関数です。構文は以下の通りです。

AGGREGATE(function_number, behavior_options, range)

• function_number(機能番号):どの計算を行うかを番号で指定します。
• behavior_options(動作オプション):関数の動作方法を番号で指定します。
• range(範囲):集計対象となる範囲を指定します。

AGGREGATE関数は多数の処理に対応しており、機能ごとに番号が割り振られています。よく使われる機能番号をいくつか紹介します。

機能	機能番号
AVERAGE	1
COUNT	2
COUNTA	3
MAX	4
MIN	5
PRODUCT	6
SUM	9
LARGE	14
SMALL	15

関数の詳細については、Microsoftサポートの公式サイトをご覧ください。

それでは、実際の数式を見てみましょう。

=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")

ここでは、AGGREGATE関数と組み合わせてINDEX関数を使用しています。INDEX関数は配列を保持し、数式の後半部分で見つかった一致に基づいて値を返します。

注目すべきは、AGGREGATE関数のfunction_numberに「15」を指定している点です。上の表からわかるように、「15」はSMALL関数の操作を呼び出します。つまり、先ほどのINDEX-SMALLの数式を、AGGREGATE関数の形式で実行していることになります。

behavior_optionsに指定した「6」は「エラー値を無視する」という意味です。

残りの値についても同様に数式を入力しましょう。

関連記事

• ExcelでToDoリストを作成する方法(3つの簡単な方法)
• Excelでメーリングリストを作成する方法(2つの方法)
• Excelで連番付きリストを作成する方法(8つの方法)

方法3:INDEX-MATCH-COUNTIFの組み合わせで重複のないリストを生成する

条件に基づいて、重複のないユニークなリストを作成することもできます。その場合は、INDEX関数、MATCH関数、COUNTIF関数の組み合わせを活用します。

COUNTIF関数は、範囲内で単一の条件を満たすセルの個数を数える関数です。MATCH関数は、範囲内で検索値が位置する場所を特定します。詳細は各関数の解説記事(MATCH、COUNTIF)をご覧ください。

それでは、数式を確認しましょう。

=IFERROR(INDEX($B$2:$B$12,MATCH(0,IF(G$2=$C$2:$C$12,COUNTIF($G$2:$G2,$B$2:$B$12),""),0)),"")

この数式の構成要素は以下の通りです。

• B2:B12:抽出したいユニークな値が含まれる列の範囲
• C2:C12:条件となる値が含まれる列
• G2:条件(検索基準)を示すセル

MATCH関数の内部では、lookup_valueとして「0」を指定し、lookup_arrayにはCOUNTIFを含むIF部分を使用しています。この部分は、0が見つかる限り値を返し続けます。返された値は、INDEX関数にとっての行番号として機能します。

数式を下方向にドラッグすると、すべてのユニークな値が抽出されます。

数式を実行する際は、忘れずにCtrl + Shift + Enterキーを押してください。

これは、条件に基づいて重複のないリストを生成する手法のご紹介でした。より詳しく知りたい方は、条件に基づくユニークリストの生成に関する専用記事をご参照ください。

方法4:FILTER関数を使って条件に基づくリストを生成する

Excel 365をお使いの場合は、FILTER関数という組み込み関数ひとつでこの作業を完了できます。

FILTER関数は、指定した条件に基づいてデータの範囲をフィルタリングし、一致するレコードを抽出します。関数の詳細については、FILTERの解説記事をご覧ください。

数式は非常にシンプルで、以下のようになります。

=FILTER($B$2:$B$12,$C$2:$C$12=G$2)

B2:B12がフィルタリング対象の配列で、続けてリスト生成の基準となる条件を指定します。

この方法の大きなメリットは、数式を下方向にドラッグする必要がない点です。一度の入力ですべての値が返され、リストが自動的に完成します。

関連記事:Excelでアルファベット順のリストを作成する方法(3つの方法)

まとめ

以上、条件に基づいてリストを生成する複数の方法をご紹介しました。従来の関数の組み合わせから、Excel 365ならではのFILTER関数まで、状況に応じて最適な方法を選んでみてください。内容についてわかりにくい点があれば、お気軽にコメントでお知らせください。また、ここで紹介していない他の方法があれば、ぜひ教えてください。

さらに学びたい方へ

• Excelで箇条書きリストを作成する方法(9つの方法)
• Excelでカンマ区切りのリストを作成する方法(5つの方法)
• Excelで価格リストを作成する方法(ステップバイステップガイド)