При работе с большими списками (таблицами) для быстрого получения отдельных записей из этих списков, можно использовать функции подстановок. Функции поиска используются для поиска связанных записей в таблицах. При использовании таких функций задача, по существу, формулируется следующим образом — есть значения, по которым нужно найти совпадение в другой таблице и получить в ответ значение, которое хранится в ячейке, соответствующей строки и столбца этой другой таблицы.
Для решения подобных задач часто применяют функции ВПР и ГПР, однако они накладывают ряд ограничений на использование. Более универсальные функции — ПОИСКПОЗ и ИНДЕКС, их использование не зависит от расположения данных в таблицах, из которых осуществляется подстановка.
Использование функции ДВССЫЛ, АДРЕС, СТРОКА и СТОЛБЕЦ, ТРАНСП, СМЕЩ
Функция АДРЕС используется для получения адреса ячейки на листе, для которой указаны номера строки и столбца.
АДРЕС (Номер_строки; Номер_столбца; Тип_ссылки; А1; Имя_листа) — возвращает ссылку на одну ячейку рабочего листа в виде текста.
ADDRESS (Row_num; Column_num; Abs_num; A1; Sheet_text).
Номер_строки [Row_num] - номер строки, используемый в ссылке на ячейку.
Номер_столбца [Column_num] - номер столбца, используемый в ссылке на ячейку.
Тип_ссылки [Abs_num] - значение от 1 до 4, определяет тип ссылки:
- · 1 или опущен — $A$ 1 (абсолютная ссылка)
- · 2 — A$ 1 (абсолютная строка; относительный столбец)
- · 3 — $A1 (относительная строка; абсолютный столбец)
- · 4 — A1 (относительная ссылка)
A1 [A1] - определяет тип ссылок: А1 или R1C1Microsoft Excel 2010.
- · 1 (ИСТИНА) или опущен — стиль ссылки A1.
- · 0 (ЛОЖЬ) — стиль ссылки R1C1.
Имя_листа [Sheet_text] - текстовое значение, определяющее имя листа. Если аргумент отсутствует, то адрес, возвращаемый функцией, ссылается на ячейку текущего листа.
ПРИМЕР: Определить значение ячейки, которое находится на листе Заказы в 4-й строке и 3-м столбце.
=ДВССЫЛ (АДРЕС (C3;C4;C5;C6;C2)) — функция АДРЕС формирует адрес ячейки (Заказы!$C$ 4), а функция ДВССЫЛ возвращает значение из указанного адреса ячейки.
ПРИМЕР: Транспонировать вертикальную таблицу (ячейки B2: E6) в горизонтальную таблицу (ячейки G2: K5).
=ДВССЫЛ (АДРЕС (СТОЛБЕЦ ()-5;СТРОКА (B2))) — выдает результат из адреса ячейки, полученного функцией АДРЕС по номеру строки СТОЛБЕЦ ()-5 (-5 т.к. столбец, в который помещается результат находится на 5 столбцов правее) и номеру столбца СТРОКА (B2).
