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

При роботі з таблицями першочергове значення мають виведені в ній значення. Але важливою складовою є також і її оформлення. Деякі користувачі вважають це другорядним фактором і не звертають на нього особливої ​​уваги. А дарма, адже красиво оформлена таблиця є важливою умовою для кращого її сприйняття і розуміння користувачами. Особливо велику роль в цьому відіграє візуалізація даних. Наприклад, за допомогою інструментів візуалізації можна забарвити елементи таблиці в залежності від їх вмісту. Давайте дізнаємося, як це можна зробити в програмі 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. Але якщо ми замінимо число на інше, що виходить за межі, які встановлені для конкретного кольору, то колір не зміниться, як це було в попередньому способі. Це свідчить про те, що даний варіант буде надійно працювати тільки в тих таблицях, в яких дані не змінюються.

урок: Як зробити пошук в Ексель

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

• ← Перетворення всіх букв в прописні в Microsoft Excel
• Функція ДВССИЛ в Microsoft Excel →