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

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

更新:2025/6/1;作成:2018/11/20

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

• UNIQUE関数を使って、重複しない一意のデータを取り出すことができるようになりました。
  UNIQUE関数はExcel for Microsoft365,Excel2021以降で使用できます。
  UNIQUE関数の詳細な使い方は UNIQUE関数で重複しない値を取り出す:Excel関数　をご覧ください。
• ここで使用するデータは、Sheet2に受注リストが入力されています。
  テーブルに変換して、「受注_tbl」というテーブル名に設定しました。
  
  このリストの商品名から重複しないものを取り出します。
  Sheet1の入力リストの商品名にドロップダウンリストから入力できるように入力規則を設定します。

1. Sheet1にUNIQUE関数で重複しないリストを作成します。
   なお、元の受注リストはデータの増減に対応するために、テーブルにしておきます。
   重複しないリストを　=SORT(UNIQUE(受注_tbl[商品名])　で取り出します。
   
2. 入力規則の設定を行います。
   セル範囲C3:C12を選択して、[データ]タブのデータツール グループにある[データの入力規則]を実行します。
   
3. データの入力規則で入力値の種類で「リスト」を選択します。
   元の値に　スピル範囲演算子(#)を使って　=$G$3#　とします。←ここがポイントです。
   数式を元の値で指定できないが#を付けることで計算結果の配列のデータ全体を指定することができます。
   
4. 重複しないドロップダウンリストが作成できました。
   
5. Sheet2に受注リスト「受注_tbl」に新たな商品「パイナップル」を追加します。
   
6. 重複のないリストに「パイナップル」が追加され、ドロップダウンリストにも追加されます。
   元のリストがテーブルになっているおかげです。
   

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

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

1. 作業列を利用して、重複しないデータの行番号を調べて、重複しないデータのリストを作成します。
   重複しないデータの行番号は　=IF(COUNTIF($B$3:B3,B3)=1,ROW(),"")　として求めました。
   (この数式はF20セルまでコピーします。入力規則の設定時に重要になります)
   
2. 重複しないデータのリストは　(この数式はG20セルまでコピーします。入力規則の設定時に重要になります)
   =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列から取り出しています。
   • MATCH(SMALL($F$3:$F$20,ROW(A1))　でF列の小さい値から順番に取り出します。
     3，4，7，9と順番に取り出します。
   • INDEX($B$3:$B$20,3,$F$3:$F$20,0))でB3セルの「りんご」を取り出し、
     B4セルの「みかん」、B7セルの「マンゴー」、B9セルの「パイナップル」を取り出しています。
   
3. 入力規則の設定を行います。
   D3セルを選択します。
   [データ]タブのデータツール グループにある[データの入力規則]を実行します。
   
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. 重複しないデータが入力されると、入力規則のリストに追加されます。
   下図のように、　梨、桃 を追加したら、入力規則のリストにも 梨、桃 が追加されました。
   

スポンサードリンク

PageViewCounter

Since2006/2/27