Размещено на http://allbest.ru

Министерство транспорта Российской Федерации

Федерально - государственное бюджетное образовательное учреждение высшего образования

«Дальневосточный государственный университет путей сообщения»

Кафедра “ Информационные технологии и системы”

КОНТРОЛЬНАЯ РАБОТА

Операции с базами данных

по дисциплине «Информатика»

Выполнил: Жданов А.В.

студент 2 курса

Проверил: Олейник В.М.

Тында

2019г.



1. Создание учебной базы

Задание: Создать учебную базу. Учебная БД содержит поля (столбцы) исходных данных, поясняемые в таблице 1. В MS Excel делаем таблицу:

Таблица 1

Фамилия	Имя	Отчество	Пол	Дата рождения	Должность	Оклад	Семейное положения	Количество детей
Агафонов	Юрий	Александр.	м	05.02.64	механик	1 800р.	женат	3
Агеев	Сергей	Петрович	м	25.05.70	товаровед	2 500р.	женат	2
Андреев	Георгий	Алексеевич	м	03.08.44	бухгалтер	2 100р.	разведен	0
Андреев	Юрий	Дмитриевич	м	12.08.42	бухгалтер	2 100р.	вдовец	5
Андреев	Александр	Алексеевич	м	22.09.55	продавец 2кат.	2 450р.	женат	4
Андреев	Александр	Сергеевич	м	19.02.51	сторож	1 300р.	холост	0
Аникина	Ирина	Григорьевна	ж	20.09.73	продавец 2кат.	2 200р.	не замужем	4
Анисимов	Григорий	Сергеевич	м	07.07.64	грузчик	1 200р.	женат	1
Аннушкин	Сергей	Петрович	м	02.11.49	зам. директора	3 500р.	вдовец	2
Антоненко	Иван	Анатольевич	м	26.12.76	продавец 2кат.	2 060р.	женат	1
Атаманюк	Алексей	Федорович	м	13.01.77	зав. секцией	2 800р.	женат	3
Багирова	Елена	Констант.	ж	09.12.74	продавец 1кат.	2 400р.	замужем	1
Базина	Людмила	Петровна	ж	07.04.54	экономист	2 900р.	замужем	1
Баранова	Ирина	Петровна	ж	03.05.77	бухгалтер	1 960р.	вдова	5
Березкина	Алла	Сергеевна	ж	15.02.71	товаровед	2 500р.	замужем	3
Бирюкова	Александра	Алексеевна	ж	18.09.80	продавец 2кат.	2 060р.	замужем	4
Богданова	Ирина	Игоревна	ж	06.09.75	продавец 3кат.	1 740р.	вдова	0
Бондарчук	Сара	Георгиевна	ж	08.12.68	зав. секцией	2 800р.	замужем	1
Борисов	Александр	Алексеевич	м	16.05.75	экспедитор	2 200р.	женат	1
Борисова	Татьяна	Александровна	ж	21.08.48	зав. секцией	2 600р.	вдова	1
Бурова	Татьяна	Александр.	ж	11.05.61	кассир-контролер	2 300р.	замужем	2
Васильева	Татьяна	Борисовна	ж	24.07.59	юрист	2 900р.	вдова	2
Гарин	Александр	Петрович	м	22.01.65	продавец 1кат.	2 400р.	вдовец	5
Гранина	Нина	Алексеевна	ж	21.10.78	администратор	2 600р.	замужем	1
Грачева	Алла	Алексеевна	ж	09.09.71	гл. бухгалтер	3 000р.	разведена	2
Григорьев	Александр	Сергеевич	м	26.04.60	коммерч. агент	3 200р.	холост	2
Данилова	Татьяна	Александр.	ж	02.11.59	продавец 2кат.	2 060р.	замужем	0
Дмитриева	Ирина	Игоревна	ж	01.11.65	бухгалтер	1 960р.	замужем	1
Дорофеева	Алла	Сергеевна	ж	03.03.78	кассир-контролер	2 400р.	не замужем	0
Дрозд	Татьяна	Александр.	ж	30.07.68	зам. зав. секцией	2 600р.	вдова	4
Егорова	Нина	Алексеевна	ж	15.11.68	уборщица	1 500р.	замужем	3
Ефимова	Алла	Сергеевна	ж	21.11.60	продавец 2кат.	2 100р.	замужем	2
Жук	Вера	Алексеевна	ж	02.01.59	продавец 1кат.	2 400р.	не замужем	1
Зверева	Бэла	Гавриловна	ж	12.04.49	продавец 3кат.	1 840р.	замужем	1
Иванова	Нина	Алексеевна	ж	10.03.76	продавец 2кат.	2 100р.	не замужем	0
Ильина	Людмила	Николаевна	ж	04.08.75	уборщица	1 500р.	разведена	0
Каро	Наталия	Констант.	ж	11.07.64	продавец 3кат.	1 750р.	не замужем	1
Ким	Ирина	Олеговна	ж	12.06.69	продавец 3кат.	1 800р.	не замужем	0
Киреева	Нина	Борисовна	ж	03.03.70	фасовщица	1 600р.	замужем	1
Китуничева	Вера	Дмитриевна	ж	07.07.64	ст. кассир	2 500р.	замужем	2
Коваленко	Анна	Давыдовна	ж	17.07.73	продавец 3кат.	1 800р.	разведена	3
Козлова	Елена	Николаевна	ж	26.02.63	продавец 3кат.	1 900р.	замужем	1
Лазаренко	Алла	Сергеевна	ж	01.06.65	продавец 3кат.	1 900р.	замужем	1
Леонова	Нина	Алексеевна	ж	05.10.75	кассир	2 200р.	замужем	0
Мартынихина	Анна	Дмитриевна	ж	01.10.65	приемщик посуды	1 600р.	замужем	2
Милошевич	Алла	Александр.	ж	12.09.75	продавец 2кат.	2 060р.	замужем	1
Минина	Лиана	Зурабовна	ж	02.11.75	кассир	2 250р.	замужем	2
Минц	Александр	Сергеевич	м	07.02.56	охранник	2 160р.	разведен	1
Михайлов	Алексей	Петрович	м	05.08.71	приемщик посуды	1 600р.	женат	0
Москвина	Алла	Сергеевна	ж	01.08.77	зам. зав. секцией	2 400р.	замужем	3
Мухина	Наталья	Олеговна	ж	29.06.62	кассир	2 300р.	замужем	1
Наливайко	Ирина	Олеговна	ж	02.07.75	повар	2 300р.	замужем	1
Нарышкина	Елена	Емельян.	ж	25.06.75	фасовщица	1 900р.	замужем	1
Никодимов	Петр	Александр.	м	31.01.61	продавец 3кат.	1 900р.	разведен	0
Николаев	Петр	Алексеевич	м	05.12.39	кассир	2 300р.	вдовец	1
Николаева	Наталия	Констант.	ж	23.07.61	повар	2 000р.	замужем	0
Озерова	Нина	Алексеевна	ж	05.06.64	продавец 3кат.	1 900р.	замужем	1
Павлова	Вера	Олеговна	ж	17.10.59	продавец 3кат.	1 800р.	не замужем	0
Петрова	Анна	Дмитриевна	ж	07.04.73	бухгалтер	1 900р.	не замужем	0
Петрова	Анна	Юрьевна	ж	31.08.63	продавец 3кат.	1 800р.	замужем	2
Петрушевич	Ирина	Олеговна	ж	22.01.80	продавец 3кат.	1 800р.	не замужем	0
Пушкина	Нина	Алексеевна	ж	31.01.58	кассир	2 300р.	не замужем	0

Выполнение задания: - Запускаем программу MS Excel

- Выделяем область 9 столбцов и 63 строки (в соответствии с заданием)

- Выбираем вкладку «Границы»

- В раскрывшемся меню выбираем «Все границы»

- Выбираем шрифт

- Размер шрифта

- Формат данных для каждого из полей (текстовый, числовой, денежный/финансовый или дата)

- Выбираем ячейку «А1»

- Вводим данные: «Фамилия»

- Далее заполняем все ячейки БД, согласно таблице 1.

2. Анализ базы данных отдела кадров

Задание: Описать структуру существующей БД отдела кадров с данными о работниках универсама, созданную в среде табличного процессора MS Excel.

При этом указать число полей, записей в БД и тип (формат) данных для каждого из полей (текстовый, числовой, денежный/финансовый или дата), заполнив таблицу 2.

Выполнение задания: Описание структуры базы данных о работниках универсама

Диапазон ячеек, занимаемых БД - A1: I63.

Число полей в БД - _9_.

Число записей в БД - _567_.

Таблица 2

Столбец	Наименование поля	Тип данных
A	Фамилия	Текстовый
B	Имя	Текстовый
C	Отчество	Текстовый
D	Пол	Текстовый
E	Дата рождения	Дата
F	Должность	Текстовый
G	Оклад	Финансовый
H	Семейное положение	Текстовый
I	Количество детей	Числовой

3. Сортировка базы данных

Задание: Провести двухуровневую сортировку БД согласно критериям в таблице 3.

При этом на первом этапе провести сортировку согласно первичному критерию, а на втором этапе для записей, имеющих одинаковые значения первичного критерия, предусмотреть сортировку согласно вторичному критерию. Подробно по шагам описать действия пользователя с представлением в виде рисунка диалогового окна Сортировка диапазона.

Критерии сортировки
Первичный	Вторичный
Вначале мужчины, а затем женщины	По убыванию возраста работника

Выполнение задания. В табличный процессор Excel

- выделяем ячейку - «Пол»

- в выборе пунктов инструментального меню - «Сортировка и Фильтр»

- Выбираем «настраиваемая сортировка»



- Столбец «Сортировать по» выбираем «Пол»

- Порядок выбираем «от Я до А»

- в табличном процессоре Excel.

- Выделяем ячейку - «Дата рождения»

- в выборе пунктов инструментального меню - «Сортировка и Фильтр»

- Выбираем пункт «Настраиваемая сортировка»



- «Добавить уровень»

- «Сортировка по - Дата рождения»

- «Порядок - От старых к новым»

- ОК



4. Создание выборки записей с помощью автофильтра

Задание: Используя операцию автофильтра, провести выборку записей из БД согласно приведенным в таблице 4 критериям фильтрации. Подробно по шагам описать необходимые действия пользователя.

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

Критерии фильтрации
Фамилии, начинающиеся на “Ми” или “Ни”

Выполнение задания: в табличном процессоре Excel

- Выделяем ячейку - «Фамилия».

- Вкладка - «Данные»

- Пункт меню - «Фильтр»

- Убираем галочку в квадрате на против - «Выделить всё».

- Выбираем «Текстовый фильтр»

-Выбираем «Начинается с…» или «Настраиваемый фильтр»

- Выбираем условие «начинается с.»

- Прописываем значение условия «Ми»

- Выбираем условие «начинается с.»

- Прописываем значение условия «Ни»



5. Многошаговая операция автофильтра

Задание: Используя многошаговую операцию автофильтра, провести выборку записей из БД согласно приведенным в таблице критериям фильтрации.

Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунков диалоговые окна применяемых пользовательских автофильтров.

Критерии фильтрации
Мужчины с окладом выше 2000 руб.

Выполнение задания: в табличном процессоре Excel

- Выделяем ячейку - «Пол»

- Вкладка - «Данные»

- Вкладка - «Фильтр»

- Убираем галочку в квадрате на против - «Выделить всё»

- Выбираем «М»

- ОК

Выбираем ячейку - «Оклад»

- Убираем галочку в квадрате на против - «Выделить всё»

- Выбираем «Числовой фильтр»

- Выбираем условие «Больше»

- Прописываем значение условия «2 000р.»



6. Операция с расширенным фильтром

Задание: Используя операцию расширенного фильтра, выполнить одношаговую фильтрацию согласно критериям задания 5. Соответствующий блок критериев расположить над таблицей БД. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунков используемую часть такого блока и диалоговое окно расширенного фильтра.

Выполнение задания: В табличном процессоре Excel

- Зажимаем левой кнопкой мыши обозначение строки 1

- Тянем курсором вниз 5-6 строк

- Нажимаем правую кнопку мыши (появляется меню)

- Выбираем пункт меню «Вставить»

- Выделяем две верхние строки БД

- Нажимаем правую кнопку мыши (появляется меню)

- Выбираем пункт «копировать»

- Выбираем ячейку «А1»

- Нажимаем сочетание клавиш «CTRL+V» (вставить)

- В «блоке критериев» со второй строки удаляем все значения.

- Прописываем нужные для задания критерии

- Выбираем вкладку «Данные»

- В поле «Фильтр» выбираем пункт «Дополнительно»

- Откроется диалоговое окно «Расширенный фильтр»

- Нажимаем кнопку выбора диапазона в строке «Исходный диапазон»



- Выделяем область БД

- В окне «Расширенный фильтр - Исходный диапазон» нажимаем клавишу

- Откроется диалоговое окно «Расширенный фильтр»

- Нажимаем кнопку выбора диапазона в строке «Диапазон условий»

- Выделяем область «блока критериев»

- В окне «Расширенный фильтр - Диапазон условий» нажимаем клавишу

- Нажимаем «Ок»



7. Реализация запроса в базе данных

Задание: Реализовать запрос к БД, используя функции категории Работа с базой данных. Подробно по шагам описать необходимые действия пользователя.

При этом представить в виде рисунка используемую часть блока критериев и привести соответствующую расчетную формулу (см. ниже пример выполнения задания).

Запрос к базе данных
Сумма окладов продавцов любых категорий

Выполнение задания: В табличном процессоре Excel.

- Выделяем несколько ячеек ниже области БД(A-F66)

- Выбираем вставку «Главная»

- Пункт меню «Границы»

- В раскрывшемся меню выбираем «Все границы»

- Далее пункт меня «Объединить и поместить в центре»

- В получившуюся ячейку вводим текст задания

- Выбираем ячейку G66

- Выбираем вставку «Главная»

- Пункт меню «Границы»

- В раскрывшемся меню выбираем «Все границы»

В меню выбора функций необходимо выбрать функцию, т.к. аргументов несколько функция «СУММЕСЛИ» не подходит. Функция «СУММЕСЛИМН» также не подходит т.к. выполняет для множества условий логическую функцию «И». Выбираем функцию «СУММ», но вместо каждого из аргументов подставим функцию «СУММЕСЛИ» проставляя в диапазон условий нужные значения.

- Нажимаем клавишу ввода функции

- В открывшемся окне выбираем «СУММ»

- Нажимаем «ОК»



- В окне ввода аргументов нажимаем «ОК» аргументы будем прописывать в строке формул

- В поле 1 аргумента начинаем вписывать функцию «СУММЕСЛИ», откроется меню выбора, выбираем нужную.

- Задаём аргумент «диапазон условий»

- Выделяем столбец «Должность»

- Каждый аргумент любой функции отделяется знаком «;»

- Задаём «критерий отбора», по условиям задания критериев три: «продавец 1кат.», «продавец 2кат.», «продавец 3кат.».

- Выбираем любую ячейку со значением «продавец 1кат.»

- Задаём аргумент «диапазон суммирования»

- Выделяем столбец «Оклад»

- Закрываем скобку и ставим «;», т.к. это был первый аргумент функции «СУММ»

- Второй и третий аргумент будут подобны, меняться будет только критерий.

- Выделяем первый аргумент левой кнопкой мыши.

- Нажимаем сочетание клавиш «CTRL+C» (копировать)



- Вставляем его на места второго и третьего аргументов сочетанием клавиш «CTRL+V»

- Меняем критерий отбора во втором и третьем аргументах на ячейки со значениями «продавец 2кат.» и «продавец 3кат.» соответственно.

- Закрываем поле аргументов функции «СУММ» скобкой.

Нажимаем «ENTER» для завершения ввода.

- Результат вычислений появится в ячейке G66

- Если где-то была допущена ошибка система уведомит об этом.



8. Построение сводной таблицы

Задание: Реализовать перекрестный запрос к БД, используя операцию построения сводной таблицы. Подробно по шагам описать необходимые действия пользователя. При этом представить в виде рисунка диалоговое окно шага 3 из 4 Мастера сводных таблиц (см. ниже пример выполнения задания). Варианты запросов приведены в таблице

Запрос к базе данных
Количество работников в каждой должности отдельно для женщин и мужчин

Выполнение задания: Выбираем вкладку «Вставка»

- Выбираем пункт «Сводная таблица»

- Появится диалоговое окно

- В строке «Таблица или диапазон» нажимаем клавишу выбора

- Выделяем всю область БД

- Ниже, в строке «Диапазон» нажимаем клавишу выбора

- Выбираем любую ячейку рядом с БД, например, К1

- Нажимаем «ОК»

Появится макет таблицы и меню «Поля сводной таблицы»

табличный процессор автофильтр сортировка

- В меню «Поля сводной таблицы»

- Перетаскиваем в поле «столбцы» параметр «Пол»

- Перетаскиваем в поле «значения» параметр «Пол»

- Перетаскиваем в поле «строки» параметр «Должность»

- На месте макета появится таблица с рассчитанными значениями



Литература

1. Информатика: Базовый курс. / Симонович С.В. и др. - СПб.: Питер, 2001. - 640с.

2. Чекотовский Э.В. Графический анализ статистических данных в Microsoft Excel, 2000. - М., 2002. - 464с.

3. Столяров А.М., Столярова Е.С. Excel 2000 для себя. - М., 2002. - 336с.

4. Гарнаев. А. Excel, VBA, Internet в экономике и финансах. - СПб.: БХВ-Петербург, 2002. - 816с.

Размещено на Allbest.ru

...