Author: Мхитарян В.С. Козлов А.Ю. Шишов В.Ф.
Tags: экономика народное хозяйство экономические науки экономическая статистика статистика программное обеспечение учебное пособие excel компьютерные технологии
ISBN: 5-238-00535-0
Year: 2003
А.Ю. КОЗЛОВ, B.C. МХИТАРЯН, В.Ф. ШИШОВ СТАТИСТИЧЕСКИЕ ФУНКЦИИ MS Excel В ЭКОНОМИКО- СТАТИСТИЧЕСКИХ РАСЧЕТАХ Под редакцией профессора B.C. Мхитаряна Рекомендовано Учебно-методическим объединением по образованию в области математических методов в экономике в качестве учебного пособия для студентов высших учебных заведений, обучающихся по специальности 061800 «Математические методы в экономике» и другим экономическим специальностям Рекомендовано Учебно-методическим центром «Профессиональный учебник» в качестве учебного пособия для студентов экономических вузов и специальностей юн ити UNITY Москва . 2003
УДК [31:33]004.4(075.8) ББК 65.051в6я73 К59 Рецензенты: кафедра экономических "информационных систем и информационных технологий Московского государственного университета экономики, статистики и информатики (зав кафедрой д-р экон. наук, проф. В П. Божко); д-р техн. наук, проф. Т.П. Шлыков (Пензенский государственный университет) Главный редактор издательства доктор экономических наук Н.Д. Эриашвили Козлов А.Ю., Мхитарян B.C., Шишов В.Ф. К59 Статистические функции MS Excel в экономико-статистических расчетах: Учеб. пособие для вузов /Под ред. проф. B.C. Мхитаряна. - М.: ЮНИТИ-ДАНА, 2003. - 231с. ISBN 5-238-00535-0 Это вторая из двух книг, посвященных применению пакета MS Excel (см.: Козлов А.Ю., Шишов В.Ф Применение пакета анализа MS Excel в экономико-статистических расчетах. ЮНИТИ, 2003). Учебное пособие содержит полное и подробное описание всех статистических функций, входящих в MS Excel. Достаточно подробно изложены необходимые теоретические основы, приведены формульные зависимости, используемые для расчета различных параметров, необходимые сведения по основам работы в Excel. Все рассмотренные вопросы сопровождаются примерами решения конкретных задач (экономика, статистика, теория вероятностей),. Для студентов, аспирантов, преподавателей и практических работников, занимающихся вопросами анализа и обработки статистической информации. ББК 65.051в6я73 ISBN 5-238-00535-0 © А Ю. Козлов, B.C. Мхитарян, В.Ф. Шишов, 2003 © ИЗДАТЕЛЬСТВО ЮНИТИ-ДАНА, 2003. Воспроизведение всей книги или любой ее части запрещается без письменного разрешения издательства Оглавление Введение j 1. Необходимые сведения о MS Excel 9 1.1. Основные положения работы с электронной таблицей 9 1.2. Мастер функций 22 1.3. Работа с массивами 23 1.4. Некоторые замечания об использовании статистических функций 28 2. Предварительная обработка статистических данных 32 2.1. Подсчет количества значений. Функции СЧЕТ, СЧЕТЗ, СЧЕТЕСЛИ, СЧИТАТЬПУСТОТЫ 32 2.2. Определение экстремальных значений совокупности данных. Функции МАКС, МИН, МАКСА, МИНА, НАИБОЛЬШИЙ, НАИМЕНЬШИЙ 36 2.3. Подсчет частот из массива данных, попадающих в заданные интервалы. Функция ЧАСТОТА 42 2.4. Оценка относительного положения точки. Функция ПРОЦЕНТРАНГ 45 2.5. Определение величины, соответствующей ее относительному положению. Функция ПЕРСЕНТИЛЬ 47 2.6. Определение числа перестановок. Функция ПЕРЕСТ 49 2.7. Определение ранга числа в списке чисел. Функция РАНГ 51 3. Определение характеристик положения 54 3.1. Вычисление среднего. Функции СРЗНАЧ, СРЗНАЧА, СРГЕОМ, СРГАРМ, УРЕЗСРЕДНЕЕ 54 3.2. Определение моды в интервале данных или массиве. Функция МОДА 61 3.3. Определение медианы. Функция МЕДИАНА 62 3.4. Определение квартилей. Функция КВАРТИЛЬ 63 4. Определение характеристик рассеивания 66 4.1. Определение среднего отклонения. Функция СРОТКЛ 66 4.2. Определение суммы квадратов отклонений. Функция КВАДРОТКЛ 67 4.3. Вычисление дисперсии. Функции ДИСП, ДИСПА, ДИСПР, ДИСПРА 69 4.4. Вычисление стандартного (среднего квадратического) отклонения. Функции СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА 73 4.5. Определение асимметрии распределения. Функция СКОС 78 4.6. Определение эксцесса распределения. Функция ЭКСЦЕСС 80 %
5. Зависимость случайных величин 83 ^ 5.1. Определение ковариации. Функция КОВАР 83 5.2. Определение коэффициента корреляции. Функция КОРРЕЛ 85 5.3. Определение коэффициента корреляции Пирсона. Функции ПИРСОН, КВПИРСОН 87 6. Интервальное оценивание 92 6.1. Определение доверительного интервала для среднего. Функция ДОВЕРИТ 92 6.2. Определение вероятности попадания дискретной случайной величины в интервал. Функция ВЕРОЯТНОСТЬ 95 7. Определение параметров распределений непрерывных случайных величин 98 7.1. Определение значения функции распределения и функции плотности нормального распределения. Функция НОРМРАСП 98 7.2. Определение аргумента по значению функции распределения. Функция НОРМОБР 101 7.3. Определение нормализованного значения аргумента. Функция НОРМАЛИЗАЦИЯ 103 7.4. Определение значения функции распределения стандартного нормального распределения. Функция НОРМСТРАСП 105 7.5. Определение аргумента по значению стандартной интегральной функции нормального распределения. Функция НОРМСТОБР 106 7.6. Определение вероятности статистики Z при проверке гипотезы о равенстве статистической оценки математического ожидания заданному значению. Функция ZTECT 107 7.7. Определение значения функции распределения логнормального распределения. Функция ЛОГНОРМРАСП ПО 7.8. Определение аргумента по значению функции распределения. Функция ЛОГНОРМОБР 113 7.9. Определение значения функции распределения Стьюдента (интегральной функции). Функция СТЬЮДРАСП 115 7.10. Определение параметра t по значению функции распределения. Функция СТЬЮДРАСПОБР 118 7.11. Проверка гипотезы о равенстве средних (определение вероятности, соответствующей критерию Стьюдента). Функция ТТЕСТ 120 7.12. Определение значения функции распределения %2. Функция ХИ2РАСП 130 7.13. Определение параметра у по значению функции распределения у}. Функция ХИ20БР 133 7.14. Проверка гипотезы о виде закона распределения (определение вероятности значения %2). Функция ХИ2ТЕСТ 134 А 7.15. Определение значения функции распределения /'-распределения (распределения Фишера-Снедекора). Функция ЕРАСП 142 7.16. Определение параметра х по значению функции распределения f-распределения. Функция ЕРАСПОБР 145 7.17. Проверка гипотезы о равенстве дисперсий (определение вероятности статистики F). Функция ФТЕСТ 147 7.18. Определение значения преобразования Фишера. Функция ФИШЕР 151 7.19. Определение обратного преобразования Фишера. Функция ФИШЕРОБР 152 7.20. Определение значения функции распределения и функции плотности экспоненциального распределения. Функция ЭКСПРАСП 154 7.21. Определение значения функции распределения и функции плотности гамма — распределения. Функция ГАММАРАСП 156 7.22. Определение аргумента по значению функции распределения. Функция ГАММАОБР 159 7.23. Определение натурального логарифма гамма — функции. Функция ГАММАНЛОГ 161 7.24. Определение функции распределения бета — распределения. Функция БЕТАРАСП 162 7.25. Определение аргумента по значению функции бета — распределения. Функция БЕТАОБР 165 7.26. Определение значения функции распределения и функции плотности распределения Вейбулла. Функция ВЕЙБУЛЛ 167 8. Определение параметров распределений дискретных случайных величин 171 8.1. Определение вероятности возможного значения и функции распределения случайной величины, имеющей биномиальное распределение. Функция БИНОМРАСП 171 8.2. Определение наименьшего значения биномиальной случайной величины. Функция КРИТБИНОМ 174 8.3. Определение вероятности числа неудач в последовательности испытаний Бернулли. Функция ОТРБИНОМРАСП 176 8.4. Определение вероятности возможного значения и функции распределения случайной величины, имеющей распределение Пуассона. Функция ПУАССОН 178 8.5. Определение вероятности заданного количества успехов в выборке. Функция ГИПЕРГЕОМЕТ 180 9. Построение уравнения регрессии и прогнозирование /• 184 9.1. Определение параметров линейной регрессии. Функция ЛИНЕЙН 184 s.
9.2. Определения значений результативного признака по линейному уравнению регрессии. Функция ТЕНДЕНЦИЯ 194 9.3. Определение параметров показательной функции. Функция ЛГРФПРИБЛ 198 9.4. Определение значений результативного признака по показательному уравнению регрессии. Функция РОСТ 204 9.5. Определение значения уравнения регрессии вида у = bo + b\X в заданной точке. Функция ПРЕДСКАЗ 208 9.6. Определение точки пересечения линии регрессии с осью Y. Функция ОТРЕЗОК 211 9.7. Определение тангенса угла наклона линии регрессии к оси X. Функция НАКЛОН 213 9.8. Определение стандартной ошибки отклонения результативного признака от уравнения регрессии. Функция СТОШУХ 215 Библиографический список 218 Приложение 219 Введение В настоящее время трудно себе представить исследование и прогнозирование экономических явлений без использования математической статистики, эконометрического моделирования, регрессионного и корреляционного анализа, трендовых и сглаживающих моделей и других методов, опирающихся на вероятностно-статистические закономерности, присущие как централизованной, так и рыночной экономике. С развитием общества экономическая система все более усложняется. Следовательно, должен усиливаться статистический характер законов, описывающих социально-экономические явления. Все это предопределяет необходимость овладения методами теории вероятностей и математической статистики как инструментом статистического анализа и прогнозирования экономических явлений и процессов, а компьютерные программы для аналитических исследований и прогнозирования должны являться повседневным рабочим инструментом специалиста, связанного с обработкой статистической информации. В настоящее время наиболее популярно программное обеспечение, работающее в операционной системе Windows и поставляемое вместе с компьютером. Одна из составляющих этого обеспечения — программа Microsoft Excel, с помощью которой удобно работать с таблицами статистических данных. Она позволяет упорядочивать, обрабатывать, графически представлять и анализировать различную статистическую информацию. MS Excel содержит много встроенных функций, их использование значительно облегчает обработку статистической информации. В данной книге подробно рассмотрены статистические функции, с помощью которых достаточно просто и удобно проводить экономические и статистические расчеты. Все статистические функции (80 функций) распределены на восемь разделов. Статистические функции каждого из разделов позволяют выполнять следующие действия: • проводить предварительную обработку данных; • рассчитывать характеристики положения и рассеивания; • определять зависимость определяемых величин; • проводить интервальное оценивание; • определять параметры законов распределения непрерывных случайных величин; • проверять статистические гипотезы о параметрах распределения и виде закона распределения случайных величин; • определять параметры законов распределения дискретных случайных величин; • строить линейные и нелинейные уравнения регрессии, проводить их анализ и давать прогнозные оценки результативного признака. Для новичков, впервые встречающихся с MS Excel, в книге имеется раздел, посвященный основам работы в Excel. Он составлен таким об-
разом, что за минимальное время можно овладеть основными практическими навыками работы с Excel и всем, что необходимо для работы со статистическими функциями. В данной книге при рассмотрении той или иной статистической функции MS Excel подробно изложены теоретические основы данного вопроса, приведены формульные зависимости, используемые для расчета различных параметров, приведен пример, который решен «вручную» и с помощью соответствующей статистической функции. Такой подход дает возможность пользователю понять, каким образом решаются различные статистические и экономические задачи с помощью статистических функций, правильно интерпретировать и анализировать полученные результаты, делать обоснованные выводы. Учебный материал в книге изложен так, что читатель может освоить его, последовательно изучая раздел за разделом. При необходимости изучение материала можно начать с любого раздела или с любой статистической функции. Для более полного представления о возможностях Excel по обработке статистических данных читателю будет полезно ознакомиться с ранее изданной книгой Козлова А.Ю., Шишова В.Ф. «Применение Пакета анализа MS Excel в экономико-статистических расчетах», в которой рассматриваются статистические инструменты Пакета анализа MS Excel, позволяющие: • генерировать случайные числа, подчиняющиеся различным законам распределения; • проводить формирование различных выборок; • по выборке строить вариационные ряды, гистограмму, кумулятивную кривую, диаграмму Парето; • вычислять точечные и интервальные оценки статистической совокупности; • проводить сглаживание временных рядов; • проверять статистические гипотезы; • проводить однофакторный и двухфакторный дисперсионный анализ; • строить и проводить анализ множественного уравнения регрессии; • проводить прямое и обратное преобразование Фурье. 1. Необходимые сведения о MS Excel В этой главе изложены сведения, необходимые для использования статистических функций в экономических и статистических расчетах. При этом в качестве базовой версии будем использовать русскую версию Microsoft Excel 2000. Excel относится к программным продуктам, известным под названием электронные таблицы. Электронные таблицы — это интерактивная программа, состоящая из набора строк и столбцов, изображенных на экране в специальном окне. Область, находящаяся на пересечении строки и столбца, называется ячейкой. В ячейке могут находиться число, текст или формула, с помощью которой осуществляются вычисления, относящиеся к одной или нескольким ячейкам. Ячейки можно копировать, перемещать, а также изменять их содержимое. При изменении содержимого ячейки производится автоматический пересчет содержимого всех ячеек, использующих в формулах измененную ячейку. На основе групп ячеек создаются диаграммы, графики и сводные таблицы. Электронную таблицу можно сохранить в отдельном файле для дальнейшего использования. Для начала работы необходимо запустить Excel. Это можно сделать, нажав кнопку Пуск и выбрав категорию Программы Microsoft Excel или с помощью иконки на рабочем столе Windows. 1.1. Основные положения работы с электронной таблицей После запуска на экране появится окно программы Excel (рис. 1.1). Рассмотрим основные элементы этого окна, которыми мы будем пользоваться при использовании статистических функций Microsoft Excel. При запуске Excel автоматически создается файл, называемый Книга (на рис. 1.1 — Книга 1). Книга состоит из листов, число которых обычно равно 16. Каждому листу можно присвоить имя, оно указывается на ярлычке листа. При первом запуске имена листов — Лист 1, Лист 2 и т.д. Щелкнув мышью на нужном ярлычке, на экран выводится соответствующий рабочий лист. Когда рабочих листов в книге много, все ярлычки не помещаются на экране. С помощью кнопок в левом нижнем углу экрана можно прокручивать ярлычки
горизонтально, чтобы найти нужный. Каждый лист — это электронная таблица, являющаяся элементом одного файла-книги. Электронная таблица состоит из строк и столбцов; строки нумеруются 1, 2, ..., 16394; столбцы обозначаются буквами А, В, ..., AA, AB,...; всего столбцов 256. На пересечении строк и столбцов находятся ячейки. Каждая ячейка имеет свой адрес: Al B18 А0243 и т.д. Заголовок окна Полоса меню — Панель инструментов Стандартная Закрыть окно Максимизировать или восстановить окно Панель инструментов Форматирование Свернуть окно —i £ а Ветлам Фоймвг Сэиис Данные flww Спрями a v f х % # <у 10™ • * ~ % м> *4 Л * «' _-/»-А ., ■Текущая ячейка ■Заголовки строк Л _ Кнопки для прокрутки It Z J "Z ". ярлыков рабочих листов ; : Горизонтальная полоса _, —Ярлыки рабочих листов 'прокрутки Вертикальная полоса _ прокрутки N \ли«т1 / Лмст2 / Лки.гЗ / трока состояния ей Рис. 1.1. Окно программы Excel Вертикальная и горизонтальная полосы прокрутки предназначены для просмотра той части рабочего листа, которая в данный момент не видна, т.е. с помощью указанных полос информация на листе может прокручиваться в окне в вертикальном или горизонтальном направлении. ш Настройка Excel Для работы с программой Excel следует провести необходимые настройки. Практически все настройки Excel можно сделать с помощью диалога Параметры, который вызывается командой меню Сервис -> Параметры. > Выберите команду меню Сервис -> Параметры. На экране появится диалог Параметры. > Если какой-либо из описанных выше элементов окна, за исключением панелей инструментов, не присутствует на экране монитора, щелкните мышью на ярлычке с надписью Вид, чтобы выбрать нужную вкладку с элементами управления (рис. 1.2). Параметры Переход ВЕЗ Списки Вид Диаграмма Правка Цвет Общие Отображать - — - 17 строку формул (7 строку состояния Примечания - - ■- f" не отображать ^ только индикатор Объекты ■ • (* отображать f только очертания 17 окна на панели задач <~ примечание и индикатор <"" не отображать Параметры окна — - - Г" формулы 17 сетка 17 нулевые значения 17 заголовки строк и столбцов 17 символы структуры |7 горизонтальная полоса прокрутки 17 полоса прокрутки 17 ярлычки листов Г" авторазбиение на страницы Цвет: J Авто -»-] ОК Отмена Рис. 1.2. Ярлык Ввд > Затем с помощью мыши установите флажки напротив названий тех элементов, которых нет на экране. > Выберите вкладку Правка (рис. 1.3) и убедитесь, что установлены флажки Правка прямо в ячейке, Автозаполнение значений ячеек, Переход к другой ячейке после ввода, в направлении и что в списке выбора установлена строка Вниз. 11
Параметры ЕЮ I ' Переход | ' Списки ] Диаграмма | Цвет - Вид ] Вычисления . ! .[fe3?!?? , ]!.'• Общие Параметры —— . . ._ - . -..„1 • •' Р Пр_авка прямо в ячейке Р" Расширять форматы и формулы в списках Р Перетаскивание ячеек ■ ^ Автоматический ввод процентов '. . Р Предупреждать перед перезаписью ячеек ■ . ' ■ ., _ • р Переход к другой ячейке после ввода, ' •.""',. , в направлении: ' ]вниз _»j ' . ■ Г" in десятичный формат при вводе, , ' . ' . десятичных разрядов: р ^ ' 1 ,Р Перемещать объекты вместе с ячейками • " -; -.,'■, Р Запрашивать об обновлении автоматических связей : -- ' • . • , , ^ Г" Плавная вставка и удаление ячеек , '','.■' Р Автозаполнение значений ячеек I , - ' ОК I Отмена Рис. 1.3. Ярлык Правка > После установки флажков закройте диалог Параметры, нажав кнопку ОК. Видимостью панелей инструментов можно управлять с помощью диалога Панели инструментов, который вызывается, используя команду меню Вид -> Панели инструментов. Если в окне Excel на вашем экране нет панелей инструментов Стандартная или Форматирование, выполните следующие действия: > выберите команду меню Вид -> Панели инструментов. На экране появится диалог Панели инструментов (рис. 1.4); > установите с помощью щелчка мыши флажки напротив названий Стандартная и Форматирование и убедитесь, что все остальные флажки сброшены. Если нет, то сбросьте их. Использование справочной системы Excel, как и другие программы Windows, имеет мощную справочную систему. Если возникают вопросы во время работы, 12 \ мбжно быстро получить на них ответы, не отходя от компьютера. \При этом вызов справки в Excel ничем не отличается от вызове справки в других программах Windows. Ж| Microsoft Excel - Книга! |£) £*л Правка i В.ИД Встдеса Формат Сервис Данные Окно Справка Ю СЯОЙ^вШычный ' ' i^ *~-—-"—■'" Ш Разметка страницы ц Anal Lyi i А1 rj :§S :Щ % и» ttf A t= ts i _. Ф - а » ,u А 10 11 12 13 14 15 IB 17 1В 19 20 21 22 23 Масштаб , Панели инструишгиЕ ► */ Стандартная I»" Visual Basic . V.'eb Wortfet Буфер обмена Внешние йанные Диаграммы Настройка i - - г - ... - ■■ - Рисование Сводные табпицы Формы Элементы управления ^встройка. „МММ И Рис. 1.4. Диалог Панели инструментов > Для вызова справки нажмите клавишу F1. На экране появится окно, где находится справочная информация. О том, как пользоваться справочной системой, подробно написано в книгах, посвященных Windows, поэтому мы остановимся лишь на тех особенностях и способах получения подсказки, которые присущи программе Excel. > Закройте справочное окно, нажав комбинацию клавиш AW+F4. > Подведите указатель мыши к кнопке - на панели инструментов Стандартная. > Под указателем мыши появится надпись Печать (Название принтера). Этот способ получения подсказки действует только в случае, если в диалоге Панели инструментов установлен флажок Вснлы- 13
вающие подсказки. Более подробно справку о кнопках на панелях инструментов, командах меню и других элементах экрана можно получить следующим образом. / > Нажмите команду меню Справка -> Что это такое? /или комбинацию клавиш Shift+Fl. К указателю мыши добавится знак вопроса. | Когда перемещается этот указатель над кнопками панелей инструментов, в строке состояния также появляются подсказки. > Щелкните мышью на кнопке J^* . На экране появится окно, которое содержит подсказку о назначении этой кнопки (рис. 1.5). Команда «Печать» (меню «Файл») Печать текущего файла или выделенных элементов. Параметры печати задаются с помощью команды Печать меню Файл. Рис. 1.5. Подсказка о назначении кнопки Ввод и форматирование данных При запуске Excel автоматически создается новая рабочая книга. При этом темная рамка находится в левом верхнем углу чистого рабочего листа, в ячейке А1, что и отражается в поле имен (рис. 1.6). Рис. 1.6. Текущая ячейка А1 > Наберите на клавиатуре текст «Статистические функции». Этот текст появится в ячейке А1 и строке формул. В ячейке после текста увидите вертикальную черту — текстовый курсор (рис. 1.7). А1 j\ X \f a' Статистические функции • 1 д./ | б ■ . с....-!1 _d_,J:_.jO 1 [Статистические функции! _ ' И ! i ; ! Рис. 1.7. Ввод текста в ячейку А1 14 \ Появление вводимого текста одновременно в ячейке и строке формул означает, что можно вводить информацию либо сразу в ячейку, либо в строку формул. Сейчас вводим строку непосредственно в ячейку, работу со строкой формул рассмотрим позднее. Y Нажмите клавишу Enter, чтобы закончить ввод текста в ячейку. Темная рамка перейдет в ячейку А2. Таким образом, ввести текст в ячейку очень просто. В процессе ввода можно удалять неправильно введенные символы с помощью клавиши Backspace. Здесь рассмотрен только один способ ввода текста, на самом деле это можно сделать по-разному: > нажав клавишу F2; > дважды щелкнув мышью в ячейке А1; > установив указатель мыши в строке формул. При выполнении перечисленных действий в выделенной ячейке сразу появится текстовый курсор. Передвижение по таблицам и способы выделения ячеек Перемещаться по ячейкам таблицы можно с помощью клавиш управления курсором. Одно нажатие на клавишу со стрелкой приводит к перемещению на одну ячейку. Чтобы ускорить перемещение, можно нажать и удерживать клавишу со стрелкой. Включится режим автоповторения, в котором клавиатура сама быстро генерирует коды нажатия клавиши так, как будто вы быстро нажимаете и отпускаете клавишу. Для перемещения по таблице вверх и вниз на размер окна надо использовать клавиши Page Up и Page Down. С помощью вертикальной и горизонтальной полос прокрутки можно прокручивать изображение таблицы в окне, при этом текущая ячейка не изменяется, т.е. темная рамка вокруг текущей ячейки остается на месте при перемещении изображения таблицы. Для оформления таблицы очень часто будет необходимо выделять диапазоны ячеек, например, чтобы вьщелить их цветом или полужирным шрифтом, поэтому очень важно усвоить навыки, с помощью которых можно быстро выделять ячейки. Вьщелить одну ячейку не составляет труда: надо щелкнуть на ней мышью или с помощью клавиш управления курсором переместить на нее темную рамку. Выделение диапазона ячеек осуществляется с помощью клавиш управления курсором. При этом необходимо удерживать нажатой клавишу Shift. 15
> Выделите ячейку А2. / > Нажмите и удерживайте клавишу Shift. / > Удерживая нажатой клавишу Shift, три раза нажмите клавишу I. Будет выделен диапазон А2:А5. / > Отпустите клавишу Shift. / После того как ячейки будут выделены, их содержимое можно скопировать в другое место таблицы, например, для ого, чтобы не вводить одни и те же формулы. Копирование и перенос Для копирования информации в среде Windows существует так называемый буфер обмена. Это участок памяти, в который можно скопировать любую информацию из различных программ: из Excel можно скопировать содержимое ячеек. Затем содержимое ячеек из буфера обмена вставить в любое другое место таблицы. > Выделите ячейку А4. > Введите в эту ячейку формулу «=СУММ (В2.ВЗ)». > Выберите команду Правка -> Копировать. Вокруг ячейки А4 появится бегущая пунктирная рамка, а значит, содержимое ячейки А4 уже скопировано в буфер обмена Windows. > Выделите ячейку В4 и выберите команду меню Правка -> Вставить. В ячейку В4 из буфера обмена будет вставлена формула «=СУММ (В2:ВЗ)». Здесь надо обратить внимание на то, что в ячейке А4 находится формула «=СУММ (А2:АЗ)», а в ячейку В4 вставлена формула «=СУММ (В2:ВЗ)». Дело в том, что Excel автоматически отслеживает адреса ячеек при переносе формулы из одной ячейки в другую. Формула переносится на один столбец вправо, и адреса всех ячеек формулы также увеличиваются на один столбец. Это очень удобно в повседневной работе, например, если сумму или другую операцию или функцию надо вьиислять для нескольких столбцов, расположенных подряд. Но иногда необходимо избежать автоматического изменения адресов ячеек. Для этого следует использовать абсолютные адреса ячеек в формуле. Абсолютный адрес ячейки А2 обозначается так: $А$2. > Введите в ячейку А4 формулу «=$А$2+А3». > Выделите ячейку А4 и нажмите комбинацию клавиш Ctrl+Insert. Содержимое ячейки будет скопировано в буфер обмена. > Выделите ячейку В4 и нажмите комбинацию клавиш Ctrl+Insert. В ячейку будет вставлена формула «=$А$2+В3». 16 Абсолютный адрес ячейки А2 в формуле не изменился. Необходимо обратить внимание еще на одну особенность абсолютной адресации. Если указать в формуле адрес ячейки А$2, то при переносе формулы в этом адресе будут изменяться только столбцы; соответственно если $А2, то только строки. Дли изменения адреса ячейки на абсолютный во время редактирования формулы используется клавиша F4. > Выделите ячейку В4. > Нажмите клавишу F2, чтобы начать редактирование формулы. > С помощью клавиши <- установите текстовый курсор сразу за адресом ВЗ или между символами «В» и «3». > Нажмите клавишу F4. Адрес ВЗ изменится на $В$3. > Нажмите клавишу Esc, чтобы отменить редактирование. Отметим, что, один раз скопировав информацию в буфер обмена Windows, можно вставлять ее из буфера в разные места таблицы неограниченное число раз. Существует два разных способа помещения информации в буфер обмена. Первый — это копирование, уже рассмотренный выше. Второй — вырезание, т.е. когда содержимое ячейки удаляется из таблицы и помещается в буфер обмена. Рассмотрим этот способ на практике, напомним, что в таблице выделена ячейка В4. "и < > Нажмите кнопку на панели инструментов Стандартная. Вокруг ячейки В4 появится бегущая штриховая рамка. > Выделите ячейку С4 и нажмите кнопку & на панели инструментов Стандартная. Содержимое ячейки В4 перенесено в ячейку С4. Обратите внимание: для работы с буфером обмена использовали кнопки на панели инструментов. Это самый быстрый и удобный способ. Для копирования информации в буфер обмена можно воспользоваться кнопкой . Если необходимо просто быстро удалить содержимое ячеек, то нажмите клавишу Delete. Выбор шрифта и выравнивание текста Все действия по оформлению таблиц можно выполнить с помощью кнопок и списков на панели инструментов Форматирование (рис. 1.8). 1. Щелкните мышью на ячейке А1 и откройте список Шрифт на панели инструментов Форматирование. Напомним, чтобы от- 17
3 4 5 9 Ю 11 15 16 17 /18 Г. .-~П1Г "ПГ^Л.71Л1. urnes New Roman Cyt 10 '' Ж> К ^^Ш,Ш'Ш W'gX j й /« #'ts «» «"* * •*"* _ — —j j [*-jjtT" 6 7 8 12 13 14 Рис. 1.8. Панель инструментов Форматирование крыть список, надо щелкнуть на кнопке I-J. Прокручивая список с помощью полосы прокрутки, найдите необходимый шрифт, например Times New Roman Суг, и щелкните на нем мышью. Текст в ячейке А1 (см. рис. 1.7) будет отображен выбранным шрифтом. 2. Откройте список для выбора размера шрифта и выберите размер 16. Размер символов в ячейке А1 будет увеличен. 3. Нажмите кнопку d£J на панели инструментов Форматирование. Текст в ячейке А1 будет выделен полужирным стилем, кнопка да ' ■ зафиксируется в нажатом состоянии. К' 4. С помощью кнопки -»* можно выделить текст курсивом. — 5. С помощью кнопки L*. можно подчеркнуть текст. а 6. Выделите диапазон ячеек А2:АЗ и нажмите кнопку «S на панели инструментов Форматирование. Текст в выделенном диапазоне будет выровнен по левой границе ячеек. 7. С помощью кнопки L~ можно выровнять текст по центру ячеек. 8. С помощью кнопки ^ можно выровнять текст по правой границе ячеек. 9. Выделите диапазон ячеек А1:Е1 и нажмите кнопку " на панели инструментов Форматирование. Текст в ячейке А1 будет выровнен по центру выделенного диапазона ячеек. 10—14. Кнопки I®. 2^-Ш—'60 *'° I предназначены для форматирования ячеек. 15. Кнопки вставки и удаления строк. 16. Кнопка для оформления границ таблиц. 18 \ 17. Кнопка выделения цветом. 18. Кнопка для установления цвета шрифта. Выберите из контекстного меню команду Формат ячеек (данную команду можно вызвать и через пункт Формат панели Стандартная). На экране появится окно Формат ячеек (рис. 1.9). В данном окне имеются следующие ярлыки: Число, Выравнивание, Шрифт, Граница, Вид, Защита. Формат ячеек Число j Выравнивание j Шрифт J' Граница | Вид ВЕЗ Защита | Числовые форматы: Денежный > -'. Финансовый Дата ч >■ Время , ,'< Процентный .;;. } Дробный Экспоненциальный' , Текстовый * '-А Дополнительный -", (все форматы) !*1 г Образец- '-■ - -1 -- - Число десятичных знаков: Г* Разделитель групп разрядов < ) Отрицательные числа: .■ • -1234 10 1234,10 -1234,10 -1234,10 3- ■ Числовой формат является наиболее общим способом представления . чисел. Для вывода денежных.значений Используются также форматы' .: "Денежный" и "Финансовый".. ОК Отмена Рис. 1.9.' Ярлык Число окна Формат ячеек В ярлыке Число определяется числовой формат и число десятичных знаков после запятой. В ярлыке Выравнивание предлагается выровнять текст по горизонтали и вертикали, сориентировать текст, а также можно управлять видом ячеек. Ярлык Шрифт предлагает инструменты по формату шрифта текста. С помощью ярлыка Граница оформляется обрамление ячеек. Ярлык Вид предлагает выбрать цвет заливки ячеек. С помощью ярлыка Защита можно установить на выделенные ячейки защиту или скрыть формулы. 19
Печать рабочих листов Распечатать таблицу из программы Excel можно несколькими способами: | > нажав кнопку Печать в режиме предварительного просмотра; | > нажав кнопку Печать в диалоге Параметры страницы; | > выбрав команду меню Файл -> Печать в обычном режиме редактирования; I > с помощью комбинации клавиш Ctrl+P в обычном режиме; > с помощью кнопки! ~ на панели инструментов Стандартная. Работа с диаграммами Мастер диаграмм — одно из наиболее мощных средств в программе Excel. На панели Стандартная имеется кнопка для вызова Мастера диаграмм . Построить диаграмму можно, выделив фрагмент таблицы с исходными данными и нажав указанную кнопку. Для построения диаграммы Мастер диаграмм сделает четыре шага (рис. 1.10). Мастер диаграмм (шаг 1 из 4) тип диаграммы Стандартные | D*i. Гистограмма Щ? Линейчатая j£X График Ф Круговая |Li. Точечная {^ С областями Q Кольцевая ^ Лепестковая '(Jp Поверхность •{ Пузырьковая |£й.Бир>кввая zi ВЕЗ 1ГП Ш ,-А fs-s РЧ 13 ■■'■ ^ :-" ' >;. 1-~ 1 \Т Обычная гистограмма отображает имения различных категорий Просмотр результата | Далее > } Сртово Рис. 1.10. Окно Мастера диаграмм (тип диаграммы) Первый шаг — это выбор типа диаграммы. Мастер предлагает Стандартные и Нестандартные диаграммы. Каждый тип диа- 20 граммы снабжен комментариями, а также предварительным просмотром. На втором шаге необходимо указать источник данных диаграммы. На этом окне имеются два ярлыка: Диапазон данных и Ряд. На ярлыке Диапазон данных необходимо указать, как представлены данные в исходной таблице — в строках или в столбцах, а также указать диапазон ячеек, в которых представлены данные (поле Диапазон автоматически заполняется ссылками на ячейки исходной таблицы). На ярлыке Ряд можно добавлять или удалять ряды данных. Для ввода значений в другие поля данного ярлыка необходимо использовать кнопку _Ы. Нажав на эту кнопку, сворачиваем окно и переходим к листу Excel с исходными данными. Выделив с помощью мыши необходимую информацию из таблицы и нажав снова на эту кнопку, разворачиваем окно Мастера диаграмм. На третьем шаге производится изменение параметров диаграммы. В полях Название диаграммы, Ось X (категорий), Ось Y (значений) можно набрать с клавиатуры заголовки как самой диаграммы, так и ее осей. В поле Оси можно выбрать, будет ли показана оцифровка осей диаграммы или нет. На ярлыке Линии сетки предложены варианты показа сетки диаграммы, а именно: показывать для осей X и Y основные или промежуточные линии сетки или нет. В поле Легенда можно выбрать: показывать или нет легенду и ее размещение. В поле Подписи данных необходимо указать: отображать или нет подписи значений на диаграмме. Если поставить флажок в поле Таблица данных, то на листе с диаграммой будет показана еще и таблица с исходными данными. На четвертом шаге указывается размещение диаграммы: на отдельном листе или на одном из имеющихся. После выбора размещения нажмите кнопку Готово. Параметры уже построенной диаграммы можно изменять, используя контекстное меню, вызываемое по нажатию правой кнопки мыши. Если внести изменения в ту часть таблицы, по которой строилась диаграмма, то Excel автоматически модифицирует диаграмму. Ввод и редактирование формул Вводить формулы в ячейки таблицы можно точно так же, как и текст, единственное отличие: все формулы начинаются со знака равенства «=». Покажем, как вычислить сумму двух ячеек. 21
> Введите цифру 4 в ячейку А2 и затем цифру 5 в ячейку A3. > Введите в ячейку А4 формулу «=А2+А3». После того, как завершится ввод формулы, в ячейке А4 появится результат сложения 9. Если необходимо сложить содержимое большого количества ячеек, то операцию суммирования можно легко автоматизировать вместо того, чтобы вводить длинную формулу. > Удерживая левую кнопку мыши, перемещайте мышь и выделите диапазон ячеек от А2 до A3. Диапазоны ячеек в Excel обозначаются так: «А2:АЗ». > Щелкните мышью на кнопке которая находится на панели инструментов Стандартная. В ячейку А4 будет автоматически вставлена формула «=СУММ (А2:АЗ)», что означает вычисление суммы значений, начиная с ячейки А2 и заканчивая A3. Таким образом, можно быстро вычислять сумму ячеек по столбцам или по строкам таблицы. Кроме простых арифметических действий можно использовать сложные встроенные функции Excel, доступ к которым осуществляется с помощью так называемого Мастера функций. 1.2. Мастер функций Вставка формул с помощью Мастера функций В Excel существует специальная программа — мастер, упрощающая процесс создания формул. Ее задача — исключить некоторые типичные ошибки, давать по ходу дела подсказки и комментарии, вычислять промежуточные результаты. Формула в Excel может состоять как из одной, так и из нескольких функций, а также включать в себя различные арифметические операторы. Для примера вставим в ячейку В2 формулу, состоящую из одной функции СЧЕТ для подсчета количества чисел. > Сформируйте таблицу исходных данных, для чего в диапазон Al: A6 введите различные числовые данные: 11 2 3 4 | б! в А I ll 4 3i -9 67, -«, 22 > Выделите ячейку В2. > Нажмите клавишу «=». Включится режим редактирования. > Нажмите кнопку ill. На экране появится развернутый список, содержащий перечень последних используемых функций (рис. 1.11). ; , КОРЕНЬ ' - КОРЕНЬ СРЗНАЧ ЕСЛИ ГИПЕРССЫЛКА СЧЕТ МАКС SIN СУММЕСЛИ х ^••*г= , Значение; .'■ , ' ' ОК . — , _ — _ , _ -— „- - -. — — ~ t i - ■ ! _ — _ ., — * i 1 - - — - 1 ~ Отмена — .. ™ „. - — '1 - " "™ Рис. 1.11. Список последних используемых функций > Нажмите кнопку мыши на поле Другие функции..., вызвав окно Мастера функций (рис. 1.12). Мастер Функций - шаг 1 из 2 10 недавно использовавшихся Полный алфавитный перечень Финансовые Дата и время Математические НЯВИИЯЕ функция: Ссылки и массивы Работа с базой данных Текстовые Логические Проверка свойств и значений СЧЁТ(значение 1 ;значение2;...) СТДНДОТКЛОНПД CTOUJYX СТЬЮДРДСП СТЬЮДРДСПОБР СЧЕТЕСЛИ JC4ET3 СЧИТДТЬПУСТОТЫ ТЕНДЕНЦИЯ ТТЕСТ УРЕЗСРЕДНЕЕ 1 Подсчитывает количество чисел в списке аргументов. ОК Отмена Рис. 1.12. Окно Мастера функций > Щелкните мышью на строке Статистические в списке Категория. > Прокручивая список Функция, найдите строку СЧЕТ и щелкните на ней мышью. 23
> Нажмите кнопку ОК. На экране появится следующее окно Мастера функций для ввода аргументов выбранной функции (рис. 1.13). На данном окне есть подсказки. Кроме смысла функции поясняется и смысл ее аргументов. Excel сразу же вычисляет промежуточные результаты по каждому из аргументов, ниже — по всем аргументам, а в самом низу окна пишет результат вычисления функции (Значение). io-et—-- -- - --■..■"mm~";'_::z ^"ТВтГ.. „",; ту ■ •~r~ ■"" '■")• Значение1 |А1:Дб| ^j = <J;4;3:-9:6?;42} • ,,/, . Значений) 3= • '' Подсчитывает количество чисел в списке аргументов. - ■ '' -.',', '. * ... Значение!: значение1;значение2;,!, от 1 до 30 аргументов, которые могут . ' ■ " ■■' •' . содерчотьиписсылвтьсймаданныеразличныхтипов^нов ■ • ' . './,.•' , ■, ,. подсчете участвуют только числе, • ' ' * ' ' ■ * ' ■ щ Значение: 6 (Ж Рис. 1.13. Окно Мастера функций (функция СЧЕТ) > Щелкните мышью на кнопке Ш> в поле Значение1. Окно Мастера функций свернется и можно будет мышью выделить диапазон ячеек, содержащих исходные данные. После этого, снова щелкнув мышью на кнопке За, возвратитесь в окно Мастера функций. В поле Значение1 появятся адреса этих ячеек. В нижней части окна появится результат вычисления функции — Значение:6 (рис. 1.13). Следует отметить, что, нажав кнопку L3, с помощью которой вы вызвали окно Мастера функций первый раз, вы можете запустить новую копию Мастера функций, чтобы ввести любую вложенную функцию. > Нажмите кнопку ОК (или кнопку Е на панели инструментов), чтобы закончить ввод формулы. Excel содержит сотни функций, поэтому очень удобно пользоваться Мастером функций, который всегда подскажет количество и назначение аргументов выбранной функции. 1.3. Работа с массивами Формулы массива и их ввод Формулы (могут содержать многие функции и различные арифметические операторы), которые возвращают массивы (рас- 24 считывают несколько значений), должны быть введены как формулы массивов после выделения подходящего числа ячеек. Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива обрабатывает несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используется комбинация клавиш CTRL+ +SHIFT+ENTER. Создание формулы массива При вводе формулы массива Microsoft Excel автоматически заключает ее в фигурные скобки ({}). Порядок ввода формулы массива следующий: 1. Выделите ячейку (если формула массива рассчитывает одно значение) или диапазон ячеек (если формула массива рассчитывает несколько значений). 2. Наберите формулу. 3. Нажмите комбинацию клавиш CTRL+SHIFT+ENTER. Иногда для получения одного результата в Microsoft Excel необходимо выполнить несколько вычислений. Например, есть данные о компании, имеющей региональные представительства в Европе и США, торгующие тремя типами товаров. Необходимо рассчитать средний доход по каждому типу товаров в Европе за 1992 г., используя формулу массива. Задачу можно решить, используя функции ЕСЛИ и СРЗНАЧ (все используемые здесь функции подробно рассмотрены в следующих разделах книги). Функция ЕСЛИ используется при проверке условий для значений и формул. Она рассчитывает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция СРЗНАЧ рассчитывает среднее арифметическое своих аргументов. Решение задачи показано на рисунке 1.14. Ячейка С16 содержит формулу массива {=СРЗНАЧ (ЕСЛИ (С5:С14=«Европа»; D5:D14))}, которая отбирает из диапазона С5:С14 ячейки, содержащие текст «Европа», а затем вычисляет среднее значение по соответствующим ячейкам диапазона D5.D14 - 106566,7. 25
16 jj" ="{=СРЗНА^ЕСЛИ(С5ГС1^'1|^опа^Р5тЩ В _Р Ьт Е 1992 Аппаратура Аппаратура: итог Акустические Европа США 233700 Европа США Акустические итог Студийное оборудование Европа США Студийное оборудовать: итог =СРЗНАЧ(ЕСЛИ(05-С14='Европа';Р5-Р14)) 100600 133100 129200 150500 279700 89900 112300 202200 1997 161000 198200 359200 160700 190100 350800 153900 190700 344600 Рис. 1.14. Решение задачи по определению среднего дохода Для вычисления нескольких значений с помощью формулы массива необходимо ввести массив в диапазон ячеек, состоящий из того же числа строк или столбцов, что и аргументы массива. . Рассмотрим пример, в котором по заданному ряду из трех значений продаж (в строке 5) и ряду из трех месяцев (в строке 3) (рис. 1.15) необходимо определить продолжение линейного ряда объемов продаж. Для решения данной задачи можно использовать функцию ТЕНДЕНЦИЯ. Данная функция рассчитывает значения в соответствии с линейным трендом, используя аппроксимацию по методу наименьших квадратов. Для отображения всех вычисляемых значений формула введена в три ячейки строки 6 (С6:Е6). с . 1^Х'.-о^-2г ^ ....•' •--< ■■ е - --аь^ ! 1 20234 =ТЕНДЕНЦИЯ(С5 Е5..СЗ ЕЗ) 2 21003 =ТЕНДЕНЦИЯ(С5 Е5..СЗ ЕЗ) 3 10000 =ТЕНДЕНЦИЯ(С5 Е5..СЗ ЕЗ) Рис. 1.15. Решение задачи по определению продолжения линейного ряда объемов продаж 26 Формула =ТЕНДЕНЦИЯ (С5:Е5;;СЗ:ЕЗ), введенная как формула массива, определяет три значения, вычисленные по трем объемам продаж за три месяца: 22196, 17079, 11962. Формулу массива можно также использовать для вычисления одного или нескольких результатов для ряда значений, еще не введенных на лист. Формулы массива принимают константы так же, как и другие формулы, но константы массива необходимо вводить в определенном формате. Например, имея те же три значения продаж, что и в предыдущем примере, можно спрогнозировать объемы продаж на следующие два месяца (рис. 1.16). D |20234 121003 110000 |=ТЕНДЁНЦИЯ(С5 Е5;,{4;5>)[=ГЕНДЕНЦИЯ(С5-Е5,.{4,5}) Рис. 1.16. Решение задачи по прогнозированию объемов продаж в условиях предыдущего примера Для прогнозирования четвертого и пятого значений в ежемесячной-последовательности на основе первых трех значений используется формула =ТЕНДЕНЦИЯ (С5: Е5;;{4;5}), которая дает следующие результаты: 6845, 1728. Константы в формулах массива Обычно формула при обработке одного или нескольких аргументов возвращает (рассчитывает) одно значение; в качестве аргумента формулы может при этом выступать либо ссылка на ячейку, содержащую значение, либо само значение. В формуле массива, которая обычно используется для ссылки на диапазон ячеек, можно ввести массив значений в одну ячейку. Этот массив значений называется массивом констант; удобен он тем, что при этом не требуется заносить по одному значению в ячейку на листе. Чтобы создать массив констант, надо выполнить следующие действия: > ввести значения непосредственно в формулу, заключив в фигурные скобки ({}); > столбцы разделить запятыми (,); > строки разделить точками с запятой (;). 27
Например, вместо ввода четырех чисел (10, 20, 30, 40) в отдельные ячейки их можно ввести в массив, в одну ячейку в фигурных скобках: {10, 20, 30, 40}. Такой массив констант является матрицей размерности 1 на 4 и соответствует ссылке на 1 строку и 4 столбца. Чтобы представить значения 10, 20, 30, 40 и 50, 60, 70, 80, находящиеся в расположенных друг под другом ячейках, можно создать массив констант размерностью 2 на 4, причем строки будут отделены друг от друга точкой с запятой, а значения в столбцах — запятыми: {10, 20, 30, 40; 50, 60, 70, 80}. Массив констант может состоять из чисел (числа в массиве могут быть целыми, с десятичной точкой или в экспоненциальном формате), текста (текст должен быть взят в двойные кавычки, например «Вторник»), логических значений (например, ИСТИНА или ЛОЖЬ) или значений ошибок (например, #Н/Д). Массив констант может состоять из элементов разного типа, например {1,3,4; ИСТИНА, ЛОЖЬ, ИСТИНА}. Элементы массива должны быть константами, но не формулами. Массив констант не может содержать $ (знак доллара), скобки или % (знак процента), ссылки на ячейки, столбцы или строки разной длины. 1.4. Некоторые замечания об использовании статистических функций В состав Microsoft Excel входит большое количество функций как специального, так и общего назначения. Доступ к функциям может быть осуществлен, если непосредственно набрать имя и параметры функции в строке формул или через рассмотренный выше Мастер функций. Для вызова статистических функций необходимо в окне Мастера функций из списка Категория выбрать Статистические. В списке Функция появится весь список статистических функций (всего 80 функций). В данной книге для удобства пользования информация о всех статистических функциях разделена на восемь разделов (со второго по девятый), каждый из которых содержит функции одной тематики. Во втором разделе рассмотрены функции, позволяющие осуществлять предварительную обработку статистических данных: > подсчет количества значений; > определение экстремальных значений совокупности данных; 28 > подсчет частот из массива данных, попадающих в заданные интервалы; > оценку относительного положения точки; > определение величины, соответствующей ее относительному положению; > определение числа перестановок; > определение ранга числа в списке чисел. Третий раздел посвящен определению характеристик положения. С этой целью описаны функции: > вычисления среднего; > определения моды в интервале данных или массиве; > определения медианы; > определения квартилей. В четвертом разделе рассматривается определение характеристик рассеивания с помощью следующих функций: > определения среднего отклонения; > определения суммы квадратов отклонений; > вычисления дисперсии, стандартного (среднего квадрати- ческого) отклонения; > определения асимметрии распределения, эксцесса распределения. Содержанием пятого раздела (зависимость случайных величин) являются функции для определения ковариации; коэффициента корреляции; коэффициента корреляции Пирсона. Шестой раздел посвящен интервальному оцениванию: функции определения доверительного интервала для среднего и определения вероятности попадания дискретной случайной величины в интервал. В седьмом разделе рассматриваются функции для определения параметров распределений непрерывных случайных величин: > определение значения функции распределения и функции плотности нормального распределения; > определение аргумента по значению функции распределения; > определение нормализованного значения аргумента; > определение значения функции распределения стандартного нормального распределения; > определение аргумента по значению стандартной интегральной функции нормального распределения; > определение вероятности статистики Znpn проверке гипотезы о равенстве статистической оценки математического ожидания заданному значению; 29
> определение значения функции распределения логарифмически нормального распределения; > определение аргумента по значению функции распределения; > определение значения функции распределения Стьюдента (интегральной функции); > определение параметра t по значению функции распределения; > проверка гипотезы о равенстве средних (определение вероятности, соответствующей критерию Стьюдента); > определение значения функции распределения у}; > определение параметра у по значению функции распределения х2; > проверка гипотезы о виде закона распределения (определение вероятности значения х2); > определение значения функции распределения /"-распределения (распределения Фишера — Снедекора); > определение параметра х по значению функции распределения jF-распределения; > проверка гипотезы о равенстве дисперсий (определение вероятности статистики Т); > определение значения преобразования Фишера; > определение обратного преобразования Фишера; > определение значения функции распределения и функции плотности экспоненциального распределения; > определение значения функции распределения и функции плотности гамма-распределения; > определение аргумента по значению функции распределения; > определение натурального логарифма гамма-функции; > определение функции распределения бета-распределения; > определение аргумента по значению функции бета-распределения; > определение значения функции распределения и функции плотности распределения Вейбулла. В восьмом разделе рассматриваются функции для определения параметров распределений дискретных случайных величин: > определение вероятности возможного значения и функции распределения случайной величины, имеющей биномиальное распределение; > определение наименьшего значения биномиальной случайной величины; 30 > определение вероятности числа неудач в последовательности испытаний Бернулли; > определение вероятности возможного значения и функции распределения случайной величины, имеющей распределение Пуассона; > определение вероятности заданного количества успехов в выборке. Последний раздел — девятый, он посвящен описанию функций для построения уравнения регрессии и прогнозирования: > определение параметров линейной регрессии; > определение значений результативного признака по линейному уравнению регрессии; > определение параметров показательной функции; > определение значений результативного признака по показательному уравнению регрессии; > определение значения уравнения регрессии вида у = bo + + b\x в заданной точке; > определение точки пересечения линии регрессии с осью У; > определение тангенса угла наклона линии регрессии к оси X; > определение стандартной ошибки отклонения результативного признака от уравнения регрессии. Все статистические функции можно использовать самостоятельно для решения экономических и статистических задач и в составе различных математических зависимостей (уравнений, неравенств и т.д.). Зная материал, представленный в книге, и основы языка Visual Basic, можно самому составлять сложные статистические программы. В следующих главах книги подробно рассмотрены все перечисленные статистические функции, приведены примеры использования этих функций в экономико-статистических расчетах.
2. Предварительная обработка статистических данных 2.1. Подсчет количества значений. Функции СЧЕТ, СЧЕТЗ, СЧЕТЕСЛИ, СЧИТАТЬПУСТОТЫ Подсчет количества чисел. Функция СЧЕТ Синтаксис функции: СЧЕТ (Значение 1, Значение2, ...) При использовании функции СЧЕТ производится подсчет количества чисел в списке аргументов. Список аргументов при этом может содержать различные данные, но в подсчете участвуют только числа. Если аргумент является массивом или ссылкой, то подсчиты- ваются только числа в этом массиве или ссылке. Учитываются аргументы, которые являются числами, пустыми значениями, логическими значениями, датами, или текстами, изображающими числа; аргументы, представляющие собой значения ошибок или тексты, если их нельзя интерпретировать как числа, игнорируются. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются. Окно данной функции имеет следующий вид (числовые данные из примера 2.1, а): очЕт - — J» ■, Значение1 |В2 Q2| 53»-РКД25;"-";1,Ь8, Значешег]3* . U ," Подсчитывает количество чисел в списке аргументов. , ; Значение!: значенив1;значение2, от1 асЗОавгументоб, которыеногут содержатьиписсылатьечна данные различны* типов, нов гшсчете участвуют только чисча 1§У ' ' ' Значение: Ц ' 1 ОК | Отмена | где Значение1, Значение2, ... — от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа. Подсчет количества непустых значений. Функция СЧЕТЗ Синтаксис функции: СЧЕТЗ (Значение1, Значение2, ...) При использовании функции СЧЕТЗ производится подсчет непустых значений в списке аргументов, т.е. подсчитывается количество ячеек с данными в интервале или массиве. 32 Окно этой функции аналогично окну функции СЧЕТ, где Значение!, Значение2,... — от 1 до 30 аргументов, количество которых требуется сосчитать. В данном случае значением считается значение любого типа, включая пустую строку (""), но не включая пустые ячейки. Если аргументом является массив или ссылка, то пустые ячейки в массиве или ссылке игнорируются. Подсчет количества непустых значений в соответствии с заданным критерием. Функция СЧЕТЕСЛИ Синтаксис функции: СЧЕТЕСЛИ (Диапазон, Условие) Функция СЧЕТЕСЛИ подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. Окно данной функции имеет следующий вид (числовые данные из примера 2.1, в): 1 ' к<5?. . .. W3,S2;4,25,-"-°;l,S8; [. УеяовиеГя" За-**'" • [ ш .-™ .. „ . .„ _. ^ ч -..., _ _ 1 ' '-' ' ■»$ ■ непустых ячеек в диапазоне, удовлетворяющих заданному условию. Условие условие в форме числа, или , какие меики надо подсчитыватк. Значение* ' * <Ж I Отмена где Диапазон — диапазон, в котором нужно подсчитать ячейки; Условие — критерий в форме числа, выражения или текста, определяющий, какие ячейки надо подсчитывать. Шпример, критерий может бьпъ выражен следующим образом (согласно примеру 2.1): 4 (подсчитает, сколько значений, : . 4, имеется в диапазоне), «4» (значение может быть задано в виде текста), «>4» (подсчитает, сколько значений, больших 4, имеется в диапазоне), «компьютеры» (значение может бьпъ задано в виде текста, если диапазон содержит ячейки с текстом). Подсчет количества пустых ячеек в заданном интервале. Функция СЧИТАТЬПУСТОТЫ Синтаксис функции: СЧИТАТЬПУСТОТЫ (Диапазон) Под . : количество пустых ячеек в заданном интервале. Ячейки с формулами, которые возвращают значение «» (пустой текст), учитываются при подсчете. Ячейки с нулевыми значениями не учитываются. уешоы Подсчитывает РВздкЯ 2 Статистические функции в экономике» 33 статиетичес!гиг пягчетм
Окно данной функции имеет следующий вид (числовые данные из примера 2.1, г): СЧЯАТЬ ПУСТОТЫ '^=: . , ■ Диапазон |B2:Q2| ж вши Подсчитывает количество пустых ячеек в _2г^ , • -г ' ■'.• диапазон, в котором требуется определить количество пустых ячеек. т Значемие-.г <Ж Отмена где Диапазон — интервал, в котором требуется подсчитать количество пустых ячеек. Работу вышеперечисленных функций рассмотрим на следующем примере. Пример 2.1. При проведении анкетирования 16 респондентов на вопрос анкеты о величине средней заработной платы за первый квартал текущего года были даны ответы, отраженные в табл. 2.1. Найдите: а) число респондентов, давших ответ на вопрос анкеты; б) число респондентов, давших ответ на вопрос анкеты, включая прочерк в анкете; в) число респондентов, имеющих среднюю заработную плату более 4 тыс. руб.; г) число респондентов, не давших ответ на вопрос анкеты (которые не поставили прочерка в графе вопроса). Таблица 2.1 Результаты анкетирования N° респондента Средняя заработная плата, тыс. руб. 1 3,52 2 4,25 3 4 1,58 5 2,73 6 5,11 7 8 4,10 9 3,72 10 11 3,15 12 4,00 13 2,95 14 4,17 15 4,55 16 Решение. 1. Сформируем таблицу исходных данных: А 1 |ЛЬ |Ср з/ц в ,h ^ ^Ч1ММИ>11ШШМЮМШМ{1ШЩЛЯМ11М1ЯВИЯ 1 3,52 2 4.25 3 ■ 4 1.58 5 2,73 6 5.11 7 8 4.1 9 3,72 10 ■ 11 3,15 12 4 13 2,95 14 4.17 15 4,55 16 34 2. Выберем ячейку, в которую будет выведен результат вычисления ($А$3). 3. Определим число респондентов, давших ответ на вопрос анкеты: > вызовем Мастер функций, нажав кнопку L& на панели инструментов; > в Мастере функций из категории Статистические выберем функцию СЧЕТ -> ОК; > в левом верхнем углу листа Excel появится окно функции СЧЕТ; > нажав кнопку «И в поле Значение1, перейдем на рабочий лист с исходными данными и выделим их мышью (B2:Q2). Затем, повторно нажав эту же кнопку, вернемся к окну функции СЧЕТ; > в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 12 респондентов. 4. Определим число респондентов, давших ответ на вопрос анкеты, включая прочерк в анкете: > вызовем Мастер функций, нажав кнопку J* ' на панели инструментов; > в Мастере функций из категории Статистические выберем функцию СЧЕТЗ -> ОК; > в левом верхнем углу листа Excel появится окно функции СЧЕТЗ; > нажав кнопку За в поле Значение1, перейдем на рабочий лист с исходными данными и выделим их мышью (B2:Q2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СЧЕТЗ; > в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 14 респондентов. 5. Определим число респондентов, имеющих среднюю заработную плату более 4 тыс. руб.: > вызовем Мастер функций, нажав кнопку LH. на панели инструментов; > в Мастере функций из категории Статистические выберем функцию СЧЕТЕСЛИ -> ОК; > в левом верхнем углу листа Excel появится окно функции СЧЕТЕСЛИ; 2* 35
> нажав кнопку -Н в поле Диапазон, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:Q2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СЧЕТЕСЛИ; > в поле Условие наберем — «>4»; > в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 5 респондентов. 6. Определим число респондентов, не давших ответ на вопрос анкеты (которые не поставили прочерка в графе вопроса): > вызовем Мастер функций, нажав кнопку '" на панели инструментов; > в Мастере функций из категории Статистические выберем функцию СЧИТАТЬПУСТОТЫ -> ОК; > в левом верхнем углу листа Excel появится окно функции СЧИТАТЬПУСТОТЫ: > нажав кнопку -У в поле Диапазон, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:Q2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СЧИТАТЬПУСТОТЫ; > в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 2 респондента. 2.2. Определение экстремальных значений совокупности данных. Функции МАКС, МИН, МАКСА, МИНА, НАИБОЛЬШИЙ, НАИМЕНЬШИЙ Определение наибольшего значения. Функция МАКС Синтаксис функции: МАКС (Число!, Число2, ...) При использовании функции МАКС производится определение максимального значения. При этом аргумент функции должен являться массивом или ссылкой. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если аргументы не содержат чисел, то функция МАКС определяется в качестве максимального числа ноль. Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА. 36 Окно данной функции имеет следующий вид (числовые данные из примера 2.2, а): Число» B2:Q2| 4*mtfij if- -_ игнорируются. значение ю списка аргументов. Логические значения или текст Число!: число! ;чиаю2'„. от 1 да 30 чисел, логических значений или строк, среди которых ищется максимальное значение, П Значенив.5,11 |Л™ ОК | где Число1, Число2,... — от 1 до 30 чисел, среди которых ищется максимальное значение. Определение минимального значения. Функция МИН Синтаксис функции: МИН (Число!, Число2, ...) При использовании функции МИН производится определение наименьшего значения в списке аргументов. При этом список аргументов может содержать от одного до тридцати чисел. Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения или тексты в массиве или ссылке игнорируются. Если логические значения или тексты игнорироваться не должны, то следует пользоваться функцией МИНА. Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, представляющие собой значения ошибок или тексты, не преобразуемые в числа, вызывают значения ошибок. Окно данной функции аналогично окну функции МАКС, где Число1, Число2, ... — это от 1 до 30 чисел, среди которых ищется минимальное значение. Определение наибольшего значения в списке аргументов. Функция МАКСА Синтаксис функции: МАКСА (Значение!, Значение2, ...) В отличие от функции МАКС выполняет также сравнения текстовых и логических значений, таких, как ИСТИНА и ЛОЖЬ. Список аргументов должен содержать от одного до тридцати значений. 37
Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями числа. Если аргумент — массив или ссылка, учитываются только значения массива или ссылки. Пустые ячейки и тексты в массиве или ссылке игнорируются. Аргументы, содержащие значение ИСТИНА, интерпретируются как 1 (единица), аргументы, содержащие текст или значение ЛОЖЬ, интерпретируются как 0 (ноль). Аргументы, являющиеся значениями ошибок, приводят к ошибке. Если логические значения и тексты не должны игнорироваться, следует пользоваться функцией МАКС. Если аргументы не содержат значений, то функция МАКСА показывает результат 0 (ноль). Окно данной функции аналогично окну функции СЧЕТ, где Значение1, Значение2, ... — это от 1 до 30 значений, среди которых ищется наибольшее. Определение наименьшего значения в списке аргументов. Функция МИНА Синтаксис функции: МИНА (Значение!, Значение2, ...) Определяет наименьшее значение в списке аргументов, при этом наряду с числовыми значениями выполняется сравнение текстовых и логических значений, таких, как ИСТИНА и ЛОЖЬ. Список аргументов должен содержать до тридцати значений. Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Если аргумент — массив или ссылка, учитываются только значения массива или ссылки. Пустые ячейки и тексты в массиве или ссылке игнорируются. Аргументы, содержащие значение ИСТИНА, интерпретируются как 1 (единица), аргументы, содержащие текст или значение ЛОЖЬ, интерпретируются как 0 (ноль). Аргументы, являющиеся значениями ошибок, приводят к ошибке. Если логические значения и тексты должны игнорироваться, следует пользоваться функцией МИН. Если аргументы не содержат значений, то функция МИНА показывает результат 0 (ноль). Окно данной функции аналогично окну функции МАКСА, где Значение1, Значение2, ... — это от 1 до 30 значений, среди которых ищется наименьшее. 38 Определение значения, отстоящего от наибольшего на К значений. Функция НАИБОЛЬШИЙ Синтаксис функции: НАИБОЛЬШИЙ (Массив, К) Функция определяет К-е наибольшее значение'из множества данных. Эту функцию можно использовать, например, чтобы определить второй (К = 2), третий (К = 3) и т.д. результат в баллах, показанных при тестировании на экзамене. Окно данной функции имеет следующий вид (числовые данные из примера 2.2, г): НАИБОЛЬШИЙ-- Возвращает к-ое . ■ , ШО ' -._._._ . —. ' Массив B2rQ2 наибольшее значение'из множества данных. К позиция(начиная с Значение;4,55 _j***ss uw^ тш ,- {3,52;<!,25;VH,58j Е . • • . •' . mAJSS ■ >' . массиве или | <Ж j Отмена | где Массив — массив или интервал данных, для которых определяется А"-е наибольшее значение; К — позиция (начиная с наибольшей) в массиве или интервале ячг- ек данных. Если п — это количество данных в массиве или интервале, то функция НАИБОЛЬШИЙ (массив; 1) определяет наибольшее значение, а функция НАИБОЛЬШИЙ (массив; л) определяет наименьшее значение. Если Массив пуст, то функция НАИБОЛЬШИЙ показывает значение ошибки #ЧИСЛО! Если К меньше или равно 0 или если К больше, чем число точек данных, то функция НАИБОЛЬШИЙ показывает значение ошибки #ЧИСЛО! Определение значения, отстоящего от наименьшего на К значений. Функция НАИМЕНЬШИЙ Синтаксис функции: НАИМЕНЬШИЙ (Массив, К) Функция определяет К-е наименьшее значение из множества данных. Эту функцию можно использовать, например, чтобы определить предпоследний (К= 2), третий от наименьшего (К = = 3) результат. 39
Окно данной функции аналогично окну функции НАИБОЛЬШИЙ, где Массив — массив или диапазон числовых данных, для которого определяется К-е наименьшее значение; К — это позиция (начиная с наименьшей) в массиве или интервале ячеек данных. Если п — это количество данных в массиве или интервале, то функция НАИМЕНЬШИЙ (массив; 1) определяет наименьшее значение, а функция НАИМЕНЬШИЙ (массив; и) определяет наибольшее значение. Если Массив пуст, то функция НАИМЕНЬШИЙ показывает значение ошибки #ЧИСЛО! Если К меньше или равно 0 или К больше, чем число точек данных, то функция НАИМЕНЬШИЙ показывает значение ошибки #ЧИСЛО! Работу вышеперечисленных функций рассмотрим на следующем примере. Пример 2.2. В условиях примера 2.1 определите: а) наибольшее значение средней заработной платы; б) наименьшее значение средней заработной платы; в) третью, от наименьшей, величину заработной платы; г) вторую, от наибольшей, величину заработной платы. Решение. 1. Сформируем таблицу исходных данных: *рь~ 1 3,52 Г « 3 ■ 4 1,58 5 2,73 Г ,, 7 S 4,1 9 3,72 10 - 11 3,15 4 2,95 14 4,17 15 4,55 16 2. Выберем ячейку, в которую будет выведен результат вычисления ($А$3). 3. Определим наибольшее значение средней заработной платы: > вызовем Мастер функций, нажав кнопку ft на панели инструментов; > в Мастере функций из категории Статистические выберем функцию МАКС -» ОК; > в левом верхнем углу листа Excel появится окно функции МАКС; > нажав кнопку Ди в поле Число1, перейдем на рабочий лист с исходными данными и выделим их мышью (B2:Q2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции МАКС; 40 > в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -» 5,11 тыс. руб. Примечание. Если бы исходные данные содержали текстовые и логические значения, такие, как ИСТИНА и ЛОЖЬ, то необходимо было бы использовать функцию МАКСА. 4. Определим наименьшее значение средней заработной платы: > вызовем Мастер функций, нажав кнопку L1L на панели инструментов; > в Мастере и из категории Статистические выберем функцию МИН -> ОК; > в левом верхнем углу листа Excel появится окно функции МИН; > нажав кнопку Ин в поле Число1, перейдем на рабочий лист с исходными данными и выделим их мышью (B2:Q2). Затем, повторно нажав эту же кнопку, •. ••* ся к окну функции МИН; > в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -» 1,58 тыс. руб. Примечание. Если бы исходные данные содержали текстовые и логические значения, такие, как ИСТИНА и ЛОЖЬ, то необходимо было бы использовать функцию МИНА. 5. Определим третью, от наименьшей, величину заработной платы: Л > вызовем Мастер функций, нажав кнопку " на панели инструментов; > в Мастере функций из категории Статистические выберем функцию НАИМЕНЬШИЙ -» ОК; > в левом верхнем углу листа Excel появится окно функции НАИМЕНЬШИЙ; > нажав кнопку Зт в поле Массив, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:Q2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции НАИМЕНЬШИЙ; > в поле К наберем — 3; > в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -» 2,95 тыс. руб. 41
6. Определим вторую, от наибольшей, величину заработной платы: > вызовем Мастер функций, ^ажав кнопку S*' на панели инструментов; > в Мастере функций из категории Статистические выберем функцию НАИБОЛЬШИЙ -> ОК; > в левом верхнем углу листа Excel появится окно функции НАИБОЛЬШИЙ; > нажав кнопку ЭЙ в поле Массив, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:Q2). Затем, повторно нажав эту же кнопку, возвращаемся к окну функции НАИБОЛЬШИЙ; > в поле К наберем — 2; > в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 4,55 тыс. руб. 2.3. Подсчет частот из массива данных, попадающих в заданные интервалы. Функция ЧАСТОТА Синтаксис функции: ЧАСТОТА {Массив_данных; Двоичный^ массив) Функция подсчитывает появление значений в заданном интервале и может быть использована для построения интервального вариационного ряда. Для построения интервального вариационного ряда необходимо иметь массив данных — это массив или ссылка на множество данных, для которых вычисляются частоты; массив карманов (границы интервалов), где группируются значения массива данных. ЧАСТОТА вводится как формула массива после выделения интервала смежных ячеек, в которые нужно вернуть полученный массив распределения. ЧАСТОТА игнорирует пустые ячейки и тексты. Формулы, возвращающие массивы, должны быть введены как формулы массивов. Окно данной функции имеет следующий вид (числовые данные из примера 2.3): 42 ЧАСТОТА Массив_данн'ык|А1:А30 Чц - #,5;3,2:3,3:5:4,3:£ I - ■" ' црДД-^. — -- -■■-' t Т Т I I Двоичный_массив |в1:ВЗ| *У»^SiS^Sj- . 1 ает распределение частот в виде вертикального массива. Двоичный_массив массив или ссылка на диапазон, в котором группируются значения* массиве_даннь1Х, " ., ' ' ■ Щ Значением ; .<Ж. | Отмена где Массив_данных — массив или ссылка на множество данных, для которых вычисляются частоты. Если Массив_данных не содержит значений, то функция ЧАСТОТА показывает массив нулей; Двоичный_массив — массив или ссылка на множество интервалов, в них группируются значения аргумента Массив_данных. Если Двоич- ный массив не содержит значений, то функция ЧАСТОТА показывает количество элементов в аргументе Массив_данных. Количество элементов в определяемом массиве на единицу больше числа элементов в массиве Двоичный_массив. Дополнительный элемент в определяемом массиве содержит количество значений, больших, чем максимальное значение в интервалах. Например, при подсчете трех диапазонов значений (интервалов), введенных в три ячейки, убедитесь в том, что функция ЧАСТОТА возвращает значения в четырех ячейках. Дополнительная ячейка показывает число значений в массиве Мас- сив_данных, больших, чем значение границы третьего интервала. Пример 2.3. Постройте интервальный вариационный ряд для следующего массива данных: 2,5; 3,2; 3,3; 5,0; 4,3; 6,2; 3,9; 7,5; 9,1; 6,4; 3,8; 8,5; 7,5; 8,6; 9,4; 2,5; 0,7; 8,6; 6,3; 6,8; 7,2; 4,7; 5,8; 1,9; 2,5; 5,5; 6,3; 7,1; 8,7; 5,0 границы интервалов (массив карманов): 2,5; 5,0; 7,5. Решение. Интервальный вариационный ряд будет состоять из двух строк. В первой определены границы интервалов, затем произведем подсчет значений из массива данных, попавших в заданные интервалы, и запишем это число. В результате получим интервальный вариационный ряд (табл. 2.2). 43
Интервальный вариационный рад Таблица 2.2 Интервалы Частота 0-2,5 5 2,6-5,0 8 5,1-7,5 11 7,6-10,0 6 Решим данный пример с использованием функции ЧАСТОТА Алгоритм действий следующий: 1. сформируем исходные данные в виде двух столбцов: массива данных (А1:А30) и массива карманов (В1:ВЗ): 1 *-> 3 4 5 6 7 8 9 1С 11 11 13 и 15 № 17 18 19 20 21 22 23 24 25 26 27 28 29 30 А 3.2 3.3 5,0 4,3 6,2 3,9 7,5 9,1 6,4 3,8 8,5 7,5 8,6 9,4 25 0.7 8,6 63 65 7.2 4.7 5В 15 2.5 5.5 6,3 7.1 6,7 5,0 В 2,5 5 7,5 2. так как массив карманов содержит три значения, мышью выделим четыре (3 + 1) вертикально смежные ячейки для вывода частот попаданий значений из массива данных в заданные интервалы (Н5:Н8); 3. вызовем Мастер функций, нажав кнопку •* на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ЧАСТОТА -» ОК; 44 5. в левом верхнем углу листа Excel появится окно функции ЧАСТОТА; 6. нажав кнопку -У в поле Массив данных, перейдем на рабочий лист с исходными данными и выделим его мышью (А1: А30). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ЧАСТОТА; 7. нажав кнопку Jh в поле Двоичный_массив, перейдем на рабочий лист с данными границ интервалов и выделим его мышью (В1:ВЗ). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ЧАСТОТА; 8. в окне функции появится результат решения. Наберем комбинацию клавиш CTRL+SHIFT+ENTER (ввод формулы массива); 9. в окне функции появится результат решения (первый элемент массива). В ячейках Н5:Н8 появится результат вычислений — число значений из массива данных, попавших в заданные интервалы: НО Ш 2.4. Оценка относительного положения точки. Функция ПРОЦЕНТРАНГ Синтаксис функции: ПРОЦЕНТРАНГ (Массив; X; Значимость) Функция используется для оценки относительного положения некоторого значения в наборе данных. Оценка проводится в относительных величинах. При этом наибольшему значению соответствует 1,0; наименьшему значению соответствует 0. Эту функцию можно, например, использовать для оценки подходящего результата тестирования среди всех полученных результатов. Если интересующий нас результат отсутствует (не соответствует ни одному из значений массива), то функция ПРОЦЕНТРАНГ проводит интерполяцию и определяет относительное положение этого результата. Окно данной функции имеет следующий вид (числовые данные из примера 2.4): 45
«ТРОЦЕНТРАНГ, Массив jSii U2 X 73 ^У »'-{48;56;31;45;62;?3.*,. Значимость |з| Возвращает процентную норму значения в множестве данных, - , =» 0,631' ' Значимость необязательное значение, определяющее количество значащих цифр е'возерамаеиом значении процентного содержания, щ Значение; 0,631 ОК- Отмена где Массив — массив или интервал данных с численными значениями, который определяет относительное положение; X — значение, для которого определяется процентное содержание; Значимость — необязательное значение, определяющее количество значащих цифр в определяемой величине процентного содержания значения. Если этот аргумент опущен, то функция ПРОЦЕНТРАНГ использует три цифры (0,ххх%). Если Массив пуст, то функция ПРОЦЕНТРАНГ показывает значение ошибки #ЧИСЛО! Если Значимость < 1, то функция ПРОЦЕНТРАНГ показывает значение ошибки #ЧИСЛО! Если X не соответствует ни одному из значений аргумента Массив, то функция ПРОЦЕНТРАНГ производит интерполяцию и определяет корректное значение процентного содержания. Пример 2.4. При приеме экзаменов в вузе используется балльная система оценок. Максимальное количество баллов, которое может набрать абитуриент, равно 100. В группе абитуриентов получены следующие результаты (табл. 2.3). Таблица 2.3 Результаты сдачи экзаменов в баллах Порядковый номер абитуриента Количество баллов • 48 2 56 3 81 4 45 5 62 6 73 7 82 8 87 9 36 10 40 " 51 12 62 13 67 14 80 15 82 16 38 17 49 18 69 19 73 20 84 Определите процентный ранг значения 73. Решение. Процентный ранг определяется следующим образом. Рассматриваемая выборка ранжируется. Определяется ранг данного значения г. Процентный ранг рассчитывается по формуле: 46 Процентный ранг= , и-1 где и — объем выборки. 13-1 Для условий задачи Процентный ранг= = 0,631. Решим данный пример с использованием функции ПРОЦЕНТРАНГ. Алгоритм действий следующий: 1. сформируем исходные данные в виде таблицы: 2. выберем ячейку, в которую будет выведен результат вычисления ($А$6); 3. вызовем Мастер ■■ , нажав кнопку Гж на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ПРОЦЕНТРАНГ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ПРОЦЕНТРАНГ; 6. нажав кнопку Ш в поле Массив, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:U2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ПРОЦЕНТРАНГ; 7. в поле X наберем значение 73; 8. в поле Значимость наберем значение 3; 9. в окне функции появится результат решения. После нажатия кнопки ОК в ячейке А6 появится результат расчета — 0,631. 2.5. Определение величины, соответствующей ее относительному положению. Функция ПЕРСЕНТИЛЬ Синтаксис функции: ПЕРСЕНТИЛЬ {Массив; К) Функция используется для определения некоторого значения из набора данных, соответствующего заданному относительному положению этого значения. Набор данных должен содержать не 47
более 8191 значений. Если число к не кратно величине и-1 где п — общее число значений в наборе данных, то функция / ПЕРСЕНТИЛЬ производит интерполяцию для определения I значения к-и переменной, к — значение персентиля в интервале I от 0 до 1 включительно. Эта функция может быть использована,' например, для определения границы изменяемости значений из набора данных, причем наименьшее значение имеет персентиль О, наибольшее значение — 1. I Окно данной функции имеет следующий вид (числовые данные из примера 2.5): IfTbttHicHib''* — .—— _—. - . ■ — Массив 82:112 = {43;56;81;45;62;73. j— ~-— »'*:"»- ' К|0,6 2и = 0,б Возвращает k-yto персентиль для значений диапазона. ;70,6 К значение . от 0 до 1 т Значение; 70,6 Отмена где Массив — массив или интервал данных с численными значениями, определяющий относительное положение; К — значение персентиля в интервале от 0 до 1 включительно. Если Массив пуст или содержит более 8191 точек данных, то функция ПЕРСЕНТИЛЬ показывает значение ошибки #ЧИС- ЛО! Если К не является числом, то функция ПЕРСЕНТИЛЬ показывает значение ошибки #ЗНАЧ! Если К меньше 0 или К больше 1, то функция ПЕРСЕНТИЛЬ показывает значение ошибки #ЧИСЛО! Пример 2.5. В условиях примера 2.4 принято решение принимать в вуз тех абитуриентов, которые набрали количество баллов, превышающее 60-й персентиль (К = 0,6). Определите количество баллов, соответствующее этому персентилю. Решение. Вначале определим процентранг для каждого значения (балла) и выберем значения, у которых процентранг больше и меньше заданного к = 0,6:69^0,578, 73-»0,631; используя ли- 48 нейную интерполяцию, определим количество баллов, соответствующее заданному персентилю: Количество баллов= 69+ (0,6-0,578) = 70,6. 0,631-0,578 Решим данный пример с использованием функции ПЕРСЕНТИЛЬ. Алгоритм действий следующий: 1. сформируем исходные данные в виде таблицы: т ' 1 ГДЯИ А № абитуриента Количество Баллов В 1 48 С 2 56 "D" 3 81 4 45 5 62 6 73 7 82 8 87 9 36 10 40 11 51 12 13 62 |67 14 80 15 82 16 38 17 49 18 69 19 73 20 84 2. выберем ячейку, в которую будет выведен результат вычисления ($А$6); нажав кнопку на панели ин- 3. вызовем Мастер п 1 струментов; 4. в Мастере функций из категории Статистические выберем функцию ПЕРСЕНТИЛЬ -» ОК; 5. в левом верхнем углу листа Excel появится окно функции ПЕРСЕНТИЛЬ; 6. нажав кнопку ~Ж в поле Массив, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:U2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ПЕРСЕНТИЛЬ; 7. в поле К наберем значение 0,6; 8. в окне функции появится результат решения. После нажатия кнопки ОК в ячейке А6 появится результат расчета — 70,6. 2.6. Определение числа перестановок. Функция ПЕРЕСТ Синтаксис функции: ПЕРЕСТ (Число; Выбранноечисло) Соединения, каждое из которых содержит к различных элементов к<п, взятых из совокупности элементов объема и, отличающихся друг от друга или составом элементов, или их порядком, называются перестановками (размещениями) из п элементов по А: в каждом. Число таких перестановок обозначается Ркп (Р„*) и определяется по формуле: 49
PKn = Т^ТГ, = <« ~ *)(« - 2)...(и - * +1). (2.1) Соединения, в каждое из которых входили все п элементов рассматриваемой совокупности к = п и которые отличаются друг от друга только порядком элементов, также являются перестановками из п элементов. Число таких перестановок • и! Рп=~( г = >й (2-2) (и-и)! Окно данной функции имеет следующий вид (числовые данные из примера 2.6): •«ТРЕСТ »1ЖЖ_ SfciffigjjSffljg Число J9 _ ЗУ = 9 Выбранное _число |4^} = 4 «30Z4-. Возвращает количество перестановок для заданного числа объектов. Вы6ранное_число целое число, задающее количество объектов в каждой перестановке. ® Значение:3024 ОК I Отмена где Число — целое число, задающее количество объектов; Выбранное_число — целое число, задающее количество объектов в каждой перестановке. Оба аргумента усекаются до целых. Если Число или В. 11 ■ i -_число не • • • я числом, то функция ПЕРЕСТ по . ■ - значение ошибки #ЗНАЧ! Если Число <= 0 или В. м. i i _число < 0, то функция ПЕРЕСТ по . : значение ошибки #ЧИСЛО! Если Число < Выбранное_число, то функция ПЕРЕСТ показывает значение ошибки #ЧИСЛО! Пример 2.6. На девяти карточках записаны цифры 1, 1, 3, 4, 5, 6, 7, 8, 9. Берут четыре карточки и составляют из цифр, записанных на них, четырехзначное число. Сколько различных четырехзначных чисел можно составить таким образом? Решение. Всего различных комбинаций из четырех карточек можно составить столько, сколько существует перестановок из 9 элементов по 4: 50 Ра = — = 9-8-7-6 = 3024. (9-4)! Решим данный пример с использованием функции ПЕРЕСТ. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычисления ($А$1); 2. вызовем Мастер функций, нажав кнопку JsJ на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию ПЕРЕСТ -> ОК; 4. в левом верхнем углу листа Excel появится окно функции ПЕРЕСТ; 5. в поле Число наберем значение 9 (число карточек); 6. в поле Выбранное_число наберем значение 4; 7. в окне функции появится результат решения. После нажатия кнопки ОК в ячейке А1 появится результат расчета — 3024. 2.7. Определение ранга числа в списке чисел. Функция РАНГ Синтаксис функции: РАНГ (Число; Ссылка; Порядок) Ранг числа — это его величина относительно других значений в списке чисел. Если список чисел отсортировать, то ранг числа будет его позицией в этом списке. Список чисел может быть отсортирован в порядке возрастания и в порядке убывания. Для этого в функцию РАНГ вводится число, которое носит название «порядок», и это число определяет способ упорядочения. Если «порядок» равен нулю или опущен, то Excel отсортировывает список в порядке убывания, если «порядок» отличен от нуля, то список сортируется в порядке возрастания. РАНГ присваивает повторяющимся числам одинаковый ранг. Однако наличие повторяющихся чисел влияет на ранг последующих чисел. Например, для списка целых, если число 10 появляется дважды и имеет ранг 5, то число 11 будет иметь ранг 7 (и никакое число не будет иметь ранг 6). Окно данной функции имеет следующий вид (числовые данные из примера 2.7): 51
-РАНГ , " Чисяо |b2:U2 ■ ■ :. f*- ■ Ссылка |B2:U2 "3-48 Порядок jo| ~5J =. ^48iS6;81 j4Sj62;73 "l 2у-ложь lie Возвращает ранг числа в списке чисел: его порядкоеи номер относительно других чисел в списке. Порядок число, определяющее способ округления. Значение: 16 ок Отмена где Число — число, для которого определяется ранг; Ссылка — это массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются; Порядок — это число, определяющее способ упорядочения. Пример 2.7. В условиях примера 2.3 провести ранжирование полученного ряда в порядке убывания. Решение. В результате ранжирования получим следующий ранжированный ряд (табл. 2.4). Т а б л и ц а 2.4 № абитуриента Количество баллов РАНГ 1 48 16 J 2 56 13 Ганою 3 81 5 ярова 4 45 17 нный 5 62 11 рад 6 73 7 7 82 3 8 87 1 9 36 20 10 40 18 Окончание табл. 2.4 абитуриента Количество баллов РАНГ И 51 14 12 62 11 13 67 10 14 80 6 15 82 3 16 38 19 17 49 15 18 69 9 19 73 7 20 84 2 Решим данный пример с использованием функции РАНГ. Алгоритм действий следующий: 1. сформируем исходные данные в виде таблицы: so | 1а6игуриента рсшюбш» 4g 5б gl 45 б2 73 82 87 36 40 51 62 67 80 82 38 49 69 73 84 10 11 12 13 14 15 16 17 18 19 20 2. выберем диапазон, в который будет выведен результат вычислений (B5:U5); 3. вызовем Мастер функций, нажав кнопку f" на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию РАНГ -» ОК; 5. в левом верхнем углу листа Excel появится окно функции РАНГ; 6. нажав кнопку Jm в поле Число, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:U2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции РАНГ; 7. нажав кнопку Зт в поле Ссылка, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:U2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции РАНГ; 8. в поле Порядок наберем значение 0 (Excel отсортировывает список в порядке убывания рангов); 9. наберем комбинацию клавиш CTRL+SHIFT+ENTER (ввод формулы массива); 10. в окне функции появится результат решения (первый элемент массива). В ячейках B5:U5 появится результат вычислений — список рангов для количества баллов каждого абитуриента: [Кол-во белов 48 56 81 45 62 16 13 5 17 11 73 7 82 87|36 1 | ' "! 3 1 20 40 51 62 18 14 11 67 ВО 82 38 49 69 73|84 ill ill 10 6 3 19 15 9 7 2 Примечание. Как было отмечено ранее, функция РАНГ присваивает повторяющимся числам одинаковый ранг. Однако наличие повторяющихся чисел влияет на ранг последующих чисел. В нашем примере число 82 появилось дважды и имеет ранг 3, тогда число 81 имеет ранг 5 (и никакое число не имеет ранг 4). 53
3. Определение характеристик положения 3.1. Вычисление среднего. Функции СРЗНАЧ, СРЗНАЧА, СРГЕОМ, СРГАРМ, УРЕЗСРЕДНЕЕ Вычисление среднего арифметического значения. Функция СРЗНАЧ Синтаксис функции: СРЗНАЧ (Число 1, Число2, ...) Среднее арифметическое значение определяется по формуле: * = -£>,, (3.1) где п — число значений; Xj — значения величин, для которых рассчитывается среднее (аргументы). Если аргумент функции является массивом или ссылкой и содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются, однако, ячейки, содержащие нулевые значения, учитываются. Окно данной функции имеет следующий вид (числовые данные из примера 3.1): ,-СРЗНАЧ- J. . ' i '' J" _ Число! JAliKll ~ ' 3- ■P;4;4;5;3;3;5;3;5;': j Чисж>г| 3" •-•'■■ ■ : '.--%% ' ■. среднее (арифметическое) своих аргументов, которые «oryr быть числами или именами, " или ссылками на ячейки с числами, , ' ■ ,..',• Чисяо1:число1зчисло2;'... от 1 до 30 аргументов, для которых вычисляется ■•','" ■ ■ • среднее, ■ ■ ' ' ' ■ ., © ,. , ' • -: : айчение;3,9- ',..-,' 1 ОК ( Отмена где Число1, Число2, ... — от 1 до 30 числовых аргументов, для которых вычисляется среднее. Пример 3.1. Имеются данные о баллах, набранных студентами на экзамене: 3; 4; 4; 5; 3; 3; 5; 3; 5; —; 4. Определите средний балл. 54 Решение. _ 1 " 1 * = -£*! =— (3 + 4 + 4 + 5 + 3 + 3 + 5 + 3 + 5 + 4) = 3,9. и£Г Ю Решим данный пример с использованием функции СРЗНАЧ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных (в ячейке Л введен дефис вместо числа): ^^ЛЛЖМ^^ 'Е р.ТГь Гн-ХТ-згс 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); 3. вызовем Мастер функций, нажав кнопку & на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию СРЗНАЧ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции СРЗНАЧ; 6. нажав кнопку Зш в поле Число1, перейдем на рабочий лист с исходными данными и вьщелим его мышью (А1:К1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СРЗНАЧ; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -» 3,9 (функция СРЗНАЧ дефис в ячейке Л проигнорировала). Вычисление среднего арифметического значения. Функция СРЗНАЧА Синтаксис функции: СРЗНАЧА (Значение!, Значение2, ...) Среднее арифметическое значение определяется по формуле (3.1). При использовании этой формулы кроме чисел в расчете могут участвовать текст и логические значения, такие, как ИСТИНА и ЛОЖЬ. Аргументы должны быть числами, именами, массивами или ссылками. Массивы и ссылки, содержащие текст, интерпретируются как 0 (ноль). Пустой текст интерпретируется тоже как ноль. <5
Аргументы, содержащие значение ИСТИНА, интерпретируются как 1 (единица), а содержащие значение ЛОЖЬ, интерпретируются как 0 (ноль). Окно данной функции аналогично окну функции СРЗНАЧ, где Значение^ Значение2,... — от 1 до 30 ячеек, интервалов ячеек или значений, для которых вычисляется среднее. При решении примера 3.1 с использованием функции СРЗНА- ЧА результат будет отличен от результата функции СРЗНАЧ. Среднее арифметическое будет равно 3,55, так как дефис интерпретировался как 0. Вычисление среднего геометрического значения. Функция СРГЕОМ Синтаксис функции: СРГЕОМ (Число 1, Число2, ...) Среднее геометрическое — одна из форм средней величины. Вычисляется как корень и-й степени из произведения отдельных значений — вариантов признака х,: х = !{jxl-x2-...-xn , (3.2) где и — число значений признака; Xj — значения признака. Среднее геометрическое, в частности, применяется в статистических расчетах при исчислении средних темпов роста. При использовании данной функции аргументы должны быть числами, именами, массивами или ссылками, содержащими числа. Если аргумент, являющийся массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются, но ячейки с нулевыми значениями учитываются. Окно данной функции аналогично окну функции СРЗНАЧ, где Число1, Число2, ... — от 1 до 30 числовых аргументов, для которых вычисляется среднее геометрическое. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Пример 3.2. Имеются данные о темпах роста производства продукции. Год Темп роста 1990 — 1991 1,111 1992 1,078 1993 1,075 1994 1,094 56 Определите средний темп роста производства за 1990— 1994 годы. Решение. Средний темп роста определим как среднюю геометрическую по формуле (3.2): ТР = ЦЦ11 • 1,078 • 1,075 • 1,094 = 1,089 (108,9%). Решим данный пример с использованием функции СРГЕОМ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: ||г°д" ■Темп роста 1990 1991 1,111 1992 1,078 1993 1,075 1994 1,094 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку m на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию СРГЕОМ -» ОК; 5. в левом верхнем углу листа Excel появится окно функции СРГЕОМ; 6. нажав кнопку Ju в поле Число 1, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:F2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СРГЕОМ; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -» 1,089 (функция СРГЕОМ дефис в ячейке В2 проигнорировала). Вычисление среднего гармонического значения. Функция СРГАРМ Синтаксис функции: СРГАРМ (Число], Число2, ...) Среднее гармоническое — одна из форм средней величины. Вычисляется из обратных значений признака по формуле средней гармонической невзвешенной: * = ^ГТ> (3-3) £- 57
где обратные значения вариантов признака; и — число вариантов. Среднее гармоническое всегда меньше среднего геометрического, которое всегда меньше среднего арифметического. При использовании данной функции аргументы должны быть числами, именами, массивами или ссылками, содержащими числа. Если аргумент, представляющий собой массив или ссылку, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются, но ячейки с нулевыми значениями учитываются. Если любая из точек данных меньше или равна 0, то функция СРГАРМ показывает значение ошибки #ЧИСЛО! Окно данной функции аналогично окну функции СРЗНАЧ, где Число1, Число2, ... — от 1 до 30 числовых аргументов, для которых вычисляется среднее гармоническое. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Пример 3.3. Имеется три одинаковых участка для выращивания пшеницы, на которых урожайность составила 22, 26 и 30 ц/га соответственно. Определите среднюю урожайность. Решение. Среднюю урожайность вычислим по формуле средней гармонической невзвешенной (3.3): 3 х = — — = 25,862 ц/га. 22 + 26 + 30 Решим данный пример с использованием функции СРГАРМ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: 8 : С 1|22=|2Б |30 сШ 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); 3. вызовем Мастер функций, нажав кнопку ** на панели инструментов; 58 4. в Мастере функций из категории Статистические выберем функцию СРГАРМ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции СРГАРМ; 6. нажав кнопку -и в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:С1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СРГАРМ; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений ->• 25,862. Вычисление среднего без крайних экстремальных значений. Функция УРЕЗСРЕДНЕЕ Синтаксис функции: УРЕЗСРЕДНЕЕ {Массив, Процент) Эта функция используется в тех случаях, когда необходимо исключить из анализа выбросы (крайние экстремальные значения). Для исключения крайних значений задается доля (процент) значений, исключаемых из вычислений. При этом количество отбрасываемых значений округляется до ближайшего целого числа, кратного 2. Значения, не включаемые в обработку, отбрасываются из начала и конца множества (наибольшие и наименьшие значения). Среднее вычисляется из числа оставшихся значений по формуле (3.1). Окно данной функции имеет следующий вид (числовые данные из примера 3.4): УРЕЗСРЕДНЕЕ-,-— "•-.. --,--'- -- - -;-,--- • , Массив|Д1:01 '^М'- ■{38<?;45,6;53J6;52J' Процент |о,2 2" 5J-"°j2 , ' . •• ■ ' • , ' ■ '■ • *, , .-«,73076923 •Возвращает среднее внутренней части множестве ванных, - ' ' ■ ■„■.'■ Процент дробное число точек данных, исключаемых из вычислений,. Щ " , Значение: 48д73_ ' ОК ( Отмена где Массив — массив или интервал усредняемых значений; Процент — доля точек данных, исключаемых из вычислений. Например, если Процент = 0,2, то 4 точки исключаются из множества 59
I данных, содержащих 20 точек (20 • 0,2), 2 точки с наибольшими зна- , чениями и 2 точки с наименьшими значениями в множестве данных. Если Процент меньше 0 или Процент больше 1, то функция УРЕЗСРЕДНЕЕ показывает значение ошибки #ЧИСЛО! Если ] Процент = 0,1, то 10 процентов от 30 точек данных составляют 3 i точки, но из соображений симметрии функция УРЕЗСРЕДНЕЕ исключит по одному значению из начала и конца множества. Пример 3.4. Имеются данные о скорости автомобилей на одном из участков дороги (км/ч): 38,7; 45,6; 53,6; 52,7; 50,3; 49,6; 48,7; 51,2; 46,7; 47,6; 48,1; 50,1; 49,2; 43,6; 50,1. Определите среднюю скорость автомобилей, исключив 20% экстремальных значений. i Решение. Всего получено 15 значений скорости. 20% составляют 3 значения. Значит, необходимо исключить из обработки одно наибольшее (53,6) и одно наименьшее (38,7) значения. По оставшимся результатам вычислим среднее по формуле (3.1): ] х = — (45,6 + 52,7 + 50,3 + 49,6 + 48,7 + 51,2 + 46,7 + 47,6 + 48,1 + 50,1 + + 50,1+ 49,2+ 43,6+ 50,1) = 48,73 км/ч. I Решим данный пример с использованием функции УРЕЗСРЕДНЕЕ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: .*Т387| 45,61 53,6[ 5271 50,з| 49,б| 48/| 51^ 46,7147^1 48Т1 И,1| 49^ 4ЗД 50/1] 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); 3. вызовем Мастер функций, нажав кнопку J™J на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию УРЕЗСРЕДНЕЕ -► ОК; 5. в левом верхнем углу листа Excel появится окно функции УРЕЗСРЕДНЕЕ; 6. нажав кнопку Ш в поле Массив, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:01). За- 60 I тем, повторно нажав эту же кнопку, возвратимся к окну функции УРЕЗСРЕДНЕЕ; 7. в поле Процент введем долю точек данных, исключаемых из вычислений, равную 0,2; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 48,7. 3.2. Определение моды в интервале данных или массиве. Функция МОДА Синтаксис функции: МОДА (Число!, Число2, ...) Мода (Мо) представляет собой значение изучаемого признака, повторяющееся с наибольшей частотой. При использовании данной функции аргументы должны быть числами, именами, массивами или ссылками, содержащими числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются, но ячейки с нулевыми значениями учитываются. Если количество данных (значение признака) не содержит одинаковых значений, то функция МОДА выдает значение ошибки #Н/Д. Окно данной функции имеет следующий вид (числовые данные из примера 3.5): ЛОДА Число1 |д1:Н1 ~ ~ " __ ~jjj| - {4;3;4;3;3)ejZ;6} Чистой] ^ '_" ~~" 2jj- PWF i -3 значение моды множества данных. Чисяо1: число1;чиою2;.„ от 1 до 30 аргументов, для которых вычисляется мода. Значением ОК I Отмена где Число1, Число2, ... — от 1 до 30 числовых аргументов, для которых вычисляется мода. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. 61
Пример 3.5. Рабочие бригады, состоящей из восьми человек, имеют следующие тарифные разряды: 4; 3; 4; 3; 3; 6; 2; 6. Определите моду. I Решение. Так как в данной бригаде больше всего рабочих 3-го разряда, то этот тарифный разряд и будет модальным: Мо=3. Решим данный пример с использованием функции МОДА, Алгоритм действий следующий: 1. сформируем таблицу исходных данных: А В С , О jSTn 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3): 3.-вызовем Мастер функций, нажав кнопку г на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию МОДА -> ОК; 5. в левом верхнем углу листа Excel появится окно функции МОДА; 6. нажав кнопку За в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:Н1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции МОДА; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 3. 3.3. Определение медианы. Функция МЕДИАНА Синтаксис функции: МЕДИАНА (Число1, Число2, ...) Медиана (Me) — это значение признака, приходящееся на середину ранжированной (упорядоченной) совокупности. Другими словами, медиана — число, которое является серединой множества чисел. При этом половина чисел имеет большие значения, чем медиана, половина чисел — меньшие значения, чем медиана. При использовании данной функции аргументы должны быть числами, именами, массивами или ссылками, содержащими числа. Все другие игнорируются. 62 Если в рассматриваемом массиве четное количество чисел, то медиана определяется как среднее двух чисел, находящихся в середине рассматриваемого ряда. Окно данной функции аналогично окну функции МОДА, где Число1, Число2, ... — от 1 до 30 чисел, для которых определяется медиана. Пример 3.6. В условиях примера 3.5 определите медиану. Решение. Для определения медианы надо провести ранжирование рассматриваемой совокупности: 2; 3; 3; 3; 4; 4; 6; 6. Так как в массиве четное количество чисел, то медиана определяется как среднее двух чисел, находящихся в середине ранжированного ряда — 3 и 4, Me = 3,5. Решим данный пример с использованием функции МЕДИАНА. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: ■" •: а [ в- т .щщ__яшшшшящ 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); 3. вызовем Мастер функций, нажав кнопку ^ на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию МЕДИАНА -> ОК; 5. в левом верхнем углу листа Excel появится окно функции МЕДИАНА; 6. нажав кнопку Зя в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (Al: HI). Затем, повторно нажав эту же кнопку, возвратимся к окну функции МЕДИАНА; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 3,5. " * 3.4. Определение квартилей. Функция КВАРТИЛЬ Синтаксис функции: КВАРТИЛЬ (Массив, Значение) 63
Квартили (Q) представляют собой значения признака, делящие ранжированную совокупность на четыре равновеликие части. Различают квартиль нижний (первый) £?ь отделяющий 1Л часть совокупности с наименьшими значениями признака, и квартиль верхний (третий) £>з, отсекающий 1А часть с наибольшими значениями признака. Средним (вторым) квартилем Q2 является медиана. Это означает, что 25% единиц совокупности будут меньше по величине Q\, 25% единиц будут заключены между Qi и £Ь; 25% — между Qi и Qy, остальные 25% превосходят 03- Квартили вариационного ряда хп>, X(j), ..., Х(п\ объемом я вычисляются по формуле: Qq = Щщ) + (1 - q)(X(ng +l)- Хщ), (3.4) где q = 0,25 для Qx\ q = 0,5 для Qi, q = 0,75 для Q3; п • q — целое число. Если п ' д — дробь, то Qq = X([nq] + l), (3.5) где \nq\ — целая часть числа и • q. Окно данной функции имеет следующий вид (числовые данные из примера 3.7): Массив A1;L1 = {3,7;4>3;6,2;6,3;6,{ Значение П 2М = ' Vk." —*-*■ . 6j275 Г Значение значение: минимальное = 0; первый вкартияь = I, медиана = 2; третий ■ = 3; значение = 4, Значение:6,275 ОК J ОтменаJ где Массив — массив или интервал ячеек с числовыми значениями, для которых определяются значения квартилей; Значение — значение, которое необходимо определить. Если Массив пуст или содержит более 8191 точек данных, то функция КВАРТИЛЬ показывает значение ошибки #ЧИСЛО! Если Значение не целое, то оно усекается. Если Значение меньше 0 или Значение больше 4, то функция КВАРТИЛЬ показывает значение ошибки #ЧИСЛО! 64 Пример 3.7. Имеется выборка из генеральной совокупности объемом п = 12: 3,7; 4,3; 6,2; 6,3; 6,6; 7,2; 8,3; 8,4; 9,1; 9,3; 9,9; 10,2. Определите квартили данной выборки. Решение. Qi = 6,2 + (1 - 0,25)(6,3 - 6,2) = 6,275 (пд = 3); Q2 = 7,2 + (1 - 0,5)(8,3 - 7,2) = 7,75 (пд = 6); Q3 = 9,1 + (1 - 0,75)(9,3 - 9,1) = 9,15 (пд = 9). Решим данный пример с использованием функции КВАРТИЛЬ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: Р Е , г_ ■_, ь -и i I J.-.K LJL j 6,2] б,з1 sjbI 7,2ПзЖ1м1 элГэзГэТэ! юif 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); Г" — 3. вызовем Мастер функций, нажав кнопку UZ на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию КВАРТИЛЬ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции КВАРТИЛЬ; 6. нажав кнопку ~У в поле Массив, перейдем на рабочий лист с исходными данными и выделим его мышью (Al: L1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции КВАРТИЛЬ; 7. в поле Значение введем число 1 для расчета первого квартиля; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 6,275. Примечание. Для расчета второго и третьего квартиля необходимо снова вызвать функцию КВАРТИЛЬ, но в поле Значение ввести числа 2 и 3 соответственно. Результат вычислений будет аналогичен решению примера 3.7. Если в поле Значение ввести числа 0 и 4, то результатом вычислений будут наименьшее и наибольшее значения выборки соответственно (3,7 и 10,2). 3 Статистические функции в экономико- статистических расчетах
4. Определение характеристик рассеивания 4.1. Определение среднего линейного отклонения. Функция СРОТКЛ Синтаксис функции: СРОТКЛ (Число 1, Число2, ...) Среднее линейное отклонение вычисляется как средняя арифметическая из абсолютных значений отклонений вариант х, от средней х: <*=-Ek-*l' (4.1) где п — объем выборки; х,- — значения выборки; - If X = — > X, — среднее выборки. При использовании функции аргументы должны быть числами, именами, массивами или ссылками, содержащими числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, содержащие нулевые значения, учитываются. Окно данной функции имеет следующий вид (числовые данные из примера 4.1): : сроткл - ~ ' ■ ' : ~~b1l! ......; :.".. ^wSSSSP Число1 |а1:]1| ЗУ'« {3?;39;35;33;31;38.' Числа2| __ _ _ _ _ За!-" '■ ' ' ■■•■■" • -V абсолютных значений отклонений точек данных от среднего. Аргументами иогут являться числа,.имена, массивы или ссылки на ячейки с числами,. •, . • , "■' , , 'Чисяо1:число1;число2;,..от1до30 - длйкоторых ''■ ' среднее отклонений,''; Щ : .Значение;-^ ;' '",'" |' OK \ ,'Отмена | где Число1, Число2, ... — от 1 до 30 аргументов, для которых определяется среднее абсолютных отклонений. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. 66 Пример 4.1. Имеется выборка объемом п = 10: 37; 39; 35; 33; 31; 38; 42; 45; 47; 43. Определите среднее линейное отклонение. Решение. Вычислим среднее выборки: х = — (37 + 39 + 35 + 33 + 31 + 38 + 42 + 45 + 47 + 43) = 39. 10 ' Определим среднее линейное отклонение: d=—[J37-39| + |39-39|+|35-39|+...+|43-39|]=4,2. Решим данный пример с использованием функции СРОТКЛ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: bJ&Js&S^J^EX&b i 1 | 371 391 351 "ЗЗГ 31 Р7ЧГб~~ 381 42 45 47 J" 43 [ 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); г* 3. вызовем Мастер функций, нажав кнопку Jm на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию СРОТКЛ -> ОК; 5. В левом верхнем углу листа Excel появится окно функции СРОТКЛ; 6. Нажав кнопку Ш в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:Л). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СРОТКЛ; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 4,2. 4.2. Определение суммы квадратов отклонений. Функция КВАДРОТКЛ Синтаксис функции: КВАДРОТКЛ (Число 1, Число2, ...) Сумма квадратов отклонений определяется по формуле: rf2 =£(*,.-*)2, (4.2) где Xj — значения выборки; 3. 67
x = — V jc, — среднее выборки. «,=i При использовании функции аргументы должны быть числами, именами, массивами или ссылками, содержащими числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, содержащие нулевые значения, учитываются. Сумма квадратов отклонений как самостоятельная величина не используется для характеристики или анализа выборки, но может быть использована в дальнейшем для вычисления дисперсии, среднего квадратического отклонения и других величин. Окно данной функции аналогично окну функции СРОТКЛ, где Число1, Число2,... — от 1 до 30 аргументов, для которых определяется сумма :. » тов отклонений. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Пример 4.2. По данным примера 4.1 вычислить сумму квадратов отклонений. Решение. <fl = (37 - 39)2 + (39 - 39)2 + (35 + 39)2 +...+ (43 - 39)2 = 246. Решим данный пример с использованием функции КВАД- РОТКЛ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: 1 l 37J! 39] ЩЗЗ] 311 38 j 421 45] 47f 43| 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); jt ш 3. вызовем Мастер функций, нажав кнопку * на пан ли инструментов; 4. в Мастере функций из категории Статистические выберем функцию КВАДРОТКЛ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции КВАДРОТКЛ; 6. нажав кнопку Jm в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:Л). Затем, повторно нажав эту же кнопку, возвратимся к окну функции КВАДРОТКЛ; 68 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 246. 4.3. Вычисление дисперсии. Функции ДИСП, ДИСПА, ДИСПР, ДИСПРА Вычисление несмещенной оценки дисперсии. Функция ДИСП Синтаксис функции: ДИСП (Число!, Число2, ...) Дисперсия представляет собой средний <•.. i». отклонений значений признака от средней : - .и характеризует рассеивание случайной величины относительно среднего (математического ожидания). Несмещенная оценка дисперсии вычисляется по формулам: s2 = б2 =-!-]•>,-*)2. <4-3> или «1*,2-(2>|>2 s =а = — , (4.4) и(и-1) где п — объем выборки; х,- — значения выборки; _ i и х =—V л: — среднее выборки. Аргументами данной функции являются только числа. Окно данной функции имеет следующий вид (числовые данные из примера 4.3): Число» jM-ji З5--»3*5;13*";1368;1" ,■ - числог) ~^_ 53- ' • •' ■- , ' ', «754,2666667 ' , Эценивает дисперсию по'еыборке (лоигические значения и текст игнорируются). Число!: число1;чисяо2;... от 1 до 30 числовых аргументов, выборке из - совокупности, " Щ1| . ' i Значение:754,3 где Число 1, Число2, ... — от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. 69
ДИСП предполагает, что определяется несмещенная оценка генеральной дисперсии. Если требуется определить выборочную смещенную оценку дисперсии, то используют функцию ДИСПР. Логические значения, такие, как ИСТИНА или ЛОЖЬ, а также текст, игнорируются. Если они не должны игнорироваться, необходимо использовать функцию ДИСПА. Пример 4.3. Из деталей, изготовленных на одном станке, выбраны наугад 10 штук и испытаны на излом. Получены следующие результаты (кг): 1345; 1301; 1368; 1322; 1310; 1370; 1318; 1350; 1303; 1299. Оцените дисперсию сопротивления деталей на излом. Решение. Вычислим среднее значение сопротивления на излом: 3f = —(1345 + 1301 + 1368 +... +1299) = 1328,6 кг. Вычислим дисперсию по формуле (4.3): 52 =—[(1345-1328,6)2 + (1301 -1328,6)2 +... + (1299-1328,6)2]= 754,3 кг?. Решим данный пример с использованием функции ДИСП. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); - ■ 3. вызовем Мастер функций, нажав кнопку t£- на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ДИСП -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ДИСП; 6. нажав кнопку -За в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:Л). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ДИСП; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 754,3. 70 Вычисление несмещенной оценки дисперсии. Функция ДИСПА Синтаксис функции: ДИСПА {Значение1, Значение2, ...) Дисперсия вычисляется по формулам (4.3) или (4.4), но в расчетах помимо числовых значений учитываются текстовые и логические значения. При этом аргументы, содержащие значе- I ние ИСТИНА, интерпретируются как 1 (единица), а аргументы, 1 содержащие текст или значение ЛОЖЬ, интерпретируются как 0 I (ноль). Если текст и логические значения должны игнорироваться, следует использовать функцию ДИСП. Окно данной функции аналогично окну функции ДИСП, где Значение 1, Значение2, ... — от 1 до 30 аргументов, соответст- I вующих выборке из генеральной совокупности. ДИСПА предполагает, что определяется несмещенная оценка I генеральной дисперсии. Если требуется определить выборочную смещенную оценку дисперсии, то используют функцию ДИСПРА. I Вычисление выборочной дисперсии. Функция ДИСПР Синтаксис функции: ДИСПР (Число 1, Число2, ...) Если предполагается вычисление выборочной дисперсии, 1 смещенной оценки дисперсии генеральной совокупности а2, то используются следующие формулы: s2=o2=-j^{xi-x)2 , у. (4.5) или s2=a2= -si ,"* , (4.6) I и2 где и — объем выборки; xt — значения выборки; Ix= — J\xl — среднее выборки. «,=1 I Логические значения, например ИСТИНА и ЛОЖЬ, а также I текст игнорируются. Если они не должны игнорироваться, используют функцию ДИСПРА. I ДИСПР предполагает, что определяется смещенная оценка дисперсии. Если требуется определить исправленную, несмещенную оценку дисперсии, то следует использовать функцию ДИСП. 71
Окно данной функции аналогично окну функции ДИСП, где Число1, Число2, ... — от 1 до 30 числовых аргументов, соответствующих генеральной совокупности. / Пример 4.4. Воспользуемся данными примера 4.3 и определим выборочную дисперсию. Решение. х = 1328,6 кг; s2 = — [(1345-1328,6)2 +(1301-1328,6)2 +...+(1299-1328,6)2]= = 678,84 кг2. Решим данный пример с использованием функции ДИСПР. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: А 11 1345Щ3011 1368|1322| 1310 F Gi.h ;_!_ 1370 131 ё [1ЗЭЗТ1303 J 1299J 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); 3. вызовем Мастер функций, нажав кнопку < на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ДИСПР -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ДИСПР; 6. нажав кнопку Зм в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:Л). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ДИСПР; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 678,84. Вычисление выборочной дисперсии. Функция ДИСПРА Синтаксис функции: ДИСПРА (Значение!, Значение 2, ...) Дисперсия вычисляется по формулам (4.5) или (4.6), но в расчетах помимо числовых значений учитываются текстовые и логические значения. При этом аргументы, содержащие значение ИСТИНА, интерпретируются как 1 (единица), а аргументы, содержащие текст или значение ЛОЖЬ, интерпретируются как 0 (ноль). 72 Окно данной функции аналогично окну функции ДИСПА, где Значение1, Значение2, ... — от 1 до 30 аргументов, соответствующих генеральной совокупности. ДИСПРА предполагает, что определяют смещенную оценку дисперсии. Если требуется определить исправленную, несмещенную оценку дисперсии, то следует использовать функцию ДИСПА. Если текст и логические значения должны игнорироваться, следует использовать функцию ДИСПР. 4.4. Вычисление стандартного (среднего квадратического) отклонения. Функции СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА Вычисление исправленной оценки стандартного (среднего квадратического) отклонения по выборке. Функция СТАНДОТКЛОН Синтаксис функции: СТАНДОТКЛОН (Число 1, Число2, ...) Стандартное (среднее квадратическое) отклонение характеризует рассеивание случайной величины относительно центра распределения (средней величины). Исправленная оценка стандартного (среднего квадратического) отклонения по выборке определяется по формуле: -а = ,|^2>,-Т. <«> или s = a = »Z*?-(2>i>2 i=1 , (4-8) «(«-1) где n — объем выборки; Xj — значения выборки; _ i « х = —'У.х, — среднее выборки. ««=1 Аргументами данной функции являются только числа. СТАНДОТКЛОН предполагает, что определяется исправленная оценка стандартного отклонения. Если требуется определить 73
выборочное стандартное отклонение, то следует использовать функцию СТАНДОТКЛОНП. } Логические значения, такие, „:ак ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если текст и логические значения игнорироваться не должны, следует использовать функцию СТАНДОТКЛОНА. Окно данной функции имеет следующий вид (числовые данные из примера 4.5): стдндотйчон - ——"- ' -"- —- -—-у...::—:: ~;;:...:— "— — " — Чисяо1 |А1:Л| ii-<1345;1301;l368;l; чисяо2"С _ ~ 53= ш 27,46391572 Оценивает стандартное отклонение по выборке. Логические значения или текст игнорируются. Число 1: число 1;число2;.,. от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. . ттп*ш © Значение:27,46 ОК { Отмена ] где Число1, Число2, ... — от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. Можно использовать массив или ссьику на массив вместо аргументов, разделяемых точкой с запятой. ! Пример 4.5. По условиям примера 4.3 определите исправленную оценку стандартного отклонения по выборке. Решение. Вычисление проведем по формуле (4.7) или (4.8), в результате получим £ = 0 = ^754,3 =27,46 кг. Решим данный пример с использованием функции СТАН- дотклон. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: А I 1 | 13451 11301 [1368113221 1310[ 1370|131ёТ1350| 1 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); 3. вызовем Мастер функций, нажав кнопку LJLI на панели инструментов; 74 4. в Мастере функций из категории Статистические выберем функцию СТАНДОТКЛОН -> ОК; 5. в левом верхнем углу листа Excel появится окно функции СТАНДОТКЛОН; 6. нажав кнопку Ш в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:Л). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СТАНДОТКЛОН; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 27,46. Вычисление исправленной оценки стандартного (среднего квадратического) отклонения по выборке. Функция СТАНДОТКЛОНА Синтаксис функции: СТАНДОТКЛОНА (Значение!, Значение!,...) Исправленная оценка стандартного (среднего квадратического) отклонения вычисляется по формулам (4.7) или (4.8), но в расчетах помимо числовых значений учитываются текстовые и логические значения. При этом аргументы, содержащие значение ИСТИНА, интерпретируются как 1 (единица), а аргументы, содержащие текст или значение ЛОЖЬ, интерпретируются как 0 (ноль). СТАНДОТКЛОНА предполагает, что определяется исправленная оценка стандартного отклонения. Если определяется выборочное стандартное отклонение, то следует использовать функцию СТАНДОТКЛОНПА. Если текст и логические значения должны игнорироваться, следует использовать функцию СТАНДОТКЛОН. Окно данной функции аналогично окну функции СТАНДОТКЛОН, где Значение1, Значение2, ... — от 1 до 30 аргументов, соответствующих выборке из генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Вычисление выборочного стандартного (среднего квадратического) отклонения. Функция СТАНДОТКЛОНП Синтаксис функции: СТАНДОТКЛОНП (Число!, Число2, ...) Если предполагается вычисление выборочного стандартного (среднего квадратического) отклонения, то используются формулы 75
S = 5 = J-Z(*.-*)2. (4-9) Vn 1=1 или l«i>,2-(2>i)2 s = о = I '=' ^2 , (4.10) где и — объем выборки; Xj — значения выборки; 1 » х = — V х- — среднее выборки. «,=i Аргументами данной функции являются только числа. СТАНДОТКЛОНП предполагает, что определяется выборочное стандартное отклонение. Если требуется определить исправленную оценку стандартного отклонения, то следует использовать функцию СТАНДОТКЛОН. Для больших выборок СТАНДОТКЛОН и СТАНДОТКЛОНП приводят к примерно равным значениям. Логические значения, такие, как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если текст и логические значения игнорироваться не должны, следует использовать функцию СТАН- ДОТКЛОНА. Окно данной функции аналогично окну функции СТАНДОТКЛОН, где Число1, Число2, ... — от 1 до 30 числовых аргументов, соответствующих генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Пример 4.6. По условию примера 4.4 определите выборочное стандартное (среднее квадратическое) отклонение. Решение. Вычисление проведем по формуле (4.9) или (4.10), в результате получим s = о = V678.84 = 26,05 кг. Решим данный пример с использованием функции СТАНДОТКЛОНП. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: 76 ГЧ>А | в КС i 1 1345113011 1368 13227 1310 F 1370 0 1318 i H/' I ■ М 1350 1303112991 | 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); ! 3. вызовем Мастер функций, нажав кнопку ^Li на панели инструментов; | 4. в Мастере функций из категории Статистические выберем функцию СТАНДОТКЛОНП -► ОК; 5. в левом верхнем углу листа Excel появится окно функции СТАНДОТКЛОНП; 6. нажав кнопку -51 в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:Л). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СТАНДОТКЛОНП; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -» 26,05. I Вычисление выборочного стандартного (среднего квадратического) отклонения. Функция СТАНДОТКЛОНПА Синтаксис функции: СТАНДОТКЛОНПА (Значение1, Значение!, ...) \ Стандартное (среднее квадратическое) отклонение определяется по формулам (4.9) или (4.10), но в расчетах помимо числовых значений учитываются текстовые и логические значения. При этом аргументы, содержащие значение ИСТИНА, интерпретируются как 1 (единица), а аргументы, содержащие текст или значение ЛОЖЬ, интерпретируются как 0 (ноль). СТАНДОТКЛОНПА предполагает, что определяется выборочная оценка стандартного отклонения. Если определяется исправленная оценка стандартного отклонения, то следует использовать функцию СТАНДОТКЛОНА. Если текст и логические значения должны игнорироваться, следует использовать функцию рабочего листа СТАНДОТКЛОНП. Для больших выборок СТАНДОТКЛОНА и СТАНДОТКЛОНПА приводят к примерно равным значениям. Окно данной функции аналогично окну функции СТАНДОТКЛОНА, где Значение1, Значение2,... — от 1 до 30 аргументов, соответствующих генеральной совокупности. Можно ис- 77
пользовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. 4.5. Определение асимметрии распределения. Функция СКОС I Синтаксис функции: СКОС (Число!, Число2,...) Асимметрия характеризует степень асимметричности распределения относительно среднего. Положительная асимметрия указывает на преобладание в распределении положительных отклонений от среднего. Отрицательная асимметрия указывает на преобладание в распределении отрицательных отклонений от среднего (рис. 4.1). /(*) А>0 /(*)' I У As<0 X Рис. 4.1. Правосторонняя А > 0 и левосторонняя А < 0 асимметрия I Коэффициент асимметрии определяется по формуле: " (х, -Г3 (и-1Хи-2)£ (4.11) где и — объем выборки; х,- — значения выборки; х = —^Xj — среднее выборки; s = ,| XX*i ~x) ~ стандартное отклонение выборки. Принято считать, что асимметрия выше |0,5| считается значительной, а меньше |0,25| — незначительной. Асимметрия симметричного распределения равна нулю (нормальное, Стьюдента и др.). 78 Аргументами данной функции могут быть числа, имена, массивы или ссылки, содержащие только числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то 4акие значения игнорируются; однако ячейки с нулевыми значениями учитываются, I Если имеется менее трех точек данных или стандартное отклонение равно нулю,\то функция СКОС показывает значение ошибки #ДЕЛ/0! I I Окно данной функции имеет следующий вид (числовые данные из примера 4.7): \ j t 1 скос- ---■■■- - • \ Т-- "«г -ЧГ ■--- jy-{35;27;32;18;30;39 -- ■ - - I Чисяо1|А1£1| Число2|I Возвращает ассшетрию распределения. 1 "3= -0,097800653 Число!: число »число2;... от 1 до 30 аргументов, для которых вычисляется ассимегричность. где Число1, Число2, вычисляется асимметричность, да 30 числовых аргументов, для которых Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Пример 4.7. Имеются данные об урожайности зерновых на различных посевных площатях (ц/га): 35; 27; 32; 18; 30; 39; 24; 28; 31; 21. Определите асимметрию данного распределения. Решение. \ Величину асимметрии определим по формуле (4.11). Предварительно определим: и = 10; Зс = 28,5; 5 = 6,35 \3" А.= 10 (10-1X10-2) 35-28,5 6,35 /т:_ой<Л3 1^27-28,5ч3 6,35 + ...+ 21-28,5 6,35 -0,098. Полученный результат свидетельствует о незначительной по величине и отрицательной по характеру асимметрии. Решим данный пример с использованием функции СКОС. Алгоритм действий следую] ций:
1. сформируем таблицу исходных данных: ±1 35У С Tg 32 18 30 LJJ5 jjHjgriJMl ^24] 2В|31]21} 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); f | 3. вызовем Мастер функций, нажав кнопку L£J на панели инструментов; j J 4. в Мастере функций из категории Статистические выберем функцию СКОС -> ОК; / 5. в левом верхнем углу листа Excel /появится окно функции СКОС; J | 6. нажав кнопку Зш в поле Число1, перейдем на рабочий лист с исходными данными и выделим его ^ышью (А1:Л). Затем, повторно нажав эту же кнопку, возвратимся к окну функции СКОС; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> -0,098. ика 4.6. Определение эксцесса распределения. Функция ЭКСЦЕСС / Синтаксис функции: ЭКСЦЕСС {Число 1, Число2, ...) Эксцесс характеризует относительную остроконечность или сглаженность распределения по сравнению пределением. Эксцесс нормального Положительный эксцесс означает < распределение. Отрицательный эксцесс сглаженное распределение (рис. 4.2). fix) A нормальное распределение с нормальным рас- распределения равен нулю, относительно остроконечное означает относительно Рис. 4.2. Эксцесс распределения 80 Эксцесс определяется по формуле: | п{п+\) (и-1)(и-2Хи-ЗШ 'jsziVL **-р2 f (4Л2) S ) (и-2Хи-3) где п — объем выборки; Xj — значения выборки; S — стандартное отклонение выборки (определяется по формуле (4.7))j х — среднее выборки (определяется по формуле (3.1)). Аргументами функции должны быть числа, имена, массивы или ссылки, содержащие только числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки с нулевыми значениями учитываются. Если задано менее четырех точек данных или если стандартное отклонение выборки равняется нулю, то функция ЭКСЦЕСС показывает значение ошибки #ДЕЛ/0! Окно данной функции имеет следующий вид (числовые данные из примера 4.8): (ЭКСЦЕСС— ■■■—-■-- --———---—------^-ШШГ- —. —- — — -и Число» |au5I JJ « <3S;27;3ZJ 18j30j39 ' ,; . Чиело2.| ОМ*" • ' ■ , ,' ' ,"■-', ."■'' '' = -0,320948531 :' '■ Возвращает эксцесс множества данных. Более подробные сведения приведены в справочной системе. Число!: число 1;число2;.., от 1 до 30 аргументов, для которых вычисляется эксцесс. , . ■ •■ •' - ' , ' ■ ■ ' .. Значение:-0,321 ОК I Отмена где Число1, Число2, ... — от 1 до 30 числовых аргументов, для которых вычисляется эксцесс. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Пример 4.8. В условиях примера 4.7 определите эксцесс распределения. Решение. Величину эксцесса определим по формуле 4.12. Предварительно определим: п = 10; х=28,5; 5=6,35. 81
10(10 + 1) (10-1)(10-2Х10-3) 35-28,5 6,35 + ^27-28,5^4 6,35 f 21 -28,5 'Л 6,35 . / 3(10 -if = -0,321. (10-2)(10-3) Полученный результат свидетельствует об относительно сглаженном распределении по сравнению с нормальным. Решим данный пример с использованием функции ЭКСЦЕСС. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: Е 'I F ! J3 " Н "30РЗЭ| 24Г28Г 2. выберем ячейку, в которую будет выведен результат вычислений ($А$3); т 3. вызовем Мастер функций, нажав кнопку ^* на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ЭКСЦЕСС -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ЭКСЦЕСС; 6. нажав кнопку Jm в поле Число1, перейдем на рабочий лист с исходными данными и выделим его мышью (А1:Л). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ЭКСЦЕСС; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> —0,321. 5. Зависимость случайных величин 5.1. Определение ковариации. Функция КОВАР Синтаксис функции: КОВАР {Maccuel, Массив!) Для однозначного определения системы двух случайных величин кроме статистических оценок математического ожидания и дисперсии необходимо уметь определять статистическую оценку ковариации. Статистическую оценку ковариации определяют по формуле: w(X,Y) = k^ = !]>>,. -х)(У( -у), (5.1) п 1=1 где п — объем двумерной выборки; X/, у/ — значения двумерной выборки; — 1 " х = —^х, — средняя выборки X; п i=i — Iй y = —J\yi — средняя выборки Y. и,-=1 Ковариация характеризует рассеивание и взаимную зависимость этих случайных величин, имеет размерность, равную произведению размерностей случайных величин. Аргументами этой функции должны быть числа или имена, массивы, ссылки, содержащие числа. Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки с нулевыми значениями учитываются. Окно данной функции имеет следующий вид (числовые данные из примера 5.1): rKOBAP-f- Массив1 JB1:K1 ' ' 3~512<!>lV;?°*5<"» ■• i B2:K2 _ 3" <53,г;<И,2;51Л;5?, Г ' Г.'. , '"'..". -, ■■■'.' ' .--2,7094. -. -Возвращает коеариацию, среднее поларньк пршзведений отклонений, Массив? второй диапазон аелых чисея- миела, массивы или ссылки на ' ячейки/содержащие числа. ' , ' т Значение:2,71 83 <Ж 1 ' Отнена I """"'' '" ™""JLj ШШШ Ш ПНЯ
где Массив1 — первый исходный массив, или интервал данных; Массив2 — второй исходный массив, или интервал данных. Если Массив1 и Массив2 имеют различное число данных, то КОВАР показывает значение ошибки #Н/Д. Если либо Mac- ami, либо Массив2 пуст, то КОВАР показывает значение ошибки #ДЕЛ/0! Пример 5.1. Имеется выборка из генеральной совокупности системы двух случайных величин (X, Y): X; У1 12,1 53,2 14,7 44,2 20,5 51,4 11,2 57,7 16,6 45,5 10,0 42,0 13,0 53,5 14,9 68,9 16,3 57,7 15,1 63,3 Определите ковариацию этих случайных величин. Решение. Определим средние: — 1 " * = -]►>,-= 14,4; 7 = -!>,• =53,7; ",=1 Вычислим ковариацию по формуле (5.1): сот(Х,Г) = — [(12,1-14,4)(53,2-53,7)+(14,7-14,4)(44,2-53,7)+...+(15,1- -14,4)(63,3-53,7)] = 2,71. Решим данный пример с использованием функции КОВАР. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: ■ф I 12,1 14,7 гЬ | 53,2 1 44,2 20,5 51,4 11,2 57,7 16,6 45,5 10 42 13 53,5 14,9 68,9 16,3 57,7 15,1 63,3 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку '" на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию КОВАР -+ ОК; 5. в левом верхнем углу листа Excel появится окно функции КОВАР; 84 L 6. нажав кнопку Ж в поле Массив1, перейдем на рабочий лист с исходными данными и выделим его мышью (Bl: K1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции КОВАР; 7. нажав кнопку Ш в поле Массив 1, перейдем на рабочий лист с исходными данными и выделим его мышью (В2: К2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции КОВАР; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений —> 2,71. 5.2. Определение коэффициента корреляции. Функция КОРРЕЛ Синтаксис функции: КОРРЕЛ (Массив 1, Массив!) Ковариация имеет размерность, равную произведению размерностей случайных величин. Более удобной величиной, характеризующейся только зависимостью случайных величин, является коэффициент корреляции: сот(Х,7) Р^= ~У ' (5-2> схоу где [Г ^7 - 1Л Коэффициент корреляции является безразмерной величиной и может изменяться в пределах -1 < р < 1. Аргументы этой функции должны быть числами или именами, массивами или ссылками, содержащими числа, Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки с нулевыми значениями учитываются. Окно данной функции аналогично окну функции КОВАР, где Массив 1 — первый исходный массив или интервал данных; Массив2 — второй исходный массив или интервал данных. 85
Если Массив 1 и Массив2 имеют различное количество точек данных, то функция КОРРЕЛ показывает значение ошибки #Н/Д. Если Массив1 либо Массив2 пуст или если S (стандартное отклонение) их значений равно нулю, то функция КОРРЕЛ показывает значение ошибки #ДЕЛ/0! Пример 5.2. В условиях примера 5.1 определите коэффициент корреляции. Решение. 1. сот(Х,У) = 2,71; 5х =2,89; 5^=8,09. cw(X,Y)_ 2,71 Р^=- ovo, 5,89-8,09 = 0,116. Решим данный пример с использованием функции КОРРЕЛ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: j А I Г В 1 Р. | 12,1 2 >, | 53,2 14,7 44,2 20,5 51,4 1U 57,7 16,6 45,5 10 42 ^■1 13 53^> 14,9 68,9 16,3 57,7 ■Ж 15,1 63,3 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку Ш. на панели инструментов; '• 4. в Мастере функций из категории Статистические выберем функцию КОРРЕЛ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции КОРРЕЛ; 6. нажав кнопку 31 в поле Массив1, перейдем на рабочий лист с исходными данными и выделим его мышью (В1:К1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции КОРРЕЛ; 7. нажав кнопку Ш в поле Массив1, перейдем на рабочий лист с исходными данными и выделим его мышью (В2:К2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции КОРРЕЛ; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,116. 86 5.3. Определение коэффициента корреляции Пирсона. Функции ПИРСОН, КВПИРСОН Определение коэффициента корреляции Пирсона. Функция ПИРСОН. " Синтаксис фикции: ПИРСОН (Массив 1, Массив!) Линейный коэффициент корреляции (Пирсона) характеризует тесноту и направление связи между двумя корреляционными признаками в случае наличия между ними линейной зависимости. На практике применяются различные формулы расчета данного коэффициента. Наиболее удобной является формула: И ИИ пИх1У{ -Их,Цу, <=i i=i i=i 1 и nHxi2 - /=i ( п Л2' п <=1 (« Л2 ^1=1 J где п — объем двумерной выборки; *i> yi — значения двумерной выборки. Можно рассчитать этот коэффициент и по формуле (5.2). Коэффициент корреляции Пирсона используется при исследовании социально-экономических процессов и явлений, распределение которых близко к нормальному. Коэффициент корреляции изменяется в пределах от — 1 до 1, т.е. — 1 < г^< 1. При Гуу = 0 величины Хи У являются независимыми. Аргументы этой функции должны быть числами или именами, массивами или ссылками, содержащими числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки с нулевыми значениями учитываются. Окно данной функции имеет такой вид, как показано ниже (числовые данные из примера 5.3). Если Массив 1 или Массив2 пуст или они содержат различное число точек данных, то функция ПИРСОН возвращает значение ошибки #Н/Д. ! , 87
ПИРСОН. Массив! JB1:G1 MaccMB2]B2:G2l Возвращает коэффициент корреляции Пирсона. Более подробные сведения системе '.-'■.' •',,' - ■ , '•" ■ ■ . ~5Л.~ <96j77;77j89}82j8i; 31» #21,1070;1001,6Ш, '. *=-0, 42 ® Массив2 множество эависимых значений, • Значение:-0,984 сж ] Отмена где Массив1 — первый массив, или интервал данных; Массив2 — второй массив, или интервал данных. Пример 5.3. На основе выборочных данных о деловой активности однотипных коммерческих структур оцените тесноту связи между прибылью Y (млн руб.) и затратами X на 1 рубль производства продукции: X Y 96 221 77 1070 77 1001 89 606 82 779 81 789 Решение. Используя формулу (5.3), получим значение линейного коэффициента корреляции Гху = —0,984. Полученный результат свидетельствует о сильной обратной зависимости между изучаемыми признаками. Решим данный пример с использованием функции ПИРСОН. Алгоритм действий следующий: 1. сформируем таблицу исходньгх данных: " ] _йв! 'i Iх aJr в 96 221 J уж" 1 77 1070 ~0 77 1001 Е Ш 89 606 Т "Ж" ~G 82 779 81 789 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку -" на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ПИРСОН -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ПИРСОН; 88 6. нажав кнопку JjJ в поле Массив 1, перейдем на рабочий лист с исходными данными и выделим его мышью (В1:К1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ПИРСОН; 7. нажав кнопку Зт в поле Массив 1, перейдем на рабочий лист с исходными данными и выделим его мышью (В2:К2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ПИРСОН; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> -0,984. Определение квадрата коэффициента корреляции Пирсона. Функция КВПИРСОН Синтаксис функции: КВПИРСОН (Известные_уг, Известныех) Квадрат коэффициента корреляции г^, носит название коэффициента детерминации и вычисляется по формуле: 4- ( п и и \2 пИх,у.-ЦъЦу, и и2>«2 - (=1 (п VI 2>, ^i=i ) . и "Еу,2 - 1=1 (п \2~] 2>,- U=l ) (5.4) где п — объем двумерной выборки; хи У1 — значения двумерной выборки. Этот коэффициент показывает долю вариации зависимой переменной, учтенной в модели и обусловливаемой вариацией включенных факторов. Аргументы данной функции должны быть числами или именами, массивами или ссылками, содержащими числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки с нулевыми значениями учитываются. Окно данной функции имеет следующий вид (числовые данные из примера 5.4): 89
Известиые_у JB2:G2 is-—— •-,,.• Известныеjc Jbi :G1 .квадрат коэффициента ч» Б ■ " ^] ш -{96;77;77;89;82;ei; | .0, no данным точкам. щ Извесгные_?1 массив или или ссылки на Значение:0,9681 могущий включать числа или именал массивы с числами. ''•'..■'■ ОК Отмена где Известные^ — массив, или интервал точек данных; Известные_х — массив, или интервал точек данных. Если Известные_у и Известные_х пусты или содержат различное число точек данных, то функция КВПИРСОН показывает значение ошибки #Н/Д. Пример 5.4. На основе данных примера 5.3 определите квадрат коэффициента корреляции Пирсона. Решение. Расчеты проведем по формуле (5.4), в результате получим •ху =0,9681 (96,81%). Полученный результат свидетельствует, что около 97% вариации зависимой переменной Y (прибыли) обусловлено вариацией фактора X (затратами на 1 рубль производства продукции). Остальные 3% вариации У обусловлены другими факторами. Решим данный пример с использованием функции КВПИРСОН. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: Щ. 1 2 А X Y О 96 221 77 1070 F и : 77 1001 Til 1— '""таг™™ 89 606 82 779 81 789 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку JfLJ на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию КВПИРСОН -> ОК; 90 5. в левом верхнем углу листа Excel появится окно функции КВПИРСОН; 6. нажав кнопку 2* в поле Известные_у, перейдем на рабочий лист с исходными данными и выделим его мышью (B2:G2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции КВПИРСОН; 7. нажав кнопку 2м в поле Известные_х, перейдем на рабочий лист с исходными данными и вьщелим его мышью (Bl: G1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции КВПИРСОН; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений ->• 0,9681.
6. Интервальное оценивание 6.1. Определение доверительного интервала для среднего. Функция ДОВЕРИТ Синтаксис функции: ДОВЕРИТ {Альфа, Стандартное_откл, Размер) Выборочные параметры являются случайными величинами, их отклонения от генеральных параметров (погрешности) также будут случайными. Оценка этих отклонений носит вероятностный характер: можно лишь указать вероятность той или иной погрешности. Для этого в статистике используется интервальное оценивание (доверительные интервалы и доверительные вероятности). Задачу интервального оценивания можно сформулировать следующим образом: по данным выборки построить числовой интервал, относительно которого с заранее выбранной вероятностью можно сказать, что внутри этого интервала находится оцениваемый параметр. Доверительным интервалом 2е для параметра 0 называется такой интервал, относительно которого можно с заранее выбранной вероятностью р = 1 — а, близкой к единице, утверждать, что он содержит неизвестное значение параметра 0, т.е. Р(|®-©|<Е) = 1-а. (6.1) Чем меньше для выбранной вероятности доверительный интервал 2е, тем точнее оценка неизвестного параметра 0, и наоборот, если этот интервал велик, то оценка, произведенная с его помощью, мало пригодна для практики. При этом интервал практически возможных значений ошибки при замене 0 на 0 будет равен +е ; большие по абсолютной величине ошибки будут появляться только с малой вероятностью а, называемой уровнем значимости. Доверительный интервал для среднего (интервальная оценка математического ожидания) строится следующим образом. 1. По доверительной вероятности р — 1 — а по значению функции Лапласа Ф(^) определим zp. 2. Вычислим величину е: V/г где п — объем выборки; ох — известное стандартное (среднее квадратическое) отклонение. 92 3. Определим величину доверительного интервала для математического ожидания тх: mr e - о, X±Zp-fz V/г. Если какой-либо из аргументов не является числом, то функция ДОВЕРИТ показывает значение ошибки #ЗНАЧ! Окно данной функции имеет следующий вид (числовые данные из примера 6.1): . * -0,05 "V *20 «12 at ■ сведения ■ $ » -=_ ^=„ Альфа 0,05 Стандартное_откя 20 ■ — —~ Размер 12) льный интервал для среднего в справочной системе. Размер размер выборки. Значение:11,3 "•afepr - * ——=«- "к^-т- •'11,31584056 совокупности. Более по СЖ I Отмена где Альфа — уровень значимости, используемый для вычисления уровня надежности. Уровень надежности равняется 100 • (1 — Альфа) процентам. Например, Альфа, равное 0,05, определяет 95%-ный уровень надежности; Стандартное_откл — стандартное отклонение генеральной совокупности для интервала данных, предполагается известным; Размер — размер выборки. Если Альфа <= 0 или Альфа => 1, то функция ДОВЕРИТ показывает значение ошибки #ЧИСЛО! Если Ставдартное_откл <= 0, то функция ДОВЕРИТ показывает значение ошибки #ЧИСЛО! Если Размер не целое, то оно усекается. Если Размер меньше 1, то функция ДОВЕРИТ показывает значение ошибки #ЧИСЛО! Пример 6.1. Для отрасли, включающей 500 фирм, проведена случайная выборка из 12 фирм. По этим фирмам определена численность работающих: 325; 415; 381; 510; 435; 366; 515; 465; 458; 386; 358; 410. ~ Заранее известно, что среднее квадратическое (стандартное) отклонение ах = 20. 93
Постройте доверительный интервал для данной выборки при уровне значимости а = 0,05 и а = 0,1. Решение. 1. Определим среднюю численность работающих: х=-Ух{ =419. и,=1 2. По таблице функции Лапласа определим zo 95 = 1,96; zog = = 1,645. 3. Вычислим величину е: 20 при/> = 0,95 е = —=1,96 = 11,3 ; V12 20 при/> = 0,9 е =-т=-1,645 = 9,5. V12 4. Определяем величину доверительного интервала: при р = 0,95 тх е [419 +11,3]; прир = 0,9 тх е [419+9,5]. Решим данный пример с использованием функции ДОВЕРИТ. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$3); на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию ДОВЕРИТ -> ОК; 4. в левом верхнем углу листа Excel появится окно функции ДОВЕРИТ; 5. в поле Альфа введем уровень значимости а = 0,05; 6. В поле Ставдартное_откл введем величину среднего квад- ратического (стандартного) отклонения ах = 20; 7. в поле Размер введем объем выборки п — 12; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$3 появится результат вычислений -> 11,3. Примечание. В результате использования данной функции мы получили значение величины половины доверительного интервала для среднего (е). Полученный нами результат соответствует только доверительной вероятности, равной 0,05. Если необходимо получить е для доверительной вероятности, равной 0,10, необходимо в поле Альфа ввести значение 0,10. Результат вычислений будет равен 9,5. 94 6.2. Определение вероятности попадания дискретной случайной величины в интервал. Функция ВЕРОЯТНОСТЬ Синтаксис функции: ВЕРОЯТНОСТЬ {Х_диапазон, Диапазон_веро- ятн, Нижнийпредел, Верхнийпред) Функция определяет вероятность того, что значение выборки находится внутри заданного интервала. Выборка должна быть задана дискретным вариационным рядом в виде: Варианты х{ Вероятность pt Ч Р\ х2 Pi *3 Рг хп Рп При расчете вероятности задаются границы интервала числовых значений х,- — Xj (i *j). Для тех значений хь которые попали в интервал, подсчитываются вероятности, соответствующие этим значениям. Если верхняя граница интервала не задана, то определяется вероятность значения х/. Для заданного ва- п риационного ряда необходимо выполнение условия ^р, = 1. i=i Окно данной функции имеет следующий вид (числовые данные из примера 6.2): ВЕРОЯТНОСТЬ-, Х_Диапазон )В1 :К1 Диапэзон_вероятн |В2:К2 Нижнии_предея 18 Верхний_пред|46 31 - {3;7;i0;20;2i;32;4< 31 «ft • ,130,1 шт мттш ., «46,- -0,6 Зозвращает вероятность того, что значения диапазона находятся, внутри заданных пределов. необязательная верхняя граница значения, для которого требуется вычислить Если опущена, вероятность того, чтозначения диапазона X равны нижнему пределу. О Значение;Сб ОК | Отмена где Х_диапазон — интервал числовых значений х, с которыми связаны вероятности; ^ Диапазон_вероятн — множество вероятностей, соответствующих значениям в аргументе Х_ диапазон; Нижнийпредел — нижняя граница значения, для которого вычисляется вероятность; * 95
Верхнийпред — необязательная верхняя граница значения, для которого требуется вычислить вероятность. Если любое значение в аргументе Диапазонвероятн <= 0 или если какое-либо значение в аргументе Диапазон_вероятн > 1, то функция ВЕРОЯТНОСТЬ показывает значение ошибки #ЧИСЛО! Если сумма значений в аргументе Диапазонвероятн не равна 1, то функция ВЕРОЯТНОСТЬ показывает значение ошибки #ЧИСЛО! Если Верхний_пред опущен, то функция ВЕРОЯТНОСТЬ показывает вероятность равенства значению аргумента Ниж- ний_предел. Если Х_диапазон и Диапазонвероятн содержат различное количество точек данных, то функция ВЕРОЯТНОСТЬ показывает значение ошибки #Н/Д. Пример 6.2. Задан дискретный вариационный ряд X, Pi 3 0,08 7 0,09 10 0,12 20 0,11 21 0,10 32 0,12 44 0,08 45 0,07 47 0,13 50 0,10 Определите вероятность попадания случайной величины в интервал (8—46), а также вероятности того, что величина X примет значения X/ = 10 и х,- = 15. Решение. 1. Д8 < х < 46) = 0,12 + 0,11 + 0,10 + 0,12 + 0,08 + 0,07 = 0,6; 2. Д10) = 0,12; 3. Д15) = 0. Решим данный пример с использованием функции ВЕРОЯТНОСТЬ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: 1 А ь% шш 1 ШМ I3 |0,08 шшшт 7 0,09 тШШтт 10 0,12 20 0,11 21 0,1 32 0,12 на tLii 44 0,08 45 0,07 47 0,13 50 ОД 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку *"*' на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ВЕРОЯТНОСТЬ -> ОК; 96 5. в левом верхнем углу листа Excel появится окно функции ВЕРОЯТНОСТЬ; 6. нажав кнопку -J в поле Х_лиапазон, перейдем на рабочий лист с исходными данными и выделим его мышью (Bl: K1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ВЕРОЯТНОСТЬ; 7. нажав кнопку -*1 в поле Диапазонвероятн, перейдем на рабочий лист с исходными данными и выделим его мышью (В2: К2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ВЕРОЯТНОСТЬ; 8. в поле Нижний_предел введем значение 8; 9. в поле Верхнийпред введем значение 46; 10. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,6. Примечание. Полученный нами результат ет вероятности попадания в интервал (8—46). Если необходимо получить вероятность принятия конкретных значений 10 и 15, необходимо в поле Нижнийпредел ввести значения 10 и 15ctt s- :-нно (поле Верхний_пред оставляем пустым). Результаты вычислений будут равны 0,12 и 0 о. 4 Статистические функции в экономике--
7. Определение параметров распределений непрерывных случайных величин 7.1. Определение значения функции распределения и функции плотности нормального распределения. Функция НОРМРАСП Синтаксис функции: НОРМРАСП (X, Среднее, Стандарт- ноеоткл, Интегральный) Нормальным называется распределение непрерывной случайной величины X, плотность распределения которой имеет вид: (х-тх)2 \2пах а функция распределения (интегральная функция) (x-mxf 1 Fix): :_L-f л/2тгог _« е 2G- dx, (7.1) (7.2) где тх — математическое ожидание случайной величины X; ах — стандартное (среднее квадратическое) отклонение случайной величины X. Графики функции плотности и функции распределения имеют следующий вид (рис. 7.1 и 7.2). 0 1 тх х Рис. 7.1. График функции плотности нормального распределения 98 F(x), 1 _ 0,5 - 0 А 1 - F(x) y'S F(x) ЛГ \ тх >- X Рис. 7.2. График функции распределения нормального распределения (интегральная функция) Среди непрерывных случайных величин нормальное распределение занимает центральное место. С ним приходится встречаться при анализе погрешностей измерений, контроле технологических процессов и режимов, при анализе и прогнозировании различных явлений в экономике, биологии, медицине и других областях знаний. Нормальный закон проявляется во всех случаях, когда случайная величина X является результатом действия большого числа факторов, причем каждый фактор в отдельности на случайную величину X влияет незначительно и не преобладает по своему влиянию над остальными. Основная особенность, выделяющая нормальный закон среди других законов, — то, что он является предельным законом, к которому, при определенных условиях, приближаются другие законы распределения. На практике нормальный закон распределения используется при определении доверительных интервалов и доверительных вероятностей, проверке статистических гипотез и других методах статистического анализа. Практически во всех этих случаях необходимо определять значение функции плотности или функции распределения в заданной точке. Статистические оценки параметров распределения по выборке объемом п определяются по формулам: 4* 99
m Окно данной функции имеет следующий вид (числовые данные из примера 7.1): .НОРМРАСП- - - -— ■ ;"" " ". ■' ' - " ] х$Ь5 53-24*5 • Среднее J20 ГУ"*20 ■ I MTWiimm ^lmifmnlTftliffiifri miiffTin п мчпши ШЙМИОТГОти i. Стандартное_откл )з ^У"*3 Интегральный ]ложь| _ %i - ЛОЖЬ , . ' ' ' - 0,043172532 ает нормальную функцию распре- Интегральный логическое значение, определяющее вид функции- интегральная функция распреде пения (ИСТИНА) или весовая функция распределения (ЛОЖЬ), ОР Значение: 0,043 . СЖ | Отмена | где X — значение, для которого строится распределение; Среднее — математическое ожидание распределения; Стандартное_откл — стандартное отклонение распределения; Инге i , . — логическое значение, определяющее форму функции. Если Интегральный имеет значение ИСТИНА, то функция НОРМРАСП определяет интегральную функцию распределения; если этот аргумент имеет значение ЛОЖЬ, то определяется функция плотности распределения. Если Среднее или Стандартное_откл не являются числом, то функция НОРМРАСП показывает значение ошибки #ЗНАЧ! Если Стандартное_откл <= 0, то функция НОРМРАСП показывает значение ошибки #ЧИСЛО! Если Среднее равно 0 и Стан- дартное_откл равно 1, то функция НОРМРАСП возвращает стандартное нормальное распределение, то есть НОРМСТРАСП. Пример 7.1. Случайная величина X имеет нормальное распределение с параметрами тх = 20, ах = 3. Определите значение функции плотности и функции распределения в точке х = 24,5. Решение. Значение функции плотности определим по формуле (7.1): /(*) = —L_e 2o* =0,043. л12псх 100 Значение функции распределения определим по формуле (7.2): (x-ffQ2 F(x) = -=L- ]e 2о* dx = 0,933. л12псх _«, Решим данный пример с использованием функции НОРМРАСП. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку fi на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию НОРМРАСП -> ОК; 4. в левом верхнем углу листа Excel появится окно функции НОРМРАСП; 5. в поле X введем значение х = 24,5; 6. в поле Среднее введем значение тх - 20; 7. в поле Стандартное_откл введем значение ох = 3; 8. в поле Интегральный введем логическое значение ЛОЖЬ; 9. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,043 (значение функции плотности). Примечание. Если в поле Интегральный ввести логическое значение ИСТИНА, то функция НОРМРАСП вычислит значение функции распределения — 0,933. 7.2. Определение аргумента по значению функции распределения. Функция НОРМОБР Синтаксис функции: НОРМОБР (X, Среднее, Стандартное_откл, Интегральный) При использовании данной функции решается обратная задача: известно, что случайная величина X имеет нормальное распределение, определяемое законом распределения (7.2); известны параметры распределения тх и gx и значение функции распределения в заданной точке х. Необходимо по известным параметрам определить точку х, в которой функция распределения принимает заданное значение. 101
Окно данной функции имеет следующий вид (числовые данные из примера 7.2): НОРМОБР-- : ..■ . ъ|с,933 ]у« 0/933 • ,',' - Среднее|го ' ' Щ.-20 , ' ■ .' ,-;■■"..■ , ' ' *—>—"■'——'■'--■""—— ——=-i , Стандартное_откл|з 2У'~ 3' ■' ' ■ '- • •''•,''. .'• : '" ' ,' , '.'«24,49554591 Возвращает обратное нормальное распределение, . ' ' ,> , , ' Стандартное^отки стандартное отклонение распределения, положительное число. (5, Значение: 24Д ОК I >. Отмена м,шт!жшштттттшт*тг№мтш&н1т<ж где Вероятность — вероятность, соответствующая нормальному распределению; Среднее — математическое ожидание распределения; Стандартное_откл — стандартное отклонение распределения. Если какой-либо из аргументов не является числом, то функция НОРМОБР показывает значение ошибки #ЗНАЧ! Если Вероятность меньше 0 или Вероятность больше 1, то функция НОРМОБР показывает значение ошибки #ЧИСЛО! Если Стан- дартное_откл <= 0, то функция НОРМОБР показывает значение ошибки #ЧИСЛО! НОРМОБР использует стандартное нормальное распределение, если Среднее равно 0 и Стандартное_откл равно 1 (см. НОРМСТОБР). НОРМОБР использует метод итераций для вычисления функции. Если задано значение вероятности, то функция НОРМОБР производит итерации, пока не получит результат с точностью ± 3 • Ю-7. Если НОРМОБР не сходится после 100 итераций, то функция показывает значение ошибки #Н/Д. Пример 7.2. Случайная величина X имеет нормальное распределение с параметрами тх = 20, ах = 3. В некоторой точке х функция распределения 1{х) = 0,933. Определите значение этой точки. Решение. Определим значение х из уравнения: 0,933= J- ]е 2о* dx. V2kov __. 102 Решение данного уравнения дает результат х = 24,5 (табл. П.1). Решим данный пример с использованием функции НОРМОБР. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию НОРМОБР -> ОК; 4. в левом верхнем углу листа Excel появится окно функции НОРМОБР; 5. в поле Вероятность введем значение 0,933; 6. в поле Среднее введем значение тх = 20; 7. в поле Стандартное_откл введем значение gx = 3; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 24,5. 7.3. Определение нормализованного значения аргумента. Функция НОРМАЛИЗАЦИЯ Синтаксис функции: НОРМАЛИЗАЦИЯ (X, Среднее, Стандарт- ноеоткл) Для того чтобы привести нормальное распределение к так называемому стандартному виду с шх = 0 и о = 1, для зависимости (7.1) и (7.2) вводится замена переменной: z= х-. (7.3) С использованием этого преобразования рассматриваемые формулы примут вид: 1 ± /(z) = -=e 2, (7.4) л/2я 1 z -^ F(z) = -7=Je 2dz. (7.5) Окно данной функции имеет следующий вид (числовые данные из примера 7.3): 103
, НОРМАЛИЭмцетЯ - - Среднее (го ' 3 =20 ' -**-*-*-■ !■" '. ,et. .. . TKn|i 3=3 •/■ ■-■■'• "' '"'""' ''■""-: •' , ' '•':' ■'..•'■. ■ ■■' ' "■ ' =1,5 . " -; '. '■• Возвращает нормализованное значение, ' • i ■ , Стандартное_откя стандартное отклонение распределения, положительное число,• Щ . - 'Значение;1(5 | ОК \ Отмена где X — нормализуемое значение; Среднее — среднее арифметическое распределения; Стандартное_откл — стандартное отклонение распределения. Если Стандартное_откл <= 0, то функция НОРМАЛИЗАЦИЯ показывает значение ошибки #ЧИСЛО! Пример 7.3. Определите нормализованное значение аргумента нормального распределения при тх = 20, ох = 3, х = 24,5. Решение. Нормализованное значение аргумента определим по формуле (7.3): х-тх 24,5-20 .. z = = = 1,5. сх 3 Решим данный пример с использованием функции НОРМАЛИЗАЦИЯ. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию НОРМАЛИЗАЦИЯ -> ОК; 4. в левом верхнем углу листа Excel появится окно функции НОРМАЛИЗАЦИЯ; 5. в поле X введем значение 24,5; 6. в поле Среднее введем значение тх — 20; 7. в поле Стандартное_откл введем значение ах = 3; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 1,5. 104 7.4. Определение значения функции распределения стандартного нормального распределения. Функция НОРМСТРАСП Синтаксис функции: НОРМСТРАСП (2) Функция распределения стандартного нормального распределения (тх = 0, ох = 1) такая: 1 г -^ F(z) = -= fe 2dz. (7.6) V2rc-oo При использовании данной функции необходимо определить значение функции распределения в точке z- Окно данной функции имеет следующий вид (числовые данные из примера 7.4): • НОРМСТРАСП- | -;'. ,-— — --- . - т ^ -•. ■_-." ' .' - .-zJTis ' 3=1.5' ' '■■■ ' \ ' ■■ —0,933192771' _-i Возвращает стандартное нормальное интегральное распределение. - , ■ , 2значенйе, длякоторогостроитсяраспределение,. ', . ,'• Щ Значение: 0,933 '. ' <Ж 1 Отмена тш ътжшятт м>*ъ& где Z — значение, для которого строится распределение. Если Z не является числом, то функция НОРМСТРАСП показывает значение ошибки #ЗНАЧ! Пример 7.4. Определите значение функции распределения стандартного нормального распределения в точке z= 1,5. Решение. Значение функции распределения определим по формуле (7.6): F(z) = -= fe 2<fe = 0,933 (табл. П. 1). Решим данный пример с использованием функции НОРМСТРАСП. Алгоритм действий следующий: 105
1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку * на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию НОРМСТРАСП -> ОК; 4. в левом верхнем углу листа Excel появится окно функции НОРМСТРАСП; 5. в поле Z введем значение 1,5; 6. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,933. 7.5. Определение аргумента по значению стандартной интегральной функции нормального распределения. Функция НОРМСТОБР Синтаксис функции: НОРМСТОБР {Вероятность) При использовании данной функции решается обратная задача: известно, что случайная величина X имеет нормальное распределение с параметрами тх = 0, сх — 1, которое описывается функцией распределения (7.6). Известно значение функции (7.6) в заданной точке z- Необходимо по значению функции F(z) определить значение аргумента Z- Окно данной функции имеет следующий вид (числовые данные из примера 7.5): НОРМСТОБР • Вероятность |о,933| ~" 3J = 0,933 ' 1,198515303 т обратное значение стандартного нормального распределения. Вероятность вероятность, соответствующая нормальному распределению. ..W е • Значение; 1,5 ОК { Отмена I где Вероятность — вероятность, соответствующая нормальному распределению. Если Вероятность не является числом, то функция НОРМСТОБР показывает значение ошибки #ЗНАЧ! 106 Если Вероятность меньше 0 или Вероятность больше 1, то функция НОРМОБР показывает значение ошибки #ЧИСЛО! НОРМСТОБР использует метод итераций для вычисления функции. Если задано значение вероятности, то функция НОРМСТОБР производит итерации, пока не получит результат с точностью ± 3 • 10~7. Если НОРМСТОБР не сходится после 100 итераций, то функция показывает значение ошибки #Н/Д. Пример 7.5. Определите значение z, в котором стандартное нормальное распределение принимает значение Дг)=0,933. Решение. Значение z определим из уравнения: 1 2 -^ 0,933 = -= fe 2dz. Решение данного уравнения дает результат z — 1,5 (табл. П.1). Решим данный пример с использованием функции НОРМСТОБР. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку ■■&* на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию НОРМСТОБР -> ОК; 4. в левом верхнем углу листа Excel появится окно функции НОРМСТОБР; 5. в поле Вероятность введем значение 0,933; 6. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 1,5. 7.6. Определение вероятности статистики Z при проверке гипотезы о равенстве статистической оценки математического ожидания заданному значению. Функция ZTECT Синтаксис функции: ZTECT {Массив, X, Сигма) Имеетея нормальная генеральная совокупность, из которой извлечена выборка объема п, по ней найдена статистическая 107
оценка математического ожидания (выборочное среднее) х, причем генеральная дисперсия о\ известна. Требуется по выборочной средней при заданном уровне значимости а проверить нулевую гипотезу Д>: ЩХ\ = xq о равенстве оценки математического ожидания заданному значению. При проверке статистической гипотезы рассчитывается опытное значение критерия: *в=^-, (7-7) ^£. Л/И 1 " = -£*,■ <?.8) где п~ Критическая область строится в зависимости от вида конкурирующей гипотезы, при этом рассматривают три случая. 1. Нулевая гипотеза Щ : ЩХ\ = Xq. Конкурирующая гипотеза Н\ : ЩХ\ # .% В этом случае строят двустороннюю критическую область с уровнем значимости 2а. 2. Нулевая гипотеза Щ : М[Х] = xq. Конкурирующая гипотеза Н\ : М[Х] > xq. В этом случае строят правостороннюю критическую область с уровнем значимости а. 3. Нулевая гипотеза Щ : ЩХ\ = xq. Конкурирующая гипотеза Щ : М[Х] < xq. В этом случае строят левостороннюю критическую область с уровнем значимости а. Если вычисленный уровень значимости больше заданного, то нет оснований отвергнуть нулевую гипотезу, в противном случае гипотеза отвергается. Окно данной функции имеет следующий вид (числовые данные из примера 7.6): —— - ■ || . МИССИ» B2iK2 _^ц_д1ц_цд1; jjfl»0,C8,l,H,t,a5iU | , _ , CwwJciS ИуМ* I ~й «7048329, , бсдврашют двустороннее р-знэчениег-теетв Сигме, известное етвнлартное отклонение ^енейвльной совокупности, 108 где Массив — массив или интервал данных, с которыми сравнивается X; X — проверяемое значение; Сигма — известное стандартное отклонение генеральной совокупности. Если этот параметр опущен, то используется стандартное отклонение выборки. Если Массив пуст, то функция ZTECT показывает значение ошибки #Н/Д. Пример 7,6. Станок-автомат изготавливает детали с номинальным размером д^ = 1,3 мм. Точность станка ох =0,15 мм. Для контроля точности станка взята выборка из десяти деталей, измерения размеров которых дали следующие результаты. Деталь Размер, X/, ММ 1 1,08 2 1,14 3 1,25 4 1,10 5 1,36 6 1,42 7 1,40 8 1,38 9 1,15 10 1,12 Требуется при уровне значимости а = 0,05 проверить нулевую гипотезу Щ : М[Х] = щ при конкурирующей гипотезе Нх : ЩХ\ * xq. Решение. Вычислим статистическую оценку математического ожидания: 1 " ? = i£^=l,24 мм i=i Определим расчетное значение критерия: Za =■ х-х0 _ 1,24-1,3 _ ~ 0,15 = -1,265, л/й" л/Й) По найденному значению критерия определим двустороннюю доверительную вероятность (табл. П.1): P(|zB|< 1,265) = 0,897; 1-Р = 0,103. Так как вычисленное значение 1 — Р = 0,103 больше заданного а = 0,05, то нулевая гипотеза не отвергается. Решим данный пример с использованием функции ZTECT. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: 1.36 ЛМ- 1.4 1.38 1 1.13 10 109
2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); fl 3. вызовем Мастер функций, нажав кнопку щ_1 на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ZTECT -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ZTECT; 6. нажав кнопку Л1 в поле Массив, перейдем на рабочий лист с исходными данными и вьщелим его мышью (В2: К2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ZTECT; 7. в поле X введем значение xq = 1,3; 8. в поле Сигма введем значение стх = 0,15; 9. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,897 (P(\zB\< 1,265)). 7.7. Определение значения функции распределения логнормального распределения. Функция ЛОГНОРМРАСП Синтаксис функции: ЛОГНОРМРАСП (X, Среднее, Стандарт- ноеоткл) Логарифмически нормальным называется распределение случайной величины X, при котором ее логарифм У= In X распределен нормально с функцией плотности: f[y) = -pU-e 2°' , (7.9) л/2яо>, где y = lnx;my = M[Y\; су = у/ЩУ]. Случайная величина Xимеет функцию плотности (Inx-nty)1 /(*)= Д е 2°' , (7.10) х\2пс. 'У ПО -еу+ту 2 02+2т о1 . щетх=е2 ;сх=еу у (е у -1) . График функции плотности имеет вид (рис. 7.3). 0,8 Рис. 7.3. График функции плотности логнормального распределения Логарифмически нормальное распределение используется в теории надежности. Статистические оценки параметров распределения определяются по формулам: 1^ т. = y = ~Ydtoxl ; «=i П- —гЕ\Ых'—Ё1п*' • и-1^ и1=1 ) (7.11) (7.12) Функция распределения логнормального распределения имеет вид F{x) = - 1 U (lnjc—m ) 2с: у dx. 4Ъкоу 0х График функции распределения изображен на рис. 7.4. (7.13) 111
Рис. 7.4. График функции распределения логнормального распределения Окно данной функции имеет следующий вид (числовые данные из примера 7.7): ■■ ЛОГНОРМРАСП Х|7,9 Среднее 2,30 Стандартное_откл |о,20 31=2>' распределение, Стандартное_отклст ''=0,121870727' где tn(x) представляет собой нормальное отклонение 1п(х), положительное число. © Значение; 0,121870727 <Ж Отмена где X — значение xh для которого вычисляется функция; Среднее — среднее, вычисленное по значениям In jc,-; Стандартное_откл — стандартное отклонение, вычисленное по значениям In jc,-. Если какой-либо из аргументов не является числом, то функция ЛОГНОРМРАСП показывает значение ошибки #ЗНАЧ! Если X <= 0 или Ставдартное_откл <= 0, то функция ЛОГНОРМРАСП показывает значение ошибки #ЧИСЛО! Пример 7.7. Имеется выборка объемом п = 10 случайных величин xj. 10,8; 8,1; 7,9; 10,7; 13,2; 7,9; 11,4; 10,9; 8,1; 12,4. Определите значение функции распределения в точке х — 7,9. Решение. Определим величины у, = In xf. 2,38, 2,09; 2,06; 2,37; 2,58; 2,06; 2,44; 2,39; 2,09; 2,52. 112 Определим статистические оценки у и оу: У = -£у, =2,30; °У=1—£(.У1-У) =0.20. Значение функции распределения определим по формуле (7.13): (In*-?)2 1^1 2 F(x) = -=—1-« 2Су dx = 0,122. л!2псу о х Решим данный пример с использованием функции ЛОГНОРМРАСП. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку fx на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию ЛОГНОРМРАСП ->• ОК; 4. в левом верхнем углу листа Excel появится окно функции ЛОГНОРМРАСП; 5. в поле X введем значение х = 7,9; 6. в поле Среднее введем значение у =2,30; 7. в поле Стандартное_откл введем значение су = 0,20 ; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,122. 7.8. Определение аргумента по значению функции распределения. Функция ЛОГНОРМОБР Синтаксис функции: ЛОГНОРМОБР (Вероятность, Среднее, Стандартное откл) При использовании данной функции решается обратная задача: известно, что случайная величина X имеет логнормальное распределение, определяемое законом распределения (7.13); известны параметры распределения тх, ох и значение функции ИЗ
распределения в точке х. Необходимо по известным параметрам определить точку х, в которой функция распределения принимает заданное значение. Окно данной функции имеет следующий вид (числовые данные из примера 7.8): I ., Вероятность jo, 122 ,jy« 0,122 j I :/'- Среднее|^30 ' ' '"'"' 53=гА.' •• \ | . Стандартное_рткл ]о,20| 5 = 0,2 '■ ','.-...• ' •■ ■ •' - =7,901010326' • обратное логарифмическое нормальное распределение, где !п(х) представляет собой ■нормальное распределение, " "* ,'. ■._'._•' . -'. • ' Стандартное_откл стандартное отклонение h(x), положительное число. О . • Значение; 7,901010326 <Ж I . Отмена где Вероятность — вероятность, связанная с логарифмически- нормальным распределением; Среднее — среднее, вычисленное по значениям In х{, Стандартное_откл — стандартное отклонение, вычисленное по значениям In х,. Если любой из аргументов не является числом, то функция ЛОГНОРМОБР показывает значение ошибки #ЗНАЧ! Если Вероятность меньше 0 или Вероягаость больше 1, то функция ЛОГНОРМОБР показывает значение ошибки #ЧИСЛО! Если Стан- дартное_откл <= 0, то функция ЛОГНОРМОБР показывает значение ошибки #ЧИСЛО! Пример 7.8. Случайная величина X имеет логнормальное распределение с параметрами ту — 2,30, ау = 0,20. В некоторой точке х функция распределения F(x) = 0,122. Определить значение этой точки. Решение. Значение х определяем из решения уравнения: 0,122 = —L— | Де 2°' dx при ту = 2,30, ау = 0,20. л12поу о х Решение данного уравнения дает результат х = 7,9. Решение получаем путем итераций (подбором х), пока не получим искомый результат с заданной точностью. 114 Решим данный пример с использованием функции ЛОГНОРМОБР. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); , в 2. вызовем Мастер функций, нажав кнопку '* на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию ЛОГНОРМОБР -> ОК; 4. в левом верхнем углу листа Excel появится окно функции ЛОГНОРМОБР; 5. в поле Вероятность введем значение 0,122; 6. в поле Среднее введем значение ту = 2,30; 7. в поле Стандартное_откл введем значение оу = 0,20; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 7,9. 7.9. Определение значения функции распределения Стьюдента (интегральной функции). Функция СТЬЮДРАСП Синтаксис функции: СТЬЮДРАСП(X, Степенисвободы, Хвосты) Распределению Стьюдента подчиняется случайная величина: т = 4п X Лп где х = -|>, ; S = -У\(х1 ~х)2 I Xj — независимые нормальные случайные величины с М [X; ] = пгх и Функция плотности распределения Стьюдента имеет вид: /ю=- П) г1^1г ^-f л&| ш k (7.14) где к = п — 1 — число степеней свободы; п — число наблюдений (опытов); 115
r(s) = J e xxs ldx — гамма-функция (Эйлеров интеграл второго рода). Частные значения гамма-функции: Г[ - ] = л/л ; Г(1) = 1; Г(и+1) = иГ(и) = и! (и - целое число). График функции плотности распределения Стьюдента имеет вид (рис. 7.5). Рис. 7.5. График функции плотности распределения Стьюдента Интегральная функция распределения Стьюдента определяется зависимостью: F(t) = - л/Атсг| 1+- к+1 dt. (7.15) Числовые характеристики распределения Стьюдента: математическое ожидание ЩТ\ = 0; у дисперсия ЦТ] = (к > 2). к-2 Распределение Стьюдента не зависит от математического ожидания и дисперсии случайной : • • X, а зависит лишь от объема выборки п. ° • плотности (7.15) часто называют законом распределения статистики t или ^-распределением Стьюдента. Данное распределение в математической статистике используется для построения доверительных интервалов и проверки 116 статистических гипотез при использовании малых выборок. При больших значениях к = п — 1 распределение Стьюдента асимптотически приближается к стандартному нормальному распределению. С помощью распределения Стьюдента, определяемого функцией распределения (7.15), можно найти значение уровня значимости а при односторонней доверительной вероятности р = 1 — а, числе степеней свободы к = п — 1 по значению величины t; значение уровня значимости 2а при двусторонней доверительной вероятности р = 1 — 2а. Окно данной функции имеет следующий вид (числовые данные из примера 7.9): СТЬЮДРАСП | ■ - ■ •-• -■" Xl2,262 3-2,262 ' * £телени_своб0ДЫ |9 Хвосты h| 31 = 9 "3-» Возвращает t-раслределение Стьюдента, . 0,025006423 Хвосты число хвостов (1 или 2). | Щ Значение;Р;025 ОК Отмена где X — численное значение, для которого требуется вычислить уровень значимости; Степени_свободы — число степеней свободы; Хвосты — число определяемых хвостов распределения. Если Хвосты равно 1, то функция СТЬЮДРАСП определяет одностороннюю доверительную вероятность. Если Хвосты равно 2, то функция СТЬЮДРАСП определяет двустороннюю доверительную вероятность. Если какой-либо из аргументов не является числом, то функция СТЬЮДРАСП показывает значение ошибки #ЗНАЧ! Если Степени_свободы меньше 1, то функция СТЬЮДРАСП показывает значение ошибки #ЧИСЛО! Аргументы Степе- ни_свободы и Хвосты усекаются до целых. Если Хвосты — любое значение, отличное от 1 и 2, то функция СТЬЮДРАСП показывает значение ошибки #ЧИСЛО! СТЬЮДРАСП вычисляется следующим образом: СТЬЮДРАСП = р(х < X), где X— случайная величина, соответствующая /-распределению. 117
Пример 7.9. Случайная величина Т имеет распределение Стьюдента с числом степеней свободы к =9. Для значения t = 2,262 определите уровень значимости при односторонней и двусторонней доверительной вероятности. Решение. По формуле функции распределения (7.15) определим: • для односторонней доверительной вероятности а = 0,025; • для двусторонней доверительной вероятности 2а = 0,05 (табл. П.З). Решим данный пример с использованием функции СТЬЮД- РАСП. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку •£*! на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию СТЬЮДРАСП -> ОК; 4. в левом верхнем углу листа Excel появится окно функции СТЬЮДРАСП; 5. в поле X введем значение t = 2,262; 6. в поле Степени_свободы введем значение к = 9; 7. в поле Хвосты введем значение 1; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,025 (уровень значимости для односторонней доверительной вероятности). Примечание. Если в поле Хвосты ввести значение 2, то получим уровень значимости для двусторонней доверительной вероятности -> 0,05. 7.10. Определение параметра t по значению функции распределения. Функция СТЬЮДРАСПОБР Синтаксис функции: СТЬЮДРАСПОБР (Вероятность, Степе- нисвободы) При использовании данной функции решается обратная задача: известно, что случайная величина Т имеет распределение Стьюдента, определяемое функцией распределения (7.15); известны параметры распределения Стьюдента — число степеней 118 свободы к = п -1 и двусторонняя доверительная вероятность р = 1 - 2а. Необходимо по числу степеней свободы к и вероятности 2а определить параметр t, при котором функция распределения принимает заданное значение. Окно данной функции имеет следующий вид (числовые данные из примера 7.10): Вероятность 10,05 *У = 0,05' ' '■■ ■>■ «*■ - - ■ . Г~ Степенисвободы |у| _^ *У = 9 ■' '. • ' • ' '. • ' •','.■' = 2,262158887' .Возвращает распределение Стьюдента, ... '.■.'■" • "■ - ' Степени_сво6оды положительное целое число степеней свободы, характеризующее • , . - ..распределение. „'-■.' - . ' ... Значение:2;262 ОК Отмена iF^-J»^ »^S#^p ,^рГЧ^ где Вероятность — вероятность, соответствующая двустороннему распределению Стьюдента; '! Степени_свободы — число степеней свободы, характеризующее распределение. Если любой из аргументов не является числом, то функция СТЬЮДРАСПОБР показывает значение ошибки #ЗНАЧ! Если Вероятность меньше 0 или Вероятность больше 1, то функция СТЬЮДРАСПОБР показывает значение ошибки #ЧИСЛО! Если Степени_свободы не целое, то оно усекается. Если Степенисвободы меньше 1, то функция СТЬЮДРАСПОБР показывает значение ошибки #ЧИСЛО! СТЬЮДРАСПОБР вычисляется следующим образом: СТЬЮДРАСПОБР=/> (t < X), где X— случайная величина, соответствующая ^-распределению. СТЬЮДРАСПОБР использует метод итераций для вычисления функции. Если задано значение вероятности, то функция СТЬЮДРАСПОБР производит итерации, пока не получит результат с точностью ± 3 • Ю-7. Если СТЬЮДРАСПОБР не сходится после 100 итераций, то функция показывает значение ошибки #Н/Д. Пример 7.10. Случайная величина Т имеет распределение Стьюдента с числом степеней свободы к = 9. В некоторой точке t функция распределения ДО = 0,975, 2а = 0,05 (F[t) = 0,95, 2а = 0,10). Определите значение этой точки. 119
Решение. Значение параметра t определим из равенства: Jk + Л ш при 2а = 0,05 F(t) = 0,975, t = 2,262; при 2а = 0,10 F(t) = 0,95, t = 1,833. Решим данный пример с использованием функции СТЬЮД- РАСПОБР. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку -^2' на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию СТЬЮДРАСПОБР -> ОК; 4. в левом верхнем углу листа Excel появится окно функции СТЬЮДРАСПОБР; 5. в поле Вероятность введем значение 2а = 0,05; 6. в поле Степени_свободы введем значение к = 9; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -» 2,262. Примечание. Если в поле Вероятность ввести значение 2а = 0,10, то получим значение параметра t - 1,833. 7.11. Определение вероятности, соответствующей критерию Стьюдента. Функция ТТЕСТ Синтаксис функции: ТТЕСТ (Maccuel, Массив2, Хвосты, Тип) Окно данной функции имеет такой вид, как показано ниже (числовые данные из примера 7.11). Если Массив! и Массив2 имеют различное число точек данных, а Тип равен 1 (парный), то функция ТТЕСТ показывает значение ошибки #Н/Д. Аргументы Хвосты и Тип усекаются до целых. 120 , ПЕСТ' Массив1 |В1:К1 Массива ]в2тК2 ХВОСТЫ |2 Twijl ' ■•. <j-{76,l;76,2;76;76,CK чу.= р6,2;76;76,25,?6,С :У=г - ■ . 2У = 1.' ', : • ' ш 0,68442036 • , возвращает вероятность, соответствующую t-тесту.Стьюдента.', -- . . ; „ ' ТипвидНе51:парный=.1/двухпарный = 2;.двухпарньйснеравным ■ ■ ' , ' . отклонением = 3,- -,,,.•■ ■ '.• Значение:0,684 ОК I Отмена где Массив! — первое множество данных; Массив2 — второе множество данных; Хвосты — число хвостов распределения. Если Хвосты равно 1, то функция ТТЕСТ определяет уровень значимости для односторонней доверительной вероятности. Если Хвосты равно 2, то функция ТТЕСТ определяет уровень значимости для двусторонней доверительной вероятности; *d' Тип — вид исполняемого f-теста. Тип 1 2 3 Выполняемый тест Парный Двухвыборочный с равными дисперсиями (гомоскедастический) Двухвыборочный с неравными дисперсиями (гетероскеда- стический) Если Хвосты или Тип не являются числом, то функция | ТТЕСТ показывает значение ошибки #ЗНАЧ! Если Хвосты имеет значение, отличное от 1 и 2, то функция ТТЕСТ показывает значение ошибки #ЧИСЛО! Рассмотрим подробно каждый их выполняемых тестов. Парный двухвыборочный f-тест для средних I Парный двухвыборочный f-тест Стьюдента используется для проверки гипотезы о равенстве средних для двух зависимых вы- | борок из одной генеральной совокупности, когда каждый элемент выборки наблюдается по двум признакам хну. При этом равенство дисперсий не предполагается. 121 >
Таким образом, необходимо установить, значимо или незначимо различаются статистические оценки хну, вычисленные по выборкам объема п из одной генеральной совокупности. Проверка статистической гипотезы проводится следующим образом. Вычисляют разности выборочных значений х,- и у{. d( = л - х, (/ = 1, 2,... и). (7.16) Полученный ряд разностей dj считается выборкой объема п. Рассчитывают характеристики новой выборки: *=-£</,; (7.17) и.=1 и-1,=1 Вычисляют опытное значение критерия с числом степеней свободы к= п — 1: tb=—4n. (7.19) *«/ Критическая область строится в зависимости от вида конкурирующей гипотезы, при этом рассматриваются три случая. 1. Нулевая гипотеза Hq : МЩ = M[Y\. Конкурирующая гипотеза Щ : МЩ ф M[Y\. В этом случае строят двустороннюю критическую область с уровнем значимости 2а. 2. Нулевая гипотеза Н0 : МЩ = M[Y\. Конкурирующая гипотеза Hi : М[Х\ > M[Y\. В этом случае строят правостороннюю критическую область с уровнем значимости а. 3. Нулевая гипотеза Н0 : МЩ = МЩ. Конкурирующая гипотеза Щ : МЩ < МЩ. В этом случае строят левостороннюю критическую область с уровнем значимости а. Если вычисленный уровень значимости больше заданного, то нет оснований отвергнуть нулевую гипотезу, в противном случае гипотеза отвергается. Пример 7.11. Необходимо сравнить работу двух измерительных приборов, используемых для проверки размеров некоторых 122 деталей. Из партии была сделана случайная выборка объемом п = 10 и проведены замеры обоими приборами. Результаты замеров представлены в таблице. Прибор А X, Прибор В У, d, = x,- -У; 76,10 76,20 0,10 76,20 76,00 -0,20 76,00 76,25 0,25 76,04 76,02 -0,02 76,10 76,18 0,08 76,08 76,06 -0,02 76,18 76,04 -0,14 76,02 76,25 0,23 76,12 76,00 -0,12 76,06 76,10 0,04 При уровне значимости а = 0,05 определите, имеются ли различия между приборами А и В, т.е. проверьте нулевую гипотезу Щ : МЩ = МЩ при конкурирующей гипотезе — Н\ : : МЩ ф МЩ. Решение. По условию задачи имеем две парные случайные выборки. Определим среднее и дисперсию полученных разностей: d = -£</,- = 0,02; si = -Ц-2>, - df = 0,0225 . /=i и-1 ;=i Определим опытное значение критерия: ,B=Z^ = ^VD5 = 0,422. sd 0,15 Определим расчетный уровень значимости: 1 - Р(\1] < 0,422) = 0,684 — для двусторонней критической области; 1 - F[t) = 1 - Р(Т< 0,422) = 0,342 — для односторонней критической области. Так как вычисленный уровень значимости больше заданного, то нет оснований отвергнуть нулевую гипотезу. Это означает, что приборы существенно не отличаются друг от друга. Решим данный пример с использованием функции ТТЕСТ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: 1 i; а Прибор А Прибор В в 76.1 76,2 С, 76.2 76 . D 76 *" 76,25 76,04 76,02 : f 76,1 76,18 G " 76.08 76,06 " 76.18 76,04 I ' 76,02 76.25 ■ J 76,12 76 • К • 76,06 761 123
2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку f* на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ТТЕСТ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ТТЕСТ; 6. нажав кнопку -а! в поле Массив!, перейдем на рабочий лист с исходными данными и выделим его мышью (Bl: K1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ТТЕСТ; at ] 7. нажав кнопку ~ш£ в поле Массив2, перейдем на рабочий лист с исходными данными и выделим его мышью (В2: К2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ТТЕСТ; 8. в поле Хвосты введем значение 2 (для двусторонней критической области); 9. в поле Тип введем значение 1 (парный тест); 10. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,684. Примечание. Если в поле Хвосты ввести значение 1 (для односторонней критической области), то получим расчетный уровень значимости 0,342. Двухвыборочный t-mecm с одинаковыми дисперсиями Двухвыборочный Г-тест с одинаковыми дисперсиями используется для проверки гипотезы о равенстве средних двух выборок из разных генеральных совокупностей. Если возможно предположить, что неизвестные дисперсии генеральных совокупностей равны между собой, то можно построить критерий сравнения средних. Например, если сравниваются средние размеры двух партий деталей, изготовленных на одном и том же станке, то естественно допустить, что дисперсии контролируемых размеров одинаковы. Если нет уверенности в одинаковости дисперсий, то, прежде чем сравнивать средние, следует, пользуясь критерием Фишера (функция ФТЕСТ), предварительно проверить гипотезу о равенстве дисперсий генеральных совокупностей. 124 Таким образом, в предположении, что дисперсии генеральных совокупностей одинаковы, требуется проверить нулевую гипотезу #о : МЩ = МЩ, т.е. необходимо установить, значимо или незначимо различаются статистические оценки х и у , вычисленные по независимым малым выборкам объемов щ и щ- Проверка статистической гипотезы производится следующим образом. Вычисляют статистические оценки средних: _ 1 1А _ 1 & "l ,=i «2 ,=1 Определяют опытное значение критерия J>,-*)2+f>,->02 п,п 1"2 П, +П>, (7.20) Критическая область строится в зависимости от вида конкурирующей гипотезы, при этом рассматриваются три случая. 1. Нулевая гипотеза Щ : М[Х\ = МЩ. Конкурирующая гипотеза Н\ : МЩ ф МЩ. В этом случае строят двустороннюю критическую область с уровнем значимости 2а. 2. Нулевая гипотеза Hq : МЩ = МЩ. Конкурирующая гипотеза Н\ : МЩ > МЩ. В этом случае строят правостороннюю критическую область с уровнем значимости а. 3. Нулевая гипотеза Щ : МЩ — МЩ. Конкурирующая гипотеза Н\ : М[Х\ < МЩ. В этом случае строят левостороннюю критическую область с уровнем значимости а. Если вычисленный уровень значимости больше заданного, то нет оснований отвергнуть нулевую гипотезу, в противном случае гипотеза отвергается. Пример 7.12. Имеются две независимые выборки из генеральных совокупностей XvlY. X, У1 7,52 0,75 8,18 7,94 2,02 4,82 4,46 4,80 1,95 2,36 9,47 7,68 6,79 0,23 6,45 4,15 1,50 3,51 9,91 1,70 125
При уровне значимости а = 0,05 проверьте нулевую гипотезу Н0 : М[Х\ = МЩ при конкурирующей гипотезе Н\ : МЩ ф МЩ. Решение. Рассчитаем средние значения выборок: 1 «1 1 "2 Зс = — 2>, = 5,83 ; у = — £>>, = 3,79 . п 1 (=1 п 2 i=l Опытное значение критерия с числом степеней свободы к = л1 + и2 - 2 = 10 + 10 - 2 = 18. (х~У)\п\ +п2~2 ~.\2 , V1/.. -ч2 ' ' 2 К^-ЗсГ+Ку,-*)2 «!« 1"2 = 1,56. Ii=i i=i Определим расчетный уровень значимости: 1 — Р(|7] < 1,56) = 0,136 — для двусторонней критической области; 1 - ДО = 1 - Р(Т< 1,56) = 0,068 — для односторонней критической области (табл. П.З). Так как вычисленный уровень значимости больше заданного, то нет оснований отвергнуть нулевую гипотезу. Решим данный пример с использованием функции ТТЕСТ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: «' 1 А ф ' 8 - 7,52 0,75 8,18 7,94 Г о 2,02 4,82 4,46 4,8 1,95 2,36 9,47 7,68 ■ООН 6,79 0,23 ин 6,45 4,15 1И111 1,5 3,51 9,91 1,7 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку Ш- на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ТТЕСТ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ТТЕСТ; 6. нажав кнопку -У в поле Массив1, перейдем на рабочий лист с исходными данными и выделим его мышью (Bl: K1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ТТЕСТ; 126 7. нажав кнопку -И в поле Массив2, перейдем на рабочий лист с исходными и выделим его мышью (В2: К2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ТТЕСТ; 8. в поле Хвосты введем значение 2 (для двусторонней критической области); 9. в поле Тип введем значение 2 (гомоскедастический тест); 10. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -» 0,136. Примечание. Если в поле Хвосты ввести значение 1 (для односторонней критической области), то получим расчетный уровень значимости 0,068. Двухвыборочный t-mecm с неравными дисперсиями Двухвыборочный f-тест с неравными дисперсиями используется для проверки гипотезы о равенстве средних двух выборок из разных генеральных совокупностей. При этом предполагается, что их дисперсии неизвестны и в общем случае не равны между собой. Требуется проверить нулевую гипотезу #о : МЩ = МЩ, т.е. необходимо установить, значимо или незначимр, различаются статистические оценки х и у, вычисленные по независимым выборкам из генеральных совокупностей Xvl У объемом щ и щ. При проверке статистической гипотезы вычисляют опытное значение критерия: fB = ,Х~У , (7.21) где J "I J "2 *=—Е*<; у=—S^; П\ (=1 П2 7=1 2 l V/.. —\2 „2 1 V1/-.. — \2 Sl ni (=1 Щ j=\ Число степеней свободы определяют по зависимости (2 г\ — + — \П\ п2 ) к = — , которое округляют до ближайшего целого. ( 2\г ( 2 у n2 1 n2-\ 127
Критическая область строится в зависимости от вида конкурирующей гипотезы, при этом рассматриваются три случая. 1. Нулевая гипотеза В0 : М[Х\ = M[Y\. Конкурирующая гипотеза Н\ : М[Х\ * M[Y\. В этом случае строят двустороннюю критическую область с уровнем значимости 2а. 2. Нулевая гипотеза В0 : М[Х\ = M[Y\. Конкурирующая гипотеза Bi : М[Х\ > M[Y\. В этом случае строят правостороннюю критическую область с уровнем значимости а. 3. Нулевая гипотеза Щ : М[Х\ = M[Y\. Конкурирующая гипотеза В\ : М[Х\ < M[Y\. В этом случае строят левостороннюю критическую область с уровнем значимости а. Если вычисленный уровень значимости больше заданного, то нет оснований отвергнуть нулевую гипотезу, в противном случае нулевая гипотеза отвергается. Пример 7.13. В цехе работают две линии по выпуску однотипных деталей. Сделаны случайные выборки. С линии А взято 8 деталей, а с линии В — 6 деталей. Проведены замеры диаметра деталей. Результаты измерений представлены в таблице. Дисперсии этих линий неизвестны. А (х,) В0>) 7,0 7,7 7,1 8,2 7,3 7,5 7,2 8,1 7,6 7,5 7,7 7,9 7,4 — 7,5 — При уровне значимости а = 0,05 проверьте нулевую гипотезу В0 : МЩ = M[Y\ при конкурирующей гипотезе В\ : М[Х\ ф M[Y\, т.е. проверьте, различаются ли между собой линии. Решение. Вычислим средние: J И, _ 1 "2 х = —][>,. = 7,35 ; у = — £> = 7,82 ; дисперсии: *i2 =—£(*, -х)2 =0,06; s\ =-Lf>7 -yf =0,09. Рассчитаем опытное значение критерия и число степеней свободы: 128 (л + х-у = -3,115, к = - Hi ( л\ v"iy И] -1 - + KH2J Определим уровень значимости: 1 — Д|7] < 3,115) = 0,012 — для двусторонней критической области; 1 - ДО = 1 -Р(Г< 3,115) = 0,006 — для односторонней критической области (табл. П.З). Так как вычисленный уровень значимости меньше заданного, то нулевая гипотеза отвергается, т.е. линии различаются, поэтому они выпускают неодинаковые по диаметру детали. Для выпуска одинаковых деталей линии требуют наладки. Решим данный пример с использованием функции ТТЕСТ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: А 1 1 I AU) .21 BOO 7 7,7 7Д 8,2 7,3 7,5 7,2 8,1 7,6 7,5 7,7 7,9 7,4 - 7,5 - 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку Г на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ТТЕСТ -> ОК; 5. в левом верхнем углу листа Excel появится окно функции ТТЕСТ; 6. нажав кнопку Ш. в поле Массив!, перейдем на рабочий лист с исходными данными и выделим его мышью (В1: II). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ТТЕСТ; 7. нажав кнопку Ш в поле Массив2, перейдем на рабочий лист с исходными данными и выделим его мышью (В2: G2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ТТЕСТ; 5 Статистические функции в экономике- 129 cTaTMrTifMcriftiv ■
8. в поле Хвосты введем значение 2 (для двусторонней критической области); 9. в поле Тип введем значение 3 (гетероскедастический тест); 10. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,012. Примечание. Если в поле Хвосты ввести значение 1 (для односторонней критической области), то получим расчетный уровень значимости 0,006. 7.12. Определение значения функции распределения у2. Функция ХИ2РАСП Синтаксис функции: ХИ2РАСП (X, Степени_свободы) Распределению %2 подчиняется случайная величина: Y = X^+xl+... + Xl, (7.22) с2=1 где Xj — независимые нормальные случайные величины с тх = 0 и Функция плотности распределения у} имеет вид: /О0 = -Цнге 2У2 , У>0, (7.23) ® где п = к — число степеней свободы; 41) гамма-функция. График функции плотности распределения у} изображен на рис. 7.6. Числовые характеристики распределения ц2: • математическое ожидание М[ Y\ = к; • дисперсия D[Y\ = 2к, где к — число степеней свободы, равное числу независимых слагаемых в (7.22). Распределение у} не зависит от числовых характеристик (M[Y\ и D[Y\), а зависит лишь от объема выборки п. Данное распределение в математической статистике используется при построении доверительных интервалов для диспер- 130 сии (среднего квадратического отклонения) и проверке статистических гипотез. 2 4 6 8 10 12 Рис. 7.6. Функция плотности распределения %2 Функция распределения ^-распределения определяется зависимостью F{y) = - 1 22Г 2) du. (7.24) С использованием распределения у}, определяемого функцией плотности (7.23) и функцией распределения (7.24), находят уровень значимости а для односторонней доверительной вероятности Р = 1 — а, отвечающий значению у и числу степеней свободы к. Окно данной функции представлено ниже (числовые данные из примера 7.14). Если какой-либо из аргументов не является числом, то функция ХИ2РАСП показывает значение ошибки #ЗНАЧ! Если X отрицательно, то функция ХИ2РАСП показывает значение ошибки #ЧИСЛО! Если Степени_свободы не целое, то оно усекается. Если Степени_свободы меньше 1 или Степени_свободы =>1010, ХИ2РАСП показывает значение ошибки #ЧИСЛО! 131
j"<MZPACn ; X jl9,674 ~ -0 "* 19>674 j ^_l 1 i Pr-F- i*« i , Степени_свободы|11 _ _ _ ^y-n «0,050017051- Возвращает одностороннкж!бе^ятмость|Зв(ПрвДвЙмияхи-квад|)ат. , *..: Степени_сво6одычисло степеней свободы - число от 1 до 10Л1Й,.исключая 1СКЧ0.' . Щ] Значение. В,05 « "> ^ 1 Щ. 1 Отмена f где X — значение, для которого требуется вычислить распределение; Степенисвободы — число степеней свободы. ХИ2РАСП вычисляется как ХИ2РАСП = Р(Х> х), где X — у} случайная величина. I, ■ i 7 ' Случайная величина Yимеет распределение у} с числом степеней свободы к=\\. Для значения >>= 19,674 и у = 8,147 определите доверительную вероятность Р=1 — а = — Ду) и уровень значимости а. Решение. Значение Р = 1 — а определим по зависимости (7.25): для у = 19,674; Р = 1 - а = 0,95; а - 0,05; для у = 8,147; Р = 1 - а = 0,30; а = 0,70 (табл. П.4). Решим данный пример с использованием функции ХИ2РАСП. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку * на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию ХИ2РАСП -> ОК; 4. в левом верхнем углу листа Excel появится окно функции ХИ2РАСП; 5. в поле X введем значение 19,674; 6. в поле Степени_свободы введем значение к = 11; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,05 (уровень значимости а). Примечание. Если в поле X ввести значение 8,147, то получим уровень значимости а = 0,70. 132 7.13. Определение параметра у по значению функции распределения у2. Функция ХИ20БР Синтаксис функции: ХИ20БР {Вероятность, Степени_свободы) При использовании данной функции решается обратная задача: известно, что случайная величина Y имеет распределение у}, определяемое законом распределения (7.23) или (7.24): известны параметры распределения^2 — число степеней свободы к = п и односторонняя доверительная вероятность Р = 1 — а . Необходимо по числу степеней свободы к и уровню значимости а определить параметр у, при котором функция распределения принимает заданное значение. Окно данной функции имеет следующий вид (числовые данные из примера 7,15): (HZ06P Вероятность |р,70 ^jw.0,7 Степени_соободы и т 11 -в,14?865117 т ?наченив обратное к односторонней вероятности распределения хи-кеадрат ш Степени^свободы число степеней свободы - число 6т i до 10'А10, исключая 10*10, * ' ••, и™ ,,' , Значение'.б.Ш ',>, '■ , <к. | Отмена | где Вероятность — вероятность, связанная с распределением %2 (уровень значимости); Степени_свободы — число степеней свободы. Если какой-либо из аргументов не является числом, то функция ХИ20БР показывает значение ошибки #ЗНАЧ! Если Вероятность меньше 0 или Вероятность больше 1, то функция ХИ20БР показывает значение ошибки #ЧИСЛО! Если Степе- ни_свободы не целое, то оно усекается. Если Степени_свободы меньше 1 или Степени_свободы => 1010, ХИ20БР показывает значение ошибки #ЧИСЛО! ХИ20БР использует метод итераций для вычисления значения. Если задано значение вероятности, то функция ХИ20БР производит итерации, пока не получит результат с точностью ± 3 • Ю-7. Если ХИ20БР не сходится после 100 итераций, то функция показывает значение ошибки #Н/Д. 133
Пример 7.15. Случайная величина Y имеет распределение %2 с числом степеней свободы к= 11. В некоторой точке у функция распределения Ду) = 0,30 (0,95). Определите значение этой точки. Решение. Значение параметра у определим по функции распределения (7.24). При Р = 0,3; а = 1 - Р = 1 - F(y) = 1 - 0,3 = 0,7; у = 8,148; Р = 0,95; а = 1 - Р = 1 - Щу) = 1 - 0,95 = 0,05; у = 19,675 (табл. П.4). Решим данный пример с использованием функции ХИ20БР. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку JZJ на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию ХИ20БР -> ОК; 4. в левом верхнем углу листа Excel появится окно функции ХИ20БР; 5. в поле Вероятность введем значение 0,70; 6. в поле Степени_свободы введем значение к = 11; 7. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 8,148. Примечание. Если в поле Вероятность ввести значение а= 0,05, то получим у = 19,675. 7.14. Проверка гипотезы о виде закона распределения (определение вероятности значения %2). Функция ХИ2ТЕСТ Синтаксис функции: ХИ2ТЕСТ (Фактическийинтервал, Ожи- даемыйинтервал) Если закон распределения генеральной совокупности не известен, то по выборке проверяется гипотеза о виде закона распределения при помощи критериев согласия. Одним из наиболее используемых критериев является критерий у}. Согласно этому критерию наблюдаемое эмпирическое распределение выборки, выраженное абсолютными и относитель- 134 ными частотами сгруппированного ряда, сравнивается с гипотетическим теоретическим распределением соответствующей генеральной совокупности. Для этого выдвигается гипотеза Щ, утверждающая, что признак генеральной совокупности имеет функцию распределения F(x), которая сопоставляется с выборочной функцией, и в зависимости от величины отклонения эмпирического распределения от теоретического выдвинутая гипотеза принимается или отвергается. При проверке статистических гипотез о виде закона распределения строится интервальный (табл. 7.2) или дискретный (табл. 7.1)вариационный ряд. Таблица 7.1 Дискретный вариационный ряд Варианты, х,- Частоты, щ Вероятность, pt npf Xi Щ Pi npi x2 m2 Рг np2 ... ... Xj Щ Pi npf ... Xj Щ Pi npi Таблица 7.2 Интервальный вариационный рад Интервалы Частоты, rrij Вероятность, р. пр{ хх-х2 Щ Р\ прх x2-xi т2 Рг пр2 ... Xj—i Xj т-\ Pi-i npi-i Xj Xj+i Щ Pi пр; где pi — вероятность попадания случайной величины в данный интервал для непрерывной случайной величины (вероятность того, что случайная величина приняла данное значение для дискретной случайной величины). Определяется согласно выдвинутой гипотезы; п — объем выборки. По данным вариационного ряда определяется опытное значение критерия Z£=£0V^>1, (7.25) 1=1 "Pi 135
число степеней свободы к=1-г- 1, где / — число вариант (интервалов) вариационного ряда; г — число оцениваемых параметров распределения. Определяется вероятность того, что случайная величина Y примет значение больше ^в > т-е- a = P(Y>xl) = l~F(y), (7.26) где F(y) — функция распределения %2 (7.24). По величине вероятности (7.26) принимается или отвергается выдвинутая гипотеза Щ о виде закона распределения случайной величины. Для принятия гипотезы используются стандартные вероятности а^ = 0,05; а^ = 0,10. При а > а^ гипотеза принимается, в противном случае гипотеза отвергается. Окно данной функции имеет следующий вид (числовые данные из примера 7.16): з"хмгтЁст'--.—-■ ~ — .- - ■--" --■ г ---■ —:—... . ■__-_ j Фактический_интервал ]В1:Н1 53 =.-{6;8;15;40;16;8;7} Ожидаемый_интервал |в2:Н2 *У = -{4,05; 10,45;21,03;; !_.:.. ... _,_ =Ш~... ."^Ша^ 1 '...' ■_ ■ "" ' = 0,036296896 Возвращает тест на независимость: значение распределения Хи-квадрат для статистического твет ствующего числа степеней свободы. Ожидаемый_интервал диапазон, содержащий отношение произведений итогов по строкам и столбцам к общему итогу. ' . ® Значение; 0,036 OK : I Отмена где Фактическийинтервал — интервал данных, который содержит наблюдения, подлежащие сравнению с ожидаемыми значениями; Ожидаемыйинтервал — интервал данных, который содержит произведения npj (ожидаемые значения частот). Если Фактический_интервал и Ожидаемый_интервал имеют различное количество точек данных, то функция ХИ2ТЕСТ показывает значение ошибки #Н/Д. Пример 7.16. Используя критерий у} при уровне значимости а = 0,05, проверьте, согласуется ли гипотеза о нормальном распределении генеральной совокупности с эмпирическим распределением выборки объема п = 100 из этой генеральной совокупности. Выборка представлена интервальным рядом. 136 у Интервал Частота, mt 3-8 6 8-13 8 13-18 15 18-23 40 23-28 16 28-33 8 33-38 7 Решение. 1. Определим статистические оценки параметров распределения: / Е**»* Х=^г = 20,7; 2>,- 1=1 £(•*,■ -х) т1 ?=t± E™i i=i : 52,96: ах = 7,28. 2. Вычислим вероятность попадания нормальной случайной величины с параметрами х и сх в интервал с использованием функции Лапласа, приняв Xj = —со, xf = со: Ф 1+1 Л | ^ж.1 I Pi(x,<X<xi+x) = ^ (х — х 1 2 —— где Ф —— = Ф(г) = —== f e 2 dz— функция Лапласа (табл. П.2), \ сх ) V2nJ и составим расчетную таблицу. Интервал Pi npi 3-8 0,0405 4,05 8-13 0,1045 10,45 13-18 0,2103 21,03 18-23 0,2687 26,87 23-28 0,2181 21,81 28-33 0,1124 11,24 33-38 0,0455 4,55 3. Вычислим опытное значение критерия: .1=1^^^ = 13,46 i=i nP, и число степеней свободы к=1 — 2 — 1 = 4. 137
4. По таблице распределения %2 (табл. П.4) определим вероятность (можно использовать рассмотренную функцию ХИ2РАСП, п. 7.12): a = P(F>5ci) = 0,036. Так как a < a^ = 0,05, то гипотезу о нормальном распределении генеральной совокупности отвергаем. Решим данный пример с использованием функции ХИ2ТЕСТ. v Алгоритм действий следующий: 1. сформируем рабочую таблицу, где будут производиться промежуточные расчеты: 14 ,15 16, 4? № 19 | Границы 1 интервалов.^ Середины интерваловЛ Xj —X R-*)2 1 — р, «р, Частоты,^ 3 8 55 -152 231Д4 -1376.97 -1.75 0.0405 4fl5 Б 8 13 105 -102 104JD4 -1.75 -1J06 0,1045 10.45 8 13 16 155 -52 там -1J06 -0,37 02103 21 да 15 18 23 205 -02 0.04 -037 0.32 02687 26 #7 40 23 28 255 4.8 23.04 0.32 1ДО 02181 21.81 1Б 28 33 305 9.8 96,04 1ДО U69 0.1124 1124 8 33 38 355 14.8 21904 1J69 137128 0.0455 456 7 22' 2071 ! 1_ ' 23' 1 j 24 52.96| I В данной таблице приведены следующие данные: / • ячейка А22 содержит значение Зс = ~ = 20,7; / £(*,. -x)2mt • ячейка А24 содержит значение Ъ\ = —— = 52,96; i=i • ячейка А26 содержит значение дх =7,28; 138 • в первой строке рабочей таблицы приведены значения границ интервалов х,-; • вторая строка таблицы содержит значения середин интервалов Зс(; • в третьей строке рассчитываются значения (xi — Зс); • в четвертой строке рассчитываются значения (Зс,. -Зс)2; X — X • в пятой строке рассчитываются значения z, = — , при- няв Х\ = —оо, х,- = °о; • шестая строка содержит значения вероятностей попадания случайной . . в интервалы, рассчитанные с помощью функции НОРМСТРАСП: д=НОРМСТРАСП fc+1)-HOPMCTPACn fe); • в седьмой строке приведены значения произведений npf, • восьмая строка содержит значения эмпирических частот; 2. сформируем таблицу исходных данных, содержащую только две строки: строку со значениями эмпирических частот и строку со значениями рассчитанных частот: J_ ...А Частота.m, в; и* В 6 4.05 С 8 10.45 0 15 21.03 Е «и 40 26.87 F, 16 21,81 е 8 11.24 Н'ц / 4.55 3. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 4. вызовем Мастер функций, нажав кнопку 2L на панели инструментов; 5. в Мастере функций из категории Статистические выберем функцию ХИ2ТЕСТ -» ОК; 6. в левом верхнем углу листа Excel появится окно функции ХИ2ТЕСТ; 7. нажав кнопку -Я в поле Фактический_интервал, перейдем на рабочий лист с исходными данными и выделим его мышью (Bl: HI). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ХИ2ТЕСТ; 8. нажав кнопку Jm в поле Ожидаемый_интервал, перейдем на рабочий лист с исходными данными и выделим его мышью (В2: Н2). Затем, повторно нажав эту же кнопку, возвращаемся к окну функции ХИ2ТЕСТ; 9. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений ->0,036(а = Р(Г>х|)). 139
Пример 7.17. Проведено 100 опытов, каждый из которых состоит из 10 испытаний. Вероятность появления события А в одном испытании р = 0,3. В итоге получено следующее эмпирическое распределение. Число появления события А в одном опыте, Xj Число опытов, в котором событие А появилось Xj раз 0 2 1 10 2 27 3 32 4 23 5 6 Проверьте гипотезу о том, что число появлений события А распределено по биномиальному закону. Решение. 1. Вычислим вероятности появления события А в одном опыте i раз Р, = сюР Я и составим расчетную таблицу. / Pi пр; 0 0,0282 2,82 1 0,1211 12,11 2 0,2335 23,35 3 0,2668 26,68 4 0,2001 21,01 5 0,1029 10,29 2. Вычислим расчетное значение критерия x|=£K-^ = 4j214 ,=i "Pi и число степеней свободы к= I— г— 1 = 6— 1 — 1=4. 3. По таблице распределения у} (табл. П.4) определим вероятность (можно использовать рассмотренную функцию ХИ2РАСП, п. 7.12): a = P(Y>x2B) = 0,5l9. Так как а > акр = 0,05, то нет оснований отвергнуть гипотезу о биномиальном распределении генеральной совокупности. Решим данный пример с использованием функции ХИ2ТЕСТ. Алгоритм действий следующий: 1. сформируем рабочую таблицу, где будут производиться промежуточные расчеты: 140 н 8 9 I Pi nPt 0 0,0282 2,82 1 0,1211 12,11 2 0,2335 23,35 3 0,2668 26,68 4 0,2001 21,01 5 0,1029 10,29 В этой таблице приведены следующие данные: • в первой строке приведены значения числа появления события А в одном опыте /; • вторая строка содержит значения вероятностей появления события А в одном опыте / раз, рассчитанные с помощью функции БИНОМРАСП (/; 10; 0,3; ЛОЖЬ); • в третьей строке приведены значения произведений пр-{, 2. сформируем таблицу исходных данных. Данная таблица состоит из двух строк: в первой строке представлены эмпирические частоты ту (число опытов, в котором событие А появилось i раз), во второй строке — расчетные частоты npt (рассчитаны по биномиальному закону); ! 1 % да А Частота, т, «Ъ .; 2 2,82 10 12,11 D \ 27 23,35 Е JttLL-, 32 26,68 23 21,01 6 10,29 3. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 4. вызовем Мастер функций, нажав кнопку г" на панели инструментов; 5. в Мастере функций из категории Статистические выберем функцию ХИ2ТЕСТ -> ОК; 6. в левом верхнем углу листа Excel появится окно функции ХИ2ТЕСТ; 7. нажав кнопку ДУ в поле Фактический_интервал, перейдем на рабочий лист с исходными данными и выделим его мышью (Bl: G1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ХИ2ТЕСТ; 8. нажав кнопку Jm в поле Ожидаемый_интервал, перейдем на рабочий лист с исходными данными и выделим его мышью (В2: G2). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ХИ2ТЕСТ; 9. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений ->0,519(а = Р(У>5св)). 141
7.15. Определение значения функции распределения F-распределения (распределения Фишера — Снедекора). Функция FPACn Синтаксис функции. FPACIJ {X, Степени_свободы1, Степени свободы!) ^-распределение имеет случайная величина У Х = -^—к (7.27) *г, % где Х\ и Xi — независимые случайные величины, имеющие %2-рас- пределение соответственно с к\ и kj степенями свободы. Если xi, х2, ..., X/tt — выборка из нормальной (тх,сх) генеральной совокупности, а у\, у2, ..., Ук2 — выборка из нормальной (ту,о2у) генеральной совокупности, то статистика 1 *> f = A_Lm . (7_28) 1_к- к2~Ч=1 К __ л к- 1 h. - ~ 1 I-} 1 Д, - 1 *2, где х = —^х,- , ^ = —Х-^/ ' имеет ^-распределение с (&i — 1, &2 — 1) степенями свободы; /"-распределение имеет функцию плотности: (ъ. Л /00 = - 2 . Atj k2 В|У'Т . 2 / *! V ^ 1—— х , х>0, (7.29) 1 где B(r,s) = jxr~,(l-x)s~ldx —бета-функция. о График функции плотности ^-распределения имеет следующий вид (рис. 7.7). 142 № 1,0 0,8 0,6 0,4 0,2 кг = 1, кг = 4 Лх = 10, кг = 50 #1 = 4, *2 = °° „*1 = 4, Л2 = 2 р = 1 — а + + 1 2 Рис. 7.7. График функции плотности F-распределения Числовые характеристики ^-распределения: математическое ожидание — М[Х] = к2-2 ко>2; , К2 дисперсия — ЩХ] = 2к2(кх+к2-2) , к2>4. кх{к2-2){к2-*)' ""' ^-распределение в математической статистике используется для проверки статистических гипотез. Функция распределения ^-распределения определяется зависимостью: к. \к2) } f-if, кх V 2 ,2'2 J В du. (7.30) С использованием ^-распределения, определяемого функцией плотности (7.29) или функцией распределения (7.30), определяется уровень значимости а для односторонней доверительной 143
вероятности р = 1 — а, отвечающей значению х с к\ и к2 степенями свободы (рис. 7.7). Окно данной функции имеет следующий вид (числовые данные из примера 7.18): ,'f+WJl xji^j '; з.-з»«' Степени_сво6оды115 53 ""'5 J ' п. пи ц i „.I», I, . , |,..,М, it Пиит , , Степени_свободы2 8 3J " 8 . Возвращает F-распределение вероятности (степень отклонения) для двух наборов данных.} Степени свободы? знаменатель степеней свободы - число от i до ICCMO, исключая , . ЮЛЮ. - - • ., • © . Значение; 0,05. (Ж I - ■ Отмена где X — значение, для которого вычисляется функция; Степени_свободы1 — степени свободы числителя; Степени_свободы2 — степени свободы знаменателя. Если какой-либо из аргументов не является числом, то функция FPACI1 показывает значение ошибки #ЗНАЧ! Если X отрицательно, то функция FPACI1 показывает значение ошибки #ЧИСЛО! Если Степени_свободы1 или Степени_свободы2 не целое, то оно усекается. Если Степени_свободы1 меньше 1 или Степени_свободы1 => 1010, FPACI1 показывает значение ошибки #ЧИСЛО! Если Степени_свободы2 меньше 1 или Степе- ни_свободы2 => 1010, FPACI1 показывает значение ошибки #ЧИСЛО! FPACI1 вычисляется следующим образом: FPACn=P (F<x), где F — случайная величина, которая имеет ^-распределение. Пример 7.18. Случайная величина X имеет ^-распределение с A^i = 5, kj = 8 степенями свободы. Определите значения функции распределения Дх) при х = 3,69 и при х = 6,63. Решение. Значения функции распределения Дх) определим по формуле (7.30): х = 3,69; F(x) = р = 1 - а = 0,95; а = 0,05; х = 6,63; F(x) = р = 1 - а = 0,99; а = 0,01 (табл. П.5). Решим данный пример с использованием функции FPACI1. Алгоритм действий следующий: / 144 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку ^— на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию FPACn -► OK; 4. в левом верхнем углу листа Excel появится окно функции | FPACn; 5. в поле X введем значение 3,69; 6. в поле Степени_свободы1 введем значение к\ = 5; 7. в поле Степени_свободы2 введем значение к2 = 8; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -► 0,05 (уровень значимости). Примечание. Если в поле X ввести значение 6,63, то получим значение уровня значимости а = 0,01. 7.16. Определение параметра х по значению функции распределения F-распределения. Функция FPACnOBP Синтаксис функции: ГРАСПОБР (Вероятность, Степени_сво- боды1, Степени_свободы2) При использовании данной функции решается следующая задача: известно, что случайная величина X имеет ^-распределение, определяемое законом распределения (7.29) или (7.30); известны параметры ^-распределения — числа степеней свободы кх и к2 и односторонняя доверительная вероятность р=\-а. Необходимо по числу степеней свободы к\, к2и уровню значимости а определить параметр х, при котором функция распределения принимает заданное значение. Окно данной функции представлено ниже (числовые данные ' из примера 7.19). Если какой-либо из аргументов не является числом, то функция FPACnOBP показывает значение ошибки #ЗНАЧ! Если Вероятность меньше 0 или Вероятность больше 1, то функция FPACnOBP показывает значение ошибки #ЧИСЛО! Если Степе- ни_свободы1 или Степени_свободы2 не целое, то оно усекается. Если Степени_свободы1 меньше 1 или Степени_свободы1 => ДО10, FPACnOBP показывает значение ошибки #ЧИСЛО! Если Сте- 145
пени_свободы2 меньше 1 или Степени_свободы2 => 1010, FPAC- ПОБР показывает значение ошибки #ЧИСЛО! и-ажййирйяйше = 0,05 Степени_ . > • • 1J5 V| = 5 ■-■'■• ю^:—^ Степени_свободы2 J8 3d"-" е _ а , • ' ',''', = 3,687503636 Возвращает обратное значение для F-раслределения вероятностей; если р = FPAOKx,...}, то ■РРАСГЮБРСр,....)-х , Степени_свободы2 знаменатель степеней свободы - число от 1 до 10л10, исключая 10ЛШ. Значение: 3.69 ОК | Отмена где Вероятность — вероятность, связанная с /"-распределением (уровень значимости); Степени_свободы1 — степени свободы числителя; Степени_свободы2 — степени свободы знаменателя. FPACIIOBP можно использовать, чтобы определить критические значения ^-распределения. Например, результаты дисперсионного анализа обычно включают данные для F- статистики, ^-вероятности и /'-критическое значение с уровнем значимости 0,05. Чтобы определить критическое значение F, нужно использовать уровень значимости а как аргумент Вероятность для FPACIIOBP. FPACIIOBP использует метод итераций для вычисления значения. Если задано значение вероятности, то функция FPACIIOBP производит итерации, пока не получит результат с точностью ± 3 • Ю-7. Если FPACIIOBP не сходится после 100 итераций, то функция показывает значение ошибки #Н/Д. Пример 7.19. Случайная величина X имеет /'-распределение с числом степеней свободы A:i == 5, ki = 8. В некоторой точке х функция распределения Дх) = 0,95(0,99). Определите значение этой точки. Решение. Значение параметра х определяется по функции распределения (7.31). а = 1 - р = 1 - Дх) = 0,05; х = 3,69; а = 1 - р = 1 - Дх) = 0,01; х = 6,63 (табл. П.5). 146 Решим данный пример с использованием функции FPACIIOBP. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 2. вызовем Мастер функций, нажав кнопку -£*» на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию FPACnOBP -> OK; 4. в левом верхнем углу листа Excel появится окно функции FPACnOBP; 5. в поле Вероятность введем значение 0,05; 6. в поле Степени_свободы1 введем значение к\ = 5; 7. в поле Степени_свободы2 введем значение ki = 8; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 3,69. Примечание. Если в поле Вероятность ввести значение а = 0,01, то получим х = 6,63. 7.17. Проверка гипотезы о равенстве дисперсий (определение вероятности статистики F). Функция ФТЕСТ Синтаксис функции: ФТЕСТ (Массив 1, Массив!) На практике необходимость сравнения дисперсий возникает при сравнении точности приборов, инструментов, самих методов измерений. Предпочтение следует отдать прибору (методу), который имеет большую точность (меньшую дисперсию). Пусть имеются две генеральные совокупности X и Y, имеющие нормальное распределение. Из этих совокупностей извлечены две выборки, объемом щ и «2, по которым определены статистические оценки дисперсий: 1 "' — sx2 =—гХ(*,-*)2; И1~1ы (7.31) *,2=-Ц£с*-*)2. «2-1 И Требуется по этим дисперсиям при заданном уровне значимости а проверить нулевую гипотезу, состоящую в том, что генеральные дисперсии равны между собой. 147
Если окажется, что нулевая гипотеза справедлива, т.е. генеральные дисперсии одинаковы, то различие вычисленных дисперсий незначимо и объясняется случайными причинами. Например, если различие вычисленных дисперсий результатов измерений, выполненных двумя приборами, оказалось незначимым, то приборы имеют одинаковую точность. Если нулевая гипотеза будет отвергнута, т.е. генеральные дисперсии не одинаковы, то различие вычисленных дисперсий значимо и не может быть объяснено случайными причинами, а является следствием того, что сами генеральные дисперсии различны. Например, если различие вычисленных дисперсий результатов измерений, производимых двумя приборами, оказалось значимым, то точность приборов различна. При проверке статистической гипотезы вычисляют опытное значение критерия (отношение большей дисперсии к меньшей): - 2 SM и числа степеней свободы к\ = щ — 1; А^2 = «2 ~ 1- Дисперсии вычисляются по формулам (7.31). Критическая область строится в зависимости от вида конкурирующей гипотезы. При этом рассматривают два случая. 1. Нулевая гипотеза Я0 : D[X\ = D[Y\. Конкурирующая гипотеза Н\ : D[X\ * D[Y\. В этом случае строят двустороннюю критическую область с уровнем значимости 2а. 2. Нулевая гипотеза Я0 : D[X\ = D[Y\. Конкурирующая гипотеза Н\ : ЩХ\ > D[Y\. В этом случае строят одностороннюю критическую область с уровнем значимости а. Для вычисленного значения критерия (7.32) определяют расчетное значение уровня значимости (табл. П.5): а = I\FB > x). (7.33) Если вычисленный уровень значимости больше заданного, то гипотеза не отвергается (принимается). Окно данной функции имеет следующий вид (числовые данные из примера 7.20): 148 "Ч>ТЕСТ" Массив! В1:К1 MactMB2JB2:K2 ЦЩЖР ш , | — • т"г . • | т результат F-теста, рдностероннюю вероятность сходства дву = 0,599699125 ' Массив? второй массив или диапазон - числа, массивы или ссылки 1 числа(пробелы щ ,3начение:0,60 ОК 0WH9 где Массив1 — первый массив или интервал данных; Массив2 — второй массив или интервал данных. Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки с нулевыми значениями учитываются. Если количество точек, данных в аргументе Массив1 или Массив2, меньше 2, или если дисперсия аргумента Массив1 или Массив2 равна нулю, то функция ФТЕСТ показывает значение ошибки #ДЕЛ/0! Пример 7.20. Для сравнения точности двух станков-автоматов взяты две выборки, объемом п\ — 10 и «2 = 8. В результате измерения контролируемого размера получены результаты: X, У1 1,08 1,33 1,14 1,35 1,25 1,22 1,10 1,18 1,36 1,36 1,42 1,12 1,40 1Д1 1,38 1,38 1,15 — 1,12 — Можно ли считать, что станки имеют одинаковую точность при уровне значимости а = 0,10? Решение. Рассчитаем средние значения выборок и статистические оценки дисперсий: х = —1>,. = 1,240 ; у = — f> = 1,266; <i i=i <2 <=1 sx2 =-Ц-1>,--*)2 =0>°187' «1 "I (=1 149
1 "2 Д^,-JO2 =0,0124. «2-1 1=1 Вычислим опытное значение критерия: 2 У _ 0,0187 ~ 0,0124 1,51. ъм Уровень значимости для опытного значения Fq определим по формуле (7.30): х = 1,51; а = 0,3; 2а = 0,6. Так как а = 0,3 > а = 0,1, то гипотеза о равенстве дисперсий принимается. Станки имеют одинаковую точность. Решим данный пример с использованием функции ФТЕСТ. Алгоритм действий следующий: 1. сформируем таблицу исходных данных: ' , . А ... •ifc •2 Л i 1,08 1,33 1,14 1,35 __^ 1,22 1,1 1,18 1,36 1,36 1,42 1Д2 1,4 1,11 1,38 1,38 1,15 - 1,12 - 2. выберем ячейку, в которую будет выведен результат вычислений ($А$4); 3. вызовем Мастер функций, нажав кнопку ®шш»* на панели инструментов; 4. в Мастере функций из категории Статистические выберем функцию ФТЕСТ -► ОК; 5. в левом верхнем углу листа Excel появится окно функции ФТЕСТ; 6. нажав кнопку -У в поле Массив 1, перейдем на рабочий лист с исходными данными и выделим его мышью (Bl: K1). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ФТЕСТ; 7. нажав кнопку JH в поле Массив2, перейдем на рабочий лист с исходными данными и выделим его мышью (В2: 12). Затем, повторно нажав эту же кнопку, возвратимся к окну функции ФТЕСТ; 8. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -> 0,60 (двусторонний уровень значимости 2а). !| 150 7.18. Определение значения преобразования Фишера. Функция ФИШЕР Синтаксис функции: ФИШЕР {X) Плотность распределения выборочного коэффициента корреляции Гху, вычисляемого по выборке объема п из двумерной нормальной генеральной совокупности с коэффициентом корреляции р^, в общем случае имеет довольно сложный вид (особенно при р»у*0). Распределение выборочного коэффициента корреляции можно приближенно привести к нормальному с помощью преобразования Фишера: 1 1 + ^w Z=-ln ^, (7.34) 2 1-г 'ху п _ Z(*, - *)(у, - у) где Гф = ( — выборочный коэффициент корре- if -*>22>,-Л2 /=1 ляции; — 1 " — 1 " х = —'Y.Xf ; у = —У\у, — выборочные средние. И ,=1 « ,=1 Распределение величины Z, отдельные реализации которой определяются соотношением (7.34), при и -> оо стремится к нормальному с числовыми характеристиками: M[Z] = iln^^+-^—; D[Z] = ^-. 2 \-9ху 2(л-1)' и-з При этом даже при небольших и, приближение достаточно хорошее, и получаемая погрешность аппроксимации вполне приемлема для решения практических задач. Окно данной функции имеет следующий вид (числовые данные из примера 7.21): Если X не является числом, то функция ФИШЕР показывает значение ошибки #ЗНАЧ! Если X <= -1 или X => 1, то функция ФИШЕР показывает значение ошибки #ЧИСЛО! Пример 7.21. По двумерной выборке объема и определен выборочный коэффициент корреляции г^ = 0,541. Определите значение Z-преобразования Фишера. 151
ФИШЕР- X 10,541 _ ^ 3=0,541 ' • ' , • , - 0,605568313 Возвращает ание Фишера, более подробные сведения приведены в системе. . X чистовое значение, которое желательно ть, целое число е интервале от -1 до 1, исключая концы. Значение:0,6056 ' ОК ( Отмена 1 где X — числовое значение, которое желательно преобразовать. Решение. По формуле (7.34) определяем: z = ilni±054l 2 1-0,541 Решим данный пример с использованием'функции ФИШЕР. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); т. 2. вызовем Мастер функций, нажав кнопку «Г* на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию ФИШЕР -► ОК; 4. в левом верхнем углу листа Excel появится окно функции ФИШЕР; 5. в поле X введем значение 0,541; 6. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -► 0,6056. 7.19. Определение обратного преобразования Фишера. Функции ФИШЕРОБР > Синтаксис функции: ФИШЕРОБР (Y) Обратное преобразование проводится по зависимости v =— — (7.35) С помощью обратного преобразования по величине Z определим выборочный коэффициент корреляции г^. 152 Окно данной функции имеет следующий вид (числовые данные из примера 7.22): гФИШЕРОБР - -.—;——• -,-.v~- -,. ... .— . ..-._.. _. ,_ ' , ■. YJ0,6056 53 = 0,6056 "'•; ,j '• - '■-'•'" ■■■ ■'-' •' ' '•' .'"•■ '..'■.•'" ' =0,541022413 обратное преобразование Фишера: если у «< ФИШЕР(х)/то ФИШЕРОБРДу)=•*. Более ■ ■ • подробные сведения приведены в справочной системе. , ■■:■■'■ ■ - У значение, для которого производится обратное'преобразование,' - Значение: 0,541 ' | ' IX J ОЧмена] где Y — значение, для которого производится обратное преобразоюние. Если Y не является числом, то функция ФИШЕРОБР показывает значение ошибки #ЗНАЧ! Пример 7.22. Известно значение преобразования Фишера Z— 0,6056. Определите значение выборочного коэффициента корреляции. Решение. Значение выборочного коэффициента корреляции определим по формуле (7.35): 20,6056 _ j г = 0541 *У 20,6056 , , U'J41 • е +1 Решим данный пример с использованием функции ФИШЕРОБР. Алгоритм действий следующий: 1. выберем ячейку, в которую будет выведен результат вычислений ($А$4); ш- 2. вызовем Мастер функций, нажав кнопку =£- на панели инструментов; 3. в Мастере функций из категории Статистические выберем функцию ФИШЕРОБР -> ОК; 4. в левом верхнем углу листа Excel появится окно функции ФИШЕРОБР; 5. в поле Y введем значение 0,6056; 6. в окне функции появится результат решения. Нажав кнопку ОК, в ячейке $А$4 появится результат вычислений -► 0,541. 153 i