Задание 3. Работа с базой данных. Поиск данных. Фильтрация данных. Сортировка и расчет промежуточных итогов. Сводные таблицы

1. Работа с учебной базой данных.

Откройте рабочую книгу, которую сохранили в лабораторной работе 2. На новом листе рабочей книги наберите учебную базу данных или скопируйте ее из файла Bazaprimer1.xls.

Редактирование учебной базы данных. В учебной Базе данных (БД) внесите следующие дополнения.

В БД добавьте поля с названиями Дата поступления и Дата Продажи. Заполните их любыми реальными данными. Заполнение данных выполните с использование прогрессии. Для этого в меню Главнаявыберите командыРедактирование - Заполнить – Прогрессия.

В БД добавьте поле с названием Срок продажи и заполните его формулами. Срок продажи = Дата продажи – Дата поступления.

Вставьте после столбцов Дата поступления и Дата продажи столбцы Месяц поступления и Месяц продажи. Заполните новые столбцы формулами для отображения числа месяца из даты поступления с помощью функции Месяц ().

В БД добавьте поле с названием «Итого Сумма» и вычислите его по формуле: Итого Сумма = Цена в $ * Количество

Добавление новых записей в список Базы Данных и их редактирование.Добавление записей в БД можно выполнить с помощью ручного ввода данных в новые строки или с помощью командыЯчейки в менюГлавная.Научитесь добавлять, удалять записи БД.

Вручную добавьте с 40 по 44 записи в БД. При заполнении поля Наименование вводите только те названия, которые перечислены выше, задав произвольные данные в других полях БД.

Примечание:поля БД необходимо заполнять однотипными данными. Например, в поле Наименование данные введены только строчными буквами. Записи БД с наименованием товара – телевизор, Телевизор, теле-р или ТЕЛЕВИЗОР могут быть восприняты как разные.

С помощью команды Вставить в менюЯчейкии кнопкиДобавитьвведите с 45 по 50 записи. Заполните их перечисленными товарами с произвольными данными. С помощью кнопкиУдалитьиз БД удалите 45 запись, а затем вновь ее добавьте. Отредактируйте последнюю запись БД (измените ее). Возвратите прежние данные в последней записи с помощью кнопки Вернуть (возвращение предыдущего значения возможно, если вы еще не перешли к следующей записи).

2. Поиск данных

Изучите способы поиска записей в БД с помощью команды Форма. Для задания условий поиска необходимо использовать кнопкуКритериив диалоговом окнеФорма.

При поиске можно применять символы ? и *, а также операторы сравнения = , <>, <,>, <= , >=.

После задания всех условий поиска будет отображена первая найденная запись. Для отображения других найденных записей используются кнопки Назад и Далее.

Выполните поиск следующих данных:

• найдите в БД пылесосы, проданные в магазине 5;

• найдите в БД телевизоры, марка которых начинается на S;

• найдите в БД видеомагнитофоны, цена которых меньше 400 $.

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

Фильтрация данных – это отбор данных по заданным условиям. Фильтрацию данных можно выполнить с помощью Автофильтра и Расширенного фильтра

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

Изучите фильтрацию данных с помощью Автофильтра.

Решите следующие задачи с помощью команды Фильтр – Автофильтрв менюДанные:

• выведите 10 элементов списка, имеющих наибольшую цену (используйте метод Первые 10). Отмените результат;

• установите Автофильтр для отображения только телевизоров и видеомагнитофонов и ценой < 1000 $. Отмените результат;

• установите Автофильтр для отображения муз. центров, марка которых начинается на S или Pи датой поступления после 1 апреля текущего года. Отмените результат;

• установите Автофильтр для отображения телевизоров, проданных в магазинах 1,3, ценой от 200 до 1000$$. Отмените результат.

Использование Расширенного фильтра. Расширенный фильтр применяют при более сложных условиях отбора записей из БД, чем при Автофильтре. При создании данного фильтра обязательно создается блок критериев (диапазон условий). Условия отбора в блоке критериев могут быть простые и вычисляемые. Правила создания блока критериев зависит от типа условий отбора. После создания блока критериев необходимо применить командуДополнительнов менюДанные.

1. Работа Расширенного фильтра с простыми условиями

Изучите правила работы Расширенного фильтра с простыми условиями. Копирование результатов фильтрации на другой лист.

Пример создания простого Расширенного фильтра. Из списка БД отберите пылесосы, марка которых начинается на М или S, с ценой от 200$ до 500$ и отобразите в том же списке, а затем начиная с ячейки А55. Ячейки блока критериев выделите цветом. В ячейку А48 введите название фильтра – Выбор пылесосов маркой на М,Sи ценой от 200$ до 500$.

Порядок действий по созданию блока критериев.

В ячейки А50 и B50 скопируйте соответственно из основной БД заголовки полей: Название и Марка, а в ячейки С50 иD50 – Цена в $.

Таблица 2.5.

Пример создания Расширенного фильтра с простыми условиями

	A	B	C	D
50	НАИМЕНОВАНИЕ	МАРКА	ЦЕНА В $	ЦЕНА В $
51	пылесос	S	>=100	<=500
52	пылесос	M	>=100	<=500

В ячейки А51 и А52 введите условие фильтра путем копирования из БД наименование товара – пылесос. Заполните ячейки блока критериев согласно Таблице. Выполните фильтрацию данных. Установите курсор в любую ячейку БД.

В меню Данныевыберите командуДополнительно.

В диалоговом окне Расширенный фильтр заполните путем выделения диапазонов следующие поля: Исходный диапазон (А1: H41) и Диапазон условий (А50: D52). Включите кнопку: – Скопировать результат в другое место. Выделите курсором ячейку А55. Нажмите кнопку – ОК.

Выполните самостоятельно следующие задания. :

• выберите из списка БД телевизоры с маркой Sony , ценой < 700 $. Выберите из списка БД видеомагнитофоны, марка которых начинается на Sи P, магазинов 2 и 4, цена которых < 800$. Блок критериев и результаты фильтрации отобразите ниже списка БД на втором листе рабочей книги.

• выберите из списка БД телевизоры и видеомагнитофоны, марка которых начинается на S и P, количеством от 1 до 8. Блок критериев и результаты фильтрации отобразите на новом листе рабочей книги.

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

Переименуйте новый лист с фильтрами как «Фильтры 1».

2. Работа Расширенного фильтра с вычисляемыми условиями

Изучите правила работы Расширенного фильтра с вычисляемыми условиями.

Пример создания Расширенного фильтра с вычисляемыми условиями.

Из списка БД выберите телевизоры из магазинов 1 и 3, а затем из них те, цена которых ниже средней. Решение задачи представлено в таблице

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

• выберите из БД видеокамеры с маркой, которая начинается на S и P. Затем из них выберите видеокамеры с ценой ниже средней.

• из списка БД выберите телевизоры и видеомагнитофоны, с ценой от 100 до 500. Из них выберите те, у которых сроком продажи больше среднего;

• из списка БД выберите товары, проданные в магазинах 2 и 4. Из них выберите товары, количество которых меньше среднего.

• создайте новый фильтр с вычисляемыми условиями поиска. Критерии фильтрации задайте по своему желанию.

Переименуйте ярлык листа как «Фильтры 2».