Zugegeben, ein sper­riger Titel. Darum hier mit eini­gen Worten mehr die Auf­gaben­stel­lung, die Prob­lematik: Für fünf Pro­duk­t­grup­pen soll aus­gew­ertet wer­den, wie hoch der Umsatz mit der betrof­fe­nen Pro­duk­t­gruppe am let­zten Umsatz-Tag dieser Gruppe war. Betra­cht­en Sie das ein­fach ein­mal unter dem Gesicht­spunkt, dass nicht an jedem Tag mit jed­er Pro­duk­t­gruppe Umsätze getätigt wer­den. Es ist aber auch denkbar, dass an einem Tag mehrere einzelne Umsätze zu ein­er Pro­duk­t­gruppe getätigt und auch jew­eils einzeln in ein­er Daten­zeile erfasst wer­den. In unser­er Beispiel­d­atei wäre der 24. Feb­ru­ar 2015 bei der Pro­duk­t­gruppe 1 solch ein Fall. Da muss dann natür­lich der Gesam­tum­satz zählen.

Das Ziel ist es nun, aus ein­er Musterta­belle mit 200 Daten­sätzen im Zeitraum von 2 Jahren für jede Pro­duk­t­gruppe den let­zten Verkauf­stag und den an dem Tag getätigten Umsatz in 5 Pro­duk­t­grup­pen-Zeilen (plus Über­schrift) darzustellen. Das hört sich vielle­icht recht sim­pel an, aber ver­suchen Sie ein­mal ohne die fol­gende oder fremde Hil­fe, das Prob­lem zu lösen. Für ein­fache Vorschläge sind wir immer offen!  😉 

▲ nach oben …

Mit „normalen” Tabellen-Mitteln

Falls Sie noch nicht unsere Musterta­belle geladen haben, tun Sie es bitte jet­zt. Sie ist eine wichtige Grund­lage unser­er Lösungswege. Sie erken­nen eine Auflis­tung von 200 Zeilen und ein­er Über­schrift. Die kalen­darischen Dat­en und auch die Pro­duk­t­grup­pen sind her­rlich durcheinan­der gewür­felt. Das ist dem Umstand geschuldet, dass alle 600 Werte dieser drei Spal­ten per Zufalls­funk­tion erzeugt wor­den sind. So ist aber auch bess­er erkennbar, wo welche Stärken der einzel­nen Vorge­hensweisen liegen. Darum ist diese Un-Ord­nung auch gewollt.

Bevor Sie begin­nen, soll­ten Sie erst ein­mal das (einzig sicht­bare) Arbeits­blatt kopieren und dann mit dieser Kopie weit­er arbeit­en. So haben Sie immer noch das Orig­i­nal für weit­ere Vorge­hensweisen oder Kopi­en zur Ver­fü­gung. – Gesagt, getan. Im ersten Schritt wer­den Sie die Dat­en sortieren. Und zwar zuerst nach der Pro­duk­t­gruppe. Und inner­halb dieser nach dem Datum. Bei­des natür­lich auf­steigend.

Dem The­men-Mot­to entsprechend gibt es (natür­lich) auch hier mehrere Wege, die zum Ziel führen. Und prinzip­iell ist es Ihrem Geschmack und Ihrer Gewohn­heit über­lassen, wie Sie zum Ziel kom­men.

▲ nach oben …

Sortieren I

Auch hier soll­ten Sie vielle­icht zu Beginn eine Kopie des Tabel­len­blattes anle­gen und darauf arbeit­en, dann kön­nen Sie den Vor­gang später mit den anderen Optio­nen wieder­holen und mit der unge­ord­neten Liste starten.

Sie arbeit­en sich in umgekehrter Rang­folge der Sortierung voran. Das bedeutet, Sie sortieren zuerst das Datum in auf­steigen­der Rei­hen­folge und dann die Pro­duk­t­gruppe. Das geht recht ein­fach, indem Sie jew­eils in die Spalte Klick­en und dann in der Rib­bon-Leiste (Menü-Band) auf die Sortieren-Schalt­fläche:

Sortieren-Schalt­fläche im Start-Menü

Der Erfolg ist rasch gegeben. Falls Sie darüber „stolpern”, dass die Rei­hen­folge der Sortierung so ist und nicht anders: Mit jed­er erneuten Sortierung wird ja ein ander­er Auf­bau gener­iert. Erst wer­den die kalen­darischen Dat­en sortiert, dann sind aber die Pro­duk­t­grup­pen immer noch durcheinan­der. Wenn ich nun die Pro­duk­t­grup­pen sortiere, dann wird ja die ursprüngliche Sortierung der kalen­darischen Dat­en passend zu der jew­eilige Zeile mitgenom­men.

▲ nach oben …

Sortieren II

Die gold­ene Mitte, mein per­sön­lich­er Favorit! Und das aus ver­schiede­nen Grün­den. Ins­beson­dere weil ich mit der Liste/Intel­li­gen­ten Tabelle ein aus­ge­sprochen hil­fre­ich­es „Schweiz­er Taschen­mess­er” in Sachen Tabel­len­han­dling in der Hand habe. Zur Vor­bere­itung Klick­en Sie erst ein­mal irgend­wo in die Dat­en und StrgL oder StrgT. Alter­na­tiv kön­nen Sie natür­lich auch den Weg über die Sym­bol­leiste gehen und im Menü Start das Icon Als Tabelle for­matieren anklick­en.

Damit ändert sich (meis­tens) das Ausse­hen der Dat­en. In der Über­schriftzeile Klick­en Sie nun auf das Erweit­ern-Sym­bol ▼ in Spalte A und im Menü ist auch klar erkennbar, dass die Datum-Spalte bear­beit­et wer­den soll. Ganz oben ein Klick auf die Möglichkeit der auf­steigen­den Sortierung und gut ist es. Sofort ist auch in der Aufk­lapp-Schalt­fläche durch den zusät­zlichen Pfeil erkennbar, dass die Dat­en hier auf­steigend sortiert vor­liegen. – Gle­ich danach noch ein­mal das prinzip­iell iden­tis­che Vorge­hen bei der Pro­duk­t­gruppe und das Ziel ist erre­icht. Ach ja, dass jet­zt nur noch diese Spalte in der Über­schrift als sortiert gekennze­ich­net wird ist nor­mal und stimmt prinzip­iell auch, denn Spalte A ist ja nicht mehr durchgängig auf­steigend sortiert.

▲ nach oben …

Sortieren III

Wiederum aus­ge­hend von der Ursprungs­datei Klick­en Sie irgend­wo in die Dat­en. Im Menü Dat­en, Gruppe Sortieren und Fil­tern auf die große Schalt­fläche Sortieren Klick­en und es tut sich dieses Fen­ster auf:

Die klas­sis­che Sortier­meth­ode

Hier ist fast alles anders als in den vorheri­gen Abläufen. Sie wählen bei Sortieren nach an erster Stelle die Haupt-Pri­or­ität, also die Pro­duk­t­gruppe. Die bei­den weit­eren Auswahlen (Sortieren nach und Rei­hen­folge) belassen Sie so, wie vorgegeben. Danach ein Klick auf die Schalt­fläche Ebene Hinzufü­gen und in der zweit­en Zeile wählen Sie das Datum und belassen es auch hier bei den Vor­gaben:

Bei­de Sortierun­gen sind aus­gewählt

Auch wenn beim Datum etwas von absteigend ver­merkt ist, das stimmt so, weil es sich auf das Alter bezieht. Wie gesagt, vieles ist anders als vorher. Nach einem OK ist auch hier die Sortierung so, wie sie sein soll.

▲ nach oben …

Letzte Vorbereitungen

Jet­zt sind einige weit­ere Vor­bere­itun­gen erforder­lich. Die Dat­en ste­hen zwar schon schön geord­net in der Liste und bei weni­gen Pro­duk­t­grup­pen wäre es dur­chaus denkbar, dass jew­eils die let­zte Zeile der Pro­duk­t­gruppe von Hand gesucht, dann kopiert und in einen Ziel­bere­ich einge­fügt wird. Aber das ist nun wirk­lich nicht der Hit, wenn es mehr als 10 oder 20 Grup­pen wären. Da kann Excel uns doch helfen.  😆 

Um nicht jedes Mal die eingegebene Formel nach ganz unten kopieren zu müssen, wan­deln Sie die Dat­en ein­fach per StrgT in eine Intel­li­gente Tabelle um. Schreiben Sie nun in D1 die Über­schrift Tages­summe. Und wenn Sie schon ein­mal dabei sind, in E1 Fil­terKrit. In D3 (nicht D2!) geben Sie nun diese Formel ein: 
=WENN([@Datum]=B2;C2+D2;[@Umsatz])
Das Ganze scheint ver­wirrend, aber wenn Sie das so erledi­gen wie ich es mache, dann wird das recht easy und logisch. Immer noch in D3: Nach dem Gle­ich­heit­sze­ichen schreiben Sie das WENN(, dann Klick­en Sie in A3. Jet­zt ergänzen Sie =B2;C2+ und Klick­en dann in D2. Damit ist der Teil der WENN-Funk­tion for­muliert, welch­er den zutr­e­f­fend­en Fall beschreibt. Also: Wenn in Datum der gle­ich Wert ste­ht wie in der Zelle darüber, dann ist das Funk­tion­sergeb­nis die Summe des aktuellen Umsatzes und der bish­eri­gen Tages­summe. Bleibt noch die Möglichkeit, dass der darüber ste­hende Wert in der Datum-Spalte nicht gle­ich ist, dann entspricht die Tages­summe natür­lich dem in Spalte C ste­hen­den Umsatz. Dazu Klick­en Sie in C3 und fügen dann noch die schließende Klam­mer ein. – Natür­lich haben Sie längst erkan­nt, dass die Intel­li­gente Tabelle dafür sorgt, dass automa­tisch alle Formeln und alle Zeilen der Liste nach oben und nach unten aufge­füllt wer­den.

Bleibt nur noch die Spalte E mit den Fil­terkri­te­rien. Hier soll ver­glichen wer­den, ob in der Fol­gezeile eine andere Pro­duk­t­gruppe ste­ht wie in der aktuellen Zeile. Und das ist ja genau dann nicht der Fall, wenn ein Wech­sel der Pro­duk­t­gruppe stat­tfind­et, weil die Sortierung die Pro­duk­t­grup­pen zusam­men hält. Und da inner­halb der Gruppe ja nach Datum sortiert wurde, ist das auch der let­zte Tag mit Umsatz dieser Gruppe. Geben Sie also in E2 erst ein­mal das Gle­ich­heit­sze­ichen ein, Klick­en dann auf A2 und schreiben dann <>A3. Mit anderen Worten: Sie behaupten, dass in der Fol­gezeile der Spalte A ein ander­er Wert ste­ht als in der aktuellen Zeile. Die ganze Formel sieht nun so aus: 
=[@ProduktGruppe]<>A3
und es wird ein Wahrheitswert zurück gegeben, ob diese Zeile die let­zte ein­er Pro­duk­t­gruppe ist. Sie merken gewiss, dass das genau das ist, was Sie zur Bewäl­ti­gung der Auf­gabe brauchen. Auss­chließlich jede let­zte Zeile ein­er Gruppe enthält hier ein WAHR. 

▲ nach oben …

Daten filtern

Der let­zte Schritt ist der am wenig­sten aufwendi­ge. Ein Klick in E1 auf das Erweit­ern-Sym­bol ▼ und set­zen Sie den Fil­ter auf WAHR. Ruck zuck wer­den nur die gewün­scht­en Zeilen her­aus gefiltert und angezeigt. Entwed­er belassen Sie es dabei oder Sie kopieren den gefilterten Bere­ich und fügen ihn an ein­er vorge­se­henen Stelle ein. Der Optik wegen kön­nen Sie natür­lich auch die Spalte E aus­blenden, klar …  😎 Um wieder alle Dat­en zu sehen, ent­fer­nen Sie ein­fach den Fil­ter.

▲ nach oben …

Lösungsweg PivotTable

Piv­ot-Tabellen sind ein anspruchsvolles aber auch mächtiges Instru­ment zur Auswer­tung von Dat­en. Und wenn die Basis­dat­en in ein­er „wohl geord­neten Form” vor­liegen, macht es richtig Spaß, mit unter­schiedlichen Darstel­lun­gen regel­recht zu „spie­len”. In dieser Anleitung set­zen wir einige Grund­ken­nt­nisse oder etwas Erfahrung in Sachen Piv­ot voraus, darum ist das Ganze hier eher stich­wor­tar­tig mit weni­gen zusät­zlichen Aus­führun­gen aufge­führt.

Natür­lich ist wiederum die Musterta­belle die Basis für Ihre Auswer­tung. Entwed­er nutzen Sie eine Kopie der Roh-Dat­en oder Sie laden sich noch ein­mal die Muster-Datei herunter. StrgPos1 (dann ist die aktive Zelle garantiert im Daten­bere­ich) und erstellen Sie auf eine Ihnen gewohnte Weise eine Piv­ot­Ta­belle. Ich erstelle aus den Roh-Dat­en erst eine Intel­li­gente Tabelle, befinde mich dadurch automa­tisch im Entwurf der Tabel­len­tools und dort in der Gruppe Tools die Auswahl Mit Piv­ot­Table zusam­men­fassen. Sie kön­nen die Piv­ot-Auswer­tung auf dem gle­ichen oder einem getren­nten Blatt vornehmen, das ist neben­säch­lich.

Sortieren brauchen Sie die Dat­en nicht, das übern­immt die Piv­ot­Table für Sie bzw. es bedarf kein­er Sortierung der Grund-Dat­en. Ab hier gel­ten alle Hin­weise auss­chließlich der Piv­ot-Tabelle (PT). Und hal­ten Sie sich bitte exakt an die Anweisun­gen, damit das Vorhaben auch klappt  😉 .

• Als erstes ziehen Sie das Feld Pro­duk­t­Gruppe in den Bere­ich Zeilen. 
• Anschließend ver­fahren Sie mit dem Feld Datum gle­icher­maßen und posi­tion­ieren es unter­halb der Pro­duk­t­Grup­pen.
• Nutzen Sie nun noch ein­mal das Feld Datum und ziehen es aber dieses Mal in den Bere­ich Werte.
• Klick­en Sie auf den Ein­trag in Werte und dann auf Wert­felde­in­stel­lun­gen… 
• Wech­seln Sie in Wert­feld zusam­men­fassen nach die Markierung von Auswahl nach Max­i­mum. Anschließend OK.
• Ziehen Sie nun das Feld Umsatz in den Bere­ich Werte unter­halb des Datums.
• Acht­en Sie darauf, dass hier als Zusam­men­fas­sung die Summe angegeben ist. Erforder­lichen­falls ändern Sie das.

Obwohl ja das Datum mit der Zusam­men­fas­sung Max­i­mum markiert wurde, sind noch alle kalen­darischen Dat­en und natür­lich die dazu gehöri­gen Umsätze in der PT sicht­bar. Und die Spalte Max­i­mum von Datum ist auch nicht als Datum for­matiert. Wenn Sie das „vernün­ftig” ändern wollen, dann ändern Sie das über die Felde­in­stel­lun­gen. Unten links ist die Schalt­fläche Zahlen­for­mat und dort wer­den Sie rasch fündig. Sie wer­den aber fest­stellen, dass die Kor­rek­tur keinen Ein­fluss auf die dargestellte Daten­menge hat.

• Schal­ten Sie nun die Anzeige der Teil- und der Gesamtergeb­nisse aus; der Weg geht über die Piv­ot­Table-Tools, Menü Entwurf, Gruppe Lay­out. 
• Ändern Sie in der gle­ichen Gruppe das Bericht­slay­out auf das Tabel­len­for­mat.

Der näch­ste Schritt ist der entschei­dende, gehört aber nicht zu den viel genutzten Vorge­hensweisen in diesem Bere­ich. Das Ziel ist ja, nur den zeitlich let­zten Tag und Umsatz jed­er Pro­duk­t­gruppe her­aus zu fil­tern. Dazu ein Recht­sklick in ein beliebiges Feld ser Spalte Datum, dort Fil­ter und Top 10… durch Klick wählen:

Top 10 Werte ein­er Spalte fil­tern

 Im fol­gen­den Fen­ster ändern Sie die Anzahl der Ele­mente von der Vor­gabe der Ober­sten 10 auf 1. Den Ein­trag im Feld nach belassen Sie bei der Vor­gabe Max­i­mum von Datum. Nach einem OK schrumpft die extrem lange Liste auf die Über­schrift und jew­eils eine Zeile jed­er Pro­duk­t­gruppe mit den kalen­darischen Dat­en und den Umsätzen des let­zten Umsatz­tages. – Die Spalte Max­i­mum von Datum dür­fen Sie gerne ver­steck­en aber nicht löschen.

Der riesige Vorteil der Piv­ot­Table ist, dass sie mit weni­gen Mausklicks auf neue, verän­derte oder erweit­erte Basis­dat­en anpass­bar ist und natür­lich auch beispiel­sweise die let­zten 3 Umsätze mit min­i­mal­stem Aufwand angezeigt wer­den kön­nen. Dazu kommt, dass die vorhan­de­nen Dat­en natür­lich auch noch anders, weit­erge­hen­der aus­gew­ertet wer­den kön­nen.

Ach ja, eine Kleinigkeit noch: Wenn Sie meine/unsere Ergeb­nisse sehen wollen, dann blenden Sie ein­fach die ver­steck­ten Blät­ter ein. Dazu reicht ein Recht­sklick auf eines der Blatt-Reg­is­ter und Ein­blenden… wählen.

Schnelle Alternative: Neues Tabellenblatt ohne Filter

Es gibt auch eine Möglichkeit, eine neue Tabelle als Kopie zu erstellen und diese dann so zu behan­deln, dass neben der Über­schrift auss­chließlich die fünf Zeilen mit den entsprechen­den Dat­en enthal­ten sind. „Zu Fuß” oder „Automatik”, das ist hier die Frage …  😉 

Klassisch per Hand

Mit kleinen Aus­nah­men ist diese Meth­ode der ersten sehr ähn­lich. Sie soll­ten darum die dort aus­ge­führte Vorge­hensweise ken­nen und ver­standen haben. – Zu Beginn gle­ich die erste Änderung: Sie kopieren in jedem Fall die Roh-Dat­en, denn es bleiben am Ende wirk­lich nur noch die entsprechen­den fünf Pro­duk­t­grup­pen übrig; alle anderen Zeilen sind unwieder­bringlich gelöscht. Darum brauchen Sie auch noch für den Fall des Fall­es (Änderun­gen, Ergänzun­gen, …) die Orig­i­nal-Dat­en.

Alles ab hier aufge­führte bezieht dich auss­chließlich auf diese Arbeit­skopie.

• Falls Sie mögen, for­matieren Sie die Dat­en als Tabelle
• Sortieren Sie nach dem Datum, aber dieses mal absteigend
• Sortieren Sie nun nach Pro­duk­t­grup­pen, wie gehabt aufsteigend
• Menü Dat­en, Gruppe Daten­tools und hier Dup­likate ent­fer­nen auswählen
• Nur die Spalte Pro­duk­t­Gruppe ange­hakt lassen
• OK und freuen.

Diese Dat­en sind sta­tisch. Das bedeutet, dass Sie bei verän­derten Basis­dat­en den erstell­ten Datenex­trakt löschen müssen und begin­nend mit dem kopieren der Quell­dat­en alles noch ein­mal durch­führen müssen.

Mehrfache Auswertungen: Makro!

Wenn die reine Auswer­tung wie im Beispiel „Klas­sisch per Hand” direkt hierüber akzept­abel oder sog­ar wün­schenswert ist und der Vor­gang wieder­holt durchge­führt wer­den soll, dann bietet sich eine VBA-Lösung an. Hier erst ein­mal der Code:

Option Explicit

Sub LetzterUmsatz()
   Const NeuBlattName As String = "Auswertung"
   Const tblDataName As String = "tbl_Data"
   Dim lRow As Long, lCol As Integer
   Dim wks As WorkSheet, rngData As Range
   Dim SpDatum As String, SpPrGrp As String
   
   On Error GoTo ErrorHandler
   Sheets.Add After:=ThisWorkbook.Sheets(Sheets.Count)
   For Each wks In ThisWorkbook.Sheets
      If wks.Name = NeuBlattName Then
         Application.DisplayAlerts = False
         wks.Delete
         Application.DisplayAlerts = True
      End If
   Next wks
   ActiveSheet.Name = NeuBlattName
   With Sheets("Basis-Daten")
      lRow = .Cells(Rows.Count, 1).End(xlUp).Row
      lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
      .Range(.Cells(1, 1), .Cells(lRow, lCol)).Copy Sheets(NeuBlattName).Range("A1")
   End With
    
   With Sheets(NeuBlattName)
      Set rngData = .Range(.Cells(1, 1), .Cells(lRow, lCol))
      SpDatum = tblDataName & "[[#All],[Datum]]"
      SpPrGrp = tblDataName & "[[#All],[ProduktGruppe]]"
      .ListObjects.Add(xlSrcRange, rngData, , xlYes).Name = tblDataName
      
      '--- Datum sortieren ----------------------------------
      .ListObjects(tblDataName).Sort.SortFields.Clear
      .ListObjects(tblDataName).Sort.SortFields.Add _
         Key:=Range(SpDatum), _
         SortOn:=xlSortOnValues, _
         Order:=xlDescending, DataOption:=xlSortTextAsNumbers

      With .ListObjects(tblDataName).Sort
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
      '--- Produktgruppe sortieren --------------------------
      .ListObjects(tblDataName).Sort.SortFields.Clear
      .ListObjects(tblDataName).Sort.SortFields _
         .Add Key:=Range(SpPrGrp), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortTextAsNumbers
      With .ListObjects(tblDataName).Sort
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
      'Nur Unikate bei der Produktgruppe
      .Range(tblDataName).RemoveDuplicates Columns:=2, Header:=xlYes
   End With
   
ErrorHandler:
   If Err.Number <> 0 Then MsgBox "Fehler Nr.: " & Err.Number & vbCrLf & Err.Description
   Application.DisplayAlerts = True
End Sub

Dieser Code gehört in das Mod­ul DieseAr­beitsmappe oder nach Wahl in ein All­ge­meines Mod­ul. Wie Sie das ein­binden kön­nen Sie bei Bedarf hier im Blog nach­le­sen. Zugegeben, das Ganze ließe sich auch kom­plett mit VBA-Arrays lösen, aber diese Lösung ist bess­er nachvol­lziehbar. – Diese bei­den Lösun­gen liegen nicht als Datei zum Down­load bere­it, wir senden sie Ihnen bei Bedarf aber gerne als E‑Mail-Anhang zu.