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

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

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

Зведені таблиці: Короткий вступ.

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

Why is it called a “Pivot table”? This is because you can  обертати або переміщувати   this table using another attribute of your data. Let’s gain a deeper understanding of this.

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

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

Переваги зведених таблиць.

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

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

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

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

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

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

Ви можете легко визначити шаблони в даних за допомогою зведених таблиць.

• Зведені таблиці допомагають легко створювати звіти зі своїх даних.

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

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

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

На аркуші Excel під назвою Список предметів ми маємо такі стовпці:

• Стовпець B: Ні.
• Стовпець С: Код товару.
• Стовпець D: Назва предмета.
• Стовпець E: Опис.
• Стовпець F: власник.
• Стовпець G: Перша дата продажу.
• Стовпець H: Ринкова ціна.
• I стовпець: Ціна наклейки.
• Колонка J: Фактична ціна продажу.
• Стовпець K: Дата продажу.
• Стовпець L: продано.
• Колонка М: Телефон покупця.

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

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

Виконайте наступні дії:

1. Почніть створювати зведену таблицю

Клацніть на опції меню ВСТАВИТИ, а потім натисніть Зведена таблиця.

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

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

Стати Excel Pro: Приєднуйтесь до нашого курсу!

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

Зареєструйтесь сюди

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

3. Перегляньте поля для зведеної таблиці зі списку.

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

• Фільтри.
• Стовпці.
• Рядки.
• Цінності.

4. Перетягніть відповідні поля для створення вашої зведеної таблиці.

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

Які значення ми аналізуємо в цьому зразковому проекті? Це ціна наклейки, ринкова ціна та фактична ціна продажу. Відповідні стовпці в головному аркуші даних - це Наклейка ціна, Ринкова ціна та Фактична ціна продажу відповідно. Це стовпці I, H і J в головному аркуші даних.

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

• Чи можемо ми знайти ціни на товар у відсотках?
• Чи можна додати до рядків поле код коду?

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

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

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

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

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

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

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

6. Обмежте свій аналіз, фільтруючи рядки.

Що робити, якщо ви хочете виключити елемент з аналізу? Потрібно відфільтрувати рядки в зведеній таблиці. Клацніть на спадному списку біля Мітки рядків.

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

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

7. Додавання поля Код предмета до окремих рядків.

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

Висновок:

Microsoft Excel - це потужний інструмент. Більше того, сховища на зразок шаблону Excel надають дуже корисні шаблони, які допомагають швидко досягти багато чого. Крім того, у Excel є потужні вбудовані інструменти, як зведені таблиці для аналізу даних. У цьому посібнику ми створили зведену таблицю з даних Excel, використовуючи простий покроковий підхід. Використовуйте цей потужний інструмент Excel, щоб зрозуміти ваші дані!

Сніжина Піскова

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