Check-moscow.ru

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

Как добавить линию ряда в диаграмму в Excel?

Как работать с линиями тренда в диаграммах Microsoft Excel

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

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

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

График Excel с линейной линией тренда.

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

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

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

1 Линия горизонтальной сетки

1 Вертикальная линия сети

1 Линия координатной сетки оси Z

Следующие инструкции относятся к Office 2013 и более поздним версиям. Какие действия доступны для Office 2010?

Отображение линий сетки

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

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

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

Скрытие линий сетки

Щелкните на графике, где нужно скрыть линии сетки.

На вкладке Конструктор в группе Макеты диаграмм нажмите Добавить элемент диаграммы, наведите курсор на Линии сетки и выберите нужный вариант. Чтобы просмотреть каждый вариант в сетке, наведите на него курсор мыши.

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

Читайте так же:
Как динамически вставлять изображение или изображение в ячейку на основе значения ячейки в Excel?

Для отображения линий сетки выполните следующие действия.

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

В группе Оси появится линия сетки. Щелкните ее на вкладке Макет.

Сделайте одно или несколько из следующих действий.

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

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

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

Вы можете добавить вертикальные линии сетки, выбрав Vertical Grid Lines вдоль главной оси, а затем выбрав нужную опцию.

Вспомогательные вертикальные линии можно добавить, выбрав Линии сетки на вспомогательной вертикальной оси.

Примечание: Эта опция доступна только для графиков со вспомогательной вертикальной осью. Для получения дополнительной информации см. статью Добавление и удаление вспомогательной оси на графике.

Выберите линии сетки по оси Z, затем выберите нужный вариант для добавления линий сетки по оси глубины.

Примечание: Эта опция доступна только в том случае, если выбранный график является графиком объема, например гистограммой объема.

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

Щелкните на графике, где нужно скрыть линии сетки.

На вкладке Макет группы Оси нажмите кнопку Линии сетки.

Сделайте одно или несколько следующих действий

Выберите Горизонтальные линии сетки на главной оси, вертикальные линии сетки на главной оси или линии сетки на оси Z (для объемной диаграммы) и выберите Нет.

Выберите Сетки на дополнительной горизонтальной оси или Сетки на дополнительной вертикальной оси и Нет значения.

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

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

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

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

Для этого выберите пункт Макет страницы в меню Вид в Microsoft Word для Mac.

После нажатия на диаграмму появится вкладка Конструктор.

Щелкните Добавить элемент диаграммы > Сетка.

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

Читайте так же:
Как выделить даты старше года в Excel?

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

Иногда требуется обозначить на графике некоторые контрольные значения или коридор, например, среднее значение и сигму-окрестность. Однажды, выступая на конференции с докладом «Как повысить качество управленческих решений» мне потребовалось проиллюстрировать разброс числа рекламаций в течение года (рис. 1). Наряду со значениями (красные точки), я вывел на графике линию среднего значения (зеленая линия) и верхнюю границу диапазона, соответствующую отклонению 3s от среднего значения (синяя пунктирная линия):

Рисунок 1 — График со средним значением и пределом

Скачайте записку в формате Word, примеры в формате Excel

Построение такого графика не должно вызвать затруднений. Рассмотрим лист «Коридор» Excel-файла:

Во второй колонке показана средняя дебиторская задолженность по месяцам, в третьей — средняя по годам, рассчитанная по формуле =C2+STANDOTCLON($B$2:$B$13), в четвертой — нижняя граница, соответствующая значениям на s меньше среднего =C2-STANDOTCLON($B$2:$B$13), в пятой — верхняя граница, соответствующая значениям на s больше среднего =C2+STANDOTCLON($B$2:$B$13).

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

После ##0,0 появятся два пробела.

Нарисовать стандартный календарь:

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

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

Выделите одну из контрольных линий, например, линию среднего, и правой кнопкой мыши вызовите контекстное меню; выберите пункт «Формат ряда данных»:

Поставьте переключатель в положение «По вспомогательной оси»:

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

Выберите вкладку Макет и перейдите к Оси — Вспомогательная горизонтальная ось — Слева направо.

Выделите вспомогательную горизонтальную ось и переключите «Положение оси» в позицию «по делениям»:

Диаграмма должна выглядеть как показано ниже:

Выделите и удалите вспомогательную вертикальную ось. При этом контрольные линии расположатся в соответствии с основной вертикальной осью. А вот горизонтальную вспомогательную ось удалить нельзя, так как настройки контрольных линий (вспомогательная горизонтальная ось в положении «по делениям») пропадут. Поэтому нужно выделить горизонтальную вспомогательную ось и отключить основные деления и подписи оси:

Вот, что мы получили:

Небольшое домашнее задание. Сделайте горизонтальную вспомогательную ось невидимой.

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

Читайте так же:
Как выполнить экспоненциальный расчет для диапазона ячеек в Excel?

Вам нужна схема, подобная этой:

Гистограмма состоит из 6 столбцов. Динамические линии, т.е. она должна принимать значения от 1 до 6 (порядковый номер максимальной категории, крайний правый столбец гистограммы) в зависимости от значения, установленного на листе. Вертикальная линия будет падать на первую категорию, например, если значение =1.

Помимо графика, в файле примера содержатся исходные таблицы.

Построим обычную столбчатую диаграмму (см. статью Основы построения диаграмм в MS Excel). Затем добавим на диаграмму еще один ряд. Для этого выделите диаграмму, выберите вкладку Конструктор и нажмите кнопку меню Выбрать данные. В качестве значений введите ссылку на любую ячейку со значениями будущей вертикальной линии, например, первое значение X (ячейка A19, желтая). Создание многорядных диаграмм подробно рассматривается в разделе «Создание многорядных диаграмм». Построение многорядных диаграмм в MS EXCEL.

На гистограмме появляется второй столбик в первой категории. Щелкните ее правой кнопкой мыши и в появившемся меню измените тип диаграммы для ряда на точечную диаграмму. Затем снова введите данные графика (их также можно ввести с помощью того же контекстного меню).

Укажите правильные ссылки для значений X и Y нового ряда.

Чтобы вывести подпись у верхней точки вертикальной линии – применим трюк. В качестве имени ряда укажем ячейку со значением Х. Далее, через вкладку Макет установим подпись для точек вертикальной линии – Имя ряда.

Убрав маркеры с вертикальной линии, добавьте линию, соединяющую две точки. Диаграмма готова.

Гистограмму можно легко преобразовать в график типа диаграммы. Щелкните по ней и в появившемся меню выберите Chart.

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

Выберите данные в Excel

Начните с выделения данных в двух столбцах. На ленте перейдите на вкладку Вставка и найдите раздел Графики. Нажмите кнопку с надписью Scatter (Разброс), а затем выберите кнопку из меню с надписью Scatter with markers only (Разброс только с маркерами),

Разброс только с маркерами

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

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

Добавить линию тренда в Excel

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

Читайте так же:
Как добавить линии выноски в столбец с накоплением в Excel?

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

Добавить линию тренда в Excel

Теперь вы должны изучить окно Формат линии тренда. Это окно содержит множество опций для добавления линии тренда на диаграмму рассеяния Excel.

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

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

Линейная регрессия Trendline на диаграмме Excel

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

Форматирование Excel Trendline

Начните с выбора Format Trendline в меню после щелчка правой кнопкой мыши на линии. Excel снова откроет панель Формат линии тренда.

Формат Trendline в Excel

Одна из самых популярных опций, используемых при добавлении линии тренда в Excel, — отображение уравнения линии и значения R-квадрат непосредственно на графике. Эти опции можно найти в нижней части окна. На данный момент выберите оба варианта.

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

В этом окне измените значение ширины с 0,75 до примерно 3 и измените тип ленты на вариант Square Stitch (третий в выпадающем меню). Чтобы показать, что опция существует, измените тип End Type на опцию со стрелкой.

Нажмите кнопку X на панели Формат линии тренда, когда закончите, и вы увидите, как изменились ваши точки. Теперь на графике есть уравнение линии и значение R-квадрат, а также более заметная линия тренда.

Финальная линия тренда в Excel

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

Вы можете изменить цвет и толщину линии и даже добавить к ней 3D-элементы, например эффект затенения (нажмите на значок «Последствия»).

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

Читайте так же:
Как добавить все границы к ячейкам с помощью ярлыков в Excel?

Пользовательская комбинированная диаграмма

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

Шаг 1 — Выберите данные на вашем листе.

Шаг 2: На вкладке Вставка в группе Диаграммы нажмите на значок комбинированной диаграммы на ленте.

Пользовательская комбинированная диаграмма

Шаг 3 — Нажмите Создать собственную комбинированную диаграмму. Появится окно «Вставка диаграммы». На левой панели выделен тип комбинированной диаграммы. Для пользовательской комбинации появляется диалоговое окно.

Создать собственную комбинацию

Шаг 4 — Выберите тип диаграммы для каждой серии.

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

Шаг 6 — Когда вы будете удовлетворены пользовательской комбинацией, нажмите OK.

Как построить диаграмму Парето в Excel

Вильфредо Парето открыл принцип 80/20. Это открытие распространилось и стало стандартом, применимым во многих областях человеческой деятельности.

Согласно правилу 80/20, 20% усилий дают 80% результатов (только 20% причин объясняют 80% проблем и т.д.). Диаграмма Парето отражает эту взаимосвязь в виде гистограммы.

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

  1. Создадим в Excel таблицу с данными. 1 столбец – причины. 2 столбец – количество фактов, при котором были обнаружены данные причины (числовые значения). Обязательно – итог. Причины и факты.
  2. Теперь посчитаем в процентах воздействие каждой причины на общую ситуацию. Создаем третий столбец. Вводим формулу: количество фактов по данной причине / общее количество фактов (=В3/В9). Нажимаем ВВОД. Устанавливаем процентный формат для данной ячейки – Excel автоматически преобразует числовое значение в проценты. Процент воздействия.
  3. Отсортируем проценты в порядке убывание. Выделим диапазон: C3:C8 (кроме итога) – правая кнопка мыши – сортировка – «от максимального к минимальному». Процент воздействия.
  4. Находим суммарное влияние каждой причины и всех предыдущих. Для причины 2 – причина 1 + причина 2. Сумма влияния.
  5. Столбец «Факты» вспомогательный. Скроем его. Выделить столбец – правая кнопка мыши – скрыть (или нажимаем комбинацию горячих клавиш CTRL+0). Скрытые столбцы.
  6. Выделяем три столбца. Переходим на вкладку «Диаграммы» — нажимаем «Гистограмма». График Парето.
  7. Выделяем вертикальную ось левой кнопкой мышки. Затем нажимаем правую клавишу и выбираем «Формат оси». Устанавливаем максимальное значение 1 (т.е. 100%). Формат оси.
  8. Добавляем для каждого ряда подписи данных (выделить – правая кнопка – «Добавить подписи данных»).
  9. Выделяем ряд «Сум.влиян.» (на рис. – зеленый). Правая кнопка мыши – «Изменить тип диаграммы для ряда». «График» — линия.

Исходя из диаграммы Парето, видно, что больше всего на результат повлияли причины 3, 5 и 1.

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