Зведена таблиця в кількох діапазонах даних

Постановка проблеми

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

Перш ніж ми почнемо, давайте уточнимо кілька моментів. Я вважаю, що апріорі наші дані виконують такі умови:

• Таблиці можуть мати будь-яку кількість рядків з будь-якими даними, але вони повинні мати однаковий заголовок.
• На аркушах з вихідними таблицями не повинно бути зайвих даних. Один аркуш – одна таблиця. Для управління раджу використовувати комбінацію клавіш Ctrl+кінець, який перемістить вас до останньої використаної клітинки на аркуші. В ідеалі це має бути остання комірка в таблиці даних. Якщо при натисканні на Ctrl+кінець будь-яка порожня клітинка праворуч або під таблицею буде виділена – видаліть ці порожні стовпці праворуч або рядки під таблицею після таблиці та збережіть файл.

Спосіб 1. Створення таблиць для зведених таблиць за допомогою Power Query

Починаючи з версії 2010 для Excel, існує безкоштовна надбудова Power Query, яка може збирати та перетворювати будь-які дані, а потім надавати їх як джерело для створення зведеної таблиці. Вирішити нашу проблему за допомогою цієї надбудови зовсім не складно.

Спочатку створимо новий порожній файл в Excel – в ньому буде відбуватися збірка, а потім буде створена зведена таблиця.

Потім на вкладці дані (якщо у вас Excel 2016 або новішої версії) або на вкладці PowerQuery (якщо у вас Excel 2010-2013) виберіть команду Створити запит – З файлу – Excel (Отримати дані — З файлу — Excel) і вкажіть вихідний файл із таблицями, які потрібно зібрати:

У вікні, що з’явилося, виберіть будь-який аркуш (не важливо який) і натисніть кнопку внизу Редагувати (Edit):

Вікно редактора запитів Power Query має відкритися поверх Excel. Праворуч від вікна на панелі Параметри запиту видалити всі автоматично створені кроки, крім першого – Source (Джерело):

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

Видалити всі стовпці, крім стовпця даніклацнувши правою кнопкою миші заголовок стовпця та вибравши Видалити інші стовпці (Видалити інші колонки):

Потім ви можете розгорнути вміст зібраних таблиць, натиснувши подвійну стрілку у верхній частині стовпця (прапорець Використовуйте оригінальну назву стовпця як префікс можна вимкнути):

Якщо ви все зробили правильно, то на цьому етапі ви повинні побачити вміст усіх таблиць, зібраних одна під одною:

Залишилося кнопкою підняти перший рядок до шапки таблиці Використовуйте перший рядок як заголовки (Використовуйте перший рядок як заголовки) таб Головна (Головна) і видаліть повторювані заголовки таблиці з даних за допомогою фільтра:

Збережіть все, що зроблено за допомогою команди Закрийте та завантажте – Закрийте та завантажте в… (Закрити та завантажити — Закрити та завантажити до…) таб Головна (Головна), і у вікні, що відкриється, виберіть опцію Тільки підключення (Тільки підключення):

все Залишається тільки побудувати резюме. Для цього перейдіть на вкладку Вставка – зведена таблиця (Вставити — зведена таблиця), виберіть варіант Використовуйте зовнішнє джерело даних (Використовуйте зовнішнє джерело даних)а потім, натиснувши кнопку Виберіть зв'язок, наше прохання. Подальше створення і налаштування зводу відбувається абсолютно стандартним способом шляхом перетягування потрібних нам полів в область рядків, стовпців і значень:

Якщо вихідні дані зміняться в майбутньому або буде додано ще кілька аркушів магазину, тоді буде достатньо оновити запит і наше резюме за допомогою команди Оновити все таб дані (Дані — оновити все).

Спосіб 2. Об'єднуємо таблиці командою UNION SQL в макрос

Інше рішення нашої проблеми представлено цим макросом, який створює набір даних (кеш) для зведеної таблиці за допомогою команди UNITY Мова запитів SQL. Ця команда об'єднує таблиці з усіх зазначених у масиві Назви аркушів аркушів книги в одну таблицю даних. Тобто замість того, щоб фізично копіювати та вставляти діапазони з різних аркушів на один, ми робимо те саме в оперативній пам’яті комп’ютера. Потім макрос додає новий аркуш із заданим ім’ям (змінна ResultSheetName) і створює повноцінне (!) резюме на основі зібраного кешу.

Щоб використати макрос, скористайтеся кнопкою Visual Basic на вкладці розробник (розробник) або комбінацію клавіш інший+F11. Потім через меню вставляємо новий порожній модуль Вставка – модуль і скопіюйте туди наступний код:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'назва аркуша, де відображатиметься результуюча зведена таблиця ResultSheetName = "Pivot" 'масив аркуша імена з вихідними таблицями SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'формуємо кеш для таблиць з аркушів із SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With 'повторно створити аркуш для відображення отриманої зведеної таблиці При помилці Відновити наступне Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivot. Name = ResultSheetName 'відобразити згенерований підсумок кешу на цьому аркуші Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range ("A3"). Виберіть End With End Sub    

Потім готовий макрос можна запустити за допомогою комбінації клавіш інший+F8 або кнопку Макроси на вкладці розробник (Розробник — Макроси).

Мінуси цього підходу:

• Дані не оновлюються, оскільки кеш не має зв’язку з вихідними таблицями. Якщо ви змінюєте вихідні дані, вам потрібно знову запустити макрос і знову створити зведення.
• При зміні кількості аркушів необхідно відредагувати код макросу (масив Назви аркушів).
  

Але в підсумку ми отримуємо справжню повноцінну зведену таблицю, побудовану на декількох діапазонах з різних аркушів:

Вуаля!

Технічна примітка: якщо під час запуску макросу ви отримуєте помилку на зразок «Постачальник не зареєстрований», то, швидше за все, у вас 64-розрядна версія Excel або встановлена ​​неповна версія Office (немає доступу). Щоб виправити ситуацію, замініть фрагмент у коді макросу:

	 Постачальник=Microsoft.Jet.OLEDB.4.0;  

щоб:

	Постачальник=Microsoft.ACE.OLEDB.12.0;  

Завантажте та встановіть безкоштовний механізм обробки даних із Access із веб-сайту Microsoft – Microsoft Access Database Engine 2010 Redistributable

Спосіб 3: Консолідація майстра зведеної таблиці зі старих версій Excel

Цей метод трохи застарів, але все ж варто згадати. Формально кажучи, у всіх версіях до 2003 року включно у майстрі зведених таблиць була опція «побудувати зведену таблицю для кількох діапазонів консолідації». Однак побудований таким чином звіт, на жаль, буде лише жалюгідною подобою справжнього повноцінного зведення і не підтримує багатьох «фішок» звичайних зведених таблиць:

У такому зведенні немає заголовків стовпців у списку полів, немає налаштування гнучкої структури, обмежений набір використовуваних функцій, і, в цілому, все це не дуже схоже на зведену таблицю. Можливо, тому, починаючи з 2007 року, Microsoft прибрала цю функцію зі стандартного діалогу при створенні звітів зведеної таблиці. Тепер ця функція доступна лише за допомогою спеціальної кнопки Майстер зведених таблиць(Майстер зведених таблиць), який за бажанням можна додати на панель швидкого доступу через Файл – Параметри – Налаштувати панель швидкого доступу – Усі команди (Файл — Параметри — Налаштувати панель швидкого доступу — Усі команди):

Після натискання на додану кнопку необхідно на першому кроці майстра вибрати відповідний параметр:

А потім в наступному вікні виділяємо по черзі кожен діапазон і додаємо його в загальний список:

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

• Створення звітів за допомогою зведених таблиць
• Налаштування обчислень у зведених таблицях
• Що таке макроси, як ними користуватися, куди копіювати код VBA тощо.
• Збір даних з кількох аркушів на один (надбудова PLEX)