Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня

Билл Джелен – MVP Microsoft, автор целого ряда книг по Excel (см., например, Сводные таблицы в Microsoft Excel 2013), ведущий сайта http://mrexcel.com/. Мне нравится, как он пишет, а также мне нравится его сайт, на котором можно за разумные деньги приобрести интересные книги. Ранее я опубликовал перевод книги Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel, приобретенной на сайте MrExcel.com. Сегодня представляю перевод небольшой книги, посвященной всецело одной функции – ВПР. Надо отметить, что это действительно мощная и интересная функция Excel, которую я использую в своей работе очень широко. И я уже писал о ней – см., например, Использование функции ВПР для извлечения данных из таблицы с двумя параметрами.

Bill ‘MrExcel’ Jelen. VLOOKUP Awesome Quick: From Your First VLOOKUP to Becoming a VLOOKUP Guru (Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). – Holy Macro! Books, 2012 – 74р.

Скачать заметку в формате Word или pdf, примеры в формате Excel

Глава 1. Ваша первая ВПР

Глава 2. Устранение проблем

Глава 3. Облегчая использование ВПР

Глава 4. Использование ВПР для решения общих проблем

Глава 5. Функции ИНДЕКС и ПОИСПОЗ

Глава 6. Продвинутая ВПР

Глава 7. Использование функций СМЕЩ и ДВССЫЛ для поиска

Бонус. Конвертация формул в значения

Введение

ВПР – это самая важная функция Excel. Да, больше людей используют функции СУММ, СРЗНАЧ и ЕСЛИ, но мощь Excel сосредоточена в ВПР. Однажды, рекрутер спросил у меня, какие требования указать при поиске финансового аналитика. Я сказал, чтобы спрашивали у кандидатов, владеют ли они ВПР? Если они умеют пользоваться ВПР, все остальное окажется проще пареной репы. [1]

Для меня все люди делятся на два типа. Группа А – это те, кто понимают ВПР, группа В – все остальные. Если вы прочтете первую главу этой книги, я гарантирую, что вы попадете в группу А. Если вы будете использовать ВПР постоянно, у вас даже не возникнет мысли, что эта функция сложна. Но, если вы слышали о ВПР лишь от преподавателя колледжа, вероятно вы находитесь под влиянием нескольких мифов о ВПР. Более того, если вы слышали о ВПР от кого-то, кто слышал о ВПР из уст преподавателя колледжа, то вы наверняка находитесь под влиянием мифов о ВПР.

Во время своих семинаров по Excel я спрашиваю, что бы слушатели хотели узнать? Два наиболее распространенных ответа – сводные таблицы и ВПР. Люди шепчут: «ты можешь просто объяснить мне на простом английском языке как использовать ВПР?» Именно этому и посвящена книга. Я разрушу мифы и покажу как запустить ВПР за несколько секунд. Затем, я расскажу вам, что может пойти не так, и как можно решить проблемы.

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

Оригинальные файлы (на английском языке), используемые в этой книги доступны для скачивания по адресу: http://www.mrexcel.com/VLAQfiles.html.

Глава 1. Ваша первая ВПР

Использование ВПР для объединения двух таблиц. IT-отдел предоставил вам данные по номенклатуре, дате и объему продаж (рис. 1.1). К сожалению, они не внесли описание товара. Если вы перезвоните и попросите их заново сделать файл, это займет три недели.

Рис. 1.1. Необходимо добавить описание товара

Вы вспоминаете, что у вас есть другой файл с наименованием, ценой и описанием товара (рис. 1.2). Не страшно, что вторая таблица содержит дополнительный (ненужный) столбец Цена. Важно, что искомый столбец (Описание) находится справа от «ключа» – столбца Наименование.

Рис. 1.2. Вторая таблица с наименованием и описанием товаров

Для удобства обработки (скорее для удобства представления рисунков) расположите обе таблицы на одном листе Excel. Оставьте по крайней мере один пустой столбец между таблицами (рис. 1.3).

Рис. 1.3. Расположите обе таблицы на одном листе

В ячейке D2 начните вводить формулу =вп… (рис. 1.4). Появится подсказка с названием и описанием функции. Дважды кликните на названии ВПР (номер 1 на рис. 1.4), а затем на значке f_x (номер 2 на рис. 1.4).

Рис. 1.4. Вызов функции ВПР

Появится окно Аргументы функции (рис. 1.5). В поле Искомое_значение укажите, какое Наименование следует искать. Т.е., Описание какого Наименования нам нужно. Мы ищем Описание для W25-6. В поле Таблица укажите, в какой области мы будем искать Описание. При этом в самой левой колонке Таблицы должны быть Наименования. Т.е. значение W25-6 должно быть среди прочих в левой колонке Таблицы (подчеркнуто красной линией на рис. 1.5). В поле Номер_столбца укажите номер столбца Описание в Таблице. Это номер 3 (1-й – Наименование, 2-й – Цена). Параметр Интервальный_просмотр = ЛОЖЬ, т.е. ВПР будет искать точное совпадение в колонке Наименование, и найдет Описание именно для W25-6. Второе возможное значение этого параметра – ИСТИНА – позволяет искать приближенное значение. Мы это изучим позже.

Рис. 1.5. Окно Аргументы функции

Если функция =ВПР(A2;$F$1:$H$29;3;ЛОЖЬ) найдет в столбце Наименование таблицы F1:H29 значение W25-6, то вернет значение из третьей колонки – Н (рис. 1.6).

Рис. 1.6. Работа ВПР

Обратите внимание на то что в поле Искомое_значение используется относительная ссылка – А2, а в поле Таблица – абсолютная $F$1:$H$29. Этим мы подготовили функцию ВПР к протягиванию по столбцу D. В ячейке D3 функция примет вид =ВПР(A3;$F$1:$H$29;3;ЛОЖЬ), т.е. значение в поле Искомое_значение изменилось на А3, а значение в поле Таблица – не изменилось $F$1:$H$29 (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel).

Протяните формулу из ячейки D2 вниз. Чтобы сделать это, выделите ячейку D2 и дважды щелкните на квадратной точке в нижнем правом углу ячейки. Это квадратная точка называется маркер заполнения. Excel протянет формулу вдоль столбца D пока не обнаружит, что данные в столбце С закончились. Проверьте, например, в ячейке D9, что формула протянулась верно.

Используйте ВПР, чтобы вычислить стоимость. Вам так понравилась ВПР, что вы захотели добавить два новых столбца в исходную таблицу, чтобы показать цену за единицу товара и стоимость по строке. Вставьте два новых столбца в первую таблицу. Обратите внимание, что формула в D2 автоматически изменилась (рис. 1.7), но по-прежнему ссылается на Таблицу, которая была перемещена в столбцы H:J.

Рис. 1.7. После вставки новых столбцов формула с ВПР автоматически изменилась

Однако, при попытке скопировать формулу из ячейки D2 в ячейку Е2, вы получите ошибку #Н/Д.

Рис. 1.8. При перетаскивании вправо формула не работает

Присмотритесь к формуле на рисунке 1.8. Она теперь пытается искать по Дате (ячейка В2), а не по Номенклатур (из А2). Вы могли бы предотвратить эту проблему, сразу написав формулу в ячейке D2 со ссылкой на $А2. Сейчас же просто измените фрагменты формулы в E2: В2 замените на А2, а номер столбца 3 – на 2, чтобы указать, что вы хотите узнать Цену, а не Описание (рис. 1.9).

Рис. 1.9. Редактирование двух символов в формуле позволяет получить цену в ячейке Е2

Стоимость (в ячейке F2) определяется по формуле =Е2*C2. Скопируйте формулы из Е2:F2 вниз на все строки диапазона.

В 99% случаев используйте в ВПР в качестве четвертого параметра ЛОЖЬ. Если вы не научный сотрудник и не бухгалтер, специализирующийся на комиссионных расчетах, во всех ваших ВПР используйте ЛОЖЬ. Возьмите себе за правило всегда использовать ЛОЖЬ. В главе 6 будет рассказано об использовании значения ИСТИНА. Но пока… только ЛОЖЬ.

Вот исключения, которые я считаю допустимыми:

• Некоторые люди предпочитают ноль вместо ЛОЖЬ. Это нормально. Это сэкономит на наборе 4 символа, но 0 и ЛОЖЬ – это лишь два обозначения одного и того же.
• Бухгалтеры, специализирующиеся на комиссионных расчетах, имеют право использовать ИСТИНА, но только в 1% случаев, когда они пытаются устранить кучу вложенных конструкций IF.
• Ученые, которые ВПР-ят все время. Однако, если вы ученый, и читаете эту книгу, отправьте ее обратно мне, и я вышлю вам книгу Gerard Verschuuren. Excel 2013 for Scientists.
• Некоторые продвинутые пользователи могут использовать трюк с аргументом Таблица, чтобы получить последнее непустое значение в строке или столбце.

Все остальные должны использовать значение ЛОЖЬ всякий раз (странно, почему Microsoft значением по умолчанию сделало ИСТИНА).

Таблица подстановки не должна быть отсортирована. Наверное, вы когда-нибудь слышали, что строки в таблице подстановки следует предварительно отсортировать. Однако, если вы используете параметр ЛОЖЬ, этого не требуется. Лишь, когда вы используете параметр ИСТИНА, тогда Таблица должна быть отсортирована.

[1] Следует заметить, что мой английский оставляет желать лучшего, поэтому я использую переводчик Yandex. Так вот, я восхитился, увидев это выражение – все остальное окажется проще пареной репы. Оцените – в оригинале было everything else was a piece of cake.