Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1124to1128
1124to1128
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

RANG-Funktion in VBA auf ARRAY anwenden

Forumthread: RANG-Funktion in VBA auf ARRAY anwenden

RANG-Funktion in VBA auf ARRAY anwenden
NoNet
Hallo VBA-Spezialisten,
ich suche ein Synonym für die RANG()-Funktion in VBA, die man auf ARRAYS anwenden kann.
Die RANG()-Funktion selbst erhält man in VBA bekanntlich mit Application.WorksheetFunction.Rank()
Diese funktioniert auch, wenn man als Argumente der Funktion Tabellenbereiche angibt !
Ich möchte jedoch keine Tabellenbereiche angeben (auch nicht das ARRAY temp. in eine Tabelle kopieren !), sondern direkt die Werte des ARRAYs auswerten.
Beispiel : Das ARRAY enthält die Werte {30,8,4,90,5,60,2,1,7}
Ich möchte nun das Ranking des ersten Wertes (30) ermitteln (das Ergebnis müsste 7 lauten, da 30 der 7.grösste Wert im Array ist !).
Mit einer UDF (udfRank - siehe unten) kann ich das ermitteln, ich würde jedoch gerne auf die Excel-eigene Funktion RANK() zurückgreifen. Hat jemand eine tolle Idee dazu ?
Hier ein kleines Beispiel zum Testen mit 10 Zufallswerten im ARRAY :
Sub ArrayTest()
Dim ar(10), intZ As Integer
'Zufallswerte in ARRAY schreiben :
For intZ = 0 To 9
ar(intZ) = Rnd()
Next
'funktioniert nicht :
'MsgBox Application.WorksheetFunction.Rank(ar(0), ar)
[A1:J1] = ar 'Werte in Zeile 1 zur Kontrolle ausgeben
MsgBox udfRank(ar(0), ar)
End Sub
Function udfRank(Wert, Wertereihe, Optional aufsteigend = True)
Dim dblW As Double, lngZ As Long, lngE As Long
For lngZ = LBound(Wertereihe) To UBound(Wertereihe)
If aufsteigend Then
If Wert > Wertereihe(lngZ) Then lngE = lngE + 1
Else
If Wert
Vielen Dank für's Mitdenken, Gruß NoNet
Anzeige
OT
06.01.2010 14:34:22
Reinhard
Hallo NoNet,
30 ist für mich der 7-kleinste Wert bzw. der 3-größte Wert.
Frage noch offen.
Gruß
Reinhard
Klaro - kleiner Verschreiber ;-) _oT - offen !
06.01.2010 14:42:54
NoNet
_oT
AW: RANG-Funktion in VBA auf ARRAY anwenden
06.01.2010 14:43:40
Reinhard
Hallo NoNet,
wenn man das verkürzen könnte...
=(30=KGRÖSSTE({30;8;9;60};1))*1+(30=KGRÖSSTE({30;8;9;60};2))*2+(30=KGRÖSSTE({30;8;9;60};3)) *3+(30=KGRÖSSTE({30;8;9;60};4)) *4...
Gruß
Reinhard
Anzeige
AW: RANG-Funktion in VBA auf ARRAY anwenden
06.01.2010 15:32:54
Wolli
Hallo NoNet,
habe jetzt eine ganze Zeit gedacht und probiert - ohne Erfolg. Daher versuche ich es noch mal mit Logik und erkläre, warum es nicht gehen kann:
1.) Die WorksheetFunction.Rank-Methode verlangt lt. Onlinehilfe "Datentyp: Range, Beschreibung: Bezug – ein Array von oder ein Bezug auf eine Liste von Zahlen. Nichtnumerische Werte in Bezug werden ignoriert. " Auch wenn da eigentlich steht "ein Array von ... Zahlen", heißt das leider noch lange nicht, dass das auch funktioniert.
Debug.Print Application.WorksheetFunction.Rank(2, Array(2, 5, 1, 7, 3))
führt zu einem Fehler, es wird also definitiv ein "Range"-Objekt benötigt.
2.) Onlinehilfe: "Range-Objekt - Stellt eine Zelle, eine Zeile, eine Spalte, eine Auswahl von Zellen aus einem oder mehreren zusammenhängenden Zellblöcken oder einen 3D-Bereich dar."
Es scheint mir also nicht möglich zu sein, ein Range-Objekt zu verwenden, das NICHT auf einen Tabellenbereich verweist.
Ich hatte auch überlegt, ob man irgendwie ein "virtuelles" Range-Objekt neu erstellen kann, das sich nicht auf einen vorhandenen Zellbereich bezieht, aber da sehe ich schwarz.
Es liefe also darauf hinaus, tatsächlich das Array in einen (möglicherweise versteckten) Zellbereich zu kopieren und anschließend die Funktion zu verwenden.
Viele Grüße, Wolfgang
Anzeige
So ist es! Wenn man eine udFkt schreibt...
06.01.2010 16:04:38
Luc:-?
...und ein Argument As Range deklariert, NoNet & Wolfgang,
kann man dem ja auch keinen Variant zuweisen! So ist das halt mit den nachträglich installierten xlFktt (von KrisTech ?); sie lassen die „Genialität des Ursprungs“ vermissen... :->
Gruß Luc :-?
Damke - ich gebe mich geschlagen
06.01.2010 19:56:21
NoNet
Hallo Leute,
vielen Dank für eure Statements - ich gebe mich nun geschlagen und werde meine eigene UDF (s.o.) verwenden.
Dass die Funktion RANK() als Argumente Bereiche benötigt, ist mir natürlich klar (daher der Hinweis auf "Tabellenbereiche" => RANGE in meiner Fragestellung ;-), ich hatte jedoch gehofft, dass irgendjemand einen genialen Kniff kennt, um das zu umgehen, zumal das bei anderen (Worksheet-)Functions ja auch möglich ist.
Für eine "Quick-and dirty"-Lösung bietet es sich an, das ARRAY temporär in eine Tabelle zu kopieren, dort das Ranking zu ermitteln (klappt bekanntermaßen auch sehr schnell), aber in Mappen, in denen ein Blatt- oder gar Mappenschutz hinterlegt ist, klappt das nicht (da müsste man allenfalls eine neue temp. Mappe erstellen, aber das möchte ich auch nicht).
Reinhards Lösungsvorschlag ist auch ganz nett und trickreich, aber für große Arrays nicht ganz praktikabel (OK, mit einer Schleife wäre das auch kein Hexenwerk, aber da kann ich auch meine UDF verwenden, die ist universeller).
Manchmal darf man eben auch mal auf kleine Wunder hoffen - auch wenn sich das hier leider nicht erfüllt hat ;-)
Danke nochmals, Gruß, NoNet
Anzeige
"...zumal das bei anderen (Worksheet-)...
06.01.2010 23:21:45
Luc:-?
...Functions ja auch möglich ist..."
Eben, NoNet,
die sind halt anders deklariert...
Nebenbei, dass mit T und N als „Array-Stabilisierer“ bzw -Erzeuger ist auch so ein Irrglaube...
Gruß Luc :-?
Das bezog sich auf ARRAY, nicht auf RANGE
07.01.2010 16:59:22
NoNet
Hey Luc,
meine Aussage bezog sich darauf, dass viele WorksheetFunctions sowohl Ranges als auch ARRAYs als Argumente akzeptieren (da diese wohl als Variant deklariert sind) - nicht darauf, ein als RANGE deklariertes Argument "umzubiegen". Es könnte ja sein, dass es eine mir unbekannte Funktion cARRAY2Range (o.ä.) gibt ;-) oder einfach nur eine andere Funktion/Syntax, die das gleiche Ergebnis liefert wie das gewünschte Ergebnis der Application.RANK-Funktion !
Das mit T() und N() liegt Dir wohl quer im Hals oder ;-) - Das hat mit diesem Thread überhaupt nichts zu tun ! Immerhin bewirken N() und T() defakto die Akzeptanz von ARRAYs in anderen Funktionen - auch wenn das nicht die originäre Absicht dieser Funktionen ist :D
Gruß, NoNet
Anzeige
1.Was Anderes hatte ich auch nicht gemeint...
07.01.2010 19:24:35
Luc:-?
...wie man auch unschwer meiner 1.AW entnehmen kann, NoNet!
2. Dass das OT ist, weiß ich doch, aber weil u.a. auch du ein Anhänger von N/T-MxFmln bist, wollte ich dich mal eben so nebenbei veranlassen, diese mal pur mit vbFkt Evaluate zu testen... ;-)
(Da wird wohl auch etwas anders deklariert worden sein — Ggteil INDEX mit Arg2=0 — quasi als Nachtrag zu deiner alten Diskussion vom Vorjahr...)
Gruß Luc :-?
Anzeige
;
Anzeige

Infobox / Tutorial

RANG-Funktion in VBA auf ARRAY anwenden

Schritt-für-Schritt-Anleitung

  1. Erstelle ein neues Modul in deinem Excel-VBA-Editor (Alt + F11).
  2. Definiere dein Array mit den Werten, die du bewerten möchtest. Beispiel: 
    Dim ar() As Variant
ar = Array(30, 8, 4, 90, 5, 60, 2, 1, 7)
  3. Nutze die UDF (User Defined Function), um das Ranking zu ermitteln. Hier ist ein Beispiel für eine einfache UDF: 
    Function udfRank(Wert As Double, Wertereihe As Variant, Optional aufsteigend As Boolean = False) As Long
   Dim lngZ As Long, lngE As Long
   For lngZ = LBound(Wertereihe) To UBound(Wertereihe)
       If (aufsteigend And Wert > Wertereihe(lngZ)) Or (Not aufsteigend And Wert < Wertereihe(lngZ)) Then
           lngE = lngE + 1
       End If
   Next lngZ
   udfRank = lngE + 1
End Function
  4. Rufe die Funktion auf, um das Ranking eines Wertes zu erhalten: 
    MsgBox udfRank(ar(0), ar) ' Gibt das Ranking für den ersten Wert (30) zurück

Häufige Fehler und Lösungen

  • Fehler: "Typ nicht korrekt" beim Aufruf von Rank
    Lösung: Stelle sicher, dass du das Array korrekt übergibst. Application.WorksheetFunction.Rank erwartet ein Range-Objekt, daher musst du die Werte in eine Tabelle kopieren, wenn du diese Funktion verwenden möchtest.

  • Fehler bei der Verwendung von nicht-numerischen Werten
    Lösung: Achte darauf, dass dein Array nur numerische Werte enthält. Nicht-numerische Werte werden ignoriert und können zu unerwarteten Ergebnissen führen.

Alternative Methoden

Wenn du keine eigene UDF nutzen möchtest, kannst du auch die Excel-Funktion RANK in Kombination mit einem temporären Range verwenden. Das geht so:

  1. Kopiere dein Array in eine temporäre Tabelle.
  2. Verwende die RANK-Funktion direkt auf diesem Range. Beispiel: 
    Range("A1:A9").Value = Application.Transpose(ar) ' Transponiere das Array für die Tabelle
RankValue = Application.WorksheetFunction.Rank(Range("A1").Value, Range("A1:A9"))
MsgBox RankValue

Praktische Beispiele

Hier ist ein einfaches Beispiel, das die UDF in Aktion zeigt:

Sub TestRanking()
    Dim ar() As Variant
    ar = Array(30, 8, 4, 90, 5, 60, 2, 1, 7)
    Dim rankValue As Long
    rankValue = udfRank(ar(0), ar)
    MsgBox "Das Ranking von " & ar(0) & " ist: " & rankValue
End Sub

Dieses Skript gibt aus, dass der Wert 30 das 7. größte Element im Array ist.

Tipps für Profis

  • Überlege, deine UDF so zu gestalten, dass sie auch mit größeren Arrays gut funktioniert, indem du die Effizienz der Schleifen und Berechnungen optimierst.
  • Nutze Option Explicit und deklariere alle Variablen, um Fehler zu vermeiden.
  • Teste deine Funktionen gründlich, insbesondere bei der Übergabe von Arrays und Range-Objekten.

FAQ: Häufige Fragen

1. Kann ich die RANG-Funktion auch auf mehrdimensionale Arrays anwenden?
Ja, du kannst eine UDF anpassen, um mehrdimensionale Arrays zu verarbeiten. Beachte jedoch, dass dies die Komplexität der Funktion erhöht.

2. Gibt es eine Möglichkeit, die RANG-Funktion ohne temporäre Tabellen zu nutzen?
Aktuell ist es am einfachsten, die eigene UDF zu verwenden, da die Excel-Funktion RANK ein Range-Objekt erfordert. Temporäre Tabellen sind oft die praktikabelste Lösung.

3. Funktioniert das auch in Excel 365?
Ja, die oben genannten Methoden funktionieren auch in Excel 365 sowie in älteren Versionen. Achte darauf, dass die VBA-Umgebung aktiviert ist.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige