Darbe.ru

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

Excel подбор слагаемых для нужной суммы

excel подбор слагаемых для нужной суммы

Хороший инструмент — надстройка Поиск решения в MS Excel!

Например, можно использовать в ситуации, когда вам нужно найти вариант из каких различных чисел могла сложиться определнная сумма (может вы ищете из каких счетов могла сложиться сумма оплаты). Допустим, нужно найти по приведенным числам сумму 10:

Для начал включим надстройку или проверим, что она включена (в Excel 2013): Файл / Параметры, раздел Надстройки, выбрать Управление: Надстройки Excel, нажать Перейти. Отметить флагом Поиск решения, нажать ОК

На ленте на вкладке Данные появился Поиск решения:

Теперь нужно придумать, как мы можем параметрами в разных строках «подбирать» сумму. Я сделал вариант, когда в столбце мы указываем множитель 0 или 1, в соседнем столбце считаем произведение, и потом значения складываем в итоговую сумму:

  • в ячейках столбца B указываем 0 или 1 (сейчас неважно, что конкретно)
  • в ячейке C4 формула =A4*B4
  • в ячейках C5:C14 — аналогично, с учетом номера строки
  • в ячейке C3 формула =СУММ(C4:C14)

Теперь запускаем Поиск решения. И заполняем:

  • Оптимизировать целевую функцию: $C$3
  • До: Значения: 10
  • Изменяя ячейки переменных: $B$4:$B$14
  • В соответствии с ограничениями: — добавляем (кнопка Добавить) три условия: 1) значения переменных должны быть целые, 2) значения должны быть >= 0, 3) 2) значения должны быть

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

В реальной жизни эта задача может выглядеть по-разному.

  • Например, мы выгрузили из интернет-банка все платежи, которые поступили на наш счет за последний месяц. Один из клиентов разбивает сумму своего платежа на несколько отдельных счетов и платит частями. Мы знаем общую сумму оплаты и количество счетов, но не знаем их сумм. Надо подобрать те суммы в истории платежей, которые дадут в общем заданное значение.
  • У нас есть несколько рулонов стали (линолеума, бумаги. ), из которых надо подобрать под заказ те, что дадут заданную длину.
  • Блэкджек или в народе «очко». Надо набрать карты суммарной стоимостью максимально близкой к 21 баллу, но не превысить этот порог.

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

Давайте рассмотрим несколько способов решения такой задачи в Excel.

Способ 1. Надстройка Поиск решения (Solver)

Эта надстройка входит в стандартный набор пакета Microsoft Office вместе с Excel и предназначена, в общем случае, для решения линейных и нелинейных задач оптимизации при наличии списка ограничений. Чтобы ее подключить, необходимо:

  • в Excel 2007 и новее зайти Файл — Параметры Excel — Надстройки — Перейти (File — Excel Options — Add-ins — Go)
  • в Excel 2003 и старше — открыть меню Сервис — Надстройки (Tools — Add-ins)

и установить соответствующий флажок. Тогда на вкладке или в меню Данные (Data) появится нужная нам команда.

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

  • Диапазон A1:A20 содержит наши числа, из которых мы будем выбирать нужные, чтобы «вписаться» в заданную сумму.
  • Диапазон В1:B20 будет своего рода набором переключателей, т.е. будет содержать нули или единички, показывая, отбираем мы данное число в выборку или нет.
  • В ячейке E2 стоит обычная автосумма всех единичек по столбцу B, подсчитывающая кол-во выбранных чисел.
  • В ячейке E3 с помощью функции СУММПРОИЗВ (SUMPRODUCT) считается сумма попарных произведений ячеек из столбцов А и B (то есть A1*B1+A2*B2+A3*B3+. ). Фактически, здесь подсчитывается сумма чисел из столбца А, отобранных единичками из столбца В.
  • В розовую ячейку E4 пользователь вводит желаемую сумму для подбора.
  • В ячейке E5 вычисляется абсолютное по модулю значение погрешности подбора с целью ее будущей минимизации.
  • Все желтых ячейках Е8:E17 хотелось бы получить список отобранных чисел, т.е. тех чисел из столбца А, напротив которых в столбце В есть единички. Для этого необходимо выделить сразу все (!) желтые ячейки и в них ввести вот такую формулу массива:

После ввода формулы ее необходимо ввести не как обычную формулу, а как формулу массива, т.е. нажать не Enter, а Ctrl+Shift+Enter. Похожая формула используется в примере о ВПР, выдающей сразу все найденные значения (а не только первое).

Теперь перейдем на вкладку (или в меню) Данные и запустим инструмент Поиск решения (Data — Solver):

В открывшемся окне необходимо:

  • Задать как целевую функцию (Target Cell) — ячейку вычисления погрешности подбора E5. Чуть ниже выбрать опцию — Минимум, т.к. мы хотим подобрать числа под заданную сумму с минимальной (а лучше даже нулевой) погрешностью.
  • В качестве изменяемых ячеек переменных (Changing cells) задать диапазон столбца переключателей B1:B20.
  • С помощью кнопки Добавить (Add) создать дополнительное условие на то, что ячейки диапазона B1:B20 должны быть бинарными (т.е. содержать только 0 или 1):


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

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

Теперь можно либо оставить найденное решение подбора (Сохранить найденное решение), либо откатиться к прежним значениям (Восстановить исходные значения).

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

Найденные комбинации можно сохранять виде сценариев (кнопка Сохранить сценарий), чтобы вернуться к нем позднее с помощью команды Данные — Анализ «что-если» — Диспетчер сценариев (Data — What-If Analysis — Scenario Manager):

И весьма удобно будет вывести все найденные решения, сохраненные в виде сценариев, в одной сравнительной таблице с помощью кнопки Отчет (Summary):

Способ 2. Макрос подбора

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

Для использования макроса нажмите сочетание Alt+F11, в открывшемся окне редактора Visual Basic вставьте новый модуль через меню Insert — Module и скопируйте туда этот код:

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

в Excel 2007 и новее зайти Файл — Параметры Excel — Надстройки — Перейти
в Excel 2003 и старше — открыть меню Сервис — Надстройки

и установить соответствующий флажок. Тогда на вкладке или в меню Данные появится нужная команда.
Способ 2. Макрос подбора

Проводим анализ данных в Excel 2010

Проводим анализ данных в Excel 2010

Всего есть два варианта анализа с использованием этого приложения.

Читайте так же:
Заблокирован гугл хром что делать

Статистический анализ

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

Чтобы провести анализ данных в Excel 2010 необходимо активировать «Диспетчер сценариев». Это идет по схеме: вкладка «Данные», активируем кнопочку «Работа с данными» — «Анализ «Что если»» — «Сценарии». После вы увидите такое окошко

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

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

Примечание самостоятельно заполняется, получая сведения об авторе и время создания сценария. Его можно дополнять по необходимости.

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

После заполнения данной формы также сохраните изменения. Вы автоматически вернетесь к «Диспетчеру».

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

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

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

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

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

Визуальный анализ

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

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

Кстати, размещение элементов в «Названии строк» играет существенную роль. В случае, если «Менеджеры» выше «Дат», то все данные будут разбиваться соответственно имен этих работников. Если выше «Даты» — то соответственно календарных дат.

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

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

Допустим, что месячная выруская каждого нашего менеджера не должна быть меньше 100 000. Просматривать все самостоятельно не нужно – это займет слишком много времени и сил. Поэтому просто проводим условное форматирование (вкладка «Вставка» — «Условное форматирование» — «Набор значков») по понравившемуся шаблону. Например, «Светофор».

Создаем правила форматирования (вводим показатели напротив значков), что позволит автоматически оценить работу сотрудника, как отличную, стабильную и неудовлетворительную. Показатели вводим напротив каждого кружочка в «Значение», в «Тип» устанавливаем «Числа», а не «Процент». Мы установили такой показатель, как 100 000 и 90 000, а третий выставиться автоматически, чтобы подключить оставшиеся значения. Сохраняем.

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

Однако это только самые простые из возможностей современного Excel 2010. В нем появились дополнительные элементы, называемые «Цветовыми шкалами» и «Гистограммами». Давайте попробуем использовать именно их.

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

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

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

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

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

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

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

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

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

Читайте так же:
Как в ворде сделать оглавление с подзаголовками

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

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

Сама инфокривая может быть одного из 3 типов:

— График, который мы и рассматривали на примере;

— Столбец – он отображает обрабатываемые данные маленькими столбиками. Чем больше данных, тем тоньше будут столбики, но они способны наглядно продемонстрировать минимальное и максимальное значение;

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

Вот как это выглядит в графическом примере

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

Решение задач оптимизации с помощью MS Excel

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

Прикрепленные файлы: 1 файл

Курсовая работа по информатике.doc

В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office).

Рисунок 2 — Наглядная таблица действий

Процедура поиска решения:

  1. создать таблицу с формулами, которые устанавливают связи между ячейками;
  2. выделить целевую ячейку, которая должна принять необходимое значение, и выберите команду:
  3. установить переключатели Equal To (Равной), задающие значение целевой ячейки, — Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа;
  4. указать в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата;
  5. создать ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение;

Рисунок 3 — Диалоговое окно надстройки Поиск решения

  1. щелкнуть на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям);
  2. щелкнув на кнопке Solver (Выполнить), запустить процесс поиска решений;

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

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

Максимальное время — служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию.

Рисунок 5 — Диалоговое окно Параметры поиска решений

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

Относительная погрешность — определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений.

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

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

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

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

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

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

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

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

Оценка линейная — выберите этот переключатель для работы с линейной моделью.

Оценка квадратичная — выберите этот переключатель для работы с нелинейной моделью.

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

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

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

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

1.3 Использование сценариев в Excel

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

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

Для создания нового сценария или работы с ранее созданными сценариями предназначено диалоговое окно Диспетчер сценариев, которое открывается с помощью команды Сервис — Сценарии.

Читайте так же:
Можно ли восстановить удаленные диалоги вконтакте

Диалоговое окно Диспетчер сценариев имеет следующие элементы управления.

  • список Сценарии содержит перечисление всех доступных сценариев, сохраненных в активном рабочем листе.
  • в поле Изменяемые ячейки отображаются адреса ячеек, содержащие изменяемые значения для выбранного сценария. Если в активном рабочем листе нет сохраненных сценариев, то это поле будет пустым.
  • в поле Примечание отображаются комментарии, которые записаны при создании сценария или которые Excel создаст самостоятельно. Если в' активном рабочем листе нет сохраненных сценариев, то это поле будет пустым.
  • щелчок на кнопке Вывести приводит к тому, что на рабочем листе в ячейки, указанные в поле Изменяемые ячейки, записываются значения, сохраненные в выбранном сценарии. Если в активном рабочем листе нет сохраненных сценариев, то эта кнопка будет недоступна.
  • щелчок на кнопке Закрыть закрывает диалоговое окно Диспетчер сценариев.
  • щелчок на кнопке Добавить открывает диалоговое окно Добавление сценария, предназначенное для создания нового сценария.
  • щелчок на кнопке Удалить приводит к удалению из рабочего листа выбранного сценария.
  • щелчок на кнопке Изменить открывает диалоговое окно Изменение сценария, предназначенное для редактирования сценария. Если в активном рабочем листе нет сохраненных сценариев, то эта кнопка будет недоступна.
  • щелчок на кнопке Объединить открывает диалоговое окно Объединение сценариев, предназначенное для объединения сценариев из разных рабочих листов.
  • щелчок на кнопке Отчет открывает диалоговое окно Отчет по сценарию, где создается итоговый отчет по выбранным сценариям в виде структурированного рабочего листа или в виде сводной таблицы. Если в активном рабочем листе нет сохраненных сценариев, то эта кнопка будет недоступна.

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

Создание нового сценария

Для создания нового сценария в открытом диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить. Откроется диалоговое окно Добавление сценария.

Диалоговое окно Добавление сценария содержит следующие элементы управления:

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

Если установить флажок скрыть, то после установки защиты рабочего листа название данного сценария не будет отображаться в списке Сценарии диалогового окна Диспетчер сценариев.

После того как в диалоговом окне Добавление сценария щелкнуть на кнопке ОК, откроется следующее диалоговое окно Значения ячеек сценария, показанное. Это окно имеет по одному полю ввода для каждого изменяемого значения, где можно увидеть и при необходимости изменить эти значения.

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

  1. выбрать команду Сервис — Сценарии.
  2. в открывшемся диалоговом окне Диспетчер сценариев щелкнуть на кнопке Добавить.
  3. в диалоговом окне Добавление сценария ввести название сценария в поле ввода Название сценария.
  4. в поле ввода Изменяемые ячейки ввести адреса изменяемых ячеек (вручную или путем выделения их непосредственно на рабочем листе).
  5. щелкнуть в диалоговом окне Добавление сценария на кнопке ОК.
  6. в открывшемся диалоговом окне Значения ячеек сценария ввести значения для изменяемых ячеек.

Щелкнуть в диалоговом окне Значения ячеек сценария на кнопке Добавить, чтобы создать сценарий и вернуться в диалоговое окно Добавление сценария, либо на кнопке ОК, чтобы создать сценарий и вернуться в диалоговое окно Диспетчер сценариев

Работа со сценариями

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

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

Чтобы удалить сценарий из текущего рабочего листа, в открытом диалоговом окне Диспетчер сценариев в списке Сценарии выберать сценарий, который необходимо удалить, и щелкните на кнопке Удалить [2].

1.4 Применение оптимизации

Задача оптимизации кормового рациона:

На ферме в качестве корма для животных используются два продукта — M и N.

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

Подсчитано, что в 1 кг каждого продукта содержится:

  • в продукте M — 150 ккалорий и 14 единиц жира;
  • в продукте N — 200 ккалорий и 4 единицы жира.

Задача: разработать максимально дешевый рацион откорма животных, отвечающий этим условиям, если стоимость 1 кг продукта М составляет 1,5 руб., а 1 кг продукта N — 2,3 руб.

Экономико-математическая постановка задачи:

x1 — количество продукта М в рационе; x2 — количество продукта N в рационе.

Диспетчер сценариев в excel

Урок № 3
СОСТАВЛЕНИЕ ШТАТНОГО РАСПИСАНИЯ ХОЗРАСЧЕТНОЙ БОЛЬНИЦЫ

  • научиться использовать электронные таблицы для автома­тизации расчетов;
  • закрепить приобретенные навыки по заполнению, форма­тированию и печати таблиц.

Постановка задачи:
Заведующий хозрасчетной больницей должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен при­нять на работу. Общин месячный фонд зарплаты составляет $10 000.
Построим модель решения этой задачи.
Поясним, что является исходными данными. Казалось бы, что ничего не дано, кроме общего фонда заработной платы. Од­нако заведующему больницей известно больше: он знает, что для нормальной работы больницы нужно:
5-7 санитарок;
8-10 медсестер;
10-12 врачей;
1 заведующий аптекой;
3 заведующих отделениями;
1 главный врач;
1 завхоз,
1 заведующий больницей.
На некоторых должностях число людей может меняться. Например, зная, что найти санитарок трудно, руководитель может принять решение сократить число санитарок, чтобы увеличить оклад каждой из них.
Итак, заведующий принимает для себя следующую модель задачи. За основу берется оклад санитарки, а все остальные вычисляются через него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад яв­ляется линейной функцией от оклада санитарки:
А • С + В,
где С — оклад санитарки;
А и В — коэффициенты, которые для каждой должности определяются решением совета трудового коллектива.
Допустим, совет решил, что медсестра должна получать в 1,5 раза больше санитарки,
врач — в 3 раза больше санитарки,
заведующий отделением — на 30 $ больше, чем врач заведующий аптекой — в 2 раза больше санитарки, завхоз — на 40 $ больше медсестры,
главный врач — в 4 раза больше санитарки, заведующий больницей — на 20 $ больше главного врача.
Задав количество человек на каждой должности, можно со­ставить уравнение:
N1 *(А1 *C+B1)+N2 * (А2 *C+B2)+. +N8 *(А8 * С+В8)=10000,
Где:

  • N1 — количество санитарок
  • N2 — количество медсестер
  • А1. А8 и В1. В8; и т.д. — коэффициенты для каждой должности.
Читайте так же:
Как восстановить закрепленные вкладки в хроме

В этом уравнении нам известны А1. А8 и В1. В8, а не из­вестны С и N1. N8.
Ясно, что решить такое уравнение известными методами не удается, да и единственно верного решения нет. Остается решать уравнение путем подбора.
Взяв первоначально какие-либо приемлемые значения не­известных, подсчитаем сумму. Если эта сумма равна фонду за­работной платы, то вам повезло. Если фонд заработной платы превышен, то можно снизить оклад санитарки, либо отказаться от услуг какого-либо работника и т. д. Проделать такую ра­боту вручную трудно. Но вам поможет электронная таблица.

ХОД РАБОТЫ:
ЗАДАНИЕ 1. Заполните таблицу (см. рис. пример заполнения таблицы).
1.1. Заполните шапку таблицы.
1.2.Отведите для каждой должности одну строку и занесите
название должностей в столбец С.
1.3.В столбцах А и В укажите коэффициенты А и В, соответ­ствующие каждой должности.
1.4.В ячейку НЗ занесите значение заработной платы санитар­ки 150 и установите для нее формат 0,00 — два знака после
запятой.

ЗАДАНИЕ 2. В столбце D вычислите заработную плату для каждой
должности.
В постановке задачи было объяснено, что заработная плата вычисляется по формуле А*С+В. В нашей таблице коэффициенты А и В находятся в столбцах А и В, а С — зарплата санитарки указана в ячейке НЗ.
Внимание! Столбец D должен заполняться формулами с ис­пользованием абсолютной ссылки на ячейку НЗ. Изменение со­держимого этой ячейки должно приводить к изменению содержимого всего столбца D. Аналогично в работе №2(1) в задании 3 изменение значения шага во вспомогательной таблице приводи­ло к пересчету в основной. В данной задаче удобно использовать еще один способ абсолютной адресации — именованную ячейку.

Именованная ячейка

В Excel можно присвоить имя любой ячейке или области. Что­бы присвоить имя ячейке, ее необходимо выделить и выполнить команду Вставка – Имя — Присвоить. На экране появится диалого­вое окно с полем ввода, где необходимо набрать имя и нажать кнопку <ОК>

картинка excel

Имя должно начинаться с буквы, не содержать пробелов, не совпадать с адресацией. Например, нельзя использовать имя F:2, но можно F_12 (рис. 3.3).
Второй способ именования состоит в использовании поля имени, которое располагается слева в строке формул.
Для этого необходимо:

  • выделить ячейку или область;
  • перейти в поле имени и щелкнуть левой кнопкой мыши;
  • ввести имя и нажать клавишу <Ок>. При выборе имени из списка имен Excel немедленно перей­дет к этой именованной ячейке или области.

картинка excel

Использовать именованную ячейку удобно в формулах, так как можно заменить адрес ячейки, ничего не говорящий о ее содержании на более выразительное имя. При ссылке в форму­лах на именованную ячейку она будет адресована абсолютно и при копировании формул не возникнут ошибки. Кроме того, рекомендуется именовать все "важные" ячейки, в которых пла­нируется часто изменять данные и которые содержат итоговые результаты.
2.1.Ячейке НЗ присвойте имя С.
2.2.В ячейку D3 занесите формулу =АЗ*С+В3.
Хотя эта формула равнозначна =АЗ* $Н$3+В, но имя С об­легчает понимание формулы.
2.3. Скопируйте формулу из ячейки D3 в D4: D10.
При копировании адрес ячейки с зарплатой санитарки остал­ся постоянным (абсолютным), а адреса A3 и ВЗ перенастраива­ются (они относительные).

ЗАДАНИЕ 3. В столбце Е укажите количество сотрудников на дол­жностях.
Данные в ячейках ЕЗ:Е5 могут изменяться в пределах штат­ного расписания, количество сотрудников на других должно­стях неизменно (см. постановку задачи).

ЗАДАНИЕ 4. В столбце F вычислите заработную плату всех со­трудников данной должности.
4.1.В ячейку F3 занесите формулу =D3*E3 (зарплата * количе­ство сотрудников).
4.2.Скопируйте формулу из ячейки F3 в F4 -.F10.
4.3.Установите для данных в столбцах D и F формат 0,00 —
два знака после запятой.

ЗАДАНИЕ 5. Определите суммарный месячный фонд заработной платы.
5.1. Просуммируйте столбец F, используя кнопку картинка excel
5.2.Переместите значение суммы в ячейку F12 и сделайте к
ней подпись : Суммарный месячный фонд заработной платы
5.3.Составьте штатное расписание.
Вносите изменения в зарплату санитарки или меняйте коли­чество сотрудников в ячейках ЕЗ:Е5 до тех пор, пока полученный суммарный месячный фонд заработной платы не будет равен за­данному $10000 (в ячейке F12 необходимо получить значение *= 10000).

ЗАДАНИЕ 6. Сохраните таблицу в личном каталоге под именем work3.xls

ЗАДАНИЕ 7. Составьте штатное расписание с использованием функции автоматизации расчетов — Подбор параметра.

Подбор параметра — удобное средство Excel для анализа "Что — если". При этом значения для ячеек-параметров изменяются так, чтобы число в целевой ячейке стало равно заданному.
7.1. Выберите команду Меню: Сервис – Подбор параметра.

картинка excel

7.2. Укажите в поле Установить в ячейке адрес целевой ячейки
7.3. Укажите в поле Значение — 10000.
7.4.Укажите в поле Изменяя значение ячейки — адрес ячейки с зарплатой
санитарки $Н$3 и нажмите на кнопку <ОК>.
Начнется процесс подбора параметра. На рисунке показам ре­зультат подбора параметра. Если нажать на кнопку <ОК>, значения ячеек в таблице будут изменены в соответствии с найденным решением.

картинка excel

ЗАДАНИЕ 8. Составьте несколько вариантов штатного расписа­ния с использованием функции Подбор параметра и оформите их в виде таблицы.

картинка excel

8.1.Измените количество сотрудников на должностях санитар­ки, медсестры и врача.
8.2.Подберите зарплату санитарки в новых условиях.
8.3.Составьте таблицу нескольких вариантов штатного распи­сания.

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

ЗАДАНИЕ 9. Сохраните таблицу в личном каталоге под тем же именем work3.xls

ЗАДАНИЕ 10. Проанализируйте полученные варианты, выберите и оформите один из них.
10.1. Оставьте столбцы С, D, Е, F.

Внимание! Удалить столбцы А, В, Н нельзя, так как в табли­це на них есть ссылки, но их можно скрыть. Столбцы А, В, Н необходимо выделить, а затем воспользо­ваться пунктом главного меню Формат.- Столбец — Скрыть или выз­вать контекстно-зависимое меню и выбрать пункт — Скрыть.

10.2.Дайте заголовок таблице "Штатное расписание хозрас­четной больницы и подзаголовок "Зав. больницей Петров И.С. "
10.3.Удалите таблицу вариантов штатного расписания.

    • Таблицу оформите, используя «автоформатирование»
    • выделите всю таблицу, включая заголовки;
    • выберите пункт меню Формат — Автоформат;
    • выберите удовлетворяющий вас формат.

    картинка excel

    ЗАДАНИЕ 11. Сохраните отредактированную таблицу в личном каталоге под именем hospital.xls.

    ЗАДАНИЕ 12. Подготовьте таблицу к печати, воспользовавшись предварительным просмотром печати:
    12.1.Выберите альбомное расположение.
    12.2.Уберите сетку.
    12.3.Укажите в верхнем колонтитуле фамилию, а в нижнем —
    дату и время.

    Финансовые функции. Средства анализа данных. Работа с финансовыми функциями. Анализ данных «Что – если?»

    Функция ПЛТ (PMT) – возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.

    • СТАВКА – Удельная ставка за период займа.
    • КПЕР– общее число периодов выплат.
    • ПС– текущая стоимость: общая сумма всех будущих платежей с настоящего момента.
    • БС – будущая стоимость или баланс наличности, которую нужно достичь после последней выплаты.Если аргумент БС опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение БС равно 0.
    • ТИП– логическое значение (0 или 1), обозначающее, должна ли производиться выплата в конце периода (0) или в начале периода (1).

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

    Пример 1. Предположим, что нужно воспользоваться 9-процентной 15-летней ссудой. Объем ссуды составляет 150 000 000 рублей. C помощью Мастера функций можно определить величины ежемесячных выплат. Предварительно следует привести все другие значения к месячной норме.

    Ввести таблицу (рис. 9.1 рис. 9.1 ), начиная с ячейки А1:

    Определение величины ежемесячных выплат

    В ячейки В 3 и В 4 ввести соответствующие формулы.

    Процентная ставка (СТАВКА) – годовая, поэтому для получения месячной ставки (Удельная ставка) соответствующее значение делится на 12 (0,09/12).

    Срок действия ссуды – 15 лет, поэтому с учетом 12 платежей год общее количество месячных выплат (КПЕР) составит 12х15.

    Для ячейки В6 пошаговыми действиями Мастера функций выполните настройку функции ПЛТ. Для вызова Мастера функций необходимо выбрать команду Вставить функцию (значок fx ) в меню Формулы.

    После этого в поле Значение диалогового окна Мастера функций вы увидите сумму ежемесячного взноса. А после нажатия на кнопку Готово результат отобразится в ячейке.

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

    Функция БС

    Функция БС(FV) предназначена для расчета будущей стоимости периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки.

    БС – будущее значение, возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.

    • СТАВКА – это процентная ставка за период.
    • КПЕР– это общее число периодов платежей.
    • ПЛТ– это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно ПЛТ состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов.
    • ПС – это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент ПС опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента ПЛТ.
    • ТИП– это число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если аргумент опущен, то он полагается равным 0.

    Для аргументов СТАВКА и КПЕР используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то СТАВКА должна быть 12%/12, а КПЕР должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то СТАВКА должна быть 12%, а КПЕР должно быть 4.

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

    Например, вы собираетесь вложить под 12% годовых (что составит в месяц 12%/12 или 1%). Вы собираетесь вкладывать по 1000 руб. в конце каждого следующего месяца в течении следующих 12 месяцев. Сколько денег будет на счету в конце12 месяцев?

    Результат 12682,50 руб.

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

    Функция ПС

    Функция ПС ( PV ) предназначена для расчета текущей стоимости как единой суммы вклада (займа), так и будущих фиксированных периодических платежей. Этот расчет является обратным по отношению к будущей стоимости (БС).

    ПС ( PV )– возвращает текущий объем вклада. Текущий объем -это общая сумма, которую составят будущие платежи. Например, когда вы берете взаймы деньги, заимствованная сумма и есть текущий объем для заимодавца.

    Например, определите необходимую сумму текущего вклада в банк, чтобы через пять лет он достиг 5000 руб. при 20% годовых и ежегодном начислении процентов в конце года. Синтаксис: ПС (20%, 5, 5000). Результат 2009,39.

    Функция КПЕР

    Для определения срока платежа и процентной ставки используются функции КПЕР ( NPER ) и СТАВКА ( RATE ).

    Функция КПЕР вычисляет общее число периодов выплат как для единой суммы вклада (займа), так и для периодических постоянных выплат на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, то для того, чтобы найти число лет выплат, общее число периодов надо разделить на число периодов в году.

    • СТАВКА– это процентная ставка за период.
    • ПЛТ– это выплата, производимая в каждый период; он не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам, никакие другие сборы или налоги не учитываются.
    • ПС– это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.
    • БС– это будущая стоимость, или баланс наличности, который должен быть достигнут после последней выплаты. Если аргумент БС опущен, то предполагается, что он равен 0 (будущая стоимость займа, например, равна 0).
    • ТИП– это число 0 или 1, обозначающее, когда должна производиться выплата.

    Например, рассчитаем срок погашения ссуды размером 5000 руб., выданной под 20% годовых при погашении ежемесячными платежами по 200 руб.

    Результат 32,6 месяца или 2,7 года.

    Функция СТАВКА

    Функция СТАВКА ( RATE ) определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение необходимо умножить на число расчетных периодов в году.

    СТАВКА вычисляется путем итерации и может давать нулевое значение или несколько значений. Если последовательные результаты функции СТАВКА не сходятся с точностью 0,0000001 после 20-ти итераций, то СТАВКА возвращает сообщение об ошибке #ЧИСЛО!.

    • Кпер– общее число периодов платежей по аннуитету.
    • Плт– регулярный платеж (один раз в период), величина которого остается постоянной в течение всего срока аннуитета. Обычно плт состоит из платежа основной суммы и платежа процентов, но не включает других сборов или налогов. Если аргумент опущен, должно быть указано значение аргумента БС.
    • Пс– приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.
    • Бс– требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (например, бс для займа равно 0).
    • Тип– число 0 или 1, обозначающее, когда должна производиться выплата.
    • Предположение – предполагаемая величина ставки. Если значение предположения опущено, то оно полагается равным 10 процентам.

    Если функция СТАВКА не сходится, попробуйте подставить различные значения для предположения. СТАВКА обычно сходится, если величина предположения находится между числами 0 и 1.

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

    Результат 0,008, или 0,8 в месяц или 9,6% годовых.

    Функции по расчету амортизации: AПЛ, АСЧ и ДДОБ

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

    Функция АПЛ (SLN) возвращает величину амортизации имущества за один период времени, используя метод равномерной амортизации.

    • нач_стоимость – начальная стоимость имущества;
    • ост_стоимость –остаточная стоимость в конце периода амортизации;
    • время_эксплуатации – количество периодов, за которые собственность амортизируется (иногда называется временем полной амортизации).

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

    которая возвращает значение 1000 р.

    Функция АСЧ(SYD) возвращает годовую амортизацию имущества для указанного периода.

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