Як виділити комірки з формулами в Excel
В Excel не передбачено вбудованої функції для пошуку формул. Коли формула введена в комірку, дізнатися, чи є комірка постійним значенням або значенням, отриманим з формули, можна, тільки клацнувши комірку і глянувши на рядок формул або ж натиснувши Ctrl+
(Тільда; щоб скасувати режим показу формул, натисніть Ctrl+
ще раз). Пропонований трюк дозволить вам виділити комірки з формулами за допомогою трьох рядків коду VBA та умовного форматування. [1]
Якщо ви ніколи не створювали функцію користувача за допомогою VBA, рекомендую почати з нотатки Сума за кольором осередків в Excel, в якій пояснюються перші кроки.
Якщо ви уявляєте, про що мова, пройдіть меню Розробник –> Visual Basic і у вікні Microsoft Visual Basic for Applications пройдіть по меню Insert –> Модулі (рис. 1). У вікні нового модуля наберіть наступний код:
FunctionIsFormula (Check_Cell As Range) IsFormula = Check_Cell.HasFormula End Function
Мал. 1. Код функції користувача IsFormula у вікні Microsoft Visual Basic for Applications
Завантажити нотатку у форматі Word або pdf, завантажити приклад у форматі Excel (із вбудованим кодом VBA)
Мал. 2. Користувальницька функція доступна у вікні Вставлення функції
Також ви можете викликати функцію просто, почавши набирати в комірці =i (рис. 3).
Мал. 3. Функцію можна вибрати через підказку, почавши набирати в комірці =i
Функція повертає два значення: ІСТИНА, якщо у відповідному осередку міститься формула, і БРЕХНЯ, якщо це не так. Цей булевський результат можна використовувати разом із умовним форматуванням, щоб автоматично виділити всі формули із застосуванням потрібного форматування.
Одна з основних переваг цього методуполягає в тому, що можливості ідентифікації формул на динамічному листі. Це означає, що, якщо ви додасте або видалите формулу, форматування зміниться відповідним чином. Ось як це зробити.
Виділіть діапазон осередків, заповнений даними, плюс зробіть запас на випадок, якщо дані додаватимуться. Не виділяйте весь аркуш, оскільки це може збільшити розмір файлу (іноді катастрофічно). У прикладі (рис. 4) я виділив область А1:К28. Причому осередок А1 має бути активним.
Як приклад діапазон А1:F20 я ввів формулу =СЛЧИС(). Потім виділив весь діапазон, скопіював його в буфер і вставив як значення. Після цього в кількох осередках знову вставив формулу =СЛЧИС().
Мал. 4. Створення правила форматування для виділеної області
Пройдіть по меню Головна –> Умовне форматування –> Створити правило (я працюю в Excel2013). У вікні Створення правила форматування виберіть опцію Використовувати формулу для визначення осередків, що форматуються. І в полі Форматувати значення, для яких наступна формула є істинною, введіть =IsFormula(A1). Натисніть кнопку Формат поряд з полем Зразок і виберіть жовту заливку, щоб ідентифікувати комірок з формулами. Натисніть кнопку ОК (рис. 5).
Мал. 5. За допомогою коду VBA та умовного форматування вдалося виділити комірки, що містять формули
Вийшов ножиданий ефект, який можна спостерігати в прикріпленому Excel-файлі. Я вважав, що функція =СЛЧИС() перераховується тільки за зміни хоча б одного значення на аркуші. Так ось, на аркуші формально немає зміни значень. Але, мабуть, функція IsFormula, що у умовному форматуванні, якось впливає цей процес, отже екран оживає, і значення постійно змінюються.🙂
Іноді при введенні формул у вікні умовного форматування Excel намагається додати лапки навколо формул після того, як ви натискаєте кнопку ОК. Це означає, що Excel розпізнав те, що ви ввели як текст, а не як формулу. Якщо це сталося, поверніться у вікно Умовне форматування, видаліть лапки та натисніть кнопку ОК.
Тепер зазначена формула застосовується до всіх осередків виділеного діапазону, що містять формули. Якщо видалити або виправити вміст комірки з формулою, умовне форматування зникне. Так само, якщо ви введете нову формулу в будь-яку комірку діапазону, вона також буде виділена форматуванням.
[1] За матеріалами книги Р. Холі, Д. Холі. Excel 2007. Трюки, стор. 70–72
Якщо знаходження осередків з формулами - це разова або не дуже часта операція, то цілком можна скористатися вже наявним функціоналом, без використання функцій користувача. Вкладка стрічки «Головна», секція «Редагування», кнопка «Знайти та виділити», у меню вибрати пункт «Формули». Ну чи CTRL+G -> "Виділити" -> "Формули" - результат аналогічний. Трохи докладніше про виділення спеціальних діапазонів можна прочитати тут: http://allexcel.info/2013/04/04/виділення-хитрих-діапазонів/
Доброго дня, Сергію! Дякуємо за цікаві матеріали з Excel та бізнесу – дуже здорово пишете!
Щодо статті хотів додати, що в Excel 2013 можна скористатися новою функцією ЕФОРМУЛУ, яка якраз визначає, чи є в комірці формула чи ні.