ヒント:他の言語はGoogle翻訳されています。 訪問することができます English このリンクのバージョン。
ログイン
x
or
x
x
お申し込み
x

or

以前に使用したアイテムをドロップダウンリストで非表示にするにはどうすればいいですか?

Excelでは、通常のドロップダウンリストをすばやく作成できますが、1つの項目を選択したときにドロップダウンリストを作成しようとしたことがあります。以前使用したものがリストから削除されますか? たとえば、名前を選択するときに、100名のドロップダウンリストがある場合、この名前をドロップダウンリストから削除して、ドロップダウンリストが空になるまで99名などをドロップダウンリストに追加します。 たぶん、これは私たちにとって大変なことですが、ここではExcelでこのようなドロップダウンリストを作成する方法について説明します。

以前に使用された項目をヘルパー列のドロップダウンリストで非表示にする

OfficeタブOfficeでタブ付き編集とブラウジングを有効にし、作業をより簡単にします...
Kutools for Excelは、ほとんどの問題を解決し、生産性を80%向上させます
  • 何でも再利用: 最も使用されている式や複雑な式、チャート、その他をお気に入りに追加し、将来的にすぐに再利用できます。
  • 20以上のテキスト機能: テキスト文字列から数値を抽出します。 テキストの一部を抽出または削除します。 数字と通貨を英語の単語に変換...
  • マージツール:複数のワークブックとシートを1つに; データを失うことなく複数のセル/行/列を結合します。 重複する行と合計をマージ...
  • 分割ツール:値に基づいてデータを複数のシートに分割します。 1つのワークブックから複数のExcel、PDF、またはCSVファイル。 1列から複数列...
  • 貼り付けスキップ 非表示/フィルターされた行。 カウントアンドサム 背景色別; メーリングリストを作成し、 セルの価値でメールを送信する...
  • スーパーフィルター: 高度なフィルタースキームを作成し、任意のシートに適用します。 ソート 週、日、頻度などにより; フィルタ 太字、式、コメントで...
  • 300の強力な機能以上。 Office 2007-2019および365で動作します。 すべての言語をサポートしています。 企業または組織に簡単に展開できます。

矢印青い右の泡 以前に使用された項目をヘルパー列のドロップダウンリストで非表示にする


表示されるスクリーンショットのように、列Aに名前のリストがあると仮定して、この手順を1つずつ順番に実行してこのタスクを完了します。

doc-hide-used-items-dropdown-list-1

1。 あなたの名前リストの他に、この数式を入力してください = IF(COUNTIF($ F $ 1:$ F $ 11、A1)> = 1、 ""、ROW()) セルB1に、スクリーンショットを参照してください:

doc-hide-used-items-dropdown-list-1

上記の式において、 F1:F11ドロップダウンリストを配置するセルの範囲です。 A1 あなたの名前のセルです。

2。 次に、塗りつぶしハンドルをこの数式を含む範囲にドラッグすると、次の結果が得られます。

doc-hide-used-items-dropdown-list-1

3。 列Cに数式を適用するには、次の式を入力してください: =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1)))) セルC1に、スクリーンショットを参照してください:

doc-hide-used-items-dropdown-list-1

4。 この数式を必要な範囲に塗りつぶしてください。スクリーンショットをご覧ください:

doc-hide-used-items-dropdown-list-1

5。 ここで、列Cでこれらの名前の範囲名を定義し、C1:C11(手順4で数式を適用する範囲)を選択してから、をクリックします。 > 名前を定義する、スクリーンショットを見る:

doc-hide-used-items-dropdown-list-1

6。 の中に 新しい名前 ダイアログボックスで、[名前]テキストボックスに名前を入力し、この式を入力します =OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1) を参照する フィールド、スクリーンショットを参照してください:

doc-hide-used-items-dropdown-list-1

:上の数式では、C1:C11はステップ3で作成したヘルパー列の範囲であり、Sheet 2は現在使用しているシートです。

7。 設定が完了したら、ドロップダウンリストを作成し、ドロップダウンリストを配置するセルF1:F11を選択してから、をクリックします。 データ > データ検証 > データ検証、スクリーンショットを見る:

doc-hide-used-items-dropdown-list-1

8。 の中に データ検証 ダイアログボックスで、をクリックします。 設定 タブを選択してから リスト から 許可する ドロップダウンリスト、そして次に ソース セクションに、次の数式を入力します。 = namecheckネームチェック ステップ6で作成した範囲の名前です)、スクリーンショットを参照してください:

doc-hide-used-items-dropdown-list-1

9。 そして、 OK ボタンをクリックしてこのダイアログを閉じます。ドロップダウンリストが選択範囲内に作成され、ドロップダウンから1つの名前を選択すると、この使用された名前がリストから削除され、使用されていない名前のみが表示されます、スクリーンショットを見る:

doc-hide-used-items-dropdown-list-1

先端:上記の手順で作成したヘルパー列を削除することはできません。削除した場合、ドロップダウンリストは無効になります。


関連記事:

Excelにドロップダウンリストを挿入するには?

どのように迅速にExcelでダイナミックドロップダウンリストを作成するには?

どのようにExcelで画像をドロップダウンリストを作成するには?


Kutools for Excelは、ほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート そして、以前に使用したもの; セルを暗号化する パスワード付き メーリングリストの作成 そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストや数式を簡単に編集する) レイアウトを読む (多数のセルを簡単に読んで編集できます)。 フィルター範囲に貼り付ける...
  • セル/行/列を結合 データを失うことなく; セルコンテンツの分割。 重複する行/列を結合する...重複セルの防止。 範囲の比較...
  • 重複または一意を選択します空白行を選択 (すべてのセルは空です)。 スーパー検索とファジー検索 多くのワークブックで。 ランダム選択
  • 完全コピー 式の参照を変更せずに複数のセル。 参照を自動作成 複数のシートに 箇条書きを挿入、チェックボックスなど
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷 セルのコンテンツとコメント間の変換...
  • スーパーフィルター (保存して他のシートにフィルタ方式を適用する)。 高度な並べ替え 月/週/日、頻度などによる。 特殊フィルター 太字、斜体で...
  • ワークブックとワークシートを組み合わせる; キー列に基づいて表をマージします。 データを複数のシートに分割する; xls、xlsx、およびPDFのバッチ変換...
  • 300を超える強力な機能。 Office / Excel 2007-2019および365をサポートします。 すべての言語をサポートします。 企業または組織に簡単に展開できます。 フル機能の30日間の無料トライアル。
KTEタブ201905

OfficeタブはOfficeにタブ付きインターフェイスを提供し、作業をより簡単にします

  • Word、Excel、PowerPointでタブ付き編集と読み取りを有効にする、出版社、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を50%向上させ、毎日数百回のマウスクリックを削減します。
オフィシタブ底
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    John · 10 months ago
    Is there a way to have only some of the options get removed when selected and others be permanent?
  • To post as a guest, your comment is unpublished.
    Keith Race · 11 months ago
    How do I get this activity to work if I transpose from Row to Column
  • To post as a guest, your comment is unpublished.
    Sam · 1 years ago
    I’ve entered all the formulas correctly, but the only name showing up is the first one on the list. What am I doing wrong??
  • To post as a guest, your comment is unpublished.
    Stefan · 2 years ago
    Works great, however, if you have two people on the list with the same name e.g. John Smith it removes both incidents of 'John Smith' from the list when you select one of them.


    Is there a way to amend this so that you have have multiple versions of the one name without them all being removed?


    Thanks.
  • To post as a guest, your comment is unpublished.
    Derric · 3 years ago
    How would you change this formula for use of data validation list across multiple rows instead of a single column. Is that possible?

    Thanks
  • To post as a guest, your comment is unpublished.
    Mohanraj · 3 years ago
    Awesome, Working for me...
  • To post as a guest, your comment is unpublished.
    tim · 3 years ago
    I changed mine to accommodate my needs "=IF(COUNTIF(Statusboard!$C:$C,A1)>=1,"",ROW())" Make sure you change the $F$1:$F$11 from "=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())" to wherever your list is in my case it was Statusboard!$C:$C,A1.
  • To post as a guest, your comment is unpublished.
    Yolanda · 4 years ago
    I need to be able to create a list that has items that disappear but I want to be able to use it in multiple columns on the same sheet. Does anyone know how to do that? - Thanks!
  • To post as a guest, your comment is unpublished.
    Yolanda · 4 years ago
    I need to be able to use this list multiple times in multiple columns but as soon as an item disappears it's gone for good. Does anyone know how I can create this and be able to use it for multiple columns? Thanks!
  • To post as a guest, your comment is unpublished.
    NighT · 4 years ago
    Hey all,
    I got this to work, and it works like a charm!
    @Amanda, yes. I have my data on a different sheet as the dropdown menu. See the below written formulas.

    @Filip,
    Yes, you can use a formula to automatically select the unique values from a list. I used this to have a dynamic list.

    Mind you; I used google and a lot of different website to get to this formula, so it's not all my own work.
    First: to get the list of things to display:
    IF(INDEX(Sheet1!$A$2:$A$100;MATCH(0;COUNTIF($AA$14:AA14;Sheet1!$A$2:$A$100);0))=0;"";INDEX(Sheet1!$A$2:$A$100;MATCH(0;COUNTIF($AA$14:AA14;Sheet1!$A$2:$A$100);0)))

    === Basically this is the same formula twice. Which will give an empty ("") value if no further unique values are found. Anyway, the formula returns an unique list of values from my 'Sheet1!'. (lets say for easy reference I have this formula on Sheet 2, column A)

    Then I just start using the same formula as above (my sheet 2 column B):
    IF(COUNTIF(Sheet3!$S$2:$U$4;A1)>=1;"";ROW())

    === Sheet 3 is where I have my dropdowns. This is probably what you're looking for Amanda.

    Then the last bit of the formula:
    IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$24);"";INDEX(A:A;SMALL(B$1:B$24;1+ROW(A1)-ROW(A$1))))

    === This formula is in my Sheet 2 column C.
    This *should* work.

    Good luck to you all! And again, a great thank you to the author!
    Regards,

    NighT
  • To post as a guest, your comment is unpublished.
    Filip · 4 years ago
    Thanks for this guide. I'm just asking if it's possible when I update a value in formula, this value will be updated automatically in list of items?

    Example:
    I select value "James" from list in cell F
    Now, I want to change value from "James" to "Thomas". I rewrite value "James" to "Thomas" in cell A, formula automatically change value in cell C. It's OK, but I need this changed value is automatically updated in cell F as well.

    How can I reach that? Any ideas?
  • To post as a guest, your comment is unpublished.
    Amanda · 4 years ago
    Does anyone know if it is possible to use this between sheets? For instance if the original info (the column A portion) is on one sheet, but the dropdown (the column F portion) is on another? How would that change the formula?
  • To post as a guest, your comment is unpublished.
    Kent · 4 years ago
    Hi, I can't get it to work properly.
    When trying to complete step 8 I get a message about 'The source returns an error at evaluation. Do you wish to continue?'

    I am using Excel 2010, any idea?
    • To post as a guest, your comment is unpublished.
      Amanda · 4 years ago
      I had that happen at first, too. I had not changed the "sheet2" portion to the proper sheet name for what I was using.