Путь данных в Excel

Ниже приведены main действия, которые можно сделать, пока данные находятся на железнодорожной станции Excel:

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

• Power Query    Вы можете использовать Power Query (ранее — Get & Transform) для создания запросов для формирования, преобразования и объединения данных различными способами. Вы можете экспортировать свою работу в качестве шаблона Power Query, чтобы определить операцию потока данных в Power Apps. Вы даже можете создать тип данных для дополнения связанных типов данных. Дополнительные сведения см. в разделе справка по Power Query для Excel.

• Безопасность    Конфиденциальность данных, учетные данные и проверка подлинности всегда являются постоянной проблемой. Дополнительные сведения см. в разделах Управление параметрами и разрешениями источника данных иНастройка уровней конфиденциальности.

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

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

• Наследство    Традиционные методы, такие как устаревшие мастеры импорта и MSQuery, по-прежнему доступны для использования. Дополнительные сведения см. в разделах Параметры импорта и анализа данных и Использование Microsoft Query для получения внешних данных.

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

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

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

Диалоговое окно или область Пути к командам	Вкладки и туннели	Основной раздел справки
Последние источники Последние источники данных>	(Нет вкладок) Диалоговое окно " Туннели для подключения> навигатор "	Управление параметрами и разрешениями источника данных
Свойства подключения ИЛИ мастер подключения к данным Запросы> данных & Connections > вкладке Connections > (щелкните правой кнопкой мыши подключение) > Свойства	Вкладка "Использование" Вкладка "Определениеиспользуется в"	Свойства подключения
Свойства запроса > данных > существующей Connections (щелкните правой кнопкой мыши подключение) > изменить свойства подключения Or Data > Queries & Connections| Вкладка "Запросы" > (щелкните правой кнопкой мыши подключение) > Свойства Or Query > Properties OR Data > Обновить все > Connections (при расположении на загруженном листе запроса)	Вкладка "Использование" Вкладка "Определениеиспользуется в"	Свойства подключения
Запросы & Connections Запросы > данных & Connections	Вкладка "Запросы" Connections  tab	Свойства подключения
Существующие Connections Данные > существующей Connections	вкладка "Таблицы" Connections	Подключение к внешним данным
Свойства внешних данных ИЛИ Свойства диапазона внешних данных OR Свойства >данных (отключено, если оно не расположено на листе запроса)	Используется на вкладке (из диалогового окна Свойства подключения )   Кнопка "Обновить" в правых туннелях для свойства запроса	Управление диапазонами внешних данных и их свойствами
Вкладка "Свойства подключения" > "Определение " > экспорт файла подключения ИЛИ запрос> экспорт файла подключения	(Нет вкладок) Папка "Туннели в файл" в диалоговом окне"Источники данных"	Создание и редактирование подключений к внешним данным и управление ими

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

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

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

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

Для многих источников данных также требуется драйвер ODBC или поставщик OLE DB для координации потока данных между Excel, файлом подключения и источником данных.

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

1. Существует множество источников данных, к которым можно подключиться: службы Analysis Services, SQL Server, Microsoft Access, другие OLAP и реляционные базы данных, электронные таблицы и текстовые файлы.

2. Многие источники данных имеют связанный драйвер ODBC или поставщик OLE DB.

3. Файл подключения определяет все сведения, необходимые для доступа к данным и их извлечения из источника данных.

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

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

Чтобы найти файлы подключения, используйте диалоговое окно Существующая Connections. (Выберите Данные > существующие Connections.) В этом диалоговом окне можно увидеть следующие типы подключений:

• Connections в книге    

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

• Файлы подключения на компьютере    

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

• Файлы подключения в сети    

  Этот список можно создать из набора папок в локальной сети, расположение которых можно развернуть по сети в рамках развертывания групповых политик Microsoft Office или библиотеки SharePoint. 

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

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

Примечание    Иногда диалоговое окно Свойства подключения называется диалоговое окно Свойства запроса, если с ним связан запрос, созданный в Power Query (ранее — Get & Transform).

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

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

С помощью диалогового окна Connections вы можете легко управлять этими подключениями, в том числе создавать, изменять и удалять их (выбор запросов> данных & Connections > Connections вкладки > (щелкните правой кнопкой мыши подключение) > Свойства.) Вы можете: 

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

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

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

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

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

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

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

Использование файлов ODC

Вы можете создать файлы подключения к данным Office (ODC) (ODC) путем подключения к внешним данным с помощью диалогового окна Выбор источника данных или с помощью мастера подключения к данным для подключения к новым источникам данных. ODC-файл использует пользовательские теги HTML и XML для хранения сведений о подключении. Вы можете легко просматривать или изменять содержимое файла в Excel.

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

ODC-файлы — это рекомендуемый метод для подключения к данным и совместного использования данных. Вы можете легко преобразовать другие традиционные файлы подключения (DSN, UDL и файлы запросов) в ODC-файл, открыв файл подключения и нажав кнопку Экспорт файла подключения на вкладке Определение диалогового окна Свойства подключения .

Использование файлов запросов

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

Использование файлов запросов DQY    Вы можете использовать Microsoft Query для сохранения DQY-файлов, содержащих запросы к данным из реляционных баз данных или текстовых файлов. Открыв эти файлы в Microsoft Query, можно просмотреть данные, возвращаемые запросом, и изменить запрос, чтобы получить другие результаты. DQY-файл можно сохранить для любого создаваемого запроса с помощью мастера запросов или непосредственно в Microsoft Query.

Использование OQY-файлов запросов    OQY-файлы можно сохранить для подключения к данным в базе данных OLAP на сервере или в автономный файл куба (CUB). При использовании мастера многомерных соединений в Microsoft Query для создания источника данных для базы данных или куба OLAP файл OLAP создается автоматически. Так как базы данных OLAP не организованы в записях или таблицах, вы не можете создавать запросы или DQY-файлы для доступа к этим базам данных.

Использование RQY-файлов запросов    Excel может открывать файлы запросов в формате RQY для поддержки драйверов источников данных OLE DB, использующих этот формат. Дополнительные сведения см. в документации по драйверу.

Использование qry-файлов запросов    Microsoft Query может открывать и сохранять файлы запросов в формате QRY для использования с более ранними версиями Microsoft Query, которые не могут открывать DQY-файлы. Если у вас есть файл запроса в формате QRY, который вы хотите использовать в Excel, откройте его в Microsoft Query, а затем сохраните его как DQY-файл. Сведения о сохранении DQY-файлов см. в справке microsoft query.

Использование файлов веб-запросов IQY    Excel может открывать IQY-файлы веб-запросов для получения данных из Интернета. Дополнительные сведения см. в статье Экспорт в Excel из SharePoint.

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

Excel автоматически присваивает имена диапазону внешних данных следующим образом:

• Диапазоны внешних данных из файлов подключения к данным Office (ODC) получают то же имя, что и имя файла.

• Диапазоны внешних данных из баз данных именуются по имени запроса. По умолчанию Query_from_source — это имя источника данных, который использовался для создания запроса.

• Диапазоны внешних данных из текстовых файлов именуются с именем текстового файла.

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

Если лист содержит более одного диапазона внешних данных из одного источника, диапазоны нумеруются. Например, MyText, MyText_1, MyText_2 и т. д.

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

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

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

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

Превосходить данные объект	Создает Внешний данные диапазон?		OLE ДБ	ODBC	СМС файл	HTML файл	XML файл	SharePoint список
Мастер импорта текста	Да		Нет	Нет	Да	Нет	Нет	Нет
Отчет сводной таблицы (не OLAP)	Нет		Да	Да	Да	Нет	Нет	Да
Отчет сводной таблицы (OLAP)	Нет		Да	Нет	Нет	Нет	Нет	Нет
Таблица Excel	Да		Да	Да	Нет	Нет	Да	Да
Xml-карта	Да		Нет	Нет	Нет	Нет	Да	Нет
Веб-запрос	Да		Нет	Нет	Нет	Да	Да	Нет
Мастер подключения к данным	Да		Да	Да	Да	Да	Да	Да
Microsoft Query	Да		Нет	Да	Да	Нет	Нет	Нет

службы Excel обходной путь для таблиц Excel и именованных диапазонов

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

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

Давайте перейдем в полосу памяти базы данных.

Сведения о MDAC, OLE DB и OBC

Прежде всего, извинения за все сокращения. Компоненты доступа к данным Майкрософт (MDAC) 2.8 входят в состав Microsoft Windows . С помощью MDAC можно подключаться к данным и использовать их из самых разных реляционных и нереляционных источников данных. Вы можете подключиться к различным источникам данных с помощью драйверов ODBC (Open Database Connectivity) или поставщиков OLE DB, которые либо создаются и поставляются корпорацией Майкрософт, либо разрабатываются различными сторонними поставщиками. При установке Microsoft Office на компьютер добавляются дополнительные драйверы ODBC и поставщики OLE DB.

Чтобы просмотреть полный список поставщиков OLE DB, установленных на компьютере, откройте диалоговое окно Свойства канала данных из файла канала данных и перейдите на вкладку Поставщик .

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

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

Использование ODBC для подключения к источникам данных

В архитектуре ODBC приложение (например, Excel) подключается к диспетчеру драйверов ODBC, который, в свою очередь, использует конкретный драйвер ODBC (например, драйвер ODBC Microsoft SQL) для подключения к источнику данных (например, базе данных Microsoft SQL Server).

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

1. Убедитесь, что на компьютере с источником данных установлен соответствующий драйвер ODBC.

2. Определите имя источника данных (DSN) с помощью администратора источника данных ODBC , чтобы сохранить сведения о подключении в реестре или файле DSN, или строку подключения в коде Microsoft Visual Basic для передачи сведений о подключении непосредственно в диспетчер драйверов ODBC.

   Чтобы определить источник данных, в Windows нажмите кнопку Пуск, а затем панель управления. Щелкните Система и обслуживание, а затем — Администрирование. Щелкните Производительность и обслуживание, а затем — Администрирование. а затем щелкните Источники данных (ODBC). Для получения дополнительных сведений о различных параметрах нажмите кнопку Справка в каждом диалоговом окне.

Машинные источники данных

Источники данных компьютера хранят сведения о подключении в реестре на определенном компьютере с определяемым пользователем именем. Источники данных компьютера можно использовать только на компьютере, на котором они определены. Существует два типа источников данных компьютера : пользовательские и системные. Пользовательские источники данных могут использоваться только текущим пользователем и видны только для этого пользователя. Системные источники данных могут использоваться всеми пользователями на компьютере и видны всем пользователям на компьютере.

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

Файловые источники данных

Файловые источники данных (также называемые файлами DSN) хранят сведения о подключении в текстовом файле, а не в реестре, и, как правило, более гибки для использования, чем источники данных компьютера. Например, можно скопировать файловый источник данных на любой компьютер с правильным драйвером ODBC, чтобы приложение хозяйчиво и точно использовало сведения о подключении ко всем компьютерам, которые оно использует. Кроме того, можно поместить файловый источник данных на отдельный сервер, сделать его общим для нескольких компьютеров в сети и легко управлять централизованными сведениями о подключении.

Некоторые файловые источники данных нельзя сделать общими. Недоступный источник данных файлов находится на одном компьютере и указывает на источник данных компьютера. Их можно применять для доступа к существующим машинным источникам данных из файловых источников данных.

Использование OLE DB для подключения к источникам данных

В архитектуре OLE DB приложение, которое обращается к данным, называется потребителем данных (например, Excel), а программа, которая предоставляет собственный доступ к данным, называется поставщиком базы данных (например, поставщикОм Microsoft OLE DB для SQL Server).

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

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

• Создайте пустой текстовый файл с расширением UDL, а затем измените файл, в котором откроется диалоговое окно Свойства канала данных .