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

Question

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

Years
1993
1993
1994
1994
1994
...
2011
2011

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

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

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

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

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

Answer 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

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

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

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(диапазон) в заголовок сообщения об ошибке проверки данных.

Answer 2

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

Answer 3

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

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

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

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

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