Методичка (Андреева) информатика для ИФБиБТ

2.3. Фильтр

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

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

Демонстрационные примеры этого раздела используют данные пользовательской таблицы «Биогеоклиматические параметры» из прилагаемого файла.

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

10)или пихта (значение поля N1 равно 12).

По р я д о к в ы п о л н е н и я з а д а н и я

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

Фильтр1.

2.Курсор установить в области списка. Затем на линейке управления, во вкладке Данные, в группе Сортировка и фильтр, выбрать команду

Фильтр (рис. 2. 11).

31

		Рис. 2. 11. Кнопка вызова функции рабочего листа Фильтр
3.		Около	каждого		имени
столбца появится раскрывающийся
список, в нем перечислены все
значения поля. Галочкой отметить
выбранные значения.
Например,			для		отбора
сектора		Западная	Сибирь		следует
отметить		значение		«3»		в
раскрывающемся			списке			поля

	Сектор (рис. 2. 12).
		Рис. 2. 12. Условие для отбора строк списка
	4. Затем выбрать зону
	тайги, значение «3» в поле
	Зона (рис. 2. 14).

Рис. 2. 13. Выбор нужного значения из раскрывающегося списка

32

5. Для отбора строк, соответствующих кедру

(N1=10) и пихте (N1=12),

воспользоваться

Настраиваемым фильтром

(Рис. 2. 14).

Рис. 2. 14. Вызов Настраиваемого фильтра

6. В диалоговом окне

Пользовательский автофильтр

определить критерии отбора операций по значениям поля N1

(рис. 2. 15):

равно – «10»;

 или; равно – «12».

Рис. 2. 15. Условия отбора строк списка

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

значком

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

Рис. 2. 16. Результат выполнения задания

33

2.4. Сводные таблицы

Для расчета статистических характеристик по данным списка используются сводные таблицы. Источником данных для сводных таблиц могут служить данные рабочего листа MS Excel или данные внешних источников, например информация базы данных MS Access. Результат расчета – таблица, у которой в области строк и столбцов находятся поля списка, которые служат классификаторами, а в области данных – поля, по которым рассчитываются значения статистических характеристик: сумма, среднее, максимум, минимум, счет количества значений и другие статистические показатели. Структура сводной таблицы предусматривает задание условий отбора строк списка. Допускается группировка строк и/или столбцов сводной таблицы, при этом статистические характеристики пересчитываются автоматически.

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

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

Демонстрационные примеры этого раздела используют данные

пользовательской таблицы «Биогеоклиматические параметры»	из
прилагаемого файла.

Задание 1. По данным списка для каждого сочетания «сектор– зона» определить:

1)минимальное и максимальное значение индекса континентальности

(поле К конт.);

2)среднее значение количества осадков за год (поле R год.);

3)минимальное и максимальное значение суммы температур активного периода вегетации (поле GDD10).

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

П о р я д о к в ы п о л н е н и я з а д а н и я

34

1. Создать копию

листа

Биогеоклиматические

параметры. Новому

листу

присвоить

имя

Сводн 1.

Функция Сводные таблицы

всегда

работает

с

полным

списком, даже если он

включает

скрытые

данные.

Для удобства работы скрыть

строки списка с 26 по 350

Рис. 2. 17. Строки списка с номерами 26-350 скрыты

(рис. 2. 17).

2. Курсор установить в области

списка и выделить любую ячейку

внутри списка. Вставить сводную

таблицу, для этого на линейке

управления

в

группе

Таблицы

выбрать команду Сводная таблица

(рис. 2. 18).

Рис. 2. 18. Вызов функции Сводная

таблица

3. В

диалоговом

окне

Создание

сводной

таблицы

автоматически

выделяется

диапазон

списка.

Определить

местоположение

отчета

сводной таблицы, указав курсором

левую верхнюю ячейку диапазона,

где будет расположен отчет,

определить значения параметров (

рис. 2. 19):

Рис. 2. 19. Определение

Таблица

или

диапазон

–

исходных данных и размещение

'Сводн 1'!$A$19:$AT$353;

сводной таблицы

 На существующий лист;

Диапазон – 'Сводн 1'!$A$357

Нажать кнопку ОК.

35

3. В диалоговом окне параметров

Список полей сводной таблицы

определить структуру сводной таблицы. 3.1. Метку столбца К конт.

«отбуксировать» в область Значения. Мастер сводных таблиц предлагает итоговую функцию Сумма по полю

(рис. 2. 20).

Рис. 2. 20. Суммирование в области значений сводной таблицы

3.2. «Кликом», нажав на правую кнопку мыши, вызвать мини-меню поля значений сводной таблицы. Выбрать опцию Параметры полей значений (рис. 2. 21).

Рис. 2. 21. Параметры поля сводной таблицы

36

3.3. В диалоговом окне

Параметры поля значений

определить значения параметров (рис. 2. 22):

Пользовательское имя –

Минимум по полю K конт;

Операция – Минимум.

Нажать кнопку Числовой формат, в открывшемся диалоговом окне

ФоматЯчеек в поле Числовые форматы выбрать значение Числовой формат,

число десятичных знаков –2.

Закрыть окно нажав кнопку

ОК.

Закрыть диалоговое окно

Рис. 2. 22. Панель определения формата поля сводной таблицы

Параметры поля значений,

нажав кнопку ОК.

3.4. Метку столбца К конт. «отбуксировать» в область Значения еще раз, определить итоговую операцию для него Максимум.

Метку столбца R год. «отбуксировать» в область Значения, определить итоговую операцию для него Максимум.

Метку столбца GDD10 «отбуксировать» в область Значения, определить итоговую операцию для него Минимум.

Метку столбца GDD10 «отбуксировать» в область Значения, определить итоговую операцию для него Максимум (рис. 2. 23).

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

десятичных знаков – 2.

Рис. 2. 23. Итоговые функции в области значений

3.5. Поле Зона перенести в область Фильтр отчета, поле Сектор – в

область Название строк (рис. 2. 24).

37

Рис. 2. 24. Области Фильтр отчета, Названия строк и Значения в сводной таблице

4. Задание выполнено. На рабочем пространстве MS Excel отражается построенная сводная таблица и ее структура (рис. 2. 25) .

Рис. 2. 25. Сводная таблица построена

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

Для поля Зона в области страниц сводной таблицы выбрать значения: 3-

тайга, 4-подтайга, 7-черневые леса.

38

Рис. 2. 26. Постраничный просмотр сводной таблицы

6. Раскрывающийся список в поле Сектор служит фильтром отбора строк списка по заданным значениям поля. Для сектора Западной Сибири выбрать значение, равное 3

(рис. 2. 28).

Рис. 2. 27. Фильтр в сводной таблице

7.Задание выполнено (рис. 2. 28).

Рис. 2. 28. Результат выполнения задания

Задание 2. По данным списка определить число реперных точек в каждом секторе и в каждой зоне.

П о р я д о к в ы п о л н е н и я з а д а н и я

1. Создать копию рабочего листа Биогеоклиматические параметры.

39

Курсор установить в области списка, выделить любую ячейку внутри списка. Вставить сводную таблицу, для этого на линейке управления в группе Таблицы выбрать команду Сводная таблица. Разместить сводную таблицу на существующем листе, указав адрес её левой верхней ячейки.

2. В диалоговом окне параметров Список полей сводной таблицы определить структуру сводной таблицы:

2.1.Разместить поля: Зона – в области Названия строк, Сектор – в

области Названия столбцов.

2.2.Для определения итоговых значений метку столбца Название перенести в область Значения, выбрать операцию – Количество, формат поля – числовой, число десятичных знаков – 0, пользовательское имя поля –

Количество метеостанций (рис. 2. 29).

Рис. 2. 29. Сводная таблица Количество метеостанций и ее структура

3. Задание выполнено.

2.5. Группировка данных в сводной таблице

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

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

Если в результате проведения группировки высвечивается сообщение об ошибке: «Нельзя объединить в группу данное выделение!», значит, множество значений поля включает неопределенное значение, например пустое. Неопределенное значение поля не позволяет автоматически определить параметры группировки.

40