II. Выполнение тренировочного задания

1. Повторить выполнение Задания 1 самостоятельно и сохранить результаты на гибком диске под именем Excel-10.xls.

2. Повторить выполнение Задания 1, но при условии, что строка меток столбцов не учитывается (нет).

3. Повторить выполнение Задания 1, но при условии, что область сортировки не содержит поле п/п и сортировка проводится дополнительно по ключу третьего уровня руб. в порядке убывания.

4. Выполнить сортировку данных в таблице по следующим ключам: Размер ссуды - по убыванию и Фамилии - по возрастанию. Сохранить результаты на гибком диске в каталоге Excel под именем Excel-11.xls.

Лабораторно-практическое занятие N7. Обработка данных: выборка записей из базы данных по заданным критериям с помощью функций базы данных.

Задание: научиться использовать программу Excel для работы с функциями базы данных, позволяющими осуществлять анализ данных в таблицах по задаваемым условиям.

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

Решение: рассмотрим возможности программы Excel на примере решения задачи.

Задание 1. Создать таблицу результатов анализа исходной базы данных (таблица 2) по форме таблицы 1. Таблица 1 и таблица 2 должны быть расположены на разных листах.

Таблица 1

	B	C	D	E	F	G	H
	#	Минимальный срок кредита, число	Максимальный срок кредита, число	Минимальный кредит тыс.	Максимальный кредит тыс.	Общая сумма кредита за данные сроки	Общая сумма взносов за данные сроки
	n/n	лет	лет	Руб.	Руб.	тыс. руб.	тыс. руб.

Таблица 2 (Расчет периодических взносов за полученную в банке ссуду (кредит) з агружается из файла TR14.xls.

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

Функции базы данных используют три аргумента: База данных, поле, критерий.

Формат функции - БДФ(База данных; поле; критерий),где БДФ - название функции (например, БДСУММ), База данных - интервал ячеек, формирующих базу данных (например,B10:K60), поле - имя поля, над значениями которого проводится операция с помощью данной функции (например, суммирование), причем имя может быть задано одним из следующих способов: 1/ текстом в двойных кавычках (например, "руб."), 2/ номером поля (например, 1 для поля п/п, 2 для поля Фамилия и т.д.) 3/ именем ячейки, где содержится имя поля (например, C10), критерий - интервал ячеек, который содержит условия, при соблюдении которых выполняется данная функция (например, E68:F69 - таблица 3).

Если критерию удовлетворяет более чем один результат действия функции, то сообщается значение ошибки #ЧИСЛО!

Таблица 3

B C D

	Срок	Срок
	Число	Число
	Лет	Лет
	<=15	>=0

Алгоритм выполнения задания

а) Открыть документ с базой данных, который находится в файле Y:\01. Информатика (общий курс)\04. Ивасюк Ю.Д\Задачи Excel \tr14.xls. База данных соответствует таблице 1.

б) Создать шаблон расчетной таблицы по форме таблицы 1 на новом листе (лист 1).

в) Создать таблицу критериев (таблица 4) на листе КРЕДИТ, которые используются в БДФ таблицы 1.

Таблица 4

F G H

	Срок	Срок	Срок
	Число	Число	число
	ИСТИНА	ЛОЖЬ	ЛОЖЬ

В соответствующие ячейки вводятся формулы:

Ячейки Формула Описание критериев формул таблицы 1

F69 И(F11>=Лист1!$C$7;F11<=Лист1!$D$7) Критерий формул 1 строки

G69 И(F11>=Лист1!$C$8;F11<=Лист1!$D$8) Критерий формул 2 строки

H69 И(F11>=Лист1!$C$9;F11<=Лист1!$D$9) Критерий формул 3 строки

г) Ввести БДФ в ячейки таблицы 1 на листе 1.

Ячейки Формула

E7 ДМИН(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!F$68:F$69)

F7 ДМАКС(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!F$68:F$69)

G7 БДСУММ(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!F$68:F$69)

H7 БДСУММ(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$H$10;КРЕДИТ!F$68:F$69)

E8 ДМИН(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!G$68:G$69)

F8 ДМАКС(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!G$68:G$69)

G8 БДСУММ(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!G$68:G$69)

H8 БДСУММ(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$H$10;КРЕДИТ!G$68:G$69)

E9 ДМИН(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!H$68:H$69)

F9 ДМАКС(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!H$68:H$69)

G9 БДСУММ(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$E$10;КРЕДИТ!H$68:H$69)

H9 БДСУММ(КРЕДИТ!$B$10:$K$60;КРЕДИТ!$H$10;КРЕДИТ!H$68:H$69)

Примечание: результаты выполнения алгоритма находятся в файле C:\PROBLEM\TRAINING\tr15.xls.

Задание 2. Повторить выполнение Задания 1 самостоятельно и сохранить результаты в каталоге под именем Excel-12.xls.