Выпадающий список в excel 2007 не работает excelka.ru

Поиск ячеек

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

Для поиска ячеек с формулами выполняются следующие действия.

В открытом документе выделить ячейку или диапазон ячеек (в первом случае поиск идет по всему листу, во втором – в выделенных ячейках).
Во вкладке «Главная» выбрать функцию «Найти и выделить».
Обозначить команду «Перейти».
Выделить клавишу «Выделить».
Выбрать «Формулы».
Обратить внимание на список пунктов под «Формулами» (возможно, понадобится снятие флажков с некоторых параметров).
Нажать клавишу «Ок».

Для поиска объединенных ячеек потребуется выполнение следующих манипуляций.

Перейти во вкладку «Главная».
Выбрать функцию «Найти и выделить».
Нажать на команду «Найти».
Перейти в «Параметры» и выбрать «Формат».
Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
Нажать на «Ок».
Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

При нажимании кнопкой мыши на элемент в списке происходит выделение объединенной ячейки на листе. Дополнительно доступна функция «Отменить объединение ячеек».

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

Выбрать лист, требующий анализа на присутствие скрытых ячеек и их нахождения.
Нажать клавиши «F5_гт_ Special».
Нажать сочетание клавиш «CTRL + G_гт_ Special».

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

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

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

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

Чтобы найти ячейки, для которых применено условное форматирование:

нажать на ячейку, не предусматривающую условное форматирование;
выбрать функцию «Редактирование» во вкладке «Главная»;
нажать на кнопку «Найти и выделить»;
выделить категорию «Условное форматирование».

Чтобы найти ячейки, для которых применено одинаковое условное форматирование:

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

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

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

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

PrivateSub Worksheet_Change(ByVal Target As Range)     OnErrorResumeNextIfNot Intersect(Target, Range("Е2:Е9")) IsNothingAnd Target.Cells.Count = 1 Then         Application.EnableEvents = FalseIf Len(Target.Offset(0, 1)) = 0 Then             Target.Offset(0, 1) = Target         Else             Target.End(xlToRight).Offset(0, 1) = Target         EndIf         Target.ClearContents         Application.EnableEvents = TrueEndIfEndSub

Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.</li>

PrivateSub Worksheet_Change(ByVal Target As Range)     OnErrorResumeNextIfNot Intersect(Target, Range("Н2:К2")) IsNothingAnd Target.Cells.Count = 1 Then         Application.EnableEvents = FalseIf Len(Target.Offset(1, 0)) = 0 Then             Target.Offset(1, 0) = Target         Else             Target.End(xlDown).Offset(1, 0) = Target         EndIf         Target.ClearContents         Application.EnableEvents = TrueEndIfEndSub

Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль. </li>

</ol>

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

Как сделать выпадающий список в экселе 2003?

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

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

Зачем нужен выпадающий список?

заполнять таблицы любой сложности;
составлять их самостоятельно.

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

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

Первый способ создания выпадающего списка

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

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

В графе «Игнорировать пустые ячейки» нужно поставить галочку.

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

Нюансы создания списков

Выпадающий список с помощью проверки данных.

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

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

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

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

Сделать это просто. Следует выделить диапазон со списками, которые больше не потребуется изменять, открыть вкладку «Данные» и выбрать пункт «Очистить все». При этом все ранее внесенные значения будут сохранены и никуда не пропадут.

Второй способ создания списка

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

Сделайте обводку этой надписи контуром с помощью мыши. Применив правую кнопку нажмите на «Формат объекта» в образовавшемся окне. Там же в первом поле выбирается и диапазон ячейки, а во втором – указывается ячейка, где будет указываться номер элемента.

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

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

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

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

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

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

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

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

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

На основе данных из перечня

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

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

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

Найти пункт «Тип данных» и переключить значение на «Список». 7

В поле, обозначающем опцию «Источник», ввести нужный диапазон

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

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

С ручной записью данных

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

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

Нажать по ячейке, отведенной под перечень.
Открыть «Данные» и там отыскать знакомый нам раздел «Проверка данных».
9
Снова выбираем тип «Список».
10
Здесь в качестве источника необходимо ввести “Да;Нет”. Видим, что информация при ручном вводе вводится с использованием точки с запятой для перечисления.

После нажатия «ОК» у нас появился следующий результат.

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

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

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

Первый называем «Имя»
, второй – «Фамилия»
, третий – «Отч»
.

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники»
.

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

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

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

Выделяем ячейку и кликаем по кнопочке «Проверка данных»
. В поле «Тип данных»
выбираем «Список»
, в поле источник прописываем следующее: =ДВССЫЛ($Е$1)
. Здесь Е1
– это ячейка с первым выпадающим списком.

По такому принципу можно делать зависимые выпадающие списки.

Если в дальнейшем, нужно будет вписать значения в диапазон, которому задано имя, например, «Фамилия»
. Перейдите на вкладку «Формулы»
и кликните «Диспетчер имен»
. Теперь в имени диапазона выбираем «Фамилия»
, и внизу, вместо последней ячейки С3
, напишите С10
. Нажмите галочку. После этого диапазон увеличится, и в него можно будет дописывать данные, которые автоматически будут появляться в выпадающем списке.

Теперь Вы знаете, как сделать раскрывающийся список в Excel.

Добрый день, уважаемый читатель!

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

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

Ну что же, рассмотрим создание выпадающих списков и для чего же это нужно:

Я лично постоянно использую выпадающий список по всем 3 причинам. И она значительно упрощает мне работу с данными, я сознательно сокращаю к 0% возможность при введении первичных данных.

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

А делать список в ячейке будем в несколько этапов:

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

2. Выделяем нужный нам диапазон и в меню выбираем пункт “Данные” — “Проверка данных”, в появившемся контекстном окне выбираем из указанного выбора пункт “Список”.

3. В разблокированной ниже строке указываем диапазон данных, которые должны быть у нас в выпадающем списке. Нажимаем “Ок” и работа сделана.

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

Рад вам помочь, пользуйтесь себе во благо!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На первом листе нажмите на букву под которой вы хотите чтобы во всей колонке был выбор из раскрывающегося списка Excel. Потом перейдите на вкладку ДАННЫЕ, а в ней нажмите на пункт Проверка данных. В результате откроется окно в котором выберите тип данных Список. Затем под пунктом Источник тыкнете в пустое поле.

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

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

Зависимые раскрывающиеся списки

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

Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать». Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»
Нажимаем «Создать»
Введите имя и диапазон, затем нажмите «ОК». Если вы перед этим выделяли нужные ячейки мышкой, то диапазон будет указан автоматически. Также примите к сведению, что имя диапазона должно быть уникальным и не должно содержать знаков препинания с пробелами.
Пишем имя «Деревья», нажимаем «ОК»
По такой же методике сделайте столько именованных диапазонов, сколько логических зависимостей хотите создать. В данном примере это ещё два диапазона: «Кустарники» и «Травы».
Создаем таким же способом остальные диапазоны
Откройте вкладку «Данные» (в первом способе указан путь к ней) и укажите в источнике названия именованных диапазонов, как это показано на скриншоте. В поле «Источник» указываем ячейки с названием диапазонов, нажимаем «ОК»
Выпадающий список с названием диапазона ячеек
Теперь вам нужно создать дополнительный раскрывающийся список по той же схеме. В этом списке будут отражаться те слова, которые соответствуют заголовку. Например, если вы выбрали «Дерево», то это будут «береза», «липа», «клен» и так далее. Чтобы осуществить это, повторите вышеуказанные шаги, но в поле ввода «Источник» введите функцию «=ДВССЫЛ(E1)». В данном случае «E1» – это адрес ячейки с именем первого диапазона. По такому же способу вы сможете создавать столько взаимосвязанных списков, сколько вам потребуется. В поле ввода «Источник» вводим функцию «=ДВССЫЛ(E1)», нажимаем «ОК»
Результат выпадающего связанного списка

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

Использование инструментов от разработчика

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

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

1. Активировать опции, поскольку они автоматически отключены. Для этого нужно перейти в меню «Файл».

2. В списке слева найти меню «Параметры» и кликнуть по нему.

3. Открыть раздел с настройками ленты и перейти к «основным вкладкам», поставить галочку напротив разработчика. Остается кликнуть на ОК, чтобы сохранить опции.

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

5. Перейти в «Разработчик». В подпункте элементов управления кликнуть на кнопку вставки. В появившемся окне выбрать опцию «элементы ActiveX» и кликнуть на значок «Поле со списком».

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

7. В параметрах найти ListFillRange. В столбике поставить рядом «:» и описать координаты ячеек, чтобы создать определенный диапазон. Закрыть окно.

8. Правой кнопкой мышки кликнуть на список в окне, выбрать «объект ComboBox» и кликнуть на edit.

9. Должен появиться список с заранее заданным параметрами.

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

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

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

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

• для начала создается один, общий список для всех названий. Выделяются ячейки с наименованиями посредством контекстного меню;

• задать определенное имя;

• по аналогии формируются отдельные списки для каждого наименования с определением количества единиц;

• далее потребуется вставить общий список с наименованиями в верхней ячейке в первом столбике главной страницы;

• указать в качестве основного источника ранее заданную таблицу;

• кликнуть по верхней части столбика, где указаны единицы измерения, зайти в проверочное окно и в источнике указать «=ДВССЫЛ(A2)», вместо А2 может быть любая ячейка с наименованием продукта;

• список готов. Теперь можно растянуть по желанию строки и таблицы.

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

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

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

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

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

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

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

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

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

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

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

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

1 Применение комбинации клавиш

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

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

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

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

2 Использование меню

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

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

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

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

Автоматическое заполнение, скорее всего, будет некорректным. Но при выборе значений вручную они будут выбираться из нужного диапазона — это становится возможным благодаря знакам доллара перед каждой частью адреса источника ($I$6: $I$8).

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

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

3 Создание нового элемента управления

Еще один способ, как в ячейке Эксель сделать выпадающий список, требует от пользователя создания нового элемента управления. Алгоритм создания включает такие действия:

Включить режим разработчика. Для это следует кликнуть правой кнопкой мыши по ленте Excel, открыть настройки и поставить галочку напротив соответствующего пункта.
Перейти к появившейся новой вкладке. В меню «Вставить» выбрать поле со списком.
Кликнуть по любому месту таблицы. После того, как там появилось нужное поле, открыть его контекстное меню и выбрать «Формат объекта». Здесь следует указать связь с ячейкой, в которой будет находиться номер значения в списке (48/96 — второй, поэтому в ячейке G2 находится «2»). А еще — диапазон, откуда берутся данные.

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