2.5. Лабораторная работа 5

Организация баз данных на основе таблиц Excel

Цель работы:

• освоить функции поиска в справочнике;

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

Пример задания.

Сформировать таблицу-справочник «Цены на ткани»:

• код;

• наименование товара;

• сорт;

• цена за метр.

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

Используя фильтр, показать выпуск:

• за определенный период времени;

• только 1 сорта в течение месяца,

• выпуск определенного вида продукции за месяц;

Подвести итоги выпуска продукции

• в сумме за каждый день;

• по каждой ткани и каждому сорту за месяц;

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

Порядок выполнения работы.

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

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

Для его получения выполняем следующие действия:

Формируем исходную таблицу-справочник (пока лучше не запол­нять).

Формируем таблицу продаж копированием исходной таблицы и добавлением столбцов А - Дата, F - Кол-во и G -Сумма.

Заполняем справочник.

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

ВПР(искомое значение; массив ячеек; номер столбца; вид поис­ка)

Первая функция выполняет поиск заданного значения в первом столбце заданного массива ячеек и возвращает значение ячейки за­данного столбца найденной строки. Массив задается в виде диапазона ячеек, обычно это диапазон ячеек справочника (в нашем случае $В$4:$Е$9). При этом вид поиска может принимать значения Истина(1) или Ложь(О). Ноль обозначает, что нужно искать значение, точно соот­ветствующее искомому, а единица - что нужно искать наиболее близ­кое к искомому значение (этот вид поиска правильно работает только в случае, когда столбец, в котором ведется поиск, - отсортирован) В нашей задаче, конечно, вид поиска - ноль (или Ложь).

	A	B	C	D	E	F	G
1			Цены на	ткани
2
3		Код	Наименование	Сорт	Цена
4		11	Сатин	1	5
5		12	Сатин	2	3
6		21	Шелк	1	10
7		22	Шелк	2	8
8		31	Шерсть	1	20
9		32	Шерсть	2	15
10

11	Продажи тканей
12
13	Дата	Код	Наименование	Сорт	Цена	Кол-во	Сумма
14	01.01.04	22	Шелк	2	8	20	160
15	01.01.04	12	Сатин	2	3	30	90
16	03.01.04	11	Сатин	1	5	15	75
17	04.01.04	32	Шерсть	2	15	23	345
18	04.01.04	21	Шелк	1	10	31	310

Рис 2.8. Часть рабочего листа «Продажа тканей»

При поиске возможна ситуация, когда искомое значение вообще не будет обнаружено. В этом случае выводится сообщение об ошибке #Н/Д. Это сообщение появляется и в том случае, если столбец Код не заполнен, а формула уже занесена в другие столбцы. Чтобы избежать этой неприятной ситуации, добавим проверку, заполнен столбец Код или нет, воспользовавшись функцией ЕПУСТО().

В ячейку С14 при помощи мастера функций заносим формулу: =ЕСЛИ(ЕПУСТО($В14);"";ВПР($В14;$В$4:$Е$9;2;0))

При занесении обратите внимание на правильную расстановку относительных и абсолютных ссылок, т.к. эта формула будет распро­страняться и по строкам и по столбцам. Распространяем формулу на два соседних столбца вправо, при этом поправляем номер столбца на 3 и 4, а затем на нужное количество строк вниз.

В ячейку G14 вводим формулу =ПРОИЗВЕД(Е14;Р14).

5. Заполнять таблицу продаж можно обычным способом, но для удобства воспользуемся дополнительной возможностью, предостав­ляемой Excel для ввода данных в базу данных. Выделяем таблицу продаж, а затем выполняем команду Данные/Форма. Заполняем таб­лицу на целый месяц.

Фильтрация данных.

Фильтрация - это скрытие лишних данных в таблице, при этом на экране остаются только те записи, которые удовлетворяют заданным критериям. Имеется две возможности фильтрации: Автофильтр и Расширенный фильтр. Изучим Автофильтр.

Выделяем заголовки столбцов таблицы продаж и выполняем ко­манду Данные/Фильтр/Автофильтр. При этом заголовки всех столб­цов превратятся в раскрывающиеся списки. При работе с автофильт­ром имеются следующие возможности:

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

• можно выбрать команду Настройка, чтобы задать например диапазон значений;

• команда Показать все позволяет отменить действие фильт­ ра.

Задание

Используя Автофильтр, сформируйте и скопируйте на новый лист таблицы, отражающие:

1. продажи за определенный период времени (1 декада);

2. продажи только 1 сорта в течение месяца;

3. продажи сатина за месяц;

4. продажи сатина и шелка за месяц.

Подведение итогов

Excel позволяет подводить не только общие итоги по всей таб­лице, но и подводить промежуточные итоги при различных способах группирования данных в таблице. Промежуточные итоги подводятся при каждом изменении в значении какого-либо столбца, поэтому перед подведением итогов обычно выполняют сортировку данных по одному из столбцов. Для сортировки используется команда Дан­ные/Сортировка, для подведения итогов Данные/Итоги.

Задание

Подведите итоги:

1. за каждый день (см.рис.2.9.);

2. по наименованию и сорту ткани.

Скопируйте полученные таблицы на другой рабочий лист с на­званием «Итоги» и дайте им там соответствующие заголовки.

Продажа тканей
Дата	Код	Наименование	Сорт	Цена	Кол-во		Сумма
01.01.04	22	Шелк	2	8	20		160
01.01.04	12	Сатин	2	3	30		90
01. 01. 04 Всего					50		250
03.01.04	11	Сатин	1	5	15		75
03.01. 04 Всего					15		75
04.01.04	32	Шерсть	2	15	23		345
04.01.04	21	Шелк	1	10	31		310
04. 01. 04 Всего					54		655
Общий итог					119		040

Рис.2.9. Таблица итогов продажи тканей

Самостоятельное задание

Добавить в справочник еще две строки (например, ситец 1 и 2 сорта). Изменить формулы в таблице продаж и добавить в ее конец несколько строк о продажах ситца. Отсортировать таблицу по дате.

Измените формулу для поиска в справочнике, используя вместо функции ВПР() сочетание функций ИНДЕКС() и ПОИСКПОЗ().