☰

Wie finde ich den nächstkleineren/nächstgrößeren Wert ohne zu sortieren?

tag-icon • tag-icon microsoft-excel-2010 worksheet-function microsoft-excel-2013
Wie finde ich den nächstkleineren/nächstgrößeren Wert ohne zu sortieren?

Ich habe eine Tabelle, aus der ich den nächstgrößeren und/oder nächstkleineren Wert relativ zu einem Wert in einer separaten Tabelle abrufen muss. Allerdings muss ich auch zulassen, dass die erste Tabelle nach beliebigen Kriterien sortiert werden kann, ohne die Genauigkeit der Suche zu beeinträchtigen.

Ich habe nach verschiedenen Methoden gesucht und gelesen, mit denen man mit VLOOKUP, LOOKUP, HLOOKUP und MATCH den nächstgrößeren/nächstkleineren Wert finden kann, aber alles, was ich finde, scheint zu erfordern, dass die Quellspalte aufsteigend/absteigend sortiert wird, je nachdem, ob man den nächstkleineren/nächstgrößeren Wert haben möchte. Dies unterbricht die Funktionalität, wenn ich beides gleichzeitig verfügbar haben möchte oder die Tabelle sortieren möchte, ohne die Formel zu unterbrechen.

Gibt es eine Möglichkeit, in Excel eine sortierungsunabhängige Suche nach nächstgrößeren/nächstkleineren Werten durchzuführen?

Bevorzugte Lösungen würden ausschließlich native Excel-Funktionen verwenden, da ich derzeit nicht sehr vertraut mit VBScript bin und die Installation von Drittanbieter-Tools derzeit keine Option ist. Lösungen müssen außerdem mit Excel 2010 kompatibel seinUnd2013.

Antwort1

Vorgeschlagenes Ergebnis

Bildbeschreibung hier eingeben

Verwenden von IF und SMALL in der ARRAY-Formel

Wenn das Ergebnis wie auf dem Bild dargestellt das ist, wonach Sie suchen, sieht die Formel zur Ermittlung des nächstgrößeren Werts folgendermaßen aus:
=SMALL(IF(Relative[Value]>[@Value];Relative[Value];99999999999);1)

Erläuterung

  • Relative[Value]>[@Value]gibt ein Array von TRUEoder zurückFALSE
  • IF(Relative[Value]>[@Value];Relative[Value];99999999999)gibt dann die Werte zurück, die aus der relativen Tabelle größer sind, und für die, die es nicht sind, gibt es einen großen Wert außerhalb des Bereichs zurück. Wählen Sie einen aus, der in Ihren Daten nie natürlich vorkommen wird. Alternativ können Sie 0den Wert für die FALSES verwenden oder mit Fehlerwerten arbeiten.
  • Dann verwenden wir einfach die SMALLFunktion mit Argument, k=1um den ersten kleinsten der größeren Werte zu finden.
  • Da es sich um eine Arrayformel handelt, geben Sie die Formel mit STRG+UMSCHALT+EINGABE ein.

Links:

Antwort2

Wenn Ihre Nummern eindeutig sind, funktioniert Folgendes:

Nächstkleinstes:

=SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)+1)

Nächstgrößte:

=SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)-1)

Wenn dies nicht der Fall ist, können Sie mithilfe von Array-Formeln oder Hilfsspalten komplexere Manipulationen durchführen. Sie müssen auch entscheiden, wie Sie mit doppelten Zahlen umgehen möchten (denselben oder einen anderen Wert zurückgeben). Möglicherweise wechseln Sie dazu LARGEund ändern die Sortierreihenfolge von RANK.

Dies sollte Ihnen dennoch einen Ausgangspunkt bieten.

Antwort3

Verwenden Sie eine leere Hilfsspalte (böse Spalte) und kopieren Sie diese und fügen Sie sie ganz nach unten ein. =WENN(B3>JETZT(),B3,"") Ich werde sie Spalte T nennen. Geben Sie dann in das Feld "Als Nächstes" Folgendes ein: **=MIN(T1:T1000)

Als Funktion etwa so:

    Function Soonest(scolumn As String) As Date

'
'
Dim a, b
Dim test(20000) As Date
Dim Min As Date

b = 0

   For a = 1 To 20000
    If (IsEmpty(Range(scolumn & a))) Then
     GoTo SkipMe
    End If

     If (Range(scolumn & a).Value - Now() > 0) Then
     b = b + 1
     test(b) = Range(scolumn & a).Value
     End If
SkipMe:
  Next a

  If b = 0 Then
   Min = "None"
  GoTo NoneFound
  End If
  Min = test(1)
  For c = 1 To b
   If test(c) < Min Then Min = test(c)
  Next c
NoneFound:

Soonest = Min
End Function

verwandte Informationen