Коллеги, добрый день! В этом видео мы поговорим о двух очень полезных функциях, которые используются в самых разных задачах по поиску, обработке и переносу данных. Это функции ПОИСКПОЗ (Поиск позиции, или MATCH на английском), и функция ИНДЕКС (по-английски она тоже называется INDEX). С помощью них можно сравнивать два списка, чтобы понять, каких элементов из одного нет в другом, извлекать элементы из массивов, например, из адреса для доставки извлекать только название города, и решать многие другие задачи. Давайте посмотрим, как они работают, и начнем с функции ПОИСКПОЗ, то есть Поиск позиции, или MATCH на английском. Начнем с очень простого примера: у нас есть список, в данном случае, фруктов, список текстовых значений, и мы на его примере рассмотрим, что, собственно говоря, выполняет функция Поиск позиции. Она определяет порядковый номер элемента в диапазоне. Запрос, ее первый аргумент, — это то, что мы ищем. Например, название какого-то фрукта. Какой-то текст, в кавычках, мы ищем в столбце А, например. То есть что мы ищем — где ищем. И наконец, последний аргумент Метод поиска, как и в случае с ВПРом, должен быть равным нулю, когда вы ведете точный поиск по текстовому названию, а это 90, если не 99, процентов случаев, когда мы ищем именно текст. Нажимаем Enter, получаем значение "шесть", то есть шестым по порядку стоит в столбце А слово "мандарин". При этом, если б мы указали, например, диапазон А2:А значение бы поменялось на "пятерку", потому что мы уже анализируем другой диапазон, то есть число возвращается исходя из положения элемента в диапазоне, а не во всем столбце. Просто диапазон может быть и столбцом целиком, и каким-то отдельным набором ячеек. Итак, функция Поиск позиции возвращает порядковый номер элемента. Для чего это можно использовать? Например, для сравнения двух списков. В нашем следующем примере есть список зарегистрированных на какое-то мероприятие людей, их фамилии, имена, отчества; есть список оплативших, он несколько меньше. Мы понимаем, что они отличаются, и не все оплатили, но как быстро найти тех людей, кто зарегистрировался, но еще не оплатил? Надо понять, кто из зарегистрированных отсутствует во втором списке. Собственно говоря, функция Поиск позиции может с этим помочь, потому что она не только возвращает порядковый номер элемента, но она, естественно, будет выдавать ошибку, если элемент не найден. Именно это нам и нужно. Такая ошибка будет означать, что элемента нет во втором списке. Давайте попробуем. Напишем, введем функцию Поиск позиции, в качестве запроса указываем ячейку А2, то есть мы будем искать первого человека из списка зарегистрированных, и искать мы его будем в списке оплативших — в столбце D. При этом понятно, что дальше функцию мы протянем, и первый аргумент будет меняться на А3, А4 и так далее, то есть мы будем последовательно искать каждого из потенциальных участников в списке оплат. Последний аргумент равен нулю, то есть точный поиск. Получили, что первый человек у нас стоит вторым в списке оплативших, мы это и так визуально видим, но мы протянем функцию до конца и увидим, что для некоторых фамилий появилась ошибка #Н/Д, "нет данных", на английском было бы #N/A, и соответственно, это означает, что вот эти фамилии, имена, отчества не найдены во втором списке, а значит, что эти люди не оплатили пока свое участие. Аналогично мы могли бы проверить, все ли оплатившие есть в списке зарегистрированных, ну вдруг такая ошибка была, что кто-то оплатил, но почему-то в списке зарегистрированных его нет, и на него не запланируют там отель, или обед, или какое-то другое мероприятие. Будем искать теперь, соответственно, каждого человека из списка оплативших в столбце А, так же с последним аргументом, равным нулю, потому что речь идет про поиск текста. Протягиваем функцию двойным щелчком по маркеру, видим, что для каждого из оплативших есть номер в первом списке, то есть они все там присутствуют, на том или ином порядковом месте в столбце А. Значит, все в порядке. И также мы видим, кто не оплатил. Добавлю, что если вам хочется для большей наглядности указывать не ошибку, а какой-то текст специальный, например, "не оплачено", это можно сделать с помощью уже знакомый нам функции ЕСЛИОШИБКА. Введем перед Поиском позиции ЕСЛИОШИБКА, возьмем всю нашу функцию ПОИСКПОЗ внутрь ЕСЛИОШИБКИ, и вторым аргументом, который называется Значение при ошибке, то есть что выдавать, если будет ошибка в поиске позиции, будем выдавать текст "не оплатил", или, например, "не оплачено". Любой текст в кавычках, который вам, исходя из контекста, важен. Скопируем-вставим эту функцию, теперь вместо #Н/Д будут слова "не оплачено", и можно переименовать теперь столбец, например, в Статус, и например, поставить фильтр, и вы можете отфильтровать по значению "не оплачено", и таким образом увидеть, кто эти пять человек, кому можно написать и напомнить об оплате. Вы наверняка обратили внимание, что у нас все время был режим "ноль", то есть последний, третий аргумент равен нулю, но на самом деле у функции Поиск позиции есть и режим поиска чисел, как и у ВПРа. Отмечу, что используется он крайне редко, я на практике, наверное, не сталкивался ни разу, но важно знать, что он в целом существует, то есть вы можете искать и число в каком-то списке, и находить порядковый номер этого числа. Например, мы можем искать число 55 в столбце А, при этом если диапазон отсортирован по возрастанию, последний аргумент должен быть равен нулю. Видите — значит, число 55 на шестой позиции, а вернее, ближайшее к нему число на этой позиции. Попробуем указать, например, 110 — это, соответственно, 11 строка, число 100 ближайшее к 110, оно стоит в 10, в 11 строке. Это видно вот в всплывающей подсказке. Аналогично, по убыванию все так же, то есть, например, будем искать число 750 в столбце D, и так как он отсортирован по убыванию, нужно указать последний аргумент, равный минус единице, тут ближайшее число к 750 стоит в четвертой строке. Но, как я отметил, такой режим используется намного реже. Итак, продолжим и перейдем к функции Индекс. Она в сочетании с функцией Поиск позиции может помочь решить такую нетривиальную задачу, как замену ВПР в тех случаях, когда ВПР не работает. Пример мы видим прямо здесь. Та же форма заказа с товарами, которые нужно подтягивать из прайс-листа, по которому нужно находить цену в прайс-листе. Но обратите внимание, что прайс-лист теперь устроен немного по-другому: сначала идет код номенклатуры, видимо, из учетной системы, потом цена, и только потом название товара. И в такой ситуации ВПР работать не будет, к сожалению, потому что он всегда ведет поиск только в первом столбце диапазона, но никак не может найти значения в третьем столбце, а потом из правого, который левее, подтянуть данные. Такое вот ограничение ВПРа, но его можно обойти с помощью Поиска позиции и Индекса. Но давайте сначала познакомимся с функцией Индекс, вернемся к простому нашему примеру с фруктами. Если Поиск позиции отвечает на вопрос, каким по порядку находится элемент в списке, каким он является по порядку, — например, мандарин пятый в таком-то диапазоне — то функция Индекс решает обратную задачу. Вы ей говорите: "Принеси мне четвертый элемент из списка". То есть вы ссылаетесь на столбец и говорите: "Мне нужен четвертый элемент". И она приносит это значение, то есть она работает немножко в обратном порядке. Как это может нам пригодиться? Как я уже отметил, в нашем случае классический ВПР не сработает. Если вы попытаетесь искать название товара вот в таблице такого вида и тянуть цену из второго столбца, как мы привыкли, функция выдаст ошибку, то есть она не нашла названия кроссовок в столбце А, потому что они стоят в столбце С. Такое вот у функции ВПР ограничение, она будет работать только с левым столбцом вашей таблицы. Получается, что нам нужно решить эту задачу самостоятельно, сложной формулой. Как это делается? Нам ведь нужно найти порядковое положение товара в таблице, правильно? Мы можем сделать это с помощью Поиска позиции, будем искать название кроссовок в прайс-листе в столбце С, то есть пока мы только работаем со столбцом С и выясняем, что вот в таких-то строках стоят искомые товары. Ну, при этом для последнего ошибка, потому что его название введено неправильно. Если это исправить, все будет работать, то есть функция Поиск позиции так же чувствительна с точностью до символа к названиям, если у него последний параметр равен нулю. Это хорошо, потому что это позволяет быстро найти ошибки и их исправить. Итак, вот мы выяснили, что эти товары, цена которых нас интересует, стоят в таких-то строках. Мы же теперь можем с помощью функции Индекс обратиться к столбцу с ценой, B, и ссылаться на эти самые строки, забирать из них данные. Таким образом, в два шага мы обошли ограничения в структуре таблицы и подтянули нужную нам информацию. При этом отмечу, что промежуточные расчеты нужны, как правило, только при составлении формулы, то есть никто нам не запрещает взять функцию Поиск позиции и ссылаться на ячейку с ней, а прямо внутри Индекса ее реализовывать. И таким образом нам в принципе будет достаточно одного столбца, в котором будет происходить весь расчет, то есть мы в столбце B находим, из столбца B извлекаем цену по номеру строки нужного нам товара, который находим с помощью Поиск позиции. Кстати, главный мировой гуру Google — не Google Таблицы, а Excel, я прошу прощения, — Джон Уокенбах в своих книгах тоже рекомендует сложные формулы составлять пошагово, и только потом их собирать. Это очень удобно, особенно в начале изучения таблиц, решать задачку пошагово и только потом все объединять в одну большую формулу, которая решает какую-то комплексную задачу, как в данном примере. Итак, коллеги, мы с вами рассмотрели две функции в этом уроке — Поиск позиции, или ПОИСКПОЗ, которая помогает сравнивать списки значений между собой, и также помогает решать более сложные задачи в составе сложных формул, и функцию Индекс, которая позволяет извлечь элемент из диапазона или массива.
