Вітаю шановний, читач! Сьогодні хочу торкнутися такої важливої і глобальної функції MS Excel - це зведена таблиця. Зведена таблиця в Excel спеціалізована для того, щоб приводить в порядок величезну кількість ваших даних які складаються, в таблиці і робить її в тому вигляді і обсязі, який вам потрібен. І якщо ви працюєте з великими об'ємами даних і не використовувати зведену таблицю, це рівноцінно розглядати калькулятор в ролі молотка. Ви просто фізично не в змозі обробити за короткий час тисячі, десяток, а то і сотню тисяч рядків в яких ви зберігаєте величезну кількість матеріалу, а ось здатність зведеної таблиці це вам дозволить зробити і не в одній формі, а в тому вигляді який вам потрібен. При всьому цьому, такі маніпуляції ні крапельки не проявляються на первинних даних.
Зведена таблиця може динамічно змінювати дані, значить коли ви в базу даних (вихідна таблиця даних) вносите коригування, вони також само собою змінюють вашу зведену таблицю.
Виникає закономірне питання, де ж застосування зведеної таблиці дасть найбільший ефект:
- по-перше, коли проробляється аналіз бази даних за різноманітними критеріями (місто, номенклатура, персонал, пора року тощо)
- по-друге, коли просто працюєш з величезною кількістю статистичної або аналітичної інформації фільтри з вибіркою зовсім не можуть вам допомогти;
- по-третє, це коли попередні 2 варіанти потрібно постійно перераховувати, оновлюючи свою базу даних.
Хоча я може бути я і не торкнувся ще якісь варіанти використання, але ці я вважаю основними, а решта - це вже схожі від них.
Єдиний великий мінус у всіх зведених таблицях, це те що вона не зможе бути застосована якщо дані в ній відповідають конкретним умовам, а саме:
- Кожен без винятку стовпчик зобов "язаний мати власний заголовок шапки;
- Всі рядки і стовпчики ви зобов'язані заповнити, пробіли повинні бути відсутні.
- Для всіх стовпчиків даних, должені бути певні формати комірок, для тих даних, які повинні в них зберігатися (приклад, для поля «Дата» потрібен формат календарної дати, а для поля «Контрагент» - формат тексту тощо)
- Значення в цих комірках повинні бути «одноосібним», це означає такими, що не діляться (наприклад, «Договір № 23 від 03.09.2016 року» повинен бути записаний в 3 різних стовпчиках «Документ», «Номер» і «Дата», це дозволить створювати гнучку і зручну систему). Також це можливо за допомогою функції ЗЧЕПИТИ.
- Якщо ви ведете видатково-прибуткову табличку в якій крім підсумовування ще є потреба віднімання, то і в базу первинних даних вводите дані які вже спочатку зі знаком «-» і тоді в згортаному вигляді ви отримаєте потрібний вам результат;
- Сама конструкція вашої зведеної таблиці зобов'язана мати оптимальний вигляд.
Якщо ви вже виконали всі умови ви отримаєте диво-інструментарій для роботи з вашою базою даних інформації, та й не тільки.
Трохи теорії
Зведені таблиці Excel (для чайника) - це різновид реєстру, який містить конкретну частину даних з джерела для аналізу і зображений так, щоб можна було простежити між ними логічні зв'язки. Основа для її проектування - певний список значень.
Перш ніж почати роботу, варто підготувати для неї необхідні матеріали, які можна звести для аналізу. Формуючи підготовчий варіант, стежте за тим, щоб дані були класифіковані, наприклад, цифри не плуталися з буквеним позначенням, а всі стовпчики мали заголовки.
Зведені таблиці в Excel незамінні для тих, хто за родом зайнятості має справу з великою кількістю цифр, які періодично необхідно систематизувати і формувати звітність. Програма Excel допоможе підрахувати і проаналізувати велику кількість значень, заощадивши час.
Плюси використання такого виду угруповання даних:
- під час роботи не потрібні особливі пізнання зі сфери програмування, метод підійде і для чайників;
- можливість комбінувати інформацію з інших першоджерел;
- можна поповнювати базовий екземпляр новою інформацією, дещо підкоригувавши параметри.
Навчання роботі зі зведеними таблицями в Excel не займе багато часу і може ґрунтуватися на відео.
Створення
Створити зведену таблицю можна в програмах різних версій (2010-2016). Розглянута інструкція створена на основі Майкрософт Ексель 2013.
У запущеній програмі потрібно перейти до вкладки «Вставка» і вибрати «Зведена таблиця».
Після виконання описаних дій на робочому полі з'явиться вікно, яке необхідно заповнити.
У першому порожньому рядку необхідно вказати координати таблиці з даними, їх можна прописати вручну, але простіше просто виділити бажаний об'єкт.
Зверніть увагу! Цим способом неможливо консолідувати (об'єднати) кілька таблиць.
Якщо база даних зберігається в іншій книзі, її можна вказати, вибравши розділ «Використовувати зовнішнє джерело».
Останнім етапом буде вибір розташування нової конструкції. Рекомендується за можливості розміщувати її на новій сторінці, але дозволяється вибрати координати на наявному аркуші.
Після натискання кнопки «Ok» з'явиться нова сторінка, на якій розташовано основу бажаного об'єкта та набір інтсрументів для створення структурованих об'єктів.
Зверніть увагу! Якщо перед цим етапом вибрати таблицю, всі поля буде заповнено автоматично.
Приклад створення зведеної таблиці Excel - алгоритм чайників
Ознайомившись з базовими теоретичними нюансами про зведені таблиці в Excel, давайте перейдемо до застосування їх на ділі. Для старту створення зведеної таблиці в Excel 2020, 2010 або 2007 необхідно встановити програмне забезпечення. Як правило, якщо ви користуєтеся програмами системи Microsoft Office, то Excel вже є на вашому комп'ютері. Запустивши його, перед вами відкриється велике поле, поділене на велику кількість комірок. Більш детально про те, як робити зведені таблиці в Excel, вам підкаже відеоурок вище.
За допомогою наступного алгоритму ми детально розглянемо приклад, як побудувати зведену таблицю в Excel. На панелі вгорі вікна переходимо на вкладку «Вставка», де ліворуч оберемо «Зведена таблиця».
Далі на екрані відкривається діалогове вікно, де потрібно вказати відповідні параметри. На цьому етапі створення зведених таблиць в Excel є кілька важливих моментів. Якщо ви встановили піктограму курсора на аркуші, заповнення порожніх рядків вікна буде автоматично. Інакше адресу діапазону даних потрібно позначити самостійно.
Розгляньмо детальніше самостійне заповнення пунктів діалогового вікна.
Перший рядок не залишаємо порожнім, інакше програма видасть помилку. Якщо є джерело, з якого плануєте переноситися дані, виберіть його в пункті «Використовувати зовнішнє джерело даних». Під зовнішнім джерелом мається на увазі інша книга Excel або набір моделей даних з СУБД.
Заздалегідь озаглавте кожен стовпчик
Виберіть місце, де буде розташовано майбутню рамку з комірками. Це може бути нове вікно або ж цей аркуш, рекомендуємо використовувати інший аркуш.
Закріпивши всі налаштування, отримуємо готову основу. Ліворуч розташовується область, де розміщена основа майбутньої рамки. У правій частині є вікно з параметрами, яке допомагає керувати реєстром.
Тепер необхідно розібратися, як будується вся ця конструкція. У вікні параметрів «Поля вільної таблиці» ви позначите дані, які будуть присутні.
Вся структура будується таким чином, щоб текстові дані грали роль об'єднуючих елементів, а числові показували консолідовані значення. Наприклад, об'єднаємо всі надходження по відділах і дізнаємося на яку суму отримав товари кожен. Для цього поставимо галочку у двох заголовків: відділ і вартість товару в партії.
Зауважте, як розташовано ці дані у нижній області панелі налаштування.
Відділ автоматично пішов у рядки, а числові дані у значення. Якщо ви спробуєте клацнути на будь- якому стовпчику з числами, їх буде показано у цій області. А в самій таблиці додасться новий стовпчик.
При цьому відбувається підсумовування. Зараз ми можемо дізнатися з нашого звіту, скільки товарів надійшло в кожен відділ і їх загальну вартість.
Ви можете вільно налаштувати ширину стовпчиків для оптимального розташування даних. Просто розсувайте ширину стовпчиків або висоту рядків так, як ви звикли в Excel.
Якщо вам не подобається така орієнтація, ви можете перетягнути мишкою назви рядків в область стовпчиків - просто наведіть мишу, натисніть ліву кнопку і тягніть.
Що стосується підрахунку результатів, то сума - далеко не єдина функція. Щоб подивитися, які Excel пропонує ще, натисніть на назву в області значень і виберіть останню команду.