Как вернуть значение, если искомое значение присутствует в заданном диапазоне Excel?
При работе с данными в Excel часто требуется проверить, содержится ли определённое значение в заданном диапазоне, и, если да — автоматически получить соответствующее значение из соседней ячейки. Например, как показано на скриншоте слева, при поиске числа 5 в списке или диапазоне вы мгновенно получаете связанное с ним соседнее значение. Это особенно полезно для задач вроде поиска товаров по артикулам, получения данных о пользователях или сопоставления кодов со значениями — без утомительного ручного перебора.
Вернуть значение, если заданное значение существует в определённом диапазоне
Вернуть значение, если заданное значение существует в определённом диапазоне, с помощью функции ВПР
Чтобы быстро получить значение, связанное с конкретной записью из таблицы или диапазона данных в Excel, воспользуйтесь простым решением — функцией ВПР.
This method is particularly effective if your lookup column (where you search for the value) — это крайний левый столбец вашей Диапазон данных, и вы хотите получить данные из столбца справа от него. Эта функция Общие для поиска кодов, имён, идентификаторов или справочных номеров и удобного получения связанных сведений.
1. Выберите пустую ячейку, куда хотите поместить результат, и введите следующую формулу в строку формул:
=VLOOKUP(E2,A2:C8,3,TRUE)Нажмите Enter, чтобы выполнить формулу. См. скриншот:
В данном примере, если число 5 (в ячейке E2) попадает в указанный числовой диапазон в столбце A (например, между 4 и 6), Excel найдёт это значение и сразу же подставит соответствующее значение из третьего столбца (столбца C) диапазона A2:C8 в выбранную ячейку. На иллюстрации возвращается «Addin 012», поскольку число 5 находится в диапазоне от 4 до 6.
Примечание: В формуле E2 — это искомое значение, A2:C8 — диапазон данных, включающий как диапазон для поиска, так и возвращаемые данные, а 3 указывает, что возвращаемое значение должно браться из третьего столбца указанного диапазона. При необходимости скорректируйте эти ссылки в соответствии с вашим листом.
Советы и подводные камни:
- Убедитесь, что диапазон поиска (A2:C8) включает и столбец для поиска, и столбец для возврата.
- При использовании ВПР с аргументом ИСТИНА столбец поиска должен быть отсортирован по возрастанию, иначе результат может оказаться непредсказуемым.
- Для точного совпадения укажите ЛОЖЬ в качестве четвёртого аргумента, а для поиска по диапазону (как в данном примере) оставьте его равным ИСТИНА.
- Если ваши данные часто обновляются, обязательно дважды проверяйте ссылки, чтобы избежать ошибок несоответствия.
Вернуть значение, если заданное значение существует в определённом диапазоне, с помощью функций ИНДЕКС и ПОИСКПОЗ
Комбинация функций ИНДЕКС и ПОИСКПОЗ — это гибкий способ получить значение, когда искомое присутствует в заданном диапазоне. В отличие от ВПР, ИНДЕКС и ПОИСКПОЗ позволяют искать значение в любом столбце и возвращать результат из любого другого столбца — независимо от их расположения. Это особенно удобно, если столбец поиска не находится слева или когда структура данных требует большей гибкости.
1. Выберите пустую ячейку для размещения результата (например, F2) и введите следующую формулу в строку формул:
=INDEX(C2:C8, MATCH(E2, A2:A8,1))Нажмите Enter, чтобы подтвердить формулу.
- MATCH(E2, A2:A8, 1) находит позицию наибольшего значения, не превышающего E2, в столбце A. (Для этого столбец A должен быть отсортирован по возрастанию.)
- INDEX(C2:C8, …) возвращает значение из столбца C в той строке, номер которой определяет функция ПОИСКПОЗ.
Эта формула ищет значение из ячейки E2 в диапазоне A2:A8. Если значение найдено (например, 5 находится между 4 и 6 в одной из строк), функция ПОИСКПОЗ возвращает его относительную позицию, а функция ИНДЕКС извлекает соответствующее значение из диапазона C2:C8. Аргумент «1» в функции ПОИСКПОЗ указывает на приближённое совпадение, поэтому убедитесь, что диапазон поиска отсортирован по возрастанию.
- Если требуется точное совпадение, используйте
0в качестве третьего аргумента функции ПОИСКПОЗ. - Функции ИНДЕКС и ПОИСКПОЗ поддерживают как вертикальную, так и горизонтальную ориентацию данных.
- Если значение не найдено, формула возвращает #Н/Д. Для более удобного отображения рекомендуется использовать обёртку
ЕСЛИОШИБКА.
Вернуть значение, если заданное значение существует в определённом диапазоне, с помощью функции XПОИСК
Функция XПОИСК — это современная альтернатива для поиска значений в Excel 365 и Excel 2019. Она преодолевает многие ограничения функции ВПР, такие как зависимость от положения столбца с искомыми данными и автоматический выбор между точным и приближённым поиском.
1. В ячейку, куда вы хотите поместить результат (например, F2), введите следующую формулу:
=XLOOKUP(1, (E2>,=A2:A8)*(E2<,=B2:B8), C2:C8)После ввода формулы нажмите Enter, чтобы увидеть результат в выбранной ячейке.
- (E2>=A2:A8) проверяет, больше или равно ли значение в ячейке E2 каждому значению в диапазоне A2:A8.
- (E2<=B2:B8) проверяет, является ли значение в ячейке E2 меньшим или равным каждому значению в диапазоне B2:B8.
- Умножение этих двух условий даёт массив из единиц и нулей, где 1 означает, что значение в ячейке E2 попадает в диапазон между значениями A и B в соответствующей строке.
- XLOOKUP(1, …, C2:C8) находит первое вхождение значения 1 и возвращает соответствующее значение из столбца C.
- Функция XПОИСК автоматически адаптируется при вставке или перемещении столбцов, в то время как ВПР с фиксированными номерами столбцов такой гибкостью не обладает.
- Работает как с вертикальными, так и с горизонтальными данными.
- Требуется Excel 365 или Excel 2021; для более старых версий воспользуйтесь другими методами, описанными выше.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Связанные статьи:
- Как выполнить ВПР в Excel и получить результат в виде ИСТИНА/ЛОЖЬ или ДА/НЕТ?
- Как с помощью ВПР найти значение в соседней или следующей ячейке в Excel?
- Как получить значение в другой ячейке, если ячейка содержит определённый текст в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек