Поделиться
196. Инструменты анализа данных.pdf
Инструменты анализа данных в электронных таблицах OpenOffice.org Calc
2. Таблица подстановок
Таблица данных, таблица подстановок - диапазон ячеек, содержащий результаты подстановки различных значений в одну или несколько формул. Таблицы данных предоставляют способ быстрого вычисления нескольких версий в рамках одной операции, просмотра и сравнения результатов всех различных вариантов на одном листе.
Ячейка ввода - ячейка, в которую подставляются все значения из таблицы данных. Хотя ячейка ввода не обязана входить в таблицу данных, формулы в таблице данных должны ссылаться на ячейку ввода.
Массив - объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам.
Существует два типа таблиц данных: таблицы с одной переменной и таблицы с двумя переменными.
1. Сформировать таблицу: в отдельный столбец или строку ввести список значений, которые следует подставлять в ячейку ввода.
2. Выполнить одно из следующих действий.
§ если значения в таблице данных ориентированы по столбцу, ввести формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения; правее первой формулы в той же строке ввести другие формулы;
§ если значения в таблице данных ориентированы по строке, ввести формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения, ниже ввести другие формулы.
3. Выделить диапазон ячеек, содержащий формулы и значения подстановки.
4. Команда Данные \ Совмещенные операции.
5. В появившемся диалоговом окне Таблица подстановок заполните пустые поля.
§если значения в таблице расположены в одном столбце ввести ссылку на ячейку ввода в поле Столбец ввода;
§если значения в таблице расположены в одной строке ввести ссылку на ячейку в поле Строка ввода.
Диалоговое окно Таблица подстановки Задача 2.1. (PPMT) Для покупки недвижимости необходимо взять ссуду. Банки предоставляют ссуду под определенный процент. Посмотреть, как различные процентные ставки (12%, 13%, 14%) влияют на размер ежемесячных выплат по закладной (3 000 000 руб.) на данный срок (10 лет). Решение.
1. Создать таблицу (вариант оформления листа на рис. 2.4, обратите особое внимание на формат ячеек):
§ В4 – ячейка ввода;
§ B2:В3 – исходные данные;
§ В5 – формула для расчета:
=PPMT(B4/12;B2;-B3)
§ В4:D4 – список значений, которые подставляются в ячейку ввода В4; на ячейки диапазона установить процентный формат; § В4:D5 – таблица подстановок.
Рис. 2.4. Вариант оформления листа 2. Выделить диапазон В4:D5. Команда Данные \ Совмещенные операции.
3. В появившемся окне заполнить поля. В поле Строка ввода ввести значение $В$4. Нажать кнопку ОК.
4. Результат вычислений:
Таблицы подстановки с двумя переменными используют одну формулу с двумя наборами значений. В ячейку листа ввести формулу, которая ссылается на две ячейки ввода. В том же столбце ниже формулы ввести значения подстановки для первой переменной. В той же строке справа от формулы ввести значения подстановки для второй переменной.
1. Выделить диапазон ячеек, содержащий формулу и оба набора данных подстановки.
2. Команда Данные \ Совмещенные операции.
3. В появившемся окне заполнить поля.
4. В поле Строка ввода в ввести ссылку на ячейку ввода для значений, расположенных в строке. В поле Столбец ввода ввести ссылку на ячейку ввода для значений, расположенных в столбце.
5. Нажать кнопку OK. Задача 2.2. Составить таблицу умножения чисел от 1 до 5.
Нетрудно заметить, что таблица умножения представляет собой таблицу данных с двумя переменными. Решение.
1. Настроить лист (вариант оформления листа на рис.).
§ А1:В1 – ячейки ввода с начальными значениями;
§ В3:F3-– значения для подстановки в ячейку ввода А1; § А4:А8 – значения для подстановки в ячейку ввода В1; § А3 – формула для расчета, зависящая от ячеек ввода: =А1*В1
2. Выделить диапазон A3:F8.
3. Вызвать инструмент Таблица данных (Совмещенные операции).
4. В появившемся диалоговом окне заполнить пустые поля. В поле Строка ввода в ввести ссылку A1; в поле Столбец ввода ввести ссылку В1.
5. Нажать кнопку OK.
Задача 2.3. (PPMT) Проанализировать влияние различных процентных ставок (12%, 13%, 14%) и сроков займа (10, 20, 30 лет) на размер ежемесячных выплат по ссуде в 3 000 000 руб. Решение.
1. Настроить лист (вариант оформления листа на рис., обратить внимание на формат ячеек).
Формула
§ В3:В4 – ячейки ввода с начальными значениями;
§ В5 – исходные данные;
§ В8:D8 – значения для подстановки по столбцам в ячейку ввода B4; § А9:А11 – значения для подстановки по строкам в ячейку ввода В3;
§ А8 – формула для расчета, зависящая от ячеек ввода:
=PPMT(B3/12;B4;-B5)
2. Выделить диапазон: А8:D11.
3. Вызвать инструмент Таблица данных (Совмещенные операции).
4. В появившемся диалоговом окне заполнить пустые поля. В поле Строка ввода в ввести ссылку В4; в поле Столбец ввода ввести ссылку В3.
5. Нажать кнопку OK. Результат вычислений:
2.3. Удаление таблицы подстановки данных
1. Выделить всю таблицу данных, включая все формулы, значения подстановки, рассчитанные значения, примечания.
2. Команда Правка \ Удалить ячейки.
Поскольку рассчитанные значения находятся в массиве, то можно очистить все значения без удаления структуры таблицы подстановки данных. Для этого:
1. Выделить в таблице данных все рассчитанные значения. Формулы и значения подстановки не выделять!
2. Команда Правка \ Удалить содержимое.
2.4. Задачи для самостоятельной работы
Задача 2.4. (FV) Планируется в течение 1 года ежемесячно в банк класть некоторую сумму под проценты (например, по 1000 руб.). Составить таблицу данных, которая показывает влияние различных годовых процентных ставок (9%; 9,5%; 10%; 10,5%; 11%) на размер накоплений.
Задача 2.5. (FV) Планируется в течение 1 года ежемесячно в банк класть некоторую сумму под 11,2% процентов годовых. Составить таблицу данных, которая показывает влияние размера ежемесячных вкладов (по 1000 руб., 2500 руб., 5000 руб., 7500 руб.) на размер накоплений.
Задача 2.6. (PPMT) Проанализировать, каков будет размер ежемесячных выплат по закладной в 900 000 руб. при процентной ставке 12% и сроках 10, 20 и 25 лет.
Задача 2.7. (PPMT) Проанализировать влияние различных процентных ставок (12%; 12,5% и 13% годовых) и сроков займа (5, 10, 20 и 30 лет) на размер ежемесячных выплат по ссуде (например, 800 000 руб.).
Задача 2.8. При оформлении заказа на изготовление визитных карточек существует система накопительных скидок. Проанализировать влияние различных процентных скидок (5%, 8% и 10%) и размера тиража (24, 120 и 240 штук) на стоимость одной визитной карточки при заданной начальной цене (например, 8 руб.).
2.5. Вопросы для самопроверки 1. Какие задачи можно решить с помощью инструмента Таблица подстановок?
2. Каким образом должен быть подготовлен лист электронных таблиц перед применением средства Таблица подстановок?
3. Как вызвать инструмент Таблица подстановок?
ПРИЛОЖЕНИЕ. Некоторые стандартные функции
СУММ (аргумент1; аргумент2; …) – суммирует указанные числа; в качестве аргументов можно указывать данные различных типов, но в подсчете участвуют только числа. Как правило, аргументами являются диапазоны ячеек.
СУММПРОИЗВ (массив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)
БС (ставка;кпер;плт;пс;тип) - возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.
Пример 2. Сколько будет на счету, если в течение 30 лет вкладывать по 2000 в месяц при 10% годовых.
Ответ: =БС(10%/12;30*12;-2000;;1) = FV10%/12;30*12;-2000;;1)
ПС (ставка;кпер;плт;бс;тип) - возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат.
Пример 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 с. – (Сер. «Классический университетский учебнк»).
Материалы на данной страницы взяты из открытых источников либо размещены пользователем в соответствии с договором-офертой сайта. Вы можете сообщить о нарушении.
