内容
ドロップダウン リストは、情報をより快適に操作できる非常に便利なツールです。 一度に複数の値をセルに含めることができ、他の値と同様に操作できます。 必要なものを選択するには、矢印アイコンをクリックするだけで、値のリストが表示されます。 特定のものを選択すると、セルが自動的に入力され、それに基づいて数式が再計算されます。
Excel には、ドロップダウン メニューを生成するためのさまざまな方法が用意されており、さらに、それらを柔軟にカスタマイズできます。 これらの方法をさらに詳しく分析しましょう。
リスト作成の流れ
ポップアップ メニューを生成するには、パス「データ」-「データ検証」に沿ったメニュー項目をクリックします。 「パラメーター」タブを見つける必要がある場所にダイアログボックスが開き、以前に開いていない場合はクリックします。 多くの設定がありますが、「データ型」項目は私たちにとって重要です。 すべての意味の中で、「リスト」が正しいものです。
ポップアップ リストに情報を入力する方法は非常に多くあります。
- 同じダイアログボックスの同じタブにある「ソース」フィールドで、セミコロンで区切られたリスト要素の独立した表示。
2 - 値の暫定的な表示。 Source フィールドには、必要な情報が利用可能な範囲が含まれています。
3 - 名前付き範囲の指定。 先ほどの繰り返しの方法ですが、事前に範囲を指定するだけで済みます。
4
これらの方法のいずれでも、目的の結果が得られます。 実際の状況でドロップダウン リストを生成する方法を見てみましょう。
リストのデータに基づく
さまざまな果物の種類を説明する表があるとします。
この一連の情報に基づいてドロップダウン メニューにリストを作成するには、次の手順を実行する必要があります。
- 将来のリスト用に予約されているセルを選択します。
- リボンの [データ] タブを見つけます。 そこで「データの検証」をクリックします。
6 - 「Data Type」項目を見つけて、値を「List」に切り替えます。
7 - 「ソース」オプションを示すフィールドに、目的の範囲を入力します。 リストをコピーするときに情報が移動しないように、絶対参照を指定する必要があることに注意してください。
8
また、複数のセルで一度にリストを生成する機能があります。 これを実現するには、それらをすべて選択し、前述と同じ手順を実行する必要があります。 ここでも、絶対参照が記述されていることを確認する必要があります。 アドレスの列名と行名の横にドル記号がない場合は、列名と行名の横に $ 記号が表示されるまで F4 キーを押して追加する必要があります。
手動データ記録付き
上記の状況では、必要な範囲を強調表示してリストを作成しました。 これは便利な方法ですが、手動でデータを記録する必要がある場合もあります。 これにより、ワークブック内の情報の重複を避けることができます。
はいといいえという XNUMX つの可能な選択肢を含むリストを作成するタスクに直面したとします。 タスクを達成するには、次のものが必要です。
- リストのセルをクリックします。
- 「データ」を開くと、おなじみの「データチェック」セクションが見つかります。
9 - ここでも、「リスト」タイプを選択します。
10 - ここで、「はい。 いいえ」を出典としている。 列挙にセミコロンを使用して、情報が手動で入力されていることがわかります。
[OK] をクリックすると、次の結果が得られます。
次に、プログラムは適切なセルにドロップダウン メニューを自動的に作成します。 ユーザーがポップアップ リストのアイテムとして指定したすべての情報。 複数のセルでリストを作成するための規則は、セミコロンを使用して手動で情報を指定する必要があることを除いて、前の規則と似ています。
OFFSET 関数を使用してドロップダウン リストを作成する
古典的な方法に加えて、関数を使用することが可能です 廃棄ドロップダウン メニューを生成します。
シートを開いてみましょう。
ドロップダウン リストの関数を使用するには、次の手順を実行する必要があります。
- 将来のリストを配置する目的のセルを選択します。
- 「データ」タブと「データ検証」ウィンドウを順番に開きます。
13 - セットリスト"。 これは、前の例と同じ方法で行われます。 最後に、次の式が使用されます。 =オフセット(A$2$;0;0;5)。 引数として使用されるセルが指定されている場所に入力します。
次に、プログラムは果物のリストを含むメニューを作成します。
この構文は次のとおりです。
=OFFSET(参照,行のオフセット,列のオフセット,[高さ],[幅])
この関数には 5 つの引数があることがわかります。 まず、オフセットする最初のセルアドレスが与えられます。 次の 5 つの引数は、オフセットする行数と列数を指定します。 これはリストの高さを表すため、Height 引数は XNUMX です。
データ置換を使用した Excel のドロップダウン リスト (+ OFFSET 関数を使用)
与えられたケースでは 廃棄 固定範囲にポップアップメニューを作成できるようにしました。 この方法の欠点は、アイテムを追加した後で、数式を自分で編集する必要があることです。
新しい情報の入力をサポートする動的リストを作成するには、次のことを行う必要があります。
- 目的のセルを選択します。
- 「データ」タブを展開し、「データ検証」をクリックします。
- 開いたウィンドウで、「リスト」項目を再度選択し、次の式をデータ ソースとして指定します。 =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
- [OK]をクリックします。
これには関数が含まれています COUNTIF、埋められたセルの数をすぐに判断します (はるかに多くの用途がありますが、特定の目的のためにここに記述します)。
数式が正常に機能するためには、数式のパス上に空のセルがあるかどうかをトレースする必要があります。 彼らはすべきではありません。
別のシートまたは Excel ファイルからのデータを含むドロップダウン リスト
別のドキュメントや同じファイルに含まれるシートから情報を取得する必要がある場合、従来の方法は機能しません。 このために、関数が使用されます 間接、別のシートまたは一般的にはファイルにあるセルへのリンクを正しい形式で入力できます。 次のことを行う必要があります。
- リストを配置するセルをアクティブにします。
- すでに知っているウィンドウを開きます。 以前に他の範囲のソースを示したのと同じ場所に、式が次の形式で示されています。 =INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”). もちろん、List1 と Sheet1 の代わりに、ブックとシートの名前をそれぞれ挿入できます。
注意！ ファイル名は角括弧で指定されます。 この場合、Excel は、現在閉じられているファイルを情報源として使用できなくなります。
ファイル名自体は、必要なドキュメントがリストが挿入されるフォルダと同じフォルダにある場合にのみ意味があることに注意してください。 そうでない場合は、このドキュメントのアドレスを完全に指定する必要があります。
依存ドロップダウンの作成
依存リストは、別のリストでのユーザーの選択によって内容が影響を受けるリストです。 それぞれに名前が付けられた XNUMX つの範囲を含むテーブルが目の前に開いているとします。
結果が別のリストで選択されたオプションの影響を受けるリストを生成するには、次の手順に従う必要があります。
- 範囲名で最初のリストを作成します。
25 - ソース エントリ ポイントで、必要なインジケータが XNUMX つずつ強調表示されます。
26 - その人が選択した植物の種類に応じて、2 番目のリストを作成します。 または、最初のリストで木を指定すると、XNUMX 番目のリストの情報は「オーク、シデ、クリ」以降になります。 データソースの入力箇所に式を書き留める必要があります =間接(E3)。 E3 – 範囲の名前を含むセル 1.=INDIRECT(E3)。 E3 – リスト 1 の名前のセル。
これですべての準備が整いました。
ドロップダウンリストから複数の値を選択する方法は?
場合によっては、11 つの値だけを優先することができないため、複数の値を選択する必要があります。 次に、ページ コードにマクロを追加する必要があります。 キーの組み合わせ Alt + FXNUMX を使用すると、Visual Basic Editor が開きます。 そしてそこにコードが挿入されます。
Private Sub Worksheet_Change（ByVal Target As Range）
エラーで次の再開
Intersect(Target, Range(«Е2:Е9»)) が何もなく、Target.Cells.Count = 1 の場合
Application.EnableEvents = False
Len (Target.Offset (0, 1)) = 0 の場合
Target.Offset (0, 1) = ターゲット
ほかに
Target.End (xlToRight) .Offset (0, 1) = ターゲット
終了する場合
ターゲット.ClearContents
application.enablevents = true
終了する場合
End Subの
セルの内容を下に表示するために、次のコードをエディターに挿入します。
Private Sub Worksheet_Change（ByVal Target As Range）
エラーで次の再開
Intersect(Target, Range(«Н2:К2»)) が何もなく、Target.Cells.Count = 1 でない場合
Application.EnableEvents = False
Len (Target.Offset (1, 0)) = 0 の場合
Target.Offset (1, 0) = ターゲット
ほかに
Target.End (xlDown) .Offset (1, 0) = ターゲット
終了する場合
ターゲット.ClearContents
application.enablevents = true
終了する場合
End Subの
最後に、このコードを使用して XNUMX つのセルに書き込みます。
Private Sub Worksheet_Change（ByVal Target As Range）
エラーで次の再開
Intersect(Target, Range(«C2:C5»)) が何もなく、Target.Cells.Count = 1 の場合
Application.EnableEvents = False
newVal = ターゲット
アプリケーション.元に戻す
oldval = ターゲット
Len (oldval) <> 0 かつ oldval <> newVal の場合
ターゲット = ターゲット & «,» & newVal
ほかに
ターゲット = newVal
終了する場合
Len (newVal) = 0 の場合、Target.ClearContents
application.enablevents = true
終了する場合
End Subの
範囲は編集可能です。
検索でドロップダウンリストを作成するには?
この場合、最初に別のタイプのリストを使用する必要があります。 「開発者」タブが開きます。その後、「挿入」 - 「ActiveX」要素をクリックまたはタップ (画面がタッチされている場合) する必要があります。 コンボボックスがあります。 このリストを描画するように求められた後、ドキュメントに追加されます。
さらに、ListFillRange オプションで範囲を指定するプロパティを使用して構成します。 ユーザー定義値が表示されるセルは、LinkedCell オプションを使用して構成されます。 次に、プログラムが可能な値を自動的に提案するので、最初の文字を書き留めるだけです。
自動データ置換付きのドロップダウン リスト
範囲に追加した後、自動的にデータを代入する機能もあります。 これを行うのは簡単です：
- 将来のリストのセルのセットを作成します。 私たちの場合、これは色のセットです。 それを選択します。
14 - 次に、テーブルとしてフォーマットする必要があります。 同じ名前のボタンをクリックして、表のスタイルを選択する必要があります。
15 16
次に、「OK」ボタンを押してこの範囲を確認する必要があります。
結果のテーブルを選択し、列 A の上部にある入力フィールドから名前を付けます。
それだけです。テーブルがあり、必要なドロップダウン リストの基礎として使用できます。
- リストが配置されているセルを選択します。
- データ検証ダイアログを開きます。
19 - データ型を「リスト」に設定し、値として = 記号を使用してテーブルの名前を指定します。
20 21
すべて、セルの準備が整い、最初に必要だったように、色の名前が表示されます。 最後の位置のすぐ下にあるセルに書き込むだけで、新しい位置を追加できるようになりました。
これはテーブルの利点であり、新しいデータが追加されると範囲が自動的に増加します。 したがって、これがリストを追加する最も便利な方法です。
ドロップダウンリストをコピーするには？
コピーするには、キーの組み合わせ Ctrl + C と Ctrl + V を使用するだけで十分です。したがって、ドロップダウン リストは書式設定と共にコピーされます。 書式設定を削除するには、「値の条件」オプションが設定されている特別な貼り付けを使用する必要があります (コンテキスト メニューでは、リストをコピーした後にこのオプションが表示されます)。
ドロップダウン リストを含むすべてのセルを選択する
このタスクを達成するには、「検索と選択」グループの「セルのグループを選択する」機能を使用する必要があります。
その後、ダイアログボックスが開き、「データ検証」メニューで「すべて」および「これらと同じ」項目を選択する必要があります。 最初の項目はすべてのリストを選択し、XNUMX 番目の項目は特定のリストに類似したものだけを選択します。