Прогнозирование значений в рядах

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

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

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

Начальное значение

Продолжение ряда (арифметическая прогрессия)

1, 2

3, 4, 5...

1, 3

5, 7, 9

100, 95

90, 85

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

  1. Укажите не менее двух ячеек, содержащих начальные значения.

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

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

    Например, если ячейки C1:E1 содержат начальные значения 3, 5 и 8, то при протаскивании вправо значения будут возрастать, влево — убывать.

Совет : Чтобы управлять созданием ряда вручную или заполнять ряд значений с помощью клавиатуры, воспользуйтесь командой Прогрессия (вкладка Главная, группа Редактирование, кнопка Заполнить).

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

Начальное значение

Продолжение ряда (геометрическая прогрессия)

1, 2

4, 8, 16

1, 3

9, 27, 81

2, 3

4.5, 6.75, 10.125

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

  1. Укажите не менее двух ячеек, содержащих начальные значения.

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

  2. Удерживая правую кнопку мыши, перетащите маркер заполнения в нужном направлении для заполнения ячеек возрастающими или убывающими значениями, отпустите правую кнопку, а затем щелкните Экспоненциальное приближение в контекстное меню.

Например, если ячейки C1:E1 содержат начальные значения 3, 5 и 8, то при протаскивании вправо значения будут возрастать, влево — убывать.

Совет : Чтобы управлять созданием ряда вручную или заполнять ряд значений с помощью клавиатуры, воспользуйтесь командой Прогрессия (вкладка Главная, группа Редактирование, кнопка Заполнить).

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

  • Для получения линейного тренда к начальным значениям применяется метод наименьших квадратов (y=mx+b).

  • Для получения экспоненциального тренда к начальным значениям применяется алгоритм расчета экспоненциальной кривой (y=b*m^x).

В обоих случаях не учитывается шаг прогрессии. При создании этих прогрессий получаются те же значения, которые вычисляются с помощью функций ТЕНДЕНЦИЯ и РОСТ.

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

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

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

  2. На вкладке Главная в группе Правка нажмите кнопку Заполнить и выберите пункт Прогрессия.

  3. Выполните одно из указанных ниже действий.

    • Если необходимо заполнить значениями ряда часть столбца, выберите вариант по столбцам.

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

  4. В поле Шаг введите число, которое определит значение шага прогрессии.

Тип прогрессии

Результат шага

Арифметическая

Шаг — это число, добавляемое к каждому следующему члену прогрессии.

Геометрическая

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

  1. В разделе Тип выберите тип прогрессии: арифметическая или геометрическая.

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

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

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

Эта процедура предполагает, что диаграмма, основанная на существующих данных, уже создана. Если это еще не сделано, просмотрите раздел Создание диаграмм.

  1. Щелкните диаграмму.

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

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

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

  5. Выберите параметры линии тренда, тип линий и эффекты.

    • При выборе типа Полиномиальная введите в поле Степень наибольшую степень для независимой переменной.

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

Примечания : 

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

  • Если к двумерной диаграмме (диаграмме распределения) добавляется скользящее среднее, то это скользящее среднее базируется на порядке расположения значений X в диаграмме. Для получения нужного результата перед добавлением скользящего среднего, возможно, потребуется отсортировать значения X.

Использование функции ПРЕДСКАЗ   . Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это y-значение, соответствующее заданному x-значению. Известные значения — это существующие x- и y-значения; новое значение предсказывается с использованием линейной регрессии. Этой функцией можно воспользоваться для прогнозирования будущих продаж, потребностей в складских запасах или тенденций потребления.

Использование функций ТЕНДЕНЦИЯ и РОСТ   . Функции ТЕНДЕНЦИЯ и РОСТ позволяют экстраполировать будущие y-значения, продолжающие прямую линию или экспоненциальную кривую, наилучшим образом описывающую существующие данные. Эти функции могут возвращать y-значения, соответствующие заданным x-значениям, на базе линейной или экспоненциальной зависимости. Используя существующие x-значения и y-значения, возвращаемые этими функциями, можно построить прямую или кривую, описывающую существующие данные.

Использование функций ЛИНЕЙН и ЛГРФПРИБЛ   . Функции ЛИНЕЙН и ЛГРФПРИБЛ позволяют вычислить прямую линию или экспоненциальную кривую для имеющихся данных. Функции ЛИНЕЙН и ЛГРФПРИБЛ возвращают различные данные регрессионного анализа, включая наклон и точку пересечения линии с осью.

Следующая таблица содержит ссылки на дополнительные сведения об этих функциях.

Функция

Описание

ПРЕДСКАЗ

Прогнозирование значений

тенденция

Прогнозирование линейной зависимости.

РОСТ

Прогнозирование экспоненциальной зависимости.

линейн

Построение линейного приближения.

лгрфприбл

Построение экспоненциального приближения.

При необходимости выполнить более сложный регрессионный анализ — включая вычисление и отображение остатков — можно использовать средство регрессионного анализа в надстройке "Пакет анализа". Дополнительные сведения см. в статье Загрузка пакета статистического анализа.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

К началу страницы

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×