ВПР - это функция табличного редактора ACell, с помощью которой появляется возможность искать необходимое значение в заданном диапазоне. Работает, как функция ссылок и массивов.
Чтобы применить данную функцию необходима таблица с данными.
Рассмотрим на примере исходной таблицы небольшого размера,
На практике по такой таблице точно можно определить сколько часов и сверх нормы отработал человек в месяц, но если таблица на 500 и более значений? Тут как раз помогает функция ВПР. Представим, что показанные значения на примере входят в одну большую таблицу. Нужно найти по таблице сколько часов отработал C# разработчик, для этого воспользуемся функцией:
=ВПР("C# разработчик"; C3:E7; 2; 0)Вместо параметра 0 можно использовать так же использовать обозначения (ЛОЖЬ), они равнозначны, но в этой статье будет использован 0 и 1 как логическая единица.
Получаем следующее:
Общая формула выглядит следующим образом:
=ВПР(что_ищем; где_ищем; номер_столбца; логическое_значение)
- ВПР - функция для поиска значения из массива.
- что_ищем - указывается значение в кавычках, если значение текстовое, числовое и ячеек пишется без кавычек.
- где_ищем - это интервал, он будет массивом данных среди которых и будет поиск значения.
- номер_столбца - из какого столбца по счёту вывести значение.
- 0 или 1 - логическое значение. 0 указывается в том случае, если значение ищется точно, а 1 указывается, если значение приблизительное.
Необходимо учитывать разницу между параметром 0 и 1, в искомом запросе было применено логическое значение 0 , но это потому, что мы знаем значение которое ищем: Сколько часов отработал C# разработчик в этом месяце?
Теперь с "примерным" значением, если мы хотим использовать для задачи : Есть ли среди наших работников, кто смог отработать 190 часов и какая у него сверх норма?. Как можно догадаться этот запрос будет приблизительным, потому что никто не отработал 190 часов. В этом случае важный нюанс перед работой с приблизительными значениями - таблица должна быть отсортирована по возрастанию для того "что_ищем"!, иначе алгоритм поиска просто не сможет найти приблизительное значение.
=ВПР(190; D3:E7; 2; 1)
Формулы разные не просто так, в первом случае говорится: "ищем в C, а значение берём из D".
=ВПР("C# разработчик"; C3:E7; 2; 0).
Во второй формуле говорится: "ищем в D, а значение берём из E".
=ВПР(190; D3:E7; 2; 1)
ВПР с разных листов табличного документа
Для того, чтобы в поиске использовать информацию с другого листа необходимо указать знак $ с точкой после имени листа. В данном случае лист называется "Данные" поэтому формула следующая:
=ВПР(190; $Данные.D3:E7 ; 2; 1)
Для того, чтобы ссылаться на документ с информацией локально с другого файла необходимо прописать путь с формулой. Например:
=ВПР(190; 'file:///home/user/VPR.ods'#$Данные.D3:E7; 2; 1)
Аналогичная работа происходит с сетевыми папками.
ВАЖНОЕ УТОЧНЕНИЕ.
Для корректной работы ссылок в формуле, где используется запрос на документ расположенный на сетевом ресурсе рекомендуется использовать относительные пути в настройках программы АльтерОфис.
Для включения этого значения выберете "Сервис" - "Параметры" (либо ALT+F12), далее в меню "Загрузка/Сохранение" , вкладка "Общие" раздел "Сохранение " - "Относительные пути к файлам" снимите флажок настройки.
Сохраните параметр настроек, далее сохраните документ с новыми данными, если ранее эта функция была активна. Проверьте ссылки на внешние документы, скорректируйте на актуальные.
ВПР в сложной формуле
При прочтении данной статьи и предыдущих примеров возникает потребность "более автоматизировать" и улучшить, такая возможность предоставляется дополнительными формулами внутри ВПР. Самое интересное - это функция ПОИСКПОЗ. Осуществляет поиск в таблице по значению из заголовков, заменяя параметр номер_столбца. Благодаря этой функции не обязательно знать номер столбца, а достаточно его названия.
=ВПР("C# разработчик"; C3:E7; ПОИСКПОЗ("Отработанные часы в месяце"; C2:E2; 0); 0)
Общая формула выглядит следующим образом:
=ВПР(что_ищем; где_ищем ;ПОИСКПОЗ("Заголовок"; строка_заголовков; 0); логическое_значение)
Ещё одна из интересных функций ВЫБОР. Более сложная, но очень помогающая функция при поиске значений. Позволяет осуществлять выбор из двух и более "виртуальных" таблиц. Возникает, к примеру, вопрос Есть ли кто-нибудь без сверх нормы часов? Сколько он отработал и на какой позиции?. Для этого необходимо применить формулу на нашем примере:
=ВПР(0; ВЫБОР({1;2;3}; E3:E7; C3:C7; D3:D7); 2; 0) & " - " & ВПР(0; ВЫБОР({1;2;3}; E3:E7; C3:C7; D3:D7); 3; 0)
В итоге получаем составную формулу. Для понимания разделим формулы: ВПР(0; ВЫБОР({1;2;3}; E3:E7; C3:C7; D3:D7); 2; 0) и ВПР(0; ВЫБОР({1;2;3}; E3:E7; C3:C7; D3:D7); 3; 0)
- 0 - это поиск человека без сверх нормы часов, его и необходимо найти.
- ВЫБОР({1;2;3}; E3:E7; C3:C7; D3:D7) - абсолютно одинаковая запись в двух формулах, создает структуру "виртуальных" таблиц в таком порядке, в котором и записана.
| Сверх норма | Позиция | Отработанные часы в месяце |
|---|---|---|
| 16 | Сетевой инженер | 176 |
| 5 | C# разработчик | 165 |
| 0 | Linux инженер | 160 |
| 19 | Тим лид | 179 |
| 20 | Python разработчик | 180 |
- Следующие параметры отличаются, в первой формуле написано 2, а во второй 3. Это так, потому что первая формула найдёт значение 0 из первого виртуального столбца и запишет значение из второго, а вторая формула найдёт значение 0 и запишет уже с третьего столбца.
Общая формула выглядит следующим образом:
=ВПР(что_ищем; ВЫБОР({1;2...}; столбец_для поиска; столбец_для_результата); номер_столбца; логическое_значение)
Можно ли указать в разном порядке столбцы для поиска? Да, но в том случае, если поиск нужен по другому параметру, а не по "сверх нормы" часов. Главная особенность работы с ВПР, в большинстве случаев работа происходит с первым столбцом, то есть поиск других по отношению к нему.
Другие функции внутри ВПР
Помимо рассмотренных функций существует еще несколько, в пример взята таблица выше, где столбец C - это Позиция, D - это Отработанные часы в месяце, E - Сверх норма:
| Функция | Что делает? | Пример | Результат |
|---|---|---|---|
| =ЕСЛИОШИБКА(ВПР(...); "Не найдено") | Позволяет скрыть ошибку #Н/Д и заменить её на любой текст или число | =ЕСЛИОШИБКА(ВПР("Java разработчик"; C3:E7; 3; 0); "Не найден") | Не найден |
| =ВПР(A1*1; ...) | Преобразует текстовое значение в число (например, "165" в 165) | =ВПР("165"*1; D3:D7; 1; 0) | 165 |
| =ВПР(A1&""; ...) | Преобразует числовое значение в текст (например, 165 → "165") | =ВПР(165&""; C3:C7; 1; 0) | "C# разработчик" (если бы в C был номер, искали бы текст) |
| =ВПР(СЖПРОБЕЛЫ(A1); ...) | Удаляет лишние пробелы в искомом значении (в начале, в конце, двойные внутри) | =ВПР(СЖПРОБЕЛЫ("C# разработчик "); C3:E7; 3; 0) | 5 |
Дополнительные рекомендации при использовании функции ВПР
В ACell функция ВПР, работает как с локальными файлами, так и с сетевыми папками, при использовании ссылок на сетевой ресурс рекомендуется использовать настройку "Относительные пути к файлам", о чем ранее упоминали ранее в этой статье. Если ВПР работает медленно, то необходимо сократить диапазон поиска, это может улучшить скорость обработки функции.
Совместимость с расширениями .xlsx и в обратную сторону работает корректно при соблюдении синтаксиса формулы. Также ВПР имеет несколько встроенных функций, которые могут облегчить работу.
Для быстрого доступа к описанию других функций используете встроенную справку в АльтерОфис вызов которой можно осуществить по клавише F1.