Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Haupt und Unteradresse zusammenfassen und weiteres
#1
Hallo liebe Forumsmitglieder,

 

nachdem ich mit meinen letzten Lösungsversuchen nicht weiter gekommen bin, habe ich von ganz vorne angefangen und schildere meine Aufgabe mal von ganz Anfang an. Ich hoffe jemand kann mir helfen.


Situation ist folgende: Mustertabelle anbei.


Ich möchte herausfinden, wieviel Provision ich pro Kunde erhalte.


1.  Schwierigkeit: In der Liste habe ich sowohl Kunden (als Hauptadresse), als auch (z.B. Ehefrauen oder Kinder) Unteradresse.
Ich kann das aber selektieren und ausgeben, ob es sich um eine Haupt- oder Unteradresse handelt und zu welcher Hauptadresse die Unteradresse gehört.


2.  Schwierigkeit: Die Kunden haben verschiedene Verträge, die unterschiedlich verprovisioniert werden. Ich kann die Verträge nach den verschiedenen Vertragsarten sortieren. Nun muss ich aber noch eine Formel zuweisen, die sagt: Sach wird mit Satz x verprovisioniert, Leben mit Satz y und Kfz mit Satz z.


Das Endergebnis sollte folgendes sein:

Ich bekomme pro Familie (als Kunde samt Unteradressen) eine Zahl, die auswirft, wie viel Provision ich für diesen Kunden erhalte. Dazu müssen die Provisionen der einzelnen Verträge der Haupt- und Unteradressen zusammengezählt werden.

 

Da es über 8.000 Verträge sind, ist alles was "händisch" gemacht werden soll relativ schwierig. Deshalb erbitte ich Hilfe, wie ich dies "relativ" automatisiert machen kann.

 

Ich bin für jede Hilfe dankbar. Gerne auch mit Zwischenschritten, …

 

Ich würde mal so vorgehen:

1.  Excel muss irgendwie erkennen, wenn es ein Sachvertrag ist, dann muss bei Provision die Formel x% mal die Prämie hin. Wenn es ein Kfz Vertrag ist, die Formel y% mal Prämie. Dann habe ich schon mal die Provision pro Vertrag


2.  Dann muss Excel irgendwie erkennen, dass die Unteradresse nicht selber "zählt", sondern der Hauptadresse zugerechnet wird


3.  Als drittes (oder vielleicht wäre das auch der zweite Schritt) sollte Excel die Summe der einzelnen Provisionen pro Kunde zusammenzählen und als 1 Wert auswerfen.

4.  Dann hätte ich mein Ziel eigentlich schon erreicht.
Also in der Theorie habe ich es schon drauf. Hab aber nur wenig Ahnung, wie ich das praktisch umsetze.


Angehängte Dateien
.xlsx   Crisu Auswertung Mappe.xlsx (Größe: 8,01 KB / Downloads: 7)
Antworten Top
#2
Hallo Crisu,

ich habe es mal so gelöst (siehe Anhang)


Angehängte Dateien
.xlsx   Crisu Auswertung Mappe 1.xlsx (Größe: 17,41 KB / Downloads: 14)
Gruß Conny :)
_______________________________________________________________

Die Summe der Intelligenz auf unserem Planeten ist konstant, aber die Bevölkerung wächst!
Antworten Top
#3
Hallo crisu,

in coemms datei solltest du in Spalte J diese Funktion eintragen:
Code:
=WENN(F3="";B3&", "&C3;F3)

und die Pivottabelle aktualisieren.
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antworten Top
#4
Hallo,

zuerst: die Lösung von "Ego" funktioniert n.m.E.

Gestern hatte ich mir die Beispieldatei angesehen unter der Prämisse, dass die Originaldatei nicht so sortiert sind und Namen nicht immer eindeutig sind. Dann wäre ein eindeutiger Schlüssel Vor-, Nachname und Geburtsdatum. Dies ist aber nicht mit den Nebenaddressen zu verbinden.

Deshalb, sofern eine Auswertung über Haushalte durchgeführt werden soll, müssen zuerst eindeute Schlüssel pro HH erzeugt werden.

mfg
Antworten Top
#5
(30.09.2016, 08:00)Fennek schrieb: zuerst: die Lösung von "Ego" funktioniert n.m.E.

stimmt, denn dann schreibt er nur noch Muster, Max und Maier, Hans.
Gruß Conny :)
_______________________________________________________________

Die Summe der Intelligenz auf unserem Planeten ist konstant, aber die Bevölkerung wächst!
Antworten Top
#6
Hallo Zusammen,

erst mal vielen Dank für die bisherigen Lösungen.

Wie kann ich den Schritt (von Fennek geschrieben): erst mal eindeutige Zuordnung von Haushalt lösen?

Folgendes ist mir auch noch aufgefallen:
Ich habe drei unterschiedliche Vertragsvarianten mit drei unterschiedlichen Provisionssätzen.
In der Lösung gibt es nur zwei Sätze: Wenn Varianta a, dann x%, ansonsten y%. Aber wie gesagt: Ich habe drei Sätze.
Was ich evtl. machen könnte. Aber das verkompliziert das Ganze. Ich könnte drei Tabellen erstellen mit den jeweiligen Vertragsvarianten.
Aber am Schluß muss ich dann doch wieder zusammenführen.

Was ich auch noch nicht kapiert habe, ist ganz am Schluß
Ich bräuchte eine Tabelle, in der steht "sinngemäß"
Familie Meier: 348,-€
Familie Muster: 230,-€

Denn diese Tabelle muss ich dann wieder in mein Verwaltungsprogramm einlesen.

Wie gesagt, schon mal vielen Dank
Antworten Top
#7
Ich habe mich jetzt weiter an der Sache versucht.

Das Problem mit der eindeutigen Zuordnung könnte man evtl. wie folgt lösen.
Ich habe mal eine neue Excel Liste mit angehängt. Dort habe ich den Kunden in einer Spalte und aufgedrösselt nach Kunde. Vorname; Kunde.Name,...
Evtl. könnte man jetzt per Formel festlegen. Z.B. in Spalte C: Wenn die Spalte leer, dann soll er dort den Kunden aus Spalte A eintragen. Wenn schon ein Name drin steht, soll er nichts machen.
DAnn hätten wir doch eine eindeutige Zuordnung zu Kunde.Hauptadresse.

Als nächstes bräuchte ich dann z.B. in Spalte L: Wenn Spalte J = Gebäude-Versicherung, dann x% mal Spalte K; Wenn Spalte J = Haftpflicht, dann y% mal Spalte K, wenn ..... (da habe ich bis zu 10 verschiedene Hauptsparten)

Dann hätte ich eine Spalte L mit allen Provisionen.
Jetzt müsste er die Provisionen des gleichen Kunden (Spalte C) zusammenzählen und z.B. in Spalte M auswerfen.

Dann bräuchte ich (evtl. in einer separaten Tabelle) folgendes
eine Zeile, die den Kunden genau definiert (so dass ich ihn wieder in mein Verwaltungsprogramm zurückspielen und identifizieren kann) und nur 1 Wert nämlich die Summe ausweist.

Vielleicht kann mir jemand helfen.

Danke


Angehängte Dateien
.xlsx   Mappe3.xlsx (Größe: 9,41 KB / Downloads: 2)
Antworten Top
#8
Hallo  crisu & Fennek

ich habe mich bis jetzt nicht eingeklinkt weil schön Lösungen vorlagen.  Danke an Fennek für seine Formel Lösung. Ich habe mir erlaubt sie ein wenig zu erweitern, denke das sie so optimaler ist.  Die Grundidee stammt aber von Fennek.

Ich mache mir noch ein paar Gedanken wie man per Makro die Summenformel zuweisen kann. Daran arbeite ich noch. Bitte erst mal prüfen ob die neue Formel Lösung so praxisgerecht ist. Die Hilfsspalte P % Satz kann verschoben werden. 

crisu, prüfe doch bitte mal was passiert wenn du den Vertragstyp zum Testen einfach mal löschst.  Überrascht ????
Ich hoffe das Fennek mich nicht als Konkurrenten ansieht und mit der Lösung einverstanden ist.

mfg  Gast 123


Angehängte Dateien
.xlsx   Crisu Auswertung Mappe 2.xlsx (Größe: 18,26 KB / Downloads: 4)
Antworten Top
#9
Hallo, ich bin mir nicht sicher, aber hier stimmt doch was nicht, oder..?

Arbeitsblatt mit dem Namen 'Tabelle1'
 JKLM
1  KundeMeier, Hans
2KundeCourtage  
3Muster, Max7,5Summe von Courtage 
4Muster, Max9130,85 
5Muster, Max75  
6Muster, Max15  
7Muster, Inge7,5  
8Muster, Inge11,5  
9Meier, Hans14,69  
10Meier, Hans7,5  
11Meier, Hans9  
12Meier, Hans75  
13Meier, Hans15  
14Meier, Anna7,5  
15Meier, Anna7,5  
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Antworten Top
#10
Hallo Gast 123,

vielen Dank. Ja, das ist schon gut.
Bitte sieh Dir doch noch meine angehängte Datei an.
Ich stehe vorher noch vor dem Problem. (siehe Sparte J), dass ich eigentlich mehrere Hauptsparten habe, die ich erst mal einer der drei "Oberhauptsparten" (Sach, Leben, Kfz) zuordnen muss.
D.h. ich muss Excel in der Spalte Kerst einmal sagen. Wenn in J "Gebündelte Versicherung" "Haftpflicht" "Gebäude" oder "Hausrat" steht, ist dies eine Sachsparte.
Wenn in J "Leben" oder "Berufsunfähigkeit" steht ist dies eine Lebensparte
Wenn in J "Kfz" steht ist es eine Kfzsparte

Dann käme deine Lösung.
Jetzt hätte ich schon mal pro Vertrag die Provision

Mit der Pivot kann ich zwar einen Kunden anzeigen lassen.
Ich bräuchte aber eine Tabelle, wo ich pro einzelnem Kunden (Hauptadresse) bzw. der Kundenfamile die Provision hätte.

für die Hilfe schon mal vielen Dank.


Angehängte Dateien
.xlsx   Mappe3_Version_2.xlsx (Größe: 9,92 KB / Downloads: 2)
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste