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

Фильтрация – это способ поиска подмножества данных в списке в соответствии с заданными условиями. В табличном процессоре MS Excel используется два способа фильтрации списков: Автофильтр для простых условий отбора и Расширенный фильтр для более сложных условий. В отличие от сортировки при фильтрации порядок записей в списке не изме­няется. При фильтрации временно скрываются строки, которые не требуется отображать. Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их.

Чтобы отфильтровать список с помощью Автофильтра, небходимо выделить одну из ячеек списка и выбрать команду Данные/Фильтр/Автофильтр (рис. 2).

Рис. 2. Область Сортировка и фильтр на Ленте MS Excel 2007

После выполнения этой команды в нижнем правом углу ячеек с заголовками столбцов появится черный треугольник, обращенный вершиной вниз, означающий появление в этой ячейке элемента управления «Поле со списком». Список в этом поле содержит условия отбора Автофильтра. Чтобы отфильтровать список по двум или более значениям, встречающимся в столбце, или с использованием операторов сравнения, следует из развернувшегося набора значений выбрать строку «Условие...». Эта процедура применяется для фильтрации списка с наложением одного или двух условий отбора значений ячеек отдельного столбца. Для того чтобы наложить одно условие отбора, надо выбрать из разворачивающихся наборов в полях первого условия необходимые оператор сравнения и значение сравнения (рис. 3).

Рис. 3. Пользовательский автофильтр

Для задания второго условия следует установить переключатель в положение И или ИЛИ и выбрать из разворачивающихся наборов в полях второго условия необходимые оператор и значение сравнения. В расширенном фильтре условия отбора вводятся в диапазон условий на листе книги. Команда Расширенный фильтр применяется, чтобы отфильтровать данные в тех случаях, когда для отбора записей требуется записать:

– условия в два или более столбцов;

– более двух условий в одном столбце;

– условие, которое использует значение, вычисляемое формулой.

Чтобы отфильтровать список с помощью расширенного фильтра, следует рядом со списком создать диапазон условий. Для этого нужно в пустую строку создаваемого Диапазона ввести или скопировать заголовки фильтруемых столбцов, а в нижележащие строки ввести условия отбора. Диапазон условий и фильтруемый список должны быть разделены. по крайней мере, одним пустым столбцом или строкой. Затем для запуска процесса фильтрации необходимо указать ячейку в фильтруемом списке и выбрать команду Данные/Фильтр/Расширенный фильтр. В результате появится диалоговое окно Расширенный фильтр (рис. 4).

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

С помощью переключателя Обработка, расположенного в этом окне, пользователь должен указать программе, где следует размещать отфильтрованные записи – на месте или в другом диапазоне. Чтобы поместить отфильтрованные записи за пределами существующего списка, следует установить переключатель Обработка в положение Скопировать результат в другое место, а в поле Поместить результат в диапазон указать верхнюю левую ячейку области вставки. Затем необходимо ввести в поле Диапазон критериев ссылку на диапазон условий отбора, включая заголовки.

С дополнительным материалом по теме можно ознакомиться в литературе [1, 3, 4, 7].

Задание

1. Создать список в виде таблицы 1.

Таблица1

2. Ввести 5 записей.

3. Поля Общий размер и Общая стоимость сделать вычисляемыми.

4. Общий размер вычислить как сумму ячеек с адресами D3+E3+F3.

5. Общая стоимость должна быть вычислена по формуле D3*G3+E3*H3+F3*I3.

6. Выполнить сортировку данных по номеру садового участка и ФИО.

Для этого выделить данные. Во вкладке Данные выбрать сортировку. С помощью вкладки на ленте Данные выбрать Автофильтр и отобрать данные о садовых участках, у которых самые неплодородные земли. Выделить данные без 1-ой строки и выполнить команду Данные-Фильтр-Автофильтр.

В строке заголовка таблицы появятся стрелки раскрывающегося списка. Щелкнуть столбец неплодородные земли и задать условие отбора. Условие задать таким образом, чтобы в списке осталось 3-4 владельца садового участка. Результат показать преподавателю и восстановить базу данных.

7. Расширенный фильтр. Выдать на экран владельцев, у которых самые плодородные земли и больше всего строений.

Для этого скопировать БД (базу данных) на новый лист и назвать лист Расширенный. Задать диапазон условий ниже БД. Скопировать область заголовка на свободное место за БД и задать условия отбора в полях Плодородные земли и строения. Затем установить курсор в БД и выдать команду Данные – Фильтр - Расширенный фильтр.

Откроется диалоговое окно, в котором необходимо:

– установить флажок - Скопировать результат на новое место;

– в строке исходный диапазон указать адрес БД;

– в строке условие задать диапазон условий;

– для результата отвести место на свободном поле после диапазона условий.

Если такие записи есть в вашей БД, то они будут выведены на экран. Для задания условий использовать операции отношения <, >, < >, > =, < =, =.

8. Выдать список владельцев садовых участков, у которых самые неплодородные земли и меньше всего строений, т.е. нуждающихся в материальной помощи. Скопировать БД на новый лист и выбрать таких владельцев с помощью расширенного фильтра. Скопировать список на новый лист. Оформить с заголовком Список, поместить в список дату с помощью функции Сегодня. Список должен содержать следующие столбцы: Имя владельца, номер садового участка, общий размер участка, размер неплодородной земли и количество строений, остальные столбцы выделить и скрыть: Формат – Столбцы – Скрыть.

Работу сохранить в книге База данных_ФИО_студента.xlsx.

Содержание отчета

Отчетом является файл База данных_ФИО_стутента.xlsx, созданный в результате выполнения задания.

Технология выполнения работы

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

Вопросы для защиты работы

1. Описать технологию отбора записей с помощью Автофильтра.

2. Когда нужно для отбора данных использовать Расширенный фильтр?

3. Как выполнить сортировку записей?

4. Требования, которым должны удовлетворять списки?

Лабораторная работа № 9
Работа в среде системы управления реляционными базами данных MS Access

Время выполнения – 6 часов.

Цель работы

Освоение технологии работы в MS Access 2007.

Задачи лабораторной работы

После выполнения работы студент должен:

1) освоить интерфейс программы;

2) уметь создавать структуру базы данных;

3) освоить приемы создания объектов базы данных (таблиц, форм, запросов, отчетов);

4) научиться устанавливать связи между таблицами;

5) уметь производить отбор данных по заданным критериям.

Перечень обеспечивающих средств

Для обеспечения выполнения работы необходимо иметь компьютер со следующим обеспечением: операционная система Windows и MS Office 2007 и выше.

Общие теоретические сведения

Современные информационные системы характеризуются большими объемами хранимых данных, их сложной организацией, а также высокими требованиями к скорости и эффективности обработки этих данных. Это становится возможным при использовании специальных программных средств – систем управления базами данных (СУБД).

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

Система управления базами данных (СУБД) – это комплекс программных и языковых средств, необходимых для создания обработки баз данных и поддержания их в актуальном состоянии.

Почти все современные СУБД основаны на реляционной модели данных. Название " реляционная" связано с тем, что каждая запись в такой базе данных содержит информацию, относящуюся (related) только к одному объекту. Все данные в реляционной БД представлены в виде таблиц. Каждая строка таблицы содержит информацию только об одном объекте и называется записью. Столбец таблицы содержит однотипную для всех записей информацию и называется полем. Для успешного функционирования базы данных важна правильная организация данных в ней. При определении структуры данных в базе выделяют следующие основные понятия.

Класс объектов - совокупность объектов, обладающих одинаковым набором свойств. Например, в базе данных о ВУЗе классами объектов могут быть студенты, преподаватели, предметы.

Свойство (атрибут) - определенная часть информации о некотором объекте. Хранится в виде столбца (поля) таблицы. Например, фамилия, имя, отчество - это свойства для объекта Студент.

Связь (отношение) - способ, которым связана информация о разных объектах.