Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1616to1620
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

Anzahl der größten Werte

Anzahl der größten Werte
16.04.2018 09:50:53
BeenieMan
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

31
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: 501
16.04.2018 10:04:47
BeenieMan
Hallo Lupo1,
danke für deine Nachricht.
Gibt es auch eine Möglichkeit ohne Sortierung und ohne lfdNr?
Vielen Dank!
Die lfdNr war nie nötig (bitte richtig lesen) ...
16.04.2018 10:08:26
lupo1
... und eine Hilfsspalte mit KGRÖSSTE bekommst Du ja vielleicht selbst hin?
AW: Die lfdNr war nie nötig (bitte richtig lesen) ...
16.04.2018 10:20:47
BeenieMan
Ja bekomme ich hin :)
Allerdings darf ich die ursprüngliche Tabelle nicht verändern...
AW: Hier Datei mit KGRÖSSTE und Kumulierung
16.04.2018 10:25:39
BeenieMan
Danke Lupo1,
genau so wollte ich es. Am besten wäre es jetzt noch ohne die Hilfsspalten.
Meinst du, dass das funktioniert?
Danke!
Könnte sein ...
16.04.2018 10:36:56
lupo1
... 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 ;)
AW: Frühstück? Um diese Zeit? ...
16.04.2018 17:38:42
...
Hallo lupo,
... da dachte ich schon an das Mittagessen ;-)
Gruß Werner
.. , - ...
Danke an Daniel
16.04.2018 11:01:55
lupo1
G2: {=MIN(WENN(SUMMEWENN(A:A;"&gt="&KGRÖSSTE(A:A;ZEILE(A1:A1000)))&gt=G1;ZEILE(A1:A1000)))}
Anzeige
AW: diese Formel hat jedoch noch einen Haken ...
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
.. , - ...
AW: diese Formel hat jedoch noch einen Haken ...
16.04.2018 17:49:33
Daniel
stimmt.
Aber ich vermute, wenn das berücksichtigt werden soll, ist es einfacher die Tabelle nach Werten aufsteigen zu sortieren.
AW: sehe auch keine vertretbare Lösungsformel ...
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
.. , - ...
AW: heißt nicht, dass es keine geben könnte owT
16.04.2018 18:14:29
...
Gruß Werner
.. , - ...
AW: heißt nicht, dass es keine geben könnte owT
16.04.2018 18:34:51
Daniel
ja dann sind wir gespannt auf deine hilfsspaltenfreie Formellösung für unsortierte Daten.
Gruß Daniel
Anzeige
Wenn ich neopa meine, meine ich dabei doch ...
16.04.2018 20:58:18
lupo1
... meistens auch AGGREGAT.
Denn AGGREGAT hat doch die KGRÖSSTE-Fkt., die TEILERGEBNIS nicht hat.
Aber vielleicht irre ich mich auch.
Nein, nur bis 12, Lupo! ;-) owT
17.04.2018 02:49:18
Luc:-?
:-?
AW: man könnte es auch ohne AGGREGAT() lösen ...
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
.. , - ...
AW: Anzahl der größten Werte
16.04.2018 10:49:12
Daniel
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
Anzeige
AW: Anzahl der größten Werte
16.04.2018 16:30:16
BeenieMan
Danke an alle! Kann geschlossen werden :)
AW: die Aufgabenstellung ist aber eine andere, ...
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
.. , - ...
AW: (m)eine Formellösung ohne Hilfsspalte ...
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
.. , - ...
Anzeige
AW: (m)eine Formellösung ohne Hilfsspalte ...
17.04.2018 15:24:44
BeenieMan
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
AW: (m)eine Formellösung ohne Hilfsspalte ...
17.04.2018 15:24:56
BeenieMan
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
AW: u.a. mit benutzerdefinierten Zahlenformat ...
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))

und Formel nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: u.a. mit benutzerdefinierten Zahlenformat ...
19.04.2018 15:41:46
BeenieMan
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
AW: nun, hierzu folgende Anmerkungen ...
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
.. , - ...
Anzeige
AW: nun, hierzu folgende Anmerkungen ...
19.04.2018 17:20:19
BeenieMan
Ok... Nehmen wir an ich kann die Spalte nach Größe sortieren.
Wie lautet dann die Formel?
Danke & schönen Abend!
AW: dann wird es natürlich einfacher, denn ...
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
.. , - ...
Anzeige
AW: dann wird es natürlich einfacher, denn ...
20.04.2018 08:49:03
BeenieMan
Cooooooooooool!!!
Danke! Made my day!
Schönes Wochenende!
AW: freut mich und ...
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
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige