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.