Сводная таблица из нескольких таблиц в excel 2010 как сделать

Сводная таблица из нескольких таблиц в excel 2010 как сделать
Сводная таблица из нескольких таблиц в excel 2010 как сделать
Сводная таблица из нескольких таблиц в excel 2010 как сделать
Сводная таблица из нескольких таблиц в excel 2010 как сделать

Думаю, что большинство профессиональных пользователей согласятся со мной в том, что сводные таблицы - один из самых замечательных инструментов в Excel. Но до сих пор, к сожалению, ни одна из версий Excel до 2010-й включительно не умеет делать такой простой и нужной вещи как построение сводной по нескольким исходным диапазонам данных, находящимся, например, на разных листах:

pivot_multi_sheets1.gif

Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации". Однако, отчет, построенный таким образом был лишь жалким подобием настоящей полноценной сводной и не имел большей части возможностей настройки обычных сводных таблиц:

pivot_multi_sheets2.gif

В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому в Excel 2007/2010 эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц и она доступна только через настраиваемую кнопку "Мастер сводных таблиц" (Pivot Table Wizard), которую при желании можно добавить на панель быстрого доступа.

Тем не менее, выход есть всегда. Одно из решений представлено вот таким макросом, который надо добавить в новый модуль в редакторе Visual Basic (жмем Alt+F11 и затем в меню Insert - Module):

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 = "Сводная" 'массив имен листов с исходными таблицами SheetsNames = Array("Альфа", "Бета", "Гамма", "Дельта") 'формируем кэш по таблицам с листов из 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 'создаем заново лист для вывода результирующей сводной таблицы On Error Resume Next 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").Select End With End Sub

Макрос создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую базу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.

Минусы такого подхода:

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

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

pivot_multi_sheets3.gif

Вуаля!

Ссылки по теме

 

Сводная таблица из нескольких таблиц в excel 2010 как сделать Сводная таблица из нескольких таблиц в excel 2010 как сделать Сводная таблица из нескольких таблиц в excel 2010 как сделать Сводная таблица из нескольких таблиц в excel 2010 как сделать Сводная таблица из нескольких таблиц в excel 2010 как сделать Сводная таблица из нескольких таблиц в excel 2010 как сделать Сводная таблица из нескольких таблиц в excel 2010 как сделать

Тоже читают:



Топ гир дом на колесах своими руками

Как сделать пол в ванне в деревянном доме

Красочные оформления и поздравления

Поздравления с днем рождения тебе сегодня

Как сделать розовую воду в домашних условиях из лепестков