☰

Excel のドロップダウン リストの値を変更する方法

tag-icon • tag-icon microsoft-excel
Excel のドロップダウン リストの値を変更する方法

定義済みのセット（ドロップダウンリスト）の値を使用する列があり、次のように実装されています。データ検証。 今リスト内の値を変更するとドロップダウン（開いている場合）にはすぐにこの新しい値が表示されます。ただし、テーブルは自動更新されませんつまり、手動で修正するまで、列内の一部の値は無効になります。

マクロ/VBA に基づくソリューションを見たことがありますが、Excel UI で直接実行できる賢い方法はないでしょうか?

答え1

コメントで述べたように、これを実行する唯一の方法は VBA を使用することです。

ここに 1 つのオプションがあります。コード全体にコメントを追加しました。これは、検証リストに「List」という名前の名前付き範囲を使用しており、検証対象のセルと同じシート上にあることを前提としています。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim isect As Range
    Dim vOldValue As Variant, vNewValue As Variant


    Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
    If Not isect Is Nothing Then
        ' Get previous value of this cell
        Application.EnableEvents = False
        With Target
            vNewValue = .Value
            Application.Undo
            vOldValue = .Value
            .Value = vNewValue
        End With

        ' For every cell with validation
        For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
            With cell
                ' If it has list validation AND the validation formula matches AND the value is the old value
                If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
                    ' Change the cell value
                    cell.Value = vNewValue
                End If
            End With
        Next cell
        Application.EnableEvents = True
    End If
End Sub

ダウンロードすることもできますサンプルスプレッドシートこれをテストするためにまとめました。（マクロが含まれています！）

答え2

しかし、Excel UI で直接実行できる賢い方法はないのでしょうか?

私は 1 つ知っていると思います。少なくとも、それはあなたの要求をすべて満たしているようです。

  1. 設定する必要があります動的に変化する名前付き領域データの検証のソースとして。これは、関数を使用して実現できますOFFSET。 の列 A にドロップダウン ボックスの値のリストがありSheet1(名前は数式でのみ重要です)、セルA1にヘッダー (例 ) がありList of values、値が から下に配置されていると仮定するとA2、次の操作を行う必要があります。リボン に移動しFormulas > Name Manager、新しい領域 ( と呼びますItems) を作成し、その領域をセルを指すのではなく、代わりに次の数式を使用するように設定します=OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1)。
  2. セットデータ検証ルール: リストのセル範囲を指定する代わりに、「=Items-」と入力します。これにより、名前付き領域がリスト項目のソースとして使用されます。
  3. 上記の結果、すべての要件を満たす真に動的なリストが得られます。列 A の項目を自由に変更/追加でき、これらの変更は、次回ドロップダウンを使用するときにすぐに反映されます。同時に、古い値はそのまま残ります。

私は仕事でこのソリューションを約 2 年間使用しています。皆さんにも役立つことを願っています。

PS 実際のサンプル ファイルは次のとおりです。ダイナミックドロップダウン

関連情報