Login

***Itzeberg*** · 12.03.2021, 20:29

Hallo zusammen,

ich habe folgendes Problem:

Ich habe in Spalte A eine Reihe von Sportläden. In Spalten B bis ZZ habe ich die Monatsumsätze jedes Ladens. Ich möchte nun den Durchschnittlichen Monatsumsatz jedes Ladens aus den letzten 12 Monaten ermitteln. Gezählt werden sollen nur die Monate, in denen der Laden auch einen Wert hat. Es kann nämlich sein, dass ein Laden zwischenzeitlich geschlossen hatte und keinen Umsatz gemacht hat. Hat ein Store weniger als 12 Monate mit Umsätzen, sollten der Durchschnitt der gültigen Monate verwendet werden.

Beispiel:

Store A: 10 10 10 10 - - 20 20 20 20 20 20 20 20 20 = 17,5 ((9*20 + 3*10)/12)
Store B: 10 10 10 20 20 20  20 20 20 20 20 20 20 20 20 = 20 (12*20 /12)
Store C: - - - - - - - - - -    -    -    -    -    -    -    -    20 40 = 30 ((20 +40) /2

Irgendeine Idee wie ich das hinbekomme?

Vielen Dank schon einmal an jeden, der sich die Mühe macht hier eine Lösung zu finden! Smile

Gast 123 · 12.03.2021, 23:22

Hallo

wenn du mit Formeln arbeiten willst auf die schnelle eine Idee. Verwende in einer Hilfsspalte COUNTA
Damit kannst du die Anzahl der Eintrage in den Spalten Jnauar - Dezember feststellen. Es dürfen aber keine Nullen drin sein!

mfg Gast 123

shift-del · 12.03.2021, 23:27

Mahlzeit

Arbeitsblatt mit dem Namen 'MTD'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

9

Total

Gewünschtes

Total

10

Jan_Periodic 2019

Run-rate

Ergebnis

Net Sales

11

Store A

151

126

86

140

183

188

116

186

164

183

164

183

117

171

117

51

171

12

Store B

149

126

86

140

183

188

116

186

164

183

117

171

117

51

171

13

Store C

139

164

183

117

171

117

51

171

14

15

16

Store A

151

FALSCH

WAHR

17

Store B

149

FALSCH

WAHR

18

Store C

139

WAHR

Zelle	Formel
C16	=MITTELWERTWENNS(E11:T11;E16:T16;WAHR)
E16	=ANZAHL(E11:$T11)<=12

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

LCohen · (Dieser Beitrag wurde zuletzt bearbeitet: 13.03.2021, 08:53 von LCohen.)

Hervorragende Aufgabenstellung! Man muss ja auch mal loben.

aus herber.de/excelformeln/pages/Letzte_benutzte_Zelle_in_einer_Zeile_oder_Spalte_Frank_Kabel_in_memoriam.html

C11: =MITTELWERT(ZZ11:INDEX(E11:ZZ11;KGRÖSSTE((E11:ZZ11<>"")*SPALTE(E11:ZZ11);12)))

Ohne Hilfszellen - und trotzdem recht kurz.

Sie funktioniert nur, weil leere Zellen hier nicht leer rechnen, sondern als 0. Und es 12 Zellen (inklusive leere) oder mehr gibt. Bei weniger als 12 Zahlengefüllten gäbe es #ZAHL!.

Fred11 · 13.03.2021, 08:47

Hallo Itzeberg,

z.B. so, mit zwei Hilfsspalten zu Ermittlung von Beginn und Ende des zu berücksichtigenden Zellbereiches:

Arbeitsblatt mit dem Namen 'MTD'
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
8		Hilfsspalten
9		erste	letzte	Total	Gewünschtes		Total	Total	Total	Total	Total	Total	Total	Total	Total	Total	Total	Total	Total	Total	Total	Total
10	Jan_Periodic 2019	Spalte	Spalte	Run-rate	Ergebnis		Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales	Net Sales
11	Store A	5	16		151		126	86	140	183	188	116	186	164	183	164	183	117	171	117	51	171
12	Store B	3	16		149		126	86	140	183	188	116	186			164	183	117	171	117	51	171
13	Store C	1	16		139											164	183	117	171	117	51	171

Zelle	Formel
B11	=MAX(1;VERGLEICH(-1;$G11:$AAB11;-1)-11-ANZAHLLEEREZELLEN(INDEX($G11:$AAB11;1):INDEX($G11:$AAB11;$C11)))
C11	=VERGLEICH(-1;$G11:$AAB11;-1)
E11	=MITTELWERT(INDEX($G11:$AAB11;$B11):INDEX($G11:$AAB11;$C11))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

(Formeln dann nach unten ziehen)

Funktioniert nur (richtig), wenn die Umsatzwerte positiv sind; wenn hier auch mal negative Werte vorkommen können, müssen die Formeln etwas modifiziert werden

Das ganze geht natürlich auch ohne die beiden Hilfsspalten; man kann das alles auch direkt in die MITTELWERT-Formel reinpacken, aber dann wird die Formel halt sehr lang und unübersichtlich...

Gruß
Fred

RPP63 · Registriert seit: 12.10.2014 Version(en): 365 Insider (64 Bit)

Moin!
Ich bin an die Aufgabe gegangen, ohne die bisherigen Lösungen zu sehen, weil es ja wirklich interessant ist.

War dann tatsächlich sehr nah an LCohens Lösung.
Da meine Formel (INDEX(11:11;;0)) in einen Fehler lief, wenn weniger als 12 Einträge vorhanden sind, habe ich mir mit MAX() beholfen:
=MITTELWERT(ZZ11:INDEX(11:11;;MAX(5;KGRÖSSTE((E11:ZZ11<>"")*SPALTE(E11:ZZ11);12))))

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
9		Total	gew.		Total	Total	Total	Total	Total	Total	Total	Total	Total	Total	Total
10		Run-rate	Erg		TNS	TNS	TNS	TNS	TNS	TNS	TNS	TNS	TNS	TNS	TNS
11	Store A	151	151		126	86	140	183	188	116	186	164	183	164	183
12	Store B	149	149		126	86	140	183	188	116	186			164	183
13	Store C	139	139											164	183

Zelle	Formel
B11	=MITTELWERT(ZZ11:INDEX(11:11;;MAX(5;KGRÖSSTE((E11:ZZ11<>"")*SPALTE(E11:ZZ11);12))))
B12	=MITTELWERT(ZZ12:INDEX(12:12;;MAX(5;KGRÖSSTE((E12:ZZ12<>"")*SPALTE(E12:ZZ12);12))))
B13	=MITTELWERT(ZZ13:INDEX(13:13;;MAX(5;KGRÖSSTE((E13:ZZ13<>"")*SPALTE(E13:ZZ13);12))))

Gruß Ralf

***Itzeberg*** · Registriert seit: 12.03.2021 Version(en): Office 365

Ganz herzlichen Dank an alle Helfer!

Ich bin mit der Formel von LCohen / RPP63 gegangen, weil ich da am wenigsten an der Datenstruktur ändern muss.

Danke euch!

Sulprobil · 14.03.2021, 10:05

https://berndplumhoff.gitbook.io/sulprob.../sbsumlast

Es können ja auch mal weniger als 12 Werte da sein.

LCohen · (Dieser Beitrag wurde zuletzt bearbeitet: 14.03.2021, 10:09 von LCohen.)

Dann habe ich mich für Dich anscheinend missverständlich ausgedrückt. C:ZZ sind IMMER mehr als 12 Werte, weil sie in 0 oder Zahl gewandelt werden, jedoch nicht leer bleiben.

Login
Benutzername:
Passwort:	Passwort vergessen?
	Merken