Нормальное распределение. Построение графика в Excel. Концепция шести сигм
Наверное, не все знают, что в Excel есть встроенная функция для построения нормального распределения. Графики нормального распределения часто используются для демонстрации идей статистической обработки данных.
Функция НОРМРАСП имеет следующий синтаксис:
НОРМРАСП (Х; среднее; стандартное_откл; интегральная)
Х — аргумент функции; фактически НОРМРАСП можно трактовать как y=f(x); при этом функция возвращает вероятность реализации события Х
Среднее (µ) — среднее арифметическое распределения; чем дальше Х от среднего, тем ниже вероятность реализации такого события
Стандартное_откл (σ) — стандартное отклонение распределения; мера кучности; чем меньше σ, тем выше вероятность у тех Х, которые расположены ближе к среднему
Интегральная — логическое значение, определяющее форму функции. Если «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения, тот есть суммарную вероятность всех событий для аргументов от -∞ до Х; если «интегральная» имеет значение ЛОЖЬ, возвращается вероятность реализации события Х, точнее говоря, вероятность событий находящихся в некотором диапазоне вокруг Х
Например, для µ=0 имеем:
Скачать заметку в формате Word, пример в формате Excel
Здесь по оси абсцисс единица измерения – σ, или (что то же самое), можно сказать, что график построен для σ = 1. То есть, «-2» на графике означает -2σ. По оси ординат шкала убрана умышленно, так как она лишена смысла. Точнее говоря, высота кривой зависит от плотности точек на оси абсцисс, по которым мы строим график. Например, если на интервал от 0 до 1σ приходится 10 точек, то высота в максимуме составит 4%, а если 20 точек – 2%. Здесь проценты означают вероятность попадания случайной величины в узкий диапазон окрестности точки на оси абсцисс. Зато имеет смысл площадь под кривой на определенном интервале. И эта площадь не зависит от плотности точек. Так, например, площадь под кривой на интервале от 0 до 1σ составляет 34,13%. Это значение можно интерпретировать следующим образом: с вероятностью 68,26% случайная величина Х попадет в диапазон µ ± σ.
Теперь, наверное, вам будет лучше понятен смысл выражения «качество шести сигм». Оно означает, что производство налажено таким образом, что случайная величина Х (например, диаметр вала) находясь в диапазон µ ± 6σ, всё еще удовлетворяет техническим условиям (допускам). Это достигается за счет значительного уменьшения сигмы, то есть случайная величина Х очень близка к нормативному значению µ. На графике ниже представлено три ситуации, когда границы допуска остаются неизменными, а благодаря повышению качества (уменьшению вариабельности, сужению сигма) доля брака сокращается:
На первом рисунке только 1,5σ попадают в границы допуска, то есть только 86,6% деталей являются годными. На втором рисунке уже 3σ попадают в границы допуска, то есть 99,75% являются годными. Но всё еще 25 деталей из каждых 10 000 произведенных являются браком. На третьем рисунке целых 6σ попадают в границы допуска, то есть в брак попадут только две детали на миллиард изготовленных!
Вообще-то говоря, измерение качества в терминах сигм использует не совсем нормальное распределение. Вот что пишет на эту тему Википедия:
Опыт показывает, что показатели процессов имеют тенденцию изменяться с течением времени. В результате со временем в промежуток между границами поля допуска будет входить меньше, чем было установлено первоначально. Опытным путём было установлено, что изменение параметров во времени можно учесть с помощью смещения в 1,5 сигма. Другими словами, с течением времени длина промежутка между границами поля допуска под кривой нормального распределения уменьшается до 4,5 сигма вследствие того, что среднее процесса с течением времени смещается и/или среднеквадратическое отклонение увеличивается.
Широко распространённое представление о «процессе шесть сигма» заключается в том, что такой процесс позволяет получить уровень качества 3,4 дефектных единиц на миллион готовых изделий при условии, что длина под кривой слева или справа от среднего будет соответствовать 4,5 сигма (без учёта левого или правого конца кривой за границей поля допуска). Таким образом, уровень качества 3,4 дефектных единиц на миллион готовых изделий соответствует длине промежутка 4,5 сигма, получаемых разницей между 6 сигма и сдвигом в 1,5 сигма, которое было введено, чтобы учесть изменение показателей с течением времени. Такая поправка создана для того, чтобы предупредить неправильною оценку уровня дефектности, встречающееся в реальных условиях.
С моей точки зрения, не вполне внятное объяснение. Тем не менее, во всем мире принята следующая таблица соответствия числа дефектов и уровня качества в сигмах:
Нормальное распределение в статистике
История закона насчитывает 300 лет. Первым открывателем стал Абрахам де Муавр, который придумал аппроксимацию биномиального распределения еще 1733 году. Через много лет Карл Фридрих Гаусс (1809 г.) и Пьер-Симон Лаплас (1812 г.) вывели математические функции.
Лаплас также обнаружил замечательную закономерность и сформулировал центральную предельную теорему (ЦПТ), согласно которой сумма большого количества малых и независимых величин имеет нормальное распределение.
Нормальный закон не является фиксированным уравнением зависимости одной переменной от другой. Фиксируется только характер этой зависимости. Конкретная форма распределения задается специальными параметрами. Например, у = аx + b – это уравнение прямой. Однако где конкретно она проходит и под каким наклоном, определяется параметрами а и b. Также и с нормальным распределением. Ясно, что это функция, которая описывает тенденцию высокой концентрации значений около центра, но ее точная форма задается специальными параметрами.
Кривая нормального распределения Гаусса имеет следующий вид.
График нормального распределения напоминает колокол, поэтому можно встретить название колоколообразная кривая. У графика имеется «горб» в середине и резкое снижение плотности по краям. В этом заключается суть нормального распределения. Вероятность того, что случайная величина окажется около центра гораздо выше, чем то, что она сильно отклонится от середины.
На рисунке выше изображены два участка под кривой Гаусса: синий и зеленый. Основания, т.е. интервалы, у обоих участков равны. Но заметно отличаются высоты. Синий участок удален от центра, и имеет существенно меньшую высоту, чем зеленый, который находится в самом центре распределения. Следовательно, отличаются и площади, то бишь вероятности попадания в обозначенные интервалы.
Формула нормального распределения (плотности) следующая.
Формула состоит из двух математических констант:
π – число пи 3,142;
е – основание натурального логарифма 2,718;
двух изменяемых параметров, которые задают форму конкретной кривой:
m – математическое ожидание (в различных источниках могут использоваться другие обозначения, например, µ или a);
σ2 – дисперсия;
ну и сама переменная x, для которой высчитывается плотность вероятности.
Конкретная форма нормального распределения зависит от 2-х параметров: математического ожидания (m) и дисперсии (σ2). Кратко обозначается N(m, σ2) или N(m, σ). Параметр m (матожидание) определяет центр распределения, которому соответствует максимальная высота графика. Дисперсия σ2 характеризует размах вариации, то есть «размазанность» данных.
Параметр математического ожидания смещает центр распределения вправо или влево, не влияя на саму форму кривой плотности.
А вот дисперсия определяет остроконечность кривой. Когда данные имеют малый разброс, то вся их масса концентрируется у центра. Если же у данных большой разброс, то они «размазываются» по широкому диапазону.
Плотность распределения не имеет прямого практического применения. Для расчета вероятностей нужно проинтегрировать функцию плотности.
Вероятность того, что случайная величина окажется меньше некоторого значения x, определяется функцией нормального распределения:
P(a ≤ X < b) = Ф(b) – Ф(a)
Понимание простой случайной выборки
Исследователи могут создать простую случайную выборку, используя несколько методов. При использовании метода лотереи каждому члену населения присваивается номер, после чего номера выбираются случайным образом.
Пример, в котором имена 25 сотрудников из 250 выбраны совершенно неожиданно, является примером действующего метода лотереи. Каждому из 250 сотрудников будет присвоен номер от 1 до 250, после чего 25 из этих номеров будут выбраны случайным образом.
Поскольку люди, составляющие подмножество большей группы, выбираются случайным образом, каждый человек в большой группе населения имеет одинаковую вероятность быть выбранным. В большинстве случаев это создает сбалансированное подмножество, несущее наибольший потенциал для представления большей группы в целом, без каких-либо предубеждений.
Для больших групп населения ручной метод лотереи может быть довольно обременительным. Для выбора случайной выборки из большой совокупности обычно требуется компьютерный процесс, в котором используется та же методология, что и в методе лотереи, только присвоение номеров и последующий выбор выполняется компьютерами, а не людьми.
Случайные выборки
Они предполагают, что в выборке каждый элемент генеральной совокупности имеет заранее заданную вероятность быть отобранным в исследование.
Простая случайная выборка. Сначала нужно присвоить каждому потенциальному респонденту идентификационный номер. Дальше с помощью генератора случайных чисел определить номера, которые будут включены в выборку для опроса.
Механическая выборка. Как и в простой выборке пользователям присваивается порядковый номер. Только отбор происходит не с помощью генератора случайных чисел, а с шагом равным n. Например, каждый сотый.
Стратифицированная выборка. Для такой выборки нужно поделить генеральную совокупность на сегменты или страты. После чего респонденты внутри каждой группы отбираются случайным образом. Из каждого сегмента выделяют пользователей пропорционально их доле в генеральной совокупности.
Кластерный отбор или гнездовая выборка. Группа потенциальных респондентов отбирается случайным образом из всей генеральной совокупности. Далее внутри этой группы опрашиваются все пользователи. Например, можно опросить всех пользователей, которые зарегистрировались в сервисе в прошлом квартале.
При таком отборе риск искажений выше и важно учитывать внешние и внутренние факторы. Может быть в прошлом квартале в жизни пользователей произошло что-то важное, что повлияло на их желание воспользоваться сервисом
Тогда эта группа будет сильно отличаться от генеральной совокупности.
Функциональная генерация случайных чисел в Excel и Google Таблицах
Генерация случайных чисел в Excel реализуется очень просто. Функция применима как к приложению из офисного пакета от Microsoft, так и к электронным таблицам от Google.
При этом, у пользователя не возникает необходимости в установке каких-либо дополнительных модулей. Функция работает внутри вашего документа по умолчанию.
Стоит заметить, что при работе с онлайн-приложением Google Таблицы, дополнительно будет допустимо указать и интервал обновления расчета. На выбор есть 3 варианта:
- при внесении изменений (по умолчанию);
- при изменениях и каждую минуту;
- при изменениях и каждый час.
Давайте начнем. Запустите приложение и создайте новый документ. Далее установите курсор в ячейку таблицы, которая должна генерировать число. На этом шаге подготовка закончена.
Что такое Простая случайная выборка?
Простая случайная выборка — это подмножество статистической совокупности, в которой каждый член подмножества имеет равную вероятность быть выбранным. Простая случайная выборка предназначена для беспристрастного представления группы.
Примером простой случайной выборки могут быть имена 25 сотрудников, выбранных на пустом месте из компании с 250 сотрудниками. В этом случае совокупность состоит из 250 сотрудников, и выборка является случайной, потому что у каждого сотрудника есть равные шансы быть выбранным. Случайная выборка используется в науке для проведения рандомизированных контрольных тестов или слепых экспериментов.
Краткая справка
Нет более простого метода извлечения исследовательской выборки из более широкой совокупности, чем простая случайная выборка. Полностью случайный выбор субъектов из большей совокупности также дает выборку, которая является репрезентативной для изучаемой группы.
Статистическое распределение выборки. Полигон. Гистограмма
Пусть из генеральной совокупности извлечена выборка, причем , наблюдалось раз, раз, раз и объем выборки. Наблюдаемые значения называются вариантами, а последовательность вариант, записанная в возрастающем порядке,— вариационным рядом. Числа наблюдений называют частотами, а их отношения к объему выборки — относительными частотами. Отметим, что сумма относительных частот равна единице:
Статистическим распределением выборки называют перечень вариант и соответствующих им частот или относительных частот. Статистическое распределение можно задать также в виде последовательности интервалов и соответствующих им частот (непрерывное распределение). В качестве частоты, соответствующей интервалу, принимают сумму частот вариант, попавших в этот интервал.
Заметим, что в теории вероятностей под распределением понимают соответствие между возможными значениями случайной величины и их вероятностями, а в математической статистике — соответствие между наблюдаемыми вариантами и их частотами или относительными частотами.
Пример:
Перейдем от частот к относительным частотам в следующем распределении выборки объема n = 20:
Найдем относительные частоты:
Поэтому получаем следующее распределение:
Для графического изображения статистического распределения используются полигоны и гистограммы.
Для построения полигона в декартовых координатах на оси Ох откладывают значения вариант на оси Оу— значения частот (относительных частот ).
Пример:
Рис. 14 представляет собой полигон следующего распределения:
Полигоном обычно пользуются в случае небольшого количества вариант. В случае большого количества вариант и в случае непрерывного распределения признака чаще строят гистограммы. Для этого интервал, в котором заключены все наблюдаемые значения признака, разбивают на несколько частичных интервалов шириной h и находят для каждого частичного интервала — сумму частот вариант, попавших в і-й интервал. Затем на этих интервалах как на основаниях строят прямоугольники с высотами (или , где n —объем выборки). Площадь i-го частичного прямоугольника равна
(или ). Следовательно, площадь гистограммы равна сумме всех частот (или относительных частот), т. е. объему выборки (или единице).
Пример:
Рис. 15 показывает гистограмму непрерывного распределения объема n =100, заданного следующей таблицей:
Модельное распределение
С помощью надстройки Пакет Анализа можно сгенерировать числа, имеющее так называемое модельное распределение. В этом распределении нет никакой случайности — генерируются заранее заданные последовательности чисел.
Поле Число переменных задает количество столбцов генерируемых данных. Т.к. в них будут сгенерированы совершенно одинаковые данные, то нет особого смысла указывать в поле Число переменных значение отличное от 1.
Поле Число случайных чисел можно оставить незаполненным, т.к. оно рассчитывается автоматически в зависимости от значений, указанных в группе Параметры диалогового окна. Например, при параметрах, указанных на рисунке выше, в каждом столбце будет выведено по 24 «случайных» числа: четыре нечетных числа 1; 3; 5; 7 (от 1 и до 7; шаг равен 2) будут повторены по 2 раза, а каждая последовательность будет повторена по 3 раза (4*2*3=24).
Выборка данных в Excel (как сделать из таблицы массива по условию)
Первый способ: Применение расширенного автофильтра
На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «Главная» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр.
Можно сделать иначе, после выделения области переходите во вкладку «Данные» и нажимаете на «Фильтр», размещенной в группе «Сортировка и фильтр».
Когда эти действия выполнены, в шапке таблицы должны появиться пиктограммы для запуска фильтрования. Они будут отображены острием вниз небольшими треугольниками в правом крае ячеек. Нажимаете на этот значок в начале того столбца, по которому и собираетесь сделать выборку. Запуститься меню, в нем переходите в «Текстовые фильтры» и выбираете «Настраиваемый фильтр…».
Теперь должно активироваться окно пользовательской фильтрации. В нем задаете ограничение, по которому и будет осуществляться отбор. Можно выбрать одно из пяти предложенных видов условий: равно, не равно, больше, больше или равно, меньше.
После фильтрации остаются только те строчки, в которых сумма выручки превышает значение 10000 (как пример).
В этом же столбце есть возможность добавить и второе условие. Нужно снова вернуться в окно пользовательской фильтрации, а в его нижней части установить другую границу отбора. Переключатель выставляете в позицию «Меньше», а в поле справа вписываете «15000».
В таблице останутся только те строки, в которых сумма выручки не меньше 10000, но и не больше 15000.
В других столбцах выборка настраивается по аналогии. В нужном столбце нажимаете по значку фильтрации, а дальше последовательно кликаете по пунктам списка «Фильтр по дате» и «Настраиваемый фильтр».
Должно запуститься окно пользовательского автофильтра. Выполните, к примеру, отбор результатов в таблице с 4 по 6 мая 2016 года включительно. Нажимаете «После или равно», а в поле справа выставляете значение «04.05.2016». В нижнем блоке переключатель ставите в позицию «До или равно», а в правом поле вносите «06.05.2016». Переключатель совместимости условий оставляете в положении по умолчанию, то есть «И». Для применения фильтрации кликаете на ОК.
Список теперь должен сократиться еще больше, потому что останутся только строки, в которых сумма выручки варьируется от 10000 до 15000 и это за период с 04.05 по 06.05.2016 включительно.
В одном из столбцов при желании можно сбросить фильтрацию. К примеру, можно сделать это для значений выручки. Нажимаете на значок автофильтра в соответствующем столбце. Выбираете «Удалить фильтр».
Выборка по сумме выручки отключится и останется только отбор по датам (с 04.05.2016 по 06.05.2016).
В таблице есть и еще одна колонка под названием «Наименование». В ней расположенные данные в текстовом формате. По этим значениям тоже можно сформировать выборку. В наименовании столбца нажмите на значок фильтра. Переходите на «Текстовые фильтры», а затем «Настраиваемый фильтр…».
Снова откроется окно пользовательского фильтра, в котором можно сделать выборку, к примеру, по наименованиям «Мясо» и «Картофель». В первом блоке нужно установить переключатель в позиции «Равно» а в поле справа от него внести «Картофель». Переключатель нижнего блока поставить в позицию «Равно», а в поле напротив – «Мясо». Теперь следует установить переключатель совместимости условий в позиции «ИЛИ». Нажимаете ОК.
В новой выборке выставлены ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (Мясо и Картофель). Ограничений нет только по сумме выручки.
Можно фильтр удалить полностью и делается это теми же способами, которые применялись для его выставления. Для того чтобы сбросить фильтрацию во вкладке «Данные» нажмите на «Фильтр» в группе «Сортировка и фильтр».
Во втором варианте можно перейти во вкладку «Главная» и нажать там на «Сортировка и фильтр» в «Редактирование». Далее кликаете на «Фильтр».
Если использовать любой из указанных методов, то таблица удалится, а результаты выборки очистятся. То есть таблица будет отображать все ранее внесенные в нее данные.
Второй способ: Применение формулы массива
На том же листе Excel создаете пустую таблицу с теми же наименованиями столбцов в шапке, которые имеются у исходника.
Все пустые ячейки необходимо выделить в первой колонке новой таблицы. В строку формул устанавливаете курсор, чтобы занести формулу — =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000
Выборка и репрезентативность
Собрать выборку можно бесконечным количеством способов. Если говорить о нашем «детском опросе», то можно опросить только четырёхлетних детей, которые посещают курсы японского языка. Однако понятно, что в таком случае мы получим результаты, которые маловероятно можно обобщить на генеральную совокупность.
Как же собрать «хорошую» выборку, то есть, такую выборку, чтобы она отражала все свойства нашей генеральной совокупности? В Примере 2 необходимо задать вопрос о любимом мультике представителям всех «категорий» нашей генеральной совокупности в равных пропорциях, то есть помнить про разный возраст, пол и другие характеристик детей. Точность и полнота признаков, которые можно извлечь из выборки, называют её репрезентативностью.
Репрезентативная выборка — это такая выборка, в которой все основные признаки генеральной совокупности, из которой извлечена данная выборка, представлены приблизительно в той же пропорции или с той же частотой, что и в этой генеральной совокупности.
Какова доля выборки в общей совокупности
Объём выборки — число случаев, включённых в выборку.
Объем формально называют большим или маленьким в зависимости от размеров генеральной совокупности. В обычных исследованиях объем от тридцати объектов считается большим.
Статистическая репрезентативность данных
Чтобы выборка правильно отражала генеральную совокупность, она должна обладать свойством репрезентативности.
Репрезентативность — это соответствие характеристик выборки генеральной совокупности в целом.
Одна и та же выборка может быть репрезентативной и нерепрезентативной по отношению к разным генеральным совокупностям.
Не стоит путать репрезентативность со статистической ошибкой выборки. Разница в том, что ошибки обычно связаны с размером набора: если уменьшить или увеличить охват объектов, её можно исправить. Нерепрезентативная выборка показывает те характеристики объектов, которые не требуются или которых не хватает для полного исследования.
К примеру, если в генеральной совокупности берут всех москвичей, а в выборке оказываются только автолюбители. Значит ли это, что все москвичи ездят на машине? Нет. Если увеличить количество автолюбителей, по ним можно судить обо всех москвичах? Тоже нет. Значит, выборка по отношению именно к этой генеральной совокупности нерепрезентативна.
Случайная выборка из генеральной совокупности в MS EXCEL
Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.
Пусть имеется некая совокупность данных, которая слишком велика для обработки или построения диаграммы. Для этих целей можно использовать репрезентативную выборку из данной совокупности.
Данную выборку можно получить с помощью инструмента «Выборка» надстройки Пакет анализа (Analysis ToolPak).
Надстройка доступна из вкладки Данные, группа Анализ. 
СОВЕТ: Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье Надстройка Пакет анализа MS EXCEL.
Репрезентативность
Выборка может рассматриваться в качестве репрезентативной или нерепрезентативной.
Пример нерепрезентативной выборки
В США одним из наиболее известных исторических примеров нерепрезентативной выборки считается случай, происшедший во время президентских выборов в 1936 году. Журнал «Литрери Дайджест», успешно прогнозировавший события нескольких предшествующих выборов, ошибся в своих предсказаниях, разослав десять миллионов пробных бюллетеней своим подписчикам, а также людям, выбранным по телефонным книгам всей страны и людям из регистрационных списков автомобилей. В 25 % вернувшихся бюллетеней (почти 2,5 миллиона) голоса были распределены следующим образом:
·57 % отдавали предпочтение кандидату-республиканцу Альфу Лэндону
·40 % выбрали действующего в то время президента-демократа Франклина Рузвельта
На действительных же выборах, как известно, победил Рузвельт, набрав более 60 % голосов. Ошибка «Литрери Дайджест» заключалась в следующем: желая увеличить репрезентативность выборки, — так как им было известно, что большинство их подписчиков считают себя республиканцами, — они расширили выборку за счёт людей, выбранных из телефонных книг и регистрационных списков. Однако они не учли современных им реалий и в действительности набрали ещё больше республиканцев: во время Великой депрессии обладать телефонами и автомобилями могли себе позволить в основном представители среднего и высшего класса (то есть большинство республиканцев, а не демократов).