Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
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). При неверном типе сортировки функция может вернуть неправильное значение.

Читать еще:  Код для офиса 2020 бесплатно

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

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

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

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

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

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

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

Примеры

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

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

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

Excel как универсальный инструмент сравнения списков.

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

  • Список покупок, занесенных в табличный процессор (Excel, LibreOffice Calc, Google Docs и т.п.);
  • Список файлов, полученный копированием из Total Commander;

Вариантов огромное количество, цель одна – получить список отличий одного списка от другого.

Для решения такой задачи отлично подходят две программы: Excel или LibreOffice Calc.

В качестве «сравнителя» используется функция ВПР. Причем синтаксис, используемой функции настолько идентичен, что нет никакого различия какую программу использовать.

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

Для получения списка файлов из папки я буду использовать поиск в Total Commander. Да, есть такая классная фича в Total Commander.

Открываю первую папку с фото и открываю стандартное окно поиска по ALT+F7, задаю маску поиска *.jpg для поиска только файлов фото.

В результате поиска в папке «Фото» было найдено 49 файлов. Чтобы скопировать список найденных файлов в буфер обмена нажимаем стандартную комбинацию клавиш CTRL+C.

Запускаем Excel. По умолчанию открывается пустая книга. На первом листе становимся в ячейку A1 и нажимаем стандартную комбинацию CTRL+V, чтобы вставить скопированный список.

Теперь нужно сделать поиск файлов во второй папке «Фото1». В результате поиска найдено 199 файлов. Копируем список через CTRL+C.

Скопированный список из второй папки вставляем в ячейку A1 на другой лист, в моем случае это «Лист2»

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

Итак, на листе «Лист1» я выделяю в любой ячейке кусочек строки «d:Фото», копирую его в буфер и нажатием ESC выхожу из режима редактирования.

Для групповой замены нажимаю CTRL+H. В стандартном окне замены в поле «Найти:» вставляю скопированный кусок строки. Поле «Заменить на:» оставляю пустым.

Нажимаю кнопку «Заменить все».

Контролирую процесс замены, сравнивая количество изначальных строк с количеством замен. В моем случае все отлично 49 строк и 49 замен.

Тоже самое проделываю и на втором листе.

В результате замен на двух листах остаются списки файлов без пути. Теперь их можно сравнивать.

Для сравнения используем функцию «ВПР».

В ячейке B1 листа «Лист1» нажимаю «=» и начинаю писать имя функции ВПР, открываю скобку «(«. Теперь Excel подсказывает мне синтаксис аргументов функции. Первым параметром мне нужно указать строку, которую я буду искать в другой области(«искомое_значение»). Я указываю ячейку A1.

Далее ставлю точку с запятой и указываю второй параметр «таблица». Под таблицей понимается область листа из нескольких столбцов. Поиск искомого значения будет происходить в первом столбце указанной области. В моем случае я просто указываю весь столбец A листа «Лист2». Чтобы указать весь столбец нажимаю на заголовок столбца когда курсор становится жирной стрелкой вниз.

Опять ставлю точку с запятой и указываю параметр «номер_столбца». Все столбцы в области поиска условно нумеруются от 1. Так как у меня в области поиска всего один столбец в параметре я ставлю 1.

Последний параметр, который я указываю, это «интервальный_просмотр». Суть его сводится к тому, как будет происходить поиск: точно или приблизительно. Для нашего сравнения обязательно нужно выбрать точное совпадение. Выбираю «ЛОЖЬ» — Точное совпадение.

Читать еще:  Раздели в офисе

Итоговая формула выглядит вот так

В нашем случае файл с таким именем есть во втором списке и функция ВПР нашла его и вернула значение из первого столбца области поиска, т.е. само имя файла.

Теперь осталось скопировать формулу для всех остальных строк на первом листе.

Следующим шагом будет установка фильтра по столбцу B на листе «Лист1». Выделяем весь столбец «B»

Затем нажимаем «Сортировка и фильтр» и выбираем пункт «Фильтр»

В результате получается вот такой вид на листе «Лист1»

Чтобы увидеть список файлов, которых нет во втором списке, надо раскрыть список фильтра и выделить только значение с типом «Н/Д»

В нашем случае таких файлов нет. Т.е. во втором списке есть все файлы из первого списка. Осталось установить функцию ВПР для всех строк на втором листе.
Обратите внимание на то, что в качестве области поиска указывается столбец «A» на «Лист1». Формулу копируем для всех строк листа «Лист2»

Устанавливаем фильтр на втором листе аналогично первому. После установки фильтра, в списке значений фильтра в самом конце обнаруживается значение «Н/Д». Строки, содержащие такое значение и указывают на файлы из второго списка, которых нет в первом.

Устанавливаем галочку на «Н/Д» и нажимаем «ОК». Все остальные галочки в фильтре снимаем.

В результате фильтрации мы видим список уникальных файлов второго списка, которых нет в первом.

Собственно, цель сравнения достигнута. Что делать с этим списком уже другой вопрос.

При сравнении в LibreOffice Calc формула ВПР выглядит абсолютно аналогично Excel

Автофильтр устанавливаем через меню «Данные»«Автофильтр»

Функции Libre Office, которые вам захочется применить в своей работе

Ответ на http://pikabu.ru/story/_3736968

Первое и самое главное отличие MS Office и Libre Office в архитектуре программ. Если MS – набор отдельных программ, то LO – единый организм с несколькими интерфейсами управления. При этом установленный LO занимает в 2-3 раза меньше места на жестком диске.

Второе — некоторые из этих функций работают в MS Office. Так что — пробуйте, может быть Вам повезет.

Итак что мне нравиться в LO:

1. «Горячие клавиши»:
Ctrl+Q – выход
Ctrl+W – закрыть текущий документ
Ctrl+E – Выровнять текст по центру
Ctrl+Shift+E — запись изменений в документе.
Ctrl+R — Выровнять текст по правому краю
Ctrl+Shift+R — показать/скрыть линейку.
Ctrl+Y – вернуть отмененное действие
Ctrl+Shift-Y — повторить последнее действие (или продублировать введенное слово).
Ctrl+U — подчеркивание
Ctrl+I — курсив
Ctrl+O – открыть файл
Ctrl+Shift+O — просмотр печати
Ctrl+P – печать документа
Ctrl+Shift+P – верхний индекс
Ctrl+A – выделить весь текст
Ctrl+S – сохранить
Ctrl+D – двойное подчеркивание
Ctrl+Shift+S – сохранить как
Ctrl+F — поиск
Ctrl+H – замена
Ctrl+J – выравнивание по ширине.
Ctrl+Shift+J – полноэкранный режим.
Ctrl+K – вставка гиперссылки
Ctrl+L – выравнивание по левому краю
Ctrl+Z – отменить действие
Ctrl+X — вырезать
Ctrl+C — копировать
Ctrl+Shift+C – вставить как
Ctrl+Alt+C — вставить примечание/комментарий
Ctrl+V – вставить
Ctrl+Shift+V – вставить как
Ctrl+Shift+Alt+V – вставить неформатированный текст
Ctrl+B – полужирный шрифт
Ctrl+Shift+B – нижний индекс
Ctrl+N – новый документ
Ctrl+M – отмена форматирования
Ctrl+[0-5] – стили текста
Alt+Shift+F8 – режим блочного/обычного выделения
Ctrl+F3 – редактор автотекста
Shift+F3 – переключение регистра текста (заглавные/прописные)
F4 – источники данных
F5 – навигатор документа
F7 – правописание
F11 – выбор стилей
F12 – нумерованный список
Ctrl+F12 – вставить таблицу

И это — далеко не полный перечень. Команды назначаются комбинациям клавиш очень легко и наглядно (гораздо проще чем в MS).

2. Вычисления непосредственно в документе и в таблицах.
Нажмите F2 и введите выражение которое необходимо вычислить и после нажатия «ввода» результат будет отображен. Двойной клик на результате — изменение формулы.

В таблице — в ячейке наберите «=» после чего таблица будет работать в режиме «Excel» т. е. будет возможность набирать формулы. Редактирование так же по F2.

3. Сохраняйте сразу в Jpeg/PNG/PDF/MediaWiki через меню «Экспорт» (бывает невероятно полезно при склейке сканов).

4. Открытие файла независимо от формата. Через меню «Открыть» можно открывать любой поддерживаемый файл. То есть из Writer(Word) можно открыть файл Calc(Excel) и он нормально откроется в Calc.

5. Редактирование PDF. Просто открываем и редактируем PDF (в 2013-м появилась функция).

6. Меню оперативного редактирования таблиц. Пока курсор находится в таблице отображается окно с оперативными функциями по работе с таблицами (вставка/удаление/выделение ячеек/строк/столбцов).

7. Возможность массовой вставки строк и столбцов в таблицу через меню правой кнопки мыши (строка/столбец — вставить)

8. Смена регистра через меню правой кнопки мыши и по Shift+F3. Теперь включенный CapsLock лечиться гораздо быстрее! (в MS – просто F3).

9. Кнопка Insert переключает режимы редактирования текста «добавление»/«Замена» (есть и в MS)

Читать еще:  Офис 2020 отзывы

10. Ctrl+Alt+Вверх/Вниз — поменять абзацы местами.

11. Более удобная и логичная организация меню.

Кстати, скрытый текст, кернинг можно отредактировать поставив соответствующие галочки и значения в окне «символы» (правой кнопкой мыши на тексте — «Символы»).

Libre Office позволяет вставлять картинки, видео, звук и некоторые другие объекты.

Use of LibreOffice for an office automation environment

VLOOKUP Function

What is VLOOKUP

The VLOOKUP function (short for Vertical LOOKUP) is a built-in Calc function that is designed to work with data that is organized into columns. For a specified value, the function finds (or looks up) the value in one column of data, and returns the corresponding value from another column. Once you understand how VLOOKUP works and how to use it you will be able to create more sophisticated spreadsheets and further automate your work.

VLOOKUP example

The VLOOKUP function is best explained using an example. In the spreadsheet below we calculate the Invoice Total as the sum of the Order Total plus the Shipping Cost.

Instead of manually typing the Shipping Cost every time, VLOOKUP enables you to find the cost from the table above using the Shipping Type information («Premium» in our example). You can probably already see in our example, at a glance, what is the cost of shipping, but on a more complex spreadsheet this information might be out of view, or even on another sheet. It is also shown by the example that with VLOOKUP we can refer to properties or values of a category simply by its name.

Using VLOOKUP

Lets insert the VLOOKUP formula into the Shipping Cost cell (C9). VLOOKUP has a lot of arguments therefore it is best to use the function wizard . Select the cell C9 where the VLOOKUP function will be inserted. On the Function Wizard window select the Spreadsheet function category and choose the VLOOKUP function from the bottom of the list. Now you have to fill the four arguments that VLOOKUP requires.

Search criterion. This is the value that is searched for in the first column of the data table. In our example we are looking for the «shipping type» contained in cell C8.

Array. Now you must tell VLOOKUP the array where to look for the search criterion. In our example the array is the cell range A2:C4. VLOOKUP will search for the criterion in the first column of the array. One must ask why we give the array and not just the first column? The answer is because VLOOUKP needs the array not only to search but to return results from the other columns.

Index. The third argument is the column number from which the value will be returned. The first column has index 1, column 2, etc. In our example, we try to find the shipping cost contained in the second column.

Sort order. The last argument is optional but you are advised to use 0 or FALSE. When the sort order is 0 and a search term is not found, VLOOKUP returns an error message. If you skip this argument, the default value will be 1 or TRUE. In this case, VLOOKUP will always return the result of the last row even if the search does not match the term. This can easily lead to bugs in your spreadsheet if you do not know exactly how VLOOKUP works.

When you finish and click OK the formula result will be displayed in the cell with the Shipping Cost.

Now if you change the Shipping Type to «Standard» the Shipping Cost automatically calculates to the value of 5

However if you insert a value that is not found on the array VLOOKUP will output an error. However, if you have assigned a sort order of 1 then the last ranked result will be returned.

How VLOOKUP works

Now that you have seen an example we can summarize how VLOOKUP function works.

Look for a value (for example «Premium«) specified in the search criterion.

Define the a range that contains the column to look for and the columns to return results in the array argument. VLOOKUP will always search the first column in this range.

Now return the value that is in the same row with the matching result and in the column specified by the column index.

Use sort order 0, except when you have very large arrays and the first column is sorted.

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