IV. СТАТИСТИЧЕСКИЙ АНАЛИЗ С ИСПОЛЬЗОВАНИЕМ

Microsoft Excel предоставляет для проведения анализа данных широкий набор функций, основное назначение которых - проведение простого статистического анализа данных. Данный табличный процессор включает более 70 статистических функций. Правильное использование статистических функций помогает глубоко и всесторонне изучить имеющиеся наборы данных, сохраненных в рабочих листах Microsoft Excel, списках или сводных таблицах.

 

1. Базовые понятия

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

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

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

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

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

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

Продемонстрируем сказанное на двух примерах использования простейших функций. Цел настоящих примеров - дать общие знания об использовании функций.

Функция СУММ предназначена для вычисления суммы всех значений, указанных в качестве ее аргументов. В приведенном ниже примере этими аргументами являются числа 2, 2, значение ячейки А1 и значения, введенные в диапазоне В3:G5.

=СУММ(2;2;А1;В3:G5).

В следующем примере показано использование функции СРЗНАЧ, которая вычисляет среднее для всех значении, сохраненных в диапазоне В2:В100.

В выше приведенных примерах необходимо знать следующее. Ввод ссылок на ячейку (в нашей ситуации А1) или на диапазон ячеек (В3:G5 или В2:В100) можно осуществить двумя способами: непосредственный ввод и указание манипулятором мышь. В первом случае регистр клавиатуры должен быть переведен на английский либо комбинацией клавиш CTRL+SHIFT (или ALT+SHIFT), либо изменением индикатора клавиатуры на панели задач с помощью манипулятора мышь. Во втором случае фиксируется манипулятором мыши соответствующая ячейка на рабочем листе или выделяется диапазон ячеек. Выделение диапазона производится следующим образом: указатель манипулятора мышь устанавливается в предполагаемую начальную ячейку диапазона и при фиксированном положении левой кнопки мыши указатель перемещается до последней ячейки диапазона, а затем отпустить кнопку.

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

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

 

Типы функций.

Для удобства работы функции в Microsoft Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, статистические, текстовые и математические и др.

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

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

В Microsoft Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную, транспонировать.

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

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

 

Использование команды Функция.

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

Процесс использования команды Вставка - Функция заключается в следующем.

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

2. Выбрать команду Вставка - Функция. Или щелкнуть манипулятором мышь на кнопке Вставка функции.

3. На появившемся диалоговом окне определить категорию функции.

4. Выбрать нужную функцию и подтвердить.

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

6. После определения значения всех аргументов щелкнуть по кнопке ОК.

 

2.Краткое описание некоторых статистических функций

 

Подсчет количества элементов.

Подсчет количества ячеек с числовыми значениями: =СЧЕТ(диапазон). Значение аргумента диапазон указывает на диапазон ячеек, содержащих исходные данные. Пример: =СЧЕТ(А1:В5).

Подсчет непустых ячеек: =СЧЕТЗ(диапазон). Пример: =СЧЕТЗ(С1:D6).

Подсчет количества пустых ячеек: =СЧИТАТЬПУСТОТЫ(диапазон). Пример: =СЧИТАТЬПУСТОТЫ(А5:H8).

Посчет ячеек, значения которых удовлетворяют заданному критерию: =СЧЁТЕСЛИ(диапазон;критерий). Пример: =СЧЁТЕСЛИ(С1:С10;">4").

 

Средние значения, моды и медианы.

 

Поиск значений, рангов.

 

Стандартное отклонение и дисперсия.

 
Распределение хи-квадрат.

 

Корреляция.

 

3.Использование инструментов описательной статистики

Под описательной статистикой подразумевается метод описания больших наборов данных с помощью нескольких ключевых показателей. Некоторые инструменты описательной статистики в Microsoft Excel становятся доступными после подключения надстройки Пакет анализа. В случае отсутствия оной необходимо выбрать команду Сервис - Надстройки, в открывшемся диалоговом окне установить флажок напротив опции Пакет анализа и щелкнуть на кнопке ОК. Инструментальные средства Пакета анализа позволяют провести анализ данных по широкому кругу статистических показателей, и в т.ч. показателей описательной статистики.

Продемонстрируем особенности его работы на примере списка данных (рис. 1 ).

Рис. 1 . Исходный набор данных

1. 
   Выбрать команду Сервис-Анализ данных.
   
2. 
   В диалоговом окне Анализ данных в списке Инструменты анализа выделить пункт Описательная статистика и щелкнуть на кнопке ОК (рис.2.).
   

Рис. 2 . Диалоговое окно Анализ данных

Рис. 3 . Диалоговое окно Описательная статистика

Рис. 4. Значения показателей описательной статистики

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

Вышеуказанные действия можно оформить в соответствии с рис. . В результате подтверждения ввода формул в ячейках будут представлены числовые значения, полученные в ходе вычислений, а не сами формулы. Адреса ячеек вводятся фиксированием указателем соответствующей ячейки. Названия используемых функций в данном примере могут вводить непосредственно, или использованием команд Вставка – Функция. Для округления полученного значения классового промежутка К до ближайшего четного числа используется функция ЧЕТН(К).

Введем в G4 значение W_.

Введем в G5 значение W_.

Зная значение классового промежутка К введем остальные границы классов (ячейки G6 по G11). Правильность определения границ классов проверить по рис.5.

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

1. Использовать команду Сервис-Анализ данных.

2. В диалоговом окне Анализ данных в списке Инструменты анализа выбрать пункт Гистограмма и щелкнуть на кнопке ОК.

3. В диалоговом окне Гистограмма определить диапазоны ячеек, содержащих данные, которые необходимо проанализировать. Входной интервал - диапазон ячеек с исходными данными, интервал карманов - диапазон ячеек с границами классов (категории) (рис.6.).

4. В диалоговом окне Гистограмма определить параметры вывода. Выбрать один из трех вариантов вывода.
Рис. 5. Пример оформления расчетов.

Рис. 6. Установление параметров гистограммы