Материал: Методические указания по выполнению лабораторных и самостоятельных работ. Морозов В.П., Свиридова Т.А

Задания для самостоятельной работы

1. Решите похожую задачу при измененном значении исходной цены.

2. Уменьшите в два раза начальный взнос и проанализируйте изменение значений выплат.

3. Поменяйте величину первого взноса и срока погашения и рассчитайте суммы выплат.

4. Придумайте самостоятельно пример на отвлеченную тему для применения ППЛАТ.

Лабораторная работа № 4

Тема: Логические функции в MS Excel

Время проведения: 4 часа

Программное обеспечение: OS Windows, MS Excel, MS Word

Рассмотрим наиболее часто используемые логические функции ЕСЛИ(), И(), ИЛИ().

Синтаксис функций:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

И(логическое_значение1; логическое_значение2;...)

ИЛИ(логическое_значение1;логическое_значение2; ...)

Задание 1. Применение логических функций для решения расчетной задачи.

В таблице приведен список деталей, изготовленных рабочим за смену, с указанием общего количества деталей, деталей с браком и себестоимости в рублях одной детали. Рассчитать сумму заработка рабочего за день, зная, что он получит 7% от итоговой суммы за вычетом штрафных удержаний. При расчете учесть, что рабочему начисляется штраф 5% от суммы по каждому виду изделия, если брак по нему составляет 10% и более.

П о р я д о к р а б о т ы :

Рис. 1. Исходные данные для задачи

1. Создать таблицу по образцу (рис. 1).

2. Подсчитать Сумму по каждому виду изделия (количество*себестоимость).

3. Подсчитать % брака путем деления Брака на Количество и умножения на 100.

4. Используя функцию ЕСЛИ, подсчитать размер штрафа. При этом в пункте «логическое выражение» должно быть сравнение процента брака с 10%. Например, запишем здесь F5>=10 (в ячейке F5 содержится процент брака по шайбам). Тогда в пункте «значение_если_истина» мы должны записать формулу, по которой рассчитывается размер штрафа (т.е. сумма*5/100), а в пункте «значение_если_ложь» напишем 0 (брак в пределах нормы, и штраф в этом случае не будет взыскиваться).

5. Подсчитать итог путем вычитания штрафа из суммы.

6. Подсчитать «К выдаче», просуммировав «Итого» и взяв от этой суммы 7%. Для проверки.

Теперь усложним задачу. Допустим, при тех же исходных данных, процент штрафа начисляется иначе. Пусть при проценте брака от 10% до 20% штраф будет по-прежнему 5%, а при проценте брака более 20% штраф будет в размере 12% от суммы. Рассчитать сумму к выдаче при новых условиях.

П о р я д о к р а б о т ы :

1. Скопировать основную расчетную таблицу на Лист 2 и затем на Лист 3. Удалить формулы из столбца Штраф.

2. Данную задачу можно решить двумя способами. На Листе 2. реализуем первый способ:

- вызовем функцию ЕСЛИ и в пункте «логическое_выражение» укажем F5<10. Теперь в пункте «значение_если_истина» мы должны указать 0 (штраф не берется, т.к. процент брака менее 10%). А в пункте «значение_если_ложь» необходимо снова вызвать функцию ЕСЛИ (или просто написать от руки ее название прописными буквами русского алфавита без пробелов).

- в новой вызванной функции также нужно заполнить три пункта. «Логическое_выражение» будет проверять на истинность условие, что процент брака более 20% (F5>20). Тогда «значение_если_истина» будет содержать формулу подсчета штрафа в размере 12% от суммы. «Значение_если_ложь» будет содержать формулу подсчета штрафа в

размере 5% от суммы.

- если все выполнено правильно, то к выдаче должно пересчитать автоматически:

3. Реализуем второй способ решения задачи с помощью функции И () на Листе 3:

- вызовем функцию ЕСЛИ и в пункте «логическое_выражение» укажем И(F5>=10;F5<20). Здесь будет проверяться на истинность условие, что процент брака составляет более 10% включительно, но менее 20%.

Теперь в пункте «значение_если_истина» мы должны указать формулу подсчета штрафа в размере 5% от суммы;

- в пункте «значение_если_ложь» необходимо снова вызвать функцию ЕСЛИ. В новой вызванной функции также нужно заполнить три пункта. «Логическое_выражение» будет проверять на истинность условие, что процент брака более 20% (F5>20). Тогда «значение_если_истина» будет содержать формулу подсчета штрафа в размере 12% от суммы.

«Значение_если_ложь» будет содержать в этом случае 0.

Задание 2. Построение графика функции

Рассмотрим пример построения графика функции при x [0;1] с шагом 0,1:

С начала строится таблица значений, а затем сам график (рис. 2).

З десь мы воспользуемся логической функцией ЕСЛИ. В ячейке B2 формула: =ЕСЛИ(A2<0,5; (1+ABS(0,2-A2))/(1+A2+A2^2); A2^(1/3)). Здесь используется функция ABS для задания модуля разности, она находится в категории «математические».

Р ис. 2. Построение графика функции с использованием логических функций

Задание 3. Построение поверхности. Построить поверхность

при x,y [-1;1], используя функцию ЕСЛИ(). Результат приведен на рис. 3.

Р ис. 3. Результат построения поверхности с использованием логических функций

Задание для самостоятельной работы

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

Т аблица 1

Лабораторная работа №5. Базы данных создание и основные приемы редактирования таблиц

1. Цель работы

Изучить принципы построения баз данных, освоить правила создания и редактирования таблиц в СУБД ACCESS.

2. Задачи работы

Ознакомиться со справочной системой MS Access. Создать и отредактировать многотабличную базу данных.

3. Содержание работы

3.1 Запустить MS Access.

3.2 Создать новую базу данных в файле с именем Student.

3.3 Создать структуру ключевой таблицы БД, определив ключевое поле и индексы; сохранить ее, задав имя Студенты.

3.4 Ввести в таблицу Студенты 20-25 записей и сохранить их.

3.5 Создать структуру неключевой таблицы БД и сохранить ее, задав имя Экзамены.

3.6 Установить связь с отношением один-ко-многим между таблицами Студенты и Экзамены с обеспечением целостности данных.

3.7 Заполнить таблицу Экзамены данными.

3.8 Проверить соблюдение целостности данных в обеих таблицах.

3.9 Методические рекомендации

Для запуска MS Access использовать Главное системное меню.

Вывести и просмотреть раздел справочной системы “Создание базы данных и работа в окне базы данных”.

Для создания новой БД выбрать команду Файл-Создать базу данных.

Для создания структуры ключевой таблицы Студенты рекомендуется использовать режим конструктора.

В бланке Свойства обязательно указать длину текстовых полей, формат числовых полей и дат. Поле Номер зачетки в таблице Студенты объявить ключевым и индексированным со значением Совпадения не допускаются.

Структура таблицы Студенты может быть следующей:

Вводить данные в таблицу Студенты рекомендуется в режиме таблицы. Для сохранения записей достаточно просто закрыть окно таблицы.

Структура таблицы Экзамены может быть следующей:

4.6 Создание новой базы данных с помощью Конструктора

После запуска Access нужно щелкнуть на кнопке Новая база данных в окне Microsoft Access и в предложенном диалоговом окне задать имя для файла БД. После этого на экране появляется окно базы данных (рис.1.1), из которого можно получить доступ ко всем ее объектам: таблицам, запросам, отчетам, формам, макросам, модулям.

Для создания новой таблицы нужно перейти на вкладку Таблица и нажать кнопку Создать. В следующем окне следует выбрать способ создания таблицы - Конструктор.

Рис.1.1 Окно базы данных (фрагмент)

После этого Access выводит окно Конструктора таблицы (рис.1.2), в котором задаются имена, типы и свойства полей для создаваемой таблицы.

Имя поля не должно превышать 68 символа и в нем нельзя использовать символы ! ..

Каждая строка в столбце Тип данных является полем со списком, элементами которого являются типы данных Access (таблица 1.1). Тип поля определяется характером вводимых в него данных.

Среди типов данных Access есть специальный тип - Счетчик. В поле этого типа Access автоматически нумерует строки таблицы в возрастающей последовательности. Редактировать значения такого поля нельзя.

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

Рис.2.1 Окно Конструктора таблицы

Размер поля - определяется только для текстовых и Memo-полей; указывает максимальное количество символов в данном поле. По умолчанию длина текстового поля составляет 50 символов

Формат поля – определяется для полей числового, денежного типа, полей типа Счетчик и Дата\Время. Выбирается один из форматов представления данных.

Число десятичных знаков - определяет количество разрядов в дробной части числа.

Маска ввода - определяет шаблон для ввода данных. Например, можно установить разделители при вводе телефонного номера

Подпись поля - содержит надпись, которая может быть выведена рядом с полем в форме или отчете (данная надпись может и не совпадать с именем поля, а также может содержать поясняющие сведения).

Значение по умолчанию - содержит значение, устанавливаемое по умолчанию в данном поле таблицы. Например, если в поле Город ввести значение по умолчанию Воронеж, то при вводе записей о проживающих в Воронеже, это поле можно пропускать, а соответствующее значение (Воронеж) будет введено автоматически. Это облегчает ввод значений, повторяющихся чаще других.

Условие на значение - определяет множество значений, которые пользователь может вводить в это поле при заполнении таблицы. Это свойство позволяет избежать ввода недопустимых в данном поле значений. Например, если стипендия студента не может превышать 250 р., то для этого поля можно задать условие на значение: <=250.

Сообщение об ошибке - определяет сообщение, которое появляется на экране в случае ввода недопустимого значения.

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

Пустые строки - установка, которая определяет, допускается ли ввод в данное поле пустых строк (“ “).