41650

Технологии работы со списками в EXCEL

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

Информатика, кибернетика и программирование

Цель работы: научиться создавать при помощи EXCEL базу данных содержащую различные сведения о работе фирмы. Список выполняет функцию базы данных. Столбцы называются полями а строки записями множество записей образует поле данных. Требуется создать базу данных работы фирмы и автоматизировать работу выдачи бланков заказов.

Русский

2013-10-24

1.79 MB

158 чел.

Лабораторная работа №1. Технологии работы со списками в EXCEL.

Цель работы: научиться создавать при помощи EXCEL базу данных, содержащую различные сведения о работе фирмы.

Задачи:

  1.  Создание списков.
  2.  Организация связей между списками.
  3.  Создание БЛАНКА ЗАКАЗА.

Теоретические сведения.

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

  1.  Название столбца занимает одну ячейку;
  2.  Все данные в ячейках столбца представлены в одном формате;
  3.  Все названия столбцов размещены в одной строке и образуют строку заголовка списка;
  4.  Данные помещаются в строке следующей за строкой заголовков.

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

Задание 1. Создание списков.

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

1.1. Создание списка КЛИЕНТЫ.

  1.  Создайте рабочую книгу  (Файл / Создать).
  2.  Присвойте первому рабочему листу имя Клиенты.
  3.  Введите в ячейки A1: F1 следующие заголовки:
  4.  Название фирмы
  5.  Код
  6.  Контактная персона
  7.  Город
  8.  Телефон
  9.  Скидка (%)


  1.  Отформатируйте таблицу, используя пункт меню Формат / Автоформат.  В списке форматов выберите Список1.
  2.  Для ячеек, содержащих процентное содержание скидки, назначьте процентный формат, либо используя пиктограмму на панели  инструментов Форматирование, либо через команду меню Формат / Ячейка /Число.

  1.  Создание списка ТОВАРЫ.

  1.  Перейдите на второй лист рабочей книги и присвойте ему имя ТОВАРЫ.
  2.  Список ТОВАРЫ должен состоять из трех столбцов: Номер, Наименование  товара, Цена (USD).
  3.  В ячейки А1-С1 введите соответствующие заголовки.
  4.  Введите записи в список ТОВАРЫ.
  5.  Отформатируйте таблицу аналогично списку КЛИЕНТЫ.

  1.  Создание списка ЗАКАЗЫ.
  2.  Перейдите на третий лист рабочей книги и присвойте ему имя ЗАКАЗЫ.
  3.  Определяем следующую структуру списка:

  1.  Введите данные по следующим полям: Месяц, Дата, Номер заказа, Номер товара, Код заказчика,  Количество.

  1.  Отформатируйте таблицу по своему усмотрению.
  2.  Заполним автоматически столбцы Наименование товара, Фирма,  Сумма, Скидка, Оплачено в списке ЗАКАЗЫ, используя данные из соответствующих столбцов списка ТОВАРЫ и списка КЛИЕНТЫ соответственно.

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

Автоматическое заполнение столбцов будем производить с помощью функции ПРОСМОТР, аргументами которой являются имена диапазонов необходимых ячеек.

1) СИНТАКСИС функции ПРОСМОТР:

ПРОСМОТР (искомое значение; просматриваемый вектор; вектор результатов)

Вектор в MS Excel - это массив, который содержит только одну строку или один столбец.

Искомое_значение - это значение, которое ПРОСМОТР ищет в первом векторе.

Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

Просматриваемый_вектор - это интервал, содержащий только одну строку или один столбец. Значения в аргументе Просматриваемый вектор могут быть текстами, числами или логическими значениями.

Вектор результатов - это интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый  вектор.

2) СИНТАКСИС функции ЕСЛИ:

ЕСЛИ (Р, А, В), где Р - логическое выражение, А – значение, вычисленное при истинном значении Р,  В – значение, вычисленное при ложном значении Р.

С помощью функции ЕСЛИ можно задать отмену заполнения ячеек в том случае, если запись не введена, что позволит избежать появления значений ошибки.

  1.  Заполним столбец Наименование товара. Для этого.
  2.  Сделайте текущей ячейку E2.
  3.  В строке формул введите следующую формулу:

=ПРОСМОТР(D2;Товары!$A$2:$A$10;Товары!$B$2:$B$10), где ячейка D2 – искомое значение, которое необходимо найти в просматриваемом векторе,  диапазон $A$2:$A$10 – просматриваемый вектор (столбец Номер товара в списке Товары, $B$2:$B$10 – вектор результатов (столбец  Наименование товара в списке Товары).

  1.  Скопируйте формулу в смежные ячейки E3:E12.
  2.  Используя функцию ПРОСМОТР, заполните самостоятельно столбец Фирма.
  3.  Определим значение столбца Сумма: Цена (список Товары)*Количество (список Заказы).
  4.  Для этого в ячейку I2 введите формулу: =ЕСЛИ(H2="";"";H2*ПРОСМОТР(D2;Товары!$A$2:$A$10;Товары!$C$2:$C$10)), где H2 – ячейка с количеством, D2 - просматриваемая ячейка, диапазон $A$2:$A$10 – просматриваемый вектор (столбец Номер товара в списке Товары), а диапазон $C$2:$C$10 – вектор результатов (столбец Цена в списке Товары).
  5.  Т. О.  если ячейка Н2 = 0  (данные отсутствуют), то возвращаемое значение отсутствует (‘’’’), если ячейка Н2 содержит данные, то возвращаемое значение равно H2*ПРОСМОТР(D2;Товары!$A$2:$A$10;Товары!$C$2:$C$10).
  6.  Рассчитайте значения столбца Скидка. Для этого в ячейку J2 введите формулу: =I2*ПРОСМОТР(F2;Клиенты!$B$2:$B$10;Клиенты!$F$2:$F$10), где диапазон $B$2:$B$10 - просматриваемый вектор (столбец Код заказчика в списке Клиенты), а диапазон $F$2:$F$10 – вектор результатов (столбец Скидка в списке Клиенты).
  7.  Рассчитайте значения столбца Оплачено. 

Задание 2. Создание Бланка заказов.

Задача: Предусмотреть возможность печати Бланка Заказа, который может заполняться автоматически при внесении конкретного номера заказа.

  1.  Откройте 4-ый лист вашей рабочей книги. Дайте ему название Бланк. 
  2.  В  область  для  номера заказа введите любой номер из списка Заказы

  1.  В остальные выделенные области будем вносить формулы:
  2.  В область  Дата  внесите формулу, позволяющая вводить автоматически дату

= ПРОСМОТР (Ячейка Номер заказа из Бланка; поле Номер заказа из списка Заказы; поле Дата из этого же списка)

  1.  В область Название фирмы  внесите формулу, позволяющую вводить автоматически название фирмы заказчика.

= ПРОСМОТР (Ячейка Номер заказа из Бланка; поле Номер заказа из списка Заказы; поле Фирма из этого же списка)

  1.  Аналогично заполним выделенные области Количество, Скидка, К оплате.
  2.  В область Доверенность выдал внесите формулу, позволяющая вводить автоматически фамилию: = ПРОСМОТР (ячейка Название фирмы из Бланка, Поле Название фирмы из списка Клиенты, поле Контактная персона из этого же списка).
  3.  Аналогично заполните область Телефон.
  4.  В область Цена единицы  внесите формулу:  =ПРОСМОТР (ячейка Название товара из Бланка, поле Наименование товара  из списка Товары, поле Цена из этого же списка)
  5.  Внесите в ячейку G11 для поля Оформил свою Фамилию.
  6.  Вы создали Бланк заказа, позволяющий автоматически получать данные Заказа согласно введенному вами номеру Заказа. Поработайте со своим Бланком Заказа. Внесите другие номера заказов. Проверьте правильность результатов.

Контрольные вопросы

  1.  Что такое список.
  2.  Обязательные требования к оформлению списка.
  3.  Требования к полям списка.
  4.  Можно ли использовать объединение ячеек при создании списка.
  5.  Объясните действие функции ПРОСМОТР.
  6.  Использование функции ЕСЛИ при работе со списками.

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

1. Создайте таблицу  «Сводная  сессионная ведомость» вашей группы. Таблица состоит из следующих списков:

  1.  Список СТУДЕНТ. Поля: Фамилия студента, Предмет, Оценка, Дата экзамена
  2.  Список ПРЕДМЕТ. Поля: Фамилия преподавателя, Предмет, Дата экзамена, Аудитория.

2. Создайте Бланк для разрешения на пересдачу  экзамена, который должен автоматически заполняться  по данным, содержащимся в списках. Бланк должен заполняться, если оценка равна 2. Используйте для этого  функцию  ЕСЛИ (оценка=2, печатается фамилия, иначе - ‘’ ‘’).

Лабораторная работа №2. Вычисление итогов и структурирование данных.

Цель работы: Изучение инструментов для вычисления промежуточных итогов на основе одной или нескольких таблиц.

Задачи:

  1.  Проводить декомпозицию таблиц, посредством разбивки ее на связные классы.
  2.  Выполнять автоструктурирование таблиц, содержащих формулы и функции.
  3.  Выполнять структурирование таблиц с подсчетом итогов по изменяющемуся полю.

Теоретические сведения.

Большие таблицы неудобны для анализа – при обработке данных представленных в них приходится  делать много лишних перемещений по полям и записям таблицы (операции навигации), закрывать и открывать отдельные части таблиц. С целью упрощения анализа данных в Excel предусмотрены операции по группировке и структурированию данных, которые позволяют  выделить из основной таблицы подтаблицы, то есть представить таблицу в виде иерархии подтаблиц.

Структурирование таблиц  можно проводить как по строкам, так и по столбцам. На одной и той же таблице можно построить несколько вариантов структур.

 Требования к структурируемым данным:

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

Выполнение структурирование выполняется с помощью команд: Данные / Группа и структура; Данные / Итоги. Последняя команда не только выполняет структурирование, но и позволяет выполнять расчеты над записями таблицы.

Промежуточными итогами называются вычисления выполненные с помощью определенной функцией по изменяющимся значениям записей одного из полей списка .

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

Рассмотрим список ЗАКАЗЫ, созданный в предыдущих работах. Поля списка содержат сведения о заказчиках (левая часть списка), заказах (центральная часть) и исполнении заказа (правая часть).

Если в ваших отчетах таблица отличается, от приведенной выше, выполните предварительно сортировку записей по основному полю (Код заказчика).

Задание 1. Структурирование таблицы по полям и записям.

  1.  Выделим весь список.
  2.  Выберем команду Данные / Группа и структура  / Группировать / Столбцы. Получим структуру первого уровня (вся таблица). Обратите внимание на появившуюся линию уровня Охватывающую все поля списка и заканчивающуюся кнопкой со знаком  « - ». Выполнив щелчок по кнопке можно свернуть список.

  1.  Выделим из нее таблицы  второго уровня, содержащие поля

Месяц

Дата

Номер заказа

Номер товара

Наименование товара

  1.  Выберем команду Данные / Группа и структура  / Группировать / Столбцы. Получим структуру второго уровня. Продолжая процесс группировки по столбцам, а затем по строкам,  получите следующую иерархию:

  1.  Щелкая по соответствующим кнопкам со знаком  «- » и «+», сверните и разверните элементы структуры.
  2.  Для удаления структуры выбирается команда Данные / Группа и структура  / Удалить структуру.
  3.  Построим на данной таблице другую структуру. Выделим связные блоки полей и записей. Например, выделим сначала столбцы Месяц и Дата и выполним группировку. Результатом операции будет группировка полей Месяц и Дата.
  4.  Выделим  столбцы Номер товара и Наименование товара. Выполним группировку. Результатом операции будет группировка полей Номер товара  и Наименование товара.
  5.  Выполните группировку по записям ОАО Финиш.
  6.  Сверните и разверните классы структуры.

Задание 2. Автоструктурирование.

Автоструктурирование выполняется только для таблиц содержащих формулы.

Требование: ссылки в формулах должны быть на ячейки в смежных левых полях или  смежных верхних записях.

  1.  Сформируем и заполним таблицу ВЕДОМОСТЬ.

  1.  В ячейки E4, F4, G4, H4, I4, J4, K4 вставьте формулы и скопируйте их на остальные ячейки списков.

  1.  Выполним над заполненным списком Автоструктурирование. Данные / Группа и структура / Создать структуру.

  1.  Свернем структуру  по нижним уровням.

  1.  Свернем по верхнему уровню. Получим только одно поле «К выдаче»

  1.  Удалите структуру.

Задание 3. Структурирование с подсчетом ИТОГОВ.

Выполним структурирование списка ВЕДОМОСТЬ  с подсчетом Итогов.

Для выполнения структурирования необходимо определить основное поле, по которому будет проводиться структурирование. Пусть это будет поле Ф.И.О.

  1.  Выполним сортировку записей в поле, тем самым разобьем записи на классы. Для этого выделим диапазон ячеек А4:К12, выполним команду Данные / Сортировка, в окне установите сортировку по полю ФИО.
  2.  Выполним команду Данные / Итоги. В открывшемся окне установим
  3.  Заголовок изменяющегося поля;
  4.  Операцию;
  5.  Поля с вычисляемыми итогами (Начислено, Удержано, К выдаче);
  6.  Итоги под данными;
  7.  Другие опции.

  1.  Получим структуру. Строки с итогами выделены жирным шрифтом.

  1.  Свернем структуру. Получим только Итоговые строки.

  1.  Выполнив свертку еще раз, получим одну строку Общих итогов.

  1.  Удалите структуру, выделите список, затем введите команду Данные / Итоги / Убрать все.
  2.  Скопируйте таблицу «Ведомость» на новый лист.
  3.  Подведите Итоги, изменив функцию Суммы на функцию Среднее.
  4.  Перегруппируйте данные (проведите сортировку по месяцам) и выполните подсчет Итогов по месяцам.

  1.  Свернем структуру. Получим только Итоговые строки.
  2.  Выполнив свертку еще раз, получим одну строку Общих итогов.
  3.  Измените функцию Суммы на функцию Среднее.

Контрольные вопросы.

  1.  Что такое структурирование таблиц, для чего оно применяется.
  2.  Какие требования к подклассам данных структуры.
  3.  Как вы понимаете термин «Смежные классы».
  4.  Как выполняется «ручное структурирование»
  5.  Основные требования для автоструктурируемых таблиц. Почему нельзя выполнить Автоструктурирование для таблицы «Заказы»
  6.  Что такое промежуточные итоги.
  7.  Для чего нужно сортировать записи перед просчетом итогов.

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

  1.  Создайте на одном из рабочих листов список.

  1.  Выполните Автоструктурирование.
  2.  Выполните «ручное» структурирование по полям Предмет - Семестр, План в часах, Фактически в часах и записям «Весенний - Осенний».
  3.  Подсчитайте Итоги по изменяемым полям:  а) Предмет; б) Семестр.

 

Лабораторная работа №3. Консолидация данных и их анализ в сводной таблице.

Цель работы: изучить механизмы подведения  Итогов  посредством объединения (консолидирования) данных  разных источников.

Задачи:

  1.  Консолидирование данных из разных списков.
  2.  Конструирование сводных таблиц.
  3.  Анализ данных в сводных таблицах

Теоретические сведения.

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

.

В Excel существуют операции, позволяющие выполнять несколько вариантов консолидации списков.

  1.  По расположению
  2.  По категориям
  3.  С помощью сводных таблиц

При консолидации по расположению все списки источники имеют структуру полей, названия  заголовков и количество  записей. Например, ведомости заработных плат за несколько месяцев (если отсутствует «текучка кадров»). В данном варианте строка заголовков списков источников одинаковая и совпадает со строкой заголовков результата. Операция консолидации по расположению коммутативна, т.е. результат не зависит от порядка консолидации списков источников.

При консолидации по категориям  списки источники содержат однотипные данные, но расположены поля в этих списках по-разному и количество записей тоже разное. Список результатов консолидации также содержит поля отличающихся от расположения полей источников. Операций консолидации по категориям не коммутативна, т.е. структура списка результата зависит от порядка консолидируемых источников. Консолидация данных производится с помощью команды Данные / Консолидация.

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

Создание и обработка сводных таблиц осуществляется с помощью Мастера сводных таблиц, Данные / Сводная таблица.

Задание 1. Консолидация данных по расположению.

  1.  Подготовим исходные данные.
  2.  В качестве таблицы источника выберем «Ведомость» (см. пред. работа).
  3.  С помощью команды Фильтр / Автофильтр выберем из нее последовательно записи, относящиеся к месяцам (октябрь и ноябрь).
  4.  Скопируем их и разнесем по разным листам книги. Листы назовем Октябрь и Ноябрь.
  5.  Из таблиц удалим столбцы Стаж и Месяц.
  6.  В таблицу «Ведомость за ноябрь» добавим одну запись, чтобы число их в каждой таблице было одинаковое.

  1.  Выполним консолидацию данных по расположению.
  2.  Добавим в книгу новый лист Консолидация.
  3.  Активизируем ячейку А1 и выполним команду Данные / Консолидация.
  4.  В открывшемся окне введем диапазон, занимаемым первым списком – Октябрь!$A$3:$I$7. Нажмем кнопку Добавить. 
  5.  Далее введем диапазон, занимаемым вторым списком – Ноябрь!$A$3:$I$7. Нажмем кнопку Добавить.
  6.  Установим функцию из раскрываемого списка, например Сумма

  1.  Установим флажки на опциях: Использование в качестве имен, или Создавать связи с исходными данными (но не одновременно). Нажмем ОК.
  2.  Получим таблицу консолитизированных данных. Как видим, структура таблицы не изменилась, а значения в ячейках просуммированы.

Задание 2. Консолидация по категориям.

  1.  Подготовим исходные данные.
  2.  В качестве таблицы источника выберем «Ведомость» (см. пред. работа).
  3.  С помощью команды Фильтр / Автофильтр выберем из нее последовательно записи, относящиеся к месяцам (сентябрь, октябрь и ноябрь).
  4.  Скопируем их и разнесем по разным листам книги. Листы назовем Сентябрь, Октябрь и Ноябрь.
  5.  Из таблиц удалим столбцы Стаж и Месяц.
  6.  Данные в источниках дополнять не будем. Количество записей в каждой из таблиц отличается.
  7.  Выполним консолидацию всех списков по описанной выше технологии. Получим таблицу консолитизированных данных.

Задание 3. Консолидация  списков с разным числом полей.

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

  1.  Список «Ведомость за сентябрь». Начисление

2. Список «Ведомость за октябрь». Удержание.

3. Список «Ведомость за ноябрь». Удержание.

4. Структура результата консолидации.

Задание 4. Создание сводной  таблицы.

Выполним анализ данных и консолидацию на основе сводных таблиц

  1.  Откройте новый рабочий  лист и присвойте ему имя Таблица. 
  2.  Выполните команду Данные/Сводная таблица. После ее активизации откроется первое диалоговое окно Мастера сводных таблиц.

  1.  Шаг 1. В этом окне вам предлагается выбрать один из четырех источников данных для сводной таблицы и выбрать форму отчета.. Одобрите выбор мастера - В списке или базе данных Microsoft Office Excel - и нажмите кнопку Далее.

  1.  Шаг 2. В следующем диалоговом окне необходимо указать интервал ячеек, данные из которого будут представлены в сводной таблице. Выберем диапазон ячеек  списка Ведомость (кроме строк Ведомость оплаты труда, Начислено и Удержано). Нажмем кнопку Далее.

  1.  Шаг 3. Определим расположение сводной таблицы: На  существующем листе.

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

  1.  Поместим: в область строк - поле ФИО; в область столбцов – поле Месяц; в область данных - поля Начислено, Удержано, К выдаче. Щелкнем Готово. Сводная таблица построена.

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

  1.  Постройте сводную диаграмму, выбрав соответствующую команду из контекстного меню или кнопку на панели Сводные таблицы.

  1.  Отредактируйте и отформатируйте сводную диаграмму.


Контрольные вопросы.

  1.  Что такое консолидация.
  2.  Какие виды консолидации вы знаете.
  3.  В чем они отличаются.
  4.  Можно ли изменять функцию консолидации.
  5.  Можно ли связать результат консолидации с источниками.
  6.  Что такое сводная таблица.
  7.  Как изменить параметры таблицы.
  8.  Как построить макет таблицы.

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

1. Выполните консолидацию списков.

предмет

семестр

лекции

практические

всего

лекции

практические

всего

иностранный

весенний

34

34

32

32

культурология

весенний

17

17

34

16

32

48

математика

весенний

17

34

51

16

32

48

микроэкономика

весенний

17

34

51

16

32

48

статистика

весенний

17

34

51

16

32

48

предмет

семестр

лекции

практические

всего

лекции

практические

всего

иностранный

осенний

34

34

32

32

информатика

осенний

34

34

32

32

макроэкономика

осенний

17

17

34

16

32

48

математика

осенний

17

34

51

16

32

48

статистика

осенний

17

34

51

16

32

48

2. Постройте сводную таблицу для списка ЗАКАЗЫ (Лаб. № 3).

Лабораторная работа № 4. Технологии работы с внешними источниками данных.

Цель: научиться работать с  внешними источниками данных, проводить импорт данных из MS Access в MS Word и MS Excel.

Задачи:

  1. Слияние документов, где в качестве источника данных выступает MS Access.
  2. Расчет итогов в MS Excel на основе импортированных данных из MS Access.
  3. Создание сводных таблиц и диаграмм в MS Excel на основе данных из внешнего источника MS Access.

Задание 1. Создание серийных писем (приглашений). Источник данных – MS Access.

В практике делопроизводства часто возникает задача создания нескольких типовых документов, отличающихся друг от друга конкретными данными (имя, фамилия, адрес и т.д.). Например, необходимо разослать письмо-приглашение по нескольким адресам. Для этого создается шаблон письма, в который вставляются поля слияния из источника данных. В качестве источника данных может выступать документ Word, таблица или запрос MS Access, таблица MS Excel. Рассмотрим процедуру создания серийных писем, где данные будут взяты из БД «Борей».

  1. В текстовом процессоре Word откройте новый документ Файл/Создать/Шаблоны на моем компьютере. Перейдите на вкладку «Слияние» и выберите «Современное составное письмо».
  2. Загрузите панель инструментов «Слияние» (Вид/Панели инструментов/Слияние) (см. рис.1).

Рис. 1. Панель инструментов «Слияние».

  1. Откройте источник данных, используя пиктограмму  «Открыть источник данных». В качестве источника данных выберите БД «Борей» (….\ Microsoft Office\Office11\Samples\Борей.mdb).
  2. В окне «Выделить таблицу» (см. рис.2) выберите таблицу «Сотрудники».

Рис.2. Окно выбора таблиц БД «Борей».

  1. Сформируйте шаблон «Приглашение на семинар» по образцу (см. рис.4). Сохраните в своей папке под названием «Приглашение».
  2. Для добавления полей слияния используйте пиктограмму  «Вставить поля слияния». Выберите по очереди следующие поля слияния: Должность, Страна, Индекс, Город, Адрес, Фамилия, Имя (см. рис.3). Расположите и отформатируйте их согласно образцу.

Рис. 3. Добавление полей слияния в шаблон.

Рис.4. Шаблон «Приглашение на семинар».

  1. Для того чтобы обращение к сотруднику формировалось автоматически и корректно, воспользуйтесь полями слияния Word с помощью пиктограммы . Из раскрывающегося списка выберите оператор IfThenElse и сформируйте поле слияния согласно рис.5.

 

Рис.5. Вставка поля If.

  1. После того как шаблон отредактирован и вставлены поля слияния из таблицы «Сотрудники», необходимо провести слияние в новый документ, используя пиктограмму .
  2. В результате получится новый документ, состоящий из такого же количества страниц, сколько записей в таблице «Сотрудники». Сохраните получившийся документ в своей папке под названием «Письма».

Задание 2. Импорт данных в MS Excel.

  1. Откройте новую книгу MS Excel и сохраните в своей папке под именем «Импорт продаж».
  2. Для импорта данных из БД «Борей» выполните команду Данные/Импорт внешних данных/Импортировать данные. Откройте источник данных (….\ Microsoft Office\Office11\Samples\Борей.mdb).
  3. В окне «Выделить таблицу» выберите запрос «Продажи по типам».
  4. Поместите данные в ячейку А1 текущего листа (см. рис.6).

Рис. 6. Импорт данных.

  1. Отсортируйте столбец «Код типа» по возрастанию. Дайте листу имя Продажи по типам.
  2. Скопируйте всю таблицу на новый лист и дайте ему имя Продажи по категориям.
  3. Вернитесь на лист Продажи по типам  и подведите промежуточные итоги Продаж по типам, используя команду Данные/Итоги…(см. рис.7), предварительно выделив диапазон данных, включая имена столбцов.

Рис. 7. Промежуточные итоги.

  1. Сверните полученную структуру (см. рис.8).

Рис.8. Таблица итогов по полю КодТипа.

  1. Постройте объемную круговую диаграмму продаж по типам согласно образцу (см. рис.9).

 

Рис.9. Диаграмма «Объемы продаж по типам».

  1. Перейдите на лист Продажи по категориям и проанализируйте объемы продаж по категориям, следуя пунктам 5-8 задания 2.

Задание 3. Создание сводных таблиц на основе данных из внешнего источника.

  1. Откройте новую книгу MS Excel и сохраните в своей папке под именем «Сводная таблица».
  2. Выполните команду Данные/Сводные таблицы. В первом окне Мастера сводных таблиц и диаграмм установите опцию «Во внешнем источнике данных» (см. рис. 10).

Рис.10. Шаг 1. Мастер сводных таблиц и диаграмм.

  1. На втором шаге Мастера щелкните по кнопке «Получить данные».
  2. В окне «Выбор источника данных» выделите строку «База данных MS Access» и нажмите кнопку ОК (см. рис.11).

Рис. 11. Выбор источника данных.

  1. Далее укажите путь к БД «Борей» и нажмите кнопку ОК (см. рис.12).

Рис. 12. Выбор базы данных.

  1. В следующем окне разверните структуру запроса «Продажи товаров в 1997» и перетащите все столбцы в область запроса (см. рис.13). Нажмите кнопку Далее.

Рис. 13. Создание запроса: выбор столбцов.

  1. В окне Создание запроса: отбор данных нажмите также на кнопку Далее.
  2. В окне Создание запроса: порядок сортировки установите сортировку по полю Квартал Исполнения по возрастанию. Нажмите кнопку Далее.
  3. На заключительном шаге установите опцию «Вернуть данные в MS Excel и нажмите кнопку Готово (см. рис.14).

Рис. 14. Создание запроса: заключительный шаг.

  1. Если данные успешно получены, то вновь откроется окно 2-го шага Мастера сводных таблиц и диаграмм с сообщением, что данные получены. Нажмите кнопку Далее.
  2. Далее укажите месторасположение данных (на этом же листе в ячейке А1).
  3.  В результате успешного импорта откроется область для создания сводных таблиц (см. рис.15).

Рис. 15. Область формирования сводной таблицы.

  1. В область столбцов перетащите поле Квартал Исполнения, в область строк – поле Категория, в область данных – Продажи Товаров. В результате получится сводная таблица (см. рис. 16).

Рис. 16. Сводная таблица «Продаж товаров за 1997 год».

  1. По данным сводной таблицы постройте сводную диаграмму.

Лабораторная работа №5. Статистические функции Excel. Метод экспертных оценок.

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

Задачи:

  1. Прогноз спроса товаров.
  2. Метод экспертных оценок.

Задание №1. Прогноз спроса товаров, исходя из данных предыдущих периодов.

Предполагается, что имеются данные о покупке товаров за 2002, 2003 и 2004 годы. Необходимо спрогнозировать объем покупок на 2005 год.

Задание решается двумя способами:

  1. алгоритмическим способом, т.е. способом пошаговой экстраполяции;
  2. функциональным способом с использованием функций ТЕНДЕНЦИЯ и РОСТ.

Функция ТЕНДЕНЦИЯ аппроксимирует исходные данные по прямой, а функция РОСТ – по экспоненциальной кривой. Все данные, прогнозируемые алгоритмическим и функциональным способами, отобразите на графике.

Выполнение работы:

  1. В MS Excel создайте таблицу следующего вида и сохраните в папке с номером вашей группы под именем  Прогноз.

  1. Отформатируйте таблицу: для шапки таблицы установите полужирный шрифт, выравнивание по центру, светло-зеленый цвет фона, обрамление – все границы.
  2. В таблицу введите исходные данные о покупке товаров (не  менее 10 наименований) за 2002, 2003 и 2004 годы.
  3. Определите коэффициенты:

                 К1=(2003-2002)/2002;

                 К2=(2004-2003)/2003.

  1. Определите средний коэффициент:

                 К=(К1 + К2)/2

  1. Рассчитайте прогноз на 2005 год:

                 П=2004+2004*К

  1. Рассчитайте прогноз объема покупок функциональным способом. С помощью мастера вызовите функции ТЕНДЕНЦИЯ и РОСТ (категория Статистические).

                 ТЕНДЕНЦИЯ (ВХ:ВХ;;4),

                 РОСТ (ВХ:ВХ;;4), где ВХ:ВХ – диапазон исходных данных.

  1. Данные, прогнозируемые алгоритмическим и функциональным способами, отобразите на графике. Используйте тип диаграммы График, вид . Дайте диаграмме заголовок  – Прогноз спроса товаров, оси Х – Товар, оси YПрогноз.
  2. Диаграмма должна иметь приблизительно следующий вид:

  1. Проанализируйте полученные результаты.

Задание №2.  Определение степени влияния различных факторов на эффективность производства с использованием метода предпочтений.

Для решения таких задач предлагается использовать метод экспертных оценок. Этот метод применяется для решения задач, которые невозможно описать в виде каких-либо математических соотношений, например, в виде системы уравнений. Такие задачи называются неструктурированными.

В нашем случае будет использован один из методов экспертных оценок, а именно, метод предпочтений, суть которого описана далее.

Например, пусть требуется оценить влияние на рост производительности труда следующих факторов:

1) уровень профессиональной подготовки рабочих;

2) соблюдение технологической дисциплины;

3) эффективность материальных стимулов;

4) эффективность организации соревнования;

5) технологическое переоснащение.

В качестве экспертов, оценивающих влияние этих факторов на рост производительности труда, выступают следующие специалисты предприятия:

1) главный инженер;

2) главный экономист;

3) начальник ОТиЗ;

4) начальник одного из цехов.

   Количество оцениваемых факторов через  n  (n = 5).

  1. В MS Excel Создайте таблицу следующего вида (исходную матрицу оценок) и сохраните ее в своей папке под именем  Метод предпочтения:

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

  1. Отформатируйте таблицу по образцу: для ячеек A1:F1 и B2:F2 установите объединение, заголовок таблицы отцентрируйте и задайте для него полужирный шрифт размером 12. Ячейки A3:F3 отцентрируйте по вертикали.
  2. Присвойте Листу имя Исходная матрица.
  3. Далее произведите преобразование матрицы оценок по формуле:

                             Aji=  n - xji, где    j = 1...m;  i = 1...n.

 

                                   Например, A12 = 5-x12 = 5-1= 4. Для этого:

  1. Перейдите на новый лист и присвойте ему имя Преобразование.
  2. Скопируйте исходную матрицу на новый лист.
  3. Сделайте преобразование по приведенной выше формуле. В результате должна быть получена следующая матрица:

  1. Добавьте в таблицу следующие сведения:

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

                      m

             Сi =   Aji

                                  j=1

                                 

  1. Найдите сумму всех оценок  С по формуле:

                     n

             С =  Сi.

                                i=1

  1. Найдите веса факторов Vi  по формуле:  

     

                                           Vi = Ci/C

  1. Убедитесь, что полученные данные совпадают с данными, приведенными ниже:

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

Требуется также проверить,  насколько согласованными были мнения экспертов. Если мнения экспертов резко различаются, то, возможно, требуется  повторить их опрос и уточнить некоторые оценки.

Для проверки согласованности мнений экспертов вычисляется величина, называемая коэффициентом конкордации W. Вычислим коэффициент конкордации W.

  1. Перейдите на лист Исходная матрица и добавьте следующие сведения:

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

                      m

             Si =   xji

                                 j=1

  1. Найдите вспомогательную величину А по формуле:

                                                       A = m(n+1)/2

  1. Найдите  разность   Ri  по формуле:

                                                        Ri = Si - A 

  1. Найдите сумму квадратов разностей:

                    n       2

             S =  Ri

                               i=1

Используйте математическую функцию СУММКВ.

  1. Найдите коэффициент конкордации W по формуле:

                    12S

   W = --------------------

                m2 n(n2-1)

  1. Убедитесь, что получены следующие результаты:

  1. Сделайте анализ степени согласованности следующим образом. Коэффициент конкордации W изменяется от 0 до 1, причем его равенство 1 означает, что все эксперты дали одинаковые оценки по данному признаку Х, а равенство 0 означает, что связи между оценками, полученными от разных экспертов, не существует. При W > 0,5 степень согласованности экспертных оценок может считаться достаточной. При  W < 0,5 требуется уточнение экспертных оценок. В данном примере W = 0,7375 , значит,  уточнения оценок не требуется.

Задание №3.  

Возьмите  в качестве исходной матрицы оценок одну из ниже приведенных из вариантов значений матриц оценок. Причем, значения  оценок 4-ого эксперта проставьте по своему усмотрению. Решите задачу с применением метода предпочтения для получившейся исходной матрицы оценок.

Если в результате выполнения задания получится значение коэффициента конкордации W> 0,5, то задачу можно считать решенной, а если в результате выполнения задания получится значение коэффициента конкордации W< 0,5, это означает, что требуется уточнение экспертных оценок. В этом случае изменяйте значения 4-ого эксперта до тех пор, пока не получится значение коэффициента конкордации W> 0,5.

Варианты значений матрицы оценок.

Вариант № 1.

 Факторы

Эксперты

1

2

3

4

5

1

2

1

3

5

4

2

2

3

1

5

4

3

1

3

2

5

4

4

Вариант № 2.

 Факторы

Эксперты

1

2

3

4

5

1

4

1

5

2

3

2

2

3

4

5

1

3

2

3

5

1

4

4

Вариант № 3.

 Факторы

Эксперты

1

2

3

4

5

1

1

4

5

2

3

2

2

3

4

5

1

3

1

4

5

3

2

4

Вариант № 4.

 Факторы

Эксперты

1

2

3

4

5

1

2

5

3

2

4

2

2

3

1

5

3

3

1

5

2

3

4

4

Вариант № 5.

 Факторы

Эксперты

1

2

3

4

5

1

1

4

5

3

2

2

2

5

4

3

1

3

1

4

3

4

2

4

Вариант № 6.

 Факторы

Эксперты

1

2

3

4

5

1

1

5

4

3

2

2

2

4

5

3

1

3

3

4

5

1

2

4

Вариант № 7.

 Факторы

Эксперты

1

2

3

4

5

1

1

4

5

2

3

2

2

5

4

1

3

3

1

4

5

3

2

4

Вариант № 8.

 Факторы

Эксперты

1

2

3

4

5

1

1

4

5

3

2

2

3

4

5

2

1

3

1

3

5

4

2

4

Вариант № 9.

 Факторы

Эксперты

1

2

3

4

5

1

1

2

5

4

3

2

2

3

4

5

1

3

1

3

5

4

2

4

Вариант № 10.

 Факторы

Эксперты

1

2

3

4

5

1

1

4

5

2

3

2

2

5

4

3

5

3

1

4

5

3

2

4

Вариант № 11.

 Факторы

Эксперты

1

2

3

4

5

1

3

4

1

2

3

2

2

5

4

3

4

3

1

2

5

3

2

4

Вариант № 12.

 Факторы

Эксперты

1

2

3

4

5

1

5

4

5

3

3

2

4

5

4

2

1

3

3

4

5

3

2

4

Лабораторная работа №6.  Технологии финансовых расчетов в MS EXCEL.  

Цель: научиться использовать возможности MS Excel для проведения финансовых расчетов.

Задачи:

  1. Расчет будущей стоимости инвестиций.
  2. Вычисление ренты. Расчет сроков займов.
  3. Составление планов погашения займов.
  4. Расчет амортизационных отчислений линейным методом, накопленного износа и остаточной стоимости.

Среди встроенных функций MS Excel  есть несколько десятков финансовых функций. В большинстве случаев эти функции используются для проведения финансового анализа, но часто эти функции могут оказаться полезными и экономисту–менеджеру.

Так как некоторые финансовые функции Excel могут оказаться, недоступны в процессе работы, поэтому перед началом работы с финансовыми функциями рекомендуется установить надстройку Пакет анализа. Для этого выполните команду меню СервисНадстройки…. В диалоговом окне Надстройки установите флажок напротив строки Пакет анализа. В результате вам будут доступны все 54 финансовые функции Excel, которые ориентированы на решение задач, связанных с расчетами различных аннуитетов, амортизации, цены, доходности и других параметров ценных бумаг (облигаций, акций и т.п.), а также задач оценки эффективности инвестиционных проектов.

1. Расчет будущих стоимостей инвестиций.

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

Величина наращенной суммы при использовании простых процентов определяется по формуле:

                                                        S = P(l + rt).

В этой формуле примем, что  t = 1 год,   тогда S=Р(\ + r). Отношение S/P носит название «коэффициент наращения», здесь обозначено Р - предоставляемая сумма,  r – банковский процент, t - период времени пользования кредитом. В финансовых расчетах наряду с банковским процентом используется коэффициент дисконта d, связанный с банковским процентом формулой:

Сложные проценты начисляются c использованием формулы:

                                              S=P(1+r)t

При использовании финансовых функций необходимо учитывать, точку зрения кредитора и дебитора. Дебитор получает сумму Р, а в конце периода возвращает сумму S, знак «-» на рис 1. Наоборот  кредитор лишается суммы Р,  но в конце получает  сумму S, знак «+» на рис. 2.

     

           Рис.1.                                                                             Рис.2.

Время в финансовых функциях измеряется в периодах. Границы периодов — это моменты платежей. Период может составлять год, квартал, месяц, день. Обычно процентную ставку относят к фиксированному периоду (как правило, году).

Задача №1.

Выдан кредит в сумме 1 млн. долл. с 15.01.93 по 15.03.93 под 120% годовых. Рассчитать сумму погасительного платежа.

Решение.

  1.  Откройте новую книгу MS Excel и создайте таблицу согласно рис.3. Лист 1 переименуйте в Задача 1. В столбце D приведены формулы, которые необходимо ввести в ячейки В6:В8.

Рис. 3.

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

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

=БС(ставка; кпер; плт; пс; тип)

СТАВКА   — процентная ставка за период.

КПЕР    — это общее число периодов платежей по аннуитету.

ПЛТ — это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно ПЛТ состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если аргумент опущен, должно быть указано значение аргумента ПС.

ПС    — это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент ПС опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента ПЛТ.

ТИП   — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент ТИП опущен, то он полагается равным 0.

  1.  Введите в ячейку В9 формулу, рассчитывающую сумму возврата с использованием функции БС. Для этого выполните команду Вставка/Функция или щелкните по пиктограмме  .
  2.  Перейдите в категорию Финансовые и выберите необходимую функцию БС.
  3.  В диалоговом окне Аргументы функции установите необходимые значения (см. рис.4). Щелкните по кнопке ОК. Значение получилось отрицательное. Кредиты нужно возвращать!
  4.  Сохраните файл в своей папке под именем Финансовые расчеты.xls.

Рис. 4. Аргументы финансовой функции БС.

Задача №2.

Ссуда в 20 000 долл. дана на полтора года под ставку 28% годовых ежеквартальным начислением. Определить сумму конечного платежа.

Решение.

  1.  Перейдите на новый лист и переименуйте его в Задача 2. Подготовьте таблицу для расчетов согласно рис. 5.

Рис. 5.

  1.  В данной задаче  базовый период — квартал, поэтому срок ссуды (количество периодов) – 6. За период начисляется ставка 7% = 28% / 4. Тогда формула, дающая решение задачи, имеет вид: =БС(28%/4;6;;20000). Она возвращает результат:  -$30 014,61 .
  2.  Сохраните изменения в файле Финансовые расчеты.xls.

Задача №3.

Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100%  или на 6 месяцев под 110%. Как выгоднее вкладывать деньги на полгода: дважды на три месяца или один раз на 6 месяцев?

Решение.

  1.  Перейдите на новый лист и переименуйте его в Задача 3. Подготовьте таблицу для расчетов согласно рис. 6.

Рис.6.

  1.  Рассчитайте ставки за период для обоих вкладов в ячейках В4 и С4. Для первого вклада будет формула: =B2*B3/12 (100%*3/12), для второго: =C2*C3/12  (110%*6/12).
  2.  Рассчитайте, используя функцию БС накопленную сумму. Для первого вклада получим формулу: =БС(B4;2;;B5), где 2 – число платежей (мы вкладываем деньги дважды за полгода). Для второго: =БС(C4;1;;C5), где 1 – число платежей (мы вкладываем деньги только один раз).
  3.  Сравните полученные результаты (рис.7) и сделайте вывод.

Рис.7.

  1.  Сохраните изменения в файле Финансовые расчеты.xls.

Задача №4 (самостоятельно).

Рассчитать будущее значение вклада 1000 долл. через  1, 2, 3, 4, 5 лет при годовых процентных ставках 10%, 20%,..., 50%.  Дополнительные поступления и выплаты отсутствуют.

Примечание. Для расчетов создайте таблицу на новом листе Задача 4 согласно рис. 8. Для ячеек В5:В8 используйте процедуру копирования формулы. Сохраните изменения в файле Финансовые расчеты.xls.

Рис.8.

2. Вычисление ренты. Расчетов сроков вклада (займа).

Рассмотрим схему с многократными взносами или выплатами.

Поток платежей, все члены которого имеют одинаковую величину R и разделены равными промежутками времени, называют постоянной рентой.

 Один из возможных вариантов такого потока {-Р, -R, -R, ..., -R, S}, т.е. начальный взнос Р и последующие выплаты R дают в итоге S. Если платежи производятся в конце периодов, то ренту называют обыкновенной, или постнумерандо. Если же платежи происходят в начале периодов, то ренту называют пренумерандо.

Для расчетов используется формулы:

           

Р — современное значение.

S — будущее значение.

R — периодическая выплата.

r — процентная ставка за период.

n — количество периодов.

type — тип ренты, если type = 0 или опущен, то рента постнумерандо (выплата в конце периода), если type = 1, то рента пренумерандо (выплата в начале периода).

Задача №5.

На счет в банке вносится сумма 1000 долл. в течение 10 лет равными долями 1) в конце каждого года 2) в начале каждого года. Годовая ставка - 4%. Какая сумма будет на счете после 10 лет в обоих случаях?

Решение.

  1.  Перейдите новый лист и переименуйте его в Задача 5. Для проведения расчетов создайте таблицу согласно рис.  9.

Рис.9.

  1.  Если платежи осуществляются в конце периодов (рента постнумерандо), то тип = 0 (или его можно опустить). В этом случае формула для расчета накопленной суммы будет: =БС(B2;B3;B4;;B5) или  = БС( 4%;10; -1000), где ПЛТ (выплата за каждый период)=-1000 $.
  2.  Если же сумма вносится в начале года (рента пренумерандо), то формула принимает вид: =БС(C2;C3;C4;;C5) или = БС( 4%;10; -1000; ;1).
  3.  Сравните полученные результаты и сделайте вывод.

  1.  Сохраните изменения в файле Финансовые расчеты.xls.

Задача №6 (самостоятельно).

Рассматриваются две схемы вложения денег на 3 года: в начале каждого года под 24% годовых или в конце каждого года под 36%. Ежегодно вносится по 4000 долларов. Какая схема выгоднее?

Примечание. Для расчетов создайте на новом листе Задача 6 таблицу согласно рис. 10.

Рис.10.

Сравните полученные результаты и сделайте вывод. Сохраните изменения в файле Финансовые расчеты.xls.

Задача №7.

За какой срок в годах сумма, равная 75 000 долл., достигнет 200 000 долл. при начислении процентов по сложной ставке 15% раз в году и поквартально.

Решение. 

  1.  Перейдите новый лист и переименуйте его в Задача 7. Для проведения расчетов создайте таблицу согласно рис. 11.

Рис. 11.

  1. Ставку за квартал в ячейке С3 рассчитайте самостоятельно.
  2. Для расчета срока вклада воспользуемся новой финансовой функцией КПЕР, которая возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

Синтаксис функции КПЕР.

КПЕР(ставка; плт; пс; бс; тип)

СТАВКА— процентная ставка за период.

ПЛТ — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.

ПС — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

БС— требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0.

Тип — число 0 или 1, обозначающее, когда должна производиться выплата.

  1. Рассчитаем срок вклада, если начисление процентов производится в конце каждого года. Для этого в ячейку В5 введите формулу: =КПЕР(B3;;B2;B4) или =КПЕР(15%;;-75000;200000). В данном случае аргумент ПЛТ опущен, т.к. не производится никаких дополнительных вкладов.
  2. Аналогичным образом в ячейке С5 рассчитайте срок вклада, если начисление процентов производится по кварталам. Обратите внимание, что в данном случае результатом расчета будет количество кварталов. Поэтому полученный результат необходимо разделить на 4.
  3. Сравните полученные результаты и сделайте вывод.

  1. Сохраните изменения в файле Финансовые расчеты.xls.

Задача №8 (самостоятельно).

Ссуда 63 200 руб., выданная под 32% годовых, погашается ежеквартальными платежами по 8400 руб. Рассчитайте срок погашения ссуды.

Примечание. Для расчетов создайте на новом листе Задача 8 таблицу согласно рис. 12.

Рис.12.

Сохраните изменения в файле Финансовые расчеты.xls.

3. Составление планов погашения займа.

Задача №9.

Банк выдал долгосрочный кредит в сумме 40 000 долл. на 5 лет под 6% годовых. Погашение кредита должно производиться равными ежегодными выплатами в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в год. Составить план погашения займа.

Решение.

  1.  Перейдите новый лист и переименуйте его в Задача 9. Для проведения расчетов создайте таблицу согласно рис. 13.

Рис. 13.

  1.  Для расчета платежей по процентам воспользуемся финансовой функцией ПРПЛТ, которая возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.

Синтаксис функции ПРПЛТ:

ПРПЛТ(ставка; период; кпер; пс; бс; тип)

СТАВКА — процентная ставка за период.

ПЕРИОД — это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до КПЕР.

КПЕР — общее число периодов платежей по аннуитету.

ПС — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

БС — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0.

ТИП — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент ТИП опущен, то он полагается равным 0.

  1.  Рассчитайте платеж по процентам за первый год. Для этого в ячейку В7 введите формулу: =ПРПЛТ($B$3;A7;$B$2;$B$1).
  2. Скопируйте формулу на диапазон ячеек В7:В11.
  3. Для расчета платежей по основному долгу воспользуемся финансовой функцией ОСПЛТ, которая возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.

Синтаксис функции ОСПЛТ:

ОСПЛТ(ставка; период; кпер; пс; бс; тип)

СТАВКА — процентная ставка за период.

ПЕРИОД — задает период, значение должно быть в интервале от 1 до КПЕР.

КПЕР — общее число периодов платежей по аннуитету.

ПС — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

БС — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0.

ТИП— число 0 или 1, обозначающее, когда должна производиться выплата.

  1.  Рассчитайте платеж по основному долгу за первый год. Для этого в ячейку С7 введите формулу: =ОСПЛТ($B$3;A7;$B$2;$B$1)
  2. Скопируйте формулу на диапазон ячеек С7:С11.
  3. Для расчета годовых выплат воспользуемся финансовой функцией ПЛТ, которая возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Синтаксис функции ПЛТ:

ПЛТ(ставка; кпер; пс; бс; тип)

СТАВКА  — процентная ставка по ссуде.

КПЕР— общее число выплат по ссуде.

ПС — приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.

БС — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0.

ТИП — число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.

  1.  Рассчитайте общий платеж за первый год. Для этого в ячейку D7 введите формулу: =ПЛТ($B$3;$B$2;$B$1). Для расчета общего платежа можно просто просуммировать значения ячеек В7 и С7 (=СУММ(B7:D7)).
  2. Скопируйте формулу на диапазон ячеек D7:D11.
  3. Для расчета остатка  долга за первый год в ячейку E7 введите формулу: =$B$1+C7, далее в ячейку E8 введите формулу: =E7+C8 и скопируйте ее на диапазон ячеек Е9:Е11.
  4. Рассчитайте итоговые значения в ячейках B12, C12, D12.
  5.  Сравните полученные результаты с рис. 14

Рис.14.

  1.  Сохраните изменения в файле Финансовые расчеты.xls.

Задача №10 (самостоятельно).

Выдан кредит на 10 лет в размере 10000000 руб. под 11% годовых. Погашение кредита равными долями в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в год. Составить план погашения займа.

Примечание. Для расчетов создайте на новом листе Задача 10 таблицу, аналогичную предыдущей задаче. Проведите расчеты и сохраните изменения в файле Финансовые расчеты.xls.

Задача №11.

Покупатель взял в кредит 20 тыс. руб. на 12 месяцев под 25% годовых. Кредит погашается в течение года равными выплатами в конце каждого месяца. Рассчитать величину каждой выплаты, а также суммы, идущие на погашение основного долга и платежи за пользование кредитом. 

Уплата в погашение кредита =Платежи в погашение основного долга + Проценты на оставшуюся сумму долга.

Решение.

  1.  На новом листе Задача 11 создайте таблицу согласно рис. 15.
  2.   Рассчитайте месячную ставку в ячейке С4 самостоятельно.
  3. В ячейку В7 введите формулу = - ОСПЛТ($C$4;A7;$C$3;$C$1)
  4. В ячейку С7 введите формулу = - ПРПЛТ($C$4;A7;$C$3;$C$1)

Рис.15.

  1. В ячейку D7 введите формулу самостоятельно, используя функцию ПЛТ.
  2. В ячейку Е7 введите формулу = - ОБЩДОХОД($C$4;$C$3;$C$1;$A$7;A7;0). 
  3. В ячейку F7 введите формулу = - ОБЩПЛАТ($C$4;$C$3;$C$1;$A$7;A7;0)
  4. В ячейку G7 введите формулу самостоятельно.
  5. Скопируйте эти формулы в соответствующие ячейки.
  6. Рассчитайте итоговые значения в ячейках В18, С18, D18.
  7. Сравните полученные результаты с рис. 16.

 

Рис.16.

  1.  Сохраните изменения в файле Финансовые расчеты.xls.

4. Расчет амортизационных отчислений линейным методом, накопленного износа и остаточной стоимости.

Задача №12.

Первоначальная стоимость объекта 60000 руб. Срок полезного использования – 2 года. Объект вводится в эксплуатацию 1 мая 2004 года. Рассчитать норму амортизации, суммы амортизационных отчислений линейным методом, накопленный износ и остаточную стоимость по месяцам.

Решение.

  1. На новом листе Задача 12 создайте таблицу согласно рис. 15. В диапазоны ячеек  Е3:Е4, С7:Е18 и G7:I18 данные пока вводить не надо.
  2. Чтобы ввести названия месяцев, в ячейку В7 введите Январь, а затем, нажав левую кнопку мыши, «протащите» курсор по ячейкам В7:В18.

Рис. 15.

  1. При форматировании ячеек В6 и F6 воспользуйтесь командой меню Формат/ Ячейки/Граница. Для слова Год используйте надстрочное начертание (Формат/ Ячейки/Шрифт), для слова Месяц подстрочное. 
  2.  В ячейку Е3 самостоятельно введите формулу для расчета нормы амортизации за один месяц. Норма амортизации рассчитывается по формуле , где n – срок полезного использования в месяцах.
  3. В ячейке Е4 для расчета величины амортизационных отчислений за месяц используйте функцию АПЛ. Задайте аргументы: Стоимость- $Е$1, Остаток -0, Период - $Е$2.
  4. В ячейку С12 введите формулу =$E$4, а в ячейку С13 введите формулу =C12+$E$4. Скопируйте формулу из ячейки С13 в ячейки С14:С18.
  5. В ячейку D7 введите формулу =C18+$E$4, а в ячейку D8 введите =D7+$E$4. Скопируйте формулу из ячейки D8 в ячейки D9:D18.
  6. В ячейки Е7:Е11 скопируйте формулы из ячеек D7:D11.
  7. В ячейки С7:С11 и Е12:Е18 введите 0.
  8. Выделите диапазон ячеек С7:Е18 и задайте денежный формат данных (кнопка Денежный формат ).
  9. В ячейку G11 введите формулу =$E$1–C11, а затем скопируйте эту формулу в соответствующие ячейки.
  10. В ячейку Н11 введите формулу =$E$1-D7 и скопируйте ее на диапазон ячеек Н8:Н18.
  11. В ячейку I11 введите формулу =$E$1-E7 и скопируйте ее на диапазон ячеек I8:I10.
  12. В ячейки I11:I18 введите 0.
  13.  Сохраните изменения в файле Финансовые расчеты.xls.

Лабораторная работа №7.  Технологии статистических расчетов в MS EXCEL.  

Цель: научиться использовать возможности MS Excel для проведения статистических  расчетов.

Задачи:

  1.  Расчет коэффициента корреляции Пирсона и t-статистики Стьюдента.
  2.  Построение модели регрессии различными способами.
  3.  Выбор наиболее точной модели связи между двумя величинами.

1. Параметрический корреляционный анализ.

Одна из наиболее распространенных задач статистического исследования состоит в изучении связи между выборками. Обычно связь между выборками носит не функциональный, а вероятностный (или стохастический) характер. В этом случае нет строгой, однозначной зависимости между величинами. При изучении стохастических зависимостей различают корреляцию и регрессию.

Корреляционный анализ состоит в определении степени связи между двумя случайными величинами X и Y. В качестве меры такой связи используется коэффициент корреляции. Коэффициент корреляции оценивается по выборке объема п связанных пар наблюдений (xi, yi) из совместной генеральной совокупности X и Y. Существует несколько типов коэффициентов корреляции, применение которых зависит от измерения (способа шкалирования) величин X и Y.

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

1. Линейный коэффициент корреляции параметр, который характеризует степень линейной взаимосвязи между двумя выборками, рассчитывается по формуле:

 

где  хi — значения, принимаемые в выборке X,

yi — значения, принимаемые в выборке Y;

— средняя по X, — средняя по Y.

Коэффициент корреляции изменяется от -1 до 1. Когда при расчете получается величина большая +1 или меньшая -1 — следовательно, произошла ошибка в вычислениях.  При значении 0 линейной зависимости между двумя выборками нет.

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

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

Теснота связи и величина коэффициента корреляции.

Коэффициент корреляции rxy

Теснота связи

+ 0,91-1,0

Очень сильная

+ 0,81-0,9

Весьма сильная

+ 0,65-0,8

Сильная

+ 0,45-0,64

Умеренная

+ 0,25-0,44

Слабая

До + 0,25

Очень слабая

«+» - прямая зависимость

              «-» - обратная зависимость

2. t-статистика Стьюдента.

Для того чтобы оценить наличие связи между двумя переменными, также можно использовать t-статистику Стьюдента, которая оценивает отношение величины линейного коэффициента корреляции к среднему квадратическому отклонению и рассчитывается по формуле

Полученную величину tрасч  сравнивают с табличным значением t-критерия Стьюдента с n-2 степенями свободы. Если tрасч > tтабл, то практически невероятно, что найденное значение обусловлено только случайными совпадениями величин X и Y d в выборке из генеральной совокупности, т.е. существует зависимость между X и Y. И наоборот, если tрасч < tтабл , то величины X и Y независимы.

3. Регрессионный анализ.

Цель регрессионного анализа – определить количественные связи между зависимыми случайными величинами. Одна из этих величин полагается зависимой и называется откликом, другие – независимые, называются факторами. Для установления степени зависимости между откликом и факторами используются вычисляемые величины ковариации и коэффициент корреляции. Если коэффициент корреляции по абсолютной величине близок к единице, то для построения зависимости используется линейная модель. Для других случаев используются более сложные нелинейные модели.

Уравнение линейной регрессии имеет вид:

Y=a1X1 + a2X2 + …+ akXk, где а1, а2… аk – параметры, подлежащие определению методом наименьших квадратов (МНК). В среде MS Excel для этого используется встроенная функция ЛИНЕЙН и инструмент Регрессия из Пакета анализа.

Задание 1. Исследование связей между двумя исследуемыми признаками.

Условие задачи: По 20 туристическим фирмам были установлены затраты на рекламную кампанию и количество туристов, воспользовавшихся после ее проведения услугами каждой фирмы. Определить коэффициент корреляции между исследуемыми признаками.

Ход выполнения:

  1.  Откройте новую книгу MS Excel и создайте таблицу согласно рис. 1:

Рис.1.

  1.  Рассчитайте в ячейке С23 коэффициент корреляции, используя функцию КОРРЕЛ из категории Статистические. Синтаксис функции:

КОРРЕЛ (массив1 ; массив 2):

где массив1 – ссылка на диапазон ячеек первой выборки (X);

массив2 – ссылка на диапазон ячеек второй выборки (Y).

     В нашей задаче формула будет иметь вид: =КОРРЕЛ(B2:B21;C2:C21)

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

  1.  Оцените значимость коэффициента корреляции. С этой целью рассматриваются две гипотезы. Основная Н0: xy=0 и  альтернативная Н1: xy≠0. Для проверки гипотезы Н0  рассчитайте t-статистику Стьюдента по формуле, указанной выше в ячейке С24. В нашем случае число степеней свободы ν = n-2=20-2 = 18 и формула будет следующей: =C23*КОРЕНЬ(20-2)/КОРЕНЬ(1-(C23*C23))
  2.  Сравните полученное значение с критическим значением tν,α распределения Стьюдента. (При ν =18 и доверительной вероятности α = 0,05, tν,α,табл = 1,734).  Сделайте вывод о наличии связи между исследуемыми величинами.

Задание 2. Построение регрессионной модели.

1-й способ. Функция ЛИНЕЙН.

  1.  В первом способе для получения коэффициентов   а и b линейного  уравнения регрессии Y=а*X+b, описывающего  зависимость  количества привлеченных туристов от затрат на рекламу воспользуемся статистической функцией ЛИНЕЙН.  Для  этого  выделите  две  ячейки  C26:D26 и выполните вставку функции ЛИНЕЙН с  аргументами согласно рис.2.   Здесь Известные_значения_y – диапазон значений Количество туристов, Известные_значения_x – диапазон значений Затраты на рекламу. Нажмите  комбинацию  клавиш  SHIFT+CTRL+ENTER.  

Рис. 2. Аргументы функции ЛИНЕЙН.

  1.  В  ячейку  D27 введите  уравнение   Y= a*X+b  (вместо  a  и  b  подставьте полученные коэффициенты линейной регрессии).  

2-й способ (графический). Построение линии тренда.

  1.  Для получения уравнения  регрессии построим корреляционное поле переменных X (затраты на рекламу) и Y (количество туристов).
  2.  Выделите диапазон ячеек В2:С21, запустите мастера диаграмм и выберите тип диаграммы – Точечная. Задайте для диаграммы имя – Корреляционное поле, ось Х – Затраты на рекламу, ось YКоличество туристов. На последнем шаге мастера укажите место расположения – отдельный лист.
  3.  Добавьте  линию  тренда на точечный график.  Для  этого  необходимо выделить диаграмму и выполнить команду меню Диаграмма /Добавить  линию  тренда, либо выполнить данную команду из контекстного меню, щелкнув по любой точке графика. Линия тренда – графическое представление направления изменения ряда данных
  4.  Выберите  тип  тренда  Линейный, который используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением: y = ax +b, где a — угол наклона и b — координата пересечения оси абсцисс.
  5.  На вкладке Параметры установите флажки  Показать уравнение  на диаграмме и  Поместить  на  диаграмму  величину  достоверности аппроксимации .  Щелкните по кнопке  ОК.  - это число от 0 до 1, которое отражает близость линии тренда к фактическим данным. Линия тренда наиболее соответствует действительности, когда значение близко к 1.
  6.  Сравните уравнение регрессии, полученное графическим методом (рис. 3), с уравнением, рассчитанным с помощью функции ЛИНЕЙН.

Рис.3.

 3-й способ. Инструмент анализа Регрессия.

  1.  Сначала убедитесь,  что  был  активизирован  Пакет  анализа, т.е.  в  меню Сервис  есть  команда  Анализ  данных.  Если  нет,  то выполните  команду  Сервис/Надстройки. В  диалоговом  окне  Надстройки установите  флажок  Пакет  анализа  и щелкните  по  кнопке  ОК.  
  2.  Далее  выполните  команду Сервис/Анализ  данных.  Выберите  инструмент  анализа  Регрессия  из  списка  Инструменты  анализа.  Щелкните  по  кнопке  ОК.
  3.  На экране  появится  диалоговое  окно Регрессия (рис.4).
  4. в  текстовом поле  ВХОДНОЙ  ИНТЕРВАЛ  Y  введите  диапазон  со  значениями зависимой  переменной  $C$2:$C$21.
  5. в текстовом  поле  ВХОДНОЙ  ИНТЕРВАЛ  Х  введите  диапазон  со  значениями независимых  переменных  $В$2:$В$21.
  6. Убедитесь,  что  в  поле  Уровень  надежности введено  95 % и  переключатель Параметры  вывода  установлен  в  положении  Новый  рабочий  лист.
  7. Щелкните  по  кнопке  ОК.

Рис. 4 Диалоговое окно инструмента анализа Регрессия.

  1.  В результате на новом листе будет отображены результаты использования инструмента Регрессия (рис.5).

Рис. 5. Вывод итогов инструмента Регрессия.

  1.  Среди  полученных  результатов  после  применения  инструмента  Регрессия  есть столбец «Коэффициенты»,  содержащий  значение  b  в  строке «Y-пересечение», а – в строке  «Переменная  Х1».
  2.  Сравните полученные результаты с ранее рассчитанными коэффициентами a и b.
  3.  Обратите  также  внимание  на  следующие  показатели:

Столбец df  -  число  степеней  свободы  (используется  при  проверке  адекватности модели  по  статистическим  таблицам):

  1.  в  строке  Регрессия находится  – количество  коэффициентов  уравнения,  не  считая  свободного  члена  b;
  2.  в  строке  Остаток находится =n--1,  где  n – количество  исходных  данных.

        Столбец SS (сумма квадратов):  

  1.  в  строке  Регрессия:  SS=,
    где  
     -  модельные  значения  Y,  полученные  путем  подстановки  значений  Х  в  построенную  модель;   - среднее  значение  Y;
  2.   в  строке  Остаток:  .

       Столбец MS  -  вспомогательные  величины:       

  1.   в  строке  Регрессия:  ;  
  2.  в  строке  Остаток:  .

Столбец F  -  критерий  Фишера.  Используется  для  проверки  адекватности  модели:
                 .

Столбец Значимость  F  -  оценка  адекватности  построенной  модели.  Находится  по  значениям  F, и  с  помощью  функции  FРАСП.  Если  Значимость  F  меньше  0,05,  то  модель  может  считаться  адекватной  с  вероятностью  0,95.

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

Р - величина  -  оценка  значимости  коэффициентов  модели.  Если  Р - величина  меньше  0,05,  то  с  вероятностью  0,95  можно  считать,  что  соответствующий  коэффициент модели  значим  (т.е.  его  нельзя  считать  равным  нулю  и  Y  значимо  зависит  от  соответствующего  Х).

Нижние  и  верхние  95 - доверительные  интервалы  для  коэффициентов  модели.

Задание 3. Выбор наиболее точной модели связи.

Условие задачи. Исследуется  зависимость  дозы  облучения  от  толщины  слоя  защитного  материала.  Имеются  результаты  10  экспериментов  (см. рис.6).

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

  1.  Y=A0 + A1*X  (линейная  модель);
  2.  Y=A0*  (степенная  модель);
  3.  Y=A0+A1/X  (гиперболическая  модель).

Выберите  наиболее  точную  модель  и  определите  ее  коэффициенты.


Рис.6 Исходные данные.

  1.  Создайте на новом листе таблицу согласно рис. 6.
  2.  Постройте на этом же листе точечную диаграмму зависимости Y=f(X).
  3.  Нанесите на нее линейный и степенной тренды с уравнениями и величиной
    достоверности аппроксимации  (
    ).
  4.  Для построения гиперболической модели преобразуйте модель в линейную, получив в ячейках С2:С11 величину 1/Х. А в ячейку С1 введите заглавие: «Величина U=1/X».
  5.  Используя функцию ЛИНЕЙН, получите в ячейках А14:В14 коэффициенты уравнения m1 и b (т.е. уравнение Y= b+m1*U).
  6.  В ячейку A16 введите заголовок «Гиперболическая модель». В ячейку A17 введите уравнение Y= b+m*x (вместо b и m укажите конкретные числа).
  7.  Для построенной гиперболической модели найдите величину достоверности
    аппроксимации. Для этого найдите сначала среднее значение
     c помощью функции СРЗНАЧ в ячейке D2. В ячейку D1 введите заглавие «Ср. знач. Y».
  8.  В столбце E2:E11 получите модельные значения путем подстановки значений U из блока ячеек С2:С11 в построенную модель. Для этого в ячейку E2 введите формулу =$B$14+$A$14*C2. Скопируйте формулу вниз в смежные ячейки.  В ячейку E1 введите заголовок: «Модельные значения Y».
  9.  Найдите сумму квадратов , скорректированную на среднее:. Для  этого в столбце F2:F11 получите разность . В ячейку F1 введите заголовок: «Yi-Ycp.».
  10.  В столбце  G2:G11  получите квадраты разностей, а в ячейку G1 введите заголовок: «(».
  11.  В ячейке Н2 получите итоговую сумму, а  в ячейку Н1 введите заголовок: «SSy».
  12.  Аналогичным образом найдите сумму квадратов прогнозируемых (модельных) значений, скорректированную  на  среднее . Для  этого используйте столбцы I, J, K.
  13.  Найдите величину достоверности аппроксимации: в ячейке L2.
  14.  По значениям коэффициентов достоверности аппроксимации выберите наиболее  точную модель, которая соответствует максимальному коэффициенту достоверности.
  15.  Копия экрана Задания 3. приведена на рис. 7.

Рис. 7. Расчеты гиперболической модели.

  1.  Проверьте правильность вычислений, воспользовавшись инструментом анализа Регрессия.

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

1. Имеются данные по двум экономическим показателям  X   и Y. Необходимо:

  1. Вычислить коэффициент корреляции.
  2. Построить корреляционное поле.
  3. Построить регрессионную модель (с использованием функции ЛИНЕЙН).

Цена (X)

997

987

1002

1012

1011

1017

978

997

1010

989

Спрос (Y) 

120

140

115

100

100

90

150

130

95

155

2. Установить, зависит ли количество посетителей музея и  посетителей парка от  числа ясных дней за определенный период. Для этого:

  1. Вычислить коэффициенты корреляции.
  2. Построить корреляционное поле.
  3. Построить регрессионную модель (графическим способом и с помощью инструмента Регрессия).

Число ясных дней (Х)

8

14

20

25

20

15

Количество посетителей музея (Y)

495

503

380

305

348

465

Количество посетителей парка (Y)

132

348

643

865

743

541

Лабораторная работа №8.  Сетевое проектирование средствами MS Excel и MS Project.   

Цель: изучить возможности по использованию MS Excel и MS Project для решения задач планирования экономических процессов.

Задачи:

  1. Научиться строить сетевой график выполнения проекта и определять критический путь средствами MS Excel.
  2. Изучить возможности MS Project как средства управления проектами.

В настоящее время система сетевого планирования и управления (СПУ) является одним из эффективных методов по организации и управлению проектами. Система СПУ позволяет:

  1. Формировать календарные планы реализации проектов;
  2. Определять наиболее проблемные операции при реализации проектов;
  3. Выявлять резервы времени, трудовые, материальные и финансовые ресурсы.

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

 При составлении проекта работ  выделено 8 событий: (0,1,2,3,4,5,6,7),  которые связаны работами (i – j ), где i,j  0,1,2,3…,7 и i  ≠ j, например, событие 1 связано с событием 2 работой (1-2).

Исходные данные по продолжительности работ

Работа

0-1

0-2

0-3

1-2

1-3

1-4

2-3

2-4

2-5

3-4

3-5

4-5

4-6

5-6

5-7

6-7

Длит. дни

8

12

10

8

10

4

10

6

8

12

5

8

6

6

7

5

Требуется:

  1.  Построить сетевой график выполнения проекта.
  2.  Определить критический путь.

Ход выполнения:

Данная задача относится к классу задач сетевого планирования и решается методами булева программирования.

Задание 1. Построение сетевого графика выполнения проекта.

События на сетевом графике (или как говорят на графе) изображаются кружками (вершинами графа), а работы – стрелками (ориентированными дугами), показывающими связь между работами.

Так как исходные данные представлены работами, то из их анализа видно, что процесс начинается событием Ѕ0 и заканчивается событием Ѕ7. Все остальные события являются промежуточными.

Нарисуем график процесса, размещая события в последовательности: событие Ѕ0 – крайне левое, Ѕ7 – крайнее правое, если событие имеет номер i≤j ,то оно изображается левее, любые события связываются одной стрелкой. С каждой стрелкой свяжем число, продолжительность работы.

Рис.1. Сетевой график проекта.

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

Задание 2. Определение  критического пути в MS Excel.

С  сетевым графиком связана таблица, которая называется матрицей инцидентностей. 

Рис. 2. Матрица инцидентностей.

Она строится следующим образом: столбцы соответствуют работам, а строки событиям. Если для дуги (i - j)  начало соответствует  i, а конец  дуги соответствует  j , то элемент матрицы в строке i будет равен  -1, в строке j равен 1, а все другие элементы столбца равны 0.

  1. Откройте новую книгу MS Excel и сохраните в своей папке под именем Сетевое проектирование.xls.
  2. Переименуйте Лист1 в лист Матрица инцидентностей.
  3. Для обеспечения проверки вводимых значений в диапазон ячеек B3:Q10 создайте список подстановки. Для этого:
  4. Выделите диапазон ячеек.
  5. Выполните команду Данные/Проверка… 
  6. В окне Проверка вводимых значений на вкладке Параметры задайте Тип данных Список. 
  7. В поле Источник введите значения: -1;1 
  8. В диапазон ячеек A11:Q11 введите продолжительность работ.

Путь – любая последовательность работ, в которой конечное событие каждой работы совпадает с начальным событием следующей за ней работы. Среди различных путей сетевого графика наибольший интерес представляет полный путь L – любой путь, начало которого совпадает с исходным событием сети, а конец – с завершающим.

Полными путями являются пути:

Ѕ0 Ѕ3 Ѕ5 Ѕ7  продолжительность его 22 ед. 

Ѕ0 Ѕ2 Ѕ3 Ѕ4 Ѕ6 Ѕ7 продолжительность 45 ед.

Критический путь имеет максимальную продолжительность.

Для вычисления критического пути введем переменные хi = 0, если ребро не принадлежит пути и  хi =1, если принадлежит. Такие переменные называются булевыми или двоичными.

Рассмотрим функцию U(хi)= , где Ti – исходные значения продолжительности работ.

По условию эта функция для критического пути должна быть максимальной. Построим систему ограничений. Все ограничения имеют вид:

 ,

где bj = -1 – для начальной вершины,

      bj = 1 – для конечной вершины,

      bj = 0 для всех промежуточных вершин,

      aij – элементы строки матрицы инцидентностей

Для начального события Ѕ0 (вершина,  исходящая для всех путей): 123= -1

Для первого события  Ѕ1: х14- х5- х6=0

Для второго события Ѕ2:  х24- х78 –х9=0       

Для третьего события Ѕ3:  х357- х1011=0  

Для четвертого   события Ѕ4: х68 101213=0

Для пятого события Ѕ5: х911121415=0

Для шестого события Ѕ6: х131416=0

Для седьмого события  Ѕ7 (завершающего) х15 16=1

Начальные значения всех переменных примем равными 1.

Составим модель для поиска критического пути: Для этого:

  1. В строке 12 введите переменные xi, равные 1.
  2. В столбце R рассчитайте , воспользовавшись функцией СУММПРОИЗ.
  3. В столбец S введите ограничения bj, учитывая, что bj = -1 – для начальной вершины, bj = 1 – для конечной вершины, bj = 0 для всех промежуточных вершин.
  4. В ячейке R11 рассчитайте .
  5. Сравните полученный результат с рисунком 3.

Рис. 3. Матрица инцидентностей.

  1. Для того, чтобы рассчитать критический путь (максимальную продолжительность проекта), воспользуйтесь возможностями MS Excel по поиску решений. Для этого:
  2. Выполните команду Сервис/Поиск решений (Если данный модуль отсутствует, то предварительно установите его, выполнив команду Сервис/Надстройки/Поиск решения).
  3. В диалоговом окне Поиск решения установите параметры поиска решения согласно рис.4.
  4. Установите параметры модели – Линейная и Неотрицательные значения, щелкнув по кнопке [Параметры] диалогового окна Поиск решения.

Рис. 4. Диалоговое окно Поиск решения.

             Где: целевая ячейка – $R$11 (сумма произведений Ti xi).

                                  изменяемые ячейки – $B$12:$Q$12 (переменные хi ).

                                  ограничения – ячейки столбца Σaijxi= bj, а также $B$12:$Q$12 = двоичное.

  1. Установите параметры модели – Линейная и Неотрицательные значения, щелкнув по кнопке [Параметры] диалогового окна Поиск решения.
  2. Щелкните по кнопке [Выполнить] и в окне Результат поиска решения установите опцию «Сохранить найденное значение» и выберите Тип отчета – Результаты.
  3.  По результатам поиска определите критический путь и сравните с рис. 5.

Рис. 5. Результат поиска решения.

Значение целевой функции равно 57 ед.

Таким образом, критический путь включает работы Р01Р12 Р23 Р 34 Р 45 Р 56Р67.

Этот путь  или подпроцесс имеет максимальную продолжительность, и работы находящиеся на нем не имеют ни каких резервов времени. Критический путь определяет полное время завершения всех работ.


Задание 3. Построение сетевого графика и определение критического пути в
MS Project.

Программа MS Project предназначена для создания и управления графиками выполнения проектов на основе технологий сетевого планирования.

Окно системы приведено на рис. 6.

Рис. 6. Окно MS Project.

Слева расположена Панель консультанта (Вид /Панель инструментов/Консультант).

В рабочей области находится Диаграмма Ганта (Вид/Диаграмма Ганта), которая состоит из Панели для ввода задач (работ) и  Панели протяженности работ (диаграмма выполнения проекта)

Рассмотрим выполнение проекта представленного выше.

Ход выполнения:

  1. Установите дату начала выполнения проекта, выполнив команду Проект/Сведения о проекте, согласно рис. 7.

Рис. 7. Сведения о проекте.

  1. Введите названия задач и их продолжительность (исходные данные) согласно рис 8. Столбцы Начало и Окончание заполнятся автоматически, столбец Предшественники  заполняется перечислениями через точку с запятой номерами предшествующих работ.

Рис. 8. Область задач диаграммы Ганта.

  1. В крайнем правом столбце отражается диаграмма выполнения работ (рис. 9).

Рис. 9. Фрагмент диаграммы Ганта.

  1. Рассмотрите сетевой график (Вид/Сетевой график). Работы критического пути отражены на сетевом графике красным цветом, но можно их рассмотреть отдельно, выполнив команду Проект/Фильтр /Критические задачи.
  2. Определите, какова продолжительность критического пути, и какие работы он в себя включает. Сравните полученный результат с расчетами, проведенными в MS Excel. Для определения сроков выполнения проекта можно также выполнить команду Проект/Сведения о проекте (в диалоговом окне кнопка Статистика.)
  3. Для того чтобы уточнить продолжительность рабочей недели, выберите на панели пункт меню Задачи , и далее пункт «Определение рабочего времени проекта».
  4. На первом шаге мастера укажите шаблон календаря «Стандартный».
  5.   На втором шаге мастера укажите рабочие дни проекта.
  6. На третьем шаге мастера выберите пункт «Изменить рабочее время» и сделайте нерабочими днями 7.11.08, 25.12.08, 26.12.08, 01.01.09, 02.01.09, 07.01.09. Для дней 20.12.08 и 10.01.09 установите опцию Нестандартное рабочее время.
  7. На четвертом шаге мастера определите единицы времени.
  8. На пятом шаге сохраните внесенные изменения.
  9. Для того чтобы связать с сетевым графиком ресурсы (например, работников и их зарплату), выберите на панели пункт меню Ресурсы, и  далее перейдите по ссылке «Выбор людей и оборудования для проекта», на следующем шаге выберите опцию «Ввести ресурсы вручную». Создайте каталог трудовых ресурсов согласно рис.10. (Для всех ресурсов в диалоговом окне «Сведения о ресурсе» на вкладке «Общие» укажите тип ресурса «Трудовой»)

Рис. 10. Трудовые ресурсы проекта.

  1. После того, как создан каталог трудовых ресурсов, нажмите на кнопку [Готово].
  2. Для того чтобы связать ресурсы с задачами в окне Ресурсы перейдите по ссылке «Назначение людей и оборудования задачам». Выделите задачу 0-1 и выполните команду «Назначить ресурсы». Назначьте ресурсы задачам согласно рис.11.

Рис. 11. Назначение ресурсов задачам.

  1. После того, как ресурсы назначены,  нажмите кнопку [Готово] и просмотрите лист ресурсов, выполнив команду Вид/Лист ресурсов. Обратите внимание, что Иванов и Сидоров на листе ресурсов выделены красным цветом.
  2. Для того чтобы просмотреть загруженность Иванова по дням, выделите его в списке ресурсов и выполните команду Вид/График ресурсов. Обратите внимание,  на какие дни приходится перегрузка данного сотрудника.
  3. Для того чтобы просмотреть загруженность всех сотрудников по дням, выполните команду Вид/Использование ресурсов. На листе использования ресурсов видно, какие работы выполняет сотрудник, трудозатраты по каждой работе в отдельности и в целом по каждому сотруднику. Определите точные периоды для каждого сотрудника, когда он выполняет несколько работ одновременно и, соответственно,  его рабочий день длится 16 часов.
  4. Перейдите на лист Задачи и перераспределите сотрудников таким образом, чтобы не было перегрузок (в данной задаче предполагается, что все сотрудники взаимозаменяемы). Если невозможно провести оптимальное перераспределение сотрудников по задачам, то можно уменьшить трудозатраты для конкретного сотрудника по конкретному дню на листе Использование ресурсов.
  5. Для добавления новых столбцов в область задач на диаграмме Ганта, необходимо выделить столбец, перед которым желаете вставить новый, из контекстного меню выбрать команду Вставить столбец и в диалоговом окне «Определение столбца» указать имя вставляемого поля.  Добавьте столбец Трудозатраты после поля Длительность.
  6. Для определения суммарных трудовых и финансовых затрат выполните команду Проект/Сведения о проекте (в диалоговом окне кнопка Статистика).
  7. Для отслеживания хода выполнения проекта выберите на панели пункт меню Отслеживание, перейдите по ссылке «Подготовка к отслеживанию хода работы над проектом», на первом шаге мастера установите опцию Нет, на втором шаге мастера выберите способ отслеживания «Всегда отслеживать путем указания процента завершения по трудозатратам» и новом поле «% завершения по трудозатратам» для работ 0-1, 0-2, 0-3 установите 100% - ное завершение. Вернитесь в окно Отслеживание и перейдите по ссылке «Проверка хода выполнения проекта». Указав любую дату, просмотрите индикатор выполнения задач проекта.
  8.  MS Project позволяет формировать различные виды отчетов. Для составления отчетности выберите на панели пункт меню Отчет, установите опцию «Напечатать отчет о проекте» и перейдите по ссылке «Показать отчеты». В диалоговом окне выберите категорию отчета, например, Загрузка и укажите вид отчета «Использование ресурсов». Просмотрите другие виды отчетности.

Задание 4. (самостоятельно).

При составлении проекта работ  выделено 8 событий:(0,1,2,3,4,5,6,7),  которые связаны работами (i –j ), где i,j  0,1,2,3…,7 и i  ≠ j , например событие 1 связано с событием 2 работой (1-2).Определено штатное расписание для выполнения проекта в составе:

  1. Руководитель проекта (РП), стандартная ставка – 70$/день;
  2. Ведущий инженер (ВИ), стандартная ставка - 60$/день;
  3. Исполнитель 1 (И1), стандартная ставка - 50$/день;
  4. Исполнитель 2 (И2), стандартная ставка - 50$/день;

Рабочий день исполнителя 8 часов при 5 дневной рабочей неделе.

Требуется:

  1.  Построить сетевой график выполнения проекта.
  2.  Определить критический путь.
  3.  Провести анализ использования ресурсов.
  4.  Провести анализ стоимости проекта.

Исходные данные по продолжительности работ и закрепленные работы  приведены в таблице.

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

Компьютерные технологии в СКС и туризме на базе Microsoft Office

Цель практической работы

Деятельность любой туристической фирмы связана с использованием информационных технологий различного уровня:

  1.  стандартное программное обеспечение (пакет программ Microsoft Office, электронная почта, программы-переводчики);
  2.  специализированные разработки комплексов автоматизации в сфере сервиса и туризма;
  3.  интернет-технологии и глобальные компьютерные системы бронирования.

Пояснения к работе

Практическое задание №1:

1. Гостиница имеет различные категории номеров. Информация о ценах на гостиничные услуги и количестве проживающих на конкретное число размещена на одном листе:

Стоимость проживания в гостинице Категория номера

количество номеров

Цена (руб.)/сутки

номер «Эконом»

4

1500

одноместный номер

4

1900

двухместный номер

20

2800

Полулюкс

6

3500

Пентхаус

1

4500

Рассчитайте доход гостиницы (общий и по категориям номеров) за 11.08.2007;

3. Постройте на отдельном листе объемный вариант обычной гистограммы, отражающий степень заселенности гостиницы на указанное число.

Практическое задание №2:

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

Лист 1. Цены на услуги

1. Услуги

стоимость услуги (разовой/дневной), $

взрослые

дети

Гостиница

40

30

Экскурсия

15

12

Питание

15

12

Трансфер

10

9

Лист 2. Курс доллара

курс доллара

27

Лист 3. Расчет стоимости тура продолжительностью 7 дней для группы отдыхающих

категория отдыхающих

количество чел.

стоимость тура $

общая стоимость (руб.)

взрослые

34

дети

21

Итого

2. Используя формулу, на листе 3 рассчитайте стоимость тура продолжительностью 7 дней в долларах, при условии, что за это время было совершено 3 экскурсии;

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

4. Постройте круговую диаграмму, отражающую соотношение общей стоимости тура в рублях для взрослых и детей.

Практическое задание №3:

1. В начале июня 2007 г. турфирма занималась реализацией туров по разным направлениям. Информация о проданных путевках и курсе валют хранится на разных листах:

Лист 1. Проданные путевки:

дата

наименование тура

цена тура

скидка

цена со скидкой

цена $

цена евро

01.06.07

Вена

1200

02.06.07

Анталия

500

03.06.07

Хургада

600

04.06.07

Карловы Вары

800

05.06.07

Солнечный берег

1100

06.06.07

Стамбул

800

07.06.07

Прага

870

08.06.07

Каир

750

Итого

Лист 2. Курс валют:

евро

доллар

36

27

2. Для заполнения столбца «Дата» воспользуйтесь автопродолжением;

3. С помощью функции «Автофильтр» найдите все туры, цена которых больше или равна 800 руб.

4. С помощью формулы «Если» рассчитайте скидку, автоматически предоставляемую на тур, при следующих условиях: при стоимости тура менее 599 руб. скидка не предоставляется, от 600 до 899 руб. – 3%, от 900 до 1149 руб. – 5%, выше 1150 руб. – 6%.

5. Рассчитайте с помощью формулы цену тура со скидкой;

6. Рассчитайте стоимость услуг в евро и долларах, используя абсолютную адресацию и предусмотрев автоматический пересчет при изменении текущего курса этих валют;

7. С помощью функции «Автосумма» вычислите итоговую прибыль турфирмы за указанный период в рублях, евро и долларах;

8. Постройте график дохода турфирмы за указанный период (в евро).

Дополнительное задание:

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

Лист 1. Страхование клиентов:

Дата

Фамилия клиента

Страховая сумма $

Тариф $/

Сутки

Количество дней

Стоимость полиса (руб.)

12.09.2007

Васин В.В.

1000

7

13.09.2007

Котов К.К.

5000

9

14.09.2007

Орлов О.О.

1000

15

15.09.2007

Горин Г.Г.

10000

10

Лист 2. Курс доллара

курс доллара

27

2. Для заполнения столбца «Дата» воспользуйтесь автопродолжением;

3. С помощью функции «Если» рассчитайте тариф, взимаемый за один день поездки, который бы автоматически появлялся в четвертом столбце при введении различных страховых сумм. Условие: если страховая сумма равна 1000 $, то тариф составляет 0,1 $ в сутки; если 5000 $, то 0.24 $ в сутки; если 10000 $, то 0.48 $ в сутки;

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

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

Контрольные вопросы

Список литературы

Проектирование и создание баз данных средствами Microsoft Access

Цель практической работы

Сфера туристского бизнеса связана с хранением и обработкой больших объемов информации, где требуется не только автоматизация документации турифирм, осуществляемая с помощью программ Microsoft Word и Microsoft Excel, но и управление производственными процессами предприятия. В большинстве случаев это становится возможным благодаря построению информационной модели организации посредством баз данных (БД).

Microsoft Access − это функционально полная реляционная система управления базами данных (СУБД). Кроме того, Access одна из самых мощных, гибких и простых в использовании СУБД. Она позволяет создать большинство приложений, не написав ни единой строчки программы и потому доступна широкому кругу пользователей.

База данных Microsoft Access представляет совокупность объектов различного типа и назначения, которые используются для хранения, отображения и вывода на печать информации, а также содержат созданный пользователями программный код.

СУБД Access работает со следующими основными типами объектов:

􀀹таблицы – структурированные объекты, состоящие из строк (записей в терминологии СУБД Access) и столбцов (полей) и предназначенные для хранения информации;

􀀹формы – создаваемый разработчиком БД интерфейс для ввода и просмотра данных;

􀀹запросы – специальные средства отбора записей, содержащие точную формулировку критерия отбора, заданного пользователем;

􀀹отчеты – специальные средства для вывода данных на экран, в принтер, в файл, позволяющие придать информации определенную форму.

Пояснения к работе

Разработка структуры БД предусматривает определение состава и взаимосвязи реляционных таблиц, описывающих предметную область, а также характеристику этих таблиц по входящим в них реквизитам (качественная или количественная характеристика объекта, отраженная в названии столбца).

Создание базы данных проходит три этапа:

1. концептуальное описание – определение информации, которая должна храниться в базе, а также форм и отчетов, которые могут потребоваться пользователю;

2. логическое проектирование – выбор подходящей СУБД, т.е. создание логической схемы, структуры, ориентированной на конкретную базу данных;

3. физическое проектирование – определение способов и мест размещения базы данных, оценка ее объема и других параметров.

Затем созданная информационная модель реализуется на компьютере с помощью Microsoft Access.

Практическое задание №1 «Учебная база данных «Борей»:

Учебная база данных "Борей" устанавливается вместе с Microsoft Access. В ней хранятся сведения о деятельности вымышленной торговой компании "Борей", занимающейся импортом и экспортом деликатесов по всему миру. Просмотр таблиц, запросов, форм, отчетов, включенных в базу данных "Борей", позволяет лучше понять, как следует организовать собственные базы данных Microsoft Access. Кроме того, содержащиеся в базе данных "Борей" сведения позволяют потренироваться в работе с Microsoft Access перед началом работы с собственными проектами.

1. Откройте учебную базу данных «Борей» в меню «Справка» − «Примеры баз данных»;

2. Создайте резервную копию в папке «Мои документы» под именем «Борей-№», где № − номер вашей группы;

3. Откройте базу данных «Борей-№», выбрав команду меню «Файл» − «Открыть базу данных»;

4. В «Окне базы данных» откройте вкладку «Таблицы». Посмотрите описание каждой таблицы, выделив ее и выбрав команду меню «Вид» − «Свойства»;

5. Посмотрите содержание каждой таблицы базы данных «Борей-№» в режиме таблицы и режиме конструктора;

6. Изучите связи между таблицами через меню «Сервис» − «Схема данных»;

7. Откройте вкладку «Запросы». Посмотрите, как выглядит запрос «Счета» в Access в режиме таблицы и режиме конструктора;

8. Откройте вкладку «Формы». Посмотрите, как выглядит форма «Сотрудники» в Access в режиме формы и режиме конструктора;

9. Откройте вкладку «Отчеты». Посмотрите, как выглядит отчет «Список товаров» в Access в режиме просмотра и режиме конструктора;

10. Создайте с помощью мастера таблицу «Расходы» со следующими полями: код расходов, код сотрудника, тип расходов, сумма затрат, описание;

11. Свяжите таблицу «Расходы» с имеющимися в базе данных таблицами через меню «Сервис» − «Схема данных» с помощью переноса ключевых полей (при этом обеспечьте целостность данных и их каскадное обновление);

12. Откройте таблицу «Клиенты» и произведите сортировку столбцов «Код клиента» и «Название» по возрастанию, затем по убыванию;

13. С помощью меню «Правка» − «Найти» найдите в таблице «Клиенты» всех лиц с должностью «бухгалтер»;

14. Замените в таблице должность «бухгалтер» на «счетовод»;

15. На вкладке «Формы» создайте с помощью мастера форму «Расходы» и создайте три записи в данной форме;

16. На вкладке «Запросы» создайте простой запрос на основе таблицы «Клиенты» с полями: Код клиента, название, должность, адрес, телефон;

17. На вкладке «Отчеты» создайте, используя «Мастер отчетов» и таблицу «Клиенты», отчет со следующими полями: Код клиента, название, должность, адрес, телефон. Для работы выберите ступенчатый макет и деловой стиль оформления;

18. Закройте базу данных. Все сделанные вами изменения сохранятся в ней автоматически.

Практическое задание №2:

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

Туры

Код тура

Название тура

Продолжи-тельность

тура

Цена тура,

евро

EG-1

Хургада

10

560

EG-2

Древности Каира

10

700

RUS-1

оз. Байкал

5

250

RUS-2

Золотое кольцо

4

180

TR-1

Солнечная Анталия

8

300

TR-2

Пляжи Турции

10

480

UA-1

Древний Киев

6

250

Туристы

Код туриста

ФИО

Адрес

Телефон

Код тура

Т-1

Петров П.П.

Промышленная, 10, кв.18

95-87-68

UA-1

Т-2

Сидоров С.С.

Октябрьская, 107, кв.90

26-98-65

TR-2

Т-3

Добрынин Д.Д.

Свободы, 178, кв.13

36-90-43

EG-1

Т-4

Степанов В.В.

Пролетарская, 11, кв. 90

43-78-65

RUS-2

Т-5

Истомин И.И.

Фрунзе, 98, кв. 1.

28-98-54

RUS-1

Этапы работы:

1. Создайте таблицу «Туристы» (только реквизиты) с помощью мастера, переименовав необходимые поля в категории «Деловые» − «Клиенты»;

2. Создайте таблицу «Туры» (только реквизиты), используя режим конструктора (код тура − текстовый тип данных (ТД), название тура – текстовый ТД, продолжительность тура – числовой ТД, цена - денежный ТД (формат поля – евро));

3. Постройте схему данных через меню «Сервис» − «Схема данных» с помощью переноса ключевых полей (при этом обеспечьте целостность данных и их каскадное обновление);

4. Создайте с помощью мастера форму «Туры», выбрав все доступные поля, выровненный вид формы и международный стиль;

5. Заполните форму «Туры» приведенными в таблице данными;

6. Аналогично создайте и заполните форму «Туристы» (ленточный вид формы ленточный, стандартный стиль);

7. С помощью мастера составьте запрос, содержащий сведения о ФИО туриста, его телефоне и названии тура;

8. Сформируйте запрос в режиме конструктора на основе таблиц «Туры» и «Туристы», используя поля ФИО туриста, название тура, цена тура. Условие отбора – цена тура не превышает 300 евро;

9. С помощью мастера создайте отчет, содержащий сведения о ФИО туриста, его телефоне, названии и цене тура (ступенчатый макет, деловой стиль).

Практическое задание №3:

Создайте модель организации размещения и учета проживающих в некоторой гостинице граждан (клиентов). Гостиница располагает номерами с разным уровнем сервиса и, соответственно, оплаты. Одной из характеристик номера является его тип. Предположим, что существуют следующие типы номеров: люкс – многокомнатный номер с высоким уровнем сервиса, комфортности и обслуживания; полулюкс – номер меньшей, чем люкс, площади, но с достаточным уровнем сервиса и комфортности; одноместный или двухместный номер с минимальным уровнем сервиса. Стоимость для номеров типа люкс и полулюкс устанавливается как стоимость всего номера (в сутки), независимо от количества проживающих в номере. Стоимость проживания в одно- и двухместных номерах устанавливается для одного человека (в сутки). Все прибывающие в отеле клиенты проходят обязательную процедуру регистрации и заносятся в карту клиента и карточку регистрации.

  1.  База данных состоит из следующих таблиц:

Номерной фонд

Код комнаты

Номер комнаты

Тип номера

Стоимость

(руб.)/сутки

Л-1

1

люкс

1500

ПЛ-1

2

полулюкс

1200

ПЛ-2

3

полулюкс

1200

2М-1

4

двухместный

800

2М-2

5

двухместный

800

1М-1

6

одноместный

600

Таблица «Номерной фонд» создается в режиме конструктора и сразу заполняется необходимыми сведениями. Тип данных: код комнаты – текстовый, номер комнаты – числовой, тип номера – текстовый, стоимость – денежный (рубли).

Клиенты

Код клиента

ФИО

Паспорт

Дата

рождения

Адрес

Телефон

Таблица «Клиенты» создается в режиме конструктора, но имеет только поля (информация о клиентах заносится позднее). Тип данных: код клиента и ФИО – текстовый, паспорт – тестовый, размер поля 10 (по количеству цифр в номере паспорта), дата рождения – дата/время (краткий формат даты), адрес и телефон – текстовый.

Расчетные карточки

Код карточки

Код

клиента

Код

комнаты

Дата

заезда

Дата

выезда

Сумма

оплаты (тг.)

Таблица «Расчетные карточки» создается в режиме конструктора (только поля). Тип данных: код карточки, код клиента, код комнаты – текстовый, дата заезда и выезда – дата/время (краткий формат даты), сумма оплаты - денежный (рубли).

  1.  Создайте схему базы данных, объединив между собой три таблицы;
  2.  С помощью мастера форм создайте многотабличную форму «Размещение клиентов» на основе таблиц «Клиенты» и «Расчетные карточки» (вид представления данных – подчиненный, внешний вид – табличный, стиль – стандартный, код комнаты редактируется с помощью конструктора и должен выдаваться как поле со списком).

Форма «Размещение клиентов»

4. С помощью формы «Размещение клиентов» произведите заселение трех клиентов:

А) № Л-1 – Иванов И.И., паспорт – 5467847804, дата рождения – 21.09.1960, адрес – г. Киров, ул. Свободы, 12, кв. 1, тел. 555567, код карточки – 1, дата заезда 21.07.2007, дата выезда – 23.07.2007, сумма оплаты – 3000 руб.;

В) № ПЛ-2 – Петров П.П., паспорт – 7658467802, дата рождения - 30.01.1984, адрес – г. Ярославль, ул. Машиностроителей, д. 10, кв. 67, тел. 786590, код карточки – 2, дата заезда 22.07.2007, дата выезда – 26.07.2007, сумма оплаты – 4800 руб.;

С) № 1М-1 – Степанов С.С., паспорт – 5467327802, дата рождения - 15.01.1976, адрес – г. Ярославль, ул. Панина, д. 67, кв. 13, тел. 569021, код карточки – 3, дата заезда 21.07.2007, дата выезда – 28.07.2007, сумма оплаты – 5600 руб.;

5. Используя режим конструктора, составьте запрос о всех клиентах:

− заехавших в гостиницу 21.07.2007;

− сумма оплат за гостиницу у которых превышает 5000 руб.

6. С помощью меню «Правка» − «Найти» − «Заменить» найдите в таблице клиента Степанов С.С. и исправьте его фамилию на «Степашин С.С.»;

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

Дополнительное задание:

Разработайте базу данных для страхования туристов:

  1.  Составьте таблицу «Тарифы страхования» для получения страховой суммы в 10000 евро:

«Тарифы страхования»

Код тарифа

Название тарифа

Сумма платежа €

Д1-15

поездка длительностью 1-15 дней

6.60

Д16-30

поездка длительностью 16-30 дней

12.20

Д31-60

поездка длительностью 31-60 дней

24.40

Д61-365

поездка длительностью 61-365 дней

48.80

2.Составьте таблицу «Клиенты», содержащую поля: код клиента, ФИО клиента, адрес и телефон;

3. Составьте таблицу «Оформление страховки» со следующими полями: код страховки, код клиента, код тарифа, дата страхования (краткий формат даты);

4. Создайте схему базы данных, связав между собой три таблицы;

5. Создайте многотабличную форму «Клиенты», с помощью которой можно было бы на основе трех связанных таблиц фиксировать не только реквизиты клиентов, но и выбирать код тарифа из поля со списком, автоматически получая расчет суммы платежа (вид представления данных – подчиненный, внешний вид – табличный, стиль – международный):

Форма «Клиенты»

6. С помощью формы «Клиенты» занесите в базу данных трех туристов, желающих оформить страховку:

А) № 1 − Иванов И.И., адрес – г. Киров, ул. Свободы, 12, кв. 1, тел. 555567, код страховки – 1, код тарифа Д16-30, дата страхования – 23.07.2007, сумма платежа появляется автоматически;

В) № 2 – Петров П.П., адрес – г. Ярославль, ул. Машиностроителей, д. 10, кв. 67, тел. 786590, код страховки – 2, код тарифа Д1-15, дата страхования – 24.07.2007, сумма платежа появляется автоматически;

С) № 3 – Степанов С.С., адрес – г. Ярославль, ул. Панина, д. 67, кв. 13, тел. 569021, код страховки – 3, код тарифа Д61-365, дата страхования – 27.07.2007, сумма платежа появляется автоматически;

5. Составьте запрос о клиентах:

− оформивших страховку 24.07.2007;

− сумма платежа за страховые услуги у которых превышает 20 €;

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

ЛАБОРАТОРНАЯ РАБОТА № 10

ПЛАНИРОВАНИЕ РЕКЛАМНОЙ КАМПАНИИ

Цель лабораторной работы:

Научиться использовать средства табличного процессора Microsoft Excel для решения финансовой задачи.

Пояснения к работе

Время выполнения – 2 часа

Порядок выполнения лабораторной работы:

1. Решить все описанные упражнения самостоятельно, руководствуясь методическими указаниями.

2. Выполнить задание.

3. Проверить свои знания по контрольным вопросам и сдать лабораторную работу.

После выполнения лабораторной работы студент должен:

Знать: назначение и параметры функций ПОИСКПОЗ, МАКС,

СУММПРОИЗВ и МУМНОЖ.

Уметь: находить максимальный элемент массива с помощью функции МАКС, относительную позицию элемента массива, который соответствует указанному значению, с помощью функции ПОИСКПОЗ, сумму произведений элементов массива с помощью функции СУММПРОИЗВ, произведение массивов с помощью функции МУМНОЖ. С применением перечисленных функций студент должен уметь решать задачу о планировании рекламной компании с целью определения целесообразности ее проведения.

Работа в лаборатории

9.1. Планирование рекламной компании

В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данной лабораторной работе выполняйте на новом листе, которому дайте имя «Рекламная компания».

Прежде всего, опишем функции МАКС (МAХ) и ПОИСКПОЗ (MATCH), которые используются в дальнейшем при рассмотрении примера составления оптимального плана рекламной кампании.

Функция МАКС возвращает максимальный элемент массива. Функция ПОИСКПОЗ возвращает относительную позицию элемента массива, который соответствует указанному значению. Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента, а не сам элемент.

Синтаксис: ПОИСКПОЗ(искомое значение; просматриваемый_массив;тип сопоставления).

.

Рис. 9.1. Планирование рекламной кампании.

В ячейки диапазона В13:В15 введены формулы:

=МАКС(I12:I13)

=МАКС(I14:I15)

=МАКС(I16:I17),

определяющие максимальную ожидаемую прибыль на третьей неделе, если на предыдущей неделе система находилась в первом, втором или третьем состоянии, соответственно. В ячейках диапазона С13:С15 по формулам:

=ПОИСКПОЗ(В13;I12:I13;0)

=ПОИСКПОЗ(В14;I14:I15;0)

=ПОИСКПОЗ(В15;I16:I17;0)

определяется оптимальный вариант действий. Если 1, то деньги на рекламу не тратить, а если 2 – то тратить.

Перейдем ко второй неделе рекламной кампании. В ячейку J5 введена формула:

=I5+МУМНОЖ(В5:D5;$В$13:$В$15),

В ячейки диапазона D13:D15 введены формулы:

=МАКС(J12:J13)

=МАКС(J14:J15)

=МАКС(J16 :J17),

определяющие максимальную ожидаемую прибыль на второй неделе, если на предыдущей неделе система находилась в первом, втором или третьем состоянии, соответственно. В ячейках диапазона Е13:Е15 по формулам:

=ПОИСКПОЗ(D13;J12:J13;0)

=ПОИСКПОЗ(D14;J14:J15;0)

=ПОИСКПОЗ(D15;J16:J17;0)

определяется оптимальный вариант действий. Аналогично проводятся расчеты для первой недели.

Из рис. 9.1 видно, что на первой и второй неделях необходимо использовать рекламу, не считаясь с состоянием системы, однако, на третьей неделе рекламу следует использовать только тогда, когда система находится во втором или третьем состояниях. Суммарный ожидаемый доход фирмы составит 10736 при отличной оценке, 7923 – при хорошей и 4222 – при удовлетворительной оценке.

9.2. Задание

Задание выполняйте в книге Задания.xls. Скопируйте из книги ФИНАНСОВЫЙ АНАЛИЗ лист «Рекламная компания» и на нем путем корректировки рассчитанного примера выполните задание.

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

Порядок отчета лабораторной работы

При отчете лабораторной работы необходимо:

1. Продемонстрировать выполненные упражнения, описанные в методических указаниях.

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

3. Ответить на контрольные вопросы.

Контрольные вопросы

1. Что возвращает функция МАКС?

2. Что возвращает функция ПОИСКПОЗ?

3. Что такое Просматриваемый_массив?

4. Опишите параметры функции СУММПРОИЗВ.

5. Опишите параметры функции МУМНОЖ.


 

А также другие работы, которые могут Вас заинтересовать

3842. Перевірка основного рівняння динаміки обертального руху за допомогою маятника Обербека 159 KB
  Перевірка основного рівняння динаміки обертального руху за допомогою маятника Обербека Мета роботи: Експериментальна перевірка основного рівняння динаміки обертального руху. Ознайомлення на дослідах з поняттями момент інерції, кутове прискорення, но...
3843. Принцип действия полупроводникового транзистора 121 KB
  Цель работы: ознакомиться с принципом действия полупроводникового транзистора. Задача: получить выходные характеристики транзистора по напряжению в схеме с общей базой, рассчитать коэффициент усиления транзистора по напряжению. Приборы и прин...
3844. ИЗУЧЕНИЕ МАЯТНИКА МАКСВЕЛЛА 62.5 KB
  ИЗУЧЕНИЕ МАЯТНИКА МАКСВЕЛЛА Цель работы: определение момента инерции маятника Максвелла. Краткая теория. Маятником Максвелла называют однородный диск с валом (тонким стержнем кругового сечения), проходящим через центр масс диска перпендикулярно его ...
3845. Закон сохранения момента импульса 159 KB
  Закон сохранения момента импульса. Закон сохранения момента импульса. Гироскоп. Работа и кинетическая энергия при вращательном движении. Закон сохранения момента импульса. Согласно основному уравнению динамики вращательного движени...
3846. Определение отношения удельных теплоемкостей воздуха методом адиабатного расширения 190.5 KB
  Определение отношения удельных теплоемкостей воздуха методом адиабатного расширения Приборы и принадлежности Закрытый стеклянный баллон с краном, манометр, насос рис. 1 Теория работы и описание прибора Для вещества в любом агрегатном состоянии харак...
3847. Определение коэффициента вязкости жидкости методов стокса 207.5 KB
  Определение коэффициента вязкости жидкости методов стокса Приборы и принадлежности: Стеклянный цилиндр с исследуемой жидкостью, шарики малого диаметра, микрометр, секундомер, пинцет, масштабная линейка. Теория работы и описание приборов При движении...
3848. Исследование характеристика приборов и назначения шунтов и дополнительных сопротивлений к приборам 185.5 KB
  Цель работы: Приобрести навыки в чтении технических характеристик приборов, понять назначении шунтов и дополнительных сопротивлений к приборам, научится их рассчитывать. Приборы и принадлежности: Исследуемый электроизмерительный прибор (V-A), контро...
3849. Абсолютна та відносна похибка 78.27 KB
  Абсолютна та відносна похибка. Мета роботи: вивчити і засвоїти поняття абсолютної й відносної похибки та методи їх оцінювання. Короткі теоретичні відомості. Зв'язок між кількістю точних десяткових знаків і відносною похибкою наближеного числа дається у наведеній далі теоремі.
3850. Розв’язування системи нелінійних алгебраїчних рівнянь методом простої ітерації (методом Ньютона) 63 KB
  Розв’язування системи нелінійних алгебраїчних рівнянь методом простої ітерації (методом Ньютона) Мета роботи: вивчити і засвоїти метод простої ітерації. Короткі теоретичні відомості Метод простої ітерації для розв’язування системи двох нел...