Если тип_сопоставления равен -1, функция ПОИСКПОЗ ищет в диапазоне наименьшее значение, которое больше или равно искомому значению. В этом случае элементы списка должны быть отсортированы по убыванию. Если в диапазоне нет элементов, которые больше или равны искомому значению, функция возвращает ошибочное значение #Н/Д.

Аргумент искомое_значение и элементы в диапазоне могут быть также текс­товыми строками. Например, если ячейки A1:D1 содержат текстовые значе­ния, как показано на рис.11, следующая формула возвратит значение 2: =ПОИСКПОЗ("Двадцать";А1:01;0)

При использовании функции ПОИСКПОЗ для определения позиции тексто­вых строк аргумент тип_сопоставления обычно задается равным 0 (полное совпадение). В этом случае в аргументе искомое_значение можно использо­вать символы шаблона * и ?.

Рис. 11. Функцию ПОИСКПОЗ можно использовать для определения позиции текстовой строки

5.4. Функция ПРОСМОТР

Функция ПРОСМОТР имеет две синтаксические формы. В обе­их формах она аналогична функциям ВПР и ГПР и подчиняется тем же пра­вилам.

5.4.1. Первая форма

Первая (векторная) форма функции ПРОСМОТР:

=ПРОСМОТР(искомое_значение;просмагриваемый_вектор;вектор_результатов)

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

Подобно функциям ГПР и ВПР, функция ПРОСМОТР ищет в просматрива­емом векторе наибольшее сравниваемое значение, не превышающее искомое значение. Затем она выбирает значение из соответствующей позиции в векторе_результатов. Хотя обычно просматриваемый_вектор и вектор_ резуль­татов размещаются в листе параллельно, это совсем не обязательно. Они могут быть расположены в разных областях листа, кроме того, один диапазон может быть горизонтальным, а другой вертикальным. Единственное ограниче­ние состоит в том, что они должны иметь одинаковое число элементов. Например, рассмотрим формулу в листе на рис.12. =ПРОСМОТР(3;ВЗ:В7;ЕЗ:Е7)

Эта формула сравнивает Искомое_значение 3 со значениями в просматриваемом_векторе ВЗ:В7. Третья ячейка этого диапазона (В5) содержит наиболь­шую величину, которая не превосходит искомое значение. Таким образом, функция возвращает число 300, а именно содержимое третьей ячейки диапа­зона результатов (Е5).

Рис.12. Функцию ПРОСМОТР можно использовать для извлечения информации из заданного диапазона

Теперь рассмотрим формулу в листе на рис.13 с непараллельными диапа­зонами. =ПРОСМОТР(3;А1:А5;06:Н6)

Эта формула возвращает значение 300. Просматриваемый_вектор, А1:А5, и вектор результатов, D6:H6, имеют по пять элементов. Искомое_значение 3 совпадает с числом в третьей ячейке просматриваемого_вектора, и, таким образом, формула возвращает содержимое третьей ячейки диапазона резуль­татов: 300.

Рис. 13. Функция ПРОСМОТР позволяет извлекать информацию из непараллельных, диапазонов ячеек

5.4.2. Вторая форма

Вторая синтаксическая форма функции ПРОСМОТР предназначена для ра­боты с массивами: =ПРОСМОТР(искомое_значение; массив)

Аргумент искомое_значение — это значение, которое функция ПРОСМОТР ищет в массиве. Функция ПРОСМОТР всегда возвращает значение, получен­ное из последней строки или столбца массива.

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

Поскольку поведение функций ГПР и ВПР более предсказуемо и управляемо, предпочтительнее использовать именно их, а не функцию ПРОСМОТР.

5.5. Функция ИНДЕКС

Функция ИНДЕКС тоже имеет две формы. Одна предназначена для работы с массивами и возвращает одно или несколько значений, а другая возвращает ссылку на ячейку или диапазон ячеек рабочего листа.

5.5.1. Первая форма

Первая синтаксическая форма функции ИНДЕКС работает только с массивом аргументов и возвращает значения, а не ссылки на ячейки. Эта форма функ­ции имеет следующий синтаксис: =ИНДЕКС(массив;номер_строки;номер_столбца)

Функция возвращает значение элемента массива, заданного номером строки и номером столбца. Например, следующая формула возвращает значение 20, поскольку 20 — это значение во втором столбце первой строки массива:

=ИНДЕКС( {ДО; 20; 30:40; 50; 60}; 1; 2)

5.5.2. Вторая форма

Вторая синтаксическая форма функции ИНДЕКС возвращает адрес ячейки. Она полезна в тех случаях, когда нужно выполнить операции с

ячейками (например, изменить ширину ячейки), а не с их значениями. Эта функция может, иногда вызвать замешательство, поскольку если она вложена в другую функцию, то последняя может использовать значение в ячейке, адрес которой возвращается функцией ИНДЕКС. Более того, ссылочная форма функций ИНДЕКС не отображает свой результат как адрес, а выводит значение (значения), находящееся по этому адресу. Важно запомнить, что результатом функции ИНДЕКС является именно адрес, даже если этот результат выглядит совсем иначе.

Функция ИНДЕКС имеет два достоинства: во-первых, в качестве просматри­ваемого диапазона может использоваться область листа, состоящая из не­скольких несмежных диапазонов; во-вторых, функция может возвратить диа­пазон (несколько ячеек). Ссылочная форма этой функции имеет следующий синтаксис:

=ИНДЕКС( ссылка; номер_строки; номер_столбца; номер_области)

Аргумент ссылка может быть одним или несколькими диапазонами, которые называются областями (areas). Каждая область должна быть прямоугольной и может содержать числа, текстовые значения или формулы. Если области несмежные, аргумент ссылка должен быть заключен в круглые скобки.

Аргументы номер_строки и номер_столбца должны быть положительными числами (или ссылками на ячейки, содержащие числа), которые задают ячей­ку в аргументе ссылка. Если аргумент номер_строки больше числа строк в таблице или аргумент номер_столбца больше числа столбцов, функция ИНДЕКС возвращает ошибочное значение #ССЫЛКА!.

Если все области в аргументе ссылка содержат только одну строку, указывать номер_строки не обязательно. Подобным образом, аргумент номер_столбца является необязательным, если все области содержат только один столбец. Если в качестве аргумента номер_строки или номер_столбца указан 0, функция ИНДЕКС возвращает ссылку соответственно для всей строки или столбца.

Аргумент номер_области требуется только в том случае, если аргумент ссыл­ка содержит несколько областей. Номер_области задает область в аргументе ссылка в дополнение к аргументам номер_строки и номер_столбца. Область, заданная в аргументе ссылка первой, имеет номер 1, второй — 2 и т. д. Если аргумент номер_области опущен, он считается равным 1. Этот аргумент всегда должен быть положительным целым числом. Если номер_области мень­ше 1, функция возвращает ошибочное значение #ССЫЛКА!.