Использование фильтров и автофильтров в 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. Установив флажок Сохранить данные вместе с таблицей можно создать дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении.
После щелчка по кнопке Готово в третьем диалоговом окне Мастер сводных таблиц на рабочем листе будет создана сводная таблица.
Дата добавления: 2018-05-13; просмотров: 293; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!