Застосування умовного форматування в Excel 2010

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

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

Наведемо простий приклад зведеної таблиці, що описує обсяги продажів якогось товару по регіонах:

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

Найпростіший варіант – використовувати колірні шкали. Для цього виділяємо поле «Обсяг продажів», охоплюючи все періоди. Залишилося відкрити вкладку «Головна», де натискаємо кнопку «Умовне форматування» (якщо ви раптом використовуєте англійську версію, то дана функція називається «Conditional Formatting»). Наведіть курсор на «Гістограми».

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

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

Отже – готові сценарії, здатні допомогти в більшості ситуацій:

– перші 10 елементів;

– останні 10 елементів;

– перші 10%;

– останні 10%;

– більше середнього;

– менше середнього.

Видалення вже використовуваного умовного форматування в Excel 2010 відбувається за такою схемою: в зведеній таблиці переходимо на вкладку «Головна», натискаємо на «Умовне форматування», далі «Стилі», і в випадаючому меню використовуємо команду «Видалити правила» – «Видалити правила з цієї зведеної таблиці »(в англійському варіанті -« Clear Rules »і« Clear Rules from this PivotTable »).

Втім, можна легко створювати власні правила форматування.

Дана таблиця – ускладнений варіант першої, так що відразу переходимо до прикладу. Давайте відстежимо обсяг продажів і виручку за годину. Ми будемо використовувати умовне форматування Excel 2010 для прискорення пошуку збігів і відмінностей. Виділяємо «Обсяг продажів». Далі за стандартною процедурою активуємо сценарій ( «Головна» – «Умовне форматування»), але обираємо не готовий варіант, а функцію «Створити правило» (або «New Rule»).

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

– виділені ( «Selected Cells»);

– що входять до стовпець «Обсяг продажів» ( «All Cells Showing« Sales_Amount »Values»), включаючи проміжні і загальні підсумки. Даний варіант, до речі, добре підходить для аналізу тих даних, які вимагають визначення середнього, процентного співвідношення або іншими величинами, так чи інакше є різними рівнями однієї величини;

– входять до категорії «Обсяг продажів» тільки для «Ринку збуту» ( «All Cells Showing« Sales_Amount »Values ​​for« Market »»). Даний варіант повністю виключає загальні і проміжні підсумки, що зручно для аналізу деяких окремих значень.

Відзначимо, що команди «Обсяг продажів», «Ринок збуту» при створенні правил змінюються в залежності від наявних робочих таблиць.

За нашим прикладом найвигіднішим є варіант «3», тому використовується такий варіант:

При виборі правила (розділ «Вибрати правило» або «Select a Rule Туре») вказуємо саме те, яке і відповідає нашим вимогам.

Це може бути:

– «Форматування осередків на підставі значень» ( «Format All Cells Based on Their Values»). Використовується для форматування осередків, які відповідають вибраному діапазону значень. Найкраще підходить для визначення найрізноманітніших відхилень, якщо доводиться працювати з величезним набором даних.

– «Форматування осередків містять» ( «Format Only Cells That Contain»). Форматує осередки, що відповідають відповідним умовам. В даному випадку порівняння значень форматованих осередків зі звичайними не відбувається. Використовується для порівняння загального набору даних з вказаною раніше характеристикою.

– «Форматування перших і останніх значень» ( «Format Only Top or Bottom Ranked Values»).

– «Форматувати значення нижче або вище середнього» ( «Format Only Values ​​That Are Above or Below the Average»).

– «Використовувати формулу визначення форматується осередків» ( «Use a Formula to Determine Which Cells to Format»). Тут уже умови умовного форматування спираються на формулу, задану самим користувачем. Якщо значення комірки (з підставлених в формулу) приходить зі значенням «true», то до осередку застосовують форматування. У випадку зі значенням «false» форматування не застосовується.

Застосування гістограм, наборів значків і колірних шкал можливо тільки тоді, коли форматування виділених осередків відбувається на підставі значень, занесених в них. Для цього встановлюємо перший перемикач на «Форматування всіх осередків на підставі значень» ( «Format All Cells Based on Their Values»). Для позначення проблемних областей можна використовувати набір значків, що також добре підходить для даного сценарію.

Ну і залишилося визначити точні параметри нашого форматування. Тут стане в нагоді розділ «Зміни опису правила» ( «Edit the Ruie Description»). Для додавання значків в проблемні осередки, ми використовуємо меню, що випадає «Стиль формату» ( «Format Style») і вибираємо «Набори значків» ( «Icon Sets»).

У списку «Стилю значка» залишається вибрати значення «3 знаки» Це добре підійде, якщо наявну таблицю неможливо повністю розфарбувати. В результаті в віконці у нас повинно вийти наступне:

При такій конфігурації Excel самостійно додаватиме в осередку значки, при цьому дотримуючись функції:

>= 67, >= 33 і <33.

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

Додамо, що в кожному випадку граничні значення можуть легко змінюватися, а в прикладі просто використані значення «за замовчуванням».

Залишилося тільки застосувати наші правила до таблиці. В результаті вона зміниться ось так:

Залишилося застосувати це ж правило до колонку «Виручка за годину».

Але не поспішайте відразу ж закривати приклад – краще його проаналізувати. Адже будь-який більш-менш досвідчений менеджер відразу ж помітить взаємозв’язок між виручкою за годину і загальним доходом. Наприклад, що відповідає за ринок збуту в Далласі визначить, що у нього обсяг продажів є мінімальним, хоч виручка за годину і є найбільшою. Це дозволяє зробити висновок, що для його ринку виручка за годину занадто висока.

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

Для чого було останнім пояснення? Просто для наочного прикладу, наскільки умовне форматування в Excel 2010 полегшує сприйняття різної інформації. Володіючи необхідними навичками в створенні фільтрів форматування, можна швидко отримувати необхідну інформацію.



Ссылка на основную публикацию