Методические рекомендации для выполнения практических работ в табличном процессоре Excel
Частное учреждение - профессиональная образовательная организация
«КРАСНОДАРСКИЙ ТЕХНИКУМ УПРАВЛЕНИЯ, ИНФОРМАТИЗАЦИИ И СЕРВИСА»
Методические указания для выполнения практических работ в табличном процессоре 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 (значение числа х) будет подбираться значение, поэтому ничего не вводим.