Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.
В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.
Теперь немного о действиях, совершенных мной с целью подключения к базе:
В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».
Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.
Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).
После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.
Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».
Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:
Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».
В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.
В итоге получаем данные прямо из базы, что и требовалось.
Теперь наше подключение будет находиться в книге и доступно в меню «(1) Данные» -> «(2) Подключения».
Как видно из кнопок формы, здесь мы можем:
«(1) Добавить» в книгу новое подключение из уже существующих;
«(2) Удалить» подключение из книги;
Отредактировать «(3) Свойства» подключения;
«(4) Обновить» данные, предоставляемые подключением.
Подытоживая весь вышеописанный опыт, хочется сказать, что конечно же по функционалу и кругу решаемых задач MS Excel не сравнится с такими системами как MS SQL Server Management Studio, Oracle SQL Developer и подобными. Но, в условиях ограниченного времени и доступных инструментов, при необходимости быстро получить доступ к данным из базы, MS Excel вполне справляется.
Источником
данных (адресной книгой или списком
рассылок) может быть документ почти
любого типа, лишь бы его структура была
понятной текстовому процессору. То
есть, процессор должен различать, где
заканчивается одна запись и начинается
другая, а также как разделены поля данных
внутри записи.
Типы источников данных
На
практике в качестве источников данных
применяют:
-
специально
подготовленные документы Word; -
веб-документы,
имеющие табличную структуру; -
таблицы
баз данных (например, Microsoft
Access); -
электронные
таблицы Microsoft
Excel; -
документы,
подготовленные в специальном программном
средстве, входящем в пакет Microsoft
Office
и интегрированном с Microsoft
Word.
У
встроенного средства Microsoft
Office,
предназначенного для работы с источниками
данных, есть много достоинств, среди
которых простота, удобство, скорость
работы. Но есть у него и существенный
недостаток — его надо наполнять данными,
а это весьма нелегкий труд. Вот почему
в реальной жизни приходится использовать
в качестве источников данных все, что
можно — почтовые базы данных, полученные
из Интернета,
таблицы Access
или Excel,
устаревшие базы данных в форматах dBase,
Clipper,
FoxPro
и т.п.
Текстовый документ как источник данных
Microsoft
Word
очень терпимо относится к источникам
данных и готов принять почти все, что
угодно, даже простые текстовые документы
в формате .txt,
подготовленные в простейшем текстовом
редакторе типа Блокнот.
Надо только принять меры, чтобы четко
и однозначно выразить структуру данных
(отделить друг от друга записи
и значения полей).
Записи
(строки) принято разделять символом
«конец абзаца», то есть нажатием клавиши
Enter.
Поля
(столбцы) друг от друга можно отделять
множеством разных символов, например
символом табуляции (клавишей Tab),
звездочкой, косой чертой, восклицательным
знаком и другими. Обычно применяют точку
с запятой. При этом важно следить, чтобы
во всем документе для этой цели применялся
исключительно один символ и чтобы для
других целей он не использовался.
Имена
полей. Первая
строка текстового файла играет особую
роль. Данные, введенные в нее, воспринимаются
как имена
полей. Таким
образом, первая физическая запись
содержится во второй строке, вторая
запись — в третьей строке и так далее.
Документ Word как источник данных
В
документе Word
есть существенное преимущество перед
обычным текстовым документом — он может
содержать таблицы. В текстовом процессоре
Microsoft
Word
работа с таблицами организована очень
удобно: не надо заботиться ни о каких
символах-разделителях. Таблицы Word
надежно превращаются в источники данных
для документов слияния.
Веб-страница как источник данных
Возможность
использования таблиц Word
в качестве источников данных открывает
перед нами необъятные возможности
использования таблиц (или списков),
размещенных на веб-страницах. Для этого
их достаточно выделить в браузере,
скопировать и вставить в пустой документ
Word,
после чего проверить и отредактировать.
Электронная таблица Excel как источник данных
Таблицы
Excel
тоже можно успешно подключать к Microsoft
Word
в качестве источника данных. При этом
важно иметь в виду простейшие ограничения:
-
таблица
должна располагаться на отдельном
листе Excel; -
данные
первой строки рассматриваются как
имена полей;
-
между
первой строкой (строкой заголовков) и
строками записей нежелательны пустые
строки.
Соседние файлы в папке WORD 2007
- #
- #
- #
- #
- #
- #
- #
- #
Если данные всегда в пути, Excel похож на главный центральный станция. Представьте, что данные — это поезд, который регулярно посещает Excel, вносит изменения, а затем покидает его. Существует множество способов войти в Excel, при котором данные всех типов будут импортироваться, и список будет постоянно расти. После того как данные уже есть в Excel, вы можете изменить фигуру в нужном виде с помощью Power Query. Для бесперебойной работы данных, как и для всех нас, требуется «уход и подача». Сюда приходят свойства подключения, запроса и данных. Кроме того, данные сют с док-станции Excel во многих случаях: они импортируются из других источников, делятся как отчеты, диаграммы и совые таблицы, а также экспортируются в Power BI и Power Apps.
Вот основные вещи, которые можно делать, когда данные находятся на поезде Excel:
-
Импорт Вы можете импортировать данные из различных внешних источников. Эти источники данных могут быть на вашем компьютере, в облаке или на полпути по всему миру. Дополнительные сведения см. в теме «Импорт данных из внешних источников данных».
-
Power Query С помощью Power Query (ранее — » Get & Transform») можно создавать запросы для фигуры, преобразования и объединения данных различными способами. Вы можете экспортировать свою работу как шаблон Power Query, чтобы определить операцию потока данных в Power Apps. Вы даже можете создать тип данных в дополнение к связанным типамданных. Дополнительные сведения см. в справке Power Query для Excel.
-
Безопасность Конфиденциальность данных, учетные данные и проверка подлинности всегда являются проблемой. Дополнительные сведения см. в сведениях об управлении настройками и разрешениями источников данных, а также настройке уровней конфиденциальности.
-
Обновить Для внесения в Excel изменений, например дополнений, обновлений и удаления, обычно требуется операция обновления. Дополнительные сведения см. в теме «Обновление подключения к внешним данным в Excel».
-
Подключения и свойства Каждый внешний источник данных имеет связь с данными о каждом внешнем источнике данных и связанную с ним информацию о свойстве, которая иногда требует внесения изменений в зависимости от обстоятельств. Дополнительные сведения см. в дополнительных сведениях об управлении диапазонами внешних данных и их свойствами, создании,редактировании подключений к внешним данным и свойствах подключения.
-
Устаревшая версия Традиционные методы, такие как устаревшие мастеры импорта и MSQuery, по-прежнему доступны. Дополнительные сведения см. в параметрах импорта и анализа данных, а также извлечении внешних данных с помощью Microsoft Query.
В следующих разделах более подробные сведения о том, что происходит за кадром на этом занятом поезде Excel.
Существуют свойства подключения, запроса и диапазона внешних данных. Оба свойства подключения и запроса содержат традиционные сведения о под соединении. В заголовке диалогового окна «Свойства подключения» означает, что с ним не связан ни один запрос, а с свойствами запроса — нет. Свойства диапазона внешних данных контролируют макет и формат данных. Во всех источниках данных есть диалоговое окно «Свойства внешних данных», но для источников данных со связанными учетными данными и данными обновления используется большее диалоговое окно «Свойства внешних данных диапазона».
В следующих сведениях общались наиболее важные диалоговые окна, области, пути к командам и соответствующие разделы справки.
|
Диалоговое окно или области |
Вкладки и туннель |
Основной раздел справки |
|---|---|---|
|
Последние источники
Данные> |
(Без вкладок) Tunnels to Connect > Navigator dialog box |
Управление настройками и разрешениями источника данных |
|
Свойства подключения Data > Queries & Connections > Connections tab > (right click a connection) > Properties |
Вкладка Usage |
Свойства подключения |
|
Свойства запроса
Data> Existing Connections > (right click a connection) > Edit Connection Properties |
Вкладка Usage |
Свойства подключения |
|
Запросы & connections Data > Queries & Connections |
Вкладка «Подключения» |
Свойства подключения |
|
Существующие подключения
Данные> |
Вкладка «Таблицы» |
Подключение к внешним данным |
|
Свойства внешних данных |
Используется на вкладке (в диалоговом окне «Свойства подключения») Кнопка «Обновить» в правом туннелье в свойствах запроса |
Управление диапазонами внешних данных и их свойствами |
|
Свойства подключения > |
(Без вкладок) Tunnels to |
Создание, изменение подключений к внешним данным и управление ими |
Данные в книге Excel могут быть из двух разных мест. Данные могут храниться непосредственно в книге или во внешнем источнике данных, например в текстовом файле, базе данных или кубе OLAP. Этот внешний источник данных подключается к книге с помощью подключения к данным, которое является набором сведений о том, как найти внешний источник данных, войти в систему и получить к нему доступ.
Основное преимущество подключения к внешним данным — возможность периодически анализировать эти данные без многократного копирования в книгу (это операция, которая может быть трудоемкой и частой). После подключения к внешним данным вы также можете автоматически обновлять (обновлять) книги Excel из исходного источника при обновлении источника данных с новой информацией.
Сведения о под соединении хранятся в книге и также могут храниться в файле подключения, например в файле подключения к данным Office (ODC) или файле имени источника данных (DSN).
Чтобы внести внешние данные в Excel, необходим доступ к ним. Если доступ к внешнему источнику данных не находится на локальном компьютере, может потребоваться обратиться к администратору базы данных, чтобы узнать пароль, разрешения пользователей или другие сведения о под соединении. Если источником данных является база данных, убедитесь, что она не открыта в монопольном режиме. Если источником данных является текстовый файл или таблица, убедитесь, что другие пользователи не имеют доступа к ним.
Для многих источников данных для координации потока данных между Excel, файлом подключения и источником данных также требуется драйвер ODBC или поставщик OLE DB.
На следующей схеме ключевых моментах подключения к данным нет.
1. Существуют различные источники данных, к которые можно подключиться: службы Analysis Services, SQL Server, Microsoft Access, другие реляционные базы данных OLAP, электронные таблицы и текстовые файлы.
2. У многих источников данных есть связанный драйвер ODBC или поставщик OLE DB.
3. Файл подключения определяет все сведения, необходимые для доступа к источнику данных и извлечения данных из него.
4. Данные подключения копируется из файла подключения в книгу, и их можно легко изменить.
5. Данные копируется в книгу, чтобы их можно было использовать точно так же, как и данные, хранимые непосредственно в книге.
Чтобы найти файлы подключения, используйте диалоговое окно «Существующие подключения». (Выберите «Данные>существующие подключения.) В этом диалоговом окне можно увидеть следующие типы подключений:
-
Подключения в книге
В этом списке отображаются все текущие подключения в книге. Список создается на окне подключений, которые вы уже определили, с помощью диалоговых окна «Выбор источника данных» мастера подключения к данным, или на окне подключений, выбранных в этом диалоговом окне как подключение.
-
Файлы подключения на компьютере
Этот список создается из папки «Мои источники данных», которая обычно хранится в папке «Документы».
-
Файлы подключения в сети
Этот список можно создать на основе набора папок в локальной сети, расположение которых можно развернуть в сети при развертывании групповых политик Microsoft Office или библиотеки SharePoint.
Excel также можно использовать в качестве редактора файлов подключений для создания и изменения подключений к внешним источникам данных, которые хранятся в книге или файле подключения. Если нужного подключения нет, вы можете создать подключение, нажав кнопку «Обзор» и выбрав диалоговое окно «Выбор источника данных», а затем нажав кнопку «Создать источник», чтобы запустить мастер подключения к данным.
После создания подключения вы можете использовать диалоговое окно «Свойства подключения» (выберите «Свойства подключения» >«Запросы & Подключения >» > (щелкните правой кнопкой мыши «Свойства > подключения») для управления различными настройками подключений к внешним источникам данных, а также использования, повторного использования или переключения файлов подключения.
Примечание Иногда диалоговое окно «Свойства подключения» называется диалогом «Свойства запроса», если с ним связан запрос, созданный в Power Query (прежнее название — «& Преобразовать»).
Если для подключения к источнику данных используется файл подключения, Excel копирует сведения о подключении из файла подключения в книгу Excel. При внесении изменений в диалоговом окне «Свойства подключения» вы редактируете данные подключения к данным, хранимые в текущей книге Excel, а не исходный файл подключения, который мог использоваться для создания подключения (указывается именем файла, которое отображается в свойстве «Файл подключения» на вкладке «Определение»). После изменения данных подключения (за исключением свойств «Имя подключения» и «Описание подключения») ссылка на файл подключения удаляется, а свойство «Файл подключения» удаляется.
Чтобы гарантировать, что файл подключения всегда используется при обновлении источника данных, на вкладке «Определение» нажмите кнопку «Всегда пытаться использовать этот файл для обновления данных». Если этот установить, все книги, которые используют этот файл подключения, всегда будут использовать изменения файла подключения, для которых также должно быть за установлено это свойство.
В диалоговом окне Connections можно легко управлять этими подключениями, включая их создание, редактирование и удаление (выберите данные> Запросы & Connections >Connections > > (щелкните правой кнопкой мыши подключение) > Свойства.) В этом диалоговом окне можно сделать следующее:
-
Создание, изменение, обновление и удаление подключений, которые используются в книге.
-
Проверьте источник внешних данных. Это можно сделать, если подключение было определено другим пользователем.
-
Показывать, где каждое подключение используется в текущей книге.
-
Диагностика сообщения об ошибке, которое касается подключений к внешним данным.
-
Перенаправить подключение к другому серверу или источнику данных или заменить файл подключения для существующего подключения.
-
Легко создавайте файлы подключения и делитесь ими с пользователями.
Файлы подключений особенно полезны для совместного доступа к подключениям, что делает подключения более обнаруживаемыми, повышает уровень безопасности подключений и облегчает администрирование источников данных. Лучший способ поделиться файлами подключения — поместить их в безопасное и надежное расположение, например в сетевую папку или библиотеку SharePoint, где пользователи могут читать файл, но только назначенные пользователи могут изменять его. Дополнительные сведения см. в подмноговом окне «Совместное передачу данных с помощью ODC».
Использование ODC-файлов
Вы можете создавать файлы подключения к данным Office (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). При создании источника данных для базы данных или куба 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 автоматически создает диапазон внешних данных. Единственным исключением из этого правила является отчеты pivotttable, подключенные к источнику данных, в которых не создается диапазон внешних данных. В Excel можно форматирование и разложить диапазон внешних данных или использовать его в вычислениях, как и любые другие данные.
Excel автоматически называет диапазон внешних данных следующим образом:
-
Имена внешних данных в диапазоне от файлов подключения к данным Office (ODC) и имени файла одинаковы.
-
Диапазоны внешних данных из баз данных называются именем запроса. По Query_from_источником данных является имя источника данных, который использовался для создания запроса.
-
Диапазоны внешних данных от текстовых файлов называются текстовыми именами файлов.
-
Диапазоны внешних данных от веб-запросов называются именем веб-страницы, на которой были извлечены данные.
Если на вашем компьютере несколько диапазонов внешних данных из одного источника, они нуметы. Например, MyText, MyText_1, MyText_2 и т. д.
У диапазона внешних данных есть дополнительные свойства (которые не следует путать с свойствами подключения), которые можно использовать для управления данными, например для сохранения форматирования ячеок и ширины столбцов. Эти свойства диапазона внешних данных можно изменить, нажав кнопку «Свойства» в группе «Подключения» на вкладке «Данные» и внося изменения в диалоговые окна «Свойства диапазона внешних данных» или «Свойства внешних данных».
|
|
|
Существует несколько объектов данных (например, диапазон внешних данных и отчеты в отчете, которые можно использовать для подключения к различным источникам данных). Однако тип источника данных, к который можно подключиться, отличается от типа каждого объекта данных.
Вы можете использовать и обновлять подключенные данные в службы Excel. Как и для любого внешнего источника данных, может потребоваться проверка подлинности для доступа. Дополнительные сведения см. в теме «Обновление подключения к внешним данным в Excel». Fдополнительные сведения об учетных данных см. в службы Excel параметрах проверки подлинности.
В таблице ниже общались сведения о том, какие источники данных поддерживаются для каждого объекта данных в Excel.
|
Excel |
|
OLE |
ODBC |
Текст |
HTML |
XML |
SharePoint |
|
|
Мастер импорта текста |
Да |
Нет |
Нет |
Да |
Нет |
Нет |
Нет |
|
|
Отчет в области «Отчеты и отчеты» |
Нет |
Да |
Да |
Да |
Нет |
Нет |
Да |
|
|
Отчет в области «Отчеты и отчеты» |
Нет |
Да |
Нет |
Нет |
Нет |
Нет |
Нет |
|
|
Таблица Excel |
Да |
Да |
Да |
Нет |
Нет |
Да |
Да |
|
|
Карта XML |
Да |
Нет |
Нет |
Нет |
Нет |
Да |
Нет |
|
|
Веб-запрос |
Да |
Нет |
Нет |
Нет |
Да |
Да |
Нет |
|
|
Мастер подключения к данным |
Да |
Да |
Да |
Да |
Да |
Да |
Да |
|
|
Microsoft Query |
Да |
Нет |
Да |
Да |
Нет |
Нет |
Нет |
|
Примечание: Эти файлы: текстовый файл, импортируемый с помощью мастера импорта текста, XML-файл, импортируемый с помощью карты XML, и HTML- или XML-файл, импортируемый с помощью веб-запроса, не используют драйвер ODBC или поставщик OLE DB для подключения к источнику данных.
службы Excel обходного пути для таблиц и именовых диапазонов Excel
Если вы хотите отобразить книгу Excel в службы Excel, вы можете подключиться к данным и обновить их, но для этого нужен отчет таблицы. службы Excel не поддерживает диапазоны внешних данных, то службы Excel не поддерживает таблицу Excel, подключенную к источнику данных, веб-запросу, карте XML или Microsoft Query.
Однако это ограничение можно обойти, подключив с помощью таблицы к источнику данных, а затем спроектировать и разметить ее как двумерную таблицу без уровней, групп или подытогов, чтобы отображались все нужные значения строк и столбцов.
Давайте отпустим дорожку памяти базы данных.
О MDAC, OLE DB и OBC
Во-первых, приносим извинения за все акронимы. Компоненты доступа к данным Майкрософт (MDAC) 2.8 входят в состав Microsoft Windows. С помощью MDAC можно подключаться к данным из самых разных реляционных и нереляционных источников и использовать их. Вы можете подключаться к различным источникам данных с помощью драйверов ODBC или поставщиков 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, сделайте следующее:
-
Убедитесь, что на компьютере с источником данных установлен соответствующий драйвер ODBC.
-
Определите имя источника данных (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 и отредактйте его, чтобы отобразить диалоговое окно «Свойства ссылки на данные».
См. также
Справка по Power Query для Excel
Нужна дополнительная помощь?
Содержание
- Как это работает
- Подключаем внешние данные из интернет
- Импорт внешних данных Excel 2010
- Отличите нового и старого мастера импорта
- Пример работы функции БИЗВЛЕЧЬ при выборке данных из таблицы Excel
- Примеры использования функции БИЗВЛЕЧЬ в Excel
- Тип данных: Числовые значения MS Excel
- Классификация типов данных
- Текстовые значения
- Дата и время
- Логические данные
- Разновидности типов данных
- Число
- Текст
- Ошибки
- Подключение к внешним данным
- Подключение к базе данных
- Импорт данных из базы данных Microsoft Access
- Импорт данных с веб-страницы
- Копировать-вставить данные из Интернета
- Импорт данных из текстового файла
- Импорт данных из другой книги
- Импорт данных из других источников
- Задача для получения данных в Excel
Как это работает
Инструменты для импорта расположены во вкладке меню «Данные». 
Если подключение отключено, перейдите: 
Далее: 
На вкладке «Центр управления» перейдите: 
Подключаем внешние данные из интернет
В Excel 2013 и более поздних версиях, по умолчанию для импорта информации из внешних источников используется надстройка Power Query. Как это работает? Перейдите: 
Пропишите адрес сайта, с которого импортируются данные: 
Выберите что отобразится, нажмите кнопку «Загрузить». 
Информация подгрузится в лист Excel. Работайте с ними как с простым документом: используйте формулы графики, сводные таблицы. 
Для обновления нажмите ПКМ по таблице: 
Или: 
Импорт внешних данных Excel 2010
Перейдите: 
В новом окне пропишите адрес сайта. Получите информацию из областей страницы, где проставлены желтые ярлыки. Отметьте их мышкой, нажмите «Импорт». 
Отметьте пункт «Обновление». Тогда внешняя информация обновится автоматически. 
Отличите нового и старого мастера импорта
Преимущества Power Query:
- Поддерживается работа с большим числом страниц;
- Промежуточная обработка информации перед загрузкой на лист;
- Информация импортируется быстрее.
Как создать базу данных в Excel? Базой данных в программе Excel считается таблица, которая была создана с учетом определенных требований:
- Заголовки таблицы должны находиться в первой строке.
- Любая последующая строка должна содержать хотя бы одну непустую ячейку.
- Объединения ячеек в любых строках запрещены.
- Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
- Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.
Таким образом, практически любая таблица в Excel может быть преобразована в базу данных. Ее строки являются записями, а столбцы – полями данных.
Функция БИЗВЛЕЧЬ хорошо работает с корректно отформатированными таблицами.
Примеры использования функции БИЗВЛЕЧЬ в Excel
Пример 1. В таблице, которую можно рассматривать как БД, содержатся данные о различных моделях смартфонов. Найти название бренда смартфона, который содержит процессор с минимальным числом ядер.
Вид таблиц данных и критериев:
В ячейке B2 запишем условие отбора данных следующим способом:
=МИН(СТОЛБЕЦ(B1))
Данный вариант записи позволяет унифицировать критерий для поиска данных в изменяющейся таблице (если число записей будет увеличиваться или уменьшаться со временем).
В результате получим следующее:
В ячейке A4 запишем следующую формулу:
Описание аргументов:
- A8:F15 – диапазон ячеек, в которых хранится БД;
- 1 – числовое указание номера поля (столбца), из которого будет выводиться значение (необходимо вывести Бренд);
- A2:F3 – диапазон ячеек, в которых хранится таблица критериев.
Результат вычислений:
При изменении значений в таблице параметров условий мы будем автоматически получать выборку соответственных им результатов.
Тип данных: Числовые значения MS Excel
Числовые значения, в отличие от текстовых, можно и складывать и умножать и вообще, применять к ним весь богатый арсенал экселевских средств по обработке данных. После ввода в пустую ячейку MS Excel, числовые значения выравнивается по правой границе ячейки.
Фактически, к числовым типам данных относятся:
- сами числа (и целые и дробные и отрицательные и даже записанные в виде процентов)
- дата и время
Несколько особенностей числовых типов данных
Если введенное число не помещается в ячейку, то оно будет представлено в экспоненциальной форму представления, здорово пугающей неподготовленных пользователей. Например, гигантское число 4353453453453450 х 54545 в ячейку будет записано в виде 2,37459Е+20. Но, как правило в «жизни» появление «странных чисел» в ячейках excel свидетельствует о простой ошибке.
Если число или дата не помещается в ячейку целиком, вместо цифр в ней появляются символы ###. В этом случае «лечение» ещё более простое — нужно просто увеличить ширину столбца таблицы.
Иногда есть необходимостью записать число как текст, например в случае записи всевозможных артикулов товаров и т.п. дело в том, что если вы запишите 000335 в ячейку, Excel посчитав это значение числом, сразу же удалит нули, превратив артикул в 335. Чтобы этого не произошло, просто поместите число в кавычки — это будет сигналом для Excel, что содержимое ячейки надо воспринимать как текст, то есть выводить также, как его ввел пользователь. Естественно, производить с таким числом математических операций нельзя.
Что представляет собой дата в MS Excel?
Если с числами все более-менее понятно, то даты имеют несколько особенностей, о которых стоит упомянуть. Для начала, что такое «дата» с точки зрения MS Excel? На самом деле все не так уж и просто.
Дата в Excel — это число дней, отсчитанных до сегодняшнего дня, от некой начальной даты. По умолчанию этой начальной датой считается 1 января 1900 года.
А что же текущее время? Ещё интереснее — за точку отсчета каждых суток берется 00:00:00, которое представляется как 1. А дальше, эта единичка уменьшается, по мере того как уменьшается оставшееся в сутках время. Например 12.00 дня это с точки зрения MS Excel 0,5 (прошла половина суток), а 18.00 — 0,25 (прошли 3 четверти суток).
В итоге, дата 17 июня 2019 года, 12:30, «языком экселя» выглядит как 43633 (17.06.19) + 0,52 (12:30), то есть число 43633,52.
Как превратить число в текст? Поместите его в кавычки!
Классификация типов данных
Тип данных — это характеристика информации, хранимой на листе. На основе этой характеристики программа определяет, каким образом обрабатывать то или иное значение.
Типы данных делятся на две большие группы: константы и формулы. Отличие между ними состоит в том, что формулы выводят значение в ячейку, которое может изменяться в зависимости от того, как будут изменяться аргументы в других ячейках. Константы – это постоянные значения, которые не меняются.
В свою очередь константы делятся на пять групп:
- Текст;
- Числовые данные;
- Дата и время;
- Логические данные;
- Ошибочные значения.
Текстовые значения
Текстовый тип содержит символьные данные и не рассматривается Excel, как объект математических вычислений. Это информация в первую очередь для пользователя, а не для программы. Текстом могут являться любые символы, включая цифры, если они соответствующим образом отформатированы. В языке DAX этот вид данных относится к строчным значениям. Максимальная длина текста составляет 268435456 символов в одной ячейке.
Для ввода символьного выражения нужно выделить ячейку текстового или общего формата, в которой оно будет храниться, и набрать текст с клавиатуры. Если длина текстового выражения выходит за визуальные границы ячейки, то оно накладывается поверх соседних, хотя физически продолжает храниться в исходной ячейке.
Дата и время
Ещё одним типом данных является формат времени и даты. Это как раз тот случай, когда типы данных и форматы совпадают. Он характеризуется тем, что с его помощью можно указывать на листе и проводить расчеты с датами и временем. Примечательно, что при вычислениях этот тип данных принимает сутки за единицу. Причем это касается не только дат, но и времени. Например, 12:30 рассматривается программой, как 0,52083 суток, а уже потом выводится в ячейку в привычном для пользователя виде.
Существует несколько видов форматирования для времени:
- ч:мм:сс;
- ч:мм;
- ч:мм:сс AM/PM;
- ч:мм AM/PM и др.
Аналогичная ситуация обстоит и с датами:
- ДД.ММ.ГГГГ;
- ДД.МММ
- МММ.ГГ и др.
Есть и комбинированные форматы даты и времени, например ДД:ММ:ГГГГ ч:мм.
Также нужно учесть, что программа отображает как даты только значения, начиная с 01.01.1900.
Логические данные
Довольно интересным является тип логических данных. Он оперирует всего двумя значениями: «ИСТИНА» и «ЛОЖЬ». Если утрировать, то это означает «событие настало» и «событие не настало». Функции, обрабатывая содержимое ячеек, которые содержат логические данные, производят те или иные вычисления.
Разновидности типов данных
Выделяются две большие группы типов данных:
- константы – неизменные значения;
- формулы – значения, которые меняются в зависимости от изменения других.
В группу “константы” входят следующие типы данных:
- числа;
- текст;
- дата и время;
- логические данные;
- ошибки.
Число
Этот тип данных применяется в различных расчетах. Как следует из названия, здесь предполагается работа с числами, и для которых может быть задан один из следующих форматов ячеек:
- числовой;
- денежный;
- финансовый;
- процентный;
- дробный;
- экспоненциальный.
Формат ячейки можно задать двумя способами:
- Во вкладке “Главная” в группе инструментов “Число” нажимаем по стрелке рядом с текущим значением и в раскрывшемся списке выбираем нужный вариант.
- В окне форматирования (вкладка “Число”), в которое можно попасть через контекстное меню ячейки.
Для каждого из форматов, перечисленных выше (за исключением дробного), можно задать количество знаков после запятой, а для числового – к тому же, включить разделитель групп разрядов.
Чтобы ввести значение в ячейку, достаточно просто выделить ее (с нужным форматом) и набрать с помощью клавиш на клавиатуре нужные символы (либо вставить ранее скопированные данные из буфера обмена). Или можно выделить ячейку, после чего ввести нужные символы в строке формул.
Также можно поступить наоборот – сначала ввести значение в нужной ячейке, а формат поменять после.
Текст
Данный тип данных не предназначен для выполнения расчетов и носит исключительно информационный характер. В качестве текстового значения могут использоваться любые знаки, цифры и т.д.
Ввод текстовой информации происходит таким же образом, как и числовой. Если текст не помещается в рамках выбранной ячейки, он будет перекрывать соседние (если они пустые).
Ошибки
В некоторых случаях пользователь может видеть в Excel ошибки, которые бывают следующих видов:
- #ДЕЛ/О! – результат деления на число 0
- #Н/Д – введены недопустимые данные;
- #ЗНАЧ! – использование неправильного вида аргумента в функции;
- #ЧИСЛО! – неверное числовое значение;
- #ССЫЛКА! – удалена ячейка, на которую ссылалась формула;
- #ИМЯ? – неправильное имя в формуле;
- #ПУСТО! – неправильно указан адрес дапазона.
Подключение к внешним данным
Вы можете получить доступ к внешним источникам через вкладку Данные, группу Получить и преобразовать данные. Подключения к данным хранятся вместе с книгой, и вы можете просмотреть их, выбрав пункт Данные –> Запросы и подключения.
Подключение к данным может быть отключено на вашем компьютере. Для подключения данных пройдите по меню Файл –> Параметры –> Центр управления безопасностью –> Параметры центра управления безопасностью –> Внешнее содержимое. Установите переключатель на одну из опций: включить все подключения к данным (не рекомендуется) или запрос на подключение к данным.
Настройка доступа к внешним данным; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Подробнее о подключении к внешним источникам данных см. Кен Пульс и Мигель Эскобар. Язык М для Power Query. При использовании таблиц, подключенных к данным можно переставлять и удалять столбцы, не изменяя запрос. Excel продолжает сопоставлять запрошенные данные с правильными столбцами. Однако ширина столбцов обычно автоматически устанавливается при обновлении. Чтобы запретить Excel автоматически устанавливать ширину столбцов Таблицы при обновлении, щелкните правой кнопкой мыши в любом месте Таблицы и пройдите по меню Конструктор –> Данные из внешней таблицы –> Свойства, а затем снимите флажок Задать ширину столбца.
Свойства Таблицы, подключенной к внешним данным
Подключение к базе данных
Для подключения к базе данных SQL Server выберите Данные –> Получить данные –> Из базы данных –> Из базы данных SQL Server. Появится мастер подключения к данным, предлагающий элементы управления для указания имени сервера и типа входа, который будет использоваться для открытия соединения. Обратитесь к своему администратору SQL Server или ИТ-администратору, чтобы узнать, как ввести учетные данные для входа.
Подключение к базе данных SQL Server
При импорте данных в книгу Excel их можно загрузить в модель данных, предоставив доступ к ним другим инструментам анализа, таким как Power Pivot.
Существует много различных типов доступных источников данных, и иногда шаблоны соединений по умолчанию, представленные Excel, не работают.
Импорт данных из базы данных Microsoft Access
Мы научимся импортировать данные из базы данных MS Access. Следуйте инструкциям ниже
Шаг 1 – Откройте новую пустую книгу в Excel.
Шаг 2 – Перейдите на вкладку ДАННЫЕ на ленте.
Шаг 3 – Нажмите « Доступ» в группе «Получить внешние данные». Откроется диалоговое окно « Выбор источника данных ».
Шаг 4 – Выберите файл базы данных Access, который вы хотите импортировать. Файлы базы данных Access будут иметь расширение .accdb.
Откроется диалоговое окно «Выбор таблицы», в котором отображаются таблицы, найденные в базе данных Access. Вы можете импортировать все таблицы в базе данных одновременно или импортировать только выбранные таблицы на основе ваших потребностей анализа данных.
Шаг 5 – Установите флажок Включить выбор нескольких таблиц и выберите все таблицы.
Шаг 6 – Нажмите ОК. Откроется диалоговое окно « Импорт данных ».
Как вы заметили, у вас есть следующие опции для просмотра данных, которые вы импортируете в свою рабочую книгу:
- Таблица
- Отчет сводной таблицы
- PivotChart
- Power View Report
У вас также есть возможность – только создать соединение . Далее отчет по сводной таблице выбран по умолчанию.
Excel также дает вам возможность поместить данные в вашу книгу –
- Существующий лист
- Новый лист
Вы найдете еще один флажок, который установлен и отключен. Добавьте эти данные в модель данных . Каждый раз, когда вы импортируете таблицы данных в свою книгу, они автоматически добавляются в модель данных в вашей книге. Вы узнаете больше о модели данных в следующих главах.
Вы можете попробовать каждый из вариантов, чтобы просмотреть импортируемые данные и проверить, как эти данные отображаются в вашей рабочей книге.
-
Если вы выберете « Таблица» , опция «Существующая рабочая таблица» будет отключена, будет выбрана опция « Новая рабочая таблица», и Excel создаст столько таблиц, сколько будет импортировано таблиц из базы данных. Таблицы Excel отображаются в этих таблицах.
-
Если вы выберете Отчет сводной таблицы , Excel импортирует таблицы в рабочую книгу и создаст пустую сводную таблицу для анализа данных в импортированных таблицах. У вас есть возможность создать сводную таблицу на существующем листе или новом листе.
Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей сводной таблицы вместе с полями в каждой таблице.
-
Если вы выберете PivotChart , Excel импортирует таблицы в рабочую книгу и создаст пустую PivotChart для отображения данных в импортированных таблицах. У вас есть возможность создать сводную диаграмму на существующем или новом листе.
Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей PivotChart вместе с полями в каждой таблице.
-
Если вы выберите Power View Report , Excel импортирует таблицы в рабочую книгу и создаст Power View Report в новой рабочей таблице. В последующих главах вы узнаете, как использовать отчеты Power View для анализа данных.
Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей Power View Report вместе с полями в каждой таблице.
-
Если вы выберете опцию – Только создать соединение , между базой данных и вашей книгой будет установлено соединение для передачи данных. Таблицы или отчеты не отображаются в книге. Однако импортированные таблицы по умолчанию добавляются в модель данных в вашей книге.
Вам необходимо выбрать любой из этих параметров в зависимости от вашего намерения импортировать данные для анализа данных. Как вы заметили выше, независимо от выбранной вами опции, данные импортируются и добавляются в модель данных в вашей рабочей книге.
Если вы выберете « Таблица» , опция «Существующая рабочая таблица» будет отключена, будет выбрана опция « Новая рабочая таблица», и Excel создаст столько таблиц, сколько будет импортировано таблиц из базы данных. Таблицы Excel отображаются в этих таблицах.
Если вы выберете Отчет сводной таблицы , Excel импортирует таблицы в рабочую книгу и создаст пустую сводную таблицу для анализа данных в импортированных таблицах. У вас есть возможность создать сводную таблицу на существующем листе или новом листе.
Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей сводной таблицы вместе с полями в каждой таблице.
Если вы выберете PivotChart , Excel импортирует таблицы в рабочую книгу и создаст пустую PivotChart для отображения данных в импортированных таблицах. У вас есть возможность создать сводную диаграмму на существующем или новом листе.
Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей PivotChart вместе с полями в каждой таблице.
Если вы выберите Power View Report , Excel импортирует таблицы в рабочую книгу и создаст Power View Report в новой рабочей таблице. В последующих главах вы узнаете, как использовать отчеты Power View для анализа данных.
Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей Power View Report вместе с полями в каждой таблице.
Если вы выберете опцию – Только создать соединение , между базой данных и вашей книгой будет установлено соединение для передачи данных. Таблицы или отчеты не отображаются в книге. Однако импортированные таблицы по умолчанию добавляются в модель данных в вашей книге.
Вам необходимо выбрать любой из этих параметров в зависимости от вашего намерения импортировать данные для анализа данных. Как вы заметили выше, независимо от выбранной вами опции, данные импортируются и добавляются в модель данных в вашей рабочей книге.
Импорт данных с веб-страницы
Иногда вам может понадобиться использовать данные, которые обновляются на веб-сайте. Вы можете импортировать данные из таблицы на веб-сайте в Excel.
Шаг 1 – Откройте новую пустую книгу в Excel.
Шаг 2 – Перейдите на вкладку ДАННЫЕ на ленте.
Шаг 3 – Нажмите « Из Интернета» в группе « Получить внешние данные ». Откроется диалоговое окно « Новый веб-запрос ».
Шаг 4 – Введите URL-адрес веб-сайта, с которого вы хотите импортировать данные, в поле рядом с адресом и нажмите «Перейти».
Шаг 5 – Данные на сайте появляются. Рядом с данными таблицы будут отображаться желтые значки со стрелками, которые можно импортировать.
Шаг 6 – Нажмите желтые значки, чтобы выбрать данные, которые вы хотите импортировать. Это превращает желтые значки в зеленые поля с галочкой, как показано на следующем снимке экрана.
Шаг 7 – Нажмите кнопку «Импорт» после того, как вы выбрали то, что вы хотите.
Откроется диалоговое окно « Импорт данных ».
Шаг 8 – Укажите, куда вы хотите поместить данные и нажмите Ok.
Шаг 9 – Организовать данные для дальнейшего анализа и / или представления.
Копировать-вставить данные из Интернета
Другой способ получения данных с веб-страницы – копирование и вставка необходимых данных.
Шаг 1 – Вставьте новый лист.
Шаг 2 – Скопируйте данные с веб-страницы и вставьте их на лист.
Шаг 3 – Создайте таблицу с вставленными данными.
Импорт данных из текстового файла
Если у вас есть данные в файлах .txt или .csv или .prn , вы можете импортировать данные из этих файлов, рассматривая их как текстовые файлы. Следуйте инструкциям ниже
Шаг 1 – Откройте новый лист в Excel.
Шаг 2 – Перейдите на вкладку ДАННЫЕ на ленте.
Шаг 3 – Нажмите « Из текста» в группе «Получить внешние данные». Откроется диалоговое окно « Импорт текстового файла ».
Вы можете видеть, что текстовые файлы с расширениями .prn, .txt и .csv принимаются.
Шаг 4 – Выберите файл. Имя выбранного файла появится в поле Имя файла. Кнопка «Открыть» изменится на кнопку «Импорт».
Шаг 5 – Нажмите кнопку «Импорт». Мастер импорта текста – появляется диалоговое окно « Шаг 1 из 3 ».
Шаг 6 – Выберите опцию «С разделителями», чтобы выбрать тип файла, и нажмите «Далее».
Откроется мастер импорта текста – шаг 2 из 3 .
Шаг 7 – В разделе «Разделители» выберите « Другое» .
Шаг 8 – В поле рядом с Другой введите | (Это разделитель в текстовом файле, который вы импортируете).
Шаг 9 – Нажмите Далее.
Откроется мастер импорта текста – шаг 3 из 3 .
Шаг 10 – В этом диалоговом окне вы можете установить формат данных столбца для каждого из столбцов.
Шаг 11. После завершения форматирования данных столбцов нажмите кнопку «Готово». Откроется диалоговое окно « Импорт данных ».
Вы увидите следующее –
-
Таблица выбрана для просмотра и отображается серым цветом. Таблица – единственный вариант просмотра, который у вас есть в этом случае.
-
Вы можете поместить данные либо в существующий рабочий лист, либо в новый рабочий лист.
-
Вы можете установить или не устанавливать флажок Добавить эти данные в модель данных.
-
Нажмите OK после того, как вы сделали выбор.
Таблица выбрана для просмотра и отображается серым цветом. Таблица – единственный вариант просмотра, который у вас есть в этом случае.
Вы можете поместить данные либо в существующий рабочий лист, либо в новый рабочий лист.
Вы можете установить или не устанавливать флажок Добавить эти данные в модель данных.
Нажмите OK после того, как вы сделали выбор.
Данные появятся на указанном вами листе. Вы импортировали данные из текстового файла в книгу Excel.
Импорт данных из другой книги
Возможно, вам придется использовать данные из другой книги Excel для анализа данных, но кто-то другой может поддерживать другую книгу.
Чтобы получать последние данные из другой книги, установите соединение данных с этой книгой.
Шаг 1 – Нажмите DATA> Соединения в группе Соединения на ленте.
Откроется диалоговое окно « Подключения к книге».
Шаг 2. Нажмите кнопку «Добавить» в диалоговом окне «Подключения к книге». Откроется диалоговое окно « Существующие подключения ».
Шаг 3 – Нажмите кнопку Обзор для более … Откроется диалоговое окно « Выбор источника данных ».
Шаг 4 – Нажмите кнопку « Новый источник» . Откроется диалоговое окно мастера подключения к данным .
Шаг 5 – Выберите Other / Advanced в списке источников данных и нажмите Next. Откроется диалоговое окно «Свойства ссылки на данные».
Шаг 6 – Установите свойства канала передачи данных следующим образом –
-
Перейдите на вкладку « Соединение ».
-
Нажмите Использовать имя источника данных.
-
Нажмите стрелку вниз и выберите « Файлы Excel» в раскрывающемся списке.
-
Нажмите ОК.
Перейдите на вкладку « Соединение ».
Нажмите Использовать имя источника данных.
Нажмите стрелку вниз и выберите « Файлы Excel» в раскрывающемся списке.
Нажмите ОК.
Откроется диалоговое окно « Выбрать рабочую книгу ».
Шаг 7 – Найдите место, где у вас есть рабочая книга для импорта. Нажмите ОК.
Откроется диалоговое окно « Мастер подключения к данным » с выбором базы данных и таблицы.
Примечание. В этом случае Excel обрабатывает каждый рабочий лист, который импортируется, как таблицу. Имя таблицы будет именем рабочего листа. Таким образом, чтобы иметь значимые имена таблиц, назовите / переименуйте рабочие листы в зависимости от ситуации.
Шаг 8 – Нажмите Далее. Откроется диалоговое окно мастера подключения к данным с сохранением файла подключения к данным и завершением.
Шаг 9 – Нажмите кнопку Готово. Откроется диалоговое окно « Выбор таблицы ».
Как вы заметили, Name – это имя листа, которое импортируется как тип TABLE. Нажмите ОК.
Соединение данных с выбранной вами рабочей книгой будет установлено.
Импорт данных из других источников
Excel предоставляет вам возможность выбора различных других источников данных. Вы можете импортировать данные из них в несколько шагов.
Шаг 1 – Откройте новую пустую книгу в Excel.
Шаг 2 – Перейдите на вкладку ДАННЫЕ на ленте.
Шаг 3 – Нажмите Из других источников в группе Получить внешние данные.
Появляется выпадающий список с различными источниками данных.
Вы можете импортировать данные из любого из этих источников данных в Excel.
Задача для получения данных в 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.
Источники
- https://public-pc.com/podklyuchenie-vneshnih-dannyh-v-excel/
- https://exceltable.com/funkcii-excel/vyborka-iz-bazy-dannyh-bizvlech
- http://bussoft.ru/tablichnyiy-redaktor-excel/tipy-dannyh-v-redaktore-elektronnyh-tablicz-ms-excel.html
- https://lumpics.ru/data-types-in-excel/
- https://MicroExcel.ru/tipy-dannyh/
- https://baguzin.ru/wp/glava-8-rabota-s-vneshnimi-dannymi-v-tablitsah-excel/
- https://coderlessons.com/tutorials/bolshie-dannye-i-analitika/izuchite-analiz-dannykh-excel/import-dannykh-v-excel
- https://info-comp.ru/obucheniest/375-excel-get-data-from-sql-server.html
+14
1С 8.x : Excel файл как Внешний источник данных
Подключимся и загрузим из файла Excel данные в таблицу значений 1С. Сделать теперь это очень просто.
1. В конфигурации добавляет новый объект метаданных типа «Внешние источники данных» и назовем его просто «Excel».
2. На закладке «Данные» созданного объекта «Excel» добавляем новую таблицу. При этом появится окно мастера добавления таблицы внешнего источника.
Выбираем первый пункт «Вручную», т.к. лично у меня второй пункт «Выбрать из списка таблиц внешнего источника данных» работает нестабильно.
Если Вы все же хотите попробовать добавить таблицу через второй пункт, то в качестве строки соединения в появившемся окне указываем:
Driver={Microsoft Excel Driver (*.xls)};DBQ=C:Documents and SettingsAdminМои документыfinance3.xls; DriverID=790
В этой строке укажите путь к своему файлу. Имя пользователя и пароль я не указывал.
Отмечу, что путь к файлу и строка соединения в дальнейшем не запоминается и нужны эти параметры единожды, чтобы создать автоматически описание полей таблицы источника.
Если соединение прошло нормально, то должен отобразиться список таблиц источника. Флажком можно выбрать нужную таблицу и ее поля.
3. Даем имя новой таблице «Данные». Переходим на вкладку таблицы «Данные». В поле «Имя в источнике данных» указываем имя листа в файле Excel и добавляем в конце знак «$». В моем случае это «Данные$». В табличной части «Поля» добавляем поля и редактируем их тип.
У каждого поля в свойствах должно быть задано как минимум «Имя» и «Имя в источнике данных».
4. Создаем процедуру в которой пишем подключение к источнику и запрос:
Код 1C v 8.х
ПараметрыСоединения = Новый ПараметрыСоединенияВнешнегоИсточникаДанных;
ПараметрыСоединения.СтрокаСоединения=
"Driver={Microsoft Excel Driver (*.xls)};DBQ=C:Documents and SettingsAdminМои документыfinance2.xls; DriverID=790";
ВнешниеИсточникиДанных.Excel.УстановитьОбщиеПараметрыСоединения(ПараметрыСоединения);
ВнешниеИсточникиДанных.Excel.УстановитьСоединение();
Запрос = Новый Запрос();
Запрос.Текст = "ВЫБРАТЬ
| Данные.date КАК Дата,
| Данные.time КАК Время,
| Данные.account КАК Счет,
| Данные.amount КАК Сумма,
| Данные.currency КАК Валюта,
| Данные.category КАК Категория,
| Данные.parent КАК КатегорияРодитель
|ИЗ
| ВнешнийИсточникДанных.Excel.Таблица.Данные КАК Данные";
ТЗ = Запрос.Выполнить().Выгрузить();
Вот и все!
Источник: passion-programmer
Параметры соединения для файла Excel выглядят вот так:
Файлы XLSX (версия Office 2007 и выше)
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:ФайлExcel.xlsx;
Файлы XLS (ранее)
Driver={Microsoft Excel Driver (*.xls)}; DriverID=790; DBQ=D:ФайлExcel.xls;