Excel
На самом деле я в данной статье хочу рассказать про возможности не только функции ВПР, но так же хочу затронуть и ПОИСКПОЗ, как очень родственную, на мой взгляд, с ВПР функцию. У каждой из данных функций есть как свои плюсы, так и минусы. Каждый сам для себя решит, что для него плюс, а что минус. Теперь к сути. Пожалуй наиболее часто возникает необходимость сравнить данные, найти данные в другой таблице, из одной таблицы добавить данные в другу, опираясь на какой-либо критерий и т.д. Возьмем простой пример: есть у Вас две таблицы:
и Вам из первой таблицы необходимо подставить во вторую дату для каждой фамилии. В принципе ничего сложного – всего три записи. Но три записи лишь на картинке. Реально это могут быть таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. К тому же я забыл упомянуть одну мелочь, а точнее две: мало того, что ФИО расположены совершенно в разном порядке в обеих таблицах и в одной из них может быть больше записей, чем в другой(или меньше), так еще таблицы расположены на разных листах(а может даже разных книгах). Вот где ВПР будет незаменима. Вам практически ничего не надо будет делать – только записать в перую ячейку столбца С второй таблицы(туда, куда необходимо подставить даты из первой таблицы):
=ВПР($A2;Лист1!$A$2:$C$4;3;0)
Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций, выбрав в категории Ссылки и массивы ВПР и по отдельности указав нужные критерии. Теперь копируем(Ctrl+C) ячейку с формулой, выделяем все ячейки столбца С до конца данных и вставляем(Ctrl+V).
А теперь разберем поподробнее саму функцию. Прежде чем читать далее я бы настоятельно советовал прочитать в встроенной справке подробнее про данную функцию, а если уж будет непонятно – то дочитать статью. Т.к. я лично не уверен, что поясню лучше справки. Но постараюсь
Сначала основной принцип работы: ВПР ищет в первом столбце аргумента Таблица значение, указанное аргументом Искомое_значение. При нахождении нужного значения функция возвращает значение напротив найденного значения, но из столбца Таблицы, указанного аргументом Номер_столбца. С интервальным просмотром разберемся чуть позже. ВПР может вернуть только одно значений – первое, подходящее под критерий. Если искомое значение не найдено(отсутствует в таблице), то результатом функции будет #Н/Д. Не надо этого бояться – это даже полезно. Вы точно будете знать, каких записей нет и таким образом можете сравнивать две таблицы друг с другом. Иногда получается так, что Вы видите: данные есть в обеих таблицах, но ВПР выдает #Н/Д. Значит данные в Ваших таблицах не идентичны. В какой-то из них есть лишние неприметные пробелы(обычно перед значением или после), либо знаки кириллицы перемешаны со знаками латиницы. Так же #Н/Д будет, если критерии числа и в искомой таблице они записаны как текст(как правило в левом верхнем углу такой ячейки появляется зеленый треугольничек), а в итоговой – как числа. Или наоборот.
$A2 – это аргумент Искомое_значение(назовем его Критерий для краткости). Это то, что мы ищем. Т.е. для первой записи второй таблицы это будет Петров С.А. Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках – =ВПР(“Петров С.А”;Лист1!$A$2:$C$4;3;0), либо ссылку на ячейку, с данным текстом(как в примере функции). Есть небольшой нюанс: так же можно применять символы подстановки: “*” и “?”. Это очень удобно, если Вам надо найти значения лишь по части строки. Например, Вы можете не вводить полностью “Петров С.А”, а ввести лишь фамилию и знак звездочки – “Петров*”. Тогда будет выведена любая запись, которая начинается на “Петров”. Если же Вам надо найти запись, в которой в любом месте строки встречается фамилия “Петров”, то можно указать так: “*петров*”. Если хотите найти фамилию Петров и Вам неважно какие инициалы будут у имени-отчества, то здесь в самый раз такой вид: “Иванов ?.?.”. Если хотите указать знак подстановки вместе с данными из ячейки. Например, в ячейке А1 у Вас записано: Иванов. Вы точно знаете, что у Вас есть Иванов в другой таблице, но так же Вы знаете, что после фамилии есть еще слова. Тогда запишите следующим образом: A1&"*". Эта запись будет равнозначна “Иванов*”. Очень удобно, если значений много.
Лист1!$A$2:$C$4 – это аргумент Таблица. Ничего сложного – просто указываете диапазон ячеек. Только диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100. Важно помнить три вещи: первое, это Таблица всегда должна начинаться с того столбца, в котором ищем Критерий. И никак иначе. В противном случае ничего найдено не будет или результат будет совсем не тот, которого ожидаете. Второе: аргумент Таблица должен быть “закреплен”. Что это значит. Видите знаки доллара – $? Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон). Как это делается. Выделяете текст ссылки(только один диапазон – один критерий) и жмете F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы аргумент Таблица будет “съезжать” и результат опять-таки будет неверным. И последнее – таблица должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере Лист1!$A$2:$C$4 – значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца.
Номер_столбца(3) – здесь просто указываем номер столбца в аргументе Таблица, значения из которого нам необходимо подставить в качестве результата. В примере это Дата принятия – т.е. столбец №3. Если бы нужен был отдел, то указали бы 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Важно: аргумент Номер_столбца не должен превышать кол-во столбцов в аргументе Таблица. Иначе результатом формулы будет ошибка #ССЫЛКА!
Практический совет: если аргумент Таблица имеет слишком большое кол-во столбцов и Вам необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их кол-во. Можно указать так: =ВПР($A2;Лист1!$A$2:$C$4;ЧИСЛСТОЛБ(Лист1!$A$2:$C$4);0). К слову в данном случае Лист1! тоже можно убрать, как лишнее: =ВПР($A2;Лист1!$A$2:$C$4;ЧИСЛСТОЛБ($A$2:$C$4);0).
Интервальный_просмотр(0) – очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Сразу возникает вопрос: а почему в моей формуле там 0? Все очень просто – Excel в формулах может воспринимать 0 как ЛОЖЬ, а 1 как ИСТИНА. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Критерию. Это не имеет никакого отношения к знакам подстановки(“*” и “?”). Если же использовать 1 или ИСТИНА(или же вообще не указывать последний аргумент, т.к. по умолчанию он равен ИСТИНА), то…Очень долгая история. Вкратце – ВПР будет искать наиболее похожее значение, подходящее под Критерий. Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию. Обращаю внимание на то, что сортировка необходима только в том случае, если аргумент Интервальный_просмотр у Вас равен ИСТИНА или 1. Если же 0 или ЛОЖЬ – сортировка не нужна.
Кстати, если Вы заметили, то на картинке у меня попутаны отделы для ФИО. Это не ошибка записи. В прилагаемом к статье примере Вы увидите, как можно одной формулой подставить и их и даты, не меняя вручную аргумент Номер_столбца. Мне показалось, что подобный пример вполне может Вам пригодиться.
Еще частая проблема – многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это легко обойти:
=ЕСЛИ(ЕНД(ВПР($A2;Лист1!$A$2:$C$4;3;0));"";ВПР($A2;Лист1!$A$2:$C$4;3;0)))
Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше можно использовать ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР($A2;Лист1!$A$2:$C$4;3;0);"")
Обещанная ПОИСКПОЗ
Данная функция ищет значение, указанное параметром Искомое_значение в аргументе Просматриваемый_массив. А результатом функции является номер позиции найденного значения в Просматриваемом_массиве. Именно номер позиции, а не само значение. В принципе её я не буду расписывать так же подробно, потому как основные моменты ровно такие же. Если бы мы хотели применить её для таблицы выше, то она была бы такой:
=ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0)
$A2 – Искомое_значение. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки и ровно в таком же исполнении.
Лист1!$A$2:$A$4 – Просматриваемый_массив. Основное отличие от ВПР – допускается указать массив лишь с одним столбцом. Это должен быть тот столбец, в котором мы собираемся искать Искомое_значение. Если попытаться указать более одного столбца, то функция вернет ошибку.
Тип_сопоставления(0) – то же самое, что и в ВПР Интервальный_просмотр. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего. Но на этом я не буду останавливаться в данной статье.
С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ нам не подходит. По крайней мере сама по себе. Но если её использовать вместе с функцией ИНДЕКС – то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1!$A$2:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2)
Такая формула результатом вернет то же, что и ВПР.
Лист1!$A$2:$C$4 – Массив. В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не надо. К слову – данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.
Далее идут Номер_строки и Номер_столбца. Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива, которое находится в указанной строке(Номер_строки) Массива и указанном столбце(Номер_столбца), если столбцов более одного. Важно знать, что в данной связке кол-во строк в аргументе Массив функции ИНДЕКС и кол-во строк в аргументе Просматриваемый_массив функции ПОИСКПОЗ должно совпадать. И начинаться с одной и той же строки. Это в обычных случаях, если Вы не преследуете иные цели.
Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же:
=ЕСЛИ(ЕНД(ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0));"";
ИНДЕКС(Лист1!$A$2:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2))
=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$A$2:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2);"")
Есть у ИНДЕКС(ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов. Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий:
=ВПР(ПСТР($A2;1;255);ПСТР(Лист1!$A$2:$C$4;1;255);3;0)
но это формула массива. Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются – формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.
Поэтому лучше использовать такую формулу:
=ИНДЕКС(Лист1!$A$2:$C$4;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$2:$A$4=$A2;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Ну и все же я рекомендовал бы Вам прочитать подробнее про данные функции в справке.
В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР.
Скачать пример »
Так же см.:
Как заменить/удалить/найти звездочку?
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
