Лабораторная работа № 2. Построение диаграмм и графиков функций.

Лабораторная работа № 7. Работа с массивами

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

Массив – прямоугольный диапазон формул или значений, которые MS Excel обрабатывает как единую группу. Прямоугольный числовой массив, состоящий из mстрок и nстолбцов, принято называть матрицей размерности m n.

При работе с массивами необходимо помнить следующие два основных правила:

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

2. По окончании ввода формулы нажимать комбинацию клавиш CTRL+SHIFT+ENTER.

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

Функция ТРАНСП находится в категории «Ссылки и массивы», а все остальные – в категории «Математические».

Задание 1. Работа с массивами

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

2. Найти произведение матриц:

3. Вычислить определители матриц из п. 1)

4. Найти матрицы, обратные к матрицам из п.1)

5. Решите систему уравнений методом обратной матрицы

Задание 2. Решить систем уравнений

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

Задание 3. Решить задачу, используя функции работы с массивами и матрицами.

Ателье выпускает три вида изделий: брюки, юбки, жилеты. При этом используется два вида тканей: шерстяная и подкладочная. Норма расхода тканей характеризуется матрицей A (таблица).

Определить:

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



b) общую стоимость тканей (S), если известна их цена (С)

Задание 1.

Корпорация «А» планирует покупку земельного участка стоимостью 1000000 рублей. Какой должна быть величина ежегодного взноса для создания соответствующего фонда в течение 10 лет, если процентная ставка равна 5% годовых?

Указание. Условиями данной операции первоначальной суммы на вкладе в момент времени t = 0 не предусмотрено. Поэтому ПС (начальное значение вклада) принимаем равным 0.

Задание 2.

Работник решил уйти на пенсию. Фирма, в которой он работает, предлагает ему два варианта выплаты пенсии: 1) в виде единовременного пособия в 55000 руб.; 2) ежегодную выплату 10000 руб. на протяжении 10 лет. Какой вариант пенсии можно посоветовать выбрать при ставке депозита 10%?

Задание 3.

Клиент банка осуществил заем в размере $5000 под 6% годовых на 6 месяцев. Определите ежемесячные платежи клиента, при условии их осуществления в конце каждого периода.

Кредит погашается одинаковыми платежами, равномерно распределенными во времени. Возникающие при этом денежные потоки также являются аннуитетами.

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

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

Рис. 22. Образец оформления задачи о погашении кредита

Алгоритм расчета основных показателей:

1. Величина периодического платежа: используемая функция – ПЛТ.

2. Баланс на начало периода: для первого периода это начальная сумма кредита, для всех остальных – баланс на конец предшествующего периода.

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

4. Выплата по основному долгу: используемая функция – ОСПЛТ.

5. Выплата по процентам: используемая функция – ПРПЛТ

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

Задание 4. Разработка плана погашения кредита

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

Задание 1.

Вас просят дать в долг 10000 рублей, обещая вернуть через год – 2000, через 2 – 4000, через 3 – 7000. При какой годовой процентной ставке эта сделка имеет смысл?

В ячейку В7 (рис.) вводится формула для расчета чистого приведенного значения вклада с функцией ЧПС(ставка, 1-е значение, 2-е значение,…), аргумент которой «ставка» – ссылка на ячейку годовой учетной ставки (она пока пустая). Затем вызывается Подбор параметра.

Рис. Процедура Подбор ____________параметра

Лабораторная работа №15.
Совместная работа в приложениях MS Office.

Задание 1

Используя возможности текстового редактора WORD и табличного процессора EXCEL и указания к работе, создайте документ по приведенному образцу:

1. Находясь на странице документа Word, установить все поля страницы по 2 см.

2. Набрать текст, соблюдая точный повтор оригинала, до абзаца «Состав и количество затрат на питание представлен на диаграмме:» включительно.

3. Для создания диаграммы перейти в EXCEL и создать таблицу следующего содержания:

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

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

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

7. Вставить построенные диаграммы в документ WORD.

8. Закончить создание документа по образцу.

Лабораторная работа №16.
Элементы автоматизации с помощью макросов

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

Задача 1. Записать макрос

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

1. Добавить вкладку Разработчик на ленту команд: Меню MS Office/Параметры Excel/Показывать вкладку Разработчик на ленте команд – поставить флажок. Для активизации макрорекордера выбрать команду Разработчик/Запись макроса. Появится диалоговое окно Запись макроса, которое позволит задать параметры макроса.

2. Задать имя макроса: СоздатьОтчет. Сохранить в – Эта книга Задать описание: Создание рабочей книги с отчетной таблицей. Нажмите ОК. Теперь все производимые действия будут записываться до тех пор, пока не будет выбрана команда Разработчик/Остановить запись.

3. Последовательность действий, которую запишет макрорекордер:

a. Меню MS Office/Параметры Excel/Основные/раздел «При создании новых книг»: Число листов – 1/ОК.

b. Меню MS Office/Создать/Новая книга/кнопка Создать.

c. Переименовать ярлык Лист 1 в Отчет.

d. Выделить ячейку А2 и ввести в нее Отдел закупок.

e. Выделить ячейку А3 и ввести в нее Отдел рекламы.

f. Выделить ячейку А4 и ввести в нее Итого.

g. Двойным щелчком по границе столбцов А и В подберите ширину столбца А.

h. Выделить ячейку В1 и ввести в нее Расходы.

i. В ячейку В4 ввести формулу: =СУММ(В2:В3).

j. Выделить диапазон А1:В4, на вкладке Главная в разделе Стиль ячеек выбрать любые стили (можно поочередно применить несколько, например «Хороший» и «Примечание»). Когда таблица примет нужный вид – остановите запись макроса: Разработчик/Остановить запись.

4. Для выполнения только что записанной процедуры выберите команду Разработчик/Макросы, отобразится диалоговое окно Макросы. В нем выберите имя нашего макроса и нажмите кнопку Выполнить. Если нажать кнопку Изменить, то на экране отобразится окно редактора VBA с активизированным стандартным модулем, в котором будет код только что записанного макроса. Ничего менять не будем, закрываем окно модуля.

Если все выполнено верно, то после выполнения команды Выполнить будет создана еще одна рабочая книга с одним листом, названным «Отчет», на котором буде размещена заданная таблица. Введите данные в столбец В (например В2 - 1000 и В3 - 1200). Сумма будет подсчитана автоматически.

Макрос можно назначить кнопке, созданной с помощью панели инструментов «Элементы управления формы». Для этого выполним последовательность действий:

1) Разработчик/Вставить/ Элементы управления формы.

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

3) В окне Назначить макрос объекту выбрать имя нашего макроса и нажать ОК.

4) Поместите указатель мыши в центр кнопки и дважды щелкните левой кнопкой.

5) Сотрите имя «Кнопка 1» и введите имя кнопки «СоздатьОтчет», аналогичное имени макроса.

6) Проверьте работу макроса нажатием на созданную кнопку.

Задача 2.

Выполните создание следующих макросов:

1) макроса, выполняющего добавление в рабочую книгу листа;

2) макроса, вставляющего диаграмму (гистограмму) для любого ряда данных;

3) макроса, который выполняет построение графика функции .

Назначьте их кнопкам и проверьте работу макросов.

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

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

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

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

предшествуют поступлениям или его притоку.

Рис. Пример потоков инвестиционного проекта

Критерии оценки эффективности инвестиционных проектов

Базой для расчета показателей эффективности являются так называемые чистые денежные потоки (Net Cash-Flow, NCF), включающие в себя выручку от реализации, текущие и инвестиционные затраты, прирост потребности в оборотном капитале и налоговые платежи. Название «чистые потоки» говорит о том, что потоки не учитывают схему финансирования – вложение собственных средств и привлечение кредитных ресурсов. Без этого вложения денежный поток проекта будет, естественно, получаться отрицательным на начальном этапе и накопленные денежные средства будут выглядеть так, как это показано на рисунке.

Рис. Чистые и дисконтированные денежные потоки

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

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

где i – номер года проекта, а d – ставка дисконтирования. То есть будущие денежные потоки «обесцениваются» для инвестора с годовыми темпами, равными ставке дисконтирования.

Основными показателями эффективности инвестиций являются:

1) срок окупаемости – Ток;

2) чистый приведенный доход – NPV;

3) внутренняя норма доходности – IRR;

4) индекс прибыльности (рентабельности) – PI.

Срок окупаемости

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

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

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

Чистый приведенный доход (чистая приведенная стоимость) – NPV

Инвестиции оправдают себя только в том случае, если принесут новые ценности для инвестора. Чистая приведенная стоимость – это разность между рыночной стоимостью проекта и затратами на его реализацию. В EXCEL существует специальная функция, выполняющая операцию расчета NPV – ЧПС. Аргументы этой функции – значение процентной ставки за период и значения денежных потоков каждого периода. Применение функции может идти несколькими способами. Наиболее предпочтительно в качестве аргументов брать только поступления, а начальные вложения добавлять к рассчитанной приведенной стоимости поступлений. Разумеется, вложения должны быть отражены со знаком «плюс».

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

В нашем примере NPV= $795,44. Значит, по этому показателю проект также можно будет принять.

Внутренняя норма доходности

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

В EXCEL существует функция ВСД, которая позволяет вычислить IRR.

Основное правило IRR: если внутренняя норма доходности превышает запланированную доходность или доходность от альтернативных вложений, то проект стоит принять. Если наоборот – проект стоит отклонить.

В нашем примере IRR=30%, следовательно, по данному показателю проект тоже стоит принять.

Индекс прибыльности

Это отношение приведенной стоимости будущих денежных потоков от реализации инвестиционного проекта к приведенной стоимости первоначальных инвестиций. Его следует понимать, как дополнительную ценность, созданную на каждый вложенный рубль. При положительном значении NPV PI>1, при NPV< 0 PI<1. В нашем примере PI=1, 7647059.

При решении вопроса о целесообразности инвестиций пользуются

следующей сводной таблицей:

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

Образец оформления отчета по расчетной работе

Контрольная работа по дисциплине «Экономическая информатика»

Вариант 1

Вариант 2

Вариант 3

Вариант 4

Вариант 5

Вариант 6

Лабораторная работа № 2. Построение диаграмм и графиков функций.

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

Задание 1. Построение диаграмм.

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

Рис. 4. Построение диаграмм

2. Выделить значения столбцов Приход и Расход без заголовков.

3. Выполнить команду Вставка/Гистограмма, а затем, не снимая выделения с диаграммы, команду Конструктор/Выбрать данные.

4. В открывшемся диалоговом окне:

a) В категории «Элементы легенды (ряды)» выделить Ряд 1, нажать «Изменить», выделить ячейку с заголовком «Приход», нажать ОК новое имя ряда «Приход» появится в диалоговом окне и на диаграмме. По аналогии Ряд 2 переименовать в «Расход».

b) В категории «Подписи горизонтальной оси (категории)» нажать «Изменить» и выделить диапазон ячеек со значениями годов, ОК, ОК (рис. 4).

5. Не снимая выделения с диаграммы, перейти в меню Формат и внести изменения в категориях Стили WordArt и Стили фигур, по одному из параметров диаграммы (по выбору) в каждой категории. Гистограмма готова. Снять выделение.

6. Выделить значения ряда «Приход» (без заголовка).

7. Выполнить команду Вставка/Круговая диаграмма, а затем, не снимая выделения с диаграммы, команду Конструктор/Выбрать данные.

8. В открывшемся диалоговом окне:

a) В категории «Элементы легенды (ряды)» выделить Ряд 1, нажать «Изменить», выделить ячейку с заголовком «Приход», нажать «ОК», после чего новое имя ряда «Приход» появится в диалоговом окне и на диаграмме.

b) В категории «Подписи горизонтальной оси (категории)» нажать «Изменить» и выделить диапазон ячеек со значениями годов, ОК, ОК.

9. Не снимая выделения, выполнить команду Конструктор/Макеты диаграмм и выбрать в перечне третий образец во втором ряду. Круговая диаграмма готова. Снять выделение (рис. 4).

10. Выделить значения ряда «Расход» (без заголовка).

11. Выполнить команду Вставка/График, а затем, не снимая выделения с диаграммы, команду Конструктор/Макеты диаграмм и выбрать первый образец в списке.

12. В получившейся диаграмме выделить надпись «Название диаграммы», удалить шаблонное название и написать «Расход». Затем выделить надпись «Название оси», удалить шаблонное название и написать «Млн. руб.».

13. Правой кнопкой мышки щелкнуть по подписям оси ОХ (вызов контекстного меню), выбрать пункт «Выбрать данные».

14. В диалоговом окне изменить название ряда «Ряд 1» на «Расход», а по горизонтальной оси сделать подписи соответствующих годов.

15. Правой кнопкой мыши щелкнуть по ряду данных на диаграмме и выбрать «Добавить подписи данных».

16. Правой кнопкой мыши щелкнуть по ряду данных на диаграмме и выбрать «Добавить линию тренда». Ничего не меняя в открывшемся окне, нажать «Закрыть». График с линией тренда построен. Снять выделение (рис. 4).

17. Внесите изменения в построенную круговую диаграмму. Выделите один из секторов диаграммы, щелкните по выделенному сектору правой кнопкой мыши и выберите команду Формат точки данных/Заливка, поставьте переключатель «Сплошная заливка» и выберите новый цвет сектора.

18. Выделите гистограмму и скопируйте в Буфер Обмена. Выполните команду Вставить.

19. Внести изменения в копию гистограммы. Для этого правой кнопкой мыши щелкнуть по рядам данных на диаграмме и выбрать пункт Выбрать данные.

20. В категории «Элементы легенды (ряды)» нажать кнопку «Добавить», дать новому ряду имя «Приход фирмы» и выделить значения ряда «Приход» (без заголовка). Щелкнуть правой кнопкой мыши по новому ряду на диаграмме и выбрать «Изменить вид ряда данных» и выбрать «График с маркерами» первого вида. Добавить на новом ряду подписи данных.

21. Аналогичные действия проделайте с добавлением ряда «Расход фирмы» (рис. 4).

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

Построить график функции на отрезке с шагом 0,1.

1. Построим таблицу, состоящую из ряда значений аргумента Х, значений функции Y, начального значения (НЗ) и шага (рис. 5). Значения НЗ и шага вводятся с клавиатуры. При этом на рабочем листе необходимо создать три формулы:

a) в ячейке А2: =С2 (т.е. первое значение в ряду Х равно начальному значению).

b) в ячейке А3: =А2+$D$2 и скопировать формулу вниз до достижения значения 1.

c) в ячейке В2: =ABS(A2-3)*COS(ПИ()*A2^2) и скопировать формулу вниз по столбцу.

Рис. 5. Построение графика функции

2. Выделить ряды X и Y вместе с заголовками и выполнить команду Вставка/Точечная, выбрать вид гладкой кривой без маркеров.

3. Изменить вид диаграммы, согласно образцу (рис. 5).

4. Построить графики следующих функций с шагом 0,1.

а)

б)

в)

Лабораторная работа № 3.
Применение смешанных ссылок. Построение поверхностей.

Смешанные ссылки – это ссылки вида $NN или N$N, то есть те, в которых в отличие от абсолютных защищены от копирования только имя столбцаили только номер строки, а не то и другое одновременно. Данные ссылки применяются при построении формул, последующее копирование которых происходит одновременно как вниз по столбцам, так и вправо по строкам.

Задание 1. Применение смешанных ссылок

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

1. Создать таблицу по образцу (рис. 6). В столбце «Итого по году» и строке «Итого по заводу» провести вычисления.

Рис. 6. Данные об авариях

2. Ниже создать две таблицы для проведения статистического анализа по образцу (рис. 7).

Рис. 7. Таблицы для статистического анализа

3. В первой таблице за 100% берется итоговое количество аварий в текущем году. Для каждого года эта величина различна, но универсальная формула должна быть введена в левую верхнюю ячейку таблицы (выделена в таблице), затем скопирована вниз и вправо. Для этого применим смешанную ссылку, в которой будет зафиксировано имя столбца (Итого по году). В формуле выполняется деление текущего значения аварии (Альфа, 2005 – ссылка относительная) на итоговое по году (Итого по году 2005 – ссылка смешанная). Затем формула копируется вниз и вправо. По строке суммарно должно получаться 100%.

4. Аналогично заполняется вторая статистическая таблица. Но в ней при создании аналогичной формулы необходимо фиксировать номер строки (Итого по заводу). При верном выполнении задания сумма по каждому столбцу будет равна 100%.

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

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

Построить поверхность: .

1. На рабочем листе создать диапазоны изменения x и y от -20 до 20 по образцу на рис. 8 (приведен фрагментарно, строка – значения x, столбец – значения y):

Рис. 8. Фрагмент таблицы для построения поверхности

2. В ячейку В2 (на рис. 8 она выделена темным фоном) ввести формулу, ссылающуюся на ячейки В1 и А2, как на значения x и y, причем ссылки на данные ячейки смешанные.

3. Скопировать формулу вниз и вправо, затем выделить все полученные числовые данные, вызвать мастер диаграмм, выбрать тип диаграммы «Поверхность», вид – первый. Результат приведен фрагментарно на рис. 9.

Рис. 9. Построение поверхности

4. Построить поверхности: а) при , шаг 0,1. б) при , шаг 0,1