Как сделать отрицательный график в excel

Добавил пользователь Алексей Ф.
Обновлено: 04.10.2024

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

Пошаговый процесс создания диаграммы

График включает в себя множество элементов, при этом одни из них по умолчанию отображаются на рисунке, другие можно добавить/удалить и настроить в ручном режиме. При построении графика в Excel пользователь выбирает нужный вариант из нескольких его видов. Например, простой (и объемный) показывает изменение кого-то значения во времени (прибыль по месяцам и т.д.), с накоплением – изменение составляющей целого значения с течением времени, а нормированный с накоплением – изменение доли каждого значения с течением времени. Каждый из них также может быть с маркером или без него, но рисуются они одинаково.

Создание графика происходит после подготовки исходной таблицы.

С простым содержимым

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

С несколькими кривыми

С последующим добавлением дополнительной оси

С функцией

Составить таблицу с исходными данными. Первая ячейка столбца X – это 1, вторая – содержимое первой ячейки + 0,3. Формула имеет следующий вид: =A2+0,3. При помощи маркера заполнения протянуть вычисление вниз.

С наложением нескольких функций

С зависимостью между данными

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

Есть условие: A=f(E); B=f(E); C=f(E); D=f(E). Необходимо построить диаграмму стандартным способом.

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

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

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

Предварительно выбираем диапазон ячеек, на основе которых мы будем строить наш график. В нашем примере, в графике, мы отразим помесячно динамику продаж офисных стульев. На графике будет видно сколько стульев продано в каждом из 12 месяцев года. Так же график отразит зависимость межу конкретных месяцем и количеством проданных офисных стульев. Соответственно в таблице нам нужно выделить диапазон ячеек с месяцами (В3:В14) и диапазон ячеек с количеством продаж офисных стульев (С3:С14).

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

Нажимаем на нее и выбираем нужный нам тип графика — График.

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

Настройка графика в Excel.

Теперь выполним некоторые настройки нашего графика.

Название графика.

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

Название осей.

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

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

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

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

Можно переместить название оси наведя курсор мыши на рамку поля Название оси.

Можно изменить направление текста в названиях осей и названии самой диаграммы. Для этого нажимаем правой кнопкой мыши в любом месте диаграммы. Откроется контекстное меню в котором выбираем Формат области диаграммы.

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

Метки данных.

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

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

Стиль и цвет диаграммы.

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

Способ 1. Изменение системы дат Excel по умолчанию

Первый быстрый и простой способ научить Excel отображать отрицательные значения времени — выбрать команду пункт меню Файл → Параметры Excel → Вычисления (File → Parametres Excel → Calculation) и установить флажок Система дат 1904 (1904 date system). Система дат 1904 называется также датой и временем Macintosh.

Помните, что при этом в Excel изменятся начальные даты, от которых отсчитывается значение всех ячеек. Вместо 1 января 1900 года это будет 2 января 1904 года. Изменение этого параметра влияет только на ту рабочую книгу, в которую вы вносите изменения.

Попробуйте. Выберите команду Файл → Параметры Excel → Вычисления (File → Parametres Excel → Calculation) и установите флажок Система дат 1904 (1904 date system). В ячейку А2 введите 5:00:00 AM, в ячейку A3 — 6:00:00 AM, в ячейку А4 — =А2-А3. Вы получите результат -1:00, но только если выберете систему дат 1904, как описано ранее.

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

Способ 2. Функция ТЕКСТ (TEXT)

Второй способ требует использования функции ТЕКСТ (TEXT). Для начала в ячейке А2 введите 5:00:00 AM, в ячейке A3 — 6:00:00 AM. В ячейке А4 введите следующую формулу: =ТЕХТ(МАХ($А$2:$А$3)-MIN($А$2:$А$3);"-Н::ММ") , в русской версии Excel =ТЕКСТ(МАКС($А$2:$А$3)-МИН($А$2:$А$3);"-Ч::ММ") .

Используя эту вложенную формулу, вы вычтете A3 из А2, чтобы получить положительное значение времени. Затем отформатируете ячейку при помощи функции ТЕКСТ (TEXT), чтобы она могла отображать отрицательные значения времени. Комбинация функций МАКС (МАХ) и МИН (MIN) гарантирует, что более ранее время всегда будет вычитаться из более позднего.

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

Способ 3. Пользовательский формат

Еще один способ отображения отрицательных значений времени без перехода к системе дат 1904 и с гарантированным возвращением числового значения — это использование пользовательского формата -ч:мм (-h:mm) в диалоговом окне Главная → Формат → Формат ячеек (Home → Format → Format Cells).

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

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

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

Как проводится корреляционный анализ в Excel
Вариант 1: Вызов через Мастер функций
Вариант 2: Применение пакета анализа

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

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

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

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

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

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

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

Откроется окно мастера функций. В поле “Категория” нужно поставить значение “Полный алфавитный перечень”, чтобы отобразились все доступные для применения функции. Там отыщите пункт “КОРРЕЛ” нажмите по нему и затем на кнопку “Ок”.

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

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

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

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

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

Корреляцию в Excel можно найти по формуле:

Результат показан ниже

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

затем переходим на область графика

и выбираем данные из диапазона B3:C11, затем Ок. В итоги получаем график поля корреляции по точкам

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

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