Servisneva.ru

Сервис Нева
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как построить график в Excel по уравнению правильно

Exceltip

95-0-Нормальное распределение в Excel лого

Так как я часто имею дело с большим количеством данных, у меня время от времени возникает необходимость генерировать массивы значений для проверки моделей в Excel. К примеру, если я хочу увидеть распределение веса продукта с определенным стандартным отклонением, потребуются некоторые усилия, чтобы привести результат работы формулы СЛУЧМЕЖДУ() в нормальный вид. Дело в том, что формула СЛУЧМЕЖДУ() выдает числа с единым распределением, т.е. любое число с одинаковой долей вероятности может оказаться как у нижней, так и у верхней границы запрашиваемого диапазона. Такое положение дел не соответствует действительности, так как вероятность возникновения продукта уменьшается по мере отклонения от целевого значения. Т.е. если я произвожу продукт весом 100 грамм, вероятность, что я произведу 97-ми или 103-граммовый продукт меньше, чем 100 грамм. Вес большей части произведенной продукции будет сосредоточен рядом с целевым значением. Такое распределение называется нормальным. Если построить график, где по оси Y отложить вес продукта, а по оси X – количество произведенного продукта, график будет иметь колоколообразный вид, где наивысшая точка будет соответствовать целевому значению.

Таким образом, чтобы привести массив, выданный формулой СЛУЧМЕЖДУ(), в нормальный вид, мне приходилось ручками исправлять пограничные значения на близкие к целевым. Такое положение дел меня, естественно, не устраивало, поэтому, покопавшись в интернете, открыл интересный способ создания массива данных с нормальным распределением. В сегодняшней статье описан способ генерации массива и построения графика с нормальным распределением.

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

Построение графиков на основе одного ряда таблицы

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

Это связано с тем, что информация из первого столбца будет отображена на горизонтальной оси (ОХ), а значения остальных столбцов – на вертикальной оси (ОУ). Так же при заполнении таблицы не нужно пренебрегать заголовками и подзаголовками. Они необходимы для пояснения введенных значений и при построении графика автоматически будут на него перенесены.

Допустим, мы имеем готовую заполненную таблицу (в столбце А введены названия месяцев, в столбцах В и С – количество книг).

Для построения графика выполняем следующую последовательность шагов.

1 шаг. Выделяем данные

Для начала нужно обязательно выделить данные в таблице.

  • для создания графика на основе одного набора данных нужно выделить в таблице определенный столбец (строку);
  • чтобы правильно отобразились подписи данных нужно выделять и заголовки столбцов.

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

2 шаг. Выбор инструмента построения

Самый простой способ создания графика – инструменты, расположенные на Ленте команд. Чтобы их открыть, переходим на вкладку Вставка и в группе Диаграммы выбираем кнопку график.

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

3 шаг. Выбор вида графика

В Эксель можно построить 7 видов графиков:

  • график;
  • график с маркерами (каждая точка помечается маркером);
  • график с накоплением;
  • график с маркерами и накоплением;
  • нормированный график с накоплением;
  • нормированный график с маркерами и накоплением;
  • объемный график.

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

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

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

Эту же операцию можно выполнить еще одним способом — при помощи диалогового окна Вставка диаграммы. На вкладке Вставка в группе Диаграммы в нижнем левом углу располагается командная кнопка создать диаграмму.

Нажав на нее, откроем диалоговое окно вставка диаграммы.

В нем в списке шаблонов нужно выбрать нужный тип.

Построение графиков на основе нескольких рядов табличных данных

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

Выделим в данной таблице диапазон А2:С15 и построим обычный график (без маркеров).

Изменение параметров графика

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

После того, как график появился на листе, в Ленте команд открывается временный раздел Работа с диаграммами (подсвечен зеленым цветом), который содержит три вкладки – Конструктор, Макет, Формат. Элементы этих вкладок дают возможность настроить параметры графика «под себя».

Читать еще:  Как сделать эффект дождя в Adobe Photoshop

Форматирование графиков

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

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

Все операции применимы только к выделенным объектам!

Выполним форматирование построенного графика:

  • изменим цвет Области построения и Область построения, зададим цвет контура, толщину, тип линий;
  • аналогично поэкспериментируем с Легендой. Для форматирования текстовых элементов Легенды и подписей координатных применятся стандартные операции по форматированию теста (они доступны на вкладке Главная);
  • изменим цвет Рядов (как это сделать, видно на скриншоте).

Редактирование графиков

К операциям редактирования относят:

  • изменение способа формирования ряда (взаимная замена данных столбца и строки). При построении графиков в нашем примере его ряды данных были сформированы по значениям, расположенным в столбцах таблицы. Чтобы изменить способ формирования рядов данных, нужно выполнить Конструктор → Данные → Строка-Столбец;
  • изменение диапазона ячеек. Иногда к существующему диапазону ячеек таблицы (для которых уже построен график) нужно добавить дополнительные строки или столбцы или удалить существующие. Если этот диапазон является таблицей, то редактирование графика произойдет автоматически.

Например, удалим в существующей таблице строки 7 и 8 (апрель и май). Как видим, данные этих строк и их подписи на графике тоже исчезли.

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

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

На вкладке Макет в группах Подписи и Оси собраны инструменты для работы с элементами графика.

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

Прием 3: отобразить среднее скользящее

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

5 приемов, как улучшить диаграммы в Excel

Как сделать:

  1. ПКМ по линии графика;
  2. Добавить линию тренда;
  3. Выбрать скользящее среднее;
  4. Установить период.

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

5 приемов, как улучшить диаграммы в Excel

5 приемов, как улучшить диаграммы в Excel

Как добавить линейную регрессию в графики Excel

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

Добавление линии тренда линейной регрессии на график

Сначала откройте пустую электронную таблицу Excel, выберите ячейку D3 и введите «Месяц» в качестве заголовка столбца, который будет переменной x. Затем щелкните ячейку E3 и введите «Значение Y» в качестве заголовка столбца переменной y. В основном это таблица с записанным рядом значений данных за месяцы январь-май. Поэтому введите месяцы в ячейки от D4 до D8 и значения данных для них в ячейки от E4 до E8, как показано на снимке непосредственно ниже.

линейная регрессия

Теперь вы можете настроить график рассеяния для этой таблицы. Выделите все ячейки в таблице с помощью курсора. Перейдите на вкладку «Вставка» и выберите «Разброс»> «Разброс только с маркерами», чтобы добавить график в электронную таблицу, как показано ниже. Кроме того, вы можете нажать горячую клавишу Alt + F1, чтобы вставить гистограмму. Затем вы должны щелкнуть правой кнопкой мыши по диаграмме и выбрать Изменить тип диаграммы> X Y (разброс)> Разброс только с маркерами.

линейная регрессия2

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

Читать еще:  Как в Photoshop сделать обводку текста?

линейная регрессия3

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

линейная регрессия4

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

Форматирование линии тренда линейной регрессии

Чтобы отформатировать линию тренда, щелкните ее правой кнопкой мыши и выберите «Формат линии тренда». Это снова откроет окно Format Trendline, из которого вы можете нажать Line Color. Выберите Сплошную линию и щелкните поле Цвет, чтобы открыть палитру, из которой вы можете выбрать альтернативный цвет для линии тренда.

Чтобы настроить стиль линии, перейдите на вкладку «Стиль линии». Затем вы можете настроить ширину стрелки и настроить параметры стрелки. Нажмите кнопки настройки стрелки, чтобы добавить стрелки к линии.

линейная регрессия5

Добавьте эффект свечения на линию тренда, нажав Glow и Soft Edges. Это откроет вкладку ниже, из которой вы можете добавить свечение, нажав кнопку Presets. Затем выберите вариант свечения, чтобы выбрать эффект. Нажмите «Цвет», чтобы выбрать альтернативные цвета для эффекта, и вы можете перетащить панели «Размер» и «Прозрачность» для дальнейшей настройки свечения линии тренда.

линейная регрессия6

Прогнозирование значений с помощью линейной регрессии

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

линейная регрессия7

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

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

линейная регрессия8

Функции линейной регрессии

Excel также включает функции линейной регрессии, с помощью которых вы можете найти значения наклона, точки пересечения и r квадрата для массивов данных y и x. Выберите ячейку электронной таблицы, чтобы добавить одну из этих функций, а затем нажмите кнопку «Вставить функцию». Функции линейной регрессии являются статистическими, поэтому выберите «Статистические» в раскрывающемся меню категории. Затем вы можете выбрать RSQ, SLOPE или INTERCEPT, чтобы открыть их функциональные окна, как показано ниже.

линейная регрессия9
Окна RSQ, SLOPE и INTERCEPT практически одинаковы. Они включают в себя поля Known_y и Known_x, которые можно выбрать для добавления значений переменных y и x из вашей таблицы. Обратите внимание, что ячейки должны содержать только цифры, поэтому замените месяцы в таблице соответствующими цифрами, такими как 1 для января, 2 для февраля и т. Д. Затем нажмите кнопку ОК, чтобы закрыть окно и добавить функцию в электронную таблицу.

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

Создание диаграммы

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

выделение области

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

И открыть меню «Вставка диаграмм» во вкладке «Вставка»

вход в создание диаграмм

Меню диаграмм находится во вкладке Вставка

Затем нужно выбрать наиболее подходящий для этих данных тип диаграммы

Выбор типа диаграммы

На наш выбор огромное количество разных диаграмм

После этого, нажимаем на «ОК» и диаграмма тут же построена.

диаграмма готова

Один клик и диаграмма готова

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

диаграмма с несколькими рядами

Можно построить и многорядные диаграммы

Чтобы, например, показать соотношение разных демографических факторов в течение этого периода, то есть которые из них больше повлияли на прирост численности населения, выделяем данные только эти данные

выделение разных строк

Можно выделить только нужные строки в таблице

Читать еще:  Ummy Video Downloader - обзор программы для скачивания видео

и строим другую, более наглядную для этого случая диаграмму:

два ряда данных

И построить более наглядную диаграмму

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

изменить тип диаграммы

Нажатием на «Выбрать данные» можно изменить ряды данных, для которых строится график.

Построение диаграммы Ганта

Диаграмма Ганта чаще всего используется для визуализации сроков выполнения каких-либо задач. Простого и удобного инструмента для ее создания в Microsoft Excel не предусмотрено, однако ее можно построить вручную по следующему алгоритму:

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

Таблица с задачами в ExcelФотография Таблица с задачами в Excel

2. В пункте основного меню «Вставка» кликните по кнопке «Линейчатая» в разделе «Диаграммы» и выберите вариант «Линейчатая с накоплением» в выпадающем списке. У вас появится пустая диаграмма.

Пустая диаграмма в ExcelФотография Пустая диаграмма в Excel

3. Нажмите правой кнопкой мыши по пока что пустой диаграмме и выберите пункт меню «Выбрать данные…». В открывшемся окне нажмите на кнопку «Добавить» в разделе «Элементы легенды (ряды)».

Выбор источника данных для диаграммы в ExcelФотография Выбор источника данных для диаграммы в Excel

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

Изменение ряда в ExcelФотография Изменение ряда в Excel

5. Аналогичным образом (повторив шаги 3 и 4) внесите в диаграмму сведения из столбца с количеством дней, необходимых для выполнения каждой задачи.

Снова выбираем источники данныхФотография Снова выбираем источники данных

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

Выделяем все названия задач из первой осиФотография Выделяем все названия задач из первой оси

7. Удалите из диаграммы легенду (в нашем случае в нее входят разделы «Начало выполнения» и «Продолжительность»), занимающую лишнее место.

Диаграмма без легендыФотография Диаграмма без легенды

8. Кликните на любой из синих фрагментов диаграммы, выберите пункт «Формат ряда данных…» и уберите заливку и границы в соответствующих разделов («Нет заливки» в разделе «Заливка» и «Нет линий» в разделе «Цвет границы»).

Убираем заливку синих фрагментов таблицыФотография Убираем заливку синих фрагментов таблицы

9. Кликните правой кнопкой мыши на поле, в котором отображаются названия задач, и выберите раздел «Формат оси…». В открывшемся окне кликните на «Обратный порядок категорий», чтобы задачи отображались в том порядке, в котором были записаны вами в таблицу.

Выбираем обратный порядок категорийФотография Выбираем обратный порядок категорий

10.1. Диаграмма Ганта практически готова: осталось лишь убрать пустой промежуток в ее начале, то есть подкорректировать временную ось. Для этого кликните правой кнопкой по дате начала первой задаче в таблице (не в диаграмме) и выберите пункт «Формат ячеек». Перейдите в раздел «Общий» и запомните число, которое там увидите. Нажмите «Отмена».

Выбираем формат ячеекФотография Выбираем формат ячеек

10.2. Кликните правой кнопкой мыши по тому полю диаграммы, в котором отображаются даты, и выберите пункт «Формат оси…». В разделе «Минимальное значение» выберите «Фиксированное» и введите то число, которое запомнили на предыдущем шаге. В этом же окне вы можете изменить цену делений оси. Нажмите «Закрыть» и любуйтесь полученным результатом.

Параметры осиФотография Параметры оси Готовая диаграмма ГантаФотография Готовая диаграмма Ганта

Прогнозируем

Чтобы рассчитать продажи за 10-ый месяц, подставляем в функцию тренда 10 вместо x. То есть,

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

Прогноз данных

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

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

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