Micrisoft Office Excel 2007 – специальная программа Windows, позволяющая составлять различные таблицы с вводимыми данными. Более того, данная программа позволяет решать уравнения.
Открываем Excel 2007. Для наиболее простого решения уравнения воспользуйтесь функцией «поиск решений». Правда, во многих стандартных пакетах Office данная надстройка не установлена. Чтобы установить, откройте параметры Office Excel, которые находятся в правом нижнем углу всплывающего нижнего диалогового окна. В открывшемся меню кликаем в следующей последовательности: «надстройки» - «Поиск решения» - «перейти».
После перехода установите галочку рядом с пунктом «поиск решения» и нажмите OK.
Затем Excel выполнит настройку программы.
Затем, чтобы решить уравнение, введите его в поле листа. Пусть ваше уравнение с двумя переменными: F(x1,x2)=3×1+2×2 – max, в случае определенных ограничений:
- X1 - x2 ≥ -2
- 3×1 - 2×2 ≤ 6
- 2×1+3×2 ≥ 2
- X2 ≤ 3
- X1 ≥ 0
- X2 ≤ 0
Введите в колонку А таблицы Excel переменные х1 и x2. Затем выделите синим цветом поле, где расположены полученные значения переменных. Затем в колонке А введите саму функцию F(x1, x2)=. А справа от нее выделите красным цветом ту ячейку, в которой будет находиться значение данной функции.
Затем в красное поле введите само уравнение 3×1+2×2. Учтите, что х1 – ячейка В1, а х2 – ячейка В2.
Теперь введите в поле все ограничения.
Затем перейдите в раздел «поиск решений» (папка данные). Найдите поле «установить целевую ячейку», куда нужно поставить красную ячейку. Напротив «=» пишем максимальное значение.
В поле «изменяя ячейки» добавьте синие ячейки – х1, х2.
Если вы ввели все ограничения, проверьте их правильность, после чего нажмите кнопку «выполнить». В случае если все данные введены верно, то программа должна рассчитать неизвестные. В нашем случае х1=4, ч2=3 и F(x1,x2)=18. Уравнение решено.
Цель урока : продолжить формирование навыков работы с электронными таблицами.
- обучающие: формировать умения создания, редактирования, форматирования и выполнения простейших вычислений в электронных таблицах.
- развивающие: расширить представления учащихся о возможных сферах применения электронных таблиц; развивать навыки аналитического мышления, речи и внимания.
- воспитательные: формировать и воспитывать познавательный интерес; прививать навыки самостоятельности в работе.
План урока.
- Организационный момент.
- Актуализация знаний учащихся.
- Проверка домашнего задания.
- Решение задач.
- Самостоятельное решение задачи.
- Подведение итогов. Оценки.
- Домашнее задание.
Ход урока
1. Организационный момент.
Сообщить тему урока, сформулировать цели и задачи урока.
Сегодня мы вновь окажемся в гостях у маленького великана Васи в Сказочной стране. Ему, как всегда, требуется ваша помощь, ребята.
Сможете ли вы помочь Васе? Сейчас проверим!
2. Актуализация знаний учащихся.
1) Устно ответить на вопросы.
| A | B | C | D | |
| 1 | 2 | 1 | =A1+3*B1 | =A1^2+B1 |
| 2 | 4 | 6 | =A2+3*B2 | =A2^2+B2 |
- Что такое электронная таблица?
- Какие основные элементы электронной таблицы вам известны?
- Как задается имя ячейки (строки, столбца) в электронной таблице?
- Что может быть содержимым ячейки?
- Число 1 находится в столбце..., в строке..., в ячейке с адресом...
- Число 4 находится в ячейке с адресом...
- Каковы правила записи формул в ячейках?
- Чему равно значение, вычисляемое по формуле, в ячейке С1?
- Чему равно значение, вычисляемое по формуле, в ячейке D2?
2) Какой результат будет получен в ячейках с формулами?
| А | В | |
| 1 | 25 | 4 |
| 2 | 2 | =A1*B1/2 |
| 3 |
Ответ: 25*4/2=50
| A | B | C | D | |
| 1 | 5 | 2 | 1 | |
| 2 | 6 | 8 | 3 | |
| 3 | 8 | 3 | 4 | |
| 4 | =СУММ(B1:D3) |
- Что означает запись =СУММ(В1:D3)?
- Сколько элементов содержит блок В1:D3? Ответ: 9.
- Содержимое ячейки D3? Ответ: 5+2+1+6+8+3+8+3+4= 40
3) Проверка домашнего задания
Результаты соревнований по плаванию
Один ученик рассказывает, как он выполнил домашнее задание (через проектор).
| № | Ф.И.О. | 1 | 2 | 3 | Лучшее время | Среднее время | Отклонение | |
| 1 | Лягушкин | 3.23 | 3.44 | 3.30 | ||||
| 2 | Моржов | 3.21 | 3.22 | 3.24 | ||||
| 3 | Акулов | 3.17 | 3.16 | 3.18 | ||||
| 4 | Рыбин | 3.24 | 3.20 | 3.18 | ||||
| 5 | Черепахин | 3.56 | 3.44 | 3.52 | ||||
| Максимальное отклонение | ||||||||
- Среднее время для каждого спортсмена находится как среднее арифметическое трех его заплывов.
- В ячейку "Лучшее время" записывается минимальный результат из 3 заплывов.
- В ячейку "Лучший результат соревнований" записывается минимальное время из столбца.
- В столбец "Отклонение" записывается разность между лучшим временем спортсмена и лучшим результатом соревнований.
- В ячейку "Максимальное отклонение" записывается максимальное значение столбца.
| Результаты соревнований по плаванию | |||||||
| № | Ф.И.О. | 1 | 2 | 3 | Лучшее время | Среднее время | Отклонение |
| 1 | Лягушкин | 3,23 | 3,44 | 3,30 | 3,23 | 3,32 | 0,07 |
| 2 | Моржов | 3,21 | 3,22 | 3,24 | 3,21 | 3,22 | 0,05 |
| 3 | Акулов | 3,17 | 3,16 | 3,18 | 3,16 | 3,17 | 0,00 |
| 4 | Рыбин | 3,24 | 3,20 | 3,18 | 3,18 | 3,21 | 0,02 |
| 5 | Черепахин | 3,56 | 3,44 | 3,52 | 3,44 | 3,51 | 0,28 |
| Лучший результат соревнований | 3,16 | ||||||
| Среднее время участников соревнований | 3,29 | ||||||
| Максимальное отклонение | 0,28 | ||||||
4) Решение простых задач.
Маленький великан Вася решил отремонтировать забор вокруг своего огорода и вскопать его под посадку овощей (наступила очередная весна), разметить грядки прямоугольной формы. Для работы ему потребовалось найти длину забора и площадь участка. Но ведь в школе он никогда не учился. Поможем Васе.
№ 1. Вычислить периметр и площадь прямоугольника со сторонами:
а) 3 и 5; б) 6 и 8; в) 10 и 7.
Эту задачу обсуждаем совместно с детьми:
- Как оформить таблицу?
- Какие формулы использовать?
- Как использовать уже записанные формулы для следующего прямоугольника?
Оформление таблицы – на доске и в тетрадях.
В то же время другой ученик самостоятельно решает следующую задачу и представляет свое решение учащимся (через проектор).
Обсудив решение задачи № 2, переходим к решению следующей.
Один ученик показывает, как работать с формулами, другой – как использовать функцию суммирования, числовой формат (общий, денежный) и т.д. (Таблица уже готова, ученикам предстоит ввести формулы, использовать суммирование и получить ответ).
№ 3. Посчитайте, используя ЭТ, хватит ли Васе 150 рублей, чтобы купить все продукты, которые ему заказала мама, и хватит ли на чипсы за 10 рублей? Сдачу мама разрешила положить в копилку. Сколько рублей попадет в копилку?
Предполагаемое решение:
| № | Наименование | Цена в рублях | Количество | Стоимость |
| 1 | Хлеб | 9,6 | 2 | =C2*D2 |
| 2 | Кофе | 2,5 | 5 | =C3*D3 |
| 3 | Молоко | 13,8 | 2 | =C4*D4 |
| 4 | Пельмени | 51,3 | 1 | =C5*D5 |
| =СУММ(E2:E5) | ||||
| После покупок останется | =150-E6 | |||
| После покупки чипсов останется | =D7-10 | |||
5) Самостоятельное решение задачи.
Маленький великан Вася часто бывал в гостях у жителей Цветочного города.
Собираясь на пляж, веселые человечки решили запастись прохладительными напитками. Незнайка взял с собой 2 литра кваса, 1 литр газировки и 1 литр малинового сиропа, Пончик – 3 литра газировки и 2 литра малинового сиропа, Торопыжка – 2 литра газировки, доктор Пилюлькин – 1 литр кваса и 1 литр касторки.
- Сколько литров напитков каждого вида взяли все человечки вместе?
- Сколько всего литров напитков взял с собой каждый из человечков?
- Сколько всего литров напитков взяли все человечки вместе?
Оформите таблицу произвольно и сохраните в своей личной папке.
Результат работы.
| Веселые человечки. Напитки. | |||||
| Напиток | Незнайка | Пончик | Торопыжка | Пилюлькин | Всего |
| Квас, л | 2 | 0 | 0 | 1 | 3 |
| Газировка, л | 1 | 3 | 2 | 0 | 6 |
| Сироп, л | 1 | 2 | 0 | 0 | 9 |
| Касторка, л | 0 | 0 | 0 | 1 | 1 |
| ИТОГО: | 4 | 5 | 2 | 2 | 13 |
7) Подведение итогов. Оценки.
8) Домашнее задание.
Подумайте и решите эту задачу, если известны еще следующие величины.
Как изменится таблица? Какие формулы появятся?
Известно, что 1 литр кваса в Цветочном городе стоит 1 монету, 1 литр газировки – 3 монеты, 1 литр малинового сиропа – 6 монет, 1 литр касторки – 2 монеты.
- Сколько монет истратил на покупку напитков каждый человечек?
- Сколько монет затрачено на покупку напитков каждого вида?
- Сколько потрачено денег всеми человечками вместе?
Литература
- Информатика. Задачник-практикум в 2 т. /Под ред. И.Г.Семакина, Е.К.Хеннера – М.: Лаборатория Базовых Знаний, 2010.
- Ефимова О. Курс компьютерной технологии с основами информатики. – М.: ООО “издательство АСТ”; АВF, 2005.
Существует множество задач, решение которых может быть существенно облегченно с помощью инструмента Поиск решений. Но для этого следует начать с организации рабочего листа в соответствии с пригодной для поиска решений моделью, для чего нужно хорошо понимать взаимосвязи между переменными и формулами. Хотя постановка задачи обычно представляет основную сложность, время и усилия, затраченные на подготовку модели, вполне оправданы, поскольку полученные результаты могут уберечь от излишней траты ресурсов, при неправильном планирование, помогут увеличить прибыль за счет оптимального управление финансами или выявить наилучшее соотношение объемов производства, запасов и наименований продукции.
За своей сущностью задача оптимизации – это математическая модель определенного процесса производства продукции, его распределение, хранение, переработки, транспортирования, покупки или продажи, выполнение комплекса сервисных услуг и т.д. Это обычная математическая задача типа: Дано/Найти/При условии, но которая имеет множество возможных решений. Таким образом, задача оптимизации – задача выбора з множества возможных вариантов наилучшего, оптимального. Решение такой задачи называют планом или программой , например, говорят – план производства или программа реконструкции. Другими словами это те неизвестные которые нам надо найти, например, количество продукции которое даст максимальную прибыль. Задача оптимизации – поиск экстремума, то есть, максимального или минимального значения определенной функции, которую называют целевой функцией , например, это может быть функция прибыли – выручка минус затраты. Так как и всё в мире ограничено (время, деньги, природные и человеческие ресурсы), в задачах оптимизации всегда есть определенные ограничения , например, количество метала, рабочих и станков на предприятии по изготовлению деталей. Далее рассмотрен пример оформления очень простой задачи оптимизации, но с помощью его можно легко понять организации о построение таблицы для эффективности решений практический проблем оптимизации.
Имеем классическую задачу когда фирма производит два вида продукции (товар А и товар Б) по определенной цене, на их производство требуется 4 вида ресурсов (ресурс 1, ресурс 2, ресурс 3, ресурс 4), которые есть в наличие на фирме в определенном количестве (Запас), также имеется информация сколько нужно каждого ресурса на производство единицы продукции, соответственно товара А и товара Б. Нужно найти, то количество товара А и товара Б, которое максимизирует доход (выручку) (см. рис.).
Далее нам надо сделать взаимосвязи между ограничениями, планом и целевой функцией. Для этого мы строим дополнительный столбец (Использовано), в котором вводим формулуСУММПРОИЗВ (Норма; План). Норма - это затраты определенного ресурса на производство единицы продукции товара А и Б, а План – количество продукции, которое мы ищем. В ячейки Доход вводим формулу СУММПРОИЗВ (Цена; План). Таким образом мы заполнили формулами столбец Использовано и ячейку Доход. Так как план это переменные от которых зависит количество использованных ресурсов и доход, то ячейки с формулами напрямую зависят от данных, которые там появятся в результате поиска решений. С выше сказанного можно сделать следующие выводы, что каждая задача оптимизации обязательно должна иметь три компоненты:
неизвестные (что ищем, то есть, план);
ограничение на неизвестные (область поиска);
целевая функция (цель, для которой ищем экстремум).
Мощным средством анализа данных Excel является надстройка Solver (Поиск решения) . С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета. Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных та нелинейных задач оптимизации, используется с 1991 года. Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:
количество неизвестных (decision variable) – 200;
количество формульных ограничений (explicit constraint) на неизвестные – 100;
количество предельных условий (simple constraint) на неизвестные – 400.
Разработчик программы Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Высокая эффективность их применения объясняется интеграциею программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса. По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007 , щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel , а затем выберите категорию Надстройки . В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти . В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК .
В Excel 2003 и ниже выберите команду Сервис/Надстройки , в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office).
Процедура поиска решения 1. Создайте таблицу с формулами, которые устанавливают связи между ячейками.
2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду: - В Excel 2007 Данные/Анализ /Поиск решения ;
В Excel 2003 и ниже Tools > Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки. 3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, - Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа. 4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата. 5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение.
6. Щелкните на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)
7. Щелкнув на кнопке Solver (Выполнить), запустите процесс поиска решения.
8. Когда появится диалоговое окно Solver Results (Результаты поиска решения), выберите переключатель Keep Solve Solution (Сохранить найденное решение) или Restore Original Values (Восстановить исходные значения). 9. Щелкните на кнопке ОК.
Параметры средства Поиск решения Максимальное время - служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.
Предельное число итераций - управляет временем решения задачи путем ограничения числа вычислительных циклов (итераций). Относительная погрешность - определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений. Допустимое отклонение - предназначен для задания допуска на отклонение от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. Чем больше значение допуска, тем меньше времени требуется на поиск решения. Сходимость - применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Линейная модель - служит для ускорения поиска решения путем применения к задаче оптимизации линейной модели. Нелинейные модели предполагают использование нелинейных функций, фактора роста и экспоненциального сглаживания, что замедляет вычисления. Неотрицательные значения - позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых не было задано соответствующее ограничение в диалоговом окне Добавить ограничение. Автоматическое масштабирование - используется, когда числа в изменяемых ячейках и в целевой ячейке существенно различаются. Показывать результаты итераций - приостанавливает поиск решения для просмотра результатов отдельных итераций. Загрузить модель - после щелчка на этой кнопке отрывается одноименное диалоговое окно, в котором можно ввести ссылку на диапазон ячеек, содержащих модель оптимизации. Сохранить модель - служит для отображения на экране одноименного диалогового окна, в котором можно ввести ссылку на диапазон ячеек, предназначенный для хранения модели оптимизации. Оценка линейная - выберите этот переключатель для работы с линейной моделью. Оценка квадратичная - выберите этот переключатель для работы с нелинейной моделью. Разности прямые - используется в большинстве задач, где скорость изменения ограничений относительно невысока. Увеличивает скорость работы средства Поиск решения. Разности центральные - используется для функций, имеющих разрывную производную. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдано сообщение о том, что получить более точное решение не удается. Метод поиска Ньютона - требует больше памяти, но выполняет меньше итераций, чем в методе сопряженных градиентов. Метод поиска сопряженных градиентов - реализует метод сопряженных градиентов, для которого требуется меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно большая и необходимо экономить память или если итерации дают слишком малое отличие в последовательных приближениях.
Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.
«Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации». И если ранее вам еще не приходилось его использовать, то сейчас самое время исправить это.
Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»
Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».
После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке
Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.
Пример первый .
Допустим, что вы занимаете пост начальника крупного отдела производства и необходимо правильно распределить премии сотрудникам. Допустим, общая сумма премий составляет 100 000 рублей, и необходимо, чтобы премии были пропорциональны окладам.
То есть, сейчас нам необходимо подобрать правильный коэффициент пропорциональности, чтобы определить размер премии относительно оклада.
В первую очередь необходимо быстро составить (если ее еще нет) таблицу, где будут хранится исходные формулы и данные, согласно которым и можно будет получить желаемый результат. Для нас этот результат – суммарная величина премии. А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично. В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).
Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.
Под «1 » обозначена наша целевая ячейка. Она может быть только одна.
«2 » — это возможные варианты оптимизации. Всего можно выбрать «Максимальное», «Минимальное» или «Конкретное» возможные значения. И если вам необходимо именно конкретное значение, то его нужно указать в соответствующей графе.
«3 » — изменяемых ячеек может быть несколько (целый диапазон или же отдельно указанные адреса). Ведь именно с ними и будет работать Excel, перебирая варианты так, чтобы получилось значение, заданное в целевой ячейке.
«4 » — Если понадобиться задать ограничения, то стоит воспользоваться кнопкой «Добавить», но мы это рассмотрим чуть позже.
«5 » — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.
Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.
Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», «<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).
Но в нашем примере ограничение может быть лишь одно – положительный коэффициент. Задать его, конечно, можно несколькими способами – либо используя «Добавить» (что называют «явно указать ограничение»), либо просто отметить действующей функцию «Сделать переменные без ограничений неотрицательными». Это можно сделать в надстройке «Поиск решения», нажав на кнопочку «Параметры».
Кстати, после подтверждения параметров и запуска программы (кнопочка «Выполнить»), вы сможете в таблице просмотреть полученный результат. Тогда программа продемонстрирует окошко «результатов поиска».
Если продемонстрированный результат полностью вам подходит, тогда останется только вновь подтвердить его (кнопочка «ОК»), что зафиксирует результат в вашей таблице. Если же что-то в расчетах вас не устраивает, то необходимо отменить результат (кнопочка «Отмена»), вернуться к предыдущему состоянию нашей таблицы и исправить допущенные ошибки.
Правильное решение задачи примера должно получиться вот таким
Очень важно — чтобы получить правильный результат даже при малейшем изменении исходных данных необходимо перезапустить «Поиск решений».
Чтобы более подробно взглянуть на то, как действует данная программа, давайте разберем еще один пример.
Допустим, вы являетесь владельцем крупного мебельного предприятия и необходимо наладить производство таким образом, чтобы получить максимально возможную прибыль. Вы производите только книжные полки, при этом всего двух моделей – «А» и «В», производство которых ограничивается исключительно наличием (или отсутствием) высококачественных досок, а также машинным временем (обработка на станке).
Модель «А» требует 3 м 3 досок, а модель «В» — на 1 м 3 больше (то есть – 4). От своих поставщиков вы за неделю получаете максимум 1700 м 3 досок. При этом модель «А» создается за 12 минут работы станка, а «В» — за 30 минут. Всего в неделю станок может работать не более 160 часов.
Вопрос – сколько всего изделий (и какой модели), должна выпускать фирма за неделю, чтобы получить максимально возможную прибыль, если полочка «А» дает 60 рублей прибыли, а «В» — 120?
Поскольку порядок действия известен, то начинаем создавать необходимую нам таблицу с данными и формулами. Расположение ячеек, как и ранее, вы можете установить на свое усмотрение. Или же воспользоваться нашим
Любым удобным способом запускаем наш «Поиск решений», вводим данные, производим настройку.
Итак, рассмотрим то, что мы имеем. В целевой ячейке F7 содержится формула, которая и рассчитает прибыль. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).
Конечно, в этом случае можно было не указывать адреса ячеек, а напрямую прописать необходимые цифровые значения, однако если использовать адреса, то изменения ограничений можно будет проводить и в таблице, что поможет рассчитывать прибыль этого предприятия в будущем, при смене исходных данных.
Активируем программу, и она подготавливает решение.
Впрочем, это не единственное решение и у вас вполне может выскочить другой результат. Это может произойти даже в том случае, если все данные были указаны верно и ошибок в формулах тоже не было
Да. Это может произойти даже в том случае, если мы сказали программе искать целое число. И если это вдруг произошло, то необходимо просто провести дополнительную настройку «Поиска решений». Открываем окно «Поиска решений» и входим в «Параметры».
Наш верхний параметр отвечает за точность. Чем он меньше, тем выше точность и в нашем случае это значительно повышает шансы получить целое число. Второй параметр («Игнорировать целочисленные ограничения») и дает ответ на вопрос, как мы смогли получить такой ответ с тем, что в запросе явно указали целое число. «Поиск решений» просто проигнорировал это ограничение в связи с тем, что так ему сказали расширенные настройки.
Так что будьте предельно внимательны в будущем.
Третий и, пожалуй, последний пример. Попробуем минимизировать затраты транспортной компании используя поиск решений в Excel 2010.
Итак, строительная компания дает заказ на перевозку песка, который берется от 3 поставщиков (карьеров). Его необходимо доставить 5 разным потребителям (которыми выступают строительные площадки). Стоимость доставки груза включена в себестоимость объекта, так что наша задача обеспечить доставку груза на стройплощадки с минимальными затратами.
Мы имеем – запас песка в карьере, потребность стройплощадок в песке, затрату на транспортировку «поставщик-потребитель».
Необходимо найти схему оптимальной перевозки груза (куда и откуда), при которой общая затрата на перевозку была бы минимальной.
Серые ячейки нашей таблицы содержат формулы суммы по столбцам и строкам, а целевая ячейка – формула для общего подсчета затраты на доставку груза. Запускаем наш «Поиск решения» и вносим необходимые настройки
После этого приступаем к поиску решения этой задачки
Впрочем, не будем забывать, что достаточно часто транспортные задачи могут быть усложнены некоторыми дополнительными ограничителями. Допустим, возникло осложнение на дороге и теперь из карьера 2 просто технически невозможно доставить груз на стройплощадку 3. Чтобы учесть это, необходимо просто дописать дополнительное ограничение «$D$13=0». И если теперь запустить программу, то результат будет иным
Напоследок осталось сказать только о выборе метода решения. И если задачка действительно очень сложная, то чтобы получить необходимый результат, скорее всего, понадобиться подобрать необходимый метод решения.
Вот и все по данному вопросу.
Мы выполнили поиск решений в Excel 2010 — для решения сложных задач
Решение нелинейных уравнений и систем»
Цель работы : Изучение возможностей пакета Ms Excel 2007 при решении нелинейных уравнений и систем. Приобретение навыков решения нелинейных уравнений и систем средствами пакета.
Задание1. Найти корни полинома x 3 - 0,01x 2 - 0,7044x + 0,139104 = 0.
Для начала решим уравнение графически. Известно, что графическим решением уравнения f(x)=0 является точка пересечения графика функции f(x) с осью абсцисс, т.е. такое значение x, при котором функция обращается в ноль.
Проведем табулирование нашего полинома на интервале от -1 до 1 с шагом 0,2. Результаты вычислений приведены на ри., где в ячейку В2 была введена формула: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеется не более трех вещественных корней, то графическое решение поставленной задачи найдено. Иначе говоря, была проведена локализация корней, т.е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], и .
Теперь можно найти корни полинома методом последовательных приближений с помощью команды Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра .
После ввода начальных приближений и значений функции можно обратиться к команде Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра и заполнить диалоговое окно следующим образом.
В поле Установить в ячейке дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения (уравнение должно быть записано так, чтобы его правая часть не содержала переменную). В поле Значение вводим правую часть уравнения, а в поле Изменяя значения ячейки дается ссылка на ячейку, отведенную под переменную. Заметим, что вводить ссылки на ячейки в поля диалогового окна Подбор параметров удобнее не с клавиатуры, а щелчком на соответствующей ячейке.
После нажатия кнопки ОК появится диалоговое окно Результат подбора параметра с сообщением об успешном завершении поиска решения, приближенное значение корня будет помещено в ячейку А14.
Два оставшихся корня находим аналогично. Результаты вычислений будут помещены в ячейки А15 и А16.
Задание 2. Решить уравнение e x - (2x - 1) 2 = 0.
Проведем локализацию корней нелинейного уравнения.
Для этого представим его в виде f(x) = g(x) , т.е. e x = (2x - 1) 2 или f(x) = e x , g(x) = (2x - 1) 2 , и решим графически.
Графическим решением уравнения f(x) = g(x) будет точка пересечения линий f(x) и g(x).
Построим графики f(x) и g(x). Для этого в диапазон А3:А18 введем значения аргумента. В ячейку В3 введем формулу для вычисления значений функции f(x): = EXP(A3), а в С3 для вычисления g(x): = (2*A3-1)^2.
Результаты вычислений и построение графиков f(x) и g(x):
На графике видно, что линии f(x) и g(x) пересекаются дважды, т.е. данное уравнение имеет два решения. Одно из них тривиальное и может быть вычислено точно:
Для второго можно определить интервал изоляции корня: 1,5 < x < 2.
Теперь можно найти корень уравнения на отрезке методом последовательных приближений.
Введём начальное приближение в ячейку Н17 = 1,5, и само уравнение, со ссылкой на начальное приближение, в ячейку I17 = EXP(H17) - (2*H17-1)^2.
и заполним диалоговое окно Подбор параметра .
Результат поиска решения будет выведен в ячейку Н17.
Задание 3 . Решить систему уравнений:
Прежде чем воспользоваться описанными выше методами решения систем уравнений, найдем графическое решение этой системы. Отметим, что оба уравнения системы заданы неявно и для построения графиков, функций соответствующих этим уравнениям, необходимо разрешить заданные уравнения относительно переменной y.
Для первого уравнения системы имеем:
Выясним ОДЗ полученной функции:
Второе уравнение данной системы описывает окружность.
Фрагмент рабочего листа MS Excel с формулами, которые необходимо ввести в ячейки для построения линий, описанных уравнениями системы. Точки пересечения линий изображенных являются графическим решением системы нелинейных уравнений.
Не трудно заметить, что заданная система имеет два решения. Поэтому процедуру поиска решений системы необходимо выполнить дважды, предварительно определив интервал изоляции корней по осям Оx и Oy . В нашем случае первый корень лежит в интервалах (-0.5;0) x и (0.5;1) y , а второй - (0;0.5) x и (-0.5;-1) y . Далее поступим следующим образом. Введем начальные значения переменных x и y, формулы отображающие уравнения системы и функцию цели.
Теперь дважды воспользуемся командой Данные→Анализ→Поиск решений, заполняя появляющиеся диалоговые окна.
Сравнив полученное решение системы с графическим, убеждаемся, что система решена верно.
Задания для самостоятельного решения
Задание 1 . Найти корни полинома
Задание 2 . Найдите решение нелинейного уравнения.
Задание 3 . Найдите решение системы нелинейных уравнений.
