2.1. Варианты индивидуальных заданий

Распределение заданий по вариантам Таблица 2

Номер варианта	Номер задания	Номер варианта	Номер задания
1	1.1; 2.1; 3.1; 4.1; 5.1	11	1.11; 2.3; 3.4; 4.5; 5.1
2	1.2; 2.2; 3.2; 4.2; 5.2	12	1.12; 2.4; 3.5; 4.6; 5.2
3	1.3; 2.3; 3.3; 4.3; 5.3	13	1.13; 2.5; 3.6; 4.7; 5.3
4	1.4; 2.4; 3.4; 4.4; 5.4	14	1.14; 2.6; 3.7; 4.8; 5.4
5	1.5; 2.5; 3.5; 4.5; 5.5	15	1.15; 2.7; 3.8; 4.9; 5.5
6	1.6; 2.6; 3.6; 4.6; 5.6	16	1.16; 2.8; 3.9; 4.10; 5.6
7	1.7; 2.7; 3.7; 4.7; 5.7	17	1.17; 2.9; 3.10; 4.1; 5.7
8	1.8; 2.8; 3.8; 4.8; 5.8	18	1.18; 2.10; 3.1; 4.2; 5.8
9	1.9; 2.9; 3.9; 4.9; 5.9	19	1.19; 2.1; 3.2; 4.3; 5.9
10	1.10; 2.10; 3.10; 4.10; 5.10	20	1.20; 2.10; 3.6; 4.7; 5.10

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

Задание 1. Вычисление выражений. Использование функций.

Краткие теоретические сведения

1.9. Использование стандартных функций

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

Палитра формул

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

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

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

1. Выберите команду: Вставка/Функция ( рис. 9). На экране появится первая страница диалогового окна Мастер функций

Рис.9. Команда Вставка / Функция

2. Выберите в списке Категория нужный тип функции, затем активизируйте в списке Функция нужную функцию (рис. 10).

Рис.10. Окно мастера функций (шаг1 из 2)

3. Для перехода ко второй странице щелкните по кнопке ОК. Появится диалоговое окно, которое выводит доступные для функции аргументы (рис.11).

Рис.11. Окно мастера функций ( аргументы функции)

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

5. Чтобы ввести функцию в активную ячейку, выберите кнопку ОК. Функция с аргументами будет вставлена в ячейку.

Итоговые вычисления

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

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

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

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

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

Функции для итоговых вычислений

Прочие функции для итоговых вычислений выбираются обычным образом, с помощью раскрывающегося списка в строке формул или с использованием мастера функций. Все эти функции относятся к категории Статистические. В их число входят функции ДИСП (вычисляет дисперсию), МАКС (максимальное число в диапазоне), СРЗНАЧ (среднее арифметическое значение чисел диапазона), СЧЕТ (подсчет ячеек с числами в диапазоне) и другие.

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

Задание 1. Вычисление формулы с использованием однобуквенных имен .

Дано значение переменной x. Вычислите значение соответствующей формулы.

Пример выполнения задания.

Введите формулу приx=5,21

1. Введите в ячейку A1 названия задания, в ячейку A2 и В2 соответствующие поясняющие надписи (рис. 2.12).

2. Введите в ячейку A3 значение переменной x.

Рис. 2.12

3. Выделите ячейку A3, далее на вкладке Функция группа Определенные имена нажать Вставить функцию (рис 2.13), и в появившемся окне Создание имени присвоить данной ячейки имя х (рис 2.14).

Рис. 2.13

Рис. 2.14

4. Введите в ячейку В3 заданную формулу (рис. 2.15) Необходимо помнить, что ввод формулы в Excel осуществляется со знака «=»., и формула может быть только линейного вида. Вставлять необходимые функции можно следующим образом:

а) вызовите f_x Мастер функций (рис)

Рис. 2.15

б) на вкладке Функция группа Библиотека Функций нажать Вставить функцию (рис 2.16). В появившемся окне Мастер функций — шаг 1 из 2 в списке Категория выберите соответствующую категорию (Математическая), в списке Функция необходимую функцию (рис 2.17)

Рис. 2.16

Рис. 2.17

Основные математические операции

( ) – действия в скобках

^ – возведение в степень

* – умножение

/ – деление

+ –сложение

- – вычитание

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

Рис. 2.18

Задания. Вычислите выражение в соответствии со своим вариантом

№ варианта	Выражение	Параметр
1		x = 2,21
2		x= –3,82
3		x= –2,36
4		y = 2,92
5		t = 5,37
6		x= –3,14
7		t = 5,73
8		y = 3,82
9		t = 2,27
10		a = 2,51
11		b= –1,85
12		y = 2,64
13		x = 0,74
14		x = 1,64
15		x = 2,32
16		v = 2,85
17		x = 9,12
18		x = 3,17
19		y = 0,24
20		u= 3,21

Задание 2. Построение графиков функций в табличном процессоре .

Даны функции Y1(x) и Y2(x). Вычислите значения функции Y(x) в указанном интервале с заданным шагом и постройте совмещенный график функций Y1(x), Y2(x) и Y(x).

Пример выполнения задания.

Даны функции Y1(x) = cos(x) и Y2(x) = sin(2x). Вычислить значения функции Y(x) = Y1(x) + Y2(x) на интервале [0; 2] с шагом /10 0,31 и построить совмещенный график Y1(х), Y2(х) и Y(х).

1. Введите текст задачи по образцу (рис. 2.19).

Рис. 2.19

2. В ячейку F7 необходимо ввести значение шага /10. Для этого в ячейке введите знак “ = “ и вызовите f_x Мастер функций (рис 2.20), или на вкладке Функция группа Библиотека Функций нажать Вставить функцию (рис 2.21). В появившемся окне Мастер функций — шаг 1 из 2 в списке Категория выберите — Математическая, в списке Функция — ПИ, затем щелкните на кнопке ОК (рис 2.22). В следующем окне следует щелкнуть на кнопке ОК, т.к. функция ПИ не имеет аргументов. Затем в строке ввода формул (или непосредственно в самой ячейке) завершите ввод формулы, которая должна иметь окончательный вид =ПИ()/10 (рис 2.23). После окончания ввода нажмите клавишу [Enter].

Рис. 2.20

Рис. 2.21

Рис. 2.22

Рис. 2.23

3. Заполните ячейки, начиная с А7 значениями аргумента из интервала [0; 2] =[0; 6,28]. В ячейку А7 введите число 0 (начало интервала), затем установите в ячейку А8 курсор ввода. Введите формулу = предыдущая ячейка (в нашем случае – А7) + шаг (F7). Ссылка на шаг должна быть абсолютной (=A7+$F$7), т.к. при копировании формулы ссылка на него не должна изменяться (рис 2.24)

Рис. 2.24

Далее выделяете ячейку А8, наводите курсор в правый нижний угол (появится маркер автозаполнения), нажимаете левую клавишу и протягиваете формулу до конца диапазона. (рис 2.25)

Рис. 2.25

В результате диапазон ячеек А7:А27 заполнится числовыми значениями.

4. Выделите блок ячеек А7:D27 будущей таблицы, нажмите на вкладке Главная группа Ячейки команду Формат. В появившемся меню выбираете Формат ячеек (рис 2.26). В появившемся диалоговом окне Формат ячеек на вкладке Число в области Числовые форматы выбираете Числовой и устанавливаете Число десятичных знаков равное 2, затем щелкните на кнопке ОК. (рис 2.27)

Рис. 2.26 Рис. 2.27

5. В ячейку В7 введите формулу SIN(А7). Вызовите Мастер функций f_x . В появившемся окне выберите категорию функции – Математическая. Из списка выберите функцию SIN. Появится окно функции SIN (рис 2.28). В поле Число введите А7 (путем щелчка левой клавиши мыши по соответствующей ячейки), затем щелкнуть на кнопке ОК. В ячейке В7 появится значение результата.

Рис. 2.28

6. Из ячейки В7 формулу скопируйте в диапазон ячеек В8:В27. Установите указатель мыши в ячейке В7 на маркер заполнения, нажмите левую кнопку мыши и протяните его до ячейки В27. Отпустите кнопку.

7. В ячейку С7 введите формулу = СOS(2*A7) и скопируйте ее в диапазон ячеек С8:С27.

8. В ячейку D6 введите формулу = В6+С6 и скопируйте ее в диапазон ячеек D7:D26.

9. Далее постройте графики трех функций на одном чертеже, т.е. совмещенный график. Выделите диапазон Вашей таблицы B6:D27. На вкладке Вставка в группе Диаграммы нажимаете команду График и в области График выбираете График с маркерами (рис 2.29).

Рис. 2.29

10. Необходимо задать оси Х на диаграмме значения, соответствующие нашему диапазону Х. На вкладке Конструктор (Работа с диаграммами) в группе Данные нажимаете команду Выбрать данные (рис 2.30). В появившемся окне Выбор источника данных в области Подписи горизонтальной оси нажимаете Изменить (рис. 2.31) В появившемся окне Подписи оси указываете диапазон значений X (А7:А27), путем выделения левой клавиши мыши соответствующего диапазона ячеек (рис. 2.32). Нажимаете клавишу ОК (2 раза).

Рис. 2.30

Рис. 2.31

Рис. 2.32

11. Необходимо подписать ось Y,и указать заголовок диаграммы. На вкладке Конструктор (Работа с диаграммами) в группе Макет нажимаете команду Макет 1 (рис 2.33). И в появившихся соответствующих областях на диаграмме вносим соответствующие надписи рис 2.19.

Рис. 2.33

Задания

Построение графиков функций в табличном процессоре .

Даны функции У1(x) и Y2(x). Вычислите значения функции Y(x) в указанном интервале с заданным шагом и постройте совмещенный график функций У1(x), Y2(x) и Y(x).

Таблица 3

№ Задания	Функция Y1(x)	Функция Y2(x)	Y(x) для вычисления	Интервал	Шаг
2.1	Y=2x	Y=sinx	Y= 2x*sinx	[0;2]	/15
2.2	Y=x2	Y= 4x	Y=x2+4x	[-5;+5]	1
2.3	Y=x2	Y=sinx	Y=x2+sinx	[-;+]	/8
2.4	Y=lnx	Y=log10x	Y=lnx+log10x	[l;15]	1
2.5	Y=2x	Y=(1/2)x	Y=2x*(1/2)x	[- 4;+4]	1
2.6	Y=	Y=	Y= +	[0,4;2]	0,2
2.7	Y=	Y=	Y= –	[4;36]	2
2.8	Y=x2	Y=x3	Y=x2+x3	[-6;6]	1
2.9	Y=x2	Y=sinx	Y=x2*sinx	[-;+]	/12
2.10	Y= 2x	Y=x1/3	Y= 2x-x1/3	[0;3]	0,3

Задание 3

Пример выполнения задания 3

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

Алгоритм выполнения задания.

В табличном процессоре Excel выполнить следующие пункты задания:

1. Запустим программу Microsoft Office Excel 2003. (Пуск/ Все программы / Microsoft Office / Microsoft Office Excel 2003) и откроем рабочую книгу Книга1.xls.

2. Подготовим исходные данные на Листе1 в табличной форме следующего вида:

3. Листу 1 присвоим название «Исходные данные».

4. В столбец "Опт. Цена, у.е." заносятся числа по формуле "Цена, у.е. *0.95". В столбце "Цена, р." значения вычисляются по формуле "Цена, у.е.*Курс у.е.". Для этого нужно предварительно занести в какую-нибудь ячейку, например в G2 "Курс 1 у.е. в р.". Значения столбца "Опт. Цена, у.е." заполняются копированием вниз формулы из ячейки D2,используя относительную адресацию. Значения столбца "Цена, р." заполняются копированием вниз формулы из ячейки Е2, используя абсолютную адресацию ячейки, где помещено значение курса 1 у.е. в р.

5. На листе «Исходные данные» составить таблицу 2, используя статистические встроенные функции Excel - MИН, МАКС, СРЗНАЧ, СТАНДОТКЛОН.

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

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

8. По данным таблицы 3 построим график с маркерами типа «График X-У», где X - месяц, У – объём. Для этого необходимо:

• выделить диапазон C4 : C14;

• По данным таблицы 3 построим график с маркерами типа «График X-У», где X - месяц, У – объём. Для этого необходимо:

• выделить диапазон C4 : C14;

• вызвать Мастер диаграмм;

• назначить тип диаграммы – График, вид – График с маркерами, помечающими точки данных;

• нажать кнопку Готово.

• выделить и отредактировать полученный график в меню Диаграмма/ Исходные данные /Ряд.

9. Для аппроксимации полученного графика построить линию линейного тренда

• активизировать график одним щелчком мыши;

• через опции системного меню Диаграмма /Добавить линию тренда выбрать на закладке Тип окна Линия тренда тип линии тренда Линейная;

• в окне Линия тренда перейти на закладку Параметры;

• здесь установить: Прогноз вперёд на 1 период;

• Показать уравнение на диаграмме;

• -Поместить на диаграмму величину достоверности аппроксимации (R²);

• Нажать кнопку ОК.

10. Значения коэффициентов А и В из уравнения занести в таблицу.

11. Вычислим значение теоретического объёма продаж товаров по формуле, показанной на линии тренда = $C$2*A7+$D$2. Скопируем его в диапазон D7:D14.

12. Вычислим абсолютное значение отклонения теоретического от фактического объёма продаж товаров в столбце «Отклонение»: =ABS(C7-D7). Скопируем его далее.

13. Определим максимальную погрешность в столбце «Отклонение» при помощи функции МАКС Мастера функций (клетка E16).

14. Произведем минимизацию величины погрешности, используя сервисное средство Excel «Поиск решения» (кнопка системного меню – Сервис). При этом в качестве целевой ячейки надо выбрать ту, в которой находится величина погрешности (E16). Изменять следует значение коэффициентов a и b (ячейки C2:D2).

15. Cделаем прогноз объёма продаж на девятый период, скопировав формулу из предыдущей строки.

16. Построим на диаграмме совмещенные графики объёма продаж силикатного кирпича теоретического и фактического.

17. Указанные действия выполним для степенной математи­ческой модели, используя функцию Y=A*X^B на листе 3, предварительно присвоя ему имя

18. «Степенная модель»). Определим какая математическая модель даёт минимальную погрешность решения.

Задание 3. Использование электронных таблиц для моделирования и прогнозирования экономических процессов.

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

3.1

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фасадная панель	«Мой Дом»	25,33
Фасадная плита	«Мой Дом»	28,96
Газобетонный блок	«Мой Дом»	0,67
Керамическая плитка	«Мой Дом»	0.41
Гипсокартонный лист	«Мой Дом»	8,16
Кирпич М-150	«Мой Дом»	0,26
Кирпич М-100	«Мой Дом»	0,2
Пеноблок 100 мм	«Стройград»	1,43
Пеноблок 150 мм	«Стройград»	1,89
Пергамин	«Стройград»	5,67
Пенофол	«Стройград»	6,38
Изовер	«Стройград»	25,66
Пенополистирол1.2*1,2*0,05	«Стройград»	8,48
Гидростеклоизол	«Стройград»	15,34

Объем продаж керамической плитки
	недели
Фирма	1	2	3	4	5	6	7	8
«Мой Дом»	170	200	225	215	193	168	151	162

3.2

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Блоки из ячеистого бетона	«Восточное»	1,95
Гипсовая плита полнотелая	«Восточное»	6,169
Гипсовая плита	«Восточное»	7,17
Фанера ФК 8мм	«Восточное»	16,60
Труба вентиляционная	«Восточное»	59,2
Шифер плоский	«Восточное»	35
Сетка металлическая	«Восточное»	4,85
Кирпич полнотелый М-150	«Альта»	0,39
Панель фасад. DT-Stone	«Альта»	51,67
Пенополистирол	«Альта»	8,1
Керамогранит темно-серый	«Альта»	13,7
Рубероид	«Альта»	0,68
Гидростеклоизол	«Альта»	29,71
Пенофол 2000	«Альта»	3,71

Объем продаж пенополистирола
	недели
Фирма	1	2	3	4	5	6	7	8
«Альта»	168	161	157	129	133	140	146	134

3.3

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фанера ФК 21мм	«ТехноСтрой»	40,69
Плитка глазиров, пол	«ТехноСтрой»	9.06
Плитка каф. стена	«ТехноСтрой»	9,11
Минеральная вата	«ТехноСтрой»	58,81
Бордюр из гранита	«ТехноСтрой»	68,43
Гипсокартон 12.5мм	«ТехноСтрой»	9,73
ДСП строит.	«ТехноСтрой»	27,83
Панель пластиковая	«СтройЦентр»	5,33
Кирпич щелевой М-150	«СтройЦентр»	0,55
Дверное полотно	«СтройЦентр»	113,33
Бирепласт	«СтройЦентр»	26,48
Рубемаст	«СтройЦентр»	1,90
Мин.плита ROCKWOOL	«СтройЦентр»	18,40
Сетка штукатурная	«СтройЦентр»	2,46

Объем продаж гипсокартона 12.5мм
	недели
Фирма	1	2	3	4	5	6	7	8
«ТехноСтрой»	280	305	291	290	283	290	300	250

3.4

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фанера ФК 18мм	«ТехноРесурс»	34,25
Сетка стеклотканевая	«ТехноРесурс»	14,25
Пенофол 2000 А	«ТехноРесурс»	1,475
Айситекс-Моно	«ТехноРесурс»	60,37
Линолеум Фаворит	«ТехноРесурс»	16,23
Паркет ламинир.	«ТехноРесурс»	3,5
Панель потолочная	«ТехноРесурс»	10,5
Штукатурка	«ЭкоДом»	8,16
Краска водоэмульсионная	«ЭкоДом»	42,73
Полистиролбетонные блоки	«ЭкоДом»	4,03
Нордик терракота	«ЭкоДом»	15,04
Металлочерепица «Монтеррей»	«ЭкоДом»	35,83
Тепломат-100	«ЭкоДом»	28
Пергамин	«ЭкоДом»	0,53

Объем продаж пергамина
	недели
Фирма	1	2	3	4	5	6	7	8
«ЭкоДом»	139	116	135	148	145	133	125	110

3.5

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фанера ФК 15мм	«СтройИнвест»	29,28
ПЕНОПЛЭКС К	«СтройИнвест»	55,91
Айситекс-Профи	«СтройИнвест»	51,52
Керамогранит Серо-рыжый	«СтройИнвест»	19,67
Линолеум 35-415F	«СтройИнвест»	9,03
Паркет лам. KRONOSTAR	«СтройИнвест»	2,85
Мозаика фиолетовая	«СтройИнвест»	4,67
Панель потолочная Албес	«СтройОптТорг»	2,65
Раствор для стяжки	«СтройОптТорг»	7,34
Краска водоэмульсионная	«СтройОптТорг»	42,75
Стеновые пеноблоки	«СтройОптТорг»	3,58
Кронштейн	«СтройОптТорг»	0,55
Техноэласт ЭПП	«СтройОптТорг»	5,76
Кирпич полнотелый М-125	«СтройОптТорг»	0,42

Объем продаж стеновых пеноблоков
	недели
Фирма	1	2	3	4	5	6	7	8
«СтройОптТорг»	125	126	130	126	131	118	108	110

3.6

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Пенофол 2000 С	«РосСтрой»	3,98
Унифлекс ЭПП	«РосСтрой»	4,45
Альпин синий с отливом	«РосСтрой»	18,6
Керамогранит зелёный	«РосСтрой»	11,34
Ондулин (зеленый)	«РосСтрой»	12,67
Покрывающий фартук	«РосСтрой»	10,35
Гипсокартон ламинир.	«РосСтрой»	23,95
Профиль направляющий	«МегаОпт»	2,17
Фанера березовая ФСФ 9 мм	«МегаОпт»	27,37
Стекло , 4мм	«МегаОпт»	4,92
ДСП мебельная	«МегаОпт»	28,85
Линолеум Tarkett	«МегаОпт»	12,57
Мозаика желтая	«МегаОпт»	22,39
Грунтовка P-PRIMER	«МегаОпт»	27,93

Объем продаж линолеума Tarkett
	недели
Фирма	1	2	3	4	5	6	7	8
«МегаОпт»	40	36	39	35	32	30	32	33

3.7

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Бирепласт-Норма-Л	«СтройТемп»	26,48
Панель потолочная TAURUS	«СтройТемп»	2,5
Шлакоблок стеновой	«СтройТемп»	0,9
Биполь ЭПП	«СтройТемп»	3,76
Шифер волнистый	«СтройТемп»	9
Ондуфлеш терракота	«СтройТемп»	25
Битум БН 90/30	«СтройТемп»	21,33
Сетка кладочная	«Райтер»	2,94
Керамогранит бежевый	«Райтер»	19,63
Тепломат-80	«Райтер»	23,21
Мастика БПХ Техномаст	«Райтер»	16,53
Панель облицовочная	«Райтер»	21,38
Коньковый элемент	«Райтер»	6,94
Панель сайдинга	«Райтер»	6,52

Объем продаж панелей сайдинга
	недели
Фирма	1	2	3	4	5	6	7	8
«Райтер»	78	63	88	79	81	77	78	81

3.8

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Сэндвич-панели стеновые	«ЦентрСтрой»	42,5
Грунтовка Vosko	«ЦентрСтрой»	28,23
Клей для кафеля Terraco	«ЦентрСтрой»	6,17
Бикрост ХПП	«ЦентрСтрой»	2,44
Щипцовый профиль	«ЦентрСтрой»	7,97
Профнастил Н-60 оцинкованный	«ЦентрСтрой»	67,75
Линолеум Moda	«ЦентрСтрой»	14,86
Мозаика голубая	«СтройИнфо»	7,17
Тепловит-20	«СтройИнфо»	66,94
Клей FA-500	«СтройИнфо»	11,56
Техноэластмост Б ЭМП	«СтройИнфо»	8,28
Конек коричневый 90см	«СтройИнфо»	8,32
Лист гладкий оцинкованный	«СтройИнфо»	27,36
Затирка Terraco grout	«СтройИнфо»	2,35

Объем продаж мозаики голубой
	недели
Фирма	1	2	3	4	5	6	7	8
«ИнфоСтрой»	355	350	327	400	462	450	440	500

3.9

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фанера БЕРЕЗА 12 мм	«РесурсСтрой»	25,32
Сотовый поликарбонат STPLAST	«РесурсСтрой»	54,56
ДСП ламиниров.	«РесурсСтрой»	19,45
Коробка дверная 90 мм	«РесурсСтрой»	23,93
Линолеум Магия	«РесурсСтрой» «РесурсСтрой»	15,13
Краска Krastone	«РесурсСтрой»	44,82
Ендова зеленая 100см	«Стройкомплект»	8,58
Тепловит-12	«Стройкомплект»	15,25
Мозаика JA-24	«Стройкомплект»	26,37
Грунтовка ALC-PRIMER	«Стройкомплект»	29,77
Гранит GG17 ступень	«Стройкомплект»	45,23
Мозаика оранжевая	«Стройкомплект»	26,25
Айситекс-Стандарт	«Стройкомплект»	43,64
Клей для плитки ПХВ	«Стройкомплект»	19,38

Объем продаж краски Krastone
	недели
Фирма	1	2	3	4	5	6	7	8
«РесурсСтрой»	475	439	401	436	398	400	426	407

3.10

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Линолеум Астория	«ГрадСтрой»	10,21
Айситекс-Мастер ТКП	«ГрадСтрой»	35,62
Клей Хомакол	«ГрадСтрой»	17,53
Гипсокартон влагостойкий	«ГрадСтрой»	12,26
Фанера березовая ФСФ 15 мм	«ГрадСтрой»	43,13
Сотовый поликарбонат NOVOGLASS	«ГрадСтрой»	131,57
Оргстекло 1,2х1,8 3мм прозрачное	«ГрадСтрой»	31,37
Заполнитель карнизов универсальный 2 м	«СтройтТорг»	1,13
Плитка Керамогранит полиров.	«СтройтТорг»	18,45
Ламнированная панель HILUC	«СтройтТорг»	30,31
Дверное полотно ВОСТОК ДГШ	«СтройтТорг»	106,67
Плёнка пароизоляционная универсальная	«СтройтТорг»	0,67
Фанера хвойная 18 мм	«СтройтТорг»	37,25
Сетка стеклотканевая 5х5	«СтройтТорг»	19,42

Объем продаж хвойной фанеры18 мм
	недели
Фирма	1	2	3	4	5	6	7	8
«СтройтТорг»	95	97	103	112	97	105	106	105

Пример выполнения задания 4.

Даны стороны треугольника a, b, c. Вычислить площадь треугольника по оформуле Герона

, где p– полупериметр треугольника;

вычислить радиус вписанной окружности по формуле и

вычислить радиус описанной окружности по формуле .

При этом, если подкоренное выражение p(p-a)(p-b)(p-c)≤ 0, то при вычислении S выдать сообщение «Это не треугольник», а при вычислении r и R вывести пустые строки.

Алгоритм выполнения задания:

1. Запустим программу Excel (Пуск/Программы/Microsoft Excel) и откроем рабочую книгу Книга1.xls.

2. Неиспользуемому рабочему листу присвоим имя Задание_3.

3. В ячейки A2, B2, C2 введём имена сторон треугольника a, b, c_.

4. В ячейки A5, A7, A9, A11, C11 введём имена p, PV, S, r, R_ соответственно.

5. Для присвоения имён a, b, c_, p, PV, S, r, R_ ячейкам A3:C3, B5, B7, B9, B11 и D11 выделим диапазоны A2:C3, A5:B5, A7:B7, A9:B9, A11:D11 и выполним команду меню Вставка/Имя/Создать. В появившемся диалоговом окне Создать имена выберем варианты В строке выше и В столбце слева и нажмём кнопку ОК.

6. В ячейки A2, B2, C2 введём произвольные числа.

7. В ячейку B5 введём формулу для вычисления полупериметра =(a+b+c_)/2.

8. В ячейку B7 введём формулу для вычисления подкоренного выражения =p*(p-a)*(p-b)*(p-c_).

9. В ячейку B9 введём формулу =ЕСЛИ(PV>0;КОРЕНЬ(PV);”Это не треугольник”).

10. В ячейку B11 введём формулу ==ЕСЛИ(PV>0;S/p;” “).

11. В ячейку D11 введём формулу ==ЕСЛИ(PV>0;(a*b*c_)/(4*S);” “). Вид экрана после ввода приведён на рис. 3.1.

12. Выполним форматирование ячеек. Для ячеек с именами выделим их и выполним команду на панели Форматирование По левому краю. Для ячеек с числовыми значениями после выделения выполним команду на панели Форматирование Уменьшить разрядность, например, до3-х знаков после запятой. Можно отформатировать числа другим способом: выбрать в меню Формат/Ячейки, в диалоговом окне – вкладку Число, в списке Числовые форматы – Числовой и переключателем Число десятичных знаков выбрать значение 3.

13. Можно выделить весь блок решения, сделать заливку и установить внешние границы. Если какие-то данные в ячейки не помещаются, то нужно выделить блок ячеек с данными и выполнить автоподбор ширины ячеек через меню Формат/Столбец…/Автоподбор ширины.

14. Выполним защиту листа кроме входных данных. Сначала объявим ”беззащитными” ячейки с исходными данными. Для этого выделим ячейки A2:C2, введём команду Формат/Ячейки, выберем вкладку Защита и снимем флажок Защищаемые ячейки. Затем защитим лист командой меню Сервис/Защита/Защитить лист. Снять защиту можно командой Сервис/Защита/Снять защиту листа.

15. Для ограничения ввода введём разрешение вводить только положительные значения a, b, c_. Для этого введём команду Данные/Проверка. В появившемся диалоговом окне выберем вкладку Параметры, а затем в выпадающем списке Тип данных выберем Действительное. В выпадающем списке Значение выберем Больше, а в поле ввода Минимум вводим число 0.

16. Сохраним рабочую книгу Книга1.xls. в папке Мои документы/Информатика/Группа/Фамилия.

Рис. 17. Решение задачи в табличном процессоре Excel

Задание 4. Решение задач с присвоением имён, проверкой ошибочных ситуаций (функция ЕСЛИ) и ограничением ввода.

Задания

4.1. Дан цилиндр с радиусом основания R и высотой H. Вычислить:

• площадь основания SO=π*R²;

• площадь боковой поверхности SB=2*π*R*H;

• площадь полной поверхности SP=SB+2*SO;

• объём V=SO*H.

Если SO>2, выдать сообщение «Площадь основания велика» и объём V не вычислять.

4.2. Найти действительные корни x1 и x2 квадратного уравнения ax²+bx+c=0 по заданным значениям коэффициентов a, b, c. Если дискриминант D=b²-4a*c≥0, то вычислить корни по формулам x1=иx2=. ЕслиD<0, то выдать сообщение «Корни комплексные».

4.3. Дан прямоугольный параллелепипед со сторонами a, b, c. Вычислить:

• длину диагонали d=;

• угол между диагональю плоскостью основания ;

• объём V=a*b*c.

Если d>7, то выдать сообщение «Длина диагонали велика» и остальные величины не вычислять.

4.4. В правильной треугольной пирамиде заданы сторона основания a и высота h. Вычислить:

• длину бокового ребра ;

• радиус описанного около пирамиды шара ;

• угол наклона боковой грани к основанию .

Если b>10, то выдать сообщение «Длина бокового ребра велика» и остальное не считать.

4.5. Задан конус с радиусом основания R и высотой H. Вычислить:

• площадь основания SO= π*R²;

• площадь боковой поверхности SB= π*R*;

• площадь полной поверхности SP=SO+SB;

• объём V=.

Если SO>2, то выдать сообщение «Площадь основания велика» и объём не считать.

4.6. Вычислить активное давление грунта на глубинеz

=, где –коэффициент активного давления грунта;–средний удельный вес грунта;–угол внутреннего трения;c–удельное сцепление.

Если >1, то выдать сообщение «Коэффициент активного давления грунта велик» ине считать.

4.7. Фундамент сооружения имеет в плане размеры A и B. Размеры искусственного основания F определяются из выражения:

, где C=0,1*B.

Если C<0,5, то выдать сообщение «Данные не соответствуют нормам» и F не считать.

4.8. Дана правильная треугольная призма со стороной основания a и высотой h. Вычислить:

• длину диагонали боковой грани ;

• площадь боковой поверхности призмы SB=3*a*h;

• объём .

Если b>10, то выдать сообщение «Длина диагонали велика» и остальное не считать.

4.9. Дана правильная четырёхугольная пирамида со стороной основания a и высотой h. Вычислить:

• длину бокового ребра ;

• угол наклона боковой грани к основанию ;

• радиус описанного около пирамиды шара .

Если b>10, то выдать сообщение «Длина бокового ребра велика» и остальное не считать.

4.10. Дана правильная четырёхугольная призма с высотой H и радиусом R окружности, описанной вокруг основания призмы. Вычислить :

• площадь основания SO=2*R²;

• площадь боковой поверхности призмы SB=4*R*H;

• площадь полной поверхности SP=SB+SO;

• объём V=2*R^2*H.

Если SO>2, то выдать сообщение «Площадь основания велика» и объём V не считать.

Задание 5. Решение систем линейных уравнений методом обратной матрицы и специальными средствами Excel.

Пример выполнения задания 5.

Дана система линейных уравнений

Решить систему средствами Excel.

Алгоритм решения задачи:

1. Откроем рабочую книгу Книга_1.xls и неиспользуемому рабочему листу присвоим имя Задание_5.

2. В ячейки диапазона A1:D1 введём последовательно имена x1, x2, x3, x4 для обозначения ячеек A2:D2.

3. В ячейку A4 введём формулу левой части 1-го уравнения: =A2+B2+2*D2; в ячейку A6 введём формулу левой части 2-го уравнения: =2*A2+4*B2-C2+5*D2; в ячейку A8 введём формулу левой части 3-его уравнения: =A2+3*B2+5*D2; в ячейку A10 введём формулу левой части 4-го уравнения: =3*A2+7*B2-3*C2+9*D2.

4. В ячейки A3, A5, A7, A9 введём поясняющие тексты.

5. Запускаем Сервис/Поиск решения. Вид экрана с рабочим листом и диалоговым окном Поиск решения показан на рис. 18

6. В диалоговом окне присутствует параметр Целевая ячейка. Для решения системы линейных уравнений она особой роли не играет, но какую-нибудь формулу ввести необходимо. Поэтому в ячейку A13 введём формулу =A2+B2+C2+D2 и в окне ввода для целевой ячейки укажем адрес A13.

7. Флажок «максимальному значению» или «минимальному значению» в окне Поиск решения тоже не имеет значения для решения этой задачи.

8. Ограничения в диалоговом окне Поиск решения вводятся после нажатия кнопки Добавить. При каждом нажатии кнопки Добавить появляется другое окно, в которое последовательно вводятся адреса формул левых частей уравнений, знак “=” и числа из правых частей уравнений (рис.19). После ввода последнего ограничения нажимаем кнопку ОК и возвращаемся в основное окно Поиск решения. Щёлкнув по кнопке Выполнить, получаем результат (рис. 19). Чтобы узнать каким методом получено решение, нужно щёлкнуть по кнопке Параметры (рис. 18).

9. Сохраним рабочую книгу Книга1.xls.

10. Теперь решим ту же систему уравнений, используя матричные операции. Для этого запишем систему в матричном виде A*X=B и применим формулу X=A^-1*B.

11. Сделаем рабочий лист Задание 4 книги Книга1.xls текущим.

12. Разместим матрицу коэффициентов A в блоке A16:D19, а столбец свободных членов B – в блоке F16:F19/

13. В блок A21:D24 введём формулу для вычисления обратной матрицы. Для этого выделим блок A21:D24 и введём формулу {=МОБР(A16:D19)}. Завершить ввод нужно нажатием комбинации клавиш Ctrl+Shift+Enter.

14. Для дальнейших вычислений присвоим матрицам на рабочем листе имена: A16:D19–A, A21:D24–Aobr, F16:F19–B. Чтобы во введённых формулах появились эти имена нужно выбрать в меню пункт Вставка/Имя/Присвоить, выделить в диалоговом окне нужные имена и щёлкнуть ОК.

15. Теперь выполним умножение матрицы Aobr на B. В блок F21:F24 введём формулу {=МУМНОЖ(Aobr,B)}. В ячейках F21:F24 должен получиться результат. На рис. 23. показано решение системы этим способом. Можно проанализировать результаты, полученные разными методами.

16. Сохраним рабочую книгу Книга1.xls. в папке Мои документы/Информатика/Группа/Фамилия.

Рис. 18. Подготовка к решению системы уравнений посредством надстройки Поиск решения

Рис. 19. Форма Добавление ограничений

Рис. 20. Результаты поиска решения: x₁=3,7; x₂=5; x₃=6,7; x₄=-4,3

Задания

№ задания	Системы уравнений	№ задания	Системы уравнений
5.1		5.6
5.2		5.7
5.3		5.8
5.4		5.9
5.5		5.10

75