Анализ данных в Excel
Microsoft Excel предлагает средства для анализа статистических данных. Такие встроенные функции, как СРЗНАЧ (AVERAGE), МЕДИАНА (MEDIAN) и МОДА (MODE), могут использоваться для проведения анализа данных. Если встроенных статистических функций недостаточно, необходимо обратиться к пакету Анализ данных.
Пакет Анализ данных доступен при каждом запуске Excel. Функции пакета Анализ данных можно использовать точно так же, как и любые другие функции Excel, а чтобы получить к ним доступ, выполните описанные ниже действия:
1. Выберите в меню Сервис команду Анализ данных. При первом выборе этой команды Excel загружает файл с диска. Затем на экране появится окно диалога Анализ данных (рис. 2.19).
2. Чтобы использовать какой-либо из инструментов анализа, выберите его имя в списке и нажмите кнопку ОК.
3. Заполните открывшееся окно диалога. В большинстве случаев это означает задание входного диапазона с данными, которые вы собираетесь анализировать, задание выходного диапазона, куда должны быть помещены результаты, и выбор нужных параметров.
Встроенные статистические функции Microsoft Excel применяются при проведении статистического анализа данных. В данном разделе мы ограничимся обсуждением наиболее часто используемых статистических функций. Кроме них Excel также предлагает более сложные функции ЛИНЕЙН (LINEST), ЛГРФПРИБЛ (LOGEST), ТЕНДЕНЦИЯ (TREND) и РОСТ (GROWTH), которые работают с числовыми массивами.
Для использования Описательная статистика в меню Сервис выберите команду Анализ данных, затем в списке Инструменты анализа окна диалога Анализ данных выберите инструмент Описательная статистика и нажмите кнопку ОК. Появится окно диалога, показанное на рис. 2.20.
Чтобы получить представленную выше таблицу статистических характеристик, установите флажки в области Параметры вывода.
Подобно другим инструментам пакета анализа, Описательная статистика создает таблицу констант. Если эта таблица вас не устраивает, можно получить большинство из перечисленных ниже статистических характеристик с помощью других инструментов пакета анализа или формул с использованием встроенных функций Excel.
В MS Excel имеется возможность графического представления данных в виде диаграммы. Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются каждый раз, когда изменяются данные на листе.
Диаграммы могут использовать данные несмежных ячеек. Диаграмма может также использовать данные сводной таблицы.
Для того чтобы построить диаграмму, выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме; если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, выделите также содержащие их ячейки; нажмите кнопку Мастер диаграмм и следуйте инструкциям Мастера.
Для создания диаграмм из несмежных диапазонов нужно выделить первую группу ячеек, содержащих необходимые данные, удерживая клавишу CTRL, выделить необходимые дополнительные группы ячеек и нажать кнопку Мастер диаграмм.
Большая часть текстов диаграммы, например подписи делений оси категорий, имена рядов данных, текст легенды и подписи данных, связана с ячейками рабочего листа, используемого диаграммой. Если изменить текст этих элементов на диаграмме, они потеряют связь с ячейками листа. Чтобы сохранить связь, следует изменять текст этих элементов в исходных таблицах.
Ряд данных – группа связанных точек данных диаграммы, отображающая значение строк или столбцов листа. Каждый ряд данных отображается по-своему. На диаграмме может быть отображен один или несколько рядов данных. На круговой диаграмме отображается только один ряд данных.
Чтобы изменить текст легенды или имя ряда данных на листе, выберите ячейку, содержащую изменяемое имя ряда, введите новое имя и нажмите клавишу ENTER.
При большом диапазоне изменения значений для разных рядов данных в линейчатой диаграмме или при смещении типов данных (таких, как цена и объем) есть возможность отобразить один или несколько рядов данных на вспомогательной оси. Шкала этой оси соответствует значениям для соответствующих рядов:
– выберите ряды данных, которые нужно отобразить на вспомогательной оси, щелчком мыши;
– установите переключатель в положение По вспомогательной оси.
Для большинства плоских диаграмм можно изменить диаграммный тип ряда данных или диаграммы в целом. Для объемной диаграммы изменение типа диаграммы может повлечь за собой и изменение диаграммы в целом. Порядок преобразования рядов данных в конусную, цилиндрическую или пирамидальную диаграммы:
– выберите диаграмму, которую необходимо изменить, а также ряд данных на ней. Для изменения типа диаграммы в целом на самой диаграмме ничего не нажимайте;
– Диаграмма – Тип диаграммы – на вкладках Стандартные или Нестандартные выберите необходимый тип.
Для использования типов диаграмм конус, цилиндр или пирамида в объемной диаграмме или гистограмме выберите в поле Тип диаграммы в меню Стандартные пункт Цилиндр, Конус или Пирамида, а затем установите значок в поле Применить к.
Процедура изменения цветов, узора, ширины линии или типа рамки для маркеров данных, области диаграммы, области построения, сетки, осей и подписей делений на плоских и объемных диаграммах, линий тренда и планок погрешностей на плоских диаграммах, а также стенки и основания на объемных диаграммах:
– установить указатель на изменяемый элемент диаграммы и дважды нажать кнопку мыши;
– при необходимости выбрать вкладку Узор и указать нужные параметры.
Для указания эффекта заливки необходимо выбрать соответствующую команду, а затем указать нужные параметры на вкладках Градиентная, Текстура и Узор.
Список – это упорядоченный набор данных, состоящий из строки заголовков (описания данных) и строк данных, которые могут быть числовыми и текстовыми.
Размер списка ограничен размерами одного рабочего листа, т.е. список может иметь не более 256 полей и не более 65 535 записей. Полями принято называть столбцы списка, а записями – строки.
Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям:
– список обязательно должен содержать строку заголовков;
– в каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст;
– рекомендуется помещать список на отдельный лист. Но если все же на лист нужно поместить еще и другую информацию, следите, чтобы список от нее отделялся хотя бы одной пустой строкой и одним пустым столбцом. В противном случае вы рискуете приобрести, например, сотрудника с фамилией «Итого».
Excel обладает мощными средствами для работы со списками. Это:
– создание итоговой сводной таблицы на основе данных списка.
Для того чтобы воспользоваться любым из этих инструментов, нужно установить курсор на одну из ячеек списка.
При вводе данные можно добавлять непосредственно в ячейки, а можно воспользоваться специальной формой ввода (рис. 2.21).
Если вы выбрали первый способ, то используйте команду контекстного меню Выбрать из списка. Excel избавит вас от необходимости много раз набирать один и тот же текст.
Если вы решили прибегнуть к помощи формы ввода, поместите курсор в любое место списка и выберите команду Данные – Форма. На экране появится диалоговое окно, в котором будет отображено каждое поле списка. При этом поля, содержащие формулы, хотя и отображаются в форме ввода, их значения изменить нельзя.
Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в форме.
Чтобы ввести новую запись, щелкните по кнопке Добавить. Форма очистится, и вы сможете ввести нужную информацию в соответствующие поля. После этого снова щелкните по кнопке Добавить, а если не хотите больше добавлять записи – по кнопке Закрыть.
Вновь введенные данные появятся в конце списка. Формулы, содержавшиеся в ячейках списка, автоматически будут распространены и на новую запись
Форму ввода можно использовать не только для ввода данных. Она позволяет просматривать существующие записи, редактировать их, удалять и выборочно отображать данные по определенному критерию.
В Excel существует два типа фильтров: Автофильтр и Расширенный фильтр.
Перед тем как использовать Автофильтр, выделите любую ячейку списка. Затем выберите команду Данные – Фильтр – Автофильтр. При включении Автофильтра возле имен полей списка появятся кнопки со стрелками.
При щелчке по любой из этих кнопок раскрывается меню (рис. 2.22), содержащее команды и список значений данного поля. С помощью этого меню можно отобрать все записи с заданным значением поля.
Рис. 2.22. Вид меню, содержащего команды и список значений поля
Обратите внимание на цвет стрелок на кнопках Автофильтра: если Автофильтр включен, кнопки окрашиваются в синий цвет.
Чтобы отключить ранее заданный фильтр, в раскрывающемся меню кнопок Автофильтра следует выбрать команду Все.
Если задан сложный критерий, то придется отменять составляющие условия отбора по очереди. Иногда бывает проще отказаться от Автофильтра, выбрав команду Данные – Фильтр – Автофильтр, а потом установить Автофильтр снова.
Кроме команды Все, в раскрывающемся меню кнопок Автофильтра есть еще одна команда Первые 10. которая используется для полей числового типа или дат. Эта команда покажет «горячую десятку» вашего списка.
Пусть необходимо узнать расходы за последние три дня. Щелкните по кнопке Автофильтра в столбце Дата, выберите в раскрываемся меню команду Первые 10. в диалоговом окне сделайте установки, как на рис. 2.23.
Рис. 2.23. Диалоговое окно установки расходов за последние 3 дня
Иногда стандартных условий Автофильтра оказывается недостаточно. Для создания собственного Автофильтра необходимо:
– для выбранного поля (например, Менеджер) из раскрывающегося меню кнопки Автофильтра выбрать команду (Условие…);
– в диалоговом окне Пользовательский автофильтр (рис. 2.24) задать условия отбора значений списка.
Если вы применяете Пользовательский автофильтр к текстовому полю, в качестве логической функции, связывающей условия, всегда выбирайте ИЛИ.
Для полей числового типа или дат используются следующие правила:
– И, когда интересует область между двумя числами или датами;
– ИЛИ, если интересует область вне интервала, заданного двумя числами или датами.
Часто для отбора нужной информации из списка бывает вполне достаточно Автофильтра или пользовательского фильтра. Однако для решения сложной задачи приходится прибегать к помощи расширенной фильтрации. Расширенный фильтр гораздо гибче Автофильтра, но чтобы воспользоваться им, придется выполнить подготовительные действия.
С помощью Расширенного фильтра (рис. 2.25) можно:
– помещать результат отбора данных на другое место и даже на новый лист рабочей книги;
Чтобы воспользоваться Расширенным фильтром, необходимо задать диапазон критериев.
Диапазон критериев – область рабочего листа, в которой формируется условие (условия) отбора. Диапазон критериев должен состоять, по крайней мере, из двух строк, первая из которых содержит все или некоторые названия полей списка.
Удобнее всего отвести для диапазона критериев область над списком. Названия полей, не используемых при фильтрации, можно не помещать в диапазон критериев. Но если вы предполагаете, что в дальнейшем в зависимости от обстоятельств вам может понадобиться и другая информация из списка, скопируйте строку, содержащую названия полей списка, целиком.
Условия отбора следует вносить в пустые ячейки диапазона критериев. Условия отбора, расположенные в ячейках одной строки, соединяются оператором И. Условия, расположенные на разных строках, соединяются оператором ИЛИ. Диапазон критериев может состоять из любого количества строк.
Область ячеек, содержащих критерии, должна отделяться от списка, по крайней мере, одной пустой строкой.
Для того чтобы отключить Расширенный фильтр, используют команду Данные – Фильтр – Отобразить все.
При использовании вычисляемого критерия отбор производится «по несуществующему полю». При создании формул вычисляемых критериев всегда ссылайтесь на первую строку списка, а не на строку заголовков. Если в формулу будут подставляться значения вне списка, используют абсолютные ссылки.
Если отфильтрованный список должен быть помещен на другой лист рабочей книги, сначала переходят на этот лист и только потом обращаются к команде Данные – Фильтр – Расширенный фильтр.
ABC-XYZ анализ. Как определить самые ходовые товары · BIRDYX
Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».
Как найти выбросы в Excel (и как с ними справиться)
При работе с данными в Excel у вас часто возникают проблемы с обработкой выбросов в вашем наборе данных. Выбросы довольно часто встречаются для всех видов данных, и важно идентифицировать и обрабатывать эти выбросы, чтобы убедиться, что ваш анализ правильный и более значимый.
В этом руководстве я покажу вам, как находить выбросы в Excel , а также некоторые методы, которые я использовал в своей работе для обработки этих выбросов.
Что такое выбросы и почему их важно найти?
Выброс — это точка данных, которая выходит за рамки других точек данных в наборе данных. Если у вас есть выброс в данных, это может исказить ваши данные, что может привести к неверным выводам.
Допустим, 30 человек едут на автобусе из пункта назначения А в пункт назначения Б. Все люди относятся к одной весовой группе и группе доходов. Для целей этого руководства давайте рассмотрим, что средний вес составляет 220 фунтов, а средний годовой доход — 70 000 долларов.
Сейчас где-то посередине нашего маршрута автобус останавливается, и в него садится Билл Гейтс.
Как вы думаете, как это повлияет на средний вес и средний доход людей в автобусе?
Хотя средний вес вряд ли сильно изменится, средний доход пассажиров автобуса резко вырастет.
Это связано с тем, что доход Билла Гейтса является исключением в нашей группе, и это дает нам неправильную интерпретацию данных. Средний доход каждого человека в автобусе составит несколько миллиардов долларов, что намного превышает реальную стоимость.
При работе с фактическими наборами данных в Excel вы можете иметь выбросы в любом направлении (например, положительный выброс или отрицательный выброс).
И чтобы убедиться, что ваш анализ верен, вам нужно каким-то образом идентифицировать эти выбросы, а затем решить, как лучше всего их лечить.
Теперь давайте посмотрим несколько способов найти выбросы в Excel.
Найдите выбросы путем сортировки данных
С небольшими наборами данных быстрый способ выявить выбросы — просто отсортировать данные и вручную просмотреть некоторые значения в верхней части отсортированных данных.
А поскольку выбросы могут быть в обоих направлениях, убедитесь, что вы сначала сортируете данные в порядке возрастания, а затем в порядке убывания, а затем просматриваете верхние значения.
Ниже у меня есть набор данных, в котором у меня есть продолжительность звонков (в секундах) для 15 звонков в службу поддержки.
Ниже приведены шаги по сортировке этих данных, чтобы мы могли идентифицировать выбросы в наборе данных:
Вышеупомянутые шаги сортируют столбец продолжительности звонка с наивысшими значениями вверху. Теперь вы можете вручную просмотреть данные и посмотреть, есть ли выбросы.
В нашем примере я вижу, что первые два значения намного выше остальных значений (а два нижних намного ниже).
Примечание. Этот метод работает с небольшими наборами данных, где вы можете вручную сканировать данные. Это не научный метод, но он хорошо работает
Поиск выбросов с помощью функций
Теперь давайте поговорим о более научном решении, которое поможет вам определить, есть ли какие-то выбросы.
В статистике квартиль составляет четверть набора данных. Например, если у вас есть 12 точек данных, то первый квартиль будет тремя нижними точками данных, второй квартиль будет следующими тремя точками данных и так далее.
Ниже приведен набор данных, по которому я хочу найти выбросы. Для этого мне нужно будет вычислить 1-й и 3-й квартили, а затем с его помощью вычислить верхний и нижний предел.
Ниже приведена формула для вычисления первого квартиля в ячейке E2:
и вот тот, который вычисляет третий квартиль в ячейке E3:
Теперь я могу использовать два вышеупомянутых вычисления, чтобы получить межквартильный размах (который составляет 50% наших данных в пределах 1-го и 3-го квартилей).
Теперь мы будем использовать межквартильный диапазон, чтобы найти нижний и верхний предел, который будет содержать большую часть наших данных.
Все, что выходит за эти нижние и верхние пределы, будет считаться выбросом.
Ниже приведена формула для расчета нижнего предела:
И формула для расчета верхнего предела:
Теперь, когда у нас есть верхний и нижний предел в нашем наборе данных, мы можем вернуться к исходным данным и быстро определить те значения, которые не лежат в этом диапазоне.
Быстрый способ сделать это — проверить каждое значение и вернуть ИСТИНА или ЛОЖЬ в новом столбце.
Я использовал приведенную ниже формулу ИЛИ, чтобы получить ИСТИНА для тех значений, которые являются выбросами.
Теперь вы можете фильтровать столбец Outlier и отображать только те записи, для которых значение TRUE.
В качестве альтернативы вы также можете использовать условное форматирование, чтобы выделить все ячейки, в которых значение TRUE.
Поиск выбросов с помощью функций НАИБОЛЬШИЙ / МАЛЕНЬКИЙ
Если вы работаете с большим количеством данных (значения в нескольких столбцах), вы можете извлечь 5 или 7 наибольших и наименьших значений и посмотреть, есть ли в них выбросы.
Если есть какие-либо выбросы, вы сможете их идентифицировать, не просматривая все данные в обоих направлениях.
Предположим, у нас есть приведенный ниже набор данных, и мы хотим знать, есть ли какие-либо выбросы.
Ниже приведена формула, которая даст вам наибольшее значение в наборе данных:
Точно так же второе по величине значение будет равно
Если вы не используете Microsoft 365, в котором есть динамические массивы, вы можете использовать приведенную ниже формулу, и она даст вам пять наибольших значений из набора данных с помощью одной единственной формулы:
Точно так же, если вам нужны 5 наименьших значений, используйте следующую формулу:
или следующее, если у вас нет динамических массивов:
Когда у вас есть эти значения, очень легко обнаружить любые выбросы в наборе данных. Хотя я решил извлечь 5 наибольших и наименьших значений, вы можете выбрать 7 или 10 в зависимости от размера вашего набора данных.
Я не уверен, является ли это приемлемым методом для поиска выбросов в Excel или нет, но это метод, который я использовал, когда мне приходилось работать с большим количеством финансовых данных на моей работе несколько лет назад. По сравнению со всеми другими методами, описанными в этом руководстве, я считаю этот наиболее эффективным.
Как правильно обращаться с выбросами
До сих пор мы видели методы, которые помогут нам найти выбросы в нашем наборе данных. Но что делать, если вы знаете, что есть выбросы.
Вот несколько методов, которые вы можете использовать для обработки выбросов, чтобы ваш анализ данных был правильным.
Удалить выбросы
Самый простой способ удалить выбросы из набора данных — просто удалить их. Таким образом, это не исказит ваш анализ.
Это более жизнеспособное решение, когда у вас большие наборы данных и удаление пары выбросов не повлияет на общий анализ. И, конечно же, перед удалением данных обязательно создайте копию и выясните, что вызывает эти выбросы.
Нормализовать выбросы (отрегулировать значение)
Нормализация выбросов — это то, что я делал, когда работал полный рабочий день. Для всех значений выбросов я бы просто изменил их на значение, немного превышающее максимальное значение в наборе данных.
Это гарантирует, что я не удаляю данные, но в то же время я не позволяю этому искажать мои данные.
Чтобы дать вам реальный пример, если вы анализируете маржу чистой прибыли компаний, где большинство компаний находится в пределах от -10% до 30%, а есть несколько значений, превышающих 100%, я просто изменит эти выбросы на 30% или 35%.
Итак, вот некоторые из методов, которые вы можете использовать в Excel для поиска выбросов .
После того, как вы определили выбросы, вы можете углубиться в данные и посмотреть, что их вызывает, и в то же время выбрать один из методов обработки этих выбросов (который может удалить их или нормализовать, изменив значение)
Почему в ячейке excel отображается формула а не значение
Если фактическое отношение Фишера больше критического отношения Фишера, то следует отклонить нулевую гипотезу с уровнем значимости α . Это означает, что фактор существенно влияет на данные: данные зависят от фактора с вероятностью P = 1 − α .
