Глава 8. Работа с наружными данными в Таблицах Excel

Это продолжение перевода книжки Зак Барресс и Кевин Джонс. Таблицы Excel: Полное управление для сотворения, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.). Получение и хранение большущего количества данных сделалось принципиальной частью современного бизнес-ландшафта, и анализ таковых данных становится все наиболее сложным. Чтоб совладать с данной для нас тенденцией, Microsoft продолжает добавлять в Excel средства обработки запросов и анализа данных.

Крайнее дополнение – это набор инструментов BI (business intelligence). Эти инструменты дают возможность представить данные при помощи обычных инструментов, таковых как Таблицы, сводные таблицы и диаграммы, чтоб отлично поведать историю. До сего времени мы изучали роль Таблиц в рабочей книжке. В данной для нас главе рассматривается, как Excel употребляет Таблицы для обработки наружных данных.

Рис. 8.1. Группа Получить и конвертировать данные вкладки Данные

Скачать заметку в формате Word либо pdf, примеры в формате Excel

Подключение к наружным данным

Вы сможете получить доступ к наружным источникам через вкладку Данные, группу Получить и конвертировать данные (рис. 8.1). Подключения к данным хранятся вкупе с книжкой, и вы сможете просмотреть их, выбрав пункт Данные –> Запросы и подключения.

Подключение к данным быть может отключено на вашем компе. Для подключения данных пройдите по меню Файл –> Характеристики –> Центр управления сохранностью –> Характеристики центра управления сохранностью –> Наружное содержимое. Установите переключатель на одну из опций: включить все подключения к данным (не рекомендуется) либо запрос на подключение к данным.

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

Подробнее о подключении к наружным источникам данных см. Кен Пульс и Мигель Эскобар. Язык М для Power Query. При использовании таблиц, присоединенных к данным можно переставлять и удалять столбцы, не изменяя запрос. Excel продолжает сопоставлять запрошенные данные с правильными столбцами. Но ширина столбцов обычно автоматом устанавливается при обновлении. Чтоб запретить Excel автоматом устанавливать ширину столбцов Таблицы при обновлении, щелкните правой клавишей мыши в любом месте Таблицы и пройдите по меню Конструктор –> Данные из наружной таблицы –> Характеристики, а потом снимите флаг Задать ширину столбца.

Рис. 8.3. Характеристики Таблицы, присоединенной к наружным данным

Подключение к базе данных

Для подключения к базе данных SQL Server изберите Данные –> Получить данные –> Из базы данных –> Из базы данных SQL Server. Покажется мастер подключения к данным, предлагающий элементы управления для указания имени сервера и типа входа, который будет употребляться для открытия соединения. Обратитесь к собственному админу SQL Server либо ИТ-администратору, чтоб выяснить, как ввести учетные данные для входа.

Рис. 8.4. Подключение к базе данных SQL Server

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

Существует много разных типов доступных источников данных, и время от времени шаблоны соединений по дефлоту, выставленные Excel, не работают. Посетите веб-сайт https://www.connectionstrings.com/. Там есть много примеров строк подключения, статьи и форум вопросцев и ответов.

Имя Таблицы

Excel импортирует каждую избранную таблицу базы данных в новейшую Таблицу Excel на новеньком листе в активной книжке. Он делает имена Таблиц в формате Table_Name, где Name – это имя таблицы базы данных либо представления в базе данных SQL Server. При импорте одной таблицы имя таблицы будет Table_ServerName_ DatabaseName_TableName, где ServerName – имя сервера, DatabaseName – имя базы данных, а TableName – имя импортируемой таблицы.

Работа с текстовыми файлами

Текстовые файлы обычно представлены в виде CSV-файла (значения, разбитые запятыми), txt-файла (с разделителями табуляции) либо PRN-файла (с фиксированными полями либо пробелами). Excel как и раньше делает подключение к данным при импорте текстовых файлов, но не помещает данные в Таблицу. Это соединено с отсутствием инфы о данных, содержащихся в текстовом файле. Базы данных имеют определенные правила, такие как неповторимые имена полей/столбцов, в то время как текстовые файлы не соединены этими правилами. Excel загружает текстовые данные в запрос; Excel не пробует принудительно поместить данные в Таблицу либо иной структурированный формат.

Подключение к текстовому файлу

Чтоб избрать текстовый файл для импорта, пройдите по меню Данные –> Получить и конвертировать данные –> Из текстового/CSV-файла. Форматы файлов по дефлоту – PRN, TXT и CSV. Опосля выбора файла Excel открывает диалоговое окно:

Текст файл был подготовлен создателями книжки в англо-американской традиции, потому перед размещением данных в книжке Excel, их необходимо доработать. Кликните клавишу Конвертировать данные, и измените формат столбцов OrderDate, Cost и Total используя локаль (подробнее см. Изменение опций Power Query, работающих по дефлоту).

Рис. 8.6. Данные опосля преобразования в редакторе Power Query

Сейчас данные можно поместить на лист Excel. Кликните Закрыть и загрузить. Excel по дефлоту расположит данные в Таблицу:

Рис. 8.7. Обработанные данные из текстового файла в Таблице Excel

Чтоб обновить подключение к текстовому файлу, нужно обновить запрос.

Работа с данными из Azure Marketplace

Azure Marketplace является хранилищем данных и приложений анализа данных. Это пасмурная инфраструктура и платформа, которая употребляется для сотворения, развертывания и управления пасмурными службами. Данные, доступные в Azure Marketplace, можно экспортировать в текстовые файлы, поместить в модель данных Power Pivot либо конкретно в Таблицу. Если запрашиваемый набор записей превосходит 1 млн, его придется импортировать в модель данных, которая поддерживает неограниченное число строк.

Для подключения к Azure Marketplace из Excel требуется URL-адрес данных и ключ учетной записи. На домашней страничке Azure Marketplace щелкните ссылку Databases. Покажется страничка со перечнем наборов данных; некие из их бесплатны, а некие доступны за плату. Изберите один из бесплатных источников данных. Пройдите регистрацию. Опосля подключения к набору данных Azure Marketplace это соединение становится таковым же, как и хоть какое другое, и его можно обновить, чтоб работать с самыми крайними данными. Имейте в виду, что каждое соединение считается трансакцией.