Clever-Excel-Forum

Clever-Excel-Forum > Office-Anwendungen > Excel > Maximum für bestimmten Bereich
Normale Version: Maximum für bestimmten Bereich
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2

Ben

21.11.2016, 11:00
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 Sleepy 

Vielen Dank im Vorraus :)

Fennek

21.11.2016, 11:17
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

21.11.2016, 12:50
(21.11.2016, 11:17)Fennek schrieb: [ -> ]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. Angel

Luffy

21.11.2016, 14:08
Hallo,

so?


Tabelle1
AB
1
200
300
423
533
6
700
856
916
1066
11
1200
1300

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

21.11.2016, 14:22
Hallo,

erste Zeile muß leer sein:

Arbeitsblatt mit dem Namen 'Tabelle2'
 AB
200
300
423
533
6  
700
856
916
1066
11  
1200
1300

ZelleFormel
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

21.11.2016, 14:24
Code:
=MAX(OFFSET($B1;;;4))

BoskoBiati

21.11.2016, 14:42
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'
 AB
200
388
466
533
6  
788
800
916
1066
11  
1200
1300

ZelleFormel
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'
 AB
100
288
368
438
5  
677
700
817
967
10  
1100
1200

ZelleFormel
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

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

Arbeitsblatt mit dem Namen 'Blatt2'
 ABC
2000
3888
4666
5333
6   
7888
8000
9166
10666
11   
12000
13000

ZelleFormel
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

21.11.2016, 15:42
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'
 ABCDEFG
1 SollVierer-BlockEdgarLuffyJörg_2Jörg_1
20000000
30000000
42333333
53333333
6       
70000000
85666666
91666666
106666666
11       
120000000
134444444
142444333
153444333
16       
170000000
187777777
191777666
206777666
21       
220000000
230000000

ZelleFormel
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

21.11.2016, 16:51
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'
 ABCDEF
1 SollVierer-BlockEdgarJockelEdgar_modifiziert
2000000
3000000
4233333
5333333
6      
7000000
8566666
9166666
10666666
11      
12000000
13444444
14244444
15344444
16      
17000000
18777777
19177777
20677777
21      
22000000
23000000

ZelleFormel
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
Seiten: 1 2
Clever-Excel-Forum > Office-Anwendungen > Excel > Maximum für bestimmten Bereich