Clever-Excel-Forum

Guten Morgen,

kann mir bitte jemand bei dem folgenden Excel-Formelproblem helfen?

Wie kann ich die folgende Index-Formel erweitern? Mit der Formel konnte ich die Top 10 Kunden auf Ebene unseres Unternehmens ermitteln. Nun gibt es aber in unserem Unternehmen für die Kunden auch 8 Vertriebsmitarbeiter, für die ich die Top 10 eigentlich ermitteln möchte. Der Name des jeweiligen Vertriebsmitarbeiters steht auch in einer eigenen Spalte der Quelltabelle.

Hier die Index-Formel für das Gesamtunternehmen, die funktioniert:

=INDEX(‘Daten – detailliert’!$B$4:$B$11487;VERGLEICH(KGRÖSSTE(‘Daten – detailliert’!$L$4:$L$11487;$Q53);’Daten – detailliert’!$L$4:$L$11487;0);SPALTE(‘Daten – detailliert’!A1))

Nun ergänzt um den Vertriebsmitarbeiter, dann erhalte ich aber als Ergebnis “#NV”:

=SVERWEIS($A$1;’Daten – detailliert’!$B$4:$C$11487;INDEX(‘Daten – detailliert’!$B$4:$B$11487;VERGLEICH(KGRÖSSTE(‘Daten – detailliert’!$L$4:$L$11487;$Q53);’Daten – detailliert’!$L$4:$L$11487;0);SPALTE(‘Daten – detailliert’!A1));FALSCH)
In der Quelltabelle “Daten – detailliert” steht in Spalte B der Kundenname und in Spalte C der Vertriebsmitarbeiter.

Weiß jemand, wo der Bug in meiner Formel ist?

Viele Grüße

Chainsaw

Moin,

Zitat:Weiß jemand, wo der Bug in meiner Formel ist?

wenn du uns deine Datei (natürlich anonymisiert) zur Verfügung stellst, können wir gerne nachsehen. http://www.clever-excel-forum.de/Thread-...ng-stellen

Hallo,

hast du geprüft, ob die Fragestellung mit einer Pivot-Tabelle (Top-n) zu lösen ist?

mfg

Moin Günter,

anbei die stark verschlankte und anoymisierte Datei.

Wie beschrieben funktioniert es für das Unternehmen (grünes Tab), für den einzelnen Vertriebler (z.B. Vertriebler39, rotes Tab) funktioniert es nicht.

Wäre dankbar für Hilfe.

Viele Grüße

Chainsaw

Hallo Fennek,

ginge natürlich auch.

Ich möchte die Datei aber monatlich aktualisieren. Daher soll alles verformelt sein. Ansonsten muss zu viel händisch angepasst werden.

Danke

Viele Grüße,

Chainsaw

Hai,

mein Lösungsansatz wäre eine neues Tabellenblatt mit Daten Vertrieb. 
Dort alle MA reinpacken. Damit das dynamisch bleibt hole ich einfach aus der Spalte Sachbearbeiter die Leute rüber und ziehe einfach nach unten.
Ist kein MA vorhanden dann steht dort eine 0, kommt einer dazu wird er automatische hinzugefügt. 

Dann ein neues Tabellenblatt eingefügt (Daten - Vertrieb) und die Summe aus Daten detalliert für die MA gebildet. 
Im Tabellenblatt MA (funktioniert nicht) die größte Zahl von 1 bis 10 ausgeben lassen und zu dem Wert den MA per SVerweis zuordnen. 

Alles bleibt Dynamisch und du kannst deine Daten nach belieben erweitern bzw. ändern. 
Ich hoffe das hat dir geholfen.

Gruß

Hallo MajorOli,

danke.

Aber es ist leider nicht ganz das, was ich benötige. Ich hatte wahrscheinlich nicht präzise genug die Problemstellung beschrieben:

Das Tab "MA (funktioniert nicht!)" möchte ich 8x, d.h. für jeden Vertriebler erstellen.

Der jeweilige Vertriebler wird in dem Tab in Zelle "B1" über einen Link zum Tab "Sachbearbeiter" definiert. In Spalte "B" sollen dann die Top 10-Kunden und in Spalte "C" der Umsatz in 2017 meines jeweiligen Vertrieblers aus dem Tab "Daten - detailliert" gezogen werden.

Also, analog zu der Darstellung im Tab "Unternehmen (funktioniert!)", wo ich die Analyse für das Gesamtunternehmen ermittelt habe. Ändere ich in Zelle "B1" den Vertriebler, sollen die Top 10-Kunden für den dann ausgewählten Vertriebler gezogen werden. Wenn das dann bei einem Vertriebler klappt, kann ich das Tab einfach 7x duplizieren (so mein Wunsch) und alles zieht sich monatlich nach dem Update der Zahlen automatisch aus dem Tab "Daten - detailliert".

Am liebsten wäre es mir auch alles ohne weitere Zwischentabs zu ermitteln, also mit einer Formel je Zeile (= Top 10-Kunde in Spalte B im Tab "MA (funktioniert nicht!)"), ähnlich zu der oder aufbauend auf der Formel in Spalte B im Tab "Unternehmen (funktioniert!)". Ich möchte das Datenvolumen der Datei, die tatsächlich deutlich größer ist und mehr Analysen zum Vertriebscontrolling enthält, nicht durch zusätzliche Tabs weiter in die Höhe schießen lassen.

Ich würde mich freuen, wenn mir jemand dabei helfen könnte, diese Nuss zu knacken.

Viele Grüße

Chainsaw

Moin,
sieh dir mal Stichwort: Power Query (2010/13) aka Daten | Abrufen und transformieren (2016/365) an, damit ist das sehr schön zu realisieren.

Hallo Chainsaw.

Ich empfehle AGGREGAT statt KGROESSTE zu nutzen, weil Du dort eine logische Abfrage auf den jeweiligen Vertriebler einbauen kannst.

Nur dieser Befehl 

              AGGREGAT(14;7;'Daten - detailliert'!$L$4:$L$202/('Daten - detailliert'!$C$4:$C$202="Vertriebler68");ZEILE(A1))

liefert für Vertriebler68 diese 10 Geschäfte

1134372,85
309981,28
280139,67
221457,84
209580,94
207971,26
169226,63
153288,90
147835,72
139194,60
132835,69
118160,56
113692,41 

INDEX und VERGLEICH bleiben wie beim Kunden. Du erreichst über AGGREGAT , dass die Abfrage wie beim Kunden funktioniert - aber es kommt nur immer ein Vetriebler "zum Zuge". 

Probier es bitte mal aus.

LG Peter

Hallo

Ich hätte da eine Lösung mit Power Query und Pivot.