Рвущий массивы ПРОСМОТРХ: примеры формул учета в бизнесе

Вам нужно подставить данные из огромной таблицы? Суммировать значения между двумя диапазонами? Всегда видеть перед глазами последнюю отгрузку? Может еще какой-то специфический запрос. Многие пользователи редактора электронных таблиц знают и ценят функцию ВПР, которая сильно помогает с поиском, но имеет ограничения и часто требует комбинирования с другими формулами. Кто-то даже знает про ее «сестру» ГПР. Теперь про все это можно забыть. Знатоки таблиц используют представленную не так давно функцию ПРОСМОТРХ (и сочувственно смотрят на пользователей тех редакторов, в которых этой функции нет).

Правильный подход

Применяем правильно: стандартное описание говорит нам, что ПРОСМОТРХ применяют для поиска. Почему тогда не пользоваться поиском? Потому что когда у нас сложная таблица, то в ней как правило нужно найти что-то неизвестное, соответствующее чему-то известному. Допустим у вас каталог семян цветов со всего света и вам нужно найти артикул для фиалки Инчэнтид Эйприл или на потоке 500 студентов и нужно посмотреть текущий рейтинг Зайцева-Завалдайского. Пропишите функцию в ячейке и увидите результат – или сообщение об отсутствии, если господин Зайцев был давно отчислен, а фиалку занесли в каталог как Зачарованный Апрель. Это были шутливые примеры, ближе к реальности поговорим ниже.

Читаем правильно: на конце не русская буква «ха», а латинская «икс». То есть функция читается «просмотр-икс». И пишется тоже с иксом на конце. Если вы напишете всё русскими буквами, получите ошибку #ИМЯ?. Если не хотите держать эти особенности в голове, пользуйтесь встроенным помощником – диалоговым окном, которое открывается, если на вкладке «Формула» нажать кнопку «Функция». Но об этом позже, когда будем показывать пример. 

Пишем правильно: функция имеет следующий синтаксис

=ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска])

Три обязательных параметра:

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

Три необязательных параметра. Первый – текст на тот, случай, если ничего не найдено, например «здесь рыбы нет».

Второй – тип сопоставления/совпадение (пригодится для работы с числами):

• 0 — точное (по умолчанию): если ни один элемент не найден, возвращается #Н/Д (#N/A)
• -1 — точное (с минусом): если ни один элемент не найден, возвращается следующий элемент меньшего размера,
• 1 — точное: если ни один элемент не найден, возвращается следующий более крупный элемент,
• 2 — совпадение с использованием знаков ? (любой символ), * (любое количество символов),  ~ (в сочетании с ? и * дает понять, что мы ищем именно вопросительный знак и звездочку, а не заменяем символы).

Третий необязательный – режим поиска:

• 1 или -1 —поиск с первого элемента (по умолчанию) и обратный поиск с последнего элемента (пишется с минусом).
• 2 или -2— двоичный поиск (требуется сортировка по возрастанию или по убыванию, иначе будут возвращены недопустимые результаты).

Преимущества функции ПРОСМОТРХ

Сильные стороны ПРОСОМТРХ видны в сравнении с ВПР, так что если вам вдруг незнаком вертикальный просмотр, отсылаем вас к прошлогодней статье.

Говоря о преимуществах, стоит отметить несколько пунктов (порядок не вполне отражает приоритет):

• не нужно высчитывать и указывать цифрами точный номер столбца с той информацией, которую мы хотим получить, достаточно задать диапазон (бонус: если вставить новый столбец, функция не сломается),
• в ВПР нельзя было извлечь значение из столбца, если он стоял слева – теперь извлекайте откуда хотите,
• в режиме поиска появился удобный обратный поиск с последнего значения – это просто маст хэв,
• тип сопоставления стал необязательным параметром, по умолчанию ищется точное совпадение (на один знак меньше писать – мелочь, а приятно),
• функция работает как в вертикальных столбцах, так и в горизонтальных строках,
• параметр «если ничего не найдено» заменяет отдельный перехват ошибок.

Если вы вдруг не знали, что раньше были ограничения, то не забивайте голову, если знали – вздыхайте спокойно.

Примеры использования

Откроем нашу тестовую таблицу с данными по поставкам продуктов в разные города на разные суммы в разные даты и покажем несколько примеров того, что может ПРОСМОТРХ. Первым шагом всегда заходим на вкладку «Формула», нажимаем кнопку «Функция», в открывшемся окне выбираем «ПРОСМОТРХ». 

Пример 1. Допустим у нас не тестовая таблица, а огромная, которую не охватить глазом и мы хотим вытащить из нее информацию по сливам.

На всякий случай сразу вписываем в формулу текст “не завозили” на случай, если слив не было (хотя это не обязательно). Кавычки именно такие, «ёлочки» не подходят.

=ПРОСМОТРX(J4;A1:A24;F1:F24; “не завозили”)

Результат – потраченная сумма. Заодно в следующей строке поищем ананасы, которых у нас точно не было. Результат – на экране. Все корректно. Мы для примера ищем цифры, но это вполне может быть текст, в нашем случае – название города или поставщик.

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

=ПРОСМОТРX(J4;A1:A24;F1:F24;”не завозили”;0;-1)

Пример 3. Редактор электронных таблиц был бы просто хранилищем строк и колонок, если бы не возможность составлять сложные формулы. Для примера одной строкой найдем затраты на сливы и рожки и сложим.

=СУММ(ПРОСМОТРX(J4;A1:A24;F1:F24);ПРОСМОТРX(J5;A3:A26;F3:F26))

Возможности использования формул почти безграничны. Можно вставить ПРОСМОТРX в ПРОСМОТРX и получить пакет с пакетами (шутка). Или обеспечить взаимодействие между несколькими таблицами.

Допустим у вас есть одна таблица из трех столбцов: фамилия сотрудника, процент выполнения плана в этом квартале, премия в этом квартале. Успехи подтягиваются откуда-то автоматически или вносятся руководителем, премия получается перемножением некой базы на коэффициент, остается узнать коэффициент – вот в этой ячейке будет ПРОСМОТРХ. Куда ему обращаться? Скорее всего уже есть вторая таблица, в которой задано соответствие процента выполнения плана и коэффициента. Причем скорее всего определены не все 100, а максимум пять-шесть рубежей, начиная с середины (меньше половины плана — это уж совсем грустно): 50%, 60%… 110%. А сотрудники вероятно выполняют не на круглые числа, а на 47%, 61%, 103%. Здесь помогут режимы сопоставления, которые выбирают следующее большее или меньшее значение.

Полагаем, вы уже поняли, что про ПРОСМОТРХ можно писать долго. Предлагаем перейти от теории к практике и поэкспериментировать самим.

Хотите узнать о других функциях, ставших доступными с выходом обновления «Р7-Офис» — прочитайте наши посты:

• С какой стороны колонку делать будем?
• Настрой кнопки в рабочей области

Пробуете ли вы все новинки или ограничиваетесь необходимыми по работе функциями? Используете ли нашу базу знаний с инструкциями? О чем еще вам хотелось бы почитать? Пишите удобным для вас способом в VK или в Telegram, а также оставляйте комментарии ниже. Всё прочтем и ответим!