Подбор параметра организация обратного расчета в excel
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
Видеолекции для
профессионалов
- Свидетельства для портфолио
- Вечный доступ за 120 рублей
- 311 видеолекции для каждого
Практическая работа № 11
Тема: ПОДБОР ПАРАМЕТРА. ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА
Цель: изучение технологии подбора параметра при обратных расчетах.
Задание 1. Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250 000 р. (на основании файла «Зарплата»).
Краткая справка: к исходным данным этой таблицы относятся значения Оклада и % Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.
Порядок работы
Запустите редактор электронных таблиц Microsoft Excel и откройте созданный ранее файл «Зарплата».
Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги ( Главная/Ячейки/Формат/Переместить или скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте скопированному листу имя «Подбор параметра».
Осуществите подбор параметра командой Данные/Работа с данными/Анализ «что-если»/ Подбор параметра (рис.1).
В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G 19), на второй строке наберите заданное значение 250 000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D 4), затем нажмите кнопку ОК. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки ОК (рис. 2).
Произойдет обратный пересчет % Премии. Результаты подбора на рис. 3:
если сумма к выдаче равна 250 000 р., то % Премии должен быть 203 %.
Рис. 1. Задание параметров подбора параметра
Рис. 2. Подтверждение результатов подбора параметра
Рис. 3. Подбор значения % Премии для заданной общей суммы заработной платы, равной 250 000 р.
Задание 2. Используя режим подбора параметра, определить штатное расписание фирмы. Исходные данные приведены на рис. 4.
Краткая справка: Известно, что в штате фирмы состоит:
8 младших менеджеров;
10 менеджеров;
3 заведующих отделами;
1 гл.бухгалтер;
1 программист;
1 системный аналитик;
1 генеральный директор фирмы.
Рис.4. Исходные данные для задания 2
Общий месячный фонд зарплаты составляет 100 000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.
Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = А i * x + B i , где x – оклад курьера; A i и B i - коэффициенты, показывающие:
A i - во сколько раз превышается значение x ;
B i - на сколько превышается значение x .
Порядок работы
Запустите редактор электронных таблиц Microsoft Excel.
Создайте таблицу штатного расписания фирмы по приведенному образцу (см. рис. 4). Введите исходные данные в рабочий лист электронной книги.
Выделите отдельную ячейку D 3 для зарплаты курьера (переменная « x ») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.
В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D 6 формула расчета имеет следующий вид: = B 6 * $ D $3 + C 6 (ячейка D 3 задана в виде абсолютной адресации). Далее скопируйте формулу из ячейки D 6 вниз по столбцу автокопированием.
В ячейке F 14 автосуммированием вычислите суммарный фонд заработной платы фирмы.
Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 100 000 р. Для этого осуществите команду Данные/Работа с данными/Анализ «что-если»/ Подбор параметра.
В поле Установить в ячейке появившегося окна введите ссылку на ячейку F 14, содержащую формулу расчета фонда заработной платы.
В поле Значение наберите искомый результат 100 000 р.
В поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D 3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК . Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 р.
Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.
Анализ задач показывает, что с помощью MS Excel можно решать линейные уравнения. Задания 1 и 2 показывают, что поиск значения параметра формулы – это не что иное, как численное решение уравнений. Другими словами, используя возможности программы MS Excel , можно решать любые уравнения с одной переменной.
Задание 3. Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 2), определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.
Порядок работы
Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициенты уравнений для расчета согласно табл. 1 (один из пяти вариантов расчетов).
Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100 000, 150 000, 200 000, 250 000, 300 000, 350 000, 400 000 р. Результаты подбора значений зарплат скопируйте в табл. 2 в виде специальной вставки.
Цель занятия : Изучение технологии подбора параметра при обратных расчетах.
Запустить 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
Ответ: Можно согласиться на процентную ставку 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:
- в ячейку – 2,9 %
- в ячейку В7 – 72 (6лет умножить на 12 месяцев)
- выберите команду Подбор параметров
- в поле Установить в ячейке введите В6 или щелкните на ячейке В6;
- в поле Значение введите число –1395
- в поле Изменения значения ячеек введите В3
- Введите новые данные на листе Ссуда_2 и выполните подбор параметров самостоятельно:
- Введите новые данные на листе Ссуда_3 и выполните подбор параметров самостоятельно:
- Сохраните файл Подбор параметра_1.xlsx
- Откройте файл Зарплата.xlsx
- Сохраните файл под именем Подбор параметра_2.xlsx
- На рабочем листе Зарплата октябрь используя режим подбора параметра, определить, при каком значении %Премии общая сумма заработной платы будет равна 250 000 р.
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
- Свидетельства для портфолио
- Вечный доступ за 120 рублей
- 311 видеолекции для каждого
Задание 1. (подбор параметра для вычисления размера ссуды)
Ссуда берется на 6 лет с процентной ставкой 2,9 % при условии, что сумма ежемесячных платежей не должна превышать 1395 руб. Каков максимальный размер ссуды?
Ответ: Максимальный размер ссуды 92085,41 руб.
ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ
Задание 2. (подбор параметра для вычисления срока погашения ссуды)
Каков срок погашения ссуды, если сумма ссуды равна 180000 руб., процентная ставка 1,7% годовых, а ежемесячные платежи равны 3250 руб.?
Ответ (проверьте себя): ссуда берется на 58 месяцев.
Задание 3. (подбор параметра для вычисления процентной ставки)
Ссуда в размере 130000 руб. берется на 5 лет с максимальными ежемесячными платежами 2390 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?
Ответ (проверьте себя): возможная процентная ставка 3,93%
Анализ результатов работы и формулировка выводов
В отчете необходимо предоставить: в своей папке файлы: Подбор параметра_1.xlsx, Подбор параметра_2.xlsx
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Видеолекции для
профессионалов
Выберите документ из архива для просмотра:
практика_Подбор параметра_Обеды (с ответом для учителя).xlsx
Выбранный для просмотра документ Практика _Подбор параметра_Обеды.doc
Цель: Отработать навыки использования функции «Подбор параметров»
Использование операции « Подбор параметров » в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов.
Задание . В школьной столовой на обеды школьникам скалькулированы карты на комплексный обед из четырех блюд (и сходные данные приведены на рис .). Известно, что на оплату комплексных обедов администрация города выделяет 155 0000 p . Технологу необходимо определить, сколько комплексных обедов можно приготовить на эту сумму.
Порядок работы:
Откройте файл с именем Практика_Подбор параметра_Обеды .
На листе Комплексный обед (задание) рассчитайте самостоятельно Стоимость 1 порции для каждого блюда комплексного обеда.
В ячейку Н37 временно введено число 1. В ячейке I 37 рассчитайте Общую стоимость комплексного обеда , включающего в себя по одной порции каждого блюда.
Используя режим подбора параметра, определите сколько можно приготовить комплексных обедов на выделенную сумму в 155 000р. Для этого
установите курсор в ячейку I 37;
в меню Сервис активизируйте команду Подбор параметра;
в поле Установить в ячейке появившегося окна должна появиться ссылка на ячейку I 37, содержащую формулу расчета Общей стоимости обедов ;
в поле Значение наберите искомый результат 155 000 ;
в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку Н37, в которой находится значение количества комплексных обедов, щелкните по кнопке ОК. Произойдет обратный расчет стоимости обедов на выделенную сумму в 155 000 р.
Составляя документ для отчетности, важно разместить в нем информацию с максимальной наглядностью.
Сохраните выполненную работу и п ригласите преподавателя для проверки (1623).
Выбранный для просмотра документ Презентация_Подбор параметра.ppt
«Как закрыть гештальт: практики и упражнения»
Свидетельство и скидка на обучение каждому участнику
Описание презентации по отдельным слайдам:
Цели урока: выполнять вычисления по формулам в электронной таблице, применять алгоритм выполнения обратных расчетов в электронной таблице. После изучения материала Вы научитесь:
Технолог общественного питания - это специалист по разработке, производству и контролю за качеством кулинарной и кондитерской продукции на предприятиях общественного питания.
Технолог общественного питания содержание труда определение качества продуктов; расчет их количества для получения готовых блюд; составление меню профессиональные качества аналитическое мышление; требовательность; чистоплотность; творческие и организаторские способности; высокая чувствительность к оттенкам запахов и вкусов; правильное цветоразличение хороший объемный и линейный глазомер тактильная чувствительность честность, порядочность и т.д.
Специфика профессии технолога Работа технолога общественного питания заключается не только в работе с продуктами. Деятельность технолога гораздо шире и ответственнее. Технолог: организует производство (размещает оборудование, обучает правилам пользования) составляет меню распределяет обязанности между поварами и контролирует их работу проверяет нормы выхода блюд продукции внедряет прогрессивные технологии в производство продукции отвечает за исправность кухонного оборудования и качество готовой пищи разрабатывает новые рецептуры, оформляя соответствующие нормативные документы изучает новые тенденции на рынке общепита и координирует работу в соответствии с ними предлагает новый ассортимент блюд с целью повышения спроса контролирует соблюдение санитарных норм составляет технологические карты новых блюд (расчет количества продуктов, калорийности и т.д.) осуществляет своевременное снабжение производства сырьем, инструментами, инвентарем и т.д. принимает участие в переподготовке и повышении квалификации производственных кадров с учетом требований современности.
изображениесодержание труда Прием сырья и его переработка; приготовление различных блюд и их оформление профессиональные качества Образная память; эстетический вкус; хорошо развитые обоняние и вкусовые ощущения
изображениесодержание труда Приготовление холодных закусок, первых, вторых и сладких блюд; изготовление кондитерских изделий из теста профессиональные качества Образная память; хорошо развитые обоняние и вкусовые ощущения, аккуратность
Личные качества высокая чувствительность к оттенкам запахов и вкусов правильное цветоразличение хороший объемный и линейный глазомер тактильная чувствительность хорошая память (кратковременная, долговременная, зрительная) высокий уровень распределения и переключения внимания чистоплотность эмоциональная устойчивость творческие способности эстетический вкус аккуратность требовательность коммуникабельность организаторские способности ответственность физическая выносливость честность, порядочность
Место работы рестораны кафе, кафетерии столовые бары мясоперерабатывающие комбинаты рыбоперерабатывающие заводы (консервные) молочные комбинаты пекарни кондитерские заготовочные фабрики.
Выполнение обратных расчетов
Выполнение обратных расчетов Использование операции «Подбор параметров» в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов.
Алгоритм выполнения обратного расчета подготовить таблицу и выполнить ВСЕ расчеты по формулам; в меню Сервис активизируем команду Подбор параметра; в поле Установить в ячейке указать параметр , т.е. ссылку на ячейку, содержащую конечную формулу расчета; в поле Значение вводим конкретное значение (искомый результат); в поле Изменяя значение ячейки вводим ссылку на изменяемую ячейку, от значений которой зависит конечная формула; щелкаем по кнопке ОК.
Практическая работа: Завтрак студента Выяснить сколько раз на свою стипендию студент сможет посетить столовую колледжа.
Рекомендации по выполнению практической работы: Откройте сетевой диск на вашем компьютере (2-ПК, 3-ПК, 4-ПК и т.д.). Скопируйте файл Практика_Подбор параметра_Обеды в свою личную папку. Переименуйте файл, добавив к его имени Вашу фамилию.
Практическая работа: Завтрак студента Выяснить сколько раз на свою стипендию студент сможет посетить столовую колледжа.
Задание. В школьной столовой на обеды школьникам скалькулированы карты на комплексный обед из четырех блюд (исходные данные приведены на рис.). Известно, что на оплату комплексных обедов администрация города выделяет 155 0000 p. Технологу необходимо определить, сколько комплексных обедов можно приготовить на эту сумму. Практическая работа: Завтрак школьника
Калькуляционные карты «Винегрет овощной» №100 «Солянка сборная мясная » № 227 «Поджарка с жареным картофелем» №562 №696 «Напиток апельсиновый»№ 1008
Задание. В школьной столовой на обеды школьникам скалькулированы карты на комплексный обед из четырех блюд (исходные данные приведены на рис.). Известно, что на оплату комплексных обедов администрация города выделяет 155 0000 p. Технологу необходимо определить, сколько комплексных обедов можно приготовить на эту сумму. Практическая работа: Завтрак школьника
Краткое описание документа:
Архив содержит презентацию, практическую работу, электронную таблицу для работы студентов, электронную таблицу с ответами для учителя).
На уроке решается проблемная ситуация. Задание . В школьной столовой на обеды школьникам скалькулированы карты на комплексный обед из четырех блюд (и сходные данные приведены на рис.). Известно, что на оплату комплексных обедов администрация города выделяет 155 0000 p . Технологу необходимо определить, сколько комплексных обедов можно приготовить на эту сумму.
Данный урок проводится в группах "поварской направленности" - это повара, кондитеры, технологи.
На этом уроке студенты знакомятся историей появления профессии технолога, спецификой работы и с оформлением калькуляционных карт, научатся выполнять обратный расчет с помощью команды "Подбор параметра".
В сетевую папку рекомендую заранее сбросить файл Практика_Подбор параметра_Обеды в котором подготовлены калькуляционные карты для расчетов. Также студентам необходимо раздать практическую работу на бумажном носителе.
Работа технолога общественного питания является первичной в процессе приготовления пищи и определяет качество еды в общепите, ее безопасность и вкусовые качества. Технолог, зная технологию производства продуктов питания, рецептуру блюд, закладку продуктов, технику безопасности приготовления пищи содействует тому, что сырье превращается в высококлассный продукт. От его добросовестности в определении качества исходных продуктов, соблюдении полноценной нормы их закладки зависит качество приготовленной еды, и, соответственно, престиж ресторана или столовой.
Первыми технологами по контролю за качеством приготовленной пищи можно считать людей, которые дегустировали королевскую еду. Им короли доверяли безоговорочно. В настоящее время профессия технолога общественного питания востребована как никогда ранее в связи с развитием и расширением сети предприятий общественного питания.
В России так называемые предприятия общественного питания в виде трактиров, корчм возникли в конце XVIII века наряду с возникновением кулинарии как науки. Контролировать качество пищи, приготовленной нанятыми людьми, приходилось хозяевам заведений. Таким образом возникла профессия технолога общественного питания.
Тема: Подбор параметра и организация обратного расчёта.
Цель: - изучение технологии подбора параметра при обратных расчетах.
Вид работы: фронтальный
Время выполнения: 2 часа
Задания к практической работе
Задание 1. Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250000 р. (на основании файла «Зарплата»).
Краткая справка. К исходным данным этой таблицы относятся значения Оклада и % Премии, одинакового для всех сотрудников. Результатом вычислений является ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов. Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.
Ход работы
1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный файл «Зарплата».
2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги. Не забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте скопированному листу имя «Подбор параметра».
3. Осуществите подбор параметра командой Данные – Анализ «что - если» - Подбор параметра (рис. 1).
Рисунок 1 - Задание параметров подбора параметра.
В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G19), на второй строке наберите заданное значение 250000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку OK. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки ОК (рис.2.).
Рисунок 2 - Подтверждение результатов подбора параметра.
Произойдет обратный пересчет % Премии. Результаты подбора (Рис. 3.):
Если сумма к выдаче равна 250000 р., то % Премии должен быть 203 %
Рисунок 3 - Подбор значения % Премии для заданной общей суммы заработной платы, равной 250000 р.
Задание 2. Используя режим подбора параметра, определить штатное расписания формы. Исходные данные на рис. 4.
Краткая справка. Известно, что в штате фирмы состоит:
8 младших менеджеров;
10 менеджеров;
3 заведующих отделами;
1 главный бухгалтер;
1 программист;
1 системный аналитик;
1 генеральный директор фирмы.
Общий месячный фонд зарплаты составляет 100000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.
Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = Ai*x+Bi, где x – оклад курьера; Ai и Bi – коэффициенты, показывающие:
Ai - во сколько раз превышает значение x;
Bi – на сколько превышается значение x.
Ход работы
1. Запустите редактор электронных таблиц Microsoft Excel.
2. Создайте таблицу штатного расписания фирмы по приведенному образцу (рис. 4). Введите исходные данные в рабочий лист электронной книги.
Рисунок 4 - Исходные данные для Задания 2.
3. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.
4. В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет следующий вид: = B6*$D$3 + C6 (ячейка D3 задана в виде абсолютной адресации). Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.
В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейки F6 формула расчета имеет вид = D6*E6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.
В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы.
5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 100000 р. Для этого в меню Данные активизируйте команду Подбор параметра.
В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы;
в поле Значение наберите искомый результат 100000;
в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000 р.
6. Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.
Анализ задач показывает, что с помощью MS Excel можно решать линейные уравнения. Задания 1 и 2 показывают, что поиск значения параметра формулы – это не что иное, как численное решение уравнений. Другими словами, используя возможности программы MS Excel, можно решить любые уравнения с одной переменной.
Задание 3. Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 2), определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.
Ход работы
1. Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициент уравнений для расчета согласно табл. 1. (один из пяти вариантов расчетов).
2. Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100000, 150000, 200000, 250000, 300000, 350000, 400000 р. Результаты подбора значений зарплаты скопируйте в табл. 2. в виде специальной вставки.
Рисунок 5 - Специальная вставка значений данных
Специальная вставка информации в виде значений позволяет копировать значения, полученные в результате расчетов, без дальнейшей их зависимости от пересчета формул.
Цель занятия : Изучение технологии подбора параметра при обратных расчетах.
Ход работы:
Запустить Microsoft Excel и создайте в своей папке файл Подбор параметра_1.xlsx
ЗАДАЧА 1. КРЕДИТ НА КВАРТИРУ
1. Оформите на Листе1 таблицу как показано на рисунке:
· Для расчета ежемесячного платежа используется функция =ПЛТ(Ставка;Кпер;Пс),
Ставка – ежемесячная процентная ставка по кредиту (в нашей формуле это В5/12)
Кпер – количество периодов (месяцев) погашения (В4)
Пс – сумма кредита (В3)
2. Создайте две копии для Листа 1
3. Эти три листа назовите соответственно: Кредит_1, Кредит_2, Кредит_3
Задание 1. (подбор параметра для вычисления суммы кредита)
Кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11000 руб. Какова максимальная сумма кредита?
1. Введите новые данные на листе Кредит_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
Ответ: Можно согласиться на процентную ставку 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, Ссуда_2, Ссуда_3
Задание 1. (подбор параметра для вычисления размера ссуды)
Ссуда берется на 6 лет с процентной ставкой 2,9 % при условии, что сумма ежемесячных платежей не должна превышать 1395 руб. Каков максимальный размер ссуды?
1. Введите новые данные на листе Ссуда_1:
· в ячейку – 2,9 %
· в ячейку В7 – 72 (6лет умножить на 12 месяцев)
2. выберите команду Подбор параметров
· в поле Установить в ячейке введите В6 или щелкните на ячейке В6;
· в поле Значение введите число –1395
· в поле Изменения значения ячеек введите В3
Ответ: Максимальный размер ссуды 92085,41 руб.
ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ
Задание 2. (подбор параметра для вычисления срока погашения ссуды)
Каков срок погашения ссуды, если сумма ссуды равна 180000 руб., процентная ставка 1,7% годовых, а ежемесячные платежи равны 3250 руб.?
1. Введите новые данные на листе Ссуда_2 и выполните подбор параметров самостоятельно:
Ответ (проверьте себя): ссуда берется на 58 месяцев.
Задание 3. (подбор параметра для вычисления процентной ставки)
Ссуда в размере 130000 руб. берется на 5 лет с максимальными ежемесячными платежами 2390 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?
1. Введите новые данные на листе Ссуда_3 и выполните подбор параметров самостоятельно:
Ответ (проверьте себя): возможная процентная ставка 3,93%
2. Сохраните файл Подбор параметра_1.xlsx
3. Откройте файл Зарплата.xlsx
4. Сохраните файл под именем Подбор параметра_2.xlsx
5. На рабочем листе Зарплата октябрь используя режим подбора параметра, определить, при каком значении %Премии общая сумма заработной платы будет равна 250 000 р.
Анализ результатов работы и формулировка выводов
В отчете необходимо предоставить: в своей папке файлы: Подбор параметра_1.xlsx, Подбор параметра_2.xlsx
Читайте также: