Методические рекомендации работы с надстройкой Поиск решения в Exсel 2010

Министерство  сельского хозяйства и продовольствия  Самарской области

государственное бюджетное  профессиональное образовательное учреждение  Самарской области

«Борский государственный техникум»

 

 

«Согласовано» Руководитель МК ________Н.Е. Кочкарева «___»   августа   2019г. Протокол № _____ от «___»__________2019г.

Утверждаю Зам. директора по УВР ________Е.М. Ковалева «___»    августа   2019г.

 

 

 

 

 

Методические  рекомендации

 

работы с надстройкой Поиск решения  в Exсel 2010

 

 

 

 

 

по учебной дисциплине

ОП. 08 Информационные технологии в профессиональной деятельности

 

 

программы  подготовки  специалистов среднего звена

35.02.07 Механизация сельского хозяйства

(технический  профиль)

 

 

 

 

 

 

 

 

Автор-составитель:

Волгина Е.В.- преподаватель первой  квалификационной категории ГБПОУ СО «Борский государственный техникум»

 

Организация-разработчик: ГБПОУ СО «Борский государственный техникум»

 

 

 

 

с. Борское, 2019

Содержание

 

 

 

Введение                                                                                                                                              3

Работы с надстройкой «Поиск решений» в  Excel  2010                                                                4

Установка надстройки «Поиск решения»                                                                                         4

Решение задач оптимизации в Excel  2010                                                                                        7

Задача для самостоятельного решения.                                                                                             9

Список использованных источников                                                                                                  10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение

 

 

            Оптимизация – целенаправленная деятельность, заключающаяся в получении наилучших результатов при соответствующих условиях. Оптимизация в широком смысле слова находит применение в науке, технике, экономике и других областях человеческой деятельности.

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

            Основной целью рекомендаций является формирование  навыков использования программы MS Excel для решения сложных задач оптимизации

Методическая рекомендация  соответствует реализуемой основной профессиональной образовательной программы по подготовки ППСЗ и  разработаны для  студентов III курса Борского государственного техникума, обучающихся по специальности 35.02.07 Механизация сельского хозяйства и

 

.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

работы с надстройкой «Поиск решения»  в Exсel 2010

 

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

Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.

 С помощью надстроек электронных таблиц можно строить информационные модели, приближенно с заданной точностью решать уравнения методом подбора параметра, решать задачи оптимизационного моделирования методом поиска решений и т.д. Некоторые из надстроек не устанавливаются по умолчанию и требуют установки. «Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации»

 

установка надстройки «Поиск решения»,

 

В первую очередь необходимо установить надстройку «Поиск решения», поскольку самостоятельно она не появится.

Активируется  данная  функции, в настройках программы.

Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». (рис.1)

Рис.1

В открывшемся окне, переходим в раздел «Параметры». (рис.2)

Рис.2

 

В появившемся  окне параметров кликаем по пункту «Надстройки». После перехода в новое окно, в его  нижней части, напротив параметра «Управление» выбираем пункт  «Надстройки Excel», и активизируем  пункт  «Перейти» (рис.3)

Рис.3

 

В открывшемся  окне с надстройками ставим галочку напротив нужной нам надстройки – «Поиск решения» и нажимаем на кнопку «OK».(рис.4),

 

Рис.4

 

в результате чего на ленте Excel во вкладке «Данные». появляется кнопка для запуска функции Поиска решений (рис.5)

Рис.5

После нажатия кнопки Поиск решения в группе Анализ, откроется его диалоговое окно 

Рис.6

При частом использовании Поиска решения его удобнее запускать с Панели быстрого доступа, а не из вкладки Данные, лучше его закрепить на Панели.  Чтобы поместить кнопку на Панель, кликните на ней правой клавишей мыши и выберите пункт Добавить на панель быстрого доступа (рис.7)

 

Рис.7

 

 

 

РЕШЕНИЕ ЗАДАЧ ОПТИМИЗАЦИИ В EXCEL

 

Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).

В Excel для решения задач оптимизации используются следующие команды:

·        Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.(рис.8)

Рис.8

·        Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения.(рис.9)

 

Рис.9

·        Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев».

            Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Для решения простейших задач применяется команда Подбор параметра. Самых сложных – «Диспетчер сценариев».

Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения» с применением команды подбор параметра

Функция «Подбор параметра» в Excel применяется тогда, когда известен результат формулы, но начальный параметр для получения результата неизвестен. Чтобы не подбирать входные значения, используется встроенная команда.

Пример 1. Метод подбора начальной суммы инвестиций (вклада).

Известные параметры:

·        срок – 10 лет;

·        доходность – 10%;

·        коэффициент наращения – расчетная величина;

·        сумма выплат в конце срока – желаемая цифра (500 000 рублей).

Внесем входные данные в таблицу:

   

Вызываем окно команды «Подбор параметра». Заполняем поля:

После выполнения команды Excel выдает результат:

Чтобы через 10 лет получить 500 000 рублей при 10% годовых, требуется внести 192 772 рубля.

 

Задача для самостоятельного решения.

 

Задача 1. Рассчитайте  возможную прибавку к пенсии по старости за счет участия в государственной программе со финансирования.

Входные данные:

·           ежемесячные отчисления – 1000 руб.;

·           период уплаты дополнительных страховых взносов – расчетная величина (пенсионный возраст (в примере – для мужчины) минус возраст участника программы на момент вступления);

·           пенсионные накопления – расчетная величина (накопленная за период участником сумма, увеличенная государством в 2 раза);

·         ожидаемый период выплаты трудовой пенсии – 228 мес.;

·         желаемая прибавка к пенсии – 2000 руб

 

Вопрос: С какого возраста необходимо уплачивать по 1000 рублей в качестве дополнительных страховых взносов, чтобы получить прибавку к пенсии в 2000 рублей?

 

Задача 2. Завод производит электронные приборы трех видов (прибор А, прибор В, прибор С), используя при сборке микросхемы трех типов (тип1, тип 2, тип 3)  Расход микросхем задается следующей таблицей.

 

 

 Стоимость изготовленных приборов одинакова. Ежедневно на склад завода поступает 400 микросхем типа 1 и по 500 микросхем типа 2,3.

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Список использованных источников

 

 

1.                 Бурьков Д.В.,  Полуянович Н.К. Практикум по информатике: Учебное пособие. – М.:Издательско-торговая корпорация «Дашков и К^о»; Ростов н/Д. Наука-Спектр, 2008, – 192с. ISBN987-5-91131-607-5

 

2.                 Воробьева Ф.И. Информатика. MS Excel 2010 [Электронный ресурс]: учебное пособие/ Воробьева Ф.И., Воробьев Е.С.— Электрон. текстовые данные.— Казань: Казанский национальный исследовательский технологический университет, 2014.— 100 c.— Режим доступа: http://www.iprbookshop.ru/62175.html.— ЭБС «IPRbooks»

 

3.                 Башмакова Е.И. Информатика и информационные технологии. Умный Excel 2016: библиотека функций [Электронный ресурс]: учебное пособие/ Башмакова Е.И.— Электрон. текстовые данные.— Москва: Ай Пи Ар Медиа, 2020.— 109 c.— Режим доступа: http://www.iprbookshop.ru/94205.html.— ЭБС «IPRbooks»

 

 

Интернет-ресурсы

https://excel2.ru/articles/poisk-resheniya-ms-excel-znakomstvo

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Скачано с www.znanio.ru