Запросы в эксель как делать

Использование выражения в качестве выходного поля

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

1. В пустом столбце бланка запроса выберите строку Поле.
2. На вкладке Конструктор в группе Настройка запроса нажмите кнопку Построитель.
3. Введите или вставьте необходимое выражение. Перед выражением введите имя, которое хотите использовать для результата выражения, а после него — двоеточие. Например, чтобы обозначить результат выражения как «Последнее обновление», введите перед ним фразу «Последнее обновление:». Для создания выражений вы также можете воспользоваться построителем выражений, чтобы задействовать доступные элементы, категории и значения выражений.

Примечание: Выражения — это мощный и гибкий инструмент со множеством параметров. Дополнительные сведения см. в этой статьи.

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

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

Если задать альтернативные условия, записи буду отбираться, когда значение поля будет удовлетворять хотя бы одному из указанных условий. Например, если задействовать поле Штат из таблицы клиентов и указать в качестве условия CA, а в строке или ввести ID и WA, будут отображаться записи клиентов, живущих в одном из этих трех штатов.

1. При необходимости добавьте в бланк запроса в нижней области строку Всего. В режиме конструктора на вкладке Конструктор найдите группу Показать или скрыть и нажмите кнопку Итоги.
2. Для каждого необходимого поля в строке Групповая операция выберите нужную функцию. Доступные функции будут зависеть от типа данных в поле.

Начало работы в 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 этот раздел называется Скачать & преобразовать).

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

Power Query умеет:

• напрямую подключаться к данным в различных источниках;
• очищать данные и выполнять преобразования;
• подготовленные данные выгружать на лист, в сводную таблицу или добавлять в модель данных 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 умеет выполнять и другие действия: сортировать, фильтровать, заменять, группировать, заполнять пустые значения, удалять дубликаты, работать с текстом и числами, выполнять простые вычисления, транспонировать таблицы и разворачивать их столбцы, объединять данные и многое-многое другое.

Power Query в Excel: для чего это нужно и как работает. Инструкция со скриншотами

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

Иллюстрация: Meery Mary для Skillbox Media

Ксеня Шестак

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

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

Если в Excel без надстроек можно обрабатывать таблицы только из 1 048 576 строк, то в Power Query количество строк не ограничено. А в отличие от Power Pivot, похожей надстройки Excel, Power Query поддерживает гораздо больше форматов источников. Также в нём больше функций для редактирования данных.

Рассказываем, какие возможности даёт Excel Power Query, и разбираем на примере, как им пользоваться. Статья будет полезна специалистам, которые работают с большими базами данных — собирают информацию из разных источников, преобразовывают и анализируют её.

• Что умеет надстройка Power Query
• Как включить Power Query в Excel
• Как загрузить и преобразовать данные
• Как импортировать данные из Power Query
• Как узнать больше о работе в Excel

При подготовке статьи мы использовали материалы курса Skillbox «Excel + Google Таблицы с нуля до PRO».

Что умеет надстройка Power Query

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

Разберём каждый этап подробнее.

Подключение к источникам и загрузка данных. Power Query позволяет выгружать данные из разных источников и поддерживает практически все форматы файлов.

Например, из Power Query можно подключиться к файлам XLS, TXT, PDF, CSV, JSON, HTML, XML. Также можно выгрузить информацию из разных баз данных — например, MS Access и MS SQL Server; из систем ERP, программ «1C», облачных хранилищ, Google Analytics, «Яндекс Метрики» и других сервисов.

При этом можно одновременно получать данные нескольких источников — например, всех файлов, лежащих в одной папке, или всех листов файла Excel.

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

• менять тип данных — например, изменить числовой формат на формат даты, чтобы день и месяц отображались корректно;
• менять регистр букв — это будет полезно, например, когда нужно в тысячах строк заменить строчные буквы на прописные;
• очистить данные от лишних элементов — например, удалить лишние пробелы, пустые столбцы и строки, повторяющиеся значения ячеек;
• сортировать и фильтровать данные, изменять порядок столбцов и строк;
• разделять единый текст на столбцы или, наоборот, объединять столбцы в единый текст;
• проводить необходимые расчёты — например, суммировать данные или рассчитывать процент;
• подставлять значения из одной таблицы в другую — как с помощью функции ВПР (Vlookup) в Excel.

При этом данные будут форматироваться только в редакторе Power Query — в файлах-источниках они останутся без изменений.

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

Импортировать данные можно тремя способами:

• Выгрузить на лист Excel — тогда на этом листе появится смарт-таблица с данными из Power Query.
• Создать сводную таблицу или сводную диаграмму.
• Добавить данные Power Pivot и построить из них модель данных.

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

Напомним, одно из главных преимуществ Power Query в том, что в нём нет ограничений по объёму данных, с которыми он может работать без потери производительности. Excel без надстроек позволяет работать только с 1 048 576 строками.

В следующих разделах расскажем, где найти Power Query в Excel, и разберём на примере, как с ним работать.

Как запустить Power Query

Power Query — бесплатная надстройка Excel. Она доступна для всех версий программы, начиная с 2010 года.

Версии Excel 2010 и 2013 года. Power Query нужно скачивать отдельно — например, с сайта Microsoft.

После установки надстройка отобразится в виде отдельной вкладки Power Query на главной панели Excel. Нужно открыть эту вкладку и нажать на кнопку «Из файла».

Версии Excel 2016 года и новее. Power Query скачивать отдельно не нужно — надстройка есть в Excel по умолчанию. Чтобы её запустить в Excel 2016 года, нужно на вкладке «Данные» выбрать раздел «Скачать и преобразовать». Затем нажать кнопку «Создать запрос».

В Excel 2019 года — на вкладке «Данные» выбрать раздел «Получить и преобразовать данные» и нажать на кнопку «Получить данные».

Дальше нужно выбрать, откуда выгружать данные, преобразовать их в редакторе Power Query и импортировать дальше. Как это делать — разбираем на примере в следующих разделах.

Загружаем в Power Query данные из внешнего источника

Для примера выгрузим в Power Query справочник товаров книжного магазина в формате XLS. В нём перечислены названия книг, их формат и ID‑номера.

На вкладке Excel «Данные» в разделе «Получить и преобразовать данные» нажимаем кнопку «Получить данные».

Дальше выбираем источник и формат файла, из которого нужно выгрузить таблицу. В нашем случае это «Из файла» → «Из книги Excel».

В появившемся окне выбираем наш XLS-файл для выгрузки и нажимаем «Открыть».

Появляется окно навигатора. В нём выбираем, какую таблицу из XLS-файла нужно выгрузить в Power Query. Можно выгрузить все листы файла или какие-то конкретные.

В нашем примере выберем первый лист — «Таблица 1» — и нажмём «Преобразовать данные».

Готово — Excel открывает окно редактора Power Query, в котором мы сможем обработать данные, полученные из справочника товаров.

Преобразовываем данные в Power Query

В открывшемся редакторе мы можем внести изменения в выгруженную таблицу.

В нашем случае нужно:

• изменить формат данных в столбце «ID-товара»;
• изменить регистр букв в столбце «Формат»;
• удалить лишние пробелы в столбце «Книга».

Изменяем формат данных. Автоматически формат столбца «ID-товара» определился как числовой, поэтому в номерах ID удалились лишние нули. Чтобы они снова появились, нужно изменить числовой формат на текстовый.

Подробнее о форматах ячеек в Excel рассказывали в этой статье Skillbox Media.

Чтобы поменять формат в Power Query, нажимаем на значок «123» слева от названия столбца и выбираем нужный формат — «Текст».

Готово — теперь ID товаров отображаются корректно.

Изменяем регистр букв. В нашем примере все значения столбца «Формат» написаны в нижнем регистре — нужно сделать так, чтобы они начинались с прописной буквы.

Для этого правой кнопкой мыши нажмём на название столбца. В появившемся контекстном меню выберем «Преобразование» → «Каждое Слово С Прописной». Если в ячейках будет несколько слов, то каждое слово будет начинаться с прописной. В нашем примере в ячейках по одному слову, поэтому эта функция подходит.

Готово — теперь все слова столбца «Формат» начинаются с прописной буквы.

Удаляем лишние пробелы. Для этого нажимаем правой кнопкой мыши на название столбца, в который нужно внести изменение. Выбираем «Преобразование» и затем «Усечь».

Готово — теперь во всех ячейках столбца нет пустых участков перед текстом.

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

Все выполненные в редакторе Power Query действия автоматически записались в виде шагов запроса — в блоке «Применённые шаги» в правой области редактора. При необходимости можно нажать правой кнопкой мыши на любое действие и удалить его — это вернёт прежний вид данных таблицы.

Импортируем данные из Power Query

Итак, мы внесли необходимые изменения в данные таблицы. Теперь эти данные можно просто сохранить в полученном виде, создать из них сводную таблицу, сводную диаграмму или добавить в модель Power Pivot.

Покажем, как сделать последнее.

Нажмём кнопку «Закрыть и загрузить» в левой части верхнего меню, затем — «Закрыть и загрузить в…».

В появившемся окне выберем способ импорта. В нашем случае нужно выбрать «Только создать подключение», поставить галочку рядом с «Добавить эти данные в модель данных» и нажать «ОК».

Готово — мы сохранили преобразованную ранее таблицу и импортировали её в Power Pivot.

Подробнее о том, как работать с выгруженными данными дальше в Power Pivot — например, связать их с данными других таблиц и создать удобные отчёты для анализа, — мы говорили в этой статье.

Как узнать больше о работе в Excel

• В Excel много функций, которые упрощают и ускоряют работу с таблицами. В подборке даём ссылки на 15 статей и видео об инструментах Excel, необходимых в повседневной работе.
• В Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
• Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.

Другие материалы Skillbox Media по Excel

• Как сделать ВПР в Excel
• Основы Excel: работаем с выпадающим списком
• Как сделать сортировку в Excel
• Как установить фильтр и расширенный фильтр в Excel
• Логические функции в Excel: для чего нужны и как их использовать

Extract, transform, load — извлечение, преобразование и загрузка.