Registriert seit: 02.08.2016
Version(en): 2013
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 :)
Registriert seit: 06.12.2015
Version(en): 2016
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
Registriert seit: 02.08.2016
Version(en): 2013
(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.
Registriert seit: 30.05.2016
Version(en): 2013
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 |
Gruß,
Luffy :100:
Personaltrainer - Ernährungsberater
Registriert seit: 13.04.2014
Version(en): 365, 2019
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 |
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 29.09.2015
Version(en): 2030,5
Code: =MAX(OFFSET($B1;;;4))
Registriert seit: 13.04.2014
Version(en): 365, 2019
21.11.2016, 14:42
(Dieser Beitrag wurde zuletzt bearbeitet: 21.11.2016, 14:42 von 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]
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 10.04.2014
Version(en): Office 2019
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 |
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
Registriert seit: 13.04.2014
Version(en): 365, 2019
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]
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 10.04.2014
Version(en): Office 2019
21.11.2016, 16:51
(Dieser Beitrag wurde zuletzt bearbeitet: 21.11.2016, 16:51 von 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 |
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
|