Импорт данных в Excel

Возможно, вам придется использовать данные из разных источников для анализа. В Excel вы можете импортировать данные из разных источников данных. Некоторые из источников данных следующие:

База данных Microsoft Access
Страница интернета
Текстовый файл
Таблица SQL Server
Куб анализа SQL Server
XML файл

Вы можете импортировать любое количество таблиц одновременно из базы данных.

Импорт данных из базы данных Microsoft Access

Мы узнаем, как импортировать данные из базы данных MS Access. Следуйте инструкциям ниже -

Step 1 - Откройте новую пустую книгу в Excel.

Step 2 - Щелкните вкладку ДАННЫЕ на ленте.

Step 3 - Щелкните From Accessв группе Получить внешние данные. ВSelect Data Source появится диалоговое окно.

Step 4- Выберите файл базы данных Access, который вы хотите импортировать. Файлы базы данных Access будут иметь расширение .accdb.

Откроется диалоговое окно «Выбор таблицы», в котором отображаются таблицы из базы данных Access. Вы можете либо импортировать все таблицы в базе данных одновременно, либо импортировать только выбранные таблицы в зависимости от ваших потребностей в анализе данных.

Step 5 - Установите флажок «Разрешить выбор нескольких таблиц» и выберите все таблицы.

Step 6- Щелкните ОК. ВImport Data появится диалоговое окно.

Как вы заметили, у вас есть следующие возможности для просмотра данных, которые вы импортируете в свою книгу:

Table
Отчет сводной таблицы
PivotChart
Отчет Power View

У вас также есть вариант - only create connection. Кроме того, по умолчанию выбран отчет сводной таблицы.

Excel также дает вам возможность помещать данные в вашу книгу -

Существующий рабочий лист
Новый рабочий лист

Вы найдете еще один флажок, который установлен и отключен - Add this data to the Data Model. Каждый раз, когда вы импортируете таблицы данных в свою книгу, они автоматически добавляются в модель данных в вашей книге. Вы узнаете больше о модели данных в следующих главах.

Вы можете попробовать каждый из вариантов, чтобы просмотреть данные, которые вы импортируете, и проверить, как данные отображаются в вашей книге -

Если вы выберете Table, Существующая опция рабочего листа отключается, New worksheetбудет выбрана опция, и Excel создаст столько рабочих листов, сколько таблиц вы импортируете из базы данных. Таблицы Excel отображаются на этих листах.
Если вы выберете PivotTable Report, Excel импортирует таблицы в книгу и создает пустую сводную таблицу для анализа данных в импортированных таблицах. У вас есть возможность создать сводную таблицу на существующем или новом листе.

Таблицы Excel для импортированных таблиц данных не появятся в книге. Однако вы найдете все таблицы данных в списке полей сводной таблицы вместе с полями в каждой таблице.
Если вы выберете PivotChart, Excel импортирует таблицы в книгу и создает пустую сводную диаграмму для отображения данных в импортированных таблицах. У вас есть возможность создать сводную диаграмму на существующем или новом листе.

Таблицы Excel для импортированных таблиц данных не появятся в книге. Однако вы найдете все таблицы данных в списке полей сводной диаграммы вместе с полями в каждой таблице.
Если вы выберете Power View Report, Excel импортирует таблицы в книгу и создает отчет Power View на новом листе. Вы узнаете, как использовать отчеты Power View для анализа данных в следующих главах.

Таблицы Excel для импортированных таблиц данных не появятся в книге. Однако вы найдете все таблицы данных в списке полей отчета Power View вместе с полями в каждой таблице.
Если вы выберете опцию - Only Create Connection, между базой данных и вашей книгой будет установлено соединение для передачи данных. В книге нет таблиц или отчетов. Однако импортированные таблицы по умолчанию добавляются в модель данных в вашей книге.

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

Импорт данных с веб-страницы

Иногда вам, возможно, придется использовать данные, которые обновляются на веб-сайте. Вы можете импортировать данные из таблицы на веб-сайте в Excel.

Step 1 - Откройте новую пустую книгу в Excel.

Step 2 - Щелкните вкладку ДАННЫЕ на ленте.

Step 3 - Щелкните From Web в Get External Dataгруппа. ВNew Web Query появится диалоговое окно.

Step 4 - Введите URL-адрес веб-сайта, с которого вы хотите импортировать данные, в поле рядом с «Адрес» и нажмите «Перейти».

Step 5- Появятся данные на сайте. Рядом с табличными данными, которые можно импортировать, будут значки желтых стрелок.

Step 6- Щелкните желтые значки, чтобы выбрать данные, которые вы хотите импортировать. Это превращает желтые значки в зеленые поля с галочкой, как показано на следующем снимке экрана.

Step 7 - После того, как вы выбрали то, что хотите, нажмите кнопку «Импорт».

В Import Data появится диалоговое окно.

Step 8 - Укажите, куда вы хотите поместить данные, и нажмите ОК.

Step 9 - Организуйте данные для дальнейшего анализа и / или представления.

Копирование и вставка данных из Интернета

Другой способ получения данных с веб-страницы - копирование и вставка необходимых данных.

Step 1 - Вставьте новый рабочий лист.

Step 2 - Скопируйте данные с веб-страницы и вставьте их на рабочий лист.

Step 3 - Создайте таблицу с вставленными данными.

Импорт данных из текстового файла

Если у вас есть данные в .txt или же .csv или же .prnфайлы, вы можете импортировать данные из этих файлов, рассматривая их как текстовые файлы. Следуйте инструкциям ниже -

Step 1 - Откройте новый рабочий лист в Excel.

Step 2 - Щелкните вкладку ДАННЫЕ на ленте.

Step 3 - Щелкните From Textв группе Получить внешние данные. ВImport Text File появится диалоговое окно.

Ты это видишь .prn, .txt and .csv текстовые файлы расширения принимаются.

Step 4- Выберите файл. Выбранное имя файла появится в поле Имя файла. Кнопка «Открыть» изменится на кнопку «Импорт».

Step 5 - Щелкните кнопку Импорт. Text Import Wizard – Step 1 of 3 появится диалоговое окно.

Step 6 - Нажмите на опцию Delimited чтобы выбрать тип файла и нажмите Далее.

В Text Import Wizard – Step 2 of 3 появится диалоговое окно.

Step 7 - В разделе "Разделители" выберите Other.

Step 8- В поле рядом с Other введите | (Это разделитель в текстовом файле, который вы импортируете).

Step 9 - Щелкните Далее.

В Text Import Wizard – Step 3 of 3 появится диалоговое окно.

Step 10 - В этом диалоговом окне вы можете установить формат данных столбца для каждого из столбцов.

Step 11- После того, как вы завершите форматирование данных столбцов, нажмите Готово. ВImport Data появится диалоговое окно.

Вы увидите следующее -

Таблица выбрана для просмотра и отображается серым цветом. Таблица - единственный вариант просмотра, который у вас есть в этом случае.
Вы можете поместить данные либо в существующий рабочий лист, либо на новый рабочий лист.
Вы можете установить или не устанавливать флажок Добавить эти данные в модель данных.
Нажмите ОК после того, как сделаете выбор.

Данные появятся на указанном вами листе. Вы импортировали данные из текстового файла в книгу Excel.

Импорт данных из другой книги

Возможно, вам придется использовать данные из другой книги Excel для анализа данных, но кто-то другой может вести другую книгу.

Чтобы получать актуальные данные из другой книги, установите соединение для передачи данных с этой книгой.

Step 1 - Щелкните DATA > Connections в группе Подключения на ленте.

В Workbook Connections появится диалоговое окно.

Step 2- Нажмите кнопку «Добавить» в диалоговом окне «Подключения к книге». ВExisting Connections появится диалоговое окно.

Step 3 - Щелкните Browse for More…кнопка. ВSelect Data Source появится диалоговое окно.

Step 4 - Щелкните значок New Source button. ВData Connection Wizard появится диалоговое окно.

Step 5 - Выбрать Other/Advancedв списке источников данных и нажмите Далее. Откроется диалоговое окно «Свойства связи с данными».

Step 6 - Задайте следующие свойства канала передачи данных -

Щелкните значок Connection таб.
Щелкните Использовать имя источника данных.
Щелкните стрелку вниз и выберите Excel Files из раскрывающегося списка.
Щелкните ОК.

В Select Workbook появится диалоговое окно.

Step 7- Перейдите в место, где находится книга для импорта. Щелкните ОК.

В Data Connection Wizard появляется диалоговое окно с выбором базы данных и таблицы.

Note- В этом случае Excel обрабатывает каждый импортируемый рабочий лист как таблицу. Имя таблицы будет именем рабочего листа. Итак, чтобы иметь понятные имена таблиц, назовите / переименуйте рабочие листы соответствующим образом.

Step 8- Щелкните Далее. ВData Connection Wizard появляется диалоговое окно с командами «Сохранить файл подключения данных» и «Готово».

Step 9- Нажмите кнопку «Готово». ВSelect Table появится диалоговое окно.

Как вы заметили, Name - это имя рабочего листа, которое импортируется как тип TABLE. Щелкните ОК.

Будет установлено соединение для передачи данных с выбранной вами книгой.

Импорт данных из других источников

Excel предоставляет вам возможность выбирать различные другие источники данных. Вы можете импортировать данные из них за несколько шагов.

Step 1 - Откройте новую пустую книгу в Excel.

Step 2 - Щелкните вкладку ДАННЫЕ на ленте.

Step 3 - Щелкните From Other Sources в группе Получить внешние данные.

Появится раскрывающийся список с различными источниками данных.

Вы можете импортировать данные из любого из этих источников данных в Excel.

Импорт данных с использованием существующего соединения

В предыдущем разделе вы установили соединение для передачи данных с книгой.

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

Step 1 - Щелкните вкладку ДАННЫЕ на ленте.

Step 2 - Щелкните Existing Connectionsв группе Получить внешние данные. Откроется диалоговое окно "Существующие подключения".

Step 3 - Выберите соединение, из которого вы хотите импортировать данные, и нажмите «Открыть».

Переименование подключений к данным

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

Step 1 - Перейти к DATA > Connectionsна ленте. ВWorkbook Connections появится диалоговое окно.

Step 2 - Выберите соединение, которое вы хотите переименовать, и нажмите «Свойства».

В Connection Propertiesпоявится диалоговое окно. Настоящее имя отображается в поле Имя подключения -

Step 3- Отредактируйте имя подключения и нажмите ОК. Соединение для передачи данных будет иметь новое имя, которое вы дали.

Обновление подключения к внешним данным

Когда вы подключаете свою книгу Excel к внешнему источнику данных, как вы видели в приведенных выше разделах, вы хотели бы, чтобы данные в вашей книге обновлялись, отражая изменения, вносимые время от времени во внешний источник данных.

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

Вы можете обновить только выбранные данные или все подключения к данным в книге одновременно.

Step 1 - Щелкните вкладку ДАННЫЕ на ленте.

Step 2 - Щелкните Refresh All в группе Connections.

Как вы заметили, в раскрывающемся списке есть две команды - «Обновить» и «Обновить все».

Если вы нажмете Refresh, выбранные данные в вашей книге обновятся.
Если вы нажмете Refresh All, все подключения данных к вашей книге обновляются.

Обновление всех подключений к данным в книге

У вас может быть несколько подключений к вашей книге для передачи данных. Вам необходимо обновлять их время от времени, чтобы ваша книга имела доступ к самым последним данным.

Step 1 - Щелкните любую ячейку в таблице, содержащую ссылку на импортированный файл данных.

Step 2 - Щелкните вкладку «Данные» на ленте.

Step 3 - Нажмите «Обновить все» в группе «Подключения».

Step 4- Выберите «Обновить все» из раскрывающегося списка. Все подключения к данным в книге будут обновлены.

Автоматически обновлять данные при открытии книги

Возможно, вы захотите иметь доступ к последним данным из подключений к вашей книге при каждом открытии книги.

Step 1 - Щелкните любую ячейку в таблице, содержащую ссылку на импортированный файл данных.

Step 2 - Щелкните вкладку Данные.

Step 3 - Щелкните Подключения в группе Подключения.

Откроется диалоговое окно «Подключения к книге».

Step 4- Щелкните кнопку "Свойства". Откроется диалоговое окно "Свойства подключения".

Step 5 - Щелкните вкладку Использование.

Step 6 - Отметьте опцию - Обновлять данные при открытии файла.

У вас также есть другой вариант - Remove data from the external data range before saving the workbook. Вы можете использовать этот параметр, чтобы сохранить книгу с определением запроса, но без внешних данных.

Step 7- Щелкните ОК. Всякий раз, когда вы открываете книгу, в нее загружаются актуальные данные.

Автоматически обновлять данные с регулярными интервалами

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

Step 1 - Щелкните любую ячейку в таблице, содержащую ссылку на импортированный файл данных.

Step 2 - Щелкните вкладку «Данные» на ленте.

Step 3 - Щелкните Подключения в группе Подключения.

Откроется диалоговое окно «Подключения к книге».

Step 4 - Щелкните кнопку "Свойства".

Откроется диалоговое окно "Свойства подключения". Установите свойства следующим образом -

Щелкните значок Usage таб.
Отметьте опцию Refresh every.
Введите 60 в качестве количества минут между каждой операцией обновления и нажмите ОК.

Ваши данные будут автоматически обновляться каждые 60 минут. (т.е. каждый час).

Включение фонового обновления

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

Щелкните любую ячейку в таблице, содержащую ссылку на импортированный файл данных.
Щелкните вкладку Данные.
Щелкните Подключения в группе Подключения. Откроется диалоговое окно «Подключения к книге».