IERROR VLOOKUP альтернативы
Комбинация ЕСЛИ ОШИБКА — это старейший проверенный временем метод ВПР без ошибок в Excel. Со временем появились новые функции, обеспечивающие более простые способы выполнения той же задачи. Ниже мы обсудим другие возможные решения и когда каждое из них лучше всего применять.
ЕСЛИ ОШИБКА ВПР
Доступно в Excel 2007 и выше
Начиная с версии 2007, в Excel есть специальная функция ЕСЛИОШИБКА, которая проверяет формулу на наличие ошибок и возвращает собственный текст (или запускает альтернативную формулу) при обнаружении какой-либо ошибки.
ЕСЛИОШИБКА(ВПР(…), «text_if_error«)
Реальная формула выглядит следующим образом:
=ЕСЛИОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), «Нет»)
На первый взгляд это выглядит как укороченный аналог формулы ЕСЛИ ОШИБКА ВПР. Однако есть существенное отличие:
- ЕСЛИОШИБКА ВПР предполагает, что вам всегда нужен результат ВПР, если это не ошибка.
- ЕСЛИ ОШИБКА VLOOKUP позволяет указать, что возвращать, если ошибка и что, если ошибки нет.
Дополнительные сведения см. в разделе Использование ЕСЛИОШИБКА с функцией ВПР в Excel.
ЕСЛИ ISNA ВПР
Работает в Excel 2000 и более поздних версиях
В ситуации, когда вы хотите перехватывать только #N/A, не перехватывая никаких других ошибок, вам пригодится функция ISNA. Синтаксис такой же, как у IF IERROR VLOOKUP:
ЕСЛИ(ИСНА(ВПР(…)), «text_if_error«, ВПР(…))
Но при определенных обстоятельствах эта, казалось бы, идентичная формула может давать разные результаты:
=ЕСЛИ(ИСНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «Нет», ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))
На изображении ниже ячейка A13 содержит много пробелов в конце, из-за чего общая длина искомого значения превышает 255 символов
В результате формула вызывает ошибку #ЗНАЧ! ошибка, привлекая ваше внимание к этой ячейке и призывая разобраться в причинах. IERROR VLOOKUP в этом случае вернет «Нет», что только скроет проблему и даст абсолютно неверный результат
Когда использовать:
Эта формула прекрасно работает в ситуации, когда вы хотите отобразить некоторый текст только тогда, когда значение поиска не найдено, и не хотите маскировать основные проблемы с самой формулой ВПР, например, когда имя функции введено с ошибкой (#ИМЯ?) или не указан полный путь к книге поиска (#ЗНАЧ!).
Для получения дополнительной информации см. функцию ISNA в Excel с примерами формул.
IFNA VLOOKUP
Доступно в Excel 2013 и выше
Это современная замена комбинации IF ISNA, которая упрощает обработку ошибок #N/A.
ЕСЛИНА(ВПР(…), «text_if_error«)
Вот сокращенный эквивалент нашей формулы IF ISNA VLOOKUP:
=ЕСЛИНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), «Нет»)
Когда использовать:
Это идеальное решение для перехвата и обработки ошибок #N/A в современных версиях Excel (2013–365).
Для получения полной информации см. Функция Excel IFNA.
XLOOKUP
Поддерживается в Excel 2021 и Excel 365.
Благодаря встроенной функции «если ошибка» функция XLOOKUP — это самый простой способ поиска без ошибок #N/A в Excel. Просто введите удобный для пользователя текст в необязательный 4-й аргумент с именем если_не_найдено.
Например:
=XLOOKUP(A3, $D$3:$D$9, $E$3:$E$9, «Для»)
Ограничение: он перехватывает только ошибки #N/A, игнорируя другие типы.
Для получения дополнительной информации ознакомьтесь с функцией XLOOKUP в Excel.
Как видите, Excel предоставляет довольно много различных возможностей для обработки ошибок ВПР. Надеюсь, этот урок пролил свет на то, как их эффективно использовать. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Интервальный просмотр в функции ВПР
Пришло время обсудить последний аргумент функции ВПР. Как правило, я указываю 0, чтобы функция искала точное совпадение критериев. Но есть вариант нечеткого поиска, он называется интервальным поиском.
Рассмотрим алгоритм ВПР при выборе интервального сканирования. Во-первых (это обязательно), столбец критериев в таблице поиска должен быть отсортирован по возрастанию (если числа) или по алфавиту (если текст). ВПР просматривает список критериев выше и находит такой же, а если его нет, то ближайший наименьший к заданным критериям, т.е на одну ячейку выше (поэтому и нужна предварительная сортировка). После нахождения подходящего критерия ВПР считает указанное количество столбцов справа и берет туда содержимое ячейки, которая является результатом формулы.
Легче понять на примере. По результатам выполнения плана продаж каждый торговый агент должен получить заслуженную премию (в процентах от оклада). При выполнении плана менее чем на 100% премия не начисляется, при выполнении плана от 100% до 110% (не включая 110%) — премия 20%, от 110% до 120% (не включая 120%). — 40%, 120% и более — 60% надбавка. Данные имеют следующий вид.
Требуется замена премии по результатам выполнения планов продаж. Чтобы решить задачу, в первой ячейке введите следующую формулу:
=ВПР(B2,$E$2:$F$5,2,1)
и скопируйте вниз.
На следующем рисунке показана диаграмма того, как работает представление диапазона функции ВПР.
Джеки Чан выполнил план на 124%. Это означает, что ВПР в качестве критерия ищет ближайшее наименьшее значение во второй таблице. Это 120%. Затем он считает 2 столбца и возвращает 60% надбавки. Брюс Ли не выполнил план, поэтому его следующий самый низкий критерий — 0%.
Предлагаю посмотреть видеоурок по работе ВПР из курса «Основные функции Excel».
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).
Вот несколько примеров использования функции ВПР.
Вы можете использовать функцию ВПР для объединения нескольких таблиц в одну, если одна из них имеет общие поля. Это может быть особенно удобно, если вам нужно предоставить доступ к книге пользователям более ранних версий Excel, которые не поддерживают функции работы с данными с несколькими таблицами в качестве источников данных — путем объединения источников в одну таблицу и изменения источника данных компонента данных на новый. Таблица, функция данных может использоваться в более ранних версиях Excel (при условии, что сама функция данных поддерживается в более ранней версии).
Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.
Скопируйте таблицу с общими полями на новый лист и присвойте ей имя.
Чтобы открыть диалоговое окно Управление связями, нажмите кнопку данные > работа с данными > связей .
Для каждой из перечисленных связей обратите внимание на следующее:
Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.
Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.
Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.
Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце с помощью сведений, собранных в действии 3.
В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца (col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР (, tbl_Attorneys, 4, ложь).
В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; «Юристы»! А:Д, 4, ложь).
Продолжайте добавлять поля, пока не будут созданы все нужные поля. Если вы пытаетесь подготовить книгу с функциями данных, которые используют несколько таблиц, измените источник данных функции данных в новой таблице.
Запуск Excel в безопасном режиме
Сейф режиме можно запускать Excel запуска, не сталкиваясь с определенными программами запуска. Вы можете открыть Excel в безопасном режиме, нажав и удерживая нажатой CTRL во время запуска программы, или с помощью переключателя /safe (excel.exe /safe) при запуске программы из командной строки. При запуске Excel в безопасном режиме она обходить такие функции и параметры, как альтернативное расположение запуска, измененные панели инструментов, папка xlstart и Excel надстройки. Однако надстройки COM исключаются.
Если проблема устранена после запуска Excel в безопасном режиме, см. в этой Excel.
Если проблема не устранена после запуска Excel в безопасном режиме, переначните к следующему элементу в этом списке.
ВПР не может смотреть влево
Функция ВПР имеет такое ограничение: она не может извлекать информацию из колонок, находящихся слева от первой. Она ищет искомое значение в крайней левой колонке заданной таблицы и возвращает информацию, находящуюся правее.
Решение
Решение этой проблемы – не использовать ВПР вовсе. Используйте комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ), которая стала привычной альтернативой для ВПР. Это намного более гибкое решение
Пример, приведённый ниже, был использован для извлечения информации из колонки слева от той, по которой производится поиск:
=INDEX(B3:B13,MATCH(H3,C3:C13,0)) =ИНДЕКС(B3:B13;ПОИСКПОЗ(H3;C3:C13;0))
Схемы работы формул
Следствия для формул вида I:
- Формулы можно использовать для распределения значений по диапазонам.
- Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
- Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
- Данный вид вернёт ошибку #Н/Д только, если не найдёт значения меньше или равного искомому.
- Понять, что формула возвращает неправильные значения, в случае, если ваш массив не отсортирован, довольно затруднительно.
Примеры
В примере 1 используется просмотр названия страны в диапазоне с использованием XLOOKUP, а затем возврат кода страны телефона. Она содержит lookup_value (ячейка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Аргумент “match_mode” не включается, так как по умолчанию в аргументе ПРОСМОТРX создается точное совпадение.
Примечание: В функции ПРОСМОТРX используются массивы искомого массива и возвращаемого массива, в то время как в функции В ПРОСМОТР используется один массив таблицы, за которым следует номер индекса столбца. Эквивалентной формулой в данном случае будет формула =ВПВ.(F2;B2:D11;3;ЛОЖЬ)
———————————————————————————
В примере 2 ищется информация о сотруднике на основе его ИД. В отличие от функции В ПРОСМОТРX, она может возвращать массив с несколькими элементами, поэтому одна формула может возвращать как имя сотрудника, так и отдел из ячеек C5:D14.
———————————————————————————
В примере 3 if_not_found в предыдущий пример.
———————————————————————————
В примере 4 личный доход, введенный в ячейку E2, ищется в столбце C и находит совпадающие налоговые ставки в столбце B. Он задает if_not_found возвращает 0 (ноль), если ничего не найдено. Аргумент match_mode имеет вид 1,т. е. функция будет искать точное совпадение, а если не может найти его, возвращается следующий более крупный элемент. Наконец, search_mode аргументу назначено 1,то есть функция будет искать результаты от первого элемента до последнего.
Примечание: Столбец “lookup_array XARRAY” находится справа от return_array, в то время как ВРУП может выглядеть слева направо.
———————————————————————————
Пример 5 Вложенная функция ПРОСМОТРX используется для выполнения как вертикального, так и горизонтального совпадения. Сначала она ищет валовую прибыль в столбце B, а затем — “Кв1” в верхней строке таблицы (диапазон C5:F5), а затем возвращает значение на пересечении этих двух таблиц. Это аналогично совместному использованию функций ИНДЕКСи MATCH.
Совет: Функцию ПРОСМОТРX также можно использовать для замены функции Г ПРОСМОТР.
Примечание: Формула в ячейках D3:F3 содержит формулу =ПРОСМОТРX(D2;$B 6:$B 17;ПРОСМОТРX($C 3;$C 5:$G 5;$C 6:$G 17)).
———————————————————————————
Пример 6 использует функцию СУММи две вложенных функции ПРОСМОТРX для суммы всех значений между двумя диапазонами. В данном случае мы хотим свести значения для яблоков, бананов и груш, которые находятся между ними.
Ячейка E3 содержит формулу =СУММ(ПРОСМОТРX(B3;B6:B10;E6:E10):ПРОСМОТРX(C3;B6:B10;E6:E10))
Как это работает? Возвращает диапазон, поэтому при вычислениях формула выглядит примерно так: =СУММ($E$7:$E$9). Чтобы узнать, как это работает самостоятельно, выберите ячейку с формулой ПРОСМОТРX, аналогичной этой, выберите “Формулы” > Зависимости формул > Вычислите формулу, а затем выберите “Вычислите для шага вычисления”.
Примечание: Благодарим MVP по Microsoft Excel Билла Джилена (Bill Jelen),который предлагает этот пример.
———————————————————————————
Примеры использования
Первый простой пример – имеются 2 таблицы. В одной указываются Товары и их идентификаторы (ID). Во второй, с помощью фильтра по ID, мы хотим получить наименование товара.
После знака равно вводим ВПР, затем Enter и Fx для ввода аргументов.
Аргументы также можно вводить в соответствующей строке, перечисляя их через точку с запятой.
Искомыми будут значения, по которым будем искать совпадения. В данном случае это ячейка E1.
Для второго аргумента выделяем диапазон таблицы.
Обязательно необходимо зафиксировать (указать знаки доллара или клавиша F4 для всего диапазона) и сделать абсолютные ссылки для того чтобы диапазон не «сползал», так как в данном случае не указывается имя таблица.
Номер столбца – то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар). Для точного поиска 4 аргумент – .
Введя все значения, жмём кнопку ОК.
Теперь при изменении в фильтре номера ID будет изменяться наименование товара.
Теперь посмотрим другой пример.
Теперь нужно получить партию для каждого наименования товара по критерию Количество.
Например, для мелкой партии количество должно быть от 100 до 200, средней – 200-300 и т.д.
Искомым значением в данном случае будет количество, Таблицу выбираем диапазон Критерий – Партия (фиксируем F4). Номер столбца 2, интервальный просмотр в этом случае должен быть 1 (позволит получить ближайшее меньшее значение к искомому значению).
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР, т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ, а тратить время на изучение более сложной формулы никто не хочет.
Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: покажет эту возможность в действии.
2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.
Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:
Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.
Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.
3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.
Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:
Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:
4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.
Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как . Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.
С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.
Как сделать ВПР в Excel понятная инструкция
Чтобы понять, как пользоваться функцией ВПР в Excel, разберем ее синтаксис:
=ВПР(искомое значение; таблица; номер столбца; )
Искомое значение — значение или ссылка на ячейку, по которой будем искать значения в другой таблице. Это критерий поиска.
Таблица — ссылка на таблицу, в которой будет искать.
Номер столбца — порядковый номер столбца относительно самого левого столбца из выделенного диапазона поиска.Интервальный просмотр — указывается 0 (точный поиск) или 1 (приблизительный поиск)
Рассмотрим использование ВПР на примере:
Есть таблица с количеством гостей в отеле, необходимо понять, есть ли зависимость количества проживающих от того, насколько тепло в этом городе. Для этого нужно подтянуть значения температуры воздуха из таблицы справочника.
В качестве критерия будет использоваться название месяца.
Добавим в первую таблицу столбец Температура и напишем следующую формулу:
В примере формула ВПР ищет значение Январь из первой таблице в крайнем левом столбце второй таблицы. И подтягивает совпадающее значение из указанного столбца (в данном случае, из второго столбца).
Аргумент Интервальный просмотр задаем , чтобы функция искала точное совпадение.
Запомним, что в абсолютном большинстве случаем Интервальный просмотр ставим = 0.
Кстати, если не указать интервальный просмотр совсем, то формула не выдаст ошибку, т.к. этот аргумент необязательный. Но результат будет неожиданным, поскольку в этом случае ВПР считает не указанный Интервальный просмотр равным 1 (а это приблизительный поиск).
Не забываем добавить абсолютные ссылки (значки доллара $) к диапазону, иначе диапазон “съедет” при копировании формулы.
Итак, мы получили в нашу таблицу значение из таблицы-справочника — подтянули по критерию названия месяца. Осталось только протянуть или скопировать формулу во все ячейки этого столбца.
Как употреблять функцию «ВПР» для сопоставления данных
Показывать эту возможность будем с помощью 2-ух таблиц. Представим, что у нас на втором листе возникли обновлённые данные о сотрудниках. Наша задачка заключается в том, чтоб выяснить, что конкретно поменялось. Для этого необходимо будет создать последующее.
- Добавим 2-ой лист с буквально таковой же таблицей (копировали с помощью жарких кнопок Ctrl+C и Ctrl+V).
- Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сопоставления.
- Добавим ещё один столбец в нашу старенькую таблицу.
- Перебегаем в первую клеточку новейшего столбца и вводим там последующую формулу.
- $B$3:$B$11 – для поиска употребляются все значения первой колонки (используются абсолютные ссылки);
- Лист2! – эти значения необходимо находить на листе с обозначенным заглавием;
- $B$3:$E$11 – таблица, в какой необходимо находить (спектр ячеек);
- 4 – номер столбца в обозначенной области данных;
- ЛОЖЬ – находить четкие совпадения.
- Новенькая информация выведется в том месте, где мы указали формулу.
- Итог будет последующим.
- Сейчас продублируйте эту формулу в другие ячейки. Для этого необходимо потянуть мышкой за правый нижний угол начальной клеточки.
- В итоге мы увидим, что написанная нами формула работает корректно, так как все новейшие должности скопировались как положено.
Сейчас мы можем без усилий найти, в записях какого сотрудника произошли конфигурации.
В схожих вариантах приходится употреблять разные доп столбцы, в каких объединяют информацию с нескольких колонок. А это смотрится безобразно и не совершенно комфортно.
Вместо этого используйте индекс и сопоставление
Функции индекс и соответствие являются хорошими вариантами для многих случаев, в которых ВПР не отвечает вашим потребностям. Ключевое преимущество функции индекс/соответствие заключается в том, что вы можете найти значение в столбце в любом месте таблицы подстановки. Функция индекс возвращает значение из указанной таблицы или диапазона (в зависимости от его положения). Функция MATCH Возвращает относительное положение значения в таблице или диапазоне. Использование ИНДЕКСов и СОВПАДЕНИй в формуле для поиска значения в таблице или массиве путем указания относительной позиции значения в таблице или массиве.
Существует несколько преимуществ использования функции индекс и СОВПАДЕНИе вместо функции ВПР.
При использовании индекса и совпадения возвращаемое значение не обязательно должно находиться в том же столбце, что и столбец подстановки. Это отличается от функции ВПР, в которой возвращаемое значение должно находиться в указанном диапазоне. Как это делается? При использовании функции ВПР необходимо знать номер столбца, который содержит возвращаемое значение. Несмотря на то, что это может показаться несложным, оно может быть утомительным при наличии большой таблицы и для подсчета количества столбцов. Кроме того, при добавлении или удалении столбца в таблице необходимо пересчитать и обновить аргумент Номер_столбца . При использовании ИНДЕКСов и СОВПАДЕНИй подсчет не требуется, так как столбец подстановок отличается от столбца с возвращаемым значением.
С помощью функции индекс и совпадение можно указать либо строку, либо столбец в массиве, либо указать оба значения. Это означает, что вы можете искать значения по вертикали и по горизонтали.
Индекс и совпадение можно использовать для поиска значений в любом столбце. В отличие от функции ВПР, в которой можно найти значение в первом столбце таблицы, функция индекс и СОВПАДЕНИе будут работать, если искомое значение находится в первом столбце, в последнем или в любом месте.
Функция индекс и соответствие предлагают гибкость создания динамической ссылки на столбец, содержащий возвращаемое значение. Это означает, что вы можете добавлять столбцы в таблицу, не нарушая индекс и не подойти. С другой точки, ВПР прерывает, если нужно добавить столбец в таблицу, так как он формирует статическую ссылку на таблицу.
Индексирование и сопоставление обеспечивает большую гибкость при использовании совпадений. ИНДЕКС и СОВПАДЕНИе могут находить точное совпадение или значение, которое больше или меньше значения подстановки. ФУНКЦИЯ ВПР будет искать только наиболее подходящие значения (по умолчанию) или точное значение. ФУНКЦИЯ ВПР также предполагает, что первый столбец в массиве таблицы отсортирован в алфавитном порядке, и предположим, что таблица не настроена таким образом, функция ВПР вернет первое ближайшее соответствие в таблице, которая может не быть искомыми данными.
Синтаксис
Чтобы создать синтаксис для функции индекс и ПОИСКПОЗ, необходимо использовать аргумент array/Reference из функцией индекс и вложить в него синтаксис MATCH. Форма будет иметь следующий вид:
=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;)
Используйте ИНДЕКСы и СОВПАДЕНИЯ для замены функции ВПР из приведенного выше примера. Синтаксис будет выглядеть следующим образом:
= Индекс (возвращающий значение из значения C2: C10, которое будет соответствовать (первое), которое находится где-то в массиве B2: B10, где возвращаемое значение является первым значением, соответствующим первое).
Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 ( 100).
Другие важные особенности работы функции ВПР
Если вы хотите разбить не саму ячейку, а текст в ячейке по столбцам, то тогда вам нужно смотреть другую статью «Как разбить текст по столбцам в Excel»
1. ВПР не чувствительна к регистру
Функция ВПР не чувствительна к регистру и для нее все символы нижнего и верхнего регистра будут одинаковые. То есть, слово «Стол», «СТОЛ» и «стол» для функции ВПР будут одинаковыми.
Если вам необходимо учитывать регистр при использовании ВПР, то используйте другую функцию Excel, наприме (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр и возвращается ИСТИНУ или ЛОЖЬ при совпадении или не совпадении с искомым значением.
2. ВПР возвращает первое найденное значение
Всегда помните, что ВПР возвращает первое найденное значение, поэтому если в нашем примере в прайс листе по ошибке будет два одинаковых товара с разной ценой, то возвращаться будет только первый цена.
3. Работа функции ВПР и добавлении или удалении столбца
Как правило при написании функции ВПР мы в ручную отсчитываем столбец и указываем его в виде числа. В нашем примере с прайс листом. Вы визуально определили, что вместе с вспомогательным столбцом, цена товара находится в третьем столбце и указали цифру 3 в функцию.
Если кто-то добавит перед столбцом с ценой какую-нибудь информацию, например об остатках товара, то теперь в третьем столбце будет остаток товаров, а цена будет в 4-м столбце, но формуле это автоматически не поменяется, мы должны помнить об этом и менять информацию.
Для решения этой проблемы можно использовать функцию ПОИСКПОЗ для поиска номера столбца. В качестве искомого значения мы можем указать текст «Цена» и искать в строке — шапке таблице Прайс лист. Теперь если добавлять столбцы в таблицу Прайс лист, то функция ПОИСКПОЗ все равно найдет ячейку со словом «Цена» и вернет на номер позиции данной ячейки.
Выявление возможных проблем с надстройками
Хотя надстройки могут улучшить ваш опыт, они могут периодически мешать работе или конфликтовть с Excel. Попробуйте запустить Excel без надстроек, чтобы проверить, не решится ли проблема.
Выполните одно из указанных ниже действий.
Если вы работаете в Windows 10, выберите Начните > Все приложения > Windows Система > Выполнить > введите Excel /safe в поле Выполнить, а затем нажмите кнопку ОК.
Если вы запущены Windows 8, в меню Приложения > введите Excel /safe в поле Выполнить, а затем нажмите кнопку ОК.
Если вы работаете Windows 7, нажмите кнопку Начните > введите Excel /safe в поиске программ и файлов,а затем нажмите кнопку ОК.
Если проблема устранена, щелкните Файл > Параметры > Надстройки.
Выберите Надстройки COM и нажмите кнопку Перейти.
С очистки всех флажков в списке нажмите кнопку ОК.
Закройте приложение Excel и снова запустите его.
Если проблема не возникает при перезапуске Excel, включите надстройки по одной, пока она не будет возникать. Это позволит понять, какая надстройка вызывает проблему. Перезапускайте Excel после включения каждой надстройки.
Если отключение надстроек не решило проблему, перейдите к следующему пункту в списке.