Excelでリスト管理をしていると、ある列に登録されている値を除外した一覧を別の列に自動表示したい場面があります。例えば、元の数列が「1、2、3、4、5」で、除外対象として別の列に「2、4」が入力されている場合、「1、3、5」だけを表示したいケースです。この記事では、Excelで特定の値を除外したリストを自動作成する方法を、Excelのバージョン別にわかりやすく解説します。
Excelで特定の値を除外した一覧を作る仕組み
基本的な考え方は、元データと除外リストを比較し、一致しないデータだけを抽出することです。
たとえば元データがC列にあり、除外したい値がB列に入力されている場合、A列には「B列に存在しない値のみ」を表示させます。
| 列 | 内容 |
|---|---|
| C列 | 元データ（1,2,3,4,5） |
| B列 | 除外対象（2,4） |
| A列 | 表示結果（1,3,5） |
Excel 365・Excel 2021ならFILTER関数が便利
動的配列に対応したExcelでは、FILTER関数を利用すると簡単に実現できます。
例えば元データがC2:C100、除外リストがB2:B100の場合は次のような数式を使用します。
=FILTER(C2:C100,COUNTIF(B2:B100,C2:C100)=0)
この数式は、B列に存在しない値だけを抽出して一覧表示します。元データや除外リストが変更されると結果も自動更新されます。
古いExcelでは補助列を使う方法
Excel 2019以前などFILTER関数が使えない場合は、補助列を利用する方法があります。
例えばD列に次の式を入力します。
=COUNTIF($B$2:$B$100,C2)
結果が0なら除外対象に存在しないことを意味します。その後、オートフィルターで0だけを表示すれば除外後の一覧を確認できます。
重複データがある場合の注意点
元データに同じ数値や文字列が複数存在する場合でも、FILTER関数やCOUNTIF関数は正常に動作します。
ただし重複を除いた一覧を作りたい場合はUNIQUE関数と組み合わせると便利です。
例として、重複を除外しながら抽出する場合は次のような式になります。
=UNIQUE(FILTER(C2:C100,COUNTIF(B2:B100,C2:C100)=0))
数値以外の文字列にも応用できる
この方法は数値だけでなく、商品コードや顧客名、メールアドレスなどの文字列データにも利用できます。
例えば顧客一覧から退会者リストを除外したり、在庫一覧から販売済み商品を除外したりする際にも活用できます。
業務効率化のためにも覚えておくと便利なテクニックです。
まとめ
Excelで特定の列に存在する値を除外した一覧を作るには、Excel 365やExcel 2021ならFILTER関数とCOUNTIF関数の組み合わせが最も簡単です。古いバージョンでは補助列とCOUNTIF関数を利用する方法が実用的です。元データや除外リストの変更に自動対応できるため、データ管理や集計作業の効率化に役立ちます。
コメント