Виділити кольором комірки із значенням більше 500. Excel – зафарбувати комірки за умовою. Як виділити стовпець кольором в Excel за умовою

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

Колір заливки змінюється разом із значенням

Для прикладу ми потренуємося на тому, щоб комірка змінювала колір у цій таблиці за певної умови. Так, жодна, а все зі значенням в діапазоні від 60 до 90. Для цього ми скористаємося функцією «Умовне форматування».

Для початку виділіть той діапазон даних, який ми форматуватимемо.

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

У нас відчинилося вікно «Створення правил форматування». У цьому вікні вибираємо тип правила: "Форматувати тільки комірки, які містять".

Далі, переходимо до розділу «Змініть опис правила», де потрібно вказати ті умови, за якими буде заливка. У цьому розділі можна виставити різні умови, за яких вона буде змінюватися.

У разі необхідно поставити такі: «значення осередку» і «між». Також ми позначаємо діапазон, що за умови значення від 60 до 90 буде застосовано заливку. Подивіться на скріншоті, як це я зробив.

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

Якщо ви заповнили, то не поспішайте натискати на кнопку «ОК». Перш ніж натиснути на кнопку «Формат», як на скріншоті, і перейти до налаштування заливки.

Добре, як бачите, у вас відчинилося вікно «Формат осередку». Тут вам потрібно перейти на вкладку «Заливка», де ви вибираєте потрібну, та натиснути на «ОК» у цьому вікні та у попередньому. Я вибрав зелену заливку.

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

Пофарбуємо осередок у певний колір, якщо він дорівнює чомусь

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

На цей раз ми скористаємося функцією «Знайти та замінити».

Виділіть ту ділянку таблиці, в яку вноситимемо зміни. Якщо це весь лист, то виділяти немає сенсу.

Тепер час відкрити вікно пошуку. На вкладці «Головна» у розділі «Редагування» натисніть кнопку «Знайти та виділити».

Можна ж і гарячими клавішами користуватися: CTRL+F

У полі "Знайти" ми вказуємо те, що шукаємо. В даному випадку пишемо «40», а потім натискаємо кнопку «Знайти все».

Тепер, коли нижче були показані результати пошуку, виберіть одне з них і натисніть CTRL + A, щоб вибрати їх все відразу. А потім натисніть «Закрити», щоб прибрати вікно «Знайти та замінити».

Коли у нас вибрані всі, що містять цифру 40, на вкладці «Головна» у розділі «Шрифт» виберіть фарбу комірки. В нас це червоний. І як ви бачите у себе на екрані, так і у мене на скріншоті, вони пофарбувалися в червоний.

Тепер ті самі дії потрібно виконати, щоб пофарбувати ті, де вказано число 50. Думаю, тепер вам зрозуміло, як це зробити.

У вас вийшло? А подивіться, що вийшло в мене.

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

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

Інструкція для Excel 2010

Увімкніть субтитри!

Як це зробити в Excel 2007

Увімкніть субтитри!
Виділимо осередки з цінами замовлень і, натиснувши на стрілку поруч із кнопкою «Умовне форматування», оберемо «Створити правило».

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

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

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

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

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

Звернули увагу, що статуси задаються вибором зі списку значень? Як робити такі списки, ми розповідали в інструкції.

Як це зробити в Excel 2003

Увімкніть субтитри!
Умовне форматування в меню Формат. Тут знадобиться трохи більше ручної роботи. Ось так будуть виглядати налаштування для нашого першого завдання – зафарбувати комірки зі значеннями більшими за середні.

Прийде вручну ввести функцію «=СРЗНАЧ()», поставити курсор між дужками, натиснути на кнопку поруч і мишкою вказати потрібний діапазон.
Але принцип дій той самий.
Підкорюйте Excel до нових зустрічей!

Функція =КОЛІРЗАЛИВКИ(осередок)повертає код кольору заливки вибраного осередку. Має один обов'язковий аргумент:

КОМІРКА- Посилання на комірку, для якої необхідно застосувати функцію.

Нижче наведено приклад, що демонструє роботу функції.

Слід звернути увагу на те, що функція не перераховується автоматично. Це з тим, що зміна кольору заливки осередки Excel не призводить до перерахунку формул. Для перерахунку формули необхідно скористатися поєднанням клавіш Ctrl+Alt+F9

Приклад використання

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

За допомогою функції КВІТЗАЛИВКИвсе це стає здійсненним. Наприклад, "протягніть" цю формулу з кольором заливки в сусідньому стовпці і робіть обчислення на основі числового коду комірки.

Код VBA

Public Function КОЛІРЗАЛИВКИ(осередок As Range) As Double КОЛІРЗАЛИВКИ = осередок.Interior.Color End Function

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Спосіб 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 за тією ж аналогією, як ми це робили раніше.

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

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

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

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

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

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

Зафарбувати один або кілька блоків в Екселі не складно. Спочатку виділіть їх і на вкладці «Головна» натисніть на стрілку біля відерця з фарбою, щоб розгорнути список. Виберіть відповідний колір, а якщо нічого не підійде, натисніть «Інші кольори» .

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

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

Залежно від введених даних

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

Текстових

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

Виділяємо дані, з якими працюватимемо, у моєму випадку – це назви фруктів. Потім натискаємо "Умовне форматування", яке постійно використовуватимемо далі. Зі списку клікніть по "Створити правило".

Відкривається таке вікно. Вгорі вибираємо тип – «Форматувати лише комірки, які містять», Далі теж відзначатимемо саме його. Дещо нижче вказуємо умови: у нас текст, який містить певні слова. В останньому полі або натисніть кнопку і вкажіть комірку, або впишіть текст.

Відмінність у тому, що поставивши посилання на комірку (=$B$4 ), умова змінюватиметься в залежності від того, що в ній набрано. Наприклад, замість яблука у В4 вкажу смородину, відповідно зміниться правило, і будуть зафарбовані блоки з таким самим текстом. А якщо саме в полі вписати яблуко, то шукатиметься саме це слово, і воно ні від чого не залежатиме.

Тут виберіть колір заливки та натисніть «OK» . Для перегляду всіх варіантів натисніть на кнопку «Інші» .

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

В результаті всі блоки, в яких був зазначений текст, зафарбувалися в червоний.

Також створюються правила й інших фруктів, вибирається лише інший варіант заливки.

Числових

Тепер розберемося з числовими даними. Давайте числа в стовпці D розмістимо на певний фон за умовою, яку поставимо.

Виділяємо стовпець, створюємо правило, вказуємо його тип. Далі прописуємо - "Значення" "більше" "15". Останнє число може або ввести вручну, або вказати адресу осередку, звідки будуть братися дані. Визначаємось із заливкою, тиснемо «ОК».

Блоки, де введені числа більше за обраний, зафарбувалися.

Давайте для виділених осередків вкажемо ще правила - виберіть «Управління правилами».

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

Коли все буде готове, натисніть «Застосувати» та «ОК» .

Все працює, значення рівні та нижче 15 зафарбовані блідо-блакитним.

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

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

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

Вибираємо потрібні пункти у вікні, що відкрилося. Я заллю темно зеленим всі значення, що більше 90. Оскільки в останньому полі я вказала адресу (=$F$15 ), то при зміні в клітинці числа 90, наприклад, на 110, правило також зміниться. Збережіть зміни, натиснувши кнопку «ОК» .

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

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

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

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

Щоб подивитися, що Ви додавали, виберіть діапазон і у вікні «Управління правилами»буде повний перелік. Використовуючи кнопки вгорі, їх можна додавати, змінювати або видаляти.