Office: Zellen mit 50% der größten und kleinsten Werte ermitteln

Hi nochmal,

eine Frage hätte ich noch, ausgehend von dieser Tabellenstruktur, erste Zeile enthält die Monate Jan - Dezember (1 ... 12) und die zweite Zeile den dzaugehörigen Wert der Monate.

Die letzten beiden Spalten geben die Summe über alle 12 Monate wieder sowie den Durchschnittswert über 12 Monate.

Month 1 2 3 4 5 6 7 8 9 10 11 12 all-Months all-Monts avarage
Value 5 2 3 7 8 1 4 6 5 3 9 6 59 5

Ich würde nun gerne mir in 6 Zellen die 6 Monate mit den jeweils größten Werten anzeigen lassen und in 6 weiteren Zellen, die 6 Monate die jeweils geringsten werten.

Es kann durchaus sein, dass es nicht 6 Monate gibt die über dem durchschnitt sind, dann sollen trotzdem die 6 größten genommen werden. Bei gleichstand 'gewinnt' der frühere Monat (also zb Januar vor März)


In dem Beispiel wären also die Monate mit den 6 größten Werten:

1, 4, 5, 8, 11, 12

die anderen 6 monate wären entsprechend die mit den kleinsten werten.

denke das ganze müsste irgendwie mit RANG() zu lösen sein, oder?

vg, chess

 

Oder mit KKLEINSTE und KGRÖSSTE in Verbindung mit INDEX und VERGLEICH.
Falls ein Wert auch mehrfach auftauchen kann, müsste man noch eine entsprechende Weiche einbauen.

 

danke, ich habe es jetzt hinbekommen, dass ich folgendes zwischergebnis habe:

Month 1 2 3 4 5 6 7 8 9 10 11 12

Status L L H H H H H H L H H L

H (steht jeweils in Zeile 2) bedeutet, dass es in diesem Monat über dem Durchschnitt war, und L dass es relativ klein war.

Nun würde ich gerne in eine Zelle alle Monats-Nummer (die in Zeile 1 stehen) mit einem H anzeigen lassen, bzw alle die ein L haben, am besten mit Punkt getrennt.

In diesem Fall sollte der Output für die H-Zelle sein:

3.4.5.6.7.8.10.11

Und in der L-Zelle sollte stehen

1.2.9.12

Könnte man das mit einer einzigen Formel realisieren?

 

hi, welche XL-version benutzt du? und was ist mit 10 und 11..?

 

sorry, hab 10 und 11 ergänzt.

ich bentuze excel 2007

 

Hallo!

Grundregel bei Excel: Immer nur EINEN Wert pro Zelle! Alles andere ist Murks!

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8	9	10	11	12
2		L	L	H	H	H	H	H	H	L	H	H	L
3
4
5
6	High	3	4	5	6	7	8	10	11
7	Low	1	2	9	12

Zelle	Formel
B6	{=WENN(ZÄHLENWENN($B2:$M2;"H")<SPALTE(A1);"";INDEX($B1:$M1;KKLEINSTE(WENN($B2:$M2="H";SPALTE($A:$L));SPALTE(A1))))}
B7	{=WENN(ZÄHLENWENN($B2:$M2;"L")<SPALTE(A1);"";INDEX($B1:$M1;KKLEINSTE(WENN($B2:$M2="L";SPALTE($A:$L));SPALTE(A1))))}

Achtung, Matrixformel enthalten!Die geschweiften Klammern{} werden nicht eingegeben.Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

 

hi, welche XL-version benutzt du? und was ist mit 10 und 11..?

 

hi, ich würde so tun..:

Arbeitsblatt mit dem Namen 'Tabelle1 (2)'
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	1	2	3	4	5	6	7	8	9	10	11	12
2	Status	L	L	H	H	H	H	H	H	L	H	H	L
3
4
5		3	4	5	6	7	8	10	11					3.4.5.6.7.8.10.11
6		1	2	9	12									1.2.9.12

Zelle	Formel
B5	{=WENNFEHLER(KKLEINSTE(WENN($B$2:$M$2="H";$B$1:$M$1);SPALTE(A1));"")}
B6	{=WENNFEHLER(KKLEINSTE(WENN($B$2:$M$2="l";$B$1:$M$1);SPALTE(A2));"")}

Achtung, Matrixformel enthalten!Die geschweiften Klammern{} werden nicht eingegeben.Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

und dann..:

Arbeitsblatt mit dem Namen 'Tabelle1 (2)'
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	1	2	3	4	5	6	7	8	9	10	11	12
2	Status	L	L	H	H	H	H	H	H	L	H	H	L
3
4
5		3	4	5	6	7	8	10	11					3.4.5.6.7.8.10.11
6		1	2	9	12									1.2.9.12

Zelle	Formel
N5	=WECHSELN(GLÄTTEN(B5&" "&C5&" "&D5&" "&E5&" "&F5&" "&G5&" "&H5&" "&I5&" "&J5);" ";".")
N6	=WECHSELN(GLÄTTEN(B6&" "&C6&" "&D6&" "&E6&" "&F6&" "&G6&" "&H6&" "&I6&" "&J6);" ";".")

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

 

vielen dank!
probiere es gleich aus.

gebe dir recht mit dem murks in einer zelle. hintergrund ist aber mein anderer post mit dieser formel von schatzi:
{=SUMME((MONAT(A2:A999)={2.3.6.7.10.12})*B2:B999)}

da würde ich eben gerne mit einem indirekt-befehl auf eine zelle mit der 3.4.5.6.7.8.10.11 ... verweisen.
problem ist auch, dass man nicht genau weiß wieviele H bzw L sind.

 

hi das eine hat mit dem anderen nix zu tun und geht auch nicht...

 

hi, wenn du doch (d)eine (beispiel)datei posten würdest, wie auch schon geschrieben, das wird doch hier ein rätselraten

 

Hallo!

Für das, was du erreichen möchtest, brauchst du die Werte nicht in eine Zelle gezwängt!

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		1	2	3	4	5	6	7	8	9	10	11	12
2		L	L	H	H	H	H	H	H	L	H	H	L
3		101	102	103	104	105	106	107	108	109	110	111	112
4
5
6	High	3	4	5	6	7	8	10	11						836
7	Low	1	2	9	12										410

Zelle	Formel
B6	{=WENN(ZÄHLENWENN($B2:$M2;"H")<SPALTE(A1);"";INDEX($B1:$M1;KKLEINSTE(WENN($B2:$M2="H";SPALTE($A:$L));SPALTE(A1))))}
O6	{=SUMME((MTRANS(B1:M1)=B6:M6)*B3:M3)}
B7	{=WENN(ZÄHLENWENN($B2:$M2;"L")<SPALTE(A1);"";INDEX($B1:$M1;KKLEINSTE(WENN($B2:$M2="L";SPALTE($A:$L));SPALTE(A1))))}
O7	{=SUMME((MTRANS(B1:M1)=B7:M7)*B3:M3)}

Achtung, Matrixformel enthalten!Die geschweiften Klammern{} werden nicht eingegeben.Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

 

danke, aber so einfach ist es bei mir nicht, da die werte die ich später addieren will getrennt stehen und in einer zeitreihe dargestellt (spalte runterwärts) und nicht zeilenweise wie hier.

leider habe ich es aber nicht geschafft die formel von hiob bei mir umzusetzen.

daher im anhang eine beispiel datei:

Den Anhang beispiel-high-low.xlsx betrachten

in dem roten feld hab ich versucht die formel einzufügen, leider bleibt es hinterher leer.

 

Du musst die Formel natürlich auch noch nach rechts ziehen!

 

hi Schatzi, sollte hier nicht SUMMEWENN() reichen? und bist du sicher, dass deine ergbenisse stimmen..?

Arbeitsblatt mit dem Namen 'Tabelle1 (2)'
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Month	1	2	3	4	5	6	7	8	9	10	11	12
2	Status	L	L	H	H	H	H	H	H	L	H	H	L
3		101	102	103	104	105	106	107	108	109	110	111	112
4
5		3	4	5	6	7	8	10	11						836	854
6		1	2	9	12										410	424

Zelle	Formel
O5	{=SUMME((MTRANS(B1:M1)=B5:M5)*B3:M3)}
P5	=SUMMEWENN(B2:M2;"H";B3:M3)
O6	{=SUMME((MTRANS(B1:M1)=B6:M6)*B3:M3)}
P6	=SUMMEWENN(B2:M2;"L";B3:M3)

Achtung, Matrixformel enthalten!Die geschweiften Klammern{} werden nicht eingegeben.Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg