Страницы: 1
RSS
PoverPivot или сводная на основе нескольких листов, структура листов может отличаться
 
Суть такая: есть книга Excel, в ней помесячные листы предположим, их всегда 12.
На листах таблицы, начинаются с A1, заголовки полей в первом столбце.
Основная проблема в том, что некоторые поля на некоторых листах могут отсутствовать
Те поля, которые в итоге нужно анализировать - есть всегда, но могут быть расположены в разном порядке.
Записей в таблицах может быть >100.000, т.е. суммарное количество строк со всех листов может быть больше лимита листа.
Приложил примерный файлик - в нем зеленые ярлычки листов - таблицы с отсутствующими полями, а оранжевые - таблицы с переставленными полями.
Необходимые поля отмечены желтым.

Я решил эту задачу макросом, который бегает по листам и нужным полям и вытягивает нужную информацию, но это медленное и жутко негибкое решение, так как задачи анализа могут модифицироваться.
По ряду причин Access не подходит, необходимо решение в Excel.

Стал смотреть в сторону PowerPivot - раньше с ней не работал, но вроде бы она похожие задачи может решать. Пока еще мало что в нем понял, увы.
Пока наиболее приемлемое решение, которое нашел - это выгрузка в csv, объединение в один большой csv-файл и затем загрузка его в PowerPivot как источника данных. Но тут опять же нужно иметь идентичную структуру, удалять заголовки полей во всех таблицах кроме первой и прочие танцы с бубнами.
Может ли PowerPivot взять в качестве источника данных таблицы с похожей структурой, и если "да" - как его заставить это сделать? Либо единственный способ - это приводить структуру в единый вид?
Изменено: Максим Зеленский - 22.10.2014 14:06:20
F1 творит чудеса
 
А через запросы SQL не пробовали?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Попробуйте такой вариант...
Excel 2013
 
Есть надстройка PowerQuery, которая, вроде бы, решает похожие задачи. Поэкспериментируйте. Вдруг поможет.
OfficeProPlus 365x64
Win64forWorkstation
 
Спасибо всем, попробую разобраться.
The_Prist, с SQL у меня сложные отношения. Я когда вижу уже написанный запрос, то, теоретически, могу его подправить - заменить имена полей там )) более глубоко как-то не доводилось залезать, тем более, писать с нуля. Я так понимаю, что при работе с PowerPivot, что при работе с PowerQuery все равно без него не обойтись.
Изменено: Максим Зеленский - 22.10.2014 14:05:24
F1 творит чудеса
 
Можно SQL запросом так. А вообще с помощью надстройки Активные таблицы можно делать что угодно.
Код
Public Sub RefreshData()
'Created using add-in ActiveTables
Dim strConnection As String
Dim strSQL As String
strConnection = IIf(Val(Application.Version) < 12, "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=3';", "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=3';")
strSQL = "select 1 as Mes,* from [Jan$] union all select 2 as Mes,* from [Feb$] union all select 3 as Mes,* from [Mar$]"
With ThisWorkbook.Sheets("Itog")
 .UsedRange.Clear
 With .QueryTables.Add(strConnection, .Range("A1"), strSQL)
 .Refresh False
 .Delete
 End With
End With
End Sub
Excel + SQL = Activetables
 
только лучше создать ADO шный Recordset и впихнуть его сразу в сводную. Примеры на форуме есть от KL, R Dmitry  и др.
------
Если источник данных относительно статичен, то я бы все же выгружал его куда ни будь, потому что
У PP с OLEDB(excel)  есть странности. Они не очень дружат. конект к текущей книге проходит нормально, а вот данных он не видит (excel 2010).
-
Я бы все же остановился на варианте выгрузки данных во внешний источник и загрузке информации в PP
 
Rustem, спасибо, первая ссылка очень интересная. Я так понимаю, сделано именно на ее основе?
Вторую видел, но не разобрался поначалу. сейчас просмотрел комментарии под статьей, становится немного понятнее.

vector1, так же большое спасибо, я думал, эта надстройка только для 2013, но вроде бы нет. Попробую установить и замучить
Цитата
B.Key пишет: только лучше создать ADO шный Recordset и впихнуть его сразу в сводную
да, именно это и желательно. Не создавать лист с более чем миллионом строк, часть из которых может обрезаться. Но сразу запихивать как источник данных для сводной таблицы или PowerPivot

Спасибо еще раз всем за помощь и комментарии
F1 творит чудеса
 
пример не нашел, на ваял свой.
 
? разобрались
 
B.Key, извините за молчание. Еще нет, пока не успел внимательно посмотреть. Открыл, запустил, получил сводную - всё работает супер. В детали макроса еще не влезал - я сейчас тут больше с телефона :( но вопросы есть некоторые. Чуть позже спрошу обязательно.
Еще раз спасибо!
F1 творит чудеса
Страницы: 1
Наверх