Решение задач в табличном процессоре MS Excel

ГОУ ВПО

Уфимский  государственный  авиационный технический  университет

Кафедра Информатики 

 
 
 

            Решение задач в  табличном процессоре MS Excel  
 

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

 

к курсовой работе

по    дисциплине «Информатика» 

(наименование дисциплины) 

 
 
 
 
 

Уфа 2011   г.

Содержание:

 

Задача 1  3

Задача 2 4

Задача 3 6

Задача 4 8

Задача 5  10

Список литературы  11 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Задача 1.

Построить в одной системе координат при xÎ[-3,0] графики следующих двух функций:

     

Построить блок-схему  алгоритма для табулирования функций y(x), z(x).

Решение.

1. Строим таблицу значений x, y, z.

1.1. В  столбцах A и B создаем ряды значений соответственно x и у. В ячейке A1

вводим «x», в ячейке В1 – «y», в ячейке В2 – «z» .

1.2. Заполняем  значения x в ячейке А2-А3,с шагом 0,3. Используя механизм автозаполнения заполним диапазон А2-А12

1.3. Введем формулу для в ячейку B1. Она позволит рассчитать y. С помощью автозаполнения протянем формулу в диапазоне В2-В12.

1.4. Аналогично  вводим формулу для   в ячейки С2-С12.

1.5. Строим  график. Выделяем диапазон ячеек  А1-С12 и вызываем мастер диаграмм. Выбираем точечную диаграмму, сглаживающую значения.

 
 

Рисунок 1 – Решение задачи  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Задача 2. Найти все решения нелинейного уравнения:

Решение.

    Отметим, что у полиномов третей степени  имеется не более трех вещественных корней. Для нахождения корней их предварительно нужно локализовать. Для этого построим график функции или протабулируем ее на отрезке [-4, 4] с шагом 0,5. В ячейку B2 введена следующая формула: =A2^3+2,84*A2^2-5,6064*A2-14,766336.

 
 

    Рисунок 3 - Локализация корней полинома 

    На  рисунке видно, что полином меняет знак на интервалах [-3,5;-3], [-2,5;-2], [2;2,5]. Это означает, что на каждом из них имеется корень данного полинома. 

    Найдем  корни полинома методом последовательных приближений с помощью команды Сервис, Подбор параметра (Tools, Goal Seek). Относительная погрешность вычислений и предельное число итераций задаются на вкладке Вычисления диалогового окна Параметры, открываемого командой Сервис, Параметры.

    Зададим относительную погрешность и предельное число итераций, равным 0.00001 и 1000, соответственно. 

В качестве начальных  значений приближений к корням выберем  любые точки из отрезков локализации  корней. -3,25, -2,25, 2,25. Введем их в диапазон ячеек С2-С4. В ячейку D2 введем формулу =С2^3+2,84*С2^2-5,6064*С2-14,766336. 

Выберем команду Сервис, Подбор параметра и заполним диалоговое окно Подбора параметра. 
 
 

 

 

 

Рисунок 4 - Диалоговые окна Результат подбора параметра 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Задача 3. Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды 300000 руб. под годовую ставку 28% на срок 6 лет.

 

Рисунок 5 – Форма решения задачи 

В задаче использованы следующие формулы. 

ПЛТ(ставка ;кпер;пс;бс;тип)

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

Кпер  — общее число выплат по ссуде.

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

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

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

ОСПЛТ(ставка ;период;кпер;пс;бс;тип)

Ставка  — процентная ставка за период.

Период  — задает период, значение должно быть в интервале от 1 до «кпер».

Кпер  — общее число периодов платежей по аннуитету.

Пс —  приведенная к текущему моменту  стоимость или общая сумма, которая  на текущий момент равноценна ряду будущих платежей.

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

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

ПРПЛТ(ставка ;период;кпер;пс;бс;тип)

Ставка  — процентная ставка за период.

Период  — это период, для которого требуется  найти платежи по процентам; должен находиться в интервале от 1 до «кпер».

Кпер — общее число периодов платежей по аннуитету.

Пс —  приведенная к текущему моменту  стоимость или общая сумма, которая  на текущий момент равноценна ряду будущих платежей.

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

Тип —  число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент «тип» опущен, то он полагается равным 0. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Задача 4. Имеется однотабличная база данных с полями (столбцами): Банк, Количество индивидуальных сейфов, Количество свободных сейфов, Доля свободных сейфов, Недели года.

Произвести следующие  операции:

      а) Отсортировать базу данных по названию банка;

      б) Создать сводную таблицу и подсчитать Среднее количество и долю свободных ячеек для каждого банка.

      в) При помощи фильтра оставить на экране только банки, у которых свободно не менее 30% индивидуальных сейфов.

      г) Отразить на круговой диаграмме долю каждого банка в общем фонде индивидуальных сейфов.

Решение.

а) Сортировка

 

Рисунок 6 - Сортировка 

б) Сводная таблица 

 

Рисунок 7 - Вид сводной таблицы 

в) Фильтрация

 

Рисунок 8 – Отфильтрованные данные 

г) Диаграмма

 

Рисунок 9 - Доля каждого банка в общем фонде индивидуальных сейфов. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Задача 5. Из наблюдений получены следующие данные о колебаниях цен на Акции нефтяной компании в зависимости от стоимости одной баррели нефти на бирже