close

Вход

Забыли?

вход по аккаунту

?

520. Информационные системы управления

код для вставкиСкачать
1088
ИНФОРМАЦИОННЫЕ СИСТЕМЫ УПРАВЛЕНИЯ
Методические указания по выполнению
лабораторных и самостоятельных работ
Специальность: 27.03.03 «Системный анализ и управление»
Воронеж 2015
Министерство образования и науки РФ
Федеральное государственное бюджетное образовательное
учреждение высшего профессионального образования
«Воронежский государственный
архитектурно - строительный университет»
Институт экономики, менеджмента и информационных технологий
Кафедра управления строительством
ИНФОРМАЦИОННЫЕ СИСТЕМЫ УПРАВЛЕНИЯ
Методические указания по выполнению
лабораторных и самостоятельных работ
Специальность: 27.03.03 «Системный анализ и управление»
Воронеж 2015
2
УДК 338.24.01
ББК 65с5
Составители
В.П. Морозов, Т.А. Свиридова
Рецензент:
В.Е. Белоусов, к.т.н., профессор, зав. кафедры автоматизации технологических
процессов и производств Воронежского ГАСУ
Информационные системы управления: метод. указания по выполнению
лабораторных и самостоятельных работ для студ. спец. 27.03.03 «Системный
анализ и управление» / Воронежский ГАСУ; сост.: В.П. Морозов, Т.А. Свиридова - Воронеж, 2015. – 56 с.
Методические указания предназначены для подготовки и выполнения
студентами, обучающимися по специальности 27.03.03 «Системный анализ и
управление»
предусмотренных учебным планом лабораторных и
самостоятельных работ по дисциплине «Информационные системы
управления».
Методические указания содержат описание заданий, порядок выполнения
работ и правила оформления их результатов.
Библиогр.: 17 источников
УДК 338.24.01
ББК 65с5
Печатается по решению учебно-методического совета
Воронежского ГАСУ
3
СОДЕРЖАНИЕ
Методические указания по выполнению лабораторных работ ……
Методические указания по выполнению самостоятельной работы
и изучению дисциплины……………………………………………...
Список рекомендуемой литературы....................................................
4
5
54
59
1. Методические указания по выполнению лабораторных работ
Лабораторная работа №1
Тема: ТАБЛИЧНЫЙ РЕДАКТОР MICROSOFT EXCEL
Время проведения: 4 часа
Программное обеспечение: OS Windows, MS Excel, MS Word
Цель работы: освоить технологию решения экономических задач.
Методика выполнения работы:
1. Предварительные сведения об электронных таблицах.
После активизации пакета на экране появляется основная таблица, столбцы которой
помечены латинскими буквами, строки числами. Каждая ячейка имеет относительный адрес
(типа А1) и абсолютный ($A$1). Ячейке можно присвоить имя, ввести в нее текст, число, дату или формулу.
При вводе текста можно выбрать шрифт, его размер и другие параметры, как это делается в WORD'е. Для размещения заголовков можно, выбрав пункты меню формат> ячейки>
выравнивание>, включить режим переноса по словам. Для корректировки значений можно
использовать пункты меню формат> ячейка> число. Первым символом формулы является
знак «=». Аргументами формулы являются константы и адреса, или имена, ячеек. Для ввода
арифметических операций используются знаки +, –, * (умножение), / (деление), ^ (возведение в степень). Например, в А1 введем =В1*В2+С1, нажмем на кнопку, помеченную зеленой
галочкой. Если значение ячейки В1=2, В2=3, С1=10, то результат вычисления по этой формуле запишется в А1. При записи формулы можно использовать также многочисленные математические, статистические, финансовые и другие функции. Для ввода функций нажимаем
на кнопку fx.
Задача 1. Создать таблицу из 10 строк, столбцы которой имеют имена «фио», «часы»,
«за час», «сумма». В первый столбец ввести фамилии, во второй и третий числа, а во вторую
строку четвертого столбца формулу.
2. Операции с объектами.
В качестве объектов используются ячейки, столбцы и строки, а также прямоугольные
блоки ячеек (таблицы данных) и пункты меню.
Для указания выполняемых операций используется клавиатура — управляющие клавиши Еnter (ввести), Еsc (отменить), клавиши модификации режима Shift, Ctrl, Alt, нажимаемые вместе с другими клавишами или одновременно с мышью, а также мышь и меню.
Любую ячейку можно активизировать, поместив в нее курсор мыши и нажав на левую
кнопку. Для активизации столбца или строки необходимо активизировать первую ячейку и,
нажав на Shift, переместить курсор мыши на последнюю из выделяемых ячеек. Для выделения блока аналогично поступаем с верхней левой и правой нижней ячейками.
Активизированные блоки (столбцы, строки) можно перемещать и копировать вместе с
их содержимым. Для перемещения зацепляем курсором мыши за соответствующую сторону
блока и, не отпуская нажатой левой кнопки мыши, перемещаем курсор. Для копирования одновременно нажимаем на Ctrl.
Задача 2. Переместить созданную таблицу, переместить отдельно столбец и строку.
Копировать таблицу на новое место. Перемещение объекта можно совершить также через
5
буфер, для этого следует активизировать его, нажать Ctrl + Insert (копирование) или Shift +
Del (перемещение), переместить курсор и нажать Shift + Insert, извлекая из буфера.
Задача 3. Переместить таблицу на новое место с помощью буфера. Копировать таблицу
на новое место с помощью буфера. Таблицу можно дополнять новыми столбцами и строками
(вставка> столбец или строка) или удалять их содержимое (выделить и нажать Del) или их
самих (правка> удалить).
Задача 4. Добавить новый столбец в таблицу с заголовком «сумма». Удалить столбцы и
строки дубликатов таблицы. Операцией, значительно расширяющей возможности Excel, является размножение содержания объекта. Для этого служит маркер заполнения, находящийся
в правом нижнем углу выделенного объекта. Для размножения содержания объекта совмещаем курсор мыши с маркером и, не отпуская нажатую кнопку, перемещаем курсор.
Задача 5. Ввести в ячейку А1 число 1 и размножить.
Задача 6. Ввести в В1 число 1, а в В2 — 1,2. Размножить содержимое этих двух ячеек
по столбцу.
Задача 7. Ввести в А1 слово «январь» и размножить по столбцу.
Задача 8. Ввести в С1 формулу =В1*В1 и размножить по столбцу. Другой важной возможностью является присвоение имен ячейкам.
Задача 9. Перейти на Лист 2, выбрав соответствующую метку внизу. Ввести в ячейку
А1 «Расчет краткосрочной ссуды», в А2 — «Ссуда», А3 — «Дата выдачи ссуды», А4 — «Дата возврата ссуды», А5 — «Процентная ставка», в А6 — «Сумма к возврату». В столбец D,
начиная со второй строки, ввести последовательно краткие обозначения переменных S, D0,
DV, p, SV. В столбец Е — значения переменных 200, 01.01.98, 01.05.99, 10%. Выделить
столбцы D и Е. Далее с помощью действий вставка> имя>создать присвоить имена ячейкам столбца Е, используя обозначения столбца D, которые теперь можно использовать в
формуле в ячейке Е6:
=S*(1+p)^((DV-D0)/365).
3. Создание и корректировка таблиц.
Для создания таблицы сделаем следующее:
— перейдите на Лист 3;
— увеличьте вдвое высоту строк 1 и 2
— в строку 1 введите «Отчет», а в столбцы строки 2 —
«Поставщик», «Товар», «Кол. по контракту»,«Факт»,
«Остаток»;
— Отцентрируйте заголовок «Отчет», активизировав
для этого в первой строке ячейки с А до G, и нажмите кнопку панели управления с буквой «а» в центре;
— выбрав формат>ячейки>шрифт, установите различные параметры шрифта;
— выбрав формат>ячейки>выравнивание, установите
флажок переносить по словам;
— заполните три строки таблицы данными;
— в первую ячейку столбца «остаток» введите формулу
для подсчета остатка и размножьте на весь столбец;
— внизу таблицы создайте новую строку и введите слово «Итого»;
— Активизируйте ячейку столбца «Остаток» и нажмите
кнопку a>Enter;
— выделите первую строку после закрепляемого заголовка и выберите окно>закрепить область.
Построение таблицы закончено.
6
Создавать и корректировать таблицы можно с помощью форм. Это особенно удобно
для таблиц большого размера. В Excel предусмотрена возможность автоматического создания форм, связанных с данной таблицей.
Форма — это способ представления на экране отдельных строк текста. Форма позволяет добавлять новые
строки в таблицу, удалять строки, просматривать таблицу построчно вниз или вверх, искать необходимые
строки по предлагаемым критериям поиска.
Для создания формы достаточно поместить курсор внутрь таблицы и набрать данные>форма. Для последовательного просмотра строк таблицы (записей) используем кнопки
«назад» и «далее». Для поиска нужных записей нажать кнопку «критерий», и после набора
критерия поиска — кнопки «назад» или «далее». Для добавления новых строк (в конец таблицы) нажать кнопку «добавить», для удаления текущей строки — «удалить»
Задача 10. Добавить не менее 9 строк в таблицу, используя соответствующую форму.
Удалить введенные ранее три строки. Испытать форму во всех возможных режимах. В процессе работы выяснить: как определить номер текущей записи, как стереть введенный ранее
критерий поиска?
4. Консолидация данных.
Пусть имеются несколько однотипных таблиц, которые необходимо слить в одну таблицу. Чаще всего эти таблицы содержат входную информацию. Например, имеются результаты опроса, проведенного сотрудниками и представленные в виде таблиц, содержащих данные о профессии, возрасте и доходах опрашиваемых. Необходимо объединить эти сведения в
общую таблицу, при этом количественные данные о каждой профессии, т.е. возраст и доход,
должны объединяться с использованием одной из имеющихся функций. Например, нас могут
интересовать сумма или среднее значение этих величин.
Для выполнения консолидации:
— создайте три таблицы, тип которых указан выше;
— Активизируйте ячейку, которая будет левым верхним
углом таблицы;
— выберите данные>консолидация;
— в списке функция укажите требуемую функцию;
— Активизируйте окно ссылка и наберите координаты
первой области в окне ссылка, нажмите кнопку добавить;
— повторите последний пункт для всех исходных таблиц;
— установите флажок значение левого столбца и нажмите ОК.
Задача 11. Пусть имеются наряды на выполненные работы, в каждом из которых указаны фамилии и отработанные часы. С помощью операции консолидации построить консолидированную таблицу, используя три различные функции.
5. Сводные таблицы.
В ряде случаев необходимо представить данные, помещенные в одной таблице, в серии
таблиц. Это называется представлением данных в различных разрезах. Например, таблицу
ПОСТАВКИ (поставщик, потребитель, товар, количество) необходимо представить в разрезе
поставщиков, т.е. в виде последовательности таблиц, в каждой из которых включены данные
только по одному поставщику.
Для построения таблиц необходимо:
— установить курсор в левом верхнем углу расположения
будущей сводной таблицы;
— вызвать «мастер сводных таблиц» выполнив данные>сводная таблица;
— нажать кнопку далее и указать диапазон ячеек, занятый
7
исходной таблицей;
— нажать кнопку далее и создать макет сводной таблицы;
— нажать кнопки далее и готово.
После построения сводных таблиц на экране появится панель инструментов для управления сводной таблицей. С помощью кнопки панели «мастер сводных таблиц» можно изменить структуру таблиц (используя крайнюю левую кнопку панели).
Если исходные данные изменяются, то можно обновить результирующие данные, активизируя данные, которые надо обновить, нажав на кнопку «!» на панели «мастера сводных
таблиц» или выбрав соответствующий пункт в меню, вызываемого нажатием правой кнопки
мыши.
Задача 12. Создать таблицу ПОСТАВКИ, создать сводные таблицы, представляющие
данные в различных разрезах. Изменить структуру таблицы, используя «мастер сводных таблиц». Изменить исходные данные и сводную таблицу.
Замечание. Чтобы создаваемая таблица была представительной, необходимо чтобы в
ней содержалось m 1 m2 m3 строк, где m 1 — число различных значений поля «поставщик», а m 2
и m 3 — аналогичные значения для полей «потребитель», «товар».
Задача 13. Создать таблицу регистрации температур воздуха, состоящую из полей: месяц, число месяца, город, температура. Получить сводную таблицу данных о температурах в
масштабе месяцев.
6. Сортировка данных.
Многие методы обработки данных и поиска работают эффективнее с отсортированн ыми данными. В Excel имеется простой способ сортировки строк таблицы. Чтобы им воспользоваться, достаточно указать столбец, который будет ключом сортировки, и выбрать пункты
меню данные>сортировка. Предусмотрена возможность задания одновременно до трех
ключей сортировки.
Задача 14. Отсортировать таблицу ПОСТАВКИ по столбцу «поставщик».
7. Группирование данных и создание итоговой строки.
В ряде случаев бывает необходимым разбить таблицу на группы, включив в каждую
группу все строки с одним и тем же значением одного из столбцов. Например, все данные о
банковских вкладах желательно разбить на группы, включив в каждую группу данные об
определенном виде вклада (депозит, облигация, срочный вклад и т. д.). В Excel имеется возможность указать столбец группировки и подсчитать итоговые данные для каждого значения
из этого столбца.
Переместите таблицу на новый лист, используя буфер. Отсортируйте таблицу по полю
группировки. Укажите это поле и пункты меню данные>итоги, вслед за тем выберите поле
подсчета и требуемую операцию (суммирования, осреднения и т. п.).
Задача 15. В таблице ПОСТАВКИ просуммировать «количество» по каждому значению поля «поставщик».
8. Фильтрация данных.
Фильтр может быть создан в любом столбце таблицы. Для его создания необходимо активизировать часть столбца для размещения списка выбора фильтра, причем размещать его
надо прямо поверх текста столбца. Затем переместить таблицу с данными на новый лист и
выбрать пункты меню данные>фильтр> автофильтр. Этим создается фильтр, который
можно использовать для поиска строк таблицы по запросам. Повторное выполнение указанных действий уничтожает фильтр.
Задача 16. Для таблицы ПОСТАВКИ в столбце «поставщик» создать автофильтр и реализовать поиск по всевозможным условиям. Обратите внимание на возможность использования символов «?» и «*».
9. Расширенный фильтр.
8
При расширенной фильтрации для отбора строк таблицы используется вспомогательная таблица (фильтр), где указываются некоторые из столбцов и строк исходной таблицы.
Например, можно создать таблицу:
Поставщик
Товар
ООО «Павлин»
мороженое
ЗАО «Энергия»
моторы
Для этого скопируйте часть исходной таблицы данных, изменив ее содержание, если
необходимо.
Установите курсор мыши в пределах исходной таблицы и выберите пункты меню данные> фильтр> расширенный фильтр. Введите ссылки на фильтр в открывшемся окне «расширенный фильтр» и нажмите кнопку ОК.
Задача 17. Создайте расширенный фильтр для своей таблицы и испытайте его для различных запросов и режимов.
Указанный выше фильтр наиболее простой из возможных. Прежде всего, в фильтре
каждое поле исходной (фильтруемой) таблицы можно представить дважды, а в строке под
ней могут быть использованы числовые значения поля со знаками неравенства (<, >, <=, <=).
Это дает возможность записать неравенство 150 < количество < 250 в виде таблицы:
Количество
Количество
>150
<250
С помощью таблицы
Количество
Количество
Товар
>150
<250
мороженое
можно выделить записи о поставках мороженого в количестве, удовлетворяющем неравенству 150 < количество < 250. Это условие можно записать также с помощью следующего логического выражения (товар=’мороженое’)И(150<количество<250).
Можно создать таблицу-фильтр, содержащую несколько строк. Каждой строке соответствуют элементарные
условия, объединяемые союзом «И», а условия соответствующие различным строкам объединяются союзом
«ИЛИ». Например, таблица-фильтр
Количество
Количество
Товар
>150
<250
мороженое
>200
вафли
определяет все записи, удовлетворяющие логическому условию ((150<количество<250) И
(товар=’мороженое’)) ИЛИ ((количество >200) И (товар=’вафли’)), т. е. те записи, которые относятся к сделкам по поставке мороженного в количестве от 150 до 250 единиц или
поставкам вафель в количестве превосходящем 200 единиц.
Задача 18. Создать фильтры, указанные выше, и профильтровать записи исходной таблицы ПОСТАВКИ.
10. Использование функций для работы с таблицами.
Для работы с таблицами (базами данных) в Excel предлагается несколько встроенных
функций, с помощью которых можно подсчитать сумму элементов заданного столбца (поля)
– БДСУММ, среднее значение – ДСРЗНАЧ, наибольшее и наименьшее значение – ДМАКС и
9
ДМИН и другие. Способ обращения ко всем перечисленным функциям унифицирован. Он
включает три аргумента: имя базы данных (таблицы), имя поля, по значениям которого происходят вычисления, и адрес таблицы-фильтра, задающей подмножество записей, по которым и происходит расчет. Например:
БДСУММ(база_данных, поле, критерий).
Присвоим таблице, содержащей данные о поставках, имя ПОСТАВКИ, ячейке, содержащей слово «колич ество», присвоим имя КОЛИЧЕСТВО, и создадим фильтр. Сформируем итоговую таблицу из двух столбцов.
В левый столбец поместим слова «сумма», «среднее», «максимум» и «минимум», а в правый — соответствующие функции:
БДСУММ(ПОСТАВКИ, КОЛИЧЕСТВО, адрес таблицы
фильтра),
ДСРЗНАЧ(ПОСТАВКИ, КОЛИЧЕСТВО, адрес таблицы
фильтра),
ДМАКС(ПОСТАВКИ, КОЛИЧЕСТВО, адрес таблицы
фильтра),
ДМИН(ПОСТАВКИ, КОЛИЧЕСТВО, адрес таблицы
фильтра).
Задача 19. Для данных ПОСТАВКИ создать таблицу подсчета итогов, используя функции для работы с базами данных (таблицами). Таблицу итогов сформировать в следующем
виде:
Товар
Товар
Товар
мороженое
вафли
хлеб
Сумма
Среднее
Максимум
Минимум
Первые две строки будут образовывать фильтры. Функции введите во второй столбец, начиная с третьей
строки. В остальные строки функции переместите с помощью символа продолжения.
ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬ НОЙ РАБОТЫ
1. На листе, содержащем таблицу базы данных, две первые строки займите критериями
поиска. В первой строке введите несколько раз имя любого текстового поля таблицы, а во
второй строке введите значения этого поля. Каждая пара: «имя поля — значение» будет образовывать критерий отбора записей. Назовите эти пары ячеек «критерий 1», «критерий 2» и т.
д. Ниже, в столбце А, введите заголовки: сумма, среднее, максимум, минимум. В столбце В
введите соответствующие функции работы с базой данных. Таблице присвойте имя «база
данных». Ячейке, содержащей имя числового поля, присвойте имя «чисполе1». В качестве
аргументов функций используйте переменные: база данных; чисполе1; критерий1. Также п оступите с другими критериями. В результате выполнения этих функций должен быть получен аналитический отчет о хранимых в таблице данных.
2. Для любой из таблиц базы данных создайте списки фильтров, испытайте их в разли чных режимах поиска. Используя три любых ключа сортировки, отсортируйте таблицы базы
данных.
10
Лабораторная работа № 2
Тема: решение задач оптимизации личного состава фирмы в процессе выполнения
определенного финансового проекта (стюардессы).
Время проведения: 2 часа
Программное обеспечение: OS Windows, MS Excel, MS Word
Постановка целей занятия: в какой-либо фирме, например авиакомпании, имеется
штатный состав сотрудников – стюардесс; по имеющимся данным на выбранный период
времени известна требуемая трудоемкость, превышающая возможности штатных сотрудн иков; необходимо найти оптимальное решение по набору дополнительного числа стюардесс.
Задача оптимизации решается по принятому алгоритму:
а) по текстовому описанию создается математическая модель задачи, в которой определены взаимосвязи параметров и границы их изменений;
б) по сформулированной в электронном виде модели переходят к решению с помощью
оптимизатора для выявления наилучшего варианта;
в) составленный по результатам оптимизации отчет позволяет проанализировать оптимальность решения.
Задание: на начальный период времени на работе в компании 60 стюардесс; зная, что в
соответствии с трудозатратами на ближайшие полгода этот штат недостаточен, найти минимальное число дополнительных работников и определить оптимальные сроки их принятия.
Методика выполнения работы:
1-й этап: создадим таблицу с условиями задачи в электронном виде (MS Excel), для чего:
- в колонку А вносим названия месяцев периода рассмотрения;
- в колонку В число новых стюардесс;
- в диапазоне ячеек С3:С8 вводим количество человеко-часов налета;
- в D12 и E12 вносятся затраты на обучение и работу имеющегося персонала;
- в F12 и G12 указываем допустимый месячный налет для обучаемой и
штатной сотрудницы;
Полученная в результате этих действий картинка приведена на рис.1.
2-й этап: в колонку D вводим формулу для расчета полного количества стюардесс в
данном месяце, для чего в D3 помещаем =B2, а в D4 вводим = D3 + B3 (протаскиваем последнюю для задания формул в ячейках D4 - D8) (см. рис.2).
3-й этап: в колонке ячеек E определяется оптимальный налет по месяцам, для чего в
первую из них (E3) вводится соответствующая формула (=D3*$G$12+B3*$F$12) и протаскивается до ячейки, соответствующей последнему месяцу – E8. Полученный результат представлен на рис.3.
4-й этап: для расчета затрат по месяцам в ячейки F3:F8 вводится формула
=D3*$E$12+B3*$D$12 с протаскиванием до ячейки F8 (она учитывает как оплату штатных
11
сотрудниц, так и возможные дополнительные расходы на принимаемых вновь). Результат
представлен на рис.4.
Рис.1.
12
Рис.2.
13
Рис.3.
Рис.4.
5-й этап: для расчета за планируемый период суммарных затрат необходимо ввести в
ячейку F9 формулу суммирования, для чего вызвать формулу “СУММ” и применить ее для
соответствующих ячеек (F3:F8) (см. рис. 5 ).
14
Рис.6.
Рис.7.
6-й этап: осуществляем поиск оптимального решения, для чего через “Сервис” вызываем “Поиск решения” и устанавливаем в появившемся окне целевую ячейку($F$9), выбираем
изменяемые ячейки ($B$3:$B$8) и фиксируем ограничения ($B$3:$B$8=целое;
$B$3:$B$8>=0; $E$3:$E$8>= $C$3:$C$8); после этого нажимаем “Выполнить” (полученные
результаты представлены на рис. 8, рис.9, рис.10).
Примечание: формат всех ячеек должен быть числовым; для целевой ячейки нужно в ыбрать минимальное значение.
15
Рис. 8.
Рис.9.
16
Рис.10.
Рис.11.
17
Рис.12.
Рис.13.
18
На 11 и 12 рисунках представлены результаты применения оптимизации численности
личного состава стюардесс. После выбора в окне “Результаты поиска решения” трансп аранта “Результаты” и нажатия “ОК” создается отчет, пример которого приведен на рис.13.
Анализ полученного отчета по заданию. Выводы.
Из полученного отчета следует, что в феврале необходимо принять дополнительно 1 сотрудницу, что позволит выполнить существенно большую трудоемкость работ при незн ачительном повышении затрат.
Контрольные вопросы
1. Каков должен быть формат ячеек от B3 до F9?
2.
3.
4.
5.
Каким образом задаются параметры в столбце C, D, E?
Как рассчитывается значение затрат в целевой ячейке?
Объясните последовательность действий при поиске оптимального решения.
Каким образом устанавливаются ограничения, изменяемые ячейки?
Задания для самостоятельной работы
1. Рассмотрите, как повлияет изменение количества сотрудниц в начальном штате на оптимизацию при сохранении распределения часов по месяцам.
2. Измените значения “затрат на стюардессу” и “разрешенный налет” и проанализи руйте результаты оптимизации.
3. Придумайте самостоятельно задачу на оптимизацию (минимизацию) штата сотрудн иков учреждения (в условиях расширения или сокращения).
4. Рассмотрите возможный набор неточностей в разобранной лабораторной работе, который не позволит правильно провести оптимизацию.
Лабораторная работа № 3
Тема: Финансовый и статистический анализ. Применение в MS Excel встроенных
функций.
Время проведения: 4 часа
Программное обеспечение: OS Windows, MS Excel, MS Word
Постановка целей занятия: для вычисления величины постоянной периодической выплаты ренты (например, регулярных платежей по займу) при неизменной величине процен тной ставки используется функция ППЛАТ; эта функция содержит пять основных аргументов (ставка; кпер; нз; бз; тип);
первый аргумент характеризует процентную ставку за период выплат;
второй – это общее число периодов выплат;
третий является текущим значением, равным общей сумме, которую составят в будущем платежи;
четвертый – баланс наличности, достижимый после последней выплаты (если этот аргумент не задан, он считается равным 0);
пятый – обозначает фазу периода, когда производится выплата (если “тип” равен 0 или
отсутствует, то оплата происходит в конце периода, если 1, то в начале периода).
В том случае, когда последние два аргумента являются нулями, функция ППЛАТ задается формулой:
19
n
i 1 i
P
n
1 i
1
,
где I , n, P – первые три из аргументов ППЛАТ.
Следует помнить, что при выборе единиц измерения первых двух аргументов нужно
быть последовательным, т.е. при выплатах, например, по четырехгодичному займу из расчета 12% годовых “ставка” задается как 12%/12, а аргумент “кпер” – 4*12. Если же платежи
являются ежегодными, то и обозначения изменятся на 12% и 4 соответственно.
Если необходимо найти общую сумму, выплачиваемую на протяжении интервала выплат, то возвращаемое функцией ППЛАТ значение следует умножить на величину “кпер”. И,
наконец, следует помнить, что в функциях, связанных с интервалами выплат, деньги, выплачиваемые в качестве депозита на накопление представляются отрицательным числом, а получаемые в качестве дивидендов – положительным.
Итак, в ходе данного лабораторного занятия нужно применить встроенную функцию
ППЛАТ (PMT) для вычисления 30-летней ипотечной ссуды со ставкой 8% годовых при
начальном взносе20% и ежемесячной (ежегодной) выплате.
Порядок выполнения задания:
1-й этап: создается в электронном виде (с помощью MS Excel) таблица исходной задачи;
для этого полностью заполняется колонка А, а также остальные ячейки, как показано на
рис.1.
Необходимо проверить, чтобы форматы В3, В4, В5 были соответственно “денежным” и
“процентным” (рис.2).
Рис.1.
20
Рис.2.
2-й этап: для вычисления значения размера ссуды в ячейку В7 вводится формула
=B4*(1-B5) (рис.3); после нажатия “ENTER” в ячейке получается искомое значение (рис.4).
Рис.3.
21
Рис.4.
3-й этап: выделяем ячейку В9 для расчета срока погашения ежемесячной ссуды, для чего вводим формулу: =D9*12. Нажимаем
и получаем 360 месяцев (рис.5). В В11 вводим
формулу ППЛАТ (рис.6 и рис.7).
Рис.5.
22
Рис.6.
Рис.7.
23
Рис.8.
Аналогично же поступаем и с ячейкой D11, вводя в нее функцию ППЛАТ (рис.8 и рис.9).
Рис.9.
24
Рис.10.
4-й этап: для нахождения общей суммы выплат (за весь интервал) выделяется ячейка
В12 и вводится формула =B9*B11. Аналогично поступают и для D12 (=D9*D11) (см. рис.10
и рис.11).
Рис.11.
25
Рис.12.
5-й этап: и, наконец, вычисляем общую сумму комиссионных, для чего выделяем ячейки B13 и D13, в которые помещаем формулы (=B12-$B$7) и (=D12-$B$7) соответственно
(рис.12 и рис.13).
Рис.13.
26
Анализ полученного отчета по заданию. Выводы.
Таким образом, с помощью одной из встроенных функций нами определены значения
размеров ссуды, периодических выплат, общих сумм выплат и комиссионных. Примен ение стандартных функций существенно упрощает процедуру вычислений, делает ее более
быстрой.
Контрольные вопросы
1. Какие стандартные функции Вам известны? Продемонстрируйте умение их вызвать.
2.
3.
4.
5.
6.
7.
Аргументы и их назначение при использовании функции ППЛАТ.
Что такое “интервал выплат”, “ставка”, “кпер”? их определения.
Каким образом и почему задаются расчеты в ячейках В7, В9, В11, В12, В13?
Каким образом и почему задаются расчеты в ячейках D11, D12, D13?
Каковы правила использования формул в Excel?
Какие возможны характерные ошибки при выполнение данной лабораторной работы?
8. Расскажите последовательность применения ППЛАТ в работе.
Задания для самостоятельной работы
1. Решите похожую задачу при измененном значении исходной цены.
2. Уменьшите в два раза начальный взнос и проанализируйте изменение значений выплат.
3. Поменяйте величину первого взноса и срока погашения и рассчитайте суммы выплат.
4. Придумайте самостоятельно пример на отвлеченную тему для применения ППЛАТ.
Лабораторная работа № 4
Тема: Логические функции в MS Excel
Время проведения: 4 часа
Программное обеспечение: OS Windows, MS Excel, MS Word
Рассмотрим наиболее часто используемые логические функции ЕСЛИ(), И(), ИЛИ().
Синтаксис функций:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
И(логическое_значение1; логическое_значение2;...)
ИЛИ(логическое_значение1;логическое_значение2; ...)
Задание 1. Применение логических функций для решения расчетной задачи.
27
В таблице приведен список деталей, изготовленных рабочим за смену, с указанием общего количества деталей, деталей с браком и себестоимости в рублях одной детали. Рассчитать сумму заработка рабочего за день, зная, что он получит 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. реализуем первый способ:
28
- вызовем функцию ЕСЛИ и в пункте «логическое_выражение» укажем 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
для задания модуля разности, она находится в категории «математические».
29
Рис. 2. Построение графика функции с использованием логических функций
Задание 3. Построение поверхности. Построить поверхность
при x,y [-1;1], используя функцию ЕСЛИ(). Результат приведен на рис. 3.
Рис. 3. Результат построения поверхности с использованием логических функций
Задание для самостоятельной работы
Используя логические функции и правила построения графиков функций и поверхностей, построить на отдельных листах следующие графики (формулировка и фрагмент ответа
приводятся в таблице 1).
Таблица 1
30
Лабораторная работа №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 использовать Главное системное меню.
Вывести и просмотреть раздел справочной системы “Создание базы данных и работа в окне базы данных”.
Для создания новой БД выбрать команду Файл-Создать базу данных.
Для создания структуры ключевой таблицы Студенты рекомендуется использовать режим конструктора.
В бланке Свойства обязательно указать длину текстовых полей, формат числовых
полей и дат. Поле Номер зачетки в таблице Студенты объявить ключевым и индексированным со значением Совпадения не допускаются.
Структура таблицы Студенты может быть следующей:
Имя поля
Тип поля
Номер зачетки
Числовой
Фамилия
Текстовый
Имя
Текстовый
Отчество
Текстовый
31
Факультет
Курс
Группа
Дата рождения
Стипендия
Текстовый
Числовой
Числовой
Дата\Время
Числовой
Вводить данные в таблицу Студенты рекомендуется в режиме таблицы. Для сохранения записей достаточно просто закрыть окно таблицы.
Структура таблицы Экзамены может быть следующей:
Имя поля
Номер зачетки
Предмет
Оценка
Дата сдачи
Тип поля
Мастер подстановок.
Текстовый
Числовой
Дата\Время
Обязательно определить нужные формат и длину полей в бланке Свойства.
Тип поля Номер зачетки определяется Мастером подстановок, используя для подстановки данные из таблицы Студенты. В качестве доступных при подстановке полей выбрать Фамилию и Имя.
Поле Номер зачетки в таблице Экзамены объявить индексированным со значением Совпадения допускаются.
При сохранении структуры неключевой таблицы Access может предупредить об
отсутствии ключевого поля и предложит создать это поле сейчас. В данном случае
следует отказаться от этого.
После определения структур обеих таблиц вызвать окно Схема данных и добавить
в схему данных обе таблицы (Студенты и Экзамены). Установить в окне схемы данных связь между таблицами по полю Номер зачетки (см.п.5.8) В окне Связи включить переключатель Определение целостности данных. После этого нужно указать
тип связи: Один-ко-многим и включить опции Каскадное обновление связанных
полей и Каскадное удаление связанных полей. После этого закрыть окно Связи.
В режиме таблицы ввести данные в таблицу Экзамены, используя созданный с
помощью Мастера подстановок список в поле Номер зачетки.
Для проверки соблюдения целостности данных при работе с таблицами нужно:
изменить значение ключевого поля (Номер зачетки) для одной из записей в таблице Студенты. Перейти в таблицу Экзамены и проверить, изменилось ли в ней значение общего поля для соответствующих записей;
удалить одну из записей в таблице Студенты. Перейти в таблицу Экзамены и
проверить, удалены ли в ней соответствующие записи.
После редактирования таблицы Студенты нужно сначала сохранить в ней изменения,
а затем переходить в неключевую таблицу.
4. Общие сведения
32
4.1. Основные определения
База данных (БД) - это поименованная совокупность данных, относящихся к
определенной предметной области.
Система управления базами данных - это комплекс программных и языковых
средств, необходимых для создания, обработки баз данных и поддержания их в актуальном состоянии .
Почти все современные СУБД основаны на реляционной модели данных. Все данные в реляционной БД представлены в виде таблиц. Каждая строка таблицы содержит
информацию только об одном объекте и называется записью. Столбец таблицы содержит однотипную для всех записей информацию и называется полем.
При определении структуры данных в базе данных выделяют следующие основные
понятия.
Класс объектов - совокупность объектов, обладающих одинаковым набором
свойств. Для каждого отдельного объекта из данного класса объектов в таблице с оздается отдельная запись.
Свойство - определенная часть информации о некотором объекте (фамилия, имя,
отчество - это свойства для объекта Студент).
Связь (отношение) - способ, которым связана информация о разных объектах.
4.2 Типы связей между объектами
Основным структурным компонентом базы данных является таблица. В каждой
таблице должны храниться данные только об одном классе объектов. Если в базе
данных должна содержаться информация о разных классах объектов, то она должна
быть разбита на отдельные таблицы. Связь между таблицами осуществляется с помощью общих полей.
Связи между любыми двумя таблицами относятся к одному из трех типов: один-кодному (1:1) , один-ко-многим (1:М) и много-ко-многим (М:М).
Связь типа “один-к-одному” (1:1)
При этом типе связи каждой записи в одной таблице соответствует не более одной
записи в другой таблице, т.е. когда часть информации об объекте либо редко используется, либо является конфиденциальной (такая информация хранится в отдельной
таблице, которая защищена от несанкционированного доступа).
Связь типа “один-ко-многим” (1:М)
При таком типе связи каждой записи в одной таблице соответствует несколько записей в связанной таблице. Для его реализации используются две таблицы. Одна из
них представляет сторону "один", другая - сторону "много". Например, нужно иметь
информацию о студентах и результатах сдачи ими экзаменов (дата сдачи, предмет,
оценка и т.д.).
Решением такой задачи является создание двух таблиц, например Студенты и Экзамены. В каждой из них хранятся соответствующие свойства. Для связи этих таблиц
нужно использовать только часть информации о студенте, сдающем экзамен. Но она
должна однозначно определять каждого студента среди всех. Такой информацией
может явиться, например, номер зачетки (он уникален для каждого студента).
В таблице со стороны "один" (Студенты) такие поля называются ключевыми.
Основное требование к значениям в ключевых полях - это их уникальность для каждой записи (т.е. они не должны повторяться).
33
Связь типа “много-ко-многим” (М:М)
При таком типе связи множеству записей в одной таблице соответствует множество записей в связанной таблице. Для реализации такая связь разбивается на две связи типа один-ко-многим . Соответственно, для хранения информации потребуется
уже три таблицы: две со стороны "много" и одна со стороны "один". Связь между
этими тремя таблицами также осуществляется по общим полям.
4.3 Структура MS Access
Первая версия MS Access была создана в 1993 г. фирмой Microsoft. MS Access - это
функционально полная реляционная СУБД, работающая в среде Windows. Access
позволяет создавать сложные базы данных, определяя структуру таблиц, связи между
ними. Access обладает совершенной системой создания запросов, отчетов и форм любой сложности. В Access, как любом приложении Windows, можно использовать все
возможности обмена данными между приложениями (DDE и OLE), что позволяет
включить в базу данных графическую и (или) звуковую информацию.
В Access база данных включает в себя все объекты, связанные с хранимыми данными (таблицы, формы, отчеты, запросы, макросы, модули). В таблицах хранятся
данные, которые можно просматривать, редактировать, добавлять. Используя формы,
можно выводить данные на экран в удобном виде, просматривать и изменять их. З апросы позволяют быстро выбирать необходимую информацию из таблиц. С помощью
отчетов можно создавать различные виды документов для вывода на печать. Макросы
и модули позволяют автоматизировать работу с базой данных.
4.4 Справочная система MS Access
В Access предусмотрено несколько способов быстрого доступа к справочной информации с помощью команд из меню Справка.
Выбор пункта Вызов справки выводит на экран диалоговое окно справочной системы, содержащее три вкладки: Содержание, Предметный указатель и Поиск.
Первые две вкладки снабжены краткими инструкциями, вкладка Поиск вызывает
вспомогательное диалоговое окно. В нижней части окна расположены управляющие
клавиши. Окно справочной системы всегда расположено поверх всех остальных окон.
Вкладка Содержание открывает страницу с перечнем папок, содержащих разделы
справочной системы, этой вкладкой удобно пользоваться при систематическом изучении разделов Access. При единичных обращениях к справке, удобнее пользоваться
вкладкой Предметный указатель. Вкладкой Поиск обычно пользуются в тех случаях, когда не удается разрешить вопрос первыми двумя способами.
4.5 Начало работы с MS Access
Запуск Access осуществляется двойным щелчком мыши по значку MS Access
на рабочем столе, в окне Microsoft Office.
После запуска на экране появится рабочий экран программы MS Access, в верхней
строке которого расположено Главное меню, а под ним панель инструментов. Она
содержит кнопки, дублирующие действия команд главного меню. С помощью ко34
манды Вид\Панели инструментов можно самостоятельно определить, которые из
них будут отображены на экране.
В средней части расположено начальное окно Microsoft Access, состоящее из двух
полей. Верхнее поле предлагает создать новую БД и содержит кнопки для выбора
способа ее создания: с помощью конструктора (Новая база данных) или с помощью
Мастера баз данных (Запуск мастера). Нижнее поле этого окна предлагает открыть
уже созданную ранее БД.
После нажатия кнопки Новая база данных Access запрашивает имя для файла, в
котором будет храниться база данных и открывает окно новой базы данных (рис.1).
Нажатие кнопки Запуск Мастера Access позволяет создать базу данных, используя
некоторые стандартные темы приложений из своей библиотеки. Эти приложения
включают в себя весь необходимый набор объектов: таблицы, запросы, формы, отчеты, макросы, модули. Из предложенных примеров можно выбрать подходящий и на
его основе создать свою БД. После этого пользователю остается только ввести в таблицы нужные данные.
Мастер баз данных не может учитывать все нюансы конкретного применения БД,
поэтому созданное таким образом приложение можно откорректировать, используя
режим Конструктора для нужного объекта БД.
4.6 Создание новой базы данных с помощью Конструктора
После запуска Access нужно щелкнуть на кнопке Новая база данных в окне
Microsoft Access и в предложенном диалоговом окне задать имя для файла БД. После
этого на экране появляется окно базы данных (рис.1.1), из которого можно получить
доступ ко всем ее объектам: таблицам, запросам, отчетам, формам, макросам, модулям.
Для создания новой таблицы нужно перейти на вкладку Таблица и нажать кнопку
Создать. В следующем окне следует выбрать способ создания таблицы - Конструктор.
Рис.1.1 Окно базы данных (фрагмент)
После этого Access выводит окно Конструктора таблицы (рис.1.2), в котором задаются имена, типы и свойства полей для создаваемой таблицы.
Имя поля не должно превышать 68 символа и в нем нельзя использовать символы !
..
Каждая строка в столбце Тип данных является полем со списком, элементами которого являются типы данных Access (таблица 1.1). Тип поля определяется характером вводимых в него данных.
35
Среди типов данных Access есть специальный тип - Счетчик. В поле этого типа
Access автоматически нумерует строки таблицы в возрастающей последовательности.
Редактировать значения такого поля нельзя.
Каждое поле обладает индивидуальными свойствами, по которым можно установить, как должны сохраняться, отображаться и обрабатываться данные. Набор
свойств поля зависит от выбранного типа данных. Для определения свойств поля используется бланк Свойства поля в нижней части окна конструктора таблиц.
Рис.2.1 Окно Конструктора таблицы
Размер поля - определяется только для текстовых и Memo-полей; указывает максимальное количество символов в данном поле. По умолчанию длина текстового поля составляет 50 символов
Формат поля – определяется для полей числового, денежного типа, полей типа
Счетчик и Дата\Время. Выбирается один из форматов представления данных.
Число десятичных знаков - определяет количество разрядов в дробной части числа.
Маска ввода - определяет шаблон для ввода данных. Например, можно установить
разделители при вводе телефонного номера
Подпись поля - содержит надпись, которая может быть выведена рядом с полем в
форме или отчете (данная надпись может и не совпадать с именем поля, а также может содержать поясняющие сведения).
Значение по умолчанию - содержит значение, устанавливаемое по умолчанию в
данном поле таблицы. Например, если в поле Город ввести значение по умолчанию
Воронеж, то при вводе записей о проживающих в Воронеже, это поле можно пропускать, а соответствующее значение (Воронеж) будет введено автоматически. Это
облегчает ввод значений, повторяющихся чаще других.
Условие на значение - определяет множество значений, которые пользователь может вводить в это поле при заполнении таблицы. Это свойство позволяет избежать
36
ввода недопустимых в данном поле значений. Например, если стипендия студента не
может превышать 250 р., то для этого поля можно задать условие на значение: <=250.
Сообщение об ошибке - определяет сообщение, которое появляется на экране в
случае ввода недопустимого значения.
Обязательное поле - установка, указывающая на то, что данное поле требует обязательного заполнения для каждой записи. Например, поле Домашний телефон может быть пустым для некоторых записей ( значение Нет в данном свойстве). А поле
Фамилия не может быть пустым ни для одной записи
(значение Да).
Пустые строки - установка, которая определяет, допускается ли ввод в данное поле пустых строк (“ “).
Индексированное поле - определяет простые индексы для ускорения поиска записей.
Для сохранения структуры таблицы нужно ввести команду Файл\Сохранить и в
окне Сохранение ввести имя таблицы.
Таблица 1.1 Типы данных MS Access.
Тип
данных
Текстовый
MЕМО
Числовой
Дата\Время
Денежный
Счетчик
Логический
Объект
OLE
Гиперссылка
Использование
Алфавитно-цифровые данные (до 255 символов)
Алфавитно-цифровые данные, абзацы, тексты ( до 64000 символов)
Различные числовые данные (имеет несколько форматов: целое, длинное
целое, с плавающей точкой)
Дата или время в одном из предлагаемых Access форматов
Денежные суммы, хранящиеся с 8 знаками в десятичной части. В целой
части каждые три разряда разделяются запятой.
Уникальный (неповторяющийся) номер создаваемый Access для каждой
новой записи
Логические данные, имеющие значения Истина или Ложь
Картинки, диаграммы и другие объекты OLE из приложений Windows
В полях этого типа хранятся гиперссылки, которые представляют собой
путь к файлу на жестком диске, либо адрес в сетях Internet или Intranet.
* Кроме вышеперечисленных типов данных в списке есть элемент Мастер подстановок, который позволяет представить значения полей в виде простого
или комбинированного списка. Дополнительные свойства такого поля представлены на вкладке Подстановка окна конструктора таблиц.
4.7 Создание таблиц с помощью Мастера таблиц
Создавая таблицу с помощью Мастера таблиц нужно в окне базы данных перейти
на вкладку Таблицы, нажать кнопку Создать и выбрать способ создания Мастер
таблиц. Так же, как и Мастер баз данных, Мастер таблиц предлагает множество готовых структур таблиц по разным тематикам. Пользователь может выбрать подходящую таблицу и ее поля. При необходимости созданную таким образом таблицу мо жно откорректировать, открыв ее в режиме конструктора.
37
В отличие от Мастера баз данных , Мастер таблиц создает лишь одну таблицу, которую нужно дополнить множеством других необходимых объектов для получения
законченного приложения.
4.8 Определение ключевых полей
При создании многотабличной базы данных важно правильно определить связи
между таблицами и задать ключи.
Создавая таблицы с помощью Конструктора, Access может автоматически создать
первичный ключ, поставив перед первым указанным в структуре полем поле Код и
задав для него тип Счетчик. Для этого при сохранении таблицы в запросе на автоматическое определение ключа нужно ответить Да.
В большинстве случаев ключи должен определять сам разработчик БД. Для того,
чтобы определить какое-либо поле таблицы ключевым, нужно установить курсор в
строке с именем этого поля и нажать на кнопку Определить ключ
на панели инструментов или ввести команду Правка\Ключевое поле. После этого в строке рядом
с именем этого поля появится изображение ключа.
4.9 Определение связи таблиц
При реализации отношений один-ко-многим и много-ко-многим данные разбиваются на несколько таблиц, определяются ключевые поля, обеспечивающие связь таблиц. После определения структуры таблиц БД нужно указать Access способ, которым
эти таблицы связаны.
Для определения связей нужно перейти в окно базы данных и выполнить команду
Сервис\Схема данных или на панели инструментов нажать кнопку Схема данных
. После этого Access откроет окно Схема данных и окно Добавление таблицы.
В окне Добавление таблицы нужно выделить имена таблиц, добавляемых в схему
данных, и нажать на кнопку Добавить. После этого данное окно можно закрыть.
В окне Схема данных появятся имена всех указанных таблиц вместе со списками
полей. Имена общих полей выделены полужирным шрифтом. Нужно выделить ключевое поле ключевой таблицы, нажать кнопку мыши, перетащить ее курсор на аналогичное поле в связываемой таблице, после чего кнопку мыши отпустить. В результате
появится диалоговое окно Связи (рис.1.3).
В этом окне Access заполнил первую строку именем поля, по которому связывались таблицы. Чтобы в связанных таблицах не нарушалась целостность данных, нужно щелкнуть по флажку Обеспечение целостности данных. После этого Access сделает невозможным запись в неключевую таблицу такого значения общего поля, которого нет в таблице ключевой.
После установления целостности данных Access включает две дополнительные опции: Каскадное обновление связанных полей и Каскадное удаление связанных
полей.
Если выбрать первую опцию, то при изменении какого-либо значения ключевого
поля в ключевой таблице Access автоматически обновит значения этого поля для соответствующих записей во всех связанных таблицах. Например, если у одного из студентов изменился номер зачетки в таблице Студенты, то он автоматически должен
поменяться и в таблице Экзамены.
38
Выбор второй опции при удалении одной из записей в ключевой таблице приведет
к удалению тех записей в таблице со стороны "много", которые имеют такое же значение ключа. Например, если из таблицы Студенты удалить запись об одном из студентов, то записи о результатах сданных им экзаменов будут удалены автоматически.
Для завершения процесса создания связей, нужно щелкнуть по кнопке Создать.
Access нарисует линию между таблицами в окне Схема данных, указывающую на
наличие связи между ними. На конце линии у таблицы со стороны "один" будет нарисована цифра 1, а на другом конце, у таблицы со стороны "много" - символ бесконечности
. После закрытия этого окна все установленные связи будут сохранены.
Рис.1.3 Диалоговое окно Связи
4.10 Ввод, редактирование и просмотр данных в режиме таблицы.
Для осуществления этих действий нужно открыть базу данных в режиме таблицы:
перейти в окно базы данных на вкладку Таблицы
выделить имя нужной таблицы и щелкнуть по кнопке Открыть.
Используя горизонтальную и вертикальную полосы прокрутки, можно перемещаться на новые поля или записи таблицы.
В режиме таблицы можно не только просматривать данные, но и редактировать их.
Для этого используются обычные приемы редактирования и клавиши перемещения
между записями.
Если нужно удалить запись целиком, то нужно выделить ее и нажать клавишу Delete.
Для вставки новой записи в середине таблицы нужно выделить строку, перед которой должна расположиться новая запись, и нажать клавишу Insert. На появившейся
пустой строке нужно ввести данные.
Для выделения полной записи нужно щелкнуть мышью на области маркировки
данной записи. Для выделения значения текущего поля используется двойной щелчок мыши на нем.
После окончания ввода или редактирования данных таблицы нужно выполнить команду Файл\Сохранить записи.
39
Для облегчения ввода и редактирования данных Access дает несколько "горячих"
клавиш, назначение которых можно посмотреть в разделе Работа с данными справочной системы Access 97.
4.11 Использование Мастера подстановок при вводе данных в таблицы
Мастер подстановок позволяет формировать для нужного поля список значений,
который может содержать данные другой таблицы или запроса, либо состоять из
фиксированного набора значений. В обоих случаях Мастер подстановок облегчает
ввод данных и обеспечивает их достоверность.
Например, в базе данных Студенты-Экзамены поле Номер зачетки является общим для обеих таблиц. При заполнении этого поля в неключевой таблице Экзамены
важно, чтобы его значения для каждого студента точно соответствовали значениям в
ключевой таблице. Для этого значения поля Номер зачетки в таблице Экзамены
лучше всего представить в виде списка. Это избавит от необходимости печатать данные при заполнении этой таблицы, предотвратит появление ошибок при наборе и
сделает ввод данных более наглядным (рис.1.4).
Комбинированный список для поля Номер зачетки формируется на основе данных связанной таблицы Студенты. Для создания комбинированного списка нужно
войти в режим конструктора таблицы Экзамены, перейти в колонку Тип данных для
поля Номер зачетки. Из списка доступных типов полей выбрать элемент Мастер
подстановок.
Первое диалоговое окно Мастера подстановок предлагает выбрать источник формирования списка: на основе данных таблицы\запроса или фиксированный набор значений. В данном случае нужно выбрать первый вариант.
В следующем окне из приведенного списка таблиц\запросов следует выбрать таблицу\запрос, являющуюся источником данных для списка. В нашем примере такой
таблицей является таблица Студенты, т.к. она послужит источником данных для
списка номеров зачеток.
В третьем окне Мастера подстановок из списка Доступные поля нужно выбрать
поля, значения которых фигурируют в списке. В данном случае можно выбрать поля
Фамилия и Имя, которые сделают список более информативным. После этого можно
нажать кнопку Готово для завершения процесса проектирования комбинированного
списка. Сохранив проект таблицы, можно открыть таблицу Экзамены и просмотреть
результат работы.
Рис.1.4 Использование комбинированного списка
40
ЛАБОРАТОРНАЯ РАБОТА № 6
АВТОМАТИЗАЦИЯ РАСЧЕТОВ ПО РАЗОВЫМ ПЛАТЕЖАМ И АНАЛИЗ ПОТОКОВ ПЛАТЕЖЕЙ
1. Автоматизированное вычисление по формулам.
Пусть необходимо производить вычисления по формуле, в которую входят переменные, имеющие определенное смысловое значение. Например, известен начальный размер вклада S 0, процентная ставка p относительно периода начисления, время
хранения вклада k, заданное в периодах начисления, и при расчетах используются
простые проценты. Необходимо определить размер вклада S k в момент k. Для расчета
используем формулу
Sk= S0 (1+pk)
Для реализации расчетов на компьютере введем в первый столбец полные имена
четырех переменных и выберем пункты меню формат>столбец>автоподбор. Во
второй столбец сокращенные обозначения переменных. В третий столбец введем заданные значения трех переменных и присвоим им в качестве имен сокращенные обозначения, выбрав для каждой ячейки в меню пункты вставка >имя> присвоить. В
четвертую ячейку третьего столбца введем формулу для расчета, указанную выше.
Задача 1. Выполнить указанные действия на компьютере, если p=0,1; k=5;
S0=100.
2. Расчеты по простым процентам.
В рассмотренном выше примере ситуация была упрощена. Чтобы сделать расчеты, имеющими практическое значение, обозначим р — годовая ставка процента, m —
число периодов начисления за один год. Тогда p/m — годовая ставка, отнесенная к
периоду начисления. Заметим, что обычно величина дается в процентах, а в формулах
чаще используется в долях, поэтому перед подстановкой в формулу p делим на сто.
Например, если годовая ставка составляет 6%, то в формулу подставляется 0,06.
Если отсчет времени начисления начинается с момента внесения вклада и время
хранения вклада равно k периодам начисления, то:
Sk
S 0 (1
pk
),
m
(1)
Если момент внесения вклада совпадает с началом периода начисления, то сумму, начисленную за t единицу времени хранения начисляем по формуле:
Sk
p
S 0 (1 ( )
m
t
),
где
скобки
[
]
означают
взятие
Δ— длительность периода начисления.
(2)
целой
части
числа,
Используемые в формуле длительности необходимо задавать в одинаковых единицах времени, учитывая, что обычно в финансовых операциях полагается, что один
года равен 360 дням, один месяц равен 30 дням.
41
Например, если вклад пролежал на депозите 2 года 16 дней, проценты начисляются ежемесячно из расчета 6% годовых, в этом случае t = 736, Δ= 30, m = 12. Следовательно, каждый рубль вклада превратится в (1+(0.06/12)[736/30]) = 1+(0.06/12)24 =
1.12 руб.
Если время хранения, следующее за последним начислением процентов, также
учитывается при начислении, то формула (2) упрощается:
где mΔ= 1 год.
Если t исчисляется в днях, то
St = S0 (1+pt/mΔ),
(3)
St = S0 (1+pt/360),
(4)
Если исчисление ведется в годах, то
St = S0(1+pt),
(5)
Отметим также, что стоимость вклада в формулах (3)—(5) не зависит от длительности периода начисления.
Задача 2. Создать таблицу анализа эффективности депозита при различных процентных ставках. Для этого столбцы озаглавить: процент, 1 год, 2 года, 5 лет, 10 лет.
Для процентов выбрать значения от 1 до 51 с шагом 5. Использовать формулу (5), полагая S0 = 1.
Задача 3. Пусть ссуда в размере 10 000 руб. дана на два года из расчета 22 % годовых. Какую сумму получит заимодавец в конце указанного периода?
Обычно при проведении практических расчетов задают дату внесения вклада d 0
и текущую дату dt в формате чч.мм.гг. Для расчета будем использовать формулу (4),
которую преобразуем к следующему виду:
St = S0(1+p(dt –d0)/360),
(6)
где
dt –d0 = день360(нач_дата; кон_дата),
(7)
Задача 4. Создать следующую таблицу:
Расчет депозита
Сумма вклада
S0
число_1
Дата вклада
d0
дата _0
Текущая дата
dt
дата _1
Время хранения
t
формула (7)
Процентная ставка p
число _2
Сумма начисления St
формула (6)
Введите значения для дат и формулу (7) и проверьте ее работоспособность при
различных значениях d0 и dt . Далее, используя формулу (6), подсчитайте текущую величину вклада St при различных значениях остальных параметров.
В формуле (6) t = dt – d0, поэтому если задать время t, величину начального вклада
S0. и величину вклада St в момент t, то можно подсчитать процент р. Для этого
воспользуемся формулой (5), и получим формулу (8):
p
(
St
S0
1) / t ,
(8)
Например, пусть нам выдана ссуда S0 на определенный срок t с условием возврата St . Какая процентная ставка обеспечивает выполнение контракта?
42
Для использования этой формулы необходимо изменить таблицу: вместо формулы (7)
записать число дней, а число_2, заменить формулой (8).
Задача 5. Сравнить условия контракта задачи 3 с другим контрактом, по которому через два года возвращается ссуда в 15000 руб.
Используя формулу (5), можно получить современную стоимость денег:
S0
St
,
1 pt
(9)
Эта формула позволяет сравнить контракты с различным сроком действия.
Задача 6. Вычислив современную стоимость денег, сравнить два контракта:
· уплатить 10 тыс. руб. через 5 лет и 5 тыс. руб. через 15 лет;
· уплатить 6 тыс. руб. через 3 года и 8 тыс. руб. через 7 лет.
На деньги начисляется 8% годовых процентов.
3. Амортизационные отчисления.
Под амортизацией подразумевается уменьшение стоимости имущества в процессе эксплуатации.
Если время эксплуатации равно n лет и остаточная стоимость имущества составляет Sn, а начальная стоимость — S0 , то его годовая амортизация при равномерном
списании составит
A
S0
(S 0
Sn )
n
,
(10)
Для вычисления величины А амортизации за год при равномерном списании в
Excel используется функция АМР(S0, Sn, n).
Предположим, что стоимость компьютера 25000 руб. После 5 лет эксплуатации он
оценивается в 5000 руб. Снижение стоимости компьютера для каждого года эксплуатации начисляется по формуле =АМР(25000; 5000; 5).
Функция АМГД (S0 , Sn , n; k) возвращает амортизацию для периода k при ускоренном списании. Пользуясь этой функцией, можно рассчитать амортизационные отчисления за любой год эксплуатации. Например, за четвертый год эксплуатации компьютера упомянутого выше, его стоимость уменьшится на АМГД (25000; 5000; 5; 4).
Задача 7. Рассчитать амортизацию за каждый год эксплуатации автомобиля, если начальная стоимость его равна 250000 руб., срок эксплуатации 10 лет, остаточная
стоимость 40000 руб. при равномерном и неравномерном списании.
Задача 8. Используя функцию АМГД() выяснить закон, по которому она рассчитывает амортизацию. Подготовить для этого простые исходные данные и определить,
какую часть от первоначальной стоимости составляют списания за 1-й, 2-й и т. д. периоды.
Построить график зависимости размеров амортизационных выплат от номера
периода, вызвав для этого мастер диаграмм (пункт меню вставка>диаграмма).
4. Расчет стоимости продукта при изменении стоимости компонента.
Пусть стоимость одного из компонентов (сырья или услуги), стоимость которого
входит в стоимость продукта, возрастет на р%. Например, возрастет стоимость бензина, которая косвенно через стоимость услуги по перевозке входит в стоимость продукта.
Предположим, что стоимость компонента (бензина) составляет q% от стоимости продукта. Новая стоимость продукта может быть найдена по формуле А(1+0.0001pq).
43
Задача 9. На сколько увеличится цена продукта, если стоимость бензина увеличится в 2 раза, а его доля в стоимости продукта составляет 20%.
5. Сложные проценты.
Если при каждом начислении в качестве базы используется не начальная сумма,
как при расчете по простым процентам, а накопленная к моменту начисления, то полученная формула называется формулой сложных процентов.
Если отсчет времени начисления начинается с момента внесения вклада и время
хранения вклад равно k периодам начисления, то
Sk=S0(1+p/m)k ,
(11)
Если момент внесения вклада совпадает с началом периода начисления, то сумму,
начисленную за t единиц времени хранения, вычисляем по формуле
St S 0 (1 ( p / m)) t /
,
(12)
где [ ] означает взятие целой части числа,
a Δ — длительность периода начисления.
Используемые в формуле длительности необходимо задавать в одинаковых единицах времени, учитывая, что обычно в финансовых операциях полагается, что один
года равен 360 дням, один месяц равен 30 дням.
Например, если вклад пролежал на депозите 2 года 16 дней, проценты начисляются
ежемесячно из расчета 6% годовых, то t= 736, D=30, m=12. Следовательно, каждый
рубль вклада превратится в (1+(0.06/12)[736/30])= 1+(0.06/12)24= 1.12 руб.
Если время хранения, следующее за последним начислением процентов, также учитывается при начислении, то формула (12) упрощается:
S 0 (1 p / m) t / m S 0 (1 ( p ) /(m )) t / ,
St
(13)
Если t исчисляется в днях, то
St
S0 (1 ( p ) / 360)
t/
S0e
t/
ln(1
p
)
360
,
(14)
Если исчисление ведется в годах, то
S t S0 (1 p )t /
S 0 et /
ln(1 p )
,
(15)
Предположим, что начисления происходят настолько часто, что Δ — очень мало.
Тогда: ln(1+p Δ )=p Δ +о(Δ), получим формулу непрерывных процентов:
St = S0 е pt ,
(16)
Задача 10. Используя формулы (14), (15) и (16), получить таблицу значений отношения S t /S0 при процентных ставках р = 0,01; 0,02; ...; 0,1; 0,2; ...; 0,5 (по столбцу) и
при различных периодах начисления: ежемесячных, еженедельных, ежедневных и
непрерывных (по строке), t = 1 год.
6. Применение сложных процентов в экономике.
Если налоги на продаваемую продукцию взимаются в виде процента от ее текущей стоимости, то процесс роста стоимости продаваемой продукции выражается
сложными процентами. Обозначим стоимость произведенной продукции S0, процент
налога — p. Тогда стоимость продукта после n перепродаж составит
44
Sn= (1+p)n S0 ,
(17)
Задача 11. Пусть p = 10, 12, 15, 18, 20%. Во сколько раз возрастет стоимость
продукции после 2, 5, 7 перепродаж. Результат оформить в виде таблицы.
7. Расчет реальной годовой ставки.
Пусть начисления по вкладу производятся m раз в году из расчета годового процента p%, при использовании начислений сложных процентов. Тогда к концу года
вклад S0, сделанный в его начале, станет равным S1 = S0 (1+p/m)m, т. е. относительный
его прирост составит:
q( p ,m )
S1
S0
S0
(1 p / m) m 1 ,
(18)
образуя реальную годовую процентную ставку.
Задача 12. Рассчитать q(p, m) для различных значений p и m. Сравнить полученные значения p и q.
8. Расчет параметров потока платежей с использованием финансовых
функций Excel.
Последовательность нескольких следующих друг за другом платежей называется потоком денежных платежей. Если платежи производятся через равные промежутки времени, то поток называется финансовой рентой. Именно этот случай будем рассматривать далее.
Пусть c одинаковыми промежутками делается n платежей в банк, каждый из которых равен А руб., и в конце каждого промежутка на всю накопленную ранее сумму
делаются начисления процентов по ставке q% на промежуток.
Тогда сумма вклада
Sn = A((1+q)n – 1)/q.
Задача 13. Составить таблицу значений функции s(n, q) = ((1+q) n –1)/q при значениях n = 1, 2, ..., 10; q = 1, 5, 10, 20, 30%.
В Excel имеется функция БЗ, которая возвращает величину вклада Sn на основе
периодических постоянных платежей А, начального взноса S0 и ставки процента на
период q.
Sn= БЗ(q; n; –A; –S0),
(19)
Если дана годовая процентная ставка p, то q = p/m, где m — число платежей в
году.
Задача 14. Для различных процентных ставок q = 1, 2, 5, 10, 20, 30% вычислить
изменение накопленной суммы в зависимости от числа взносов n при А = 1, S0 = 0.
Функция КПЕР возвращает число платежей n для данной суммы Sn при заданной процентной ставке q и величине периодического платежа А:
n = КПЕР(q; –A; Sn),
(20)
Например, если вы берете в долг 100 руб. при годовой ставке 1% и собираетесь
выплачивать по 100 руб. в год, то число выплат можно подсчитать, используя формулу
n = КПЕР(1%; –А; 1000),
(21)
Задача 15. Для покупки квартиры необходима ссуда 900000 руб., которая может
быть получена под р% годовых. Сколько времени потребуется для выплаты ссуды
при р% = 5, 10, 15 и ежегодных взносах 150000, 200000, 300000 руб.? Как изменятся
сроки выплат, если выплаты будут ежемесячными?
45
Функция НОРМА вычисляет процентную ставку за один период, необходимую
для накопления необходимой суммы путем постоянных взносов
q = НОРМА(n; –A; Sn),
(22)
Пусть требуется определить процентную ставку для 4-летнего займа размером в
8000 руб. с ежемесячной выплатой 200 руб.
Используя указанную функцию q = НОРМА(48; –200; 8000), получим 0,77%.
Задача 16. Определить годовую процентную ставку для 4-летнего займа размером 8000 руб. С ежегодной выплатой 2400 руб.
Лабораторная работа №7
Тема: Выполнение типовых экономических расчетов. Задача о командировках в
MS Excel
Программное обеспечение: OS Windows, MS Excel
Постановка задачи. Определить оплату командировочных расходов группе работников,
посетивших научные семинары в городах Москве, С-Петербурге и Новосибирске.
Порядокработы:
1. Оформить рабочий лист в соответствии с приведенным образцом (рис. 1).
ОПЛАТА КОМАНДИРОВОЧНЫХ РАСХОДОВ
Рис. 1. Исходные данные для задачи о командировках
2. Выполните расчет оплаты проезда в столбце «Оплата проезда», используя функцию
ЕСЛИ и учитывая, что проезд не оплачивается в случае отсутствия документов.
3. Выполните расчет проживания в сутки, учитывая, что при наличии документов за
проживание расчет производится по предоставленным документам, но не более 270 рублей в
сутки. При отсутствии документов начисляется 7 рублей за сутки. Используйте для расчета
функцию ЕСЛИ и другие логические функции.
46
4. Рассчитайте суточные, исходя из приведенных тарифов для различных городов, используя функцию ЕСЛИ.
5. Рассчитайте сумму к оплате для каждого командированного сотрудника, учитывая,
что она равна сумме стоимости проезда, суточных и стоимости проживания. С помощью соответствующих формул вычислите и занесите в отдельные ячейки минимальные, максимальные и средние командировочные расходы. Построить диаграмму, иллюстрирующую
сумму, полученную каждым работником на руки.
Лабораторная работа №8
Тема: Построение диаграмм и графиков функций в MS Excel
Программное обеспечение: OS Windows, MS Excel
Графическое представление помогает осмыслить закономерности, лежащие в основе
больших объемов данных. Один взгляд на диаграмму или график иногда дает гораздо больше, чем длительное изучение длинных колонок чисел. MS Excel предлагает богатые возможности визуализации данных. Первое задание направлено на освоение приемов построения и
модификации трех основных типов диаграмм: гистограмма, круговая диаграмма и график.
Во втором задании приводится алгоритм построения графиков функций с помощью точечной диаграммы.
Задание 1. Построение диаграмм.
Порядокработы:
1. Создать таблицу по образцу (рис. 12).
2. Выделить значения столбцов Приход и Расход без заголовков.
3. Выполнить команду Вставка/Гистограмма, а затем, не снимая выделения с диаграммы,
команду Конструктор/Выбрать данные.
4. В открывшемся диалоговом окне:
a. В категории «Элементы легенды (ряды)» выделить Ряд 1,
нажать «Изменить», выделить ячейку с заголовком «Приход», нажать ОК
новое имя
ряда «Приход» появится в диалоговом окне и на диаграмме.
По аналогии Ряд 2 переименовать в «Расход».
b. В категории «Подписи горизонтальной оси (категории)» нажать «Изменить» и выделить диапазон ячеек со значениями годов, ОК, ОК (рис. 12).
5. Не снимая выделения с диаграммы, перейти в меню Формат и внести изменения в категориях Стили WordArt и Стили фигур, по одному из параметров диаграммы (по выбору) в
каждой категории. Гистограмма готова. Снять выделение.
6. Выделить значения ряда «Приход» (без заголовка).
7. Выполнить команду Вставка/Круговая диаграмма, а затем, не снимая выделения с диаграммы, команду Конструктор/Выбрать данные.
8. В открывшемся диалоговом окне:
47
a. В категории «Элементы легенды (ряды)» выделить Ряд 1, нажать «Изменить», выделить ячейку с заголовком «Приход», нажать «ОК», после чего новое имя ряда «Приход» появится в диалоговом окне и на диаграмме.
b. В категории «Подписи горизонтальной оси (категории)» нажать «Изменить» и выделить диапазон ячеек со значениями годов, ОК, ОК.
9. Не снимая выделения, выполнить команду Конструктор/Макеты диаграмм и выбрать в
перечне третий образец во втором ряду. Круговая диаграмма готова. Снять выделение (рис.
12).
10. Выделить значения ряда «Расход» (без заголовка).
11. Выполнить команду Вставка/График, а затем, не снимая выделения с диаграммы, команду Конструктор/Макеты диаграмм и выбрать первый образец в списке.
12. В получившейся диаграмме выделить надпись «Название диаграммы», удалить шаблонное название и написать «Расход». Затем выделить надпись «Название оси», удалить
шаблонное название и написать «Млн. руб.».
13. Правой кнопкой мышки щелкнуть по подписям оси ОХ (вызов контекстного меню),
выбрать пункт «Выбрать данные».
14. В диалоговом окне изменить название ряда «Ряд 1» на «Расход», а по горизонтальной
оси сделать подписи соответствующих годов.
15. Правой кнопкой мыши щелкнуть по ряду данных на диаграмме и выбрать «Добавить
подписи данных».
16. Правой кнопкой мыши щелкнуть по ряду данных на диаграмме и выбрать «Добавить
линию тренда». Ничего не меняя в открывшемся окне, нажать «Закрыть». График с линией
тренда построен. Снять выделение (рис.12).
17. Внесите изменения в построенную круговую диаграмму. Выделите один из секторов
диаграммы, щелкните по выделенному сектору правой кнопкой мыши и выберите команду
Формат точки данных/Заливка, поставьте переключатель «Сплошная заливка» и выберите
новый цвет сектора.
18. Выделите гистограмму и скопируйте в Буфер Обмена. Выполните команду Вставить.
19. Внести изменения в копию гистограммы. Для этого правой кнопкой мыши щелкнуть
по рядам данных на диаграмме и выбрать пункт Выбрать данные.
20. В категории «Элементы легенды (ряды)» нажать кнопку «Добавить», дать новому
ряду имя «Приход фирмы» и выделить значения ряда «Приход» (без заголовка). Щелкнуть
правой кнопкой мыши по новому ряду на диаграмме и выбрать «Изменить вид ряда данных»
и выбрать «График с маркерами» первого вида. Добавить на новом ряду подписи данных.
21. Аналогичные действия проделайте с добавлением ряда «Расход фирмы» (рис.12).
48
Задание 2. Построение графика функции.
Построить график функции y = x
3 cos x 2 на отрезке [0; 1] с шагом 0,1.
Порядокработы:
1. Построим таблицу, состоящую из ряда значений аргумента Х, значений функции Y,
начального значения (НЗ) и шага (рис. 13). Значения НЗ и шага вводятся с клавиатуры. При
этом на рабочем листе необходимо создать три формулы:
a) в ячейке А2: =С2 (т.е. первое значение в ряду Х равно начальному значению).
b) в ячейке А3: =А2+$D$2 и скопировать формулу вниз до достижения значения 1.
c) в ячейке В2: =ABS(A2-3)*COS(ПИ()*A2^2) и скопировать формулу вниз по столбцу.
2. Выделить ряды X и Y вместе с заголовками и выполнить команду Вставка/Точечная,
выбрать вид гладкой кривой без маркеров.
3. Изменить вид диаграммы, согласно образцу (рис. 13). Рис. 12. Построение диаграмм
Рис. 13. Построение графика функции
Задание для самостоятельной работы
Построить графики следующих функций с шагом 0,1. Фрагменты графиков для проверки
приводятся в таблице 1.
49
Таблица 1
Задание для самостоятельной работы
Лабораторная работа №9
Тема: Работа с массивами в MS Excel
Программное обеспечение: OS Windows, MS Excel
Методические указания
При работе с таблицами часто возникает необходимость применить одну и ту же операцию к целому диапазону ячеек или произвести расчеты по формулам, зависящим от большого массива данных.
Массив – прямоугольный диапазон формул или значений, которые MS Excel обрабатывает как единую группу. Прямоугольный числовой массив, состоящий из m строк и n
столбцов, принято называть матрицей размерности mxn .
При работе с массивами необходимо помнить следующие два основных правила:
1. Результатом операции над массивами (матрицами) также является массив (матрица)
определенного размера. Поэтому при вводе формулы необходимо заранее выделить весь
диапазон рабочего листа, где будет находиться будущий результат.
50
2.
По
окончании
ввода
формулы
нажимать
комбинацию
клавиш
CTRL+SHIFT+ENTER.
Две матрицы одинаковой размерности можно сложить или вычесть путем простого
сложения или вычитания (получится массив такой же размерности). Функции для работы с
матрицами приведены в таблице 1.
Таблица 1
Функция ТРАНСП находится в категории «Ссылки и массивы», а все остальные – в
категории «Математические».
Задача 1
Выполните следующие действия:
1) Найдите сумму и разность двух матриц:
и
2) Найти произведение матриц:
3) Вычислить определители матриц из п. 1)
4) Найти матрицы, обратные к матрицам из п.1)
5) Решите систему уравнений методом обратной матрицы
Задача 2
Решите следующие системы уравнений методом обратной матрицы, предварительно
найдя матрицу коэффициентов при неизвестных:
51
Задача 3
Решить задачу, используя функции работы с массивами и матрицами.
Ателье выпускает три вида изделий: брюки, юбки, жилеты. При этом используется
два вида тканей: шерстяная и подкладочная. Норма расхода тканей характеризуется матрицей A (таблица 2).
Таблица 2
Определить:
a) количество метров тканей (D), необходимое для выпуска следующих изделий (таблица 3).
Таблица 3
b) общую стоимость тканей (S), если известна их цена (С)
Лабораторная работа №10
52
Тема: Условное форматирование в MS Excel
Программное обеспечение: OS Windows, MS Excel
Методические указания
Условное форматирование – это один из способов визуального выделения данных,
отвечающих определенным требованиям (критериям).
Для его применения необходимо выделить массив данных (без заголовков) и выполнить команду:
Главная/Стили/Условное форматирование/Правила выделения ячеек/Другие
правила.
Данная команда позволяет пользователю создавать свои правила выделения ячеек.
Кроме этого, имеется еще множество встроенных правил выделения, доступных по той же
команде.
Задача о транспортной компании.
Обработать совокупные данные о грузоперевозках транспортной компании за 2009
год, применяя условное форматирование.
Таблица 1
Таблица 2
53
Порядокработы:
1. Перенести на рабочий лист таблицы 8 и 9 вместе с данными.
2. Создать формулы для заполнения данными строк «суммарно», «максимум», «минимум», «среднеквартальное».
3. Получить итоговые данные в столбце «За 2009 год». Они должны быть представлены в числовом формате с одним знаком после запятой.
4. На основании имеющихся и полученных данных построить:
a. диаграмму (тип – "график с маркерами"), сравнивающую изменение объема перевозок по направлениям за 1, 2, 3 и 4 кварталы одновременно (каждый ряд данных отображает
объемы перевозок на протяжении одного квартала по всем направлениям); заголовок и подписи данных не показывать, легенда должна отражать подписи рядов: 1квартал, 2 квартал и
т.д.
b. диаграмму (тип – "круговая"), отражающую долю перевозок каждого квартала 2009
года (суммарно) в сумме перевозок за год; секторы должны быть подписаны кварталами, легенду показывать не обязательно;
заголовок: "Объем перевозок за 2009 год по кварталам".
5. Задайте условный формат для верхней части таблицы 8, в результате применения
которого ячейки, значение которых меньше среднего значения за год, были бы помечены
светло-розовым фоном и синим жирным шрифтом, а ячейки, значение которых больше среднего значения за год, были бы помечены светло-голубым фоном и красными пунктирными
границами.
6. Заполните таблицу 2 данными, полученными в результате вычисления процента
перевозок, принимая за 100% суммарное количество перевозок за квартал (см. расчеты таблицы 1). При этом формула для вычисления процента должна быть введена только в одну
ячейку (например, Москва-1 квартал), а в остальные скопирована. Чтобы это было возможным, примените смешанные ссылки. Формулу для вычисления создайте самостоятельно, используя известные соотношения в пропорциях.
Данные должны быть отражены в процентном формате с одним десятичным знаком
после запятой.
7. Примените условный формат к полученным данным. В результате ячейки, содержащие значения менее 10%, должны быть помечены синим шрифтом, а ячейки, содержащие
значения более 20%, должны быть помечены красным шрифтом.
Методические указания по выполнению самостоятельной работы
и изучению дисциплины
Тенденция усиления фактора самостоятельной работы в организации з анятий студентов требует методического руководства при изучении дисциплины. Знания и навыки, полученные во время аудиторных занятий, закрепляются
в ходе выполнения самостоятельной работы.
Цели СРС – формирование у студентов навыков к самостоятельному
творческому труду, умения решать профессиональные задачи с использованием
54
всего арсенала современных средств, потребности к непрерывному самообразованию и совершенствованию своих знаний; приобретение опыта планирования и организации рабочего времени и расширение кругозора. Планирование,
организация, контроль и анализ СРС являются необходимыми составляющими
научной организации учебного процесса, позволяющими обеспечить полноценное управление и необходимую эффективность учебной работы. По мере
увеличения объема пройденного учебного материала возрастает доля самостоятельной работы. Кроме аудиторной появляется и внеаудиторная работа: в библиотеке, дисплейном классе, консультации, а на заключительном этапе (последние 3–4 недели) постепенно возрастает доля самостоятельной работы (подготовка к итоговой аттестации по дисциплине).
Примерные нормы времени на выполнение студентами внеаудиторной
самостоятельной работы приведены в таблице 1.
Таблица 1
Примерные нормы времени на выполнение студентами внеаудиторной
самостоятельной работы
Вид
самостоятельной работы
1 Проработка:
- конспекта лекций
- учебников, учебных пособий и обязательной литературы
2 Подготовка:
- выполнению лабораторной
работы
- к практическому (семинарскому) занятию
- к контрольному опросу (тестированию)
Единица
измерения
Норма
времени, час
1 час
лекций
0,5-1,0
1 п. л.
0,9-1,0
4-часовая работа 1-2
4-часовое занятие 1-2
1 опрос (тестиро1,5
вание)
Согласно учебному плану на СРС отводится примерно 60 % от общего
количества часов, необходимых на изучение дисциплины. Формами СРС при
изучении дисциплины «Информационные системы» являются:
- изучение лекционного материала и разделов, входящих в программу
55
дисциплины, но не изучаемых на лекциях;
- подготовка к выполнению лабораторных работ;
- подготовка к текущей и итоговой аттестации (контрольный опрос или
тестирование).
При подготовке к выполнению лабораторной работы рекомендуется
ознакомиться с литературой по соответствующей теме. Итогом работы является
защита полученных результатов, защита проводится устно или в форме тестирования.
Все лабораторные работы сохраняются в рабочей папке студента. После
выполнения всех работ формируется отчет. Защита лабораторной работы пр оизводится студентом в день ее выполнения в соответствии с планом-графиком.
Преподаватель проверяет правильность выполнения лабораторной работы студентом, контролирует знание студентом пройденного материала с помощью контрольных вопросов или тестирования.
Проработка конспекта лекций и учебной литературы осуществляется студентами в течение всего семестра, после изучения новой темы. Дважды в с еместр предусмотрена текущая аттестация в виде контрольных опросов и итоговая аттестация в виде экзамена. К экзамену допускаются студенты, выполнившие все виды текущей аттестации – лабораторные работы и контрольные опросы. Для сдачи экзамена студент выбирает случайным образом билет, на подготовку отводится время не более 20 мин.
Экзамен сдается в виде устного ответа на теоретический вопрос билета и
решение практических задач.
При изучении дисциплины рекомендуется использовать конспект лекций,
программу курса, план-график выполнения СРС, методические указания к лабораторным работам. Подготовку к контрольному опросу и экзамену рекомендуется проводить на основе вопросов для текущей и итоговой аттестации.
Последовательность действий студента при изучении дисциплины:
- Посещение занятий, проработка конспекта лекций.
56
- Посещение библиотеки, работа с литературой.
- Изучение методических рекомендаций к лабораторным работам.
- Выполнение лабораторной работы в аудитории.
- Подготовка к контрольному опросу, проработка конспекта лекций и литературы за пройденный период.
- Ответы на контрольный опрос.
- Подготовка к экзамену, проработка конспекта лекций и литературы по
всему курсу.
- Сдача экзамена.
При изучении дисциплины рекомендуется использовать конспект лекций,
программу курса, план-график выполнения СРС, методические указания к лабораторным работам, РГР. Подготовку к контрольному опросу и зачету рекомендуется проводить на основе вопросов для текущей и итоговой аттестации.
При подготовке к выполнению лабораторной работы работ рекомендуется ознакомиться с литературой по соответствующей теме. При подготовке к
контрольному опросу и экзамену рекомендуется проработать литературу по
каждой теме, предусмотренную рабочей программой дисциплины.
При подготовке к контролю знаний студент должен вначале ознакомиться
со списком основной и дополнительной литературы и самостоятельно просмо треть рекомендованные разделы по данному материалу, изучить соответствующие разделы учебного издания. По окончании изучения материала студент обязан ответить на контрольные вопросы, пройти тестирование. Рекомендуется регулярно повторять и прорабатывать материал лекций и учебников в течение
всего семестра. Своевременное выполнение плана-графика, защита лабораторных работ, сдача контрольных опросов текущей аттестации позволят успешно
сдать экзамен до начала сессии и освободить время для подготовки к более
сложным предметам и сдачи экзаменов в период сессии.
Тестирование представляет собой процедуру, позволяющую объективно
установить уровень учебных достижений студентов в области теоретических
57
знаний, интеллектуальных умений, практических навыков. Тесты включают в
себя вопросы, содержащиеся в программе дисциплины, которые выносятся на
контроль. Тестовые задания, используемые для текущего, итогового и остаточного контроля знаний.
Используемая форма тестовых заданий определяется содержанием фрагмента учебного материала.
58
СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ
Основная литература:
1. Информационные системы и технологии в экономике и управлении: учебник
/ под ред. проф. В.В. Трофимова. – 3-е изд., перераб. и доп. – М.: Издательство
Юрайт, 2012. – 521 с.– (Основы наук).
2. Гагарина Л.Г. и др. Компьютерный практикум для менеджеров: информационные технологии и системы: учебное пособие / Л.Г. Гагарина, Е.М. Портнов,
И.С. Холод; под ре-дакцией Л.Г. Гагариной. – М.: Финансы и статистика, 2006.
– 352 с.
3. Хроленко А.Т. Современные информационные технологии для гуманитария.М.: Флинта, 2007-128 с.
Дополнительная литература:
1. Автоматизированные информационные технологии в экономике: Учебник /
Под ред. проф. Титоренко Г.А. – М.: ЮНИТИ, 2009
2. Информационные системы и технологии в экономике: учеб.пособие / Е.Н.
Ефимов, Е.В. Ефимова, Г.М. Лапицкая; под ред. к.э.н., проф. Г.М. Лапицкой. –
Ростов н/Д: Издательский центр «МарТ»; Феникс, 2010. – 286 с.: ил. – (Учебный курс).
5. Белов В.М., Новиков С.Н., Солонская О.И. Теория информации. Курс лекций. Учебное пособие для вузов. – М.: Горячая линия – Телеком, 2012. – 143 с.:
ил. Режим доступа: www.iprbookshop.ru.
6. Теория информационных процессов и систем: учебник для студ. высш.
учеб.заведений / [Б.Я. Советов, В.А. Дубенецкий, В.В. Цехановский и др.]; под
ред. Б.Я. Советова. – М.: Издательский центр «Академия», 2010. – 432 с.(Университетский учебник. Сер. Прикладная математика и информатика).
Электронные ресурсы, Интернет-ресурсы, электронные библиотечные
системы:
1. www.iprbookshop.ru – Электронно-библиотечная система IPRbooks
2. www.intuit.ru – Национальный открытый университет – «ИНТУИТ»
3. www.iis.ru – Глоссарий по информационному обществу
4. www.ict.nsc.ru/win/fedotov/inter/internet.html – Спецкурс «Введение в
Internet», посвященный глобальной компьютерной сети Internet и спецсеминар
«Информационные технологии»
5. www.consultant.ru – Справочные правовые системы семейства «КонсультантПлюс»
6. www.garant.ru – Справочная правовая система «Гарант»
7. http://window.edu.ru/ – Финансовая электронная библиотека Миркин.Ру
8. http://www.gaudeamus.omskcity.com/ – Электронная библиотека
9. http://www.aup.ru/ – Административно-управленческий портал. Бесплатная
электронная библиотека по вопросам экономики, финансов, менеджмента и
маркетинга на предприятии.
59
10. www.ozon.ru – Онлайн мегамаркет
60
Документ
Категория
Без категории
Просмотров
19
Размер файла
1 715 Кб
Теги
информационные, система, управления, 520
1/--страниц
Пожаловаться на содержимое документа