Для меня одной из самых загадочных функций Excel является ДВССЫЛ. Как она работает, я понимаю не на 100%. Но это не мешает мне её использовать в той части, которую я понимаю Функция ДВССЫЛ является почти незаменимой, когда в формуле нужно сослаться на какую-то ячейку, а на какую именно, становится понятным только в результате вычислений. Аргумент функции ДВССЫЛ – текстовая строка, которая (если её обработать / вычислить) становится ссылкой на ячейку, например В22. То есть функция ДВССЫЛ сначала вычисляет, какая ячейка является аргументом, а потом выводит значение, хранящееся в этой ячейке.
Функция ДВССЫЛ используется, если требуется изменить ссылку на ячейку в формуле без изменения самой формулы. Не понятно? Попробуем разобраться. Какие аргументы использует большинство функций? Ссылки на ячейки. Например, =КОРЕНЬ(D29). Если мы хотим найти квадратный корень значения, хранящегося в другой ячейке, мы должны изменить формулу, например, на =КОРЕНЬ(A29). Что делает функция ДВССЫЛ? Не изменяя формулы, возвращает значения из разных ячеек. Например, =ДВССЫЛ("B"&СТРОКА()). Сначала вычисляется функция СТРОКА(); например, получается значение 3. Далее происходит объединение двух частей текстовой строки: "B" и 3, получается В3. Далее берется значение из ячейки В3 и возвращается как результат функции =ДВССЫЛ("B"&СТРОКА()).
Еще одна трудность, связанная с восприятием функции ДВССЫЛ, заключается в использовании не традиционного стиля обозначения ячеек (А1), а стиля R1C1, где R – строка (row), C – столбец (column). Это делать не обязательно, но согласитесь вычислять цифры существенно проще, чем последовательность букв. Например, для определения, на какую ячейку нужно сослаться, мы используем некую формулу. Результатом вычисления формулы является число, которое мы объединим с буквой С и получим номер столбца, например, R1C128. В стиле А1 эта же ячейка будет обозначаться, как DX1. Вы можете придумать формулу для получения текстовой строки «DX»!?
В примере на листе «график» указаны рабочие / выходные / отпускные дни сотрудников на 2011 год. Для анализа, как распределяется число смен по дням недели / кварталам, удобно создать сводную таблицу. К сожалению, сводную нельзя сформировать на основании данных расположенных в строках – только в столбцах. То есть, мы могли бы скопировать строки с 77-й по 79-ую листа «график» и вклеить на новый лист с помощью специальной вставки:
переведя (транспонировав) строки в столбцы. Это, действительно, самый удобный способ… если только не потребуется вносить изменения в график работы. После каждого изменения, вам будет нужно опять скопировать и специально вставить обновленные значения в строках 77–79. Чтобы связать столбцы на листе «сводные» с итоговыми значениями на листе «график» можно использовать функцию ДВССЫЛ.
Вот как это выглядит на листе «сводные»:
а ниже соответствующий участок листа «график»:
Рассмотрим действие функции =ДВССЫЛ("график!R78C"&СТРОКА()+2;ЛОЖЬ) в ячейке D28 листа «сводные»:
|
Фрагмент формулы |
Действие |
Результат |
|
СТРОКА() |
определяется номер строки ячейки D28 |
28 |
|
СТРОКА()+2 |
прибавляет двойку к номеру строки (чтобы синхронизировать номер строки на листе «сводные» и номер столбца на листе «график» |
30 |
|
"график!R78C"&СТРОКА()+2 |
объединяет две части текстовой строки |
ссылка на ячейку график!R78C30 |
|
ЛОЖЬ |
признак, говорящий функции ДВССЫЛ, что полученную ссылку следует понимать, как сделанную в стиле R1C1 (если бы было значение ИСТИНА, или параметр был опущен, то – стиль А1) |
|
|
=ДВССЫЛ("график!R78C"& СТРОКА()+2;ЛОЖЬ) |
возвращает значение, хранящееся на листе «график» в ячейке AD78 (R78C30) |
22 |
Попробуйте добавить значение «д» в одной из строк в столбце AD на листе «график». Значение в ячейке AD78 увеличится. Синхронно изменится значение в ячейке D28 на листе «сводные».
Особенности использования функции ДВССЫЛ:
-
Обратите внимание, что на листе «сводные» во всех ячейках столбца С формулы одинаковые! Аналогично и в столбцах D и E (это является полной противоположностью «протягиванию» обычных формул со ссылками на ячейки). -
При изменении названия листа «график» формулы ДВССЫЛ на листе «сводные» «слетят»; это следствие того, что ссылка на лист «график» внутри функции ДВССЫЛ дана в виде текста. -
Аналогично формулы ДВССЫЛ «слетят», если вы добавите / удалите несколько фамилий; это следствие того, что ссылка на строки 77 / 78 / 79 внутри функции ДВССЫЛ даны в виде текста.