ich habe ein (für mich) komplexes Formelproblem zu lösen und komme leider nicht weiter.
Der Erklärung vorangeschickt eine Skizze und Musterdatei, anhand derer die Beschreibung des Problems - und was errechnet werden soll - einfacher ist. Ist zwar ein wenig ausführlicher, aber besser als wenn ohne Details Missverständnisse auftreten.
Skizze:
Musterdatei: https://www.herber.de/bbs/user/94681.xlsx
Ausgangslage:
Es gibt eine Basistabelle mit 5 Spalten á 538 folgenden Zeilenwerten.
Die erste Spalte stellt enthält lediglich ein "j" oder "nein" als Voraussetzung, mit welchen Zeilen später überhaupt gerechnet werden darf (nähere Erklärung gleich).
Die restlichen vier Spalten enthalt allesamt Zahlenwerte. Jede Spalte trägt eine Überschrift A, B, C und D.
Manuelle Vorgaben für die spätere Berechnung (rot markiert):
1. Festlegung der Reihenfolge der Spalten nach denen berechnet werden soll (Zellen J2:J5 abfolgend von 1 bis 4).
2. Für die erste Rechenreihe-Spaltenüberschrift der Berechnung eine weitere Bedingung: ein Minimumwert. (Zelle K2)
3. Für alle drei folgenden Spalten ein maximaler Prozentwert (mehr dazu gleich).
Was soll errechnet werden?
Gelb markiert sind die Felder für die ich Formelansätze suche (Zellen N3 bis N5).
Folgende Werte sollen berechnet werden:
1. Nur Verwendung von Zeilen, in denen ein "j" in Spalte 1 steht.
In der Musterdatei gibt es genau 538 Zeilen, von denen 356 ein "j" besitzen (grün markiert).
Informativ - Prozentual bedeutet dies: Jeder Wert mit dem überhaupt gerechnet werden soll, enspricht 1/356 (also ca. 0,0028089). Oder als Formel: "=1/Zählenwenn(A2:A539;"j")".
2. Für die weitere Berechnung wird in Zelle K2 ein Minimumwert definiert. Alle Werte größergleich diesem Wert aus der ersten Spaltenüberschrift (im Beispiel "C") sind von Interesse.
Im folgenden Rechenschritt Nr. 3. soll nur noch mit den Zeilen gearbeitet werden, wo in der Spalte A ein "j" steht UND in der ersten Spaltenübschrift "C" alle Werte größergleich dem Miniumwert aus Zelle K2.
Im Beispiel gibt es exakt 38 Werte in der Spaltenüberschrift "C", die größergleich den definierten "13,00" aus Zelle K2 sind (das entspricht 10,67% aller Werte mit "j").
Der Übersichtlichkeit halber sind diese verbleibenden Werte in der Musterdatei blau markiert.
Per Autofilter sähe das so aus:
3. Jetzt wirds kniffliger...
Die zweite Spaltenüberschrift mit der laut Vorgabe gerechnet werden soll, ist "A". Für die Berechnung verwendet werden dürfen nur die verbleibenden 38 Zeilen aus Rechenschritt 1.+2.
Das Ganze erfolgt mit dem Ziel: Mit der Vorgabe in Zelle K3 gebe ich manuell vor, dass nach der nächsten Filterung mindestens 7,02% Restzeilen aus allen "j"-Kriterien erhalten bleiben sollen; dies entspricht mithin absolut 25 Werten (7,02% von 356 "j"-Werten ist gleich 25).
Es soll nun der kleinste aus den höchsten Werten aus Spaltenüberschrift "A" ermittelt werden, welcher dies gewährleistet.
Dies bedeutet, man kürzt resultierend aus der 7,02%-Vorgabe aus den verbleibenden 38 Werten aus Rechenschritt Nr. 2 insgesamt die niedrigsten 13 Werte weg (da 38-25 = 13).Der niedrigste der höchsten 7,02% Restwerte wäre demnach im Beispiel 7,28.
Per Autofilter sähe das wieder so aus:
4. Als nächstes folgt laut manueller Vorgabe die Spaltenüberschrift "D" mit dem vorgegebenen Restprozentwert aus Zelle K4 äquivalent zu Rechenschritt Nr. 3.
Gleiches dann bei Spaltenüberschrift "B" mit dem vorgegebenen Restprozentwert aus Zelle K5.
Wichtig: Es darf jeweils immer nur aus den "Restzeilen" des vorherigen Rechenschrittes weggekürzt werden.
(Ergänzung: Beließe man z. B. die Vorgabe der Zellen K4 und K5 im Musterbeispiel bei 7,02% oder höher, so würde im Umkehrschluss nur der Minimumwert aus den restlichen 25 Zeilenwerten nach Rechenschritt 3. ausgegeben werden, weil die Reihenanzahl 25 ja erhalten werden müsste.)
Die zusätzliche "Kunst" der Aufgabe ist, dass man eben in J2:J5 die Reihenfolgen der Spaltenüberschriften bei der Berechnung auch tauschen darf.
Natürlich darf jede Spaltenüberschrift von A bis D nur einmal vorkommen.
Für einen Lösungsansatz wären Hilfsspalten(-tabellen) natürlich kein Problem.
Hat jemand eine Idee, wie ein solcher Ansatz aussehen könnte?
Ich könnte mir vorstellen, ein Mix aus Summenprodukt + Max + Rank, aber auf die "Zusammensetzung" komme ich leider nicht :-(
Liebe Grüße, Joerschi
PS: Ein gesundes neues 2015 noch allen!