Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Maximum für bestimmten Bereich
#1
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 :)
Antworten Top
#2
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
Antworten Top
#3
(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
Antworten Top
#4
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

Gruß,

Luffy  :100:

Personaltrainer - Ernährungsberater 
Antworten Top
#5
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
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#6
Code:
=MAX(OFFSET($B1;;;4))
Antworten Top
#7
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]
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#8
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
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Antworten Top
#9
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]
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#10
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
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste