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)?

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!

0

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

1
@Iamiam

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?)

1
@Iamiam

ich verstehs zwar noch nicht, aber mit negativen Zahlen -oder zu wenigen positiven?- scheint auch meine Formel nicht zu funktionieren!

Hab aber heute keinen Bock mehr!

0
@Iamiam

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}
0

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)

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)

0

Excelformel den kleinsten und höchsten Wert markieren?

Hallo! Ich habe eine ziemlich große Exceltabelle mit vielen € Werten wo ich in jeder Zeile den kleinsten Wert die Schriftfarbe rot und den größten Wert in Schriftfarbe grün markieren soll. Es sind über 800 Zeilen. Übers Internet habe ich schon ein paar Formel versucht aber es funktioniert nicht :-/ Ich habe Excel 2010. Es gibt aber auch leere Zellen. Die Werte sind als € Währung mit zwei Dezimalstellen formartiert. Ich hoffe ihr könnt mir helfen, ich brauch sonst Stunden damit! Danke

...zur Frage

Wie kann ich bei Microsoft Excel Zeilen automatisch sortieren?

Ich habe ein Problem mit Excel. Ich möchte eine Tabelle erstellen, die sich selbst sortiert, wenn ich Werte ändere. Ich weiß zwar, wie ich das manuell tue (siehe Bild), aber gibt es dafür vllt auch eine Formel?

...zur Frage

Zahlen-Text-Kombination in Excel sortieren

Hallo,

ich muss eine riesige Exceldatei nach einer Zahlen-Text-Kombination sortieren. Diese Spalte hat Werte wie "MUA_1_1", "MUA_1_2", MUA_1_3" etc. Wie kann ich diese Spalte sinnvoll sortieren? Wenn ich sie ganz normal sortiere sieht das dann so aus: MUA_1_1 MUA_1_10 MUA_1_11 MUA_1_2

Vielleicht kann mir jemand eine Formel basteln, die ich in eine Hilfszelle einsetze. Ich kenn mich da leider nicht so aus. Danke für die Hilfe.

...zur Frage

Verbundene Zellen bei Excel

Ich habe ne kurze Frage...

Ich will in Excel 2003 Zellen miteinander verbinden (ich weiß, dass das sicher nicht die beste Lösung ist, aber ist nunmal so) und habe in ca. 5 Zeilen dasselbe stehen (z.B. Abgase, Abgase, Abgase, Abgase, Abgase) und das will ich auch behalten (für das sortieren mit AutoFilter). Wenn ich die Zeilen nun verbinde, wird natürlich nur der erste Wert übernommen, aber dann funktioniert das Suchen mit dem AutoFilter nicht. Gibt es nun eine Möglichkeit beim Zellen verbinden "versteckte" Werte mitzunehmen? Oder gibt es eine andere Möglichkeit (ohne zusätzliche Behelfsspalte)?

...zur Frage

Excel - Kopieren einer Zellen in gefilterter Tabelle

Hallo,

ich habe eine Excel-Tabelle erstellt, in der aufgrund einer SVERWEIS-Formel in einer Spalte einige Werte #NV ergeben. Diese habe ich per Filter ausgeblendet. In der nächsten Spalte will ich 2 Werte multiplizieren und kopiere die Formel runter. Er kopiert die Formel aber leider auch in die durch den Filter eigentlich ausgeblendeten Zeilen mit #NV. Wie kann ich das verhindern, dass er die Zeilen mit #NV, die er überspringen soll, trotzdem mit der kopierten Formel befüllt?

Danke

...zur Frage

Werte in Excel nach der größe sortieren?

Ich möchte, dass ich in Excel eine Liste von Zahlen eingeben kann, die dann nach der größe, in der Spalte daneben aufgelistet werden. Es gibt ja die Funktionen Max und Min, mit denen man den Maximum und den Minimum bestimmen kann. Gibt es auch etwas dazwischen, also das ich zum beispiel den drittgrößten wert aus der Liste bekomme?

...zur Frage

Was möchtest Du wissen?