Робота з зведеними таблицями в Microsoft Excel

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

Примітка: Ця стаття написана за допомогою Excel 2010 (Beta). Концепція зведеної таблиці мало змінювалася протягом багатьох років, але метод створення одного змінювався майже в кожній ітерації Excel. Якщо ви використовуєте версію Excel, яка не є 2010, розраховуйте на різні екрани від тих, які ви бачите в цій статті.

Трохи історії

У перші дні роботи з програмами електронних таблиць Lotus 1-2-3 керував місцем відпочинку. Його домінування було настільки повним, що люди думали, що для Майкрософт було марно витрачати час на розробку власного програмного забезпечення для роботи з таблицями (Excel), щоб конкурувати з Lotus. Flash-вперед до 2010, і домінування Excel на ринку електронних таблиць більше, ніж коли-небудь був Lotus, в той час як число користувачів, які все ще працюють Lotus 1-2-3 наближається до нуля. Як це сталося? Що викликало настільки різке зміна стану?

Промислові аналітики привели це до двох факторів: По-перше, Lotus вирішила, що ця нова фантастична платформа з графічним інтерфейсом під назвою «Windows» була прохідною примхою, яка ніколи не вилетить. Вони відмовилися створювати версію Windows Lotus 1-2-3 (протягом декількох років, так чи інакше), передбачаючи, що їхня версія DOS цього програмного забезпечення буде потрібна будь-кому. Microsoft, природно, розробила Excel виключно для Windows. По-друге, Microsoft розробила функцію для Excel, яку Lotus не надав у 1-2-3, а саме Зведені таблиці. Функція PivotTables, що є виключною для Excel, вважалася настільки приголомшливо корисною, що люди хотіли вивчити весь новий пакет програмного забезпечення (Excel), а не дотримуватися програми (1-2-3), яка її не мала. Ця особливість, разом з неправильною оцінкою успіху Windows, стала смертельним дзвоном для Lotus 1-2-3 і початком успіху Microsoft Excel.

Розуміння зведених таблиць

Отже, що таке PivotTable, точно?

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

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

Перегляньте наведені нижче дані:

Зверніть увагу, що це так ні необроблені дані. Насправді, це вже є резюме деякого роду. У клітині B3 ми бачимо 30 000 доларів, що, очевидно, є сумою продажів Джеймса Кука за січень. Так де ж вихідні дані? Як ми дійшли до цифри $ 30,000? Звідки походить оригінальний перелік операцій з купівлі-продажу? Зрозуміло, що десь хтось повинен був зіткнутися з усіма операціями з продажу за останні шість місяців у резюме, яке ми бачимо вище. Як довго ви вважаєте, що це відбулося? Години? Десять?

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

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

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

Як створити зведену таблицю

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

Тому ми запускаємо Excel… і завантажуємо такий список…

Після того, як список відкриється в Excel, ми готові почати створення зведеної таблиці.

Натисніть будь-яку одну клітинку у списку:

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

The Створити зведену таблицю з'являється вікно, яке задає вам два запитання: Які дані має базуватися на вашій новій зведеної таблиці і де вона повинна бути створена? Оскільки ми вже натиснули на клітинку у списку (на наведеному вище етапі), для нас уже вибрано весь список навколо цієї комірки ($ A $ 1: $ G $ 88 на Платежі в цьому прикладі). Зауважимо, що ми можемо вибрати список у будь-якому іншому регіоні будь-якого іншого аркуша, або навіть якийсь зовнішній джерело даних, наприклад, таблицю бази даних Access, або навіть таблицю бази даних MS-SQL Server. Ми також повинні вибрати, чи хочемо, щоб наша нова зведена таблиця була створена на новий аркуша або на існуючих один. У цьому прикладі ми виберемо a новий один:

Для нас створено новий аркуш, а на цьому аркуші створюється порожня таблиця:

Також з'являється інше вікно: The Список полів зведеної таблиці. Цей список буде відображатись, коли ми натискаємо будь-яку клітинку у таблиці зведеної таблиці (вище):

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

The Значення коробка, можливо, найважливіша з чотирьох. Поле, яке перетягується в це вікно, являє собою дані, які необхідно узагальнити певним чином (підсумовуючи, усереднюючи, знаходимо максимум, мінімум і т.д.). Це майже завжди числовий даних. Ідеальним кандидатом для цього вікна в нашому прикладі даних є поле / колонка "Сума". Давайте перетягнемо це поле в Значення поле:

Зверніть увагу, що (a) поле "Сума" у списку полів тепер позначено, а "Сума Суми" додано до Значення , що вказує на те, що колонка суми була підсумована.

Якщо ми розглянемо сам зведену таблицю, ми дійсно знайдемо суму всіх значень "Сума" з таблиці вихідних даних:

Ми створили першу зведену таблицю! Зручний, але не особливо вражаючий. Ймовірно, що нам потрібно трохи більше зрозуміти наші дані, ніж це.

Посилаючись на наші зразкові дані, ми повинні визначити один або кілька заголовків стовпців, які ми могли б використовувати для поділу цієї суми. Наприклад, ми можемо вирішити, що ми хотіли б побачити резюме наших даних, де ми маємо a заголовок рядка для кожного з різних продавців в нашій компанії, і загальна для кожного. Щоб досягти цього, потрібно лише перетягнути поле "Продавець" у поле Рядок міток поле:

Тепер, нарешті, все починає цікаво! Наша зведена таблиця починає формуватися ... .

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

Так що ще ми можемо зробити? Ну, в одному сенсі наша PivotTable завершена. Ми створили корисний підсумок наших вихідних даних. Важливі речі вже вивчені! Для решти статті ми розглянемо деякі способи створення більш складних зведених таблиць, а також способи налаштування цих зведених таблиць.

По-перше, ми можемо створити a два-розмірні таблиці. Давайте зробимо це, використовуючи метод оплати як заголовок стовпця. Просто перетягніть заголовок "Спосіб оплати" до Мітки стовпців поле:

Це виглядає так:

Початок отримувати дуже прохолодно!

Давайте зробимо це три-розмірні таблиці. Як може виглядати такий стіл? Ну, подивимося ...

Перетягніть стовпець "Пакет" / заголовок до Фільтр звітів поле:

Зверніть увагу, де вона закінчується ... .

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

Тому, якщо ви думаєте про це правильно, наша зведена таблиця тепер є тривимірною. Продовжуємо налаштовувати ...

Якщо виявиться, скажімо, що ми тільки хочемо побачити чек і кредитну картку транзакцій (тобто без касових операцій), тоді ми можемо скасувати вибір пункту "Готівка" у заголовках стовпців. Натисніть стрілку розкривного меню поряд з Мітки стовпців, і скасуйте "Готівка":

Давайте подивимося, як це виглядає ... Як ви бачите, "готівка" пішла.

Форматування

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

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

По-перше, ми знаходимо запис "Сума Суми" в Значення і натисніть на неї. З'явиться меню. Ми вибираємо Параметри полів значень ... з меню:

The Параметри поля поля з'явиться вікно.

Натисніть кнопку Формат номерів і стандарт Вікно Формат клітинок з'являється:

Від Категорія виберіть, скажімо, Бухгалтерський облік, і відкинути число десяткових знаків до 0. Натисніть в порядку кілька разів, щоб повернутися до зведеної таблиці…

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

Поки ми маємо справу з форматуванням, давайте відформатуємо всю зведену таблицю. Є кілька способів зробити це. Давайте скористаємося простим ...

Натисніть кнопку Інструменти PivotTable / Дизайн вкладка:

Потім стрілка внизу праворуч Стилі зведеної таблиці список, щоб побачити велику колекцію вбудованих стилів:

Виберіть будь-який, який подає апеляцію, і перегляньте результат у вашій зведеної таблиці:

Інші параметри

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

По-перше, видалимо стовпець "Спосіб оплати" з Мітки стовпців (просто перетягніть його назад до списку полів) і замініть його на стовпець "Зареєстровано датами":

Як ви можете бачити, це робить нашу PivotTable миттєво марною, даючи нам один стовпець для кожної дати, коли відбулася транзакція - дуже широка таблиця!

Щоб виправити це, клацніть правою кнопкою миші на будь-яку дату та виберіть Групувати ... з контекстного меню:

З'явиться вікно групування. Ми вибираємо Місяці та натисніть "ОК":

Вуаля! A багато більш корисна таблиця:

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

Ще одна цікава річ, про яку потрібно знати, це те, що ви можете мати більше одного набору заголовків рядків (або заголовків стовпців):

… Який виглядає так ... .

Можна зробити подібне з заголовками стовпців (або навіть фільтрами звітів).

Знову прості речі, давайте подивимося, як сюжет усереднено значення, а не підсумовані значення.

По-перше, натисніть кнопку «Сума суми» і виберіть Параметри полів значень ... з контекстного меню, яке відображається:

В Підсумовуйте поле значення у списку Параметри поля поля , виберіть Середня:

Поки ми тут, давайте змінимо Власна назва, від “Середньої суми” до чогось більш лаконічного. Введіть щось на зразок "Сер.":

Натисніть в порядку, і подивитися, як він виглядає. Зауважте, що всі значення змінюються від підсумкових підсумків до середніх, а назва таблиці (верхня ліва комірка) змінилася на "Avg":

Якщо нам подобається, ми можемо навіть мати суми, усереднення та підрахунки (кількість = кількість продажів) на одній і тій же PivotTable!

Ось кроки, щоб отримати щось подібне на місці (починаючи з порожньої зведеної таблиці):

Перетягніть "Продавця" до розділу Мітки стовпців
Перетягніть поле "Сума" вниз у поле Значення три рази
Для першого поля "Сума" змініть його власне ім'я на "Всього" і його формат на номер Бухгалтерський облік (0 знаків після коми)
Для другого поля «Сума» змініть його назву на «Середнє», на його функцію Середня і це формат номера Бухгалтерський облік (0 знаків після коми)
Для третього поля "Сума" змініть його назву на "Граф" і його функцію на Рахувати
Перетягніть автоматично створену поле з Мітки стовпців до Рядок міток

Ось що ми маємо:

Загальна, середня і розраховується на ту ж PivotTable!

Висновок

Є багато, багато інших можливостей і опцій для зведених таблиць, створених Microsoft Excel - занадто багато, щоб перелічити в такій статті. Щоб повністю охопити потенціал зведених таблиць, потрібна буде невелика книга (або великий веб-сайт). Сміливі та / або прискіпливі читачі можуть досить легко досліджувати зведені таблиці: просто клацніть правою кнопкою миші на усьому, і перегляньте, які варіанти стануть доступними для вас. Також є дві вкладки стрічки: Інструменти / параметри зведеної таблиці і Дизайн. Неважливо, якщо ви помилитеся - легко видалити PivotTable і почати знову - можливість старих користувачів DOS Lotus 1-2-3 ніколи не мала.

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

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

Завантажте нашу практичну книгу Excel