Excel Funktion mit vielen verschachtelungen...

...komplette Frage anzeigen

5 Antworten

Die Formel ist für einen Aussenstehenden kaum nachvollziehbar, da man die Begriiffe bzw ihre Fkt nicht kennt: BaF=irgendwas mit Ferien?oder FrühSpät/Tagschicht? usw. ausserdem weiß ausser Dir keiner, was in i,C,G,M,11:15 steht...

Was Du aber machen kannst und solltest:
Trenne die durch & verknüpften Formelteile in einzelne Zellen auf, deren Ergebnisse Du dann in einer Ergebniszelle wieder zusammenfügen kannst, dann wirds schon mal übersichtlicher.
Kannst Du eine Matrix schaffen mit Parametern für Wochentage(in einer Spalte) und einem anderen Parameter (ich vermute (BaF, BaS, BaT, ... SpS in jeweils einer zugehörigen Zeile?
Dann könntest Du mit =index(Bereich;Vergleich(Wochentag; in 1:7 Woche3ntage;0);Vergleich(BaF;BaF..SPS;0) vielleicht aus der Parametertabelle die zugehörige Zahl auslesen?
Das für die 5 durch & verknüpften Teile und dann wieder aneinandergehängt: das ist zwar nichz EINE FORMEL, aber EINE (Doppel-)EINGABE (zB:Sa und daneben BaS)mit daraus resultierend EINER AUSGABE.
Mehr kann ich so abstrakt auch nicht sagen! (aber wenn es so einfach wäre, wären die Profis brotlos!)

Iamiam 23.01.2013, 20:00

Hab jetzt doch noch eine wesentliche Vereinfachungsmöglichkeit entdeckt mit der ODER-Fkt anstelle von vielen Wenn-Verschachtelungen. Ich führ das für den ersten Abschnitt der Formel hier mal aus:

=WENN(UND(R$7="BaF";R$4="Sa B");$I$11;
WENN(UND(R$7="BaF";R$4="So");$M$11;
WENN(UND(R$7="BaF";
ODER(R$4="Mo";R$4="Di";R$4="Mi";R$4="Do";R$4="Fr"));$C$11
;"")))
&...

Das geht natürlich nur für jeweils den Teil, der das gleiche Ergebnis liefern soll
Spart 5-1=4 Klammerebenen und viel Formeltext!

Für die anderen Teile kannst Du das sicher selber machen.

Tipp: Du siehst, dass ich Umbrüche eingefügt habe zur Strukturierung. xl toleriert diese, auch reichen oft schon Leerzeichen, um Umbrüche hervorzurufen, was xl ebenfalls toleriert..

0
Iamiam 23.01.2013, 20:06
@Iamiam

Sehe eben, dass Achris Deinen Aufbau offensichtlich so gut durchblickt hat, dass er eine bessere Alternative mit anderen Formeln und spezieller Datenanordnung vorschlagen konnte!

0

Also grundsätzlich VBA brauchst du hier NICHT!!!

Erfasse in einem Bereich deine möglichen Kombinationen (in meinem Beispiel in B2:C22) in einer weiteren Spalte erfasst du die Zelle deren Wert bei der jeweiligen Kombination ausgegeben werden soll (bei mir D2:D22).

in der Zelle in der die riesige Formel steht erfasst du: =INDIREKT(INDEX(D2:D100;VERGLEICH(R4&R7;B2:B100&C2:C100;0)))

nach dem reinkopieren meiner Formel schliesst du Zelle nicht mit Enter ab sondern mit der Tastenkombination Strg+Shift+Enter.

In der Zelle sollte nun stehen {=INDIREKT(INDEX(D2:D100;VERGLEICH(R4&R7;B2:B100&C2:C100;0)))}

Das sollte deine Anforderung erfüllen.

Die Bereiche musst du natürlich noch anpassen. ich habe es der übersichtlichkeit halber in die ersten Zellen gemacht aber die sind ja bei dir belegt. Du kannst beliebig viele Kombinationen und Ausgabezellen hinterlegen musst dann halt nur die entsprechenden Bereiche erweitern.

Jedesmal wenn du in der Zelle mit der formel was bearbeitest must du erneut mit Strg+Shift+Enter abschliessen sonst funktioniert die formel nicht.

P.S. die geschweiften Klammern nicht manuell mit eingeben, diese werden automatisch erzeugt.

Bild zur Antwort - (Excel, Formel)

Mit VBA gehts tatsätzlich wesendlich übersichtlicher. Ellenlange Formen,da blickt keiner mehr durch. Daher zuerst die Zellen benamen. Kleines Beispiel: Statt A1 * B1 lieber schreiben: Netto * MWST.

Dann ist es auch besser in jeder Spalte nur ein Teil berechnen. In der nächsten Spalte aus diesem Ergebnis das nächste berechnen. Zum Schluss wird aus allen Ergebnissen das Schlussergebnis berechnet. Dann alle Zwischenergebnisse ausblenden.

augsburgchris 23.01.2013, 14:35

Was soll man da denn in einzelnen Spalten berechnen? Was genau gedenkst du hier mit VBA zu tun? Bitte poste doch den entsprechenden Code.

0
dkilli 23.01.2013, 15:44
@augsburgchris

Ich habe geschrieben, dass es mit VBA übersichtlicher ist. Man kann doch in einer Zelle =Wenn das so ist, mache was sonst mache was anderes. Nächste Zelle Wenn vorhergehendes Ergebnis dann mache was usw. Ende wäre Wenn alles nicht erfüllt wurde, dann mache dies.

0

Solche Monster sind unkotrollierbar - eine Funktion per VBA ließe sich wesentlich überschaubarer gestalten

Ummelchen 22.01.2013, 23:41

da habe ich leider keinen Plan

0
augsburgchris 23.01.2013, 14:48

wieso denn VBA. Wenn die WENN-Formel nicht sinnvoll ist (wie in diesem Fall) gibt es unzählige andere Formeln die genau das gewünschte tun. Im Grunde könnte man sich sogar die Hilfspalten sparen. was die Formel dann etwas grösser macht: {=INDIREKT(INDEX({"I11";"M11";"E11";"E11";"E11";"E11";"E11";"E11";"E12";"E12";"E12";"E12";"E12";"E12";"E13";"E13";"E13";"E13";"E13";"E13";"E13"};VERGLEICH(R4&R7;{"Sa";"So";"Mo";"Di";"Mi";"Do";"Fr";"Sa";"So";"Mo";"Di";"Mi";"Do";"Fr";"Sa";"So";"Mo";"Di";"Mi";"Do";"Fr"}&{"BaF";"BaF";"BaF";"BaF";"BaF";"BaF";"BaF";"BaF";"BaS";"BaS";"BaS";"BaS";"BaS";"BaS";"BaT";"BaT";"BaT";"BaT";"BaT";"BaT";"BaT"};0)))}

Aber auch das ist noch recht überschaubar. Warum in VBA was programmieren wenn man die Möglichkeiten die in Excel eingebaut sind noch gar nicht ausgeschöpft hat.
Verstehe ich nicht wirklich. Und was soll deiner Meinung nach das VBA Proramm denn genau tun?

0
gejaf 23.01.2013, 23:25
@augsburgchris

Was ist da überschaubar??

Adäquates Kopfschütteln nicht möglich - mit einem Schleudertrauma ist nicht zu spaßen

So etwas kann nur jemad schreiben, der kaum Ahnung von VBA hat

0

Was möchtest Du wissen?