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

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

Вимоги до вихідних даних.

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

Давайте розберемо в чому, власне, "правильність" цієї таблиці? Правильність полягає в тому, що:

• У кожному стовпці містяться лише однотипні дані, у стовпці Атільки Дати, в стовпці Утільки Документи, у стовпці Зтільки Клієнти, Гроші у Грошах, Постачальники у Постачальниках, Категорії Товарів у Категоріях тощо… У такій таблиці ми можемо дуже легко використовувати Фільтр;
• Стовпці з однотипними даними не повторюються;
• У таблиці немає жодних підсумкових рядків, лише "чисті дані";
• У таблиці немає порожніх осередків у текстових даних, у кожному рядку є назва і Клієнта, і Товару, і Постачальника, і Менеджера і так далі.

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

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

Ось ще один приклад "Неправильного масиву":

Тут цілий букет "неправильностей":

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

Але це все для окремої розмови, якщо ви хочете навчитися швидко, перетворювати такі "криві таблиці" на "правильні масиви" читайте статті: "Як швидко побудувати зведену таблицю зі звіту 1C або SAP? " і "Як швидко перетворити таблицю на масив для зведеної таблиці?"

Власне, побудова Зведеної таблиці:

Беремо "правильний масив", курсором встаємо в будь-яку комірку масиву, в головному меню вибираємо вкладку "Вставка", в лівому кутку, в розділі "Таблиці" натискаємо кнопку "Зведена таблиця":

У діалоговому вікні "Створення зведеної таблиці", що відкрилося, натискаємо "ОК":

MS Excel створить новий аркуш, на якому позначить місце вставки Зведеної таблиці, а праворуч виведе вікно налаштування полів Зведеної таблиці, в якому ви побачите всі назви стовпців свого масиву:

Починаємо налаштування Зведеної таблиці. Який розріз хочемо отримати? Товарні категорії у розрізі Менеджерів – будь ласка. Наводимо мишку на полі "Категорія", натискаємо на ньому лівою кнопкою мишки і тягнемо його в поле "РЯДКИ". Поле "Менеджер" тягнемо в "КОЛОНІ", "Суму" тягнемо в "ЗНАЧЕННЯ":

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

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

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

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

Цю табличку необхідно заповнити, тобто. підсумувати виручку за відповідними товарами та регіонами. Це неважко зробити з допомогою функції СУМЕСЛІМН. Також додамо підсумки. Вийде зведений звіт про продаж у розрізі область-продукція.

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

— Чи можна звіт зробити не з виручки, а з прибутку?

— Чи можна товари показати рядками, а регіони стовпцями?

— Чи можна робити такі таблиці для кожного менеджера окремо?

Навіть якщо ви досвідчений користувач Excel, на створення нових звітів знадобиться чимало часу. Це вже не говорячи про можливі помилки. Однак якщо ви володієте зведеними таблицями, то дайте відповідь: так, мені потрібно 5 хвилин, можливо менше.

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

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

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

Дуже часто нічого міняти тут не потрібно. Після натискання Ок буде створено новий аркуш Excel із порожнім макетом зведеної таблиці.

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

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

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

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

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

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

Область стовпців– аналогічно рядкам показує унікальні значення вибраного поля лише по стовпцях. Назви стовпців – це також зазвичай якісна ознака. Наприклад, роки та місяці, групи товарів.

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

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

Подивимося, як це працює у дії. Створимо поки що таку ж таблицю, як було створено з допомогою функції СУМЕСЛИМН. Для цього перетягнемо в область Значенняполе «Виручка», в область Рядкиперетягнемо поле «Область» (регіон продажу), в Стовпці- "Товар".

В результаті ми отримуємо справжню зведену таблицю.

На її побудову знадобилося буквально 5-10 секунд. Змінити існуючу зведену таблицю також легко. Подивимося, як побажання директора легко втілюються у реальність.

Замінимо виручку з прибутку.

Товари та області змінюються місцями також перетягуванням миші.

Для фільтрації зведених таблиць є кілька інструментів. В даному випадку просто помістимо поле "Менеджер" в область фільтрів.

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

Вихідні дані

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

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

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

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

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

Загалом вимог небагато, але їх слід знати.

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

або
через команду у вкладці Дані – Оновити всі.

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

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

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

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

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

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

Аналітики виділяють два основні фактори:

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

Зведені таблиці разом з недооцінкою успіху Windows загалом зіграли похоронний марш для Lotus 1-2-3 і започаткували успіх Microsoft Excel.

Що таке зведені таблиці?

Отже, як краще охарактеризувати, що являють собою зведені таблиці?

Говорячи простою мовою, зведені таблиці – це результати якихось даних, створені задля полегшення аналізу цих даних. На відміну від створених вручну підсумків зведені таблиці Excel інтерактивні. Після створення, Ви можете легко змінювати їх, якщо вони не дали картини, яку Ви сподівалися отримати. Лише парою клацань мишею підсумки можуть бути перевернуті таким чином, що заголовки стовпців стають заголовками рядків і навпаки. Зі зведеними таблицями можна зробити безліч різних дій. Замість того, щоб намагатися описати на словах усі можливості зведених таблиць, простіше продемонструвати це на практиці.

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

Подивіться на дані, показані на малюнку нижче:

Зверніть увагу, що це не сирі вихідні дані, оскільки для них вже підбито підсумки. У осередку B3 ми бачимо $30000, що, ймовірно, є сумарним результатом, який зробив James Cook у січні. Де тоді вихідні дані? Звідки взялася цифра $30000? Де вихідний перелік продажів, з якого цей результат за місяць був отриманий? Зрозуміло, що хтось зробив величезну працю з упорядкування та сортування всіх даних про продаж за останні шість місяців і перетворив їх на таблицю підсумків, яку ми бачимо. Скільки, на вашу думку, це зайняло часу? Година? Десята година?

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

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

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

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

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

Отже, запускаємо Excel… та завантажуємо такий список…

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

Виділіть будь-яку комірку з цього списку:

Потім на вкладці Insert(Вставка) виберіть команду PivotTable(Зведена таблиця):

З'явиться діалогове вікно Create PivotTable(Створення зведеної таблиці) з двома питаннями для Вас:

• Які дані використовувати для створення нової зведеної таблиці?
• Куди помістити зведену таблицю?

Так як на попередньому кроці ми вже вибрали одну з осередків списку, то для створення зведеної таблиці буде виділено весь список автоматично. Зауважте, що ми можемо вибрати інший діапазон, іншу таблицю і навіть якесь зовнішнє джерело даних, наприклад, таблицю бази даних Access або MS-SQL. До того ж, нам необхідно вибрати, де розмістити нову зведену таблицю: на новому аркуші або на одному з існуючих. У цьому прикладі ми виберемо варіант – New Worksheet(На новий аркуш):

Excel створить новий лист і розмістить на ньому порожню зведену таблицю:

Як тільки ми клацнемо по будь-якому осередку в зведеній таблиці, з'явиться ще одне діалогове вікно: PivotTable Field List(Поля зведеної таблиці).

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

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

Зверніть увагу, що заголовок Amountтепер відзначений галочкою, а в області Values(Значення) з'явився запис Sum of Amount(Сума по полю Amount), що вказує на те, що стовпець Amountпідсумовано.

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

Отже, наша перша зведена таблиця створена! Зручно, але не дуже вражає. Ймовірно, ми хочемо отримати більше інформації про наші дані, ніж зараз.

Звернемося до вихідних даних і спробуємо визначити один або кілька стовпців, які можна використовувати, щоб подрібнити цю суму. Наприклад, ми можемо сформувати нашу зведену таблицю таким чином, щоб підсумкова сума продажу підраховувалася для кожного продавця окремо. Тобто. до нашої зведеної таблиці додадуться рядки з ім'ям кожного продавця компанії та його підсумковою сумою продажів. Щоб досягти такого результату, достатньо перетягнути заголовок Salesperson Row Labels(Рядки):

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

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

Що ми ще можемо зробити? Ну, у певному сенсі наша зведена таблиця вже готова. Ми створили корисне зведення за вихідними даними. Вже отримано важливу інформацію! У частині статті, що залишилася, ми розберемо деякі способи створення більш складних зведених таблиць, а також дізнаємося, як їх налаштовувати.

Налаштування зведеної таблиці

По-перше, ми можемо створити двовимірну зведену таблицю. Зробимо це, використовуючи заголовок стовпця Payment Method(Спосіб оплати). Просто перетягніть заголовок Payment Methodв область Column Labels(Колони):

Отримаємо результат:

Виглядає дуже круто!

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

Перетягніть заголовок Package(Комплекс) в область Report Filter(Фільтри):

Зауважте, де він опинився...

Це дає нам можливість відфільтрувати звіт за ознакою “Який відпочинковий комплекс був оплачений”. Наприклад, ми можемо бачити розбивку за продавцями та за способами оплати для всіх комплексів або за пару клацань мишею змінити вигляд зведеної таблиці і показати таку ж розбивку тільки для тих, хто замовив комплекс Sunseekers.

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

Якщо раптом з'ясовується, що у зведеній таблиці повинні виводитись лише оплата чеком та кредитною карткою (тобто безготівковий розрахунок), то ми можемо відключити висновок заголовка Cash(готівкою). Для цього поряд з Column Labelsнатисніть стрілку вниз і в меню, що випадає, зніміть галочку з пункту Cash:

Погляньмо, на що тепер схожа наша зведена таблиця. Як бачите, стовпець Cashзник із неї.

Форматування зведених таблиць в Excel

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

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

По-перше, знайдемо запис Sum of Amountв області Values(Значення) і клацнемо по ній. У меню виберемо пункт Value Field Settings(Параметри полів значень):

З'явиться діалогове вікно Value Field Settings(Параметри поля значень).

Натисніть кнопку Number Format(Числовий формат), відкриється діалогове вікно Format Cells(Формат осередків):

З списку Category(Числові формати) виберіть Accounting(Фінансовий) та число десяткових знаків встановіть рівним нулю. Тепер кілька разів натисніть ОК, щоб повернутися назад до нашої зведеної таблиці.

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

Якщо ми зайнялися форматуванням, давайте налаштуємо формат для всієї зведеної таблиці. Є кілька способів зробити це. Використовуємо той, що простіше.

Відкрийте вкладку PivotTable Tools: Design(Робота зі зведеними таблицями: Конструктор):

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

Інші налаштування зведених таблиць в Excel

Іноді доводиться фільтрувати дані щодо дат. Наприклад, у нашому списку торгових операцій є багато дат. Excel надає інструмент для групування даних за днями, місяцями, роками і т.д. Погляньмо, як це робиться.

Для початку заберіть запис Payment Methodз області Column Labels(Колони). Для цього перетягніть його назад до списку заголовків, а на його місце перемістіть заголовок Date Booked(Дата бронювання):

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

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

З'явиться діалогове вікно угруповання. Ми вибираємо Months(Місяць) і тиснемо ОК:

Вуаль! Від такої таблиці набагато більше користі:

До речі, ця таблиця практично ідентична тій, що була показана на початку статті, де результати продажів були складені вручну.

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

… а виглядатиме це так…

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

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

Для початку клацніть на Sum of Amountі з меню виберіть Value Field Settings(Параметри полів значень):

В списку Summarize value field by(Операція) у діалоговому вікні Value Field Settings(Параметри поля значень) виберіть Average(Середнє):

Заодно, поки ми тут, давайте змінимо Custom Name(Ім'я користувача) з Average of Amount(Кількість по полю Amount) на щось коротше. Введіть у цьому полі щось на зразок Avg:

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

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

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

1. Перетягніть заголовок Salesperson(Торговий представник) в область Column Labels(Колони).
2. Тричі перетягніть заголовок Amount(Вартість) в область Values(Значення).
3. Для першого поля Amountзмініть назву на Total(Сума), а формат чисел у цьому полі на Accounting(Фінансовий). Кількість десяткових знаків дорівнює нулю.
4. Друге поле Amountназвіть Averag e, операцію для нього встановіть Average(Середнє) та формат чисел у цьому полі теж зміните на Accounting(Фінансовий) із числом десяткових знаків рівним нулю.
5. Для третього поля Amountвстановіть назву Countта операцію для нього – Count(Кількість)
6. В області Column Labels(Колони) автоматично створено поле Σ Values(Σ Значення) – перетягніть його в область Row Labels(Рядки)

Ось що ми отримаємо в результаті:

Загальна сума, середнє значення та кількість продажів – все в одній зведеній таблиці!

Висновок

Зведені таблиці Microsoft Excel містять дуже багато функцій і налаштувань. У такій невеликій статті їх не охопити навіть близько. Щоб повністю описати всі можливості зведених таблиць, знадобилася б невелика книга або великий веб-сайт. Сміливі та допитливі читачі можуть продовжити дослідження зведених таблиць. Для цього достатньо клацати правою кнопкою миші практично на будь-якому елементі зведеної таблиці та дивитися, які відкриваються функції та налаштування. На Стрічці Ви знайдете дві вкладки: PivotTable Tools: Options(Аналіз) та Design(Конструктор). Не бійтеся припуститися помилки, завжди можна видалити зведену таблицю і почати все заново. Ви маєте можливість, якої ніколи не було у давніх користувачів DOS і Lotus 1-2-3.

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

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

Тепер про вимоги, яких необхідно дотримуватись при її створенні в Екселі. Вихідні дані мають бути представлені у вигляді таблиці, у якої має бути назва для стовпців, тобто шапка. Для цих цілей відмінно підійде створення розумної таблиці в Excel. Також вона не повинна містити порожні рядки, стовпці та комірки. Не повинно бути прихованих рядків/стовпців та об'єднаних осередків.

Як створити

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

Щоб зробити зведену таблицю, виділяємо будь-яку комірку з вихідної, потім переходимо на вкладку «Вставка» та натискаємо на кнопочку "Зведена таблиця".

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

У відкритій книзі Excel створиться новий аркуш, на якому буде розміщена, поки що порожня зведена таблиця.

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

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

Як розставити поля

Тепер давайте визначимося, за яким принципом аналізуватимемо дані. Наприклад, потрібно дізнатися, який саме продавець, продав якийсь товар у кожному місяці і на яку суму.

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

Як рядки виберемо «Товари» . Аналогічно перетягуємо потрібне поле в область «Назви рядків».

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

Якби у нас у вихідній таблиці був стовпець «Одиниці». Те дана зведена таблиця показувала б, який продавець, у якому місяці, скільки продав одиниць товару за певною ціною.

В область «Назви стовпців»перетягнемо поле «Дата». Щоб відобразити продаж не за кожен день, а, наприклад, по місяцях, клацніть правою кнопкою миші за будь-якою датою і виберіть з меню «Групувати» .

Таблиця набуде наступного вигляду.

Тепер у область «Значення» перетягнемо поле «Сума».

Як бачите, відобразилися просто числа, хоча у вихідному діапазоні був заданий числовий формат осередків для даного стовпця, він може бути грошовий і фінансовий. У стовпці «Дата» формат осередків був також відповідний – дата.

Щоб це виправити, у зведеній виділяємо потрібний діапазон осередків та натискаємо праву кнопку миші. далі виберіть із меню «Числовий формат».

У наступному вікні вибираємо «Числовою», можете поставити галочку в полі «Розділювач груп розрядів»та натисніть «OK» .

Як працювати з даними

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

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

В області «Значення» можна настроїти параметри поля. У прикладі виводиться сума значень: Рома у лютому продав сорочок у сумі 1 800.00. Давайте подивимося, скільки це штук. Клацаємо лівою кнопкою мишки по рядку "Сума по полю ..."та вибираємо з меню "Параметри полів значень".

У наступному вікні зі списку вибираємо «Кількість», виберіть те, що підходить саме у вашому випадку, і натискаємо «ОК».

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

Тепер зробимо, щоб загальний фільтр таблиці був по місяцях. Змінюємо області: у «Фільтр звіту» перетягуємо поле «Дата» , «Назви стовпців»- «Продавець».

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

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

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

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

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

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

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

Як застосовувати просунуті прийоми зведених таблиць в Excel (коротке відео)

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

5 просунутих прийомів зведених таблиць в Excel

Продовжуйте читати для ознайомлення про те, як використовувати кожну з цих п'яти функцій у наведеному нижче уроці, включаючи: Зрізи, Тимчасову шкалу, Табличний вигляд, Обчислювані поля та Рекомендовані Зведені таблиці. Давайте приступимо.

1. Зрізи

Зрізи- інструмент вказівки та кліка, щоб уточнити дані, включені до зведеної таблиці Excel. Вставте зріз і ви зможете легко змінювати дані, включені до зведеної таблиці.

У цьому прикладі я вставив зріз для типу Item. Після того, як я натискаю на Backpack, зведена таблиця показує лише цей параметр у таблиці.

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

Щоб додати зріз, клацніть у зведеній таблиці та знайдіть вкладку Аналізна стрічці Excel.

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

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

2. Тимчасова шкала

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

Перейдіть до розділу Аналіз > Вставити часову шкалу, щоб додати тимчасову шкалу, спеціальний тип зрізу, який управляє даними, включеними до розширеної зведеної таблиці Excel, на основі дати.

Порада: якщо ця функція не працює, переконайтеся, що у вихідних даних є дата, відформатована в електронній таблиці, як дата.

Щоб додати тимчасову шкалу, переконайтеся, що ви вибрали зведену таблицю (клацніть всередині неї), а потім натисніть кнопку Аналіз > Вставити часову шкалуна стрічці Excel. У спливаючому вікні позначте стовпець дати (або кілька стовпців) і натисніть OKстворити тимчасову шкалу.

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

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

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

3. Табличний вигляд

Стандартний вид зведених таблиць Excel виглядає як водоспад; при перетягуванні більшої кількості полів у рядки, Excel створює більше шарів у даних.

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

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

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

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

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

4. Обчислювані поля

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

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

Щоб розпочати роботу з обчисленими полями, почніть клацнувши всередині зведеної таблиці, а потім знайдіть на стрічці Аналіз. Натисніть меню Поля, елементи та набори, а потім виберіть Обчислюване поле.

Використовуйте Аналіз > Поля, елементи та набори > Обчислюване поле, щоб вставити обчислене поле у ​​вашу зведену таблицю.

У новому спливаючому вікні почніть з визначення обчисленого поля імені. у моєму випадку я назву його Total Order. Загальна вартість замовлення – це кількість, помножена на ціну кожної одиниці. Потім я двічі клацнув по першому полю назви (quantity) у списку полів у цьому вікні.

Щоб вирахувати загальну вартість замовлення, я помножив існуюче поле ціни за одиницю ( Unit Price) на полі кількості ( Quantity).

Після додавання назви цього поля, я додам знак множення * , а потім двічі натискаю за загальною кількістю (quantity). Давайте продовжимо та натисніть ОК.

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

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

Я помножив загальну вартість замовлення на 1.05 для розрахунку вартості, включаючи податок із продажу; Ви можете використовувати числові значення поряд з існуючими полями.

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

Перейдіть до Вставка >, щоб спробувати цю функцію.

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

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

Функція рекомендації зведеної таблиці пропонує безліч варіантів для аналізу даних в один клік миші.

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

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

Повторюйте та продовжуйте навчання (з ще більшими уроками з Excel)

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

Ці уроки вам просунути ваші навички з Excel і зведені таблиці на наступний рівень. Перевір їх:

• ExcelZoo має великий огляд прийомів зведеної таблиці у статті, 10 уроків для освоєння зведених таблиць (англійською).
• Ми в Envato Tuts+ розглянули зведені таблиці за допомогою уроку для новачків.
• Для більш простого введення в Microsoft Excel ознайомтеся з нашою навчальною серією.

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