1. Главная страница » Компьютеры » Excel подключение к внешним данным

Excel подключение к внешним данным

Автор: | 16.12.2019

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

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

Задача для получения данных в Excel

И для того чтобы более понятно рассмотреть данную возможность, мы это будем делать как обычно на примере. Другими словами допустим, что нам надо выгрузить данные, одной таблицы, из базы SQL сервера, средствами Excel, т.е. без помощи вспомогательных инструментов, таких как Management Studio SQL сервера.

Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.

И для начала разберем исходные данные, допустим, есть база test, а в ней таблица test_table, данные которой нам нужно получить, для примера будут следующими:

Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.

Настройка Excel для получения данных с SQL сервера

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

И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»

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

  • Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
  • Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.

Далее необходимо выбрать базу, к которой подключаться, в нашем примере это база test. Также это подключение можно настроить сразу на работу с определенной таблицей или представлением, список таблиц и представлений у Вас будет отображен, давайте мы сделаем именно так и настроем подключение сразу на нашу таблицу test_table. Если Вы не хотите этого, а хотите чтобы Вы подключались к базе и потом выбирали нужную таблицу, то не ставьте галочку напротив пункта «Подключаться к определенной таблице», а как я уже сказал, мы поставим эту галочку и жмем «Далее».

В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:

После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:

В итоге у меня загрузятся из базы вот такие данные:

Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.

Вот собственно и все, как мне кажется все достаточно просто.

Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

Читайте также:  Msi 970 gaming bios

Основным преимуществом соединения с внешними данными является то, что вы можете периодически анализировать эти данные в Microsoft Office Excel без повторного анализа данных, что является операцией, которая может занять много времени и погрешности. После подключения к внешним данным вы также можете автоматически обновлять (или обновлять) книги Excel из исходного источника данных, когда источник данных обновляется с новыми данными.

Важно: Подключение к внешним данным может быть отключено на вашем компьютере. Чтобы подключаться к данным при открытии книги, включите функцию подключения к данным на панели центра управления безопасностью или сохраните книгу в надежном расположении. Дополнительные сведения можно найти в разделе Создание, удаление и изменение надежного расположения для файлов, Добавление, удаление и просмотр надежного издателя, а также Просмотр параметров и параметров в центре управления безопасностью Microsoft Office.

На вкладке данные выберите пункт существующие подключения. Откроется диалоговое окно " существующие подключения ".

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

В раскрывающемся списке Показать выполните одно из следующих действий:

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

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

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

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

Этот список создается из папки Мои источники данных , которая обычно хранится в папке Мои документы на компьютере.

Чтобы отобразить только подключения, доступные из файла подключения, доступного из сети, щелкните значок файлы подключения в сети. Этот список создается из библиотеки подключений к данным Excel (ДКЛ) на сайте Microsoft SharePoint Services. ДКЛ — это библиотека документов на сайте SharePoint Services, содержащая набор файлов подключения к данным Office (ODC). Обычно ДКЛ устанавливается администратором сайта, который может также настраивать сайт SharePoint для отображения ODC-файлов из этого ДКЛ в диалоговом окне внешние подключения . Дополнительные сведения можно найти в справке по центру администрирования SharePoint Services.

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

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

Выберите нужное подключение и нажмите кнопку Открыть.

В диалоговом окне Импорт данных в разделе выберите способ просмотра данных в книге выполните одно из указанных ниже действий.

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

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

Чтобы создать отчет сводной таблицы для суммирования больших объемов данных путем статистической обработки и суммирования данных, щелкните отчет сводной таблицы.

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

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

С помощью параметра создать подключение можно сохранить выбранное подключение в книге для последующего использования. Например, если вы подключаетесь к источнику данных куба OLAP и хотите преобразовать ячейки сводной таблицы в формулы листа с помощью команды " преобразовать в формулы " (на вкладке " Параметры " в меню "инструменты ") Нажмите кнопку инструменты OLAP. Вы можете использовать этот параметр, так как вам не нужно сохранять отчет сводной таблицы.

В группе Куда следует поместить данные? выполните одно из следующих действий:

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

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

Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки a1, нажмите кнопку Новый лист.

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

На вкладке данные в группе Получение внешних данных нажмите кнопку существующие подключения.

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

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

Читайте также:  Excel тормозит при открытии файла

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

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

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

Этот список создается из папки Мои источники данных , которая обычно хранится в папке Мои документы .

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

Этот список создается из библиотеки подключений к данным служб Excel (ДКЛ) на сайте Microsoft Office SharePoint Server 2007. ДКЛ — это библиотека документов на сайте Microsoft Office SharePoint Services 2007, которая содержит коллекцию файлов подключения к данным Office (ODC). Обычно ДКЛ устанавливается администратором сайта, который может также настраивать сайт SharePoint для отображения ODC-файлов из этого ДКЛ в диалоговом окне внешние подключения . Дополнительные сведения можно найти в справке по центру администрирования Office SharePoint Server 2007.

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

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

Выберите нужное подключение и нажмите кнопку Открыть.

В диалоговом окне Импорт данных в разделе выберите способ просмотра данных в книге выполните одно из указанных ниже действий.

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

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

Чтобы создать отчет сводной таблицы для суммирования больших объемов данных путем статистической обработки и суммирования данных, щелкните отчет сводной таблицы.

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

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

С помощью параметра создать подключение можно сохранить выбранное подключение в книге для последующего использования. Например, если вы подключаетесь к источнику данных куба OLAP и хотите преобразовать ячейки сводной таблицы в формулы листа с помощью команды " преобразовать в формулы " (на вкладке " Параметры " в меню "инструменты ") Нажмите кнопку инструменты OLAP. Вы можете использовать этот параметр, так как вам не нужно сохранять отчет сводной таблицы.

В группе Куда следует поместить данные? выполните одно из следующих действий:

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

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

Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки a1, нажмите кнопку Новый лист.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

С помощью Microsoft Office Excel можно создавать и изменять подключения к внешним источникам данных, хранящимся в книге или в файле подключения. С помощью диалогового окна подключения к книге вы можете легко управлять этими подключениями, в том числе создавать, редактировать и удалять их.

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

Данные в книге Excel могут поступать из двух разных папок. Данные могут храниться непосредственно в книге или храниться во внешнем источнике данных, таком как текстовый файл, база данных или куб OLAP (Online Analytical Processing). Внешний источник данных подключается к книге через подключение к данным — набор сведений, описывающих способ поиска, входа, запроса и доступа к внешнему источнику данных.

Читайте также:  Assassin s creed origins где найти графит

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

Сведения о подключении могут храниться либо в книге, либо в файле подключения, например в файле подключения к данным Office (ODC) или в файле универсального подключения к данным (UDC) (. удккс). Файлы подключения особенно полезны для единого совместного использования подключений и для облегчения администрирования источника данных.

Если вы подключаетесь к источнику данных с помощью файла подключения, Excel копирует сведения о подключении из файла подключения в книгу Excel. Когда вы вносите изменения с помощью диалогового окна Свойства подключения , вы редактируете сведения о подключении к данным, которые хранятся в текущей книге Excel, а не в исходном файле подключения к данным, который может использоваться для создания подключения. Указывает имя файла, которое отображается в свойстве файла подключения . После изменения сведений о подключении (за исключением имени подключения и свойств описания подключения ) связь с файлом подключения удаляется, а свойство файла подключения очищается.

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

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

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

Просматривать подключения в текущей книге.

Анализировать сообщения об ошибках, связанные с подключениями к внешним данным.

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

Открывать диалоговое окно Существующие подключения для создания новых подключений. Дополнительные сведения можно найти в разделе Подключение к внешним данным (импорт).

Отображать диалоговое окно Свойства подключения для изменения свойств подключения к данным, редактирования запросов и настройки параметров. Дополнительные сведения см. в статье Свойства подключения.

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

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

В верхней части диалогового окна автоматически отображаются все подключения, а также следующие сведения:

Имя подключения, заданное в диалоговом окне Свойства подключения.

Необязательное описание подключения, заданное в диалоговом окне Свойства подключения.

Дата и время последнего успешного обновления подключения. Если эта информация отсутствует, то подключение никогда не обновлялось.

Нажмите кнопку Добавить, чтобы открыть диалоговое окно Существующие подключения. Дополнительные сведения можно найти в разделе Подключение к внешним данным (импорт).

Отображение сведений о подключении

Выберите подключение, а затем щелкните Свойства, чтобы открыть диалоговое окно Свойства подключения. Дополнительные сведения см. в статье Свойства подключения.

Обновление внешних данных

Щелкните стрелку рядом с кнопкой Обновить, а затем выполните следующие действия:

Для обновления определенных подключений выберите одно или несколько подключений, а затем щелкните Обновить.

Для обновления всех подключений в книге снимите все флажки, а затем щелкните Обновить все.

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

Для остановки текущего обновления щелкните Отменить обновление.

Удаление одного или нескольких подключений

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

Эта кнопка недоступна, если защищены книга или объект, использующий подключение (например, отчет сводной таблицы).

При удалении подключения удаляется только подключение. Объекты или данные из книги не удаляются.

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

Отображение мест использования подключений в книге

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

Будет выведена следующая информация:

Лист, на котором используется подключение.

Имя запроса Excel.

Ссылка на ячейку, диапазон или объект.

Значение ячейки. Для диапазона ячеек значение не указывается.

Формула ячейки или диапазона ячеек.

При выборе другого соединения в верхней части диалогового окна эта информация изменяется.

С помощью диалогового окна Свойства подключения или мастера подключения к данным вы можете создать файл подключения к данным Office (ODC) с помощью Excel.

Выполните одно из следующих действий.

Создайте новое подключение к источнику данных. Дополнительные сведения можно найти в следующих статьях справки:

Использование существующего подключения. Дополнительные сведения можно найти в разделе Подключение к внешним данным (импорт).

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *