Mini-ats102.ru

ООО “Мультилайн”
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как сделать базовый анализ данных в Excel

Как сделать базовый анализ данных в Excel

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

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

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

СЧИТАТЬ, НЕ ПЕРЕСЧИТАТЬ

Теоретический блок пройден, основы изучены. Можем переходить к самому расчёту. И у меня для Вас две новости.

Плохая – я не дам Вам формул для расчёта. Хорошая – я Вам дам готовую excel-таблицу для внедрения.

Поэтому объясню основные смыслы, а расчёт в экселе сделается самостоятельно, когда Вы проставите цифры.

1. Сбор информации

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

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

  1. Клиенты/товары
  2. Сумма продаж
  3. Количество продаж
  4. Маржинальность

Эти данные Вы собираете за выбранный промежуток времени. Чем он будет длиннее, тем лучше. Так как Вы будете видеть динамику компании.

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

Интересно. Упростить все процессы в розничном магазине поможет сервис Cloudshop. Внутри: складской учет, автоматизация рабочего места кассира, готовая онлайн-касса, поддержка дисконтных карт и база покупателей, а также многое другое. Кликайте -> Cloudshop (по промокоду “PDGRY1” + 600руб. на тестирование).

2. Анализ данных

После того как Вы собрали всю информацию воедино, начинается самая скучная работа – это всё нужно расформировать по необходимым столбцам.

Процесс этот не быстрый, но тем не менее не требует особых знаний. Вы, конечно, можете пойти более умным путём и сразу сделать так, чтобы Ваша CRM-система выгружала такой готовый отчет. Как говорится, всё в Ваших руках. Главное, чтобы был результат.

abc и xyz клиентов анализ данных

Интеграция анализов

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

Именно он покажет Вам синергию ABC-анализа и XYZ-анализа. Как Вы уже догадались, для Вас самый лучшие клиенты и товары – это AX (покупают много и часто), а самые плохие – CZ/DZ (покупают мало и редко/нерентабельны).

Кстати. Если Вы решили использовать CRM, то рекомендую Мегаплан. Кликайте по ссылке и получайте 14 дней бесплатного периода -> megaplan.ru

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

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

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

3. Обновление и миграция

Ваша компания развивается, у Вас меняются менеджеры, продукты, условия. Меняется всё, кроме Вас – руководителя ;-).

Читайте так же:
Метеостанция домашняя с беспроводным датчиком как выбрать

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

То же самое касается клиентов. Помимо того, что к Вам постоянно приходят новые, старые тоже меняются.

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

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

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

abc и xyz группы клиентов

Миграция клиентов

Чтобы оценить потенциал клиента для миграции в более “хорошую” группу, Вам нужно следовать двум стратегиям.

В B2B рынке Вам нужно узнать у своих клиентов что и сколько они покупают у конкурентов (+ что бы хотели покупать).

В B2C рынке просто нужно понять, сколько клиент берёт продукта у Вас (может он берёт у Вас 50% и 50% у конкурента), исходя из его нормы.

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

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

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

Вариант 1: Вызов через Мастер функций

В отличии от некоторых других типов анализов, корреляционный анализ можно вызвать с помощью функций. За него отвечает функция КОРРЕЛ вида: КОРРЕЛ(массив1;массив2):

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

Вариант 2: Применение пакета анализа

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

    Перейдите во вкладку “Файл”, что расположена в верхней части окна.

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

Четыре метода, которые можно использовать для анализа трендов в Excel

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

  • Графическое изображение
  • Использование средних (или использование скользящих средних)
  • Использование функции прогноза и тренда
  • Изучение данных с помощью выполнения регрессионного анализа

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

Квебек: Продажи автомобилей с 1960-1968 (Скачать)

Графическое изображение — визуальное представление и построение графика

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

Построить данные можно, перейдя на вкладку Вставка, затем в Диаграммы и Вставить линейная диаграмма; Кроме того, вы также можете нажать Alt + N + N + Enter, чтобы вставить эту диаграмму.

Читайте так же:
Мода в excel формула

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

Анализ тренда Excel — Скользящие средние

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

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

Скользящие средние

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

Скользящие средние

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

Trend Analysis Excel — Использование функций прогноза и тренда

Большинство не знали об этом, но в Excel есть встроенная функция, которая может делать прогнозирование. Чтобы использовать эту функцию прогноза (например, FORECAST () FUNCTION), вам необходимо использовать второй лист данных со значениями из научного эксперимента. Если вы выбрали правильный вариант, вы должны увидеть таблицу, которая выглядит примерно так:

Использование функций прогноза и тренда

В этом синтаксисе мы можем использовать функцию FORECAST

ПРОГНОЗ (x, known_x’s, known_y’s)

Отсюда вам нужно направиться к строке формул, ввести формулу, упомянутую выше, и ввести следующие аргументы:

  • X — это значение, по которому вы будете делать прогноз
  • known_x’s — известные значения x
  • known_y’s — это известные значения y по отношению к соответствующим значениям x

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

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

TREND (известные_y, известные_x, x, [константа])

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

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

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

В выводе, который вы видите ниже, у нас есть эти термины, которые мы можем использовать мгновенно:

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

Скорректированный R-квадрат и R-квадрат: эти два объяснят, насколько хорошо эта модель объяснена в этих данных. Мы уверены, что наши данные объяснены на 95% и 90%; это признак позитива. Переменная X и Перехват также объясняют уравнение для которого представлено:

Y = 8.8 X -19.15

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

Читайте так же:
Замена word и excel

Функции предсказания в Excel

Excel, как универсальный табличный редактор, давно и неплохо справляется с большинством задач прогнозирования (см. список литературы в конце заметки). Однако, не всегда вычисления в Excel являются простыми и понятными. И вот в версии 2016 года разработчики Microsoft добавили семейство функций ПРЕДСКАЗ (FORECAST), которые позволяют в несколько кликов решать большой круг задач прогнозирования на основе экспоненциального сглаживания.

Рис. 1. Прогнозирование продаж в Excel с помощью семейства функций ПРЕДСКАЗ

Скачать заметку в формате Word или pdf, примеры в формате Excel

Об экспоненциальном сглаживании

Экспоненциальное сглаживание также известно, как метод ETS: ошибки (Errors), тренд (Trend), сезонный фактор (Seasonal). Для составления прогноза используются все исторические данные, но коэффициенты, определяющие вклад, убывают в прошлое по экспоненте (отсюда и название). Это позволяет, с одной стороны, чутко реагировать на свежие данных, с другой стороны, сохранять информацию об историческом поведении всего временного ряда. Если данным присущ тренд, он вычисляется в каждой точке данных (а не на основе регрессии всего временного ряда). Наконец, с помощью автокорреляции в данных выявляется сезонность.

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

Собственно, оптимизируются три коэффициента:

α – разброс относительно среднего

Разработчики Microsoft не предоставили пользователям возможность влиять на выбор коэффициентов, за исключением периода сезонности (об этом ниже).

Обзор функций семейства ПРЕДСКАЗ

В Excel представлено 5 функций:

Рис. 2. Семейство функций ПРЕДСКАЗ в Excel

ПРЕДСКАЗ.ETS рассчитывает будущее значение на основе существующих (ретроспективных) данных методом экспоненциального сглаживания. Т.е., дает прогноз одним числом.

ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ возвращает доверительный интервал для прогнозной величины. Доверительный интервал следует отложить по обе стороны от среднего значения. Вместе с ПРЕДСКАЗ.ETS позволяет построить «коридор» прогноза.

ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ возвращает длину повторяющегося фрагмента, обнаруженного в заданном временном ряду. Например, 12, если исторические данные представляют из себя продажи за месяц.

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

ПРЕДСКАЗ.ЛИНЕЙН вычисляет будущее значение с помощью линейной регрессии исторических данных. До версии 2016 в Excel вместо семейства функций была единственная функция ПРЕДСКАЗ, которая работала также, как и ПРЕДСКАЗ.ЛИНЕЙН. Функция ПРЕДСКАЗ оставлена для обратной совместимости, но скоро перестанет поддерживаться. Далее в заметке ПРЕДСКАЗ.ЛИНЕЙН не рассматривается, так как не относится к функциям, использующим алгоритм экспоненциального сглаживания.

ПРЕДСКАЗ.ETS

В качестве примера рассмотрим месячный пассажиропоток в аэропорту (пример от MS). Исторические данные были собраны за период с января 2009 по декабрь 2912 г.

Рис. 3. Исторические данные

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

Рис. 4. Прогнозные значения на основе функции ПРЕДСКАЗ.ETS

Подробнее о формуле в ячейке С50:

Первый аргумент – целевая_дата = А50 – янв.13, т.е., в ячейке С50 ищется прогноз пассажиропотока для января 2013 г. Ссылка относительная, что позволит при протягивании функции вниз по столбцу ссылаться на новое значение: в С51 – на А51, в С52 – на А52 и т.д.

Читайте так же:
Как в ворде выровнять страницы в содержании

Второй аргумент – значения = $B$2:$B$49. Здесь расположены исторические данные пассажиропотока. Ссылка абсолютная, чтобы при протягивании формулы ячейки, на которые ссылаются не изменились.

Третий аргумент – временная_шкала = $A$2:$A$49. Здесь расположены даты временной шкалы или номера периодов. Важно чтобы они отстояли друг от друга на фиксированный интервал. Если интервал не будет фиксированным, Excel всё еще будет исходить из гипотезы, что интервал фиксированный, а некоторые данные пропущены. Как обрабатываются такие ситуации описано ниже. Сортировать массив по значениям временной шкалы не обязательно, так как ПРЕДСКАЗ.ETS сама отсортирует данные прежде, чем выполнить расчеты.

Четвертый аргумент – [сезонность] = 1. Это необязательный аргумент. Значение по умолчанию равно 1. Для него Excel автоматически определяет сезонность и использует положительные целые числа в качестве длины сезонного шаблона. Значение 0 предписывает не использовать фактор сезонности, в результате чего прогноз будет линейным. Если для этого параметра задано положительное целое число, алгоритм использует его в качестве длины шаблона сезонности. Например, вы знаете, что сезонность равна 4 (квартальная периодичность), но предполагаете, что она слабая, и автоматический алгоритм Excel может ее не выявить, и будет считать, что сезонности нет. Для начала я рекомендовал бы использовать значение по умолчанию.

Пятый аргумент – [заполнение_данных] = 1. Это необязательный аргумент. Хотя временная шкала требует постоянный шаг между точками данных, FORECAST.ETS поддерживает до 30% отсутствующих данных и автоматически настраивает их. 0 указывает, что алгоритм учитывает отсутствующие точки в качестве нулей. Если задано значение 1 (вариант по умолчанию), функция определяет отсутствующие значения как среднее между соседними точками.

Шестой аргумент – [агрегирование] – в нашем примере опущен. Это необязательный аргумент. Он нужен, если даты временной шкалы или номера периодов содержат дубли. Функция ПРЕДСКАЗ.ETS выполнит агрегирование точек с одинаковой меткой времени. Параметр агрегирования — это числовое значение, определяющее способ агрегирования нескольких значений с одинаковой меткой времени. Для значения по умолчанию 0 используется метод СРЗНАЧ; также доступны варианты СУММ, СЧЁТ, СЧЁТЗ, МИН, МАКС и МЕДИАНА.

SQL расшифровывается как Structured Query Language , что переводится как « язык структурированных запросов ».

Но я люблю другое описание: это Excel на стероидах. Вот как выглядит один и тот же набор данных в Excel и SQL :

SQL для анализа данных? Что это? Какая от него польза?

Как мы видим, SQL и Excel довольно похожи. Оба представляют данные в виде двухмерной таблицы со столбцами и строками. Оба предельно структурированы и просты для понимания.

Но между ними есть и ряд отличий:

  1. Первое — это производительность . Excel отлично работает с небольшими объёмами данных, но когда у вас 100 000 строк, использование формул становится долгим и неэффективным занятием.
  2. Второе — это способ взаимодействия с базой данных . Excel — это, в первую очередь, графический интерфейс пользователя ( GUI ). Он позволяет прокручивать таблицу, вводить формулы, курсором необходимые области. В SQL нет графического интерфейса — здесь используются так называемые SQL-запросы .

Когда вы освоите SQL запросы для начинающих , то поймёте, что работа с ним прозрачнее и эффективнее, чем с Excel. Кроме этого, в SQL намного удобнее автоматизировать свою работу и использовать предыдущие наработки.

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

А как насчёт Python, R или bash?

Когда вы начнёте использовать эти языки для анализа данных, то поймёте, что Python и R хороши для одного, а SQL для другого. Основное различие этих языков — в синтаксисе, « функциях » и производительности. Ну а теперь давайте перейдём к практической части!

Читайте так же:
Где клавиша option на macbook pro

Шаг 0 – установите SQL-систему управления базами данных

Я буду использовать postgreSQL . Есть и другие виды СУБД SQL . Но все они похожи, поэтому если выучить postgreSQL , адаптация к другому языку займёт всего несколько часов (или даже минут).

На данный момент у вас должно быть три вещи:

  1. Сервер данных с доступом через терминал или iTerm ;
  2. PostgreSQL , установленный на ваш сервер;
  3. Установленный на компьютер Pgadmin4 ( или SQL Workbench ).

Примечание : в качестве инструмента для работы с SQL я буду использовать SQL Workbench .

Шаг 1 – Зайдите в свою базу данных SQL через командную строку

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

  1. Откройте Терминал ( или iTerm ):
  2. Подключитесь к серверу через SSH .

В моём случае я ввожу:

Шаг 1 – Зайдите в свою базу данных SQL через командную строку

  1. Зайдя на сервер, я получаю доступ к базе данных postgreSQL . Поскольку уже разрешил доступ своему пользователю, ввожу следующую команду:

psql это сама команда, -U указывает на имя пользователя ( в моём случае это “tomi” ), а -d указывает название базы данных ( в моём случае postgres , как и у вас ). Приглашение командной строки должно измениться на следующее:

Шаг 1 – Зайдите в свою базу данных SQL через командную строку - 2

Готово! Теперь у вас есть полный доступ к базе данных SQL .

  1. Для проверки SQL запроса можете ввести:

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

Примечание : базы данных SQL также называют « реляционными базами данных ».

Шаг 2 – Загружаем данные

В этом разделе мы будем работать с небольшим набором данных под названием zoo (« зоопарк »). Можете скачать его отсюда в текстовом формате .tsv .

Задача: анализ большого объема данных

Определение big data для анализа больших данных в бизнесе указывает на наличие четко определенных целей и задач проведения данной процедуры:

Определение анализа данных

В исследовании компании IBS говорится, что уже к 2003 году в мире было накоплено 5 эксабайтов данных (1 ЭБ = 1 млрд гигабайтов), в 2011 году данных было уже 1,76 зеттабайта (1 ЗБ = 1024 эксабайта), а в мае 2015 года мировой объем данных составил более 6,5 ЗБ. По информации из исследования The Data Age 2025, прогнозируется, что к 2025 году будет сформировано более 400 зеттабайтов информации.

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

Решения для анализа варьируются в зависимости от стоимости внедрения и персонала, который будет задействован для управления ими. Есть решения для крупных корпораций, где не обойтись без больших вложений – как на этапе внедрения, так и позже – на этапе работы решения (основная трата – это специалисты, работающие с ПО). Для небольших, средних и малых компаний есть решения типа Tableau: стоимость их внедрения минимальна (от 2000 руб. в месяц – на начало 2018 года), с ними не требуется специальных знаний – сделать красивый интерактивный отчет сможет любой пользователь с начальными знаниями компьютера.

Мы, компания АНАЛИТИКА ПЛЮС, с 2012 года помогаем нашим клиентам работать с данными – находить полезные инсайты и использовать эту информацию для увеличения прибыли компании.

За это время мы разработали и внедрили решения для различных отраслей и направлений бизнеса:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector