マスター Excel ドロップダウン リスト:作成、編集、削除、フィルター、保護

このデータセットには、「注文 ID」、「顧客名」、「デバイス」、「注文日」、「配達日」、「支払い方法」といったオンライン ショッピング情報が表示されます。

Excel のドロップダウン リストとは何ですか?

ドロップダウン リストは、ユーザーがオプションのリストから要素を選択できる機能です。

Excel でドロップダウン リストを作成する方法

1.セル範囲からのドロップダウン リストの作成

列 B の値に基づいてドロップダウン リストを作成するには :

• セルを選択します (C18) ).
• データ に移動します。 タブをクリックし、データ検証を選択します。 .

• データ検証中 、[設定] に移動します。 .
• リストで [許可] を選択します。 .
• 範囲を定義します ($B$6:$B$15) ) ソース .
• [OK] をクリックします。 .

これが出力です。

• C19 に次の数式を入力します。 列 C の値を取得します。 注文 ID の選択に基づきます。

=VLOOKUP($C$18,$B$6:$G$15,ROW(B19)-ROW($B$18)+1,FALSE)

• フィル ハンドル を使用します。 値を抽出します。

2.テーブルからドロップダウン リストを作成する

• セル範囲を選択し、挿入 に移動します。 タブをクリックしてテーブルを作成します。
• [テーブル] をクリックします。 .

• テーブルにヘッダーがあるをチェックします。 .
• [OK] をクリックします。 。

• C18 でドロップダウン リストを作成するには 顧客名のデータを含む をクリックし、[リスト ] を選択します。 許可で .
• INDIRECT 関数を使用して次の数式を入力します。 ソース内 [OK] をクリックします。 .

=INDIRECT("Table1[Customer Name]")

• C19 に次の数式を入力します。 顧客名の選択に基づいてデバイス名を抽出します。

=INDEX(Table1,MATCH(C18,Table1[Customer Name],0),3)

これが出力です。

3.名前付き範囲を使用したドロップダウン リストの作成

• セル範囲を選択し、数式に移動します。 タブ。
• [名前の定義] をクリックします。 .

• 名前 (ID) を設定します。 ) 名前に そして参照先で範囲を定義します。 .

• C19 で注文 ID のドロップダウン リストを作成します。 ソース: に次の数式を入力します。

• C19 では次の数式を使用します。 注文 ID の選択に基づいて顧客名を取得します。

=INDEX(B6:G15,MATCH($C$18,ID,0),2)

• C20 にデバイスの名前を付けるには、次の式を使用します。 .

=INDEX(B6:G15,MATCH($C$18,ID,0),3)

これが出力です。

4.色付きのドロップダウン リストの作成

条件付き書式を使用してカスタム カラーを定義します。 ドロップダウン リストの各値について。

• リストを選択します 許可で。
• 色の名前をカンマ記号 (,) で区切って入力します。 ):( 黒、白、シルバー、ゴールド ) ソース .

• ドロップダウンの各要素の色を定義するには、ドロップダウンを選択し、条件付き書式設定 に移動します。 家で タブ。
• セルのハイライト ルール に移動します。 [等しい…] をクリックします。

• 等しい 、色の名前を入力します (黒) ).
• カスタム形式を選択します。 色名に色を設定します。

• 塗りつぶしで色を選択します .

• フォントでフォントの色を変更することもできます 。ほら、白。

• [OK] をクリックします。 .

• 他のオプションの色を設定します。

ドロップダウン リストには、セルの値に基づいて色が表示されます。

色は選択内容に基づいて自動的に変更されます。

• フィル ハンドル を使用します。 自動入力 ドロップダウン。

5.一意の値を含むドロップダウン リストの作成

D 列に一意の値を含むドロップダウン リストを作成します。 そしてエ .

• B22 で次の数式を使用します。 固有のブランド名を確認します。

• 次の数式を入力して、C5 にドロップダウン リストを作成します。

• C22 に次の数式を入力します。 列 E の一意の値を確認するには C5 のブランドに一致するもの .

=FILTER(E9:E18,C5=D9:D18)

• データ検証に移動します。 .
• ソースで次の数式を使用します。 .

これが出力です。

6.別のシートのデータに基づいてドロップダウン リストを作成する

携帯ブランドとその携帯電話モデルは PhoneList にあります。 ワークシート。

ブランド名と電話モデルを使用して、別のワークシート (別のシート) に 2 つのドロップダウン リストを作成します。 ).

• D6 でブランド名のドロップダウン リストを作成するには PhoneList のデータを使用 ワークシートのソースに次の数式を入力します。

• フィル ハンドル を使用します。 同じドロップダウン リストを列 D に作成します。 .
• 間接 で次の数式を使用します。 ソースの関数  PhoneList から電話モデルを取得するには 列 D のブランド名に基づいたドロップダウンのワークシート .

画像を拡大するにはここをクリックしてください。

これが出力です。

7.別のワークブックのデータに基づいてドロップダウン リストを作成する

利用可能なPaymentMethodsで ワークブックには、オンライン ショッピングの支払い方法のリストがあります。

• 数式 に移動します をクリックし、[名前の定義] を選択します。 .

• 新しい名前で 、名前 (PayType) を定義します。 )名前に。
• 範囲を定義します (Sheet1!$B$3:$B$7) ) の参照 .
• [OK] をクリックします。 .

• Excel ワークブックのドロップダウン に移動します。 名前付き範囲 (PayType) を設定します。 ).
• 「[AvailablePaymentMethods.xlsx]Sheet1!PayType 」と入力します。 AvailablePaymentMethods で名前の範囲を定義します。 ワークブック。
• ソース に次の数式を入力します。 G6 で名前付き範囲のドロップダウン リストを作成します。 .

• 支払い方法はドロップダウン リストから選択できます。

• フィル ハンドル を使用します。 自動入力 ドロップダウン リスト。

注意

私。ドロップダウンの作成中は両方のワークブックが開いている必要があります。
ii.別のワークブックから複数の依存ドロップダウン リストを作成しないでください。

8.依存ドロップダウン リストの作成

• D6 でブランドの独立したドロップダウン リストを作成するには 、ソース () に次の数式を入力します。 データ検証) .

• E6 で電話モデルの依存ドロップダウン リストを作成するには 、 間接 を使用して次の数式を入力します。 ソースの関数 .

画像を拡大するにはここをクリックしてください。

独立したドロップダウン リストからのブランドの選択に基づいて、依存するドロップダウン リストが作成されます。

• フィル ハンドル を使用します。 自動入力 ドロップダウン リスト。

9.検索可能なドロップダウン リストの作成

• 新しい列を挿入 (G ) そしてH) 固有のブランド名とモデル名を付ける。 G6 では次の式を使用します。 .

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• ソース に次の数式を入力します。 C19 で検索可能なドロップダウン リストを作成するには ブランド名を含めて。

C19 でブランド名を検索できるようになります。 .

• H6 に次の数式を入力します。 検索可能なドロップダウンのブランド選択に基づいて携帯電話モデルを表示します。

=UNIQUE(FILTER(E6:E17,C19=D6:D17))

• ソースに次の数式を入力します。 C20 で検索可能なドロップダウンを作成するには 電話機のモデル名を含めます。

独立した検索可能なドロップダウン リストと依存した検索可能なドロップダウン リストがあります。

10.新しいエントリを追加および自動更新するための動的ドロップダウン リストの作成

ID に基づいて値を抽出します。

画像を拡大するにはここをクリックしてください。

• 別の列を挿入し、L6 に次の数式を入力します。 B 列の値に基づいて一意の ID を確認します。

=UNIQUE(OFFSET(B6,0,0,COUNTA(B:B)-3,1))

画像を拡大するにはここをクリックしてください。

• J5 に一意の ID を含むドロップダウン リストを作成します。 ソースに次の数式を入力します。 .

画像を拡大するにはここをクリックしてください。

• ドロップダウン リストから ID を選択し、J6 で次の数式を使用します。 選択した ID の顧客名を確認します。

=VLOOKUP($J$5,B:G,ROW(I6)-ROW($I$5)+1,FALSE)

• フィル ハンドル を使用します。 自動入力 残りの値。

画像を拡大するにはここをクリックしてください。

• 抽出された日付は整数として表示されます。数値形式を 短い日付 に変更します。 または 長い日付 数値形式 .

• 追加の ID とそれに関連する値を追加します。これらはドロップダウン リストと列 L で自動的に更新されます。 .

画像を拡大するにはここをクリックしてください。

これが出力です。

11.検索可能、動的、複数依存のドロップダウン リストの作成

• L6 に次の数式を入力します。

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• J5 でブランド名の独立した検索可能なドロップダウン リストを作成する ソースに次の数式を入力します。 .

• J5 のドロップダウン リストから選択したブランドに基づいて電話モデルをフィルタリングします。 。次の式を使用してください。

=UNIQUE(FILTER(E:E,J5=D:D))

• J6 でブランド名に応じて検索可能な電話モデル名のドロップダウン リストを作成する 。次の数式をソースに入力します。 .

• J6 のドロップダウン リストから選択したモデルに基づいてチップセットをフィルタリングします。 次の式を使用してください。

=UNIQUE(FILTER(F:F,J6=E:E))

• ソース に次の数式を入力します。 J7 でモデル名に応じて検索可能なチップセットのドロップダウン リストを作成します。 .

• J8 で次の数式を使用します。 選択した値に一致する商品の価格を検索します。

=INDEX(G:G,MATCH(1,(J5=D:D)*(J6=E:E)*(J7=F:F),0))

すべてのドロップダウン リストは動的であるため、新しいエントリを追加すると、ドロップダウン リストが更新されます。

12.カスタム メッセージを含むドロップダウン リストの作成

• 方法 7 の説明に従って、電話モデルの依存ドロップダウン リストを作成します。 .

• データ検証中 をクリックし、メッセージの入力に移動します。
• セルが選択されたときに入力メッセージを表示するをオンにします。 .
• タイトルでタイトルを設定します 入力メッセージのカスタム メッセージ .
• [OK] をクリックします。 .

セルが選択されると、カスタム メッセージが表示されます。

13.エラー警告メッセージを含む編集可能なドロップダウン リストの作成

• G6 に次の数式を入力します。 固有のブランド名を含む動的な列を作成する

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• C19 でブランド名の動的な検索可能なドロップダウン リストを作成する ソースに次の数式を入力します。 .

• 編集可能なドロップダウン リストを作成するには、エラー アラート に移動します。 タブ。
• [無効なデータが入力された後にエラー警告を表示する] チェックボックスをオンにします。 .
• 間違った検索値のスタイル、タイトル、エラー メッセージを設定し、[OK] をクリックします。 .

• モデルの値を使用してドロップダウン リストを作成します。 .

これらの編集可能なドロップダウン リストに間違った入力を入力すると、エラー アラートが表示され、再試行またはキャンセルのオプションが表示されます。

Excel ドロップダウン リストに項目を追加または削除するにはどうすればよいですか?

ソースで範囲を定義するときにセルを追加または削除することで、項目を追加または削除できます。 .

Excel でドロップダウン リストを保護する方法

顧客名のドロップダウン リストが C18 で作成されました。 .

• テーブル全体を選択し、ホームに移動します。 タブ。
• [配置 ] をクリックします。

• セルの書式設定内 、[ロック中] をオンにします。 保護内 タブ。

• テーブルは保護されています。 レビュー に移動します。 タブをクリックし、ワークブックの保護をクリックします。 .

• パスワードを入力し、[ロックされたセルを選択する] をオンにします。 .
• [OK] をクリックします。 .

• パスワードを再入力します。

テーブルとドロップダウン リストの両方が保護されます。

Excel でドロップダウン リストを削除する方法

• ドロップダウン リストを選択し、データに移動します。 タブ。
• [データ検証] をクリックします。 .

• データ検証中 をクリックし、[すべてクリア] をクリックします。
• [OK] をクリックします。 .

ドロップダウン リストは削除されます。

Excel でドロップダウン リストをフィルタリングし、選択に基づいてデータを抽出する方法

• テーブルを作成します。

• 列 D にドロップダウン リストを作成します。

• 次の数式を入力して、選択に基づいてデータ全体を抽出します。

=FILTER(Table3,Table3[Device]=$C$18,"No Information Available")

• ドロップダウン リストの値を変更すると、データ抽出が自動的に更新されます。

Excel のドロップダウン リストに関する問題を解決するにはどうすればよいですか?

1.ドロップダウン リストが表示されない

解決策:

• セル内ドロップダウン をオンにします。 ドロップダウン リストを表示します。

2.ドロップダウン リストに空白を表示する

解決策:

• 空白を無視するようにドロップダウン リストの範囲を定義します。

3.ドロップダウン リストでは有効なエントリは許可されません

解決策:

• 有効なエントリを入力するときは、大文字と小文字を区別して入力してください。

4.ドロップダウン リストで許可される無効なエントリ

ソース範囲が実際の値リストよりも大きいため、それらのセルに入力された値が、無効であってもリストされる可能性があります。

解決策:

• 無効な要素の入力を停止するためにドロップダウン リストの範囲を定義します。

練習ワークブックをダウンロード
練習用ワークブックをダウンロードしてください。

Excel ドロップダウン リスト:ナレッジ ハブ

• Excel でドロップダウン リストを作成する
• Excel でドロップダウン リストを編集
• Excel のフィルター付きドロップダウン リスト
• Excel に依存するドロップダウン リスト
• Excel でドロップダウン リストを削除する方法
• Excel でセルの値をドロップダウン リストにリンクする方法
• Excel のドロップダウン リストが機能しない
• Excel でドロップダウン リストを作成するための IF ステートメント
• Excel のドロップダウン リストの選択に基づいてデータを抽出する
• Excel のセル値に基づいてリストを作成する
• Excel の複数列のドロップダウン リスト
• Excel のドロップダウン リストの空のオプション
• ドロップダウンから選択し、別のシートからデータを取得する
• [修正!] Excel で空白を無視するドロップダウン リストが機能しない
• Excel のオートコンプリート データ検証ドロップダウン リスト

<

ソリューション付きの高度な Excel 演習を無料で入手しましょう!