Зведена таблиця з декількох аркушів | Як створити зведену таблицю?

Зведена таблиця Excel із декількох аркушів

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

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

Як створити зведену таблицю з декількох аркушів?

Реалізація зведених таблиць з декількох аркушів має деякі додаткові процеси. Цей процес також може здійснюватися різними способами. Нижче наведено різні методи створення зведеної таблиці з декількох аркушів у Excel.

Ви можете завантажити цю зведену таблицю з декількох аркушів шаблону Excel тут - Зведену таблицю з декількох аркушів шаблону Excel

Процес No1

Для цього нам потрібні дані на кількох робочих аркушах. Наприклад: аркуш1, аркуш2, аркуш3 тощо.

Ні. аркушів має бути два або більше 2.

  • Крок 1: Клацніть Alt + D, потім натисніть P., з’явиться таке діалогове вікно.

  • Крок 2: У цьому діалоговому вікні виберіть Кілька діапазонів консолідації та натисніть ДАЛІ.

  • Крок 3: На цьому кроці після натискання НАСТУПНИЙ перейде до кроку 2, і в цьому пункті виберіть «Я буду створювати поля сторінки», як у діалоговому вікні нижче.

  • Крок 4: Після вибору наведених вище опцій натисніть кнопку ДАЛІ ще раз. Вікно також буде переміщено на третій крок. На цьому кроці буде відкрито ще один крок після натискання кнопки ДАЛІ, як показано нижче,

  • Крок 5: Виберіть діапазон таблиці 1, а потім натисніть кнопку ДОДАТИ, а потім виберіть діапазон іншої таблиці на іншому аркуші, а потім натисніть ДОДАТИ. Тепер дві таблиці будуть додані до однієї таблиці.
  • Крок 6: Тепер виберіть поле Сторінка як 1 під опцією Діапазони. Потім дайте імена двом аркушам відповідно до кращого розуміння, так що якщо ми клацнемо таблицю 1, тоді назва таблиці буде показано в зведеній таблиці для кращого розуміння.

  • Крок 7: Тепер є можливість вибрати, чи буде зведена таблиця вставлятися в новий аркуш чи наявний аркуш. За замовчуванням буде вибрано новий аркуш, як показано на малюнку.

  • Крок 8: Нарешті, натисніть кнопку Готово. Тепер зведену таблицю буде створено на новому аркуші.

  • Крок 9: За замовчуванням значення відображатиметься для Count. Ми повинні змінити значення в заголовках для суми. Для цього перейдіть до будь-якої комірки, що містить значення та клацніть правою кнопкою миші, а потім виберіть опцію «Підсумувати значення за» та в
  • Крок 10: Після цього ми можемо видалити Стовпці, які насправді нам потрібні, і вони нам не потрібні. Це можна зробити, вибравши “Етикетки стовпців”. Ми можемо перевірити мітки стовпців залежно від того, що це необхідно.
  • Крок 11: Тепер є інша колонка для загальних підсумків. Загалом нам не потрібні загальні підсумки стовпців. Отже, це можна видалити, клацнувши правою кнопкою миші на значенні та вибравши „Параметри зведеної таблиці”, і з’явиться діалогове вікно, як показано нижче.

  • Крок 12: Перейдіть на вкладку «Підсумки та фільтри» та зніміть прапорець «Показати загальні підсумки для рядків». Якщо це необхідно, ви можете зберегти його або зняти прапорець біля нього. Тепер натисніть ОК.

  • Крок 13: Тепер загальні підсумки для стовпців будуть доступні лише для стовпців.

  • Крок 14: Завершальним кроком буде зміна назви зведеної таблиці, її можна визначити користувачем або пов’язати з даними зведеної таблиці.

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

Процес No2

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

Тут ми будемо використовувати аркуш 4, аркуш 5, щоб створити зведену таблицю з декількох аркушів у Excel.

Ми проаналізуємо другий метод за допомогою наступного прикладу.

  • Крок 1: На аркушах 3 та 4 виберіть таблицю, натисніть CTRL + T, щоб вибрати цілі дані та намалювати таблицю для повних даних. Тепер у дані буде вставлена ​​таблиця. Ім'я буде показано для таблиці в лівому куті. Цей процес можна виконати для всіх таблиць, що містять дані.

Фільтри будуть додані за замовчуванням, якщо вони нам не потрібні, ми можемо їх вимкнути, натиснувши сполучення клавіш CTRL + SHIFT + L з клавіатури або просто перейдіть на вкладку «Дані» та натисніть опцію Фільтри. Тоді фільтри буде вимкнено.

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

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

Добре використовувати новий варіант аркуша в Excel.

  • Крок 2: Нарешті встановіть прапорець «Додати цю таблицю до моделі даних». Це важливий крок у цьому процесі створення зведеної таблиці.

  • Крок 3: Зведена таблиця буде створена зараз на новому аркуші, і в правій частині ми маємо всі поля, пов’язані зі зведеною таблицею.

  • Крок 4: Перейдіть на  вкладку "Аналіз " -> Відносини -> Нове.

  • Крок 5: Таблиця - це поточна таблиця. Пов’язана таблиця - це таблиця, яку слід додати до зведені таблиці. Пов’язаний стовпець - це стовпець, який однаковий в обох таблицях, це з першої таблиці, він також називається первинним ключем. Стовпець - це той самий стовпець у другому стовпці, який називається зовнішнім ключем.

  • Крок 6: Тепер натисніть OK.

  • Крок 7: Тепер ми можемо вибрати необхідні поля відповідно до вимоги. Поля можна вибрати з усіх таблиць зведених таблиць. Для цього спочатку буде запропоновано створити нову зведену таблицю.

  • Крок 8: Натисніть Так, і ви можете вибрати поля з усіх таблиць, щоб створити зведену таблицю.

Процес No3

Перше і головне в цьому методі - це присвоєння імені всім даним. Ми проілюструємо це на прикладі, нижче наведено приклад цього процесу.

  • Крок 1: Створіть таблицю для всіх даних за допомогою CTRL + T та видаліть фільтри. Тепер перейдіть на вкладку «Дані» -> натисніть «З інших джерел» -> З запиту Microsoft -> Файли Excel.

  • Крок 2: Потім виберіть аркуш, в якому ви створили дані.

  • Крок 3:  На цьому кроці буде показано інше вікно для вибору таблиць усіх аркушів книги. Таблиці можна вибрати відповідно до вимог, як показано нижче. Ми вибрали аркуші No 5 та 6.

  • Крок 4: Вибравши таблиці з аркушів, натисніть Далі, ви отримаєте це діалогове вікно і натисніть на OK.

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

  • Крок 5: Потім з’явиться вікно, в якому будуть присутні всі дані, що належать до всіх таблиць. У цьому вікні буде дві частини, одна - «Діапазон імен», а інша - «Область даних».

  • Крок 6: Тепер перейдіть до меню «Файл», виберіть «Повернути дані в Microsoft Excel»

  • Крок 7: Тепер ви отримаєте вікно “Імпорт даних”.

  • Крок 8: У цьому вікні, коли ми хочемо створити зведену таблицю, виберіть опцію “Звіт зведеної таблиці” та натисніть кнопку ОК.

  • Крок 9: Тепер зведена таблиця готова, і ми можемо створити її відповідно до вимоги.

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

Що слід пам’ятати

  • Для створення зведеної таблиці в одній книзі повинно бути дві або більше таблиць.
  • Якщо будь-які нові дані додаються в будь-яку з таблиць після одного разу створення зведеної таблиці, то для відображення змін нам потрібно оновити зведену таблицю вручну.
  • Кожного разу, коли ми створюємо зведену таблицю, її слід створювати на новому аркуші, щоб правильно відображати зміни.
  • Коли ми перетягуємо атрибути excel з будь-якої з таблиць в будь-яке з 4 полів, підсумок звіту буде сформовано всього за кілька секунд.
  • Головне при створенні зведеної таблиці - жодна клітинка, стовпець або рядок не повинні залишатися порожніми, якщо немає значення, воно має бути «0», або будь-яке інше значення. В іншому випадку зведена таблиця відображатиметься неправильно.
  • Порада щодо швидкого інструменту. Короткий довідник зведеної таблиці можна додати на панель швидких інструментів, використовуючи такий процес.

Клацніть Налаштувати панель інструментів швидкого доступу, перейдіть до Додаткові команди -> Популярні команди -> Виберіть зведені таблиці -> натисніть ДОДАТИ.

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