1 курс 2 сесия / Экономическая информатика / Теория / Ex_4

Лекция 4. Использование фильтров. Автофильтры. Расширенные фильтры. Промежуточные итоги.

Теоретические сведения.

Поиск данных и сортировка списков являются наиболее частыми задачами при работе с Microsoft Excel. Для решения этих задач можно использовать фильтры, промежуточные итоги и сводные таблицы.

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

Для выполнения фильтрации достаточно установить курсор в любую ячейку списка и выполнить пункт меню ДАННЫЕ - ФИЛЬТР - АВТОФИЛЬТР. Excel преобразует строки заголовка в поля списков, в которых вы можете задавать нужные критерии для поиска данных. Если активной является строка (Все), то фильтрация отлючена.

Если выбрать какую-то строку, то будут отображены только строки, содержащие выбранное значение.

Чтобы отфильтровать список по двум значениям в одном столбце или применить отличные от равенства операторы сравнения, выберите пункт (Условие…).

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

Чтобы убрать фильтрацию выполните повторно пункт ДАННЫЕ - ФИЛЬТР - АВТОФИЛЬТР.

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

Расширенный фильтр. Для того чтобы использовать расширенный фильтр, вы должны определить область критериев (условий отбора), которая должна отражать структуру ваших данных. Для этого достаточно скопировать строку заголовка вашего списка в ту часть рабочего листа, которая не содержит данных для отбора. Число строк под скопированным заголовком не играет роли и определяется только тем, какое количество критериев отбора вы задаете. После выполнения команды меню ДАННЫЕ – ФИЛЬТР – РАСШИРЕННЫЙ ФИЛЬТР откроется окно диалога, в котором нужно задать исходный диапазон, котором находятся данные и область в которой заданы условия отбора.

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

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

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

Пример.

1. Создайте таблицу, приведенную ниже.

№/№	Процессор	Т/ч	ОП	Винчестер	Цена	Кол-во	Сумма
1	Pentium III	850	64	30	4 402 грн	8	?
2	Celeron	733	128	40	2 663 грн	10
3	Pentium III	850	128	30	4 432 грн	7
4	Pentium IV	1300	128	30	5 520 грн	13
5	Pentium IV	1300	256	30	5 919 грн	7
6	Celeron	733	128	30	2 623 грн	12
7	Pentium IV	1300	128	40	5 559 грн	9
8	Pentium IV	1300	256	40	5 959 грн	3
9	Celeron	850	128	20	2 675 грн	5
10	Pentium III	933	128	30	4 704 грн	4
11	Celeron	633	64	30	2 532 грн	12
12	Celeron	850	64	30	2 715 грн	21
13	Pentium III	750	256	20	4 278 грн	3
14	Celeron	633	128	40	2 602 грн	5
15	Celeron	850	64	40	2 755 грн	15
	Итого					?	?

2. Выдайте информацию о компьютерах с процессорами Celeron.

Установите курсор в любую клетку таблицы и выполните команду ДАННЫЕ - ФИЛЬТР - АВТОФИЛЬТР.

1. Покажите все записи., выбрав в фильтре (Все).

2. Выдайте информацию о компьютерах с:

ОП>64, 700<Т/ч<1000, Винчестером>20

Условия можно задать поочерёдно в выведенном фильтре (Условие…) в виде, приведенном ниже:

3. Скопируйте полученную информацию на отдельный Лист.

4. Отмените Автофильтр: ДАННЫЕ - ФИЛЬТР – Автофильтр.

5. Выдайте информацию о компьютерах с:

ОП=128 и Винчестер=30

или ОП=256 и Винчестер=40

или ОП=256 и Сумма > 40000

Это можно выполнить с помощью Расширенного Фильтра. Для этого

создайте Диапазон условий - скопируйте «шапку» таблицы на несколько строк ниже (или выше) основной таблицы и укажите условия Фильтрации:

№/№	Процессор	Т/ч	ОП	Винчестер	Цена	Кол-во	Сумма
			128	30
			256	40
			64				>40000

или ОП=64 и Сумма >=40000грн

Выполните команду ДАННЫЕ - ФИЛЬТР - РАСШИРЕННЫЙ ФИЛЬТР.

Укажите клетки таблицы

Укажите клетки диапазона условий

8. Скопируйте исходную таблицу на лист, переименованный в Итоги, на этом листе отсортируйте её по возрастанию следующих признаков:

Курсор  в любую клетку таблицы и выполните ДАННЫЕ - СОРТИРОВКА

1 - типу процессора

2 - ОП одной командой ДАННЫЕ - СОРТИРОВКА

3 - объёму Винчестера

9. На листе Итоги выдайте таблицу с Промежуточными итогами - операции Кол-во, Сумма по типам Процессоров.

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

ДАННЫЕ – ИТОГИ – ПРОМЕЖУТОЧНЫЕ ИТОГИ.

9. Дополните Итоги Средней ценой каждой модели компьютеров.

Выполняется аналогично п. 9 с указанием Операция: Среднее,

добавить итоги по: Цена и снятием флажка Заменить текущие итоги

В результате таблица на Листе Итоги будет иметь вид:

Уровни структуризации