PDA

Vollständige Version anzeigen : Artikelpreis zum Monatsletzten ermitteln aus Preisänderungen


winfire
26.08.2017, 15:17
Hallo!

Ich habe eine Liste mit Artikeln und deren Preisen. Wenn sich der Preis eines Artikels ändert wird ein Eintrag gemacht. Dies kann mehrmals pro Monat der Fall sein oder nur alle paar Monate.

Nun möchte ich daraus eine Pivot-Tabelle erstellen, die mir die Preisentwicklung in den letzten 12 Monaten anzeigt. Stichtag ist immer der Monatsletzte. Also, wenn es mehrere Einträge/Monat gibt, dann zählt immer nur der letzte.


z.B. Ausgangstabelle:

Artikel|Datum|Preis| Monatsende
A 01.01.2017 100 31.01.2017
A 23.03.2017 120 31.03.2017
A 24.03.2017 125 31.03.2017
B 05.01.2017 500 31.01.2017
B 06.06.2017 490 30.06.2017

gewünschtes Ergebnis:

Artikel Jän|Feb|Mär|Apr|Mai|Juni|Juli|....
A 100 100 125 125 125 125 125..
B 500 500 500 500 500 490 490.

ebs17
27.08.2017, 11:32
die Preisentwicklung in den letzten 12 Monaten
Da wir jetzt noch August haben, wäre der Zeitraum der letzten 12 Monate etwa September 2016 bis August 2017. Dazu sind Deine Zahlen unzureichend, insbesondere der Jahreswechsel ist ein besonderer Punkt und die Reihenfolge und Benennung der Spaltenüberschriften entspricht nicht der selbstformulierten Aufgabenstellung.

lupo1
27.08.2017, 11:46
Ich hab's erst mal einfach gemacht.

RPP63neu
27.08.2017, 11:53
Moin!
Wenn es keine Preissenkungen gibt, dann einfach so:

Artikel in Zeilen
Datum in Spalten (es wird automatisch nach Monaten gruppiert)
Preis in Werte (zusammenfassen nach Maximum)

https://i.imgur.com/hMUUMEC.png

Gruß Ralf

ebs17
27.08.2017, 12:01
Wenn jeder die Aufgabenstellung in eine eigene umschreibt, die er gerade lösen kann, geht es natürlich einfacher.
Blöd wäre dann nur, wenn der Auftraggeber auf seiner ursprünglichen Aufgabenstellung (alle Monatsletzten mit dem dann gültigen Preis in einem formulierten Zeitraum) besteht.

lupo1
27.08.2017, 12:19
Ja, das Leben kann streng sein.

RPP63 und ich haben übrigens ähnlich geantwortet und beide etwas missachtet: Der letzte Eintrag im Monat muss nicht der größte sein ... MAX als Pivot-Werte-Zusammenfassung ist also falsch.<pre><b>__|A|____B___|___C__|D|E|____F___|____G___|____H___|____I___|____J___|CODETab von lupo1<br>*1|A|B |C | | |31.01.17|28.02.17|31.03.17|30.04.17|31.05.17|<br>*2|A|01.01.17|100,00| |A| 100,00| 100,00| 122,00| 122,00| 122,00|<br>*3|A|23.03.17|125,00| |B| 500,00| 500,00| 500,00| 490,00| 490,00|<br>*4|A|24.03.17|122,00| | | | | | | |<br>*5|B|05.01.17|500,00| | | | | | | |<br>*6|B|06.04.17|490,00| | | | | | | |<br><br>Zellformeln:<br>F1: =MONATSENDE(MIN(B:B);0)<br>G1: =MONATSENDE(F1+1;0)<br>F2: =INDEX($C:$C;MAX(INDEX((F$1>=$B$2:$B$6)*($E2=$A$2:$A$6)*ZEILE($C$2:$C$6);)))</b></pre>

winfire
27.08.2017, 12:39
Danke für die Hilfe!
Habt ihr noch eine Idee, wie ich den Preis in jenen Monaten bekomme, wo keine Einträge existieren? Ich brauche eine Tabelle mit allen Monaten und alle Zellen müssen einen Preis enthalten.
Und wie gesagt, es ist ganz wichtig, dass immer das letzte Datum vor dem Monatsende als Preis genommen wird.
Preise mit LASTDATE() zusammenfassen?

lupo1
27.08.2017, 12:41
Bitte gucke Dir meine zweite Lösung an.

ebs17
27.08.2017, 12:56
Als Datenbänkler würde ich etwa so vorgehen:
SELECT
A.Artikel,
K.Monatsletzter,
(
SELECT TOP 1
P.Preis
FROM
Preistabelle AS P
WHERE
P.Artikel = A.Artikel
AND
P.Preisdatum <= K.Monatsletzter
ORDER BY
P.Preisdatum DESC
) AS GueltPreis
FROM
Artikeltabelle AS A,
Kalendertabelle AS K
WHERE
K.Monatsletzter BETWEEN [Zeitraumbeginn]
AND
[Zeitraumende]
Man benötigt also als Zwischenschritt eine Liste Artikel, Monatsletzte und zugehöriger Preis. Da die Preistabelle nicht absichern kann, dass alle benötigten Monatsletzten vorkommen, würde ich da von Haus aus eine eigene Tabelle mit der vollständigen Menge der benötigten Monatsletzten führen, die entsprechende Spalte in der Preistabelle ist damit verzichtbar.

Aus der Kombination aller Artikel (einfach) und aller Monatsletzten des gewünschten Zeitraumes erhält man die vollständige Datenbasis, die man nun noch um den letzten gültigen Preis zu Artikel und Monatsletzten ergänzt.

Diese Liste wäre jetzt die Ausgangstabelle für eine einfache Pivottabelle, man könnte aber auch diese Auswahlabfrage in eine Kreuztabellenabfrage umformen, die dann die gewünschte Ansicht mit vollständigen Daten liefert.

Drogist
27.08.2017, 17:11
Welche Excel-Version verwendest du denn?

WS-53
27.08.2017, 23:54
Hallo,

Habt ihr noch eine Idee, wie ich den Preis in jenen Monaten bekomme, wo keine Einträge existieren?

hättest du Lupos Vorschlag getestet, dann hättest du diese unnötige Frage nicht gestellt!!!

Du hättest nur feststellen können, dass die Formel:


F2: =INDEX($C:$C;MAX(INDEX((F$1>=$B$2:$B$6)*($E2=$A$2:$A$6)*ZEILE($C$2:$C$6);)))

dann ein falsches Ergebnis liefert, wenn ein Artikel erst später im Jahr das Erste Mal auftaucht.

Aber dieses Probelm lässt sich durch eine einfache Erweiterung von Luos Formel lösen:

=WENN(MONATSENDE(INDEX($B$3:$B$17;VERGLEICH($E3;$A$3:$A$17;0));0)>F$2;0;INDEX($C:$C;MAX(INDEX((F$2>=$B$3:$B$17)*($E3=$A$3:$A$17)*ZEILE($C$3:$C$17);))))

Un da ich den Bereich der Preisänderungen als Tabelle formatiert habe, passen sich die Formeln automatisch an.

winfire
31.08.2017, 07:43
Danke für die Hilfe. Da ich habe es mit dem SQL-Statement von ebs17 gelöst.