Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як виділити / умовне форматування комірок формулами в Excel?

Припустимо, у вас є великий аркуш, який містить як константи, так і формули, і тепер ви хочете знати розташування всіх комірок формул. Звичайно, ви можете легко та швидко вибрати всі формули, використовуючи функцію Перейти до спеціального. Але якщо ваші дані або формули потрібно час від часу змінювати, ви повинні застосовувати цю функцію неодноразово.

Тут я представляю вам краще рішення - умовне форматування. За допомогою умовного форматування ви можете виділити всі клітинки формулами, і більше того, виділене форматування буде скориговано відповідно до змінних даних.

Виділіть клітинки формули за допомогою формули імені та умовного форматування

Виділіть комірки формул за допомогою функції користувача та умовного форматування

Виділіть клітинки формул за допомогою Kutools для Excel швидко і просто


Перед використанням Умовне форматування, вам слід створити визначене ім'я, виконайте такі дії:

1. Відкрийте робочий аркуш і натисніть кнопку Формули > Менеджер імен, див. скріншот:

doc-highlight-formula1

2. В вискочив Менеджер імен діалогове вікно натисніть кнопку новий кнопки.

doc-highlight-formula1

3. І в Нове ім'я у діалоговому вікні виконайте такі операції:

  • (1.) Введіть ім'я для формули визначеного імені, у цьому прикладі я введу Формати формул;
  • (2.) Виберіть робочий зошит зі спадного списку Scope;
  • (3.) Введіть цю формулу = GET.CELL (48, непрямий ("rc", FALSE)) в Відноситься до поле.

doc-highlight-formula1

4. Потім натисніть OK щоб повернутися до попереднього діалогового вікна та закрити його.

5. Потім виберіть діапазон або цілий аркуш, який потрібно виділити клітинками формули.

6. Під Головна вкладка, клацніть Умовне форматування > Нове правило, див. скріншот:

doc-highlight-formula1

7, в Нове правило форматування діалог, клацніть За допомогою формули визначте, які клітинки потрібно форматувати і введіть цю формулу = Формули формату (Формати формул - це ваше визначене ім’я на кроці 3), ви можете змінити його на власне визначене ім’я. Дивіться знімок екрана:

doc-highlight-formula1

8. Потім натисніть сформований кнопку, встановіть форматування під Заповнювати вкладку, див. знімок екрана:

doc-highlight-formula1

9. А потім клацніть OK > OK щоб закінчити операції, і всі клітинки з формулою були виділені відразу, як показано нижче:

doc-highlight-formula1

Примітки:

1. Коли ви вводите формули в будь-яку з комірок, на які поширюється умовне форматування, вони будуть виділені автоматично.

2. Після закінчення операцій вам потрібно зберегти свою книгу як Книга Excel з підтримкою макросів так що клітинки умовного форматування зберігаються. І коли наступного разу ви відкриєте свою книгу, натисніть Увімкнути вміст.

doc-highlight-formula1


Якщо ви не хочете використовувати формулу визначеного імені, ви можете створити просту визначену користувачем функцію, а потім застосувати умовне форматування для вирішення цього завдання.

1. По-перше, утримуйте клавішу ALT + F11 ключі, щоб відкрити Вікно Microsoft Visual Basic для програм.

2. Клацання Insert > Модуліта вставте наступний коротший код у Вікно модуля.

Function HasFormula(Rng As Range) As Boolean
'Updateby Extendoffice
Application.Volatile
HasFormula = Rng.HasFormula
End Function

3. Потім збережіть і закрийте цей код і поверніться назад до робочого аркуша, щоб вибрати діапазон або цілий аркуш, який ви хочете виділити комірки формули.

4. Йти до Головна та натисніть Умовне форматування > Нове правило.

5, в Нове правило форматування діалог, виберіть За допомогою формули визначте, які клітинки потрібно форматувати і введіть цю формулу = Hasformula (A1), див. скріншот:

doc-highlight-formula1

6. Потім натисніть сформований і виберіть один колір, який вам подобається Заповнювати Вкладка в Формат ячеек діалогове вікно.

7. А потім клацніть OK > OK щоб закрити діалоги. І всі клітинки, що містять формулу, були відразу виділені.

Примітки:

1. У формулі A1 позначає першу клітинку у вибраному діапазоні, ви можете змінити її відповідно до ваших потреб.

2. Коли ви вводите формули в будь-яку з комірок, на які поширюється умовне форматування, вони будуть виділені автоматично.

3. Після закінчення операцій вам потрібно зберегти свою книгу як Книга Excel з підтримкою макросів так що клітинки умовного форматування зберігаються. І коли наступного разу ви відкриєте свою книгу, натисніть Увімкнути вміст.


Здається, два вищезазначені методи є дещо складними для нашого початківця Excel, тут я порекомендую потужний інструмент- Kutools для Excel, з його багатофункціональністю Дизайн робочого аркуша функцію, ви можете швидко виділити всі формули цілої книги.

Примітка:Щоб застосувати це Дизайн робочого аркуша, по-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Відкрийте книгу, в якій ви хочете виділити всі формули.

2. Клацання Kutools Plus > Дизайн робочого аркуша, і новий дизайн на стрічці відобразиться вкладка, а потім натисніть Виділіть формули опція в вид групу, див. скріншоти:

1

3. І всі формули у цілій книзі були відразу виділені. Дивіться знімок екрана:

doc-highlight-formula1

Примітки:

1. Натисніть Виділіть формули знову ж виділення буде вимкнено.

2. Ви можете змінити колір виділення на свій вподобання, натиснувши настройки під дизайн , а потім у Налаштування конструкторських інструментів діалоговому вікні, виберіть один із кольорів, який вам подобається Колір формул розділ:

3. Коли ви вводите нові формули в будь-яку з комірок поточної книги, вони будуть виділені автоматично.

Натисніть, щоб дізнатись більше про цю утиліту «Виділення формул».

Завантажте та безкоштовну пробну версію Kutools для Excel зараз!


Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!

Статті по темі:

Як заблокувати та захистити формули в Excel?

Як приховати формули, не відображені в рядку формул в Excel?


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Johan · 2 years ago
    Hey,

    ik wil een cel, welke een formule heeft, met een kleur aanduiden in functie van een andere cel die een waarde heeft.

    vb: als E10<4 dan moet F10 (welke een formule heeft) samen met E10 in een bepaalde kleur komen.

    Als ik dit doe, wordt enkel E10 gemarkeerd.


    Hoe los Ik dit op?


    Mvg
  • To post as a guest, your comment is unpublished.
    Katie Samek · 4 years ago
    I was able to follow all the below. I made a macro to do this for me, but it is not able to apply the formatting.

    My range is named, but how to I ass VBA to highlight the range with formatting. For context, I am highlighting all cells with a formula in it to be a green color. If the formula is typed over it changes colors
    • To post as a guest, your comment is unpublished.
      Natalie · 3 years ago
      This is exactly what I want to do, and the first stage (highlighting every cell with a formula in) isn't working for me either, despite following steps to the letter. Did you manage to get a solution?
  • To post as a guest, your comment is unpublished.
    SANDEEP.POLAPALLY · 5 years ago
    dear sir,

    i have a doubt ,for example in a student marks table i want the student names highlited based on the student who got lessthan 400 total marks using conditional formatting.please help me sir
  • To post as a guest, your comment is unpublished.
    Abu Belaluddin · 6 years ago
    Hi, How can I highlight cells in a row based on days of a month, for example if I need to highlight cells in increments of 1 day i.e. it should highlight each subsequent cells if 24 hrs has passed,
  • To post as a guest, your comment is unpublished.
    Jamal · 7 years ago
    Hi,
    I am trying to do conditional formatting on a cell based on another cell value which already has a formula on it. i have tried everything but i would not work. eg. cell B1 has a formula (A1+1) and i am trying to change the color of say cell D8 by using formula (=B1=21). it works fine when i remove the increment formula from B1 and enter a value manually...
  • To post as a guest, your comment is unpublished.
    Pritesh Patel · 7 years ago
    Thanks for the help.

    This is the only website where i have found complete steps