Excel Befehl =MAX()

[Hanfyy]

Hi,
(Office 2010 auf Win XP)
ich versuche aus einer Tabelle mit Variabler Zeilenanzahl in einer spalte den MAX wert zu bekommen.
(Über ein Eingabefenster kann man am Anfang die Anzahl der Regelkreise auswählen, dementsprechend hat die Tabelle zwischen 4 und 'unendlich' Zeilen)

Eingegeben hab ich:

=MAX(E9:E12)

(von E9-E12 ist die minimal mögliche auszuwertende Menge)

Das Problem ist, wenn die Tabelle nun mehr Zeilen bekommt, bleibt der Befehl bei E12 und wächst nicht mit.

Zum Vgl. der Befehl

=SUMME(D912)

ändert sich automatisch mit.


Gibt es da eine Ergänzung oder eine Befehlsmatrix die das Problem löst?

Danke

 

[-Zocker-]

2 Möglichkeiten:

Entweder die Formel abändern auf

=MAX(E:E)

dann wird per se die komplette Spalte E genommen, egal wieviel Zeilen

oder

=MAX(E$9:E12)

und diese Formel dann bereits bis zum Ende ziehen (z.B. bis Zeile 50 oder bis wo auch immer).
Solange z.B. Zeile 13 dann unbefüllt bleit, erscheint als Max auch nur "", wenn E13 jedoch befüllt wird, so wird automatisch der neue gültige MAX-Wert ausgegeben.

 

[Hanfyy]

Geht leider beides nicht, da das Ergebnis der Auswertung jeweils unter der Tabelle steht.
Soll heißen:
Tabelle geht bis Zeile 12, Ergebnis steht in Zeile 13.
Tabelle geht bis Zeile 33, Ergebnis steht in Zeile 34.

Oder gibt es einen befehl, mit dem man sagen kann 'Komplette Spalte E ohne Feld X' ?

 

[Mirakel]

was machst du denn in der Summenspalte anders als in der MAX-Spalte?

Wie setzt du den Max-Befehl in die richtige Zelle/Zeile?
Wie werden die zusätzlichen Zahlen in die Tabelle geschrieben?

 

[-Zocker-]

wie fügst du eine weitere Zeile ein?

 

[Hanfyy]

Also Grundaufbau ist der, das über VBA beim drücken eines 'start' Knopfes erst ein Popup Fenster aufgeht, in dem man ein paar Grundinfos eingibt. Dann gelangt man auf das zweite Tabellenblatt (tbl_Details), in dem man weitere details ergänzen muss. Dieses wird generiert aufgrund eines ausgeblendeten Tabellenblattes (tbl_Details_Vorlage), in dem nur 1 Regelkreis vorhanden ist. Per VBA Programmierung werden dann eben mehr kreise hinzugefügt, je nach dem, was für Grundinformationen man eingegeben hat.

Wie oben bereits beschrieben, steht bei dem Summen befehl auch nicht mehr drin als =SUMME(von:bis) aber der befehlt wächst eben automatisch mit, was der MAX befehl nicht macht.
Und ich habe keine Ahnung warum... und google hilft leider auch nicht.

EDIT:
Wenn ich das Feld mit der SUMME 1zu1 kopiere, wächst es auch nicht mit. Das heißt doch, dass es irgendwo in VBA geregelt sein muss oder?

 

[qhil]

Also wenn das Ergebnis bei mir direkt unter der Zahlenreihe steht, dann wird die Funktion entsprechend erweitert. Wenn nicht, dann eben nicht. Es erscheint ein Hinweis, dass nicht alle Zellen mit einbezogen werden.

Siehe Anhang. Zahlenreihe; Linke Funktion = MAX; Rechte Funktion = SUMME

Allerdings frage ich mich, wieso du die Rechnung nicht direkt ins Makro baust, dass wäre doch wesentlich eleganter oder ist das bei dir nicht realisierbar?

 

[myoizo]

Ich habe das ganze gerade ausprobiert und bei mir wächst MAX mit, wenn ich weitere Werte eingebe (MAX steht hierbei auch in der selben Spalte, wie die Werte) . Alternativ könntest Du auch einen dynamischen Namen (mittels BEREICH.VERSCHIEBEN() ) erstellen und davon dann den MAX-Wert ermitteln.

Ins Makro einbauen wäre doch, wie auch schon qHiL schreibt, auch problemlos machbar.

 

[-Zocker-]

Das der Summenbefehl automatisch mit wächst, liegt an der "künstlichen Intelligenz" von Excel und ist normal.
Wenn du eine Summe willst, denkt Excel automatisch, dass die Summe über alle Werte gehen soll, während er beim Max Befehl die Intelligenz nicht hat, da diese Formel oftmals genutzt wird um nur den Max Wert eines bestimmten Bereiches auszugeben (und eben nicht den Max Wert von allem).

Dass dies so ist, bestätigt auch die Excel-Formelüberprüfung, die seit 2003 Einzug gehalten hat.
Verwendest du z.B. die Summenformel in Zeile 10 (wobei z.B. in Zeile 1-9 und in Zeile 10-19 Werte stehen), so merkert der Formelüberprüfung, dass sich die Summenformel nicht auf den ganzen Bereich bezieht. Nutzt du an gleicher Stelle jedoch die Max-Formel, so merkert die Formelüberprüfung nicht, da Excel denkt, dass du auch wirklich nur vom halben Bereich (also in meinem Bsp. von Zeile 1-9) den Max-Wert ausgeben möchtest.

 

[Hanfyy]

Ich habe das Programm nicht geschrieben und es ist zu umfangreich, um die richtige stelle zu finden. Und den Schreiber kann ich leider auch nicht kontaktieren.
Und mal unter uns, meine VBA Kenntnisse reichen dazu wohl auch nicht aus.

Bereich verschieben klappt auch nicht, da sich der Bereich ja immer unterschiedlich verschiebt.

 

[myoizo]

Habe ich das richtig verstanden: Es ist so, dass bspw. in einer Spalte die Werte von Zeile 1 bis 19 gehen, in Zeile 20 dann der MAX-Wert steht und dann direkt von Zeile 21 bis bspw. 44 die nächsten Werte kommen mit MAX-Wert dann in 45? Und diese Bereiche immer variieren?

>> dann bleibt fast nur eine VBA-Lösung, denn dort wird festgelegt wo sich die Bereiche befinden, bei denen der MAX-Wert zu ermitteln ist

>> sonst wäre vllt ein Beispiel hilfreich

 

[Hanfyy]

Nicht ganz.
Erst kommt ein Tabellen Kopf etc. ca Zeile 1-6.
Spaltenbeschriftung ist in Zeile 7.
Einheit der Spalte (zB kWh) ist in Zeile 8
Dann kommen Werte für der jeweiligen Regelkreise. Zeile 9 - X
dann kommen die Auswertungen in Zeile X+1.
Fertig. Darunter kommt nichts mehr.

weiter rechts geht es auch noch weiter mit anderen kalkulatorischen Sachen, die damit allerdings nichts zu tun haben

 

[Mirakel]

irgendwie klingt das so, als ob die Werte für die Summe anders eingefügt werden, als für die MAX-Berechnung.
Würde dir eine Makro-Funktion weiterhelfen?
Die könnte ich dir eben zusammenbauen. Die könntest du dann nutzen wie eine normale Funktion, nur das sie eben über VBA läuft.

 

[Hanfyy]

Vielen Dank, aber musst dir die Mühe nicht machen.
Wenn ich mal Zeit habe, werde ich die Programmstruktur durchsuchen.
Will auch nichts anderes zerschießen...

 

[Mirakel]

Ich mach mir die Mühe auch, weils mir Spaß macht
Und ist ja nix sooo großes

Function mymax()
mymax = Application.WorksheetFunction.Max(ActiveSheet.Range(Cells(9, 3), Cells(ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row - 1, 3)))
Application.Volatile
End Function

Diese Funktion ist jetzt sehr auf deine Bedürfnisse zugeschnitten.

Sie checkt die letzte Zeile in Spalte C, da wo die Max-Funktion drinsteht, und nimmt dann den Bereich von Zeile 9 bis eins drüber.

Wenn du was anderes in Spalte C stehen hast gehts so nicht. Wenn die Max-Funktion wo anders als direkt drunter steht, müsste man auch was ändern.
Dafür funktioniert sie aber sonst, genau so wie du möchtest
einfach in die Zelle "=mymax()" schreiben (ohne Anführungszeichen) und die Ausgabe ist richtig.

 

[myoizo]

Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Value = WorksheetFunction.max(Range("C9:C" & Cells(Rows.Count, 3).End(xlUp).Row))

Ermittelt ebenfalls den MAX-Wert zwischen Zeile 9 und X (in Spalte C), schreibt aber den MAX-Wert gleich in die erste leere Zelle (ergo in Zelle X+1)

Also erst das bekannte Makro ausführen, dann den Einzeiler oder direkt in den Code integrieren.

 

[Mirakel]

Also erst das bekannte Makro ausführen, dann den Einzeiler oder direkt in den Code integrieren.

Ja, das ist mir irgendwie auch erst später gekommen. Direkt in den Code vom Makro kann man das auch noch einfügen

Ich würde das dann aber lieber so machen:

Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Formula = "=MAX(C9:C" & Cells(Rows.Count, 3).End(xlUp).Row & ")"

Geht davon aus, dass "=max..." noch nicht drinsteht.

Der Code schreibt dir nämlich die Formel da rein und nicht nur das Ergebnis.

 

[cawti]

Warum macht ihr es euch so schwer mit Office 2010? Das ganze einfach als Tabelle definieren, bzw. Formatieren und man hat einen dynamischen Bereich in dem man einfach In der Ergebniszeile die Funktion Max auswählt.