Tooprogram.ru

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

Функция рост в excel

МНК: Экспоненциальная зависимость в EXCEL

Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью экспоненциальной функции.

Метод наименьших квадратов (англ. Ordinary Least Squares , OLS ) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным. Основная статья про МНК — МНК: Метод Наименьших Квадратов в MS EXCEL .

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

y=a*m x =a*(e ln(m) ) x = a*e x*ln(m) =a*e bx , где b= ln(m))

В свою очередь экспоненциальную зависимость y=a*EXP(b*x) при a>0 можно свести к случаю линейной зависимости с помощью замены переменных (см. файл примера ).

После замены переменных Y=ln(y) и A=ln(a) вычисления полностью аналогичны линейному случаю Y=b*x+A. Для нахождения коэффициента a необходимо выполнить обратное преобразование a= EXP(A) .

Примечание : Построить линию тренда по методу наименьших квадратов можно также с помощью инструмента диаграммы Линия тренда ( Экспоненциальная линия тренда ). Поставив в диалоговом окне галочку в поле «показывать уравнение на диаграмме» можно убедиться, что найденные выше параметры совпадают со значениями на диаграмме. Подробнее о диаграммах см. статью Основы построения диаграмм в MS EXCEL .

Следствием замены Y=ln(y) и A=ln(a) являются дополнительные ограничения: a>0 и y>0. При уменьшении х (в сторону больш и х по модулю отрицательных чисел) соответствующее значение y асимптотически стремится к 0. Именно такую линию тренда и строит инструмент диаграммы Линия тренда. Если среди значений y есть отрицательные, то с помощью инструмента Линия тренда экспоненциальную линию тренда построить не удастся.

Чтобы обойти это ограничение используем другое уравнение экспоненциальной зависимости y=a*EXP(b*x)+с, где по прежнему a>0, т.е. при росте х значения y также будут увеличиваться. В качестве с можно взять некую заранее известную нижнюю границу для y , ниже которой у не может опускаться, т.е. у>с. Далее заменой переменных Y=ln(y-c) и A=ln(a) опять сведем задачу к линейному случаю (см. файл примера лист Экспонента2 ).

Если при росте х значения y уменьшаются по экспоненциальной кривой, т.е. a файл примера лист Экспонента3 ).

Функция РОСТ()

Еще одним способом построить линию экспоненциального тренда является использование функции РОСТ() , английское название GROWTH.

Синтаксис функции следующий:

РОСТ( известные_значения_y; [известные_значения_x]; [новые_значения_x]; [конст] )

Для работы функции нужно просто ввести ссылки на массив значений переменной Y (аргумент известные_значения_y ) и на массив значений переменной Х (аргумент известные_значения_x ). Функция рассчитает прогнозные значения Y для Х, указанных в аргументе новые_значения_x . Если требуется, чтобы экспоненциальная кривая y=a*EXP(b*x) имела a=1, т.е. проходила бы через точку (0;1), то необязательный аргумент конст должен быть установлен равным ЛОЖЬ (или 0).

Если среди значений y есть отрицательные, то с помощью функции РОСТ() аппроксимирующую кривую построить не удастся.

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

Наконец, покажем как с помощью функции РОСТ() вычислить коэффициенты уравнения y= a *EXP( b *x).

Примечание : В MS EXCEL имеется специальная функция ЛГРФПРИБЛ() , которая позволяет вычислить коэффициенты уравнения y=a*EXP(b*x). Об этой функции см. ниже.

Чтобы вычислить коэффициент a (значение Y в точке Х=0) используйте формулу =РОСТ(C26:C45;B26:B45;0) . В диапазонах C26:C45 и B26:B45 должны находиться массивы значений переменной Y и X соответственно.

Чтобы вычислить коэффициент b используйте формулу:

= LN(РОСТ(C26:C45;B26:B45;МИН(B26:B45))/ РОСТ(C26:C45;B26:B45;МАКС(B26:B45)))/ (МИН(B26:B45)-МАКС(B26:B45))

Функция ЛГРФПРИБЛ()

Функция ЛГРФПРИБЛ() на основе имеющихся значений переменных Х и Y подбирает методом наименьших квадратов коэффициенты а и m уравнения y= a * m ^x.

Используя свойство степеней a mn =(a m ) n приведем уравнение экспоненциального тренда y= a *EXP( b *x)= a *e b *x = a *(e b ) x к виду y= a * m ^x, сделав замену переменной m= e b =EXP( b ).

Чтобы вычислить коэффициенты уравнения y= a *EXP( b *x) используйте следующие формулы:

= LN(ЛГРФПРИБЛ(C26:C45;B26:B45)) — коэффициент b

= ИНДЕКС(ЛГРФПРИБЛ(C26:C45;B26:B45);;2) — коэффициент a

Примечание : Функция ЛГРФПРИБЛ() , английское название LOGEST, является формулой массива, возвращающей несколько значений . Поэтому, например, для вывода коэффициентов уравнения необходимо выделить 2 ячейки в одной строке, в Строке формул ввести = ЛГРФПРИБЛ(C26:C45;B26:B45) , затем для ввода формулы вместо обычного ENTER нажать CTRL + SHIFT + ENTER .

Функция ЛГРФПРИБЛ() имеет линейный аналог – функцию ЛИНЕЙН() , которая рассмотрена в статье про простую линейную регрессию. Если 4-й аргумент этой функции ( статистика ) установлен ИСТИНА, то ЛГРФПРИБЛ() возвращает регрессионную статистику: стандартные ошибки для оценок коэффициентов регрессии, коэффициент детерминации, суммы квадратов: SSR , SSE и др.

Примечание : Особой нужды в функции ЛГРФПРИБЛ() нет, т.к. с помощью логарифмирования и замены переменной показательную функцию y= a * m ^x можно свести к линейной ln(y)=ln(a)+x*ln(m)=> Y=A+bx. То же справедливо и для экспоненциальной функции y= a *EXP( b *x).

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

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

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

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

Расширенная линейная серия

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

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

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

Перетащите маркер заполнения в нужном направлении, увеличив значения или уменьшив значения.

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

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

Читать еще:  Как включить вкладку разработчик в excel

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

Расширенный ряд для роста

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

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

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

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

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

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

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

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

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

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

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

Выделите ячейку, в которой нужно начать ряд. Ячейка должна содержать первое значение в ряду.

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

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

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

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

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

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

Результат значения шага

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

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

В разделе типвыберите вариант линейный или рост.

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

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

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

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

Щелкните ряд данных, в который вы хотите добавить линия тренда или скользящее среднее.

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

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

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

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

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

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

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

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

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

Ниже показано, как с помощью маркера заполнения создать линейную тенденцию чисел в Excel в Интернете.

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

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

Перетащите маркер заполнения в нужном направлении, увеличив значения или уменьшив значения.

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

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

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

Читать еще:  Value в excel что означает

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

Значения проекта, которые соответствуют прямой линии тренда

Значения проекта, которые соответствуют экспоненциальной кривой

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

Вычисление экспоненциальной кривой на основе существующих данных

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

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

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

Примеры функции РОСТ и прогноз экспоненциального роста в Excel

Функция РОСТ используется для расчета прогнозируемого экспоненциального роста на основе принимаемых на вход известных массивов данных X и Y, и возвращает массив значений для зависимой переменной Y на основе полученных новых данных для массива независимой переменной X.

Метод прогнозируемого экспоненциального роста c использованием функции РОСТ

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

Таблица с исходными данными:

Выделяем диапазон ячеек B10:B11 и используем следующую функцию:

  • B2:B9 – диапазон известных значений количества теплоты, полученные в результате проведения опытов;
  • A2:A9 – диапазон температур, для которых проводились опыты;
  • A10:A11 – диапазон температур, для которых необходимо вычислить предполагаемые значения количества теплоты.

Для ввода формулы используем комбинацию клавиш CTRL+SHIFT+Enter так как формула должна выполняться в массиве. В результате получим:

Визуально заметно явное несоответствие найденных величин диапазону уже известных значений. В Excel существует еще одна функция для прогнозирования на основе известных значений – ТЕНДЕНЦИЯ. Воспользуемся ей и сравним полученные результаты. Для этого выделяем диапазон ячеек C10:C11 и снова в массиве вводим функцию ТЕНДЕНЦИЯ:

Как видно, синтаксические записи функций РОСТ и ТЕНДЕНЦИЯ идентичны, однако они используют различные алгоритмы для вычислений. Для ввода функции ТЕНДЕНЦИЯ снова используем комбинацию клавиш CTRL+SHIFT+Enter. В результате получим:

То есть, в данном примере функция ТЕНДЕНЦИЯ дает более точный прогноз и целесообразно использовать именно ее.

Прогноз эффективности использования рекламного бюджета по функции РОСТ

Пример 2. За 10 дней до окончания 30-дневного месяца было решено определить общую прогнозируемую прибыль сети магазинов в месяц на основании имеющихся данных за прошедшие 20 дней, на протяжении которых прибыль за день в целом постоянно увеличивалась благодаря использованию эффективной рекламы. Необходимо рассчитать, превысит ли прибыль значение в 3 млн. рублей.

Для решения используем следующую формулу:

  • B2:B21 – массив известных значений прибыли за день для первых 20 дней;
  • A2:A21 – массив дней, для которых размер прибыли уже известен;
  • A22:A31 – массив дней, для которых выполняется прогнозирование прибыли.

В результате имеем:

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

=3000000;»Превысит 3 млн. руб»;»Менее 3 млн. руб»)’ >

Прогноз прибыли за месяц с использованием функции РОСТ в Excel

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

  • Количество сделок будет равно показателю за предыдущий месяц;
  • Количество сделок увеличится на 2.

Вводим функцию РОСТ и получаем ошибку #ЗНАЧ!:

Внимание! В данном случае для прогнозирования прибыли будет использовано сочетание двух факторов: номер месяца и число сделок. Поэтому в качестве аргумента [известные_значения_x] необходимо передать диапазон значений A2:B6, а в качестве аргумента [новые_значения_x] – диапазон A7:B7.

Для определения прибыли при условии, что число сделок составит 41, запишем следующую формулу:

Теперь увеличим количество сделок на 2-е:

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

Функция РОСТ в Excel и особенности ее использования

Функция РОСТ имеет следующую синтаксическую запись:

  • известные_значения_y – массив данных, элементы которого характеризуют значения зависимой переменной y в уравнении y=bkx. Аргумент обязателен для заполнения.
  • [известные_значения_x] – массив данных, элементы которого соответствуют известным значениям независимой переменной x в записи y=bkx. Аргумент является необязательным.
  • [новые_значения_x] – массив с новыми значениями независимой переменной x, на основе которых функция выполняет расчет новых значений зависимой переменной y. Аргумент необязателен для заполнения.
  • [конст] – данные логического типа (ИСТИНА или ЛОЖЬ), определяющие значение константы b в уравнении y=bkx. По умолчанию (если аргумент явно не указан), а также при явном указании логического ИСТИНА, коэффициент b вычисляется обычным способом. Если данный параметр принимает значение ЛОЖЬ, дальнейшие расчеты проводятся для уравнения y=kx, поскольку значение b принимается равным единице.
  1. Элементы массива известные_значения_y должны быть взяты из диапазона положительных чисел. При наличии отрицательных значений или значений, равных 0 (нулю), результатом выполнения функции РОСТ будет код ошибки #ЧИСЛО!.
  2. В качестве аргумента [известные_значения_x] может быть передано одно либо несколько множеств значений. Размерности множеств, передаваемых в качестве первого и второго аргументов должны совпадать, если используется единственная переменная. При вводе нескольких переменных в качестве аргумента известные_значения_y должен быть передан вектор. В Excel вектором считается интервал значений, высота которого составляет одну строку, либо ширина которого равна только одному столбцу).
  3. Функция РОСТ интерпретирует каждый столбец или каждую строку массива [известные_значения_x] в качестве отдельной переменной, если массив известные_значения_y содержит только один столбец или только одну строку соответственно.
  4. Если второй аргумент функции явно не указан, то по умолчанию используется массив данных <1;2;3;…;n>, размерность которого соответствует размерности массива известные_значения_y.
  5. Массив [новые_значения_x]должен быть аналогичен по своей структуре массиву [известные_значения_x], то есть содержать строку либо столбец для каждого элемента массива известные_значения_y.
  6. Если третий аргумент рассматриваемой функции явно не указан, считается, что он тождественен значению второго аргумента данной функции. Если второй и третий аргументы опущены, они оба являются массивами типа <1;2;3;…;n>с требованиями, указанными в пункте 4.
  7. Если массив значений передается в качестве константы массива, по правилам записи массивов в Excel необходимо использовать знак «;» для разделения значений, содержащихся в одной строке, и знак «:» для разделения строк.
  1. Функция РОСТ часто используется для аппроксимации (упрощения) значений независимой (x) и зависимой (y) переменных экспоненциальной кривой.
  2. Данная функция принадлежит к классу формул массивов, поэтому при ее использовании необходимо выделить соответствующее количество ячеек, а после ввода всех требуемых аргументов следует нажать сочетание клавиш Ctrl+Shift+Enter для корректного отображения результатов.
  3. В качестве функции экспоненциального роста используется уравнение типа y=bkx.

3.3. Функции прогноза тенденция() и рост()

В тех случаях, когда вас интересует прогноз значений Y на какой-то интервал времени (или другой отрезок независимых переменных), Excel предлагает использование функций прогноза, основанных на линейном или экспоненциальном приближении 13 .

Функция ТЕНДЕНЦИЯ() вычисляет значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известных значений Y и известных значений Х. Возвращает значения Y, в соответствии с этой прямой для заданного массива новые значения Х.

Функция ПРЕДСКАЗ() вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это Y -значение, соответствующее заданному Х — значению. Известные значения — это Х — и Y -значения, а новое значение предсказывается с использованием линейной регрессии. Эта функцию рассчитывает прогнозные данные аналогично функции ТЕНДЕНЦИЯ(). Полученные значения одинаковы.

Функция РОСТ() рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения Y для последовательности новых значений Х, задаваемых с помощью существующих Х— и Y-значений. Функция рабочего листа РОСТ может применяться также для аппроксимации существующих Х— и Y-значений экспоненциальной кривой.

ТЕНДЕНЦИЯ (Известные значения Y; Известные значения Х; Новые значения Х; Константа).

Функция возвращает значения Yв соответствии с линейным трендом.

РОСТ (Известные значения Y; Известные значения Х; Новые значения Х; Константа).

Функция возвращает значения Yв соответствии с экспоненциальным трендом.

«Известные значения Y» — это множество значений Y, которые уже известны для соотношения Y=m*x+b (2.1) или Y=b*m^x (2.40) — при экспоненциальном сглаживании.

«Известные значения X» — это необязательное множество значений Х, которые уже известны для соотношения Y=m*x+b (2.1) или Y=b*m^x (2.40) — при экспоненциальном сглаживании.

«Новые значения X» — это новые значения X, для которых должны быть определены значения Y.

«Константа» — это логическое выражение, которая при значении равном «1» (истина) указывает на необходимость вычисления константы b. Если константа равна «0» (ложь), то b полагается равным «0» или «1» при экспоненциальном сглаживании.

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

Задание 6. Прогнозирование с помощью функций тенденция() и рост()

Имеются следующие данные о товарообороте крупного магазина за 2010 год (рис. 3.6):

Для прогнозирования значения Y для нового значения Х (значения, которое не содержится в исследуемом ряде наблюдений) выполните следующее (в нашем случае – это прогноз на январь 2011 года):

Рис. 3. 6. Таблица с начальными данными

Курсор установите в ячейку D14.

Вызовите функцию ТЕНДЕНЦИЯ() (категория функция «Статистические»).

В диалоговом окне Аргументы функции (рис. 3.7) введите необходимые адреса:

В окно «Известные значения_Y» введите адреса ячеек, содержащих известные значения товарооборотов за 2010 год — Y (С2:С13).

В окно «Известные значения_Х», введите адреса ячеек известных дат — Х (В2:В13)

В окно «Новые значения_Х» введите адрес ячейки, содержащей новое значение расчетного месяца (января 2011) — Х (В14).

Примечание. В предыдущих версиях Excel даты не всегда корректно влияли на конечный результат, поэтому вместо них приходилось использовать значения номеров месяцев 2010 года (А2:А13). Попробуйте пересчитать формулу с использованием этих номеров, например, в ячейке D15, и вы увидите, что результат будет аналогичным. Не забудьте, затем очистить ячейку D15.

Рис. 3. 7. Окно для ввода аргументов функции ТЕНДЕНЦИЯ() для одного значения

Полученный результат округлите до целых чисел кнопкой Уменьшить разрядность. Должно получиться число 249.

Аналогично выполните прогнозирование величины товарооборота на январь 2011 года для магазина, используя функцию РОСТ() (ячейка Е14). Для этого последовательно выполните вышеописанные действия, заменив функцию ТЕНДЕНЦИЯ() на функцию РОСТ(). В результате будет получено значение —24.

Полученные значения 249 и 240 отличаются незначительно друг от друга (на 3,6%), но довольно далеки от величины товарооборота за декабрь 2011 года, равного 190.

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

В ячейки D2:D13 и E2:E13 введите функции ТЕНДЕНЦИЯ() и РОСТ() как формулы массива соответственно. Рассмотрим этот процесс подробно на функции ТЕНДЕНЦИЯ().

Выделите интервал ячеек, куда должны быть возвращены новые значения Y D2:D13.

Откройте Мастер функций и в категории Статистические выберите функцию ТЕНДЕНЦИЯ().

Введите все необходимые данные, как показано на рис. 3.8.

Рис. 3. 8. Окно для ввода аргументов функции ТЕНДЕНЦИЯ()

Завершите ввод данных одновременным нажатием на клавиши CTRL + SHIFT + ENTER, что позволит посчитать функцию ТЕНДЕНЦИЯ() как формулу массива.

Округлите полученные данные до целых чисел.

Аналогичные действия выполните для ячеек E2:E13, куда будет введена функция РОСТ() (рис. 3.9).

Рис. 3. 9. Окно для ввода аргументов функции РОСТ()

Как видно из сравнения фактических и прогнозных данных (рис. 3.14) между ними существуют значительные отличия. Для наглядности постройте Точечную диаграмму продажи для фактических и прогнозных данных. После соответствующей корректировки она может иметь следующий вид (рис. 3.10).

Рис. 3. 10.. Сравнение результатов фактического товарооборота магазина за 2010 год и рассчитанных по функциям ТЕНДЕНЦИЯ() иРОСТ()

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

Самостоятельно вычислите среднее значение СРЗНАЧ в ячейках C15, D15 и Е15. Окно ввода аргументов функции для ячейки С15 приведено на рис. 3.11, а полученные значения – на рис. 3.14

Рис. 3. 11. Окно функции СРЗНАЧдля расчета среднего значения результатов, полученных в блоке ячеекD2:D14

Ссылка на основную публикацию
Adblock
detector