Formel auf Zelle der vorherigen Zeile beziehen

[thetoby]

Hallo zusammen,

ich habe folgendes Anliegen:

Bei einer sehr umfangreichen Datenauswertung soll Excel den Mittelwert über bestimmte Bereiche bilden, also z.B: =MITTELWERT(B1:B50)
Auf diesen Mittelwert folgend soll dann der Mittelwert von (B51:100) gebildet werden. Darauf folgend dann (B101:B150) und so weiter. Also ist das Ende des vorherigen Bereichs der Beginn des neuen Bereichs.
Wenn ich allerdings zwei drei Zeilen mit der Mittelwert-Formel befülle und dann versuche die Formel automatisch auf alle weiteren Bereiche anwenden zu lassen, führt Excel die Formel (B1:B50) mit (B2:B51), (B3:B52)... fort.

Könnt ihr mir sagen, wie ich das am einfachsten anpasse?


Danke und Grüße!
Toby

[Klaus-Dieter]

Hallo Tobi,

könnte mit der Funktion Teilergebnis gehen, falls deine Abschnitte irgend einen gemeinsamen Nenner haben.

[echo]

Hallo Toby , ein Gedanke dazu:

=MITTELWERT(INDIREKT("B"&(ZEILE(A1)-1)*$D$2+1&":B"&(ZEILE(A2)-1)*$D$2))
Formel rein kopieren und runter ziehen.
In D2 habe ich die Schrittweite eingefügt, hier kann natürlich für dein Beispiel auch 50 stehen.

Gruß Holger

[WillWissen]

Hi Tobi,

mein Vorschlag:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Zahlenreihe	Mittelwert	Block
2	1	25,5	A2 - A51
3	2
4	3
49	48
50	49
51	50
52	51	75,5	A52 - A101
53	52
54	53
99	98
100	99
101	100
102	101	125,5	A102 - A151
103	102
104	103
105	104

Zelle	Formel
B2	=WENN(REST(ZEILE()-1;50)=50-49;MITTELWERT(A2:A51);"")
C2	=WENN(ISTZAHL(B2);"A"&ZEILE()&" - "&"A"&ZEILE()+49;"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[RPP63]

Geht auch ohne INDIREKT():
=MITTELWERT(INDEX(B:B;ZEILE(A1)*50-49):INDEX(B:B;ZEILE(A1)*50))
herunterziehen …

	B	C
1	1	25,5
2	2	75,5
3	3	125,5
4	4	175,5
5	5	225,5
6	6	275,5
7	7	325,5
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20

Zelle	Formel
C1	=MITTELWERT(INDEX(B:B;ZEILE(A1)*50-49):INDEX(B:B;ZEILE(A1)*50))

Gruß Ralf

[thetoby]

Habe das Problem mit eurer Hilfe gelöst bekommen. 

=MITTELWERT(INDEX(B:B;ZEILE(B2)+$AV2):INDEX(B:B;ZEILE(B61)+$AV2))

In der Spalte AV steht bei mir nun 0x59,1x59,2x59... Dadurch kann ich die Zeit relativ einfach variieren. 


Vielen Dank und ein frohes Weihnachtsfest euch allen.

[snb]

Oder

Code:

Sub M_snb()
  With ThisWorkbook.Sheets("DPV1")
    sn = Array(.Range("A3"), Format(.Range("B1"), "m_yyyy"), Application.TemplatesPath & "\DPV1_" & Format(Now, "ddmmyyyy_hhmm") & ".xlsx")
  End With
    
  Sheets("DPV1").Copy
  With ActiveWorkbook
    .Sheets(1).UsedRange.Value = Sheets(1).UsedRange.Value
    .SaveAs sn(2)
    .Close 0
  End With
   
  With CreateObject("Outlook.Application").CreateItem(0)
    .To = "My.Kollegen@mail.de"
    .Subject = "Dienstplan - Gruppe: " & sn(0) & " - Monat: " & sn(1) & " - " & Now
    .Attachments.Add sn(2)
    .Body = "Öffnen auf eigenem Gefahr"
    .Send
  End With
End Sub

[Jockel]

Hallo, so kurz vor dem Jahreswechsel eine Power Query Variante (also aktualisieren nicht vergessen) 


  31122018_Mittelwert_mit_Schrittweite_PowerQuery.xlsx (Größe: 40,33 KB / Downloads: 3)