Умовне форматування щодо тексту в комірці Excel

У нас є дані для аналізу продукції, що виробляється. Нам потрібно автоматично виділити всі вироби, випущені в 2006 році. На жаль, у таблиці бракує ще одного стовпця, що містить значення року виробництва кожного виробу. Але видно, що у фрагменті ідентифікатора (ID) виробу вказується рік виробництва. У такому разі нам потрібно виконати пошук щодо тексту в Excel. А потім ми будемо використовувати умовне форматування із текстовими функціями у формулі. Завдяки цьому блискавично реалізуємо розв'язання задачі.

Приклад таблиці виробленої продукції:

Щоб на основі ідентифікатора виділити вироби 2006 року випуску виконаємо кроки наступних дій:

Виділіть діапазон комірок A2:A10 і виберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Створити правило».
Виберіть: «Використовувати формулу для визначення осередків, що форматуються».
Щоб виконати пошук частини тексту в осередку Excel, введіть формулу: =ПСТР(A2;5;4)="2006"
Натисніть кнопку «Формат», щоб встановити червоний колір заливки для комірки. І натисніть кнопку ОК на всіх відкритих вікнах.

Експоновані кольором вироби 2006 року випуску:

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

Функція ПСТР та її приклад використання умовному форматуванні

Щоб легко зрозуміти, як вдалося експонувати кольором певні значення за допомогою умовного форматування, розберемо етапи дій у двох словах. Спочатку ми отримуємо частину тексту, та був порівнюємо його з необхідним значенням. Але як із комірки витягти частину тексту в Excel? Звернімо увагу до функції =ПСТР() у формулі правила. Ця функція повертає частину рядка, взятого з кожноїосередки стовпця A, що зазначено у першому аргументі. Посилання в першому аргументі має бути відносною, так як формула застосовується до кожної комірки стовпця A. У другому аргументі функції вказується номер символу вихідного тексту, з якого має розпочатися відрізок рядка. У третьому аргументі вказується кількість символів, які слід взяти після певного (у другому аргументі) символу вихідного тексту. В результаті функція =ПСТР() повертає тільки частину тексту довжиною 4 символу взятого починаючи з 5-ї літери в кожному осередку зі стовпця А. Це "2005" або "2006". Після функції стоїть оператор порівняння до значення рядка "2006". Якщо комірка містить таку частину тексту, то їй буде присвоєно новий формат.

Аналогічно можна використовувати й інші текстові функції в умовному форматуванні. Наприклад, за допомогою функції =ПРАВСИМВ() ми можемо експонувати кольором певну групу товарів із партії C. Для цього потрібно використовувати формулу:

Тут все просто функція дозволяє вибрати частину тексту із комірки Excel, починаючи з правого боку вихідного тексту. Кількість необхідних символів вказано у другому аргументі функції ПРАВСІМВ. Після чого все повертає функція порівнюється зі значенням рядка "C".

У всіх вище описаних прикладах дуже легко застосовувати текстові функції в умовному форматуванні, оскільки довжина рядків у вихідних даних однакова. Але якщо у нас у вихідних даних різного типу індикатори з різною довгою символів, а нам все ще потрібно виділяти 2006-й рік або групу "C".

У розв'язанні цього завдання нам допоможе додаткова текстова функція у формулі =ЗНАЙТИ(). У першому випадку формула виглядатиме так:

Для зручного виділення ідентифікаторів з різною довгою текстовоюрядки товарів із групи "C" використовуємо таку формулу:

Функція =ЗНАЙТИ() шукає фрагмент тексту в комірці Excel. Потім повертає номер символу, в якому було знайдено текст, заданий у першому аргументі. У другому аргументі вказуємо, де шукати текст. А третій аргумент – це номер позиції, з якого символу вести пошук у вихідному тексті. Третій аргумент дозволяє нам зміщуватися рядком. Наприклад, якщо в ідентифікаторі двічі використовується символ "C". У такому разі третій аргумент задає користувач залежно від ситуації.

Так як функція повертає потрібне число ми чудово використовуємо її як аргументи для інших функцій (ПСТР і ПРАВСІМВ).