Как выбрать значение из списка в Excel
Обновлено: 21.11.2024
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ВЫБОР в Microsoft Excel.
Описание
Использует index_num для возврата значения из списка аргументов значения. Используйте ВЫБОР, чтобы выбрать одно из 254 значений на основе порядкового номера. Например, если значения от 1 до 7 являются днями недели, функция ВЫБОР возвращает один из дней, если в качестве индекса используется число от 1 до 7.
Синтаксис
ВЫБЕРИТЕ(номер_индекса, значение1, [значение2], . )
Синтаксис функции ВЫБОР имеет следующие аргументы:
Index_num Требуется. Указывает, какой аргумент значения выбран. Index_num должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число от 1 до 254.
Если номер_индекса равен 1, функция ВЫБОР возвращает значение1; если 2, ВЫБОР возвращает значение2; и так далее.
Если index_num является дробью, то перед использованием оно усекается до наименьшего целого числа.
Значение1, значение2, . Значение 1 обязательно, последующие значения необязательны. От 1 до 254 аргументов значений, из которых ВЫБОР выбирает значение или действие для выполнения на основе index_num. Аргументами могут быть числа, ссылки на ячейки, определенные имена, формулы, функции или текст.
Примечания
Если index_num является массивом, каждое значение оценивается при оценке CHOOSE.
Аргументы значения для ВЫБОР могут быть ссылками на диапазон, а также отдельными значениями.
Например, формула:
который затем возвращает значение на основе значений в диапазоне B1:B10.
Функция ВЫБОР оценивается первой, возвращая ссылку B1:B10. Затем функция СУММ оценивается с использованием B1:B10, результата функции ВЫБОР, в качестве аргумента.
Примеры
Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.
Допустим, вы хотите найти добавочный номер телефона сотрудника, используя его номер значка или правильную ставку комиссии за сумму продажи. Вы просматриваете данные, чтобы быстро и эффективно находить определенные данные в списке и автоматически проверять, используете ли вы правильные данные. После поиска данных вы можете выполнять вычисления или отображать результаты с возвращенными значениями. Существует несколько способов поиска значений в списке данных и отображения результатов.
Что вы хотите сделать?
Поиск значений в списке по вертикали с помощью точного совпадения
Для выполнения этой задачи можно использовать функцию ВПР или комбинацию функций ИНДЕКС и ПОИСКПОЗ.
Примеры ВПР
Дополнительную информацию см. в разделе Функция ВПР.
Примеры ИНДЕКС и ПОИСКПОЗ
На простом английском языке это означает:
=INDEX(Мне нужно возвращаемое значение из C2:C10, которое будет MATCH(Kale, которое находится где-то в массиве B2:B10, где возвращаемое значение является первым значением, соответствующим Kale))
Формула ищет первое значение в ячейках C2:C10, соответствующее капусте (в ячейке B7), и возвращает значение в ячейке C7 (100), которое является первым значением, соответствующим кале.
Поиск значений в списке по вертикали с использованием приблизительного совпадения
Для этого используйте функцию ВПР.
Важно! Убедитесь, что значения в первой строке отсортированы по возрастанию.
В приведенном выше примере функция ВПР ищет имя учащегося с 6 опозданиями в диапазоне A2:B7. В таблице нет записи для 6 опозданий, поэтому функция ВПР ищет следующее максимальное совпадение, меньшее 6, и находит значение 5, связанное с именем Дэйв, и таким образом возвращает Дейва.
Дополнительную информацию см. в разделе Функция ВПР.
Поиск значений по вертикали в списке неизвестного размера с использованием точного совпадения
Для выполнения этой задачи используйте функции СМЕЩЕНИЕ и ПОИСКПОЗ.
Примечание. Используйте этот подход, если ваши данные находятся во внешнем диапазоне данных, который вы обновляете каждый день. Вы знаете, что цена указана в столбце B, но не знаете, сколько строк данных вернет сервер, а первый столбец не отсортирован по алфавиту.
C1 — верхние левые ячейки диапазона (также называемые начальной ячейкой).
MATCH("Oranges",C2:C7,0) ищет апельсины в диапазоне C2:C7. Вы не должны включать начальную ячейку в диапазон.
1 – это количество столбцов справа от начальной ячейки, из которой должно быть получено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Продажи.
Поиск значений в списке по горизонтали с помощью точного совпадения
Для выполнения этой задачи используйте функцию ГПР. См. пример ниже:
HLOOKUP ищет столбец Продажи и возвращает значение из строки 5 в указанном диапазоне.
Дополнительную информацию см. в разделе Функция ГПР.
Поиск значений в списке по горизонтали с использованием приблизительного совпадения
Для выполнения этой задачи используйте функцию ГПР.
Важно! Убедитесь, что значения в первой строке отсортированы по возрастанию.
В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11 000 и, следовательно, ищет следующее наибольшее значение, меньшее 1100, и возвращает 10 543.
Дополнительную информацию см. в разделе Функция ГПР.
Создание формулы поиска с помощью мастера поиска (только в Excel 2007)
Примечание. Надстройка «Мастер поиска» больше не поддерживается в Excel 2010. Эта функция была заменена мастером функций и доступными функциями «Поиск и ссылки» (ссылка).
В Excel 2007 мастер поиска создает формулу поиска на основе данных рабочего листа с метками строк и столбцов. Мастер поиска помогает найти другие значения в строке, когда известно значение в одном столбце, и наоборот. Мастер поиска использует ИНДЕКС и ПОИСКПОЗ в формулах, которые он создает.
Нажмите ячейку в диапазоне.
На вкладке "Формулы" в группе "Решения" нажмите "Поиск".
Если команда «Поиск» недоступна, необходимо загрузить программу-надстройку «Мастер поиска».
Как загрузить программу надстройки Lookup Wizard
Нажмите кнопку Microsoft Office , выберите «Параметры Excel» и выберите категорию «Надстройки».
В поле "Управление" нажмите "Надстройки Excel", а затем нажмите "Перейти".
В диалоговом окне «Доступные надстройки» установите флажок рядом с «Мастер поиска» и нажмите «ОК».
Как искать значение в списке в Excel?
Предположим, у вас есть ряд данных, как показано на снимке экрана ниже, и вы хотите найти апельсин в столбце «Фрукты», а затем вернуть соответствующую цену в столбце «Цена». Как быстро найти его в Excel?
Поиск значения в списке с формулой
Чтобы найти значение в списке, вы можете использовать формулы.
Выберите пустую ячейку, в которую вы хотите поместить результат, введите в нее эту формулу =ИНДЕКС(A2:C7,MATCH("Orange",A2:A7,0),2) и нажмите клавишу Enter.
Примечание:
<р>1. В приведенной выше формуле A2: C7 — это диапазон данных, оранжевый — это значение, которое вы хотите найти, A2: A7 — это столбец, который вы ищете, 2 указывает, что нужно вернуть соответствующее значение во втором столбце диапазона. Вы можете изменить их по своему усмотрению. <р>2. Вы также можете использовать эту формулу =СМЕЩ(A1,MATCH("Orange",A2:A7, 0),2) для поиска значения и возврата соответствующего значения в другом столбце. В этой формуле A1 — это первая ячейка вашего диапазона данных, а A2: A7 — это диапазон, содержащий значение поиска, а 2 указывает, что нужно найти соответствующее значение в третьем столбце.Найдите значение в списке с помощью Kutools for Excel
Если вам не нравится запоминать сложные формулы, вы можете бесплатно установить Kutools for Excel и найти утилиту «Искать значение в списке» из своей мощной группы «Формулы», которая может помочь вам быстро найти значение и вернуть соответствующее значение в другом списке.
После бесплатной установки Kutools for Excel сделайте следующее:
<р>1. Выберите ячейку, чтобы поместить результат, и нажмите Kutools > Помощник по формулам > Поиск и ссылка > Найдите значение в списке. Смотрите скриншот:<р>2. В всплывающем диалоговом окне «Помощник по формулам» щелкните, чтобы выбрать весь диапазон данных в Table_array, выберите значение поиска в Look_value, а затем выберите диапазон столбцов, который вы хотите вернуть в столбец. Смотрите скриншот:
<р>3. Нажмите «ОК». Теперь соответствующее значение помещается в ячейку, выбранную на шаге 1.
Совет:
<р>1. Значение поиска может находиться только в первом столбце диапазона данных. <р>2. В группе "Формулы" вы можете рассчитать возраст на основе дня рождения, суммировать абсолютное значение диапазона или суммировать все числа в одной ячейке и т. д.Если вы работаете с большой таблицей и вам нужно найти в Excel уникальные значения, соответствующие конкретному запросу, то вам нужно использовать фильтр. Но иногда нам нужно выбрать все строки, которые содержат определенные значения по отношению к другим строкам. В этом случае следует использовать условное форматирование, которое относится к значениям ячеек с запросом. Для получения максимально эффективного результата в качестве запроса будем использовать выпадающий список. Это очень удобно, если вам нужно часто менять один и тот же тип запроса для отображения разных строк таблицы. Ниже мы подробно рассмотрим: как сделать выбор повторяющихся ячеек из выпадающего списка.
Выбор уникальных и повторяющихся значений в Excel
В этой таблице нам нужно выделить цветом все транзакции для конкретного клиента. Для переключения между клиентами мы будем использовать выпадающий список. Поэтому в первую очередь нужно подготовить контент для выпадающего списка. Нам нужны все имена клиентов из столбца А, без повторений.
Прежде чем выбрать уникальные значения в Excel, нам нужно подготовить данные для раскрывающегося списка:
В результате мы получили список данных с уникальными значениями (имена без повторений).
Пришло время создать выпадающий список, из которого мы будем выбирать имена клиентов в качестве запроса.
Прежде чем выбрать уникальные значения из списка, необходимо сделать следующее:
В результате в ячейке B1 мы создали выпадающий список имен клиентов.
Выбор ячеек из таблицы по условию в Excel:
Как работает выбор уникальных значений Excel? При выборе любого значения (имени) из выпадающего списка B1 все строки, содержащие это значение (имя), выделяются в таблице цветом. Чтобы убедиться в этом, в выпадающем списке B1 нужно выбрать другое имя. После этого остальные строки будут автоматически выделены цветом. Такую таблицу теперь легко читать и анализировать.
Принцип автоматического выделения строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет находить уникальные значения в таблице Excel. Если данные совпадают, то формула возвращается к значению ИСТИНА и для всей строки автоматически назначается новый формат. Для того, чтобы формат присваивался всей строке, а не только ячейке в столбце А, используем смешанную ссылку в формуле =$A4.
Читайте также: