☰

Как создать раскрывающийся список для столбца с постоянно меняющимися значениями в Excel 2010?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Как создать раскрывающийся список для столбца с постоянно меняющимися значениями в Excel 2010?

У меня есть таблица Excel со столбцом за несколько лет и заголовком:

Years
1993
1993
1994
1994
1994
...
2011
2011

Имеются повторяющиеся значения по годам, и со временем будут добавлены дополнительные строки.

У меня есть еще одна ячейка, в которой нужно показать раскрывающийся список для лет, но только уникальные годы. Я пробовал использовать функцию проверки данных в Excel 2011, но у нее есть 2 проблемы:

  1. Он отображает повторяющиеся годы.
  2. Я говорю ему использовать весь столбец, и он включает пустые ячейки в раскрывающийся список.

Как получить раскрывающийся список лет, который будет отображать только уникальные значения и автоматически обновляться по мере добавления дополнительных строк?

Редактировать: немного больше информации. Раскрывающийся список используется на отдельном листе для отображения расчетных данных, как в форме Access. Пользователь может выбрать диапазон лет, и данные будут обновлены соответствующим образом. Исходный лист — это просто список всех данных.

решение1

Для такого рода проверок я использую VBA + один грязный трюк:

Сначала откройте редактор VBA с помощью Alt+F11. Затем я вставляю свой «Код проверки динамического списка» (tm) :) на соответствующий рабочий лист.

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

Этот код обновляет список проверки ячеек списком, сгенерированным формулой, введенной в Данные->Проверка->Сообщение об ошибке->Заголовок. Таким образом, каждая ячейка с проверкой списка может иметь свою собственную формулу.

Затем я добавляю модуль (Вставка->Модуль) и помещаю этот код в новый модуль:

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

Эта функция возвращает все ячейки в диапазоне без пробелов и повторений. Затем в каждой ячейке с проверкой списка я добавляю GenDynList(range) в заголовок сообщения об ошибке проверки данных.

решение2

Грязно. Нет встроенного способа сделать это автоматически обновляемым. Было бы проще просто создать отдельный список со всеми возможными годами, которые могут вас заинтересовать, а не пытаться ограничить его теми, которые есть в вашем наборе данных.

решение3

Добавьте сводную таблицу из данных на другом листе. Используйте годы в качестве строк, остальная часть таблицы не имеет значения. Отсортируйте и отфильтруйте строки по мере необходимости (например, явно отфильтруйте OUT "[blank]"). Сделайте ячейки, в которых находятся метки строк, целью проверки данных.

Используйте расширяемый именованный диапазон, чтобы всегда использовать все новые метки: http://www.ozgrid.com/Excel/DynamicRanges.htm

Повторите для других столбцов, таких как страна, штат и т. д. Если вы основываете все сводки на одном и том же диапазоне данных, они все будут обновляться вместе, когда вы обновляете любую из них. Теперь ваш процесс будет таким: добавить новые данные, обновить сводки, использовать проверку обновленных данных.

Дальнейшие мысли: Использование таблицы для исходных данных облегчит обновление сводных таблиц, поскольку они всегда будут использовать всю таблицу. В качестве альтернативы используйте расширяющийся именованный диапазон в качестве источника данных, чтобы избежать проблем при добавлении дополнительных строк.

Обратите внимание, что вы можете использовать диапазон из другого рабочего листа в качестве источника для проверки данных, если вы делаете это, определяя именованный диапазон. Вы не можете использовать обычный диапазон, явно ссылающийся на другой лист.

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