Як в таблиці excel знайти всі осередки з формулами - трюки і прийоми в microsoft excel
Досить часто в таблицях буває така помилка, коли формула випадково замінюється значенням. Цей тип помилок зазвичай важко визначається, особливо якщо лист містить велику кількість формул. У цьому прийомі описується два способи швидко ідентифікувати формули в аркуші, виділяючи їх. Потім при зменшенні масштабу ви могли б визначити прогалини в групах формул.
Використання вікна Виділення групи осередків
Цей метод виявлення осередків з формулами легкий, але не динамічний. Іншими словами, він хороший для одиничної перевірки.
- Виберіть одну клітинку в аркуші.
- Виберіть Головна ► Редагування ► Знайти і виділити ► Виділення групи осередків для відкриття діалогового вікна Виділення групи осередків.
- У вікні Виділення групи осередків виберіть пункт формули і переконайтеся, що всі прапорці нижче встановлені.
- Натисніть кнопку ОК. Excel вибере все осередки з формулами.
- Натисніть кнопку Колір заливки в групі Шрифт вкладки Головна. Виберіть будь-який колір, який не використовується.
- Використовуйте елемент керування Масштаб і задайте для свого листа невеликий масштаб (наприклад, 25%).
- Уважно перевірте лист і подивіться, які клітинки залишилися невиділеними. Можливо, це формула, яка була переписана значенням.
Якщо ви не робили ніяких змін, то можете натиснути кнопку Скасувати (або натиснути Ctrl + Z) для скасування колірної заливки, яку застосували в кроці 6.
Використання умовного форматування
Цей метод визначення осередків з формулами потребують невеликої настройки, але він має явну перевагу в порівнянні з попереднім, так як є динамічним. Осередки з формулами визначаються відразу, як тільки заповнюються.
Щоб налаштувати умовне форматування, виконайте наступні дії.
- Виберіть Формули ► Певні імена ► Присвоїти ім'я для відкриття діалогового вікна Створення імені.
- У вікні Створення імені введіть наступний рядок в поле Ім'я: CellHasFormula.
- Введіть таку формулу в полі Діапазон. = ПОЛУЧІТЬ.ЯЧЕЙКУ (48; ДВССИЛ ( "rc"; ЛОЖЬ)).
- Натисніть кнопку ОК. щоб закрити діалогове вікно Створення імені.
- Виділіть всі комірки, до яких потрібно застосувати умовне форматування. Як правило, вони складають діапазон від А1 до правого нижнього кута використовуваної області листа.
- Виберіть Головна ► Стилі ► Умовне форматування ► Створити правило для відкриття діалогового вікна Створення правила форматування.
- У верхній частині вікна виберіть пункт Використовувати формулу для визначення форматується осередків.
- Введіть наступну формулу в полі діалогового вікна (рис. 196.1): = CellHasFormula.
- Натисніть кнопку Формат для відкриття діалогового вікна Формат ячеек і виберіть тип форматування для осередків, що містять формулу.
- Натисніть кнопку ОК. щоб закрити вікно Формат ячеек. і знову натисніть ОК. щоб закрити вікно Створення правила форматування.
Мал. 196.1. Вікно для установки умовного форматування для виділення осередків з формулами
Після виконання цих кроків кожна клітинка, яка містить формулу та знаходиться в межах обраного за крок 5 діапазону, буде відформатована за вашим вибором. Крім того, при введенні формули в діапазон, для якого задано умовне форматування, осередок негайно поміняє вигляд. Це форматування дозволяє вам легко визначити осередок, яка повинна містити формулу, але не містить.
Формула, яку ви вводили в кроці 3, - макрос XLM. Отже, вам необхідно зберегти книгу з розширенням з підтримкою макросів (використовуючи розширення XLSM). Якщо ви збережете книгу у вигляді XLSX-файлу, Excel видалить ім'я CellHasFormula.