[Петр-и-Алекс]

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

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

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

Особенности предлагаемых функций:
- таблица может содержать и числовые, и текстовые поля. Но при этом "тексты" должны иметь возможность интерпретации как числа
- десятичным разделителем в записи может служить и точка, и запятая
- порядок возрастания\убывания строк\столбцов в таблице для функций значения не имеет
- минимальное количество аргументов. По умолчанию - их всего пара: "число" и "таблица" (прямоугольный диапазон ячеек)
- функции позволяют искать и "число", и соответствующий ему "ответ" в ЛЮБЫХ строках\столбцах таблицы
- функции представлены в текстах vba, не запаролены, не компилированы. (всё на благо человека


Обязательными аргументами функций являются "число" и "таблица" (прямоугольный диапазон ячеек).
По умолчанию принято, что "число" ищется в первой строке\столбце таблицы, ответ - во второй. но можно задать любую строку\столбец, как для поиска "числа", так и для "ответа".

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

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

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

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

использование файла - свободно

[andriadi]

Attribute VB_Name = "Interpolate"
'Visual Basic Module for Interpolation
'Experimental Soil Mechanics
'J.P.Bardet
'February 1999
Option Base 1
Option Explicit
'Value obtained by linear interpolation at value Z from arrays X and Y
'INTERL performs an extrapolation when any argument is entered after Y.
Function InterL(Z As Single, X As Variant, Y As Variant, Optional Extra) As Single
Dim i As Integer, X1, X2
i = 0
X2 = X(1)
For Each X1 In X
i = i + 1
If Z = X1 And Z = X2 Then
InterL = Y(i)
Exit Function
ElseIf (Z - X1) * (Z - X2) <= 0 And X1 <> X2 Then
InterL = Y(i - 1) + (Y(i) - Y(i - 1)) / (X1 - X2) * (Z - X2)
Exit Function
End If
X2 = X1
Next
If IsMissing(Extra) Then Exit Function
If (X(1) - Z) * (X(1) - X(i)) < 0 Then i = 2
InterL = Y(i - 1) + (Y(i) - Y(i - 1)) * (Z - X(i - 1)) / (X(i) - X(i - 1))
End Function
'Value obtained by power law interpolation at value Z from arrays X and Y
'INTER performs an extrapolation when any argument is entered after Y.
Function Inter(Z As Single, X As Variant, Y As Variant, Optional Extra) As Single
Dim i As Integer, X1, X2
i = 0
X2 = X(1)
For Each X1 In X
i = i + 1
If Z = X1 And Z = X2 Then
Inter = Y(i)
Exit Function
ElseIf (Z - X1) * (Z - X2) <= 0 And X1 <> X2 Then
Inter = Y(i - 1) * (Y(i) / Y(i - 1)) ^ ((Z - X2) / (X1 - X2))
Exit Function
End If
X2 = X1
Next
If IsMissing(Extra) Then Exit Function
If (X(1) - Z) * (X(1) - X(i)) < 0 Then i = 2
Inter = Y(i - 1) * (Y(i) / Y(i - 1)) ^ ((Z - X(i - 1)) / (X(i) - X(i - 1)))
End Function

Например
Interpolate.InterL(0,68;B4:G4;B7:G7)
где 1 - число поиска ()0,68)
2- диапазон таблицы содержащий число (обычно первая строка) (B4:G4)
3 - строка или столбец, где искать (B7:G7)

[Петр-и-Алекс]

andriadi
У меня к Вам ряд вопросов:
Главный: как вы представляете себе перекрестный поиск с интерполяцией в таблице по НЕ КРАЙНИМ столбцам\строкам?
В моей практике такой ситуации не случалось. Если бы привели реальный пример такой ситуации - было бы весьма интересно.

Дальше:
- таблица должна быть как то нормализована по возрастанию/убыванию?
- какие разделители десятичной дроби в ней допускаются?
---
это продолжение диалога, начатого в другом месте

[Tyhig]

Файл не имеет смысла.
Для повседневной работы нет необходимости в интерполяции автоматически. Если есть, то всё решается проще. Например, линией тренда по строкам таблицы. Это ещё более перспективно в плане экстраполяции и автоматом можно и сразу видно что куда стремится.
Для калькуляторов при необходимости, проще написать что-то упрощённое, без скриптов, в файле калькулятора.
Множить макросы ради калькуляторов или начинать калькулятор в чужом файле - бессмысленный риск.

Ексель, конечно, морально устарел 10-20 лет назад.
Но нет выбора. Более платные вещи и стоят и сложнее и менее популярны.

Я бы с радостью освоил какую-нибудь Математику, если бы у меня на каждой работе у каждого она бы стояла. А ексель засуживают авторы математических пакетов... Это тупик, товарищи. Оскал капитализма.
К счастью, это не наш тупик. Нас ничто не сдерживает, кроме желания.

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

[Петр-и-Алекс]

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

[Петр-и-Алекс]

прелестный обзор разных типов интерполяций
https://habr.com/ru/post/264191/
убеждает, что для инженеров лучшее - линейная