- 1 -

Федеральное бюджетное государственное образовательное учреждение

высшего профессионального образования

«МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ЛЕСА»

Л.В. Стоноженко, А.Н. Югов, В.Н. Карминов, Н.Г.Иванов

Применение MS Excel и Statistica for Windows

для лесотаксационных вычислений

и обработки экспериментальных данных

методами математической статистики

Рекомендовано к изданию Редакционно-издательским советом университета

в качестве учебного пособия

для направления подготовки 250100 "Лесное дело"

(квалификация (степень) "бакалавр")

Москва

Издательство Московского государственного университета леса

2012

- 2 -

УДК 681.3.06 : 630.5

С 81

Разработано в соответствии с Федеральным государственным образо-

вательным стандартом ВПО 2011 г. для направления подготовки 250100

"Лесное дело" (квалификация (степень) "бакалавр").

Рецензенты: профессор кафедры лесоводства и подсочки леса,

кандидат сельскохозяйственных наук Ф.А. Никитин;

доцент кафедры информационных технологий в лесном сек-

торе, кандидат сельскохозяйственных наук А.С. Мухин

Работа подготовлена на кафедрах лесоустройства и охраны леса и почво-

ведения.

Стоноженко, Л. В.

С 81 Применение MS Excel и Statistica for Windows для лесотаксационных

вычислений и обработки экспериментальных данных методами ма-

тематической статистики : учеб. пособие / Л.В. Стоноженко, А.Н.

Югов, В.Н. Карминов. – М. : ФГБОУ ВПО МГУЛ, 2012. – ___ с.

Учебное пособие предназначено для аудиторной работы студентов, прежде всего, в ин-

терактивных формах (компьютерных симуляций, деловых игр, разбора конкретных си-

туаций). Пособие можно также использовать как самоучитель. В нем описаны возмож-

ности MS Excel на примере расчетов встречающихся в теории и практике лесного хо-

зяйства и лесной промышленности, так же приведены примеры расчетов статистиче-

ских показателей и решения оптимизационных задач. Описаны возможности Statistica

для Windows для различных видов статистического анализа.

УДК 681.3.06 : 630.5

© Л. В. Стоноженко, А. Н. Югов,

В. Н. Карминов, 2012

© ФГБОУ ВПО МГУЛ, 2012

- 3 -

Оглавление

Введение 5

1. ОБЩИЕ СВЕДЕНИЯ ОБ EXCEL 7

1.1. Математические вычисления в Excel 7

1.1.1. Общий вид (структура) 7

1.1.2. Перемещение по листу Excel и выделение ячеек 8

1.1.3. Ввод данных в ячейки Excel 10

1.1.4.Составные элементы формул 12

1.1.5. Операции и их приоритетность 13

1.1.6. Использование скобок для изменения порядка вычислений 14

1.1.7. Использование ссылок 15

1.1.8. Ошибки 17

1.1.9. Вычисления с использованием функций 17

1.2. Создание диаграмм в Excel 19

1.2.1. Мастер диаграмм 19

1.2.2. Порядок построения диаграммы 21

1.2.3. Редактирование созданных диаграмм 27

1.3. Дополнительные возможности Excel 29

1.3.1. Статистические функции 29

1.3.2. Решение оптимизационных задач 30

1.3.3. Сортировка списка 31

1.3.4. Сортировка с помощью панели инструментов 32

1.3.5. Фильтрация списка по заданному критерию 33

1.4. Введение в систему Statistica 37

1.4.1. Настройка системы Statistica 38

1.4.2. Элементарные понятия анализа данных 38

1.4.3. Простейшие описательные статистики 39

1.4.4. Шкалы измерений 44

1.4.5. Исследование связей между наблюдаемыми переменными

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

1.4.6. Основные шаги обработки данных в системе 46

1.4.7. Построение линейного регрессионного уравнения 47

1.4.8. Расчет основных описательных статистик 53

1.4.9. Определение статистической достоверности различий

средних двух выборок 57

- 4 -

2. ЗАДАЧИ ДЛЯ ПРОВЕДЕНИЯ ЗАНЯТИЙ В АКТИВНОЙ И

ИНТЕРАКТИВНОЙ ФОРМЕ 62

2.1. Работа с файлами и простейшие действия в MS Excel (на примере

расчета таксационных показателей насаждения) 62

2.2. Работа с Мастером диаграмм (на примере построения графика

высот и определения средней высоты насаждения) 65

2.3. Расчет количественной спелости насаждения 67

2.4. Расчет материальной оценки лесосек 71

2.5. Построение вариационных рядов 73

2.6. Расчет статистических показателей 75

2.7. Постановка и решение оптимизационных задач (на примере

решения транспортной задачи) 77

Библиографический список 82

Приложение 1. Функции MS Excel 83

- 5 -

Введение

В 2011 году введен в действие ФГОС ВПО по направлению подго-

товки 250100 "Лесное дело". Одним из требований стандарта является реа-

лизация компетентностного подхода. При его реализации необходимо пре-

дусматривать использование в учебном процессе активных и интерактив-

ных форм проведения занятий со студентами. Данное учебное пособие

может применяться в качестве дополнительной литературы в курсах "Био-

метрия", "Таксация леса" и в курсе "Основы лесоустройства и государст-

венной инвентаризации лесов" при организации занятий в форме деловых

игр, компьютерных симуляций, различных тренингов и разбора конкрет-

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

Первая часть пособия содержит самоучитель по освоению MS Excel и Sta-

tistica для Windows. Во второй части представлены примеры задач для ор-

ганизации учебного процесса в интерактивной форме.

В настоящее время существует достаточно много руководств, учеб-

ных и методических пособий по использованию MS Excel, изданных еще с

1988 года, когда впервые появился Excel (в DOS версии). Однако в боль-

шинстве из них рассматриваются возможности Excel для экономических

расчетов. В настоящем пособии рассматриваются возможности процессора

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

анализа в данном учебном пособии рассматриваются возможности Statisti-

ca 6.0.

С применением настоящего пособия студент может овладевать сле-

дующими компетенциями:

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

восприятию информации, постановке цели и выбору путей ее достиже-

ния (ОК-1);

готовностью к кооперации с коллегами и работе в коллективе (ОК-3);

стремлением к саморазвитию, повышению своей квалификации и мас-

терства (ОК-5);

владением основными методами, способами и средствами получения,

хранения, переработки информации, навыками работы с компьютером

как средством управления информацией (ПК-2);

владением методами таксации, мониторинга состояния и инвентариза-

ции в лесах (ПК-10);

готовностью систематизировать и обобщать информацию по использо-

ванию и формированию ресурсов предприятия (ПК-27);

способностью применять современные методы исследования лесных и

урбоэкосистем (ПК-29);

готовностью спланировать необходимый эксперимент, получить адек-

ватную модель и исследовать ее (ПК-32);

- 6 -

готовностью к участию в разработке проектов объектов лесного и ле-

сопаркового хозяйства с учетом заданных технологических и экономи-

ческих параметров с использованием новых информационных техноло-

гий (ПК-34);

способностью принимать участие в обосновании конкретного техниче-

ского решения при разработке технологических процессов рациональ-

ного, непрерывного, неистощительного использования, охраны, защи-

ты и воспроизводства лесов, направленных на повышение их продук-

тивности, сохранение средообразующих, водоохранных, защитных, са-

нитарно-гигиенических, оздоровительных и иных полезных функций

(ПК-36).

Авторы надеются, что пособие найдет применение и поможет сту-

дентам не только в освоении дисциплины "Биометрия", но также будет ис-

пользовано как вспомогательная литература в курсе "Таксация леса", кур-

совом проектировании, дипломном проектировании и при решении многих

задач, требующих вычислений, представления данных в табличной форме,

работы с базами данных.

- 7 -

1. ОБЩИЕ СВЕДЕНИЯ ОБ EXCEL

1.1. Математические вычисления в Excel

1.1.1. Общий вид (структура)

Электронные таблицы Microsoft Excel очень мощное средство созда-

ния и ведения различных электронных документов.

Интерфейс программы очень схож с Microsoft Word. После запуска

программы экран Excel содержит пять областей (по порядку сверху вниз):

строка меню;

панели инструментов;

строка формул;

окно книги;

строка состояния.

Основным отличием от Word является присутствие вместо окна до-

кумента, так называемого окна книги, другими словами электронной таб-

лицы.

Книга Excel разбита на несколько листов (таблиц). Листы можно

удалять или добавлять новые. Как и всякая таблица, лист Excel состоит из

строк и столбцов, пересечения которых образуют ячейки (рис.1).

Рис. 1. Общий вид MS Excel

- 8 -

В нижней части окна книги находится несколько кнопок, с помощью

которых можно переходить от одного листа к другому. Если видны не все

ярлычки листов, то для просмотра содержания книги можно использовать

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

Ячейки Excel являются основными строительными единицами рабо-

чего листа. Каждая ячейка имеет свои строго определенные координаты,

или адрес ячейки, где можно хранить и отображать информацию. Ячейка,

находящаяся на пересечении столбца А и строки 1 имеет адрес А1.

Зачастую границы данных таблицы выходят за пределы экрана мо-

нитора. В этом случае для просмотра содержимого листа надо использо-

вать полосы прокрутки, расположенные вдоль правой и нижней сторон ок-

на книги.

В верхней части рабочей области Excel расположена строка меню.

Ниже находятся панели инструментов с кнопками, выполняющими наибо-

лее часто используемые функции. Работа с этой частью рабочей области

Excel аналогична работе в Word.

Ниже панели инструментов расположена строка формул.

Содержимое активной (выделенной в данный момент) ячейки Excel

всегда появляется в строке формул.

Информацию можно вводить как непосредственно в ячейку, так и в

строку формул.

В самом низу окна рабочей области находится строка состояния, по-

казывающая режим работы программы.

1.1.2. Перемещение по листу Excel и выделение ячеек

Выделение с помощью мыши

Чтобы производить какие-либо действия с ячейками Excel их надо

выделить. При выделении одной ячейки ее адрес появляется в поле имени

в строке формул.

Самый простой и распространенный способ выделения одной ячейки

– щелчок левой кнопкой мыши. Подведите курсор к нужной ячейке Excel и

нажмите левую кнопку мыши. При этом вокруг ячейки появится рамка, ко-

торая говорит о том, что данная ячейка является активной, т.е. в нее можно

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

Для ускорения выполнения операций часто бывает необходимо вы-

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

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

левую кнопку мыши и, не отпуская кнопку, "тащить" мышь к правой ниж-

ней ячейке. При достижении нужной ячейки кнопку мыши надо отпустить.

При этом выделенный диапазон будет подсвечен другим цветом.

- 9 -

Для выделения большого диапазона ячеек Excel можно воспользо-

ваться приемом расширения выделения. Для этого надо выделить левую

верхнюю ячейку диапазона, затем, удерживая клавишу Shift, щелкнуть на

правой нижней ячейке, если ячейка не видна, то можно воспользоваться

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

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

формул, через двоеточие ввести координаты правой нижней ячейки и на-

жать клавишу Enter.

Если необходимо выделить группу диапазонов ячеек, то для этой це-

ли надо воспользоваться клавишей Ctrl.

Если необходимо выделить столбец или строку целиком, надо щелк-

нуть на заголовке строки или столбца.

Работа с блоком ячеек

Блок – это диапазон ячеек Excel, окруженный со всех сторон пусты-

ми ячейками или заголовками столбцов и строк.

Активная область – это прямоугольник, который заключает в себя

все столбцы и строки листа, содержащие непустые ячейки.

Рис. 2.

На рисунке представлены три блока ячеек: A3:E9, A11:E11, G3:G9.

Активная область – A3:G11. Если установить указатель мыши на нижней

границе активной ячейки и дважды щелкнуть левой кнопкой мыши, то бу-

дет выделена нижняя ячейка блока. Если активной является самая нижняя

ячейка блока, то двойной щелчок на нижней границе ячейки приведет к

выделению ячейки, расположенной ниже блока. Аналогичным образом

- 10 -

выделяются ячейки при щелчке на правой, левой или верхней границе ак-

тивной ячейки. Если дважды щелкнуть на границе ячейки при нажатой

клавише Shift, то будут выделены все ячейки, начиная с текущей до края

блока.

Перемещаться по соседним ячейкам можно при помощи клавиш

управления курсором. Для перемещения к краю блока ячеек используются

клавиши со стрелками при нажатой клавише Ctrl.

Для перемещения по листу Excel и выделения ячеек удобно пользо-

ваться клавишами Home, End.

Home Перемещение к первой ячейке текущей строки

Ctrl+Home Перемещение к ячейке A1

Ctrl+End Перемещение к последней ячейке последнего столб-

ца активной области

End Включение/Выключение режима End

Scroll Lock+Home Перемещение к первой ячейке текущего окна

Scroll Lock+End Перемещение к последней ячейке текущего окна

Для быстрого перемещения и выделения ячейки или диапазона мож-

но пользоваться командой "Правка" – "Перейти" (F5).

1.1.3. Ввод данных в ячейки Excel

Существует два типа данных, которые можно вводить в ячейки листа

Excel – константы и формулы.

Константы в свою очередь подразделяются на: числовые значения,

текстовые значения, значения даты и времени, логические значения и

ошибочные значения.

Числовые значения могут содержать цифры от 0 до 9, а также спец-

символы: + - Е е ( ) . , $ % /.

Для ввода числового значения в ячейку необходимо выделить нуж-

ную ячейку и ввести с клавиатуры необходимую комбинацию цифр. Вво-

димые цифры отображаются как в ячейке, так и в строке формул. По за-

вершению ввода необходимо нажать клавишу Enter. После этого число

будет записано в ячейку. По умолчанию после нажатия Enter активной

становится ячейка, расположенная на строку ниже, но командой "Сервис"

– "Параметры" можно на вкладке "Правка" установить необходимое на-

правление перехода к следующей ячейке после ввода, либо вообще исклю-

чить переход. Если после ввода числа нажать какую-либо из клавиш пере-

- 11 -

мещения по ячейкам (Tab, Shift+Tab…), то число будет зафиксировано в

ячейке, а фокус ввода перейдет на соседнюю ячейку.

Рассмотрим особенности ввода числовых значений, использующих

спецсимволы.

1. Если надо ввести отрицательное число, то перед числом необходимо

поставить знак "-" (минус).

2. Числа, заключенные в скобки интерпретируются как отрицательные,

даже если перед числом нет знака минуса. Т.е. (40) - для Excel означает

-40.

3. При вводе больших чисел для удобства представления между группами

разрядов можно вводить пробел (23 456,00). В этом случае в строке

формул пробел отображаться не будет, а в самой ячейке число будет с

пробелом.

4. Для ввода денежного формата используется знак доллара ($).

Для ввода процентного формата используется знак процента (%).

5. Для ввода даты и дробных значений используется знак косой черты (/).

Если Excel может интерпретировать значение как дату, например, 1/01,

то в ячейке будет представлена дата - 1 января. Если надо представить

подобное число как дробь, то надо перед дробью ввести ноль - 0 1/01.

Дробью также будет представлено число, которое не может быть ин-

терпретировано как дата, например, 88/32.

Количество вводимых цифр зависит от ширины столбца. Если шири-

на недостаточна, то Excel либо округляет значение, либо выводит символы

###. В этом случае можно попробовать увеличить размер ячейки.

Ввод текста полностью аналогичен вводу числовых значений. Вво-

дить можно практически любые символы. Если длина текста превышает

ширину ячейки, то текст накладывается на соседнюю ячейку, хотя факти-

чески он находится в одной ячейке. Если в соседней ячейке тоже присут-

ствует текст, то он перекрывает текст в соседней ячейке.

Для настройки ширины ячейки по самому длинному тексту, надо

щелкнуть на границе столбца в его заголовке. Так если щелкнуть на линии

между заголовками столбцов А и В, то ширина ячейки будет автоматиче-

ски настроена по самому длинному значению в этом столбце.

Если возникает необходимость ввода числа как текстового значения,

то перед числом надо поставить знак апострофа, либо заключить число в

кавычки – '123 или "123".

Различить какое значение (числовое или текстовое) введено в ячейку

можно по признаку выравнивания. По умолчанию текст выравнивается по

левому краю, в то время как числа - по правому.

При вводе значений в диапазон ячеек ввод будет происходить слева-

направо и сверху-вниз. Т.е. вводя значения и завершая ввод нажатием

Enter, курсор будет переходить к соседней ячейке, находящейся справа, а

по достижении конца блока ячеек в строке, перейдет на строку ниже в

крайнюю левую ячейку.

- 12 -

Для изменения значений в ячейке до фиксации ввода надо пользо-

ваться, как и в любом текстовом редакторе, клавишами Del и Backspace.

Если надо изменить уже зафиксированную ячейку, то надо дважды щелк-

нуть на нужной ячейке, при этом в ячейке появится курсор. После этого

можно производить редактирование данных в ячейке. Можно просто вы-

делить нужную ячейку, а затем установить курсор в строке формул, где

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

окончания редакции надо нажать Enter для фиксации изменений. В случае

ошибочного редактирования ситуацию можно "отмотать" назад при помо-

щи кнопки "Отменить" (Ctrl+Z).

Для защиты отдельных ячеек надо воспользоваться командой "Сер-

вис" – "Защита" – "Защитить лист". После включения защиты изменить за-

блокированную ячейку невозможно. Однако, не всегда необходимо блоки-

ровать все ячейки листа. Прежде чем защищать лист, выделите ячейки, ко-

торые надо оставить незаблокированными, а затем в меню "Формат" выбе-

рите команду "Ячейки". В открывшемся окне диалога "Формат ячеек" на

вкладке "Защита" снимите флажок "Защищаемая ячейка". Следует иметь в

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

ячеек. Если необходимо отличать заблокированные ячейки, можно выде-

лить их цветом. В защищенном листе можно свободно перемещаться по

незаблокированным ячейкам при помощи клавиши Tab.

1.1.4. Составные элементы формул

Формулы в Excel всегда начинаются со знака равенства (=), после

которого указываются следующие компоненты:

Значение. Число (19.13)

Адрес ячейки. D14, A13:D14, Лист 3!D14

Функция. СУММ, СРЗНАЧ, СЧЕТ, ЛЕВСИМВ, ЕСЛИ, НАЙТИ и т.д.

Символ операции. +,-,*,/,^,>,= и т.д.

Скобки. Используются для задания связи с файлом, например, [перечет

из методички.xls]

Пробелы. Символы табуляции и прочие управляющие символы. Исполь-

зуются для облегчения чтения формулы.

Если ячейка содержит формулу, то в такой ячейке отображается

только результат вычисления по формуле. Сама же формула отображается

в строке формул после выделения ячейки.

Если после ввода формулы в ячейку, вы по-прежнему видите форму-

лу, а не еѐ результат, убедитесь, что поставили в начале формулы знак ра-

венства (=). Создание формул в Excel можно сравнить с процессом ведения

- 13 -

расчетов на калькуляторе. Задайте формулу слева направо, связывая зна-

чения символами операций. Например:

=13.7+14.12+11.11*21

После нажатия клавиши Enter в ячейке, отобразится результат вы-

числения по формуле, (рис. 3)

Рис. 3

Большинство формул не будут (и не должны) включать числовые

значения. Они должны ссылаться на другие ячейки, которые содержат зна-

чения. Ячейки этого типа часто называют переменными, поскольку их со-

держимое может изменяться, в то время как ссылка на ячейку в формуле,

остается неизменной (хотя результат вычисления по формуле изменится).

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

без необходимости редактирования самих формул.

Постоянно встречающаяся операция – это копирование формулы.

Выполняется она следующим образом. Ячейку с копируемой формулой

необходимо сделать активной, выделив ее подвижной рамкой или просто

щелкнув по ней курсором мыши, навести курсор на правый нижний угол

рамки, чтобы курсор принял форму узкого креста и, нажав левую кнопку

мыши, растянуть рамку на нужное число ячеек, после чего кнопку отпус-

тить. Копировать формулу таким образом можно только по горизонтали

или по вертикали.

Вообще в Excel, как и в других программах MS необходимо обра-

щать внимание на вид курсора мыши, от этого зависит его функции (воз-

можности). Так курсор может быть в виде выше описанного узкого кре-

ста , так и виде белого креста , который служит для выделения блоков

ячеек с целью дальнейшей работы с ними.

1.1.5. Операции и их приоритетность

Символы операций выполняют функцию «склеивания» других ком-

понентов формулы. При наличии в формуле нескольких операций Excel

производит вычисления слева направо. Однако, у операций имеется при-

оритет, влияющий на порядок вычислений. Например, формула

=7+4*3

- 14 -

возвратит значение 19, а не 33, поскольку операция умножения приоритет-

нее сложения.

Правила приоритетов, как правило, стандартны для мира математики

и компьютеров.

В таблице 1 перечисляются доступные Excel операции в порядке

убывания их приоритета. Из двух операций первой выполняется та, у ко-

торой более высокий приоритет. Операции с одинаковым приоритетом,

например, операции умножения и деления, вычисляются слева направо.

Таблица 1

Операции Описание

: Операция ссылки (например, А1:С3)

; Разделитель аргументов

- Знак отрицательного числа (например, -14)

% Знак процента

^ Возведение в степень

* и / Умножение и деление

+ и - Сложение и вычитание

& Конкатенация текста

>,<,>=,<=,

<>

Операторы сравнения (больше чем, меньше чем, не меньше чем, не

больше чем, не равно)

Без использования приоритетов, было бы невозможно получить, од-

нозначный результат формулы. Чтобы на практике понять важность при-

оритетов операций Excel, откройте пустой рабочий лист и выполните сле-

дующие действия.

1. В ячейку A1 введите = 2+3^2

2. В ячейку A2 введите = 1+4*3

3. В ячейку A3 введите = 4+1*3

4. В ячейку A4 введите = 10-8/2

Ваши формулы должны вернуть результаты 11, 13, 7 и 6 соответст-

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

вычислений.

1.1.6. Использование скобок для изменения порядка вычислений

Если заключить часть формулы в скобки, Excel будет вычислять эту

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

нормальном процессе вычислений слева на право. Чтобы увидеть, каким

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

ствия.

- 15 -

1. В ячейку В1 введите = (2+3)^2

2. В ячейку В2 введите = (1+4)*3

3. В ячейку В3 введите = (4+1)*3

4. В ячейку В4 введите = (10-8)/2

В данном случае формулы должны вернуть значения 25, 15, 15 и 1

соответственно.

Каждая открывающая скобка в формуле должна иметь соответст-

вующую закрывающую. В противном случае Excel обнаружит противоре-

чие и предложит исправить ошибку либо позволит вам самостоятельно по-

ставить недостающую скобку.

1.1.7. Использование ссылок

Большинство формул, которые вы создадите в Excel, будут ссылать-

ся на одну или несколько из миллионов ячеек текущей рабочей книги.

Формулы зависят от ячеек, на которые создаются. Поэтому любое измене-

ние, внесенное в эти ячейки, меняет результат вычисления в формуле.

Существует несколько способов ссылки на ячейку. Чтобы сослаться

на единичную ячейку, просто укажите ее адрес, например, как в формуле

=А4*В2-25

Для ввода данной формулы необходимо выделить ячейку в которую

будет записан результат вычислений с клавиатуры ввести знак =. Затем

подвести курсор к ячейке А4 на текущем листе и однократно нажать

левую клавишу мыши, далее с клавиатуры ввести знак *, после чего ввести

значение ячейки В2 аналогично А4. Для того чтобы закончить ввод фор-

мулы необходимо с клавиатуры ввести знак – и число 25. Для получения

ответа требуется нажать Enter.

При копировании формул с помощью команд Правка\Копировать

или Вставить соответствующим образом изменяются все ссылки на рас-

положение ячеек. Такие адреса в формуле называют относительными ад-

ресами. Относительные адреса не указывают на конкретную ячейку, а про-

сто задают относительные координаты ячейки. Например, в ячейке А1 на-

ходится простая формула

=В3

ссылающаяся на ячейку, расположенную на один столбец вправо и на две

строки ниже. Если скопировать формулу из ячейки А1 в С15, ссылка на

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

ложенную один столбец вправо и на две строки ниже. Для того чтобы ско-

пировать ячейку А1 необходимо навести курсор на ячейку и нажать пра-

вую клавишу мыши появится меню (рис. 4) в котором надо подвести кур-

сор к строке «Копировать» и нажать левую клавишу мыши, затем выби-

раем ячейку куда необходимо вставить данные и нажать правую клавишу

- 16 -

мыши подвести курсор к строке «Вставить» (рис. 4) и нажать левую кла-

вишу мыши.

Рис. 4

Формула примет вид

=D17

Ссылки на диапазоны изменяются соответствующим образом. Поэтому

формула в ячейке А1

=СУММ(В3:D10)

изменится на

=СУММ(D17:F24)

после копирования ячейки из А1 в С15.

В формуле можно задать абсолютную ссылку на ячейку, которая не

будет изменять свое значение поле копирования. Чтобы создать абсолют-

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

знак доллара.

=$B$3

Для этого необходимо после окончания ввода формулы установить

курсор мыши в строке формул перед нужной нам ячейкой и нажать кла-

вишу F4.

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

B3. Ссылка на поименный диапазон (ячейку) всегда является абсолютной и

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

зафиксировать значения только по строкам, то мы удаляем знак доллара

перед цифрой =$B3, а если по столбцам, то перед буквой =B$3

- 17 -

1.1.8. Ошибки

К сожалению, при составлении формулы существует масса возмож-

ностей допустить ошибку. Однако Excel может предупредить вас об этом

несколькими способами и помочь найти ошибку.

Ниже приведен список кодов распространенных ошибок, возвращаемых

Excel при невозможности вычислить результат формулы.

#####. Столбец слишком узок для отображения числа. Расширьте столбец

либо уменьшите размер шрифта, либо измените числовой формат.

#ДЕЛ/0! Деление на ноль – недопустимая операция. И имейте в виду, что

все пустые ячейки содержат нулевые значения.

#Н/Д! Данные недоступны, поскольку формула ссылается на недоступную

(НД) функцию или значение.

#ИМЯ? Формула ссылается на неизвестное Excel имя.

#ЧИСЛО! Формула использует некорректное число.

#ССЫЛ! Ссылка на ячейку более не действительна. Возможно, ячейка

удалена либо на ее место перемещена другая ячейка.

#ЗНАЧ! Формула содержит некорректную операцию или аргумент. Воз-

можно, вы пытайтесь сложить текстовое и числовое значения.

В случае совершения нежелательной операции удаления, перемеще-

ния или другой операции, самый простой способ исправить ошибку это

щелкнуть курсором мыши на символ «Отменить» на панель инстру-

ментов.

1.1.9. Вычисления с использованием функций

Функции в формулах применяются для сокращения записи сложных

вычислений. В Excel существуют сотни функций, выполняющих широкий

спектр операций. Например, одна из самых распространенных функций

СУММ складывает числа и содержимое одной или нескольких ячеек. Так,

формула

=СУММ(A1:B5;C20;C25;3.14)

возвращает сумму числовых значений, находящихся в ячейках диапазона

A1:B5, числа 3.14 и числовых значений в ячейках C20 и C25.

Эта функция сокращает запись длинной формулы

=А1+А2+А3+А4+А5+В1+…

В Excel встроены сотни функций выполняющих множество разнооб-

разных операций. Скорее всего, вам придется регулярно использовать

лишь несколько десятков функций. С остальными можно ознакомиться

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

новые. Для использования функции в вычислениях необходимо воспользо-

ваться командой Вставка функции для чего необходимо на панели инст-

рументов выбрать меню Вставка, далее в выпавшем меню выбрать Функ-

- 18 -

ция. При этом на экране появляется сдвоенное окно (рис. 5), в верхней

части (в предыдущих версиях Excel в левой) которого дан список функций

Excel по категориям, а в нижней (правой) – список функций в выбранной

категории.

Рис. 5

При выборе какой-либо функции в нижней части окна появляется

текстовая подсказка, кратко характеризующая назначение этой функции.

При необходимости можно нажать курсором мыши кнопку Справка и по-

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

ции надо нажать кнопку ОК при этом мастер переходит в режим ввода ар-

гументов. При этом аргументы могут вводиться как с клавиатуры, так и

мышью выбирая необходимое содержимое ячеек. Некоторые функции, на-

пример, ПИ, не имеют аргументов.

После ввода всех аргументов надо выбрать клавишу ОК, и функция

оказывается введенной в соответствующую ячейку рабочего листа страни-

цы.

Некоторые функции, такие как ЧАСТОТА ( ) и ЛИНЕЙН ( ), долж-

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

в том, что результатом работы этих функций является не одно число, а

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

массива сначала необходимо ввести выбранную функцию в одну клетку,

как объяснялось выше. Вводить функцию необходимо с таким расчетом,

чтобы она оказалась в клетке, которая являться крайней для массива ре-

зультатов. После того, как функция введена, надо выделить мышкой

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

чальной ячейкой выделения блока должна быть ячейка с введенной фор-

- 19 -

мулой. Далее надо нажать и отпустить клавишу [F2], после чего одновре-

менно нажать [Ctrl], [Shift], [Enter]. Формула массива ведена. Если все

было сделано правильно, то в соответствующих ячейках должны появить-

ся результаты работы функции.

Если при вводе формулы массива допущены, какие либо ошибки (в

том числе, было выделено слишком много ячеек), то проще всего удалить

весь массив и ввести формулу снова.

1.2. Создание диаграмм в Excel

1.2.1. Мастер диаграмм

Проще всего для создания диаграмм использовать «Мастер диа-

грамм», при этом вы сможете оформить (а в дальнейшем отредактировать)

диаграмму как захотите.

В большинстве случаев построение диаграммы начинается с выбора

исходных данных. Выбранные данные см. рис. 6 содержат следующие

компоненты диаграмм.

Столбцы данных. Три столбца данных В2:В11, С2:С11 и D2:D11

Названия столбцов. Заголовки столбцов, соответствующих сериям дан-

ных.

- 20 -

Рис. 6

-20

-

- 21 -

Заголовки категорий. Заголовки в первом столбце, выведенные вдоль

оси Х и определяющие каждую группу точек.

Текст легенды. В нашем случае легендой диаграммы являются названия

рядов (В1:D1), определяющие каждую серию данных.

Выделенные для диаграммы, данные не обязательно должны быть

одним непрерывным блоком и находиться на одном рабочем листе или

книге. Однако если вы выделите не непрерывные диапазоны, то каждый из

них должен быть прямоугольным выделенным блоком. Например, к диа-

грамме на рис. 6 можно добавить большое количество рядов данных выде-

лением десяти ячеек в строке или столбце (в соответствии с десятью ячей-

ками, используемыми для каждого ряда данных имени ряда)

1.2.2. Порядок построения диаграммы

Выбрав в рабочем листе диапазон, для чего выделите его курсором

мыши , начните создание диаграммы.

1. Щелкните по кнопке Мастер диаграмм на панели инструментов

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

Вставка/Диаграмма. Откроется диалоговое окно (рис. 7).

.

Рис. 7

- 22 -

2. Выберите в списке Тип, требуемый тип создаваемой диаграммы.

3. В области Вид щелкните на требуемом стиле. Вы увидите описание,

появившееся под группой подтипов диаграмм.

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

использовании выбранного типа диаграммы, нажмите (и удерживайте)

мышью на кнопке Просмотр результата.

Щелкните по кнопке Далее и перейдите к следующему шагу мастера.

Как и остальные мастера Excel, Мастер диаграмм, всегда предос-

тавляет возможность вернуться к предыдущему примеру с помощью кноп-

ки Назад. Чтобы отказаться от создания диаграммы, щелкните по кнопке

Отмена . Если необходимо создать диаграмму с текущими параметрами

и отказаться от дополнительных возможностей мастера, в любом из диало-

говых окон мастера щелкните по кнопке Готово.

Теперь необходимо определиться, каким образом следует отобразить

выбранные данные. Параметры второго диалогового окна Мастера диа-

грамм содержит две вкладки. На рис. 8 показана вкладка Диапазон дан-

ных.

Рис. 8

- 23 -

Вкладка Диапазон данных отображает адрес диапазона А1:D11 в рабочем

листе Лист1, выбранного для построения диаграммы (рис. 6). У вас есть

возможность убедиться, что для диаграммы выбран нужный диапазон, и

при необходимости его откорректировать.

В группе Ряды в: можно выбирать переключатель Строках или

Столбцах, чтобы ряды данных выбирались из строки или столбца. В на-

шем примере ряды данных расположены в столбцах.

Теперь щелкните на вкладке Ряд диалогового окна Шаг 2. В этой

вкладке (рис. 9) также будет показан вид диаграммы, под которой нахо-

дится следующий набор параметров.

Рис. 9

На этой вкладке можно изменить ссылки на ячейки каждого ряда с

помощью следующих параметров.

Ряд. Выберите в диаграмме один из рядов. В нашем примере доступны

три ряда данных с названиями. Ср. высота, Ср. диаметр, Сумма G.

Ссылки на текущий диапазон, выбранный вами, появятся во всех строках

диалогового окна. Если щелкнуть по кнопке Удалить, из диаграммы бу-

дут удалены все выбранные ряды (данные в рабочем листе не пострадают

просто этот ряд не появится на диаграмме). Можно добавить дополни-

тельные ряды данных, щелкнув для этого по кнопке Добавить и запол-

нив определенный диапазон.

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

ячейка В1 в рабочем листе Лист1 содержит название ряда Ср. высота. В

качестве иного названия для ряда можно назначить другую ячейку. Мож-

но вернуться в рабочий лист и ввести в ячейку В1 новое название ряда.

Значения. Диапазон ячеек, которые содержат значения для выбранных

рядов. В нашем рабочем листе данные для ряда Ср. высота расположены

в ячейках В2:В11.

- 24 -

Подписи по Х. Диапазон содержит подписи, которые появятся вдоль оси

категорий. В нашем случае – это диапазон А2:А11. (по умолчанию будут

введены значения 1; 2; 3; ...)

Щелкните по кнопке Далее, чтобы перейти к третьему шагу мастера диа-

грамм.

Примечание. Изменив параметры для одного ряда, выполните по-

добные действия и с другими рядами. В противном случае не исключена

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

Третье диалоговое окно мастера (шаг 3) имеет шесть вкладок, в ко-

торых можно задать множество параметров. Вкладка Заголовки показана

на рис 10.

Рис. 10

1. Щелкните на вкладке Заголовки, если она еще не выбрана.

2. В строку Название диаграммы введите «Ход роста насаждения»

3. Выберите строку Ось Х (значений). Введите: «возраст»

4. Выберите вкладку Линии сетки и в зависимости от необходимости ус-

тановите флажки четырех параметров (рис. 11) – линии сетки помогут счи-

тывать данные с графика (особенно на распечатанной диаграмме). И на-

оборот флажки можно сбросить - если требуется не загромождать рисунок.

- 25 -

Рис. 11

5. Щелкните по вкладке Легенда, установите флажок Добавить легенду, и

переключатель Справа (рис. 12), чтобы легенда отобразилась справа от

области построения диаграммы.

Рис. 12

- 26 -

6. Задав эти параметры, щелкните по кнопке Далее, чтобы перейти к сле-

дующему шагу.

7. Последнее диалоговое окно мастера диаграмм, Шаг 4, позволяет опре-

делить расположение создаваемой диаграммы (рис. 13).

Рис. 13

Отдельном. Если выбрать этот режим, то получим диаграмму на отдель-

ном листе, полностью выделенном для нее. При этом диаграмма будет

иметь максимальный размер, ограниченный только параметрами страни-

цы. Такую диаграмму легче редактировать и печатать.

Имеющемся. Выберите этот режим для создаваемой в данный момент

диаграммы – и она будет внедрена в заданный рабочий лист (в нашем

случае это Лист1). Как правило, диаграмма внедряется для того, чтобы

получить возможность просматривать или печатать диаграмму вместе с

данными рабочего листа, которые и являются ее исходными данными.

Диаграмма будет меняться вместе с изменением исходных данных.

Задав расположение для диаграммы, щелкните по кнопке Готово,

после чего диаграмма будет внедрена в рабочий лист. При необходимости

внедренную диаграмму несложно удалить. Щелкните в области диаграм-

мы, но только не на одном из ее компонентов левой клавишей мыши и в

выпавшем меню (рис. 14) нажмите Очистить.

- 27 -

Рис. 14

1.2.3. Редактирование созданных диаграмм

Диаграмму можно редактировать и совершенствовать, манипулируя

для этого многочисленными параметрами.

Вы можете изменить размер и форму внедренной диаграммы, а так-

же переместить ее. Те же операции выполняются и с компонентами диа-

граммы. Например: перемещение главного заголовка в левую часть диа-

граммы или уменьшение области построения.

Чтобы выбрать внедренную диаграмму полностью, щелкните в лю-

бом месте области диаграммы (указатель мыши примет форму стрелки, ес-

ли его расположить над областью диаграммы), но только не на одном из ее

компонентов (заголовки, легенда, область построения и т.д.). По углам пе-

риметра области диаграммы и посередине каждой стороны периметра поя-

вятся маркеры выделения.

Выбрав внедренную диаграмму, вы можете переместить и изменить

ее размеры стандартным способом Windows. (Можно сдвинуть диаграмму

в правый нижний угол рабочего листа, чтобы открыть как можно больше

исходных данных.)

Для перемещения диаграммы выберите ее область (но не один из

компонентов), а далее нажмите и удерживайте левую кнопку мыши. Ука-

затель мыши примет форму четырех стрелок, направленных в разные сто-

- 28 -

роны. Это означает, что вы можете перемещать диаграмму. Перетащите ее

на новое место.

Если выбрать внедренную диаграмму и указать на один из маркеров

выделения, указатель мыши примет форму двух противоположно направ-

ленных стрелок, что означает возможность изменения ее размеров. Суще-

ствуют три способа изменения размера.

Изменение размера диаграммы только в одном направлении: перетащите

один из маркеров выделения, расположенных посередине одной из сто-

рон. Например, если необходимо только расширить диаграмму, перета-

щите маркер, расположенный посередине одной из вертикальных сторон

области диаграммы. Расширьте диаграмму так, чтобы подписи по оси ка-

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

Изменение размера диаграммы сразу по горизонтали и вертикали: пере-

тащите один из угловых маркеров.

Пропорциональное изменение размера диаграммы с сохранением соот-

ношения между высотой и шириной: перетащите один из угловых марке-

ров выделения, удерживая одновременно клавишу Shift.

После изменения размера внедренной диаграммы Excel перерисовы-

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

пример, если увеличить область диаграммы, Excel попытается вывести

подписи возле каждого основного деления оси категорий. Если уменьшить

размер диаграммы, Excel удалит при необходимости подписи оси катего-

рий.

Для перемещения заголовка диаграммы, легенды или области по-

строения щелкните на объекте, чтобы выбрать его, а затем перетащите в

новое место. Для изменения размера области построения или легенды вы-

берите объект и перетащите один из маркеров выделения.

Редактировать диаграмму (не имеет значения, какую - внедренную

или расположенную на отдельном листе) и ее компоненты можно различ-

ными способами в зависимости от того, что именно следует изменить.

Выберите область диаграммы или любой из компонентов - в строке меню

появится новый пункт Диаграмма. С помощью этого меню можно изме-

нить тип (первое диалоговое окно мастера), исходные данные (второе ок-

но), разнообразные параметры (третье окно) и расположение диаграммы

(четвертое диалоговое окно мастера).

Щелкните правой кнопкой мыши в области диаграммы - появится кон-

текстное меню с теми же командами, что и в меню Диаграмма, плюс ко-

манда Окно диаграммы, отображающая диаграмму в отдельном окне.

Это облегчает работу с диаграммой, поскольку вы можете изменять раз-

меры, перемещать или закрывать это окно, независимо от внедренной

диаграммы.

Щелкните правой кнопкой мыши на компоненте диаграммы (легенде, оси

категорий, ряде данных или его заголовке) и выберите из меню пункт

- 29 -

Формат Объекта. Например, щелкните правой кнопкой мыши на оси ка-

тегорий и выберите из контекстного меню пункт Формат оси. Появится

диалоговое окно Формат оси, задающее вид и параметры оси. Если два-

жды щелкнуть на компоненте диаграммы, откроется диалоговое окно с

соответствующими компоненту параметрами.

Вы можете также использовать кнопки и элементы управления, рас-

положенные на панели инструментов Диаграмма.

Закончив редактирование, щелкните на рабочем листе вне области

диаграммы, чтобы вернуться к рабочему листу. Для снятия выделения с

диаграммы, нажмите один или два раза клавишу Esc.

1.3. Дополнительные возможности Excel

1.3.1. Статистические функции

В Excel существует большое число статистических функций. Многие

из них имеют всего один аргумент – массив исходных данных:

СРЗНАЧ – функция вычисляет среднее арифметическое выборки;

СРГЕОМ - функция вычисляет среднее геометрическое выборки;

МОДА - функция вычисляет моду выборки;

МЕДИАНА - функция вычисляет медиану выборки;

ДИСП - функция возвращает дисперсию выборки;

СТАНДОТКЛОН - функция вычисляет стандартное отклонение по ге-

неральной совокупности;

СКОС - функция возвращает коэффициент асимметрии выборки;

ЭКСЦЕСС - функция возвращает коэффициент эксцесса выборки.

Или два – например, функция КОРРЕЛ возвращает парный коэффи-

циент линейной корреляции для выборок, размещенных в массивах (стро-

ках или столбцах).

Перечень основных математических и статистических функций Excel

приводится в приложении 1.

Кроме использования функций для расчета статистических показате-

лей можно использовать специальную надстройку в меню Сервис\Анализ

данных. Данная надстройка должна быть дополнительно установлена на

ПК. Многие показатели в меню Анализ данных и Вставка функции дуб-

лируются, однако надо иметь в виду что расчет, производимый через

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

ячейке. Соответственно изменение массива данных моментально приводит

к изменению значения в ячейке. Иногда это необходимо, в то же время

- 30 -

Анализ данных позволяет мгновенно рассчитать ряд статистических по-

казателей относящихся к Описательной статистике, в то время как расчет

через Вставку функции требует многократного ввода различных функ-

ций. То же самое касается расчета коэффициента парной корреляции, ко-

гда имеется множество массивов данных (например, таблица хода роста) и

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

Ряд расчетов статистических показателей приведен в лабораторном

практикуме настоящего пособия.

1.3.2. Решение оптимизационных задач

Еще одна надстройка из меню Сервис позволяет решать задачи оп-

тимизации, в частности транспортную задачу. Для ее решения используют

надстройку Поиск решения.

Принцип решения оптимизационных задач с ее помощью чрезвы-

чайно прост. В рабочей таблице необходимо выделить ячейки, которые

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

вносятся коэффициенты и ограничения, определяемые экономическими

условиями задачи; наконец, выделяется ячейка, в которую записывается

формула критерия задачи, подлежащего оптимизации. Формула критерия

строится с использованием ячеек первых двух типов. Для упрощения фор-

мирования критерия могут быть использованы промежуточные ячейки

таблицы.

Вторым шагом является вызов панели поиска решения и заполнение

в нем полей исходных данных. Указываются адрес целевой ячейки (ячейка

критерия): цель оптимизации (минимум, максимум или конкретное значе-

ние); адреса ячеек, подлежащих оптимизации (ячейки переменных); адреса

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

дополнительно указаны параметры выполнения процедуры оптимизации

(метод оптимизации, тип модели и пр.).

После этого выполняется процесс оптимизации и, если задача по-

ставлена корректно, на экране появляются результаты. При этом пользова-

тель может кроме результатов оптимизации посмотреть оценку качества

оптимизации (например, качество сходимости).

При необходимости пользователь может вернуть таблицу к исход-

ному виду, поменять значения коэффициентов или ограничений и повто-

рить решение задачи.

Подробно пример решения транспортной задачи приведен нами в

лабораторном практикуме.

- 31 -

1.3.3. Сортировка списка

Вы можете отсортировать данные в любом столбце или строке Excel,

но, как правило, такая операция осуществляется в списках.

Выбор диапазона для сортировки:

Рис. 15

Для сортировки списка имен и адресов (рис. 15) в алфавитном по-

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

1. Выделите любую ячейку в столбце А диапазона с данными в строках от

1 до 10.

2. Выберите команду Данные\Сортировка. Появился диалоговое окно

Сортировка диапазона.

Рис. 16

- 32 -

Excel выделит диапазон A2:G10. По умолчанию в диалоговом окне Сор-

тировка диапазона выбран переключатель "Идентифицировать поля по

подписям" (первая строка диапазона). В этом режиме Excel считает, что

первая строка содержит заголовки столбцов и ее не следует включать в

диапазон для сортировки. Если над выбранным диапазоном расположены

другие строки с данными, Excel не будет делать такого предположения.

Поскольку вы начали с выбора ячейки в столбце фамилий, раскрывающий-

ся список Сортировать по уже должен содержать название столбца Фа-

милия. Это значит, что именно этот столбец будет использоваться для сор-

тировки списка, в котором перечислены все заголовки столбцов, располо-

женные в верхней строке. Если заголовки столбцов не заданы, список бу-

дет содержать стандартные подписи столбцов.

3. Выберите переключатель По возрастанию группы параметров

Сортировать по. Теперь сортировка будет выполняться в алфавитном по-

рядке от А до Я.

4. В списке Затем по выберите название столбца Имя и переключатель

По возрастанию.

5. Чтобы выполнить сортировку, щелкните на ОК. Процесс сортировки

может быть слишком быстрым, чтобы его заметить.

На рис. 17 приведен полученный в результате сортировки рабочий лист.

Поскольку при задании сортировки определен второй столбец, то две

строки с одинаковыми фамилиями отсортированы по именам.

Рис. 17

1.3.4. Сортировка с помощью панели инструментов

Процедуру сортировки диапазона можно инициировать, щелкнув по

кнопке Сортировка по возрастанию или Сортировка по убыва-

нию стандартной панели инструментов. Однако Сортировка при помо-

щи панели инструментов выполняется так быстро, что можно не заметить

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

- 33 -

Например, выделенный для сортировки диапазон может содержать

несколько смежных столбцов, которые Excel автоматически включит в

сортируемый диапазон. Это может привести к нежелательным результа-

там, поэтому не следует полагаться в выделении диапазона на Excel.

Сортировка с помощью кнопок панели инструментов осуществляет-

ся следующим образом.

Выделите ячейку (в диапазоне, который предполагается отсортировать) в

том столбце, который будет определять порядок сортировки. Задать вто-

рой и третий критерии для сортировки выделением ячейки нельзя.

Щелкните на кнопке Сортировка по возрастанию, чтобы отсортировать

диапазон в алфавитном порядке от А до Я, или щелкните на кнопке Сор-

тировка по убыванию, чтобы отсортировать диапазон в обратном по-

рядке, от Я до А.

Кнопками сортировки удобно пользоваться в случаях, когда необхо-

димо сортировать диапазон различными способами, например, по возрас-

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

столбце кликните по соответствующей кнопке.

Предупреждение

Вы не сможете использовать для сортировки кнопки панели инстру-

ментов, если ваш диапазон содержит какие-либо слитые ячейки.

1.3.5. Фильтрация списка по заданному критерию

Вы можете отфильтровать список, чтобы в нем отображались только

нужные в данный момент строки. Например, можно вывести только те

строки, у которых значение в столбце Порода равно Осина, или те, для ко-

торых значение в столбце Возраст больше 80 и меньше 100. Для этого ис-

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

В нашем примере мы отфильтруем список адресов (см. рис. 18) та-

ким образом, что все строки будут скрыты, за исключением тех, которые

содержат Осина в столбце Порода. Сделайте следующее.

1. Выберите произвольную ячейку списка. Средство Автофильтр предпо-

лагает, что первая строка диапазона содержит заголовки столбцов, и будет

игнорировать ее в процессе фильтрации. Кроме того, Excel позволяет

фильтровать одновременно не более чем один список на рабочем листе.

2. Выберите команду Данные\Фильтр\Автофильтр, чтобы включить

средство Автофильтр для этого списка. В заголовках всех столбцов поя-

вится кнопка раскрытия списка.

3. Щелкните на кнопке в заголовке столбца Порода (ячейка А1), чтобы от-

крыть список со всеми уникальными значениями этого столбца. На рис. 18

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

- 34 -

брать из списка значение, которое средство Автофильтр будет использо-

вать при фильтрации.

4. Из списка Автофильтр для столбца Порода выберите значение Осина.

В результате список будет мгновенно отфильтрован (рис. 19).

Рис. 18

Рис. 19

Все строки, которые не содержат заданного критерия, будут скрыты,

как если бы вы выбрали команду Формат\Строка\Скрыть. Теперь можно

- 35 -

работать только с необходимыми вам строками, к примеру, теперь очень

легко можно построить «График высот» по породе Осина, или рассчитать

частную полноту или запас по Осине и.т.д.

После фильтрации общее количество строк в списке выводится, си-

ним цветом, напоминая о выполненной операции. Изображение стрелки

вниз на кнопке списка Автофильтр также будет иметь синий цвет для

всех столбцов, которым назначен критерий фильтрации.

Фильтрацию можно отменить двумя способами.

Щелкните на пункте Все в раскрывающемся списке Автофильтр.

Выберите команду Данные\Фильтр\Автофильтр.

Если из списка Автофильтр выбрать пункт Условие, можно создать

специальный Автофильтр с более гибкими возможностями. Например,

можно отфильтровать список по столбцу Возраст и вывести только те

строки, для которых значение в этом столбце больше 50 и меньше 70.

Чтобы сузить границы поиска, список можно отфильтровать по несколь-

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

становиться все меньше и меньше, поскольку все меньше и меньше строк

сможет "просочиться" через фильтр.

Для добавления второго фильтра к одному из предыдущих примеров вы-

полните следующие действия.

1. Щелкните на стрелке рядом с заголовком Возраст в ячейке G1.

2. Выберите из этого списка пункт Условие для открытия диалогового ок-

на Пользовательский автофильтр, показанного ниже.

Диалоговое окно Пользовательский автофильтр имеет два поля, в

которых следует задать критерии сравнения для фильтра. Каждое поле, в

свою очередь, состоит из раскрывающегося списка логических операторов

и поля для ввода сравниваемого элемента (рис. 20).

Рис. 20

- 36 -

3. В первом раскрывающемся списке выберите пункт больше

4. В расположенное рядом поле введите 50. В результате отфильтруются

только те строки, у которых значение в ячейке больше 50. Если столбец

уже содержит значение, которое может служить в качестве критерия срав-

нения, выберите его из списка.

5. Щелкните на кнопке И, чтобы второй фильтр использовался в конъ-

юнкции с первым.

6. Во втором раскрывающемся списке выберите пункт меньше.

7. В расположенное справа поле введите 70.

8. Щелкните на ОК и закройте диалоговое окно.

Теперь список отфильтрован в еще большей степени и должен вы-

глядеть так, как показано на рис. 21.

Рис. 21

С помощью логических операторов, Начинается с и Заканчивается

на можно задавать критерии фильтрации на основе символов, которыми

начинается и заканчивается содержимое ячейки.

Другим способом фильтрации списка является выбор для числового

столбца пунктов Первые 10 из раскрывающегося списка Автофильтр

(этот способ неприменим для текстовых столбцов). Такая функциональная

возможность предназначена для работы исключительно с числовыми

столбцами.

- 37 -

1.4. Введение в систему Statistica

Statistica – это интегрированная система анализа и управления дан-

ными. Statistica – это инструмент разработки пользовательских приложе-

ний в бизнесе, экономике, финансах, промышленности, медицине, страхо-

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

Все аналитические инструменты, имеющиеся в системе, доступны

пользователю. Пользователь может всесторонне автоматизировать свою

работу, вплоть до интеграции системы с другими приложениями или Ин-

тернетом. Технология автоматизации позволяет даже неопытному пользо-

вателю настроить систему на свой проект.

Процедуры системы Statistica имеют высокую скорость и точность

вычислений. Гибкая и мощная технология доступа к данным позволяет

эффективно работать как с таблицами данных на локальном диске, так и с

удаленными хранилищами данных.

Система Statistica обладает следующими общепризнанными досто-

инствами:

содержит полный набор классических методов анализа данных: от

основных методов статистики до продвинутых методов, что позволя-

ет гибко организовать анализ;

является средством построения приложений в конкретных областях;

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

высокоинтерактивным;

система может быть интегрирована в Интернет;

поддерживает web-форматы: HTML, JPEG, PNG;

легка в освоении, и как показывает опыт, пользователи из всех об-

ластей применения быстро осваивают систему;

данные системы Statistica легко конвертировать в различные базы

данных и электронные таблицы, в т.ч. в MS Excel;

поддерживает высококачественную графику, позволяющую эффект-

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

является открытой системой: содержит языки программирования,

которые позволяют расширять систему, запускать ее из других

Windows-приложений, например, из Excel.

В настоящем пособии рассматривается работа с системой Statistica

версии 6.0 – наиболее современной на сегодняшний день. Однако эти ме-

тодические указания вполне успешно может быть использовано и с ее бо-

лее ранними версиями.

- 38 -

1.4.1. Настройка системы Statistica

В системе предусмотрена возможность настройки множества харак-

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

зователя. Можно изменить, например, процесс запуска, а именно – отме-

нить установленный по умолчанию полноэкранный режим, изменить вид

стартовой панели, панели инструментов, таблиц с данными и другие пара-

метры.

Настройку общих параметров системы можно изменить в любой мо-

мент работы с программой. Эти параметры определяют:

общие аспекты поведения программы (максимизация окна Statistica

при запуске, Рабочие книги, инструмент ―Перетащить и отпустить‖ –

Drag-and-Drop, автоматические связи между графиками и данными,

многозадачный режим и т. д.);

режим вывода (например, автоматическая распечатка таблиц или

графиков, форматы отчетов, буферизация и т. д.);

общий вид окна приложения (значки, панели инструментов и т. д.);

вид окон документов (цвета, шрифты).

Все общие параметры могут быть настроены независимо от типа ок-

на документа (например, таблица или график), которое активно в данный

момент.

При работе с системой Statistica имеется возможность настройки

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

более ―продуманным‖ с точки зрения потребностей конкретного пользова-

теля.

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

висимости от требований задачи и личных предпочтений (а также эстети-

ческих соображений) можно использовать разнообразные ―режимы‖ и ус-

ловия работы программы.

Каждый из этих параметров можно настроить в соответствующем

окне, доступ к которому осуществляется через меню Tools, подменю Cus-

tomize и Option.

1.4.2. Элементарные понятия анализа данных

Здесь мы попытаемся предложить краткое обсуждение элементарных

статистических понятий, лежащих в основе процедур в любой области ста-

тистического анализа данных. Выбранные нами темы иллюстрируют ос-

новные допущения, принимаемые в большинстве статистических методов

для описания численной ―природы действительности‖, а изложение ведет-

ся на языке, доступном для широкого круга читателей.

- 39 -

Мы начнем с самых простых, интуитивно ясных понятий и рассмот-

рим связи между ними, фактически представим описание языка, на кото-

ром говорят при проведении анализа данных.

Переменная (английский термин variable) – это то, что можно изме-

рять, контролировать или чем можно манипулировать в исследованиях.

Иными словами, переменная – это то, что варьируется, изменяется, а не

является постоянным (от английского корня var).

Например, измеряя кислотность почвы в течение некоторого периода

времени или число проросших семян, вы получаете различные значения у

разных объектов исследования или одни и те же значения для одного и то-

го же объекта в разное время. Измеряя уровень осадков, получаете различ-

ные значения в разные дни недели, а также различные значения в одни и те

же дни в разных точках географической карты.

Другие примеры переменных из разных областей: анкетные данные,

систолическое давление пациентов, количество лейкоцитов в крови, цена

акций, товаров, услуг, потребление, инвестиции, доход, государственные

закупки товаров и услуг, инструмент государственного регулирования (в

экономике); рейтинг программ, доля зрителей, количество посещений сай-

та (в рекламе); скорость, температура, объем, масса в (физике) и т. д.

Очевидно, что это очень разные по своим свойствам переменные, и

поэтому можно сказать, что переменные отличаются характеристиками, в

частности, той ролью, которую они играют в исследованиях, типом изме-

рений и т. д.

1.4.3. Простейшие описательные статистики

Так как значения переменных не постоянны, нужно научиться опи-

сывать их изменения.

Для этого придуманы описательные или дескриптивные статистики:

минимум, максимум, среднее, дисперсия, стандартное отклонение, медиа-

на, квартили, мода и т. д.

Идея этих статистик очень проста: вместо того чтобы рассматривать

все значения переменной, а их может быть очень много (тысячи и миллио-

ны), вначале стоит просмотреть описательные статистики. Они дают об-

щее представление о значениях, которые принимает переменная.

Так как программа Statistica имеет англоязычный интерфейс, мы бу-

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

их английское написание

Минимум и максимум (minimum & maximum values) – это мини-

мальное и максимальное значения переменной.

- 40 -

Среднее (mean) – сумма значений переменной, деленная на n (число

значений переменной). Выборочное среднее обычно обозначается X и чи-

тается ―X с чертой‖.

Формально имеет следующее выражение:

Выборочное среднее является той точкой, сумма отклонений наблю-

дений от которой равна 0. Выборочное среднее – единственная точка, ко-

торая обладает данным свойством, и это выделяет ее среди всех других.

Формально это записывается следующим образом:

Среднее обладает рядом замечательных свойств. Однако эта оценка

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

сдвига, иногда используют взвешенное среднее (каждому значению пере-

менной приписывают определенный вес в соответствии с его важностью, а

затем для взвешенных наблюдений вычисляется обычное среднее).

Кроме того, выборочное среднее обладает еще одним замечательным

свойством: Сумма квадратов расстояний между наблюдаемыми значения-

ми и их средним арифметическим является минимальным. Если вместо

среднего арифметического взять величину, то сумма квадратов расстояний

между наблюдаемыми значениями и этой величиной будет только больше,

но никак не меньше.

Дисперсия (variance) и стандартное отклонение (standard

deviation) – наиболее часто используемые меры изменчивости переменной.

Дисперсия меняется от нуля до бесконечности. Крайнее значение 0

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

Выборочная дисперсия переменной X (термин впервые введен Фищером, в

1918 г.) вычисляется по формуле

Обратите внимание, что коэффициент в данной формуле равен n – 1,

такая оценка дисперсии является несмещенной (математическое ожидание

- 41 -

несмещенной оценки равно в точности значению оцениваемого парамет-

ра).

Стандартное отклонение (син. среднеквадратическое отклонение)

вычисляется как корень квадратный из дисперсии. Чем выше дисперсия

или стандартное отклонение, тем сильнее разбросаны значения перемен-

ной относительно среднего. Часто стандартное отклонение – более удоб-

ная характеристика, так как измерена в тех же единицах, что исходная ве-

личина. Стандартное отклонение равно корню квадратному из выборочной

дисперсии.

Формально имеем:

Медиана (median) разбивает выборку на две равные части (термин

был впервые введен Гальтоном в 1882 г.). Половина значений переменной

лежит ниже медианы, половина – выше. Медиана дает общее представле-

ние о том, где сосредоточены значения переменной, иными словами, где

находится ее центр. В некоторых случаях, например, при описании дохо-

дов населения, медиана более удобна, чем среднее.

Наблюдения упорядочивается по возрастанию: Х1< Х2 < … < Хn. По-

лученная последовательность Хi называется вариационным рядом, а ее

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

Медиана обладает следующим замечательным свойством: сумма аб-

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

Медиана является средней точкой вариационного ряда, поэтому она

не так чувствительна к выбросам. В официальной статистике США именно

медиана используется в качестве оценке центральной точки доходов насе-

ления.

Если медиана меньше среднего, то распределение сдвинуто вправо.

Если медиана больше среднего, то распределение сдвинуто влево.

Если распределение несимметрично (сдвинуто влево или вправо), то

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

в какой области концентрируются наблюдения.

Среднее и медиана оценивают положение центра выборки, вокруг

которого группируются значения переменной.

Квартили (quartile) представляют собой значения, которые делят

две половины выборки (разбитые медианой) еще раз пополам (термин

впервые использовал Гальтон в 1882 г.). Таким образом, медиана и кварти-

ли делят диапазон значений переменной на четыре равные части. Различа-

ют верхнюю квартиль, которая больше медианы и делит пополам верхнюю

- 42 -

часть выборки (значения переменной больше медианы), и нижнюю квар-

тиль, которая меньше медианы и делит пополам нижнюю часть выборки.

Нижнюю квартиль часто обозначают символом 25%, это означает, что 25%

значений переменной меньше нижней квартили. Верхнюю квартиль часто

обозначают символом 75%, это означает, что 75% значений переменной

меньше верхней квартили.

Нижняя и верхняя квартили, от слова кварта – четверть, равны соот-

ветственно 25-й и 75-й процентилям распределения. 25-я процентиль пе-

ременной – это значение, ниже которого располагаются 25% значений пе-

ременной. Аналогично, 75-я процентиль равна значению, ниже которого

расположено 75% значений переменной.

Итак, 3 точки – нижняя квартиль, медиана и верхняя квартиль – де-

лят выборку на 4 равные части. ¼ наблюдений лежит между минимальным

значением и нижней квартилью, ¼ – между нижней квартилью и медиа-

ной, ¼ – между медианой и верхней квартилью, ¼ – между верхней квар-

тилью и максимальным значением выборки.

Квартальный размах (quartile range). Квартальный размах перемен-

ных (термин был впервые использован Галтоном в 1882 г.) равен разности

значений 75-й процентили и 25-й процентили. Таким образом, это интер-

вал, содержащий медиану, в который попадает 50% наблюдений.

Квантиль (quartiles) выборки представляет собой число xp, ниже ко-

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

Кендаллом в 1940 г.).

Например, квантиль 0,25 для некоторой переменной – это такое зна-

чение (xp), ниже которого находится 25% значений переменной.

Аналогично квантиль 0,75 – это такое значение, ниже которого по-

падают 75% значений выборки.

Мода (Mode) представляет собой максимально часто встречающееся

значение переменной (иными словами, наиболее ―модное‖ значение пере-

менной), например, популярная передача на телевидении, модный цвет

платья или марка автомобиля и т. д. Термин мода был впервые введен

Пирсоном в 1894 г. Мода хорошо описывает, например, типичную реак-

цию водителей на сигнал светофора о прекращении движения. Классиче-

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

обуви или цвета обоев. Если распределение имеет несколько мод, то гово-

рят, что оно мультимодально или многомодально (его график имеет два

или более ―пика‖).

Мультимодальность распределения дает важную информацию о

природе исследуемой переменной. Например, в социологических опросах,

если переменная представляет собой предпочтение или отношение к чему-

то, то мультимодальность может означать, что существуют несколько оп-

ределенно различных мнений. Мультимодальность также служит индика-

- 43 -

тором того, что выборка не является однородной и наблюдения, возможно,

порождены двумя или более ―наложенными‖ распределениями.

Асимметрия. Асимметрия, или коэффициент асимметрии (термин

введен Пирсоном в 1895 г.), является мерой несимметричности распреде-

ления. Если этот коэффициент значительно отличается от 0, распределение

является асимметричным (несимметричным). Формально имеем:

Эксцесс. Эксцесс, или коэффициент эксцесса (термин впервые вве-

ден Пирсоном в 1905 г.) измеряет остроту пика распределения. Оценка

эксцесса, или выборочный эксцесс, вычисляется по формуле:

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

Нормальное распределение симметрично, следовательно, коэффициент

асимметрии равен 0. Эксцесс нормального распределения также равен 0,

поэтому по отклонениям выборочного эксцесса и асимметрии от 0 можно

судить о близости распределения наблюдаемой переменной к нормально-

му. Распределение с более острой вершиной, чем нормальное, имеет поло-

жительный эксцесс, а с более закругленной – отрицательный.

- 44 -

1.4.4. Шкалы измерений

Переменные различаются тем, ―насколько хорошо‖ они могут быть

измерены, или, другими словами, как много измеряемой информации

обеспечивает шкала их измерений, поскольку в каждом измерении присут-

ствует некоторая ошибка, определяющая границы ―количества информа-

ции‖, которую можно получить в данном измерении.

Другим фактором, определяющим количество информации, содер-

жащейся в переменной, конечно, является тип шкалы, в которой проведено

измерение. Вы можете считать, что шкала – это просто линейка: очень

грубая, менее грубая, точная.

Обычно используют следующие типы шкал измерений:

a) номинальная,

b) порядковая (ординальная),

c) интервальная,

d) относительная (шкала отношения).

Соответственно имеются четыре типа переменных:

a) номинальная,

b) порядковая (ординальная),

c) интервальная,

d) относительная.

Номинальные переменные используются только для качественной

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

ны только в терминах принадлежности к некоторым существенно различ-

ным классам, при этом вы не сможете определить количество или упоря-

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

ляются древесная порода (сосна-ель), тип почвы (чернозем, подзолистая,

серая лесная), признак (болен – здоров) и т. д. Часто номинальные пере-

менные называются категориальными. Близкими к ним являются категори-

зованные переменные, то есть переменные, искусственно превращенные в

категориальные.

Порядковые переменные позволяют ранжировать (упорядочить)

объекты, если указано, какие из них в большей или меньшей степени обла-

дают качеством, выраженным данной переменной. Однако они не позво-

ляют определить «на сколько больше» или «на сколько меньше» данного

качества содержится в переменной.

Порядковые переменные иногда также называют ординальными. Ти-

пичный пример – класс бонитета насаждения. Мы понимаем, что насажде-

ния I класса бонитета превосходят насаждения II класса бонитета, однако

сказать, что разница между ними равна, допустим, 18%, мы не можем. Са-

мо расположение шкал в порядке возрастания их информативности – но-

минальная, порядковая, интервальная – является хорошим примером по-

рядковой переменной. Например, можно сказать, что измерения в номи-

- 45 -

нальной шкале предоставляют меньше информации, чем в порядковой

шкале, а в порядковой – меньше, чем в интервальной. Однако невозможно

придать термину ―меньше‖ точный количественный смысл или сравнить

между собой эти различия.

Другой пример порядковой переменной – это градации влажности

почвы при определении еѐ на ощупь: сухая, свежая, влажная, сырая, теку-

чая.

Категориальные и порядковые переменные особенно часто исполь-

зуют при анкетировании, так как естественно отражают характер мышле-

ния человека. Например, измерение интенсивности посещения кинотеат-

ров можно проводить в следующей шкале: не посещаю, посещаю редко,

посещаю, посещаю часто.

Как легко понять, категориальные и порядковые шкалы часто ис-

пользуются для описания качественных признаков.

Интервальные переменные позволяют не только упорядочивать

объекты измерения, но и численно выражать и сравнивать различия между

ними.

Такого рода переменные часто возникают в естественных науках,

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

измеренная в градусах по Фаренгейту или Цельсию, образует интерваль-

ную шкалу. Вы можете не только сказать, что температура 40 градусов

выше, чем температура 30 градусов, но и то, что увеличение температуры с

20 до 40 градусов вдвое больше увеличения температуры от 30 до 40 гра-

дусов. Другой пример интервальной переменной – величина pH почвенно-

го раствора.

Относительные переменные очень похожи на интервальные пере-

менные. В дополнение ко всем свойствам переменных, измеренных в ин-

тервальной шкале, их характерной чертой является наличие определенной

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

обоснованными утверждения типа: х в два раза больше, чем у. Например,

температура по Кельвину образует шкалу отношения, и вы можете не

только утверждать, что температура 200 градусов выше, чем 100 градусов,

но и то, что она вдвое выше. Интервальные шкалы (например, шкала Цель-

сия) не обладают данным свойством шкалы отношения. Однако в боль-

шинстве статистических процедур не делается тонкого различия между

свойствами интервальных шкал и шкал отношения. Заметим, что всегда

можно перейти от более богатой шкалы к менее богатой. Так, непрерыв-

ные переменные можно искусственно превратить в категориальные, то

есть категоризовать.

Например, непрерывная переменная ―рост человека в сантиметрах‖

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

средний, высокий или очень низкий; низкий, средний, высокий, очень вы-

сокий; для размера одежды используют следующую порядковую шкалу: S,

- 46 -

M, L, XL, XXL, XXXL, XXXXL и т. д. Или в зависимости от соотношения

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

можно выделить песок (рыхлый и связный), супесь, суглинок (лѐгкий,

средний, тяжелый), глину.

Категоризованные данные часто представляют в виде частот наблю-

дений, попавших в определенные категории или классы. Для описания ка-

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

1.4.5. Исследование связей между наблюдаемыми переменными в сравнении с экспериментальными исследованиями

Большинство эмпирических исследований данных можно отнести к

одному из двух типов:

1. Это сбор данных и оценка связей между ними.

2. Прямой эксперимент, в котором фиксируются некоторые воздейст-

вия на объект исследования и регистрируется отклик.

В первом случае вы не влияете (или, по крайней мере, пытаетесь не

влиять) на какие-либо переменные, а только собираете их значения и хоти-

те найти зависимости (корреляции) между некоторыми измеренными пе-

ременными. Типичный пример здесь – измерение запасов насаждений и

свойств почв, на которых эти насаждения произрастают, с целью выявле-

ния силы и направленности возможных связей.

В экспериментальных исследованиях второго типа вы непосредст-

венно и целенаправленно варьируете некоторые переменные и измеряете

воздействия этих изменений на объект. Например, можно изучать измене-

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

Анализ данных в экспериментальном исследовании также приходит к вы-

числению ―корреляций‖ между переменными, а именно между перемен-

ными, на которые воздействуют, и теми переменными, на которые влияет

воздействие. Тем не менее, экспериментальные данные потенциально

снабжают исследователей более качественной информацией.

1.4.6. Основные шаги обработки данных в системе

Статистическая обработка данных в системе Statistica обычно состо-

ит из следующих основных шагов:

ввод исходных данных в электронную таблицу системы Statistica;

предварительные преобразования данных перед непосредственным

применением конкретного статистического метода;

- 47 -

визуализация данных при помощи того или иного типа графиков

и/или статистический анализ при помощи некоторого статистическо-

го метода;

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

процедурах;

вывод численных, текстовых и графических результатов, как на ра-

бочее пространство системы, так и в файл с отчетом; анализ резуль-

татов.

На основе нескольких простейших примеров статистической обработки

мы проиллюстрируем эти основные этапы работы.

1.4.7. Построение линейного регрессионного уравнения

Регрессионный (линейный) анализ — статистический метод исследова-

ния зависимости между зависимой переменной y и одной или несколькими

независимыми переменными x1, x2, ... xi. Независимые переменные иначе

называют регрессорами, предикторами или факторами, а зависимые пере-

менные – откликом или критериальными переменными. Терминология за-

висимых и независимых переменных отражает лишь математическую за-

висимость переменных, а не причинно-следственные отношения.

На практике регрессии чаще всего определяется в виде линейной функ-

ции y = b0 + b1x1 + b2x2 + ... + bixi, которая находится с помощью метода

наименьших квадратов

Исходные данные. Выборочные значения средней высоты и среднего

диаметра из таблиц хода роста для сосновых насаждений I класса боните-

та.

Таблица 2

Зависимость продуктивности сосновых насаждений

от почвенных свойств

Запас

насаж-

дения, м3

Уровень

грунтовых

вод, см

Запас гумуса в

корнеобитае-

мом слое, т/га

Запас на-

саждения,

м3

Уровень

грунтовых

вод, см

Запас гумуса в

корнеобитае-

мом слое, т/га

326 250,1 45,6 147 115,1 38,6

311 261,8 44,7 123 82,4 6,6

301 267,1 43,8 117 82,1 7,5

273 125,8 43,9 114 80,2 5,6

264 124,3 41,2 78 51,6 2,6

245 125,4 41,1 64 49,2 2,1

172 123,2 40,3 57 47,3 2,0

158 118,3 39,2

- 48 -

Мы запускаем программу Statistica 6.0, после чего перед нами появ-

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

с ними сильно напоминает работу в MS Excel.

Рис. 22

Вы вводим данные из таблицы 1 в первые три столбца (Var1, Var2,

Var3), после чего рабочее окно примет вид как на рис. 23.

- 49 -

Рис. 23

Выбираем пункт Multiple Regression в Меню Statistics (рис. 24).

Рис. 24

- 50 -

В появившемся окне нажимаем кнопку Variables и в сле-

дующем окне выбираем Зависимую переменную (Dependent var.) и две Не-

зависимых переменных (Independent variable list) как показано на рис. 25. В

качестве зависимой переменной мы используем ― Запас насаждения‖

(столбец Var1), а в качестве двух независимых переменных мы используем

―Уровень грунтовых вод‖ и ― Запас гумуса в корнеобитаемом слое‖

(столбцы Var2 и Var3 соответственно).

Рис. 25

После чего подтверждаем свой выбор нажатием кнопки

OK в правой части окна. Мы возвращаемся к предыдущему окну, вновь

нажимаем кнопку OK.

Перед нами возникают предварительные результаты вычислений

(рис. 26). Красным цветом подсвечиваются коэффициенты, достоверные на

уровне значимости Alpha, который можно вручную изменить. По умолча-

нию Alpha = 0,05.

- 51 -

Рис. 26

Для упрощения восприятия мы приведем краткие результаты расчета

сначала в оригинальном английском тексте, а потом продублируем их же в

русской терминологии:

Multiple Regression Results

Dependent: Var1 Multiple R = ,94135571 F = 46,70119

R?= ,88615058 df = 2,12

No. of cases: 15 adjusted R?= ,86717568 p = ,000002

Standard error of estimate:34,448557489

Intercept: 34,869627736 Std.Error: 18,11564 t( 12) = 1,9248 p = ,0783

Var2 beta=,538 Var3 beta=,465

Результаты использования расчета Множественной Регрессии

Зависимая переменная: Var1 Множественный R = ,94135571 F = 46,70119

R2 = ,88615058 степени свободы = 2,12

Число наблюдений: 15 Нормированный R2 = ,86717568 p = ,000002

Стандартная ошибка: 34,448557489

Свободный член: 34,869627736 Станд. ош.: 18,11564 t(12) = 1,9248 p = ,0783

Var2 beta=,538 Var3 beta=,465

- 52 -

Обращаем ваше внимание на то, что система Statistica опускает це-

лую часть десятичных дробей, если целая часть равна нулю. Поэтому зна-

чение ―R = ,94135571‖ следует понимать как ―R = 0,4135571‖.

Далее, мы можем перейти к более подробному анализу, нажав кноп-

ку OK, или ограничиться упрощенным представлением ре-

зультатов, для чего следует нажать на кнопку

Summary: Regression results, после чего мы получим следующий результат

(рис. 27).

Рис. 27

Для нас наибольший интерес представляют результаты, приведенные

в третьем слева столбце B, которые представляют собой коэффициенты

нашего линейного регрессионного уравнения, которое имеет следующий

вид:

Var1 = 34,86963 + Var2 * 0,68346 + Var3 * 2,28686; R2 = 0,88615058

Таким образом, мы получили следующее регрессионное уравнение

связи возраста насаждения со средней высотой и средним диаметром:

Запас насаждения = 34,86963 + Уровень грунтовых вод * 0,68346 + Запас гумуса * 2,28686;

R2 = 0,88615058

- 53 -

Коэффициенты В имеют такую же размерность, что и исходные дан-

ные и показывают, насколько изменяется переменная х при изменении пе-

ременной y на единицу.

Термином БЕТА (beta) в таблице названы стандартизованные коэф-

фициенты, или, коэффициенты частной детерминации (β-коэффициенты).

Как видно из уравнения β-коэффициенты – это безразмерные величины.

Указанные β-коэффициенты являются коэффициентами, которые были бы

получены, если бы мы заранее стандартизовали все переменные, т.е. сде-

лали их среднее равным 0, а стандартное отклонение равным 1. Одно из

преимуществ β-коэффициентов (по сравнению с B-коэффициентами) за-

ключается в том, что β-коэффициенты позволяют сравнить относительные

вклады каждой независимой переменной в предсказание зависимой пере-

менной.

В данном примере сила влияния грунтовых вод составляет примерно

54 %, а влияние запасов гумуса в корнеобитаемом слое составляет 46 %.

При построении регрессионных моделей часто появляется искуше-

ние использовать большое количество независимых переменных. Однако,

в природе так называемые ―независимые‖ переменные очень часто связаны

между собой. Поэтому обязательным условием перед построением регрес-

сионного уравнения является проверка всех независимых переменных на

наличие мультиколлинеарности. При наличии мультиколлинеарности

снижается точность оценок регрессионных коэффициентов и затрудняется

адекватная интерпретация полученной модели. Наиболее простым и эф-

фективным методом проверки на мультиколлинеарность полученных дан-

ный является расчѐт коэффициентов парной корреляции. Считается, что из

двух признаков, имеющих коэффициент парной корреляции по модулю

равный 0,85 или больше, один следует исключить.

1.4.8. Расчет основных описательных статистик

Исходные данные. Величины pH солевой вытяжки образцов дерно-

во-подзолистой почвы (табл. 3).

Таблица 3

pH (солевой)

№ pH № pH

1 4,1 6 4,0

2 4,4 7 4,9

3 5,2 8 3,6

4 4,3 9 4,8

5 3,9 10 5,0

- 54 -

Вводим эти данные, аналогично предыдущему примеру. Первый

столбец вводить необязательно, т.к. в расчетах участвует лишь второй

столбец. На этот раз мы рассчитаем основные описательные статистики.

Для этого, выберем пункт Basic Statistics/Tables в Меню Statistics (рис. 28).

Рис. 28

В появившемся окне выберем Descriptive statistics

и нажмѐм OK (рис. 29).

- 55 -

Рис. 29

Выберем переменную Var1, нажав на кнопку Variables,

как на рис. 30 и подтвердим выбор, нажав OK.

Рис. 30

- 56 -

Перед нами появляется новое окно (рис. 31). Мы переходим на за-

кладку и проставим галочки слева от тех показателей, которые

нужно рассчитать.

Рис. 31

. В данном примере мы выбрали:

Valid N – количество значений;

Mean – среднее арифметическое;

Median – медиана;

Mode – мода;

Frequency of Mode – частота встречаемости моды ;

Sum – сумма всех значений;

Minimum – минимальное значение;

Maximum – минимальное значение;

Variance – дисперсия;

Std.Dev. – стандартное (среднеквадратическое) отклонение;

Standard Error – стандартная ошибка;

Skewness – ассиметрия;

Kurtosis – эксцесс.

- 57 -

Сделав выбор, мы нажимаем кнопку Summary и попа-

даем в окно с результатами расчетов (рис. 32).

Рис. 32

Поскольку, результатов слишком много и все они не умещаются на

экране, мы можем просмотреть их последовательно, передвигая нижний

бегунок вправо.

1.4.9. Определение статистической достоверности различий

средних двух выборок

В практике исследований по лесной тематике часто мы имеем дело с

двумя (или более) выборками, для которых необходимо определить стати-

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

две пробные площади, на которых методом сплошного перечѐта измерены

диаметры стволов (табл. 4).

- 58 -

Таблица 4

Диаметры деревьев двух пробных площадей

№ Диаметр, см

п/п № 1 п/п № 2

1 24,3 14,5

2 24,1 13,2

3 21,2 13,3

4 25,3 11,2

5 17,9 10,8

6 20,1 14,1

7 21,9 15,2

8 23,6 10,1

9 24,8 12,4

10 26,2 14,5

Для определения статистической значимости различий средних мо-

жет применяться t-критерий Стьюдента. Данный критерий очень часто ис-

пользуется при обнаружении различия между средними значениями двух

выборок. Теоретически, t-критерий может применяться, даже если размеры

выборок очень небольшие, и если переменные нормально распределены

(внутри групп), а дисперсии наблюдений в группах не слишком различа-

ются. Известно, что t-критерий устойчив к отклонениям от нормальности.

Результатом использования t-критерия является p-уровень значимо-

сти, равный вероятности ошибочно отвергнуть гипотезу о равенстве сред-

них двух выборок, когда в действительности эта гипотеза имеет место.

Иными словами, он равен вероятности ошибки принять гипотезу о нера-

венстве средних, когда в действительности средние равны.

Вводим данные из таблицы 3 также как и в ранее рассмотренных

примерах. Первый столбец можно не вводить. Выбираем пункт Basic Sta-

tistics/Tables в Меню Statistics и в появившемся окне выделяем

, после чего нажимаем кнопку OK

(рис. 33).

- 59 -

Рис. 33

В появившемся окне нажимаем на кнопку Variables.

Выберем переменную Var1 в левом поле и переменную Var2 в правом по-

ле, после чего подтвердим выбор, нажав OK (рис. 34).

Рис. 34

- 60 -

Далее, нажимаем кнопку и получаем следующее окно с

результатами работы программы (рис. 35).

Рис. 35

Программа рассчитала следующие показатели:

Mean – среднее арифметическое;

Std.Dv. – стандартное (среднеквадратическое) отклонение;

N – количество значений;

Diff. – разность средних значений;

Std.Dv. Diff. – стандартная ошибка разности средних;

t – критерий Стьюдента;

df – степени свободы;

p – уровень вероятности.

Прежде всего, нас интересует величина p, которая характеризует ве-

роятность того, что наши выборки относятся с одной генеральной сово-

купности, или, иначе говоря, их средние значения статистически значимо

не различаются между собой. Как мы видим, p = 0,000002, что показывает,

что с очень большой долей вероятности мы можем считать, что наши вы-

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

- 61 -

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

графики, носящие название Box & whisker plot, в русском переводе звучит

как ―ящики и усы‖ (рис. 36).

Рис. 36

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

следует нажать кнопку . Данный график показывает,

насколько перекрываются (или не перекрываются) средние значения и до-

верительные интервалы наших выборочных средних. Как мы можем ви-

деть, графические изображения наших средних довольно далеко отстоят

друг от друга, что свидетельствует в пользу гипотезы о том, что они при-

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

- 62 -

2. ЗАДАЧИ ДЛЯ ПРОВЕДЕНИЯ ЗАНЯТИЙ

В АКТИВНОЙ И ИНТЕРАКТИВНОЙ ФОРМАХ

2.1. Работа с файлами и простейшие действия в MS Excel

(на примере расчета таксационных показателей насаждения)

Для выполнения этого задания (равно как и последующих) потребу-

ются исходные данные. Для чего необходимо открыть файл «Перечет из

методички», в котором содержатся данные по высотам и диаметрам изме-

ренных деревьев из методического пособия Н.Н. Свалова «Вариационная

статистика». Открыв файл необходимо сделать следующее:

1. Зайти в меню Файл \ Сохранить как – при этом появится следующее

окно

Рис. 37

2. В диалоговом окне «Имя файла» удалить название «Перечет из мето-

дички» и ввести свое персональное имя, например: «Сидоров – ЛХ – 22»

(рис. 37).

3. Выбрать диск и папку (указываются преподавателем) в которую необ-

ходимо сохранить свой файл.

4. Нажать курсором мыши на кнопку «Сохранить»

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

и сохраните первичный файл «Перечет из методички» на случай восста-

- 63 -

новления исходных данных. Студентам рекомендуется работать с теми же

данными, что и при расчетах вручную, для возможности сопоставления ре-

зультатов. Для этого достаточно удалить лишние ячейки. Делается это сле-

дующим образом:

Курсором мыши выделяются ненужные (лишние) ячейки;

Наведя курсор мыши на выделенный блок нажать правую клавишу

мыши;

Выбрать в выпавшем меню Удалить со сдвигом вверх.

Первое что необходимо рассчитать – это показатель «Сумма площа-

дей сечений», для чего потребуется первоначально рассчитать площади

сечений всех деревьев, а затем их суммировать.

Порядок вычислений:

- сделайте ячейку D2 текущей (выделите ее курсором мыши);

- введите в нее формулу площади круга (=B2^2*ПИ()/4)

Примечание: знак возведения в степень вводится комбинацией кла-

виш (Shift и 6), ПИ из меню Вставка\Функция\Математические

нажмите «Enter» и скопируйте формулу курсором мыши . Для чего

выделив ячейку D2, наведите курсор на правый нижний угол и, нажав

левую клавишу мыши, растяните рамку на нужное число ячеек вниз,

после чего клавишу отпустить. Результатом будет площадь сечения де-

рева в столбце D для каждого диаметра в столбце В.

суммируйте значения столбца D, щелкнув знак Σ на панели инстру-

ментов или выделив ячейку подрядом значений в столбце D, зайдите в

меню Вставка\Функция\Математические\СУММ введите в качестве

аргументов значения столбца D.

Следующий показатель – средний диаметр насаждения, для его на-

хождения вначале необходимо рассчитать площадь среднего дерева.

Порядок вычислений:

Сделать ячейку ниже той, в которой содержится сумма столбца D, те-

кущей;

Ввести формулу, разделив сумму площадей сечений на количество де-

ревьев (рис. 38):

- 64 -

Рис. 38

в следующую ячейку ввести формулу:

=КОРЕНЬ(D208*4/ПИ()) (рис. 39)

Рис. 39

- 65 -

2.2. Работа с Мастером диаграмм

(на примере построения графика высот

и определения средней высоты насаждения)

Для построения графика высот необходимо открыть Мастер диа-

грамм и выбрать точечную диаграмму (рассмотрено в соответствующем

разделе настоящего пособия).

В качестве диапазона построения диаграммы выберите столбцы

«Диаметр» и «Высота», не захватывая сами названия столбцов, в следую-

щем окне введите название диаграммы: График высот, ось Х – диаметр,

ость Y – высота. При необходимости добавьте линии сетки, легенду и др.

В следующем окне выбрав размещение диаграммы на имеющемся листе

(на том где находятся наши данные) закончите построение, нажав кнопку

Готово. Затем зайдите в меню Диаграмма\Добавить линию тренда и вы-

берите в появившемся окне тип тренда (лучше логарифмический или по-

линоминальный). Затем, открыв закладку, Параметры установите флаж-

ки:

показывать уравнение на диаграмме;

поместить на диаграмму величину достоверности аппроксимации (R^2).

Нажав затем ОК. при правильном выполнении задания получите примерно

следующий график (рис. 40).

Для вычисления средней высоты используется уравнение линии

тренда, которое вводится в ячейку рядом с предыдущими вычислениями. В

качестве аргумента Х подставляют вычисленный ранее средний диаметр

насаждения.

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

толщины, или используя известные из «Таксации леса» формулы опреде-

лить запас насаждения.

- 66 -

Рис. 40

-67

-

- 67 -

2.3. Расчет количественной спелости насаждения

Достаточно стандартная процедура в лесоустройстве, постоянно

встречающаяся в курсовом проектировании.

Исходные данные приводятся в таблицах хода роста и товарных таб-

лицах. Методика выбора соответствующей таблицы приводится в курсе

«Лесоустройство», в данном методическом пособии рассматриваются

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

технической спелости. Данные столбцов А, В, D, E, F взяты из таблиц хода

роста; столбцов G, I – из товарных таблиц. Столбцы C, H, J, K – получены

расчетным путем. Для расчета количественной спелости используются

данные столбцов Е и F. (Zср и Zтек). Сложность построения графика заклю-

чается в том, что значения столбца F относятся не к концу периода как

отображено в таблицах, а к середине. Например, Zтек=7.63 в 25 лет

Zтек=11.34 в 35 лет. MS Excel самостоятельно преобразовать данные не

сможет, их необходимо представить в соответствующем виде.

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

следующем виде (рис. 41).

Рис. 41

- 68 -

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

противном случае мы сможем построить только точечный график, а нам

необходимы пересекающиеся линии.

Для заполнения пустых ячеек прибегаем к линейной интерполяции –

в ячейку Е16 вводится формула (рис. 41):

=(Е15+Е17)/2

Затем копируем ее в буфер обмена и вставляем во все промежу-

точные ячейки диапазона, в результате в каждой ячейке в которую мы ско-

пируем формулу, отобразится среднее арифметическое между предыдущей

ячейкой и последующей.

Теперь остается зайти в Мастер диаграмм, выбрать тип диаграммы

– График, в качестве диапазона построения диаграммы ввести откоррек-

тированные нами значения Zср и Zтек. (В нашем случае с Е15 до F35), от-

крыть вкладку Ряд и в окне Подписи оси Х: ввести значения возраста (в

нашем случае с D15 по D35) там же в окне Имя поменять названия рядов с

«ряд 1», «ряд 2» на Zср и Zтек. Теперь остается оформить диаграмму введя

заголовок, добавив подписи осей, линии сетки и т.д. Для определения тех-

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

по данным Среднего прироста крупной и средней древесины (в нашем

случае столбец К) рис. 42 и рис. 43.

Эти данные получены следующим образом:

- в ячейку Н5 введена формула

=D5*G5/100

- в ячейку J5 формула

=Н5*I5/100

- в ячейку К5 формула

=J5/A5

- формулы скопированы по соответствующим столбцам Н, J, K курсором

мыши .

- 69 -

Рис. 42

- 70 -

Рис. 43

- 71 -

2.4. Расчет материальной оценки лесосеки

Одна из наиболее часто встречающихся задач в лесной таксации это

материальная оценка лесосек, ее решение в MS Excel очень органично и

практически не требует каких-либо преобразований исходных данных, т.к.

они изначально представлены в табличной форме. В производственной

практике материальную оценку производят с использованием сортимент-

ных или товарных таблиц. В настоящей работе рассмотрен вариант с сор-

тиментными таблицами, причем, как и для предыдущей работы, методика

выбора сортиментных таблиц и все теоретические положения материаль-

ной оценки лесосек рассматриваются в курсе «Таксация леса»

В рассматриваемом примере представлен расчет для сосны разряд

такс 3, разряд высот 2 (рис. 44).

Столбцы B, C, D являются исходными данными и берутся из пере-

счетной ведомости по материалам отвода и таксации лесосек. Столбцы А,

Е, F, G, H, I, J, K – данные таблиц материалов оценки лесосек вносятся в

соответствующие ячейки из сортиментной таблицы соответствующей по-

роды и разряда высот. Столбцы L, M, N, О, Р, Q, R, S – являются расчет-

ными. Методика расчета чрезвычайно проста, данные сортиментных таб-

лиц последовательно перемножаются на количество деревьев из перечет-

ной ведомости.

В ячейку М7 введена формула =Е7*В7

В ячейку N7 введена формула =F7*В7

В ячейку O7 введена формула =G7*В7

В ячейку P7 введена формула =H7*В7

В ячейку Q7 введена формула =I7*В7

В ячейку R7 введена формула =J7*В7

В ячейку S7 введена формула =K7*В7

В ячейку L7 введена формула =СУММ(Р7:S7)

Затем формулы копируются по соответствующим столбцам курсо-

ром мыши , находятся суммы по столбцам. В ячейки М18, N18, O18,

Q18, S18, вносятся цены 1 м3 древесины соответствующей категории.

Стоимость древесины находится перемножением цен на запас и после-

дующим их суммированием (ячейка L19).

- 72 -

Пример расчета материальной оценки лесосеки в MS Excel

Рис. 44

-73-

- 73 -

2.5. Построение вариационных рядов

Для того чтобы разбить на классы имеющиеся диаметры деревьев,

необходимо выполнить несколько операций:

скопировать целиком колонку B в колонку J через буфер обмена;

провести сортировку по возрастанию ;

задать границы наших классов, мы будем использовать 4 сантиметро-

вые шкалу, зададим верхние границы классов от 10 до 40 в ячейках от

К2 до К9;

сделаем активной ячейку L2, выберем Вставка\Функция\ Статисти-

ческие \Частота, в массив данных указываем имеющиеся диаметры, в

массив интервалов указываем границы классов;

После того, как функция введена, надо выделить мышкой блок яче-

ек, в которые будет помещен результирующий массив, причем началь-

ной ячейкой выделения блока должна быть ячейка с введенной форму-

лой. Далее надо нажать и отпустить клавишу [F2], после чего одновре-

менно нажать [Ctrl], [Shift], [Enter]. Формула массива введена. Если

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

появиться результаты работы функции (рис. 45).

Заканчивается работа построением Гистограммы распределения и

Кумуляты. Гистограмму построить очень просто, достаточно выбрать Тип

диаграммы в Мастере диаграмм – Гистограмма и понравившийся вид

(смотри параграф 1.2.2 настоящего методического пособия) и пройти все

этапы построения и редактирования предлагаемых Мастером диаграмм

MS Excel.

Для построения Кумуляты необходимо представить наши данные в

соответствующей форме, для чего в ячейку М2 вводим формулу

=L2+M1

т.е. текущее значение складываем с предыдущим. Так как ячейка M1 пус-

тая значение M2 будет равно L2, но при копировании данной формулы

курсором мыши мы получим накопленные количества деревьев по клас-

сам диаметра. После чего остается зайти в Мастер диаграмм и выбрав

Тип: график и Вид: График с маркерами, помечающими точки дан-

ных (или другой) закончить построение Кумуляты. (рис. 45).

- 74 -

Рис. 45

-74

-

- 75 -

2.6. Расчет статистических показателей

Для расчета статических показателей в Excel предусмотрен целый

ряд функций и надстроек, рассмотрим некоторые из них. Надстройка Ана-

лиз данных используется для получения различных статистических или

аналитических данных, на данном этапе нас будет интересовать Описа-

тельная статистика.

Порядок расчетов:

1. Выбрать Сервис\Анализ данных\Описательная статистика

2. Входным интервалом будут служить значения диаметров и высот, необ-

ходимо выделить курсором мыши все диаметры и высоты вместе с

названиями в первой строке;

3. Группирование выбираем по столбцам;

4. Ставим галочку (флажок) в пункте Метка в первой строке (это будет

означать, что в первой строке находятся данные, с которыми не произ-

водятся математические действия);

5. Параметры вывода статистики - на новый рабочий лист;

6. Ставим галочку (флажок) на пункт Итоговая статистика.

В итоге мы должны получить на новом рабочем листе табличку с

данными (рис. 46) рассмотрим некоторые из них:

Рис. 46

- 76 -

среднее арифметическое выборки;

стандартная ошибка - мера ошибки предсказанного значения y для от-

дельного значения x;

медиана - это число, которое является серединой множества чисел, то

есть половина чисел имеют значения большие, чем медиана, а полови-

на чисел имеют значения меньшие, чем медиана;

мода возвращает наиболее часто встречающееся или повторяющееся

значение в массиве или интервале данных;

стандартное отклонение - это мера того, насколько широко разброса-

ны точки данных относительно их среднего;

эксцесс характеризует относительную остроконечность или сглажен-

ность распределения по сравнению с нормальным распределением. По-

ложительный эксцесс обозначает относительно остроконечное распре-

деление. Отрицательный эксцесс обозначает относительно сглаженное

распределение.

При этом в стандартном наборе, предлагаемом в Excel, отсутствуют:

коэффициент вариации и показатель точности опыта. Однако их легко рас-

считать, используя известные из статистики формулы, так в ячейку В16

вводим формулу =B7/B3*100, а в ячейку В17 вводим формулу

=B4/B3*100.

Еще один из наиболее применяемых в статистике показателей, это

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

и насколько она тесная.

Для его расчета надо выбрать Сервис\Анализ данных\Корреляция

в выпавшем меню произвести действия, аналогичные тем, что были проде-

ланы «Описательной статистике». Если при этом во входном интервале

помимо значения диаметров и высот захватить и № дерева, то получим

следующие значения (рис. 47).

Рис. 47

Как видно из рисунка, значение само на себя дает 1, это максималь-

ное значение коэффициента корреляции, диаметр и высота коррелируют

очень тесно, а вот номер дерева ни с высотой, ни с диаметром связи не об-

наруживают, что вполне естественно. Кроме надстройки Сервис\Анализ

- 77 -

данных, статистические показатели в Excel можно рассчитать через встав-

ку функции. Для этого надо зайти в меню Вставка\Функция или щелк-

нуть мышью на , в выпавшем меню выбрать Статистические и из пред-

лагаемого набора функций выбрать интересующие Вас. При этом надо

иметь ввиду, что в отличие от Анализа данных каждую функцию прихо-

дится рассчитывать отдельно, однако в полученной ячейке будет введена

формула которая при изменении исходных данных (в нашем случае диа-

метры и высоты) поменяет вычисленное значение автоматически (в Ана-

лизе данных вычисленные значения неизменны). Соответственно каждый

из способов имеет свои плюсы и минусы в зависимости от поставленной

задачи.

2.7. Постановка и решение оптимизационных задач

(на примере решения транспортной задачи)

Теперь рассмотрим, как практически решить с помощью Excel

транспортную задачу. Будем рассматривать решение задачи закрытого ти-

па. Приведение задачи открытого типа к задаче закрытого типа известно из

теории.

Имеется девять предприятий B1, B2, B3, B4, B5, B6, B7, B8, B9 с го-

довой потребностью в ДСП 29000, 30000, 31000, 32000, 33000, 34000,

35000, 36000, 37000 м3 соответственно. Для обеспечения этих предприятий

в этом же районе имеется десять предприятий A1, A2, A3, А4,А5, А6, А7,

А8, А9, А10 с мощностью 39000 м3, 60000, 34000, 25000, 29000, 26000,

28000, 24000, 22000, 10000 м3 соответственно. Решить задачу оптимально-

го прикрепления поставщиков к потребителям, если известны стоимости

перевозок, руб. (см. табл.5), и пропускные способности коммуникаций не

ограничены.

Таблица 5

B1 B2 B3 B4 B5 B6 B7 B8 B9

А1 16 30 15 29 35 23 17 20 39

А2 20 36 42 31 41 30 12 36 75

А3 6 8 14 36 18 70 27 35 51

А4 11 27 38 72 50 40 100 93 18

А5 15 23 36 15 17 6 10 36 52

А6 27 4 40 12 36 20 100 72 15

А7 53 70 36 72 24 92 41 50 9

А8 18 85 70 42 34 24 78 100 20

А9 12 19 42 56 11 8 90 21 15

А10 78 20 13 8 40 38 50 10 6

- 78 -

Решение задачи с помощью Excel удобно выполнить в следующем

порядке (рис. 48).

Рис. 48

Сначала выделяются ячейки таблицы, которые будут играть роль оп-

тимизируемых переменных. В случае приведенной задачи это будут ячей-

ки C3 – K12 (размерность задачи 10x9). Эти ячейки удобно для наглядно-

сти обвести в рамку. Для этого установите курсор на ячейку СЗ, нажми-

те левую кнопку мыши и удерживая ее, растяните блок до ячейки K12, по-

сле чего кнопку отпустите. Нажмите правую клавишу мыши и в выпавшем

окне выберите пункт Формат ячеек, далее выберите закладку Граница, и

нажмите на кнопку Внешние. Кроме того, в ячейки АЗ-А12 и C1-K1 по-

лезно ввести названия строк и столбцов таблицы.

В ячейки ВЗ-В12 и C2-K2 вводятся ограничения задачи возможности

поставщиков и потребности потребителей соответственно.

В ячейки L3-L12 и C13-K13 вводятся формулы суммирования по

строкам и столбцам обведенной в рамку таблицы, обеспечивающие кон-

троль выполнения заданных ограничений (в результате решения задачи

значения соответствующих ячеек столбцов В и L, а также строк 2 и 13

должны сравняться).

- 79 -

Для ввода формулы суммирования, например, в ячейку L3, необхо-

димо сделать эту ячейку текущей, затем выбрать в панели инструментов

пиктограмку суммирования Σ и выделить курсором в таблице блок яче-

ек, который необходимо просуммировать (в данном случае это блок ячеек

C3-K3). Для завершения ввода формулы суммирования можно нажать кла-

вишу Enter или еще раз выбрать в панели инструментов пиктограмку сум-

мирования. После этого готовую формулу суммы можно просто скопиро-

вать в ячейки L4-L12. Для этого убедитесь, что активной является ячейка с

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

ячейки (он примет форму ), нажмите левую кнопку мыши и растяните

блок до ячейки L12 включительно, после чего кнопку отпустите.

Формулы суммирования в ячейки C13-K13 вводятся аналогично, т.е.

сначала вводится формула суммы в крайнюю ячейку (например, С13), а за-

тем получившаяся формула копируется на все остальные ячейки.

Ниже выделяются (и также для наглядности обводятся в рамку) еще

две таблицы. В первую записываются стоимости перевозок, приведенные в

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

целевой функции (критерия) оптимизации. В данном случае таблица стои-

мостей перевозок расположена в ячейках C15-K24, а таблица вычисления

целевой функции – C26-K35. Для заполнения этой таблицы используется

следующий прием. В ячейку С26 записывается формула =СЗ*С15. Для

ввода этой формулы сделайте ячейку С26 активной, наберите на клавиату-

ре [=], покажите мышкой ячейку СЗ наберите на клавиатуре [*], пока-

жите мышкой ячейку С15 и нажмите на клавиатуре Enter, затем фор-

мула копируется на всю таблицу сначала по столбцу, а затем (не снимая

получившегося выделения) сразу по всем строкам. Для этого убедитесь,

что активной является ячейка С26, наведите курсор на правый нижний

угол ячейки и, нажав левую кнопку мышки, растяните блок до ячейки

С35 включительно, теперь отпустите и снова нажмите левую кнопку мыш-

ки (проследите при этом, чтобы курсор сохранил форму и растяните

блок до ячейки K35, после чего кнопку отпустите.

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

вая формула. В данном случае выберем ячейку А16. В нее записывается

формула суммирования ячеек C26-K35.

После ввода всех данных и зависимостей вызовите Поиск решения.

Для этого выберите мышкой в горизонтальном меню пункт Сервис и в

выпавшем меню пункт Поиск решения.

В появившейся панели поиска решения (рис. 49) необходимо:

указать целевую ячейку $А$16;

указать, что целевая ячейка минимизируется;

указать изменяемые ячейки C3:K12;

задать следующие ограничения;

- 80 -

C2:K2=C13:K13 B3:B12=L3:L12 C3:K12=целое C3:K12>=0.

Рис. 49

Для выполнения этих действий:

установите курсор в строку Целевая ячейка, нажмите левую кнопку

мыши, а затем выделите курсором ячейку А16:

установите в панели поиска решения флажок Минимальное значение;

установите курсор в строку Изменяя ячейки, нажмите левую кнопку

мыши, а затем выделите курсором ячейки C3-К12;

для ввода ограничений нажмите кнопку Добавить, появится окно «До-

бавить ограничение» (рис. 50);

Рис. 50

в этом окне установите курсор в поле Ссылка на ячейку нажмите левую

кнопку мыши, затем выделите курсором ссылку (например, ячейки

B3:B12). Для удобства можно переместить окно «Добавить ограниче-

ние», захватив курсором, заголовок окна, нажать левую кнопку мыши и, не

- 81 -

отпуская кнопку, передвинуть окно так, чтобы окрылись нужные ячейки.

Затем курсором откройте список отношений, выберите среди них нужное.

Наконец, выбрав правое поле, введите в него или выделите курсором ячей-

ки, для правой части ограничения (например, L3:L12).

После формирования каждого условия (кроме последнего) выберите

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

Если во время формирования условий Вы допустили ошибку, то за-

кончите ввод всех правильных условий, затем в панели поиска решений

выделите курсором неверно введенное условие и выберите Изменить.

Для проведения расчета обязательно следует вызвать из панели По-

иска решения панель параметров и установить для решаемой задачи ли-

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

и установите курсором флажок Линейная модель. После этого выберите

OK.

После выполнения всех описанных действий остается только вы-

брать пункт Выполнить поиска решения и дождаться появления результа-

тов решения.

Решение задачи появится в ячейках C3-K12, которые были выбраны

в качестве оптимизируемых переменных. В ячейке А16 появится значение

суммарной стоимости перевозок, причем это значение будет минимальным

из возможных.

После решения задачи можно, сохранив результаты, провести неко-

торое ее исследование. Например, можно исключить из рассмотрения не-

которые маршруты перевозок, которые могут быть нежелательны из не-

экономических соображений, и оценить возможные экономические по-

следствия такого решения, повторно выполнив поиск решения. Для того

чтобы исключить какой-либо маршрут из рассмотрения, достаточно ука-

зать в таблице стоимостей очень большую стоимость (например, 99999999)

на перевозку по соответствующему маршруту.

Повтор поиска решения обычно не вызывает проблем, так как все

условия и ограничения задачи уже указаны в его панели. Необходимо

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

C3-K12 в блок и нажать кнопку Del) и после вызова поиска решения визу-

ально проконтролировать, что все задано правильно, после чего выбрать в

его панели Выполнить. В процессе решения может возникнуть сообщение

о невозможности решения задачи с применением линейной модели. В этом

случае можно повторить поиск решения, проведя корректировку его пара-

метров (отключить линейную модель, выбрать другой метод поиска реше-

ния из указанных в меню параметров поиска решения).

Подобным образом может быть решена любая экономическая задача.

Ввод данных в панель поиска решения и запуск на выполнение проведите

для этих задач самостоятельно.

- 82 -

Библиографический список

1. Боровиков В.П. STATISTICA. Искусство анализа данных на компьюте-

ре: Для профессионалов. – 2-е изд. (+CD) – СПб.: Питер, 2003. – 688 с.

2. Боровиков В.П. и др. STATISTICA 5.1 (краткое руководство), 3-е изда-

ние – М.: StatSoft , 1997. – 252 с.

3. Боровиков В.П. Программа STATISTICA для студентов и инженеров. –

2-е изд. – М.: КомпьютерПресс, 2001. – 301 с.

4. Боровиков В.П., Боровиков И.П. STATISTICA – Статистический анализ

и обработка данных в среде Windows. – М.: Информационно-

издательский дом ―Филинъ‖, 1998.

5. Вейсскопф Д. Excel 2000. К. Век+, М. ЭНТРОП, СПб.:Корона принт,

2000 г. – 400 c.

6. Вуколов Э.А. Основы статистического анализа. Практикум по стати-

стическим методам и исследованию операций с использованием паке-

тов Statistica и Excel. М.: Форум, 2008. – 464 с.

7. Гусев В.А. Математические методы и модели на ЭВМ. М.:МГУЛ, 1998

г. – 94 с.

8. Мешалкина Ю.Л., Самсонова В.П. Математическая статистика в поч-

воведении. Практикум. М.: МГУ, 2008. – 84 с.

9. Рожков В.А., Малахова И.А. Применение математических методов и

ЭЦВМ в почвоведении. – М.: Изд-во ВАСХНИЛ, 1971. – 89 с.

10. Свалов Н.Н. Вариационная статистика. – М.: МЛТИ, 1993. – 79 с.

11. Сурков А.В. Использование MS Excel для решения экономических задач

методами математической статистики. М.:МГУЛ, 2001 г. – 35 с.

12. Чернышов Ю.Н. Разработка офисных программ с помощью Visual Ba-

sic. М.:МГУЛ, 2000 г. – 28 c.

13. Чернышов Ю.Н. Решение экономических задач с помощью Excel.

М.:МГУЛ, 2001 г. – 24 c.

- 83 -

Приложение 1

Функции MS Excel

В данном приложении содержится список основных функций рабо-

чего листа Excel. Для получения более подробной информации о конкрет-

ной функции и об ее аргументах выберите ее в диалоговом окне Мастер

функций и нажмите кнопку Справка.

Функции категории Математические

Функция (рус.) Функция (англ.) Назначение

ABC ABC Возвращает модуль (абсолютную величину) числа

ACOS ACOS Возвращает арккосинус числа в радианах

ACOSH ACOSH Возвращает гиперболический арккосинус числа

ASIN ASIN Возвращает арксинус числа

ASINH ASINH Возвращает гиперболический арксинус числа

ATAN ATAN Возвращает арктангенс числа

ATAN2 ATAN2 Возвращает арктангенс для заданных координат X и Y

ATANH ATANH Возвращает гиперболический арктангенс числа

COS COS Возвращает косинус заданного угла

COSH COSH Возвращает гиперболический косинус числа

EXP EXP Возвращает число «е», возведенное в указанную степень

LN LN Возвращает натуральный логарифм числа

LOG LOG Возвращает логарифм числа по заданному основанию. Если

основание опущено, то оно полагается равным 10

LOG10 LOG10 Возвращает десятичный логарифм числа

SIN SIN Возвращает синус заданного угла

SINH SINH Возвращает гиперболический синус числа

TAN TAN Возвращает тангенс заданного угла

TANH TANH Возвращает гиперболический тангенс числа

ГРАДУСЫ DEGRESS Преобразует радианы в градусы

ЗНАК SIGH Возвращает 1, если число положительное, 0, если число

равно 0, и -1, если число отрицательное

КОРЕНЬ SQRT Возвращает положительное значение квадратного корня из

неотрицательного числа

МОБР MINVERSE Возвращает обратную матрицу

МОПРЕД MDETERM Возвращает определитель матрицы

- 84 -

Продолжение прил. 1

Функция (рус.) Функция (англ.) Назначение

МУМНОЖ MMULT Возвращает произведение матриц

НЕЧЕТ ODD Возвращает число, округленное до ближайшего нечетного

целого

ОКРВВЕРХ CEILING Возвращает результат округления с избытком до ближайше-

го числа, кратного точности

ОКРВНИЗ FLOOR Возвращает результат округления числа до заданной точно-

сти с недостатком

ОКРУГЛ MROUND Округляет число до указанного количества десятичных раз-

рядов

ОКРУГЛВВЕРХ ROUNDUP Округляет число до ближайшего большого по модулю цело-

го

ОКРУГЛВНИЗ ROUNDDOWN Округляет число до ближайшего меньшего по модулю цело-

го

ОСТАТ MOD Возвращает остаток от деления числа на делитель

ОТБР TRUNC Усекает число до целого, отбрасывая его дробную часть

ПИ PI () Возвращает значение числа π

ПРОИЗВЕД PRODUCT Возвращает произведение чисел, заданных в качестве аргу-

мента

ПРОМЕЖУТОЧНЫЕ

ИТОГИ SUBTOTAL Возвращает промежуточный итог в список или базу данных

РАДИАНЫ RADIANS Преобразует градусы в радианы

РИМСКОЕ ROMAN Преобразует число из арабской записи в римскую запись

СЛЧИС RAND Возвращает равномерно распределенное случайное число,

большее или равное 0 и меньшее 1

СТЕПЕНЬ POWER Возвращает результат возведения числа в степень

СУММ SUM Возвращает сумму всех чисел, входящих в список аргумен-

тов

СУММЕСЛИ SUMIF Возвращает сумму значений в ячейках, специфицированных

заданным критерием

СУММКВ SUMSQ Возвращает сумму квадратов аргументов

СУММКВРАЗН SUMXMY2 Возвращает сумму квадратов разностей соответствующих

значений в двух массивах

СУММПРОИЗВ SUMPRODUCT Возвращает сумму произведений соответствующих элемен-

тов массивов

СУММРАЗНКВ SUMX2MY2 Возвращает сумму разностей квадратов соответствующих

значений в двух массивах

СУММСУММКВ SUMX2PY2 Возвращает сумму сумм квадратов соответствующих эле-

ментов двух массивов

СЧЕТЕСЛИ COUNTIF Возвращает количество не пустых ячеек заданного диапазо-

на, удовлетворяющих заданному критерию

СЧИТАТЬПУСТО-

ТЫ COUNTBLANK Возвращает количество пустых ячеек в заданном диапазоне

ФАКТР FACT Возвращает факториал числа

ЦЕЛОЕ INT Возвращает число, округленное до ближайшего меньшего

целого

ЧЕТН EVEN Возвращает число, округленное до ближайшего четного це-

лого

ЧИСЛКОМБ COMBIN Возвращает количество комбинаций для заданного числа

объектов

- 85 -

Продолжение прил. 1

Функции категории Статистические

Функция (рус.) Функция (англ.) Назначение

FРАСП FDISP Возвращает F – распределение вероятности

FРАСПОБР FINV Возвращает обратное значение для F – распределения веро-

ятности

ZТЕСТ ZTEST Возвращает двустороннее Р – значение z – теста

БЕТАОБР BETAINV Возвращает обратную функцию к интегральной функции

плотности β – вероятности

БЕТАРАСП BETADIST Возвращает интегральную функцию плотности β - веро-

ятности

БИНОМРАСП BINOMDIST Возвращает отдельное значение биномиального распределе-

ния

ВЕЙБУЛЛ WIEBULL Возвращает распределение Вейбулла

ВЕРОЯТНОСТЬ PROB Возвращает вероятность того, что значение из интервала на-

ходится внутри заданных пределов

ГАММАНЛОГ GAMMALN Возвращает натуральный логарифм гамма функции Г (х)

ГАММАОБР GAMMAINV Возвращает обратное гамма распределение

ГАММАРАСП GAMMADIST Возвращает гамма распределение

ГИПЕРГЕОМЕТ HYPGEOMDIST Возвращает гипергеометрическое распределение

ДИСП VAR Возвращает дисперсию по выборке

ДИСПР VARP Возвращает дисперсию для генеральной совокупности

ДОВЕРИТ CONFEDENCE Возвращает доверительный интервал для среднего генераль-

ной совокупности

КВАДРОТКЛ DEVSQ Возвращает сумму квадратов отклонений точек данных от их

среднего

КВАРТИЛЬ QUARTILE Возвращает квартиль набора данных

КВПИРСОН RSQ Возвращает квадрат коэффициента корреляции Пирсона

КОВАР COVAR Возвращает ковариацию, т.е. среднее произведений отклоне-

ний для каждой пары точек данных

КОРЕЛ CORREL Возвращает коэффициент корреляции

КРИТБИНОМ CRITBINOM

Возвращает наименьшее значение, для которого интеграль-

ное биноминальное распределение больше или равно задан-

ному критерию

ЛГРФПРИБЛ LOGEST Возвращает экспоненциальное уравнение регрессии

ЛИНЕЙН LINEST Возвращает линейное уравнение регрессии

ЛОГНОРМОБР LOGINV Возвращает обратную функцию нормального логарифмиче-

ского распределения

ЛОГНОРМРАСП LOGNORMDIST Возвращает нормальное интегральное логарифмическое рас-

пределение

МАКС MAX Возвращает максимальное значение из списка аргументов

МЕДИАНА MEDIAN Возвращает медиану заданного набора чисел

МИН MIN Возвращает наименьшее значение в списке аргументов

МОДА MODE Возвращает наиболее часто встречающееся значение набора

данных

- 86 -

Продолжение прил. 1

Функция (рус.) Функция (англ.) Назначение

НАИБОЛЬШИЙ LARGE Возвращает к-е наибольшее значение из множества данных

НАИМЕНЬШИЙ SMALL Возвращает к-е наименьшее значение из множества данных

НАКЛОН SLOPE Возвращает наклон линии линейной регрессии

НОРМАЛИЗАЦИЯ STANDARDIZE Возвращает нормализованное значение для распределения,

характеризуемого средним и стандартным отклонением

НОРМОБР NORMINV Возвращает обратное нормальное распределение для указанно-

го среднего и стандартного отклонения

НОРМРАСП NORMDIST Возвращает нормальную функцию распределения для указан-

ного среднего и стандартного отклонения

НОРМСТОРБ NORMSINV Возвращает обратное значение стандартного нормального рас-

пределения

НОРМСТРАСП NORMSDIST Возвращает стандартное нормальное интегральное распреде-

ление

ОТРБИНОМРАСП NEGBINOMDIST Возвращает отрицательное биномиальное распределение

ОТРЕЗОК INTERCEPT Возвращает отрезок, отсекаемый на оси линией линейной рег-

рессии

ПЕРЕСТ PERMUT Возвращает количество перестановок для заданного числа объ-

ектов, которые выбираются из общего числа объектов

ПЕРСНЕНТИЛЬ PERCENTILE Возвращает к-ю перцентиль для значений из интервала

ПИРСОН PEARSON Возвращает коэффициент корреляции Пирсона

ПРЕДСКАЗ FORECAST Возвращает предсказанное значение функции в данной точке

на основе уравнения линейной регрессии

ПРОЦЕНТРАНГ PERCENTRANK Возвращает процентное содержание значения из множества

данных

ПУАССОН POISSON Возвращает распределение Пуассона

РАНГ RANK Возвращает ранг числа в списке чисел. Ранг числа – это его

величина относительно других значений в списке

РОСТ GROWTH Возвращает значения в соответствии с экспоненциальным

уравнением регрессии

СКОС SKEW Возвращает коэффициент асимметрии распределения

СРГАРМ HARMEAN Возвращает среднее гармоническое множества данных

СРГЕОМ GEOMEAN Возвращает среднее геометрическое значений

СРЗНАЧ AVERAGE Возвращает среднее (арифметическое) значение

СРОТКЛ AVEDEV Возвращает среднее абсолютных значений отклонений точек

данных от среднего

СТАНДОТКЛОН STDEV Возвращает стандартное отклонение по выборке

СТАНДОТКЛОНП STDEVP Возвращает стандартное отклонение по генеральной выборке

СТОШXY STEXY Возвращает стандартную ошибку предсказанных значений Y

для каждого значения Х в регрессии

СТЬЮДРАСП TDIST Возвращает t – распределение Стьюдента

СТЬЮДРАСПОБР TINV Возвращает обратное распределение Стьюдента для заданного

числа степеней свободы

СЧЕТ COUNT Возвращает количество чисел в списке аргументов

СЧЕТЗ COUNTA Возвращает количество непустых значений в списке аргумен-

тов

ТЕНДЕНЦИЯ TREND Возвращает значения в соответствии с линейным уравнением

регрессии

- 87 -

Окончание прил. 1

Функция (рус.) Функция (англ.) Назначение

ТТЕСТ TTEST Возвращает вероятность, соответствующую критерию Стью-

дента

УРЕЗСРЕДНЕЕ TRIMMEAN Возвращает усеченное среднее значение

ФИШЕР FISHER Возвращает преобразование Фишера

ФИШЕРОБР FISHERINV Возвращает обратное преобразование Фишера

ФТЕСТ FTEST Возвращает результат F – теста

ХИ20ОБР CHIINV Возвращает обратную функцию для Χ распределения

ХИ2РАСП CHIDIST Возвращает одностороннюю вероятность Χ2 распределения

ХИ2ТЕСТ CHITEST Возвращает тест на независимость

ЧАСТОТА FREQUENCY Возвращает распределение частот в виде вертикального масси-

ва

ЭКСПРАСП EXPONDIST Возвращает экспоненциальное распределение

ЭКСЦЕСС KURT Возвращает коэффициент эксцесса множества данных

- 88 -

Учебное пособие

Стоноженко Леонид Валерьевич

Югов Андрей Николаевич

Карминов Виктор Николаевич

Применение MS Excel и Statistica for Windows

для лесотаксационных вычислений

и обработки экспериментальных данных

методами математической статистики

Под редакцией авторов

Компьютерный набор и верстка Югова А.Н.

По тематическому плану внутривузовских изданий учебной литера-

туры на 2012 г.

Подписано в печать _______. Формат ___. Бумага ___

Гарнитура "___". Ризография. Усл. печ. л. 5,6.

Тираж 200 экз. Заказ № ___

Издательство Московского государственного университета леса,

141005, Мытищи-5, Московская обл., 1-я Институтская улица, 1, МГУЛ.

Е-mail: izdat@mgul.ac.ru

По вопросам приобретения литературы издательства ФГБОУ ВПО МГУЛ

обращаться в отдел реализации.

Телефон: 8 (498) 687-37-14