43. Использование фильтров и автофильтров в ms-Excel при решении социально-экономических задач.

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

Запросы в Excel реализованы с помощью фильтров. Фильтрация списка - это процесс, в результате которого в списке скрываются все строки, не удовлетворяющие критериям фильтрации, а видимыми остаются только те, которые соответствуют условиям запроса (остаётся выборка).

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

Формирование запросов с помощью автофильтра.

Для установки автофильтра на все поля необходимо выполнить: активизировать любую ячейку списка (базы данных), щёлкнув мышкой по этой ячейке; щёлкнуть мышкой по кнопке меню <Данные>; в раскрывшемся меню щёлкнуть мышкой по строке <Фильтр>; в появившемся справа подменю установить флажок на строке <Автофильтр>, щёлкнув мышкой по строке.

Для установки автофильтра на одно поле необходимо выполнить: активизировать заголовок нужного поля, щёлкнув мышкой по нему; нажать комбинацию клавиш Sift+Ctrl+(стрелка вниз) .

После установки автофильтра в ячейках с названиями всех полей или одного поля появляется кнопка в виде треугольника вершиной вниз.

Отмена установки автофильтра осуществляется той же последовательностью команд, что и при установке, за исключением последней команды. Вместо установки флажка на строке <Автофильтр> надо снять флажок щелчком мышки по нему.

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

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

- (Первые 10 …) - для формирования запросов только по числовым полям. После щелчка мышкой по этой строке появляется диалоговое окно <Наложение условия по списку>. В данном окне устанавливаются условия выборки: количество записей, наибольших или наименьших, элементов списка или % от количества элементов;

(Условие …) - для установки критериев выборки. После щелчка мышкой по этой строке появляется диалоговое окно <Пользовательский автофильтр>, который позволяет создать критерий выборки. Критерий может состоять не более чем из двух условий, соединённых операциями <И>, <ИЛИ>. Каждое из условий представляет собой выражение логического типа, содержащее любые операции отношения (<, <=, =, <>, >, >=). В пользовательском автофильтре эти операции представлены в виде текста и предназначены для создания условий преимущественно по числовым полям и полям типа дата и время. Для создания условий по текстовым полям предназначены следующие ограничения: <начинается с>, <не начинается с>, <заканчивается на>, <не заканчивается на>, <содержит>, <не содержит>. Кроме того, при создании текстовых критериев можно использовать символы шаблона:

"*" - для обозначения последовательности произвольной длины, состоящей из любых символов;

"?" - для обозначения символа, стоящего на определённом месте.

При включении символов шаблона в критерий в качестве обычных символов перед ними надо ставить тильду "~".

Формирование запросов с помощью расширенного фильтра.

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

Для установки расширенного фильтра необходимо выполнить:активизировать любую ячейку списка (базы данных), щёлкнув мышкой по этой ячейке;щёлкнуть мышкой по кнопке меню <Данные>;

в раскрывшемся меню щёлкнуть мышкой по строке <Фильтр>;в появившемся справа подменю установить флажок на строке <Расширенный фильтр>, щёлкнув мышкой по строке.

При работе с расширенным фильтром необходимо определить три области:

1) исходный диапазон - вся область базы данных, например, $A$1:$H$26;

2) диапазон условий - область, содержащая критерии фильтрации, например, Критерии! $A$28:$C$30;

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

Назначение флажка <Только уникальные записи> в окне диалога <Расширенный фильтр> очевидно. Установка этого флажка при копировании выборки позволяет убрать из неё все повторяющиеся записи. При отсутствии диапазона условий с помощью этого флажка можно избавиться от повторяющихся записей в исходном списке.

При создании интервала критериев необходимо руководствоваться следующими правилами:

диапазон условий должен состоять не менее чем из двух строк: первая строка - строка заголовков, последующие строки - соответствующие критерии;

интервал критериев должен располагаться вне списка или на другом листе;

интервал критериев должен располагаться вне списка или на другом листе;

в интервале критериев не должно быть пустых строк;

если условия располагаются в одной строке, то это означает одновременность их выполнения, то есть считается, что между ними поставлена логическая операция <И>;

если условия располагаются в разных строках, то требуется выполнение хотя бы одного условия, то есть считается, что они соединены логической операцией <ИЛИ>.

При формировании текстовых критериев необходимо помнить о следующих правилах:

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

если содержимое ячейки представляет собой текстовую константу вида ">БУКВА" или "<БУКВА", то такому условию соответствует любой текст, начинающийся с этой и последующих БУКВ, или начинающийся с предшествующих ей БУКВ;

для поиска текста на полное совпадение содержимое ячейки с критерием должно иметь вид ="=ТЕКСТ";

в текстовых критериях можно использовать символы шаблона

Вычисляемый критерий представляет собой формулу, в которой обязательно имеется ссылка на соответствующую ячейку первой строки списка. Так как эта формула является логическим выражением, то в ячейке, её содержащей, отображается результат вычисления (ИСТИНА или ЛОЖЬ) для первой записи списка. А после фильтрации в списке будут скрыты те записи, для которых при вычислении формулы получается значение ЛОЖЬ.

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

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

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

ссылки на ячейки вне списка должны быть абсолютными.