2.1.Вопросы для самоподготовки
Каким образом выполняется присвоение имен ячеек?
Что означает кнопка панели инструментов Форма?
Что означает кнопка панели инструментов Форма?
Каким образом присваиваются свойства элементам управления?
Как сделать, чтобы шаг изменения принимал не целочисленное значение?
Как с помощью линеек прокруток можно достичь пересечения прямых "Выручка" и "Затраты" при заданном количестве изделий?
3.Подбор парметров16
Зачастую Вы знаете тот результат, который нужно получить с помощью вычисления по формуле, однако, входное значение, необходимое для получения этого результата, Вам не известно. Перебор возможных значений этого параметра (подгонка результата) достаточно утомительное и трудоемкое занятие.
Чтобы решить эту задачу, целесообразно воспользоваться заложенным в Excel средством Подбора параметра. Excel варьирует значение в заданной ячейке до тех пор, пока вычисление по формуле, зависящей от этой ячейки, не даст желаемый результат.
3.1.Подбор параметра в таблице
Когда следует применять Подбор параметра.
Для того, чтобы найти определенное значение для какой – либо ячейки путем подбора значения другой отдельной ячейки необходимо воспользоваться процедурой Подбор параметра.
Правила выполнения процедуры Подбора параметра
В окна Установить в ячейке и Изменяя ячейку можно вводить как ссылки на ячейки, так и их имена
Изменяемая ячейка должна содержать значение, от которого прямо или косвенно зависит формула, указанная в окне Установить в ячейке
Изменяемая ячейка не должна содержать формулу
Когда подбор параметра завершен, Excel выводит результаты на рабочий лист и в окно Состояние параметра
Для сохранения полученных результатов нажмите кнопку ОК
Для восстановления исходных значений нажмите кнопку Отмена
Если вы решили сохранить найденное решение на рабочем листе, а затем передумали, выберите в меню Правка команду Отменить подбор параметра сразу же после завершения операции подбора параметра
Познакомимся с этой процедурой на примерах.
Задание 12. Выполнение процедуры Подбор параметра
Предприятие решило выбросить на рынок новый продукт в количестве 10 000 единиц, какова должна быть цена продукта, если предприятие намеренно получить прибыль в 100 тыс. рублей.
При изготовлении единицы продукта расходы фирмы составили:
Затраты (материалы, эксплуатация оборудования, транспортные расходы. и др.) = 0,1*Цена ед. продукта.
Зарплата =0,3*Цена ед. продукта.
Реклама =0,05*Цена ед. продукта.
Прочие расходы (связанные с продажей изделия) = 0,1*Цена ед. продукта.
Расходы = Затраты +Зарплата +Реклама + Прочие.
Прибыль = Количество * Цена - Расходы * Количество или
= Количество * (Цена – Расходы)
Для этого:
Создайте новый рабочий лист 6. ПОДБОР ПАРАМЕТРА
Заполните ячейки A1:A8 в соответствии с рис. 6.1.
Рис. 6. 1. Исходная таблица расчета прибыли
Ячейкам В1:В8 присвойте имена. Для этого выделите диапазон ячеек A1:B8. На вкладке Формулы в группе Определенные имена выберите команду и выделите команду Создать из выделенного фрагмента (рис. 6.2)…
Рис. 6. 2. Выбор команды присвоение имен
Выберите в столбце слева (рис. 6.2).
Рис. 6. 3. Присвоение имен диапазону ячеек
Ячейкам В1:В8 будут присвоены имена содержащиеся в блоке А1:А8.
В ячейках В2:В8 установите денежный формат с точностью два знака.
Введите начальное значение цены товара, например = 1 руб.
Введите все необходимые формулы, как на рис. 6. 4.
Рис. 6. 4. Исходная таблица расчета прибыли
Результат вычислений будет выглядеть следующим образом (рис. 6.5).
Рис. 6. 5. Результат вычислений при цене изделия 1 руб.
Курсор установите в ячейке В8.
На вкладке Данные в группе Работа с данными откройте список возможностей команды Анализ «что – если» (рис. 6.6).
Рис. 6. 6. Выбор команды Подбор параметров
В поле «Установить в ячейке» появившегося окна введите ссылку на ячейку B8, содержащую формулу (рис. 6.7).
Рис. 6. 7. Окно процедуры Подбор параметра
В поле «Значение» наберите искомый результат 100 000.
В поле «Изменяя значение ячейки» введите ссылку на изменяемую ячейку B2 и щелкните на кнопке ОК.
Достигнутый объем прибыли будет достигнут при цене товара равной 22,22р., что будет отражено в следующем сообщении (рис. 6.8).
Рис. 6. 8. Окно Результат подбора параметра
Сохраните результат вычислений.
Примечание. Если в предыдущем окне появится сообщение, что решение не найдено, то отмените подбор параметра и посмотрите, почему этого не произошло.