Как построить график в эксель с большим разбросом цифр

Обновлено: 07.07.2024

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

Что представляет собой корреляционный анализ

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

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

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

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

Количество человек, которые стали утопленниками в бассейнах, четко коррелирует с количеством фильмов, в которых Николас Кейдж был актером.
Количество съеденной моцареллы и количество человек, которые получили докторскую степень, также коррелирует на протяжении 2000-2009 годов. Наверно, действительно, моцарелла как-то влияет на мозг и стимулирует желание совершать научные открытия.
Почти во всех случаях средний возраст женщин, которые получили статус «Мисс Америка» коррелирует с количеством людей, которые погибли от нахождения в горячем паре.
Число людей, которое погибло в результате дорожно-транспортного происшествия, четко коррелирует с количеством сметаны, которое съедают люди.
Мало кто знает, что чем больше курятины человек ест, тем больше сырой нефти импортируется в мире. Правда, это тоже пример ложной корреляции. Кстати, импорт сырой нефти родом из Норвегии тесно связано с количеством людей, которые погибли в результате столкновения автомобиля с поездом. Причем в этом случае корреляция почти 100 процентов.
А еще маргарин негативно влияет на статистику разводов. Чем больше людей, которые проживали в штате Мэн, потребляли маргарина, тем выше была частота разводов. Правда, здесь еще может быть рациональное зерно. Ведь частота потребления маргарина имеет обратную корреляцию с экономическим положением в семье. В свою очередь, плохое экономическое положение в семье имеет непосредственную связь с количеством разводов. И это уже доказано научно. Так что кто знает, может, эта корреляция и не является такой ложной. Правда, никто этого не перепроверял.
Количество денег, которое правительство США тратит на развитие науки, космоса и технологий, имеет тесную связь с количеством самоубийств, проведенных в форме повешения или удушения.

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

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

Вычисление коэффициента корреляции осуществляется двумя способами. Первый – это использование Мастера функций, который позволяет ввести формулу КОРРЕЛ. Второй инструмент – это пакет анализа, требующий отдельной активации.

Как рассчитать коэффициент корреляции

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

Способ 1. Определение корреляции с помощью Мастера Функций

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

С помощью левой кнопки мыши выделяем ту ячейку, в которой будет находиться получившийся коэффициент корреляции. После этого находим слева от строки формул кнопку fx, которая откроет инструмент ввода функций.
Далее выбираем категорию «Полный алфавитный перечень», в котором ищем функцию КОРРЕЛ. Как видно из названия категории, все названия функций располагаются в алфавитном порядке.
Далее открывается окно ввода параметров функции. У нас два основных аргумента, каждый из которых являет собой массив данных, которые сравниваются между собой. В поле «Массив 1» указываем координаты первого диапазона, а в поле «Массив 2» – адрес второго диапазона. Для ввода данных массива, используемого для расчета, достаточно выделить нажать левой кнопкой мыши по соответствующему полю и выделить правильный диапазон.
После того, как мы введем данные в аргументы, нажимаем кнопку «ОК», чем подтверждаем совершенные действия.

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

Способ 2. Вычисление корреляции с помощью пакета анализа

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

Все, теперь наша надстройка включена. Теперь мы во вкладке «Данные» можем увидеть кнопку «Анализ данных». Если она появилась, то мы все сделали правильно. Нажимаем на нее.

Появляется перечень с выбором разных способов анализа информации. Нам следует выбрать пункт «Корреляция» и нажать на «ОК».

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

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

В настройках вывода уже стоит пункт «Новый рабочий лист». То есть, информация о корреляции будет располагаться на отдельном листе. Пользователь может настроить место самостоятельно с помощью соответствующего переключателя – на текущий лист или в отдельный файл. Проверяем, все ли настройки были введены правильно. Если да, подтверждаем свои действия нажатием на клавишу «ОК».

Поскольку мы оставили поле с данными о том, куда будут выводиться результаты, таким, каким оно было, мы переходим на новый лист. На нем можно найти коэффициент корреляции. Конечно, он такой же самый, как был в предыдущем методе – 0,97. Причина этого в том, что вычисления производятся одинаковые, исходные данные мы также не меняли. Просто разными методами, но не более.

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

Как построить поле корреляции в Excel

Итак, давайте теперь разберемся, как построить поле корреляции. Для начала нужно разобраться, что это вообще такое. Под корреляционным полем подразумевается фактически график корреляции. Главное требование к такой диаграмме – каждая точка должна соответствовать единице совокупности. Поле корреляции поможет установить более глубокие связи и проанализировать данные более качественно. Для начала нам нужно найти коэффициент корреляции между двумя диапазонами, используя функцию КОРРЕЛ.

После того, как мы это сделали, мы теперь можем сделать поле корреляции. Для этого выполняем следующие действия:

Этот график можно построить не только на основе корреляции, определенной через функцию КОРРЕЛ.

Диаграмма рассеивания. Поле корреляции

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

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

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

Далее выставляем настройки. Нас интересует тип «Линейная», а в окне параметров нужно поставить флажок «Показывать уравнение на диаграмме».

После подтверждения действий у нас появится что-то типа такого графика.

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

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

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

Что показывает диаграмма рассеяния

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

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

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

Построение диаграммы рассеяния в Excel

Диаграмма разброса представляет наблюдаемое явление в пространстве двух измерений. Если одну величину рассматривать как «причину», влияющую на другую величину, то ей будет соответствовать ось Х (горизонтальная ось). Реагирующей на это влияние величине соответствует ось Y (вертикальная ось). Когда четко классифицировать переменные невозможно, распределение производится пользователем.

Построим диаграмму рассеяния для небольшой двумерной совокупности данных:

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

Для построения диаграммы рассеяния в Excel выделим столбцы «Контакты», «Объем продаж» (включая заголовки). Перейдем на вкладку «Вставка» в группу «Диаграммы». Использование данного инструмента анализа возможно с помощью точечных диаграмм:

По умолчанию программа построила диаграмму разброса такого вида:

Изменим параметры горизонтальной и вертикальной оси, чтобы четыре пары показателей расположились более равномерно в области построения. Щелкнем сначала правой кнопкой мыши по вертикальной оси. Выберем «Формат оси»:

На вкладке «Параметры оси» установим минимальное значение 100 000, а максимальное – 200 000. Показатели объема продаж находятся в этих пределах:

Минимальное значение для горизонтальной оси Х – 100, т.к. ниже этого показателя данных в таблице нет.

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

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

Способ 1: Выбор таблицы для диаграммы

Откройте необходимую таблицу и выделите ее, зажав левую кнопку мыши и проведя до завершения.

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

Там нас интересует блок «Диаграммы», в котором можно выбрать одну из диаграмм или перейти в окно с рекомендуемыми.

Откройте вкладку «Все диаграммы» и отыщите среди типов ту, которая устраивает вас.

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

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

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

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

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

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

Способ 2: Ручной ввод данных

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

На листе выберите любую свободную ячейку, перейдите на вкладку «Вставка» и откройте окно со всеми диаграммами.

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

Из появившегося контекстного меню выберите пункт «Выбрать данные».

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

В блоках «Элементы легенды (ряды)» и «Подписи горизонтальной оси (категории)» вы самостоятельно решаете, какие столбы с данными будут отображаться и как они подписаны. При помощи находящихся там кнопок можно изменять содержимое, добавляя или удаляя ряды и категории.

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

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

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

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

Кликните по кнопке ниже и мы пришлем Вам Excel файл с примером графика, построенного по данным таблицы на E-mail:

Видеоурок

Как построить простой график в Excel

Представим, что у нас есть таблица с ежемесячными данными среднего курса Доллара в течении года:

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

Как построить график в Excel на основе данных таблицы с двумя осями

Представим, что у нас есть данные не только курса Доллара, но и Евро, которые мы хотим уместить на одном графике:

Для добавления данных курса Евро на наш график необходимо сделать следующее:

Если вы хотите отразить данные графика в разных форматах по двум осям X и Y, то для этого нужно:

Ниже мы рассмотрим как улучшить информативность полученных графиков.

Как добавить название в график Эксель

На примерах выше мы строили графики курсов Доллара и Евро, без заголовка сложно понять про что он и к чему относится. Чтобы решить эту проблему нам нужно:

Как подписать оси в графике Excel

Для лучше информативности нашего графика в Excel есть возможность подписать оси. Для этого:

Как добавить подписи данных на графике Excel

Ваш график может стать еще более информативным с помощью подписи отображаемых данных.

На примере курсов валют мы хотим отобразить на графике стоимость курса Доллара и Евро помесячно. Для этого нам потребуется:

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

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

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

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