Лабораторная работа №1
Основные приемы работы в Microsoft Excel. Формулы и функции.
Формулы представляют собой выражения, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=).
Формула также может включать следующие элементы: функции, ссылки, операторы и константы.
Функции — заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Например, функция ОКРУГЛ(А10) округляет число в ячейке A10.
Ссылка указывает на ячейку или диапазон ячеек листа и передает в Microsoft Excel сведения о расположении значений или данных, которые требуется использовать в формуле. При помощи ссылок можно использовать в одной формуле данные, находящиеся в разных частях листа, а также использовать в нескольких формулах значение одной ячейки. Кроме того, можно задавать ссылки на ячейки других листов той же книги и на другие книги. Ссылки на ячейки других книг называются связями
Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется.
Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется.
Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.
Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
Операторы. Оператор ^ возводит число в степень, а звездочка (*) выполняет умножение.
Задание 1. Выполните расчет выручки, всех издержек и прибыли с помощью Microsoft Excel. Постройте графики AVC, ATC и МС (диаграмма 1) и ТС и TR (диаграмма 2).
| Q | P | TC |
Лабораторная работа №2
Решение задач оптимизации с помощью надстройки Поиск решения
Многие экономические задачи (максимизация выпуска при ограниченных ресурсах, максимизация полезности при ограниченном доходе и т.д.) сводятся к широкому классу задач оптимизации, для решения которых применяются математические методы.
В МЕ существует возможность с помощью надстройки Поиск решения найти оптимальное при данных ограничениях решение.
Для этого в меню Сервис выберите команду Поиск решения. В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу. Установите переключатель в положение максимального, минимального или конкретного значению;
В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.
В поле Ограничения введите все ограничения, накладываемые на поиск решения
ПРИМЕР.
Функция полезности имеет вид U(x,y)=xayb, а располагаемый доход потребителя: Pxx + Pyy = I. Определите оптимальный объем потребляемых благ x, y по следующим данным:
Рис. 7. Постановка задачи оптимизации
Рис. 8. Установка необходимых параметров задачи в окне Поиск решения
Рис. 9. Результат расчета надстройки Поиск решения
Задание 1: Решите задачу максимизации полезности по следующим ниже исходным данным:
| Вариант | a | b | px | py | I |
| 1/2 | 1/2 | ||||
| 1/2 | 1/4 | ||||
| 1/2 | 1/2 | ||||
| 1/2 | 1/3 | ||||
| 1/2 | 1/4 | ||||
| 1/4 | 3/2 | ||||
| 1/4 | 3/4 | ||||
| 1/2 | 3/2 | ||||
| 1/2 | |||||
| 1/2 | |||||
| 1/4 | 1/4 | ||||
| 1/4 | 1/2 | ||||
| 1/2 | 1/2 | ||||
| 3/2 | 1/4 | ||||
| 1/2 | 1/4 | ||||
| 1/4 | |||||
| 1/4 | |||||
| 1/2 | 3/2 | ||||
| 1/2 |
Лабораторная работа №3
Подбор параметра. Таблица подстановки.
Средство MS Excel Подбор параметра позволяет определить значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке (ячейке результата).
Задание 1. Вас просят дать в долг Р рублей и обещают вернуть через год F1 руб., через два - F2 руб., через три - F3. При какой процентной ставке эта сделка выгодна?
| Вариант | Р | F1 | F2 | F3 |
| 100 000 | 50 000 | 30 000 | 30 000 | |
| 100 000 | 60 000 | 30 000 | 30 000 | |
| 100 000 | 40 000 | 50 000 | 40 000 | |
| 100 000 | 30 000 | 40 000 | 50 000 | |
| 50 000 | 30 000 | 20 000 | 20 000 | |
| 50 000 | 10 000 | 30 000 | 30 000 | |
| 50 000 | 20 000 | 30 000 | 20 000 | |
| 50 000 | 40 000 | 10 000 | 10 000 | |
| 80 000 | 40 000 | 30 000 | 20 000 | |
| 80 000 | 40 000 | 30 000 | 20 000 | |
| 80 000 | 50 000 | 20 000 | 20 000 | |
| 70 000 | 20 000 | 30 000 | 40 000 | |
| 60 000 | 40 000 | 20 000 | 10 000 | |
| 60 000 | 25 000 | 30 000 | 15 000 | |
| 60 000 | 30 000 | 30 000 | 10 000 | |
| 60 000 | 10 000 | 20 000 | 50 000 | |
| 30 000 | 5 000 | 25 000 | 5 000 | |
| 30 000 | 20 000 | 10 000 | 10 000 | |
| 30 000 | 10 000 | 20 000 | 5 000 | |
| 30 000 | 5 000 | 10 000 | 20 000 |
При решении задачи следует использовать функцию ЧПС и команду Сервис/Подбор параметра.
Функция ЧПС возвращает величину чистой приведенной стоимости инвестиции, используя ставку дисконтирования, а также стоимости будущих выплат (отрицательные значения) и поступлений (положительные значения).
ЧПС(ставка;значение1;значение2;...)
Ставка — ставка дисконтирования за один период.
Значение1, значение2,... — от 1 до 29 аргументов, представляющих расходы и доходы.
Рабочий лист с решением поставленной задачи представлен на рис. 10.
Рис. 10. Решениезадачи с помощью команды Подбор параметра
Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных.
Задание 2. Рассчитайте сумму вклада с помощью таблицы подстановки в зависимости от сроков (от 1 до 10 лет) и процентных ставок (от 5% до 15%). Первоначальную сумму вклада взять из предыдущей задачи.
Расчет сложных процентов осуществляется по формуле:
, где F – будущая стоимость вклада, P – первоначальная стоимость вклада, r – годовая номинальная ставка, n – количество лет
Сначала создадим таблицу для входных данных, используем конкретные значения процентной ставки и срока вклада, входящие в указанный интервал (например, 5 лет и 12%). Таблица представлена на рис. 11.
Рис. 11. Подготовка данных задачи
Далее нужно подготовить диапазон для использования двухмерной таблицы подстановки. Изменяемые данные поместить в левый столбец и верхнюю строку (в нашем случае это – значения процентной ставки и срока вклада). На пересечении строки и столбца в левом верхнем углу поместить необходимую формулу или ссылку на нее с обязательной абсолютной адресацией ячеек.
Выделите диапазон ячеек, содержащей формулу для расчета, изменяемые данные для двух переменных и ячейки для результатов вычислений. Далее выполните команду Данные/Таблица подстановки, в появившемся окне указать, куда и какие значения необходимо подставлять (см. рис. 12).
Рис. 12 Использование таблицы подстановки
Результаты вычислений будут помещены в незаполненные ячейки выделенного диапазона (см. рис. 13).
Рис. 13. Рассчитанные данные с использованием двухмерной таблицы подстановки
ЭЛЕКТРОННЫЙ АДРЕС ПРЕПОДАВАТЕЛЯ:
vyakina@yahoo.com
ПРИЛОЖЕНИЕ 1