зміст
Осінні таблиці є одним із найпотужніших інструментів Excel. Вони дозволяють аналізувати та підсумовувати різні підсумки великих обсягів даних лише кількома клацаннями миші. У цій статті ми познайомимося зі зведеними таблицями, розберемося, що це таке, навчимося їх створювати та налаштовувати.
Цю статтю було написано за допомогою Excel 2010. Концепція зведених таблиць не зазнала значних змін протягом багатьох років, але спосіб їх створення дещо відрізняється в кожній новій версії Excel. Якщо у вас версія Excel не 2010, будьте готові, що скріншоти в цій статті будуть відрізнятися від того, що ви бачите на екрані.
трохи історії
У перші дні програмного забезпечення для роботи з електронними таблицями було правило Lotus 1-2-3. Його домінування було настільки повним, що спроби Microsoft розробити власне програмне забезпечення (Excel) як альтернативу Lotus здавалося марною тратою часу. А тепер швидко вперед до 2010 року! Excel домінує в електронних таблицях більше, ніж код Lotus будь-коли за свою історію, і кількість людей, які все ще використовують Lotus, близька до нуля. Як це могло статися? Що стало причиною такого драматичного повороту подій?
Аналітики виділяють два основні фактори:
- По-перше, Lotus вирішила, що ця новомодна платформа з графічним інтерфейсом під назвою Windows — це лише минуща мода, яка не триватиме довго. Вони відмовилися створювати Windows-версію Lotus 1-2-3 (але лише на кілька років), передбачаючи, що DOS-версія їхнього програмного забезпечення буде всім, що коли-небудь знадобиться споживачам. Microsoft природно розробила Excel спеціально для Windows.
- По-друге, Microsoft представила в Excel інструмент під назвою PivotTables, який не був доступний у Lotus 1-2-3. Зведені таблиці, ексклюзивні для Excel, виявилися настільки надзвичайно корисними, що люди схилялися дотримуватись нового програмного пакету Excel, а не продовжувати використовувати Lotus 1-2-3, у якому їх не було.
Зведені таблиці, поряд із недооцінкою успіху Windows загалом, зіграли смертельний марш для Lotus 1-2-3 і започаткували успіх Microsoft Excel.
Що таке зведені таблиці?
Отже, який найкращий спосіб охарактеризувати, що таке зведені таблиці?
Говорячи простою мовою, зведені таблиці — це підсумки деяких даних, створені для полегшення аналізу цих даних. На відміну від створених вручну підсумків, зведені таблиці Excel є інтерактивними. Після створення ви можете легко змінити їх, якщо вони не дають зображення, на яке ви сподівалися. Всього кількома клацаннями миші підсумки можна перевернути так, щоб заголовки стовпців стали заголовками рядків і навпаки. За допомогою зведених таблиць можна робити багато речей. Замість того щоб намагатися описати словами всі можливості зведених таблиць, простіше продемонструвати це на практиці...
Дані, які ви аналізуєте за допомогою зведених таблиць, не можуть бути випадковими. Це мають бути необроблені необроблені дані, як якийсь список. Наприклад, це може бути список продажів, здійснених компанією за останні шість місяців.
Подивіться на дані, показані на малюнку нижче:
Зауважте, що це не необроблені необроблені дані, оскільки їх уже було узагальнено. У клітинці B3 ми бачимо 30000 30000 доларів США, що, ймовірно, є загальним результатом, отриманим Джеймсом Куком у січні. Тоді де вихідні дані? Звідки взялася цифра в XNUMX XNUMX доларів? Де оригінальний список продажів, з якого була отримана ця місячна сума? Зрозуміло, що хтось зробив чудову роботу з організації та сортування всіх даних про продажі за останні шість місяців і перетворення їх у таблицю підсумків, яку ми бачимо. Як ви думаєте, скільки часу це зайняло? Година? XNUMX:XNUMX?
Справа в тому, що наведена вище таблиця не є зведеною. Він був створений вручну з необроблених даних, які зберігалися в інших місцях, і обробка зайняла принаймні пару годин. Саме таку зведену таблицю можна створити за допомогою зведених таблиць всього за кілька секунд. Давайте розберемося, як...
Якщо ми повернемося до початкового списку продажів, він виглядатиме приблизно так:
Ви можете бути здивовані, що з цього списку угод за допомогою зведених таблиць і всього за кілька секунд ми можемо створити місячний звіт про продажі в Excel, який ми проаналізували вище. Так, ми можемо зробити це та багато іншого!
Як створити зведену таблицю?
По-перше, переконайтеся, що у вас є вихідні дані на аркуші Excel. Перелік фінансових операцій є найбільш типовим з тих, що зустрічаються. Насправді це може бути список будь-чого: контактні дані співробітників, колекція компакт-дисків або дані про споживання палива вашою компанією.
Отже, ми запускаємо Excel ... і завантажуємо такий список ...
Після того як ми відкрили цей список в Excel, ми можемо почати створювати зведену таблицю.
Виберіть будь-яку клітинку зі списку:
Потім на вкладці вставка (Вставити) вибрати команду PivotTable (Зведена таблиця):
З'явиться діалогове вікно Створіть зведену таблицю (Створення зведеної таблиці) з двома запитаннями для вас:
- Які дані використовувати для створення нової зведеної таблиці?
- Де розмістити зведену таблицю?
Оскільки на попередньому кроці ми вже вибрали одну з комірок списку, весь список буде автоматично вибрано для створення зведеної таблиці. Зверніть увагу, що ми можемо вибрати інший діапазон, іншу таблицю і навіть деяке зовнішнє джерело даних, наприклад таблицю бази даних Access або MS-SQL. Крім того, нам потрібно вибрати, де розмістити нову зведену таблицю: на новому аркуші або на одному з існуючих. У цьому прикладі ми виберемо варіант – Новий аркуш (на новий аркуш):
Excel створить новий аркуш і розмістить на ньому порожню зведену таблицю:
Щойно ми клацнемо будь-яку комірку зведеної таблиці, з’явиться інше діалогове вікно: Список полів зведеної таблиці (Поля зведеної таблиці).
Список полів у верхній частині діалогового вікна — це список усіх заголовків із вихідного списку. Чотири порожні області внизу екрана дають змогу вказати зведеній таблиці, як ви хочете узагальнити дані. Поки ці області порожні, у таблиці також нічого немає. Все, що нам потрібно зробити, це перетягнути заголовки з верхньої області в порожні області внизу. При цьому автоматично формується зведена таблиця, згідно з нашими інструкціями. Якщо ми зробили помилку, ми можемо видалити заголовки з нижньої області або перетягнути інші, щоб замінити їх.
Область Цінності (Значення), мабуть, найважливіше з чотирьох. Заголовок, розміщений у цій області, визначає, які дані будуть узагальнені (сума, середнє, максимальне, мінімальне тощо). Це майже завжди числові значення. Відмінним кандидатом на місце в цій області є дані під заголовком сума (Вартість) нашої оригінальної таблиці. Перетягніть цей заголовок до області Цінності (Значення):
Зверніть увагу, що назва сума тепер позначено прапорцем і в області Цінності (Значення) з’явився запис Сума суми (поле Сума Сума), вказуючи, що стовпець сума підведені.
Якщо ми подивимося на саму зведену таблицю, то побачимо суму всіх значень зі стовпця сума оригінальний стіл.
Отже, наша перша зведена таблиця створена! Зручно, але не особливо вражає. Ймовірно, ми хочемо отримати більше інформації про наші дані, ніж ми маємо зараз.
Давайте звернемося до вихідних даних і спробуємо визначити один або кілька стовпців, які можна використовувати для розділення цієї суми. Наприклад, ми можемо сформувати нашу зведену таблицю таким чином, щоб загальний обсяг продажів розраховувався для кожного продавця окремо. Ті. до нашої зведеної таблиці буде додано рядки з іменами кожного продавця в компанії та їх загальною сумою продажів. Щоб досягти цього результату, просто перетягніть заголовок продавець (торговий представник) в обл Етикетки рядків (Рядки):
Стає ще цікавіше! Наша зведена таблиця починає формуватися…
Бачите переваги? За пару кліків ми створили таблицю, створення якої вручну зайняло б дуже багато часу.
Що ще ми можемо зробити? Ну в якомусь сенсі наша зведена таблиця готова. Ми створили корисне резюме вихідних даних. Важливу інформацію вже отримано! У решті цієї статті ми розглянемо деякі способи створення складніших зведених таблиць, а також дізнаємося, як їх налаштувати.
Налаштування зведеної таблиці
По-перше, ми можемо створити двовимірну зведену таблицю. Зробимо це за допомогою заголовка стовпця спосіб оплати (Спосіб оплати). Просто перетягніть назву спосіб оплати до області Етикетки стовпців (Стовпці):
Отримуємо результат:
Виглядає дуже круто!
Тепер зробимо об'ємну таблицю. Як би виглядав такий стіл? Давайте подивимось…
Перетягніть заголовок пакет (Комплекс) до площі фільтри звітів (Фільтри):
Зверніть увагу, де він…
Це дає нам можливість відфільтрувати звіт за ознакою «За який курортний комплекс було сплачено». Наприклад, ми можемо побачити розбивку по продавцям і за способами оплати для всіх комплексів, або в пару кліків мишкою змінити вигляд зведеної таблиці і показати таку ж розбивку тільки для тих, хто замовив комплекс Шукачі сонця.
Отже, якщо ви правильно це зрозуміли, то нашу зведену таблицю можна назвати тривимірною. Продовжимо налаштування…
Якщо раптом виявиться, що в зведеній таблиці повинна відображатися тільки оплата чеком і кредитною карткою (тобто безготівковий розрахунок), то ми можемо вимкнути відображення назви готівкові гроші (Готівка). Для цього поруч Етикетки стовпців натисніть стрілку вниз і зніміть прапорець у спадному меню готівкові гроші:
Давайте подивимося, як зараз виглядає наша зведена таблиця. Як бачите, колонка готівкові гроші зникла з неї.
Форматування зведених таблиць в Excel
Зведені таблиці, очевидно, є дуже потужним інструментом, але поки що результати виглядають трохи просто і нудно. Наприклад, цифри, які ми складаємо, не виглядають як суми в доларах – це просто цифри. Давайте це виправимо.
Спокусливо зробити те, до чого ви звикли в такій ситуації, просто виділити всю таблицю (або весь аркуш) і за допомогою стандартних кнопок форматування чисел на панелі інструментів встановити потрібний формат. Проблема цього підходу полягає в тому, що якщо ви коли-небудь зміните структуру зведеної таблиці в майбутньому (що станеться з імовірністю 99%), форматування буде втрачено. Нам потрібен спосіб зробити це (майже) постійним.
Спочатку давайте знайдемо запис Сума суми in Цінності (Значення) і натисніть на нього. У меню, що з’явиться, виберіть пункт Параметри поля значення (Параметри поля значення):
З'явиться діалогове вікно Параметри поля значення (Параметри поля значення).
Натисніть кнопку формат номера (Формат числа), відкриється діалогове вікно. Формат ячеек (формат комірки):
Зі списку Категорія (Числові формати) виберіть бухгалтерський облік (Фінансовий) і встановіть кількість десяткових знаків на нуль. Тепер натисніть кілька разів OKщоб повернутися до нашої зведеної таблиці.
Як бачите, числа відформатовано як суми в доларах.
Поки ми займаємося форматуванням, давайте налаштуємо формат для всієї зведеної таблиці. Є кілька способів зробити це. Використовуємо той, який простіший…
Натисніть Інструменти PivotTable: Дизайн (Робота зі зведеними таблицями: Конструктор):
Далі розгорніть меню, натиснувши на стрілку в нижньому правому куті розділу Стилі зведеної таблиці (Стилі зведеної таблиці), щоб побачити велику колекцію вбудованих стилів:
Виберіть будь-який відповідний стиль і подивіться на результат у вашій зведеній таблиці:
Інші параметри зведеної таблиці в Excel
Іноді потрібно фільтрувати дані за датами. Наприклад, у нашому списку угод дуже багато дат. Excel надає інструмент для групування даних за днями, місяцями, роками тощо. Давайте подивимося, як це робиться.
Спочатку видаліть запис. спосіб оплати з обл Етикетки стовпців (Колонки). Для цього перетягніть його назад до списку заголовків, а на його місце перемістіть заголовок Дата бронювання (дата бронювання):
Як бачите, це тимчасово зробило нашу зведену таблицю марною. Excel створив окремий стовпець для кожної дати, коли було здійснено угоду. В результаті ми отримали дуже широкий стіл!
Щоб виправити це, клацніть правою кнопкою миші будь-яку дату та виберіть у контекстному меню ГРУПА (Група):
З’явиться діалогове вікно групування. Ми вибираємо Місяців (Місяці) і натисніть OK:
Вуаля! Ця таблиця набагато корисніша:
До речі, ця таблиця практично ідентична наведеній на початку статті, де підсумки продажів складалися вручну.
Є ще один дуже важливий момент, який потрібно знати! Ви можете створити не один, а кілька рівнів заголовків рядків (або стовпців):
…і це буде виглядати так…
Те саме можна зробити із заголовками стовпців (або навіть фільтрами).
Давайте повернемося до початкового вигляду таблиці і подивимося, як відображати середні значення замість сум.
Щоб почати, натисніть на Сума суми і в меню, що з’явиться, виберіть Параметри поля значення (Параметри поля значення):
Список Підсумуйте поле значення за (Операція) у діалоговому вікні Параметри поля значення (Параметри поля значення) виберіть середній (Середній):
Водночас, поки ми тут, змінюймося CustomName (Власна назва) с Середня сума (Поле суми Сума) на щось коротше. Введіть у це поле щось на зразок Сер:
прес OK і подивіться, що станеться. Зверніть увагу, що всі значення змінилися з загальних на середні, а заголовок таблиці (у верхній лівій клітинці) змінився на Сер:
Якщо ви хочете, ви можете відразу отримати суму, середнє значення та кількість (продажів), розміщених в одній зведеній таблиці.
Ось покрокове керівництво щодо того, як це зробити, починаючи з порожньої зведеної таблиці:
- Перетягніть заголовок продавець (торговий представник) в обл Етикетки стовпців (Колонки).
- Перетягніть заголовок тричі сума (Вартість) до області Цінності (Значення).
- Для першого поля сума змінити назву на Усього: (Сума), і формат числа в цьому полі бухгалтерський облік (Фінансовий). Кількість знаків після коми дорівнює нулю.
- Друге поле сума ім'я Середнійe, установіть для нього операцію середній (Середнє) і формат числа в цьому полі також зміниться на бухгалтерський облік (Фінансовий) з нульовими знаками після коми.
- Для третього поля сума встановити заголовок Рахувати і операція для нього – Рахувати (Кількість)
- У Етикетки стовпців Поле (Стовпці) створюється автоматично Σ Значення (Σ Значення) – перетягніть його в область Етикетки рядків (Рядки)
Ось що у нас вийде:
Загальна сума, середнє значення та кількість продажів – усе в одній зведеній таблиці!
Висновок
Зведені таблиці в Microsoft Excel містять багато функцій і налаштувань. У такій невеликій статті вони навіть близько не охоплять усіх. Щоб повністю описати всі можливості зведених таблиць, знадобиться невелика книга або великий сайт. Сміливі та допитливі читачі можуть продовжити дослідження зведених таблиць. Для цього просто клацніть правою кнопкою миші майже будь-який елемент зведеної таблиці та подивіться, які функції та налаштування відкриваються. На стрічці ви знайдете дві вкладки: Інструменти зведеної таблиці: параметри (аналіз) і дизайн (Конструктор). Не бійтеся зробити помилку, ви завжди можете видалити зведену таблицю та почати все спочатку. У вас є можливість, якої ніколи не мали користувачі DOS і Lotus 1-2-3 протягом тривалого часу.