Частное учреждение - профессиональная образовательная организация
«КРАСНОДАРСКИЙ ТЕХНИКУМ УПРАВЛЕНИЯ, ИНФОРМАТИЗАЦИИ И СЕРВИСА»
Методические указания для выполнения практических работ
в табличном процессоре EXCEL (подбор параметра, оптимизационные задачи)
по специальности 09.02.03 «Программирование в компьютерных системах»
Базовый уровень СПО
Преподаватель:
2016 г.
1.1. РЕШЕНИЕ ПРОСТЕЙШИХ ЗАДАЧ
Пример 1. Заработок между строителями бригады распределяется по договору
Следующим образом: прорабу - 45%, первому помощнику - 20%, второму помощнику 10% остаток делится поровну между двумя оставшимися строителями. Определите, К, распределят строители сумму в 120 тысяч рублей.
Цель моделирования. Определить сумму, получаемую каждым строителем.
Формализация задачи. Составим таблицу, в которой пометим ячейки разными цветами
Лвырпрлвалпловдлор - вычисляемые ячейки
Алврпарпаорпыворапворпоавы - исходные данные
А | В | С | D | Е | F | G |
12 | Прораб | 1-й помощник | 2-й помощник | Строитель | Строитель | Сумма |
13 | Кол-во процентов | 45 | 20 | 15 | 100 | |
14 | Зарплата Руб. | 120 000 |
Разработка модели. Введём в таблицу данные в соответствии с условием задачи.
Компьютерная модель. 1. В ячейке E13, F13 введите формулу: разность сумм процентов и известного количества процентов, деленная на 2 (=(G13-B13-C13-D13)/2)
2. В ячейку В14 введите формулу: всю сумму разделить на количество всех процентов и умножить на количество частей процентов по договору, при этом лучше пользоваться абсолютными ссылками (=$G$14/$G$13 *В13).
3. Скопируйте формулу из ячейки B14 нa диапазон C14:F14.
Анализ результатов. Проверьте, насколько соответствуют результаты моделирования. Попробуйте изменить исходные данные, и проверьте универсальность построенной модели.
ЗАДАЧИ
1. При перегонке нефти получается 30% керосина и 53% мазута, остальное - потери при обработке. Определите, сколько получится керосина и мазута и сколько составят отходы при переработке 20 тонн нефти.
2. Сплав состоит из меди, цинка и свинца. Медь составляет 62,8% сплава, цинк 34,8%, остальное приходится на свинец. Сколько граммов каждой из составляющих нужно взять, чтобы получить 0,8 кг сплава?
3. Гречневая крупа содержит 1 % белков, 64% углеводов, остальное составляют другие продукты, что составляет 28 кг. На сколько больше в гречневой крупе углеводов чем белков?
1.2. ЗАДАЧИ НА ПОДБОР ПАРАМЕТРА
Пример 2. С помощью электромотора за 7 секунд можно накачать в бак 20 литров воды. За какое время можно наполнить бак, вмещающий 2000 литров воды?
Цель моделирования. Определите время, за которое заполнится бак объемом 2000 литров.
Формализация задачи. Составим таблицу, в которой пометим ячейки разными цветами.
- вычисляемые ячейки
- исходные данные
- подбираемые данные
Разработка модели. Введем в таблицу данные в соответствии с условием задачи.
А | В | С |
12 | Часть бака | Полный бак |
13 | время | 7 |
14 | объём | 20 |
Компьютерная модель. 1. В ячейке С13, где будет подбираться значение, ничего не вводим.
2. В ячейку С14 введите формулу: часть объема разделить на часть времени на время наполнения полного бака (=В14/В13*С13).
.3. У становив курсор в ячейке, значение которой задано (2000 литров) С14
выполните «Сервис / Подбор параметра...»
В поле «Значение» введите 2000, а в поле «Изменяя значение ячейки» введите С13.
Анализ результатов. Проверьте, насколько соответствуют результаты цели моделирования. Пересчитайте время, необходимое для заполнения бака, объёмом 4000 .литров, 1200 литров, и проверьте универсальность построенной модели.
Пример 3. Найдите один корень уравнения х 2 - 4х + 3 = 0.
Цель моделирования. Определить значение одного из корней данного уравнения. Формализация задачи. Составим таблицу, в которой пометим ячейки теми же цветами, что и в предыдущем примере.
Разработка модели. Введем в. таблицу данные в соответствии с условием задачи.
А | В | С |
12 | х | уравнение |
13 |
Компьютерная модель. 1. В ячейке А13, где будет подбираться значение, ничего
не вводим.
2. В ячейку В 14 введите формулу квадратного уравнения, считая, что неизвестная х
-ячейка А13, при этом возведение в степень обозначается знаком ^ (=А13^2-4* A13+3)
3. Установив курсор в ячейке В14, в которой находится значение уравнения
выполните «Сервис / Подбор параметра…».
В поле «Значение» введите 0, а в поле «Изменяя значение ячейки» А13.
Анализ результатов. Проверьте, насколько соответствуют результаты цели моделирования. Проверьте универсальность построенной модели, решив другие уравнения.
Заметим, что такой способ нахождения корней уравнения дает только один из корней, хотя известно, что уравнение степени n должно иметь не более n действительных корней. При этом значение находится приближенно с некоторой погрешностью. Увеличить точность вычислений можно, если зайти в меню «Сервис ~ Параметры - Вычисления», уменьшить относительную погрешность и увеличить количество итераций.
Пример 4. Требуется огородить прямоугольный участок площадью 300м2, если имеется сетка длиной 240м. Определите стороны участка.
Цель моделирования. Определить стороны прямоугольного участка.
Формализация задачи. Составим таблицу, в которой пометим ячейки теми же цветами, что и в предыдущем примере. При этом длина сетки - это периметр прямоугольного участка. Если х и у - стороны участка, то Р = 2(х + у), отсюда у= Р/2 - х Площадь участка S = ху.
Разработка модели. Введем в таблицу данные в соответствии с условием задачи.
А | В | |
11 | Периметр Р | 240 |
12 | Сторона х | |
13 | Сторона у | |
14 | Площадь S |
Компьютерная модель. 1. В ячейке A12 (значение стороны х) будет подбираться значение, поэтому ничего не вводим.
2. В ячейку B13-введите формулу Для вычисления стороны у (=В11/2-В12).
3. В ячейку В14 введите формулу для вычисления площади S (=В 12*В13).
4. Установив курсор в ячейке В14, в которой находится значение площади, выполните «Сервис - Подбор параметра…». В поле «Значение» введите 300, а в поле «Изменяя значение ячейки» введите В12.
Анализ результатов. Проверьте, насколько соответствуют результаты цели моделирования. Проверьте универсальность построенной модели, изменив значение, площади и периметра.
ЗАДАЧИ
1. На ипподроме лошадь, пробегая по кругу 15 раз, преодолевает 24 километра.
Сколько километров она преодолеет, пробежав по кругу 2 раза? 25 раз?
2. Заготовленного запаса кормов хватит двум кроликам на 120 дней. На сколько
дней. такого же запаса кормов хватит 11 кроликам? 12 кроликам?
3. Три тракториста могут вспахать поле за 18 часов. Сколько потребуется
трактористов, чтобы вспахать это поле за 9 часов? За 27 часов?
4. Найдите один корень уравнения 2х4- 5х3 + 7 = 0; 3х5 - 2х2 + 6 = О.
5. Из прямоугольного листа жести с длиной а = 28см, шириной b = 16см надо
изготовить короб с объёмом \/ = З00см 2. Для этого из листа вырезают четыре угловых квадрата. Определить длину стороны Х вырезаемого квадрата.
6. Площадь прямоугольника равна 253м2, а его периметр равен 68м1. Найти стороны прямоугольника.
1.3. ОПТИМИЗАЦИОННЫЕ ЗАДАЧИ (ВЫЧИСЛЕНИЕ НАИБОЛЬШИХ, НАИМЕНЬШИХ ЗНАЧЕНИЙ)
Пример 5. Число 18 разбить на такие два положительных слагаемых, чтобы сумма их квадратов была наименьшей.
Цель моделирования. Найти слагаемые числа 18, удовлетворяющие условию
задачи.
Формализация задачи. Составим таблицу, в которой по-прежнему будем помечать ячейки теми же цветами, что и в предыдущем разделе. Если х и у – искомые числа, то х + у = 18, отсюда у = 18 - х. Сумму квадратов чисел обозначим как S, тогда S= х 2 + у 2.
Разработка модели. Введем в таблицу данные в соответствии с условием задачи.
А | В |
12 | Число х |
13 | Число у |
14 | Сумма S |
Компьютерная модель. 1. В ячейке А12 (значение числа х) будет подбираться значение, поэтому ничего не вводим.
2. В ячейку В13 введите формулу для вычисления числа у (=18-В12).
3. В ячейку В14 введите формулу для вычисления суммы (=В121\2+В131\2).
4. Установив курсор в ячейке В14, которая по условию должна принимать наименьшее или наибольшее значение, зайдите в «Сервис - Поиск решения...» Установите целевую ячейку $В$14 равной максимальному значению, изменяя ячейки SB$12.
Ограничения (нажмите добавить), так как числа х и у должны быть положительны, то В12>= 0, В13 >= 0, затем нажмите на кнопку Выполнить.
Анализ результатов. Проверьте, насколько соответствуют результаты цели моделирования.
Проверьте универсальность построенной модели, изменив число.
Пример 6. Требуется огородить прямоугольный участок наибольшей площади, если имеется
сетка длиной 240м. Определите стороны полученного участка.
Цель моделирования. Найти стороны прямоугольного участка наибольшей площади.
Формализация задачи. Составим таблицу, в которой ячейки отмечены различными цветами.
Длина сетки - это периметр Р. Если х и у - стороны участка, то Р = 2(х + у), отсюда у = Р/2 - х.
Площадь участка S = ху..
Разработка модели. Введем в таблицу данные в соответствии с условием задачи.
А | В | |
11 | Периметр Р | 240 |
12 | Сторона х | |
13 | Сторона у | |
14 | Площадь S |
Компьютерная модель. 1. В ячейке А12 (значение стороны Х) будет подбираться
значение, поэтому ничего не вводим.
2. В ячейку В13 введите формулу для вычисления стороны у (= В11/ 2-В12).
3. В ячейку В14 введите формулу для вычисления площади S (=В12* В13).
4. Установив курсор в ячейке со значением площади В 14, которая по условию должна принимать наименьшее или наибольшее значение, зайдите в «Сервис~ Поиск решения...». Установите целевую ячейку $В $14 равной максимальному значению изменяя ячейки $В$12.
Ограничения, так как стороны х и у должны быть положительны, то В12>=0, В13 >= 0, затем нажмите Выполнить.
Анализ результатов. Проверьте, насколько соответствуют результаты цели моделирования. Проверьте универсальность построенной модели, изменив заданную длину сетки.
Пример 7. Малое предприятие выпускает детали А и В. Для этого используете литье, подвергаемое токарной обработке, сверлению и шлифованию. Производительность станков по обработке деталей приведена в таблице:
Типы станков | Время, затраченное на1деталь | Резерв Времени | |
А | В | ||
Токарные Сверильные Шлифовальные | 25 28 35 | 40 35 25 | 800 700 575 |
Прибыль от продажи деталей А составляет 25 Д. е., деталей В - 30 Д. е.. Определите план выпуска деталей, обеспечивающий максимальную прибыль предприятия.
Цель моделирования. Определить план выпуска (количество деталей вида А и в:
Формализация задачи. Составим таблицу, в которую перенесем данные J
условия. Введем три дополнительных строки, в одну внесем прибыль от продажи деталей, во второй - искомый план выпуска, который будет подбираться, в третьей - общая прибыль от продажи всех деталей. Также введем дополнительный столбец, в котором будем считать реальное время, затраченное на каждом типе станка, для некоторого плана выпуска.
Разработка модели. Введём в таблицу данные в соответствии с условием задачи.
А | В | С | D | Е |
11 | Тип Станков | Время на 1 деталь | Резерв Времени | Реальное время |
12 | А | В | ||
13 | Токарные | 25 | 40 | 800 |
14 | Сверильные | 28 | 35 | 700 |
15 | Шлифовальные | 35 | 25 | 575 |
16 | Прибыль | 25 | 30 | |
17 | ||||
18 |
Компьютерная модель. 1. В ячейках В17 и С17 (план выпуска х и у) будет
подбираться значение, поэтому ничего не вводим.
2. В ячейку общей прибыли В18 введите формулу: прибыль от деталей; умноженная на их количества (х) плюс прибыль от деталей В, умноженная на их количество (у) (=1316*В17+С16*С17). Попробуйте ввести произвольный план выпуска и проверьте, как меняется при этом общая прибыль.
3. В каждую ячейку столбца реального времени введите формулу: время на одну деталь А, умноженное на их количество (х) плюс время на одну деталь В, умноженное на их количество (у) (например, в ячейке E13=B13*B17+C13*C17, аналогично в E14 Е15)
4. Установив курсор в ячейке со значением общей прибыли В18, которая по условию должна принимать наибольшее значение, зайдите в “Сервис→Поиск решения...”. Установите целевую ячейку $В$18 равной максимальному значению изменяя ячейки $В$17: $С$17 (выделите их вместе).
Ограничения. Так как стороны количество деталей х и у должно быть положительно и целое, то В17>= 0, С17 >= 0, В17 - цел, С17 - цел; также реально затраченное время по каждому станку не должно превышать резерва времени E13<=D13, E14<=D14, E15<=D15, затем нажмите кнопку Выполнить.
Анализ результатов. Проверьте, насколько соответствуют результаты цели моделирования. Проверьте универсальность построенной модели, изменив какие-нибудь начальные данные.
ЗАДАЧИ
1. Число 256 разбить на такие два положительных множителя, чтобы их сумма
была наименьшей.
2. Среди всех прямоугольников с заданной площадью выбрать тот, у которого периметр минимален.
3. Вовочке родители дали сумму в S=500 руб. с условием, чтобы он купил компакт-диски по b=90 руб. и аудио-диски по а=55 руб., причём максимальное количество денег он должен потратить на компакт-диски, а сдачу вернуть. Определить, сколько дисков и сколько аудио-дисков должен купить Вовочка и оставшуюся сдачу.
а). Должно быть потрачено максимальное количество денег.
б). Должно быть куплено максимальное количество дисков (любых).
4. В опытном хозяйстве установили, что полноценный дневной рацион животных
должен включать не - менее 6 ед. питательного вещества А и не менее 12 ед. вещества В и не менее 4 ед. вещества С. Для кормления животных используют 2 вида кормов I и II. В таблице показано, сколько единиц каждого питательного вещества содержит 1 кг. корма каждого вида:
Питательное вещество | I | II |
А В С | 2 2 0 | 1 4 4 |
Цена 1 кг корма 1 равна 5 ден. ед., корма II - 6 ден. ед. Сколько корма каждого вида необходимо расходовать ежедневно, чтобы затраты на него были минимальны.
5. Для изготовления батона хлеба надо 0,5 кг муки и 0,02кг сахара, а для изготовления сдобной булки надо 0,2 кг муки и 0,05 кг сахара. Батон стоит 3 булка стоит 4 рубля. У частного предприятия в наличии 40 кг муки и 2 кг сахара. Сколько нужно испечь батонов и булок, чтобы заработать больше денег?
б. Предприятие может выпускать продукцию двух видов: П1 и П2. Используется три вида ресурсов: оборудование, сырье и электроэнергия. Нормы расхода ресурсов резервы и прибыль от реализации продукции сведены в таблицу:
Ресурсы | Нормы расхода на единицу продукта | Резерв времени | |
П1 | П2 | ||
Оборудование Сырьё Электроэнергия | 2 1 2 | 3 1 1 | 31 12 20 |
Прибыль за единицу продукции | 40 | 25 |
Определите план выпуска продукции обоих видов, при котором прибыль предприятия будет максимальной.
КОНТРОЛЬНАЯ РАБОТА “ОСНОВЫ МОДЕЛИРОВАНИЯ”
1. Из прямоугольного листа жести с длиной а = 30 см, шириной b = 20 см надо изготовить короб с объёмом 400 см2. Для этого из листа вырезают четыре угловых квадрата. Определить длину стороны х вырезаемого квадрата.
2. Предприятие располагает тремя видами сырья А, В, С и может выпускать одну и
ту же продукцию двумя способами. При этом за 1 час работы первым способом выпускается 20 единиц продукции, а вторым способом -, 30 единиц продукции. Количество сырья каждого вида, расходуемого за 1 ч при различных способах производства, и запасы сырья приведены в таблице:
Сырьё | Способ производства | Запасы сырья | |
Мука Сахар дрожжи | 10 20 15 | 20 10 15 | 100 100 90 |
Определите план производства, при котором будет выпущено наибольшее количество продукции.
