Электронные таблицы

К середине 90-х гг. ХХ в. большую популярность приобрел па­кет Excel. Электронные таблицы Excel позволяют решать очень многие задачи обработки данных, сформированных в таблицы. В Excel преду­смотрен автоматический ввод данных из SQL-серверов и различных СУБД.

В пакете Excel предусмотрены различные средства для сохране­ния логической целостности информации — автоматическая замена номеров листов и ячеек при внесении изменений в книгу, автоматиче­ское копирование формул со сдвигом номеров ячеек в пределах столб­цов со сходными вычислениями и многое другое. Продемонстрируем возможности пакета Excel для обработки данных экологического мо­ниторинга.

Обработка данных анализа проб воды. На рис. 6.5 представлен

фрагмент одного из листов рабочей книги, ориентированной на обра­ботку данных анализа проб воды на разных стадиях ее очистки.

Рис. 6.5. Общий вид экрана при работе с электронными таблицами Excel

В одной из нижних линеек перечислена часть листов и показа­но, что активным является лист, название которого начинается симво­лами «Таб2». Весь рабочий лист разбит на ячейки, координаты кото­рых определяются заголовками — названием столбца (латинские бук­вы A, B, C) и номером строки (цифры 1, 2, 3). Любую ячейку в преде­лах листа можно сделать активной, для этого надо указать на нее кур­сором и щелкнуть левой кнопкой мышки.

Рабочий лист на рис. 6.5 состоит из двух частей — диаграммы (2) и таблицы (3). В верхнем окне размещается таблица, в пределах которой проводятся вычисления. В одной из верхних линеек (4) при­ведена формула, по которой рассчитывается результат, отображенный в активной ячейке F12 = F11 - B12. Это означает, что число в ячейке F12 определяется результатом вычитания числа в ячейке B12 из числа в ячейке F11. В нижнем окне рабочего листа размещается диаграмма, более полно показанная на рис. 6.6.

Рис. 6.6. Результаты обработки данных анализа проб воды на разных стадиях ее очистки: ряд 1 -- исходная вода; ряд 2 -- фильтрат, обработанный аммиа­ком и хлором; ряд 3 -- исходная вода, прошедшая озонирование; ряд 4 -­

фильтрат озонированной воды после реагентной обработки и вторичного хлорирования

грамма, и далее в окне «Мастер диаграмм» надо установить тип диа­граммы «График». Свойства элементов диаграммы могут быть легко изменены. Для этого надо щелкнуть правой кнопкой по выбранному элементу и вызвать в выпадающем меню специальную форму.

Линейный регрессионный анализ. Линейный регрессионный

анализ обычно включает четыре стандартных этапа:

- оценка по заданной выборке {xi yi}, i = 1...k коэффициентов b1 и b0 линейных моделей Y = b0 + b1X или Y = b1X;

- оценка остаточной дисперсии, характеризующей качество ап­проксимации исходных данных;

- оценка значимости коэффициентов b1 и b0;

- проверка адекватности модели экспериментальным данным.

В Excel линейный регрессионный анализ осуществляется с по­мощью функции ЛИНЕЙН. Эта функция вызывается стандартным способом (Вставка -- Функция). Пример формы, заполненной для за­пуска функции ЛИНЕЙН, приведен на рис.

Рис. 6.7. Входные данные для функции ЛИНЕЙН

Массивы Х и Y, использованные для расчетов, представлены на рис. 6.7. В форме ЛИНЕЙН заполняются четыре поля:

- в поле Изв_знач_у вводится ссылка на массив значений Y — ячейки B4: B13, которые видны слева от формы;

- в поле Изв_знач_х вводится ссылка на массив значений X — ячейки A4: A13, которые видны в первом столбце таблицы Excel;

- в поле Константа вводится логическое значение ИСТИНА или ЛОЖЬ. При значении ИСТИНА анализ проводится для модели Y = b + + mX (или в более привычном написании: Y = b0 + b1X), а при значе­нии ЛОЖЬ — для модели Y = mX;

- в поле Стат вводится логическое значение ИСТИНА, если на­до выводить результаты статистического анализа, и значение ЛОЖЬ (или ничего не вводится), если эти результаты не нужны.

Результаты расчетов с помощью функции ЛИНЕЙН представ­лены на рис. 6.8. Для того чтобы вывести в результате расчетов мас­сив значений, перед вызовом функции ЛИНЕЙН необходимо выде­лить ячейки, в которые необходимо записать результаты (выделены цветом). После заполнения формы (рис. 6.8) необходимо одновремен­но нажать клавиши CTRL + SHIFT + ENTER. При этом формула (верхнее правое поле) для вызова функции ЛИНЕЙН окаймляется фи­гурными скобками {}, а в отведенное место таблицы выводятся ре-

зультаты.

Рис. 6.8. Результаты расчетов с помощью функции ЛИНЕЙН

Расшифровка результатов расчета функции ЛИНЕЙН представ­лена в табл. 6.1.

Таблица 6.1

Результаты расчета функции ЛИНЕЙН

Значения bl и b0 присвоены переменным а и b (Вставка — Имя — Присвоить). Переменные а и b использованы для получения расчет­ных значений Урас. Формула для расчета 1-го значения представлена на рис. 6.9 (С3 = а + b*A3). Вызов переменных: (Вставка — Имя — Вставить). Остальные Урас значения получены копированием 1-го.

График полученной зависимости представлен на рис. 6.9.

Рис. 6.9. Расчетные значения У и график полученной зависимости

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

таблиц Excel в геоэкологических исследованиях можно найти в спе­

циализированных пособиях.

6.3.2.