Моя знайома, економіст на підприємстві, володіє Ексель, іноді поглядає на форуми, але 5 років не могла підступитися до самого, на мій погляд, корисного інструменту Excel - зведених таблицях. Що ж це таке? Зведені таблиці - це результат вибірки з правильно побудованих даних. Переваг у інструменту багато, я описувати не буду, просто скажу, що без неї багатьом доводилося б освоювати не тільки Excel, а й Microsoft Access.

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

СТ вимагає правильно побудованих даних. Ідеальний вигляд - однієї операції в рядок відповідають різні поля властивостей записи.

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

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

Excel запропонує діапазон, тому просто клікайте Ок.

Тут 4 варіанти: рядки, стовпці, значення і фільтр звіту.

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

значення   - сюди ставимо тільки цифрові значення.

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

Тепер на прикладі моїх даних.

Який вид може прийняти таблиця:

Як бачите, варіацій багато.

1) Хоча зведена таблиця витягує дуже наочно всі зрізи наявною інформацією, в ній працювати не завжди просто, вона не піддається редагуванню, тільки read-only. Якщо у вас багато розрізів інформації, то простіше зробити таблицю і заповнити її, використовуючи функцію ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ і правила роботи з відносними посиланнями.

Формат функції простий ( ​​«Значення»; «Зведена таблиця»; «Поле1»; «Елемент1»; «Поле2»; «Елемент2» і т.д.)

Тобто, можна вручну замінити значення Елементів на відносні посилання вашої таблиці.

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

«Очі бояться, а руки роблять»

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

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

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

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

На фото наведена зведена таблиця, яка побудована на основі наведеної вище вихідної таблиці.

Для того щоб побудувати зведену таблицю в Excel, потрібно виділити область, в якій знаходяться вихідні дані (включаючи заголовки стовпців), на вкладці Вставка -\u003e Таблиці розкрити список Зведена таблиця і вибрати команду Зведена таблиця.

На екрані з'явиться діалогове вікно Створення зведеної таблиці, в якому слід вибрати розміщення створюваної зведеної таблиці і зробити клацання на кнопці OK.

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

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

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

При побудові зведених таблиць потрібно звернути увагу на наступне. Кількість рядків і стовпців зведеної таблиці дорівнює кількості різних значень відповідного поля початкової таблиці. У розглянутому прикладі в колонці (в поле) Дата вихідної таблиці є назви тільки трьох місяців: грудень, Січень і Февраль. Тому в зведеній таблиці три основні колонки. Тут слід згадати, що вміст комірки може відображатися на екрані по-різному. Різні дати, наприклад 30.02.2010 і 8.02.2010, в разі, якщо заданий формат відображення дати "тільки місяць", на екрані відображаються як Февраль. При формуванні зведеної таблиці Excel виконує аналіз вмісту комірок колонок без урахування формату відображення. Тому, для того щоб наведений приклад працював правильно, дати витрат у вихідній таблиці повинні бути введені як текст.

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

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

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

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

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

Відкриваємо книгу на аркуші «Варіант 1»

відкриваємо « Дані - Зведена таблиця ...»

Відкриється вікно майстра створення зведених таблиць. Ставимо все як на знімку:

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

У відео-прикладі я вказав діапазон "Варіант 1"! $ A: $ G

Це необхідно в тому випадку, якщо таблиця постійно доповнюється даними, а постійно будувати таблицю може бути дуже важко або просто лінь :) Таким чином, я вказав діапазон стовпців, але діапазон рядків обмежений тільки можливостями Excel (в 2003 це 65536 рядків, в 2007- 2010 року понад 1млн. рядків). Але у такого способу є невеликий недолік, в таблицях з'являється критерій «порожньо» (побачите далі). Хоча мені він особливо не заважає.

На цьому кроці вказуємо, де створити таблицю. Ми залишаємо « новий лист». Так само можна відразу побудувати макет (на мій погляд це зручніше робити описаним далі способом, він більш наочний) або задати деякі параметри таблиці. Але все це можна в подальшому поправити.

тиснемо « Готово».

Ми побачимо наступну картину

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

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

Тип операції - тягнемо в ліву зону

Постачальник - так само в ліву, але трохи правіше Типу операції;

Найменування товару тягнемо в верхню зону;

Кількість і Суму тягнемо в найбільшу зону по черзі;

Для розбивки по датах перетягнемо поле Дата в зону трохи вище області даних;

В результаті отримаємо таку таблицю:

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

Виправляємо.

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

Клацнувши один раз лівою кнопкою миші все рядки групи «Кількість по полю Кількість» повинні виділитися як на знімку вище.

тепер тиснемо правою кнопкою   миші і в контексті вибираємо пункт « параметри поля»

У вікні параметрів обчислень виберемо « сума»

Те ж саме виконайте і для рядків групи «Кількість по полю Сума» /

Тепер сховаємо зайві рядки підсумків. Для цього також виділяємо групи:

І в контекстному меню вибираємо пункт « приховати»

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

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

Вийти таблиця виду:

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

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

Прикріплений файл: svodnaya_excel.zip

Відео:Будуємо зведену таблицю в Excel

Здрастуй шановний, читачу!

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

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

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

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

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

Єдиний великий мінус у всіх зведених таблицях, це те що вона не зможе бути застосована якщо дані в ній відповідають конкретним умовам, а саме:

1. Кожен без винятку стовпець зобов'язаний мати власний заголовок шапки;
2. Всі рядки і стовпчики ви зобов'язані заповнити, прогалини мають бути відсутні.
3. Для всіх стовпців даних, повинен бути певні формати осередків, для тих даних, які повинні в них зберігаються (приклад, для поля "Дата" потрібен формат календарної дати, а для поля "Контрагент" - формат тексту і т.п.)
4. Значення в цих осередках повинні бути "одноосібним", це означає такими які не діляться (наприклад, "Договір №23 від 03.09.2016 року" повинен бути записаний в 3 різних шпальтах "Документ", "Номер" і "Дата", це дозволить створювати гнучку і зручну систему). Також це можливо за допомогою
5. Якщо ви ведете витратно-прибуткової табличку в якій крім ще є потреба віднімання, то і в базу первинних даних вводите дані які вже спочатку зі знаком "-" і тоді в згорнутому вигляді ви отримаєте потрібний вам результат;
6. Сама конструкція вашої зведеної таблиці повинна мати оптимальний вид.

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

Як створюється зведена таблиця в Excel

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

На панелі управління вибираємо вкладку «Вставка» і отримуємо на вибір 2 варіанти створення вашої зведеної таблиці:

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

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

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

2. Зведена таблиця (майстер зведених таблиць)

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

У вікні, ми вибираємо кілька умов створення зведеної таблиці - це діапазон потрібних вихідних даних і куди ж слід запхати зведену табличку, толі поряд на одному аркуші, ну або створити новий лист і на ньому розмістити. А оскільки курсор вже стояв на таблиці, Excel швидко і автоматично визначив структуру таблиці і підставив в графу діапазону. Натискаємо «ОК» і отримуємо:

Як бачите, був створений новий «Ліст3» і викликаний «Майстер створення зведених таблиць» ну або для друзів просто «Конструктор зведених таблиць», його ви власне і бачите в наступному скріншоті. У конструкторі ви вказуєте які стовпчики вихідної таблиці, вам потрібно перенести в зведену і які саме обчислення потрібно буде над ними зробити і все це відбувається звичайним перетягуванням в необхідну область, заголовка потрібної нам таблиці.

Ось ми отримали і наш перший результат, але він нас не влаштовує так як у нас не підсумовується кількість фруктів які були продані, а значить, нам потрібно з області «РЯДКИ» перетягнути заголовок стовпця «Вага, кг» і у нас створюється та конструкція зведеної таблиці, яку ми хочемо.

Ну ось форма-то та, звичайно, але ось результат не той, а саме поле «Вага, кг» збирає за критерієм - кількість значень, а нам треба, а значить підводимо курсор миші до області значень «ЗНАЧЕННЯ» і на вказівку поле « кількість по полю Вага, кг », натискаємо ліву кнопку миші викликаючи контекстне меню. Нам потрібно вибрати останній пункт «Параметр полів значень».

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

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

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

Ну що ж зведена таблиця з вибіркою фруктів у нас зроблена. Але що ж робити якщо нам потрібно і цікаво знати, а як же все-таки відбувається рух по країнам. Та й будь-якого буде цікаво під різними кутами, а оскільки ми вже відформатували таблицю і все зробили для ідеальної роботи. Ми просто копіюємо нашу табличку і в поле необхідної області «РЯДКИ» міняємо обчислювані значення місцями. Вказуємо першим обчислюваним значенням «Країна», ось і все з 1 вихідної таблиці даних ми отримали 2 зведені таблиці потрібних нам даних.

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

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

Але не варто забувати що дані в нашій таблиці самостійно не вміють оновлюватися, а це значить те що при зміні в вихідних даних (для прикладу я просто прибрав дані 2 рядків) у нас змінився підсумок, а значиться треба повідомити про це нашій зведеній таблиці:

Для цього, в меню «Робота зі зведеними таблицями» переходимо в закладку «Аналіз» і натискаємо кнопочку «Оновити» та всі дані перерахувати, зважаючи на зміни, які ми внесли в нашу вихідну таблицю:

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

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

Наприклад, ось простий список витрат сім'ї і зведена таблиця, створена на його основі:

А ось зведена діаграма:

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

Перед початком роботи

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

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

Робота зі списком полів зведеної таблиці

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

Значення в зведеній таблиці

Оновлення зведених таблиць

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