Letzter Tagesumsatz einer Produktgruppe

  Es führen viele Wege nach Rom …  

Wann wurde welcher Umsatz letztmalig mit einer Produktgruppe getätigt?

Zugegeben, ein sperriger Titel. Darum hier mit einigen Worten mehr die Aufgabenstellung, die Problematik: Für fünf Produktgruppen soll ausgewertet werden, wie hoch der Umsatz mit der betroffenen Produktgruppe am letzten Umsatz-Tag dieser Gruppe war. Betrachten Sie das einfach einmal unter dem Gesichtspunkt, dass nicht an jedem Tag mit jeder Produktgruppe Umsätze getätigt werden. Es ist aber auch denkbar, dass an einem Tag mehrere einzelne Umsätze zu einer Produktgruppe getätigt und auch jeweils einzeln in einer Datenzeile erfasst werden. In unserer Beispieldatei wäre der 24. Februar 2015 bei der Produktgruppe 1 solch ein Fall. Da muss dann natürlich der Gesamtumsatz zählen.

Das Ziel ist es nun, aus einer Mustertabelle mit 200 Datensätzen im Zeitraum von 2 Jahren für jede Produktgruppe den letzten Verkaufstag und den an dem Tag getätigten Umsatz in 5 Produktgruppen-Zeilen (plus Überschrift) darzustellen. Das hört sich vielleicht recht simpel an, aber versuchen Sie einmal ohne die folgende oder fremde Hilfe, das Problem zu lösen. Für einfache Vorschläge sind wir immer offen!  😉 

▲ nach oben …

Mit „normalen“ Tabellen-Mitteln

Falls Sie noch nicht unsere Mustertabelle geladen haben, tun Sie es bitte jetzt. Sie ist eine wichtige Grundlage unserer Lösungswege. Sie erkennen eine Auflistung von 200 Zeilen und einer Überschrift. Die kalendarischen Daten und auch die Produktgruppen sind herrlich durcheinander gewürfelt. Das ist dem Umstand geschuldet, dass alle 600 Werte dieser drei Spalten per Zufallsfunktion erzeugt worden sind. So ist aber auch besser erkennbar, wo welche Stärken der einzelnen Vorgehensweisen liegen. Darum ist diese Un-Ordnung auch gewollt.

Bevor Sie beginnen, sollten Sie erst einmal das (einzig sichtbare) Arbeitsblatt kopieren und dann mit dieser Kopie weiter arbeiten. So haben Sie immer noch das Original für weitere Vorgehensweisen oder Kopien zur Verfügung. – Gesagt, getan. Im ersten Schritt werden Sie die Daten sortieren. Und zwar zuerst nach der Produktgruppe. Und innerhalb dieser nach dem Datum. Beides natürlich aufsteigend.

Dem Themen-Motto entsprechend gibt es (natürlich) auch hier mehrere Wege, die zum Ziel führen. Und prinzipiell ist es Ihrem Geschmack und Ihrer Gewohnheit überlassen, wie Sie zum Ziel kommen.

▲ nach oben …

Sortieren I

Auch hier sollten Sie vielleicht zu Beginn eine Kopie des Tabellenblattes anlegen und darauf arbeiten, dann können Sie den Vorgang später mit den anderen Optionen wiederholen und mit der ungeordneten Liste starten.

Sie arbeiten sich in umgekehrter Rangfolge der Sortierung voran. Das bedeutet, Sie sortieren zuerst das Datum in aufsteigender Reihenfolge und dann die Produktgruppe. Das geht recht einfach, indem Sie jeweils in die Spalte klicken und dann in der Ribbon-Leiste (Menü-Band) auf die Sortieren-Schaltfläche:

Sortieren-Schaltfläche im Start-Menü

Sortieren-Schaltfläche im Start-Menü

Der Erfolg ist rasch gegeben. Falls Sie darüber „stolpern“, dass die Reihenfolge der Sortierung so ist und nicht anders: Mit jeder erneuten Sortierung wird ja ein anderer Aufbau generiert. Erst werden die kalendarischen Daten sortiert, dann sind aber die Produktgruppen immer noch durcheinander. Wenn ich nun die Produktgruppen sortiere, dann wird ja die ursprüngliche Sortierung der kalendarischen Daten passend zu der jeweilige Zeile mitgenommen.

▲ nach oben …

Sortieren II

Die goldene Mitte, mein persönlicher Favorit! Und das aus verschiedenen Gründen. Insbesondere weil ich mit der Liste/Intelligenten Tabelle ein ausgesprochen hilfreiches „Schweizer Taschenmesser“ in Sachen Tabellenhandling in der Hand habe. Zur Vorbereitung klicken Sie erst einmal irgendwo in die Daten und StrgL oder StrgT. Alternativ können Sie natürlich auch den Weg über die Symbolleiste gehen und im Menü Start das Icon Als Tabelle formatieren anklicken.

Damit ändert sich (meistens) das Aussehen der Daten. In der Überschriftzeile klicken Sie nun auf das Erweitern-Symbol ▼ in Spalte A und im Menü ist auch klar erkennbar, dass die Datum-Spalte bearbeitet werden soll. Ganz oben ein Klick auf die Möglichkeit der aufsteigenden Sortierung und gut ist es. Sofort ist auch in der Aufklapp-Schaltfläche durch den zusätzlichen Pfeil erkennbar, dass die Daten hier aufsteigend sortiert vorliegen. – Gleich danach noch einmal das prinzipiell identische Vorgehen bei der Produktgruppe und das Ziel ist erreicht. Ach ja, dass jetzt nur noch diese Spalte in der Überschrift als sortiert gekennzeichnet wird ist normal und stimmt prinzipiell auch, denn Spalte A ist ja nicht mehr durchgängig aufsteigend sortiert.

▲ nach oben …

Sortieren III

Wiederum ausgehend von der Ursprungsdatei klicken Sie irgendwo in die Daten. Im Menü Daten, Gruppe Sortieren und Filtern auf die große Schaltfläche Sortieren klicken und es tut sich dieses Fenster auf:

Die klassische Sortiermethode

Die klassische Sortiermethode

Hier ist fast alles anders als in den vorherigen Abläufen. Sie wählen bei Sortieren nach an erster Stelle die Haupt-Priorität, also die Produktgruppe. Die beiden weiteren Auswahlen (Sortieren nach und Reihenfolge) belassen Sie so, wie vorgegeben. Danach ein Klick auf die Schaltfläche Ebene Hinzufügen und in der zweiten Zeile wählen Sie das Datum und belassen es auch hier bei den Vorgaben:

Beide Sortierungen sind ausgewählt

Beide Sortierungen sind ausgewählt

Auch wenn beim Datum etwas von absteigend vermerkt 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

Jetzt sind einige weitere Vorbereitungen erforderlich. Die Daten stehen zwar schon schön geordnet in der Liste und bei wenigen Produktgruppen wäre es durchaus denkbar, dass jeweils die letzte Zeile der Produktgruppe von Hand gesucht, dann kopiert und in einen Zielbereich eingefügt wird. Aber das ist nun wirklich nicht der Hit, wenn es mehr als 10 oder 20 Gruppen wären. Da kann Excel uns doch helfen.  😆 

Um nicht jedes Mal die eingegebene Formel nach ganz unten kopieren zu müssen, wandeln Sie die Daten einfach per StrgT in eine Intelligente Tabelle um. Schreiben Sie nun in D1 die Überschrift Tagessumme. Und wenn Sie schon einmal dabei sind, in E1 FilterKrit. In D3 (nicht D2!) geben Sie nun diese Formel ein: 
=WENN([@Datum]=B2;C2+D2;[@Umsatz])
Das Ganze scheint verwirrend, aber wenn Sie das so erledigen wie ich es mache, dann wird das recht easy und logisch. Immer noch in D3: Nach dem Gleichheitszeichen schreiben Sie das WENN(, dann klicken Sie in A3. Jetzt ergänzen Sie =B2;C2+ und klicken dann in D2. Damit ist der Teil der WENN-Funktion formuliert, welcher den zutreffenden Fall beschreibt. Also: Wenn in Datum der gleich Wert steht wie in der Zelle darüber, dann ist das Funktionsergebnis die Summe des aktuellen Umsatzes und der bisherigen Tagessumme. Bleibt noch die Möglichkeit, dass der darüber stehende Wert in der Datum-Spalte nicht gleich ist, dann entspricht die Tagessumme natürlich dem in Spalte C stehenden Umsatz. Dazu klicken Sie in C3 und fügen dann noch die schließende Klammer ein. – Natürlich haben Sie längst erkannt, dass die Intelligente Tabelle dafür sorgt, dass automatisch alle Formeln und alle Zeilen der Liste nach oben und nach unten aufgefüllt werden.

Bleibt nur noch die Spalte E mit den Filterkriterien. Hier soll verglichen werden, ob in der Folgezeile eine andere Produktgruppe steht wie in der aktuellen Zeile. Und das ist ja genau dann nicht der Fall, wenn ein Wechsel der Produktgruppe stattfindet, weil die Sortierung die Produktgruppen zusammen hält. Und da innerhalb der Gruppe ja nach Datum sortiert wurde, ist das auch der letzte Tag mit Umsatz dieser Gruppe. Geben Sie also in E2 erst einmal das Gleichheitszeichen ein, klicken dann auf A2 und schreiben dann <>A3. Mit anderen Worten: Sie behaupten, dass in der Folgezeile der Spalte A ein anderer Wert steht 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 letzte einer Produktgruppe ist. Sie merken gewiss, dass das genau das ist, was Sie zur Bewältigung der Aufgabe brauchen. Ausschließlich jede letzte Zeile einer Gruppe enthält hier ein WAHR

▲ nach oben …

Daten filtern

Der letzte Schritt ist der am wenigsten aufwendige. Ein Klick in E1 auf das Erweitern-Symbol ▼ und setzen Sie den Filter auf WAHR. Ruck zuck werden nur die gewünschten Zeilen heraus gefiltert und angezeigt. Entweder belassen Sie es dabei oder Sie kopieren den gefilterten Bereich und fügen ihn an einer vorgesehenen Stelle ein. Der Optik wegen können Sie natürlich auch die Spalte E ausblenden, klar …  😎 Um wieder alle Daten zu sehen, entfernen Sie einfach den Filter.

▲ nach oben …

Lösungsweg PivotTable

Pivot-Tabellen sind ein anspruchsvolles aber auch mächtiges Instrument zur Auswertung von Daten. Und wenn die Basisdaten in einer „wohl geordneten Form“ vorliegen, macht es richtig Spaß, mit unterschiedlichen Darstellungen regelrecht zu „spielen“. In dieser Anleitung setzen wir einige Grundkenntnisse oder etwas Erfahrung in Sachen Pivot voraus, darum ist das Ganze hier eher stichwortartig mit wenigen zusätzlichen Ausführungen aufgeführt.

Natürlich ist wiederum die Mustertabelle die Basis für Ihre Auswertung. Entweder nutzen Sie eine Kopie der Roh-Daten oder Sie laden sich noch einmal die Muster-Datei herunter. StrgPos1 (dann ist die aktive Zelle garantiert im Datenbereich) und erstellen Sie auf eine Ihnen gewohnte Weise eine PivotTabelle. Ich erstelle aus den Roh-Daten erst eine Intelligente Tabelle, befinde mich dadurch automatisch im Entwurf der Tabellentools und dort in der Gruppe Tools die Auswahl Mit PivotTable zusammenfassen. Sie können die Pivot-Auswertung auf dem gleichen oder einem getrennten Blatt vornehmen, das ist nebensächlich.

Sortieren brauchen Sie die Daten nicht, das übernimmt die PivotTable für Sie bzw. es bedarf keiner Sortierung der Grund-Daten. Ab hier gelten alle Hinweise ausschließlich der Pivot-Tabelle (PT). Und halten Sie sich bitte exakt an die Anweisungen, damit das Vorhaben auch klappt  😉 .

  • Als erstes ziehen Sie das Feld ProduktGruppe in den Bereich Zeilen
  • Anschließend verfahren Sie mit dem Feld Datum gleichermaßen und positionieren es unterhalb der ProduktGruppen.
  • Nutzen Sie nun noch einmal das Feld Datum und ziehen es aber dieses Mal in den Bereich Werte.
  • Klicken Sie auf den Eintrag in Werte und dann auf Wertfeldeinstellungen… 
  • Wechseln Sie in Wertfeld zusammenfassen nach die Markierung von Auswahl nach Maximum. Anschließend OK.
  • Ziehen Sie nun das Feld Umsatz in den Bereich Werte unterhalb des Datums.
  • Achten Sie darauf, dass hier als Zusammenfassung die Summe angegeben ist. Erforderlichenfalls ändern Sie das.

Obwohl ja das Datum mit der Zusammenfassung Maximum markiert wurde, sind noch alle kalendarischen Daten und natürlich die dazu gehörigen Umsätze in der PT sichtbar. Und die Spalte Maximum von Datum ist auch nicht als Datum formatiert. Wenn Sie das „vernünftig“ ändern wollen, dann ändern Sie das über die Feldeinstellungen. Unten links ist die Schaltfläche Zahlenformat und dort werden Sie rasch fündig. Sie werden aber feststellen, dass die Korrektur keinen Einfluss auf die dargestellte Datenmenge hat.

  • Schalten Sie nun die Anzeige der Teil- und der Gesamtergebnisse aus; der Weg geht über die PivotTable-Tools, Menü Entwurf, Gruppe Layout
  • Ändern Sie in der gleichen Gruppe das Berichtslayout auf das Tabellenformat.

Der nächste Schritt ist der entscheidende, gehört aber nicht zu den viel genutzten Vorgehensweisen in diesem Bereich. Das Ziel ist ja, nur den zeitlich letzten Tag und Umsatz jeder Produktgruppe heraus zu filtern. Dazu ein Rechtsklick in ein beliebiges Feld ser Spalte Datum, dort Filter und Top 10… durch Klick wählen:

Top 10 Werte einer Spalte filtern

Top 10 Werte einer Spalte filtern

 Im folgenden Fenster ändern Sie die Anzahl der Elemente von der Vorgabe der Obersten 10 auf 1. Den Eintrag im Feld nach belassen Sie bei der Vorgabe Maximum von Datum. Nach einem OK schrumpft die extrem lange Liste auf die Überschrift und jeweils eine Zeile jeder Produktgruppe mit den kalendarischen Daten und den Umsätzen des letzten Umsatztages. – Die Spalte Maximum von Datum dürfen Sie gerne verstecken aber nicht löschen.

Der riesige Vorteil der PivotTable ist, dass sie mit wenigen Mausklicks auf neue, veränderte oder erweiterte Basisdaten anpassbar ist und natürlich auch beispielsweise die letzten 3 Umsätze mit minimalstem Aufwand angezeigt werden können. Dazu kommt, dass die vorhandenen Daten natürlich auch noch anders, weitergehender ausgewertet werden können.

Ach ja, eine Kleinigkeit noch: Wenn Sie meine/unsere Ergebnisse sehen wollen, dann blenden Sie einfach die versteckten Blätter ein. Dazu reicht ein Rechtsklick auf eines der Blatt-Register und Einblenden… 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 behandeln, dass neben der Überschrift ausschließlich die fünf Zeilen mit den entsprechenden Daten enthalten sind. „Zu Fuß“ oder „Automatik“, das ist hier die Frage …  😉 

Klassisch per Hand

Mit kleinen Ausnahmen ist diese Methode der ersten sehr ähnlich. Sie sollten darum die dort ausgeführte Vorgehensweise kennen und verstanden haben. – Zu Beginn gleich die erste Änderung: Sie kopieren in jedem Fall die Roh-Daten, denn es bleiben am Ende wirklich nur noch die entsprechenden fünf Produktgruppen übrig; alle anderen Zeilen sind unwiederbringlich gelöscht. Darum brauchen Sie auch noch für den Fall des Falles (Änderungen, Ergänzungen, …) die Original-Daten.

Alles ab hier aufgeführte bezieht dich ausschließlich auf diese Arbeitskopie.

  • Falls Sie mögen, formatieren Sie die Daten als Tabelle
  • Sortieren Sie nach dem Datum, aber dieses mal absteigend
  • Sortieren Sie nun nach Produktgruppen, wie gehabt aufsteigend
  • Menü Daten, Gruppe Datentools und hier Duplikate entfernen auswählen
  • Nur die Spalte ProduktGruppe angehakt lassen
  • OK und freuen.

Diese Daten sind statisch. Das bedeutet, dass Sie bei veränderten Basisdaten den erstellten Datenextrakt löschen müssen und beginnend mit dem kopieren der Quelldaten alles noch einmal durchführen müssen.

Mehrfache Auswertungen: Makro!

Wenn die reine Auswertung wie im Beispiel „Klassisch per Hand“ direkt hierüber akzeptabel oder sogar wünschenswert ist und der Vorgang wiederholt durchgeführt werden soll, dann bietet sich eine VBA-Lösung an. Hier erst einmal 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 Modul DieseArbeitsmappe oder nach Wahl in ein Allgemeines Modul. Wie Sie das einbinden können Sie bei Bedarf hier im Blog nachlesen. Zugegeben, das Ganze ließe sich auch komplett mit VBA-Arrays lösen, aber diese Lösung ist besser nachvollziehbar. – Diese beiden Lösungen liegen nicht als Datei zum Download bereit, wir senden sie Ihnen bei Bedarf aber gerne als E-Mail-Anhang zu.

▲ nach oben …

Dieser Beitrag wurde unter Mit VBA/Makro, Ohne Makro/VBA, Suchen und finden, Tabelle und Zelle, Wege nach Rom abgelegt und mit , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.