Життя людини зі світу технологій - це неймовірна комбінація цифр, показників, які періодично вимагають систематизації. Щоб здійснити останнє, доводиться використовувати особливі технічні інструменти. Наша стаття доведе, що зведені таблиці в 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 дійте за аналогією). Сподіваємося, ця інформація допоможе вам здійснювати швидкий аналіз консолідованих даних.
Відмінного Вам дня!
