2_MS_Excel

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

получено при сложении С4+D4+E4+F4=320+350+400+400=1470, а в строке формул будет записана соответствующая расчетная формула.

11.Сделайте активной ячейку «Итого»-«Иванов». Вызовите команду Вставка → Функция. В списке ―Категория‖ выберите класс функций ―Математические‖. В списке ―Функция‖ с помощью кнопок линейки прокрутки найдите и выделите функцию СУММ, нажмите кнопку Далее. В строку аргумента введите с помощью мыши диапазон суммируемых ячеек, нажмите Enter.

12.Скопируйте содержимое ячейки G5 в ячейки G6:G12, для чего сделайте активной ячейку G5. Нажав правую кнопку мыши, вызовите контекстное меню, в котором выделите команду Копировать. Ячейка G5 будет помещена в мерцающую. Маркируйте (т.е. сделайте активной) область G6:G12 и нажмите <Enter>. Любым из способов заполните ячейки, в каждой из которых содержится сумма заработной платы остальных сотрудников отдела за соответствующий месяц.

13.Для улучшения внешнего вида таблицы выделите рамкой названия столбцов. Маркируйте область, названий столбцов таблицы (A3:G3). Вызовите команду через контекстнозависимое меню Формат ячеек и перейдите к листу Рамка. Определите положение рамки и тип линии. Нажмите кнопку ОК.

14.Расположите табельные номера по центру, для чего маркируйте содержащую их область

(А4:А12). Нажмите кнопку в пиктографическом меню (―По центру‖).

15.Расположите название первого столбца в две строчки, для чего сделайте активной ячейку А3. В диалоговом окне Формат ячеек на листе Выравнивание включите переключатель ―Переносить по словам‖ и нажмите кнопку ОК.

16.Измените ширину второго столбца. Подведите курсор мыши к координатной ячейке столбца В справа. Курсор трансформируется в двунаправленную стрелку. Отбуксируйте вправо на нужное расстояние разделитель столбцов и отпустите кнопку мыши.

17.Расположите в центре ячейки название столбцов документа.

18.Заключите аналогичным способом в рамку содержимое подстроки «Итого» (А13:G13).

19.Измените шрифт для заголовка документа, используя команду контекстно-зависимого меню Формат ячеек и лист Шрифт. Поэкспериментируйте с различными характеристиками оформления текста, такими как шрифт, стиль, размер, эффект. Выберите наиболее удачный с Вашей точки зрения вариант шрифта для заголовка.

20.Сохраните рабочую книгу под именем Задание_1_1

Задание 2. Настройка рабочей книги, ввод текста и формул в ячейки, форматирование ячеек.

1.Создайте новую рабочую книгу.

2.Установите следующие параметры для рабочей книги: листов в новой книге – 2, стандартный шрифт - Arial Cyr, размер 10, авторазбиение на страницы.2

3.Сохраните пока пустую рабочую книгу под именем «Задание_1_2».

4.Переименуйте лист 1, дав ему название «Упражнение».

2 воспользуйтесь командой Сервис → Параметры, вкладки Общие и Вид

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

5.На листе «Упражнение» вычислите n-ый член и сумму арифметической прогрессии по разности арифметической прогрессии d и первому члену a1. В случае правильного выполнения упражнения результат будет соответствовать изображенному на рисунке:

Следуйте инструкциям:

–Оформите заголовок таблицы следующим образом: тип шрифта Times New Roman, размер

12 пт, начертание полужирное; выравнивание по горизонтали По центру, по вертикали По центру; установите переключатели (флажки) Перенос по словам и Объединение ячеек.3

–Оформите строку заголовков столбцов следующим образом: тип шрифта Tahoma, размер 11 пт, выравнивание по центру, начертание полужирное и курсив.4

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

–Заполните ячейки В4 – В13 числами от 1 до 10 с шагом 1, используя прогрессию.

–В ячейку С4 введите значение первого члена арифметической прогрессии. В ячейку С5 введите формулу n-го члена арифметической прогрессии и скопируйте ее в остальные нижние ячейки этого столбца, применив абсолютную ссылку на ячейку A4.

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

–Установите для ячеек С4:D13 выравнивание по горизонтали: По правому краю, по вертикали: По нижнему краю.

6.Выполните автоподбор ширины ячеек.5

7.Скройте 12 строку.6

8.Отобразите скрытую строку.7

3воспользуйтесь пунктами меню Формат→Ячейки→Выравнивание и Формат→Ячейки→Шрифт

4Для набора нижних индексов воспольуйтесь пунктом меню Формат→Ячейки→Шрифт

5воспользуйтесь пунктом меню Формат→Ячейки→Выравнивание

6выделите строку и выберите команду Скрыть контекстного меню строки

7Выделите 11 и 13 строки, выберите команду Отобразить контекстного меню выделенных строк

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

9.Скройте столбцы B и C и отобразите их.

10.Обрамите таблицу по образцу.8

11.Ячейки С4-С13 сделайте ярко-зеленого цвета, тип узора - 12,50% серый, а ячейки D4-D13 сиреневого цвета.9

12.Сохраните изменения в файле Задание_1_2.

Задание 3. Копирование данных, вычисления по формулам.

1.Создайте новую книгу MS Excel. На листе 1 этой книги создайте экзаменационную ведомость по дисциплине «Прикладная информатика» так, как это показано на рисунке:

Следуйте инструкциям:

–Внесите в ведомость фамилии студентов вашей подгруппы и поставьте им оценки по экзамену: по своему усмотрению, но так, чтобы задействованы были все оценки.

–Настройте автоматический подсчет того, сколько раз каждая из оценок была поставлена в вашей подгруппе.10

–Рассчитайте общее количество полученных оценок.

2.Сделайте так, чтобы в рабочей книге было три листа (лишние удалите, недостающие добавьте).11

3.Создайте на листах 2 и 3 экзаменационные ведомости для тех же студентов по дисциплинам «Вычислительные машины и сети» и «Математика». Выполните на них аналогичные расчеты.

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

5.Сохраните книгу под именем Задание_1_3.

8воспользуйтесь пунктом меню Формат→Ячейки→Границы

9воспользуйтесь пунктом меню Формат→Ячейки→Вид

10воспользуйтесь функцией СЧЕТЕСЛИ, выбрав Вставка→Функция→Статистические

11Используйте команду Добавить контекстного меню ярлыка листа в нижней части окна программы

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

Задание 4. Использование статистических и логических функций, работа с данными из разных книг.

1.Создайте новую книгу MS Excel. На листе 1 этой книги создайте ведомость назначения на стипендию. Для этого заполните ячейки в соответствии с таблицей:

2.Оформите ведомость (меняя параметры формата ячеек и текста в них) так, чтобы она была удобна для чтения и восприятия информации.

3.Скопируйте список группы из экзаменационной ведомости, созданной при выполнении предыдущего задания.

4.Вычислите средний балл по результатам сдачи экзаменов для каждого из студентов.12

5.Используя минимальное значение стипендии (600 р.), выведите формулу начисления стипендии по условию:13

–если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;

–если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;

–если средний балл меньше 3, стипендия не выплачивается.

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

7.Переименуйте лист 1, дав ему название Стипендия. Остальные листы книги удалите.

8.Сохраните книгу под именем Задание_1_4.

Задание 5. Применение средств автоматизации ввода.

1.Создайте новую рабочую книгу.

2.Дважды щелкните ни ярлыке нового листа и переименуйте его в «Дополнительные расходы по месяцам».

3.Сделайте текущей ячейку А1 и введите в нее текст: Месяцы.

4.Сделайте текущей ячейку В1 и введите в нее текст: Расходы.

5.Сделайте текущей ячейку А2. Введите в нее текст Январь 2009. Нажмите клавишу Enter. Убедитесь, что текст был автоматически распознан как дата (изменился формат, а данные выровнялись по правому краю ячейки). Нажмите стрелочную клавишу Вверх. При желании, дайте команду Формат → Ячейки и укажите иной формат записи даты.

12Примечание: воспользуйтесь функцией СРЗНАЧ, выбрав Вставка→Функция→Статистические

13Используйте функцию ЕСЛИ дважды: одну в другой. Подробную информацию о применении этой функции можно получить, нажав ссылку «Справка о формуле» в нижнем левом углу окна Мастера функции.

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

6.Установите указатель мыши на маркер заполнения в правом нижнем углу рамки текущей ячейки. Нажмите правую кнопку мыши и протяните рамку так, чтобы она охватила все ячейки от А2 до А25.

7.Отпустите кнопку мыши. В открывшемся меню выберите пункт Заполнить по месяцам. Убедитесь, что в столбце А появились обозначения для всех месяцев по декабрь 2010 года включительно.

8.Будем считать, что в первый месяц расходы составляли 10000 рублей, а в каждом последующем возрастали на 10%. Сделайте текущей ячейку В2. Введите в нее число 10000. Нажмите клавишу Enter.

9.Щелкните на ячейке В2 правой кнопкой мыши и выберите в контекстном меню пункт Формат ячеек. На вкладке Число, выберите вариант: Денежный и щелкните на кнопке ОК. Убедитесь, что число теперь записано как денежная сумма.

10.Щелкните правой кнопкой мыши на маркере заполнения и протяните рамку так, чтобы она охватила ячейки В2 и B3. Отпустите кнопку мыши. Убедитесь, что формат ячейки B3 такой же, как и в B2. Измените сумму в ячейке B3, увеличив ее на 10%, как и было задумано

11.Выделите обе ячейки: B2 и B3. Щелкните правой кнопкой мыши на маркере заполнения и протяните рамку так, чтобы она охватила ячейки с В2 по В25. Отпустите кнопку мыши. Убедитесь, что ячейки оказались заполнены денежными суммами, находящимися в геометрической прогрессии.

12.В ячейку С1 введите текст Нарастающий итог.

13.Сделайте текущей ячейку С2. Введите в нее текст =В2. Эта простая формула гарантирует, что если значение в ячейке В2 будет изменено, то ячейка С2 все равно будет содержать верные данные.

14.Щелкните на ячейке СЗ. В строке формул щелкните на значке Изменить формулу. Щелкните на ячейке ВЗ. Убедитесь, что ссылка на эту ячейку помещена в строку формул. Нажмите клавишу +. Щелкните на ячейке С2. Нажмите клавишу Enter.

15.Снова сделайте ячейку СЗ текущей. Наведите указатель мыши на маркер заполнения, нажмите левую кнопку и протяните рамку так, чтобы она захватила ячейки с С3 по С25.

16.Щелкните на одной из ячеек столбца С и посмотрите, какая формула в ней записана. Убедитесь, что все формулы были скорректированы по принципу относительной адресации.

17.Сохраните рабочую книгу? Назвав ее Задание_1_5.

Лабораторная работа № 2. Вычисления средствами MS Excel.

Цель работы: познакомиться с назначением основных логических, математических и статистических функций; научиться пользоваться Мастером функций.

Задание 1. Математические и статистические расчеты средствами MS Excel.

1.Создайте новую книгу MS Excel.

2.На первом листе книги в ячейки А2:А13 занесите следующий набор произвольных чисел:

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

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. Щелкните на кнопке ОК и еще раз на кнопке ОК, произошло изменение ряда данных.