Excel и внешние данные ГЛАВА 2
1 Excel и внешние данные ГЛАВА 2 В этой главе: Подключение к базе данных Microsoft Access Данные с веб-страниц и текстовых файлов Данные из баз данных Программа Microsoft Query Работа с файлами XML Большим преимуществом Microsoft Excel является относительно простое подключение к внешним источники данных. Мы можем создавать из них таблицы и сводные таблицы, а можем просто настроить подключение к другой таблице Excel с помощью функции ПОИСК. Все эти соединения относительно легко настроить, и их можно использовать в различных местах Microsoft Excel. Универсальным способом данные из них можно импортировать в таблицу или создать сводную таблицу напрямую. Microsoft Excel 2013, особенно версия Professional Plus, добавляет ряд дополнительных источников данных в этой области, особенно с надстройкой PowerPivot. Затем данные можно легко обработать с помощью инструментов Excel. Любой, кто когда-либо обрабатывал данные из больших баз данных с помощью специальных инструментов обработки данных, оценит простую, мощную и относительно недорогую обработку данных в Excel. Существует довольно много внешних источников, из которых вы можете получить данные в Excel. Эти внешние источники должны быть сначала доступны для Excel. Относительно большое количество ресурсов доступно сразу после установки Microsoft Office, другие доступны после установки систем баз данных (например, Oracle) или их необходимо устанавливать вручную (MySQL). Впоследствии мы можем подключиться несколькими способами: Самое простое прямое подключение — к другим продуктам Microsoft, особенно к базам данных Microsoft Access, службам данных и MS SQL Server. Данные можно извлекать из большинства систем баз данных с помощью соединений ODBC или OLEDB, которые являются универсальными методами. Данные из текстовых файлов, из таблиц навеб-страницы из файлов XML и в виде таблиц, связанных с помощью функций, получаются определенным образом. 45
2 ГЛАВА 2 Excel и внешние данные Подключение базы данных Microsoft Access Программа базы данных Microsoft Access 2013 является частью офисного пакета Microsoft Office Professional Plus Базы данных, созданные в этой программе, также можно подключать из Microsoft Excel По сравнению с подключением к другим внешним источников данных, подключение к базе данных Microsoft Access очень простое, в основном вам нужно только выбрать расположение базы данных Microsoft Access. Поскольку параметр «Из доступа» также находится первым в группе «Загрузить внешние данные» на вкладке «Данные», мы начинаем с этого подключения. На нем мы также покажем общие процедуры создания соединений и их использования. Подключение к другим источникам данных работает по тому же принципу. Загрузка данных из Access Одноразовая загрузка данных из Access выполняется просто на вкладке Данные в группе Загрузить внешние данные с помощью команды Из Access. В этом случае нам не нужно выбирать какой-либо источник данных, просто выберите сам файл базы данных Access. Создание соединения Если мы планируем постоянное соединение с источником данных, мы должны создать так называемое соединение, которое станет частью рабочей книги. Можно установить, что обновление данных будет происходить автоматически, и, конечно, это будет только односторонний доступ к Excel. Создаем его с помощью команды «Соединение» в группе «Соединение» на вкладке «Данные». Впоследствии в диалоговом окне подключения к рабочей книге мы используем кнопку «Добавить», а затем у нас есть два варианта: мы используем существующие файлы подключения (мы выбираем их). Мы можем найти больше, используя кнопку «Найти больше». В нашем случае мы будем использовать второй вариант, а затем в диалоговом окне «Выбор источника данных» мы найдем соответствующую базу данных, файл базы данных Microsoft Access. В общем случае на данный моментнеобходимо выбрать сохраненный файл подключения к определенному источнику данных. 46
3 Подключение к базе данных Microsoft Access Рисунок 2.1: Подключение к таблице базы данных Microsoft Access Примечание: Программа Microsoft Access использует в качестве баз данных файлы типа ACCDB, начиная с версии 2007, в более старых версиях это были файлы типа MDB; в зависимости от выбранного типа файла мы можем подключиться к обоим типам. Мы также можем изменить параметр «Все источники данных» на определенные файлы базы данных. Свойства подключения В приведенном выше диалоговом окне «Подключение к рабочей книге» можно использовать кнопку «Свойства», чтобы отобразить параметры выбранного подключения. Это можно найти на двух вкладках: Здесь используется, прежде всего, установка опции автоматического обновления данных. Определение содержит определенные параметры соединения 47
4 ГЛАВА 2 Excel и внешние данные Рисунок 2.2: Свойства соединения с базой данных Microsoft Access Примечание. Параметр «Тип команды» на вкладке «Определение» теперь установлен на «Таблица». Если бы это было значение SQL, кнопка «Редактировать запрос» была бы активной, и ее можно было бы изменить с помощью Microsoft Query. Примечание. Свойства соединения могут отображаться и в других местах, например, при импорте внешних данных в сводную таблицу (в диалоговом окне «Импорт данных» после нажатия кнопки «Свойства»). Параметры обновления Обновление подключенных данных задается в свойствах подключения и поэтому может быть: Автоматическим через указанное время или при открытии файла. Принудительно с помощью кнопки «Обновить» в диалоговом окне «Подключить книгу» или с помощью кнопки «Обновить все» на вкладке «Данные». Примечание. В диалоговом окне «Подключение к книге» можно просмотреть информацию о состоянии подключения и возможных неудачных обновлениях. 48
5 Данные с веб-страниц и текстовых файлов Совет: Соединение с источником данных также можно отменить, нажав правую кнопку мыши в таблице.выберите «Таблица», а затем «Отключиться от источника данных». Данные с веб-страниц и текстовых файлов Данные с веб-страниц Мы также можем импортировать данные с веб-страниц (точнее, из html-таблиц на этих страницах) в Microsoft Excel. Довольно интересная опция, позволяющая, например, обновлять курсы валют в таблицах Excel. Однако на практике это связано с рядом проблем: Невозможно импортировать данные из всех таблиц. Например, сводные таблицы нельзя создавать непосредственно из данных, их необходимо импортировать в виде списка и только дальше работать с ними. Поэтому, в конце концов, альтернативные процедуры, как правило, лучше, например, ручное сохранение таблицы или текстовых данных (в формате CSV, с которым Excel может работать напрямую). После импорта данных автоматически создается новое соединение, которым мы можем продолжать пользоваться и устанавливать время обновления. Если мы хотим создавать сводные таблицы непосредственно из данных, импортируя их, в данном случае это нельзя сделать напрямую. Данные с сайта нужно сначала импортировать в список, и только потом из них можно создать сводную таблицу. Порядок получения данных из таблиц на веб-сайтах: Мы подготовим веб-адрес, к которому мы хотим подключиться. На вкладке «Данные» мы будем использовать кнопку «Из Интернета» в группе «Загрузить внешние данные». Вводим выбранный веб-адрес в окно специального браузера, либо постепенно попадаем на конкретную страницу. Если наша таблица имеет желтую стрелку в левом верхнем углу, ее можно выбрать, щелкнув по ней, а затем после нажатия кнопки Импорт (справа внизу) появится диалог Импорт данных и данные будут размещены на листе в виде список. Соединение автоматически сохраняется как соединение с рабочей книгой и может использоваться. Данные из текстовых файлов Microsoft Excel может работать с текстовыми файлами несколькими способами: Он может импортировать из текстового файла в рабочий лист.Текстовый файл можно определить как подключение к книге (со всеми свойствами подключения, включая автоматические обновления). Текстовый файл может стать источником данных, из которого можно импортировать данные. 49
6 ГЛАВА 2 Excel и внешние данные Примечание. Текстовые файлы чаще всего имеют формат TXT (обычный текст с определенным разделителем полей) или CSV (полуразделенные текстовые поля). Импорт текста в рабочую книгу Если текстовый файл имеет подходящий формат с разделителями текстовых полей, его можно импортировать на лист Excel: 1. Обычно мы импортируем в новую рабочую книгу с помощью параметра «Открыть файл», а затем переходим к соответствующему текстовому файлу. 2. Если это файл CSV, он загружается непосредственно в книгу (Microsoft Excel может напрямую работать с этим типом файла). В других случаях запустится мастер импорта текста. 3. В Мастере импорта текста мы будем постепенно вводить соответствующие данные (тип файла, кодировку, разделители и т. д.), а в последней части мастера мы также можем выбрать, какие столбцы мы не хотим импортировать (мы выбираем столбец в предварительном просмотре и используйте переключатель Не импортировать столбец). 4. После нажатия кнопки Готово текстовый файл будет импортирован на лист. Рисунок 2.3: Мастер импорта текста, затем вам нужно будет ввести тип разделителя и выбрать поле Предупреждение: это не подключение, а просто импорт данных. 50
7 Данные из баз данных Текстовый файл в качестве источника данных Другой вариант — сделать текстовый файл источником данных и дополнительно установить соединение с временем обновления. Примечание. Этот метод загружает данные только в виде списка на лист, его нельзя использовать для непосредственного создания сводной таблицы. Процедура: 1. На вкладке Данные используйте команду Существующее соединение в группе Загрузить внешние данные, а затем Искать еще. 2. Выберите соответствующий текстовый файл и нажмите кнопку Открыть. 3. Пройдем еще разМастер импорта текста, после чего появится диалоговое окно «Импорт данных». Это не позволит создать сводную таблицу, а только разместит данные на листе. 4. Соединение создается автоматически, и его можно редактировать, как и любое другое соединение для передачи данных, с помощью кнопки «Подключения» на вкладке «Данные». Примечание. Также можно выбрать противоположный подход, сначала создать соединение (команда «Соединение» на вкладке «Данные», затем кнопка «Добавить»), а затем изменить его свойства. Данные из баз данных Внешним источником данных чаще всего являются реляционные базы данных. Доступ к этим данным можно получить двумя способами: Прямое подключение к таблицам базы данных. Импортируя данные из базы данных в лист Excel, а затем, например, создавая таблицу непредвиденных обстоятельств. Рекомендуется заранее протестировать соединение с базой данных. Также может потребоваться установка различных вспомогательных программ (драйверов, ODBC). На практике мы можем столкнуться с различными проблемами: Прямое подключение обычно только к ресурсам Microsoft (например, база данных Microsoft Access) без особых проблем. Вы можете подключаться к другим источникам с помощью ODBC (Open Database Connectivity), возможно, используя Microsoft Data Access (OLE DB) или канал данных. При наличии нескольких вариантов подключения к источнику данных целесообразно проверить их надежность (например, возможно подключение к базе данных Oracle с помощью ODBC-драйвера от Microsoft или Oracle). Проблема 32-разрядных и 64-разрядных систем: сейчас широко используются 64-разрядные операционные системы и аналогичные 64-разрядные базы данных. Теоретически должно быть возможно без проблем использовать все программы в 64-битной версии, но на практике все обстоит иначе. Не все компоненты офисного пакета Micro-51
8 ГЛАВА 2 Данные Excel и внешних программных продуктов Office, включая надстройки, являются полностью 64-разрядными, и, кроме того, в 64-разрядной операционной системе Microsoft Windows имеются две версии ресурсов ODBC: 32-разрядная и 64-разрядная. -битные версии.Практичнее использовать комбинацию 64-битной операционной системы Microsoft Windows, 64-битной реляционной базы данных (если она имеется, иначе она, конечно, будет 32-битной) и 32-битной версии Microsoft Office. Если у нас есть, например, 64-битная система Microsoft Windows 7, программе источников данных (ODBC) соответствует исполняемый файл odbcad32.exe в 64-битной версии в каталоге System32. Его 32-битный аналог называется так же и находится, как ни странно, в системном каталоге SysWOW64. Если мы запускаем программу Источники данных (ODBC) из Панели управления, а затем из группы Администрирование в 64-битной системе, запускается ее 64-битная версия. Настройка соединения ODBC Настройка соединения происходит в несколько этапов, которые, среди прочего, зависят от того, установлен ли уже соответствующий драйвер ODBC. Это может произойти, например, во время фактической установки операционной системы или системы баз данных. Порядок действий следующий: 1. У нас должна быть в наличии соответствующая программа, обеспечивающая подключение к базе данных (она называется, например, коннектор или ODBC-драйвер). Если он еще не готов, его необходимо скачать и установить. В случае с базой данных Oracle это, например, часть установки клиента, для базы данных MySQL ее можно скачать бесплатно, то же самое касается и других баз данных. Совет: Попробуйте ввести в поисковик название базы драйверов odbc и вы обязательно найдете настоящий драйвер. 2. Откройте панель управления «Администрирование» и запустите программу «Источники данных» (ODBC). Примечание. Источники данных (ODBC) — это название этой программы в Windows 7, ее имена изменились в других версиях операционной системы Microsoft Windows. Поскольку мы не используем Microsoft Office 2013 в Windows Vista и более ранних операционных системах, это только для информации. 3. В диалоговом окне «Диспетчер источников данных ODBC» мы можем добавить источник данных в качестве пользователя.DSN (действителен только для текущей учетной записи пользователя) или как системный DSN (действителен для всех учетных записей пользователей). Мы сделаем это, выбрав Добавить. 4. В следующем диалоге мы выбираем подходящий драйвер, а затем настраиваем соединение в соответствии с настройками базы данных. 52
9 Данные из баз данных Рисунок 2.4: Диспетчер источников данных ODBC Предупреждение: В действительности мы можем столкнуться с различными проблемами, которые могут быть вызваны, например, разными архитектурами (32-битной и 64-битной). Настройка соединения с помощью Microsoft Data Access (OLE DB) Порядок действий следующий: 1. На вкладке Данные используем кнопку Из других источников в группе Загрузить внешние данные. 2. Мы выбираем опцию Из мастера подключения к данным, а затем соответствующий источник данных (например, для определенного подключения к базе данных Oracle мы выбираем опцию Microsoft Data Access OLE DB Provider for Oracle). 3. Введите данные аутентификации, если они верны, через некоторое время появится список таблиц. 4. Выбираем нужную таблицу из списка (к сожалению, выбор не так удобен, список нельзя отфильтровать по отдельным владельцам таблиц). 5. В последнем диалоговом окне вам будет предложено сохранить файл подключения к данным для использования в будущем. 6. Впоследствии есть возможность сразу импортировать данные, например, в таблицу, или создать сводную таблицу прямо из них. Преимущество такого способа подключения в том, что драйвера обычно уже установлены. 53
10 ГЛАВА 2 Excel и внешние данные Рисунок 2.5: Мастер подключения к данным и подключение к базе данных Oracle Microsoft SQL Server В этом случае подключение будет простым и надежным с использованием драйвера ODBC, который является частью установки Microsoft Office: 1 С помощью кнопки Из других источников и опций С SQL-сервера (вкладка Данные, группа Загрузить внешние данные) запускаем Мастер подключения данных и выбираем источник данных Microsoft SQL Server. 2. Входимсоответствующие данные доступа, мы выполняем тест, выбираем таблицу и сохраняем соединение. Oracle Система реляционных баз данных Oracle — это надежная, широко используемая система, поверх которой, особенно в крупных компаниях, часто работает система ERP, например SAP (информационная система предприятия). Мы можем получить данные из этой системы двумя способами: Как экспорт данных из информационной системы. Прямое подключение к таблицам базы данных. Более распространенный способ — экспорт данных. Тем не менее, на этой базе данных мы постараемся показать, как мы можем напрямую подключаться к данным базы данных из Microsoft Excel. Предположим, нам требуется в первый раз загрузить данные из таблиц базы данных и создать из них сводную таблицу. У нас есть доступ к данным, что означает, помимо прочего, что база данных работает, и у нас есть соответствующие права доступа. Мы можем выбрать подключение с использованием ODBC или OLE DB. Здесь мы будем использовать уже функционирующее соединение ODBC. 54
11 Программа Microsoft Query Сначала мы проверим подключение к базе данных: 1. На вкладке Данные используйте команду Из других источников в группе Загрузить внешние данные. 2. Выбираем подходящий тип соединения, в нашем случае мы будем подключаться с помощью функционального соединения ODBC, этому теперь соответствует вариант From Microsoft Query. 3. Выбираем подходящее подключение к базе. 4. Нас попросят пройти аутентификацию (имя пользователя и пароль). 5. Появится подключение к источнику данных, и если все нормально, нам предложат таблицы из базы данных. 6. Выбираем нужные таблицы и проходим по отдельным диалогам Мастера запросов. 7. В финальном диалоге (Завершение мастера запросов) у нас есть несколько вариантов: Загрузить данные в Microsoft Excel Просмотреть данные или отредактировать запрос в Microsoft Query Сохранить запрос в виде файла в формате DQY Совет: Возможно, нам также предложат системные таблицы в пункте 5, который нам, вероятно, не понадобится. Выбороднако можно ограничиться конкретными владельцами таблиц базы данных. MySQL Реляционная база данных MySQL является бесплатной и используется в основном в области веб-технологий, например, в сочетании с PHP и веб-сервером в качестве редакторской системы для управления веб-страницами. В настоящее время (2013 год) эта база данных принадлежит и разрабатывается Oracle. Для подключения данных к этой базе данных из Microsoft Excel необходимо установить программу MySQL Connector/ODBC на клиентский компьютер, а затем активировать подключение данных. После тестирования подключения и сохранения подключения к данным мы можем приступить, например, к созданию сводной таблицы. Программа Microsoft Query Программа Microsoft Query может использоваться для получения данных из внешних источников, прежде всего из реляционных баз данных. В отличие от подключений к таблицам базы данных, в этой программе запросы к таблицам базы данных можно модифицировать различными способами и таким образом получать данные именно так, как вы хотите. Если нам достаточно подключения к источнику данных, созданного, например, с помощью Мастера подключения данных, и нам требуются все данные из таблиц, программа Microsoft Query нам не понадобится. Иногда, однако, нам нужно будет изменить запрос таблицы, например, для необходимости только некоторых полей таблицы или для связи таблиц с сессиями. Мы также можем сохранить запрос в виде файла и передать его на другой компьютер. 55
