1. Создадим макрос для вычисления расходов на оплату квартиры
2. Макрос для построения графика
Sub График()
'
' График Макрос
' Построение точечного графика
'
' Сочетание клавиш: Ctrl+t
'
Columns("A:B").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Лист1").Columns("A:B")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlRight
End Sub
Sub График()
'
' График Макрос
' Построение точечного графика
'
' Сочетание клавиш: Ctrl+t
'
n = Application.CountA(Worksheets(1).Range("A:A"))
' Columns("A:B").Select
Range("A1:B" & CStr(n)).Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Лист1").Range("A1:B" & CStr(n))
ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlRight
End Sub
7.9 Средства для анализа данных (Использование надстроек: подбор параметра; поиск решения)
В Excel имеются два мощных средства для анализа данных: Подбор параметра (Goal Seek) — служит для определения входной величины, обеспечивающей определенное значение функции, и Поиск решения (Solver) — позволяет определить величину или группу величин, обеспечивающих оптимальное в некотором смысле значение функции при заданных ограничениях.
Зачастую мы знаем тот результат, который нужно получить с помощью вычисления по формуле, однако входное значение, необходимое для получения этого результата, Вам неизвестно.
Чтобы решить эту задачу нужно воспользоваться возможностью Подбора параметра. С помощью Подбора параметра Excel варьирует значение в заданной ячейке до тех пор, пока вычисление по формуле, зависящее от этой ячейки, не даст нужный результат. Подбора параметра, позволит избежать проведение анализа методом проб и ошибок, на который уходит слишком много времени.
Чтобы найти оптимальное значение для какой-либо ячейки путем подбора значений одной или более ячеек или наложить определенные ограничения на одно или несколько значений, вовлеченных в вычисления, то необходимо воспользоваться программой Поиск решения Microsoft Excel.
Мы остановимся на следующих темах:
Использование средства Подбор параметра (Goal Seek)
Использование диаграмм для подбора параметров
Использование надстройки Поиск решения (Solver) для подбора оптимального решения при заданных ограничениях
7.9.1 Подбор параметра
Средство Подбор параметра (Goal Seek) предназначено для получения заданного значения в целевой ячейке путем подбора значений в ячейке-параметре.
Как применять команду Подбор параметра
1. Выделить ячейку с формулой, которая должна принять заданное значение (целевую ячейку)..
2. Выбрать команду Сервис, Подбор параметра. Появится диалоговое окно Подбор параметра
Рисунок– Диалоговое окно Подбор параметра
При этом в поле Установить в ячейке уже будет находиться ссылка на выделенную при выполнении шага 1 ячейку.
3. В поле Значение ввести величину, которую необходимо получить в целевой ячейке.
4. В поле Изменяя значение ячейки ввести ссылку на ячейку-параметр. (Изменяемая ячейка не должна содержать формулу)
5. Нажать кнопку ОК. При этом откроется диалоговое окно Результат подбора параметра и начнется итерационный процесс поиска решения, каждый шаг которого дает следующее приближение к искомой величине.
Рисунок Диалоговое окно Результат подбора параметра
6. Если задача обладает плохой сходимостью, т. е. для поиска решения с заданной точностью требуется много (или бесконечно много) итераций, можно воспользоваться кнопками Пауза или Отменить диалогового окна Результат подбора параметра, чтобы приостановить или отменить затянувшийся процесс подбора параметра. После нажатия кнопки Пауза можно выполнять процесс поиска по шагам. Для этого служит кнопка Шаг . Для возобновления автоматического поиска используется кнопка Продолжить
7. После того как решение найдено, диалоговое окно Результат подбора параметра нажать кнопку ОК, чтобы заменить значения рабочего листа новыми значениями, или нажмите кнопку Отмена, чтобы сохранить прежние величины.
Как уже отмечалось выше, ячейка-параметр, выбранная на шаге 4, должна влиять на целевую ячейку, указанную в поле Установить в ячейке, и сама не должна содержать формулу.
Примеры подбора параметра
1. С помощью средства Подбор параметра можно определить, например, необходимый для получения заданной прибыли рост производства или объем продаж, обеспечивающий окупаемость.
На рисунке, представленном ниже приведен упрощенный пример калькуляции, в которой расписаны расходы и доходы от издания партии компакт-дисков (CD). Описание ячеек и их содержимого приведено в табл. 33.1.
Рисунок– Пример подбора параметров
Таблица. Описание ячеек в примере
|
Ячейки |
Элемент |
Содержимое |
|
С3 |
Количество экземпляров |
1000 |
|
С4 |
ДОХОД |
СЗ*С14 |
|
С5 |
Себестоимость |
СЗ*С15 |
|
С6 |
Валовая прибыль |
С4-С5 |
|
С7 |
Затраты на зарплату |
2000 |
|
С8 |
Затраты на рекламу |
5000 |
|
С9 |
Накладные расходы |
12000 |
|
С10 |
Валовые издержки |
С7+С8+С9 |
|
С12 |
Прибыль |
С6-С10 |
|
С14 |
Цена одного CD |
50 |
|
С15 |
Себестоимость одного CD |
15 |
Зададимся целью получить прибыль 50000 руб. вместо 16000 руб. В данном примере это можно сделать несколькими способами: понизить валовые издержки и накладные расходы, увеличить тираж, уменьшить себестоимость. Проще всего, разумеется, поднять цену.
В результате целевой ячейкой будет С12 — "Прибыль", а ячейкой-параметром будет С14 - "Цена одного CD". Требуется узнать содержимое ячейки C14, при котором ячейка С12 примет значение 50000.
1. С помощью средства Подбор параметра можно найти с точностью 0.0001 наименьший корень уравнения 3x3-cos2x-1=0 на отрезке [-1,1].