Хитрости »
Основные понятия (22)
Сводные таблицы и анализ данных (7)
Графики и диаграммы (5)
Работа с VB проектом (11)
Power BI и Power Query (11)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (62)
Разное (37)

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

Очень часто при использовании связки Выпадающий список-Именованный диапазон возникает проблема: при добавлении новых данных в диапазон они не отразятся сразу в списке - для этого необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Но тогда появляются лишние пустые значения в списке, что тоже не очень-то красиво и совсем не удобно, особенно если список не маленький. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, уже не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен(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, 5 332 скачиваний)


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

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

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

    Есть вариант ещё интереснее.
    Размещаем список в "Таблице" (Вставка\Таблица)
    Создаём Диапазон ссылающийся на часть таблицы с данными например =Таблица1[Почта](если заголовок списка "Почта" в таблице "Таблица1")
    Далее всё как указано в основном описании по созданию списка в третьем варианте отсюда: http://www.excel-vba.ru/chto-umeet-excel/vypadayushhie-spiski/

    При необходимости увеличения или изменения состава списка просто добавляете значения вниз таблицы со списком (таблица сама расширится включив новое значение в список) и сортируете. Пустые значения упадут вниз и их можно просто исключить из таблицы или оставить.
    При изменении заголовка списка ссылка в именованном диапазоне поменяется сама.
    Важный момент в настройках должна быть включена функция "Формулы\Использовать имена таблиц в формулах".

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

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


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

Тренинги

Заказать
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2017 Excel для всех  Войти
Авторизация
*
*
Регистрация
*
*
*
Пароль не введен
*
captcha
Генерация пароля