Пакет анализа в MS Excel

Таблица 1

Статистические функции в MS Excel

Цель

Средства статистической обработки MS Excel

Лекция 2. MICROSOFT EXCEL.

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

2. Ознакомиться с возможностями Пакета анализа в Excel.

3. Привести примеры работы со списками в Excel.

Пусть представлены следующие статистические данные (см. таб. 1), по которым надо вычислить:

§ количество опрошенных;

§ количество опрошенных женщин;

§ процент женщин среди опрошенных;

§ процент мужчин среди опрошенных;

§ средний возраст опрошенных (среднеарифметическое);

§ средний возраст (медиана);

§ минимальный и максимальный возраст опрошенных;

§ количество женщин с высшим образованием;

§ средний возраст женщин с высшим образованием;

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

1) СЧЕТ(значение1; значение2;…), которая подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек.

Аргументы: значение1; значение2; …— это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.

2) СЧЕТЕСЛИ(диапазон;критерий), где диапазон – диапазон, в котором нужно подсчитать ячейки. Критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

3) СРЗНАЧ, которая возвращает среднее (арифметическое) своих аргументов. СРЗНАЧ(число1; число2; ...)

Число1, число2, ...– это от 1 до 30 аргументов, для которых вычисляется среднее.

4) МЕДИАНА(число1;число2;...). Число1, число2,...– от 1 до 30 чисел, для которых определяется медиана. Медиана – это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана.

5) МОДА(число1;число2;...). Число1, число2,...– от 1 до 30 чисел, для которых определяется мода. МОДА определяет значение, которое чаще других встречается во множестве чисел.

6) МАКС(число1;число2; ...). Число1, число2,...– от 1 до 30 чисел, среди которых требуется найти наибольшее.

7) МИН(число1;число2; ...). Число1, число2,...– от 1 до 30 чисел, среди которых требуется найти наименьшее.

8) если числовые значения образуют полную генеральную совокупность, то для вычисления дисперсии и стандартного отклонения (среднего квадратического отклонения) используются функции ДИСПР и СТАНДОТКЛОНП.

9) функции ДИСП и СТАНДОТКЛОНиспользуются, если необходимо произвести вычисления дисперсии и стандартного отклонения по выборке.

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

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

– создания гистограмм;

– ранжирования данных;

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

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

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

Доступ к определенному инструменту Пакету анализа может быть обеспечен следующим образом:

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

Если указанный выше пункт меню отсутствует, то необходимо установить соответствующий компонент, выполнив команду Сервис – Надстройки. В диалоговом окне выбрать Пакет анализа. Затем следует повторить пункт 1.

2. Выбрать нужный инструмент в списке.

Опишем назначение некоторых наиболее часто употребляющиеся инструментов Пакета анализа:

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

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

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