excel - 两个尺度之间的反相关

Question

我有一个从 1 开始的数字范围，以 1 递增到上限，上限可能在 100 到 200 之间。

我正在尝试创建一个函数，将这些数字转换为 0.5 到 20 的范围，其中 1 始终 = 20，最大值始终等于 0.5 其余数字在两者之间进行插值。

假设从一到最大的数字在 A 列中，我已经设法将旧比例转换为新比例:

=(((A1-1)*(20 - 0.5))/(MAX(A:A) - 1))+0.5

可以表示为 (((OldVal - OldMin) * (NewMax - NewMin)) / (OldMax - OldMin)) + NewMin
这正确地转换了比例大小

200 20
199 19.90201005
198 19.8040201
197 19.70603015
196 19.6080402
195 19.51005025
194 19.4120603
    etc...
8   1.185929648
7   1.087939698
6   0.989949749
5   0.891959799
4   0.793969849
3   0.695979899
2   0.59798995
1   0.5

我现在需要做的是反转比例，使最大值(100 到 200)= 0.5，最小值(1)= 20。

我怎么能做到这一点，记住最大值是动态的，可以在 100 到 200 之间的任何地方？是否有另一种方法来解决创建逆相关的原始问题？

理想情况下，如果有一些聪明的数学技巧(你可能猜到我不是数学男孩!)我希望能够在不先计算整个序列的情况下找出序列中的任何数字，例如 returnValue = someFunction(120) ，尽管这不是必需的。

非常感谢。

Answer 1

假设线性插值，可以使用 y = mx + b , 其中 x是您的输入(在这种情况下，1 到 100 到 200 之间的某个未知值)，y是您的输出(在本例中为 20 到 0.5)，斜率 ( m ) 和截距 ( b ) 动态确定为 x 的上限变化。

我将 100 到 200 之间的未知值定义为 z .

我们有两个方程和两个未知数。 (记住 z 是已知的，只是它是动态的)。这是方程组:

20 = m*1 + b
0.5 = m*z + b

一些线性代数:

19.5 = m*(1-z)
m = 19.5/(1-z)

和:

20*z = m*z + b*z (the equation '20 = m*1 + b` multiplied by 'z')
0.5 = m*z + b
20*z - 0.5 = b*(z-1)
b = (20*z - 0.5)/(z-1)

因此，将两者联系起来的方程式是:

y = 19.5*x/(1-z) + (20*z - 0.5)/(z-1)

再次在哪里 z是 100 到 200 之间的数字。