Оптимальное количество интервалов excel
Для определения оптимального количества интервалов может быть использована формула Стерджесса:
n = 1 + (3,322 × lgN )
где N — количество наблюдений. В этом случае величина интервала:
Поскольку количество групп не может быть дробным числом, то полученную по этой формуле величину округляют до целого большего числа.
Нижнюю границу первого интервала принимают равной минимальному значению x min . Верхняя граница первого интервала соответствует значению ( x min + h ). Для последующих групп
границы определяются аналогично, то есть последовательно прибавляется величина интервала h .
В Excel для построения гистограмм используются статистическая функция ЧАСТОТА в сочетании с мастером построения обычных диаграмм и процедура Гистограмма из пакета анализа .
Функция ЧАСТОТА (массив_данных, двоичный_массив) вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр, где
• Массив_данных — массив исходных данных, для которых вычисляются частоты;
• Массив_интервалов — это массив интервалов, по которым группируются значения выборки .
Перед вызовом функции ЧАСТОТА необходимо выделить столбец c числом ячеек, равным числу интервалов n , в который будут выведены результаты выполнения функции.
Вызвать Мастер функций (кнопка f x ):
и функцию ЧАСТОТА .
В поле Массив_данных ввести диапазон данных наблюдений А3:А102 (с листа ‘Расчетные данные’) . В поле Массив_интервалов ввести диапазон интервалов с того же листа ([‘Расчетные данные’!F16:F23] – в данном примере).
При завершении ввода данных нажать комбинацию клавиш Ctrl+Shift+Enter.
В предварительно выделенном столбце (C5:C12 – в данном примере) должен появиться массив
Столбец Накопленные частоты получается последовательным суммированием относительных частот (в процентном формате) в направлении от первого интервала к последнему.
В завершении с помощью Мастера диаграмм строится диаграмма абсолютных и накопленных частот с выбором типа диаграммы соотвественно гистограмма и график.
(Excel 2007. 2010):
В появившемся списке выбрать инструмент Гистограмма и щелкнуть на кнопке ОК. Появится окно гистограммы, где задаются следующие параметры:
Интервал карманов : (необязательный параметр) – адреса ячеек, содержащие границы интервалов. Это поле предлагается оставить пустым, предоставив Excel самому вычислить границы интервалов (карманов – в терминах Excel).
Метки – флажок, включаемый, если первая строка во входных данных содержит заголовки. Если заголовки отсутствуют, то флажок следует выключить.
Выходной интервал: / Новый рабочий лист: / Новая рабочая книга.
Включенный переключатель Выходной интервал требует ввода адреса верхней ячейки, начиная с которой будут размещаться вычисленные относительные частоты j .
В положении переключателя Новый рабочий лист: открывается новый лист, в котором начиная с ячейки А1 размещаются частности j .
В положении переключателя Новая рабочая книга открывается новая книга, на первом листе которой начиная с ячейки А1 размещаются частности j .
Парето ( отсортированная гистограмма ) – устанавливается, чтобы представить j в порядке их убывания. Если параметр выключен, то j приводятся в порядке следования интервалов.
Интегральный процент – устанавливается в активное состояние для расчета выраженных в процентах накопленных относительных частот (аналог значений столбца Накопленные частоты ).
Вывод графика – устанавливается в активное состояние для автоматического создания встроенной диаграммы на листе, содержащем частоты.
Как правило, гистограммы изображаются в виде смежных прямоугольных областей. Поэтому столбики гистограммы следует расширить до соприкосновения друг с другом. Для этого необходимо щелкнуть мышью на диаграмме, далее на панель инструментов Диаграмма , раскрыть список инструментов и выбрать элемент Ряд ‘Частота’ , после чего щелкнуть на кнопке Формат ряда . В появившемся одноименном диалоговом окне необходимо активизировать закладку Параметры и в поле Ширина зазора установить значение 0 ((Excel 2003):
В Excel 2007. 2010 встать на любой столбик гистограммы и правой кнопкой мыши выбрать
Формат ряда данных:
Для построения теоретической кривой нормального распределения по эмпирическим данным необходимо найти теоретические частоты.
В Excel для вычисления значений нормального распределения используются функция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения.
Функция имеет параметры:
НОРМРАСП (х; среднее; стандартное_откл; интегральная) , где:
х — значения выборки, для которых строится распределение; среднее — среднее арифметическое выборки; стандартное_откл — стандартное отклонение распределения;
интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.
Для получения абсолютных значений плотностей распределения (теоретических частот) достаточно найденные значения вероятности умножить на величину интервала h и количество наблюдений N = 100 по каждой строке.
Для завершения выполнения задания необходимо внести полученные значения теоретических частот на рисунок с гистограммой, добавив ряд в закладке Исходные данные и выбрав тип диаграммы
– график ((Excel 2003):
В Excel 2007. 2010 находясь в обласи гистограммы по правой кнопке мыши выбрать Выбрать данные (или по одноименной кнопке на вкладке Конструктор ):
и в появившемся окне провести манипуляции с вводом нового ряда «Теоретические частоты»:
Функция ЧАСТОТА( ) , английская версия FREQUENCY() , вычисляет частоту попадания значений в заданные пользователем интервалы и возвращает соответствующий массив чисел.
Функцией ЧАСТОТА() можно воспользоваться, например, для подсчета количества результатов тестирования, попадающих в определенные интервалы (См. Файл примера )
Синтаксис функции
ЧАСТОТА ( массив_данных ; массив_интервалов )
Массив_данных — массив или ссылка на множество ЧИСЛОвых данных, для которых вычисляются частоты.
Массив_интервалов — массив или ссылка на множество интервалов, в которые группируются значения аргумента «массив_данных».
Функция ЧАСТОТА() вводится как формула массива после выделения диапазона смежных ячеек, в которые требуется вернуть полученный массив распределения (частот). Т.е. после ввода формулы необходимо вместо нажатия клавиши ENTER нажать сочетание клавиш CTRL+SHIFT+ENTER .
Количество элементов в возвращаемом массиве на единицу больше числа элементов в массиве « массив_интервалов ». Дополнительный элемент в возвращаемом массиве содержит количество значений, превышающих верхнюю границу интервала, содержащего наибольшие значения (см. пример ниже).
Пример
Пусть в диапазоне А2:А101 имеется исходный массив чисел от 1 до 100.
Подсчитаем количество чисел, попадающих в интервалы 1-10; 11-20; . 91-100.
Сформируем столбце С массив верхних границ диапазонов (интервалов). Для наглядности в столбце D сформируем текстовые значения соответствующие границам интервалов (1-10; 11-20; . 91-100).
Для ввода формулы выделим диапазон Е2:Е12 , состоящий из 11 ячеек (на 1 больше, чем число верхних границ интервалов). В Строке формул введем =ЧАСТОТА($A$2:$A$101;$C$2:$C$11) . После ввода формулы необходимо нажать сочетание клавиш CTRL+SHIFT+ENTER . Диапазон Е2:Е12 заполнится значениями:
Примечание . Функцию ЧАСТОТА() можно заменить формулой = СУММПРОИЗВ(($A$5:$A$104>C5)*($A$5:$A$104 (См. Файл примера )
Разделы: Математика
- Совершенствование умений и навыков нахождения статистических характеристик случайной величины, работа с расчетами в Excel;
- применение информационно коммутативных технологий для анализа данных; работа с различными информационными носителями.
- Сегодня на уроке мы научимся рассчитывать статистические характеристики для больших по объему выборок, используя возможности современных компьютерных технологий.
- Для начала вспомним:
– что называется случайной величиной? (Случайной величиной называют переменную величину, которая в зависимости от исхода испытания принимает одно значение из множества возможных значений.)
– Какие виды случайных величин мы знаем? (Дискретные, непрерывные.)
– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).
– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).
– Какие приемы используются для наглядного представления статистических характеристик случайной величины (полигон частот, круговые и столбчатые диаграммы, гистограммы).
- Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.
Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):
- моду
- медиану
- размах ряда
- построить полигон частот
- построить столбчатую и круговую диаграммы
- раскрыть смысловую сторону каждой характеристики
1. Занести данные в EXCEL, каждое число в отдельную ячейку.
| 23 | 25 | 24 | 25 | 30 | 24 | 30 | 26 | 28 | 26 |
| 32 | 33 | 31 | 31 | 25 | 33 | 25 | 29 | 30 | 28 |
| 23 | 30 | 29 | 24 | 33 | 30 | 30 | 28 | 26 | 25 |
| 26 | 29 | 27 | 29 | 26 | 28 | 27 | 26 | 29 | 28 |
| 29 | 30 | 27 | 30 | 28 | 32 | 28 | 26 | 30 | 26 |
| 31 | 27 | 30 | 27 | 33 | 28 | 26 | 30 | 31 | 29 |
| 27 | 30 | 30 | 29 | 27 | 26 | 28 | 31 | 29 | 28 |
| 33 | 27 | 30 | 33 | 26 | 31 | 34 | 28 | 32 | 22 |
| 29 | 30 | 27 | 29 | 34 | 29 | 32 | 29 | 29 | 30 |
| 29 | 29 | 36 | 29 | 29 | 34 | 23 | 28 | 24 | 28 |
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.
Используя тот же путь вычисляем медиану.
Вставка – Функция – Статистические – Медиана.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.
Размах ряда чисел – разница между наименьшим и наибольшим возможным значением случайной величины. Для вычисления размаха ряда нужно найти наибольшее и наименьшее значения нашей выборки и вычислить их разность.
Вставка – Функция – Статистические – МАКС.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наибольшее значение = 36.
Вставка – Функция – Статистические – МИН.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наименьшее значение = 22.
36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.
Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni |
Чтобы сосчитать частоту каждого значения воспользуемся
Вставка – Функция – Статистические – СЧЕТЕСЛИ.
В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22
Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni | 1 | 3 | 4 | 5 | 11 | 9 | 13 | 18 | 16 | 6 | 4 | 6 | 3 | 1 |
Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)
Нажимаем клавишу Далее, в Мастере диаграмм указываем название диаграммы (Полигон частот), удаляем легенду, редактируем шкалу и характеристики диаграммы для наибольшей наглядности.
Для построения столбчатой и круговой диаграмм используем тот же путь (выбирая нужный нам тип диаграммы).
Диаграмма – Стандартные – Круговая.
Диаграмма – Стандартные – Гистограмма.
4. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.
Гистограмма распределения - это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств 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 .
Примечание : Альтернативой графику накопленной частоты может служить Кривая процентилей , которая рассмотрена в статье про Процентили .
Примечание : Когда количество значений в выборке недостаточно для построения полноценной гистограммы может быть полезна Блочная диаграмма (иногда она называется Диаграмма размаха или Ящик с усами ).
Программа Эксель используется для выполнения различных статистических задач, одной из которых является вычисление доверительного интервала, который применяется как наиболее подходящая замена точечной оценки при малом объеме выборки.
Хотим сразу заметить, что сама процедура вычисления доверительного интервала довольно непростая, однако, в Excel существует ряд инструментов, призванных облегчить выполнение данной задачи. Давайте рассмотрим их.
Вычисление доверительного интервала
Доверительный интервал нужен для того, чтобы дать интервальную оценку каким-либо статическим данным. Основная цель этой операции – убрать неопределенности точечной оценки.
В Microsoft Excel существует два метода выполнения данной задачи:
- Оператор ДОВЕРИТ.НОРМ – применяется в случаях, когда дисперсия известна;
- Оператор ДОВЕРИТ.СТЬЮДЕНТ– когда дисперсия неизвестна.
Ниже мы пошагово разберем оба метода на практике.
Метод 1: оператора ДОВЕРИТ.НОРМ
Данная функция впервые была внедрена в арсенал программы в редакции Эксель 2010 года (до этой версии ее заменял оператор “ДОВЕРИТ”). Оператор входит в категорию “статистические”.
Формула функции ДОВЕРИТ.НОРМ выглядит так:
Как мы видим, у функции есть три аргумента:
- “Альфа” – это показатель уровня значимости, который берется за основу при расчете. Доверительный уровень считается так:
- 1-"Альфа" . Это выражение применимо в случае, если значение “Альфа” представлено в виде коэффициента. Например, 1-0,7=0,3, где 0,7=70%/100%.
- (100-"Альфа")/100 . Применятся это выражение, если мы считаем доверительным уровень со значением “Альфа” в процентах. Например, (100-70)/100=0,3.
Примечание: У данной функции наличие всех трех аргументов является обязательным условием.
Оператор “ДОВЕРИТ”, который применялся в более ранних редакциях программы, содержит такие же аргументы и выполняет те же самые функции.
Формула функции ДОВЕРИТ выглядит следующим образом:
Отличий в самой формуле нет никаких, лишь название оператора иное. В редакциях приложения Эксель 2010 года и последующих этот оператор находится в категории “Совместимость”. В более же старых версиях программы он находится в разделе статических функций.
Граница доверительного интервала определяется следующей формулой:
где Х – это среднее значение по заданному диапазону.
Теперь давайте разберемся, как применять эти формулы на практике. Итак, у нас есть таблица с различными данными 10-ти проведенных замеров. При этом, стандартное отклонение совокупности данных равняется 8.
![Таблица с данными]()
Перед нами стоит задача – получить значение доверительного интервала с 95%-ым уровнем доверия.
![Аргументы функции ДОВЕРИТ.НОРМ]()
- в поле “Альфа” указываем уровень значимости. В нашей задаче предполагается 95%-ый уровень доверия. Подставив данное значение в формулу расчета, которую мы рассматривали выше, получаем выражение: (100-95)/100 . Пишем его в поле аргумента (или можно сразу написать результат вычисления, равный 0,05).
- в поле “Станд_откл” согласно нашим условия, пишем цифру 8.
- в поле “Размер” указываем количество исследуемых элементов. В нашем случае было проведено 10 замеров, значит пишем цифру 10.
Примечание: В пунктах выше мы постарались максимально подробно расписать все шаги и каждую применяемую функцию. Однако все прописанные формулы можно записать вместе, в составе одной большой:
- Для определения правой границы ДИ общая формула будет выглядеть так:
=СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)) . - Точно также и для левой границы, только вместо плюса нужно поставить минус:
=СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)) .
Метод 2: оператор ДОВЕРИТ.СТЬЮДЕНТ
Теперь давайте познакомимся со вторым оператором для определения доверительного интервала – ДОВЕРИТ.СТЬЮДЕНТ. Данная функция была внедрена в программу относительно недавно, начиная с версии Эксель 2010, и направлена на определение ДИ выбранной совокупности данных с применением распределения Стьюдента, при неизвестной дисперсии.
Формула функции ДОВЕРИТ.СТЬЮДЕНТ выглядит следующим образом:
Давайте разберем применение данного оператора на примере все той же таблицы. Только теперь стандартное отклонение по условиям задачи нам неизвестно.
- Сначала выбираем ячейку, куда планируем вывести результат. Затем кликаем по значку “Вставить функцию” (слева от строки формул).
- Откроется уже хорошо знакомое окно Мастера функций. Выбираем категорию “Статистические”, затем из предложенного списка функций щелкаем по оператору “ДОВЕРИТ.СТЬЮДЕНТ”, после чего – OK.
- В следующем окне нам нужно настроить аргументы функции:.
- В поле “Альфа” как и в первом методе указываем значение 0,05 (или “100-95)/100”).
- Переходим к аргументу “Станд_откл”. Т.к. по условиям задачи его значение нам неизвестно, нужно произвести соответствующие расчеты, в чем нам поможет оператор “СТАНДОТКЛОН.В”. Щелкаем по кнопке добавления функции и затем – по пункту “Другие функции…”.
- В очередном окне Мастера функций выбираем оператор “СТАНДОТКЛОН.В” в категории “Статистические” и кликаем OK.
- Мы попадаем в окно настройки аргументов функции, формула которой выглядит так: =СТАНДОТКЛОН.В(число1;число2;. ) . В качестве первого аргумента указываем диапазон, включающий все ячейки столбца “Значение” (не считая шапки).
- Теперь нужно вернуться обратно в окно с аргументами функции “ДОВЕРИТ.СТЬЮДЕНТ”. Для этого щелкаем по одноименной надписи в поле ввода формул.
- Теперь переходим к последнему аргументу “Размер”. Как и в первом методе, здесь можно либо просто указать диапазон ячеек, либо вставить оператор “СЧЕТ”. Выбираем последний вариант.
- Как только все аргументы заполнены, жмем кнопку OK.
- В выбранной ячейке отобразится значение доверительного интервала согласно заданным нами параметрам.
- Далее нам нужно рассчитать значения границ ДИ. А для этого потребуется получить среднее значение по выбранному диапазону. Для этого снова применим функцию “СРЗНАЧ”. Алгоритм действий аналогичен тому, что был описан в первом методе.
- Получив значение “СРЗНАЧ”, можно приступать к расчетам границ ДИ. Сами формулы ничем не отличаются от тех, что использовались с оператором “ДОВЕРИТ.НОРМ”:
- Правая граница ДИ=СРЗНАЧ+ДОВЕРИТ.СТЬЮДЕНТ
- Левая граница ДИ=СРЗНАЧ-ДОВЕРИТ.СТЬЮДЕНТ
Заключение
Арсенал инструментов Excel невероятно большой, и наряду с распространенными функциями, программа предлагает большое разнообразие специальных функций, которые помогут существенно облегчить работу с данными. Возможно, описанные выше шаги некоторым пользователям, на первый взгляд, могут показаться сложными. Но после детального изучения вопроса и последовательности действий, все станет намного проще.
Гистограмма – очень полезный инструмент, с помощью которого можно сделать демонстрируемую информацию более наглядной и простой для восприятия. Это особенно актуально, когда время на изучение данных в таблице ограничено, и нужно в сжатые сроки сформировать целостное представление о них. Давайте посмотрим, каким образом можно построить гистограмму в Эксель.
Строим гистограмму
Для выполнения поставленной задачи в Excel можно воспользоваться разными методами:
- использовать инструменты на ленте программы;
- воспользоваться надстройкой “Пакет анализа”;
- применить условное форматирование.
Ниже мы подробнее остановимся на каждом из этих пунктов.
Метод 1: используем инструменты на ленте программы
Это, пожалуй, самый простой способ. И вот, как он реализуется:
![Выделение ячеек таблицы Эксель]()
-
Открываем (или создаем) таблицу. Выделяем любым удобным способом (например, с помощью зажатой левой кнопки мыши) ячейки, на базе которых планируется построить диаграмму.
![Самая верхняя левая ячейка таблицы Эксель]()
Гистограмма с накоплением
До того, как приступить к созданию гистограммы с накоплением, проверяем, чтобы самая верхняя левая ячейка таблицы была пустой.
Затем делаем следующее:Примечание: в гистограммах с накоплением один столбец содержит сразу несколько значений. В нашем случае – это данные по всем четырем торговым точкам за конкретную дату.
Нормированная гистограмма с накоплением
В данном случае отображается (в процентном выражении) вклад каждого значения в общем количестве.![Нормированная гистограмма с накоплением в Эксель]()
Метод 2: пользуемся надстройкой “Пакета “анализа”
Для начала нужно включить данный пакет.
Метод 3: выполняем условное форматирование с гистограммой
Получить гистограмму можно и с помощью условного форматирования ячеек. План действий следующий:
![Выбор гистограммы в вариантах условного форматирования в Excel]()
- Для начала нужно выделить элементы, которые нужны для гистограммы.
- Находясь в главной вкладке в группе “Стили” щелкаем по кнопке “Условное форматирование”. Откроется список, в котором выбираем “Гистограмму”. Раскроется еще один перечень, где нужно определиться с вариантами заливки – градиентная или сплошная.
Редактирование гистограммы
Вставленную на лист гистограмму можно изменить:
- Корректируем или удаляем название. Для активации режима редактирования щелкаем по названию, после чего вокруг него появится рамка. Теперь кликаем в любом месте внутри рамки и мы можем приступать к редактированию.
- Удаляем легенду (если она не нужна). Предварительно нужно ее выбрать. Первым кликом выделяем все легенды (если их несколько), вторым – конкретную легенду. Удалить легенду можно через контекстное меню, вызываемое щелчком правой кнопки мыши по ней или просто нажав клавишу Del на клавиатуре.
- Меняем положение названия гистограммы и легенды. Для этого кликаем по выбранному объекту, чтобы выделить его. Затем наводим указатель мыши на любую из появившихся границ, и когда появится характерный символ в виде стрелок в четыре стороны, зажав левую кнопку мыши перетаскиваем объект в новое место.
- Меняем размер и положение гистограммы. Чтобы изменить размер гистограммы, зажав левую кнопку мыши тянем за одну из точек, расположенных на рамке вокруг нее.Для изменения положения выполняем те же самые действия, что и в отношении названия или легенды.
- Для более детальных настроек щелкаем по любой свободной части в области диаграммы (белые участки) и в открывшемся контекстном меню выбираем пункт “Формат области диаграммы”.Появится вспомогательное окно, в котором мы можем настроить:
- заливку и границы;
- добавить различные эффекты;
- размеры и свойства;
- параметры текста.
- Настраиваем шкалы по осям X и Y. Щелкаем по подписям выбранной шкалы правой кнопкой мыши и в открывшемся контекстном меню выбираем “Формат оси”.Откроется окно, в котором мы можем выполнить соответствующие настройки.
Помимо вышеперечисленного, на ленте программы во вкладке “Конструктор”, в которой мы окажемся автоматическим после создания гистограммы, мы можем сменить стиль, выбрать один из экспресс-макетов и т.д.
![Настройка диаграммы во вкладке Конструктор в Эксель]()
Также, мы можем добавить новый элемент диаграммы, щелкнув на соответствующий значок в левом верхнем углу ленты и выбрав из списка нужный пункт.
![Добавление нового элемента диаграммы в Эксель]()
Примечание: возможных настроек и параметров, которые можно задать для гистограммы достаточно много, и каждый пользователь сможет подобрать для себя оптимальный вариант. Мы описали лишь часть основных действий и инструментов, которые могут помочь в этом.
Заключение
Программа Excel позволяет построить различные гистограммы, которые помогают лучше и быстрее освоить информацию, особенно, когда ее слишком много, а времени, наоборот, мало. Выбор конкретного вида и метода построения диаграммы зависит от того, какие цели преследует пользователь, и каким он видит конечный результат.
Читайте также:
