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

У такій ситуації вам допоможе кольорове оформлення комірок зі значенням. Більшість користувачів виділяють осередку вручну за допомогою функції «Колір заливки». Такий спосіб зручний тільки в тому випадку, якщо даних небагато. Якщо ж в таблиці багато даних, і в процесі вони можуть змінюватися, такий спосіб не підійде.

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

Зміна кольору комірки в залежності від значення Excel

Як вибрати функцію, що змінює колір клітинки згідно значенням

Для вибору функції заливки потрібно визначити тип таблиці і її зміст:

• динамічна. У цій таблиці дані можуть змінюватися масово;
• статистична таблиця, в якій знаходяться постійні дані.

Визначившись з типом таблиці, вибираємо функцію:

• «Умовне форматування» підійде для таблиць з мінливими даними, в результаті колір клітинки зміниться одночасно з коригуванням даних;
• «Знайти і виділити» використовується для таблиць з постійною інформацією, тому колір комірки буде постійним.

І додатково розглянемо процес заливки порожніх рядків і з помилками.

Довідка! У статті ми працюємо в Ексель версії 2016. Для версії 2007, 2010 і 2013 описуються дії будуть аналогічними, можливо незначну відмінність у назвах опцій.

Робота з функцією «Умовне форматування»

Для одночасного зміни кольору осередку відповідно до значення застосуємо налаштування функції «Умовне форматування».

Таблиця, в якій застосовується налаштування функції «Умовне форматування»

Закрасимо клітинки цифрами:

• 39 і більше червоним;
• 36,5 і менше зеленим.

Почнемо роботу:

Крок 1. Виділимо з допомогою лівої кнопки мишки комірки з даними. Не виділяються стовпці і рядки, що мають назву.

Виділимо з допомогою лівої кнопки мишки клітинки з даними

Крок 2. У вкладці «Головна» шукаємо групу «Стилі».

У вкладці «Головна» знаходимо групу «Стилі»

Крок 3. Натискаємо лівою кнопкою мишки по іконці «Умовне форматування».

Натискаємо лівою кнопкою мишки по іконці «Умовне форматування»

Крок 4. У меню, що розкрилося клацаємо по рядку з назвою «Створити правило».

Клацаємо по рядку з назвою «Створити правило»

Крок 5. Виділимо лівим кліком мишки функцію «Форматувати лише клітинки…».

Виділимо лівим кліком мишки функцію «Форматувати лише клітинки…»

Крок 6. В частині вікна під назвою «Змініть опис правила» виставляємо потрібні параметри, згідно з описаним вище умовам:

• графу «Значення клітинки» залишаємо за замовчуванням;

  

  Графу «Значення клітинки» залишаємо за замовчуванням

• в наступній графі «між» розкриваємо список меню, клацнувши по стрілочці вниз, і вибираємо варіант «більше або дорівнює»;

  

  Клікаємо по стрілочці вниз, вибираємо варіант «більше або одно»

• в останній графі вводимо цифру 39.

  

  В останній графі вводимо цифру 39

Крок 7. Натискаємо кнопку «Формат».

Натискаємо кнопку «Формат»

Крок 8. У відкритому вікні «Формат ячеек» активуємо клацанням мишки вкладку «Заливка», якщо вона не відкрилася за замовчуванням.

Активуємо клацанням мишки вкладку «Заливка»

Крок 9. Лівою кнопкою миші натискаємо за відповідним кольору в палітрі. У блоці «Зразок» з’явиться вибраний колір.

Лівою кнопкою миші натискаємо за відповідним кольором панелі

Крок 10. Якщо колір влаштовує, натискаємо «ОК».

Натискаємо «ОК»

Крок 11. Перевіряємо виставлені параметри, натискаємо «ОК», щоб налаштування відображення в таблиці.

Перевіряємо виставлені параметри, натискаємо «ОК»

Крок 12. Створюємо інше правило, відповідно до другого критерію, перейшовши в «Умовне форматування».

Клікаємо по іконці «Умовне форматування», потім по рядку з назвою «Створити правило»

Крок 13. Вибираємо другий тип правила.

Вибираємо другий тип правила

Крок 14. У графі «між» виставляємо «менше або дорівнює», вводимо цифру 36,5.

У графі «між» виставляємо «менше або дорівнює», вводимо цифру 36,5

Крок 15. Тиснемо кнопку «Формат».

Тиснемо кнопку «Формат»

Крок 16. Вибираємо зелений колір, натискаємо «ОК».

Вибираємо зелений колір, натискаємо «ОК»

Крок 17. Перевіряємо всі установки, тиснемо «ОК».

Перевіряємо всі установки, тиснемо «ОК»

Таким чином ми створили в одній таблиці два правила з різною заливкою.

Таблиця з двома створеними правила різної заливки

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

При коригуванні даних в комірках автоматично зміниться і їх колір

Застосуємо функцію заливки одним кольором різних відтінків.

1. За допомогою лівої клавіші мишки виділимо всі дані.

   

   Виділимо з допомогою лівої кнопки мишки клітинки з даними

2. Розкриємо меню функції «Умовне форматування», клацнемо «Створити правило».

   

   Розкриємо меню функції «Умовне форматування», клацнемо «Створити правило»

3. Залишаємо за замовчуванням виділеним пункт «Форматувати всі клітинки…».

   

   Залишаємо за замовчуванням виділеним пункт «Форматувати всі клітинки…»

4. У графі «Колір» виставляємо підходящий. Вибраного кольору будуть фарбувати всі комірки з даними. У блоці «Зразок» ви зможете побачити, як це буде виглядати.

   

   У графі «Колір» вибираємо відповідний колір, який буде фарбувати всі клітинки з даними

5. Натискаємо «ОК».

   

   Натискаємо «ОК»

У підсумку отримуємо кольорові клітинки різного відтінку, темний відтінок забарвлює менші значення, чим більше значення, тим відтінок світліше.

Клітинки таблиці пофарбовані в один колір тільки різного відтінку залежно від значення

Робота з функцією «Знайти і виділити»

За допомогою функції «Знайти і виділити» ми визначимо постійний колір клітинок згідно з їх значенням. Для прикладу розглянемо роботу з функцією на таблиці з доходами компанії за цілий рік. Окрасим всі дані різними кольорами, вибравши потрібний діапазон. Отже, переходимо до дій:

1. Виділимо комірки з доходом.

   

   Виділимо комірки з доходом

2. Знаходимо групу «Редагування» в головній вкладці. Клацаємо лівою кнопкою миші на іконці «Знайти і виділити».

   

   Знаходимо групу «Редагування» в головній вкладці, клацаємо лівою кнопкою миші на іконці «Знайти і виділити»

3. У меню, що розкрилося клікаєм по рядку з назвою «Знайти».

   

   Клікаємо по рядку з назвою «Знайти»

4. Розділимо умовно дані на діапазони. Почнемо з меншого, щоб знайти до 40000 друкуємо в полі «Знайти» таку комбінацію «3?????», клікаємо по опції «Знайти усі». Поставивши таку комбінацію, функція знайде всі значення від 30000 до 40000. Знаків «?» додається стільки скільки нулів у значеннях, так як конкретний діапазон в цьому інструменті неможливо встановити. Якщо б у нас були дані менше 30000, тоді потрібно було б вводити комбінацію «2?????», і так для кожного діапазону.

   

   Друкуємо в полі «Знайти» комбінацію «3?????», клікаємо по опції «Знайти усі»

5. Внизу вікна відобразиться поле зі знайденим результатом, клацаємо по кожному значенню лівою клавішею мишки.

   

   Внизу вікна клацаємо по кожному значенню лівою клавішою миші

6. Затискаємо одночасно на клавіатурі кнопки «Ctrl+A», для виділення рядків у блоці знайдених результатів. Одночасно виділятися ті ж клітинки у стовпці.

   

   Затискаємо одночасно на клавіатурі кнопки «Ctrl+A» для виділення рядків у блоці знайдених результатів

7. Переміщаємося до групи інструментів «Шрифт», знаходимо іконку «Колір заливки» і клацаємо в ній по маленькому трикутника лівою кнопкою мишки.

   

   Переміщаємося до групи інструментів «Шрифт», знаходимо іконку «Колір заливки» і клацаємо в ній по маленькому трикутника лівою кнопкою мишки

8. Визначаємося з кольором і клікаєм по його значку на панелі. В результаті всі знайдені осередку забарвляться в обраний колір.

   

   Визначаємося з кольором і клікаєм по його значку на панелі

9. Проробляємо те ж саме для всіх даних, вибираючи правильно діапазон, і задавши правильну комбінацію для пошуку:

• від 40000 до 50000 друкуємо у рядку «Знайти» комбінацію «4?????», натискаємо «Знайти» і проробляємо ті ж дії, як і для першого діапазону;

  

  Друкуємо у рядку «Знайти» комбінацію «4?????», натискаємо «Знайти усі»

• від 50000 до 60000 друкуємо комбінацію «5?????» далі всі дії аналогічні вищеописаним.

  

  Друкуємо комбінацію «5?????», натискаємо «Знайти усі», виділяємо всі рядки, вибираємо колір

Важливо! Перед кожним завданням не забувайте виділяти дані в клітинках, так як функція «Знайти» буде шукати значення тільки у виділеному фрагменті.

В результаті дані в таблиці пофарбовані в різні кольори згідно значень.

Комірки зі значенням пофарбовані в різні кольори

Інструкція по зафарбуванню порожніх клітинок і з помилками

Для зафарбовування особливих клітинок ми застосуємо налаштування функції «Умовне форматування». Слідуємо нижче описаним діям:

1. Виділяємо всі рядки з даними.

   

   Виділяємо всі рядки з даними

2. Відкриваємо меню «Умовне форматування», тиснемо по рядку з назвою «Створити правило».

   

   Відкриваємо меню «Умовне форматування», тиснемо по рядку з назвою «Створити правило»

3. Виділяємо лівим кліком мишки «Форматувати лише клітинки…».

   

   Виділяємо лівим кліком мишки «Форматувати лише клітинки…»

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

   

   Розкриваємо список, клацнувши по стрілочці вниз у графі з назвою «Значення клітинки», вибираємо варіант «Порожні»

5. Тиснемо кнопку «Формат».

   

   Тиснемо на кнопку «Формат»

6. Визначаємося з кольором, натискаємо «ОК».

   

   Визначаємося з кольором, натискаємо «ОК»

7. Якщо все влаштовує, клацаємо по «ОК».

   

   Клацаємо по «ОК»

Пусті клітинки закрасятся у визначений колір.

Пусті клітинки забарвилися в означений колір

Таким же способом клітинки зафарбовуються й з помилками. Зі списку необхідно вибрати «Помилки», визначиться з кольором в блоці «Формат» і зберегти налаштування натисканням по функції «ОК».

Вибираємо «Помилки», визначаємося з кольором в блоці «Формат», натискаємо по функції «ОК»

Як змінити правила створені

Якщо вам раптом необхідно змінити в створених правилах умови, виконайте описані нижче дії:

1. Клацніть по іконці з назвою «Умовне форматування».

   

   Натискаємо лівою кнопкою мишки по іконці «Умовне форматування»

2. Клацніть лівою кнопкою мишки по рядку з назвою «Керування правилами».

   

   Клацаємо лівою кнопкою мишки по рядку з назвою «Керування правилами»

3. Відобразиться вікно з створеними раніше правилами, клацніть за правилом, яке потрібно відкоригувати лівою кнопкою мишки, потім по функції «Змінити правило».

   

   Клацаємо за правилом, яке потрібно відкоригувати лівою кнопкою мишки, потім по функції «Змінити»

4. Змініть значення, за необхідності колір в блоці «Формат». Клацніть «ОК» для збереження змін.

   

   Міняємо значення, який колір за необхідності в блоці «Формат», натискаємо «ОК»

5. В основному вікні клацніть по опціях «Застосувати» і «ОК».

   

   Клікаємо по опціях «Застосувати» і «ОК»

Довідка! В настройках вікна «Диспетчер правил умовного форматування» ви також можете видалити правило або створити нове, вибравши відповідну функцію.

У вікні «Диспетчер правил умовного форматування можна видалити правило або додати нове, клацнувши по відповідних кнопок

Висновок

Тепер ви знаєте кілька способів для зафарбовування комірок зі значенням. Додатково ми показали, як змінити колір в порожніх клітинках і з помилками. Сподіваємося ці навички знадобляться вам у роботі з таблицями і значно полегшать її.

Відео — Як змінити колір в комірках Excel в залежності від значення