Лабораторная работа №5 Тема: Использование таблицы в качестве базы данных Цель работы: Научиться работать с данными электронно. Использование таблицы в качестве базы данных

• Название: Использование таблицы в качестве базы данных
• Анкор: Лабораторная работа №5 Тема: Использование таблицы в качестве базы данных Цель работы: Научиться работать с данными электронно
• Дата: 28.03.2022
• Размер: 77.57 Kb.
• Имя файла: Laboratornaya_rabota5_SamRab.docx
• Тип: Лабораторная работа; #423377

Лабораторная работа №5

Тема: Использование таблицы в качестве базы данных

Цель работы: Научиться работать с данными электронной таблицы как с базой данных. Освоить приемы сортировки, поиска и фильтрации данных.
Порядок выполнения работы

1. Подготовьте таблицу по образцу:

Ведомость

учёта работы транспорта в автохозяйстве за месяц

№ п/п	Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
1	ГАЗ-66	СР 12-37	1000	720	7
2	ЗИЛ-130	ТД 21-18	550	610	6
3	ЗИЛ-130	СР 28-12	600	300	14
4	УАЗ-3151	НФ 19-67	600	680	5
5	ГАЗ-66	АЯ 18-16	600	0	30
6	УАЗ-3962	НС 96-12	1000	1200	0
7	УАЗ-3962	НС 84-17	0	0	30

Замечание: базой данных в Microsoft Excel называется таблица, оформленная по определенным правилам и состоящая из однотипных строк, которые называются записями. Столбцы таблицы являются полями записи в базе данных. Существует ряд ограничений, накладываемых на структуру базы данных:

- заголовки полей базы данных должны содержать уникальные (неповторяющиеся) имена полей и располагаться строго в одной строке и отдельных ячейках (объединение ячеек не допускается!);

- таблицу базы данных следует отделить от других данных рабочего листа пустой строкой и столбцом;

- поля записей должны содержать однородные данные, т.е. только числа или только текст.

2. Скопируйте созданную таблицу на следующие два листа и последовательно выполните на каждом из них сортировку записей базы данных в соответствии с заданиями :

• 
  по фактическому пробегу в возрастающем порядке;
  
• 
  по количеству дней простоя в убывающем порядке;
  
• 
  по маркам автомобилей, а внутри каждой полученной группы по плановому пробегу.
  

Замечание: инструменты для работы с базами данных можно найти на вкладке Главная или на специальной вкладке Данные. Так например, для сортировки данных созданного списка (базы данных) можно воспользоваться командой Сортировка и фильтр - >Настраиваемая сортировка…(см. рис. …) В этом случае сортировка производится через диалоговое окно, где можно добавить несколько уровней сортировки с помощью кнопки .



Рисунок 5.1.
3. Восстановите первоначальный порядок записей в таблице на Листе1.

5. Используя инструмент Форма… выполните следующие действия с базой данных на Листе 1:

Замечание: с помощью Формы удобно просматривать данные списка построчно, изменять, добавлять или удалять записи, а так же осуществлять поиск записей по определенному критерию. Указанный инструмент можно вывести на Ленту или на Панель быстрого доступа с помощью команды Файл –> Параметры –> Настройка ленты (или Панель быстрого доступа), в списке Выбрать команды установить категорию Команды не на ленте и выбрать в соответствующем списке команд Форма…
- пролистывая записи с помощью Формы найдите сведения об автомобиле с номером СР 28-12.

- используя критерии отбора, с помощью Формы последовательно определите (после вопросов приводятся правильные ответы):

а) у каких автомобилей фактический пробег составил более 500 км:

№ п/п	Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
1	ГАЗ-66	СР 12-37	1000	720	7
2	ЗИЛ-130	ТД 21-18	550	610	6
4	УАЗ-3151	НФ 19-67	600	680	5
6	УАЗ-3962	НС 96-12	1000	1200	0

б) у какого из автомобилей ЗИЛ-130 простой составил менее 10 дней:

№ п/п	Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
2	ЗИЛ-130	ТД 21-18	550	610	6

- используя Форму, добавьте в таблицу запись:

№ п/п	Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
8	ЗИЛ-130	СК 14-18	800	640	4

- удалите введённую новую запись и закройте окно формы.
6. Применяя Автофильтр, последовательно осуществите выбор записей из базы данных в соответствии с указанными ниже условиями (после каждого задания приводится правильный результат работы).

Замечание: инструмент Автофильтр подключается с помощью команды Сортировка и фильтр -> Фильтр на вкладке Главная или на вкладке Данные с помощью команды Фильтр. Предварительно нужно сделать активной любую ячейку таблицы. После подключения автофильтра в заголовочных полях таблицы появятся кнопки со стрелочками, с помощью которых можно формировать условия отбора.

а) у каких автомобилей простой составляет от 5 до10 дней включительно:

№ п/п	Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
1	ГАЗ-66	СР 12-37	1000	720	7
2	ЗИЛ-130	ТД 21-18	550	610	6
4	УАЗ-3151	НФ 19-67	600	680	5

б) у какого автомобиля плановый пробег составляет 600 км, простой менее 15 дней, фактический пробег – более 500 км:

№ п/п	Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
4	УАЗ-3151	НФ 19-67	600	680	5

в) у каких автомобилей фактический пробег составляет менее 500 км или более 1000 км, а простой – менее 15 дней:

№ п/п	Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
3	ЗИЛ-130	СР 28-12	600	300	14
6	УАЗ-3962	НС 96-12	1000	1200	0

г) отмените все условия фильтрации и верните таблицу к первоначальному виду.

7. Применяя Расширенный фильтр, осуществите выбор записей из базы данных в соответствии с указанными ниже условиями отбора. Результаты поиска копируйте ниже основной таблицы базы данных (состав полей и приводимая в них информация должны соответствовать приведённым ниже каждого задания образцам).

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

- первая строка области критериев состоит из заголовков полей, которые участвуют в условиях отбора;

- в случае, когда условие отбора содержит формулу, заголовок соответствующего поля в области критериев не должен совпадать ни с одним из заголовков полей основной таблицы базы данных;

- условия отбора по каждому полю записываются с использованием операторов сравнения (>,<,= ,< >, >=, <=) и формул;

- все условия, записанные в одной строке, при фильтрации применяются одновременно (соответствует объединению условий логическим оператором И), а в разных строках – последовательно (соответствует объединению условий логическим оператором ИЛИ).

а) у какого автомобиля планировался пробег 1000 км, фактический пробег составил более 500 км, простой – менее 6 дней:

Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
УАЗ-3962	НС 96-12	1000	1200	0

б) у каких автомобилей простой составляет 0, 7 или 14 дней:

Марка автомашины	Номер	Простой, дн
ГАЗ-66	СР 12-37	7
ЗИЛ-130	ТД 28-12	14
УАЗ-3962	НС 96-12	0

в) для какого автомобиля ЗИЛ-130 или ГАЗ-66 планировался пробег менее 1000 км, а фактический пробег составил более 500 км:

№ п/п	Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км	Простой, дн
2	ЗИЛ-130	ТД 21-18	550	610	6

г) какие автомобили имеют фактический пробег более 1000 км или простой от 5 до 10 дней:

№ п/п	Марка автомашины	Номер	Фактический пробег, км	Простой, дн
1	ГАЗ-66	СР 12-37	720	7
2	ЗИЛ-130	ТД 21-18	610	6
4	УАЗ-3151	НФ 19-67	680	5
6	УАЗ-3962	НС 96-12	1200	0

д) у каких автомобилей фактический пробег превышает плановый:

Марка автомашины	Номер	Плановый пробег, км	Фактический пробег, км
ЗИЛ-130	ТД 21-18	550	610
УАЗ-3151	НФ 19-67	600	680
УАЗ-3962	НС 96-12	1000	1200

8. Покажите результаты работы преподавателю.
9. Завершите работу с MS Excel, сохранив результаты.
Задания для самостоятельной работы

Вариант 1

Создайте таблицу поступления товаров в магазин со следующими заголовками столбцов: Дата поступления, Наименование товара, Отдел, Количество, Цена, Стоимость товаров в партии. Заполните таблицу данными для двух отделов (Одежда, Обувь) и трех дней поступления товаров (пальто, костюм, сапоги, туфли). Рассчитайте стоимость товаров.

1. 
   Выполните сортировку по отделам, а внутри каждого отдела по наименованию товара.
   
2. 
   Выберите сведения о костюмах, поступивших конкретного числа, тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  

3. 
   Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:
   

• 
  какие товары из отдела Одежда поступили в определенную дату;
  
• 
  какие пальто имеют стоимость больше 3000 руб. В результирующую таблицу включить поля: Дата поступления, Наименование товара, Количество, Стоимость;
  
• 
  какая обувь имеет цену больше средней по отделу.
  

Вариант 2

Создайте таблицу успеваемости студентов 456 и 457 групп, содержащую следующие поля: Ф.И.О., № группы, Дата сдачи, Предмет, Балл. Заполните таблицу данными для четырех студентов, сдающих экзамены по двум предметам (Математика, Информатика).

1. 
   Выполните сортировку по № группы, а внутри каждой группы по возрастанию балла.
   
2. 
   Выберите сведения о студентах, сдавших Информатику с баллом больше 4.0 тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  

3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:

• 
  какие студенты сдали математику и информатику с баллом больше 4,5. В результирующую таблицу включите поля: Ф.И.О., Предмет, Балл;
  
• 
  какие студенты из группы 457 получили баллы меньше 3 или больше 4;
  
• 
  какие студенты получили балл на 20% меньше среднего по двум группам.
  

Вариант 3

Создайте таблицу реализации печатной продукции, с заголовками столбцов: Дата реализации, Название, Тип издания (газета, журнал, календарь), Цена одного экземпляра, Количество, Сумма от реализации. Заполните данными десять строк таблицы для трех дней продаж. Рассчитайте сумму от реализации.

1. 
   Выполните сортировку по дате реализации, а внутри каждой даты по названию.
   
2. 
   Выберите сведения о журналах, проданных на сумму более 400 рублей тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  
  3. 
     Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:
     
     • 
       какие журналы или календари имеют цену меньше 30 рублей;
       
     • 
       какие издания были реализованы конкретного числа в количестве более 20 экземпляров. В результирующую таблицу включить поля: Дата, Название, Тип издания, Количество;
       
     • 
       какие типы изданий были проданы на сумму меньше средней по всем изданиям.
       

Вариант 4

Создайте таблицу реализации дисков для изучения английского, немецкого и французского языков со следующими заголовками столбцов: Месяц продаж, Наименование диска (Английский язык и т.д.), Тип (разговорный, деловой), Цена одного диска, Кол-во проданных дисков, Сумма от реализации. Заполните таблицу данными о продажах за три месяца (не менее 10 строк). Рассчитайте сумму от реализации.

1. 
   Выполните сортировку по месяцу продаж, а внутри каждого месяца по количеству проданных дисков.
   
2. 
   Выберите сведения о дисках делового типа, проданных в конкретном месяце, в количестве более 200 тремя способами:
   
   • 
     с помощью формы;
     
   • 
     с помощью автофильтра;
     
   • 
     с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
     
3. 
   Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:
   
   • 
     какие диски для изучения английского или немецкого языков разговорного типа были проданы в конкретном месяце;
     
   • 
     в каком месяце диски делового типа для изучения французского языка были проданы на сумму более 2000 рублей. В результирующую таблицу включить поля: Месяц, Наименование диска, Тип, Сумма от реализации;
     
   • 
     какие диски были проданы в количестве на 10% больше минимального количества.
     

Вариант 5

Создайте таблицу поступления в магазин компьютерной продукции, содержащую следующие поля: Дата поступления, Наименование товара (системный блок, монитор, принтер), Фирма изготовитель, Цена, Количество, Стоимость товаров в партии. Заполнить таблицу данными по двум дням работы магазина и двум фирмам изготовителям (не менее 10 строк). Рассчитатйте стоимость товаров в партии.

1. 
   Выполните сортировку по фирме изготовителю, а внутри каждой фирмы по наименованию товара.
   
2. 
   Выберите сведения о фирмах, у которых цена монитора меньше 10000 рублей, тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  

3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:

• 
  какие товары поступили конкретного числа в количестве более 20 штук. В результирующую таблицу включить поля: Дата, Наименование товара, Количество;
  
• 
  какие фирмы поставили мониторы или принтеры на общую стоимость меньше 80000 рублей;
  
• 
  системные блоки какой фирмы имеют цену меньше средней цены системных блоков.
  

Вариант 6

Создайте таблицу начисления заработной платы сотрудникам за последние три месяца работы, используя следующие заголовки столбцов: Ф.И.О., Дата зачисления, Отдел (бухгалтерия, канцелярия), Месяц, Оклад. Заполните таблицу данными для четырех сотрудников.

1. 
   Выполните сортировку по отделу, а внутри отдела по Ф.И.О.
   
2. 
   Выберите сведения о сотрудниках, которые были зачислены с окладом 5000 рублей в январе тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  

3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:

• 
  сотрудники каких отделов имеют оклады более 10000 рублей или менее 3000 рублей;
  
• 
  какого числа в бухгалтерию была зачислена Петрова Ольга Ивановна. В результирующую таблицу включить поля: Ф.И.О., Дата зачисления, Отдел;
  
• 
  какие сотрудники из канцелярии имеют оклад на 10% больше среднего оклада по всем отделам.
  

Вариант 7

Создайте таблицу отгрузки нефтепродуктов со следующими полями: Дата отгрузки, Покупатель, Наименование товара (нефть, мазут, и т.д.), Количество (т.), Цена, Стоимость отгруженного товара. Заполните таблицу данными для трех покупателей (Завод 1, Завод 2, Завод 3) и двух дней работы. Рассчитайте стоимость отгруженного товара.

1. 
   Выполните сортировку по покупателю, а внутри покупателя по наименованию товара.
   
2. 
   Выберите сведения о товарах, которые были отгружены для Завода1 конкретного числа тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  

3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:

• 
  сведения о нефти или мазуте, которые были отгружены на Завод 3 в количестве более 5 тонн. В результирующую таблицу включите поля: Покупатель, Наименование товара, Количество;
  
• 
  какого числа нефть была отгружена стоимостью больше 100000 рублей;
  
• 
  какие покупатели получили нефть в количестве, меньше среднего количества отгруженной нефти всем покупателям.
  

Вариант 8

Создайте таблицу реализации продукции мебельной фабрики со следующими полями: Месяц, Наименование товара, Тип (кухня, спальня, детская), Цена гарнитура, Количество (шт.), Сумма от реализации. Заполните таблицу данными для двух месяцев продаж, используя 2-3 наименования товара по каждому типу мебели (например, спальни «Престиж» и «Ирина»). Рассчитайте сумму от реализации.

1. 
   Выполните сортировку по типу товара, а внутри каждого типа по цене.
   
2. 
   Выберите сведения о кухнях, которые были реализованы в конкретном месяце тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  

3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:

• 
  сведения о спальнях, по цене больше 50000 рублей или меньше 25000 рублей;
  
• 
  какой товар был реализован в конкретном месяце на сумму больше 20000 рублей. В результирующую таблицу включите поля: Месяц, Наименование товара, Тип, Сумма от реализации;
  
• 
  какие товары были реализованы в количестве на 20% больше среднего по всем товарам.
  

Вариант 9

Создайте таблицу заказов авиабилетов туристическими агентствами, используя следующие поля: Дата вылета, Страна прибытия (Турция, Испания, Египет), Название тур. агентства, Цена билета, Количество билетов, Стоимость заказа. Заполните таблицу данными для двух тур. агентств и трех дат вылета.

1. 
   Выполните сортировку по названию тур. агенства, а внутри каждого агентства по стране.
   
2. 
   Выберите сведения о странах, в которые были реализованы билеты в количестве больше 100 в конкретном месяце тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  

3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:

• 
  сведения об агентствах, которые заказали билеты в Испанию на сумму больше 100000 рублей;
  
• 
  в какие из стран Турцию или Испанию конкретное агентство заказало билеты в количестве меньше 100 штук. В результирующую таблицу включить поля: Страна, Название агентства, Цена, Количество;
  
• 
  в какую страну количество заказанных билетов меньше среднего количества билетов по всем странам.
  

Вариант 10

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

1. 
   Выполните сортировку по факультету, а внутри факультета по фамилии абитуриента.
   
2. 
   Выберите сведения об абитуриентах, которые сдавали экзамены конкретного числа по русскому языку тремя способами:
   

• 
  с помощью формы;
  
• 
  с помощью автофильтра;
  
• 
  с помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
  

3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:

• 
  какие абитуриенты сдавали экзамены по математике и русскому языку на конкретный факультет. В результирующую таблицу включите поля: Фамилия, Факультет, Название экзамена;
  
• 
  какие абитуриенты по экзаменам получили оценки 4 или 5;
  
• 
  на какие факультеты оценки студентов по математике оказались меньше средней оценок по всем предметам.