Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1056to1060
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

bestimmte Anzahl kleinste, resp. grösste ermitteln

bestimmte Anzahl kleinste, resp. grösste ermitteln
05.03.2009 21:22:15
Peter
Guten Abend
Ich habe eine Spalte mit vielen Zahlen. Der entsprechende Bereich ist mit "Zahlen" benannt.
Wenn ich nun die Summe der drei kleinsten Zahlen ermitteln will, erreiche ich dies mit der Formel
=KKLEINSTE(Zahlen;1)+KKLEINSTE(ZAHLEN;2)+KKLEINSTE(ZAHLEN;3) bei den 3 grössten entsprechend
=KGRÖSSTE(Zahlen;1)+KGRÖSSTE(ZAHLEN;2)+KGRÖSSTE(ZAHLEN;3)
Nun möchte ich mittels VBA eine je Funktion (für grösste / kleinste) basteln, der ich den zwei Variablen übergeben kann, nämlich den Bereichsnamen und eine Zahl, z.B. vier, wenn die vier grössten (resp. kleinsten) Zahlen zu summieren sind.
Wie erhalte ich mittels VBA eine bestimmte Anzahl grösster resp. kleinster Zahlen?
Danke für jede Hilfe.
Gruss, Peter

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
05.03.2009 21:25:40
Renee
Hi Peter,
Wenn du mir noch einen vernünftigen Grund für eine VBA-Routine geben kannst, bitte.
Denn das kann sicher schneller, sicherer und weniger fehleranfällig mit einer Formel gelöst werden.
GreetZ Renée
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
05.03.2009 21:34:49
Peter
Hallo Renée
Es geht um eine Datensimulation. Wenn ich beispielsweise zwischen den drei kleinsten und fünf kleinsten wechseln will, muss ich der Formel
=KKLEINSTE(Zahlen;1)+KKLEINSTE(ZAHLEN;2)+KKLEINSTE(ZAHLEN;3)
+KKLEINSTE(ZAHLEN;4)+KKLEINSTE(ZAHLEN;5)
anfügen.
Das finde ich umständlich. Bei der Datensimulation ist die so gesuchte Summe nur ein Zwischenergebnis.
Gruss, Peter
Anzeige
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
05.03.2009 21:33:22
Josef
Hallo Peter,
dazu brauchts kein VBA.
Tabelle2

 ABCDE
1Zahlen Anzahl2 
283    
344 Summe der größten 2166 
440 Summe der kleinsten 235 
580    
663    
730    
877    
969    
1034    
1145    
1282    
1337    
1457    
1550    
1652    
1778    
1866    
1971    
2031    
2181    
2220    
2372    
2481    
2546    
2615    
2738    
2863    
2983    
3045    
31     

Formeln der Tabelle
ZelleFormel
C3="Summe der größten " &D1
D3{=SUMME(WENN(zahlen>=KGRÖSSTE(zahlen;D1); zahlen))}
C4="Summe der kleinsten " &D1
D4{=SUMME(WENN(zahlen<=KKLEINSTE(zahlen;D1); zahlen))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
D3zahlen=Tabelle2!$A$2:$A$30
D4zahlen=Tabelle2!$A$2:$A$30
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Sepp

Anzeige
Genau, das hatte ich im Hinterkopf ;-)) (owT)
05.03.2009 21:38:04
Renee

AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
05.03.2009 21:39:31
Peter
Hallo Sepp
Vielen Dank - das löst mein Problem und bei Bedarf kann ich das ja auch mit EVALUATE im VBA umsetzen.
Gruss, Peter
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
05.03.2009 21:48:22
Peter
Hallo Sepp
Habe noch etwas an diesen Matrixformeln herumstudiert. Muss zugeben, dass ich nicht schlau geworden bin, wie die Logik dahinter ist.
Kannst du mir das erklären?
Danke, peter
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
05.03.2009 22:04:09
Josef
Hallo Peter,
also wenn du in dieser Formel aus meinem Beispiel
{=SUMME(WENN((zahlen>=KGRÖSSTE(zahlen;D1)*1);zahlen))}


den WENN()-Teil markierst und F9 drückst, dann siehst du das


=SUMME({83;FALSCH;FALSCH;80;FALSCH;FALSCH;77;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH; _
FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;81;FALSCH;72;81;FALSCH;FALSCH;FALSCH;FALSCH;83;FALSCH})


statt FALSCH kannst du dir auch 0 vorstellen, es werden also nur die Zahlen summiert, die größer-gleich als die Größten n-Zahlen sind.
Bei KKLEINSTE() funktioniert das analog.

Gruß Sepp

Anzeige
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
05.03.2009 23:13:10
Peter
Hallo Sepp
Danke schön - da wird mir schon noch etwas trümmlig ...
Nun habe ich es geschafft, das ganze in eine Funktion zu verpacken:
Option Explicit

Public Function ABIG(Bereich As Range, Anzahl As Long)
Dim BName As String
BName = Right(Bereich.Name, Len(Bereich.Name) - 1)
'{=SUMME(WENN(Zahlen>=KGRÖSSTE(Zahlen;D1);Zahlen))}
'Evaluate("SUM(IF(Zahlen>=LARGE(Zahlen,D1),Zahlen))")
ABIG = Evaluate("SUM(IF(" & BName & ">=LARGE(" & BName & "," & Anzahl & ")," & BName & "))")
End Function



Public Function ASMALL(Bereich As Range, Anzahl As Long)
Dim BName As String
BName = Right(Bereich.Name, Len(Bereich.Name) - 1)
'{=SUMME(WENN(Zahlen=SMALL(Zahlen,D1)*1,Zahlen))")
ASMALL = Evaluate("SUM(IF(" & BName & "


Gruss, Peter

Anzeige
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
07.03.2009 19:46:50
Peter
Hallo Sepp
Muss nochmals darauf zurückkommen.
Ich habe festgestellt, dass die Formel in bestimmten Fällen nicht stimmt. Dies ist dann der Fall, wenn Zahlen in der Liste mehrmals vorkommen.
Gibt es da allenfalls eine Abhilfe?
Gruss, Peter
https://www.herber.de/bbs/user/60091.xls
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
07.03.2009 22:25:02
Peter
Hallo Sepp
Ich habe festgestellt, dass die Matrixformel nur die richtige Summe der x kleinsten resp. y grössten Beträge zurückgibt, wenn nicht im Grenzbereich, wo Zahlen noch zu den kleinsten gehören oder nicht, genau gleiche Zahlen vorhanden sind.
Wenn ich also beispielsweise die Summe der 3 kleinesten Zahlen suche und zufälligerweise 4 x die 1 vorkommt, dann erhalte ich als Resultat 4 und nicht drei.
Kann man dem Abhilfe schaffen?
Gruss, Peter
Anzeige
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
07.03.2009 22:58:56
Josef
Hallo Peter,
klar, kein Problem.
Tabelle3

 ABCDE
1Zahlen Anzahl3
2100
3100 Summe der größten 2300
4100 Summe der kleinsten 23
5100
6100
730
877
969
101
111
121
131
1457
1550
1652
1778
1866
1971
2031
2181
2220
2372
2481
2546
2615
2738
2863
2983
3045
31

Formeln der Tabelle
ZelleFormel
D3{=SUMME(KGRÖSSTE(A2:A30;ZEILE(INDIREKT(("1:"&D1)))))}
D4{=SUMME(KKLEINSTE(A2:A30;ZEILE(INDIREKT(("1:"&D1)))))}
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

Anzeige
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
07.03.2009 23:05:35
Peter
Hallo Sepp
Das ist genial. Vielen Dank!
Kannst du mir das Geheimnis verraten, was das ZEILE(INDIREKT(("1.:"&D1))) genau bewirkt?
Gruss, Peter
AW: bestimmte Anzahl kleinste, resp. grösste ermitteln
07.03.2009 23:11:24
Josef
Hallo Peter,
nehmen wir an, in D1 steht 2, dann ergibt

ZEILE(INDIREKT("1:"&D1))


1;2, also wird in KGRÖSSTE einmal mit k=1 und einmal mit k=2 gerechnet.

Gruß Sepp

AW: Danke, owT
07.03.2009 23:14:08
Peter

307 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige