ПРОСМОТРX в Excel: как найти данные по артикулу или коду

ПРОСМОТРX Excel

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

ПРОСМОТРX, или XLOOKUP в английском Excel, доступна в Excel из Office 2021, Office 2024 и Microsoft 365. В Excel 2016 и 2019 этой функции нет.

Подготовьте небольшой справочник

Введите данные в диапазон A1:C5. Первая строка содержит заголовки, остальные четыре описывают товары. Артикулы набирайте латинскими буквами. Остатки в примере условные.

A: Артикул B: Товар C: Остаток
P-101 Мышь 12
P-102 Клавиатура 7
P-103 USB-концентратор 0
P-104 SSD 5

В ячейку E1 впишите «Искомый артикул», в F1 «Остаток». В E2 введите P-102. Ячейка F2 будет содержать формулу.

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

Введите формулу точного поиска

=ПРОСМОТРX(E2;$A$2:$A$5;$C$2:$C$5;"Нет в справочнике")

После Enter в F2 должно появиться 7. Формула ищет содержимое E2 в столбце A и возвращает остаток из столбца C.

Часть формулы Назначение в примере
E2 Артикул, который нужно найти
$A$2:$A$5 Список артикулов
$C$2:$C$5 Остатки в соответствующих строках
"Нет в справочнике" Результат при отсутствии совпадения

По умолчанию ПРОСМОТРX ищет точное совпадение. Для этого примера сортировать артикулы не нужно. Английский вариант формулы с разделителем-запятой:

=XLOOKUP(E2,$A$2:$A$5,$C$2:$C$5,"Not found")

Разделитель аргументов зависит от региональных настроек. Если Excel не принимает запись, начните вводить название функции и выберите её из подсказки. Затем подставьте диапазоны через окно аргументов. Это также поможет избежать путаницы между похожими русскими и латинскими буквами.

Диапазоны поиска и результата охватывают одни и те же строки справочника.
Диапазоны поиска и результата охватывают одни и те же строки справочника.

Скопируйте формулу на несколько строк

Введите в E3 артикул P-104, а в E4 P-103. Скопируйте F2 вниз до F4. Должны получиться значения 7, 5 и 0.

Ссылка E2 при копировании превращается в E3 и E4. Диапазоны со знаками доллара остаются на месте. Без фиксации Excel сдвинул бы справочник вниз вместе с формулой, и первые товары могли бы выпасть из поиска.

После добавления новых товаров расширьте оба диапазона до одной последней строки. Например, при записи в строке 6 используйте $A$2:$A$6 и $C$2:$C$6. Проверьте формулу на новом артикуле, прежде чем распространять её на весь отчёт.

Отличайте нулевой остаток от отсутствующего артикула

Замените E2 на P-999. Формула должна показать «Нет в справочнике». Затем введите P-103: результатом будет число 0.

Это разные ситуации. P-103 существует, но остаток равен нулю. P-999 отсутствует в источнике. Если возвращать 0 и для отсутствующего артикула, сотрудник может принять ошибку справочника за отсутствие товара.

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

Почему найдено не то значение

При повторяющихся артикулах обычный поиск возвращает первое совпадение. Допишите ещё одну строку P-102 с другим остатком и расширьте оба диапазона. Результат останется равным 7 из первой найденной строки. Перед расчётом разберитесь с дублями: порядок записей не должен случайно определять рабочий остаток.

Если визуально одинаковый код не находится, проверьте пробелы, латинские и русские буквы, а также тип значения. Число 123 и текстовый идентификатор «00123» нельзя без проверки считать одним кодом. При работе с выгрузкой пригодится инструкция по импорту CSV с сохранением ведущих нулей.

Не исправляйте проблему одним изменением формата ячейки: оно не всегда меняет уже сохранённый тип данных. Сравните исходные значения и приведите идентификаторы к согласованному виду.

Что проверить перед передачей книги

Протестируйте три строки: существующий артикул с остатком, товар с нулём и отсутствующий код. Сверьте результаты со справочником. Затем уточните, поддерживает ли Excel получателя ПРОСМОТРX.

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

Оцените статью
( Пока нет оценок )
Поделиться с друзьями
msoft.store
Добавить комментарий

Нажимая на кнопку "Отправить комментарий", я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.