Фильтрация {выборка) данных
в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. С помощью фильтров пользователь может в удобной для себя форме выводить или удалять (скрывать) записи списка.
В отличие от сортировки данные при фильтрации не переупорядочиваются, а лишь скрываются те записи, которые не отвечают заданным критериям выборки.
Отобранные записи можно форматировать или удалять, копировать в отдельную область таблицы, распечатывать, а также использовать для последующих вычислений или построения диаграмм.
Фильтрация данных в электронных таблицах может выполняться двумя способами: с помощью автофильтра
или расширенного фильтра.
Фильтрация данных с использованием автофильтра.
Для выполнения этой операции нужно:
1) установить курсор внутри таблицы;
2) ввести команду меню ДАННЫЕÞФильтрÞАвтофильтр;
3) щелчком мыши по кнопке со стрелкой раскрыть список столбца, по которому будет производиться выборка;
4) указать требуемые значения или выбрать строку "условие"
и задать критерии выборки в диалоговом окне Пользовательский автофильтр.
Условия для отбора записей в определенном столбце могут состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ.
Каждая часть условия может включать:
· значение, которое может выбираться из списка или содержать шаблонные символы подстановки. В качестве символов подстановки используются звездочка * - для указания произвольного количества символов или вопросительный знак? - для замены одного символа;
· оператор отношения (сравнения). При задании критериев выборки могут использоваться следующие операторы сравнения:
Равно <> Не равно
< Меньше < = Меньше или равно
> Больше > = Больше или равно
Для восстановления
всех строк исходной таблицы нужно щелкнуть мышью по кнопке со стрелкой (синего цвета) и в раскрывшемся списке выбрать строку "все"
или выполнить команду ДАННЫЕÞФильтрсÞОтобразить все.
Для отмены
режима фильтрации нужно установить курсор внутри таблицы и снова ввести команду меню ДАННЫЕÞФильтр
ÞАвтофильтр
(убрать переключатель).
Фильтрация данных с использованием расширенного фильтра.
Расширенный фильтр позволяет формировать множественные критерии
выборки и осуществлять более сложную фильтрацию данных электронной таблицы с заданием набора условий отбора по нескольким столбцам.
Для фильтрации записей списка расширенный фильтр обеспечивает использование двух типов критериев:
· критериев сравнения;
· вычисляемых критериев.
Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню ДАННЫЕÞФильтрÞРасширенный фильтр.
Важной особенностью этого режима является то, что до выполнения самой команды фильтрации необходимо сформировать специальную область для задания условий фильтрации данных -диапазон условий отбора
(интервал критериев).
Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Обычно для создания диапазона условий вначале копируют в отдельное место (на другом или на том же самом рабочем листе - как правило, выше исходной таблицы) строку с заголовками столбцов, затем в расположенные ниже строки вводят критерии выборки по отдельным столбцам.
Между значениями условий отбора и таблицей должна находиться, как минимум, одна пустая строка.
Если критерии отбора (условия фильтрации) вводятся в одной строке для разных столбцов, то они считаются связанными условием "И". Если критерии отбора записываются в разных строках, то они считаются связанными условием "ИЛИ".
После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду ДАННЫЕÞФильтрÞРасширенный фильтр
и в диалоговом окне Расширенный фильтр
указывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазона условий.
Записи можно фильтровать на месте
или одновременно с выполнением фильтрации копировать
в указанную область на текущем рабочем листе.
Чтобы скопировать отфильтрованные строки в другую область листа, следует установить переключатель Скопировать результаты в другое место,
перейти в поле Поместить результат в диапазон
и указать верхнюю левую ячейку области вставки отобранных данных.
Что делать, если из большого объема информации вам нужно найти нужные данные, относящийся к определенному человеку или дате? А затем, к примеру, скопировать на отдельный лист? Проще всего сделать автофильтр в Excel. Особенность работы фильтра — скрыть не удовлетворяющий вашим требованиям объем информации и оставить только запрашиваемые фильтром данные. Выводимые фильтром строки можно форматировать, распечатывать, редактировать и т.д.
Так же вы когда-нибудь применяли расширенный фильтр? Довольно удобный инструмент!
Итак, как сделать автофильтр в Excel (отобрать только нужные ячейки)?
Выделить нужные ячейки с конкретным значением или по цвету, или по условию можно двумя простыми способами:
1. Пройдя по пути на ленте “Данные” — “Сортировка и Фильтр” — “Автофильтр” (см. картинку вначале статьи
)
2. Нажатием правой кнопки мыши после выделения таблицы, в открывшемся меню фильтр — “Фильтр”
Основные условия использования автофильтра:
- Возможность работы
только с целостным списком. Т.е. если списка два, то фильтр применить не получится.
- Вы можете отфильтровать по двум столбцам. Но сначала необходимо отфильтровать один, затем второй.
- Фильтровать данные можно так же по цвету или по содержанию какой-то части фразы.
- С числовыми значениями можно делать очень широкий спектр задач
Расширенный фильтр. Как сделать фильтр по условию в ячейке
Расширенный фильтр применяется для более сложных условий отбора. Воспользуйтесь им, если
— нужно задать большое число условий сразу
— нужно вывести получивший список в отдельное место.
Суть расширенного фильтра в том, что в соседней таблице (т2) вы указываете все условия фильтрации (т1) и выводите их в новую таблицу т3.
Прежде чем работать с расширенным автофильтром, вам необходимо будет подготовить таблицу. Т.е. заголовки в имеющейся таблице перенести в отдельное место и в отдельном столб
Для того, чтобы его найти откройте вкладку «Данные», раздел «Сортировка и Фильтр, нажмите кнопку «Дополнительно», в открывшемся окне заполните исходный диапазон (если нужно).
Диапазон условий нужно тоже заполнить (т2). Если необходимо скопировать в другое место, выделите соответствующую галочку. Вторая таблица — это как раз таблица 2 — таблица условий, третья — то что получиться.
Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.
Для этой цели предназначено два инструмента: автофильтр и расширенный фильтр. Они не удаляют, а скрывают данные, не подходящие по условию. Автофильтр выполняет простейшие операции. У расширенного фильтра гораздо больше возможностей.
Автофильтр и расширенный фильтр в Excel
Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.
Если отформатировать диапазон данных как таблицу или объявить списком, то автоматический фильтр будет добавлен сразу.
Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:
Сразу видим результат:
Особенности работы инструмента:
- Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
- Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
- Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.
У расширенного фильтра гораздо больше возможностей:
- Можно задать столько условий для фильтрации, сколько нужно.
- Критерии выбора данных – на виду.
- С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.
Как сделать расширенный фильтр в Excel
Готовый пример – как использовать расширенный фильтр в Excel:
В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».
Как пользоваться расширенным фильтром в Excel
Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.
Заполняем меню расширенного фильтра:
Получаем таблицу с отобранными по заданному критерию строками:
Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.
Заполняем параметры фильтрации. Нажимаем ОК.
Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.
Применим инструмент «Расширенный фильтр»:
Данный инструмент умеет работать с формулами, что дает возможность пользователю решать практически любые задачи при отборе значений из массивов.
Основные правила:
- Результат формулы – это критерий отбора.
- Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
- Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
- Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.
Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.
Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).
В таблице остались только те строки, где значения в столбце «Количество» выше среднего.
Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».
Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.
КРАТКАЯ СПРАВКА
Общие сведения
Фильтрация данных
в списке - это выбор
данных по заданному
критерию (условию).
Осуществляется
эта операция
с помощью команды
Данные, Фильтр.
Имеются две разновидности
этой команды,
задаваемые параметрами:
Автофильтр и
Расширенный
фильтр.
Фильтрация
данных может
осуществляться
с помощью специальной
формы, которая
вызывается командой
Данные, Форма.
Автофильтрация
Команда
Данные,
Фильтр, Автофильтр
для каждого столбца
строит список
значений, и который
используется
для задания условий
фильтрации (рис.
3.36). В каждом столбце
появляется кнопка
списка, нажав
которую можно
ознакомиться
со списком возможных
критериев выбора.
Рис. 3.36.
Список
с автофильтромПо отдельному
столбцу в списке
критериев отбора
предусматриваются
следующие варианты:
- все
-
выбираются
все записи без
ограничений;
- первые
10
- данный пункт
позволяет во
вновь появляющемся
диалоговом окне
«Наложение условия
по списку» (рис.3.37)
выбрать определенное
количество наибольших
или наименьших
элементов списка,
которые необходимо
отобразить;
- значения
-
будут выбраны
только те записи,
которые в данном
столбце содержат
указанное значение;
Рис.
3.37. Диалоговое
окно «Наложение
условия по списку»
- условие
-
выбираются
записи по формируемому
пользователем
условию в диалоговом
окне «Пользовательский
фильтр» (рис.3.3 8).
Рис.
3.38. Диалоговое
окно «Пользовательский
фильтр»Условие для отбора
записей по конкретным
значениям в определенном
столбце может
состоять из двух
самостоятельных
частей, соединенных
логической связкой
И/ИЛИ.Каждая часть
условия включает:
- оператор отношения:
= (равно), <> (не равно),
> (больше), >= (больше
или равно), < (меньше),
<= (меньше или равно),
начинается с,
содержит и т.п.;
- значение, которое
может выбираться
из списка или
содержать шаблонные
символы *,?.
Пример.
Для
Кода
предмета
можно
сформировать
условия:
>=п*
- отобрать
все записи, которые
содержат код
предмета, начинающийся
с буквы
п;
>= п1
И <=п2
- отобрать
все записи, которые
содержат коды
предметов
п1
и п2;
<>п1 -
отобрать
все записи, которые
не содержат кода
предмета
п1.
Можно задать
условия отбора
для нескольких
столбцов независимо
друг от друга,
.фильтрация записей
выполняется
по всем условиям
одновременно.
Все записи, не
прошедшие через
фильтр, будут
скрыты. Отфильтрованные
записи можно
выделить и скопировать
в другое место,
удалить. Отмена
результата фильтрации
и возврат к исходному
состоянию списка
производятся
повторным вводом
команды Данные,
Автофильтр.
Расширенный
фильтр
Команда
Данные,
Фильтр, Расширенный
фильтр
обеспечивает
использование
двух типов критериев
для фильтрации
записей списка:
- критерий сравнения;
- вычисляемый
критерий.
Обычно критерий
фильтрации формируется
в нескольких
столбцах, и тогда
его называют
множественным
критерием.
Важной особенностью
этого режима
является необходимостьформирования
заранее, до выполнения
самой команды
фильтрации, специального
блока (области)
для задания сложных
поисковых условий,
называемых
областью
критерия (диапазоном
условия).
Технология использования
расширенного
фильтра состоит
из двух этапов:этап 1 - формирование
области критериев
поиска;этап 2 - фильтрация
записей списка.Э т а п 1. Формирование
диапазона условий
для расширенного
фильтра. Область
критериев поиска
содержит строку
имен столбцов
и произвольное
число строк для
задания поисковых
условий. Рекомендуется
скопировать
первую строку
с именами полей
из области списка
в область, где
будет формироваться
критерий отбора
записей (на тот
же или другой
лист, в другую
рабочую книгу).
Далее ненужные
имена столбцов
из диапазона
условий можно
удалить.
Критерий сравнения
формируется
при соблюдении
следующих требований:
- состав столбцов
области критериев
определяется
столбцами, по
которым задаются
условия фильтрации
записей;
- имена столбцов
области критериев
должны точно
совпадать с именами
столбцов исходного
списка;
- ниже имен столбцов
располагаются
критерии сравнения
типа:
- точного значения;
- значения, формируемого
с помощью операторов
отношения;
- шаблона значения,
включающего
символы * и (или)
?.
Правила формирования
множественного
критерия:
1. Если критерии
(условия) указываются
в каждом столбце
на одной строке,
то они считаются
связанными условием
И.2. Если условия
записаны в нескольких
строках, то они
считаются связанными
условием ИЛИ.
Пример.
Условие
выбора записей
о сдаче экзаменов
студентами группы
133
по предмету
п1 на оценки 4 или
5 можно записать
несколькими
способами: 1-й
способ. Множественный
критерий сравнения
- все условия находятся
в одной строке,
связка И. Номер
группы, код предмета
заданы как точные
значения, оценка-
оператор сравнения
со значением
константы.
3-й способ. Множественный
критерий сравнения
- условия (точные
значения полей)
записаны в двух
строках, связка
ИЛИ.
|
Номер группы
|
Код предмета
|
Оценка
|
|
133
|
п1
|
4
|
|
133
|
п1
|
5
|
Вычисляемый
критерий
представляет
собой формулу,
записанную в
строке области»
условий, которая
возвращает логическое
значение ИСТИНА
или ЛОЖЬ. Формула
строится с использованием:
адресов ячеек,
встроенных функций,
констант различных
типов (числе текст,
дата, логическая
константа), операторов
отношения.
Внимание!
Имя
столбца,
содержащего
формулу вычисляемого
критерия, должно
отличаться от
имени столбца
в списке
.
Пример.
Выбрать
записи о сдаче
экзаменов студентами
группы 133 с оценкой
ниже общего среднего
балла или записи
с оценкой 5:
|
Номер группы
|
Оценка1
|
|
133
|
=ИЛИ(G2<=CP3HAЧ($G$2:$G$I7);G2=5)
|
В области критериев
присутствуют
столбцы с заголовками:
Номер группы.Оценка1.
Последовательность
действий по созданию
данного вычисляемого
критерия:
- присвоить отличное
от имен полей
списка новое
имя столбцу, куда
будет введен
вычисляемый
критерий;
- установить
курсор в ячейку
ввода;
- вызвать Мастер
функций -
команда
Вставка, Функция,
выбор категории
-Логические
и выбор функции
- ИЛИ;
- ввод параметров
функции ИЛИ:
Логическое!: G2<=CP3HAЧ($G$2:$G$17)(при вводе формулы
использовать
курсорный указатель
на ячейки таблицы,
вызов встроенной
функции
СРЗНАЧ,
указание на абсолютные
ссылки с помощью
клавиши
)Логическое2: G2=5После завершения
ввода вычисляемого
критерия в ячейке
должна появиться
логическая константа
ИСТИНА или ЛОЖЬ
- результат применения
сформированного
вычисляемого
критерия по отношению
к первой записи
списка; формулу
критерия можно
просмотреть
лишь в строке
формул. Этот же
критерий можно
было записать
по-другому:
Э т а п 2 .Фильтрация
записей расширенным
фильтром. После
подготовки области
критерия курсор
устанавливается
в список и выполняется
команда Данные,
Фильтр, Расширенный
фильтр (рис. 3.39).
Фильтровать
записи списка
можно на месте
либо копировать
в указанную область
на текущем рабочем
листе. Для копии
на другой лист
или книгу следует
установить курсор
по месту копии,
а затем выполнять
команду фильтрации,
указывая соответствующие
исходный диапазон
и диапазон условий.Исходный диапазон
и диапазон условий
включают все
строки, в том числе
и строку наименования
столбцов. Если
предполагается
копирование
результата в
другое место,
указывается
левая верхняя
ячейка области.
Переключатель
Только уникальные
записи
позволяет
исключить дублирование
записей.
Рис. 3.39.
Диалоговое
окно «Расширенный
фильтр»Для сложных по
логике обработки
запросов фильтрация
записей списка
может выполняться
постепенно, то
есть копируется
первый результат
фильтрации, к
нему применяется
следующий вариант
фильтрации и
т.д.Для снятия действия
условий фильтрации
выполняется
команда Данные,
Фильтр, Отобразить
все.
Фильтрация с
помощью формы
данных
ППП Excel 97 позволяет
работать с отдельными
записями списка
с помощью экранной
формы (рис.3,40). Основные
операции обработки
записей списка:
последовательный
просмотр записей,
поиск или фильтрация
записей по критериям
сравнения, создание
новых и удаление
существующих
записей списка,При установке
курсора в область
списка и выполнении
команды Данные,
Форма на экран
выводится форма,
в составе которой
имена полей - названия
столбцов списка.Для просмотра
записей используется
полоса прокрутки
либо кнопки <Назад>
или <Далее>, выводится
индикатор номера
записи. При просмотре
записей возможно
их редактирование.
Поля, не содержащие
формул, доступны
для редактирования,
вычисляемые
или защищенные
поля не редактируются.
Корректировку
текущей записи
с помощью кнопки
<Вернуть> можно
отменить.Для создания
новой записи
нажимаете) кнопка
<Добавить>, выполняется
заполнение пустых
полей экранной
формы; для переходи
между полями
формы используются
курсор мыши либо
клавиша <ТаЬ>.
При повторном
нажатии кнопки
<Добавить> сформированная
запись добавляется
в конец списка.
Для удаления
текущей записи
нажимается кнопка
<Удалить>, Удаленные
записи не могут
быть восстановлены,
при их удалении
происходит сдвиг
всех остальных
записей списка.С помощью экранной
формы задаются
критерии сравнения.
Для этого нажимаете»
кнопка <Критерии>,
форма очищается
для т
да условий
поиска в полях
формы с помои
кнопки <0чистить>,
а название кнопки
заменяется
на название <Правка>.
По ввода критериев
сравнения нажимаются
кнопки <Назад>
или <Далее> для
просмотра отфильтрованных
записей в нужном
направлении.
1И просмотре можно
удалять и корректировать
отфильтрованные
записи списка.
Для возврата
к форме нажимается
кнопка <Правка>,
для выхода из
формы - кнопка
<3акрыть>.
Рис.3.40.
Экранная
форма для работы
со списком записей
ЗАДАНИЕ 1
Выберите данные
из списка по критерию
отбора, используя
Автофильтр.
- Проведите подготовительную
работу - переименуйте
новый лист на
Автофильтр
(см рис, 3.35).
-
Выберите
из списка данные,
используя критерий:
- для преподавателя
- а1 выбрать сведения
о сдаче экзамена
на положительную
оценку,
- вид занятий
- л.
- Выберите из
списка данные,
используя критерий:
для группы 133 получить
сведения о сдаче
экзамена по
предмету п1 на
оценки 3 и 4.
- Отмените результат
автофильтрации.
- Выполните несколько
самостоятельных
заданий, задавая
произвольные
критерии отбора
записей.
ТЕХНОЛОГИЯ РАБОТЫ
- Проведите подготовительную
работу:
- переименуйте
ЛистЗ -Автофильтр
- выделите
блок ячеек исходного
списка, начиная
от имен полей
и вниз до конца
записей таблицы,
и скопируйте
их на лист Автофильтр.
- Выберите из
списка данные,
используя критерий
- для преподавателя
- а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.
Проведите подготовительную
работу:
- переименуйте
Лист4
- Расширенный
фильтр
- Расширенный
фильтр.
Этап 1. Формирование
диапазона условий
по типу Критерий
сравнения
2.
Скопируйте все
имена полей списка
(см.
рис. 3.35) в другую
область на том
же листе например
установив курсор
в ячейку J1.
Это
область, где будут
формироваться
условия отбора
записей. Например,
блок ячеек J1:O1
- имена полей области
критерия, J2:О5 -
область значений
критерия.3.
Сформируйте
в области условий
отбора Критерий
сравнения -
о
сдаче экзаменов
студентами группы
133 по предметуп1
на оценки 4 или
5. Для этого в первую
строку после
имен полей введите:
- в столбец
Номер группы
-
точное значение
- 133;
- в столбец
Код предмета
-
точное значения
- п1;
- в столбец
Оценка
-условие->3
Этап 2. Фильтрация
записей расширенным
фильтром.
4.
Произведите
фильтрацию записей
на том же листе:
- выполните
команду Данные,
Фильтр, Расширенный
фильтр;
- в диалоговом
окне «Расширенный
фильтр» с помощью
мыши задайте
параметры, например;
Скопировать
результат в другое
место: установите
флажокИсходный диапазон:
A1:G17 Диапазон условия:
J1:O5Поместить результат
в диапазон: J6
5.
Придумайте собственные
критерии отбора
по типу Критерий
сравнения
и
проведите фильтрацию
на том же листе,
соблюдая технологию
п.З и п.4.Этап 1. Формирование
диапазона условий
по типу Вычисляемый
критерий.
6.
Сформируйте
в области условий
отбора Вычисляемый
критерий
- для
каждого преподавателя
выберите сведения
о сдаче студентами
экзамена на оценку
выше средней,
вид занятий -
л;
результат
отбора поместите
на новый рабочий
лист. Для этого:
- в столбец
Вид занятия
введите точное
значения - букву
л;
- переименуйте
в области критерия
столбец Оценка,
например, на имя
Оценка 2:
- в столбец
Оценка
1
введитевычисляемый
критерий, например,
вида
=G2>CP3HAЧ($G$2:$G$17)где G2 - адрес первой
клетки с оценкой
в исходном списке, $G$2: $G$I7 - блок ячеек
с оценками, СРЗНАЧ -
функция вычисления
среднего значения.Этап 2. Фильтрация
записей расширенным
фильтром.
7.
Произведите
фильтрацию записей
на новом листе;
- установите
курсор в область
списка (базы данных);
- выполните
команду Данные,
Фильтр, Расширенный
фильтр;
- в диалоговом
окне «Расширенный
фильтр» с помощью
мыши задайте
параметры, например:
Скопировать
результат в другое
место: установите
флажокИсходный диапазон:
A1:G17Диапазон условия:
Л:05Поместить результат
в диапазон: перейдите
на новый лист
и щелкните мышью
в любой ячейке
8.
Придумайте собственные
критерии отбора
по типу Вычисляемый
критерий
и поместите
результаты фильтрации
на выбранном
ранее листе, соблюдая
технологию п.6
и п.7.
ЗАДАНИЕ 3
Используя Форму,
выберите данные
из списка.
1.
Проведите подготовительную
работу - переименуйте
новый лист на
Форма
и скопируйте
на него исходную
базу данных (см.
рис.3,35).2.
Просмотрите
записи списка
с помощью формы
данных, добавьте
новые.3.
Сформируйтеусловие
отбора с помощью
формы данных
- для преподавателя
выбрать сведения
о сдаче студентами
экзамена на положительную
оценку, вид занятий
- л.4. Просмотрите
отобранные записи.5. Сформируйте
собственные
условия отбора
записей и просмотрите
их,
ТЕХНОЛОГИЯ РАБОТЫ
1. Проведите
подготовительную
работу:
- переименуйте
Лист5
- Форма;
- выделите
блок ячеек исходного
списка, начиная
от имен полей
и вниз до конца
записей таблицы,
и скопируйте
их на лист Форма;
- установите
курсор в область
списка и выполните
команду Данные,
Форма,
2. Просмотрите
записи списка
и внесите необходимые
изменения с помощью
кнопки <Назад>
и <Далее>. С помощью
кнопки <Добавить>
добавьте новые
записи.3. Сформируйте
условие отбора
- для преподавателя
- а1 выбрать сведения
о сдаче студентами
экзамена на положительную
оценку, вид занятий
- л. Для этого:
- нажмите
кнопку <Критерии>,
название которой
поменяется
на <Правка>;
- в пустых
строках имен
полей списка
введите критерии:
- в строку Таб
№ препод.
введите
а1
- в строку Вид
занятия
введите
л;
- в строку Оценка
введите условие
> 2
4. Просмотрите
отобранные записи,
нажимая на кнопку
<Назад> или <Далее>.5. Аналогично
сформируйте
собственные
условия отбора
записей и просмотрите
их.
Фильтр - это быстрый и легкий способ поиска подмножества данных и работы с ними в списке. В отфильтрованном списке отображаются только строки, отвечающие условиям. В отличие от сортировки, фильтр не меняет порядок записей в списке. При фильтрации временно скрываются строки, которые не требуется отображать
.
Строки, отобранные при фильтрации, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.
При фильтрации выбираются только необходимые данные, а остальные данные скрываются. Таким образом, отображается только то, что вы хотите увидеть, и это можно сделать одним щелчком.
При фильтрации данные никак не изменяются. Как только фильтр удален, все данные появляются снова в том же виде, в каком они были до применения фильтра.
В Excel доступны две команды для фильтрации списков:
- Автофильтр
, включая фильтр по выделенному, для простых условий отбора.
- Расширенный фильтр
для более сложных условий отбора.
Автофильтр
Для включения Автофильтра
нужно выделить любую ячейку в таблице, затем на вкладке Данные
в группе Сортировка
и
фильтр
нажать большую кнопку
:
После этого в шапке таблицы справа от каждого заголовка столбца появится кнопка со стрелкой вниз :
Щелчок по стрелке открывает меню со списком для соответствующего столбца. В списке содержатся все элементы столбца в алфавитном или числовом порядке (в зависимости от типа данных), так что можно быстро найти требуемый элемент:
Если нам нужен фильтр только для одного столбца, то можно не выводить кнопки со стрелкой для остальных столбцов. Для этого перед нажатием кнопки
выделяем несколько ячеек нужного столбца вместе с заголовком.
Фильтрация по точному значению
Включаем Автофильтр
, щелкаем по кнопке со стрелкой и выбираем из раскрывшегося списка какое-нибудь значение. Для того, чтобы быстро выделить все элементы столбца или снять выделение со всех элементов, щелкните по пункту (Выделить все)
:
При этом все строки, в поле которых не содержится выбранное значение, скрываются.
При выполнении лабораторной работы, выделяем результат фильтрации, копируем на другое место листа и подписываем.
Для выключения Автофильтра
нужно еще раз нажать кнопку
.
Для отмены действия фильтра, не выходя из режима фильтрации, щелкаем по кнопке и выбираем из раскрывшегося списка пункт (Выделить все)
. При этом появляются скрытые фильтром строки таблицы.
Признаки фильтрации данных
Фильтры скрывают данные. Именно для этого они и предназначены. Однако, если о фильтрации данных не известно, может возникнуть впечатление, что некоторые данные пропущены. Вы могли, например, открыть чей-нибудь отфильтрованный лист или даже забыть, что сами ранее применили фильтр. Поэтому когда на листе есть фильтры, можно обнаружить различные визуальные указатели и сообщения.
(находится слева внизу окна). Исходное состояние:
Сразу после фильтрации данных итог применения фильтра отображаются в левом нижнем углу строки состояния. Например, “Найдено записей: 2 из 11
”:
Номера строк
. По прерывистым номерам строк можно сказать, что некоторые строки скрыты, а изменившийся цвет номеров видимых строк указывает на то, что выделенные строки являются результатом отбора фильтра.
Вид стрелок
. Изменение стрелки автофильтра в отфильтрованном столбце на указывает на то, что данный столбец отфильтрован.
”
“” - это еще один универсальный фильтр, который можно применять к столбцам с числами или датами.
“” - это очень условное название. На самом деле возможности этого фильтра гораздо шире. С помощью этого фильтра можно находить или первые элементы или последние элементы (наименьшие или наибольшие числа либо даты). И, вопреки названию фильтра, получаемые результаты не ограничиваются первыми 10 элементами или последними 10 элементами. Число отображаемых элементов можно выбирать от 1 до 500.
” позволяет также отбирать данные по проценту от общего числа строк в столбце. Если в столбце содержится 100 чисел и требуется просмотреть наибольшие пятнадцать, то выбираем 15 процентов.
Фильтр можно использовать для нахождения продуктов с наибольшими или наименьшими ценами, для определения списка сотрудников, нанятых последними по времени, или для просмотра списка студентов с наилучшими или наихудшими отметками.
Чтобы применить фильтр “” к столбцу данных (только числа или даты!!!
), щелкаем в столбце стрелку и выбираем пункт Числовые фильтры
далее
:
После этого откроется диалоговое окно Наложение
условия
по
списку
:
В диалоговом окне выбираем число
(строк или процентов), наибольших
или наименьших
, элементов списка
или % от количества элементов
.
Создание собственных настраиваемых фильтров
Например, нам нужно вывести только строки с должностями, начинающимися с буквы ‘Д
’. Для этого щелкаем по стрелке автофильтра в первом столбце и выбираем Текстовые фильтры
, затем пункт начинается с…
:
При этом появится диалоговое окно
(какой бы пункт справа вы бы ни выбирали, все равно появится одно и то же диалоговое окно.):
В поле Должность
выбираем – начинается с
, справа вводим д
:
В окне
есть подсказка:
Знак вопроса “?
” обозначает один любой знак.
Знак “*
” обозначает последовательность любых знаков.
