6. Приложения на основе табличного процессораExcel
Связь рабочей
книги с базой данных выполняется при
помощи создания запроса командой Данные/
Импорт внешних данных/ Создать запрос.
Связь организуется
при помощи окна «Выбор источника данных».
На вкладке «Базы данных» выбирается тип базы данных, например, база данных MS Access.
Щелчок мышкой по кнопке ОК запускает мастер подключения к источнику данных. В окне «Выбор базы данных» при помощи полей «Диски», «Каталоги» и «Имя базы данных », определяется путь к файлу нужной БД.
Щелчок по кнопке
ОК открывает окно «Создание запроса:
выбор столбцов».
Используя кнопки
выбора
переместить нужные столбцы из поля
«Имеющиеся таблицы и столбцы» в поле
«Столбцы запроса». Нажать кнопку «Далее».
В следующем окне определить правило отбора данных по строкам таблицы, используя условия. Например, Код клиента больше 1.
Щелчок по кнопке «Далее» позволяет перейти к сортировке записей в запросе.
Щелчок по кнопке
«Далее» открывает окно «заключительный
шаг». В этом окне выбрать режим «Вернуть
данные в Microsoft
Excel».
Нажать кнопку «Готово».
Программа выдаст запрос о месте размещения данных. Выбрать опцию «Имеющийся лист». Нажать ОК.
Данные будут помещены в рабочую книгу. Для работы с полученной информацией открывается панель инструментов
Информация, получаема по запросу из базы данных, далее может быть обработана средствами Excel. Например, при помощи сводных таблиц.
Задание 6.1. Создать рабочую книгу Excel с именем «Анализ заказов». На первом рабочем листе книги создать запрос к базе данных «БД заказов от ФИО». В запросе использовать таблицы БД: Заказы, Клиенты, Исполнители и Услуги. Необходимо выбрать следующий набор данных.
|
Номер заказа |
Фамилия клиента |
Наименование услуги |
Фамилия исполнителя |
Дата приема |
Дата исполнения |
Оплачен |
|
|
|
|
|
|
|
|
6.1. Создание сводной таблицы
Сводные таблицы создают с помощью мастера.
Прежде, чем создавать сводные таблицы, следует построить базы данных (таблицы в Excel) в рабочей книге.
Итак, пусть база данных имеет вид, представленный на рис.1.
Дважды щелкните по ярлыку рабочего листа Лист1. Переименуйте его, назвав именем Исходные данные. Используя форму, разместите на листе базу данных (рис.1).
Рис.1. Исходная табличная база данных
Запустите мастер сводных таблиц, воспользовавшись командами: Данные, Сводная таблица. В результате появляется первое окно Мастера сводных таблиц, в котором следует выбрать источник данных. Поскольку в рассматриваемом примере источник данных находится в рабочей таблице, выберем в поле «Создать таблицу на основе данных, находящихся …» переключатель «в списке или базе данных Microsoft Excel».
После выбора источника данных следует нажать кнопку Далее. В следующем диалоговом окне мастера (рис.2) в поле Диапазон нужно указать диапазон ячеек, где располагается таблица исходных данных. Внимание! Если курсор находится внутри диапазона источника данных, то диапазон будет указан автоматически. После завершения второго шага в алгоритме построения сводных таблиц следует нажать кнопку Далее.
Рис.2. Диалоговое окно мастера сводных таблиц
Шаг 3 заключается в определении места расположения сводной таблицы: на существующем листе или на новом и создании макета сводной таблицы. Выберите переключатель Новый лист и нажмите кнопку Макет. В диалоговом окне Макет (рис. 3) поля базы данных расположены в правой части макета в виде кнопок. Каждую из них следует разместить (перетащить с помощью «мыши») в одной их 4 областей макета сводной таблицы: область страниц, строк, столбцов, данных. При размещении кнопки поля в области страниц, строк, столбцов имена полей становятся заголовками строк, столбцов и страниц соответственно. При размещении кнопки поля в области данных мастер сводных таблиц использует формулу СУММ (SUM) по умолчанию, если значение поля является числом, и формулу СЧЕТ (COUNT), если поле содержит нечисловое значение. При определении макета сводной таблицы необязательно все поля должны быть размещены в указанных областях, в каждой области можно разместить одно или более полей. Если порядок размещения полей вам не понравился, вы можете вынести поле за пределы макета.
Рис.3. Макет сводной таблицы
Щелкните левой кнопкой манипулятора мышь по полю Наименование и удерживая кнопку мыши перетащите поле в область Страницы;
Щелкните левой кнопкой манипулятора мышь по полю Клиент и перетащите его в область Столбцы.
Щелкните левой кнопкой манипулятора мышь по полю Сотрудники и перетащите поле в область Строки.
Щелкните левой кнопкой манипулятора мышь по полю Количество и перетащите его в область Данные. В результате получится макет сводной таблицы, который представлен на рис.4. Нажмите кнопки ОК и Готово.
Рис.4. Мастер сводных таблиц после размещения на нем полей
В результате на новом листе разместится сводная таблица и будут открыты две новых панели инструментов Сводная таблица и Список полей сводной таблицы.
Щелкните дважды по ярлычку вновь появившегося рабочего листа. Переименуйте рабочий лист, выбрав имя Услуги БНТИ. Щелкните по раскрывающемуся списку значения поля страницы (все) и выберите Ксерокопирование. В результате получите сводную таблицу, представленную на рис.5. С помощью этой таблицы можно легко установить, каким клиентам и в каком объеме оказывались услуги по ксерокопированию, а также определить общий объем услуг по ксерокопированию.
Рис.5. Сводная таблица, содержащая услуги по ксерокопированию