Об'єднання даних з кількох аркушів
Якщо дані, які потрібно проаналізувати, представлені на декількох аркушах або кількох книгах, їх можна об'єднати на одному аркуші за допомогою команди "Консолідація". Наприклад, якщо є окремий аркуш витрат для кожного регіонального представництва, за допомогою консолідації можна створити на базі цих даних корпоративний аркуш витрат. Такий лист може містити підсумкові та середні дані з продажів, поточних рівнів запасів та найбільш популярних продуктів у рамках усієї організації.
Тип консолідації слід вибирати з урахуванням того, як виглядають аркуші, що об'єднуються. Якщо дані на аркушах розташовані однаково (назви рядків та стовпців можуть при цьому відрізнятися), скористайтесь консолідацією за розташуванням. Якщо ж на аркушах для відповідних категорій використовуються однакові назви рядків і стовпців (дані можуть бути розташовані по-різному), використовуйте консолідацію по категорії.
Консолідація за розташуванням
Для консолідації за розташуванням діапазон даних на кожному з вихідних аркушів повинен мати формат списку без порожніх рядків і стовпців.
- Відкрийте кожен вихідний аркуш і переконайтеся, що дані на кожному аркуші розташовані в однаковому положенні.
- На цільовому аркуші виберіть верхню ліву комірку області, в якій мають відображатися об'єднані дані.
Примітка: Переконайтеся, що справа та знизу достатньо вільних осередків для консолідованих даних.
Консолідація за категорією
Для консолідації за категорією діапазон даних на кожному з вихідних аркушів повинен мати формат списку без порожніх рядків та стовпців.Крім того, категорії повинні бути послідовно позначені. Наприклад, якщо один із стовпців називається. Середовище. , а інший - СереднєКонсолідація не підсумує ці стовпці.
- Відкрийте кожен із вихідних аркушів.
- На цільовому аркуші виберіть верхню ліву комірку області, в якій мають відображатися об'єднані дані.
Примітка: Переконайтеся, що справа та знизу достатньо вільних осередків для консолідованих даних.
Примітка: Будь-які назви, що не збігаються з назвами в інших вихідних областях, можуть призвести до появи консолідованих даних окремих рядків або стовпців.
Консолідація кількох аркушів в одній зведеній таблиці
Консолідація даних є зручним способом об'єднання даних з декількох джерел в одному звіті. середні дані з продажів, поточних рівнів запасів та найбільш популярних продуктів у рамках всієї організації.
Примітка: Інший спосіб консолідації даних - використання Power Query. Додаткові відомості див.
Для підбиття підсумків та узагальнення результатів за даними в окремих діапазонах можна консолідувати їх у зведеній таблиці на головному аркуші. мірою необхідності.
Підсумковий консолідований звіт зведеної таблиці може містити такі поля в області Список полів зведеної таблиці, що додається в зведену таблицю: "Рядок", "Стовпець" та "Значення". Крім того, у звіт можна включити до чотирьох полів фільтра, які називаються "Сторінка1", "Сторінка2", "Сторінка3" та "Сторінка4".
Кожен з діапазонів даних слід перетворити на формат перехресної таблиці з іменами рядків і стовпців, що збігаються, для елементів, які ви хочете об'єднати. У вибрані дані не слід включати підсумкові рядки та підсумкові стовпці. У наведеному нижче прикладі показано чотири діапазони у форматі перехресної таблиці.
При консолідації даних можна використовувати поля сторінки, що містять елементи, які становлять один або кілька вихідних діапазонів. Наприклад, при консолідації даних бюджету відділу маркетингу, відділу продажу та виробничого відділу поле сторінки може містити окремий елемент з даними щодо кожного з цих відділів, а також елемент, що містить зведені дані. У прикладі нижче показано зведену таблицю, в якій вибрано одне поле сторінки та кілька елементів.
Якщо велика ймовірність того, що наступного разу при консолідації даних вихідний діапазон даних зміниться (наприклад, зміниться кількість рядків), рекомендується вказати ім'я для кожного з вихідних діапазонів на різних аркушах. Ці імена можна використовувати для консолідації діапазонів на головному аркуші. У разі розширення вихідного діапазону перед оновленням зведеної таблиці можна оновити діапазон на окремому аркуші для вказаного імені таким чином, щоб увімкнути нові дані.
В Excel також доступні інші способи консолідації даних, які дозволяють працювати з даними у різних форматах та макетах. Наприклад, ви можете створювати формули з об'ємними посиланнями або використовувати команду Консолідація (доступну на вкладці Дані у групі Робота з даними).
Для консолідації кількох діапазонів можна скористатися майстром зведених таблиць і діаграм. У ньому можна зазначити, скільки полів сторінки використовуватиметься: жодного, одне чи кілька.
Щоб об'єднати дані всіх діапазонів та створити консолідований діапазон без полів сторінки, зробіть таке:
- Додайте майстер зведених таблиць та діаграм на панель швидкого доступу. Для цього:
- Клацніть стрілку поруч із панеллю інструментів та виберіть Додаткові команди.
- Натисніть Налаштувати панель швидкого доступу (…) у лівому нижньому кутку під стрічкою, а потім натисніть Додаткові команди.
- У списку Вибрати команди з виберіть пункт Усі команди.
- Виберіть у списку пункт Майстер зведених таблиць та діаграм та натисніть кнопку Додати, а потім - кнопку ОК.
Порада: Якщо діапазон клітинок знаходиться в іншій книзі, спочатку відкрийте її, щоб спростити вибір даних.
Щоб увімкнути одне поле сторінки, яке містить елемент для кожного вихідного діапазону, а також елемент для консолідації всіх діапазонів, зробіть таке:
- Додайте майстер зведених таблиць та діаграм на панель швидкого доступу. Для цього:
- Клацніть стрілку поруч із панеллю інструментів та виберіть Додаткові команди.
- Натисніть Налаштувати панель швидкого доступу (…) у лівому нижньому кутку під стрічкою, а потім натисніть Додаткові команди.
- У списку Вибрати команди з виберіть пункт Усі команди.
- Виберіть у списку пункт Майстер зведених таблиць та діаграм та натисніть кнопку Додати, а потім - кнопку ОК.
Порада: Якщо діапазон клітинок знаходиться в іншій книзі, спочатку відкрийте її, щоб спростити вибір даних.
Ви можете створити кілька полів сторінки та призначити імена елементів для кожного з вихідних діапазонів. Це дозволяє виконувати часткову чи повну консолідацію. Наприклад, в одному полі сторінки можуть консолідуватися дані відділу маркетингу та відділу продажів без даних виробничого відділу, а в іншому полі дані всіх трьох відділів. Щоб створити консолідацію з використанням кількох полів сторінки, зробіть таке:
- Додайте майстер зведених таблиць та діаграм на панель швидкого доступу. Для цього:
- Клацніть стрілку поруч із панеллю інструментів та виберіть Додаткові команди.
- Натисніть Налаштувати панель швидкого доступу (…) у лівому нижньому кутку під стрічкою, а потім натисніть Додаткові команди.
- У списку Вибрати команди з виберіть пункт Усі команди.
- Виберіть у списку пункт Майстер зведених таблиць та діаграм та натисніть кнопку Додати, а потім - кнопку ОК.
Порада: Якщо діапазон клітинок знаходиться в іншій книзі, спочатку відкрийте її, щоб спростити вибір даних.
- Якщо у розділі По-перше, вкажіть кількість полів сторінки зведеної таблиці задано число 1, виберіть кожний діапазон, а потім введіть унікальне ім'я в полі Перше поле. Якщо у вас чотири діапазони, кожен з яких відповідає кварталу фінансового року, виберіть перший діапазон, введіть ім'я "Кв1", виберіть другий діапазон, введіть ім'я "Кв2" та повторіть процедуру для діапазонів "Кв3" та "Кв4".
- Якщо у розділі По-перше, вкажіть кількість полів сторінки зведеної таблиці задано число 2, виконайте аналогічні дії у полі Перше поле. Потім виберіть два діапазони та введіть у поле Друге поле однакове ім'я, наприклад "Пг1" та "Пг2". Виберіть перший діапазон і введіть ім'я "Пг1", виберіть другий діапазон і введіть ім'я "Пг1", виберіть третій діапазон і введіть ім'я "Пг2", виберіть четвертий діапазон та введіть ім'я "Пг2".
