Значение2

Значение1

Условие

Первые 10

Все

Фильтрация данных в Excel

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

При фильтрации данных возможны следующие ситуации:

1. вывод записей с одним фиксированным значением одного поля.

В этом случае, находясь в любой ячейке базы данных, входим в меню Данные / Фильтр / Автофильтр. В заголовках всех полей появляется знак ▼. Следует выбрать желаемое поле и щелкнуть левую клавишу мыши на этом значке. При этом в столбце раскрывается окно вида:

…….

Здесь значение1, значение2 и т. д. – это значения элементов поля, уже автоматически отсортированные в порядке возрастания. Из них следует выбрать требуемое и щелкнуть на нем мышью (рис. 1.4). При этом на экране появятся уже отфильтрованные записи.

Так, выбрав с помощью автофильтра в поле ПРОВЕРИЛ значение “Самарин А.Л.”, получим на экране записи:

ВЫПОЛНИЛ	ПРОВЕРИЛ
Петров А.Р.	Самарин А.Л.
Антипенко Л.Г.	Самарин А.Л.
Беляков Н.Н.	Самарин А.Л.
Ягодин Ю.П.	Самарин А.Л.

При этом номера этих записей (1, 5, 9, 10) выделяются другим цветом. Внизу экрана в строке состояния сообщается количество найденных записей в базе. Для нашего примера сообщение имеет вид " Найдено записей 4 из 10 ".

Аналогичную операцию можно проделать с несколькими полями. Например, имеется база данных "Поликлиника" с указанием фамилии врача-терапевта, даты посещения пациентом врача и т. д. Так, для отбора пациентов, посетивших врача Терентьева И.Л. 25-го октября 2002 года, следует в поле "ВРАЧ" выбрать значение “Терентьев И.Л.”; в результате будут выбраны только пациенты, посетившие указанного врача. Затем в поле "ДАТА ПОСЕЩЕНИЯ" выбрать значение “25.10.02”. В итоге будут выбраны только пациенты, посетившие указанного врача в один указанный день. Можно проделать эти действия и в обратном порядке – сначала найти всех пациентов, посетивших поликлинику 25.10.02, а затем выбрать из них только тех, кто посетил врача Терентьева И.Л.

Если результаты сортировки не нужны для дальнейшей работы, то следует войти в меню Данные / Фильтр / Показать все. При этом восстановится исходный вид базы.

2. вывод записей, значение поля в которых удовлетворяет некоторому логическому отношению (<, >, =, ¹, £, ³).

Аналогично переходим в меню Данные / Фильтр / Автофильтр, в раскрывшемся окошечке выбираем пункт Условие. В левом верхнем окошечке с помощью кнопок ▼ и ▲ выбираем нужное нам отношение, в правом верхнем окошечке определяем числовое значение. Например, для того, чтобы выбрать всех студентов, сдавших зачет позже 25.01.03, следует с помощью автофильтра в поле “ДАТА СДАЧИ ЗАЧЕТА” в верхнем левом окошечке установить отношение "больше", а в правом верхнем – значение 25.01.03. После определения условия следует щелкнуть кнопку ОК (рис. 1.5).

Рис.1.4.

ВНИМАНИЕ! Для нахождения вхождения текста в символьную строку в современных версиях Excel удобно использовать отношение вида =*текст*. Здесь знак * означает любую последовательность символов. Например, необходимо найти всех студентов, посещающих факультатив по этике (имеется в виду, что студент может посещать несколько факультативов). Для этого в поле "ФАКУЛЬТАТИВ" следует с помощью автофильтра выбрать условие =*Этика*. Аналогичную операцию можно выполнить с помощью условия СОДЕРЖИТ, содержащегося в списке возможных отношений в окне фильтра. Требуемое отношение выбирается с помощью кнопки прокрутки ▼.

Для текстовых данных используются также отношения НАЧИНАЕТСЯ С и аналогичные ему. Например, для поиска лиц, живущих на улице Луговой, следует в окне фильтра установить отношение НАЧИНАЕТСЯ С и значение Луговая.

3. вывод записей, значение одного поля в которых соответствует двум условиям, связанным операциями И или ИЛИ (например, если значение некоторого числового поля лежит в каком-либо интервале). В этом случае поступают следующим образом: вначале производят действия, аналогичные пункту 2, затем выбирают значок ● для И или ИЛИ; в левом нижнем окошечке выбирают отношение, в правом нижнем – значение. После этого следует щелкнуть кнопку ОК, и на экране появятся нужные записи.

Пример. Вывести на печать всех новорожденных, вес которых лежит в интервале от 2500 до 3500 г. Окно фильтрования должно иметь вид, представленный на рис. 1.6.

Рис. 1.5

Рис.1.6.

Пример. Пусть имеется поле ДАТА РОЖДЕНИЯ. Требуется выбрать лиц, родившихся в период 1970-1980 г. Окно фильтрования представлено на рис. 1.7.

Рис.1.7.

Для вывода лиц, проживающих на улицах Васенко или Серова, окно фильтрования представлено на рис. 1.8.

Рис. 1.8.

4. Фильтрация по нескольким полям. Для фильтрации по нескольким полям необходимо использовать расширенный или пользовательский фильтр. Для этого сначала в свободную часть рабочей области скопировать в одну строку имена полей, по которым нужно произвести фильтрацию. После этого в следующие строки необходимо ввести условия для значений полей, причем если условия для разных полей записаны в одной строке, то будет выполняться операция И, а если в разных – то операция ИЛИ. Так, например, чтобы выбрать новорожденных, вес которых меньше 2500 г, а рост меньше 45 см, в свободной части листа записываем:

Вес	Рост
<2500	<45

Для выбора новорожденных, вес которых меньше 2500 г, или рост которых меньше 45 см, записываем:

Вес	Рост
<2500
	<45

После этого переходим в пункт меню Данные / Фильтр / Расширенный Фильтр.

В появившемся окне следует указать данные таким образом: в качестве исходного диапазона ввести адреса ячеек в виде $A$1:$X$n. Здесь $X$n – адрес правой нижней ячейки базы данных. Если до перехода в меню курсор находился в любой из ячеек базы данных, то исходный диапазон в окне устанавливается автоматически. В диапазоне условий следует указать диапазон ячеек, начиная с ячейки первого поля условий, и заканчивая последней ячейкой последнего поля. Если в окне выбран пункт Скопироватьданные в другое место, то в окне Поместить результат в диапазон следует указать левую верхнюю ячейку расположения выбранных записей.

Пример. Пусть имеется база данных «Успеваемость», содержащая поля «ФИО», «Физика», «Математика», «Экономика» (три последних поля содержат оценки по указанным предметам). Чтобы выбрать всех отличников, формируем диапазон условий в виде

Математика	Физика	Экономика

А для поиска студентов, имеющих оценку 5 хотя бы по одному предмету, следует записать диапазон условий в виде

Математика	Физика	Экономика

Пример. Пусть имеется база данных вида:

	А	В	С	D
	ФАМИЛИЯ	ВОЗРАСТ	АДРЕС	СОЦИАЛЬНОЕ ПОЛОЖЕНИЕ
	Иванов М.Л.		Советская,112-16	Учащийся
	Ушаков А.О.		Гагарина,34-89	Рабочий
	Велехова С.М.		Пушкина,12	Рабочая
	Крохалев В.П.		Рабочая,80	Служащий
	Семенов Я.Н.		Гагарина,45-1	Служащий
	Сомов А.А.		Заречная,13	Пенсионер
	ВОЗРАСТ	АДРЕС	СОЦИАЛЬНОЕ ПОЛОЖЕНИЕ
	<16
		Гагарина*
			Пенсионер

Требуется найти всех лиц, моложе 16 лет, или живущих на улице Гагарина, или являющихся пенсионерами.

В качестве исходного диапазона указываем $A$1:$D$7, в качестве диапазона условий – $A$10:$C$13. Если установить значок ● в пункте “Фильтровать список на месте”, то таблица принимает вид, приведенный ниже. Здесь обычным шрифтом выделяется исходная база данных, жирным шрифтом – формируемый диапазон условий. Следует отметить, что в пункте “АДРЕС” в диапазоне условий после названия улицы указывается знак *. Это означает, что в список включаются жители всех домов, расположенных на указанной улице.

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

Сопутствующие заболевания	Осложнения
*
	*

5. Расширенный фильтр применяется также, если значения одного поля связаны двумя и более операциями ИЛИ. В этом случае в свободную часть листа копируется имя поля, а под ним указывается по одному значению в строке.

Пример. Пусть в базе данных содержатся фамилии и места работы жителей некоторого микрорайона. Требуется выбрать жителей, работающих на ОАО “Лисма”, “Биохимик”, “Химмаш” или “Текстильторг”. В этом случае диапазон условий, находящийся в ячейках $c$15:$c$19 принимает вид:

	А	В	С
	ФАМИЛИЯ	МЕСТО РАБОТЫ
	Иванов М.Л.	Лисма
	Ушаков А.О.	Биохимик
	Велехова С.М.	Текстильторг
	Крохалев В.П.	Лисма
	Семенов Я.Н.	Столовая № 6
	Сомов А.А.	ПТУ № 14
	Бакулева К.П.	Поликлиника № 5
	Демидович В.А.	АТП
	Кораблев В.Д.	Химмаш
	Колосов Г.Д.	Биохимик
	Дерябина Т.О.	Роспечать
			МЕСТО РАБОТЫ
			Лисма
			Биохимик
			Текстильторг
			Химмаш

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

	А	В	С
	ФАМИЛИЯ	МЕСТО РАБОТЫ
	Иванов М.Л.	Лисма
	Ушаков А.О.	Биохимик
	Велехова С.М.	Текстильторг
	Крохалев В.П.	Лисма
	Кораблев В.Д.	Химмаш
	Колосов Г.Д.	Биохимик
			МЕСТО РАБОТЫ
			Лисма
			Биохимик
			Текстильторг
			Химмаш

Пример. Пусть в базе данных требуется найти всех пациентов, переболевших краснухой, скарлатиной или корью. Диапазон условий принимает вид:

ПЕРЕНЕСЕННЫЕ ЗАБОЛЕВАНИЯ

*скарлатина*

*корь*

*краснуха*

Пример. Пусть в базе данных “Поликлиника” необходимо найти пациентов, посетивших врача Терентьева И.Л. позже 25.10.02 или врача Монахову И.Н. ранее 15.10.02.

Диапазон условий принимает вид:

ВРАЧ	ДАТА ПОСЕЩЕНИЯ
Терентьев И.Л.	>25.10.02
Монахова И.Н.	<15.10.02

Здесь врач и дата связаны операцией И и соответственно находятся в одной строке, и связаны со второй парой данных операцией ИЛИ, соответственно вторая пара условий находится в следующей строке.

В Excel 2007 расширенный фильтр реализуется следующим образом: перейти в пункт Сортировка и фильтр, в подпункт Дополнительно (см. рис.1.9), далее действия аналогичны рассмотренным.

Рис.1.9.

Дата добавления: 2014-11-20