3.1 Содержание и оформление контрольной работы

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

1. Определить свой вариант по двум последним цифрам зачетной книжки и записать задания.

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

3. Выполнить задания.

4. Оформить отчет в соответствии с перечисленными ниже требованиями.

Структура контрольной работы должна содержать:

• титульный лист (Образец титульного листа контрольной работы представлен в Приложении)

• содержание;

• задания;

• отчет в текстовом редакторе Word;

• результаты (тексты, таблицы, диаграммы, графики и т.д.)

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

Отчет подготовить в текстовом редакторе Word и в электронном виде. Задания выполненные в программе MS Excel сохранить в отдельном файле и распечатать.

Выполненная работа предоставляется на диске CD RW и распечатанная на бумаге – стандартных листах формата А4. Полный объем контрольной работы не должен превышать 20…25 страниц формата А4 при наборе основного текста шрифтом Times New Roman размером 14 пт.

Диск CD RW тоже должен быть подписан и вложен в файл-папку с распечатанной контрольной работой.

3.2 КОНТРОЛЬНАЯ РАБОТА № 1

Задание 1. Встроенные функции Excel. Статистический анализ.

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

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

1. На рабочем листе построить таблицу значений функции, согласно варианта задания и ее график.

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

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

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

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

6. Провести статистический анализ с использованием инструмента Описательная статистика из Пакета анализа.

7. Построить гистограмму распределения данных, используя надстройку Пакет анализа.

Варианты задания 1

Y=/x+ex/+tg 3x*lg x2

-10  x  10, x=1

Задание 2 Технология работы с финансовыми функциями MS Excel. Анализ «Что-если».

Цель задания: научиться работать с финансовыми функциями Excel и выполнять анализ «Что-если» при варьировании данных.

1. Используя соответствующие финансовые функции, решите следующие задачи.

2. Для созданной задачи изменить величины ее параметров так, чтобы (не меняя формулы) результат вычислений тоже изменился.

3. Выполнить анализ данных «Что - если», используя таблицу подстановок.

4. Изменить результат вычислений задачи с помощью Сценария. Вывести итоговый отчет типа структура.

5. Изменить результат вычисления при помощи Подбора параметров.

---

Варианты задания 2

Вариант 2

1. Вычислить, на сколько снизится стоимость основных фондов, рассчитанная по методу двойной амортизации (ДДОБ), если начальная стоимость имущества 30000р., а в конце периода эксплуатации 4000р. Время эксплуатации считать равным 3 года, период, для которого вычисляется амортизация, равным 2,5 года.

2. Вычислить эту величину (не меняя формулу) при условии, что начальная стоимость имущества равна 50000р..

3. Определить величину ДДОБ используя таблицу подстановок:

• При изменении времени эксплуатации: 7, 6, 5, 4, 3 года;

• При изменении времени эксплуатации: 7, 6, 5, 4, 3 года; и начальной стоимости 70000, 60000, 50000, 40000, 30000 руб. соответственно.

4. Составить сценарий, если величина начальной стоимости изменится на 35000р.

5. Подобрать параметр срока эксплуатации, если стоимость основных фондов снизится на 1000р.

4 Методические рекомендации по выполнению контрольных работ

4.1 Методические рекомендации по выполнению контрольной работы № 1

4.1.1 Методические указания по выполнению задания 1

MS EXCEL обеспечивает 10 разных категорий функций: математические/тригонометрические, инженерные, логические, текстовые, статистические, функции категории дата/время, функции для работы с базами данных/списками, финансовые, информационные и функции категории ссылки/массивы.

Программа EXCEL содержит более 400 встроенных функций, которые можно выбрать с помощью Мастера функций.

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

Для вызова Мастера функций необходимо выбрать команду Вставить функцию f_x из меню Формулы или нажать на панели инструментов формула кнопку .

После её нажатия появится окно Мастера функций (рисунок 1).

Рисунок 1 – Запуск Мастера функций

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

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

Рисунок 2 – Диалоговое окно Аргументы функции СРЗНАЧ (A1:A10)

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

Аргументы функций:

1) числовые константы, например, функция ПРОИЗВЕД(2;3) вычисляет произведение чисел 2 и 3, т.е. 2·3.

2) ссылки на ячейки и блоки ячеек (функция ПРОИЗВЕД (А1;С1:СЗ) вычисляет произведение содержимого ячеек А1,С1,С2 и С3, т.е. А1·С1·С2·СЗ.

3) текстовые константы (заключенные в кавычки).

4) логические значения.

5) массивы.

6) имена ссылок, например, если ячейке А10 присвоить имя СУММА –последовательность команд Формулы \ Присвоить имя... – рисунок 3),а блоку ячеек В10:Е10 – имя ИТОГИ, то допустима следующая запись: =СУММ(СУММА;ИТОГИ).

---

7) смешанные аргументы, например,

=СРЗНАЧ (Группа;АЗ;5*3)

Рисунок 3 – Присвоение имени ячейке или блоку ячеек

Пример 1. Вычислить значения функции

Y=e^x*sin (x) для -1 x 1 x=0.1

1. Заполним столбец А значениями аргумента функции. Чтобы не вводить их вручную, применим следующий прием. Введите в ячейку А1 начальное значения аргумента (-1). Во вкладке Главная> Редактирование выберите кнопку Заполнить, затем Прогрессия и в открывшемся диалоговом окне укажите предельное значение (1), шаг(0,2) и направление По столбцам (рисунок 4). После нажатия кнопки ОК в столбце А будут введены все значения аргумента

Рисунок 4 – Автозаполнение ячеек данными

2. В ячейку В1 введите формулу =exp(А1)*sin(A1). Размножьте эту формулу на остальные ячейки столбца В, ухватив левой мышью маркер заполнения (черный квадратик в правом нижнем углу рамки выделенной ячейки В1) и протащив маркер до конца изменения аргумента. В итоге будут вычислены соответствующие значения функции.

Логические функции

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

Так, функция ЕСЛИ выполняет проверку условия, задаваемого первым аргументом логич_выр:

=ЕСЛИ(логич_выр; знач_да; знач_нет) и возвращает знач_да, если условие выполнено (ИСТИНА), и знач_нет, противном случае (ЛОЖЬ).

Например:

=ЕСЛИ(А6<10;5;10).

Если значение в ячейке А6<10, то функция вернет результат 5, а иначе – 10.

=ЕСЛИ(B4>80;”Сданы”;”Не сданы”).

Если значение в ячейке B4>80, то в ячейке с приведенной формулой будет записано ”Сданы”, иначе – ”Не сданы”.

=ЕСЛИ(СУММ(А1:А10)>0;СУММ(В1:В10);0).

Если сумма значений в столбце А1:А10 больше 0, то вычислится сумма значений в столбце В1:В10, в противном случае результат – 0.

Дополнительные логические функции

=И (логич_выр1;логич_выр2)

=ИЛИ (логич_выр1;логич_выр2)

=НЕ (логич_выр)

позволяют создавать сложные условия, например:

=ЕСЛИ (И(СУММ(А1:А10)>0;СУММ(В1:В10)>0);СУММ (A1:B10);0).

Если суммы и в столбце А1:А10 и в столбце В1:В10 положительны, то вычислить суму значений в ячейках А1:В10, иначе – 0.

Статистические функции

MS EXCEL предоставляет широкие возможности для анализа статистических данных. Для решения простых задач можно использовать встроенные функции. Рассмотрим некоторые из них.

1. Вычисление среднего арифметического последовательности чисел:

=СРЗНАЧ (числа).

Например, =СРЗНАЧ(5;7;9);

=СРЗНАЧ (А1:А10;С1:С10)

=СРЗНАЧ (А1:Е20).

2. Нахождение максимального (минимального) значения:

=МАКС (числа)

=МИН (числа).

Например: =МАКС (А4:С10);

=МИН (А2;С4;7).

3. Вычисление медианы (числа, являющегося серединой множества):

=МЕДИАНА(числа).

4. Вычисление моды (наиболее часто встречающегося значения в множестве):

=МОДА(числа).

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

---

5. Дисперсия:

=ДИСП(числа).

6. Стандартное отклонение:

=СТАНДОТКЛОН(числа).

Статистический анализ с помощью Пакета анализа

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

Чтобы воспользоваться инструментами анализа, выполните следующие действия.

1. В меню Данные Анализ выберите команду Анализ данных (Рис. 5).

Рисунок 5 – Инструмент Описательная статистика

2. Выберите из списка название нужного инструмента анализа и нажмите кнопку ОК.

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

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

Рисунок 6 – Обработка столбца В инструментом Описательная статистика

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

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

Пример 2 Пусть дана таблица с данными о температуре воздуха в Краснодаре летом 2014г. Интервал изменения температуры от 18 до 38 градуса по Цельсию (его можно определить с помощью функций МАКС() и МИН()).

1. Разобьем этот интервал на подинтервалы – карманы шириной, например, 2 градуса по Цельсию (ширина карманов не обязательно должна быть равной).

2. Воспользуемся командой Заполнить из меню Главная в группе Редактирование для быстрого заполнения столбца карманов (значения в столбце будут изменятся от 18 до 38 градусов по Цельсию с шагом 2 градуса).

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

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

2. Методические указания по выполнению задания 2

Пример 1. Предположим, что нужно воспользоваться 9-процентной 15-летней ссудой. Объем ссуды составляет 150 000 000 рублей. C помощью Мастера функций можно определить величины ежемесячных выплат - используется функция MS Excel ПЛТ().

---

Ввести условие примера в MS Excel (рис. 7), начиная с ячейки А1:

	A	B
1	Процентная ставка	9%
2	Период	15
3	Удельная ставка	=B1/12
4	Число выплат	=B2*12
5	Объем ссуды	-150000000
6	Ежемесячная выплата	=ПЛТ(B3;B4;B5)

Рисунок 7 – Определение величины ежемесячных выплат

В ячейки В3 и В4 ввести соответствующие формулы.

Процентная ставка (СТАВКА) – годовая, поэтому для получения месячной ставки (Удельная ставка) соответствующее значение делится на 12 (0,09/12). Срок действия ссуды – 15 лет, поэтому с учетом 12 платежей год общее количество месячных выплат (КПЕР) составит 12х15.

Для ячейки В6 пошаговыми действиями Мастера функций выполните настройку функции ПЛТ. Для вызова Мастера функций необходимо выбрать команду Вставить функцию (значок f_x) в меню Формулы ( Рис. 8).

Рисунок 8 – Мастер функций в режиме ввода функции ПЛТ

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

Анализ может проводиться для функций с одной переменной или для функций с двумя переменными. Рассмотрим пример.

Пример 2. Определить какими будут выплаты по ссуде при меняющейся процентной ставке (для примера 1)

В ячейки А9:В13 введите следующие значения, оставив пустой строку перед числовыми значениями (рис. 9):

	A	B
9	Процентная ставка	Выплаты
10		=B6
11	7%
12	8%
13	10%

Рисунок 9 – Определение величины ежемесячных выплат с использованием таблицы подстановки

В ячейку В10 ввести ссылку на ячейку с формулой для расчета ежемесячных выплат (= В6). Для расчета выплат по каждой из ставок воспользуйтесь возможностью автоматической подстановки значений в нужную ячейку (в нашем случае в В1).

Для этого нужно: 1) Выделить диапазон А10:В13, включив в него значения процентных ставок и расчетную формулу (формула должна находиться в ячейке, расположенной правее и выше заданных значений).

2) В меню Данные выбрать Анализ «Что-если» и выбрать команду Таблица данных.

3) В поле «Подставлять значения по строкам в:» указать ячейку В1 (рис.10).

Рисунок 10 – Таблица подстановки

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

Чтобы создать сценарий, следует:

1. В меню Данные выбрать команду Анализ «Что-если», указав Диспетчер сценариев.

Появится окно «Диспетчер сценариев» (рис. 11).

Рисунок 11 – Диспетчер сценариев

---