зміст
- Динамічна зміна кольору тла комірки
- Як зберегти колір комірки незмінним, навіть якщо значення змінюється?
- Виділіть усі клітинки, які містять певну умову
- Зміна фону виділених клітинок у вікні «Формат клітинок».
- Редагування кольору фону для спеціальних комірок (порожніх або з помилками при написанні формули)
- Як отримати максимум від Excel?
У цій статті ви дізнаєтесь про два простих способи змінити фон клітинок на основі їх вмісту в останніх версіях Excel. Ви також зрозумієте, якими формулами змінювати відтінок комірок або комірок, де формули написані неправильно, або де інформація відсутня взагалі.
Всім відомо, що редагування фону простої комірки - це проста процедура. Натисніть «Колір фону». Але що, якщо вам потрібна корекція кольору на основі вмісту певної комірки? Як зробити так, щоб це відбувалося автоматично? Далі наведено низку корисних відомостей, які допоможуть вам знайти правильний спосіб виконання всіх цих завдань.
Динамічна зміна кольору тла комірки
Завдання: у вас є таблиця або набір значень, і вам потрібно відредагувати колір фону комірок залежно від того, яке число там введено. Вам також потрібно переконатися, що відтінок реагує на зміни значень.
рішення: для цього завдання передбачено функцію «умовного форматування» Excel, щоб розфарбувати клітинки з числами, більшими за X, меншими за Y або між X і Y.
Припустимо, у вас є набір продуктів у різних штатах із їхньою ціною, і вам потрібно знати, який із них коштує дорожче 3,7$. Тому ми вирішили виділити червоним кольором ті продукти, які вище цього значення. А клітини, які мають аналогічне або більше значення, було вирішено пофарбувати в зелений відтінок.
Примітка: Скріншот зроблений у версії програми 2010 року. Але це ні на що не впливає, так як послідовність дій однакова, незалежно від того, яку версію – останню чи ні – використовує людина.
Отже, ось що вам потрібно зробити (крок за кроком):
1. Виділіть комірки, у яких потрібно відредагувати відтінок. Наприклад, діапазон $B$2:$H$10 (назви стовпців і перший стовпець, який містить назви держав, виключені з вибірки).
2. Натисніть на "Головна" В групі “Стиль”. Буде предмет «Умовне форматування». Там теж потрібно вибрати «Нове правило». В англійській версії Excel послідовність дій така: «Домашня сторінка», «Група стилів», «Умовне форматування > Нове правило».
3. У вікні, що відкриється, поставте галочку «Форматувати лише клітинки, які містять» («Форматувати лише клітинки, які містять» в англійській версії).
4. У нижній частині цього вікна під написом «Форматувати лише клітинки, які відповідають наступній умові» (Форматувати тільки клітинки за допомогою) можна призначити правила, за якими буде виконуватися форматування. Ми вибрали формат для вказаного значення в клітинках, який має бути більше 3.7, як видно на скріншоті:
5. Далі натисніть на кнопку "Формат". З’явиться вікно з областю вибору кольору фону зліва. Але перед цим потрібно відкрити вкладку «Заповнити» («Заповнити»). В даному випадку він червоний. Після цього натисніть на кнопку «ОК».
6. Потім ви повернетеся до вікна «Нове правило форматування», але вже внизу цього вікна ви можете попередньо переглянути, як ця комірка буде виглядати. Якщо все в порядку, потрібно натиснути на кнопку «ОК».
В результаті ви отримаєте щось на зразок цього:
Далі потрібно додати ще одну умову, тобто змінити фон комірок зі значеннями менше 3.45 на зелений. Щоб виконати це завдання, необхідно натиснути на «Нове правило форматування» і повторіть наведені вище дії, лише умову потрібно встановити як «менше або еквівалент» (в англійській версії «менше або дорівнює», а потім написати значення. В кінці потрібно натиснути кнопку «ОК».
Тепер таблиця відформатована таким чином.
Він відображає найвищі та найнижчі ціни на пальне в різних штатах, і ви можете відразу визначити, де ситуація найбільш оптимістична (звісно, у Техасі).
Рекомендація: При необхідності можна використовувати аналогічний спосіб форматування, редагуючи не фон, а шрифт. Для цього у вікні форматування, що з’явилося на п’ятому етапі, потрібно вибрати вкладку "Шрифт" і дотримуйтесь підказок у вікні. Все інтуїтивно зрозуміло, розібратися зможе навіть новачок.
В результаті ви отримаєте ось таку таблицю:
Як зберегти колір комірки незмінним, навіть якщо значення змінюється?
Завдання: Ви повинні розфарбувати фон таким чином, щоб він ніколи не змінювався, навіть якщо фон зміниться в майбутньому.
рішення: знайдіть усі клітинки з певним числом за допомогою функції Excel «Знайти все» «Знайти все» або надбудова «Виділити спеціальні комірки» («Виберіть спеціальні комірки»), а потім відредагуйте формат комірки за допомогою функції «Формат клітинок» («Формат клітинок»).
Це одна з тих рідкісних ситуацій, які не розглядаються в інструкції по Excel, і навіть в Інтернеті рішення цієї проблеми можна зустріти досить рідко. Що й не дивно, адже це завдання не стандартне. Якщо ви хочете назавжди відредагувати фон, щоб він ніколи не змінювався, доки його не налаштує вручну користувач програми, ви повинні дотримуватись інструкцій, наведених вище.
Виділіть усі клітинки, які містять певну умову
Існує кілька можливих методів, залежно від того, яке конкретне значення потрібно знайти.
Якщо потрібно позначити осередки з певним значенням спеціальним фоном, необхідно перейти на вкладку "Головна" І вибирай «Знайти і вибрати» – «Знайти».
Введіть необхідні значення та натисніть «Знайти все».
Документи: Ви можете натиснути на кнопку “Параметри” праворуч, щоб скористатися перевагами деяких додаткових налаштувань: де шукати, як переглядати, чи поважати великі та малі літери тощо. Ви також можете ввести додаткові символи, наприклад зірочку (*), щоб знайти всі рядки, що містять ці значення. Якщо ви використовуєте знак питання, ви можете знайти будь-який окремий символ.
У нашому попередньому прикладі, якби ми хотіли знайти всі ціни на паливо від 3,7 до 3,799 доларів США, ми могли б уточнити наш пошуковий запит.
Тепер виберіть будь-яке зі значень, яке знайшла програма в нижній частині діалогового вікна, і натисніть на одне з них. Після цього натисніть комбінацію клавіш «Ctrl-A», щоб вибрати всі результати. Далі натисніть на кнопку «Закрити».
Ось як вибрати всі комірки з певними значеннями за допомогою функції «Знайти все». У нашому прикладі нам потрібно знайти всі ціни на паливо вище $3,7, і, на жаль, Excel не дозволяє зробити це за допомогою функції «Знайти та замінити».
Тут виявляється «бочка меду», тому що є інший інструмент, який допоможе в таких складних завданнях. Він називається «Вибір спеціальних клітинок». Ця надбудова (яку потрібно встановити в Excel окремо) допоможе:
- знайти всі значення в певному діапазоні, наприклад від -1 до 45,
- отримати максимальне або мінімальне значення в стовпці,
- знайти рядок або діапазон,
- знаходити клітинки за кольором фону та багато іншого.
Після встановлення надбудови просто натисніть кнопку «Вибрати за значенням» («Вибрати за значенням»), а потім уточніть пошуковий запит у вікні надбудови. У нашому прикладі ми шукаємо числа, більші за 3,7. Прес «Вибрати» («Виберіть»), і через секунду ви отримаєте такий результат:
Якщо вас зацікавив додаток, ви можете завантажити пробну версію за адресою зв'язок.
Зміна фону виділених клітинок у вікні «Формат клітинок».
Тепер, після того, як одним із описаних вище способів були виділені всі осередки з певним значенням, залишилося задати для них колір фону.
Для цього потрібно відкрити вікно «Формат комірки»натиснувши клавіші Ctrl + 1 (також можна клацнути правою кнопкою миші на виділених комірках і клацнути лівою кнопкою миші на пункті «форматування комірки») і налаштувати потрібне форматування.
Ми виберемо помаранчевий відтінок, але ви можете вибрати будь-який інший.
Якщо вам потрібно відредагувати фоновий колір без зміни інших параметрів зовнішнього вигляду, ви можете просто натиснути кнопку «колірна заливка» і виберіть колір, який вам ідеально підходить.
Результатом є така таблиця:
На відміну від попереднього способу, тут колір комірки не зміниться, навіть якщо значення буде відредаговано. Це можна використовувати, наприклад, для відстеження динаміки товарів певної цінової групи. Їх вартість змінилася, але колір залишився колишнім.
Редагування кольору фону для спеціальних комірок (порожніх або з помилками при написанні формули)
Подібно до попереднього прикладу, користувач має можливість редагувати колір фону спеціальних клітинок двома способами. Є статичний і динамічний варіанти.
Застосування формули для редагування фону
Тут колір клітинки буде редаговано автоматично на основі її значення. Цей метод дуже допомагає користувачам і затребуваний в 99% ситуацій.
Як приклад можна використовувати попередню таблицю, але тепер деякі клітинки будуть порожніми. Нам потрібно визначити, які з них не містять показань, і відредагувати колір фону.
1. На вкладці "Головна" вам потрібно натиснути «Умовне форматування» -> «Нове правило» (те саме, що в кроці 2 першого розділу «Динамічна зміна кольору фону».
2. Далі необхідно вибрати пункт «Визначте за формулою…».
3. Введіть формулу =IsBlank() (ISBLANK у версії), якщо ви хочете відредагувати фон порожньої клітинки, або =IsError() (ISERROR у версії), якщо потрібно знайти комірку, де є помилково написана формула. Так як в даному випадку потрібно редагувати порожні комірки, то вводимо формулу =IsBlank(), а потім помістіть курсор між дужками та натисніть кнопку поруч із полем введення формули. Після цих маніпуляцій слід вручну виділити діапазон комірок. Крім того, ви можете самостійно вказати діапазон, наприклад, =IsBlank(B2:H12).
4. Натисніть на кнопку «Формат» і виберіть відповідний колір фону і виконайте все, як описано в пункті 5 розділу «Динамічна зміна кольору фону комірки», а потім натисніть «ОК». Там же можна побачити, якого кольору буде клітина. Вікно буде виглядати приблизно так.
5. Якщо вам подобається фон комірки, необхідно натиснути на кнопку «ОК», і зміни будуть негайно внесені в таблицю.
Статична зміна кольору фону спеціальних комірок
У цій ситуації після призначення фоновий колір залишатиметься таким, незалежно від того, як змінюється клітинка.
Якщо вам потрібно назавжди змінити спеціальні клітинки (порожні або містять помилки), дотримуйтесь цих інструкцій:
- Виділіть документ або кілька комірок і натисніть клавішу F5, щоб відкрити вікно Перейти, а потім натисніть кнопку «Висвітлення».
- У діалоговому вікні, що відкриється, натисніть кнопку «Пусті» або «Порожні клітинки» (залежно від версії програми – або англійської), щоб виділити порожні клітинки.
- Якщо потрібно виділити комірки, що містять формули з помилками, слід вибрати пункт «Формули» і залиште один прапорець біля слова «Помилки». Як випливає з наведеного вище скріншоту, комірки можна вибирати за будь-якими параметрами, і кожна з описаних налаштувань доступна при необхідності.
- Нарешті, вам потрібно змінити колір фону виділених клітинок або налаштувати їх будь-яким іншим способом. Для цього скористайтеся описаним вище способом.
Просто пам’ятайте, що зміни форматування, внесені таким чином, збережуться, навіть якщо ви заповните прогалини або зміните спеціальний тип комірки. Звичайно, навряд чи хтось захоче скористатися цим методом, але на практиці все може бути.
Як отримати максимум від Excel?
Як активний користувач Microsoft Excel, ви повинні знати, що він містить багато функцій. Деякі з них ми знаємо і любимо, а інші залишаються загадковими для звичайного користувача, і велика кількість блогерів намагаються хоч трохи пролити на них світло. Але є загальні завдання, які кожному з нас доведеться виконувати, і Excel не містить деяких функцій або інструментів для автоматизації деяких складних дій.
І рішенням цієї проблеми є аддони (аддони). Деякі з них розповсюджуються безкоштовно, інші – за гроші. Існує багато подібних інструментів, які можуть виконувати різні функції. Наприклад, знайдіть дублікати у двох файлах без загадкових формул або макросів.
Якщо поєднати ці інструменти з основними функціями Excel, можна досягти дуже чудових результатів. Наприклад, ви можете дізнатися, які ціни на пальне змінилися, а потім знайти дублікати у файлі за минулий рік.
Ми бачимо, що умовне форматування - це зручний інструмент, який дозволяє автоматизувати роботу над таблицями без будь-яких спеціальних навичок. Тепер ви знаєте, як заповнити комірки кількома способами залежно від їх вмісту. Тепер залишилося тільки втілити це в життя. Удачі!