Power pivot excel как обновить
Обновлено: 07.07.2024
Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.
Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да — есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.
А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных
На написание данной статьи сподвигло то что в рунете по этому инструменту не много какой либо детальной информации о конкретных приемах работы, все больше о звездах, поэтому решил, изучая этот инструмент, написать данный обзор.
Собственно, постановка задачи (на обезличенном примере) следующая:
В исходных данных csv файла:
Есть торговые точки, детализированные до строк накладных, при этом допускается для точек с одинаковым наименованием иметь разные адреса только в том случае если они расположены в разных городах, но в исходном массиве данных есть точки, у которых попадаются разные адреса в одном и том же городе при том, что названия точек одинаковые (имя торговой точки уникально, т.е. это единица сети или отдельно стоящая точка). Как частный случай в агрегированном виде:
Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:
• Детализация данных до строк накладной
• Количество записей в несколько миллионов строк
• Отсутствие sql инструментария (К примеру: Access — не в комплекте)
Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.
Задача: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Данное поле требуется для быстрого нахождения всех имен торговых точек в городе, где адресов больше 1.
Думаю, удобнее всего решать и рассказывать итерационно, с допущением что у нас знания по DAX в зачаточном уровне.
Поэтому предлагаю пока оторваться от задачи и рассмотреть некоторые базовые аспекты.
Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:
=ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)
Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
Теперь добавим вычисляемое поле для цены за штуку без НДС:
=ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)
Теперь для сравнения добавим в меру расчет средней цены за штуку:
Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)
Как видно из формулы, мера работает со столбцом исходных данных по вертикали, поэтому она всегда должна содержать в себе какую то работающую с множеством функцию (Сумму, среднюю, дисперсию и т.д.)
При возврате в сводную таблицу Excel это выглядит так:
Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.
Отсюда делаем вывод, что вычисляемое поле «Цена за штуку без НДС» является вспомогательным инструментом для расчета меры «Средняя цена за штуку без НДС» и дабы не смущать пользователя этим полем мы скроем его из списка клиентских средств, оставив меру средней цены.
Еще одно отличие меры от столбца – она позволяет добавить визуализацию:
К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.
К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])
В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):
Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:
Cильнее чем при коэффициенте 15%:
Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках PowerPivot.
Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.
Вот первый пример сравнения подходов оконных функций MS SQL Server и DAX:
Понятно, что в рамках сводных таблиц это делается буквально в 2 клика мышкой не касаясь клавиатуры, но для понимания попробуем это непосредственно в PowerPivot с применением формул.
На sql я бы это написал так (за огрехи не пинать, ибо Word синтаксис SQL Server не проверяет):
Здесь, как можно заметить окно открывается через все записи датасэта, попробуем аналогичную вещь в PowerPivot:
=[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL('Таблица1'))
Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали ( в рамках одной записи) а меры – по вертикали ( в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод CALCULATE. И если ширину окна в SQL мы отрегулировали через Over() то здесь мы сделали это через All().
Попробуем теперь, обладая данным навыком, сделать с нашими данными что –нибудь полезное, например, вспомнив что показатель разброса цен вокруг средних варьировался в широком диапазоне, попробуем выделить статистические выбросы цен через правило 3-х сигм.
Оконные функции на sql будут смотреться так:
А вот то же самое в DAX:
=if(ABS([Поле_Цена за штуку без НДС]-CALCULATE(AVERAGE([Поле_Цена за штуку без НДС]);ALL('Таблица1')))>(3*CALCULATE(STDEV.P([Поле_Цена за штуку без НДС]);all('Таблица1')));1;0)
Как видите, цена несколько высоковата при средней арифметической 40,03 руб.
Шаг 3. Сужаем окна.
Попробуем теперь посчитать в вычисляемом поле каждой записи общее количество записей в рамках того города, к которому принадлежит и данная запись.
На MS sql Server оконные функции будут выглядеть так:
В DAX:
=CALCULATE(COUNTROWS('Таблица1');ALLEXCEPT('Таблица1';'Таблица1'[Город]))
Обратите внимание на разницу в отображении данных в таблице, я специально бросил адреса в область мер что бы посчитать их количество и сравнить с новым полем которое вывел в заголовок строк после имени торговой точки.
Отчетлива видна разница: если обычный расчет количества адресов идет для каждой точки в городе и потом только выводит промежуточный итог для агрегата «Город» то использование оконных функций позволяет присвоить каждой атомарной записи значение любого агрегата, либо использовать его в каких-то промежуточных расчетах вычисляемого поля ( как было показано выше).
Возвращаемся к исходной задаче
Итак, напомню, исходная постановка задачи: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Не забываем, что датасэт у нас детализирован до строк накладной, поэтому перед подсчетом адресов внутри окна их необходимо сгруппировать.
Запрос на SQL Server:
Теперь нам ничего не мешает это сделать и в DAX:
В итоге у нас появилась возможность отобрать подозрительные записи, где на одну и ту же точку в одном городе приходится более 1 адреса.
Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.
"Эта книга содержит модель данных Power Pivot, созданную в предыдущей версии надстройки Power Pivot. Вам нужно обновить эту модель данных с помощью Power Pivot в Microsoft Excel 2013.
Перед обновлением ознакомьтесь с описанием известных проблем при обновлении.
Начните с сохранения копии файла на случай, если вам впоследствии понадобится его предыдущая версия. После обновления книга будет работать только в Excel 2013 или более поздней. Узнайте подробнее, чем отличается обновленная модель.
Нажмите Power Pivot > Управление, чтобы начать обновление.
Появится уведомление о необходимости обновления.
Обновите модель данных. После этого вы больше не сможете работать с этой книгой в предыдущей версии Power Pivot.
Нажмите ОК, чтобы обновить книгу.
Сохраните и перезагрузите книгу. Этот шаг фиксирует изменения, внесенные при обновлении. После сохранения отменить изменения нельзя. Перезагрузка займет всего несколько секунд в зависимости от размера и месторасположения книги.
Нажмите Да, чтобы перезагрузить книгу.
В этой статье
Известные проблемы
Удаление или перекомпоновка таблиц, связь которых с исходной таблицей в Excel утрачена
Устаревшие и потерянные таблицы не обновляются вместе с остальной моделью, из-за чего данные удаляются или недоступны. Чтобы избежать этой проблемы, необходимо, чтобы существующие связанные таблицы были связаны с существующей таблицей в Excel.
В Power Pivot для Excel 2010 щелкните связанную таблицу в окне Power Pivot.
Выберите на ленте вкладку Связанная таблица.
Убедитесь, что параметр Таблица Excel указывает на существующую таблицу. Если таблица в Excel была переименована, нажмите стрелку вниз рядом с параметром Таблица Excel и выберите таблицу под новым именем.
Нажмите Перейти к таблице Excel и убедитесь, что связанная таблица подключена к исходной таблице в Excel.
Если параметр Таблица Excel пуст:
удалите связанную таблицу из модели данных, если она больше не используется,
После этого все готово к обновлению книги, если только не возникает другая проблема.
Удаление таблиц запросов детализации
Эта проблема возникает только при обновлении моделей данных Power Pivot, основанных на кубах Analysis Services, которые поддерживают детализацию. Если книга включает таблицы запросов детализации, обновление пройдет успешно, но выборе любого поля в сводной таблице будет возвращаться ошибка обновления.
В Power Pivot для Excel 2010 найдите в книге все таблицы запросов детализации.
Таблица запросов детализации создается в книге, если щелкнуть правой кнопкой мыши меру и выбрать Показать подробные сведения. Результирующая таблица представлена как отдельный лист книги. Первая строка начинается со слов: "Возвращены данные для…".
Удалите все листы таблиц запросов детализации. Их можно создать снова после обновления.
Повторно создайте таблицы запросов детализации, если это необходимо. Щелкните правой кнопкой мыши вычисляемое поле сводной таблицы и выберите Показать подробные сведения. Таблица запросов детализации будет создана на отдельном листе.
Удаление защиты паролем
"Не удалось обновить модель данных книги".
Чтобы избежать этой проблемы, удалите пароль перед обновлением.
Откройте книгу в Excel 2013 или Excel 2016.
Выберите Файл > Сведения > Защита книги > Зашифровать с использованием пароля.
Удалите звездочки и нажмите ОК.
Нажмите Power Pivot > Управление, чтобы перезапустить обновление.
После обновления снова защитите книгу паролем.
Снятие ограничений доступа
"Не удалось обновить модель данных книги".
Чтобы избежать этой проблемы, снимите все ограничения доступа перед обновлением.
Откройте книгу в Excel 2013 или Excel 2016.
Выберите Файл > Сведения > Защита книги > Ограничить доступ.
Установите флажок Неограниченный доступ, чтобы отменить все ограничения доступа.
Сохраните книгу в Excel.
Нажмите Power Pivot > Управление, чтобы перезапустить обновление.
После обновления снова установите ограничения доступа для книги.
В Excel 2013 или Excel 2016 2013 перейдите в отчет или с полями, которые возникли проблемы при обновлении.
Удалите эти поля из сводной таблицы или отчета. Они по-прежнему будут отображаться в списке полей, просто их не будет в отчете.
Сохраните и закройте книгу.
Откройте книгу снова.
Нажмите Power Pivot > Управление, чтобы перезапустить обновление.
После успешного обновления верните поля в сводную таблицу или отчет.
Не удается обновить подключение к источнику данных в обновленной книге
"Excel не удалось получить необходимые сведения об этом кубе. Возможно, куб был реорганизованы или изменен на сервере. Обратитесь к администратору куба OLAP и при необходимости настройте новый источник данных для подключения к кубу".
Когда вы пытаетесь изменить существующее подключение, то видите имя модели данных, но не можете изменить источник данных.
Проблема
Подключение к источнику данных больше не является допустимым, и его необходимо обновить, но его нельзя обновить в Excel 2013 или Excel 2016.
Решение
Откройте книгу в Excel 2010 с надстройкой Power Pivot.
Щелкните Power Pivot > Модель данных > Управление, чтобы открыть окно Power Pivot.
Выберите подключение к текущей базе данных и нажмите кнопку Изменить.
В диалоговом окне Изменение соединения нажмите кнопку Обзор, чтобы выбрать другую базу данных того же типа с другим именем или в другом расположении.
Сохраните и закройте книгу.
Откройте книгу в Excel 2013 или Excel 2016.
Завершение частичного обновления
В большинстве случаев обновление книги должно проходить без изменений, но иногда для завершения работы требуется внести дополнительные изменения. В этом разделе вы узнаете, как реагировать на определенные ошибки, указывающие на частичное или неполное обновление. Книга обновлена, но отсутствует определенная функциональность.
Отсутствуют изображения ключевых показателей эффективности (KPI)
Если отсутствуют изображения ключевых показателей эффективности (цветные значки или значки с рисунками), удалите KPI из области Значения списка полей, после чего добавьте их снова.
Ошибка обновления вычислений DAX: Не удалось обновить книгу надлежащим образом.
"Не удалось полностью обновить книгу. После сохранения некоторые из функций обработки данных Excel не работают. Подробнее см. по адресу http://go.microsoft.com/fwlink/?LinkID=259419."
Мы получили такую ошибку: "Не удалось выполнить запрос ко внутренней структуре, обеспечивающей ‘<таблица>[<имя столбца>]’, поскольку она зависит от столбца, связи или меры, которые не обработаны. Обновите или повторно вычислите модель".
Примечание: Если вам будет предложено сохранить сводную таблицу в Power Pivot, закрыть и повторно открыть книгу, нажмите кнопку Да.
Найдите следующие известные проблемы совместимости DAX:
Обозначение NULL пустыми двойными кавычками (“”) больше не допускается. Замените двойные кавычки нулями.
Попытка сравнения несовместимых типов в функциях LOOKUPVALUE, CONTAINS или SWITCH. Необходимо либо указать [Тип] в аргументе, либо гарантированно обеспечить, что в операции будут участвовать только числовые, строковые или логические значения. Для преобразования типов значений можно использовать функции VALUE или FORMAT.
Функции сводной таблицы, ссылающиеся на "Power Pivot Data", больше не работают
Если в функциях Excel для работы с аналитическими кубами определена связь с моделью Power Pivot из предыдущего выпуска, необходимо вручную указать новое имя модели ("ThisWorkbookDataModel").
=CUBEVALUE("Power Pivot Data",[Measures].[TotalSales])
В Excel для каждого листа выберите Формулы > Показать формулы, чтобы на всех листах книги отображались используемые вычисления.
В функциях куба Excel, которые определяют связь, найдите значение "Power Pivot Data".
Замените значение "Power Pivot Data" на "ThisWorkbookDataModel".
Утрата данных: данные модели удаляются при обновлении
"Не удалось обновить следующие связанные таблицы: <имятаблицы>. Эти таблицы были удалены из модели."
В особых случаях данные удаляются из модели во время обновления. Это происходит, если в модели данных имеются потерянные связанные таблицы, для которых не получается найти исходную таблицу в той же книге, либо если исходная таблица удалена или переименована.
Обновление проходит успешно, но связанные таблицы удаляются
Связанная таблица — это таблица, которую вы создаете в книге и затем включаете в модель данных, чтобы связать с другими таблицами и добавить вычисления DAX. Связанные таблицы, не связанные ни с одной существующей в книге исходной таблицей, будут удалены при обновлении.
Вот сценарий: вы создали или импортировали таблицу в Excel, добавили ее в качестве связанной таблицы в Power Pivot, где она стала частью модели данных, а затем удалили исходную таблицу, на которой она основана. В модели сохраняется связанная таблица, хотя таблица в Excel не существует.
"Не удалось обновить следующие связанные таблицы: <имятаблицы>. Эти таблицы были удалены из модели."
По возможности отмените обновление и измените модель либо создайте резервную копию, если собираетесь использовать таблицу впоследствии.
На предложение сохранить, закрыть и повторно открыть книгу ответьте Нет.
Закройте файл в Excel 2013 без сохранения.
Откройте книгу в Excel 2010 и устраните проблемы со связанными таблицами.
Связанная таблица больше не нужна? Удалите его из модели или перезапустите обновление в Excel 2013 и позвольте обновить таблицу.
Excel таблица была переименована, а связанная таблица не была? Обновите сведения о под Power Pivot, как описано в шаге 1 в верхней части этой страницы.
Связанная таблица должна оставаться, так как она используется в вычислениях или в Excel таблице? Повторно создать таблицу в Excel и обновить ссылку:
скопируйте строки из связанной таблицы в окне Power Pivot;
вставьте строки в книгу Excel;
отформатируйте строки как таблицу;
вернитесь в окно Power Pivot;
на вкладке Связанная таблица нажмите черную стрелку вниз рядом с полем Таблица Excel и выберите только что созданную таблицу;
после внесения изменений откройте файл в Excel 2013;
вновь запустите обновление, нажав Power Pivot > Управление.
После сохранения книги откат к предыдущей версии невозможен. Связанную таблицу придется создавать заново. Подробнее см. в разделе Добавление данных листа в модель данных с помощью связанной таблицы.
Чем отличается обновленная модель
В большинстве случаев обновленная книга идентична предыдущей. Однако в новой версии есть некоторые изменения, о которых стоит упомянуть.
Срезы и описания столбцов больше не отображаются в списке полей
В предыдущих версиях описания столбцов или полей в списке полей в столбце отображались в качестве подскаутых. В Excel 2013 или более поздней не поддерживается список полей. Все описания столбцов, которые существуют в модели, будут игнорироваться в визуализациях данных в Excel.
Срезы поддерживаются, но теперь вы создаете их другим способом. В списке полей нет параметров для создания среза.
После обновления невозможно работать с книгами в предыдущих версиях Excel и Power Pivot
До Excel 2013 было выпущено две версии надстройки Power Pivot. Обе версии предназначены только для Excel 2010. Это:
SQL Server 2008 R2 Power Pivot для Excel;
SQL Server 2012 Power Pivot для Excel 2010.
"Не удалось выполнить инициализацию источника данных."
Эта ошибка возникает, потому что при взаимодействиях с данными, таких как создание среза, формируется запрос к модели данных, а это источник данных сводной таблицы. Так как модель данных недействительна в Excel 2010, попытка взаимодействия с данными приводит к ошибке инициализации их источника.
Предположим, что теперь вы пытаетесь открыть модель данных в Power Pivot окно. Модель не открывается, и возникает следующая ошибка:
"Книга содержит модель данных Excel, созданную в более новой версии Excel. Эту книгу можно открыть в более старой версии Excel, но нельзя будет загрузить или работать с Power Pivot, если программа сосуществует вместе с моделью данных Excel".
Способа выхода из этой ситуации нет, обратной совместимости при обновлении не предусмотрено. Переход на Excel 2013 должен выполняться всем коллективом организации одновременно. Кроме того, может потребоваться временно хранить отдельные версии книг, если ваша организация планирует использовать Excel 2010 и Excel 2013 или Excel 2016 рядом.
Получение данных из 1С в Microsoft Power BI PowerBI
Когда вы загружаете данные из внешнего источника данных в Power Pivot, вы по существу создаете статический снимок этого источника данных во время создания. Power Pivot использует этот статический снимок в своей внутренней модели данных.
С течением времени внешний источник данных может меняться и расти с помощью новых добавленных записей. Однако Power Pivot все еще использует свой снимок, поэтому он не может включать какие-либо изменения в источник данных, пока вы не сделаете другой снимок.
Действие обновления модели данных Power Pivot с помощью другого моментального снимка вашего источника данных называется обновлением данных. Вы можете обновить вручную, или вы можете настроить автоматическое обновление.
Обновление ручных данных Power Pivot
На домашней вкладке окна Power Pivot вы увидите команду Refresh. Нажмите стрелку раскрывающегося списка ниже, чтобы увидеть два варианта: Обновить и Обновить все.
Power Pivot позволяет обновить одну таблицу или все таблицы.
Используйте параметр «Обновить», чтобы обновить активную таблицу Power Pivot. То есть, если вы находитесь на вкладке Dim_Products в Power Pivot, нажатие Refresh выдает внешний SQL Server и запрашивает обновление только для таблицы Dim_Products. Это хорошо работает, когда вам нужно стратегически обновлять только определенные источники данных.
Используйте параметр «Обновить все», чтобы обновить все таблицы в модели данных Power Pivot.
Настройка автоматического обновления
Вы можете настроить источники данных, чтобы автоматически извлекать последние данные и обновлять Power Pivot.
Перейдите на вкладку «Данные» на ленте Excel и выберите команду «Подключения». Откроется диалоговое окно «Подключения рабочей книги». Выберите соединение с данными, с которым вы хотите работать, и нажмите кнопку «Свойства».
Выберите соединение и нажмите кнопку «Свойства».
В открывшемся диалоговом окне «Свойства» выберите вкладку «Использование». Здесь вы найдете возможность обновлять выбранное соединение данных каждые X минут и возможность обновлять соединение для передачи данных при открытии работы Excel:
- Обновить каждые X минут: Размещение проверки рядом с этой опцией сообщает Excel автоматически обновлять выбранное соединение данных на указанное количество минут. Это обновляет все таблицы, связанные с этим соединением.
- Обновить данные при открытии файла: Установка флажка рядом с этим параметром указывает Excel автоматически обновлять выбранное соединение для передачи данных после открытия книги. Это обновляет все таблицы, связанные с этим соединением, как только открывается рабочая книга.
Диалоговое окно «Свойства» позволяет настроить автоматическое обновление выбранного соединения.
Предотвращение обновления всех
Вы можете обновить все подключения, которые подают Power Pivot, с помощью команды Refresh All. Ну, на самом деле есть еще два места, где вы можете нажать «Обновить все в Excel»: на вкладке «Данные» в ленте Excel и на вкладке «Анализ», которую вы видите при работе в сводной таблице.
В Excel 2010 эти две кнопки обновили только стандартные сводные таблицы и соединения с данными в рабочей книге, а кнопки обновления Power Pivot повлияли только на Power Pivot. Теперь все они запускают ту же операцию. Таким образом, нажатие любой кнопки «Обновить все» в любом месте Excel по существу полностью перезагружает Power Pivot, обновляет все сводные таблицы и обновляет все соединения данных с книгой. Если ваша модель данных Power Pivot импортирует миллионы строк данных из внешнего источника данных, вы можете не использовать функцию «Обновить все».
К счастью, у вас есть способ предотвратить обновление некоторых подключений данных при выборе Refresh All. Перейдите на вкладку «Данные» на ленте Excel и выберите команду «Соединения». Откроется диалоговое окно «Подключения рабочей книги», в котором вы выбираете соединение для передачи данных, которое хотите настроить, а затем нажмите кнопку «Свойства».
Когда откроется диалоговое окно «Свойства», выберите вкладку «Использование», а затем снимите галочку рядом с «Обновить это соединение при обновлении всех» (как показано).
Диалоговое окно «Свойства» позволяет настроить выбранное соединение данных, чтобы игнорировать команду «Обновить все».
Редактирование подключения к данным
В некоторых случаях вам может потребоваться отредактировать подключение к исходным данным после того, как вы его уже создали. В отличие от обновления, когда вы просто делаете другой снимок одного и того же источника данных, редактирование источника данных позволяет вам вернуться назад и перенастроить соединение. Вот несколько причин, по которым вам может потребоваться отредактировать подключение к данным:
- Изменено местоположение или сервер или файл источника данных.
- Изменено имя файла сервера или файла данных.
- Вам необходимо изменить свои учетные данные или режим проверки подлинности.
- Вам нужно добавить таблицы, которые вы оставили во время первоначального импорта.
В окне Power Pivot перейдите на вкладку «Главная» и нажмите кнопку «Существующие подключения». Откроется диалоговое окно «Существующие подключения». Подключения Power Pivot Data Connections подключены к вашим разъемам Power Pivot. Выберите соединение для передачи данных, требующее редактирования.
Используйте диалоговое окно «Существующие подключения», чтобы перенастроить подключения к источнику данных Power Pivot.
После выбора целевого соединения данных просмотрите кнопки «Редактировать» и «Открыть». Кнопка, которую вы нажимаете, зависит от того, что вам нужно изменить:
Появится окно "Создание таблицы". Ставим галочку, указывая что наша таблица имеет заголовки и жмем ОК.
После этого сразу же открывается окно Power Pivot.
- Перед добавлением диапазона в модель данных Power Pivot сначала преобразует его в умную таблицу;
- Для каждого диапазона добавляемого в модель данных создаётся отдельная вкладка;
- Вкладке присваивается название умной таблицы.
Преимущества загрузки данных из Excel:
- это самый быстрый способ добавления данных в Power Pivot;
- при изменении данных в исходной таблице они автоматически изменяются и в окне Power Pivot;
- то же самое касается добавления новых строк и столбцов.
- не подходит для загрузки большого объёма данных, так как при каждом переключении окон Power Pivot автоматически обновляет данные, что ведёт к замедлению работы программы.
Кстати данные в Power Pivot можно добавлять и из другой книги Excel. Для этого в окне Power Pivot, на вкладке "Главная" нажимаем на кнопку "Из других источников".
С помощью кнопки "Обзор" указываем путь к расположению файла и ставим галочку в чекбоксе, напротив "Использовать первую строку в качестве заголовков столбцов".
Указываем на каком листе книги находятся данные и жмем "Готово".
Если мы всё сделали правильно, "Мастер импорта таблиц" должен сообщить нам, что импорт прошёл успешно. Далее закрываем окно "Мастера импорта таблиц" и видим, что данные из книги добавлены в Power Pivot.
Далее в появившемся окне пишем название вставляемой таблицы, ставим галочку напротив "Использовать первую строку в качестве заголовков столбцов" и жмем ОК.
- Не подходит для работы с большими объёмами данных;
- В созданную в окне Power Pivot с помощью данного метода таблицу можно добавлять дополнительные строки, но невозможно добавить дополнительные столбцы;
- Отсутствует возможность обновления данных.
Далее, указываем путь к нашему csv-файлу, выбираем в качестве разделителя столбцов "Точка с запятой (;)" и опять-таки используем в первую строку в качестве заголовков столбцов.
- Подходит для работы с большими объёмами данных;
- Можно добавлять дополнительные строки и столбцы;
- В книге в качестве недостатка данного метода приводится то что заголовки столбцов в таком формате файла легко могут быть изменены, что может вызвать проблемы с обновлением данных в Power Pivot. Но лично мне кажется, что вероятность возникновения данной ситуации равна нулю. Формат CSV именно тем и хорош, что при двойном нажатии файл открывается в окне Excel, так что содержимое файла удобно изменять при этом не нарушая его структуру.
На ленте инструментов Power Pivot, на вкладке "Конструктор", спрятана очень важная кнопка - "Свойства таблицы".
Читайте также: