База Знаний: Функции Calc. VLOOKUP
VLOOKUP
Функция возвращает значение из столбца таблицы, в строке, удовлетворяющей условиям поиска в первом столбце.
Синтаксис функции:
| =VLOOKUP(lookupvalue; datatable; columnindex; mode) |
|---|
где:
- lookupvalue — значение (число, текст или логическое значение), разыскиваемое в первом столбце диапазона / массива. Когда значение найдено в первом столбце, VLOOKUP возвращает соответствующее значение (в той же самой строке) в столбце columnindex диапазона datatable, где для первой строки columnindex = 1;
- datatable — ссылка, которая должна включать по крайней мере два столбца;
- columnindex — номер столбца в массиве, содержащего значения, которые должны быть возвращены. Первый столбец имеет номер 1;
- mode — необязательный параметр, который указывает, отсортирован ли первый столбец в массиве в порядке возрастания. Если mode = 0 или FALSE, первый столбец не отсортирован в порядке возрастания, и ищется первое точное совпадение (поиск выполняется сверху вниз). В неотсортированных списках, значение поиска должно быть подобрано точно. Иначе функция возвратит сообщение об ошибке: #N/A. Если mode = 1, TRUE или опущен, первый столбец datatable должен быть отсортирован, с числами, в порядке возрастания располагающимися перед текстовыми значениями в алфавитном порядке. Поиск по отсортированным столбцам может выполняться намного быстрее, и функция всегда возвращает значение, даже если значение поиска не было точно подобрано, если оно находится между наименьшим и наибольшим значением отсортированного списка. VLOOKUP решает, где в первом столбце появляется lookupvalue. Если есть точное совпадение, оно соответствует найденной строке; если есть более чем одно точное совпадение, найденная строка — не обязательно самая верхняя.
Если регулярные выражения разрешены в диалоговом окне Сервис → Параметры → InfraOffice.pro Calc → Вычисления, VLOOKUP находит точное соответствие, рассматривая lookupvalue как регулярное выражение. Это имеет смысл и должно использоваться только если mode имеет значение 0 или FALSE.
Если параметр Условия поиска = и <> должны распространяться на всю ячейку в диалоговом окне Сервис → Параметры → InfraOffice.pro Calc → Вычисления — установлен, lookupvalue должно соответствовать всему тексту в ячейке; в противном случае он может соответствовать только части текста.
Соответствие всегда не зависит от регистра — значение параметра Учитывать регистр в диалоговом окне Сервис → Параметры → InfraOffice.pro Calc → Вычисления, не употребляется.
На рис. 1:
- в ячейке D2 возвращается A. Ячейка A1 в левом столбце соответствует 3. Возвращается содержимое соответствующей ячейки в 2-ом столбце (то есть, B1). Левый столбец может быть отсортирован, но это не требуется в mode = 0;
- в ячейке D4 возвращается ошибка #N/A. Нет соответствия для 4;
- в ячейке D6 возвращается C. Различие между нижним и верхним регистрами игнорируется, таким образом CAT соответствует cat;
- в ячейке D8 возвращается C, если регулярные выражения разрешены (и значение ошибки #N/A если запрещены). Точка “.” соответствует «любому одиночному символу» в регулярном выражении, таким образом c.t соответствует cat;
- в ячейке D10 возвращается C, если регулярные выражения разрешены. Заданному условию поиска соответствует cat, а так же mat, но всегда находится самое верхнее совпадение при mode = 0;
- в ячейке D12 возвращается значение ошибки #N/A, если параметр Условия поиска = и <> должны распространяться на всю ячейку — установлен и C в противном случае. Строка at не соответствует никакому целому содержимому ячеек, но оно соответствует части cat.
На рис. 2:
- в ячейке D2 возвращается A. Ячейка A1 в левом столбце соответствует 3. Левый столбец должен быть отсортирован при mode = 1, с текстовыми данными, располагающимися после чисел. Текстовая строка matter следует за mat, потому что, хотя первые буквы — одинаковые, matter имеет больше букв;
- в ячейке D4 возвращается A. 4 появилось бы между 3 и 5 в левом столбце, таким образом находится верхняя позиция;
- в ячейке D6 возвращается ошибка #N/A. 1 появилась бы перед 3 в левом столбце, но позиция выше неё в таблице отсутствует;
- в ячейке D8 возвращается B. 7 появился бы между 5 и cat в левом столбце, таким образом находится верхняя позиция;
- в ячейке D10 возвращается B. Текстовое значение apple появилось бы между 5 и cat в левом столбце, таким образом находится верхняя позиция;
- в ячейке D12 возвращается D. возвращений появился бы между циновкой и вопросом в левом столбце, таким образом положение выше найдено. Текстовое значение mate появилось бы между mat и matter в левом столбце, таким образом находится верхняя позиция;
- в ячейке D14 возвращается E. Текстовое значение rat появилась бы после matter в левом столбце, таким образом находится верхняя позиция;
- в ячейке D16 возвращается B, даже если параметр Условия поиска = и <> должны распространяться на всю ячейку — не установлен. В этом случае соответствие между at и cat игнорируется в пользу рассмотрения at как слова, которое располагается между 5 и cat.
Вот простой пример классификации для иллюстрирования того, как VLOOKUP может сделать для Вас жизнь легче. В формуле, последний аргумент 1 (TRUE) говорит InfraOffice.pro Calc, что столбец поиска упорядоченный, а точное соответствие не требуется. Возвращается самое близкое соответствие, которое не превышает значение поиска — это именно то, чего мы хотим.
Другое полезное применение функции VLOOKUP — использование OpenOffice Calc для выписки счетов. На одном листе мы имеем каталог — где мы можем хранить цены и ассортимент товаров в одном месте - рис. 3.
Функция VLOOKUP вступает в игру на листе счёта — как проиллюстрировано на рис. 4. Здесь, таблица соответствий (каталог) находится на другом листе.
На рис. 5 другое простое применение функции VLOOKUP. Рассмотрим торгового агента, которому платят комиссионные от продаж на основе скользящей шкалы.
При продажах до 50 $, платится комиссионные в 25%, между 50$ и 100$, комиссионные уменьшаются до 22.5% — и так далее. При использовании таблицы соответствий и функции VLOOKUP, правильная ставка комиссионного вознаграждения может быть применена к каждой сделке - рис. 6.
InfraOffice.pro 3.1.x
