expertenaustausch > microsoft.* > microsoft.excel

Thom (24.03.2008, 22:34)
Hallo liebe NG

Es ist kompliziert mein Anliegen zu formulieren. In der Spalte der
Produktegruppe habe ich Einträge mit Werten. Es gibt untereinander diverse
solcher Produktegruppen. In einer Separaten Spalte sollte jeweils der
Maximalwert der Produktegruppe sein. Da die Produktegruppen unterschiedliche
Einträge (einmal 4 einmal 20 oder mehr) haben, ist der Bereich nicht immer
der selbe.

Ich suche eine Formel, die automatisch die leerzeile oben und unten bemerkt
und dann nur der Bereich dazwischen berechnet.

Hier eine Musterdatei zum besseren Verständnis.


Danke im Voraus für mögliche Tipps.

Gruss Thom
Andreas Killer (25.03.2008, 13:17)
> Ich suche eine Formel, die automatisch die leerzeile oben und unten bemerkt
> und dann nur der Bereich dazwischen berechnet.
> Hier eine Musterdatei zum besseren Verständnis.

Ist mit einer Formel IMHO wohl schwerlich zu machen, mit VBA ist's
ganz einfach:

Function MaxW(ByVal ErsteZelle As Range) As Double
'Durchläuft alle Zellen ab ErsteZelle nach unten bis zur einer
leeren Zelle, gibt den größten Wert zurück
Dim Temp As Double, X As Integer, Y As Integer

'Position der ersten Zelle
X = ErsteZelle.Column
Y = ErsteZelle.Row
'Wert der ersten Zelle
Temp = ErsteZelle.Value
'Durchlaufe alle Zellen nach unten bis zu einer leeren Zelle
Do While Cells(Y, X) <> ""
'Ist der Wert größer als der bisher gefundene?
If Cells(Y, X).Value > Temp Then Temp = Cells(Y, X).Value
'Haben wir das Ende des Tabellenblattes erreicht?
If Y = Rows.Count Then Exit Do
'Nächste Zelle
Y = Y + 1
Loop
'Wert zurückgeben
MaxW = Temp
End Function

(Mappe öffnen, Alt-F11 drücken, Menü Einfügen/Modul, obigen Text
einfügen, Fenster zu)

In Deine Ergebniszelle gibt's Du z.B. nun =MaxW(C6) ein, speichern,
fertig.

Wenn Du eine Function suchst die automatisch alle Produktgruppen in
einer Spalte berechnet, dann musst Du uns sagen wo die Ergebnisse
ausgegeben werden sollen.

Andreas.
Alexander Wolff (25.03.2008, 13:39)
> Es ist kompliziert mein Anliegen zu formulieren. In der Spalte der
> Produktegruppe habe ich Einträge mit Werten. Es gibt untereinander diverse
> solcher Produktegruppen. In einer Separaten Spalte sollte jeweils der
> Maximalwert der Produktegruppe sein. Da die Produktegruppen
> unterschiedliche Einträge (einmal 4 einmal 20 oder mehr) haben, ist der
> Bereich nicht immer der selbe.
> Ich suche eine Formel, die automatisch die leerzeile oben und unten
> bemerkt und dann nur der Bereich dazwischen berechnet.


Da der Bereich anscheinend händisch erstellt wird, würde ich vor einer neuen
Produktgruppe eine Zeile frei lassen und dann die Formel =MAX(C5:C15) für
den ersten Block nehmen. Einfügen musst Du neue Produkte dann immer

nach C5 bzw. vor C15.

Die richtige Vorgehensweise ist aber eine ganz andere: Mach aus Deiner Liste
statt:

Produktgruppe A
Produkt
Produktname1
Produktname2
:

eine "anständige":

Produktgruppe A Produktname1
Produktgruppe A Produktname2
:

und arbeite dann mit Daten Teilergebnisse.

Wenn Deine Tabelle jedoch so bleiben muss, gibt es noch folgende Möglichkeit
(max. 99 Produktnamen hintereinander, sonst entsprechend anpassen!):

E5: =MAX(BEREICH.VERSCHIEBEN(C6;;;MIN((100-99*(C6:C104=0))*ZEILE(1:99))-1;))
Diese Formel musst Du mit Strg-Umsch-Eingabe abschließen, sonst gehts nicht!

Kopiere sie in alle gelben Felder der Spalte E.
Wolfgang Habernoll (25.03.2008, 13:42)
Hallo Thom

"Thom" <tstuder> schrieb im Newsbeitrag
news:4344
[..]
> Hier eine Musterdatei zum besseren Verständnis.
>
> Danke im Voraus für mögliche Tipps.


hier eine Formellösung die aber *genau auf deinen Dateiaufbau* angewiesen ist.
Kopiere sie in E5 und ziehe sie soweit wie nötig and den Produktgruppen nach
unten. Am Ende (A41) wird ein Eintrag erwartet, schreibe einfach etwas rein. Du
kannst auch Zeilen mit neuen Produkten einfügen.

=WENN(UND(B4="";B5="");MAX(BEREICH.VERSCHIEBEN($C$ 1;ZEILE();0;VERGLEICH("*";BEREICH.VERSCHIEBEN($A$1 ;ZEILE();0;555;1);0);1));"")
Thom (25.03.2008, 14:45)
Fantastisch. Danke euch für die Tipps. Mal sehen welche Lösung die
effektivste ist.

Gruss Thom

"Thom" <tstuder> schrieb im Newsbeitrag
news:4344
[..]
Alexander Wolff (25.03.2008, 16:46)
> Wenn Deine Tabelle jedoch so bleiben muss, gibt es noch folgende
> Möglichkeit (max. 99 Produktnamen hintereinander, sonst entsprechend
> anpassen!):
> E5:
> =MAX(BEREICH.VERSCHIEBEN(C6;;;MIN((100-99*(C6:C104=0))*ZEILE(1:99))-1;))
> Diese Formel musst Du mit Strg-Umsch-Eingabe abschließen, sonst gehts
> nicht!
> Kopiere sie in alle gelben Felder der Spalte E.


Korrektur:

Bitte ändere die o.g. Formel auf
E5:
=MAX(BEREICH.VERSCHIEBEN(C6;;;MIN((100-99*(BEREICH.VERSCHIEBEN(C6;;;99;)=0))*ZEILE(1:99))-1;))
(mit Strg-Umsch-Eing!) damit Du in Deinen Daten straflos Zeilen einfügen und
löschen kannst.

In Anlehnung an Wolfgang Habernoll (siehe auch sein "A41"!) ginge auch das
einfachere
E5: =MAX(BEREICH.VERSCHIEBEN(C6;;;VERGLEICH("*";A6:A10 5;);))
(ohne Strg-Umsch-Eing!) und mit nur einer Längenangabe 100 statt oben vier.
Bernd P (25.03.2008, 17:55)
Hallo,

Nimm wie bereits Alexander sagte, am besten die Teilergebnis
Funktionalität:

Produktgruppe Produktname Wert LfdNr
Produktgruppe A Produktname1 21 1
Produktgruppe A Produktname2 21 2
:

Mit den Überschriften wird es recht einfach, denke ich.

Matrixformeln sind IMHO fehleranfällig, und ein Makroergebnis in
einzelnen Datenzeilen verschwindet, sobald die entsprechende Zeile
gelöscht wird, oder Du hast zu viele Ergebnisse, falls Du sie kopierst
(manueller Aufwand eben).

Viele Grüße,
Bernd
Alexander Wolff (25.03.2008, 18:03)
Da von grundsätzlicher Bedeutung:
Ähnliche Themen