Файл: 2_MS_Excel.pdf

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

получено при сложении С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 занесите следующий набор произвольных чисел: