Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1668to1672
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Min/Max mit Kriterium auf Bereich begrenzen

Min/Max mit Kriterium auf Bereich begrenzen
15.01.2019 18:29:16
Klaus
Liebe Herbers,
die Spalte B ist formatiert als tt.mm.jjjj; in D stehen Zahlenwerte. Mit =MIN(WENN(MONAT(B6:B481)=9;D6:D481))und Shift/Ctrl/Enter fische ich alle Monate ( 9 = Sept ) heraus, so hier also das Minimum für den September. Wie müßte die Formel aber heißen, wenn ich statt B6 immer eine fixe Start-Zelle $B$6 und statt D481 nun $D und hole die Zeile aus AF4, wo "=ANZAHL2(A:A)" steht ? Will sagen, die Tabelle startet immer bei B6 - aber auch, und das ist das Problem, befinden sich oberhalb davon auch noch Datumseinträge. Eine Lösung, wie {=MAX(WENN(B:B="BLAU";C:C))} funktioniert somit nicht. Die Tiefe = letzte Zeile ist dynamisch. Das Gleiche sollte auch für MAX funktionieren.
Danke, Klaus

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ist möglich ....
15.01.2019 18:41:23
neopa
Hallo Klaus,
... dazu kann man INDEX() einsetzen. Doch eine Beispieldatei könnte hilfreich(er) sein.
Gruß Werner
.. , - ...
AW: ist möglich ....
15.01.2019 21:55:18
Klaus
Gerne Werner, ich versuche zunächst immer, alles ohne Datenanhänge bestmöglich erklären. Leider resultiert jedoch aus den verständlichen Restriktionen des Herber-Forums immer mehr Erklärungsbedarf, da ich schon bei der Begrenzung auf max 300 kB je Datei viele Dinge ausblenden muß, die ich dann wieder verdeutlichen und in ggf. in mehreren Nachträgen zu Eurem Verständnis mit Daten füllen muß.
Sorry Herbers, es steht zu befürchten. daß sich mit einem 1-MB-Anhang zur Darstellung meines Gesamt-Problems oder einen Verweis auf eine zip in der Cloud, sich kein Mitglied auch nur eine Sekunde mit meiner Frage beschäfigt, isn't it ? Also Werner hier das erste von mehreren Tabellenblättern leider mit diversen ### aus Blatt2. https://www.herber.de/bbs/user/126790.xlsm
Klaus
Anzeige
AW: damit hab ich nicht gerechnet ...
16.01.2019 08:06:02
neopa
Hallo Klaus,
... dass Du für Dein vermeintlich einfache Eingangsfragestellung eine derartig große (wie Du schreibst) Beispieldatei einstellst. Zunächst, da ich mich mit VBA nicht wirklich beschäftige, lade ich aus diese und Sicherheitsgründen keine xlsm-Dateien aus dem Netz.
Kannst Du Dein Problem nicht schematisiert in einer Dateikopie auf ein paar Datensätze reduziert als XLSX-Datei noch einmal einstellen?
Gruß Werner
.. , - ...
AW: damit hab ich nicht gerechnet ...
16.01.2019 09:43:13
Luschi
Hallo Klaus,
hier mal mein Versuch. Damit auch neopa sich daran versuchen kann, habe ich die Makros rausgeschmissen und eine xlsx-Datei erzeugt.
Freue mich schon auf Werner's Verbesserungen!, da ich seine Abneigung gegen 'Bereich.Verschieben()' [volatile Funktion] kenne.
Beachte dabei bitte auch die definierten Namen https://www.herber.de/bbs/user/126801.xlsx
Gruß von Luschi
aus klein-Paris
Anzeige
AW: ich nutze da natürlich INDEX() ...
16.01.2019 10:46:45
neopa
Hallo Luschi, hallo Klaus,
... und die letzte Zeile hat ja bereits Klaus schon ermittelt. Ich verzichte auf auf die volatile Funktion INDIREKT() in P35 und den klassische Matrixformelabschluss für die MAX()-Werte. Wenn mit {}, entfällt dort dafür das äußere INDEX() und die Formel wird kürzer. Wie auch immer, für MAX() braucht es kein WENN().
Arbeitsblatt mit dem Namen 'Wetterdaten'
 JKLMNOP
35Monat9 bis  11
36 10.09.18 bis 30.09.18 : 01.10.18 bis 31.10.18 :  01.11.18 bis 08.11.18 :
37tmin1,7 2,1  -1,0
38tmax31,8 27,2  18,8
39tm_arith16,8 14,7  8,9
40tmExcl15,0Summme aller Temp/Anzahl Einträge !!!
41       
42Pmin1004,0 985,9  1005,3
43Pmax1040,2 1033,8  1030,8
44P_arith1022,1 1009,9  1018,1

NameBezug
Sp_B=INDEX(!$B$6:INDEX(!$B:$B;!$AF$4);)
Sp_D=INDEX(!$D$6:INDEX(!$D:$D;!$AF$4);)
Sp_E=INDEX(!$E$6:INDEX(!$E:$E;!$AF$4);)

ZelleFormel
K35=MONAT(B6)
P35=MONAT(INDEX(B:B;AF4))
K37{=MIN(WENN(MONAT(Sp_B)=K35;Sp_D))}
M37{=MIN(WENN(MONAT(Sp_B)=K35+1;Sp_D))}
P37{=MIN(WENN(MONAT(Sp_B)=K35+2;Sp_D))}
K38=MAX(INDEX((MONAT(Sp_B)=K35)*Sp_D;))
M38=MAX(INDEX((MONAT(Sp_B)=K35+1)*Sp_D;))
P38=MAX(INDEX((MONAT(Sp_B)=K35+2)*Sp_D;))
K42{=MIN(WENN(MONAT(Sp_B)=K35;Sp_E))}
M42{=MIN(WENN(MONAT(Sp_B)=K35+1;Sp_E))}
P42{=MIN(WENN(MONAT(Sp_B)=K35+2;Sp_E))}
K43=MAX(INDEX((MONAT(Sp_B)=K35)*Sp_E;))
M43=MAX(INDEX((MONAT(Sp_B)=K35+1)*Sp_E;))
P43=MAX(INDEX((MONAT(Sp_B)=K35+2)*Sp_E;))
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: ich nutze da natürlich INDEX() ...
16.01.2019 20:39:42
Luschi
Hallo Werner,
eigentlich erstaunlich, daß Niemand Nachfragen zu solchen Formeln in der Namensdefinition hat:
=INDEX(!$B$6:INDEX(!$B:$B;!$AF$4);)
Ich werde doch wohl nicht der Einzige sein, dem das unbekannt war?
Inzwischen weiß ich auch, wie man mit solchen Namen in Vba umgeht;
statt: Names("Sp_B").RefersToRange so: [Sp_B].Cells
obwohl M$ seit mindestens Office 2000 predigt, diese alte Excel4-Schreibweise nicht mehr zu benutzen, da total 'OUT'.
Wahrscheinlich ist es aber einfacher, neue Funktionalitäten zu integrieren, statt mal mit dem Uralt-Kram aufzuräumen. Wahrscheinlich gehört auch dieses o.g. Konstrukt zu den Altertümlichkeiten von Excel4, denn es funktioniert ja nur in definierten Namen und selbst der Namensmanager kann den so definierten Bereich nicht mit der Ameisenstrichlinien nachvollziehen; aber es hat schon was für sich!
Gruß von Luschi
aus klein-Paris
Anzeige
AW: ich nutze da natürlich INDEX() ...
16.01.2019 22:19:38
Klaus
Guten Abend Luschi und neopa C,
genau das ist das was mich so bekloppt macht ! Warum muß ich Namen vergeben, wenn ich Zeilen/Spalten oder Adressen eindeutig benennen kann ? Warum geht's mit MAX ohne, aber mit MIN nur mit geschweift ? Oder warum muß ich mit VBA ( also Kanonen auf Spatzen ) schießen, wenn ich alles doch auch so verständlich/durchschaubar/wartungsfreundlich/leicht änder- und anpassbar" zu Fuß" hinbekomme ?
Gut's nächtle, Klaus
AW: es geht auch ohne benannte Formeln ...
17.01.2019 06:57:02
neopa
Hallo Klaus,
... aber dazu melde ich mich am Nachmittag noch einmal.
Gruß Werner
.. , - ...
AW: nun wie versprochen ... und ...
17.01.2019 14:54:49
neopa
Hallo Klaus,
... nachstehend nun meine Formeln ohne benannte Formeln. Ich persönlich nutze auch gern banannte Formeln (mehr dazu sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=60 und ff), weil man mit deren Hilfe auch die komplexesten Formeln änderungsfreundlicher definieren kann.
In Foren setze ich diese normalerweise dagegen seltener ein, weil ich deren Einsatz und Strukturierung den User selbst überlasse. Hier hatte ich sie dagegen eingesetzt, weil Luschi seine Lösungsvorschlag so aufgezeigt hatte, und ich ihm so den Unterschied zu meiner Lösung vergleichbarer aufzeigen wollte.
Gleichzeitig hab ich nun die MAX()-Formeln auch wieder zu klassischen Matrixformeln gewandelt, weil es in Deiner Excelversion (2007) keine vertretbare Lösung gibt, die MIN()-Formeln ohne diese zu definieren. Somit hast Du beide Versionen kennengelernt.
In neueren Excel-Versionen ist es übrigens kein Problem mehr, alle derartige klassische Matrixformeln ohne den spez. Formelabschluss zu definieren. Es gibt nur noch seltene Ausnahmefälle, wo dieser noch erforderlich ist.
Arbeitsblatt mit dem Namen 'Wetterdaten'
 JKLMNOP
35Monat9 bis  11
36 10.09.18 bis 30.09.18 : 01.10.18 bis 31.10.18 :  01.11.18 bis 08.11.18 :
37tmin1,7 2,1  -1,0
38tmax31,8 31,8  18,8
39tm_arith16,8 17,0  8,9
40tmExcl15,0Summme aller Temp/Anzahl Einträge !!!
41       
42Pmin1004,0 985,9  1005,3
43Pmax1040,2 1033,8  1030,8
44P_arith1022,1 1009,9  1018,1

ZelleFormel
K35=MONAT(B6)
P35=MONAT(INDEX(B:B;AF4))
K37{=MIN(WENN(MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35;INDEX(D6:INDEX(D:D;AF4);)))}
M37{=MIN(WENN(MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35+1;INDEX(D6:INDEX(D:D;AF4);)))}
P37{=MIN(WENN(MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35+2;INDEX(D6:INDEX(D:D;AF4);)))}
K38{=MAX((MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35)*INDEX(D6:INDEX(D:D;AF4);))}
M38{=MAX((MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35)*INDEX(D6:INDEX(D:D;AF4);))}
P38{=MAX((MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35+2)*INDEX(D6:INDEX(D:D;AF4);))}
K42{=MIN(WENN(MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35;INDEX(E6:INDEX(E:E;AF4);)))}
M42{=MIN(WENN(MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35+1;INDEX(E6:INDEX(E:E;AF4);)))}
P42{=MIN(WENN(MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35+2;INDEX(E6:INDEX(E:E;AF4);)))}
K43{=MAX((MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35)*INDEX(E6:INDEX(E:E;AF4);))}
M43{=MAX((MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35+1)*INDEX(E6:INDEX(E:E;AF4);))}
P43{=MAX((MONAT(INDEX(B6:INDEX(B:B;AF4);))=K35+2)*INDEX(E6:INDEX(E:E;AF4);))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: nun wie versprochen ... und ...
17.01.2019 22:10:59
Luschi
Hallo Werner,
sehr schön, aber ich liebe die Unterschiede - und die Bereich.Verschieben()-Funktion, aber das kennst Du ja. In meiner Lösung ermittle ich einmal für Spalte 'B' den Datenbereich und verschiebe diesen dann entsprechend der gewünschten Spaltenzahl.
Sollte sich einmal die Errechnung des Bereiches durch Änderung der Ausgangsbedingungen ändern, dann brauche ich nur für Spalte 'B' die Formel im definierten Namen ändern und alles Andere ist dann Paletti. Du mußt dagegen etwas fleißiger sein; trotzdem gebe ich Dir recht wegen der Vermeidung von 'volatilen' Funktionen - aber wer im Ferrari sitzt, dem sind sie Normen für Abgaswerte/Weltuntergang auch egal; und mein i7-6*Kern-16 GB (DDR-5) gibt gerne Gas.
Aber solange viele Excel-Nutzer schon mit den Grundlagen Probleme haben und in den Excel-Foren Weiterbildung betrieben werden muß, sind doch unsere Auffassungen wirklich 'peanuts'.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: nun wie versprochen ... und ...
17.01.2019 22:10:59
Luschi
Hallo Werner,
sehr schön, aber ich liebe die Unterschiede - und die Bereich.Verschieben()-Funktion, aber das kennst Du ja. In meiner Lösung ermittle ich einmal für Spalte 'B' den Datenbereich und verschiebe diesen dann entsprechend der gewünschten Spaltenzahl.
Sollte sich einmal die Errechnung des Bereiches durch Änderung der Ausgangsbedingungen ändern, dann brauche ich nur für Spalte 'B' die Formel im definierten Namen ändern und alles Andere ist dann Paletti. Du mußt dagegen etwas fleißiger sein; trotzdem gebe ich Dir recht wegen der Vermeidung von 'volatilen' Funktionen - aber wer im Ferrari sitzt, dem sind sie Normen für Abgaswerte/Weltuntergang auch egal; und mein i7-6*Kern-16 GB (DDR-5) gibt gerne Gas.
Aber solange viele Excel-Nutzer schon mit den Grundlagen Probleme haben und in den Excel-Foren Weiterbildung betrieben werden muß, sind doch unsere Auffassungen wirklich 'peanuts'.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: hab keinen Ferrari, brauch auch keinen und ...
18.01.2019 08:54:14
neopa
Hallo Luschi,
... bin auch kein Fan davon. Dies alles real und im übertragen Sinn.
Ich werde weiterhin zum Bäcker um die Ecke gehen, wenn ich meine Brötchen holen möchte. Früher hat man da übrigens sich noch beim warten über Gott und die Welt verständigt - war da schon so so etwas wie der Vorläufer von "Social Media".
Gruß Werner
.. , - ...
AW: hab keinen Ferrari, brauch auch keinen und ...
18.01.2019 13:30:12
Klaus
Danke @neopa C, Luschi und Werner
das gefällt mir, andererseits : die Namensgebung macht auch Sinn, nur leider schwerfällig bei der Eingabe. By the way : ich - alter Mann - mag Leute, die zu Fuß einkaufen und habe selbst noch nicht ein Mal einen Führerschein. Hier jetzt bitte nicht falsch abbiegen : nicht etwa keinen mehr, sondern nie einen gehabt !
In diesem Sinne, Klaus
Anzeige
AW: offensichtlich hast Du übersehen ...
16.01.2019 12:04:52
neopa
Hallo Klaus,
.... das Luschi bereits seinen Lösungsvorschlag als xlsx-Datei eingestellt hatte und ich darauf hin meinen alternativen Lösungsvorschlag, so dass diese Fragmentdatei nicht mehr notwendig war und ist.
Gruß Werner
.. , - ...
AW: offensichtlich hast Du übersehen ...
16.01.2019 15:45:20
Klaus
Hi Werner, Franz, neopa, Luschi
besten Dank für Eure Hilfen. Ich, der noch auf der "Volksschule" mit Kreide auf der Schiefertafel herumgekratzt hat, mit 'nem Tintenfäßchen und 'ner Edelstahlfeder "Schönschrift" in Sütterlin geübt hat und auch heute noch mit 'nem Rechenschieber umgehen kann, bin schon überfordert, Eure tollen Ansätze umzusetzen. So then : Schuster, bleib' bei Deinen Leisten ! Ich muß es verstehen, um nicht immer wieder jede Kleinigkeit nachfragen zu müssen. Ich will 'mal schauen, wie weit ich mit meinen Primitiv-Lösungen komme ...
Ganz lieben Dank in's Forum ! Klaus
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige