Как привязать доступ к Excel

Обновлено: 21.11.2024

Чтобы сделать внешний запрос данных к таблице базы данных Microsoft Access, щелкните Данные→ Получить данные→ Из базы данных→ Из базы данных Microsoft Access на ленте Excel или нажмите клавиши ALT+APNDC. Excel открывает диалоговое окно «Импорт данных», в котором вы выбираете имя базы данных Access (используя расширение файла *.mdb), а затем нажимаете кнопку «Импорт».

После того как Excel установит соединение с файлом базы данных Access, который вы выбрали в диалоговом окне «Импорт данных», откроется диалоговое окно «Навигатор». Диалоговое окно Navigator разделено на две панели: Selection слева и Preview справа. Когда вы щелкаете имя таблицы данных или запроса в области выбора, Excel отображает часть данных Access в области предварительного просмотра справа. Чтобы импортировать несколько (связанных) таблиц данных из выбранной базы данных Access, установите флажок «Включить несколько элементов». Затем Excel отображает флажки перед именем каждой таблицы в базе данных. После того, как вы установите флажки для всех таблиц, которые хотите импортировать, у вас появится выбор из следующих вариантов:

Кнопка «Загрузить», чтобы импортировать данные файла Access из элементов, выбранных в навигаторе, непосредственно на текущий рабочий лист, начиная с текущей позиции курсора в ячейке.
Функция «Загрузить в» в раскрывающемся меню кнопки «Загрузить», чтобы открыть диалоговое окно «Импорт данных», в котором вы можете выбрать способ просмотра импортированных данных Access (в виде таблицы данных рабочего листа, сводной таблицы, сводной диаграммы или просто подключения к данным без импорта). какие-либо данные) и куда импортировать данные Access (существующий или новый рабочий лист), а также добавлять ли данные Access в модель данных рабочего листа.
Кнопка «Преобразовать данные», чтобы отобразить таблицы данных Access в редакторе Excel Power Query, где вы можете дополнительно запрашивать и преобразовывать данные перед импортом в текущий лист Excel с помощью параметра «Закрыть и загрузить» или «Закрыть и загрузить в».

Использование навигатора для выбора таблиц данных и запросов из базы данных Northwind Access для импорта в текущий лист Excel.

При выборе параметра «Загрузить в», чтобы указать, как и куда импортировать данные Access, диалоговое окно «Импорт данных» содержит следующие кнопки выбора:

Кнопка параметра «Таблица», чтобы импортировать данные из таблицы (таблиц) данных Access в таблицу данных Excel на текущем или новом рабочем листе — см. следующие маркеры «Существующий рабочий лист» и «Новый рабочий лист». Обратите внимание, что при импорте нескольких таблиц данных параметр "Существующий лист" больше недоступен, и данные из каждой импортированной таблицы данных будут импортированы на отдельный новый лист в текущей книге.
Кнопка параметра "Отчет сводной таблицы" (по умолчанию), чтобы данные из таблиц данных Access были импортированы в новую сводную таблицу, которую можно построить с данными Access.
Кнопка параметра "Сводная диаграмма", чтобы данные из таблиц данных Access были импортированы в новую сводную таблицу со встроенной сводной диаграммой, которую можно построить на основе данных Access.
Кнопка «Только создать подключение», чтобы создать подключение к таблице (таблицам) базы данных Access, которую вы сможете использовать позже для фактического импорта ее данных.
Кнопка выбора «Существующий рабочий лист», чтобы импортировать данные из таблиц данных Access в текущий рабочий лист, начиная с адреса текущей ячейки, указанного в текстовом поле ниже.
Кнопка параметра "Новый рабочий лист", чтобы данные из таблиц данных Access были импортированы в новые листы, которые добавляются в конец листов, уже находящихся в рабочей книге.
Флажок «Добавить эти данные в модель данных», чтобы добавить импортированные данные из таблиц данных Access в модель данных, уже определенную в книге Excel, с помощью связанных ключевых полей.
Кнопка раскрывающегося списка "Свойства", чтобы открыть раскрывающееся меню с флажком "Импорт связей между таблицами" (установлен по умолчанию) и элементом "Свойства". Снимите флажок, чтобы запретить Excel распознавать отношения, установленные между таблицами данных в Access. Нажмите кнопку "Свойства", чтобы открыть диалоговое окно "Свойства подключения", в котором можно изменить все свойства подключения, в том числе время обновления данных Access на листе Excel и способ установления подключения.

Рабочий лист "Клиенты" с данными, импортированными из таблицы данных Access "Клиенты" в образце базы данных "Борей".

На следующем изображении показана та же самая новая книга, на этот раз с выбранным листом «Заказы» и открытым диалоговым окном «Управление отношениями» (нажатием кнопки «Отношения» на вкладке «Данные» или нажатием клавиш Alt+AA). Когда Excel импортирует эти две таблицы данных, он автоматически выбирает и сохраняет исходную связь между ними в базе данных «Борей», где поле CustomerID является полем первичного ключа в таблице данных «Клиенты» и полем внешнего ключа в таблице данных «Заказы».

Рабочий лист «Заказы» с данными, импортированными из таблицы данных «Заказы» в образце базы данных «Борей», показывающей связь с таблицей «Клиенты».

После импорта внешних данных в один из ваших листов Excel вы можете использовать кнопки "Фильтр", прикрепленные к различным полям, для сортировки и фильтрации данных.

После импорта данных из внешнего источника, например базы данных Microsoft Access, на лист Excel автоматически отображает панель задач «Запросы и подключения» с двумя вкладками: «Запросы», где отображаются источники данных, импортированные в текущая рабочая книга и соединения, которые отображают их связь с моделью данных рабочей книги (и друг с другом, если имеется несколько источников и они связаны друг с другом). Если эта область задач в настоящее время не отображается на текущем листе, щелкните Данные → Запросы и подключения (или нажмите Alt+AO), чтобы снова отобразить ее.

Excel хранит список всех внешних источников данных и запросов данных, которые вы отправляете в текущую книгу, чтобы вы могли повторно использовать их для импорта обновленных данных из другой базы данных или веб-страницы. Чтобы быстро повторно подключиться к источнику данных, нажмите кнопку «Недавние источники» на вкладке «Данные» (Alt+PR), чтобы открыть диалоговое окно «Последние источники», в котором вы щелкаете имя внешнего файла, прежде чем нажимать кнопку «Подключиться». Чтобы повторно использовать запрос, нажмите кнопку «Существующие подключения» на вкладке «Данные» (Alt+AX), чтобы открыть диалоговое окно «Существующие подключения» для доступа к этому списку, а затем щелкните имя запроса, который нужно повторить, прежде чем нажать кнопку «Открыть».

Об этой статье

Эта статья взята из книги:

Об авторе книги:

Грег Харви, доктор философии, опытный преподаватель компьютерных технологий, работающий еще со времен DOS и Lotus 1-2-3. Он читал курсы по управлению электронными таблицами и базами данных в Университете Золотых Ворот и написал десятки книг, в том числе многие из серии Для чайников.

Компания Northwind Trading Company, занимающаяся мелкой оптовой торговлей продуктами питания, чувствует себя неплохо после того, как они перешли в онлайн. Розничные торговцы по всей стране покупают все, от лосося до мюсли, и быстро доставляют его в свои магазины.

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

Майкрософт Эксель

Превратите данные в идеи.

Создание подключения к данным между Excel и Access

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

<р>1. Перейдите на вкладку «Данные» в Excel и нажмите кнопку «Из доступа».

2. В диалоговом окне «Выбор источника данных» перейдите в папку, где хранится база данных Access, выберите ее и нажмите кнопку «Открыть».

<р>3. В диалоговом окне "Выбрать таблицу" выберите таблицу из базы данных для импорта.

<р>4. Примите параметры по умолчанию в диалоговом окне «Импорт данных» и нажмите «ОК».

Excel и Access теперь подключены, и данные из таблицы Northwind CustomersExtended отображаются в Excel.

Обновить данные

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

Принудительное обновление

Чтобы принудительно обновить данные, нажмите кнопку «Обновить все» на вкладке «Данные». Это мгновенно импортирует последние данные Access в Excel.

Настраиваемое обновление

Northwind может настроить режим обновления своей книги следующими способами:

Включить фоновое обновление (этот параметр позволяет им продолжать работу в Excel во время выполнения операции обновления).
Обновлять данные через указанный период времени (например, каждые 30 минут)
Обновлять данные при открытии книги

<р>1. Нажмите кнопку «Подключения» на вкладке «Данные», а затем нажмите кнопку «Свойства» в диалоговом окне «Подключения к книге».

<р>2. В диалоговом окне «Свойства подключения» выберите нужные параметры управления обновлением и нажмите «ОК».

Все подключено!

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

Доступ к базе данных

База данных Access – это система управления реляционными базами данных, которая эффективно и упорядоченно сохраняет большие объемы данных.

Подключение базы данных Access в качестве источника данных к Excel

Добавить ссылку на объект данных AcitveX

Во-первых, на листе Excel необходимо перейти на вкладку "Разработчик".
Затем вам нужно выбрать параметр Visual Basic в разделе «Код».

Теперь в Microsoft Visual Basic нам нужно использовать ADO для подключения к базе данных.
Для этого вам нужно добавить ссылку на объект ADO.
Вы должны добавить модуль в свой проект VBA и нажать на инструменты.

Затем вам нужно нажать на ссылки.

Теперь вам нужно найти библиотеку объектов данных Microsoft ActiveX.
Здесь вам нужно проверить последнюю версию, которая у вас есть, и нажать кнопку ОК, и все готово.

Наконец, вы можете создать ссылку на базу данных Access.

Напишите код VBA для установления соединения с базой данных Access

Чтобы подключить Excel к базе данных Access, у вас должна быть база данных Access.
Во-первых, на листе Excel необходимо перейти на вкладку "Разработчик".
Затем вам нужно выбрать параметр Visual Basic в разделе «Код».

Теперь вам нужно скопировать и вставить приведенный ниже код.

После этого вам нужно сохранить код, выбрав его, а затем закрыть окно.

Опять же, вам нужно перейти в электронную таблицу Excel и щелкнуть вкладку "Разработчик".
Необходимо выбрать параметр "Макросы" в разделе "Код".

Теперь вам нужно убедиться, что выбрано имя вашего макроса, и нажать кнопку "Выполнить".

Наконец, вы получите выходные данные, поскольку данные в Excel будут подключены к базе данных в Microsoft Excel.

Закрытие

Мы надеемся, что это краткое руководство даст вам рекомендации по подключению Excel к базе данных Access с помощью Excel VBA. Пожалуйста, оставьте комментарий в случае возникновения каких-либо вопросов и не забудьте также упомянуть ваши ценные предложения. Большое спасибо за посещение нашего сайта!! Продолжайте учиться на Geek Excel!! Подробнее о формулах Excel!!

У меня есть база данных в MS Access, и мне нужно связать какую-то ячейку в MS Excel.

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

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

Мне нужно, чтобы решения одинаково работали в MS Excel 2007 и в MS Excel 2016

1 Ответ 1

В Excel перейдите на вкладку "Данные" > "Получить внешние данные" > "Из других источников" > "Из Microsoft Query"

Оттуда снимите флажок "Использовать мастер запросов для создания/редактирования запросов" (это связано с тем, что мастер запросов не позволяет указать критерии в поле без его выбора) и обнаружите, что вы получаете доступ к базе данных. На этом экране вы можете либо ввести SQL для своего запроса, либо использовать инструменты для его создания. Чтобы добавить поля, дважды щелкните имена в «панели таблицы», которая должна отображаться по умолчанию, а чтобы добавить критерии, нажмите кнопку «очки с символом фильтра» и добавьте критерии в новую панель.

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

Вот несколько изображений, которые могут оказаться полезными

Это моя исходная таблица для справки (дважды щелкните *, чтобы появились все столбцы)

Здесь нажмите добавить панель критериев

Добавьте критерий, основанный на первичном ключе, который ограничит вас только нужными записями (продолжайте добавлять строки "или", если вы хотите несколько строк)

Затем, если вы импортировали все столбцы, а не только нужные, щелкните каждый заголовок и удалите их (просто удалите их из запроса).

Теперь у меня есть столбец "Высота" только для строки с идентификатором 4.

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

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

Читайте также: