Методичка

Создание расчетных электронных таблиц. Табличные процессоры

1.Протабулируйте аргумент Х − введите значения аргумента в диапазоне, например, от –10 до 10 с шагом изменения 1 (ячейки А2:А22).

2.Вычислите значения функции для всех значений аргумента по указанной формуле − ячейки В2:В22.

3.Постройте график. Выделите интервал ячеек, в которых располагаются столбцы Х и У – на панели инструментов выберите Диа-

грамма Шаг 1 Тип диаграммы Диаграмма ХУ линии и точки. Назовите его «Исходный график функции».

4.Если по первоначальному графику трудно определить точки пересечения графика с осью Х, то скопируйте график − при нажатой клавише Ctrl зацепите мышью и перетащите на новое место. Назовите график «График функции с измененной шкалой».

5.Новый график необходимо переформатировать, изменив шкалу для Х [–3, 3] и Y [–5, 5]. Это позволит увидеть точки пересечения функции с осью.

6.Команды выбирайте в контекстном меню соответствующего объекта. Указатель мыши поместите на ось графика, вызовите кон-

текстное меню → Формат оси → вкладка Масштабирование уста-

новите для оси Х максимум 3, минимум – 3, для Y максимум 5, минимум – 5.

7.Визуально определите точки пересечения графиком оси Х (У=0). Это и есть корни уравнения, определите по графику приблизительные значения Х.

8.Для определения точных корней выделите ячейки, например, D6:D8, в них поместите примерные значения корней (взятые с графика). В диапазон ячеек Е6:Е8 поместите формулы для вычисления функций.

9.Выполните команду Сервис → Подбор параметра → Настройка по умолчанию – Целевая ячейка Е6 → Целевое значение

→Изменяя ячейку D6. Повторите эти действия, если корней несколько, рис. 53.

Лабораторная работа 14. Построение поверхности

Задание. Построить поверхность согласно вашему варианту задания, табл. 3. В методических указаниях рассмотрен пример постро-

81

Создание расчетных электронных таблиц. Табличные процессоры

ения поверхности, заданную функцией Z = Х2 + У2, при изменении Х и У на интервале [-1;1] с шагом 0,1. Результат выполнения работы приведен на рис. 54.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ВЫПОЛНЕНИЯ РАБОТЫ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ EXCEL

1.Для построения поверхности необходимо, как при построении любого графика, создать таблицу для расчета координат точек поверхности. Поверхность Z имеет два аргумента Х и У.

В таблице в столбце А задать изменение аргумента Х автозаполнением от -1 до 1. с шагом 0,1.

В строке 3 (рис. 52) задать изменение аргумента У автозаполнением от -1 до 1 с шагом 0,1.

Ввести в ячейку В4 формулу функции поверхности. В примере =$A4^2+B$3^2. Адреса ячеек аргументов Х и У задаются в виде смешанных ссылок: для Х фиксируется столбец А ($A4), а для У фиксируется строка 3 (B$3). Смешанные ссылки устанавливаются так же как абсолютные клавишей F4, добавляется знак $ к имени столбца и номеру строки.

2.Скопировать формулу из ячейки В4 маркером заполнения на весь диапазон ячеек изменения Х − вниз по столбцу и затем, выделенный столбец протянуть маркером заполнения через все значения У. Получим таблицу с рассчитанными значениями координат поверхности Z.

82

Создание расчетных электронных таблиц. Табличные процессоры

3. Построить график поверхности. Выделить диапазон ячеек, включая значения Х, У и Z – лента Вставка Другие Поверх-

ность.

Постройте поверхность, соответствующую Вашему варианту.

Рис. 52. Расчетная таблица координат точек поверхности

Рис. 53. График поверхности в Excel

83

Создание расчетных электронных таблиц. Табличные процессоры

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ВЫПОЛНЕНИЯ РАБОТЫ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ CALC

1.В CALC процесс расчета таблицы координат поверхности Z аналогичен описанному выше.

2.Построение графика поверхности. Выбрать на панели ин-

струментов Диаграмма Тип диаграммы Области Трехмерный вид, рис. 54.

Рис. 54. Поверхность, построенная в Calc

Лабораторная работа 15. Решение задач оптимизации

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

Решение задачи оптимизации состоит из нескольких этапов: 1. Формализация задачи − математическая постановка задачи.

• Целевая функция:

84

Создание расчетных электронных таблиц. Табличные процессоры

Z=F(X1, X2,…… Xn)min (max, заданной величине) – это минимум потерь или максимум прибыли.

X1, X2,…… Xn − искомые параметры, влияющие на целевую функцию.

• Ограничения для X1, X2,…… Xn − обычно условия неравенства. 2. Решение задачи методами математического программирования.

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

Задание.

Фирма производит изделия, назовём их А, В, С. Для их производства используются три типа комплектующих изделий: компл.1,

компл.2, компл.3.

Известно: расход каждого типа комплектующего для каждого вида изделия, цена каждого вида изделия, запасы каждого типа комплектующих (приведены в таблице 4).

Таблица 4

Исходные данные для решения.

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

МАТЕМАТИЧЕСКАЯ ПОСТАНОВКА ЗАДАЧИ. Пусть Х1, Х2, Х3 количество выпускаемых изделий А, В, С.

• Целевая функция – доход от продажи изделий

Z=500 Х1+ 700 Х2+ 550 Х3max.

•Ограничения:

По расходу комплектующих. Их расход должен быть меньше или равен запасам.

20 Х1+50 Х2+10 Х3 5000 – ограничение по компл.1.

85

Создание расчетных электронных таблиц. Табличные процессоры

По количеству выпускаемой продукции.

Х1 0; Х2 0; Х3 0 − количество выпускаемых изделий не может быть отрицательным.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ВЫПОЛНЕНИЯ РАБОТЫ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ EXCEL

1. По исходным данным табл.3 и математической постановке задачи создается таблица для решения, рис. 55.

Рис. 55. Таблица решения задачи оптимизации

2.Ячейки В10:D10 предназначены для размещения будущих найденных Excel количеств изделий А, В, С – Х1, Х2, Х3.

3.Целевая функция – это сумма произведений Количества изде-

лий и Цены изделий, т.е. в ячейке В11 формула

=СУММПРОИЗВ(B8:D8;B10:D10).

4.Правая часть ограничения по расходу комплектующих (запасы комплектующих) записаны в ячейках G5:G7.

5.Левая часть ограничений (формулы) записаны в ячейках Е5:Е7. Формула, например в ячейке Е5, имеет вид =СУММПРОИЗВ(B5:D5;$B$10:$D$10). Эту формулу скопировать в ячейки Е6:Е7.

86

Создание расчетных электронных таблиц. Табличные процессоры

6. Решение задачи средствами Excel. Установите курсор в ячейку В11 и выполните лента Данные Поиск решения. Заполните окно «Поиск решения» информацией, рис. 56.

Рис. 56. Установка параметров поиска решения

-В поле «Оптимизировать целевую ячейку» адрес ячейки В11.

-До Максимума.

-В поле «Изменяя ячейки переменных» дать ссылку на ячейки с искомыми Х1, Х2, Х3 − на ячейки В10:D10.

-В поле «В соответствии с ограничениями» добавим ограничения, нажав на кнопку Добавить. В появившемся диалоговом окне указать − для ограничения по комплектующим в поле «Ссылка на ячейки» адреса ячеек с расходами комплектующих, в правом поле «Ограничения» − адреса ячеек с запасами комплектующих, в среднем, поле из списка выбрать соответствующий знак логического отношения, нажать на кнопку Добавить, рис. 57.

87

Создание расчетных электронных таблиц. Табличные процессоры

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

Таким образом задать все ограничения.

Отметить флажком «Сделать переменные неотрицательными», выберите метод решения − Поиск решения линейных задач. Затем

Найти решение, рис. 58.

Рис. 58. Результат решения задачи оптимизации

Выводы: Из данного запаса комплектующих можно изготовить изделий вида А – 183 шт., изделий типа В – 25 шт., изделий типа С – 8 шт. Все комплектующие будут израсходованы полностью. При этом при реализации изделий будет получен максимально возможный доход 113750 рублей.

Если на ленте Данные команда Поиск решения отсутствует, то выполните следующие действия: Файл Параметры Надстрой-

ки Пакет анализа Поиск решения.

88

Создание расчетных электронных таблиц. Табличные процессоры

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ВЫПОЛНЕНИЯ РАБОТЫ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ CALC

1. Разработка исходной таблицы в Calc аналогична тому, как она реализована в Excel, рис. 55. Однако есть различия в именах функций, так, например, целевая функция будет иметь вид

=SUMPRODUCT(B8:D8;B10:D10), а ограничение по компл. 1

=SUMPRODUCT(B5:D5;$B$10:$D$10).

2. Решение задачи средствами Calc. Установите курсор в ячейку целевой функции В11 и выполните команду Сервис Решатель. Заполните окно «Решателя» информацией, рис. 59.

Рис. 59. Установка параметров Решателя

Установить Параметры (рис. 60): «Механизм решателя» ли-

нейный решатель, Принять переменные как неотрицательные.

89

Создание расчетных электронных таблиц. Табличные процессоры

Рис. 60. Установка параметров Решателя

Лабораторная работа 16. Создание макросов и формы

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

Задание 1 Протабулируйте функцию Y=5Х3+2, для чего рассчитайте значения аргумента Х, функции У и постройте график, рис. 61.

Рис. 61. Табулирование функции

90