Исследование (прогнозирование) задач в Excel
Задание:
• Решить и провести исследование (прогнозирование) упрощенной задачи расчета прибыли при реализации определенного количества изделий с определенной величиной наценки к закупочной цене.
• Создать таблицу решения задачи, присвоить имена ячейкам для ввода конечной формулы.
• Создать таблицы данных при изменении одной и двух величин, пересчитать таблицу данных при новых условиях.
• Осуществить подбор параметра для поиска значения исходного параметра, обеспечивающего заданный конечный результат.
Создание таблицы.
1.1 В ячейку А1 введите заголовок: Закупка, в ячейку D1 введите заголовок: Реализация. Установите в первой строке Размер шрифта - 16, начертание - Полужирный.
1.2 Установите обрамление двойной линией нижней границы строки и осуществите автоподбор ширины столбцов А и D (выделить А и D; Формат - Столбцы).
1.3 В ячейки А2, A3 и А4 соответственно введите наименования: Количество; Цена; Стоимость.
1.4 В ячейки D2, D3, D4, D5 и D7 соответственно введите наименования: Наценка, Доход, Расход, Зарплата, Прибыль.
1.5 В ячейку В2 введите число 30, а в ячейку Е2 - 0,15. Сделайте ячейку ВЗ активной.
1.6 В Строке формул щелкните мышью знак равенства - строка готова к вводу формулы. В ячейки рабочего листа введите следующие формулы:
В ячейку ВЗ - 10000*В2/(В2-5)
В ячейку В4 - В2*ВЗ
В ячейку ЕЗ - В4*Е2
В ячейку Е4 - В4/(150-В2)
В ячейку Е5 - 1000*В2
Убедитесь, что в этих ячейках возникли результаты вычислений соответственно: 12 000, 360 000, 54 000, 3000, 30 000.
Присвоение имени ячейке.
2.1 1-й способ. Для присвоения имени ячейке ЕЗ сделайте ее активной, щелкните мышью в поле имен ячеек в строке формул, нажмите клавишу <Backspace>, наберите имя ячейки: Доход и нажмите клавишу <Enter>. В поле имен адрес заменится именем ячейки.
2.2 2-й способ. Для присвоения имени ячейке Е4 сделайте ее активной, выполните команду меню Вставка - Имя -Присвоить, в окне диалога Присвоение имени убедитесь, что в поле имени находится имя Расход, взятое из смежной ячейки слева от активной, и щелкните кнопку ОК. В поле имен адрес заменится именем ячейки.
2.3 Любым из способов присвойте имя Зарплата ячейке Е5.
3. Ввод формулы с использованием имен ячеек.
3.1 Сделайте активной ячейку Е7 и наберите в ней знак равенства <=>.
3.2 Установите курсор на ячейке ЕЗ с именем Доход, и название этой ячейки скопируется в ячейку Е7.
3.3 Наберите арифметический знак минус и установите курсор на ячейку Е4 с именем Расход. Это имя введется в ячейку Е7.
3.4 Повторите действия, описанные в п. 3.3, для ввода в формулу имени ячейки Зарплата и завершите ввод формулы, нажав кнопку ОК. В ячейке Е7 возникнет результат - 21 000. Сделайте активной ячейку Е7 и убедитесь, что в Строке формул расположена формула, использующая не ссылки, а имена ячеек. Этим обеспечивается использование в формуле абсолютной адресации ячеек (при копировании формулы адреса ячеек не меняются).
3.5 Выполните команду меню Сервис - Параметры, щелкните вкладку Вычисления и убедитесь, что для режима Вычисления установлен переключатель Автоматически. При необходимости установите его и щелкните кнопку ОК.
3.6 Для проверки правильности решения задачи программой Excel 2000 введите в ячейку В2 число 50 и убедитесь, что программа произвела пересчет по формулам с новыми данными и в ячейке Е7 возник результат 27777,7.... Таким образом, можно прогнозировать результат при изменении любого параметра, входящего в расчеты.