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

Блог про LibreOffice

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

Счетов очень много;
Мне нужно проделать это для пяти банков и в динамике за три года, итого 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.

Функции электронных таблиц

Этот раздел содержит описание функций электронных таблиц , а также примеры.

Доступ к этой команде

Вставка — Функция — Тип Электронная таблица

[text/scalc/01/func_error_type.xhp#error_type_head not found].

Returns a number representing a specific Error type, or the error value #N/A, if there is no error.

STYLE

Applies a style to the cell containing the formula. After a set amount of time, another style can be applied. This function always returns the value 0, allowing you to add it to another function without changing the value. Together with the CURRENT function you can apply a color to a cell depending on the value. For example: =. +STYLE(IF(CURRENT()>3;»red»;»green»)) applies the style «red» to the cell if the value is greater than 3, otherwise the style «green» is applied. Both cell formats, «red» and «green» have to be defined beforehand.

Syntax

STYLE(«Style» [; Time [; «Style2»]])

Стиль : имя стиля, назначенного ячейке. Имена стилей заключаются в кавычки.

Время (необязательный параметр): промежуток времени в секундах. Если этот параметр не задан, то по истечении определенного промежутка времени стиль остаётся без изменений.

Стиль2 (необязательный параметр): имя стиля, применяемого к ячейке по истечении определенного промежутка времени. Если этот параметр не определен, то используется стиль «По умолчанию».

В функциях LibreOffice Calc параметры, отмеченные, как «необязательные» могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра «необязательные», вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.

Example

=STYLE(«невидимый»; 60; «по умолчанию») применяет к ячейке прозрачный формат на 60 секунд после повторного расчета или загрузки документа, после чего применяет формат «По умолчанию». Для обеих ячеек необходимо предварительно определить формат.

Since STYLE() has a numeric return value of zero, this return value gets appended to a string. This can be avoided using T() as in the following example:

Еще один пример см. в описании CURRENT().

LOOKUP

Returns the contents of a cell either from a one-row or one-column range. Optionally, the assigned value (of the same index) is returned in a different column and row. As opposed to VLOOKUP and HLOOKUP, search and result vector may be at different positions; they do not have to be adjacent. Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results.

Если с помощью функции LOOKUP не удаётся установить критерий поиска, то он соответствует самому большому значению в векторе просмотра, который меньше, чем критерий поиска, или равен ему.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter «all.*», for example to find the first location of «all» followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a «\» character, or enclose the text into \Q. \E. You can switch the automatic evaluation of wildcards or regular expression on and off in LibreOffice — Preferences Tools — Options — LibreOffice Calc — Calculate .

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, «.0» will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as «.[0]» or «.\0» or «(?i).0».

Syntax

LOOKUP(SearchCriterion; SearchVector [; ResultVector])

Условие_поиска : значение для поиска, которое вводится вручную или посредством создания ссылки на него.

Вектор_поиска : область для выполнения поиска, состоящая из отдельной строки или столбца.

Вектор_результата : другой диапазон из одной строки или одного столбца, из которого извлекается результат функции. Функция возвращает ячейку для вектора результата с тем же индексом, что и экземпляр, найденный в векторе просмотра.

Example

=LOOKUP(A1;D1:D100;F1:F100) позволяет выполнить поиск соответствующей ячейки в диапазоне D1:D100 для числа, указанного в ячейке A1. Для найденного экземпляра определяется индекс, например, 12-я ячейка в этом диапазоне. Затем содержимое 12-й ячейки возвращается в виде значения функции (в векторе результата).

VLOOKUP

Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of an array. The function then returns the value in the same row of the column named by Index . If the Sorted parameter is omitted or set to TRUE or one, it is assumed that the data is sorted in ascending order. In this case, if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned. If Sorted is set to FALSE or zero, an exact match must be found, otherwise the error Error: Value Not Available will be the result. Thus with a value of zero the data does not need to be sorted in ascending order.

Syntax

=VLOOKUP(SearchCriterion; Array; Index [; SortedRangeLookup])

Условие_поиска : значение для поиска в первом столбце массива.

Array is the reference, which is to comprise at least as many columns as the number passed in Index argument.

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

SortedRangeLookup is an optional parameter that indicates whether the first column in the array contains range boundaries instead of plain values. In this mode, the lookup returns the value in the row with first column having value equal to or less than SearchCriterion. E.g., it could contain dates when some tax value had been changed, and so the values represent starting dates of a period when a specific tax value was effective. Thus, searching for a date that is absent in the first array column, but falls between some existing boundary dates, would give the lower of them, allowing to find out the data being effective to the searched date. Enter the Boolean value FALSE or zero if the first column is not a range boundary list. When this parameter is TRUE or not given, the first column in the array must be sorted in ascending order . Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is greater than the lowest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return #N/A with message: Error: Value Not Available .

Example

You want to enter the number of a dish on the menu in cell A1, and the name of the dish is to appear as text in the neighboring cell (B1) immediately. The Number to Name assignment is contained in the D1:E100 array. D1 contains 100 , E1 contains the name Vegetable Soup , and so forth, for 100 menu items. The numbers in column D are sorted in ascending order; thus, the optional Sorted parameter is not necessary.

Введите следующую формулу в ячейку B1:

При вводе номера в ячейку A1 в ячейке B1 будет отображен соответствующий текст, который содержится во втором столбце массива D1:E100. При вводе несуществующего номера в ячейке отображается текст для следующего номера. Для исключения этого задайте последнему параметру формулы значение FALSE, чтобы при вводе несуществующего номера отображалось сообщение об ошибке.

ADDRESS

Возвращает адрес (ссылку) ячейки в виде текста в соответствии с указанными номерами строки и столбца. Можно выбрать отображение адреса как абсолютного (например, $A$1), относительного (A1) или смешанного типа (A$1 или $A1). Можно также указать имя листа.

Для функциональной совместимости функции ADDRESS и INDIRECT поддерживают необязательный параметр, который позволяет указать, использовать ли вместо нотации адреса A1 нотацию R1C1.

В функции ADDRESS этот параметр вставляется в качестве четвертого параметра, смещая при этом необязательный параметр имени листа на пятое место.

В функции INDIRECT этот параметр добавляется в качестве второго параметра.

В обеих функциях, если аргумент вставлен со значением 0, используется нотация R1C1. Если аргумент не используется или его значение не равно нулю, используется нотация A1.

Если используется нотация R1C1, функция ADDRESS возвращает строки адреса, используя в качестве разделителя имён листов восклицательный знак ‘!’, а функция INDIRECT ожидает, что в качестве разделителя имён листов используется восклицательный знак. В нотации А1 обе функции по-прежнему используют в качестве разделителя имён листов точку ‘.’.

When opening documents from ODF 1.0/1.1 format, the ADDRESS functions that show a sheet name as the fourth parameter will shift that sheet name to become the fifth parameter. A new fourth parameter with the value 1 will be inserted.

Если при сохранении документа в формате ODF 1.0/1.1 в функциях ADDRESS имеется четвертый параметр, этот параметр будет удален.

Не сохраняйте электронную таблицу в старом формате ODF 1.0/1.1, если четвертый параметр функции ADDRESS использовался со значением 0.

Функция INDIRECT сохраняется без преобразования в формат ODF 1.0/1.1. Если имеется второй параметр, более старая версия Calc возвратит для этой функции ошибку.

Syntax

ADDRESS(Row; Column [; Abs [; A1 [; «Sheet»]]])

Строка : номер строки для ссылки на ячейку.

Столбец : номер столбца для ссылки на ячейку (число, а не буква).

Абс определяет тип ссылки:

1: абсолютная ($A$1)

2: абсолютная ссылка на строку, относительная ссылка на столбец (A$1)

3: строка (относительная), столбец (абсолютная) ($A1)

4: относительная (A1)

A1 (необязательный параметр): если для этого параметра установлено значение 0, то используется нотация R1C1. Если этот параметр отсутствует или имеет значение, отличное от 0, то используется нотация A1.

Лист : имя листа. Имя столбца заключается в двойные кавычки.

Пример:

=ADDRESS(1;1;2;;»Sheet2″) возвращает следующий результат: Лист2.A$1

If the formula above is in cell B2 of current sheet, and the cell A1 in sheet 2 contains the value -6 , you can refer indirectly to the referenced cell using a function in B2 by entering =ABS(INDIRECT(B2)) . The result is the absolute value of the cell reference specified in B2, which in this case is 6.

OFFSET

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

Syntax

OFFSET(Reference; Rows; Columns [; Height [; Width]])

Ссылка : ссылка, начиная с которой функция выполняет поиск новой ссылки.

Строки : количество строк, на которое ссылка была смещена вверх (отрицательное значение) или вниз.

Высота (необязательный параметр) высота области, которая начинается с новой позиции ссылки.

Ширина (необязательный параметр): ширина области, которая начинается с новой позиции ссылки.

Аргументы Строки и Столбцы не должны привести к нулю или отрицательной строке или столбцу начала.

Аргументы Высота и Ширина не должны привести к нулю или отрицательному количеству строк или столбцов.

Example

=OFFSET(A1;2;2) возвращает значение ячейки C3 (ячейка A1 смещается вниз на две строки и два столбца). Если ячейка C3 содержит значение 100 , то эта функция возвращает значение 100.

=OFFSET(B2:C3;1;1) возвращает ссылку на диапазон B2:C3, перемещенный на 1 строку вниз и на один столбец вправо (C3:D4).

= OFFSET(B2:C3;-1;-1) возвращает ссылку на диапазон B2:C3, поднятый на 1 строку и сдвинутый влево на 1 столбец (A1:B2).

=OFFSET(B2:C3;0;0;3;4) возвращает ссылку на диапазон B2:C3, размер которого изменён на 3 строки и 4 столбца (B2:E4).

=OFFSET(B2:C3;1;0;3;4) возвращает ссылку на диапазон B2:C3, смещенный вниз на одну строку и изменивший размер на 3 строки и 4 столбца (B2:E4).

=SUM(OFFSET(A1;2;2;5;6)) позволяет определить общую площадь области, которая начинается с ячейки C3 и имеет в своем составе 5 строк в высоту и 6 столбцов в ширину (область=C3:H7).

If Width or Height are given, the OFFSET function returns a cell range reference. If Reference is a single cell reference and both Width and Height are omitted, a single cell reference is returned.

AREAS

Возвращает количество отдельных диапазонов, входящих в составной диапазон. Диапазон может состоять из смежных ячеек или одной ячейки.

В функцию передаётся только один аргумент. При определении составных диапазонов их необходимо заключать в дополнительные скобки. Составные диапазоны указываются через точку с запятой (;), но этот знак автоматически преобразуется в оператор «тильда» (

). Тильда используется для объединения диапазонов.

Syntax

Ссылка. Ссылка на ячейку или диапазон ячеек.

Example

=AREAS((A1:B3;F2;G1)) возвращает значение 3, поскольку это ссылка на три ячейки или области. После ввода выполняется преобразование в =AREAS((A1:B3

=AREAS(Все) возвращает значение 1, если в окне Данные – определить диапазон была определена область с именем «Все».

COLUMNS

Возвращает количество столбцов для заданной ссылки.

Syntax

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

Example

=COLUMNS(B5) возвращает значение 1, поскольку ячейка содержит только один столбец.

=COLUMNS(A1:C5) возвращает значение 3. Диапазон содержит три столбца.

=COLUMNS(Rabbit) возвращает значение 2, если Rabbit – указанный диапазон (C1:D3).

Возвращает количество строк в массиве или ссылке.

Syntax

Массив : ссылка или имя области, для которой требуется определить общее количество строк.

Example

=ROWS(B5) возвращает значение 1, поскольку ячейка включает только одну строку.

=ROWS(A10:B12) возвращает значение 3.

=ROWS(Rabbit) возвращает значение 3, если » Rabbit» – указанный диапазон (C1:D3).

SHEET

Возвращает номер листа для ссылки или строку с именем листа. Если параметры не заданы, возвращается номер листа с формулой.

Syntax

Ссылка (необязательный параметр): ссылка на ячейку или область, либо строка с именем листа.

Example

= SHEET(Лист2.A1) возвращает значение 2, если лист2 является вторым листом электронной таблицы.

COLUMN

Возвращает номер столбца для ссылки на ячейку. Если ссылка указывает на ячейку, возвращается номер столбца для этой ячейки; если ссылка указывает на область ячеек, возвращаются соответствующие номера столбцов в виде массива из одной строки при условии ввода формулы в качестве формулы массива. Если функция COLUMN со ссылкой на область не используется в качестве функции массива, возвращается только номер столбца для первой ячейки области.

Syntax

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

Если ссылка не указана, возвращается номер столбца для ячейки с формулой. LibreOffice Calc автоматически создаёт ссылку на текущую ячейку.

Example

=COLUMN(A1) возвращает значение 1. Столбец A является первым столбцом в таблице.

=COLUMN(C3:E3) возвращает значение 3. Столбец C является третьим столбцом в таблице.

=COLUMN(D3:G10) возвращает значение 4, поскольку столбец D является четвертым в таблице, а функция COLUMN не используется в качестве формулы массива. (В этом случае результатом всегда является первое значение массива.)

<=COLUMN(B2:B7)>и =COLUMN(B2:B7) возвращают значение 2, поскольку ссылка указывает только на столбец B, являющийся вторым столбцом в таблице. Поскольку для области, состоящей из одного столбца, можно извлечь только один номер столбца, формулу массива использовать необязательно.

=COLUMN() возвращает значение 3, если формула была введена в столбце C.

<=COLUMN(Rabbit)>возвращает массив с одной строкой (3, 4), если » Rabbit» – указанный диапазон (C1:D3).

Возвращает номер строки для ссылки на ячейку. Если ссылка указывает на ячейку, возвращается номер строки для этой ячейки. Если ссылка указывает на диапазон ячеек, функция возвращает соответствующие номера строк в массиве с одним столбцом, если формула используется как формула массива. Если функция ROW со ссылкой на диапазон не является формулой массива, возвращается только номер строки для первой ячейки диапазона.

Syntax

Ссылка : ячейка, область или имя области.

Если ссылка не указана, возвращается номер строки для ячейки, которая содержит формулу. LibreOffice Calc автоматически создаёт ссылку на текущую ячейку.

Example

=ROW(B3) возвращает значение 3, поскольку ссылка указывает на третью строку таблицы.

<=ROW(D5:D8)>возвращает массив, состоящий из одного столбца (5, 6, 7, 8), поскольку ссылка указывает на строки с 5 по 8.

=ROW(D5:D8) возвращает значение 5, поскольку функция ROW не используется как формула массива, таким образом, возвращается только номер первой строки ссылки.

<=ROW(A1:E1)>и =ROW(A1:E1) возвращают значение 1, поскольку ссылка указывает только на строку 1 как на первую строку в таблице. (Поскольку для области, состоящей из одной строки, можно извлечь только один номер строки, формулу массива использовать необязательно.)

=ROW() возвращает значение 3, если формула была введена в строку 3.

<=ROW(Rabbit)>возвращает массив с одним столбцом (1, 2, 3), если «Rabbit» – указанный диапазон (C1:D3).

ERRORTYPE

Возвращает номер типа ошибки в другой ячейке. С помощью этого номера можно воспроизвести текст сообщения об ошибке.

If an error occurs, the function returns a logical or numerical value.

В строке состояния при щелчке ячейки с ошибкой отображается стандартный код ошибки LibreOffice .

Syntax

Ссылка : адрес ячейки с ошибкой.

Example

Если в ячейке A1 отображается ошибка Err:518, функция =ERRORTYPE(A1) возвращает номер 518.

MATCH

Возвращает относительную позицию в массиве элемента, который совпадает с заданным значением. Функция возвращает позицию значения, найденного в массиве, в виде числа.

Syntax

MATCH(SearchCriterion; LookupArray [; Type])

Условие_поиска : значение для поиска в массиве с одной строкой или столбцом.

Массив : ссылка для поиска. Это может быть одна строка или столбец, либо часть одной строки или столбца.

Тип : параметр, который может принимать значения 1, 0 или -1. Если этот параметр имеет значение 1, либо значение не указано, предполагается, что значения в первом столбце массива отсортированы по возрастанию. Если этому параметру присвоено значение -1, это означает, что значения столбца отсортированы по убыванию. Эта функция соответствует аналогичной функции в Microsoft Excel.

If Type = 0, only exact matches are found. If the search criterion is found more than once, the function returns the index of the first matching value. Only if Type = 0 can you search for regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options).

Если параметр «Тип» имеет значение 1 или третий параметр не задан, возвращается индекс последнего значения, которое меньше либо равно условию поиска. Это условие действует, даже если значения в массиве не отсортированы. Если параметр «Тип» равен -1, возвращается первое значение, которое больше либо равно условию поиска.

Example

=MATCH(200; D1:D100) выполняет поиск значения 200 в области D1:D100, отсортированной по столбцу D. По достижении этого значения возвращается номер соответствующей строки. Если найденное значение больше искомого, возвращается номер предыдущей строки.

Возвращает результат для ссылки DDE. Если содержимое диапазона или раздела изменилось, возвращаемое значение также меняется. Чтобы просмотреть обновлённые ссылки, следует перезагрузить электронную таблицу или выбрать команду Правка — Ссылки . Межплатформенные ссылки, например, ссылки в установке LibreOffice , запущенной в ОС Windows на документ, созданный в ОС Linux, запрещены.

Syntax

DDE(«Server»; «File»; «Range» [; Mode])

Server is the name of a server application. LibreOffice applications have the server name «soffice».

Файл : полное имя файла, включая путь.

Диапазон : область, содержащая данные для оценки.

Режим : необязательный параметр для управления методами преобразования данных в числа на сервере DDE.

0 или отсутствует

Формат числа из стиля ячейки «По умолчанию»

Данные всегда преобразуются в стандартный формат для английского языка (США)

Данные извлекаются в виде текста; преобразование в числа не выполняется

Example

=DDE(«soffice»;»c:\office\document\data1.ods»;»sheet1.A1″) reads the contents of cell A1 in sheet1 of the LibreOffice Calc spreadsheet data1.ods.

=DDE(«soffice»;»c:\office\document\motto.odt»;»Today’s motto») returns a motto in the cell containing this formula. First, you must enter a line in the motto.odt document containing the motto text and define it as the first line of a section named Today’s Motto (in LibreOffice Writer under Insert — Section ). If the motto is modified (and saved) in the LibreOffice Writer document, the motto is updated in all LibreOffice Calc cells in which this DDE link is defined.

INDIRECT

Возвращает ссылку в виде текстовой строки. Эту функцию можно также использовать для получения области соответствующей строки.

В функции INDIRECT этот параметр добавляется в качестве второго параметра.

Syntax

Ссылка : ссылка на ячейку или область (в текстовой форме), содержимое которой подлежит возврату.

If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in INDIRECT(«[filename]sheetname!»&B1) is not converted into the Calc address in INDIRECT(«filename#sheetname.»&B1).

Example

=INDIRECT(A1) возвращает значение 100, если ячейка A1 содержит ссылку на ячейку C108, а ячейка C108 содержит значение 100 .

=SUM(INDIRECT(«a1:» & ADDRESS(1;3))) суммирует содержимое ячеек в области от A1 до ячейки, адрес которой определен в строке 1 и столбце 3. Таким образом, вычисляется сумма диапазона A1:C1.

HYPERLINK

При щелчке ячейки с функцией HYPERLINK открывается соответствующая гиперссылка.

В случае ввода необязательного параметра Текст_ячейки формула выполняет поиск URL–адреса, а затем отображает его в виде текста или числа.

Чтобы открыть ячейку с гиперссылкой с помощью клавиатуры, выделите ячейку, нажмите клавишу F2, чтобы включить режим редактирования, поместите курсор в начало гиперссылки, нажмите сочетание клавиш SHIFT+F10, а затем выберите команду Открыть гиперссылку .

Syntax

URL : адрес, на который указывает ссылка. Текст_ячейки (необязательный параметр): текст или число, которое отображается в ячейке и возвращается как результат функции. Если параметр Текст_ячейки не определен, то в качестве текста ячейки отображается URL , который также возвращается как результат функции.

Для пустых ячеек и элементов матрицы возвращается 0.

Example

=HYPERLINK(«http://www.example.org») выводит на экран текст «http://www.example.org» в ячейке и при щелчке по ней переходит по гиперссылке http://www.example.org.

=HYPERLINK(«http://www.example.org»; «Щёлкните здесь») выводит на экран текст «Щёлкните здесь» в ячейке и при щелчке переходит по гиперссылке http://www.example.org.

=HYPERLINK(«http://www.example.org»;12345) displays the number 12345 and executes the hyperlink http://www.example.org when clicked.

=HYPERLINK($B4) , где ячейка B4 содержит строку http://www.example.org . Функция добавляет «http://www.example.org» к адресу веб-страницы в ячейке с гиперссылкой и возвращает этот текст в качестве результата формулы.

=HYPERLINK(«http://www.»;»Щёлкните») & «example.org» выводит на экран текст «Щёлкните example.org» в ячейке и при щелчке переходит по гиперссылке http://www.example.org.

=HYPERLINK(«#Лист1.A1»; «Перейти наверх») выводит на экран текст «Перейти наверх» и осуществляет переход к ячейке A1 листа1 этого документа.

=HYPERLINK(«file:///C:/writer.odt#Specification»;»Go to Writer bookmark») displays the text «Go to Writer bookmark», loads the specified text document and jumps to bookmark «Specification».

SHEETS

Служит для определения количества листов для ссылки. Если параметры не заданы, возвращается количество листов в текущем документе.

Syntax

Ссылка : ссылка на лист или область. Этот параметр является необязательным.

Example

=SHEETS(Sheet1.A1:Sheet3.G12) возвращает значение 3, если листы лист1, листt2 и лист3 стоят в указанной последовательности.

HLOOKUP

Служит для поиска значения и ссылки на ячейки в выделенной области. Эта функция проверяет первую строку массива на наличие определенного значения. Функция возвращает значение в тот же столбец в строку массива в соответствии с её номером в индексе .

Syntax

HLOOKUP(SearchCriterion; Array; Index [; SortedRangeLookup])

For an explanation on the parameters, see: VLOOKUP (columns and rows are exchanged)

GETPIVOTDATA

Функция GETPIVOTDATA возвращает значение результата из сводной таблицы. Значение адресуется с помощью имён поля и элемента, поэтому оно остаётся действительным даже при изменении структуры сводной таблицы.

Syntax

Можно использовать два разных синтаксиса:

GETPIVOTDATA(TargetField; pivot table[; Field 1; Item 1][; . [Field 126; Item 126]])

GETPIVOTDATA(сводная таблица; ограничения)

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

First Syntax

Целевое_поле : строка для выбора одного из полей данных сводной таблицы. Эта строка может содержать имя исходного столбца или имя поля данных, отображаемое в таблице (например, «Сумма – Сбыт»).

Сводная_таблица является ссылкой на ячейку или диапазон ячеек, расположенный в сводной таблице или содержащий сводную таблицу. Если диапазон ячеек содержит несколько сводных таблиц, то используется таблица, созданная последней.

При отсутствии пар поле n/элемент n возвращается общий итог. В противном случае каждая пара добавляет ограничение, которому должен удовлетворять результат. Поле n является именем поля из сводной таблицы. Элемент n является именем элемента из этого поля.

Если сводная таблица содержит только одно значение результата, которое удовлетворяет всем ограничениям, или результат промежуточного итога, который является суммой всех сопоставимых значений, то возвращается этот результат. При отсутствии результата сопоставления или при нескольких результатах без промежуточного итога возвращается ошибка. Эти условия относятся к результатам, включённым в сводную таблицу.

Исходные данные, которые содержат записи, скрытые настройками сводной таблицы, игнорируются. Порядок пар «поле/элемент» не имеет значения. Регистр имён полей и элементов не учитывается.

При отсутствии ограничения для поля страницы выбранное значение поля используется неявно. Если ограничение для поля страницы существует, оно должно совпадать с выбранным значением поля, в противном случае возвращается ошибка. Поля страницы — это поля в верхнем левом углу сводной таблицы заполненные с использованием области «Поля страницы» диалогового окна «Разметка сводной таблицы». Из каждого поля страницы можно выбрать элемент (значение), которое означает, что в расчёт включён только этот элемент.

Значения промежуточных итогов из сводной таблицы используются только для функции «Авто» (кроме значений, включённых в ограничения, см. ниже Второй синтаксис ).

Second Syntax

Сводная_таблица : аналогично первому варианту синтаксиса.

Ограничения : список значений, разделённых пробелами. Элементы списка могут заключаться в кавычки (одиночные). Вся строка заключается в двойные кавычки (за исключением случая ссылки на строку из другой ячейки).

Одна из записей может быть именем поля данных. Если сводная таблица содержит только одно поле данных, имя поля данных можно не указывать; в противном случае его необходимо определить.

Все остальные записи указывают ограничение в форме Поле[Элемент] (с символами «[» и «]»), либо только Элемент , если имя элемента уникально среди всех полей, используемых в сводной таблице.

Имя функции можно добавить в форме Поле[Элемент;Функция] , в результате чего сопоставление ограничится только значениями промежуточных итогов, для которых эта функция используется. Допустимые имена функций — Sum, Count, Average, Max, Min, Product, Count (только числа), StDev (выборка), StDevP (заполнение), Var (выборка) и VarP (заполнение) без учёта регистра.

INDEX

Функция INDEX возвращает поддиапазон, для которого указаны номер строки и столбца или имя диапазона. В зависимости от контекста, функция INDEX возвращает ссылку или значение.

Syntax

INDEX(Reference [; Row [; Column [; Range]]])

Ссылка : ссылка, введенная вручную или определенная посредством указания имени диапазона. Если ссылка содержит несколько диапазонов, то эту ссылку или имя диапазона следует заключить в скобки.

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

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

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

Example

=INDEX(Цены;4;1) возвращает значение для строки 4 и столбца 1 из диапазона в базе данных, определенного по пути Данные – Определить как Цены .

=INDEX(SumX;4;1) returns the value from the range SumX in row 4 and column 1 as defined in Sheet — Named Ranges and Expressions — Define .

=INDEX(A1:B6;1) возвращает ссылку на первую сроку диапазона A1:B6.

=INDEX(A1:B6;0;1) возвращает ссылку на первый столбец диапазона A1:B6.

=INDEX((multi);4;1) indicates the value contained in row 4 and column 1 of the (multiple) range, which you named under Sheet — Named Ranges and Expressions — Define as multi . The multiple range may consist of several rectangular ranges, each with a row 4 and column 1. If you now want to call the second block of this multiple range enter the number 2 as the range parameter.

=INDEX(A1:B6;1;1) возвращает значение левой верхней ячейки диапазона A1:B6.

=INDEX((составной);0;0;2) возвращает ссылку на второй диапазон составного диапазона.

CHOOSE

Эта функция использует индекс для возврата значения из списка, содержащего до 30 значений.

Syntax

CHOOSE(Index; Value1 [; Value2 [; . [; Value254]]])

Index is a reference or number between 1 and 254 indicating which value is to be taken from the list.

Value1, Value2, . Value254 is the list of values entered as a reference to a cell or as individual values.

Example

=CHOOSE(A1;B1;B2;B3; «Сегодня»; «Вчера»; «Завтра») , например, возвращает содержимое ячейки B2 для A1 = 2; для A1 = 4 функция возвращает текст «Сегодня».

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

Данные о количестве

Данные о ценах

Несоответствие позиций по ценам и количеству

Расположение соответствующих позиций

Введение функции ВПР

ВПР в Мастере функций

Диалоговое окно ВПР

Заполненные поля для ВПР

Копируем ВПР вниз

Особенности использования формулы ВПР в Excel

Разорвать связи

Диалоговое окно разрыва внешних связей

Примеры функции ВПР в Excel

Две таблицы

Результат работы ВПР

ЕСЛИОШИБКА и ВПР

Различные форматы у критериев

Преобразование текстового формата в числовой

Преобразование критерия в текст внутри ВПР

Текстовый критерий в первой таблице

Преобразование текста в число внутри ВПР

Смешанный формат критериев

Быстрый подсчет столбцов для ВПР

Данные для ВПР

Интервальный просмотр в ВПР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Аналог функции впр в openoffice

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

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

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

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

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

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

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

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

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

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

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

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

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

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

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

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

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

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

Функция ВПР имеет четыре параметра:

=ВПР( ; ; [; ] ), тут:

— искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра ;

— номер столбца в диапазоне, из которого будет возвращено значение;

— это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

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

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

Как же конкретно работает формула ВПР

Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

Формулы можно использовать для распределения значений по диапазонам.
Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II

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

Производительность работы функции ВПР

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

мне нужен более мощный компьютер;
мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Некоторые аспекты применения формулы в реальной жизни

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

Разный формат данных

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

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

Двойное отрицание —D7.
Умножение на единицу D7*1.
Сложение с нулём D7+0.
Возведение в первую степень D7^1.

Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.

Массив

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

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

Блог про LibreOffice

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

Страницы

2 октября 2014 г.

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

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

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

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

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

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

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

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

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

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

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

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

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

ВПР (VLOOKUP)

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

Примечания

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

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

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

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

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

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

Примеры

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

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

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

ВПР (функция ВПР)

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

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

Самая простая функция ВПР означает следующее:

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).

Используйте функцию ВПР для поиска значения в таблице.

ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

= ВПР (A2; A10: C20; 2; ИСТИНА)

= ВПР (a2; сведения о клиенте)! А:Ф, 3, ЛОЖЬ)

Значение для поиска. Значение, которое вы хотите найти, должно находиться в первом столбце диапазона ячеек, указанного в аргументе table_array .

Например, если Таблица-массив охватывает ячейки B2: D7, то lookup_value должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

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

Номер столбца (начиная с 1 для самого левого столбца table_array), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР («Смит»; a1: B100; 2; ложь).

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

Значение, которое вам нужно найти, то есть искомое значение.

Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.

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

Теперь объедините все перечисленное выше аргументы следующим образом:

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Вы можете использовать функцию ВПР для объединения нескольких таблиц в одну, если одна из них имеет общие поля. Это может быть особенно удобно, если вам нужно предоставить доступ к книге пользователям более ранних версий Excel, которые не поддерживают функции работы с данными с несколькими таблицами в качестве источников данных — путем объединения источников в одну таблицу и изменения источника данных компонента данных на новый. Таблица, функция данных может использоваться в более ранних версиях Excel (при условии, что сама функция данных поддерживается в более ранней версии).

Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.

Скопируйте таблицу с общими полями на новый лист и присвойте ей имя.

Чтобы открыть диалоговое окно Управление связями, нажмите кнопку данные > работа с данными > связей .

Для каждой из перечисленных связей обратите внимание на следующее:

Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.

Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.

Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.

Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце с помощью сведений, собранных в действии 3.

В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца (col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР ([@Attorney], tbl_Attorneys, 4, ложь).

В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; «Юристы»! А:Д, 4, ложь).

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