Инструменты анализа данных  в электронных таблицах OpenOffice.org Calc

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

Термины и определения

Анализ «что-если» – процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе. 

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

Команда Подбор параметра находит только одно решение, даже если задача имеет несколько решений, и только для одной ячейки. 

Перед применением рассматриваемого инструмента следует решить задачу средствами электронных таблиц с любыми исходными данными.

1.1. Инструмент анализа Подбор параметра

1. Cоставить таблицу, отвечающую требованиям:

§     ячейка, в которой должен быть получен желаемый результат (целевая ячейка), должна содержать формулу, а значение в ней должно быть наиболее близким к тому, которое требуется получить;

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

2. Применить инструмент Подбор параметра (команда Сервис \ Подбор параметра). 3. В появившемся диалоговом окне Подбор параметра заполнить пустые поля.  В поле Яч. с формулой ввести ссылку на ячейку, содержащую формулу. В поле Целевое значение ввести искомый результат. В поле Изменяемая ячейка ввести ссылку на ячейку, значение которой нужно подобрать. 

Диалоговое окно инструмента Подбор параметра

1.2. Примеры подбора параметра

Задача 1.1. Какие должны быть ежемесячные вклады в течение 12 месяцев при процентной ставке 10,5% годовых, чтобы по истечении срока на счету накопилась сумма в 100 000 руб.?

Решение.

1. Создать таблицу (вариант оформления листа  приведен на рис.):

Вариант оформления листа § в ячейке В1 – подбираемый параметр, установить в ячейке денежный формат (команда Формат \ Ячейки \ Число: Денежный);

§ в ячейках В2:В3 – исходные данные, в ячейке В3 установить процентный формат (команда Формат \ Ячейки \ Число: Процентный); § в ячейке В4 – формула расчета ежемесячной выплаты: 

=FV(B3/12;B2;-B1)^[1]           установить в ячейке денежный формат.

2.     Применить инструмент Подбор параметра. Для этого выполнить команду Сервис \ Подбор параметра.

3.     В появившемся диалоговом окне задать параметры:  

§   в поле Яч. с формулой ввести ссылку на формулу ($B$4);

§   в поле Целевое значение ввести искомый результат (100000);

§   в поле Изменяемая ячейка ввести ссылку на ячейку, значение которой нужно подобрать ($B$1).

4. Результат вычислений: 

Результат вычислений

Ответ: ежемесячные вклады должны быть по 7939,86 р.

Задача 1.2. Решить линейное уравнение: ax+b=0. Решение.

1. Создать таблицу (вариант оформления листа на рис.):

Вариант построения листа

§     в ячейках А3:В3 – исходные данные;

§     в ячейке В6 – подбираемое значение переменной х; § в ячейке А6 – формула:      =А3*В6+В3

                 2. Применить инструмент Подбор параметра:           

Яч. с формулой: $А$6; Целевое значение:0; Изменяемая ячейка: $B$6.

Ответ: х=-1,5.

Задача 1.3. Тело брошено вертикально вверх со скоростью v=10 м/с с высоты h=5 м. Известно, что его положение относительно поверхности земли у (в метрах) в зависимости от времени t (в секундах) описывается функцией y=h+vt-4,9t². Определить момент времени, когда тело упадет на землю. Решение.

1. Создать таблицу (вариант оформления листа на рис.):

Вариант построения листа 

§   в ячейках В1:В2 – исходные данные;

§   в ячейке В3 – подбираемое значение момента времени;

§   в ячейке В4 – формула, описывающая положение тела относительно земли:       =В2+В1*В3-4,9*В3^2

2.     Применить инструмент Подбор параметра, учитывая, что когда тело упадет на землю, значение у=0, следовательно в  ячейке В4 число 0.

Яч. с формулой: $В$4; Целевое значение:0; Изменяемая ячейка: $B$3.

3.     Результат вычислений:

Результат вычислений при начальном значении t=0

Данный результат вызывает большие сомнения (отрицательное значение времени). Поэтому надо выбрать кнопку Отмена.

4.     В ячейке В3, установить значение таким образом, чтобы в ячейке В4 появилось значение близкое к искомому – 0. Таким значением может быть, например, число 2. 

5.     Повторно применить инструмент Подбор параметра. Результат вычислений: 

Результат вычислений при начальном значении t=2 Ответ: тело упадет на землю примерно через 2,5 секунды.

1.3. Косвенное влияние ячеек

В приведенных примерах формула непосредственно зависела от изменяемого параметра. Рассмотрим случай косвенного влияния ячеек.

Задача 1.4. (RADIANS, SIN, COS, IF, И). Траектория снаряда, вылетающего из орудия под углом α c начальной скоростью v₀ м/с, задается уравнениями: x  v₀tcos;

                  y v₀tsin4,9t₂      , где t – время в секундах.

_

При v₀=30 м/с и α=45º определить, на какой высоте будет снаряд на расстоянии 50 м от орудия (х=50)?  Решение.

1. Создать таблицу (вариант оформления листа на рис.):

§   в ячейках В1:В2 - исходные данные;

§   в ячейке В3 – расстояние от орудия через время t:          

=B2*B5*COS(RADIANS(B1))

§   в ячейке В4 – высота снаряда через время t: 

=B2*B5*SIN(RADIANS(B1))-4,9*B5^2

Примечание. При написании формул необходимо учесть, что по условию задачи угол дан в градусах, а аргументы функций SIN, COS в ЭТ задаются в радианах. Для перевода значения из одной единицы измерения в другую используют функцию RADIANS.

Вариант построения листа

2.        Применить инструмент Подбор параметра.

Яч. с формулой: $В$4; Целевое значение:0; Изменяемая ячейка: $B$3.

3.        Результат вычислений:

Результат вычислений Ответ: тело будет примерно на высоте 23 м.

1.4. Нахождение корней уравнения

Нахождение корней полинома в ЭТ выполняется в два этапа:

§   приближенное определение корней графическим методом;

§   получение точных значений с помощью инструмента Подбор параметра.

Задача 1.5. Найти корни полинома третьей степени x³-x²-x+0,5=0. Решение.

1. Подготовить лист для построения графика функции  y= x³-x²-x+0,5 на промежутке [х_нач, х_кон] для 10 точек (вариант оформления листа на рис.):

Вариант построения листа

§   в ячейках      В2:В3         –        промежуток         построения          графика,     например, 

[-1,2] (подбирается опытным путем); 

§   в ячейке В4 – количество точек для построения графика, например 10 (также подбирается опытным путем);

§   шаг вычисляется по формуле, зависящей от промежутка построения и количества точек: 

=(В3-В2)/(В4-1)

§   в ячейке E3 – ссылка на начальное значение х:      =В2

§   в ячейку E4 ввести формулу вычисления следующего значения х и размножить (скопировать) в ячейки диапазона E5:E12: 

=Е3+$В$5

§   в ячейку F3 ввести формулу вычисления значения y и размножить (скопировать) в ячейки диапазона F4:F12: 

=Е3^3-Е3^2-E3+0,5

2. По данным диапазона E3:F12 построить точечную диаграмму:

График функции y= x³-x²-x+0,5 на промежутке [-1,2] 3. Полином третьей степени должен иметь три корня, следовательно, график должен трижды пересечь ось Ох. Построенный график имеет три точки перехода через ось Ох. Следовательно, мы нашли все три приближенных значения корней уравнения.

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

4. Добавить на лист таблицу нахождения точных значений корней уравнения:

Поиск корней уравнения

§   в ячейках В10:В12 – приближенные значения х, при которых график пересекает ось Ох, т.е. y=0; на графике (рис. 1.15) видно, что это могут быть значения: -1; 0,5 и 1,5;

§   в ячейку С10 ввести формулу вычисления значения y и размножить (скопировать) в ячейки диапазона С11:С12: 

=В10^3- В10^2- В10+0,5 

5.     Для поиска более точного значения х₁ применить инструмент Подбор параметра: Яч. с формулой: $С$10; Целевое значение:0; Изменяемая ячейка: $B$10.

6.     Аналогично определить значение двух других корней. Ответ: х₁ ≈ -0,85485; х₂ ≈ 0,40304; х₃ ≈ 1,45170.

1.5. Задачи для самостоятельной работы

Задача 1.6. (PPMT) По сумме займа (500 000 $) и сроку займа (10 лет) определить процентную ставку, при которой ежемесячная плата составит 5000 $. 

Задача 1.7. (FV) На отпуск за год (учитывать только рабочие месяцы, т.е. 11) надо накопить определенную сумму денег (60 000 р.). Определить величину ежемесячных вкладов на счет в банк под 9,8% годовых. 

Задача 1.8. (PPMT) Какую максимальную ссуду на год можно взять, если вы вынуждены ограничить ежемесячные выплаты определенной суммой (например, 5000 р.), а процентная ставка в размере 13% начисляется в конце каждого месяца?

Задача 1.9. Тело брошено вертикально вверх со скоростью v м/с с высоты h м. Известно, что его положение относительно поверхности земли у (в метрах) в зависимости от времени t (в секундах) описывается функцией y=h+vt-4,9t². 

a)                 Скорость v=10 м/с и высота h=5 м. Определить момент времени, когда тело

будет на высоте 8 м.

b)                С какой скоростью нужно бросить тело с высоты h=5 м, чтобы через 2 секунды

оно достигло высоты h=100 м?

c)                 С какой высоты нужно бросить тело со скоростью v=5 м/с, чтобы через 2 секун-

ды оно упало на поверхность земли?

Задача 1.10. (RADIANS, SIN, COS, IF, И). Траектория снаряда, вылетающего из орудия под углом α c начальной скоростью v₀ м/с, задается уравнениями:

x  v₀tcos;

y  v₀tsin4,9t₂ , где t – время в секундах. _

При v₀=30 м/с и α=45º определить:

a)    Через какое время после выстрела снаряд упадет на землю.

b)   Попадет ли снаряд в мишень, расположенную на расстоянии 85 м от орудия. Высота мишени – 2 м, расстояние от ее нижней части до земли – 5 м. Другими словами, надо определить положение снаряда (у) при х=85 и в отдельной ячейке сравнить полученное значение с высотой мишени. Задача 1.11. Найти корни полиномов: a) 2x³-0,1x²-0,7x+0,1=0

b)    -x³+0,2x²+0,6x=0

c)     3x²+2,5x-1=0

d)    -5x²+2x+5=0

Примечание. При поиске корней, обратите внимание на степень полинома.

1.6. Вопросы для самопроверки

1.                Какие задачи можно решить с помощью инструмента Подбор параметра?

2.                Как работает средство Подбор параметра?

3.                Каким образом должен быть подготовлен лист электронных таблиц перед применением средства Подбор параметра?

4.                Как вызвать инструмент Подбор параметра?

5.                Какая ячейка называется целевой?

6.                Может ли ячейка, влияющая на целевую, содержать формулу?

7.                Сколько решений находит команда Подбор параметра?

ПРИЛОЖЕНИЕ. Некоторые стандартные функции

Математические функции

СУММ (аргумент1; аргумент2; …) – суммирует указанные числа; в качестве аргументов можно указывать данные различных типов, но в подсчете участвуют только числа. Как правило, аргументами являются диапазоны ячеек.

OpenOffice.org Calc: функция SUM

СУММПРОИЗВ (массив1;массив2;массив3; ...) – перемножает соответствующие элементы заданных массивов (диапазонов) и возвращает сумму произведений.

Массив1, массив2, массив3, ... — от 2 до 30 массивов (диапазонов), чьи компоненты нужно перемножить, а затем сложить; они должны иметь одинаковые размерности..  OpenOffice.org Calc: функция SUMPRODUCT РАДИАНЫ (угол) – преобразует величину угла из градусах в радианы.

OpenOffice.org Calc: функция RADIANS

Финансовые функции

Аргументы:

Ставка - процентная ставка по ссуде.

Кпер – количество выплат по ссуде.

Пс – значение ссуды или общая (полная, приведенная) стоимость.

Бс  - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным нулю, т.е. для займа, например, Бс=0.

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

Тип — число 0или 1 обозначающее, когда должна производиться выплата (0 или опущен - в конце периода; 1 - в начале)

ПЛТ (ставка;кпер;пс;бс;тип) - возвращает размер периодического платежа, необходимого для погашения ссуды за определенный период времени. 

OpenOffice.org Calc: функция PPMT(Ставка; Период; Кпер; пс;бс; тип)

Пример 1. Определить размер ежемесячных платежей при ссуде в $10000 на срок в 3 года под 9% годовых.

Ответ: =ПЛТ(9%/12;3*12;10000)

  =PPMT (9%/12; 1;3*12;10000)

БС (ставка;кпер;плт;пс;тип) - возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.

OpenOffice.org Calc: функция FV

Пример 2. Сколько будет на счету, если в течение 30 лет  вкладывать по 2000 в месяц при 10% годовых.

Ответ: =БС(10%/12;30*12;-2000;;1)    = FV10%/12;30*12;-2000;;1) 

ПС (ставка;кпер;плт;бс;тип) - возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат.

OpenOffice.org Calc: функция РV

Пример 4. Условия страховки: 500 руб. платится в конце каждого месяца в течении 20 лет при 8% годовых.. Определить, общую стоимость выплат. Ответ: =ПС(8%/12;20*12;500;;0)

                           = РV(8%/12;20*12;500;;0)

Логические функции

ЕСЛИ(лог_выражение;знач_если_истина;знач_если_ложь) – возвращает одно из двух значений:

Знач_если_истина, если лог_выражение имеет значение ИСТИНА. Знач_если_ложь, если лог_выражение имеет значение ЛОЖЬ. OpenOffice.org Calc: функция IF

Пример 4. Определить, является ли значение в ячейке В1 положительным. Ответ: =ЕСЛИ(В1>0; "да";"нет")

Литература

1.                 Кошелев В. Excel 2007. Эффективное использование. - М.: Бином. Лаборатория знаний, 2008 - 544 с.

2.                 Лапчик М.П., Семакин И.Г., Хеннер Е.К.Методика преподавания информатики. – 4-е изд. – М.: Академия, 2007. – 624 стр.

3.                 Орлов А.И. Теория принятия решений: Учеб. пособие. - М.: Издательство "Март", 2004. - 656 с.

4.                 Отставнов М. Прикладные свободные программы в школе. – М.: Издательство "Медиа Технолоджи сервис", 2003. – 96 с.: ил.

5.                 Слетова Л. Excel 2007 - М.: "ЭКСМО", 2007 - 336 стр. 

6.                 Сурядный А., Глушаков С. Microsoft Excel 2007: Самоучитель. – 2-е изд. – М.: АСТ, 2008 - 416 стр.

7.                 Шихин Е.В., Чхартишвили А.Г. Математические методы и модели в управлении: Учеб. пособие. – 3-е изд. – М.: Дело, 2004. – 440 с. – (Сер. «Классический университетский учебнк»).