Ich möchte die größten 10 Zahlenwerte (von 12 Zellen) multiplizieren. Wie geht das, ohne das ganze Tabellenblatt zu sortieren (Tabelle A->Z sortieren)?
=Countifs(H10:H19; ">0")*15
So ist die derzeitige Formel. Jetzt nimmt er aber alle Werte von H10 bis H19. Er soll jetzt also die kleinsten 2 Werte rausschmeißen, wobei ">0" bleiben muss, da sich alle anderen Werte ändern können, den Wert ständig wechseln und damit jeder Zeit neue "kleinste Werte" entstehen.
Danke schonmal :)
3 Antworten
H10:H19 sind aber nur 10 Zellen, nicht 12.
Um die 10 größten Zahlen von Spalte H zu addieren und mit 15 multiplizieren würde ich in Excel folgende Formel verwenden:
=SUMME(KGRÖSSTE(H:H;ZEILE(1:10)))*15
und als Matrixformel mit Strg+Shift+Enter (das erzeugt die {} geschweiften Klammern) abschließen. Sichter gibt es in Google Sheets etwas ähnliches. Allerdings schließt das auch negative Zahlen ein, falls sie sich unter den 10 größten Zahlen befinden.
man sollte doch immer erst die anderen AW testen: es geht tatsächlich auch so einfach, auch bei gleichen Grenz-Beträgen!
Zumindest für diese Erkenntnis hat sich die ansonsten fehlinvestierte Zeit gelohnt....
DH!
Was genau willst du multiplizieren? Jede einzelne Zahl, die größer als das zweitkleinste Element größer 0 ist?
In Excel gibt es die Funktion KGRÖSSTE. Schau mal, ob es die Funktion bei Google auch gibt:
=A1*WENN(UND(A1>=KGRÖSSTE(A:A;10);A1>0);15;1)
inclusive der Bedingung >0 (etwas kompliziert, aber geht):
{=SUMME(((A1:A12>=KGRÖSSTE(A1:A12-ZEILE(A1:A12)*10^-9;10)))*A1:A12)*(A1:A12>0)*15}
Habs jetzt nicht ausprobiert, aber es könnte im extrem hinteren Nachkommabereich (<10^-9) Probleme bei sehr hohen Zeilennummern geben, seltenst praxisrelevant.
Auch das ist -wie die von DeeDee- eine Matrixformel, also ohne {} eingeben, aber mit Strg+Shift+Enter (bei jeder Korrektur wieder!) abschließen, dann entsteht die {Matrixformel}.
Erklärung: KGrösste(A1:A12;K) sollte klar sein (zum Kopieren $ setzen!), aber bei gleichen 10. und 11. Zahlen würde die 11 Zahl ja mit-einfließen. .Man muss also dafür sorgen, dass gleiche Zahlen "individualisiert" werden.
Das geht nur, indem man für den Vergleich eine Fkt wie Zeile(in der der Wert steht) mit einem sehr kleinen Anteil (hier 10^-9) miteinbezieht. Erst dann kann man definitiv mit dem 10-größten Wert vergleichen (also Positions-spezifisch!)
Dann wird der Vergleich für die größten 10 WAHR (und nur für diese, die anderen werden FALSCH=>0 !), was durch die Multiplikation zu 1 wird. Diese 1 wird -ebenfalls positionsspezifisch- mit A1:A12 multipliziert, aber auch noch die Bedingung (A1:A12>0) mit hineinmultipliziert.
Danach wird die Summe nochmals *15 genommen
Kommst Du zurecht?
Da Du englisches xl hast: KGrösste heißt LARGE() und Zeile ROW, Summe SUM
ich hab noch was übersehen (der Test erschien plausibel, war aber nicht...):
=SUMME((a1:a12-ZEILE(a1:a12)*10^-9>=KGRÖSSTE(a1:a12-ZEILE(a1:a12)*10^-9;10))*a1:a12)*(a1:a12>0)*15
Das Zeile()-Inkrement muss auch bei den Basiszellen dazugezählt werden, und dann muss es auch >= heissen. Die Zellen mit den reinzumultiplizierenden Werten *a1:a10 bleiben unverändert, die Positions-bezogenen Wahrheitsfaktoren sind ja links schon gefunden.
(übrigens haben Punkt(vor)Strich Vorrang gegenüber Vergleichsoperatoren, falls die Klammersetzung nicht nachvollziehbar wäre. Versteht man Punkt vor Strich heute noch wo das : als / dargestellt wird?)
hab den Grund jetzt, es war ein Klammerfehler. so ists richtig:
{=SUMME((A1:A12-ZEILE(A1:A12)*10^-9>=KGRÖSSTE(A1:A12-ZEILE(A1:A12)*10^-9;10))*(A1:A12)*(A1:A12>0))*15}
oder -für bessere Nachvollziehbarkeit in Abschnitten(wird von xl akzeptiert):
- {=SUMME((
- A1:A12-ZEILE(A1:A12)*10^-9
- >=KGRÖSSTE(
- A1:A12-ZEILE(A1:A12)*10^-9
- ;10))
- *(A1:A12)
- *(A1:A12>0))
- *15}
Wenn die Grenz-Zahlen unterschiedlich sind, geht das, wobei dann noch summiert werden muss.
KGrösste heißt auf englisch einfach LARGE (Hätte KLargest erwartet)