Почему в экселе нельзя построить линию тренда
Перейти к содержимому

Почему в экселе нельзя построить линию тренда

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

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

Примечание: Эти действия применимы Office 2013 и более новых версиях. Ищете по Office 2010 г.?

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

Ваш браузер не поддерживает видео.

  1. Выделите диаграмму.
  2. Щелкните значок «+» в правом верхнем углу диаграммы.
  3. Выберите пункт Линия тренда.

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

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

замещающий текст

  1. Щелкните в любом месте диаграммы.
  2. На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.
  3. Нажмите кнопку Формат выделения.
  4. В области Формат линии тренда выберите параметр Линия тренда, чтобы выбрать линию тренда для диаграммы. Форматирование линии тренда — это статистический способ измерения данных:
  5. Задайте значение в полях «Вперед» и «Назад», чтобы проецируемые данные в будущем.

Добавление линии скользящего среднего

Линию тренда можно отформатирование на линию скользящего среднего.

  1. Щелкните в любом месте диаграммы.
  2. На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.
  3. Нажмите кнопку Формат выделения.
  4. В области Формат линии тренда в области Параметры линии трендавыберите Скользящие средниезначения . При необходимости укажите баллы.

Примечание: Число точек в линии тренда скользящего среднего равно общему числу точек в ряду, которое меньше числа, заданная для точки.

Добавление линии тренда или скользящего среднего на диаграмму в Office 2010 г.

Добавление линии тренда (Office 2010)

замещающий текст

  1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните ряд данных, для которого требуется добавить линию тренда или линейную фильтрацию, или выполните указанные ниже действия, чтобы выбрать ряд данных из списка элементов диаграммы.
    1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
    2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
  2. Примечание: Если выбрана диаграмма с несколькими рядами данных, но сам ряд данных не выбран, откроется диалоговое окно Добавление линии тренда. В поле со списком выберите нужный ряд данных, а затем нажмите кнопку ОК.

    Группа

  3. На вкладке Макет в группе Анализ выберите пункт Линия тренда.
  4. Выполните одно из указанных ниже действий.
    1. Выберите подходящий предопределенный параметр линии тренда.

    Примечание: Линия тренда будет применена без возможности выбора конкретных параметров.

    Используемый тип

    Создание прямой линии тренда путем расчета по методу наименьших квадратов с помощью следующего уравнения:

    Линейная

    где m — это наклон, а b — смещение.

    Логарифмическая

    Построение логарифмической линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:

    Логарифмическая

    где c и b — константы, а «ln» — натуральный логарифм.

    Полиномиальная

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

    Полиномиальная

    c1.. c6

    где b и являются константами.

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

    Электропитание

    где c и b — константы.

    Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.

    Экспоненциальная

    Построение экспоненциальной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:

    Экспоненциальная

    где c и b — константы, e — основание натурального логарифма.

    Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.

    Линейная фильтрация

    MovingAverage

    Построение линии тренда с линейной фильтрацией по следующей формуле:

    Примечание: Число точек, образующих линию тренда с скользящее среднее, равно общему числу точек ряда за вычетом числа, указанного для параметра «Точки».

    Величина достоверности аппроксимации

    Построение линии тренда с указанием на диаграмме величина достоверности аппроксимации, вычисляемой по следующей формуле:

    RSquaredValue

    Этот параметр линии тренда располагается на вкладке Параметры диалогового окна Добавление линии тренда или Формат линии тренда.

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

    • Если выбран тип Полиномиальная, введите в поле Степень наибольшую степень для независимой переменной.
    • Если выбран тип Линейная фильтрация, введите в поле Точки число точек, используемых для расчета линейного фильтра.
    • При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения X.
    • При добавлении линии тренда в график, гистограмму, диаграмму с областями или линейчатую диаграмму линия тренда рассчитывается исходя из предположения, что значения x равны 1, 2, 3, 4, 5, 6 и т. д. Такое допущение делается вне зависимости от того, являются ли значения x числовыми или текстовыми. Чтобы рассчитать линию тренда на основе числовых значений x, следует использовать точечную диаграмму.
    • В Excel название линии тренда назначается автоматически, но его можно изменить. В диалоговом окне Формат линии тренда в категории Параметры линии тренда в разделе Название аппроксимирующей (сглаженной) кривой выберите параметр Другое, а затем укажите название в поле Другое.
    • Кроме того, можно добавить линейную фильтрацию, которая сглаживает отклонения в данных и более четко показывает форму линии тренда.
    • Если изменить диаграмму или ряд данных таким образом, что они больше не будут поддерживать соответствующую линию тренда (например, если изменить тип диаграммы на объемную диаграмму или изменить представление отчет сводной диаграммы или связанный отчет сводной таблицы), линия тренда больше не будет отображаться на диаграмме.
    • Для данных в строке (без диаграммы) наиболее точные прямые или экспоненциальные линии тренда можно создать с помощью автозаполнения или статистических функций, таких как РОСТ() или ТЕНДЕНЦИЯ().

    Изменение формата линии тренда (Office 2010)

    1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
      1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
      2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы. замещающий текст
    2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда. Группа
    3. Чтобы изменить параметры цвета, типа или тени линии тренда, выберите категорию Цвет линии, Тип линии или Тень и задайте нужные значения.

    Укажите количество периодов, которые нужно включить в прогноз (Office 2010 г.)

    1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
      1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
      2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы. замещающий текст
    2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда. Группа
    3. Чтобы указать число периодов для включения в прогноз, в разделе Прогноз укажите число периодов в поле вперед на или назад на.

    Укажите точку пересечения линии тренда с вертикальной осью (значений) (Office 2010).

    1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
      1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
      2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы. замещающий текст
    2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда. Группа
    3. Установите флажок пересечение кривой с осью Y в точке, а затем в поле пересечение кривой с осью Y в точке введите значение, чтобы задать точку пересечения линии тренда с вертикальной осью (осью значений).

      Примечание: Это можно сделать только при использовании экспоненциальной, прямой или полиномиальной линии тренда.

      Отображение уравнения линии тренда на диаграмме (Office 2010)

      1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
        1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
        2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы. замещающий текст
      2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда. Группа
      3. Чтобы показать на диаграмме уравнение линии тренда, установите флажок показывать уравнение на диаграмме.

        Примечание: Уравнения линии тренда нельзя показать для скользящего среднего.

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

        Отображение величины квадрата R для линии тренда (Office 2010)

        1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, для которой требуется показать величина достоверности аппроксимации, или выполните указанные ниже действия, чтобы выбрать линию тренда из списка элементов диаграммы.
          1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
          2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы. замещающий текст
        2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда. Группа
        3. На вкладке Параметры линии тренда установите флажок поместить на диаграмму величину достоверности аппроксимации (R^2).

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

        Удаление линии тренда (Office 2010)

        1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо удалить, или выполните следующие действия, чтобы выбрать линию тренда из списка элементов диаграммы.
          1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
          2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы. замещающий текст
        2. Выполните одно из указанных ниже действий.
          1. На вкладке Макет в группе Анализ нажмите Линия тренда, а затем выберите пункт Нет. Группа
          2. Нажмите клавишу DELETE.

        Совет: Вы также можете удалить линию тренда сразу после ее добавления на диаграмму, нажав кнопку Отменить панели быстрого доступа или нажав CTRL+Z.

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

        1. В меню Вид выберите пункт Разметка страницы.
        2. На диаграмме выберите ряд данных, к который вы хотите добавить линию тренда, а затем перейдите на вкладку Конструктор диаграмм. Например, щелкните одну из линий графика. Будут выделены все маркер данных этого ряд данных. Вкладка
        3. На вкладке Конструктор нажмите кнопку Добавить элемент диаграммыи выберите линия тренда. замещающий текст
        4. Выберите вариант линии тренда или нажмите кнопку Дополнительные параметры линии тренда.замещающий текстВы можете выбрать один из следующих вариантов:
          • Экспоненциальная
          • Линейная
          • Логарифмическая
          • Полиномиальная
          • Электропитание
          • Линейная фильтрация

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

        Удаление линии тренда

        1. В меню Вид выберите пункт Разметка страницы.
        2. Щелкните диаграмму с линией тренда и перейдите на вкладку Конструктор диаграмм.Вкладка
        3. Нажмите кнопку Добавить элемент диаграммы,выберите линия трендаи щелкните Нет. замещающий текстТакже можно щелкнуть линию тренда и нажать кнопку DELETE.

        Ваш браузер не поддерживает видео.

        1. Щелкните в любом месте диаграммы, чтобы отдемонстрировать вкладку Диаграмма на ленте.
        2. Нажмите кнопку Формат, чтобы открыть параметры форматирования диаграммы.

        Excel для Интернета Формат диаграммы

        Откройте ряд, который вы хотите использовать в качестве основы для линии

        Линия тренда для Excel для Интернета

        тренда

      4. Настройте линию тренда в нужном вам направлении.
      5. Используйте переключатель, чтобы показать или скрыть линию тренда.

      Важно: Начиная с Excel 2005 г., Excel скорректировал способ расчета значения R2 для линейных линий тренда на диаграммах, для которых для перехватить линию тренда установлено значение 0 (0). Эта корректировка исправит вычисления, которые дают неправильные значения R 2, и выровняет вычисление R2 с функцией LINEST. В результате на диаграммах, созданных в предыдущих Excel, могут отображаться разные значения R2. Дополнительные сведения см. в таблице Изменения внутренних вычислений линейных линий тренда на диаграмме.

      Дополнительные сведения

      Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

      Линия тренда в Excel на разных графиках

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

      Рассмотрим, как добавить линию тренда на график в Excel.

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

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

      Нефть.

      1. Построим на основе таблицы график. Выделим диапазон – перейдем на вкладку «Вставка». Из предложенных типов диаграмм выберем простой график. По горизонтали – год, по вертикали – цена. График.
      2. Щелкаем правой кнопкой мыши по самому графику. Нажимаем «Добавить линию тренда». Добавить тренд.
      3. Открывается окно для настройки параметров линии. Выберем линейный тип и поместим на график величину достоверности аппроксимации. Параметры.
      4. На графике появляется косая линия.

      Косая.

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

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

      Внимание. Линию тренда нельзя добавить следующим типам графиков и диаграмм:

      • лепестковый;
      • круговой;
      • поверхностный;
      • кольцевой;
      • объемный;
      • с накоплением.

      Уравнение линии тренда в Excel

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

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

      Линейная аппроксимация

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

      Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

      Контракты.

      На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):

      График1.

      Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).

      Опции.

      Пример.

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

      y = 4,503x + 6,1333

      • где 4,503 – показатель наклона;
      • 6,1333 – смещения;
      • y – последовательность значений,
      • х – номер периода.

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

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

      Экспоненциальная линия тренда

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

      Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

      Электроэнергия.

      Строим график. Добавляем экспоненциальную линию.

      Экспоненциальная.

      Уравнение имеет следующий вид:

      • где 7,6403 и -0,084 – константы;
      • е – основание натурального логарифма.

      Показатель величины достоверности аппроксимации составил 0,938 – кривая соответствует данным, ошибка минимальна, прогнозы будут точными.

      Логарифмическая линия тренда в Excel

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

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

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

      Продажи.

      R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

      Период 14 15 16 17 18 19 20
      Прогноз 1005,4 1024,18 1041,74 1058,24 1073,8 1088,51 1102,47

      Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.

      Полиномиальная линия тренда в Excel

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

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

      Цены на нефть.

      Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.

      6 степень.

      Зато такой тренд позволяет составлять более-менее точные прогнозы.

      • Excel Formula Examples
      • Создать таблицу
      • Форматирование
      • Функции Excel
      • Формулы и диапазоны
      • Фильтр и сортировка
      • Диаграммы и графики
      • Сводные таблицы
      • Печать документов
      • Базы данных и XML
      • Возможности Excel
      • Настройки параметры
      • Уроки Excel
      • Макросы VBA
      • Скачать примеры

      Почему в экселе нельзя построить линию тренда

      Argument ‘Topic id’ is null or empty

      Сейчас на форуме

      © Николай Павлов, Planetaexcel, 2006-2023
      info@planetaexcel.ru

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

      ООО «Планета Эксел»
      ИНН 7735603520
      ОГРН 1147746834949
      ИП Павлов Николай Владимирович
      ИНН 633015842586
      ОГРНИП 310633031600071

      Почему в экселе нельзя построить линию тренда

      На этом шаге мы рассмотрим добавление линии тренда.

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

      Чтобы дополнить диаграмму линией тренда надо воспользоваться командой Диаграмма | Добавить линию тренда . Эту команду можно выбрать из меню, только если выделен диапазон данных. На рисунке 1 показано диалоговое окно Линия тренда .

      Рис.1. Диалоговое окно Линия тренда

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

      Если щелкнуть на вкладке Параметры диалогового окна Линия тренда , то можно увидеть опции, показанные на рисунке 2.

      Рис.2. Вкладка Параметры

      Во вкладке Параметры предлагается определить названия, которые будут включены в легенду, и количество периодов, для которых будут прогнозироваться данные. Две дополнительные опции позволяют отобразить на диаграмме уравнение линии тренда и значение R 2 (величину достоверности аппроксимации).

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

      На следующем шаге мы рассмотрим построение комбинированных диаграмм.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *