В программе Excel от Microsoft есть одна полезная функция для удобства пользователей – возможность в ячейках вводить данные из оформленного списка. С ее использованием можно создать шаблон отчета, составить заявление для сотрудников. Они просто должны будут выбирать предложенные значения. Эта функция называется выпадающий список в Excel (Drop-down lists). Его иногда еще называют раскрывающимся. Сделать выпадающий список в Excel по 4 инструкциям.

Выпадающий список в Эксель представляет собой оформленный перечень в конкретной ячейке таблицы. В нем прописано одно или же сразу несколько значений. Подобная функция облегчает работу пользователю, так как ему больше нет необходимости вводить все вручную, а только выбрать нужное. Также человек может установить собственный фильтр-контроль, чтобы другие не могли вносить какие-либо изменения в уже предложенный перечень.
Если сделать Drop-down lists в Excel, то можно исключить «человеческий фактор», ведь людям свойственно делать ошибки. А уже готовый перечень данных убережет от опечаток при вводе. Плюсов у программы действительно много, и потому стоит разобраться, как же создать раскрывающийся перечень.

Создаем список на основе данных из перечня

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

Пошаговая и детальная инструкция оформления в Excel выпадающего списка:

1. Кликнуть ЛКМ по ячейке Excel – той, где сделать Drop-down lists требуется.
   
2. В «шапке» Excel кликаем на вкладку меню «Данные».
   
   
   
   
3. Здесь есть «Работа с данными». Что сделать – перейти в него и кликнуть «Проверка данных».
   
   
   
   
4. Если сделать по этому значку клик мышкой, появится дополнительное окно. В нем будет 3 дополнительные вкладки, но интерес представляет только одна – «Параметры». Наводим на «Тип данных» и при выборе жмем на «Список».
   
   
   
   
5. Снизу добавится поле – «Источник». Чтобы сделать раскрывающийся список, в этой строке ввести полностью весь диапазон с названиями канцелярских принадлежностей, что уже записаны. Это будет =$A$2:$A$7. Но есть и более просто вариант, где не нужно вводить весь диапазон вручную. Достаточно дважды кликнуть в поле «Источник», а затем выделить мышкой нужные строки в листе Excel. Чтобы изменения вступили в силу, прожать «Ок» внизу окна.
   
   
   
   
6. Теперь на первой строке со словом «Ножницы» будет закреплен выпадающий список. В свернутом виде он выглядит как небольшая стрелка, направленная вниз. Если на нее кликнуть, Drop-down lists раскроется.
   
   
   
   
   
   
   

Если требуется сделать Drop-down lists в Excel сразу в 3-5 ячейках и более, то и в этом не будет ничего сложного. Пользователю достаточно выбрать все строки, где должен быть раскрывающийся список, и повторить в точности инструкцию, описанную выше. Но есть один нюанс. Нужно внимательно следить за тем, чтобы ссылки были не относительными (A$2 или $A2), а исключительно абсолютными ($A$2). В противном случае функция будет работать некорректно.

Ввод данных вручную

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

1. Чтобы сделать раскрывающийся список, клик ЛКМ на ячейку, к которой нужно его прикрепить. Далее идем в «шапку», повторяем последовательность из инструкции выше.
   
   
   
   
2. Кликаем по этому значку. Появляется окно «Проверка вводимых значений». Теперь открыть первую вкладку «Параметры» (если она неактивна), спуститься ниже на условия проверки. Рядом с полем «Тип проверки» один раз кликнуть по значку в виде опущенной вниз стрелки, чтобы раскрыть перечень доступных вариантов. Указать «Список».
   
   
   
   
3. Если сделать все верно, ниже появится дополнительное поле «Источник». Для оформления выпадающего списка ввести вручную значения «Ножницы; Шариковые ручки». После нажать «Ок», чтобы внесенные элементы сохранились и вступили в силу.
   
   
   
   

В ячейке Excel будет открывать Drop-down lists с введенными в «Источник» значениями. Но важно вводить слова через точку с запятой, чтобы программа могла их обработать и добавить в перечень. Если этого не сделать, функция работать не будет.
Если есть необходимость сделать Drop-down lists сразу для нескольких ячеек, следует все их выделить мышкой, а после повторить все шаги из инструкции.

СМЕЩ

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

Вариант ее написания: =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]) — русская версия.

Чтобы разобраться в том, как это работает, сначала стоит определить, что значат аргументы этой функции Excel:

1. Reference (ссылка) – это та самая ссылка, от которой нужно будет сделать смещение.
   
2. Rows (смещ_по_строкам) – здесь указывают необходимое количество строк для смещения от изначальной точки. Если число будет положительным, строки станут смещаться ниже, если отрицательное – то выше.
   
3. Cols (смещ_по_столбцам) – то же самое, что и выше. Только там смещали строки, а здесь столбцы. Если указанное пользователем число будет положительным, столбики сместятся вправо, если отрицательное – влево.
   
4. [height] ([высота]) – заданное количество строк в выбранном пользователем диапазоне.
   
5. [width] ([ширина]) – заданное количество столбцов в выбранном пользователем диапазоне.
   

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

Как создать Drop-down lists с помощью СМЕЩ в Excel:

1. Составить перечень нужных элементов. Здесь это набор канцелярских принадлежностей.
   
   
   
   
2. Применяем формулу СМЕЩ. Что сделать – выбрать ячейку в таблице, в которой нужно создать Drop-down lists.
   
3. На панели инструментов в верхней части окна Excel кликнуть по вкладкам и значкам в такой последовательности: «Данные» => «Работа с данными» => «Проверка данных».
   
   
   
   
4. Появится окно «Проверка вводимых значений». В нем интересна только первая вкладка «Параметры». Опускаемся ниже к «Условиям проверки». В «Тип данных» развернуть перечень стрелкой справа и указать «Список».
   
   
   
   
5. Ниже появится поле для ввода «Источник». И теперь, чтобы сделать выпадающий список, здесь вручную вводится формула СМЕЩ: =СМЕЩ(A$2$;0;0;5). Чтобы сохранить изменения и активировать функцию, внизу окошка нажать на «Ок».
   
   
   
   

Если пользователь сделала что-то неверно или же не так написал формулу СМЕЩ, он получит такое сообщение от системы.

Но, если все было сделано верно, в выбранной ячейке в Excel появится выпадающий список с нужным перечнем элементов.

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

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

Как создать Drop-down lists с подстановкой (вручную) в Excel:

1. В таблице Excel выбрать диапазон, к которому будет прикреплен выпадающий перечень.
   
2. Перейти в верхнюю часть программы и мышкой последовательно прожать «Данные» => «Работа с данными» => «Проверка данных».
   
3. Появится окно «Проверка вводимых значений». Чтобы получить доступ к полю «Источник», в типе данных из предложенных вариантов указать «Список».
   
4. В пустом поле «Источник» написать формулу СМЕЩ: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;»<>»)). Подробное описание ее аргументов приведено выше.
   
5. Чтобы закрепить и активировать формулу, нажать внизу «Ок».
   

В формуле есть аргумент «высота». Здесь в качестве аргумента, обозначающего высоту списка с перечнем элементов, указать новую формулу – СЧЕТЕСЛИ. С ее помощью рассчитывается количество заполненных ячеек в диапазоне строк A2:A100.

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

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

Как это сделать в Excel:

1. В таблице Excel пишем нужный перечень. В этом случае это канцелярские принадлежности. Полностью выделяем его, зажав левую клавишу мышки.
   
   
   
   
2. Переходит на панель инструментов – «Форматировать как таблицу».
   
   
   
   
3. Появится окно, в котором нужно указать тип оформления таблицы.
   
   
   
   
4. Выбираете подходящий стиль. Появился еще 1 дополнительное окошко, в котором все, что нужно сделать, это подтвердить форматирование в указанном диапазоне и нажать клавишу «Ок». Еще поставь галочку напротив «Таблица с заголовками».
   
   
   
   
5. Вновь выделить все ячейки с перечнем элементов, которые должны быть в выпадающем списке. Слева вверху, прямо над первым столбцом будет дополнительное поле. В нем ввести имя списка.
   
   
   
   
6. Оформление таблицы в Excel закончено. Можно переходить к тому, как сделать выпадающий список. Все действия, описанные в инструкциях выше, повторяются. Следует выбрать нужную ячейку в таблице. На панели инструментов найти пункт «Данные». Прожать последовательность: «Данные» => «Работа с данными» => «Проверка данных».
   
7. Откроется окно «Проверка вводимых значений». Здесь же выбираем «Список».
   
8. Внизу появится поле «Источник». И теперь, чтобы сделать выпадающий список в Excel, потребуется ввести название не диапазона, как ранее, а таблицы, которая была создана в первых пунктах инструкции. Выглядеть это будет так.
   
   
   
   
9. Выпадающий список в Excel готов. Для того, чтобы в него добавлять новые значения, следует перейти в составленную таблицу еще данные, создавая новую ячейку. Таблица создана таким образом, что она будет сама расширять свой диапазон данных.
   

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

Excel – достаточно «умная» программа. И в ней есть масса полезных функций, призванных упростить жизнь пользователю. Например, созданный единожды выпадающий список можно просто скопировать, чтобы вновь не заниматься созданием идентичного.
Как это сделать в Excel:

1. Для примера – в ячейке А1 уже есть готовый выпадающий список. Но его нужно скопировать в ячейки А2:А6.
   
   
   
   
2. Чтобы при копировании сохранить исходное форматирование, нужно один раз кликнуть мышью на ту ячейку, которую требуется продублировать.
   
3. Зажать стандартную комбинацию клавиш на клавиатуре – CTRL+C.
   
4. Все ячейки из диапазона А2:А6 выделить, зажав левую клавишу мыши, так как во все эти строки нужно будет добавить выпадающий список.
   
5. Зажать на клавиатуре комбинацию двух клавиш – CTRL+V. Drop-down lists с сохранением исходного форматирования скопирован и вставлен.
   
6. Если же нет необходимости в сохранении исходного форматирования, то сделать можно следующее – выделить ячейку с уже сформированным выпадающим списком.
   
7. Зажать стандартную комбинацию клавиш на клавиатуре – CTRL+C.
   
8. Выбрать в таблице ячейку, для которой нужен раскрывающийся список. Кликнуть по ней 1 раз правой клавишей мышки и вызвать дополнительное меню инструментов и действий. Найти и выбрать пункт «Специальная вставка».
   
   
   
   
9. Появится новое окно, в котором нужно будет поставить отметку на «Условия назначения» в разделе «Вставить».
   
   
   
   
10. Будет сформирован выпадающий список, который не сохраняет исходное форматирование скопированного.
    

Как выделить все ячейки, содержащие выпадающий список в Экселе

В таблице Excel абсолютно все ячейки, которые включают в себя Drop-down lists, не отображаются. Что очень неудобно для пользователя. Но есть алгоритм, как сделать их все видимыми и упростить работу:

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

Как сделать зависимые выпадающие списки в Excel

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

1. Пользователь должен создать 2 именных диапазона, например, для ячеек A2:A5 и B2:B5. Чтобы это сделать, выделяют все заполненные ячейки в этих 2 столбцах, и кликают последовательно на «Формулы» => «Определенные имена» => «Создать из выделенного».
   
2. Появится новое окно с названием «Создание имен из выделенного диапазона». В перечне ниже поставить отметку на «в строке выше».
   
3. Действие подтвердить, нажав на «Ок».
   
4. В любой выбранной ячейке, например, D2, создать новый выпадающий список, но уже с заданными именами выделенных ранее 2 столбцов. Пользователь может выбрать любое из них.
   
5. Чтобы сделать зависимый выпадающий список – выделить Е2. Кликнуть поочередно «Данные» => «Работа с данными» => «Проверка данных».
   
6. В новом окне выбрать в типе данных «Список». В появившейся строке «Источник» указать ссылку =INDIRECT(D2) или =ДВССЫЛ(D2). Свое действие подтвердить нажатием на кнопку «Ок».
   

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

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