Зведені таблиці в Excel: докладна покрокова інструкція

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

Початок роботи

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

Наприклад, розглянемо одні і ті ж фінансові витрати в різних місцях.

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

Для того щоб побудувати подібну таблицю, необхідно зробити наступні дії.

  1. Для початку її необхідно повністю виділити.

  1. Потім перейдіть на вкладку «Вставка». Натисніть на іконку «Таблиця». В меню оберіть пункт «Зведена таблиця».

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

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

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

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

  1. Для завершення налаштувань потрібно натиснути на кнопку «OK».

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

  1. На цьому етапі необхідно вказати, яке поле буде:
    1. стовпцем;
    2. рядком;
    3. значенням для аналізу.

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

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

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

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

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

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

  1. Знову закриваємо помічник для побудови.

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

Використання рекомендованих зведених таблиць

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

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

  1. Перейдіть на вкладку «Вставка». Потім натисніть на іконку «Таблиця». В меню оберіть другий пункт.

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

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

  1. Можна вибрати те, що подобається найбільше.

  1. Для вставки обраного варіанту достатньо натиснути на кнопку «OK».

  1. У підсумку ви отримаєте наступний результат.

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

аналіз

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

Розглянемо кожну з них більш детально.

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

Натиснувши на кнопку, зазначену на скріншоті, ви зможете зробити наступні дії:

  • змінити ім’я;

  • викликати вікно налаштувань.

У вікні параметрів ви побачите багато чого цікавого.

активне поле

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

  1. Для початку потрібно виділити якусь клітинку. Потім натисніть на кнопку «Активне поле». У меню клікніть на пункт «Параметри поля».

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

  1. Крім цього, можна налаштувати числовий формат. Для цього потрібно натиснути на відповідну кнопку.

  1. В результаті з’явиться вікно «Формат ячеек».

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

групувати

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

вставити зріз

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

  1. Виділіть якийсь стовпець. Потім натисніть на кнопку «Вставити зріз».
  2. У вікні, в якості прикладу, виберіть одне із запропонованих полів (в майбутньому ви можете виділяти їх в необмеженій кількості). Після того як що-небудь буде вибрано, відразу ж активується кнопка «OK». Натисніть на неї.

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

  1. Можна клікнути на будь-який з пунктів. Відразу після цього в поле сума зміняться всі значення.

  1. Таким чином вийде вибрати будь-який проміжок часу.

  1. У будь-який момент все можна повернути в початковий вигляд. Для цього потрібно клікнути на іконку в правому верхньому куті цього вікна.

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

Вставити тимчасову шкалу

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

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

Потім потрібно буде побудувати зведену таблицю.

Знову переходимо на вкладку «Вставка». Кількома на іконку «Таблиця». У підменю вибираємо потрібний нам варіант.

  1. Потім нас попросять вибрати діапазон значень.

  1. Для цього достатньо вибрати всю таблицю.

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

  1. Редактор Excel запропонує нам всього один варіант, оскільки таблиця дуже проста (для прикладу більше і не потрібно).

  1. Спробуйте знову натиснути на іконку «Вставити тимчасову шкалу» (вона розташована на вкладці «Аналіз»).

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

  1. Завдяки цьому з’явиться віконце, в якому можна буде вибирати потрібну дату за допомогою бігунка.

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

оновити

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

Джерело даних

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

Для цього потрібно натиснути на іконку «Джерело даних». Потім вибрати однойменний пункт меню.

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

дії

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

  • очистити таблицю;
  • виділити;
  • перемістити її.

обчислення

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

До них відносяться:

  • обчислюване поле;

  • обчислюваний об’єкт;

  • порядок обчислень (в списку відображаються додані формули);

  • вивести формули (інформації немає, так як немає доданих формул).

сервіс

Тут ви зможете створити зведену діаграму або змінити тип рекомендованої таблиці.

Показати

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

Завдяки цьому ви зможете:

  • налаштувати відображення бічній панелі зі списком полів;

  • включити або вимкнути кнопки «плюс / міну» с;

  • налаштувати відображення заголовків полів.

конструктор

При роботі зі зведеними таблицями крім вкладки «Аналіз» також з’явиться ще одна – «Конструктор». Тут ви зможете змінити зовнішній вигляд вашого об’єкта аж до невпізнання в порівнянні з варіантом за умовчанням.

Можна налаштувати:

  • проміжні висновки:
    • НЕ показувати;
    • показувати все підсумки в нижній частині;
    • показувати все підсумки в заголовку.

  • загальні підсумки:
    • відключити для рядків і стовпців;
    • включити для рядків і стовпців;
    • включити тільки для рядків;
    • включити тільки для стовпців.

  • макет звіту:
    • показати в стислій формі;
    • показати в формі структури;
    • показати в табличній формі;
    • повторювати всі підписи елементів;
    • не повторювати підписи елементів.

  • порожні рядки:
    • вставити порожній рядок після кожного елемента;
    • видалити порожній рядок після кожного елемента.

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

  • налаштувати стиль оформлення елементів.

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

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

Крім цього, при бажанні, ви можете створити свій власний стиль оформлення.

Сортування значень

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

Для цього потрібно зробити наступне.

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

Якщо стандартного варіанту недостатньо, ви можете в цьому ж меню клікнути на пункт «Додаткові параметри сортування».

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

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

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

Описані вище дії підходять для сучасних редакторів (2007, 2010, 2013 і 2016 року). У старій версії все виглядає інакше. Можливостей, зрозуміло, там набагато менше.

Для того щоб створити зведену таблицю в Ексель 2003 року, потрібно зробити наступне.

  1. Перейти в розділ меню «Дані» і вибрати відповідний пункт.

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

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

  1. Для завершення налаштувань тиснемо на «Готово».

  1. В результаті цього ви побачите наступне. Тут потрібно перетягнути поля до відповідних області.

  1. Наприклад, може вийти ось такий результат.

Стає очевидно, що створювати подібні звіти набагато краще в сучасних редакторах.

висновок

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

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

ВІДЕОінструкція

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

Ссылка на основную публикацию