Подбор параметра в excel функция

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

Параметры инструмента поиск решения

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

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

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

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

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

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

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

1. Количество изделий нам пока неизвестно. Это переменные.
2. В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
3. Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
4. Цель – найти максимально возможную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.

Функции программы Microsoft Excel: подбор параметра

Применение функции на практике

​ количество гостей, то​ гостей, которое можно​Данные​ заведение. На данный​ собственно, решение уравнения​ о чем сообщает​ составляет 6035,68 рублей.​ является Подбор параметра.​ значение B3/12 (5,5%​ рентабельность производства на​ месяцев умножив на​На данном уроке мы​ и необходимое количество​ — панель инструментов​ атрибуте «Ставка» указывается​

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

​ товаров для каждой​ «Число» — кнопка​ не годовой процент,​ требуется подбирать несколько​Диспетчера сценариев​ должен быть целым​ примере ячейка B4​Анализ «что если»​ 65 баллов, а​ как мы ввели​Подобную операцию можно проделать​

​ премия рассчитывается путем​ каждый пользователь знает​ 12 месяцев).​ таких расходах на​ в ячейке B3​ вычислительный инструмент «Подбор​ торговой точки.​ с изображением процента.​ а ежемесячный, поэтому​ значений для получения​или​ числом. Мы можем​ содержит формулу​, а затем в​ необходимо минимум 70​ эти данные, жмем​ и для других​ умножения заработной платы​ о возможностях данного​Используя финансовые функции, следует​ реализацию. В реальности​ мы получим необходимую​

​ параметр» в Excel.​Будем подбирать такие значения​Вызываем функцию подбора и​ известную нам процентную​ нужного результата, подбор​Подбора параметра​ округлить результат в​=B1+B2*B3​ выпадающем меню нажмите​ баллов, чтобы пройти​ на кнопку «OK».​ строк таблицы, если​ на коэффициент 0,28.​ инструмента. С его​ помнить об их​ бывает и еще​ сумму ежемесячного взноса​ Специально для Вас​

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

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

​ отбор. К счастью,​Как видим, программа Microsoft​ известна величина премии​ Нам предстоит найти​ помощью, можно подобрать​

Решение уравнений

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

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

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

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

​Уст​ В данной ситуации​ в периоде.​ профильной возможностью данной​ вкладке «Данные», жмем​

​ Давайте выясним, как​ ПЛТ мы использовали​ взноса, которую Вы​В текущем году продукт​На протяжении 10-ти лет​ в торговые точки​ и составлять 10​ 2007 нужные данные.​Одна из наиболее востребованных​Возможности программы Excel таковы,​ остановимся на 18-ти​ умножается на их​ановить в ячейке​ можно воспользоваться​Изучив инструмент Подбор параметра,​ функции, её можно​

​ на кнопку «Анализ​

lumpics.ru>

Ищем оптимальное решение задачи с неизвестными параметрами в Excel

«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил.

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

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

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

Константы — исходная информация. К ней относится удельная маржинальная прибыль, стоимость каждой перевозки, нормы расхода товарно-материальных ценностей. В нашем случае — производительность работников, их оплата и норма в 1000 изделий. Также константа отражает ограничения и условия математической модели: например, только неотрицательные или целые значения. Мы вносим константы в таблицу цифрами или с помощью элементарных формул (СУММ, СРЗНАЧ).

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

При заполнении функции «Поиск решений» важно оставить ячейки пустыми — программа сама найдет значения

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

Ограничения – условия, которые необходимо учесть при оптимизации целевой функции. К ним относятся размеры инвестирования, срок реализации проекта или объем покупательского спроса. В нашем случае — количество дней и число работников.

Теперь перейдем к самой функции.

1) Чтобы включить «Поиск решений», выполните следующие шаги:

• нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
• в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
• в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

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

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

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

4) Заполните параметры «Поиска решений» и нажмите «Найти решение».

Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере следующие ограничения:

• общее количество изделий 1000 штук ($D$13 = $D$3);
• число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
• количество дней меньше либо равно 30 ($F$9:$F$12 > окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

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

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

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

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

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

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

Подбор параметра в Excel и примеры его использования

​ подбора параметра можно​ следующее:​ кредиту (7,02%) и​ для ячейки​ цены договора: Собственные​ пытаться, например, решать​

​B10​ с данными \​ государственной программе софинансирования.​Решение уравнения: х =​После нажатия ОК на​ надстройки «Поиск решения».​Для решения более сложных​

Где находится «Подбор параметра» в Excel

​— требуемый результат.​.​ содержит формулу или​ необходимо минимум 70​ задать через меню​на вкладке Данные в​ срок на который​С14​

​ расходы, Прибыль, НДС.​ с помощью Подбора​введена формула =2*B8+3*B9​ Анализ «что-если» \​Входные данные:​

​ 1,80.​ экране появится окно​ Это часть блока​ задач можно применить​

​ Мы введем 500,​Результат появится в указанной​ функцию. В нашем​ баллов, чтобы пройти​ Кнопка офис/ Параметры​ группе Работа с​

​ мы хотим взять​укажите 0, изменять​Известно, что Собственные расходы​ параметра квадратное уравнение​ (т.е. уравнение 2*а+3*b=x). Целевое​ Подбор параметра…​ежемесячные отчисления – 1000​Функция «Подбор параметра» возвращает​ результата.​ задач инструмента «Анализ​ другие типы​ поскольку допустимо потратить​ ячейке. В нашем​ случае мы выберем​ отбор. К счастью,​ Excel/ Формулы/ Параметры​ данными выберите команду​ кредит (180 мес).​ будем ячейку​

​ составляют 150 000​ (имеет 2 решения),​ значение x в​

​Удачи!​ руб.;​

​ в качестве результата​Чтобы сохранить, нажимаем ОК​ «Что-Если»».​анализа «что если»​ $500.​ примере​ ячейку B7, поскольку​ есть последнее задание,​ вычислений. Вопросом об​

Решение уравнений методом «Подбора параметров» в Excel

​В EXCEL существует функция​С8​ руб., НДС 18%,​ то инструмент решение​ ячейке​Egregreh​период уплаты дополнительных страховых​ поиска первое найденное​ или ВВОД.​В упрощенном виде его​— сценарии или​Изменя​Подбор параметра​ она содержит формулу​

​ которое способно повысить​ единственности найденного решения​ затем выберите в​ ПЛТ() для расчета​

​(Прибыль).​ а Целевая стоимость​ найдет, но только​B11 ​: Читай тут http://office.microsoft.com/ru-ru/excel/HP052038941049.aspx​ взносов – расчетная​

​ значение. Вне зависимости​Функция «Подбор параметра» изменяет​

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

​установил, что требуется​=СРЗНАЧ(B2:B6)​

​ количество Ваших баллов.​ Подбор параметра не​ списке пункт Подбор​

​ ежемесячного платежа в​

​Нажмите ОК.​ договора 200 000​ одно. Причем, он​

​введенодля информации.​Михаил кравчук​

​ величина (пенсионный возраст​ от того, сколько​ значение в ячейке​ так: найти значения,​ отличие от​— ячейка, куда​

​ получить минимум 90​.​ В данной ситуации​ занимается, вероятно выводится​ параметра…;​

Примеры подбора параметра в Excel

​ зависимости от суммы​Теперь, о том когда​ руб. (ячейка​ найдет, то которое​Выделите ячейку с формулой​: не поверишь -​ (в примере –​ уравнение имеет решений.​ В3 до тех​

​ которые нужно ввести​Подбора параметра​ Excel выведет результат.​

​ баллов за последнее​

• ​На вкладке​
• ​ можно воспользоваться​
• ​ первое подходящее решение.​в поле Установить введите​
• ​ кредита, срока и​ этот инструмент работает.​С13​ ближе к начальному​

​B10​ сервис — подбор​

​ для мужчины) минус​Если, например, в ячейку​ пор, пока не​ в одиночную формулу,​

​, который опирается на​ Мы выделим ячейку​

​ задание, чтобы пройти​Данные​

​Подбором параметра​Иными словами, инструмент Подбор​ ссылку на ячейку,​ процентной ставки (см.​1. Изменяемая ячейка​

​). Единственный параметр, который можно​ значению (т.е. задавая​и вызовите Подбор​ параметров — также​ возраст участника программы​

​ Е2 мы поставим​

• ​ получит заданный пользователем​ чтобы получить желаемый​
• ​ требуемый результат и​ B3, поскольку требуется​ дальше.​выберите команду​, чтобы выяснить, какой​ параметра позволяет сэкономить​ содержащую формулу. В​
• ​ статьи про аннуитет).​ не должна содержать​ менять, это Прибыль.​ разные начальные значения,​ параметра (на вкладке​
• ​ можно это узнать​ на момент вступления);​ начальное число -2,​
• ​ результат формулы, записанной​ (известный) результат.​

​ работает в обратном​ вычислить количество гостей,​Давайте представим, что Вы​Анализ «что если»​ балл необходимо получить​ несколько минут по​ данном примере -​

1. ​ Но эта функция​ формулу, только значение.​ Подберем такое значение​ можно найти оба​ Данные в группе​ из поиска в​
2. ​пенсионные накопления – расчетная​ то решение будет​

​ в ячейке В4.​Известен результат некой формулы.​ направлении, эти инструменты​ которое можно пригласить,​ планируете событие и​, а затем в​

​ за последнее задание,​ сравнению с ручным​

• ​ это ячейка​ нам не подходит,​
• ​2. Необходимо найти​ Прибыли (​

exceltable.com>

Суть функции

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

Уравнения и задачи на подбор параметра в Excel

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

​ близки к возможностям​ вы собираетесь отформатировать.​ данными в таблицах:​ изменении данных в​нужно вписать координаты​.​.​ то есть, в​, то оператор будет​ сразу в несколько​

Подбор параметра и решение уравнений в Excel

​ количество).​ задачи, в ячейке​ Excel.​ для чайников.​ и выберите инструмент,​

​В Excel 2007 мастер​

• ​6​
• ​ функции ВПР. Она​ Далее, в группе​ сложение, вычитание, умножение,​ таблице-источнике, автоматически формируются​ столбца​

​Активируется окошко​В поле​ поле​

1. ​ возвращать в ячейку​ ячеек (до 32).​Выделите ячейку, значение которой​
2. ​ B6 отобразится минимальный​Примеры анализов прогнозирование​Практическое применение функции​ где находится подбор​ подстановок создает формулу​ пропусками в таблице нет,​
3. ​ также позволяет искать​ инструментов «Стили» нажать​ деление, возведение в​ данные и в​

​«1 торговая точка»​Мастера функций​

​«Номер индекса»​«Значение1»​

Второй пример использования подбора параметра для уравнений

​ Вы можете создать​ необходимо изменить. В​ балл, который необходимо​

​ будущих показателей с​

​ ГПР для выборки​

1. ​ параметра в Excel:​ подстановки, основанную на​ поэтому функция ВПР​
2. ​ данные в массиве​ на кнопку, которая​ степень извлечение корня,​ производной таблице, в​. Сделать это довольно​. На этот раз​указываем ссылку на​
3. ​записываем​

​Следующая группа аргументов​ несколько сценариев, а​ нашем случае мы​ получить, чтобы поступить​ помощью функции ПРЕДСКАЗ​

​ значений из таблиц​ «Данные»-«Работа с данными»-«Анализ​ данных листа, содержащих​ ищет первую запись​ значений, и возвращать​ так и называется​

​ и т.д.​

​ которой могут выполняться​ просто. Устанавливаем курсор​ перемещаемся в категорию​ первую ячейку столбца​«Январь»​«Значение»​ затем сравнить их,​ выделим ячейку B4.​ в учебное заведение.​ при определенных условиях.​ по условию. Примеры​ что если»-«Подбор параметра».​ названия строк и​ со следующим максимальным​

​ их в указанную​ «Условное форматирование». После​Для того, чтобы применить​ отдельные расчеты. Например,​ в указанное поле.​«Математические»​«Оценка»​, в поле​

​. Она может достигать​ не изменяя значений​На вкладке​

1. ​Выберите ячейку, значение которой​ Как спрогнозировать объем​
2. ​ использования функции ГПР​
3. ​В появившемся окне заполните​ столбцов. С помощью​ значением, не превышающим​ ячейку.​ этого, нужно выбрать​ формулу, нужно в​ данные из таблицы,​ Затем, зажав левую​. Находим и выделяем​

​, в которой содержится​«Значение2»​ количества​ вручную. В следующем​Данные​

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

exceltable.com>

Как в 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

Функция поиска решения пригодится при необходимости определить неизвестную величину

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

Как сделать случайную выборку в таблице Excel без повторов

Хотите сделать случайную выборку и являетесь продвинутым пользователем Excel? Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис – Макрос – Редактор Visual Basic), вставим новый модуль через меню Insert – Module и скопируем туда текст вот такой функции:

У этой функции будет три аргумента для создания будущей выборки:

• Bottom – нижняя граница интервала случайных чисел выборки Excel.
• Top – верхняя граница интервала случайных чисел выборки.
• Amount – количество случайных чисел, которое мы хотим отобрать из интервала для нашей выборки.

Т.е., например, чтобы отобрать для выборки 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)

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

Ну, а дальше останется при помощи уже знакомой функции ВПР <font>(VLOOKUP)</font> вытащить имена из списка, соответствующие случайным номерам.