Информатика.Ч1_2Ed

Рис. 2.18.

В ячейки С32 и В36 введите следующие формулы

=ЕСЛИ(ИЛИ(B32=1; B32=21); "год"; ЕСЛИ(ИЛИ(И(B32>=2; B32<=4); И(B32>=22; B32<=24)); "года"; "лет"))

=ЕСЛИ(B31<B35; "Выгодно дать деньги в долг"; ЕСЛИ(B35=B31; "Варианты равносильны"; "Выгоднее деньги положить под проценты"))

3. Настроить сценарии выполнения вычислений.

Рис. 2.19.

МЕТОДИКА ВЫПОЛНЕНИЯ

1.На листе Финансовые вычисления создайте таблицу и внесите данные представленные на рис. 2.17. Скопируйте форматы ячеек с таблицы из первого или второго задания.

2.Введите формулу для вычисления объема вклада на основании постоянных платежей в ячейку В35 отформатированной таблицы. Так-

121

же скопируйте формулу в ячейку С32 для изменения текстовой надписи срока возвращения ссуды.

=ЕСЛИ(ИЛИ(B32=1; B32=21); "год"; ЕСЛИ(ИЛИ(И(B32>=2; B32<=4); И(B32>=22; B32<=24)); "года"; "лет"))

В ячейку В36 добавьте формулу отображающую решение, которое следует из результата анализа доходности сделки.

=ЕСЛИ(B31<B35; "Выгодно дать деньги в долг"; ЕСЛИ(B35=B31; "Варианты равносильны"; "Выгоднее деньги положить под проценты"))

3.Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. В MS Office Excel имеется возможность задания сценариев расчетов с одновременным автоматическим составлением отчета. Для создания сценария расчета перейдите на вкладку ленты Данные и в группе Работа с данными выберите из списка Анализ «что-если» команду Диспетчер сценари-

ев… . Нажмите на кнопку Добавить и в открывшемся окне задайте следующие параметры сценария:

Название сценария - ПС1

Изменяемые ячейки - B32:B33 В следующем окне введите

Значение изменяемой ячейки $B$32 - 5

Значение изменяемой ячейки $B$33 - 2500

Таким образом, один из трех сценариев расчета будет создан. Не закрывая окна диспетчера сценариев аналогичным образом добавьте еще два сценария (рис. 2.19).

После создания всех трех сценариев нажмите на кнопку Отчет в окне диспетчера сценариев и установите в поле Ячейки результата адрес В35. Нажмите кнопку ОК. Отчет должен появиться на новом листе (не забудьте изменить авторскую надпись).

Сохраните изменения рабочей книги MS Office Excel, в которой выполнялась данная практическая работа.

ЗАДАНИЕ 4

1.Создайте новый лист MS Office Excel 2007 и назовите его Линейное программирование. Составьте математическую модель для решения следующей задачи. Небольшая фабрика выпускает два типа красок: для внутренних (I) и наружных (E) работ. Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта A и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн, соответст-

122

венно. Расходы продуктов A и В на 1 тонну соответствующих красок приведены в таблице 2.1.

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

Исходный	Расход исходных продуктов на		Максимально воз-
продукт	тонну краски, т		можный запас, т
	краска E	краска I
A	1	2	6
B	2	1	8

Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает спроса на краску E более чем на 1 т. Кроме того, установлено, что спрос на краску I никогда не превышает 2 тонны в сутки. Оптовые цены одной тонны красок равны: 3000 руб. для краски E и 2000 руб. для краски I. Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?

2.Поместите на лист Excel условные обозначения величин математической модели задачи, введите исходные данные и настройте необходимые формулы, например, так как показано на рис. 2.20.

Рис. 2.20. Исходные данные задачи линейного программирования

3.С помощью надстройки Поиск решения получите оптимальное решение задачи планирования производства и создайте отчет с результатами решения (рис. 2.21).

123

Рис. 2.21. Отчет с результатами решения задачи

МЕТОДИКА ВЫПОЛНЕНИЯ

1.Создайте новый лист щелкнув на кнопке вставить лист . В контекстном меню нового ярлычка рабочего листа выберите команду Переименовать и задайте новое имя листа – Линейное программиро-

вание.

Примечание. Для решения задач подобного рода необходимо построить математическую модель. Процесс построения модели можно начать с ответа на следующие три вопроса:

Для определения каких величин строится модель (т.е. каковы переменные модели)?

В чем состоит цель, для достижения которой из множества всех допустимых значений переменных выбираются оптимальные? Каким ограничениям должны удовлетворять неизвестные?

В нашем случае фабрике необходимо спланировать объем производства красок так, чтобы максимизировать прибыль. Поэтому переменными являются: – суточный объем производства краски I и – суточный объем производства краски E.

124

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

Объем производства красок не может быть отрицательным, следовательно, ограничения, которые налагаются на и - это . Расход исходного продукта для производства обоих видов красок не может превосходить максимально возможный запас данного исходного продукта:

,

.

Кроме того, ограничения на величину спроса на краски таковы:

,

.

Таким образом, математическая модель данной задачи имеет следующий вид:

Максимизировать при следующих ограничениях:

,

,

,

,

.

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

2.На лист Линейное программирование добавьте надписи (рис. 3.1), занимающие несколько ячеек одновременно. Для этого воспользуй-

тесь командой Объединить и поместить в центре на вкладке ленты Главная.

Чтобы добавить условные обозначения, содержащие нижний ин-

декс или , наберите соответствующие символы в строке формул, затем выделите последний символ и в диалоге Формат ячеек: шрифт (Ctrl+Shift+F) отметьте галочкой видоизменение на подстрочный символ. В строке формул запись не измениться, но к записи в самой ячейке будет применен выбранный формат.

Знак меньше либо равно, который необходим для обозначения ограничений добавьте с помощью вставки символа Вставка, Текст,

Символ .

3.Обычно в MS Office Excel 2007 с настройками по умолчанию дополнительные надстройки (Пакет анализа, Поиск решения и др.)

125

отключены. Чтобы активировать какую либо надстройку нужно нажать на кнопку Office и в открывшемся диалоге нажать на кнопку

Параметры . В окне установки параметров выберите пункт Надстройки и нажмите на кнопку Перейти в нижней части окна. В появившемся списке отметьте надстройку Поиск решения и установите ее. После установки надстройки Поиск реше-

ния (Solver.xlam) кнопка становиться доступной в группе Анализ вкладки ленты Данные.

Выделите на ячейку (группу ячеек) содержащих целевую функцию

и нажмите на кнопку Поиск решения . В открывшемся диалоге установите следующие параметры.

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

Нажмите на кнопку Параметры и отметьте галочками пункты Ли-

нейная модель и Неотрицательные значения. После чего нажми-

те на кнопку Выполнить.

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

126

КОНТРОЛЬНЫЕ ЗАДАНИЯ ПО ТЕМЕ 3

1.Оформить на листе общие сведения о планете Земля

Указания по выполнению:

вячейках С2 и С3 пробелы вручную не ставить;

вячейке С4 выравнивание по правому краю вручную не производить.

2.Оформить на листе данные о распределении суши и воды на земном шаре

Указания по выполнению:

вячейках С4, , С6, E4, E5, E6, G4, G5 и G6 символ «%» вручную не вводить;

вячейке С4, С5, E4, E5, G4, G5 и F6 цифру 0 после запятой вручную не вводить.

3.Оформить на листе данные об океанах на земном шаре

Указания по выполнению:

в ячейках с числовым значением пробел вручную не ставить.

127

4.Оформить фрагмент листа, который при предварительном просмотре будет иметь вид, представленный ниже

5.В школе проводиться шахматный турнир, в котором участвуют 15 учащихся. Соревнования проводятся по круговой системе – каждый играет с каждым по одному разу. Результаты заносятся в обычную турнирную таблицу с диагональю, заполненной «крестиками»

За победу участнику дается 1 очко, за ничью – 0.5, за проигрыш

– 0. Например, в приведенной таблице Бендукидзе К. выиграл у Василенко О. и сыграл вничью с Гончаровым Д., а Василенко О. выиграл у Гончарова Д. и т.д. Оформить таблицу результатов турнира. При вводе результатов игр исключить, во избежание случайных ошибок, избыточность данных, т.е. результаты вводить только над диагональю таблицы. В остальные ячейки соответствующие показатели должны заноситься автоматически.

6.На предприятии персонал работает по графику: 12-часовая дневная смена, на следующий день – 12-часовая ночная смена, затем двое суток отдыха. Составить график для четырех человек на 8 дней

128

7.Вычислить значение логического выражения (сложного условия) при следующих значениях простых условий A, B и С: А=ИСТИНА,

В=ЛОЖЬ, С=ЛОЖЬ:

a)не А;

b)не А и В;

c)не В;

d)А или не В;

e)А и В;

f)А и В или С.

Решение оформить в виде, показанном ниже

8.Вычислить значение логического выражения (сложного условия) при следующих значениях простых условий X, Y и Z: X=ИСТИНА,

Y= ИСТИНА, Z=ЛОЖЬ:

a)не X;

b)не X и Y;

c)не Y;

d)X или не Y;

e)Y и Z;

f)X или Y и Z.

Решение оформить в виде, показанном ниже

129

9.Дано двузначное число. Найти:

a)число десятков в нем;

b)число единиц в нем;

c)сумму его цифр;

d)произведение его цифр.

Решение оформить в виде, представленном ниже

10.В некоторой стране используются денежные купюры достоинством в 1, 2, 4, 8, 16, 32 и 64. Как наименьшим количеством таких денежных купюр можно выплатить сумму денег n (указать количество используемых для выплаты купюр)? Предполагается, что имеется достаточно большое количество купюр всех достоинств. Решение оформить в виде, представленном ниже

130