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

Рис. 1. 31. Задание выполнено

2. Накопление и анализ данных в электронных таблицах

2.1. Информационная модель климата и растительности зональных комплексов типов леса

Структура табличной базы данных определяется именами полей таблицы и типами их значений. В MS Excel пользовательская таблица представляется в виде списка – особого способа организации данных на рабочем листе. Данные списка хранятся в непрерывном диапазоне ячеек, эта область рабочего листа не содержит пробельных (пустых) строк или столбцов. В первой строке списка – имена полей базы данных, каждая строка списка соответствует одной единице данных. Вертикальные колонки таблицы – поля базы данных. Каждая колонка таблицы хранит данные одного типа (дата, число или текст). Каждая строка списка соответствует одной записи базы данных. Функции работы со списками реализуют основные операции ведения базы данных: поиск релевантных данных, просмотр, редактирование и удаление записей, добавление новых единиц данных.

Задания этого раздела выполняются в пользовательской таблице, которая представляет собой фрагмент информационной базы данных по климату и растительности зональных и высотно-поясных комплексов типов леса «БИОМ». Разработка базы данных «БИОМ» ведётся в Институте леса СО РАН (г. Красноярск) с 1990х гг [1]. Цель создания такой базы данных состоит в выявлении современных связей между растительностью и климатом на широком географическом фоне. Для лесных экосистем Сибири, как и других регионов Евразии, актуальной задачей является определение ключевых параметров климата (диапазона значений тепловлагообеспеченности, дефицита увлажнения и т.д.) и таких значений этих параметров, при которых растительность теряет устойчивость и переходит из одного зонального класса, или биома, в другой, например, лес в степь, либо лес в тундру, либо одна господствующая зональная лесная формация в другую. Уровни иерархии таксонов классификации в информационной базе отражают различные степени генерализации данных, наивысшую – при

21

рассмотрении зон и наименьшую – для растительных сообществ или ценопопуляций.

Структура «БИОМ» включает характеристики гетерогенных структур покрова, слагающих конкретные типы лесных массивов на территории Сибири: состав зональных формаций и групп типов леса, структуры почвенного покрова и уровень продуктивности лесов. Для кодирования зональных категорий растительного покрова (или иначе – экосистем с наземной жизнью) используются единицы: зона, подзона, округ, высотнопоясной комплекс (ВПК) типов биогеоценозов. На более низких уровнях – традиционные единицы: формации, группы типов леса, типы леса. Такой принцип совмещения эколого-фитоценотической классификации на нижней ступени и эколого-географической – на верхней ступени иерархии позволяет включать в анализ всю лесоустроительную информацию по территориям (природным лесорастительным округам и провинциям), обеспечивая массовость данных, достаточно достоверных для суждения о составах лесообразователей, лесотипологической структуре и продуктивности лесов (классах бонитета) и даже о фоновых эколого-ценотических группах видов нижних ярусов (ЭЦГ).

Объектом предметной области (области учета) базы банных «БИОМ» служит территориальная единица (экорегион) с однородной зональной растительностью, с характеризующими ее показателями климата, фиксированными в многолетних данных реперной точки – метеостанции.

Для демонстрационного примера этого раздела сформирована таблица «Биогеоклиматические параметры», которая включает 334 реперные точки (метеостанции) из базы банных «БИОМ». Для каждой реперной точки указаны ее географические координаты, высота над уровнем моря, сектор, зона, показатели прироста хвойных (куб.м/год), четыре доминирующих вида лесообразователей, выставленные по убыванию степени доминирования. Кроме того, внесены усредненные по данным пятидесятилетних наблюдений показатели климата: среднемесячные значения температур и осадков. По этим данным с помощью формул MS Excel рассчитываются: среднегодовые суммы осадков в мм/год, среднегодовая температура, разница между средними температурами января и июля, индекс континентальности Конрада, сумма средних месячных температур, сумма температур выше 10 градусов (активного периода вегетации), показатель отношения потенциальной эвапотранспирации (относительное увлажнение) и коэффициент увлажнения по Мезенцеву.

Имена полей таблицы «Биогеоклиматические параметры», их адреса на рабочем листе MS Excel, тип хранимых данных и назначение приведены в схеме заполнения рабочего листа (таблица 2.1).

22

Таблица 2. 1

Таблица «Биогеоклиматические параметры». Схема заполнения рабочего листа

	Ад-
	рес	Содержи-	Тип данных	Пояснения
	ячей-	мое
	ки
		Дата
	A19	занесения	Дата	Дата появления строки в
		в базу		таблице
		данных
	B19	Долгота	Число, число десятичных знаков равно 2.	Географические
	C19	Широта	Число, число десятичных знаков равно 2.	координаты.
	D19	Aбсолют	Число, число десятичных знаков равно 2.	Абсолютная высота в
				метрах.
	E19	Название	Текст	Условное название
				реперной точки.
	F19	Сектор	Число, число десятичных знаков равно 0.	Номер сектора
				Номер лесорастительной
	G19	Зона	Число, число десятичных знаков равно 0.	зоны.
	H19	R год.	=Y20+Z20+AA20+AB20+AC20+AD20+	Среднегодовые суммы
				осадков в мм/год.
			AE20+AF20+AG20+AH20+AI20+AJ20
	I19	T год.	=(M20+N20+O20+P20+Q20+R20+S20+	Среднегодовая
			T20+U20+V20+W20+X20)/12	температура.
	J19	амплит	=ABS(M20)+S20	Разница между средними
		уда		температурами января и
				Индекс
	K19	K конт.	=1,7*J20/SIN((C20+10)*3,14/180)-14	континентальности
				Конрада.
	L19	T био.	=СУММЕСЛИ(M20:X20;">0")/12	Биотемпература
	M19	T1	Число, число десятичных знаков равно 2.	Среднемесячная
				температура января
	N19	T2	Число, число десятичных знаков равно 2.	Среднемесячная
				температура февраля
	O19	T3	Число, число десятичных знаков равно 2.	Среднемесячная
				температура марта
	P19	T4	Число, число десятичных знаков равно 2.	Среднемесячная
				температура апреля
	Q19	T5	Число, число десятичных знаков равно 2.	Среднемесячная
				температура мая
	R19	T6	Число, число десятичных знаков равно 2.	Среднемесячная
				температура июня
	S19	T7	Число, число десятичных знаков равно 2.	Среднемесячная
				температура июля
			23

	T19	T8	Число, число десятичных знаков равно 2.	Среднемесячная
				температура августа
	U19	T9	Число, число десятичных знаков равно 2.	Среднемесячная
				температура сентября
	V19	T10	Число, число десятичных знаков равно 2.	Среднемесячная
				температура октября
	W19	T11	Число, число десятичных знаков равно 2.	Среднемесячная
				температура ноября
	X19	T12	Число, число десятичных знаков равно 2.	Среднемесячная
				температура декабря
				Среднемесячное
	Y19	R1	Число, число десятичных знаков равно 2.	количество осадков
				января
				Среднемесячное
	Z19	R2	Число, число десятичных знаков равно 2.	количество осадков
				февраля
	AA19	R3	Число, число десятичных знаков равно 2.	Среднемесячное
				количество осадков марта
				Среднемесячное
	AB19	R4	Число, число десятичных знаков равно 2.	количество осадков
				апреля
	AC19	R5	Число, число десятичных знаков равно 2.	Среднемесячное
				количество осадков мая
	AD19	R6	Число, число десятичных знаков равно 2.	Среднемесячное
				количество осадков июня
	AE19	R7	Число, число десятичных знаков равно 2.	Среднемесячное
				количество осадков июля
				Среднемесячное
	AF19	R8	Число, число десятичных знаков равно 2.	количество осадков
				августа
				Среднемесячное
	AG19	R9	Число, число десятичных знаков равно 2.	количество осадков
				сентября
				Среднемесячное
	AH19	R10	Число, число десятичных знаков равно 2.	количество осадков
				октября
				Среднемесячное
	AI19	R11	Число, число десятичных знаков равно 2.	количество осадков
				ноября
	AJ19	R12	Число, число десятичных знаков равно 2.	Среднемесячное
				количество осадков
	AK19	СТМ	=СУММЕСЛИ(M20:X20;">0")	сумма средних месячных
			24

				сумма температур выше
	AL19	GDD10	=30,926*AK94-356,1	10 градусов (активного
				периода вегетации)
				показатель отношения
				потенциальной
	AM19	ППЭ	=L20*58,93/H20	эвапотранспирации
				(относительное
				увлажнение)
	AN19	К_увлаж	=H20/(0,2*AL20+306)	коэффициент увлажнения
				по Мезенцеву
	AO19	Древесина	Число, число десятичных знаков равно 2.	запас древесины
	AP19	Прирост	Число, число десятичных знаков равно 2.	прирост хвойных пород
		хвойных
	AQ19	N1	Число, число десятичных знаков равно 0.	Четыре доминирующих
				вида лесообразователей,
	AR19	N2	Число, число десятичных знаков равно 0.
				выставленные по
	AS19	N3	Число, число десятичных знаков равно 0.	убыванию степени
				доминирования
				(N1, N2, N3, N4).
	AT19	N4	Число, число десятичных знаков равно 0.

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

Числовые обозначения секторов: 3 – Западная Сибирь, 4 – Средняя Сибирь, 5 – Восточная Сибирь, 6 – Южная Сибирь.

Лесорастительные зоны и их обозначения: Тундра (1), Редколесье (2), Тайга (3), Подтайга (4), лесостепь (5), Степь (6), Черневые (7).

Вбазе данных рассматриваются 16 видов лесообразователей:

1	– нет лесообразователей (0);
2	– лиственница сибирская (Лс) – Larix sibirica Ledeb.;
3	– лиственница Гмелина (Лг) – Larix gmelinii (Rupr.) Rupr.;
4	– лиственница Каяндера (Лк) – Larix cajanderi Mayr;
5	– лиственница Чекановского (Лч) – Larix czekanowskii Szaf. ;
6	– сосна обыкновенная (С) – Pinus sylvestris L. ;
7	– береза повислая (Бп) – Betula pendula Roth ;
8	– береза (Б) Betula spp;

9–– ерник (Ер) –Betula spp, Salix spp;

10– кедр сибирский (К) – Pinus sibirica Du Tour;

11– кедровый стланик (Кст) – Pinus pumila (Pall). Rgl. ;

12– пихта сибирская (П) – Abies sibirica Ledeb.;

13– ель сибирская (Е) Picea obovata Ledeb.;

14– осина (Ос) – Populus tremula L.;

25

15 – сосна интразональная (Синт) – Pinus sylvestris L; 16 – степи зональные (Ст); 17степи азональные (Ст-аз).

Для каждой реперной точки выявлены от одного до четырех видов преобладающих лесообразователей, они указываются в полях таблицы N1, N2, N3, N4 в порядке убывания степени доминирования в этой области.

2.2. Сортировка списка, промежуточные итоги

Сортировка данных в списке – упорядочивание строк списка по возрастанию или убыванию значений в выбранных полях списка, составляющих ключ на сортировку. Ключ на сортировку может быть простым, т. е. состоять из одного поля, или составным (многомерным) – включать несколько полей. В случае сортировки по многомерному ключу строки списка упорядочиваются сначала по первому полю ключа, затем по второму и т. д.

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

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

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

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

Задание 1. Упорядочить данные о биогеоклиматических параметрах метеостанций по значениям полей – Сектор, Зона, К конт.:

1)расположить строки по возрастанию значений в столбце Сектор;

2)строки списка, у которых значения поля Сектор совпадают, расположить в порядке убывания значений в столбце Зона;

26

3) строки списка с совпадающими значениями полей Сектор и Зона должны располагаться в списке по убыванию значений поля К конт.

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

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

Сорт1.

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

области списка (выделить любую ячейку внутри списка). Затем перейти на вкладке

Данные в группу Сортировка и фильтр, выбрать команду

Сортировка (рис. 2. 1).

Рис. 2. 1. Вызов функции рабочего листа

Сортировка

3. В открывшемся диалоговом окне, добавляя уровни сортировки, определить параметры сортировки – поля сортировки и порядок следования значений (рис. 2. 2):

для столбца Сектор порядок следования значений в отсортированном списке – по возрастанию;

для второго поля сортировки Зона порядок следования значений – по убыванию;

для третьего поля сортировки К конт. порядок следования значений

– по убыванию.

Рис. 2. 2. Добавление уровней сортировки и определение параметров сортировки

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

27

Задание 2. С помощью функции рабочего листа Промежуточные итоги определить количество реперных точек в каждом секторе. Как они распределяются по зонам?

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

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

Биогеоклиматические параметры на новый лист с созданием копии.

Новому листу присвоить имя Итог1.

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

2.1.Курсор установить в области списка.

2.2.Выбрать команду Сортировка в группе команд Сортировка и

фильтр.

2.3.Определить параметры сортировки:

порядок сортировки значений в столбце Сектор – по возрастанию, порядок сортировки значений в столбце Зона – по возрастанию. Нажать кнопку ОК! (рис. 2. 3).

Рис. 2. 3. Параметры сортировки строк списка

3. Подвести итоги по первому полю сортировки, для этого:

3.1.Курсор установить в области списка.

3.2.Затем на линейке управления во вкладке Данные в группе Структура выбрать команду Промежуточные итоги.

3.3.В диалоговом окне определить значения параметров:

При каждом изменении в: Сектор;

Операция: Количество;

Добавить итоги по: Название.

Нажать кнопку ОК! (рис. 2. 4).

28

Рис. 2. 4. Панель параметров функции Промежуточные итоги

	4.	Определены
	общие	суммы	сделок на
	каждой	бирже	и	общая
	сумма	сделок	по	всему
	списку.	Для	просмотра
	результата работы нажать
	на кнопочку 2		в	левой
	части экрана (рис. 2. 5).
					Рис. 2. 5. Просмотр промежуточных итогов по полю Сектор

5. Подвести итоги по второму полю сортировки: количество реперных точек по каждой зоне. Для этого снова вызвать функцию Промежуточные итоги. Затем определить её параметры:

При каждом изменении в: Зона;

Операция: – Количество;

Добавить итоги по: – Название;

Заменить текущие итоги –

очистить поле параметра.

Нажать кнопку ОК (рис. 2. 6).

Рис. 2. 6. Параметры функции

Промежуточные итоги

6. В левой верхней части экрана появились кнопки 1, 2, 3 и 4. Они позволяют просмотреть разные уровни детализации итогов. В левой части экрана кнопки  и - определяют структуру просмотра результата работы функции Промежуточные итоги: скрывают и «распахивают» отдельные уровни списка (рис. 2. 7).

29

Рис. 2. 7. Результат выполнения функции Промежуточные итоги

7. Скрыть пустые ячейки листа.

Для этого выделить пустые столбцы рабочего листа, передвигая курсор мыши по адресам столбцов – по буквам (рис. 2. 8).

Рис. 2. 8. Выделение пустых столбцов

8. Затем установить курсор на одну из букв (адрес столбца) и выбрать опцию мини-меню Скрыть (рис. 2. 9).

Рис. 2. 9. Опция мини-меню Скрыть

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

Рис. 2. 10. Результат выполнения упражнения

30