Query что это за программа

Как установить Power Query в Excel

Всем привет! Как часто Вам приходится делать в Excel рутинные операции по обработке, очистке, подготовке, «причесыванию» данных? Скорее всего, довольно часто. Это самое нудное и скучное занятие, которое, к тому же, требует внимательности и сосредоточенности. Самые продвинутые пользователи берут на вооружение макросы и с их помощью автоматизируют часть рутины.

Но на освоение макросов нужно время и достаточно много усилий.

Что такое Power Query и зачем он нужен?

Что же делать тем, кому нужно работать быстрее и эффективнее, но учиться программировать не хочется или нет возможности? Ответ прост — используйте Power Query . Это очень мощный инструмент из класса ETL ( Extract, Transform, Load — извлечение, преобразование, загрузка). Как следует из названия, он умеет извлекать данные из разных источников, преобразовывать их (та самая рутина), а затем выгружать или загружать, куда нужно. На практике это позволяет решать множество проблем.

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

Ваша задача: каждый день брать из папки вновь появившиеся файлы, доставать из них только то, что нужно, копировать в свои таблицы Excel, обновлять сводные и графики. Скучно и рутинно. А с помощью Power Query все описанные выше операции можно свести к нажатию одной кнопки «Обновить»! Потрясающе просто и эффективно!

Где взять Power Query?

Всё очень просто. Это БЕСПЛАТНАЯ надстройка, которую можно скачать и установить для Excel 2010 и Excel 2013 . Скачивать нужно прямо с официального сайта Microsoft по этой ссылке . Только сначала узнайте разрядность своей версии Excel: Файл — Справка или Файл — Учетная запись . Для Excel разрядностью 32 бита скачивайте версию 32-bit ( x86 ), для 64-разрядного Excel — версию 64-bit ( x64 ).

А для Excel 2016, 2019 или Office 365 даже скачивать ничего не надо. Всё уже встроено и доступно на вкладке «Данные» (Data) в группе команд «Получить и преобразовать» (Get

2) Если Excel был открыт, то закрываете его (об этом вас предупредит мастер установки);

3) Следуете инструкциям мастера установки, нажимая кнопки » Далее » и » Установить » (адрес установки можно оставить по умолчанию).

4) Запускаете Excel. На ленте появляется новая вкладка команд, которая так и называется — Power Query.

Если сразу после установки и запуска Excel вкладка с новыми командами не появилась на ленте, то её нужно включить вручную. Выбираете » Файл » — » Параметры » — » Настройка ленты «, находите в списке справа Power Query и ставите галочку. Жмете ОК .

Если что-то вдруг не понятно, то посмотрите видеоурок на эту тему:

Ну а если Вам интересны примеры возможностей инструмента и уроки по работе с ним, то их можно увидеть тут: раз , два , три . Будьте уверены, освоить Power Query не сложнее (а даже легче), чем сам Excel, и уж точно намного легче, чем работу с макросами. Поэтому обязательно берите на вооружение эту мощную надстройку.

Поддержать наш проект и его дальнейшее развитие можно вот здесь .

С уважением, команда tDots.ru

Источник: dzen.ru

Power Query — что такое и почему её необходимо использовать в работе?

Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные (Data) -группа Скачать и преобразовать (Get вытащить» на основании ссылки на сайт и обновлять это автоматически, да еще и «привязать» к расчетам в других таблицах

можно легко и быстро получить обновляемую информацию даже из файлов Google sheets, что до этого момента даже из VBA было занятием не из простых

есть встроенные возможности получения данных из аккаунта Facebook. Мне лично этого никогда не требовалось, но краем правого уха подмечал, что кому-то это очень было нужно 🙂

Google таблицы. Как пользоваться функцией Query. Урок 10.

В общем, полагаю, мотив понятен — с помощью PowerQuery каждый пользователь не имея никаких навыков программирования может достаточно легко получить данные практически из любого источника и сразу же на месте привести к их нужному виду.

Статья помогла? Поделись ссылкой с друзьями!

Источник: www.excel-vba.ru

Извлечение внешних данных с помощью Microsoft Query

Для извлечения данных из внешних источников можно использовать приложение Microsoft Query. Используя Microsoft Query для извлечения данных из корпоративных баз данных и файлов, не нужно повторно вводить данные, которые нужно проанализировать в Excel. Кроме того, вы Excel отчеты и сводные данные из исходной исходной базы данных при обновлении новой информации.

Подробнее о Microsoft Query

С помощью Microsoft Query можно подключаться к внешним источникам данных, выбирать данные из них, импортировать их на свой сайт и обновлять данные, чтобы данные на вашем компьютере всегда были синхронизированы с данными во внешних источниках.

Типы баз данных, к которые можно получить доступ Вы можете извлекать данные из баз данных нескольких типов, включая Microsoft Office Access, Microsoft SQL Server и Microsoft SQL Server OLAP Services. Вы также можете получать данные из Excel книг и текстовых файлов.

Microsoft Office драйверы, которые можно использовать для получения данных из следующих источников:

• Microsoft SQL Server Analysis Services (поставщик OLAP )
• Microsoft Office Доступа
• dBASE
• Microsoft FoxPro
• Microsoft Office Excel
• Oracle
• Парадокс
• Базы данных текстовых файлов

Вы также можете использовать драйверы ODBC или драйверы источников данных других производителей для извлечения информации из источников данных, которые здесь не указаны, включая другие типы баз данных OLAP. Чтобы получить сведения об установке драйвера ODBC или драйвера источника данных, который здесь не указан, проверьте документацию к базе данных или обратитесь к поставщику базы данных.

Выбор данных из базы данных Чтобы получить данные из базы данных, создайте запрос ( вопрос о данных, хранимых во внешней базе данных). Например, если данные хранятся в базе данных Access, может потребоваться узнать данные о продажах для определенного продукта по регионам. Вы можете получить часть данных, выбрав только данные для продукта и региона, которые вы хотите проанализировать.

С помощью Microsoft Query можно выбрать нужные столбцы данных и импортировать только эти данные в Excel.

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

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

Импорт данных с помощью Microsoft Query чтобы импортировать внешние данные в Excel с помощью Microsoft Query, выполните основные действия, описанные в следующих разделах более подробно.

Подключение к источнику данных

Что такое источник данных? Источник данных — это хранимый набор сведений, позволяющий Excel и Microsoft Query подключаться к внешней базе данных. При использовании Microsoft Query для работы с источником данных вы даете источнику данных имя, а затем ука вашему имени и расположению базы данных или сервера, тип базы данных, а также сведения о вашем учете и пароле. Эти сведения также включают имя драйвера OBDC или драйвера источника данных — программы, которая создает подключения к базе данных определенного типа.

Чтобы настроить источник данных с помощью Microsoft Query:

1. На вкладке Данные в группе Внешние данные нажмите кнопку Из другихисточников и выберите из Microsoft Query.
2. Выполните одно из указанных ниже действий.
3. Чтобы указать источник данных для базы данных, текстового файла или Excel книги, перейдите на вкладку Базы данных.
4. Чтобы указать источник данных куба OLAP, перейдите на вкладку Куба OLAP. Эта вкладка доступна, только если вы запустили Microsoft Query из Excel.
5. Дважды щелкните . ИЛИ Щелкните и нажмите кнопку ОК.Отобразилось диалоговое окно Создание источника данных.
6. На шаге 1 введите имя для определения источника данных.
7. На шаге 2 выберите драйвер для типа базы данных, используемой в качестве источника данных.

• Если внешняя база данных, к которой вы хотите получить доступ, не поддерживается драйверами ODBC, установленными с помощью Microsoft Query, необходимо получить и установить драйвер ODBC, совместимый с Microsoft Office, у стороного поставщика, например производителя базы данных. За инструкциями по установке обратитесь к поставщику базы данных.
• Для баз данных OLAP драйверы ODBC не требуются. При установке Microsoft Query драйверы устанавливаются для баз данных, созданных с Microsoft SQL Server Analysis Services. Чтобы подключиться к другим базам данных OLAP, необходимо установить драйвер источника данных и клиентское программное обеспечение.

• Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков.
• Очень важно запомнить свой пароль. Если вы забудете пароль, корпорация Майкрософт не сможет его восстановить. Все записанные пароли следует хранить в надежном месте отдельно от сведений, для защиты которых они предназначены.

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

После выполнения этих действий имя источника данных появится в диалоговом окне Выбор источника данных.

Определение запроса с помощью мастера запросов

Использование мастера запросов для большинства запросов Мастер запросов упрощает выбор и сбор данных из разных таблиц и полей базы данных. С помощью мастера запросов можно выбрать таблицы и поля, которые вы хотите включить. Внутреннее объединить (операция запроса, которая определяет, что строки из двух таблиц объединяются на основе одинаковых значений полей) создается автоматически, когда мастер распознает поле первичного ключа в одной таблице и поле с одинаковым именем во второй таблице.

Мастер также можно использовать для сортировки набора результатов и простой фильтрации. На завершаемом этапе мастера можно вернуть данные в Excel или уточнить запрос в Microsoft Query. После создания запроса его можно запустить в Excel или в Microsoft Query.

Чтобы запустить мастер запросов, выполните следующие действия:

1. На вкладке Данные в группе Внешние данные нажмите кнопку Из другихисточников и выберите из Microsoft Query.
2. Убедитесь в том, что в диалоговом окне Выбор источника данных был выбран вариант Использовать мастер запросов для создания и изменения запросов.
3. Дважды щелкните нужный источник данных. ИЛИ Выберите нужный источник данных и нажмите кнопку ОК.

Работа с другими типами запросов непосредственно в Microsoft Query Если вы хотите создать более сложный запрос, чем позволяет мастер запросов, вы можете работать непосредственно в Microsoft Query. С помощью Microsoft Query можно просматривать и изменять запросы, которые создаются в мастере запросов, или создавать новые запросы без использования мастера. Работайте непосредственно в Microsoft Query, если вы хотите создать запросы, которые будут:

• Выбор определенных данных из поля В большой базе данных может потребоваться выбрать часть данных в поле и опустить данные, которые вам не нужны. Например, если вам нужны данные для двух продуктов в поле, которое содержит сведения о многих товарах, можно использовать условия для выбора данных только для двух нужных продуктов.
• Извлечение данных на основе различных критериев при каждом запуске запроса Если вам нужно создать один и тот же Excel отчет или сводку для нескольких областей в одном и том же внешнем данных , например для отдельного отчета о продажах для каждого региона, можно создать запрос с параметрами. При запуске запроса с параметрами запрашивается значение, которое будет применяться в качестве критерия при выборе записей. Например, запрос с параметрами может потребовать ввести определенный регион и повторно использовать его для создания каждого из региональных отчетов о продажах.
• Различными способами для сбора данных Внутренние соединители, которые создает мастер запросов, являются наиболее распространенными типами, используемыми при создании запросов. Однако иногда необходимо использовать другой тип. Например, если у вас есть таблица сведений о продажах продуктов и таблица сведений о клиенте, внутреннее присоединиться (тип, созданный с помощью мастера запросов) предотвращает истощить записи клиентов, которые не сделали покупку. С помощью Microsoft Query вы можете объединить эти таблицы, чтобы получить все записи клиентов, а также данные о продажах для тех клиентов, которые сделали покупки.

Чтобы запустить Microsoft Query, выполните следующие действия:

1. На вкладке Данные в группе Внешние данные нажмите кнопку Из другихисточников и выберите из Microsoft Query.
2. В диалоговом окне Выбор источника данных убедитесь в том, что для создания и изменения запросов не заметен диалоговое окно Создание и изменение запросов с помощью мастера запросов.
3. Дважды щелкните нужный источник данных. ИЛИ Выберите нужный источник данных и нажмите кнопку ОК.

Повторное использование запросов и общий доступ Как в мастере запросов, так и в Microsoft Query можно сохранить запросы в качестве DQY-файла, который можно изменять, повторно использовать и совместно использовать. Excel можно открывать DQY-файлы напрямую, что позволяет создавать дополнительные диапазоны внешних данных из того же запроса.

Чтобы открыть сохраненный запрос из Excel:

1. На вкладке Данные в группе Внешние данные нажмите кнопку Из другихисточников и выберите из Microsoft Query. Отобразилось диалоговое окно Выбор источника данных.
2. В диалоговом окне Выбор источника данных перейдите на вкладку Запросы.
3. Дважды щелкните сохраненный запрос, который вы хотите открыть. Запрос отображается в Microsoft Query.

Если вы хотите открыть сохраненный запрос, а Microsoft Query уже открыт, откройте меню Файл запроса Майкрософт и нажмите кнопку Открыть.

Если дважды щелкнуть DQY-файл, Excel откроется, выполняется запрос, а затем вставляет результаты на новый.

Если вы хотите поделиться сводной Excel отчетом, основанным на внешних данных, вы можете предоставить другим пользователям книгу, содержаную диапазон внешних данных, или создать шаблон. Шаблон позволяет сохранить сводку или отчет без сохранения внешних данных, чтобы файл был меньше. Внешние данные извлекаются, когда пользователь открывает шаблон отчета.

Работа с данными в Excel

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

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

Excel форматирование новых данных, которые вы введите в конце диапазона, в соответствие с предыдущими строками. Excel также можно автоматически копировать формулы, которые повторялись в предыдущих строках, и распространять их на дополнительные строки.

Примечание: Чтобы расширить диапазон на новые строки, форматы и формулы должны отображаться по крайней мере в трех из пяти предыдущих строк.

Вы можете включить этот параметр (или отключить его снова) в любое время:

1. Щелкните Файл >Параметры >Дополнительно. В Excel 2007: нажмите кнопку Microsoft Office , Excel Параметры, а затем выберите категорию Дополнительные.
2. В разделе Параметры правки выберите параметр Расширить форматы диапазонов данных и формулы. Чтобы снова отключить автоматическое форматирование диапазона данных, сверните этот квадрат.

Обновление внешних данных. При обновлении внешних данных вы будете запускать запрос, чтобы получить все новые или измененные данные, которые будут совпадать с вашими спецификациями. Запрос можно обновить как в Microsoft Query, так и Excel.

Excel предлагает несколько вариантов обновления запросов, включая обновление данных при ее открытие и автоматическое обновление через определенные промежутки времени. Вы можете продолжать работать в Excel во время обновления данных, а также проверять состояние во время обновления данных. Дополнительные сведения см. в этойExcel.

Источник: support.microsoft.com

Начало работы в Power Query

Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.

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

Power Query в меню Excel

В зависимости от того, какая у вас версия Excel, вид надстройки Power Query может выглядеть по-разному. В Excel 2010 и 2013 надстройка появляется в виде отдельной вкладки «Power Query» (если у вас такой вкладки нет, прочитайте, как ее установить ).

В Excel после 2016 года Power Query уже встроен по умолчанию и находится в меню Данные → раздел Получить и преобразовать данные (в некоторых версиях Excel этот раздел называется Скачать

очищать данные и выполнять преобразования;

подготовленные данные выгружать на лист, в сводную таблицу или добавлять в модель данных Excel.

Таким образом, Power Query – это полноценный ETL-инструмент (Extract, Transform, Load).

Подключение к данным в Power Query

В Power Query можно подключать данные из самых разных источников: таблицы в самом файле и других Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в интернете, файлы xml и json, pdf-файлы, данные из канала OData и так далее. А также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.

Чтобы посмотреть, какие именно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (или Создать запрос, если у вас не новая версия Excel).

Для примера добавим в Power Query данные из таблицы.

• Выделите любую таблицу на листе Excel и перейдите в меню: — в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
  — для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).

В открывшемся окне поставьте галочку «Таблица с заголовками».

Таблица с данными при этом превратится в «умную» smart-таблицу.

• Откроется окно редактора запросов, в котором будет наша таблица. Нажимаем кнопку в меню Главная → Закрыть и загрузить. Готово!

Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.

Редактор запросов Power Query

Разберем подробнее интерфейс редактора запросов Power Query.

Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.

Итак, в редакторе Power Query есть:

1. Лента редактора запросов для вкладок меню: Главная, Преобразование, Добавить столбец, Просмотр.
2. Перечень созданных запросов, который можно свернуть / развернуть.
3. Строка формул.
4. Название самого запроса.
5. Примененные шаги запроса: записанные шаги получения или преобразования данных. Их можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.
6. Область предварительного просмотра, в которой выводится результат преобразования данных для каждого шага.
7. Меню для данных, которое открывается при нажатии правой кнопкой мышки.
8. При выборе правой кнопкой мыши названия шага появляется его контекстное меню.

Преобразование данных

Посмотрим на простом примере, как преобразовать данные в Power Query.

Допустим, у нас есть таблица с выручкой и расходами по городам за несколько лет. В таблице эти показатели разделены на две группы. Столбец с городами тоже имеет группировки (смотрите рисунок).

Если вы знакомы со сводными таблицами, то знаете, что построить сводную на основе таких данных не получится. Привести их в «нужный вид» можно в Power Query буквально за несколько щелчков мышкой:

• выделите таблицу (можно выделить таблицу целиком или одну из ячеек);
• выберите в меню Данные → Из таблицы (Из таблицы/диапазона);
• в появившемся окне поставьте галочку рядом с «Таблица с заголовками» → ОК;
• в открывшемся редакторе запросов выделите столбцы «показатель» и «город», нажав мышкой на названия столбцов с зажатым Ctrl;
• в меню нажмите Преобразование → Заполнить → Вниз.

• Готово! Пустые строки заполнены значениями из ячеек сверху.

При создании запроса Power Query сам автоматически записывает его шаги. Их можно увидеть в области справа Параметры запроса → Примененные шаги.

Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.

Обработка данных в Power Query выполняется последовательно, шаг за шагом, и каждое последующее действие использует результаты предыдущего. Поэтому при добавлении новых шагов или изменении их последовательности обязательно проверьте, все ли в порядке со следующими операциями. Проверить, все ли в порядке, можно, нажав на самый нижний шаг.

Кроме простых операций с данными, Power Query умеет выполнять и другие действия: сортировать, фильтровать, заменять, группировать, заполнять пустые значения, удалять дубликаты, работать с текстом и числами, выполнять простые вычисления, транспонировать таблицы и разворачивать их столбцы, объединять данные и многое-многое другое.

Источник: finalytics.pro