Разработка и типы запросов к базе данных в СУБД MS Access

Параметрические запросы

Запросы, представляющие собой варианты базового запроса и незначительно отличающиеся друг от друга, называются параметрическими. В параметрическом запросе указывается критерий, который может изменяться по заказу пользователя.

Последовательность  создания параметрического запроса:

• Создать запрос в режиме конструктора или открыть существующий запрос в режиме конструктора, например «Образец запроса в режиме Конструктор».
• В Бланк запроса в строке Условия отбора ввести условие отбора в виде приглашения в квадратных скобках, например [Введите фамилию]
• Закрыть окно Запрос на выборку, на вопрос о сохранении изменения ответить – Да. Вернуться в окно базы данных, где созданный запрос будет выделен.
• Выполнить запрос, щелкнув по кнопке: Открыть. В появившемся на экране окне диалога «Введите значение параметра» надо ввести, например фамилию студента, информацию об успеваемости которого необходимо получить, выполнить щелчок по кнопке ОК.

2. Практическая  часть

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

2.1.1. Цель решения задачи 

Цель решения  данной задачи состоит в расчете ежемесячного платежа по кредиту клиентом   банка Ивановым А.А. Ежемесячное погашение кредита осуществляется равными (аннуитетными) платежами. Этот вид расчета дает возможность банку получения более высоких доходов по процентам, а клиенту обеспечивает удобства при расчетах. Очень удобно и не хлопотно ежемесячно платить одну и ту же сумму в погашение кредита и процентов, которую легко запомнить и, кроме того, не нужно ежемесячно встречаться с консультантом банка для выяснения очередной суммы платежа

Задача, которая будет решаться в программной среде MS Excel ежемесячно, называется «Платежи по кредиту клиента Иванова А.А. банка «Акцепт+» за 2010 г.». .

2.1.2. Условие задачи 

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

В результате следует  получить документ со следующими реквизитами по месяцам: номер платежа, дата платежа, текущий остаток по кредиту, сумму процентов, погашение основного долга и платеж по основному долгу

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

В технологии необходимо использовать таблицу для организации ввода и контроля исходных данных, а также для организации процессов расчета функции ОКРУГЛ(), ЕСЛИ() и др.

 

2.2. Компьютерная  модель решения задачи 

2.2.1. Информационная модель  решения задачи 

Информационная  модель, отражающая взаимосвязь исходных и результирующих документов, приведена на рис. 9

 

Рис.9. Информационная графовая модель взаимосвязи исходных и результативных данных

p-годовая процентная ставка; n-количество месяцев, на которые выдан кредит; S-первоначальная сумма выданного кредита; J-процентная ставка в месяц; D-погашение основного долга; S _n-остаток по кредиту на период; К_n-платеж по кредиту на период; P _n-сумма процентов на период.

 

2.2.2. Аналитическая модель  решения задачи 

Для получения документа «Платежи по кредиту клиента Иванова А.А. банка «Акцепт+» за 2010 г» необходимо рассчитать следующие показатели:

• процентная ставка в месяц;
• сумму погашения основного долга по месяцам;
• общую сумму погашения основного долга за год;
• сумма текущего остатка по кредиту по месяцам.
• сумма процентов по месяцам;
• общую сумму процентов за год;
• платеж по кредиту по месяцам;
• платеж по кредиту за год;

 

Расчеты выполняются  по следующим формулам:

J=p/ n*100	D= S/ n	P n = J* S n	S n = S n -1- D	Кn= S n+ D
∑ D= D*12; ∑ Кn = ∑( P n + D); ∑ P n = ∑ (J * S n)

где

J	процентная  ставка в месяц
p	годовая процентная ставка
D	погашение основного  долга
S	первоначальная  сумма выданного кредита
∑ D	общая сумма  погашения основного долга за год
n	количество  месяцев, на которые выдан кредит
P n	сумма процентов  на период
S n	остаток по кредиту  на период
Кn	платеж по кредиту  на период
∑ Кn	выплаченного  кредита за год
∑ P n	общая сумма  процентов за год

2.2.3. Технология решения  задачи MS Excel

Решение задачи средствами MS Excel

1. Вызовите Excel:

• выполните команды Пуск → Программы MS→ Excel

2. Переименуйте «Лист 1» в ««Платежи по кредиту»:

• установите курсор мыши на ярлык  «Лист 1» (нижняя часть экрана) и нажмите правую кнопку мыши;

• выберите в контекстном меню команду «Переименовать» и нажмите левую кнопку мыши;

• наберите на клавиатуре ««Платежи по кредиту»;

• нажмите клавишу «Enter».

3. Введите заголовок таблицы «Платежи по кредиту клиента Иванова А.А. банка «Акцепт+» за 2010 г.»:

• сделайте ячейку A1 активной (установите курсор мыши на пересечение столбца A и строки 1 и нажмите левую кнопку мыши);

• наберите на клавиатуре «Платежи по кредиту»;

• нажмите кнопку в строке формул     (установите курсор мыши на эту кнопку и нажмите левую кнопку мыши).

4. Отформатируйте заголовок:

• выделите ячейки A1÷F1 (сделайте активной ячейку A1, затем нажмите левую кнопку мыши и, не отпуская ее, перемесите курсор на ячейку F1) (рис.10);

• на панели инструментов в закладке «Главная» выберите раздел «Выравнивание» и нажмите кнопку .

Рис. 10. Пример выделения группы ячеек

5. Введите строки  «Годовая процентная ставка», «Кредит выдан на», «Сумма кредита, руб.», соответственно в ячейки А2÷А4

• выделите действия аналогичные  п3 и п.4;

6. Введите в ячейки С2÷С4 информацию, соответствующую строкам «Годовая процентная ставка», «Кредит выдан на», «Сумма кредита, руб.» 

• выделите ячейки С2÷F2;

• выполните команду «Выравнивание» в разделе «Формат ячеек» меню «Главная» на панели инструментов;

• выберите закладку «Выравнивание»;

• в группе опций «Отображение» установите флажок опции «объединение ячеек» (рис.11);

• нажмите кнопку «OK».

• выделите ячейку С2 и потяните за правый нижний край до ячейки С4

• введите в ячейки С2÷С4 информацию, представленную на рис. 12.

Рис. 11. Задание объединение ячеек.

Рис. 12. Имена строк таблицы

7.Отформатируйте ячейки A6÷F6 под ввод длинных заголовков:

• выделите ячейки A6÷F6;

• выполните команду «Выравнивание» в разделе «Формат ячеек» меню «Главная» на панели инструментов;

• выберите закладку «Выравнивание»;

• в группе опций «Отображение» установите флажок опции «переносить по словам»;

6. Введите в ячейки A6÷F6 информацию, представленную на рис. 13.

Рис. 13. Имена полей таблицы «Справочник поставщика

8. Отформатируйте ячейки A3÷B20 для ввода текстовых символов:

• выделите ячейки A3÷B20;

• на панели инструментов в меню «Главная» выберите «Ячейки», где в пункте «Формат» выполните команду «Формат ячеек»;

• выберите закладку «Число»;

• выберите формат «Текстовый» (рис. 14);

9. Отформатируйте ячейки С3÷F20 для ввода текстовых символов:

• выделите ячейки A3÷B20;

• на панели инструментов в меню «Главная» выберите «Ячейки», где в пункте «Формат» выполните команду «Формат ячеек»;

• выберите закладку «Число»;

• выберите формат «Числовой»

 

 

 

 

 

 

 

Рис.14 Выбор формата ячеек

10. Введите информацию, приведенную на рис.15

Рис.15. Заполнение строк «номер платежа» и «Дата платежа».

11.Заполните столбцы «Текущий остаток по кредиту», « Сумма процентов», «Погашение основного долга», «Платежи по кредиту» для января 2010  года. (для этого рассчитаем,  процентную ставку. В ячейку А5 введем «процентная ставка», в ячейку С5 введите формулу =C2/12)

• сделайте активной ячейку Е7, введите формулу =ОКРУГЛ(C$4/12;0);

• введите в ячейку С7 число 250000;
• сделайте активной ячейку D7, введите формулу =ОКРУГЛ (C7*C5;0)
• сделайте активной ячейку F7 введите формулу =ОКРУГЛ (D7+E7$0).

12.Заполним столбцы «Текущий остаток по кредиту», «Сумма процентов», «Погашение основного долга», «Платежи по кредиту» для февраля 2010  года.

• сделайте активной ячейку С8 , введите формулу  
  =ОКРУГЛ (C7-E7;0);
• сделайте активной ячейку D8 , введите формулу  
  =ОКРУГЛ (C8*C$5;0);
• сделайте активной ячейку F8 , введите формулу  
  =ОКРУГЛ (D8+E8;0);

13. Заполните столбец «Погашение основного долга» (так как, ежемесячное погашение кредита осуществляется равными (аннуитетными) платежами,погашение основного долга в каждом месяце будет равной)

• выделите ячейку E7 и курсором за правый нижний край ячейки протяните значение до E7.

14.Заполните показатели по столбцам за остальные месяцы.

• выделите ячейки С8 ÷F8 и курсором за правый нижний край ячейки протяните значение до С18 ÷F18. Данные заполнятся автоматически (рис.16)

Рис. 16 Автоматическое заполнение столбцов.

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

• сделайте активной ячейку D19
• выполните команды Формулы →Библиотека функций → ∑ Автосумма.
• выделите ячейку D19 и протяните курсором за правый нижний край ячейки значение до F19. Данные заполнятся автоматически.

16. Для того чтобы итоговая сумма погашения основного долга равнялась сумме выданного кредита, используйте функцию ЕСЛИ() для отражения остатков по платежу в последнем платеже. Сумма последнего платежа по погашению основного долга будет больше, чем платежи за предыдущие месяцы. Для этого выполните следующие действия:

• сделайте активной ячейку Е19
• выполните команды Формулы →Библиотека функций→Логические →ЕСЛИ()
• введите информацию – E19<=250000 в поле «Лог_выражение»;
• введите информацию – ОКРУГЛ(20833.33;0) в поле «Значение_если_ложь»
• введите информацию – ОКРУГЛ(20833.33;0)+250000-E19
• в поле «Значение_если_истина» (рис. 17);
• нажмите кнопку «OK»;

Рис. 17. Вид второго окна мастера функций

19. Представьте  наглядно результаты расчетов, создав  гистограмму по данным таблицы:

• выделите диапазон A6÷F19;

• выберите команду  «Гистограмма» в разделе «Диаграммы» меню «Вставка»;

• в разделе «Коническая» выберите «Коническая с группировкой» (рис. 18).

Рис. 18. Созданная гистограмма

20. Отобразите в данной гистограмме только платежи по кредиту

• сделайте активной гистограмму (подведите курсор к гистограмме и щелкнете правой кнопкой мыши );
• выберите в открывающем окне «Выберите данные»;
• удалите из «элементов легенды (ряды)» все показатели, кроме «Платеж по кредиту» и нажмем «ОК» (рис .19)