Office: Power Query

Hallo liebes Forum,
ich habe da eine Herausforderung und ich komme einfach nicht weiter.

Ich habe eine Liste mit Beratern und deren Umsätze (Kosten und Einnahmen) zu bestimmten Daten.

Jeder Berater hat einen Zielumsatz. Sobald der Zielumsatz überschritten wird, erhält der Berater von seinem Umsatz eine Provision von 40%, davor erhält er 0%.

Ich habe ein Beispiel angefügt, unten sind die Ausgaben.

Ich möchte das gerne mit Power Qurey lösen, dass die Spalte Provision befüllt wird, entweder mit 0 wenn der Wert bis zu diesem Zeitpunkt für diesen Berater noch unter dem Plan ist oder mit dem Wert * Provision wenn er überschritten wird.

Dazu kommt noch der Wert, bei dem die Überschreitung stattfindet, dieser darf nur anteilsmäßig mit der Provision versehen werden.

Ich freue mich über Hilfe und Rat!

:)

 

So wie ich das sehe, ist das eine Tabelle, die erhebliche Redundanzen und Inkonsistenzen aufweist. Zudem gibt es in der Spalte Berater und Projektnummern leere Zellen, das heißt, dass keine Zuordnung möglich ist.

Das ist meiner Ansicht nach eine typische Datenbankanwendung und sollte nicht mit Excel gelöst werden.

Kannst du mal für eine in sich schlüssige Tabelle sorgen?

Datenbank wäre schlüssig wenn:

1. Projektberater ---> Projektnummern ---> Daten

 

Ich weiß nicht… Aber irgendwie haben wir gewiss eine andere Auffassung über den Sinn und Zweck eines Forums. Du knallst hier eine ziemlich ungepflegte Tabelle hinein, wo du keinerlei Vorarbeit geleistet hast. Noch nicht einmal sauber formatiert


Da empfehle ich die die Jobbörse als gewiss kostengünstigste Alternative. Und auch da wirst du gewiss einiges an Erklärungen geben müssen, denn ich sehe keineswegs den Sinn der Spalten E:F. Das was du willst geht extrem weit über die Hilfe zur Selbsthilfe hinaus! *neneIrgendwie erinnert mich das an ein Sprichwort, das ich vor ungefähr 65 Jahren gehört und behalten habe:

Und auch mit Power Query ist das kein "mal so eben", du darfst dich gerne einmal selber in die laufende Summe einarbeiten und gewiss auch ein wenig (mehr) in die Sprache M.

 

Hallo,
ich habe alle leeren Felder gelöscht.

Ich hoffe das hilft.

 

Hallo,
danke für den Hinweis mit der laufenden Summe.

Die Tabelle ist das Ergebnis einiger Stunden arbeit - leider mit echten Daten.
Ich habe beim anonymisieren zuviel gemacht (vor allem die Daten Spalten als Zahl formatiert) - das tut mir leid.

Ich "verlange" von niemanden eine Lösung, mir fehlt nur die initiale Idee wie ich das umsetzen kann.

ich habe heute vormittag 2 Stunden mit Google verbracht um auf den richtigen Ansatz zu kommen und bin immer wieder gescheitert. Jetzt hatte ich gehofft, das mir jemand einen Tip geben kann - oder einen LInk oder die richtige Funktion *wink.gif*

Beste Grüße
Daniel

 

Es muss aber doch einen Grund haben, wie diese leeren Felder zustande gekommen sind. So wie ich das sehe gibt es einen Datensatz "Berater". Dann gibt es einen zweiten Datensatz an Projekten, die den Beratern zugeteilt werden. Jedes Projekt hat ein zugeordneten Berater mit einem Projektziel.

Ich bleibe dabei, dass es mit einer Datenbank besser zu lösen wäre. Das vermeidet Redundanzen, wäre konsistent und flexibel in der Handhabung.


Excel ist eine Tabellenkalkulation und solche Änderungen führen in der Regel zu Problemen.

 

Moin,

weil ich grad Bock auf PQ hatte Auf Basis deines Musters unter #5


ProvisionsTabelle

PHP:

         let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle4"]}[Content],
ChType1 = Table.TransformColumnTypes(Quelle,{{"Berater", type text}, {"Plan", Int64.Type}, {"Provision", type number}})
in
ChType1


 

In Anbetracht der Vorreden habe ich mir die Arbeitsmappe gar nicht angesehen. Die Fragestellung ist an sich so klar, dass dann weiteres nur zur Verwirrungen führen kann, und ordentliche verarbeitbare Datenstrukturen setze ich einfach voraus.
U ... Umsatz
UZ ... Umsatzziel
PS ... Provisionssatz

Code:

 

Hallo Jack_D,
vielen Dank! Dein Ansatz hat mich weiter gebracht *Smilie

Habe weiterhin öfters mal Bock auf PQ *wink.gif*

Besten Dank
Daniel

 

Hallo allerseits,
besten Dank für die Rückmeldungen. Insgesamt habe ich gelernt, dass das Problem kein triviales ist.

Ich mir bei der Erklärung von nicht-trivialen Problemen mehr Zeit nehmen sollte.

Bei mir Kundenanforderungen wohl oft der besten Lösung gegenüberstehen.

Die Lösung für mich war jetzt die, die Daten mit einem Makro auszuwerten und in die gewünschte Form zu bringen.

Ich füge es an, falls mal jemand über den Thread stolpert und es gerne sehen möchte (Das Makro passt allerdings auf die echte Datei und nicht auf die Testdatei)

Besten Dank allen die hier geantwortet haben. Ich wünsche euch eine schöne und besinnliche Vorweihnachtszeit.

Liebe Grüße
Daniel



Sub start()
Dim wb As Workbook
Dim wsAusgabe As Worksheet
Dim wsInput As Worksheet
Dim wsBerater As Worksheet
Call GoodStart
Set wb = ThisWorkbook
Set wsAusgabe = ActiveSheet
Set wsInput = wb.Sheets("ProvisionRohdaten")
Set wsBerater = wb.Sheets("Berater")

Dim i As Integer, iLastRow As Integer, iZeile As Integer, iSpalte As Integer
Dim strBerater As String, strJahr As String, dblPlanumsatz As Double

strBerater = wsAusgabe.Range("ZielBerater").Value
strJahr = wsAusgabe.Range("ZielJahr").Value

iLastRow = wsInput.Cells(Rows.Count, 1).End(xlUp).Row

'löschen der bisherigen Daten
wsAusgabe.Range("A7:N100000").Value = ""

'zurücksetzen autofilter
On Error Resume Next
wsAusgabe.ShowAllData
On Error GoTo 0

'Planwert errechnen
'On Error GoTo BeraterRohdaten
iSpalte = wsBerater.Range("A1:I400").Find(what:="Plan", lookat:=xlWhole).Column
iZeile = wsBerater.Range("A1:I400").Find(what:=strBerater, lookat:=xlWhole).Row
dblPlanumsatz = CDbl(wsBerater.Cells(iZeile, iSpalte).Value)

Dim iProvision
'Provision
iSpalte = wsBerater.Range("A1:I400").Find(what:="Berater Voll", lookat:=xlWhole).Column
Dim dblProvisionsSatz As Double
dblProvisionsSatz = wsBerater.Cells(iZeile, iSpalte).Value

'SearchArt
iSpalte = wsBerater.Range("A1:I400").Find(what:="Search Rat", lookat:=xlWhole).Column
Dim iSearchProvision As Integer
iSearchProvision = wsBerater.Cells(iZeile, iSpalte).Value

Dim strSearchArt As String

If iSearchProvision < 1 Then
iSpalte = wsBerater.Range("A1:I400").Find(what:="SearchPausch", lookat:=xlWhole).Column
iSearchProvision = wsBerater.Cells(iZeile, iSpalte).Value
strSearchArt = "Pauschal"
Else
strSearchArt = "Rate"
End If

On Error GoTo 0

'input durchgehen und immer die relevanten Daten in Output schreiben, dabei die Provision hochzählen
Dim strProvisionVsPlan As String
strProvisionVsPlan = "niedriger"

Dim dblAktuellerRohertrag As Double
dblAktuellerRohertrag = 0

Dim iZielzeile As Integer
iZielzeile = 7
'Berater = 18
'Jahr = 15
'Monat 14, Projektnummer 4, Honorarart 9, Kunde / Lieferant 16 , Datum 8 , PRovisionsatz - variable, Provision - errechnet
'Searchart 17
For i = 2 To iLastRow
If UCase(wsInput.Cells(i, 18).Value) = UCase(strBerater) Then
If wsInput.Cells(i, 15).Value = strJahr Then
'Übereinstimmung - übertragen der Daten
If wsInput.Cells(i, 17).Value = "SearchRat" Then
If strSearchArt = "Pauschal" Then
GoTo naechstesI 'der Berater bekommt nur Pauschalen, das hier ist wäre eine Rate
End If
End If
'übernehmen der Werte
wsAusgabe.Cells(iZielzeile, 1).Value = wsInput.Cells(i, 14).Value 'Monat
wsAusgabe.Cells(iZielzeile, 2).Value = wsInput.Cells(i, 4).Value 'Projektnummer
wsAusgabe.Cells(iZielzeile, 3).Value = wsInput.Cells(i, 9).Value 'Kunde
If Left(wsInput.Cells(i, 4).Value, 2) = "LH" Then
Debug.Print i
End If
wsAusgabe.Cells(iZielzeile, 4).Value = wsInput.Cells(i, 16).Value 'Rechnungsart
wsAusgabe.Cells(iZielzeile, 5).Value = wsInput.Cells(i, 8).Value 'Datum
wsAusgabe.Cells(iZielzeile, 6).Value = dblProvisionsSatz
wsAusgabe.Cells(iZielzeile, 7).Value = wsInput.Cells(i, 19).Value
If Left(wsInput.Cells(i, 17).Value, 6) = "Search" Then
dblAktuellerRohertrag = dblAktuellerRohertrag
Else
dblAktuellerRohertrag = dblAktuellerRohertrag + wsInput.Cells(i, 19).Value 'Wert
End If
wsAusgabe.Cells(iZielzeile, 8).Value = dblAktuellerRohertrag

If strProvisionVsPlan = "niedriger" Then
If dblAktuellerRohertrag > dblPlanumsatz Then 'Planwert wurde überstiegen
strProvisionVsPlan = "hoeher"
wsAusgabe.Cells(iZielzeile, 9).Value = (dblAktuellerRohertrag - dblPlanumsatz) * dblProvisionsSatz
wsAusgabe.Cells(iZielzeile, 10).Value = "Wechsel"
Else
wsAusgabe.Cells(iZielzeile, 9).Value = 0 'noch keine Provision
End If
Else
wsAusgabe.Cells(iZielzeile, 9).Value = wsInput.Cells(i, 10).Value * dblProvisionsSatz
End If

If Left(wsInput.Cells(i, 17).Value, 6) = "Search" Then
wsAusgabe.Cells(iZielzeile, 9).Value = iSearchProvision
End If
wsAusgabe.Cells(iZielzeile, 11).Value = wsInput.Cells(i, 17).Value
wsAusgabe.Cells(iZielzeile, 12).Value = i
iZielzeile = iZielzeile + 1
End If
End If

naechstesI:
Next i



Call GoodEnd
Exit Sub

BeraterRohdaten:
MsgBox "Berater konnte nicht gefunden werden oder Überschriften wurden verändert bei den Berater Stammdaten"
Call GoodEnd
Exit Sub


End Sub

 

Vor einigen Wochen hatte ich durch eine andere Fragestellung schon eine Lösung für "Running Total" gefunden. Dieses Beitrag habe ich nun noch einmal geöffnet und gesehen, dass dieser auch eine Lösung für das aktuelle Problem bietet.

Es war jetzt nicht sonderlich schwer, die dort gebotene Lösung zu adaptieren.

Es ist aber nicht so ganz einfach, diese vollständig zu verstehen, denn hierzu müsste ich mehr darüber wissen, wie Power Query "intern" tickt.

Aber trotzdem versuche ich nun einmal den Lösungsweg zu erläutern:

In meinen Beispiel habe ich 3 Kunden mit 2, 3 und 4 Einträgen. Somit haben diese Zeilen, bei einem Index, der mit "1" beginnt, die Werte 1-9.

Nach dem Lesen der Daten wird ein Index eingefügt und nach Kunde gruppiert, ohne zu verdichten (Alle Zeilen). Als Ergebnis erhalten wir die neu (Tabellen)Spalte "Count".

Die neue Spalte "Count" wird kopiert und die (Tabellen)Spalte "Count-Copy" eingefügt. Hierbei wird nun PQ-intern der Index neu gesetzt.

Nun wird "Count" expandiert. Als Ergebnis sehen wir nun das, was wir eigentlich vorher schon hatten.

Wenn wir nun aber als nächstes die Spalte "Count-Copy" expandieren, sehen wir, dass durch das kopieren von "Count" nach "Count-Copy", jede Zeile zu einem Kunden, so oft kopiert wurde, wie es Zeilen zu dem Kunden gibt.

Aus 2 Zeilen von Kunde A werden somit 4 Zeilen, aus 3 Zeilen zu Kunde B werden somit 9 Zeilen und aus den 4 Zeilen von Kunde C werden somit 16 Zeilen. Aus 9 Zeilen werden somit insgesamt 29 Zeilen.

Aus der Zeile mit Index 1 wurden die 2 Zeilen mit Index 1 und mit 1 und 2 mit Index.1.
Aus der Zeile mit Index 2 wurden die 2 Zeilen mit Index 2 und mit 1 und 2 mit Index.1.
Aus der Zeile mit Index 3 wurden die 3 Zeilen mit Index 3 und mit 3 und 4 und 5 mit Index.1.
Aus der Zeile mit Index 4 wurden die 3 Zeilen mit Index 3 und mit 3 und 4 und 5 mit Index.1.

Und so weiter, bis am Ende für die Zeile mit Index 9 die Zeilen 6, 7, 8 und 9 mit Index.1 angelegt wurden.

Mit dem nächsten Schritt wird nun für jede Zeile abgefragt, ob [Index] >= [Index.1] ist. Als Ergebnis erhalten wir die Spalte "Custom" mit den Wert TRUE oder FALSE.

Im nächsten Schritt wird nun auf TRUE gefiltert. Damit bleiben zu jedem Kunden die Einträge erhalten, bei denen Index.1 =< Index ist.

Mit der Gruppierung (Summenbildung) im nächsten Schritt erhalten wir nur wieder die 9 Ausgangszeilen, die in der neuen Spalte "Rng.Ttl.Cust." (Running Total Customer), jeweils den kumulierten Wert von Beginn des Kunden bis zur aktuellen Zeile zeigt.

Am Ende wir dann noch ein "Running Total" über alles eingefügt.

Anbei meine hier erläuterte Beispielmappe, sowie die Mappe von Daniel Seeger, in die ich die beschriebene Lösung ebenfalls integriert habe.

Bei einem Test mit knapp 1.800 Zeilen, wird mir das Ergebnis nach ca. 2 Sekunden angezeigt. Bei einem Versuch mit "Table.Buffer", bei dem ich diesen vermutlich falsch anwende, hat es dann 14 Sekunden gedauert.

Vom Code ist die PQ-Lösung übrigens wesentlich kürzer als der VBA-Code. Und im Tabellenblatt "Input" habe ich auch eine Formellösung eingefügt.

 

Hallo,

in der vorherigen Version wurden, wenn der letzte Betrag eines Beraters negativ war, die Provisionswerte nicht immer richtig berechnet.

In der nun hochgeladenen Mappe, sollte es nun für alle Konstellationen stimmen. Die Formellösung habe ich aber nicht angepasst.