Теми статей
Обрати теми

Excel 2007: умовне форматування

Редакція БК
Стаття

Excel 2007: умовне форматування

 

img 1 

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

Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики
та інформаційних технологій Харківської національної академії міського господарства

 

Форматування потрібне не лише для красивого оформлення документів. Це — його пряма, але не єдина мета. Бухгалтеру важливо організувати візуальний контроль над правильністю даних. Зручно користуватися таблицею, де «критичні» (або помилкові) значення Excel виділить добре помітним кольором чи покаже на контрастному фоні. Приємно працювати зі списком значень, в якому спеціальним форматом позначено мінімальні та максимальні елементи. А якби вдалося гнучко зв’язати «інтенсивність» форматування з умістом комірки, то оформлювальна функція перетворилася б на повноцінний засіб аналізу даних! Мабуть, такої ж думки дотримувалися й розробники Excel 2007. Створений ними інструмент умовного форматування в повному обсязі вирішує всі перелічені завдання. І більше того — пропонує для цього кілька альтернативних варіантів.

 

С

трічка умовного форматування

Викликаємо закладку «

Главная» основного меню Excel 2007. Знаходимо групу з трьох іконок з назвою «Стили». На першій іконці цієї групи зосереджено всі можливості умовного форматування комірок. Вона так і називається: «Условное форматирование» (рис. 1). Іконка являє собою список. Клацаємо по ній — відкриється меню з восьми пунктів (рис. 2). У верхній частині меню розташовано два розділи. Вони застосовуються для визначення умов накладення форматів і для форматування мінімальних та максимальних значень у блоці комірок. Ці розділи називаються «Правила выделения ячеек» і «Правила отбора первых и последних значений». Далі розташовано три пункти меню для спеціального форматування комірок робочого листа. Їх призначено для візуального виділення даних для їх аналізу чи з метою презентації. Так, пункт «Гистограммы» дозволяє забезпечити комірки колірними градієнтами, щоб бачити співвідношення величин у відформатованому списку. Аналогічне завдання вирішують два такі пункти меню: «Цветовые шкалы» і «Наборы значков». Тільки для форматування вони використовують замість градієнта фіксований набір кольорів чи спеціальних символів. У нижній частині меню зосереджено три пункти:

img 2

 

img 3

1) «

Создать правило…» — універсальний інструмент для створення будь-яких правил умовного форматування та всіх можливих варіантів оформлення;

2) «

Удалить правила» — дозволяє стерти раніше створені правила для комірок, листа чи всієї таблиці;

3) «

Управление правилами…» — активізує роботу модуля «Диспетчер правил условного форматирования».

Коміркам робочого листа можна присвоїти відразу кілька правил. Максимальна кількість таких правил в Excel 2007 становить 64, у попередніх версіях програми цей параметр дорівнював трьом. Крім того, починаючи з Excel 2007 умовне форматування можна вказувати для незв’язного блока комірок, раніше ж це було неможливо. Однак час переходити до практичної роботи. І почнемо ми з першого пункту меню — «

Правила выделения ячеек».

 

Створення правил для умовного форматування

Щоб наочно попрацювати з умовним форматуванням, нам знадобиться вихідний матеріал, тобто таблиця. Я вибрав для цієї мети фрагмент прайс-листа з попередньої статті (рис. 3). Нашим першим завданням буде виділити спеціальним форматуванням у прайс-листі позиції товарів, що задовольняють певну умову. Наприклад, виділимо всі роздрібні ціни, які перевищують величину 20 грн. Для цього виконуємо такі дії:

img 4

1) на робочому листі «

Прайс» виділяємо блок комірок «C2:C13»;

2) переходимо на закладку «

Главная» основного меню;

3) у групі «

Стили» клацаємо по іконці «Условное форматирование». Розкриється список з 8 варіантів (рис. 2);

4) з нього вибираємо пункт «

Правила выделения ячеек». З’явиться меню з 8 пунктів, показане на рис. 4;

img 5

5) клацаємо лівою кнопкою миші по рядку «

Больше…». З’явиться вікно, показане на рис. 5;

img 6

6) у лівій частині вікна розташоване поле для введення значення. Сюди вносимо число «

20»;

7) у правій частині вікна видно список з призначеними варіантами форматування. Клацаємо по списку лівою кнопкою миші, розкриється перелік із семи варіантів, як показано на рис. 5;

8) вибираємо будь-який спосіб форматування, наприклад «

Светло-красная заливка и темно-красный текст». Зміни у прайсі показано на рис. 6 (колонка «С»).

img 7

Важливо!

Вибираючи спосіб форматування, уважно стежте за змінами на робочому листі. Excel 2007 побудовано так, що показує передбачуваний вигляд комірок відразу після вибору варіанта оформлення із запропонованого списку (рис. 5). Таким чином, ви зможете оцінити результат форматування, не залишаючи вікна, показаного на рис. 5.

Унаслідок наших дій усі значення цін, що перевищують 20 грн., чудово видно у третій колонці прайсу. Це комірки «C2» і «C4».

Залишимо форматування колонки «С» без змін і поекспериментуємо з іншими розділами меню. У четвертій колонці прайсу (стовпець «D» з назвою «МО») спробуємо виділити дрібнооптові ціни, що потрапляють до діапазону від 3 до 10 грн. Для цього виконуємо такі дії:

1) на робочому листі виділяємо блок комірок «D2:D13»;

2) переходимо до розділу основного меню «Главная». Клацанням по іконці «Условное форматирование» розкриваємо меню, показане на рис. 2;

3) вибираємо пункт «Правила выделения ячеек». З’явиться меню, як на рис. 4;

4) вибираємо варіант «Между…». З’явиться вікно, показане на рис. 7;

img 8

5) як і слід було чекати, у цьому вікні два значення: мінімальна

та максимальна межі інтервалу. Уводимо величини «3» і «10». Тепер залишається визначити параметри форматування;

6) клацаємо по списку в правій частині вікна. Цього разу стандартні способи оформлення ми не застосовуватимемо, вибираємо пункт «

Пользовательский формат». Відкриється стандартне вікно форматування, показане на рис. 8;

img 9

7) із цим вікном ми вже знайомі. Переходимо на закладку «

Заливка», вибираємо з палітри жовтий колір. Усі дрібнооптові ціни, що знаходяться в діапазоні від 3 до 10 грн., буде показано на жовтому фоні (рис. 6, колонка «МО»).

Порада

Робота з призначеним для користувача форматом відкриває практично необмежені можливості при оформленні таблиць з використанням умовного форматування. Я раджу звернути увагу на варіант заливки візерунком. Такий спосіб для бухгалтера дуже зручний. Адже більшість документів доводиться не лише проглядати на екрані, а й відправляти на друк. У цьому випадку кольоровий фон комірок може значно погіршити читабельність документа. Застосуйте світлу візерункову заливку (наприклад, «12,5 %-ый серый» або «6,25 %-ый серый») — і документ добре виглядатиме як на екрані, так і на папері.

На практиці часто доводиться візуально аналізувати таблиці, в який дані зображено в текстовій формі. Типовий приклад — пошук товарів певної моделі або від конкретного виробника в довгому прайс-листі чи каталозі. Швидко впоратися з таким завданням допоможе умовне форматування за правилом «

Текст содержит…». Випробуємо його на нашому прикладі:

1) виділяємо на листі «Прайс» блок комірок «A2:A13» з найменуваннями товарів;

2) викликаємо інструмент умовного форматування, в меню «Правила выделения ячеек», вибираємо пункт «Текст содержит…»;

3) з’явиться вікно, аналогічне показаному на рис. 5. У полі для введення значення (у лівій частині вікна) друкуємо текст. Наприклад, такий: «dvd»;

4) установлюємо параметри форматування. Я вибрав призначений для користувача формат і візерункову заливку сірим;

5) натискуємо на «ОК». Усі товари, у назві який є текст «dvd», отримали візерункову заливку і тепер добре помітні на загальному фоні прайсу. На рис. 6 це комірки «A5», «A11», «A13».

Подивимося, що пропонують інші пункти меню «Правила выделения ячеек»:

— «Больше…» — дозволяє вибірково відформатувати комірки, значення в яких перевищують вибрану величину;

— «Меньше…» — накладає умовне форматування на комірки, в яких значення менше заданої величини;

— «Равно…» — форматує комірки, якщо значення точно збігається із зазначеним;

— «Дата…» — це можливість форматування комірок, де записано дати ;

— «Повторяющиеся значения…» дозволяє виділити в таблиці комірки з однаковими значеннями.

Вважаю, призначення перелічених пунктів зрозуміле з їх назви. Хотілося б дати коментарі лише з приводу двох розділів меню «Правила выделения ячеек»: «Дата…» і «Повторяющиеся значения…».

Розділ «Дата…» працює з комірками, в яких записано дати. Форма діалогу при роботі з цим розділом меню така сама, як показано на рис. 5, тільки замість поля для введення конкретного значення розташовано список . У ньому пропонується 10 варіантів: «Вчера», «Сегодня», «Завтра», «За последние 7 дней» тощо (рис. 9). Важливою перевагою такого способу форматування є те, що Excel автоматично визначає значення поточної дати і за нею коригує умови форматування. Наприклад, ви працюєте з базою даних надходжень ТМЦ, у якій в окремій колонці записано дату проведення операції. Накладіть на колонку з датами умовне форматування, вибравши варіант «Дата…» і умову «Сегодня». Тепер ви щодня бачитимете надходження за поточну дату. Для бухгалтера форматування даних з урахуванням хронології їх реєстрації — дуже важливий інструмент.

img 10

 

На практиці бухгалтеру доводиться мати справу з великими таблицями. Довідники, реєстри, бази даних проводок можуть налічувати сотні, тисячі, навіть десятки тисяч записів. Саме при роботі з такими обсягами переваги комп’ютера виявляються повною мірою. Тому розробники Excel 2007 приділили велику увагу засобам оброблення даних. Більше того, проблема роботи з великими обсягами даних відобразилася навіть в інструментах умовного форматування. У цьому випадку йдеться про правило вибору значень, що повторюються. Спробуємо застосувати його до нашого прайсу:

1) виділяємо на листі «Прайс» колонку з оптовими цінами — це блок комірок «E2:E13»;

2) викликаємо інструмент умовного форматування, у меню «Правила выделения ячеек», выбираем пункт «Повторяющиеся значения…»;

3) з’явиться вікно, показане на рис. 10. У цьому вікні поле для вибору значення представлене списком з двох елементів: «повторяющиеся» та «уникальные». За умовчанням стоїть «повторяющиеся»;

img 11

4) форматування залишаємо в положенні «

Светло-красная заливка и темно-красный текст». Клацаємо по кнопці «ОК». Excel змінив формат комірок «E11» і «E13»: у них записано однакову оптову ціну 2,34 грн. Вибравши як значення для пошуку варіант «уникальные», Excel відформатує у прайсі всі комірки, крім « E11» і «E13».

Умовне форматування зручно використовувати для аналізу великих баз даних. Близько місяця тому мені знадобилося знайти причину розбіжності в актах звіряння з одним контрагентом. Докладна база поставок і надходжень налічувала близько 4200 записів. Кропітка робота нашого бухгалтера за терміналом «1С» протягом півгодини успіхом не увінчалася. Цифри вперто не збігалися, і причина цього залишалася загадкою. Озброївшись Excel, я вирішив проблему десь за 3 хвилини. Спочатку базу відсортували за

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

Порада

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

Останнім розділом меню на рис. 4 є «

Другие правила…». Цю можливість ми розглянемо пізніше, коли почнемо знайомитися з Диспетчером форматування.

 

ВИДАЛЕННЯ ПРАВИЛ УМОВНОГО ФОРМАТУВАННЯ

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

1) виділяємо на листі «

Прайс» блок комірок з умовним форматуванням. У нашому випадку — це «A2:E13». Можна виділити просто колонки з «A» по «E»;

2) переходимо на закладку «

Главная», клацаємо по іконці «Условное форматирование»;

3) з’явиться меню, як показано на рис. 2. Ставимо покажчик на розділ «

Удалить правила». Excel покаже додаткове меню, зображене на рис. 11;

img 12

4) у ньому Excel пропонує чотири варіанти: «

Удалить правила из выделенных ячеек», «Удалить правила со всего листа», «Удалить правила из этой таблицы», «Удалить правила из этой сводной таблицы». Вибираємо «Удалить правила из выделенных ячеек» і натискуємо на «ОК». Форматування комірок скасоване, прайс-лист набув первісного вигляду, як зображено на рис. 3.

Вважаю, призначення інших пунктів меню коментувати немає сенсу: їх дія зрозуміла з назви.

 

Умовне форматування та вибір частини списку

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

1) відкриваємо прайс-лист: зараз у ньому немає правил умовного форматування, оскільки ми їх видалили;

2) виділяємо комірки з роздрібними цінами («

C2:C13»);

3) переходимо на розділ меню «

Главная», клацаємо по іконці «Условное форматирование». З’явиться меню, як показано на рис. 2;

4) вибираємо пункт «

Правила отбора первых и последних значений». З’явиться меню, зображене на рис. 12;

img 13

5) у цьому меню вибираємо «

10 первых элементов...». З’явиться вікно, показане на рис. 13;

img 14

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

5». Спосіб форматування не змінюємо;

7) у колонці «

С» нашого прайсу Excel виділить світло-червоною заливкою п’ять максимальних роздрібних цін;

8) спробуємо відкоригувати ціни у прайсі. Excel чітко відстежуватиме всі зміни та показуватиме п’ять найдорожчих позицій;

9) виділимо комірки «

C2:C13» та повторимо всі описані дії, але у вікні «Первые 10 элементов» укажемо значення порога, що дорівнює «3». У прайсі, як і раніше, буде виділено п’ять найдорожчих товарів! Висновок: застосовуючи нову умову форматування, не забувайте скасувати попередню. У нашому останньому випадку на комірки «C2:C13» у прайс-листі ми наклали дві умови: виділити п’ять найдорожчих позицій і виділити три найдорожчі позиції. При цьому формат комірок ми не змінили. Тому, врешті-решт, зможемо бачити результат роботи лише першої умови (п’ять найдорожчих позицій). Це — досить поширена помилка при роботі з умовними форматами. У нашому випадку, щоб побачити роботу обох умов, потрібно було використовувати різне форматування. Наприклад, перші три значення показати на червоному фоні, а перші п’ять — на зеленому.

Важливо!

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

Покажемо, як це працює на останньому прикладі:

1) видаліть усі правила з робочого листа «Прайс» (пункт «Удалить правила» в меню на рис. 2);

2) виділіть комірки «C2:C13»;

3) перейдіть на закладку «Главная». Клацніть по іконі «Условное форматирование»;

4) виберіть «Правила отбора первых и последних значений». У меню, що з’явилося, зазначте «10 первых элементов»;

5) у вікні параметрів уведіть значення порога «5» форматування «Зеленая заливка и темно-зеленый текст»;

6) натисніть на «ОК», щоб застосувати умовне форматування. П’ять максимальних цін буде показано на зеленому фоні;

7) не знімаючи виділення, повторіть пп. 3 — 5, але тепер укажіть значення порога «3», а формат комірок установіть у положення «Светло-красная заливка и темно-красный текст»;

8) натисніть на «ОК». У прайсі три максимальні ціни буде зображено на червоному фоні, а ще дві (з п’яти) залишаться на зеленому. Так сталося тому, що перше правило має загальніший характер, ніж друге. Тому частину форматування друге правило змінило, а частину — ні;

9) видаліть усі правила з робочого листа «Прайс»;

10) повторіть описані дії, тільки спочатку вкажіть правило з п. 7 (для трьох максимальних цін), а потім — правило з п. 5 (для п’яти максимальних цін). У результаті в колонці роздрібних цін п’ять максимальних значень буде показано на зеленому фоні. Правило виділення п’яти максимумів «перекрило» роботу правила для форматування трьох максимальних значень.

Порада

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

Тепер стисло пробіжимося по решті розділів меню «

Правила отбора первых и последних значений» (рис. 12):

1. «Первые 10 %…», «Последние 10 %…» — виділяють перші та останні 10 % комірок у зазначеному діапазоні робочого листа. Значення відсотка можна змінити. Кількість виділених елементів залежить від кількості комірок у діапазоні ! А від умісту в комірці залежить, чи підпаде вона під дію правила. Алгоритм роботи Excel у цьому випадку такий. Спочатку він виконує сортування значень за збільшенням (або за убуванням). На екрані цього сортування не видно, усе відбувається «всередині», у робочій області. Потім Excel підраховує загальну кількість елементів у списку, бере від цієї кількості 10 % і застосовує форматування до перших чи останніх комірок відсортованого списку. Після цього він показує результат на екрані, але без сортування.

2. «10 последних элементов…» — відформатує десять останніх елементів у списку виходячи з їх значення. Звичайно це мінімальні елементи. Поріг дії правила (за умовчанням він дорівнює «10») можна змінити.

3. «Выше среднего…», «Ниже среднего…» — дуже зручна можливість переглянути відхилення в даних. У цьому випадку Excel визначає середнє арифметичне для виділеного блока комірок та показує все, що відрізняється у бік збільшення (або зменшення).

4. «Другие правила…» — відкриває вікно «Создание правил форматирования», про яке ми поговоримо в наступній статті.

 

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

Тим часом я чекаю ваших листів, пропозицій та зауважень на

bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
App
Завантажуйте наш мобільний додаток Factor

© Factor.Media, 1995 -
Всі права захищені

Використання матеріалів без узгодження з редакцією заборонено

Ознайомитись з договором-офертою

Приєднуйтесь
Адреса
м. Харків, 61002, вул. Сумська, 106а
Ми приймаємо
ic-privat ic-visa ic-visa

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

Дякуємо, що читаєте нас Увійдіть і читайте далі