Verschachtelung der Funktion BEREICH.VERSCHIEBEN

[newcomer]

Hallo,

hab die vier Tabellen als Tabellen formatiert und tabA, tabB, tabC, tabD umbenannt.

Auswertungen E2:

=WENNFEHLER(INDEX((tabA;tabB;tabC;tabD);ZEILE(A1)+$B$2;1;VERGLEICH(Auswertungen!$B$3;{"A";"B";"C";"D"};0));0)


Bin nicht sicher, ob das Dein Wunschergebnis ist, einfach mal ausprobieren.

Gruß


Christian

[OversoulSeven]

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)))

Ich habe deinen Formelvorschlag in der Beispieldatei wie auch in meiner eigentlichen, ungleich viel komplexeren Exceldatei durchgetestet.

Nun, ist der Faktor 1, funktioniert alles perfekt, so wie es sollte. Wenn der Faktor hingegen 0 ist, wird in jeder Zeile der Spalte "Werte" der Wert in der Zeile 4 der Tabellen des Registers "Tabellen" übernommen, der ganze Rest funktioniert bestens. Daher konnte ich deine Formel leider nicht übernehmen. Trotzdem vielen Dank für deine Bemühung!

Ich habe, nebenbei erwähnt, die Lösung zwischenzeitlich selber gefunden und werde sie hier natürlich veröffentlichen. Allerdings erst zu gegebener Zeit, denn vorher muss ich eine gepfefferte Antwort an zwei sonderbar schräge "Spassvögel" dieses Forums vorbereiten.

---

Bin nicht sicher, ob das Dein Wunschergebnis ist, einfach mal ausprobieren.

Hallo Christian

Hab deinen Vorschlag getestet. Er führte leider nicht zum gewünschten Ergebnis. Besten Dank trotzdem!

Im Übrigen habe ich inzwischen die Lösung selber gefunden. Selbstverständlich werde ich sie hier zu gegebner Zeit veröffentlichen ... nebst anderem teste ich sie zuerst noch sehr gründlich durch.

[shift-del]

Wenn der Faktor hingegen 0 ist, wird in jeder Zeile der Spalte "Werte" der Wert in der Zeile 4 der Tabellen des Registers "Tabellen" übernommen, der ganze Rest funktioniert bestens.

So funktioniert aber der Teil deiner Formel von dem du behauptet hast dass er korrekt arbeitet.
Natürlich hast du jetzt nicht erklärt welches Ergebnis du bei Faktor 0 tatsächlich erwartest.

Daher konnte ich deine Formel leider nicht übernehmen.

Eine Lösung kann nur so gut sein wie die Vorgaben. Und da hast du gepatzt.

[OversoulSeven]

So funktioniert aber der Teil deiner Formel von dem du behauptet hast dass er korrekt arbeitet.
Natürlich hast du jetzt nicht erklärt welches Ergebnis du bei Faktor 0 tatsächlich erwartest.

Eine Lösung kann nur so gut sein wie die Vorgaben. Und da hast du gepatzt.

Aufgrund dessen habe ich meine Angaben soeben nochmals überprüft und musste zu meiner Schande feststellen, dass du recht hast und mir tatsächlich zwei Fehler unterlaufen sind. Es tut mir leid, diese Fehler hätten mir echt nicht passieren dürfen und ich bitte dies freundlicherweise zu entschuldigen. Ich kann mir meine Lapsus Calami nur so erklären, dass mir zum Zeitpunkt, als ich dieses Thema erstellte, wohl allzuviele diesbezügliche Daten und Formeln in meinem Kopf rumschwirrten.

Erster Fehler
Nicht wenn der Faktor 0 ist, funktioniert meine Formel wie gewünscht, sondern wenn der Faktor 1 ist.

Zweiter Fehler
In meiner eigentlichen Datei gibt meine Formel, bei Faktor 0, ab der zweiten BEREICH.VERSCHIEBEN-Zeile nicht FEHLER, sondern FALSCH zurück - die erste BEREICH.VERSCHIEBEN-Zeile liefert das gewünschte Ergebnis.
In der Beispieldatei hingegen, gibt meine Formel, bei Faktor 0, immer den Wert aus Zeile 4 der jeweils gewählten Tabelle zurück.

Der problematische Teil liegt also unbestritten im dritten Argument der WENN-Funktion meiner Formel.
Um das gewünschte Ergebnis zu erhalten, müsste die Syntax so gestaltet sein, dass die Funktion BEREICH.VERSCHIEBEN den korrekten Wert (in der Spalte Werte der jeweils gewählten Tabelle) ermittelt und zurückgibt, indem sie den Bereich um soviele Zeilen nach unten verschiebt, wie in der benachbarten Spalte Verschieben um x Zeilen angegeben ist.

[BoskoBiati]

Hi,

ich stelle fest, dass Du nicht gewillt bist, von dieser dämlichen Bereich.verschieben-Formel zu lassen. Diese ist volatil, daher sollte man sie, wenn irgend möglich, weglassen. Alternativen wurden Dir einige vorgestellt. Vielleicht kannst Du Dich ja mal dazu äußern.

[EarlFred]

Ich habe, nebenbei erwähnt, die Lösung zwischenzeitlich selber gefunden und werde sie hier natürlich veröffentlichen. Allerdings erst zu gegebener Zeit, denn vorher muss ich eine gepfefferte Antwort an zwei sonderbar schräge "Spassvögel" dieses Forums vorbereiten.

---

@Edgar
die Prioritäten dieses Menschen sind anders gesetzt, als wir es üblicherweise voraussetzen würden:
- Die Anfrage ist extrem dringend (Aussage im anderen Forum) , aber wir haben nach einer Woche, die das Thema online ist, noch keine Beschreibung, was überhaupt als "richtiges" Ergebnis gewünscht wird. Mit blumigem Geschwurbel wird nur festgestellt, dass bisher alles nicht richtig ist. Weitere Nachfragen lassen kein anderes Ergebnis erwarten als die bisherigen.
- Eine Lösung existiert bereits, wird aber zur Steigerung des Spannungsbogens nicht veröffentlicht. 
- 2 sonderbar schräge "Spassvögel" sollen zuerst mit gepfefferten Antworten bedacht werden, das ist wichtiger. Ich zähle übrigens nur einen.  
- Es ist wichtiger, sich zuerst mal in mehreren Foren anzumelden und sinnbefreite Worthülsen plakatgroß in die Signatur einzutragen, als das Problem ordentlich zu beschreiben. (Spätestens seit C-Leugnern, I-Gegnern und Q-Denkern habe ich mit "eigenen Realitäten" ohnehin Probleme)

Du wirst dich also noch gedulden müssen, von dem Menschen sinnvolles zu lesen. Ich empfehle: /i

[OversoulSeven]

Hi, ich stelle fest, dass Du nicht gewillt bist (...)

Ich war nie auf die Funktion BEREICH.VERSCHIEBEN versessen, sondern kannte schlicht keine andere Funktion oder Funktionskombinationen, welche diese Aufgabe besser, sprich "schlanker" lösen könnten.

Des Weiteren werde ich mich in Kürze selbstverständlich dazu äussern. Ich bitte diesbezüglich höflich um etwas Geduld, es ist lediglich eine Frage von ein paar wenigen Tagen - hab gerade etwas viel um die Ohren. Besten Dank für das Verständnis!

---

@ Edgar die Prioritäten dieses Menschen sind anders gesetzt (...) Du wirst dich also noch gedulden müssen, von dem Menschen sinnvolles zu lesen. (...)

In Bälde werde ich hier sachorientiert antworten und zudem auf die entstandenen Missverständnisse sowie Fehlinterpretationen eingehen. Besten Dank im Voraus für deine Geduld!

[OversoulSeven]

Fazit dieses Themas und seinen Nebenwirkungen
Rein sachlich betrachtet, enthielt meine komplexe Exceldatei eine monströse Formel in Form einer WENN-Funktion mit 3215 Zeichen. Die Formel lieferte zwar das gewünschte Ergebnis, war mir allerdings zu "fett", insbesondere weil sie dazu auch noch 2400 Mal in einem einzelnen Arbeitsblatt zu replizieren war. Daher suchte ich eine Funktion, welche die Formel unter Berücksichtigung aller Bedingungen ungleich "schlanker" darzustellen vermag, und stiess dabei auf die hier umstrittene BEREICH.VERSCHIEBEN-Funktion.

Da ich Schwierigkeiten mit der Syntax im Rahmen einer Verschachtelung dieser Funktion hatte, suchte ich zuerst nach einer anderen geeigneten Funktion, fand "auf die Schnelle" aber keine. Das diesbezügliche Stöbern und Ausprobieren wurden mir dann mal zu bunt, da Excel ja nicht bloss über eine Handvoll Funktionen verfügt und diese zudem fast unendlich kombiniert werden können. Also entschied ich mich, Rat bei bewanderteren Excel-Anwender zu suchen. Der Überschaubarkeit und den Forum-Benutzern zuliebe, verzichtete ich auf einen ellenlangen textlichen Beschrieb und reduzierte meine Exceldatei in der "TestBeispielDatei" auf das Allerwesentlichste. Dies war zum einen offenbar unsachgemäss und zum anderen unterliefen mir dabei ein paar Patzer.
Für diese meine Unzulänglichkeiten sowie für die dadurch entstandene Turbulenz und das erlittene Ungemach, bitte ich an dieser Stelle alle Betroffenen um Entschuldigung.

Da ich letztendlich aber auch mit der obenerwähnten "fetten" Formel leben kann, werde ich dieses Thema als erledigt schliessen, sollten in den folgenden zwei Wochen keine konstruktiven Beiträge dazu mehr gepostet werden. Für die Interessierten hänge ich hier eine Exceldatei an, welche die "fette" Formel mit ihren Originalbezügen enthält.
  Die WENN-Formel im Original.xlsx (Größe: 27,34 KB / Downloads: 3)

Dann abschliessend noch ein paar Worte zu den Anfeindungen in einigen der eingegangenen Threads. Doch entgegen meiner ursprünglichen Absicht, werde ich, abgesehen von ein paar Fingerzeigen, niemandem eine Standpauke halten. Stattdessen überlasse ich es den Angesprochenen, ob sie ihr deplatziertes Gebaren Revue passieren lassen und überdenken wollen.

In Sachen Eiligkeit und Crossposting
Ich postete das Thema gleichzeitig auf die mir bis dahin unbekannten Foren CEF und MOF, weil ich möglichst viele potentielle Helfer erreichen wollte, nicht, weil ich es im wortwörtlichen Sinn eilig hatte. Von Crossposting hatte ich noch nie was gehört und war mir daher auch nicht über die diesbezügliche Problematik bewusst.
Des Weiteren war mir das Thema natürlich auch in der Zeit zwischen meinen Registrierungen auf diesen zwei Foren - als ich zusammen mit meiner Familie essen ging - wichtig.
Im Übrigen habe ich vorgängig mitnichten wochenlang getrödelt, was nur eine irrige Annahme in Unkenntnis der Sachlage und der Grösse meines Engagements darstellt.

In Sachen Signatur
Der Sinnspruch in meiner Signatur ist in Tat und Wahrheit alles andere als luftleer. Ich bin mir allerdings bewusst, dass es natürlich einen gewissen Horizont sowie eine gehörige Auffassungsgabe braucht, um ihn zu erfassen und zu verstehen, geschweige denn, um seine allumfassende Gültigkeit einzusehen.

In Sachen Forenregeln
Die Benutzer, welche mich bezüglich CP energisch an die Forenregeln mahnen, empfingen mich auf den erwähnten Foren wie eine Meute hungriger Hyänen und deckten mich sodann in mehreren Threads mit Freundlichkeiten ein, wie beispielsweise "Ausgeburt des Egoismus", "dein rein egoistisches Verhalten" oder "uneinsichtiger Crossposter" und bezeichnen mich als "Troll", der "den gleichen substanzlosen Quark" und "halbgaren Mist über zig Foren verbreitet / über das Netz streut".

Ob diese Benutzer die zwei folgenden Passagen in "IHREN" Forenregeln vergessen haben oder gar denken, selber über diesen Regeln zu stehen, kann ich nicht beurteilen:

§14 - Der User verpflichtet sich, keinen beleidigenden, diskriminierenden, politischen, religiösen, rassistischen oder gegen geltendes Gesetz verstoßenden Inhalt zu veröffentlichen.
§15 - Der User verpflichtet sich, einen freundlichen, respektierenden und menschenwürdigen Umgangston zu pflegen.

[BoskoBiati]

Hi,

ich beschränke mich mal auf die grausame WENN-Formel, allerdings muß ich leider feststellen, dass diese nichts, aber absolut gar nichts, mit der ursprünglichen Aufgabe zu tun hat!

Code:

=WENN(ISTZAHL(U2);Einsatztabellen!$K$5;INDEX(Einsatztabellen!K:K;VERGLEICH(W2;Einsatztabellen!K:K;0)+1))

[schauan]

@ Edgar,

ich denke, dass die zuletzt gepostete Datei ein früherer Stand des Projektes ist, das Blatt "Einsatztabellen" mit nur einem Block "A" später wie im ursprünglichen Post mit weiteren Blöcken "B", "C" usw. gefüllt wird.

@ Sven, wenn ich richtig liege - dann ist das schon verwirrend Eigentlich hattest Du geschrieben, dass Du die Lösung posten wolltest.
Wie shift-del schon schreibt, passen seine Ergebnisse zu Deinen. Nimmst Du den Faktor 0, dann bringt Dein BEREICH.VERSCHIEBEN und die Lösung von shift-del überall den Wert aus Zeile 4. Beim Faktor 1 kommen andere Werte, aber auch bei beiden Formeln identisch.

Bei Edgars Lösung musst Du von B3 die Zeile fixieren, also B$3. Dann bekommst Du bei Faktor 1 identische Ergebnisse bis auf Zeile 15, dort kommt #BEZUG Bei Faktor 0 bekommst Du den Zellwert aus Zeile 4 nur 1x

Bei Wechsel der Tabelle - von A zu B - bekommst Du mit Deiner Formel FALSCH (und nicht Fehler, hattest Du erkannt), die anderen Formeln bringen wie bei A Werte.

Allerdings sehe ich in der ersten Zeile mit BEREICH.VERSCHIEBEN auch FALSCH - Du schriebst, in der ersten Zeile würde das gewünschte Ergebnis stehen - ist es also das, was Du willst? Nur in der ersten Zeile FALSCH, und sonst nach unten nix weiter?