среда, 3 июня 2015 г.

Использование функций VLOOKUP (ВПР) и HLOOKUP (ГПР) в LibreOffice

Эта статья о двух очень часто используемых и удобных в работе функциях — VLOOKUP и HLOOKUP. Для тех, кто пользовался русской локализацией Excel, они могут быть знакомы под названиями ВПР и ГПР соответственно. Во всех табличных процессорах, не важно OpenOffice, Excel, Gnumeric или в редакторе документов GoogleDrive, эти функции работают одинаково, разницу составляют лишь отдельные специфические нюансы, так как, например, использование регулярных выражений (символов подстановки). К сожалению, многие начинающие пользователи электронных таблиц сталкиваются со сложностями в использовании этих функций, а некоторые вообще не представляют как они работают (не знают о их существовании :)). Поэтому, мы начнём с самого начала, и будем двигаться вперёд пока нам хватит сил и терпения. Разговаривать мы конечно будем о использовании VLOOKUP и HLOOKUP в LibreOffice Calc. :) Файл с примером прилагается.

Зачем нужны функции VLOOKUP и HLOOKUP?

Допустим у нас есть какая-либо таблица с данными в нескольких столбцах и строках. В первой колонке идут определения, а в соседних столбцах, в клетках рядом с определением, находятся их значения. Функция VLOOKUP ищет значение в первом столбце таблицы и возвращает (то есть показывает нам в ответ) значение из желаемого столбца той же строки. Функция HLOOKUP делает тоже самое, но ищет в первой строке, и возвращает нам значение из желаемой строки этого же столбца.
Запомнить назначение функций просто: VLOOKUP сокращение английского “Vertical Look Up”, что значит «Искать (просматривать) по вертикали», это же отразилось и в русском названии функции в Excel ВПР (Вертикальный просмотр). HLOOKUP - сокращение от “Horizontal Look Up”, что значит «Искать (просматривать) по горизонтали», а в русском Excel ГПР (Горизонтальный просмотр).

Синтаксис функций VLOOKUP и HLOOKUP

Синтаксис функций позволят нам применять их для очень большого перечня задач, при котором необходим поиск и возврат значения. Есть одно досадное ограничение, но оно не так часто важно, и обходится при помощи других функций. Но давайте пока всё по порядку.
VLOOKUP(искомое_значение; диапазон; номер_столбца;[порядок_сортировки])
HLOOKUP(искомое_значение; диапазон; номер_строки; [порядок_сортировки])
Как видите, синтаксис у них одинаков, поэтому чтобы сократить объём статьи, дальше я буду рассказывать о функции VLOOKUP. Единственное, прошу вас не забывать, что VLOOKUP будет искать значения по вертикали, а HLOOKUP по горизонтали. Приступим.
  • Первый аргумент, который мы вносим — это искомое значение. Другими словами, что мы хотим найти. Задавать его можно по разному, например мы можем прямо написать «Николаев», если нам нужно найти какие-то данные по фамилии работника, или мы можем сделать ссылку на ячейку, в которую будет вводиться то что мы хотим найти, или же, мы можем собирать значение из разных ячеек. Как мы задаём значение для поиска - без разницы, главное чтобы оно было.
  • Вторым аргументом мы указываем диапазон (таблицу) в котором будем искать. То есть где мы ищем. Тут мы тоже можем задать диапазон, написав значения просто перечисляя их (используя матрицу констант), или сделать ссылку на наш диапазон с таблицей. Важно помнить, что если мы даём ссылку на диапазон, то он должен быть неразрывный. Если диапазон разрывается, то нам его нужно сначала собрать в одну конструкцию, а затем уже использовать в формуле.
  • Третий аргумент — номер столбца для функции VLOOKUP и номер строки для HLOOKUP, из которой будет возвращаться значение. Он всегда должен быть больше 1, потому что в первом столбце (строке) мы ищем совпадение. Это и есть тот самый минус, мы ищем всегда только справа или снизу. Чаще всего мы жестко задаём номер, так как заранее знаем из какого столбца мы хотим получить значение. Но есть возможность это значение подбирать в зависимости от условия. Обратите внимание, отсчет столбцов или строк ведется не по листу, а в пределах выделенного диапазона.
  • Четвёртый аргумент — порядок сортировки, является не обязательным. Его значение может быть равно 1 или 0. 0 (иногда говорят False) — значит что диапазон не отсортирован и будет искаться точное совпадение, если такого не будет найдено, то функция вернёт ошибку. При значении 1 мы указываем, что диапазон отсортирован и это позволяет нам искать приближенные значения. Если диапазон будет не отсортирован, то получить неправильный результат очень просто. Если этот аргумент не указать, то по умолчанию он будет равен 1. Чуть позже я приведу пример по поводу этого аргумента. Пока же мы будем подразумевать, что у нас везде этот аргумент выставлен в 0 (и ставить его таковым).

Простейшее применение функции VLOOKUP

Давайте теперь сделаем пример. Начнём не с самого простого, но очень типичного применения функции VLOOKUP. Нарисуем следующую таблицу:

Таблица для поиска формулой VLOOKUP в LibreOffice Calc

Теперь в свободной ячейке пишем знак равно (=) и начинаем вводить формулу с ее первых букв «vlo», обычно на этом месте выскакивает подсказка и мы можем просто нажать Enter, LibreOffice Calc сам подставляет всю формулу, ставит круглые скобки после неё и устанавливает курсор между ними. Появляются подсказки, которые позволяют нам определить какой аргумент мы сейчас вводим. Первым аргументом введём «вторник». Обратите внимание, все слова которые мы вводим, должны быть в кавычках. Поставим точку с запятой, и подсказка нам покажет, что теперь нужно ввести матрицу. Под матрицей в данном случае подразумевается либо массив констант, либо диапазон со значениями (таблица). Массивы констант мы обсудим чуть позже, более часто используют диапазоны значений, в нашем примере это A2:B8. Как и обычно, мы можем просто выделить нужный диапазон мышкой, он подставится в формулу сам, или ввести его с клавиатуры. После этого нужно опять поставить точку с запятой и указать номер столбца, значение из которого нам нужно вернуть, для нашего примера это 2. Последняя точка с запятой и ставим 0, то есть искать будем точное совпадение. Наша функция будет выглядеть так:
=VLOOKUP("вторник";A2:B8;2;0)
И она должна вернуть «Футбол». Вы можете поиграть с этим простым примером, просто чтобы убедится, что функция по умолчанию не восприимчива к регистру букв. Иногда это плюс, а иногда минус, но знать это стоит.
Более логично, наверное, для этой таблицы будет создать поле, в котором можно вводить (или выбирать) день недели. Давайте модифицируем немного наш пример. Щёлкните на любой понравившейся вам клетке и пройдите в меню «Данные» → «Проверка...». Выбираем в списке «Разрешить» пункт «Диапазон ячеек». Нажимаем на кнопку для выбора диапазона рядом с полем «Источник» и выберем первый столбец нашей таблицы без заголовка (там где дни недели). Нажмём «OK».

Диалоговое окно проверки значений в ячейке в LibreOffice Calc

Теперь у нас есть поле со списком, в котором мы можем выбрать день недели. Осталось изменить формулу так, чтобы она принимала это значение. Для этого заменим первый аргумент ссылкой на ячейку, в которой создали поле с выпадающим списком. Должно получиться что-то похожее на эту формулу:
=VLOOKUP(D5;A2:B8;2;0)
Как вы наверное заметили, пока ваше поле пустое, ячейка с формулой выдаёт Н/А. Эта ошибка появляется всегда, когда формула не может найти значение в диапазоне для поиска, и оно появляется только если значение сортировки выставлено в 0. Все ошибки можно обрабатывать при помощи функции IFERROR, создавая значение по умолчанию. Поменяем ещё раз формулу в нашем примере:
=IFERROR(VLOOKUP(D5;A2:B8;2;0);"День не выбран")
Аргумент, который мы указали, будет появляться всегда, когда будет ошибка в формуле VLOOKUP.

Поиск по части значения аргумента в формуле VLOOKUP

Если нам нужно найти значение, но мы знаем лишь часть от него, нам нужно использовать регулярные выражения. В LibreOffice это можно сделать достаточно просто. Встаньте в свободную ячейку и введите формулу VLOOKUP, но вместо ссылки или фразы в значение для поиска введите ".*тн.*" (кавычки нужны). Полностью формула для примера будет выглядеть следующим образом:
=VLOOKUP(".*тн.*";A2:B8;2;1)
Эта формула вернёт значение «Отчетность». Вопрос про регулярные выражения очень большой, и ему стоит посвятить отдельную статью, но я дам несколько примеров, чтобы вы смогли почувствовать точку опоры. Изменим нашу формулу следующим образом:
=VLOOKUP(".*о.*";A2:B8;2;1)
В этом случае будет найден вторник, причина в том что мы указываем 2 аргумента: точку (.) что значит любой символ, и звездочку (*) — любое количество символов. Когда мы используем звёздочку, перед ней нужно всегда ставить точку, иначе будет возвращена ошибка. Это такое свойство регулярок в формулах у LibreOffice. Calc пропускает слово понедельник и выбирает Вторник, а возвращает Футбол. Если мы хотим, чтобы был найден «Понедельник», мы должны явно указать что перед «о» стоит только один символ:
=VLOOKUP(".о.*";A2:B8;2;1)
То есть убрать звездочку. Если мы хотим, чтобы было найдено слово «Суббота» мы можем поставить 4 точки:
=VLOOKUP("....о.*";A2:B8;2;1)

Но можем воспользоваться структурой повторения:
=VLOOKUP(".{4}о.*";A2:B8;2;1)
Как видите регулярные выражения тут работают прекрасно. Подробнее вы можете ознакомится с ними на странице справки, а если что-нибудь останется неясным, то спросить в комментариях к этой статье. Только помните, формула находит первое совпадение, все последующие она игнорирует.

Сборка значения для поиска из разных ячеек

Думаю это очень простая тема, но всё-таки, для людей которые только приступили к изучению LibreOffice, наверное, её стоит объяснить. Самый простой способ объединить строки это использовать символ амперсанда (&). Попробуйте, например:
="первая строка, "&"вторая строка"
Также мы можем объединять строки ссылаясь на ячейки:
=B2&", "&C2

(запятая и пробел в середине добавлены, так как обычно в строках их нет). Как видите всё просто. Тоже самое мы можем использовать в формулах, в том числе и в формулах VLOOKUP и HLOOKUP.
Таблицы для примера с объединением строк в LibreOffice Calc

В примере я разместил таблицы рядом, но на практике они могут располагаться даже в разных документах или на разных компьютерах. Но пока мы учимся, так будет удобнее. И так... мы очень хотим найти «Зарплату для работника», но в одной таблице у нас Имя и Фамилия слиты, а в другой разнесены по разным ячейкам. Ставим в С6 знак равно и вводим следующую формулу:
=VLOOKUP(A6&" "&B6;$A$11:$B$13;2;0)
Выделив диапазон для поиска не забудьте зафиксировать его (Shift+F4) знаками доллара, это позволит ему не меняться, когда мы будем его протягивать в низ. В аргументе для поиска мы объединили Имя и Фамилию через пробел, в аргументе матрицы мы указали таблицу с зарплатой, зарплата во втором столбце, поэтому цифра 2 и 0 потому что нам нужно точное совпадение. Всё, результат готов.
В принципе, как вы понимаете, мы можем работать со строкой как нам вздумается, если это не нарушает правил синтаксиса формул и приносит нам нужный результат. Мелких трюков, которые можно использовать для создания аргумента для поиска очень много, и ограничивать нас могут лишь наши воображения и решимость.
Давайте теперь перейдём к следующему аргументу — матрице.

Диапазоны для поиска в функциях VLOOKUP и HLOOKUP

Функции VLOOKUP и HLOOKUP позволяют по разному задавать диапазон. То есть, нам не нужно хранить таблицы на одном листе, они могут располагаться на разных листах и даже в разных файлах, и кроме того, они могут быть вообще виртуальными, созданными исключительно внутри самой функции. Вот и давайте начнём по порядку.

Таблица для поиска на другом листе

Это самый частый случай. Мы распределяем таблицы по разным листам, чтобы проще ориентироваться в них. Самый простой способ обратиться к таблице на другом листе — это перейти на нужный лист и выделить на нём диапазон при заполнении формулы. LibreOffice Calc сам добавит нужные значения к аргументу. Можно так же ввести название листа вручную, но обратите внимание, название листа заключено в одинарные кавычки, и если вам нужно протянуть формулу, не забудьте зафиксировать диапазон для поиска долларами (Shift+F4). Например,
=VLOOKUP(A6&" "&B6;$'Объединение строк'.$A$11:$B$13;2;0)

Диапазон для таблицы в другом файле

Это делается также просто. Откройте файл в котором находится диапазон для поиска. Вводите формулу как обычно, когда нужно будет ввести диапазон для поиска, перейдите в файл, в котором он располагается, выделите нужный диапазон, и продолжите заполнять формулу дальше. То есть мы делаем то же самое, что и в случае с листом, но только вместо листа у нас другой файл. Ссылка на другой файл имеет вид:
=VLOOKUP(A6&" "&B6;'file:///home/user_name/Документы/VPR.ods'#$'Объединение строк'.A11:B13;2;0)
для Linux, в Windows она будет немножечко другой. Обратите внимание на одинарные кавычки и знак решётка (#). Неприятным моментом тут является то, что нельзя зафиксировать диапазон по Shift+F4, нам нужно руками проставить знаки доллара перед буквами и цифрами ссылок на ячейки. Иногда проще написать его руками. Но это уже дело привычки.

Диапазон для поиска в файле на удалённом компьютере, сервере или облаке

Файл к которому вы обращаетесь может быть даже на ftp сервере, в удалённой папке или в облаке, например, на яндекс диске. При этом оформление ссылки не изменится, изменится лишь протокол доступа, вместо file:/// будет стоять http:// или ftp:// и дальше путь до файла на удалённом сервере. Проще всего, конечно открыть файл с удалённого устройства и создать ссылку как было описано раньше, но если вы уверены, то можно писать и руками. Одно лишь замечание. Если вы напрямую обращаетесь к файлу на удалённом сервере, то это сильно начинает тормозить документ. Поэтому, если для вас важна быстрота работы с файлом, то возможно будет лучше настроить синхронизацию папок с удалённым сервером и делать ссылки на локальные файлы. Ещё один момент, LibreOffice должен быть авторизован на сервере, если сервер требует авторизации. Делается это в меню «Файл» → «Открыть» при «диалогах LibreOffice». Подробнее про авторизацию на диске яндекс и googledrive можно прочитать в этой статье. Для Яндекса есть ещё один момент. Кроме той короткой ссылки на файл, которую видят все, Яндекс её преобразует в длинную (очень длинную), и если вы скачали файл через браузер, то в Загрузках её можно найти, эту ссылку можно тоже подставить в формулу. Короткие ссылки не будут вести на файл, и формула будет выдавать ошибку.

Массив констант для поиска

Последняя возможность, которая редко востребована, но всё-таки имеет место быть — это использование массивов констант. Массив констант — это заданная определённым способом матрица, с которой можно совершать действия. Тема, связанная с массивом констант в LibreOffice Calc, достаточно обширная, и я расскажу сейчас лишь ту часть, которая необходима нам для умения пользоваться функциями VLOOKUP и HLOOKUP.
Давайте начнём с примера массива:
={"правый";1|"левый";2}
Если введёте эту формулу в ячейку, то в ней отобразится «правый», но для Calc в ней будет находится таблица (матрица) из двух колонок и двух строк, в первой колонке будут слова «правый» и «левый», а во второй колонке 1 и 2. Массив констант должен быть закрыт в фигурные скобки ({}), разделителем в русской локализации (в других будут другие разделители) будет для столбцов точка с запятой (;), а для строк вертикальная черта (|). Мы можем делать очень большие матрицы таким образом, но для нашего случая имеет смысл использовать небольшой массив констант и только теми значениями, которые мы либо хотим скрыть от пользователя, либо с очень малой вероятностью будем менять. Вот так будет выглядеть формула с нашей матрицей для VLOOKUP:
=VLOOKUP("левый";{"правый";1|"левый";2};2;0)
Вместо слова «левый» в аргументе для поиска, мы можем сделать ссылку на ячейку, никто нам этого не запретит. Тут у нас только 2 столбца, первый мы и так знаем, поэтому мы ставим 2, и нам нужно точное совпадение, поэтому в конце ставим 0.
Если мы захотим использовать эту матрицу с формулой НLOOKUP нам нужно её транспонировать (перевернуть), и тогда формула примет вид:
=HLOOKUP("левый";{"правый";"левый"|1;2};2;0)
Ответ она даст такой же, просто искать она будет в строках.

Выбор между несколькими диапазонами

Хотел бы заострить ваше внимание ещё на одной возможности, которую не сразу осознаёшь, но которую приходится иногда использовать — это выбор диапазона для поиска по условию. Делается это при помощи функции IF. Общий синтаксис функции такой: IF(условие;если условие верно; если условие ложно). Простой пример:

Таблица для примера выбора диапазона в LibreOffice Calc

В примере у нас две простых таблицы. Различия между ними нужны, чтобы мы могли проверить работу формулы. В ячейке A11 мы выбираем таблицу, а в B11 строку, в C11 получаем искомое значение. Формула выглядит как:
=VLOOKUP(B11;IF(A11=1;A2:B8;D2:E8);2;0)
Попробуйте поиграть цифрами, всё работает. Это может спасти нас если наши исходные таблицы должны быть заданы по отдельности, ну, например, на разных листах.
Хотел заметить, что на некоторых сайтах по Excel предлагают функцию VLOOKUP «заворачивать» в IF, так тоже можно, но формулу VLOOKUP приходится дублировать при этом с разными диапазонами, общий вид будет более громоздкий и, естественно, её будет читать сложнее.

Назначение столбца (строки) для возвращаемого значения

В примерах выше мы использовали таблицы только из двух столбцов. Понятно, что если у нас будет таблица шире, и нам нужно будет использовать другой столбец (строку), то мы просто изменим цифру. Но вот что делать, если мы не знаем заранее, какой столбец нам нужен? Давайте рассмотрим простой пример — таблицу умножения, изобретём с вами колесо. Сделаем такие таблички:

Пример таблицы умножения в LibreOffice Calc

В ячейку М5 поместим формулу:
=VLOOKUP(M3;A1:I9;M4;0)
Первым аргументом мы указываем номер строки для вертикального просмотра (М3), затем ссылка на диапазон (A1:I9), после нам необходимо указать номер столбца, который в данном случае можно задать просто ссылкой на соответствующую ячейку (М4), ну и ставим 0 в конце. Теперь когда мы будем менять множители, формула сама будет находить нужное произведение. Пример простой, но как вы понимаете, так как нам доступны ссылки и формулы, логика поиска столбца может быть любой, ограничить нас может только наша фантазия. Давай разберём ещё один пример, более сложный, но в нём будет использованы обе формулы и VLOOKUP, и HLOOKUP.

Таблички для примера в LibreOffice Calc

Создадим такие таблицы. В нижней у нас перечень товаров с их стоимостью за штуку в разных валютах, а в верхней мы будем рассчитывать стоимость покупки товара. В полях A3 и D3 будут выпадающие списки для выбора искомого товара и валюты. Как их делать для таких примеров я объяснял выше («Данные» → «Проверка...», и так далее). Мы из выпадающих списков выбираем товар и валюту, а в ячейке B4 указываем необходимое количество. В ячейке C4 у нас стоит формула:
=B3*VLOOKUP(A3;A8:D11;HLOOKUP(D3;A6:D7;2;0);0)
Для поиска столбца мы используем формулу HLOOKUP(D3;A6:D7;2;0), которая ищет в шестой строке листа (первой строке выделенного диапазона) название валюты, указанное в ячейке D3, и возвращает нам номер столбца из седьмой строки (второй диапазона). Номер столбца подставляется в формулу VLOOKUP на место столбца для поиска. И конечный результат умножается на число указное в B3. Вот и вся магия.

Аргумент порядок сортировки

Самый загадочный и сложно запоминаемый аргумент в формулах VLOOKUP и HLOOKUP — это аргумент порядка сортировки (или в Excel его называют интервальный_просмотр). Не то не другое название не вносит ясность, и часто только путает пользователя. Давайте разберёмся с ними.
У этого аргумента может быть только 2 значения: 0 или 1. 0 — указывает, что нам нужно искать точное совпадение, но … (и тут начинаются нюансы) … точность совпадения не учитывает регистр букв, то есть, строчные и прописные буквы для функций VLOOKUP и HLOOKUP одинаковы, и об этом нужно помнить. В остальном же она действительно ищет точное совпадение.
Со значением аргумента сортировки 1 всё более запутано. Для того чтобы он правильно работал диапазон, в котором мы ищем, должен быть отсортирован. (— Ты отсортировал диапазон? — Да! TRUE.) Вот отсюда такое запутанное название, по-моему. Если вы забудете отсортировать диапазон для поиска, то можете получить весьма странные результаты. Нужен он нам тогда, когда некоторые значения могут быть пропущены, в этом случае формула вернет то значение, которое является предыдущим для искомого по порядку поиска (именно поэтому и нужно сортировать). Примеров вам? Пожалуйста:

Таблица для эксперимента с аргументом сортировки в LibreOffice Calc

Нам нужен всего один столбец, посмотреть что будет возвращать функция. В этом столбце у нас будет не хватать чисел, например, у меня не хватает 4 и 7. В ячейку D2 вносим искомое число. В ячейку D3 вводим формулу
=VLOOKUP(D2;A2:A8;1;1)
, а в D4
=VLOOKUP(D2;A2:A8;1;0)
То есть, первая формула ищет с аргументом сортировка есть, а во второй, сортировки нет. И вот результат. Первая формула возвращает нам число предыдущее искомому, а вторая ошибку, не нашла она этого числа. Можете сами поэкспериментировать, можете попробовать также работу обеих формул на не отсортированном диапазоне.

В заключении

Формулы VLOOKUP и HLOOKUP являются очень мощным инструментом. Владение ими позволяет решать очень большой перечень вопросов по работе с таблицами как с базой данных, поэтому овладение ими для человека работающего с электронными таблицами обязательно. И я постарался как мог объяснить азы, но секретов и различных хитростей ещё осталось не мало. В основном все они связаны с использованием сочетаний с дополнительными формулами. Но основа останется та же, понимание, как работает формула. Выкладываю файл с примером, чтобы вы могли посмотреть то, что я забыл рассказать в статье.
И приятных поисков вам :)

Дополнительная литература и источники