Функция интервал в excel

Функция интервал в excel

Исследовательская работа связана с необходимостью правильно понимать и обрабатывать экспериментальные данные, оценивать влияние на них систематических и случайных ошибок.

Случайные ошибки устранить нельзя, а также нельзя вывести никакой формулы для исправления полученного результата. В тоже время проведением повторных измерений и статистической обработкой полученных данных можно оценить величину этих ошибок и уменьшить их их влияние.

Microsoft Excel предлагает большое число функций, облегчающих статистическую обработку данных эксперимента.

Наша задача — обработать данные, приведенные в файлах Sil00.xls и Sil1.xls, с помощью этих функций, а также представить результаты эксперимента в графическом виде.

I Запустите Excel, рассмотрите элементы рабочего окна. Откройте рабочие файлы. Скопируйте лист Ошибка из книги Sil00.xls в книгу Sil1.xls перед листом Данные. Сохранить как Фамилия-Sil-1.xls в папке НИТ на рабочем столе (тем самым создаем копию).

Мастер функций (повторение)

Вспомните основные приемы работы с Мастером функций.

Запуск: кнопкой вставки функции . (или командой меню Вставка-Функция).

Выбор нужной функции. Все функции разделены на несколько категорий. На данном занятии в основном будут использоваться статистические функции. Выбрав нужную категорию функций, в списке функций (упорядоченном по алфавиту) находят нужную функцию. При щелчке по названию любой функции внизу появляется ее краткое описание. Специальная ссылка позволяет вызвать систему помощи Excel, в которой данная функция будет разобрана подробно, с примерами.

Задание аргументов функции. Аргументами могут служить непосредственно данные или адреса ячеек, диапазонов ячеек, содержащих необходимые данные. Как правило, все исходные данные для функции должны быть на листе и вводят (указывая мышкой) адреса ячеек, содержащих эти значения. Обратите внимание, что окно аргументов можно перемещать, если оно заслоняет нужную часть экрана. Кроме того, рядом с полем для ввода есть маленькая кнопка с красной стрелочкой. При щелчке по ней окно аргументов сворачивается до узкой полоски, новый щелчок опять развернет окно аргументов. Помогает работе с мастером функций подсказка под полем для ввода аргументов, в которой разъясняется их смысл и возможные значения. Когда аргументы введены, в окне появляется значение функции, что тоже помогает избежать ошибок. Заканчивается работа с мастером функций нажатием кнопки "Ok" или клавиши "Enter".

=> Особенности Office-2007.
Для работы с функциями можно использовать вкладку Формулы, где представлена библиотека функций, разбитая на категории. Можно прийти к вставке функций и через значок Автосумма, выбрав там команду Другие функции.
Еще один способ — значок fx, рядом со строкой формул.

I Запустите Мастер функций. Перейдите на категорию статистических функций, и просмотрите их список. Найдите функции СЧЕТ, разберитесь, используя подсказку, чем отличаются разные варианты этой функции.

Счет значений

Для получения количества числовых ячеек в интервалах или массивах ячеек используется функция СЧЁТ. Использовать эту функцию удобно, особенно при большом числе данных, при их отбраковке.

Синтаксис: СЧЁТ(значение1; значение2; . ) где
Значение1, значение2, . — это числа, адреса ячеек и диапазонов (до 30 аргументов). Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

Функцию СЧЁТ можно вызвать через список функций значка АВТОСУММА (команда Число).

I Лист Ошибка. В ячейке B14 введите формулу для подсчета числа экспериментальных точек. Проверьте работу формулы, удаляя/возвращая одно из данных (кнопка Отменить).

Среднее значение

Функция СРЗНАЧ возвращает среднее арифметическое аргументов.

Синтаксис СРЗНАЧ(значение1; значение2; . ), где
— значение1, значение2, . — это числа, адреса ячеек и диапазонов (до 30 аргументов), для которых вычисляется среднее. Пустые ячейки, логические значения, тексты и значения ошибок игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

Для подсчета среднего значения можно также использовать функцию УРЕЗСРЕДНЕЕ, которая вычисляет урезанное среднее, отбрасывая заданный процент данных с экстремальными значениями, чтобы исключить из анализа грубые ошибки, так называемые выбросы.

Синтаксис: УРЕЗСРЕДНЕЕ(массив;доля), где
— массив — это массив или интервал усредняемых значений. — доля — это доля точек данных, исключаемых из вычислений. Например, если доля = 0,2, то из множества данных, содержащих 25 точек исключаются 4 точки (25*0,2= 5, но из соображений симметрии функция исключит 2 точки с наибольшими значениями и 2 точки с наименьшими значениями). Если доля 1, то функция возвращает ошибку #ЧИСЛО!.

I Лист Ошибка. В ячейках B15 и B16 введите формулу для среднего и урезанного среднего (доля 20%) значения. Проверьте работу формулы, удаляя/возвращая одно из данных (кнопка Отменить)

Разброс точек. Дисперсия и стандартное отклонение

Разброс экспериментальных данных, оценивается дисперсией по выборке, которое определяется функцией ДИСП или стандартным отклонением (функция СТАНДОТКЛОН)

Синтаксис: ДИСП(число1;число2; . ), или СТАНДОТКЛОН(число1; число2; . )
где число1, число2, . — это до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются.

ДИСП и СТАНДОТКЛОН используют формулы:

Стандартное (среднеквадратичное) отклонение — это мера того, насколько широко разбросаны точки данных относительно друг друга. Каждое измерение в данной серии (в данной выборке) отличается от другого в среднем на s.

Понятно, что каждое отдельное значение оказывает влияние на средний результат. чем больше измерений в нашей выборке.
Поэтому дисперсия и стандартное отклонение среднего значения, будет определяться по формулам:

I Лист Ошибка. В ячейках B17, B18 введите формулы для дисперсии и стандартного отклонения выборки, в ячейках B19, B20 — формулы для дисперсии и стандартного отклонения среднего значения. Сохраните файл.

Осваиваем Excel: Основные приемы работы

Критерий Стьюдента, доверительный интервал

Для определения доверительного интервала, в который с заданной надежностью попадает измеряемая величина, используется критерий Стюдента t( a;f). Он был предложен в 1908 году англиским математиком и химиком Госсетом, который опубликовал их в работе под псевдонимом Student (студент). Для нахождения критерия Стюдента используется функция СТЬЮДРАСПОБР( a;f) .

Синтаксис: СТЬЮДРАСПОБР(альфа;степень_свободы), где
— альфа — это вероятность ошибки, используемая для вычисления уровня надежности (Р). Уровень надежности определяется как Р = 1 — a, или, другими словами, a равная 0,05 означает 95% уровень надежности.
— степень_свободы — это число степеней свободы, характеризующее распределение. Для среднего значения степень свободы f = n-1.

Функция СТЬЮДРАСПОБР использует метод итераций для вычисления результата. Если СТЬЮДРАСПОБР не сходится после 100 итераций, то функция возвращает значение ошибки #Н/Д (не достижимо).

Доверительный интервал среднего рассчитывается по формуле ДИ = ± sср*t( a;f) где sср — стандартное отклонение среднего.
Окончательно получаем X = Xср ± sср*t( a;f)

Если число измерений велико, можно использовать доверительный интервал для среднего генеральной совокупности, который находится с помощью функции ДОВЕРИТ, однако эта функция дает заниженные результаты при небольшом числе измерений.
Синтаксис: ДОВЕРИТ(альфа; станд_откл; n)

I Лист Ошибка. Введите формулы для расчета коэффициента Стьюдента и доверительного интервала (двумя способами). Рассчитайте относительную ошибку. Снесите окончательные результаты (формулами ссылок) в ячейки С26 и Е26. Отформатируйте их до оптимального числа значащих цифр.

Линейная регрессия

Коэффициенты линейной регрессии Y = mX+b и их ошибки вычисляются по довольно сложным формулам (рассмотренным на прошлом занятии). Excel предлагает ряд функций, облегчающих эти расчеты.

Коэффициент m (наклон) линии, описываемой уравнением Y = mX+b может быть получен при помощи статистической функции НАКЛОН.

Коэффициент b, показывающий отрезок, отсекаемый линией Y = mX+b на оси Y, может быть определен с помощью функции ОТРЕЗОК.

Коэффициент корреляции Пирсона R, который отражает степень линейной зависимости между двумя множествами данных, и его квадрат (коэффициент детерминации) может быть получен с помощью функций ПИРСОН и КВПИРСОН.
Если R 2 равен 1, то имеет место полная корреляция с линейной моделью, т.е. нет различия между фактическим и оценочным значениями Y. В противоположном случае, если коэффициент равен 0, то уравнение линейной регрессии не применимо для предсказания значений Y.

Синтаксис:
НАКЛОН(известные_значения_y; известные_значения_x)
ОТРЕЗОК (известные_значения_y; известные_значения_x)
КВПИРСОН (известные_значения_y; известные_значения_x)

I Лист МНК. Введите формулы для рассчета коэффициентов линейной регрессиии и коэффициента корреляции R 2 для зависимости Y(dG) от Х(T). Сравните с данными полученными ранее, оцените приемлемость линейной модели.

Полная статистика линейной регрессии. Формулы массива

Наиболее полную статистическую информацию по линейной регрессии выдает функция ЛИНЕЙН. Кроме того, эта функция позволяет обрабатывать данные по линейным функциям нескольких переменных (многофакторный эксперимент). Поскольку функция возвращает не одно значение, а несколько, массив значений, она относится к формулам массива. Ввод этих формул имеет ряд особенностей:

  • Начинать ввод следует с выделения массива ячеек, куда будут выводиться данные. Для функции ЛИНЕЙН это прямоугольник N*5, где N — число коэффициентов в уравнении.
  • Заканчивать ввод функции следует сочетанием клавиш Ctrl-Shift-Enter (или, удерживая нажатыми клавиши Ctrl-Shift щелкнуть мышкой Ok).

Синтаксис: ЛИНЕЙН(извест_знач_y; извест_знач_x; конст; статистика)
Константа определяет, нужно ли рассчитывать отрезок b, отсекаемый линией на оси OY. Если из теоретических представлений известно, что линия проходит через начало координат и отрезок b, отсекаемый ею на оси OY равен 0, то вводится значение константы ЛОЖЬ (или 0). В противном случае вводится значение ИСТИНА (1), и отрезок b будет вычислен из массива данных.
Еще одно логическое значение Стат указывает, требуется ли вернуть полную статистику по регрессии (ИСТИНА) или нет (ЛОЖЬ) В результате правильного ввода функции в выделенных ячейках получаем таблицу данных (пример в таблице 1):

В ячейке Е7 находится коэффициент m (наклон) линии, в ячейке F7 — коэффициент b (отрезок);
В ячейках E8 и F8 приведены стандартные значения ошибок для коэффициентов m и b. (sm, sb) Стандартные значения ошибок могут быть использованы для оценки значимости коэффициентов регрессии.
В ячейке Е9 — квадрат коэффициента корреляции Пирсона (R 2 ) В ячейке F9 — cтандартная ошибка для оценки Y (sy).
В ячейке Е10 выводится F-статистика, используемая для определения адекватности модели. В ячейке F10 представлено число степеней свободы (f). Число степеней свободы используются для нахождения F-критических значений в статистической таблице. Оно находится по формуле f = n — (k + 1), где k — это число независимых переменных, а n — число точек данных. В нашем случае f = 10 — (1 + 1) = 8
В ячейках Е11 и F11 представлены, соответственно, регрессионная сумма квадратов и остаточная сумма квадратов.

I Еще раз прочитайте как вводятся формулы массива.
Лист МНК. Выделите нужное число ячеек и введите формулу массива для получения линейной статистики зависимости Y(dG) от Х(T). Сравните с данными полученными ранее. Введите подсказки к ячейкам полученных данных, используя вставку Примечаний.

Адекватность модели

Для определения уровня надежности модели нужно сравнить значения специального F( a;f1;f2) и F-статистики, возвращаемой функцией ЛИНЕЙН. Для определения используется функция FРАСПОБР( a;f1; f2), где
— a вероятность ошибочного вывода (обычно применяется значение равное 0,05), а f1 и f2 — число степеней свободы, причем f1 = k = 1, где k — число независимых переменных в уравнении модели, а f2 равно числу степеней свободы, данное в таблице результатов функции ЛИНЕЙН.
Если наблюдаемое F >> F( a;f1;f2), то полученное регрессионное уравнение адекватно.

I Лист МНК. Найдите F-критерий и определите адекватность линейной модели.

Графики: построение

Одной из важных и полезных возможностей Excel является возможность построения разнообразных графиков и диаграмм. В практике химического эксперимента чаще всего исследуется зависимость одной величины от другой. Такая зависимость может быть представлена точечным графиком.

Построение графика.

  1. Выделите массивы ячеек, содержащих значения Х и Y. (Несмежные области выделяют, используя клавишу Ctrl).
  2. Щелкните на значке Мастера диаграмм панели инструментов. На первом этапе его работы определите тип диаграммы (Точечная) и ее вид только точки, точки и линии, только линии и т.п.
  3. На следующем этапе можно примерно увидеть будущий график. Если нужно, то на вкладке Ряд можно удалить или добавить ряды данных зависящих от Х.
  4. Следующий этап позволяет ввести различные надписи, определить необходимость и расположение легенды, линий сетки.
  5. На последнем этапе определяем, будет ли для графика создан отдельный Лист и нажимаем кнопку Готово
=> Особенности Office-2007.
— В Excel-2007 нет мастера диаграмм. Первый этап выполняется с вкладки Вставка. При этом на листе сразу появляется выбранный тип диаграммы.

I Лист МНК. Постройте точечный график зависимости Y(dG) от Х(T), поместив его на лист Результат.

Редактирование графика

Полученный график не всегда сразу имеет удовлетворительный вид. Однако Excel позволяет в любой момент изменять его характеристики.

Прежде всего, можно пермещать график, изменять размеры графика, как и любого графического объекта. Для этого его надо выделить и растянуть/уменьшить до требуемой величины.

Щелкая правой кнопкой мыши по различным элементам графика (области построения, осям, линиям, точкам, надписям), мы открываем контекстное меню, позволяющее переходить к изменению их вида или удалению. Так можно изменить тип диаграммы, массивы данных, шкалы и подписи осей, цвета линий и заливки и т.д. Некоторые параметры диаграммы можно изменять, выделяя нужные элементы (или всю диаграмму) и используя кнопки на панели форматирования.

=> Особенности Office-2007.
При щелчке на диаграмме появляются три дополнительные вкладки Ленты, предназначенные для работы с диаграммами. На вкладке Конструктор можно изменить тип диаграммы, определить исходные данные, если они не были выделены предварительно, выбрать макет, стиль и размещение диаграммы. Вкладка Макет позволяет проводить более детальную работу с элементами диаграммы (Легенда, Название, Подписи, Сетка, Оси и т.д.) Вкладка Формат предоставляет дополнительные возможности форматирования объектов диаграммы.

I Лист Результат. Отредактируйте график, добавьте название, установите оптимальные шкалы и форматы чисел, выделите маркеры экспериментальных точек синим цветом, используйте градиентную заливку. Сохраните файл.

Линия тренда и дополнительные данные

Еще одной возможностью получить параметры линейной зависимости является построение линии тренда. Для этого щелкают правой кнопкой по точке графика и выбирают команду Добавить линию тренда.

На вкладках этой команды выбираем тип тренда (линейный) и его параметры. В частности, отмечаем Выводить уравнение, Выводить коэффициент R^2.

Открыв через контекстное меню точки окно "Параметры рядов данных", на график также можно нанести планки погрешностей, используя значение ошибок в определении величины Y. В качестве величины ошибки может быть использовано значение ДИ рассчитанное для одной из точек или cтандартная ошибка для оценки Y (sy), найденая функцией ЛИНЕЙН.

=> Особенности Office-2007.
Линии тренда и планки погрешностей задаются на вкладке Макет, в разделе Анализ.

I Лист Результат. Отредактируйте график, добавьте на него линию тренда и ее параметры, а также планки погрешностей. Сравните коэффициенты уравнения тренда с ранее найденными значениями. Сохраните файл.

Рубрика: Информационные технологии

Дата публикации: 09.12.2013 2013-12-09

Статья просмотрена: 8274 раза

Библиографическое описание:

Бильфельд Н. В. Особенности вычисления временных интервалов в Excel // Молодой ученый. — 2014. — №1. — С. 59-62. — URL https://moluch.ru/archive/60/8610/ (дата обращения: 23.03.2020).

Рассмотрены различные варианты определения количества полных лет, месяцев и дней между датами с помощью формул Excel, макрокоманд Excelи недокументированных возможностей Excel. Приведен метод определения високосного года. Описаны параметры функций ДОЛЯГОДА() и РАЗНДАТ()

Во многих задачах, таких как «Определить дату очередной прививки ребенку» или «Расчет отработанного стажа» необходимо определить, сколько полных лет, месяцев и дней исполнилось человеку на указанную дату. Автоматизировать данный процесс можно, используя таблицы ExcelДля определения количества лет можно использовать функцию ДОЛЯГОДА (Начальная дата; Конечная дата; Базис), которая возвращает долю года, между двумя указанными датами, где базис — используемый способ вычисления дня. Значения базисов приведены в таблице 1

Значения базисов в функции «ДОЛЯГОДА»

Способ вычисления дня

Американский (NASD) 30/360

Например, если начальная дата (дата на которую необходимо произвести расчет) составляет 01.07.2011, а конечная дата (дата рождения) составляет 08.03.2010, то результатом функции будет 1,31506849315068. Целая часть данного числа и даст количество полных лет. Количество месяцев, можно получить, как остаток данного числа, умноженное на 12. В результате получим 0,31506849315068*12=3,78082191780822 или 3 полных месяца. Приблизительное значение дней получаем как остаток от месяцев, умноженный на 30. т. е. 0,78082191780822 *30=23,4246575342465.

В результате получаем таблицу, приведенную на рисунке 1

Рис.1. Вычисление полных лет и месяцев с приближенным вычислением дней

В ячейку С5 поместим формулу

В ячейку D5 поместим формулу

В ячейку E4 поместим формулу

Если необходимо определить точное количество дней, то в последней формуле необходимо производить умножение на количество дней конкретного месяца, присутствующего в дате, введенной в ячейку B1

Определить количество дней в месяце конкретной даты с помощью формул Excel сложнее. Готовой такой формулы нет. Тем более, если месяц окажется февралем, то все будет зависеть еще и от года (високосный или нет).

Рис.2. Вычисление полных лет и месяцев с точным вычислением дней

Попробуем последовательно решить эту задачу.

Создадим таблицу, приведенную на рисунке 2.

В ячейку B2 поместим формулу =ГОД(B1)

В ячейку B3 поместим формулу =МЕСЯЦ(B1)

Чтобы определить, является ли год високосным, необходимо выполнить следующий алгоритм:

1. Если год делится на 4, перейдите к шагу 2. В противном случае перейдите к шагу 5.

2. Если год делится на 100, перейдите к шагу 3. В противном случае перейдите к шагу 4.

3. Если год делится на 400, перейдите к шагу 4. В противном случае перейдите к шагу 5.

4. Год является високосным (366 дней).

5. Год не является високосным (365 дней).

В связи с этим в ячейку B4 поместим формулу:

Формула возвращает единицу, если год високосный и ноль если нет.

В ячейку B5 поместим формулу:

Формула возвращает одно из значений 31, 30 или 28 в зависимости от месяца в дате. Остается правильно учесть дни в феврале в зависимости от года. Для этого в ячейку B6 поместим формулу: =ЕСЛИ(B5=28;B5+B4;B5) Формула будет возвращать количество дней в месяце даты с учетом года.

И последнее, что можно сделать, это объединить все формулы, по вычислению дней в месяце даты в одну формулу.

Создадим таблицу, приведенную на рисунке 3.

Рис.3. Оптимизированная таблица

Поместим в ячейку B2 формулу:

В ячейке F5 сошлемся на нее, как

Заметим, что при точном подсчете, дней оказалось не 23, как показано в таблице на рисунке 1, а 24 дня.

Еще один вариант определения количества дней в дате текущего месяца, это использование функций КОНМЕСЯЦА и ДЕНЬ. Необходимо сказать, что функция КОНМЕСЯЦА доступна только после подключения надстройки «Пакет анализа». Функция КОНМЕСЯЦА возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев. Если заданное число месяцев принять равным нулю, то функция возвратит дату последнего дня текущего месяца. Получить числовое значение количества дней можно с помощью функции ДЕНЬ.

Создадим таблицу, приведенную на рисунке 4.

В ячейку B2 поместим формулу =ДЕНЬ(КОНМЕСЯЦА($B$1;0))

В ячейку D5 поместим формулу

В ячейку E5 поместим формулу

Рис. 4. Использование функции КОНМЕСЯЦА

Задачу можно решить, зная макрокоманды Excel. Для вычисления количества дней в месяце указанной даты можно написать функцию:

‘Дней в месяце от даты

M = Month(D): G = Year(D): V = 0

If (G Mod 400 = 0) Or ((G Mod 4 = 0) And (G Mod 100 <> 0)) Then V = 1

Case 1, 3, 5, 7, 8, 10, 12

Тогда в ячейку B2 таблицы, приведенной на рисунке 3 достаточно поместить формулу: =DnMes(A2)

И еще есть такая вещь, как недокументированные возможности. Оказывается Excel поддерживает функцию РАЗНДАТ(). Правда этой функции нет среди тех, которые доступны в диалоговом окне «Мастер функций» и набирать ее придется вручную. Функция известна еще с Lotus1–2-3, и очевидно с целью совместимости Excel поддерживает эту функцию. Интересно, что с версии Excel 2000 данная функция даже не упоминалась. А из интерактивной справки Excel2003 были удалены все ссылки на эту функцию, хотя сама функция доступна, как и в Excel2007.

Функция позволяет вычислить количество дней, месяцев и лет, между двумя указанными датами (как раз, то, что нам нужно). Она использует три аргумента: «начальная дата», конечная дата» и «код», с помощью которого задается единица времени. Значения кодов приведены в таблице 2.

Программа Эксель используется для выполнения различных статистических задач, одной из которых является вычисление доверительного интервала, который применяется как наиболее подходящая замена точечной оценки при малом объеме выборки.

Хотим сразу заметить, что сама процедура вычисления доверительного интервала довольно непростая, однако, в 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%-ым уровнем доверия.

    1. Первым делом выбираем ячейку для вывода результата. Затем кликаем по кнопке “Вставить функцию” (слева от строки формул).
    2. Откроется окно Мастера функций. Кликнув по текущей категории функций, раскрываем список и щелкаем в нем по строке “Статистические”.
    3. В предложенном перечне кликаем по оператору “ДОВЕРИТ.НОРМ”, затем жмем OK.
    4. Перед нами появится окно с настройками аргументов функции, заполнив которые нажимаем кнопку OK.
      • в поле “Альфа” указываем уровень значимости. В нашей задаче предполагается 95%-ый уровень доверия. Подставив данное значение в формулу расчета, которую мы рассматривали выше, получаем выражение: (100-95)/100 . Пишем его в поле аргумента (или можно сразу написать результат вычисления, равный 0,05).
      • в поле “Станд_откл” согласно нашим условия, пишем цифру 8.
      • в поле “Размер” указываем количество исследуемых элементов. В нашем случае было проведено 10 замеров, значит пишем цифру 10.
      • Чтобы при изменении данных не пришлось заново настраивать функцию, можно автоматизировать ее. Для это применим функцию “СЧЁТ”. Ставим указатель в область ввода информации аргумента “Размер”, затем щелкаем по значку треугольника с левой стороны от строки формул и кликаем по пункту “Другие функции…”.
      • В результате откроется еще одно окно Мастера функций. Выбрав категорию “Статистические”, кликаем по функции “СЧЕТ”, затем – OK.
      • На экране отобразится еще одно окно с настройками аргументов функции, которая применяется для определения числа ячеек в заданном диапазоне, в которых находятся числовые данные.
        Формула функции СЧЕТ пишется так: =СЧЁТ(Значение1;Значение2;. ) .
        Количество доступных аргументов этой функции может достигать 255 штук. Здесь можно прописать, либо конкретные числа, либо адреса ячеек, либо диапазоны ячеек. Мы воспользуемся последним вариантом. Для этого кликаем по области ввода информации для первого аргумента, затем зажав левую кнопку мыши выделяем все ячейки одного из столбцов нашей таблицы (не считая шапки), после чего жмем кнопку OK.
      • В результате проделанных действий в выбранной ячейке будет выведено результат расчетов по оператору ДОВЕРИТ.НОРМ. В нашей задаче его значение оказалось равным 4,9583603.
      • Но это еще не конечный результат в нашей задаче. Далее требуется рассчитать среднее значение по заданному интервалу. Для этого потребуется применить функцию “СРЗНАЧ”, которая выполняет задачу по вычислению среднего значения в пределах указанного диапазона данных.
        Формула оператора пишется так: =СРЗНАЧ(число1;число2;. ) .
        Выделяем ячейку, куда планируем вставить функцию и жмем кнопку “Вставить функцию”.
      • В категории “Статистические” выбираем нудный оператор “СРЗНАЧ” и кликаем OK.
      • В аргументах функции в значении аргумента “Число” указываем диапазон, в который входят все ячейки со значениями всех замеров. Затем кликаем OK.
      • В результате проделанных действий среднее значение будет автоматически подсчитано и выведено в ячейку с только что вставленной функцией.
      • Теперь нам нужно рассчитать границы ДИ (доверительного интервала). Начнем с расчета значения правой границы. Выбираем ячейку, куда хотим вывести результат, и выполняем в ней сложение результатов, полученных с помощью операторов “СРЗНАЧ” и “ДОВЕРИТ.НОРМ”. В нашем случае формула выглядит так: A14+A16 . После ее набора жмем Enter.
      • В результате будет произведен расчет и результат немедленно отобразится в ячейке с формулой.
      • Затем аналогичным способом выполняем расчет для получения значения левой границы ДИ. Только в этом случае значение результата “ДОВЕРИТ.НОРМ” нужно не прибавлять, а вычитать из результата, полученного при помощи оператора “СРЗНАЧ”. В нашем случае формула выглядит так: =A16-A14 .
      • После нажатия Enter мы получим результат в заданной ячейке с формулой.

      Примечание: В пунктах выше мы постарались максимально подробно расписать все шаги и каждую применяемую функцию. Однако все прописанные формулы можно записать вместе, в составе одной большой:

      • Для определения правой границы ДИ общая формула будет выглядеть так:
        =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)) .
      • Точно также и для левой границы, только вместо плюса нужно поставить минус:
        =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)) .

      Метод 2: оператор ДОВЕРИТ.СТЬЮДЕНТ

      Теперь давайте познакомимся со вторым оператором для определения доверительного интервала – ДОВЕРИТ.СТЬЮДЕНТ. Данная функция была внедрена в программу относительно недавно, начиная с версии Эксель 2010, и направлена на определение ДИ выбранной совокупности данных с применением распределения Стьюдента, при неизвестной дисперсии.

      Формула функции ДОВЕРИТ.СТЬЮДЕНТ выглядит следующим образом:

      Давайте разберем применение данного оператора на примере все той же таблицы. Только теперь стандартное отклонение по условиям задачи нам неизвестно.

      1. Сначала выбираем ячейку, куда планируем вывести результат. Затем кликаем по значку “Вставить функцию” (слева от строки формул).
      2. Откроется уже хорошо знакомое окно Мастера функций. Выбираем категорию “Статистические”, затем из предложенного списка функций щелкаем по оператору “ДОВЕРИТ.СТЬЮДЕНТ”, после чего – OK.
      3. В следующем окне нам нужно настроить аргументы функции:.
        • В поле “Альфа” как и в первом методе указываем значение 0,05 (или “100-95)/100”).
        • Переходим к аргументу “Станд_откл”. Т.к. по условиям задачи его значение нам неизвестно, нужно произвести соответствующие расчеты, в чем нам поможет оператор “СТАНДОТКЛОН.В”. Щелкаем по кнопке добавления функции и затем – по пункту “Другие функции…”.
        • В очередном окне Мастера функций выбираем оператор “СТАНДОТКЛОН.В” в категории “Статистические” и кликаем OK.
        • Мы попадаем в окно настройки аргументов функции, формула которой выглядит так: =СТАНДОТКЛОН.В(число1;число2;. ) . В качестве первого аргумента указываем диапазон, включающий все ячейки столбца “Значение” (не считая шапки).
        • Теперь нужно вернуться обратно в окно с аргументами функции “ДОВЕРИТ.СТЬЮДЕНТ”. Для этого щелкаем по одноименной надписи в поле ввода формул.
        • Теперь переходим к последнему аргументу “Размер”. Как и в первом методе, здесь можно либо просто указать диапазон ячеек, либо вставить оператор “СЧЕТ”. Выбираем последний вариант.
        • Как только все аргументы заполнены, жмем кнопку OK.
        • В выбранной ячейке отобразится значение доверительного интервала согласно заданным нами параметрам.
        • Далее нам нужно рассчитать значения границ ДИ. А для этого потребуется получить среднее значение по выбранному диапазону. Для этого снова применим функцию “СРЗНАЧ”. Алгоритм действий аналогичен тому, что был описан в первом методе.
        • Получив значение “СРЗНАЧ”, можно приступать к расчетам границ ДИ. Сами формулы ничем не отличаются от тех, что использовались с оператором “ДОВЕРИТ.НОРМ”:
          • Правая граница ДИ=СРЗНАЧ+ДОВЕРИТ.СТЬЮДЕНТ
          • Левая граница ДИ=СРЗНАЧ-ДОВЕРИТ.СТЬЮДЕНТ

          Заключение

          Арсенал инструментов Excel невероятно большой, и наряду с распространенными функциями, программа предлагает большое разнообразие специальных функций, которые помогут существенно облегчить работу с данными. Возможно, описанные выше шаги некоторым пользователям, на первый взгляд, могут показаться сложными. Но после детального изучения вопроса и последовательности действий, все станет намного проще.

          Ссылка на основную публикацию
          Фартуки для кухни отзывы какие лучше брать
          Сегодня поговорим о самых популярных материалах для оформления рабочей зоны, сравним их и выясним какой же материал лучше всего подойдет...
          Удобное компьютерное кресло отзывы
          В общем, надоело мне с ноутбуком на кровати валяться, спину портить. Стол есть, 74см высотой. Сейчас сижу на офисном стуле,...
          Удобный сайт для просмотра фильмов
          Некоторые онлайн-кинотеатры радуют лицензионными бесплатными фильмами, однако за лучший контент придётся платить. В подборке Лайфхакера — несколько хороших сервисов с...
          Фейк ава в вк парня
          Фото девушек на аву Фото девушек на аву Здесь вы можете найти для себя много реальных фото на аву красивых...
          Adblock detector