Как сделать экспресс анализ в excel

Обновлено: 04.07.2024

Понятие бизнес-аналитики достаточно обширно и нередко трактуется по-разному.

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

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

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

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

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

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

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

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

На вкладке Конструктор пользователь может выбрать форматы и стили создаваемой сводной таблицы, а также макет отчета по ее данным.

Обратите внимание!

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

Функция ВПР табличного редактора Excel помогает консолидировать данные для бизнес-анализа тем пользователям, которые недостаточно хорошо знают функционал сводных таблиц.

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

К сведению

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

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

Для наглядности приведем еще пример локального применения функции ВПР при решении задачи построения оперативного бизнес-отчета из собственной практики.

Функция ЕСЛИ, предусмотренная функционалом Excel, также популярна у бизнес-аналитиков, но она применяется чаще всего не при анализе информации, а при построении различного рода прогнозов и сценариев результатов деятельности компании. Суть функции в том, что в заданной ячейке выводится один результат при выполнении определенного условия и другой — при невыполнении этого условия.

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

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

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

Добавление пакета анализа данных Excel

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

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

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

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

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

После этого нажмите ОК , и вы получите описательную статистику:

Студенческий т-тест в Excel

У вас есть три варианта:

Чтобы проверить, равны ли отклонения ваших двух выборок, вам нужно запустить F-тест. Найдите F-Test Two-Sample для отклонений в списке инструментов анализа, выберите его и нажмите OK .

Введите два набора данных в поля ввода диапазона. Оставьте альфа-значение на уровне 0,05, если у вас нет причин для его изменения — если вы не знаете, что это значит, просто оставьте. Наконец, нажмите ОК .

Excel выдаст вам результаты на новом листе (если вы не выбрали Выходной диапазон и ячейку на текущем листе):

Вы смотрите на P-значение здесь. Если оно меньше 0,05, у вас неравные отклонения . Таким образом, чтобы запустить t -test, вы должны использовать опцию неравных отклонений.

Чтобы запустить t -тест, выберите соответствующий тест в окне инструментов анализа и выберите оба набора данных таким же образом, как вы делали для F-теста. Оставьте значение альфа на 0,05 и нажмите ОК .

Результаты включают все, что вам нужно сообщить для t- теста: средние значения, степени свободы (df), t-статистику и P-значения для одно- и двусторонних тестов. Если значение P составляет менее 0,05, два образца значительно различаются.

Если вы не уверены, следует ли использовать одно- или двусторонний t- тест, обратитесь к этому объяснителю из UCLA .

ANOVA в Excel

Пакет инструментов анализа данных Excel предлагает три типа дисперсионного анализа (ANOVA). К сожалению, это не дает вам возможности запустить необходимые дополнительные тесты, такие как Tukey или Bonferroni. Но вы можете увидеть, есть ли связь между несколькими разными переменными.

Вот три теста ANOVA в Excel:

После нажатия OK мы получаем следующие результаты:

Обратите внимание, что значение P меньше 0,05, поэтому мы получаем значительный результат. Это означает, что есть существенная разница между по крайней мере двумя группами в тесте. Но поскольку Excel не предоставляет тесты для определения того, какие группы отличаются, лучшее, что вы можете сделать, это посмотреть на средние значения, отображаемые в сводке. В нашем примере Intervention 3 выглядит так, как будто она отличается.

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

Корреляция в Excel

Выберите диапазон ввода, определите группы в виде столбцов или строк и скажите Excel, есть ли у вас метки. После этого нажмите ОК .

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

Регрессия в Excel

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

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

Вот как выглядит наш расчет:

После нажатия OK вы получите большой список результатов. Я выделил P-значение здесь для веса и потребления соли:

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

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

Статистика Excel: удивительно способна

Хотя Excel не известен своей статистической мощью, он на самом деле обладает некоторыми действительно полезными функциями, такими как инструмент PowerQuery , который удобен для таких задач, как объединение наборов данных . (Узнайте, как создать свой первый сценарий Microsoft Power Query Script .) Существует также дополнение статистики для Data Analysis Toolpak, которое действительно раскрывает некоторые из лучших функций Excel. Я надеюсь, что вы узнали, как использовать Toolpak, и что теперь вы можете поиграть самостоятельно, чтобы выяснить, как использовать больше его функций.

Шаги по загрузке надстройки Data Analysis Toolpak

Шаг 3 — Нажмите на ‘Надстройки’ а затем выберите ‘Надстройки Excel’ за ‘Управление’. Нажмите на ‘Идти.’

Шаг 5 —Команда ‘Анализ данных’ появится под данные’ вкладка в Excel в крайнем правом углу ленты, как показано ниже.

Список функций, доступных в пакете инструментов анализа данных Excel

Ниже приведен список доступных функций в надстройке Excel Toolpak для анализа:

ANOVA: однофакторный анализ в Excel
Корреляция в Excel
Рейтинг и процентиль в Excel
Описательная статистика в Excel

Теперь давайте подробно обсудим каждый из них —

ANOVA (дисперсионный анализ) — это первый набор опций, доступных в надстройке Excel Toolpak для анализа. В одностороннем ANOVA мы анализируем, есть ли какие-либо статистические различия между средними значениями трех или более независимых групп. Нулевая гипотеза предполагает, что статистическая значимость не существует в наборе данных наблюдений. Мы проверяем эту гипотезу, проверяя значение p.

Давайте разберемся в этом на примере ANOVA excel.

пример

Группа А получала 0,62 мг / кг алкоголя.
Группа AC получала алкоголь плюс кофеин.
Группа AR получила алкоголь и денежное вознаграждение за выступление.
Группа P получала плацебо.

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

Как провести тест ANOVA?

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

Шаг 5: Для диапазона вывода мы выбрали F1. Пожалуйста нажмите на ‘ХОРОШО.’

Теперь у нас есть анализ ANOVA.

Чем больше значение F-статистики в Excel, тем более вероятно, что группы имеют разные средние значения, что отвергает нулевую гипотезу о том, что все средние значения равны. F-статистика, превышающая критическое значение, эквивалентна значению p в Excel меньше, чем альфа, и оба значения означают, что мы отвергаем нулевую гипотезу. Отсюда можно сделать вывод, что между группами существует значительная разница.

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

пример

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

Как найти корреляцию между двумя наборами переменных?

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

Как мы видим, корреляция между стоимостью рекламы (заголовок столбца) и продажами (заголовок строки) составляет примерно +0,86274, что указывает на положительную корреляцию и составляет 86,27%. Теперь мы можем соответственно определиться с рекламным бюджетом и ожидаемыми продажами.

Процентиль в Excel — это число, в котором определенный процент оценок ниже этого числа, и доступно в надстройке Excel Toolpak для анализа. Например, если конкретный результат находится в 90-м процентиле, это означает, что учащийся набрал больше, чем 90% людей, прошедших тест. Давайте разберемся в этом на примере.

пример

У нас есть следующие данные для оценок, полученных учеником класса.

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

Как найти ранг и процентиль?

Шаги будут такими:

Мы получили результат как на следующем изображении.

Описательная статистика, включенная в надстройку Excel Toolpak для анализа, содержит следующую информацию об образце:

Основная тенденция
1. Имеется в виду: это называется средним.
2. Медиана: это середина распределения.
3. Режим: это наиболее часто встречающееся число.
1. Спектр: Это разница между наибольшими и наименьшими переменными.
2. Разница: Это показало, насколько далеко разбросаны числа.
3. Среднеквадратичное отклонение: Насколько сильно отклонение от среднего / среднего.
пример

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

Расположение

Блок анализа находится во вкладке Данные на Панели инструментов. Если по каким-то причинам его нет, то сейчас подробно рассмотрим, как включить анализ данных в excel. Для примера воспользуемся редактором 2007 года.
1. Нажимаете кнопку в верхней левой части документа и переходите к Параметрам.
1. Ищете строку Надстройки и в правой части в самом низу нажимаете кнопку Перейти.
Важно! Строка выбора рядом с кнопкой должна содержать Надстройки Excel.
1. В новом диалоговом окне ставите галочку напротив Пакет анализа–VBA, затем нажимаете ОК.
1. Если компонент не установлен, то редактор предложит инсталлировать его. Выбираете ДА.
1. Далее программа проводит процесс установки в автоматическом режиме.
1. После успешной инсталляции надстройки во вкладке Данные появится новый блок.
Для версий редактора 2010,2013,2016 годов процесс включения будет одинаковым, только вместо кнопки Office необходимо перейти к параметрам excel через вкладку Файл.

Возможности анализа данных

Установленный пакет обладает большим количеством инструментов, которые позволяют решать достаточно сложные задачи, используя ресурсы персонального компьютера для обработки данных. Рассмотрим некоторые функции подробнее:
1. Дисперсионный анализ позволяет искать зависимости в массиве данных на основании значимости неких средних значений, при этом может использоваться несколько групп данных. Бывает трех видов: однофакторный, двухфакторный с повторениями и без них.
1. Корреляция нужна для отображения зависимости между двумя и более значениями или группами.
2. Ковариация похожа на корреляцию, однако позволяет построить только линейную зависимость двух случайных величин.
3. Экспоненциальное сглаживание один из основных приемов сглаживания числовых рядов.
4. Анализ Фурье позволяет представить сложные математические функции в более простом виде при помощи тригонометрических функций.
5. Гистограмма обрабатывает данные и подготавливает их для графического отображения в виде прямоугольников с одинаковым интервалом.
6. Генератор случайных чисел добавляет в отмеченный диапазон ячеек случайные числа.
1. Регрессия в глобальном смысле есть отход, но с точки зрения математики отображает зависимость одной переменной от других независимых величин.
2. Выборка – инструмент, который позволяет сделать отбор чисел по определенным параметрам из большого массива данных.
И это далеко не полный перечень инструментов, которые содержит установленный пакет данных.

Прочие инструменты

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

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