Р параметра в ms Excel

14.Подбор параметра в ms Excel.

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

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

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

15. Поиск решения в ms Excel.

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

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

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

16.Диаграммы в ms Excel, построение тренда.

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

графическое представление данных;

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

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

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

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

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

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

Если возникает новая опорная точка, происходит перестроение линии тренда по новому максимуму или минимуму.

Практическая работа № 6

Цель занятия : Изучение технологии подбора параметра при обратных расчетах.

Запустить Microsoft Excel и создайте в своей папке файл Подбор параметра_1.xlsx

ЗАДАЧА 1. КРЕДИТ НА КВАРТИРУ

1. Оформите на Листе1 таблицу как показано на рисунке:

Для расчета ежемесячного платежа используется функция =ПЛТ(Ставка;Кпер;Пс),

Ставка – ежемесячная процентная ставка по кредиту (в нашей формуле это В5/12)

Кпер – количество периодов (месяцев) погашения (В4)

Пс – сумма кредита (В3)

2. Создайте две копии для Листа 1

3. Эти три листа назовите соответственно: Кредит_1, Кредит_2, Кредит_3

Задание 1. (подбор параметра для вычисления суммы кредита)

Кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11000 руб. Какова максимальная сумма кредита?

Введите новые данные на листе Кредит_1 :

в ячейку В4 – число 180 (15 лет, умноженных на 12 месяцев)
в ячейку В5 – 5,75%
перейдите на страницу Ленты – Данные

В разделе Работа с данными разверните кнопку Анализ «что-если» и выберите команду Подбор параметра
откроется диалоговое окно Подбор параметров в котором:

в поле Установить в ячейке введите В6 или щелкните на ячейке В6;
в поле Значение введите число –11000 (число должно быть отрицательно, что указывает на то, что ежемесячный платеж заемщик отдает, а не получает)
в поле Изменения значения ячеек введите В3 или щелкните по ячейке В3

Ответ: Максимальная сумма кредита 1 324 647 руб.

Задание 2. (подбор параметра для вычисления процентной ставки)

Кредит в размере 850 000 руб. берется на 30 лет с максимальными ежемесячными платежами 5000 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

1. Перейдите на лист Кредит_2 и введите новые данные:

в ячейку В3 – число 850000
в ячейке В4 – 360

2. Выберите команду Подбор параметров (см. задание 1):

в поле Установить в ячейке введите – В6 или щелкните на ячейке В6;
в поле Значение введите число –5000
в поле Изменения значения ячеек введите В5

Читать еще: Хорошая и простая программа для раздачи WIFI с компьютера!

Ответ: Можно согласиться на процентную ставку 5,82 %.

Задание 3. (подбор параметра для вычисления срока погашения кредита)

Каков срок погашения кредита, если сумма кредита равна
2 250 000 руб., процентная ставка составляет 7% годовых, а ежемесячные платежи равны 14 230 руб.?

1. Перейдите на лист Кредит_3 и введите новые данные:

в ячейку В3 число 2250000
в ячейке В5 – 7 %

выберите команду Подбор параметров (см. предыдущее задание)
в поле Установить в ячейке введите – В6 или щелкните на ячейке В6;
в поле Значение введите число –14230

2. В поле Изменения значения ячеек введите В4

Ответ: Срок погашения кредита 439 месяцев (примерно 36,6 лет)

ЗАДАЧА 2. ССУДА НА ПОКУПКУ МАШИНЫ

Оформите на свободном листе таблицу как показано на рисунке

Создайте две копии созданного листа
Новые листы назовите соответственно Ссуда_1, Ссуда_2, Ссуда_3

Задание 1. (подбор параметра для вычисления размера ссуды)

Ссуда берется на 6 лет с процентной ставкой 2,9 % при условии, что сумма ежемесячных платежей не должна превышать 1395 руб. Каков максимальный размер ссуды?

Введите новые данные на листе Ссуда_1:

в ячейку – 2,9 %
в ячейку В7 – 72 (6лет умножить на 12 месяцев)

выберите команду Подбор параметров

в поле Установить в ячейке введите В6 или щелкните на ячейке В6;
в поле Значение введите число –1395
в поле Изменения значения ячеек введите В3

Ответ: Максимальный размер ссуды 92085,41 руб.

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ

Задание 2. (подбор параметра для вычисления срока погашения ссуды)

Каков срок погашения ссуды, если сумма ссуды равна 180000 руб., процентная ставка 1,7% годовых, а ежемесячные платежи равны 3250 руб.?

Введите новые данные на листе Ссуда_2 и выполните подбор параметров самостоятельно:

Ответ (проверьте себя): ссуда берется на 58 месяцев.

Задание 3. (подбор параметра для вычисления процентной ставки)

Ссуда в размере 130000 руб. берется на 5 лет с максимальными ежемесячными платежами 2390 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

Введите новые данные на листе Ссуда_3 и выполните подбор параметров самостоятельно:

Ответ (проверьте себя): возможная процентная ставка 3,93%

Сохраните файл Подбор параметра_1.xlsx
Откройте файл Зарплата.xlsx
Сохраните файл под именем Подбор параметра_2.xlsx
На рабочем листе Зарплата октябрь используя режим подбора параметра, определить, при каком значении %Премии общая сумма заработной платы будет равна 250 000 р.

Анализ результатов работы и формулировка выводов

В отчете необходимо предоставить: в своей папке файлы: Подбор параметра_1.xlsx, Подбор параметра_2.xlsx

Как работает функция «Подбор параметра» в Excel

Применение инструмента Подбор параметра

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

Конечно, менеджер может провести анализ возможностей предприятия и ответить на несколько вопросов.

1) Что будет, если потребуется приобретать не 15, а 19 единиц оборудования?

2) Как изменится платеж по кредиту, если платежи осуществлять в начале периода?

3) Какие возможности у компании есть, если стоимость единицы оборудования будет колебаться в некоторых пределах?

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

1) Снизить размер кредита за счет уменьшения количества закупаемого оборудования.

2) Продлить срок выплаты кредитной задолженности.

3) Добиться снижения кредитной ставки.

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

· Вызвать надстройку Подбор параметра. Сервис-Подбор параметра. В Excel 2007 (Данные-Анализ «что-если»-Подбор параметра)

· Ввести в диалоговое окно условия, которые удовлетворяют заемщика, как это показано на рис. 1.

· Проанализировать результат, который отображается на диалоговом окне, представленном на рис. 2.

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

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

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

Вопросы для самопроверки

1. Какое основное правило следует помнить при использовании инструмента Подбор параметра?

2. Как вызвать инструменты Подбор параметра на экран пользователя?

3. Для чего используют строку «Значение:» в окне Подбор параметра?

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

Подбор параметра в Excel. Функция «Подбор параметра» — Информатика

Подбор параметра в Excel. Функция «Подбор параметра» — Разбираем подробно

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

Принцип работы функции

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

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

Особенности применения функции: пошаговый обзор с объяснением на примере карточки товаров

Чтобы рассказать подробнее о том, как работает «Подбор параметра», воспользуемся программой Microsoft Excel 2016 года. Если у вас установлена более поздняя или ранняя версия приложения, в таком случае могут незначительно отличаться лишь некоторые этапы, при этом принцип действия остается таким же.

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

Когда появилось всплывающее окошко, в поле «Установить в ячейке» прописываем нужный адрес ячейки. В нашем случае это сумма скидки. Чтобы долго не прописывать его и периодически не менять раскладку клавиатуры, делаем клик по нужной ячейке. Значение автоматически отобразится в нужном поле. Напротив поля «Значение» указываем сумму скидки (300 рублей).

Важно! Окно «Подбор параметра» не работает без установленного значения.

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

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

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

Решение уравнения с помощью подбора параметров

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

У нас есть уравнение: х+16=32. Необходимо понять, какое число прячется за неизвестным «х». Соответственно, будем находить его с помощью функции «Подбор параметра». Для начала прописываем в ячейку наше уравнение, предварительно поставив знак «=». Причем вместо «х» устанавливаем адрес ячейки, в которой появится неизвестное. В конце введенной формулы знак равенства не ставим, иначе у нас отобразиться «ЛОЖЬ» в ячейке.

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

В появившемся окне в поле «Установить значение» прописываем адрес той ячейки, в которой у нас указано уравнение. То есть это окошко «К22». В поле «Значение», в свою очередь, прописываем число, которому равно уравнение – 32. В поле «Изменяя значение ячейки» вводим адрес, куда будет вписываться неизвестное. Подтверждаем свое действие нажатием на кнопку «ОК».

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

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

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

Подведем итоги

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

Решение задач в Excel с помощью средств «Поиск решения» и «Подбор параметра»

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

Основные надстройки, поставляемые вместе с пакетом Excel:

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

– Мастер суммирования. Позволяет автоматизировать создание формул для суммирования данных в столбце таблицы и использовать частичные суммы.

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

– Поиск решения. Используется для решения уравнений и задач оптимизации.

Средство Поиск решения.Запускается командой Данные → Анализ → Поиск решения. Элементы диалогового окна:

установить целевую ячейку –адрес ячейки с целевой функцией;

равной – значение, к которому стремиться целевая функция;

изменяя ячейки –адреса влияющих ячеек;

параметры –открывает окно для задания ограничений на значения влияющих ячеек.

Средство Подбор параметра.Запускается командой Данные → Работа с данными → Анализ «что-если» → Подбор параметра.

Задание 5.12. Решить систему нелинейных уравнений с помощью средства Поиск решения.

(1)

Выполнение.

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

(2)

Вместо системы (1) будем решать уравнение (2). Решений будет два.

Чтобы применить метод Поиск решения необходимо, предварительно, найти начальное приближение решений. Для этого построим таблицу значений левой части уравнения (2) по переменным х и у на интервале (– 1.7; +1.7) с шагом 0.3. Границы интервала взяты на основании того, что корни уравнения лежат внутри круга, радиус которого приблизительно равен =1.73.

Для построения таблицы выполняем:

1. В ячейки А2:А14 вводим значения х (в интервале [–1.7, 1.7]), а в ячейки В1:N1 – значения y в таком же интервале.

2. В ячейку В2 вводится формула =($A2^2+B$1^2-3)^2+(2*$A2+3*B$1-1)^2 –уравнение (2).

3. Копируем формулу ячейки B2 вдиапозон B2:N14.

В соответствии с формулой (2) за начальные значения х и y берутся значения в тех ячейках заполненного диапазона, где функция принимает наименьшие значения. Под значения первого корня отводим ячейки А16:В16, а А17:В17 – под значения второго корня.

Для системы (1), в соответствии с полученной таблицей первое минимальное значение 0,4325. В ячейку А16мы вводим 1.3 – значение x, в В16 – 1.4 – значение y. В ячейку С16 вводим формулу =(А16^2+В16^2-3)^2+(2*A16+3*B16-1)^2.

Открываем окно Поиска решенийи устанавливаем: Целевая ячейка – $C16; Изменяя ячейки – $A16:$B16; установить параметр – Минимальному значению. Нажимаем кнопку Выполнить.

Значение корней уравнения появятся в ячейках А16 и В16. Второй корень находим аналогично, взяв следующее наименьшее значение 0,08.

Задание 5.13. Найти корни кубического уравнения (полинома) с одним неизвестным с помощью средства Подбор параметра.

Выполнение.

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

1. Введем в ячейку A2 значение –1 , а в A3 – значение: – 0.8.

2. Используя маркер заполнения, заполним ячейки до А12.

3. В ячейку B2 вводим формулу: = A2^3 – 0,01*A2^2 – 0,7044*A2 + 0,139104.

4. Заполняем диапазон B3:B12.

5. По полученным значениям строим график заданного полинома.

Мы увидим, что для нашего случая полином меняет знак на интервалах [-1,-0.8], [0.2, 0.4] и [0.6, 0.8], т.е. пересекается с осью x. Интервалов три – столько корней имеет уравнение третьей степени. Корни локализованы.

Теперь зададим точность нахождения значений корней. На вкладке Office → Параметры Excel → Формулы → Параметры вычислений задаем относительную погрешность 0,00001 и предельное число итераций 1000 (число последовательных приближений).

Отводим на новом рабочем листе ячейку С2 под первый корень, соответственно ячейки C3 и C4 под второй и третий корни полинома.

Корни будем находим методом последовательных приближений. Поэтому в ячейку С2 вводим сначала значение, являющееся первым приближением к искомому корню. В нашем случае возьмем первый отрезок и в нем среднее значение, т.е. – 0,9. Соответственно в ячейки С3 и С4 вводим приближенные значения для второго и третьего корней: +0,3 и +0,7.

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

= C2^3 – 0,01*C2^2 – 0,7044*C2 + 0.139104.

Копируем эту формулу в ячейки D3 и D4 для второго и третьего корней полинома. С помощью инструмента Подбор параметранаходим первый корень:

1. Выбираем команду Данные → Работа с данными → Анализ «что-если» → Подбор параметра. На экране появится диалоговое окно.

2. В поле Установить в ячейке введем ссылку на ячейку D2, в которой введена формула, вычисляющая значение левой части полинома.

3. В поле Значение вводим – значение из правой части уравнения.

4. В поле Изменяя значение ячейки введем С2 –ссылка на ячейку, отведенную под первый корень.

5. Нажимаем ОК.

Получим окно с результатами:

Закрыв окно, найденное приближенное значение корня помещается в ячейку D2. В данном случае оно равно –0,92034.

Аналогично, повторив действия 1–5 для каждого из оставшихся корней, в ячейках D3 и D4 находим их значения. Соответственно, они равны 0,21021 и 0,72071.

Дата добавления: 2016-09-26 ; просмотров: 7603 ; ЗАКАЗАТЬ НАПИСАНИЕ РАБОТЫ

голоса

Рейтинг статьи