Умовне форматування в excel він працює, ви відпочиваєте

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

Як це працює? Коротенько це робиться так:

  1. Виділіть ячейкуілі масив осередків. в яких потрібно зробити умовне форматування;
  2. На стрічці знайдіть групу команд. Головна - Стилі - Умовне форматування. У випадаючому меню зібрані всі можливі опції форматування з умовою;

Умовне форматування в excel він працює, ви відпочиваєте

  1. Вибираєте потрібну правило. вводите додаткові параметри, натискаєте ОК. Ось і все, тепер виділених клітинок заданий умовний формат, і при зміні значення в ній - формат теж зміниться.

Давайте розберемо по пунктах це меню, щоб розуміти що ж можна зробити з його допомогою.

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

  1. Більше заданого значення
  2. Менше заданого значення
  3. Між заданими значеннями
  4. Містить заданий текст
  5. Дата відповідає заданій (або проміжку)
  6. повторювані значення
  7. Інші правила - відкриє вікно «Створення правил форматування», про який я розповім трохи нижче в цій статті

Умовне форматування в excel він працює, ви відпочиваєте

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

Умовне форматування в excel він працює, ви відпочиваєте

Коли ви визначилися з умовою для форматування, вам запропонують вибрати один із стандартних стилів форматування, або створити свій власний. Щоб створити свій - виберіть в списку форматів «Призначений для користувача формат ...». Відкриється вже відоме нам вікно «Формат ячеек», в якому і задається власне оформлення.

На зображенні нижче масиву чисел задано умовне форматування «Між 50 і 70». Як бачимо, відповідні осередки виділені червоною палітрою кольорів.

Умовне форматування в excel він працює, ви відпочиваєте

При використанні цих команд, програма в своїй пам'яті (на аркуші) сортує масив і виводить за вашим бажанням:

  1. Перші N елементів (N - задаєте самі)
  2. Перші N відсотків
  3. Останні N елементів
  4. Останні N відсотків
  5. Значення вище середнього
  6. Значення нижче середнього

Умовне форматування в excel він працює, ви відпочиваєте

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

Умовне форматування в excel він працює, ви відпочиваєте

Гістограми - дуже зручний спосіб оцінити співвідношення даних без побудови діаграми. Осередки будуть залиті кольором в пропорції, що відповідає числу в ній. Повністю буде залита осередок з найбільшим числом, найменша заливка - в осередку з мінімальним числом. Подивіться, як це виглядає на моєму прикладі (стовпець С):

Умовне форматування в excel він працює, ви відпочиваєте

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

За замовчуванням, програма пропонує 6 колірних тим із суцільною заливкою і ще шість - з градієнтної. Натиснувши кнопку «Інші правила», ви можете налаштувати свою унікальну кольорову схему.

Умовне форматування в excel він працює, ви відпочиваєте

Таке форматування дуже схоже на гістограми з попереднього пункту, але змінюється залитий сегмент осередки, а колір заливки (градієнтом від одного кольору до іншого). Ось, як це виглядає на попередньому прикладі (від червоного до синього):

Умовне форматування в excel він працює, ви відпочиваєте

Очевидно, колірні шкали краще виглядатимуть у великих масивах даних, тоді як в малих - краще гістограми.

Як і в попередньому пункті, ви можете вибрати зі стандартних наборів тим, або натиснути «Інші правила» і вибрати кольору вручну.

Ще одна схожа група форматів. Тут в осередку відображається значок, відповідний значенням в ній:

Умовне форматування в excel він працює, ви відпочиваєте

За замовчуванням програма пропонує кілька наборів значків, а більш гнучкі налаштування можна зробити, натиснувши «Інші правила».

Умовне форматування в excel він працює, ви відпочиваєте

Тут же можна налаштувати пороги зміни значків. Можна, так само, поставити галочку «Показати тільки значок», тоді в комірці взагалі не буде доступний широкому значення.

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

Умовне форматування в excel він працює, ви відпочиваєте

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

Щоб створити правило умовного форматування з найбільшим числом параметрів - використовуємо пункт «Створити правило».

Умовне форматування в excel він працює, ви відпочиваєте

Водите в це меню і виберіть тип правила:

  1. Форматувати все осередки на підставі їх значень. Назва пункту говорить сама за себе. Відформатовані будуть все осередки виділеного діапазону в залежності від їх значення.

У списку «Стиль формату» можна вибрати потрібний вид оформлення:

  • Двоколірна шкала - задайте колір для мінімального і максимального значення, отримаєте свій унікальний формат

Умовне форматування в excel він працює, ви відпочиваєте

  • Триколірна шкала - задайте вже три кольори: мінімальне значення, максимальне і проміжне

Умовне форматування в excel він працює, ви відпочиваєте

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

Умовне форматування в excel він працює, ви відпочиваєте

  • Набори значків - вибирайте види значків і пороги їх перемикання, відключайте значки для непотрібних даних і відображення числових даних

Умовне форматування в excel він працює, ви відпочиваєте

Умовне форматування в excel він працює, ви відпочиваєте

  1. Форматувати тільки перші або останні значення. Дозволяє форматувати тільки задану кількість найменших або максимальних значень в масиві
  2. Форматувати тільки значення, які перебувають вище, або нижче середнього. Обчислює середнє значення в виділеному масиві і форматує лише ті значення, які більше або менше середнього (на ваш вибір)
  3. Форматувати тільки унікальні або повторювані значення. Думаю, назва пункту чудово характеризує його функціонал
  4. Використовувати формулу для визначення форматується значень. Запишіть логічну формулу самі, будуть відформатовані лише ті значення, для яких результат формули - істина. У моєму прикладі відформатовані будуть лише ті осередки, для яких перше значення стовпця більше 50.

Умовне форматування в excel він працює, ви відпочиваєте

Щоб видалити умовне форматування з виділеного діапазону, або з усього листа - натисніть на стрічці Головна - Стилі - Умовне форматування - Видалити правила. і виберіть де саме потрібно видаляти

Умовне форматування в excel він працює, ви відпочиваєте

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

Умовне форматування в excel він працює, ви відпочиваєте

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

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

У наступній статті я розповім вам що таке Міні-діаграми і як вони можуть замінити графіки Ексель. До зустрічі на сторінках блогу officelegko.com!