Как найти значение в Excel: полный справочник по формулам и инструментам

Иван Корнев·11.04.2026·⏱4 мин

Чтобы найти значение в Excel, используйте Ctrl+F для ручного поиска или функции ВПР, ИНДЕКС/ПОИСКПОЗ для автоматического извлечения данных из таблиц. Для замены текста применяйте функцию ПОДСТАВИТЬ. Выбор метода зависит от задачи: разовая проверка или построение динамической отчетности.

Краткий ответ: Для разового поиска жмите Ctrl + F. Чтобы автоматически подтянуть данные из другой таблицы (например, цену по артикулу), используйте формулу =ВПР() или связку =ИНДЕКС(...; ПОИСКПОЗ(...)).

Ручной поиск и навигация по данным

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

Горячие клавиши и параметры

Нажмите Ctrl + F, чтобы открыть окно «Найти и заменить».

  • Найти все: выводит список всех совпадений внизу окна. Удобно для оценки масштаба проблемы (например, сколько ячеек содержат ошибку #Н/Д).
  • Найти далее: переключает курсор на следующее совпадение.
  • Параметры >>: позволяют уточнить поиск:
    • Искать в: формулах, значениях или примечаниях.
    • Учитывать регистр: важно для точных кодов.
    • Ячейка целиком: чтобы найти «10», но не находить «100» или «210».

Лайфхак с фильтрами: Если данных много, включите фильтр (Ctrl + Shift + L) и используйте текстовый фильтр в заголовке столбца. Это скроет лишние строки и оставит только нужные записи на экране.

Автоматический поиск с помощью формул

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

Функция ВПР (VLOOKUP)

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

Синтаксис: =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

Пример: У вас есть артикул в ячейке A2, а прайс-лист находится в диапазоне D:F (где D — артикул, E — название, F — цена). Чтобы получить цену: =ВПР(A2; D:F; 3; 0) Где 3 — это третий столбец диапазона (цена), а 0 (или ЛОЖЬ) означает точное совпадение.

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

Связка ИНДЕКС и ПОИСКПОЗ

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

Логика работы:

  1. ПОИСКПОЗ находит номер строки, где лежит искомое значение.
  2. ИНДЕКС берет значение из этой строки в нужном столбце.

Синтаксис: =ИНДЕКС(столбец_с_результатом; ПОИСКПОЗ(искомое_значение; столбец_для_поиска; 0))

Пример: Нужно найти имя сотрудника (столбец B) по его табельному номеру (столбец D). =ИНДЕКС(B:B; ПОИСКПОЗ(A2; D:D; 0)) Эта формула успешно сработает, даже если столбец с именами стоит левее столбца с номерами.

Поиск и замена фрагментов текста

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

Определение позиции: ПОИСК и НАЙТИ

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

  • ПОИСК: не чувствителен к регистру («текст» = «Текст»). Поддерживает маски (? — любой символ, * — любая последовательность).
  • НАЙТИ: чувствителен к регистру. Маски не поддерживает.

Пример использования: =ЕСЛИОШИБКА(ПОИСК("Москва"; A1); 0) Вернет позицию слова «Москва» или 0, если слова нет (благодаря обработке ошибки).

Замена данных: ПОДСТАВИТЬ

Функция заменяет старый текст на новый внутри ячейки.

Синтаксис: =ПОДСТАВИТЬ(ячейка; старый_текст; новый_текст; [номер_вхождения])

Пример: Заменить дефисы на пробелы в артикуле: =ПОДСТАВИТЬ(A1; "-"; " ") Если нужно заменить только второй дефис, укажите четвертый аргумент: =ПОДСТАВИТЬ(A1; "-"; " "; 2).

Комбинирование: Часто ПОДСТАВИТЬ вкладывают внутрь ВПР, чтобы очистить данные перед поиском. Например: =ВПР(ПОДСТАВИТЬ(A1;" ",""); Таблица; 2; 0) — ищет значение, предварительно удалив из него пробелы.

Сравнение методов поиска

МетодКогда использоватьГлавное преимуществоОграничение
Ctrl + FРазовая проверка, аудит данныхМгновенно, не требует формулНе обновляется автоматически
ВПРСтандартный поиск по справочникамПростота написанияИщет только слева направо
ИНДЕКС + ПОИСКПОЗСложные таблицы, поиск влевоГибкость, устойчивость к изменениям структурыДлиннее формула
ПОДСТАВИТЬОчистка и форматирование текстаМеняет данные «на лету»Работает только с текстом

Частые ошибки при поиске

  1. Ошибка #Н/Д в ВПР: Чаще всего возникает из-за лишних пробелов в данных (невидимых глазу). Решение: используйте функцию СЖПРОБЕЛЫ или очистите данные через «Текст по столбцам».
  2. Неверный номер столбца: При использовании ВПР нумерация идет от первого столбца выделенного диапазона, а не от столбца А листа.
  3. Числа как текст: Если в одной таблице код «123» (число), а в другой «123» (текст), поиск не сработает. Приведите форматы к единому виду.
  4. Относительные ссылки: При протягивании формулы диапазон таблицы «уезжает». Всегда фиксируйте диапазон знаками доллара: $A$2:$C$100 или превращайте его в «Умную таблицу» (Ctrl+T).

FAQ

В чем разница между ВПР и ХВПР? ХВПР (XLOOKUP) — это современная функция (доступна в Excel 2021 и Office 365), которая заменяет ВПР. Она умеет искать в любом направлении, имеет встроенную обработку ошибок и проще в написании. Если у вас новая версия Excel, используйте её.

Как найти дубликаты значений? Выделите столбец, перейдите на вкладку «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения». Excel подсветит все дубли цветом.

Можно ли искать значение по нескольким критериям сразу? Да. В классическом Excel для этого используют формулу массива с ИНДЕКС/ПОИСКПОЗ, где условия перемножаются. В новых версиях удобнее использовать функцию ФИЛЬТР или СУММЕСЛИМН (если нужно получить числовой итог).