Microsoft Excel

Herbers Excel/VBA-Archiv

Mittelwerte aus Tabellen

Betrifft: Mittelwerte aus Tabellen von: Marius
Geschrieben am: 03.09.2014 14:16:29

Hallo liebe Leute,

folgendes Problem lässt mich nicht mehr ruhig schlafen und ehrlich gesagt weiß ich auch nicht ob sich das überhaupt mit Excel umsetzen lässt:

Ich habe ein Arbeitsblatt mit Produkten denen ein, mehrere oder kein Datum zugeordnet ist.
Auf dem zweiten Blatt habe ich Performance Daten für diese Produkte.

Jetzt möchte ich auf dem dritten Blatt:

1. in Spalte B den Mittelwert für die Fonds mit Datum aus dem ersten Blatt, rückwirkend für 6 Monate

2. in Spalte C den Mittelwert vom Mittelwert für die restlichen in Blatt 2 sich befindenden Produkte, sofern deren Performance keine leeren Zellen innerhalb der 6 Monate enthält.

(Mittelwert vom Mittelwert soll heißen, dass erst von allen Produkten für die 6 Monate der Mittelwert berechnet wird sofern keine leeren Zellen, und dann von diesem Mittel, nochmal das Mittel. So das am Ende ein Wert dabei raus kommt.)

Da ich mit meinem begrenzten Excel Formel Latein jetzt wirklich nicht mehr weiter komme, und die erste Datei nun schon per Hand sortiert und berechnet habe, bitte ich Euch wirklich um Eure Hilfe.

Anbei eine Beispiel Datei
https://www.herber.de/bbs/user/92458.xlsx

Liebe Grüße und vielen Dank schon jetzt

Euer Marius

  

Betrifft: AW: Teilergebniss? von: hary
Geschrieben am: 03.09.2014 15:19:56

Moin
Hab im Moment kein Excel.
Kannst du nicht das Blatt filtern(Anzeige nur & Monate) und dann mit Formel: =Teilergebniss(101,Bereich)
den Mittelwert berechnen.
Verstehe nicht Mitelwert vom Mittelwert?????
Frage offen.
gruss hary


  

Betrifft: AW: Teilergebniss? von: Marius
Geschrieben am: 03.09.2014 16:02:13

Hallo Harry,

vielen Dank für den Tipp, damit bin ich mit meiner händischen Bearbeitung auf jeden Fall etwas schneller geworden.

Blatt filtern(Anzeige nur & Monate)

- kann ich das auch mit einer Formel umsetzen? weil bei anderen Datensätzen habe ich etwa 800 Produkte...


Formel: =Teilergebniss(101,Bereich)

- funktioniert, allerdings muss ich auch hier wieder Produkte welche leere Zellen enthalten händisch aussortieren. Gibt das dafür eventuell noch was schnelleres? Kann ich hier vielleicht "Anzahl" oder "Anzahl2" mit einbauen, das dies automatisch abgefragt wird?

Beste Grüße
Marius


  

Betrifft: AW: Teilergebniss? von: hary
Geschrieben am: 03.09.2014 17:21:44

Moin
Leere Zellen werden doch nicht mitgenommen. Hier nach der Filterung.



Daten

 F
25,69459038264721E-03
3Produkt 5
4-8,16977767310001E-03
53,97688024282901E-02
6 
71,56945568511002E-03
80,02919074375237
9-0,033886272279434

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
F2=TEILERGEBNIS(101;F4:F243)  =SUBTOTAL(101,R[2]C:R[241]C)

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.02 einschl 64 Bit

gruss hary


  

Betrifft: Hilfe für den ersten Teil der Frage von: Marius
Geschrieben am: 04.09.2014 07:28:59

Hallo zusammen,

ja, also der zweite Schritt funktioniert.

Aber hat auch noch jemand eine Idee für den ersten Teil?
Lässt sich wahrscheinlich nur mit VBA umsetzen, oder?
Leider habe ich davon gar keine Ahnung.

Gruß
Marius


  

Betrifft: AW: Frage auf offen gestellt von: hary
Geschrieben am: 04.09.2014 07:56:27

Moin
Hab mal deine Frage auf offen gestellt. Dazu musst du den Haken in das Kaestchen neben Passwort setzen. So weiss jeder, dass die Frage noch nicht beantwortet ist.
gruss hary


  

Betrifft: AW: Frage auf offen gestellt von: Marius
Geschrieben am: 04.09.2014 10:50:52

Danke Hary,


bin leider noch nicht so bewandert hier im Forum, und habe das dadurch übersehen.
Ich habe jetzt für den ersten Teil einen Ansatz, der leider nicht funktioniert.

'=MITTELWERT(SVERWEIS(B6;Daten!A:CC;VERGLEICH(Datum!A6;Daten!1:1;0);FALSCH);SVERWEIS(C6;Daten!A:CC;VERGLEICH(Datum!A6;Daten!1:1;0);FALSCH))

Und zwar habe ich gedacht, wenn ich mit einem Sverweis die Startzelle und die Endzelle abfragen, und davon den Mittelwert nehme, bekomme ich das was ich will.
Allerdings kann ich die zwei Verweise nicht als Bereich verbinden, sondern nur als zwei Zellen.

Mal einfach beschrieben:
so sollte es sein
=MITTELWERT(D3:H3)
so funktioniert es aber nur mit den Verweisen
=MITTELWERT(D3;H3)

was mir dann natürlich nur den Wert der zwei Zellen angibt und nicht des Bereiches.
Kann ich die zwei Verweise auch als Bereich verknüpfen?

Danke
Marius


  

Betrifft: AW: Frage auf offen gestellt von: fcs
Geschrieben am: 05.09.2014 10:44:02

Hallo Marius,

mit den folgenden Formeln kannst du den Mittelwert eines Zellbereichs mit berechneter Start-/Endzelle berechnen.

=MITTELWERT(BEREICH.VERSCHIEBEN(Daten!$A$1;VERGLEICH(B6;Daten!$A:$A;0)-1; 
VERGLEICH(Datum!A6;Daten!1:1;0)-1;
VERGLEICH(C6;Daten!$A:$A;0)-VERGLEICH(B6;Daten!$A:$A;0)+1;1))

oder

=MITTELWERT(INDEX(Daten!$A:$CC;VERGLEICH(B6;Daten!$A:$A;0);
VERGLEICH(Datum!A6;Daten!1:1;0)):INDEX(Daten!$A:$CC;
VERGLEICH(C6;Daten!$A:$A;0);VERGLEICH(Datum!A6;Daten!1:1;0)))
Gruß
Franz


 

Beiträge aus den Excel-Beispielen zum Thema "Mittelwerte aus Tabellen"