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

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

更新:2025/7/2;作成:2018/11/20

重複しないデータのみが表示されるようになっていた
オートコンプリート機能が追加されています　　　　　　　Topへ

• 最近気づいたのですが、入力規則のリストが重複なしのデータが表示されるようになっていました。(記：2025/6/23)
  ただし、並べ替えはされないのでリストには上から順番に表示されています。
  この点は、SORT関数とUNIQUE関数を使う方法が使いやすいです。
• また、入力規則のリストにオートコンプリートの機能が追加されていたようです。
  「ドロップダウンリストにオートコンプリート機能が追加 」
  更新プログラム バージョン 2308 (ビルド 16731.20170) :バージョン 2308:2023年8月28日

重複なしのデータがドロップダウンリストに表示される

• 重複なしのデータがドロップダウンリストに表示されるので、UNIQUE関数を使わず、SORT関数で並べ替えたものを入力規則で指定すれば良いということになります。
  この項では重複しないデータが表示されるのを説明しています。
• 上と同じデータを使って入力規則を作成します。
  ここで使用するデータは、テーブルに変換して、「受注_tbl」というテーブル名に設定しています。
  重複したデータが混在しています。
  
• 元の値にテーブルの列[商品名]を指定しています。　=INDIRECT("受注_tbl[商品名]")
  ドロップダウンリストには重複していないデータが表示されています。UNIQUE関数は使っていません。
  

ドロップダウンリストにオートコンプリートの機能が追加されていました

• オートコンプリート機能については分かり易いデータをN列に作成しました。
  せっかくなので、元の値には　トリム参照で =N3:.N100　としています。(ドットの位置が重要です)
  (トリム参照ではない場合は　=$N$3:$N$100 とすると空欄が含まれるので=$N$3:$N$14 とすることになります。)
  すると、重複なしで、ドロップダウンリストに表示されます。
  トリム参照なので、N列に新たなデータを追記してもリストに表示されます。C301とC302を追記した後の画面です。
  • トリム参照については　TRIMRANGE関数とトリム参照を使って空白の行または列を除外する　をご覧ください。
  
• Bと入力すると、Bから始まるデータがドロップダウンリストに表示されます。
  [↓]キーで選択して入力できます。
  
• Aと入力すると、Aから始まるデータがドロップダウンリストに表示されます。
  [↓]キーで選択して入力できます。
  
• SORT関数を使った数式を入力規則の設定に使用できませんので、
  R3セル以降に　=SORT(N3:.N100) でデータリストを並べ替えます。
  入力規則のドロップダウンリストの設定は　=R3#　とします。(#はスピル範囲演算子)
  =R3#は =SORT(N3:.N100) で表示されたスピル範囲を指定しています。
  

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. 重複のないリストに「パイナップル」が追加され、ドロップダウンリストにも追加されます。
   元のリストがテーブルになっているおかげです。
   

UNIQUE関数が使えない場合　　　　　　　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