Лабораторная работа №8. Создание базы данных. Фильтрация баз данных, создание сводных таблиц

1 2 3

Цель работы

1 Создание базы данных.

2 Отбор данных из базы данных с помощью расширенного фильтра.

3 Добавление, удаление записей в базу данных с помощью формы.

Вариант 8.1

Данные

В таблице 20 приведены данные по почтовым отправлениям.

Таблица 20

	A	B	C	D	E	F	G	H
	Номер почтового извещения	Вид почтового отправления	Пункт отправления	Вес	Кому	Пункт назначения	Дата отправки	Дата доставки

Требуется

1 Создать таблицу на одном из листов рабочей книги.

2 Переименовать лист в «Товар».

3 Отсортировать таблицу, расположив данные по возрастанию количества товара.

4 Отсортировать таблицу, расположив данные по убыванию даты получения товара.

Заполнение таблицы

1 Поле «Номер почтового извещения» заполняется целыми числами.

2 Поле «Вид почтового отправления» заполняется одним из значений: «Письмо», «Бандероль», «Посылка».

3 Поле вес заполняется случайными числами в зависимости от значения поля «Вид почтового отправления» -

4 Письмо – до 500 гр.

5 Бандероль – от 500 гр. до 3 кг.

6 Посылка – от 3 кг. до 10 кг.

7 Для выполнения задания необходимо использовать функции ЕСЛИ, ОКРУГЛ и СЛЧИС.

8 Значения полей «Дата отправки», «Дата доставки», «Пункт назначения», «Кому», «Пункт отправления» заполняются произвольными значениями полей.

Количество записей в таблице - не менее 15. Вид почтового отправления, Пункт отправления, Пункт назначения должны повторяться.

Задания

1 Для колонки "Вес" задайте формат, отображающий килограммы и граммы.

2 С помощью расширенного фильтра вывести записи удовлетворяющие условию: посылки из города Х, весом более G кг. (наименование города выбирается произвольно из имеющегося списка, вес берется произвольно).

3 С помощью расширенного фильтра вывести записи удовлетворяющие условию: почтовые пересылок между городами К и L (пункт назначения и пункт отправления выбирается произвольно из имеющихся

4 Создать дополнительную колонку «Доставка», в которой вычислить сколько дней производилась доставка груза. Для этого необходимо использовать функцию ДНЕЙ360 или простое вычитание одной даты из другой.

5 С помощью расширенного фильтра определить груз, доставленный быстрее всего из пункта оправления R (пункт отправления выбирается произвольно из имеющихся). При выполнении задания использовать функцию МИН и данные из колонки «Доставка».

6 Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

7 Используя автофильтр, выведите информацию из таблицы 23 только по бандеролям.

8 Используя автофильтр, выведите информацию из таблицы 23 только по бандеролям, отправленным из Красноярска.

9 Используя автофильтр, выведите информацию из таблицы 23 только по бандеролям, весом от 1 кг до 2,5 кг.

Вариант 8.2

Данные

В таблице 21 приведены данные по выходу книг из нескольких типографий.

Таблица 21

	A	B	C	D	E	F
	Автор	Наименование книги	Издательство	Дата выхода	Количество экземпляров	Наличие соавторов

Требуется

1 Создать таблицу 21 на одном из листов рабочей книги с помощью формы.

2 Переименовать лист в «Книги».

3 Отсортировать таблицу, расположив данные по возрастанию количества экземпляров книги.

4 Отсортировать таблицу, расположив по алфавиту фамилии авторов.

Заполнение таблицы

Таблица заполняется произвольными значениями полей. Колонка F заполняется значениями «+», если есть соавторы (т.е. авторов больше одного), и значениями «-», если их нет. Количество записей в таблице - не менее 15.

Задания

1 Для колонки "Количество экземпляров" задайте соответствующий формат.

2 С помощью расширенного фильтра вывести записи, удовлетворяющие условию: книги авторов X и Y в издательстве Z, начиная с даты D (авторы и издательство выбираются произвольно из имеющихся, дата берется произвольно).

3 С помощью расширенного фильтра вывести записи, удовлетворяющие условию: книги автора Y, если тираж превышает K1 экземпляров и меньше K2 экземпляров (автор выбирается произвольно из имеющихся, числа берутся произвольно).

4 В конец таблицы ввести дополнительную колонку «Раритет», в которой поставить знак «+» если книга вышла более 7 лет назад в количестве менее 500 экземпляров, в противном случае поставить знак «-».

5 С помощью расширенного фильтра выдать данные о самой редкой книге, т.е. она является раритетной и у нее самый маленький тираж. При выполнении задания использовать функцию МИН и данные колонки «Раритет».

6 Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

7 Используя автофильтр, выведите информацию из таблицы 21 только по Пушкину, книги которого выпущены тиражом более 2000 экземпляров.

8 Используя автофильтр, выведите информацию из таблицы 21 только по Пушкину, книги которого выпущены тиражом от 1000 до 1700 экземпляров.

9 Отмените автофильтр и выведите все строки таблицы.

10 Используя автофильтр, выведите информацию из таблицы 21 только по Пушкину и Лермонтову.

Вариант 8.3

Данные

В таблице 22 приведены данные по сдаче студентами зачета и курсовой работы по дисциплине Высшая математика.

Таблица 22

A	B	C	D	E	F	G
Номер студента	Фамилия студента	Номер группы	Зачет	Дата сдачи зачета	Курсовая работа	Дата сдачи курсовой работы
				01.10.2003		12.02.2004

Требуется

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

Переименовать лист в «Ведомость».

Отсортировать таблицу, расположив данные по возрастанию дат.

Отсортировать таблицу, расположив данные по убыванию отметки за курсовую работу.

Заполнение таблицы

1 В поле «Зачет» ставится символ «+», если зачет получен, и знак «-», если нет.

2 Поле «Курсовая работа» заполняется цифрами от 3 до 5 или ставится символ «-» курсовая работа не сдана.

3 Если зачет или курсовая работа не сданы, то вместо дат ставится «-» соответственно.

4 Поля таблицы A, B и C заполняется произвольными значениями полей.

Количество записей в таблице - не менее 15.

Задания

1 В колонках "Номер студента", "Дата сдачи зачета" и "Дата сдачи курсовой работы" задать формат, который показа в примере.

2 С помощью расширенного фильтра вывести записи удовлетворяющие условию: студенты групп N1 и N2, получившие за курсовую работу 5. (номера групп выбираются произвольно из имеющихся).

3 С помощью расширенного фильтра вывести записи удовлетворяющие условию: студенты сдали зачет и получили за курсовую работу 4 или 5.

4 С помощью расширенного фильтра вывести записи удовлетворяющие условию: студенты из группы N сдавали курсовую работу с даты D1 до даты D2 (номер группы выбирается произвольно из имеющихся, даты берутся произвольно).

5 В конец таблицы ввести дополнительную колонку «Выход на сессию», в которой отметить знаком «+», если есть зачет и имеется отметка по курсовой работе, и знаком «-», если это не так

6 Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

7 Используя автофильтр, выведите информацию из таблицы 22 только по студентам группы номер 81-2, у которых отметка по курсовой работе 4.

8 Используя автофильтр, выведите информацию из таблицы 22 только по студентам номер 81-01, которые сдали курсовую работу в декабре (год выбирается согласно данным таблицы).

9 Отмените автофильтр и выведите все строки таблицы.

10 Используя автофильтр, выведите информацию из таблицы 22 только по студентам группы номер 81-1 и 81-2.

Вариант 8.4

Данные

В таблице 23 приведены сведенья о жителях, обслуживаемых поликлиникой.

Таблица 23

	A	B	C	D	Е	F
	Номер амбулаторной карты	Ф И.О.	Дата рождения	Дата выдачи страхового полиса	Номер полиса обязательного медицинского страхования	Сумма, выплаченная по полису

Требуется

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

Переименовать лист в «Поликлиника».

Отсортировать таблицу, расположив данные по возрастанию номера амбулаторных карт.

Отсортировать таблицу, расположив данные по возрастанию даты рождения.

Заполнение таблицы

Таблица заполняется произвольными значениями полей. Количество записей в таблице - не менее 15.

Задания

1 С помощью расширенного фильтра вывести записи удовлетворяющие условию: люди, родившихся в годах X и Y (дата рождения выбираются произвольно из имеющихся).

2 С помощью расширенного фильтра вывести записи удовлетворяющие условию: выплаченные суммы от N1 до N2 (численные значения сумм выбирается произвольно из имеющихся).

3 В конец таблицы введите колонку «Новый полис», в которой необходимо поставить знак «+», если полис выдан менее 6 месяцев назад, и знак «-», если это не так.

4 С помощью расширенного фильтра вывести данные о людях, недавно получивших полис и имеющих минимальные страховые выплаты. При выполнении задания использовать функцию МИН и данные из столбца «Новый полис».

5 Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

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

7 Используя автофильтр, выведите информацию из таблицы 23 только по полисам, выданным за последние три месяца.

8 Используя автофильтр, выведите информацию из таблицы 23 только по полисам, выданным за последний год и по которым выплачено более 10000 руб.

9 Отмените автофильтр и выведите все строки таблицы.

10 Используя автофильтр, выведите информацию из таблицы 23 только по двум людям (необходимо выбрать только две фамилии).

11 Отмените автофильтр и выведите все строки таблицы.

Вариант 8.5

Данные

В таблице 24 приведены данные по прохождению технического осмотра машин.

Таблица 24

	A	B	C	D	E
	Модель	Код региона	Цвет	Фамилия владельца	Дата последнего технического осмотра

Требуется

1 Создать таблицу 24 на одном из листов рабочей книги с помощью формы.

2 Переименовать лист в «Машины».

3 Отсортировать таблицу, расположив данные по возрастанию даты последнего техосмотра.

4 Отсортировать таблицу, расположив данные по убыванию кода региона.

Заполнение таблицы

Таблица заполняется произвольными значениями полей.

Количество записей в таблице - не менее 15.

Задания

1 С помощью расширенного фильтра вывести записи удовлетворяющие условию: машины из региона Х прошли технический осмотр в году С (код региона и год выбираются произвольно из имеющихся).

2 С помощью расширенного фильтра вывести записи удовлетворяющие условию: регионы C1 и C2 (коды регионов выбираются произвольно из имеющихся).

3 С помощью расширенного фильтра вывести записи удовлетворяющие условию: машины синего и белого цвета прошли технический осмотр в регионе X (шифр региона выбирается произвольно из имеющихся).

4 В конец таблицы ввести еще одну колонку «Показатель», в которой поставить 1 если машина серого, черного или серебристого цвета, в противном случае поставить 0.

5 С помощью расширенного фильтра вывести данные по минимальной дате осмотра машин серого, черного или серебристого цвета. При выполнении задания использовать функцию МИН и данные из колонки «Показатель».

6 Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

7 Используя автофильтр, выведите информацию из таблицы 24 только по одной модели.

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

9 Используя автофильтр, выведите информацию из таблицы 24 только по регионам 24 и 12.

10 Отмените автофильтр и выведите все строки таблицы.

11 Используя автофильтр, выведите информацию из таблицы 24 только по «Жигулям» и «Москвичам».

12 Отмените автофильтр и выведите все строки таблицы.

Вариант 8.6

Данные

В таблице 25 приведены данные по заключенным договорам продажи продукции на предприятии.

Таблица 25

	A	B	C	D	E
	Номер договора	Фамилия	Вид продукции (доски, брус и т.д.)	Объем, м³	Сумма договора, руб

Требуется

1. Создать таблицу 25 на одном из листов рабочей книги с помощью формы.

2. Переименовать лист в «Договора».

3. Отсортировать таблицу по убыванию объема древесины.

4. Отсортировать таблицу по виду продукции.

Заполнение таблицы

Таблица заполняется произвольными значениями полей.

Количество записей в таблице - не менее 15.

Задания

1 Для колонки "Сумма договора" задайте денежный формат.

2 Для колонки "Объем древесины" задайте соответствующий формат.

3 С помощью расширенного фильтра вывести записи удовлетворяющие условию: древесина, которую продали в виде досок и фанеры.

4 С помощью расширенного фильтра вывести записи удовлетворяющие условию: продукция объемом от N1 до N2 (объемы берутся произвольно).

5 В конец таблицы введите колонку «Цена за 1 м³», в которой подсчитать по формуле: «Объем» / «Сумма договора».

6 С помощью расширенного фильтра вывести данные по продукции с максимальной ценой за 1 м³. При выполнении задания использовать функцию МАКС и данные из столбца «Цена за 1 м³».

7 Создать 3 сводные таблицы:

8 а) одну - с подсчетом суммы по одному из полей;

9 б) одну - с подсчетом количества по одному из полей;

10 в) одну - с размещением любого поля сводной таблицы для фильтрации.

11 Используя автофильтр, выведите информацию из таблицы 26 только по доскам.

12 Используя автофильтр, выведите информацию из таблицы 26 только по договорам на брус на сумму более 200000 руб.

13 Используя автофильтр, выведите информацию из таблицы 26 только по договорам с номерами с 12 по 35.

14 Отмените автофильтр и выведите все строки таблицы.

15 Используя автофильтр, выведите информацию из таблицы 26 только по бревнам и доскам.

16 Отмените автофильтр и выведите все строки таблицы.

Вариант 8.7

Данные

В таблице 26 приведены данные по предлагаемым рабочим местам из базы вакансий службы занятости.

Таблица 26

	A	B	C	D	E	F
	Фирма	Должность	Заработная плата	Минимальный возраст претендента	Минимальный стаж работы	Пол (м / ж / любой)

Требуется

1. Создать таблицу 26 на одном из листов рабочей книги с помощью формы.

2. Переименовать лист в «Предложения».

3. Отсортировать таблицу, расположив данные по возрастанию требуемого стажа работы.

4. Отсортировать таблицу, расположив данные по требуемому полу претендента.

Заполнение таблицы

Таблица заполняется произвольными значениями полей.

Количество записей в таблице - не менее 15.

Задания

1 С помощью расширенного фильтра вывести записи удовлетворяющие условию: людей, для которых предлагается работа по специальности X или Y (наименования специальностей выбираются произвольно из имеющихся).

2 С помощью расширенного фильтра вывести записи удовлетворяющие условию: сотрудники в возрасте от 20 до 35 лет.

3 В конец таблицы ввести еще одну колонку «Предложения молодым», в которой поставить 1 если требование к стажу работы менее 3 лет и возраст претендента может быть меньше 28 лет, в противном случае поставить 0.

4 С помощью расширенного фильтра вывести данные по предлагаемым работам для молодежи с максимальной заработной платой. При выполнении задания использовать функцию МАКС и данные из столбца «Предложения молодым».

5 Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

6 Используя автофильтр, выведите информацию из таблицы 29 только по фирме «Снежинка».

7 Используя автофильтр, выведите информацию из таблицы 29 только по официантам с минимальным требуемым стажем работы больше 4 лет.

8 Используя автофильтр, выведите информацию из таблицы 29 только по вакансиям, предлагаемым женщинам, с заработной платой от 3000 до 6500 руб.

9 Отмените автофильтр и выведите все строки таблицы.

10 Используя автофильтр, выведите информацию из таблицы 29 только по официантам и шоферам.

11 Отмените автофильтр и выведите все строки таблицы.

Вариант 8.8

Данные

В таблице 27 приведены данные из базы предприятий бытового обслуживания.

Таблица 27

	A	B	C	D	E
	Название предприятия	Услуга (ремонт телевизоров, обуви и т.д.)	Стоимость	Адрес	Телефон

Требуется

1 Создать таблицу 27 на одном из листов рабочей книги с помощью формы.

2 Переименовать лист в «Предприятия».

3 Отсортировать таблицу, расположив данные по возрастанию стоимости услуги.

4 Отсортировать таблицу, расположив данные по наименованиям услуг, оказываемых предприятиями.

Заполнение таблицы

Таблица заполняется произвольными значениями полей.

Количество записей в таблице - не менее 15.

Задания

1 С помощью расширенного фильтра вывести записи удовлетворяющие условию: услуги Х, которое выполнило предприятие С (наименование услуги и наименование предприятия выбираются произвольно из имеющихся).

2 С помощью расширенного фильтра вывести записи удовлетворяющие условию: услуги M предприятий C1 и C2 (наименование предприятий и услуги выбираются произвольно из имеющихся).

3 В конец таблицы ввести еще одну колонку «Ремонт», в которой поставить 1 если предприятие оказывает услуги по ремонту (т.е. ремонт видеотехники, телевизоров, часов и т.д.), в противном случае поставить 0.

4 С помощью расширенного фильтра вывести данные по ремонту с минимальной стоимостью. При выполнении задания использовать функцию МИН и данные колонки «Ремонт».

5 Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

6 Используя автофильтр, выведите информацию из таблицы 30 только по предприятию «Бытовик»

7 Используя автофильтр, выведите информацию из таблицы 30 только по услугам предприятий, расположенных на улице Кирова и стоимостью более 80 руб.

8 Используя автофильтр, выведите информацию из таблицы 30 только по услугам предприятий, расположенных на улице Кирова и стоимостью от 40 до 120 руб.

9 Отмените автофильтр и выведите все строки таблицы.

10 Используя автофильтр, выведите информацию из таблицы 30 только по ремонту телевизоров и ремонту телефонов.

11 Отмените автофильтр и выведите все строки таблицы.

Вариант 8.9

Данные

В таблице 28 приведены данные о вкладчиках нескольких банков.

Таблица 28

	A	B	C	D	E	F	G
	Наименование банка	Номер лицевого счета	Фамилия И.О.	Текущая сумма вклада	Последняя операция	Дата последней операции	Снятая / добавленная сумма

Требуется

1. Создать таблицу 28 на одном из листов рабочей книги с помощью формы.

2. Переименовать лист в «Банк».

3. Отсортировать таблицу, расположив данные по возрастанию даты последней операции.

4. Отсортировать таблицу, расположив данные по убыванию номера лицевого счета.

Заполнение таблицы

1. Поля A, B, С и F таблицы заполняется произвольными значениями полей.

2. Поле «Текущая сумма вклада» заполняется произвольными числами от 1200 до 20000.

3. Поле «Снятая / добавленная сумма» заполняется произвольными числами от 200 до 1900.

4. Поле «Последняя операция» заполняется текстовыми значениями «Снятие со счета» или «Положить на счет»

Количество записей в таблице - не менее 15.

Задания

1 Определить сумму вклада после проведения операции. Для этого в конец таблицы ввести дополнительную колонку «Итоговая сумма вклада» и рассчитать данные в ней по формуле:

2 если последняя операция «Положить на счет», то «Текущая сумма вклада» + «Снятая / добавленная сумма», если последняя операция «Снятие со счета», то «Текущая сумма вклада» - «Снятая / добавленная сумма».

3 Для расчета использовать функцию ЕСЛИ.

4 С помощью расширенного фильтра вывести записи удовлетворяющие условию: снятые суммы с даты D1 до даты D2 (даты выбираются произвольно).

5 С помощью расширенного фильтра вывести записи удовлетворяющие условию: снятые со счетов суммы от 500 до 1200 рублей начиная с даты R (дата выбирается произвольно).

6 В конец таблицы ввести еще одну колонку «Последние поступления», в которой поставить 1 если поступление на счет было с течение последних 2 месяцев от текущей даты, в противном случае поставить 0.

7 С помощью расширенного фильтра вывести данные по последней минимальной поступившей сумме. При выполнении задания использовать функцию МИН и данные колонки «Последние поступления».

Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

8 Используя автофильтр, выведите информацию из таблицы 31 только по положенным суммам.

9 Используя автофильтр, выведите информацию из таблицы 31 только по суммам, положенным после 12.8.2001.

10 Используя автофильтр, выведите информацию из таблицы 31 только по суммам, положенным в текущем году.

11 Отмените автофильтр и выведите все строки таблицы.

12 Используя автофильтр, выведите информацию из таблицы 31 только по вкладчикам Иванов и Сидоренко.

13 Отмените автофильтр и выведите все строки таблицы.

Вариант 8.10

Данные

В таблице 29 приведены данные о рабочих цеха и их заработной плате.

Таблица 29

	A	B	C	E	D	F	G
	Табельный номер	Фамилия И.О.	Профессия	Разряд	Стаж работы	Заработная плата по окладу	Заработная плата по тарифу

Требуется

1 Создать таблицу 32 на одном из листов рабочей книги с помощью формы.

2 Переименовать лист в «Рабочие».

3 Отсортировать таблицу, расположив данные по убыванию табельных номеров.

4 Отсортировать таблицу, расположив данные по профессиям и табельным номерам.

Заполнение таблицы

Таблица заполняется произвольными значениями полей.

Количество записей в таблице - не менее 15.

Задания

1 С помощью расширенного фильтра вывести записи удовлетворяющие условию: люди профессии Х, работающего по разряду С (наименование профессии и разряд работающего выбираются произвольно из имеющихся).

2 С помощью расширенного фильтра вывести записи удовлетворяющие условию: люди профессий C1 и C2 со стажем работы более 20 лет. (наименование профессий выбираются произвольно из имеющихся).

3 С помощью расширенного фильтра вывести записи удовлетворяющие условию: рабочие 6 и 11 разрядов с окладом более 6000 руб.

4 В конец таблицы ввести еще одну колонку «Анализ», в которой поставить 1 если рабочий грузчик имеет 3 или 4 разряд, в противном случае поставить 0.

5 С помощью расширенного фильтра вывести данные по грузчикам с минимальной заработной платой. При выполнении задания использовать функцию МИН и данные дополнительной колонки «Анализ».

6 Создать 3 сводные таблицы:

а) одну - с подсчетом суммы по одному из полей;

б) одну - с подсчетом количества по одному из полей;

в) одну - с размещением любого поля сводной таблицы для фильтрации.

7 Используя автофильтр, выведите информацию из таблицы 32 только по специалистам 6 разряда.

8 Используя автофильтр, выведите информацию из таблицы 32 только по специалистам 6 разряда и требуемым стажем работы больше 6 лет.

9 Используя автофильтр, выведите информацию из таблицы 32 только по работам, предлагаемым мужчинам с заработной платой от 6000 до 12000 руб.

10 Отмените автофильтр и выведите все строки таблицы.

11 Используя автофильтр, выведите информацию из таблицы 32 только по шоферам и слесарям.

12 Отмените автофильтр и выведите все строки таблицы.

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

1 Какие виды фильтров вы знаете?