Rivalry2

HTML Diff

0 added 0 removed

Original 2026-01-01

Modified 2026-02-21

1 <a>#Руководства</a>

2 <ul><li>17 май 2023</li>

3 <li>0</li>

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

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

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

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

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

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

10 <ul><li>Что<a>умеет</a>надстройка Power Query</li>

11 <li>Как<a>включить</a>Power Query в Excel</li>

12 <li>Как<a>загрузить</a>и <a>преобразовать</a>данные</li>

13 <li>Как<a>импортировать</a>данные из Power Query</li>

14 <li>Как<a>узнать больше</a>о работе в Excel</li>

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

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

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

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

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

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

21 <ul><li>менять тип данных - например, изменить числовой формат на формат даты, чтобы день и месяц отображались корректно;</li>

22 <li>менять регистр букв - это будет полезно, например, когда нужно в тысячах строк заменить строчные буквы на прописные;</li>

23 <li>очистить данные от лишних элементов - например, удалить лишние пробелы, пустые столбцы и строки, повторяющиеся значения ячеек;</li>

24 <li><a>сортировать</a>и <a>фильтровать</a>данные, изменять порядок столбцов и строк;</li>

25 <li>разделять единый текст на столбцы или, наоборот, объединять столбцы в единый текст;</li>

26 <li>проводить необходимые расчёты - например, суммировать данные или рассчитывать процент;</li>

27 <li>подставлять значения из одной таблицы в другую - как с помощью<a>функции ВПР (Vlookup)</a>в Excel.</li>

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

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

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

31 <ul><li>Выгрузить на лист Excel - тогда на этом листе появится смарт-таблица с данными из Power Query.</li>

32 <li>Создать<a>сводную таблицу</a>или сводную диаграмму.</li>

33 <li>Добавить данные Power Pivot и построить из них<a>модель данных</a>.</li>

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

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

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

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

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

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

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

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

42 Запускаем Power Query в версии Excel 2019 годаСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Дальше нужно выбрать, откуда выгружать данные, преобразовать их в редакторе Power Query и импортировать дальше. Как это делать - разбираем на примере в следующих разделах.

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

44 Так выглядит справочник товаровСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"На вкладке Excel "Данные" в разделе "Получить и преобразовать данные" нажимаем кнопку "Получить данные".

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

46 Выбираем источник и формат данных для выгрузки в Power QueryСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"В появившемся окне выбираем наш XLS-файл для выгрузки и нажимаем "Открыть".

47 Выбираем файл для выгрузки в Power QueryСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Появляется окно навигатора. В нём выбираем, какую таблицу из XLS-файла нужно выгрузить в Power Query. Можно выгрузить все листы файла или какие-то конкретные.

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

49 Выбираем в файле Excel таблицу, которую нужно загрузить в Power QueryСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Готово - Excel открывает окно редактора Power Query, в котором мы сможем обработать данные, полученные из справочника товаров.

50 Открывается окно редактора Power Query для дальнейшей обработки загруженных данныхСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"В открывшемся редакторе мы можем внести изменения в выгруженную таблицу.

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

52 <ul><li>изменить формат данных в столбце "ID-товара";</li>

53 <li>изменить регистр букв в столбце "Формат";</li>

54 <li>удалить лишние пробелы в столбце "Книга".</li>

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

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

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

58 Изменяем формат данных с числового на текстовыйСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Готово - теперь ID товаров отображаются корректно.

59 Результат смены формата данныхСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Изменяем регистр букв.В нашем примере все значения столбца "Формат" написаны в нижнем регистре - нужно сделать так, чтобы они начинались с прописной буквы.

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

61 Изменяем регистр буквСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Готово - теперь все слова столбца "Формат" начинаются с прописной буквы.

62 Результат смены регистра буквСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Удаляем лишние пробелы.Для этого нажимаем правой кнопкой мыши на название столбца, в который нужно внести изменение. Выбираем "Преобразование" и затем "Усечь".

63 Удаляем лишние пробелыСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Готово - теперь во всех ячейках столбца нет пустых участков перед текстом.

64 Результат удаления лишних пробеловСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Аналогично можно вносить другие изменения - например, отсортировать таблицу по алфавиту или найти повторяющиеся книги и удалить их.

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

66 Все выполненные в редакторе действия отображаются в блоке "Применённые шаги"Скриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Итак, мы внесли необходимые изменения в данные таблицы. Теперь эти данные можно просто сохранить в полученном виде, создать из них сводную таблицу, сводную диаграмму или добавить в модель Power Pivot.

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

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

69 Закрываем редактор Power Query, чтобы импортировать данные дальшеСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"В появившемся окне выберем способ импорта. В нашем случае нужно выбрать "Только создать подключение", поставить галочку рядом с "Добавить эти данные в модель данных" и нажать "ОК".

70 Выбираем способ импорта преобразованных данных таблицыСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Готово - мы сохранили преобразованную ранее таблицу и импортировали её в Power Pivot.

71 Теперь с импортированной таблицей можно работать в Power PivotСкриншот: курс Skillbox "<a>Excel + Google Таблицы с нуля до PRO</a>"Подробнее о том, как работать с выгруженными данными дальше в Power Pivot - например, связать их с данными других таблиц и создать удобные отчёты для анализа, - мы говорили в <a>этой статье</a>.

72 <ul><li>В Excel много функций, которые упрощают и ускоряют работу с таблицами. В <a>подборке</a> даём ссылки на 15 статей и видео об инструментах Excel, необходимых в повседневной работе.</li>

73 <li>В Skillbox есть курс "<a>Excel + Google Таблицы с нуля до PRO</a>". Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.</li>

74 <li>Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива "<a>Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня</a>". Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.</li>

75 </ul><a>Курс-тренажёр: "Excel + "Google Таблицы" с нуля до PRO" Узнать о курсе</a>