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

2) ввести команду меню ДАННЫЕÞФильтрÞАвтофильтр;

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

4) указать требуемые значения или выбрать строку «условие» и задать критерии выборки в диалоговом окне Пользовательский автофильтр.

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

Каждая часть условия может включать:

· оператор отношения (сравнения). При задании критериев выборки могут использоваться следующие операторы сравнения:

Больше > = Больше или равно

Для восстановлениявсех строк исходной таблицы нужно щелкнуть мышью по кнопке со стрелкой (синего цвета) и в раскрывшемся списке выбрать строку «все» или выполнить команду ДАННЫЕÞФильтрсÞОтобразить все.

Для отменырежима фильтрации нужно установить курсор внутри таблицы и снова ввести команду меню ДАННЫЕÞФильтрÞАвтофильтр(убрать переключатель).

Фильтрация данных с использованием расширенного фильтра.Расширенный фильтр позволяет формировать множественные критерии выборки и осуществлять более сложную фильтрацию данных электронной таблицы с заданием набора условий отбора по нескольким столбцам.

Для фильтрации записей списка расширенный фильтр обеспечивает использование двух типов критериев:

Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню ДАННЫЕÞФильтрÞРасширенный фильтр.

Между значениями условий отбора и таблицей должна находиться, как минимум, одна пустая строка.

Если критерии отбора (условия фильтрации) вводятся в одной строке для разных столбцов, то они считаются связанными условием «И». Если критерии отбора записываются в разных строках, то они считаются связанными условием «ИЛИ».

После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду ДАННЫЕÞФильтрÞРасширенный фильтри в диалоговом окне Расширенный фильтруказывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазона условий.

Записи можно фильтровать на месте или одновременно с выполнением фильтрации копировать в указанную область на текущем рабочем листе.

Чтобы скопировать отфильтрованные строки в другую область листа, следует установить переключатель Скопировать результаты в другое место,перейти в поле Поместить результат в диапазони указать верхнюю левую ячейку области вставки отобранных данных.

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

Источник

Какие существуют фильтры для выборки данных

Фильтрация данных в списке ≈ это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные, Фильтр. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командой Данные, Форма.

Автофильтрация

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

Рис. 3.36. Список с автофильтром

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

Рис. 3.37. Диалоговое окно ╚Наложение условия по списку╩

Рис. 3.38. Диалоговое окно ╚Пользовательский фильтр╩

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

Каждая часть условия включает:

>= п1 И п1 ≈ отобрать все записи, которые не содержат кода предмета п1.

Расширенный фильтр

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

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

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

Технология использования расширенного фильтра состоит из двух этапов:

этап 1 ≈ формирование области критериев поиска;

этап 2 ≈ фильтрация записей списка.

Э т а п 1. Формирование диапазона условий для расширенного фильтра. Область критериев поиска содержит строку имен столбцов и произвольное число строк для задания поисковых условий. Рекомендуется скопировать первую строку с именами полей из области списка в область, где будет формироваться критерий отбора записей (на тот же или другой лист, в другую рабочую книгу). Далее ненужные имена столбцов из диапазона условий можно удалить.

Критерий сравнения формируется при соблюдении следующих требований:

Правила формирования множественного критерия:

2. Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Пример. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами: 1-й способ. Множественный критерий сравнения ≈ все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка≈ оператор сравнения со значением константы.

2’й способ. Множественный критерий сравнения ≈ все условия (точи значения полей) находятся в одной строке, столбец Оценка используется дважды, связка И.

3-й способ. Множественный критерий сравнения ≈ условия (точные значения полей) записаны в двух строках, связка ИЛИ.

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

Внимание! Имя столбца, содержащего формулу вычисляемого критерия, должно отличаться от имени столбца в списке.

Пример. Выбрать записи о сдаче экзаменов студентами группы 133 с оценкой ниже общего среднего балла или записи с оценкой 5:

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ ≈ результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому:

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

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

Рис. 3.39. Диалоговое окно ╚Расширенный фильтр╩

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

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

Фильтрация с помощью формы данных

ППП Excel 97 позволяет работать с отдельными записями списка с помощью экранной формы (рис.3,40). Основные операции обработки записей списка: последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка,

При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей ≈ названия столбцов списка.

Рис.3.40. Экранная форма для работы со списком записей

ЗАДАНИЕ 1

Выберите данные из списка по критерию отбора, используя Автофильтр.

ТЕХНОЛОГИЯ РАБОТЫ

4. Выберите из списка данные, используя критерий ≈ для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4. Для этого воспользуйтесь аналогичной п. 3 технологией фильтрации

5.Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные╩ Фильтр, Автофильтр.

6.Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

ЗАДАНИЕ 2

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

1.Проведите подготовительную работу ≈ переименуйте новый лист на Расширенный фильтр и скопируйте на него исходную базу данных <см.рис.3.35).

2. Скопируйте имена полей списка в другую область на том же листе.

3. Сформируйте в области условий отбора Критерий сравнения ≈ о сдаче экзаменов! студентами группы 133 по предмету п1 на оценки 4 или 5.

4.Произведите фильтрацию записей на том же листе.

5.Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.

6.Сформируйте в области условий отбора Вычисляемый критерий ≈ для каждого пре╜подавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий ≈ л; результат отбора поместите на новый рабочий лист.

7.Произведите фильтрацию записей на новом листе.

8.Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.

ТЕХНОЛОГИЯ РАБОТЫ

1. Проведите подготовительную работу:

Этап 1. Формирование диапазона условий по типу Критерий сравнения

2. Скопируйте все имена полей списка (см. рис. 3.35) в другую область на том же листе например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 ≈ имена полей области критерия, J2:О5 ≈ область значений критерия.

3. Сформируйте в области условий отбора Критерий сравнения ≈ о сдаче экзаменов студентами группы 133 по предметуп1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

Этап 2. Фильтрация записей расширенным фильтром.

4. Произведите фильтрацию записей на том же листе:

Скопировать результат в другое место: установите флажок

Исходный диапазон: A1:G17

Диапазон условия: J1:O5

Поместить результат в диапазон: J6

5. Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе, соблюдая технологию п.З и п.4.

Этап 1. Формирование диапазона условий по типу Вычисляемый критерий.

6. Сформируйте в области условий отбора Вычисляемый критерий ≈ для каждого пре╜подавателя выберите сведения о сдаче студентами экзамена на оценку выше средней, вид занятий ≈ л; результат отбора поместите на новый рабочий лист. Для этого:

где G2 ≈ адрес первой клетки с оценкой в исходном списке,

СРЗНАЧ ≈ функция вычисления среднего значения.

Этап 2. Фильтрация записей расширенным фильтром.

7. Произведите фильтрацию записей на новом листе;

Скопировать результат в другое место: установите флажок

Исходный диапазон: A1:G17

Диапазон условия: Л:05

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

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

ЗАДАНИЕ 3

Используя Форму, выберите данные из списка.

1. Проведите подготовительную работу ≈ переименуйте новый лист на Форма и скопируйте на него исходную базу данных (см. рис.3,35).

2. Просмотрите записи списка с помощью формы данных, добавьте новые.

4. Просмотрите отобранные записи.

5. Сформируйте собственные условия отбора записей и просмотрите их,

ТЕХНОЛОГИЯ РАБОТЫ

1. Проведите подготовительную работу:

3. Сформируйте условие отбора ≈ для преподавателя ≈ а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий ≈ л. Для этого:

5. Аналогично сформируйте собственные условия отбора записей и просмотрите их.

Источник

Фильтрация (выборка) данных

ФИЛЬТРАЦИЯ (ВЫБОРКА) ДАННЫХ

Фильтрация данных в списке — это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные, Фильтр. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр.

Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командой Данные, Форма.

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

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

• все — выбираются все записи без ограничений;

• первые 10 — данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис.3.37) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;

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

• условие — выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис.3.38).

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

Каждая часть условия включает:

· оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), =п* — отобрать все записи, которые содержат код предмета, начинающийся с буквы п;

>= п1 И п1 — отобрать все записи, которые не содержат кода предмета п1.

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

Отмена результата фильтрации и возврат к исходному состоянию списка производятся повторным вводом команды Данные, Автофильтр.

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

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

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

Технология использования расширенного фильтра состоит из двух этапов:

· этап 2 — фильтрация записей списка.

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

Критерий сравнения формируется при соблюдении следующих требований:

• состав столбцов области критериев определяется столбцами, по которым задаются условия фильтрации записей;

• имена столбцов области критериев должны точно совпадать с именами столбцов исходного списка;

• ниже имен столбцов располагаются критерии сравнения типа:

· значения, формируемого с помощью операторов отношения;

Правила формирования множественного критерия:

1. Если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И.

2. Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

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

Внимание! Имя столбца, содержащего формулу вычисляемого критерия, должно отличаться от имени столбца в списке.

Пример. Выбрать записи о сдаче экзаменов студентами группы 133 с оценкой ниже общего среднего балла или записи с оценкой 5:

После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 3.39).

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

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

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

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

Фильтрация с помощью формы данных

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

При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей — названия столбцов списка.

Для того что бы начать пользоваться Формой данных, необходимо добавить кнопку «Форма данных» на панель быстрого доступа, т. к. в Excel 2007 кнопки Форма нет на ленте. Делается это следующим образом:
1) Щелкните стрелку на панели быстрого доступа и выберите элемент Другие команды.
2) В открывшемся окне Параметры Excel в поле «Выбрать команды из» выберете из списка «Все команды».
3) В списке выберите кнопку «Форма» и щелкните элемент «Добавить».

Выберите данные из списка по критерию отбора, используя Автофильтр.

1. Проведите подготовительную работу — переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных (см. рис. 3.35).

2. Выберите из списка данные, используя критерий:

• для преподавателя — al выбрать сведения о сдаче экзамена на положительную оценку,

3. Отмените результат автофильтрации.

4. Выберите из списка данные, используя критерий: для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4.

5. Отмените результат автофильтрации.

6. Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

1. Проведите подготовительную работу:

• переименуйте ЛистЗ — Автофильтр;

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

2. Выберите из списка данные, используя критерий — для преподавателя — al выбрать сведения о сдаче экзамена на положительную оценку, вид занятий — л.

• установите курсор в область списка и выполните команду Данные, Фильтр, Автофильтр; в каждом столбце появятся кнопки списка;

• сформируйте условия отбора записей:

• в столбце Таб. № препод, нажмите кнопку «стрелка вниз», из списка условий отбора выберите al;

• в столбце Оценка нажмите кнопку «стрелка вниз», из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;

• в столбце Вид занятия нажмите кнопку «стрелка вниз», из списка условий отбора выберите л.

3. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные, Фильтр, Автофильтр.

4. Выберите из списка данные, используя критерий — для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4. Для этого воспользуйтесь аналогичной п. 3 технологией фильтрации.

5.Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные, Фильтр, Автофильтр.

6. Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

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

1. Проведите подготовительную работу — переименуйте новый лист на Расширенный фильтр и скопируйте на него исходную базу данных <см. рис.3.35).

2. Скопируйте имена полей списка в другую область на том же листе.

3. Сформируйте в области условий отбора Критерий сравнения — о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5.

4. Произведите фильтрацию записей на том же листе.

5. Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.

6. Сформируйте в области условий отбора Вычисляемый критерий — для каждого преподавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий — л; результат отбора поместите на новый рабочий лист.

7. Произведите фильтрацию записей на новом листе.

8. Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.

1. Проведите подготовительную работу:

• переименуйте Лист4 — Расширенный фильтр;

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

2. Скопируйте все имена полей списка (см. рис. 3.35) в другую область на том же листе, например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 — имена полей области критерия, J2:O5 — область значений критерия.

3. Сформируйте в области условий отбора Критерий сравнения — о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

• в столбец Номер группы — точное значение — 133;

• в столбец Код предмета — точное значения — п1;

• в столбец Оценка — условие — >3

4. Произведите фильтрацию записей на том же листе:

• установите курсор в область списка (базы данных);

• выполните команду Данные, Фильтр, Расширенный фильтр;

• в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры, например:

Скопировать результат в другое место: установите флажок

Исходный диапазон: A1:G17

Диапазон условия: J1 :O5

Поместить результат в диапазон: J6

5. Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе, соблюдая технологию п. З и п.4.

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

• в столбец Вид занятия введите точное значения — букву л;

• переименуйте в области критерия столбец Оценка, например, на имя Оценка 1;

• в столбец Оценка 1 введите вычисляемый критерий, например, вида

7. Произведите фильтрацию записей на новом листе:

• установите курсор в область списка (базы данных);

• выполните команду Данные, Фильтр, Расширенный фильтр;

• в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры, например:

Скопировать результат в другое место: установите флажок

Исходный диапазон: A1:G17

Диапазон условия: J 1:05

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

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

Используя Форму, выберите данные из списка.

1. Проведите подготовительную работу — переименуйте новый лист на Форма и скопируйте на него исходную базу данных (см. рис.3.35).

2. Просмотрите записи списка с помощью формы данных, добавьте новые.

4. Просмотрите отобранные записи.

5. Сформируйте собственные условия отбора записей и просмотрите их.

1. Проведите подготовительную работу:

• переименуйте ЛистЗ — Форма;

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

• установите курсор в область списка и выполните команду Данные, Форма.

3. Сформируйте условие отбора — для преподавателя — al выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий —л. Для этого:

• в пустых строках имен полей списка введите критерии:

• в строку Таб№ препод, введите a l;

• в строку Вид занятия введите л;

• в строку Оценка введите условие > 2.

5. Аналогично сформируйте собственные условия отбора записей и просмотрите их.

Источник