7. Полезняшки Excel

Глава 15. Транспонирование и сложные случаи обратного разбора сводных таблиц в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Обратный разбор сводной таблицы в Power Query был рассмотрен в главе 6. Однако, могут встречаться и более сложные ситуации, например, сводные таблицы с подкатегориями. Также иногда нужно преобразовать данные, расположенные горизонтально, в обычные вертикальные таблицы. Обе эти функции, к счастью, встроены в пользовательский интерфейс Power Query. Допустим, у вас есть красивая сводная таблица, которую нужно разобрать для последующего анализа:

Ris. 15.1. Svodnaya tablitsa s podkategoriyami trebuet razbora

Рис. 15.1. Сводная таблица с подкатегориями требует разбора

Читать полностью

Глава 14. Группировка и подведение итогов в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

Ris. 14.1. Prodazhi futbolok

Рис. 14.1. Продажи футболок

Читать полностью

Глава 13. Загрузка данных из Exchange в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

Ris. 13.1. Okno podklyucheniya k Microsoft Exchange

Рис. 13.1. Окно подключения к Microsoft Exchange

Читать полностью

Глава 12. Импорт веб-данных в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

В качестве примера попытается выяснить, в каких странах лучше всего продавать ваш товар. Вы предполагаете, что это ТОП-10 стран с самым большим населением. Вы нашли нужные данные на веб-сайте ЦРУ. Создайте новую книгу Excel, пройдите по меню Данные –> Из интернета. При запросе URL-адреса введите: https://www.cia.gov/library/publications/the-world-factbook/rankorder/2119rank.html. Нажмите Ok. Вам будет предложено ввести параметры проверки подлинности:

Ris. 12.1. Dialogovoe okno veb autentifikatsii

Рис. 12.1. Диалоговое окно веб-аутентификации

Читать полностью

Глава 11. Определение типов данных в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

Ris. 11.1. Ishodnaya tablitsa v Excel

Рис. 11.1. Исходная Таблица в Excel

Читать полностью

Глава 10. Куда загружаются запросы Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Хотя большинство примеров в предыдущих главах заканчивалось загрузкой запроса Power Query в Таблицы Excel, это – не единственный вариант для размещения данных. По большому счету, у вас есть три варианта загрузки, некоторые из которых можно использовать в комбинации: Таблицы Excel, только подключение, модель Данных PowerPivot.

Ris. 10.1. Dva zaprosa tolko dlya podklyucheniya gotovye k dalnejshemu ispolzovaniyu

Рис. 10.1. Два запроса только для подключения, готовые к дальнейшему использованию

Читать полностью

Глава 9. Объединение таблиц и запросов Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

Ris. 9.1. Ishodnye Tablitsy na liste Excel

Рис. 9.1. Исходные Таблицы на листе Excel

Читать полностью

Глава 8. Импорт в Power Query из баз данных

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Ваша организация может предоставить вам доступ к базам данных. Загрузка из баз данных, как правило, более эффективна, чем загрузка из файлов. Для подключения укажите расположение базы данных, введите логин и пароль, выберите таблицы, с которыми вы хотите работать. Power Query поддерживает подключение к большому разнообразию баз данных без необходимости установки каких-либо дополнительных драйверов. Доступные подключения можно найти в Excel на вкладке Данные –> Получить данные –> Из базы данных, Из Azure, Из других источников.

Ris. 8.1. Interfejs podklyucheniya k bazam dannyh

Рис. 8.1. Интерфейс подключения к базам данных

Читать полностью

Глава 7. Импорт больших текстовых файлов в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

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

Power Query автоматически решает большинство из этих проблем. Создайте новую книгу Excel, и новый запрос: Данные –> Из текстового/CSV-файла. Загрузите файл с примерами GL JanMar.txt. В окне предварительного просмотра нажмите Изменить. Power Query помещает данные в один столбец:

Ris. 7.1. Import nestrukturirovannogo tekstovogo fajla

Рис. 7.1. Импорт неструктурированного текстового файла; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Читать полностью

Глава 6. Обратный разбор сводной таблицы в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

Ris. 6.1. Otchet v formate svodnyj tablitsy

Рис. 6.1. Отчет в формате сводный таблицы (см. файл UnPivot.xlsx); чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Читать полностью