Как построить кривую михайлова в excel
Высшее образование и наукастановятся глобальным фактором общественного развития, выдвигаются в числонаиболее важныхнациональных и общемировых приоритетов, выступаютв качестве важнейших компонентовкультурного, социального и экономическиустойчивого развития людей, сообществ, наций[1].
В докладе ЮНЕСКО " Высшее образование в XXI веке: подходы и практические меры" (1998г.) отмечалось, что "в сфере высшего образования наблюдается теснейшее сближение, если не общность проблем, тенденций, задач и целей, заставляющихзабыватьо национальных и региональныхразличиях и специфике«[2]. Сложившиеся в настоящее время социально-экономическиеи социально-культурные условиятребуют подготовки специалиста, конкурентоспособного на рынке труда.
Для успешного функционированияспециалиста в высокотехнологическом обществе необходимо постоянное пополнениебагажа знаний, умений и навыков.Непрерывное образование- необходимость и требование современнойнаучно-технической цивилизации.
Главная задача высшейшкол ы- поднять профессиональную и социальнуюкомпетентность выпускников вузов, научить их ориентироваться в потоке постоянно меняющейся информации, мыслитьсамостоятельно, критически и творчески. Сегодня этоневозможно без овладения студентами знаниями, умениями, навыками использования информационных технологий в сферебудущей профессиональной деятельности.
К настоящему времени исследователи пришлик единому мнению, что информационные технологии должны разрабатываться с учетом классических дидактических требований: принципа научности, доступности и посильной трудности, систематичности и последовательности, прочности усвоения, наглядности, связи теории с практикой, сознательности и активности (самостоятельности), принципа коллективного характера обучения и учета индивидуальных особенностейобучающихся, однако применительно к новым информационным технологиямониимеют свою специфику [3].
ПЭВМ наиболее полно удовлетворяет дидактическим требованиям и позволяет управлять процессом обучения, максимально адаптировать его киндивидуальнымособенностям обучаемого. Знания, полученные при компьютерном обучении, выступают в познавательной деятельностив качестве средстварешения профессиональных задачдеятельности специалиста.
Также исследователи выделяют возможные направлениявключениякомпьютера в процесс учебно-познавательной деятельности обучаемых: диагностика, обучающий режим, отработка умений и навыковпри решении задачпосле изучения темы, моделирование сложных процессов, графическая иллюстрация изучаемого материала, работа с базами данных [4].
Остановимся наприменениитабличного процессора Microsoft Excel при изучении элементов теории вероятностей и математической статистики вкурсематематики для экономистов. Курс математики в системе подготовки экономистовявляется основой дляизучения таких дисциплин какэконометрика, статистика, микроэкономика, макроэкономика, а математические методы исследуютсяво всех областях знаний. В процессе обучения математике у студентов вырабатываютсянавыки исследовательской работы, формируются приемы умственной деятельности, развивается интеллект, т. е.формируетсяличностьбудущего специалиста с необходимымипрофессионально значимымикачествами.
Применение математического аппарата теории вероятностей и математической статистики позволяетполучать наиболее вероятныеколичественныезначенияэкономических показателей, устанавливать связьмежду различнымислучайными параметрами и приниматьобоснованные решения в экономике.
В настоящее время математико-статистические методы широко внедрилисьв жизнь, благодаряперсональным электронно-вычислительным машинам. Статистическиепрограммные пакеты сделали эти методы более доступными и наглядными, так как трудоемкуюпо расчету различныхстатистик, параметров, характеристик, построению таблиц и графиков в основном стал выполнять компьютер, а исследователюостаетсяглавным образом творческая работа: постановка задачи, выбор методов ее решения и интерпретация результатов.
Существует множество различных пакетов программ по работе со статистическими данными, но наибольшее распространение в деловой сфере получил табличный процессор Microsoft Excel . Он включает в себя программную надстройку «Пакет анализа» и библиотеку из 83 (в среде Microsoft Excel 2000) статистических функций , 50 математических функций,которые позволяют автоматизироватьрасчеты, а такжена их основеполучить графическую интерпретацию.
При изучении основных понятий и теорем теории вероятностей можно использовать, например,такие функции Excel как: экспонента, степень, факториал, перестановки, число комбинаций, вероятность. Изучаяслучайные величины и их характеристики, можно использовать, например, такие статистические функции как дисперсия, доверительный интервал, медиана, мода , различные виды распределений случайных величин и др. Кроме того,в дальнейшем, при изучении эконометрикии статистики,предоставляется широкийвыбор другихстатистических функций.
Рассмотрим использование Excel при изученииразличных видовраспределений дискретных и непрерывных случайных величин.
При работе со случайными величинамина лекционных занятиях студентовзнакомят с понятиемслучайной величины, законами ее распределения, математическим ожиданием, дисперсией. Формируются вероятностные модели биномиального распределения, распределения Пуассона, геометрического и гипергеометрического и других распределений, во время практических занятий эти понятия закрепляются и отрабатываются. Задания, выполненные на компьютере, помогут вывести обучающихсяна более высокий уровень усвоения знаний и умений,и сопровождаться значительной экономией времени.
При рассмотрениизаконов распределения, например, нужно обратить внимание насферы ихиспользования. При построении графиков функцийсравнивать их кривые, анализировать, делать выводы.
Рассмотрим задание набиномиальное распределение:
Задание 1. Построить с помощью программы Excel , многоугольникбиномиального распределения для следующих параметров:
Используетсястатистическая функция БИНОМРАСПР:
Изменяя параметры распределения, проследить как изменяетсяконтур многоугольника распределения.
Задание 2. Работа уличного агента по приглашению потенциальных покупателей тайм-шер считаетсяудовлетворительной, если по его приглашению за день на презентацию придет более 10 покупателей. Считая, что вероятность того, что лицо, к которому агент обратится с предложением, с вероятностью 0,1 придет на презентацию, вычислить вероятность того, что работаагента будет признана удовлетворительной, если агент обратится с предложением к 40 прохожим.
Для задачи необходимо составление компьютерной модели, выполнение громоздкихрасчетовс помощью функции БИНОМРАСПР.
Для закрепленияраспределения Пуассона можно предложить задание 3.
Задание 3. Устройство состоит из 1000 элементов, работающих независимо один от другого. Вероятность отказа любого элемента в течение времени Т равно 0,002. Найти вероятность того, что за время Т откажут ровно k элементов. Построить график распределения вероятности k =0;1;2;3;4;5;6;7.
Используется функция ПУАССОН:
Рис.2Многоугольник распределения Пуассона ( l =2)
После построения графика целесообразно поставить вопрос : от какого параметра зависит распределение Пуассона?
Задание 4 и 5 на гипергеометрическое распределение, выполнение расчетов и построение графика.
Задание 4. В лотерее "Спортлото 6 из 45 «денежные призы получают участники, угадавшие 3, 4, 5 и 6 видов спорта из отобранныхслучайно 6 видов из 45. Найти закон распределения случайной величины Х- числа угаданных видов спорта среди случайно отобранных шести. Какова вероятность получения денежного приза? Найти математическое ожидание и дисперсию случайной величины Х.
В задании 4строитсямодель, через функцию ГИПЕРГЕОМЕТвыполняются громоздкие вычисления длярядаэтого распределения.
Задание 5. Построить графики гипергеометрического распределения для следующих значений параметров:
Нормальный закон распределенияприменяется взаданиях 6 и 7.
З адание 6.Построить кривую Гаусса для:
а) а=2; s =2; б )а =2; s =1; в)а=2; s =0,5. Сделать выводы по графикам. Найти площадь под каждой кривой Гаусса.
Эта задача подразумевает несколько этапов решения, процесс построениякривой , анализ полученных результатов, их геометрическую интерпретацию.
Задание 7. Полагая, что рост мужчин определенной возрастнойгруппы есть нормально распределеннаяслучайная величина Х с параметрами а=173, , найти:
а) выражение плотности вероятности и функции распределения случайной величины Х;
b ) доли костюмов 4-го роста (176- ) и 3-го роста (170-176см), которыенужно предусмотреть в общем объеме производства для данной возрастной группы;
с) квантиль и 10 % -ную точку случайной величины Х.
В задании 7 необходимо построитьмодель нормального распределения, вероятность попадания в интервал,применить правиланахождения квантиля .
Рис. 3График плотности экспоненциального распределения ( l =3)
Рис.4График интегральной функции экспоненциального распределения ( l =3)
Для решения задач на компьютере преподаватель выполняет отборзаданий для закрепления новых понятий и навыков поэтапно,различнойстепени сложности, а такжепозволяющиеизучить их в динамике. Содержание заданийтребует включения не только фундаментальных положений науки, но и вопросы,связанные с перспективами ее развития,также должно способствоватьсистематизации знаний. Необходимо использовать богатые иллюстрационныеграфические возможности компьютера для представления в наглядной форменекоторого процесса иего изучения. В процессе решения задачформируется их графическое представление,анализируютсяразличные ситуации, строятсякомпьютерные модели,автоматизируются непростые расчеты. Таким образом, компьютер служит инструментом для решениязадач, в том числе профессионально направленных .
Исследователи считают, что применение информационных технологийсущественным образомпреобразует мыслительную деятельность человека [6]. Формируется не только логическое, но икритическое мышление — качества, необходимыедля выработкинового стилямышления, приэтом повышаетсяобщий уровень интеллектуальной деятельности. .
Современный специалистбез знания компьютера и компьютерных технологийне готов к реальной жизни не только профессионально, но и психологически. Студент-первокурсник с помощью Excel делает первые статистические расчеты при изучениитеории вероятностей и математической статистике,в дальнейшем он продолжит эту практику при освоении другихбазовых и специальных дисциплин. Информационная культураспециалиста является одной из составляющих профессионализма, помочь овладеть ее — важнейшая задача высшей школы. Таким образом, педагогически обоснованноеиспользованиеинформационных технологийв учебном процессе вузовобеспечиваетзаинтересованное повышениеконкурентоспособности молодых специалистов на рынке труда.
1. Закон РеспубликиКазахстан «Об образовании» от 7 июня 1999 года, № 389-1 // Казахстанская правда. 11 июня .1999, № 147-148.
2. Heyn Michael, Katrina Lythoe andCharles Meyers,1999. «Education and Economic Development: Sustainability. Threshold and Equity. Proceedings of the Third UNESCO-ACEID International Conference on Educational Innovation for Sustainable Development». UNESCO: .
3. Сережкина А.Е., Садыкова В.А. Обучение в новой информационной среде: психолого-педагогические особенности//Высшее образование сегодня. — 2004. -.№ 1. — с.54-59.
4. Бекбаева З. Роль и функции средств информационной технологии в активизации самостоятельной учебно-познавательной деятельности учащихся.// Поиск, серия ест . н аук. −2001. -№ 6, — с.113-119.
5. Кремер Н.Ш. Теория вероятностейи математическая статистика: Учебник для вузов. — М.: ЮНИТИ-ДАНА, 2006.-573 с.
6. Тихомиров О.К. Бабанин Л.Н. ЭВМи новые проблемы психологии. М.: Изд-во Моск.ун-та, 1986.
Чтобы найти точки пересечения годографа с осями координат, необходимо приравнять нулю вещественную и мнимую части и найти частоты, при которых они равны нулю.
Построение годографа ведется в следующем порядке:
1. В характеристическом уравнении замкнутой системы производим замену на :
2. Из уравнения выделяем вещественную и мнимую части:
— уравнение вещественной части — ;
— уравнение мнимой части — .
3. Приравняем нулю мнимую часть и находим частоты, при которых годограф пересекается с вещественной осью (точки 1 и 3):
4. Полученные значения частоты подставим в уравнение вещественной части, получаем точки 1 и 3:
5. Приравняем нулю уравнение вещественной части, получаем частоты, при которых годограф пересекается с мнимой осью:
Введем новую переменную и получим квадратное уравнение:
Решим квадратное уравнение:
Найдем и (только положительные значения):
6. Полученные значения частоты подставим в уравнение мнимой части и находим точки 2 и 4:
7. Задаются промежуточными частотами и частотой , для которых находят значения вещественной и мнимой части:
8. Все расчеты сводятся в таблицу:
0.23 | 0.46 | 0.68 | 0.89 | 1.2 | 1.51 | 1.6 |
0.74 | -0.88 | -1.71 | -2.1 | 1,31 | ||
0.86 | 1.35 | 1.15 | -3.84 | -11.17 | -14,08 |
9. По данным таблицы строится годограф (рисунок 1).
10. Вывод: Система устойчива, т.к. вектор годографа Михайлова начинает свое движение с положительной вещественной полуоси, вращается против часовой стрелки, нигде не обращается в ноль и обходит последовательно 4 квадранта комплексной плоскости.
Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет
Порядок построения годографа
Частотные критерии устойчивости
Частотные критерии позволяют сравнительно легко исследовать устойчивость систем высокого порядка и дают представления о качестве процесса регулирования.
Частотные критерии используют понятие годографа, т.е. кривой в плоскости с координатами (действительная и мнимые части АФЧХ), которую описывает конец вектора при увеличении частоты от 0 до .
Значения и получают, заменив в характеристическом уравнении (2) постоянную p на переменную .
Характеристическое уравнение представляет собой полином знаменателя передаточной функции замкнутой САР, т.е.
Записать передаточные функции разомкнутой и замкнутой САР. Например: регулятор – П (пропорциональный), объект – А (апериодическое звено).
Записываем характеристическое уравнение
T | 2T |
Критерий Михайлова
Линейная система n-го порядка устойчива, если при изменении от 0 до годограф Михайлова — последовательно обходит n — квадрантов комплексной плоскости против часовой стрелки, начинаясь в точке на положительной вещественной полуоси и нигде не проходя через начало координат.
Чем дальше годограф от нуля, тем система более устойчива.
Порядок построения годографа
1. Откладывается точка при .
2. Увеличивается частота и находятся другие точки.
3. Определяются точки пересечения с осями координат, как корни уравнений: ,
Особенности годографа устойчивых систем
1. Начало в точке , .
2. При вектор поворачивается против часовой стрелки на угол , поочередно обходя n — квадрантов.
3. Модуль вектора должен быть отличен от нуля при любых .
Если годограф проходит через начало координат, то система находится на границе устойчивости.
Найдем точки пересечения с осями координат.
С мнимой осью из уравнения
Подставив значение в мнимую часть, получим точку пересечения с (0;0,26).
С действительной осью из уравнения
. Подставив значение в действительную часть, получим точку пересечения с (0,1;0).
0,32 | 0,5 | 1,25 | |
0,1 | -0,15 | -0,9 | -1,46 |
0,26 | 0,4 | 0,8 |
Найдем точки пересечения с осями координат.
С мнимой осью из уравнения
Подставив значение в мнимую часть, получим точку пересечения с (0;-6,8).
С действительной осью из уравнения
Подставив значение в действительную часть, получим точки пересечения с (3,0) и (2,0)
0,5 | 1,73 | |
2,75 | -1 | |
0,75 | -6,8 | -12 |
| | следующая лекция ==> | |
Возможные виды корней и решения ДУ | | | Область применения частотных критериев |
Частотные критерии позволяют сравнительно легко исследовать устойчивость систем высокого порядка и дают представления о качестве процесса регулирования.
Частотные критерии используют понятие годографа, т.е. кривой в плоскости с координатами (действительная и мнимые части АФЧХ), которую описывает конец вектора при увеличении частоты от 0 до .
Значения и получают, заменив в характеристическом уравнении (2) постоянную p на переменную .
Характеристическое уравнение представляет собой полином знаменателя передаточной функции замкнутой САР, т.е.
Записать передаточные функции разомкнутой и замкнутой САР. Например: регулятор – П (пропорциональный), объект – А (апериодическое звено).
Записываем характеристическое уравнение
T | 2T |
Критерий Михайлова
Линейная система n-го порядка устойчива, если при изменении от 0 до годограф Михайлова — последовательно обходит n — квадрантов комплексной плоскости против часовой стрелки, начинаясь в точке на положительной вещественной полуоси и нигде не проходя через начало координат.
Чем дальше годограф от нуля, тем система более устойчива.
Порядок построения годографа
1. Откладывается точка при .
2. Увеличивается частота и находятся другие точки.
3. Определяются точки пересечения с осями координат, как корни уравнений: ,
Особенности годографа устойчивых систем
1. Начало в точке , .
2. При вектор поворачивается против часовой стрелки на угол , поочередно обходя n — квадрантов.
3. Модуль вектора должен быть отличен от нуля при любых .
Если годограф проходит через начало координат, то система находится на границе устойчивости.
Найдем точки пересечения с осями координат.
С мнимой осью из уравнения
Подставив значение в мнимую часть, получим точку пересечения с (0;0,26).
С действительной осью из уравнения
. Подставив значение в действительную часть, получим точку пересечения с (0,1;0).
0,32 | 0,5 | 1,25 | |
0,1 | -0,15 | -0,9 | -1,46 |
0,26 | 0,4 | 0,8 |
Найдем точки пересечения с осями координат.
С мнимой осью из уравнения
Подставив значение в мнимую часть, получим точку пересечения с (0;-6,8).
С действительной осью из уравнения
Подставив значение в действительную часть, получим точки пересечения с (3,0) и (2,0)
0,5 | 1,73 | |
2,75 | -1 | |
0,75 | -6,8 | -12 |
| | следующая лекция ==> | |
Возможные виды корней и решения ДУ | | | Область применения частотных критериев |
Практическое построение годографа Михайлова
Для примера рассмотрим систему 4 ой степени:
Чтобы найти точки пересечения годографа с осями координат, необходимо приравнять нулю вещественную и мнимую части и найти частоты, при которых они равны нулю.
Построение годографа ведется в следующем порядке:
1. В характеристическом уравнении замкнутой системы производим замену на :
2. Из уравнения выделяем вещественную и мнимую части:
— уравнение вещественной части — ;
— уравнение мнимой части — .
3. Приравняем нулю мнимую часть и находим частоты, при которых годограф пересекается с вещественной осью (точки 1 и 3):
4. Полученные значения частоты подставим в уравнение вещественной части, получаем точки 1 и 3:
5. Приравняем нулю уравнение вещественной части, получаем частоты, при которых годограф пересекается с мнимой осью:
Введем новую переменную и получим квадратное уравнение:
Решим квадратное уравнение:
Найдем и (только положительные значения):
6. Полученные значения частоты подставим в уравнение мнимой части и находим точки 2 и 4:
7. Задаются промежуточными частотами и частотой , для которых находят значения вещественной и мнимой части:
8. Все расчеты сводятся в таблицу:
0.23 | 0.46 | 0.68 | 0.89 | 1.2 | 1.51 | 1.6 |
0.74 | -0.88 | -1.71 | -2.1 | 1,31 | ||
0.86 | 1.35 | 1.15 | -3.84 | -11.17 | -14,08 |
9. По данным таблицы строится годограф (рисунок 1).
10. Вывод: Система устойчива, т.к. вектор годографа Михайлова начинает свое движение с положительной вещественной полуоси, вращается против часовой стрелки, нигде не обращается в ноль и обходит последовательно 4 квадранта комплексной плоскости.
Очень давно не писал блог. Расслабился совсем. Ну ничего, исправляюсь.
Продолжаю новую рубрику блога, посвященную анализу данных с помощью всем известного Microsoft Excel.
В современном мире к статистике проявляется большой интерес, поскольку это отличный инструмент для анализа и принятия решений, а также это отличное средство для поиска причин нарушений процесса и их устранения. Статистический анализ применим во многих сферах, где существуют большие массивы данных: естественно, в первую очередь я скажу, что металлургии, а также в экономике, биологии, политике, социологии и… много где еще. Статья эта будет, как несложно догадаться по ее названию, про использование некоторых средств статистического анализа, а именно — гистограммам.
Ну, поехали.
Статистический анализ в Excel можно осуществлять двумя способами:
• С помощью функций
• С помощью средств надстройки «Пакет анализа». Ее, как правило, еще необходимо установить.
Чтобы установить пакет анализа в Excel, выберите вкладку «Файл» (а в Excel 2007 это круглая цветная кнопка слева сверху), далее — «Параметры», затем выберите раздел «Надстройки». Нажмите «Перейти» и поставьте галочку напротив «Пакет анализа».
А теперь — к построению гистограмм распределения по частоте и их анализу.
Речь пойдет именно о частотных гистограммах, где каждый столбец соответствует частоте появления* значения в пределах границ интервалов. Например, мы хотим посмотреть, как у нас выглядит распределение значения предела текучести стали S355J2 в прокате толщиной 20 мм за несколько месяцев. В общем, хотим посмотреть, похоже ли наше распределение на нормальное (а оно должно быть таким).
*Примечание: для металловедческих целей типа оценки размера зерна или оценки объемной доли частиц этот вид гистограмм не пойдет, т.к. там высота столбика соответствует не частоте появления частиц определенного размера, а доле объема (а в плоскости шлифа — площади), которую эти частицы занимают.
График нормального распределения выглядит следующим образом:
График функции Гаусса
Мы знаем, что реально такой график может быть получен только при бесконечно большом количестве измерений. Реально же для конечного числа измерений строят гистограмму, которая внешне похожа на график нормального распределения и при увеличении количества измерений приближается к графику нормального распределения (распределения Гаусса).
Построение гистограмм с помощью программ типа Excel является очень быстрым способом проверки стабильности работы оборудования и добросовестности коллектива: если получим «кривую» гистограмму, значит, либо прибор не исправен или мы данные неверно собрали, либо кто-то где-то преднамеренно мухлюет или же просто неверно использует оборудование.
А теперь — построение гистограмм!
Способ 1-ый. Халявный.
- Идем во вкладку «Анализ данных» и выбираем «Гистограмма».
- Выбираем входной интервал.
- Здесь же предлагается задать интервал карманов, т.е. те диапазоны, в пределах которых будут лежать наши значения. Чем больше значений в интервале — тем выше столбик гистограммы. Если мы оставим поле «Интервалы карманов» пустым, то программа вычислит границы интервалов за нас.
- Если хотим сразу же вывести график,то ставим галочку напротив «Вывод графика».
- Нажимаем «ОК».
- Вот, вроде бы, и все: гистограмма готова. Теперь нужно сделать так, чтобы по вертикальной оси отображалась не абсолютная частота, а относительная.
- Под появившейся таблицей со столбцами «Карман» и «Частота» под столбцом «Частота» введем формулу «=СУММ» и сложим все абсолютные частоты.
- К появившейся таблице со столбцами «Карман» и «Частота» добавим еще один столбец и назовем его «Относительная частота».
- Во всех ячейках нового столбца введем формулу, которая будет рассчитывать относительную частоту: 100 умножить на абсолютную частоту (ячейка из столбца «частота») и разделить на сумму, которую мы вычислил в п. 7.
Способ 2-ой. Трудный, но интересный.
Будет полезен тому, кто по каким-либо причинам не смог установить Пакет анализа.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
Так как я часто имею дело с большим количеством данных, у меня время от времени возникает необходимость генерировать массивы значений для проверки моделей в Excel. К примеру, если я хочу увидеть распределение веса продукта с определенным стандартным отклонением, потребуются некоторые усилия, чтобы привести результат работы формулы СЛУЧМЕЖДУ() в нормальный вид. Дело в том, что формула СЛУЧМЕЖДУ() выдает числа с единым распределением, т.е. любое число с одинаковой долей вероятности может оказаться как у нижней, так и у верхней границы запрашиваемого диапазона. Такое положение дел не соответствует действительности, так как вероятность возникновения продукта уменьшается по мере отклонения от целевого значения. Т.е. если я произвожу продукт весом 100 грамм, вероятность, что я произведу 97-ми или 103-граммовый продукт меньше, чем 100 грамм. Вес большей части произведенной продукции будет сосредоточен рядом с целевым значением. Такое распределение называется нормальным. Если построить график, где по оси Y отложить вес продукта, а по оси X – количество произведенного продукта, график будет иметь колоколообразный вид, где наивысшая точка будет соответствовать целевому значению.
Таким образом, чтобы привести массив, выданный формулой СЛУЧМЕЖДУ(), в нормальный вид, мне приходилось ручками исправлять пограничные значения на близкие к целевым. Такое положение дел меня, естественно, не устраивало, поэтому, покопавшись в интернете, открыл интересный способ создания массива данных с нормальным распределением. В сегодняшней статье описан способ генерации массива и построения графика с нормальным распределением.
Характеристики нормального распределения
Непрерывная случайная переменная, которая подчиняется нормальному распределению вероятностей, обладает некоторыми особыми свойствами. Предположим, что вся производимая продукция подчиняется нормальному распределению со средним значением 100 грамм и стандартным отклонением 3 грамма. Распределение вероятностей для такой случайной переменной представлено на рисунке.
Из этого рисунка мы можем сделать следующие наблюдения относительно нормального распределения — оно имеет форму колокола и симметрично относительно среднего значения.
Стандартное отклонение имеет немаловажную роль в форме изгиба. Если посмотреть на предыдущий рисунок, то можно заметить, что практически все измерения веса продукта попадают в интервал от 95 до 105 граммов. Давайте рассмотрим следующий рисунок, на котором представлено нормальное распределение с той же средней – 100 грамм, но со стандартным отклонением всего 1,5 грамма
Здесь вы видите, что измерения значительно плотней прилегают к среднему значению. Почти все производимые продукты попадают в интервал от 97 до 102 грамм.
Небольшое значение стандартного отклонения выражается в более «тощей и высокой кривой, плотно прижимающейся к среднему значению. Чем больше стандартное, тем «толще», ниже и растянутее получается кривая.
Создание массива с нормальным распределением
Итак, чтобы сгенерировать массив данных с нормальным распределением, нам понадобится функция НОРМ.ОБР() – это обратная функция от НОРМ.РАСП(), которая возвращает нормально распределенную переменную для заданной вероятности для определенного среднего значения и стандартного отклонения. Синтаксис формулы выглядит следующим образом:
=НОРМ.ОБР(вероятность; среднее_значение; стандартное_отклонение)
Другими словами, я прошу Excel посчитать, какая переменная будет находится в вероятностном промежутке от 0 до 1. И так как вероятность возникновения продукта с весом в 100 грамм максимальная и будет уменьшаться по мере отдаления от этого значения, то формула будет выдавать значения близких к 100 чаще, чем остальных.
Давайте попробуем разобрать на примере. Выстроим график распределения вероятностей от 0 до 1 с шагом 0,01 для среднего значения равным 100 и стандартным отклонением 1,5.
Как видим из графика точки максимально сконцентрированы у переменной 100 и вероятности 0,5.
Этот фокус мы используем для генерирования случайного массива данных с нормальным распределением. Формула будет выглядеть следующим образом:
=НОРМ.ОБР(СЛЧИС(); среднее_значение; стандартное_отклонение)
Создадим массив данных для нашего примера со средним значением 100 грамм и стандартным отклонением 1,5 грамма и протянем нашу формулу вниз.
Теперь, когда массив данных готов, мы можем выстроить график с нормальным распределением.
Построение графика нормального распределения
Прежде всего необходимо разбить наш массив на периоды. Для этого определяем минимальное и максимальное значение, размер каждого периода или шаг, с которым будет увеличиваться период.
Далее строим таблицу с категориями. Нижняя граница (B11) равняется округленному вниз ближайшему кратному числу. Остальные категории увеличиваются на значение шага. Формула в ячейке B12 и последующих будет выглядеть:
В столбце X будет производится подсчет количества переменных в заданном промежутке. Для этого воспользуемся формулой ЧАСТОТА(), которая имеет два аргумента: массив данных и массив интервалов. Выглядеть формула будет следующим образом =ЧАСТОТА(Data!A1:A175;B11:B20). Также стоит отметить, что в таком варианте данная функция будет работать как формула массива, поэтому по окончании ввода необходимо нажать сочетание клавиш Ctrl+Shift+Enter.
Таким образом у нас получилась таблица с данными, с помощью которой мы сможем построить диаграмму с нормальным распределением. Воспользуемся диаграммой вида Гистограмма с группировкой, где по оси значений будет отложено количество переменных в данном промежутке, а по оси категорий – периоды.
Осталось отформатировать диаграмму и наш график с нормальным распределением готов.
Итак, мы познакомились с вами с нормальным распределением, узнали, что Excel позволяет генерировать массив данных с помощью формулы НОРМ.ОБР() для определенного среднего значения и стандартного отклонения и научились приводить данный массив в графический вид.
Для лучшего понимания, вы можете скачать файл с примером построения нормального распределения.
Построим диаграмму распределения в Excel. А также рассмотрим подробнее функции круговых диаграмм, их создание.
Как построить диаграмму распределения в Excel
График нормального распределения имеет форму колокола и симметричен относительно среднего значения. Получить такое графическое изображение можно только при огромном количестве измерений. В Excel для конечного числа измерений принято строить гистограмму.
Внешне столбчатая диаграмма похожа на график нормального распределения. Построим столбчатую диаграмму распределения осадков в Excel и рассмотрим 2 способа ее построения.
Имеются следующие данные о количестве выпавших осадков:
Первый способ. Открываем меню инструмента «Анализ данных» на вкладке «Данные» (если у Вас не подключен данный аналитический инструмент, тогда читайте как его подключить в настройках Excel):
Задаем входной интервал (столбец с числовыми значениями). Поле «Интервалы карманов» оставляем пустым: Excel сгенерирует автоматически. Ставим птичку около записи «Вывод графика»:
После нажатия ОК получаем такой график с таблицей:
В интервалах не очень много значений, поэтому столбики гистограммы получились низкими.
Теперь необходимо сделать так, чтобы по вертикальной оси отображались относительные частоты.
Найдем сумму всех абсолютных частот (с помощью функции СУММ). Сделаем дополнительный столбец «Относительная частота». В первую ячейку введем формулу:
Способ второй. Вернемся к таблице с исходными данными. Вычислим интервалы карманов. Сначала найдем максимальное значение в диапазоне температур и минимальное.
Чтобы найти интервал карманов, нужно разность максимального и минимального значений массива разделить на количество интервалов. Получим «ширину кармана».
Представим интервалы карманов в виде столбца значений. Сначала ширину кармана прибавляем к минимальному значению массива данных. В следующей ячейке – к полученной сумме. И так далее, пока не дойдем до максимального значения.
Для определения частоты делаем столбец рядом с интервалами карманов. Вводим функцию массива:
Вычислим относительные частоты (как в предыдущем способе).
Построим столбчатую диаграмму распределения осадков в Excel с помощью стандартного инструмента «Диаграммы».
Частота распределения заданных значений:
Круговые диаграммы для иллюстрации распределения
С помощью круговой диаграммы можно иллюстрировать данные, которые находятся в одном столбце или одной строке. Сегмент круга – это доля каждого элемента массива в сумме всех элементов.
С помощью любой круговой диаграммы можно показать распределение в том случае, если
- имеется только один ряд данных;
- все значения положительные;
- практически все значения выше нуля;
- не более семи категорий;
- каждая категория соответствует сегменту круга.
На основании имеющихся данных о количестве осадков построим круговую диаграмму.
Доля «каждого месяца» в общем количестве осадков за год:
Круговая диаграмма распределения осадков по сезонам года лучше смотрится, если данных меньше. Найдем среднее количество осадков в каждом сезоне, используя функцию СРЗНАЧ. На основании полученных данных построим диаграмму:
Получили количество выпавших осадков в процентном выражении по сезонам.
В двух словах: Добавляем полосу прокрутки к гистограмме или к графику распределения частот, чтобы сделать её динамической или интерактивной.
Уровень сложности: продвинутый.
На следующем рисунке показано, как выглядит готовая динамическая гистограмма:
Что такое гистограмма или график распределения частот?
Гистограмма распределения разбивает по группам значения из набора данных и показывает количество (частоту) чисел в каждой группе. Такую гистограмму также называют графиком распределения частот, поскольку она показывает, с какой частотой представлены значения.
В нашем примере мы делим людей, которые вызвались принять участие в мероприятии, по возрастным группам. Первым делом, создадим возрастные группы, далее подсчитаем, сколько людей попадает в каждую из групп, и затем покажем все это на гистограмме.
На какие вопросы отвечает гистограмма распределения?
Гистограмма – это один из моих самых любимых типов диаграмм, поскольку она дает огромное количество информации о данных.
В данном случае мы хотим знать, как много участников окажется в возрастных группах 20-ти, 30-ти, 40-ка лет и так далее. Гистограмма наглядно покажет это, поэтому определить закономерности и отклонения будет довольно легко.
«Неужели наше мероприятие не интересно гражданам в возрасте от 20 до 29 лет?»
Возможно, мы захотим немного изменить детализацию картины и разбить население на две возрастные группы. Это покажет нам, что в мероприятии примут участие большей частью молодые люди:
Динамическая гистограмма
После построения гистограммы распределения частот иногда возникает необходимость изменить размер групп, чтобы ответить на различные возникающие вопросы. В динамической гистограмме это возможно сделать благодаря полосе прокрутки (слайдеру) под диаграммой. Пользователь может увеличивать или уменьшать размер групп, нажимая стрелки на полосе прокрутки.
Такой подход делает гистограмму интерактивной и позволяет пользователю масштабировать ее, выбирая, сколько групп должно быть показано. Это отличное дополнение к любому дашборду!
Как это работает?
Краткий ответ: Формулы, динамические именованные диапазоны, элемент управления «Полоса прокрутки» в сочетании с гистограммой.
Формулы
Чтобы всё работало, первым делом нужно при помощи формул вычислить размер группы и количество элементов в каждой группе.
Чтобы вычислить размер группы, разделим общее количество (80-10) на количество групп. Количество групп устанавливается настройками полосы прокрутки. Чуть позже разъясним это подробнее.
Далее при помощи функции ЧАСТОТА (FREQUENCY) я рассчитываю количество элементов в каждой группе в заданном столбце. В данном случае мы возвращаем частоту из столбца Age таблицы с именем tblData.
Функция ЧАСТОТА (FREQUENCY) вводится, как формула массива, нажатием Ctrl+Shift+Enter.
Динамический именованный диапазон
В качестве источника данных для диаграммы используется именованный диапазон, чтобы извлекать данные только из выбранных в текущий момент групп.
Когда пользователь перемещает ползунок полосы прокрутки, число строк в динамическом диапазоне изменяется так, чтобы отобразить на графике только нужные данные. В нашем примере задано два динамических именованных диапазона: один для данных — rngGroups (столбец Frequency) и второй для подписей горизонтальной оси — rngCount (столбец Bin Name).
Элемент управления «Полоса прокрутки»
Элемент управления Полоса прокрутки (Scroll Bar) может быть вставлен с вкладки Разработчик (Developer).
На рисунке ниже видно, как я настроил параметры элемента управления и привязал его к ячейке C7. Так, изменяя состояние полосы прокрутки, пользователь управляет формулами.
Гистограмма
График – это самая простая часть задачи. Создаём простую гистограмму и в качестве источника данных устанавливаем динамические именованные диапазоны.
Есть вопросы?
Что ж, это был лишь краткий обзор того, как работает динамическая гистограмма.
Да, это не самая простая диаграмма, но, полагаю, пользователям понравится с ней работать. Определённо, такой интерактивной диаграммой можно украсить любой отчёт.
Более простой вариант гистограммы можно создать, используя сводные таблицы.
Пишите в комментариях любые вопросы и предложения. Спасибо!
Для оценки уровня неравенства между различными слоями населения общества часто используют кривую Лоренца и производный от неё показатель — коэффициент Джинни. С помощью них можно определить, насколько велик социальный разрыв в обществе между самыми богатыми и наиболее бедными слоями населения. С помощью инструментов приложения Excel можно значительно облегчить процедуру построения кривой Лоренца. Давайте, разберемся, как в среде Эксель это можно осуществить на практике.
Использование кривой Лоренца
Кривая Лоренца представляет собой типичную функцию распределения, отображенную графически. По оси X данной функции располагается количество населения в процентном соотношении по нарастающей, а по оси Y — общее количество национального дохода. Собственно, сама кривая Лоренца состоит из точек, каждая из которых соответствует процентному соотношению уровня дохода определенной части общества. Чем больше изогнута линия Лоренца, тем больше в обществе уровень неравенства.
В идеальной ситуации, при которой отсутствует общественное неравенство, каждая группа населения имеет уровень дохода прямо пропорциональный её численности. Линия, характеризующая такую ситуацию, называется кривой равенства, хотя она и представляет собой прямую. Чем больше площадь фигуры, ограниченной кривой Лоренца и кривой равенства, тем выше уровень неравенства в обществе.
Кривая Лоренца может использоваться не только для определения ситуации имущественного расслоения в мире, в конкретной стране или в обществе, но и для сравнения в данном аспекте отдельных домохозяйств.
Вертикальная прямая, которая соединяет линию равенства и наиболее удаленную от неё точку кривой Лоренца, называется индексом Гувера или Робин Гуда. Данный отрезок показывает, какую величину дохода нужно перераспределить в обществе, чтобы достичь полного равенства.
Уровень неравенства в обществе определяется с помощью индекса Джинни, который может варьироваться от 0 до 1. Он ещё называется коэффициентом концентрации доходов.
Построение линии равенства
Теперь давайте на конкретном примере посмотрим, как создать линию равенства и кривую Лоренца в Экселе. Для этого используем таблицу количества населения разбитого на пять равных групп (по 20%), которые суммируются в таблице по нарастающей. Во второй колонке этой таблицы представлена величина национального дохода в процентном соотношении, которая соответствует определенной группе населения.
Для начала построим линию абсолютного равенства. Она будет состоять из двух точек — нулевой и точки суммарного национального дохода для 100% населения.
- Переходим во вкладку «Вставка». На линии в блоке инструментов «Диаграммы» жмем на кнопку «Точечная». Именно данный тип диаграмм подойдет для нашей задачи. Далее открывается список подвидов диаграмм. Выбираем «Точечная с гладкими кривыми и маркерами».
В поле «Значения X» следует указать координаты точек диаграммы по оси X. Как мы помним, их будет всего две: 0 и 100. Записываем данные значения через точку с запятой в данном поле.
В поле «Значения Y» следует записать координаты точек по оси Y. Их тоже будет две: 0 и 35,9. Последняя точка, как мы можем видеть по графику, соответствует совокупному национальному доходу 100% населения. Итак, записываем значения «0;35,9» без кавычек.
После того, как все указанные данные внесены, жмем на кнопку «OK».
Урок: Как сделать диаграмму в Экселе
Создание кривой Лоренца
Теперь нам предстоит непосредственно построить кривую Лоренца, опираясь на табличные данные.
-
Кликаем правой кнопкой мыши по области диаграммы, на которой уже расположена линия равенства. В запустившемся меню снова останавливаем выбор на пункте «Выбрать данные. ».
В поле «Значения X» следует занести все данные столбца «% населения» нашей таблицы. Для этого устанавливаем курсор в область поля. Далее зажимаем левую кнопку мыши и выделяем соответствующий столбец на листе. Координаты тут же будут отображены в окне изменения ряда.
В поле «Значения Y» заносим координаты ячеек столбца «Сумма национального дохода». Делаем это по той же методике, по которой вносили данные в предыдущее поле.
После того, как все вышеуказанные данные внесены, жмем на кнопку «OK».
Построение кривой Лоренца и линии равенства в Экселе производится на тех же принципах, что и построение любого другого вида диаграмм в этой программе. Поэтому для пользователей, которые овладели умением строить диаграммы и графики в Excel, данная задача не должна вызвать больших проблем.
Читайте также: