XL2003 - kombinierte Matrizenformel

08/06/2011 - 20:36 von Ingo Moch | Report spam
Hallo,

habe ein Problem, mit dem ich auch nach zweitaegigem
experimentieren nicht klarkomme (kombinierte
Matrizenformeln sind halt immer noch nicht so mein Ding) ...

Gegeben ist eine Wertetabelle (W):

ID P1 P2 P3
1 0 5 12
2 8 8 17
3 12 3 12
4 3 8 8

In einem weiteren Blatt befindet sich eine Datentabelle (D):

IstWert ID GesuchteBezeichnung GesuchterWert
20 2
4 1
12 1
4 8

Aufgabe ist es nun, die zu D.Wert passende Ueberschrift zu
finden, wobei D.Wert <= dem Wert in der Wertetabelle sein
muss und davon der kleinste Wert. Sollte der kleinste Wert
mehrfach vorkommen, so ist die davon am weitesten linke
Spalte zu waehlen. Als Ergebnis soll dann die
Spaltenueberschrift zurueckgegeben werden. Zudem kann es
vorkommen, dass D.Wert groesser als der groesste Wert in der
Wertetabelle ist. Dann braeuchte ich den Rest um auf Basis
dessen einen weiteren Suchlauf durchzufuehren. Es sollte am
Ende also so aussehen:

IstWert ID GesuchteBez Rest GesuchteBez2
20 2 P3 3 P1
4 1 P2
12 1 P3
7 4 P2

Waere schoen wenn mir jemand auf die Spruenge helfen
koennte ...

Vielen Dank

Ingo
 

Lesen sie die antworten

#1 Andreas Killer
09/06/2011 - 15:45 | Warnen spam
Am 08.06.2011 20:36, schrieb Ingo Moch:

habe ein Problem, mit dem ich auch nach zweitaegigem
experimentieren nicht klarkomme


Das wird IMHO mit einer Formel auch nicht gehen, da Du zur Ermittlung des Wertes kleiner gleich des Ausgangswertes
SVERWEIS benutzen muss, was dies prinzipiell unterstützt, jedoch nur wenn die Werte sortiert vorliegen. Das ist bei Dir
nicht der Fall.

[Zitat]
Bereich_Verweis ist ein logischer Wert, der angibt, ob SVERWEIS eine genaue oder eine ungefàhre Entsprechung suchen
soll. Wenn dieser Parameter WAHR ist oder weggelassen wird, wird eine ungefàhre Entsprechung zurückgegeben. Anders
ausgedrückt, wird der nàchstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist, wenn keine genaue
Entsprechung gefunden wird.

Wenn Bereich_Verweis WAHR ist, müssen die Werte in der ersten Spalte von Matrix in aufsteigender Reihenfolge angeordnet
werden: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSCH, WAHR; andernfalls gibt SVERWEIS möglicherweise nicht den richtigen Wert
zurück.
[Zitat]

Bei unsortierten Werten wirst Du wohl ein Makro bemühen müssen. Ist (für mich) eh einfacher. ;-)

C2 =SucheWert(A2;SucheID(B2;Tabelle1!$A$1:$D$5))

Und runterziehen.

Andreas.


Option Explicit

Function SucheID(ByVal ID, Bereich As Range) As Range
'Gibt den Datenbereich der Zeile mit der ID in Spalte A zurück
Dim R As Range
For Each R In Bereich.Columns(1).Cells
If R = ID Then
Set SucheID = Bereich.Offset(0, 1).Rows(R.Row).Resize(1, Bereich.Columns.Count - 1)
Exit Function
End If
Next
End Function

Function SucheWert(ByVal Wert As Double, Bereich As Range) As Variant
'Liefert die Überschrift zu dem <= Wert in Bereich
Dim R As Range, Ist As Variant, Count As Integer
Do
For Each R In Bereich
If Wert <= R And Wert > Ist Then
Ist = R
SucheWert = R.Offset(1 - R.Row, 0)
End If
Next
If IsEmpty(SucheWert) Then Wert = Wert - WorksheetFunction.Max(Bereich)
Loop Until Wert < 0 Or Not IsEmpty(SucheWert)
End Function

Ähnliche fragen