Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. Предыдущая глава рассказывает о том, как сослаться на другой лист с помощью функции ДВССЫЛ. В ней говорится, что нужно применить некоторые ухищрения, когда ссылаешься на лист, в имени которого есть пробел. Здесь рассматривается второй сложный случай, когда имя листа содержит дату.
Задача: у вас 30 ежедневных рабочих листов в книге по одному за каждый день месяца (рис. 1). Ячейки (столбец А) на листе Сводная содержат даты, ссылки на которые вы хотите использовать внутри функции ДВССЫЛ, чтобы получить данные из соответствующего этому дню листа, но формула возвращает ошибку #ССЫЛКА!
Рис. 1. ДВССЫЛ не может напрямую использовать ссылки на листы, в имени которых содержится дата
Скачать заметку в формате Word или pdf, примеры в формате Excel
В ячейке А3 вы применили пользовательский числовой формат ДД МММ ГГГГ, чтобы дата в А3 выглядела также, как и имя листа. К сожалению, какой бы формат для ячейки А3 вы не использовали, Excel хранит дату в виде порядкового номера, и именно в таком виде использует во всех функциях, в том числе и в ДВССЫЛ. Вы надеялись, что ДВССЫЛ обработает ссылку '01 сен 2008'!В4 но вместо этого получаете '39692'!В4 (рис. 2). (Чтобы осуществить пошаговое вычисление формулы, встаньте на ячейку В3 и пройдите по меню ФОРМУЛЫ –> Зависимости формул –> Вычислить формулу.)
Рис. 2. ДВССЫЛ пытается обработать неверную ссылку, потому что Excel изменяет дату на порядковый номер
Решение: необходимо указать корректный пользовательский числовой формат с помощью функции ТЕКСТ (рис. 3).
Рис. 3. Используйте функцию ТЕКСТ для преобразования даты в текст, который выглядит как дата
Теперь формула =ДВССЫЛ("'"&ТЕКСТ(A3;"ДД МММ ГГГГ")&"'!B4") корректно обработает данные и создаст ссылку "'01 сен 2008'!В4" (рис. 4).
Рис. 4. ДВССЫЛ обрабатывает верную ссылку, потому что «подсунули» текст вместо порядкового номера
Второй аргумент функции ТЕКСТ формирует дату, соответствующую стилю имени листа. Если стиль имен ежедневных листов изменить, например, на 01.09.08, то формулу следует скорректировать: =ДВССЫЛ("'"&ТЕКСТ(A3;"ДД.ММ.ГГ")&"'!B4"). Если вам повезло, и ваши листы просто названы 1, 2, 3…, вы можете использовать =ДВССЫЛ(ДЕНЬ(А3)&"!В4") (рис. 5).
Рис. 5. В этом случае функция ТЕКСТ может быть заменена на функцию ДЕНЬ (этот пример основан на Excel-файле)
Следует помнить, что формулы, построенные на основе ДВССЫЛ, особенно подвержены появлению ошибок #ССЫЛКА! Например, если у вас есть ссылка =3!В4, и кто-то заменил имя листа на "Сен 3", формула автоматически изменится на ='Сен 3'!В4. Однако, при использовании ДВССЫЛ, формула не отработает изменение. Попросите пользователей не менять имена листов или защитите книгу.
Резюме: даты в ДВССЫЛ всегда представлены в виде порядкового номера. Используйте функцию ТЕКСТ для приведения даты в нужный формат.