Зміст
![Заливка цветом ячеек в Microsoft Excel](https://cdn.daad.org.ua/images/8049/405abbf67f99a9a9e81a84133b8eaeb8.png)
При роботі з таблицями першочергове значення мають виведені в ній значення. Але важливою складовою є також і її оформлення. Деякі користувачі вважають це другорядним фактором і не звертають на нього особливої уваги. А даремно, адже красиво оформлена таблиця є важливою умовою для кращого її сприйняття і розуміння користувачами. Особливо велику роль в цьому відіграє візуалізація даних. Наприклад, за допомогою інструментів візуалізації можна забарвити осередки таблиці в залежності від їх вмісту. Давайте дізнаємося, як це можна зробити в програмі Excel.
Процедура зміни кольору комірок залежно від вмісту
Звичайно, завжди приємно мати добре оформлену таблицю, в якій осередки в залежності від вмісту, забарвлюються в різні кольори. Але особливо актуальна дана можливість для великих таблиць, що містять значний масив даних. В цьому випадку заливка кольором осередків значно полегшить користувачам орієнтування в цьому величезній кількості інформації, так як вона, можна сказати, буде вже структурованою.
Елементи аркуша можна спробувати розфарбувати вручну, але знову ж таки, якщо таблиця велика, то це займе значну кількість часу. До того ж, в такому масиві даних людський фактор може зіграти свою роль і будуть допущені помилки. Не кажучи вже про те, що таблиця може бути динамічною і дані в ній періодично змінюються, причому масово. В цьому випадку вручну міняти колір взагалі стає нереально.
Але вихід існує. Для осередків, які містять динамічні (змінюються) значення застосовується умовне форматування, а для статистичних даних можна використовувати інструмент " знайти та замінити» .
Спосіб 1: умовне форматування
За допомогою умовного форматування можна задати певні межі значень, при яких осередки будуть забарвлюватися в той чи інший колір. Фарбування буде проводитися автоматично. У разі, якщо значення осередку, внаслідок зміни вийде за межі кордону, то автоматично відбудеться перефарбовування даного елемента листа.
Подивимося, як цей спосіб працює на конкретному прикладі. Маємо таблицю доходів підприємства, в якій дані розбиті помісячно. Нам потрібно виділити різними кольорами ті елементи, в яких величина доходів менш 400000 рублів, від 400000 до 500000 рублів і перевищує 500000 рублів.
- Виділяємо стовпець, в якому знаходиться інформація по доходах підприємства. Потім переміщаємося у вкладку»Головна" . Клацаємо по кнопці " Умовне форматування» , яка розташовується на стрічці в блоці інструментів «стилі» . У списку вибираємо пункт " управління правилами...» .
- Запускається віконце управління правилами умовного форматування. У полі «показати правила форматування для» має бути встановлено значення " поточний фрагмент» . За замовчуванням саме воно і повинно бути там вказано, але про всяк випадок перевірте і в разі невідповідності змініть настройки згідно вищевказаним рекомендаціям. Після цього слід натиснути на кнопку " Створити правило ... » .
- Відкривається вікно створення правила форматування. У списку типів правил вибираємо позицію " форматувати лише клітинки, які містять» . У блоці опису правила в першому полі перемикач повинен стояти в позиції " Значення» . У другому полі встановлюємо перемикач в позицію " менше» . У третьому полі вказуємо значення, елементи листа, що містять величину менше якого, будуть пофарбовані певним кольором. У нашому випадку це значення буде 400000 . Після цього тиснемо на кнопку " Формат...» .
- Відкривається вікно формату комірок. Переміщаємося у вкладку»Заливка" . Вибираємо той колір заливки, яким бажаємо, щоб виділялися осередки, що містять величину менше 400000 . Після цього тиснемо на кнопку «OK» у нижній частині вікна.
- Повертаємося у вікно створення правила форматування і там теж тиснемо на кнопку «OK» .
- Після цієї дії ми знову будемо перенаправлені в Диспетчер правил умовного форматування . Як бачимо, одне правило вже додано, але нам належить додати ще два. Тому знову тиснемо на кнопку " Створити правило ... » .
- І знову ми потрапляємо у вікно створення правила. Переміщаємося в розділ " форматувати лише клітинки, які містять» . У першому полі даного розділу залишаємо параметр " значення комірки» , а в другому виставляємо перемикач в позицію " між» . У третьому полі потрібно вказати початкове значення діапазону, в якому будуть форматуватися елементи листа. У нашому випадку це число 400000 . У четвертому вказуємо кінцеве значення даного діапазону. Воно складе 500000 . Після цього клацаємо по кнопці " Формат...» .
- У вікні форматування знову переміщаємося у вкладку»Заливка" , але на цей раз вже вибираємо інший колір, після чого тиснемо на кнопку «OK» .
- Після повернення у вікно створення правила теж тиснемо на кнопку «OK» .
- Як бачимо, в диспетчері правил у нас створено вже два правила. Таким чином, залишилося створити третє. Клацаємо по кнопці " Створити правило» .
- У вікні створення правила знову переміщаємося в розділ " форматувати лише клітинки, які містять» . У першому полі залишаємо варіант " значення комірки» . У другому полі встановлюємо перемикач в поліцію " більше» . У третьому полі вбиваємо число 500000 . Потім, як і в попередніх випадках, тиснемо на кнопку " Формат...» .
- У вікні " Формат комірок» знову переміщаємося у вкладку »Заливка" . На цей раз вибираємо колір, який відрізняється від двох попередніх випадків. Виконуємо клацання по кнопці «OK» .
- У вікні створення правил повторюємо натискання на кнопку «OK» .
- Відкривається Диспетчер правил . Як бачимо, всі три правила створені, тому тиснемо на кнопку «OK» .
- Тепер елементи таблиці пофарбовані відповідно до заданих умов і кордонів в Налаштуваннях умовного форматування.
- Якщо ми змінимо вміст в одній з осередків, виходячи при цьому за межі одного із заданих правил, то при цьому даний елемент листа автоматично змінить колір.
![Переход к управлению правилами в Microsoft Excel](https://cdn.daad.org.ua/images/8049/5db4240022c21bbe760162b5c3e9f2db.png)
![Переход к созданию правила в Microsoft Excel](https://cdn.daad.org.ua/images/8049/881106af7ead04f819d67a72e9900779.png)
![Окно создания правила форматирования в Microsoft Excel](https://cdn.daad.org.ua/images/8049/46c0147e505882e8545389804a994cb6.png)
![Выбор цвета ячейки в Microsoft Excel](https://cdn.daad.org.ua/images/8049/768794ae76674dd1355b9884c7ed1298.png)
![Создание правила форматирования в Microsoft Excel](https://cdn.daad.org.ua/images/8049/ab645005f3f2ea7c00788c2b4d3e8aaf.png)
![Переход к созданию следующего правила в Microsoft Excel](https://cdn.daad.org.ua/images/8049/662bb2c191d7ebd9a8f5e3812bc9cff1.png)
![Переход в окно форматирования в Microsoft Excel](https://cdn.daad.org.ua/images/8049/e8e9ce32eaffcf9c97ad613bf728744d.png)
![Окно форматирования в Microsoft Excel](https://cdn.daad.org.ua/images/8049/db2a867eb98ea58237da92d700ac127f.png)
![Завершене создания правила в Microsoft Excel](https://cdn.daad.org.ua/images/8049/6346c9747c67fc7e6109f9b08ac6d7e6.png)
![Переход к созданию последнего правила в Microsoft Excel](https://cdn.daad.org.ua/images/8049/32cef9dc151ae0cd2c049aee479c3318.png)
![Окно создания правила в Microsoft Excel](https://cdn.daad.org.ua/images/8049/6016d6767337db282774f6cf39ce70c8.png)
![Окно формат ячеек в Microsoft Excel](https://cdn.daad.org.ua/images/8049/17d10b86147941b0fa2ee0a58a64d5a8.png)
![Последнее правило создано в Microsoft Excel](https://cdn.daad.org.ua/images/8049/c9ecec54296af0ba957ce81d46201aee.png)
![Завершение работы в Диспетчере правил в Microsoft Excel](https://cdn.daad.org.ua/images/8049/5f7ad8e0da85d3c439b30a03081819e7.png)
![Ячейки окрашены согласно заданным условиям в Microsoft Excel](https://cdn.daad.org.ua/images/8049/e352b47ca580d44540c4d4725a51050d.png)
![Смена цвета в ячеке в Microsoft Excel](https://cdn.daad.org.ua/images/8049/f8e1cdcbd753d5b6738ce0d1170237fd.png)
Крім того, можна використовувати умовне форматування дещо по-іншому для забарвлення елементів листа кольором.
- Для цього після того, як з диспетчера правил ми переходимо в вікно створення форматування, то залишаємося в розділі " форматувати всі клітинки на основі їх значень» . У полі »Колір" можна вибрати той колір, відтінками якого будуть заливатися елементи листа. Потім слід натиснути на кнопку «OK» .
- В диспетчері правил теж тиснемо на кнопку «OK» .
- Як бачимо, після цього осередки в колонці забарвлюються різними відтінками одного кольору. Чим значення, яке містить елемент листа більше, тим відтінок світліше, чим менше – тим темніше.
![Форматирование ячеек на основании их значений в Microsoft Excel](https://cdn.daad.org.ua/images/8049/7d908f9b69dbf7985161e9803960ff9d.png)
![Диспетчер правил в Microsoft Excel](https://cdn.daad.org.ua/images/8049/d04179dff696b708e698a1a82e67cf8a.png)
![Ячейки отформатированы в Microsoft Excel](https://cdn.daad.org.ua/images/8049/b257d6a98cf629ec9bf38a28cc43c171.png)
Урок: Умовне форматування в Екселі
Спосіб 2: використання інструменту "знайти та виділити"
Якщо в таблиці знаходяться статичні дані, які не планується з часом змінювати, то можна скористатися інструментом для зміни кольору осередків по їх вмісту під назвою " знайти та виділити» . Зазначений інструмент дозволить відшукати задані значення і змінити колір в цих осередках на потрібний користувачеві. Але слід врахувати, що при зміні вмісту в Елементах листа, колір автоматично змінюватися не буде, а залишиться колишнім. Для того, щоб змінити колір на актуальний, доведеться повторювати процедуру заново. Тому даний спосіб не є оптимальним для таблиць з динамічним вмістом.
Подивимося, як це працює на конкретному прикладі, для якого візьмемо все ту ж таблицю доходу підприємства.
- Виділяємо стовпець з даними, які слід відформатувати кольором. Потім переходимо у вкладку»Головна" і тиснемо на кнопку " знайти та виділити» , яка розміщена на стрічці в блоці інструментів «редагування» . У списку натискаємо по пункту »знайти" .
- Запускається вікно " знайти та замінити»
у вкладці
»знайти"
. Перш за все, знайдемо значення до
400000
рублів. Так як у нас немає жодної комірки, де містилося б значення менше
300000
рублів, то, по суті, нам потрібно виділити всі елементи, в яких містяться числа в діапазоні від
300000
до
400000
. На жаль, прямо вказати даний діапазон, як в разі застосування умовного форматування, в даному способі не можна.
Але існує можливість вчинити дещо по-іншому, що нам дасть той же результат. Можна в рядку пошуку задати наступний шаблон «3?????» . Знак питання означає Будь-який символ. Таким чином, програма буде шукати всі шестизначні числа, які починаються з цифри «3» . Тобто, у видачу пошуку потраплять значення в діапазоні 300000 – 400000 , що нам і потрібно. Якби в таблиці були числа менше 300000 або менше 200000 , то для кожного діапазону в сотню тисяч пошук довелося б проводити окремо.
Вводимо вираз «3?????» у полі »знайти" і тиснемо на кнопку " знайти все ».
- Після цього в нижній частині віконця відкриваються результати пошукової видачі. Кількома лівою кнопкою миші по кожному з них. Потім набираємо комбінацію клавіш Ctrl+A . Після цього виділяються всі результати пошукової видачі і одночасно виділяються елементи в стовпці, на які дані результати посилаються.
- Після того, як елементи в стовпці виділені, не поспішаємо закривати вікно " знайти та замінити» . Перебуваючи у вкладці »Головна" в яку ми перемістилися раніше, переходимо на стрічку до блоку інструментів «Шрифт» . Кількома по трикутнику праворуч від кнопки »колір заливки" . Відкривається вибір різних кольорів заливки. Вибираємо той колір, який ми бажаємо застосувати до елементів листа, що містить величини менш 400000 рублів.
- Як бачимо, всі осередки стовпця, в яких знаходяться значення менш 400000 рублів, виділені обраним кольором.
- Тепер нам потрібно пофарбувати елементи, в яких розташовуються величини в діапазоні від 400000 до 500000 рублів. У цей діапазон входять числа, які відповідають шаблону «4??????» . Вбиваємо його в поле пошуку і клацаємо по кнопці " знайти все» , попередньо виділивши потрібний нам стовпець.
- Аналогічно з попереднім разом в пошуковій видачі виробляємо виділення всього отриманого результату натисканням комбінації гарячих клавіш CTRL+A . Після цього переміщаємося до значка вибору кольору заливки. Кількома по ньому і тиснемо на піктограму потрібного нам відтінку, який буде фарбувати елементи листа, де знаходяться величини в діапазоні від 400000 до 500000 .
- Як бачимо, після цієї дії всі елементи таблиці з даними в інтервалі з 400000 по 500000 виділено вибраним кольором.
- Тепер нам залишається виділити останній інтервал величин-більше 500000 . Тут нам теж пощастило, так як всі числа більш 500000 знаходяться в інтервалі від 500000 до 600000 . Тому в поле пошуку вводимо вираз «5?????» і тиснемо на кнопку " знайти все» . Якби були величини, що перевищують 600000 , то нам би довелося додатково проводити пошук для вираження «6?????» і т. д.
- Знову виділяємо результати пошуку за допомогою комбінації Ctrl+A . Далі, скориставшись кнопкою на стрічці, вибираємо новий колір для заливки інтервалу, що перевищує 500000 за тією ж аналогією, як ми це робили раніше.
- Як бачимо, після цієї дії всі елементи стовпця будуть зафарбовані, згідно з тим числовим значенням, яке в них розміщено. Тепер можна закривати вікно пошуку, натиснувши стандартну кнопку закриття у верхньому правому куті вікна, так як нашу задачу можна вважати вирішеною.
- Але якщо ми замінимо число на інше, що виходить за межі, які встановлені для конкретного кольору, то колір не зміниться, як це було в попередньому способі. Це свідчить про те, що даний варіант буде надійно працювати тільки в тих таблицях, в яких дані не змінюються.
![Переход в окно Найти и заменить в Microsoft Excel](https://cdn.daad.org.ua/images/8049/8ced3f2429c9cb459798b39bf4bef37a.png)
![Запуск поиска в Microsoft Excel](https://cdn.daad.org.ua/images/8049/1a1d18f8e79165fadf3724dfb512003c.png)
![Выделение результатоа поисковой выдачи в Microsoft Excel](https://cdn.daad.org.ua/images/8049/f89c127ee55b3464d729443fda19576c.png)
![Выбор цвета заливки в Microsoft Excel](https://cdn.daad.org.ua/images/8049/0ef35477eb3ef158aaa13c36e706c43b.png)
![Ячейки выделены синим цветом в Microsoft Excel](https://cdn.daad.org.ua/images/8049/1cd06c3baee33e838e0cf106562f48d0.png)
![Поиск второго интервала значений в Microsoft Excel](https://cdn.daad.org.ua/images/8049/d60eb2346deded8da4a917c20b713af6.png)
![Выбор цвета заливки для второго диапазона данных в Microsoft Excel](https://cdn.daad.org.ua/images/8049/b8b1290770979c0cad14591ce352fa15.png)
![Ячейки выделены зеленым цветом в Microsoft Excel](https://cdn.daad.org.ua/images/8049/ab959a9b37bff208625833d80ebc9d09.png)
![Поиск третьего интервала значений в Microsoft Excel](https://cdn.daad.org.ua/images/8049/0fa1380ed306253888b95711db060316.png)
![Выбор цвета заливки для третьего диапазона данных в Microsoft Excel](https://cdn.daad.org.ua/images/8049/0c2c4ef6863f21f4e8636670f1ba6c67.png)
![Все ячейки окрашены в Microsoft Excel](https://cdn.daad.org.ua/images/8049/0e09ffa400b048dec5c93675b651009f.png)
![Цвет не поменялся после изменения значения в ячейке в Microsoft Excel](https://cdn.daad.org.ua/images/8049/08e49ca64b19d8eb1ec74fcfd7fb16f4.png)
Урок: як зробити пошук в Екселі
Як бачимо, існує два способи забарвити осередки в залежності від числових значень, які в них знаходяться: за допомогою умовного форматування і з використанням інструменту " знайти та замінити» . Перший спосіб більш прогресивний, так як дозволяє більш чітко задати умови, за якими будуть виділятися елементи листа. До того ж, при умовному форматуванні колір елемента автоматично змінюється, в разі зміни вмісту в ній, чого другий спосіб робити не може. Втім, заливку осередків в залежності від значення шляхом застосування інструменту " знайти та замінити» теж цілком можна використовувати, але тільки в статичних таблицях.