Студопедия КАТЕГОРИИ: АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция |
Использование фильтров и автофильтров в 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-29; просмотров: 180. stydopedya.ru не претендует на авторское право материалов, которые вылажены, но предоставляет бесплатный доступ к ним. В случае нарушения авторского права или персональных данных напишите сюда... |