Використання умовного форматування в Google Таблицях

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

1. Щоб скористатися цією функцією, відкрийте Google Таблицю з підготовленим заздалегідь вмістом і виділіть всі осередки, на які має впливати умовне форматування. Після цього на верхній панелі розгорніть меню «Формат» та виберіть «Умовне форматування».
2. Основні параметри знаходяться у правій колонці поруч із робочою областю редактора, де спочатку необхідно визначитися з діапазоном застосування, особливо якщо ви не охопили потрібні клітини заздалегідь. Тут можна вручну вказати значення у форматі координат сервісу або скористатися спеціальним інструментом.
3. На відміну від більшості інших функцій, у цьому випадку підтримується більше однієї пари координат, наприклад, якщо потрібно виділити лише певні діапазони. Щоб це зробити, перерахуйте координати через кому або скористайтеся кнопкою «Додати інший діапазон» у спливаючому вікні.

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

Однотонна заливка

1. Використання єдиного кольору відкриває найбільше можливостей у плані оформлення з допомогою доступності повноцінних власних формул. Однак перед роботою з умовами потрібно приділити увагу оформленню, налаштування якого розташоване в рамках категорії «Формат». Якщо натиснути кнопку «Інше» у шапці вказаної панелі, можна вибрати один із стилів заливки, будь то однокольорове тло або фарбування безпосередньо текстових символів. Дане рішення можна використовувати як альтернативу ручному налаштуванню. Для самостійного створення стилю трохи нижче представлено кілька кнопок, що дозволяють додати спеціальне форматування тексту або змінити колір без будь-яких обмежень. Ми не будемо докладно зупинятися на цій категорії, оскільки велику роль відіграють особисті уподобання.
2. Після зміни тексту можна перейти до найцікавішої категорії «Форматувати комірки», що відповідає за вибір осередків таблиці, до яких буде застосований налаштований стиль. Найпростішими варіантами є базові значення з списку, що випадає, чи то "Текст містить" або «Більше чи одно».
3. Залежно від вибраного варіанта слід заповнити наведене нижче текстове поле та ввести значення для фільтрації відповідних осередків. Наприклад, за допомогою опції «Менше чи одно» можна вибрати всі числові значення менше або рівні певній цифрі, тоді як "Текст містить" дозволяє робити те саме з текстовими символами.
4. Більшої гнучкості можна досягти, якщо в самому кінці списку вибрати "Ваша формула" і заповнити зазначене вище поле повноцінною формулою на зразок автоматичного виділення цілого рядка при виявленні того чи іншого значення певної колонці, як у нашому прикладі.Як неважко здогадатися, для повноцінного використання такого варіанту необхідно добре знати формули та можливості Google Таблиці, які неможливо охопити в рамках однієї інструкції.
5. При нестачі єдиного правила можете скористатися посиланням «Додати правило» у нижній частині аналізованої колонки. В результаті буде створено ще один шар з умовами, в рамках якого можна зазначити координати та інші унікальні значення.
6. Зберегти зміни можна за допомогою кнопки «Готово» або навпаки відмовитися від створення умовного форматування, клікнувши «Скасування». Надалі ознайомитись із усіма створеними шарами можна буде при виборі інструменту.

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

Градієнтне заливання

1. На відміну від попереднього варіанта, градієнт на однойменній вкладці можна застосувати за допомогою обмеженої кількості умов. Для початку обов'язково виберіть стиль шляхом клацання лівою кнопкою миші по блоку «Попереднього перегляду».
2. Кольори в даному випадку розподілені за трьома діапазонами, у кожному з яких можна вибрати свій відтінок. Щоб зробити заливку, змініть значення у списку, що випадає, і в рядом розташованому текстовому полі.

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

Варіант 2: Мобільний пристрій

Офіційна програма сервісу в даний час підтримує умовне форматування за аналогією з веб-сайтом, але поширюється це виключно на пристрої Android. Якщо ви використовуєте iPhone, доведеться скористатися комп'ютером або браузерною версією Google Таблиць.

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

Як і на комп'ютері, тут є відразу дві вкладки з різними наборами параметрів. При роботі зі змішаним вмістом найкраще вибрати «Один колір», тоді як для чисел цілком достатньо "Градієнта".

Однотонна заливка

1. Щоб додати заливку певних елементів, у категорії "Застосувати до діапазону" введіть діапазон потрібних клітинок. Якщо потрібно, перераховувати значення можна через кому.
2. За допомогою списку, що випадає «Форматувати комірки, якщо» можна вибрати умови вибору певних клітин. Ми не будемо докладно зупинятися на різних варіантах, оскільки принцип роботи кожного параметра повністю відповідає настільній версії, але при цьому зауважимо, що найефективнішим параметром все одно є "Ваша формула".
3. у категорії «Стиль форматування» можете вибрати один із попередньо підготовлених стилів оформлення всіх осередків, що відповідають раніше призначеним вимогам, або створити «Стиль користувача». На жаль, на відміну від ПК-версії, у разі не можна відразу оцінити підсумковий зовнішній вигляд таблиці.

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

Градієнтне заливання

1. На вкладці "Градієнт" доступна менша кількість параметрів, серед яких насамперед потрібно приділити увагу полю "Застосувати до діапазону". У цьому випадку діапазон бажано обмежити строго числовими значеннями, тому що інакше можуть виникнути проблеми з відображенням.
2. Якщо вас влаштовують стандартні варіанти оформлення, у розділі «Стиль форматування» Ви можете вибрати один із наборів. Також трохи нижче можна створити «Стиль користувача» за аналогією з попереднім розділом.
3. Щоб застосувати нове оформлення до певних осередків, виберіть значення зі списку «Мін. крапка» для позначення першого кольору градієнта. Аналогічне потрібно буде зробити і з двома пунктами, що залишилися, в кінцевому рахунку скориставшись кнопкою «Зберегти» або «Зберегти та додати нове».

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

Умовне форматування в Google Таблиці (Google Sheets)

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

Щоб використовувати умовне форматування в Google Таблиці, необхідно вибрати в меню:

Формат > Умовне форматування

Де в меню умовне форматування в Google Таблиці

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

Бічна панель правил умовного форматування в Google Таблиці

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

Після того, як ми натиснемо на додати правило, у нас з'являються дві вкладки:

На початку розглянемо можливості вкладки Один колір. Правила форматування даної вкладки розбито на чотири блоки типів даних:

Правила умовного форматування текстових даних у Google Таблиці

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

Таблиця зі списком назв товарів для використання умовного форматування в Google Таблиці

У нас тут потрібно:

1. Вибрати діапозон (будь-який діапозон осередків)
2. Вибрати правило зі списку
3. Налаштувати формат (жирний, курсивний, підкреслений, закреслений, колір тексту, колір заливки)

Давайте пройдемося за кожним правилом форматування та подивимося, як вони працюють.

Правила форматування: Не містить даних

Застосувати до діапазону, вибираємо: A2:A20

Відповідно до нашого обраного діапазону, у нас не містять дані останніх 4 осередків і вони відформатовані відповідно до нашого формату.

Правила форматування: Містить дані

• Застосувати до діапозону, вибираємо: A2:A20
• Правило форматування, вибираємо: Містить дані
• Формат залишимо: За замовчуванням

Відповідно до нашого обраного діапазону, у нас містяться дані останні 15 осередків і вони відформатовані відповідно до нашого формату. Останні 4 залишилися невідформатованими, тому що в них немає даних.

Правила форматування: Текст містить

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

• Застосувати до діапозону, вибираємо: A2:A20
• Правило форматування, вибираємо: Текст містить
• Значні: Аскона
• Формат залишимо: За замовчуванням

Відповідно до нашого обраного діапазону, у нас містяться дані 1 осередок і вони відформатовані відповідно до нашого формату.

Якщо нам необхідно виділити осередки, в яких містяться значення: Аскона або Guten Morgen, то тут можна створити два правила:

• Перше правило, форматувати комірки, якщо текст містить Аскона,
• Друге правило, форматувати комірки, якщо текст містить Guten Morgen

Але правильніше буде написати формулу, яка поєднує дві наші умови. Формула виглядає так:

Умовне форматування в Google Таблиці, Правило форматування: Текст містить, у додатковому полі вказали формулу з регулярним виразом

Відповідно до обраного діапазону та регулярного виразу, у нас дані містять 2 осередки і вони відформатовані відповідно до нашого формату.

Давайте докладніше розберемо нашу формулу, що ми тут написала.Якщо перекласти її простою мовою, вона звучить так:

=ЯКЩО(ЗНАЧЕННЯ В осередку A2 ВІДПОВІДАЮТЬ РЕГУЛЯРНОМУ ВИРАЗУ "Аскона|Guten Morgen", ТО ВЗЯТИ ЗНАЧНІ осередки A2 Інакше повернути брехню/НІЧОГО)

Осередок А2 у нашій формулі – це початок діапозону, де ми хочемо застосувати умовне форматування.

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

Правила форматування: Текст не містить

Це правило аналогічне правилу текст міститьАле тут ми застосовуємо форматування до альтернативних записів. Зробимо налаштування із зазначенням аналогічними значенням, що і в прикладі вище:

• Застосувати до діапозону, вибираємо: A2:A20
• Правило форматування, вибираємо: Текст не містить
• Значні: Аскона
• Формат залишимо: За замовчуванням

Відповідно до нашого обраного діапазону, у нас не містять дані всі, крім одного осередку, і вони відформатовані відповідно до нашого формату.

Якщо нам необхідно виділити осередки в яких не міститься значення: Аскона або Guten Morgen, Тут повторюємо нашу формулу вище, яка об'єднує дві наші умови. Формула виглядає так:

Умовне форматування в Google Таблиці, Правило форматування: Текст не містить, у додатковому полі вказали формулу з регулярним виразом

Згідно з нашим обраним діапозоном, у нас не містять дані всі, крім двох осередків, де використовуються слова Аскона або Guten Morgen і вони відформатовані відповідно до нашого формату.

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

Правила форматування: Текст починається з

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: A2:A20
• Правило форматування, вибираємо: Текст починається з
• Значні: Подушка
• Формат залишимо: За замовчуванням

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

Правила форматування: Текст закінчується на

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: A2:A20
• Правило форматування, вибираємо: Текст закінчується на
• Значні: 2 шт
• Формат залишимо: За замовчуванням

Згідно з нашим обраним діапазоном, у нас текст закінчується на слово 2 шт у трьох осередках і вони відформатовані відповідно до нашого формату.

Правила форматування: Текст точно

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: A2:A20
• Правило форматування, вибираємо: Текст точно
• Значні: Комплект подушок Blue Sleep Mix 50х68 см, 2 шт.
• Формат залишимо: За замовчуванням

Відповідно до нашого обраного діапазону, у нас текст точно з Комплект подушок Blue Sleep Mix 50х68 см, 2 шт. в одному осередку і вона відформатована відповідно до нашого формату.

Правила умовного форматування Дати у Google Таблиці

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

Таблиця зі списком назв товарів та датою замовлення для застосування умовного форматування для дати в Google Таблиці

Правила форматування: Дата

При виборі даного правила, Google Таблиці пропонують вибрати одне з додаткових представлених значень:

• сьогодні
• завтра
• вчора
• минулого тижня
• минулого місяця
• минулого року
• точна дата

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

• Застосувати до діапозону, вибираємо: B2:B20
• Правило форматування, вибираємо: Дата
• Додаткове значення: точна дата
• Значні: 04.06.2023
• Формат залишимо: За замовчуванням

Ми бачимо на картинці вище, що в нашій таблиці умовне форматування застосовано до двох осередків.

Правила форматування: Дата до

При виборі цього правила форматування додаткові значення такі ж, як і правила форматування Дата:

• сьогодні
• завтра
• вчора
• минулого тижня
• минулого місяця
• минулого року
• точна дата

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: B2:B20
• Правило форматування, вибираємо: Дата до
• Додаткове значення: точна дата
• Значні: 01.04.2023
• Формат залишимо: За замовчуванням

У нас вийшло, що кількість осередків, що відповідають нашому умовному форматуванню, вийшло 9 штук, всі вони стали залиті кольором відповідно до нашого формату.

Правила форматування: Дата після

При виборі даного правила форматування кількість додаткових значень у нас змінилося їх залишилося всього 4:

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: B2:B20
• Правило форматування, вибираємо: Дата після
• Додаткове значення: точна дата
• Значні: 01.04.2023
• Формат залишимо: За замовчуванням

Кількість осередків, що відповідають нашому умовному форматуванню, вийшло 6 штук, всі вони залиті кольором відповідно до нашого формату.

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

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

=DATE(YEAR(B2);6;1)

Умовне форматування в Google Таблиці, Правило форматування: Дата після, в додатковому полі вказали формулу, якій повинна відповідати дата формулою, що повертається

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

• Застосувати до діапозону, вибираємо: B2:B20
• Правило форматування, вибираємо: Дата до
• Додаткове значення: точна дата
• Значні: =TODAY()
• Формат залишимо: За замовчуванням

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

Правила умовного форматування Чисел у Google Таблиці

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

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

Тут у нас 8 основних правил форматування, які пропонують у Google Таблиці:

• Більше
• Більше чи одно
• Менше
• Менше чи одно
• Рівно
• Не одно
• між
• Не між

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

Правила форматування: Більше

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: C2:C20
• Правило форматування, вибираємо: Більше
• Значні: 40
• Формат залишимо: За замовчуванням

Значення більше 40 у нас містять 5 осередків, всі вони залиті згідно з нашим форматом.

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

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: C2:C20
• Правило форматування, вибираємо: Більше
• Значні: =AVERAGE($C$2:$C$20)
• Формат залишимо: За замовчуванням

Значення більше 44 у нас містять 4 осередки, всі вони залиті згідно з нашим форматом.

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

Правила умовного форматування з використанням формули Google Таблиці

Для використання формул у правилах умовного форматування в Google Таблиці, найголовніше звертати увагу на формат даних:

• Якщо в клітинку текст, то наша формула повинна повертати текстове значення
• Якщо в комірку дату, то наша формула має повертати дату
• Якщо в комірку число, то наша формула повинна повертати числове значення

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

Правила форматування для текстових значень: Ваша формула

Необхідно застосувати умовне форматування до осередків, де в тексті зустрічаються словосполучення: Аскона та Guten Morgen.

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: A2:A20
• Правило форматування, вибираємо: Ваша формула
• Значні: =A2=IF(REGEXMATCH(A2;"Аскона|Guten Morgen")=TRUE();A2;FALSE())
• Формат залишимо: За замовчуванням

Правила форматування для дати: Ваша формула

Необхідно застосувати умовне форматування до осередків, де дата менша сьогодні.

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: B2:B20
• Правило форматування, вибираємо: Ваша формула
• Значні: =B2
• Формат залишимо: За замовчуванням

Правила форматування для чисел: Ваша формула

Необхідно застосувати умовне форматування, де значення вищі за середнє.

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: C2:C20
• Правило форматування, вибираємо: Ваша формула
• Значні: =C2>AVERAGE($C$2:$C$20)
• Формат залишимо: За замовчуванням

Ми дісталися до розгляду другої вкладки Градієнт. Градієнт це перехід між двома чи більше кольорами.

Правила умовного форматування вкладка Градієнт

Робимо налаштування із зазначенням значення:

• Застосувати до діапозону, вибираємо: C2:C20
• Правило форматування, вибираємо: Будь-який градієнт, що сподобався (для прикладу вибрали від червоного до зеленого)
• Інші налаштування залишили за замовчуванням, оскільки вони заточені під конкретні випадки.

У нашому з вами прикладі червоним кольором виділено мінімальне значення, зеленим кольором максимальне з плавним переходом через білий колір.

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