Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
ГОУ ВПО «ВЯТСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ»
ФАКУЛЬТЕТ АВТОМАТИЗАЦИИ МАШИНОСТРОЕНИЯ
КАФЕДРА ТЕХНОЛОГИИ МАШИНОСТРОЕНИЯ
М.З. ПЕВЗНЕР, А.Я. ЧАСНИКОВ
ОСВОЕНИЕ ИНСТРУМЕНТОВ АНАЛИЗА И СТАТИСТИЧЕСКИХ ФУНКЦИЙ ПРОГРАММЫ MS EXCEL НА ПРИМЕРАХ РЕШЕНИЯ ЗАДАЧ ПРЕДВАРИТЕЛЬНОЙ ОБРАБОТКИ ДАННЫХ
Рекомендовано
Учёным советом Вятского государственного
университета в качестве
учебного пособия
Киров
2011
Печатается по решению редакционно-издательского совета
Вятского государственного университета
ББК 34.5 + 22.172
П23
Рецензент: доктор технических наук, старший научный сотрудник С.Г. Хаютин (ФГУП ММПП «Салют», г. Москва)
Певзнер, М.З. Освоение инструментов анализа и статистических функций программы MS EXCEL на примерах решения задач предварительной обработки данных: учебное пособие / М.З. Певзнер, А.Я. Часников. - Киров: Изд-во ВятГУ, 2011. - 43 с.
Учебное пособие включает базовые теоретические представления, а также методические указания по использованию инструментов анализа и статистических функций программы MS EXCEL на примерах изучения и предварительной обработки вариационных рядов случайных величин.
Рекомендуется для студентов специальности 151001 (120100) «Технология машиностроения» по дисциплинам «Математическая обработка экспериментальных данных», «Прикладная статистика», «Основы научных исследований», «Методология научных исследований».
Редактор Е.Г. Козвонина
Подписано в печать Усл. печ. л. 2,7
Бумага для офисной техники Печать цифровая
Заказ № Тираж 50 зкз. Бесплатно
Текст напечатан с оригинала-макета, представленного авторами
610000, г. Киров, ул. Московская, 36
Оформление обложки, изготовление ПРИП ВятГУ
© Певзнер М.З., Часников А.Я., 2011
© Вятский государственный университет, 2011
Оглавление
Введение 4
1. Основные теоретические представления 6
2. Статистические функции и пакет анализа программы EXCEL 17
3. Освоение инструментов анализа и статистических функций
на примерах изучения и предварительной обработки вариационных
рядов случайных величин 24
Задания к лабораторной работе 39
Библиографический список 41
Введение
Цель подготовки современных специалистов - сформировать у них статистическое мышление, то есть систему понятий и представлений, позволяющих с научно обоснованных позиций ориентироваться в сведениях об окружающем мире. Эти данные, относящиеся к производственной сфере, можно подразделить на следующие категории:
- параметры технологии механической, термической или иной обработки (скорость, подача, температура, время и т.д.);
- характеристики качества (размеры, шероховатость поверхности, механические и иные свойства и т.д.);
- технико-экономические характеристики (производительность, затраты на производство и т.д.).
Все характеристики производства объединяет то обстоятельство, что они представляют собой переменные величины, имеющие случайную компоненту. Во-первых, на все эти характеристики влияет множество, в том числе и случайных, факторов, не все из которых можно установить. Во-вторых, эти характеристики контролируются с определенной степенью точности и всегда имеет место некоторая погрешность.
Анализом случайных величин занимается раздел математики, называющийся математической статистикой, используемой практически во всех отраслях знаний, в частности в производственной сфере [1 - 7]. Методы промышленной статистики являются неотъемлемым элементом международных стандартов ISO и их отечественных аналогов [8 - 14]. Полноценное использование этих методов является показателем правильного функционирования на предприятии системы менеджмента качества и, следовательно, конкурентоспособности продукции на отечественном и зарубежном рынке. Стандарты серии ИСО 9000, регламентирующие принципы всеобщего управления качеством (TQM), требуют привлечения методов статистического контроля качества (SQC) и статистического управления процессами (SPC).
Широко рекламируемые специализированные программы (из наиболее успешных следует отметить пакет «STATICTICA» [15]) позволяют решать многообразные задачи статистической обработки данных, но требование соблюдения авторских прав ограничивает их использование на каждом предприятии. В этих условиях следует в полной мере научиться использовать возможности программы MS EXCEL, органично интегрированной в повсеместно применяемый пакет Microsoft Office [16 - 17], в частности её статистические функции и пакет анализа данных [18 - 22].
Предметом данного пособия является освоение статистических функций и пакета анализа MS EXCEL на примерах предварительной обработки значений случайной величины, построения статистического ряда или интервального статистического ряда и их производных.
1. Основные теоретические представления
1.1. Классификация случайных величин
В различных производственных процессах некоторые величины изменяются, а другие сохраняют свое числовое значение. Например, при работе токарного станка время, расстояние перемещения суппорта, резца изменяются (переменные величины обозначают буквами x, y, z), а скорости вращения шпинделя, перемещения суппорта и резца считаются условно-постоянными величинами и обозначаются буквами a, b, c.
Заметим, что в природе абсолютных констант очень мало. Как правило, в технике технолог, исследователь оперирует случайными величинами. Например, скорость вращения шпинделя колеблется около некоторого значения, а измеряется с некоторой погрешностью.
Итак, случайной величиной называется переменная величина, которая в результате испытаний может принимать то или иное значение в границах определенного интервала. Например, действительный размер обработанной детали является случайной величиной, так как с некоторой вероятностью он может принимать любое численное значение в пределах определенного «доверительного» интервала.
Классификация величин приведена на рис. 1.1. В соответствии с ней по роли в исследовании независимыми переменными («факторами») называются переменные, которые варьируются исследователем, тогда как зависимые переменные («отклики») - это переменные, которые измеряются или регистрируются.
Например, независимые переменные - скорость вращения шпинделя и подача суппорта станка - зависят только от экспериментатора, который манипулирует ими, воздействуя на «зависимые» от них производительность, характеристики качества и т.д. Иначе говоря, зависимость проявляется в ответной реакции исследуемого объекта на воздействие.
Рис. 1.1. Постоянные и переменные величины
В зависимости от шкалы измерений (см. рис. 1.1) различают основные четыре типа шкал и, соответственно, четыре типа переменных.
Интервальные переменные позволяют упорядочивать объекты измерения, выражать численно и сравнивать различия между ними. Например, температура, измеренная в градусах Цельсия, образует интервальную шкалу, и температура 40 градусов по Цельсию на 10 градусов выше, чем температура 30 градусов по Цельсию.
Относительные переменные очень похожи на интервальные переменные. В дополнение ко всем свойствам переменных, измеренных в интервальной шкале, их характерной чертой является наличие определенной точки абсолютного нуля. Таким образом, для этих переменных являются обоснованными предложения типа: x в два раза больше, чем y. Например, температура по Кельвину образует шкалу отношения, и можно не только утверждать, что температура 200 градусов выше, чем 100 градусов, но и что она вдвое выше. (Интервальные шкалы (например, шкала Цельсия) не обладают данным свойством шкалы отношения.) В большинстве статистических процедур не делается различия между свойствами интервальных шкал и шкал отношения.
Порядковые (ординальные) переменные позволяют ранжировать (упорядочить) объекты, указав, какие из них в большей или меньшей степени обладают качеством, выраженным данной переменной. Однако они не позволяют определить «насколько больше» или «насколько меньше». Например, переменная: мальчик - юноша - мужчина - старик, возраст которых можно примерно выразить в баллах.
Номинальные (категориальные) переменные используются только для качественной классификации. Типичные примеры номинальных переменных - пол, национальность, цвет, город и т.д.
Интервальные и относительные переменные, являясь случайными величинами, могут быть «дискретными» или «непрерывными».
Дискретными случайными величинами называются такие величины, которые при нимают отдельные, большей частью целочисленные значения. Напри мер, число деталей, обработанных на станке, есть дискретная случайная величина. Непрерывной случайной величиной называется такая величина, которая может принимать любые численные значения из интервала возможных значений. Понятно, что между двумя любыми дискретными величинами можно поместить бесконечное число непрерывных случайных величин.
В классификацию, изображенную на рис. 1.1, не вошло важное понятие, представляющее целую область математической статистики, - временные ряды. Временной ряд – это переменная величина, последовательные значения которой могут быть пронумерованы, например, какой-либо размер деталей, обработанных на станке.
1.2. Области и методы математической статистики
Математическую статистику в зависимости от элемента исследования (указан в скобках) делят на четыре большие области (рис. 1.2). Соответственно областям прикладной математической статистики на рис. 1.2 расположены и методы, используемые этой наукой.
Основные методы математической статистики следующие.
Описательная статистика - это обработка и представление количественных данных с целью получения оценок некоторых характеристик этих данных.
Планирование экспериментов - это организация экспериментов таким образом, чтобы при минимальном их количестве и с максимальной точностью решать поставленные задачи.
Проверка гипотез - система приёмов для статистической проверки некоторого предположения.
Анализ измерений - набор процедур для оценки точности измерительной системы в реальных условиях её работы.
Анализ возможностей процесса - это оценка изменчивости процесса, находящегося в состоянии статистической управляемости (стабильности); производится по ГОСТ Р 50779.44-2001.
Рис. 1.2. Области и методы прикладной математической статистики
Регрессионный анализ связывает зависимую переменную («отклик») с независимыми переменными (факторами).
Выборочный контроль - статистический метод получения информации о характеристиках совокупности путем изучения представительной выборки из этой совокупности.
Моделирование - совокупность процедур, с помощью которых теоретическая или эмпирическая система может быть представлена математически в виде функциональной зависимости.
Контрольная карта - графическое представление данных, которые берутся из процесса и наносятся на график в соответствии со временем.
Доверительный интервал - это интервал значений параметра, внутри которого с заданной вероятностью он находится.
1.3. Генеральная совокупность и выборка; задача осуществления репрезентативной выборки
«Генеральная совокупность» - это множество (N) качественно однородных элементов самой разной природы (какой-либо размер или другая характеристика множества обработанных деталей, изготовленных приборов и т.д.). Обеспечивать контроль каждо й из множества этих деталей, как правило, не удается в силу объективных причин (большая трудоёмкость, контроль может быть «разрушающим», например контроль продолжительности работы и т.д.). Поэтому для контроля генеральной совокупности выбира ют некоторое количество (n) единиц продукции («выборочную совокупность» или «выборку») и по их показателям судят обо всей продукции в целом (рис. 1.3).
Рис. 1.3. Виды генеральной и выборочной совокупности
Следует помнить, что конечной целью исследования обычно является не изучение данной конкретной выборки; выборка представляет интерес лишь постольку, поскольку она даёт информацию обо всей генеральной совокупности. Поэтому очень важно, чтобы выборка была репрезентативная (представительная), а для этого нужно, чтобы она производилась правильно (см. рис. 1.3). Можно выделить два основных типа выборок: механическую и случайную.
Механическая выборка в программе MS EXCEL называется «периодической». Подлежащие обследованию элементы генеральной совокупно сти отбирают через определенный интервал. Так, например, если выборка должна составить 50 % генеральной совокупности, то отби рают каждый второй элемент генеральной совокупности. Если вы борка 10%-ная, то отбирают каждый 10-й её элемент, и т.д. Иногда механический отбор не обеспечивает репрезентативности выборки. Например, если период отбора совпадает или кратен периоду заточки резца, то в выборку могут попасть детали, изготовленные преимущественно затупленными или, наоборот, новыми резцами. В этом случае необходимо устранить кратность этих периодов.
Случайная выборка может быть повторной (проконтролированная деталь возвращается в генеральную совокупность и снова участвует в отборе) или бесповторной (т.е. «с возвращением» или «без возвращения»). Случайная выборка и её производные (типическая, когда случайные выборки производятся в непересекающихся группах, а затем объединяются, и серийная, когда серии также выбираются) являются истинно репрезентативными. Для образования случайных выборок используют таблицы случайных чисел или элементы генеральной совокупности (контролируемые детали) предварительно нумеруют, записывая каждый номер на отдельную карточку. В результате получают набор карточек, число которых совпадает с объёмом генеральной совокупности. После тщательного перемешивания выбирают одну карточку. Объект, имеющий одинаковый номер с карточкой, считается попавшим в выборку. Как будет показано ниже, программа MS EXCEL позволяет избежать этих достаточно трудоёмких и затратных по времени операций.
1.4. Представление о вариационном ряде случайной величины
Если значения случайной величины, например, размера обработанной детали, располагаются в возрастающем (неубывающем) порядке, то этот набор величин представляет собой «вариационный ряд».
Наиболее простой характеристикой распределения и одновременно способом упорядочения случайной величины является «статистический ряд». Это таблица, которая в первой строке содержит значения случайной величины Z(i), а во второй - числа их повторений (табл. 1.1). Число ni называют «частотой», а отношение ni/n - «относительной частотой» (n = ). Статистические данные, представленные в виде статистического ряда, называют «группированными».
Таблица 1.1. Статистический ряд
|
Z(1) |
Z(2) |
… |
Z(m) |
|
n1 |
n2 |
… |
nm |
Разновидность статистического ряда - «интервальный статистический ряд» (табл. 1.2). Исходные данные в нём группируют следующим образом: отрезок J, содер жащий все выборочные значения, разбивают на m промежутков или интервалов Ji (J =), как правило, одинаковой длины (J = mJi). Число интервалов m выбирают в зависимости от объема выборки n. Для ориентировочной оценки величины m можно пользоваться формулой
(1.1)
Таблица 1.2. Интервальный статистический ряд
|
J1 |
J2 |
… |
Jm |
J |
|
n1 |
n2 |
… |
nm |
Устанавливается, что каждый промежуток содержит своё минимальное значение, но лишь по следний промежуток содержит и своё максимальное (правое) значение. (Иногда в верхней строке табл. 1.2 указывают середину интервала, а в нижней строке - относительную частоту.)
2. Статистические функции и пакет анализа программы EXCEL
Предполагается, что пользователи уже знакомы с основными настройками, опциями и функциями Microsoft Office EXCEL из курса «Информатики» или аналогичных дисциплин.
За исключением некоторых возможностей, заложенных в графических опциях, основные статистические функции программы MS EXCEL определяются собственно статистическими функциями (78 функций) и 19 инструментами пакета анализа данных. При этом, как показано ниже, возможности доступа к инструментам анализа и статистическим функциям для Microsoft Office EXCEL 2003 (а также предыдущих версий пакета) и Microsoft Office EXCEL 2007 различаются.
2.1. Установка надстройки «Пакет анализа» и технология работы в режиме «Анализ данных»
Установка надстройки «Пакет анализа» в Microsoft Office EXCEL 2003. Для то го чтобы отыскать команду вызова надстройки «Пакет анализа», необходимо воспользоваться меню Сервис. Здесь возможны следующие ситуации.
1. В меню Сервис выбирают команду «Анализ данных», чтобы попасть в окно доступных инструментов анализа.
2а. В меню Сервис отсутствует команда «Анализ данных». В этом случае необходимо в меню Сервис выполнить команду «Надстройки». Раскроется одноименное окно (рис. 2.1) со спи ском доступных надстроек. В этом списке нужно найти эле мент «Пакет анализа», поставить рядом с ним «галку» и выполнить команду ОК. Если надстройка «Пакет анализа» была инсталлирована (стандартная установка), то в меню Сервис появится команда «Анализ данных». (В случае возможного исчезновения пункта «Анализ данных» в меню «Сервис» при наличии «Пакета анализа» в меню «Сервис» - «Надстройки» для его восстановления необходима перезагрузка компьютера.)
Рис. 2.1. Окно «Надстройки»
2б. Если в списке окна «Надстройки» нет элемента «Пакет анализа», необходимо заново произвести доустановку MS EXCEL, например, с дистри бутивного компакт-диска Microsoft Office.
Установка надстройки «Пакет анализа» в Microsoft Office EXCEL 2007. В Microsoft Office 2007 для включения «Пакета анализа» необходимо выполнить следующие действия.
1. Нажать «Настройка панели быстрого доступа» (маленький треугольник в строке меню).
2. Выбрать «Другие команды» (см. рис. 2.2).
Рис. 2.2. Начальный этап настройки панели быстрого доступа
3. Выбрать вкладку «Данные» (рис. 2.3).
К этой вкладке можно перейти быстрее, нажав правой кнопкой мыши на «Office».
Рис. 2.3. Выбор вкладки «Данные»
4. Перейти от «Настройка» к «Надстройки» и нажать кнопку «Перейти» (рис. 2.4).
Рис. 2.4. Выбор «Пакета анализа» в «Надстройках»
6. В окне «Доступные надстройки» установить флажок «Пакет анализа» и нажать кнопку ОК.
7. После загрузки пакета анализа в меню «Данные» становится доступной команда «Анализ данных».
Если «Пакет анализа» отсутствует в списке поля «Доступные надстройки», то для проведения поиска необходимо нажать кнопку «Обзор». В случае появления сообщения о том, что пакет статистического анализа не установлен на компьютере и предложения установить его, следует нажать кнопку «Да».
2.2. Работа с мастером функций и знакомство
со статистическими функциями MS EXCEL
В Microsoft Office EXCEL 2003 окно «Мастер функций» (рис. 2.5) открывается через меню «Вставка» или нажатием на кнопку «fx», расположенную на панели инструментов окна MS EXCEL. В появившейся опции «Категория» следует выбрать «Статистические» (по умолчанию открывается «10 недавно использовавшихся») и нажать OK.
Рис. 2.5. Окно «Мастер функций»
В Microsoft Office 2007 для использования статистических функций необходимо последовательно включить опции «формулы» - «другие функции» - «статистические функции».
2.3. Распределение инструментов анализа и статистических функций по задачам статистического анализа
Классификация инструментов анализа и статистических функций по решаемым задачам представлена таблицей на рис. 2.6. В настоящей работе в качестве примера рассматриваются инструменты анализа и статистические функции предварительной обработки данных (см. первые столбцы таблицы).
Рис. 2.6. Инструменты анализа и основные статистические функции, классифицированные по обычно решаемым задачам статистического анализа
3. Освоение инструментов анализа и статистических функций на примерах изучения и предварительной обработки вариационных рядов случайных величин
Для поиска нужной статистической функции (рис. 3.1) следует иметь в виду, что в отличие от инструментов пакета анализа (рис. 3.2) они располагаются в алфавитном порядке, начиная с латинских букв.
Рис. 3.1. Начало списка статистических функций в окне «Мастер функций»
Рис. 3.2. Инструменты пакета «Анализ данных»
«Мастер функций» снабжён детальной справкой. Для её открытия (в отличие от справки по инструменту пакета анализа) не требуется открывать саму функцию, достаточно выделить её и нажать «Справка по этой функции» (см. рис. 3.1).
3.1. Статистическая функция СЧЁТ и родственные ей функции
Функция СЧЁТ (рис. 3.3) - наиболее простая в ряду подсчитывающих функций, использующаяся для определения количества числовых ячеек в интервалах или массивах ячеек. Учитываются аргументы, которые являются числами, пустыми значениями, логическими значениями, датами или текстами, изображающими числа. Аргументы, являющиеся значениями ошибки или текстами, которые нельзя интерпретировать как числа, игнорируются. Если аргумент является массивом или ссылкой, то подсчитываются только числа в этом массиве или ссылке. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются. Наоборот, функция СЧЁТЗ используется для подсчёта количества непустых значений в списке аргументов или количества ячеек с данными в интервале или массиве. Кроме того, имеется функция СЧИТАТЬПУСТОТЫ, которая подсчитывает количество пустых ячеек в заданном диапазоне.
Рис. 3.3. Аргументы функции СЧЁТ
Из данной серии родственных функций наиболее полезна функция СЧЁТЕСЛИ - она подсчитывает количество ячеек, удовлетворяющих заданному критерию. Например, критерий «>k» позволит подсчитывать количество ячеек со значением выше k. Таким образом, её можно использовать, например, для определения частот в статистическом ряду (см. главу 1, а также ниже функцию «ЧАСТОТА»).
Microsoft EXCEL предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, можно использовать функцию СУММЕСЛИ (в категории «Математические»). Для получения формулы, выбирающей в зависимости от выполнения условия одно из двух значений, можно использовать функцию ЕСЛИ (в категории «Логические»).
3.2. Статистические функции МИН, МАКС и родственные им функции
Функции МИН и МАКС, определяющие, соответственно, наименьшее или наибольшее значение из набора значений, просты в использовании и здесь на рисунках не представлены. Функция МАКСА (и родственная ей функция МИНА) наряду с числовыми значениями сравнивает также текстовые и логические значения, такие, как ИСТИНА и ЛОЖЬ. Аргументы, содержащие значение ИСТИНА, интерпретируются как 1 (единица), аргументы, содержащие текст или значение ЛОЖЬ, интерпретируются как 0 (нуль). (Если логические значения и текст должны игнорироваться, следует пользоваться функциями МИН и МАКС.)
Бóльшими возможностями в сравнении с предыдущими обладают функции НАИБОЛЬШИЙ и, соответственно, НАИМЕНЬШИЙ. Эти функции позволяют выбрать из множества данных k-е по величине значение по его относительному местоположению, внося в поле «к» соответствующую цифру (рис. 3.4). Например, функцию НАИБОЛЬШИЙ можно использовать для определения наилучшего, второго или, например, десятого результатов по производительности станков, по величине тестируемого размера детали и т.д.
Рис. 3.4. Аргументы функции НАИБОЛЬШИЙ
3.3. Справочная информация по технологии работы
с инструментом «Выборка»
Инструмент анализа данных «Выборка» (рис. 3.5) создает выборку из генеральной совокупности («входной интервал»). Он позволяет получать как механическую (метод выборки «периодический»), так и случайную (причём «повторную») выборку. Под «Числом выборок» следует понимать задаваемый объём выборки, а в эле мент «Метки» (здесь и в других инструментах анализа данных) следует поставить «галку», если массивы данных задаются с их названиями. При избрании в качестве «параметров вывода» «выходного интервала» произведённая выборка будет записана в виде одного столбца, начиная с ячейки, указанной в ссылке.
Рис. 3.5. Инструмент анализа данных «Выборка»
3.4. Ранжирование случайных величин
3.4.1. Статистические функции РАНГ и ПРОЦЕНТРАНГ
Функция РАНГ (рис. 3.6) определяет ранг числа, т.е. его величину относительно других значений в списке. (Если список отсортировать по величине, то ранг числа будет его позицией в этом списке.) В поле «Число» указывается число (или числа, см. ниже), для которого (которых) определяется ранг, а в поле «Ссылка» - сам массив чисел или ссылка на список чисел (нечисловые значения в ссылке игнорируются). Поле «Порядок» определяет способ упорядочения:
- если в поле «Порядок» стоит 0 (нуль) или оно не заполнено, то программа определяет ранг числа так, как если бы массив был отсортирован в порядке убывания;
Рис. 3.6. Аргументы функции РАНГ
- если в поле «Порядок» стоит любое ненулевое число, то программа определяет ранг числа так, как если бы массив был отсортирован в порядке возрастания.
Функция РАНГ присваивает повторяющимся числам одинаковый ранг. Однако наличие повторяющихся чисел влияет на ранг последующих чисел. Например, если число 8,73 появляется дважды и имеет ранг 10, то следующее в порядке возрастания число 8,75 будет иметь ранг 12 (и никакое число не будет иметь ранг 11).
Функция РАНГ может присваивать ранг одному отдельному числу в ряду данных (номер ячейки, занимаемой им, в этом случае следует поместить в поле «Число», см. рис. 3.6) или всем исходным данным. В последнем случае необходима следующая последовательность действий:
1) сформировать исходные данные в виде таблицы, в первой графе которой расположены номера проконтролированных деталей, а во второй - их размеры;
2) выделить диапазон, в который будет введён результат вычислений рангов (например, в третьей графе таблицы);
3) вызвать «Мастер функций» и выбрать функцию РАНГ;
4) поместить в поле «Число» и в поле «Ссылка» диапазон ячеек с исходными данными;
5) набрать комбинацию клавиш CTRL+SHIFT+ENTER.
Функция ПРОЦЕНТРАНГ (рис. 3.7) используется для оценки относительного положе ния некоторого значения (или всех значений, см. ниже) в наборе данных. Оценка проводится в относительных величинах: в отличие от функции РАНГ здесь наибольшему значению соответствует 1,0; наименьшему значению соответствует 0, т.е. происходит ранжирование в долевом отношении. Функцию ПРОЦЕНТРАНГ можно использовать, например, для того чтобы среди всех результатов тестирования оценить и выделить значения, наиболее удалённые от среднего значения (их процентранг близок к 1 или к 0). Это позволяет определить, относятся ли эти значения к «выбросам» с целью последующего их исключения.
Рис. 3.7. Аргументы функции ПРОЦЕНТРАНГ
В поле «Разрядность» указывается количество значащих цифр для определяемого значения. Если этот аргумент отсутствует, то функция ПРОЦЕНТРАНГ выводит число в виде (0,xxx).
Как и в работе с функцией РАНГ, функция ПРОЦЕНТРАНГ может определять процентранг как для одного отдельного числа в ряду данных (номер ячейки, занимаемой им, в этом случае следует поместить в поле «x», см. рис. 3.7), так и для всех исходных данных. Последовательность операций в последнем случае совершенно аналогична последовательности, производимой при определении рангов для всех исходных данных (см. выше).
3.4.2. Функция ПЕРСЕНТИЛЬ
Функция ПЕРСЕНТИЛЬ (рис. 3.8) в определённом отношении решает задачу, обратную функции ПРОЦЕНТРАНГ. Она используется для определения некоторого значения из набора данных (не более 8191 значений), соответствующего заданному относительному положению этого значения. Причём это относительное положение изменяется в следующих пределах: наименьшее значение имеет персентиль 0, а наибольшее значение - персентиль 1. Функция ПЕРСЕНТИЛЬ может служить для определения некоторого порога приемлемости. Например, нужно принять в институт наилучших кандидатов (90 %); если кандидатов больше, чем имеющихся мест, нужно выбрать только тех, которые набрали баллов более, чем 0,9-я персентиль.
Рис. 3.8. Аргументы функции ПЕРСЕНТИЛЬ, где k - значение персентиля в интервале от 0 до 1 включительно
Применительно к металлообработке бывает целесообразно не принимать к рассмотрению те результаты контроля, которые слишком далеки от среднего значения (настолько, что могут относиться к «выбросам»). Для этого, например, принимается решение отбросить результаты, имеющие значение персентиля больше 0,95 и меньше 0,05. Тогда в поле «к» (см. рис. 3.8) следует последовательно ставить 0,95 (95-й персентиль) и определять максимально допустимое значение результатов контроля, а затем - 0,05 (5-й персентиль), определяя минимально допустимое значение результатов контроля.
3.4.3. Инструмент анализа «Ранг и персентиль»
Этот инструмент в отличие от соответствующих функций не просто определяет ранги и персентили, а сортирует их в порядке уменьшения значения. Он может производить эту операцию не для одной характеристики (например, одного размера), а нескольких, сгруппированных по столбцам или по строкам (рис. 3.9).
Рис. 3.9. Инструмент анализа данных «Ранг и персентиль»
Инструмент выводит значения рангов и персентилей на тот же рабочий лист (необходимо указать ячейку, откуда начнётся вывод), новый рабочий лист или новую рабочую книгу. (Необходимо установить переключатель в положение «по столбцам» или «по строкам» в зависимости от расположения данных во входном диапазоне.)
3.5. Анализ частоты расположения значений в вариационном ряду
3.5.1. Статистические функции ЧАСТОТА и МОДА
Функция ЧАСТОТА (рис. 3.10) используется для подсчета количества значений случайной величины (частот), попадающих в заданные интервалы («карманы»). Полученные результаты могут использоваться для построения интервального вариационного ряда, а затем для построения соответствующей гистограммы. (Функция ЧАСТОТА игнорирует пустые ячейки и тексты.)
Рис. 3.10. Аргументы функции ЧАСТОТА
Последовательность действий должна быть следующей.
1. В таблице строится массив границ интервалов (который на рис. 3.10 представлен как «массив интервалов», а в ряде версий MS EXCEL - «Двоичный массив»). Для правильного выбора массива границ интервалов нужно первоначально оценить оптимальное количество интервалов m, например по формуле (1.1), и длину интервала: L = (Dmax - Dmin)/m, округлив L в бόльшую сторону.
2. Выделяется массив ячеек, куда в результате будут введены значения частот (на единицу больше числа заданных границ интервалов). Это увеличение на единицу объясняется тем, что первый и последний интервалы ограничены лишь с одной стороны.
3. Открывается функция ЧАСТОТА.
4. В открытое окно аргументов функции вводятся массив данных и массив границ интервалов. При этом значения частот уже появляются в окне.
5. Для выведения значений частот в таблицу (в выделенный по п. 2 массив ячеек) необходимо одновременно нажать комбинацию клавиш CTRL+SHIFT+ENTER.
Таким образом, с помощью функции ЧАСТОТА можно сформировать интервальный статистический ряд, который будет состоять из двух строк. В первой строке определены границы интервалов, причём первый и последний интервалы ограничены с одной стороны, а со второй - соответственно -∞ и +∞. Во второй строке должны быть расположены значения частот, попавших в заданные интервалы.
Функция МОДА (рис. 3.11) родственна функции ЧАСТОТА. В наборе значений МОДА - наиболее часто повторяющееся значение в массиве или интервале данных. Поэтому если множество данных не содержит одинаковых значений, то функция МОДА показывает ошибку.
Аргументы должны быть числами, именами, массивами или ссылками, которые содержат числа. Таких массивов или аргументов, для которых вычисляется МОДА («Число1, число2, ... »), может быть не более 30.
Рис. 3.11. Аргументы функции МОДА
3.5.2. Инструмент анализа «Гистограмма»
Инструмент близок по решаемым задачам к функции «ЧАСТОТА», но имеет значительно более широкие возможности. В нём поле «Входной интервал» (см. рис. 3.12) соответствует полю «Массив данных» в функции «ЧАСТОТА», а поле «Интервал карманов» соответствует полю «Массив интервалов» в функции «ЧАСТОТА». Но в отличие от функции «ЧАСТОТА» поле «Интервал карманов» не обязательно для заполнения. При заполнении эти значения должны быть введены в возрастающем порядке. В Microsoft EXCEL вычисляется число попаданий данных между текущим началом отрезка и соседним бóльшим по порядку, если такой есть. При этом включаются значения на нижней границе отрезка и не включаются значения на верхней границе. Если «Интервал карманов» не был введен, то набор отрезков, равномерно распределенных между минимальным и максимальным значениями данных, будет создан автоматически.
Может потребоваться, чтобы интервалы располагались не в возрастающем порядке, как обычно, а в порядке убывания частоты. Получаемый график в этом случае будет напоминать по форме известную диаграмму Парето [17]. Для этого следует поставить «галочку» в окне «Парето (отсортированная диаграмма)» (см. рис. 3.12). Если флажок снят, то данные в выходном диапазоне будут представлены в порядке возрастания отрезков, а трёх самых правых столбцов с отсортированными данными не будет. (Это следует учитывать при создании «Интервала карманов».)
Рис. 3.12. Инструмент анализа данных «Гистограмма»
Иногда необходимо знать не только распределение частот, но и накопленный процент частот из расчёта, что сумма всех частот = 1. Для вывода этого дополнительного графика интегральных процентов следует установить флажок не только в окне «Вывод графика», но и в окне «Интегральный процент» (см. рис. 3.12).
Гистограмма может быть получена также в рамках графических опций программы. Кроме того, эти опции позволяют преобразовать форму получаемой гистограммы: ширину зазора, формат осей, подписи данных и т.д.
Задания к лабораторной работе
1. Открыть файл MS EXCEL «Предварительная обработка данных».
2. Для конкретно используемой операционной системы:
а) опробовать работу с мастером статистических функций;
б) опробовать установку, удаление и включение «Пакета анализа», инструментов анализа.
3. В выбранном по заданию преподавателя массиве размеров контролируемой детали:
а) используя функцию СЧЁТ и родственные ей функции, определить общее количество ячеек, количество «пустых» и «непустых» ячеек, а также ячеек, удовлетворяющих заданному условию;
б) используя функции МИН, МАКС, НАИБОЛЬШИЙ и НАИМЕНЬШИЙ, определить наименьшее или наибольшее значения размера в массиве данных, а также третье, пятое и т.д. по величине;
в) произвести случайную и механическую выборки из значений размеров, представляющих всю генеральную совокупность заданного массива;
г) для выбранной части массива размеров и нескольких размеров из массива провести ранжирование, т.е. определить РАНГ и ПРОЦЕНТРАНГ для случаев, когда массив «отсортирован» в порядке убывания или, наоборот, возрастания;
д) используя функцию ПЕРСЕНТИЛЬ, для доверительной вероятности 0,9 определить верхнюю (95-й персентиль) и нижнюю (5-й персентиль) допустимые границы;
е) используя инструмент анализа данных «Ранг и персентиль», определить их значения для исходных данных, предварительно расположив их в одной строке или столбце или в нескольких строках или столбцах;
ж) определить количество интервалов и ширину интервала, выбрать массив границ интервалов, определить значения частот для этих интервалов и построить интервальный вариационный ряд контролируемого размера детали;
и) определить Моду;
к) построить гистограмму в классической форме и в форме диаграммы Парето с наложением диаграммы накопленных частот («Интегральный процент»).
4. Результаты выполнения заданий 3а - 3к представить на отдельном листе (листах) файла MS EXCEL «Предварительная обработка данных».
5. После проверки преподавателем файл «Предварительная обработка данных» закрыть без сохранения изменений.
Библиографический список
1. Орлов, А.И. Прикладная статистика: учебник для вузов / А.И. Орлов. - М. : Экзамен, 2004. - 656 с.
2. Теория статистики: под ред. Р.А. Шмойловой. - М. : Финансы и статистика, 1998 - 2010.
3. Палий, И.А. Прикладная статистика: учеб. пособие для вузов / И.А. Палий. - М. : Дашков и К0 : Наука Спектр, 2008. - 223 с.
4. Шведов, А.С. Теория вероятностей и математическая статистика: учеб. пособие для вузов / А.С. Шведов. - 2-е изд., перераб. и доп. - М. : Изд. дом ГУ ВШЭ, 2005. - 254 с. - (Учебники Высшей школы экономики).
5. Айвазян, С.А. Прикладная статистика и основы эконометрики / С.А. Айвазян, В.С. Мхитарян. - М. : ЮНИТИ, 1998. - 1022 с.
6. Тюрин, Ю.Н. Анализ данных на компьютере: учеб. пособие / Ю.Н. Тюрин, А.А. Макаров. - М.: ИД «Форум», 2008. - 368 с.
7. Гмурман, В.Е. Теория вероятностей и математическая статистика / В.Е. Гмурман. - М. : Высш. образование, 2008. - 479 с.
8. Международный стандарт ISO 9001. Системы менеджмента качества. Требования. - 3-е изд. - Дата введения 2000-12-15. - ISO, 2000. - 41 с.
9. ГОСТ Р ИСО/ТО 10017-2005. Статистические методы. Руководство по применению в соответствии с ГОСТ Р ИСО 9001. - Дата введения 2005-07-01. - М. : Стандартинформ, 2005. - 19 с.
10. ГОСТ 15467-79. Управление качеством продукции. Основные понятия. Термины и определения. - Взамен ГОСТов: 15467-70, 16431-70, 17341-71, 17102-71; Введ. 01.07.79. - М. : Изд-во стандартов, 1987. - 28 с.
11. ГОСТ Р ИСО 11462-1 - 2007. Статистические методы. Руководство по внедрению статистического управления процессами. Часть 1. Элементы. - Дата введения - 2007-09-01. - М. : Стандартинформ, 2007. - 16 с.
12. ГОСТ Р 50779.0-95. Статистические методы. Основные положения. - Дата введения 1996-07-01. - М. : Госстандарт России, 1995. - 12 с.
13. ГОСТ Р 50779.10-2000 (ИСО 3534.1-93). Статистические методы. Вероятность и основы статистики. Термины и определения. - Введ. 2001-07-01. - М. : Госстандарт России, 2001. - 35 с.
14. ГОСТ Р 50779.11-2000 (ИСО 3534.2-93). Статистическое управление качеством. Термины и определения. - Дата введения 2001-07-01. - М. : Госстандарт России, 2001. - 26 с.
15. Боровиков, В.П. STATISTICA. Искусство анализа данных на компьютере: Для профессионалов. - 2-е изд. / В.П. Боровиков. - СПб. : Питер, 2003. – 688 с.
16. Берк, К. Анализ данных с помощью Microsoft Excel / К. Берк, П. Кэйри. - М. : Издательский дом «Вильямс», 2005. - 560 с.
17. Саймон, Д. Анализ данных в Excel: наглядный курс создания отчетов, диаграмм и сводных таблиц: пер. с англ. / Д. Саймон. - М. : Издательский дом «Вильямс», 2004. - 528 с.
18. Козлов, А.Ю. Пакет анализа MS Ехсеl в экономико-статистических расчётах / А.Ю. Козлов, В.Ф. Шишов. - М. : ЮНИТИ-ДАНА, 2003. - 139 с.
19. Козлов, А.Ю. Статистические функции MS Excel в экономико-статистических расчетах / А.Ю. Козлов, В.С. Мхитарян, В.Ф. Шишов; под ред. В.С. Мхитаряна. - М. : ЮНИТИ-ДАНА, 2003. - 231 с.
20. Макарова, Н.В. Статистика в Ехсеl / Н.В. Макарова, В.Я. Трофимец. - М. : Финансы и статистика, 2002. - 368 с.
21. Горелова, Г.В. Теория вероятностей и математическая статистика в примерах и задачах с применением Excel. - 2-е изд., испр. и доп. / Г.В. Горелова, И.А. Кацко. - Ростов н/Д : Феникс, 2002. - 400 с.
22. Вуколов, Э.А. Основы статистического анализа. Практикум по статистическим методам и исследованию операций с использованием пакетов STATISTICA и EXCEL / Э.А. Вуколов. - М. : ФОРУМ; ИНФРА-М, 2004. - 464 с. - (Профессиональное образование).
Учебное издание
ПЕВЗНЕР Михаил Зиновьевич, ЧАСНИКОВ Александр Яковлевич
ОСВОЕНИЕ ИНСТРУМЕНТОВ АНАЛИЗА И СТАТИСТИЧЕСКИХ ФУНКЦИЙ ПРОГРАММЫ MS EXCEL ДЛЯ РЕШЕНИЯ ЗАДАЧ, ВОЗНИКАЮЩИХ ПРИ ИССЛЕДОВАНИИ ПРОЦЕССОВ
МЕТАЛЛООБРАБОТКИ
Учебное пособие