Работа со списками (базами данных). Создание списка базы данных. Фильтрация данных с помощью Автофильтра и Расширенного фильтра

Работа со списками (базами данных)

1.  Создание списка (базы данных).

§  переименовать лист 1 в Список;

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

§  ввести свои данные в таблицу или скопировать их без строки заголовков из файла Y:\Задания_информатика\Продажи.xls. Под одной продажей понимается одна запись списка.

Область	Город	Номер магазина	Дата продажи	Наименование товара	Цена ед. товара	Количество ед. товара
Брестская	Брест	1	14.06.03	сок	1550	340
Витебская	Новополоцк	1	13.06.03	сахар	1320	450
Витебская	Новополоцк	4	13.06.03	колбаса	5690	200
Гомельская	Солигорск	5	15.06.03	сахар	1320	450
Гомельская	Речица	1	15.06.03	сахар	1300	380
Гомельская	Речица	1	16.06.03	сахар	1300	420
Гомельская	Солигорск	5	15.06.03	сок	1500	200

Рис. 1. Фрагмент списка «Продажи»

 Правила создания списков:

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

-   нельзя отделять строку заголовков от записей пустыми или другими строками (например с нумерацией столбцов);

-  нельзя разделять столбцы с заголовками пустыми столбцами или объединять два столбца для одного поля;

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

Работа со списками выполняется с помощью меню Данные.

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

§  скопировать список на лист 2;

§  переименовать лист 2  в Сортировка;

§  выполнить сортировку: по одному ключу: по области в алфавитном порядке;

по трем ключам:    по городу, наименованию товара и по убыванию цены;

Выполнение:

§  по одному ключу:

выделить любую ячейку в столбце Область и щелкнуть по кнопке на стандартной панели инструментов;

§  по трем ключам:

-  выделить любую ячейку списка;

-  выполнить команду Данные – Сортировка, появится диалоговое окно (ДО) Сортировка диапазона;

-  в ДО указать ключи и порядок сортировки (выбрать из списка полей):

Сортировать по               Город                                   по возрастанию

Затем по:                          Наименование товара         по возрастанию

В последнюю очередь по  Цена                                      по убыванию

§  озаглавить (например, «Данные о продажах, упорядоченные по области»).

3.  Фильтрация данных с помощью Автофильтра

§  скопировать список на лист 3;

§  переименовать лист 3 в Автофильтр;

§  выбрать данные о продажах заданного товара (сахар)  в двух заданных городах (Бресте, Бобруйске) с количеством проданных товаров не меньше заданного значения(5000);

Выполнение:

-  выделить любую ячейку списка;

-  выполнить команду Данные - Фильтр – Автофильтр. Ячейки с названиями полей превращаются в раскрывающиеся списки;

-  щелкнуть по кнопке раскрытия списка в поле Наименование товара, щелкнуть по названию сахар;

-  раскрыть список в поле Город, выбрать Условие, появится ДО Пользовательский автофильтр. Выбрать из списков: равно     Брест     ИЛИ      равно      Бобруйск;

-  раскрыть список в поле Количество, выбрать Условие, в ДО Пользовательский автофильтр указать: больше или равно       5000;

§  отфильтрованные данные скопировать в свободную область этого листа, озаглавить;

§  снять Автофильтр.

-  выделить любую ячейку списка;

-  выполнить команду Данные - Фильтр – Автофильтр.

4.  Фильтрация данных с помощью Расширенного фильтра

§  скопировать список на лист 4;

§  переименовать лист 4 в Расширенный фильтр;

§  выбрать данные о продажах по условию Автофильтра, отфильтрованные данные разместить:

а) на месте исходного списка;

б) в свободной части листа;

Выполнение:

-  создать в свободной области Рабочего листа дополнительную таблицу (диапазон условий), заголовки которой скопировать из заголовков базы данных;

Город	Наименование товара	Количество ед. товара
Брест	сахар	>=5000
Бобруйск	сахар	>=5000

-  выделить любую ячейку списка;

-  выполнить команду Данные - Фильтр – Расширенный фильтр, появится ДО;

-  заполнить поля, выделяя на Рабочем листе:

Исходный диапазон     выделить исходный список

Диапазон условий         выделить дополнительную таблицу

-  а) Обработка            фильтровать список на месте, нажать ОК;

б) Обработка            скопировать результат в другое место,

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

§  отфильтрованные данные  озаглавить;

§  вывести все записи списка на экран выполнить команду Данные - Фильтр –Отобразить все.    

5.  Автоматическое подведение итогов.

§  скопировать список на лист 4;

§  переименовать лист 4 в Итоги;

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

Выполнение:

-  выделить любую ячейку списка;

-  выполнить сортировку списка по полям Город, Наименование товара (см. п.2);

-  выполнить команду Данные – Итоги, появится ДО Промежуточные итоги;.

в ДО указать:

При каждом изменении в              Город

Операция                                         Максимум

Добавить итоги по                        Цена щелчок по кнопке  ОК

Появятся максимальные цены всех товаров по каждому городу.

-  выполнить команду Данные – Итоги, появится ДО Промежуточные итоги;.

в ДО указать:

При каждом изменении в              Наименование товара

Операция                                         Максимум

Добавить итоги по                        Цена

Убрать флажок в поле            Заменить текущие итоги

щелчок по кнопке  ОК

Появятся максимальные цены по каждому товару в каждом городе.

§  вывести только итоговые строки и озаглавить:

-  слева от строки заголовков списка на служебном поле щелкнуть по кнопке с номером 3;

§  убрать все итоги:

-  выделить любую ячейку списка;

-  выполнить команду Данные – Итоги,

-  в ДО щелкнуть по кнопке Убрать все.