Office: Mehrere Bedingungen und 96 verschiedenen antworten

Hallo Leute,

mein Problem ist es wie in der Datei aufgeführt ist die Lösung zu bekommen.

Zur Aufgabe, ich soll eine Tabelle erstellen die mit verschiedenen Autos gespickt ist, die bei Aktivierung die Werte selbst auffüllen, was ich hin gekriegt habe, nun sollen wir allerdings noch die zu zahlende Summe "Jährlich" o. "monatlich" ausgeben, was die Schwierigkeit ist, da ich 6 verschiede Autos mit 5 verschiedenen Kriterien habe.

Kriterien:

1. Welches Auto, Reduzierung auf 16 Möglichkeiten
2. Monatlich, Jährlich, 8 Möglichkeiten
3. Kind unter 16 , 4 Möglichkeiten
4. Männliche Fahrer unter 23 Jahren 2 Möglichkeiten
5. Weiblich Fahrer unter 23 Jahren absolute Möglichkeit.

Vielen Dank für die Hilfe

Mfg

AndreE

 

Hallo!

Schau mal in Spalte C und teste, ob es so passt...

 

Vielen Dank,

funktioniert, nur hätte jetzt noch gern eine Auflösung wie man auf sowas kommt das wäre top.

Vielen Dank nochmal.

Mfg

AndreE

 

Hallo Andre -

Hier noch eine Lösung mit anderem Ansatz ohne Matrixformel.

 

Hallo!

Da dein Tabellenaufbau (um unseren Ex-Kanzler zu zitieren) "etwas suboptimal" ist, können die Formeln natürlich auch nicht alle kurz und knackig sein, da sie ja schliesslich die Hürden, die du aufgebaut hast, überwinden müssen...

Zur Erklärung:
Der INDEX sollte von seiner Funktionsweise her klar sein.
In deinem Fall muss man ihm aber genau erklären, in welchem Bereich er suchen soll. Dazu dient die Funktion BEREICH.VERSCHIEBEN, mit der Bereiche definiert werden können:

=BEREICH.VERSCHIEBEN(ObereLinkeEcke;Zeilenverschub;Spaltenverschub;BereichHöhe;BereichBreite)

Deine Bereiche haben alle dieselbe Höhe(6) und dieselbe Breite(1). Also muss "nur noch" der Verschub ausgehend von Tabelle2!A16 geregelt werden:
Der Spaltenverschub ist noch simpel: Wenn "jährlich", dann 4 Spalten, sonst kein Verschub.
Aufwändiger ist der Zeilenverschub: Da du (Gott sei Dank) deine Liste so aufgebaut hast, dass die Kriterien quasi absteigend sortiert sind,
JJJ=111=dual für 7
JJN=110=dual für 6
JNJ=101=5
JNN=100=4
NJJ=011=3
NJN=010=2
NNJ=001=1
NNN=000=0
werden mithilfe von
SUMME((B11:B13="Nein")*2^(3-ZEILE(1:3)))
die "Nein" dual gewertet:
Nein in B11 zählt 4=2^2
Nein in B12 zählt 2=2^1
Nein in B13 zählt 1=2^0
Deine unterschiedlichen Bereiche liegen in einem 7-zeiligen Abstand untereinander, daher noch die Multiplikation mit 7:
7*SUMME(...)
Nun hat Excel die nötigen Infos zusammen, um den richtigen Bereich zu lokalisieren.

Vielleicht bringt das etwas Licht ins Dunkel...

 

Hallo Krino!

Auch klasse, deine Lösung!
Auf die Idee, in dieser Art und Weise Namen zu definieren und vor allen Dingen abzufragen, bin ich überhaupt nicht gekommen.

 

Danke,

ja Krino mein Problem ist halt das ich das nur umsetzen kann/möchte wenn ich es verstehe, da ich das indirekt nicht ganz verstehe...Naja zur Lösung

=INDEX(BEREICH.VERSCHIEBEN(Tabelle2!A16;7*SUMME((B11:B13="Nein")*2^(3-ZEILE(1:3)));4*(B14="jährlich");6;1);D4)

ich markiere mal so wie ich denke es zusammen gehört,wo ich auch noch schwierigkeiten habe wie er das ins binäre umsetzt außerdem alles was rot markiert ist.

Danke für die Hilfe und Füllen der Wissenslücken.

Mfg

AndreE

 

Hallo Andre -

Diese Frage ist wohl Für Schatzi gedacht...

Was meine Bemühungen betrifft: Jede der 4 Abfragen (ja - nein - jährlich usw.) ergibt per WENN-Abfrage ein Stück Text. Bei "Nein" wird der Nullstring ausgegeben.

Die aneinandergereihten 16 möglichen Texte, z. B. "Fu23wMo", habe ich als NAMEN für die 16 Bereiche in Deiner Tabelle 2 vergeben. In der Formel wird ein solcher Name erzeugt. INDEX sucht in diesem Bereich und gibt den Inhalt der Zeile zurück, die über VERGLEICH ausgerechnet wird.

 

Hi,

ja stimmt wohl aber zu indirekt wie kann man den Befehl sinnvoll verstehen?

Mfg

AndreE

 

Also zu INDIREKT. Nehmen wir an, in D4 steht "Guten Morgen"; in E1 steht "D4", und in A1 schreibst Du =INDIREKT(E1). Als Ergebnis bekommst Du "Guten Morgen".

INDIREKT erwartet also als Input einen Bezug und gibt zurück, was dort gefunden wird. Das kann man verstehen, aber man fragt sich, was das soll. :roll:

Interesssant ist INDIREKT, weil man ja den Bezug nicht fertig in die Formel hineinschreiben muß, sondern dort ausrechnen lassen kann. Genau das habe ich über die Verkettung der Textstücke getan.

In meiner Formel wird der NAME eines Deiner 16 Auswahlbereiche in Tab 2 "ausgerechnet". Um den dann verwerten zu können, braucht man halt INDIREKT.

Mit NAMEN solltest Du Dich beschäftigen, falls noch nicht geschehen. Sie machen Formeln kürzer und leichter verständlich.

 

Hi,

also praktisch eine Staffelung

=INDEX(INDIREKT(WENN($B$8="Ja";"Ku16";"")&WENN($B$9="Ja";"Fu23m";"")&WENN($B$10="Ja";"Fu23w";"")&WENN($B$11="jährlich";"Jä";"Mo"));VERGLEICH(B1;Tabelle2!A2:A7;0))

d.h. Bedingung 1 erfüllt, Kind unter 16 = Tabelle Kind unter 16
2. Bedingung n. erfüllt kein mann aber 3. Bedingung erfüllt frau d.h. dann nicht mehr info aus der tabelle Ku16 sondern Fu23w... aber dann frage ich mich er gibt ja die richtigen ergebnis aus aber woher weiß er das fu23mFu23wKu16 das er da die Infos her bekommen soll?

Vielen Dank

Mfg

AndreE

 

Und hier kurz zur Erklärung der Summen-Matrixformel:

SUMME((B11:B13="Nein")*2^(3-ZEILE(1:3)))

ZEILE(1:3) ergibt ein Array der Zahlen 1 bis drei, also {1;2;3}
3-ZEILE(1:3) ergibt also logischerweise {2;1;0}
2^(3-ZEILE(1:3)) ergibt dann das Array {4;2;1}
B11:B13="Nein" ergibt ein Array aus WAHR oder FALSCH, je nachdem, ob "Nein" drinsteht oder nicht, also bei
B11="Ja"
B12="Nein"
B13="Nein"
(für B14 nehme ich hier mal "monatlich" an)
ergibt sich das Array {FALSCH;WAHR;WAHR}
Bei der Multiplikation dieses Arrays mit dem Array {4;2;1} wird FALSCH zu einer "0" und WAHR zu einer "1", also
{0;1;1}*{4;2;1}={0*4;1*2;1*1}={0;2;1}
Die Summe dieses Arrays beträgt 0+2+1=3
Mit 7 multipliziert also 21
Also: Von Tabelle2!A16 aus wird ein Bereich aufgespannt, der 21 Zeilen weiter unten liegt, keinen Spaltenverschub hat (da B14<>"jährlich"), 6 Zeilen hoch und 1 Spalte breit ist, ergo der Bereich Tabelle2!A37:A42

 

Staffelung klingt so nach Rangfolge, das ist es nicht, ich würde eher von Verkettung sprechen.

Klicke mal versuchsweise auf den Auswahlpfeil des Fensterchens links oben, wo normalerweise A1, GM233, C12 und so etwas drinsteht. Klicke dann auf einen der angebotenen Namen, dann markiert Excel den Bereich in Tab 2, der eben diesen Namen trägt (weil ich ihn vorher so festgelegt habe).

Wie gesagt rechnet sich Excel einen dieser Namen anhand Deiner vier Dropdown-Optionen aus und weiß dann, wo gesucht werden soll.

 

Sooo,

also ich habe mir das nochmal angeguckt verstehen ist bei mir ne andere definition

=INDEX(INDIREKT(WENN(B13="Ja";"Ku16";"")&WENN(B14="Ja";"Fu23m";"")&WENN(B15="Ja";"Fu23w";"")&WENN(B16="jährlich";"Jä";"Mo"));VERGLEICH(B4;Tabelle2!A2:A7;0))

das beudeutet von links nach rechts das er in dem Feld B13 guckt und sieht wenn es ein tritt ist das Feld Ku16 ein wenn B14 wahrheitsfeld eintrifft wird das feld ku16fu23m in betracht gezogen wenn im feld b15 ja steht ku16fu23mfu23w und monatlich oder jährlich ku16fu23mfu23wMo so und da fängt es an bei mir das verständnis aufzuhören, meine frage woher weiss er das er sobald b14 ja und b15 ja ist das er das feld fu23mfu23wmo o. jä zu nehmen das habe ich ihm nie gesagt, das ist mein problem wenn ich das verstehe, denke würde ich das auch mir selbst erklären können.

Vielen Dank

Mfg

AndreE

 

Hallo Andre -

Du bist kurz vor dem Durchbruch. :-) Hast Du denn auch die &-Zeichen gesehen? Die sind entscheidend und bewirken, daß Texte aneinandergereiht werden.

Beispiel. In A1 steht "Joseph". In A2 steht "Fischer". In A3 steht die Formel =A1&" "&A2. Das ist eine Kette von 3 Texten: einmal der Text in A1, dann ein Zwischenraum, dann der Text in A2. Heraus kommt "Joseph Fischer".

Also das Einzige, was diese ganzen WENNs tun, ist eine Kette von Zeichen zu produzieren. Damit begibt sich Excel auf die Reise zu Tab 2 und versucht dort einen gleichartigen Namen zu finden (einen von 16 verschiedenen). Die NEINs ergeben in meiner Formel jeweils einen "Nulltext", also gar kein Zeichen (ausgedrückt durch die doppelten Gänsefüßchen "")

Ist das halbwegs verständlich?

Es ist aber schon okay, daß Du nachhakst, dafür ist so ein Forum ja schließlich da. Formeln zu benutzen, die man nicht nachvollziehen kann, ist gefährlich, denn wenn mal was nicht klappt, steht man sofort auf dem Schlauch.