Hallo Experten,
ich habe folgendes Problem. Ich habe ein Dokument mit ca. 5.000 Artikeln und den monatlich verkauften Stueckzahlen von Jan.01-Dez.04. Etwa so:
Artikel Jan01 Feb01 Mar01…Dez04
X1 12 16 0 17
X2 0 0 0 6
X3 1 2 2 3
Nun will ich ueber die Jahre die durchschnittliche monatliche Verteilung errechnen, z.B. vom Artikel X1 werden im Jan 13%, Feb.15%…verkauft. Allerdings moechte ich Jahre, die in mindestens 3 Monaten 0 Stueckzahlen hatten rausnehmen, um die Statistik nicht zu verzerren. Kann mir jemand eine Loesung nennen, wie ich diese Jahre finde? Ich hoffe ich habe das Problem einigermassen vernueftig erklaert.
Vielen Dank fuer Eure Hilfe,
Bastian
versuchs mal in den Optionen die Nullwerte auszublenden, dann sollten die das auch nicht verzerren - ansonsten
=MITTELWERT(Bereich)
Hallo Bastian,
dein Problem ist nicht ganz einfach zu lösen. Mit einer Hilftabelle, in der Zwischenwerte berechnet werden, geht es.
Tabelle Verkauf enthält die Basisdaten für die einzelnen Monate.
In der Hilftabelle werden die Verkaufszahlen jener Monate auf 0 gesetzt, die in der Statistik nicht berücksichtigt werden sollen.
In Tabelle Statistik werden dann die nicht zu berücksigenden Jahre und die Anteile der Monate an den Verkaufzahlen in Prozent berechnet.
Damit die Formeln funktionieren müssen die Monate in der Tabelle Verkauf als Datum (z.B. 01.01.2001) eingegeben sein; das Format kann man dann so einstellen, dass z.B. Jan 01 angezeigt wird.
Nachfolgend Beispiele für die 3 Tabellen mit ihren Formeln. Formeln mit geschweiften Klammern sind Matrixformeln (Eingabe mit ++ abschließen).
Tabellenblattname: Verkauf
A B C D
1 Verkaufszahlen
2 Artikel 01.01.01 01.02.01 01.03.01
3 X1 10 12 9
4 X2 0 0 0
5 X3 0 0 120
Diese Tabele enthält bis zur Spalte AW die Daten für die Monate
Tabellenblattname: HilfTabelle
A B C D
1 Verkaufszahlen berücksichtigen
2 Artikel 01.01.01 01.02.01 01.03.01
3 X1 10 12 9
4 X2 0 0 0
5 X3 0 0 120
Benutzte Formeln:
A2: =Verkauf!A2
B2: =Verkauf!B2
C2: =Verkauf!C2
D2: =Verkauf!D2
A3: =Verkauf!A3
B3: =WENN(WVERWEIS(JAHR(B$2);Statistik!$B$2:blush:E$20000;ZEILE(B3)-1)="JA";Verkauf!B3;0)
C3: =WENN(WVERWEIS(JAHR(C$2);Statistik!$B$2:blush:E$20000;ZEILE(C3)-1)="JA";Verkauf!C3;0)
D3: =WENN(WVERWEIS(JAHR(D$2);Statistik!$B$2:blush:E$20000;ZEILE(D3)-1)="JA";Verkauf!D3;0)
Tabellenblattname: Statistik
A B C D E F G H
1 Jahr berücksichtigen ? Statistik 2001 -2004
2 Artikel 2001 2002 2003 2004 Jan Feb Mrz
3 X1 JA NEIN NEIN NEIN 0,081 0,098 0,073
4 X2 NEIN JA JA JA 0,069 0,06 0,078
5 X3 JA JA JA NEIN 0,006 0,009 0,041
Benutzte Formeln:
A2: =Verkauf!A2
A3: =Verkauf!A3
B3: {=WENN(SUMME(WENN(B$2=JAHR(Verkauf!$B$2:blush:AW$2);WENN(Verkauf!$B3:blush:AW3=0;1;0)))\>=3;"NEIN";"JA")}
C3: {=WENN(SUMME(WENN(C$2=JAHR(Verkauf!$B$2:blush:AW$2);WENN(Verkauf!$B3:blush:AW3=0;1;0)))\>=3;"NEIN";"JA")}
D3: {=WENN(SUMME(WENN(D$2=JAHR(Verkauf!$B$2:blush:AW$2);WENN(Verkauf!$B3:blush:AW3=0;1;0)))\>=3;"NEIN";"JA")}
E3: {=WENN(SUMME(WENN(E$2=JAHR(Verkauf!$B$2:blush:AW$2);WENN(Verkauf!$B3:blush:AW3=0;1;0)))\>=3;"NEIN";"JA")}
F3: {=SUMME(WENN(Statistik!F$2=TEXT(HilfTabelle!$B$2:blush:AW$2;"MMM");HilfTabelle!$B3:blush:AW3;0))/SUMME(HilfTabelle!$B3:blush:AW3)}
G3: {=SUMME(WENN(Statistik!G$2=TEXT(HilfTabelle!$B$2:blush:AW$2;"MMM");HilfTabelle!$B3:blush:AW3;0))/SUMME(HilfTabelle!$B3:blush:AW3)}
H3: {=SUMME(WENN(Statistik!H$2=TEXT(HilfTabelle!$B$2:blush:AW$2;"MMM");HilfTabelle!$B3:blush:AW3;0))/SUMME(HilfTabelle!$B3:blush:AW3)}
Da die Formeln recht kompliziert sind, schicke ich dir die Beispieldatei per e-mail zu.
Gruß
Franz
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hallo Bastian,
leider hast Du uns Programm & Version verschwiegen.
Ich habe Dir in xls2000 eine VBA-Lösung gestrickt,
die ich Dir auch per Mail schicke.
Gruß Carola
Hallo Franz,
super. Das ist eine tolle Loesung. Vielen 1000 Dank. Jetzt muss ich es nur noch umsetzen. Obwohl ich haette da noch eine Frage, was ist eine Matrizformel?
Danke nochmals,
Bastian
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hallo Franz,
super. Das ist eine tolle Loesung. Vielen 1000 Dank. Jetzt
muss ich es nur noch umsetzen. Obwohl ich haette da noch eine
Frage, was ist eine Matrizformel?
Danke nochmals,
Bastian
Hallo Bastian,
am besten schaust du in der EXCEL-Hilfe unter Matrixformeln nach.
Mit Matrixformeln kann man Rechenoperationen mit den Werten von Bereichen durchführen. Das Ergebnis kann dann ein Wert in einer Einzelnen Zelle sein oder auch viele Einzelergebnisse in einem Tabellenbereich
Gruß
Franz