Функции просмотра и ссылки
Представленные ниже функции используются в первую очередь при работе со ссылками. Сюда относится определение адресов ячеек, размеров диапазонов ячеек и т. п.
АДРЕС
Синтаксис:
АДРЕС(номер_строки, номер_столбца, тип_ссылки, a1, имя_листа)
Результат: Адрес ячейки (в текстовом виде), формируемый на основе номеров строки и столбца.
Аргументы:
- номер_строки - номер строки;
- номер_столбца - номер столбца;
- тип_ссылки - задание типа возвращаемой ссылки; может принимать следующие значения:
Значение аргумента Тип возвращаемой ссылки 1 или опущен Абсолютный 2 Абсолютная строка; относительный столбец 3 Относительная строка; абсолютный столбец 4 Относительный - a1 - логическое значение, которое определяет стиль ссылок: А1 или R1C1; если аргумент al имеет значение ИСТИНА или опущен, то функция АДРЕС возвращает ссылку в стиле А1; если этот аргумент имеет значение ЛОЖЬ, то функция АДРЕС возвращает ссылку в стиле R1C1;
- имя_листа - текст, определяющий имя рабочего листа или листа макросов, который используется для формирования внешней ссылки; если аргумент имя_листа опущен, то внешние листы не используются.
ВПР
Синтаксис:
ВПР(искомое_значение, инфо_таблица, номер_столбца, интервальный_просмотр)
Результат: В матрице инфо_таблица ищется строка, первая колонка которой содержит величину искомое_значение. В найденной строке из колонки номер_столбца извлекается значение и возвращается функцией.
Аргументы:
- искомое_значение - задает значение, которое функция ищет в первой колонке матрицы (если это значение не будет найдено, будет взято ближайшее меньшее; если меньшего не существует, возникнет ошибка #Н/Д);
- инфо_таблица - таблица, содержащая искомые данные;
- номер_столбца - колонка в найденной строке, из которой должно быть взято значение;
- интервальный_просмотр - логическое значение, которое определяет характер поиска: точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
ПРИМЕЧАНИЕ
Сравните работу функций ВПР и ГПР. Последняя работает так же, как ВПР, если поменять местами колонки и строки. В матрице инфо^таблица первая колонка, содержащая критерии поиска, должна быть упорядочена по возрастанию от наименьшего до наибольшего элемента; сначала числа, затем буквы, затем логические значения.
ВЫБОР
Синтаксис:
ВЫБОР(номер_индекса, значение1, значение2,...)
Результат: Использует аргумент номер_индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. Функция ВЫБОР применяется, чтобы выбрать одно значение из списка, в котором может быть до 30 значений. Например, если значения от значение1 до значение7- это дни недели, то функция ВЫБОР возвращает один из дней при условии, что число от 1 до 7 использовано в качестве аргумента номер_индекса.
Аргументы:
- номер_индекса- номер выбираемого аргумента-значения (аргумент номер_индекса должен быть числом от 1 до 29, формулой или ссылкой на ячейку, содержащую число от 1 до 29; если аргумент номер_индекса равен 1, то функция ВЫБОР возвращает аргумент значение1; если он равен 2, то функция ВЫБОР возвращает аргумент значение2 и т. д.; если аргумент номер_индекса меньше 1 или больше, чем номер последнего значения в списке, то функция ВЫБОР возвращает значение ошибки #ЗНАЧ!; если аргумент номер_индекса является дробным, то он округляется до ближайшего меньшего целого);
- значение1, значение2, ... - от 1 до 30 аргументов-значений, из которых функция ВЫБОР, используя аргумент номер_индекса, выбирает значение или выполняемое действие; аргументы могут быть числами, ссылками на ячейки, именами, формулами, макрофункциями или текстовыми строками.
ГИПЕРССЫЛКА
Синтаксис:
ГИПЕРССЫЛКА(адрес_документа, имя)
Результат: Создание гипертекстовой ссылки на документ, хранящийся на сервере локальной сети или на узле Internet. При перемещении курсора в ячейку с гиперссылкой Excel открывает файл, указанный в ссылке.
Аргументы:
- адрес_документа - полный путь к документу, с которым устанавливается гиперсвязь. Адрес может быть ссылкой на определенную область документа (например, адресом ячейки или именем диапазона) или на файл на локальном жестком диске. Адрес может представлять собой универсальный локатор ресурсов, если ссылка дается на документ в сети Internet или intranet. Задается он в виде текстовой строки, заключенной в кавычки.
- имя - текст или числовое значение, отображаемое в ячейке, которая содержит гипертекстовую ссылку. Если данный аргумент опущен, то в ячейке отображается значение адреса. Выделяется голубым цветом.
ГПР
Синтаксис:
ГПР(искомое_значение, инфо_таблица, номер_строки, интервальный_просмотр)
Результат: Значение, которое берется на основе критерия поиска из заданной строки (номер_строки) матрицы (инфо_таблица).
Аргументы:
См. функцию ВПР; описание аргументов функции справедливо для функции ГПР, если поменять местами колонки и строки.
ДВССЫЛ
Синтаксис:
ДВССЫЛ(ссылка_на_ячейку, a1)
Результат: Ссылка, заданная аргументом ссылка_на_ячейку. Ссылки немедленно вычисляются для вывода их содержимого. Функция ДВССЫЛ используется для того, чтобы получить значение, находящееся в ячейке, ссылка на которую находится в другой ячейке.
Аргументы:
- ссылка_на_ячейку - ссылка на ячейку, которая содержит либо ссылку в стиле А1, либо ссылку в стиле R1C1, либо имя, определенное как ссылка; если аргумент ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ возвращает значение ошибки #ССЫЛ!;
- a1 - логическое значение, указывающее, какого типа ссылка содержится в аргументе ссылка_на_ячейку; если аргумент a1 опущен или имеет значение ИСТИНА, то аргумент ссъшка_на_ячейку интерпретируется как ссылка в стиле А1; если a1 имеет значение ЛОЖЬ, то аргумент ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.
ИНДЕКС (версия для адресов)
Синтаксис:
ИНДЕКС(ссылка, номер_строки, номер_столбца, номер_области)
Результат: Адрес.
Аргументы:
- ссылка - адрес ячейки или диапазона ячеек;
- номер_строки - задает строку в диапазоне, для которого определяется адрес; если все диапазоны, указанные в аргументе ссылка, имеют только одну строку, то аргумент номер_строки может быть опущен; если аргумент номер_ строки устанавливается равным 0, то адресоваться будет весь столбец;
- номер_столбца - задает столбец в диапазоне, для которого определяется адрес (если все диапазоны, указанные в аргументе ссылка, имеют только один столбец, то аргумент номер_столбца может быть опущен; если аргумент но-мер_столбца устанавливается равным 0, то адресоваться будет вся строка);
- номер_области - если аргумент ссылка содержит ссылку на диапазон ячеек, то аргумент номер_области определяет номер диапазона (если опущен, то будет выбран первый диапазон).
ИНДЕКС (версия для матриц)
Синтаксис:
ИНДЕКС(массив, номер_строки, номер_столбца)
Результат: Значение или матрица значений.
Аргументы:
- массив - матрица, из которой должны быть взяты значения;
- номер_строки - строка, из которой должны быть взяты значения;
- номер_столбца - аналогичен аргументу номер_строкщ если аргумент номер_строки или номер_столбца равен 0, функция возвращает значения всего столбца или всей строки соответственно.
ОБЛАСТИ
Синтаксис:
ОБЛАСТИ(ссылка)
Результат: Количество диапазонов в ссылке. (Диапазон - это интервал смежных ячеек или отдельная ячейка.)
Аргументы:
- ссылка - ссылка на ячейку или интервал ячеек; может относиться к нескольким диапазонам.
ПОИСКПОЗ
Синтаксис:
ПОИСКПОЗ(искомое_значение, проcматриваемый_массив, тип_сопоставления)
Результат: Относительная позиция элемента массива просматриваемый_массив (искомой матрицы), который соответствует определенному значению искомое_значение (критерию поиска) указанным образом тип_сопоставления.
Аргументы:
- искомое_значение - значение, используемое при поиске значения в таблице (аргумент искомое_значение - это значение, для которого ищется соответствие в аргументе просматриваемый_массив например, при поиске номера телефона в телефонной книге вы используете имя человека как искомое значение (искимое_значение), но при этом значение, которое вам нужно получить, - номер телефона; аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение);
- просматриваемый_массив - непрерывный интервал ячеек, которые, возможно, содержат искомые значения; аргумент про-сматриваемый_массив может быть массивом или ссылкой на массив;
- тип_сопоставления - число -1, 0 или 1. Аргумент тип_сопоставления задает способ сопоставления значения аргумента искомое_значение со значениями в аргументе просматриваемый_массив. Если аргумент тип_сопоставления равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше аргумента искомое_значение аргумент просматриваемый_массив должен быть упорядочен по возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА. Если аргумент тип сопоставления равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Аргумент тип сопоставления может быть упорядочен любым способом. Если аргумент тип_сопоставления равен -1, то функция ПОИСКПОЗ находит наименьшее значение, которое равно или больше аргумента искомое_значение. Аргумент просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, -1, -2, ..., и т. д. Если аргумент тип_сопоставления опущен, то предполагается, что он равен 1.
ПРОСМОТР (векторная форма)
Синтаксис:
ПРОСМОТР(искомое_значение, просматриьаемый_вектор, вектор_результатов)
Результат: Векторная форма функции ПРОСМОТР просматривает вектор и находит указанное значение, переходит в соответствующую позицию второго вектора и возвращает значение оттуда.
Аргументы:
- искомое_значение - любое значение (если в аргуменге просматриваемый_вектор оно не найдено, то выбирается следующее меньшее значение);
- просматриваемый_вектор - одномерная матрица с текстами, числами или логическим значениями в порядке возрастания: числа, буквы, логические значения;
- вектор_результатов - одномерная матрица.
ПРОСМОТР (матричная форма)
Синтаксис:
ПРОСМОТР(искомое_значение, массив)
Результат: Значение, которое берется на основе критерия поиска из матрицы.
Аргументы:
- искомое_значение - любое значение;
- массив - любая матрица.
ПРИМЕЧАНИЕ
Если матрица является квадратной или имеет больше колонок, чем строк, функция ПРОСМОТР ищет в первой строке критерий поиска; если же в ней больше строк, чем колонок, проводится поиск в первой колонке. Результатом функции в любом случае является последнее значение в найденной колонке (если поиск проводился в первой строке) или строке (если поиск проводился в первой колонке).
СМЕЩ
Синтаксис:
СМЕЩ(ссылка, смещение_по_строкам, смещение_по_столбцам, высота, ширина)
Результат: Адрес диапазона ячеек, имеющего заданные высоту и ширину и смещенного относительно указанного адреса.
Аргументы:
- ссылка - адрес точки отсчета (мультивыбор запрещен);
- смещение_по_строкам, смещение_по_столбцам - величина смещения вниз или вправо;
- высота, ширина - определяют размер нового диапазона (если отсутствует, то в качестве размера будет использован аргумент ссылка).
СТОЛБЕЦ
Синтаксис:
СТОЛБЕЦ(ссылка)
Результат: Номер столбца по заданной ссылке.
Аргументы:
- ссылка - ячейка или интервал ячеек, для которых определяется номер столбца; если аргумент ссылка опущен, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТОЛБЕЦ. Если ссылка является интервалом ячеек и функция СТОЛБЕЦ введена как горизонтальный массив, то возвращаются номера столбцов в ссылке в виде горизонтального массива. Аргумент ссылка не может ссылаться на несколько диапазонов ячеек.
СТРОКА
Синтаксис:
СТРОКА(ссылка)
Результат: Номер строки адресуемого диапазона, если она содержит одну ячейку. Если адресуемый диапазон ячеек содержит более одной строки, - номера строк в виде вертикальной матрицы.
Аргументы:
- ссылка - адрес ячейки; если аргумент не задан, функция относится к своей ячейке.
ТРАНСП
Синтаксис:
ТРАНСП(массив)
Результат: Транспонированный массив. Функция ТРАНСП должна быть введена как формула массива в интервал, который имеет столько же строк и столбцов, сколько столбцов и строк имеет аргумент массив. Функция ТРАНСП используется для того, чтобы поменять ориентацию массива на рабочем листе или листе макросов с вертикальной на горизонтальную и наоборот. Например, некоторые функции, такие как ДОКУМЕНТЫ, возвращают горизонтальные массивы. Следующая формула возвращает вертикальный массив - результат работы функции ДОКУМЕНТЫ: ТРАНСП(ДОКУМЕНТЫ()).
Аргументы:
- массив - транспонируемый массив на рабочем листе или на листе макросов. Массив может быть интервалом ячеек. Транспонирование заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вторым столбцом нового массива и т. д.
ПРИМЕЧАНИЕ
Как уже упоминалось, существуют особые технические приемы ввода для всех формул, которые в качестве результата дают матрицу. Ввод матричной формулы должен завершаться нажатием комбинации клавиш [Ctrl+Shift+Enter].
ЧИСЛСТОЛБ
Синтаксис:
ЧИСЛСТОЛБ(массив)
Результат: Количество столбцов в ссылке или массиве.
Аргументы:
- массив - массив, либо формула, с помощью которой производится обработка массива, либо ссылка на интервал ячеек, для которых определяется количество столбцов.
ЧСТРОК
Синтаксис:
ЧСТРОК(массив)
Результат: Количество строк в матрице.
Аргументы:
- массив - матрица (заданная в аргументе в фигурных скобках) или адрес матрицы в таблице.