Анализ данных в microsoft excel подбор параметров разработка сценариев

Обновлено: 06.07.2024

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

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

Как использовать Подбор параметра (пример 1):

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

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

Как использовать Подбор параметра (пример 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

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

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

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

Нахождение значения аргумента функции, соответствующего определённому значению функции

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

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

Значение в ячейке С1 представляет собой среднее арифметическое значение в ячейках А1 и В1:

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

Безусловно, можно самостоятельно путём перебора значений в ячейке А1 достичь необходимый результат. Однако, в целях минимизации затрат времени следует воспользоваться функцией Подбор параметра.

Для этого необходимо:

1) выполнить команду Подбор параметра из меню Данные > Анализ "что-если".

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

2) в поле Установить в ячейке ввести ссылку или имя ячейки, содержащую формулу, для которой следует подобрать параметр. Автоматически в поле Установить в ячейке отображается имя ячейки, которая была активной на момент выполнения команды Подбор параметра. Кнопка свёртывания окна диалога, расположенная справа от поля, позволяет временно убрать диалоговое окно с экрана, чтобы было удобнее выделить диапазон на листе. Выделив диапазон, следует нажать кнопку для вывода на экран диалогового окна.

3) в поле Значение ввести число, которое должно возвращать формула с искомым значением параметра. Например, 855.

4) в поле Изменяя значение ячейки указать ссылку на ячейку, содержащую параметр, значение которого требуется подобрать для получения требуемого результата. На эту ячейку прямо или косвенно должна ссылаться формула, содержащаяся в ячейке, адрес которой указан в поле Установить в ячейке. В нашем случае это А1.

В итоге диалоговое окно примет следующий вид:

5) нажать кнопку ОК для закрытия диалогового окна. После выполнения этого действия появляется запрос Результат подбора параметра, а искомое значение параметра отображается в ячейке А1:

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

Допустим, что для решения поставленной задачи нам предстоит проанализировать построенный в Ms Excel график функции y = 3x-5 в диапазоне аргумента от –3 до 6.

Для этого следует:

$3\dot А1-5$

1) в ячейки А1-А10 ввести значения от –3 до 6 с шагом 1; в ячейку В1 – ввести формулу и путём перетаскивания маркера заполнения скопировать эту формулу на ячейки В2-В10. В результате соответствующий участок листа примет следующий вид:

2) выделив диапазон В1-В10, выберите тип диаграммы "График" на вкладке Вставить в группе Диаграммы.

3) На вкладке Макет в группе Подписи нажмите кнопку Подписи данных, а затем выберите нужный параметр отображения.

4) На вкладке Конструктор в группе Данные нажмите Выбрать данные.

5) В появившемся окне Выбор источника данных выберите Подписи горизонтальной оси. Задайте диапазон подписей оси диапазон А1-А10.

В результате должен быть построен график функции:

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

Чтобы решить эту задачу с помощью построенного графика и функции Подбор параметра необходимо:

1) щелчком левой кнопки мыши на графике выделить ряд данных, содержащий маркер данных, который нужно изменить,

а затем выделить щелчком сам маркер

2) в меню Данные > Анализ выбрать функцию Поиск решения

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

4) в поле Оптимизировать целевую функцию отображается ссылка на ячейку, содержащую формулу, в поле Значения – требуемая величина. Так, в данном случае следует указать ячейку В6 и значение "0" соответственно и нажать кнопку ОК.

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

При этом исходное значение аргумента в ряде данных сменится на значение, полученное в результате поиска решения 1,666667 (рис. 11. рис. 11.11).

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

$y=2\cdot x^<2> Поиск решения позволяет находить одно значение аргумента, соответствующее заданному значению функции (например, 0). Однако часто функция может принимать одно значение при нескольких значениях аргументов. То есть уравнение может иметь несколько корней. Например, функция -9$
может принимать значение 0 при двух значениях аргументов.

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

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

Выполнив команду Подбор параметра из меню Данные > Анализ "Что-если", необходимо заполнить поля диалогового окна следующим образом:

В результате найденным корнем уравнения будет значение 2,121343 в ячейке А4 (рис. 11. рис. 11.14).

$y=2\cdot x^<2> Однако, это не единственный корень. В этом можно убедиться, решив уравнение или построив график функции -9$
.

Для построения графика следует:

в ячейки С4-С24 ввести значения от –10 до 10 с шагом 1; в ячейку D4 – ввести формулу 2*C4*C4-9 и путём перетаскивания маркера заполнения заполнить этой формулой ячейки D5-D24 ;
выделив диапазон D4-D24 , выбрать тип диаграммы График в меню Вставка > Диаграммы;
На вкладке Конструктор в группе Данные нажмите Выбрать данные.
В появившемся окне Выбор источника данных выберите Подписи горизонтальной оси. Задайте диапазон подписей оси диапазон С4-С24.

В результате должен быть построен график функции:

$2\cdot x^<2> Из графика видно, что уравнение -9=0$
имеет 2 корня, к тому же эти корни примерно равны –2 и 2. Одни корень 2,121343 нам уже известен.

Для поиска второго корня можно поступить двояко, используя пункт А или Б методических указаний ниже:

А. Изменим значение, например, в ячейке С12 (более близкое к ожидаемому корню). Выделим ячейку D12 и выполним команду Подбор параметра из меню Данные > Анализ "Что-если". Заполним поля запроса:

и после щелчка по кнопке ОК в ячейке С12 получим значение второго корня -2,12125:

Б. Построим график функции в интервале от -10 до 10.

Щелчком левой кнопки мыши на графике выделим ряд данных, содержащий маркер данных, близкий ко второму корню:

Сделаем активной ячейку D11 со значением функции, равным 9 и в меню Данные > Анализ "что-если" выберем Подбор параметра. Заполним поле Изменяя значение ячейки запроса:

и щелкнув по кнопке ОК, в ячейке С11 получим значение второго корня -2,1213207:

Следует обратить внимание, что значения корня, полученные в п.А и п.Б имеют несущественное отличие. Это вызвано следующим обстоятельством. По умолчанию команда Подбор параметра прекращает итерационные вычисления, когда выполняется 100 итераций, либо при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, можно изменить используемые по умолчанию параметры командой Параметры меню Файл > Формулы. Затем на вкладке Параметры вычислений в поле Предельное число итераций введите значение больше 100, а в поле Относительная погрешность – значение меньше 0,001.

Если Ms Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне запроса Результат подбора параметра и прервать вычисления, а затем нажать кнопку Шаг, чтобы просмотреть результаты каждой последовательной итерации. Когда Вы решаете задачу в пошаговом режиме, в этом окне запроса появляется кнопка Продолжить. Нажмите ее, когда решите вернуться в обычный режим подбора параметра.

Задание

Решить уравнение с использованием инструмента Excel Подбор параметра двумя способами:

подбором аргумента для конкретного значения функции;
посредством изменения графика функции.

Удостовериться с помощью построения графика в количестве корней уравнения. Определить все корни.

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

Суть функции

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

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

Для того, чтобы понять, как работает данная функция, лучше всего объяснить её суть на практическом примере. Мы будем объяснять работу инструмента на примере программы Microsoft Excel 2010, но алгоритм действий практически идентичен и в более поздних версиях этой программы, и в версии 2007 года.

Для того, чтобы запустить функцию, находясь во вкладке «Данные», жмем на кнопку «Анализ «что если»», которая расположена в блоке инструментов «Работа с данными» на ленте. Появляется меню, в котором нужно выбрать пункт «Подбор параметра…».

После этого, открывается окно подбора параметра. В поле «Установить в ячейке» нужно указать ее адрес, содержащей известные нам конечные данные, под которые мы будем подгонять расчет. В данном случае, это ячейка, где установлена премия работника Николаева. Адрес можно указать вручную, вбив его координаты в соответствующее поле. Если вы затрудняетесь, это сделать, или считаете неудобным, то просто кликните по нужной ячейке, и адрес будет вписан в поле.

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

Когда все данные окна параметров заполнены, жмем на кнопку «OK».

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

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

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

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

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».

Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».

Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Вы работаете с экспертами напрямую,
не переплачивая посредникам, поэтому
наши цены в 2-3 раза ниже

Последние размещенные задания

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

Решение задач, Управление персоналом

Срок сдачи к 23 нояб.

Выполнить все лабораторные работы

Срок сдачи к 12 дек.

Контрольная, Высшая математика

Срок сдачи к 30 нояб.

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

Решение задач, Управление персоналом

Срок сдачи к 23 нояб.

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

Решение задач, Управление персоналом

Срок сдачи к 23 нояб.

Другое, Физическая культура и спорт

Срок сдачи к 30 нояб.

Детские страхи младших школьников и пути их преодоления.

Срок сдачи к 30 нояб.

Лабораторная, Электрические цепи ЭПС

Срок сдачи к 28 нояб.

Решить один вариант контрольной работы

Контрольная, Английский язык

Срок сдачи к 22 нояб.

Заполнить 8 таблиц

Контрольная, Финансовый анализ и аудит

Срок сдачи к 28 нояб.

выполнить перевод и задания

Контрольная, Английский язык

Срок сдачи к 30 нояб.

Расчет подвижности, определение групп Ассура, построение траектории звеньев механизма

Лабораторная, детали машин

Срок сдачи к 24 нояб.

Вариант 22,сделать все !

Срок сдачи к 28 нояб.

Сделать модель стяжки

Курсовая, Работа в autocad

Срок сдачи к 29 нояб.

лабораторная работа 7 обработка результатов измерений в замкнутом.

Срок сдачи к 22 нояб.

Выполнить контрольную работу

Контрольная, инженерная и компьютерная графика

Срок сдачи к 30 нояб.

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

Контрольная, Технология механизированных работ

Срок сдачи к 30 нояб.

Отчет по практике, Автоматизация технологических процессов и производств

Срок сдачи к 1 дек.

Работа выполнена на отлично, а также , я учитываю то, что насчет оформления в Word я не го. Заказываю у исполнителя Антона работу 2-й раз, работа выполнена также четко и исправно, оф. Всё замечательно, досрочно, подвёл препод старичок, долго мурыжил с оценкой, не давал резу. Работа сделана досрочно в очень короткий срок. Без замечаний и доработок. Рекомендую. Спец. Работа выполнена на отлично, а также , я учитываю то, что насчет оформления в Word я не говорила, но когда я об этом исполнителю упомянула, он сделал оформление без всяческих замечаний, работой очень довольна, мне также еще остается доделать подобное в институт и я возьму пример с этом выполненной работы, так как, так, как ее выполнил исполнитель понятно мне, а на уроке мне было не понятно.. В общем исполнителя очень и очень рекомендую всем, и между прочим цена эта самая маленькая, какую я видела у исполнителей, и не смотря на такую цену работа сделана на оценку 10 и еще выше наверно, очень качественно выполнена! Данного исполнителя рекомендую всем на все 100 %! Заказываю у исполнителя Антона работу 2-й раз, работа выполнена также четко и исправно, оформление работы также сделано на отлично, замечаний никаких абсолютно нет, видно, человек свое дело знает на все 100%, и выполняет очень быстро, я рекомендую всем этого исполнителя. не пожалеете, если работу здесь закажите , и между прочим берет дешевле прочих Всё замечательно, досрочно, подвёл препод старичок, долго мурыжил с оценкой, не давал результат. А Адиля умничка, светлая голова, рекомендуюююю Работа сделана досрочно в очень короткий срок. Без замечаний и доработок. Рекомендую. Специалист своего дела.

обратился к нам
за последний год

работают с нашим сервисом

заданий и консультаций

выполнено и сдано
за прошедший год

Сайт бесплатно разошлёт задание экспертам.
А эксперты предложат цены. Это удобнее, чем
искать кого-то в Интернете

Отклик экспертов с первых минут

С нами работают более 15 000 проверенных экспертов с высшим образованием. Вы можете выбрать исполнителя уже через 15 минут после публикации заказа. Срок исполнения — от 1 часа

Цены ниже в 2-3 раза

Вы работаете с экспертами напрямую, поэтому цены
ниже, чем в агентствах

Доработки и консультации
– бесплатны

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

Гарантия возврата денег

Если эксперт не справится — мы вернем 100% стоимости

На связи 7 дней в неделю

Вы всегда можете к нам обратиться — и в выходные,
и в праздники

Эксперт получил деньги за заказ, а работу не выполнил?
Только не у нас!

Деньги хранятся на вашем балансе во время работы
над заданием и гарантийного срока

Гарантия возврата денег

В случае, если что-то пойдет не так, мы гарантируем
возврат полной уплаченой суммы

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

Поможем вам со сложной задачкой

С вами будут работать лучшие эксперты.
Они знают и понимают, как важно доводить
работу до конца

С нами с 2017
года

Помог студентам: 10 996 Сдано работ: 10 996
Рейтинг: 82 314
Среднее 4,94 из 5

С нами с 2018
года

Помог студентам: 7 634 Сдано работ: 7 634
Рейтинг: 69 528
Среднее 4,87 из 5

С нами с 2019
года

Помог студентам: 2 422 Сдано работ: 2 422
Рейтинг: 26 260
Среднее 4,84 из 5

Обращайте внимание на отзывы и рейтинг исполнителя

С нами с 2018
года

Помог студентам: 2 081 Сдано работ: 2 081
Рейтинг: 13 082
Среднее 4,87 из 5

1. Сколько стоит помощь?

Цена, как известно, зависит от объёма, сложности и срочности. Особенностью «Всё сдал!» является то, что все заказчики работают со экспертами напрямую (без посредников). Поэтому цены в 2-3 раза ниже.

Специалистам под силу выполнить как срочный заказ, так и сложный, требующий существенных временных затрат. Для каждой работы определяются оптимальные сроки. Например, помощь с курсовой работой – 5-7 дней. Сообщите нам ваши сроки, и мы выполним работу не позднее указанной даты. P.S.: наши эксперты всегда стараются выполнить работу раньше срока.

3. Выполняете ли вы срочные заказы?

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

4. Если потребуется доработка или дополнительная консультация, это бесплатно?

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

5. Я разместил заказ. Могу ли я не платить, если меня не устроит стоимость?

Да, конечно - оценка стоимости бесплатна и ни к чему вас не обязывает.

6. Каким способом можно произвести оплату?

Работу можно оплатить множеством способом: картой Visa / MasterCard, с баланса мобильного, в терминале, в салонах Евросеть / Связной, через Сбербанк и т.д.

7. Предоставляете ли вы гарантии на услуги?

На все виды услуг мы даем гарантию. Если эксперт не справится — мы вернём 100% суммы.

Читайте также: