Excel не предназначен для анализа данных, но он все же может работать со статистикой. Мы покажем вам, как использовать дополнение Data Analysis Toolpak для запуска статистики в Excel
В большинстве случаев при выполнении статистических вычислений вы хотите использовать статистическое программное обеспечение. Эти инструменты созданы для выполнения таких расчетов, как t-тесты, тесты хи-квадрат, корреляции и так далее.Excel не предназначен для анализа данных. Но это не значит, что вы не можете этого сделать
К сожалению, статистические функции Excel не всегда интуитивно понятны. И обычно они дают эзотерические результаты. Поэтому вместо статистических функций мы будем использовать стандартную статистическую надстройку Excel: Data Analysis Toolpak
Пакет инструментов, несмотря на неудачное написание, включает широкий спектр полезных статистических функций. Давайте посмотрим, что мы можем сделать с помощью статистики Excel
Добавление пакета инструментов анализа данных Excel
Хотя статистику можно вести и без пакета Data Analysis Toolpak, с ним это гораздо проще. Чтобы установить пакет инструментов в Excel 2016, перейдите в меню Файл > Параметры > Дополнения
Нажмите Go рядом с пунктом ‘Manage: Надстройки Excel’
В появившемся окне установите флажок напротив Analysis Toolpak , а затем нажмите OK
Если вы правильно добавили пакет инструментов анализа данных в Excel, вы увидите кнопку Анализ данных на вкладке Данные , сгруппированную в разделе Анализ :
Если вы хотите получить еще больше возможностей, обязательно ознакомьтесь с другими надстройками Excel
Описательная статистика в Excel
Независимо от того, какой статистический тест вы проводите, вы, вероятно, захотите сначала получить описательную статистику Excel. Это даст вам информацию о средних значениях, медианах, дисперсии, стандартном отклонении и ошибке, эксцессе, перекосе и множестве других показателей
Запустить описательную статистику в Excel очень просто. Нажмите Анализ данных на вкладке ‘Данные’, выберите Описательная статистика и выберите диапазон ввода. Щелкните стрелку рядом с полем диапазона ввода, щелкните и перетащите, чтобы выбрать данные, и нажмите Enter (или щелкните соответствующую стрелку вниз), как показано в GIF ниже
После этого не забудьте указать Excel, есть ли у ваших данных метки, хотите ли вы получить результат на новом листе или на том же, а также хотите ли вы получить сводную статистику и другие параметры
После этого нажмите OK , и вы получите описательную статистику:
t-тест Стьюдента в Excel
t-тест является одним из самых основных статистических тестов, и его легко рассчитать в Excel с помощью пакета инструментов. Нажмите кнопку Анализ данных и прокрутите вниз, пока не увидите опции t-теста
У вас есть три варианта:
- t-Test: Paired Two Sample for Means следует использовать, если ваши измерения или наблюдения были парными. Используйте его, когда вы проводили два измерения у одних и тех же субъектов, например, измеряли кровяное давление до и после вмешательства.
- t-Test: Two-Sample Assuming Equal Variances следует использовать, когда ваши измерения независимы (что обычно означает, что они были проведены на двух разных группах испытуемых). Мы обсудим часть ‘равные вариации’ в ближайшее время.
- t-Test: Two-Sample Assuming Unequal Variances также предназначен для независимых измерений, но используется, когда ваши вариации неравны.
Чтобы проверить, равны ли вариации двух выборок, необходимо выполнить F-тест. Найдите F-Test Two-Sample for Variances в списке Analysis Tools, выберите его и нажмите OK
Введите два набора данных в поля диапазона ввода. Оставьте значение альфа на 0. 05, если у вас нет причин его менять – если вы не знаете, что это значит, просто оставьте. Наконец, нажмите OK
Excel выдаст вам результаты на новом листе (если вы не выбрали Выходной диапазон и ячейку на текущем листе):
Здесь вы смотрите на P-значение. Если оно меньше 0,05, то у вас неравные вариации. Поэтому, чтобы выполнить t-тест, вы должны использовать опцию неравных вариаций
Чтобы выполнить t-тест, выберите соответствующий тест в окне ‘Инструменты анализа’ и выберите оба набора данных так же, как и для F-теста. Оставьте значение альфа на уровне 0,05 и нажмите OK
Результаты включают все, что необходимо сообщить для t-теста: средние значения, степени свободы (df), статистику t и P-значения для тестов с одним и двумя хвостами. Если P-значение меньше 0,05, то две выборки существенно различаются
Если вы не уверены, какой t-тест использовать – однохвостовой или двуххвостовой, ознакомьтесь с этим пояснением от Калифорнийского университета
ANOVA в Excel
Пакет инструментов анализа данных Excel предлагает три типа дисперсионного анализа (ANOVA). К сожалению, он не дает возможности проводить необходимые последующие тесты, такие как Тьюки или Бонферрони. Но вы можете проверить, есть ли связь между несколькими различными переменными
Вот три теста ANOVA в Excel:
- ANOVA: Single Factor анализирует дисперсию с одной зависимой переменной и одной независимой переменной. Это предпочтительнее, чем использовать множественные t-тесты, когда у вас более двух групп.
- ANOVA: двухфакторный с репликацией похож на парный t-тест; он включает несколько измерений на одних испытуемых. Двухфакторная’ часть этого теста указывает на наличие двух независимых переменных.
- ANOVA: Двухфакторный без репликации включает две независимые переменные, но без репликации в измерениях.
Здесь мы рассмотрим однофакторный анализ. В нашем примере мы будем рассматривать три набора чисел, обозначенных как ‘Вмешательство 1’, ‘Вмешательство 2’ и ‘Вмешательство 3’. Чтобы выполнить ANOVA, нажмите Анализ данных , затем выберите ANOVA: однофакторный
Выберите диапазон ввода и не забудьте указать Excel, в каких столбцах или строках находятся ваши группы. Я также выбрал ‘Ярлыки в первой строке’, чтобы имена групп отображались в результатах
После нажатия кнопки OK мы получим следующие результаты:
Обратите внимание, что P-значение меньше 0,05, поэтому мы имеем значимый результат. Это означает, что в тесте есть значительная разница между по крайней мере двумя группами. Но поскольку Excel не предоставляет тестов для определения различий между группами, лучшее, что вы можете сделать, это посмотреть на средние значения, отображаемые в сводке. В нашем примере вмешательство 3 выглядит так, что, скорее всего, именно оно отличается
Это не является статистически обоснованным. Но если вы просто хотите посмотреть, есть ли разница, и выяснить, какая группа, вероятно, ее вызывает, это сработает
Двухфакторный ANOVA более сложен. Если вы хотите узнать больше о том, когда следует использовать двухфакторный метод, посмотрите это видео с Sophia.org и примеры ‘без репликации’ и ‘с репликацией’ с сайта Real Statistics
Корреляция в Excel
Расчет корреляции в Excel намного проще, чем t-тест или ANOVA. С помощью кнопки Анализ данных откройте окно Инструменты анализа и выберите Корреляция
Выберите диапазон ввода, определите группы как столбцы или строки и укажите Excel, есть ли у вас метки. После этого нажмите OK
Вы не получите никаких показателей значимости, но вы можете увидеть, как каждая группа коррелирует с другими. Значение единицы – это абсолютная корреляция, означающая, что значения абсолютно одинаковы. Чем ближе к единице значение корреляции, тем сильнее корреляция
Регрессия в Excel
Регрессия – один из наиболее часто используемых статистических тестов в промышленности, и Excel обладает удивительной мощностью для этих расчетов. Здесь мы проведем быструю множественную регрессию в Excel. Если вы не знакомы с регрессией, ознакомьтесь с руководством HBR по использованию регрессии в бизнесе
Допустим, наша зависимая переменная – кровяное давление, а две независимые переменные – вес и потребление соли. Мы хотим узнать, какой из них лучше предсказывает кровяное давление (или оба они хороши)
Нажмите Анализ данных и выберите Регрессия. На этот раз вам нужно быть внимательным при заполнении полей диапазона ввода. В поле Диапазон ввода Y должна содержаться единственная зависимая переменная. В поле Диапазон ввода X можно включить несколько независимых переменных. Для простой регрессии об остальном можно не беспокоиться (хотя не забудьте сообщить Excel, если вы выбрали метки)
Вот как выглядит наш расчет:
После нажатия кнопки OK вы получите большой список результатов. Здесь я выделил P-значение для веса и потребления соли:
Как вы видите, P-значение для веса больше 0,05, поэтому значимой связи нет.P-значение для соли, однако, ниже 0,05, что указывает на то, что она является хорошим предиктором кровяного давления
Если вы планируете представить данные регрессии, помните, что в Excel можно добавить линию регрессии к диаграмме рассеяния. Это отличное наглядное пособие для данного анализа
Статистика Excel: Удивительные возможности
Хотя Excel не славится своими статистическими возможностями, в нем есть несколько действительно полезных функций, например, инструмент PowerQuery, который удобен для таких задач, как объединение наборов данных.(Узнайте, как создать свой первый сценарий Microsoft Power Query Script). Есть также статистическая надстройка Data Analysis Toolpak, которая действительно раскрывает некоторые из лучших возможностей Excel. Я надеюсь, что вы узнали, как использовать Toolpak, и что теперь вы можете самостоятельно разобраться, как использовать больше его функций
Теперь, когда у вас есть все необходимое, поднимите свои навыки работы с Excel на новый уровень с помощью наших статей об использовании функции ‘Поиск цели’ в Excel для более глубокого анализа данных и о поиске значений с помощью функции vlookup. В какой-то момент вы также захотите узнать, как импортировать данные Excel в Python
Комментировать