Экспорт данных из Access в Excel

Экспорт данных из Access в Excel осуществляется следующим образом: вкладка «Внешние данные»> группа «Экспорт»> кнопка «Excel» (рис. 6.18).

Рис.6.18 Экспорт данных из Access в Excel

В окне «Экспорт» выбираем файл назначения (рис. 6.19).

Рис.6.19 Окно «Экспорт»

Результат экспорта из Access в Excel показан на рис. 6.20.

Рис.6.20. Результат экспорта из Access в Excel

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

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

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

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

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

При создании связи в таблице Access с таблицей Excel (открыть в Accessтаблицу Excel) и обновлении данных в Excel данные Access также будут обновляться.

Напомним, что отдельные вопросы обмена данными между приложениями рассмотрены в § 16 главы 3 и в § 11 Главы 4.

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

Сквозные задачи

Задача 1.«Выбор фотоаппарата»

Постановка задачи

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

Найти в Интернете информацию о фирмах (10-15 фирм):

Название фирмы;

адрес;

телефон;

сайт;

вид товара или услуги (2-3);

характеристики товара или услуги;

цена на определенную дату;

фото товара.

Создать базу данных. Продумать структуру базы (количество таблиц, их связь, типы полей). База должна включать не менее 3-х таблиц.

Вариант структуры таблиц:

1-я таблица: название фирмы-продавца (10-15 фирм), адрес, телефон, сайт (данные взять из Интернета);

2-я таблица: марка товара(три-четыре марки), цена на определенную дату, название фирмы-продавцы.

3-я таблица: марка товара; характеристики товара, фото товара.

В Excel импортировать таблицу базы.

Построить графики цен по каждому виду товара (ось х – фирма, у – цена).

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

Построить, если необходимо, сводную таблицу по фирмам и маркам. Определить фирму, продающую определенную марку за наилучшую цену.

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

Отчет оформить в Word (постановка задачи, ход выполнения работы, результаты).

Рекомендации для выполнения

Рассмотрим пример с фирмами, занимающимися продажей фотоаппаратов.

1. В Интернете находим информацию о фирмах, продающих фотоаппараты. Сначала, информацию можно собрать в файл Word.

Например:

Продукция категории «Фото»

Где купить продукцию категории «Фото»? Каталог «Shopping Online» поможет с поиском лучших и проверенных интернет-магазинов.

1.	Foto.ru Foto.ru: широкий ассортимент фотоаппаратов, фотокамер, объективов, видеокамер, телескопов, микроскопов, биноклей www.foto.ru - подробнее
2.	HiLife Основным видом деятельности фирмы является торговля фото/видео оборудованием, компьютерной и оргтехникой, радиотехническими устройствами www.hilife.ru - подробнее Нет Canon
3.	Неоград Интернет-магазин Неоград как самостоятельное подразделение открылся 18 октября 2005 года. Специализация - портативная цифровая техника и электроника. www.neograd.ru - подробнее Сайт Без цены
4.	ОнЛайн Трейд На сегодняшний момент ОнЛайн Трейд является дистрибутором таких всемирно известных компаний, как: Canon, Nikon, Sony, Panasonic, Apple, Olympus, Pentax, Wacom и других. www.onlinetrade.ru - подробнее Нет Canon G11
5.	ZumShop Интернет-магазин фототехники/ www.zumshop.ru - подробнее

2. Создаем пустые таблицы в Accesscпродуманной структурой (рис. 6.21–6.23).

Рис.6.21 Структура таблицы Фирмы-продавцы

Рис.6.22 Структура таблицы «Цены фотоаппаратов»

Рис.6.23 Структура таблицы «Фотоаппараты»

3. Организуем связь таблиц (рис.6.24).

Рис.6.24 Связь таблиц

4. Заполняем таблицы данными, начиная с таблиц «Фирмы-продавцы» и «Фотоаппараты» (рис. 6.25–6.27).

Рис.6.25 Содержимое таблицы «Фирмы-продавцы»

Рис.6.26. Содержимое таблицы «Фотоаппараты»

Рис.6.27. Содержимое таблицы «Цены фотоаппаратов»

Тип поля «Фото» в таблице «Фотоаппараты – поле объекта OLE.

Для вставки фото фотоаппарата в контекстном меню выбирается команда «Вставить объект» и далее (рис. 6.28).

Рис.6.28. Вставка изображения

5. Создаем отчеты.

Пример отчета показан на рис. 6.29:

Рис.6.29. Отчет по таблице «Фотоаппараты»

Получить сведения о лучших ценах на фотоаппараты в разных фирмах можно, сформировав отчет с группировкой по фотоаппаратам (рис. 6.30).

Рис.6.30. Отчет по таблице «Цены фотоаппаратов»
с группировкой по фотоаппаратам

6. Создаем таблицу в Excel.

Импортируем таблицу базы данных вExcelиз Access(вкладка «Данные»→ Из Access→выбираем файл базы (рис. 6.31)→выбираем нужную таблицу из базы (рис.6.32)→выбираем способ представления данных (рис. 6.33)→результат импорта (рис. 6.34).

Рис.6.31. Выбор файла базы

Рис.6.32. Выбор таблицы из базы

Рис.6.33. Выбор способа представления данных

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

Рис.6.34. Результат импорта

Выбрав информацию по определенному фотоаппарату (рис. 6.35–6.36), строим график цен по фирмам (рис. 6.37).

Рис.6.35. Задание фильтра

Рис.6.36. Информация по Cannon

Рис.6.37. График цен

7. Создаем сводную таблицу в Excel (Вставка →Сводная таблица). Выбор параметров сводной таблицы и результат показаны на рис.6.38–6.39.

Рис.6.38. Выбор параметров сводной таблицы

Рис.6.39. Сводная таблица

Задача 2. «Выбор гостиницы»

Постановка задачи

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

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

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

Для выполнения задания нужно создать базу данных в Access.Найти в Интернете и ввести в базу данных нужную информацию по каждому объекту. Экспортировать таблицы Access и запросы, требующие вычислительной обработки в Excel.Построить графики по изменяющимся параметрам выбора. Вычислить функцию «цена/качество» для каждого рассматриваемого объекта отдыха и выбрать лучший объект по минимальному значению функции. Составить в Word отчет по заданию. Сделать в PowerPointпрезентацию выбора места отдыха.

Рекомендации для выполнения

1. Создаем в Access базу данных и пустые таблицыc продуманной структурой, например (рис. 6.40–6.43). Организуем связь таблиц (рис. 6.44).

Рис.6.40. Структура таблицы «Гостиницы»

Рис.6.41. Структура таблицы «Цена гостиницы»

Рис.6.42. Структура таблицы «Цена перелета»

Рис.6.43. Структура таблицы «Солнечные дни»

Рис.6.44. Связь таблиц

2. В Интернете находится информация о предполагаемых местах отдыха:

– страна;

– город;

– название гостиницы;

– количество звезд;

– адрес;

– телефон;

– фото;

– сайт;

– цена проживания на каждый день отдыха;

– цена перелета;

– солнечные дни в данном месте по датам;

– температура воды на каждый день отдыха;

– температура воздуха на каждый день отдыха.

Найденные данныевводятся в таблицы Access, начиная с таблицы «Цена гостиницы»(рис. 6.45–6.49).

Рис.6.45. Содержание таблицы «Цена гостиницы»

Рис.6.46. Содержание таблицы «Гостиницы»

Рис.6.47.Содержание таблицы «Цена перелета»

Рис.6.48.Содержание таблицы «Солнечные дни»

Рис.6.49.Содержаниетаблицы «Температура воды»

В базе данных можем получить результаты запросов и нужные отчеты по таблицам или запросам.Примеры на рис. 6.50–6.52.

Рис.6.50. Запрос на выбор гостиниц 3* в Черногории и Испании

Рис.6.51. Результат запроса «Гостиницы 3* в Черногории и Испании»

Рис.6.52. Отчет по запросу «Гостиницы 3* в Черногории и Испании»

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

4. В Excelрассчитываем функцию «цена/качество»по формуле

.

По результатам расчета выбираем лучшее место отдыха.

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

Расширенный вариант постановки задачи «Выбор гостиницы»

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

Параметры выбора для варианта пляжного отдыха – цена проживания, цена перелета,цена трансферта, солнечные дни, дни без осадков, температура воздуха, температура воды, расстояние до пляжа.

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

В качестве возможного начала времени отдыха выберем 5, 15, 25 августа и 1 сентября.

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

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

При этом обобщенная функция имеет следующий вид:

.

Здесь -величины параметров функции, –веса параметров.

Например, для варианта пляжного отдыха,подсчитаем в планируемом интервале:

– число дней, где температура воздуха будет в комфортных для нас пределах от 24 до 29 градусов,

– воды будет от 22 до 27 градусов,

– число дней без осадков,

– число солнечных дней,

– обратная величина расстояния до пляжа в сотнях метров, умноженная на число дней пребывания (при расстоянии 200 метров и 7 днях пребывания

Вес гостиницы может учитывать комфортность проживания. Например, в отеле 3 звезды = 1.0;в отеле 4 звезды = 0.8;в отеле 5 звезд = 0.5 (Большие цены «смягчаются» меньшими весами).

Вес перелета может учитывать класс салона (эконом, бизнес) и время в пути. Например, при перелете эконом классом прямым рейсом примем .При перелете бизнес классом прямым рейсом примем

Перелет непрямыми рейсами может занимать очень разное время (сильно зависит от стыковок). Возникающее при этом существенное снижение комфортности можно учесть, увеличив исходный вес перелета в соответствии с потерями времени. Пусть при перелете эконом классом прямым рейсом «туда» время в полете t₁ = 5.0 часов, «обратно»t₂ = 4.5 часа, а при перелете с пересадками, «туда»T₁ = 7.5 часов, «обратно»T₂ = 12.5 часа. При этом вес перелета можно рассчитать так:

P_п = 1.0 + k((T₁ – t₁)/ t₁ + (T₂ – t₂)/ t₂).

При указанных параметрах и k = 0.5 получим

P_п = 1.0 + 0.5(0.5 +1.8) = 2.15

Если лишнее время в полете особо не напрягает, можно брать k< 0.5

Веса (i = 1,…,5)в зависимости от личных предпочтений могут быть приняты как больше, так и меньше единицы. Например, вес дней без дождя может быть принят существенно больше единицы, а вес удаленности от пляжа меньше единицы. Функция вычисляется для каждого объекта по всем принятым интервалам отдыха. Результаты вычислений функции представляются в сводной таблице. Минимальное значение функции определит место и время предпочтительного отдыха.

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

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

Литература

1. Информатика. Базовый курс: Учебник для Вузов.3-е издание/ Симонович и др.–Спб: Питер, 2013.–640 с.

2. Информатика. Учебник для ВУЗов. 1-е издание/ Волков В.Б., Макарова Н.В.–Спб: Питер, 2013.–576 с.

3. Практикум по информатике. MicrosoftOffice 2007/ Сост. М.И.Коробочкин, Г.З. Гарбер, Е.Е. Дмитриева, В.С. Красницкий, Е.В. Калинова, Ю.Н. Тимохин, С.С. Репин; 3-е изд., исп. и доп.–М.: ГУЗ, 2008 г.

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ.. 3

Глава 1. РАБОТА В СРЕДЕ ТЕКСТОВОГО РЕДАКТОРА.. 5

MICROSOFT WORD.. 5

§1. Общие сведения, терминология MicrosoftWord. 5

§2. Работа с файлами. 9

§3. Ввод и форматирование текста. 12

§4. Создание таблиц. 21

§5. Редактор формул. 29

§6.Инструмент SmartArt и создание фигур. 36

§7. Верстка многостраничных документов. 39

§8. Режимы отображения документа. 47

§9. Работа с документами, содержащими гиперссылки. 54

§10. MicrosoftPowerPoint 59

Тест по теме «Microsoft Word 2010». 62

Глава 2. ОПЕРАЦИОННАЯ СИСТЕМА «WINDOWS 7». 63

§1. Общие сведения о системе «WINDOWS 7». 63

§2. Сервисное обслуживание файловой системы.. 73

§3. Программы архивации. 79

§4. Новинки Windows 7. 82

§5 Программа Фотоальбом WindowsLive. 85

Глава 3. РАБОТА В СРЕДЕ ТАБЛИЧНОГО ПРОЦЕССОРА EXCEL.. 92

§1. Вид окна Excel 2010. 92

§2. Редактирование информации в ячейках. 93

§3. Форматы ячеек. 94

§4. Копирование и перемещение информации. 95

§5. Создание прогрессий. 96

§6. Выполнение арифметических операций. 97

§7. Расчёты с использованием функций. 103

§8. Формулы массива. 118

§9. Индикация ошибок при использовании формул. 121

§10. Решение уравнений с одним неизвестным с использованием функций Excel 2010 (подбор параметра) 122

§11. Решение оптимизационных задач линейного инелинейного программирования с помощью процедуры«Поиск решения». 126

§12. Графическое представление данных с помощью.. 134

диаграмм и графиков. 134

§13. ИспользованиеExcelдля управлениябазами данных. 137

§14. Анализ данных в Excel с помощью сводных таблиц. 142

§15. Создание и использование макросов в Excel 144

§16. Одновременное использование Excel и Word. 148

Задачи для самостоятельного решения. 149

Глава 4. СИСТЕМА УПРАВЛЕНИЯ БАЗОЙ ДАННЫХ ACCESS. 157

§ 1.Введение. 157

§ 2. Технология построения реляционной базы данных. 160

§3. Создание базы данных. 168

§ 4. Установка и исправление свойств полей. 173

§ 5. Определение структурной связи между таблицами. 183

§ 6. Ввод, просмотр и корректировка данных. 186

§ 7. Создание форм для ввода/редактирования данных в таблицах. 193

§ 8. Вывод на печать характеристик и содержимого таблиц. 203

§ 9. Сортировка, фильтрация и организация выборки данных. 205

§ 10. Создание и выполнение запросов на выборку данных. 211

§ 11. Создание отчетов. 218

Глава5. ОСНОВЫ РАБОТЫ В СРЕДЕ ИНТЕРНЕТ.. 226

§1.Введение. 226

§2.История Интернета. 228

§3.Интернет в России. 230

§4.Адреса и имена серверов. 230

§5.Виды доступа в Интернет.. 231

§6.World Wide Web–«Всемирная паутина». 232

§7.URL и протоколы доступа к ресурсам Интернета. 233

§8.HTTP- и FTP-серверы.. 234

§9.Браузер Internet Explorer. 235

§10.Поисковые системы Интернета. 236

§11.E-mail–электронная почта. 238

§12.Почтовая система Mail.ru. 239

§13. Программы архивации и разархивации. 244

§14.Программное обеспечение для создания сайтов. 245

§15. Текстовый процессор Word и Интернет.. 246

§16. Размещение набора Web-страниц на сайте Narod.ru. 247

§17.Создание сайтов средствами Мастерской Narod.ru. 248

§18.Интернет-магазины и интернет-услуги. 250

§19.Безопасность работы в Интернете. 251

Приложение1. Темы рекомендуемых лабораторных работ.. 254

Приложение 2. Кодировка русских букв латинскими. 256

Приложение3. Список полезных сайтов. 256

Глава 6. СВЯЗЬ ПРИЛОЖЕНИЙ MICROSOFT OFFICE 2010. 257

§1.Введение. 257

§2.Основные средства обмена данными между приложениями Microsoft Office. 257

§3. Сквозные задачи. 268

Литература. 290

ОГЛАВЛЕНИЕ.. 291