Изучение электронных таблиц в профильных классах

Разделы: Информатика

Занятие 3. Расчетные операции в EXCEL

Цель: научится работать со стандартными функциями.

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

Рис. 1

Рис. 1

Этапы выполнения:

1. Ввести название таблицы, объединив ячейки А1 : F1. Создать таблицу «Координаты точек».

2. Оформить вторую таблицу «Длины сторон» (результат рис.2).

Рис. 2

Рис. 2

3. Длина стороны находится по формуле: . Поэтому  в ячейке F4 (напротив АВ) нужно ввести формулу: = КОРЕНЬ(СУММКВРАЗН(В4 : С4, В5 : С5), где В4 : С4 – координаты первой точки – х1, у1; В5 : С5 – координаты второй точки – х2, у2.

4. Затем протянуть формулу для сторон ВС и АС, соответственно изменив диапазоны координат, для  ВС – (В5 : С5, В6 : С6), для АС – (В4 : С4, В6 : С6).

5. Ввести формулу для периметра в ячейку F7 : = СУММ(F4 : F6), для полупериметра в ячейку F8: = F7/2.

6. Рассчитать площадь по формуле Герона: img7.jpg (2524 bytes). Для этого в ячейку F9 ввести формулу : = КОРЕНЬ (F8 . (F8 – F4) . (F8 – F5) . (F8 – F6)).

7. Рассчитать радиус описанной окружности, используя  формулу:   4SR = abc.

8. Оформить таблицу «углов». Для расчета синусов, использовать формулу: sin A=a/2/R. Ввести  в ячейку I4 формулу: = F4/2/$F$10. Протянуть формулу для углов В и С. Для нахождения значения углов в градусах использовать функцию АSIN  и ПИ().

9. Для проверки, правильно ли  найдены углы,  определить их сумму.

10. Сохранить выполненную работу под именем Урок 3. Треугольник в своей папке.

Занятие 6. Надстройки в электронных таблицах. Подбор параметров

Решение уравнений через подбор параметров

1. Проверить установлена ли надстройка Подбор параметров в меню Сервис. Если нет, выполнить команды: Сервис – Надстройки, в диалоговом окне выбрать доступные надстройки Поиск параметров. Нажать ОК.

Рассмотрим пример решения уравнения с помощью подбора параметров: х3 – sinx  – 0,5 = 0.

2. Рассчитать значение функции на отрезке с некоторым шагом табуляции (например, рассмотреть отрезок [– 3, 3] с шагом 0,1).

3. Построить график функции.  По графику, приближенно определить корень уравнения (х = 1).

4. Методом подбора параметров определим значение аргумента х, при котором значение функции у равно нулю. Для этого:

  1. Под графиком функции ввести текст: «приближенный корень х = » (например, ячейка D 25), в следующей ячейке (G 25) ввести значение 1. Рядом (ячейка Н25) –  «значение функции у = », в ячейке J25 – формулу = G25 . G25 . G25 – SIN(G25) – 0,5  (ячейке появиться значение – 0,34147).
  2. Сделать текущей ячейку J25.
  3. Сервис – Подбор параметров…
  4. На панели Подбор параметров ввести в поле Значение  требуемое значение  (в данном случае 0), в поле Изменяя значения ячейки – G 25. Нажать ОК.
  5. В результате расчетов выполненных машиной в ячейке аргумента появиться значение 1,1185.

Рис. 3

Рис. 3

5. Найти приближенное решение уравнения ех – 2 = х3.

Занятие 7. Надстройки в электронных таблицах. Поиск решений

Задача на отыскание оптимального решения

Для изготовления четырех видов продукции (А, Б, В, Г) используются три вида сырья (I, II, III). Другие условия представлены в таблице:

Ресурс

Запас ресурса (ед.)

Нормы расхода сырья на единицу продукции (ед.)

А

Б

В

Г

I

3405

2

1

0,5

4

II

1210

1

5

3

0

III

3020

3

0

6

1

Прибыль от единицы продукции (ден. ед.)

7,5

3

6

12

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

Решение задачи в электронных таблицах

1. Построение модели

Пусть выпуск продукции соответственно: х1, х2, х3, х4.
Тогда прибыль от реализации задается функцией: f(х1, х2, х3, х4) = 7,5 х1 + 3х2 + 6х3 +  12х41.
Необходимо найти такие х1, х2, х3, х4, чтобы функция принимала максимальное значение при ограничениях:

2. Построение таблицы в Excel.

3. Оформить таблицу по следующему рисунку (таблица в режиме отображения формул).

Рис. 4

Рис. 4

4. Сделать текущей ячейку F5.

5. Сервис – Поиск решений…

Целевая функция – F5, значение ее должно быть максимальным,
Изменяя ячейки – диапазон значений переменных х1, х2, х3, х4 ($В$3:$Е$3)
Ограничения добавить (абсолютный адрес ставиться автоматически):

В3>=0, C3>=0, D3>=0, E3>=0
F8<=H8, F9<=H9, F10<=H10

6. Нажать Выполнить. Сохранить найденные значения.

Самостоятельно решить задачи:

Рис. 5

Рис. 5

  1. Предприятие производит 2 вида продукции Х и У. 1 кг Х приносит прибыль 5 рублей, требует 2 кг ресурса А и 3 кг ресурса В. 1 кг У приносит прибыль 10 рублей, требует 7 кг ресурса А и 9 кг ресурса В. Суммарный запас ресурсов 70 кг (А)  и 50 кг (В). При каком объеме производства прибыль будет максимальной?
  2. Решите систему линейных уравнений.