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

EXCEL дает возможность быстро и удобно обрабатывать табличные данные с помощью простого средства - автофильтра,позволяющего выделять в таблице строки, удовлетворяющие заданным критериям. Таким образом, с помощью EXCEL можно проводить операции, обычно «поручаемые» системам управления базами данных (СУБД). Рассмотрим действие автофильтра на примере таблицы из задания 6.

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

После выполнения этой команды около каждого названия столбца появится кнопка, на которой изображена стрелка, направленная вниз. После щелчка по этой кнопке открывается список, в котором представлены значения, принимаемые в этом столбце, а также опция “Настройка”. Например, если щелкнуть по кнопке около заголовка столбца Факультет, то можно увидеть перечисленные в столбце названия всех факультетов. Если щелкнуть по названию факультета, то в таблице будут отфильтрованы строки, соответствующие данному факультету.

Если же указать строку Настройка, то откроется диалогПользовательский автофильтр,в котором можно указать соединение нескольких критериев. Можно задать критерии поиска для одного столбца, объединив их с помощью логического умножения (“И”), либо логического сложения (“ИЛИ”). Список условий содержит знаки “=“, “>“ , “<“ и т.п. (чтобы открыть этот список, щелкните по кнопке “ стрелка вниз”). Выберите необходимые условия по обоим критериям, соединив их с помощью “И”,либо “ИЛИ”.Можно задать критерии поиска одновременно для нескольких столбцов.

В условиях поиска можно задавать также шаблоны, в качестве которых выступают знаки “*” и “?”.Вопросительный знак используется вместо одного любого символа, звездочка - вместо нескольких любых символов. Например, если задать условие поиска по номеру зачетной книжки “=95*”, то через фильтр пройдут строки, соответствующие студентам, у которых номер зачетной книжки начинается с “95”.

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

При создании шапок таблиц необходимо учесть замечание относительно числа строк, сделанное в разделе 7.

Для расчета промежуточных результатов (например, сумм, относящихся к каким-либо категориям объектов) необходимо:

- отсортировать таблицу по столбцу, промежуточные суммы для которого вы хотите получить (в варианте 2 - по “Наименованию”),

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

- выбрать в меню Данные пункт Итоги - откроется диалог “Промежуточные итоги”,

- выбрать необходимое имя столбца, вид функции (например -сумма),

- установить флажки “Заменить текущие итоги” и “Итоги под данными”,

- щелкнуть по кнопке ОК.

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

Для отмены результатов расчета промежуточных итогов и возврата к исходной таблице выполнить Данные - Итоги - Убрать все.

Задание 7.Подготовьте документ, соответствующий Вашему варианту - оформите заголовок, “шапку”, заполните графы с исходными данными. Введите необходимые для расчетов формулы. Проведите фильтрацию данных согласно Вашему варианту.

Вариант 1. Создайте такую же таблицу, как в задании 6, введя не менее 25 строк. Проведите фильтрацию по критериям:

1. Все студенты факультета ФКФ.

2. Все студенты факультета ПЭФ.

3. Студенты с факультета ФКФ или АПФ.

4. Студенты, фамилии которых начинаются на букву “А”.

5. Все студенты с 1-го курса.

6. Студенты с факультетов ФКФ или АПФ, которые учатся на 2 курсе.

7. Студенты с факультета ФКФ, которые учатся в 1-й группе 1-го курса.

8. Студенты с факультетов ФКФ или АПФ, которые учатся на 1 или 2 курсах.

9. Студенты с факультетов ПЭФ или АПФ, которые учатся на 1 курсе в 1 группе.

10. Студенты, фамилии которых начинаются на букву “А”, со всех факультетов, кроме ФКФ, которые учатся на 1 или 2 курсах (использовать критерий “>< ФКФ”).

Вариант 2. Учет движения изделий и материалов на складе:

Наименование	Тип	Цена за ед.	Он	Приход	Расход	Ок	Ок, руб.
Двери, шт.
Доски, кубм.
Доски, кубм.
Кирпич, пач.	к
Кирпич, пач.	с
Окна, шт.
Окна, шт.
Окна, шт.
Плитка, пач.	кер.
Плитка, пач.	облиц.
Трубы, м
Трубы, м
Цемент, меш.

В этой таблице Он - остаток материалов и изделий на начало периода, Ок - остаток на конец периода. Необходимо заполнить столбец Ок (руб) = Цена за ед. * Ок - стоимость остатка изделий и материалов в рублях (использовать процедуру копирования формул).

1.Выбрать записи (строки), по которым был только приход, и не было расхода.

2. Выбрать записи (строки), по которым был только расход, и не было прихода.

3. Для таблицы подсчитать промежуточные суммы по Ок (руб.) по видам изделий и материалов (трубы, окна, кирпич и т.д.), а также общую сумму по всей таблице.

4. Вставить в таблицу столбец Он (руб.), рассчитать эту величину, а затем подсчитать промежуточные суммы по Он (руб.) по видам изделий и материалов (трубы, окна, кирпич и т.д.), а также общую сумму по всей таблице.

5. Выбрать записи по всем видам окон и дверей, подсчитать промежуточные суммы по Ок (шт.) и Ок (руб.) по окнам и дверям, а также общую сумму по этим изделиям.

6. Выбрать записи по всем видам плитки и кирпича, подсчитать промежуточные суммы по Ок (руб.) по плитке и кирпичу, а также общую сумму по этим изделиям.

7. Выбрать изделия и материалы, для которых величина Ок (руб.) лежит в интервале от 3000 до 5000.

8. С помощью функции Среднподсчитать среднее значение Ок (руб.).

Вариант 3. Список студентов 1 курса _____группы _________ факультета:

				Оценки в зимнюю сессию
Фамилия И.О.	Дата рожд.	Пол	Где проживает	Политэк.	В. мат-ка	Технол.
Иванова А..А.	30.06.78	ж	дом
Валиев А.Р.	1.01.79	м	общ
Владимиров С.К.	22.12.77	м	дом
...

Необходимо ввести в эту таблицу не менее 20 строк. Как видим, шапка таблицы должна состоять из двух строк. В качестве ключевой строки следует выбрать нижнюю (вторую) строку. Поэтому заголовки 1-го - 4-го столбцов следует расположить в этой строке.

По данным этой таблицы надо:

1. Выбрать всех отличников.

2. Выбрать всех отличников - юношей.

3. Выбрать юношей, сдавших сессию без троек.

4. Выбрать девушек 1978 года рождения.

5. Выбрать юношей 1978 и 1979 годов рождения, проживающих в общежитии.

6. Сформировать список юношей по убыванию возраста.

7. Используя меню Данные - Итогии функцию Средннайти средний балл по политэкономии.

Найти, сколько студентов сдали высшую математику на 5 (использовать пункт меню Данные - Фильтр, а затем к отфильтрованной таблице применить пункт меню Данные - Итоги и функцию Счет).

Вариант 4. Справочник автотранспортных средств:

		Номер АТС
Владелец	Дата приобрет. ппппорпорпорпорпоррпппприприобрет.	N	Код области оообласти	Марка	Модель	Цвет
Иванов А.А	11.01.95	А456ТР		Жигули		зел
Петров О.В.	31.10.96	А568ПР		Москвич		зел
Сидоров М.А	14.08.95	Б235ОП		Жигули		красн
Мельник А.А.	18.10.96	В238ФВ		Жигули		зел
...

Заполните 25 строк справочника на основе следующей информации:

Марка Модели

Жигули 2101, 2102, 2106, 2108, 2109

ГАЗ 21, 2410, 2411, 3102

Москвич 408, М2140, М2141

Владелец - Фамилия И.О. или наименование организации.

N АТС - номер (буква, три цифры, две буквы), код области - число от 1 до 99, например, Саратовская область - 64, Волгоградская область - 34, Москва - 77.

1. Выбрать все Жигули, приобретенные после 1.01.96г.

2. Выбрать Жигули и Москвичи. приобретенные в 1996 году.

3. Выбрать все Жигули, в номере которых первая цифра 4 или 1.

4. Выбрать все Саратовские машины красного цвета.

5. Выбрать все Москвичи зеленого цвета, приобретенные до 15.03.97.

6. Сколько всего в справочнике Жигулей, приобретенных после 1.01.96? (Использовать пункт меню Данные - Фильтр, а затем к отфильтрованной таблице применить пункт меню Данные - Итоги и функцию Счет).

7. Сколько всего в справочнике Жигулей, приобретенных в 1996 году? (См. пояснение к п. 6).

9. Усиленный фильтр*

Команда Усиленный фильтр, в отличие от команды Автофильтр, позволяет:

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

- создавать критерии с тремя и более условиями для заданного столбца, связанными по крайней мере одним союзом ИЛИ,

- создавать вычисляемые критерии.

Команда Усиленный фильтр,в отличие от командыАвтофильтр,требует задания критериев фильтрации в отдельном интервале рабочего листа -обычно это делают ниже исходной таблицы.

Интервал критериев должен состоять не менее, чем из двух строк.В первой строке размещаются имена полей, т.е. заголовки столбцов, а во второй и последующих строках - соответствующие критерии фильтрации. Если не используются вычисляемые критерии, то заголовки в интервале критериев должны точно совпадать с заголовками столбцов. Для этого надо выделить и скопировать заголовки столбцов в первую строку интервала критериев. Так как в интервале критериев могут использоваться не все столбцы списка, то ненужные заголовки следует удалить.

Удобно располагать и результат фильтрации в новом интервале рабочего листа, а не на старом месте. Для этого следует скопировать заголовки столбцов еще раз, но уже в интервал рабочего листа, где расположится результат фильтрации.

Пример. Для таблицы варианта 2 задания 7 вывести строки по всем окнам, трубам и цементу. Порядок выполнения задачи:

Интервал критериев создаем в блоке А18:А21, в ячейку А18 копируем заголовок столбца “Наименование”, а в ячейках А19-А21 записываем критерии. Выбрать команду Усиленный фильтриз подменю Фильтр меню Данные. Откроется диалоговое окно Усиленный фильтр.

В окне Усиленный фильтрустановить опцию“Копировать на другое место”.

Ввести интервалы: исходной таблицы, критериев, расположения результата фильтрации. Обратить внимание на то, что адресация должна быть абсолютной. Использовать отметку мышью левой верхней и правой нижней ячеек, а также знак “:” для указания интервала. Щелкнуть по ОК. Проанализировать результаты фильтрации и убедиться, что выделенные строки удовлетворяют критериям.

Интервал критериев может содержать любое число условий, которые интерпретируются следующим образом:

- условия на одной строке объединяются по правилу логического умножения И,

- условия на отдельных строках объединяются по правилу логического сложения ИЛИ.

Вычисляемые критерии.Вычисляемыми критериями называются критерии, включающие более сложные операции, чем простое сравнение столбца с константой. При этом можно использовать функции EXCEL. Чтобы избежать ошибок, используйте следующие правила:

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

- необходимо отличать случаи, когда адресация относительна, от случаев абсолютной адресации.

Пример.Для таблицы варианта 2 задания 7 найти строительные изделия и материалы, для которых стоимость Ок руб. больше средней стоимости Ок руб. по всем изделиям и материалам. Использовать функцию СРЗНАЧ(интервал) для вычисления среднего значения числовых данных, введенных в интервалячеек.

Пусть исходная таблица записана в блоке $A$2:$H$15, причем, в столбце $H$3:$H$15 расположим данные по Ок руб., интервал критериев введем в диапазон $A$33:$I$34, заголовок критерия - в ячейку $I$33, в ячейку $I$34 (эту ячейку следует выбрать в стороне от основной таблицы) введем критерий поиска:

=H3>СРЗНАЧ($H$3:$H$15),

в строку 38 скопируем из исходной таблицы заголовки столбцов и определим интервал $A$38:$H$46 для вывода результатов (мы не знаем заранее размер этого интервала, поэтому его надо определить “с запасом”).

Обратите внимание на то, что везде использованы абсолютные адресации, кроме ячейки H3 в критерии. Это сделано потому, что на каждом шаге фильтрации нужно сравнивать содержимое текущей ячейки столбца H (эти ячейки расположены в диапазоне H3:H15) со средним значением всех данных из диапазона H3:H15.

Затем применим команду Усиленный фильтриз подменю Фильтр меню Данные.

Результат фильтрации приведен в таблице, где представлены все строки, для которых Ок > СРЗНАЧ(H3:H15) = 2863.69 руб.

Наименование	Тип	Цена за ед.	Он	Приход	Расход	Ок	Ок, руб.
Двери, шт.
Кирпич, пач.	к
Плитка, пач.	метл
Цемент, меш.

Таким образом, вычисляемый критерий не только не должен иметь тот же заголовок, что и столбцы исходной таблицы, но и располагаться не под столбцами таблицы (удобнее всего расположить его справа от таблицы). Два вычисляемых критерия, объединяемые по правилу логического умножения “И”, располагаются в разных столбцах (сбоку от таблицы) и должны иметь каждый свой заголовок. Два вычисляемых критерия, объединяемые по правилу логического сложения “ИЛИ”, располагаются в одном столбце (сбоку от таблицы) один под другим и имеют общий заголовок.