Office: (Office 2007) SVerweis mit suche nach Monat?

Hallo,

hätte da mal folgendes Problem:

Ich habe eine Tabelle, in der ein Datum (TT.MMM - 01. Jan) einem bestimmten Wert chronologisch von oben nach unten eingetragen wird, so, wie es gerade kommt. Zum Beispiel, an welchem Tag ich welchen Rohstoffpreis habe.

Nun möchte ich eine Auswertung mit mittelwerten der einzelnen Monate.
Ich möchte mir also in einer extra Tabelle in einem neuen Datenblatt jeden einzelnen Monat mit den zugehörigen Preisen anschauen um dann daraus einen Mittelwert pro Monat zu generieren.

Leider klappt die Suche mit dem SVerweis nicht, da das Datum ja eigentlich eine Zahl ist...

Kann mir da wer helfen?

LG und schon mal herzlichen Dank
DBOscar1

:)

 

Hallo dboscar1,
ich weiß nicht, ob deine Tabelle Auswertung genau so aussehen muss.
Wenn du da flexibel bist, schau mal, ob dir die angehängte Pivot Tabelle reicht.
Gruß
magie61

 

Hallo Magie61,

vielen Dank für deine Hilfe.
Das sieht schon ziemlich genau so aus, wie ich mir das in etwa vorstelle.
Ich brauche nämlich eigentlich nur den Mittelwert der einzelnen Monate, und diesen in anderen Tabellen dann automatisch zu generieren.

Wie kann ich so eine Pivot-Tabelle denn erstellen?

LG
DBOscar1

 

Hallo dboscar1,
das ist ganz einfach*Smilie
Markiere deine Quelldaten und dann lass dich über Daten -> PivotTable durchs Menü führen. In Schritt 3 dann noch das Layout bestimmen. Wenn du die Werte der einzelnen Tage nicht brauchst, dann reicht es, das Datum in den Spaltenbereich zu ziehen und den Preis in das Datenfeld. Auf dem Preis im Datumsfeld dann ein Doppelklick und statt Summe Mittelwert auswählen.
Weiter geht es dann in der erstellten Pivot Tabelle.
Jetzt muss nur noch das Datum gruppiert werden. Markiere hierzu ein einzelnes Datum in der Pivot Tabelle, rechter Mausklick und Gruppierung. Dort kannst du dann auswählen, ob nach Monaten oder Quartalen gruppiert werden soll.
Wenn du noch weitere Fragen hast, melde dich nochmal.
Gruß
magie61

 

Hallo magie61,

irgendwie klappt das nicht^^

Habe jetzt noch mal die Tabelle, so wie sie besteht und auch bleiben muss angehängt. Wichtig sind die Spalten mit der roten Überschrift. Alle sensiblen Daten habe ich natürlich vorher rausgenommen, sonst findet das meine Firma wohl nicht so toll^^. :-)

Ich brauche also rein theoretisch nur einen Mittelwert der jeweiligen Preise auf die Monate verteilt um sie dann hinterher in anderen Listen weiter verarbeiten zu können. Die Tabelle müsste allerdings auch so flexibel sein, dass sie die (noch) leeren Zeilen in der Urspungstabelle so überwacht, dass wenn da ein weiterer Eintarg hinzugefügt wird, es auch direkt den Mittelwert aktualisiert.

Hatte das ganze Prozedere erst mit wenn-Formeln und dann mist SVerweisen versucht...^^

Vielen Dank schon jetzt für deine Hilfe im Voraus :-)

LG
DBO

 

Hi,

hier eine Formellösung:

[Edit]
sry - noch ein Fehler drin

 

Sorry, war zur Pause.
Angehängt deine Datei mit Pivot.
Gruß
magie61

PS. Oh, den zweiten Teil habe ich jetzt überlesen*frown.gif* Da wird myfake wohl schneller sein.

 

mhhh,...

wenn ich jetzt in der Tabelle aber einen weiteren Wert eingeben, meinetwegen für November noch 3 und Dezember noch 2, sollte sich die Pivot auch aktualisieren... Macht sie aber auch dann nicht, wenn ich auf den Button der Symbolleiste gehe:-(

Möchte nämlich für 2009 direkt auch eine vorbereiten, die sich dann automatisch füllt...

 

So, jetzt habe ich das nochmal angepasst.
Für deine Quelldaten habe ich einen Namen "Quelle" vergeben und mit dynamischem Bereich gearbeitet. D.h., wenn eine neue Zeile dazugetragen wird, passt sich auch der Bereich für die "Quelle" an.
Als Basis für die Pivot wird dann einfach der Name eingetragen (Quelle) und nicht ein Bereich markiert.
Das Aktualisieren der Pivot musst du aber noch manuell anstossen. Eine automatische Aktualisierung lässt sich meines Wissens nur mit VBA lösen und da bin ich nicht die Richtige für*Smilie.
Ich hoffe, das hilft dir trotzdem schon mal weiter.
Gruß
magie61

 

lol,... :-)

sieht jetzt ziemlich genau so aus, wie ich mir das vorgestellt habe, aber nach mehrmaligen gescheiterten Versuchen kann ich sie nicht in das Orginaldokument einfügen ohne hinterher wieder mit den Monatn Schwierigkeiten zu bekommen...^^

Heute ist wohl nicht mein Tag... ;-)

Aber das weitaus bedeutendere Problem ist, dass ich die Aktualisierung dann imme noch selber anstoßen müsste... Das ist weder gewollt noch möglich zum Teil... Komme nicht immer dran und die Daten müssen in der Auswertung aber trotzdem imer aktuell sein. Deswegen war mein erster Weg auch das Knobeln mit Wenn-Sverweis-Formeln...

Aber eins habe ich heute gelernt: Pivot ist (noch) nicht mein Ding^^ ;-)

Aber vielen herzlichen Dank für eure Hilfe!!! :-)

LG
DBO

 

Hallo nochmal,

hier nochmal die Formellösung (sry - hat etwas länger gedauert, da ich plötzlich weg mußte *wink.gif*):

Tabelle1

	K	L
3	Januar	1,375
4	Februar	1,360
5	März	1,378
6	April	1,353
7	Mai	1,313
8	Juni	1,361
9	Juli	1,535
10	August	1,565
11	September	1,527
12	Oktober	1,350
13	November	#DIV/0!
14	Dezember	#DIV/0!

Zelle	Formel
L3	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L4	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L5	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L6	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L7	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L8	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L9	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L10	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L11	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L12	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L13	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)
L14	=SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*($E:$E))/SUMMENPRODUKT((MONAT($A:$A)=ZEILE()-2)*1)

Excel Tabellen im Web darstellen >>

 

sieht ganz schön dick aus die Formel, das mit dem Monat kannte ich gar nicht^^ :-)

Aber irgendwie bekomme ich immer nur #Div/0, egal, wie ich es dreh und wende...???

 

aber, deine Monats-GEschichte hier hat mir so weitergeholfen, dass ich es jetzt etwas aufwändiger zwar aber dennoch logisch und einfach mit Wenn-Formeln gesachafft habe :-)

Vielen Dank!!! :-)

 

Hi,

Du hast es zwar schon geschafft, aber ich wollte trotzdem hierzu noch eine kurze Anmerkung machen. Ich nehmen mal an, dass der Bezugsraum nicht stimmt. Da Du eine "abgespeckte" Version hochgeladen hast, nehme ich mal an, dass es beim Einfügen in die Originaldatei etwas verrutsch ist *Smilie

Vielleicht bekommst Du die Formel ja noch zum laufen (kannst auch gerne nochmal eine Version hochladen, in der Du die Fehlermeldung bekommst!). Ansonsten hast Du ja schon eine funktionierende Lösung *Smilie

cu
myfake