Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Zweit häufigste Zahl mit VBA-Funktion | Herbers Excel-Forum


Betrifft: Zweit häufigste Zahl mit VBA-Funktion von: Born
Geschrieben am: 06.12.2009 00:02:18

Guten Morgen,

ich suche schon ein Weilchen nach einer Funktion für die ZWEITHÄUFIGSTE Zahl
in einem Bereich.
Kann mir jemand von Euch vielleicht mit einem Ansatz für eine Formel weiterhelfen?

Ich schätze es läuft darauf hinaus, daß ein Array durchsucht wird, oder? Blos, wie?

Herzlichen Dank,

Born

  

Betrifft: AW: Zweit häufigste Zahl mit VBA-Funktion von: Josef Ehrensberger
Geschrieben am: 06.12.2009 00:14:52

Hallo Born,

Tabelle2

 EFGH
73 Zahl5
84 Anzahl3
94   
104   
113   
123   
133   
144   
154   
163   
171   
182   
195   
205   
212   
223   
232   
242   
253   
264   
273   
285   

Formeln der Tabelle
ZelleFormel
H7{=MIN(WENN(ZÄHLENWENN(E7:E28;E7:E28)=KKLEINSTE(ZÄHLENWENN(E7:E28;E7:E28); 2); E7:E28))}
H8{=SUMME(WENN(E7:E28=H7;1))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4




Gruß Sepp



  

Betrifft: AW: Zweit häufigste Zahl in VBA? von: born2b@gmx.de
Geschrieben am: 06.12.2009 11:39:33

Hallo Sepp,

das ist wirklich eine super-Formel. Wirklich klasse. Obwohl ich die VBA-Version davon suche
habe ich sie studiert, und gelernt.

Trotzdem brauche ich die VBA-Version für die ZWEITHÄUFIGSTE Zahl. Gibts da eine Idee?

Gruß und Dank,

Born


  

Betrifft: AW: Zweit häufigste Zahl in VBA? von: Josef Ehrensberger
Geschrieben am: 06.12.2009 12:30:58

Hallo Born,

kein Problem.

Sub haufigste2()
  Dim strRange As String
  Dim lngFreq As Long, lngResult As Long, lngCount As Long
  
  strRange = "Tabelle1!A1:A22" 'Tabellenbereich
  
  lngFreq = 2 'Gesuchte Häufigkeit
  
  lngResult = Evaluate("MIN(IF(COUNTIF(" & strRange & "," & strRange & ")=SMALL(COUNTIF(" & strRange & "," & strRange & ")," & lngFreq & ")," & strRange & "))")
  lngCount = Evaluate("COUNTIF(" & strRange & "," & lngResult & ")")
  
  MsgBox CStr(lngFreq) & "-häufigste Zahl:" & vbTab & CStr(lngResult) & vbLf & _
    "Häufigkeit von " & CStr(lngResult) & ":" & vbTab & CStr(lngCount)
  
End Sub



Gruß Sepp



  

Betrifft: AW: Zweit häufigste Zahl in VBA? von: Nepumuk
Geschrieben am: 06.12.2009 13:21:09

Servus Sepp,

konnte mir natürlich den Härtetest nicht verkneifen und habe versucht diese Tabelle auszuwerten:

 ABCD
11111
22222
33333
44444
55555
66666
77777
87666
97999
1010101010
1111111111
1212121212
1313131313
141414aaa14
151515aaa15
161616aaa16
17171717#NV
18181818#NV
1919191919
2020202020

Geht nicht. :-(

Mit reinem VBA kein Problem :-)

Option Explicit

Public Sub Beispiel()
    Dim vntArray As Variant, vntItem As Variant
    Dim lngCounter() As Long
    With Tabelle1
        'Bereich A1 - D20
        vntArray = Range(.Cells(1, 1), .Cells(20, 4)).Value2
    End With
    Redim lngCounter(1 To 2, 1 To 1)
    For Each vntItem In vntArray
        If IsNumeric(vntItem) And Not IsEmpty(vntItem) Then
            If UBound(lngCounter, 2) < vntItem Then _
                Redim Preserve lngCounter(1 To 2, 1 To vntItem)
            lngCounter(1, vntItem) = lngCounter(1, vntItem) + 1
            lngCounter(2, vntItem) = vntItem
        End If
    Next
    Call QuickSort(LBound(lngCounter, 2), UBound(lngCounter, 2), lngCounter)
    MsgBox lngCounter(2, 2)
End Sub

Private Sub QuickSort(lngLBound As Long, lngUBound As Long, lngArray() As Long)
    Dim lngIndex1 As Long, lngIndex2 As Long, lngColumn As Long
    Dim lngTemp As Long, lngBuffer As Long
    lngIndex1 = lngLBound
    lngIndex2 = lngUBound
    lngBuffer = lngArray(1, Fix(lngLBound + lngUBound) / 2)
    Do
        Do While lngArray(1, lngIndex1) > lngBuffer
            lngIndex1 = lngIndex1 + 1
        Loop
        Do While lngBuffer > lngArray(1, lngIndex2)
            lngIndex2 = lngIndex2 - 1
        Loop
        If lngIndex1 <= lngIndex2 Then
            For lngColumn = 1 To 2
                lngTemp = lngArray(lngColumn, lngIndex1)
                lngArray(lngColumn, lngIndex1) = lngArray(lngColumn, lngIndex2)
                lngArray(lngColumn, lngIndex2) = lngTemp
            Next
            lngIndex1 = lngIndex1 + 1
            lngIndex2 = lngIndex2 - 1
        End If
    Loop Until lngIndex1 > lngIndex2
    If lngLBound < lngIndex2 Then Call QuickSort(lngLBound, lngIndex2, lngArray())
    If lngIndex1 < lngUBound Then Call QuickSort(lngIndex1, lngUBound, lngArray())
End Sub

Schönen Sonntag noch
Nepumuk


  

Betrifft: AW: Zweit häufigste Zahl in VBA? von: born2b@gmx.de
Geschrieben am: 06.12.2009 13:57:51

Hui, Nepumuk,

das ist beeindruckend!
Ich gebe aber zu, daß ich es nicht ganz begreife: Warum kommt
als Ergebnis 12 heraus?

Ich würde es ja selber weiterbasteln, aber wie geht das mit dem Array?
Du sortierst einen Array mit QuickSort nach Größe?! Dazu hast Du den
Array vorher im Beispiel-Sub auf nur Zahlen reduziert und übergibst
an Quicksort ersten, letzten Wert und Länge des Arrays, oder?

Wie kann ich einen Array nach Häufigkeit sortieren, sodaß ich dann den
zweithäufigsten Wert auslesen kann?

Fragen über Fragen...

Danke,

Born


  

Betrifft: AW: Zweit häufigste Zahl in VBA? von: Nepumuk
Geschrieben am: 06.12.2009 14:30:51

Hallo,

eigentlich ganz einfach. Dabei gehe ich aber von ganzen, positiven Zahlen aus. Es wird ein Array gefüllt mit der Anzahl des jeweiligen Wertes an der Position des Wertes im Array. Also die Anzahl für die Werte 1, 2, 3, 4 .... Damit du dir das besser vorstellen kannst, ungefähr so:

 AB
114
226
337
442
557
667
773
885
996
10107
11114
12128
13132
14149
15158
16169
17178
18186
19194
20201

Dann wird das Array nach der Anzahl sortiert

 AB
1149
2169
3128
4158
5178
637
757
867
9107
1026
1196
12186
1385
1414
15114
16194
1773
1842
19132
20201

und der Wert aus der zweiten Zeile ausgegeben (Im Beispiel haben der erste und der zweite Wert die selbe Anzahl, das ist im Programm auch noch nicht berücksichtigt).

Das ist nicht unbedingt rationell, da es wahrscheinlich viele Arrayzeilen ohne Wert gibt. Man könnte das ganze mit Hilfe einer Collection oder eines Dictionary-Objektes besser machen. Damit könnten dann auch negative Zahlen und Zahlen mit Nachkommastellen verarbeitet werden. Da mir aber nicht bekannt ist, was da für Zahlen kommen können, hab ich es erst mal einfach gehalten.

Gruß
Nepumuk


  

Betrifft: AW: Zweit häufigste Zahl in VBA? von: Born
Geschrieben am: 06.12.2009 17:07:48

Hallo Nepumuk,

danke für die Erklärung. Das ist sehr spannend und doch noch nicht ganz klar, denn
eine Frage bleibt offen: Du schreibst, Du hast "nach Anzahl sortiert", aber die 9er sind
doch gar nicht die Zahlen mit der größten Anzahl, oder? Die häufigste Zahl war doch
die 7?! Habe ich etwas missverstanden?

Mit Dank,

Born


  

Betrifft: AW: Zweit häufigste Zahl in VBA? von: Nepumuk
Geschrieben am: 06.12.2009 20:16:06

Hallo,

ich hab für das zweite Beispiel nicht das selbe Array benutzt wie beim ersten Beispiel. Im zeiten kam die 14 und 16 je 9mal vor, die 12, 15 und 17 je 8mal. Das war Absicht um eventuell zu Erfahren, wie im Falle eines Gleichstandest zu verfahren wäre. Was ist denn für dich in diesem Fall die zweithäufigste Zahl?

Gruß
Nepumuk


  

Betrifft: AW: Zweit häufigste Zahl in VBA? von: born2b@gmx.de
Geschrieben am: 06.12.2009 13:35:58

Salut Sepp,

danke für die Umwandlung. Ich murkse damit noch ein bisschen rum, aber ich vermute,
daß ich doch noch ein wenig etwas anderes brauche. Vielleicht ein Missßverständnis?
Es ich möchte statt der Häufigsten Zahl, die man mit Modalwert bekommt, die zweithäufigste
Zahl herausfinden. Mit obiger Formel kann ich angeben welche Häufigkeit ich wünsche, doch
um die zweithäufigste zu bekommen, müßte ich eine Weile herumprobieren.
Wärst Du bitte so nett, nochmals da drauf zu schauen?

Mit Dank,

Born


Beiträge aus den Excel-Beispielen zum Thema "Zweit häufigste Zahl mit VBA-Funktion"