Создание базы данных в программе Microsoft Excel. Сводные таблицы. Сортировка баз данных

Создание базы данных в MSExcel

Сводные таблицы

Цель работы: Изучение возможностей пакета  MS Excel при работе с базами данных. Приобретение  навыков создания и обработки баз данных.

Теоретические сведения

Информация, хранящаяся в таблицах, организованна виде строк и столбцов. Каждая строка таблицы, называемая записью содержит данные об одном объекте.  В столбце, называемом полем, содержаться сведения о каком – либо свойстве всех объектов хранящихся в таблице.

База данных (БД) – это совокупность различных записей, обладающих определенными свойствами. 

В первой строке любой базы данных обязательно должны быть указаны имена полей. Максимальный размер БД в MS Excel определяется возможностями версии Excel (число строк и число столбцов в листе).

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

Рассмотрим процесс построения и обработки базы данных на следующем примере.

Ход работы

Сформируем БД на первом листе MS Excel (рис. 1), после чего переименуем лист.

Наименование товара	Дата	Поставщик	Город	Закупочная цена	Отпускная цена	Транспортные расходы	Количество	Прибыль
Колбаса	02.10.2003	Колбико	Макеевка	15,80	18,00	20 грн	100
Творог	07.10.2003	Ромол	Донецк	2,20	2,50	45 грн	500

Рисунок 1 – Пример создания БД

Сформируем поле Прибыль. Для этого в ячейку I2 введем формулу:

I2=(F2-E2)*H2-G2

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

	А	В	С	D	E	F	G	H	I
1	Наименование товара	Дата	Поставщик	Город	Закупочная цена	Отпускная цена	Транспортные расходы	Количество	Прибыль
2	колбаса	02.10.2003	колбико	Макеевка	15,80₴	18,00₴	20,00₴	10
3	творог	05.10.2003	ромол	Харьков	2,20₴	2,50₴	45,00₴	500
4	батоны	05.10.2003	хбк №1	Макеевка	1,23₴	1,40₴	10,00₴	200
5	батоны	07.10.2003	хбк №2	Донецк	0,98₴	1,06₴	5,00₴	180
6	колбаса	07.10.2003	мясокомбинат	Донецк	14,90₴	16,80₴	5,00₴	50
7	молоко	07.10.2003	ромол	Харьков	2,80₴	3,15₴	45,00₴	200
8	творог	05.10.2003	Молокозавод 2	Донецк	0,99₴	1,07₴	5,00₴	160
9	мясо	05.10.2003	мясокомбинат	Донецк	15,08₴	17,50₴	5,00₴	20
10	сыр	07.10.2003	маслосыробаза	Перевальск	16,00₴	17,00₴	42,00₴	100
11	хлеб	02.08.2003	хбк №2	Макеевка	1,03₴	1,15₴	10,00₴	170
12	хлеб	05.09.2003	хбк	Донецк	1,12₴	1,30₴	4,00₴	50
13	конфеты	05.10.2003	АВК	Донецк	23,00₴	25,00₴	4,60₴	100
14	пиво	07.09.2003	балтика	Санкт	2,80₴	3,40₴	75,00₴	400
15	колбаса	02.10.2003	мясокомбинат	Горловка	23,00₴	25,20₴	8,00₴	35
16	конфеты	05.10.2003	бабаевский	Москва	34,00₴	38,00₴	60,00₴	200
17	пиво	05.10.2003	сармат	Донецк	1,25₴	1,40₴	5,00₴	120
18	сыр	07.10.2003	маслосыробаза	Перевальск	19,00₴	21,80₴	38,00₴	60

Рисунок 2 – Пример создания БД «Поставщики товара»

	А	В	С	D	E	F	G	H	I
1	Наименование товара	Дата	Поставщик	Город	Закупочная цена	Отпускная цена	Транспортные расходы	Количество	Прибыль
2	колбаса	02.10.2003	колбико	Макеевка	15,80₴	18,00₴	20,00₴	100	200,00₴
3	творог	05.10.2003	ромол	Харьков	2,20₴	2,50₴	45,00₴	500	105,00₴
4	батоны	05.10.2003	хбк №1	Макеевка	1,23₴	1,40₴	10,00₴	200	24,00₴
5	батоны	07.10.2003	хбк №2	Донецк	0,98₴	1,06₴	5,00₴	180	9,40₴
6	колбаса	07.10.2003	мясокомбинат	Донецк	14,90₴	16,80₴	5,00₴	50	90,00₴
7	молоко	07.10.2003	ромол	Харьков	2,80₴	3,15₴	45,00₴	200	25,00₴
8	творог	05.10.2003	Молокозавод 2	Донецк	0,99₴	1,07₴	5,00₴	160	7,80₴
9	мясо	05.10.2003	мясокомбинат	Донецк	15,08₴	17,50₴	5,00₴	20	43,40₴
10	сыр	07.10.2003	маслосыробаза	Перевальск	16,00₴	17,00₴	42,00₴	100	58,00₴
11	хлеб	02.08.2003	хбк №2	Макеевка	1,03₴	1,15₴	10,00₴	170	10,40₴
12	хлеб	05.09.2003	хбк	Донецк	1,12₴	1,30₴	4,00₴	50	5,00₴
13	конфеты	05.10.2003	АВК	Донецк	23,00₴	25,00₴	4,60₴	100	195,40₴
14	пиво	07.09.2003	балтика	Санкт -	2,80₴	3,40₴	75,00₴	400	165,00₴
15	колбаса	02.10.2003	мясокомбинат	Горловка	23,00₴	25,20₴	8,00₴	35	69,00₴
16	конфеты	05.10.2003	бабаевский	Москва	34,00₴	38,00₴	60,00₴	200	740,00₴
17	пиво	05.10.2003	сармат	Донецк	1,25₴	1,40₴	5,00₴	120	13,00₴
18	сыр	07.10.2003	маслосыробаза	Перевальск	19,00₴	21,80₴	38,00₴	60	130,00₴

Рисунок 3 – Сформированное поле Прибыль

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

Для сортировки базы выделим ячейки А1: I18 и выполнимкоманду Данные / Сортировка (рис. 4).

Рисунок 4 – Сортировка данных

Добавление итогов в базу данных

Просчитать суммарную прибыль при продаже всех продуктов можно при помощи функции СУММ. Если воспользоваться пунктом меню Итоги, то появляется возможность рассчитать сумму прибыли по каждому товару или по каждому поставщику. Для суммирования прибыли по каждому из товаров сделаем следующее: отсортируем БД по наименованию товаров (рис. 4)  и выполним команду Данные / Итоги (рис. 5), рабочий лист примет вид изображенный на (рис. 6).

Рисунок 5 – Диалоговое окно Промежуточные итоги

Рисунок 6 - Итог по прибыли по всем видам товара

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

Процесс поиска и отбора информации в базе данных MS Exсel называется фильтрацией. В MS Exсel есть два вида фильтра: Автофильтр и Расширенный фильтр.

Автофильтр

Для включения автофильтра необходимо:

1.  Щелкнуть в любом месте базы данных, в нашем случае диапазон A1:I18.

2. Выполнить команду Данные /Фильтр Автофильтр. Щелкнуть по кнопке списка справа от нужного поля (например,  поле Наименование товара). Окно БД примет вид, изображенный на рисунке 7. В качестве условия отбора можно выбрать либо любое значение из списка, либо пункт Условие.

Рисунок 7 -  БД после применения команды Автофильтр

Выберем в качестве условия значение Колбаса. В результате в БД останется информация, касающаяся только поставок колбасы (рис. 8)

Рисунок 8 – Фрагмент отфильтрованной базы данных

Результат будет тем же, если из списка предложенных фильтров выбрать Условие и в открывшемся диалоговом окне (рис. 9) ввести в качестве условия «равно Колбаса».

Рисунок 9 – Диалоговое окно Пользовательский автофильтр

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

Расширенный фильтр

Для выделения из базы данных более сложных условий можно воспользоваться командой Данные / Фильтр / Расширенный фильтр. Рассмотрим работу с расширенным фильтром на примере выделения из базы данных записей донецких производителей колбасы. Для этого определим область для хранения условий отбора. Каждое условие записывается в две ячейки: в верхнюю – имя поля, в нижнюю – знак отношения (>, <, >=, <=, < >) и значение. В нашем случае в ячейку К1 введем Наименование товара. В ячейку К2 – Колбаса, в ячейку L1  - город, в ячейку L2 - Донецк (рис. 10)   

Рисунок 10 – Область для хранения условий отбора

Выполним команду Данные / Фильтр / Расширенный фильтр (рис. 11)

Рисунок 11 – Диалоговое окно Расширенный фильтр

В данном случае два условия соединены логическим действием «И». Для объединения с помощью «ИЛИ» необходимо между именем поля и условием пропустить строчку (рис.12).

Рисунок 12 – Соединение условий при помощи логического «ИЛИ»