Как вести статистику в Excel

Обновлено: 21.11.2024

Excel предоставляет довольно широкие возможности для создания графиков, которые Excel называет диаграммами. Вы можете получить доступ к возможностям построения диаграмм Excel, выбрав «Вставка» > «Диаграммы». Здесь мы опишем, как создавать гистограммы и линейные диаграммы. В другом месте на веб-сайте мы описываем, как создавать точечные диаграммы. Подобным образом создаются и другие типы диаграмм. После создания диаграммы доступны три новые ленты: «Дизайн», «Макет» и «Формат». Они используются для уточнения созданной диаграммы.

Гистограммы

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

Введите данные, которые вы наносите на диаграмму, на лист.
Выделите диапазон данных и выберите «Вставка» > «Диаграммы|Столбец». Отображается список типов гистограмм. Как обычно, вы можете навести указатель мыши на изображение любого типа диаграммы, чтобы получить краткое описание этого типа диаграммы. Например. первый тип представляет собой двумерную гистограмму, расположенную рядом друг с другом, а второй – двумерную гистограмму с накоплением.
Используйте ленты «Дизайн», «Макет» и «Формат», чтобы уточнить диаграмму. В любой момент вы можете нажать на диаграмму, чтобы получить доступ к этим лентам.

Теперь мы покажем, как создать гистограмму на следующем примере.

Пример 1. Создайте столбчатую диаграмму для данных на рисунке 1.

Первый шаг — ввести данные в рабочий лист. Затем мы выделяем диапазон A4:D10, т. е. данные (исключая итоги), включая заголовки строк и столбцов, и выбираем «Вставка» > «Диаграммы|Столбец».

Рисунок 1. Гистограмма в Excel

Результирующая диаграмма показана на рис. 1, хотя изначально диаграмма не содержит названия диаграммы или названий осей. Чтобы добавить заголовок диаграммы, щелкните диаграмму, выберите «Макет» > «Ярлыки|Заголовок диаграммы», затем выберите «Над диаграммой» и введите заголовок «Результаты маркетинговой кампании». Вы можете добавить заголовок горизонтальной оси аналогичным образом, выбрав «Макет» > «Ярлыки | Заголовки осей» > «Заголовок основной горизонтальной оси» > «Заголовок под осью» и введя слово «Город». Наконец, вы можете добавить заголовок вертикальной оси, выбрав «Макет» > «Ярлыки|Заголовки осей» > «Заголовок основной вертикальной оси» > «Повернутый заголовок».

Чтобы получить результаты, показанные на рис. 1, нам также нужно было переместить диаграмму на листе, щелкнув ее левой кнопкой мыши и перетащив ее в нужное место. Затем мы можем изменить размер диаграммы, сделав ее немного меньше (или больше), щелкнув один из углов диаграммы и перетащив угол, чтобы изменить размеры. Чтобы гарантировать, что соотношение сторон (то есть отношение длины к ширине) не изменится, важно удерживать нажатой клавишу Shift при перетаскивании угла.

Если вместо диаграммы продаж по городам вам нужна диаграмма продаж по брендам, вы можете нажать на диаграмму и выбрать «Дизайн» > «Данные|Переключить строку/столбец». Вы также можете изменить тип диаграммы, щелкнув ее, выбрав «Дизайн» > «Тип|Изменить тип диаграммы», а затем выбрав нужный тип диаграммы (например, гистограмму с накоплением вместо гистограммы, расположенной рядом).< /p>

Графики

Процесс создания линейной диаграммы аналогичен процессу создания гистограммы. Основное отличие состоит в том, что вам нужно выбрать «Вставка» > «Диаграммы|Линия».

Пример 2. Создайте линейную диаграмму среднего дохода выборки людей в возрасте от 30 лет по возрасту на основе данных на рис. 2.

Рисунок 2. Линейный график (начальный вид)

Чтобы создать диаграмму, выделите диапазон B3:B13 и выберите Вставка > Диаграммы|Линия. Результат показан на рис. 2. Далее мы опишем ряд изменений, которые мы хотим внести в диаграмму.

Легенда с надписью «Доход» не особенно полезна, поэтому мы удаляем ее, щелкнув диаграмму и выбрав «Макет» > «Ярлыки|Легенда» > «Нет». Затем мы изменим заголовок диаграммы, просто выделив заголовок (Доход) и изменив его на более информативный заголовок, такой как Средний доход по возрасту. Мы также вставляем заголовки горизонтальной и вертикальной осей, как делали это для гистограммы в примере 1.

Обратите внимание, что горизонтальная ось по умолчанию соответствует временным рядам от 1 до 10 (поскольку имеется 10 элементов данных). Чтобы изменить это значение с 31 на 40, щелкните диаграмму и выберите «Дизайн» > «Выбрать данные», чтобы отобразить диалоговое окно, показанное на рис. 3.

Рисунок 3. Диалоговое окно редактирования меток осей

Теперь нажмите кнопку «Редактировать» для меток горизонтальной оси (категории) (в правой части диалогового окна).Нам будет предложено ввести диапазон данных метки оси, и введите A4: A13 (или просто выделите этот диапазон на рабочем листе), а затем нажмите кнопку OK. Затем мы нажимаем кнопку OK в диалоговом окне, показанном на рисунке 3, чтобы принять изменение.

Поскольку ни один элемент данных не соответствует доходу ниже 20 000, может быть лучше, чтобы вертикальная ось начиналась с 20 000, а не с 0. Это можно сделать, щелкнув метки вертикальной оси (от 0 до 40 000) и выбрав Макет > Текущий выбор | Выбор формата выбора (в качестве альтернативы щелкните правой кнопкой мыши метки вертикальной оси и выберите параметр «Формат оси…»). Откроется диалоговое окно «Формат оси». Выберите «Параметры оси», а затем измените переключатель «Минимум» с «Авто» на «Фиксированный» и введите 20000.

Мы также решили изменить формат меток, чтобы использовать разделители-запятые для тысяч. Это достигается путем выбора вкладки «Число» (которая также находится в диалоговом окне «Формат оси»), выбора категории «Число», установки флажка «Использовать разделитель 1000» (,) и ввода 0 в качестве десятичных разрядов.

Результат всех этих изменений показан на рис. 4.

Рисунок 4. Линейный график (пересмотренный вид)

Наблюдение: вы также можете создавать диаграммы с несколькими линиями. Нажмите здесь, чтобы узнать больше.

Точечные диаграммы

Точечная диаграмма — это просто диаграмма, состоящая из ряда пар элементов данных, где первый элемент данных соответствует оси X, а второй — оси Y.

Пример 3. Создайте точечную диаграмму пар (x, y), показанных в диапазоне A3:C9 на рис. 5. Здесь пары представляют доходы (значения y) и эксплуатационные расходы (< значения em>x) в миллионах долларов для каждого из шести подразделений розничного бизнеса.

Выделите диапазон B4:C9 и выберите «Вставка» > «Диаграммы|Разброс», а затем измените заголовки, как мы делали это в предыдущих примерах, чтобы создать диаграмму, показанную на рисунке 5. Обратите внимание, что если строки данных были зашифрованы, мы получили бы то же самое. диаграмма.

Рисунок 5. Точечная диаграмма

Если вы хотите добавить метки к каждой точке диаграммы с соответствующим названием района, нажмите на диаграмму. Откроются три значка, показанные в правом верхнем углу диаграммы на рис. 5. Щелкните значок +, а затем щелкните справа от параметра элемента диаграммы Метки данных.

В появившемся меню выберите пункт Дополнительные параметры…. Это вызовет меню, как показано в правой части рисунка 6. Снимите флажок «Значение Y» и установите флажок «Значение из ячейки». В появившемся диалоговом окне введите диапазон A4:A9 (содержащий названия районов) и нажмите кнопку OK. Диаграмма теперь будет содержать метки с названиями районов, как показано в левой части рис. 6.

Рисунок 6. Точечная диаграмма с метками

Пошаговые диаграммы

Excel не поддерживает ступенчатую диаграмму, но мы можем создать ее, используя точечную диаграмму, показанную выше.

Пример 4. Создайте ступенчатую диаграмму для данных на рис. 7.

Рисунок 7. Данные ступенчатой диаграммы

Ключевым моментом является повторный ввод данных из A3:B9 на рис. 7 путем дублирования записей, как показано в диапазоне J3:K14 на рис. 8. Затем вы можете выделить диапазон J3:K14 (или J4:K14) и выберите «Вставка» > «Графики|Разброс», используя параметр «Разброс с прямыми линиями и маркерами». После обычных изменений заголовков вы получите диаграмму шагов, показанную на рис. 8.

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

С помощью одной функции Excel может рассчитать набор описательных статистических данных для вашего набора данных. Этот пост — отличное введение в интерпретацию описательной статистики, даже если Excel не является вашим основным пакетом статистических программ.

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

Кроме того, я включаю ссылки на написанные мной ресурсы, которые содержат четкие объяснения соответствующих статистических концепций, которых вы не найдете в документации Excel. И я использую пример набора данных для совместной работы и интерпретации!

Прежде чем продолжить, убедитесь, что установлен пакет инструментов анализа данных Excel. На вкладке Данные найдите Анализ данных, как показано ниже.

Если вы не видите Анализ данных, установите этот пакет инструментов. Узнайте, как установить его, в моем посте об использовании Excel для выполнения t-тестов. Это бесплатно!

Описательная статистика в Excel

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

В этом примере мы оценим две переменные: рост и вес девочек десятилетнего возраста. Эти данные я собрал в ходе реального эксперимента. Чтобы использовать эту функцию в Excel, расположите данные в столбцах или строках. У меня есть данные в столбцах, как показано во фрагменте ниже.

Загрузите файл Excel, содержащий данные для этого примера: HeightWeight.

В Excel нажмите Анализ данных на вкладке Данные, как показано выше. Во всплывающем окне «Анализ данных» выберите Описательная статистика и выполните следующие действия.

Пошаговые инструкции по заполнению поля описательной статистики Excel

В разделе «Входной диапазон» выберите диапазон переменных, которые вы хотите проанализировать. Вы можете включить несколько переменных, если они образуют непрерывный блок. Хотя вы можете исследовать более одной переменной, анализ оценивает каждую переменную одномерно (т. е. без корреляции).
В разделе «Сгруппировано по» выберите порядок организации переменных. Я всегда включаю одну переменную в столбец, так как этот формат является стандартным для всех программ. Кроме того, вы можете включить одну переменную в строку.
Установите флажок "Метки в первой строке", если у вас есть значимые имена переменных в строке 1. Этот параметр упрощает интерпретацию вывода.
В параметрах вывода выберите, где Excel должен отображать результаты.
Установите флажок "Сводная статистика", чтобы отобразить большую часть описательной статистики (центральная тенденция, дисперсия, свойства распределения, сумма и количество).
Установите флажок "Уровень достоверности для среднего", чтобы отобразить доверительный интервал для среднего значения. Введите уровень достоверности. 95% обычно является хорошим значением. Подробнее об уровнях достоверности читайте в моем посте о доверительных интервалах.
Отметьте K-й наибольший и K-й наименьший, чтобы отобразить максимальное и минимальное значение. Если вы введете 1, Excel отобразит самое высокое и самое низкое значения. Если вы введете 2, будут показаны 2-е самое высокое и самое низкое значения. и т. д.
Нажмите "ОК".

Для нашего примера набора данных заполните диалоговое окно, как показано ниже.

Интерпретация результатов описательной статистики Excel

После того, как Excel создаст статистический вывод, я автоматически подгоняю столбцы для ясности.

Как видите, мы оцениваем две переменные: рост в метрах и вес в килограммах.

Как правило, мы будем двигаться вниз от верхней части вывода описательной статистики Excel. Тем не менее, я сгруппирую результаты по категориям, которые имеют смысл. Следовательно, последующее обсуждение не строго следует порядку вывода. Если вы хотите узнать больше о статистике, обязательно перейдите по ссылкам для получения более подробной информации!

Центральные тенденции (среднее, медиана, мода)

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

Среднее значение. Это показатель, с которым вы лучше всего знакомы. Это сумма всех наблюдений, разделенная на количество наблюдений. Это лучше всего подходит для данных, которые следуют симметричному распределению.
Медиана. Это значение делит ваши данные пополам. Половина значений падает выше медианы, а половина ниже ее. Это лучше всего подходит для неравномерных распределений.
Мода. Этот показатель представляет значение, которое чаще всего встречается в ваших данных. Лучше всего подходит для категорийных и порядковых данных.

Данные примера являются непрерывными переменными. Excel часто отображает «Н/Д» для режима, когда у вас есть непрерывные данные. Это происходит потому, что непрерывные данные вряд ли будут иметь точно повторяющиеся значения, что является требованием для режима. Благодаря артефакту сбора данных мои данные непрерывны, но Excel все равно отображает режим. Медсестра, проводившая исследование, собрала исходные данные в дюймах и фунтах, округлила их до ближайшей единицы и преобразовала в метрические эквиваленты.Этот процесс произвел сгустки округленных значений. Однако на самом деле мода не является хорошей мерой для этих данных.

Основная тенденция для нашего примера описательной статистики

Что мы можем узнать, сравнив среднее значение и медиану для обеих переменных? По высотным данным они практически равны, 1,51 м и 1,50 м соответственно. Для симметричных распределений среднее значение и медиана будут очень близки друг к другу. Это хороший признак того, что высоты следуют симметричному распределению, что делает выбор среднего значения хорошим. Среднее значение говорит нам о том, что центр распределения по высоте находится на отметке 1,51 м.

Однако существует разница между средним (46,3 кг) и медианным (44,9 кг) весом. Когда среднее значение больше медианы, это указывает на то, что распределение смещено вправо. Мы должны использовать медиану для этих данных. Половина точек данных находится выше 44,9 кг, а половина - ниже.

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

Показатели дисперсии (стандартное отклонение, дисперсия, диапазон)

Ранее вы видели, как мера центральной тенденции показывает, куда попадает большинство наблюдений. Меры дисперсии показывают, насколько близко сгруппированные или слабо распределенные точки данных попадают вокруг центра. Excel представляет три меры дисперсии. Как правило, по мере увеличения их значений точки данных расходятся дальше от центра (т. е. распределение становится шире).

Стандартное отклонение: стандартная или типичная разница между каждой точкой данных и средним значением. В этом показателе используются исходные единицы данных, что упрощает интерпретацию. Следовательно, аналитики используют эту меру изменчивости чаще всего. Стандартное отклонение – это квадратный корень из дисперсии.
Дисперсия: среднее квадратичное отличие значений от среднего. Поскольку в расчетах используются квадраты разницы, дисперсия выражается в квадратах, а не в исходных единицах данных. Хотя более высокие значения дисперсии указывают на большую изменчивость, нет интуитивной интерпретации конкретных значений. Узнайте больше о дисперсии.
Диапазон: разница между наибольшим и наименьшим значением в наборе данных. Диапазон легко понять, но он основан только на двух самых крайних значениях в наборе данных, что делает его очень восприимчивым к выбросам. Кроме того, размер набора данных влияет на диапазон. По мере увеличения размера выборки диапазон имеет тенденцию к расширению. Следовательно, используйте диапазон для сравнения изменчивости только в том случае, если размеры выборки схожи. Подробнее об ассортименте.

Как правило, используется стандартное отклонение. Если у вас достаточно искаженные данные, рассмотрите возможность использования межквартильного диапазона (IQR), который, к сожалению, не предоставляется в Excel.

Изменчивость для нашего примера описательной статистики

Для данных о высоте стандартное отклонение составляет 0,07 м (7 см). Типичная высота составляет 7 см от среднего значения 1,51 м. Диапазон говорит нам о том, что расстояние от самого высокого до самого низкого составляет 0,33 м (33 см). Аналогичные выводы можно сделать и по данным о весе.

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

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

Свойства формы распределения: эксцесс и асимметрия

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

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

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

Эксцесс

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

Для нашего примера эксцесс роста составляет -0,35. Это значение близко к нулю, что указывает на то, что хвосты соответствуют нормальному распределению. Однако вес имеет эксцесс 1,15, что говорит о том, что хвосты тоньше, чем при нормальном распределении.

Асимметрия

Асимметрия указывает на симметричность распределения ваших данных. Искаженные данные асимметричны. Термины «правая асимметрия» и «левосторонняя асимметрия» указывают направление, в котором длинный хвост указывает на кривой распределения. Узнайте больше об асимметричных распределениях.

Значение асимметрии	Указывает	График
Ноль	Идеально симметричное распределение
Положительные	Данные с перекосом вправо
Отрицательный	Смещенные влево данные

Обратите внимание, что U-образное распределение может быть симметричным, даже если оно инвертировано по сравнению с нормальным распределением.

Для нашего примера данных высота имеет асимметрию 0,11. Это значение близко к нулю, что означает, что эти данные имеют симметричное распределение. Однако вес имеет асимметрию 1,05, что указывает на то, что он смещен вправо.

Относительное расположение среднего и медианы, а также эти свойства распределения дают согласованную картину этих двух переменных. Для данных о высоте среднее значение и медиана почти равны, а эксцесс и асимметрия практически равны нулю. В совокупности эти меры подразумевают, что высоты подчиняются симметричному распределению, согласующемуся с нормальным распределением.

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

Минимум и максимум

Минимальные и максимальные значения в вашем наборе данных могут помочь вам понять, куда попадают ваши данные. Для нашего примера данные, высота находится в диапазоне от 1,33 до 1,66 м, а вес находится в диапазоне от 29,26 до 80,74 кг. Кроме того, эти значения могут помочь вам идентифицировать выбросы. Часто ошибки ввода данных создают значения, выходящие за пределы диапазона допустимых данных. Посмотрите на минимальные и максимальные значения и посмотрите, имеют ли они смысл для ваших данных!

Сумма и подсчет

Сумма — это просто сумма всех значений каждой переменной. Я никогда не находил это полезным, но, возможно, это будет для вас. Количество — это количество наблюдений для каждой переменной. Используйте это значение, чтобы определить, соответствует ли размер выборки ожидаемому. Переменные роста и веса имеют 88 наблюдений.

Точность среднего: стандартная ошибка и доверительный интервал

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

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

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

Стандартная ошибка среднего

Стандартная ошибка среднего – это стандартное отклонение выборочного распределения среднего. Что?!

Если вы возьмете много выборок из одной и той же совокупности и вычислите среднее значение для каждой выборки, вы получите распределение выборочных средних. Это распределение имеет стандартное отклонение, которое представляет собой стандартную ошибку среднего.

Меньшие стандартные ошибки означают, что ваша выборка дает более точную оценку значения генеральной совокупности. К сожалению, интуитивной интерпретации этих величин нет. Однако расчеты доверительных интервалов (ДИ) включают стандартную ошибку, и ДИ гораздо легче интерпретировать. Поэтому сосредоточьтесь на ЭК и не беспокойтесь о стандартных ошибках!

Доверительный интервал (ДИ) среднего

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

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

Excel не предоставляет диапазон, но отображает число, которое нужно добавить и вычесть из среднего значения для расчета доверительного интервала.

Для данных о высоте Excel отображает 0,015530282, которые я округляю до 0,02. Чтобы рассчитать КИ, возьмите средний рост и +/- это значение. Другими словами, 1,51 +/- 0,02 создает доверительный интервал 1,49–1,53. Мы можем быть уверены, что средний рост для этой популяции находится между этими двумя значениями.

Используя тот же процесс, доверительный интервал для веса составляет [43,98 48,68]. Мы можем быть уверены, что средний вес для совокупности находится между этими значениями.

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

Гистограммы наших данных описательной статистики

Давайте посмотрим на гистограммы для нашего примера данных. Эти графики не являются частью описательной статистики Excel. Тем не менее, я предлагаю сначала начертить данные, а затем изучить числа. Все статистические данные в этом посте описывают данные, на основе которых построены графики ниже.

Есть ли сюрпризы?

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

Хотя анализ описательной статистики не может оценить корреляцию, прочтите мою статью об использовании Excel для расчета корреляции, чтобы оценить взаимосвязь между этими двумя переменными!

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

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

Пакет анализа включает инструменты, описанные в следующих разделах. Чтобы получить доступ к этим инструментам, нажмите «Анализ данных» в группе «Анализ» на вкладке «Данные». Если команда "Анализ данных" недоступна, необходимо загрузить программу-надстройку "Пакет анализа".

Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Надстройки".

В поле "Управление" выберите "Надстройки Excel" и нажмите "Перейти".

Если вы используете Excel для Mac, в меню файлов выберите Инструменты > Надстройки Excel.

В поле "Надстройки" установите флажок "Пакет анализа" и нажмите "ОК".

Если пакет инструментов анализа не указан в списке доступных надстроек, нажмите кнопку "Обзор", чтобы найти его.

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

Примечание. Чтобы включить функции Visual Basic для приложений (VBA) в пакет анализа, вы можете загрузить надстройку Analysis ToolPak — VBA так же, как вы загружаете пакет анализа. В поле "Доступные надстройки" установите флажок "Пакет анализа – VBA".

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

Anova: один фактор

Этот инструмент выполняет простой дисперсионный анализ данных для двух или более выборок. Анализ обеспечивает проверку гипотезы о том, что каждая выборка взята из одного и того же основного распределения вероятностей, в сравнении с альтернативной гипотезой о том, что основные распределения вероятностей не одинаковы для всех выборок. Если есть только два образца, вы можете использовать функцию рабочего листа T.TEST. При наличии более двух выборок удобного обобщения T.TEST нет, и вместо этого можно использовать модель Single Factor Anova.

Anova: двухфакторный анализ с репликацией

Этот инструмент анализа полезен, когда данные можно классифицировать по двум разным параметрам. Например, в эксперименте по измерению высоты растений растениям можно давать удобрения разных марок (например, А, В, С), а также можно хранить при разных температурах (например, низкой, высокой). Для каждой из шести возможных пар у нас есть равное количество наблюдений за высотой растений. Используя этот инструмент Anova, мы можем протестировать:

Независимо от того, берется ли высота растений для разных марок удобрений из одной и той же исходной популяции. Температуры для этого анализа игнорируются.

Независимо от того, взяты ли данные о высоте растений для разных температурных уровней из одной и той же исходной популяции. Марки удобрений в этом анализе не учитываются.

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

Anova: двухфакторный анализ без репликации

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

Функции рабочего листа CORREL и PEARSON вычисляют коэффициент корреляции между двумя переменными измерения, когда измерения каждой переменной наблюдаются для каждого из N субъектов. (Любое отсутствующее наблюдение для любого субъекта приводит к тому, что этот субъект игнорируется при анализе.) Инструмент корреляционного анализа особенно полезен, когда имеется более двух переменных измерения для каждого из N субъектов. Он предоставляет выходную таблицу, матрицу корреляции, которая показывает значение CORREL (или PEARSON), примененное к каждой возможной паре измеряемых переменных.

Коэффициент корреляции, как и ковариация, – это мера степени, в которой две измеряемые переменные "изменяются вместе". В отличие от ковариации, коэффициент корреляции масштабируется таким образом, что его значение не зависит от единиц, в которых выражаются две переменные измерения. (Например, если двумя измеряемыми переменными являются вес и рост, значение коэффициента корреляции не изменится, если вес будет преобразован из фунтов в килограммы.) Значение любого коэффициента корреляции должно находиться в диапазоне от -1 до +1 включительно.

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

Инструменты "Корреляция" и "Ковариация" можно использовать в одних и тех же условиях, когда у вас есть N различных измеряемых переменных, наблюдаемых для группы людей. Инструменты Correlation и Covariance дают выходную таблицу, матрицу, которая показывает коэффициент корреляции или ковариацию, соответственно, между каждой парой измеряемых переменных. Разница в том, что коэффициенты корреляции масштабируются так, чтобы лежать в диапазоне от -1 до +1 включительно. Соответствующие ковариации не масштабируются. И коэффициент корреляции, и ковариация являются мерами степени, в которой две переменные «изменяются вместе».

Инструмент Covariance вычисляет значение функции листа COVARIANCE.P для каждой пары измеряемых переменных. (Прямое использование COVARIANCE.P вместо инструмента Covariance является разумной альтернативой, когда есть только две измеряемые переменные, то есть N = 2.) Запись на диагонали выходной таблицы инструмента Covariance в строке i, столбце i: ковариация i-й переменной измерения с самой собой. Это всего лишь дисперсия генеральной совокупности для этой переменной, рассчитанная с помощью функции рабочего листа VAR.P.

Вы можете использовать инструмент "Ковариация" для проверки каждой пары измеряемых переменных, чтобы определить, имеют ли две измеряемые переменные тенденцию двигаться вместе, то есть связаны ли большие значения одной переменной с большими значениями другой (положительная ковариация). ), связаны ли малые значения одной переменной с большими значениями другой (отрицательная ковариация) или значения обеих переменных не связаны между собой (ковариация близка к 0 (нулю)).

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

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

Примечание. Значения от 0,2 до 0,3 являются приемлемыми константами сглаживания.Эти значения указывают на то, что текущий прогноз должен быть скорректирован на 20–30 % из-за ошибки в предыдущем прогнозе. Большие константы дают более быстрый отклик, но могут давать неустойчивые прогнозы. Меньшие константы могут привести к большим задержкам для прогнозируемых значений.

Инструмент анализа F-Test Two-Sample for Variances выполняет двухвыборочный F-тест для сравнения двух дисперсий генеральной совокупности.

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

Инструмент вычисляет значение f F-статистики (или F-коэффициента). Значение f, близкое к 1, свидетельствует о том, что базовые дисперсии генеральной совокупности равны. В выходной таблице, если f 1, "P(F

Инструмент анализа Фурье решает задачи в линейных системах и анализирует периодические данные с помощью метода быстрого преобразования Фурье (БПФ) для преобразования данных. Этот инструмент также поддерживает обратные преобразования, при которых обратные преобразованные данные возвращают исходные данные.

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

Например, в классе из 20 учащихся можно определить распределение баллов по буквенным категориям. В таблице гистограмм представлены границы буквенных оценок и количество баллов между самой низкой границей и текущей границей. Единственная наиболее часто встречающаяся оценка — это режим данных.

Совет. В Excel 2016 теперь можно создавать гистограммы или диаграммы Парето.

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

N — количество предыдущих периодов, которое необходимо включить в скользящее среднее

A j — фактическое значение в момент времени j

F j — прогнозируемое значение в момент времени j

Инструмент анализа "Генерация случайных чисел" заполняет диапазон независимыми случайными числами, взятыми из одного из нескольких распределений. Вы можете охарактеризовать субъектов в популяции с помощью распределения вероятностей. Например, можно использовать нормальное распределение, чтобы охарактеризовать совокупность показателей роста людей, или использовать распределение Бернулли двух возможных исходов, чтобы охарактеризовать совокупность результатов подбрасывания монеты.

Инструмент анализа рангов и процентилей создает таблицу, содержащую порядковый и процентный ранги каждого значения в наборе данных. Вы можете анализировать относительное положение значений в наборе данных. Этот инструмент использует функции рабочего листа RANK.EQ и PERCENTRANK.INC. Если вы хотите учитывать связанные значения, используйте функцию RANK.EQ, которая обрабатывает связанные значения как имеющие одинаковый ранг, или используйте функцию RANK.AVG, которая возвращает средний ранг для связанных значений.

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

Инструмент регрессии использует функцию рабочего листа ЛИНЕЙН.

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

Инструменты анализа Two-Sample t-Test проверяют равенство совокупностей, лежащих в основе каждой выборки. В трех инструментах используются разные предположения: дисперсии популяции равны, дисперсии популяции не равны и что две выборки представляют наблюдения до и после лечения одних и тех же субъектов.

Для всех трех приведенных ниже инструментов вычисляется значение t-статистики, t, которое отображается как «t Stat» в выходных таблицах. В зависимости от данных это значение t может быть отрицательным или неотрицательным.В предположении равной базовой совокупности означает, что если t = 0, "P(T

Инструмент Z-Test: Two Sample for Means выполняет двухвыборочный z-Test для средних с известной дисперсией. Этот инструмент используется для проверки нулевой гипотезы об отсутствии различий между двумя средними значениями генеральной совокупности по сравнению с односторонней или двусторонней альтернативной гипотезой. Если отклонения неизвестны, вместо этого следует использовать функцию рабочего листа Z.TEST.

При использовании инструмента z-Test внимательно следите за выводом. «P(Z = ABS(z)), вероятность того, что значение z дальше от 0 в том же направлении, что и наблюдаемое значение z, когда нет разницы между средними значениями генеральной совокупности». P(Z = ABS(z) или Я

Нужна дополнительная помощь?

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

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

Содержание:

Статистика Excel: проблемы, советы и предупреждения

Если вы используете Microsoft Excel в своем классе статистики, вы можете быть удивлены, узнав, что Excel на самом деле очень плохой выбор. Excel — отличный пакет электронных таблиц, но он никогда не предназначался для статистического анализа. Пакет анализа данных — это необязательная надстройка. Это должно дать вам понять, что анализ данных не является сильной стороной Excel. Вы можете легко сделать простые графики и расчеты. Но вы столкнетесь с некоторыми серьезными проблемами с более сложными задачами: включая отсутствующие данные.

Функция рейтинга Excel.

Особые проблемы со статистикой Excel

По данным Массачусетского университета, у Excel есть несколько проблем со статистическими вычислениями. К ним относятся:

Excel может одновременно выполнять определенные тесты только для одного столбца. В «реальном мире» маловероятно, что у вас будет один столбец переменных; Скорее всего, у вас будет микс из нескольких столбцов. Это может вызвать проблемы с запуском t-тестов и даже с простым подсчетом частоты.
Excel не регистрирует ваши действия. Поэтому, если вы забудете, что сделали, или просто захотите повторить задачу, это может оказаться невозможным, если вы не начнете с нуля.
Если вы хотите выполнить серию статистических тестов своих данных, вам, вероятно, придется много копировать, вставлять, сортировать и переупорядочивать. Это потому, что каждый тест в Excel требует, чтобы данные были расположены определенным образом. Например, однофакторный дисперсионный анализ требует, чтобы данные располагались в отдельных и смежных столбцах или строках для каждой группы. Для двухфакторного дисперсионного анализа с повторениями требуется прямоугольная область со столбцами, представляющими один фактор, и строками, представляющими другой. С таким статистическим пакетом, как SPSS, вам не придется ничего переставлять.

Это лишь некоторые из ловушек, связанных со статистикой Excel. Конечно, статистические пакеты, такие как SPSS, скорее всего, вам не по карману; Они стоят тысячи долларов, поэтому случайный пользователь выбирает Excel. Однако, если ваша школа или работа предлагает статистический пакет, вы можете изучить его. В долгосрочной перспективе ваши вычисления станут проще, займут меньше времени и станут более точными.

Основы

Как суммировать в Excel

Посмотрите видео или прочитайте приведенные ниже инструкции, чтобы узнать, как выполнять суммирование в Excel 2013:

Как суммировать в Excel 2013: обзор

Если вы хотите суммировать в Excel, у вас есть множество вариантов. Вы можете использовать:

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

Сумма в Excel 2013: функция СУММ

Шаг 1. Введите набор данных в один столбец без пропусков.

Шаг 2. Щелкните любую пустую ячейку на листе.

Шаг 3. Введите «=СУММ(Z1:Z99)», где «Z1:Z99» — это расположение ваших ячеек (Z1:Z99 сообщает Excel, что ваши данные находятся в столбце Z, ячейки с 1 по 99).

Сумма в Excel 2013: автосумма

Шаг 1. Введите набор данных в один столбец без пропусков.

Шаг 2. Нажмите на пустую ячейку сразу под вашими данными.

Шаг 3. Нажмите кнопку автосуммирования на вкладке "Главная". Кнопка автосуммирования (Σ) находится справа от панели инструментов.

Сумма в Excel 2013: пакет инструментов для анализа данных

Шаг 1. Перейдите на вкладку "Данные" и выберите "Анализ данных".

Шаг 4. Установите переключатель рядом со строками или столбцами в зависимости от того, как расположены ваши данные. Как правило, вы хотите вводить данные в столбцах, поэтому установите переключатель «столбцы».

Шаг 5. Нажмите на поле "Ярлыки в первой строке", если ваши данные содержат заголовки столбцов.

Шаг 6. Установите флажок "Описательная статистика".

Шаг 7. Выберите место для вывода. Например, нажмите кнопку-переключатель «Новый рабочий лист».

Шаг 8. Нажмите «ОК».

Подробнее о статистике и советах читайте на нашем канале YouTube!
Наверх

Сводные таблицы Excel

Сводная таблица — это способ суммировать данные. Это называется «сводной» таблицей, потому что вы можете поворачивать таблицу. Другими словами, вы можете переключать строки и столбцы. Эти диаграммы обычно используются в компаниях для сравнения данных. Они являются одним из основных элементов любого курса бизнес-статистики, и их часто избегают из-за неправильного представления о том, что их трудно создать. Однако на самом деле они так же просты, как и другие графики в Excel, и даже могут быть проще, потому что Excel будет автоматически заполнять ваши входные данные, если они правильно отформатированы. Что такое правильно отформатированные данные для сводной таблицы? Данные, упорядоченные по столбцам и строкам с правильными заголовками столбцов и строк.

Как сделать сводную таблицу Excel

Посмотрите видео с инструкциями:

Создание таблицы:
Шаг 1. Введите данные в столбцы. Например, у вас могут быть продажи в столбце A, валовая прибыль в столбце B и чистая прибыль в столбце C.

Шаг 2. Щелкните ячейку в любом месте набора данных.

Шаг 3. Перейдите на вкладку "Вставка" и нажмите "Сводная таблица".

Шаг 4. Проверьте правильность диапазона данных. Щелкните переключатель для расположения таблицы. Например, нажмите «Новый рабочий лист».

Шаг 5. Нажмите "ОК".

Шаг 6. Нажмите и перетащите нужные поля в строки таблицы из параметров в правом столбце. Перетащите в область «строки» внизу. Примечание: чтобы это работало должным образом, вы должны заранее спланировать свой стол. Посмотрите на изображение слева от рабочего листа, чтобы узнать, как выглядит сводная таблица Excel.

Шаг 7. Повторите шаг 6 для столбцов, которые вы хотите добавить в диаграмму.

Шаг 8. Повторите шаг 6 для центральной части таблицы. Например, если вы хотите, чтобы слово «продажи» было посередине, перетащите его в область значений.

После того как вы составили таблицу, наступает время правильного форматирования. В основном это вопрос выбора. Ни один из шагов форматирования не является абсолютно необходимым, хотя у вас будет гораздо более профессиональная (а в некоторых случаях и более понятная) сводная таблица, если вы внесете несколько изменений в свою базовую таблицу. Например, когда вы создаете сводную таблицу Excel, формат номера ячейки по умолчанию — это просто «Число». Если вы имеете дело с цифрами в долларах, имеет смысл добавить «Валюта» в качестве опции на вашу таблицу. Другие темы, затронутые в этом коротком видео, включают:

Читайте также: