Ответы на пост «Функция ВПР в Excel»

168

gibboustooth

MS, Libreoffice & Google docs

Ответ на пост «Функция ВПР в Excel»⁠⁠

5 лет назад

Отличный гайд, но есть неточности.

- ИСТИНА - поиск приблизительного соответствия.

Это, строго говоря, неправда. Хоть то же самое написано на сайте office.microsoft.com, но это всё равно неправда.

Значение "ИСТИНА" параметра "Тип поиска" означает, что ВПР выполнит бинарный поиск и вернёт то, что найдёт. Если массив отсортирован по возрастанию, то это действительно будет ближайшее "снизу" значение (например, для числа 123 это будет число 122, а для текста "абв" это будет "абб", при условии, конечно, что эти значения есть в массиве поиска). Если же массив не отсортирован или отсортирован не по возрастанию - алгоритм бинарного поиска либо вернёт ошибку "#Н/Д", либо всё-таки что-то найдёт. Скорее всего, совсем не то, что вы искали (даже если искомое значение есть в массиве!). Дело в том, что, во-первых, ВПР не проверяет, отсортирован массив или нет, а во-вторых, он не проверят действительно ли найденное алгоритмом бинарного поиска значение совпадает с тем, что искалось.

Функция ВПР выдаёт ошибку #Н/Д если:
...
2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск, не отсортирована по возрастанию наименований.

Это тоже неправда. Как я писал выше, ВПР может что-то найти даже в несортированном массиве.

Зачем вообще нужен алгоритм бинарного поиска в ВПР?

Дело в том, что бинарный поиск работает намного, намного быстрее, чем "обычный" (O(log n) против O(n)). Особенно эта разница будет заметна на больших массивах данных. Но пользоваться им надо с осторожностью. Чтобы отсечь неправильно найденные значения (по причине проблем с сортировкой или из-за отсутствия искомого значения в массиве), можно воспользоваться приёмом под названием "двойной ВПР":

=ЕСЛИ(
ВПР(Искомое_значение; Первый_столбец_таблицы; 1; ИСТИНА) = Искомое_значение; ВПР(Искомое_значение; Таблица_где_ищем; Номер_столбца_результатов; ИСТИНА);
НД()
)

Т.е. сначала мы проверяем, что ВПР находит то, что нужно, а только затем возвращаем найденное. Скорость работы больше обычного ВПР в 10-100 (sic!) раз. Такой разброс скорее всего связан с тем, насколько хорошо у Excel получается оптимизировать ваш "обычный" поиск.

Показать полностью

1633

Veseliy.4el

MS, Libreoffice & Google docs

Функция ВПР в Excel⁠⁠

5 лет назад

Многим знакома функция ВПР в Excel, которая ищет заданное значение в столбце указанной таблицы, и если находит, то выдаёт значение из требуемого столбца той же строки, где было совпадение.

=ВПР(Искомое_значение; Таблица_где_ищем; Номер_столбца_результатов; Тип_поиска)

Тема ВПР достаточно объёмная и интересная. Знание ВПР превращает обычного пользователя Excel в продвинутого)). Не сомневаюсь, что в сообществе имеется много знатоков этой функции, которые каждый день используют её на работе, думаю новичкам было бы интересно узнать о способах применения ВПР на практике.

Итак, начнём повышать уровень знаний в период самоизоляции))

Допустим, у нас имеются две таблицы – Заказы и прайс-лист:

Нам необходимо подставить цены для каждого товара из прайс-листа в таблицу заказов. При больших размерах таблиц, выполнить это вручную достаточно сложно и муторно, не исключены автоматические ошибки.

Примеры использования ВПР:

- подставить из штатного расписания данные о сотруднике (адрес, оклад, телефон) по его ФИО;

- подставить из каталога продукции подробную информацию о товаре по его артикулу;

- подставить из реестра договоров по номеру договора все подробности его заключения (с кем заключен, реквизиты, сумму и т.д.);

- и так далее.

Выделяем первую ячейку (D3), куда будем вводить функцию ВПР, на вкладке «Формулы» нажимаем «Вставить функцию». В категории «Ссылки и массивы» (Lookup and Reference) находим ВПР (VLOOKUP) и жмём ОК

Появится окно ввода аргументов для функции:

Заполняем поля по очереди:

- Искомое значение – наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа (слово «Вода» из ячейки B3).

- Таблица – таблица, из которой берутся искомые значения, т.е. наш прайс-лист. Чтобы при копировании функции вниз на весь столбец ссылка на прайс не сбилась, ее нужно сделать абсолютной, нажав клавишу F4.

- Номер_столбца – порядковый номер (не буква!) столбца в прайс-листе, из которого берём значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно, нам нужна цена из столбца с номером 2.

- Интервальный_просмотр – ЛОЖЬ (0) или ИСТИНА (1):

- ЛОЖЬ ~~КЛАДИ~~ - поиск точного соответствия. Если товар отсутствует в прайс-листе или написан с ошибкой, то функция выдаст ошибку #Н/Д.

- ИСТИНА - поиск приблизительного соответствия. Функция попытается найти товар с максимально похожим наименованием и выдаст цену для этого товара.

Делать это не рекомендуется т.к. функция может подставить значения цены не того предмета, которая должна быть.

После ввода всех аргументов нажимаем ОК и протягиваем введенную функцию на весь столбец.

Функция ВПР выдаёт ошибку #Н/Д если:

1. Включен точный поиск (Интервальный просмотр=0) и искомого наименования нет в Таблице.

2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск, не отсортирована по возрастанию наименований.

3. Форматы ячеек первого столбца и образцовой таблицы отличаются (числовой и текстовый). Можно использовать функции Ч и ТЕКСТ для преобразования форматов данных.

4. Функция не может найти нужного значения, потому что в коде есть пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ для их удаления:

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));$G$3:$H$19;0)

=VLOOKUP(TRIM(CLEAN(B3));$G$3:$H$19;0)

Для подавления сообщения об ошибке #Н/Д в тех случаях, когда функция не может найти точного соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR). В результате любые ошибки, создаваемые ВПР, заменятся на нули или пустые строки:

Итого получаем, 5 минут на заполнение формулы и куча сэкономленного времени и нервов, в которое имитируем бурную деятельность и читаем Пикабу)) Всем здоровья, берегите себя!

Дополнение к посту: #comment_166519497

Показать полностью 4

[моё] Microsoft Excel Таблица Полезное На заметку Длиннопост

191