Tw-city.info

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

Сервис подбор параметра в excel 2020

Подбор параметра в EXCEL

Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 2*a+3*b=x, заданы параметры а=1, b=2, требуется найти x (2*1+3*2=8). Инструмент Подбор параметра позволяет решить обратную задачу: подобрать такое значение параметра, при котором уравнение возвращает желаемый целевой результат X. Например, при a=3, требуется найти такое значение параметра b, при котором X равен 21 (ответ b=5). Подбирать параметр вручную — скучное занятие, поэтому в MS EXCEL имеется инструмент Подбор параметра .

В MS EXCEL 2007-2010 Подбор параметра находится на вкладке Данные, группа Работа с данным .

Простейший пример

Найдем значение параметра b в уравнении 2*а+3*b=x , при котором x=21 , параметр а= 3 .

Подготовим исходные данные.

Значения параметров а и b введены в ячейках B8 и B9 . В ячейке B10 введена формула =2*B8+3*B9 (т.е. уравнение 2*а+3*b=x ). Целевое значение x в ячейке B11 введено для информации.

Выделите ячейку с формулой B10 и вызовите Подбор параметра (на вкладке Данные в группе Работа с данными выберите команду Анализ «что-если?» , а затем выберите в списке пункт Подбор параметра …) .

В качестве целевого значения для ячейки B10 укажите 21, изменять будем ячейку B9 (параметр b ).

Инструмент Подбор параметра подобрал значение параметра b равное 5.

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

Примечание : Уравнение 2*а+3*b=x является линейным, т.е. при заданных a и х существует только одно значение b , которое ему удовлетворяет. Поэтому инструмент Подбор параметра работает (именно для решения таких линейных уравнений он и создан). Если пытаться, например, решать с помощью Подбора параметра квадратное уравнение (имеет 2 решения), то инструмент решение найдет, но только одно. Причем, он найдет, то которое ближе к начальному значению (т.е. задавая разные начальные значения, можно найти оба корня уравнения). Решим квадратное уравнение x^2+2*x-3=0 (уравнение имеет 2 решения: x1=1 и x2=-3). Если в изменяемой ячейке введем -5 (начальное значение), то Подбор параметра найдет корень = -3 (т.к. -5 ближе к -3, чем к 1). Если в изменяемой ячейке введем 0 (или оставим ее пустой), то Подбор параметра найдет корень = 1 (т.к. 0 ближе к 1, чем к -3). Подробности в файле примера на листе Простейший .

Еще один путь нахождения неизвестного параметра b в уравнении 2*a+3*b=X — аналитический. Решение b=(X-2*a)/3) очевидно. Понятно, что не всегда удобно искать решение уравнения аналитическим способом, поэтому часто используют метод последовательных итераций, когда неизвестный параметр подбирают, задавая ему конкретные значения так, чтобы полученное значение х стало равно целевому X (или примерно равно с заданной точностью).

Калькуляция, подбираем значение прибыли

Еще пример. Пусть дана структура цены договора: Собственные расходы, Прибыль, НДС.

Известно, что Собственные расходы составляют 150 000 руб., НДС 18%, а Целевая стоимость договора 200 000 руб. (ячейка С13 ). Единственный параметр, который можно менять, это Прибыль. Подберем такое значение Прибыли ( С8 ), при котором Стоимость договора равна Целевой, т.е. значение ячейки Расхождение ( С14 ) равно 0.

В структуре цены в ячейке С9 (Цена продукции) введена формула Собственные расходы + Прибыль ( =С7+С8 ). Стоимость договора (ячейка С11 ) вычисляется как Цена продукции + НДС (= СУММ(С9:C10) ).

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

Выделите ячейку С14 , вызовите Подбор параметра (на вкладке Данные в группе Работа с данными выберите команду Анализ «что-если?» , а затем выберите в списке пункт Подбор параметра …). В качестве целевого значения для ячейки С14 укажите 0, изменять будем ячейку С8 (Прибыль).

Теперь, о том когда этот инструмент работает. 1. Изменяемая ячейка не должна содержать формулу, только значение.2. Необходимо найти только 1 значение, изменяя 1 ячейку. Если требуется найти 1 конкретное значение (или оптимальное значение), изменяя значения в НЕСКОЛЬКИХ ячейках, то используйте Поиск решения.3. Уравнение должно иметь решение, в нашем случае уравнением является зависимость стоимости от прибыли. Если целевая стоимость была бы равна 1000, то положительной прибыли бы у нас найти не удалось, т.к. расходы больше 150 тыс. Или например, если решать уравнение x2+4=0, то очевидно, что не удастся подобрать такое х, чтобы x2+4=0

Примечание : В файле примера приведен алгоритм решения Квадратного уравнения с использованием Подбора параметра.

Подбор суммы кредита

Предположим, что нам необходимо определить максимальную сумму кредита , которую мы можем себе позволить взять в банке. Пусть нам известна сумма ежемесячного платежа в рублях (1800 руб./мес.), а также процентная ставка по кредиту (7,02%) и срок на который мы хотим взять кредит (180 мес).

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

Введем в ячейку B 6 ориентировочную сумму займа, например 100 000 руб., срок на который мы хотим взять кредит введем в ячейку B 7 , % ставку по кредиту введем в ячейку B8, а формулу =ПЛТ(B8/12;B7;B6) для расчета суммы ежемесячного платежа в ячейку B9 (см. файл примера ).

Чтобы найти сумму займа соответствующую заданным выплатам 1800 руб./мес., делаем следующее:

  • на вкладке Данные в группе Работа с данными выберите команду Анализ «что-если?» , а затем выберите в списке пункт Подбор параметра …;
  • в поле Установить введите ссылку на ячейку, содержащую формулу. В данном примере — это ячейка B9 ;
  • введите искомый результат в поле Значение . В данном примере он равен -1800 ;
  • В поле Изменяя значение ячейки введите ссылку на ячейку, значение которой нужно подобрать. В данном примере — это ячейка B6 ;
  • Нажмите ОК

Что же сделал Подбор параметра ? Инструмент Подбор параметра изменял по своему внутреннему алгоритму сумму в ячейке B6 до тех пор, пока размер платежа в ячейке B9 не стал равен 1800,00 руб. Был получен результат — 200 011,83 руб. В принципе, этого результата можно было добиться, меняя сумму займа самостоятельно в ручную.

Подбор параметра подбирает значения только для 1 параметра. Если Вам нужно найти решение от нескольких параметров, то используйте инструмент Поиск решения . Точность подбора параметра можно задать через меню Кнопка офис/ Параметры Excel/ Формулы/ Параметры вычислений . Вопросом об единственности найденного решения Подбор параметра не занимается, вероятно выводится первое подходящее решение.

Читать еще:  Как создать страницу в word

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

Microsoft Excel

трюки • приёмы • решения

Как в Excel использовать функцию Подбор параметра

Многие листы Excel настроены на анализ «что — если». Например, вы могли создать таблицу со списком продаж, который позволяет ответить на такой вопрос: «Какова будет общая прибыль, если продажи увеличатся на 20 %?» Если вы корректно создали таблицу, то можете изменить значение в одной ячейке, чтобы увидеть, что произойдет с ячейкой прибыли.

Excel предлагает полезный инструмент, который можно охарактеризовать как анализ «что — если» в обратном порядке. Если вы знаете, каким должен быть результат формулы, то Excel может сказать вам значение, которое необходимо ввести в ячейку для ввода, чтобы получить этот результат. Другими словами, вы можете задать такой вопрос: «Насколько необходимо увеличить продажи, чтобы получать прибыль величиной $1,2 миллиона?». Это также может быть заданием в учебном заведении, но те кто заказал реферат не пожалели о выбранной теме.

На рис. 86.1 показаны две обычные таблицы, в которых выполняются расчеты по ипотечному кредиту. В первой таблице есть четыре ячейки для ввода ( С4:С7 ), а во второй — четыре ячейки с формулами ( С10:С13 ).

Рис. 86.1. Таблица с расчетами по ипотечному кредиту

Предположим, вы находитесь на рынке недвижимости и знаете, что точно можете себе позволить ежемесячные выплаты в размере $1800 по ипотеке. Вы также знаете, что кредитор может выдать ипотечный кредит с фиксированной ставкой 6,5 %, основанный на 80% стоимости всего кредита (то есть 20% будет составлять ваш авансовый платеж). Вопрос состоит в следующем: «Какова максимальная цена недвижимости, которую я смогу взять в кредит?» Другими словами, какое значение в ячейке С4 вызовет появление результата формулы в ячейке С11 , равного $1800?

Один из подходов состоит в том, чтобы подставлять кучу значений в ячейку С4 , пока С11 не отобразит $1800. Однако Excel может вычислить ответ гораздо более эффективно. Так, чтобы ответить на этот вопрос, выполните следующие действия.

  1. Выберите Данные ► Работа с данными ► Анализ «что-если» ► Подбор параметра. Появится диалоговое окно Подбор параметра.
  2. Заполните три поля (рис. 86.2) подобно формированию предложения: вы хотите установить в ячейку С11 значение 1800 путем изменения значения ячейки С4 . Введите эту информацию в диалоговое окно, вводя ссылки на ячейки либо указывая их с помощью мыши.
  3. Нажмите кнопку ОК, чтобы начать процесс подбора параметра.

Рис. 86.2. Диалоговое окно Подбор параметра

Менее чем за секунду Excel выведет диалоговое окно Статус подбора параметра, которое показывает целевое значение и значение, рассчитанное Excel. В этом случае программа находит точное значение. Теперь в таблице в ячейке С4 показано найденное значение ($284 779). В результате этого значения ежемесячный платеж составит $1800. На данный момент у вас есть два варианта:

  • нажмите кнопку ОК, чтобы заменить исходное значение найденным;
  • нажмите Отмена, чтобы восстановить таблицу такой, какой она была, прежде чем была вызвана команда Подбор параметра.

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

Сервис подбор параметра в excel 2020

На этом шаге мы рассмотрим анализ данных: подбор параметра.

Рассмотрим следующий типичный вопрос, анализа «что-если»: «Каким станет общий доход, если объем продаж возрастет на 20%?» Если рабочий лист создан правильно, то, изменив значение в одной из ячеек, Вы увидите, что получится в ячейке, содержащей значение дохода. При выполнении процедуры подбора параметров используется противоположный подход. Если вы знаете, каким должен быть результат вычисления по формуле, то Excel подскажет Вам значения одного или нескольких входных параметров, которые позволят получить нужный результат. Другими словами, вы можете задать вопрос такого типа: «Какой рост продаж необходим для получения дохода в 1 200 000 руб.?» В Excel для этой цели предусмотрено два средства:

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

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

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

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

Вам известно, что в месяц Вы в состоянии погашать не больше 1 200 взятой ссуды. Вы также знаете, что кредитор даст вам ссуду под фиксированный процент (скажем 8,25%), рассчитывая на то, что Вы должны погасить за определенное время 80% ссуды (т.е. первоначальный взнос составляет 20%). Вопрос состоит в следующем: «Какова максимальная стоимость покупки, которую Вы себе можете позволить?» Другими словами, какое значение должно быть в ячейке С4, чтобы результат в ячейке С11 равнялся 1 200? Один способ решения — изменять значения в ячейке С4 до тех пор, пока значение в ячейке С11 не станет равным 1200. Более эффективный способ — позволить Excel найти ответ, то есть использовать процедуру подбора параметра.

Чтобы ответить на этот вопрос, выберите команду Сервис | Подбор параметра. Появится диалоговое окно, показанное на рис. 2.

Рис. 2. Диалоговое окно Подбор параметра

Заполнение этого диалогового окна подобно составлению предложения: нужно получить 1200 в ячейке С11, изменяя значение в ячейке С4. Ввести эту информацию в диалоговое окно Подбор параметра можно, либо непосредственно набрав адреса ячеек с клавиатуры, либо щелкнув указателем мыши на нужных ячейках. Чтобы начать процесс подбора параметра, щелкните на кнопке OK. Через секунду Excel объявит, что решение найдено, и выведет окно Результат подбора параметра (рис. 3).

Рис. 3. Диалоговое окно Результат подбора параметра

В этом диалоговом окне будет отображено подбираемое значение и значение, предложенное Excel. В данном случае программа нашла точное значение. В ячейке С4 рабочего листа теперь будет находиться искомое значение ($199 663). Взяв такую ссуду, в месяц Вы должны будете погашать 1 200. На данном этапе у Вас есть две возможности:

  • Щелкнуть на кнопке OK, чтобы заменить прежнее значение найденным.
  • Щелкнуть на кнопке Отмена, чтобы вернуть рабочий лист в прежнее состояние — как до выполнения команды Сервис | Подбор параметра.

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

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

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

Графический подбор параметра

Excel предоставляет проведение подбора параметра с помощью манипулирования диаграммами. На рис. 4 показан рабочий лист, отображающий предполагаемый объем продаж развивающейся компании. Предположим, что из опыта известно, что рост объема продаж компаний, работающих в этой отрасли, может увеличиваться по показательному закону: =y*(b^x)

В таблице 1 перечислены и описаны все переменные этой формулы.

Подбор параметра в Excel: решаем задачки-нерешучки

Здравствуйте, уважаемые читатели! В прошлой статье мы научились моделировать результат при разных входных параметрах, выполняя анализ «что если». Сегодня же мы разберем обратную задачу, не менее частую, сложную и насущную. Пусть нам известен результат, и нужно знать, какими должны быть входные величины для его получения. То есть, нужно подобрать решение задачи. Возможно ли это в Excel? Конечно возможно, давайте разбираться!

Программа предоставляет нам два способа решения такой проблемы:

  1. Инструмент «Подбор параметра»
  2. Инструмент «Поиск решения»

Подбор параметра в Эксель

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

Разберем на простом примере. Мы с Вами планируем открыть депозит с ежемесячным пополнением. Сейчас у нас на руках есть 10 тыс. у.е., но после окончания срока депозита, через 12 месяцев, хотим иметь капитал в 20 тысяч. Требуется посчитать, какую сумму нужно ежемесячно класть на депозит, чтобы через 12 месяцев накопить сумму в 20 тысяч у.е.

Вот наша таблица с расчетами:

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

Фактически нам нужно подобрать такое значение в ячейке В3, чтобы в В7 стало 20 000. Используем инструмент «Подбор параметра»:

  1. Жмем на ленте Данные – Работа с данными – Анализ «что если» — подбор параметра ;
  2. В открывшемся окне задаем данные для настройки:
    • Установить в ячейке: в этом параметре указываем ссылку на наше целевое значение, т.е. «Конечный капитал»;
    • Значение: здесь нужно указать то значение, которое должно быть в целевой ячейке, т.е. нужный результат вычислений. В нашем случае это 20 000;
    • Изменяя значение ячейки: Укажем ссылку на ячейку, значение которой нужно изменять, чтобы подбирать результат. В нашем примере это «Ежемесячный взнос»;

  1. Жмем Ок, программа будет искать решение. Когда оно будет найдено, Excel сообщит о завершении подбора. Нажимаем Ок в окне, чтобы принять найденное значение и записать его в ячейку, или Отмена, чтобы оставить все как было.

В нашем примере все сработало отлично, и мы узнали, что для получения капитала в 20 тыс, нужно ежемесячно добавлять на депозит по 736,55 у.е.

Иногда случается, что поиск решения не дал результата, тогда нужно проверить всё ли правильно:

  1. Первым делом удостоверьтесь, что целевая ячейка зависит от того значения, которое мы изменяем. Если итоговая формула не ссылается на изменяемое значение – восстановите эту зависимость и повторите поиск;
  2. Пробуем поставить в изменяемой ячейке значение ближе к искомому, очень часто это помогает;
  3. В Экселе ограничено количество итераций для подобного поиска. Возможно, этого количества не хватило, чтобы найти решение. Пробуем увеличить количество итераций. Для этого жмем Файл – Параметры – Формулы , а там в группе команд «Параметры вычислений» увеличьте предельное число итераций.

  1. Осмыслите вычисления, которые предлагаете произвести программе. Точно ли заданные Вами параметры имеют решение? Если не имеют – сделайте их корректными.

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

Инструмент «Поиск решения»

Как Вы убедились, подбор параметра отлично и безотказно работает практически во всех случаях. Но у него есть недостаток – он манипулирует лишь одним значением для изменения результата. А что, если нужно построить более сложную систему вычислений? Тогда используем «Поиск решения».

И снова рассмотрим на примере. Спланируем производственный процесс на месяц для получения максимальной прибыли. Вот наша таблица заготовка:

В таблице имеем такие поля:

  1. Минимальная партия – минимальное количество товара, которое нужно произвести для обслуживания уже существующих заказов;
  2. Максимальная партия – наибольшее количество товара, которое можно произвести, исходя из запасов сырья
  3. Норма рабочего времени – количество человекочасов, необходимых для производства одного изделия;
  4. Затраты рабочего времени – количество времени, которое будет затрачено на производство всего запланированного. Пусть у нас работает 20 работников по 8 часов 22 дня в месяце. Тогда сумма по этому полю должна составить 3520 ч.
  5. Себестоимость – стоимость производства одной единицы продукции
  6. Цена реализации – рыночная стоимость одной единицы продукции
  7. Валовая прибыль – прибыль, которая будет получена от реализации изготовленного товара.

Для упрощения, будем считать, что спрос на товар выше производственных возможностей, и всё произведенное будет продано. Так сколько чего нам нужно произвести, чтобы получить наибольшую выгоду, а персонал трудился ровно 3520 ч? Запускаем «Поиск решения»:

  1. Ищем на ленте Данные – Анализ – Поиск решения . Кликаем, откроется окно настройки;
  2. В поле «Оптимизировать целевую функцию» задаем ссылку на сумму по столбцу «Валовая прибыль»;
  3. В поле «До» выбираем «Максимум». В других случаях можно выбрать «минимум», или задать какое-то конкретное значение;
  4. В списке «Изменяя ячейки переменных» указываем все строки столбца «Производим»
  5. Далее нужно внести все оговоренные выше ограничения. Для этого жмем «Добавить» и в открывшемся окне выбираем ссылки на ячейки и параметры их ограничения:

Вносим все оговоренные ограничения, они отобразятся в списке окна настройки:

  1. Суммарные затраты времени должны равняться 3520 часов;
  2. Производимое количество больше или равно минимальной партии
  3. Производимое количество меньше или равно максимальной партии
  4. Производимое количество должно быть целым числом

  1. Выбираем метод решения в соответствии с рекомендациями разработчиков внизу окна настроек. Мы выберем линейный метод. Жмем «Найти решение», по завершению поиска программа сообщает о результате.

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

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

Экспериментируя с многочисленными настройками инструмента, можно детально управлять процессом поиска. На самом деле, «Поиск решения» — очень функциональная и многогранная надстройка, познать все азы которой можно на сайте разработчика: www.solver.com.

Кстати, если Вы не нашли на ленте этот инструмент – не отчаивайтесь, его просто нужно подключить. Для этого нажмите Файл – Параметры – Надстройки . Внизу в раскрывающемся списке «Управление» выберите «Надстройки Excel» и нажмите «Перейти». В открывшемся окне поставьте галку напротив «Поиск решения» и нажмите Ок. Вот и всё, он сразу же появится ленте!

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

Если у Вас что-то не получилось – задавайте свои вопросы в комментариях, будем разбираться вместе. Если все вышло — сбросьте другу ссылку на эту статью. Пусть и он использует Эксель в полной мере!

Экспериментируйте, а я отправляюсь писать следующий пост. До новых встреч на страницах блога officelegko.com!

Добавить комментарий Отменить ответ

4 комментариев

Добрый день, Александр!

Есть задача которую я не могу понять с помощью какой формулы описать решение, причем прописать эти формулы в гугл таблице, но думаю суть та же будет если сделать это и в эксели
если в кратце: то например я знаю что мне надо накопить 20000, то если откладывать каждый месяц по 10 000 то через 2 месяца я добъюсь цели, как это описать формульно чтобы эксель показал что в зависимости от того сколько накапливается в месяц я смогу накопить 20000? чтобы программа показала мне время через которое я накоплю средства есть столбец месяцев с суммами того что накопил в этих столбцах при этом там есть и пустыми суммы за декабрь например. Просто бьюсь уже 5 дней не могу понять возможно ли решение для такой задачи или нет. ссылка на файл о чем речь :
https://docs.google.com/spreadsheets/d/1kyP2HwB8WFeAqJkkANC9TxQCsIv3K-44Wfe3xabfQeA/edit?usp=sharing

Даниил, здравствуйте. Уточните пожалуйста вопрос.Вы хотите, чтобы программа считала срок исходя из какого-то обязательного ежемесячного платежа, или из средних ежемесячных накоплений за какой-то период?

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

Даниил, в Excel есть функция, которая считает средние значения — СРЗНАЧ. Тогда формула расчета количества месяцев будет такая: =<Остаток суммы>/СРЗНАЧ<Диапазон с данными по ежемесячному внесению средств>). Естественно, в фигурных скобках я указал описания, а вы укажите соответствующие ссылки на ячейки и диапазоны ячеек

Excel. Подбор параметра. Поиск решения

Подбор параметра

  1. Откройте таблицу Вычисление подоходного налога (если она не сохранилась, то придется ее сделать заново).
  2. Добавьте столбик Доход на человека. Будем предполагать, что у супруга/супруги такой же доход. Тогда доход на человека вычисляется так: 2*x/(2+k), где x – начисление минус налог, k — количество детей.
  3. Теперь задача: каким должно быть начисление, чтобы доход на человека составлял 3000 р. Для этого поместите курсор в ячейку, в которой должно получиться 3000. Далее выберите Сервис – Подбор параметра. В появившемся окне нужно задать три параметра: Название ячейки (по умолчанию написано название ячейки, в которой находится курсор), значение, которое должны получить (в нашем случае – 3000), название ячейки, за счет которой необходимо получить нужное значение (чтобы ее указать щелкните ЛКМ по красно-бело-синему значку, стоящему справа от ячейки, укажите ЛКМ на ячейку и нажмите Enter). Нажмите Ok два раза. Параметр подобран. Сделайте это для каждой фамилии.

Поиск решения

  1. Постройте и оформите следующую таблицу

    1. Здесь в столбике График отмечены группы работников, во втором столбике отмечены выходные дни у соответствующих групп (каждая группа должна иметь два дня выходных, идущих друг за другом), в столбике Работники отмечено количество работников в каждой группе. а далее цифра 1 означает, что данная группа в этот день работает, цифра 0 – группа не работает. В ячейке C14 находится формула, вычисляющая общее количество работников. В ячейке C15 – оплата труда одному человеку за неделю. В ячейке C15 – формула, вычисляющая общую оплату работников за неделю.
    2. Введите текст в клетки. Отформатируйте информацию в клетках и выполните обрамление (и заливку) диапазонов ячеек так, как указано в примечаниях (пункт меню Формат). Ширину столбца можно изменить, установив курсор мыши на разделительную линию в строке заголовков столбцов (курсор примет вид креста со стрелками) и, при нажатой левой кнопке мыши, переместив ее в нужную сторону. В ячейки E14:K14 вставьте формулы для вычисления суммарного количества сотрудников, работающих в соответствующий день недели. Для вставки Вставка – Функция. Используйте функцию СУММПРОИЗВ. Скопируйте введенную формулу в остальные ячейки диапазона, используя маркер копирования в правом нижнем углу ячейки. Для правильного копирования формулы в данном случае необходимо, чтобы в ссылке на диапазон C6:C12 использовались абсолютные адреса, т.е. ссылка должна иметь вид $C$6:$C$12.
  1. Пусть требуется решить задачу о построении графика занятости персонала парка отдыха.

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

  1. Пусть требуемый уровень обслуживания определяется заданным (для каждого дня недели) числом работников (22, 17, 13, 14, 15, 18, 24 соответственно в воскресенье, понедельник,…субботу). Дополните Таблицу. В ячейки E15:K15 введите значения 22, 17, 13, 14, 15, 18, 24. Для выделения ограничений выполните обрамление диапазона E14:K15 жирной красной линией.
  2. Параметры задачи.

C16 — Расходы на оплату труда.

C6:C12 — Число работников в группе (изменяемые данные – неизвестные в задаче).

C6:C12>=0 (Число работников в группе не может быть отрицательным)

C6:C12=Целое (Число работников должно быть целым.)

E14:K14>=E15:K15 (Число ежедневно занятых работников не должно быть меньше ежедневной потребности).

Выберите Сервис – Поиск решения. Установить целевую – С16. Изменяя ячейки – С6-С12. При помощи кнопки Добавить установите три ограничения – С6:С12>=0, С6:С12=Целое, E14:K14>=E15:K15

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

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

Связанные статьи

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

Ссылка на основную публикацию
Adblock
detector