Office: (Office 2016) Kundenumsatz Tabelle ggf. mit Grafik

Hallo,

mir ist heute die Idee zu einer neuen Tabelle gekommen. Und zwar würde ich gerne eine Tabelle erstellen, wo man gleich sehen kann, in wie weit Kunden in einem Jahr eingekauft haben und dies halt über mehrere Jahre, so dass man gleich sieht, wenn ein Kunde plötzlich nicht mehr einkauft oder weniger.

Die Umsätze kann ich mir aus einer Pivot-Tabelle ziehen, die auf unser Warenwirtschaftsprogramm zugreifen kann.
Leider werden da auch alle Kleinst- und Einmalkunden mit aufgeführt, was die Sache sehr unübersichtlich macht (ich weiß nicht, wie man bei einer Pivot-Tabelle z.B. Beträge oder Kundennummern ab einer bestimmten Höhe anzeigen lassen kann, bzw. darunter nicht anzeigen, ohne erst alle auszuwählen).

Also habe ich jetzt die Kunden mit ihren Absatzzahlen in eine neue Arbeitsmappe kopiert und jedes Jahr in eine eigene Tabelle.
Das Problem ist ja, dass sehr viel Bewegung in den Listen sind. Also es gibt Kunden, die neu sind, andere bestellen nicht mehr. Daher ist es nicht möglich alle Listen 1:1 in eine Tabelle zu kopieren.

In meiner Beispieltabelle soll im ersten Blatt das "Gesamtergebnis" stehen. Also alle Kunden (Kundennummer und Name reicht in dem Fall) und dahinter die Umsätze mit den Jahren.
Dabei sollen z.B. Neukunden, bzw. Bestandskunden ohne Zahlung z.B. "0" betragen.

Schön wäre es vielleicht auch, wenn man das ganze in eine Grafik (vielleicht für die 20 Höchsten Beträge/Gesamtumsätze insgesamt) fassen könnte. Aber mit so etwas habe ich noch gar keine Erfahrungen, bzw. waren die Ergebnisse "unbrauchbar" :D
Ist aber nur so zweitrangig.


Vielleicht hat ja jemand eine Idee, wie man das bewerkstelligen kann und ob das überhaupt möglich ist.
Die Firmen-Rechner haben zwar Office 365, aber die installierten Versionen scheinen doch älter zu sein, da z.B. XVERWEIS nicht funktioniert

Ich danke schon mal für eure Vorschläge!

Gruß
Andreas

 

Hallo,

„Die Tabelle ist ja nur schnell zusammengeschustert, zur Anschaulichmachung“
ja genau.
Alles in ein Tabelle, wat is?
Könnte dann so aussehen. Das Diagramm ist dann für Chefe.
Die Kundennummer wird über ein DropDown ausgewählt.
Gruß
Roger

 

Hallo Andreas,

ich hätte es über VBA gelöst.
Grafik vielleich Diagramm.

starte den VBA Editor (Alt+F11), Bild sollte zweigeteilt sein ansonsten Strg+R, gehe auf Deine Datei, rechte Maustaste, Einfügen Modul, Code ins rechte Fenster kopieren, VBA Editor schließen (Alt+F4, oder Kreuz rechts oben).
Register Ansicht, Befehlsgruppe Makros, auf Makro klicken, Makros anzeigen, Makro auswählen und ausführen (Alt+F8 ).

Code:

Option Explicit

Sub Tabelle()
    Dim Loletzte1 As Long                           ' Zeilenanzahl Kundenumsatz
    Dim Loletzte2 As Long                           ' Zeilenanzahl Quelltabelle
    Dim InLetzte As Long                            ' Spaltenanzahl Kundenumsatz
    Dim InL As Long                                 ' Schleifenvarible Worksheets
    Dim InK As Long                                 ' Schleifenvariable Quelltabelle
    Dim RaFoundS As Range                           ' Suchvariable
    Dim RaFoundZ As Range                           ' Suchvariable
    Application.ScreenUpdating = False              ' Bildschirm aus
    ' letzte Spalte in Kundenumsatz
    With Worksheets("Kundenumsatz")
        InLetzte = Worksheets("Kundenumsatz").UsedRange.SpecialCells(xlCellTypeLastCell).Column
        Loletzte1 = 1
    End With
    For InL = 1 To ActiveWorkbook.Worksheets.Count
        With Worksheets(InL)
            If .Name <> "Kundenumsatz" Then         ' nicht Datentabelle
                ' Letzte Zeile in der Tabnelle
                Loletzte2 = IIf(IsEmpty(.Cells(Rows.Count, 1)), .Cells(Rows.Count, 1).End(xlUp).Row, .Rows.Count)
                ' Such Spalte für Eintrag
                Set RaFoundS = Worksheets("Kundenumsatz").Rows(1).Find("Umsatz " & Worksheets(InL).Name, .Range("A1"), , xlPart, , xlNext)
                ' xlPart enthalten
                ' xlWhole kompletter Vergleich
                If RaFoundS Is Nothing Then          ' Jahr nicht gefunden
                    ' Neu
                    Worksheets("Kundenumsatz").Cells(1, InLetzte).Copy Worksheets("Kundenumsatz").Cells(1, InLetzte + 1)
                    Worksheets("Kundenumsatz").Cells(1, InLetzte + 1) = " Umsatz " & Right(Worksheets(InL).Name, 4)
                    InLetzte = InLetzte + 1
                End If
                ' Schleife über Einträge
                For InK = 2 To Loletzte2
                    ' Suche Kunde
                    Set RaFoundZ = Worksheets("Kundenumsatz").Columns(1).Find(.Cells(InK, 1), _
                        .Range("A1"), , xlPart, , xlNext)
                    ' xlPart enthalten
                    ' xlWhole kompletter Vergleich
                    If Not RaFoundZ Is Nothing Then
                        ' gefunden
                        Worksheets("Kundenumsatz").Cells(RaFoundZ.Row, RaFoundS.Column) = .Cells(InK, 7)
                    Else
                        ' nicht gefunden
                        Worksheets("Kundenumsatz").Cells(Loletzte1 + 1, 1) = .Cells(InK, 1)
                        Worksheets("Kundenumsatz").Cells(Loletzte1 + 1, 2) = .Cells(InK, 2)
                        Worksheets("Kundenumsatz").Cells(Loletzte1 + 1, RaFoundS.Column) = .Cells(InK, 7)
                        Loletzte1 = Loletzte1 + 1
                    End If
                Next InK
            End If
       End With
    Next InL
    Application.ScreenUpdating = True               ' Bildschirm aus
End Sub

 

So, langes Wochenende vorbei, ab jetzt kann es weitergehen.
Danke an Hajo für die große Mühe. Das klappt super, so wie ich es mir vorgestellt habe. Ich werde mich mal mit dem Makro/VBA beschäftigen, damit ich das auch möglichst verstehe.

Ich hatte in den letzten Tagen auch schon gegrübelt, ob es eine Möglichkeit gibt eine Tabelle zu "säubern". Also ich kopiere quasi alle Daten des Jahre in eine Tabelle, verschiebe die Gesamtsumme in das Feld des jeweiligen Jahres und dann fügt Excel alle Einträge mit z.B. gleicher Kundennummer in eine Zeile zusammen. Aber ich weiß nicht, ob das dann leichter gegangen wäre. Aber das kleine Programm dort ist natürlich auch genial. Danke dafür

 

solche Sachen sollte man gleich im ersten Beitrtag schreiben, wenn man eine XLSX Datei hochlädt.
In dieser könnenb keine Makros gespeichert werden.
Was bei Deiner Datei der Fall ist.
Ich musste jetzt die Datei erst wieder herstzellen.

Code:

Option Explicit

Sub Tabelle()
    Dim Loletzte1 As Long                           ' Zeilenanzahl Kundenumsatz
    Dim Loletzte2 As Long                           ' Zeilenanzahl Quelltabelle
    Dim InLetzte As Long                            ' Spaltenanzahl Kundenumsatz
    Dim InL As Long                                 ' Schleifenvarible Worksheets
    Dim InK As Long                                 ' Schleifenvariable Quelltabelle
    Dim RaFoundS As Range                           ' Suchvariable
    Dim RaFoundZ As Range                           ' Suchvariable
    Application.ScreenUpdating = False              ' Bildschirm aus
    ' letzte Spalte in Kundenumsatz
    With Worksheets("Kundenumsatz")
        InLetzte = Worksheets("Kundenumsatz").UsedRange.SpecialCells(xlCellTypeLastCell).Column
        Loletzte1 = 1
    End With
    For InL = 1 To ActiveWorkbook.Worksheets.Count
        With Worksheets(InL)
            If .Name <> "Kundenumsatz" Then         ' nicht Datentabelle
                ' Letzte Zeile in der Tabnelle
                Loletzte2 = IIf(IsEmpty(.Cells(Rows.Count, 1)), .Cells(Rows.Count, 1).End(xlUp).Row, .Rows.Count)
                ' Such Spalte für Eintrag
                Set RaFoundS = Worksheets("Kundenumsatz").Rows(1).Find("Umsatz " & Worksheets(InL).Name, .Range("A1"), , xlPart, , xlNext)
                ' xlPart enthalten
                ' xlWhole kompletter Vergleich
                If RaFoundS Is Nothing Then          ' Jahr nicht gefunden
                    ' Neu
                    Worksheets("Kundenumsatz").Cells(1, InLetzte).Copy Worksheets("Kundenumsatz").Cells(1, InLetzte + 1)
                    Worksheets("Kundenumsatz").Cells(1, InLetzte + 1) = " Umsatz " & Right(Worksheets(InL).Name, 4)
                    InLetzte = InLetzte + 1
                End If
                ' Schleife über Einträge
                For InK = 2 To Loletzte2
                    ' Suche Kunde
                    Set RaFoundZ = Worksheets("Kundenumsatz").Columns(1).Find(.Cells(InK, 1), _
                        .Range("A1"), , xlPart, , xlNext)
                    ' xlPart enthalten
                    ' xlWhole kompletter Vergleich
                    If Not RaFoundZ Is Nothing Then
                        ' gefunden
                        Worksheets("Kundenumsatz").Cells(RaFoundZ.Row, RaFoundS.Column) = .Cells(InK, 7)
                    Else
                        ' nicht gefunden
                        Worksheets("Kundenumsatz").Cells(Loletzte1 + 1, 1) = .Cells(InK, 1)
                        Worksheets("Kundenumsatz").Cells(Loletzte1 + 1, 2) = .Cells(InK, 2)
                        Worksheets("Kundenumsatz").Cells(Loletzte1 + 1, RaFoundS.Column) = .Cells(InK, 7)
                        Loletzte1 = Loletzte1 + 1
                    End If
                Next InK
            End If
       End With
    Next InL
    ' Summe
    With Worksheets("Kundenumsatz")
        InLetzte = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
        Loletzte1 = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
        With .Range(.Cells(2, InLetzte + 1), .Cells(Loletzte1, InLetzte + 1))
            .Formula = "=SUM(Gesamtumsatz[@[Kundennummer]:[Umsatz 20212]])"
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True               ' Bildschirm aus
End Sub