Динамические именованные диапазоны
Очень часто при использовании связки Выпадающий список-Именованный диапазон возникает проблема: при добавлении новых данных в диапазон они не отразятся сразу в списке - для этого необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Но тогда появляются лишние пустые значения в списке, что тоже не очень-то красиво и совсем не удобно, особенно если список не маленький. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, уже не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен(Ctrl+F3), задаем имя диапазона и в поле Диапазон
- Где
Лист2!$A$1 - первая ячейка значений для выпадающего списка - а
Лист2!$A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка. Если значений может быть более 1000, то необходимо увеличить диапазон $A$1:$A$1000 на необходимое количество строк. Хотя мне лично страшно представить себе такой выпадающий список. Но динамический диапазон может ведь пригодиться и для других целей
В чем главный недостаток этого метода: если добавить в список значений пропуск между значениями в виде пустой строки - список отобразит не все значения списка, т.к. СЧЕТЗ считает количество непустых ячеек:
Изменяемые ссылки для этой формулы такие же, как и в случае с вариантом через СМЕЩ:
Лист2!$A$1 - первая ячейка значений для выпадающего спискаЛист2!$A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка
Однако метод через данную формулу лишен недостатка предыдущего метода: если добавить в список разрыв в виде пустой строки - список отобразится правильно, с этим разрывом и всеми значениями:
Примечание: созданные таким образом диапазоны нельзя использовать в составе функции ДВССЫЛ
(INDIRECT) для создания зависимых выпадающих списков - список просто не будет работать
Tips_Lists_Dinamic_Range.xls (37,5 KiB, 6 787 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Спасибо, очень полезная функция.
Вместо $A$1:$A$1000
надо написать $A:$A
и считаться будет весь столбец, а не до 1000 :)
Попробуйте записать $A:$A в версии Excel 2007, затем сохранить в формате 2003 и в 2003 Excel открыть. Не "поплывет" Ваша формула? В принципе, может, конечно и не поплыть. Но случаи подобные известны. Так что лично я бы советовал все же испльзовать диапазон с запасом, нежели ссылки на целый столбец.
Присоединяюсь к Владимиру, большое спасибо за подробное описание.
Раньше всегда использовал заранее просто большой диапазон и было очень некрасиво и неудобно при выборе списка с пустыми строками и как-то не задумывался о такой функции.
Вот бы теперь какой-то макрос к кнопке прикрутить, что-то типа "Добавить значение в список" с выводом формы:
Вот только как определить эту $ПоследнююЯчейкуДиапазона....
Может быть плохо искал...
Спасибо за Ваши труды.
Андрей, будете смеяться - но действительно плохо искали. Вот ссылка на статью по определению последней ячейки:Как определить последнюю ячейку на листе через VBA?
Кстати, выделять ячейку совершенно не обязательно:
А полностью код выглядел бы так:
Впишет введенное значение в первую пустую ячейку в столбце 1.
Нет, смеяться не буду, а просто улыбнусь.. :)
А насчет ссылки - я просто до нее еще в тот момент не дошел - начал изучать постепенно, переходя по тематическим ссылкам с самого начала.
Вам спасибо за подробное описание.
Буду изучать дальше.
Здравствуйте.
А как можно привязать друг к другу два диапазона.
Например:
При выборе продуктов из списка в ячейке (А1) в другой ячейке (В1) выходила конкретная цена к выбранному продукту с возможностью выбора цены оптовой или розничной?
Заранее благодарю!
ILHAM, в этом же разделе есть статья:Связанные выпадающие списки
Ну что, идём по пути дальше, который предложил другой Владимир ;)
=СМЕЩ(Лист1!$A$2;;;СЧЁТЗ(Лист1!$A:$A)-1;)
Именованный диапазон выдаст в список все значения столбца, не считая 1-й строки (которая обычно бывает заголовком)
или можно так
=СМЕЩ(Лист1!$A$1;1;;СЧЁТЗ(Лист1!$A:$A)-1;)