Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Подключение к внешним данным (импорт)

Основным преимуществом соединения с внешними данными является то, что вы можете периодически анализировать эти данные в 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 , а затем нажав кнопку ОК.

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

Excel — Подключение и получение данных с SQL сервера

Мало пользователей, да и начинающих программистов, которые знают о возможности 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. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую пройти наши курсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

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

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

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

Читать еще:  Как сделать ячейку текущей в excel

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

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

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

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

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

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

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

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

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

Глава 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 или ИТ-администратору, чтобы узнать, как ввести учетные данные для входа.

Читать еще:  Vba excel глобальные переменные

Рис. 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 это соединение становится таким же, как и любое другое, и его можно обновить, чтобы работать с самыми последними данными. Имейте в виду, что каждое соединение считается трансакцией.

Удалить подключения к данным

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

Читаю статью Создание, редактирование и управление подключениями к внешним данным, в ней расписано, как удалять подключения с помощью диалогового окна «Подключения к книге». Вопрос таков: где найти и как открыть это диалоговое окно «Подключения к книге»?

03.01.2018, 21:04

Релизация подключения поля со списком к данным
Раньше делал кое — какие странички на ASP там приходилось генерировать список листбокса программно.

Форма настроек подключения к базе данным на удаленном сервере
Здравствуйте уважаемые Форумчане. Хочу обратиться с просьбой объяснить популярно вопрос, на.

удалить строку с данным словом
задача: дано слово, удалить строчи из текста, которое начинается на это слово. слово и текст брать.

Удалить и добавить строку в dataGridView, если есть привязка к данным
Нужно удалить или добавить строку в dataGridView. Добавляю так.

03.01.2018, 21:53203.01.2018, 23:08 [ТС]304.01.2018, 00:00404.01.2018, 08:20504.01.2018, 19:54 [ТС]6

Попадаю на страницу, которую упомянул в первом посте.

04.01.2018, 20:00704.01.2018, 23:22 [ТС]8

Dinoxromniy, вопрос снят. Рядом на вкладке есть секция «Запросы и подключения» и одноимённая кнопка, которая открывает интерфейс, позволяющий поудалять подключения. В белорусскоязычной локализации, под которой я работал, эта кнопка переведена неточно, да и я не ожидал, что на всё это дело будет выделено две секции. Обнаружил, только когда поменял язык на русский, чтобы сделать Вам скриншот.

Спасибо за внимание и прошу прощения за беспокойство!

04.01.2018, 23:22
04.01.2018, 23:22

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

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

Как удалить сетевые подключения по локальной сети?
Здравствуйте у меня вопрос: Как удалить ненужные сетевые подключения по локальной сети? Просто.

Как из мастера настройки источника данных удалить ненужные подключения?
Здравствуйте! Подскажите пожалуйста, как из мастера настройки источника данных удалить ненужные.

Удалить строки, где данные в столбцах C,D,E на i равны данным этих же столцов из строки i-1
Здравствуйте. Нужен макрос, выполняющих определенные действия (см. ниже). С VBA сталкивалась один.

Ссылка на основную публикацию
Adblock
detector