artyrH
Пользователь
Сообщений: 2054
Регистрация: 01.07.2018
#1
21.06.2019 13:16:06
добрый день. хотел бы узнать как обновить Power Query в Excel 2016?
у меня стоит Excel 2016 (вариант для бедных, т.е. без оплаченой лицензии) с версией PQ — 2.24.4065.1401. уже несколько раз приходилось переделывать запросы из за отсутствия некоторых функций. так же у меня стоит Excel 2010, но с ней легче, так как надстройка PQ скачивается отдельно. а вот что делать с PQ в Excel2016?
- Remove From My Forums
-
General discussion
-
Hi guys,
how do you update Powerquery add-in in Excel 2016? When I download it from here: https://www.microsoft.com/en-us/download/details.aspx?id=39379
I am able to install the latest version over the older one in Excel 2013 but not in Excel 2016.
When I try 2016, I get message that «I already have the latest version» which is certainly not the case since I dont see some new features which can be seen in the 2013 version.
DZ
-
Edited by
Tuesday, June 21, 2016 2:29 PM
-
Edited by
Хитрости »
12 Январь 2023 913 просмотров
С выходом Power Query в массы все чаще стал появляться вопрос: как обновить запросы один за другим в заданном порядке?
Если еще не работали с этой надстройкой и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query — что такое и почему её необходимо использовать в работе?
Описание проблемы
В книге может быть десять и более запросов. И только 4 из них надо обновлять постоянно, да еще и в заданном порядке. Например, у нас есть с десяток запросов по продажам, отгрузкам и прочей аналитике, но только 4 из них нам надо обновить. При этом обновить надо в строгой последовательности:
«Запрос — Отделы», «Запрос — Сотрудники», «Заказы и Продажи», «Запрос — Бюджет»
. При этом обязательно необходимо, чтобы «Запрос — Бюджет» обновился последним, т.к. он зависит от всех остальных.
Какие есть варианты решения?
- Вручную
- Кнопка Обновить все(Refresh All)
- Написать собственный порядок обновления через Visual Basic for Applications
Что еще может пригодиться в решении задачи:
- Вывести список имен имен всех подключений на лист
Обновление вручную
Можно обновить запросы стандартными методами, поочередно вручную обновляя каждый запрос. Но это не всегда удобно, особенно если запросов более 5. Значит этот вариант не разбираем в деталях в принципе — все его плюсы и минусы очевидны.
Данные -Обновить все
Кнопка Данные(Data) —Обновить все(Refresh All) не просто обновляет все без исключения запросы в книге, но и порядок запросов при этом никак не определить, следовательно мы не можем быть уверены в том, что обновление произошло именно в нужном нам порядке. Но все же не станем отбрасывать этот вариант как не рабочий — если проблема лишь в порядке обновления, то решение все же есть.
На самом деле, если мы имеем дело с Power Query, то правильнее всего выстраивать все взаимосвязи уже на стадии создания этих запросов в любом случае. Т.е. создавать их в правильной последовательности и «затягивать» данные в основной запрос в самую последнюю очередь, ориентируясь на результаты вспомогательных запросов. Это оптимальный вариант, т.к. в этом случае достаточно будет просто обновить нужный запрос, а Power Query сама определит какие вспомогательные запросы необходимо обновить для корректного результата. При этом встроенный механизм Power Query достаточно хорошо справляется с кешированием данных и даже если обновление происходит через кнопку Обновить все(Refresh All), то даже если на один и тот же вспомогательный запрос будет завязано несколько других — он будет обновлен лишь один раз, а не при обновлении каждого запроса.
Но здесь у нас опять же возникает проблема — если нам не надо обновлять все запросы в книге — как Power Query узнает, какие из них надо обновлять, а какие нет? Ответ — никак. Поэтому хоть и следует всегда стараться выстраивать цепочки взаимодействий запросов в «правильном» порядке — нашу проблему с обновлением только части запросов это никак не решит. В этом случае можно создать мастер запрос, в который просто через объединение запросов собирать все нужные. И обновление этого запроса должно инициировать обновление всех указанных в нем запросов со всеми их цепочками. Как создать подобный мастер запрос:
Все, запрос готов. Теперь достаточно обновить только его, чтобы все связанные запросы обновились. Правда, есть одна проблема: если данных много, то такой запрос их увеличит вдвое — ведь мы в один запрос собираем все остальные…
Собственный порядок обновления через Visual Basic for Applications
В решении через мастер-запрос есть одна проблема — запросы могут быть не только Power Query, но и более старых версий. Здесь вариантов вообще не остается — только Visual Basic for Applications. Да и в случае, если запросы уже созданы ранее кем-то еще или навыков создать мастер-запрос нет.
'--------------------------------------------------------------------------------------- ' Author : Щербаков Дмитрий(The_Prist) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' Purpose: Код обновляет поочередно указанные запросы в заданном порядке '--------------------------------------------------------------------------------------- Sub RefreshConnections() Dim oc, xQuery, aConnections, xConName Dim IsBG_Refresh As Boolean 'создаем перечисление всех запросов, которые надо обновить 'располагаем их в том порядке, в котором необходимо обновлять aConnections = Array("Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет") 'перебираем и обновляем все запросы поочередно For Each xConName In aConnections Set oc = ActiveWorkbook.Connections(xConName) Set xQuery = Nothing Select Case oc.Type Case Excel.XlConnectionType.xlConnectionTypeODBC Set xQuery = oc.ODBCConnection Case Excel.XlConnectionType.xlConnectionTypeOLEDB Set xQuery = oc.OLEDBConnection Case Else 'запрос выгружен в таблицу на листе If oc.Ranges.Count > 0 Then Set xQuery = oc.Ranges(1).QueryTable End If End Select 'непосредственно обновление запроса с ожиданием окончания обновления If Not xQuery Is Nothing Then 'запоминаем значение обновления в фоне для запроса IsBG_Refresh = xQuery.BackgroundQuery 'выставляем принудительно ждать завершения запроса xQuery.BackgroundQuery = False 'обновляем запрос xQuery.Refresh 'возвращаем обновление в фоне в первоначальное состояние xQuery.BackgroundQuery = IsBG_Refresh End If Next End Sub
Как использовать: Для начала надо убедиться, что разрешены макросы и при необходимости включить их: почему не работает макрос. Затем копируем код выше, из Excel переходим в редактор VBA(Alt+F11) —Insert —Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш Alt+F8 -выделяем RefreshConnections —Выполнить(Run).
Чтобы использовать приведенный код для своих запросов достаточно в строке:
aConnections = Array("Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет")
внутри Array() перечислить в кавычках через запятую все имена запросов, которые необходимо обновить. Перечислять надо сразу в том порядке, в котором их требуется обновлять.
Код обновляет не только запросы Power Query, но и запросы более старых версий, выгруженных просто на лист.
Что делать, если
я не знаю, как правильно называются запросы
Да, вполне логичная ситуация, когда запросов много и переписывать имя каждого запроса не такая уж простая задача. Очень легко ошибиться в имени и тогда обновления не будет. Поэтому ниже я прилагаю код, который выводит на новый лист имена всех запросов:
'--------------------------------------------------------------------------------------- ' Author : Щербаков Дмитрий(The_Prist) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' Purpose: Код выводит на лист все запросы в текущей книге '--------------------------------------------------------------------------------------- Sub GetAllConnections() Dim ws As Worksheet Dim lr As Long Dim oc, aList() 'создаем массив для запоминания имен всех запросов ReDim aList(1 To ActiveWorkbook.Connections.Count, 1 To 1) For Each oc In ActiveWorkbook.Connections lr = lr + 1 'увеличиваем счетчик aList(lr, 1) = oc.Name Next 'создаем новый лист, в который записываем имена всех запросов Set ws = ActiveWorkbook.Worksheets.Add(before:=ActiveWorkbook.Worksheets(1)) 'оформляем заголовок(для эстетики) ws.Cells(1, 1).Value = "Имя запроса" ws.Cells(1, 1).Font.Bold = True 'записываем имена всех запросов ws.Cells(2, 1).Resize(lr, 1).Value = aList End Sub
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика
The monthly update cycle for Excel’s Power Query sure keeps us on our toes.
Power Query Updates for Excel 2010 and 2013
If you’re using Excel 2010, 2013 then you update Power Query by going to the Power Query download page and getting the latest copy and reinstalling it. No need to uninstall first, just download and install again.
Power Query Updates for Excel 2016 (non-subscription)
For Excel 2016 users who aren’t on an Office 365 subscription there’s bad news and good news. The bad news is you don’t get updates, the good news is you don’t have to re-learn how to do things in Power Query because Microsoft changed something!
That’s not to say the good outweighs the bad 😉
Power Query Updates for Excel 2016 Office 365
For Excel 2016 users with an Office 365 subscription your updates come at different intervals depending on your update channel. By default, Office 365 is installed on the Deferred Channel. This is often up to 6 months behind the Current Channel.
To find what Channel you’re on go to the File tab > Account. Below the Version number in the Product Information you’ll see the channel details. For example, this is what mine looks like:
You can see I’m on a ‘special’ Office Insider Slow channel, the standard channels are Deferred and Current. You can find the updates available for the various channels on this page (change the radio buttons on the left-hand side to view the details for the different channels).
And this page explains how to switch to the current channel.
Updating Power Query Course
On my short-term to-do list is updating the Power Query course for the Excel 2016 menu (remember it’s only the Excel ribbon/menu, not the Power Query Editor menu itself), however with the constant changes it’s a moving target. For example, for non-updating versions of Excel 2016 the menu looks like this:
But recently my Excel 2016 Power Query menu has changed again and now looks like this:
Where do you think you’d click if you now wanted a ‘New Query’? If you said ‘Get Data’ you’d be right.
Did you also notice the old ‘Get External Data’ group has gone? It’s now hidden and only recoverable by changing the settings in Excel Options (via File tab):
Microsoft hid the old Get External Data menu because Power Query is the new Get data tool and it’s light years ahead of the legacy data import processes.
So, as Excel users these days we need to get used to Microsoft moving our menus around as they continue to develop and improve our beloved Excel.
And, as I’ve said before, when we’re learning we need to be prepared to go looking for icons and tools that look one way in a video, book or blog post, and are different in the latest version of the software we now have at our fingertips. To help us locate rogue icons we must comprehend the task, we can’t just copy clicks and learn by rote. Because when we comprehend the task we can use our understanding to bridge the gap between what we see in the video, book or blog post and the menus we have on our PC.
Of course, if you get stuck, our Excel Forum is where you can go for help.
Learn Power Query
Power Query is the most important Excel skill you can learn this year. It’s going to give you the most bang for your training buck because it’s for every Excel user. I can’t think of an Excel job that doesn’t involve getting data from somewhere, cleaning that data, adding columns and calculations to that data, and all before we can use it for reporting, or further analysis.
Power Query simplifies and automates these repetitive tasks with point and click ease.
If you haven’t got yourself up to speed with Power Query yet, then please check out my Power Query course.
- Remove From My Forums
-
General discussion
-
Hi guys,
how do you update Powerquery add-in in Excel 2016? When I download it from here: https://www.microsoft.com/en-us/download/details.aspx?id=39379
I am able to install the latest version over the older one in Excel 2013 but not in Excel 2016.
When I try 2016, I get message that «I already have the latest version» which is certainly not the case since I dont see some new features which can be seen in the 2013 version.
DZ
-
Edited by
Tuesday, June 21, 2016 2:29 PM
-
Edited by
Содержание
- Сведения о Power Query в Excel
- Глава 16. Автоматизация обновления решений в Power Query
- Планирование обновлений без использования кода
- Автоматизация обновления запросов с помощью VBA
- Обновление нескольких запросов
Сведения о Power Query в Excel
С помощью Power Query (в Excel это называется Get & Transform) можно импортировать или подключиться к внешним данным, а затем сформировать эти данные, например удалить столбец, изменить тип данных или объединить таблицы в соответствии со своими потребностями. Затем можно загрузить запрос в Excel для создания диаграмм и отчетов. Периодически можно обновлять данные, чтобы сделать их актуальными. Power Query доступны в трех приложениях Excel: Excel для Windows, Excel для Mac и Excel для Интернета. Общие сведения о всех разделах справки Power Query см. в Power Query справки Excel.
Существует четыре этапа использования Power Query.
Подключения Подключение к данным в облаке, в службе или локально
Преобразования Формирование данных в соответствии со своими потребностями, в то время как исходный источник остается неизменным
Объединить Интеграция данных из нескольких источников для получения уникального представления в данных
Нагрузки Выполните запрос и загрузите его на лист или модель данных и периодически обновляйте его.
В следующих разделах каждый этап рассматривается более подробно.
Можно использовать Power Query для импорта в один источник данных, например книгу Excel, или в несколько баз данных, веб-каналов или служб, распределенных по облаку. Источники данных включают данные из Интернета, файлов, баз данных, Azure или даже таблиц Excel в текущей книге. Используя Power Query, вы можете объединить все эти источники данных с помощью собственных уникальных преобразований и сочетаний для получения аналитических сведений, которые в противном случае не были бы видны.
После импорта можно обновить данные, чтобы добавить дополнения, изменения и удаления из внешнего источника данных. Дополнительные сведения см. в разделе «Обновление подключения к внешним данным» в Excel.
Преобразование данных означает их изменение в соответствии с требованиями к анализу данных. Например, можно удалить столбец, изменить тип данных или отфильтровать строки. Каждая из этих операций представляет собой преобразование данных. Этот процесс применения преобразований (и объединения) к одному или нескольким наборам данных также называется формированием данных.
Подумайте об этом так. Вазы начинаются как часть уютов, которые одна фигура создает что-то практическое и прекрасное. Данные одинаковы. Она должна формироваться в таблицу, подходящую для ваших потребностей и поддерживающая привлекательные отчеты и панели мониторинга.
Power Query использует выделенное окно с именем Редактор Power Query для упрощения и отображения преобразований данных. Чтобы открыть Редактор Power Query, выберите команду «Запустить Редактор запросов» в группе «Получение данных& Преобразование данных», но она также откроется при подключении к источнику данных, создании запроса или загрузке запроса.
Этот Редактор Power Query отслеживает все действия, которые вы выполняете с данными, записав и помечая каждое преобразование или шаг, применяемый к данным. Независимо от того, является ли преобразование подключением к данным, удалением столбца, слиянием или изменением типа данных, можно просмотреть и изменить каждое преобразование в разделе APPLIED STEPS на панели параметров запроса.
Существует множество преобразований, которые можно выполнить из пользовательского интерфейса. Каждое преобразование записывается как шаг в фоновом режиме. Вы даже можете изменять и записывать собственные шаги с помощью Power Query языка M в Расширенный редактор.
Все преобразования, применяемые к подключениям к данным, представляют собой запрос, который представляет собой новое представление исходного (без изменений) источника данных. При обновлении запроса каждый шаг выполняется автоматически. Запросы заменяют необходимость вручную подключаться и формировать данные в Excel.
Вы можете объединить несколько запросов в книге Excel, добавьте или объедините их. Операции добавления и слияния выполняются в любом запросе с табличной фигурой и не зависят от источников данных, из которых поступают данные.
Добавить Операция добавления создает новый запрос, содержащий все строки из первого запроса, за которым следуют все строки из второго запроса. Можно выполнить два типа операций добавления:
Промежуточное добавление Создает новый запрос для каждой операции добавления.
Встроенная надстройка Добавляет данные в существующий запрос, пока не будет достигнут окончательный результат.
Объединить Операция слияния создает новый запрос из двух существующих запросов. Этот запрос содержит все столбцы из первичной таблицы, один из них служит ссылкой для навигации по связанной таблице. Связанная таблица содержит все строки, соответствующие каждой строке из общего значения столбца в первичной таблице. Кроме того, можно развернуть или добавить столбцы из связанной таблицы в основную таблицу.
Существует два основных способа загрузки запросов в книгу:
В Редактор Power Query можно использовать команды «Закрыть» и «Загрузить» в группе «Закрыть» на вкладке «Главная«.
В области «Запросы книги Excel» (выбор & подключения) можно щелкнуть запрос правой кнопкой мыши и выбрать команду «Загрузить».
Вы также можете настроить параметры загрузки с помощью диалогового окна «Параметры запроса» (выберите параметры и параметры файла> параметры > Параметры запроса ), чтобы выбрать способ просмотра данных и место загрузки данных на листе или в модель данных (которая является реляционным источником данных нескольких таблиц, которые находятся в книге).
Более десяти лет Power Query в Excel для Windows. Теперь Excel расширяет возможности Power Query в Excel для Mac и добавляет поддержку в Excel для Интернета. Это означает, что мы Power Query на трех основных платформах и демонстрируем популярность и функциональность Power Query среди клиентов Excel. Следите за будущими объявлениями о стратегии развития Microsoft 365и новых возможностях Excel для Microsoft 365.
Интеграция Get & Transform Data (теперь называется Power Query) в Excel прошла через несколько лет изменений.
Excel 2010 и 2013 для Windows
В Excel 2010 для Windows мы впервые представили Power Query и она была доступна в виде бесплатной надстройки, которую можно скачать здесь: скачайте надстройку Power Query надстройку. После включения Power Query функции были доступны на Power Query на ленте.
Мы обновили Power Query, чтобы быть основным интерфейсом в Excel для импорта и очистки данных. Вы можете получить доступ к Power Query и средствам импорта данных из группы «& Преобразования данных» на вкладке «Данные» ленты Excel.
Этот интерфейс включает расширенные функции импорта данных, переупорядоченные команды на вкладке «Данные», новую панель «Запросы & Подключение», а также возможность эффективного формирования данных путем сортировки, изменения типов данных, разделения столбцов, агрегирования данных и т. д.
Этот новый интерфейс также заменил старые мастера импорта устаревших данных в команде «Данные » в группе «Получение внешних данных «. Однако доступ к ним по-прежнему можно получить в диалоговом окне «Параметры Excel» (выберите «Параметры файлов >»> « > Показать устаревшие мастера импорта данных)».
Excel 2016 2019 для Windows
Мы добавили то же & преобразования данных на основе Power Query технологии, что и в Microsoft 365.
Excel для Microsoft 365 для Mac
В 2019 году мы начали процесс поддержки Power Query в Excel для Mac. С тех пор мы добавили возможность обновлять Power Query из файлов TXT, CSV, XLSX, JSON и XML. Мы также добавили возможность обновлять данные из SQL Server и из таблиц & диапазонов в текущей книге.
В октябре 2019 г. мы добавили возможность обновлять существующие Power Query и использовать VBA для создания и изменения новых запросов.
В январе 2021 г. мы добавили поддержку обновления Power Query запросов из источников OData и SharePoint.
Дополнительные сведения см. в Power Query в Excel для Mac.
Примечание В Power Query Excel 2019 для Mac Excel 2016 не поддерживается.
Каталог данных устаревания
С помощью Каталог данных вы можете просмотреть общие запросы, а затем выбрать их для загрузки, изменения или иного использования в текущей книге. Эта функция постепенно устарела:
1 августа 2018 г. мы перестали подключать новых клиентов к Каталог данных.
3 декабря 2018 г. пользователям не удалось поделиться новыми или обновленными запросами в Каталог данных.
4 марта 2019 г. работа Каталог данных прекращена. После этой даты мы рекомендуем скачать общие запросы, чтобы вы могли продолжать использовать их за пределами Каталог данных, используя параметр «Открыть» в области задач «Мои Каталог данных Запросы».
Power Query нерекомендуемой надстройки
В начале 2019 года мы официально не рекомендуем использовать надстройку Power Query, которая требуется для Excel 2010 и 2013 для Windows. В качестве предоставленного решения вы по-прежнему можете использовать надстройку, но это может измениться позже.
Отключен соединитель данных Facebook
Импорт и обновление данных из Facebook в Excel перестали работать в апреле 2020 г. Все подключения Facebook, созданные до этой даты, больше не работают. Мы рекомендуем как можно скорее изменить или удалить все существующие Power Query, использующие соединитель Facebook, чтобы избежать непредвиденных результатов.
Источник
Глава 16. Автоматизация обновления решений в Power Query
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
По мере того, как вы создаете все больше и больше решений на основе Power Query, и начинаете понимать, сколько времени это экономит, вы захотите еще большей автоматизации. Да, вы можете просто щелкнуть правой кнопкой мыши таблицу, которая была импортирована с помощью Power Query, но даже это ощущается как… ручная операции. Нельзя ли запланировать обновление или, возможно, контролировать порядок обновления?
На самом деле вы можете использовать несколько различных методов автоматизации обновления решений Power Query:
- Обновление при открытии книги
- Обновление каждые Х минут
- Обновить соединение по требованию с помощью VBA
- Обновить все соединения по требованию с помощью VBA
- Использовать расписание обновлений в сторонней надстройке
Рис. 16.1. Настройка параметров подключения
Скачать заметку в формате Word или pdf, примеры в формате архива
Планирование обновлений без использования кода
Первые два метода работают через пользовательский интерфейс и не требуют кода VBA. Они могут быть настроены на основе подключения, и вы даже можете автоматизировать обновление вплоть до Power Pivot. Откройте файл Automating Refresh.xlsx. Перейдите на вкладку Данные –> Запросы и подключения. В правой части окна Excel откроется область Запросы и подключения. Кликните правой кнопкой мыши имя запроса –> Свойства. Откроется окно Свойства запроса (рис. 1).
Обратите внимание, что флажок Фоновое обновление установлен по умолчанию. Этот параметр позволяет указать, следует ли продолжать работать в Excel во время обновления данных. Если вы снимите этот флажок, вы можете потенциально уменьшить время, необходимое для обновления вашего решения, но вы также заблокируете пользовательский интерфейс, что означает, что вы не сможете делать другие вещи, пока обновление запроса не будет завершено. Желательно оставить этот параметр включенным.
Следующий параметр – Обновлять каждые ХХ минут. Этот параметр очень пригодится, если вы извлекаете данные из веб-источника, который постоянно меняется, или если вы ориентируетесь на базу данных, которая регулярно обновляется. Для того, чтобы это обновление произошло книга Excel должна быть открыта. Допустимые значения для этого параметра – от 1 до 32 767.
Обновлять при открытии книги. Второй параметр в этом разделе – Удалять данные из внешнего диапазона перед сохранением книги. Установите этот флажок, если вы хотите сохранить книгу с определением запроса, но без использования внешних данных. Снимите этот флажок, чтобы сохранить, и определение запроса, и данные. Этот флажок, будет доступен только после установления флажка Обновлять при открытии книги.
Автоматизация обновления запросов с помощью VBA
Приемы, описанные выше, и не использующие макросы, обходятся без каких-либо окон, предупреждающих о безопасности. Кроме того, такие книги легче переносить в Power BI, так как они не вызывают никаких проблем с блокировкой. Однако если вы работаете исключительно в настольном экземпляре Excel, иногда может потребоваться предоставить пользователю удобный и очевидный способ обновления решений Power Query. Это может быть сделано с помощью записи кода VBA.
Можно создать макрос для обновления одного подключения Power Query. В файле Automating Refresh.xlsx и перейдите на лист Transactions. На листе расположена Таблица Transactions, а также сводная таблица. Допустим, вы хотите создать макрос для обновления их обеих. Перейдите на вкладку Разработчик. Если вы не видите вкладку Разработчик, щелкните правой кнопкой мыши любую вкладку на ленте и выберите Настроить ленту. В правом окне установите флажок рядом с вкладкой Разработчик и нажмите Ok. На вкладке Разработчик нажмите кнопку Запись макроса. Также можно нажать иконку в левом нижнем углу окна Excel:
Рис. 16.2. Кнопка Начать запись макроса
Как только вы нажмете эту кнопку, Excel начнет записывать каждый щелчок листа, каждое нажатие клавиши и… каждую ошибку, которую вы делаете. Выполните в точности следующие действия. Дайте макрос Refresh и сохраните его в текущей книге. Пройдите по меню Данные –> Запросы и подключения –> В правой части окна Excel в области Запросы и подключения кликните правой кнопкой мыши на запросе Transactions –> Обновить. Щелкните правой кнопкой мыши любую ячейку сводную таблицу –> Обновить. Повторно кликните кнопку, как на рис. 16.2. Запись макроса будет остановлена. Проверьте код. Пройдите по меню Вид –> Макросы:
Рис. 16.3. Макросы
Нажмите Изменить. Откроется окно редактора VBA
Рис. 16.4. Код VBA; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Добавьте кнопку для обновления макроса. Перейдите на вкладку Разработчик –> Вставить –> Кнопку:
Рис. 16.5. Вставить элемент управления Кнопку
Выберите место на листе, и удерживая левую кнопку мыши, нарисуйте прямоугольник для Кнопки. Отпустите левую кнопку мыши. Появится окно с предложением поставить в соответствие Кнопке макрос. Выберите Refresh. Нажмите Ok. Щелкните правой кнопкой мыши по Кнопке –> Изменить текст. Введите Обновить. Щелкните любую ячейку на листе, чтобы выйти из режима редактирования названия кнопки.
Рис. 16.6. Кнопка, подключенная к макросу
Обновление нескольких запросов
Теперь можно добавить иные запросы в макрос. Пройдите по меню Разработчик –> Макросы –> Refresh –> Изменить. На данный момент код имеет вид (см. также рис. 16.4):
Источник
Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных.
Надстройка Power Query
Появление Power Query – одно из важных событий в истории развития Excel. То, что раньше пользователи делали в Excel с помощью рутинных операций по преобразованию данных, писали огромные формулы или макросы на VBA, теперь можно выполнять буквально в несколько щелчков мышкой.
Power Query – это надстройка для создания запросов по импорту, очистке и преобразованию данных.
Power Query сам автоматически генерирует и записывает код запросов на языке M.Но это вовсе не значит, что пользователям нужно писать код – интерфейс Power Query очень понятный и позволяет выполнять операции только с помощью мышки. Большинство операций по преобразованию данных в нем можно выполнить, не написав ни одной строчки кода. Все запросы Power Query записываются и сохраняются, поэтому при последующих подключениях повторять операции еще раз не нужно – они выполнятся автоматически после нажатия на кнопку в меню Данные → Обновить.
Как работает
В привычном нам виде Power Query впервые появился в Excel 2013. В Excel 2010 и 2013 надстройка отображается в виде отдельной вкладки «Power Query».
В Excel 2016 надстройка уже встроена по умолчанию, на вкладке Данные – раздел «Скачать и преобразовать».
В версии Excel 2019 на вкладке Данные разделы с Power Query — «Получить и преобразовать данные» и «Запросы и подключения».
Возможности Power Query
- Из Excel можно напрямую подключаться к самым различным источникам данных: файлы excel, csv, базы данных, папки и т.д.
- Данные в подключенных источниках можно преобразовывать самыми различными способами: сортировать, фильтровать, заменять, делать расчеты, объединять и т.д.
- Преобразованные данные можно импортировать: выгружать на лист, в сводную таблицу, сразу в модель данных (в Power Pivot). Или не выгружать, а просто сохранить запрос.
Операции по преобразованию данных выполняются в окне редактора запросов.
В редакторе Power Query пользователь работает с данными – выполняет преобразования, а его действия автоматически записываются в виде шагов запроса.
О том, как работать в редакторе запросов, читайте в следующей статье.
Как скачать Power Query
Надстройка устанавливается бесплатно и доступна для Excel начиная с версий 2010 года:
- для Excel 2010 и 2013 скачивается с сайта Microsoft;
- для Excel версии после 2016 года ничего скачивать и устанавливать не нужно, так как эта надстройка идет в составе всех лицензий.
Надстройка Power Pivot
Те, кто часто пользуются сводными таблицами, понимают, насколько они удобны и эффективны в работе.
Power Pivot — это надстройка Excel, в которой можно выполнять улучшенные вычисления для сводных таблиц с помощью DAX-формул. Power Pivot значительно расширяет функционал сводных таблиц и позволяет работать с большими объемами данных – в десятки миллионов строк.
Как работает
Вкладка меню Power Pivot во всех версиях Excel, начиная с 2010, выглядит одинаково. Чтобы открыть окно Power Pivot, нажмите в меню Power Pivot → Управление. Если вкладки Power Pivot у вас в меню нет, проверьте, та ли у вас версия Excel. И возможно, вам потребуется добавить эту вкладку в меню.
Возможности Power Pivot
- Power Pivot позволяет обращаться из одной сводной таблицы к данным из нескольких таблиц без формулы ВПР. Это возможно благодаря объединению таблиц в модель данныхс помощью связей.
- Импорт более 100 миллионов строк в рабочую книгу. При работе в «обычном» Excel у пользователей есть ограничения по размеру таблиц – размер листа, то есть чуть больше 1 млн строк. В Power Pivot такого ограничения нет и в него можно загружать миллионы строк. Единственное ограничение по объему — размер файла книги, равный 2 Гбайт, и объем оперативной памяти.
- Благодаря алгоритму сжатия данных в Power Pivot объем файла становится меньше, чем в источнике. Например, текстовый файл объемом 50 Мбайт может сжиматься до 4 Мбайт, занимаемых файлом Excel с таблицей.
- Создание улучшенных формул для сводных таблиц с помощью языка DAX.
В Power Pivot можно писать DAX-формулы для продвинутого анализа в столбцах и создавать меры — вычисления под таблицей.
Кстати, в Power Pivot есть свои инструменты для импорта данных, но из небольшого числа источников – некоторые базы данных, файлы Excel и текстовые и др. Эти инструменты можно найти на вкладке Главная. Так как доступных источников данных не очень много, то, как правило, в качестве источника для него выступает Power Query, функционал которого по подключению намного больше.
Увидеть все загруженные данные можно в нижней части окна Power Pivot – ярлычки с названиями таблиц находятся там же, где названия листов в «обычном» Excel.
Здесь же в меню Главная → Представление диаграммы можно перейти в представление, где создаются связи в модели данных. Создавать связи в этой области очень просто – с помощью «перетаскивания» полей: выбираем нужное поле мышкой и переносим его на соответствующее поле другой таблицы.
Power Pivot поддерживает типы связей «один к одному» и «один ко многим» («многие к одному»).
Перейти обратно от модели данных к таблицам можно в меню Главная → Представление данных. В представлении в виде таблиц удобно делать вычисления с помощью DAX-формул, которые очень похожи на обычные формулы Excel.
DAX-формулы пишут в столбцах таблиц – создают вычисляемые столбцы. Или в области вычислений под таблицей, такие объекты называются меры.
Еще в Power Pivot можно создавать ключевые показатели эффективности (KPI) – автоматические светофоры, которые будут выводиться в сводной таблице. KPI в Power Pivot – это расширение функционала для мер.
Язык DAX используется не только в Power Pivot в Excel, но и в Power BI и Analysis Services. Подробнее о DAX можно прочитать в наших следующих статьях.
Как скачать Power Pivot
Надстройка Power Pivot есть не во всех версиях Excel:
- Power Pivot для Excel 2010 года скачивается бесплатно с сайта Microsoft. К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query, и этот новый функционал в старом Excel сильно ограничен.
- В Excel 2013 и 2016 года Power Pivot идет в составе программы, но не во всех лицензиях. Так, в Office Pro Plus и Office 2019 надстройка есть. Узнать, какие именно версии Office включают Power Pivot, можно здесь.
Кстати, отличная новость – компания Microsoft анонсировала включение Power Pivot во все версии Excel, начиная с версий после 2019 года. Так что теперь Power Pivot всегда будет под рукой.
Если с версией Excel все в порядке, а вкладки Power Pivot в меню нет, то ее нужно добавить в меню с помощью окна Управления надстройками (как это сделать – смотрите далее).
Как добавить Power Query и Power Pivot в меню
В зависимости от того, какая у вас версия Excel, вам может потребоваться перед первым использованием добавить надстройки Power Query и Power Pivot в меню.
Если в меню Excel у вас нет надстроек, то нужно их добавить в окне Управления надстройками:
Файл → Параметры → Надстройки → в Выпадающем меню выбрать → Надстройки COM → Перейти → в открывшемся окне выбрать надстройки.
Как надстройки помогают в создании отчетов Excel
Надстройки Power Query и Power Pivot дополняют друг друга и отлично работают вместе со сводными таблицами, позволяя пользователям создавать в Excel аналитические отчеты. Как правило, используется последовательность работы с данными в таком порядке:
- с помощью Power Query подключают и обрабатывают источники данных;
- из Power Query плоские таблицы загружаются прямо в Power Pivot, минуя ограничение по количеству строк листа Excel;
- в Power Pivot выполняют сложные расчеты и создают модель данных;
- отчеты создаются с помощью сводных таблиц, диаграмм и элементов управления (временной шкалы и срезов).
Отчеты по такой схеме настраиваются один раз и при последующем изменении данных преобразования и вычисления выполнятся автоматически, после нажатия на кнопку в меню Данные → Обновить.
Надстройки в Excel и Power BI
Огромное преимущество надстроек Power Query и Power Pivot – они есть не только в Excel, но и в Power BI. Поэтому на тренингах я обычно говорю слушателям, что мы изучаем сразу две программы.
Интерфейс надстроек в обеих программах очень похож, но функционал Power BI все-таки опережает Excel (сразу уточню, что сравниваю только инструменты Power Query и Power Pivot). Например, Power BI поддерживает больше источников данных и доступны связи «многие-ко-многим», которых в Excel нет.
Для сравнения – интерфейс Power Query в обеих программах выглядит практически одинаково:
Power Query в Excel
Power Query в Power BI
Power Pivot в целом также работает одинаково в Excel и Power BI, кроме ряда некоторых нюансов. Например, так выглядит окно настройки модели данных:
Модель данных в Excel
Модель данных в Power BI
Кстати, если у вас есть файл Excel, в котором вы настраивали аналитику с помощью Power Query и Power Pivot, то его содержимое можно перенести в Power BI. Это делается в Power BI: меню Файл → Импортировать → выберите «Power Query, Power Pivot, Power View». После пары вопросов и некоторых «размышлений» запросы и вычисления из Excel будут перенесены в Power BI.
В общем, надстройки Power Query и Power Pivot – это суперсила (от слова power) Excel, которые переносят возможности аналитики в этой программе на новый уровень. С их помощью пользователи могут создавать отчеты с вычислениями на основе разных источников, без ограничений по объему данных в размер одного листа в миллион строк. Плюс, компания Microsoft включает эти инструменты и в другие свои продукты, так что понимание того, как они работают, вам точно пригодится.