Excel - Zeilen+Spalten zur Ermittlung eines Wertes

[vulgo]

In einer Tabelle stehen in Spalten die Werte für die Punkte 1-4 anhand derer dann der Wert 5 ermittelt werden soll.

in einer Zweiten entsprechende Paramter aus denen sich der Wert ergibt

Es "fühlt" sich an, als ob das mit Verweise funktionieren könnte, doch wie fasse ich das in eine Formel zusammen die Wert 5 ausgibt?



Danke für eure Hilfe

 

[King Domi]

Bin mir nicht sicher ob ich das richtig verstehe. Du hast das Ergebnis und die Werte und willst die Formel wissen?

 

[CHaos.Gentle]

Zuerst einmal ein großes Lob an dich für die ausführliche Erläuterung der Fragestellung.

Die Frage an sich ist mal wirklich kniffelig. Mit VBA sicherlich einfach zu lösen, mit Boardmitteln nicht trivial.

Im ersten Schritt bietet sich natürlich ein Verweis an. Nun haben wir aber zuerst einen Verweis mit mehreren Kriterien und zusätzlich noch einen Verweis auf Suchspalten und Suchzeilen. Also brauchen wir einen SVERWEIS mit mehreren Kriterien und irgendwas, was uns vorher die richtige Ergebnisspalte ausgibt.

=SVERWEIS(A3&$A$1&B3;WAHL({1.2.3};Tabelle2!C2:C10&Tabelle2!D2:D10&Tabelle2!E2:E10;INDIREKT("Tabelle2!"&ADRESSE(2;VERGLEICH(C3;Tabelle2!A1:G1;0))):INDIREKT("Tabelle2!"&ADRESSE(10;VERGLEICH(C3;Tabelle2!A1:G1;0))));2;0)

So, das ist erstmal das Ergebnis. War doch recht einfach...
Zerlegen wir die Formel doch mal von Innen.

VERGLEICH(C3;Tabelle2!A1:G1;0)

Sucht in Tabelle2 in der ersten Zeile nach dem Wert aus C3 und gibt die Spaltennummer zurück. Hiermit lösen wir die Kombination aus horizontaler und vertikaler Kriterienkombination auf. Passiert zwei mal, einmal für die Startzelle und einmal für die Endzelle. In deinem Fall, deine Spalten F bis K auf Blatt 2.

INDIREKT("Tabelle2!"&ADRESSE(2;VERGLEICH(C3;Tabelle2!A1:G1;0)))

Nutzt den Bezug aus der oberen Formel für die Erzeugung eines Zellebezugs (Start der Ergebnisspalte). Wird nochmal für das Ende der Ergebnisspalte benötigt. Die 2 durch die gewünschte Startzeile ersetzen...die 10 in der zweiten Formel durch die gewünschte Endzeile. Hier musst du noch selbst Hand anlegen, sonst werden nur die Zeilen 1-10 betrachtet

INDIREKT("Tabelle2!"&ADRESSE(2;VERGLEICH(C3;Tabelle2!A1:G1;0))):INDIREKT("Tabelle2!"&ADRESSE(10;VERGLEICH(C3;Tabelle2!A1:G1;0)))

Dieses Konstrukt erzeugt also nichts weiter als die Ergebnis-Suchspalte für den SVerweis.

SVERWEIS(A3&$A$1&B3;WAHL({1.2.3};Tabelle2!C2:C10&Tabelle2!D2:D10&Tabelle2!E2:E10;[...]

Ein fast normaler SVERWEIS. Außer das wir durch das nutzen von WAHL eine Verkettung der Kriterien erzeugen. Somit ist es möglich, in einem SVERWEIS mehrere Kriterien unterzubringen.
Auch hier wieder Start- und Endzeile also die 2en und 10en wie benötigt anpassen.


Das Ganze ist eine Matrixfunktion und wird nach jeder Bearbeitung durch STRG+SHIFT+ENTER bestätigt werden müssen.
Auch noch wichtig: Es kann sein, dass beim herunterziehen der Formel auf alle Zellen die Zellbezüge kaputtgehen. In diesem Fall dann absoluten Bezüge (alles hinter dem Semikolon nach der WAHL-Formel) nochmal durch F4 als absolute Bezüge kennzeichnen.

Ich bin da jetzt zu Faul zu

 

[vulgo]

das ist doch um einige ecken komplizierter als gedacht. danke für deine großartige Hilfe. Ich werde mir das ganze am Montag in aller Ruhe ansehen und hoffe, dass ich die Formeln verstehe
was meinst du mit dem Fetten Absatz der MAtrixfunktion?

Dass ich in Tabelle 1 nach jeder bearbeitung STRG+SHIFT+ENTER drücken muss oder nur einmal oder nach bearbeitung der formeln?

 

[CHaos.Gentle]

Ne Matrixformel funktioniert nur, wenn man nach der Bearbeitung der Formel die Bearbeitung mit STRG + SHIFT + ENTER beendet.

Hier hab ich im Übrigen noch etwas vergessen.

VERGLEICH(C3;Tabelle2!A1:G1;0)

Das G1 musst du noch durch die letzte Spalte ersetzen in der du deine 5-35 usw stehen hast...A1 dabei nicht anpassen.

 

[vulgo]

Danke für deine Hilfe. Ich habs mir heute angesehen, sogar verstanden und erfolgreich angepasst > funktioniert gut

=SVERWEIS(F23&$G$13&E23;WAHL({1.2.3};LTB!$A$2:$A$13&LTB!$B$2:$B$13&LTB!$C$2:$C$13;INDIREKT("LTB!"&ADRESSE(2;VERGLEICH(K23;LTB!$A$1:$J$1;0))):INDIREKT("LTB!"&ADRESSE(13;VERGLEICH(K23;LTB!$A$1:$J$1;0))));2;0)

mit Abfrage auf "-" und 0

=WENN(UND(K23>0;G23<>"-";G23>0);SVERWEIS(F23&$G$13&E23;WAHL({1.2.3};LTB!$A$2:$A$13&LTB!$B$2:$B$13&LTB!$C$2:$C$13;INDIREKT("LTB!"&ADRESSE(2;VERGLEICH(K23;LTB!$A$1:$J$1;0))):INDIREKT("LTB!"&ADRESSE(13;VERGLEICH(K23;LTB!$A$1:$J$1;0))));2;0);0)