ЛАБОРАТОРНАЯ РАБОТА «Статистические функции MS Excel 2013. Построение рядов данных»
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Международный конкурс по экологии «Экология России»
Доступно для всех учеников 1-11 классов и дошкольников
«Статистические функции MS Excel 2013. Построение рядов данных»
научиться применять статистические функции для обработки данных; закрепить навыки форматирования таблицы;
воспитывать в себе аккуратность и внимательность при выполнении работ с электронными таблицами;
развивать логическое мышление, необходимое для работы с большими массивами информации
Статистические функции позволяют выполнять статистический анализ диапазонов данных: нахождение минимального и максимального значения среди исходных чисел, выполнение элементарного подсчёта числовых значений, подсчёт числовых значений в соответствии с определённым условием и т.д. Статистические функции входят в категорию Статистические Мастера функций (рис.1).
Рис.1. Окно Мастера функций с выбранное категорией функций Статистические
Рассмотрим ряд статистических функций, встречающихся в простейших вычислениях.
Нахождение минимального значения (среди числовых значений) в списке аргументов с помощью функции МИН . Формат записи функции:
Количество допустимых аргументов, среди которых находится минимальное значение, равно 255.
Пример . Найти наименьшее значение цены на книгу «Гарри Поттер и дары смерти» среди магазинов города. Пусть в ячейках B 3: B 7 внесены значения цены (рис.2). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию МИН. Укажите исходные значения и нажмите OK .
Нахождение максимального значения (среди числовых значений) в списке аргументов с помощью функции МАКС . Формат записи функции:
Количество допустимых аргументов, среди которых находится максимальное значение, равно 255.
Пример . Найти максимальное значение цены на книгу «Гарри Поттер и дары смерти» среди магазинов города. Пусть в ячейках B 3: B 7 внесены значения цены (рис.3). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию МАКС. Укажите исходные значения и нажмите OK .
Рис. 2. Нахождение минимального значения среди аргументов
Рис. 3. Нахождение максимального значения среди аргументов
Нахождение среднего арифметического значения с помощью функции СРЗНАЧ . Формат записи функции:
Количество допустимых аргументов, среди которых находится среднее значение, равно 255.
Пример . Найти среднее значение цены на книгу «Гарри Поттер и дары смерти» в магазинах города. Пусть в ячейках B 3: B 7 внесены значения цены (рис. 4). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию СРЗНАЧ . Укажите исходные данные и нажмите OK .
Рис. 4. Нахождение среднего значения среди аргументов
Подсчет количества значений в списке аргументов осуществляется с помощью функции СЧЕТ . Формат записи функции:
Количество допустимых аргументов, среди которых находится среднее значение, равно 255.
Пример. Найти количество студентов, получающих стипендию. Пусть в ячейках B 3: B 7 внесены сведения о стипендии студентов группы (рис.5). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию СЧЕТ . Укажите исходные данные и нажмите OK . В ячейке В9 будет найдено искомое значение.
Рис. 5. Нахождение количества числовых значений среди аргументов
Нахождение количества значений, удовлетворяющих заданному условию, выполняется с помощью функции СЧЕТЕСЛИ . Формат записи функции:
где диапазон – диапазон, в котором подсчитывается количество непустых ячеек ;
критерий – проверяемое условие в заданном интервале (в форме числа, выражения, текста).
=СЧЕТЕСЛИ(А1:А9; 85) – подсчитывает, сколько раз число 85 встречается в интервале А1:А9;
=СЧЕТЕСЛИ(А1:А9; “>85”) – подсчитывает, сколько раз в интервале А1:А9 встречаются числа, большие 85;
=СЧЕТЕСЛИ(А1:А9; “высший”) – подсчитывает, сколько раз в интервале А1:А9 встречается слово «высший»;
=СЧЕТЕСЛИ(А1:А9; “в*”) – подсчитывает, сколько раз в интервале А1:А9 встречаются слова, начинающиеся на букву «в».
Обратите внимание на то, что если в качестве критерия указываются не числовые значения, а текст или символы, то они заключаются в кавычки.
Определение ранга (номера позиции) числа в списке других чисел (т.е. порядкового номера относительно других чисел списка) выполняется с помощью функции РАНГ. РВ . Формат записи функции:
где число – число, для которого определяется ранг (порядковый номер);
ссылка – массив или ссылка на список чисел, с которым сравнивается число ;
порядок – число (0 либо отличное от 0), определяющее способ ранжирования (в порядке убывания или возрастания).
Пример. Используем функцию РАНГ.РВ , которая присвоит номер места каждой марке автомобиля в зависимости от определенного параметра. Пусть в ячейки В3:В8 занесены значения расхода топлива на 100 км пробега (рис.6). Наилучшим будем считать автомобиль, имеющий минимальный расход. В ячейку С3 занесем формулу
Рис. 22. Нахождение ранга числа в порядке возрастания значений
Рис.7. Нахождение ранга числа в порядке убывания значений
Для ввода в смежные ячейки повторяющейся с определенной закономерностью информации (текст, даты, числа), т.е. построения рядов данных, существует несколько способов.
Использование маркера заполнения и перетаскивания ячеек.
Пусть необходимо построить ряд чисел от 1 до 5,5 с шагом 0,5, т.е. получить арифметическую прогрессию. Для этого:
в окне открытого листа введите данные в первую ячейку диапазона (рис.8);
наведите курсор мыши на правый нижний угол ячейки (там располагается маркер заполнения) и, когда курсор станет тонким черным крестом, при нажатой левой кнопке мыши протащите маркер заполнения вниз по столбцу;
в конце нужного диапазона отпустите левую кнопку мыши. Оцените результат.
В данном случае в новые ячейки заносятся соответствующие значения, а также форматы исходной ячейки.
Рис.8. Использование маркера заполнения для получения арифметической прогрессии
Если после ввода первых двух значений потянуть за маркер заполнения при нажатой клавише , то будет реализован принцип автозаполнения, а не получение арифметической прогрессии, и во всех ячейках получится чередование чисел 1 и 1,5 (рис.9).
Рис.9. Использование маркера заполнения для автозаполнения ячеек
Использование команды Прогрессия .
выберите команду Прогрессия …, находящуюся в группе Редактирование вкладки Главная (рис.10), которая позволяет заполнить ряд соответствующим образом;
Рис.10. Команда Прогрессия для заполнения рядов данных
в появившемся диалоговом окне установите параметры, как показано на рис.11.
по условию задачи, каждое следующее число отличается от предыдущего на 0,5, поэтому для построения ряда чисел воспользуемся формулой: =А1+0,5, которую внесем в ячейку А2 и с помощью маркера скопируем по столбцу вниз (рис.12).
Рис.12. Построение ряда чисел с использованием формулы
наведите курсор мыши на правый нижний угол ячейки и, когда курсор станет тонким черным крестом, при нажатой правой кнопке мыши протащите маркер заполнения, верх или вниз по столбцу либо вправо или влево по строке;
в конце нужного диапазона отпустите правую кнопку мыши;
«Копировать ячейки» – будут копироваться и значения, и форматы исходной ячейки;
«Заполнить только форматы» – будет копироваться только формат исходной ячейки;
«Заполнить только значения» – будет копироваться только значение исходной ячейки.
Для выполнения лабораторной работы введите исходные данные в соответствии с выданным номером задания. Отформатируйте таблицу по приведенным ниже параметрам.
Заголовки столбцов таблицы ( № п/п; ФИО студента; Рост (см); Вес (кг)… и т.д. в зависимости от варианта задания ) – по центру, полужирным шрифтом, размер шрифта – 10 пт). Возможность отображать текст внутри ячейки таблицы в несколько строк добивается следующим образом:
выделить ячейки A 3: G 3, формат которых требуется изменить;
в контекстном меню выберите команду Формат ячеек и в открывшемся диалоговом окне выберите: вкладка Выравнивание →область Выравнивание →по горизонтали – по центру; по вертикали — по центру;
в области Отображение установить флажок переносить по словам .
Ячейки А4:А13 заполните значениями от 1 до 10 одним из способов, описанных в п.1.2 «Построение рядов данных».
К тексту ячеек B 15: B 22 примените начертание курсив и сделайте перенос по словам.
Выделите ячейки A1:G13 таблицы. С помощью кнопки Границы → Все границы группы Шрифт измените границы таблицы.
Символ, соответствующий степени 2 числа, можно вставить с помощью команды Символ группы Символы вкладки Вставка . Другой способ указания символа степени: написать степень числа, выделить его, нажать кнопку группы Шрифт и в появившемся диалоговом окне во вкладке Шрифт в области Видоизменение установить флажок надстрочный .
Задание. Для данной группы студентов определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.13. На вашем рабочем листе цвет ячеек изменять не обязательно):
ранг студентов (порядковый номер относительно друг друга) в беге на 100 м.
Рис.13. Исходные данные для выполнения лабораторной работы
Задание. Для данной группы продуктов определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.14. На вашем рабочем листе цвет ячеек изменять не обязательно):
количество продуктов, название которых начинается на букву «м»;
количество продуктов, название которых начинается на букву «к»;
количество продуктов, ассортимент которых обновлялся;
ранг продуктов магазина «Рублик» (порядковый номер относительно стоимости друг друга).
Рис.14. Исходные данные для выполнения лабораторной работы
Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.15. На вашем рабочем листе цвет ячеек изменять не обязательно):
среднее значение стоимости стрижек в парикмахерской «Люкс»;
средняя стоимость других услуг (отличных от стрижек) парикмахерской «Люкс»;
ранг стоимости услуг парикмахерской «Аванта» (порядковый номер стоимости относительно друг друга).
Рис.15. Исходные данные для выполнения лабораторной работы
Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.16. На вашем рабочем листе цвет ячеек изменять не обязательно):
минимальное значение цен на услуги компаний сотовой связи;
максимальное значение цен на услуги компаний сотовой связи;
среднее значение цен на услуги компаний сотовой связи;
средняя стоимость других услуг (отличных от звонков) оператора «МТС»;
ранг стоимости услуг оператора «МТС» (порядковый номер стоимости относительно друг друга).
Рис.16. Исходные данные для выполнения лабораторной работы
Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.17. На вашем рабочем листе цвет ячеек изменять не обязательно):
минимальное значение населения, площади территории страны, количества городов-миллионеров;
максимальное значение населения, площади территории страны, количества городов-миллионеров;
среднее значение населения, площади территории страны, количества городов-миллионеров;
количество стран, берега которых омываются океанами;
ранг стран по площади территории (порядковый номер страны относительно значений площадей).
Рис.17. Исходные данные для выполнения лабораторной работы
Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.18. На вашем рабочем листе цвет ячеек изменять не обязательно):
минимальное значение населения, площади территории страны, количества городов-миллионеров;
максимальное значение населения, площади территории страны, количества городов-миллионеров;
среднее значение населения, площади территории страны, количества городов-миллионеров;
ранг стран по населению (порядковый номер страны относительно значений количества человек).
Рис.18. Исходные данные для выполнения лабораторной работы
Задание. Для данной группы услуг определить (искомые значения разместить в соответствующих выделенных ячейках, как показано на рис.19. На вашем рабочем листе цвет ячеек изменять не обязательно):
минимальное значение длины реки, площади бассейна реки и количества крупных городов, где эти реки протекают;
максимальное значение длины реки, площади бассейна реки и количества крупных городов;
среднее значение длины рек, площади бассейна и количества крупных городов;
ранг рек по длине (порядковый номер реки относительно значений длины).
Рис.19. Исходные данные для выполнения лабораторной работы
Назовите известные вам функции из категорий Статистические и их аргументы.
Сколько аргументов могут иметь функции МИН и МАКС ?
Каковы отличия функций СЧЕТ и СЧЕТЕСЛИ . Назовите аргументы этих функций.
С какой целью в функции РАНГ . РВ используется абсолютная адресация ячеек?
Самостоятельно выясните назначение и работу функций НАИМЕНЬШИЙ , НАИБОЛЬШИЙ , ТЕНДЕНЦИЯ категории Статистические , используя справку по каждой из них. Приведите примеры.
ЛАБОРАТОРНАЯ РАБОТА «Статистические функции MS Excel 2013. Построение рядов данных»
Посмотрим, как рассчитать границы доверительного интервала с неизвестным стандартным отклонением на примере всё той же совокупности, что мы рассматривали в предыдущем способе. Уровень доверия, как и в прошлый раз, возьмем 97%.
Интервал в excel формула
Процедура вычисления
Этот метод используется при интервальной оценке различных статистических величин. Главная задача данного расчета – избавится от неопределенностей точечной оценки.
В Экселе существуют два основных варианта произвести вычисления с помощью данного метода: когда дисперсия известна, и когда она неизвестна. В первом случае для вычислений применяется функция ДОВЕРИТ.НОРМ, а во втором — ДОВЕРИТ.СТЮДЕНТ.
Способ 1: функция ДОВЕРИТ.НОРМ
Оператор ДОВЕРИТ.НОРМ, относящийся к статистической группе функций, впервые появился в Excel 2010. В более ранних версиях этой программы используется его аналог ДОВЕРИТ. Задачей этого оператора является расчет доверительного интервала с нормальным распределением для средней генеральной совокупности.
«Альфа» — аргумент, указывающий на уровень значимости, который применяется для расчета доверительного уровня. Доверительный уровень равняется следующему выражению:
«Стандартное отклонение» — это аргумент, суть которого понятна из наименования. Это стандартное отклонение предлагаемой выборки.
Все аргументы данного оператора являются обязательными.
Функция ДОВЕРИТ имеет точно такие же аргументы и возможности, что и предыдущая. Её синтаксис таков:
Как видим, различия только в наименовании оператора. Указанная функция в целях совместимости оставлена в Excel 2010 и в более новых версиях в специальной категории «Совместимость». В версиях же Excel 2007 и ранее она присутствует в основной группе статистических операторов.
Граница доверительного интервала определяется при помощи формулы следующего вида:
Где X – это среднее выборочное значение, которое расположено посередине выбранного диапазона.
Теперь давайте рассмотрим, как рассчитать доверительный интервал на конкретном примере. Было проведено 12 испытаний, вследствие которых были получены различные результаты, занесенные в таблицу. Это и есть наша совокупность. Стандартное отклонение равно 8. Нам нужно рассчитать доверительный интервал при уровне доверия 97%.
Появляется Мастер функций. Переходим в категорию «Статистические» и выделяем наименование «ДОВЕРИТ.НОРМ». После этого клацаем по кнопке «OK».
Открывается окошко аргументов. Его поля закономерно соответствуют наименованиям аргументов.
Устанавливаем курсор в первое поле – «Альфа». Тут нам следует указать уровень значимости. Как мы помним, уровень доверия у нас равен 97%. В то же время мы говорили, что он рассчитывается таким путем:
Значит, чтобы посчитать уровень значимости, то есть, определить значение «Альфа» следует применить формулу такого вида:
Путем нехитрых расчетов узнаем, что аргумент «Альфа» равен 0,03. Вводим данное значение в поле.
Как известно, по условию стандартное отклонение равно 8. Поэтому в поле «Стандартное отклонение» просто записываем это число.
Появляется список недавно применяемых функций. Если оператор СЧЁТ применялся вами недавно, то он должен быть в этом списке. В таком случае, нужно просто кликнуть по его наименованию. В обратном же случае, если вы его не обнаружите, то переходите по пункту «Другие функции…».
Появляется уже знакомый нам Мастер функций. Опять перемещаемся в группу «Статистические». Выделяем там наименование «СЧЁТ». Клацаем по кнопке «OK».
Появляется окно аргументов вышеуказанного оператора. Данная функция предназначена для того, чтобы вычислять количество ячеек в указанном диапазоне, которые содержат числовые значения. Синтаксис её следующий:
Группа аргументов «Значения» представляет собой ссылку на диапазон, в котором нужно рассчитать количество заполненных числовыми данными ячеек. Всего может насчитываться до 255 подобных аргументов, но в нашем случае понадобится лишь один.
Устанавливаем курсор в поле «Значение1» и, зажав левую кнопку мыши, выделяем на листе диапазон, который содержит нашу совокупность. Затем его адрес будет отображен в поле. Клацаем по кнопке «OK».
После этого приложение произведет вычисление и выведет результат в ту ячейку, где она находится сама. В нашем конкретном случае формула получилась такого вида:
Но это ещё не все. Как мы помним, граница доверительного интервала вычисляется путем сложения и вычитания от среднего выборочного значения результата вычисления ДОВЕРИТ.НОРМ. Таким способом рассчитывается соответственно правая и левая граница доверительного интервала. Само среднее выборочное значение можно рассчитать при помощи оператора СРЗНАЧ.
Данный оператор предназначен для расчета среднего арифметического значения выбранного диапазона чисел. Он имеет следующий довольно простой синтаксис:
Аргумент «Число» может быть как отдельным числовым значением, так и ссылкой на ячейки или даже целые диапазоны, которые их содержат.
Итак, выделяем ячейку, в которую будет выводиться расчет среднего значения, и щелкаем по кнопке «Вставить функцию».
Открывается Мастер функций. Снова переходим в категорию «Статистические» и выбираем из списка наименование «СРЗНАЧ». Как всегда, клацаем по кнопке «OK».
Запускается окно аргументов. Устанавливаем курсор в поле «Число1» и с зажатой левой кнопкой мыши выделяем весь диапазон значений. После того, как координаты отобразились в поле, клацаем по кнопке «OK».
После этого СРЗНАЧ выводит результат расчета в элемент листа.
Производим расчет правой границы доверительного интервала. Для этого выделяем отдельную ячейку, ставим знак «=» и складываем содержимое элементов листа, в которых расположены результаты вычислений функций СРЗНАЧ и ДОВЕРИТ.НОРМ. Для того, чтобы выполнить расчет, жмем на клавишу Enter. В нашем случае получилась следующая формула:
Таким же образом производим вычисление левой границы доверительного интервала, только на этот раз от результата вычисления СРЗНАЧ отнимаем результат вычисления оператора ДОВЕРИТ.НОРМ. Получается формула для нашего примера следующего типа:
Мы попытались подробно описать все действия по вычислению доверительного интервала, поэтому детально расписали каждую формулу. Но можно все действия соединить в одной формуле. Вычисление правой границы доверительного интервала можно записать так:
Аналогичное вычисление левой границы будет выглядеть так:
Способ 2: функция ДОВЕРИТ.СТЮДЕНТ
Как видим, наименования операторов и в этом случае остались неизменными.
Посмотрим, как рассчитать границы доверительного интервала с неизвестным стандартным отклонением на примере всё той же совокупности, что мы рассматривали в предыдущем способе. Уровень доверия, как и в прошлый раз, возьмем 97%.
В открывшемся Мастере функций переходим в категорию «Статистические». Выбираем наименование «ДОВЕРИТ.СТЮДЕНТ». Клацаем по кнопке «OK».
Производится запуск окна аргументов указанного оператора.
В поле «Альфа», учитывая, что уровень доверия составляет 97%, записываем число 0,03. Второй раз на принципах расчета данного параметра останавливаться не будем.
После этого устанавливаем курсор в поле «Стандартное отклонение». На этот раз данный показатель нам неизвестен и его требуется рассчитать. Делается это при помощи специальной функции – СТАНДОТКЛОН.В. Чтобы вызвать окно данного оператора, кликаем по треугольнику слева от строки формул. Если в открывшемся списке не находим нужного наименования, то переходим по пункту «Другие функции…».
Запускается Мастер функций. Перемещаемся в категорию «Статистические» и отмечаем в ней наименование «СТАНДОТКЛОН.В». Затем клацаем по кнопке «OK».
Открывается окно аргументов. Задачей оператора СТАНДОТКЛОН.В является определение стандартного отклонения при выборке. Его синтаксис выглядит так:
Нетрудно догадаться, что аргумент «Число» — это адрес элемента выборки. Если выборка размещена единым массивом, то можно, использовав только один аргумент, дать ссылку на данный диапазон.
Устанавливаем курсор в поле «Число1» и, как всегда, зажав левую кнопку мыши, выделяем совокупность. После того, как координаты попали в поле, не спешим жать на кнопку «OK», так как результат получится некорректным. Прежде нам нужно вернуться к окну аргументов оператора ДОВЕРИТ.СТЮДЕНТ, чтобы внести последний аргумент. Для этого кликаем по соответствующему наименованию в строке формул.
Попав в окно аргументов СЧЁТ, ставим курсор в поле «Число1» и с зажатой кнопкой мыши выделяем совокупность. Затем клацаем по кнопке «OK».
После этого программа производит расчет и выводит значение доверительного интервала.
Для определения границ нам опять нужно будет рассчитать среднее значение выборки. Но, учитывая то, что алгоритм расчета при помощи формулы СРЗНАЧ тот же, что и в предыдущем способе, и даже результат не изменился, не будем на этом подробно останавливаться второй раз.
Сложив результаты вычисления СРЗНАЧ и ДОВЕРИТ.СТЮДЕНТ, получаем правую границу доверительного интервала.
Отняв от результатов расчета оператора СРЗНАЧ результат расчета ДОВЕРИТ.СТЮДЕНТ, имеем левую границу доверительного интервала.
Если расчет записать одной формулой, то вычисление правой границы в нашем случае будет выглядеть так:
Соответственно, формула расчета левой границы будет выглядеть так:
Как видим, инструменты программы Excel позволяют существенно облегчить вычисление доверительного интервала и его границ. Для этих целей используются отдельные операторы для выборок, у которых дисперсия известна и неизвестна.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Программа Эксель используется для выполнения различных статистических задач, одной из которых является вычисление доверительного интервала, который применяется как наиболее подходящая замена точечной оценки при малом объеме выборки.
Хотим сразу заметить, что сама процедура вычисления доверительного интервала довольно непростая, однако, в Excel существует ряд инструментов, призванных облегчить выполнение данной задачи. Давайте рассмотрим их.
Вычисление доверительного интервала
Доверительный интервал нужен для того, чтобы дать интервальную оценку каким-либо статическим данным. Основная цель этой операции – убрать неопределенности точечной оценки.
В Microsoft Excel существует два метода выполнения данной задачи:
Метод 1: оператора ДОВЕРИТ.НОРМ
Данная функция впервые была внедрена в арсенал программы в редакции Эксель 2010 года (до этой версии ее заменял оператор “ДОВЕРИТ”). Оператор входит в категорию “статистические”.
Формула функции ДОВЕРИТ.НОРМ выглядит так:
- “Альфа” – это показатель уровня значимости, который берется за основу при расчете. Доверительный уровень считается так:
- 1-«Альфа» . Это выражение применимо в случае, если значение “Альфа” представлено в виде коэффициента. Например, 1-0,7=0,3, где 0,7=70%/100%.
- (100-«Альфа»)/100 . Применятся это выражение, если мы считаем доверительным уровень со значением “Альфа” в процентах. Например, (100-70)/100=0,3.
Примечание: У данной функции наличие всех трех аргументов является обязательным условием.
Оператор “ДОВЕРИТ”, который применялся в более ранних редакциях программы, содержит такие же аргументы и выполняет те же самые функции.
Формула функции ДОВЕРИТ выглядит следующим образом:
Отличий в самой формуле нет никаких, лишь название оператора иное. В редакциях приложения Эксель 2010 года и последующих этот оператор находится в категории “Совместимость”. В более же старых версиях программы он находится в разделе статических функций.
Граница доверительного интервала определяется следующей формулой:
где Х – это среднее значение по заданному диапазону.
Теперь давайте разберемся, как применять эти формулы на практике. Итак, у нас есть таблица с различными данными 10-ти проведенных замеров. При этом, стандартное отклонение совокупности данных равняется 8.
Перед нами стоит задача – получить значение доверительного интервала с 95%-ым уровнем доверия.
Примечание: В пунктах выше мы постарались максимально подробно расписать все шаги и каждую применяемую функцию. Однако все прописанные формулы можно записать вместе, в составе одной большой:
- Для определения правой границы ДИ общая формула будет выглядеть так:
=СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)) . - Точно также и для левой границы, только вместо плюса нужно поставить минус:
=СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)) .
Метод 2: оператор ДОВЕРИТ.СТЬЮДЕНТ
Теперь давайте познакомимся со вторым оператором для определения доверительного интервала – ДОВЕРИТ.СТЬЮДЕНТ. Данная функция была внедрена в программу относительно недавно, начиная с версии Эксель 2010, и направлена на определение ДИ выбранной совокупности данных с применением распределения Стьюдента, при неизвестной дисперсии.
Формула функции ДОВЕРИТ.СТЬЮДЕНТ выглядит следующим образом:
Давайте разберем применение данного оператора на примере все той же таблицы. Только теперь стандартное отклонение по условиям задачи нам неизвестно.
Заключение
В этой статье описаны синтаксис формулы и использование функции ДОВЕРИТ в Microsoft Excel.
Описание
Возвращает доверительный интервал для среднего генеральной совокупности с нормальным распределением.
Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.
Чтобы узнать больше о новых функциях, см. в разделах Функция ДОВЕРИТ.НОРМ и Функция ДОВЕРИТ.СТЬЮДЕНТ.
Синтаксис
Альфа — обязательный аргумент. Уровень значимости, используемый для вычисления доверительного уровня. Доверительный уровень равен 100*(1 – альфа) процентам или, иными словами, значение аргумента «альфа», равное 0,05, означает 95-процентный доверительный уровень.
Стандартное_откл — обязательный аргумент. Стандартное отклонение генеральной совокупности для диапазона данных, предполагается известным.
Замечания
Если какой-либо из аргументов не является числом, функция доВЕРИТ возвращает #VALUE! значение ошибки #ЧИСЛО!.
Если альфа-канал ≤ 0 или ≥ 1, то функция доВЕРИТ Возвращает #NUM! значение ошибки #ЧИСЛО!.
Если стандартное_откл ≤ 0, то функция доВЕРИТ Возвращает #NUM! значение ошибки #ЧИСЛО!.
Если значение аргумента «размер» не является целым числом, оно усекается.
Если размер _Лт_ 1, то функция доВЕРИТ Возвращает #NUM! значение ошибки #ЧИСЛО!.
Если предположить, что альфа = 0,05, то нужно вычислить область под стандартной нормальной кривой, которая равна (1 – альфа), или 95 процентам. Это значение равно ± 1,96. Следовательно, доверительный интервал определяется по формуле:
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
[expert_bq id=»1570″]Рассмотрим стандартные действия, которые можно выполнять с данными 1 упорядочить строки по возрастанию или убыванию значений в некотором столбце ; 2 искать данные по некоторым критериям. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Диапазонам можно давать названия и использовать эти названия вместо выражений типа А1:А9. Программа сама дает названия диапазонам, если она может их однозначно распознать. Например, в таблице на рис. 1 названия столбцов распознаются автоматически, поэтому в ячейку Е4 вместо формулы = B4+C4+D4 можно ввести формулу = Январь+Февраль+Март.Использование статистических функций EXCEL
- “Альфа” – это показатель уровня значимости, который берется за основу при расчете. Доверительный уровень считается так:
- 1-«Альфа» . Это выражение применимо в случае, если значение “Альфа” представлено в виде коэффициента. Например, 1-0,7=0,3, где 0,7=70%/100%.
- (100-«Альфа»)/100 . Применятся это выражение, если мы считаем доверительным уровень со значением “Альфа” в процентах. Например, (100-70)/100=0,3.
Запускается окно аргументов. Устанавливаем курсор в поле «Число1» и с зажатой левой кнопкой мыши выделяем весь диапазон значений. После того, как координаты отобразились в поле, клацаем по кнопке «OK».
