Tw-city.info

IT Новости
37 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция впр в опен офисе

Блог про LibreOffice

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

Страницы

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.

ВПР (VLOOKUP)

Производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки.

Примеры использования

ВПР(10003; A2:B26; 2; ЛОЖЬ)

Синтаксис

ВПР(запрос; диапазон; индекс; [отсортировано])

запрос – критерий, по которому выполняется поиск ( например, 42 , » кошка» или I24 ).

диапазон – диапазон, в первом столбце которого будет производиться поиск по запросу .

индекс – номер столбца (от начала диапазона ), из которого нужно взять искомое значение.

  • Если индекс не попадает в интервал от 1 до числа столбцов в диапазоне , возвращается ошибка #ЗНАЧЕН! .

отсортировано – по умолчанию [ ИСТИНА ]. Указывает, отсортированы ли данные в столбце, в котором производится поиск (первом столбце из указанного диапазона). Как правило, рекомендуемое значение – ЛОЖЬ.

Если для параметра отсортировано указать значение ЛОЖЬ (рекомендуется), возвращается только точное совпадение. Если таких совпадений несколько, возвращается значение для первого из них. Если точных совпадений нет, возвращается ошибка #Н/Д .

Если для параметра отсортировано не выбрано значение или выбрано ИСТИНА , возвращается значение, ближайшее к запрошенному (меньшее либо равное). Если все значения в столбце поиска больше указанного, возвращается ошибка #Н/Д .

Примечания

Если для параметра отсортировано не выбрано значение или выбрано ИСТИНА , а первый столбец диапазона не отсортирован, функция может вернуть неверное значение. Если результаты ВПР вызывают сомнение, убедитесь, что для последнего параметра указано значение ЛОЖЬ. Такая конфигурация подходит для большинства случаев. Если же данные в столбце отсортированы и вы хотите оптимизировать поиск, укажите значение ИСТИНА.

Читать еще:  Не активируется офис

При поиске чисел или дат убедитесь, что первый столбец в диапазоне не отсортирован по текстовым значениям. К примеру, правильно отсортированные числа должны располагаться в порядке (1; 2; 10; 100). Если их отсортировать как текст, порядок будет иным: (1; 10; 100; 2). При неверном типе сортировки функция может вернуть неправильное значение.

Запросы на основе регулярных выражений НЕ поддерживаются. В этих случаях нужно использовать функцию QUERY .

ВПР работает быстрее, если данные в диапазоне отсортированы и для параметра отсортировано указано значение ИСТИНА .

Также для поиска можно использовать шаблоны с подстановочными знаками. Знаки «?» и «*» подставляются в запрос . При этом знак вопроса обозначает один символ, а звездочка – набор символов. Если вы хотите найти вопросительный знак или звездочку в тексте, введите перед искомым символом тильду (

), чтобы указать, что это не подстановочный знак. А если нужно найти тильду, введите ещё одну.

Похожие функции

QUERY : Выполняет запросы на базе языка запросов API визуализации Google.

ГПР : Производит поиск по первой строке диапазона и возвращает значение из найденной ячейки.

Примеры

ВПР ищет в первом столбце номер студента и возвращает соответствующую оценку.

ВПР ищет в первом столбце приблизительное значение дохода (параметр отсортировано имеет значение ИСТИНА ) и возвращает соответствующую ему ставку налога.

Если по запросу найдено несколько равных значений, ВПР возвращает первое из них.

Функция ВПР в LibreOffice, чтобы выявить и сопоставить два значения 1 этикетку

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

Информация на двух листах различна, и фактически каждое значение на одном листе будет повторяться дважды на другом, как таковое, мы будем иметь его, что, по сути, первый лист Sheet 1 будет выглядеть более или менее так:

а второй, Sheet 2 , выглядит так:

как вы можете видеть, для столбца submission # в розовом листе Sheet 1 значение 4 повторяется дважды в Sheet 2 , фиолетовом, т. е. первые две записи характеризуются значением 4 в столбце submission # .

Первоначально я думал, что я мог бы, но значения, все значения, от фиолетового листа, Sheet 2 , вдоль стороны связанного индекса submission # розового листа Sheet 1 , таким образом:

Но он также может работать, что столбец submission # со значением 4 из розового листа соединяется дважды с Sheet 2 , фиолетовым листом.

Как я уже сказал, я боролся с кнопкой «Function generator», расположенной на приборной панели LibreOffice, безрезультатно.

Как структурировать такую функцию?

1 Ответ

Я работаю с двумя помещениями здесь (поправьте меня, если я ошибаюсь):

  1. Строки в Sheet2 сортируются по представлению #.
  2. В листе 2 всегда есть 2 записи для каждой записи в листе 1. Ни больше, ни меньше.

Если обе мои предпосылки верны, вы можете использовать VLOOKUP для получения первой записи и INDIRECT и MATCH для получения второй, например:

Для первой записи (столбцы H — L листа 1)

Для второй записи (столбцы M через Q листа 1)

Я считаю, что вы уже знаете, как работает VLOOKUP, поэтому я просто объясню вторую формулу.

MATCH даст вам номер строки первой записи в Sheet2 с тем же представлением #, что и запись в Sheet1 (в данном примере, 2). Добавьте один к нему, и у вас будет номер строки второй записи (3).

INDIRECT даст вам содержимое ячейки, адрес которой вы получите, когда вы объедините «Sheet2.B» и 3 (Sheet2.B3).

Если вы не знакомы с нотацией$, она вообще не влияет на формулу: она просто существует, чтобы вам было легче скопировать формулу в другие ячейки. Все, что вам нужно сделать, это заменить «Sheet2.B» на «Sheet2.C», «Sheet2.D» и т. д., Когда вы копируете его в другие столбцы.

Надеюсь, это поможет!

Похожие вопросы:

На LibreOffice (то же самое на Excel?), Я хотел бы повторить формулу VLOOKUP (C2; B1:D2;3; 0) для всех моих столбцов. например, ВПР(С2;B1:D2;3;0) ВПР(С3;B1:D3;3;0) ВПР(С4;B1:D4;3;0) и т. д. Но если.

У меня есть электронная таблица, которая содержит два листа. Каждый лист содержит столбец C, который мне нужно извлечь только последние цифры после тире. Поэтому в ячейке D он делает это с помощью.

Читать еще:  Как установить майкрософт офис 2020

Col A Col B Fruit Grapes Fruit Mango Fruit Mango Veg Carrot Veg Brinjal Fruit Banana Veg Carrot У меня есть аналогичное требование, как показано в этом потоке набор выпадающих значений на основе ВПР.

Я пытаюсь использовать формулу, которая гласит следующее: Если A4 = T, То Vlookup Еще Vlookup2 Вот что я написал, что это не нравится. =Если(А4 =.

Я пытаюсь скомпилировать libreoffice, чтобы использовать его online, т. е.: в веб-браузере (см. демо здесь: http://www.youtube.com/watch?в=CVR7HqDokmA ). При запуске исполняемого файла, я получаю.

Возможно ли, чтобы функция возвращала два значения? Массив возможен, если оба значения имеют один и тот же тип, но как вернуть два разных значения типа?

=vlookup(c2,code1,2,false) Я использую код vlookup, но я хотел бы использовать значение ячейки в ‘c2’, а не сам c2. Значение внутри этой ячейки изменится, в данный момент оно равно ‘aj2’. Есть ли.

Я работаю над файлом excel, который имеет два листа: Лист (использование ВПР функции для получения значений) Лист B (содержит массив таблиц vloopup) Проблема в том, что я хочу сохранить только лист.

Я пытаюсь обратиться .docx файл к.pdf использование pdfgear, как описано в https://github.com/phpgearbox/pdf . По умолчанию этот компонент имеет значение libre-office-headless. Я установил.

Итак, я довольно новичок в использовании функций формулы excels, и я все еще учусь. То, что я пытаюсь сделать, — это объединить два утверждения, которые работают независимо, но мне нужно, чтобы они.

Использование функции ВПР в программе Excel

Во время работы в Эксель нередко требуется перенести или скопировать определенную информацию из одной таблицы в другую. Выполнить подобную процедуру, конечно же, можно вручную, когда речь идет о небольших объемах данных. Но что делать, если нужно обработать большие массивы данных? В программе Microsoft Excel на этот случай предусмотрена специальная функция ВПР, которая автоматически все сделает в считанные секунды. Давайте посмотрим, как это работает.

Описание функции ВПР

ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.

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

Применение функции ВПР на практике

Допустим, у нас есть таблица с продажами товаров. В ней отсутствуют данные по цене каждой позиции, однако, они есть в другой таблице рядом.

Наша задача – заполнить недостающие в основной таблице цены, чтобы мы могли посчитать итоговые суммы по продажам.

Порядок действий в данном случае следующий:

  1. Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
  2. В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
  3. Теперь предстоит правильно заполнить аргументы функции:
    • в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
    • переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
      Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
    • Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
    • в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
    • в значении аргумента “Интервальный_просмотр” можно указать два значения:
      • ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
      • ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
      • мы выбираем первый вариант, так как нам важна предельная точность.
    • Когда все готово, нажимаем OK.
  4. В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
  5. Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
  6. В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.
Читать еще:  Как продлить лицензию офис

Заключение

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

ВПР (VLOOKUP)

Производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки.

Примеры использования

ВПР(10003; A2:B26; 2; ЛОЖЬ)

Синтаксис

ВПР(запрос; диапазон; индекс; [отсортировано])

запрос – критерий, по которому выполняется поиск ( например, 42 , » кошка» или I24 ).

диапазон – диапазон, в первом столбце которого будет производиться поиск по запросу .

индекс – номер столбца (от начала диапазона ), из которого нужно взять искомое значение.

  • Если индекс не попадает в интервал от 1 до числа столбцов в диапазоне , возвращается ошибка #ЗНАЧЕН! .

отсортировано – по умолчанию [ ИСТИНА ]. Указывает, отсортированы ли данные в столбце, в котором производится поиск (первом столбце из указанного диапазона). Как правило, рекомендуемое значение – ЛОЖЬ.

Если для параметра отсортировано указать значение ЛОЖЬ (рекомендуется), возвращается только точное совпадение. Если таких совпадений несколько, возвращается значение для первого из них. Если точных совпадений нет, возвращается ошибка #Н/Д .

Если для параметра отсортировано не выбрано значение или выбрано ИСТИНА , возвращается значение, ближайшее к запрошенному (меньшее либо равное). Если все значения в столбце поиска больше указанного, возвращается ошибка #Н/Д .

Примечания

Если для параметра отсортировано не выбрано значение или выбрано ИСТИНА , а первый столбец диапазона не отсортирован, функция может вернуть неверное значение. Если результаты ВПР вызывают сомнение, убедитесь, что для последнего параметра указано значение ЛОЖЬ. Такая конфигурация подходит для большинства случаев. Если же данные в столбце отсортированы и вы хотите оптимизировать поиск, укажите значение ИСТИНА.

При поиске чисел или дат убедитесь, что первый столбец в диапазоне не отсортирован по текстовым значениям. К примеру, правильно отсортированные числа должны располагаться в порядке (1; 2; 10; 100). Если их отсортировать как текст, порядок будет иным: (1; 10; 100; 2). При неверном типе сортировки функция может вернуть неправильное значение.

Запросы на основе регулярных выражений НЕ поддерживаются. В этих случаях нужно использовать функцию QUERY .

ВПР работает быстрее, если данные в диапазоне отсортированы и для параметра отсортировано указано значение ИСТИНА .

Также для поиска можно использовать шаблоны с подстановочными знаками. Знаки «?» и «*» подставляются в запрос . При этом знак вопроса обозначает один символ, а звездочка – набор символов. Если вы хотите найти вопросительный знак или звездочку в тексте, введите перед искомым символом тильду (

), чтобы указать, что это не подстановочный знак. А если нужно найти тильду, введите ещё одну.

Похожие функции

QUERY : Выполняет запросы на базе языка запросов API визуализации Google.

ГПР : Производит поиск по первой строке диапазона и возвращает значение из найденной ячейки.

Примеры

ВПР ищет в первом столбце номер студента и возвращает соответствующую оценку.

ВПР ищет в первом столбце приблизительное значение дохода (параметр отсортировано имеет значение ИСТИНА ) и возвращает соответствующую ему ставку налога.

Если по запросу найдено несколько равных значений, ВПР возвращает первое из них.

Ссылка на основную публикацию
Adblock
detector