Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Динамические именованные диапазоны

Очень часто при использовании связки Выпадающий список-Именованный диапазон возникает проблема: при добавлении новых данных в диапазон они не отразятся сразу в списке - для этого необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Но тогда появляются лишние пустые значения в списке, что тоже не очень-то красиво и совсем не удобно, особенно если список не маленький. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, уже не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен(Ctrl+F3), задаем имя диапазона и в поле Диапазон (Refers to) пишем формулу:

Вариант с формулой СМЕЩ
=СМЕЩ(Лист2!$A$1;;;СЧЁТЗ(Лист2!$A$1:$A$1000);)
=OFFSET(Лист2!$A$1,,,COUNTA(Лист2!$A$1:$A$1000),)

Создание динамического диапазона

  • Где Лист2!$A$1 - первая ячейка значений для выпадающего списка
  • а Лист2!$A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка. Если значений может быть более 1000, то необходимо увеличить диапазон $A$1:$A$1000 на необходимое количество строк. Хотя мне лично страшно представить себе такой выпадающий список. Но динамический диапазон может ведь пригодиться и для других целей

В чем главный недостаток этого метода: если добавить в список значений пропуск между значениями в виде пустой строки - список отобразит не все значения списка, т.к. СЧЕТЗ считает количество непустых ячеек:
Пустая строка через СМЕЩ

Вариант с формулой ИНДЕКС
=Лист2!$A$1:ИНДЕКС(Лист2!$A$1:$A$1000;ПРОСМОТР(2;1/(Лист2!$A$1:$A$1000<>"");СТРОКА(Лист2!$A$1:$A$1000)))
=Лист2!$A$1:INDEX(Лист2!$A$1:$A$1000,LOOKUP(2,1/(Лист2!$A$1:$A$1000<>""),ROW(Лист2!$A$1:$A$1000)))

Изменяемые ссылки для этой формулы такие же, как и в случае с вариантом через СМЕЩ:

  • Лист2!$A$1 - первая ячейка значений для выпадающего списка
  • Лист2!$A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка

Однако метод через данную формулу лишен недостатка предыдущего метода: если добавить в список разрыв в виде пустой строки - список отобразится правильно, с этим разрывом и всеми значениями:
Список через ИНДЕКС

Примечание: созданные таким образом диапазоны нельзя использовать в составе функции ДВССЫЛ (INDIRECT) для создания зависимых выпадающих списков - список просто не будет работать

Скачать пример

  Tips_Lists_Dinamic_Range.xls (37,5 KiB, 4 937 скачиваний)


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 10 коммент.
  1. Спасибо, очень полезная функция.

  2. Владимир другой:

    Вместо $A$1:$A$1000
    надо написать $A:$A
    и считаться будет весь столбец, а не до 1000 :)

  3. Попробуйте записать $A:$A в версии Excel 2007, затем сохранить в формате 2003 и в 2003 Excel открыть. Не "поплывет" Ваша формула? В принципе, может, конечно и не поплыть. Но случаи подобные известны. Так что лично я бы советовал все же испльзовать диапазон с запасом, нежели ссылки на целый столбец.

  4. Андрей:

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

    Вот только как определить эту $ПоследнююЯчейкуДиапазона....

    Может быть плохо искал...
    Спасибо за Ваши труды.

  5. Андрей, будете смеяться - но действительно плохо искали. Вот ссылка на статью по определению последней ячейки: Как определить последнюю ячейку на листе через VBA?
    Кстати, выделять ячейку совершенно не обязательно:

    А полностью код выглядел бы так:

    Впишет введенное значение в первую пустую ячейку в столбце 1.

  6. Андрей:

    Нет, смеяться не буду, а просто улыбнусь.. :)
    А насчет ссылки - я просто до нее еще в тот момент не дошел - начал изучать постепенно, переходя по тематическим ссылкам с самого начала.
    Вам спасибо за подробное описание.
    Буду изучать дальше.

  7. ILHAM:

    Здравствуйте.
    А как можно привязать друг к другу два диапазона.
    Например:
    При выборе продуктов из списка в ячейке (А1) в другой ячейке (В1) выходила конкретная цена к выбранному продукту с возможностью выбора цены оптовой или розничной?
    Заранее благодарю!

  8. Ну что, идём по пути дальше, который предложил другой Владимир ;)

    =СМЕЩ(Лист1!$A$2;;;СЧЁТЗ(Лист1!$A:$A)-1;)
    Именованный диапазон выдаст в список все значения столбца, не считая 1-й строки (которая обычно бывает заголовком)

  9. @imort
    или можно так
    =СМЕЩ(Лист1!$A$1;1;;СЧЁТЗ(Лист1!$A:$A)-1;)

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти