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

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

Я знаю, что делать, но не знаю
куда позже девать тела…

Постановка задачи

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

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

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

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

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

=СЧЁТЕСЛИ($B$2:$B$8;E2) либо в англоязычной версии =COUNTIF($B$2:$B$8;E2)

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

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

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

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

=ЕСЛИ(F2-G2<=0;””;СТРОКА(E2)-СТРОКА($E$2)+1) или в английской версии =IF(F2-G2<=0;””;ROW(E2)-ROW($E$2)+1)

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

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

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

=ЕСЛИ(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)))

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

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

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

  • в Excel 2003 и старше идем в меню Вставка – Имя – Присвоить (Insert – Name – Define)
  • в Excel 2007 и новее – нажимаем клавишу Диспетчер Имен (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))

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

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

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

Осталось выделить ячейки B2:B8 нашего графика и добавить в их выпадающий перечень с элементами спектра Имена. Для этого

  • в Excel 2003 и старше – откроем меню Данные – Проверка (Data – Validation),
  • в Excel 2007 и новее – нажимаем клавишу Проверка данных (Data Validation) на вкладке Данные (Data)

В открывшемся окне выберем в перечне допустимых значений вариант Список (List) и укажем Источник (Source) данных:

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

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

Ссылки по теме

Как удалить выпадающий перечень в Excel

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

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

Содержание

Использование выпадающих списков

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

Процедура создания

До этого всего, давайте выясним, как сделать ниспадающий перечень. Легче всего это сделать при помощи инструмента под заглавием «Проверка данных».

  1. Выделяем столбец табличного массива, в ячейках которого планируется расположить выпадающий перечень. Передвигаемся во вкладку «Данные» и клацаем по кнопочке «Проверка данных». Она локализирована на ленте в блоке «Работа с данными».
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  2. Запускается окно инструмента «Проверка значений». Перебегаем в раздел «Параметры». В области «Тип данных» из списка избираем вариант «Список». Опосля этого перемещаемся к полю «Источник». Здесь необходимо указать группу наименований, предназначенную для использования в перечне. Эти наименования можно внести вручную, а можно указать на их ссылку, ежели они уже расположены в документе Excel в другом месте.

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

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

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

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

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

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

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

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

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

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

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

    Опосля этого раскрывается маленькое окошко, в котором содержится адресок выделенного массива. Ежели выделение было выполнено правильно, то здесь ничего изменять не необходимо. Так как у нашего спектра нет заголовков, то у пт «Таблица с заголовками» галочки быть не обязано. Хотя непосредственно в вашем случае, может быть, заголовок и будет применяться. Так что нам остается просто надавить на клавишу «OK».

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

    Опосля этого спектр будет отформатирован, как таблица. Ежели его выделить, то можно увидеть в области имён, что наименование ему было присвоено автоматом. Это наименование можно употреблять для вставки в область «Источник» в окне проверки данных по описанному ранее нами методу. Но, ежели вы желаете употреблять другое заглавие, то его можно заменить, просто вписав в область имен.

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

    Ежели список расположен в иной книжке, то для корректного его отражения требуется применить функцию ДВССЫЛ. Указанный оператор предназначен для того, чтоб сформировывать «суперабсолютные» ссылки на элементы листа в текстовом виде. Фактически при этом процедура будет выполняться фактически точно таковая же, как и в ранее описываемых вариантах, лишь в области «Источник» опосля знака «=» следует указать наименование оператора – «ДВССЫЛ». Опосля этого в скобках в качестве аргумента данной функции должен быть указан адресок спектра, включая наименование книжки и листа. Фактически, как показано на изображении ниже.

    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  3. На этом мы бы могли и окончить функцию, нажав на клавишу «OK» в окошке проверки данных, но при желании можно усовершенствовать форму. Перебегаем в раздел «Сообщения для ввода» окошка проверки данных. Здесь в области «Сообщение» можно написать текст, который будут созидать пользователи, наводя курсор на элемент листа с ниспадающим перечнем. Записываем то сообщение, которое считаем необходимым.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  4. Далее перемещаемся в раздел «Сообщение о ошибке». Здесь в области «Сообщение» можно ввести текст, который будет следить юзер при попытке ввести неправильные данные, то есть, любые данные, отсутствующие в ниспадающем перечне. В области «Вид» можно выбрать значок, которым будет сопровождаться предупреждение. Вводим текст сообщения и клацаем по «OK».
Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

Урок: Как сделать выпадающий перечень в Экселе

Читайте также  Нет доступных подключений как исправить. Нет доступных подключений на ноутбуке с Windows: как исправить?

Выполнение операций

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

  1. Если мы установим курсор на хоть какой элемент листа, к которому был использован ниспадающий перечень, то увидим информационное сообщение, введенное нами ранее в окне проверки данных. Не считая того, справа от ячейки покажется пиктограмма в виде треугольника. Конкретно она служит для доступа к выбору списочных частей. Клацаем по данному треугольнику.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  2. После клика по нему меню из списочных объектов будет открыто. В нем размещены все элементы, которые были ранее внесены через окно проверки данных. Избираем тот вариант, который считаем необходимым.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  3. Выбранный вариант отобразится в ячейке.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  4. Если же мы попытаемся ввести в ячейку хоть какое значение, которое отсутствует в списке, то данное действие заблокируется. При этом, ежели вы вносили сообщение предупреждения в окно проверки данных, то оно отобразиться на экране. Необходимо в окне предупреждения надавить на клавишу «Отмена» и со последующей пробы ввести корректные данные.
Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

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

Добавление новейшего элемента

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

  1. Если данные для формирования перечня подтягиваются из табличного массива, то перебегаем к нему. Выделяем ячейку спектра. Ежели это не «умная» таблица, а обычный спектр данных, то необходимо вставить строчку в середину массива. Ежели же применяете «умную» таблицу, то в этом случае довольно просто вписать требуемое значение в первой строке под ней и данная строчка здесь же будет включена в табличный массив. Это как раз то преимущество «умной» таблицы, о котором мы упоминали выше.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

    Но представим, что мы имеем дело с наиболее сложным случаем, используя обыденный спектр. Итак, выделяем ячейку в середине указанного массива. То есть, над данной для нас ячейкой и под ней должны быть ещё строчки массива. Клацаем по обозначенному фрагменту правой клавишей мыши. В меню избираем вариант «Вставить…».

    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  2. Запускается окошко, где следует произвести выбор объекта вставки. Избираем вариант «Строку» и нажимаем на клавишу «OK».
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  3. Итак, пустая строчка добавлена.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  4. Вписываем в неё то значение, которое хотим, чтоб отобразилось в ниспадающем перечне.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  5. После этого возвращаемся к тому табличному массиву, в котором расположен раскрывающийся список. Кликнув по треугольнику, справа от хоть какой ячейки массива, мы лицезреем, что к уже имеющимся списочным элементам было добавлено необходимое нам значение. Сейчас при желании его можно выбрать, чтоб вставить в элемент таблицы.
Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

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

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

Удаление элемента

Удаление списочного элемента проводится по точно такому же методу, что и добавление.

  1. Если данные подтягиваются из табличного массива, то тогда перебегаем к данной таблице и клацаем правой клавишей мыши по ячейке, где размещено значение, которое следует удалить. В контекстном меню останавливаем выбор на варианте «Удалить…».
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  2. Открывается окно удаления ячеек фактически аналогичное тому, которое мы лицезрели при их добавлении. Здесь опять устанавливаем переключатель в позицию «Строку» и клацаем по «OK».
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  3. Строка из табличного массива, как лицезреем, удалена.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  4. Теперь возвращаемся к той таблице, где находятся ячейки с выпадающим списком. Клацаем по треугольнику справа от хоть какой ячейки. В раскрывшемся перечне лицезреем, что удаленный элемент отсутствует.
Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

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

  1. Выделяем табличный спектр с раскрывающимся списком и перебегаем в окошко проверки значений, как это мы уже делали ранее. В указанном окне перемещаемся в раздел «Параметры». В области «Источник» выделяем курсором то значение, которое требуется удалить. Потом нажимаем на клавишу Delete на клавиатуре.
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  2. После того, как элемент удален, щелкаем по «OK». Сейчас его не будет и в выпадающем списке, точно так же, как это мы лицезрели и в прошлом варианте действий с таблицей.
Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

Полное удаление

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

  1. Выделяем весь массив, где размещен раскрывающийся список. Передвигаемся во вкладку «Главная». Щелкаем по иконке «Очистить», которая располагается на ленте в блоке «Редактирование». В открывшемся меню избираем позицию «Очистить все».
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов
  2. При выборе данного деяния в выделенных элементах листа будут удалены все значения, очищено форматирование, а не считая того, достигнута основная цель поставленной задачи: раскрывающийся список будет удален и сейчас в ячейки можно вводить любые значения вручную.
Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

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

  1. Выделяем спектр пустых ячеек, который равнозначен спектру частей массива с раскрывающимся списком. Передвигаемся во вкладку «Главная» и там кликаем по иконке «Копировать», которая локализируется на ленте в области «Буфер обмена».
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

    Также заместо этого деяния можно кликнуть по обозначенному фрагменту правой клавишей мышки и тормознуть на варианте «Копировать».

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

    Ещё проще сходу опосля выделения применить набор клавиш Ctrl+C.

  2. После этого выделяем тот фрагмент табличного массива, где размещаются выпадающие элементы. Нажимаем на клавишу «Вставить», локализированную на ленте во вкладке «Главная» в разделе «Буфер обмена».
    Как убрать раскрывающийся список в excel. Выпадающий список с удалением использованных элементов

    2-ой вариант действий – это щелкнуть по выделению правой клавишей мышки и приостановить выбор на варианте «Вставить» в группе «Параметры вставки».

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

    В конце концов, есть возможность просто обозначить нужные ячейки и набрать сочетание клавиш Ctrl+V.

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

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

Читайте также  Установка приложения Zoom в среде Windows, Android и iOS

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

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

Оставьте комментарий