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

В состав Microsoft Excel входит набор средств анализа данных (так называемый пакет анализа), предназначенный для решения сложных статистических и инженерных задач.

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

Выполнение статистического анализа

В меню Сервис выберите команду Анализ данных. Если эта команда недоступна, загрузите пакет анализа.

В меню Сервис выберите команду Надстройки.

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

Выберите нужную функцию в диалоговом окне Анализ данных и нажмите кнопку ОК. Установите параметры анализа в соответствующем диалоговом окне (рис.5.1).

Например, для вычисления коэффициента корреляции между двумя наборами данных на листе используется статистическая функция КОРРЕЛ.

Рис.5.1. Пример выбора вида статистического анализа в Microsoft Exel

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

С использованием средств статистического анализа данных Microsoft Exel была проанализирована зависимость поступления платежей за землю от объемов финансирования земельно-кадастровых и землеустроительных работ. В расчетах были использованы данные за период 2001-2006 гг.

Для этого на листе Microsoft Exel были введены данные о поступлении платежей за землю (графа B) и о финансировании земельно-кадастровых работ (графа С) (см. табл.5.1).

Таблица 5.1.

Пример представления данных в Microsoft Exel

Цифры в левой колонке таблицы 5.1 обозначают номера строк таблицы Microsoft Exel и используются вместе с именами ячеек (графы А,В,С) для обозначения местонахождения данных при написании формул и выаолнении простейших математических операций).

Далее в меню была выбрана закладка Сервис, функция Анализ данных, РЕГРЕССИЯ. В появившемся мастере был указан диапазон данных, включаемых в уравнение.

По результатам корреляционно-регрессионного анализа было составлено следующее уравнение зависимости поступления платежей за землю (П) от объемов финансирования земельно-кадастровых и землеустроительных работ (Ф):

П = − 10,489 + 0,047∙Ф, R=0,814, R²=0,663

Коэффициент множественной корреляции равен 0,814, коэффициент детерминации равен 0,663, что свидетельствует о тесной зависимости поступления платежей за землю от объемов финансирования земельно-кадастровых и землеустроительных работ.

На рисунке 5.2. представлен график подбора показателя «затраты на земельно-кадастровые и землеустроительные работы». График показывает, что при фактическом уровне платежей за землю в 2006 году объем финансирования земельно-кадастровых работ и землеустроительных работ составил 90% от необходимого объема, а должен был составить 40,42 млн.руб. в год. При планировании объемов финансирования затрат на управлении земельными ресурсами следует их увеличить на 10%.

Рис.5.2. Платежи за землю. График подбора