=ДВССЫЛ(E2) (по-английски INDIRECT) читает ячейку, адрес которой записан текстом в E2. Если в E2 написано C4, формула возвращает значение из C4. Адрес можно собрать и из частей: =ДВССЫЛ("C"&E3) читает столбец C в строке с номером из E3. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=ДВССЫЛ(E2)F2 читает C4, цену Carrot, $0.80. Замените E2 на C3 или B5, и F2 последует за ним. F3 соединяет "C" и 6 из E3 в адрес C6 и возвращает $1.10. Замените E3 на 2, чтобы получить цену Apple.
Синтаксис ДВССЫЛ
=INDIRECT(ref_text, [a1])
ref_text(ссылка_на_текст): текст, в котором записана ссылка:"C4","B2:B6","Prices!A2","'Price list'!A2:B9".a1:ИСТИНАили пропущен для адресов в стиле A1.ЛОЖЬчитает стиль R1C1, где"R4C3"означает строку 4, столбец 3, что удобно, когда и строка, и столбец заданы числами.
Если текст не является правильным адресом, результат #ССЫЛКА! (по-английски #REF!; таблицы на этой странице показывают ошибки под английскими именами). ДВССЫЛ возвращает настоящую ссылку, поэтому работает внутри СУММ, СЧЁТЕСЛИ, ВПР и любой функции, которая принимает диапазон.
Диапазон из чисел
Адрес может быть целым диапазоном. Если подставить в него число, получится диапазон, размер которого берётся из ячейки.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
=СУММ(ДВССЫЛ("B2:B"&(1+E2)))При 3 в E2 текст становится B2:B4, и F2 складывает Jan…Mar: 12,900. Поставьте в E2 6 для полугодия, 27,900. 1+E2 нужно потому, что данные начинаются со строки 2. В русском Excel формула пишется =СУММ(ДВССЫЛ("B2:B"&(1+E2))). Тот же итог можно записать без ДВССЫЛ, =СУММ(B2:ИНДЕКС(B2:B7;E2)), и эта формула не летучая; варианты сравниваются на странице СМЕЩ.
Ссылка на лист, названный в ячейке
Имя листа тоже может браться из ячейки. Так одна итоговая формула превращается в поиск по листам: каждая строка читает лист, названный в столбце A. Одинарные кавычки вокруг имени нужны, чтобы формула работала и с именами, в которых есть пробелы.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
=СУММ(ДВССЫЛ("'"&A2&"'!B2:B4"))B2 собирает текст 'Jan'!B2:B4 и складывает этот диапазон: 12,500. B3 и B4 это та же формула, протянутая вниз, поэтому они читают Feb (12,200) и Mar (13,700). Откройте лист Feb и измените число: итог последует за ним. Введите Feb вместо Jan в A2, и B2 будет складывать Feb. B2:B4 внутри кавычек это текст, поэтому при протягивании формулы вниз он не меняется; меняется только ссылка на A2.
Зависимые раскрывающиеся списки
Второй раскрывающийся список, пункты которого зависят от первого, это классическая задача для ДВССЫЛ. В Excel её обычно настраивают так:
- Разместите пункты каждой категории в отдельном столбце и назовите каждый диапазон по его категории: выделите столбцы вместе с заголовками и выберите «Формулы > Создать из выделенного > в строке выше». Так появятся имена
Fruit,VegetableиDairy. - Задайте A2 список категорий: «Данные > Проверка данных > Тип данных: Список», Источник
Fruit,Vegetable,Dairy(в русском Excel элементы списка разделяются точкой с запятой). - Задайте B2 список с Источником
=ДВССЫЛ(A2). Когда в A2 написано Fruit, список читает диапазон с именем Fruit.
Таблица ниже строит то же самое, только вместо именованных диапазонов у каждой категории свой лист. D2 с помощью ДВССЫЛ выводит пункты листа, названного в A2, а список в B2 читает D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
=ДВССЫЛ("'"&A2&"'!A2:A4")Выберите Dairy в A2: D2:D4 переключится на Milk, Butter, Cheese, и варианты в B2 тоже. B2 сохраняет старое значение, пока вы не выберете новое; Excel ведёт себя так же, поэтому в формы часто добавляют рядом с пунктом проверку вроде =СЧЁТЕСЛИ(D2:D4;B2)>0. В Excel 365 можно обойтись без именованных диапазонов и направить второй список на формулу с динамическим массивом, например =ДВССЫЛ("'"&A2&"'!A2:A4") во вспомогательной ячейке и =D2# в качестве Источника. Остальная настройка описана на странице раскрывающийся список.
ДВССЫЛ летучая и не замечает вставленных строк
У того, что ДВССЫЛ читает текст, а не ссылку, два побочных эффекта:
- Она пересчитывается при каждом изменении. Excel не может знать, на какие ячейки укажет текст, поэтому пересчитывает каждую ДВССЫЛ после любой правки в любом месте книги. Несколько десятков таких формул не вредят; десятки тысяч замедляют каждое нажатие клавиши. ИНДЕКС с номером строки (
=ИНДЕКС(C:C;E3)) даёт тот же результат, что=ДВССЫЛ("C"&E3), и пересчитывается, только когда меняются её входные данные. - Адрес не сдвигается. Вставьте строку над строкой 4, и
=C4станет=C5, а=ДВССЫЛ("C4")по-прежнему читает C4, где теперь другая строка. Иногда в этом и смысл: ссылка должна стоять на фиксированной ячейке, что бы ни случилось с листом. Но чаще это ошибка, которая ждёт, пока кто-нибудь вставит строку.
ДВССЫЛ на другую книгу работает, только пока эта книга открыта; если она закрыта, функция возвращает #ССЫЛКА!.
Практика: цена по номеру строки
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Ваша очередь: В F2 с помощью ДВССЫЛ верните цену из столбца C в строке, номер которой записан в E2.
Часто задаваемые вопросы
Что делает ДВССЫЛ в Excel?
Превращает текст в ссылку. =ДВССЫЛ("C4") возвращает значение C4, а =ДВССЫЛ(E2) возвращает значение той ячейки, адрес которой записан в E2. Адрес можно собрать через &, поэтому =ДВССЫЛ("C"&E2) читает столбец C в строке с номером из E2.
Как сослаться на другой лист, имя которого записано в ячейке?
Соберите адрес с именем листа в одинарных кавычках: =ДВССЫЛ("'"&A2&"'!B2"). Кавычки нужны, чтобы формула работала и с именами, в которых есть пробелы. =СУММ(ДВССЫЛ("'"&A2&"'!B2:B4")) складывает диапазон на этом листе.
Почему ДВССЫЛ возвращает #ССЫЛКА!?
Текст не является правильным адресом, или называет лист, которого нет, или указывает в другую книгу, которая закрыта. Проверьте текст, который строит формула: поставьте то же выражение отдельно в ячейку, без ДВССЫЛ.
ДВССЫЛ летучая функция?
Да. Excel пересчитывает каждую ДВССЫЛ при любом изменении в книге, потому что не может заранее знать, на какие ячейки укажет текст. Несколько таких формул безвредны; тысячи замедляют книгу. Часто ту же работу без летучести делает ИНДЕКС.
