с помощью какой команды можно решать простые задачи оптимизации в ms excel
Решение задач оптимизации в помощью электронных таблиц Excel
Решение задач оптимизации с помощью электронных таблиц Excel
Учитель информатики и ИКТ
Кабанова Татьяна Витальевна
Приморского района Санкт-Петербурга
Тема. Решение задач оптимизации в Excel .
Тип урока: обобщение и систематизация знаний.
образовательные — обобщение и систематизация знаний по теме «Обработка числовой информации»
закрепление знаний об общих принципах работы табличного процессора Microsoft Excel;
практическое применение изученного материала, приобретение навыков в составлении таблиц разного типа;
развитие умения выбирать наиболее оптимальную структуру таблицы, создать и оформить таблицу;
формирование представления о вычислениях в электронной таблице как наиболее важных в изучении информатики и широко применяемых на практике.
развивающие — продолжить развитие логического мышления, глубины и гибкости ума.
развитие познавательного интереса, речи и внимания учащихся;
развитие способности логически рассуждать;
формирование информационной культуры и потребности приобретения знаний;
развитие умения применять полученные знания для решения задач различных предметных областей.
развивать у школьников теоретическое мышление, формирование операционного мышления, направленного на выбор оптимальных решений.
воспитательные — продолжать воспитывать информационную культуру, общечеловеческие качества личности школьника
воспитание творческого подхода к работе, желания экспериментировать;
воспитание трудолюбия, чувства уважения к науке;
продолжить воспитывать культуру общения;
продолжить формировать чувство долга, настойчивости, дисциплинированность; продолжить формирование творческих, исследовательских качеств учащихся;
продолжить воспитывать эстетический вкус.
Организационный этап. (Цель: настроить учащихся на работу на уроке)
Сегодня цель нашего урока: закрепить и обобщить знания, полученные по этой теме, развивать умение применять полученные знания для решения задач из различных предметных областей.
II . Актуализация знаний и фронтальный опрос
– Итак, что мы с вами знаем и умеем делать: создавать редактировать и формировать табличный документ, выполнять вычисления по формулам, применять относительные и абсолютные ссылки, использовать функции. Давайте вспомним и повторим то, что нам уже знакомо.
Вопросы
Ответ
Как называется документ, созданный в электронных таблицах?
Что является основным элементом электронной таблицы?
Что не может включать в себя формула в электронных таблицах?
Как записывают в формуле адрес ячейки, если необходимо отменить принцип относительной адресации при переносе формулы?
Какой формат числа вы примените для отображения:
Времени начала уроков?
Дней рождения знакомых?
Порядковых номеров в списке?
Каким будет результат вычислений в ячейке С1?
Какие виды адресации ячеек вы знаете?
Относительная, абсолютная, смешанная.
В каких случаях необходимо использовать абсолютные адреса ячеек в формулах?
Для указания фиксированного адреса ячейки.
В ячейке электронной таблицы С5 записана формула =B5*А5. Какая формула будет получена из нее при копировании в ячейку С6?
Дан фрагмент электронной таблицы:
Это – наука, производство, бухгалтерия, торговля, статистика, экология
Таким образом, можно сделать вывод: области применения электронных таблиц очень разнообразны, без них не может обойтись практически ни один современный специалист.
III. Изложение нового материала
Человек, совершая те или иные деяния, стремился вести себя таким образом, чтобы результат, достигаемый как следствие некоторого поступка, оказался в определенном смысле наилучшим. Двигаясь из одного пункта в другой, он стремился найти кратчайший среди возможных путь. Строя жилище, он искал такую его геометрию, которая при наименьшем расходе топлива, обеспечивала приемлемо комфортные условия существования. Занимаясь строительством кораблей, он пытался придать им такую форму, при которой вода оказывала бы наименьшее сопротивление. Можно легко продолжить перечень подобных примеров.
Задачи на отыскание оптимального решения называются задачами оптимизации. Применяемые в процессе оптимизации методы получили название методов оптимизации. При постановке и решении задач оптимизации возникают два вопроса: что и как оптимизировать?
Ответ на первый вопрос получается как результат глубокого изучения проблемы, которую предстоит решить. Выявляется тот параметр, который определяет степень совершенства решения возникшей проблемы. Этот параметр обычно называют целевой функцией или критерием качества. Далее устанавливается совокупность величин, которые определяют целевую функцию. Наконец, формулируются все ограничения, которые должны учитываться при решении задачи. После этого строится математическая модель, заключающаяся в установлении аналитической зависимости целевой функции от всех аргументов и аналитической формулировки сопутствующих задаче ограничений. Далее приступают к поиску ответа на второй вопрос.
Понятие «наилучший, оптимальный» может быть выражено количественными критериями – минимум затрат, минимум времени, максимум прибыли и т.д. Для решения таких задач в ЭТ используется надстройка Поиск решения.
При решении задач оптимизации с помощью MS Excel применяют алгоритм:
Ищем оптимальное решение задачи с неизвестными параметрами в Excel
«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил. Рассказываем, как освоить функцию поиска решений.
Основные параметры поиска решений
Найти решение задачи можно тремя способами. Во-первых, вручную перебирать параметры, пока не найдется оптимальное соотношение. Во-вторых, составить уравнение с большим количеством неизвестных. В-третьих, вбить данные в Excel и использовать «Поиск решений». Последний способ самый быстрый и покажет максимально точное решение, если знать, как использовать функцию.
Итак, мы решаем задачу с помощью поиска решений в Excel и начинаем с математической модели. В ней четыре типа данных: константы, изменяемые ячейки, целевая функция и ограничения. К поиску решения вернемся чуть позже, а сейчас разберемся, что входит в каждый из этих типов:
Константы — исходная информация. К ней относится удельная маржинальная прибыль, стоимость каждой перевозки, нормы расхода товарно-материальных ценностей. В нашем случае — производительность работников, их оплата и норма в 1000 изделий. Также константа отражает ограничения и условия математической модели: например, только неотрицательные или целые значения. Мы вносим константы в таблицу цифрами или с помощью элементарных формул (СУММ, СРЗНАЧ).
Изменяемые ячейки — переменные, которые в итоге нужно найти. В задаче это распределение 1000 изделий между работниками с минимальными затратами. В разных случаях бывает одна изменяемая ячейка или диапазон. При заполнении функции «Поиск решений» важно оставить ячейки пустыми — программа сама найдет значения.
Целевая функция — результирующий показатель, для которого Excel подбирает наилучшие показатели. Чтобы программа понимала, какие данные наилучшие, мы задаем функцию в виде формулы. Эту формулу мы отображаем в отдельной ячейке. Результирующий показатель может принимать максимальное или минимальное значения, а также быть конкретным числом.
Ограничения — условия, которые необходимо учесть при оптимизации функции, называющейся целевой. К ним относятся размеры инвестирования, срок реализации проекта или объем покупательского спроса. В нашем случае — количество дней и число работников.
Пример использования поиска решений
Теперь перейдем к самой функции.
1) Чтобы включить «Поиск решений», выполните следующие шаги:
Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».
3) Выделите целевую ячейку, которая должна показать максимум, минимум или определенное значение при заданных условиях. Для этого на панели нажмите «Данные» и выберете функцию «Поиск решений» (обычно она в верхнем правом углу).
4) Заполните параметры «Поиска решений» и нажмите «Найти решение».
Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.
В нашем примере следующие ограничения:
Получите карьерную поддержку
Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.
Подписаться на карьерную рассылку
Подписывайтесь на рассылку и получайте карьерные советы — от выбора индустрии и компании до лайфхаков по самоорганизации и развитию коммуникативных навыков.
Решение задач на оптимизацию с помощью MS Excel
Оптимизационные модели широко используются Среди них задачи подбора сбалансированного рациона питания, оптимизации ассортимента продукции, транспортная задача
Модели всех задач состоят элементов:
1. неизвестные величины, которые нужно найти при решении задачи.
2. Целевая величина, которая зависит целью, ключевым показателем эффективности или оптимальности модели.
3. условия, которым должны удовлетворять переменные.
Поиск решения такой модели рассмотрим такого вопроса:
Спрос «Автомеханик» составляет экземпляров, а «Инструмент» 7,5 тысячи
Определите оптимальное количество издаваемых журналов, которое обеспечит максимально выручку
Давайте разберемся, что здесь является переменными, что целевой функцией, что ограничениями.
Найти нам необходимо оптимальное количество издаваемых журналов каждого вида. типографиях условиях. что нам необходимо определить размер тиража каждого журнала напечатанного типографии. наши переменные.
По какому принципу что считать эффективным, что нет. Перед нами поставлена задача получить максимальную выручку. Таким образом, максимальная выручка.
Теперь ограничения. сказано, что каждая типография может выделить тираж только определенное время. Длительность печати тысячи единиц тиража каждого журнала каждой типографией известна.
Таким образом, произведение объема тиража печати тысячи единиц для каждой типографии быть больше заданного количества времени.
Еще одно важное ограничение, обычно переменные должны быть неотрицательными.
Попытаемся представить модель в Excel.
Переменные, то есть объем тиража, находятся B10:C12. Целевая D13. Обратите внимание, целевая функция построена формулой, ссылаясь данные (стоимость единицы тиража).
Также формулами подсчитывается фактическое время печати тиража (ячейки E3:E5).
Все готово, приступаем решению задачи надстройки.
Включается она через меню Поиск решений. пункта меню нет, войдите Надстройки галочкой соответствующую надстройку. Может понадобиться установочный комплект Office.
Перед Вами появится следующий диалог:
Здесь указываем адрес целевой ячейки, отмечаем, что привести значению, изменяя Диапазоны можно указывать станьте поле диалога нужные ячейки. Адрес автоматически попадет
Добавляем ограничения. После нажатия кнопки Добавить появляется диалог:
Вспоминаем. фактическое время печати тиража типографии превышать заданного лимита.
Для ограничение будет таким E3 ≤ D3. E3 должна быть формула суммы продолжительности печати тиража первого журналов типографии, полученной перемножением тиража времени.
Думаю, понятно, как ввести описанное ограничение.
Если нажать Ок, ограничение будет добавлено, закроется. Чтобы несколько раз диалог, сделана кнопка Добавить. Ограничение сохраняется, очищается для добавления следующего ограничения. Аналогично добавляем ограничения для оставшихся типографий.
Ограничения неотрицательности можно также задать этого для каждой ячейки тиража установить ограничение ≥0.
Но учитывая, что такие ограничения встречаются слишком часто, разработчики надстройки предусмотрели возможность быстрой установки ограничения неотрицательности для всех переменных модели. Нажимаем Ok, возвращаемся диалог кнопку Параметры.
Здесь достаточно отметить галочку Неотрицательные значения.
Через пару секунд Вы будете иметь оптимальное решение.
Теперь выберите Сохранить решение Ok.
Можете проверить решение, пробуя подставлять другие значения тиража, перераспределяя тираж между типографиями. Вам удастся улучшить результат.
Конечно, результат Бывают слишком сложные модели, модели совсем решений (модели ограничениями). Кроме того, если Вы обратили внимание, модели можно задать максимальное время решения, число итерраций, точность установки. для профессионалов.
Что радует, все настройки модели (целевая ячейка, область переменных, ограничения, параметры) сохраняются изменении исходных данных их вводить заново. Достаточно открыть надстройку повторный поиск решения.
Поиск решения задач в Excel с примерами
Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.
Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.
Решение задач оптимизации в Excel
Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).
В Excel для решения задач оптимизации используются следующие команды:
Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».
Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:
На основании этих данных составим рабочую таблицу:
Активизируем команду «Поиск решения» и вносим параметры.
После нажатия кнопки «Выполнить» программа выдает свое решение.
Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.
Решение финансовых задач в Excel
Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.
Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.
Оформим исходные данные в виде таблицы:
Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).
Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.
Решение эконометрики в Excel
Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.
Дано 2 диапазона значений:
Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.
Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).
Решение логических задач в Excel
В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.
Решение математических задач в Excel
Средствами программы можно решать как простейшие математические задачки, так и более сложные (операции с функциями, матрицами, линейными уравнениями и т.п.).
Условие учебной задачи. Найти обратную матрицу В для матрицы А.
Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.
Решение задач оптимизации в Excel
На этой странице вы найдете примеры решений различных оптимизационных задач с использованием пакета электронных таблиц MS Excel (используется как надстройка Поиск решения, так и ручные вычисления).
Задачи оптимизации и Excel
Задачи оптимизации имеют огромное прикладное значение и возникают в самых разных разделах экономики, техники, военного дела и т.п. В таких задачах нас интересуют поиск некоторого оптимального решения (минимизующего или максимизирующего целевую функцию: прибыль, затраты, калорийность и т.п.) в условиях ограничений (наличия ресурсов, дорог, времени, продуктов и т.п.).
Вот некоторые примеры экономических задач: минимизация расходов при формировании состава сырья (например, на текстильных предприятиях), оптимизация раскроя (например, на швейных производствах), минимизация расходов при формировании штатного расписания, оптимизация калорийности и стоимости рациона (как для людей, так и для животных), минимизация расходов на перевозку грузов по маршрутам, оптимизация расходов на изготовление при выборе ассортимента продукции, максимизация прибыли при формировании инвестиционной программы и др.
Часто эти задачи (даже учебные, даже в случае линейности) содержат более десяти переменных(а в случае, например, транспортных задач, и вовсе десятки), что делает ручные расчеты нерациональными. В то же время привычная для всех программа Excel прекрасно подходит для поиска решения.
Алгоритм решения с помощью надстройки «Поиск решения» следующий:
Excel вычислит оптимальные значения переменных и покажет их в ячейках, а также значение целевой функции. Дополнительно можно построить отчеты для анализа решения задачи.
Некоторые задачи оптимизации решаются не с помощью надстройки Поиск решения, а путем подбора параметра или ручных расчетов. Ниже вы найдете примеры разных задач, а также ссылки на другие разделы со сходными заданиями.
Задачи оптимизации: примеры в Excel
Задача 2. Для производства двух видов изделий А и В предприятие использует три вида сырья. Нормы расхода каждого вида сырья на изготовление единицы продукции данного вида в таблице 6. В ней же указаны прибыль от реализации единицы изделия каждого вида и общее количество сырья данного, которое может быть использовано предприятием.
Требуется такой составить такой план производства изделий А и В, при котором прибыль от реализации будет максимальной?
Задача 4. Для изготовления одного пирожка требуется 0,8 ед. начинки и 4 ед. теста, одного пирожного 4 ед. начинки и 0,5 ед. теста, одного рулета 2 ед. начинки и 2,5 ед. теста. Сколько пирожков, пирожных и рулетов нужно сделать кондитерской, если в наличии имеется 120 ед. теста и 300 ед. начинки?
Определите доход от реализации кондитерских изделий, если доход от продажи одного пирожка составляет 3 рубля, одного пирожного 2 рубля, одного рулета 1,5.
Для решения задачи используется ППП Excel.
Задача 5. Менеджер проекта по строительству нового торгового гипермаркета компании Наше дело надеется завершить проект за пару недель до Рождества.
После обзора оценок времени выполнения отдельных стадий выяснилось, что потребуются дополнительные инвестиции, чтобы сократить длительность проекта так, чтобы он действительно завершился вовремя. В таблице приведены оценки длительностей стадий и стоимость их сокращения на 1 и на 2 недели.
a. Нарисуйте сетевую диаграмму проекта и найдите критический путь.
b. Определите минимальную стоимость сокращения проекта на 5 недель.