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

Поиск решения EXCEL. Знакомство

Поиск решения EXCEL. Знакомство

На этом шаге мы рассмотрим процедуру поиска решения.

В Excel предусмотрен мощный инструмент — Поиск решения , который позволяет расширить процедуру подбора параметра следующим образом:

  • Указывать несколько изменяемых ячеек.
  • Указывать ограничения на значения изменяемых ячеек.
  • Находить решение, при котором значение в определенной ячейке рабочего листа достигает максимума или минимума.
  • Получить несколько решений задачи.

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

  • Значение в целевой ячейке зависит от других значений в ячейках и результатов расчета по формулам. Нужно определить все исходные параметры, при которых значение в целевой ячейке будет максимальным или заранее определенным.
  • Целевая ячейка зависит от группы ячеек, которые называются изменяемыми ячейками . Их значения надо подобрать так, чтобы получить желаемый результат в целевой ячейке.
  • Решение (значение изменяемых ячеек) должно находиться в определенных пределах или удовлетворять определенным ограничениям .

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

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

Рис. 1. Рабочий лист, иллюстрирующий пример расчета количества изделий, приносящих максимальную прибыль

У компании есть несколько ограничений, которые она должна учитывать:

  • Общий объем производства — всего 300 единиц изделий в день.
  • Компании нужно произвести 50 единиц изделия А для выполнения существующего заказа.
  • Компании нужно произвести 40 единиц изделия В для выполнения планового заказа.
  • Поскольку сбыт изделий С относительно небольшой, то должно быть изготовлено не больше 40 единиц этого изделия.

Процедура поиска решения выполняется следующим образом.

  1. Введите в рабочий лист исходные данные и формулы (рис. 1).
  2. Вызовите диалоговое окно Поиск решения .
  3. Укажите целевую ячейку.
  4. Укажите изменяемые ячейки.
  5. Задайте ограничения.
  6. При необходимости измените опции процедуры поиска решения.
  7. Позвольте процедуре поиска решения выполнить поставленную задачу.

Чтобы запуcтить процедуру поиска решения , выберите команду Сервис | Поиск решения .

Примечание . Если в пункте меню Сервис отсутствует опция Поиск решения , нужно выполнить команду Сервис | Надстройки и в открывшемся диалоговом окне Надстройки установить галочку рядом с опцией Поиск решения .

В результате выполнения указанных действий появится диалоговое окно Поиск решения (рис. 2).

Рис. 2. Диалоговое окно Поиск решения

В этом примере в целевой ячейке D6 вычисляется общая прибыль по трем видам изделий. В поле Установить целевую ячейку укажите адрес ячейки D6 или щелкните в рабочем листе на этой ячейке. Поскольку наша цель — максимизировать значение в этой ячейке, установите переключатель Равной максимальному значению . Затем определите изменяемые ячейки, которые в данном случае находятся в диапазоне В3:В5 .

Дальше введите ограничения задачи. Ограничения добавляются по одному за один раз и отображаются в окне Ограничения . Для добавления ограничений щелкните на кнопке Добавить . Появится диалоговое окно Добавление ограничения (рис. 3).

Рис. 3. Диалоговое окно Добавление ограничения

В этом диалоговом окне нужно ввести ссылку на ячейку, оператор и значение. Первое ограничение — общий объем продукции должен быть равен 300 единицам изделий. Введите адрес ячейки B6 , выберите оператор равно (=) из раскрывающегося списка операторов и введите в качестве значения число 300 . Для добавления очередного ограничения щелкните на кнопке Добавить . В таблице 1 приведены все ограничения для этой задачи.

После ввода последнего ограничения щелкните но кнопке OK . Вы вернетесь к диалоговому окну Поиск решения , которое в нашем примере должно содержать четыре ограничения. Теперь для выполнения процедуры поиска решения введены все исходные данные. Чтобы начать процесс решения задачи, щелкните на кнопке Выполнить . В строке состояния будет отображаться ход решения задачи. Через некоторое время на экране появится информация о том, что решение найдено. Диалоговое окно Результаты поиска решения показано на рис. 4.

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

После решения задачи Вы можете выбрать одну из следующих возможностей:

  • Заменить исходные данные значения в изменяемых ячейках на те, которые были найдены в результате решения задачи.
  • Восстановить исходные значения в изменяемых значениях.
  • Создать несколько отчетов о процедуре поиска решения (для выбора нескольких отчетов из списка нажмите клавишу Shift и щелкните на нужном типе отчета).
  • Щелкнуть на кнопке Сохранить сценарий для сохранения решения в виде сценария, который может быть использован в средстве Диспетчер сценариев .

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

Рис. 5. Отчет по результатам поиска решения

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

Занятие 1. Знакомство со средой ms Excel

Документ MS Excel называется рабочей книгой. Каждая книга имеет имя и хранится на диске в отдельном файле. По умолчанию новой рабочей книге присваивается имя «Книга1», «Книга2» и т.д., которое при сохранении целесообразно изменить. При записи на диск к имени книги добавляется расширение .xls. В Excel допускается работа с несколькими рабочими книгами одновременно, при этом каждая открывается в собственном окне. Активной (текущей) может быть только одна из них. Для активизации рабочей книги можно, например, воспользоваться кнопками на Панели задач.

Для того, чтобы все окна рабочей книги были видны на экране:

Выберите команду Окно|Расположить. На экране появится диалоговое окно Расположение окон.

В группе Расположить отметьте нужный переключатель: рядом, сверху, вниз, слева направо, каскадом.

Рабочая книга представляет собой набор рабочих листов, каждый их которых имеет табличную структуру и может содержать одну или несколько таблиц, диаграммы, а также внедренные или связанные объекты других приложений (например, MS Word). Каждый лист имеет имя, которое отображается на ярлычке листа. По умолчанию листы имеют имена «Лист1», «Лист2» и т.д. Эти имена можно изменить командой Формат|Лист|Переименовать или, дважды щелкнув по ярлычку, ввести новое имя.

Рабочий лист MS Excel разделен сеткой на строки и столбцы (См. рис.1), на пересечении которых находятся ячейки. Обычно столбцы обозначаются прописными латинскими буквами (A, B, C, …, Z), а далее двухбуквенными сочетаниями (AA, AB, …, BA, BB, …). Всего рабочий лист может содержать до 256 столбцов (с именами от A до IY). Строки последовательно нумеруются от 1 до 65536.

Ячейка является минимальным элементом для хранения данных. Имя ячейки состоит из имени столбца и номера строки, на пересечении которых она находится. Таким образом, самая первая ячейка имеет адрес A1. Далее по строке B1, C1, D1 и т.д. В следующей (второй) строке – A2, B2, C2, D2 и т.д.

Если надо задать адрес ячейки, которая находится на другом рабочем листе или даже в другой рабочей книге, перед именем ячейки задают имя рабочего листа, а во втором случае и имя рабочей книги. Например, Лист1!А1 или [Книга1.xls].Лист1!А1.

Кроме понятия ячейки, используется понятие диапазона. Чаще всего, в качестве диапазона понимается прямоугольная область, состоящая из нескольких (или одного) столбцов и нескольких (или одной) строк. В качестве адреса диапазона указываются адреса левой верхней и правой нижней ячеек диапазона, разделенные знаком : (двоеточие). Например, диапазон A1:C8 содержит 24 ячейки (3 ячейки в строках и 8 ячеек в столбцах).

2. Главное окно ms Excel

Главное окно, появляющееся непосредственно после запуска программы (Рис.1), имеет следующие интерфейсные элементы:

В. Кузьмин — Microsoft Office Excel 2003. Учебный курс

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

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

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

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

Краткое содержание книги

Расскажем о содержании каждого урока.

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

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

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

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

В Excel аналогом простой базы данных является список, который представляет собой группу строк, содержащих связанные данные. Из урока 6, «Работа со списками и базами данных», вы узнаете, как создавать списки вручную и с помощью формы данных, сортировать строки и столбцы списка, используя сложные критерии, выполнять отбор данных с помощью функции автоматического фильтра, как задавать расширенные фильтры прямо на рабочем листе. Кроме того, вы научитесь формировать запросы к внешним базам данных с помощью программы Microsoft Query, редактировать списки базы данных, форматировать таблицу результатов, передавать данные запросов в программу Excel.

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

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

В уроке 9, «Графические объекты», речь пойдет о красочном оформлении рабочих листов за счет использования картинок из коллекции Clip Art, автофигур, а также текста, оформленного с применением специальных эффектов приложения WordArt. Освоив материал урока 10, «Элементы управления на рабочем листе», вы узнаете, какие типы элементов управления используются в Excel, чем элементы управления ActiveX отличаются от аналогичных элементов панели инструментов Forms (Формы), какими возможностями они обладают, освоите технику работы с ними. Excel предоставляет в наше распоряжение множество инструментов формирования вида печатаемого документа. Как подготовить документ к печати, как выбрать, установить и настроить принтер, как задать параметры страницы, вы узнаете из урока 11, «Подготовка документа к печати».

Тема урока 12, «Работа с макросами, основы VBA», говорит сама за себя. В данном уроке речь пойдет о создании макросов и о программировании на VBA. К сожалению, многие пользователи не умеют писать программ и не считают нужным этому учиться. А зря, затратив не так уж много времени и усилий, ряд операций можно автоматизировать, то есть избавиться от необходимости выполнять часто повторяющиеся, как правило, довольно рутинные действия, что позволит сосредоточиться на решении конкретных задач.

Приложения пакета Microsoft Office 2003 поддерживают несколько способов обмена данными. Внимательно изучив материалы урока 13, «Обмен данными», вы сделаете первый шаг к коллективной работе с документами. Обычно и эту возможность Excel в полной мере использует лишь незначительная часть пользователей. В уроке рассказывается об обмене данными между приложениями с помощью буфера обмена, о передаче данных в виде фрагментов документа, о вставке объектов с сохранением связей с приложениями, в которых они были созданы.

Интернет доступен любому современному специалисту. И тем не менее предлагаем подробно ознакомиться с материалами урока 14, «Интернет-технологии в Excel», с тем чтобы систематизировать свои познания об использовании сети Интернет при работе с программой Excel. Теперь вы сможете отправлять рабочие листы в качестве сообщений электронной почты, применять возможность маршрутизации сообщений, открывать рабочую книгу в сети Интернет, работать с FТР-узлами, публиковать данные в Интернете, создавать и использовать гиперссылки.

В уроке 15, «Коллективная работа», мы расскажем об организации совместной работы с Excel-документами при использовании средств, предоставляемых веб-узлами рабочей группы Microsoft SharePoint Team Services. Для того чтобы включиться в работу группы, достаточно иметь доступ к браузеру. Но Excel позволяет работать на веб-узле группы более эффективно: сохранять и редактировать документы в клиентской программе, пересылать данные из нее на веб-узел.

Имея некоторый опыт работы с программой, вы сможете приступить к настройке меню и панелей инструментов. Сделать Excel удобным для работы можно, даже обладая минимальными знаниями и опытом. Элементарные настройки выполняются в диалоговых окнах Options (Параметры) и Customize (Настройка), описанных в уроке 16, «Индивидуальная настройка программы».

Где находится «Поиск решения» в Excel и как им пользоваться

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

Обзор и возможности функции

Надстройка «Поиск решений» — специфическая возможность Excel 2007, 2010, 2013, 2016, которая предназначена для работы с формулой при наличии определённых условий. Описать её логику можно следуя принципу «что если?». То есть, просчитать изменение конечной ячейки при условии изменения других. Хотя и звучит это сложно, но описать данный принцип удобнее на конкретном примере: как будет изменяться остаток средств в конце месяца, если изменить разные статьи расходов.

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

Включение возможности

Функция поиска решений является надстройкой Excel. Для версий Excel ниже 2010 её потребуется запускать следующим образом:

  1. Открыть «Параметры» программы.параметры программы
  2. Перейти в раздел «Надстройки».Раздел надстроек
  3. Найти «поиск решений» и клацнуть по нему дважды. В случае если пакет не установлен Office предложит загрузить его. С таким предложением потребуется согласиться.

раздел данных

Теперь возможность можно будет найти в разделе «Данные». Отмечена она названием «Поиск решений».

Подготовка таблицы

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

  1. Представим, что в таблице имеются пункты, которые имеют какое-то значение.Заполненная таблица
  2. Осложним задачу введением двух виртуальных групп, которые могут соответствовать пунктам (это могут быть статьи расходов).Ввод виртуальных групп

В случае соответствия группы пункту она получает «вес» равный «1». В противном случае – «0». Это потребуется для дальнейших операций.

  1. Теперь добавим 2 группы формул, которые выражают взаимодействие между группами, «весомостью» (значение) и пунктами. Пускай это будет сложение и умножение, для простоты. Таким образом мы получим сумму важности пунктов (итоговое значение для каждой из групп) и что-то вроде важности затрат для каждого из пунктов.добавление формул
  2. Теперь постараемся прийти к конечным решениям по затратам и итогам. Сделаем это при помощи операций сложения в соответствующих столбцах.Конечное решение
  3. Последним действием будет сведений разницы между затратами групп.Результат вычислений

Теперь таблицу можно считать сведённой и подготовленной. Требуется только завершить работу с функцией и настроить формулы для её эффективного срабатывания. Предположим, что нас интересует результат, при котором разница между затратами групп будет минимизирована или сравняется с нулём (ячейка «Разница»).

Запускаем функцию подбора решений и начинаем задавать ей необходимые переменные и значения:

Метод решения

  1. Указывается, в какой ячейке находится формула, результат которой интересует пользователя.
  2. Указывается результат, к которому должно привести изменений значений. Если указать в качестве значения 0, то, вероятнее всего, таблица будет заполнена нулями. Чтобы избежать подобного требуется выставить хотя-бы «1».
  3. Диапазон ячеек, подлежащих изменению.
  4. Ограничения, которые можно логически вписать в переменные. В указанном случае это должны быть целые числа 0 или 1.
  5. Метод решения. Лучше оставить без изменений, если ранее не было произведено знакомство с алгоритмами работы.
  6. Запуск поиска решений.

параметры поиска

Получаем следующее заполнение:

Получаем решения

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

Конкретные примеры использования

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

Изготовление йогурта

Пример таблицы

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

расход сырья

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

В результате вычислений (с учётом дробного остатка, поскольку условие работы только с целыми числами добавлено не было), получилось, что эффективнее всего производить 1 и 3 йогурты, а второй полностью игнорировать.

Затраты на рекламу

Оптимизация расходов

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

Целевые ячейки

Итак, прибыль является целевой ячейкой (выделена изумрудным цветом). Зелёным выделены расходы на рекламу, а красным максимальные затраты. При поиске решения ограничиваем подстановку переменных в значениях рекламы максимумом, а в качестве цели ставим максимизацию прибыли.

Результат вычислений

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

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

Оптимизация игрового процесса

Пример таблицы

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

Параметры поиска

Итоговое доступное время по условиям подбора решения ограничено 4 единицами (время устанавливаем условно, не важно будут это часы, дни или месяцы). Графа «выгода» представляет собой формулу, говорящую, что будет если выделить «х» времени на сбор определённого комплекта. Задачей Excel является оптимизация максимальной (суммарной) выгоды.

В условиях имеем: требуется получить максимальную выгоду при лимите времени. Следовательно, программа определяет на каком комплекте сфокусировать внимание. Результат предсказуем: самый дорогой комплект достоин 100% временных затрат.

Установка ограничений

Ввод параметров

При работе с функцией, как упоминалось выше, можно установить ограничения. Они выставляются в поле «В соответствии с ограничениями». Их можно устанавливать, убирать или редактировать. Главное понимать какая цель ставится перед программой и какими способами Excel может её добиться.

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

голоса
Рейтинг статьи
Читайте так же:
Как восстановить утерянную или удаленную историю веб-браузера
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector