☰

Как изменить значение в раскрывающемся списке Excel

tag-icon • tag-icon microsoft-excel
Как изменить значение в раскрывающемся списке Excel

У меня есть столбец, который использует значения из предопределенного набора (раскрывающийся список), реализованный с помощьюПроверки данных. Сейчасесли я изменю какое-то значение в списке, раскрывающийся список (при открытии) немедленно предложит это новое значение. Однако,таблица не будет обновляться автоматически, что означает, что некоторые значения в столбце будут недействительными, пока я не исправлю их вручную.

Я видел решения, основанные на макросах/VBA, но разве нет какого-то разумного способа прямо в пользовательском интерфейсе Excel?

решение1

Как я уже упоминал в комментариях, единственный способ сделать это — использовать VBA.

Вот один из вариантов. Я добавил комментарии по всему коду. Это предполагает, что вы используете именованный диапазон для списка проверки с именем "Список" и что он находится на том же листе, что и проверяемые ячейки.

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?

Думаю, я знаю один — по крайней мере, он отвечает всем вашим запросам:

  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 лет в своей работе. Надеюсь, оно также будет вам полезным!

P.S. Вот сам пример файла:Динамическое выпадающее меню

Связанный контент