Контрольные вопросы

1. Что такое массив?

2. что такое формула массива?

3. Что такое диапазон массива?

4. Какие существуют в Excel функции для работы с матрицами?

5. Правило создания формулы массива.

6. Как создать собственный тип диаграмм?

7. Как защитить листы, книги, формулы, ячейки?

8. Как работать с вложенными функциями?

Лабораторная работа №6 Создание и работа с базой данных (списками) в Excel. Связывание таблиц. Создание сводных таблиц.

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

Упражнение 1

Задание 1

Создать базу данных с полями: №n/n, ФИО, Табельный номер, Дата рождения, Отдел, Образование, Дата приема на работу, Оклад, Количество детей, Пол, Телефон.

Выделив строчку с полями базы данных, используя, команду Данные/Форма заполнить базу данных. Отформатировать базу данных Кадры.

Однотабличная база данных Кадры

№ n/n	ФИО	Табель-ный номер	Дата рождения	Отдел	Образо-вание	Дата приема на работу	Оклад	Количест-во детей	Пол	Теле-фон
1	Антонов Петр Семенович	13	01.08.1965	ОГМ	высшее	11.07.1987	20 000,00	1	м	14-65-78
2	Михайленко Лариса Дмитриевна	64	03.06.1949	ОГТ	среднее	27.06.1966	8 700,00	3	ж	13-68-69
3	Антонова Лариса Дмитриевна	234	07.09.1975	ОГТ	среднее	03.08.1993	5 000,00	1	ж	12-66-80
4	Солодов Олег Петрович	899	21.12.1971	ОГМ	высшее	04.08.1993	33 000,00	1	м	17-45-88
5	Климов Иван Тимофеевич	34	28.04.1978	КБ	среднее	15.11.1999	12 000,00		м	12-35-82
6	Герасимова Наталья Павловна	56	05.05.1974	ОГМ	высшее	06.12.1997	17 000,00	1	ж	15-45-83
7	Петренко Семен Кузьмич	456	08.03.1976	КБ	высшее	07.03.1999	22 000,00	3	м	16-89-84
8	Муравьев Геннадий Львович	86	15.06.1965	ОГТ	высшее	12.08.1987	20 000,00	2	м	13-13-03
9	Яковлев Александр Сергеевич	231	01.06.1969	ОГМ	среднее	05.03.1990	12 000,00	1	м	12-35-36
10	Иванов Иван Иванович	567	04.09.1954	КБ	высшее	15.08.1976	35 000,00	3	м	14-38-87

Задание 2

Отсортировать данные по полю ФИО в алфавитном порядке, т.е. по возрастанию команда Данные/Сортировка.

Задание 3

Найти мужчин родившихся до 1974 года. Используя форму, кнопка Критерии в соответствующих полях ДО задать условие фильтрации “М” и <01.01.1974 ,нажать на Enter ,посчитать, сколько записей соответствуют заданному критерию, используя кнопки Далее и Назад.

Задание 4

Найти женщин, имеющих более одного ребенка.

Задание 5

Используя расширенный фильтр выбрать сотрудников отдела ОГМ, чей оклад меньше 20000 руб.

Для этого необходимо создать область критериев с полями Оклад и Отдел (названия полей копируются из списка(БД), в любое свободное место листа) , под этими полями задать условие фильтрации <20000 и скопировать из базы название отдела ОГМ.

Вернуть курсор в любое место списка.

Вывод отфильтрованных данных осуществите в двух вариантах:

• вывести отфильтрованный список на место исходного;

• вывести отфильтрованный список в другое место рабочего листа.

Фильтруем список на месте: команда Данные/Фильтр/Расширенный фильтр установить флажок Фильтровать список на месте. В ДО перейдите в поле Диапазон условий и выделите область заданных критериев и ОК.

Посмотрите результаты фильтрации, сколько записей соответствует заданному критерию. Восстановите исходный список - командой меню Данные/Фильтр/Отобразить все.

Для фильтрации вторым способом, для выходного документа копируются нужные из списка поля в другое место рабочего листа (чтобы не мешать развитию базы данных). В нашем случае - ФИО, Отдел и Оклад.

Курсор верните в список (БД): далее команда Данные/Фильтр/Расширенный фильтр, в ДО

установите переключатель скопировать результат в другое место и в поле Диапазон условий выделите область критериев, а в поле поместить результат в диапазон выделяется диапазон с полями выходного документа и ОК. Посмотрите результаты фильтрации под полями выходного документа.

Задание 6

Выбрать сотрудников отдела ОГМ, чей оклад в интервале от 12 000 до 20 000 руб., Критерий для фильтрации"(Отдел = ОГМ)И(Оклад>12 000)И(Оклад<20000)" .

Повторить два варианта размещения отфильтрованного списка из задания 5.

Задание 7

Выбрать сотрудников либо из отдела ОГМ, либо тех, чей оклад <20 000руб.

Критерий для фильтрации"(Отдел = ОГМ)И(Оклад любой)ИЛИ(Отдел любой) И(Оклад<20000)", откуда несложно записать условия для задачи в области критериев в разных строках.

Фильтрацию выполнить вторым способом.

Задание 8

Найти сотрудников отдела ОГМ, имеющих оклад на 15% превышающий средний оклад по предприятию.

Область критериев сформируется следующим образом:

Отдел
ОГМ	=адрес первой ячейки в столбце Оклад>СРЗНАЧ(Столбец Оклад)*1,15

Например:

Отдел
ОГМ	=H2>СРЗНАЧ($H$2:$H$12)*1,15

В область выходных результатов скопируйте поле ФИО, Отдел и Оклад.

Упражнение 2