Проекти

Центр застосувань математики, інформатики, механіки і статистики

ГоловнаПроектиExcel для фінансистів: Зведені таблиці

Excel для фінансистів: Зведені таблиці

Можливості

 

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

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


Коротко нагадаємо основні можливості ЗТ.

З їх допомогою можна:

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

 

Підготовка даних

 

ЗТ формуються на основі діапазону вихідних даних (таблиця). Дані аркуша Excel повинні бути в форматі списку із заголовком стовпця в першому рядку. Решта рядків повинні містити аналогічні об'єкти в одному стовпці.
   
Приклад

Підприємство реалізує будівельні матеріали через чотири власних склад
и в Києві, Черкасах, Житомирі та Вінниці. В якості вихідної інформації у нас є звіт по продажах за 4-й квартал 2011 г, в якому містяться дані по реалізації 700 найменувань товарів (рис. 1). Необхідно отримати різноманітну підсумкову інформацію по містах, місяцях, товарним групам. Звіти повинні бути простими і інформативними.

Перед створенням ЗТ в Excel має сенс перетворити джерело даних в таблицю за допомогою команди Вставка → Таблиця. Опцію Таблиця із заголовками залиште включеною (рис. 1).


 

Навіщо це потрібно? Використання таблиць Excel набагато спрощує підтримку джерела даних ЗТ.

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

У стовпцях, доданих в таблицю Excel, автоматично включаються заголовки, наприклад «Стовпець 1». Це зроблено для того, щоб убезпечити від помилок, викликаних порожніми заголовками при створенні або оновленні ЗТ. Можна перейменувати заголовки за замовчуванням на зрозумілі.

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

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

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

 

Створення

 

На вкладці Вставка і в групі Таблиці розкриємо меню кнопки Зведена таблиця. Виберемо пункт Зведена таблиця. У вікні "Створення зведеної таблиці» в графі «Таблиця або діапазон» відобразиться Таблиця 1, якщо ви її не перейменували (рис. 2).

 

У групі «Вкажіть, куди слід помістити звіт зведеної таблиці» необхідно вибрати один з варіантів. Відповідно, на новому або існуючому листі з'явиться порожня ЗТ у вигляді контура макета, а праворуч аркуша відкриється область «Список полів зведеної таблиці» для створення звіту. У ньому можна вставляти в ЗТ нові поля, відзначаючи їх, створювати макет і настроювати звіт. Опис розділів ЗТ відображено у таблиці 1.

Таблиця 1. Опис розділів ЗТ
 

розділ ЗТ

опис

значення

Використовуються для виведення підсумкових числових даних

назви рядків

Використовуються для виведення полів у вигляді заголовків рядків

назви стовпців

Використовуються для виведення полів у вигляді заголовків стовпців у верхній частині звіту

Фільтр звітів

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

 

Відзначимо у вікні «Список полів» імена стовпців для створення ЗТ. Наприклад, зазначимо поля Місяць, Група товару, Виручка, грн, Склад. При цьому поля будуть автоматично додаватися в області ЗТ, їх можна перетягувати і міняти між собою, при цьому ЗТ мінятиметься автоматично (рис. 3).

 

 

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

Після створення початкового звіту ЗТ з таким звітом можна виконувати дії, зазначені у таблиці 2.

 

Таблиця 2 Дії, які можна виконувати після створення початкового звіту ЗТ
Цілі Можливості
вивчення даних Розгортання і згортання даних, а також відображення докладних відомостей про значеннях.

Сортування, фільтрування та групування полів і елементів.

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

Додавання, зміна та видалення полів.

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

Відображення підсумків над або під рядками даних.

Задання ширини стовпця при оновленні.

Переміщення поля стовпця в область рядків або поля рядка в область стовпців.

Об'єднання і скасування об'єднання комірок для зовнішніх елементів рядків і стовпців
Зміна способу відображення прогалин і помилок Зміна способу відображення помилок і порожніх клітинок.

Зміна способу відображення елементів і міток без даних.

Відображення або приховування порожніх рядків
зміна формату Умовне форматування і форматування вручну комірок і діапазонів.

Зміна загального стилю форматування ЗТ.

Зміна формату чисел для полів

 

Щоб звіт оновлювався автоматично, необхідно виконати наступні команди: Параметри → Зведена таблиця → Параметри. У вікні «Параметри зведеної таблиці» на вкладці «Дані» активуйте пункт «Оновити при відкритті файлу» (рис. 4). Також є кнопка Оновити все на стрічці Excel в розділі Дані.

 

 

В Excel в ЗТ існує можливість скасування більшості дій, виконаних для створення або перегрупування ЗТ.

В принципі, обмежень по кількості групувань, які ви можете додати в ЗТ, не існує. Щоб додати додаткове групування, перетягніть відповідні поля зі списку полів ЗТ в область рядків або стовпців ЗТ. Коли ви додаєте нове поле, Excel додає групування даних у відповідний розділ ЗТ.

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

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

Щоб приховати або розгорнути деталі в певній категорії, клацніть на значку +/-, розташованому поруч з назвою категорії (рис. 5)

 

 

Фільтрація і зрізи

 

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

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

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

Зрізи - це зручні у використанні компоненти фільтрації з набором кнопок, що дозволяють швидко виконувати фільтрацію даних у звітах ЗТ (рис. 6).

 

 

Як створити зріз в існуючій ЗТ?

Для цього потрібно:

  1. Клацнути в будь-якому місці звіту ЗТ, для якого потрібно створити зріз.
  2. У розділі Робота зі зведеними таблицями на вкладці Параметри в групі Сортування й фільтр натиснути кнопку Вставити зріз.
  3. У діалоговому вікні Вставка зрізів встановити прапорці навпроти полів ЗТ, для яких потрібно створити зріз (рис.7).

 

 

  1. Натиснути кнопку ОК. Для кожного з обраних полів буде відображено зріз.
  2. У кожному зрізі вибрати елементи, по яких потрібно виконати фільтрацію.

 

Щоб вибрати кілька елементів, клацніть їх по черзі, утримуючи клавішу Ctrl.

Наприклад, в ЗТ, зображеної на рис. 3, додамо зрізи по виробникам та найменуваннями товару. Вибравши в зрізі будь-якого виробника, наприклад «Полімін», в сусідньому зрізі ми побачимо, як відразу ж у верхню його частину автоматично відфільтруються і підтягнуться вгору найменування продукції саме цього виробника. Вони будуть з заливкою. Найменування інших виробників будуть перебувати нижче без заливки. Виділивши одне або кілька найменувань, ми побачимо в ЗТ результати їх продажу по місяцях і містам (рис. 8).

 

 

Зріз можна відформатувати. Для цього виберіть зріз. На стрічці з'явиться панель Інструменти для зрізу з вкладкою Параметри. На цій вкладці в групі Стилі зрізів виберіть необхідний стиль.

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

Для відключення зрізу:

  1. Клацніть якому місці звіту ЗТ, від якого потрібно відключити зріз. З'явиться розділ Робота зі зведеними таблицями з вкладками Параметри та Конструктор.
  1. На вкладці Параметри у групі Сортування й фільтр клацніть стрілку Вставити зріз, а потім виберіть пункт Підключення до зрізів.
  1. У діалоговому вікні Підключення зрізу зніміть прапорці всіх полів, від яких потрібна відключити зріз (рис. 9).

 

 

Для видалення зрізу виконайте одну з таких дій. Виберіть зріз і натисніть клавішу Delete або, клацнувши правою кнопкою миші по зрізу, виберіть пункт Видалити <ім'я зрізу>.

 

Обчислення

 

Коли ви додаєте поле в розділ Значення, Excel автоматично визначає функцію, на основі якої будуть проводитися підсумкові обчислення. У більшості випадків програма застосовує операцію підсумовування, яка складає все значення в поле. Щоб змінити тип обчислень, пропонований Excel, потрібно:

  1. У списку полів ЗТ в розділі Значення натиснути кнопку відповідного поля і вибрати команду Параметри полів значень.
  2. У вікні діалогу Параметри полів значень на вкладці Операція виберіть функцію для розрахунку підсумкових значень (рис. 10).

 

 

В поле І’мя користувача можна ввести назву поля, яке повинно відображатися в ЗТ.

Для настройки числового формату підсумкових значень натисніть кнопку Числовий формат і у вікні діалогу Формат комірок задайте необхідний формат.

Можна додати кілька полів в список Значення. У цьому випадку кожне поле обчислюється і відображається в окремому стовпці ЗТ.

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

 

Додаткові обчислення

 

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

 

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

Місяць - в область рядків.
Склад - в область стовпців.
Виручка - в область значень.
Група товару і виробник - в фільтр звіту (необов'язково).

На вкладці Параметри в розділі Активне поле натиснувши кнопку Параметри поля на вкладці Додаткові обчислення вибираємо опцію «Частка,% від суми по батьківському стовпцю». В результаті отримуємо звіт (рис. 11), в якому бачимо частку кожного складу в підсумках продажів по місяцях.

 

 

Помінявши місцями склад і місяць, можна побачити іншу картину: який вклад кожного місяця в кожному місті (рис. 12).

 

 

При виборі опції «% від суми по батьківській рядку» підсумок всіх рядків буде дорівнює 100% (рис. 13).

 

 

Також представляє інтерес опція Додаткові обчислення - Відмінність. Якщо в двох нижніх випадаючих списках Поле і Елемент вибрати, наприклад, Місяць і назад (рис. 14), то отримаємо ЗТ, в якій буде показана динаміка продажів по кожній товарній групі порівняно з попереднім місяцем (рис. 15).

 

 

Аналогічні результати для зручності сприйняття в відносних величинах можна отримати при тих же діях, але замість Відмінність вибрати Наведена відмінність (рис. 16). Також у вікні елементи можна вибрати не назад, а будь-який з місяців, тоді цей місяць буде базовим, а в інших місцях ми побачимо відхилення порівняно з обраним базовим місяцем.

 

 

Зауважимо, що в Excel 2010 додаткові обчислення розширені наступними функціями:

     % Від суми по батьківській рядку,
     % Від суми по батьківському стовпцю,
     % Від батьківської суми,
     % Від суми з наростаючим підсумком
     Сортування від мінімального до максимального
     Сортування від максимального до мінімального

 

Обчислювані поля й елементи

 

Часто при аналізі даних виникає необхідність в проведенні спеціальних обчислень. Якщо підсумкова таблиця повинна містити поле з обчисленим значенням, отриманим на основі інших полів або спеціальних значень, то можна створити обчислюване поле.

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

 

Приклад

Дізнаємося валовий прибуток за квартал по кожному з міст. Припустимо, що валовий прибуток становить 35% від виручки.

 

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

В ЗТ виділимо комірку, перед якою буде вставлено нове обчислюване поле. На панелі інструментів Робота зі зведеними таблицями виберемо команду Параметри → Обчислення → Поля, елементи та набори → Обчислюване поле. На екрані з'явиться діалогове вікно Вставка обчислюваного поля. У полі Ім'я введемо назву Валовий прибуток (рис. 17).

 

 

В поле Формула введемо = 'Виручка, грн' * 0,35. Щоб ввести аргумент Виручка, грн, достатньо двічі клацнути по цьому імені поля в нижньому випадаю лому списку Поля або натиснути кнопку Додати поле. Після підтвердження дії отримаємо ЗТ з новими обчислюваними полями.

 Якщо в полі значень вибрати поле Сума по полю, Валовий прибуток, то отримаємо звіт про прибутковість складів по місяцях (рис. 18). При бажанні, створення полів можна продовжити. Кожне створюване поле відображається як окреме поле даних. Нові обчислювані поля додаються в список діалогового вікна Список полів зведеної таблиці. Обчислювані поля схожі на будь-яке інше поле (як тільки ви їх створите, вони з'являться в списку полів ЗТ), проте їх можна поміщати тільки в область даних. Ці поля не можна переміщати в області сторінки, рядка і стовпчика.

 

 

Іноді потрібно зробити які-небудь обчислення за межами ЗТ, використовуючи дані, що знаходяться в ЗТ. Наприклад, в комірці D26 записана формула для розрахунку частки продажів теплих підлог в загальній виручці за квартал (рис. 19). Ця формула створена шляхом зазначення відповідних комірок ЗТ. Якби замість ЗТ була звичайна таблиця, то в комірці D26 була б записана формула = F20 / F25. Насправді в цій комірці автоматично записана формула

= ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Виручка, грн»; $ A $ 4; "Група товару»; "Теплі підлоги») / ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Виручка, грн»;$A$4).

 

 

Якщо задати посилання на комірку, яка включена в ЗТ, то Excel автоматично замінить адресу цієї комірки на функцію ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, що представляє вказану комірку. Навіщо замість звичайного посилання Excel підставляє таку функцію? Щоб правильні дані витягали з ЗТ навіть тоді, коли вид ЗТ змінюється. Якби в цих формулах використовувалися звичайні посилання на комірки, то при зміненому вигляді ЗТ вийшов би неправильний результат. Але використання цієї функції має один недолік: дані, які витягуються з ЗТ з її допомогою, повинні бути видимими на екрані.

 

Створення зведених діаграм

 

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

ЗД завжди будуються лише за даними ЗТ. Разом з тим Excel дозволяє створювати ЗД до побудови ЗТ, точніше при створенні ЗТ одночасно будується і ЗД.

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

 

ЗД можна побудувати двома способами:

- Коли курсор знаходиться на будь-якій комірці ЗТ, виконайте наступні команди: Робота зі зведеними таблицями → Параметри → Сервіс → Зведена діаграма;
- Виконайте команди: Вставка → Таблиці → Зведена таблиця → Зведена діаграма.

Побудуємо за допомогою вищеописаних команд ЗД, яка відобразить динаміку продажів трьох обраних груп товарів (герметики і піна монтажна, грунтовки, клеї) по кожному з складів (рис. 20). Надалі можна вибирати інші групи товарів або склади, переглядаючи результати за допомогою фільтра.

Якщо ж для фільтрації додати зрізи, проведення аналізу стане простим, наочним і цікавим.

 

 

При створенні та використанні ЗД майте на увазі, що:

- Між ЗТ і ЗД Excel автоматично встановлює двосторонній зв'язок. Тому, якщо внесені які-небудь структурні зміни в одному з цих об'єктів, вони відразу відіб'ються і на іншому об'єкті;

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

- Щоб приховати панель Область фільтра зведеної таблиці при виділеної ЗД, клацніть на кнопці Закрити, розташованої в правому верхньому кутку цієї панелі. Після цього дана панель не буде з'являтися при виділенні ЗД. Щоб панель Область фільтра ЗТ знову з'явилася, виберіть команду Робота зі зведеними діаграмами → Аналізувати → Показати або приховати → Список полів;

- Якщо після створення ЗД видалити вихідну ЗТ, то ЗД залишиться. Її поле Діапазон даних для діаграми зберігає вихідні дані у вигляді масиву;

- За замовчуванням ЗД впроваджується на той робочий лист, де знаходиться вихідна ЗТ. Щоб перемістити ЗД на інший робочий лист або на лист діаграм, виконайте команду Робота зі зведеними діаграмами → Конструктор → Розташування → Перемістити діаграму;

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

 

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

Read 20985 times Last modified on Четвер, 09 жовтня 2014 22:24

Leave a comment

Важливо

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

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

Н. Вінер

Що ми вміємо?

01Розглядати часткове явище в ролі прояву загального порядку.

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

03Послідовно і логічно вибудовувати складні концепції або операції.

На сайті Один гість та користувачі відсутні