Суперзвіт в Exсel

Маловідомі можливості звичної програми

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

Павло Куратник,
заступник головного редактора
журналу «Справочник экономиста»

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

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

Візуалізація інформації стала головним трендом нашого часу. І для втілення найсміливіших задумів у цій сфері часто зовсім не потрібно придбавати нове програмне забезпечення або наймати нових працівників. Достатньо опанувати Exсel.

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

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

Починаючи з версії Excel 2007 спектр налаштувань істотно розширився. Зокрема, з'явилася можливість використовувати гістограми, колірні шкали та значки (вкладка Головна → кнопка Умовне форматування). У результаті користувач може довільно задавати колір заливки та облямівки стовпчиків гістограми. Причому заливка може бути як суцільним кольором, так і градієнтом. Щоб виконати таке налаштування, потрібно перейти за вкладками Головна → Умовне форматування → Управління правилами (рис. 1).

Наприклад, можна автоматично одним натисканням виокремлювати кольором:

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

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

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

Крім того, можна змішувати та зіставляти значки з різних наборів і легко ховати їх (наприклад, відображати значки лише для високих показників і не відображати для середніх і низьких значень).

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

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

Смарт-теги

Ця функція дозволяє швидко та зручно подавати дані. Йдеться, наприклад, про копіювання та вставляння діапазону. Часто буває необхідно, аби під час копіювання зберігалося форматування або вставлялися лише значення, а не результати роботи формул. У таких випадках дуже зручно користуватися смарт-тегами. Це невеличкі значки, які з'являються у правому нижньому кутку після вставляння скопійованого фрагмента або автозаповнення. В Excel версії 2007 року за допомогою натискання на таку кнопку можна було вибрати додаткові параметри дії, наприклад, вставити лише значення замість формул або лише форматування.

В Excel 2010 цю функцію реалізовано ще зручніше: під час вставляння з'являється аналогічний значок; натискання на нього відкриває цілу панель, на якій різні варіанти вставки позначені значками з підказками, що спливають.

Ось деякі з можливих варіантів «швидкого форматування» діапазону, що вставляється: формули, значення, зберегти вихідне форматування, зберегти ширину стовпчиків оригіналу, транспонувати, значення та формати чисел, значення і вихідне форматування (рис. 2).

Спарклайни та діаграми

Спарклайни, що з'явилися в Microsoft Excel 2010, — це невеличкі діаграми у клітинках аркуша, які візуалізують дані. Інша назва спарклайнів — інфокриві. За допомогою спарклайнів можна показувати тенденції у рядах значень і виокремлювати максимальні та мінімальні значення. Щоб досягти якнайкращого ефекту, спарклайни слід розташовувати поряд з відповідними даними. На відміну від діаграм на аркуші Excel, спарклайни не є об'єктами. Фактично спарклайн — це невеличка діаграма, вбудована в клітинку. До цієї клітинки можна вводити текст, а спарклайн при цьому відіграватиме роль фону.

Можна легко помітити зв'язок між спарклайном і даними, що він використовує. А в разі зміні даних — миттєво побачити відповідні зміни на спарклайні. Крім створення простого спарклайну на основі даних у рядку або стовпчику можна одночасно створювати кілька спарклайнів, обираючи кілька клітинок із відповідними даними. А також за допомогою маркера створювати спарклайни для рядків даних, що додаватимуться пізніше.

У Excel 2010 є можливість створювати три види інфокривих: графік, стовпчик або виграш (програш).

Інфокриві-графіки дають змогу ілюструвати тенденції та часові зміни значень, інфокриві-стовпчики — порівнювати значення, а інфокриві виграшу (програшу) — аналізувати значення стосовно стандарту.

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

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

Таблиці та зведені таблиці

Починаючи з версії Excel 2007 з'явилася можливість управляти таблицею як одним цілим, тобто створювати так звані зв'язані таблиці. Для створення такої таблиці призначена кнопка Форматувати як таблицю на панелі Стилі стрічки Головна (або Вставка → Таблиця). Виберіть потрібний стиль майбутньої таблиці та задайте діапазон клітинок, на основі якого буде створено зв'язану таблицю. Після налаштування усіх параметрів отримаємо зв'язану таблицю. Поряд із заголовками стовпчиків з'являться кнопки зі стрілками (аналогічно до фільтрації), а у вікні програми — контекстний інструмент Робота з таблицями, що містить стрічку Конструктор.

Створена таблиця автоматично підлаштовується за розмірами у разі додавання чи видалення з неї даних. Якщо дописати до такої таблиці нові рядки, вона розтягнеться нижче, якщо додати нові стовпчики — убік. У правому нижньому куті таблиці можна побачити маркер межі, який автоматично переміщується, і за потреби скоригувати його положення мишею. Якщо прокручувати таку таблицю вниз, заголовки стовпчиків (A, B, C...) змінюються на назви полів із автофільтром. У разі додавання до такої таблиці нових рядків до них автоматично копіюються всі формули. А якщо створити новий стовпчик із формулою, вона автоматично копіюється на весь стовпчик. До даних таблиці можна адресуватися, використовуючи імена окремих її елементів (рис. 4).

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

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

Коротко перелічимо основні можливості зведених таблиць. За їх допомогою можна:

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

В Excel 2010 стало простіше фільтрувати дані безпосередньо у звіті зведеної діаграми та вносити зміни до макета діаграми, додаючи і видаляючи поля. Крім того, одним кліком можна сховати всі кнопки полів на звіті зведеної діаграми. У цій версії додано кілька нових функцій додаткових обчислень (рис. 5).

Якщо ви часто користуєтеся зведеними таблицями, особливо з великими масивами вихідних даних (навіть мільйони рядків), є сенс завантажити надбудову PowerPivot для Excel (у Excel 2013 її вже вбудовано) (рис. 6). Надбудова PowerPivot поєднує вихідні можливості Excel і вбудовану підсистему пам'яті, що дає користувачам змогу вільно працювати з великими обсягами даних у режимі реального часу. Незалежно від кількості рядків зведені таблиці працюватимуть швидко. За допомогою надбудови PowerPivot для Excel можна швидко збирати й об'єднувати відомості з різних джерел, включаючи бази даних Access, аркуші, звіти та веб-канали даних. Завантаживши дані, можна вивчати їх в інтерактивному режимі, провадити над ними обчислення та формувати звіти за допомогою зведених таблиць, зрізів та інших інструментів Excel.

Зрізи

Працюючи з великими зведеними таблицями, часто доводиться їх примусово спрощувати, фільтруючи частину інформації, щоб не заплутатись у цифрах. Для цього помістіть деякі поля в область фільтра (у версіях до 2007 року вона називалася «область сторінок») і виберіть із списків, що випадають, лише потрібні значення.

У Excel 2010 все це робиться простіше за допомогою зрізів. Зрізи — це зручне графічне подання інтерактивних фільтрів звіту для зведеної таблиці або діаграми. Вставивши зріз, можна за допомогою кнопок швидко виокремлювати та відфільтрувати дані, представивши їх у потрібному вигляді. Крім того, якщо до даних зведеної таблиці застосовано більше ніж один фільтр, уже немає потреби відкривати список, щоб з'ясувати, що це за фільтри: вони відображаються безпосередньо на зрізі. Зрізи можна форматувати відповідно до формату книги та повторно використовувати їх в інших зведених таблицях, зведених діаграмах і функціях куба.

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

Щоб створити зріз для поточної зведеної таблиці, потрібно виконати такі команди: Параметри → Сортування і фільтр → Вставити зріз. Додатковий приємний нюанс полягає у тому, що зріз різними кольорами відображає не лише виділені, а й порожні елементи, для яких немає жодного значення у вихідній таблиці. Якщо використовуєте кілька зрізів, можна швидко наочно відобразити взаємозв'язки між елементами даних (рис. 7).

Швидкий аналіз в Excel 2013

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

Щоб спробувати, як працює Excel 2013, завітайте на офіційний сайт Microsoft та скачайте preview-версію цієї програми.

В Excel 2013 можна дуже швидко і легко скористатися майже всіма розглянутими вище можливостями за допомогою кнопки Експрес-Аналіз (Quick Analysis).

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

Перелічимо можливості такого швидкого аналізу (рис. 9):

• форматування (гістограми, колірна шкала, набір значків, більше, перші 10 %, очистити формат);
• діаграми (гістограма з групуванням, лінійчата з групуванням, графік, кругова, інші діаграми);
• підсумки (сума, середнє, лічильник, усього %, наростаючий підсумок);
• таблиці (таблиця, зведена таблиця);
• спарклайни (графік, гістограма, виграш/програш).

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

До швидкого аналізу умовно можна віднести також два нові пункти, що з'явилися на вкладці «Вставлення», а саме:

• рекомендовані діаграми;
• рекомендовані зведені таблиці.

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

Рекомендації

На теренах України ще достатньо користувачів, які у своїй роботі послуговуються Excel 2003. Їх непросто буде умовити перейти на новішу версію, навіть якщо є така можливість. Основний аргумент — «звикли». Щоби користувачі швидше вивчили новий інтерфейс або знаходили функції, добре знайомі за Excel 2003, фахівці корпорації «Майкрософт» розробили інтерактивну візуальну довідкову настанову, за допомогою якої можна буде швидко опанувати всі потрібні функції.

Досить вибрати необхідний пункт меню у звичній версії Excel 2003, і відразу з’явиться анімація тих самих дій, але в Excel 2010. Безоплатна анімація відповідності команд меню Excel 2003 і стрічки Excel 2010 доступна за адресою: http://office.microsoft.com/ru-ru/excel-help/HA101794130.aspx.

Настановою можна користуватися як через Інтернет (вона завантажується і працює в окремому вікні, див. рис. 8), так і встановивши на комп'ютер і працюючи через меню Пуск.

Менш зручний варіант — завантажити із сайту Microsoft файл-шаблон Excel, у якому також перелічено за пунктами меню Excel 2003 і відповідність команд стрічки Excel 2010.

Час не стоїть на місці. Хтось не хоче відмовлятися від Excel 2003, а хтось прагне спробувати повний функціонал Excel 2013. З одного боку, багато чого залежить від завдань, які стоять перед користувачем. А з іншого боку, користуючись старими версіями, те саме завдання можна виконувати довше, із меншою зручністю та з обмеженим функціоналом. Але будь-яка версія Exсel має безліч функцій, якими, на жаль, часто нехтують пересічні користувачі.

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

Дата підготовки
16.06.2014