Заливка комірок залежно від значення в Microsoft Excel

Зміст

• Процедура зміни кольору комірок залежно від вмісту
  • Спосіб 1: умовне форматування
  • Спосіб 2: використання інструменту "знайти та виділити"
• Питання та відповіді

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

Процедура зміни кольору комірок залежно від вмісту

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

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

Але вихід існує. Для осередків, які містять динамічні (змінюються) значення застосовується умовне форматування, а для статистичних даних можна використовувати інструмент " знайти та замінити» .

Спосіб 1: умовне форматування

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

Подивимося, як цей спосіб працює на конкретному прикладі. Маємо таблицю доходів підприємства, в якій дані розбиті помісячно. Нам потрібно виділити різними кольорами ті елементи, в яких величина доходів менш 400000 рублів, від 400000 до 500000 рублів і перевищує 500000 рублів.

1. Виділяємо стовпець, в якому знаходиться інформація по доходах підприємства. Потім переміщаємося у вкладку»Головна" . Клацаємо по кнопці " Умовне форматування» , яка розташовується на стрічці в блоці інструментів «стилі» . У списку вибираємо пункт " управління правилами...» .

2. Запускається віконце управління правилами умовного форматування. У полі «показати правила форматування для» має бути встановлено значення " поточний фрагмент» . За замовчуванням саме воно і повинно бути там вказано, але про всяк випадок перевірте і в разі невідповідності змініть настройки згідно вищевказаним рекомендаціям. Після цього слід натиснути на кнопку " Створити правило ... » .

3. Відкривається вікно створення правила форматування. У списку типів правил вибираємо позицію " форматувати лише клітинки, які містять» . У блоці опису правила в першому полі перемикач повинен стояти в позиції " Значення» . У другому полі встановлюємо перемикач в позицію " менше» . У третьому полі вказуємо значення, елементи листа, що містять величину менше якого, будуть пофарбовані певним кольором. У нашому випадку це значення буде 400000 . Після цього тиснемо на кнопку " Формат...» .

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

5. Повертаємося у вікно створення правила форматування і там теж тиснемо на кнопку «OK» .

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

7. І знову ми потрапляємо у вікно створення правила. Переміщаємося в розділ " форматувати лише клітинки, які містять» . У першому полі даного розділу залишаємо параметр " значення комірки» , а в другому виставляємо перемикач в позицію " між» . У третьому полі потрібно вказати початкове значення діапазону, в якому будуть форматуватися елементи листа. У нашому випадку це число 400000 . У четвертому вказуємо кінцеве значення даного діапазону. Воно складе 500000 . Після цього клацаємо по кнопці " Формат...» .

8. У вікні форматування знову переміщаємося у вкладку»Заливка" , але на цей раз вже вибираємо інший колір, після чого тиснемо на кнопку «OK» .

9. Після повернення у вікно створення правила теж тиснемо на кнопку «OK» .

10. Як бачимо, в диспетчері правил у нас створено вже два правила. Таким чином, залишилося створити третє. Клацаємо по кнопці " Створити правило» .

11. У вікні створення правила знову переміщаємося в розділ " форматувати лише клітинки, які містять» . У першому полі залишаємо варіант " значення комірки» . У другому полі встановлюємо перемикач в поліцію " більше» . У третьому полі вбиваємо число 500000 . Потім, як і в попередніх випадках, тиснемо на кнопку " Формат...» .

12. У вікні " Формат комірок» знову переміщаємося у вкладку »Заливка" . На цей раз вибираємо колір, який відрізняється від двох попередніх випадків. Виконуємо клацання по кнопці «OK» .

13. У вікні створення правил повторюємо натискання на кнопку «OK» .

14. Відкривається Диспетчер правил . Як бачимо, всі три правила створені, тому тиснемо на кнопку «OK» .

15. Тепер елементи таблиці пофарбовані відповідно до заданих умов і кордонів в Налаштуваннях умовного форматування.

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

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

1. Для цього після того, як з диспетчера правил ми переходимо в вікно створення форматування, то залишаємося в розділі " форматувати всі клітинки на основі їх значень» . У полі »Колір" можна вибрати той колір, відтінками якого будуть заливатися елементи листа. Потім слід натиснути на кнопку «OK» .

2. В диспетчері правил теж тиснемо на кнопку «OK» .

3. Як бачимо, після цього осередки в колонці забарвлюються різними відтінками одного кольору. Чим значення, яке містить елемент листа більше, тим відтінок світліше, чим менше – тим темніше.

Урок: Умовне форматування в Екселі

Спосіб 2: використання інструменту "знайти та виділити"

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

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

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

2. Запускається вікно " знайти та замінити» у вкладці »знайти" . Перш за все, знайдемо значення до 400000 рублів. Так як у нас немає жодної комірки, де містилося б значення менше 300000 рублів, то, по суті, нам потрібно виділити всі елементи, в яких містяться числа в діапазоні від 300000 до 400000 . На жаль, прямо вказати даний діапазон, як в разі застосування умовного форматування, в даному способі не можна.

   Але існує можливість вчинити дещо по-іншому, що нам дасть той же результат. Можна в рядку пошуку задати наступний шаблон «3?????» . Знак питання означає Будь-який символ. Таким чином, програма буде шукати всі шестизначні числа, які починаються з цифри «3» . Тобто, у видачу пошуку потраплять значення в діапазоні 300000 – 400000 , що нам і потрібно. Якби в таблиці були числа менше 300000 або менше 200000 , то для кожного діапазону в сотню тисяч пошук довелося б проводити окремо.

   Вводимо вираз «3?????» у полі »знайти" і тиснемо на кнопку " знайти все ».


3. Після цього в нижній частині віконця відкриваються результати пошукової видачі. Кількома лівою кнопкою миші по кожному з них. Потім набираємо комбінацію клавіш Ctrl+A . Після цього виділяються всі результати пошукової видачі і одночасно виділяються елементи в стовпці, на які дані результати посилаються.

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

5. Як бачимо, всі осередки стовпця, в яких знаходяться значення менш 400000 рублів, виділені обраним кольором.

6. Тепер нам потрібно пофарбувати елементи, в яких розташовуються величини в діапазоні від 400000 до 500000 рублів. У цей діапазон входять числа, які відповідають шаблону «4??????» . Вбиваємо його в поле пошуку і клацаємо по кнопці " знайти все» , попередньо виділивши потрібний нам стовпець.

7. Аналогічно з попереднім разом в пошуковій видачі виробляємо виділення всього отриманого результату натисканням комбінації гарячих клавіш CTRL+A . Після цього переміщаємося до значка вибору кольору заливки. Кількома по ньому і тиснемо на піктограму потрібного нам відтінку, який буде фарбувати елементи листа, де знаходяться величини в діапазоні від 400000 до 500000 .

8. Як бачимо, після цієї дії всі елементи таблиці з даними в інтервалі з 400000 по 500000 виділено вибраним кольором.

9. Тепер нам залишається виділити останній інтервал величин-більше 500000 . Тут нам теж пощастило, так як всі числа більш 500000 знаходяться в інтервалі від 500000 до 600000 . Тому в поле пошуку вводимо вираз «5?????» і тиснемо на кнопку " знайти все» . Якби були величини, що перевищують 600000 , то нам би довелося додатково проводити пошук для вираження «6?????» і т. д.

10. Знову виділяємо результати пошуку за допомогою комбінації Ctrl+A . Далі, скориставшись кнопкою на стрічці, вибираємо новий колір для заливки інтервалу, що перевищує 500000 за тією ж аналогією, як ми це робили раніше.

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

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

Урок: як зробити пошук в Екселі

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