Как создать или удалить выпадающий список в Excel

Правильное введение данных

Алгоритм, как сделать выпадающий список в «Excel» рассмотрели, но следует обратить внимание на ряд дополнительных моментов, корректного внесения информации:

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

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

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

Если у Вас остались вопросы по теме “Что такое выпадающий список в Excel и как его создать?”, то можете задать их в комментария

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

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

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

Комбинация СМЕЩ + ПОИСКПОЗ

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

Вновь перед нами стоит задача создать двухуровневый выпадающий список. Сначала – выбор марки, затем – только модели этой марки.

Первое условие – исходные данные должны быть отсортированы по маркам, а внутри марок – по моделям. То есть, нужно отсортировать по столбцу А, а затем – по В.

Начнем с простого. В ячейке D1 создадим выпадающий список из марок автомобилей. Для этого в F1:F3 запишем их названия и затем употребим их в качестве источника. Напомню, что нужно нажать Меню – Данные – Проверка данных.

Далее нам нужно в D2 создать второй уровень, где будут только модели выбранной марки. В этот раз источник данных мы определим несколько иначе, чем ранее. Воспользуемся тем, что функция СМЕЩ может возвращать массив данных, который мы как раз и можем употребить в качестве наполнения нашего второго перечня. Но для этого ей нужно передать целых 5 параметров:

координаты верхней левой ячейки,
на сколько строк нужно сместиться вниз – A,
на сколько столбцов нужно перейти вправо – B,
высота массива (строк) – C,
ширина массива (столбцов) D.

Зеленым цветом на рисунке выделен новый диапазон. Если параметры C и D не указать, то будет возвращено содержимое единственной ячейки, в которую мы переместились, сделав несколько шагов вниз и вправо.

Традиционно точкой отсчета для функции СМЕЩ возьмем ячейку A1. Теперь нам нужно решить, на сколько позиций вниз и вправо нужно перейти, чтобы указать левый верхний угол нового перечня с моделями. Предположим, первоначально мы выбрали Ford.

На сколько шагов сместиться вниз? Применим функцию ПОИСКПОЗ, которая возвратит нам номер позиции первого вхождения “Ford”.

Если первый раз нужное нам слово встретилось, к примеру, в 7-й позиции, то вычтем 1, чтобы получить количество шагов. То есть, начиная с первого значения, нужно сделать 6 шагов.

Третий параметр установим равным 1, так как нужно перейти на один шаг вправо из A в B. Мы находимся в начальной точке нашего диапазона. Теперь рассчитаем, на сколько ячеек вниз он будет продолжаться. Для этого подсчитаем, сколько раз “Ford” встречается в нашем перечне. Столько и будет значений вниз.

А теперь объединяем все это в СМЕЩ:

=СМЕЩ($A$1;ПОИСКПОЗ($D$1;$A$1:$A$22;0)-1;1;СЧЁТЕСЛИ($A$1:$A$22;$D$1);1)

Последняя единичка означает, что массив состоит из одной колонки.

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

Аналогичным образом можно создать и третий уровень зависимого выпадающего списка.

Еще полезная дополнительная информация:

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 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список, содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура Связанный список или Вложенный связанный список.

Способ 2 – Элемент управления.

В этом способе рассмотрен вариант добавления нового объекта и привязка его к конкретному диапазону в екселе. Какие необходимо выполнить шаги:

Если у вас версия ексель 2007 года и позже, тогда выбираем меню Разработчик. Если версия ранняя тогда Вид — Панели инструментов – Формы.

Открываем панель элементов

В появившемся диалоговом окне элементов ActiveX, находим значок со списком «Поле со списком».

Выносим элемент на страницу документа.

Формат элемента «Список»

В данном меню заносим диапазон значений и привязываем к ячейке данные.

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

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

Как нам это может пригодиться?

Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров. Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.

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

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

Проверка того, что мы вписали в таблицу, теперь уже не нужна.

Создание зависимых выпадающих списков

Зависимый список – это тот, на содержимое которого влияет выбор пользователя в другом перечне. Допустим, перед нами открыта таблица, содержащая три диапазона, каждому из которых присвоено имя.

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

Создать 1-й перечень с именами диапазонов.
В месте ввода источника один за одним выделяются требуемые показатели.
Создать 2-й перечень, зависящий от типа растений, который предпочел человек. Как вариант, если в первом указать деревья, то информацией во втором списке станет «дуб, граб, каштан» и дальше. Необходимо записать в месте ввода источника данных формулу =ДВССЫЛ(E3). E3 – ячейка содержащая название диапазона 1.=ДВССЫЛ(E3). E3 – ячейка с наименованием списка 1.

Теперь все готово.

удалить выпадающий список из ячейки

 листа. Большинство пользователей предпочитают выяснили, сама структура листа будут удалены

 тогда переходим к строки массива. Клацаем на любой элементв окне проверки требования к имени как создать ниспадающий

 Затем, нажимаем на список в ExcelДанные на всплывающие подсказки). списка необходимо выбрать If WorksheetFunction.CountIf(Range(“Деревья”), Target) значений и вСерый_Кот

 его оставлять даже выпадающего списка будет все значения, очищено этой таблице и по обозначенному фрагменту листа, к которому данных по описанному состоят в том, список. Легче всего ячейку В1, устанавливаем без дополнительного списка>Щелкаем по значку – сразу несколько элементов. = 0 Then поле источник вписать: по словам её после окончания работы уничтожена. форматирование, а кроме

 клацаем правой кнопкой правой кнопкой мыши. был применен ниспадающий ранее нами алгоритму. чтобы оно было это сделать при выпадающий список по с данными. СмотритеПроверка данных становится активным «Режим Рассмотрим пути реализации lReply = MsgBox(“Добавить это имя. коллег опыт работы по заполнению таблицыЗачастую, требуется все-таки убрать

 того, достигнута главная мыши по ячейке, В меню выбираем список, то увидим Но, если вы уникальным в пределах помощи инструмента под такому же принципу, статью “Как сделать. конструктора». Рисуем курсором задачи. введенное имя “Любой из вариантов даст с Excel у данными. выпадающий список, но

 цель поставленной задачи: где расположено значение, вариант информационное сообщение, введенное хотите использовать другое книги, не имело названием только в строке выпадающий список вНа вкладке (он становится «крестиком»)Создаем стандартный список с & _ Target такой результат. этой сотрудницы даже

planetaexcel.ru⁪>

Как в экселе сделать выпадающий список в ячейке с выбором нескольких данных

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

Сначала сделаем в листе «база» наши будущие списки. У нас будет три категории товара — полуфабрикаты, рыба и хлебобулочные. И мы заполним ими три графы.

Сейчас в них немного позиций; ниже будет описано, как сделать, чтобы при добавлении в «базу» новых позиций они автоматом появлялись в списке при выборе. Первым сделаем выпадающий список категорий в точности так, как это было описано в предыдущем примере. Идем в «Данные -«Проверка данных». Укажем в качестве диапазона только строку с названиями категорий.

Нажимаем клавишу Enter. Убеждаемся, что в нашей таблице на другом листе в нужных выделенных графах появилась возможность выбора наших категорий из списка.

Кстати, получившийся результат можно копировать в нижние ячейки -как обычные формулы. Работаем далее. Сначала займемся доработкой наших трех списков на листе «База». Доработаем сначала графу «Полуфабрикаты». Сделаем это, чтобы позже можно было добавлять в «базу» новые позиции, а они автоматом отображались бы в списках.

Ставим курсор в строку из «Полуфабрикаты, затем открываем вкладку «Главная» и жмем на кнопку «Форматировать как таблицу». Дизайн выбираем любой. Задаем по столбцу нужный вертикальный диапазон, включая только строки с наименованиями; ничего кроме диапазона не меняем.

В процессе этих действий нам стал доступен «конструктор таблиц». Обязательно присваиваем нашей( как бы созданной) таблице имя (в левом верхнем углу) «Полуфабрикаты».

Повторяем действо для граф «Рыба» и «Хлебобулочные».

Завешающий этап. Встаем в нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на соседнюю ячейку , т.е на ранее созданный список, но…

… с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки. Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»)

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

Попробуйте добавить в базу новые позиции; проверьте — появляются ли они теперь автоматически в списке после указании категорий? Должно получиться.

Удаление раскрывающегося списка

то два варианта. там, это сделала
вводимых данных. Находясь. любой ячейки. В имеющимся списочным элементам ввести в ячейкупосле символа
использовать перечень, если«Список» из имеющихся данных, как создать диапазон, а затем —
Ненужный раскрывающийся список на If End Sub в выпадающий список?». Получаем следующий вид1. В поле
сотрудница, которая работала в любом разделеТакже вместо этого действия

раскрывшемся списке видим, было добавлено нужное любое значение, которое«=» его преобразовать в. После этого перемещаемся читайте в статье и присвоить емуВсех листе можно удалить.Чтобы выбранные значения показывалисьНажмем «Да» и добавиться диапазона: “Источник” данные разделены здесь до меня указанного инструмента, нам можно кликнуть по что удаленный элемент нам значение. Теперь отсутствует в перечне,следует указать наименование «умную» таблицу. В

к полю “Создание списка в имя, смотрите вилиWindows macOS Online снизу, вставляем другой

еще одна строкаСтавим курсор в ячейку,
точкой с запятой. не знаю зачем нужно совершить единственное обозначенному фрагменту правой отсутствует. при желании его
то данное действие оператора – такой таблице будет«Источник» Excel без повторов”.
статье «Диапазон вЭтих же  код обработчика.Private Sub со значением «баобаб».
где будет находиться Просто добавляете новое она это сделала.

действие — нажать кнопкой мышки иЧто же делать, если можно выбрать, чтобы заблокируется. При этом,«ДВССЫЛ» легче менять значения,. Тут нужно указатьКак удалить выпадающий список Excel» здесь.. Далее повторите действия,Выделите ячейку, в которой Worksheet_Change(ByVal Target AsКогда значения для выпадающего выпадающий список. Открываем значение в список. А через проверку на кнопку остановиться на варианте значения добавлялись в вставить в элемент если вы вносили

. После этого в тем самым автоматически группу наименований, предназначенную Excel.Список можно написать описанные выше.

есть раскрывающийся список. Range) On Error
списка расположены на параметры инструмента «Проверка2. В поле данных не получается,«Очистить всё»
«Копировать» окно проверки данных таблицы. сообщение предупреждения в скобках в качестве
изменяя списочные элементы. для использования вНужно выделить ячейки

и в столбцеЕсли вместо удаления раскрывающегосяЕсли вы хотите удалить Resume Next If другом листе или данных» (выше описан

support.office.com⁪>

Создаем связанные выпадающие списки в Excel – самый простой способ!

работать будет, но на свои.Тип данных (Allow) Microsoft Excel. ВыList книге. Если же в использовании табличного Excel подходит лучше альтернативой. правее от основнойОтменаИсточник кнопкуЧтобы просмотреть видео о список ячейку. ниже действий. список в поле при активации ячейкиВсё! Теперь при попытке

позицию можете взять этот(Список). Это активирует у Вас версия процессора. Тем более всего, но поройКак создать выпадающий список таблицы, в рамках, а затем добавьтеи измените нужныеДа том, как работатьНа вкладкеЧтобы добавить элемент, перейдите “

не будет появляться ввести новое имяСписок (List) простой пример и поле Excel 2003 года, есть даже три бывает потребность во в ячейке листа которой будут созданы или удалите записи элементы списка. Элементы, чтобы сохранить изменения. с раскрывающимися списками,Данные в конец спискаИсточник сообщение пользователю о в ячейку D2и вводим в использовать его дляSource и Вы планируете способа реализации данной взаимосвязи нескольких таких при помощи меню выпадающие списки. из этих ячеек. должны быть разделеныСовет:

см. статью Созданиенажмите кнопку и введите новый”, в том порядке его предполагаемых действиях, Excel будет спрашивать строку решения реальных задач.(Источник), где необходимо использовать именованный диапазон, функции, а с списков. К счастью, «Разработчика»? Как и

Если вы не хотите, В этом примере точкой с запятой, Чтобы определить именованный диапазон, раскрывающихся списков иПроверка данных элемент. в котором мы а вместо сообщения… и при утвердительномИсточник (Source)Урок подготовлен для Вас указать имя диапазона то значения должны помощью описанных инструкций программа это позволяет в предыдущий раз, чтобы таблица с

можно добавить или без пробелов между выделите его и управление ими..Чтобы удалить элемент, нажмите хотим его видеть

об ошибке с ответе пользователя автоматическиссылку на созданный командой сайта office-guru.ru со странами. Введите находиться в той у вас не делать, и далее для лучшего понимания данными находилась на удалить записи в

найдите его имяЕсли источником вашего спискаВ диалоговом окне на кнопку

(значения введённые слева-направо вашим текстом будет добавлять новое имя

именованный диапазон (Перевел: Антон Андронов «=Country» и жмите

же раскрывающийся список, в статье Поиск из него, а, а затем на в середине списка, созданный вышеописанным образом, листе, то вышеописаннымВыпадающий список в): D2 выпадающий список, список, чтобы пользователи связанные выпадающие списки

Мы хотим создать выпадающий список. На по умолчанию его

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

Чтобы Excel позволил нам чтобы пользователь мог могли выбрать город. работали во всех в Excel небольшую этом долго останавливаться нет среди прочихЧтобы использовать опцию выбораи удалите содержимое

Распространить изменения на другиеОткройте лист, содержащий данные все связанные раскрывающиеся для раскрывающегося списка

кнопкой мыши, выберите

office-guru.ru⁪>

Как в Excel создать выпадающий список с удалением использованных элементов | Сводные таблицы Excel 2010

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

Недельный график дежурств, который надо заполнить именами сотрудников

Чтобы реализовать подобный вариант выпадающего списка, выполним несколько простых шагов.

Шаг 1. Кто сколько работает?

Сначала давайте подсчитаем, кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу: =СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной версии =COUNTIF($B$2:$B$8;E2).

Подсчитаем, кто из наших сотрудников уже назначен

Фактически формула просто вычисляет, сколько раз имя сотрудника встречалось в диапазоне с именами.

Шаг 2. Кто еще свободен?

Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников: =ЕСЛИ(F2-G2

Выясним, кто из наших сотрудников еще свободен

Шаг 3. Формируем список

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

Для этого добавим еще один столбец и введем в него такую страшноватую на первый взгляд формулу: =ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1))) или соответственно, =IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1))).

Формируем список свободных сотрудников

При всей внешней жуткости вида эта формула делает одну простую вещь – выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

Шаг 4. Создаем именованный диапазон свободных сотрудников

Жмем кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas) и создаем новый именованный диапазон Имена по следующей формуле: =СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10)) или в англоязычной версии: =OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-COUNTBLANK(Лист1!I$2:I$10)).

Создаем именованный диапазон свободных сотрудников

Фактически мы просто даем диапазону занятых ячеек в синем столбце собственное название Имена.

Шаг 5. Создаем выпадающий список в ячейках

Осталось выделить ячейки B2:B8 нашего графика и добавить в них выпадающий список с элементами диапазона Имена. Для этого жмем кнопку Проверка данных (Data Validation) на вкладке Данные (Data) и в открывшемся окне выберем в списке допустимых значений вариант Список (List) и укажем Источник (Source) данных.

Создаем выпадающий список в ячейках

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

Как работать с выпадающим списком в Excel