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

Трохи теорії

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

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

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

Плюси використання такого виду угруповання даних:

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

Навчання роботі зі зведеними таблицями в Excel не займе багато часу і може ґрунтуватися на відео.

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

Ознайомившись з базовими теоретичними нюансами про зведені таблиці в Excel, перейдемо до застосування їх на ділі. Для старту створення зведеної таблиців Excel 2016, 2010 чи 2007 необхідно встановити програмне забезпечення. Як правило, якщо ви користуєтесь програмами системи Microsoft Office, то Excel вже є на вашому комп'ютері.

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

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


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


Розглянемо детальніше самостійне заповнення пунктів діалогового вікна.

Перший рядок не залишаємо порожнім, інакше програма видасть помилку. Якщо є джерело, з якого плануєте переноситися дані, виберіть його в пункті «Використовувати зовнішнє джерело даних». Під зовнішнім джереломмається на увазі інша книга Excelабо набір моделей даних із СУБД.

Заздалегідь озаглавте кожен стовпчик

Виберіть місце, де буде розташована майбутня рамка з осередками. Це може бути нове вікно або цей лист, рекомендуємо використовувати інший лист.


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


Тепер потрібно розібратися, як будується вся ця конструкція. У вікні параметрів «Поля вільної таблиці» ви позначаєте дані, які будуть присутні.


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


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


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


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

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


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


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


У параметрах полів значень ви знайдете безліч варіантів аналізу.


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


Тепер бачимо, що у відділ «Аксесуари» надійшло товарів у сумі 267660 рублів, у своїй найдорожчий має ціну 2700 рублів.
Область "Фільтри" дозволяє встановити критерій відбору записів. Додамо поле «Дата вступу», просто поставивши біля нього галочку.


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


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


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


Також можна вибрати значення для відділу.


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

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

Оновлення даних у зведеній таблиці в Excel

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


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


Якщо зведена таблиця в Excel не потрібна, варто розібратися, як її видалити. Це не складе великої праці. Виділіть всі складові вручну, або використовуючи клавіші CTRL+A. Далі натисніть клавішу DELETE і поле буде очищене.

Як до зведеної таблиці Excel додати стовпець або таблицю

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


Перейдіть на вкладку «Аналіз» та відкрийте джерело даних.


Excel все запропонує.


Оновіть і ви отримаєте новий список полів в області налаштування.

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

Як зробити зведену таблицю в Excel з кількох аркушів

Для цього нам знадобиться майстер зведених таблиць. Додамо його на панель швидкого доступу(самий верх вікна – ліворуч). Натисніть стрілку і виберіть «Інші команди».


Виберіть усі команди.


І знайдіть майстер зведених таблиць Excel, клацніть по ньому, потім на «Додати» та ОК.


Піктограма з'явиться зверху.


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


Нам потрібне кілька полів, а не одне.


На наступному етапі виділіть перший діапазон та натисніть кнопку «Додати». Потім перейдіть на інший аркуш (клацніть по його назві внизу) і знову «Додати». У вас буде створено два діапазони.

Не варто виділяти всю таблицю повністю. Нам потрібна інформація про надходження у відділи, тому ми виділили діапазон, починаючи зі стовпця «Відділ».
Дайте ім'я кожному. Клікайте кружечок 1, потім у поле вписуйте «травень», клацніть кружечок 2 і вписуйте в поле 2 «червень». Не забувайте змінювати діапазони в області. Має бути виділено той, який називаємо.

Клацніть «Далі» та створюйте на новому аркуші.


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


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


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

Як бачите, у нас одне значення у відповідній галузі.

Зміна структури звіту

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

Вам відкриються на вибір три типи для структуризації інформації:

• Стиснута форма

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

• Структурована форма

Усі показники подаються ієрархічно: від малого до великого.

• Таблична форма

Інформація подається під виглядом реєстру. Це дозволяє легко переносити комірки на нові листи.

Зупинивши вибір на відповідному макеті, ви закріплюєте внесені корективи.

Отже, ми розповіли, як скласти поля зведеної таблиці MS Excel 2016 (2007, 2010 дійте за аналогією). Сподіваємось, ця інформація допоможе вам здійснювати швидкий аналіз консолідованих даних.

Чудового Вам дня!

У цій нотатці представлена ​​колекція простих та витончених інструментів роботи зі зведеними таблицями в Excel. Те, що англійською називається tips & tricks. Виділіть час і ознайомтеся з наведеними тут порадами. Хто знає, можливо, ви нарешті знайдете відповідь на питання, що довго мучило вас?

Порада 1. Автоматичне оновлення зведених таблиць

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

1. Клацніть правою кнопкою миші на зведеній таблиці та в контекстному менювиберіть пункт Параметри зведеної таблиці.
2. У діалоговому вікні, що з'явилося. Параметри зведеної таблицівиберіть вкладку Дані.
3. Встановіть прапорець Оновити під час відкриття файлу.

Рис. 1. Увімкніть опцію Оновити під час відкриття файлу

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

Завантажити нотатку у форматі або приклади у форматі (файл містить код VBA).

Порада 2. Одночасне оновлення всіх зведених таблиць книги

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

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

Спосіб 3. Скористайтеся кодом VBA для оновлення всіх зведених таблиць у робочій книзі на вимогу. Цей підхід передбачає використання методу RefreshAll об'єкта Workbook. Для використання цієї методики створіть новий модуль та введіть наступний код:

Sub Refresh_All()

ThisWorkbook.RefreshAll

Порада 3. Сортування елементів даних у довільному порядку

На рис. 2 показаний заданий за умовчанням порядок відображення регіонів у зведеній таблиці. Регіони відсортовані за абеткою: Захід, Північ, Середній Захід та Південь. Якщо ваші корпоративні правила вимагають, щоб спочатку відображався регіон Захід, а потім регіони Середній Захід, Північ і Південь, виконайте ручне сортування. Просто введіть Середній Захід у комірку С4 та натисніть клавішу Enter. Порядок сортування регіонів зміниться.

Порада 4. Перетворення зведеної таблиці на жорстко задані значення

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

Для перетворення частини зведеної таблиці виконайте такі дії:

1. Виділіть копіювані дані зведеної таблиці, клацніть правою кнопкою миші і в контекстному меню виберіть пункт Копіювати(або наберіть на клавіатурі Ctrl+C).
2. Клацніть правою кнопкою миші у довільному місці робочого аркуша та в контекстному меню виберіть команду Вставити(або наберіть Ctrl+V).

Якщо потрібно перетворити всю зведену таблицю, виконайте такі дії:

1. Виділіть всю зведену таблицю, клацніть правою кнопкою миші та в контекстному меню виберіть пункт Копіювати. Якщо зведена не містить область ФІЛЬТРИ, для виділення області зведеної таблиці можна скористатися клавіатурним скороченням Ctrl+Shift+*.
2. Клацніть правою кнопкою миші у довільному місці аркуша та в контекстному меню виберіть параметр Спеціальна вставка .
3. Виберіть параметр Значенняі клацніть ОК.

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

Порада 5. Заповнення порожніх осередків у полях РЯДКИ

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

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

Зверніть увагу на те, що поля Регіоні Ринок збутузберігає ту ж структуру рядків, яка притаманна при знаходженні цих даних в області РЯДКИ зведеної таблиці. В Excel 2013 існує швидкий спосібзаповнення осередків в області РЯДКИ значеннями. Клацніть в області зведеної таблиці, після чого пройдіть по меню Конструктор -> Макет звіту -> (Рис. 4). Після цього можна перетворити зведену таблицю на значення, у результаті ви отримаєте таблицю даних без пробілів.

Рис. 4. Після застосування команди Повторювати всі підписи елементівзаповнюються всі порожні осередки

Порада 6. Ранжування числових полів зведеної таблиці

У процесі сортування та ранжування полів, що містять велику кількість елементів даних, не завжди легко визначити числовий ранг аналізованого елемента даних. Більше того, якщо зведена таблиця буде перетворена на значення, призначений кожному елементу даних числовий ранг, відображений у цілому полі, значно полегшить аналіз створеного набору даних. Відкрийте зведену таблицю, подібну до показаної на рис. 5. Зверніть увагу на те, що один і той самий показник - Сума по полю Обсяг продажів- Відображається двічі. Клацніть правою кнопкою миші на другому примірнику показника та в контекстному меню виберіть команду Додаткові обчислення -> Сортування від максимального до мінімального(Рис. 6.)

Після створення рангу можна налаштувати підписи полів та форматування (рис. 14.9). В результаті буде отримано гарний ранжований звіт.

Порада 7. Зменшення розміру звіту зведеної таблиці

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

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

Порада 8. Створення діапазону даних, що автоматично розгортається.

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

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

Для реалізації описаної методики виділіть вихідні дані, а потім клацніть на значку таблиці, що знаходиться на вкладці Вставка(Мал. 8) або натисніть Ctrl+T (Т англійське). Клацніть ОКу вікні. Хоча діапазон вихідних даних у зведеній таблиці перевизначати не потрібно, але при додаванні вихідних даних у діапазон у зведеній таблиці все одно доведеться клацнути на кнопці Оновити.

Порада 9. Порівняння звичайних таблиць за допомогою зведеної таблиці

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

У процесі порівняння створюється одна таблиця, основі якої створюється зведена таблиця. Переконайтеся, що у вас є спосіб позначити дані, що стосуються цих таблиць. У прикладі для цього використовується стовпець Фіскальний рік(Рис. 10). Після об'єднання двох таблиць скористайтесь отриманим комбінованим набором даних для створення нової зведеної таблиці. Відформатуйте зведену таблицю таким чином, щоб як тег таблиці (ідентифікатор, що вказує на походження таблиці) використовувалася область стовпців зведеної таблиці. Як показано на рис. 11 роки знаходяться в області стовпців, а відомості про замовників - в області рядків. У сфері даних містяться обсяги продажу кожному за замовника.

Порада 10. Автоматична фільтрація зведеної таблиці

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

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

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

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

Крок 1. Об'єднання всіх полів, які не належать до області стовпців, в один стовпець.Для створення зведених таблиць із декількома консолідованими діапазонами слід створити єдиний стовпець розмірності. У прикладі все, що не відноситься до поля місяця, розглядається як розмірність. Тому поля Ринок збутуі Опис послугислід об'єднати в один стовпець. Для об'єднання полів в один стовпець просто введіть формулу, яка виконує конкатенацію цих двох полів, використовуючи крапку з комою як роздільник. Надайте новому стовпцю ім'я. Введена формула відображається у рядку формул (рис. 14).

Рис. 14. Результат конкатенації стовпців Ринок збутуі Опис послуги

Після створення конкатенованого стовпця перетворіть формули на значення. Для цього виділіть щойно створений стовпець, натисніть Ctrl+C, після чого виконайте команду Вставити -> Спеціальна вставка -> Значення. Тепер можна видалити стовпці Ринок збутуі Опис послуги(Рис. 15).

Рис. 15. Видалені стовпці Ринок збутуі Опис послуги

Крок 2. Створення зведеної таблиці із кількома діапазонами консолідації.Тепер потрібно викликати знайомий багатьом користувачам попереднім версіям Excel майстер зведених таблиць та діаграм. Щоб викликати цього майстра, натисніть клавіші Alt+D+P. На жаль, ця комбінація клавіш призначена для англомовної версії Excel 2013. У російськомовній версії відповідає комбінація клавіш Alt+Д+Н. Але вона з невідомих причин не працює. Тим не менш, можна вивести старий добрий майстер зведених таблиць на панель швидкого доступу, див. Після запуску майстра встановіть перемикач У кількох діапазонах консолідації. Клацніть Далі. Встановіть перемикач Створити поля сторінкиі клацніть Далі. Визначте робочий діапазон і клацніть Готово(Докладніше див.). Ви створите зведену таблицю (рис. 16).

Крок 3. Двічі клацніть на перетині рядка та стовпця у рядку загальних підсумків.На цьому етапі у вашому розпорядженні виявиться зведена таблиця (рис. 16), що включає кілька діапазонів консолідації, яка є практично марною. Виберіть комірку, що знаходиться на перетині рядка та стовпця загальних підсумків, і двічі клацніть на ній (у нашому прикладі це комірка N88). Ви отримаєте новий аркуш, структура якого нагадує структуру, показану на рис. 17. Фактично цей лист є транспонованою версією вихідних даних.

Крок 4. Розбиття стовпця Рядок на окремі поля.Залишилось розбити стовпець Рядокна окремі поля (повернутись до початкової структури). Додайте один порожній стовпець відразу після стовпця Рядок. Виділіть стовпець А, а потім перейдіть на вкладку стрічки Даніта клацніть на кнопці Текст по стовпцях. На екрані з'явиться діалогове вікно Майстер розподілу текстів по стовпцях. На першому кроці виберіть перемикач З роздільникамиі натисніть кнопку Далі. У наступному кроці виберіть перемикач крапка з комоюі клацніть Готово. Відформатуйте текст, додайте заголовок і перетворите вихідні дані на таблицю шляхом натискання Ctrl+T (рис. 18).

Рис. 18. Цей набір даних ідеально підходить для створення зведеної таблиці (порівняйте з рис. 13)

Порада 12. Включення двох числових форматів до зведеної таблиці

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

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

Для вирішення цієї проблеми застосовується числовий формат, який будь-яке значення, більше 1,5, форматує як число. Якщо значення менше 1,5, воно форматується як відсоток. У діалоговому вікні Формат осередківвиберіть вкладку (Всі формати)і в полі Типвведіть наступний рядок, що форматує (рис. 21): [>=1,5]$# ##0; [<1,5]0,0%

Рис. 21. Застосуйте числовий формат, в якому будь-які числа, менші 1,5, форматуються як відсотки

Отриманий результат показано на рис. 22. Як бачите, тепер кожен показник відформатовано коректно. Звичайно, наведений у цій раді рецепт не універсальний. Скоріше, він вказує напрямок, у якому варто експериментувати.

Порада 13. Створення частотного розподілу для зведеної таблиці

Якщо ви коли-небудь створювали частотні розподіли за допомогою Excel Частота, то, напевно, знаєте, що це дуже непросте завдання. Більше того, після зміни діапазонів даних все доводиться починати спочатку. У цьому розділі ви навчитеся створювати прості частотні розподіли за допомогою звичайної зведеної таблиці. Спочатку створіть зведену таблицю, в області рядків якої є дані. Зверніть увагу на рис. 23, де в області рядків знаходиться поле Об `єм продажів.

Клацніть правою кнопкою миші будь-яке значення в області рядків і в контекстному меню виберіть параметр Групувати. У діалоговому вікні Групування(рис. 24) визначте значення параметрів, що визначають початок, кінець та крок частотного розподілу. Натисніть кнопку ОК.

Рис. 24. У діалоговому вікні Групуваннянастройте параметри частотного розподілу

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

Рис. 25. Тепер у вашому розпорядженні опинився розподіл транзакцій замовників відповідно до розмірів замовлень (у доларах)

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

Порада 14. Використання зведеної таблиці для розподілу набору даних за аркушами книги

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

Рис. 28. Клацніть на кнопці Відобразити сторінки фільтру звіту

У діалоговому вікні, що з'явилося (рис. 29) можна вибрати поле фільтра, для якого будуть створені окремі зведені таблиці. Виберіть відповідне поле фільтра та клацніть ОК.

Рис. 29. Діалогове вікно Відображення сторінок фільтра звіту

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

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

У раді 14 ми скористалися спеціальною опцією для поділу зведених таблицьпо ринках збуту різних листах робочої книжки. Якщо вам потрібно розділити початкові даніз різних ринків збуту в окремих книгах, можна скористатися невеликим кодом VBA. Для початку помістіть поле, на основі якого буде фільтруватися, в область полів фільтра. Помістіть поле Об'єм продажуу область значень (рис. 31). Пропонований код VBA по черзі вибирає кожен елемент ФІЛЬТРА та викликає функцію Показати деталістворення нового листа з даними. Потім цей лист зберігається у новій робочій книзі

КодVBA.

Sub ExplodeTable()

Dim PvtItem As PivotItem

Dim PvtTable As PivotTable

Dim strfield As PivotField

'Зміна змінних відповідно до сценарію

ConststrFieldName = "Ринок збуту" ‘<—Изменение имени поля

Const strTriggerRange = "A4" ‘<—Изменение диапазона триггера

'Зміна назви зведеної таблиці (при необхідності)

SetPvtTable = ActiveSheet.PivotTables("PivotTable1") ‘<—Изменение названия сводной

'Циклічний обхід кожного елемента виділеного поля

For Each PvtItem In PvtTable.PivotFields(strFieldName).PivotItems

PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name

Range(strTriggerRange).ShowDetail = True

'Привласнення імені тимчасовому листу

ActiveSheet.Name = "TempSheet"

'Копіювання даних у нову книгу та видалення тимчасового листа

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _

Filename:=ThisWorkbook.Path & " \ " & PvtItem.Name & " .xlsx "

ActiveWorkbook.Close

Sheets("Tempsheet").Delete

Application.DisplayAlerts = True

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

• Const strFieldName. Ім'я поля, яке використовується для поділу даних. Іншими словами, це поле, яке міститься в область фільтра/сторінок зведеної таблиці.
• Const strTriggerRange. Осередок тригера, в якому зберігається однина з області даних зведеної таблиці. У нашому випадку осередком тригера є А4 (див. рис. 31).

В результаті виконання коду VBA дані для кожного ринку збуту будуть збережені в окремій книзі.

Нотатка написана на основі книги Джелен, Александер. . Розділ 14.

Дата: 16 березня 2017 Категорія:

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

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

Зовсім недавно один із читачів звернувся до мене із запитанням: «А як скопіювати зведену таблицю з одного аркуша на інший»? Вирішив висвітлити це питання тут, бо чую його не вперше. Зведена таблиця – це простий діапазон даних, це область осередків з результатами роботи інструмента. Тому просто скопіювати зведену таблицю в інше місце не можна.

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

1. даних у зведеній таблиці
2. одним із відомих способів. Наприклад, натисніть Ctrl+C на клавіатурі
3. Встановіть курсор в комірку, де повинен розташовуватися верхній лівий кут діапазону, що вставляється.
4. Виконайте на стрічці Головна – Буфер обміну – Вставити – Значення.

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

Як отримати детальні дані зі зведеної таблиці

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

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

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

А можна просто зробити подвійний клік по потрібній величині. Це альтернативний, і простіший спосіб.

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

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

Якщо стандартних функцій обчислення у зведеній таблиці (сума, кількість, відхилення тощо) Вам виявилося замало, інструмент має додаткові обчислювальні можливості. Перш за все, натисніть правою кнопкою миші в будь-якій комірці стовпця з обчисленнями та виберіть «Додаткові обчислення». У списку обчислень буде багато цікавих варіантів. Наприклад. Ми хочемо знати, яку частку продажів від кількості виконує кожен менеджер з прикладу. Виконаємо додаткові обчислення у стовпці, де підсумовуються продажі. Параметром виберемо "% від суми по стовпцю". Подивіться, в 2 кліки ми перейшли від натуральних величин до відсотків, тепер легше порівнювати показники, або приховати їх, коли не рекомендується відображати у звіті конкретні цифри у грошових одиницях.

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

Обчислювані поля та об'єкти

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

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

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

Виконуємо таку послідовність дій:

1. Встановимо курсор в одну з осередків, що містять значення
2. На стрічці натискаємо: Робота зі зведеними таблицями – Аналіз – Поля, елементи, набори – Обчислюване поле
3. У вікні, що відкрилося, в полі «Ім'я» запишемо «Середній чек»
4. Тепер запроваджуємо формулу, нам потрібно поділити суму продажу на кількість чеків. У списку полів двічі клікнемо на «Сума продажів», пишемо на клавіатурі знак поділу «/» і двічі клацаємо на «Кількість чеків. Повинна вийти така формула:

1. Тиснемо Ок і дивимося, що вийшло.

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

Об'єкти, що обчислюються

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

1. Ставимо курсор у будь-який рядок першого стовпця або будь-який стовпець першого рядка
2. Тиснемо на стрічці: Робота зі зведеними таблицями – Аналіз – Поля, елементи, набори – Обчислюваний об'єкт. Відкриється вікно вставки:

1. У полі «Ім'я» запишемо «ПДВ», у списку «Поля» вибираємо «Менеджер»
2. Клацаючи двічі на ім'я кожного менеджера, запишемо формулу: = (Олексій + Ганна + Віктор + Вікторія + Віталій + Денис + Єгор + Роман + Світлана) * 0,18
3. Видалимо непотрібні поля, тиснемо Ок. Отримуємо ще одне поле, в якому буде пораховано ПДВ. Значення цього поля буде додано до загальної суми.

Угруповання даних у зведеній таблиці

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

Угруповання з кроком

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

1. Створюємо зведену таблицю, де у рядках будуть дні, а значеннях – продажу. Якщо Ви не знаєте, як створити зведену таблицю, спочатку прочитайте ;
2. Клацніть правою кнопкою миші будь-яку з дат у зведеній таблиці і в контекстному меню виберіть «Групувати». Відкриється вікно налаштування угруповання;

1. У полях «Починаючи з» та «по» автоматично встановляться мінімальна та максимальна дати у списку. Можете, за необхідності, вказати тут вужчий період для угруповання
2. У списку З кроком виберіть еталонний інтервал часу. У нас це «Місяць». Можна вибрати кілька пунктів у цьому списку. Спробуємо побудувати по кварталах і місяцях, відзначаємо їх;
3. Тиснемо «Ок» і відразу ж отримуємо результат. Погляньте, що вийшло:

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

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

1. Клікнемо правою кнопкою в будь-якому рядку першого стовпця (суми продажів) і виберемо «Групувати». У вікні задаємо мінімальне і максимальне числа для угруповання, а також крок. У нас це 1000. Замість величезної таблиці отримуємо компактну з десяти рядків. У кожному рядку – інтервал сум та кількість чеків у цьому інтервалі.

1. Клацніть правою кнопкою по будь-якому осередку в стовпці «Кількість чеків» і виберемо Сортування – за спаданням;
2. Можна для наочності висловити результат у відсотках. Клікнемо по тому ж осередку і виберемо Додаткові обчислення - % від суми по стовпцю. Ось і все, завдання вирішене, нам чудово видно в яких інтервалах сум було найбільше чеків.

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

Також можна групувати записи вручну. Для цього виділіть потрібні дані та натисніть Робота зі зведеними таблицями – Аналіз – Групувати – Угруповання по виділеному.

До речі, щоб скасувати угруповання – клацніть правою кнопкою миші по групованому стовпцю та виберіть «Розгрупувати».

Фільтрування зведених таблиць за допомогою зрізів

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

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

Ви бачите кілька вікон на робочому аркуші з переліченими в них даними, а також зведену таблицю, що містить повний набір даних. Але що якщо комусь потрібно подивитися продаж металовиробів у Романа 1 квітня 2016 року? Клацаємо у вікнах на кнопки:

1. У вікні «Дата» шукаємо та обираємо 01.04.2016;
2. У вікні "Менеджер" вибираємо "Роман";
3. У вікні «Група товару» клацаємо «Метизи»

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

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

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

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

Зіткнувся нещодавно із проблемою:

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

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

Зведені_

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

Після створення зведеної таблиці та налаштування її структури виділіть всю таблицю та скопіюйте її в буфер обміну. Далі перейдіть на вкладку Головна (Ноті) і клацніть на кнопці Вставка (Insert), а потім виберіть у розкривному меню команду Вставити значення (Insert Values), як показано на рис. 6.19.

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

Рис 6.19. Команда Вставити значення застосовується для отримання на основі зведеної таблиці звичайної таблиці зі статичними значеннями

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

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

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

Створення зведеної Ексель таблиці вимагає дотримання певних умов:

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

Як зробити зведену таблицю в excel: покрокова інструкція

Для створення зведеної таблиці необхідно:

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

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


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

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


Категорію "Товари" ми поставимо у вигляді рядків. У «Назви рядків» ми переносимо потрібне нам поле.


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

Стовпець «Одиниці», будучи в головній таблиці, відображав кількість товару, проданого певним продавцем за конкретною ціною.


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


Вказуємо періоди дати та крок. Підтверджуємо вибір.

Бачимо таку таблицю.


Зробимо перенесення поля "Сума" до області "Значення".


Стало видно відображення чисел, а нам потрібний саме числовий формат


Для виправлення, виділимо осередки, викликавши вікно мишкою, оберемо «Числовий формат».

Числовий формат ми вибираємо для наступного вікна та відзначаємо «Розділювач груп розрядів». Підтверджуємо кнопкою "ОК".

Оформлення зведеної таблиці

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


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


Окремо налаштовуються параметри поля. На прикладі ми бачимо, що певний продавець Рома конкретного місяця продав сорочок на конкретну суму. Натисканням мишки ми у рядку «Сума по полю…» викликаємо меню та вибираємо «Параметри полів значень».


Далі для зведення даних у полі вибираємо «Кількість». Підтверджуємо вибір.

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


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

Виділення осередків у зведеній таблиці призведе до появи такої вкладки як «Робота зі зведеними таблицями», а ній ще дві вкладки «Параметри» і «Конструктор».

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

Сподіваюся, ви засвоїли весь матеріал і тепер знаєте, як зробити зведену таблицю в excel.

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

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

Зведена таблиця в Excel

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

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

Найраціональніше рішення - це створення зведеної таблиці в Excel:

1. Виділяємо комірку А1, щоб Excel знав, з якою інформацією доведеться працювати.
2. У меню "Вставка" вибираємо "Зведена таблиця".
3. Відкриється меню "Створення зведеної таблиці", де вибираємо діапазон та вказуємо місце. Так як ми встановили курсор у комірку з даними, поле діапазону заповниться автоматично. Якщо курсор стоїть у порожньому осередку, необхідно прописати діапазон вручну. Зведену таблицю можна зробити цьому ж аркуші чи іншому. Якщо ми хочемо, щоб зведені дані були на існуючій сторінці, не забувайте вказувати місце для них. На сторінці з'являється така форма:
4. Сформуємо табличку, яка покаже суму продажу відділам. У списку полів зведеної таблиці вибираємо назви стовпців, які цікавлять нас. Отримуємо підсумки з кожного відділу.

Просто, швидко та якісно.

Важливі нюанси:

• Перший рядок заданого для зведення даних діапазону має бути заповнений.
• У базовій табличці кожен стовпець повинен мати свій заголовок - простіше налаштувати звіт.
• В Excel як джерело інформації можна використовувати таблиці Access, SQL Server та ін.

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

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

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

Створимо звіт за допомогою майстра зведених таблиць:

1. Викликаємо меню «Майстер зведених таблиць та діаграм». Для цього клацаємо кнопку налаштування панелі швидкого доступу та натискаємо «Інші команди». Тут на вкладці "Налаштування" знаходимо "Майстер зведених таблиць". Додаємо інструмент до панелі швидкого доступу. Після додавання:
2. Ставимо курсор на першу табличку та натискаємо інструмент «Майстра». У вікні відзначаємо, що створити таблицю хочемо в «кілька діапазонів консолідації». Тобто, нам потрібно об'єднати кілька місць з інформацією. Вигляд звіту - "зведена таблиця". "Далі".
3. Наступний етап - "створити поля". "Далі".
4. Прописуємо діапазон даних, за якими формуватимемо зведений звіт. Виділяємо перший діапазон разом із шапкою – «додати». Другий діапазон разом із назвою стовпців – знову «додати».
5. Тепер у списку вибираємо перший діапазон. Ставимо пташку біля одиниці. Це перше поле зведеного звіту. Даємо йому ім'я – «Магазин 1». Виділяємо другий діапазон даних - ставимо пташку у цифри "2". Назва поля - "Магазин 2". Натискаємо "Далі".
6. Вибираємо, де розташувати зведену таблицю. На існуючому аркуші чи новому. Краще вибрати новий лист, щоб не було накладень та зміщень. У нас вийшло так:

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

Як працювати зі зведеними таблицями в Excel

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

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

Додамо до зведеної таблиці ще одне поле для звіту. Для цього встановимо галочку навпроти "Дати" (або навпроти "Товара"). Звіт одразу змінюється – з'являється динаміка продажів щодня у кожному відділі.

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

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

А ось що вийде, якщо ми приберемо "дату" і додамо "відділ":

А ось такий звіт можна зробити, якщо перетягнути поля між різними областями:

Щоб назва рядка зробити назвою стовпця, вибираємо цю назву, клацаємо по спливаючому меню. Натискаємо "перемістити в назву стовпців". У такий спосіб ми перемістили дату в стовпці.

Поле "Відділ" ми проставили перед найменуваннями товарів. Скориставшись розділом меню «Перемістити на початок».

Покажемо деталі щодо конкретного продукту. Приклад другої зведеної таблиці, де відображені залишки на складах. Виділяємо комірку. Клацаємо правою кнопкою миші - "розгорнути".

У меню вибираємо поле з даними, які необхідно показати.

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

Перевірка правильності виставлених комунальних рахунків

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

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

Наприклад ми зробили зведену табличку тарифів для Москви:

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

Перший стовпець = першому стовпцю зі зведеної таблиці. Другий – формула для розрахунку виду:

Тариф * кількість осіб / показання лічильника / площа

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

Завантажити всі приклади зведеної таблиці

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

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

На малюнку показано принцип, який я описав. Тобто. в діапазоні A2:E5 знаходиться вихідна зведена таблиця, яка перетворюється на список даних (діапазон H2:J14). Друга таблиця представляє той самий набір даних, лише іншому ракурсе. Кожне значення вихідної зведеної таблиці виглядає у вигляді рядка, що складається з пункту поля рядка, поля стовпця та відповідного значення. Таке відображення даних буває корисним, коли необхідно відсортувати та маніпулювати даними іншими способами.

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

Перейдіть по вкладці Файл -> Параметри. У діалоговому вікні, що з'явилося. ПараметриExcel,у вкладці Панель швидкого доступуу лівому полі знайдіть пункт Майстер зведених таблиць та діаграмі додайте його до правого. Натисніть кнопку ОК.

Тепер на панелі швидкого доступу з'явиться новий значок.

Клацніть на цій вкладці, щоб запустити Майстер зведених таблиць.

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

На кроці 2а вкажіть, як створювати поля сторінки. Помістіть перемикач Створити поля сторінки -> Далі.

На кроці 2б, у полі Діапазонвиберіть діапазон, який містить дані, і клацніть Додати.У нашому випадку це буде місце вихідної зведеної таблиці A1: E4.

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

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

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

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

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

Зведені таблиці необхідні підсумовування, аналізу та подання даних, що у «великих» вихідних таблицях, у різних розрізах. Розглянемо процес створення нескладних Зведених таблиць.

Зведені таблиці (Вставка/ Таблиці/ Зведена таблиця) можуть стати в нагоді, якщо одночасно виконуються такі умови:

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

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

Підготовка вихідної таблиці

Почнемо із вимог до вихідної таблиці.

• кожен стовпець повинен мати заголовок;
• у кожен стовпець повинні вводитися значення лише в одному форматі (наприклад, стовпець «Дата поставки» повинен містити всі значення лише у форматі Дата; стовпець «Постачальник» - назви компаній лише у текстовому форматі або можна вводити Код постачальника у числовому форматі);
• у таблиці повинні бути відсутні незаповнені рядки і стовпці;
• у комірки повинні вводитися «атомарні» значення, тобто. тільки ті, які не можна рознести у різні стовпці. Наприклад, не можна в один осередок вводити адресу у форматі: "Місто, Назва вулиці, будинок №". Потрібно створити 3 однойменні стовпці, інакше Зведена таблицяпрацюватиме неефективно (у разі, якщо Вам потрібна інформація, наприклад, у розрізі міста);
• уникайте таблиць із «неправильною» структурою (див. малюнок нижче).

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

Більш детальні поради щодо побудови таблиць викладені в однойменній статті.

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

Тепер поставимо галочку у Списку полів біля поля Продажу.

Т.к. осередки стовпця Продажі мають числовий формат, вони автоматично потраплять у розділ Списку полів Значення.

Декількома кліками миші (точніше шістьма) ми створили звіт про продаж по кожному товару. Того самого результату можна було досягти з використанням формул (див. статтю).
Якщо потрібно, наприклад, визначити обсяги продажів по кожному Постачальнику, то для цього знімемо галочку у Списку полів біля поля Товар та поставимо галочку біля поля Постачальник.

Деталізація даних Зведеної таблиці

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

Оновлення Зведеної таблиці

Якщо після створення Зведеної таблиціу вихідну таблицю додавалися нові записи (рядки), то ці дані не будуть автоматично враховані в Зведений таблиці. Щоб оновити Зведену таблицювиділіть будь-яку її комірку та виберіть пункт меню: меню Робота зі зведеними таблицями/ Параметри/ Дані/ Оновити. Того ж результату можна досягти через контекстне меню: виділіть будь-яку комірку Зведеної таблиці Оновити.

Видалення Зведеної таблиці

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

Зміна функції підсумків

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

Зміна порядку сортування

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

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

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

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

Зміна формату числових значень

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

У вікні виберіть числовий формат і поставте галочку прапорця Розділювач груп розрядів.

Додавання нових полів

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

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

Додавання стовпців

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

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

Змінюємо стовпці місцями

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

Видалення полів

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

Інший спосіб - зняти галочку навпроти поля, що видаляється у верхній частині Списку полів. Але в цьому випадку поле буде видалено відразу з усіх областей Списку полів (якщо воно використовувалося в кількох областях).

Додавання фільтра

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

• Зведеної таблиці натисніть пункт меню ;
• Ставимо галочки в Списку полів біля полів Група, Продаж та Прибуток;
• Переносимо поле Прибуток з області Назви рядків Списку полів у область Фільтр звіту;

Вигляд Зведеної таблицімає бути таким:

Тепер скориставшись Випадаючим списком, що розкриваєтьсяв осередку B1 (поле Прибуток) можна, наприклад, побудувати звіт про продажі Груп Товарів, які принесли прибуток.

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

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

Очистити фільтр можна через меню Робота зі зведеними таблицями/ Параметри/ Дії/ Очистити/ Очистити фільтри.

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

Декілька підсумків для одного поля

• Очистимо раніше створений звіт: виділіть будь-яке значення Зведеної таблиці, натисніть пункт меню Робота зі зведеними таблицями/ Параметри/ Дії/ Очистити/ Очистити все;
• Поставте галочки навпроти полів Товар та Продажі у верхній частині Списку полів. Поле Продаж буде автоматично розміщено в області Значення;
• Перетягніть мишею ще одну копію поля Продажу в область значення. У Зведений таблиціз'явиться 2 стовпці, які підраховують суми продажів;

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

Відключаємо рядки підсумків

Рядок підсумків можна відключити через меню: Робота зі зведеними таблицями/ Конструктор/ Макет/ Загальні підсумки. Не забудьте попередньо виділити будь-яку комірку Зведеної таблиці.

Групуємо числа та Дати

Припустимо, що необхідно підготувати звіт про терміни збуту. В результаті потрібно отримати таку інформацію: скільки партій Товару збувалося в період від 1 до 10 днів, у період 11-20 днів і т.д. Для цього:

• Очистимо раніше створений звіт: виділіть будь-яке значення Зведеної таблиці, натисніть пункт меню Робота зі зведеними таблицями/ Параметри/ Дії/ Очистити/ Очистити все;
• Поставте галочку навпроти поля Збут (термін фактичної реалізації Товару) у верхній частині Списку полів. Поле збуту буде автоматично розміщено в області значення;
• виділіть єдине значення поля Збут у Зведений таблиці, викличте контекстне меню правою клавішею миші та виберіть пункт Підсумки з/ Кількість.
• Перетягніть ще одну копію поля Збут в область Назви рядків;

Тепер Зведена таблицяпоказує скільки партій Товару збувалося за 5, 6, 7, … днів. Усього 66 рядків. Згрупуємо значення з кроком 10. Для цього:

• Виділіть одне значення Зведеної таблиціу стовпці Назви рядків;
• У меню виберіть пункт Угруповання по полю;
• вікно, Що З'явилося, заповніть, як показано на малюнку нижче;

• Натисніть кнопку ОК.

Тепер Зведена таблицяпоказує скільки партій Товару відбувалося у період від 1 до 10 днів, у період 11-20 днів і т.д.

Щоб розгрупувати значення, виберіть пункт Розгрупуватиу меню Робота зі зведеними таблицями/ Параметри/ Групувати.

Аналогічне угруповання можна провести по полю Дата поставки. У цьому випадку вікно Угруповання по полювиглядатиме так:

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

Умовне форматування осередків Зведеної таблиці

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

• Виділіть всі комірки, що містять значення продажів;
• Виберіть пункт меню Головна/ Стилі/ Умовне форматування/ Правила відбору перших та останніх значень/ 10 перших елементів;
• Натисніть кнопку ОК.