No Image

Функция впр в libreoffice

СОДЕРЖАНИЕ
4 просмотров
10 марта 2020

Общая информация о ВПР (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) поможет упростить вам жизнь.

Что же нужно сделать, чтобы найти оклад, например, Иванова С.А.?

Надо написать формулу

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

Кстати, в некоторых версиях 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, можно использовать такую формулу:

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

Не забывайте, что в некоторых версиях 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 аргумента. То есть в формуле вы ставите только две точки с запятой. Например, формула выглядит так:

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

В этом случае Excel решает, что надо использовать значение 4 аргумента по умолчанию и подставляет в качестве него ИСТИНА (TRUE). Тогда вы можете найти не Иванова С.А., а кого-то чья фамилия будет примерно похожа на его (смотри как работает ВПР (VLOOKUP) с приблизительным поиском).

Второй случай, когда вы ставите третью точку с запятой, но не указываете сам аргумент ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Например, формула выглядит так:

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

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

Быстрые ссылки на файлы-примеры:

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

Читайте также:  Что лучше консоль или пк для игры

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

Советы, трюки, хитрости, инструкции, руководства

Страницы

2 октября 2014 г.

LibreOffice Calc: Поиск и возврат значений (функции VLOOKUP и IFERROR)

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

Эта статья является продолжением статьи «Импорт таблицы с сайта». Для диплома мне нужно вычислить ряд показателей. Некоторые из них считаются простым сложением счетов из оборотной ведомости. Но тут две проблемы:

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

Не буду приводить все свои расчеты, покажу основную суть на небольшом примере.

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

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

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

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

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

Поиск и возврат значения (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.

LibreOffice — это свободный офисный пакет с открытым исходным кодом, доступный для Windows, Linux и Mac. Он включает в себя шесть приложений с широкой функциональностью для основных типов документов.

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

Новости

Вышел в свет LibreOffice 5.0

Анонсирован LibreOffice 4.4.5

Формат Open Document 1.2 прошел стандартизацию ISO

Статьи

Удаление «лишних» символов в LibreOffice Writer

Аналог WordArt в LibreOffice Writer

Подсчёт значений в LibreOffice Calc

Форум

  • символ переноса на другую строчку или символ enter
    [Palich, 12 Октября 2018, 10:43:54, +0]
  • Нереальные тормоза при попытке перейти в конец документа.
    [timur, 07 Июня 2018, 12:39:41, +1]
  • Установка защиты от редактирования на документы с помощью макроса
    [lkudryashov, 06 Июня 2018, 08:00:31, +0]

Writer

Современный текстовый редактор Writer обладает всеми необходимыми возможностями настольных издательских инструментов. Он прост для изучения, но при этом достаточно функционален, чтобы с нуля оформить книгу, включающую, помимо текста, изображения, оглавления и многое другое.

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

Impress

Отличный инструмент для создания эффектных мультимедийных презентаций, включающих 2D и 3D-графику, переходы, анимации и многое другое. Шаблоны страниц упростят подготовку ваших материалов. А загрузив уже готовые шаблоны, вы можете сэкономить ещё больше времени.

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

Мощная настольная СУБД, ориентированная на широкий круг пользователей из различных отраслей. Вы можете формировать новые базы данных с помощью встроенного мастера, а также создавать таблицы, запросы, формы, отчёты и многое другое.

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

«>

Комментировать
4 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock detector