Eurotehnik.ru

Бытовая Техника "Евротехник"
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

4 техники анализа данных в Microsoft Excel

4 техники анализа данных в Microsoft Excel

4 техники анализа данных в Microsoft Excel

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

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

Как работать

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

  1. Откройте файл с таблицей, данные которой надо проанализировать.
  2. Выделите диапазон данных для анализа.
  3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
  4. Должно появиться диалоговое окно «Создание сводной таблицы».
  5. Настройте отображение данных, которые есть у вас в таблице.

Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.

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

Можно её детализировать, например, по странам. Переносим «Страны».

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

Инструмент Excel для анализа данных

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

Если вы наблюдаете Excel на своем ноутбуке или компьютере, вы можете не увидеть опцию анализа данных по умолчанию. Вы должны раскрыть это. Обычно пакет инструментов анализа данных доступен на вкладке «Данные».

Под опцией «Анализ данных» мы видим множество вариантов анализа.

Дайте волю инструменту анализа данных в Excel

Если ваш Excel не показывает этот пакет, выполните следующие шаги, чтобы раскрыть эту опцию.

Шаг 1: Перейти к файлу.

Шаг 2. В разделе «Файл» выберите « Параметры» .

Шаг 3: После выбора параметров выберите Надстройки .

Шаг 4: Как только вы нажмете на Надстройки, в нижней части вы увидите раскрывающийся список Управление. Выберите надстройки Excel и нажмите « Перейти» .

Шаг 5: Как только вы нажмете кнопку Go, вы увидите новое диалоговое окно. Вы увидите все доступные инструменты для анализа. Я выбрал 3 из них, а затем нажмите кнопку ОК .

Шаг 6: Теперь вы увидите эти опции в ленте данных.

Как использовать инструмент анализа данных в Excel?

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

Вы можете скачать этот шаблон Excel для инструмента анализа данных здесь — Шаблон Excel для инструмента анализа данных

Анализ Т-теста — Пример № 1

T-тест возвращает вероятность тестов. Посмотрите на приведенные ниже данные о наборе очков двух команд в турнире.

Шаг 1. Выберите параметр « Анализ данных» на вкладке « ДАННЫЕ ».

Шаг 2: Как только вы нажмете «Анализ данных», вы увидите новое диалоговое окно. Прокрутите вниз и найдите T-тест. В T-тесте вы будете три вида T-теста, выберите первый, т. Е. T-тест: два парных образца для средних.

Шаг 3: После выбора первого t-теста вы увидите ниже варианты.

Читайте так же:
Как в фотошопе добавить картинку на слой

Шаг 4: В разделе «Переменная 1» выберите оценку команды 1, а в разделе «Переменная 2» выберите оценку команды 2.

Шаг 5: Диапазон вывода выбирает ячейку, в которой вы хотите отобразить результаты.

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

Шаг 7: Из ячейки D1 ​​начнется показ результатов теста.

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

Если вы P (T <= t) два хвоста, это 0, 314, что выше, чем стандартное ожидаемое значение P 0, 05. Это означает, что данные не являются значимыми.

Мы также можем сделать T-тест, используя встроенную функцию T.TEST.

Вариант SOLVER — Пример № 2

Решатель — это не что иное, как решение проблемы. SOLVER работает как цель в Excel.

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

Количество проданных единиц составляет 7550 по цене продажи 10 за единицу. Общая стоимость 52500, а общая прибыль 23000.

Как владелец, я хочу заработать 30000, увеличив цену за единицу. На данный момент я не знаю, какую цену за единицу я должен увеличить. СОЛВЕР поможет мне решить эту проблему.

Шаг 1: Откройте SOLVER на вкладке DATA .

Шаг 2: Установите целевую ячейку как B7 и значение 30000 и изменив ячейку на B2 . Поскольку у меня нет других специальных критериев для тестирования, я нажимаю кнопку «Решить».

Шаг 3: Результат будет таким, как показано ниже:

Хорошо, Excel SOLVER решил проблему для меня. Чтобы получить прибыль в 30000, мне нужно продавать продукты по 11 за единицу вместо 10 за единицу.

Таким образом, мы можем сделать анализ данных.

Что нужно помнить об инструменте анализа данных в Excel

  • У нас есть много других аналитических тестов, таких как регрессия, F-тест, ANOVA, корреляция, описательные методы.
  • Мы можем добавить надстройку Excel в качестве пакета инструментов анализа данных.
  • Пакет инструментов анализа также доступен в VBA.

Рекомендуемые статьи

Это было руководство к инструменту анализа данных в Excel. Здесь мы обсудим, как использовать инструмент анализа данных Excel вместе с примерами Excel и загружаемым шаблоном Excel. Вы также можете посмотреть на эти полезные статьи в Excel —

Основные возможности Minitab

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

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

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

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

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

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

Читайте так же:
Как в ворде сделать видимые поля

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

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

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

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

Часто задаваемые вопросы

Excel — это инструмент анализа данных?

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

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

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

SQL — это инструмент анализа данных?

Да, SQL — это язык программирования, который легко организует структурированные данные.

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

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

Читайте так же:
Можно ли заизолировать провода скотчем

Доступно множество систем управления реляционными базами данных, таких как MySQL, PostgreSQL, MS SQL и Oracle, и изучение ресурсов этих аналитиков данных было бы чрезвычайно полезным для любого серьезного аналитика. Мы сконцентрируемся на MySQL Workbench, потому что он самый популярный. Поскольку SQL используется для хранения большинства структурированных данных, программы, написанные на этом языке, могут легко разблокировать данные для получения мощных результатов.

Что такое инструменты и методы анализа данных?

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

1. Определите форму данных для анализа.
Вы должны сначала определить, какие данные вы хотите анализировать, прежде чем выбирать инструмент анализа данных. Информация количественная или качественная? Количественные данные, которые в основном представляют собой числовые данные, содержащиеся в электронных таблицах и базах данных, легко превратить в визуальную информацию с помощью программного обеспечения для обработки данных в Excel и инструментов бизнес-аналитики, таких как Tableau.

2. Определите, кто будет отвечать за управление данными.
Есть ли у вас группа специалистов по обработке данных и инженеров, знакомых с SQL, R и Python? Эти языки программирования часто используются в инструментах обработки данных и легко понятны тем, у кого есть опыт программирования.

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

Курс длится 10 недель. На каждой неделе курса, кроме 5 и последней, слушатели выполняют тест. К концу курса слушатели должны пройти 8 тестов и выполнить два задания на взаимное оценивание. Тест на каждой неделе содержит 15 вопросов с пятью вариантами ответа. Верный ответ на каждый вопрос оценивается в 1 балл (неверный – 0 баллов). Таким образом, за прохождение теста на каждой неделе можно набрать от 0 до 15 баллов. На ответ на каждый вопрос дается одна попытка.

Итоговая оценка за тесты складывается из оценок за 8 тестов, взятых с одинаковым весом и нормируется к 100, т.е. ((сумма оценок за 8 тестов)/(15*8))*100. На 5 и 10-ой неделях слушатели выполняют практическое задание с данными на взаимное оценивание и пишут краткий отчет (эссе), которое взаимно оценивается из 32 и 48 баллов соответственно, а затем нормируется к 100 ((результат за эссе1/32)*100 и (результат за эссе2/48)*100).

Итоговая оценка = Итоговая оценка за тесты*0,4+оценка за эссе1*0,1 + оценка за эссе2*0,1 + Оценка за экзамен с прокторингом*0,4.

Экзамен с прокторингом

Экзамен с прокторингом реализуется в виде теста, содержащего 40 вопросов. За каждый вопрос можно набрать либо 0, либо 1 балл. Таким образом, максимальное количество баллов за экзамен равно 40 (100%). Каждый вопрос имеет пять вариантов ответа. Продолжительность экзамена — 120 минут

Для выполнения практических заданий необходимо установить Программный пакет Gretl: http://gretl.sourceforge.net/ru.html#man

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

Разберем, как на практике проводить ABC-анализ продаж продукции в Excel для магазина сотовых телефонов. Для этого нам необходимо наличие наименование всех товаров (групп товаров) и их норма дохода. На рисунке ниже показана номенклатура товаров и размер прибыли на каждый из видов.

ABC анализ продаж продукции предприятия в Excel

Номенклатура продукции для проведения ABC-анализа в Excel

Далее необходимо осуществить сортировку товаров по прибыльности. Заходим в главном меню Excel → «Данные» → «Сортировка». Результатом будет сортировка групп товаров по рентабельности от самого прибыльного до самого убыточного.

На следующем этапе необходимо определить долю по каждому виду товара. Для этого воспользуемся формулами в Excel.

Читайте так же:
Можно ли использовать оперативную память разного объема

Доля продаж каждого вида товара =B5/СУММ($B$5:$B$15)

Пример проведения ABC анализа продаж в Excel

Определение доли продукции в объеме продаж компании

На следующем этапе рассчитывается доля групп накопительным итогом по формуле:

Доля товара в номенклатуре накопительным итогом =C6+D5

Пример расчета ABC-анализа в Excel для компании

Оценка доли прибыли накопительным итогом для группы товаров

После этого необходимо определить границу до 80% для группы товаров «А», 80-95% для группы товаров «В» и 95-100% для товаров «С». На рисунке ниже представлен результат группировки товаров по трем группам для магазина сотовых телефонов. Так марки Samsung, Nokia, Fly и LG дают 80% всех продаж, Alcatel, HTC, Lenovo обеспечивают 15% продаж и Philips, Sony, Apple, ASUS приносят 5% выручки от реализации.

ABC анализ продаж продукции. Пример расчета в Excel

ABC анализ продаж продукции. Пример расчета в Excel

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

30% всех товаров приносят компании 80% прибыли.

Анализ данных с pandas

Анализ данных сегодня становится все более и более популярным. Нынче аналитику данных нужно не только владеть Excel’ем, а и иметь навыки работы с языком Python как минимум. «Питон» имеет огромное множество библиотек для анализа данных, самой популярной из которых является pandas.

Введение

Pandas — библиотека для анализа и обработки данных. Работа с этой библиотекой происходит над массивами NumPy. Основные два класса в Pandas — Series и DataFrame. DataFrame содержит множество объектов Series и имеет множество методов для обработки данных в ним.

Сегодня мы рассмотрим базовые функции pandas на примере набора данных «Iris», пару способов нормализации числовых значений и визуализации данных.

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

Начало работы

Использовать для работы мы будем Jupyter Notebook — инструмент, который в представлении не нуждается.

Для начала импортируем все необходимые модули:

Импортируем pandas, matplotlib.pyplot для построения зависимостей между признаками, numpy для работы с массивами и сам датасет.

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

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

  • класс — определенная метка, к которому мы будем относить набор
  • признак — названия «полей» набора

Вот что значат данные метки:

  • data — двумерный массив с наборами чисел
  • target_names — названия классов, между которыми мы распределяем наборы
  • target — одномерный массив с числовыми значениями меток классов
  • feature_names — массив с названиями признаков
  • DESCR(ук. от description) — описание набора. Очень рекомендуется к прочтению.

Создаём объект DataFrame из полученного набора:

Здесь мы передаём сами значения, а в именнованом параметре columns указываем названия столбцов таблицы. Также мы воспользовались методов DataFrame.info(), который выведет нам исчерпывающую информацию о наборе. Отсюда мы можем узнать, что набор содержит 150 строк, 4 колонки, узнаем имена признаков(мы их знали изначально, однако если бы загружали датасет из .csv или .json файла, это могло бы нам пригодится). Также получим информацию, что все столбцы не содержат значений NaN(Not a Number) и принадлежат к типу float64.

Затем дополним наш DataFrame столбцом меток классов:

Здесь мы воспользовались методом DataFrame.head(), в который можно передать числовое значение(по умолчанию 5), которое указывает на кол-во строк таблицы, которые необходимо вывести на экран.

Анализ набора

Чтобы узнать, какие уникальные значения содержит отдельный столбец, воспользуемся методом Series.value_counts().

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

Читайте так же:
Как восстановить предыдущую сессию в гугл хром

Столбец Class содержит три уникальных значения, по 50 раз каждое.

Чтобы узнать минимальное и максимальное значения столбца, воспользуемся методами Series.min() и Series.max() соответственно.

Теперь построим матрицу рассеивания, используя под-модуль pandas.plotting.

Передаем в качестве аргументов сам DataFrame, прозрачность и размер фигуры.

Использовав магическую команду IPython, выводим изображение прямо в JupyterNotebook.

Здесь мы видим зависимости между каждыми двумя признаками. Меня наиболее заинтересовали признаки ‘sepal length (cm)’ и ‘petal length (cm)’.

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

Здесь мы можем заметить множество точек, которые находятся отдалённо от других. Сразу появляется подозрение, что эти точки принадлежат к одному классу, что означает легкое отделение этого класса от других.

Заметим, что данные точки имеют довольно маленькое значение sepal lenght(картинка слева внизу), что мы докажем, выполнив фрагмент кода ниже:

Здесь мы использовали выбор по маске(оператор == переопределён в DataFrame, возвращает массив с булевыми значениями), передав его в квадратные скобки, выбрав все строки, которые относятся к классу 0.

Как мы можем заметить, первый класс имеет довольно маленькое значение признака sepal lenght — всего 5.8, когда весь набор имеет максимальное значение, равное 7.9.

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

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

Получаем нечто такое:

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

Нормализация

Нормализация — приведение числовых значений к определённому диапазону.

Зачем это нужно? Множество метрические алгоритмы вычисляют расстояние между двумя точками с помощью эвклидой метрики.

Например, возьмём точки (1, 1) и (100, 1). Расстояние между ними будет:(

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

Сначала воспользуемся линейной нормализацией. Формула такова:

, где Xik — текущий элемент числового набора,

Xmin — минимальный элемент набора,

Xmax — максимальный элемент.

Для упрощения работы найдём список названий столбцов, воспользовавшись полем DataFrame.columns.values.

Возвращаясь к нашему набору, создадим новый объект DataFrame с помощью линейной нормализации:

Здесь из неизвестного только метод массива NumPy(именно в нём хранятся названия столбцов) aray.tolist(), который возвращает список значений, хранящиеся в этом массиве.

Следующий вид нормализации — нелинейная. Формула выглядит так:

, где Xc — среднее арифметическое между максимальным и минимальным значениями набора. Параметр а влияет на нелинейность.

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

Передав в функцию NumPy.exp массив, мы получим новый массив, который нам и нужен.

Новый диапазон значений первого столбца:

Заключение

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

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

Весь код из статьи модно найти тут.

Также рекомендую прочитать статью Базовые алгоритмы с помощью Python и подписывайтесь на мой Telegram-канал. Там еще больше полезных и интересных материалов для разработчиков

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