Раздел 1.
Электронные таблицы Microsoft Excel предназначены для выполнения расчетов, представления обрабатываемых данных в виде диаграмм, манипулирования большими объемами информации, моделирования процессов, решения вопросов оптимизации и др.
Особенность электронных таблиц состоит в том, что ячейки можно связать формулами, причем все расчеты выполняются автоматически. Любое изменение исходных данных приводит к пересчету конечного результата, что позволяет проводить вычислительные эксперименты.
Программа поставляется в составе офисного пакета MS Office и может быть установлена на компьютер пользователя либо вместе с другими программами этого пакета, либо отдельно. В программе реализован общепринятый стандарт на рабочий интерфейс офисного пакета Microsoft Office. Настройка интерфейса, операции с файлами, форматирование шрифта и т.д. осуществляется аналогично тому, как это делается в MS Word.
Практическое задание (выполняется всеми студентами)
Задание 1.
Задана функция y = 2x + 5 cos2x – 2.
Построить график функции на отрезке [–3; 4].
Процедура построения графика функции состоит из трех этапов. Первый – табулирование функции – заполнение смежных ячеек значениями аргумента из заданного интервала так, чтобы они полностью покрывали его, причем шаг сетки должен быть достаточно мал. Второй – вычисление значения функции в каждом узле сетки. Третий – построение диаграммы по полученной таблице значений функции.
У вас должен получиться примерно такой график:
Задание 2
Дана таблица результатов экзаменационной сессии. Необходимо группировать ее по значениям полей «Факультет» и «Курс», подвести итоги по полученным группам и подгруппам записей, скопировать итоговые таблицы на новые листы. Здесь имеются в виду не студенческие группы, а группы записей, содержащих одинаковое значение заданного поля, в данном случае поля «Факультет». Далее группы разбиваются на подгруппы записей, относящихся к одному и тому же курсу.
Для копирования итогов в свернутом виде следует выделить таблицу, вызвать команду Правка – Перейти – Выделить – Только видимые ячейки и далее копировать как обычно.
Таблица. Итоги сессии
№ зачетной книжки |
ФИО |
Курс |
Факультет |
Информатика |
Математика |
История |
Сумма балов |
12 |
Афоничкин |
1 |
экономический |
4 |
2 |
4 |
|
18 |
Абрамова |
1 |
экономический |
2 |
3 |
3 |
|
22 |
Бодрова |
1 |
экономический |
3 |
5 |
5 |
|
24 |
Гусева |
1 |
экономический |
5 |
5 |
2 |
|
26 |
Гусев |
1 |
экономический |
5 |
4 |
5 |
|
31 |
Кошкин |
1 |
управления |
3 |
5 |
4 |
|
32 |
Кулемин |
1 |
управления |
5 |
5 |
5 |
|
33 |
Кузьмина |
1 |
управления |
2 |
5 |
2 |
|
34 |
Кузина |
1 |
управления |
5 |
5 |
3 |
|
40 |
Соловьев |
2 |
экономический |
3 |
4 |
3 |
|
41 |
Ханина |
2 |
экономический |
4 |
4 |
2 |
|
45 |
Титова |
2 |
экономический |
3 |
2 |
4 |
|
46 |
Давыдов |
2 |
управления |
3 |
4 |
5 |
|
47 |
Коненко |
2 |
управления |
5 |
4 |
4 |
|
65 |
Алексеева |
2 |
управления |
4 |
4 |
3 |
|
67 |
Алабин |
3 |
управления |
3 |
3 |
4 |
|
69 |
Бобров |
3 |
управления |
2 |
3 |
3 |
|
73 |
Быков |
3 |
управления |
3 |
3 |
3 |
|
75 |
Васин |
3 |
управления |
5 |
4 |
3 |
|
77 |
Веденяпина |
3 |
управления |
5 |
2 |
5 |
|
93 |
Лунина |
3 |
экономический |
3 |
2 |
3 |
|
99 |
Петрова |
3 |
экономический |
5 |
5 |
5 |
|
101 |
Петров |
3 |
экономический |
5 |
5 |
4 |
|
103 |
Туманов |
3 |
экономический |
5 |
3 |
4 |
|
Задание 3.
Финансовые функции Excel предназначены для выполнения финансовых расчетов. Для удобства работы рекомендуется предварительно подготовить значения аргументов функции. В палитре каждой функции имеется кнопка Справка по этой функции, предоставляющая справочную информацию с примерами использования.
Финансовые функции имеют определенную специфику. Все аргументы, означающие расходы денежных средств, представляются отрицательными числами, а аргументы, означающие поступления, представляются положительными числами. Даты представляются в числовом формате как порядковый номер дня.
Рассчитать, какая сумма окажется на счете, если 10000 рублей положить под 12 % годовых на 10 лет с ежеквартальным начислением сложных процентов.
Замечание. При начислении сложных процентов на наращенные в предыдущем периоде суммы вновь начисляются проценты, то есть происходит многоразовое наращение.
Для решения такой задачи используется встроенная функция БС, которая возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.
Задание 4.
На нескольких листах имеются метеорологические данные за январь, февраль и март.
Январь
Город |
Температура |
Давление |
Осадки |
Москва |
-10 |
765 |
68 |
Петербург |
-12 |
764 |
45 |
Томск |
-25 |
785 |
90 |
Псков |
-9 |
760 |
46 |
Омск |
-20 |
765 |
99 |
Февраль
Город |
Температура |
Давление |
Осадки |
Москва |
-12 |
768 |
168 |
Петербург |
-14 |
750 |
145 |
Томск |
-35 |
785 |
90 |
Псков |
-8 |
760 |
146 |
Омск |
-24 |
765 |
99 |
Март
Город |
Температура |
Давление |
Осадки |
Москва |
-1 |
740 |
68 |
Петербург |
-2 |
744 |
70 |
Томск |
-17 |
748 |
72 |
Псков |
0 |
752 |
74 |
Омск |
-10 |
756 |
76 |
Получить средние значения показателей погоды за квартал и несмещенное отклонение их от средних. Сделать выводы о средних показателях в различных пунктах и их изменчивости. Результирующая таблица должна иметь вид:
Место |
Средняя температура |
Среднее давление |
Средние осадки |
Несмещенное отклонение температуры |
Несмещенное отклонение давления |
Несмещенное отклонение осадков |
|
|
|
|
|
|
|
Задание 5
Имеются следующие данные о сотрудниках.
Табельный № |
Фамилия |
Пол |
Год рождения |
Количество детей |
Отдел |
Дата поступления на работу |
Оклад |
56 |
Алексеев |
м |
1980 |
1 |
23 |
2000 |
15 000 |
57 |
Васин |
м |
1975 |
3 |
23 |
2002 |
8 000 |
58 |
Голубев |
м |
1976 |
2 |
23 |
2003 |
9 000 |
59 |
Петров |
м |
1964 |
1 |
12 |
1996 |
10 000 |
60 |
Кислов |
м |
1968 |
7 |
12 |
1996 |
23 000 |
61 |
Кузьмин |
м |
1977 |
|
12 |
2001 |
13 000 |
62 |
Любов |
м |
1975 |
2 |
11 |
2002 |
14 000 |
63 |
Михайлов |
ж |
1967 |
1 |
11 |
1990 |
25 000 |
64 |
Цай |
ж |
1980 |
1 |
11 |
2006 |
8 000 |
65 |
Иванова |
ж |
1976 |
2 |
12 |
2004 |
4 000 |
66 |
Волкова |
ж |
1975 |
2 |
12 |
2003 |
5 000 |
67 |
Иваненков |
м |
1982 |
2 |
12 |
2004 |
11 000 |
68 |
Семенов |
м |
1980 |
3 |
11 |
2005 |
4 000 |
69 |
Юдин |
м |
1982 |
|
11 |
1996 |
5 000 |
Создать форму для добавления записей в таблицу.
Используя фильтрацию, найти и скопировать на отдельные листы строки таблицы, удовлетворяющие следующим запросам:
Сотрудники отдела 12;
Табельный номер принадлежит интервалу [58; 63];
Количество детей 1 или 2;
Сотрудники, имеющие трех или более детей;
Сотрудники женского пола из отдела 11 с окладом 8 000;
Женщины, имеющие более одного ребенка;
Сотрудники, фамилии которых начинаются на букву «П»;
Сотрудники, поступившие на работу после 2000 года и имеющие оклад 8 000;
Сотрудники, работающие в отделе 12 и имеющие оклад от 8 000 до 10 000;
Сотрудники мужского пола, работающие в 23 или 11 отделе и родившиеся до 1980 года;
Выведите список сотрудников, фамилия которых заканчивается на «ов»;
Составить список сотрудников второго отдела, чей оклад на 20 % превышает средний оклад всех сотрудников.
Указание: создайте диапазон условий
Отдел
Оклад
2
=оклад 1> СРЗНАЧ(оклад)*1,2
где оклад 1 – ссылка на первую ячейку столбца «Оклад».
Выполнить сортировку по отделам, а внутри отделов по возрастанию табельных номеров;
Сортировать список по отделам, внутри отдела поместить сначала мужчин, затем женщин, их, в свою очередь, упорядочить по убыванию количества детей, а для одинакового количества детей по алфавитному порядку фамилий. Как предусмотреть возможность быстрого восстановление первоначального порядка записей?
Вычислить возраст (число полных лет) каждого сотрудника. Выполнить сортировку по отделам, а внутри отделов – по убыванию возрастов;
Построить круговую диаграмму с суммарными окладами по отделам. Скопировать суммарные оклады по отделам на новый рабочий лист;
То же для данных по средним окладам.
Задание № 6
Результаты трех экзаменов представлены на трех листах в виде списков. Создать общую ведомость
Математика
Фамилии |
Оценки |
Алексеев |
5 |
Васин |
4 |
Гальпин |
3 |
Деточкин |
4 |
Евсеев |
5 |
Лялин |
5 |
Петров |
4 |
Раков |
3 |
Информатика
Фамилии |
Оценки |
Алексеев |
4 |
Васин |
5 |
Гальпин |
3 |
Деточкин |
4 |
Евсеев |
4 |
Лялин |
4 |
Петров |
3 |
Раков |
4 |
Философия
Фамилии |
Оценки |
Алексеев |
4 |
Васин |
5 |
Гальпин |
3 |
Деточкин |
2 |
Евсеев |
2 |
Лялин |
3 |
Петров |
3 |
Раков |
3 |