Чтобы придать формуле больше гибкости, создавая ссылку на определенный диапазон, Вы можете использовать функцию ДВССЫЛ. Эта функция принимает аргумент в виде текста, напоминающий ссылку на диапазон, после чего преобразует его в действующую ссылку на нужные ячейки. Ее используют редко, однако когда Вы поймете принцип работы этой функции, то сможете использовать ее чтобы создавать довольно мощные интерактивные электронные таблицы.

Косвенное указание нужных строк

На рис.1 Вы можете увидеть пример того, как используется функция ДВССЫЛ. Формула в ячейке Е5 будет выглядеть так:
= СУММ( ДВССЫЛ ( «B»&E2&»:B»&E3) ).

Рис.1 Чтобы просуммировать предоставленные пользователем строки, удобно будет применить функцию

Следует обратить внимание на то, что аргумент функции ДВССЫЛ содержит оператор конкатенации. В этом случае появляется возможность создания ссылки на необходимый диапазон, учитывая значения в полях Е2 и Е3. Так, если Е2 присвоить 2, а Е3 — 4, ссылка на этот диапазон будет выглядеть идентичной строке: «В2:В4».

Описываемая функция ДВССЫЛ позволяет преобразовать строку в действующую ссылку на диапазон, которая в свою очередь передается функции СУММ. По сути, формула возвращает значение: =СУММ (В2:В4). Когда меняются значения в ячейках Е2, Е3 — запись формулы обновляется, отображая суммы указанных пользователем строчек.

Косвенное указание названия листа

На рис. 2 виден другой пример. Здесь видно применение ссылки на лист. В столбце А листа «Итого» содержится текст, соответствующий другим листам этой книги. В столбце В содержатся формулы, ссылающиеся на эти пункты в тексте. Например, в ячейке В2 содержится следующая формула:
= СУММ (ДВССЫЛ (A2&»!F1:F10″) ).

Рис. 2. Информация в столбце «А» соответствует другим листам этой книги

Описанная формула объединяет ссылку на диапазон с текстом в А2. Функция ДВССЫЛ позволяет вычислить результат и преобразовать его в действующую ссылку на диапазон. Полученный результат будет эквивалентен формуле:
= СУММ ( Север!F1:F10).

Такая формула будет распространяться на другие ячейки столбца. Каждая формула определяет сумму диапазона F1:F10 на соответствующем листе.

Неизменная ссылка на ячейку

Второй вариант применения функции ДВССЫЛ заключается в том, чтобы создать ссылку на ячейку, которая не будет меняться никогда. В качестве примера рассмотрим формулу, суммирующую значения в первых двенадцати строках столбца: = СУММ (А1:А12).

Если добавить еще одну строку 1, то Excel автоматически изменит формулу на = СУММ (А2:А13). Иначе говоря, формула будет подстраиваться так, чтобы продолжать ссылаться на данные, указанные изначально (она уже не будет суммировать первые двенадцать строчек столбца А).

Чтобы не допустить автоматического изменения ссылок на ячейки в редакторе, следует использовать функцию ДВССЫЛ : = СУММ (ДВССЫЛ («А1:А12») ). Приведенная формула всегда будет позвращать сумму первых двенадцати строк столбца А.

Еще статьи и уроки Excel — по этой ссылке автора: itstolytsa.ua/nashy-kursy/beginning/ms-office

Автор публикации: Валерий Попов.