Как интерполировать числа в excel

Интерполяция графика и табличных данных в Excel

Интерполяция – это своего рода «латание» графиков в тех местах, где возникают обрывы линий из-за отсутствия данных по отдельным показателям. Термин интерполяция подразумевает «латание» внутренних обрывов на графике. А если бы «латались» внешние обрывы, то это была-бы уже экстраполяция графика.

Как построить график с интерполяцией в Excel

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

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

Заполните таблицу как показано на рисунке:

Выделите диапазон A1:B4 и выберите инструмент: «Вставка»-«Диаграммы»-«График»-«График с маркерами».

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

Оба эти способа рассмотрим далее на конкретных примерах.

Как видно на рисунках сразу отображены 2 варианта опций «линию» и «нулевые значения». Обратите внимание, как ведет себя график при выборе каждой из них.

Методы интерполяции табличных данных в Excel

Теперь выполним интерполяцию данных в таблице с помощью функции: =НД(). Для этого нужно предварительно сбросить выше описанные настройки графика, чтобы увидеть как работает данный способ.

Примечание. Вместо функции =НД() в ячейку можно ввести просто значение: #Н/Д!, результат будет тот же.

Источник

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

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

Использование интерполяции

Главное условие, при котором можно применять интерполяцию – это то, что искомое значение должно быть внутри массива данных, а не выходить за его предел. Например, если мы имеем набор аргументов 15, 21 и 29, то при нахождении функции для аргумента 25 мы можем использовать интерполяцию. А для поиска соответствующего значения для аргумента 30 – уже нет. В этом и является главное отличие этой процедуры от экстраполяции.

Способ 1: интерполяция для табличных данных

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

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

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

Аналогичным образом устанавливаем в поле «Известные значения x» координаты диапазона с аргументами.

После того, как все нужные данные введены, жмем на кнопку «OK».

Способ 2: интерполяция графика с помощью его настроек

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

Для начала выделяем сплошную синюю линию, которую нужно удалить и жмем на кнопку Delete на клавиатуре.

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

Способ 3: интерполяция графика с помощью функции

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

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

Как видим, в программе Эксель можно выполнить интерполяцию, как табличных данных, используя функцию ПРЕДСКАЗ, так и графика. В последнем случае это осуществимо с помощью настроек графика или применения функции НД, вызывающей ошибку «#Н/Д». Выбор того, какой именно метод использовать, зависит от постановки задачи, а также от личных предпочтений пользователя.

Помимо этой статьи, на сайте еще 12540 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Источник

Интерполировать в Excel

Excel Interpolate (Содержание)

Введение в интерполяцию в Excel

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

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

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

Допустим, у нас есть простой набор данных из двух известных значений x и y, и мы хотим интерполировать значение (т.е. найти соответствующее значение y для значения x) следующим образом:

Итак, простая формула, которая используется для интерполяции этого значения:

Поэтому, когда мы применяем эту формулу к данному набору данных, мы получаем интерполированное значение y как:

Таким образом, мы можем видеть на скриншоте выше, что мы интерполировали значение с двумя известными значениями x и y. Могут быть моменты, когда становится трудно запомнить формулу. Таким образом, функция ПРОГНОЗ может быть использована в таких случаях.

Теперь допустим, что мы хотим интерполировать то же значение в примере 1 с помощью функции FORECAST.

Функция ПРОГНОЗ оценивает значение на основе существующих значений вместе с линейным трендом. Он имеет следующий синтаксис:

ПРОГНОЗ (x, known_y’s, known_x’s)

Итак, давайте теперь посмотрим на скриншот ниже, что происходит, когда мы применяем эту функцию FORECAST для интерполяции заданного значения x:

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

Теперь допустим, что у нас есть набор данных о розничной фирме, с указанием количества дней и соответствующих продаж фирмы в те дни (т. Е. Количества единиц, проданных в те дни), как показано ниже:

В этом случае продажи являются линейными (что также можно проверить вручную или с помощью линейного графика). Теперь давайте посмотрим, как мы используем функцию FORECAST, когда known_y’s и known_x’s вычисляются с использованием функций OFFSET и MATCH:

Давайте сначала посмотрим синтаксис функции OFFSET и функции MATCH:

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

OFFSET (ссылка, строки, столбцы, (высота), (ширина))

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

MATCH (lookup_value, lookup_array, (match_type))

Теперь, если мы хотим оценить продажи, скажем, на 28 дней, мы используем эти функции следующим образом:

Таким образом, первая функция OFFSET, используемая в качестве второго параметра в функции FORECAST, используется для выбора known_y’s (зависимых значений, то есть продаж).

Вторая функция OFFSET, используемая в качестве третьего параметра в функции FORECAST, используется для выбора известных_х (независимых значений, то есть количества дней).

Функция MATCH, используемая в качестве параметра в функции OFFSET, используется для генерации позиции значения, которая должна быть спрогнозирована, и, таким образом, для вычисления количества строк. Столбцы в функции MATCH, т. Е. Второй параметр в ней должен быть 0, так как зависимое значение требуется для того же выбранного столбца.

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

Что нужно помнить о интерполяции в 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)

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

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

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

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

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

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

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

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

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

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

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

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

Примеры линейной интерполяции в 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

Рекомендуемые статьи

Источник

Способы нахождения неизвестных значений посредством интерполяции в Excel

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

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

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

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

Введение

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

Вариантов интерполяции существует некоторое множество, но мы будем рассматривать именно линейную интерполяцию, которую в Excel можно выполнять с помощью функции ПРЕДСКАЗ. Стоит отметить, что сама эта функция имеет более широкие возможности.

Сначала мы будем использовать не все данные из этой таблицы, а только часть. Например – значения m_oed для супесей при коэффициенте пористости e 0,65-0,75. Создайте такую же таблицу в Excel. Обратите внимание, что содержимое должно соответствовать тем же строкам и столбцам, что и в примере.

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

где Y0=2,1; Y1=2,5; X=0,7; X0=0,75; X1=0,65

На рисунке 3 приведена диаграмма с соответствующими обозначениями.

Подставив все эти значения в формулу, получаем:

Y – есть наше искомое значение m_oed для коэффициента пористости 0,70, которое для супеси равно 2,3.

Удобен ли такой расчет на листе бумаги? Не очень, т.к. отнимает много времени. Однако уже хорошо, что он вообще выполняется.

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

ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)

Возвращаемся к нашему примеру и в ячейку M5 запишем известное значение коэффициента пористости – 0,70, а в ячейку N5 впишем следующую формулу:

=ПРЕДСКАЗ(M5;M3:N3;M2:N2)

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

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

Пример 1. Получение коэффициента m_oed

Построим таблицу 5.1 из пункта 5.3.7, СП 22.13330.2016 «Основания зданий и сооружений» Актуализированная редакция СНиП 2.02.01-83* (с изменениями №1, 2, 3) 2019 г. и автоматизируем получение коэффициента m_oed в зависимости от коэффициента пористости e и выбранного типа грунта. Для удобства столбец «0,45-0,55» разобьем на два. Там, где значений нет, поставим 0 (рис. 5).

Так как зависимость значений в таблице не линейная и это наглядно видно, если построить по ним все ту же диаграмму и выполнить линейную аппроксимацию (рис. 6, 7, 8), мы не можем взять сразу весь массив данных.

Поэтому сначала выберем по два значения, внутри диапазона которых будет проведена интерполяция. А затем сделаем это для всех данных из таблицы по очереди.

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

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

Выберите ячейку B2

На вкладке «Данные», в разделе «Работа с данными», выберите «Проверка данных» (рис. 9)

В появившемся окне в разделе «Условия проверки» в качестве типа данных установите «Список»

В строку «Источник» впишите:

Остается только получить нужный результат. Впишите формулу в ячейку B6:

Если вы все сделали правильно, то ваш лист Excel должен иметь следующий вид (рис. 11):

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

Пример 2. Получение расчетного сопротивления глинистых непросадочных грунтов

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

Теперь выберем ячейку B2 и создадим выпадающий список грунтов, как в первом примере. Только в поле «Источник» (см. рис. 10) впишем уже такое содержимое:

В результате лист Excel должен иметь следующий вид (рис. 13).

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

Широко известно, что отобрать образцы песка действительно ненарушенной структуры из инженерно-геологических скважин даже на сегодняшний день чрезвычайно затруднительно, особенно на значительных глубинах и в случаях, когда такие грунты обводнены. Это подтверждал и Лев Геннадьевич Мариупольский в книге «Исследования грунтов для проектирования и строительства свайных фундаментов», Москва, Стройиздат 1989.

Исходя из вышесказанного, получена формула количественного определения плотности p таких песков:

e – коэффициент пористости.

Если подставить в эту формулу коэффициент пористости, то получится следующее:

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

Таблица 1. Значения плотности песков p в зависимости от сопротивления конусу зонда q_c и влажности W по Мариупольскому

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

Теперь перенесем ее на новый лист Excel. Чтобы еще немного усложнить задачу, расположим значения в строке q_c от большего к меньшему. Соответственно, значения p тоже перенесем (рис. 14).

Далее ход действий такой же, как и в предыдущих примерах. Формат ячеек может быть, как общий, так и числовой.

Дополнительно, помимо простой интерполяции, выполним расчет p по тем формулам, которые указывались выше:

Теперь, лист Excel у вас должен иметь следующий вид (рис. 15)

Выводы

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

Список литературы

Источник