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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Работа со сводными таблицами в MS-Excel при решении социально-экономических задач.

Сводные таблицы - одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.

Выполним команду Данные Сводная таблица. В первом диалоговом окне Мастер сводных таблиц необходимо установить переключатель в положение, показывающее, откуда берутся данные для сводной таблицы:

1) в списке или базе данных MS Excel - если данные берутся с одного рабочего листа;

2) во внешнем источнике данных - если данные берутся из внешней базы данных;

3) в нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;

4) в другой сводной таблице - если сводная таблица создается на основании данных другой сводной таблицы.

В этом же диалоговом окне указывается вид создаваемого отчета - сводная таблица или сводная диаграмма.

В поле Диапазон второго диалогового окна Мастер сводных таблиц необходимо указать диапазон, на основании которого строится сводная таблица.

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

Структуру сводной таблицы можно создать, воспользовавшись кнопкой Макет…, третьего диалогового окна Мастер сводных таблиц.

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

В окне имеются четыре области:

Строка - для использования данных поля, расположенного в этой области, в качестве заголовка строки;

Столбец - для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;

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

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

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

Кнопка Параметры… третьего диалогового окна Мастер сводных таблиц позволяет задать некоторые параметры, определяющие вид сводной таблицы. Например, в поле Имя можно задать название таблицы. По умолчанию сводные таблицы называются Сводная таблица 1, Сводная таблица 2 и т.д. Устанавливая флажок Общие итоги по столбцам или флажок Общие итоги по строкам можно подвести итоги по столбцам или строкам в сводной таблице. Установка флажка Автоформат позволяет пользоваться средствами автоформата MS Excel. Установив флажок Сохранить данные вместе с таблицей можно создать дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении.

После щелчка по кнопке Готово в третьем диалоговом окне Мастер сводных таблиц на рабочем листе будет создана сводная таблица.