Hilfe bei Formeln verschlanken/Datentabelle ersetz
24.09.2015 17:22:39
Joerschi
ich habe eine Art Abfrage, wo ich Berechnungen in meiner Excel deutlich verschlanken möchte bzw. eine Tabelle ersetzen - komme aber nicht weiter.
Zur Erklärung eine Skizze (+Musterdatei) und unten dann die Beschreibung, was ausgerechnet werden soll ( Markierungen in der Skizze werden ebenfalls unten erklärt):
https://www.herber.de/bbs/user/100377.xlsx
Es gibt eine fixe Datentabelle Nr. 3 von D33:F150 mit Zahlenwerten.
Außerdem gibt eine Zahlenwert-Tabelle Nr. 1 von D3:J13 (ebenso fixe Zahlenwerte).
Desweiteren gibt es eine Datentabelle 2 (welche in der später gesuchten Lösung entfallen soll!), in der aktuell zur Erklärung folgendes ausgerechnet wird - am Beispiel: Zelle D18 rechnet die Anzahl aller Werte aus, welche in der Datentabelle Nr. 3 in der Spalte D (D33:D150) zwischen dem Werten D5 und D6 liegen - unter der Bedingung, dass in der Bedingungsspalte L je eine "1" steht.
Also sind in dem Beispiel genau 5 Werte größergleich 1,77 und kleiner gleich 3,54.
Das Ganze ist mit Summenprodukt leicht gelöst und nicht das gesuchte Problem.
Was passiert in Datentabelle Nr. 2?:
In Zelle K28 wird manuell ein maximaler Zahlenwert vorgegeben - im Beispiel die "2".
Grafisch sind in der Skizze in der Datentabelle Nr. 2 alle Werte markiert, die kleinergleich der "2" aus Zelle K28 sind. Ausgenommen sind die Nullen, welche stets ignoriert werden sollen.
Folgende Daten würde ich nun gern per Formel (OHNE die Hilfe von Datentabelle Nr. 2) gelöst haben:
Wieder Skizze dazu:
Zum Verstehen muss dabei die Datentabelle Nr. 2 vorher betrachtet werden. Stets sind dabei die gelb markierten Werte gesucht (nachfolgend "BASISWERTE" genannt) - und zwar von oben nach unten betrachtet. Nullen sollen ignoriert werden.
Falls außerdem untereinander mehrere Werte das Kriterium "kleinergleich Zelle K28" erfüllen, so interessiert nur der obere davon (also wenn z. B. in Zelle D17 sowie D18 eine "1" stünde - dann interessiert nur D17).
1. - gesucht in Zelle D153:
Den Basiswert aus Spalte D (unter Bedingung der Zelle K28), ohne dass es dazu die Datentabelle Nr. 2 benötigt.
Die Schwierigkeit darin ist, dass eben ohne die Datentabelle Nr. 2 die Formel erkennen muss, wann zuerst von oben gesehen der Basiswert eintritt und welches er ist (wieder per Summenprodukt?).
2. - gesucht in Zelle D154:
Den zugehörigen höheren Wert der Datentabelle Nr. 1 zum Basiswert aus Lösung Nr. 1 (Beispiel für Zelle D17, welche die Anzahl größergleich D4 und D5 ausgibt --- es soll davon der zahlenmäßig höhere Wert ausgegeben werden, also D5)
3. - gesucht in Zelle D155:
Die zugehörige Zeilenüberschrift der Datentabelle Nr. 1 des Lösungswertes von Lösung Nr. 2.
Die richtigen Beispiellösungen sind in der zweiten Skizee bereits kursiv eingetragen.
Wichtig: Alle drei Lösungen sollen unabhängig voneinander sein - also sollen sich formeltechnisch lediglich auf Datentabelle Nr. 1&3 beziehen und sich nicht aufeinander beziehen.
Gibt es in einer Spalte keinen Basiswert, auf den die Bedingung "kleinergleich Zelle K28" zutrifft, bleibt die Ergebniszelle leer.
Bei Unklarheiten bitte gern fragen :-)
Ich hoffe, ich habe alles richtig zusammen bekommen nach Kontrollieren. Müsste aber passen...
Danke im voraus für Eure Hilfe und Liebe Grüße
joerschi
