oberen und unteren Nachbarwert aus einer Tabelle auslesen
Andreas Freytag
ich hab da ein kleines Excel-Problem und hoffe bei Euch Hilfe zu finden.
Um mir die Arbeit etwas zu erleichtern versuche ich ein Verfahren aus
der Tragwerksplanung zu "verexceln". _Grob_ gesagt geht es dabei darum,
dass man herausfindet wieviel Bewehrungseisen in einen rechteckigen
Betonquerschnitt gehören damit er auch haelt aber das nur am Rande.
Ich ermittle mir dabei einen Wert (k_h), mit dem ich in eine gegebene
Tabelle gehe und einen zweiten Wert (k_s) ablese, mit dem dann die
weitere Berechnung durchgefuehrt wird.
Nun kommt es relativ haeufig vor, dass man nicht exakt einen
Tabellenwert "trifft", sondern mit seinem k_h zwischen 2 Werten landet.
Dann darf zwischen diesen Werten linear interpoliert werden um an das
gewuenschte Ergebnis zu gelangen.
UEber die Funktion SVERWEIS (Bereich_Verweis = WAHR) habe ich den
naechstkleineren Nachbarwert gefunden. Wie aber komme ich den
naechstgroesseren Nachbarwert? An der Frage beisse ich mir gerade die
Zaehne aus.
Unter http://freytag-net.de/kh-Verfahren.xls hab ich die Tabelle mal zum
Download bereitgestellt, vielleicht wird damit deutlicher wo mein
Problem liegt. Die Datei ist ca. 20 kB gross.
Den Wert k_h mit dem ich in die farbige Tabelle gehe habe ich dabei grün
markiert, die beiden gesuchten oberen Nachbarwerte (k_h2 und k_s2) rot.
Ich wuerd mich freuen, wenn jemand mir bei der Loesung hilft.
cu Andy
Anton Haumer
ich hatte ein ähnliches Problem:
ich wollte den nächstgrößeren Wert heraussuchen, und nicht wie
SVerweis den nächstkleineren; ich hab mir daher eine
user-defined Function geschrieben:
Public Function MySVerweis(Suchkriterium As Variant, Matrix As Range, _
Spaltenindex As Long, Optional Minimum As Variant = 1) As Variant
'Durchsucht die erste Spalte des Matrix-Bereiches (es genügt eine
'einzige Spalte) und sucht jene Zeile, deren Wert größer oder gleich
'dem Suchkriterium ist; 'der Matrix-Bereich muss dazu nach der ersten
'Spalte ausfteigend sortiert sein.
'Zurückgegeben wird der Wert in der gefundenen Zeile, der um
'Spaltenindex Zellen rechts (+) oder links (-) vom gefundenen Wert
steht.
'Weist Spaltenindex aus der Tabelle hinaus, wird #Bezug! zurückgegeben.
'Ist Suchkriterium größer als der letzte (größte) Wert des Matrix-
'Bereiches oder kleiner als Minimum * erster (kleinster) Wert des
'Matrix-Bereiches, so wird #NV zurückgegeben.
Dim Zeile As Long
If Matrix.Column + Spaltenindex < 1 Or _
Matrix.Column + Spaltenindex > 255 Then
MySVerweis = CVErr(xlErrRef)
Exit Function
End If
For Zeile = 1 To Matrix.Rows.Count
If Suchkriterium <= Matrix.Cells(Zeile, 1).Value Then
If Suchkriterium >= Minimum * Matrix.Cells(1, 1).Value Then
MySVerweis = Matrix.Cells(Zeile, Spaltenindex + 1).Value
Else
MySVerweis = CVErr(xlErrNA)
End If
Exit Function
End If
Next
MySVerweis = CVErr(xlErrNA)
End Function
Ich nehme an, wenn Du daher den nächstkleineren Wert und den
nächstgrößeren Wert ermitteln kanst, ist die lineare
Interpolation kein Problem mehr für Dich.
LG - Toni
Andreas Freytag schrieb:
Anton Haumer
ich arbeite gerade an einer user-defined function
für Interpolation; wenn Du Interesse an dem VBA-Code hast,
gibt mir eine kurze Nachricht!
Wenn der zu interpolierende Wert unterhalb des kleinsten
oder oberhalb des größten Tabellenwertes liegt,
möchtest Du extrapolieren oder eine Fehlermeldung?
LG - Toni
Peter Dorigo
"Andreas Freytag" <a.fr...@gmx.de> schrieb im Newsbeitrag
news:ldo68vck2n8eovm29...@4ax.com...
[...]
> UEber die Funktion SVERWEIS (Bereich_Verweis = WAHR) habe ich den
> naechstkleineren Nachbarwert gefunden. Wie aber komme ich den
> naechstgroesseren Nachbarwert? An der Frage beisse ich mir gerade die
> Zaehne aus.
Liste aufsteigend sortiert in Spalte A, "Suchwert" in B1:
nächstkleinerer Wert:
=INDEX(A:A;VERGLEICH(B1;A:A))
nächstgrösserer Wert:
=INDEX(A:A;1+VERGLEICH(B1;A:A))
mfg Peter
--
MVP für MS-Excel
---------------------------
Peter Dorigo
"Andreas Freytag" <a.fr...@gmx.de> schrieb im Newsbeitrag
news:ldo68vck2n8eovm29...@4ax.com...
> Hallo zusammen,
[...]
> Nun kommt es relativ haeufig vor, dass man nicht exakt einen
> Tabellenwert "trifft", sondern mit seinem k_h zwischen 2 Werten landet.
> Dann darf zwischen diesen Werten linear interpoliert werden um an das
> gewuenschte Ergebnis zu gelangen.
> UEber die Funktion SVERWEIS (Bereich_Verweis = WAHR) habe ich den
> naechstkleineren Nachbarwert gefunden. Wie aber komme ich den
> naechstgroesseren Nachbarwert? An der Frage beisse ich mir gerade die
> Zaehne aus.
falls es darum geht, den Wert mit der linear geringsten Abweichung zu finden:
=VERWEIS(2*B1-VERWEIS(B1;A:A);A:A)
Suchwert in B1, aufsteigend sortierte Liste in Spalte A
Andreas Freytag
vielen Dank fuer Deine Antworten. Noch hab ich es nicht ausprobieren
koennen aber heute Mittag will ich mich daranmachen und die Tabelle
vervollstaendigen.
>ich arbeite gerade an einer user-defined function
>für Interpolation; wenn Du Interesse an dem VBA-Code hast,
>gibt mir eine kurze Nachricht!
Ja, hab ich. Die lineare Interpolation ist zwar nicht sooo die
Herausforderung aber ich werde um den Rechenweg variabler zu machen das
Ganze wahrscheinlich eh in VB(A) aufziehen.
>Wenn der zu interpolierende Wert unterhalb des kleinsten
>oder oberhalb des größten Tabellenwertes liegt,
>möchtest Du extrapolieren oder eine Fehlermeldung?
Da gibt es geregelte Vorschriften (in Deutschland muss immer alles
irgendwie geregelt sein ;o). Wenn der Tabellenwert (k_h) kleiner ist als
der Grenzwert (k_h*), dann ist dieses Verfahren zu unwirtschaftlich und
es wird auf ein anderes Verfahren ausgewichen. Das werde ich nach
Fertigstellung von diesem Verfahren auch noch mit einbinden.
Wenn der Wert groesser als der groesste Tabellenwert ist, dann stimmt
mit dem Entwurf des Planers etwas nicht. Es gibt zwar Ausnahmen, bei
denen wird dann einfach mit dem groessten vorhandenen Wert gerechnet
aber bei einem guten Statiker sollte das nicht vorkommen. Es gibt auch
Tabellen die höhere Grenzwerte haben aber ich möchte mich doch auf den
sinnvollen Bereich des Verfahrens beschränken.
Gruss Andy
Anton Haumer
wenn Du den folgenden Code in einem VBA-Modul Deiner Arbeitsmappe
ablegst, kannst Du die Funktion wie eine interne Excel-Funktion
(Du findest sie unter Benutzerdefiniert) aufrufen:
Public Function LInt(xWerte As Range, yWerte As Range, x As Double, _
Optional Extrapolation As Boolean = False) As Variant
'Lineare Interpolation
'gibt #Bezug! zurück, wenn die x- und y-Bereiche nicht genau 1 Spalte
'breit, kleiner als mindestens 2 Zeilen oder ungleich lang sind.
'gibt #DIV/0! zurück, wenn bei der Extrapolation die beiden ersten
'respektive die beiden letzten x-Werte gleich sind.
'gibt #NV zurück, wenn der zu interpolierende Wert außerhalb des
'x-Bereiches liegt und Extrapolation = False gesetzt ist.
Dim Z As Long, L As Long
L = xWerte.Rows.Count
If xWerte.Columns.Count > 1 Or yWerte.Columns.Count > 1 _
Or L < 2 Or xWerte.Rows.Count <> yWerte.Rows.Count Then
LInt = CVErr(xlErrRef)
Exit Function
End If
For Z = 1 To L
If Not IsNumeric(xWerte.Cells(Z, 1)) _
Or Not IsNumeric(yWerte.Cells(Z, 1)) Then
LInt = CVErr(xlErrValue)
Exit Function
End If
Next
If Extrapolation And x < xWerte.Cells(1, 1).Value Then
If xWerte.Cells(1, 1).Value = xWerte.Cells(2, 1).Value Then
LInt = CVErr(xlErrDiv0)
Else
LInt = yWerte.Cells(1, 1).Value + _
(x - xWerte.Cells(1, 1).Value) * _
(yWerte.Cells(2, 1).Value - yWerte.Cells(1, 1).Value) / _
(xWerte.Cells(2, 1).Value - xWerte.Cells(1, 1).Value)
End If
Exit Function
End If
If Extrapolation And x > xWerte.Cells(L, 1).Value Then
If xWerte.Cells(L, 1).Value = xWerte.Cells(L - 1, 1).Value Then
LInt = CVErr(xlErrDiv0)
Else
LInt = yWerte.Cells(L, 1).Value + _
(x - xWerte.Cells(L, 1).Value) * _
(yWerte.Cells(L, 1).Value - yWerte.Cells(L - 1, 1).Value) /
_
(xWerte.Cells(L, 1).Value - xWerte.Cells(L - 1, 1).Value)
End If
Exit Function
End If
For Z = 1 To L - 1
If x >= xWerte.Cells(Z, 1).Value And _
x < xWerte.Cells(Z + 1, 1).Value Then
LInt = yWerte.Cells(Z, 1).Value + _
(x - xWerte.Cells(Z, 1).Value) * _
(yWerte.Cells(Z + 1, 1).Value - yWerte.Cells(Z, 1).Value) /
_
(xWerte.Cells(Z + 1, 1).Value - xWerte.Cells(Z, 1).Value)
Exit Function
End If
Next
If x = xWerte.Cells(Z, 1).Value Then
LInt = yWerte.Cells(Z, 1).Value
Else
LInt = CVErr(xlErrNA)
End If
End Function
LG & gutes Gelingen, Toni
Andreas Freytag schrieb:
Andreas Freytag
vielen Dank fuer die Antworten.
>Liste aufsteigend sortiert in Spalte A, "Suchwert" in B1:
>nächstkleinerer Wert:
>=INDEX(A:A;VERGLEICH(B1;A:A))
>nächstgrösserer Wert:
>=INDEX(A:A;1+VERGLEICH(B1;A:A))
...funktioniert einwandfrei! Die Berechnung fuer das erste Verfahren
ohne VB(A) ist damit fertig *feu*
Dankeschoen!
Andy
Peter Dorigo
"Andreas Freytag" <a.fr...@gmx.de> schrieb im Newsbeitrag
news:gl8b8v00h94k1re0t...@4ax.com...
freut mich, dass ich weiterhelfen konnte und Danke für's Feedback!