Maximum für bestimmten Bereich

[Ben]

Moin Moin Zusammen,

in der Hoffnung, dass das Thema noch nicht zu Tode gethreated wurde, eine Frage zur MAX()-Funktion.

Folgendes Problem:

- Ich habe eine große Datenmenge zum Filtern

- Eine der Spalten (A) beinhaltet fortlaufend Werte (ungeordnet), dann Nullen, dann wieder Werte, dann wieder Nullen, usw.:

A________B________

0              0
0              0
2              3
3              3

0              0
5              6
1              6
6              6

0              0
0              0


- In der Spalte B (s.o.) hätte ich gern jeweils die Maxima für die Bereiche die ungleich Null sind, die ich jetzt wie oben zu sehen manuell eingetragen habe

- Ich habe mich schon mit einigen MAX()-array-Formeln probiert, aber leider kein ordentliches Ergebnis bekommen  

Vielen Dank im Vorraus :)

[Fennek]

Hallo,

nur eine Verständnisfrage:

Soll für jeden zusammenhängenden Block mit Werten <>0 in Spalte A das Max(Spalte B) ermittelt werden? Also im Beispiel wäre das 3 und 6.

Falls das so sein sollte, wäre ein möglicher Ansatz:

- alle 0 durch "A" zuersetzen
- für Spalte A eine Auswahl mit "with columns(1).specialcells(2,1).areas" und dann für jedes Area das "Max" zu ermitteln


mfg

[Ben]

Hallo,

nur eine Verständnisfrage:

Soll für jeden zusammenhängenden Block mit Werten <>0 in Spalte A das Max(Spalte B) ermittelt werden? Also im Beispiel wäre das 3 und 6.

Falls das so sein sollte, wäre ein möglicher Ansatz:

- alle 0 durch "A" zuersetzen
- für Spalte A eine Auswahl mit "with columns(1).specialcells(2,1).areas" und dann für jedes Area das "Max" zu ermitteln


mfg

Hi Fennek,

danke schon mal für die Anregung. Aus formattechnischen Gründen müssen die 0-Zeilen auch Null bleiben. Ansonsten verstehe ich deinen Ansatz. Hilft mir leider so nicht.

[Luffy]

Hallo,

so?


Tabelle1

	A	B
1
2	0	0
3	0	0
4	2	3
5	3	3
6
7	0	0
8	5	6
9	1	6
10	6	6
11
12	0	0
13	0	0

verwendete Formeln
Zelle	Formel	Bereich	N/A
B1:B13	{=WENN(A1="";"";WENN((A1>0);MAX(A1:INDEX($A2:$A$14;VERGLEICH(WAHR;$A2:$A$14="";0)));0))}	$B$1

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.15 einschl. 64 Bit

[BoskoBiati]

Hallo,

erste Zeile muß leer sein:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
2	0	0
3	0	0
4	2	3
5	3	3
6
7	0	0
8	5	6
9	1	6
10	6	6
11
12	0	0
13	0	0

Zelle	Formel
B2	{=WENN(A2="";"";WENN(A2=0;0;MAX(INDEX($A$1:A2;VERWEIS(9;1/($A$1:A2="");ZEILE($A$1:A2))):INDEX(A:A;ZEILE(A2)+VERGLEICH(WAHR;($A2:$A$100="");0)-1))))}

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

[snb]

Code:

=MAX(OFFSET($B1;;;4))

[BoskoBiati]

Hallo snb,

1. ist offset volatil, also nicht so geschickt,
2. funktioniert das nicht, wenn die Blöcke nicht gleich lang sind!

@Luffy,

da musst Du aber noch nacharbeiten:

[html]

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
2	0	0
3	8	8
4	6	6
5	3	3
6
7	8	8
8	0	0
9	1	6
10	6	6
11
12	0	0
13	0	0

Zelle	Formel
B2	{=WENN(A2="";"";WENN((A2>0);MAX(A2:INDEX($A3:$A$14;VERGLEICH(WAHR;$A3:$A$14="";0)));0))}

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

[/html]


Bei reinen Viererblöcken ginge das:

[html]

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
1	0	0
2	8	8
3	6	8
4	3	8
5
6	7	7
7	0	0
8	1	7
9	6	7
10
11	0	0
12	0	0

Zelle	Formel
B1	=WENN(A1="";"";WENN(A1=0;0;MAX(INDEX(A:A;KÜRZEN((ZEILE()-1)/5;)*5+1):INDEX(A:A;KÜRZEN((ZEILE()-1)/5;)*5+4))))

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

[/html]

[Jockel]

Hallo, ich würde so wie in C tun..:

Arbeitsblatt mit dem Namen 'Blatt2'
	A	B	C
2	0	0	0
3	8	8	8
4	6	6	6
5	3	3	3
6
7	8	8	8
8	0	0	0
9	1	6	6
10	6	6	6
11
12	0	0	0
13	0	0	0

Zelle	Formel
B2	{=WENN(A2="";"";WENN((A2>0);MAX(A2:INDEX($A3:$A$14;VERGLEICH(WAHR;$A3:$A$14="";0)));0))}
C2	=WENN(A2="";"";(A2<>0)*MAX(A2:INDEX(A:A;AGGREGAT(15;6;ZEILE(A2:A$100)/(A2:A$100="");1))))

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

[BoskoBiati]

Hallo Jörg,

gleicher Fehler wie bei Luffy! das Maximum eines jeden Blocks in allen Zellen, in denen in A eine Zahl steht!

[html]

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F	G
1		Soll	Vierer-Block	Edgar	Luffy	Jörg_2	Jörg_1
2	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0
4	2	3	3	3	3	3	3
5	3	3	3	3	3	3	3
6
7	0	0	0	0	0	0	0
8	5	6	6	6	6	6	6
9	1	6	6	6	6	6	6
10	6	6	6	6	6	6	6
11
12	0	0	0	0	0	0	0
13	4	4	4	4	4	4	4
14	2	4	4	4	3	3	3
15	3	4	4	4	3	3	3
16
17	0	0	0	0	0	0	0
18	7	7	7	7	7	7	7
19	1	7	7	7	6	6	6
20	6	7	7	7	6	6	6
21
22	0	0	0	0	0	0	0
23	0	0	0	0	0	0	0

Zelle	Formel
C2	=WENN(A2="";"";WENN(A2=0;0;MAX(INDEX(A:A;KÜRZEN((ZEILE()-1)/5;)*5+2):INDEX(A:A;KÜRZEN((ZEILE()-1)/5;)*5+5))))
D2	{=WENN(A2="";"";WENN(A2=0;0;MAX(INDEX($A$1:A2;VERWEIS(9;1/($A$1:A2="");ZEILE($A$1:A2))):INDEX(A:A;ZEILE(A2)+VERGLEICH(WAHR;($A2:$A$100="");0)-1))))}
E2	{=WENN(A2="";"";WENN((A2>0);MAX(A2:INDEX($A3:$A$140;VERGLEICH(WAHR;$A3:$A$140="";0)));0))}
F2	=WENN(A2="";"";(A2<>0)*MAX(A2:INDEX(A:A;AGGREGAT(15;6;ZEILE(A2:A$100)/(A2:A$100="");1))))
G2	{=WENN(A2="";"";WENN((A2>0);MAX(A2:INDEX($A3:$A$140;VERGLEICH(WAHR;$A3:$A$140="";0)));0))}

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

[/html]

[Jockel]

Hi Edgar, du musst ja nicht gleich schimpfen... ... ich mag es aber matrixformelfrei... oder politsch korrekt: ohne Abschluß mit Strg+Umsch+Enter...:

Arbeitsblatt mit dem Namen 'Blatt32'
	A	B	C	D	E	F
1		Soll	Vierer-Block	Edgar	Jockel	Edgar_modifiziert
2	0	0	0	0	0	0
3	0	0	0	0	0	0
4	2	3	3	3	3	3
5	3	3	3	3	3	3
6
7	0	0	0	0	0	0
8	5	6	6	6	6	6
9	1	6	6	6	6	6
10	6	6	6	6	6	6
11
12	0	0	0	0	0	0
13	4	4	4	4	4	4
14	2	4	4	4	4	4
15	3	4	4	4	4	4
16
17	0	0	0	0	0	0
18	7	7	7	7	7	7
19	1	7	7	7	7	7
20	6	7	7	7	7	7
21
22	0	0	0	0	0	0
23	0	0	0	0	0	0

Zelle	Formel
C2	=WENN(A2="";"";WENN(A2=0;0;MAX(INDEX(A:A;KÜRZEN((ZEILE()-1)/5;)*5+2):INDEX(A:A;KÜRZEN((ZEILE()-1)/5;)*5+5))))
D2	{=WENN(A2="";"";WENN(A2=0;0;MAX(INDEX($A$1:A2;VERWEIS(9;1/($A$1:A2="");ZEILE($A$1:A2))):INDEX(A:A;ZEILE(A2)+VERGLEICH(WAHR;($A2:$A$100="");0)-1))))}
E2	=WENN(A2="";"";(A2<>0)*AGGREGAT(14;6;INDEX($A$1:A2;VERWEIS(9;1/($A$1:A2="");ZEILE($A$1:A2))):INDEX(A:A;AGGREGAT(15;6;ZEILE(A1:A$99)/(A2:A$100="");1));1))
F2	=WENN(A2="";"";(A2<>0)*MAX(INDEX($A$1:A2;VERWEIS(9;1/($A$1:A2="");ZEILE($A$1:A2))):INDEX(A:A;AGGREGAT(15;6;ZEILE(A1:A$99)/(A2:A$100="");1))))

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