Hallihallo!Erst einmal danke für Deinen Tip mit den mehrdimensionalen Matrizen. Leider konnte ich in meiner Ecxel-Hilfe und in diversen Büchern nichts hilfreiches dazu finden.
Ich habe mein Problem jetzt mathematisch gelöst, indem ich einen eindimensonale Matrix über eine Formel so abfrage, als wäre sie zwei dimensional. (Siehe Code-Zeilen mit MW1 bis MW4)
Aufgabe der Funktion ist übrigens für eine beliebige Eingabe von Temperatur und Druck die Spezifische Wärmekapazität von Wasser aus einer zweidimensionalen Zahlenmatrix zu interpolieren.
Hier der Programmcode:
Function cpWasser(TWert, pWert)
Dim XT, Xp, Zähler, Feld
Dim TSpalte As Integer, pZeile As Integer
Dim EingangsvariableT As Single, Eingangsvariablep As Single
Dim iiT As Integer, iip As Integer
Dim MW1 As Double, MW2 As Double, MW3 As Double, MW4 As Double, MW12 As Double, MW34 As Double
Dim T1 As Integer, T2 As Integer, p1 As Integer, p2 As Integer
EingangsvariableT = TWert
Eingangsvariablep = pWert
XT = Array(0, 25, 50, 75, 100, 150, 200, 250, 300, 350, 400, 450, 500, 600, 700, 800)
Xp = Array(1, 5, 10, 50, 100, 150, 200, 250, 300, 350, 400, 450, 500, 600, 700, 800, 900, 1000)
Zähler = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
'Druckzeile
If Eingangsvariablep < Xp(0) Then
pZeile = Zähler(0)
p1 = Xp(0)
p2 = Xp(1)
Else
For iip = 0 To 17
If Eingangsvariablep < Xp(iip) Then
pZeile = Zähler(iip - 1)
p2 = Xp(iip)
p1 = Xp(iip - 1)
iip = 17
End If
Next iip
End If
If Eingangsvariablep >= Xp(17) Then
pZeile = Zähler(16)
p1 = Xp(16)
p2 = Xp(17)
End If
'Temperaturspalte
If EingangsvariableT < XT(0) Then
TSpalte = Zähler(0)
T2 = XT(0)
T1 = XT(1)
Else
For iiT = 0 To 15
If EingangsvariableT < XT(iiT) Then
TSpalte = Zähler(iiT - 1)
T1 = XT(iiT)
T2 = XT(iiT - 1)
iiT = 15
End If
Next iiT
End If
If EingangsvariableT >= XT(15) Then
TSpalte = Zähler(14)
T2 = XT(14)
T1 = XT(15)
End If
'spezifische Wärmekapazität
Feld = Array(4.217, 4.18, 4.181, 4.193, 2.032, 1.979, 1.974, 1.988, 2.011, 2.037, 2.068, 2.099, 2.132, 2.2, 2.27, 2.341, _
4.215, 4.178, 4.18, 4.192, 4.215, 4.31, 2.143, 2.079, 2.065, 2.073, 2.093, 2.118, 2.146, 2.208, 2.276, 2.346, _
4.212, 4.177, 4.179, 4.191, 4.214, 4.308, 2.431, 2.215, 2.141, 2.121, 2.126, 2.141, 2.164, 2.219, 2.283, 2.352, _
4.191, 4.165, 4.17, 4.182, 4.205, 4.296, 4.477, 4.855, 3.199, 2.669, 2.451, 2.36, 2.324, 2.311, 2.347, 2.402, _
4.165, 4.151, 4.158, 4.172, 4.194, 4.281, 4.45, 4.791, 5.703, 4.041, 3.078, 2.726, 2.569, 2.445, 2.429, 2.465, _
4.141, 4.138, 4.148, 4.162, 4.183, 4.266, 4.425, 4.735, 5.495, 8.863, 4.155, 3.235, 2.875, 2.597, 2.517, 2.531, _
4.117, 4.125, 4.137, 4.152, 4.173, 4.252, 4.402, 4.685, 5.332, 8.103, 6.327, 3.959, 3.257, 2.767, 2.61, 2.598, _
4.095, 4.113, 4.127, 4.142, 4.163, 4.239, 4.379, 4.639, 5.201, 7.017, 13.018, 5.02, 3.731, 2.956, 2.709, 2.666, _
4.073, 4.101, 4.117, 4.133, 4.153, 4.226, 4.358, 4.598, 5.091, 6.451, 25.708, 6.624, 4.317, 3.161, 2.814, 2.736, _
4.052, 4.09, 4.107, 4.123, 4.144, 4.214, 4.338, 4.56, 4.999, 6.084, 11.794, 8.875, 5.019, 3.381, 2.925, 2.807, _
4.032, 4.079, 4.098, 4.114, 4.135, 4.202, 4.319, 4.525, 4.918, 5.82, 8.784, 10.887, 5.807, 3.612, 3.04, 2.878, _
4.013, 4.069, 4.089, 4.106, 4.126, 4.19, 4.301, 4.493, 4.848, 5.616, 7.517, 10.827, 6.584, 3.849, 3.158, 2.951, _
3.994, 4.059, 4.081, 4.097, 4.117, 4.179, 4.284, 4.463, 4.786, 5.451, 6.814, 9.483, 7.2, 4.086, 3.278, 3.024, _
3.957, 4.041, 4.064, 4.081, 4.1, 4.157, 4.252, 4.41, 4.681, 5.2, 6.047, 7.466, 7.48, 4.521, 3.515, 3.168, _
3.92, 4.024, 4.049, 4.065, 4.084, 4.137, 4.222, 4.362, 4.595, 5.014, 5.621, 6.44, 6.913, 4.857, 3.736, 3.309, _
3.883, 4.009, 4.035, 4.051, 4.068, 4.117, 4.195, 4.32, 4.523, 4.871, 5.34, 5.844, 6.31, 5.053, 3.928, 3.441, _
3.844, 3.995, 4.022, 4.037, 4.054, 4.099, 4.169, 4.282, 4.462, 4.757, 5.135, 5.465, 5.854, 5.104, 4.082, 3.562, _
3.801, 3.983, 4.01, 4.024, 4.039, 4.081, 4.145, 4.248, 4.41, 4.663, 4.975, 5.203, 5.511, 5.057, 4.196, 3.67)
MW1 = Feld(16 * pZeile + TSpalte)
MW2 = Feld(16 * pZeile + TSpalte - 1)
MW3 = Feld(16 * (pZeile - 1) + TSpalte)
MW4 = Feld(16 * (pZeile - 1) + TSpalte - 1)
MW12 = MW2 + ((MW1 - MW2) * (T2 - TWert) / (T2 - T1))
MW34 = MW4 + ((MW3 - MW4) * (T2 - TWert) / (T2 - T1))
cpWasser = MW34 + ((MW12 - MW34) * (p1 - pWert) / (p1 - p2))
End Function
so long
Katina