Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.06.2024 10:26:31
25.06.2024 08:19:20
Anzeige
Archiv - Navigation
1400to1404
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Filterung mehrerer Spalten per Formeln + Wertermit

Filterung mehrerer Spalten per Formeln + Wertermit
01.01.2015 18:10:55
Joerschi
Hallo liebes Forum,
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:
Userbild
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:
Userbild
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:
Userbild
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!

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Filterung mehrerer Spalten per Formeln + Wertermit
02.01.2015 04:07:00
Joerschi
Ergänzung, weil grad gefunden:
Für Rechenschritt 1 könnte dies hier Lösung sein:
http://www.excelformeln.de/formeln.html?welcher=89
(Werte aus Spalte herausfiltern (Autofilter per Formel))
Aber für den Rest?
Viele Grüße
Joerschi

AW: Filterung mehrerer Spalten per Formeln + Wertermit
02.01.2015 11:14:04
Christian
Hallo Joerschi,
einfach mal straight forward mit Hilfsspalten:
zunächst müsstest du den Bezug der Überschriften in J2 bis J5 zu der Werten in Spalte B bis E erstellen.
zB. in P1: =INDEX($B$1:$E$539;ZEILE();VERGLEICH($J$2;$B$1:$E$1;0)) und bis P539 nach unten ziehen.
Das Gleich in Q1 für J3, usw.
Dann mit der von dir genannten Formel aus http://www.excelformeln.de/formeln.html?welcher=89
Bsp:
in U1 die Matrixformel:
{=WENNFEHLER(INDEX(Q$1:Q$539;KKLEINSTE(WENN((A$1:A$539="j")*(P$1:P$539>=$K$2);ZEILE($1:$539)); ZEILE()));"") }
nach unten ziehen bis U539
Das ergibt die Liste der 38 Werte aus Spalte Q, wenn in Spalte A ein "j" steht und der Wert in Spalte P >=13,00 ist.
Diese kannst du sortieren mit:
in V1 die Formel:
=KGRÖSSTE($U$1:$U$539;ZEILE())
Davon holst du dir den 25. Wert mit
=INDEX($V$1:$V$539;RUNDEN(ZÄHLENWENN(A2:A539;"j")*K3;))
Das lässt sich sicherlich noch kompakter gestalten ...
Gruß
Christian

Anzeige
AW: Filterung mehrerer Spalten per Formeln + Wertermit
02.01.2015 11:18:24
Joerschi
Hallo Christian,
vielen Dank - gefällt mir gut.
Werde ich gleich mal reinarbeiten und bisschen testen. Man will ja auch draus lernen :-)
Danke Dir ganz herzlich.
Liebe Grüße
Joerschi

318 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige