✅ Линейная интерполяция в Excel - Как использовать линейную интерполяцию в Excel?

Что такое интерполяция?

Линейная интерполяция Excel (Содержание)

Что такое интерполяция?
Интерполяция с функцией прогноза в Excel
Примеры линейной интерполяции в Excel

Что такое интерполяция?

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

Например: если мы едем на велосипеде со скоростью 60 км / час и достигаем определенного пункта назначения за 1 час, а если мы едем со скоростью 45 км / час и достигаем определенного пункта назначения за 45 минут. Так сколько же потребуется времени, чтобы добраться до пункта назначения, если мы едем со скоростью 30 км / час.

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

= B2 + (A4-A2) * (B3-B2) / (A3-A2)

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

Давайте попробуем разбить вышеприведенную формулу и понять формулу в деталях.

= B2 + (A4-A2) * (B3-B2) / (A3-A2)

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

= B2 + (A4-A2) * (B3-B2) / (A3-A2)

Во втором разделе (синим цветом выше) вычисляется, насколько далеко наша скорость велосипеда от первой заданной скорости велосипеда, а затем умножает ее на значение, рассчитанное выше. Исходя из нашего примера, это 30 (ячейка A4) минус 60 (ячейка A2), результат которого затем умножается на 1 (что равно -30).

= B2 + (A4-A2) * (B3-B2) / (A3-A2)

Наконец, первый раздел формулы (коричневым цветом выше); мы добавляем первое значение скорости велосипеда. В нашем примере это дает конечный результат 60 + (-30) * (1) = 30 минут. В школе мы использовали следующую формулу для расчета недостающего значения Y.

Y = Y1 + (X-X1) * (Y2-Y1) / (X2 - X1)

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

В Excel есть встроенная функция, которая выполняет вычисления, аналогичные описанным выше, и она известна как функция ПРОГНОЗ. Теперь мы подробно изучим эту функцию.

Интерполяция с функцией прогноза в Excel

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

Синтаксис функции прогноза

Аргументы функции прогноза:

x - это точка данных, для которой мы хотим знать результирующее значение.
Known_y's - диапазон ячеек, содержащих значения Y.
Known_x's - диапазон ячеек, содержащих значения X.

Теперь, рассмотрев тот же пример, приведенный выше, попробуем использовать функцию Forecast.

В нашем примере

x - ячейка B4 (так как мы хотим узнать время, затрачиваемое при скорости в 30 км / час).

Known_y's - от ячейки B2 до ячейки B3 (время, необходимое для известной скорости велосипеда).

Known_x's - от ячейки A2 к ячейке A3 (скорость велосипеда, указанная нами, для которой мы знаем время, затраченное).

Итак, окончательная формула в ячейке B4 будет такой:

= ПРОГНОЗ (А4, В2: В3, А2: А3)

И, как вы можете видеть, конечный результат такой же, то есть 30 минут .

Для создания диаграммы перейдите в меню «Вставка», нажмите «Разброс», а затем выберите «Разброс с гладкими линиями и маркерами».

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

Примеры линейной интерполяции в Excel

Давайте разберемся с линейной интерполяцией в Excel на нескольких примерах.

Вы можете скачать этот шаблон Excel с линейной интерполяцией здесь - Шаблон Excel с линейной интерполяцией

Пример № 1

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

Посмотрите на таблицу ниже. У вас есть данные о продажах с 2016 по 2018 год, и вы хотите знать, какой должна быть прибыль, если ваши продажи составляют рупии. 40 000 000 в 2019 году.

Таким образом, с помощью функции прогнозирования мы можем интерполировать прибыль 2019 года, когда продажи составляют рупии. 4000000

Формула в ячейке C5 будет выглядеть следующим образом:

= ПРОГНОЗ (B5, C2: C4, B2: B4)

После использования формулы прогноза ответ показан ниже.

Результат для функции Прогноз будет Rs. 875 000 на основе данных о продажах и прибыли, доступных с 2016 по 2018 год.

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

Пример № 2

У нас есть данные о последних 9 матчах команды по крикету, в которой пропали калитки и забиты команды.

Мы хотим выяснить, сколько пробежек принесет команда, если выпадет 8 калиток.

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

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

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

Что нужно помнить о линейной интерполяции в Excel

Функция прогнозирования используется для прогнозирования / оценки значения на основе существующих доступных значений, но также может помочь в интерполяции отсутствующего значения.
В случае, если данные не являются линейными, функция «Прогноз» не даст вам точного значения, основанного на линейной интерполяции, но даст вам ближайшее значение.
Если x в функции Forecast является текстовым или не числовым, функция Forecast возвращает ошибку #VALUE.
Если Known_x и Known_y не содержат каких-либо данных или содержат другой набор точек данных, функция Forecast выдаст ошибку # N / A.
Если дисперсия Known_x равна нулю, тогда функция Forecast выдает ошибку # DIV / O.

Линейная интерполяция в Excel - Как использовать линейную интерполяцию в Excel?