Verschachtelung der Funktion BEREICH.VERSCHIEBEN

[OversoulSeven]

Ein herzliches Hallo! und meine besten Grüsse an alle in diesem Forum! Ich bin hier Neuling und schneie gleich mal mit einer Knacknuss rein, denn ...

... ich verstehe nicht, weshalb eine Verschachtelung der Funktion BEREICH.VERSCHIEBEN in einer WENN-Formel nicht wie gewohnt funktioniert bzw. wo in nachstehender Formel der oder die Fehler liegen.

Der grün dargestellte Bereich dieser WENN-Formel funktioniert immer perfekt. Wird in Zelle B$3 aber der Buchstabe B, C oder D eingegeben (rot dargestellter Bereich), gibt die Formel den Wert FEHLER zurück.

=WENN(B$2=0;WENN(B$3="A";Tabellen!B$4;WENN(B$3="B";Tabellen!F$4;WENN(B$3="C";Tabellen!J$4;WENN(B$3="D";Tabellen!N$4))));
WENN(B$3="A";BEREICH.VERSCHIEBEN(Tabellen!B$4;Tabellen!C4;
WENN(B$3="B";BEREICH.VERSCHIEBEN(Tabellen!F$4;Tabellen!G4;
WENN(B$3="C";BEREICH.VERSCHIEBEN(Tabellen!J$4;Tabellen!K4;
WENN(B$3="D";BEREICH.VERSCHIEBEN(Tabellen!N$4;Tabellen!O4;
B$4)))))))))

Kann mir ein liebes Excel-Genie hier bitte auf die Sprünge helfen?

Ich füge hier zwecks besserem Verständnis die Datei
  TestBeispielDatei.xlsx (Größe: 14,52 KB / Downloads: 9) bei, und arbeite mit Windows 11 Home (Version 22H2) und MS Excel 2019 MSO (Version 2301 Build 16.0.16026.20002) 64 Bit.

[LCohen]

teste mal:

=WENN(B$2=0;WENN(B$3="A";Tabellen!B$4;WENN(B$3="B";Tabellen!F$4;WENN(B$3="C";Tabellen!J$4;WENN(B$3="D";Tabellen!N$4))));
WENN(B$3="A";BEREICH.VERSCHIEBEN(Tabellen!B$4;Tabellen!C4);
WENN(B$3="B";BEREICH.VERSCHIEBEN(Tabellen!F$4;Tabellen!G4);
WENN(B$3="C";BEREICH.VERSCHIEBEN(Tabellen!J$4;Tabellen!K4);
WENN(B$3="D";BEREICH.VERSCHIEBEN(Tabellen!N$4;Tabellen!O4);
B$4)))))

Ansonsten verwendet man BEREICH.VERSCHIEBEN nur selten; hier wäre INDEX angeraten.

[BoskoBiati]

Hallo,

1. gibt es keine Lösung für Faktor 1
2. welche Ergebnisse erwartest Du in den einzelnen Zellen in E2:E15?

Versuch:

Code:

=INDEX(Tabellen!$B$4:$O$17;ZEILE(A1:A13);(CODE(GROSS(B3))-65)*4+1)

Ich weiß allerdings nicht, was der Faktor für eine Bedeutung hat.

[shift-del]

Moin OversoulSeven

Kürzer als deine aber länger als die von BoskoBiati. Wobei seine Formel andere Ergebnisse als dein Original ergeben.

Code:

=INDEX(WAHL(VERGLEICH($B$3;{"A";"B";"C";"D"};0);Tabellen!$B$4:$B$18;Tabellen!$F$4:$F$18;Tabellen!$J$4:$J$18;Tabellen!$N$4:$N$18);WENN($B$2=0;1;ZEILE(A2)))

[BoskoBiati]

Hi Shift-del,

ich glaube ich habe das Ganze verstanden:



  TestBeispielDatei.xlsx (Größe: 15,05 KB / Downloads: 2)

Meine Formel, angepasst:

Code:

=INDEX(Tabellen!$B$4:$O$17;WAHL(B$2+1;1;ZEILE(A1:A14));(CODE(GROSS(B3))-65)*4+1)

Formel des TE, verbessert:

Code:

=WENN(B$2=0;WENN(B$3="A";Tabellen!B$4;WENN(B$3="B";Tabellen!F$4;WENN(B$3="C";Tabellen!J$4;WENN(B$3="D";Tabellen!N$4))));WENN(B$3="A";BEREICH.VERSCHIEBEN(Tabellen!B$4;Tabellen!C4-1;;14);WENN(B$3="B";BEREICH.VERSCHIEBEN(Tabellen!F$4;Tabellen!G4-1;;14);WENN(B$3="C";BEREICH.VERSCHIEBEN(Tabellen!J$4;Tabellen!K4-1;;14);WENN(B$3="D";BEREICH.VERSCHIEBEN(Tabellen!N$4;Tabellen!O4-1;;14);B$4)))))

Gerade festgestellt, dass der TE schon weitergezogen ist:
https://www.ms-office-forum.net/forum/sh...p?t=384765

[OversoulSeven]

teste mal (...)

Hatte ich so auch schon versucht, diese Formel ergibt aber dieselbe Fehlermeldung. Danke dir trotzdem bestens und teste jetzt zuerst mal all die Lösungsvorschläge der anderen Forenmitglieder.

[26865]

Wenn ich mal den Groll gegen uneinsichtige Crossposter außen vor lasse...

Code:

=WENN(
   $B$2=0;
   INDEX(Tabellen!$4:$4;SUCHEN($B$3;"ABCD")*4-2);
   INDEX(Tabellen!5:5;SUCHEN($B$3;"ABCD")*4-2))

Nebenbei:

---

...diese Formel ergibt aber dieselbe Fehlermeldung...

Die Rückgabe "FALSCH" ist keine Fehlermeldung, sondern typischerweise ein Indiz dafür, dass ein FALSCH-Ergebnis bei Wenn-Funktionen fehlt (3. Argument), was bei solchen Wenn-Monstern häufig passiert. Bei dir ist es gleich an ganzer Haufen, da deine Bereich.Verschieben-Konstrukte nicht abgeschlossen sind, sondern einfach "enden" und nahtlos in die nächste Wenn-Funktion übergehen. Das richtige Interpretieren eines Rückgabewerte kann helfen, das Problem zu lokalisieren.

Im Übrigen ist eine ordentliche textliche Beschreibung, was man mit einer Funktion erreichen will und wie das Wunschergebnis aussehen soll, immer sinnvoller als die Zeit damit zu verschwenden, den gleichen substanzlosen Quark über zig Foren zu verbreiten.

[BoskoBiati]

Hi EarlFred,

die  Formel gibt aber andere Ergebnisse:

   

in E:

Code:

=INDEX(Tabellen!$B$4:$O$17;WAHL(B$2+1;1;ZEILE(A1:A14));(CODE(GROSS(B3))-65)*4+1)

in F:

Code:

=WENN(B$2=0;WENN(B$3="A";Tabellen!B$4;WENN(B$3="B";Tabellen!F$4;WENN(B$3="C";Tabellen!J$4;WENN(B$3="D";Tabellen!N$4))));WENN(B$3="A";BEREICH.VERSCHIEBEN(Tabellen!B$4;Tabellen!C4-1;;14);WENN(B$3="B";BEREICH.VERSCHIEBEN(Tabellen!F$4;Tabellen!G4-1;;14);WENN(B$3="C";BEREICH.VERSCHIEBEN(Tabellen!J$4;Tabellen!K4-1;;14);WENN(B$3="D";BEREICH.VERSCHIEBEN(Tabellen!N$4;Tabellen!O4-1;;14);B$4)))))

in G:

Code:

=WENN($B$2=0;INDEX(Tabellen!$4:$4;SUCHEN($B$3;"ABCD")*4-2);INDEX(Tabellen!5:5;SUCHEN($B$3;"ABCD")*4-2))

die spillt auch nicht.

[26865]

die  Formel gibt aber andere Ergebnisse:

Ich habe mich an den Ergebnissen der Mustermappe orientiert, die der TE bei MOF (und inhaltsgleich hier) gepostet hat - da passt es!

12
13
14
7
12
18
25
67
95
180
350
800
1200
0

Der TE schreib ja, für "A" passten die Ergebnisse. Egal, wäre in jede Richtung schnell änderbar.

Nun, meine Schelte in Richtung des TE gilt damit umso mehr: Zeit in die Aufgabenstellung investieren und Klarheit schaffen anstelle halbgaren Mist über das Netz zu streuen!

[BoskoBiati]

Hi,

Du hast recht, sowohl was die Ergebnisse als auch das Ändern der Formeln betrifft.

Dann sähe das so aus:

Code:

=INDEX(Tabellen!$B$4:$O$17;WAHL(B$2+1;1;ZEILE(A2:A15));(CODE(GROSS(B3))-65)*4+1)

oder so:

Code:

=WENN(B$2=0;WENN(B$3="A";Tabellen!B$4;WENN(B$3="B";Tabellen!F$4;WENN(B$3="C";Tabellen!J$4;WENN(B$3="D";Tabellen!N$4))));WENN(B$3="A";BEREICH.VERSCHIEBEN(Tabellen!B$4;Tabellen!C4;;14);WENN(B$3="B";BEREICH.VERSCHIEBEN(Tabellen!F$4;Tabellen!G4;;14);WENN(B$3="C";BEREICH.VERSCHIEBEN(Tabellen!J$4;Tabellen!K4;;14);WENN(B$3="D";BEREICH.VERSCHIEBEN(Tabellen!N$4;Tabellen!O4;;14);B$4)))))