TOP 3 mit Bedingung

Question

Hallo,
ich habe vereinfacht folgende 2 Tabellen.

Tabelle 1 = Übersichtstabelle mit allen Werten
A B C
1 Nummer Schadensname Schaden in [%]
2 111 Schaden1 125%
3 222 Schaden2 95%
4 333 Schaden3 70%
5 333 Schaden4 85%
6 333 Schaden 1 70%


Tabelle 2 = Auswertung TOP 3 nach Name (hier: Nummer 333)
A B C D
1 Schaden in [%] Schadensname Nummer 333
2 85% Schaden4
3 70% Schaden3
4 70% Schaden 1

Spalte A2 wurde folgender Formel berechnet:
{KGRÖSSTE(WENN(Tabelle1!A2:A6=Tabelle2!D1;Tabelle1!C2:C6);1)}

Wie bekomme ich jetzt aber B2 bis B4 raus?

Hab es versucht mit:
INDEX(Tabelle1!B2:B4;VERGLEICH(A2;Tabelle1!C2:C6;0))

Das funktioniert aber nicht. Kann jemand helfen?

Answer 1

Hallo Sunnykind,

klingt nach einer Rainerschen Spezialformel. Für den Moment kann ich dir leider nur eine halbe Lösung anbieten:

Matrixformel für B2 (mit STRG und Enter abschließen):
{=INDEX(Tabelle1!$B$1:$B$6;VERGLEICH($D$1&A2;Tabelle1!$A$1:$A$6&Tabelle1!$C$1:$C$6;0))} Dann runterziehen.

Nochmal vielen Dank an Rainer für diese Funktion.

Leider wird in B3 und B4 beidmalig Schaden 3 zurückgegeben, weil immer nur der erste Wert gesucht wird, der der Nummer und 70% entspricht. Man müsste also noch eine Möglichkeit einbauen, dass auch der zweite oder dritte zutreffende Wert zurückgegeben werden kann. Da hab ich dann aber auch keine Idee wie das geht.

Oder du unterscheidest die beiden 70%-Werte in Tabelle1 durch unsichtbare Nachkommastellen 70,002%, 70,001%. Dann wird das richtige Ergebnis zurückgegeben.

Gruß Mr. K.

Answer 2

Danke für die Antwort.
Eine Lösung wie ich genau das finde was du beschreibst, habe ich auch. Momentan hab ich immer noch das Problem, dass ich wie du nur den ersten Wert bekomme. Nachkommastellen kann ich nicht eingeben, da diese Werte in der Tabelle aus einer Formel ermittelt werden. Leider sind die Werte dafür genau gleich und dürfen auch nicht verändert werden.

Answer 3

Hallo Sunnykind,

in Matrixformeln bin ich leider nicht so bewandert. Ich hatte gehofft diese mit jener Funktion kombinieren zu können, um das gewünschte Ergebnis zu erreichen. Das ist mir leider nicht gelungen.

Was aber in meinen Tests ganz gut klappt, ist eine Benutzerdefinierte Funktion. Gib dazu im VBA-Editor (Alt+11) in einem Standardmodul (z.B. Modul1) den folgenden Code ein:

Function SverweisRang(Matrix As Range, Kritbereich1 As Range, Krit1, Kritbereich2 As Range, Krit2, Position As Long)
For k1 = 1 To Kritbereich1.Rows.Count
If Kritbereich1.Cells(k1, 1) = Krit1 And Kritbereich2.Cells(k1, 1) = Krit2 Then
found = found + 1
If found = Position Then
SverweisRang = Matrix.Cells(k1, 1)
Exit Function
End If
End If
Next k1
End Function

Anschließend gibst du in Tabelle2 in Zelle B2 die folgende Formel ein:

=sverweisrang(Tabelle1!$B$2:$B$6;Tabelle1!$A$2:$A$6;$D$1;Tabelle1!$C$2:$C$6;A2;ZÄHLENWENN($A$2:$A2;A2))

Kannst du dann einfach runterziehen.

Probier mal, ob dir das weiterhilft. Bei mir hat das auch mit 30 Werten ganz gut geklappt.

Gruß Mr. K.

Answer 4

Hallo,

könntest ja mal des hier testen.

https://www.dropbox.com/s/myq4seeho5rppiq/test_nummer.xlsx?dl=0

Gruß
energun223

Answer 5

Hallo,

hier nochmal die selbe Datei nur dasim zweiten Abschnitt die Daten ohne Hilfsspalte ermittelt werden.

https://www.dropbox.com/s/ihf5qcmd7l1qre1/test_nummer2.xlsx?dl=0

Gruß
energun222