Динамические именованные диапазоны
10880
Что умеет Excel
Очень часто при использовании связки Выпадающий список-Именованный диапазон возникает проблема: а что будет, когда я добавлю в свой диапазон новые данные? Ведь для того, чтобы они отобразились в списке, необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Тогда появляются лишние пустые значения в списке, что, согласитесь, тоже не очень-то красиво и не совсем удобно. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, Вам не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен и в поле Диапазон пишем формулу:
=СМЕЩ(Лист2!$A$1;;;СЧЁТЗ(Лист2!$A$1:$A$1000);)
Столбец, конечно свой указываете, в примере это столбец А. Если думаете, что у Вас может быть более 1000 значений, то увеличиваете значение $A$1000 на необходимое количество строк. Хотя мне лично страшно представить себе такой выпадающий список. Но динамический диапазон может ведь пригодиться и для других целей.
Примечание: созданный таким образом диапазон нельзя использовать в составе функции ДВССЫЛ при создании зависимых выпадающих списков(про зависимые выпадающие списки можно почитать тут). В смысле использовать можно, но результата не будет.
Tips_Lists_Dinamic_Range.xls (37,5 KiB, 2 296 скачиваний)
Спасибо, очень полезная функция.
Вместо $A$1:$A$1000
надо написать $A:$A
и считаться будет весь столбец, а не до 1000
Попробуйте записать $A:$A в версии Excel 2007, затем сохранить в формате 2003 и в 2003 Excel открыть. Не «поплывет» Ваша формула? В принципе, может, конечно и не поплыть. Но случаи подобные известны. Так что лично я бы советовал все же испльзовать диапазон с запасом, нежели ссылки на целый столбец.
Присоединяюсь к Владимиру, большое спасибо за подробное описание.
Раньше всегда использовал заранее просто большой диапазон и было очень некрасиво и неудобно при выборе списка с пустыми строками и как-то не задумывался о такой функции.
Вот бы теперь какой-то макрос к кнопке прикрутить, что-то типа «Добавить значение в список» с выводом формы:
Public Sub AddValueToList() Range("ПоследняяЯчейкаДиапазона").Select ActiveCell.Value = InputBox("Введите значение") End SubВот только как определить эту $ПоследнююЯчейкуДиапазона….
Может быть плохо искал…
Спасибо за Ваши труды.
Андрей, будете смеяться — но действительно плохо искали. Вот ссылка на статью по определению последней ячейки:
Кстати, выделять ячейку совершенно не обязательно:
Public Sub AddValueToList() Range("ПоследняяЯчейкаДиапазона").Value = InputBox("Введите значение") End SubА полностью код выглядел бы так:
Public Sub AddValueToList() Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = InputBox("Введите значение") End SubВпишет введенное значение в первую пустую ячейку в столбце 1.
Нет, смеяться не буду, а просто улыбнусь..
А насчет ссылки — я просто до нее еще в тот момент не дошел — начал изучать постепенно, переходя по тематическим ссылкам с самого начала.
Вам спасибо за подробное описание.
Буду изучать дальше.