Liebe Excel-Spezialisten,
ich habe da eine echte Herausforderung, bei der ich auf Eure Ideen und Unterstützung bei der Umsetzung angewiesen bin, um mir die mühsame (und wiederkehrende) manuelle Auswertung einer Tabelle zu sparen, und ich hoffe, dass Ihr dabei helfen könnt.
In Spalte A steht das Datum, jede Zeile repräsentiert einen Monat (zumindest in dieser Tabelle, es gibt auch noch eine, da sind es Wochen-Abstände).
Beginnend ab D stehen in jeder zweiten Spalte verschiedene Prozentwerte, meist irgendwas zwischen -5% und +5%. Derzeit reicht es von Zeile 2 bis 159, aber im Laufe der Zeit (jeden Monat) erfolgt eine Ergänzung, wobei neue Zeilen oben (Zeile 2) eingefügt werden und die anderen Zeilen nach unten rutschen. Derzeit sind die Spalten bis AB belegt, weil es 13 Datenspalten plus Prozentwert-Spalten gibt. Es werden in Kürze mehr, die Logik ist aber immer gleich. In der ersten der zwei Spalten stehen die Beträge, in der zweiten die Prozentwerte dazu, die ich prüfen will.
Ich möchte nun innerhalb jeder Zeile automatisiert die Top 5, also die Spalten mit den höchsten fünf Werten, ermitteln und entsprechend ihrer Reihenfolge jeweils mit einer bestimmten Farbe markieren.
Nach meinem Verständnis ist so eine Anforderung mit der Funktion "Bedingte Formatierung" nicht möglich, da die konkreten Zellinhalte erstens völlig unterschiedlich zueinander innerhalb der Zeile sind und zweitens in keinerlei Beziehung zu etwas stehen, das man als Kriterium heranziehen könnte. Zudem müsste ich für jede Zeile ggf. andere Kriterien definieren, weil sich die Werte von Zeile zu Zeile natürlich auch unterscheiden.
Insofern gehe ich mal davon aus, dass man es mit VBA lösen kann bzw. muss. Ich würde mir vorstellen, dass man die gewünschten Farbcodes als Variablen definiert, damit man sie leicht anpassen kann, dann die Anzahl der belegten Zeilen ermittelt (noch besser wäre es, den Bereich vom Nutzer abzufragen), in einer Schleife für jede Zeile die Spalten im Zweier-Abstand durchgeht, die Werte ausliest und zusammen mit ihrer Position in einem Array speichert, die Reihenfolge / Wertigkeit ermittelt und dann die Farb-Codes für die höchsten fünf Werte zuordnet.
In der Spalte neben der letzten Prozentwert-Spalte soll das Script die Überschriften der Top 5-Spalten einfügen.
In der letzten Spalte soll dann durch das Script eine Formel gebildet und eingefügt werden, die sich aus den ermittelten Zellen zusammensetzt und die Prozentwerte mit einem Faktor multipliziert, der an einer geeigneten Stelle abgelegt (oder durch das Script vom Nutzer abgefragt wird). Ich will durch Summe über alle Zeilen in Erfahrung bringen, ob die Top 3 reicht oder ob ich besser die Top 5 pro Zeile nehmen muss, oder gar die Top 8 um das beste Ergebnis zu erzielen.
Ich hänge mal einen Auszug der Tabelle an, in der ich mit einer Zeile manuell das vorgenommen habe, was das Script tun soll:
https://www.herber.de/bbs/user/112388.xlsx
Im zweiten Registerblatt habe ich die Matrix aufgebaut, die nach meinem Verständnis vom Script (im Speicher) gebildet werden müsste, um die Top 5 zu ermitteln. Ich lasse mich aber auch gerne eines Besseren belehren.
Einen Teil davon würde ich sicher hinbekommen und irgendwann würde ich mir das Ganze vielleicht auch zusammenstückeln und zum Laufen bringen, aber das ist wenig effektiv, da ich noch etliche weitere Dinge mit dieser Tabelle vorhabe, das einiges an Zeit fressen wird.
Daher denke ich, ich stelle es hier mal ein und schaue einfach mal, ob nicht jemand etwas (einigermaßen) Passendes "aus dem Hut zaubern" kann, weil er / sie etwas in dieser Richtung schon mal umgesetzt hat - ggf. teilweise oder ähnlich, so dass es adaptiert werden kann. Vielleicht gibt es ja auch noch einen eleganteren Ansatz als den von mit geschilderten, an den ich bisher noch nicht gedacht habe.
Ich bin gespannt und freue mich auf konstruktive Vorschläge. Vielen Dank im Voraus!
Viele Grüße
Frank