Объем выборки в excel
Обновлено: 07.07.2024
Microsoft Excel имеет десять основных статистических формул, таких как размер выборки, среднее значение, медиана, стандартное отклонение, максимум и минимум. Размер выборки - это число наблюдений в наборе данных, например, если опрашивающая компания опрашивает 500 человек, то размер выборки данных составляет 500. После ввода набора данных в Excel формула = COUNT вычислит размер выборки. , Размер выборки полезен для вычислений, таких как стандартные ошибки и уровни достоверности. Использование Microsoft Excel позволит пользователю быстро рассчитать статистические формулы, поскольку статистические формулы, как правило, длиннее и сложнее, чем другие математические формулы.
Excel облегчает сложные статистические вычисления.
Шаг 1
Введите данные наблюдений в Excel, по одному наблюдению в каждой ячейке. Например, введите данные в ячейки с A1 по A24. Это обеспечит вертикальный столбец данных в столбце А.
Шаг 2
Введите "= COUNT (" в ячейку B1.
Шаг 3
Шаг 4
Как рассчитать будущую дату в Excel
Вы можете рассчитать будущую дату в Excel, используя различные функции формул. Используйте основные арифметические операции для простого сложения и вычитания дней. Используйте функцию EDATE, чтобы получить Exce .
Как рассчитать стандартное отклонение в Excel
Выберите одну из четырех формул стандартного отклонения в Excel и всегда рассчитывайте среднее или среднее значение при вычислении стандартного отклонения.
Размер папки является бесплатным для окон, которые отображают размер папки и файла
Хотите увидеть размеры папок и файлов в детальном представлении Windows File Explorer? Размер папки из MindGems значительно упрощает представление Windows по умолчанию.
Одним из основных статистических показателей последовательности чисел является коэффициент вариации. Для его нахождения производятся довольно сложные расчеты. Инструменты Microsoft Excel позволяют значительно облегчить их для пользователя.
Вычисление коэффициента вариации
Этот показатель представляет собой отношение стандартного отклонения к среднему арифметическому. Полученный результат выражается в процентах.
В Экселе не существует отдельно функции для вычисления этого показателя, но имеются формулы для расчета стандартного отклонения и среднего арифметического ряда чисел, а именно они используются для нахождения коэффициента вариации.
Шаг 1: расчет стандартного отклонения
Стандартное отклонение, или, как его называют по-другому, среднеквадратичное отклонение, представляет собой квадратный корень из дисперсии. Для расчета стандартного отклонения используется функция СТАНДОТКЛОН. Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.
Синтаксис данных функций выглядит соответствующим образом:
= СТАНДОТКЛОН(Число1;Число2;…)
= СТАНДОТКЛОН.Г(Число1;Число2;…)
= СТАНДОТКЛОН.В(Число1;Число2;…)
- Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.
Шаг 2: расчет среднего арифметического
Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.
-
Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».
Шаг 3: нахождение коэффициента вариации
Теперь у нас имеются все необходимые данные для того, чтобы непосредственно рассчитать сам коэффициент вариации.
-
Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «Главная». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.
Таким образом мы произвели вычисление коэффициента вариации, ссылаясь на ячейки, в которых уже были рассчитаны стандартное отклонение и среднее арифметическое. Но можно поступить и несколько по-иному, не рассчитывая отдельно данные значения.
-
Выделяем предварительно отформатированную под процентный формат ячейку, в которой будет выведен результат. Прописываем в ней формулу по типу:
Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.
Как видим, программа Эксель позволяет значительно упростить расчет такого сложного статистического вычисления, как поиск коэффициента вариации. К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается. Таким образом, в Excel её может выполнить даже человек, который не имеет высокого уровня знаний связанных со статистическими закономерностями.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Одним из методов решения статистических задач является вычисление доверительного интервала. Он используется, как более предпочтительная альтернатива точечной оценке при небольшом объеме выборки. Нужно отметить, что сам процесс вычисления доверительного интервала довольно сложный. Но инструменты программы Эксель позволяют несколько упростить его. Давайте узнаем, как это выполняется на практике.
Процедура вычисления
Этот метод используется при интервальной оценке различных статистических величин. Главная задача данного расчета – избавится от неопределенностей точечной оценки.
Способ 1: функция ДОВЕРИТ.НОРМ
Оператор ДОВЕРИТ.НОРМ, относящийся к статистической группе функций, впервые появился в Excel 2010. В более ранних версиях этой программы используется его аналог ДОВЕРИТ. Задачей этого оператора является расчет доверительного интервала с нормальным распределением для средней генеральной совокупности.
Его синтаксис выглядит следующим образом:
Все аргументы данного оператора являются обязательными.
Функция ДОВЕРИТ имеет точно такие же аргументы и возможности, что и предыдущая. Её синтаксис таков:
Как видим, различия только в наименовании оператора. Указанная функция в целях совместимости оставлена в Excel 2010 и в более новых версиях в специальной категории «Совместимость». В версиях же Excel 2007 и ранее она присутствует в основной группе статистических операторов.
Граница доверительного интервала определяется при помощи формулы следующего вида:
Где X – это среднее выборочное значение, которое расположено посередине выбранного диапазона.
Теперь давайте рассмотрим, как рассчитать доверительный интервал на конкретном примере. Было проведено 12 испытаний, вследствие которых были получены различные результаты, занесенные в таблицу. Это и есть наша совокупность. Стандартное отклонение равно 8. Нам нужно рассчитать доверительный интервал при уровне доверия 97%.
-
Выделяем ячейку, куда будет выводиться результат обработки данных. Щелкаем по кнопке «Вставить функцию».
Значит, чтобы посчитать уровень значимости, то есть, определить значение «Альфа» следует применить формулу такого вида:
То есть, подставив значение, получаем:
Путем нехитрых расчетов узнаем, что аргумент «Альфа» равен 0,03. Вводим данное значение в поле.
Как известно, по условию стандартное отклонение равно 8. Поэтому в поле «Стандартное отклонение» просто записываем это число.
В поле «Размер» нужно ввести количество элементов проведенных испытаний. Как мы помним, их 12. Но чтобы автоматизировать формулу и не редактировать её каждый раз при проведении нового испытания, давайте зададим данное значение не обычным числом, а при помощи оператора СЧЁТ. Итак, устанавливаем курсор в поле «Размер», а затем кликаем по треугольнику, который размещен слева от строки формул.
Группа аргументов «Значения» представляет собой ссылку на диапазон, в котором нужно рассчитать количество заполненных числовыми данными ячеек. Всего может насчитываться до 255 подобных аргументов, но в нашем случае понадобится лишь один.
Данный оператор предназначен для расчета среднего арифметического значения выбранного диапазона чисел. Он имеет следующий довольно простой синтаксис:
Аргумент «Число» может быть как отдельным числовым значением, так и ссылкой на ячейки или даже целые диапазоны, которые их содержат.
Способ 2: функция ДОВЕРИТ.СТЮДЕНТ
Кроме того, в Экселе есть ещё одна функция, которая связана с вычислением доверительного интервала – ДОВЕРИТ.СТЮДЕНТ. Она появилась, только начиная с Excel 2010. Данный оператор выполняет вычисление доверительного интервала генеральной совокупности с использованием распределения Стьюдента. Его очень удобно использовать в том случае, когда дисперсия и, соответственно, стандартное отклонение неизвестны. Синтаксис оператора такой:
Как видим, наименования операторов и в этом случае остались неизменными.
Посмотрим, как рассчитать границы доверительного интервала с неизвестным стандартным отклонением на примере всё той же совокупности, что мы рассматривали в предыдущем способе. Уровень доверия, как и в прошлый раз, возьмем 97%.
-
Выделяем ячейку, в которую будет производиться расчет. Клацаем по кнопке «Вставить функцию».
В поле «Альфа», учитывая, что уровень доверия составляет 97%, записываем число 0,03. Второй раз на принципах расчета данного параметра останавливаться не будем.
Как видим, инструменты программы Excel позволяют существенно облегчить вычисление доверительного интервала и его границ. Для этих целей используются отдельные операторы для выборок, у которых дисперсия известна и неизвестна.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Методика расчета объема аудиторской выборки зависит от выбранного варианта исследования генеральной совокупности:
Количественное исследование для собственно-случайной выборки
Используемые обозначения:
| k | - номер слоя (при стратифицированном отборе) |
| i | - номер элемента в статистической аудиторской выборке |
| ik | - номер элемента в аудиторской выборке из слоя k |
| N | - объем генеральной совокупности |
 | - объем слоя k |
| S | - стоимость генеральной совокупности |
 | - стоимость слоя k |
| n | - объем статистической выборки |
 | - объем выборки из слоя k |
 | - сумма нарушения в элементе выборки (положительная или отрицательная) при собственно-случайной выборке |
 | - сумма нарушения в элементе выборки из слоя k |
| P | - доверительная вероятность |
| z | - квантиль нормального распределения, зависит от P, определяется из таблицы: |
| P, % | 80 | 85 | 90 | 95 |
| z | 1.28 | 1.44 | 1.64 | 1.96 |
Если n ≤ 30 (малая выборка), то z – квантиль t-распределения Стьюдента, зависит от P и n, определяется по таблице.
Объем и стоимость первой выборки при собственно-случайной аудиторской выборке
Используемые обозначения:
| O | - ожидаемая аудитором доля нарушений в генеральной совокупности (ожидаемая ошибка выборки) |
| D | - максимальная приемлемая для аудитора погрешность ожидаемой ошибки (допустимая ошибка выборки) |
Объем первой выборки , где
Стоимость первой выборки можно приблизительно оценить, умножив полученный объем выборки на среднюю стоимость документа
Экстраполированная сумма нарушений в генеральной совокупности (рассчитывается для каждого вида нарушений)
Используемые обозначения:
| E | - точечная оценка суммы нарушений в генеральной совокупности |
| Δ | - погрешность точечной оценки суммы нарушений в генеральной совокупности |
E ± Δ - экстраполированная сумма нарушений в генеральной совокупности
При нестратифицированном отборе:
, где - средняя сумма нарушений в собственно-случайной выборке
, где σ - среднее квадратическое отклонение сумм нарушений в выборке
При стратифицированном отборе:
, где - средняя сумма нарушений в выборке из слоя k
- весовой коэффициент слоя k
, где - среднее квадратическое отклонение в выборке из слоя k
Оптимальный объем собственно-случайной аудиторской выборки (для проведения довыборки)
Значения параметров в формулах определяются на основании первой выборки.
При расчете оптимального объема аудиторской выборки принимается во внимание сумма всех видов нарушений в проводке (сальдо), даже если в ней зафиксировано несколько разных видов нарушений.
При нестратифицированном отборе:
, где
При стратифицированном отборе:
, где
Количественное исследование для монетарной аудиторской выборки
Используемые обозначения:
| k | - номер слоя (при стратифицированном отборе) |
| i | - номер элемента в статистической аудиторской выборке |
| ik | - номер элемента в выборке из слоя k |
| N | - объем генеральной совокупности |
| - объем слоя k |
| S | - стоимость генеральной совокупности |
| - стоимость слоя k |
| n | - объем статистической выборки |
| - объем выборки из слоя k |
 | - относительная сумма нарушения в расчете на 1 руб. в элементе выборки, то есть отношение суммы нарушения к сумме проводки (сальдо) (положительная или отрицательная) |
 | - относительная сумма нарушения в элементе выборки из слоя k |
| P | - доверительная вероятность |
| z | - квантиль нормального распределения, зависит от P, определяется из таблицы: |
| P, % | 80 | 85 | 90 | 95 |
| z | 1.28 | 1.44 | 1.64 | 1.96 |
Если n ≤ 30 (малая выборка), то z – квантиль t-распределения Стьюдента, зависит от P и n, определяется по таблице.
Объем и стоимость первой выборки при монетарной выборке
Используемые обозначения:
| O | - ожидаемая аудитором доля нарушений в генеральной совокупности (ожидаемая ошибка выборки) |
| D | - максимальная приемлемая для аудитора погрешность ожидаемой ошибки (допустимая ошибка выборки) |
Объем первой выборки , где
Стоимость первой выборки можно приблизительно оценить, умножив полученный объем выборки на среднюю стоимость документа
Экстраполированная сумма нарушений в генеральной совокупности (рассчитывается для каждого вида нарушений)
Используемые обозначения:
| E | - точечная оценка суммы нарушений в генеральной совокупности |
| Δ | - погрешность точечной оценки суммы нарушений в генеральной совокупности |
E ± Δ - экстраполированная сумма нарушений в генеральной совокупности
При нестратифицированном отборе:
, где
, где
При стратифицированном отборе:
, где - средняя сумма нарушений в выборке из слоя k
- весовой коэффициент слоя k
, где
Оптимальный объем монетарной аудиторской выборки (для проведения довыборки)
Значения параметров в формулах определяются на основании первой выборки.
При расчете оптимального объема выборки принимается во внимание сумма всех видов нарушений в проводке (сальдо), даже если в ней зафиксировано несколько разных видов нарушений.
При нестратифицированном отборе:
, где
При стратифицированном отборе:
, где
Атрибутивное исследование генеральной совокупности
Используемые обозначения:
| k | - номер слоя (при стратифицированном отборе) |
| N | - объем генеральной совокупности |
| - объем слоя |
| n | - объем статистической выборки |
| - объем выборки из слоя k |
| m | - количество нарушений в выборке |
 | - количество нарушений в выборке из слоя k |
| P | - доверительная вероятность |
| z | - квантиль нормального распределения, зависит от P, определяется из таблицы: |
| P, % | 80 | 85 | 90 | 95 |
| z | 1.28 | 1.44 | 1.64 | 1.96 |
Если n ≤ 30 (малая выборка), то z – квантиль t-распределения Стьюдента, зависит от P и n, определяется по таблице.
Объем первой выборки при атрибутивном выборочном исследовании
Используемые обозначения:
| O | - ожидаемая аудитором доля нарушений в генеральной совокупности (ожидаемая ошибка выборки) |
| D | - максимальная приемлемая для аудитора погрешность ожидаемой ошибки (допустимая ошибка выборки) |
Объем первой выборки , где
Стоимость первой выборки можно приблизительно оценить, умножив полученный объем выборки на среднюю стоимость документа
Экстраполированная доля нарушений в генеральной совокупности (рассчитывается для каждого вида нарушений)
Используемые обозначения:
| E | - точечная оценка доли нарушений в генеральной совокупности |
| Δ | - погрешность точечной оценки доли нарушений в генеральной совокупности |
E ± Δ - экстраполированная доля нарушений в генеральной совокупности
При нестратифицированном отборе:
- доля нарушений в выборке
При стратифицированном отборе:
, где
- доля нарушений в выборке из слоя
- весовой коэффициент слоя k
Экстраполированное количество нарушений в генеральной совокупности (рассчитывается для каждого вида нарушений)
Экстраполированное количество нарушений в генеральной совокупности определяется умножением экстраполированной доли нарушений на объем генеральной совокупности, то есть:
Оптимальный объем аудиторской выборки (для проведения довыборки)
Значения параметров в формулах определяются на основании первой выборки. При расчете оптимального объема выборки имеет значение только факт наличия нарушений в проводке (сальдо), даже если в ней зафиксировано несколько разных видов нарушений.
При нестратифицированном отборе:
, где
При стратифицированном отборе:
, где
Экстраполяция ошибок на генеральную совокупность
По выявленным в результате аудита нарушений, с учетом выбранного способа распределения результатов выборки, производится расчет статистических данных (дисперсия, коэффициент вариации, стоимость совокупности, среднее значение и т.д.) и распределение выявленных ошибок на генеральную совокупность (экстраполяция).
Результат распределения ошибок на генеральную совокупность приводится в отчете Статистика проверяемой совокупности.
Внутрифирменный стандарт аудиторской выборки
Предлагаемая методика статистической аудиторской выборки может быть использована при подготовке внутрифирменного стандарта по аудиторской выборке.
Читайте также: