Как сделать впр в libreoffice

ProLibreOffice

Печальный пост будет. При разгребании баг репортов в нашей багзилле я наткнулся на проблему, связанную с очень большим временем (минуты!), которое тратит Кальк на вставку формулы, которая содержит функцию VLOOKUP (ВПР), в большое количество ячеек. Также Кальк весьма задумчив, если VLOOKUP имеет в качестве аргумента огромный диапазон для проверки значений (около 100000 записей), и мы заполняем несчастные сто строк такой формулой.

Ладно, Ноэль попытался что-то сделать с первым, однако общее улучшение не очень значительно, порядка 20% по времени всего. Проблема где-то глубоко внутри LibreOffice Calc, как я понял.

Ради интереса я проверил тот же самый случай в MS Excel. Результат — просто моментальная работа. Ладно, ОК, MS потратило миллиарды баксов на разработку и оптимизацию Excel. Говорят, там внутри давно уже все сделано, как база данных, именно для скорости работы.

Но есть и ещё один софт аналогичного функционала для работы с электронными таблицами и он OpenSourse. Я говорю про Gnumeric.

Я проверил те же проблемы с VLOOKUP (ВПР) в Gnumeric (по счастью он умеет открывать ODS файлы напрямую). Результат — моментальная работа! Не хуже, чем в MS Excel!

Я конечно этой ситуацией опечален. Наличие фундаментальных проблем с производительностью LibreOffice весьма удручает. А ещё больше удручает, что никто не берется туда залезть. Как всегда, нету квалфицированных, заинтересованных кодеров. Тут студент из GSoC программы не справится, тут нужен ОЧЕНЬ грамотный и опытный специалист в С++.

Блог про LibreOffice

С Хабра:
«Как известно, знание функции VLOOKUP в MS EXCEL, достаточно чтобы в Москве стать средним аналитиком. Если человек знаком еще и с PIVOT или например знает как убрать дубликаты из списка — все двери в счастливый офисный мир перед ним настежь раскрыты.»

Счетов очень много;
Мне нужно проделать это для пяти банков и в динамике за три года, итого 15 раз.

Постановка задачи

Для расчета мне нужны данные из оборотной ведомости по счетам бухгалтерского учета кредитной организации (пример). Из нее мне нужны только левый столбец с номерами счетов и крайний правый с итоговыми суммами.

Допустим, один из требуемых показателей рассчитывается как сумма счетов: 20210 + 20305 + 30210 + 30233. На деле формула может включать десятки счетов.

Таким образом, мне нужно найти номера этих счетов и узнать сумму на них, а дальше сложить их.

Подготовка данных

Процесс быстрого импорта веб-страницы в LibreOffice Calc описан в предыдущей статье. Для удобства я удалил лишние столбцы и оставил только два столбца — с номером счета и суммой на нём.

Поиск и возврат значения (VLOOKUP)

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

Синтаксис функции следующий:
=VLOOKUP(Критерий поиска; Массив; Индекс; Порядок сортировки)

Критерий поиска — значение (число, текст или логическое значение), которое надо найти в первом столбце.

Массив — это массив данных, который должен содержать как минимум 2 столбца.

Индекс — номер столбца в массиве, содержащего значения, которые должны быть возвращены. Первый столбец имеет номер 1.

Порядок сортировки — необязательный параметр, который указывает порядок сортировки. Может принимать значение 0 и 1.

В моем случае формула будет иметь следующий вид:
=VLOOKUP(E16;A16:B142;2;0)

В столбце E у меня указаны номера счетов, которые нужно просуммировать для нахождения требуемого показателя. Значения в нём являются критерием поиска. Сам поиск производится в массиве состоящим из столбца А и В. Но поиск идет в столбце А, в котором находятся номера счетов оборотной ведомости.

В качестве индекса у меня указано число 2, это значит, что значения будут браться из второго столбца массива, т.е. из столбца В.

Обработка ошибок (IFERROR)

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

Проблема решается обертыванием предыдущей формулы в функцию IFERROR, которая проверяет наличие ошибок. В случае их отсутствия она возвращает полученное значения, в противном случае — то значение, которое мы укажем.
=IFERROR(значение; другое значение)

Обертывание выглядит следующим образом:
=IFERROR(VLOOKUP(E16;A16:B142;2;0);0)

Т.е. в случае успеха IFERROR вернет значение, которое найдет VLOOKUP. В случае ошибки вместо #Н/Д будет подставлен 0.

Ссылки по теме

База Знаний Инфра-Ресурс: Функции Calc. VLOOKUP
[Tips&Tricks] Фильтр значений
[Tips&Tricks] Отбор уникальных (неповторяющихся) значений

P.S. В русскоязычном MO Excel эти функции называются ВПР() и ЕСЛИОШИБКА(), принцип действия полностью совпадает. В англоязычном варианте MO Excel функции имеют такие же названия VLOOKUP() и IFERROR().

Информатика в экономике и управлении

Свободное программное обеспечение для бизнеса и дома.

среда, 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. Нарисуем следующую таблицу:

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

Теперь у нас есть поле со списком, в котором мы можем выбрать день недели. Осталось изменить формулу так, чтобы она принимала это значение. Для этого заменим первый аргумент ссылкой на ячейку, в которой создали поле с выпадающим списком. Должно получиться что-то похожее на эту формулу:
=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(«.о.*»;A2:B8;2;1)
Как видите регулярные выражения тут работают прекрасно. Подробнее вы можете ознакомится с ними на странице справки, а если что-нибудь останется неясным, то спросить в комментариях к этой статье. Только помните, формула находит первое совпадение, все последующие она игнорирует.

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

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

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

В примере я разместил таблицы рядом, но на практике они могут располагаться даже в разных документах или на разных компьютерах. Но пока мы учимся, так будет удобнее. И так. мы очень хотим найти «Зарплату для работника», но в одной таблице у нас Имя и Фамилия слиты, а в другой разнесены по разным ячейкам. Ставим в С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(условие;если условие верно; если условие ложно). Простой пример:

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

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

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

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

Создадим такие таблицы. В нижней у нас перечень товаров с их стоимостью за штуку в разных валютах, а в верхней мы будем рассчитывать стоимость покупки товара. В полях 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.) Вот отсюда такое запутанное название, по-моему. Если вы забудете отсортировать диапазон для поиска, то можете получить весьма странные результаты. Нужен он нам тогда, когда некоторые значения могут быть пропущены, в этом случае формула вернет то значение, которое является предыдущим для искомого по порядку поиска (именно поэтому и нужно сортировать). Примеров вам? Пожалуйста:

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

В заключении

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

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

Уроки LibreOffice
Функции электронных таблиц в LibreOffice Calc — страница в онлайн справке по LibreOffice Calc
Прямой доступ к Google Drive из LibreOffice — Статья написаная посвященная настройке авторизации LibreOffice на Яндексю.Диске и GoogleDrive

Функция ВПР (VLOOKUP) — как работает и почему, примеры, типовые ошибки и чем заменить

Общая информация о ВПР (VLOOKUP)
Функция ВПР — это одна из наиболее популярных функций ссылок и массивов. В англоязычном Excel, а также в Google Sheets, LibreOffice, OpenOffice, эта функция называется VLOOKUP.

Уровень сложности по шкале BRP ADVICE — 3 из 7 .
ВПР (VLOOKUP) позволяет найти в таблице с данными значение из нужной строки и столбца. При этом нужную строчку ВПР (VLOOKUP) найдет сам, а вот столбец вам придется указать самостоятельно.

Чтобы разобраться с ВПР (VLOOKUP), сначала надо разобраться с тем, как работают все функции ссылок и массивов.

Как работают функции ссылок и массивов
В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах вы можете ссылаться на ячейку, чтобы получить ее значение или использовать в расчетах. Обычно, в функциях вы указываете ссылки на ячейки вида A1, B17, G34, Z52. Некоторые привыкли работать со ссылками вида R1C1, R17C2, R34C7, R52C26. При этом вы указываете номер строки и букву/номер столбца с начала листа. Указав ячейку, вы даете программе точное указание, что вам нужно значение именно этой ячейки, находящейся на пересечении нужной строки и столбца. То есть выглядит все это примерно так:

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

К сожалению, ни один табличный редактор не нарисует вам такой подсказки. Все это и вам, и программе придется держать в голове.
Как указать, где находится внутренняя таблица? Для этого вам понадобится указать ее адрес при помощи стандартных ссылок вида A1 или R1C1. На картинке выше адрес внутренней таблицы — это диапазон J17:O25.
Что же происходит в этой внутренней таблице? Каждая ячейка получает новый адрес, который состоит из номера строки и номера столбца. Именно так: сначала номер строки, затем номер столбца.
Что же делают функции ссылок и массивов? Их конечная цель — получить значение по его внутреннему адресу. При этом функции ссылок и массивов могут как найти нужную строку и нужный столбец сами, так и использовать введенные пользователем значения. Для разных задач используются разные функции. Но в конечном счете получается примерно так, Excel , Google Sheets, LibreOffice, OpenOffice определяют, что вам нужна пятая строка и третий столбец и выдают значение из такой ячейки:
Как работает ВПР (VLOOKUP)
Самый простой способ разобраться с ВПР (VLOOKUP) — это рассмотреть его на примерах. Рассмотрим один пример с точным поиском, второй пример — с приблизительным поиском.
Пример 1 — точный поиск Ссылка на файл-пример приведена в конце описания этого примера. Допустим, есть таблица по сотрудникам вашей организации и их окладам. В этой таблице указан каждый сотрудник (его ФИО) и его оклад. Первый столбец этой таблицы — ФИО сотрудника, второй — оклад. То есть ваша таблица выглядит так:
ВПР (VLOOKUP) позволит вам найти оклад, указывая ФИО сотрудника. Конечно, когда у вас в таблице 5 строк и задача разовая, глазами вы найдете нужное значение очень быстро. А если у вас сотрудников 200? Или 5000? ВПР (VLOOKUP) поможет упростить вам жизнь.
Что же нужно сделать, чтобы найти оклад, например, Иванова С.А.?
Надо написать формулу =ВПР(«Иванов С.А.»;C3:D9;2;ЛОЖЬ) или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(«Иванов С.А.»;C3:D9;2;FALSE) . Кстати, в некоторых версиях Excel, вместо «;» должна использоваться «,».
После этого программа вернет вам ответ 21 000.
Что значат все аргументы ВПР (VLOOKUP)? 1. Искомое значение. В нашем примере это «Иванов С.А.» — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.
Кстати, «Иванов С.А.» у нас написано в кавычках, потому что любой текст внутри формулы должен быть написан в кавычках. Исключением являются только названия функций и именованных диапазонов. В остальных случаях всегда ставьте текст в кавычки.
2. Таблица. В нашем примере это C3:D9. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае — оклад). Посчитать вам его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый — это ФИО, второй — оклад. Значит ставим цифру 2.
4. Интервальный просмотр. В нашем примере это ЛОЖЬ (FALSE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». То есть ИСТИНА (TRUE) означает, что нам подойдет и Иванов С.А., и Иванов С.И., и Иванова О.П., и может быть кто-то еще (кого из них выберет Excel, смотрите в Примере №2). Сейчас нам нужен именно Иванов С.А., поэтому мы поставили ЛОЖЬ (FALSE). Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.
Что именно делает ВПР (VLOOKUP) в этом примере? ВПР (VLOOKUP) просматривает каждую ячейку первого столбца сверху вниз. Он смотрит в первой строке «Газоев И.В.»=»Иванов С.А.»? Видит, что нет. Тогда смотрит вторую: «Ромашкина Б.О.»=Иванов С.А.»? И так далее, пока не доходит до нужной строки. Когда ВПР (VLOOKUP) видит, что «Иванов С.А.»=»Иванов С.А.», он останавливается и запоминает номер строки, в которой это произошло. В нашем примере это 4 строка. Четвертая, потому что подсчет начинается не с начала листа, а с начала внутренней таблицы. И, наконец, ВПР (VLOOKUP) возвращает значение внутренней таблицы, которое находится на пересечении 4 строки (то, что он нашел) и 2 столбца (то, что мы указали ему аргументом). А это значение 21 000. Все, задача решена.
А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим. Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.
Пример 2 — приблизительный поиск Ссылка на файл-пример приведена в конце описания этого примера.
Допустим, у вас в компании менеджер по продажам получает премию в зависимости от объема продаж. Если продажи за месяц больше 100, то премия составляет 4% от продаж. Если продажи больше 200, то премия — 5%. Больше 300 — 6%. Больше 400 — 7%. И при любых продажах больше 500 — 8%. При продажах меньше 100, менеджер премию не получает. При помощи ВПР (VLOOKUP) можно быстро узнать, какую премию получит менеджер при его фактических продажах.
Для этого вначале нам потребуется составить таблицу с уровнями плана и ставками премии. Такая таблица будет выглядеть вот так:
Теперь мы можем найти премию Иванова С.А. в зависимости от его фактических результатов. Основным отличием от предыдущего примера является то, что фактические продажи Иванова могут быть не только ровно 100, 200, 300, 400 или 500. Они могут находиться между указанными нами значениями. Например, фактические продажи Иванова составят 350.
Для того, чтобы найти ставку премии при продажах 350, можно использовать такую формулу: =ВПР(350;C3:D8;2;ИСТИНА) или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(350;C3:D9;2;TRUE) . Не забывайте, что в некоторых версиях Excel, вместо «;» должна использоваться «,».
После этого программа вернет вам ответ 6%. Все что вам остается сделать, это перемножить продажи 350 и ставку премии 6%. Это и будет премия Иванова С.А. при продажах равных 350.

Что значат все аргументы ВПР (VLOOKUP)? 1. Искомое значение. В нашем примере это 350 — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.

Кстати, на этот раз 350 у нас написано без кавычек, потому что 350 — это число, а в кавычки мы берем только текст.
2. Таблица. В нашем примере это C3:D8. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае — ставка премии). Посчитать его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый — это «При продажах больше», второй — «ставка премии». Значит ставим цифру 2.
4. Интервальный просмотр. На этот раз нам нужна ИСТИНА (TRUE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». Мы используем ИСТИНА (TRUE), потому что нам нужно найти не только значения 100, 200 и другие, прямо указанные в таблице, но и все, что находится между ними. То есть ИСТИНА (TRUE) означает, что нам подойдет и 300, и 350, и 380 и так далее, и может быть что-то еще (какое из них выберет Excel, смотрите ниже).
Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.
Что именно делает ВПР (VLOOKUP) в этом примере? Как и в прошлый раз ВПР (VLOOKUP) последовательно просматривает все ячейки первого столбца нашей таблицы. Но на этот раз он не ищет точного соответствия, а выполняет такие проверки: 100, указанное в таблице, меньше 350? Да, — отвечает ВПР (VLOOKUP), — тогда запоминаем строку 1 и смотрим следующее значение. 200 меньше 350? Да, тогда забываем строку 1, запоминаем 2 и смотрим следующее. 300 меньше 350? Да, тогда забываем строку 2, запоминаем 3 и смотрим следующее. 400 меньше 350? Нет! Тогда возвращаемся к строке 3. И, наконец, ВПР (VLOOKUP) возвращает значение, указанное на пересечении 3 строки (то, что он нашел) и 2 столбца (то, что мы указали в функции).
Примерно вот так, можно описать логику вычислений ВПР (VLOOKUP) при работе с приблизительным поиском. Обратите внимание, что ВПР (VLOOKUP) не просматривает всю таблицу, а смотрит до тех пор пока значения не оказываются больше искомого. Иными словами, ВПР (VLOOKUP) ищет ближайшее наименьшее. Но для того, чтобы все работало правильно, вам нужно отсортировать таблицу по возрастанию значений в первом столбце.

А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим. Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.
Типичные ошибки Какие ошибки мы чаще всего встречаем при работе с ВПР (VLOOKUP)? 1. Это ошибки, связанные с неверной работой с 4 аргументом, с интервальным просмотром. Часто путаются в разнице между ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Запомните такой вопрос: «Мы ищем приблизительно такое же значение, верно?», пусть это будет вам подсказкой. Часто еще случайно не указывают четвертый аргумент вообще. И это может приводить к совершенно разным последствиям. Первый случай, когда вы работаете с ВПР (VLOOKUP) как будто в нем всего 3 аргумента. То есть в формуле вы ставите только две точки с запятой. Например, формула выглядит так: =ВПР(«Иванов С.А.»;C3:D9;2)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(«Иванов С.А.»;C3:D9;2) .

В этом случае Excel решает, что надо использовать значение 4 аргумента по умолчанию и подставляет в качестве него ИСТИНА (TRUE). Тогда вы можете найти не Иванова С.А., а кого-то чья фамилия будет примерно похожа на его (смотри как работает ВПР (VLOOKUP) с приблизительным поиском). Второй случай, когда вы ставите третью точку с запятой, но не указываете сам аргумент ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Например, формула выглядит так: =ВПР(350;C3:D8;2;)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(350;C3:D9;2;) . В этом случае ВПР (VLOOKUP) решит, что 4 аргумент равен нулю. А ноль ВПР (VLOOKUP) превратит в ЛОЖЬ (FALSE) и будет искать именно 350 в вашей таблице, а не ближайшее меньшее.

Наш совет, чтобы не допускать таких ошибок, всегда указывайте последний, четвертый, аргумент функции ВПР (VLOOKUP). И пишите его словом, не заменяйте его на цифры, так вашу формулу будет легче прочитать и понять, что же она делает.
2. Другая частая ошибка — это попытка применять ВПР (VLOOKUP), чтобы найти второе или более позднее совпадение во внутренней таблице. Например, есть таблица для ведения складского учета, в которой отражаются поступающие материалы и цены на них. Выглядеть она будет примерно так: При помощи ВПР (VLOOKUP) иногда пытаются вернуть цену в последнем поступлении. Например, сколько стоили гвозди в последней закупке. Но ВПР (VLOOKUP) вернет вам результат только для первого совпадения. Он не покажет вам данные о второй, третьей или более поздней строчке, только первое совпадение. Поэтому использовать ВПР (VLOOKUP) в такой задаче и в задачах складского учета нужно очень аккуратно. Да, в сочетании с еще несколькими функциями и промежуточными вычислениями, вы сможете получить нужный результат, но мы называем такие подходы «танцы с бубном».
3. Иногда неправильно указывают номер столбца (третий аргумент ВПР (VLOOKUP)). Номер столбца не может быть меньше 1 и не может быть больше, чем столбцов во внутренней таблице. Если номер столбца указан неверно, то ВПР (VLOOKUP) возвращает ошибку #ССЫЛКА! (#REF!). Когда вы видите такую ошибку, пересчитайте количество столбцов в таблице, которую вы указали в функции, и убедитесь, что это значение не меньше, чем номер столбца, указанный третьим аргументом функции ВПР (VLOOKUP).
Что происходит, когда ВПР (VLOOKUP) не находит значение?
В случаях, когда Excel , Google Sheets, LibreOffice, OpenOffice не может найти точное совпадение при 4 аргументе ЛОЖЬ (FALSE), ВПР (VLOOKUP) возвращает ошибку #Н/Д ( #N/A). Такую же ошибку ВПР (VLOOKUP) вернет, если вы используете приблизительный поиск и ваша таблица начинается со значения, которое больше искомого.

Как убрать ошибку #Н/Д ( #N/A)
Во-первых, проверьте адрес внутренней таблицы. Действительно искомое значение находится в первом столбце внутренней таблицы. Во-вторых, проверьте правильно ли вы указали тип интервального просмотра: ИСТИНА (TRUE) или ЛОЖЬ (FALSE). В-третьих, если вы все сделали правильно, но во внутренней таблице нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так: =ЕСЛИОШИБКА(ВПР(«Иванова А.О.»;C3:D9;2);»нет такого сотрудника»)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=IFERROR(VLOOKUP(«Иванова А.О.»;C3:D9;2);»нет такого сотрудника») .
В этом случае Excel , Google Sheets, LibreOffice, OpenOffice вместо #Н/Д (#N/A) будут писать, что такого сотрудника нет. Это поможет сделать ваши расчеты более информативными и надежными.
Чем дополнить и заменить ВПР (VLOOKUP)?
Дополнить ВПР (VLOOKUP) можно функцией ЕСЛИОШИБКА (IFERROR) и функцией ПОИСКПОЗ (MATCH). В особо сложных случаях возможно использование в комбинации с функцией СМЕЩ (OFFSET). Основные варианты замены функции ВПР (VLOOKUP): функция ПРОСМОТР (LOOKUP), комбинация функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), а также в некоторых случаях — функция ГПР (HLOOKUP), функция СУММЕСЛИ (SUMIF). При использовании функции ВПР (VLOOKUP) большую автоматизацию и надежность вашим файлам может добавить проверка данных и выпадающие списки, а также условное форматирование.
Быстрые ссылки на файлы-примеры: Пример 1 — применение ВПР с точным поиском
Пример 2 — применение ВПР с приблизительным поиском

Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel.

Понравилась статья? Узнайте больше раньше других: заходите на нашу страницу в ВКонтакте и подписывайтесь на новости.

Желаем вам успешной работы!
Ваш Виктор Рыбцев и команда Учебного центра BRP ADVICE.