Методичка по информационным технологиям

11

Номер_столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д. Если значение аргумента «номер_столбца»:

Интервальный_просмотр — логическое значение, определяющее, какое соответствие должна найти функция ВПР — точное или приблизительное.

Самостоятельно пересчитайте стипендию с помощью функции ЕСЛИ.

Задание 5. Статистика в Excel

Тренд (Trend - тенденция) — выраженная направленность тенденции изменений показателей любого временного ряда. Графики могут быть описаны различными уравнениями — линейными, логарифмическими, степенными и т. д. Фактический тип графика устанавливают на основе графического изображения данных временного ряда, путем осреднения показателей динамики ряда, на основе статистической проверки гипотезы о постоянстве параметров графика.

Корреляция — статистическая взаимосвязь двух или нескольких случайных величин (либо величин, которые можно с некоторой допустимой степенью точности считать таковыми). При этом изменения одной или нескольких из этих величин приводят к систематическому изменению другой или других величин. Математической мерой корреляции двух случайных величин служит коэффициент корреляции.

Коэффициент корреляции или парный коэффициент корреляции в теории вероятностей и статистике — это показатель характера изменения двух случайных величин.

1.Откройте файл «Анализ данных».

2.Вычислить коэффициент упитанности (по Фультону).

Ку=Р*100/L3

где Ку — коэффициент упитанности; Р — масса тела, г;

L — длина тела, см.

3.Построить точечный график зависимости размера и веса пелагического омуля.

4.Измените параметры оси Y, задав минимальное значение – 300.

5.Уменьшите размер точек.

6.Добавьте подписи осей и диаграммы.

7.Добавьте линию тренда с наибольшей достоверностью аппроксимации R2.

Работа с диаграммами > Макет > Анализ > Линия тренда > Дополнительно

8.Включите показ на графике уравнения и величины достоверности аппроксимации.

9.В отдельной ячейке вычислите коэффициент корреляции, используя статистическую функцию:

12

КОРРЕЛ Возвращает коэффициент корреляции между интервалами ячеек «массив1» и «массив2». Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера.

Синтаксис КОРРЕЛ(массив1;массив2)

Массив1 — это интервал ячеек со значениями. Массив2 — второй интервал ячеек со значениями.

10.В отдельной ячейке, в соответствии с приведенной ниже таблицей, определите характеристику силы связи размера и массы омуля. (Можно использовать или функцию ЕСЛИ или функцию ВПР)

13

Задание 6. Выборка и фильтрация

Открыть файл автомобили.xls. Добавить еще 5 (пять) Листов.

На Листе 1: переименовать Лист 1 в «ИСХОДНАЯ ТАБЛИЦА»

удалить пустую строку под заголовками

выделить всю таблицу (без общего заголовка) и оформить, используя Стиль

«Светлый3».

заполнить столбец Цена (руб) (в соответствии с курсом рубля). Если это необходимо, выполнить Автоподбор ширины Столбца.

по центру расположить значения столбца E.

выделить таблицу и скопировать ее на Лист 2 текущей книги.

На Листе 2: Переименовать Лист 2 в «АВТОФИЛЬТР» Обратите внимание, что автофильтр автоматически устанавливается при задании

стиля. Кроме того, Автофильтр можно установить в панели Главная > Редактирование

> Сортировка и фильтр

В столбеце с марками автомобилей и с помощью Автофильтра выбрать ВАЗ-2106 и Москвич-2141

Скопировать полученную таблицу на Лист 3, начиная с ячейки B2

Восстановить показ всей таблицы на Листе 2

В столбце «Цена (дол.)» выделить с помощью Автофильтра Числовой фильтр первые 5 автомобилей с наименьшей ценой

Скопировать полученную таблицу на Лист 3, начиная с ячейки B11.

Восстановить показ всей таблицы на Листе 2.

В столбце «Дата» с помощью Автофильтра определить машины, зарегистрированные 9 сентября.

Скопировать полученную таблицу на Лист 3, начиная с ячейки B20.

Восстановить показ всей таблицы на Листе 2.

С помощью Автофильтра выбрать строки по следующим условиям: марка машины ВАЗ (любой модели); стоимостью дешевле 1200, но дороже 700 долларов; состояние – «отл» или «идеал»

Скопировать полученную таблицу на Лист 3, начиная с ячейки B30.

Восстановить показ всей таблицы на Листе 2. Таблицу с Листа 2 скопировать на Лист 4

На Листе 4: Переименовать Лист 4 в «РАСШИРЕННЫЙ ФИЛЬТР»

С помощью Расширенного фильтра выбрать данные по следующим условиям:

если это Москвич старый до 1995 то платить за него не более 500$;

если речь пойдет о более современном (после 1995) Москвиче, то устроит любая цена.

Постройте таблицу условий отбора на Листе 4 после основной таблицы.

Примените расширенный фильтр Данные > Сортировка и фильтр > Дополнительно,

указав в качестве диапазона условий построенную таблицу. Скопировать полученные данные на этом Листе 4, начиная с ячейки A40

Самостоятельно выбрать данные по следующим условиям:

ВАЗ после 1995 г. подойдет любой модели, в хорошем состоянии не дороже 1200$, но не белого цвета, и пробег чтобы был меньше сотни.

Скопировать полученные после отбора данные на этом Листе 4, начиная с ячейки

A40.

14

Тема 3. Построение графиков и решение нелинейных уравнений

Задание 7. Построение графика функции. Работа с мастером функций и мастером диаграмм.

Рассмотрим функцию y=cos2( x), при х [0,1].

1. Постройте таблицу ее значений.

Первая колонка – аргументы. Шаг изменения аргумента равен 0,1.

Вторая колонка - y(0), y(0,1), у(0,2), •••, y(1).

2. Постройте график функции. Вид диаграммы - график.

Ряды данных - В столбцах.

Подписи по оси Х – укажите колонку с аргументами.

Задание 8. Построение графика функции с одним условием

Рассмотрим пример построения графика функции

Этот график строится так же, как в Задаче 1, за одним исключением — для вычисления значения функции используйте ЕСЛИ.

Синтаксис логической функции ЕСЛИ (IF):

ЕСЛИ (лог выражение; значение_если_истина; значение_если_ложь).

Функция ЕСЛИ возвращает значение_если_истина, если лог_выражение имеет

15

значение ИСТИНА (TRUE), и значение_если_ложь — если лог_выражение

имеетзначение ложь (FALSE).

Задание 9. Построение графика функции с двумя условиями

Рассмотрим пример построения графика при х [0,1].

Для вычисления значений функции используйте вложенные ЕСЛИ.

Задание 10. Построение двух графиков в одной системе координат

Рассмотрим пример построения в одной системе координат графиков следующих двух функций

у = 2sin(x) и

z = 3cos(2x) — sin(x)

при х [-3, 0].

1.В первой колонке введите значения переменной x: от -3 до 0 с шагом 0,2.

2.В следующие две колонки значения у и z, соответственно.

3.Выделите диапазон значений y z и постройте графики. Для наглядности, графики функций можно различать по типу линий.

Задание 11. Построение поверхности

Рассмотрим пример построения поверхности: z=x2-y2, при х, у [-1, 1].

1.Постройте таблицу значений z

2.Обозначте столбцы значениями аргумента x в заданном диапозоне с шагом

0,2

3.Обозначте строки значениями аргумента y в заданном диапозоне с шагом 0,2

4.Заполните таблицу значениями z, используя автозаполнение (не забудте про абсолютные адреса).

5.Постройте поверхность по полученным значениям z.

a.Тип диаграммы — Поверхность. Дальнейшие шаги аналогичны тем, что описаны в разделе 1.

17

Вполе Значение вводим величину, которую ищем - 0.

Вполе Изменяя значение ячейки введем ссылку на ячейку, отведенную под переменную – приближение x.

После нажатия кнопки ОК средство подбора параметров находит приближенное значение корня. В данном случае оно равно -0. 919999.

4. Найдите два оставшихся корня.

Задание 13. Нахождение корня уравнения методом деления отрезка пополам.

Рассмотрим алгоритм нахождения корня уравнения F(x) = 0 методом деления отрезка пополам.

Пусть непрерывная функция F(x) имеет значения разных знаков на концах отрезка

[a; b], т. е. F(a)*F(b)<0.

Тогда уравнение F(x)=0 имеет корень внутри этого отрезка. Отрезок [a; b] называется отрезком локализации корня.

Пусть c = (а + b) / 2 - середина отрезка [a; b].

Если F(а)*F(с)<=0, то корень находится на отрезке [a; с], который берем за новый отрезок локализации корня.

Если F(а)*F(с)>0, то за отрезок локализации корня берем [c;b]. Отметим, что новый отрезок локализации корня в два раза меньше первоначального.

Процесс деления локализации корня продолжаем до тех пор, пока его длина не станет меньше, точности нахождения корня. В этом случае любая точка локализации отличается от корня не более чем на ε/2.

Найти корень с точностью до 0.001 методом деления отрезка пополам уравнения x2 —2 = 0. За первоначальный отрезок локализации корня выбрать [0; 2].

Для реализации этого метода введите в ячейки рабочего лист формулы либо значения, как показано ниже.

19

Тема 5. Финансовый анализ в Excel. Механизм подбора параметра

Задание 15. Финансовые функции

Рассчитайте 30-летнюю ипотечную ссуду со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ПЛТ, которая возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.

Расчет ипотечной ссуды

Указания

1.Постройте таблицу расчета ипотечной ссуды как показано выше.

2.Занесите исходные данные. Размер ссуды вычислите исходя из «Цены» и «Первого взноса»

3.Сделайте расчет для ежегодных и ежемесячных периодических выплат. Периодические выплаты вычисляются с помощью функции ПЛТ:

ПЛТ(СТАВКА;КПЕР;ПС;БС;ТИП)

Аргументы функции:

СТАВКА _________ Процентная ставка по ссуде КПЕР ____________ Общее число выплат

ПС ______________ Текущее значение, т. е. общая сумма, которую составят будущие платежи

БС ______________ Будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (например, будущая стоимость займа равна 0)

ТИП _____________ Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода

20

4.Вычислив величину периодических выплат и зная общее число, рассчитайте «Общую сумму выплат» для ежегодных и ежемесячных платежей.

5.Теперь, зная общую сумму выплат и размер ссуды, вычислите общую сумму комиссионных.

Задание 16. Подбор параметра

Вы хотите взять ссуду 10000 рублей на 1 год с ежемесячной выплатой. Вы готовы по прошествии каждого месяца платить в течение года по 900 рублей. Какой должна быть процентная ставка? Эта задача может быть решена подбором параметра.

Указания

1.Постройте таблицу как показано выше

2.Ячейка В1 будет играть роль регулируемой ячейки, в которой вы получите результат поиска искомого значения процентной ставки. В начале же в эту ячейку запишите любое значение, например 8,0%.

3.В ячейке В2 запишите размер ссуды, в ячейке В3 – число периодов выплат (12 месяцев), а в ячейке В4 вычислите сумму периодического платежа, с помощью функции ПЛТ.

4.Выделите ячейку В4 таблицы с формулой.

5.Выполните команду

Данные > Работа с данными > Анализ «Что-Если» > Подбор параметра.

Появится диалоговое окно Подбор параметра. В поле «Установить в ячейке» уже будет находиться адрес выделенной на предыдущем шаге ячейки с формулой.

6.В поле «Значение» введите величину, которой должна быть равна ежемесячная выплата в результате подбора значения процентной ставки. В рассматриваемом примере это значение 900.

7.В поле «Изменяя значение ячейки» введем ссылку на исходную ячейку В1 (можно просто щелкнуть по этой ячейке), влияющую на результат вычислений по формуле.

8.Щелкните на кнопке ОК. Появиться диалоговое окно Результат подбора параметра. Щелкните на кнопке ОК. Искомое значение процентной ставки будет подставлено в ячейку В1.

Задание 17. Самостоятельно. Другие финансовые функции

ПРИМЕР. Ссуда в 20 ООО долларов дана на полтора года под ставку 28% годовых с ежеквартальным начислением. Определить сумму конечного платежа.

РЕШЕНИЕ. Здесь базовый период — квартал. Срок ссуды составляет 6 периодов (4 квартала в году, срок полтора года) за период начисляется 7% = 28% / 4.