Microsoft Excel

Herbers Excel/VBA-Archiv

Top5 pos./neg selektieren

Betrifft: Top5 pos./neg selektieren von: mopitz
Geschrieben am: 21.08.2008 10:27:56

Hallo,
ich hoffe, ihr könnt mir eine recht automatisierte Lösung für mein Problem geben.
Ich habe einen Riesendatei mit folgenden Daten:

Abweichung IST zum PLAN:

TechnologieKundeUmsatz Spanne
Tech1 KundeA 500 200
Tech1 KundeB 300 100
… … .. ..
Tech2 KundeC 1000 800
Tech2 KundeD 600 300
…. … … …
Tech3 KundeE 400 50
Tech3 KundeF 550 250
=> ZIEL: pro Technologie die Top5 Kunden aufgrund pos. & neg. Abweichung rauszufinden.
und zwar einmal sortiert nach Umsatz (Top5 pos./Top5 neg) und einmal nach Spanne (Top5 pos./Top5 neg). Ich muß zwar das ganze dann einmal für den aktuellen Monat isoliert und danneinmal für den Halbjahr isoliert, aber mir würde schon reichen, wenn ich eine Lösung für z.B. den aktuellen Monat bekommen könnte.
Ich habe versucht, eine Pivottabelle pro Technologie zu erstellen, aber wenn ich Feldeigenschaften "Top5" auswählen, dann kann ich nur "oberen" oder "unteren" auswählen...und leider nicht beide. Das Ergebnis dieser Auswertung sollte ich ja "übersichtlich" sein und nicht im Blatt hunderte von Tabelle beinhalten.

Ich hoffe, ich konnte es einigermaßen verständlich mein Problem schildern.

Danke vorab für Eure Ideen.



  

Betrifft: Nachfragen von: mpb
Geschrieben am: 21.08.2008 10:58:04

Hallo,

wie werden denn die Abweichungen berechnet, Umsatz minus Spanne? Wo stehen die Daten? Wie soll die Ergebnistabelle aufgebaut sein? Sollen die Kunden und/oder Abweichungen aufgelistet werden? Schonmal die Funktionen KGRÖSSTE und KKLEINSTE versucht?

Evtl. mal eine Beispieldatei hochladen.

Gruß
Martin


  

Betrifft: AW: Nachfragen von: mopitz
Geschrieben am: 21.08.2008 12:54:25

Hallo Martin,

danke für den Tipp mit KKleinste, KGrößte. ABER ich bekomme nicht den dazugehörigen Kundenname. SVerweis funktioniert bei dieser Konstellation nicht (siehe Beispieldatei).
Wie kann ich den Kundennamen dazuspielen?

https://www.herber.de/bbs/user/54768.xls

Danke und Gruß, mopitz


  

Betrifft: AW: Lösungsansatz von: mpb
Geschrieben am: 21.08.2008 13:17:08

Hallo,

ich habe Dir für einen "Block" mal Formeln eingetragen, die die Aufgabenstellung so lösen, wie ich es verstanden habe. Werte über KGRÖSSTE/KKLEINSTE; dazugehörige Kunden über eine Kombination von INDEX und VERGLEICH. Schau Dir mal die Beispieldatei an:

https://www.herber.de/bbs/user/54771.xls

Gruß
Martin


  

Betrifft: AW: Lösungsansatz von: mopitz
Geschrieben am: 21.08.2008 13:34:27

Hallo Martin,
vielen herzlichen Dank! Ja, es ist genau das was ich gesucht habe!!

EIne kleine Frage: warum hast du bei KGrößte nach ";" nicht die Zahl 1, 2, etc. , sondern Zeile ()-22 genommen. Was sagt das aus?

Gruß, mopitz


  

Betrifft: AW: Erklärung von: mpb
Geschrieben am: 21.08.2008 13:41:16

Hallo,

Zeile() gibt die Zeilennummer der Zeile zurück, die der diese Funktion als (Teil einer) Formel steht. In Q23 ist das Ergebnis also 23, so dass Zeile()-22 den Wert 1 ergibt. Diese Formel lässt sich nun fünf Zeilen nach unten kopieren und der 2. Parameter der KGRÖSSTE-Funktion passt sich automatisch von 1-5 an. Alternativ müsstest Du den jeweiligen Wert händisch eintragen. Wenn Du an andere Stellen in der Tabellen kopierst, musst Du in Zeile()-22 die abzuziehende Zahl ggf. anpassen. Allgemein: In Zeilex gibt Zeile()-(x-1) den Wert 1.

Gruß
Martin


  

Betrifft: AW: Erklärung von: mopitz
Geschrieben am: 21.08.2008 13:47:10

Danke, habe verstanden!! ...heute habe ich wieder mal viel dazu gelernt :-)


  

Betrifft: AW: Top5 pos./neg selektieren von: heikoS
Geschrieben am: 21.08.2008 11:44:51

Hallo Nick,

das geht nach folgendem Muster:

Tabelle2

 ABCD
1TechnologieKundeUmsatzSpanne
2T1a331
3T1b112
4T2a205
5T2b226
6T3c504
7T4d301,5
8T4a402
9    
10TechnologieRangKunde 
11T11a 
12 2b 
13 3#ZAHL! 
14 4#ZAHL! 
15 5#ZAHL! 
16T21b 
17 2a 
18 3#ZAHL! 
19 4#ZAHL! 
20 5#ZAHL! 
21T31c 
22T41a 

Formeln der Tabelle
ZelleFormel
C11{=INDEX($B$2:$B$8;VERGLEICH(KGRÖSSTE(WENN($A$2:$A$8=$A$11;$C$2:$C$8); B11); $C$2:$C$8;0))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Den #ZAHL!-Fehler kannst Du umgehen, indem Du noch das Konstrukt wenn(istfehler(Formel);"";Formel) drum herum baust.

Mit der Funktion kkleinste() funktioniert das im Übrigen genauso.

Klappt´s?


Gruß Heiko

PS: Solltest Du bisher noch nichts oder wenig mit Matrixformeln zu tun gehabt haben, beachte den Link "Matrix verstehen".


  

Betrifft: AW: Top5 pos./neg selektieren von: mopitz
Geschrieben am: 21.08.2008 13:05:45

Hallo Heiko,
ich habe es versucht, aber ich habe deinen INDEX Formel nicht auf meinen Blatt umstricken können :-((
Ich habe eine Beispiel-Datei hochgeladen (im AW-Beitrag von Martin).

Gruß, mopitz


  

Betrifft: Form follows function! von: heikoS
Geschrieben am: 21.08.2008 13:20:00

Hallo Nick,

Du solltest Deine Daten in eine Datenbanklogik bringen und dann die verschiedenen angehübschten Darstellungen daraus füllen. Diese Datenbankdarstellung müßtest Du aber bereits haben, denn die Zahl -272,621787106249 in Zelle p8 ist ja vermutlich nicht wie Manna vom Himmel gefallen. ;-)

Ein Hinweis noch von mir: auf horizontal verbundene Zellen kann und sollte man jederzeit verzichten - die sorgen nur für Probleme.


Gruß Heiko


 

Beiträge aus den Excel-Beispielen zum Thema "Top5 pos./neg selektieren"