ИНДИВИДУАЛЬНЫЙ ИТОГОВЫЙ ПРОЕКТ

Государственное автономное профессиональное образовательное учреждение Архангельской области

«Архангельский торгово-экономический колледж»

(ГАПОУ АО «АТЭК»)

ИНДИВИДУАЛЬНЫЙ ИТОГОВЫЙ ПРОЕКТ

Применением функции подбора параметра в Microsoft Office Excel. Решение задачи анализа прибыли парикмахерского салона по дисциплине информатика ОУД.12

Обучающейся 1 курса специальности парикмахер по очной форме

Варфаламеевой Светланы Леонидовны _____________________________

(подпись)

«___» ______________2018 г.

Архангельск 2018

ВВЕДЕНИЕ

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

С помощью Ехсе1 можно создавать самые различные документы, выполнять самые различные задачи, например:

• • оперативно выполнять вычисления различной сложности;

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

    решать сложные финансовые, экономические и математические задачи и, в том числе, задачи статистического анализа и т.д.

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

Очень полезной функцией в программе Microsoft Exel является подбор параметра (это часть блока задач инструмента «Анализ «Что - Если») - он позволяет решать задачи, которые имеют точное целевое значение, зависящее от одного неизвестного параметра. С помощью подбора параметра можно определить значение, которое будет давать желаемый результат.

Цель работы: Показать возможность применение функции подбора параметра в Microsoft Office Excel при решении прикладных задач

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

1 ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

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

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

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

Рис.1 Изменение процентной ставки

Например, средство «Подбор параметра» используется для изменения процентной ставки в ячейке B3 в сторону увеличения до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00р. (Рис.1)

1.2 Поиск параметра

Можно рассмотреть процедуру поиска параметра на простом примере: нужно решить уравнение 10 * x - 10 / x = 15. Здесь параметр (аргумент) - x. Пусть это будет ячейка A3. Нужно ввести в эту ячейку любое число, лежащее в области определения функции (в данном примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Нужно ввести формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Подбор параметра в меню Сервис. Вводимые параметры поиска:

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

Ввести искомый результат в поле Значение.

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

Кликнуть на клавише OK.

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

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

Задачу поиска параметра при налагаемых граничных условиях поможет решить специальная надстройка Microsoft Excel Поиск решения.

2 ПРАКТИЧЕСКАЯ ЧАСТЬ

2.1 Пример решения задач с использованием функции “подбор параметра”

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

Рис.2 Подбор параметра

Если в качестве начального значения в данном примере указать -3, тогда будет найдено второе решение уравнения: -0,5.

2.2 Задача: Анализ суммы выплат по вкладу

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

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

Нужно создать новой лист с именем Вклад.

В ячейку В4 ввести текст Размер вклада, а в С4 его значение 150000р.

В ячейку В6 ввести текст Срок вклада, а в С6 его значение 20.

В ячейку В8 ввести текст Процентная ставка, а в С8 его значение 5%.

В ячейку В10 ввести текст Коэффициент наращения, а в С10 формулу его вычисления =(1+С8)^С6.

В ячейку В13 ввести текст Сумма выплат, а в С13 формулу его вычисления =С10*С4.

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

Рис. 3 Анализ суммы выплат по вкладу

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

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

В поле Значение нужно ввести целевое значение 500 000, а в поле Изменяя значение ячейки ссылку на ячейку С4 и нажать ОК.

Рис.4 Окно с результатами расчета

Появится окно с результатами расчета, которые после нажатия кнопки ОК будут внесены в таблицу. Рисунок 4. Как видно для получения суммы выплат в 500 000 руб. при 5% годовых за 20 лет требуется положить 188445 руб. Результат выполнения задачи показан на рисунке 5

Рис. 5 Результат выполнения задачи

2.3 Задача: Расчет размера пенсионных накоплений

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

Использование средства подбора параметра

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

Рис. 6 Таблица для расчета размера пенсионных накоплений

В этой таблице указаны возраст, начиная с которого в пенсионный фонд вносятся платежи (А2), величина ежемесячного взноса (В2), период отчислений, рассчитанный по формуле

=60-А2

то есть предполагается, что речь идет о мужчине, который выйдет на пенсию в 60 лет (С2), а также величина процентной ставки (D2).

Сумма накоплений рассчитывается с помощью функции по следующей формуле:

=БС(D2;C2;-B2*12; 0;1)

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

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

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

Формула имеет такой вид, так как предполагается, что проценты начисляются не ежемесячно, а в начале, следующего года за предыдущий год. Допустим, необходимо определить, в каком возрасте будущему пенсионеру надо начинать выплаты, чтобы потом получить прибавку к пенсии в размере 1000 руб. Для этого следует выделить ячейку, отведенную для представления результата (в данном случае F2), и вызвать команду “Сервис » Подбор параметра”. Когда появится диалоговое окно Подбор параметра (рис. 7), адрес выделенной ячейки будет автоматически вставлен в поле “Установить в ячейке”. Нужно указать в поле “Значение” целевое значение - 1000. Нужно поместить курсор ввода в поле “Изменяя значение ячейки” и выделить ячейку А2, после чего ее адрес отобразится в указанном поле.

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

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

После выполнения всех установок нужно нажать кнопку К, и поиск нужного значения будет начат. Результат вычисления отобразится в диалоговом окне “Результат подбора параметра”, а также в исходной таблице (рис, 8). После нажатия кнопки 0К полученные значения будут вставлены в таблицу.

Рис. 8 - Результаты подбора параметра

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

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

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

2. Вызвать команду “Сервис » Подбор параметра”. В поле “Установить в ячейке” появившегося диалогового окна будет отображаться адрес целевой ячейки.

3. Задать в поле “Значение” значение, которое должна содержать целевая ячейка

4. Указать в поле “Изменяя значение ячейки” адрес ячейки, значение которой необходимо установить таким, чтобы в целевой ячейке получить заданное значение.

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

6. Нажать кнопку ОК, если вы хотите заменить значения в ячейках на рабочем листе новыми, или кнопку “Отмена” в противном случае.

2.4 Анализ прибыли парикмахерского салона

Решим задачу «анализ прибыли» с помощью Microsoft Office Excel 2007.

Задача: с помощью функции «подбор параметра» определить, при каком количестве клиентов прибыль парикмахерского салона достигнет 1000000 рублей?

Решение:

Создадим таблицу по образцу:

Произведем расчеты в таблице:

Найдем доход. Доход = средняя цена услуги*количество клиентов (в ячейке В3 введем формулу =В1*В2).

Найдем расходы. Расход = доход*35/100 (35% от дохода, в ячейке В4 введем формулу =В3*35/100).

Найдем налог. Налог = доход*13/100 (13% от дохода, в ячейке В5 введем формулу =В3*13/100).

Найдем прибыль. Прибыль = доход-расход-налог (в ячейке В6 введем формулу =В3-В4-В5).

Установим курсор на целевой ячейке В6

Данные - Анализ «что-если» - Подбор параметра:

5. Заполним диалоговое окно и нажмем ОК.

6. Решение данной задачи видим на рисунке 9.

Рис. 9 - Результаты подбора параметра. Решение задачи «анализ прибыли»

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

ЗАКЛЮЧЕНИЕ

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

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

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

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

СПИСОК ИСПОЛЬЗУЕМЫХ ИСТОЧНИКОВ

Кашаев Офисные решения с использованием Microsoft Excel 2007 и VBA / Кашаев, Сергец. - М.: Питер, 2015. - 352 c.

Корнеев, В. Н. Microsoft Excel 2007. Просто о сложном / В.Н. Корнеев, А.В. Куприянова. - М.: Наука и техника, 2016. - 176 c.

Курбатова, Екатерина Анатольевна Microsoft Office Excel 2007. Самоучитель / Курбатова Екатерина Анатольевна. - Москва: Наука, 2014. - 454 c.

Сергеев, А. П. Использование Microsoft Office Excel 2007 / А.П. Сергеев. - Москва: СПб. [и др.] : Питер, 2017. - 288 c.

Юдин, М.В. Microsoft Excel 2007. Компьютерная шпаргалка / М.В. Юдин, А.В. Куприянова. - М.: СПб: Наука и Техника, 2016. - 237 c.

Подбор параметра в Exel и примеры его использования [Электронный ресурс]. – Режим доступа: http://exceltable.com/otchety/podbor-parametra (Дата обращения: 19.12.2017).

Использование средства подбора параметра для получения требуемого результата путем изменения входного значения [Электронный ресурс]. – Режим доступа: https://support.office.com/ru-ru/article (Дата обращения: 16.01.2018).

Введение в анализ «что-если» [Электронный ресурс]. – Режим доступа: http://office-guru.ru/excel/analiz-chto-esli-excel-140.html (Дата обращения: 23.03.2018).

Табличный процессор Exel. Подбор параметра. [Электронный ресурс]. – Режим доступа: http://www.yaklass.ru/materiali?mode=cht&chtid=507 (Дата обращения: 02.04.2018).