Функции поиска

СТРОКА (ROW)

СТРОКА([ссылка])

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

Аргументы

Аргумент	Тип	Обязательный
ссылка	Ссылка	Нет

Примеры

Формула	Результат
СТРОКА(A5)	5
СТРОКА()	1

СТОЛБЕЦ (COLUMN)

СТОЛБЕЦ([ссылка])

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

Аргументы

Аргумент	Тип	Обязательный
ссылка	Ссылка	Нет

Примеры

Формула	Результат
СТОЛБЕЦ(C5)	3
СТОЛБЕЦ()	1

ЧСТРОК (ROWS)

ЧСТРОК(массив)

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

Аргументы

Аргумент	Тип	Обязательный
массив	Массив	Да

Примеры

Формула	Результат
ЧСТРОК(A1:B10)	10
ЧСТРОК({1\2\3;4\5\6})	2

ЧИСЛСТОЛБ (COLUMNS)

ЧИСЛСТОЛБ(массив)

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

Аргументы

Аргумент	Тип	Обязательный
массив	Массив	Да

Примеры

Формула	Результат
ЧИСЛСТОЛБ(A1:E1)	5
ЧИСЛСТОЛБ({1\2\3;4\5\6})	3

ДВССЫЛ (INDIRECT)

ДВССЫЛ(адрес_ячейки)

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

Аргументы

Аргумент	Тип	Обязательный
адрес_ячейки	Ссылка	Да

Примеры

Формула	Результат
ДВССЫЛ("Cn7")	{}
ДВССЫЛ("F" & B0)	—

ВПР (VLOOKUP)

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

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

Если отсортирован равно TRUE, функция использует алгоритм бинарного поиска, поэтому первый столбец диапазон_поиска должен быть отсортирован так, чтобы меньшие значения были сверху, а большие — снизу; иначе результат функции будет бессмысленным. Если отсортирован опущено, предполагается значение false.

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

Аргументы

Аргумент	Тип	Обязательный
искомое_значение	Любое	Да
диапазон_поиска	Диапазон	Да
столбец_результата	Любое	Да
отсортирован	Логический	Нет

Примеры

Формула	Результат
ВПР(17; A1:C10; 3)	—
ВПР(17; A1:C10; 2; FALSE)	—

ГПР (HLOOKUP)

ГПР(искомое_значение; диапазон_поиска; строка_результата; [отсортирован])

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

Если отсортирован равно TRUE, функция использует алгоритм бинарного поиска, поэтому первая строка диапазон_поиска должна быть отсортирована так, чтобы меньшие значения были слева, а большие — справа; иначе результат функции будет бессмысленным. Если отсортирован опущено, предполагается значение false.

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

Аргументы

Аргумент	Тип	Обязательный
искомое_значение	Любое	Да
диапазон_поиска	Диапазон	Да
строка_результата	Текст	Да
отсортирован	Логический	Нет

Примеры

Формула	Результат
ГПР(17; A1:Z3; 3)	—
ГПР(17; A1:Z3; 2; FALSE)	—

ПРОСМОТРX (XLOOKUP)

ПРОСМОТРX(искомое_значение; диапазон_поиска; диапазон_результата; [значение_если_не_найдено]; [режим_совпадения]; [режим_поиска])

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

диапазон_поиска должен быть либо одной строкой, либо одним столбцом.

Режимы совпадения

Существует четыре режима совпадения:

• 0 — точное совпадение (по умолчанию)
• -1 — следующее меньшее
• 1 — следующее большее
• 2 — шаблон

Подробнее о том, как работают шаблоны подстановки в формулах, см. в документации.

Режимы поиска

Существует четыре режима поиска:

• 1 — линейный поиск (по умолчанию)
• -1 — обратный линейный поиск
• 2 — бинарный поиск
• -2 — обратный бинарный поиск

Линейный поиск находит первое совпадающее значение, а обратный линейный поиск — последнее совпадающее значение.

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

Бинарный поиск несовместим с режимом совпадения по шаблону.

Результат

Если диапазон_поиска — это строка, она должна иметь ту же ширину, что и диапазон_результата, чтобы каждое значение в диапазон_поиска соответствовало столбцу в диапазон_результата. В этом случае ось поиска — вертикальная.

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

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

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

Аргументы

Аргумент	Тип	Обязательный
искомое_значение	Любое	Да
диапазон_поиска	Диапазон	Да
диапазон_результата	Диапазон	Да
значение_если_не_найдено	Любое	Нет
режим_совпадения	Число	Нет
режим_поиска	Число	Нет

Примеры

Формула	Результат
ПРОСМОТРX("zebra"; A1:Z1; A4:Z6)	—
ПРОСМОТРX({"zebra"; "aardvark"}; A1:Z1; A4:Z6)	—
ПРОСМОТРX(50; C4:C834; B4:C834; {-1\ 0\ "not found"}; -1; 2)	—

ПОИСКПОЗ (MATCH)

ПОИСКПОЗ(искомое_значение; диапазон_поиска; [режим_совпадения])

Ищет значение в диапазоне и возвращает индекс первого совпадения, начиная с 1.

Если режим_совпадения равен 1 (по умолчанию), возвращается индекс наибольшего значения, меньшего искомое_значение. В этом режиме диапазон_поиска должен быть отсортирован по возрастанию: меньшие значения сверху или слева, большие — снизу или справа; иначе результат функции будет бессмысленным.

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

Если режим_совпадения равен 0, возвращается индекс первого значения, равного искомое_значение. В этом режиме диапазон_поиска может быть в любом порядке. искомое_значение также может быть шаблоном.

Подробнее о том, как работают шаблоны подстановки в формулах, см. в документации.

Аргументы

Аргумент	Тип	Обязательный
искомое_значение	Любое	Да
диапазон_поиска	Диапазон	Да
режим_совпадения	Число	Нет

Примеры

Формула	Результат
ПОИСКПОЗ(12; {10\ 20\ 30})	1
ПОИСКПОЗ(19; {10\ 20\ 30}; -1)	3
ПОИСКПОЗ("A"; {"a"; "b"; "c"}; 0)	1

ПРОСМОТР (LOOKUP)

ПРОСМОТР(искомое_значение; диапазон_поиска; [диапазон_результата])

Ищет значение в диапазоне из одной строки или одного столбца и возвращает соответствующее значение из другого диапазона из одной строки или одного столбца.

Это векторная форма LOOKUP. Если диапазон_результата опущен, то диапазон_поиска используется как для поиска, так и для возврата значений.

Значения в диапазон_поиска должны быть отсортированы по возрастанию, иначе результат может быть неверным. LOOKUP находит наибольшее значение, меньшее или равное искомое_значение.

Если значение не найдено (все значения больше искомое_значение), возвращается ошибка.

Аргументы

Аргумент	Тип	Обязательный
искомое_значение	Любое	Да
диапазон_поиска	Диапазон	Да
диапазон_результата	Диапазон	Нет

Примеры

Формула	Результат
ПРОСМОТР(5; {1\ 2\ 3\ 4\ 5})	5
ПРОСМОТР("c"; {"a"\ "b"\ "c"}; {1\ 2\ 3})	3
ПРОСМОТР(50; A1:A10; B1:B10)	—

XMATCH

XMATCH(искомое_значение; диапазон_поиска; [режим_совпадения]; [режим_поиска])

Ищет значение в диапазоне и возвращает индекс первого совпадения, начиная с 1.

Это расширенная версия MATCH с более гибкими параметрами совпадения и поиска.

• искомое_значение: Искомое значение.
• диапазон_поиска: Диапазон из одной строки или одного столбца для поиска.
• режим_совпадения: Необязательный. Как искать совпадение:
  • 0 (по умолчанию): точное совпадение
  • -1: точное совпадение или следующее меньшее
  • 1: точное совпадение или следующее большее
  • 2: совпадение по шаблону (*, ? , ~)
• режим_поиска: Необязательный. Как искать:
  • 1 (по умолчанию): от первого к последнему
  • -1: от последнего к первому
  • 2: бинарный поиск по возрастанию (диапазон должен быть отсортирован по возрастанию)
  • -2: бинарный поиск по убыванию (диапазон должен быть отсортирован по убыванию)

Подробнее о том, как работают шаблоны подстановки в формулах, см. в документации.

Аргументы

Аргумент	Тип	Обязательный
искомое_значение	Любое	Да
диапазон_поиска	Диапазон	Да
режим_совпадения	Число	Нет
режим_поиска	Число	Нет

Примеры

Формула	Результат
XMATCH("b"; {"a"\ "b"\ "c"})	2
XMATCH(5; {1\ 3\ 5\ 7\ 9}; 0)	3
XMATCH(4; {1\ 3\ 5\ 7\ 9}; 1)	3
XMATCH(4; {1\ 3\ 5\ 7\ 9}; -1)	2

АДРЕС (ADDRESS)

АДРЕС(номер_строки; номер_столбца; [тип_ссылки]; [стиль_a1]; [имя_листа])

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

• номер_строки: Номер строки (с единицы).
• номер_столбца: Номер столбца (с единицы).
• тип_ссылки: Необязательный. Тип ссылки:
  • 1 или опущен: абсолютная ($A$1)
  • 2: абсолютная строка, относительный столбец (A$1)
  • 3: относительная строка, абсолютный столбец ($A1)
  • 4: относительная (A1)
• стиль_a1: Необязательный. TRUE для стиля A1 (по умолчанию), FALSE для стиля R1C1.
• имя_листа: Необязательный. Имя листа, которое нужно включить.

Аргументы

Аргумент	Тип	Обязательный
номер_строки	Число	Да
номер_столбца	Число	Да
тип_ссылки	Число	Нет
стиль_a1	Любое	Нет
имя_листа	Текст	Нет

Примеры

Формула	Результат
АДРЕС(1; 1)	"$A$1"
АДРЕС(1; 1; 4)	"A1"
АДРЕС(1; 1; 1; TRUE; "Лист1")	"Лист1!$A$1"

ОБЛАСТИ (AREAS)

ОБЛАСТИ(диапазон)

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

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

Аргументы

Аргумент	Тип	Обязательный
диапазон	Диапазон	Да

Примеры

Формула	Результат
ОБЛАСТИ(A1:C3)	1
ОБЛАСТИ((A1:C3; D1:E5))	2
ОБЛАСТИ((A1; B2; C3:D4))	3

ИНДЕКС (INDEX)

ИНДЕКС(диапазон; [строка]; [столбец]; [номер_диапазона])

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

Если диапазон — это группа из нескольких ссылок на диапазоны, то дополнительный параметр номер_диапазона указывает, из какого диапазона брать индекс.

Когда диапазон — это ссылка на диапазон или группа ссылок на диапазоны, INDEX можно использовать как часть новой ссылки на диапазон.

Аргументы

Аргумент	Тип	Обязательный
диапазон	Диапазон	Да
строка	Текст	Нет
столбец	Любое	Нет
номер_диапазона	Число	Нет

Примеры

Формула	Результат
ИНДЕКС({1\ 2\ 3; 4\ 5\ 6}; 1; 3)	3
ИНДЕКС(A1:A100; 42)	—
ИНДЕКС(A6:Q6; 12)	—
ИНДЕКС((A1:B6; C1:D6; D1:D100); 1; 5; C6)	—
E1:ИНДЕКС((A1:B6; C1:D6; D1:D100); 1; 5; C6)	—
ИНДЕКС((A1:B6; C1:D6; D1:D100); 1; 5; C6):E1	—
ИНДЕКС(A3:Q3; A2):ИНДЕКС(A6:Q6; A2)	—

СМЕЩ (OFFSET)

СМЕЩ(ссылка; смещение_строк; смещение_столбцов; [высота]; [ширина])

Возвращает ссылку, смещённую от заданной исходной ссылки на указанное количество строк и столбцов.

• ссылка: Исходная ссылка.
• смещение_строк: Количество строк смещения (может быть отрицательным).
• смещение_столбцов: Количество столбцов смещения (может быть отрицательным).
• высота: Необязательный. Высота (количество строк) возвращаемой ссылки. По умолчанию равна высоте исходной ссылки.
• ширина: Необязательный. Ширина (количество столбцов) возвращаемой ссылки. По умолчанию равна ширине исходной ссылки.

OFFSET возвращает ссылку, которую можно использовать в других формулах.

Аргументы

Аргумент	Тип	Обязательный
ссылка	Ссылка	Да
смещение_строк	Число	Да
смещение_столбцов	Число	Да
высота	Число	Нет
ширина	Число	Нет

Примеры

Формула	Результат
СМЕЩ(A1; 2; 3)	—
СМЕЩ(A1:B2; 1; 1)	—
СМЕЩ(A1; 0; 0; 5; 5)	—
СУММ(СМЕЩ(A1; 0; 0; 10; 1))	—

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA)

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ([аргументы...])

Возвращает агрегированное значение из табличных данных, отфильтрованных по парам «поле = значение».

• поле_данных — имя столбца со значениями (заголовок строки 1).
• таблица — диапазон с заголовками в первой строке.
• Дополнительные пары (поле; значение_фильтра) фильтруют строки по значениям в столбцах с указанными именами.
• Возвращает сумму подходящих числовых значений; если ни одна строка не подошла или поля не найдены — #N/A.

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

Функции импорта внешних данных (IMPORTRANGE, IMPORTDATA, IMPORTHTML, IMPORTXML) вынесены в раздел «Веб-функции».

Аргументы

Аргумент	Тип	Обязательный
аргументы	Любое	Нет (можно несколько)

Примеры

Формула	Результат
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Sales"; A1:C10; "Region"; "North")	—
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Total"; A1:D20)	—