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

Обновлено: 20.11.2024

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

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

Ответ: да, можно! Вот как.

Как

Сначала вы открываете книгу, в которой находятся исходные ячейки для раскрывающегося списка. Затем вы определяете имя для этого диапазона ячеек. Вы должны сделать это, потому что после того, как вы откроете окно проверки данных, вы не сможете ссылаться на другую книгу в качестве источника для раскрывающегося списка. Но это можно преодолеть, создав несколько именованных диапазонов в исходной и целевой книгах. Я бы рекомендовал, чтобы и имя диапазона, и имя исходной книги не содержали пробелов и были простыми. В примере это будут Source.xlsx и Months.

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

Редактировать «целевую» книгу

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

Поэтому вы переходите в раздел Формулы/Диспетчер имен/Создать и в поле «Относится к» пишете «NameOfTheOpennedWorkbookWhereTheNamedListIs.xlsx»!NameOfTheRange в нашем образце, то есть Source.xlsx!Months. И данное имя — MonthsDestination.
Не забывайте, что при этом должна быть открыта исходная книга!

Это даст вам именованный диапазон в целевой книге, который равен диапазону в источнике, но с одним существенным отличием. На этот диапазон можно ссылаться как на источник раскрывающегося списка, поскольку он находится в рабочей книге «Назначение». Итак, теперь вы можете выбрать ячейку, в которой вы хотите разместить раскрывающийся список, и перейти к DATA / Data Validation.

В окне «Проверка данных» выберите «Список» в разделе «Разрешить» и напишите «= имя», которое вы определили в рабочей книге назначения. В нашем примере это «=MonthsDestination».

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


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

В этом посте я расскажу о целом ряде советов по работе с раскрывающимися списками в Excel, в том числе:

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

Как создавать/изменять раскрывающиеся списки

Чтобы создать/изменить раскрывающийся список:

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

В диалоговом окне "Проверка данных" нажмите кнопку выбора критериев проверки и выберите "Список":

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

  • Введите источник списка:
    1. Источник можно ввести вручную, разделяя параметры запятыми:

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

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

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

Чтобы создать список из диапазона на другом листе, в диалоговом окне "Проверка данных" нажмите кнопку выбора диапазона и перейдите к нужному диапазону на другом листе.

Как удалить раскрывающиеся списки

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

Как копировать списки из одной ячейки в другую

  • Выберите ячейку, содержащую раскрывающийся список, который вы хотите скопировать.
  • Скопируйте ячейку, нажав Ctrl + C или щелкните правой кнопкой мыши –> Копировать
  • Выберите ячейки, в которые вы хотите вставить раскрывающийся список.
  • Щелкните правой кнопкой мыши, выберите "Специальная вставка", нажмите "Проверка" и нажмите "ОК".

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

Как работать с книгой с несколькими разными списками

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

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

Затем вы можете использовать имена списков в диалоговом окне "Проверка данных"

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

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

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

Подсказка будет выглядеть следующим образом

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

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

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

Создать расширяемый раскрывающийся список

Если вы работаете со списками, которые необходимо постоянно обновлять, и каждый раз, когда вы добавляете новое значение в список, вам нужно открыть диалоговое окно "Проверка данных" и выбрать новый диапазон, существует обходной путь, который облегчит вашу жизнь.< /p>

Уловка очень проста: просто преобразуйте источник списка в таблицу, и все готово. Вот шаги:

1) Выберите диапазон, содержащий источник для списка

2) Преобразуйте исходный диапазон в таблицу: перейдите на вкладку «Вставка», нажмите «Таблица», а затем нажмите «ОК».

Более быстрый способ создания таблиц — выбрать нужный диапазон и нажать Ctrl + T, а затем нажать Enter.

3) Создайте раскрывающийся список и используйте в качестве источника только что созданную таблицу.

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

Создать зависимые раскрывающиеся списки

Еще одна вещь, которую вы можете сделать, это ограничить выбор в раскрывающемся списке на основе выбора другого списка. Например, если вы выберете «напитки» в столбце A, то в столбце B появятся только напитки; но если вы выберете «Молочные продукты» в столбце A, в столбце B появятся только молочные продукты. См. пример ниже:

1) Первым шагом является создание именованного диапазона для каждого параметра основного списка.

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

Обратите внимание, что каждый список ДОЛЖЕН называться точно так же, как соответствующая опция в основном списке.

2) Затем создайте раскрывающийся список для основного списка

3) Затем создайте раскрывающийся список для подсписка с помощью функции ДВССЫЛ

В следующем примере основной список находится в ячейке A5, поэтому вам нужно перейти в ячейку B5 и создать раскрывающийся список, используя =ДВССЫЛ(A5) в качестве источника. См. пример ниже:

Пожалуйста, поделитесь

Если вам это понравилось или вы знаете кого-то, кто мог бы его использовать, нажмите кнопки вверху, чтобы поделиться им с друзьями на Facebook, Twitter и LinkedIn.

Список Excel может извлекать данные из другого листа

Бывший писатель Lifewire Тед Френч является сертифицированным специалистом Microsoft, который преподает и пишет о электронных таблицах и программах для работы с электронными таблицами.

Крис Селф (Chris Selph) — сертифицированный CompTIA преподаватель технологии и профессиональный преподаватель информационных технологий. Он также работает администратором сети и сервера и выполняет обслуживание и ремонт компьютеров для многочисленных клиентов.

Что нужно знать

  • Лист 1 содержит раскрывающийся список, а "Лист2 – источник данных".
  • На листе 2: выберите данные, которые хотите использовать, и введите для них имя в поле "Имя".
  • На листе 1: выберите ячейку, затем выберите «Данные» > «Проверка данных». В разделе Разрешить выберите Список. В поле Источник введите =имя моего списка.

В этой статье объясняется, как создать в Excel раскрывающийся список, извлекающий данные из другого листа. Это позволяет вводить данные в определенную ячейку рабочего листа из заданного списка записей. Инструкции относятся к Excel 2019, 2016, 2013 и 2010, а также к Excel для Mac 2019, 2016 и 2011.

Введите данные для раскрывающегося меню

В этом примере раскрывающееся меню находится на Листе 1, а данные — на Листе 2. Добавьте еще один лист в Excel, если у вас его еще нет.

Откройте Sheet1 и введите Тип файла cookie: в ячейку D1.

Откройте Лист2. В ячейках с A1 по A4 введите: имбирный пряник, лимон, овсянка с изюмом и шоколадная стружка.

Отформатируйте эти ячейки по своему усмотрению. Это не повлияет на работу раскрывающегося списка.

Назовите выбор данных списка

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

Выберите ячейки, которые вы хотите использовать для раскрывающегося списка. В этом примере выберите ячейки A1–A4 на Листе2.

Выберите поле имени над столбцом A и под меню Excel. Это может быть A1 или A4.

Введите Cookies в поле имени.

Нажмите Enter.​

Ячейки с A1 по A4 на листе 2 теперь имеют название диапазона файлов cookie.

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

Создать раскрывающийся список

Теперь вы готовы создать раскрывающийся список. В этом примере раскрывающийся список появится в ячейке E1 на Листе 1.

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

Выберите Данные на ленте.

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

Выберите вкладку «Настройки» во всплывающем окне.

Выберите «Список» в меню «Разрешить».

Введите  =Cookies в текстовом поле Источник.

Выберите ОК.

В ячейке E1 на листе Sheet1 появится маленькая стрелка. Щелкните эту стрелку, чтобы просмотреть элементы списка. Выберите элемент в списке, чтобы вставить данные в ячейку.

Чтобы удалить раскрывающийся список, выберите ячейку E1, повторно откройте диалоговое окно "Проверка данных" и выберите "Очистить все".

Редактирование элементов раскрывающегося списка

Чтобы раскрывающийся список всегда обновлялся с учетом изменений ваших данных, измените варианты в списке. Поскольку в этом примере в качестве источника для списка используется именованный диапазон, изменение имен файлов cookie в ячейках с A1 по A4 на Листе 2 немедленно приводит к изменению имен в раскрывающемся списке на Листе 1.

Например, если вы замените "Овсяный изюм" на "Песочное печенье", в раскрывающемся списке появится новая запись.

Параметры защиты раскрывающегося списка

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

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

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

Раскрывающиеся списки довольно распространены на веб-сайтах/в приложениях и очень интуитивно понятны пользователю.

Посмотреть видео – Создание раскрывающегося списка в Excel

<Р>