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

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

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

В мастер-листе вы хотите поместить ВПР, чтобы проверить, присутствовал ли этот студент в этом месяце или отсутствовал. Было бы просто, если бы ваши данные были на одном листе, но это не так. Он находится на разных листах. Но это не значит, что мы не можем извлекать данные из другого листа в Excel. Мы можем и будем.

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

Общая формула для ВПР из нескольких листов:

В этом примере моя посещаемость в таблицах «Янв», «Фев» и «Март» находится в том же диапазоне A2: C11.

Теперь я подготовил мастер-лист.

В ячейку C4 поместите эту формулу и перетащите ее вниз.

Теперь, когда вы меняете название месяца в ячейке C1, Excel будет извлекать значение из другого листа (из листа этого месяца, если он существует).

Мы использовали функцию Excel Indirect, чтобы получить значение с другого листа.

INDIRECT заменяет текст на ссылку. Мы использовали INDIRECT для ссылки на другие листы в Excel.

Например, если вы напишете КОСВЕННЫЙ («лист2: А2») в a1 на листе 1. Он будет извлекать значение из листа2! A2 в лист1: A1. Если вы напишете = VLOOKUP («abc», INDIRECT («sheet2! A2: B100»), 2,0) на любом листе, функция VLOOKUP будет искать «abc» в диапазоне A2: B100 на листе 2.

КОСВЕННО («» & $ C $ 1 & «! B2: C11»): Здесь мы хотим, чтобы имя листа изменилось, поэтому мы написали это так. Если ячейка C1 содержит «Jan», она будет преобразована в INDIRECT («Jan! B2: C11»), которая затем будет преобразована в Jan! B2: C11 для массива таблицы VLOOKUP. Если у C1 есть фев, он будет переведен в КОСВЕННЫЙ («Фев! B2: C11») и так далее.

После этого VLOOKUP сделал свое дело.

VLOOKUP (B4, INDIRECT («» & $ C $ 1 & «! B2: C11»), 2,0): теперь, поскольку Indirect предоставил массив таблицы, VLOOKUP просто легко извлекает данные из этого диапазона.

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

У нас есть несколько списков, как показано ниже.

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

Выберите «Формула»> «Диспетчер имен» в «Определенные имена» ИЛИ используйте сочетание клавиш Ctrl F3, чтобы открыть диспетчер имен, где мы будем хранить списки массива с их именами, чтобы мы могли вызывать их по имени, когда это необходимо.

Нажмите New, чтобы создать. Здесь Имя будет Месяц, а в поле Относится к опции введите список под Месяцем, как показано ниже.

То же самое мы сделаем для Week_Days, и он будет выглядеть как

Затем щелкните Проверка данных под панелью данных. Выберите вариант списка «Разрешить» и выберите ячейки для названий основных категорий, которые в данном случае находятся в ячейках B2 и C2 «Месяц» и «Неделя_Дни»

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

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

Результат отображается так

Если я не хочу «Месяц» и «Неделя_Дни». Вместо этого я хочу Fruits_Name и Veget_Name. Нам просто нужно отредактировать наш список диспетчера имен.

Нажмите Ctrl + F3, чтобы открыть Диспетчер имен, удалить уже вставленный список и добавить новые списки, то есть Fruits_Name и Vegetables_Name.

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

Вместо ячеек Month и Week_Days мы будем использовать Fruits_Name и Vegetables_Name в Data Validation и нажать OK

Как видите, сюда добавлен новый список.

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

Надеюсь, эта статья о том, как выбирать данные из раскрывающегося списка и извлекать данные из разных листов в Microsoft Excel, дает пояснения. Дополнительные статьи о раскрывающихся списках и связанных инструментах Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].

link: / excel-edit-validating-text-entries-in-microsoft-excel [Проверка данных в Excel]: Ограничивает пользователей вводить значение вручную с помощью этого инструмента в Excel.

link: / tips-vlookup-function-in-data-validation [Способ использования функции Vlookup при проверке данных]: разрешить значения из таблицы vlookup в Excel.

link: / tips-restrict-date-using-data-validation [Ограничить даты с помощью проверки данных]: разрешить даты в ячейке, которая находится в формате даты Excel в Excel.

link: / tips-how-to-give-the-error-messages-in-data-validation [Как выдавать сообщения об ошибках при проверке данных]: настроить ячейку проверки данных с сообщениями об ошибках в Excel.

link: / tips-how-to-create-drop-down-lists-in-excel-sheet [Создать раскрывающиеся списки в Excel с помощью проверки данных]: ограничивает пользователей вводом данных вручную в Excel.

link: / tips-if-condition-in-excel [Как использовать функцию IF в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если оно ложно .

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.

link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.