Як побудувати зведену таблицю за кількома масивів (листам)

Типова задача при обробці інформації отриманої з різних джерел. Типове рішення - взяти і звести всі таблиці в одну. Але що робити, коли таблиць багато (наприклад, 20), або звести їх в одну немає можливості, на аркуші просто не вистачає рядків (всі таблиці в сумі дають більше 1 100 000 рядків)?

Однак рішення існує! І воно не дуже складне.

Для вирішення цього завдання нам знадобитися надбудова YOXCEL.

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

• Кількість стовпців у всіх таблицях має бути однаково;
• Крім таблиць на аркушах не повинно бути ніякої інформації.

Починаємо маніпуляції. Переходимо в головному меню у вкладку YOXCEL і натискаємо кнопку "Таблиці", у випадаючому меню вибираємо команду "Об'єднати таблиці":

У діалоговому вікні виділяємо листи з таблицями, які необхідно об'єднати і натискаємо "ОК":

Програма сформує запит - об'єднає таблиці і виведе інформаційне повідомлення:

Залишилось зовсім небагато. Переходимо в головному меню у вкладку "Дані" в розділі "Отримання зовнішніх даних" натискаємо кнопку "Існуючі підключення":

У діалоговому вікні вибираємо "Підключення в цій книзі" - "Запит з Excel Files" і натискаємо "Відкрити":

У діалоговому вікні встановлюємо перемикачі в положення "Звіт зведеної таблиці" і "Новий лист", натискаємо "ОК":

Програма створить в книзі новий лист на який виведе макет зведеної таблиці. Зверніть увагу - програма створить в сформованої таблиці новий стовпець з назвами листів з яких були отримані дані:

Добудуйте зведену таблицю до необхідного стану, додайте в будь-яку з пов'язаних таблиць нову сходинку, поновіть зведену таблицю - в ній з'являться додані дані.

Щоб оновлення зведеної таблиці відбувалося автоматично вставте в модуль кожного листа містить таблиці наступний код (Як вставляти макроси?):

• Якщо ви перемістили файл в іншу папку або відправили файл колезі по електронній пошті - необхідно заново зв'язати таблиці (в запиті прописується абсолютний шлях до файлу).

Щоб запит працював не залежно від того в якій папці лежить файл вставте в модуль "ЕтаКніга" наступний код:

Даний макрос при відкритті книги буде визначати поточний шлях до файлу та змінювати шлях до файлу в запиті.

Файли для скачування:

Щоб оцінити всю красу - вийміть файл з архіву і при завантаженні файлу включите макроси.

Можливі помилки при використанні цього методу:

• У таблицях однакову кількість стовпців, але при спробі використовувати запит отримуємо повідомлення: "У таблицях або запитах, обраних в запиті на об'єднання, не збігається число стовпців." Причина: в одному або декількох аркушах з таблицями є порожні стовпці (в них раніше були дані) і MS Excel вважає їх стовпцями таблиць. Рішення: Виділити і видалити всі порожні стовпці на аркушах з таблицями.
• Всі стовпчики видалили, але помилка залишилася. Причина: файл збережений на мережевому диску або відкритий з поштової програми. Рішення: збережіть файл у себе на локальному комп'ютері, виконайте запит, вставте в нього макрос який змінює шлях в запиті і викладіть назад в мережу (якщо потрібно).
• В отриманому запиті в деяких осередках пропадають дані. Причина: в ваших таблицях зустрічаються стовпці, які одночасно містяться і числові і текстові значення. MS Excel вважає, що ці стовпці повинні містити тільки числа і не виводить текст. Рішення: перетворіть всі числові значення в цих стовпцях у всіх таблицях в текстові. Оновлення запит - текстові дані з'являться.

Привіт, вставила в книгу Ваші коди макросів для відновлення зведеної таблиці, і у мене виникли проблеми. Перша: таблиця не оновлювалася (через що, не знаю). Я додала зайву рядок для перевірки в вихідну таблицю, але в зведеної вона не відобразилася, тому я залізла в параметри Excel і включила надбудову "VBA для помічника по Інтернету" (випадково, звичайно, і не знаю, як її відключити).
І ось тоді виникла друга проблема. Тепер таблиця оновлюється при кожній дії в даній книзі (фільтр по даті, видалення, вставка рядків, стовпців, комірок і т.д.) Зараз це займає всього 2-3 хвилини, але боюся в майбутньому це буде відбуватися довше через збільшення обсягу даних. Можете допомогти?