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

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

Обе команды вызываются в результате выбора команды Данные — Фильтр.

· Подведении итогов в базе данных

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

После выполнения команды Данные — Итоги можно выполнить следующие операции:

ü выбрать одну или несколько групп для автоматического подведения итогов по этим группам;

ü выбрать функцию для подведения итогов;

ü выбрать данные, по которым нужно подвести итоги.

Кроме подведения итогов по одному столбцу, автоматическое подведение итогов позволяет:

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

ü выводить многоуровневые, вложенные строки итогов по нескольким столбцам;

ü выводить многоуровневые строки итогов с различными способами вычисления для каждой строки;

ü скрывать или показывать детальные данные в этом списке.

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

ПРОМЕЖУТОЧНЫЕ ИТОГИ(номер_функции; ссылка), где

номер_функции - это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.

ссылка - это интервал или ссылка, для которой подводятся итоги.

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

Задания:

1. Создать таблицу по классификации растений.

2. Выполнить сортировку данных электронной таблицы.

3. Осуществить поиск и отбор данных в таблице, используя Автофильтр.

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

5. Подвести промежуточные итоги, осуществив суммирование данных по полю «Цена».

Решение:

1. Создать и заполнить таблицу 1. Исходные данные представлены на рис.1

Рис.1

2. Выполнить сортировку записей поля «Наименование растений» по алфавиту на возрастание.

3. Выполнить многоуровневую сортировку по полям «Наименование растений» и «Период созревания»– на убывание, поле «Цена, руб.» - по воз­растанию (рис.2)

Рис.2

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

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

5. Выполнить команду Данные Фильтр Автофильтр. В качестве условия отбора можно выбрать - либо любое значение из списка, либо пункт — Условие.

5.1 Осуществить выборку значений «ранние» и «поздние» по столбцу «Период созревания»:

ü щелкнуть по кнопке списка справа от поля «Период созревания»;

ü выбрать пункт Условие;

ü в диалоговом окне выбрать параметры, как показаны на рис. 3

Рис.3

ü проанализировать результаты;

ü скопировать результат на новый лист, лист назвать Автофильтр;

ü вернуться к исходному списку.

5.2 Осуществить выборку значений по двум столбцам:

ü по столбцу «Наименование растений» только яблонь и груш;

ü по столбцу «Цена, руб.» отобрать записи в диапазоне от 230 до 300;

ü скопировать результат на лист Автофильтр.

5.3 Убрать Автофильтр.

6. Использование команды Расширенный фильтр для отбора данных.

6.1 Задать критерия отбора данных отвечающих следующим требованиям: - выбрать из списка только яблони по цене более 220 рублей:

ü критерий отбора отобразить в четырех ячейках, как показано на рис.4;

Рис.4

ü щелкнуть в любом месте базы данных, в нашем случае диапазон A2:D32;

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

ü в диалоговом окне указать параметры, как показано на рис.5;

Рис.5

ü проанализировать результат.

6.2 Используя расширенный фильтр, найти растения, которые удовлетворяют ценовому диапазону от 270 рублей И до 300 рублей (используется логическое действие И):

ü критерий отбора расположить на экране в четырех ячейках, согласно рис. 6;

Рис.6

ü выполнить действия аналогичные предыдущему заданию;

ü в списке «Классификация растений» изменить цвет ячеек, которые соответствуют условиям отбора в отфильтрованной таблице.

6.3 Используя расширенный фильтр, найти растения, которые удовлетворяют ценовому диапазону менее 220 рублей ИЛИ более 430 рублей (используется логическое действие ИЛИ).

ü критерий отбора расположить на экране в трех ячейках, согласно рис. 7;

Рис.7

ü выполнить действия аналогичные предыдущим заданиям;

ü в списке «Классификация растений» изменить цвет ячеек, которые соответствуют условиям отбора в отфильтрованной таблице.

Объяснить различия в написании критериев при использовании логических действий И, ИЛИ.

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

ü выбрать яблони ранних сортов;

ü выбрать поздние сорта из всех наименований растений по цене выше среднего значения (СРЗНАЧ рассчитать в отдельной ячейке);

ü используя логические действия И, ИЛИ придумать и реализовать критерии по полю «Цена, руб.»;

ü используя функцию СЧЕТЕСЛИ, рассчитать количество ранних, средних, поздних сортов растений (разместить расчет в отдельных ячейках).

8. Подвести итоги по полю «Цена, руб.» для растений – яблоня, груша, вишня:

Рис.8

ü выполнить команду Данные Фильтр Итоги;

ü в диалоговом окне указать параметры согласно рис.8, т.е. рассчитать сумму по цене для растений;

ü проанализировать результаты.