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

Если данные всегда в пути, то Excel похож на Центральный вокзал. Представьте себе, что данные — это поезд с пассажирами, который регулярно входит в Excel, вносит изменения, а затем уходит. Существуют десятки способов входа в Excel, который импортирует данные всех типов, и список постоянно растет. Поместив данные в Excel, они могут изменять форму нужным вам образом с помощью Power Query. Данные, как и все мы, также требуют «ухода и кормления», чтобы все шло гладко. Именно здесь на помощь приходят свойства подключения, запроса и данных. Наконец, данные покидают железнодорожную станцию Excel разными способами: импортируются другими источниками данных, публикуются в виде отчетов, диаграмм и сводных таблиц, а также экспортируются в Power BI и Power Apps.  

Основные действия, которые можно выполнить с данными на железнодорожном вокзале Excel

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

• Импорт Вы можете импортировать данные из множества различных внешних источников данных. Эти источники данных могут находиться на вашем компьютере, в облаке или на другом конце света. Дополнительные сведения см. в статье Импорт данных из внешних источников.
• Power Query С помощью Power Query (ранее называвшегося "Get & Transform") можно создавать запросы на формирование, преобразование и объединение данных различными способами. Вы можете экспортировать свою работу в качестве шаблона Power Query, чтобы определить операцию потока данных в Power Apps. Вы даже можете создать тип данных для дополнения связанных типов данных. Дополнительные сведения см. в разделе справка по Power Query для Excel.
• Безопасность Конфиденциальность данных, учетные данные и проверка подлинности всегда находятся в постоянной проблеме. Дополнительные сведения см. в разделах Управление параметрами и разрешениями источника данных и Установка уровней конфиденциальности.
• Обновить Импортированные данные обычно требуют операции обновления, чтобы внести в Excel изменения, например добавления, обновления и удаления. Дополнительные сведения см. в статье "Обновление подключения к внешним данным в Excel".
• Соединения/свойства Каждый внешний источник данных имеет различные сведения о подключении и свойствах, связанные с ним, которые иногда требуют изменения в зависимости от ваших обстоятельств. Дополнительные сведения см. в статьях Управление диапазонами внешних данных и их свойствами, Создание, изменение и администрирование подключений к внешним данным, а также Свойства подключений.
• Устаревшие версии Традиционные методы, такие как устаревшие мастера импорта и MSQuery, по-прежнему доступны для использования. Дополнительные сведения см. в разделах Параметры импорта и анализа данных и Использование Microsoft Query для получения внешних данных.

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

Сводка подключений и свойств

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

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

Диалоговое окно или область Пути команд	Вкладки и туннели	Основной раздел справки
Свежие источники Данные>Последние источники	(Без вкладок) Диалоговое окно " Туннели для подключения>навигатора "	Управление параметрами и разрешениями источника данных
Свойства подключения ИЛИ Мастер подключения к данным Данные>Запросы & подключения>Вкладка "Подключения" > (щелчок правой кнопкой мыши по подключению) "Свойства" >	Вкладка "Использование " Вкладка "Определение" Вкладка "Использовано"	Свойства подключения
Свойства запроса Данные>Существующие соединения> (щелкнув правой кнопкой мыши подключение) >Редактирование свойств подключения ИЛИ Данные>Запросы & подключенияs | Вкладка "Запросы" > (щелчок подключения правой кнопкой мыши) "Свойства" > ИЛИ Запрос>Свойства ИЛИ Данные>Обновить все>Подключения (при размещении на загруженном листе запроса)	Вкладка "Использование " Вкладка "Определение" Вкладка "Использовано"	Свойства подключения
Запросы & подключения Данные>Запросы & подключения	Вкладка "Запросы " Вкладка "Подключения "	Свойства подключения
Существующие соединения Данные>Существующие соединения	Вкладка "Подключения " Вкладка "Таблицы "	Подключение к внешним данным
Свойства внешних данных ИЛИ Свойства диапазона внешних данных ИЛИ Данные>Свойства (отключается, если они не расположены на листе запроса)	Используется во вкладке (в диалоговом окне " Свойства соединения ") Кнопка "Обновить " в туннелях справа от туннелей к свойствам запроса	Управление диапазонами внешних данных и их свойствами
Свойства> подключенияВкладка "Определение" >Экспорт файла подключения ИЛИ Запрос>Экспорт файла подключения	(Без вкладок) Туннели к Диалоговое окно "Файл " Папка "Источники данных "	Создание и редактирование подключений к внешним данным и управление ими

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

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

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

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

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

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

 

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

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

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

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

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

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

Поиск связей

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

• Связи в книге 
  В этом списке отображаются все текущие подключения в книге. Список будет сформирован из соединений, которые вы уже определили, которые вы создали с помощью диалогового окна " Выбор источника данных " мастера подключения к данным, или из соединений, которые вы ранее выбрали в качестве соединения в этом диалоговом окне.
• Файлы подключения на компьютере 
  Этот список создается из папки "Мои источники данных ", которая обычно хранится в папке "Документы ".
• Файлы подключения в сети 
  Этот список может быть создан из набора папок в локальной сети, расположение которых можно развернуть в сети в рамках развертывания групповых политик Microsoft Office или библиотеки SharePoint. 

Редактирование свойств подключения

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

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

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

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

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

Управление подключениями

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

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

Совместное использование ODC и запрос подключений в файлах

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

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

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

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

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

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

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

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

Использование файлов запросов OQY Вы можете сохранять файлы OQY для подключения к данным в базе данных OLAP либо на сервере, либо в автономном файле куба (CUB). При использовании мастера многомерных подключений в Microsoft Query для создания источника данных для базы данных OLAP или куба автоматически создается файл OQY. Так как базы данных 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_источник — это имя источника данных, использованного для создания запроса.
• Диапазоны внешних данных из текстовых файлов называются по имени текстового файла.
• Диапазоны внешних данных из веб-запросов называются по имени веб-страницы, с которой были извлечены данные.

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

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

Поддержка источников данных в службы Excel

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

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

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

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

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

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

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

Компоненты доступа к данным ODBC и OLE DB

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

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

Прежде всего, приношу извинения за все аббревиатуры. Компонент Microsoft Data Access Components (MDAC) 2.8 включен в состав Microsoft Windows. С помощью MDAC можно подключаться к данным из различных реляционных и нереляционных источников данных и использовать их. Вы можете подключаться к различным источникам данных с помощью драйверов ODBC (Open Database Connection) или поставщиков 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 (например, драйвер Microsoft SQL ODBC) для подключения к источнику данных (например, базе данных 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 Provider for SQL Server).

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

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

См. также

Справка по Power Query для Excel