Top 5 Werte mit KGrößte und Bedingung

Forumthread: 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

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

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

	A	B	C	D	E
1	Kosten	Jahr	677	750	2008
2	100	2006	666	667
3	111	2006	533	333
4	122	2006	421	222
5	133	2006	410	211
6	144	2006
7	155	2006
8	166	2006
9	177	2007
10	188	2007
11	199	2007
12	210	2007
13	221	2007
14	232	2007
15	243	2007
16	667	2008
17	200	2008
18	211	2008
19	222	2008
20	750	2008
21	100	2008
22	333	2008
23	399	2009
24	410	2009
25	421	2009
26	666	2009
27	677	2009
28	50	2009
29	533	2009
30	300	2009

Formeln der Tabelle

Zelle	Formel
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

Zelle	Nr.: / Bedingung	Format
A2	1. / Formel ist =VERGLEICH(A2;$D$1:$D$5;0)	Abc
B2	1. / Zellwert ist gleich =$E$1	Abc
A3	1. / Formel ist =VERGLEICH(A3;$D$1:$D$5;0)	Abc
B3	1. / Zellwert ist gleich =$E$1	Abc
A4	1. / Formel ist =VERGLEICH(A4;$D$1:$D$5;0)	Abc
B4	1. / Zellwert ist gleich =$E$1	Abc
A5	1. / Formel ist =VERGLEICH(A5;$D$1:$D$5;0)	Abc
B5	1. / Zellwert ist gleich =$E$1	Abc
A6	1. / Formel ist =VERGLEICH(A6;$D$1:$D$5;0)	Abc
B6	1. / Zellwert ist gleich =$E$1	Abc
A7	1. / Formel ist =VERGLEICH(A7;$D$1:$D$5;0)	Abc
B7	1. / Zellwert ist gleich =$E$1	Abc
A8	1. / Formel ist =VERGLEICH(A8;$D$1:$D$5;0)	Abc
B8	1. / Zellwert ist gleich =$E$1	Abc
A9	1. / Formel ist =VERGLEICH(A9;$D$1:$D$5;0)	Abc
B9	1. / Zellwert ist gleich =$E$1	Abc
A10	1. / Formel ist =VERGLEICH(A10;$D$1:$D$5;0)	Abc
B10	1. / Zellwert ist gleich =$E$1	Abc
A11	1. / Formel ist =VERGLEICH(A11;$D$1:$D$5;0)	Abc
B11	1. / Zellwert ist gleich =$E$1	Abc
A12	1. / Formel ist =VERGLEICH(A12;$D$1:$D$5;0)	Abc
B12	1. / Zellwert ist gleich =$E$1	Abc
A13	1. / Formel ist =VERGLEICH(A13;$D$1:$D$5;0)	Abc
B13	1. / Zellwert ist gleich =$E$1	Abc
A14	1. / Formel ist =VERGLEICH(A14;$D$1:$D$5;0)	Abc
B14	1. / Zellwert ist gleich =$E$1	Abc
A15	1. / Formel ist =VERGLEICH(A15;$D$1:$D$5;0)	Abc
B15	1. / Zellwert ist gleich =$E$1	Abc
A16	1. / Formel ist =VERGLEICH(A16;$D$1:$D$5;0)	Abc
B16	1. / Zellwert ist gleich =$E$1	Abc
A17	1. / Formel ist =VERGLEICH(A17;$D$1:$D$5;0)	Abc
B17	1. / Zellwert ist gleich =$E$1	Abc
A18	1. / Formel ist =VERGLEICH(A18;$D$1:$D$5;0)	Abc
B18	1. / Zellwert ist gleich =$E$1	Abc
A19	1. / Formel ist =VERGLEICH(A19;$D$1:$D$5;0)	Abc
B19	1. / Zellwert ist gleich =$E$1	Abc
A20	1. / Formel ist =VERGLEICH(A20;$D$1:$D$5;0)	Abc
B20	1. / Zellwert ist gleich =$E$1	Abc
A21	1. / Formel ist =VERGLEICH(A21;$D$1:$D$5;0)	Abc
B21	1. / Zellwert ist gleich =$E$1	Abc
A22	1. / Formel ist =VERGLEICH(A22;$D$1:$D$5;0)	Abc
B22	1. / Zellwert ist gleich =$E$1	Abc
A23	1. / Formel ist =VERGLEICH(A23;$D$1:$D$5;0)	Abc
B23	1. / Zellwert ist gleich =$E$1	Abc
A24	1. / Formel ist =VERGLEICH(A24;$D$1:$D$5;0)	Abc
B24	1. / Zellwert ist gleich =$E$1	Abc
A25	1. / Formel ist =VERGLEICH(A25;$D$1:$D$5;0)	Abc
B25	1. / Zellwert ist gleich =$E$1	Abc
A26	1. / Formel ist =VERGLEICH(A26;$D$1:$D$5;0)	Abc
B26	1. / Zellwert ist gleich =$E$1	Abc
A27	1. / Formel ist =VERGLEICH(A27;$D$1:$D$5;0)	Abc
B27	1. / Zellwert ist gleich =$E$1	Abc
A28	1. / Formel ist =VERGLEICH(A28;$D$1:$D$5;0)	Abc
B28	1. / Zellwert ist gleich =$E$1	Abc
A29	1. / Formel ist =VERGLEICH(A29;$D$1:$D$5;0)	Abc
B29	1. / Zellwert ist gleich =$E$1	Abc
A30	1. / Formel ist =VERGLEICH(A30;$D$1:$D$5;0)	Abc
B30	1. / Zellwert ist gleich =$E$1	Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

{=KGRÖSSTE((B1:B99=2009)*A1:A99;ZEILE(A1))}

26.01.2010 13:40:26

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

{=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

Infobox / Tutorial

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:

Daten vorbereiten: Stelle sicher, dass deine Daten in zwei Spalten organisiert sind, z.B. in Spalte A (Kosten) und Spalte B (Jahr).
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.
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)
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

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

Top 5 Werte mit KGrößte und Bedingung

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

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Top 5 Werte mit KGrößte und Bedingung

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Top 5 Werte mit KGrößte und Bedingung

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

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

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Top 5 Werte mit KGrößte und Bedingung

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread