Файл: 2_MS_Excel.pdf

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

4,407678

3,481150

8,629884

8,779637

9,914596

0,490590

6,119983

7,755563

3,361304

1,194127

2,654380

5,058417

3.В ячейку A1 введите заголовок этого столбца: «Случайные числа». Столбецу B дайте название «Округление».

4.В столбец В (диапазон В2:В13) поместите числа, представляющие собой округленные значения чисел из столбца А с точностью до 2 значащих цифр после запятой. Для этого выполните следующие действия:

–Установив курсор в ячейку В2, щелкните по кнопке вызова Мастера функций (fx) на стандартной панели инструментов.

–В поле Категория открывшегося окна выберите Математические, в поле Функция найдите в списке и щелкните мышью на функции с названием ОКРУГЛ.

–Для выбранной функции следует указать два параметра: ссылку на округляемое число и количество цифр после запятой. Щелкните мышью по ячейке А2 - в поле Число отразится адрес округляемого числа. Перейдите в поле Количество цифр и напечатайте 2 (это количество значащих цифр после запятой).

–В ячейке В2 появится результат округления числа, находящегося в ячейке А2 (4,41). В строке формул отражается формула, записанная в ячейке В2.

–Скопируйте формулу из ячейки В2 на остальные ячейки столбца В. Для этого поместите табличный курсор на ячейку В2, наведите указатель мыши на маркер заполнения (черный крестик в правом нижнем углу табличного курсора) зафиксируйте левую кнопку мыши и протяните прямоугольный контур до ячейки В13. Отпустите кнопку мыши, формула из ячейки В2 будет скопирована на все выделенные ячейки столбца В и вы увидите результат вычисления по этой формуле. Проверьте правильность вычислений.

5.Составьте еще 3 столбца с заголовками «Корень» (в ячейке С1), «Целое» (в ячейке D1) и «Факториал» (в ячейке Е1).

6.Для создания третьего столбца, содержащего квадратные корни из соответствующих ячеек столбца В, используйте математическую функцию КОРЕНЬ. Используя Мастер функций, запишите формулу сначала в ячейку С2 (указав в качестве параметра ссылку на ячейку В2), а затем скопируйте ее на остальные ячейки столбца С.

7.Для записи значений в четвертый столбец D, содержащий целые значения соответствующих ячеек столбца С, используйте математическую функцию ЦЕЛОЕ.

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

8.Для создания пятого столбца, содержащего факториалы чисел, расположенных в соответствующих ячейках столбца D, используйте математическую функцию ФАКТР.

9.Применим к полученным числовым данным некоторые статистические функции, имеющиеся в и нструментарии MS Excel.

10.В столбце А, начиная с ячейки А15, расположите названия:

Среднее значение Дисперсия

Среднеквадратическое отклонение Медиана

11.Отформатируйте названия: расширьте ячейки, если названия в них не помещаются, выделите заголовки полужирным шрифтом.

12.Установите табличный курсор в ячейку В15. Вызовите Мастер функций. В открывшемся окне в поле Категория выберите Статистические, в поле Функция - СРЗНАЧ (эта функция вычисляет среднее значение чисел заданного диапазона).

13.В качестве значений интервала укажите диапазон В2:В13 (можно этот интервал выделить мышью), нажмите ОК. В строке формул вы увидите формулу =СРЗНАЧ(B2:B13), а в ячейке В15 появится результат вычислений по этой формуле - среднее значение чисел столбца В.

14.С помощью маркера заполнения скопируйте формулу из ячейки В15 на ячейки C15, D15,

E15.

15.Для заполнения строки с заголовком Дисперсия используйте статистическую функцию ДИСП, записав сначала формулу в ячейку В16, а затем скопировав ее на остальные ячейки строки.

16.Аналогичным образом заполните строки с заголовками Среднеквадратическое отклонение и Медиана, используя статистические функции КВАДРОТКЛ и МЕДИАНА соответственно.

17.Сохраните рабочую книгу под названием Задание_2_1.

Задание 2. Финансовые расчеты средствами MS Excel.

1.Предположим, что вы хотите взять 25-летнюю ссуду в размере 1000000р. под 8% годовых. Как определить величину ваших ежемесячных выплат, выплат по процентам и основных выплат за указанный период? Все эти значения помогут вычислить финансовые функции следующим образом:

2.Создайте новую рабочую книгу. Первому листу дайте имя «Ссуда», остальные удалите.

3.Начиная с ячейки А1, создайте таблицу:

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

Основные платежи за первый месяц

Основные платежи за последний месяц

4.С помощью кнопок на панели инструментов Форматирование задайте ячейке В1 Денежный формат, ячейке В3 - Процентный формат.

5.Поместите табличный курсор в ячейку В4, щелкните по кнопке вызова Мастера функций и среди финансовых функций выберите функцию ППЛАТ.

6.В поле Норма следует указать норму месячной ставки (В3/12), в поле Кпер - число периодов (или время вложения) в месяцах (В2*12), в поле Нз - размер ссуды (В1). Параметры Бс и Тип указывать не обязательно. В ячейке В4 вы получили размер ежемесячных выплат.

7.Поместите табличный курсор в ячейку В5, вызовите Мастер функций и среди финансовых функций выберите функцию ПЛПРОЦ.

8.В поле Норма следует указать норму месячной ставки (В3/12), в поле Период - заданный период в месяцах (1), в поле Кпер - число периодов (или время вложения) в месяцах (В2*12), в поле Тс - размер ссуды (В1). Параметр Бс указывать не обязательно. В ячейке В5 вы получили размер выплат по процентам за первый месяц.

9.В ячейку В6 занесите результат расчетов с помощью функции ПЛПРОЦ, указав в поле Период значение 300 (количество месяцев за 25 лет выплаты ссуды).

10.С помощью финансовой функции ОСНПЛАТ заполните значениями ячейки В7 и В8 таблицы, задав в поле Период сначала 1 затем 300.

11.Измените произвольно размер ссуды или процент годовых. Посмотрите как изменятся размеры выплат.

12.Созраните файл как Задание_2_2.

Задание 3. Решение систем n линейных уравнений с n неизвестными.

1.Создайте новую книгу MS Excel. В ней решите систему уравнений:

−

–выделите диапазон A5:C7.

–с помощью мастера функций выберите для этого диапазона функцию МОБР, которой в качестве параметра «Массив» передайте диапазон A1:C3. После ввода параметра назмите сочетание клавиш Ctrl+Shift+Enter. Полученная таким образом в диапазоне A5:C7 матрица называется обратной к матрице A.

–выделите диапазон C9:С11.

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

–с помощью мастера функций выберите для этого диапазона функцию МУМНОЖ, которой в качестве параметра «Массив1» передайте диапазон A5:C7, параметра «Массив2» – диапазон E1:E3. После ввода параметра назмите сочетание клавиш Ctrl+Shift+Enter. Если вся последовательность действий будет выполнена правильно, то в ячейках C9, C10 и С11 окажутся корни системы уравнениq x, y, z соответственно.

2.Проверьте правильность полученного решения системы уравнений.

3.Сохраните файл под именем Задание_2_3.

Лабораторная работа № 3. Построение диаграмм и графиков средствами

MS Excel.

Цель работы: Научиться строить графики на основе данных, освоить форматирование диаграмм согласно заданным условиям.

Задание 1. Построение диаграммы.

1.Откройте рабочую книгу Задание_1_5, созданную ранее.

2.На рабочем листе «Дополнительные расходы по месяцам» удерживая левую кнопку мыши, методом протягивания выделите диапазон ячеек А2:С25.

4.Щелкните на значке Мастер диаграмм на стандартной панели инструментов.

5.В списке Тип выберите пункт Гистограмма (для отображения данных в виде столбчатой диаграммы). В палитре Вид выберите нижний пункт в первом столбце (трехмерная гистограмма). Щелкните по кнопке Далее.

6.Так как диапазон ячеек был выделен заранее, мастер диаграмм автоматически определяет расположение рядов данных. Убедитесь, что данные на диаграмме выбраны правильно.

7.На вкладке Ряд выберите пункт Ряд1, щелкните в поле Имя, а затем на ячейке В1. Аналогично, выберите пункт Ряд2 и щелкните сначала в поле Имя, а затем на ячейке С1. Щелкните на кнопке Далее.

8.Выберите вкладку Заголовки. Задайте заголовок диаграммы, введя в поле Название диаграммы текст «Диаграмма расходов». Щелкните по кнопке Далее.

9.Установите переключатель Отдельном. Задайте имя добавляемого рабочего листа: «Статистический анализ данных». Щелкните на кнопке Готово.

10.Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее. Попробуйте навести указатель мыши на любой из элементов диаграммы. Убедитесь, что во всплывающем окне отображается точное значение данного элемента диаграммы.

11.Щелкните на одном из элементов ряда Нарастающий итог. Убедитесь, что весь ряд выделен.

12.Выполните команду Формат → Выделенный ряд. Откройте вкладку Вид.

13.Щелкните на кнопке Способы заливки. Установите переключатель Заготовка, в раскрывающемся списке выберите пункт «Океан», задайте тип штриховки диагональная 1. Щелкните на кнопке ОК и еще раз на кнопке ОК, произошло изменение ряда данных.