Практикум: «Обмен данными между Microsoft Access и Microsoft Excel»

Использование внешних данных (импорт, экспорт и связывание)

При работе с Access зачастую возникает необходимость использования данных, сохраненных в других форматах (например, в файлах .xls) или расположенных в других местах (по сравнению с текущей базой данных). Для таких случаев используют одно из следующих средств:

1) импорт данных;

2) связывание.

Часто приходится выполнять обратную операцию – передавать данные из Access в другие приложения – Word, Excel, Lotus или другую базу данных – MS Access, Microsoft SQL Server, Oracle. В таких случаях проводят процедуру 3) экспорта данных.

Импорт данных

В Access могут быть импортированы файлы следующих типов: файлы Microsoft Access (.mdb, можно импортировать таблицы, запросы и прочие объекты), файлы электронных таблиц(Microsoft Excel (.xls), Lotus Notes (.wks, wrk)), Microsoft Outlook, Текстовые файлы (.txt), вэб-страницы (html, xml), ODBC Databases (файлы других баз данных).

Для импорта нужного файла нужно выбрать вкладку Файл, затем Внешние данные, после чего выбираем Импорт (File, Get External Data, Import). Появится диалоговое окно Импорт (Import). Далее выбираем тип импортируемого файла (например, Microsoft Excel), находим нужный файл и нажимаем Импорт. Можно произвести импорт другим способом: в окне базы данных щелкнуть на свободном месте правой кнопкой мыши и в появившемся окне выбрать строку Импорт.

Импортировать данные можно только в таблицы – в новые или уже существующие.

Связывание

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

Чтобы присоединить внешнюю таблицу к базе данных, выберите команду Файл, Внешние данные, Связь с таблицами (File, Get External Data, Link Tables). Появится диалоговое окно Связь, в котором нужно выбрать тип связываемого файла и путь к нему. Возможные типы файлов для связывания те же, что и для импорта.

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

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

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

Второй способ обновления связи с внешней таблицей — это использование Диспетчера связанных таблиц (Linked Table Manager). Чтобы воспользоваться Диспетчером связанных таблиц: выберите команду меню Сервис, Служебные программы, Диспетчер связанных таблиц (Tools, Database Utilities, Linked Table Manager).

Экспорт данных

Экспортировать данные из Access можно в файлы различных типов (уже перечисленные + текстовый формат RTF). Экспортировать данные Access можно не только из таблицы, но и из запроса. Можно экспортировать не только данные, но и объекты Access — формы, отчеты, макросы. Для экспорта данных следует: 1) в окне базы данных выбрать экспортируемый объект (например, нужную таблицу), выделив его мышкой; 2) выбрать в строке меню команду Файл и в раскрывшемся меню выбрать Экспорт(или щелкнуть на нужном объекте правой кнопкой мыши и в появившемся окне выбрать Экспорт); 3) В окне появившемся окне Экспорт объектавыбрать тип файла и путь к нему, куда будет происходит экспорт.

Access позволяет быстро экспортировать данные из таблицы в файл Microsoft Excel. Выполняется это с помощью команды Связи с Office (Office Links). При этом можно экспортировать как всю таблицу, так и ее часть. Если нужно экспортировать всю таблицу, выделите эту таблицу в списке таблиц окна базы данных и и выберите Сервис ->Связи с Office->Анализ в Microsoft Office Excel (или нажмите стрелку справа от кнопки Связи с Office на панели инструментов). Access создаст новый файл XLS, скопирует в него данные из выделенной таблицы, запустит приложение Excel и откроет в нем этот файл. Аналогично можно экспортировать только часть таблицы Access, например подмножество строк или даже диапазон ячеек. Для этого необходимо открыть таблицу в режиме Таблицы, выбрать подмножество строк, например с помощью фильтра, или выделить диапазон ячеек, затем выполнить команду Сервис, Связи с Office, Анализ в MS Excel (Tools, Office Links, Analyze It with Microsoft Excel), и выбранные данные будут экспортированы в Excel.

 

Практикум: «Обмен данными между Microsoft Access и Microsoft Excel»

Цель работы:научиться производить обмен данными между MS Access и MS Excel. Данные будут храниться в базе данных Access, а расчеты проводиться в Excel.

Задача:расчет месячной премии сотрудников банка.

Ход работы:

1)Запустите MS Access. Создайте новую или откройте уже существующую базу данных. Создайте таблицу «Сотрудники». Она должна иметь следующую структуру:

 

Занесите в таблицу данные сотрудников (5-10 человек). Сохраните и закройте таблицу.

2) Перейдите к окну базы данных. Выделите левой кнопкой мыши таблицу «Сотрудники» и произведите быстрый экспорт в MS Excel (Сервис->Связи с Office->Анализ в Microsoft Office Excel).

3) В открывшемся файле Excel вы увидите 5 заполненных столбцов (Код сотрудника, ФИО, Должность, Телефон, Зарплата) с введенными вами данными. Дополните таблицу следующими столбцами: Премия, Суммарный доход, Средняя зарплата и Ставка (рис.1).

Рис. 1

4) В ячейке H2 рассчитайте среднюю зарплату сотрудников. В ячейках I2 и I3 введите значения процента премии: 20% и 10% соответственно.

Премия сотрудников (ячейки F2:F6) рассчитывается по формуле:

«Если зарплата сотрудника<средней зарплаты, то его премия равна зарплата*20%, в противном случае, т.е. если зарплата сотрудника больше средней зарплаты, то его премия равна зарплата*10%». (Рекомендуется использовать функцию ЕСЛИ).

В ячейках G2:G6 рассчитайте суммарный доход каждого сотрудника: он равен сумме зарплаты и премии.

Сохраните файл под названием «Сотрудники» и закройте его. Запомните адрес, куда вы сохранили файл.

5) Произведите импорт данных из MS Excel в МS Access. Для этого вернитесь к окну базы данных MS Access. Выберите Файл->Внешние данные->Импорт. В строке Тип Файлов открывшегося окна Импорт выберите Microsoft Excel и найдите созданный вами файл «Сотрудники». Нажмите кнопку Импорт. Откроется окно «Импорт электронной таблицы» (рис.2), в котором должен стоять флажок «Первая строка содержит заголовки столбцов».

Рис. 2

 

Нажмите далее. В следующем окне выберите пункт Данные необходимо сохранить в новой таблице.Нажмите Далее. Появится окно, позволяющее описывать каждое поле создаваемой таблицы. Щелкните на столбце Код сотрудника и в поле Индекс выберите Да (Совпадения не допускаются). (рис. 3) Нажмите Далее. В следующем окне выберите пункт Определить ключ (рис.4). Проверьте, что Access выбрал столбец Код сотрудника. Щелкните Далее.

Рис. 3

 

Рис. 4

В следующем окне в строке «Импорт в таблицу» наберите: Сотрудники (или оставьте без изменений, если это уже сделал Access). Нажмите готово. На вопрос «Переписать существующую таблицу или запрос?» ответьте Да.

Откройте таблицу Сотрудники. Она должна содержать новые столбцы: Премия, Суммарный доход, Средняя зарплата и Ставка (рис. 5).

Рис. 5

6) Однако импорт данных обладает следующим недостатком: в случае каких-либо изменений (изменение размеров зарплаты сотрудников или изменение ставок премии) все вышеперечисленные действия придется повторить. Чтобы этого избежать, гораздо удобнее произвести процедуру связывания, т.е. связать экселевский файл «Сотрудники» с базой данных Access. Для этого вернемся к окну базы данных. Выберите Файл->Внешние данные->Связь с таблицами (или щелкните правой кнопкой мыши на свободном месте и в появившемся меню выберите строку «Связь с таблицами»). В открывшемся окне Связь выберите Тип файлов Microsoft Excel,найдите файл «Сотрудники» и нажмите кнопку Связь.В следующем окне выберите объект Листыи щелкните Далее. Следуйте указаниям Мастера, в последнем окне выберите имя связанной таблицы (например, «СотрудникиExc») и нажмите Готово. Откройте связанную таблицу и попробуйте изменить в ней данные, например, удалить какого-либо нового сотрудника. Если подобное действие не сработает, перейдите в Конструктор и задайте ключевое поле «Код сотрудника». Сохраните изменения и вновь перейдите в режим таблицы. Попробуйте произвести изменения в таблице. Сохраните произведенные изменения и закройте таблицу.

Теперь все изменения в экселевском файле «Сотрудники» будут автоматически отражаться в связанной таблице «СотрудникиExc» базы данных Access.