Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ
Если вы будете искать функции поиска, Google покажет, что ВПР намного популярнее, чем ИНДЕКС. Это понятно, потому что для того, чтобы дать функции ИНДЕКС такую же функциональность, что и ВПР, необходимо использовать другую формулу: ПОИСК. Мне всегда было сложно одновременно осваивать две новые функции. Но они дают вам больше возможностей и гибкости при создании таблиц. Но сначала о главном.
Функция ВПР()
Предположим, у нас есть таблица с данными о сотрудниках. В первом столбце указан штатный номер сотрудника, в остальном — другие данные (ФИО, отдел и т.д.). Если у вас есть личный номер, вы можете использовать функцию ВПР для возврата определенной информации о сотруднике. Синтаксис формулы = ВПР (найти_значение; таблица; номер_столбца; [диапазон_просмотра]). Он сообщает Excel: «Найдите строку в таблице, первая ячейка которой соответствует lookup_value, и возвращает значение ячейки с порядковым номером column_number».
Но бывают ситуации, когда у вас есть имя сотрудника и вам нужно вернуть его штатный номер. На иллюстрации в ячейке A10 имя сотрудника, и вы хотите определить штатный номер в ячейке B10.
Когда ключевое поле находится справа от данных, которые вы хотите получить, функция ВПР не поможет. Если бы, конечно, можно было установить column_number -1, не было бы проблем. Распространенное решение — добавить новый столбец A, скопировать имена сотрудников в этот столбец, ввести номера сотрудников с помощью ВПР, сохранить их как значения и удалить временный столбец A.
Функция ИНДЕКС()
Чтобы решить нашу проблему за один шаг, вам необходимо использовать формулы ИНДЕКС и ПОИСК. Сложность этого подхода заключается в том, что он требует двух функций, которые вы, возможно, никогда раньше не использовали. Чтобы облегчить понимание, мы решим эту проблему в два этапа.
Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-то говорит «index», у меня в голове нет ни одной ассоциации относительно того, что делает эта функция. И он принимает три целочисленных аргумента: = ИНДЕКС (массив; номер_строки; [номер_столбца]).
Проще говоря, Excel вводит массив данных и возвращает значение на пересечении указанной строки и столбца. Как будто это было просто. Тогда формула = ИНДЕКС ($ A $ 2: $ C $ 6; 4; 2) вернет значение в ячейке B5.
В отношении нашей задачи, чтобы вернуть штатную численность сотрудника, формула должна выглядеть так = ИНДЕКС ($ A $ 2: $ A $ 6;?; 1). Это звучит глупо, но если мы заменим вопросительный знак на MATCH, у нас будет решение.
Функция ПОИСКПОЗ()
Синтаксис этой функции: = ПОИСКПОЗ (искомое_значение; поисковый_массив; [тип_соответствия]).
Он сообщает Excel: «Находит lookup_value в массиве данных и возвращает номер строки массива, в котором встречается это значение». Следовательно, чтобы узнать, в какой строке находится имя сотрудника в ячейке A10, необходимо написать формулу = ПОИСК (A10; $ B $ 2: $ B $ 6; 0). Если ячейка A10 содержит имя «Колин Фарел», то ПОИСКПОЗ вернет пятую строку массива B2: B6.
Ну вот в основном все. Функция ПОИСКПОЗ сообщает функции ИНДЕКС, в какой строке искать значение. Замените вопросительный знак в формуле ИНДЕКС формулой ПОИСК и получите эквивалент ВПР с доступными для поиска данными слева от ключевого столбца. Возьмите это так:
Сначала было непривычно, был большой соблазн вставить еще одну колонку и работать в обычном режиме. Но со временем использование функции ИНДЕКС вошло в привычку. Оказалось быстрее и требует меньше манипуляций. Поэтому в следующий раз, когда вы захотите установить для номера столбца отрицательное число в ВПР, используйте комбинацию двух странных функций ИНДЕКС и ПОИСКПОЗ для решения вашей проблемы.
Формулы с функциями ВПР и ПОИСКПОЗ для выборки данных в Excel
Таблицы, созданные в Excel, не всегда характеризуются тем фактом, что имена категорий данных необходимо определять только в заголовках столбцов. Иногда при анализе данных таблицы у нас есть возможность использовать как заголовки столбцов, так и имена строк, которые находятся в первом столбце.
Пример формулы с ВПР и ПОИСКПОЗ
Пример таблицы табеля успеваемости бонусов показан ниже:
Цель этой таблицы — найти соответствующие значения вознаграждения в диапазоне B5: K11 на основе определенной суммы дохода и магазинов с минимальными или максимальными ограничениями для выплаты премии. Сложность возникает при автоматическом определении размера бонуса, на который может рассчитывать сотрудник при превышении определенного лимита дохода. Поскольку нет четко определенной суммы страхового взноса для каждой вероятной суммы дохода. Есть только лимиты и лимиты на сумму вознаграждений для каждого магазина.
Например, нам нужна программа для автоматического определения минимально возможного бонуса для продавца в третьем магазине, чей оборот превысил уровень 370 000.
- В ячейке B14 введите сумму дохода: 370 000.
- В ячейке B15 введите номер магазина: 3.
- В ячейке B16 введите следующую формулу:
В итоге нижнего предела премии для магазина нет. 3 был определен с оборотом более 370000, но ниже 
легко видеть, что эта формула отличается от предыдущей только номером столбца, указанным в третьем аргументе функции ВПР. И, следовательно, нам просто нужно добавить +1 к значению, полученному с помощью функции ПОИСКПОЗ, поскольку сумма максимально возможного бонуса находится в столбце, следующем за минимальной суммой, которая соответствует критериям поискового запроса.
Полезные советы по формулам ВПР, ИНДЕКС и ПОИСК:
Для пошагового анализа формулы Excel любой сложности рационально использовать инструменты, встроенные в раздел: «ФОРМУЛА» — «Формульные зависимости». Например, формула Evaluate является особенно полезным инструментом для пошагового анализа вычислительного цикла».
Функция ВПР ищет значения в диапазоне слева направо. Другими словами, он анализирует ячейки только в столбцах справа от первого столбца исходного диапазона, указанного в первом аргументе функции. Если структура расположения данных в таблице не позволяет функции ВПР по этой причине покрывать все столбцы для отображения, лучше всего использовать формулу из комбинации функций ИНДЕКС и ПОИСК.
Улучшаем функцию ВПР (VLOOKUP)
«Как правильно расположить парашют?»
Выгода. Издание 2-е, переработанное.
Допустим, у нас есть следующая таблица заказов:
Нам нужно выяснить, например, какова была сумма третьего заказа Иванова или когда Петров заключил свою вторую сделку. Встроенная функция ВПР может искать только первое вхождение фамилии в таблице и нам не поможет. Вопросы типа «Кто отвечал за заказ номер 10256?» тоже останется без ответа, т.к. Встроенная функция ВПР не может отображать значения из столбцов слева от столбца поиска.
Обе эти проблемы можно решить за один раз: мы напишем нашу функцию, которая будет искать не только первое, но, в общем, n-е вхождение. Кроме того, он может искать и отображать результаты в любом столбце. Назовем это ВПР2.
Откройте редактор Visual Basic, нажав ALT + F11 или выбрав Инструменты — Макрос — Редактор Visual Basic в меню Инструменты — Макрос — Редактор Visual Basic, вставьте новый модуль (меню Вставить — Модуль) и скопируйте туда текст этой функции:
Закройте редактор Visual Basic и вернитесь в Excel.
Теперь через Insert — Function в категории User Defined вы можете найти нашу функцию VLOOKUP2 и использовать ее. Синтаксис функции следующий:
= ВПР2 (таблица; число_колонка_где_поиск; значение_поиск; N; число_столбец_от_ какое_значение_от)
Теперь ограничения стандартной функции нам не помеха:
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Примечание. Мы стремимся как можно быстрее предоставлять вам актуальные справочные материалы на вашем языке. Эта страница была переведена автоматически и может содержать неточности и грамматические ошибки. Нам важно, чтобы эта статья была вам полезна. Мы просим вас выделить пару секунд и сообщить нам, помогло ли это вам, используя кнопки внизу страницы. Для вашего удобства мы также предоставляем ссылку на оригинал (на английском языке).
Допустим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники находятся в каждом из них. Таблица огромна, так что вы можете подумать, что это непростая задача. Это достаточно просто сделать с помощью функции просмотра.
Функции ВПР и ПОИСК ПО ГОРИЗОНТУ, наряду с функциями ИНДЕКС и ПОИСК, являются наиболее полезными функциями в Excel.
Примечание. Функция «Мастер установки» больше не доступна в Excel.
Вот пример использования функции ВПР.
В этом примере ячейка B2 является первым аргументом, элементом данных, который требует, чтобы функция работала. Для функции ВПР первым аргументом является значение, которое нужно найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например «Иванов» или «21 000». Второй аргумент — это диапазон ячеек с ячейкой C2: E7, в котором вы хотите найти искомое значение. Третий аргумент — это столбец в этом диапазоне ячеек, содержащий искомое значение.
Четвертый аргумент не обязателен. Введите истину или ложь. Если вы введете ИСТИНА или оставите аргумент пустым, функция вернет приблизительное совпадение значения, указанного в первом аргументе. Если введено false, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, оставление четвертого аргумента пустым или ввод ИСТИНА дает больше гибкости.
В этом примере показано, как работает функция. Когда вы вводите значение в ячейку B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приблизительное совпадение из третьего столбца диапазона, столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Если нет, вам нужно будет ввести одно из значений в столбцы C или D, чтобы получить результат.
Если вы знакомы с функцией ВПР, ее очень легко использовать. Когда вы вводите одни и те же аргументы, поиск выполняется по строкам, а не по столбцам.
Использование функции индекс и СОВПАДЕНИе вместо функции ВПР
При использовании функции ВПР существуют некоторые ограничения: Функция ВПР может искать значение слева направо. Это означает, что столбец, содержащий значение поиска, всегда должен находиться слева от столбца, содержащего возвращаемое значение. Теперь, если ваша электронная таблица не построена таким образом, не используйте функцию ВПР. Вместо этого используйте комбинацию функций индекса и ПОИСКПОЗ.
В этом примере представлен небольшой список, в котором желаемое значение (Воронеж) отсутствует в крайнем левом столбце. Следовательно, мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1: B11 будет использоваться функция ПОИСКПОЗ. Он находится в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит население Воронежа в четвертом столбце (столбец D). Используемая формула отображается в ячейке A14.
Дополнительные примеры использования Index и MATCH вместо VLOOKUP см. На https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ Билла Гилены, Microsoft MVP.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями поиска, прежде чем работать с данными, используйте приведенный ниже образец данных.
Пример функции ВПР на работе
Скопируйте следующую информацию в пустую таблицу.
Совет: Перед вставкой данных в Excel установите ширину столбцов A на 250 пикселей и нажмите кнопку «Перенести текст» (вкладка «Главная», группа «Выравнивание «).
Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую
Прочитав статью, вы узнаете не только о том, как находить данные в одной таблице Excel и извлекать их в другую, но и о методах, которые можно использовать вместе с функцией ВПР.
Денис Батянов, как приглашенный автор, рассказывает в этом посте о том, как найти данные в одной таблице Excel и извлечь их в другую, а также раскрывает все секреты функции вертикального просмотра.
При работе в Excel очень часто необходимо найти данные в одной таблице и извлечь их в другую. Если вы все еще не знаете, как это сделать, после прочтения статьи вы не только узнаете, как это делать, но и узнаете, при каких условиях можно получить от системы максимальную производительность. Рассмотрены наиболее эффективные методы, которые следует использовать вместе с функцией ВПР.
Даже если вы годами пользуетесь функцией ВПР, с большой долей вероятности эта статья будет вам полезна и не оставит вас равнодушным. Например, будучи ИТ-специалистом, а затем ИТ-менеджером, я использую ВПР 15 лет, но справился со всеми нюансами только сейчас, когда начал обучать людей Excel на профессиональной основе.
ВПР — сокращение от вертикального просмотра. Аналогично ВПР — ПОИСК ВЕРТИКАЛЬНЫЙ. Само название функции предполагает, что она ищет в строках таблицы (по вертикали — итерация по строкам и фиксация столбца), а не в столбцах (по горизонтали — итерация по столбцам и фиксация строки). Следует отметить, что у ВПР есть сестра — гадкий утенок, который никогда не станет лебедем — это функция ВПР. LOOKUP, в отличие от VLOOKUP, выполняет горизонтальный поиск, но концепция Excel (и действительно концепция организации данных) подразумевает, что в ваших таблицах меньше столбцов и гораздо больше полос. Поэтому поиск по строкам приходится выполнять в разы чаще, чем по столбцам. Если вы слишком часто используете функцию HLO в Excel, скорее всего, вы чего-то не поняли в этой жизни.
Функция ВПР имеет четыре параметра:
= ВПР (;; [;]), здесь:
— желаемое значение (редко) или ссылка на ячейку, содержащую искомое значение (в подавляющем большинстве случаев);
— ссылка на диапазон ячеек (двумерный массив) в ПЕРВОЙ колонке (!), для которой будет производиться поиск значения параметра ;
— номер столбца в диапазоне, из которого будет возвращено значение;
— это очень важный параметр, который отвечает на вопрос, отсортирован ли первый столбец диапазона по возрастанию. Если массив отсортирован, мы указываем значение ИСТИНА или 1, в противном случае — ЛОЖЬ или 0. Если этот параметр опущен, значение по умолчанию равно 1.
Готов поспорить, многие из тех, кто знает функцию ВПР как нестандартную, после прочтения описания четвертого параметра могут почувствовать дискомфорт, поскольку привыкли видеть его в несколько иной форме — они обычно говорят о точном совпадении при поиске (ЛОЖЬ или 0) или сканирование диапазона (ИСТИНА или 1).
Теперь вы должны заставить себя прочитать следующий абзац несколько раз, пока не почувствуете смысл сказанного до конца. Здесь важно каждое слово. Примеры помогут вам понять это.
Как же конкретно работает формула ВПР
- Формула типа I. Если последний параметр опущен или задан равным 1, ВПР предполагает, что первый столбец отсортирован в порядке возрастания, поэтому поиск останавливается на строке, непосредственно предшествующей строке, значение которой больше требуемого. Если такой строки не найдено, возвращается последняя строка диапазона.
- Форма формулы II. Если последний параметр задан как 0, функция ВПР последовательно сканирует первый столбец массива и немедленно останавливает поиск при обнаружении первого точного совпадения с параметром, в противном случае возвращается код ошибки # Н / Д (# Н / Д).
Схемы работы формул
ВПР тип II
Следствия для формул вида I
- Формулы можно использовать для распределения значений по диапазонам.
- Если первый столбец содержит повторяющиеся значения и отсортирован правильно, будет возвращена последняя из строк с повторяющимися значениями.
- Если вы ищете значение, которое явно превышает то, что может содержать первый столбец, вы можете легко найти последнюю строку таблицы, которая может оказаться весьма полезной.
- Это представление вернет ошибку # N / A, только если не найдет значение, меньшее или равное желаемому.
- довольно сложно понять, что формула возвращает неверные значения, если массив не отсортирован.
Следствия для формул вида II
Если желаемое значение встречается несколько раз в первом столбце массива, формула выберет первую строку для последующего извлечения данных.
Производительность работы функции ВПР
Вы дошли до последней строки статьи. Казалось бы, ну а какая разница, если я в качестве последнего параметра укажу ноль или единицу? В принципе, все они, конечно, указывают на ноль, так как это довольно практично — вам не нужно беспокоиться о сортировке первого столбца массива, вы сразу можете увидеть, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР, вы заметите, что ВПР II работает медленно. При этом обычно все начинают думать:
- мне нужен более мощный компьютер;
- мне нужна более быстрая формула, например, многие люди знают про ИНДЕКС + ПОИСКПОЗ, что предположительно быстрее, чем жалкие 5-10%.
Подробнее: формула Excel для объединения ячеек в Excel
И мало кто думает, что как только вы начнете использовать ВПР типа I и убедитесь, что первый столбец отсортирован любым способом, скорость ВПР увеличится в 57 раз. Пишу словами — ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не 57%, а 5700%. Я достаточно достоверно проверил этот факт.
Секрет такой быстрой работы в том, что к упорядоченному массиву можно применить чрезвычайно эффективный алгоритм поиска, который называется бинарным поиском (метод деления пополам, дихотомический метод). Затем применяется ВПР I типа, а ВПР II типа выполняет поиск без какой-либо оптимизации. То же самое касается функции ПОИСКПОЗ, которая включает аналогичный параметр, и функции ПОИСК, которая работает только с отсортированными массивами и включена в Excel для совместимости с Lotus 1-2-3.
Недостатки формулы
Недостатки ВПР очевидны: во-первых, поиск выполняется только в первом столбце указанного массива, а во-вторых, только справа от этого столбца. И, как вы понимаете, может случиться так, что столбец, содержащий нужную информацию, окажется слева от столбца, который мы будем искать. Вышеупомянутая комбинация формул ИНДЕКС + ПОИСКПОЗ лишена этого недостатка, что делает ее более гибким решением для извлечения данных из таблиц, чем ВПР (VLOOKUP).
Некоторые аспекты применения формулы в реальной жизни
Диапазонный поиск
Классическим примером поиска по ассортименту является задача определения скидки в зависимости от размера заказа.
Поиск текстовых строк
Конечно, ВПР ищет не только числа, но и текст. При этом следует учитывать, что формула не различает регистр символов. Если вы используете подстановочные знаки, вы можете организовать нечеткий поиск. Есть два символа подстановки: «?» — заменить любой символ в текстовой строке, «*» — заменить любое количество любого символа.
Борьба с пробелами
Часто возникает вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу еще можно очистить, первый параметр формулы ВПР не всегда зависит от вас. Поэтому, если есть риск засорения ячеек ненужными пробелами, вы можете использовать функцию ОБРЕЗАТЬ, чтобы очистить их).
Разный формат данных
Если первый параметр функции ВПР относится к ячейке, которая содержит число, но хранится в ячейке как текст, а первый столбец массива содержит числа в правильном формате, поиск завершится ошибкой. Возможна и обратная ситуация. Проблему легко решить, переведя параметр 1 в требуемый формат:
= VLOOKUP (−− D7; Products! $ A $ 2: $ C $ 5; 3; 0) — если D7 содержит текст, а таблица содержит числа;
= ВПР (D7 & «»); Продукты 2 австралийских доллара: 5 канадских долларов; 3; 0) — и наоборот.
Кстати, перевести текст в число можно сразу несколькими способами, выберите:
- Двойное отрицание — D7.
- Умножение на D7 * 1.
- Нулевое сложение D7 + 0.
- Повышение в первой степени D7 ^ 1.
Преобразование числа в текст выполняется путем объединения с пустой строкой, что заставляет Excel преобразовывать тип данных.
Как подавить выдачу #Н/Д
Это очень удобно делать с функцией SEERROR).
Например: = SEERROR (VLOOKUP (D7; Products! $ A $ 2: $ C $ 5; 3; 0); «»).
Если ВПР возвращает код ошибки # Н / Д, ЕСЛИОШИБКА перехватит его и заменит параметр 2 (в данном случае пустую строку), а если ошибки не возникнет, эта функция будет делать вид, что ее вообще не существует, но есть только ВПР, который вернул нормальный результат.
Часто забывают сделать ссылку на массив абсолютной и при ее растяжении массив «плавает». Не забудьте использовать $ A $ 2: $ C $ 5 вместо A2: C5.
рекомендуется разместить справочную матрицу на отдельном листе книги. Он не попадет вам под ноги и будет безопаснее.
Еще лучше было бы объявить этот массив как именованный диапазон.
Многие пользователи при указании массива используют такую конструкцию, как A: C, указывая целые столбцы. Этот подход имеет право на существование, поскольку вам не нужно отслеживать, включает ли ваш массив все необходимые строки. Если вы добавляете строки на лист с исходным массивом, вам не нужно настраивать указанный диапазон как A: C. Очевидно, эта синтаксическая конструкция заставляет Excel выполнять немного больше работы, чем точное указание диапазона, но эти накладные расходы могут быть упущенным из виду. Поговорим о сотых долях секунды.
Что ж, на грани гениальности — оформление массива в виде умной таблицы.
Использование функции СТОЛБЕЦ для указания колонки извлечения
Если таблица, из которой вы извлекаете данные с помощью ВПР, имеет ту же структуру, что и таблица поиска, но просто содержит меньше строк, вы можете использовать функцию СТОЛБЕЦ () в ВПР для автоматического расчета номеров столбцов для извлечения. В этом случае все формулы ВПР будут одинаковыми (с поправкой на первый параметр, который изменяется автоматически)! Обратите внимание, что первый параметр имеет абсолютную координату столбца.
Создание составного ключа через &»»&
Если возникает необходимость одновременного поиска нескольких столбцов, вам необходимо создать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как в случае поля «Код»), а числовым, то подходила бы более удобная формула СУММЕСЛИМН, а ключ составного столбца вообще не требовался бы.
