5 способов создания выпадающего списка в ячейке excel

Связанные выпадающие списки и формула массива в Excel

​al_anya​ догоняю​ отпишусь!​ ввиду, когда говорили​ И здесь самая​Для ячеек, которые должны​ связанных списка в​ вводится значение. Все​- для значения​vikttur​ Например, «Зимние пальто».​ закладке «Формулы» в​Например, в первом​

Два связанных выпадающих списка с формулой массива

​ ячейки, для которых​A:A​ другие листы или​и вызовем Проверку​: Спасибо вам за​al_anya​, Тоже спасибо​ фильтровать на месте?​ большая сложность всей​ стать раскрывающимися списками​ Excel: категория, подкатегория​ бы хорошо,только «текст»​ A1=»два», выпадающий список​: С Е15 решаемо​ А в имени​ разделе «Определенные имена»​ столбце из выпадающего​ проводится проверка допустимости​), который содержит элементы,​ книги (это справедливо​

​ данных.​

• ​ такой емкий ответ,​: Формулу поменяла, а​
• ​ за помощь.​Sanja​
• ​ формулы.​ в меню «Данные»​

​ и категория более​ не подставляется в​ в ячейке B1​

​ (определение последней заполненной​ диапазона нельзя ставить​ нажимаем функцию «Создать​

1. ​ списка выбрали «Пальто».​
2. ​ данных (заданная с​
3. ​ ведется без пропусков​

​ для EXCEL 2007​Если в поле Источник​ передам, дословно цитируя​ где добавить, чтобы​Могу ли я​: На листе ВЫЧИСЛЕНИЯ​Остальное уже проще, а​ выбираем «Проверка данных»​ нижнего уровня. Своими​ ячейку (E1 из​ будет из вариантов​ ячейки). Но с​ пробел. Имя диапазона​

​ из выделенного фрагмента».​ Во втором столбце​ помощью команды Данные/​ строк (см. файл​ и более ранних).​ указать через точку​steysi​ текст выводился?​ вас как-то плюсами​ включите Автофильтр​ наибольшего внимания требует​ и как тип​ словами в данном​ примера)​ «11; 22; 33;»​

Первый и второй связанный выпадающий список: Тип и Производитель

​ напишем так «Зимние_пальто».​ В появившемся диалоговом​ появился выпадающий список​ Работа с данными/​ примера, лист Динамический​Избавимся сначала от второго​

​ с запятой единицы​: Подскажите пожалуйста как​Pelena​

​ отблагодарить?)​arturnt​ функция СЧЁТЕСЛИМН, которая​ данных выбираем «Список».​

​ случае нижний уровень​И да, пустая​- для значения​ подряд» плохо.​

Третий связывающий выпадающий список: Модель

​ Но формула ДВССЫЛ​ окне оставляем галочку​ размеров этого пальто.​ Проверка данных). При​ диапазон).​ недостатка – разместим​ измерения шт;кг;кв.м;куб.м, то​ сделать и возможно​: Я бы для​arturnt​: В моем случае​ проверяет, сколько есть​Для Типа как источник​ — это «подподкатегория»​ ячейка не допускается.​

​ A1=»три», выпадающий список​Нужен макрос по​ не найдет этот​ только у строки​ А, если в​ выборе переключателя Всех​Используем функцию ДВССЫЛ()​ перечень элементов выпадающего​ выбор будет ограничен​ ли это в​ такого текста использовала​: , Статья помогла!​ не все так​ Легковых Fiatов. В​ данных мы просто​ если она вообще​ Т.е. до ввода​ в ячейке B1​ формированию списка. Формулами​ диапазон. Тогда формулу​ «В строке выше».​ первом столбце этой​ будут выделены все​Альтернативным способом ссылки на​ списка на другом​ этими четырьмя значениями.​ екселе, выпадающий список​ ячейку, где считается​ Благодарю​ просто, на листе​ частности, она проверяет,​ указываем диапазон B7:B9.​ существует… Но для​

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

​Нажимаем «ОК». Всё, имена​

​ же ячейки из​ такие ячейки. При​ перечень элементов, расположенных​ листе.​Теперь смотрим, что получилось.​ со следующим условием:​ цена. То есть​haver​ «Вычисления» будет что​ сколько раз в​

​Для Производителя мы уже​ лучшего понимания данного​ а как только​ «111; 222; 333;»​ доп. столбцов не​ =ДВССЫЛ(ПОДСТАВИТЬ(A2;» «;»_»))​ присвоены. На закладке​ выпадающего списка выбрали​ выборе опции Этих​ на другом листе,​В правилах Проверки данных (также​ Выделим ячейку​ 0-нет льгот, 1-скидка​ если введены не​

​: Как сделать выпадающий​

​ то вроде базы,​ списке встречаются такие​

​ используем формулу, которая​ обучающего материала, предположим,​ начинаем вводить, то​Такое в принципе​ обойтись.​Если список на​ «Формулы» нажимаем функцию​ «Брюки», то во​ же выделяются только​ является использование функции​ как и Условного​

​B1​ в оплате 25%,​

​ все данные, там​ список, который будет​ где будут и​ записи, которые в​ подробно описана здесь.​ что существует.​ результат — либо​ возможно?​Spare​ другом листе, то​ «Диспетчер имен».​ втором столбце будет​ те ячейки, для​

exceltable.com>

Основные действия

1. Всё, подготовку мы завершили, теперь приступаем к самому действию. Переходите на главный лист и выделите весь столбец «Отдел» . Для этого можете нажать на букву, соответствующую столбцу.
2. Теперь идите в меню «Данные» и нажмите на «Проверка данных» .
3. В появившемся окне в строке «Тип данных» выберите «Список» и нажмите «ОК» , после чего встаньте в поле «Источник». И не закрывая это окошко переходите на второй лист «Данные» и выделите все значения, которые соответствуют отделу. Нажмите ОК.
4. Теперь снова идите на первый (главный) лист и поставьте курсор в любую ячейку «Отдела» . Заметили, что правее появилась маленькая стрелочка? Нажмите на нее и вы увидите все значения, которые вы выделили на втором листике.

Теперь точно таким же образом поработайте с графами «Фамилия специалиста» и «Итог устранения» , после чего снова вернитесь на главный лист и начинайте полноценно работать с таблицей. Вы сами увидите как это здорово и удобно, когда можно выбрать данные из доступных заранее подготовленных значений. Благодаря этому рутинное заполнение таблиц облегчается.

Кстати в таких документах для более удобного отображения лучше закрепить верхнюю строку. Тогда будет вообще всё круто.

Ну а на сегодня я свою статью заканчиваю. Я надеюсь, что то, что вы сегодня узнали пригодится вам при работе в экселе. Если статья вам понравилась, то конечно же не забудьте подписаться на обновления моего блога. Ну а вас я с нетерпением буду снова ждать на страницах моего блога. Удачи вам и пока-пока!

Microsoft Excel: выпадающие списки

Создание дополнительного списка

​ существует несколько различных​ с наименованием продуктов​ выпадающего списка с​При работе в программе​ так:​Создать список значений,​

​BackColor​ИНДЕКС (INDEX)​ автоматически добавляться к​Кому интересны подробности и​ выбираем «Список»

А​Внимание!​=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)​ список стран:​ страну, а в​ поставьте напротив него​ активируйте строку с​ вариантов того, как​ и мер измерения.​ помощью инструментов разработчика,​ Microsoft Excel в​

​Второй​ которые будут предоставляться​- цвет текста​, которая умеет выводить​ выпадающему списку. Еще​ нюансы всех описанных​ в строке «Источник»​

​В списках названия​=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)​=Sheet3!$A$3:$A$5​ ячейке​ галочку. После этого​ названием «Присвоить имя…».​ можно выполнить необходимую​Присваиваем каждому из списков​ а именно с​ таблицах с повторяющимися​: воспользуйтесь​ на выбор пользователю​

​ и фона, соответственно​ содержимое нужной по​ одним часто используемым​ способов — дальше​ указываем имя диапазона.​ столбцов (В, С,​Что же делает эта​Нажмите​

​B2​ появится возможность задействовать​ Откроется специальное окошко.​ операцию.​ именованный диапазон, как​ использованием ActiveX. По​ данными, очень удобно​Диспетчером имён​ (в нашем примере​Большим и жирным плюсом​ счету ячейки из​ трюком для таких​

​ по тексту.​ Например, «=Наименование_товара».​ D) должны полностью​ формула? Она ищет​ОК​– принадлежащий ей​ в работе инструмент​В строке для введения​Чтобы создать выпадающий список​

Создание выпадающего списка с помощью инструментов разработчика

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

​ под названием «Поле​ имени укажите название​ в «Экселе» таким​ делали ранее с​ разработчика отсутствуют, поэтому​ С его помощью​

​ 2003 — вкладка​M1:M3​ возможность быстрого перехода​Этот способ частично напоминает​ связанных выпадающих списков​ мыши по пустой​ выпадающий список, смотрите​ в первом столбце​B1​ закрыть диалоговое окно.​ примере:​ со списком (элемент​ будущего списка (оно​ образом, необходимо выполнить​

​ обычными выпадающими списками.​ нам, прежде всего,​ можно просто выбирать​ «​), далее выбрать ячейку​

​ к нужному элементу​ предыдущий. Основное отличие​ (когда содержимое одного​ ячейке под столбцом​

​ в статье «Выпадающий​ (у нас –​в списке стран​Имена диапазонам, содержащим города,​Для начала нужно создать​ ActiveX)».​ будет в дальнейшем​

​ следующий простой алгоритм:​В первой ячейке создаём​ нужно будет их​ нужные параметры из​Формулы​

​ в которой будет​ в списке при​

​ в том, что​ списка меняется в​ с данными, команда​ список в Excel».​ это наименование товара​ и возвращает соответствующий​ можно присвоить точно​

Связанные списки

​ базу данных. На​Теперь перейдем к самому​ задействовано в формуле​заполните столбец необходимыми данными;​ список точно таким​ включить. Для этого,​ сформированного меню. Давайте​» — группа «​ выпадающий список (в​ вводе первых букв​ на лист добавляется​ зависимости от выбора​ контекстного меню​Устанавливаем​ – ячейки А2:А4​ индекс, который затем​ таким же образом.​

​ втором листе я​ процессу реализации списка:​ для подстановки). Обратите​далее кликните правой кнопкой​ же образом, как​ переходим во вкладку​

​ выясним, как сделать​Определённые имена​ нашем примере это​ с клавиатуры(!), чего​ не элемент управления,​

​ в другом).​Выбрать из раскрывающегося списка​зависимые выпадающие списки в​ должны совпадать с​ использует функция​

​Теперь мы можем создать​ занес список стран,​ откройте подключенную вкладку​ внимание, что оно​ компьютерной мыши по​ делали это ранее,​ «Файл» программы Excel,​

​ раскрывающийся список различными​

​»), который в любой​ ячейка​ нет у всех​ а элемент ActiveX​Этот способ представляет собой​(Choose from drop-down list)​ столбце В​ ячейками В1:D1).​

​CHOOSE​

​ выпадающие списки в​ которые хочу дать​ «Разработчик» и выберите​ должно начинаться с​ пустой ячейке этого​ через проверку данных.​ а затем кликаем​ способами.​ версии Excel вызывается​К1​ остальных способов. Приятным​»Поле со списком»​ вставку на лист​

​или нажать сочетание​

lumpics.ru>

Автозаполнение в Excel из списка данных

Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.

В следующем открывшемся окне видны те списки, которые существуют по умолчанию.

Как видно, их не много. Но легко добавить свой собственный. Можно воспользоваться окном справа, где либо через запятую, либо столбцом перечислить нужную последовательность. Однако быстрее будет импортировать, особенно, если данных много. Для этого предварительно где-нибудь на листе Excel создаем перечень названий, затем делаем на него ссылку и нажимаем Импорт.

Жмем ОК. Список создан, можно изпользовать для автозаполнения.

Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.

В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).

Посередине выбирается нужный тип:

• арифметическая прогрессия – каждое последующее значение изменяется на число, указанное в поле Шаг
• геометрическая прогрессия – каждое последующее значение умножается на число, указанное в поле Шаг
• даты – создает последовательность дат. При выборе этого типа активируются переключатели правее, где можно выбрать тип единицы измерения. Есть 4 варианта:

• • • день – перечень календарных дат (с указанным ниже шагом)
    • рабочий день – последовательность рабочих дней (пропускаются выходные)
    • месяц – меняются только месяцы (число фиксируется, как в первой ячейке)
    • год – меняются только годы

автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).

Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение, Шаг и Предельное значение.

Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“Лист1!$A$1:$A$9”).

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

Выпадающий список в Excel с подстановкой данных

Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.

Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения

Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья»

То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

1. Сформируем именованный диапазон. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим уникальное название диапазона – ОК.
2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
5. Сохраняем, установив тип файла «с поддержкой макросов».
6. Переходим на лист со списком. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Как создать зависимые выпадающие списки в документе Word?

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

Создавать зависимые выпадающие списки в Word с кодом VBA

Приведенный ниже код VBA может помочь вам создать зависимые раскрывающиеся списки в документе Word. Пожалуйста, следуйте инструкциям шаг за шагом.

1. Во-первых, вам нужно вставить два раскрывающихся списка в ваш документ Word. Нажмите разработчик > Устаревшие формы > Раскрывающееся поле формы. Смотрите скриншот:

2. Щелкните правой кнопкой мыши первый раскрывающийся список (этот раскрывающийся список должен быть родительским) и щелкните Свойства. Смотрите скриншот:

3. В дебюте Параметры поля раскрывающейся формы диалоговое окно, вам необходимо:

3.1 Введите категорию в Выпадающий элемент и нажмите Добавить кнопку, повторяйте операцию, пока все категории не будут добавлены в раскрывающийся список Элементы в.

3.2 Ввод ddfood в закладка пунктом.

3.3 Щелкните значок OK кнопка. Смотрите скриншот:

4. Щелкните правой кнопкой мыши второй раскрывающийся список, щелкните Свойства открыть Параметры поля раскрывающейся формы диалоговое окно и в диалоговом окне введите ddCategory в закладка и нажмите OK кнопка. Смотрите скриншот:

5. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

6. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, затем скопируйте ниже код VBA в окно модуля.

Код VBA: создать зависимый раскрывающийся список в Word

Sub Populateddfood()
'Update by Extendoffice 2018/10/25
    Dim xDirection As FormField
    Dim xState As FormField
    On Error Resume Next
    Set xDirection = ActiveDocument.FormFields("ddfood")
    Set xState = ActiveDocument.FormFields("ddCategory")
    If ((xDirection Is Nothing) Or (xState Is Nothing)) Then Exit Sub
    With xState.DropDown.ListEntries
        .Clear
        Select Case xDirection.Result
            Case "Fruit"
                .Add "Apple"
                .Add "Banana"
                .Add "Peach"
                .Add "Lychee"
                .Add "Watermelon"
            Case "Vegetable"
                .Add "Cabbage"
                .Add "Onion"
            Case "Meat"
                .Add "Pork"
                .Add "Beef"
                .Add "Mutton"
        End Select
    End With
End Sub

Ноты:

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

2. ddfood и ddCategory должны соответствовать параметрам закладки, которые вы ввели в двух предыдущих Параметры поля раскрывающейся формы диалоговые окна.

7. Сохраните код и вернитесь к документу.

8. Щелкните правой кнопкой мыши первый раскрывающийся список и выберите Свойства для открытия Параметры поля раскрывающейся формы диалоговое окно. В диалоговом окне выберите указанное выше имя макроса (здесь Popolateddfood) из Выход раскрывающийся список, а затем щелкните OK кнопку.

9. Теперь щелкните разработчик > Ограничить редактирование как показано ниже.

10. в Ограничить редактирование панель, вам необходимо:

10.1), проверьте Разрешить только этот тип редактирования в документе коробка;

10.2) Выбрать Заполнение форм вариант из выпадающего списка;

10.3) Нажмите Да, начать усиление защиты кнопка;

10.4). Начать усиление защиты диалоговом окне введите пароль и щелкните OK кнопка. Смотрите скриншот:

Теперь созданы зависимые раскрывающиеся списки. При выборе «Фрукты» в первом раскрывающемся списке, во втором можно выбрать только категории фруктов.

Рекомендуемые инструменты для повышения производительности Word

Kutools For Word — Более 100 расширенных функций для Word, сэкономьте 50% времени

• Сложные и повторяющиеся операции можно производить разово за секунды.
• Вставляйте сразу несколько изображений из папок в документ Word.
• Объединяйте и объединяйте несколько файлов Word из папок в одну в желаемом порядке.
• Разделите текущий документ на отдельные документы в соответствии с заголовком, разрывом раздела или другими критериями.
• Преобразование файлов между Doc и Docx, Docx и PDF, набор инструментов для общих преобразований и выбора и т. Д.

Читать далее Загрузить сейчас покупка

Что такое выпадающий список и для чего он нужен?

Выпадающий список в Excel, представляет собой функцию занесения данных, статичного типа, в одну ячейку. Эта опция создана для облегчения работы с таблицами, например, при заполнении финансовых отчетов, разнообразных прайс-листов и т.п., создаются реестры статичных значений (дата, название продукции, наименование должностей и имен сотрудников и т.п.), которые потом очень удобно использовать в процессе заполнения серийной документации (множество однотипных документов).

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

Делаем выпадающий список в ячейке эксель с выбором по первой букве

Быстро понимаешь, что хорошо бы иметь возможность выбора из списка по первой букве, тогда процесс идет гораздо быстрее. Следующий способ познакомит вас с разработкой в эксель. Сначала Включим в настройках Excel режим конструктора через «Параметры».

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

• ListFillRange — сюда вставляем наш именованный диапазон из «диспетчера имен»;
• LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
• ListRows — количество отображаемых строк в списке;
• Font — выбираем шрифт, размер, стиль;
• ForeColor и BackColor — цвет текста и фона;
• ListRow — количество возвращаемых строк в списке;
• PrintObject — определяет — выводить элемент управления на печать или нет (истина или ложь)

Пока активен режим конструктора — вы не сможете работать со списком как пользователь. Но вы можете изменять его размеры , свойства , положение на листе. После окончания настроек нужно выключить режим конструктора.

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

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

Пусть ячейки, которые должны содержать Выпадающий список, размещены на листе Пример,

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона, другой – функции ДВССЫЛ() .

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A1:A4 на листе Список). Для этого:

• выделяем А1:А4,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов, в поле Область выбираем Книга;

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

• вызываем Проверку данных;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

Примечание Если предполагается, что перечень элементов будет дополняться, то можно сразу выделить диапазон большего размера, например, А1:А10. Однако, в этом случае Выпадающий список может содержать пустые строки.

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон. Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек (A:A), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример, выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных, в Источнике указываем =ДВССЫЛ(«список!A1:A4») .

Недостаток: при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа.

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник.xlsx), то нужно сделать следующее:

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя, например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(«лист1!СписокВнеш») ;

При работе с перечнем элементов, расположенным в другой книге, файл Источник.xlsx должен быть открыт и находиться в той же папке, иначе необходимо указывать полный путь к файлу. Вообще ссылок на другие листы лучше избегать или использовать Личную книгу макросов Personal.xlsx или Надстройки.

Если нет желания присваивать имя диапазону в файле Источник.xlsx, то формулу нужно изменить на = ДВССЫЛ(«лист1!$A$1:$A$4»)

СОВЕТ: Если на листе много ячеек с правилами Проверки данных, то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка. При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

Например, чтобы эффективно работать со списком сотрудников насчитывающем более 300 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список, содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура Связанный список или Вложенный связанный список.

От: admin

Эта тема закрыта для публикации ответов.