Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Top 5 Werte mit KGrößte und Bedingung

Top 5 Werte mit KGrößte und Bedingung
chrissey
Hallo Leut,
kriegs leider nicht hin und bin auf Hilfe angewiesen.
Habe in der Spalte A Kosten. In der Spalte B steht das Jahr in dem die Kosten angefallen sind ( 2006 - 2010 ).
Jetz möchte ich gerne in Zelle C1 - C5 eine Formel oder fünf ;-) Die mir in C1 den größten Wert
in C2 den zweitgrößten Wert, usw bis 5 eben suchen, aber unter der Nebenbedingung dass
in der Spalte B 2009 steht.
Hoffe das ist verständlich erklärt und jmd kann mir helfen
Danke Euch schon mal und liebe Grüße
Chrissey
Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Top 5 Werte mit KGrößte und Bedingung
26.01.2010 13:38:59
jockel
hi Chrissey, meinst du so? einmal mit starr 2009 in der formel und einmal flexibel mit auswahl des jahres in E1..:
Tabelle2

 ABCDE
1KostenJahr6777502008
21002006666667 
31112006533333 
41222006421222 
51332006410211 
61442006   
71552006   
81662006   
91772007   
101882007   
111992007   
122102007   
132212007   
142322007   
152432007   
166672008   
172002008   
182112008   
192222008   
207502008   
211002008   
223332008   
233992009   
244102009   
254212009   
266662009   
276772009   
28502009   
295332009   
303002009   

Formeln der Tabelle
ZelleFormel
C1{=KGRÖSSTE(WENN($B$2:$B$30=2009;$A$2:$A$30;); ZEILE(A1))}
D1{=KGRÖSSTE(WENN($B$2:$B$30=$E$1;$A$2:$A$30;); ZEILE(A1))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
A21. / Formel ist =VERGLEICH(A2;$D$1:$D$5;0)Abc
B21. / Zellwert ist gleich =$E$1Abc
A31. / Formel ist =VERGLEICH(A3;$D$1:$D$5;0)Abc
B31. / Zellwert ist gleich =$E$1Abc
A41. / Formel ist =VERGLEICH(A4;$D$1:$D$5;0)Abc
B41. / Zellwert ist gleich =$E$1Abc
A51. / Formel ist =VERGLEICH(A5;$D$1:$D$5;0)Abc
B51. / Zellwert ist gleich =$E$1Abc
A61. / Formel ist =VERGLEICH(A6;$D$1:$D$5;0)Abc
B61. / Zellwert ist gleich =$E$1Abc
A71. / Formel ist =VERGLEICH(A7;$D$1:$D$5;0)Abc
B71. / Zellwert ist gleich =$E$1Abc
A81. / Formel ist =VERGLEICH(A8;$D$1:$D$5;0)Abc
B81. / Zellwert ist gleich =$E$1Abc
A91. / Formel ist =VERGLEICH(A9;$D$1:$D$5;0)Abc
B91. / Zellwert ist gleich =$E$1Abc
A101. / Formel ist =VERGLEICH(A10;$D$1:$D$5;0)Abc
B101. / Zellwert ist gleich =$E$1Abc
A111. / Formel ist =VERGLEICH(A11;$D$1:$D$5;0)Abc
B111. / Zellwert ist gleich =$E$1Abc
A121. / Formel ist =VERGLEICH(A12;$D$1:$D$5;0)Abc
B121. / Zellwert ist gleich =$E$1Abc
A131. / Formel ist =VERGLEICH(A13;$D$1:$D$5;0)Abc
B131. / Zellwert ist gleich =$E$1Abc
A141. / Formel ist =VERGLEICH(A14;$D$1:$D$5;0)Abc
B141. / Zellwert ist gleich =$E$1Abc
A151. / Formel ist =VERGLEICH(A15;$D$1:$D$5;0)Abc
B151. / Zellwert ist gleich =$E$1Abc
A161. / Formel ist =VERGLEICH(A16;$D$1:$D$5;0)Abc
B161. / Zellwert ist gleich =$E$1Abc
A171. / Formel ist =VERGLEICH(A17;$D$1:$D$5;0)Abc
B171. / Zellwert ist gleich =$E$1Abc
A181. / Formel ist =VERGLEICH(A18;$D$1:$D$5;0)Abc
B181. / Zellwert ist gleich =$E$1Abc
A191. / Formel ist =VERGLEICH(A19;$D$1:$D$5;0)Abc
B191. / Zellwert ist gleich =$E$1Abc
A201. / Formel ist =VERGLEICH(A20;$D$1:$D$5;0)Abc
B201. / Zellwert ist gleich =$E$1Abc
A211. / Formel ist =VERGLEICH(A21;$D$1:$D$5;0)Abc
B211. / Zellwert ist gleich =$E$1Abc
A221. / Formel ist =VERGLEICH(A22;$D$1:$D$5;0)Abc
B221. / Zellwert ist gleich =$E$1Abc
A231. / Formel ist =VERGLEICH(A23;$D$1:$D$5;0)Abc
B231. / Zellwert ist gleich =$E$1Abc
A241. / Formel ist =VERGLEICH(A24;$D$1:$D$5;0)Abc
B241. / Zellwert ist gleich =$E$1Abc
A251. / Formel ist =VERGLEICH(A25;$D$1:$D$5;0)Abc
B251. / Zellwert ist gleich =$E$1Abc
A261. / Formel ist =VERGLEICH(A26;$D$1:$D$5;0)Abc
B261. / Zellwert ist gleich =$E$1Abc
A271. / Formel ist =VERGLEICH(A27;$D$1:$D$5;0)Abc
B271. / Zellwert ist gleich =$E$1Abc
A281. / Formel ist =VERGLEICH(A28;$D$1:$D$5;0)Abc
B281. / Zellwert ist gleich =$E$1Abc
A291. / Formel ist =VERGLEICH(A29;$D$1:$D$5;0)Abc
B291. / Zellwert ist gleich =$E$1Abc
A301. / Formel ist =VERGLEICH(A30;$D$1:$D$5;0)Abc
B301. / Zellwert ist gleich =$E$1Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Anzeige
{=KGRÖSSTE((B1:B99=2009)*A1:A99;ZEILE(A1))}
26.01.2010 13:40:26
WF
und runterkopieren
ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluß der Formel mit gleichzeitig Strg / Shift / Enter (statt Enter allein); - das erzeugt sie.
Salut WF
hatte die Dollars vergessen
26.01.2010 13:44:58
WF
{=KGRÖSSTE((B$1:B$99=2009)*A$1:A$99;ZEILE(A1))}
AW: hatte die Dollars vergessen
26.01.2010 14:03:15
chrissey
Danke funktioniert super!!! Ihr seid die Besten,...
Grüße
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Top 5 Werte mit KGrößte und Bedingung


Schritt-für-Schritt-Anleitung

Um die Top 5 Werte mit Bedingung in Excel zu ermitteln, kannst du die Funktion KGRÖSSTE verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

  1. Daten vorbereiten: Stelle sicher, dass deine Daten in zwei Spalten organisiert sind, z.B. in Spalte A (Kosten) und Spalte B (Jahr).

  2. Formel für den größten Wert: In Zelle C1 kannst du folgende Formel verwenden, um den größten Wert für das Jahr 2009 zu ermitteln:

    {=KGRÖSSTE(WENN($B$2:$B$30=2009;$A$2:$A$30);1)}

    Um diese Formel einzugeben, musst du sie mit STRG + SHIFT + ENTER bestätigen, damit sie als Matrixformel funktioniert.

  3. Weitere Werte ermitteln: Du kannst die Formel in C2 bis C5 kopieren und den Parameter 1 in der Formel entsprechend anpassen (2 für den zweitgrößten Wert, 3 für den drittgrößten usw.):

    • C2: =KGRÖSSTE(WENN($B$2:$B$30=2009;$A$2:$A$30);2)
    • C3: =KGRÖSSTE(WENN($B$2:$B$30=2009;$A$2:$A$30);3)
    • C4: =KGRÖSSTE(WENN($B$2:$B$30=2009;$A$2:$A$30);4)
    • C5: =KGRÖSSTE(WENN($B$2:$B$30=2009;$A$2:$A$30);5)
  4. Alternative mit flexibel auswählbarem Jahr: Wenn du das Jahr in Zelle E1 flexibel ändern möchtest, kannst du die Formel in D1 wie folgt anpassen:

    {=KGRÖSSTE(WENN($B$2:$B$30=$E$1;$A$2:$A$30);1)}

    Auch hier wieder mit STRG + SHIFT + ENTER bestätigen.


Häufige Fehler und Lösungen

  • Fehler bei der Eingabe der Matrixformel: Stelle sicher, dass du die Formel mit STRG + SHIFT + ENTER abschließt. Wenn du nur ENTER drückst, wird die Formel nicht korrekt ausgeführt.

  • Werte werden nicht angezeigt: Überprüfe, ob die Bedingungen in der WENN-Funktion korrekt sind. Achte darauf, dass die Bereiche (z.B. $B$2:$B$30) richtig definiert sind.

  • Falsche Werte zurückgegeben: Wenn du die Formel kopierst, achte darauf, dass sich die Zeilen- und Spaltenreferenzen entsprechend anpassen.


Alternative Methoden

  1. KGRÖSSTE mit 2 Bedingungen: Wenn du mehrere Bedingungen einbeziehen möchtest, kannst du die Formel so anpassen:

    {=KGRÖSSTE(WENN(($B$2:$B$30=2009)*($C$2:$C$30="Bedingung2");$A$2:$A$30);1)}

    Hierbei multiplizierst du die Bedingungen.

  2. Verwendung von AGGREGAT: Eine weitere Möglichkeit ist die AGGREGAT-Funktion, die auch Fehler ignorieren kann:

    =AGGREGAT(14;6;$A$2:$A$30/(($B$2:$B$30=2009));1)

Praktische Beispiele

Angenommen, du hast folgende Daten:

Kosten Jahr
677 2009
750 2008
100 2006
666 2009
410 2009

Mit der oben beschriebenen Methode kannst du in den Zellen C1 bis C5 die folgenden Werte für das Jahr 2009 ermitteln:

  • C1: 677
  • C2: 666
  • C3: 410
  • C4: 0 (wenn weniger als 4 Werte vorhanden sind)
  • C5: 0 (wenn weniger als 5 Werte vorhanden sind)

Tipps für Profis

  • Nutze Bedingte Formatierungen, um die Top-Werte visuell hervorzuheben.
  • Experimentiere mit der Funktion KGRÖSSTE in Kombination mit anderen Formeln, um komplexe Berechnungen durchzuführen.
  • Halte deine Daten sauber und gut strukturiert, um die Genauigkeit der Ergebnisse zu gewährleisten.

FAQ: Häufige Fragen

1. Wie kann ich die Formel anpassen, wenn ich Daten aus einer anderen Tabelle verwenden möchte? Du kannst den Bereich in der Formel einfach anpassen, z.B. Tabelle2!$A$2:$A$30, um auf eine andere Tabelle zuzugreifen.

2. Was mache ich, wenn ich mehr als 5 Werte benötige? Erweitere die Formeln in den Zellen C1 bis C5 entsprechend, indem du die Zeilennummer in der KGRÖSSTE-Funktion anpasst.

3. Gibt es eine Möglichkeit, die Top-Werte in einer Dropdown-Liste anzuzeigen? Ja, du kannst die Top-Werte in eine Dropdown-Liste integrieren, indem du die Datenüberprüfung von Excel verwendest.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige