ПРОСМОТРX по двум условиям
Практический разбор: найти цену по сочетанию поставщика и артикула. Таблица под задачу, контрольный результат, ошибки конкретной формулы, самопроверка и самостоятельное задание.
=ПРОСМОТРX(1;(A2:A100=H2)*(B2:B100=I2);D2:D100;"Не найдено")=XLOOKUP(1,(A2:A100=H2)*(B2:B100=I2),D2:D100,"Not found")В русской локализации Excel аргументы чаще разделяются точкой с запятой, а в английской — запятой. При копировании формул учитывайте региональные настройки.
Короткий ответ
В этой статье разберем, как в Excel найти цену по сочетанию поставщика и артикула. Сначала — готовая формула, затем подробная логика, рабочий пример, проверка результата, типовые ошибки и варианты применения в реальной таблице.
Зачем использовать этот прием
Практическая задача этой статьи — найти цену по сочетанию поставщика и артикула. Именно поэтому пример ниже использует поля «Поставщик» и «Артикул», а не универсальную таблицу с абстрактными «Значение 1 / Значение 2».
Контрольный результат для примера: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽. Сначала получите его вручную по данным таблицы, затем используйте формулу. Так вы проверяете не только синтаксис Excel, но и саму бизнес-логику расчёта.
Пример исходных данных
| Поставщик | Артикул | Наименование | Цена, ₽ |
|---|---|---|---|
| АльфаСнаб | P-101 | Бумага A4 | 410 |
| АльфаСнаб | P-205 | Тонер 85A | 3150 |
| ОфисОпт | P-101 | Бумага A4 | 395 |
| ОфисОпт | P-205 | Тонер 85A | 3290 |
| ТехПартнёр | P-205 | Тонер 85A | 3070 |
Контроль: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.
Таблица для «ПРОСМОТРX по двум условиям» подобрана так, чтобы результат можно было проверить вручную. При переносе в свой файл сохраняйте смысл ключевых полей, даже если рабочие названия столбцов отличаются.
Подготовка данных
- Ключи одного типа и формата.
- Уникальность ключа там, где ожидается одна запись.
- Границы поисковых диапазонов.
- Отдельно проверьте контрольную строку: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.
- Определите, ожидаете ли одно совпадение, ближайший порог или список совпадений: режим поиска должен соответствовать задаче.
Разбираем формулу пошагово
1. Сформулируйте задачу словами
Запишите правило без Excel:
> «Для каждой строки нужно найти цену по сочетанию поставщика и артикула.»
Для «ПРОСМОТРX по двум условиям» сначала сформулируйте правило однозначно и получите ожидаемый ответ словами. Формулу стоит вводить только после того, как бизнес-логика не допускает двух разных трактовок.
2. Определите входные данные
Отметьте:
- откуда берется исходное значение;
- какой диапазон участвует;
- какие критерии обязательны;
- какой результат ожидается;
- что делать при пустых данных;
- что делать при ошибке.
3. Введите формулу
Русская версия:
=ПРОСМОТРX(1;(A2:A100=H2)*(B2:B100=I2);D2:D100;"Не найдено")
Английская версия:
=XLOOKUP(1,(A2:A100=H2)*(B2:B100=I2),D2:D100,"Not found")
Формулу для «ПРОСМОТРX по двум условиям» сначала проверьте на одной понятной строке; только после совпадения с ручным ответом переносите её на весь диапазон.
4. Проверьте вручную
Для контрольной строки «ПРОСМОТРX по двум условиям» получите ожидаемый результат без Excel: поисковый ответ найдите в таблице глазами, а расчётный пересчитайте отдельно. Затем сравните ручной ответ с формулой.
5. Проверьте ссылки
В Excel:
A2— относительная ссылка;$A$2— полностью абсолютная;$A2— фиксирован столбец;A$2— фиксирована строка.
В «ПРОСМОТРX по двум условиям» неверно закреплённая ссылка может не дать ошибки Excel, но сместить диапазон после копирования. Поэтому сравните адреса ссылок в первой и последней формуле.
---
Ключевой нюанс именно этой задачи
Логические массивы превращаются в 1 и 0; единица возникает только там, где выполнены оба условия.
Для поисковых задач ключ должен быть очищен и, если того требует бизнес-логика, уникален. Всегда проверяйте, что формула возвращает именно ту строку, которую вы ожидаете.
---
Как читать формулу, а не заучивать ее
Для «ПРОСМОТРX по двум условиям» читайте формулу через задачу найти цену по сочетанию поставщика и артикула, а не по названиям функций. Сначала найдите в выражении входное значение или ключ, затем диапазон/порог, после этого — возвращаемый или вычисляемый результат. Контрольная точка для текущего примера: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.
Определите, ожидаете ли одно совпадение, ближайший порог или список совпадений: режим поиска должен соответствовать задаче. Ключевой смысл из исходного материала: Логические массивы превращаются в 1 и 0; единица возникает только там, где выполнены оба условия.
Рабочий сценарий №1: ежедневная выгрузка
Используйте приём в процессе, где действительно нужно найти цену по сочетанию поставщика и артикула. Не начинайте с массового копирования формулы: возьмите одну понятную строку из примера и подтвердите результат: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽. После этого перенесите логику на 5–10 реальных строк с разными исходными значениями.
Особенность именно этой темы: Логические массивы превращаются в 1 и 0; единица возникает только там, где выполнены оба условия.
Рабочий сценарий №2: контроль качества
Сделайте рядом с основным результатом контроль качества. Для статьи «ПРОСМОТРX по двум условиям» минимум один контроль должен проверять следующую вещь: скрытые пробелы и текст/число в ключах. Второй контроль должен показывать строки, где исходные данные не позволяют уверенно применить правило.
Проверочный сценарий: Проверьте отсутствующий ключ, дубликат ключа и значение ровно на пороге, если используется приближённый режим.
Рабочий сценарий №3: подготовка к сводной таблице
После расчёта не прячьте результат внутри исходного листа. Выведите его в отчёт в том разрезе, в котором принимается решение. Для задачи «найти цену по сочетанию поставщика и артикула» рядом полезно показывать исходные значения, из которых получился результат, а также число строк/объектов в базе расчёта.
Сводная полезна после того, как поиск уже обогатил исходные строки нужными атрибутами. Саму задачу точечного поиска сводная обычно не заменяет.
Частые ошибки
Ошибка 1. Скрытые пробелы и текст/число в ключах
В этой теме это особенно опасно, потому что результат может выглядеть правдоподобно. Проверьте отсутствующий ключ, дубликат ключа и значение ровно на пороге, если используется приближённый режим.
Ошибка 2. Дубли ключа дают правдоподобный, но спорный результат
Сверьте результат с контрольной точкой: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.
Ошибка 3. Диапазоны поиска и возврата смещены
Если структура данных изменилась, заново сопоставьте аргументы формулы со столбцами, а не просто протягивайте старое выражение.
Ошибка 4. Игнорируется нюанс конкретной функции
Логические массивы превращаются в 1 и 0; единица возникает только там, где выполнены оба условия.
Профессиональная проверка формулы
Для «ПРОСМОТРX по двум условиям» одной обычной строки недостаточно. Прогоните отдельные тесты на корректный пример, изменение входа, качество данных и пограничное значение:
- Контрольный пример: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.
- Изменение входа: Проверьте отсутствующий ключ, дубликат ключа и значение ровно на пороге, если используется приближённый режим.
- Качество данных: специально создайте проблему «скрытые пробелы и текст/число в ключах» и убедитесь, что вы её замечаете.
- Граница правила: проверьте значение непосредственно на пороге/границе, если она есть в формуле.
- Массовое копирование: сравните первую, вторую и последнюю формулу после протягивания.
- Независимый итог: пересчитайте хотя бы одну строку вручную или альтернативным способом.
Чек-лист аудита
Советы для больших таблиц
Для статьи «ПРОСМОТРX по двум условиям» при росте файла особенно важно сохранить проверяемость задачи «найти цену по сочетанию поставщика и артикула». На больших справочниках ограничивайте диапазоны фактическими строками или Таблицей Excel. Перед оптимизацией проверьте качество ключа: ускорение не исправляет неверное сопоставление. Определите, ожидаете ли одно совпадение, ближайший порог или список совпадений: режим поиска должен соответствовать задаче. Контроль качества не должен исчезать после оптимизации: оставьте хотя бы одну независимую строку, где ожидается результат «Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.».
Когда лучше использовать Power Query
Для задачи «найти цену по сочетанию поставщика и артикула» Power Query становится предпочтительнее, когда обработка повторяется с каждой новой выгрузкой. Power Query лучше, когда справочники нужно регулярно объединять, сверять, очищать и получать список несовпадений. Формулу оставляйте, когда результат нужен прямо в строке рабочего листа. В случае «ПРОСМОТРX по двум условиям» формулу имеет смысл оставить на листе, если пользователь должен менять параметры и сразу видеть пересчёт.
Когда лучше сводная таблица
В контексте «ПРОСМОТРX по двум условиям» сводная нужна не вместо формулы, а после подготовки корректного поля/показателя. Сводная полезна после того, как поиск уже обогатил исходные строки нужными атрибутами. Саму задачу точечного поиска сводная обычно не заменяет. Для проверки всегда возвращайтесь к исходным строкам, на которых подтверждается: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.
Практическое задание
Создайте таблицу минимум из 20 строк и примените:
=ПРОСМОТРX(1;(A2:A100=H2)*(B2:B100=I2);D2:D100;"Не найдено")
После этого специально добавьте:
- пустое значение в тесте «ПРОСМОТРX по двум условиям»;
- дубликат ключа или строки;
- нулевое значение там, где оно допустимо;
- граничный сценарий правила;
- строку с неправильным типом данных.
Запишите, как меняется результат и почему.
---
Задание повышенной сложности
Возьмите практическое задание именно по теме «ПРОСМОТРX по двум условиям» и добавьте исключение «скрытые пробелы и текст/число в ключах». Сделайте так, чтобы пользователь либо получил корректный результат, либо увидел понятный контроль качества, а не правдоподобное неверное число.
Затем выполните тест: Проверьте отсутствующий ключ, дубликат ключа и значение ровно на пороге, если используется приближённый режим. Финальная проверка должна по-прежнему объяснять контрольный результат: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.
Что запомнить
- Что решаем: найти цену по сочетанию поставщика и артикула.
- Контрольный результат: Для поставщика «ОфисОпт» и артикула P-205 цена — 3 290 ₽.
- Ключевой нюанс: Логические массивы превращаются в 1 и 0; единица возникает только там, где выполнены оба условия.
- Главный риск: скрытые пробелы и текст/число в ключах.
- Практический принцип: Определите, ожидаете ли одно совпадение, ближайший порог или список совпадений: режим поиска должен соответствовать задаче.
- Следующий шаг: примените формулу на собственных данных и сохраните независимую контрольную строку.
Что изучить дальше
После темы «ПРОСМОТРX по двум условиям» сравните два способа решить близкую задачу: формулой на листе и преобразованием данных. Для сценария «найти цену по сочетанию поставщика и артикула» ориентир по Power Query такой: Power Query лучше, когда справочники нужно регулярно объединять, сверять, очищать и получать список несовпадений. Формулу оставляйте, когда результат нужен прямо в строке рабочего листа. Если же нужен групповой отчёт по периодам/сегментам, используйте следующий ориентир: Сводная полезна после того, как поиск уже обогатил исходные строки нужными атрибутами. Саму задачу точечного поиска сводная обычно не заменяет.
Не знаете, что учить дальше?
Проверьте свой уровень Excel за 7–10 минут
18 рабочих ситуаций покажут сильные стороны и темы, которые дадут вам самый заметный прирост.
Практика по системе
Закрепите формулы на рабочих задачах
В курсах Tablicus теория связана с практикумами, таблицами и автоматической проверкой результата.