Заметка написана с использованием книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: вы хотите выделить все ячейки на листе, которые не содержат формул.
Примечание Багузина. Именно эту задачу можно решить довольно просто, если вы пользуетесь версией Excel 2013 или более поздней. Примените функцию ЕФОРМУЛА(ссылка). Функция проверяет содержимое ячейки, и возвращает значение ИСТИНА или ЛОЖЬ. Однако подход Билла Джелена любопытен сам по себе, поскольку открывает окно в мир макрофункций (скорее всего, неизвестный большинству пользователей).
Решение: до введения VBA, макросы писали на языке xlm (Excel Macro). Язык использовал макрофункции, т.е., функции листа макросов Excel 4.0. Этот язык до сих пор поддерживается Microsoft для совместимости с предыдущими версиями Excel (подробнее см. Что такое макрофункции?). Система макросов xlm является «пережитком», доставшимся нам от предыдущих версий Excel (4.0 и более ранних). Более поздние версии Excel все еще выполняют макросы xlm, но, начиная с Excel 97, пользователи не имеют возможности записывать макросы на языке xlm.
Язык xlm среди прочих содержит функцию Получить.Ячейку (GET.CELL), которая предоставляет гораздо больше информации, чем современная функция ЯЧЕЙКА(). На самом деле, Получить.Ячейку может рассказать о 66 различных атрибутах ячейки, в то время, как функция ЯЧЕЙКА возвращает лишь 12 параметров. Функция Получить.Ячейку весьма полезна, за исключением одного «но»… Вы не можете ввести ее непосредственно в ячейку (рис. 1).
Рис. 1. Функция Получить.Ячейку недоступна для ввода на листе Excel
Скачать заметку в формате Word или pdf, примеры в формате Excel (с макросами)
Однако есть обходной путь. Вы можете определить имя, основанное на функции, а затем ссылаться на это имя в любой ячейке. Например, чтобы выяснить, содержит ли ячейка A1 формулу, можно записать =Получить.Ячейку(48,А1). Здесь 48 – аргумент, отвечающий за анализ, является ли содержимое ячейки формулой. Для более универсального случая, когда вы хотите применить условное форматирование, воспользуйтесь формулой =Получить.Ячейку(48,ДВССЫЛ("RC",ЛОЖЬ)). Если вы не знакомы с функцией ДВССЫЛ, советую почитать Примеры использования функции ДВССЫЛ (INDIRECT). Нам эта функция нужна для того, чтобы обозначить ссылку на ячейку, в которой мы сейчас находимся. Мы не можем указать никакую конкретную ячейку, поэтому используем ссылку в стиле R1C1, где RC означает относительную ссылку на текущую ячейку. В стиле ссылок А1 для ссылки на текущую ячейку нам бы потребовалось этот фрагмент формулы записать в виде =ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ();4)). Подробнее см. Зачем нужен стиль ссылок R1C1.
Чтобы использовать формулу =Получить.Ячейку() для выделения ячеек с помощью условного форматирования, выполните следующие действия (для Excel 2007 или более поздней версии):
- Чтобы определить новое имя, пройдите по меню ФОРМУЛЫ –> Присвоить имя. В открывшемся окне (рис. 2) выберите подходящее имя, например, ЕслиФормула. В поле формула введите =Получить.Ячейку(48,ДВССЫЛ(
"RC",ЛОЖЬ)). Нажмите Оk. Нажмите Закрыть. - Выделите ячейки, к которым хотите применить условное форматирование (рис. 3); в нашем примере – это В3:В15.
- Пройдите по меню ГЛАВНАЯ –> Условное форматирование –> Создать правило. В открывшемся окне выберите пункт Использовать формулу для определения форматируемых ячеек. В нижней половине диалогового типа введите =ЕслиФормула, как показано на рис. 3. Excel может автоматически добавить кавычки =»ЕслиФормула». Уберите их. Нажмите кнопку Формат, в открывшемся окне Формат ячеек перейдите на вкладку Заливка и выберите цвет заливки. Нажмите Оk.
Рис. 2. Окно Создание имени
Рис. 3. Создание нового правила условного форматирования
Чтобы выделить ячейки, которые не содержат формулу, используйте настройку формата =НЕ(ЕслиФормула).
Будьте осторожны. Иногда при копировании ячеек, содержащих формулу, на другой лист, есть риск «обрушить» Excel (у меня такого не случилось ни разу).
Альтернативные стратегии: преимущество описанного выше метода заключается в том, что форматирование будет автоматически обновляться всякий раз, когда кто-то меняет содержимое ячеек, заменяя формулы на константы или наоборот. Если вам просто нужно получить разовый снимок, какие ячейки содержат формулы, выполните следующие действия:
- Выберите все ячейки; для этого встаньте на одну из ячеек диапазона и нажмите Ctrl+А (А – английское).
- Нажмите Ctrl+G, чтобы открыть окно Переход.
- В левом нижнем углу этого окна нажмите кнопку Выделить.
- В открывшемся диалоговом окне Выделить группу ячеек выберите формулы, нажмите Ok.
- На закладке ГЛАВНАЯ выберите цвет заливки, например, красный.
Синтаксис функции: ПОЛУЧИТЬ.ЯЧЕЙКУ(номер_типа; ссылка). Полный список первого аргумента функции Получить.Ячейку см., например, здесь. Обратите внимание, что в некоторых случаях функциональность современных версий Excel существенно изменилась, и функция не вернет допустимое значение. Для некоторых аргументов номер_типа удобнее использовать функцию ЯЧЕЙКА.
Несколько примеров функции ПОЛУЧИТЬ.ЯЧЕЙКУ.
Номер_типа = 1. Абсолютная ссылка левой верхней ячейки аргумента ссылка в виде текста в текущем стиле: $А$1 или R1C1 (рис. 4). Проще использовать формулу =ЯЧЕЙКА("адрес";ссылка)
Рис. 4. Определение адреса левой верхней ячейки диапазона
Номер_типа = 63. Возвращает номер цвета заливки ячейки (рис. 5).
Рис. 5. Определение номера цвета заливки ячейки
Любопытно. Несмотря на то что это макрофункция, язык приложения важен. В русском Excel функция GET.CELL не работает. И еще. Если вам нужна информация о сводной таблице, то аналог ПОЛУЧИТЬ.ЯЧЕЙКУ — обычная функция (доступная для ввода на листе Excel) ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ.
Источник: http://www.mrexcel.com/archive2/18800/21312.htm