Примеры условий отбора расширенного фильтра
Лабораторная работа № 6
Работа с элементами таблиц в Excel. Построение диаграмм и фильтрация данных
Диаграммы
Графическое представление данных выполняется в несколько этапов.
Вначале выделяется область, данные из которой следует представить графически. Рекомендуется выделять также и названия столбцов, т.к. они будут использоваться как комментарии к различным цветовым решениям в легенде. В линейке Вставка в меню Диаграммы выбирается опция, соответствующая виду требуемой диаграммы.
Открывается окно, показывающее первый вариант диаграммы. Для его изменения используется контекстная линейка Работа с диаграммами или контекстное меню в области диаграммы:
· для изменения названия диаграммы выделяют сформированное название и через его контекстное меню изменяют текст;
· для изменения подписей под горизонтальной осью во вкладке Конструктор в меню Данные выбирают опцию Выбрать данные и в окне Подписи горизонтальной оси выбирают опцию Изменить, с помощью которой выделяют в таблице требуемый диапазон;
· для формирования подписей данных, которые выводят числовые значения каждой точки диаграммы, выделяют саму диаграмму и в контекстном меню выбирают опцию Подписи данных;
· для формирования названий горизонтальной и вертикальной осей в меню Макет выбирают опцию Названия осей.
Полученный график можно перемещать и менять его размеры, оперируя с ним как с обычным рисунком. С помощью контекстного меню можно готовый график корректировать, внося в него практически любые изменения.
Задание 1
1. Открыть таблицу из предыдущей работы.
2. Построить 2 диаграммы:
· у первой диаграммы по вертикальной оси помещены средний, минимальный и максимальный баллы студентов, по горизонтальной оси – фамилии студентов;
· у второй диаграммы по вертикальной оси помещены стипендии, по горизонтальной оси – фамилии студентов.
3. При построении использовать по своему усмотрению такие типы диаграмм, которые наиболее удачны для выражения смысла отображаемых данных.
4. Сформировать название диаграммы и обеих осей, легенду, подписи данных.
5. Поместить графики на отдельных листах.
6. Сохранить данные в файле.
7. Показать результат преподавателю.
Фильтры
Использование фильтров позволяет выделять из крупных списков данных их подмножества на основании ввода одного или нескольких условий.
Под списком в Excel понимают таблицу, первая строка которой содержит заголовки каждого столбца, а все остальные строки однородны.
Для ввода фильтра выбирают в линейке Данные опцию Фильтр, при этом указатель курсора должен находиться в фильтруемой таблице. В ячейках с названиями граф таблицы появляются специальные маркеры, с помощью которых можно выполнять автофильтрацию, щелкнув по требуемому маркеру и выбрав нужные данные из появившегося списка. В этом же меню можно отказаться от фильтра через опцию Выделить все.
Отказаться от фильтрации можно, «отжав» опцию Фильтр в линейке Данные.
Можно также фильтровать данные через расширенный фильтр, позволяющий строить сложные условия поиска и фильтрации, копировать записи в другое место или отбирать данные на основе вычисленного значения.
Фильтры могут быть использованы только для одного списка на листе, причем столбцы должны быть поименованы.
Фильтрация списка с помощью расширенного фильтра
Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки. Последовательность шагов следующая:
Скопировать из списка заголовки столбцов, по которым выставляются условия фильтрации, и вставить скопированные заголовки столбцов в свободной области на листе (не рекомендуется использовать области, соответствующие строкам таблицы).
Ввести в строки под заголовками условий требуемые критерии отбора (см далее).
Установить курсор на фильтруемой таблице и в линейке Данные выбрать опцию Дополнительно в меню Сортировка и фильтр.
В окне Расширенный фильтр в поле Исходный диапазон указывается диапазон ячеек, в которых размещается исходная таблица, в поле Диапазон условий указывается диапазон ячеек, в которых записаны условия фильтрации. Чтобы показать результат фильтрации, скрыв ненужные строки, установить переключатель Обработка в положение Фильтровать список на месте (рекомендуется). Чтобы скопировать отфильтрованные строки в другую область листа, установить переключатель Обработка в положение Скопировать результаты в другое место, перейти в поле Поместить результат в диапазон, а затем указать левую верхнюю ячейку области вставки. Нажать кнопку ОК.
Для снятия расширенного фильтра использовать опцию Очистить меню Сортировка и фильтр линейки Данные.
Примеры условий отбора расширенного фильтра
В условия отбора расширенного фильтра может входить несколько условий:
а) накладываемых на один столбец. Чтобы задать для отдельного столбца три или более условий отбора, они вводятся в ячейки, расположенные в смежных строках. Например, для следующего диапазона условий будут отобраны строки, содержащие либо «Белов», «Батурин» или «Сушкин» в столбце «Студент».
| Студент |
| Белов |
| Батурин |
| Сушкин |
б) накладываемых одновременно на несколько столбцов. Для этого условия вводятся в ячейки, расположенные в одной строке диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах группы 2010 ИС, сдавших экзамен по физике на 4 или 5 и по культурологии – на 5:
| Группа | Физика | Культурология |
| 2010 ИС | >3 |
в) накладываемых на один из столбцов. Для этого условия вводятся в ячейки, расположенные в разных строках диапазона условий. Например, для следующего диапазона условий будут отобраны строки с информацией о студентах, сдавших либо физику, либо культурологию на 3:
| Физика | Культурология |
г) сложное условие отбора, накладываемое на несколько столбцов. Для этого вводят его составные части в отдельные строки диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах групп 2010 ИС и 2010 ВТ1, сдавших экзамен по физике на 4 и 5:
| Группа | Физика |
| 2010 ИС | >3 |
| 2010 ВТ1 | >3 |
Виды условий отбора
При настройке автофильтра или вводе условий отбора в диапазоне условий расширенного фильтра можно использовать перечисленные ниже элементы условий:
Последовательности символов. При использовании текстовой константы в качестве условия отбора будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности символов. Например, при вводе условия «Пет» будут отобраны строки с ячейками, содержащими фамилии Петров, Петеров и Петухов.
Знаки подстановки. Используются для того, чтобы отобрать строки с ячейками, содержащими последовательность символов, в некоторых позициях которой могут стоять произвольные символы. Знак подстановки эквивалентен одному символу или произвольной последовательности символов. Например, знак ? заменяет любой символ в той же позиции, что и сам знак (так, д?м задает поиск строк «дом« и «дым»); знак * задает любую последовательность символов в той же позиции, что и сам знак (так, *ино задает поиск строк «Люблино» и «Выхино»)
Значения сравнения. Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать оператор сравнения. Условие отбора с оператором сравнения следует ввести в ячейку ниже заголовка столбца в диапазоне условий. Например, чтобы отобрать строки, имеющие значения ячеек большие или равные 1000, введите условие отбора >=1000 ниже заголовка «Количество». Отметим, что строчные и прописные буквы при фильтрации данных не различаются.
Задание 2
1. Использовать список студентов из предыдущей работы .
2. Отфильтровать список, выбрав только отличников по всем дисциплинам. Скопировать результат во второй лист.
3. Отфильтровать список, выбрав тех, кто сдал сессию, т.е. получил положительные оценки. Скопировать результат в третий лист.
4. Отфильтровать список, выбрав тех, кто получил только оценки 3 или 4. Скопировать результат в четвертый лист.
5. С помощью расширенного фильтра установить список студентов, получивших хотя бы одну двойку. Скопировать результат в пятый лист.
6. Найти в списке результаты сессии для студентов, фамилии которых начинаются на букву В. Скопировать результат в шестой лист.
7. Все листы поименовать по смыслу.
8. Показать результаты преподавателю.