1. Home » 
2. Excelの基本操作の目次 » 
3. 入力規則 » 
4. 重複しないリストをドロップダウンリストに表示する(入力規則)

重複しないリストをドロップダウンリストに表示する(入力規則)：Excelの基本操作

2022/10/20

UNIQUE関数を利用する方法　　　　　　　Topへ

• Excel for Microsoft365ではUNIQUE関数を使って、重複しない一意のデータを取り出すことができるようになりました。
  Excel2021でもUNIQUE関数は使用できます。
  UNIQUE関数の詳細な使い方は UNIQUE関数で重複しない値を取り出す:Excel関数　をご覧ください。

1. UNIQUE関数で重複しないリストを作成します。
   なお、元の商品リストはデータの増減に対応するために、テーブルにしておきます。
   重複しないリストを　=UNIQUE(テーブル1[商品名])　で取り出します。
   
2. 入力規則の設定を行います。
   D3セルを選択して、[データ]タブのデータツール グループにある[データの入力規則]を実行します。
   
3. データの入力規則で入力値の種類で「リスト」を選択します。
   元の値に　スピル範囲演算子(#)を使って　=F3#　とします。
   (F3# と指定すると、UNIQUE関数で取り出した配列のデータ全体を指定することができます。)
   
4. 重複しないドロップダウンリストが作成できました。
   
5. 新たな商品「サクランボ」を追加します。
   重複しないリストに「サクランボ」が追加され、ドロップダウンリストにも追加されます。
   元のリストがテーブルになっているおかげです。
   

• ちなみに、重複しないリストを50音順に表示したい場合は、SORT関数と組み合わせます。
  =SORT(UNIQUE(テーブル1[商品名]))　とします。
  

重複しないリストをドロップダウンリストに表示する(入力規則)　　　　　　　Topへ

• UNIQUE関数が使用できない環境ではこちらの方法で重複しないリストを作成できます。
  ちょっと複雑な数式を作成する必要があります。
• この項では重複のあるデータから、重複のないデータを入力規則のドロップダウンリストに表示する方法について書いています。
  下図のB列に入力されたデータをD3セルの設定した入力規則のリスト(ドロップダウンリスト)に、重複していないデータだけを表示します。
  

1. 作業列を利用して、重複しないデータの行番号を調べて、重複しないデータのリストを作成します。
   重複しないデータの行番号は　=IF(COUNTIF($B$3:B3,B3)=1,ROW(),"")　として求めました。
   
2. 重複しないデータのリストは　=IF(COUNT($F$3:$F$20)>=ROW()-2,INDEX($B$3:$B$20,MATCH(SMALL($F$3:$F$20,ROW(A1)),$F$3:$F$20,0)),"")　としました。
   IF関数で IF(COUNT($F$3:$F$20)>=ROW()-2,・・・　としてエラー値を表示しないようにしています。
   ROW()-2 はF列のデータの始まりが3行目からなので、3行目が1になるように -2 で調整しています。
   重複しないデータは INDEX関数を使って B列から取り出しています。
   
   • IF関数使わず #NUM! エラーを表示してもかまわない場合は、=INDEX($B$3:$B$20,MATCH(SMALL($F$3:$F$20,ROW(A1)),$F$3:$F$20,0))　とすることができます。
     その場合、#NUM! 以外のデータをカウントするときには　=OFFSET($F$3,0,0,COUNTIF($F$3:$F$20,"<>#NUM!"),1)　とします。(これは入力規則で設定する数式です)
     
3. 入力規則の設定を行います。
   [データ]タブのデータツール グループにある[データの入力規則]を実行します。
   
4. データの入力規則 のダイアログボックスが開きます。
   [設定]タブを選択して、入力値の種類で「リスト」を選択します。
   元の値に　=OFFSET($G$3,0,0,COUNTA($G$3:$G$20)-COUNTBLANK($G$3:$G$20),1)　と入力します。
   [OK]ボタンをクリックします。
   
   • G列のデータをリストにするための数式の説明
     =OFFSET($G$3,0,0,COUNTA($G$3:$G$20)-COUNTBLANK($G$3:$G$20),1)
     リストに表示するセル位置をOFFSET関数を使って指定します。
     OFFSET関数の構文は =OFFSET(参照,行数,列数,[高さ],[幅]) ですので、参照には G3 セルとします。
     行数、列数はともに 0 とします。
     高さは　重複しないデータのセル数を指定します。数式は　COUNTA($G$3:$G$20)-COUNTBLANK($G$3:$G$20)　として、全セル数から空白セル "" の数を差し引いて求めました。
     幅は 1 としました。
5. 入力規則のリストで重複しないデータをリスト表示することができました。
   
6. 重複しないデータが入力されると、入力規則のリストに追加されます。
   下図のように、　梨、桃 を追加したら、入力規則のリストにも 梨、桃 が追加されました。