6 простых шагов сделать Выпадающий список в Excel

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

Где применяется в Excel

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

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

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

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

На данном сайте вы найдете и другие статьи по работе с программой Excel.

Преимущества выпадающих списков Excel

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

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

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

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

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

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

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

  

• В открывшемся окне находим строку, где написано «Любое значение». Раскрываем меню и ищем среди него пункт «Список».

  

• В строке «Источник» нам нужно ввести все значение, которые требуются, разделяя каждое из них «точкой с запятой», например: «Хлеб; Колбаса; Масло».

  

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

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

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

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

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

  

• Откроется окошко «Проверка данных», где в типах данных устанавливаем «Список».

  

• В строке «Источник» нужно ввести диапазон требуемых нам ячеек, где содержатся данные, на основе которых будет составлен список. К примеру, если вся требуемая информация расположилась в столбце A, а их диапазон от A1 до A7, то нужно ввести следующую функцию «=$A$1:$A$7».

  

• Или можно не писать вручную, а сделать следующий трюк. Когда начинаете писать в строке «Источник» — выбираем ячейку, где все начинается, потом поставить «двоеточие» и выбираем ячейку, на которой все заканчивается. А программа сама введет требуемые данные.

  

• Подтверждаем изменения, нажав на кнопку «ОК».

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

Как создать раскрывающийся список в эксель с помощью функции смещ

Используемая формула в раскрывающихся списках

Чтобы создать раскрывающийся список применяя функцию СМЕЩ требуется воспользоваться следующими шагами:

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

  

• Прописываем заголовок и ставим метку в ячейке, где нужно расположить список, который будет раскрываться. К примеру, в нашем случае вводим «Выбор значения» в ячейке B1.

  

• Далее нужно выделить тот квадратик, в котором планируется разместить сам список (допустим берем ячейку B2). Снова открываем раздел «Данные». И уже здесь находим иконку «Проверка данных». При нажатии на нее будет открыто окошко «Проверка данных».

  

• На первом разделе «Параметры» выбираем «Список».

  

• В строке «Источник» нужно ввести формулу, которая в итоге создаст наш список. В данном случае применим функцию СМЕЩ, чтобы определить диапазон нужных нам ячеек. К примеру, в нашем случае, если необходимый перечень расположен в столбце A, формулу стоит написать следующим образом:

=СМЕЩ($A$1;0;0;СЧЁТЗ($A:$A);1)»

Немного разъясним написанное. $A$1 -это мы указываем ссылку на первую ячейку, 0,0 – на какое количество клеточек нужно сместиться по вертикали и горизонтали (в этом примере, мы указали что смещения быть не должно), а СЧЕТ3($A:$A) — это функция, определяющая кол-во значений в столбце A.

• Для сохранения изменений и закрытия окна, нажимаем на кнопку «OK».

  

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

Как эта формула работает

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

=СМЕЩ(стартовая_ячейка; смещение_по_строкам; смещение_по_столбцам; [количество_строк]; [количество_столбцов])

Параметр «стартовая_ячейка» определяет начальную точку, относительно которой будет производиться смещение. Потом следуют параметры «смещение_по_строкам» и «смещение_по_столбцам», указывающие, на сколько строк или столбцов следует переместиться. Необязательные параметры «количество_строк» и «количество_столбцов» определяют количество ячеек, которые следует возвращать.

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

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

Чтобы создать выпадающий список с возможностью автоматически подставлять данные, воспользуемся последующими шагами:

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

  

• В открывшемся окошке «Проверка данных» переключаем тип на «Список».

  

• В строке «Источник» нужно ввести перечень информации, которая будет использована для списка. Допустим, если требуется создать список с продуктами, которые покупались в магазине, то просто прописываем их названия, разделяя «точкой с запятой» (например, «Майонез; Картофель; Конфеты; Помидоры»).

  

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

  

  

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

  

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

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

Если требуется скопировать список в Excel, то тогда нужно предпринять следующие простые шаги:

• Нажимаем на ячейку (или ячейки), на которых содержится нужный нам список, а потом просто копируем их (нажимаем на сочетание клавиш Ctrl+C либо выбрав опцию «Копировать», которая расположилась в появившемся меню).

  

• Переходим к месту, где требуется добавить скопированный выпадающий список, и нажимаем на соответствующие ячейки.
• Щелкаем правой клавишей мыши на только что выбранной ячейки и нажимаем на опцию «Вставить», которая также имеется в меню либо же применяем другую комбинацию — Ctrl+V.

  

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

Для организации списка, иногда данные нужно разложить отдельно по ячейкам, для удобства выбора. В нашей статье описаны различные способы, пользуясь которым можно разделить ячейку таблицы Excel на части (2 и более), в том числе, по диагонали.

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

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

• Открываем таблицу, где нам нужно выделить каждую ячейку, в которой содержится список и проделать с ними последующие действия.
• Выделяем абсолютно каждую ячейку в таблице. Чтобы это сделать, можно воспользоваться комбинацией клавиш Ctrl+A на клавиатуре либо использовать мышь для выделения нужной области.
• Переходим на раздел «Данные», которая располагается в верхней части Эксель.
• Находим и кликаем на кнопку «Проверка данных». Откроется знакомое нам окошко «Проверка данных».
• В уже открывшемся окне, переходим на вкладку «Параметры», если она еще не открыта.
• В ней нам нужно выбрать пункт «Список».
• Нажимаем на кнопку «ОК», для сохранения изменений.

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

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

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

Чтобы создать зависимые выпадающие списки в Excel достаточно просто воспользоваться несколькими простыми действиями. Рассмотрим их подробнее:

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

=ИмяЛиста!ДиапазонДанных

Где «ИмяЛиста» — это наименование листа в Excel, где расположены данные, а «ДиапазонДанных» — диапазон ячеек, в которых содержатся значения для списка. К примеру, если необходимые данные расположены на листе «Страны» в диапазоне A2:A10, то формулу нужно будет прописать так:

=Страны!$A$2:$A$10

• Сохраняем изменения. Определив источник данных каждому списку, остается только нажать на кнопку «ОК». После проделанных действий в выбранных ячейках появится выпадающий список со значениями, которые будут зависеть от указанного значения в прошлом списке.

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

Выбор нескольких значений из выпадающего списка excel

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

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

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

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

• Нажимаем на ячейку или диапазон ячеек, в которых нужно разместить выбранные значения из списка.
• Кликаем на ячейку, в которой содержится раскрывающийся перечень, чтобы активировать его. Щелкаем по стрелке, расположенной с правой стороны от ячейки, для открытия выпадающего списка.
• Удерживая клавишу «Ctrl» на клавиатуре, щелкаем по каждому варианту, который хочется выделить из перечня. Каждый раз, когда будет сделан щелчок по значению, оно будет сразу же, самостоятельно добавляться в выделенную ячейку.
• После того, как будут выбраны все необходимые варианты, отпускаем клавишу Ctrl.
• Для закрытия выпадающего списка, щелкаем в любом месте вне списка.

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

Выпадающий список с поиском

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

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

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

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

  

• Теперь мы выделяем в столбце отбор немаленькое количество строк, чтобы в них точно вместились все данные после поиска. И прописываем следующую функцию:

=ФИЛЬТР(A2:A115; ЕЧИСЛО(ПОИСК(D3;A2:A115)); «Ничего не найдено»)

Немного рассмотрим, значения функции. =ФИЛЬТР – разрешит нам выполнять поиск, среди большого массива данных. A2:A115 – этим выражением мы показываем программе в какой области нужно будет выполнять поиск. ЕЧИСЛО(ПОИСК(D3;A2:A115) – этим выражением мы показываем, что именно нужно искать. Например, ПОИСК(D3 – говорит о том, что в ячейке D3 мы будем писать те значения, которые нужно искать. A2:A115 – а эта запись опять же говорит программе о зоне поиска.

• Почти готово! Теперь самое время сделать ячейку, куда мы вводили запрос – выпадающим списком. Выделяем ячейку. Нажимаем на раздел «Данные» и кликаем по иконке «Проверка данных».

  

• В разделе «Параметры» указываем, что тип данных является «Список».

  

• В строке «Источник» нужно указать весь столбец, предназначенный для «Отбора».

  

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

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

Как сделать выпадающий список в excel с данными другого листа или файла

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

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

  

• Появится окошко «Свойства проверки данных». В первом пункте «Настройки» указываем, что тип данных является «Список».

  

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

  

  

• Для применения настроек и добавления выпадающего списка в выбранной ячейке, кликаем на кнопку «ОК».

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

Как убрать удалить выпадающий список в excel

Если есть необходимость в удалении выпадающего списка в программе Эксель, достаточно использовать приведённые ниже советы:

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

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

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

Используем умные таблицы, видеоролик выпадающих списков в Excel