Программа Excel – это не просто табличный редактор, но ещё и мощный инструмент для различных математических и статистических вычислений. В приложении имеется огромное число функций, предназначенных для этих задач. Правда, не все эти возможности по умолчанию активированы. Именно к таким скрытым функциям относится набор инструментов «Анализ данных». Давайте выясним, как его можно включить.

Включение блока инструментов

Чтобы воспользоваться возможностями, которые предоставляет функция «Анализ данных», нужно активировать группу инструментов «Пакет анализа», выполнив определенные действия в настройках Microsoft Excel. Алгоритм этих действий практически одинаков для версий программы 2010, 2013 и 2016 года, и имеет лишь незначительные отличия у версии 2007 года.

Активация

  1. Перейдите во вкладку «Файл». Если вы используете версию Microsoft Excel 2007, то вместо кнопки «Файл» нажмите значок Microsoft Office в верхнем левом углу окна.

В открывшемся окне параметров Эксель переходим в подраздел «Надстройки» (предпоследний в списке в левой части экрана).

В этом подразделе нас будет интересовать нижняя часть окна. Там представлен параметр «Управление». Если в выпадающей форме, относящейся к нему, стоит значение отличное от «Надстройки Excel», то нужно изменить его на указанное. Если же установлен именно этот пункт, то просто кликаем на кнопку «Перейти…» справа от него.

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

Запуск функций группы «Анализ данных»

Теперь мы можем запустить любой из инструментов группы «Анализ данных».

    Переходим во вкладку «Данные».

  • После этого запускается окошко с большим перечнем различных инструментов, которые предлагает функция «Анализ данных». Среди них можно выделить следующие возможности:
    • Корреляция;
    • Гистограмма;
    • Регрессия;
    • Выборка;
    • Экспоненциальное сглаживание;
    • Генератор случайных чисел;
    • Описательная статистика;
    • Анализ Фурье;
    • Различные виды дисперсионного анализа и др.

      • Выбираем ту функцию, которой хотим воспользоваться и жмем на кнопку «OK».

      Работа в каждой функции имеет свой собственный алгоритм действий. Использование некоторых инструментов группы «Анализ данных» описаны в отдельных уроках.

      Как видим, хотя блок инструментов «Пакет анализа» и не активирован по умолчанию, процесс его включения довольно прост. В то же время, без знания четкого алгоритма действий вряд ли у пользователя получится быстро активировать эту очень полезную статистическую функцию.

      Отблагодарите автора, поделитесь статьей в социальных сетях.

      Важным способом «описания» переменной является форма ее распределения, которая показывает, с какой частотой значения переменной попадают в определенные интервалы. Эти интервалы, называемые интервалами группировки, выбираются исследователем. Обычно исследователя интересует, насколько точно распределение можно аппроксимировать каким-либо стандартным распределением, например нормальным. Простые описательные статистики дают об этом некоторую информацию. Более точную информацию о законе распределения можно получить с помощью графического анализа, а также с использованием специальных статистических критериев.

      1. Построение гистограммы в Excel.Рассмотрим построение гистограммы в Excel с помощью «Пакета анализа»: Данные → Анализ данных → Гистограмма → ОК. После этого появится диалоговое окно (рис. 1.14), где в качестве входного интервала требуется ввести ссылку на ячейки, содержащие анализируемые данные. Установите флажок напротив слов Вывод графикаи нажимаем OK.

      Кроме этого пользователь может выбрать необязательный параметр Интервал карманов— набор граничных значений, определяющих отрезки (карманы). Excel вычисляет число попаданий данных в диапазон между текущим началом отрезка и соседним большим по порядку, если такой существует. Если диапазон карманов не введен, то набор отрезков, равномерно распределенных между минимальным и максимальным значениями данных, будет создан автоматически.

      2. Построение гистограммы в STATISTICA.Рассмотрим построение гистограмм в системе STATISTICA с помощью модуля Descriptive statistics.

      Статистика → Основная статистика/Таблицы → Descriptive statistics → OK. Далее выбираем вкладку Normality (рис. 1.15), которая предназначена для исследования возможности аппроксимации эмпирического закона распределения нормальным законом.

      Если установить флажок на Number of intervals, переменная воспринимается программой как непрерывная случайная величина и можно указать число интервалов разбиения диапазона ее изменения для построения гистограммы или Frequency tables (таблицы частот). При этом можно указать критерии соответствия эмпирического распределения нормальному закону (например, Kolmogorov-Smirnov & Liliefors test for normality).

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

      После выбора всех параметров, предоставляемых программой, нажимаем кнопку Histograms. На рис. 1.16 показана гистограмма для показателя уровня безработицы.

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

      Задание для самостоятельной работы

      1. Найти данные (в Интернете, журналах, статистических сборниках, справочниках). Используя средства Excel и STATISTICA, провести их группировку, графический анализ, вычисление и анализ описательных статистик.

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

      Задание для самостоятельного изучения

      Генератор случайных чисел

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

      Цели задания

      Знать –определения понятий «случайное число» и «псевдослучайное число».

      Уметь – генерировать последовательность случайных чисел на интервале [0;1) и осуществлять их отображение на произвольный интервал [a; b).

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

      Статистическое моделирование– построение математических имитаций случайных явлений или процессов. Это перспективное научное направление получило развитие в середине XX века в связи с ростом возможностей вычислительной техники и широко применяется для решения задач из различных областей человеческого знания. Например, расчеты систем массового обслуживания, расчеты качества и надежности изделий, задачи теории игр, задачи дискретной оптимизации, задачи финансовой математики, численное интегрирование, задачи динамики разреженного газа [1]. Появление методов статистического моделирования (Монте-Карло) в различных областях прикладной математики, как правило, связано с необходимостью решения качественно новых задач, возникающих из потребностей практики. Основа методов Монте-Карло – генератор случайных чисел (ГСЧ). О видах генераторов случайных чисел можно прочитать в [1,12].

      Согласно [4], случайными числами будем называть возможные значения xk равномерно распределенной случайной величины x, где 0 £ x

      Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте:

      Гистограмма распределения — это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и в ручную с помощью функции ЧАСТОТА() и диаграммы.

      Гистограмма (frequency histogram) – это столбиковая диаграмма MS EXCEL, в каждый столбик представляет собой интервал значений (корзину, карман, class interval, bin, cell), а его высота пропорциональна количеству значений в ней (частоте наблюдений).

      Гистограмма поможет визуально оценить распределение набора данных, если:

      • в наборе данных как минимум 50 значений;
      • ширина интервалов одинакова.

      Построим гистограмму для набора данных, в котором содержатся значения непрерывной случайной величины. Набор данных (50 значений), а также рассмотренные примеры, можно взять на листе Гистограмма AT в файле примера. Данные содержатся в диапазоне А8:А57.

      Примечание: Для удобства написания формул для диапазона А8:А57 создан Именованный диапазон Исходные_данные.

      Построение гистограммы с помощью надстройки Пакет анализа

      Вызвав диалоговое окно надстройки Пакет анализа, выберите пункт Гистограмма и нажмите ОК.

      В появившемся окне необходимо как минимум указать: входной интервал и левую верхнюю ячейку выходного интервала. После нажатия кнопки ОК будут:

      • автоматически рассчитаны интервалы значений (карманы);
      • подсчитано количество значений из указанного массива данных, попадающих в каждый интервал (построена таблица частот);
      • если поставлена галочка напротив пункта Вывод графика, то вместе с таблицей частот будет выведена гистограмма.


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

      Как видно из рисунка, первый интервал включает только одно минимальное значение 113 (точнее, включены все значения меньшие или равные минимальному). Если бы в массиве было 2 или более значения 113, то в первый интервал попало бы соответствующее количество чисел (2 или более).

      Второй интервал (отмечен на картинке серым) включает значения больше 113 и меньше или равные 216,428571428571. Можно проверить, что таких значений 11. Предпоследний интервал, от 630,142857142857 (не включая) до 733,571428571429 (включая) содержит 0 значений, т.к. в этом диапазоне значений нет. Последний интервал (со странным названием Еще) содержит значения больше 733,571428571429 (не включая). Таких значений всего одно — максимальное значение в массиве (837).

      Размеры карманов одинаковы и равны 103,428571428571. Это значение можно получить так:
      =(МАКС(Исходные_данные)-МИН(Исходные_данные))/7
      где Исходные_данные – именованный диапазон, содержащий наши данные.

      Почему 7? Дело в том, что количество интервалов гистограммы (карманов) зависит от количества данных и для его определения часто используется формула √n, где n – это количество данных в выборке. В нашем случае √n=√50=7,07 (всего 7 полноценных карманов, т.к. первый карман включает только значения равные минимальному).

      Примечание: Похоже, что инструмент Гистограмма для подсчета общего количества интервалов (с учетом первого) использует формулу
      =ЦЕЛОЕ(КОРЕНЬ(СЧЕТ(Исходные_данные)))+1

      Попробуйте, например, сравнить количество интервалов для диапазонов длиной 35 и 36 значений – оно будет отличаться на 1, а у 36 и 48 – будет одинаковым, т.к. функция ЦЕЛОЕ() округляет до ближайшего меньшего целого (ЦЕЛОЕ(КОРЕНЬ(35))=5 , а ЦЕЛОЕ(КОРЕНЬ(36))=6) .

      Если установить галочку напротив поля Парето (отсортированная гистограмма), то к таблице с частотами будет добавлена таблица с отсортированными по убыванию частотами.

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

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

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

      В результате получим практически такую же по форме гистограмму, что и раньше, но с более красивыми границами интервалов.

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

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

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

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

      СОВЕТ: Часто рекомендуют, чтобы границы интервала были на один порядок точнее самих данных и оканчивались на 5. Например, если данные в массиве определены с точностью до десятых: 1,2; 2,3; 5,0; 6,1; 2,1, …, то границы интервалов должны быть округлены до сотых: 1,25-1,35; 1,35-1,45; …
      Для небольших наборов данных вид гистограммы сильно зависит количества интервалов и их ширины. Это приводит к тому, что сам метод гистограмм, как инструмент описательной статистики, может быть применен только для наборов данных состоящих, как минимум, из 50, а лучше из 100 значений.

      В наших расчетах для определения количества интервалов мы будем пользоваться формулой =ЦЕЛОЕ(КОРЕНЬ(n))+1 .

      Примечание: Кроме использованного выше правила (число карманов = √n), используется ряд других эмпирических правил, например, правило Стёрджеса (Sturges): число карманов =1+log2(n). Это обусловлено тем, что например, для n=5000, количество интервалов по формуле √n будет равно 70, а правило Стёрджеса рекомендует более приемлемое количество — 13.

      Расчет ширины интервала и таблица интервалов приведены в файле примера на листе Гистограмма . Для вычисления количества значений, попадающих в каждый интервал, использована формула массива на основе функции ЧАСТОТА() . О вводе этой функции см. статью Функция ЧАСТОТА() — Подсчет ЧИСЛОвых значений в MS EXCEL.

      В MS EXCEL имеется диаграмма типа Гистограмма с группировкой, которая обычно используется для построения Гистограмм распределения.

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

      Примечание: О построении и настройке макета диаграмм см. статью Основы построения диаграмм в MS EXCEL.

      Одной из разновидностей гистограмм является график накопленной частоты (cumulative frequency plot).

      На этом графике каждый столбец представляет собой число значений исходного массива, меньших или равных правой границе соответствующего интервала. Это очень удобно, т.к., например, из графика сразу видно, что 90% значений (45 из 50) меньше чем 495.

      СОВЕТ : О построении двумерной гистограммы см. статью Двумерная гистограмма в MS EXCEL.

      Примечание: Альтернативой графику накопленной частоты может служить Кривая процентилей, которая рассмотрена в статье про Процентили.

      Примечание: Когда количество значений в выборке недостаточно для построения полноценной гистограммы может быть полезна Блочная диаграмма (иногда она называется Диаграмма размаха или Ящик с усами).