如何实现动态范围数值的逆相关线性转换及单值直接计算？

Alright, let's tackle this problem clearly. You need a reverse linear mapping where 1 maps to 20, your max value (100-200) maps to 0.5, plus a way to calculate this with a standalone function. Here's how to do it step by step:

Correct Forward Mapping Formula #

Your original formula was doing an increasing linear map (1→0.5, max→20), but you need the opposite: a decreasing map. The adjusted formula is straightforward—we just flip the direction of the interpolation:

For Excel (cell formula):

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

Generic Form #

If you want to adapt this for other min/max values later:

NewVal = NewMax - ((OldVal - OldMin) * (NewMax - NewMin)) / (OldMax - OldMin)

• OldMin = 1, OldMax = your dynamic max (100-200)
• NewMax = 20, NewMin = 0.5

This works because:

• When OldVal = 1, the subtracted term becomes 0, so result is 20
• When OldVal = OldMax, the subtracted term equals 20 - 0.5 = 19.5, so 20 - 19.5 = 0.5

Inverse Mapping Formula #

If you have a converted value (between 0.5 and 20) and need to get back the original number, we can rearrange the forward formula to solve for OldVal:

For Excel (cell formula):

=1 + ((20 - B1)*(MAX(A:A)-1))/19.5

(Where B1 is your converted value, and MAX(A:A) is the original max)

Generic Inverse Form #

OldVal = OldMin + ((NewMax - NewVal) * (OldMax - OldMin)) / (NewMax - NewMin)

Standalone Function Implementations #

You wanted a function you can call directly (like returnValue = someFunction(120)). Here are examples in common tools/languages:

Excel VBA Custom Function #

Open the VBA editor (Alt+F11), insert a module, and paste this:

Function MapValue(oldVal As Double, maxOld As Double) As Double
    ' Returns the converted value (0.5-20) from original number (1-maxOld)
    If oldVal < 1 Or oldVal > maxOld Then
        MapValue = CVErr(xlErrValue) ' Return error if input is out of range
        Exit Function
    End If
    MapValue = 20 - ((oldVal - 1) * 19.5) / (maxOld - 1)
End Function

Function ReverseMap(newVal As Double, maxOld As Double) As Double
    ' Returns original number (1-maxOld) from converted value (0.5-20)
    If newVal < 0.5 Or newVal > 20 Then
        ReverseMap = CVErr(xlErrValue) ' Return error if input is out of range
        Exit Function
    End If
    ReverseMap = 1 + ((20 - newVal) * (maxOld - 1)) / 19.5
End Function

Now you can use =MapValue(120, 150) or =ReverseMap(10, 150) directly in cells.

Python Functions #

def forward_map(old_val: float, max_old: float) -> float:
    """Convert original value (1 ≤ old_val ≤ max_old) to 0.5-20 range."""
    if not (1 <= old_val <= max_old):
        raise ValueError("Old value must be between 1 and max_old")
    return 20 - ((old_val - 1) * 19.5) / (max_old - 1)

def reverse_map(new_val: float, max_old: float) -> float:
    """Convert mapped value (0.5 ≤ new_val ≤20) back to original range."""
    if not (0.5 <= new_val <= 20):
        raise ValueError("New value must be between 0.5 and 20")
    return 1 + ((20 - new_val) * (max_old - 1)) / 19.5

Call them like:

# Example: max original value is 150
converted = forward_map(120, 150)
original = reverse_map(converted, 150)

Bonus: Range Validation #

Adding range checks (like in the functions above) is a good practice—it prevents invalid inputs from returning nonsensical results (e.g., a converted value greater than 20 or less than 0.5).

内容的提问来源于stack exchange，提问作者Absinthe