Microsoft Excel

Herbers Excel/VBA-Archiv

Anzahl der größten Werte


Betrifft: Anzahl der größten Werte von: BeenieMan
Geschrieben am: 16.04.2018 09:50:53

Hallo Excel-Freunde,

sitze aktuell vor einem Problem und komme nicht weiter... :(

Habe in einer Spalte bunt gemischt Zahlen bspw. von 0-1000.
Jetzt möchte ich die Anzahl der größten Werte ausgeben bis die Summe dieser Werte bspw. 512 ist.

Könnt ihr mir da weiterhelfen?

Freue mich über jede Antwort.

Danke & Grüße
BeenieMan

  

Betrifft: 501 von: lupo1
Geschrieben am: 16.04.2018 10:01:28

Werte sortieren (vorher lfdNr vergeben, für einen Rücksort).

Dann http://www.excelformeln.de/formeln.html?welcher=501


  

Betrifft: AW: 501 von: BeenieMan
Geschrieben am: 16.04.2018 10:04:47

Hallo Lupo1,

danke für deine Nachricht.

Gibt es auch eine Möglichkeit ohne Sortierung und ohne lfdNr?

Vielen Dank!


  

Betrifft: Die lfdNr war nie nötig (bitte richtig lesen) ... von: lupo1
Geschrieben am: 16.04.2018 10:08:26

... und eine Hilfsspalte mit KGRÖSSTE bekommst Du ja vielleicht selbst hin?


  

Betrifft: AW: Die lfdNr war nie nötig (bitte richtig lesen) ... von: BeenieMan
Geschrieben am: 16.04.2018 10:20:47

Ja bekomme ich hin :)

Allerdings darf ich die ursprüngliche Tabelle nicht verändern...


  

Betrifft: Hier Datei mit KGRÖSSTE und Kumulierung von: lupo1
Geschrieben am: 16.04.2018 10:17:47

https://www.herber.de/bbs/user/121061.xlsx


  

Betrifft: AW: Hier Datei mit KGRÖSSTE und Kumulierung von: BeenieMan
Geschrieben am: 16.04.2018 10:25:39

Danke Lupo1,

genau so wollte ich es. Am besten wäre es jetzt noch ohne die Hilfsspalten.

Meinst du, dass das funktioniert?

Danke!


  

Betrifft: Könnte sein ... von: lupo1
Geschrieben am: 16.04.2018 10:36:56

... aber dafür würde die Formel 501 aus dem Link noch komplizierter.

Willst Du das? Du willst Deine Anwendung doch verstehen. Und Deine Kinder und Kindeskinder auch.

Ansonsten kommt sicher gleich neopa nach dem Frühstück auf Dich zu ;)


  

Betrifft: AW: Frühstück? Um diese Zeit? ... von: ... neopa C
Geschrieben am: 16.04.2018 17:38:42

Hallo lupo,

... da dachte ich schon an das Mittagessen ;-)

Gruß Werner
.. , - ...


  

Betrifft: Danke an Daniel von: lupo1
Geschrieben am: 16.04.2018 11:01:55

G2: {=MIN(WENN(SUMMEWENN(A:A;">="&KGRÖSSTE(A:A;ZEILE(A1:A1000)))>=G1;ZEILE(A1:A1000)))}


  

Betrifft: AW: diese Formel hat jedoch noch einen Haken ... von: ... neopa C
Geschrieben am: 16.04.2018 17:40:13

Hallo @all,

... nämlich dann, wenn gleichgroße Werte unter den zu ermittelnden größten Werten vorhanden sind.

Gruß Werner
.. , - ...


  

Betrifft: AW: diese Formel hat jedoch noch einen Haken ... von: Daniel
Geschrieben am: 16.04.2018 17:49:33

stimmt.
Aber ich vermute, wenn das berücksichtigt werden soll, ist es einfacher die Tabelle nach Werten aufsteigen zu sortieren.


  

Betrifft: AW: sehe auch keine vertretbare Lösungsformel ... von: ... neopa C
Geschrieben am: 16.04.2018 18:12:59

Hallo,

... für unsortierte Daten. Hab jetzt schon eine Weile daran geknobelt
Wenn Sortierung nicht erwünscht ist, ist z.B. zu der Hilfsspaltenformellösung von Lupo zu raten.

Gruß Werner
.. , - ...


  

Betrifft: AW: heißt nicht, dass es keine geben könnte owT von: ... neopa C
Geschrieben am: 16.04.2018 18:14:29

Gruß Werner
.. , - ...


  

Betrifft: AW: heißt nicht, dass es keine geben könnte owT von: Daniel
Geschrieben am: 16.04.2018 18:34:51

ja dann sind wir gespannt auf deine hilfsspaltenfreie Formellösung für unsortierte Daten.

Gruß Daniel


  

Betrifft: Wenn ich neopa meine, meine ich dabei doch ... von: lupo1
Geschrieben am: 16.04.2018 20:58:18

... meistens auch AGGREGAT.

Denn AGGREGAT hat doch die KGRÖSSTE-Fkt., die TEILERGEBNIS nicht hat.

Aber vielleicht irre ich mich auch.


  

Betrifft: Nein, nur bis 12, Lupo! ;-) owT von: Luc:-?
Geschrieben am: 17.04.2018 02:49:18

:-?


  

Betrifft: AW: man könnte es auch ohne AGGREGAT() lösen ... von: ... neopa C
Geschrieben am: 17.04.2018 09:42:10

Hallo lupo,

... allerdings ist es mE mit der AGGREGAT() einfacher, wenn man es ohne Hilfsspalten lösen möchte.

Gruß Werner
.. , - ...


  

Betrifft: AW: Anzahl der größten Werte von: Daniel
Geschrieben am: 16.04.2018 10:49:12

Hi

ggf mit dieser Matrixformel (Eingabe immer mit STRG+SHIFT+ENTER abschließen:

=MIN(WENN(SUMMEWENN(A:A;">="&KGRÖSSTE(A:A;ZEILE(A1:A1000)))>=512;ZEILE(A1:A1000)))
in den Zeile-Funktionen musst du den Zellbereich mindestens so groß angeben, wie du das Ergebnis erwartest.
Im WorstCase also so groß wie du Werte hast, wenn du abschätzen kannst, dass die Anzahl kleiner ist, solltest du das tun um Rechenzeit zu sparen.

Gruß Daniel


  

Betrifft: AW: Anzahl der größten Werte von: Günther
Geschrieben am: 16.04.2018 11:15:14

Moin,
ich löse so etwas recht komfortabel mit Power Query ohne eine Formel...
https://www.herber.de/bbs/user/121062.xlsx

Gruß
Günther


  

Betrifft: AW: Anzahl der größten Werte von: BeenieMan
Geschrieben am: 16.04.2018 16:30:16

Danke an alle! Kann geschlossen werden :)


  

Betrifft: AW: die Aufgabenstellung ist aber eine andere, ... von: ... neopa C
Geschrieben am: 16.04.2018 17:26:37

Hallo Günther,

... als wie von Dir interpretiert. Dieses, Dein Ergebnis, wäre auch sonst mit einer ganz einfachen Formel zu ermitteln.

Gruß Werner
.. , - ...


  

Betrifft: AW: (m)eine Formellösung ohne Hilfsspalte ... von: ... neopa C
Geschrieben am: 17.04.2018 09:40:12

Hallo BeenieMan,

... und zwar für den Fall, dass die Zahlen auch mehrfach vorkommen können, wäre auch nur mit einer Formel möglich. Allerdings würde diese dann sehr unübersichtlich. Deshalb arbeite ich in so einem Fall mit benannten Formeln.

Aus Deinen bisherigen Angaben geht auch nicht eindeutig hervor, wie groß der auszuwertende Bereich sein kann. Nachfolgend werte ich einen fixen Bereich von 998 Zellen aus. Zu empfehlen wäre jedoch, dass der Bereich als "intelligente" Tabelle formatiert wird. Die Formellösung daran angepasst, hätte dann den Vorteil, dass die Formel sich bei Datenbereichsänderung sich dieser dann auch automatisch anpasst.

Meine Formellösung zeige ich nachfolgend am Beispiel von nur 11 Zahlen auf:

 ABE
1unsortiert 49
25 7
32  
49  
56  
66  
7   
8   
96  
10   
115  
129  
139  
145  
158  
16   

Formeln der Tabelle
ZelleFormel
E2=WENNFEHLER(W_US+AUFRUNDEN((E1-INDEX(W_SU;W_US))/INDEX(W_GR;W_US+1); ); "Summe ist zu klein")
Namen in Formeln
ZelleNameBezieht sich auf
E2W_GR=KGRÖSSTE(!$A:$A;ZEILE(!$A$1:$A$999))
E2W_SU=SUMMEWENN(!$A$2:$A$999;">="&W_GR)
E2W_US=AGGREGAT(14;6;ZEILE(!$A$1:$A$999)/(W_SU<!$E$1)/(W_SU>0);1)
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: (m)eine Formellösung ohne Hilfsspalte ... von: BeenieMan
Geschrieben am: 17.04.2018 15:24:44

Hallo Werner,

ich danke dir für deine ausführliche Antwort.
Meinst du es gibt jetzt noch die Möglichkeit in deinem Excel Screenshot bei den 7 Zahlen die notwendig sind um auf die Summe von 49 kommen ein "X" in der Spalte B zu erzeugen?

Dankeschön!
Grüße
BeenieMan


  

Betrifft: AW: (m)eine Formellösung ohne Hilfsspalte ... von: BeenieMan
Geschrieben am: 17.04.2018 15:24:56

Hallo Werner,

ich danke dir für deine ausführliche Antwort.
Meinst du es gibt jetzt noch die Möglichkeit in deinem Excel Screenshot bei den 7 Zahlen die notwendig sind um auf die Summe von 49 kommen ein "X" in der Spalte B zu erzeugen?

Dankeschön!
Grüße
BeenieMan


  

Betrifft: AW: u.a. mit benutzerdefinierten Zahlenformat ... von: ... neopa C
Geschrieben am: 17.04.2018 16:42:06

Hallo BeenieMan,

... in B2 so: "x";; und z.B. mit folgender Formel, die auf die bereits definierten benannten Namen zugreift:

=(A2>KGRÖSSTE(A:A;E$2))+(ZÄHLENWENN(A$2:A2;KGRÖSSTE(A:A;E$2))
<=(AUFRUNDEN((E$1-INDEX(W_SU;W_US))/INDEX(W_GR;W_US+1);)))*(A2=KGRÖSSTE(A:A;E$2))


und Formel nach unten kopieren.

Gruß Werner
.. , - ...


  

Betrifft: AW: u.a. mit benutzerdefinierten Zahlenformat ... von: BeenieMan
Geschrieben am: 19.04.2018 15:41:46

Hallo Werner,

danke für deine Antwort.
Leider ist meine Datenmenge sehr groß und Excel braucht lange um zu rechnen.
Deine Formel funktioniert aber.

Dachte da gibt es vielleicht eine einfachere Lösung:
Schreibe neben die Zahl aus Spalte A ein "x" wenn sie zu den größten gehört (abhängig von E2)

Danke dir!
Grüße
BeenieMan


  

Betrifft: AW: nun, hierzu folgende Anmerkungen ... von: ... neopa C
Geschrieben am: 19.04.2018 16:05:54

Hallo BeenieMan,

... wenn Deine Datenmenge sehr groß ist, ist es verständlich dass die Berechnung sehr lange dauert.
Wenn keine doppelte Zahlen im Spaltenbereich vorkommen, vereinfacht sich die Auswertungsformel und auch die Formel für die Kennzeichnung in Spalte B.

Wenn doppelte vorkommen, vereinfacht sich die Auswertung in Spalte B, wenn die Zahlen in Spalten A abwärts sortiert werden können. Wenn nicht sortiert werden kann/darf, kann man man die Auswertungsgeschwindigkeit der Formel für die Kennzeichnung in Spalte B beschleunigen, wenn mit mit Hilfszellen arbeitet.

Gruß Werner
.. , - ...


  

Betrifft: AW: nun, hierzu folgende Anmerkungen ... von: BeenieMan
Geschrieben am: 19.04.2018 17:20:19

Ok... Nehmen wir an ich kann die Spalte nach Größe sortieren.
Wie lautet dann die Formel?

Danke & schönen Abend!


  

Betrifft: AW: dann wird es natürlich einfacher, denn ... von: ... neopa C
Geschrieben am: 19.04.2018 19:18:06

Hallo BeenieMan,

... mit den gleichen Beispieldaten nun aber sortiert, bedarf es lediglich einer Formel (ohne {} und ohne zusätzliche benannte Formel etc). für die Ermittlung der Anzahl und eine kleine einfache Formel für die Markierung der entsprechenden Werte in Spalte B (Formel nach unten kopieren), sofern dies dann überhaupt noch notwendig sein sollte.

 ABCDEF
1sortiert   46Suchwert
29x  6Ges. Anz Werte
39x    
49x    
58x    
66x    
76x    
86     
95     
105     
115     
122     
13      

Formeln der Tabelle
ZelleFormel
B2=WENN(ZEILE(A1)>E$2;"";"x")
E2=WENNFEHLER(AGGREGAT(15;6;ZEILE(A1:A99)/(SUMMEWENN(BEREICH.VERSCHIEBEN(A2;;;ZEILE(A1:A99); 1); ">0")>E1); 1); "Summe ist zu klein")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: dann wird es natürlich einfacher, denn ... von: BeenieMan
Geschrieben am: 20.04.2018 08:49:03

Cooooooooooool!!!
Danke! Made my day!

Schönes Wochenende!


  

Betrifft: AW: freut mich und ... von: ... neopa C
Geschrieben am: 20.04.2018 09:07:05

Hallo BeenieMan,

... wie ich bereits schrieb sollte auch für unsortierte Daten in Spalte B eine Beschleunigung noch möglich sein, die allerdings die Geschwindigkeit der Auswertung sortierter Daten nicht erreichen wird.

Auch Dir ein schöne WE

Gruß Werner
.. , - ...


Beiträge aus dem Excel-Forum zum Thema "Anzahl der größten Werte"