Microsoft Excel

Herbers Excel/VBA-Archiv

Datum/Stückzahl auswerten


Betrifft: Datum/Stückzahl auswerten von: Daniel
Geschrieben am: 10.07.2018 00:05:32

Hallo alle zusammen ich bin neu hier und habe ein frage und zwar soll ich für die Arbeit ein art Bestellliste erstellen wo in einer Zelle Datum/Stückzahl stehen soll
Zur Auswertung soll mit in einer separaten zelle die summe aller Stükzahlen in einer Zeile sprich von einem Produkt ausgegeben werden jedoch soll es auch möglich seine nach datum zu filtern und so fie Stückzahlen aus eonem Monat zu bekommen
Ist das irgendwie realisierbar wenn ja wie?
Danke im voraus für eure Antworten

  

Betrifft: AW: Datum/Stückzahl auswerten von: SF
Geschrieben am: 10.07.2018 06:24:09

Hola,

ohne Beispieldatei ist das reine Raterei.
Zählenwenn(), Summenproduktj), Teilergebnis().....

Gruß,
steve1da


  

Betrifft: AW: Datum/Stückzahl auswerten von: Matthias L
Geschrieben am: 10.07.2018 06:57:58

Hallo

Das kommt drauf an wie Deine Tabelle aufgebaut sein soll.
Ich entscheide mich oft für Teilergebnis.
Dann lässt sich der Filter sehr gut verwenden.


Tabelle1

 ABCD
1  Summe (Filter) ? ?28
2ProduktDatumMonat(Datum)Stückzahl
3Produkt310.04.201841
4Produkt109.05.2018510
5Produkt210.05.201854
6Produkt310.05.201853
7Produkt110.07.201874
8Produkt210.07.201874
9Produkt401.08.201882

Formeln der Tabelle
ZelleFormel
D1=TEILERGEBNIS(9;Tabelle1[Stückzahl])
C3=MONAT(B3)
C4=MONAT(B4)
C5=MONAT(B5)
C6=MONAT(B6)
C7=MONAT(B7)
C8=MONAT(B8)
C9=MONAT(B9)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gefiltert nach Monat sieht es dann so aus:

Tabelle1

 ABCD
1  Summe (Filter) ? ?8
2ProduktDatumMonat(Datum)Stückzahl
7Produkt110.07.201874
8Produkt210.07.201874


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

oder gefiltert nach Produkt dann so:

Tabelle1

 ABCD
1  Summe (Filter) ? ?8
2ProduktDatumMonat(Datum)Stückzahl
5Produkt210.05.201854
8Produkt210.07.201874


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Wobei man die Spalte("C") nicht unbedingt braucht, man kann auch Spalte("B")
nach Monat filtern.

Du hast die Wahl

Gruß Matthias


  

Betrifft: Hört sich … von: RPP63
Geschrieben am: 10.07.2018 07:28:46

Moin!
… für mich eindeutig nach Pivot-Table an.
Genaueres kann auch ich aber erst bei Vorlage eines Musters sagen.
Grob aber: Datum und Produkt in Zeilen, Stück in Werte.
Das Datum kannst Du dann nach Monat gruppieren.

Gruß Ralf


  

Betrifft: Zusatz von: Daniel
Geschrieben am: 10.07.2018 14:52:16

Hier die Datei wie ich mir das Vorstelle frage ist weiterhin ob dies realisierbar ist

https://www.herber.de/bbs/user/122591.xlsx

Gruß Daniel


  

Betrifft: AW: wäre schon realisierbar, allerdings ... von: neopa C
Geschrieben am: 10.07.2018 15:43:51

Hallo Daniel,

... ist Deine Datenerfassung nicht gerade optimal zu nennen. Datum und Stückzahl sollte immer besser in zwei Zellen getrennt erfasst und für jeden dieser Datenkombination ein separater Datensatz angelegt sein. Dann wäre wie von Ralf vorgeschlagen, eine Pivotauswertung die schnellste und auch die einfachste Lösung.

Wenn jedoch die Datumsangaben und die Stückzahl immer durch z.B. ein "/" getrennt sind, kann man bei Vorhandensein neueren Excelversionen, mittels PowerQuery wie von Günther vorgeschlagen, die vorbezeichnete Datenstruktur erstellen lassen und dann die Pivotauswertung anwenden. Wenn keine Massendaten auszuwerten sind, wäre allerdings auch eine Formelauswertung möglich.

Gruß Werner
.. , - ...


  

Betrifft: AW: wäre schon realisierbar, allerdings ... von: Daniel
Geschrieben am: 10.07.2018 15:55:38

Hallo Werner
Ja das datum und die Stückzahl sind immer durch ein "/" getrennt jedoch gibt es auch leere zellen. Wenn es möglich wäre vllt ein Beispiel zu power query ganz nett
Gruß Daniel


  

Betrifft: AW: nachgefragt und hingewiesen ... von: neopa C
Geschrieben am: 10.07.2018 16:02:35

Hallo Daniel,

... welche Excelversion hast Du im Einsatz? Und kannst Du eine Datei einstellen, die ein paar mehr Daten hat? Ich bin zwar auch erst PQ-Lernender, aber ich könnte mich dann ja mal daran versuchen. PQ-Profi ist Günther der dann sicherlich aushelfen könnte.

Gruß Werner
.. , - ...


  

Betrifft: AW: nachgefragt und hingewiesen ... von: Daniel
Geschrieben am: 10.07.2018 16:08:51

Excel version ist die 2016er und ja einstellen mehrer dateien sollte möglich sein
Wäre echt cool wenn du dich mal daran versuchen könntest
Gruß Daniel


  

Betrifft: AW: meine 2 Frage ist noch offen owT von: neopa C
Geschrieben am: 10.07.2018 16:20:07

Gruß Werner
.. , - ...


  

Betrifft: AW: meine 2 Frage ist noch offen owT von: Daniel
Geschrieben am: 10.07.2018 16:23:24

Entschuldige leider weiß ich nicht was owT ist
Gruß Daniel


  

Betrifft: AW: zu Deiner Rückfrage ... von: neopa C
Geschrieben am: 10.07.2018 17:36:23

Hallo Daniel

... owT wird in der Betreffzeile eingesetzt, weil: "owT" = "ohne weiteren Text" so darauf hingewiesen wird, dass im eigentlichen Textteil nichts weiter steht und angenommen wird, dass der (Kurz)Text in der Betreffzeile ausreichend ist bzw. sein sollte.

Gruß Werner
.. , - ...


  

Betrifft: AW: zu Deiner Rückfrage ... von: Daniel
Geschrieben am: 10.07.2018 17:51:30

Achso sorry für die dumme Frage

hier ist eine datei mit mehr daten

https://www.herber.de/bbs/user/122600.xlsx

Gruß Daniel


  

Betrifft: AW: vorab ... von: neopa C
Geschrieben am: 10.07.2018 19:32:08

Hallo Daniel,

... eine derartige Liste hätte ich mir auch zusammenstellen können.

Ich schau es mir morgen an.

Gruß Werner
.. , - ...


  

Betrifft: AW: vorab ... von: Daniel
Geschrieben am: 10.07.2018 19:50:13

Achso ok danke
Grus Daniel


  

Betrifft: AW: nachgefragt und hingewiesen ... von: Günther
Geschrieben am: 10.07.2018 22:36:33

Moin @ all,
ja das kann er (und wird es auch tun, falls erforderlich). Aber es ist schon eine kleine Herausforderung ... ;-). Es ist durchaus auch so machbar, wie ab Zeile 3³ vorgegeben. Mit allen "kleinen Gemeinheiten" (aus der Sicht der Darstellung). - Was mein jetziger, vorläufiger Stand ist: Nur die Monate mit Verkäufen, nur die Monate mit Verkäufen und die Produkt-Sortierung alphanumerisch (1, 10, 11, .. 2, 4, ..), Monatsnamen in MMMM-Schreibweise.



Hoffentlich ist das weitgehend dem Wunsch entsprechend.


Und ohne Ironie: Für jeden, der schon ein Basiswissen in PQ hat und ein gewisses Maß an verbissener Energie zur Lösung komplexer Probleme (wie beispielsweise Werner), ist das eine schöne Aufgabe. Und natürlich führen wieder viele Wege zur Lösung, wie so oft im Excel-Umfeld. Also: Bei Bedarf einfach nachfragen, ich helfe weiter, so weit ich es kann und liefere natürlich auf Nachfrage auch meine Lösung (gerne per e-Mail, damit sich auch andere PQ-Novizen die Zähne daran ausbeißen können). Und noch ein Trost: Ich brauchte 3 Anläufe.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@ Daniel:
Darf ich deine "Vorlage" so wie sie ist (oder auch ein weiteres von dir erstelltes Muster) in meinem Blog verwenden und du verzichtest dauerhaft auf Widersprüche? - Ich will keine Gefahr laufe, von irgend so einem Abmahn-Widerling angeschrieben zu werden und die abartige DSGVO öffnet den Herrschaften ja Tür und Tor...

Gruß
Günther


  

Betrifft: AW: nachgefragt und hingewiesen ... von: Daniel
Geschrieben am: 10.07.2018 23:31:45

Hallo Günter
Ja das ist eine gute Lösung könntest du mir vllt deine Lösungsansatz erläutern.
Und klar darfst du die Datei/en in deinem Block verwenden ich berufen keinerlei amsprüche darauf
Gruß Daniel


  

Betrifft: AW: nachgefragt und hingewiesen ... von: Günther
Geschrieben am: 11.07.2018 00:45:36

Moin Daniel,
ich möchte ja auch den anderen PQ-Forschern die Chance lassen, zu solch einem Ergebnis zu kommen. Ein Erfolgserlebnis bei neuen Aufgaben streichelt doch irgendwo die Seele. :-) Und/aber die Versuchung ist einfach zu groß, wenn ich die Lösung -auch als Stichwort-Sammlung- hier einstelle. Darum mein Vorschlag: Schicke mir deine Mail-Adresse an: G.Mumme@Excel-ist-sexy.de und du bekommst erst einmal die Schritte per Mail von mir. Und wenn es dann hakt, helfe ich weiter. - Das gilt übrigens auch für Mit-Leser. Und ich sammele keine Mailadressen, denn ich hasse Spammer aller Art. ;-)

Und ich werde natürlich auch den Link aus meinem Blog hier veröffentlichen, der sich mit diesem Beitrag beschäftigt. Das bin ich dem Forum und seinen Teilnehmern schuldig.

Gruß
Günther


  

Betrifft: AW: (m)eine Lösung mittels PQ + Pivot ... von: neopa C
Geschrieben am: 11.07.2018 16:57:15

Hallo Günther,

... konnte ich erzeugen, so wie ich es mir vorgestellt hatte :-)

Meine Schritte dazu:

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type text}, {"Spalte3", type text}, {"Spalte4", type text}, {"Spalte5", type text}, {"Spalte6", type text}}),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ", {"Spalte1"}, "Attribut", "Wert"),
#"Duplizierte Spalte" = Table.DuplicateColumn(#"Entpivotierte andere Spalten", "Wert", "Wert - Kopie"),
#"Der Text nach dem Trennzeichen wurde extrahiert." = Table.TransformColumns(#"Duplizierte Spalte", {{"Wert - Kopie", each Text.AfterDelimiter(_, "/"), type text}}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Der Text nach dem Trennzeichen wurde extrahiert.",{{"Wert - Kopie", Int64.Type}}),
#"Der Text vor dem Trennzeichen wurde extrahiert." = Table.TransformColumns(#"Geänderter Typ1", {{"Wert", each Text.BeforeDelimiter(_, "/"), type text}}),
#"Geänderter Typ2" = Table.TransformColumnTypes(#"Der Text vor dem Trennzeichen wurde extrahiert.",{{"Wert", type date}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ2",{{"Wert", "Datum"}, {"Wert - Kopie", "Stück"}}),
#"Entfernte Spalten" = Table.RemoveColumns(#"Umbenannte Spalten",{"Attribut"}),
#"Umbenannte Spalten1" = Table.RenameColumns(#"Entfernte Spalten",{{"Spalte1", "Produkte"}})
in
#"Umbenannte Spalten1"

Dies hab ich dann in eine neues Tabellenblatt gespeichert und dafür dann eine Pivotauswertung vorgenommen (welches inhaltlich identische Ergebniswerte ermittelt aber layoutmäßig natürlich etwas anders aussieht. Dafür kann man diese Ergebnisauswertung nachträglich schnell und flexibler ändern und wenn man anstelle Pivottabelle ein PivotChart erstellen lässt, hat man auch gleich noch eine bessere Visualisierung des Ergebnisses.

Eine Tabellen-Lösung ausschließlich mittels PQ, wie Du es nach Deinem eingestellten Bild zu urteilen wohl erzeugt hast, wüsste ich nicht wie :-( Da bin ich nun gespannt auf Deinen Link.

Allerdings hab ich eine alternative Formellösung, die eine 1:1 Ausgabe (zu Deinem Ergebnisbild) erzeugt. Dies mittels lediglich 3 Matrixfunktion(alität)sformeln, die keines spez. Formelabschluss wie klassische Matrixformeln benötigen. Erstellt mit Beispieldaten, die ich teils übernommen und teils abgeändert und ergänzt habe. Die Formellösung setzt in dieser Form jedoch momentan voraus, dass wirklich immer einheitliche korrekte Datenerfassung im Format: TT.MM.JJJJ/### vorliegt, welches bei der PQ-Lösung nur teilweise berücksichtigt werden muss. Da muss ich mir keine Gedanken machen wegen evtl. Eingaben in folgenden Format: 2.4.18/07 unproblematisch aber natürlich schon bei Eingaben wie 30.02.2019. Aber beides wäre natürlich sowohl mit Formeln und auch PQ zu berücksichtigen.

Gruß Werner
.. , - ...


  

Betrifft: AW: hierzu heute ... von: neopa C
Geschrieben am: 12.07.2018 11:26:56

Hallo,

... hab noch einen etwas kürzeren "Weg" gefunden und die PQ-Ergebnisliste nicht ausgegeben sondern gleich mittels Pivot ausgewertet:

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle8"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type text}, {"Spalte3", type text}, {"Spalte4", type text}, {"Spalte5", type text}, {"Spalte6", type text}}),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ", {"Spalte1"}, "Attribut", "Wert"),
#"Umbenannte Spalten" = Table.RenameColumns(#"Entpivotierte andere Spalten",{{"Spalte1", "Produkte"}}),
#"Entfernte Spalten" = Table.RemoveColumns(#"Umbenannte Spalten",{"Attribut"}),
#"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entfernte Spalten", "Wert", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Wert.1", "Wert.2"}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Spalte nach Trennzeichen teilen",{{"Wert.1", type date}, {"Wert.2", Int64.Type}}),
#"Umbenannte Spalten1" = Table.RenameColumns(#"Geänderter Typ1",{{"Wert.1", "Datum"}, {"Wert.2", "Stück"}})
in
#"Umbenannte Spalten1"

Offen ist für mich trotzdem noch, wie Günther zu seiner Ergebnisdarstellung gekommen ist.

Gruß Werner
.. , - ...


  

Betrifft: ...meine Lösung... von: robert
Geschrieben am: 12.07.2018 19:03:45

https://www.herber.de/bbs/user/122641.xlsx


  

Betrifft: AW: hierzu ... von: neopa C
Geschrieben am: 13.07.2018 21:58:29

Hallo Robert.

... dank Günthers Hilfe konnte ich seinen Weg über das "Pivotieren" heute Mittag auch nachstellen.

Dein Lösungsansatz hat dem gegenüber den Vorteil, dass bei gänzlich neuen Monatsdaten auch in bisherigen Daten-Leerspalten, die Stückzahlen schon auch addiert werden. Jedoch besteht aber noch ein Sortierungsproblem der pivotierten Monate. Ganz davon abgesehen, dass man bei Daten aus verschiedenen Jahren noch ein paar Klimmzüge mehr machen muss.

Ich hab mich jetzt eine Weile damit beschäftigt, finde aber das die PQ-Pivotierungsfunktion entweder von mir noch nicht beherrscht wird oder diese noch nicht wirklich ausgereift ist. Oder hast Du dafür einen anderen Lösungsweg?

Deswegen würde ich ja auf diese PQ-Pivotieren verzichten und anstelle dessen meinen gestern bereits beschrieben Lösungsweg nutzen, d.h. ohne Gruppierung und Pivotisierung in PQ und die Daten beim Schließen als Pivot / PivotChart ausgeben. Damit wird zwar nicht das Vorgabelayout erzeugt, aber ich muss weniger Aufwand betreiben und bin flexibler in der Auswertung.

Allerdings gibt es diese Funktionalität offensichtlich nur in Office365 (nicht in Excel2016).

Gruß Werner
.. , - ...


  

Betrifft: AW: der Vollständigkeit halber ... von: neopa C
Geschrieben am: 11.07.2018 17:42:49

Hallo Günther, hallo Daniel,

... hier noch meine Beispieldaten und meine dazugehöriger reine Formellösung (dazu siehe auch meine Anmerkungen und Hinweise) in meinem darüber stehenden Beitrag.

Formel AA3 nach unten, Formel AB2 nach rechts und Formel AB3 sowohl nach rechts und unten ziehend kopieren. Zahlenformat in AB2:XX2 ist "MMM.JJ"

 ABCDEFGHZAAABACADAEAFAGAHAIAJAK
2ProdukteDatum/Stückzahl       ProdukteAnz. gesFeb.17Mai.17Jun.17#####Jan.18Feb.18Apr.18Mai.18 
3Produkt107.02.2017/0712.06.2017/0524.09.2017/06     Produkt1187 56     
4Produkt224.09.2017/06  17.01.2018/0917.04.2018/05   Produkt220   69 5  
5Produkt317.02.2017/11  17.05.2018/03    Produkt31411      3 
6Produkt420.05.2017/0511.09.2017/0623.09.2017/0627.09.2017/0629.09.2017/06   Produkt429 5 24     
7Produkt502.09.2017/06       Produkt56   6     
8Produkt6        Produkt711   11     
9Produkt704.09.2017/0524.09.2017/06      Produkt88   8     
10Produkt814.09.2017/08       Produkt1019 7 12     
11Produkt9        Produkt116   6     
12Produkt1012.05.2017/0704.09.2017/0624.09.2017/06     Produkt1312   12     
13Produkt1102.09.2017/06       Produkt146   6     
14Produkt12        Produkt1618   18     
15Produkt1303.09.2017/0629.09.2017/06      Produkt176     6   
16Produkt1407.09.2017/06                  
17Produkt15                   
18Produkt1624.09.2017/0624.09.2017/0624.09.2017/06                
19Produkt1724.02.2018/06                  
20Produkt18                   
21Produkt19                   
22                    

Formeln der Tabelle
ZelleFormel
AC2=WENNFEHLER(TEXT(AGGREGAT(15;6;LINKS($B3:$Z$26;SUCHEN("/";$B$3:$Z$26;1)-1)/(TEXT(LINKS($B$3:$Z$26;SUCHEN("/";$B$3:$Z$26;1)-1); "M.J")*1>WENN(SPALTE(A1)=1;0;AB$2)); 1); "M.J")+0;"")
AA3=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$3:A$26)/(B$3:Z$26>0)/(ZÄHLENWENN(AA$2:AA2;A$3:A$26)=0); 1)); "")
AB3=WENN(($AA3="")+(AB$2=""); "";WENN(ISTTEXT(AB$2); SUMME($AC3:AY3); SUMME(INDEX(($A$3:$A$26=$AA3)*(TEIL($B$3:$Z$26;4;7)=TEXT(AB$2;"MM.JJJJ"))*(0&TEIL($B$3:$Z$26;12;9)); ))))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: Datum/Stückzahl auswerten von: Günther
Geschrieben am: 10.07.2018 10:52:07

Moin,
ich werfe auch einmal die Power Query-Lösung in den Ring. Aber ohne Muster-xlsx ...

Gruß
Günther


Beiträge aus dem Excel-Forum zum Thema "Datum/Stückzahl auswerten"