ДВССЫЛ. Ссылка на ячейку, чей адрес основан на вычислениях

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. Ранее я уже довольно подробно писал о функции ДВССЫЛ (см. Примеры использования функции ДВССЫЛ). Однако, учитывая то, что функция ДВССЫЛ весьма непроста для понимания, а также, что Джелен пишет очень интересно и попутно затрагивает различные побочные аспекты, я решил, что и эта заметка будет полезной.

Задача: вам нужно обратиться к ячейке, но ее адрес варьируется, основываясь на расчете.

Решение: функция ДВССЫЛ использует аргумент, который выглядит как ссылка на ячейку, и возвращает значение по этой ссылке (рис. 1). Например, формула в D2 запрашивает значение в D1, получает ответ С9, тут же обращается к ячейке С9 и возвращает текущее ее значение – 17. В Lotus 1-2-3, эта функция имела название @@ («на-на»-функция).

Рис. 1. Вы можете использовать ДВССЫЛ для указания адреса ячейки, и Excel вернет значение по этому адресу

Рис. 1. Вы можете использовать ДВССЫЛ для указания адреса ячейки, и Excel вернет значение по этому адресу

Скачать заметку в формате Word или pdf, примеры в формате Excel

Аргументом в ДВССЫЛ может быть именованный диапазон (рис. 2). Вы можете создать пять именованных диапазонов для указанных метрик. Выделите область В1:F10, и пройдите по меню ФОРМУЛЫ –> Создать из выделенного. В окне Создание имен из выделенного диапазона установите галочку в положение в строке выше. Кликните Ok.

Рис. 2. Именованные диапазоны для каждого столбца

Рис. 2. Именованные диапазоны для каждого столбца

В ячейке С12 (см. рис. 2) создан раскрывающийся список, в который включены 5 метрик. Чтобы создать такой список, встаньте на ячейку С12 и пройдите по меню ДАННЫЕ –> Проверка данных –> Проверка данных… (рис. 3). В окне Проверка вводимых значений выберите тип данных Список и в поле Источник укажите диапазон, который содержит список значений (в нашем примере – $С$1:$F$1). Удостоверьтесь, что установлен флажок Список допустимых значений.

Рис. 3. Создание выпадающего списка

Рис. 3. Создание выпадающего списка

Формула в В13 использует конкатенацию для построения правильного названия: =»Итого «&C12. Также подойдет формула =СЦЕПИТЬ(«Итого «;C12).

Формула в С13: =СУММ(ДВССЫЛ(C12)). Поскольку в С12 хранится Расходы, а Расходы – имя для диапазона Е2:Е10, формула возвращает сумму значений в ячейках Е2:Е10. Если в С12 ввести (выбрать из списка) EBITA, формула в С13 не изменится, но теперь вернет сумму значений в ячейках F2:F10.

Дополнительные сведения: аргумент функции ДВССЫЛ вычисляется на лету. Например (рис. 4), в ячейке C3 в аргументе функции ДВССЫЛ выполняется конкатенация номера (имени) столбца из С1 и номера строки из С2. Сами значения в С1 и С2 являются результатами случайного выбора функции СЛУЧМЕЖДУ. Эта функция возвращает случайное число между указанными границами (включая эти границы). В формуле =СИМВОЛ(СЛУЧМЕЖДУ(65;68)) значение 65 соответствует заглавной букве А (английской), а 68 – D. Откройте файл Excel перейдите на лист «Рис. 4», и нажимайте F9 (клавиша, принудительно пересчитывающая лист; при этом все, так называемые волатильные, они же летучие, функции пересчитываются). Понаблюдайте, как при каждом нажатии F9 меняются значения в ячейках С1:С3.

Рис. 4. Аргумент ДВССЫЛ получает ссылку на основе вычислений в ячейках С1 и С2

Рис. 4. Аргумент ДВССЫЛ получает ссылку на основе вычислений в ячейках С1 и С2

Резюме: функция ДВССЫЛ позволяет вычислить, на какую ячейку сослаться для извлечения ее значения.


Прокомментировать