Mini-ats102.ru

ООО “Мультилайн”
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Средство Excel «Поиск решения»

Средство Excel «Поиск решения»

Решение задач прикладной информатики в менеджменте.


Практическое занятие 5.


Средство Excel «Поиск решения»

Цель работы:
изучение постановки задачи оптимизации и средства «Поиск решение»

Задачи оптимизации параметров объекта исследования

Оптимизационные модели служат для поиска наилучших, в определенном смысле, вариантов. В этом случае среди параметров модели выделяют один или несколько, доступных нашему влиянию – независимые переменные или управляемые параметры X . Среди выходных характеристик Y выделяют такую, которая позволяет оценить качество объекта – критерий оптимальности Q k .

С учетом введенных обозначений задача оптимизации формализуется следующим образом:

Q * k = extr Q k
X
Y j ( х ) j max

В зависимости от особенностей реального объекта, характера зависимости критерия оптимальности от независимых переменных и целей исследования различают задачи:

условной (при наличии дополнительных ограничений) и безусловной (без дополнительных условий) оптимизации;

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

линейной (при линейной зависимости критерия качества от параметров) и нелинейной оптимизации;

локальной (существует единственный экстремум) и глобальной (существуют несколько экстремумов) оптимизации.

Когда экономическую или техническую задачу удается привести к виду стандартной задачи оптимизации (1), ее решение можно получить одним из известных математических методов.

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

Средство Excel «поиск решения»

Средство Excel Поиск решения позволяет получить искомое значение в определенной ячейке, которую называют целевой , путем изменения значений нескольких влияющих ячеек. Кроме того, при поиске решения можно указать дополнительные условия – ввести ограничения на изменение параметров влияющих ячеек. Допускается установка до 200 изменяемых ячеек.

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

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

Рисунок 1 Список подключенных настроек

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

Читайте так же:
Метеостанция домашняя с беспроводным датчиком как выбрать

Для выполнения операции Поиск решения нужно выполнить команду Сервис – Поиск решения и в диалоговом окне Поиск решения задать параметры решения:

адрес целевой ячейки, в которой будет подбираться значение;

критерий оптимальности (максимальное или минимальное значение) или значение, которое следует найти;

адреса изменяемых ячеек; при этом адреса отдельных ячеек или диапазонов разделяются запятыми; кнопка «Предположить» служит для автоматического выделения ячеек, влияющих на целевую;

ограничения, которые должны учитываться при поиске решения; для ввода нескольких ограничений используется кнопка «Добавить».

Рисунок 2 Диалог «Поиск решения»

Кнопка «Параметры» позволяет изменить параметры поиска: способ поиска решения, время вычислений, точность определения результатов.

Рисунок 3 Диалог установки параметров поиска решения

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

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

сохранить найденной решение в исходной таблице;

восстановить исходные значения;

сохранить результаты в виде сценария;

сформировать отчет по результатам выполнения операции.

Рисунок 4 Диалог «Результаты поиска решения»

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

В программе Excel-2007 средство «Поиск решения» вызывается пиктограммой из группы Анализ , расположенной на вкладке Данные.

Определение критического объема реализации с помощью поиска решений

Рассмотрим использование Поиска решений для решения уравнения.

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

Скопируйте заголовок и первую строку таблицы анализа затрат – доходов:

Объем
реализации
Q к

В столбце «Баланс» введите формулу: Прибыль от реализации — Затраты

С помощью средства «Поиск решения» определите величину Объема реализации, обеспечивающую нулевой баланс.

Указания.
1) Целевая ячейка в нашем случае – ячейка, в которой вычислен баланс; требуется установить в ней нулевое значение путем изменения ячейки с объемом реализации.

2) Математическая модель рассматриваемой задачи линейна.

3) Задача без ограничений.

Замечание. Иногда требуется проверить, какие ячейки влияют на вычисление значение в другой ячейке. Чтобы наглядно увидеть взаимное влияние ячеек, можно использовать команду меню Сервис – Зависимости формул – Влияющие ячейки (Зависимые ячейки).

Читайте так же:
Можно ли в кинотеатр со своими напитками

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

Варианты решения

Транспортную задачу можно решить «вручную». Существует несколько подходов к её решению на бумаге. Среди них:

  • Метод опорного плана;
  • Метод минимального элемента;
  • Метод Фогеля.

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

Однозначного алгоритма, работающего безотказно в любых условиях, к сожалению, пока не придумали.

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

Как работает Решатель?

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

excel 2007 формулы

  1. Найти неизвестные→несколько «x».
  2. При условии, что→формула или функция.
  3. При ограничениях→здесь обычно указывается неравенство, либо минимальные/максимальные значения.

Также нужно указать на ячейки, с которыми следует проводить вычисления. Есть возможность решать несколько разных задач, если задать программе соответствующие параметры.

Как подключить надстройку?

Перед использованием необходимо предварительно включить надстройку, поставив галочку перед ее названием в списке доступных надстроек диалогового окна «Надстройки».

kak-podklyuchit-nadstrojku-poisk-resheniya

Вызов этого окна несколько различается в зависимости от версии приложения. Подробно об этом написано в отдельной статье «Как установить надстройку для Excel 2003/2007/2010/2013/2016?» со скриншотами для каждой версии приложения Excel, поэтому не буду повторяться. Да, добавлю лишь несколько слов о втором способе установки этой надстройки. Можно отыскать на просторах Интернета файл с названием Solver.xla (это и есть надстройка «Поиск решения») и произвести установку в соответствии с описанием по ссылке выше.

Готовим таблицу

Мы нашли 5 китайских товаров, которыми нам было бы интересно заняться:

Как заставить Эксель думать за тебя

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

Читайте так же:
Можно ли ездить без колодок

Как заставить Эксель думать за тебя

Теперь добавим колонки с временем на упаковку: каждого товара в отдельности и всего по каждой позиции. Указывать будем в минутах. Чтобы получить общее время на упаковку по каждой позиции, используем формулу:

Время на упаковку всей позиции = время упаковки одного товара × количество товаров

На языке Экселя это выглядит для первой строки так:

Протянем мышкой эту ячейку вниз на 5 клеток, чтобы Эксель сам пересчитал формулы для соответствующих товаров. У нас пока нет ни одного заказанного товара, поэтому везде по нулям:

Как заставить Эксель думать за тебя

Добавим колонки с закупочной ценой и ценой, по которой мы будем продавать готовые подарки. Сразу же добавим последнюю колонку «Доход» — в ней мы посчитаем наш доход с каждой позиции:

Доход = (цена продажи − закупочная цена) × количество товаров

На языке формул это будет выглядеть так:

Тоже протягиваем ячейку с формулой вниз, чтобы всё пересчиталось на каждой строчке:

Как заставить Эксель думать за тебя

Финальный штрих: добавим внизу строку «Итого», чтобы в ней посчитать наш бюджет и будущий доход. Смысл в том, что мы не можем превысить наш закупочный бюджет, потому что в долг нам никто ничего не пришлёт.

Чтобы посчитать сумму на закупки и сумму дохода, используем функцию СУММ():

=СУММ(I2:I6) ← считаем общую сумму дохода

А для подсчёта бюджета мы умножаем количество каждого заказанного товара на его стоимость и складываем так всё по каждой позиции:

Как заставить Эксель думать за тебя

Напоследок посчитаем общие затраты времени на упаковку: без этого мы не сможем понять, успеет наш упаковщик всё сделать или нет:

Как заставить Эксель думать за тебя

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

Искать и выделять результаты поиска по коду VBA

В Excel есть код VBA, который может помочь вам найти значение и затем выделить результаты поиска.

1. Включите лист, который вы хотите найти, и выделите результаты, нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте приведенный ниже код в новый модуль.

Читайте так же:
Импорт dbf в excel

VBA: выделение результатов поиска

doc search выделить результат поиска 1

3. Нажмите F5 нажмите клавишу, затем появится окно поиска, в котором вы можете ввести указанное вами значение.
doc search выделить результат поиска 2

4. Нажмите OK, совпадающие результаты были выделены цветом фона. Кроме того, появляется диалоговое окно с вопросом, отменить ли выделение. Нажмите OK чтобы отменить выделение и закрыть диалоговое окно, нажмите Отмена чтобы сохранить выделение и закрыть диалог.
doc search выделить результат поиска 3

Внимание:

1. Если подходящего значения не найдено, появится диалоговое окно с напоминанием.
doc search выделить результат поиска 4

2. Этот VBA работает для всего активного листа и нечувствителен к регистру.

Вы хотите повысить зарплату и много времени проводить с семьей?

Вкладка Office повышает эффективность работы Microsoft Office на 50% прямо сейчас

Невероятно, но работать с двумя или более документами проще и быстрее, чем над одним.

По сравнению с известными браузерами инструмент с вкладками в Office Tab более мощный и эффективный.

Сократите для вас сотни щелчков мыши и набора текста с клавиатуры каждый день, попрощайтесь с рукой мыши.

Если вы обычно работаете с несколькими документами, вкладка Office поможет вам сэкономить время.

30-дневная бесплатная пробная версия , кредитная карта не требуется.

Поиск и выделение результатов поиска с помощью условного форматирования

В Excel функция условного форматирования также может автоматически выделять результаты поиска.

Предположим, что данные и окно поиска отображаются, как показано на скриншоте ниже, теперь выполните следующие действия:
doc search выделить результат поиска 5

1. Выберите диапазон, в котором нужно выполнить поиск, и нажмите Главная > Условное форматирование > Новое правило.
doc search выделить результат поиска 6

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел, затем перейдите в текстовое поле под Формат значений, где эта формула истинна, напишите = И ($ E $ 2 <> «», $ E $ 2 = A4) .

E2 — это ячейка, в которую вы поместите поисковое значение, а A4 — это первая ячейка в диапазоне, в котором вы выполняете поиск.
doc search выделить результат поиска 7

3. Нажмите Формат кнопку, перейдите к Формат ячеек диалога под Заполнять на вкладке выберите нужный цвет. Нажмите OK > OK чтобы закрыть диалоги.
doc search выделить результат поиска 8

С этого момента, когда вы вводите ключевое слово в ячейку E2, результаты поиска будут автоматически выделяться в указанном диапазоне.

Файл примера

Другие операции (статьи), связанные с форматированием Conditioanl

Подсчет / суммирование ячеек по цветам с условным форматированием в Excel
Теперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.

Читайте так же:
Можно ли восстановить данные после сброса настроек

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

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

Условное форматирование строк или ячеек, если два столбца равны в Excel
В этой статье я представляю метод условного форматирования строк или ячеек, если два столбца равны в Excel.

Применить условное форматирование для каждой строки в Excel
Иногда может потребоваться применить условное форматирование для каждой строки. Кроме многократной установки одних и тех же правил для каждой строки, есть несколько уловок для решения этой задачи.

Функция ИНДЕКС для массивов

Допустим, у нас есть таблица с наименованиями товаров, их ценой, количеством и итоговой суммой.

Таблица Excel

Наша задача – в заранее выбранной ячейке отобразить наименование 5-ой позиции в списке.

    Встаем в ячейку, куда планируем вывести требуемые данные. Затем жмем кнопку “Вставить функцию” (fx).

Вставка функции в ячейку таблицы Эксель

Выбор функции ИНДЕКС в Эксель

Выбор списка аргументов для функции ИНДЕКС в Эксель

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

Заполнение аргументов функции ИНДЕКС в Excel

Заполнение аргументов функции ИНДЕКС в Эксель

Результат по функции ИНДЕКС в ячейке таблицы Excel

Как мы ранее отмечали, один из аргументов функции (“Номер_столбца” или “Номер_строки”) может оставаться незаполненным, если выделенный массив будет одномерным, т.е. занимать либо одну строку, либо один столбец. На практике это выглядит следующим образом.

    В окне аргументов функции в поле “Массив” выделяем только ячейки первого столбца. Указываем номер строки – 5, а номер столбца, соответственно, остается незаполненным, так как в выделенном нами массиве он только один.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector