Как объединить данные из разных таблиц с помощью PowerPivot (без ВПР)
Вставка
- Опубліковано 28 січ 2016
- Работа в Excel с большими объемами данных сопряжена с подвисанием файлов. Особенно когда данные из одних таблиц "перекачиваются" в другие с помощью формул типа ВПР. Да и само понятие БОЛЬШИХ ОБЪЕМОВ ДАННЫХ ограничивается 1 048 576 срок листа Excel. Хотя ограничение начинается уже на 300 тыс строк - файл виснет.
Проблема легко решается современными инструментами Excel. Один из них - PowerPivot, как раз та среда, в которой могут храниться данные, превышающие несколько миллионов строк. И где без потери производительности могут объединяться таблицы.
Присоединяйтесь к нам в социальных сетях и на сайте:
- Статьи и примеры файлов Power BI и Excel www.finalytics.pro/inform/
- Вконтакте finalytics
- Facebook / finalytics
- Telegram t.me/finalyticspro
- Записаться на курсы www.finalytics.pro/seminar/
Спасибо. И полезно,и понятно , интересно-что немаловажно
Спасибо, интересный урок!
Большое спасибо!
Спасибо, хороший урок! Где можно скачать таблицу для тренировки?
Про PowerPivot очень мало информации на русском языке, спасибо за Ваши видео - доступно и информативно, очень помогает в работе.)
+Надежда Рогачева, рад, что информация полезная.
Жаль что так мало можно уместить в один ролик )
Станислав, вы очень большое дело делаете! Действительно, на русском языке вы и Щербаков Дмитрий только делаете качественные понятные и доступные материалы. На английском пытаюсь читать книги, но тяжеловато идёт. Думали ли о самостоятельном написании или в соавторстве книги по Power Excel?
Станислав, спасибо! У меня хроническая проблема: связи между таблицами строятся без проблем, а вот в сводной таблице при попытке увидеть данные, совмещённые их связанных таблиц, в колонке результатов - в каждой строке размножено одно число.
Отличный урок. Только у меня не получается немного( делаю параллельно с уроком в экселе, но даже после создания связей, после добавления чисел в блок значения, в сводной таблице выдаются одинаковые цифры, как будто связи нет. Уже все перепробовал.
Спасибо большое за это видео!
Если можно, несколько вопросов:
1) сохраняется ли какая-то взаимосвязь между данными, которые попали в т.н. "модель данных" с тем диапазоном, который послужил источником для них? (в примере Вы выделили диапазон, который потом назвали "продажи" и добавили его в модель данных, если теперь нужно дополнить, причем дополнить прямо на листе данные про продажам новыми записями, то как это можно сделать, чтобы они "подтянулись" и в модель данных")
2) Есть какие-то требования для создания моделей данных? Будет ли работать модель данных Pover Pivot если ее создать в Excel 2010, а потом пытаться открывать и работать в Excel 2013? Какие условия должны быть соблюдены, чтобы все корректно работало? Сильно ли функционал Pover Pivot экселя 2010 отличается от функционала Pover Pivot экселя 2013 и выше?
+Konstantin Zhizhilkin, на здоровье )
1. Привязывать нужно к форматированной таблице. И тогда данные будут добавляться в нее автоматически.
2. Модель созданная в 2010 будет (после небольшой автопаузы) открываться в 2013. А вот модель данных, созданная в 2013 в 10-ке не откроется.
скажите, в каком итоговом формате получается отчет, чтобы его посмотреть? получается руководителю надо будет на свой пк/ноут/планшет установить power BI? или это на выходе будет какой то удобоваримый формат или экспорт во всем известный Power point?
Добрый день!
Нужно установить Power BI Mobile, доступный бесплатно в эпл-стори, гугл-плэй и магазине виндоус (простите мой английский ))) )
Здравствуйте! Подскажите, можно ли связать в одну сводную таблицу столбцы с разных листов или разных именованных диапазонов? На первом листе столбец дата, на втором продажи, а в третьем город и всё это соединить в одну сводную таблицу.
Можно - соберите все таблицы в единый источник данных с помощью power query.
Да, только это идеальная картина: что существует справочник с уникальными значениями. А не с уникальными связь не построишь!
На практике обычно имеем две выгрузки - и в основную надо подтянуть из дополнительной. LOOKUPVALUE наиболее часто используемая функция.
Добрый день, Станислав!
Что обозначает данная ошибка не установленных связей?
"Исключение из HRESULT: 0x800A03EC
----------------------------
----------------------------
Команда отменена. Нажмите клавишу F1, чтобы открыть раздел справки "Окно Excel: список полей PowerPivot" и получить дополнительные сведения.
============================
Стек вызовов:
============================
Server stack trace:
Exception rethrown at [0]:
в System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
в System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
в Microsoft.Office.Interop.Excel.CubeField.set_Orientation(XlPivotFieldOrientation RHS)
в Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToFields(String cubeFieldName, XlPivotFieldOrientation pivotOrientation, Int32 positionIndex)
----------------------------
в Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToFields(String cubeFieldName, XlPivotFieldOrientation pivotOrientation, Int32 positionIndex)
в Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
----------------------------
в Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
в Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.AddToFieldListInternal(PivotArea location, IGeminiColumn column, Int32 positionIndex)
в Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.MoveField(IGeminiColumn column, PivotArea source, PivotArea target, Int32 positionIndex)
в Microsoft.AnalysisServices.XLHost.Addin.FieldListControl.MoveFieldInternal(IGeminiColumn field, PivotArea sourceArea, PivotArea targetArea, Int32 targetIndex)"
Спасибо.
Станислав, добрый день! Спасибо за такое познавательное видео, действительно информации на русском языке очень мало по данному приложению. Пользуясь случаем, хотелось бы спросить у вас, почему иногда при попытке добавить данные в модель данных при создании сводной таблицы это поле неактивно, и при переходе в Power Pivot вся его панель также неактивна. Диапазон был создан на основе выгрузки с 1С, но впоследствии неоднократно редактировался и копировался на новые листы, тем не менее это происходит. Также я заметил подобный эффект для выгрузки с базы авторской программы. Возможно ли такое, что перед добавлением таких данных в модель, необходимо каким-то образом дополнительно их редактировать? Заранее спасибо за ответ!
+Сергей, спасибо за отзыв.
Что касается вашего вопроса:
Да, данные для загрузки должны иметь корректный вид. Но если вид "не такой" Excel выдаст сообщение об этом. Сами команды на вкладках будут активными. Поэтому, ваша проблема скорее всего связана с форматом файла. Из 1С и некоторых других программ отчеты выгружаются в старом формате - *.xls
Попробуйте сохранить в *.xlsx, а если у вас там есть макросы в *.xlsm
+Станислав Салостей. Да! Сработало! Поменял формат и все собрал. Еще раз спасибо!
Вот и отлично )
Здравствуйте Станислав,
У меня проблема. При попытке импортирования данных (из файла excel), после загрузки выдает следующую ошибку:
"Нам не удалось получить данные из модели данных. Мы получили следующее сообщение об ошибке:
Индекс за пределами диапазона. Индекс должен быть положительным числом, а его размер не должен превышать размер коллекции.
Имя параметра: index"
Вопрос: как быть? в чем может проблема?
Выучить простейшие циклы на VBA- и нет проблем! Лично я так и не поняла зачем нужно было делать power pivot. На ВБА все проще.
У VBA, как ни странно, есть свои ограничения по использованию:
1. Процедуру не пишут, если ее написание по трудозатратам сопоставимо с использованием встроенного функционала (нерационально как по времени, так по ресурсам Excel).
2. Не каждый рядовой сотрудник знает VBA и, уж тем более, может использовать его функционал.
3. Не в каждой компании (организации, "конторе" и т.д.) разрешено использовать файлы с поддержкой макросов *.xlsm
4. Часто написанные процедуры тестируют только под "свои нужды", т.е. корректная работа этого макроса (например, в книге с немного другими исходными данными) остается под большим вопросом.
Можно очень долго перечислять. Все сугубо индивидуально и зависит от конкретной ситуации.
P.S. Построение реляционной модели данных в Excel - это очень сильная вещь, т.к. основное назначение Excel - расчеты. Может будет сказано достаточно грубо, но, по сути, в excel реализовали часть функционала Access.
Добрый день. Станислав, пытался объединить два запроса PQ через "append" с формированием связи и добавлением в модель, а данные теряются. Таблица 1 на 1,2 млн записей , Таблица 2 на 80 тыс а в результате получается Таблица 3 на 900 тысяч... На англ форумах пишут что можно попробовать через новый запрос DAX вида: Table = UNION('BASE 1','BASE 2'), но я что-то совершенно запутался как его вставить в запрос PQ. Ткните, пожалуйста, где я могу найти ответ как решить вопрос с потерей данных. Спасибо!
Не рассказал, как связать таблицы на разных листах и книгах (структура всех таблиц одинаковая). Мне нужны все таблицы на разных книгах объединить в одну сводную
Через power query. В других видео можно найти.
В 2010 оФИСЕ 32 bit ЭТО ВСЕ НЕ РАБОТАЕТ ! ! ! ! ! ! Я целый день на это потратил !!!!!!! Связь в power Pivot создается а в excel нет . При вопросе создать ли мне связь (в excel) жму создать - ответ связь не обнаружена ! ! ! ! В окне excel 2 таблицы все поля есть но принцип "ВПР" не работает !!!!!! Связи нет и все тут на вкладке конструктор нет кнопки создать связь!!!!! Подскажите что делать.
изначально кнопка pivot table не доступна ни в excel ни в окне power pivot . В excel Доступна только кнопка Создание связанной таблицы через неё захожу в окне power pivot и только тогда там становиться доступна кнопка pivot table. и Там только добавляю вторую таблицу . Может поэтому , тогда вопрос почему изначально не активна кнопка pivot table ни в excel ни в окне power pivot????
В Excel 2010 связи тоже создаются, хотя сам Power Pivot может работать нестабильно. Лучше всего создавать и управлять связями в самом Power Pivot-е.
Разобрался заработало с горем пополам, но есть одно но....
создал Запрос в Power Query собирает из нескольких книг в одну однотипную таблицу. (строк чуть больше 11 000)
Потом на основе этого запроса несколько раз создавал следующие запросы.
Получилось уровней 5 вложенности.
Там и слияние типа join и добавление новых столбцов и расчеты по новым столбцам и опять слияние на основе этих данных.
Сами запросы к примеру обновляются хорошо .
Но потом на основе последнего запроса создал Pivot Table.
Ну это просто что-то ! ! ! ! книга еще при создании запроса тормозила жутко один раз чуть не слетела кое как восстановил.
С горем пополам сводная заработала но с такими тормозами что капец ! ! !
Так что даже и не знаю.
Хотя Логика у меня тоже неверная несколько уровней вложенностей когда один запрос ссылается на предидущий тоже не верная..
Надо что то с логикой менять.
Но надстройка Power Pivot однозначно хуже Power Query ! ! !
То ли дело в уровнях вложенности то ли в количестве строк.
А может В Excel 2010, Power Pivot и Power Query скачивал последние версии.
А вот Power Query радует очень .
Это ж надо было додуматься sql'евские запросы в Excel внедрить ! ! ! ! ! !
И как я раньше эту надстройку не заметил ! ! !
Спасибо что ответили....... ))))
Что-то не срабатывает связь
Ryhor Salauyou формат данных?