Моделирование с помощью EXCEL.

 

Модель некоторого объекта или процесса — другой объект или процесс (реальный или абстрактный), который сохраняет существенные для целей моделирования свойства исходного объекта и в рамках этих целей полностью его заменяет.

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

EXCEL прекрасное средство для организации математических моделей того или иного типа. Лабораторные работы №8 и №9 знакомят студентов с физической моделью и имитационной. Для студентов экономического факультета имеет важное значение решение задач оптимизационного характера, решение оптимизационной модели. Это Лабораторная №10.

 

 

Практикум по ЕXCEL

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

Задание №1	Ввод текста, значений и простейших формул
	Выделить ячейку А10
	Ввести текст " мой первый опыт", нажать кл." Enter"
	В следующую ячейку ввести слово "задача"
	В две нижеследующие ячеки ввести значения 5 и 3
	В ячейку А14 введем формулу = А12+А13
		(адрес ячейки вводить латинскими символами)

Обратите внимание

- как вводятся текст и значения, к какой стороне ячейки прижимаются

- что выводится в ячейке после ввода формулы

- что произойдет со значением в ячейке А14, если изменить значения в ячейках А12 и А13

Задание	1.Ввести предлагаемый текст
№2	2.Выделить в каждом столбце одну или две ячейки (обведенные)
	3.Протянуть за маркер автозаполнениявниз на 10 ячеек
тема		март				-1		упр.1
						-0,9	0,1	упр.2

Самостоятельно:1) организовать список однокурсников и вывести его на листе с помощью автозаполнения 2) вывести рабочие дни текущего месяца, 3)организовать последовательность чисел от -5 до 5 с шагом 0,5

 

Задание	Рассчитать Цену (стоимость + 30%надбавка) и сумму по каждому типу факса
№3	1. Активизировать (выделить ячейку) D3
и 3а	2. Ввести формулу = C3+C3*30%
	3. Автозаполнением скопировать эту ячейку на нижеследующие
	4. Активизировать ячейку Н18
	5. Ввести формулу подсчета выручки ЦЕНА * КОЛИЧЕСТВО
	6. Автозаполнением скопировать эту ячейку на нижеследующие
	7. Подсчитать общий объем выручки с помощью автосуммы
		Факсы
		Модель#	Название	Стоимость	Цена	Количество	Выручка
		F100G	Персональный	1607,96
		F150G	Персональный
		F200G	Персональный Плюс	1729,55
		F250G	Персональный Плюс	2075,66
		F300G	Деловой	2550,55
		F350G	Деловой	2760,66
		F400G	Персональный	3512,8
		F450G	Персональный	3815,35
		F500G	Персональный Плюс	4878,34
		F550G	Персональный Плюс	5614,11
3а	Построить на отдельном листе круговую диаграмму с процентной долей каждого факса в общей выручке
	1.Выделить области A3:A12 и F3:F12(держа кл "Ctrl")
	2.Вставка - Диаграмма
	3. Тип: Круговая
	4. По кнопке "Далее" дойти до 3-его шага (Параметры Диаграммы)
	5. Вкладка "Подписи данных", щелкнуть на "Доля"
	6. Кнопка "Далее", щелкнуть "Отдельном",кнопка "Готово"
	7. Щелкнуть правой кнопкой мыши на корешке листа"Диаграмма 1", выбрать "Переименовать"
	8. Ввести имя " Доля прибыли"

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

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

 

Задание предназначено для закрепления навыков правильного ввода формул по правилам EXCEL.

Организовать данные на листе EXCEL следующим образом: столбец А символьные обозначения, столбец В расчетные. Расчет полупериметра сделать самим и сравнить с заданным результатом. После расчета углов треугольника проверить полученные результаты через сумму углов. Медианы, высоты и биссектрисы не могут быть больше всех трех сторон одновременно. В конце задания измените исходные данные сторон а, b и с. Проверьте и ответе на вопросы:

1. В каких случаях задача работает неправильно?
2. Можно ли подобную работу назвать программированием в EXCEL?

 

Даны три три стороны треугольникаa,b,c
Вычислить площадь по формуле Герона, где р-полупериметр
Вычислить по предложенным формулам следующие значения:
a
b
c
p		по формуле =СУММ(B4:B6)/2
S		по формуле =КОРЕНЬ(B7*(B7-B4)*(B7-B5)*(B7-B6))
r		по формуле
R		по формуле =B4*B5*B6/(4*B8)
ВЫЧИСЛИТЬ УГЛЫ
A		по формуле
B		B,C-аналогично
C
		Углы получите в радианах, в соседнем столбце переведите их в градусы, найдите соответствующую функцию в Математических функциях
ma		медианы по формуле
mb
mc
la		биссектрисы
lb
lc
ha		высоты
hb
hc

 

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

 

Задание	Создание собственного формата ячеек
№5	Выполнить последовательно:
		выделить массив В16: Е22
		ввести цифры температур (придумать)
		не снимая выделения, щелкнуть правой кнопкой мыши
		формат ячейки, вкладка "Число"
		(все форматы)
		Из строки все форматы выбрать 0
		щелкнуть лев. кнопкой мыши в окне "Тип:" после 0
		вставить " 'C"
		нажать <Enter>
Температура воздуха в городах мира с 12 по 18 декабря
	Лондон	Рим	Париж	Киев
понедельник
вторник
среда
четверг
пятница
суббота
воскресенье
Сравните свой результат с таблицей ниже
Температура воздуха в городах мира с 12 по 18 декабря
	Лондон	Рим	Париж	Киев
понедельник	10'C	15'C	5'C	0'C
вторник	12'C	13'C	8'C	-3'C
среда	7'C	10'C	4'C	2'C
четверг	8'C	14'C	6'C	4'C
пятница	4'C	8'C	5'C	2'C
суббота	-2'C	9'C	-4'C	-5'C
воскресенье	0'C	5'C	-2'C	3'C

 

Задание	Формат таблицы
№5а
Произведите автоформат таблицы
1. Выделить хотя бы одну ячейку в поле таблицы
2. В меню "Формат" выбрать "Автоформат (03), (07 и выше) вкладка Главная, Форматировать как таблицу
3. Среди списка форматов выбрать подходящий и ОК

 

	Лондон	Рим	Париж	Киев
понедельник	10'C	15'C	5'C	0'C
вторник	12'C	13'C	8'C	-3'C
среда	7'C	10'C	4'C	2'C
четверг	8'C	14'C	6'C	4'C
пятница	4'C	8'C	5'C	2'C
суббота	-2'C	9'C	-4'C	-5'C
воскресенье	0'C	5'C	-2'C	3'C

Самостоятельно 1) В Лабораторной работе №1 с помощью денежного формата поставьте единицы стоимости и сделайте свой формат на «штуки» в столбце Количество

 

Задание 6 Форматирование ячеек для оформления таблиц

Сделать при помощи формата ячеек сложную таблицу, в которой 1. объединить несколько ячеек А) 5ячеек по горизонтали, Б) 3 ячейки по вертикали, В)2 ячейки по горизонтали и 3 ячейки по вертикали, 2. Текст внутри ячейки расположить в несколько строк А)внутри необъединенной ячейки, б) внутри объединенной ячейки; 3.Текст расположить под разными углам, вертикально. 4. Расположить 1 символ (например. цифру) в центре объединенной ячейки (скажем 2*3ячейки).

5. Прорисовать все линии таблицы различными типами линий, различным цветом.

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

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

Абсолютный адрес ячеек.(описание «Кл.F4», смотри ниже)

Задание №4 Счет за ремонт квартиры

I.Ввести данные согласно приведенной ниже таблице

В какую ячейку?	Что вводить?
А1	Счет за ремонт квартиры
В3	Дата:
В4	Курс евро:
А6	№
В6	Наименование работ
D6	Стоимость работ
В7	Замена труб
В8	Замена ванны
B9	Настилка паркета
B10	Наклейка обоев
C11	Сумма:
B12	Ндс:
B13	СпецНалог:
C14	К оплате:
C3	21.02.96
C4	27,5
D7
D8
D9
D10
C12	0,2
C13	0,03

 

II.Последовательность выполнения дальнейших действий:

1.Выделить ячейку D11

2.На панели инструментов нажать значок S

(в ячейке отобразится результат суммы 4-х значений)

3.В ячейку D12 ввести формулу =D11*С12

4.В ячейку D13 ввести формулу=D11*С13

5.В D14 просуммировать 3 вышестоящих числа

выбрать функцию автосуммы S

выделить бегущей рамкой 3 ячейки

нажать кл. <Enter>

6.В яч С3 заменить дату на текущую

вставить функцию =СЕГОДНЯ(), отображающую текущую дату

7. В яч. С4 заменить значение курса

8. В диапазоне Е7:Е14 произвести пересчет выплат по курсу

вставить формулу =D7/$C$4 ($C$4-ссылка на фиксированную ячейку)

скопировать формулу на весь диапазон

9. В D7:D14 ввести знак рубля, в Е7:Е14 знак евро

используя Формат ячейки, категория Денежный

10.Сверить результат со счетом, расположенным ниже

 

Счет за ремонт квартиры
	Дата:	16.10.00
	Курс евро:	27,5
№	Наименование работ		Стоимость работ
	Замена труб		560 000,00р.	?20 363,64
	Замена ванны		250 000,00р.	?9 090,91
	Настилка паркета		3 560 000,00р.	?129 454,55
	Наклейка обоев		450 000,00р.	?16 363,64
		Сумма:	4 820 000,00р.	?175 272,73
	Ндс:	20%	964 000,00р.	?35 054,55
	СпецНалог:	3%	144 600,00р.	?5 258,18
		К оплате:	5 928 600,00р.	?215 585,45

 

 

Клавиша F4 производит переход из одного типа адреса в другой:

§ A1 – относительный,

§ $A$1– абсолютный (фиксируется адрес одной определенной ячейки)

§ $A1– смешанный (фиксируется ссылка на определенный столбец, а ссылка на строку смещается вслед за формулой),

§ A$1– смешанный (фиксируется ссылка на определенную строку, а ссылка на столбец смещается вслед за формулой)

Самостоятельно: Рассчитать сумму к выдаче в ведомости по зарплате, взяв подоходный налог 12%, пенс.налог 1%, премия 50%, организовав их в строках выше таблицы, налоги берутся от суммы оклада и премии. Правило ввода абсолютного адреса смотреть в лекциях или пособии по EXCEL.

Пенсионный налог	1%
Подоходный налог	12%
Премия	50%

 

№ п/п	Ф.И.О.	Оклад	Премия	Подоходный	Пенсионный	К выдаче
				налог	налог
	Иванов
	Петров

 

Смешанные ссылки: решить следующую задачу, копируя формулы из столбца D в F,H

	A	B	C	D	E	F	G	H
	Наименование товара	Закупочная цена	Магазин 1	Магазин 2	Магазин 3
	К-во	К оплате	К-во	К оплате	К-во	К оплате
	Молоко	5р		=$B3*C3
	Кефир	4р		=$B4*C4
	Масло(пачка)	8р		=$B5*C5
	Сметана	12р		=$B6*C6
	И т.д.

Самостоятельно 1) Построить таблицу умножения вида,

			…
			…
			…
…	…	…	…	…	…
			…
			…

организовать ее таким образом,чтобы множители можно было задавать начиная с любого числа и с произвольным шагом

 

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

P1=P0*(1+r)^n, где P0 – Сумма, размещенная на депозите, r – ставка по депозиту, n – число периодов (лет).

Рассчитать на 10 лет и 10 разных ставок процента, используя все возможные ссылки.

Сумма (денежная единица)
Начальное значение процента	5%
Начальное значение периода времени(лет)
Шаг изменения (%)	0,50%
Шаг изменения (лет)
Процент	Годы

 

3) Решить задачу определения реальной стоимости денег по формуле:

PV=P0*(1+r)^n/(1+j)^n, где P0 – Сумма, размещенная на депозите, r – ставка по депозиту, n – число периодов (лет), j- процент инфляции

 

Область ввода	Процент	Инфляция
Сумма (денежная единица)
Начальное значение процента	5%	3%
Начальное значение периода времени(лет)
Шаг изменения (%)	1,00%	0,50%
Шаг изменения (лет)
Процент	Инфляция	Годы

 

 

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

 

Задание Рассчитать ежемесячные платежи по представленным данным

Сумма кредита
Срок
Процент	15%
Ежемес. платеж

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

2. Выбираем категорию Финансовые

3. Выбираем функцию ПЛТ

4. Задаем аргументы (см рисунок) Ставка – процентная ставка за месяц = годовая ставка/12, Кпер – количество платежей за весь срок=срок*12, ПС –сумма кредита, БС –будущий результат, которого достигаем после всех выплат: долг=0 ,тип стандартно =0 (плата через месяц после взятия кредита.

 

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

 

Задача. Компании необходима сумма в $140 000,взятая в кредит на 5 лет. Банк согласен предоставить ссуду под 8,5%, с ежемесячной выплатой.По договоренности последний платеж должен составить $10 000.Определить величину ежемесячных платежей.
		График аммортизационных выплат
	Заем	Месяц	Начало	Доход	Осн.Сум.	Конец
Кредит	$140 000	апр.98	$140 000,00	$991,67	-$991,67	$140 991,67
Последний платеж	$10000	май.98	$140 991,67
Процент	8,50%	июн.98	$0,00
Срок		июл.98	$0,00
Платеж
Вначале высчитайте ежемесячный "платеж" с помощью функции ПЛТ, где бс = последнему платежу 1.В яч.F6 ввести формулу "=В6",т.е. Начало= Кредиту 2.В яч.G6 формулу=F6*$B$8/12(подсчет ежемес.%-та банку) 3.В яч.H6 Формулу=-$B$10-G6 (сумма на погашение кредита) 4.В след. яч. Формулу= начало- Осн.Сумма(остаток кредита) 5.В яч.F7 ввести =I6(остаток переходит на начало след. Месяца) 6. Все формулы скопировать в нижеследующие ячейки. 7.Довести таблицу до марта 2003 8.Убедитесь, что на конец марта 2003 года осталась сумму в10 000 9. Определите полную прибыль банка за 5 лет

Лабораторная работа №6.

Подбор параметра

Пример: необходимо вычислить, сколько нужно взять сырого замороженного мяса, чтобы приготовить 400 порций жареного (порция 100г), если известно, что при оттаивании теряется 5%веса, при разделке 15%, при горячей обработке 25%? Сначала посчитаем результат 400порций по 100г это 40кг жареного мяса. Затем сделаем расчет выхода жареного мяса из 1кг сырого

сырое мясо		1,000 кг
	Потери в%	выход
оттаивание	5%	0,950 кг
разделка	15%	0,808 кг
горячая обработка	25%	0,606 кг

Выделив ячейку С5 запускаем через меню СЕРВИС средство Подбор параметра, указывая нужное значение 40 и ячейку, которая отвечает за вес сырого мяса

Получим результат

Задание для самостоятельной работы. 1.Используя Лабораторную работу №5 определить при помощи Подбора параметра 1) Какой кредит мы можем взять, если ежемесячный платеж будем совершать в сумме 2000р(3000р,5000р)? За какой срок выплатим всю сумму кредита, если ежемесячный платеж будем совершать в сумме 2000р(3000р,1500р)?

2. При каком значении Х функция Y(X)=X³-X²+4 принимает значение равное 2?

Лабораторная работа №7.

Двумерная таблица подстановки

Пример: Построить таблицу значений функции у=k*sin(x) при х меняющимся от –5 до 5 с шагом 0,5 и K меняющимся от 1 до 5 и график этих функций.

Решение Подготовим данные раположив их следующим образом

формула. находящаяся в ячейке В2 =В1*SIN(В2)

к0
х0		0,841470985
		-5
		-4,5
		-4
		-3,5
		…
		4,5

Далее:

5) выделить диапазон, подсвеченный серым цветом

6) вызвать Данные, Таблица Подстановки

7) Поставить требуемые ячейки

a) по столбцам - ячейку содержащую значение К0(В1)

b) по строкам –ячейку содержащую значение Х0 (В2)

8) нажать ОК и получим результат вычислений

к0
х0		0,841470985
		-5	0,958924	1,917848549	2,876773	3,835697	4,794621
		-4,5	0,97753	1,955060235	2,93259	3,91012	4,887651
		-4	0,756802	1,513604991	2,270407	3,02721	3,784012
		-3,5	0,350783	0,701566455	1,05235	1,403133	1,753916
		-3	-0,14112	-0,282240016	-0,42336	-0,56448	-0,7056
		-2,5	-0,59847	-1,196944288	-1,79542	-2,39389	-2,99236
		-2	-0,9093	-1,818594854	-2,72789	-3,63719	-4,54649
		-1,5	-0,99749	-1,994989973	-2,99248	-3,98998	-4,98747
		-1	-0,84147	-1,68294197	-2,52441	-3,36588	-4,20735
		-0,5	-0,47943	-0,958851077	-1,43828	-1,9177	-2,39713
		0,5	0,479426	0,958851077	1,438277	1,917702	2,397128
			0,841471	1,68294197	2,524413	3,365884	4,207355
		1,5	0,997495	1,994989973	2,992485	3,98998	4,987475
			0,909297	1,818594854	2,727892	3,63719	4,546487
		2,5	0,598472	1,196944288	1,795416	2,393889	2,992361
			0,14112	0,282240016	0,42336	0,56448	0,7056
		3,5	-0,35078	-0,701566455	-1,05235	-1,40313	-1,75392
			-0,7568	-1,513604991	-2,27041	-3,02721	-3,78401
		4,5	-0,97753	-1,955060235	-2,93259	-3,91012	-4,88765
			-0,95892	-1,917848549	-2,87677	-3,8357	-4,79462

 

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

 

Задание 1. Построить таблицы значений и графики следующих функций

А) у=(х+к)²-1) при х меняющимся от –5 до 5 с шагом 0,5 и K меняющимся от 1 до 5

Б) у=соs(х-к) при х меняющимся от –5 до 5 с шагом 0,5 и K меняющимся от 1 до 5

В) у=к*х³ при х меняющимся от –2 до 2 с шагом 0,2 и K меняющимся от 1 до 5

Задание 2. Решить с помощью таблицы подстановки следующую задачу.

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

При необходимости смотри файл «Задания для заочников»

Лабораторная работа №8.

Рассмотрим процесс построения и исследования модели на конкретном примере движения тела, брошенного под углом к горизонту.

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

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

Из условия задачи можно сформулировать следующие основ­ные предположения:

• мячик мал по сравнению с Землей, поэтому его можно счи­тать материальной точкой;

• изменение высоты мячика мало, поэтому ускорение свобод­ного падения можно считать постоянной величиной g = 9,8 м/с² и движение по оси Y можно считать равноуско­ренным;

• скорость бросания тела мала, поэтому сопротивлением воз­духа можно пренебречь и движение по оси X можно считать равномерным.

Формальная модель. Движение мячика по оси X равномер­ное, а по оси Y равноускоренное, поэтому для формализации модели используем известные из курса физики формулы равно­мерного и равноускоренного движения. При заданных началь­ной скорости v₀ и угле бросания а значения координат дально­сти полета х и высоты у от времени можно описать следующими формулами:

х = v₀ • cosa • t,

у = v₀-sina-t - g • t²/2.

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

v₀ • sina • t - g • t² /2 = 0,

t • (v₀ • sina - g • t/2) = 0.

Значение времени t = 0 не имеет физического смысла, поэтому:

v₀ • sina - g • t/2 = 0, t = (2 • v₀ • sina) /g.