Анализ данных с помощью команд Подбор параметра и Поиск решения

§ выбрать ячейку, содержащую формулу (функцию искомого аргумента),

§ выбрать вкладку Данные, панель инструментов Работа с данными → Анализ «что если» →Подбор параметра и в диалоговом окне Подбор параметра задать

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

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

Для установки предельного числа итераций и относительной погрешности вычислений следует использовать вкладку Вычисления диалогового окна команды Сервис→Параметры. По умолчанию Excel предлагает предельное число итераций – 1000 и относительную погрешность – 0,001.

Задания выполняйте на отдельных листах созданной Вами книги.

Задание 1. Табулирование функций и построение графиков функций

Задание 2. Построение трехмерных графиков

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

Задание 3. Работа с массивами. Решение системы линейных алгебраических уравнений (СЛАУ)

СЛАУ в матричной форме имеет вид Ax = b, где A – матрица коэффициентов, x – вектор неизвестных, b ‑ вектор свободных членов:

Решение системы уравнений в матричном виде: x = A -1 b,

Где A -1 –матрица,обратная к матрицеА. Для проведения вычислений следует:

§ построить обратную матрицу (выделить диапазон ячеек для хранения обратной матрицы (размер диапазона должен совпадать с размером массива А) и вызвать функцию МОБР. Для запуска функции следует воспользоваться комбинацией клавиш [Ctrl+Shift+Enter];

§ выделить массив для результата и вызвать функцию МУМНОЖ;

§ в диалоговом окне задать исходные массивы ‑ диапазоны ячеек с матрицей A -1 и вектором b.Для запуска функции также следует нажать [Ctrl+Shift+Enter].

Создайте новый лист с именем СЛАУ и выполните на нем вычисление заданной системы матричным методом.

Задание 5. Подбор параметра при выполнении финансовых расчетов

Методом подбора параметра рассчитать, при какой ежемесячной процентной ставке С можно за год накопить S рублей, внося каждый месяц платеж на n % больше предыдущего, начав с первого платежа P рублей.

Слева представлено заполнение таблицы для расчета накопления за год при процентной ставке C=12% годовых, n=10% и P=100 руб.

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

Синтаксис: БС(ставка; кпер; плата; нз; тип),

§ ставка – процентная ставка или скидка по вложению или ссуде за период выплат,

§ кпер – общее количество платежей или периодов выплат (это значение в примере уменьшается на 1 каждый месяц),

§ плата– объем периодической выплаты по вложению или ссуде (в примере =0),

§ нз – общая сумма, которую составят будущие платежи, начиная с текущего момента (в примере это сумма, которую следует вернуть (=0), минус сумма начального вложения);

§ тип – режим выплат, с которым осуществляются выплаты (значение 0 соответствует выплатам в конце месяца, значение 1 ‑ в начале месяца).

Для расчета накопления, например 7000 рублей, вызывается команда Подбор параметра для формулы в ячейке Е17 (общая сумма выплаты) и задается изменяемая ячейка D2 (ежемесячная процентная ставка).

Выполните расчеты для заданных преподавателем значений S, n, P и C.

1. Какие типы диаграмм Excel можно применять для построения графиков функций?

2. Чем различаются диаграммы типа График и Точечная?

3. Как метод работы с массивами применяется для решения системы линейных уравнений?

4. Следует ли учитывать работу с массивами при решении СЛАУ методом Крамера?

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

7. В каких случаях решается задача линейного программирования?

8. Как применить команду Поиск решения для решения задачи оптимизации?

9. Какая из команд – Поиск решения или Подбор параметра – может быть применена для решения нелинейного уравнения?

Маржинальная прибыль от всего объема выпуска будет рассчитана по формуле:
35x + 66y
Наша цель: найти такой объем выпуска, который даст максимальное значение

Поиск решений в Excel для экономиста

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