Lab09
.pdf
Для этого необходимо выполнить следующие действия.
1.Выделим всю таблицу и выполним команду Вставка/(таблицы)Сводная таблица. укажем диапазон таблицы (Он должен быть указан автоматически, если перед созданием сводной таблицы мы выделили её). Укажем поместить таблицу на новый лист, и нажмём ОК.
2.Выберем абонентов справа в списке полей сводной таблицы. Добавим
81
Дату в Название столбцов щелкнув правой кнопкой мыши на элементе
дата и выбрать Название столбцов, аналогично добавьте Количество исходящих звонков на мобильный в Значения.
3.Вы видите перед собой подсчёты. Но это только сумма по количеству исходящих звонков на мобильный за месяц. Поэтому создадим ещё одну сводную таблицу.
4.Выделим всю таблицу и выполним команду Вставка/(таблицы) Сводная таблица. укажем диапазон таблицы (Он должен быть указан автоматически, если перед созданием сводной таблицы мы выделили её). Укажем поместить таблицу на существующий лист, и укажем диапазон: Лист4!$A$17 и нажмём ОК.
5.Выберем абонентов справа в списке полей сводной таблицы нажав на правую кнопку мыши и выберем добавить в название строк. Аналогично добавим Дату в название столбцов, а Количество исходящих звонков на домашний в Значения.
6.Готово. Теперь выделим ячейки А31 и В31. Придадим им цвет и границы.
В А31 напишем Количество потраченных $ в месяц. Теперь выделим ячейку В31. В строке формул введём следующую формулу:
=AF14*Тарификация!A2 + AF28*Тарификация!B2.
Заключение
Данная база данных позволяет вычислить сумму затрат на мобильную связь за месяц.
Важно! Вводить новых абонентов и информацию про них через форму, путем : ДАННЫЕ – ФОРМА. Таким образом, если каждый день вписывать данные с телефона можно проверить, правильно ли вас обслуживает оператор сотовой связи.
3.Контрольные вопросы
1.Что такое электронная таблица?
2.Что такое электронный процессор?
3.Какие структурные элементы содержит электронная таблица?
4.Как задается адрес ячейки, адрес диапазона ячеек?
5.С какими типами данных работает MS EXCEL?
6.Какой символ нужно нажать в MS EXCEL, чтобы начать ввод формул?
7.В чем отличие между абсолютными и относительными ссылками в MS
EXCEL?
8.Как в MS EXCEL записать абсолютную ссылку на ячейку?
9.Как в MS EXCEL записать относительную ссылку на ячейку?
10.Что может быть аргументом функции MS EXCEL?
82
11.Каким способом можно вызвать список категорий функций MS EXCEL? 12.Какие типы диаграмм позволяет использовать MS EXCEL?
13.Из каких объектов состоит диаграмма в MS EXCEL?
14.Что является исходными данными для диаграмм в MS EXCEL? 15.Позволяет ли MS EXCEL строить трехмерные поверхности? 16.Что означает сообщение об ошибке #### в ячейке MS EXCEL? 17.Что означает сообщение об ошибке #ДЕЛ/0! в ячейке MS EXCEL? 18.Что означает сообщение об ошибке #Н/Д! в ячейке MS EXCEL? 19.Что означает сообщение об ошибке #ИМЯ? в ячейке MS EXCEL?
20.Что означает сообщение об ошибке #ПУСТО! в ячейке MS EXCEL? 21.Что означает сообщение об ошибке #ЧИСЛО! в ячейке MS EXCEL? 22.Что означает сообщение об ошибке #ССЫЛКА! в ячейке MS EXCEL? 23.Как осуществить сортировку данных в MS EXCEL?
24.Можно ли сортировать данные в MS EXCEL по нескольким столбцам? 25.Для чего служит фильтр в MS EXCEL?
26.Как задать фильтр в MS EXCEL?
27.Сколько параметров можно указать при фильтрации в MS EXCEL? 28.Для чего нужен расширенный фильтр?
29.Организация работы со сводными таблицами
30.Как осуществляется защита данных в Exсel?
31.Консолидация данных в EXCEL.
32.Работа с базами данных в EXCEL.
4. Задания
4.1 Тема «Составление простых таблиц в MS Excel»
I. Создайте таблицу как показано на рисунке в соответствии с указаниями расположенными после рисунка.
83
1.Произведите набор шапки таблицы (строки 1-3) и введите столбцы А, В, С до 13 строки, «Начисления» и «Налоги» выравниваются методом объединения ячеек.
2.Введите формулу для вычисления «Районные» (столбец D) – 15%.
3.Скопируйте формулу для остальных.
4.Аналогичным образом наберите формулы для всех остальных видов начислений и налогов:
Колонка Е «Всего (начисления)» = «Оклад» + «Районные»;
Колонка F «Пенсионный» = «Всего (начисления)» *1%
Колонка G Если «Всего (начисления)» - «Пенсионный» больше чем «Мин. зарплата», то «Подоходный» = («Всего (начисления)» - «Пенсионный» - «Мин. зарплата»)*12%, в противном случае «Подоходный» = 0;
Колонка Н «Всего (налоги)» = «Пенсионный» + «Подоходный»
Колонка I «На руки» = «Всего (начисления)» - «Всего (налоги)»
5.Введите формулы для подсчета суммы, среднего, максимального и минимального значений по колонкам С-I (используя, мастер функций).
6.Произведите форматирование и обрамление таблицы.
7.Постройте график по результатам расчета:
84
II. Создайте таблицу как показано на рисунке в соответствии с указаниями расположенными после рисунка.
1.Произведите набор шапки таблицы (строки 1-5) и введите столбцы A, B, C, D до 15 строки.
2.Введите формулу для вычисления «Прописью», используя мастер функций (столбец Е).
3.Скопируйте формулу для остальных студентов.
4.Введите формулы для подсчета количества оценок по каждой категории.
5.Подсчитайте строку «Итого».
85
6. Постройте график по результатам расчетов.
4.2 Тема «Использование математических функций в Excel»
Постройте график заданной функции (вариант выбирается исходя из последней цифры в списке студентов).
№ |
Функция |
варианта |
|
0 |
1 cos(sin( x 2)) |
|
|
|
|
|
|
|
5 x 9 |
|||||||||||||
|
|
|
|
x |
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
y |
2 5 |
|
4 log 8 x |
|
|
|
|
|
|
|
|
|
|
9 x 17 |
||||||||
|
arcctg(x3 |
2x 3) |
|
|
|
|
17 x 22 |
|||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
log |
3 |
(x 7) e5 x5 |
|
|
|
|
|
|
|
4 x 8 |
||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
y |
arcsin( 25 |
x x3 |
|
|
|
2 |
|
) |
|
|
|
8 x 12 |
||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
x |
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
12 x 20 |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
sin( |
|
|
x |
2 |
x 2) |
|
|
|
|
|
|
|
|||||||||
|
|
|
|
|
|
|
|
|
|
|
||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
arctg(ln 3 (x 4)) |
|
|
|
3 x 10 |
||||||||||||||||
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||
y |
(4 1 2x5 sin x 4) |
10 x 16 |
||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
lg( 6 x 2 ) |
7 |
x |
|
|
|
|
16 x 25 |
|||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
3 |
|
|
|
|
|
ln( x 2 1) |
|
|
|
|
|
|
|
|
|
|||||||
|
arcsin( |
|
|
|
|
|
|
|
|
) |
|
|
|
|
|
|
|
2 x 7 |
||||
|
|
|
100 x |
|
|
|
|
|
|
|
|
|
|
|||||||||
|
lg( x3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
y |
|
6) |
|
(1 3 |
|
x ) |
|
|
|
|
7 x 15 |
|||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 ctg(5 3x |
3 |
) |
|
|
|
|
|
|
|
|
15 x 21 |
|||||||||
|
|
|
|
|
|
|
|
|
|
|
||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
arccos((ex 2 2) 10 x3 ) |
|
5 x 10 |
|||||||||||||||||||
y |
1 cos( x3 |
lg( 5 3x 2 )) |
10 x 18 |
|||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5x 4 |
|
3 |
x 4 3log |
|
|
|
x |
|
|
|
18 x 25 |
|||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
5 |
|
arctg(1 cos(x3 5)) |
|
5 x 9 |
||||||||||||||||||
|
|
|
||||||||||||||||||||
|
ln( 4x5 3 |
|
|
|
|
|
|
|||||||||||||||
y |
|
x x4 ) 5x 4 |
9 x 17 |
|||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
1 |
|
17 x 22 |
|
|
4tg(x sin( x 2x |
|
|
|
|
|
|
) |
||||||||||||||
|
|
|
|
|
|
|||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
x |
|
|
|
Шаг
0,2
0,1
0,3
0,2
0,3
0,4
86
№ |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Функция |
|
Шаг |
|
варианта |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
|
|
|
|
4x log |
|
4 (x |
5 |
5) |
|
|
1 x |
6 |
0,5 |
|||||||||||||||||
|
|
|
|
|
|
|
|
||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
sin( x2 |
2) |
|
6 x 15 |
|
||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||||||||
|
y cos( x 1) |
|
3 |
|
|
|
|||||||||||||||||||||||||
|
|
7 |
x |
2 ln( x |
3 |
|
7x 9) |
|
15 x 20 |
|
|||||||||||||||||||||
|
|
|
|
|
|
|
|||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0,2 |
|
|
e x 5 cos(3 |
|
x5 |
|
4) |
|
1 x 5 |
|
|
|||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
|
y ln( 4 x |
|
) |
log 3 ( |
|
|
|
|
|
|
) |
|
5 x 12 |
|
|
||||||||||||||||
|
|
x 3 |
|
|
|||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
x |
2 |
|
4x 2 |
|
|
|
|
|
12 x 19 |
|
|
||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||
|
|
arcsin( |
|
|
) |
|
|
|
|
|
|
|
|||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
3 |
|
|
|
|
|
|
|
|
|
|
|
|||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
x |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
8 |
|
|
|
|
|
|
|
|
2 |
|
1 |
1 |
|
|
|
|
|
|
|
|
|
|
|
0,4 |
|||||||
|
sin(cos( x |
|
|
|
)) |
|
|
3 x 7 |
|
|
|||||||||||||||||||||
|
|
|
x3 |
|
|
||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 sin(log 4 |
5 x 4 1) |
|
|
|
|
|
7 x 10 |
|
|
|||||||||||||||||||||
|
y |
|
|
|
|
|
|
|
|||||||||||||||||||||||
|
3 |
|
|
|
e x3 1 |
|
|
|
|
||||||||||||||||||||||
|
1 ln( x 6) |
10 x 19 |
|
|
|||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||||||||||||
9 |
|
|
|
|
1 lg( 5 sin x3 ) |
|
|
|
|
0,1 |
|||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 x 8 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
3 |
4 |
|
x |
3 |
ln x |
|
|
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||||||||
|
|
|
|
6 x 4 log 2 (x 2 |
3x 4) |
8 x 13 |
|
||||||||||||||||||||||||
|
y |
|
|
|
|||||||||||||||||||||||||||
|
arctg(sin x 4 cos(5 |
|
x 4 1)) |
13 x 18 |
|
||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Тема «MS Excel. Сводные таблицы»
1. Создайте список (см. таблицу) и сохраните его.
Год |
Месяц |
Район |
|
Закупщик |
Продукция |
Объем (т) |
|
|
|
|
|
|
|
2012 |
сентябрь |
Тюменский |
|
Иванов |
говядина |
254 |
|
|
|
|
|
|
|
2012 |
сентябрь |
Ишимский |
|
Иванов |
говядина |
12 |
|
|
|
|
|
|
|
2012 |
сентябрь |
Тобольский |
|
Николаев |
говядина |
36 |
|
|
|
|
|
|
|
2012 |
октябрь |
Упоровский |
|
Сидоров |
свинина |
569 |
|
|
|
|
|
|
|
|
|
|
87 |
|
|
|
Год |
Месяц |
Район |
|
Закупщик |
Продукция |
Объем (т) |
|
|
|
|
|
|
|
2012 |
октябрь |
Бердюжский |
|
Иванов |
картофель |
400 |
|
|
|
|
|
|
|
2012 |
октябрь |
Голышмановский |
|
Николаев |
говядина |
10 |
|
|
|
|
|
|
|
2012 |
ноябрь |
Бердюжский |
|
Сидоров |
говядина |
30 |
|
|
|
|
|
|
|
2012 |
ноябрь |
Ишимский |
|
Сидоров |
свинина |
25 |
|
|
|
|
|
|
|
2013 |
сентябрь |
Ишимский |
|
Иванов |
картофель |
42 |
|
|
|
|
|
|
|
2013 |
сентябрь |
Тобольский |
|
Николаев |
говядина |
6 |
|
|
|
|
|
|
|
2013 |
сентябрь |
Тобольский |
|
Сидоров |
говядина |
8 |
|
|
|
|
|
|
|
2013 |
октябрь |
Голышмановский |
|
Сидоров |
свинина |
78 |
|
|
|
|
|
|
|
2013 |
октябрь |
Тюменский |
|
Иванов |
картофель |
4 |
|
|
|
|
|
|
|
2013 |
октябрь |
Тюменский |
|
Николаев |
говядина |
159 |
|
|
|
|
|
|
|
2013 |
ноябрь |
Тюменский |
|
Сидоров |
говядина |
52 |
|
|
|
|
|
|
|
2013 |
ноябрь |
Тобольский |
|
Иванов |
свинина |
56 |
|
|
|
|
|
|
|
2013 |
ноябрь |
Тобольский |
|
Николаев |
картофель |
32 |
|
|
|
|
|
|
|
2013 |
ноябрь |
Тюменский |
|
Сидоров |
говядина |
23 |
|
|
|
|
|
|
|
2014 |
сентябрь |
Тюменский |
|
Сидоров |
говядина |
14 |
|
|
|
|
|
|
|
2014 |
сентябрь |
Ишимский |
|
Иванов |
свинина |
14 |
|
|
|
|
|
|
|
2014 |
сентябрь |
Ишимский |
|
Николаев |
картофель |
74 |
|
|
|
|
|
|
|
2014 |
сентябрь |
Тюменский |
|
Сидоров |
говядина |
54 |
|
|
|
|
|
|
|
2014 |
сентябрь |
Голышмановский |
|
Сидоров |
говядина |
521 |
|
|
|
|
|
|
|
2014 |
октябрь |
Тобольский |
|
Иванов |
свинина |
8 |
|
|
|
|
|
|
|
2014 |
октябрь |
Бердюжский |
|
Николаев |
картофель |
5 |
|
|
|
|
|
|
|
2014 |
октябрь |
Бердюжский |
|
Сидоров |
говядина |
45 |
|
|
|
|
|
|
|
|
|
88 |
|
|
|
|
Год |
Месяц |
Район |
Закупщик |
Продукция |
Объем (т) |
|
|
|
|
|
|
2014 |
октябрь |
Голышмановский |
Иванов |
свинина |
4 |
|
|
|
|
|
|
2014 |
октябрь |
Голышмановский |
Николаев |
картофель |
41 |
|
|
|
|
|
|
2014 |
ноябрь |
Тюменский |
Сидоров |
говядина |
12 |
|
|
|
|
|
|
2014 |
ноябрь |
Тобольский |
Сидоров |
свинина |
14 |
|
|
|
|
|
|
2014 |
ноябрь |
Голышмановский |
Иванов |
картофель |
33 |
|
|
|
|
|
|
2.С помощью Мастера сводных таблиц получите итоги по объему закупок по годам для каждого закупщика.
3.Получите сводную таблицу для анализа объема закупок по годам для каждого закупщика и района.
4.Получите сводную таблицу для анализа объема закупок по каждому району отдельно по всем видам продукции.
5.Получите сводную таблицу для анализа объема закупок по годам и районам.
6.Выдайте постраничные итоги по каждому району, году, закупщику и продукции.
7.Получите сводную таблицу по году, закупщику и продукции с постраничным итогом по каждому району.
8.Внесите изменения в исходную таблицу, добавьте пять строк, в которых обязательными должны быть следующие реквизиты: год - 2011, районы - Тюменский, Тобольский, закупщик - Петров, продукция - птица. Обновите данные во всех сводных таблицах.
9.Из последней сводной таблицы уберите поле «год».
10.Получите сводную таблицу с максимальный объем по каждому виду продукции по закупщику.
11.Получите сводную таблицу с данными по закупщику Иванову за 2012 год по всем видам продукции:
замените название «Год» на «Дата»;
скройте детали за 2012 год;
покажите детали за 2012 год.
12.Получите сводную таблицу для анализа объема закупок по закупщику и продукции:
сгруппируйте Иванова и Николаева в одну группу, Петрова и Сидорова в другую;
89
замените название «Закупщик2» на «Бригады», «Группа 1» на «Бригада 1» и
т.д.;
переместите продукцию «картофель» так, чтобы появилась возможность сгруппировать остальную продукцию и обозначить ее как «Мясопродукты»;
уберите детализацию по «Мясопродуктам».
90